Recover database from a backup taken before a RESETLOGS

Prior to Oracle 10g, the redo log files generated after opening the database with RESETLOGS
could not be used with the backups taken before the RESETLOGS was performed.

Therefore, whenever a resetlogs was done, it was important to take an immediate full database
backup, since all previous backups became invalid.

A RESETLOGS needs to be performed when we need to do the following activities
1)    Do a point in time recovery
2)    Recover a database using a backup of the control file

What does a RESETLOGS do?

•    Archives the current online redo logs (if they are accessible) and then erases the contents
    of the online redo logs and resets the log sequence number to 1.
•    Creates the online redo log files if they do not currently exist.
•    Updates all current datafiles and online redo logs and all subsequent archived redo logs
    with a new RESETLOGS SCN and time stamp.
   
Scenario: Here i'm trying to recover my database using a backup which was taken before resetlogs
option.


[oracle@server1 prim]$ sqlplus /  as sysdba
SQL> select group#, member from v$logfile;

    GROUP#            MEMBER
--------------------------------------------------------------------------------
        3            /u01/app/oracle/oradata/prim/redo03.log

        2            /u01/app/oracle/oradata/prim/redo02.log

        1            /u01/app/oracle/oradata/prim/redo01.log
       
SQL> !
[oracle@server1 prim]$ cd /u01/app/oracle/oradata/prim
[oracle@server1 prim]$ ls -ltrh
total 1.5G
-rw-r-----. 1 oracle oinstall  21M Jan  8 22:22 temp01.dbf
-rw-r-----. 1 oracle oinstall  51M Jan  8 23:09 redo02.log
-rw-r-----. 1 oracle oinstall  51M Jan  8 23:09 redo03.log
-rw-r-----. 1 oracle oinstall 101M Jan  8 23:09 example01.dbf
-rw-r-----. 1 oracle oinstall 5.1M Jan  8 23:14 users01.dbf
-rw-r-----. 1 oracle oinstall  56M Jan  8 23:25 undotbs01.dbf
-rw-r-----. 1 oracle oinstall 671M Jan  8 23:25 system01.dbf
-rw-r-----. 1 oracle oinstall 501M Jan  8 23:25 sysaux01.dbf
-rw-r-----. 1 oracle oinstall  51M Jan  8 23:26 redo01.log
-rw-r-----. 1 oracle oinstall 9.3M Jan  8 23:27 control01.ctl

Before dropping the redologs lets take a fresh backup of database.
RMAN> backup database plus archivelog;
RMAN> exit

Now we will manually remove the redologs.
[oracle@server1 prim]$ cd /u01/app/oracle/oradata/prim
[oracle@server1 prim]$ rm -rf *.log

Here's the output in alertlog file:-
[oracle@server1 u01]$ tail -100f /u01/app/oracle/diag/rdbms/prim/prim/trace/alert_prim.log
Thu Jan 08 23:44:12 2015
Thread 1 advanced to log sequence 6 (LGWR switch)
  Current log# 3 seq# 6 mem# 0: /u01/app/oracle/oradata/prim/redo03.log
Thu Jan 08 23:44:12 2015
Errors in file /u01/app/oracle/diag/rdbms/prim/prim/trace/prim_arc1_8374.trc:
ORA-00313: open failed for members of log group 2 of thread 1
ORA-00312: online log 2 thread 1: '/u01/app/oracle/oradata/prim/redo02.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Thu Jan 08 23:44:12 2015       

SQL> shut abort;
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area  413372416 bytes
Fixed Size                  2213896 bytes
Variable Size             322963448 bytes
Database Buffers           83886080 bytes
Redo Buffers                4308992 bytes
Database mounted.

Now we will recover the database until last log sequence

[oracle@server1 prim]$ rman target /
RMAN> run {
 set until logseq=6 thread=1; 
 restore database;
 recover database;
 }
executing command: SET until clause

Starting restore at 08-JAN-15
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=18 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /u01/app/oracle/oradata/prim/system01.dbf
channel ORA_DISK_1: restoring datafile 00002 to /u01/app/oracle/oradata/prim/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/prim/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/prim/users01.dbf
channel ORA_DISK_1: restoring datafile 00005 to /u01/app/oracle/oradata/prim/example01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/flash_recovery_area/PRIM/backupset/2015_01_08/o1_mf_nnndf_TAG20150108T233907_bbxky3k1_.bkp
channel ORA_DISK_1: piece handle=/u01/app/oracle/flash_recovery_area/PRIM/backupset/2015_01_08/o1_mf_nnndf_TAG20150108T233907_bbxky3k1_.bkp tag=TAG20150108T233907
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:36
Finished restore at 08-JAN-15

Starting recover at 08-JAN-15
using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 4 is already on disk as file /u01/app/oracle/flash_recovery_area/PRIM/archivelog/2015_01_08/o1_mf_1_4_bbxkztt9_.arc
archived log file name=/u01/app/oracle/flash_recovery_area/PRIM/archivelog/2015_01_08/o1_mf_1_4_bbxkztt9_.arc thread=1 sequence=4
unable to find archived log
archived log thread=1 sequence=5
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 01/08/2015 23:51:10
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 5 and starting SCN of 1034128

Here we can ignore the above error. its generated cause Oracle does not stop recovery at the
last available archive log in the database backupsets as per the restored control file. It keeps
on requesting for "next available archive log sequence number (5)".
In fact, there is no log sequence 5 recorded in the restored control file.

Now open the database using resetlogs option.
RMAN> alter database open resetlogs;

Now lets insert some data into a schema.
[oracle@server1 prim]$ sqlplus soumya/soumya

SQL> create table t3 (id number);
Table created.
SQL> insert into t3 values(1);
1 row created.
SQL> /
1 row created.
SQL> /
1 row created.
SQL> commit;
Commit complete.
SQL> exit

Now lets do a few log switches.

[oracle@server1 prim]$ sqlplus /  as sysdba
SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
SQL> /   
SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     4
Next log sequence to archive   6
Current log sequence           6
SQL>

Now lets remove the redolog files once again.
NOTE: No Backup has been taken after opening the database with RESETLOGS option.
[oracle@server1 prim]$ cd /u01/app/oracle/oradata/prim
[oracle@server1 prim]$ rm -rf *.log

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

Total System Global Area  413372416 bytes
Fixed Size                  2213896 bytes
Variable Size             327157752 bytes
Database Buffers           79691776 bytes
Redo Buffers                4308992 bytes
Database mounted.
ORA-03113: end-of-file on communication channel
Process ID: 9221
Session ID: 1 Serial number: 5
SQL> exit

[oracle@server1 prim]$ rman target /
RMAN> startup mount

Oracle instance started
database mounted

Total System Global Area     413372416 bytes

Fixed Size                     2213896 bytes
Variable Size                327157752 bytes
Database Buffers              79691776 bytes
Redo Buffers                   4308992 bytes

RMAN> list incarnation of database;

using target database control file instead of recovery catalog

List of Database Incarnations
DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1       1       PRIM     4130009889       PARENT  1          15-AUG-09
2       2       PRIM     4130009889       PARENT  945184     24-JAN-14
3       3       PRIM     4130009889       CURRENT 1034129    08-JAN-15

Now we have to recover the database once again until the log sequence no 6

RMAN> run {
2> set until sequence=6 thread =1;
3> restore database;
4> recover database;
5>  }

executing command: SET until clause

Starting restore at 09-JAN-15
using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /u01/app/oracle/oradata/prim/system01.dbf
channel ORA_DISK_1: restoring datafile 00002 to /u01/app/oracle/oradata/prim/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/prim/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/prim/users01.dbf
channel ORA_DISK_1: restoring datafile 00005 to /u01/app/oracle/oradata/prim/example01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/flash_recovery_area/PRIM/backupset/2015_01_08/o1_mf_nnndf_TAG20150108T233907_bbxky3k1_.bkp
channel ORA_DISK_1: piece handle=/u01/app/oracle/flash_recovery_area/PRIM/backupset/2015_01_08/o1_mf_nnndf_TAG20150108T233907_bbxky3k1_.bkp tag=TAG20150108T233907
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:35
Finished restore at 09-JAN-15

Starting recover at 09-JAN-15
using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 4 is already on disk as file /u01/app/oracle/flash_recovery_area/PRIM/archivelog/2015_01_08/o1_mf_1_4_bbxkztt9_.arc
archived log for thread 1 with sequence 1 is already on disk as file /u01/app/oracle/flash_recovery_area/PRIM/archivelog/2015_01_09/o1_mf_1_1_bbxmmqq9_.arc
archived log for thread 1 with sequence 2 is already on disk as file /u01/app/oracle/flash_recovery_area/PRIM/archivelog/2015_01_09/o1_mf_1_2_bbxn97nj_.arc
archived log for thread 1 with sequence 3 is already on disk as file /u01/app/oracle/flash_recovery_area/PRIM/archivelog/2015_01_09/o1_mf_1_3_bbxn98kg_.arc
archived log for thread 1 with sequence 4 is already on disk as file /u01/app/oracle/flash_recovery_area/PRIM/archivelog/2015_01_09/o1_mf_1_4_bbxn99oh_.arc
archived log for thread 1 with sequence 5 is already on disk as file /u01/app/oracle/flash_recovery_area/PRIM/archivelog/2015_01_09/o1_mf_1_5_bbxn9dmp_.arc
archived log file name=/u01/app/oracle/flash_recovery_area/PRIM/archivelog/2015_01_08/o1_mf_1_4_bbxkztt9_.arc thread=1 sequence=4
archived log file name=/u01/app/oracle/flash_recovery_area/PRIM/archivelog/2015_01_09/o1_mf_1_1_bbxmmqq9_.arc thread=1 sequence=1
archived log file name=/u01/app/oracle/flash_recovery_area/PRIM/archivelog/2015_01_09/o1_mf_1_2_bbxn97nj_.arc thread=1 sequence=2
archived log file name=/u01/app/oracle/flash_recovery_area/PRIM/archivelog/2015_01_09/o1_mf_1_3_bbxn98kg_.arc thread=1 sequence=3
archived log file name=/u01/app/oracle/flash_recovery_area/PRIM/archivelog/2015_01_09/o1_mf_1_4_bbxn99oh_.arc thread=1 sequence=4
archived log file name=/u01/app/oracle/flash_recovery_area/PRIM/archivelog/2015_01_09/o1_mf_1_5_bbxn9dmp_.arc thread=1 sequence=5
media recovery complete, elapsed time: 00:00:02
Finished recover at 09-JAN-15

RMAN> alter database open resetlogs;
database opened

RMAN> quit
[oracle@server1 prim]$ sqlplus soumya/soumya
SQL> select * from t3;

        ID
----------
         1
         1
         1
       
Now we can see all data recoved until point of failure.        







Nenhum comentário:

Postar um comentário

Related Posts Plugin for WordPress, Blogger...