Oracle 10g,跨Resetlogs时间点恢复
2024-08-29 13:45:45
供稿：网友
在Oracle10g以前，在进行了不完全恢复使用resetlogs选项打开数据库以后，Oracle建议你要立即进行全备份。 因为日志序号会被置位，以防止后续日志被应用。resetlogs之前的备份将不能用于进行跨域resetlogs时间点的恢复。 在Oracle10g中，Oracle答应跨越resetlogs时间点进行完全/不完全恢复。 看一下测试的例子: 1.全备份数据库 $ rman target / Recovery Manager: Release 10.1.0.2.0 - 64bit PRodUCtion Copyright (c) 1995, 2004, Oracle. All rights reserved. connected to target database: EYGLE (DBID=1337390772) RMAN> backup database plus archivelog delete all input; Starting backup at 2005-04-05 16:29:08current log archivedusing target database controlfile instead of recovery catalogallocated channel: ORA_DISK_1channel ORA_DISK_1: sid=143 devtype=DISKchannel ORA_DISK_1: starting archive log backupsetchannel ORA_DISK_1: specifying archive log(s) in backup setinput archive log thread=1 sequence=3 recid=294 stamp=554823466input archive log thread=1 sequence=4 recid=295 stamp=554828647input archive log thread=1 sequence=5 recid=297 stamp=554833749channel ORA_DISK_1: starting piece 1 at 2005-04-05 16:29:16channel ORA_DISK_1: finished piece 1 at 2005-04-05 16:29:19piece handle=/data5/Flash_recovery_area/EYGLE/backupset/2005_04_05/o1_mf_annnn_TAG20050405T162914_154m2woc_.bkp comment=NONEchannel ORA_DISK_1: backup set complete, elapsed time: 00:00:04channel ORA_DISK_1: deleting archive log(s)archive log filename=/data5/flash_recovery_area/EYGLE/archivelog/2005_04_05/o1_mf_1_3_154919qm_.arc recid=294 stamp=554823466archive log filename=/data5/flash_recovery_area/EYGLE/archivelog/2005_04_05/o1_mf_1_4_154g3728_.arc recid=295 stamp=554828647archive log filename=/data5/flash_recovery_area/EYGLE/archivelog/2005_04_05/o1_mf_1_5_154m2og4_.arc recid=297 stamp=554833749Finished backup at 2005-04-05 16:29:20 Starting backup at 2005-04-05 16:29:21using channel ORA_DISK_1channel ORA_DISK_1: starting full datafile backupsetchannel ORA_DISK_1: specifying datafile(s) in backupsetinput datafile fno=00009 name=/data1/oradata/systemfile/bigtbs.dbfinput datafile fno=00001 name=/opt/oracle/oradata/eygle/system01.dbfinput datafile fno=00006 name=/opt/oracle/oradata/eygle/EYGLE/datafile/o1_mf_test_03xv34ny_.dbfinput datafile fno=00002 name=/opt/oracle/oradata/eygle/undotbs01.dbfinput datafile fno=00003 name=/opt/oracle/oradata/eygle/sysaux01.dbfinput datafile fno=00010 name=/opt/oracle/oradata/eygle/dfmbrc.dbfinput datafile fno=00008 name=/opt/oracle/oradata/eygle/trans01.dbfchannel ORA_DISK_1: starting piece 1 at 2005-04-05 16:29:22channel ORA_DISK_1: finished piece 1 at 2005-04-05 16:31:07piece handle=/data5/flash_recovery_area/EYGLE/backupset/2005_04_05/o1_mf_nnndf_TAG20050405T162921_154m33kf_.bkp comment=NONEchannel ORA_DISK_1: backup set complete, elapsed time: 00:01:45channel ORA_DISK_1: starting full datafile backupsetchannel ORA_DISK_1: specifying datafile(s) in backupsetinput datafile fno=00005 name=/data1/oradata/systemfile/eygle01.dbfinput datafile fno=00004 name=/opt/oracle/oradata/eygle/users01.dbfinput datafile fno=00007 name=/opt/oracle/oradata/eygle/EYGLE/datafile/o1_mf_itpub_03xv5g66_.dbfchannel ORA_DISK_1: starting piece 1 at 2005-04-05 16:31:08channel ORA_DISK_1: finished piece 1 at 2005-04-05 16:31:33piece handle=/data5/flash_recovery_area/EYGLE/backupset/2005_04_05/o1_mf_nnndf_TAG20050405T162921_154m6dxm_.bkp comment=NONEchannel ORA_DISK_1: backup set complete, elapsed time: 00:00:25channel ORA_DISK_1: starting full datafile backupsetchannel ORA_DISK_1: specifying datafile(s) in backupsetinput datafile fno=00011 name=/opt/oracle/oradata/eygle/t2k01.dbfchannel ORA_DISK_1: starting piece 1 at 2005-04-05 16:31:34channel ORA_DISK_1: finished piece 1 at 2005-04-05 16:31:41piece handle=/data5/flash_recovery_area/EYGLE/backupset/2005_04_05/o1_mf_nnndf_TAG20050405T162921_154m76kz_.bkp comment=NONEchannel ORA_DISK_1: backup set complete, elapsed time: 00:00:07Finished backup at 2005-04-05 16:31:41 Starting backup at 2005-04-05 16:31:41current log archivedusing channel ORA_DISK_1channel ORA_DISK_1: starting archive log backupsetchannel ORA_DISK_1: specifying archive log(s) in backup setinput archive log thread=1 sequence=6 recid=298 stamp=554833902channel ORA_DISK_1: starting piece 1 at 2005-04-05 16:31:44channel ORA_DISK_1: finished piece 1 at 2005-04-05 16:31:47piece handle=/data5/flash_recovery_area/EYGLE/backupset/2005_04_05/o1_mf_annnn_TAG20050405T163142_154m7jpo_.bkp comment=NONEchannel ORA_DISK_1: backup set complete, elapsed time: 00:00:04channel ORA_DISK_1: deleting archive log(s)archive log filename=/data5/flash_recovery_area/EYGLE/archivelog/2005_04_05/o1_mf_1_6_154m7gb3_.arc recid=298 stamp=554833902Finished backup at 2005-04-05 16:31:47 Starting Control File and SPFILE Autobackup at 2005-04-05 16:31:48piece handle=/data5/flash_recovery_area/EYGLE/autobackup/2005_04_05/o1_mf_s_554833908_154m7qps_.bkp comment=NONEFinished Control File and SPFILE Autobackup at 2005-04-05 16:31:56 RMAN> exit Recovery Manager complete. 2.进行数据更改 $ sqlplus "/ as sysdba" SQL*Plus: Release 10.1.0.2.0 - Production on Tue Apr 5 16:32:23 2005 Copyright (c) 1982, 2004, Oracle. All rights reserved. Connected to:Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - 64bit ProductionWith the Partitioning, OLAP and Data Mining options SYS AS SYSDBA on 2005-04-05 16:32:23 >alter system switch logfile; System altered. SYS AS SYSDBA on 2005-04-05 16:32:30 >select count(*) from t; COUNT(*)---------- 18956 SYS AS SYSDBA on 2005-04-05 16:33:03 >insert into t select * from t; 18956 rows created. SYS AS SYSDBA on 2005-04-05 16:33:17 >commit; Commit complete. SYS AS SYSDBA on 2005-04-05 16:33:19 >alter system switch logfile; System altered. SYS AS SYSDBA on 2005-04-05 16:33:45 >truncate table t; Table truncated. SYS AS SYSDBA on 2005-04-05 16:34:58 >alter system switch logfile; System altered. 此时的日志序列情况如下: SYS AS SYSDBA on 2005-04-05 16:35:00 >select * from v$log_history where recid >1811; RECID STAMP THREAD# SEQUENCE# FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# RESETLOGS_CHANGE# RESETLOGS_TIME------ ---------- ---------- ---------- ------------- ------------------- ------------ ----------------- ------------------- 1812 554813757 1 1 10923677 2005-04-05 09:54:53 10925720 10923677 2005-04-05 09:54:53 1813 554813974 1 2 10925720 2005-04-05 10:55:57 10925827 10923677 2005-04-05 09:54:53 1814 554823464 1 3 10925827 2005-04-05 10:59:34 10947409 10923677 2005-04-05 09:54:53 1815 554828646 1 4 10947409 2005-04-05 13:37:44 10950318 10923677 2005-04-05 09:54:53 1816 554833748 1 5 10950318 2005-04-05 15:04:06 10953123 10923677 2005-04-05 09:54:53 1817 554833901 1 6 10953123 2005-04-05 16:29:08 10953187 10923677 2005-04-05 09:54:53 1818 554833950 1 7 10953187 2005-04-05 16:31:41 10953213 10923677 2005-04-05 09:54:53 1819 554834005 1 8 10953213 2005-04-05 16:32:30 10953633 10923677 2005-04-05 09:54:53 1820 554834118 1 9 10953633 2005-04-05 16:33:25 10953726 10923677 2005-04-05 09:54:53 9 rows selected. 关闭数据库:SYS AS SYSDBA on 2005-04-05 16:35:18 >shutdown immediate;Database closed.Database dismounted.ORACLE instance shut down. SYS AS SYSDBA on 2005-04-05 16:37:30 >exitDisconnected from Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - 64bit ProductionWith the Partitioning, OLAP and Data Mining options 3.执行不完全恢复$ rman target / Recovery Manager: Release 10.1.0.2.0 - 64bit Production Copyright (c) 1995, 2004, Oracle. All rights reserved. connected to target database (not started) RMAN> startup mount; Oracle instance starteddatabase mounted Total System Global Area 314572800 bytes Fixed Size 1301704 bytesVariable Size 261890872 bytesDatabase Buffers 50331648 bytesRedo Buffers 1048576 bytes RMAN> run {2> set until sequence 8 thread 1;3> restore database;4> recover database;5> } executing command: SET until clauseusing target database controlfile instead of recovery catalog Starting restore at 2005-04-05 16:39:17allocated channel: ORA_DISK_1channel ORA_DISK_1: sid=160 devtype=DISK datafile 8 not processed because file is read-onlychannel ORA_DISK_1: starting datafile backupset restorechannel ORA_DISK_1: specifying datafile(s) to restore from backup setrestoring datafile 00001 to /opt/oracle/oradata/eygle/system01.dbfrestoring datafile 00002 to /opt/oracle/oradata/eygle/undotbs01.dbfrestoring datafile 00003 to /opt/oracle/oradata/eygle/sysaux01.dbfrestoring datafile 00006 to /opt/oracle/oradata/eygle/EYGLE/datafile/o1_mf_test_03xv34ny_.dbfrestoring datafile 00009 to /data1/oradata/systemfile/bigtbs.dbfrestoring datafile 00010 to /opt/oracle/oradata/eygle/dfmbrc.dbfchannel ORA_DISK_1: restored backup piece 1piece handle=/data5/flash_recovery_area/EYGLE/backupset/2005_04_05/o1_mf_nnndf_TAG20050405T162921_154m33kf_.bkp tag=TAG20050405T162921channel ORA_DISK_1: restore completechannel ORA_DISK_1: starting datafile backupset restorechannel ORA_DISK_1: specifying datafile(s) to restore from backup setrestoring datafile 00004 to /opt/oracle/oradata/eygle/users01.dbfrestoring datafile 00005 to /data1/oradata/systemfile/eygle01.dbfrestoring datafile 00007 to /opt/oracle/oradata/eygle/EYGLE/datafile/o1_mf_itpub_03xv5g66_.dbfchannel ORA_DISK_1: restored backup piece 1piece handle=/data5/flash_recovery_area/EYGLE/backupset/2005_04_05/o1_mf_nnndf_TAG20050405T162921_154m6dxm_.bkp tag=TAG20050405T162921channel ORA_DISK_1: restore completechannel ORA_DISK_1: starting datafile backupset restorechannel ORA_DISK_1: specifying datafile(s) to restore from backup setrestoring datafile 00011 to /opt/oracle/oradata/eygle/t2k01.dbfchannel ORA_DISK_1: restored backup piece 1piece handle=/data5/flash_recovery_area/EYGLE/backupset/2005_04_05/o1_mf_nnndf_TAG20050405T162921_154m76kz_.bkp tag=TAG20050405T162921channel ORA_DISK_1: restore completeFinished restore at 2005-04-05 16:41:45 Starting recover at 2005-04-05 16:41:46using channel ORA_DISK_1datafile 8 not processed because file is offline starting media recovery archive log thread 1 sequence 7 is already on disk as file /data5/flash_recovery_area/EYGLE/archivelog/2005_04_05/o1_mf_1_7_154m8z7k_.arcchannel ORA_DISK_1: starting archive log restore to default destinationchannel ORA_DISK_1: restoring archive logarchive log thread=1 sequence=6channel ORA_DISK_1: restored backup piece 1piece handle=/data5/flash_recovery_area/EYGLE/backupset/2005_04_05/o1_mf_annnn_TAG20050405T163142_154m7jpo_.bkp tag=TAG20050405T163142channel ORA_DISK_1: restore completearchive log filename=/data5/flash_recovery_area/EYGLE/archivelog/2005_04_05/o1_mf_1_6_154mtkfk_.arc thread=1 sequence=6channel default: deleting archive log(s)archive log filename=/data5/flash_recovery_area/EYGLE/archivelog/2005_04_05/o1_mf_1_6_154mtkfk_.arc recid=302 stamp=554834513archive log filename=/data5/flash_recovery_area/EYGLE/archivelog/2005_04_05/o1_mf_1_7_154m8z7k_.arc thread=1 sequence=7media recovery completeFinished recover at 2005-04-05 16:41:57 RMAN> alter database open resetlogs; database opened RMAN> exit Recovery Manager complete. 4.此时的数据库状态$ sqlplus "/ as sysdba" SQL*Plus: Release 10.1.0.2.0 - Production on Tue Apr 5 16:43:58 2005 Copyright (c) 1982, 2004, Oracle. All rights reserved. Connected to:Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - 64bit ProductionWith the Partitioning, OLAP and Data Mining options SYS AS SYSDBA on 2005-04-05 16:43:58 >archive log list;Database log mode Archive ModeAutomatic archival EnabledArchive destination USE_DB_RECOVERY_FILE_DESTOldest online log sequence 1Next log sequence to archive 1Current log sequence 1SYS AS SYSDBA on 2005-04-05 16:44:01 >select count(*) from t; COUNT(*)---------- 18956 5.继续进行数据更改 SYS AS SYSDBA on 2005-04-05 16:44:08 >create table tt as select * from dba_users; Table created. SYS AS SYSDBA on 2005-04-05 16:45:51 >alter system switch logfile; System altered. SYS AS SYSDBA on 2005-04-05 16:45:56 >select count(*) from tt; COUNT(*)---------- 12 SYS AS SYSDBA on 2005-04-05 16:46:01 >insert into tt select * from tt; 12 rows created. SYS AS SYSDBA on 2005-04-05 16:46:13 >commit; Commit complete. SYS AS SYSDBA on 2005-04-05 16:46:16 >alter system switch logfile; System altered. 此时的日志序列: SYS AS SYSDBA on 2005-04-05 17:46:19 >select * from v$log_history where recid >1811; RECID STAMP THREAD# SEQUENCE# FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# RESETLOGS_CHANGE# RESETLOGS_TIME----- ---------- ---------- ---------- ------------- ------------------- ------------ ----------------- -------------------1812 554813757 1 1 10923677 2005-04-05 09:54:53 10925720 10923677 2005-04-05 09:54:531813 554813974 1 2 10925720 2005-04-05 10:55:57 10925827 10923677 2005-04-05 09:54:531814 554823464 1 3 10925827 2005-04-05 10:59:34 10947409 10923677 2005-04-05 09:54:531815 554828646 1 4 10947409 2005-04-05 13:37:44 10950318 10923677 2005-04-05 09:54:531816 554833748 1 5 10950318 2005-04-05 15:04:06 10953123 10923677 2005-04-05 09:54:531817 554833901 1 6 10953123 2005-04-05 16:29:08 10953187 10923677 2005-04-05 09:54:531818 554833950 1 7 10953187 2005-04-05 16:31:41 10953213 10923677 2005-04-05 09:54:531819 554834005 1 8 10953213 2005-04-05 16:32:30 10953633 10923677 2005-04-05 09:54:531820 554834118 1 9 10953633 2005-04-05 16:33:25 10953726 10923677 2005-04-05 09:54:531821 554834756 1 1 10953214 2005-04-05 16:42:34 10953433 10953214 2005-04-05 16:42:341822 554834781 1 2 10953433 2005-04-05 16:45:56 10953445 10953214 2005-04-05 16:42:34 11 rows selected. SYS AS SYSDBA on 2005-04-05 16:46:21 >shutdown immediate;Database closed.Database dismounted.ORACLE instance shut down.SYS AS SYSDBA on 2005-04-05 16:47:38 >exitDisconnected from Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - 64bit ProductionWith the Partitioning, OLAP and Data Mining options 6.再次执行恢复 $ rman target / Recovery Manager: Release 10.1.0.2.0 - 64bit Production Copyright (c) 1995, 2004, Oracle. All rights reserved. connected to target database (not started) RMAN> startup mount; Oracle instance starteddatabase mounted Total System Global Area 314572800 bytes Fixed Size 1301704 bytesVariable Size 261890872 bytesDatabase Buffers 50331648 bytesRedo Buffers 1048576 bytes RMAN> run {2> restore database;3> recover database;4> } Starting restore at 2005-04-05 16:49:54using target database controlfile instead of recovery catalogallocated channel: ORA_DISK_1channel ORA_DISK_1: sid=160 devtype=DISK datafile 8 not processed because file is read-onlychannel ORA_DISK_1: starting datafile backupset restorechannel ORA_DISK_1: specifying datafile(s) to restore from backup setrestoring datafile 00001 to /opt/oracle/oradata/eygle/system01.dbfrestoring datafile 00002 to /opt/oracle/oradata/eygle/undotbs01.dbfrestoring datafile 00003 to /opt/oracle/oradata/eygle/sysaux01.dbfrestoring datafile 00006 to /opt/oracle/oradata/eygle/EYGLE/datafile/o1_mf_test_03xv34ny_.dbfrestoring datafile 00009 to /data1/oradata/systemfile/bigtbs.dbfrestoring datafile 00010 to /opt/oracle/oradata/eygle/dfmbrc.dbfchannel ORA_DISK_1: restored backup piece 1piece handle=/data5/flash_recovery_area/EYGLE/backupset/2005_04_05/o1_mf_nnndf_TAG20050405T162921_154m33kf_.bkp tag=TAG20050405T162921channel ORA_DISK_1: restore completechannel ORA_DISK_1: starting datafile backupset restorechannel ORA_DISK_1: specifying datafile(s) to restore from backup setrestoring datafile 00004 to /opt/oracle/oradata/eygle/users01.dbfrestoring datafile 00005 to /data1/oradata/systemfile/eygle01.dbfrestoring datafile 00007 to /opt/oracle/oradata/eygle/EYGLE/datafile/o1_mf_itpub_03xv5g66_.dbfchannel ORA_DISK_1: restored backup piece 1piece handle=/data5/flash_recovery_area/EYGLE/backupset/2005_04_05/o1_mf_nnndf_TAG20050405T162921_154m6dxm_.bkp tag=TAG20050405T162921channel ORA_DISK_1: restore completechannel ORA_DISK_1: starting datafile backupset restorechannel ORA_DISK_1: specifying datafile(s) to restore from backup setrestoring datafile 00011 to /opt/oracle/oradata/eygle/t2k01.dbfchannel ORA_DISK_1: restored backup piece 1piece handle=/data5/flash_recovery_area/EYGLE/backupset/2005_04_05/o1_mf_nnndf_TAG20050405T162921_154m76kz_.bkp tag=TAG20050405T162921channel ORA_DISK_1: restore completeFinished restore at 2005-04-05 16:52:31 Starting recover at 2005-04-05 16:52:32using channel ORA_DISK_1datafile 8 not processed because file is offline starting media recovery archive log thread 1 sequence 7 is already on disk as file /data5/flash_recovery_area/EYGLE/archivelog/2005_04_05/o1_mf_1_7_154m8z7k_.arcarchive log thread 1 sequence 1 is already on disk as file /data5/flash_recovery_area/EYGLE/archivelog/2005_04_05/o1_mf_1_1_154n24v5_.arcarchive log thread 1 sequence 2 is already on disk as file /data5/flash_recovery_area/EYGLE/archivelog/2005_04_05/o1_mf_1_2_154n2y40_.arcchannel ORA_DISK_1: starting archive log restore to default destinationchannel ORA_DISK_1: restoring archive logarchive log thread=1 sequence=6channel ORA_DISK_1: restored backup piece 1piece handle=/data5/flash_recovery_area/EYGLE/backupset/2005_04_05/o1_mf_annnn_TAG20050405T163142_154m7jpo_.bkp tag=TAG20050405T163142channel ORA_DISK_1: restore completearchive log filename=/data5/flash_recovery_area/EYGLE/archivelog/2005_04_05/o1_mf_1_6_154ngror_.arc thread=1 sequence=6channel default: deleting archive log(s)archive log filename=/data5/flash_recovery_area/EYGLE/archivelog/2005_04_05/o1_mf_1_6_154ngror_.arc recid=306 stamp=554835160archive log filename=/data5/flash_recovery_area/EYGLE/archivelog/2005_04_05/o1_mf_1_7_154m8z7k_.arc thread=1 sequence=7media recovery completeFinished recover at 2005-04-05 16:53:04 RMAN> alter database open; database opened RMAN> exit Recovery Manager complete. 7.检查数据恢复情况 注重此次恢复跨越了resetlogs时间点 $ sqlplus "/ as sysdba" SQL*Plus: Release 10.1.0.2.0 - Production on Tue Apr 5 16:53:56 2005 Copyright (c) 1982, 2004, Oracle. All rights reserved. Connected to:Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - 64bit ProductionWith the Partitioning, OLAP and Data Mining options SYS AS SYSDBA on 2005-04-05 16:53:56 >select count(*) from t; COUNT(*)---------- 18956 SYS AS SYSDBA on 2005-04-05 16:54:01 >select count(*) from tt; COUNT(*)---------- 24 SYS AS SYSDBA on 2005-04-05 16:54:07 >SYS AS SYSDBA on 2005-04-05 16:54:47 >
