RMAN> recover database from service ora12c noredo using compressed backupset;
using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=619 device type=DISK skipping datafile 5; already restored to SCN 1439827 skipping datafile 6; already restored to SCN 1439827 skipping datafile 8; already restored to SCN 1439827 channel ORA_DISK_1: starting incremental datafile backup set restore channel ORA_DISK_1: using compressed network backup set from service ora12c destination for restore of datafile 00001: /u01/app/oracle/oradata/ORA12C_DG/datafile/o1_mf_system_gfnobxo0_.dbf channel ORA_DISK_1: restore complete, elapsed time: 00:00:15 channel ORA_DISK_1: starting incremental datafile backup set restore channel ORA_DISK_1: using compressed network backup set from service ora12c destination for restore of datafile 00003: /u01/app/oracle/oradata/ORA12C_DG/datafile/o1_mf_sysaux_gfnoc19w_.dbf channel ORA_DISK_1: restore complete, elapsed time: 00:00:16 channel ORA_DISK_1: starting incremental datafile backup set restore channel ORA_DISK_1: using compressed network backup set from service ora12c destination for restore of datafile 00004: /u01/app/oracle/oradata/ORA12C_DG/datafile/o1_mf_undotbs1_gfnod4tg_.dbf channel ORA_DISK_1: restore complete, elapsed time: 00:00:08 channel ORA_DISK_1: starting incremental datafile backup set restore channel ORA_DISK_1: using compressed network backup set from service ora12c destination for restore of datafile 00007: /u01/app/oracle/oradata/ORA12C_DG/datafile/o1_mf_users_gfnog41g_.dbf channel ORA_DISK_1: restore complete, elapsed time: 00:00:03 channel ORA_DISK_1: starting incremental datafile backup set restore channel ORA_DISK_1: using compressed network backup set from service ora12c destination for restore of datafile 00009: /u01/app/oracle/oradata/ORA12C_DG/86DF3648F8F79A5AE0536892A8C00070/datafile/o1_mf_system_gfnog8nr_.dbf channel ORA_DISK_1: restore complete, elapsed time: 00:00:07 channel ORA_DISK_1: starting incremental datafile backup set restore channel ORA_DISK_1: using compressed network backup set from service ora12c destination for restore of datafile 00010: /u01/app/oracle/oradata/ORA12C_DG/86DF3648F8F79A5AE0536892A8C00070/datafile/o1_mf_sysaux_gfnogcrs_.dbf channel ORA_DISK_1: restore complete, elapsed time: 00:00:07 channel ORA_DISK_1: starting incremental datafile backup set restore channel ORA_DISK_1: using compressed network backup set from service ora12c destination for restore of datafile 00011: /u01/app/oracle/oradata/ORA12C_DG/86DF3648F8F79A5AE0536892A8C00070/datafile/o1_mf_undotbs1_gfnogmbq_.dbf channel ORA_DISK_1: restore complete, elapsed time: 00:00:08 channel ORA_DISK_1: starting incremental datafile backup set restore channel ORA_DISK_1: using compressed network backup set from service ora12c destination for restore of datafile 00012: /u01/app/oracle/oradata/ORA12C_DG/86DF3648F8F79A5AE0536892A8C00070/datafile/o1_mf_users_gfnoh25p_.dbf channel ORA_DISK_1: restore complete, elapsed time: 00:00:03 channel ORA_DISK_1: starting incremental datafile backup set restore channel ORA_DISK_1: using compressed network backup set from service ora12c destination for restore of datafile 00043: /u01/app/oracle/oradata/ORA12C_DG/8838793F133511C4E0536892A8C01690/datafile/o1_mf_system_gfnohkg4_.dbf channel ORA_DISK_1: restore complete, elapsed time: 00:00:07 channel ORA_DISK_1: starting incremental datafile backup set restore channel ORA_DISK_1: using compressed network backup set from service ora12c destination for restore of datafile 00044: /u01/app/oracle/oradata/ORA12C_DG/8838793F133511C4E0536892A8C01690/datafile/o1_mf_sysaux_gfnohrhf_.dbf channel ORA_DISK_1: restore complete, elapsed time: 00:00:08 channel ORA_DISK_1: starting incremental datafile backup set restore channel ORA_DISK_1: using compressed network backup set from service ora12c destination for restore of datafile 00045: /u01/app/oracle/oradata/ORA12C_DG/8838793F133511C4E0536892A8C01690/datafile/o1_mf_undotbs1_gfnoj06c_.dbf channel ORA_DISK_1: restore complete, elapsed time: 00:00:16
List of Files Which Were Not Cataloged ======================================= File Name: /u01/app/oracle/oradata/ORA12C_DG/onlinelog/o1_mf_1_gcxbtmj1_.log RMAN-07529: Reason: catalog is not supported for this file type File Name: /u01/app/oracle/oradata/ORA12C_DG/onlinelog/o1_mf_2_gcxbtnfs_.log RMAN-07529: Reason: catalog is not supported for this file type File Name: /u01/app/oracle/oradata/ORA12C_DG/onlinelog/o1_mf_3_gcxbtopn_.log RMAN-07529: Reason: catalog is not supported for this file type File Name: /u01/app/oracle/oradata/ORA12C_DG/onlinelog/o1_mf_4_gcxbtq2x_.log RMAN-07529: Reason: catalog is not supported for this file type File Name: /u01/app/oracle/oradata/ORA12C_DG/onlinelog/o1_mf_5_gcxbtsh9_.log RMAN-07529: Reason: catalog is not supported for this file type File Name: /u01/app/oracle/oradata/ORA12C_DG/onlinelog/o1_mf_6_gcxbttm1_.log RMAN-07529: Reason: catalog is not supported for this file type File Name: /u01/app/oracle/oradata/ORA12C_DG/onlinelog/o1_mf_7_gcxbtvgx_.log RMAN-07529: Reason: catalog is not supported for this file type File Name: /u01/app/oracle/oradata/ORA12C_DG/onlinelog/o1_mf_1_gfnojotd_.log RMAN-07529: Reason: catalog is not supported for this file type File Name: /u01/app/oracle/oradata/ORA12C_DG/onlinelog/o1_mf_2_gfnojtn0_.log RMAN-07529: Reason: catalog is not supported for this file type File Name: /u01/app/oracle/oradata/ORA12C_DG/onlinelog/o1_mf_3_gfnojzln_.log RMAN-07529: Reason: catalog is not supported for this file type File Name: /u01/app/oracle/oradata/ORA12C_DG/onlinelog/o1_mf_4_gfnok35r_.log RMAN-07529: Reason: catalog is not supported for this file type File Name: /u01/app/oracle/oradata/ORA12C_DG/onlinelog/o1_mf_5_gfnok7fj_.log RMAN-07529: Reason: catalog is not supported for this file type File Name: /u01/app/oracle/oradata/ORA12C_DG/onlinelog/o1_mf_6_gfnol2wo_.log RMAN-07529: Reason: catalog is not supported for this file type File Name: /u01/app/oracle/oradata/ORA12C_DG/onlinelog/o1_mf_7_gfnolyr2_.log RMAN-07529: Reason: catalog is not supported for this file type File Name: /u01/app/oracle/oradata/ORA12C_DG/8835360767C07549E0536892A8C06926/datafile/o1_mf_system_gdzxf5td_.dbf RMAN-07519: Reason: Error while cataloging. See alert.log. File Name: /u01/app/oracle/oradata/ORA12C_DG/8835360767C07549E0536892A8C06926/datafile/o1_mf_sysaux_gdzxf5tf_.dbf RMAN-07519: Reason: Error while cataloging. See alert.log. File Name: /u01/app/oracle/oradata/ORA12C_DG/8835360767C07549E0536892A8C06926/datafile/o1_mf_undotbs1_gdzxf5tg_.dbf RMAN-07519: Reason: Error while cataloging. See alert.log. File Name: /u01/app/oracle/oradata/ORA12C_DG/8835360767C07549E0536892A8C06926/datafile/o1_mf_temp_gdzxmo4z_.tmp RMAN-07518: Reason: Foreign database file DBID: 980071166 Database Name: CDB1
Switch to cataloged copy
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16
RMAN> SWITCH DATABASE TO COPY;
datafile 1 switched to datafile copy "/u01/app/oracle/oradata/ORA12C_DG/datafile/o1_mf_system_gfnobxo0_.dbf" datafile 3 switched to datafile copy "/u01/app/oracle/oradata/ORA12C_DG/datafile/o1_mf_sysaux_gfnoc19w_.dbf" datafile 4 switched to datafile copy "/u01/app/oracle/oradata/ORA12C_DG/datafile/o1_mf_undotbs1_gfnod4tg_.dbf" datafile 5 switched to datafile copy "/u01/app/oracle/oradata/ORA12C_DG/86DF1439DC9B8E60E0536892A8C0ECA0/datafile/o1_mf_system_gfnodmhj_.dbf" datafile 6 switched to datafile copy "/u01/app/oracle/oradata/ORA12C_DG/86DF1439DC9B8E60E0536892A8C0ECA0/datafile/o1_mf_sysaux_gcxbqyr5_.dbf" datafile 7 switched to datafile copy "/u01/app/oracle/oradata/ORA12C_DG/datafile/o1_mf_users_gfnog41g_.dbf" datafile 8 switched to datafile copy "/u01/app/oracle/oradata/ORA12C_DG/86DF1439DC9B8E60E0536892A8C0ECA0/datafile/o1_mf_undotbs1_gfnog77f_.dbf" datafile 9 switched to datafile copy "/u01/app/oracle/oradata/ORA12C_DG/86DF3648F8F79A5AE0536892A8C00070/datafile/o1_mf_system_gfnog8nr_.dbf" datafile 10 switched to datafile copy "/u01/app/oracle/oradata/ORA12C_DG/86DF3648F8F79A5AE0536892A8C00070/datafile/o1_mf_sysaux_gfnogcrs_.dbf" datafile 11 switched to datafile copy "/u01/app/oracle/oradata/ORA12C_DG/86DF3648F8F79A5AE0536892A8C00070/datafile/o1_mf_undotbs1_gfnogmbq_.dbf" datafile 12 switched to datafile copy "/u01/app/oracle/oradata/ORA12C_DG/86DF3648F8F79A5AE0536892A8C00070/datafile/o1_mf_users_gfnoh25p_.dbf" datafile 43 switched to datafile copy "/u01/app/oracle/oradata/ORA12C_DG/8838793F133511C4E0536892A8C01690/datafile/o1_mf_system_gfnohkg4_.dbf" datafile 44 switched to datafile copy "/u01/app/oracle/oradata/ORA12C_DG/8838793F133511C4E0536892A8C01690/datafile/o1_mf_sysaux_gfnohrhf_.dbf" datafile 45 switched to datafile copy "/u01/app/oracle/oradata/ORA12C_DG/8838793F133511C4E0536892A8C01690/datafile/o1_mf_undotbs1_gfnoj06c_.dbf"
如果在备库进行recover from service的过程中,主库新增了数据文件,那么需要单独将这个文件恢复到备库,也就是之前步骤查询的数据库SCN号
1 2 3 4 5 6 7 8 9 10 11
SELECT file# FROM V$DATAFILE WHERE creation_change# >= v$database.current_scn(STANDBY);
-- NON-CATALOG: RUN { SET NEWNAME FOR DATABASE TO '/u01/app/oracle/oradata/ORA12C_DG'; RESTORE DATAFILE XX FROM SERVICE ORA12C; }
-- CATALOG: RESTORE DATAFILE XX FROM SERVICE ORA12C;
更新备库控制文件中redo日志文件的名称,这里直接clear备库日志,等下RMAN会自动重建
1 2 3 4 5 6 7
alter database clear logfile group 1; alter database clear logfile group 2; alter database clear logfile group 3; alter database clear logfile group 4; alter database clear logfile group 5; alter database clear logfile group 6; alter database clear logfile group 7;
Starting recover at 22-MAY-19 using channel ORA_DISK_1
starting media recovery
archived log for thread 1 with sequence 175 is already on disk as file /u01/arch/ora12c/1_175_1006450609.dbf archived log for thread 1 with sequence 176 is already on disk as file /u01/arch/ora12c/1_176_1006450609.dbf archived log for thread 1 with sequence 177 is already on disk as file /u01/arch/ora12c/1_177_1006450609.dbf archived log file name=/u01/arch/ora12c/1_175_1006450609.dbf thread=1 sequence=175 archived log file name=/u01/arch/ora12c/1_176_1006450609.dbf thread=1 sequence=176 archived log file name=/u01/arch/ora12c/1_177_1006450609.dbf thread=1 sequence=177 media recovery complete, elapsed time: 00:00:59 Finished recover at 22-MAY-19
RMAN> ALTER DATABASE OPEN READ ONLY;
Statement processed
DGMGRL> edit database ora12c_dg set state='apply-on'; Succeeded. DGMGRL> show configuration
specification does not match any backup in the repository
-- 当前并没有可用备份,对CDB和它的PDBs做一个全备
RMAN> backup database plus archivelog;
Starting backup at 26-MAR-19 current log archived using channel ORA_DISK_1 channel ORA_DISK_1: starting archived log backup set channel ORA_DISK_1: specifying archived log(s) in backup set input archived log thread=1 sequence=3 RECID=1 STAMP=1002984384 input archived log thread=1 sequence=4 RECID=2 STAMP=1002984802 input archived log thread=1 sequence=5 RECID=3 STAMP=1003929462 channel ORA_DISK_1: starting piece 1 at 26-MAR-19 channel ORA_DISK_1: finished piece 1 at 26-MAR-19 piece handle=/u01/app/oracle/product/12.2.0/dbhome_1/dbs/02ttdfrm_1_1 tag=TAG20190326T131742 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03 Finished backup at 26-MAR-19
Starting backup at 26-MAR-19 using channel ORA_DISK_1 channel ORA_DISK_1: starting full datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set input datafile file number=00001 name=/u01/app/oracle/oradata/ora12c/system01.dbf input datafile file number=00003 name=/u01/app/oracle/oradata/ora12c/sysaux01.dbf input datafile file number=00004 name=/u01/app/oracle/oradata/ora12c/undotbs01.dbf input datafile file number=00007 name=/u01/app/oracle/oradata/ora12c/users01.dbf channel ORA_DISK_1: starting piece 1 at 26-MAR-19 channel ORA_DISK_1: finished piece 1 at 26-MAR-19 piece handle=/u01/app/oracle/product/12.2.0/dbhome_1/dbs/03ttdfrp_1_1 tag=TAG20190326T131745 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15 channel ORA_DISK_1: starting full datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set input datafile file number=00010 name=/u01/app/oracle/oradata/ora12c/pdb12c/sysaux01.dbf input datafile file number=00009 name=/u01/app/oracle/oradata/ora12c/pdb12c/system01.dbf input datafile file number=00011 name=/u01/app/oracle/oradata/ora12c/pdb12c/undotbs01.dbf input datafile file number=00013 name=/u01/app/oracle/oradata/ora12c/pdb12c/ORA12C/83F09DB5C9E42BE4E0556597A8E5428B/datafile/o1_mf_tbs1_g8kd5pwo_.dbf input datafile file number=00015 name=/u01/app/oracle/oradata/ora12c/pdb12c/ORA12C/83F09DB5C9E42BE4E0556597A8E5428B/datafile/o1_mf_tbs2_g8pl709m_.dbf input datafile file number=00012 name=/u01/app/oracle/oradata/ora12c/pdb12c/users01.dbf channel ORA_DISK_1: starting piece 1 at 26-MAR-19 channel ORA_DISK_1: finished piece 1 at 26-MAR-19 piece handle=/u01/app/oracle/product/12.2.0/dbhome_1/dbs/04ttdfs8_1_1 tag=TAG20190326T131745 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03 channel ORA_DISK_1: starting full datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set input datafile file number=00006 name=/u01/app/oracle/oradata/ora12c/pdbseed/sysaux01.dbf input datafile file number=00005 name=/u01/app/oracle/oradata/ora12c/pdbseed/system01.dbf input datafile file number=00008 name=/u01/app/oracle/oradata/ora12c/pdbseed/undotbs01.dbf channel ORA_DISK_1: starting piece 1 at 26-MAR-19 channel ORA_DISK_1: finished piece 1 at 26-MAR-19 piece handle=/u01/app/oracle/product/12.2.0/dbhome_1/dbs/05ttdfsc_1_1 tag=TAG20190326T131745 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:07 Finished backup at 26-MAR-19
Starting backup at 26-MAR-19 current log archived using channel ORA_DISK_1 channel ORA_DISK_1: starting archived log backup set channel ORA_DISK_1: specifying archived log(s) in backup set input archived log thread=1 sequence=6 RECID=4 STAMP=1003929491 channel ORA_DISK_1: starting piece 1 at 26-MAR-19 channel ORA_DISK_1: finished piece 1 at 26-MAR-19 piece handle=/u01/app/oracle/product/12.2.0/dbhome_1/dbs/06ttdfsj_1_1 tag=TAG20190326T131811 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01 Finished backup at 26-MAR-19
Starting Control File and SPFILE Autobackup at 26-MAR-19 piece handle=/u01/app/oracle/product/12.2.0/dbhome_1/dbs/c-380590835-20190326-00 comment=NONE Finished Control File and SPFILE Autobackup at 26-MAR-19
构建测试表
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15
drop table t1 purge;
create table t1 (id number); insert into t1 values(1); commit;
-- 检查当前scn xb@PDB12C> select current_scn from v$database;
sql statement: alter database mount clone database
sql statement: alter system archive log current
contents of Memory Script: { # set requested point in time set until scn 1612170; # set destinations for recovery set and auxiliary set datafiles set newname for clone datafile 1 to new; set newname for clone datafile 9 to new; set newname for clone datafile 4 to new; set newname for clone datafile 11 to new; set newname for clone datafile 3 to new; set newname for clone datafile 10 to new; set newname for clone tempfile 1 to new; set newname for clone tempfile 3 to new; # switch all tempfiles switch clone tempfile all; # restore the tablespaces in the recovery set and the auxiliary set restore clone datafile 1, 9, 4, 11, 3, 10;
renamed tempfile 1 to /u01/aux/ORA12C/datafile/o1_mf_temp_%u_.tmp in control file renamed tempfile 3 to /u01/aux/ORA12C/83F09DB5C9E42BE4E0556597A8E5428B/datafile/o1_mf_temp_%u_.tmp in control file
Starting restore at 26-MAR-19 using channel ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: starting datafile backup set restore channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set channel ORA_AUX_DISK_1: restoring datafile 00001 to /u01/aux/ORA12C/datafile/o1_mf_system_%u_.dbf channel ORA_AUX_DISK_1: restoring datafile 00004 to /u01/aux/ORA12C/datafile/o1_mf_undotbs1_%u_.dbf channel ORA_AUX_DISK_1: restoring datafile 00003 to /u01/aux/ORA12C/datafile/o1_mf_sysaux_%u_.dbf channel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/product/12.2.0/dbhome_1/dbs/03ttdfrp_1_1 channel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/product/12.2.0/dbhome_1/dbs/03ttdfrp_1_1 tag=TAG20190326T131745 channel ORA_AUX_DISK_1: restored backup piece 1 channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:15 channel ORA_AUX_DISK_1: starting datafile backup set restore channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set channel ORA_AUX_DISK_1: restoring datafile 00009 to /u01/aux/ORA12C/83F09DB5C9E42BE4E0556597A8E5428B/datafile/o1_mf_system_%u_.dbf channel ORA_AUX_DISK_1: restoring datafile 00011 to /u01/aux/ORA12C/83F09DB5C9E42BE4E0556597A8E5428B/datafile/o1_mf_undotbs1_%u_.dbf channel ORA_AUX_DISK_1: restoring datafile 00010 to /u01/aux/ORA12C/83F09DB5C9E42BE4E0556597A8E5428B/datafile/o1_mf_sysaux_%u_.dbf channel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/product/12.2.0/dbhome_1/dbs/04ttdfs8_1_1 channel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/product/12.2.0/dbhome_1/dbs/04ttdfs8_1_1 tag=TAG20190326T131745 channel ORA_AUX_DISK_1: restored backup piece 1 channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:07 Finished restore at 26-MAR-19
contents of Memory Script: { # set requested point in time set until scn 1612170; # online the datafiles restored or switched sql clone "alter database datafile 1 online"; sql clone 'PDB12C' "alter database datafile 9 online"; sql clone "alter database datafile 4 online"; sql clone 'PDB12C' "alter database datafile 11 online"; sql clone "alter database datafile 3 online"; sql clone 'PDB12C' "alter database datafile 10 online"; # recover and open database read only recover clone database tablespace "SYSTEM", "PDB12C":"SYSTEM", "UNDOTBS1", "PDB12C":"UNDOTBS1", "SYSAUX", "PDB12C":"SYSAUX"; sql clone 'alter database open read only'; } executing Memory Script
executing command: SET until clause
sql statement: alter database datafile 1 online
sql statement: alter database datafile 9 online
sql statement: alter database datafile 4 online
sql statement: alter database datafile 11 online
sql statement: alter database datafile 3 online
sql statement: alter database datafile 10 online
Starting recover at 26-MAR-19 using channel ORA_AUX_DISK_1
starting media recovery
archived log for thread 1 with sequence 6 is already on disk as file /u01/arch/1_6_1002791605.dbf archived log for thread 1 with sequence 7 is already on disk as file /u01/arch/1_7_1002791605.dbf archived log file name=/u01/arch/1_6_1002791605.dbf thread=1 sequence=6 archived log file name=/u01/arch/1_7_1002791605.dbf thread=1 sequence=7 media recovery complete, elapsed time: 00:00:00 Finished recover at 26-MAR-19
sql statement: alter database open read only
contents of Memory Script: { sql clone 'alter pluggable database PDB12C open read only'; } executing Memory Script
sql statement: alter pluggable database PDB12C open read only
contents of Memory Script: { sql clone "create spfile from memory"; shutdown clone immediate; startup clone nomount; sql clone "alter system set control_files = ''/u01/aux/ORA12C/controlfile/o1_mf_g9mfwx6s_.ctl'' comment= ''RMAN set'' scope=spfile"; shutdown clone immediate; startup clone nomount; # mount database sql clone 'alter database mount clone database'; } executing Memory Script
sql statement: create spfile from memory
database closed database dismounted Oracle instance shut down
connected to auxiliary database (not started) Oracle instance started
sql statement: alter database mount clone database
contents of Memory Script: { # set requested point in time set until scn 1612170; # set destinations for recovery set and auxiliary set datafiles set newname for datafile 13 to new; # restore the tablespaces in the recovery set and the auxiliary set restore clone datafile 13;
contents of Memory Script: { # set requested point in time set until scn 1612170; # online the datafiles restored or switched sql clone 'PDB12C' "alter database datafile 13 online"; # recover and open resetlogs recover clone database tablespace "PDB12C":"TBS1", "SYSTEM", "PDB12C":"SYSTEM", "UNDOTBS1", "PDB12C":"UNDOTBS1", "SYSAUX", "PDB12C":"SYSAUX" delete archivelog; alter clone database open resetlogs; } executing Memory Script
executing command: SET until clause
sql statement: alter database datafile 13 online
Starting recover at 26-MAR-19 using channel ORA_AUX_DISK_1
starting media recovery
archived log for thread 1 with sequence 6 is already on disk as file /u01/arch/1_6_1002791605.dbf archived log for thread 1 with sequence 7 is already on disk as file /u01/arch/1_7_1002791605.dbf archived log file name=/u01/arch/1_6_1002791605.dbf thread=1 sequence=6 archived log file name=/u01/arch/1_7_1002791605.dbf thread=1 sequence=7 media recovery complete, elapsed time: 00:00:00 Finished recover at 26-MAR-19
Oracle Error: 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: '+DG/xxx/datafile/system01.dbf'
16:54:07 sys. >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: '+DG/xxx/datafile/system01.dbf'
sys >select (select distinct checkpoint_change# from v$datafile) dfile_ckp,(select checkpoint_change# from v$database) db_ckp,(select distinct checkpoint_change# from v$datafile_header) dh_ckp from dual;
set numwidth 30; set pagesize 50000; alter session set nls_date_format = 'DD-MON-RRRR HH24:MI:SS';
sys.>select status,checkpoint_change#,checkpoint_time, resetlogs_change#, resetlogs_time, count(*), fuzzy from v$datafile_header group by status,checkpoint_change#,checkpoint_time, resetlogs_change#, resetlogs_time, fuzzy;
GROUP# STATUS TYPE MEMBER IS_ ---------- ------- ------- ---------------------------------------------------------------------- --- 3 ONLINE +REDO/xxx/redo03.log NO 2 ONLINE +REDO/xxx/redo02.log NO 1 ONLINE +REDO/xxx/redo01.log NO 4 STANDBY +REDO/xxx/stdredo01.log NO 5 STANDBY +REDO/xxx/stdredo02.log NO 6 STANDBY +REDO/xxx/stdredo03.log NO 7 STANDBY +REDO/xxx/stdredo04.log NO 10 ONLINE +REDO/xxx/redo10.log NO 11 ONLINE +REDO/xxx/redo11.log NO
ALTER DATABASE RENAME FILE '/home/oradata/xxx/redo03.log' to '+REDO/xxx/redo03.log'; ALTER DATABASE RENAME FILE '/home/oradata/xxx/redo02.log' to '+REDO/xxx/redo02.log'; ALTER DATABASE RENAME FILE '/home/oradata/xxx/redo01.log' to '+REDO/xxx/redo01.log';
ALTER DATABASE RENAME FILE '/home/oradata/xxx/stdredo01.log' to '+REDO/xxx/stdredo01.log'; ALTER DATABASE RENAME FILE '/home/oradata/xxx/stdredo02.log' to '+REDO/xxx/stdredo02.log'; ALTER DATABASE RENAME FILE '/home/oradata/xxx/stdredo03.log' to '+REDO/xxx/stdredo03.log'; ALTER DATABASE RENAME FILE '/home/oradata/xxx/stdredo04.log' to '+REDO/xxx/stdredo04.log'; ALTER DATABASE RENAME FILE '/home/oradata/xxx/redo10.log' to '+REDO/xxx/redo10.log'; ALTER DATABASE RENAME FILE '/home/oradata/xxx/redo11.log' to '+REDO/xxx/redo11.log';
22:52:18 sys. xxx>RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL ; ORA-00279: change 75238614094 generated at 02/23/2019 00:15:46 needed for thread 1 ORA-00289: suggestion : +NVMEDG/xxx/archivelog/2019_03_01/thread_1_seq_21201.664.1001796429 ORA-00280: change 75238614094 for thread 1 is in sequence #21201
22:52:35 Spexxxy log: {>RET>=suggested | filename | AUTO | CANCEL} +NVMEDG/xxx/archivelog/2019_03_01/thread_1_seq_21201.664.1001796429 ORA-00279: change 75238624057 generated at 02/23/2019 00:19:25 needed for thread 1 ORA-00289: suggestion : +NVMEDG/xxx/archivelog/2019_03_01/thread_1_seq_21202.663.1001796179 ORA-00280: change 75238624057 for thread 1 is in sequence #21202 ORA-00278: log file '+NVMEDG/xxx/archivelog/2019_03_01/thread_1_seq_21201.664.1001796429' no longer needed for this recovery
22:52:49 Spexxxy log: {>RET>=suggested | filename | AUTO | CANCEL} +NVMEDG/xxx/archivelog/2019_03_01/thread_1_seq_21202.663.1001796179 ORA-00279: change 75238632711 generated at 02/23/2019 00:22:37 needed for thread 1 ORA-00289: suggestion : +nvmedg ORA-00280: change 75238632711 for thread 1 is in sequence #21203 ORA-00278: log file '+NVMEDG/xxx/archivelog/2019_03_01/thread_1_seq_21202.663.1001796179' no longer needed for this recovery
22:53:03 Spexxxy log: {>RET>=suggested | filename | AUTO | CANCEL} >>>>====缺少进一步的日志用于恢复 auto ORA-00308: cannot open archived log '+nvmedg' ORA-17503: ksfdopn:2 Failed to open file +nvmedg ORA-15045: ASM file name '+nvmedg' is not in reference form
ORA-00308: cannot open archived log '+nvmedg' ORA-17503: ksfdopn:2 Failed to open file +nvmedg ORA-15045: ASM file name '+nvmedg' is not in reference form
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: '+NVMEDG/xxx/datafile/system01.dbf'
当我恢复完21201和21202两个文件以后,发现依然无法打开数据库。
1 2 3 4 5 6
sys. >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: '+DG/xxx/datafile/system01.dbf'
sys >select (select distinct checkpoint_change# from v$datafile) dfile_ckp,(select checkpoint_change# from v$database) db_ckp,(select distinct checkpoint_change# from v$datafile_header) dh_ckp from dual;
set numwidth 30; set pagesize 50000; alter session set nls_date_format = 'DD-MON-RRRR HH24:MI:SS';
select status,checkpoint_change#,checkpoint_time, resetlogs_change#, resetlogs_time, count(*), fuzzy from v$datafile_header group by status,checkpoint_change#,checkpoint_time, resetlogs_change#, resetlogs_time, fuzzy;
alter database open resetlogs; alter database open resetlogs * ERROR at line 1: ORA-01139: RESETLOGS option only valid after an incomplete database recovery
alter database open; alter database open * ERROR at line 1: ORA-03113: end-of-file on communication channel Process ID: 97239 Session ID: 1921 Serial number: 1
内存脚本的内容: { # set requested point in time set until logseq 122 thread 1; # restore the controlfile restore clone controlfile; # mount the controlfile sql clone 'alter database mount clone database'; # archive current online log sql 'alter system archive log current'; # avoid unnecessary autobackups for structural changes during TSPITR sql 'begin dbms_backup_restore.AutoBackupFlag(FALSE); end;'; } 正在执行内存脚本
sql 语句: begin dbms_backup_restore.AutoBackupFlag(FALSE); end;
内存脚本的内容: { # set requested point in time set until logseq 122 thread 1; # set destinations for recovery set and auxiliary set datafiles set newname for clone datafile 1 to new; set newname for clone datafile 3 to new; set newname for clone datafile 2 to new; set newname for clone tempfile 1 to new; set newname for datafile 5 to "/u01/app/oracle/oradata/xb/tbs_xb01.dbf"; # switch all tempfiles switch clone tempfile all; # restore the tablespaces in the recovery set and the auxiliary set restore clone datafile 1, 3, 2, 5; switch clone datafile all; } 正在执行内存脚本
内存脚本的内容: { # make read only the tablespace that will be exported sql clone 'alter tablespace TBS_XB read only'; # create directory for datapump import sql "create or replace directory TSPITR_DIROBJ_DPDIR as '' /u01/app/oracle/oradata/''"; # create directory for datapump export sql clone "create or replace directory TSPITR_DIROBJ_DPDIR as '' /u01/app/oracle/oradata/''"; } 正在执行内存脚本
sql 语句: alter tablespace TBS_XB read only
sql 语句: create or replace directory TSPITR_DIROBJ_DPDIR as ''/u01/app/oracle/oradata/''
sql 语句: create or replace directory TSPITR_DIROBJ_DPDIR as ''/u01/app/oracle/oradata/''
SYS@xb> select tablespace_name,status,plugged_in from dba_tablespaces;
TABLESPACE_NAME STATUS PLU ------------------------------ --------- --- SYSTEM ONLINE NO SYSAUX ONLINE NO UNDOTBS1 ONLINE NO TEMP ONLINE NO USERS ONLINE NO TBS_XB OFFLINE YES >>>>====表示plug