Using Rman to implement incremental database recovery based on SCN is a common method of repairing gap in DG. In fact, this method can also use the conventional incremental recovery, through manual control, to achieve a certain specific business requirements of the database (special Data Migration). The main approach is to get the data files of the backup minimum SCN (This SCN can be generated either through a full recovery or incremental restore), and then implement an incremental database backup based on the SCN, and then use that backup for incremental recovery.
Database version
| The code is as follows |
Copy Code |
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 |
Fully-prepared database
| The code is as follows |
Copy Code |
| 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 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 |
To create a test database validation recovery standard
| The code is as follows |
Copy Code |
| 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. |
Heterogeneous Recovery Library
| The code is as follows |
Copy Code |
| 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
--Here you can find the SCN at backup time, take the smallest SCN here as the starting point for incremental backups
--Delete all archived logging to eliminate impact
rman> Delete Archivelog all;
rman> list Archivelog all;
Specification does not match no 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, 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 |
Incremental backup based on SCN
| The code is as follows |
Copy Code |
| 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 would be obsolete on date 26-apr-12
Archived logs won't is 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 would be obsolete on date 26-apr-12
Archived logs won't is kept or backed up
Channel ora_disk_1:starting full DataFile backup set
Channel ora_disk_1:specifying DataFile (s) in backup set
Including 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 |
Incremental recovery
| The code is as follows |
Copy Code |
| 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
--This shows the use of archived log recovery, essentially a backup set recovery based on the SCN incremental backup
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 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 |
