熱點推薦:
您现在的位置: 電腦知識網 >> 編程 >> Oracle >> 正文

Oracle10g跨越Resetlogs時間點進行恢復

2013-11-13 22:10:26  來源: Oracle 

  原因日志序號會被置位以此防止後續日志被應用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 :: >

  至此數據恢復徹底完成
 
From:http://tw.wingwit.com/Article/program/Oracle/201311/18371.html

    推薦文章
    Copyright © 2005-2013 電腦知識網 Computer Knowledge   All rights reserved.