[20170213]现有控制文件恢复到以前时间点
[20170213]现有控制文件恢复到以前时间点.txt
--如果使用现有控制文件是否可以恢复以前的时间点?假设这个时间点之后还增加了数据文件,这样新的控制文件里面有记录.
--恢复后是什么情况呢? 通过真实的测试来说明问题.
--一般我做全备份完成后会在脚本最后加入备份当前控制文件的命令,如果我需要恢复,我会选择这个控制文件来恢复.
--而上面的情况呢?
1.环境:
SYS@book> @ &r/ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
2.测试前准备:
create table t1 (id number,d date);
insert into t1 select current_scn,sysdate from v$database ;
commit ;
SCOTT@book> set numw 12
SCOTT@book> select * from t1;
ID D
------------ -------------------
13276257537 2017-02-13 15:07:28
--//使用rman做全备.
RMAN> backup database format '/u01/backup/full_20170213_%U';
RMAN> backup archivelog all not backed up 1 times tag='archivelog 20170213' format '/u01/backup/archive_20170213_%U';
insert into t1 select current_scn,sysdate from v$database ;
commit ;
--//建立新的数据文件以及表空间.
CREATE TABLESPACE TEA DATAFILE
'/mnt/ramdisk/book/tea01.dbf' SIZE 40M AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED
LOGGING
ONLINE
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT MANUAL
FLASHBACK ON;
insert into t1 select current_scn,sysdate from v$database ;
commit ;
SCOTT@book> create table tt tablespace tea as select * from emp ;
Table created.
insert into t1 select current_scn,sysdate from v$database ;
commit ;
SCOTT@book> select * from t1 order by 1;
ID D
------------ -------------------
13276257537 2017-02-13 15:07:28 -->备份前
13276257730 2017-02-13 15:09:29 -->备份后
13276258059 2017-02-13 15:10:25 -->备份后并且增加1个数据文件.
13276258118 2017-02-13 15:11:05 -->往增加的表空间建立表tt.
3.关闭数据库仅仅保留控制文件.
--//假设现在要恢复到scn=13276257730.首先做一个冷备份略.
--//删除数据文件以及日志文件:
$ cd /mnt/ramdisk/book
$ rm -f *.dbf
$ rm -f *.log
$ ls -l
total 20952
-rw-r----- 1 oracle oinstall 10698752 2017-02-13 15:13:05 control01.ctl
-rw-r----- 1 oracle oinstall 10698752 2017-02-13 15:13:05 control02.ctl
--//仅仅保留控制文件.
4.开始恢复:
--//恢复到scn=13276257730
$ cat 13276257730.rman
run {
set until scn 13276257730;
restore database;
recover database;
}
RMAN> startup mount ;
Oracle instance started
database mounted
Total System Global Area 634732544 bytes
Fixed Size 2255792 bytes
Variable Size 197133392 bytes
Database Buffers 427819008 bytes
Redo Buffers 7524352 bytes
RMAN> run {
2> set until scn 13276257730;
3> restore database;
4> recover database;
5> }
executing command: SET until clause
Starting restore at 2017-02-13 15:21:10
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=254 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=1 device type=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=12 device type=DISK
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00003 to /mnt/ramdisk/book/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /mnt/ramdisk/book/users01.dbf
channel ORA_DISK_1: reading from backup piece /u01/backup/full_20170213_f0rsfurm_1_1
channel ORA_DISK_2: starting datafile backup set restore
channel ORA_DISK_2: specifying datafile(s) to restore from backup set
channel ORA_DISK_2: restoring datafile 00001 to /mnt/ramdisk/book/system01.dbf
channel ORA_DISK_2: restoring datafile 00005 to /mnt/ramdisk/book/example01.dbf
channel ORA_DISK_2: reading from backup piece /u01/backup/full_20170213_eursfurm_1_1
channel ORA_DISK_3: starting datafile backup set restore
channel ORA_DISK_3: specifying datafile(s) to restore from backup set
channel ORA_DISK_3: restoring datafile 00002 to /mnt/ramdisk/book/sysaux01.dbf
channel ORA_DISK_3: reading from backup piece /u01/backup/full_20170213_evrsfurm_1_1
channel ORA_DISK_1: piece handle=/u01/backup/full_20170213_f0rsfurm_1_1 tag=TAG20170213T150838
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
channel ORA_DISK_3: piece handle=/u01/backup/full_20170213_evrsfurm_1_1 tag=TAG20170213T150838
channel ORA_DISK_3: restored backup piece 1
channel ORA_DISK_3: restore complete, elapsed time: 00:00:01
channel ORA_DISK_2: piece handle=/u01/backup/full_20170213_eursfurm_1_1 tag=TAG20170213T150838
channel ORA_DISK_2: restored backup piece 1
channel ORA_DISK_2: restore complete, elapsed time: 00:00:03
Finished restore at 2017-02-13 15:21:15
Starting recover at 2017-02-13 15:21:15
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
starting media recovery
archived log for thread 1 with sequence 469 is already on disk as file /u01/app/oracle/archivelog/book/1_469_896605872.dbf
archived log file name=/u01/app/oracle/archivelog/book/1_469_896605872.dbf thread=1 sequence=469
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 02/13/2017 15:21:17
ORA-00283: recovery session canceled due to errors
RMAN-11003: failure during parse/execution of SQL statement: alter database recover logfile '/u01/app/oracle/archivelog/book/1_469_896605872.dbf'
ORA-00283: recovery session canceled due to errors
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '/mnt/ramdisk/book/redo01.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
RMAN> **end-of-file**
--//昏,忘记切换了.
SYS@book> recover database until change 13276257730;
ORA-00283: recovery session canceled due to errors
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '/mnt/ramdisk/book/redo01.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
--//昏也不行.测试疏忽了.先从冷备份恢复过来看看.
$ cp /u01/backup/20170213/redo01.log /mnt/ramdisk/book/
RMAN> @ 13276257730.rman
RMAN> run {
2> set until scn 13276257730;
3> #restore database;
4> recover database;
5> }
executing command: SET until clause
Starting recover at 2017-02-13 15:31:16
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=254 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=1 device type=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=12 device type=DISK
starting media recovery
media recovery complete, elapsed time: 00:00:01
Finished recover at 2017-02-13 15:31:18
RMAN> **end-of-file**
RMAN> report schema ;
Report of database schema for database with db_unique_name BOOK
List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 760 SYSTEM *** /mnt/ramdisk/book/system01.dbf
2 930 SYSAUX *** /mnt/ramdisk/book/sysaux01.dbf
3 350 UNDOTBS1 *** /mnt/ramdisk/book/undotbs01.dbf
4 570 USERS *** /mnt/ramdisk/book/users01.dbf
5 346 EXAMPLE *** /mnt/ramdisk/book/example01.dbf
6 0 TEA *** /mnt/ramdisk/book/tea01.dbf
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 20 TEMP 32767 /mnt/ramdisk/book/temp01.dbf
--//因为控制文件有记录,这里显示size=0.
SYS@book> alter system set log_archive_dest_state_2=defer ;
System altered.
SYS@book> alter database open read only ;
alter database open read only
*
ERROR at line 1:
ORA-16005: database requires recovery
--无法只读打开.
SYS@book> alter database open resetlogs;
Database altered.
RMAN> report schema ;
using target database control file instead of recovery catalog
Report of database schema for database with db_unique_name BOOK
List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 760 SYSTEM *** /mnt/ramdisk/book/system01.dbf
2 930 SYSAUX *** /mnt/ramdisk/book/sysaux01.dbf
3 350 UNDOTBS1 *** /mnt/ramdisk/book/undotbs01.dbf
4 570 USERS *** /mnt/ramdisk/book/users01.dbf
5 346 EXAMPLE *** /mnt/ramdisk/book/example01.dbf
List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 20 TEMP 32767 /mnt/ramdisk/book/temp01.dbf
--//可以发现open resetlog后正常.
SYS@book> SELECT file#, CHECKPOINT_CHANGE#,CHECKPOINT_TIME,UNRECOVERABLE_CHANGE#,UNRECOVERABLE_TIME,LAST_CHANGE#,LAST_TIME, OFFLINE_CHANGE#, ONLINE_CHANGE#,status,name FROM v$datafile;
FILE# CHECKPOINT_CHANGE# CHECKPOINT_TIME UNRECOVERABLE_CHANGE# UNRECOVERABLE_TIME LAST_CHANGE# LAST_TIME OFFLINE_CHANGE# ONLINE_CHANGE# STATUS NAME
----- ------------------ ------------------- --------------------- ------------------- ------------ ------------------- --------------- -------------- ------- --------------------------------------------------
1 13276257734 2017-02-13 15:35:01 0 13276257730 13276257731 SYSTEM /mnt/ramdisk/book/system01.dbf
2 13276257734 2017-02-13 15:35:01 0 13276257730 13276257731 ONLINE /mnt/ramdisk/book/sysaux01.dbf
3 13276257734 2017-02-13 15:35:01 0 13276257730 13276257731 ONLINE /mnt/ramdisk/book/undotbs01.dbf
4 13276257734 2017-02-13 15:35:01 0 13276257730 13276257731 ONLINE /mnt/ramdisk/book/users01.dbf
5 13276257734 2017-02-13 15:35:01 0 2017-01-16 22:00:05 13276257730 13276257731 ONLINE /mnt/ramdisk/book/example01.dbf
SYS@book> SELECT file#, CHECKPOINT_CHANGE#, CHECKPOINT_TIME,CREATION_CHANGE# , RESETLOGS_CHANGE#,status, CHECKPOINT_COUNT,fuzzy,name,tablespace_name FROM v$datafile_header;
FILE# CHECKPOINT_CHANGE# CHECKPOINT_TIME CREATION_CHANGE# RESETLOGS_CHANGE# STATUS CHECKPOINT_COUNT FUZ NAME TABLESPACE_NAME
----- ------------------ ------------------- ---------------- ----------------- ------- ---------------- --- -------------------------------------------------- ------------------------------
1 13276257734 2017-02-13 15:35:01 7 13276257731 ONLINE 631 YES /mnt/ramdisk/book/system01.dbf SYSTEM
2 13276257734 2017-02-13 15:35:01 1834 13276257731 ONLINE 620 YES /mnt/ramdisk/book/sysaux01.dbf SYSAUX
3 13276257734 2017-02-13 15:35:01 923328 13276257731 ONLINE 541 YES /mnt/ramdisk/book/undotbs01.dbf UNDOTBS1
4 13276257734 2017-02-13 15:35:01 16143 13276257731 ONLINE 625 YES /mnt/ramdisk/book/users01.dbf USERS
5 13276257734 2017-02-13 15:35:01 952916 13276257731 ONLINE 537 YES /mnt/ramdisk/book/example01.dbf EXAMPLE
SYS@book> select * from scott.t1;
ID D
------------ -------------------
13276257537 2017-02-13 15:07:28
--^_^,应该在增加scn才能看到第2条记录.
5.还原:
SYS@book> shutdown abort ;
ORACLE instance shut down.
$ cp /u01/backup/20170213/* /mnt/ramdisk/book/ */
SYS@book> alter system set log_archive_dest_state_2=enable ;
System altered.
--从测试看是没有什么问题的.