Thursday, March 29, 2012

ORA-30012: undo tablespace 'UNDO_2' does not exist or of wrong type


ORA-30012: undo tablespace 'UNDO_2' does not exist or of wrong type

I have 3 node RAC system. I am trying to convert from single to the RAC system. I have created undo tablespace for instance 2 & 3. I am having thrown the below error messages.


SQL> startup
ORACLE instance started.

Total System Global Area 1219334144 bytes
Fixed Size                  2227824 bytes
Variable Size             620757392 bytes
Database Buffers          587202560 bytes
Redo Buffers                9146368 bytes
Database mounted.
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-30012: undo tablespace 'UNDO_2' does not exist or of wrong type
Process ID: 6920
Session ID: 67 Serial number: 5

Note: you might the get this error message when you have specified the wrong undo tablespace for the particular instance in the rac environment or single instance environment. In that case, you have to create a pfile and you have modify the undo_tablespace value.


I have login to the Instance-1 and try to drop the undo tablespace which has been created for instance-2 & instance-3

SQL> drop tablespace UNDO_2 including contents and datafiles;

Tablespace dropped.

SQL> drop tablespace UNDO_3 including contents and datafiles;

Tablespace dropped.

Then I have used the CREATE UNDO TABLESPACE option to create the tablespace for Instance – 2 & 3.


An undo tablespace is a type of permanent tablespace used by Oracle Database to manage undo data if you are running your database in automatic undo management mode. Oracle strongly recommends that you use automatic undo management mode rather than using rollback segments for undo.

SQL> create undo tablespace UNDO_2 datafile '+BHU_A_DATA1' size 4096M reuse;

Tablespace created.

SQL> create undo tablespace UNDO_3 datafile '+BHU_A_DATA1' size 4096M reuse;

Tablespace created.

I have modified the undo tablespace for the Instance 2 & 3 in the spfile

SQL>  alter system set undo_tablespace='UNDO_2' scope=spfile sid='BHU_2';

System altered.

SQL> alter system set undo_tablespace='UNDO_3' scope=spfile sid='BHU_3';

System altered.

After the modification, I am trying to open the instance-2 & 3. it opens with out any issues.


SQL> startup
ORACLE instance started.

Total System Global Area 1219334144 bytes
Fixed Size                  2227824 bytes
Variable Size             620757392 bytes
Database Buffers          587202560 bytes
Redo Buffers                9146368 bytes
Database mounted.
Database opened.
SQL>

SQL> show parameter undo

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
_in_memory_undo                      boolean     FALSE
undo_management                      string      AUTO
undo_retention                       integer         900
undo_tablespace                      string        UNDO_2
SQL>

I hope this resolve your issue and happy learning!!!

Saturday, March 17, 2012

ORA-16737: the redo transport service for standby database 'BHU_B" has an error


ORA-16737: the redo transport service for standby database "BHU_B" has an error


Error message
DGMGRL> show database verbose 'BHU_A';

Database – BHU_A

  Role:            PRIMARY
  Intended State:  TRANSPORT-ON
  Instance(s):
    BHU_1
    BHU_2
    BHU_3
      Error: ORA-16737: the redo transport service for standby database "BHU_B" has an error

  Database Warning(s):
    ORA-16629: database reports a different protection level from the protection mode


#1
CHECK: when you have the above problem, you will get the protection mode in the    Primary & standby v$database.protection_level shows  as "RESYNCHRONIZATION"

select protection_mode, protection_level from v$database; 

#2
CHECK: whether you are getting any in the archive location 

select dest_id,status,error from v$archive_dest;

#3
CHECK: Check whether online redo log are configured in the primary & standby database properly, it includes size & accessibility 
Note: primary &standby online redo log should be same
select group#,thread#,sequence#,bytes,archived,status from v$log;  

#4
CHECK: Check whether standby redo log are configured in the primary &standby database properly, it includes size & accessibility 
Note: primary &standby standby redo log should be same 
select member from v$logfile where type='STANDBY'; 

#5
CHECK: Check parameters are configured properly; some times instance parameters have a different value. Ex: some common parameter will have different value for each instance in the cluster database. You need to check on the primary &standby cluster database environment.

#6
Check whether maximum Availability is enabled, when you have LogXptMode is synchronization

SYMP:
ORA-16629: database reports a different protection level from the protection mode

In DG Broker
DGMGRL> EDIT CONFIGURATION SET PROTECTION MODE AS MAXAVAILABILITY;
Succeeded.

#7 check your password with the setting
A)
1) check for the "sec_case_sensitive_logon" parameter.
2) if the problem exist, create the password file with ignorecase option in the orapwd password creation.
3) after recreating the password, restart both the primary & standby database.

B)

DGMGRL> show database verbose 'BHU_A' LogXptStatus;
LOG TRANSPORT STATUS
PRIMARY_INSTANCE_NAME STANDBY_DATABASE_NAME               STATUS
            BHU1_1             BHU1_B
            BHU1_2             BHU1_B ORA-16191: Primary log shipping client not logged on standby