Oracle Forensics : RMAN RECOVERY SCENERIO (RMAN-03002, RMAN-06053,RMAN-06025, ORA-01152, ORA-01110)

ISSUE ERRORS:

RMAN-03002: failure of recover command at 03/08/2017 16:34:37

RMAN-06053: unable to perform media recovery because of missing log

RMAN-06025: no backup of archived log for thread 1 with sequence 312 and starting SCN of 5155205 found to restore

ORA-01152: file 1 was not restored from a sufficiently old backup

ORA-01110: data file 1: 'C:\APP\ASHWANIK\ORADATA\DB11G\SYSTEM01.DBF'

RMAN> list backup;

List of Backup Sets

===================

BS Key Size Device Type Elapsed Time Completion Time

------- ---------- ----------- ------------ ---------------

76 285.00K DISK 00:00:01 07-MAR-17

BP Key: 76 Status: AVAILABLE Compressed: NO Tag: TAG20170307T164612

Piece Name: C:\APP\ASHWANIK\FLASH_RECOVERY_AREA\DB11G\BACKUPSET\2017_03_07\O1_MF_ANNNN_TAG20170307T164612_DCX5MWTH_.BKP

List of Archived Logs in backup set 76

Thrd Seq Low SCN Low Time Next SCN Next Time

---- ------- ---------- --------- ---------- ---------

1 313 5155212 07-MAR-17 5155223 07-MAR-17

1 314 5155223 07-MAR-17 5155757 07-MAR-17

BS Key Type LV Size Device Type Elapsed Time Completion Time

------- ---- -- ---------- ----------- ------------ ---------------

77 Full 2.93G DISK 00:02:01 07-MAR-17

BP Key: 77 Status: AVAILABLE Compressed: NO Tag: TAG20170307T164614

Piece Name: C:\APP\ASHWANIK\FLASH_RECOVERY_AREA\DB11G\BACKUPSET\2017_03_07\O1_MF_NNNDF_TAG20170307T164614_DCX5MZ80_.BKP

List of Datafiles in backup set 77

File LV Type Ckp SCN Ckp Time Name

---- -- ---- ---------- --------- ----

1 Full 5155765 07-MAR-17 C:\APP\ASHWANIK\ORADATA\DB11G\SYSTEM01.DBF

2 Full 5155765 07-MAR-17 C:\APP\ASHWANIK\ORADATA\DB11G\SYSAUX01.DBF

3 Full 5155765 07-MAR-17 C:\APP\ASHWANIK\ORADATA\DB11G\UNDOTBS01.DBF

4 Full 5155765 07-MAR-17 C:\APP\ASHWANIK\ORADATA\DB11G\USERS01.DBF

5 Full 5155765 07-MAR-17 C:\APP\ASHWANIK\ORADATA\DB11G\EXAMPLE01.DBF

6 Full 5155765 07-MAR-17 C:\APP\ASHWANIK\ORADATA\DB11G\METALS.DBF

7 Full 5155765 07-MAR-17 ----- datafile information missing here--

8 Full 5155765 07-MAR-17 ----- datafile information missing here--

9 Full 5155765 07-MAR-17 ----- datafile information missing here--

BS Key Size Device Type Elapsed Time Completion Time

------- ---------- ----------- ------------ ---------------

78 25.00K DISK 00:00:01 07-MAR-17

BP Key: 78 Status: AVAILABLE Compressed: NO Tag: TAG20170307T164820

Piece Name: C:\APP\ASHWANIK\FLASH_RECOVERY_AREA\DB11G\BACKUPSET\2017_03_07\O1_MF_ANNNN_TAG20170307T164820_DCX5QX2F_.BKP

List of Archived Logs in backup set 78

Thrd Seq Low SCN Low Time Next SCN Next Time

---- ------- ---------- --------- ---------- ---------

1 315 5155757 07-MAR-17 5155845 07-MAR-17

BS Key Type LV Size Device Type Elapsed Time Completion Time

------- ---- -- ---------- ----------- ------------ ---------------

79 Full 9.55M DISK 00:00:01 07-MAR-17

BP Key: 79 Status: AVAILABLE Compressed: NO Tag: TAG20170307T164822

Piece Name: C:\APP\ASHWANIK\FLASH_RECOVERY_AREA\DB11G\AUTOBACKUP\2017_03_07\O1_MF_N_938018902_DCX5QZ3X_.BKP

Control File Included: Ckp SCN: 5155856 Ckp time: 07-MAR-17

RMAN> restore database;

Starting restore at 08-MAR-17

using channel ORA_DISK_1

skipping datafile 1; already restored to file C:\APP\ASHWANIK\ORADATA\DB11G\SYSTEM01.DBF

skipping datafile 2; already restored to file C:\APP\ASHWANIK\ORADATA\DB11G\SYSAUX01.DBF

skipping datafile 3; already restored to file C:\APP\ASHWANIK\ORADATA\DB11G\UNDOTBS01.DBF

skipping datafile 4; already restored to file C:\APP\ASHWANIK\ORADATA\DB11G\USERS01.DBF

skipping datafile 5; already restored to file C:\APP\ASHWANIK\ORADATA\DB11G\EXAMPLE01.DBF

skipping datafile 6; already restored to file C:\APP\ASHWANIK\ORADATA\DB11G\METALS.DBF

restore not done; all files read only, offline, or already restored

Finished restore at 08-MAR-17

RMAN> recover database;

Starting recover at 08-MAR-17

using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 314 is already on disk as file C:\APP\ASHWANIK\ORADATA\DB11G\REDO03.LOG

archived log for thread 1 with sequence 315 is already on disk as file C:\APP\ASHWANIK\ORADATA\DB11G\REDO02.LOG

archived log for thread 1 with sequence 316 is already on disk as file C:\APP\ASHWANIK\ORADATA\DB11G\REDO01.LOG

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of recover command at 03/08/2017 16:34:37

RMAN-06053: unable to perform media recovery because of missing log

RMAN-06025: no backup of archived log for thread 1 with sequence 312 and starting SCN of 5155205 found to restore

RMAN-06025: no backup of archived log for thread 1 with sequence 311 and starting SCN of 5155202 found to restore

RMAN-06025: no backup of archived log for thread 1 with sequence 310 and starting SCN of 5155199 found to restore

RMAN-06025: no backup of archived log for thread 1 with sequence 309 and starting SCN of 5151497 found to restore

We received above error while restoring full database. RMAN doesnot have information of datafile 7,8 and 9 that’s why we are not able to restore the database. This is because autoback enabled and as per controlfile datafile 7,8 and 9 not a part of database. So we have to restore the old controlfile first from the physical location as below:

SLOUTION: Restore controlfile from the location of autobackup as autobackup enabled in the database than restore and recover the database. there after open the database using openresetlogs:

Logging to RMAN

C:\app\ashwanik\product\11.2.0.3\dbhome_1\BIN>rman target sys/**password***@db11g

RMAN> restore controlfile from 'C:\app\ashwanik\flash_recovery_area\db11g\AUTOBACKUP\2017_03_07\O1_MF_N_938018902_DCX5QZ3X_.BKP';

Starting restore at 08-MAR-17

using channel ORA_DISK_1

channel ORA_DISK_1: restoring control file

channel ORA_DISK_1: restore complete, elapsed time: 00:00:04

output file name=C:\APP\ASHWANIK\ORADATA\DB11G\CONTROL01.CTL

output file name=C:\APP\ASHWANIK\FLASH_RECOVERY_AREA\DB11G\CONTROL02.CTL

Finished restore at 08-MAR-17

RMAN> alter database mount;

database mounted

released channel: ORA_DISK_1

RMAN> list backup;

List of Backup Sets

===================

BS Key Size Device Type Elapsed Time Completion Time

------- ---------- ----------- ------------ ---------------

76 285.00K DISK 00:00:01 07-MAR-17

BP Key: 76 Status: AVAILABLE Compressed: NO Tag: TAG20170307T164612

Piece Name: C:\APP\ASHWANIK\FLASH_RECOVERY_AREA\DB11G\BACKUPSET\2017_03_07\O1_MF_ANNNN_TAG20170307T164612_DCX5MWTH_.BKP

List of Archived Logs in backup set 76

Thrd Seq Low SCN Low Time Next SCN Next Time

---- ------- ---------- --------- ---------- ---------

1 313 5155212 07-MAR-17 5155223 07-MAR-17

1 314 5155223 07-MAR-17 5155757 07-MAR-17

BS Key Type LV Size Device Type Elapsed Time Completion Time

------- ---- -- ---------- ----------- ------------ ---------------

77 Full 2.93G DISK 00:02:01 07-MAR-17

BP Key: 77 Status: AVAILABLE Compressed: NO Tag: TAG20170307T164614

Piece Name: C:\APP\ASHWANIK\FLASH_RECOVERY_AREA\DB11G\BACKUPSET\2017_03_07\O1_MF_NNNDF_TAG20170307T164614_DCX5MZ80_.BKP

List of Datafiles in backup set 77

File LV Type Ckp SCN Ckp Time Name

---- -- ---- ---------- --------- ----

1 Full 5155765 07-MAR-17 C:\APP\ASHWANIK\ORADATA\DB11G\SYSTEM01.DBF

2 Full 5155765 07-MAR-17 C:\APP\ASHWANIK\ORADATA\DB11G\SYSAUX01.DBF

3 Full 5155765 07-MAR-17 C:\APP\ASHWANIK\ORADATA\DB11G\UNDOTBS01.DBF

4 Full 5155765 07-MAR-17 C:\APP\ASHWANIK\ORADATA\DB11G\USERS01.DBF

5 Full 5155765 07-MAR-17 C:\APP\ASHWANIK\ORADATA\DB11G\EXAMPLE01.DBF

6 Full 5155765 07-MAR-17 C:\APP\ASHWANIK\ORADATA\DB11G\METALS.DBF

7 Full 5155765 07-MAR-17 C:\APP\ASHWANIK\ORADATA\DB11G\METALS1.DBF

8 Full 5155765 07-MAR-17 C:\APP\ASHWANIK\ORADATA\DB11G\METALS2.DBF

9 Full 5155765 07-MAR-17 C:\APP\ASHWANIK\ORADATA\DB11G\USERS.DBF

BS Key Size Device Type Elapsed Time Completion Time

------- ---------- ----------- ------------ ---------------

78 25.00K DISK 00:00:01 07-MAR-17

BP Key: 78 Status: AVAILABLE Compressed: NO Tag: TAG20170307T164820

Piece Name: C:\APP\ASHWANIK\FLASH_RECOVERY_AREA\DB11G\BACKUPSET\2017_03_07\O1_MF_ANNNN_TAG20170307T164820_DCX5QX2F_.BKP

List of Archived Logs in backup set 78

Thrd Seq Low SCN Low Time Next SCN Next Time

---- ------- ---------- --------- ---------- ---------

1 315 5155757 07-MAR-17 5155845 07-MAR-17

RMAN>

RMAN> report schema;

Starting implicit crosscheck backup at 08-MAR-17

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=3 device type=DISK

Crosschecked 3 objects

Finished implicit crosscheck backup at 08-MAR-17

Starting implicit crosscheck copy at 08-MAR-17

using channel ORA_DISK_1

Finished implicit crosscheck copy at 08-MAR-17

searching for all files in the recovery area

cataloging files...

cataloging done

List of Cataloged Files

=======================

File Name: C:\APP\ASHWANIK\FLASH_RECOVERY_AREA\DB11G\AUTOBACKUP\2017_03_07\O1_MF_N_938018902_DCX5QZ3X_.BKP

File Name: C:\APP\ASHWANIK\FLASH_RECOVERY_AREA\DB11G\BACKUPSET\2017_03_08\O1_MF_NNNDF_TAG20170308T162134_DCZRKWYJ_.BKP

RMAN-06139: WARNING: control file is not current for REPORT SCHEMA

Report of database schema for database with db_unique_name DB11G

List of Permanent Datafiles

===========================

File Size(MB) Tablespace RB segs Datafile Name

---- -------- -------------------- ------- ------------------------

1 0 SYSTEM *** C:\APP\ASHWANIK\ORADATA\DB11G\SYSTEM01.DBF

2 0 SYSAUX *** C:\APP\ASHWANIK\ORADATA\DB11G\SYSAUX01.DBF

3 0 UNDOTBS1 *** C:\APP\ASHWANIK\ORADATA\DB11G\UNDOTBS01.DBF

4 0 USERS *** C:\APP\ASHWANIK\ORADATA\DB11G\USERS01.DBF

5 0 EXAMPLE *** C:\APP\ASHWANIK\ORADATA\DB11G\EXAMPLE01.DBF

6 0 METALS *** C:\APP\ASHWANIK\ORADATA\DB11G\METALS.DBF

7 0 METALS *** C:\APP\ASHWANIK\ORADATA\DB11G\METALS1.DBF

8 0 METALS *** C:\APP\ASHWANIK\ORADATA\DB11G\METALS2.DBF

9 0 USERS *** C:\APP\ASHWANIK\ORADATA\DB11G\USERS.DBF

List of Temporary Files

=======================

File Size(MB) Tablespace Maxsize(MB) Tempfile Name

---- -------- -------------------- ----------- --------------------

1 20 TEMP 32767 C:\APP\ASHWANIK\ORADATA\DB11G\TEMP01.DBF

Now we can see the datafiles 7, 8 and 9

RMAN> restore database;

Starting restore at 08-MAR-17

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 00007 to C:\APP\ASHWANIK\ORADATA\DB11G\METALS1.DBF

channel ORA_DISK_1: restoring datafile 00008 to C:\APP\ASHWANIK\ORADATA\DB11G\METALS2.DBF

channel ORA_DISK_1: restoring datafile 00009 to C:\APP\ASHWANIK\ORADATA\DB11G\USERS.DBF

channel ORA_DISK_1: reading from backup piece C:\APP\ASHWANIK\FLASH_RECOVERY_AREA\DB11G\BACKUPSET\2017_03_07\O1_MF_NNNDF_TAG20170307T164614_DCX5MZ80_.BKP

channel ORA_DISK_1: piece handle=C:\APP\ASHWANIK\FLASH_RECOVERY_AREA\DB11G\BACKUPSET\2017_03_07\O1_MF_NNNDF_TAG20170307T164614_DCX5MZ80_.BKP tag=TAG20170307T164614

channel ORA_DISK_1: restored backup piece 1

channel ORA_DISK_1: restore complete, elapsed time: 00:00:03

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 C:\APP\ASHWANIK\ORADATA\DB11G\SYSTEM01.DBF

channel ORA_DISK_1: restoring datafile 00002 to C:\APP\ASHWANIK\ORADATA\DB11G\SYSAUX01.DBF

channel ORA_DISK_1: restoring datafile 00003 to C:\APP\ASHWANIK\ORADATA\DB11G\UNDOTBS01.DBF

channel ORA_DISK_1: restoring datafile 00004 to C:\APP\ASHWANIK\ORADATA\DB11G\USERS01.DBF

channel ORA_DISK_1: restoring datafile 00005 to C:\APP\ASHWANIK\ORADATA\DB11G\EXAMPLE01.DBF

channel ORA_DISK_1: restoring datafile 00006 to C:\APP\ASHWANIK\ORADATA\DB11G\METALS.DBF

channel ORA_DISK_1: reading from backup piece C:\APP\ASHWANIK\FLASH_RECOVERY_AREA\DB11G\BACKUPSET\2017_03_08\O1_MF_NNNDF_TAG20170308T162134_DCZRKWYJ_.BKP

channel ORA_DISK_1: piece handle=C:\APP\ASHWANIK\FLASH_RECOVERY_AREA\DB11G\BACKUPSET\2017_03_08\O1_MF_NNNDF_TAG20170308T162134_DCZRKWYJ_.BKP tag=TAG20170308T162134

channel ORA_DISK_1: restored backup piece 1

channel ORA_DISK_1: restore complete, elapsed time: 00:04:06

Finished restore at 08-MAR-17

RMAN> recover database;

Starting recover at 08-MAR-17

using channel ORA_DISK_1

starting media recovery

channel ORA_DISK_1: starting archived log restore to default destination

channel ORA_DISK_1: restoring archived log

archived log thread=1 sequence=315

channel ORA_DISK_1: reading from backup piece C:\APP\ASHWANIK\FLASH_RECOVERY_AREA\DB11G\BACKUPSET\2017_03_07\O1_MF_ANNNN_TAG20170307T164820_DCX5QX2F_.BKP

channel ORA_DISK_1: piece handle=C:\APP\ASHWANIK\FLASH_RECOVERY_AREA\DB11G\BACKUPSET\2017_03_07\O1_MF_ANNNN_TAG20170307T164820_DCX5QX2F_.BKP tag=TAG20170307T164820

channel ORA_DISK_1: restored backup piece 1

channel ORA_DISK_1: restore complete, elapsed time: 00:00:01

archived log file name=C:\APP\ASHWANIK\FLASH_RECOVERY_AREA\DB11G\ARCHIVELOG\2017_03_08\O1_MF_1_315_DCZVLOPY_.ARC thread=1 sequence=315

channel default: deleting archived log(s)

archived log file name=C:\APP\ASHWANIK\FLASH_RECOVERY_AREA\DB11G\ARCHIVELOG\2017_03_08\O1_MF_1_315_DCZVLOPY_.ARC RECID=314 STAMP=938106797

unable to find archived log

archived log thread=1 sequence=316

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of recover command at 03/08/2017 17:13:22

RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 316 and starting SCN of 5155845

If you get above error than use below command.

RMAN> recover database noredo;

Starting recover at 08-MAR-17

using channel ORA_DISK_1

Finished recover at 08-MAR-17

RMAN> alter database open resetlogs;

database opened

Verify the Database:

SQL> select name, open_mode, database_role from v$database;

NAME OPEN_MODE DATABASE_ROLE

--------- -------------------- ----------------

DB11G OPEN PRIMARY

Oracle Forensics

Total Pageviews

Wednesday, March 8, 2017

RMAN RECOVERY SCENERIO (RMAN-03002, RMAN-06053,RMAN-06025, ORA-01152, ORA-01110)

No comments:

Post a Comment