ITPub博客

首页 > 数据库 > Oracle > 关于using backup controlfile(丢失控制文件和数据文件)

关于using backup controlfile(丢失控制文件和数据文件)

Oracle 作者:零度的雨1205 时间:2019-11-30 16:57:02 0 删除 编辑

转载自:  http://blog.itpub.net/26524307/viewspace-1061246/

先确保数据库处于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没区别啊,
那这个里面说的实在是让人费解了

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小于控制文件中的那么就会提示还需要介质恢复的,具体情况具体分析


再来一次
[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/69902769/viewspace-2666349/,如需转载,请注明出处,否则将追究法律责任。

上一篇: 搭建12c em express
请登录后发表评论 登录
全部评论
本人的所有文章,仅代表一个学习的过程,其中不乏有错误之处,欢迎指正!

注册时间:2018-12-14

  • 博文量
    34
  • 访问量
    46233