原因日志序号会被置位以此防止后续日志被应用resetlogs之前的备份将不能用于进行跨域resetlogs时间点的恢复
在Oracle数据库g中Oracle允许跨越resetlogs时间点进行完全/不完全恢复
本文将针对此特性介绍一个详细的参考示例:
一全备份数据库
$ rman target /
Recovery Manager: Release bit Production
Copyright (c) Oracle All rights reserved
connected to target database: EYGLE (DBID=)
RMAN> backup database plus archivelog delete all input;
Starting backup at ::
current log archived
using target database controlfile instead of recovery catalog
allocated channel: ORA_DISK_
channel ORA_DISK_: sid= devtype=DISK
channel ORA_DISK_: starting archive log backupset
channel ORA_DISK_: specifying archive log(s) in backup set
input archive log thread= sequence= recid= stamp=
input archive log thread= sequence= recid= stamp=
input archive log thread= sequence= recid= stamp=
channel ORA_DISK_: starting piece at ::
channel ORA_DISK_: finished piece at ::
piece handle=/data/flash_recovery_area/EYGLE/backupset/__
/o_mf_annnn_TAGT_mwoc_bkp comment=NONE
channel ORA_DISK_: backup set complete elapsed time: ::
channel ORA_DISK_: deleting archive log(s)
archive log filename=/data/flash_recovery_area/EYGLE
/archivelog/__
/o_mf___qm_arc recid= stamp=
archive log filename=/data
/flash_recovery_area/EYGLE/archivelog/__
/o_mf___g_arc recid= stamp=
archive log filename=/data/flash_recovery_area/EYGLE/archivelog
/__/o_mf___mog_arc recid= stamp=
Finished backup at ::
Starting backup at ::
using channel ORA_DISK_
channel ORA_DISK_: starting full datafile backupset
channel ORA_DISK_: specifying datafile(s) in backupset
input datafile fno= name=/data/oradata/systemfile/bigtbsdbf
input datafile fno= name=/opt/oracle/oradata/eygle/systemdbf
input datafile fno= name=/opt/oracle/oradata/eygle/EYGLE/datafile/o_mf_test_xvny_dbf
input datafile fno= name=/opt/oracle/oradata/eygle/undotbsdbf
input datafile fno= name=/opt/oracle/oradata/eygle/sysauxdbf
input datafile fno= name=/opt/oracle/oradata/eygle/dfmbrcdbf
input datafile fno= name=/opt/oracle/oradata/eygle/transdbf
channel ORA_DISK_: starting piece at ::
channel ORA_DISK_: finished piece at ::
piece handle=/data/flash_recovery_area/EYGLE/backupset
/__/o_mf_nnndf_TAGT_mkf_bkp comment=NONE
channel ORA_DISK_: backup set complete elapsed time: ::
channel ORA_DISK_: starting full datafile backupset
channel ORA_DISK_: specifying datafile(s) in backupset
input datafile fno= name=/data/oradata/systemfile/eygledbf
input datafile fno= name=/opt/oracle/oradata/eygle/usersdbf
input datafile fno= name=/opt/oracle/oradata/eygle/EYGLE/datafile/o_mf_itpub_xvg_dbf
channel ORA_DISK_: starting piece at ::
channel ORA_DISK_: finished piece at ::
piece handle=/data/flash_recovery_area/EYGLE/backupset/__
/o_mf_nnndf_TAGT_mdxm_bkp comment=NONE
channel ORA_DISK_: backup set complete elapsed time: ::
channel ORA_DISK_: starting full datafile backupset
channel ORA_DISK_: specifying datafile(s) in backupset
input datafile fno= name=/opt/oracle/oradata/eygle/tkdbf
channel ORA_DISK_: starting piece at ::
channel ORA_DISK_: finished piece at ::
piece handle=/data/flash_recovery_area/EYGLE/backupset
/__/o_mf_nnndf_TAGT_mkz_bkp comment=NONE
channel ORA_DISK_: backup set complete elapsed time: ::
Finished backup at ::
Starting backup at ::
current log archived
using channel ORA_DISK_
channel ORA_DISK_: starting archive log backupset
channel ORA_DISK_: specifying archive log(s) in backup set
input archive log thread= sequence= recid= stamp=
channel ORA_DISK_: starting piece at ::
channel ORA_DISK_: finished piece at ::
piece handle=/data/flash_recovery_area/EYGLE/backupset/__
/o_mf_annnn_TAGT_mjpo_bkp comment=NONE
channel ORA_DISK_: backup set complete elapsed time: ::
channel ORA_DISK_: deleting archive log(s)
archive log filename=/data/flash_recovery_area/EYGLE/archivelog
/__/o_mf___mgb_arc recid= stamp=
Finished backup at ::
Starting Control File and SPFILE Autobackup at ::
piece handle=/data/flash_recovery_area/EYGLE/autobackup
/__/o_mf_s__mqps_bkp comment=NONE
Finished Control File and SPFILE Autobackup at ::
RMAN> exit
Recovery Manager complete
二进行数据的更改
$ sqlplus / as sysdba
SQL*Plus: Release Production on
Tue Apr ::
Copyright (c) Oracle All rights reserved
Connected to:
Oracle Database g Enterprise Edition Release
bit Production
With the Partitioning OLAP and Data Mining options
SYS AS SYSDBA on :: >alter system switch logfile;
System altered
SYS AS SYSDBA on :: >select count(*) from t;
COUNT(*)
SYS AS SYSDBA on :: >insert into t select * from t;
rows created
SYS AS SYSDBA on :: >commit;
Commit complete
SYS AS SYSDBA on :: >alter system switch logfile;
System altered
SYS AS SYSDBA on :: >truncate table t;
Table truncated
SYS AS SYSDBA on :: >alter system switch logfile;
System altered
日志序列的情况如下:
SYS AS SYSDBA on :: >
select * from v$log_history where recid >;
RECID STAMP THREAD# SEQUENCE# FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# RESETLOGS_CHANGE# RESETLOGS_TIME
:: ::
:: ::
:: ::
:: ::
:: ::
:: ::
:: ::
:: ::
:: ::
rows selected
关闭数据库:
SYS AS SYSDBA on :: >shutdown immediate;
Database closed
Database dismounted
ORACLE instance shut down
SYS AS SYSDBA on :: >exit
Disconnected from Oracle Database g Enterprise
Edition Release bit Production
With the Partitioning OLAP and Data Mining options
三执行不完全恢复
$ rman target /
Recovery Manager: Release bit Production
Copyright (c) Oracle All rights reserved
connected to target database (not started)
RMAN> startup mount;
Oracle instance started
database mounted
Total System Global Area bytes
Fixed Size bytes
Variable Size bytes
Database Buffers bytes
Redo Buffers bytes
RMAN> run {
> set until sequence thread ;
> restore database;
> recover database;
> }
executing command: SET until clause
using target database controlfile instead of recovery catalog
Starting restore at ::
allocated channel: ORA_DISK_
channel ORA_DISK_: sid= devtype=DISK
datafile not processed because file is readonly
channel ORA_DISK_: starting datafile backupset restore
channel ORA_DISK_: specifying datafile(s) to restore from backup set
restoring datafile to /opt/oracle/oradata/eygle/systemdbf
restoring datafile to /opt/oracle/oradata/eygle/undotbsdbf
restoring datafile to /opt/oracle/oradata/eygle/sysauxdbf
restoring datafile to /opt/oracle/oradata/eygle/EYGLE/datafile/o_mf_test_xvny_dbf
restoring datafile to /data/oradata/systemfile/bigtbsdbf
restoring datafile to /opt/oracle/oradata/eygle/dfmbrcdbf
channel ORA_DISK_: restored backup piece
piece handle=/data/flash_recovery_area/EYGLE/backupset
/__/o_mf_nnndf_TAGT_mkf_bkp tag=TAGT
channel ORA_DISK_: restore complete
channel ORA_DISK_: starting datafile backupset restore
channel ORA_DISK_: specifying datafile(s) to restore from backup set
restoring datafile to /opt/oracle/oradata/eygle/usersdbf
restoring datafile to /data/oradata/systemfile/eygledbf
restoring datafile to /opt/oracle/oradata/eygle/EYGLE/datafile/o_mf_itpub_xvg_dbf
channel ORA_DISK_: restored backup piece
piece handle=/data/flash_recovery_area/EYGLE/backupset
/__/o_mf_nnndf_TAGT_mdxm_bkp
tag=TAGT
channel ORA_DISK_: restore complete
channel ORA_DISK_: starting datafile backupset restore
channel ORA_DISK_: specifying datafile(s) to restore from backup set
restoring datafile to /opt/oracle/oradata/eygle/tkdbf
channel ORA_DISK_: restored backup piece
piece handle=/data/flash_recovery_area/EYGLE/backupset
/__/o_mf_nnndf_TAGT_mkz_bkp
tag=TAGT
channel ORA_DISK_: restore complete
Finished restore at ::
Starting recover at ::
using channel ORA_DISK_
datafile not processed because file is offline
starting media recovery
archive log thread sequence is already on disk as file /data/flash_recovery_area/EYGLE/archivelog
/__/o_mf___mzk_arc
channel ORA_DISK_: starting archive log
restore to default destination
channel ORA_DISK_: restoring archive log
archive log thread= sequence=
channel ORA_DISK_: restored backup piece
piece handle=/data/flash_recovery_area/EYGLE/backupset
/__/o_mf_annnn_TAGT_mjpo_bkp
tag=TAGT
channel ORA_DISK_: restore complete
archive log filename=/data/flash_recovery_area/EYGLE/archivelog
/__/o_mf___mtkfk_arc thread= sequence=
channel default: deleting archive log(s)
archive log filename=/data/flash_recovery_area/EYGLE/archivelog
/__/o_mf___mtkfk_arc recid= stamp=
archive log filename=/data/flash_recovery_area/EYGLE/archivelog
/__/o_mf___mzk_arc thread= sequence=
media recovery complete
Finished recover at ::
RMAN> alter database open resetlogs;
database opened
RMAN> exit
Recovery Manager complete
四此时的数据库状态
$ sqlplus / as sysdba
SQL*Plus: Release Production on Tue Apr ::
Copyright (c) Oracle All rights reserved
Connected to:
Oracle Database g Enterprise Edition
Release bit Production
With the Partitioning OLAP and Data Mining options
SYS AS SYSDBA on :: >archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence
Next log sequence to archive
Current log sequence
SYS AS SYSDBA on :: >select count(*) from t;
COUNT(*)
五继续进行数据更改
SYS AS SYSDBA on :: >
create table tt as select * from dba_users;
Table created
SYS AS SYSDBA on :: >alter system switch logfile;
System altered
SYS AS SYSDBA on :: >select count(*) from tt;
COUNT(*)
SYS AS SYSDBA on :: >insert into tt select * from tt;
rows created
SYS AS SYSDBA on :: >commit;
Commit complete
SYS AS SYSDBA on :: >alter system switch logfile;
System altered
此时的日志序列:
SYS AS SYSDBA on :: >
select * from v$log_history where recid >;
RECID STAMP THREAD# SEQUENCE# FIRST_CHANGE# FIRST_TIME
NEXT_CHANGE# RESETLOGS_CHANGE# RESETLOGS_TIME
:: ::
:: ::
:: ::
:: ::
:: ::
:: ::
:: ::
:: ::
:: ::
:: ::
:: ::
rows selected
SYS AS SYSDBA on :: >shutdown immediate;
Database closed
Database dismounted
ORACLE instance shut down
SYS AS SYSDBA on :: >exit
Disconnected from Oracle Database g Enterprise
Edition Release bit Production
With the Partitioning OLAP and Data Mining options
六再次执行恢复
$ rman target /
Recovery Manager: Release bit Production
Copyright (c) Oracle All rights reserved
connected to target database (not started)
RMAN> startup mount;
Oracle instance started
database mounted
Total System Global Area bytes
Fixed Size bytes
Variable Size bytes
Database Buffers bytes
Redo Buffers bytes
RMAN> run {
> restore database;
> recover database;
> }
Starting restore at ::
using target database controlfile instead of recovery catalog
allocated channel: ORA_DISK_
channel ORA_DISK_: sid= devtype=DISK
datafile not processed because file is readonly
channel ORA_DISK_: starting datafile backupset restore
channel ORA_DISK_: specifying datafile(s) to restore from backup set
restoring datafile to /opt/oracle/oradata/eygle/systemdbf
restoring datafile to /opt/oracle/oradata/eygle/undotbsdbf
restoring datafile to /opt/oracle/oradata/eygle/sysauxdbf
restoring datafile to /opt/oracle/oradata/eygle/EYGLE/datafile/o_mf_test_xvny_dbf
restoring datafile to /data/oradata/systemfile/bigtbsdbf
restoring datafile to /opt/oracle/oradata/eygle/dfmbrcdbf
channel ORA_DISK_: restored backup piece
piece handle=/data/flash_recovery_area/EYGLE/backupset
/__/o_mf_nnndf_TAGT_mkf_bkp
tag=TAGT
channel ORA_DISK_: restore complete
channel ORA_DISK_: starting datafile backupset restore
channel ORA_DISK_: specifying datafile(s) to restore from backup set
restoring datafile to /opt/oracle/oradata/eygle/usersdbf
restoring datafile to /data/oradata/systemfile/eygledbf
restoring datafile to /opt/oracle/oradata/eygle/EYGLE/datafile/o_mf_itpub_xvg_dbf
channel ORA_DISK_: restored backup piece
piece handle=/data/flash_recovery_area/EYGLE/backupset
/__/o_mf_nnndf_TAGT_mdxm_bkp tag=TAGT
channel ORA_DISK_: restore complete
channel ORA_DISK_: starting datafile backupset restore
channel ORA_DISK_: specifying datafile(s) to restore from backup set
restoring datafile to /opt/oracle/oradata/eygle/tkdbf
channel ORA_DISK_: restored backup piece
piece handle=/data/flash_recovery_area/EYGLE/backupset
/__/o_mf_nnndf_TAGT_mkz_bkp
tag=TAGT
channel ORA_DISK_: restore complete
Finished restore at ::
Starting recover at ::
using channel ORA_DISK_
datafile not processed because file is offline
starting media recovery
archive log thread sequence is already on disk as file /data/flash_recovery_area/EYGLE/archivelog
/__/o_mf___mzk_arc
archive log thread sequence is already on disk as file /data/flash_recovery_area/EYGLE/archivelog
/__/o_mf___nv_arc
archive log thread sequence is already on disk as file /data/flash_recovery_area/EYGLE/archivelog
/__/o_mf___ny_arc
channel ORA_DISK_: starting archive log
restore to default destination
channel ORA_DISK_: restoring archive log
archive log thread= sequence=
channel ORA_DISK_: restored backup piece
piece handle=/data/flash_recovery_area/EYGLE/backupset
/__/o_mf_annnn_TAGT_mjpo_bkp
tag=TAGT
channel ORA_DISK_: restore complete
archive log filename=/data/flash_recovery_area/EYGLE/archivelog
/__/o_mf___ngror_arc thread= sequence=
channel default: deleting archive log(s)
archive log filename=/data/flash_recovery_area/EYGLE/archivelog
/__/o_mf___ngror_arc recid= stamp=
archive log filename=/data/flash_recovery_area/EYGLE/archivelog
/__/o_mf___mzk_arc thread= sequence=
media recovery complete
Finished recover at ::
RMAN> alter database open;
database opened
RMAN> exit
Recovery Manager complete
七最后检查数据恢复情况
注意此次恢复跨越了resetlogs时间点
$ sqlplus / as sysdba
SQL*Plus: Release Production on
Tue Apr ::
Copyright (c) Oracle All rights reserved
Connected to:
Oracle Database g Enterprise Edition Release
bit Production
With the Partitioning OLAP and Data Mining options
SYS AS SYSDBA on :: >select count(*) from t;
COUNT(*)
SYS AS SYSDBA on :: >select count(*) from tt;
COUNT(*)
SYS AS SYSDBA on :: >
SYS AS SYSDBA on :: >
至此数据恢复彻底完成
