open resetlogs後資料恢復
系統環境：
SQL> select * from v$version whererownum=1;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise EditionRelease 11.2.0.3.0 - 64bit Production
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch
Oldest online log sequence 1
Next log sequence to archive 2
Current log sequence 2
1、建立備份集
RMAN> backup as compressed backupset database;
Starting backup at 06-JUN-13
using target database control file insteadof recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=23 device type=DISK
channel ORA_DISK_1: starting compressed fulldatafile backup set
channel ORA_DISK_1: specifying datafile(s)in backup set
input datafile file number=00001name=/u01/app/oracle/oradata/wailon/system01.dbf
input datafile file number=00002name=/u01/app/oracle/oradata/wailon/sysaux01.dbf
input datafile file number=00003name=/u01/app/oracle/oradata/wailon/undotbs01.dbf
input datafile file number=00005name=/u01/app/oracle/oradata/wailon/tbs_lrj.dbf
input datafile file number=00004name=/u01/app/oracle/oradata/wailon/users01.dbf
channel ORA_DISK_1: starting piece 1 at06-JUN-13
channel ORA_DISK_1: finished piece 1 at06-JUN-13
piecehandle=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/0aobg5b7_1_1tag=TAG20130606T072111 comment=NONE
channel ORA_DISK_1: backup set complete,elapsed time: 00:01:05
Finished backup at 06-JUN-13
Starting ControlFile and SPFILE Autobackupat 06-JUN-13
piecehandle=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/c-2746988849-20130606-05comment=NONE
Finished Control File and SPFILE Autobackupat 06-JUN-13
2、手工備份控制檔案
SQL> alter database backup controlfile to '/u01/controlfile.bk';
3、模擬產生資料，切換日誌並執行檢查點，關閉資料庫
SQL> create table wailon.tab as select *from scott.emp;
Table created.
SQL> select count(*) from wailon.tab;
COUNT(*)
----------
14
SQL> alter system switch logfile;
System altered.
SQL> alter system checkpoint;
System altered.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
4、用手工備份的控制檔案替換現有的控制檔案
[oracle@RAC1 ~]$ cp /u01/controlfile.bk/u01/app/oracle/oradata/wailon/control01.ctl
[oracle@RAC1 ~]$ cp /u01/controlfile.bk/u01/app/oracle/oradata/wailon/control02.ctl
5、恢復並以RESETLOGS開啟資料庫
SQL> startup
ORACLE instance started.
Total System Global Area 417546240 bytes
Fixed Size 2228944 bytes
Variable Size 281021744 bytes
Database Buffers 125829120 bytes
Redo Buffers 8466432 bytes
Database mounted.
ORA-01589: must use RESETLOGS orNORESETLOGS option for database open
SQL>alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01194: file 1 needs more recovery to beconsistent
ORA-01110: data file 1:'/u01/app/oracle/oradata/wailon/system01.dbf'
SQL> recover database;
ORA-00283: ?
ORA-01610: ?ACKUP CONTROLFILE ?
SQL> recover database using backup controlfile;
SQL> recover database using backupcontrolfile;
ORA-00279: change 1129473 generated at06/06/2013 06:37:10 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch1_1_817367830.dbf
ORA-00280: change 1129473 for thread 1 isin sequence #1
Specify log: {
auto
ORA-00279: change 1129589 generated at06/06/2013 07:26:11 needed for thread 1
ORA-00289: suggestion :/u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch1_2_817367830.dbf
ORA-00280: change 1129589 for thread 1 isin sequence #2
ORA-00278: log file'/u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch1_1_817367830.dbf' no longerneeded for this
recovery
ORA-00308: '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch1_2_817367830.dbf'
ORA-27037: ??
Linux-x86_64 Error: 2: No such file ordirectory
Additional information: 3
SQL> select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_
---------- ------- --------------------------------------------------------- ---
3 ONLINE /u01/app/oracle/oradata/wailon/redo03.log NO
2 ONLINE /u01/app/oracle/oradata/wailon/redo02.log NO
1 ONLINE /u01/app/oracle/oradata/wailon/redo01.log NO
SQL> select group#,thread#,sequence#,archived,status,first_change# from v$log;
GROUP# THREAD# SEQUENCE# ARC STATUS FIRST_CHANGE#
---------- ---------- ---------- ------------------- -------------
1 1 1 NO CURRENT 1127284
3 1 0 YES UNUSED 0
2 1 0 YES UNUSED 0
SQL> recover database using backup controlfile;
ORA-00279: change 1129589 generated at06/06/2013 07:26:11 needed for thread 1
ORA-00289: suggestion :/u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch1_2_817367830.dbf
ORA-00280: change 1129589 for thread 1 isin sequence #2
Specify log: {
/u01/app/oracle/oradata/wailon/redo02.log
Log applied.
Media recovery complete.
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS orNORESETLOGS option for database open
SQL> alter database open resetlogs;
Database altered.
6、RESETLOGS後備份控制檔案
SQL> alter database backup controlfile to '/u01/controlfile_after_resetlogs.bk';
7、模擬產生資料，切換日誌並執行檢查點，關閉資料庫
SQL> select count(*) from wailon.tab;
COUNT(*)
----------
14
SQL> insert into wailon.tab select *from wailon.tab;
14 rows created.
SQL> commit;
Commit complete.
SQL> select count(*) from wailon.tab;
COUNT(*)
----------
28
SQL> alter system switch logfile;
System altered.
SQL> alter system checkpoint;
System altered.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
8、用reselogs之前的備份集還原並恢復資料庫
情況一、恢復備份集的控制檔案
[oracle@RAC1 ~]$ rman target /
Recovery Manager: Release 11.2.0.3.0 -Production on Thu Jun 6 04:59:14 2013
Copyright (c) 1982, 2011, Oracle and/or itsaffiliates. All rights reserved.
connected to target database (not started)
RMAN> startup nomount;
Oracle instance started
Total System Global Area 417546240 bytes
Fixed Size 2228944 bytes
Variable Size 281021744 bytes
Database Buffers 125829120 bytes
Redo Buffers 8466432 bytes
RMAN>--從備份集還原控制檔案
RMAN> restore controlfile from'/u01/app/oracle/product/11.2.0/dbhome_1/dbs/c-2746988849-20130606-05';
Starting restore at 06-JUN-13
using channel ORA_DISK_1
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsedtime: 00:00:01
output filename=/u01/app/oracle/oradata/wailon/control01.ctl
output filename=/u01/app/oracle/oradata/wailon/control02.ctl
Finished restore at 06-JUN-13
RMAN> alter database mount;
database mounted
released channel: ORA_DISK_1
RMAN> restore database;
Starting restore at 06-JUN-13
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=134 devicetype=DISK
channel ORA_DISK_1: starting datafilebackup set restore
channel ORA_DISK_1: specifying datafile(s)to restore from backup set
channel ORA_DISK_1: restoring datafile00001 to /u01/app/oracle/oradata/wailon/system01.dbf
channel ORA_DISK_1: restoring datafile00002 to /u01/app/oracle/oradata/wailon/sysaux01.dbf
channel ORA_DISK_1: restoring datafile00003 to /u01/app/oracle/oradata/wailon/undotbs01.dbf
channel ORA_DISK_1: restoring datafile00004 to /u01/app/oracle/oradata/wailon/users01.dbf
channel ORA_DISK_1: restoring datafile00005 to /u01/app/oracle/oradata/wailon/tbs_lrj.dbf
channel ORA_DISK_1: reading from backuppiece /u01/app/oracle/product/11.2.0/dbhome_1/dbs/0aobg5b7_1_1
channel ORA_DISK_1: piecehandle=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/0aobg5b7_1_1tag=TAG20130606T072111
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete,elapsed time: 00:01:05
Finished restore at 06-JUN-13
--未還原上次RESETLOGS後備份的控制檔案－－開始--
RMAN> recover database;
Starting recover at 06-JUN-13
using channel ORA_DISK_1
starting media recovery
archived log filename=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch1_1_817367830.dbf thread=1sequence=1
archived log filename=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch1_2_817367830.dbf thread=1sequence=2
media recovery complete, elapsed time:00:00:01
Finished recover at 06-JUN-13
SQL> conn / as sysdba
Connected.
SQL> alter database open resetlogs;
Database altered.
--RESETLOGS後產生的資料就丟失了
SQL> select count(*) from wailon.tab;
COUNT(*)
----------
14
--未還原上次RESETLOGS後備份的控制檔案－－結束--
SQL> shutdown immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
--還原RESETLOGS後備份的控制檔案
[oracle@RAC1 u01]$ cp/u01/controlfile_after_resetlogs.bk/u01/app/oracle/oradata/wailon/control01.ctl
[oracle@RAC1 u01]$ cp/u01/controlfile_after_resetlogs.bk/u01/app/oracle/oradata/wailon/control02.ctl
--恢復資料庫
SQL> startup mount;
ORACLE instance started.
Total System Global Area 417546240 bytes
Fixed Size 2228944 bytes
Variable Size 301993264 bytes
Database Buffers 104857600 bytes
Redo Buffers 8466432 bytes
Database mounted.
SQL> recover database using backup controlfile;
ORA-00279: change 1129393 generated at06/06/2013 07:21:12 needed for thread 1
ORA-00289: suggestion :/u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch1_1_817367830.dbf
ORA-00280: change 1129393 for thread 1 isin sequence #1
Specify log: {
auto
ORA-00279: change 1129589 generated at06/06/2013 07:26:11 needed for thread 1
ORA-00289: suggestion :/u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch1_2_817367830.dbf
ORA-00280: change 1129589 for thread 1 isin sequence #2
ORA-00278: log file'/u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch1_1_817367830.dbf' no longerneeded for this
recovery
ORA-00279: change 1129825 generated at06/06/2013 07:33:16 needed for thread 1
ORA-00289: suggestion :/u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch1_1_817371196.dbf
ORA-00280: change 1129825 for thread 1 isin sequence #1
ORA-00278: log file'/u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch1_2_817367830.dbf' no longerneeded for this
recovery
ORA-00279: change 1130093 generated at06/06/2013 07:34:22 needed for thread 1
ORA-00289: suggestion :/u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch1_2_817371196.dbf
ORA-00280: change 1130093 for thread 1 isin sequence #2
ORA-00278: log file'/u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch1_1_817371196.dbf' no longerneeded for this
recovery
ORA-00308: '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch1_2_817371196.dbf'
ORA-27037: ??
Linux-x86_64 Error: 2: No such file ordirectory
Additional information: 3
SQL> recover database using backup controlfile until cancel;
ORA-00279: change 1130093 generated at06/06/2013 07:34:22 needed for thread 1
ORA-00289: suggestion :/u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch1_2_817371196.dbf
ORA-00280: change 1130093 for thread 1 isin sequence #2
Specify log: {
cancel
Media recovery cancelled.
SQL> alter database open resetlogs;
Database altered.
--校驗資料
SQL> select count(*) from wailon.tab;
COUNT(*)
----------
28
情況二、使用現有的控制檔案
--模擬生產資料
SQL> insert into wailon.tab select *from wailon.tab;
28 rows created.
SQL> commit;
Commit complete.
SQL> select count(*) from wailon.tab;
COUNT(*)
----------
56
SQL> alter system switch logfile;
System altered.
SQL> alter system checkpoint;
System altered.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
--還原並恢復資料
[oracle@RAC1 ~]$ rman target /
Recovery Manager: Release 11.2.0.3.0 -Production on Thu Jun 6 07:55:00 2013
Copyright (c) 1982, 2011, Oracle and/or itsaffiliates. All rights reserved.
connected to target database (not started)
RMAN> startup mount;
Oracle instance started
database mounted
Total System Global Area 417546240 bytes
Fixed Size 2228944 bytes
Variable Size 301993264 bytes
Database Buffers 104857600 bytes
Redo Buffers 8466432 bytes
RMAN> restore database;
Starting restore at 06-JUN-13
using target database control file insteadof recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=133 devicetype=DISK
channel ORA_DISK_1: starting datafilebackup set restore
channel ORA_DISK_1: specifying datafile(s)to restore from backup set
channel ORA_DISK_1: restoring datafile00001 to /u01/app/oracle/oradata/wailon/system01.dbf
channel ORA_DISK_1: restoring datafile00002 to /u01/app/oracle/oradata/wailon/sysaux01.dbf
channel ORA_DISK_1: restoring datafile00003 to /u01/app/oracle/oradata/wailon/undotbs01.dbf
channel ORA_DISK_1: restoring datafile00004 to /u01/app/oracle/oradata/wailon/users01.dbf
channel ORA_DISK_1: restoring datafile00005 to /u01/app/oracle/oradata/wailon/tbs_lrj.dbf
channel ORA_DISK_1: reading from backuppiece /u01/app/oracle/product/11.2.0/dbhome_1/dbs/0aobg5b7_1_1
channel ORA_DISK_1: piecehandle=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/0aobg5b7_1_1tag=TAG20130606T072111
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete,elapsed time: 00:01:05
Finished restore at 06-JUN-13
RMAN> recover database;
Starting recover at 06-JUN-13
using channel ORA_DISK_1
starting media recovery
archived log for thread 1 with sequence 1is already on disk as file /u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch1_1_817367830.dbf
archived log for thread 1 with sequence 2is already on disk as file/u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch1_2_817367830.dbf
archived log for thread 1 with sequence 1is already on disk as file/u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch1_1_817371196.dbf
archived log filename=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch1_1_817367830.dbf thread=1sequence=1
archived log filename=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch1_2_817367830.dbf thread=1sequence=2
archived log filename=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch1_1_817371196.dbf thread=1sequence=1
media recovery complete, elapsed time:00:00:02
Finished recover at 06-JUN-13
--校驗資料
SQL> select count(*) from wailon.tab;
COUNT(*)
----------
56
9、總結
恢復資料庫時，若是不完全恢復或者使用了舊的控制檔案，需要使用OPEN RESETLOGS開啟資料庫，必須馬上進行備份，否則有可能丟失資料。
如果當前的控制檔案未損壞，就算未做備份，資料也有可能不會丟失。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/429786/viewspace-776250/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 資料庫resetlogs後進行rman恢復7資料庫
- 資料庫resetlogs後進行rman恢復6資料庫
- 資料庫resetlogs後進行rman恢復5資料庫
- 資料庫resetlogs後進行rman恢復4資料庫
- 資料庫resetlogs後進行rman恢復3資料庫
- 資料庫resetlogs後進行rman恢復2資料庫
- 資料庫resetlogs後進行rman恢復1資料庫
- Oracle資料庫恢復之resetlogsOracle資料庫
- oracle實驗記錄 （恢復-使用resetlogs open前備份恢復）Oracle
- 10可以穿越resetlogs恢復資料庫！資料庫
- 使用RESETLOGS重建控制檔案恢復資料庫資料庫
- Oracle資料恢復:強制Resetlogs的可能資料損失Oracle資料恢復
- 使用RESETLOGS重建控制檔案恢復資料庫（二）資料庫
- Backup And Recovery User's Guide-RMAN資料修復概念-OPEN RESETLOGS操作GUIIDE
- oracle用備份的控制檔案恢復後不用resetlogs開啟方式的恢復Oracle
- Oracle中truncate table後的資料恢復(Oracle資料恢復工具-ODU)Oracle資料恢復
- 恢復資料,資料塊恢復
- alter database open resetlogs;Database
- 資料恢復：AMDU資料抽取恢復資料恢復
- 電腦硬碟資料丟失後怎麼恢復？硬碟資料恢復技巧教程硬碟資料恢復
- 在open狀態下恢復丟失的資料檔案
- chkdsk 後資料丟失的恢復方法
- Mongodb資料庫誤刪後的恢復MongoDB資料庫
- MySQL誤操作後如何快速恢復資料MySql
- git reset --hard 操作後的資料恢復Git資料恢復
- ASM後設資料備份與恢復ASM
- 【資料庫資料恢復】SAP資料庫資料恢復案例資料庫資料恢復
- 在open狀態下恢復未備份的資料檔案
- 【資料庫資料恢復】透過恢復NDF檔案修復資料庫的資料恢復過程資料庫資料恢復
- 【資料庫資料恢復】Sql Server資料庫資料恢復案例資料庫資料恢復SQLServer
- 缺少歸檔日誌，ORACLE資料庫恢復使用_allow_resetlogs_corruption引數Oracle資料庫
- 資料庫修復資料恢復資料庫資料恢復
- 恢復控制檔案避免使用resetlogs選項
- oracle 10g跨越resetlogs恢復-incarnationOracle 10g
- 【Vsan資料恢復】Vsan資料恢復案例資料恢復
- Vsan資料恢復—Vsan資料恢復案例資料恢復
- NAS陣列恢復資料資料恢復開盤陣列資料恢復
- Sybase ASE資料庫恢復，Sybase資料恢復，資料誤刪除恢復工具READSYBDEVICE資料庫資料恢復dev