Wednesday, July 16, 2014

Oracle EBS R12 DB Tier Cloning Issue Resolution


Oracle EBS R12 DB Tier Cloning was NOT successful. But Database was mounted. Then following can be attempted to recover and startup the database.

1. Try to open the database using resetlogs option.

SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/u01/TEST/db/apps_st/data/system01.dbf'


2. If similar error comes like above, Shutdown immediate     

SQL> Shutdown immediate

3. Remark the parameter in initTEST.ora:  

 UNDO_MANAGEMENT=AUTO
 UNDO_TABLESPACE=APPS_UNDOTS

4. Add the parameter in initTEST.ora :         

    UNDO_MANAGEMENT=MANUAL
    _ALLOW_RESETLOGS_CORRUPTION = TRUE
    _ALLOW_ERROR_SIMULATION = TRUE

5. Startup database with Manual undo management.using new initTEST.ora      

sqlplus '/as sysdba'

SQL> startup mount pfile=initTEST.ora

SQL> recover database using backup controlfile until cancel;

SQL> alter database open resetlogs;

6. Create new UNDO Tablespace  

SQL> Create UNDO tablespace NEW_UNDOTS datafile '/u01/TEST/db/apps_st/data/undo01.dbf' size 2048M;

7. Take offline the OLD Undo Tablespace :  
 
SQL> alter tablespace APPS_UNDOTS offline;

8. Take online the NEW Undo Tablespace :  

SQL> alter tablespace NEW_UNDOTS online;

9. Shutdown the database :      

SQL> shutdown immediate;

10. Edit the initTEST.ora :  

    + Remark the parameter :

    UNDO_MANAGEMENT=MANUAL
    _ALLOW_RESETLOGS_CORRUPTION = TRUE
    _ALLOW_ERROR_SIMULATION = TRUE

    + Add and edit the parameter :

    UNDO_MANAGEMENT=AUTO
    UNDO_TABLESPACE=NEW_UNDOTS 

11. Startup the database :

SQL> startup

12. Set the  default Undo tablespace as NEW_UNDOS

SQL> alter system set undo_tablespace=NEW_UNDOTS;

13. Then we can drop the OLD Undo tablespace :   

SQL> drop tablespace APPS_UNDOTS including contents and datafiles;

14. Also reuse the existing TEMP tablespace tempfiles or create a new TEMP tablespace.

Open Database failed - DATAFILE NEEDS MORE RECOVERY TO BE CONSISTENT ORA-1194 ORA-1547 ORA-1110 (Doc ID 1528788.1)