Automatic TSPITR in 10G RMAN -A walk Through [ID 335851.1]

Automatic TSPITR in 10G RMAN -A walk Through [ID 335851.1]

		修改时间 14-JUN-2009 类型 BULLETIN 状态 PUBLISHED

In this Document
  Purpose
  Scope and Application
  Automatic TSPITR in 10G RMAN -A walk Thruogh

Applies to:

Oracle Server - Enterprise Edition - Version: 10.1.0.0 to 10.2.0.0
Information in this document applies to any platform.

Purpose

This Article contains an example of Automated TSPITR in 10G using RMAN

Scope and Application

RMAN automatic Tablespace Point-In-Time Recovery ( TSPITR) enables you to quickly recover one or more tablespaces in an Oracle database to an earlier time, without affecting the state of the rest of the tablespaces and other objects in the database.

Automatic TSPITR in 10G RMAN -A walk Thruogh

When performing fully automated TSPITR, letting RMAN manage the entire process. There are only two requirements which we need to specify manually.

1. Auxiliary destination for RMAN to use for the auxiliary set datafiles and other files for the auxiliary instance.

2. Configure any channels required for the TSPITR on the target instance.

Note: The auxiliary instance will use the same channel configuration as the target instance when performing the TSPITR.

3. Use TS_PITR_CHECK view for identifying and resolving dependencies within the recovery set tablespaces. Refer:
http://download.oracle.com/docs/cd/B19306_01/backup.102/b14191/rcmtspit.htm#i1015064

CASE STUDIES
~~~~~~~~~~~~
The following case studies give the audience detailed steps in building a test environment and in practicing the procedure.

Setting up the environment:

1. Created a tablespace test and a table in that tablespace

SQL> Create tablespace test datafile 'D:\BACKUP\TEST.DBF' size 2 M;
Tablespace created

SQL> create table test001 (id number) tablespace test;
Table created.

SQL> insert into test001 values(100);
1 row created.

SQL> commit;
Commit complete.

SQL> select * from test001;
ID
----------
100

2. Checked the CURRENT LOG Sequence

SQL> select SEQUENCE# from v$log where status='CURRENT';

SEQUENCE#
----------
135

## 135 is my current log sequence

3. Taken the backup of the database and current controlfile using RMAN.

4. Done few log switch and droped that table.
 
SQL> drop table test001;        Table dropped.

SQL> select SEQUENCE# from v$log where status='CURRENT';

 SEQUENCE#
----------
       139
   ## Table dropped in log sequence 139.
   So TSPITR until log sequence 138 will give that table back.

Note: UNTIL TIME TSPITR syntax is :

RMAN> recover tablespace "TEST" until time "23-SEP-2005 10:00:00','DD-MON-YYYY HH24:MI:SS"
      auxiliary destination 'D:\BAckup\temp';
 
 
True test starts:
-----------------

1. Connect to RMAN
   C:> rman target / catalog rman/rman

2. Need to Do A TSPITR until log sequence 138.  Auxiliary destination  is D:\Backup\temp 
 RMAN> recover tablespace "TEST"  until logseq 138 thread 1 
       auxiliary destination 'D:\Backup\temp';
Starting recover at 20-SEP-05
    starting full resync of recovery catalog
    full resync complete
    allocated channel: ORA_DISK_1
    channel ORA_DISK_1: sid=26 devtype=DISK
    
    Creating automatic instance, with SID='ljsA'
    
    initialization parameters used for automatic instance:
    db_name=AMAR
    compatible=10.2.0.1.0
    db_block_size=8192
    db_files=200
    db_unique_name=tspitr_AMAR_ljsA
    large_pool_size=1M
    shared_pool_size=110M
    #No auxiliary parameter file used
    db_create_file_dest=D:\BAckup\temp
    control_files=D:\BAckup\temp/cntrl_tspitr_AMAR_ljsA.f
    
    
    starting up automatic instance AMAR
    
    Oracle instance started
    
    Total System Global Area     201326592 bytes
    
    Fixed Size                     1247044 bytes
    Variable Size                146802876 bytes
    Database Buffers              50331648 bytes
    Redo Buffers                   2945024 bytes
    Automatic instance created
    
    contents of Memory Script. {
    # set the until clause
    set until  logseq 138 thread 1;
    # restore the controlfile
    restore clone controlfile;
    # mount the controlfile
    sql clone 'alter database mount clone database';
    # archive current online log for tspitr to a resent until time
    sql 'alter system archive log current';
    # avoid unnecessary autobackups for structural changes during TSPITR
    sql 'begin dbms_backup_restore.AutoBackupFlag(FALSE); end;';
    # resync catalog after controlfile restore
    resync catalog;
    }
    executing Memory Script. executing command: SET until clause
    
    Starting restore at 20-SEP-05
    allocated channel: ORA_AUX_DISK_1
    channel ORA_AUX_DISK_1: sid=36 devtype=DISK
    
    channel ORA_AUX_DISK_1: starting datafile backupset restore
    channel ORA_AUX_DISK_1: restoring control file
    channel ORA_AUX_DISK_1: reading from backup piece
    +DATA/amar/backupset/2005_09_20/ncnnf0_tag20050920t105610_0.291.569501773
    channel ORA_AUX_DISK_1: restored backup piece 1
    piece handle=+DATA/amar/backupset/2005_09_20/ncnnf0_tag20050920t105610_0.291.569501773
    tag=TAG20050920T105610
    channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:15
    output filename=D:\BACKUP\TEMP\CNTRL_TSPITR_AMAR_LJSA.F
    Finished restore at 20-SEP-05
    
    sql statement: alter database mount clone database
    
    sql statement: alter system archive log current
    
    sql statement: begin dbms_backup_restore.AutoBackupFlag(FALSE); end;
    
    starting full resync of recovery catalog
    full resync complete
    released channel: ORA_DISK_1
    released channel: ORA_AUX_DISK_1
    
    contents of Memory Script. {
    # generated tablespace point-in-time recovery script. # set the until clause
    set until  logseq 138 thread 1;
    plsql <<<-- tspitr_2
    declare
      sqlstatement       varchar2(512);
      offline_not_needed exception;
      pragma exception_init(offline_not_needed, -01539);
    begin
      sqlstatement := 'alter tablespace '||  'TEST' ||' offline for recover';
      krmicd.writeMsg(6162, sqlstatement);
      krmicd.execSql(sqlstatement);
    exception
      when offline_not_needed then
        null;
    end; >>>;
    # set an omf destination filename for restore
    set newname for clone datafile  1 to new;
    # set an omf destination filename for restore
    set newname for clone datafile  2 to new;
    # set an omf destination tempfile
    set newname for clone tempfile  1 to new;
    # set a destination filename for restore
    set newname for datafile  6 to
     "D:\BACKUP\TEST.DBF";
    # rename all tempfiles
    switch clone tempfile all;
    # restore the tablespaces in the recovery set plus the auxilliary tablespaces
    restore clone datafile  1, 2, 6;
    switch clone datafile all;
    #online the datafiles restored or flipped
    sql clone "alter database datafile  1 online";
    #online the datafiles restored or flipped
    sql clone "alter database datafile  2 online";
    #online the datafiles restored or flipped
    sql clone "alter database datafile  6 online";
    # make the controlfile point at the restored datafiles, then recover them
    recover clone database tablespace  "TEST", "SYSTEM", "UNDOTBS1" delete archivelog;
    alter clone database open resetlogs;
    # PLUG HERE the creation of a temporary tablespace if export fails due to lack
    # of temporary space.
    # For example in Unix these two lines would do that:
    #sql clone "create tablespace aux_tspitr_tmp
    #           datafile ''/tmp/aux_tspitr_tmp.dbf'' size 500K";
    }
    executing Memory Script
    
    executing command: SET until clause
    
    sql statement: alter tablespace TEST offline for recover
    
    executing command: SET NEWNAME
    
    executing command: SET NEWNAME
    
    executing command: SET NEWNAME
    
    executing command: SET NEWNAME
    
    renamed temporary file 1 to D:\BACKUP\TEMP\TSPITR_A\DATAFILE\O1_MF_TEMP_%U_.TMP in control file
    
    Starting restore at 20-SEP-05
    allocated channel: ORA_AUX_DISK_1
    channel ORA_AUX_DISK_1: sid=39 devtype=DISK
    
    channel ORA_AUX_DISK_1: starting datafile backupset restore
    channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
    restoring datafile 00001 to D:\BACKUP\TEMP\TSPITR_A\DATAFILE\O1_MF_SYSTEM_%U_.DBF
    restoring datafile 00002 to D:\BACKUP\TEMP\TSPITR_A\DATAFILE\O1_MF_UNDOTBS1_%U_.DBF
    restoring datafile 00006 to D:\BACKUP\TEST.DBF
    channel ORA_AUX_DISK_1: reading from backup piece
    +DATA/amar/backupset/2005_09_20/nnndf0_tag20050920t105434_0.295.569501677
    channel ORA_AUX_DISK_1: restored backup piece 1
    piece handle=+DATA/amar/backupset/2005_09_20/nnndf0_tag20050920t105434_0.295.569501677
    tag=TAG20050920T105434
    channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:01:06
    Finished restore at 20-SEP-05
    
    datafile 1 switched to datafile copy
    input datafile copy recid=4 stamp=569503256
    filename=D:\BACKUP\TEMP\TSPITR_A\DATAFILE\O1_MF_SYSTEM_1LZ8QZF1_.DBF
    datafile 2 switched to datafile copy
    input datafile copy recid=5 stamp=569503256
    filename=D:\BACKUP\TEMP\TSPITR_A\DATAFILE\O1_MF_UNDOTBS1_1LZ8QZN7_.DBF
    
    sql statement: alter database datafile  1 online
    
    sql statement: alter database datafile  2 online
    
    sql statement: alter database datafile  6 online
    
    Starting recover at 20-SEP-05
    using channel ORA_AUX_DISK_1
    
    starting media recovery
    
    archive log thread 1 sequence 134 is already on disk as file
    +DATA/amar/archivelog/2005_09_20/thread_1_seq_134.292.569501807
    archive log thread 1 sequence 135 is already on disk as file
    +DATA/amar/archivelog/2005_09_20/thread_1_seq_135.280.569501919
    archive log thread 1 sequence 136 is already on disk as file
    +DATA/amar/archivelog/2005_09_20/thread_1_seq_136.283.569501925
    archive log thread 1 sequence 137 is already on disk as file
    +DATA/amar/archivelog/2005_09_20/thread_1_seq_137.297.569501931
    archive log filename=+DATA/amar/archivelog/2005_09_20/thread_1_seq_134.292.569501807 thread=1
    sequence=134
    archive log filename=+DATA/amar/archivelog/2005_09_20/thread_1_seq_135.280.569501919 thread=1
    sequence=135
    archive log filename=+DATA/amar/archivelog/2005_09_20/thread_1_seq_136.283.569501925 thread=1
    sequence=136
    archive log filename=+DATA/amar/archivelog/2005_09_20/thread_1_seq_137.297.569501931 thread=1
    sequence=137
    media recovery complete, elapsed time: 00:00:06
    Finished recover at 20-SEP-05
    
    database opened
    
    contents of Memory Script.:
    {
    # export the tablespaces in the recovery set
    host 'exp userid
    =\"/@(DESCRIPTION=(ADDRESS=(PROTOCOL=beq)(PROGRAM=oracle)(ARGV0=oracleljsA)(ARGS=^'(DESCRIPTION=(LO
    AL=YES)(ADDRESS=(PROTOCOL=beq)))^')(ENVS=^'ORACLE_SID=ljsA^'))(CONNECT_DATA=(SID=ljsA))) as
    sysdba\" point_in_time_recover=y tablespaces=
     TEST file=
    tspitr_a.dmp';
    # shutdown clone before import
    shutdown clone immediate
    # import the tablespaces in the recovery set
    host 'imp userid =\"/@ as sysdba\" point_in_time_recover=y file=
    tspitr_a.dmp';
    # online/offline the tablespace imported
    sql "alter tablespace  TEST online";
    sql "alter tablespace  TEST offline";
    # enable autobackups in case user does open resetlogs from RMAN after TSPITR
    sql 'begin dbms_backup_restore.AutoBackupFlag(TRUE); end;';
    # resync catalog after tspitr finished
    resync catalog;
    }
    executing Memory Script
    
    Export: Release 10.2.0.1.0 - Production on Tue Sep 20 11:21:40 2005
    
    Copyright (c) 1982, 2005, Oracle.  All rights reserved.
    
    
    Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Produc
    tion
    With the Partitioning, OLAP and Data Mining options
    Export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
    server uses AL32UTF8 character set (possible charset conversion)
    Note: table data (rows) will not be exported
    
    About to export Tablespace Point-in-time Recovery objects...
    For tablespace TEST ...
    . exporting cluster definitions
    . exporting table definitions
    . . exporting table                        TEST001
    . exporting referential integrity constraints
    . exporting triggers
    . end point-in-time recovery
    Export terminated successfully without warnings.
    
    host command complete
    
    database closed
    database dismounted
    Oracle instance shut down
    
    Import: Release 10.2.0.1.0 - Production on Tue Sep 20 11:22:17 2005
    
    Copyright (c) 1982, 2005, Oracle.  All rights reserved.
    
    
    Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Produc
    tion
    With the Partitioning, OLAP and Data Mining options
    
    Export file created by EXPORT:V10.02.01 via conventional path
    About to import Tablespace Point-in-time Recovery objects...
    import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
    import server uses AL32UTF8 character set (possible charset conversion)
    . importing SYS's objects into SYS
    . . importing table                      "TEST001"
    Import terminated successfully without warnings.
    
    host command complete
    
    sql statement: alter tablespace  TEST online
    
    sql statement: alter tablespace  TEST offline
    
    sql statement: begin dbms_backup_restore.AutoBackupFlag(TRUE); end;
    
    starting full resync of recovery catalog
    full resync complete
    
    Removing automatic instance
    Automatic instance removed
    auxiliary instance file D:\BACKUP\TEMP\CNTRL_TSPITR_AMAR_LJSA.F deleted
    auxiliary instance file D:\BACKUP\TEMP\TSPITR_A\DATAFILE\O1_MF_SYSTEM_1LZ8QZF1_.DBF deleted
    auxiliary instance file D:\BACKUP\TEMP\TSPITR_A\DATAFILE\O1_MF_UNDOTBS1_1LZ8QZN7_.DBF deleted
    auxiliary instance file D:\BACKUP\TEMP\TSPITR_A\DATAFILE\O1_MF_TEMP_1LZ8TSLB_.TMP deleted
    auxiliary instance file D:\BACKUP\TEMP\TSPITR_A\ONLINELOG\O1_MF_1_1LZ8TM4X_.LOG deleted
    auxiliary instance file D:\BACKUP\TEMP\TSPITR_A\ONLINELOG\O1_MF_2_1LZ8TMSL_.LOG deleted
    auxiliary instance file D:\BACKUP\TEMP\TSPITR_A\ONLINELOG\O1_MF_3_1LZ8TNCV_.LOG deleted
    Finished recover at 20-SEP-05

TSPITR recovery completed sucessfully.

3. Connect to target database using SQLPLUS and take the tablespace test Online.
    SQL>  select * from test001;
         ID
    ----------
         100
Recoverd the lost table using TSPITR.















 相关的
产品 
Oracle Database Products > Oracle Database > Oracle Database > Oracle Server - Enterprise Edition 
关键字 
TSPITR

ITPub博客

Oracle10g表空間不完整恢復

Applies to:

Purpose

Scope and Application

Automatic TSPITR in 10G RMAN -A walk Thruogh

最新文章