After the restore, I got "ORA-01113: file 1 needs media recovery" error when using "alter database open;" to open database. Then, I ran followings to open the database:
SQL> recover database;
Media recovery complete.
SQL> alter database open;
Database altered.
The above actions uses redo logs to recover the database. After the database was up, we found the mistakes that users made on 2/20/08 were still in the database. Even the alert log says all data files were restored I was not sure if the redo logs brought the mistakes in, or if the RMAN restore failed or not because there are no timestamps on files in ASM and there are no additional files created in ASM by the restore job.
I had to restore the database again using a backup on an earlier date, and used "recover database until cancel using backup controlfile;" to recover the database after reading Metalink Note 272321.1. Below are what I did to restore the database successfully in 2nd run.
-- Find the backup
RMAN> list backup of database completed between 'sysdate-9' and 'sysdate-5';
-- Preview
RMAN> run {
2> ALLOCATE CHANNEL ch00 TYPE 'SBT_TAPE';
3> ALLOCATE CHANNEL ch01 TYPE 'SBT_TAPE';
4> ALLOCATE CHANNEL ch02 TYPE 'SBT_TAPE';
5> ALLOCATE CHANNEL ch03 TYPE 'SBT_TAPE';
6> restore database from tag=TAG20080216T233131 preview;
7> RELEASE CHANNEL ch00;
8> RELEASE CHANNEL ch01;
9> RELEASE CHANNEL ch02;
10> RELEASE CHANNEL ch03;
11> }
allocated channel: ch00
channel ch00: sid=317 devtype=SBT_TAPE
channel ch00: VERITAS NetBackup for Oracle - Release 6.0 (2006110304)
allocated channel: ch01
channel ch01: sid=316 devtype=SBT_TAPE
channel ch01: VERITAS NetBackup for Oracle - Release 6.0 (2006110304)
allocated channel: ch02
channel ch02: sid=315 devtype=SBT_TAPE
channel ch02: VERITAS NetBackup for Oracle - Release 6.0 (2006110304)
allocated channel: ch03
channel ch03: sid=314 devtype=SBT_TAPE
channel ch03: VERITAS NetBackup for Oracle - Release 6.0 (2006110304)
Starting restore at 25-FEB-08
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
2239936 Incr 0 39.01G SBT_TAPE 00:31:57 16-FEB-08
BP Key: 2239943 Status: AVAILABLE Compressed: NO Tag: TAG20080216T233131
Handle: bk_cpj8t1uq_1_1_646875098 Media: PH0313
List of Datafiles in backup set 2239936
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 0 Incr 32529107158 16-FEB-08 +DATA/dbrep/datafile/system.270.630660291
5 0 Incr 32529107158 16-FEB-08 +DATA/dbrep/datafile/cis_data_1
7 0 Incr 32529107158 16-FEB-08 +DATA/dbrep/datafile/dev_user_1
13 0 Incr 32529107158 16-FEB-08 +DATA/dbrep/datafile/dss_data_3
14 0 Incr 32529107158 16-FEB-08 +DATA/dbrep/datafile/dss_idx_1
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
2239937 Incr 0 44.25G SBT_TAPE 00:33:54 16-FEB-08
BP Key: 2239944 Status: AVAILABLE Compressed: NO Tag: TAG20080216T233131
Handle: bk_cnj8t1uq_1_1_646875098 Media: X228L2
List of Datafiles in backup set 2239937
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
6 0 Incr 32529107158 16-FEB-08 +DATA/dbrep/datafile/cis_idx_1
10 0 Incr 32529107158 16-FEB-08 +DATA/dbrep/datafile/law_data_1
11 0 Incr 32529107158 16-FEB-08 +DATA/dbrep/datafile/dss_data_1
16 0 Incr 32529107158 16-FEB-08 +DATA/dbrep/datafile/dss_idx_3
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
2239938 Incr 0 43.63G SBT_TAPE 00:35:33 16-FEB-08
BP Key: 2239945 Status: AVAILABLE Compressed: NO Tag: TAG20080216T233131
Handle: bk_coj8t1uq_1_1_646875098 Media: EX0213
List of Datafiles in backup set 2239938
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
3 0 Incr 32529107158 16-FEB-08 +DATA/dbrep/datafile/sysaux.272.630660299
9 0 Incr 32529107158 16-FEB-08 +DATA/dbrep/datafile/pdw_idx_1
12 0 Incr 32529107158 16-FEB-08 +DATA/dbrep/datafile/dss_data_2
15 0 Incr 32529107158 16-FEB-08 +DATA/dbrep/datafile/dss_idx_2
18 0 Incr 32529107158 16-FEB-08 +DATA/dbrep/datafile/symantec_i3_orcl.dbf
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
2239939 Incr 0 45.18G SBT_TAPE 00:36:11 16-FEB-08
BP Key: 2239946 Status: AVAILABLE Compressed: NO Tag: TAG20080216T233131
Handle: bk_cmj8t1uq_1_1_646875098 Media: X441L2
List of Datafiles in backup set 2239939
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
2 0 Incr 32529107158 16-FEB-08 +DATA/dbrep/datafile/undotbs1.271.630660297
4 0 Incr 32529107158 16-FEB-08 +DATA/dbrep/datafile/users.274.630660303
8 0 Incr 32529107158 16-FEB-08 +DATA/dbrep/datafile/pdw_data_1
17 0 Incr 32529107158 16-FEB-08 +DATA/dbrep/datafile/dss_data_4
19 0 Incr 32529107158 16-FEB-08 +DATA/dbrep/datafile/dss_idx_4
archive logs generated after SCN 32529107158 not found in repository
Media recovery start SCN is 32529107158
Recovery must be done beyond SCN 32529107158 to clear data files fuzziness
Finished restore at 25-FEB-08
released channel: ch00
released channel: ch01
released channel: ch02
released channel: ch03
-- Restore
RMAN> run {
2> ALLOCATE CHANNEL ch00 TYPE 'SBT_TAPE';
3> ALLOCATE CHANNEL ch01 TYPE 'SBT_TAPE';
4> ALLOCATE CHANNEL ch02 TYPE 'SBT_TAPE';
5> ALLOCATE CHANNEL ch03 TYPE 'SBT_TAPE';
6> restore database from tag=TAG20080216T233131;
7> RELEASE CHANNEL ch00;
8> RELEASE CHANNEL ch01;
9> RELEASE CHANNEL ch02;
10> RELEASE CHANNEL ch03;
11> }
allocated channel: ch00
channel ch00: sid=317 devtype=SBT_TAPE
channel ch00: VERITAS NetBackup for Oracle - Release 6.0 (2006110304)
allocated channel: ch01
channel ch01: sid=316 devtype=SBT_TAPE
channel ch01: VERITAS NetBackup for Oracle - Release 6.0 (2006110304)
allocated channel: ch02
channel ch02: sid=315 devtype=SBT_TAPE
channel ch02: VERITAS NetBackup for Oracle - Release 6.0 (2006110304)
allocated channel: ch03
channel ch03: sid=314 devtype=SBT_TAPE
channel ch03: VERITAS NetBackup for Oracle - Release 6.0 (2006110304)
Starting restore at 25-FEB-08
channel ch00: starting datafile backupset restore
channel ch00: specifying datafile(s) to restore from backup set
restoring datafile 00001 to +DATA/dbrep/datafile/system.270.630660291
restoring datafile 00005 to +DATA/dbrep/datafile/cis_data_1
restoring datafile 00007 to +DATA/dbrep/datafile/dev_user_1
restoring datafile 00013 to +DATA/dbrep/datafile/dss_data_3
restoring datafile 00014 to +DATA/dbrep/datafile/dss_idx_1
channel ch00: reading from backup piece bk_cpj8t1uq_1_1_646875098
channel ch01: starting datafile backupset restore
channel ch01: specifying datafile(s) to restore from backup set
restoring datafile 00006 to +DATA/dbrep/datafile/cis_idx_1
restoring datafile 00010 to +DATA/dbrep/datafile/law_data_1
restoring datafile 00011 to +DATA/dbrep/datafile/dss_data_1
restoring datafile 00016 to +DATA/dbrep/datafile/dss_idx_3
channel ch01: reading from backup piece bk_cnj8t1uq_1_1_646875098
channel ch02: starting datafile backupset restore
channel ch02: specifying datafile(s) to restore from backup set
restoring datafile 00003 to +DATA/dbrep/datafile/sysaux.272.630660299
restoring datafile 00009 to +DATA/dbrep/datafile/pdw_idx_1
restoring datafile 00012 to +DATA/dbrep/datafile/dss_data_2
restoring datafile 00015 to +DATA/dbrep/datafile/dss_idx_2
restoring datafile 00018 to +DATA/dbrep/datafile/symantec_i3_orcl.dbf
channel ch02: reading from backup piece bk_coj8t1uq_1_1_646875098
channel ch03: starting datafile backupset restore
channel ch03: specifying datafile(s) to restore from backup set
restoring datafile 00002 to +DATA/dbrep/datafile/undotbs1.271.630660297
restoring datafile 00004 to +DATA/dbrep/datafile/users.274.630660303
restoring datafile 00008 to +DATA/dbrep/datafile/pdw_data_1
restoring datafile 00017 to +DATA/dbrep/datafile/dss_data_4
restoring datafile 00019 to +DATA/dbrep/datafile/dss_idx_4
channel ch03: reading from backup piece bk_cmj8t1uq_1_1_646875098
channel ch00: restored backup piece 1
piece handle=bk_cpj8t1uq_1_1_646875098 tag=TAG20080216T233131
channel ch00: restore complete, elapsed time: 00:21:57
channel ch01: restored backup piece 1
piece handle=bk_cnj8t1uq_1_1_646875098 tag=TAG20080216T233131
channel ch01: restore complete, elapsed time: 00:26:32
channel ch02: restored backup piece 1
piece handle=bk_coj8t1uq_1_1_646875098 tag=TAG20080216T233131
channel ch02: restore complete, elapsed time: 00:31:57
channel ch03: restored backup piece 1
piece handle=bk_cmj8t1uq_1_1_646875098 tag=TAG20080216T233131
channel ch03: restore complete, elapsed time: 00:32:22
Finished restore at 25-FEB-08
released channel: ch00
released channel: ch01
released channel: ch02
released channel: ch03
RMAN>
-- Bring the database up in SQL*Plus
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: '+DATA/dbrep/datafile/system.270.630660291'
SQL> recover database until cancel using backup controlfile;
ORA-00279: change 32529107158 generated at 02/16/2008 23:30:33 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/product/10.2.0/db_1/dbs/arch1_490_630660282.dbf
ORA-00280: change 32529107158 for thread 1 is in sequence #490
Specify log: {
cancel
Media recovery cancelled.
SQL> alter database open resetlogs;
Database altered.
No comments:
Post a Comment