Sunday, October 15, 2017

Recover the Database without Archive Log

source

When we did a cloning/recover the database with noarchivelog mode, we got the problem that some datafile need to be recover. It will be difficulty since no archivelog that can help us to recover it. Otherwise we can copy all datafiles from offline backup of the source database. But it will takes time to copy/ftp/restore especially if the database size are hundreds GB or even TB. But there is a solution to recover the database with noarchivelog mode, please check this out :
When we did a cloning, startup nomount :           
$ sqlplus '/as sysdba' 
SQL*Plus: Release 10.2.0.4.0 - Production on Tue Apr 13 13:54:43 2010 
Copyright (c) 1982, 2007, Oracle. All Rights Reserved. 
Connected to an idle instance. 
SQL> startup nomount pfile=initMYDB.ora
ORACLE instance started. 
Total System Global Area 5251268608 bytes
Fixed Size 2091368 bytes
Variable Size 1040189080 bytes
Database Buffers 4194304000 bytes
Redo Buffers 14684160 bytes 

Create New control File:
      
SQL> @createctl.sql 
Control file created. 
Since the cloning come from offline backup and the SID in target db as same as source db so
we don’t need to resetlogs, but the one of datafile need to recover : 
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: '/u01/system01.dbf' 
 Try To recover, but we don’t have the archivelog file that needed : 
SQL> recover database using backup controlfile until cancel;
ORA-00279: change 5991183372639 generated at 04/13/2010 13:51:42 needed for
thread 1
ORA-00289: suggestion :
/u02/db/10.2.0/dbs/arch1_1125_714320021.dbf
ORA-00280: change 5991183372639 for thread 1 is in sequence #1125 
Specify log: {=suggested | filename | AUTO | CANCEL}
cancel
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/u01/system01.dbf' 
ORA-01112: media recovery not started 
Try to open resetlogs, we still got the same error  
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/system01.dbf' 
To fix this issue :
1. Shutdown immediate 
     
SQL> Shutdown immediate 
2. Remark the parameter in initMYDB.ora:   
    - UNDO_MANAGEMENT=AUTO - UNDO_TABLESPACE=OLD_UNDOTS
3. Add the parameter in initMYDB.ora :          
    - UNDO_MANAGEMENT=MANUAL - _ALLOW_RESETLOGS_CORRUPTION = TRUE - _ALLOW_ERROR_SIMULATION = TRUE
4. Startup database with new init.ora :          
$ sqlplus '/as sysdba' 
SQL*Plus: Release 10.2.0.4.0 - Production on Tue Apr 13 16:06:56 2010 
Copyright (c) 1982, 2007, Oracle. All Rights Reserved. 
Connected to an idle instance. 
SQL> startup mount pfile=initMYDB.ora
ORACLE instance started. 
Total System Global Area 5251268608 bytes
Fixed Size 2091368 bytes
Variable Size 1040189080 bytes
Database Buffers 4194304000 bytes
Redo Buffers 14684160 bytes
Database mounted.
SQL> recover database using backup controlfile until cancel;
ORA-00279: change 5991183372639 generated at 04/13/2010 13:51:42 needed for
thread 1
ORA-00289: suggestion :
/u02/db/10.2.0/dbs/arch1_1125_714320021.dbf
ORA-00280: change 5991183372639 for thread 1 is in sequence #1125 
Specify log: {=suggested | filename | AUTO | CANCEL}
cancel
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/u01/system01.dbf' 
ORA-01112: media recovery not started 
SQL> alter database open resetlogs; 
Database altered. 
5. Now the database already startup with Manual undo management.
6. Create new UNDO Tablespace 
  
SQL> Create UNDO tablespace NEW_UNDOTS datafile '/u02/undo01.dbf' size 2048M; 
7. Take offline the OLD Undo Tablespace :   
 
 
 
SQL> alter tablespace OLD_UNDOTS offline; 
8. Take online the NEW Undo Tablespace :   
SQL> alter tablespace NEW_UNDOTS ; 
9. Shutdown the database :       
SQL> shutdown immediate; 
10. Edit the initMYDB.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. The database will startup with the NEW Undo tablespace, change the default undo tablespace :  
 

SQL> alter system set undo_tablespace=NEW_UNDOTS; 
13. Then we can drop the OLD Undo tablespace :    

SQL> drop tablespace OLD_UNDOTS including contents and datafiles;

2 comments:

  1. This is outstanding in every way. Thank you Rashad

    ReplyDelete
  2. Recovering the data base
    At Genex DBS data loss is only temporary and we prove it every day with the highest data recovery success rate in the industry. Our volume of recovered data speaks for itself.
    For over 17 years, Data Storage Solutions has performed professional data recovery on every kind of storage device including Desktop hard drive data recovery, Laptop hard drives data recovery, External / USB hard disc Data Recovery / Hard Disc Recovery, Server and RAID arrays data recovery, NAS, SAN, DAS, SSD, Encryption storage devices data recovery, Database & File Recovery, CCTV Data Recovery, Flash Card Data Recovery. and enterprise-level devices like RAIDs. We have proprietary tools to handle every kind of data loss situation on any server including physical and mechanical failure, backup failure, water and fire damage, data corruption, file deletion, head crash, system failure, and more. We conduct all our data recoveries using software and solutions that won’t further damage your device.Genexdbs Database

    ReplyDelete