联系：手机/微信(+86 17813235971) QQ(107644445)
作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
数据库版本
SQL> select * from v$version; BANNER -------------------------------------------------------------------------------- Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production PL/SQL Release 11.2.0.3.0 - Production CORE 11.2.0.3.0 Production TNS for Linux: Version 11.2.0.3.0 - Production NLSRTL Version 11.2.0.3.0 - Production
全备数据库
RMAN> backup as compressed backupset database format '/u01/oracle/oradata/tmp/ora11g_0_%U'; Starting backup at 18-APR-12 using channel ORA_DISK_1 channel ORA_DISK_1: starting compressed full datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set input datafile file number=00001 name=/u01/oracle/oradata/ora11g/system01.dbf input datafile file number=00002 name=/u01/oracle/oradata/ora11g/sysaux01.dbf input datafile file number=00003 name=/u01/oracle/oradata/ora11g/undotbs01.dbf input datafile file number=00004 name=/u01/oracle/oradata/ora11g/users01.dbf input datafile file number=00006 name=/u01/oracle/oradata/ora11g/xifenfei02.dbf channel ORA_DISK_1: starting piece 1 at 18-APR-12 channel ORA_DISK_1: finished piece 1 at 18-APR-12 piece handle=/u01/oracle/oradata/tmp/ora11g_0_07n8p916_1_1 tag=TAG20120418T234958 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:01:15 channel ORA_DISK_1: starting compressed full datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set including current control file in backup set including current SPFILE in backup set channel ORA_DISK_1: starting piece 1 at 18-APR-12 channel ORA_DISK_1: finished piece 1 at 18-APR-12 piece handle=/u01/oracle/oradata/tmp/ora11g_0_08n8p93h_1_1 tag=TAG20120418T234958 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01 Finished backup at 18-APR-12
创建测试数据库验证恢复标准
SQL> conn chf/xifenfei
Connected.
SQL> drop table xifenfei purge;
Table dropped.
SQL> create table xifenfei as
2 select * from dba_objects;
Table created.
SQL> insert into xifenfei
2 select * from dba_objects;
74534 rows created.
SQL> /
74534 rows created.
SQL> /
74534 rows created.
SQL> commit;
Commit complete.
SQL> select count(*) from xifenfei;
COUNT(*)
----------
298136
SQL> create table xifenfei01 as
2 select * from dba_objects;
Table created.
SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL> create table chf.xifenfei02 as
2 select * from dba_objects;
Table created.
SQL> alter system switch logfile;
System altered.
异机恢复库
RMAN> restore controlfile from '/u01/oracle/oradata/tmp/ora11g_0_08n8p93h_1_1';
Starting restore at 19-APR-12
using channel ORA_DISK_1
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/u01/oracle/oradata/ora11g/control01.ctl
output file name=/u01/oracle/oradata/ora11g/control02.ctl
Finished restore at 19-APR-12
RMAN> alter database mount;
database mounted
released channel: ORA_DISK_1
RMAN> list backup;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
7 Full 262.79M DISK 00:01:08 18-APR-12
BP Key: 7 Status: AVAILABLE Compressed: YES Tag: TAG20120418T234958
Piece Name: /u01/oracle/oradata/tmp/ora11g_0_07n8p916_1_1
List of Datafiles in backup set 7
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 1155510 18-APR-12 /u01/oracle/oradata/ora11g/system01.dbf
2 Full 1155510 18-APR-12 /u01/oracle/oradata/ora11g/sysaux01.dbf
3 Full 1155510 18-APR-12 /u01/oracle/oradata/ora11g/undotbs01.dbf
4 Full 1155510 18-APR-12 /u01/oracle/oradata/ora11g/users01.dbf
6 Full 1155510 18-APR-12 /u01/oracle/oradata/ora11g/xifenfei02.dbf
--这里可以发现备份时的scn,增量备份时取这里的最小scn为起点
--为了排除影响,删除所有归档日志记录
RMAN> delete archivelog all;
RMAN> list archivelog all;
specification does not match any archived log in the repository
RMAN> restore database;
Starting restore at 19-APR-12
using channel ORA_DISK_1
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 00001 to /u01/oracle/oradata/ora11g/system01.dbf
channel ORA_DISK_1: restoring datafile 00002 to /u01/oracle/oradata/ora11g/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /u01/oracle/oradata/ora11g/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /u01/oracle/oradata/ora11g/users01.dbf
channel ORA_DISK_1: restoring datafile 00006 to /u01/oracle/oradata/ora11g/xifenfei02.dbf
channel ORA_DISK_1: reading from backup piece /u01/oracle/oradata/tmp/ora11g_0_07n8p916_1_1
channel ORA_DISK_1: piece handle=/u01/oracle/oradata/tmp/ora11g_0_07n8p916_1_1 tag=TAG20120418T234958
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:01:36
Finished restore at 19-APR-12
[oracle@xifenfei oradata]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Thu Apr 19 00:54:42 2012
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select file#,to_char(checkpoint_change#,'9999999999999999') "SCN",
2 to_char(RESETLOGS_CHANGE#,'9999999999999999') "RESETLOGS SCN"
3 from v$datafile_header;
FILE# SCN RESETLOGS SCN
---------- ---------------------------------- ----------------------------------
1 1155510 787897
2 1155510 787897
3 1155510 787897
4 1155510 787897
6 1155510 787897
SQL> select file#,to_char(checkpoint_change#,'999999999999999') "SCN",
2 to_char(last_change#,'999999999999999')"STOP_SCN" from v$datafile;
FILE# SCN STOP_SCN
---------- -------------------------------- --------------------------------
1 1155510
2 1155510
3 1155510
4 1155510
6 1155510
基于scn增量备份
RMAN> BACKUP INCREMENTAL FROM SCN 1155510 DATABASE 2> FORMAT '/u01/oracle/oradata/tmp/ora11_scn_%U' tag 'XIFENFEI'; Starting backup at 19-APR-12 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=125 device type=DISK backup will be obsolete on date 26-APR-12 archived logs will not be kept or backed up channel ORA_DISK_1: starting full datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set input datafile file number=00001 name=/u01/oracle/oradata/ora11g/system01.dbf input datafile file number=00002 name=/u01/oracle/oradata/ora11g/sysaux01.dbf input datafile file number=00003 name=/u01/oracle/oradata/ora11g/undotbs01.dbf input datafile file number=00004 name=/u01/oracle/oradata/ora11g/users01.dbf input datafile file number=00006 name=/u01/oracle/oradata/ora11g/xifenfei02.dbf channel ORA_DISK_1: starting piece 1 at 19-APR-12 channel ORA_DISK_1: finished piece 1 at 19-APR-12 piece handle=/u01/oracle/oradata/tmp/ora11_scn_0bn8pbsd_1_1 tag=XIFENFEI comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:45 using channel ORA_DISK_1 backup will be obsolete on date 26-APR-12 archived logs will not be kept or backed up channel ORA_DISK_1: starting full datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set including current control file in backup set channel ORA_DISK_1: starting piece 1 at 19-APR-12 channel ORA_DISK_1: finished piece 1 at 19-APR-12 piece handle=/u01/oracle/oradata/tmp/ora11_scn_0cn8pbtq_1_1 tag=XIFENFEI comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01 Finished backup at 19-APR-12
增量恢复
RMAN> catalog start with '/u01/oracle/oradata/tmp/ora11_scn_09n8pa5h_1_1';
searching for all files that match the pattern /u01/oracle/oradata/tmp/ora11_scn_09n8pa5h_1_1
List of Files Unknown to the Database
=====================================
File Name: /u01/oracle/oradata/tmp/ora11_scn_09n8pa5h_1_1
Do you really want to catalog the above files (enter YES or NO)? yes
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: /u01/oracle/oradata/tmp/ora11_scn_09n8pa5h_1_1
RMAN> recover database;
Starting recover at 19-APR-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=125 device type=DISK
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00001: /u01/oracle/oradata/ora11g/system01.dbf
destination for restore of datafile 00002: /u01/oracle/oradata/ora11g/sysaux01.dbf
destination for restore of datafile 00003: /u01/oracle/oradata/ora11g/undotbs01.dbf
destination for restore of datafile 00004: /u01/oracle/oradata/ora11g/users01.dbf
destination for restore of datafile 00006: /u01/oracle/oradata/ora11g/xifenfei02.dbf
channel ORA_DISK_1: reading from backup piece /u01/oracle/oradata/tmp/ora11_scn_0bn8pbsd_1_1
channel ORA_DISK_1: piece handle=/u01/oracle/oradata/tmp/ora11_scn_0bn8pbsd_1_1.bak tag=XIFENFEI
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07
starting media recovery
--这里表现出来的是利用归档日志恢复，实质是基于scn增量备份的备份集恢复
archived log file name=/u01/oracle/oradata/archivelog/ora11g/1_49_777766629.dbf thread=1 sequence=49
archived log file name=/u01/oracle/oradata/archivelog/ora11g/1_50_777766629.dbf thread=1 sequence=50
archived log file name=/u01/oracle/oradata/archivelog/ora11g/1_51_777766629.dbf thread=1 sequence=51
archived log file name=/u01/oracle/oradata/archivelog/ora11g/1_52_777766629.dbf thread=1 sequence=52
archived log file name=/u01/oracle/oradata/archivelog/ora11g/1_53_777766629.dbf thread=1 sequence=53
archived log file name=/u01/oracle/oradata/archivelog/ora11g/1_54_777766629.dbf thread=1 sequence=54
archived log file name=/u01/oracle/oradata/archivelog/ora11g/1_55_777766629.dbf thread=1 sequence=55
archived log file name=/u01/oracle/oradata/archivelog/ora11g/1_56_777766629.dbf thread=1 sequence=56
archived log file name=/u01/oracle/oradata/archivelog/ora11g/1_57_777766629.dbf thread=1 sequence=57
unable to find archived log
archived log thread=1 sequence=58
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 04/19/2012 00:55:48
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 58 and starting SCN of 1157379
SQL> select file#,to_char(checkpoint_change#,'9999999999999999') "SCN",
2 to_char(RESETLOGS_CHANGE#,'9999999999999999') "RESETLOGS SCN"
3 from v$datafile_header;
FILE# SCN RESETLOGS SCN
---------- ---------------------------------- ----------------------------------
1 1157379 787897
2 1157379 787897
3 1157379 787897
4 1157379 787897
6 1157379 787897
SQL> select file#,to_char(checkpoint_change#,'999999999999999') "SCN",
2 to_char(last_change#,'999999999999999')"STOP_SCN" from v$datafile;
FILE# SCN STOP_SCN
---------- -------------------------------- --------------------------------
1 1157379
2 1157379
3 1157379
4 1157379
6 1157379
SQL> alter database open resetlogs;
Database altered.
SQL> select count(*) from chf.xifenfei;
COUNT(*)
----------
298136
SQL> select TABLE_NAME from dba_tables where table_name LIKE 'XIFENFEI%';
TABLE_NAME
------------------------------------------------------------
XIFENFEI02
XIFENFEI01
XIFENFEI
使用rman基于scn实现数据库增量恢复是在dg中修复gap的时候常见的方法,其实该方法也可以使用常规的增量恢复,通过人工控制,实现数据库的某种特殊的业务需求(特殊的数据迁移).处理思路主要是获得备库的数据文件最小scn(这个scn可能是通过全备恢复或者增量恢复产生),然后基于该SCN实现数据库增量备份,然后利用该备份进行增量恢复.
加我QQ
hnhycwh,
是有可能出现不能正常open的情况，最后可能需要归档日志或者resetlogs打开
我数据库是10.2.4的，按照你的做实验，恢复数据后打开
ERROR at line 1:
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: ‘/u01/app/oracle/oradata/hhps/system01.dbf’