Tuesday, November 03, 2009

Just Fun with RMAN Recovery...

My Test Database (ARCHIVELOG MODE) lost:
Tue Nov 03 20:12:13 2009
ALTER DATABASE OPEN
This instance was first to open
Tue Nov 03 20:12:13 2009
Errors in file /oracle/product/diag/rdbms/orcl/orcl/trace/orcl_dbw0_3372.trc:
ORA-01157: cannot identify/lock data file 1 - see DBWR trace file
ORA-01110: data file 1: '+DISK01/orcl/datafile/system.267.696964505'
ORA-17503: ksfdopn:2 Failed to open file +DISK01/orcl/datafile/system.267.696964505
ORA-15012: ASM file '+DISK01/orcl/datafile/system.267.696964505' does not exist
Errors in file /oracle/product/diag/rdbms/orcl/orcl/trace/orcl_dbw0_3372.trc:
ORA-01157: cannot identify/lock data file 2 - see DBWR trace file
ORA-01110: data file 2: '+DISK01/orcl/datafile/sysaux.266.696964509'
ORA-17503: ksfdopn:2 Failed to open file +DISK01/orcl/datafile/sysaux.266.696964509
ORA-15012: ASM file '+DISK01/orcl/datafile/sysaux.266.696964509' does not exist
Errors in file /oracle/product/diag/rdbms/orcl/orcl/trace/orcl_dbw0_3372.trc:
ORA-01157: cannot identify/lock data file 3 - see DBWR trace file
ORA-01110: data file 3: '+DISK01/orcl/datafile/undotbs1.265.701210003'
ORA-17503: ksfdopn:2 Failed to open file +DISK01/orcl/datafile/undotbs1.265.701210003
ORA-15012: ASM file '+DISK01/orcl/datafile/undotbs1.265.701210003' does not exist
It's time to test(RMAN) database recovery (From BACKUPSET).
Workaround:
run{
startup nomount;
restore controlfile;
alter database mount;
restore database;
recover database;
alter database open resetlogs;
}
But It could not "resetlogs"... and alert log showed:
alter database open resetlogs
Errors in file /oracle/product/diag/rdbms/orcl/orcl/trace/orcl_ora_5509.trc:
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '+DISK02/orcl/onlinelog/group_1.491.696964811'
ORA-17503: ksfdopn:2 Failed to open file +DISK02/orcl/onlinelog/group_1.491.696964811
ORA-15012: ASM file '+DISK02/orcl/onlinelog/group_1.491.696964811' does not exist
ORA-00312: online log 1 thread 1: '+DISK01/orcl/onlinelog/group_1.262.696964787'
ORA-17503: ksfdopn:2 Failed to open file +DISK01/orcl/onlinelog/group_1.262.696964787
ORA-15012: ASM file '+DISK01/orcl/onlinelog/group_1.262.696964787' does not exist
Perhaps I made something wrong, so i check last SCN(backupset) in catalog and recovery "until scn" :
$ rman target / catalog rman/password@catalog
RMAN> list backup
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
422 Incr 4 760.00K DISK 00:01:58 03-NOV-09
BP Key: 425 Status: AVAILABLE Compressed: NO Tag: TAG20091103T174313
Piece Name: /oracle/RMAN/0uktekpi_1_1
List of Datafiles in backup set 422
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 4 Incr 3626382 03-NOV-09 +DISK01/orcl/datafile/system.267.696964505
2 4 Incr 3626382 03-NOV-09 +DISK01/orcl/datafile/sysaux.266.696964509
3 4 Incr 3626382 03-NOV-09 +DISK01/orcl/datafile/undotbs1.265.701210003
4 4 Incr 3626382 03-NOV-09 +DISK01/orcl/datafile/users01.dbf
5 4 Incr 3626382 03-NOV-09 +DISK01/orcl/datafile/example.259.696964945
6 4 Incr 3626382 03-NOV-09 +DISK01/orcl/datafile/tbs1.256.697633229
7 4 Incr 3626382 03-NOV-09 +DISK01/orcl/datafile/tbs_fda.257.701208791
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
443 Full 17.95M DISK 00:00:06 03-NOV-09
BP Key: 451 Status: AVAILABLE Compressed: NO Tag: TAG20091103T174521
Piece Name: +DISK02/orcl/autobackup/2009_11_03/s_701977521.358.701977527
SPFILE Included: Modification time: 03-NOV-09
SPFILE db_unique_name: ORCL
Control File Included: Ckp SCN: 3626526 Ckp time: 03-NOV-09
RMAN> shutdown

database dismounted
Oracle instance shut down
RMAN> startup nomount;

connected to target database (not started)
Oracle instance started

Total System Global Area 810053632 bytes

Fixed Size 2217712 bytes
Variable Size 645925136 bytes
Database Buffers 125829120 bytes
Redo Buffers 36081664 bytes
RMAN> restore controlfile from autobackup;

Starting restore at 03-NOV-09
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=14 device type=DISK

recovery area destination: +DISK02
database name (or database unique name) used for search: ORCL
channel ORA_DISK_1: AUTOBACKUP +disk02/ORCL/AUTOBACKUP/2009_11_03/s_701977521.358.701977527 found in the recovery area
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20091103
channel ORA_DISK_1: restoring control file from AUTOBACKUP +disk02/ORCL/AUTOBACKUP/2009_11_03/s_701977521.358.701977527
channel ORA_DISK_1: control file restore from AUTOBACKUP complete
output file name=+DISK01/orcl/controlfile/current.263.696964775
output file name=+DISK02/orcl/controlfile/current.492.696964779
Finished restore at 03-NOV-09
RMAN> alter database mount;

Database altered.

RMAN> run {
set until scn 3626382;
restore database;
recover database;
alter database open resetlogs;
}

executing command: SET until clause

Starting restore at 03-NOV-09
Starting implicit crosscheck backup at 03-NOV-09
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=13 device type=DISK
Crosschecked 22 objects
Finished implicit crosscheck backup at 03-NOV-09

Starting implicit crosscheck copy at 03-NOV-09
using channel ORA_DISK_1
Crosschecked 1 objects
Finished implicit crosscheck copy at 03-NOV-09

searching for all files in the recovery area
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: +disk02/ORCL/AUTOBACKUP/2009_11_03/s_701977521.358.701977527

using channel ORA_DISK_1

skipping datafile 4; already restored to file +DISK01/orcl/datafile/users01.dbf
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 +DISK01/orcl/datafile/system.267.696964505
channel ORA_DISK_1: restoring datafile 00002 to +DISK01/orcl/datafile/sysaux.266.696964509
channel ORA_DISK_1: restoring datafile 00003 to +DISK01/orcl/datafile/undotbs1.265.701210003
channel ORA_DISK_1: restoring datafile 00005 to +DISK01/orcl/datafile/example.259.696964945
channel ORA_DISK_1: restoring datafile 00006 to +DISK01/orcl/datafile/tbs1.256.697633229
channel ORA_DISK_1: restoring datafile 00007 to +DISK01/orcl/datafile/tbs_fda.257.701208791
channel ORA_DISK_1: reading from backup piece /oracle/RMAN/0sktekgc_1_1
channel ORA_DISK_1: piece handle=/oracle/RMAN/0sktekgc_1_1 tag=TAG20091103T173819
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:03:48
Finished restore at 03-NOV-09

Starting recover at 03-NOV-09
using channel ORA_DISK_1
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00001: +DISK01/orcl/datafile/system.272.701988391
destination for restore of datafile 00002: +DISK01/orcl/datafile/sysaux.273.701988393
destination for restore of datafile 00003: +DISK01/orcl/datafile/undotbs1.276.701988395
destination for restore of datafile 00004: +DISK01/orcl/datafile/users01.dbf
destination for restore of datafile 00005: +DISK01/orcl/datafile/example.275.701988395
destination for restore of datafile 00006: +DISK01/orcl/datafile/tbs1.274.701988395
destination for restore of datafile 00007: +DISK01/orcl/datafile/tbs_fda.277.701988395
channel ORA_DISK_1: reading from backup piece /oracle/RMAN/0uktekpi_1_1
channel ORA_DISK_1: piece handle=/oracle/RMAN/0uktekpi_1_1 tag=TAG20091103T174313
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07

starting media recovery
media recovery complete, elapsed time: 00:00:03

Finished recover at 03-NOV-09

database opened
new incarnation of database registered in recovery catalog
starting full resync of recovery catalog
full resync complete
Just fun with RMAN (If A database has backup)... ;)

2 comments:

Anonymous said...

how about

run{
startup nomount;
restore controlfile;
alter database mount;
restore database;
recover database noredo;
alter database open resetlogs;
}


leon

Surachart said...

After I reseted log, I found:
But It could not "resetlogs"... and alert log showed:

alter database open resetlogs
Errors in file /oracle/product/diag/rdbms/orcl/orcl/trace/orcl_ora_5509.trc:
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '+DISK02/orcl/onlinelog/group_1.491.696964811'
ORA-17503: ksfdopn:2 Failed to open file +DISK02/orcl/onlinelog/group_1.491.696964811
ORA-15012: ASM file '+DISK02/orcl/onlinelog/group_1.491.696964811' does not exist
ORA-00312: online log 1 thread 1: '+DISK01/orcl/onlinelog/group_1.262.696964787'
ORA-17503: ksfdopn:2 Failed to open file +DISK01/orcl/onlinelog/group_1.262.696964787
ORA-15012: ASM file '+DISK01/orcl/onlinelog/group_1.262.696964787' does not exist

Perhaps I made something wrong...
So, I used... set until scn... to help