ORACLE异机数据库基于时间点的恢复 (DBPITR) Method

Restore RMAN backup to another server ,Database Point in Time Recovery (DBPITR) Method
DBPITR enables you to recover a database to some time in the past. For Example, if a logical error occurred today at 7:30 AM, DBPITR would enable you to restore the entire database to the state it was in 07:29 AM there by removing the effect of the error but also remove all other valid updates that occurred since 07:29 AM.
Flashback Technology provided by Oracle is one of the most useful in a production environment. It is a life saver. You can even rollback a query to a point in time without requiring traditional database restore. But first you have to enable flashback. when you logical error and do not enable flashback before that and undo has been covered flashback query unable meet demand . you should do a DBPITR or TSPITR.I will demonstrate below how to do flashback a database to a point in time.
Premise ORACLE Softwared has been install, and ORACLE_SID,ORACLE_BASE,ORACLE_HOME,LD_LIBRARY_PATH Environment Variables had configured, modify ORACLE_SID, update original backup file to the new device(target Host) contain RMAN level 0 backup and incremental backup if you used and Archived redo logs that must be restored to recover the instance to specified point in time..

$ export ORACLE_SID=pora40
复制

$ rman target /
Recovery Manager: Release 11.2.0.3.0 - Production on Wed May 21 17:23:01 2014
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database (not started)
RMAN> startup nomount force
Restore the SPFILE

RMAN> restore spfile from '/backup/db40/o1_mf_s_848106318_9qq2th09_.bkp';
复制

Create the PFILE from SPFILE and make parameter changes as required

SQL> sqlplus / as sysdba
SQL> create pfile from spfile;
复制

$ cd $ORACLE_HOME/dbs/
# example
[oracle@zyy-jilin dbs]$ cat initpora40.ora
*.audit_file_dest='/oracle/admin/pora40/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_file_record_keep_time=30
*.control_files='/oradata/db40/control01.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='pora40'
*.db_recovery_file_dest='/oracle/fast_recovery_area'
*.db_recovery_file_dest_size=8516534272
*.diagnostic_dest='/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=pora40XDB)'
*.nls_language='SIMPLIFIED CHINESE'
*.nls_territory='CHINA'
*.open_cursors=300
*.processes=1000
*.remote_login_passwordfile='EXCLUSIVE'
*.session_cached_cursors=100
*.sessions=1105
*.undo_retention=1200
*.undo_tablespace='UNDOTBS1'
[oracle@zyy-jilin dbs]$
Note:
you should use multiple control files in you product environment.
mkdir -p /oracle/admin/pora40/adump
mkdir -p /oradata/db40
mkdir -p /oracle/fast_recovery_area
Note:
make required changes here in the init.ora file to take care of new directories for control files, audit dump, fast recovery area etc

SQL> create spfile from pfile;
SQL> shutdown abort
SQL> startup nomount
复制

Restore the Control Files

RMAN> restore controlfile  from '/backup/db40/pora40_ctrl_20140521_3178_bak';
复制

Mount the database and catalog the backup pieces which have been restored in the new location

RMAN> alter database mount;
RMAN> catalog start with '/backup/db40';
RMAN> crosscheck backup;
RMAN> delete expired backup;
复制

RMAN> list failure;
List of Database Failures
=========================
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------------- -------
5 CRITICAL OPEN 2014-05-21 18:00:10 System datafile 1: '/oracle/oradata/pora40/system01.dbf' is missing
2 CRITICAL OPEN 2014-05-21 18:00:10 Control file needs media recovery
8 HIGH OPEN 2014-05-21 18:00:10 One or more non-system datafiles are missing
Tip:
One of the new features in Oracle 11g is the RMAN Recovery Advisor. DRA (Data Recovery Advisor) automatically detects data failures, provides repair options and executes repair option provided.
advise failure;

RMAN> report schema;
复制

List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 0 SYSTEM *** /oracle/oradata/pora40/system01.dbf
2 0 SYSAUX *** /oracle/oradata/pora40/sysaux01.dbf
3 0 UNDOTBS1 *** /oracle/oradata/pora40/undotbs01.dbf
4 0 USERS *** /oracle/oradata/pora40/users01.dbf
5 0 UNDOTBS1 *** /oracle/oradata/pora40/undotbs02.dbf
...
List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 20 TEMP 32767 /oracle/oradata/pora40/temp01.dbf
2 2 TEMP 32767 /oracle/oradata/pora40/temp02.dbf
Generate the SET NEWNAME FOR DATAFILE command
Note:
Since the OSS backup image copy is based on Oracle Managed File format (OMF), I find this error even though we have set the DB_FILE_NAME_CONVERT parameter to account for the directory path change between source and target.
RMAN is not restoring the data files in the new location but is looking for the directory path which existed on the source database, but which is not present on the new or target server where we are doing the restore.
To copy above "report schema" output to a file (b.txt),to generate "set newname" commands when had many datafile in your DB. in my case ,the new datafile will stored in '/oradata/db40'.

#  awk '{print "set newname for datafile "$1 " to '\\''" $5 "'\\'';"}' b.txt|sed "s/\\/oracle\\/oradata\\/pora40\\//\\/oradata\\/db40\\//;s/\\/oradata\\/pora40\\/datafile\\//\\/oradata\\/db40\\//"
复制

or
To update online redo logs NAME in control files before "alter database open resetlogs" ,So to work around this we will generate a text file via SQL*PLUS which will contain the SET NEWNAME commands and call this file from RMAN.
These are the contents of the text file rename_files.sql

set head off pages 0 feed off echo off verify off
set lines 200
spool rename_datafiles.lst
select 'SET NEWNAME FOR DATAFILE ' || FILE# || ' TO "' || '/home/oracle/sqlfun/' || substr(name,instr(name,'/',-1)+1) || "';' from v$datafile;
spool off
exit;
复制

Tip:
To check all redo logfiles. to generate the new redo log file names. If to recover point in time need logs all have been archived .and recover don't need apply redo logs ,Even you do not need "ALTER DATABASE RENAME FILE " online logfile before recover databases .

SQL> select member from v$logfile;
MEMBER
------------------------------------------------
/oracle/oradata/pora40/redo03.log
/oracle/oradata/pora40/redo02.log
/oracle/oradata/pora40/redo01.log
/oracle/oradata/pora40/redo04.log
/oracle/oradata/pora40/redo05.log
复制

The following script which will generate the new online redo log file names.

set head off pages 0 feed off echo off verify off
set lines 200
spool rename_logfiles.lst
select 'alter database rename file "'|| member ||"' '||chr(10)|| ' TO "' || '/home/oracle/sqlfun/' || substr(member,instr(member,'/',-1)+1) || "';' from v$logfile;
spool off
exit;
复制

Tip:
If you are using a target time expression instead of a target SCN, then make sure that the time format environment variables are set appropriately before invoking RMAN. The following are sample Globalization Support settings:

NLS_LANG = american_america.utf8
NLS_DATE_FORMAT="Mon DD YYYY HH24:MI:SS"
复制

Restore and Recover the database.
Now pass this file name to the RMAN run block

RMAN> run{
set newname for datafile 1 to '/oradata/db40/system01.dbf';
set newname for datafile 2 to '/oradata/db40/sysaux01.dbf';
set newname for datafile 3 to '/oradata/db40/undotbs01.dbf';
set newname for datafile 4 to '/oradata/db40/users01.dbf';
set newname for datafile 5 to '/oradata/db40/undotbs02.dbf';
set newname for datafile 6 to '/oradata/db40/xmsb01.dbf';
set newname for datafile 7 to '/oradata/db40/xmsb02.dbf';
set newname for datafile 8 to '/oradata/db40/zyy01.dbf';
set newname for datafile 9 to '/oradata/db40/iptv01.dbf';
set newname for datafile 10 to '/oradata/db40/medical01.dbf';
set newname for datafile 11 to '/oradata/db40/medical02.dbf';
set newname for datafile 12 to '/oradata/db40/users02.dbf';
set newname for datafile 13 to '/oradata/db40/gppx.dbf';
set newname for datafile 14 to '/oradata/db40/jspx01.dbf';
set newname for datafile 15 to '/oradata/db40/sczy01.dbf';
set newname for datafile 16 to '/oradata/db40/xy_henan01.dbf';
set newname for datafile 17 to '/oradata/db40/xiangyi01.dbf';
set newname for datafile 18 to '/oradata/db40/cms01.dbf';
set newname for datafile 19 to '/oradata/db40/px_platform01.dbf';
SET NEWNAME FOR TEMPFILE 1 TO '/oradata/db40/temp01.dbf' ;
SET NEWNAME FOR TEMPFILE 2 TO '/oradata/db40/temp02.dbf' ;
# rename redo log name
#SQL "ALTER DATABASE RENAME FILE ''/dev3/oracle/dbs/redo01.log''   TO ''/oradata/test/redo01.log'' ";
#SQL "ALTER DATABASE RENAME FILE ''/dev3/oracle/dbs/redo02.log''  TO ''/oradata/test/redo02.log'' ";
# set target time for all operations in the RUN block	
SET UNTIL TIME 'May 20 2014 14:00:00';
restore database;
SWITCH DATAFILE ALL;
switch TEMPFILE ALL;
recover database;
}
复制

NOTE:
You can also use time expressions, restore points, or log sequence numbers to specify the SET UNTIL time:

  SET UNTIL TIME 'Nov 15 2004 09:00:00';
  SET UNTIL SEQUENCE 9923;  
  SET UNTIL RESTORE POINT before_update;
复制

To run a sql script which above generated to rename redo logfiles name,Open the database with RESETLOGS

sqlplus / as sysdba
SQL> @rename_logfiles.lst
RMAN> ALTER DATABASE OPEN RESETLOGS;
复制

Tip:
One of the new features in Oracle 11g is the RMAN Recovery Advisor. DRA (Data Recovery Advisor) automatically detects data failures, provides repair options and executes repair option provided.
advise failure;
From 11gR2 oracle introduced new options for “SET NEWNAME” command.
1.SET NEWNAME FOR DATAFILE and SET NEWNAME FOR TEMPFILE
2.SET NEWNAME FOR TABLESPACE
3.SET NEWNAME FOR DATABASE
The following variables are introduced for SET NEWNAME from 11gR2 :

    %b The file name remains same as the original. For example, if a datafile is named D:\\oracle\\oradata\\matrix\\test.dbf, then %b results in test.dbf.
    %f Specifies the absolute file number of the datafile for which the new name is generated.
    %I Specifies the DBID.
    %N Specifies the tablespace name.
    %U Specifies the following format: data-D-%d_id-%I_TS-%N_FNO-%f.
复制

Tack a example:

RMAN> run
{
 SET NEWNAME FOR DATABASE to '/oradata/db40/%b';
 SET NEWNAME FOR TEMPFILE 1 TO '/oradata/db40/temp01.dbf' ;
 SET NEWNAME FOR TEMPFILE 2 TO '/oradata/db40/temp02.dbf' ;
 SET UNTIL TIME 'May 20 2014 14:00:00';
 restore database;
 SWITCH DATAFILE ALL;
 switch TEMPFILE ALL;
 recover database;
}
复制

Reference http://gavinsoorma.com/2013/07/restore-rman-backup-to-another-server-for-testing-disaster-recovery-procedures-as-well-as-for-cloning/