--整理以前的学习笔记
先确保数据库处于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: {=suggested | filename | AUTO | CANCEL}
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: {=suggested | filename | AUTO | CANCEL}
/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: {=suggested | filename | AUTO | CANCEL}
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: {=suggested | filename | AUTO | CANCEL}
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: {=suggested | filename | AUTO | CANCEL}
/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: {=suggested | filename | AUTO | CANCEL}
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: {=suggested | filename | AUTO | CANCEL}
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: {=suggested | filename | AUTO | 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
ORA-00278: log file '/u01/arch/1_4_797547884.dbf' no longer needed for this
recovery
Specify log: {=suggested | filename | AUTO | CANCEL}
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: {=suggested | filename | AUTO | 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
ORA-00278: log file '/u01/arch/1_4_797547884.dbf' no longer needed for this
recovery
Specify log: {=suggested | filename | AUTO | CANCEL}
--这里其实我按了回车键
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: {=suggested | filename | AUTO | CANCEL}
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: {=suggested | filename | AUTO | CANCEL}
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: {=suggested | filename | AUTO | CANCEL}
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: {=suggested | filename | AUTO | CANCEL}
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: {=suggested | filename | AUTO | CANCEL}
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/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/26524307/viewspace-1061246/
134
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
