關於using backup controlfile(丟失控制檔案和資料檔案)
--整理以前的學習筆記
先確保資料庫處於archivelog模式下，/u01/arch下是空的
SQL> archive log list
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination /u01/arch
Oldest online log sequence 1
Current log sequence 1
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.
Total System Global Area 497995776 bytes
Fixed Size 1337464 bytes
Variable Size 364906376 bytes
Database Buffers 125829120 bytes
Redo Buffers 5922816 bytes
Database mounted.
SQL> alter database archivelog;
Database altered.
SQL> alter database open;
Database altered.
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/arch
Oldest online log sequence 1
Next log sequence to archive 1
Current log sequence 1
先做一個冷備，完全修改之前的
[oracle@mylinux coldbackup]$ mkdir 20121027_01
[oracle@mylinux coldbackup]$ cd 20121027_01/
[oracle@mylinux 20121027_01]$ cp /u01/oradata/orcl/*.log .
[oracle@mylinux 20121027_01]$ cp /u01/oradata/orcl/*.ctl .
[oracle@mylinux 20121027_01]$ cp /u01/oradata/orcl/*.dbf .
開啟資料庫
conn hr/hr
SQL> create table fruits(name varchar2(10));
Table created.
SQL> insert into fruits values('apple');
1 row created.
SQL> insert into fruits values('banana');
1 row created.
SQL> insert into fruits values('orange');
1 row created.
SQL> commit;
Commit complete.
SQL> select group#,sequence#,status,archived from v$log;--查詢發現當前的聯機重做日誌檔案是group 3,但是還未歸檔
GROUP# SEQUENCE# STATUS ARCHIVED
---------- ---------- ---------------- --------
1 1 INACTIVE YES
2 2 INACTIVE YES
3 3 CURRENT NO
4 0 UNUSED YES
[oracle@mylinux orcl]$ strings redo03.log | grep orange
orange
[oracle@mylinux orcl]$ strings users01.dbf | grep orange
orange
--發現資料檔案中也已經有了
SQL> alter system archive log current;--歸檔當前聯機重做日誌，/u01/arch下已經有了一條記錄
System altered
(
[oracle@mylinux arch]$ ll
total 548
-rw-r----- 1 oracle oinstall 555520 Oct 27 09:00 1_3_797547884.dbf
--3即sequence#
--797547884即select resetlogs_id from V$DATABASE_INCARNATION where status='CURRENT';
)
[oracle@mylinux arch]$ strings 1_3_797547884.dbf | grep orange --歸檔日誌檔案中已經有我們的資料了
orange
SQL> select group#,sequence#,status,archived from v$log;--發現歸檔之後會切換聯機重做日誌組，但是還是active
狀態，我們發生下檢查點，使髒資料寫到資料檔案中
GROUP# SEQUENCE# STATUS ARCHIVED
---------- ---------- ---------------- --------
1 1 INACTIVE YES
2 2 INACTIVE YES
3 3 ACTIVE YES
4 4 CURRENT NO
SQL> alter system checkpoint;--發生檢查點
System altered
SQL> select group#,sequence#,status,archived from v$log;--現在我們保證我們的fruits資料已經可靠地寫到資料文
件中，也已經在歸檔日誌檔案中了
GROUP# SEQUENCE# STATUS ARCHIVED
---------- ---------- ---------------- --------
1 1 INACTIVE YES
2 2 INACTIVE YES
3 3 INACTIVE YES
4 4 CURRENT NO
下面我關閉資料庫再做下冷備，備份聯機重做日誌檔案、控制檔案和資料檔案
SQL> conn /as sysdba
Connected.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
[oracle@mylinux coldbackup]$ mkdir 20121027_02
[oracle@mylinux coldbackup]$ cd 20121027_02
[oracle@mylinux 20121027_02]$ cp /u01/oradata/orcl/*.log .
[oracle@mylinux 20121027_02]$ cp /u01/oradata/orcl/*.ctl .
[oracle@mylinux 20121027_02]$ cp /u01/oradata/orcl/*.dbf .
下面我重新開啟資料庫，往表裡插入資料
SQL> conn hr/hr
Connected.
SQL> insert into fruits values ('grape');
1 row created.
SQL> commit;
Commit complete.
SQL> select group#,sequence#,status,archived from v$log;
GROUP# SEQUENCE# STATUS ARCHIVED
---------- ---------- ---------------- --------
1 1 INACTIVE YES
2 2 INACTIVE YES
3 3 INACTIVE YES
4 4 CURRENT NO
SQL> alter system archive log current;
System altered
SQL> select group#,sequence#,status,archived from v$log;
GROUP# SEQUENCE# STATUS ARCHIVED
---------- ---------- ---------------- --------
1 5 CURRENT NO
2 2 INACTIVE YES
3 3 INACTIVE YES
4 4 ACTIVE YES
SQL> alter system checkpoint;
System altered
SQL> select group#,sequence#,status,archived from v$log;
GROUP# SEQUENCE# STATUS ARCHIVED
---------- ---------- ---------------- --------
1 5 CURRENT NO
2 2 INACTIVE YES
3 3 INACTIVE YES
4 4 INACTIVE YES
--說明現在fruits.name='grape'這條資料在group 4日誌組裡，且已經在歸檔檔案中，資料也已經寫到資料檔案中
(
[oracle@mylinux arch]$ ll
total 2064
-rw-r----- 1 oracle oinstall 555520 Oct 27 09:00 1_3_797547884.dbf
-rw-r----- 1 oracle oinstall 1547776 Oct 27 09:11 1_4_797547884.dbf
)
[oracle@mylinux arch]$ strings 1_4_797547884.dbf | grep grape
grape
[oracle@mylinux orcl]$ strings users01.dbf | grep grape
grape
[oracle@mylinux orcl]$ strings redo04.log | grep grape
grape
下面我再插入一條資料fruits.name='peach'
SQL> conn hr/hr
Connected.
SQL> insert into fruits values ('peach');
1 row created.
SQL> commit;
Commit complete.
SQL> select group#,sequence#,status,archived from v$log;--現在fruits.name='peach'這條資料在group 1中，未歸
檔
GROUP# SEQUENCE# STATUS ARCHIVED
---------- ---------- ---------------- --------
1 5 CURRENT NO
2 2 INACTIVE YES
3 3 INACTIVE YES
4 4 INACTIVE YES
[oracle@mylinux orcl]$ strings redo01.log | grep peach
peach
[oracle@mylinux orcl]$ strings users01.dbf | grep peach
peach,
fruits.name='peach'這條資料在資料檔案和聯機重做日誌檔案裡面都有，沒有在歸檔日誌裡面
現在再做一個冷備
shutdown immediate
[oracle@mylinux coldbackup]$ mkdir 20121027_03
[oracle@mylinux coldbackup]$ cd 20121027_03
[oracle@mylinux 20121027_03]$ cp /u01/oradata/orcl/*.log .
[oracle@mylinux 20121027_03]$ cp /u01/oradata/orcl/*.ctl .
[oracle@mylinux 20121027_03]$ cp /u01/oradata/orcl/*.dbf .
下面開始進入恢復
1、模擬控制檔案全部丟失的情況（資料檔案和聯機重做日誌檔案是最新的，沒有丟失）
刪除當前的控制檔案，並拷貝備份的控制檔案
SQL> conn /as sysdba
Connected.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
[oracle@mylinux arch]$ cd /u01/oradata/orcl
[oracle@mylinux orcl]$ rm *.ctl
[oracle@mylinux orcl]$ cp /u01/backup/coldbackup/20121027_02/*.ctl .
此時開啟資料庫，提示old controlfile
SQL> startup
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.
Total System Global Area 497995776 bytes
Fixed Size 1337464 bytes
Variable Size 364906376 bytes
Database Buffers 125829120 bytes
Redo Buffers 5922816 bytes
Database mounted.
ORA-01122: database file 1 failed verification check
ORA-01110: data file 1: '/u01/oradata/orcl/system01.dbf'
ORA-01207: file is more recent than control file - old control file
假如我們現在恢復資料庫，recover database，不加using backup controlfile,
Oracle就知道我們要把資料恢復到控制檔案記錄的最大SCN
SQL> select name,checkpoint_change# from v$datafile;
NAME CHECKPOINT_CHANGE#
-------------------------------------------------------------------------------- ------------------
/u01/oradata/orcl/system01.dbf 2246274
/u01/oradata/orcl/sysaux01.dbf 2246274
/u01/oradata/orcl/undotbs01.dbf 2246274
/u01/oradata/orcl/users01.dbf 2246274
/u01/oradata/orcl/example01.dbf 2246274
/u01/oradata/orcl/tbsmn01.dbf 2246274
/u01/oradata/orcl/tbsmn02.dbf 2246274
/u01/oradata/orcl/manualsegs01.dbf 2246274
/u01/oradata/orcl/autosegs01.dbf 2246274
/u01/oradata/orcl/rman01.dbf 2246274
/u01/oradata/orcl/hello.dbf 2246274
11 rows selected
SQL> select name,checkpoint_change# from v$datafile_header;
NAME CHECKPOINT_CHANGE#
-------------------------------------------------------------------------------- ------------------
/u01/oradata/orcl/system01.dbf 2247714
/u01/oradata/orcl/sysaux01.dbf 2247714
/u01/oradata/orcl/undotbs01.dbf 2247714
/u01/oradata/orcl/users01.dbf 2247714
/u01/oradata/orcl/example01.dbf 2247714
/u01/oradata/orcl/tbsmn01.dbf 2247714
/u01/oradata/orcl/tbsmn02.dbf 2247714
/u01/oradata/orcl/manualsegs01.dbf 2247714
/u01/oradata/orcl/autosegs01.dbf 2247714
/u01/oradata/orcl/rman01.dbf 2247714
/u01/oradata/orcl/hello.dbf 2247714
11 rows selected
--資料檔案頭中記錄的SCN比控制檔案中記錄的SCN要大
SQL> select * from v$recover_file;
FILE# ONLINE ONLINE_STATUS ERROR CHANGE# TIME
---------- ------- ------------- ------------------ ---------- -----------
1 ONLINE ONLINE UNKNOWN ERROR 2247714 2012-10-27
2 ONLINE ONLINE UNKNOWN ERROR 2247714 2012-10-27
3 ONLINE ONLINE UNKNOWN ERROR 2247714 2012-10-27
4 ONLINE ONLINE UNKNOWN ERROR 2247714 2012-10-27
5 ONLINE ONLINE UNKNOWN ERROR 2247714 2012-10-27
6 ONLINE ONLINE UNKNOWN ERROR 2247714 2012-10-27
7 ONLINE ONLINE UNKNOWN ERROR 2247714 2012-10-27
8 ONLINE ONLINE UNKNOWN ERROR 2247714 2012-10-27
9 ONLINE ONLINE UNKNOWN ERROR 2247714 2012-10-27
10 ONLINE ONLINE UNKNOWN ERROR 2247714 2012-10-27
11 ONLINE ONLINE UNKNOWN ERROR 2247714 2012-10-27
11 rows selected
SQL> select * from v$recovery_log;
THREAD# SEQUENCE# TIME ARCHIVE_NAME
---------- ---------- ----------- ------------------------------------------------------------------------
--------
SQL> recover database;
ORA-00283: recovery session canceled due to errors
ORA-01122: database file 1 failed verification check
ORA-01110: data file 1: '/u01/oradata/orcl/system01.dbf'
ORA-01207: file is more recent than control file - old control file
但是事實上此時恢復資料庫是出錯的，因為資料檔案比控制檔案要新，recover database（不加using backup
controlfile）命令是把資料庫恢復到控制檔案記錄的最大SCN，不可能使用recover命令來把資料庫恢復到過去的一個時間
點，閃回資料庫需要用flashback database（使用閃回日誌）
如果使用using backup controlfile，則是告訴Oracle把資料庫恢復到資料檔案頭記錄的最大SCN，同時也會把控制檔案恢
復到最新，下面來實驗下
SQL> recover database using backup controlfile;
ORA-00279: change 2246274 generated at 10/27/2012 09:00:51 needed for thread 1
ORA-00289: suggestion : /u01/arch/1_4_797547884.dbf
ORA-00280: change 2246274 for thread 1 is in sequence #4
因為我們restore的控制檔案是第二次冷備裡的，所以 1_3_797547884.dbf這個歸檔日誌檔案不需要，需要的
是/u01/arch/1_4_797547884.dbf這個歸檔日誌檔案，這時候我輸入auto讓Oracle自動應用歸檔日誌檔案
Specify log: {
auto
ORA-00279: change 2246612 generated at 10/27/2012 09:11:18 needed for thread 1
ORA-00289: suggestion : /u01/arch/1_5_797547884.dbf
ORA-00280: change 2246612 for thread 1 is in sequence #5
ORA-00278: log file '/u01/arch/1_4_797547884.dbf' no longer needed for this
recovery
ORA-00308: cannot open archived log '/u01/arch/1_5_797547884.dbf'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
發現沒有，使用using backup controlfile時，當Oracle找不到可以應用的歸檔日誌檔案時，它就自動結束這個命令了，
下面我們加上until cancel試試
(
因為using backup controlfile是不完全恢復，不會去應用聯機重組日誌，當找不到所需的歸檔日誌時，就報錯了
)
SQL> recover database using backup controlfile until cancel;
ORA-00279: change 2246612 generated at 10/27/2012 09:11:18 needed for thread 1
ORA-00289: suggestion : /u01/arch/1_5_797547884.dbf
ORA-00280: change 2246612 for thread 1 is in sequence #5
雖然Oracle找不到這個/u01/arch/1_5_797547884.dbf，但是他會進行提示，你可以輸入filename,auto,或cancel，
我們知道，我們插入的最後一條資料name='peach'在聯機重做日誌檔案redo01.log裡面，不在歸檔日誌裡面，這時我們輸
入聯機重做日誌檔案的路徑和名稱
Specify log: {
/u01/oradata/orcl/redo01.log
Log applied.
Media recovery complete.--已經成功恢復了，可以說是完全恢復，但是由於我們使用備份的控制檔案（using backup
controlfile），我們還是需要用resetlogs開啟資料庫
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
SQL> alter database open noresetlogs;
alter database open noresetlogs
*
ERROR at line 1:
ORA-01588: must use RESETLOGS option for database open
SQL> alter database open resetlogs;
Database altered.
SQL> select * from hr.fruits;--已經完全恢復
NAME
----------
apple
banana
orange
grape
peach
SQL> select group#,sequence#,status,archived from v$log;--聯機重做日誌組已經重置，又進入了下一個incarnation
GROUP# SEQUENCE# STATUS ARCHIVED
---------- ---------- ---------------- --------
1 1 CURRENT NO
2 0 UNUSED YES
3 0 UNUSED YES
4 0 UNUSED YES
2、模擬控制檔案和資料檔案都丟失的情況，聯機重做日誌檔案沒有丟失，是最新的
由於進行了第1個實驗，我們的聯機重做日誌要從第三次冷備中拷貝
關閉資料庫
[oracle@mylinux ~]$ cd /u01/oradata/orcl
[oracle@mylinux orcl]$ rm *.log
[oracle@mylinux orcl]$ rm *.dbf
[oracle@mylinux orcl]$ rm *.ctl
[oracle@mylinux orcl]$ cp /u01/backup/coldbackup/20121027_03/*.log . --從第三次冷備中拷貝
[oracle@mylinux orcl]$ cp /u01/backup/coldbackup/20121027_02/*.ctl . --從第二次冷備中拷貝
[oracle@mylinux orcl]$ cp /u01/backup/coldbackup/20121027_02/*.dbf . --從第二次冷備中拷貝
開啟資料庫startup
SQL> startup
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.
Total System Global Area 497995776 bytes
Fixed Size 1337464 bytes
Variable Size 369100680 bytes
Database Buffers 121634816 bytes
Redo Buffers 5922816 bytes
Database mounted.
ORA-03113: end-of-file on communication channel
Process ID: 6199
Session ID: 1 Serial number: 5
檢視/u01/diag/rdbms/orcl/orcl/trace/alert_orcl.log發現提示下列資訊
Errors in file /u01/diag/rdbms/orcl/orcl/trace/orcl_m000_6205.trc:
ORA-00338: log 1 of thread 1 is more recent than control file
ORA-00312: online log 1 thread 1: '/u01/oradata/orcl/redo01.log'
此時使用命令ps -ef | grep oracle檢視Oracle例項是沒有啟動的
startup mount;
SQL> startup mount;
ORA-24324: service handle not initialized
ORA-01041: internal error. hostdef extension doesn't exist
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@mylinux ~]$ sqlplus /nolog
SQL*Plus: Release 11.2.0.1.0 Production on Sun Oct 28 16:38:35 2012
Copyright (c) 1982, 2009, Oracle. All rights reserved.
SQL> conn /as sysdba
Connected to an idle instance.
SQL> startup mount;--要退出sqlplus重新進入才行
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.
Total System Global Area 497995776 bytes
Fixed Size 1337464 bytes
Variable Size 369100680 bytes
Database Buffers 121634816 bytes
Redo Buffers 5922816 bytes
Database mounted.
SQL> col name for a50
SQL> select name,checkpoint_change# from v$datafile;
NAME CHECKPOINT_CHANGE#
-------------------------------------------------- ------------------
/u01/oradata/orcl/system01.dbf 2246274
/u01/oradata/orcl/sysaux01.dbf 2246274
/u01/oradata/orcl/undotbs01.dbf 2246274
/u01/oradata/orcl/users01.dbf 2246274
/u01/oradata/orcl/example01.dbf 2246274
/u01/oradata/orcl/tbsmn01.dbf 2246274
/u01/oradata/orcl/tbsmn02.dbf 2246274
/u01/oradata/orcl/manualsegs01.dbf 2246274
/u01/oradata/orcl/autosegs01.dbf 2246274
/u01/oradata/orcl/rman01.dbf 2246274
/u01/oradata/orcl/hello.dbf 2246274
11 rows selected.
SQL> select name,checkpoint_change# from v$datafile_header;
NAME CHECKPOINT_CHANGE#
-------------------------------------------------- ------------------
/u01/oradata/orcl/system01.dbf 2246274
/u01/oradata/orcl/sysaux01.dbf 2246274
/u01/oradata/orcl/undotbs01.dbf 2246274
/u01/oradata/orcl/users01.dbf 2246274
/u01/oradata/orcl/example01.dbf 2246274
/u01/oradata/orcl/tbsmn01.dbf 2246274
/u01/oradata/orcl/tbsmn02.dbf 2246274
/u01/oradata/orcl/manualsegs01.dbf 2246274
/u01/oradata/orcl/autosegs01.dbf 2246274
/u01/oradata/orcl/rman01.dbf 2246274
/u01/oradata/orcl/hello.dbf 2246274
11 rows selected.
SQL> select * from v$recover_file;
no rows selected
SQL> select * from v$recovery_log;
no rows selected
發現資料檔案頭中的SCN和控制檔案中記錄的資料檔案的SCN是一致的，而alert_orcl.log中提示聯機重做日誌檔案比控制
檔案新
假如我們現在使用recover database命令（恢復到控制檔案的末尾），
SQL> recover database;--提示不需要進行介質恢復，因為控制檔案中記錄的資料檔案的SCN與資料檔案頭中的SCN是一致
的
ORA-00283: recovery session canceled due to errors
ORA-00264: no recovery required
如果使用using backup controlfile
SQL> recover database using backup controlfile;
ORA-00279: change 2246274 generated at 10/27/2012 09:06:13 needed for thread 1
ORA-00289: suggestion : /u01/arch/1_4_797547884.dbf
ORA-00280: change 2246274 for thread 1 is in sequence #4
Specify log: {
auto
ORA-00279: change 2246612 generated at 10/27/2012 09:11:18 needed for thread 1
ORA-00289: suggestion : /u01/arch/1_5_797547884.dbf
ORA-00280: change 2246612 for thread 1 is in sequence #5
ORA-00278: log file '/u01/arch/1_4_797547884.dbf' no longer needed for this
recovery
Log applied.
Media recovery complete.
哈哈，這有點奇怪了，Oracle竟然自動應用了1_4_797547884.dbf和1_5_797547884.dbf，而我之前根本就沒有歸檔
1_5_797547884.dbf，
原來是這樣的，在我上一個實驗中我開啟資料庫的時候使用了alter database open resetlogs,而在Oracle清空日誌之前
，會自動歸檔當前的聯機重做日誌，這樣就產生了1_5_797547884.db
聯機文件Backup and Recovery Reference中關於RESETLOGS
(
Archives the current online redo log files (or up to the last redo record before
redo corruption if corruption is found), clears the contents of the online redo log
files, and resets the online redo log to log sequence 1.
這樣就解釋得通了
)
所以這個實驗要重新做了哦
[oracle@mylinux arch]$ ll
total 2064
-rw-r----- 1 oracle oinstall 555520 Oct 28 16:17 1_3_797547884.dbf
-rw-r----- 1 oracle oinstall 1547776 Oct 28 16:17 1_4_797547884.dbf
--刪除5號歸檔日誌
[oracle@mylinux orcl]$ cp /u01/backup/coldbackup/20121027_03/*.log . --從第三次冷備中拷貝
[oracle@mylinux orcl]$ cp /u01/backup/coldbackup/20121027_02/*.ctl . --從第二次冷備中拷貝
[oracle@mylinux orcl]$ cp /u01/backup/coldbackup/20121027_02/*.dbf . --從第二次冷備中拷貝
SQL> recover database;
ORA-00283: recovery session canceled due to errors
ORA-00264: no recovery required
SQL> recover database using backup controlfile;
ORA-00279: change 2246274 generated at 10/27/2012 09:06:13 needed for thread 1
ORA-00289: suggestion : /u01/arch/1_4_797547884.dbf
ORA-00280: change 2246274 for thread 1 is in sequence #4
Specify log: {
auto
ORA-00279: change 2246612 generated at 10/27/2012 09:11:18 needed for thread 1
ORA-00289: suggestion : /u01/arch/1_5_797547884.dbf
ORA-00280: change 2246612 for thread 1 is in sequence #5
ORA-00278: log file '/u01/arch/1_4_797547884.dbf' no longer needed for this
recovery
ORA-00308: cannot open archived log '/u01/arch/1_5_797547884.dbf'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
SQL> recover database using backup controlfile until cancel;--我覺得從這個例子可以看出，使用using backup
controlfile命令Oracle使用聯機重做日誌裡的最大SCN來進行應用日誌，而不是資料檔案中的最大SCN來進行應用日誌，當
然，如果不加using backup controlfile時是使用控制檔案中最大SCN來應用日誌
ORA-00279: change 2246612 generated at 10/27/2012 09:11:18 needed for thread 1
ORA-00289: suggestion : /u01/arch/1_5_797547884.dbf
ORA-00280: change 2246612 for thread 1 is in sequence #5
Specify log: {
/u01/oradata/orcl/redo01.log
Log applied.
Media recovery complete.
SQL> alter database open resetlogs;
Database altered.
特地比對了下resetlogs前和resetlogs後
[oracle@mylinux arch]$ ll --resetlogs前
total 2064
-rw-r----- 1 oracle oinstall 555520 Oct 28 16:17 1_3_797547884.dbf
-rw-r----- 1 oracle oinstall 1547776 Oct 28 16:17 1_4_797547884.dbf
[oracle@mylinux arch]$ ll ----resetlogs後
total 3748
-rw-r----- 1 oracle oinstall 887296 Oct 28 17:16 1_2_797547884.dbf
-rw-r----- 1 oracle oinstall 555520 Oct 28 17:16 1_3_797547884.dbf
-rw-r----- 1 oracle oinstall 1547776 Oct 28 17:16 1_4_797547884.dbf
-rw-r----- 1 oracle oinstall 824320 Oct 28 17:16 1_5_797547884.dbf
SQL> select * from hr.fruits;--可以看到，資料都恢復回來了
NAME
--------------------------------------------------
apple
banana
orange
grape
peach
再做一遍：
[oracle@mylinux arch]$ ll
total 2064
-rw-r----- 1 oracle oinstall 555520 Oct 28 16:17 1_3_797547884.dbf
-rw-r----- 1 oracle oinstall 1547776 Oct 28 16:17 1_4_797547884.dbf
[oracle@mylinux orcl]$ cp /u01/backup/coldbackup/20121027_03/*.log . --從第三次冷備中拷貝
[oracle@mylinux orcl]$ cp /u01/backup/coldbackup/20121027_02/*.ctl . --從第二次冷備中拷貝
[oracle@mylinux orcl]$ cp /u01/backup/coldbackup/20121027_02/*.dbf . --從第二次冷備中拷貝
startup mount:
SQL> recover database;
ORA-00283: recovery session canceled due to errors
ORA-00264: no recovery required
SQL> recover database until cancel;
Media recovery complete.
在日誌檔案中是這樣記錄的
Completed: ALTER DATABASE MOUNT
Sun Oct 28 19:41:12 2012
ALTER DATABASE RECOVER database ---------------------------
Media Recovery Start
Serial Media Recovery started
Media Recovery failed with error 264----------------------------
ORA-283 signalled during: ALTER DATABASE RECOVER database ...
ALTER DATABASE RECOVER database until cancel -----------------------------
Media Recovery Start
Serial Media Recovery started
Media Recovery Not Required-------------------------------
Completed: ALTER DATABASE RECOVER database until cancel
發現沒有，其實recover database和recover database until cancel一樣，都是什麼事情都沒做，
recover database;--ORA-00264: no recovery required
recover database until cancel;--日誌檔案中記錄的Media Recovery Not Required
因為控制檔案和資料檔案是一致的，所以不需要介質恢復，但是因為聯機重做日誌檔案比較新，所以alter database
open/alter database open resetlogs都會出錯
SQL> recover database;
ORA-00283: recovery session canceled due to errors
ORA-00264: no recovery required
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01139: RESETLOGS option only valid after an incomplete database recovery
SQL> recover database until cancel;
Media recovery complete.
SQL> alter database open resetlogs;
Database altered.
SQL> select * from hr.fruits;
NAME
----------
apple
banana
orange
為了開啟資料庫，必須要使用resetlogs，因為原先的聯機重做日誌檔案已經沒有用了，但是要resetlogs必須是在不完全
恢復之後，recover database是完全恢復，recover database until cancel是不完全恢復，所以只能使用recover
database until cancel了
再來一次，這次恢復，一開始就使用using backup controlfile until cancel，然後輸入auto
[oracle@mylinux arch]$ ll
total 2064
-rw-r----- 1 oracle oinstall 555520 Oct 28 16:17 1_3_797547884.dbf
-rw-r----- 1 oracle oinstall 1547776 Oct 28 16:17 1_4_797547884.dbf
[oracle@mylinux orcl]$ cp /u01/backup/coldbackup/20121027_03/*.log . --從第三次冷備中拷貝
[oracle@mylinux orcl]$ cp /u01/backup/coldbackup/20121027_02/*.ctl . --從第二次冷備中拷貝
[oracle@mylinux orcl]$ cp /u01/backup/coldbackup/20121027_02/*.dbf . --從第二次冷備中拷貝
SQL> startup mount;
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.
Total System Global Area 497995776 bytes
Fixed Size 1337464 bytes
Variable Size 377489288 bytes
Database Buffers 113246208 bytes
Redo Buffers 5922816 bytes
Database mounted.
SQL> recover database using backup controlfile until cancel;
ORA-00279: change 2246274 generated at 10/27/2012 09:06:13 needed for thread 1
ORA-00289: suggestion : /u01/arch/1_4_797547884.dbf
ORA-00280: change 2246274 for thread 1 is in sequence #4
Specify log: {
auto
ORA-00279: change 2246612 generated at 10/27/2012 09:11:18 needed for thread 1
ORA-00289: suggestion : /u01/arch/1_5_797547884.dbf
ORA-00280: change 2246612 for thread 1 is in sequence #5
ORA-00278: log file '/u01/arch/1_4_797547884.dbf' no longer needed for this
recovery
ORA-00308: cannot open archived log '/u01/arch/1_5_797547884.dbf'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
SQL> recover database using backup controlfile until cancel;
ORA-00279: change 2246612 generated at 10/27/2012 09:11:18 needed for thread 1
ORA-00289: suggestion : /u01/arch/1_5_797547884.dbf
ORA-00280: change 2246612 for thread 1 is in sequence #5
Specify log: {
cancel
Media recovery cancelled.
這次我只是驗證下using backup controlfile until cancel輸入auto會不會尋找應用所有的歸檔日誌，還是會的
SQL> alter database open resetlogs;
Database altered.
SQL> select * from hr.fruits;
NAME
----------
apple
banana
orange
grape
有一條記錄丟失，因為最後的聯機重做日誌檔案沒有被應用
再來一次，
[oracle@mylinux arch]$ ll
total 2064
-rw-r----- 1 oracle oinstall 555520 Oct 28 16:17 1_3_797547884.dbf
-rw-r----- 1 oracle oinstall 1547776 Oct 28 16:17 1_4_797547884.dbf
[oracle@mylinux orcl]$ cp /u01/backup/coldbackup/20121027_03/*.log . --從第三次冷備中拷貝
[oracle@mylinux orcl]$ cp /u01/backup/coldbackup/20121027_02/*.ctl . --從第二次冷備中拷貝
[oracle@mylinux orcl]$ cp /u01/backup/coldbackup/20121027_02/*.dbf . --從第二次冷備中拷貝
SQL> recover database using backup controlfile;
ORA-00279: change 2246274 generated at 10/27/2012 09:06:13 needed for thread 1
ORA-00289: suggestion : /u01/arch/1_4_797547884.dbf
ORA-00280: change 2246274 for thread 1 is in sequence #4
Specify log: {
ORA-00279: change 2246612 generated at 10/27/2012 09:11:18 needed for thread 1
ORA-00289: suggestion : /u01/arch/1_5_797547884.dbf
ORA-00280: change 2246612 for thread 1 is in sequence #5
ORA-00278: log file '/u01/arch/1_4_797547884.dbf' no longer needed for this
recovery
Specify log: {
cancel
Media recovery cancelled.
SQL> alter database open resetlogs;
Database altered.
SQL> select * from hr.fruits;
NAME
----------
apple
banana
orange
grape
事實證明：recover database using backup controlfile;輸入cancel與recover database using backup controlfile
until cancel;輸入cancel沒區別啊，
http://www.oracle.com.cn/viewthread.php?tid=113060&extra=&page=1那這個裡面說的實在是讓人費解了
3、第三種情況，資料檔案是第一次冷備時的，控制檔案是第二次冷備時的，聯機重做日誌檔案是第三次冷備時的
關閉資料庫
[oracle@mylinux arch]$ ll
total 2064
-rw-r----- 1 oracle oinstall 555520 Oct 28 16:17 1_3_797547884.dbf
-rw-r----- 1 oracle oinstall 1547776 Oct 28 16:17 1_4_797547884.dbf
[oracle@mylinux orcl]$ cp /u01/backup/coldbackup/20121027_03/*.log . --從第三次冷備中拷貝
[oracle@mylinux orcl]$ cp /u01/backup/coldbackup/20121027_02/*.ctl . --從第二次冷備中拷貝
[oracle@mylinux orcl]$ cp /u01/backup/coldbackup/20121027_01/*.dbf . --從第一次冷備中拷貝
startup
SQL> startup
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.
Total System Global Area 497995776 bytes
Fixed Size 1337464 bytes
Variable Size 377489288 bytes
Database Buffers 113246208 bytes
Redo Buffers 5922816 bytes
Database mounted.
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: '/u01/oradata/orcl/system01.dbf'
SQL> col name for a50
SQL> select name,checkpoint_change# from v$datafile;
NAME CHECKPOINT_CHANGE#
-------------------------------------------------- ------------------
/u01/oradata/orcl/system01.dbf 2246274
/u01/oradata/orcl/sysaux01.dbf 2246274
/u01/oradata/orcl/undotbs01.dbf 2246274
/u01/oradata/orcl/users01.dbf 2246274
/u01/oradata/orcl/example01.dbf 2246274
/u01/oradata/orcl/tbsmn01.dbf 2246274
/u01/oradata/orcl/tbsmn02.dbf 2246274
/u01/oradata/orcl/manualsegs01.dbf 2246274
/u01/oradata/orcl/autosegs01.dbf 2246274
/u01/oradata/orcl/rman01.dbf 2246274
/u01/oradata/orcl/hello.dbf 2246274
11 rows selected.
SQL> select name,checkpoint_change# from v$datafile_header;
NAME CHECKPOINT_CHANGE#
-------------------------------------------------- ------------------
/u01/oradata/orcl/system01.dbf 2245615
/u01/oradata/orcl/sysaux01.dbf 2245615
/u01/oradata/orcl/undotbs01.dbf 2245615
/u01/oradata/orcl/users01.dbf 2245615
/u01/oradata/orcl/example01.dbf 2245615
/u01/oradata/orcl/tbsmn01.dbf 2245615
/u01/oradata/orcl/tbsmn02.dbf 2245615
/u01/oradata/orcl/manualsegs01.dbf 2245615
/u01/oradata/orcl/autosegs01.dbf 2245615
/u01/oradata/orcl/rman01.dbf 2245615
/u01/oradata/orcl/hello.dbf 2245615
11 rows selected.
--資料檔案頭的SCN比控制檔案中的小，所以需要介質恢復
SQL> col error for a20
SQL> select * from v$recover_file;
FILE# ONLINE ONLINE_ ERROR CHANGE# TIME
---------- ------- ------- -------------------- ---------- ---------
1 ONLINE ONLINE 2245615 27-OCT-12
2 ONLINE ONLINE 2245615 27-OCT-12
3 ONLINE ONLINE 2245615 27-OCT-12
4 ONLINE ONLINE 2245615 27-OCT-12
5 ONLINE ONLINE 2245615 27-OCT-12
6 ONLINE ONLINE 2245615 27-OCT-12
7 ONLINE ONLINE 2245615 27-OCT-12
8 ONLINE ONLINE 2245615 27-OCT-12
9 ONLINE ONLINE 2245615 27-OCT-12
10 ONLINE ONLINE 2245615 27-OCT-12
11 ONLINE ONLINE 2245615 27-OCT-12
11 rows selected.
SQL> select * from v$recovery_log;
no rows selected
SQL> recover database;--本來這個操作是把日誌應用到控制檔案的最大SCN處，但是Oracle發現聯機重做日誌檔案比控制
檔案還要新，所以控制檔案是備份的控制檔案
ORA-00283: recovery session canceled due to errors
ORA-00338: log 4 of thread 1 is more recent than control file
ORA-00312: online log 4 thread 1: '/u01/oradata/orcl/redo04b.log'
ORA-00338: log 4 of thread 1 is more recent than control file
ORA-00312: online log 4 thread 1: '/u01/oradata/orcl/redo04.log'
但是我要求只要恢復到控制檔案的末尾就可以了，不恢復到聯機重做日誌檔案的末尾
SQL> recover database until cancel;
ORA-00283: recovery session canceled due to errors
ORA-01610: recovery using the BACKUP CONTROLFILE option must be done
SQL> recover database using backup controlfile;--我奇怪的是，資料檔案是第一次冷備時候的資料檔案，裡面根本就
沒有fruits這張表，這張表的建立以及前三條記錄的內容都是在這個1_3_797547884.dbf歸檔日誌裡面的，為什麼沒有提示
應用呢？
ORA-00279: change 2245615 generated at 10/27/2012 09:00:51 needed for thread 1
ORA-00289: suggestion : /u01/arch/1_4_797547884.dbf
ORA-00280: change 2245615 for thread 1 is in sequence #4
Specify log: {
--這裡其實我按了Enter鍵
ORA-00279: change 2246612 generated at 10/27/2012 09:11:18 needed for thread 1
ORA-00289: suggestion : /u01/arch/1_5_797547884.dbf
ORA-00280: change 2246612 for thread 1 is in sequence #5
ORA-00278: log file '/u01/arch/1_4_797547884.dbf' no longer needed for this
recovery
Specify log: {
--這裡其實我按了Enter鍵
ORA-00308: cannot open archived log '/u01/arch/1_5_797547884.dbf'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
SQL> recover database using backup controlfile;
ORA-00279: change 2246612 generated at 10/27/2012 09:11:18 needed for thread 1
ORA-00289: suggestion : /u01/arch/1_5_797547884.dbf
ORA-00280: change 2246612 for thread 1 is in sequence #5
Specify log: {
cancel --要求不應用聯機重做日誌檔案裡的內容
Media recovery cancelled.
SQL> alter database open resetlogs;--竟然成功了，按照那個網站上說的不是會出錯的嗎？不是要recover database
using backup controlfile until cancel才可以的嗎？
Database altered.
SQL> select * from hr.fruits;--最後一條記錄在聯機重做日誌裡的丟失了
NAME
--------------------------------------------------
apple
banana
orange
grape
SQL> select name,checkpoint_change# from v$datafile;
NAME CHECKPOINT_CHANGE#
-------------------------------------------------- ------------------
/u01/oradata/orcl/system01.dbf 2246616
/u01/oradata/orcl/sysaux01.dbf 2246616
/u01/oradata/orcl/undotbs01.dbf 2246616
/u01/oradata/orcl/users01.dbf 2246616
/u01/oradata/orcl/example01.dbf 2246616
/u01/oradata/orcl/tbsmn01.dbf 2246616
/u01/oradata/orcl/tbsmn02.dbf 2246616
/u01/oradata/orcl/manualsegs01.dbf 2246616
/u01/oradata/orcl/autosegs01.dbf 2246616
/u01/oradata/orcl/rman01.dbf 2246616
/u01/oradata/orcl/hello.dbf 2246616
11 rows selected.
SQL> select name,checkpoint_change# from v$datafile_header;
NAME CHECKPOINT_CHANGE#
-------------------------------------------------- ------------------
/u01/oradata/orcl/system01.dbf 2246616
/u01/oradata/orcl/sysaux01.dbf 2246616
/u01/oradata/orcl/undotbs01.dbf 2246616
/u01/oradata/orcl/users01.dbf 2246616
/u01/oradata/orcl/example01.dbf 2246616
/u01/oradata/orcl/tbsmn01.dbf 2246616
/u01/oradata/orcl/tbsmn02.dbf 2246616
/u01/oradata/orcl/manualsegs01.dbf 2246616
/u01/oradata/orcl/autosegs01.dbf 2246616
/u01/oradata/orcl/rman01.dbf 2246616
/u01/oradata/orcl/hello.dbf 2246616
11 rows selected.
具體我也不知道了，alter database open resetlogs;的時候應該是會判斷資料檔案頭的SCN和控制檔案中的是否一致，一
致的話就可以開啟資料庫，如果資料檔案頭的SCN小於控制檔案中的那麼就會提示還需要介質恢復的，具體情況具體分析
http://www.oracle.com.cn/viewthread.php?tid=113060&extra=&page=1
再來一次
[oracle@mylinux arch]$ ll
total 2064
-rw-r----- 1 oracle oinstall 555520 Oct 28 16:17 1_3_797547884.dbf
-rw-r----- 1 oracle oinstall 1547776 Oct 28 16:17 1_4_797547884.dbf
[oracle@mylinux orcl]$ cp /u01/backup/coldbackup/20121027_03/*.log . --從第三次冷備中拷貝
[oracle@mylinux orcl]$ cp /u01/backup/coldbackup/20121027_02/*.ctl . --從第二次冷備中拷貝
[oracle@mylinux orcl]$ cp /u01/backup/coldbackup/20121027_01/*.dbf . --從第一次冷備中拷貝
SQL> recover database using backup controlfile;
ORA-00279: change 2245011 generated at 10/27/2012 08:46:01 needed for thread 1
ORA-00289: suggestion : /u01/arch/1_3_797547884.dbf
ORA-00280: change 2245011 for thread 1 is in sequence #3
Specify log: {
ORA-00279: change 2245615 generated at 10/27/2012 09:00:51 needed for thread 1
ORA-00289: suggestion : /u01/arch/1_4_797547884.dbf
ORA-00280: change 2245615 for thread 1 is in sequence #4
ORA-00278: log file '/u01/arch/1_3_797547884.dbf' no longer needed for this
recovery
Specify log: {
cancel
Media recovery cancelled.
SQL> alter database open resetlogs;
Database altered.
SQL> select * from hr.fruits;
NAME
----------
apple
banana
orange
這次可以看到應用了1_3_797547884.dbf這個歸檔日誌檔案，我知道了，上一次也是這樣的步驟，但是在recover database
using backup controlfile;這個命令之前我使用過recover database這個命令，它已經把1_3_797547884.dbf應用進去了
，因為資料檔案比控制檔案舊，所以它會以控制檔案為目標進行應用，但是後來又發現聯機重做日誌檔案更新，所以它才
報錯，如果要繼續恢復資料，那麼就要用using backup controlfile;事實上上個實驗中recover database until cancel
（需不完全恢復，recover database完全恢復則不行）之後是可以使用alter database open resetlogs開啟資料庫的，只
不過恢復之後表裡只有三條資料，不信我們可以試下
再來一次，
[oracle@mylinux arch]$ ll
total 2064
-rw-r----- 1 oracle oinstall 555520 Oct 28 16:17 1_3_797547884.dbf
-rw-r----- 1 oracle oinstall 1547776 Oct 28 16:17 1_4_797547884.dbf
[oracle@mylinux orcl]$ cp /u01/backup/coldbackup/20121027_03/*.log . --從第三次冷備中拷貝
[oracle@mylinux orcl]$ cp /u01/backup/coldbackup/20121027_02/*.ctl . --從第二次冷備中拷貝
[oracle@mylinux orcl]$ cp /u01/backup/coldbackup/20121027_01/*.dbf . --從第一次冷備中拷貝
SQL> startup mount;
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.
Total System Global Area 497995776 bytes
Fixed Size 1337464 bytes
Variable Size 385877896 bytes
Database Buffers 104857600 bytes
Redo Buffers 5922816 bytes
Database mounted.
SQL> recover database until cancel;
ORA-00279: change 2245011 generated at 10/27/2012 08:46:01 needed for thread 1
ORA-00289: suggestion : /u01/arch/1_3_797547884.dbf
ORA-00280: change 2245011 for thread 1 is in sequence #3
Specify log: {
ORA-00279: change 2245615 generated at 10/27/2012 09:00:51 needed for thread 1
ORA-00289: suggestion : /u01/arch/1_4_797547884.dbf
ORA-00280: change 2245615 for thread 1 is in sequence #4
ORA-00278: log file '/u01/arch/1_3_797547884.dbf' no longer needed for this
recovery
Specify log: {
cancel
Media recovery cancelled.
SQL> alter database open resetlogs;
Database altered.
SQL> select * from hr.fruits;
NAME
----------
apple
banana
orange
看來我們的假設是成立的
總結下：
recover database;完全恢復
recover database until cancel;不完全恢復
這兩個命令都是用於在資料檔案頭的SCN小於控制檔案中記錄的資料檔案的SCN的情況下，目標是將資料檔案恢復到控制文
件中記錄的最大SCN為止
如果recover database之後資料檔案頭的SCN等於控制檔案中記錄的資料檔案的SCN，且應用完了所有的日誌，那麼資料庫
可以正常開啟，即noresetlogs開啟
如果recover database之後資料檔案頭的SCN等於控制檔案中記錄的資料檔案的SCN，但未應用完所有的日誌，那麼只能用
resetlogs方式開啟資料庫。
如果recover database之後資料檔案頭的SCN小於控制檔案中記錄的資料檔案的SCN，那麼是需要繼續進行介質恢復的
有些情況下可能recover database與recover database until cancel所應用的日誌是一樣的，但是為了使資料庫能以
resetlogs方式開啟，必須使用until cancel方式
recover database using backup controlfile;
recover database using backup controlfile until cancel;
這兩個命令都是以聯機重做日誌中記錄的最大SCN為終點來應用日誌（我的猜測，有些地方說是以資料檔案頭中的最大SCN
為終點來應用日誌，或者以資料檔案、聯機重做日誌檔案中較大的SCN作為終點）
下次試試聯機重做日誌檔案不是最新的時候，看看會發生什麼情況
SQL> show autorecovery;--預設是關閉的
autorecovery OFF
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26524307/viewspace-1061246/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 冷備控制檔案 & Using Backup Controlfile
- 引數檔案控制檔案和資料檔案丟失的恢復
- 無備份丟失部分資料檔案和控制檔案恢復 [轉]
- rman恢復:資料檔案丟失,控制檔案丟失,聯機日誌檔案丟失(非當前使用與當前使用)
- 新建的表空間(或資料檔案)丟失以及控制檔案丟失，有新建表空間(或資料檔案)前的控制文
- 控制檔案丟失恢復
- 【控制檔案丟失恢復】
- 重建控制檔案--alter database backup controlfile to traceDatabase
- 關於資料庫丟失控制檔案的測試資料庫
- 資料檔案損壞、丟失
- 模擬資料檔案丟失
- Controlfile和system01資料檔案丟失的恢復(透過standby)
- 控制檔案丟失恢復(二)
- 恢復丟失的控制檔案
- 控制檔案丟失處理方法
- 控制檔案全部丟失恢復
- 使用DBMS_BACKUP_RESTORE來還原控制檔案和資料檔案REST
- 關於丟失表空間資料檔案的處理方式
- 丟失一個控制檔案並恢復資料庫資料庫
- 資料檔案丟失的恢復
- dfm檔案資料丟失問題
- 資料檔案丟失如何恢復
- 控制檔案全部丟失的恢復
- 控制檔案部分丟失的恢復
- 控制檔案丟失的RMAN恢復
- 歸檔模式有備份丟失控制檔案和資料檔案後恢復模式
- Oracle恢復例項之一：資料檔案、控制檔案、聯機日誌丟失Oracle
- Oracle 控制檔案(CONTROLFILE)Oracle
- 恢復之丟失全部控制檔案以及備份中的控制檔案
- RMAN恢復案例:無恢復目錄，丟失全部資料檔案、控制檔案、日誌檔案恢復
- 當前控制檔案全部丟失恢復
- 開啟 控制檔案自動備份下，引數檔案、控制檔案全部丟失恢復
- [20141106]建立控制檔案與丟失資料檔案問題
- 【原創】模擬控制檔案丟失的資料庫恢復資料庫
- rman 恢復---歸檔丟失and資料檔案損壞
- 歸檔模式下資料檔案丟失的恢復模式
- 同時丟失控制檔案與資料檔案的恢復
- Oracle 11g重建控制檔案——控制檔案全部丟失，從零開始Oracle