ORA-12528: TNS:listener: all appropriate instances are blocking new connections

Symptoms

– Start RMAN to duplicate a new database from existing database, I got the following messages

$ rman target / nocatalog auxiliary sys/12345@mhqa1

Recovery Manager: Release 11.1.0.7.0 – Production on Fri Jun 11 10:19:36 2010

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

connected to target database: CVIQA (DBID=188335673)
using target database control file instead of recovery catalog
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00554: initialization of internal recovery manager package failed
RMAN-04006: error from auxiliary database: ORA-12528: TNS:listener: all appropriate instances are blocking new connections

Causes

Most probably your auxiliary database (in nomount state) is the only instance works on current ORACLE_HOME. Each instance is registered in listener by PMON process, which starts only when database is at least mounted. If there is nothing registered in listener before, instance in blocked.

Workaround

You can do 2 things:
1. Modify listener.ora like this:

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = AUXDB)
(ORACLE_HOME = /app/oracle/product/10.2.0/)
(SID_NAME = AUXDB)
)
)
OR
2. Modify tnsnames.ora (10g only!) :

AUXDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = AUXDB)(UR=A)
)
)

Slow sqlpus connection

Symptoms

1. Get connected fast to the database with sysdba as the following
[oracle@SGRAC1 ~]$ sqlplus ‘/as sysdba’

2. Get connected slowly to the database with other user with tnsname
[oracle@SGRAC1 ~]$ sqlplus vod/vod@sgcmsdev1
SQL*Plus: Release 11.1.0.7.0 – Production on Tue May 11 01:54:40 2010
Copyright (c) 1982, 2008, Oracle. All rights reserved. <– Stuck at here and wait about 10 seconds.

Connected to: Oracle Database 11g Release 11.1.0.7.0 – 64bit Production With the Real Application Clusters option

SQL>

Fixed

Remove /etc/resolv.conf, the problem get solved

ORA-00214: control file ‘/u01/oracle/oradb/control01.ctl’ version 1316

Symptoms

When starting the database, it returned the messages.

SQL> startup
ORACLE instance started.

Total System Global Area 4275781632 bytes
Fixed Size                  2220200 bytes
Variable Size            2298482520 bytes
Database Buffers         1962934272 bytes
Redo Buffers               12144640 bytes
ORA-00214: control file ‘/u01/oracle/oradb/control01.ctl’ version 1316
inconsistent with file ‘/opt/oracle/flash_recovery_area/oradb/control02.ctl’
version 1138

Fix Procedures

1. Startup nomount

SQL> startup nomount
ORACLE instance started.

Total System Global Area 4275781632 bytes
Fixed Size                  2220200 bytes
Variable Size            2298482520 bytes
Database Buffers         1962934272 bytes
Redo Buffers               12144640 bytes

2. Change control_files setting

SQL> alter system set control_files=’/u01/oracle/oradb/control01.ctl’ scope=spfile;

System altered.

3. Restart the database

SQL> shutdown immediate
ORA-01507: database not mounted

ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 4275781632 bytes
Fixed Size                  2220200 bytes
Variable Size            2298482520 bytes
Database Buffers         1962934272 bytes
Redo Buffers               12144640 bytes
Database mounted.
Database opened.
SQL>
SQL> show parameter spfile

NAME                                 TYPE        VALUE
———————————— ———– ——————————
spfile                               string      /opt/oracle/product/11g/dbs/sp
fileoradb.ora
SQL> show parameter control

NAME                                 TYPE        VALUE
———————————— ———– ——————————
control_file_record_keep_time        integer     7
control_files                        string      /u01/oracle/oradb/control01.ct

ORA-06512 ‘DBSNMP.BSLN_INTERNAL’

Symptoms

1.  BSLN_MAINTAIN_STATS_JOB failed at every SUNDAY

2.  Alert messages got the following

Errors in file /opt/oracle/diag/rdbms/vstbpro/vstbpro1/trace/vstbpro1_j000_7796.trc:
ORA-12012: error on auto execute of job 11762
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at “DBSNMP.BSLN_INTERNAL”, line 2073

3. Details of the trace file

Trace file /opt/oracle/diag/rdbms/vstbpro/vstbpro1/trace/vstbpro1_j000_7796.trc
Oracle Database 11g Release 11.1.0.7.0 – 64bit Production
With the Real Application Clusters option
ORACLE_HOME = /opt/oracle/product/11g
System name:    SunOS
Node name:      wvpdb09
Release:        5.10
Version:        Generic_138888-01
Machine:        sun4u
Instance name: vstbpro1
Redo thread mounted by this instance: 1
Oracle process number: 73
Unix process pid: 7796, image: oracle@wvpdb09 (J000)

*** 2010-04-11 15:00:04.639
*** SESSION ID:(1041.32020) 2010-04-11 15:00:04.639
*** CLIENT ID:() 2010-04-11 15:00:04.639
*** SERVICE NAME:(SYS$USERS) 2010-04-11 15:00:04.639
*** MODULE NAME:(DBMS_SCHEDULER) 2010-04-11 15:00:04.639
*** ACTION NAME:(BSLN_MAINTAIN_STATS_JOB) 2010-04-11 15:00:04.639

ORA-12012: error on auto execute of job 11762
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at “DBSNMP.BSLN_INTERNAL”, line 2073
ORA-06512: at line 1

Cause

Table DBSNMP.BSLN_BASELINES contains inconsistent information. After database cloning , existing records in table “DBSNMP.BSLN_BASELINES” conflict with new baseline information inserted in the cloned database. The DBSNMP user needs to be dropped and re-created.

Fixed

SQL> @?/rdbms/admin/catnsnmp.sql

SQL> @?/rdbms/admin/catsnmp.sql

RACG][1] [2945][1][ora.m3k.vip]: checkIf: Default gateway is not defined

Symptons

1.  Failed to start VIP on servers nodes

2. Get messags below from $CRS_HOME/log/m3k/racg/ora.m3k.vip.log

2010-03-15 18:21:48.068: [    RACG][1] [5400][1][ora.m3k.vip]: checkIf: Default gateway is not defined (host=m3k)
Interface bge0 checked failed (host=m3k)
Failed to start VIP 192.168.1.42 (host=m3k)

Fixed

Set FAIL_WHEN_DEFAULTGW_NOT_FOUND=0 in $CRS_HOME/bin/racgvip as below
# set it to 0 for checkIf() to return success if default gateway is not found,
# otherwise set it to 1
FAIL_WHEN_DEFAULTGW_NOT_FOUND=0

RMAN-06207: WARNING: 2 objects could not be deleted for DISK channel(s) due to mismatched status

Problem Symptoms

RMAN-06207: WARNING: 2 objects could not be deleted for DISK channel(s) due
RMAN-06208:          to mismatched status.  Use CROSSCHECK command to fix status
RMAN-06210: List of Mismatched objects
RMAN-06211: ==========================
RMAN-06212:   Object Type   Filename/Handle
RMAN-06213: ————— —————————————————
RMAN-06214: Backup Piece    /export/home/oracle/bkup/vstbqa/bkup_VSTBQA_2_1_703089741.dat
RMAN-06214: Backup Piece    /export/home/oracle/bkup/vstbqa/bkup_VSTBQA_3_1_703089956.dat

Fix

RMAN> crosscheck backup;

using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=264 instance=vstbqa1 device type=DISK
crosschecked backup piece: found to be ‘EXPIRED’
backup piece handle=/export/home/oracle/bkup/vstbqa/bkup_VSTBQA_2_1_703089741.dat RECID=2 STAMP=703089744
crosschecked backup piece: found to be ‘EXPIRED’
backup piece handle=/export/home/oracle/bkup/vstbqa/bkup_VSTBQA_3_1_703089956.dat RECID=3 STAMP=703089956
crosschecked backup piece: found to be ‘AVAILABLE’
backup piece handle=/bkuppool/oracle/bkup/databases/vstbqa/bkup_VSTBQA_250_1_713655198.dat RECID=248 STAMP=713655198
crosschecked backup piece: found to be ‘AVAILABLE’
backup piece handle=/bkuppool/oracle/bkup/databases/vstbqa/bkup_VSTBQA_251_1_713655384.dat RECID=249 STAMP=713655385
crosschecked backup piece: found to be ‘AVAILABLE’
backup piece handle=/bkuppool/oracle/bkup/databases/vstbqa/bkup_ctl_c-3621525582-20100314-00.dat RECID=250 STAMP=713655386
Crosschecked 5 objects

RMAN> delete expired backup;

using channel ORA_DISK_1

List of Backup Pieces
BP Key  BS Key  Pc# Cp# Status      Device Type Piece Name
——- ——- — — ———– ———– ———-
2       2       1   1   EXPIRED     DISK        /export/home/oracle/bkup/vstbqa/bkup_VSTBQA_2_1_703089741.dat
3       3       1   1   EXPIRED     DISK        /export/home/oracle/bkup/vstbqa/bkup_VSTBQA_3_1_703089956.dat

Do you really want to delete the above objects (enter YES or NO)? YES
deleted backup piece
backup piece handle=/export/home/oracle/bkup/vstbqa/bkup_VSTBQA_2_1_703089741.dat RECID=2 STAMP=703089744
deleted backup piece
backup piece handle=/export/home/oracle/bkup/vstbqa/bkup_VSTBQA_3_1_703089956.dat RECID=3 STAMP=703089956
Deleted 2 EXPIRED objects

Fix mismatched backup pieces at RMAN

Symptoms

RMAN-06207: WARNING: 119 objects could not be deleted for DISK channel(s) due
RMAN-06208:          to mismatched status.  Use CROSSCHECK command to fix status
RMAN-06210: List of Mismatched objects
RMAN-06211: ==========================
RMAN-06212:   Object Type   Filename/Handle
RMAN-06213: ————— —————————————————
RMAN-06214: Backup Piece    /export/home/oracle/bkup/sgptqa/bkup_SGPTQA_611_1_708325704.dat
RMAN-06214: Backup Piece    /export/home/oracle/bkup/sgptqa/bkup_SGPTQA_612_1_708325913.dat
RMAN-06214: Backup Piece    /export/home/oracle/bkup/sgptqa/bkup_ctl_c-3621525582-20100115-00.dat
RMAN-06214: Backup Piece    /export/home/oracle/bkup/sgptqa/bkup_SGPTQA_627_1_708411652.dat
RMAN-06214: Backup Piece    /export/home/oracle/bkup/sgptqa/bkup_SGPTQA_628_1_708411719.dat
RMAN-06214: Backup Piece    /export/home/oracle/bkup/sgptqa/bkup_SGPTQA_629_1_708411786.dat
RMAN-06214: Backup Piece    /export/home/oracle/bkup/sgptqa/bkup_SGPTQA_630_1_708411852.dat
RMAN-06214: Backup Piece    /export/home/oracle/bkup/sgptqa/bkup_SGPTQA_631_1_708411918.dat
RMAN-06214: Backup Piece    /export/home/oracle/bkup/sgptqa/bkup_SGPTQA_632_1_708411985.dat
RMAN-06214: Backup Piece    /export/home/oracle/bkup/sgptqa/bkup_SGPTQA_633_1_708412041.dat
RMAN-06214: Backup Piece    /export/home/oracle/bkup/sgptqa/bkup_SGPTQA_634_1_708412098.dat
RMAN-06214: Backup Piece    /export/home/oracle/bkup/sgptqa/bkup_SGPTQA_635_1_708412109.dat

Fixed Procedure

$ env|grep SID
ORACLE_SID=cviqa1
$ ORACLE_SID=sgptqa1;export ORACLE_SID
$ rman target /

Recovery Manager: Release 11.1.0.7.0 – Production on Mon Feb 1 12:33:04 2010

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

connected to target database: SGPTQA (DBID=3621525582)

RMAN> crosscheck backup;

using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=273 instance=sgptqa1 device type=DISK
crosschecked backup piece: found to be ‘EXPIRED’
backup piece handle=/export/home/oracle/bkup/sgptqa/bkup_SGPTQA_611_1_708325704.dat RECID=611 STAMP=708325707
crosschecked backup piece: found to be ‘EXPIRED’
backup piece handle=/export/home/oracle/bkup/sgptqa/bkup_SGPTQA_612_1_708325913.dat RECID=612 STAMP=708325913
crosschecked backup piece: found to be ‘EXPIRED’
backup piece handle=/export/home/oracle/bkup/sgptqa/bkup_ctl_c-3621525582-20100115-00.dat RECID=613 STAMP=708325915
crosschecked backup piece: found to be ‘EXPIRED’
backup piece handle=/export/home/oracle/bkup/sgptqa/bkup_SGPTQA_627_1_708411652.dat RECID=627 STAMP=708411653
crosschecked backup piece: found to be ‘EXPIRED’
backup piece handle=/export/home/oracle/bkup/sgptqa/bkup_SGPTQA_628_1_708411719.dat RECID=628 STAMP=708411720
crosschecked backup piece: found to be ‘EXPIRED’

RMAN-06059: expected archived log not found, lost of archived log compromises recoverability

Symptoms

Starting backup at 01-FEB-10
current log archived
released channel: ch1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of backup plus archivelog command at 02/01/2010 09:45:23
RMAN-06059: expected archived log not found, lost of archived log compromises recoverability
ORA-19625: error identifying file +SG_DG/sgptqa/archivelog/2010_01_31/thread_1_seq_8972.6485.709712185
ORA-17503: ksfdopn:2 Failed to open file +SG_DG/sgptqa/archivelog/2010_01_31/thread_1_seq_8972.6485.709712185
ORA-15012: ASM file ‘+SG_DG/sgptqa/archivelog/2010_01_31/thread_1_seq_8972.6485.709712185’ does not exist

Fixed

1. Perform a crosscheck, run the following command from within RMAN:

RMAN> change archivelog all crosscheck;

2. Perform a full backup of the database at this point.

RMAN> run{
2> allocate channel ch1 type disk;
3> sql ‘alter system archive log current’;
4> backup
5> format ‘/bkuppool/oracle/bkup/databases/sgptqa/bkup_%d_%s_%p_%t.dat’
6> database plus archivelog;
7> delete obsolete;
8> }
9>
using target database control file instead of recovery catalog
allocated channel: ch1
channel ch1: SID=257 instance=sgptqa1 device type=DISK

sql statement: alter system archive log current

ORA-39083: Object type TABLE failed to create with error:

Symptoms

Today,  I transfer a schema from 10.2.0.3 database to 11.1.0.7 database. All tables can be transferred by expdp/impdp except of one with following when it is imported by imdp

With the Real Application Clusters option
Master table “SYSTEM”.”SYS_IMPORT_TABLE_01″ successfully loaded/unloaded
Starting “SYSTEM”.”SYS_IMPORT_TABLE_01″:  system/******** parfile=imp_sgcmsqa_tab.par
Processing object type SCHEMA_EXPORT/TABLE/TABLE
ORA-39083: Object type TABLE failed to create with error:
ORA-00922: missing or invalid option
Failing sql is:
CREATE TABLE “CMSADM”.”VOD_METADATA_UPLOAD” (“ID” NUMBER(19,0) NOT NULL ENABLE, “UPLOAD_DATA” BLOB, “FILE_NAME” VARCHAR2(100 BYTE), “IS_LOAD” NUMBER(1,0), “IS_VALIDATE” NUMBER(1,0), “LAST_LOAD_DATE” TIMESTAMP (6), “LIBRARY_ID” VARCHAR2(30 BYTE) NOT NULL ENABLE, “IS_DELETE” NUMBER(1,0), “VERSION” NUMBER(10,0) NOT NULL ENABLE, “UPDATER_ID” NUMBER(19,0), “AUDIT_ID” NUMBER(19,0), “CREATOR_ID” NUMBER(
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
ORA-39112: Dependent object type INDEX:”CMSADM”.”VOD_METADATA_UPLOAD_PK” skipped, base object type TABLE:”CMSADM”.”VOD_METADATA_UPLOAD” creation failed
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
ORA-39112: Dependent object type CONSTRAINT:”CMSADM”.”VOD_METADATA_UPLOAD_PK” skipped, base object type TABLE:”CMSADM”.”VOD_METADATA_UPLOAD” creation failed
Job “SYSTEM”.”SYS_IMPORT_TABLE_01″ completed with 3 error(s) at 16:27:36

Fixed

Add version option as the following impdp parfile, it is fixed.

dumpfile=EXPDMP:sgcmsqa.dmp
LOGFILE=expdmp:imp_sgcmsqa_tab.log
TABLE_EXISTS_ACTION=REPLACE
version=10.2.0.3 <– add this option
REMAP_SCHEMA=(CMSADM:CMSADM)
tables=VOD_METADATA_UPLOAD

$ impdp system/oracle parfile=imp_sgcmsqa_tab.par

Import: Release 11.1.0.7.0 – 64bit Production on Wednesday, 13 January, 2010 16:40:19

Copyright (c) 2003, 2007, Oracle.  All rights reserved.

Connected to: Oracle Database 11g Release 11.1.0.7.0 – 64bit Production
With the Real Application Clusters option
Master table “SYSTEM”.”SYS_IMPORT_TABLE_01″ successfully loaded/unloaded
Starting “SYSTEM”.”SYS_IMPORT_TABLE_01″:  system/******** parfile=imp_sgcmsqa_tab.par
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported “CMSADM”.”VOD_METADATA_UPLOAD”              6.111 MB      47 rows
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job “SYSTEM”.”SYS_IMPORT_TABLE_01″ successfully completed at 16:40:37

WARNING 10.0.0.0 could not be translated to a network address error 1

Symptoms

1. When the database is started,  get the following warning messages in the alert log

Interface type 1 bge2 10.0.0.0 configured from OCR for use as a cluster interconnect
WARNING 10.0.0.0 could not be translated to a network address error 1
Interface type 1 fjgi1 10.0.0.0 configured from OCR for use as a cluster interconnect
WARNING 10.0.0.0 could not be translated to a network address error 1
Interface type 1 bge1 172.31.0.0 configured from OCR for use as  a public interface
Interface type 1 fjgi0 172.31.0.0 configured from OCR for use as  a public interface
WARNING: No cluster interconnect has been specified. Depending on
the communication driver configured Oracle cluster traffic
may be directed to the public interface of this machine.
Oracle recommends that RAC clustered databases be configured
with a private interconnect for enhanced security and
performance.

2.  IPMP is configured for the private interconnect and public interface

bge1: flags=201000843<UP,BROADCAST,RUNNING,MULTICAST,IPv4,CoS> mtu 1500 index 3
inet 172.31.3.124 netmask ffffff00 broadcast 172.31.3.255
groupname orapub <– public network IPMP group
bge1:1: flags=209040843<UP,BROADCAST,RUNNING,MULTICAST,DEPRECATED,IPv4,NOFAILOVER,CoS> mtu 1500 index 3
inet 172.31.3.121 netmask ffffff00 broadcast 172.31.3.255
bge1:2: flags=201040843<UP,BROADCAST,RUNNING,MULTICAST,DEPRECATED,IPv4,CoS> mtu 1500 index 3
inet 172.31.3.128 netmask ffffff00 broadcast 172.31.3.255
bge2: flags=201000843<UP,BROADCAST,RUNNING,MULTICAST,IPv4,CoS> mtu 1500 index 4
inet 10.1.1.124 netmask ffffff00 broadcast 10.1.1.255
groupname orapriv <– private interconnections IPMP group
bge2:1: flags=209040843<UP,BROADCAST,RUNNING,MULTICAST,DEPRECATED,IPv4,NOFAILOVER,CoS> mtu 1500 index 4
inet 10.1.1.121 netmask ffffff00 broadcast 10.1.1.255
fjgi0: flags=269040843<UP,BROADCAST,RUNNING,MULTICAST,DEPRECATED,IPv4,NOFAILOVER,STANDBY,INACTIVE,CoS> mtu 1500 index 5
inet 172.31.3.122 netmask ffffff00 broadcast 172.31.3.255
groupname orapub
fjgi1: flags=269040843<UP,BROADCAST,RUNNING,MULTICAST,DEPRECATED,IPv4,NOFAILOVER,STANDBY,INACTIVE,CoS> mtu 1500 index 6
inet 10.1.1.122 netmask ffffff00 broadcast 10.1.1.255
groupname orapriv

Cause

When the instance starts, it gets two NIC interfaces configured as  public network and cluster interconnect according to OCR. To fix it, we have to remove the private interconnect and public interface configuration from OCR and specific it in the database initial parameter file.

Fix

1. To remove public and cluster interconnect from OCR

# oifcfg delif -global <if-name>

2. To update spfile initial parameter

ALTER SYSTEM SET cluster_interconnects=’10.1.1.124′ SCOPE=SPFILE SID=’vstbpro1′;

ALTER SYSTEM SET cluster_interconnects=’10.1.1.123′ SCOPE=SPFILE SID=’vstbpro2′;

3. Restart instance