分享程序员开发的那些事...
更新时间：2022-09-13 10:31:48
[20150309]使用冷备份做恢复的问题.txt
--做一个例子，说明冷备份做不完全恢复的问题。
1.测试环境：
SCOTT@test> @ &r/ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- ----------------------------------------------------------------
x86_64/Linux 2.4.xx 10.2.0.4.0 Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
create table t1 as select rownum id ,'test' data from dual ;
SYS@test> shutdown immediate ;
Database closed.
Database dismounted.
ORACLE instance shut down.
2.开始测试
--做1个冷备份。
$ cp -a test test0309
SYS@test> startup
ORACLE instance started.
Total System Global Area 473956352 bytes
Fixed Size 2084776 bytes
Variable Size 230686808 bytes
Database Buffers 230686720 bytes
Redo Buffers 10498048 bytes
Database mounted.
Database opened.
SCOTT@test> select current_scn ,sysdate from v$database ;
CURRENT_SCN SYSDATE
------------ -------------------
11997339850 2015-03-09 16:28:04
SCOTT@test> insert into t1 values (2,'aaaa');
1 row created.
SCOTT@test> commit ;
Commit complete.
SYS@test> shutdown immediate ;
Database closed.
Database dismounted.
ORACLE instance shut down.
3.开始使用冷备份恢复：
$ mv test test.good
$ mv test0309 test
--注意还有把redo拷贝过来，否则是不能恢复的。
$ cd test
$ mkdir redo.old
$ mv redo0* redo.old
$ cp ../test.good/redo0* .
4.开始恢复：
SYS@test> startup mount
ORACLE instance started.
Total System Global Area 473956352 bytes
Fixed Size 2084776 bytes
Variable Size 230686808 bytes
Database Buffers 230686720 bytes
Redo Buffers 10498048 bytes
Database mounted.
SYS@test> recover database until scn 11997339850;
ORA-00277: illegal option to the UNTIL recovery flag SCN
--看来以后应该使用scn号，问题更好定位。
SYS@test> recover database until time '2015-03-09 16:28:04';
ORA-00283: recovery session canceled due to errors
ORA-38760: This database instance failed to turn on flashback database
SYS@test> alter database flashback off;
Database altered.
SYS@test> recover database until time '2015-03-09 16:28:04';
Media recovery complete.
SYS@test> alter database open read only ;
Database altered.
SYS@test> select * from scott.t1;
ID DATA
------------ ----
1 test
--可以发现是成功的。实际上面的recover什么都没有做。
SYS@test> shutdown immediate ;
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@test> startup mount
ORACLE instance started.
Total System Global Area 473956352 bytes
Fixed Size 2084776 bytes
Variable Size 230686808 bytes
Database Buffers 230686720 bytes
Redo Buffers 10498048 bytes
Database mounted.
SYS@test> recover database until cancel ;
Media recovery complete.
SYS@test> alter database open read only ;
Database altered.
SYS@test> select * from scott.t1;
ID DATA
------------ ----
1 test
--可以发现并没有恢复后来插入的记录。因为我们使用的是冷备份，是正常关闭数据库的备份。
--继续测试：
SYS@test> shutdown immediate ;
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@test> startup mount
ORACLE instance started.
Total System Global Area 473956352 bytes
Fixed Size 2084776 bytes
Variable Size 230686808 bytes
Database Buffers 230686720 bytes
Redo Buffers 10498048 bytes
Database mounted.
SYS@test> recover database until time '2015-03-11 16:28:04';
Media recovery complete.
SYS@test> recover database until time '2015-03-12 16:28:04';
Media recovery complete.
--可以发现即使我使用的时间是明天的日期，提示也是Media recovery complete.实际上如果看alert*.log文件。
ALTER DATABASE RECOVER database until time '2015-03-12 16:28:04'
Mon Mar 9 16:43:35 2015
Media Recovery Start
parallel recovery started with 16 processes
Media Recovery Not Required
Completed: ALTER DATABASE RECOVER database until time '2015-03-12 16:28:04'
--可以发现实际上什么都没有做。使用rman也一样。
RMAN> recover database ;
Starting recover at 2015-03-09 16:46:42
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=151 devtype=DISK
starting media recovery
media recovery complete, elapsed time: 00:00:00
Finished recover at 2015-03-09 16:46:43
SYS@test> alter database open read only ;
Database altered.
SYS@test> select * from scott.t1;
ID DATA
------------ ----
1 test
--如果打开数据库提示：
SYS@test> alter database open ;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
5.正确的做法是：
SYS@test> recover database until cancel using backup controlfile;
ORA-00279: change 11997339705 generated at 03/09/2015 16:24:54 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/flash_recovery_area/TEST/archivelog/2015_03_09/o1_mf_1_71_%u_.arc
ORA-00280: change 11997339705 for thread 1 is in sequence #71
Specify log: {=suggested | filename | AUTO | CANCEL}
auto
ORA-00308: cannot open archived log '/u01/app/oracle/flash_recovery_area/TEST/archivelog/2015_03_09/o1_mf_1_71_%u_.arc'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
SYS@test> @ &r/logfile ;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME GROUP# STATUS TYPE MEMBER IS_
------ ------- --------- ---------- ------- --- ---------- ------------- ------------------- ------ ---------- ---------- ----------------------------- ---
1 1 70 52428800 1 YES INACTIVE 11997254482 2015-03-07 11:00:42 1 ONLINE /mnt/ramdisk/test/redo01.log NO
2 1 71 52428800 1 NO CURRENT 11997305678 2015-03-08 19:00:22 2 ONLINE /mnt/ramdisk/test/redo02.log NO
3 1 69 52428800 1 YES INACTIVE 11997227573 2015-03-06 22:00:09 3 ONLINE /mnt/ramdisk/test/redo03.log NO
SYS@test> recover database until cancel using backup controlfile;
ORA-00279: change 11997339705 generated at 03/09/2015 16:24:54 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/flash_recovery_area/TEST/archivelog/2015_03_09/o1_mf_1_71_%u_.arc
ORA-00280: change 11997339705 for thread 1 is in sequence #71
Specify log: {=suggested | filename | AUTO | CANCEL}
/mnt/ramdisk/test/redo02.log
Log applied.
Media recovery complete.
SYS@test> alter database open read only ;
Database altered.
SYS@test> select * from scott.t1;
ID DATA
------------ ----
1 test
2 aaaa
--实际上正确理解oracle数据库，以上问题的解决思路很好理解。这种方式视乎rman不好解决，只能取一个控制文件的备份来恢复数据库。
--因为冷备份的控制文件数据文件都是一致的，ok的。
--还有什么方法在rman下解决问题呢？我不知道。。。。