oracle 10g跨越resetlogs时间点进行恢复（1）

人气：2018 2008-05-04

【赛迪网－it技术报道】在oracle数据库10g以前，假如用户对数据库进行了不完全恢复，在使用resetlogs选项打开数据库后，oracle会提示你需要立即进行全备份。

原因：日志序号会被置位，以此防止后续日志被应用。resetlogs之前的备份将不能用于进行跨域resetlogs时间点的恢复。

在oracle数据库10g中，oracle允许跨越resetlogs时间点进行完全/不完全恢复。

本文将针对此特性介绍一个详细的参考示例:

一、全备份数据库

$ rman target /

recovery manager: release 10.1.0.2.0 - 64bit production

connected to target database: eygle (dbid=1337390772)

rman> backup database plus archivelog delete all input;

starting backup at 2005-04-05 16:29:08

current log archived

using target database controlfile instead of recovery catalog

allocated channel: ora_disk_1

channel ora_disk_1: sid=143 devtype=disk

channel ora_disk_1: starting archive log backupset

channel ora_disk_1: specifying archive log(s) in backup set

input archive log thread=1 sequence=3 recid=294 stamp=554823466

input archive log thread=1 sequence=4 recid=295 stamp=554828647

input archive log thread=1 sequence=5 recid=297 stamp=554833749

channel ora_disk_1: starting piece 1 at 2005-04-05 16:29:16

channel ora_disk_1: finished piece 1 at 2005-04-05 16:29:19

piece handle=/data5/flash_recovery_area/eygle/backupset/2005_04_05

/o1_mf_annnn_tag20050405t162914_154m2woc_.bkp comment=none

channel ora_disk_1: backup set complete, elapsed time: 00:00:04

channel 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=554823466

archive log filename=/data5

/flash_recovery_area/eygle/archivelog/2005_04_05

/o1_mf_1_4_154g3728_.arc recid=295 stamp=554828647

archive log filename=/data5/flash_recovery_area/eygle/archivelog

/2005_04_05/o1_mf_1_5_154m2og4_.arc recid=297 stamp=554833749

finished backup at 2005-04-05 16:29:20

starting backup at 2005-04-05 16:29:21

using channel ora_disk_1

channel ora_disk_1: starting full datafile backupset

channel ora_disk_1: specifying datafile(s) in backupset

input datafile fno=00009 name=/data1/oradata/systemfile/bigtbs.dbf

input datafile fno=00001 name=/opt/oracle/oradata/eygle/system01.dbf

input datafile fno=00006 name=/opt/oracle/oradata/eygle/eygle/datafile/o1_mf_test_03xv34ny_.dbf

input datafile fno=00002 name=/opt/oracle/oradata/eygle/undotbs01.dbf

input datafile fno=00003 name=/opt/oracle/oradata/eygle/sysaux01.dbf

input datafile fno=00010 name=/opt/oracle/oradata/eygle/dfmbrc.dbf

input datafile fno=00008 name=/opt/oracle/oradata/eygle/trans01.dbf

channel ora_disk_1: starting piece 1 at 2005-04-05 16:29:22

channel ora_disk_1: finished piece 1 at 2005-04-05 16:31:07

piece handle=/data5/flash_recovery_area/eygle/backupset

/2005_04_05/o1_mf_nnndf_tag20050405t162921_154m33kf_.bkp comment=none

channel ora_disk_1: backup set complete, elapsed time: 00:01:45

channel ora_disk_1: starting full datafile backupset

channel ora_disk_1: specifying datafile(s) in backupset

input datafile fno=00005 name=/data1/oradata/systemfile/eygle01.dbf

input datafile fno=00004 name=/opt/oracle/oradata/eygle/users01.dbf

input datafile fno=00007 name=/opt/oracle/oradata/eygle/eygle/datafile/o1_mf_itpub_03xv5g66_.dbf

channel ora_disk_1: starting piece 1 at 2005-04-05 16:31:08

channel ora_disk_1: finished piece 1 at 2005-04-05 16:31:33

piece handle=/data5/flash_recovery_area/eygle/backupset/2005_04_05

/o1_mf_nnndf_tag20050405t162921_154m6dxm_.bkp comment=none

channel ora_disk_1: backup set complete, elapsed time: 00:00:25

channel ora_disk_1: starting full datafile backupset

channel ora_disk_1: specifying datafile(s) in backupset

input datafile fno=00011 name=/opt/oracle/oradata/eygle/t2k01.dbf

channel ora_disk_1: starting piece 1 at 2005-04-05 16:31:34

channel ora_disk_1: finished piece 1 at 2005-04-05 16:31:41

piece handle=/data5/flash_recovery_area/eygle/backupset

/2005_04_05/o1_mf_nnndf_tag20050405t162921_154m76kz_.bkp comment=none

channel ora_disk_1: backup set complete, elapsed time: 00:00:07

finished backup at 2005-04-05 16:31:41

starting backup at 2005-04-05 16:31:41

current log archived

using channel ora_disk_1

channel ora_disk_1: starting archive log backupset

channel ora_disk_1: specifying archive log(s) in backup set

input archive log thread=1 sequence=6 recid=298 stamp=554833902

channel ora_disk_1: starting piece 1 at 2005-04-05 16:31:44

channel ora_disk_1: finished piece 1 at 2005-04-05 16:31:47

piece handle=/data5/flash_recovery_area/eygle/backupset/2005_04_05

/o1_mf_annnn_tag20050405t163142_154m7jpo_.bkp comment=none

channel ora_disk_1: backup set complete, elapsed time: 00:00:04

channel 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=554833902

finished backup at 2005-04-05 16:31:47

starting control file and spfile autobackup at 2005-04-05 16:31:48

piece handle=/data5/flash_recovery_area/eygle/autobackup

/2005_04_05/o1_mf_s_554833908_154m7qps_.bkp comment=none

finished control file and spfile autobackup at 2005-04-05 16:31:56

rman> exit

recovery manager complete.

二、进行数据的更改

$ sqlplus "/ as sysdba"

sql*plus: release 10.1.0.2.0 - production on

tue apr 5 16:32:23 2005

connected to:

oracle database 10g enterprise edition release

10.1.0.2.0 - 64bit production

with 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 >exit

disconnected from oracle database 10g enterprise

edition release 10.1.0.2.0 - 64bit production

with the partitioning, olap and data mining options

三、执行不完全恢复

$ rman target /

recovery manager: release 10.1.0.2.0 - 64bit production

connected to target database (not started)

rman> startup mount;

oracle instance started

database mounted

total system global area 314572800 bytes

fixed size 1301704 bytes

variable size 261890872 bytes

database buffers 50331648 bytes

redo buffers 1048576 bytes

rman> run {

2> set until sequence 8 thread 1;

3> restore database;

4> recover database;

5> }

executing command: set until clause

using target database controlfile instead of recovery catalog

starting restore at 2005-04-05 16:39:17

allocated channel: ora_disk_1

channel ora_disk_1: sid=160 devtype=disk

datafile 8 not processed because file is read-only

channel ora_disk_1: starting datafile backupset restore

channel ora_disk_1: specifying datafile(s) to restore from backup set

restoring datafile 00001 to /opt/oracle/oradata/eygle/system01.dbf

restoring datafile 00002 to /opt/oracle/oradata/eygle/undotbs01.dbf

restoring datafile 00003 to /opt/oracle/oradata/eygle/sysaux01.dbf

restoring datafile 00006 to /opt/oracle/oradata/eygle/eygle/datafile/o1_mf_test_03xv34ny_.dbf

restoring datafile 00009 to /data1/oradata/systemfile/bigtbs.dbf

restoring datafile 00010 to /opt/oracle/oradata/eygle/dfmbrc.dbf

channel ora_disk_1: restored backup piece 1

piece handle=/data5/flash_recovery_area/eygle/backupset

/2005_04_05/o1_mf_nnndf_tag20050405t162921_154m33kf_.bkp tag=tag20050405t162921

channel ora_disk_1: restore complete

channel ora_disk_1: starting datafile backupset restore

channel ora_disk_1: specifying datafile(s) to restore from backup set

restoring datafile 00004 to /opt/oracle/oradata/eygle/users01.dbf

restoring datafile 00005 to /data1/oradata/systemfile/eygle01.dbf

restoring datafile 00007 to /opt/oracle/oradata/eygle/eygle/datafile/o1_mf_itpub_03xv5g66_.dbf

channel ora_disk_1: restored backup piece 1

piece handle=/data5/flash_recovery_area/eygle/backupset

/2005_04_05/o1_mf_nnndf_tag20050405t162921_154m6dxm_.bkp

tag=tag20050405t162921

channel ora_disk_1: restore complete

channel ora_disk_1: starting datafile backupset restore

channel ora_disk_1: specifying datafile(s) to restore from backup set

restoring datafile 00011 to /opt/oracle/oradata/eygle/t2k01.dbf

channel ora_disk_1: restored backup piece 1

piece handle=/data5/flash_recovery_area/eygle/backupset

/2005_04_05/o1_mf_nnndf_tag20050405t162921_154m76kz_.bkp

tag=tag20050405t162921

channel ora_disk_1: restore complete

finished restore at 2005-04-05 16:41:45

starting recover at 2005-04-05 16:41:46

using channel ora_disk_1

datafile 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_.arc

channel ora_disk_1: starting archive log

restore to default destination

channel ora_disk_1: restoring archive log

archive log thread=1 sequence=6

channel ora_disk_1: restored backup piece 1

piece handle=/data5/flash_recovery_area/eygle/backupset

/2005_04_05/o1_mf_annnn_tag20050405t163142_154m7jpo_.bkp

tag=tag20050405t163142

channel ora_disk_1: restore complete

archive log filename=/data5/flash_recovery_area/eygle/archivelog

/2005_04_05/o1_mf_1_6_154mtkfk_.arc thread=1 sequence=6

channel 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=554834513

archive log filename=/data5/flash_recovery_area/eygle/archivelog

/2005_04_05/o1_mf_1_7_154m8z7k_.arc thread=1 sequence=7

media recovery complete

finished recover at 2005-04-05 16:41:57

rman> alter database open resetlogs;

database opened

rman> exit

recovery manager complete.

四、此时的数据库状态

$ sqlplus "/ as sysdba"

sql*plus: release 10.1.0.2.0 - production on tue apr 5 16:43:58 2005

connected to:

oracle database 10g enterprise edition

release 10.1.0.2.0 - 64bit production

with the partitioning, olap and data mining options

sys as sysdba on 2005-04-05 16:43:58 >archive log list;

database log mode archive mode

automatic archival enabled

archive destination use_db_recovery_file_dest

oldest online log sequence 1

next log sequence to archive 1

current log sequence 1

sys as sysdba on 2005-04-05 16:44:01 >select count(*) from t;

count(*)

----------

18956

五、继续进行数据更改

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(*)

----------

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