Recovery Manager (RMAN) Automatic TSPITR enables quick recovery of one or more tablespaces in a database to an earlier time without affecting the rest of the tablespaces and objects in the database.
RMAN TSPITR is most useful for the following situations:
- We want to recover a logical database to a point different from the rest of the physical database, when multiple logical databases exist in separate tablespaces of one physical database. For example, we maintain logical databases in the orders and personnel tablespaces. An incorrect batch job or DML statement corrupts the data in only one of the tablespaces.
- We want to recover data lost after DDL operations that change the structure of tables. We cannot use Flashback Table to rewind a table to before the point of a structural change such as a truncate table operation.
- We want to recover a table after it has been dropped with the PURGE option.
- We want to recover from the logical corruption of a table.
- We want to recover dropped tablespaces. In fact, RMAN can perform TSPITR on dropped tablespaces even when a recovery catalog is not used.
- We can also use Flashback Database to rewind data, but we must rewind the entire database rather than just a subset. Also, unlike TSPITR, the Flashback Database feature necessitates the overhead of maintaining flashback logs. The point in time to which you can flash back the database is more limited than the TSPITR window, which extends back to our earliest recoverable backup.
TSPIR was there in earleir release but have some limitation i.e, we cannot recover a dropped tablespace . Oracle 11gr2 performs a fully automated managed TSPIR. It automatically creates and start the auxiliary instance and restores the datafiles it requires and the files pertaining to the dropped tablespace. It will first
perform a recovery of the tablespace on the auxiliary instance and then use Data Pump and Transportable Tablespace technology to extract and import the tablespace meta data into the original source database .
Here we will illustrate the Concept of TSPIR with an example .
We will create a user say "TSPIR" and assign the default tablespace say "tspir" and create tables in this tablespace. We take the full backup of the database and further drop the tablespace "tspir" . Before dropping we we note the scn and use this scn to do TSPIR . Below are steps for the TSPIR
Step 1 : Clean the previous failed TSPIR
SQL> exec dbms_backup_restore.manageauxinstance ('TSPITR',1) ;
PL/SQL procedure successfully completed.
Step 2 : Create tablespace and Users and Create tables
SQL> create tablespace tspir datafile 'C:\app\neerajs\oradata\orcl\tspir.dbf' size 150m autoextend on;
Tablespace created.
SQL> create user tspir identified by tspir
2 default tablespace tspir
3 quota unlimited on tspir;
User created.
SQL> grant resource,connect to tspir;
Grant succeeded.
SQL> connect tspir/tspir@orcl
Connected.
SQL> create table test(id number);
Table created.
SQL> insert into test values(12);
1 row created.
SQL> insert into test values(121);
1 row created.
SQL> commit;
Commit complete.
SQL> select * from test;
ID
----------
12
121
SQL> create table emp as select * from user_objects;
Table created.
SQL> select count(*) from emp;
COUNT(*)
----------
2
SQL> conn / as sysdba
Connected.
Step 3 : Take the fresh backup of database
SQL> host rman target sys@orcl
Recovery Manager: Release 11.2.0.1.0 - Production on Wed Nov 30 14:35:44 2011
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
target database Password:
connected to target database: ORCL (DBID=1296005542)
RMAN> backup database plus archivelog;
Starting backup at 30-NOV-11
current log archived
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=141 device type=DISK
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=3 RECID=1 STAMP=768238310
input archived log thread=1 sequence=4 RECID=2 STAMP=768238310
input archived log thread=1 sequence=5 RECID=3 STAMP=768238311
input archived log thread=1 sequence=6 RECID=4 STAMP=768238314
input archived log thread=1 sequence=7 RECID=5 STAMP=768239453
input archived log thread=1 sequence=8 RECID=6 STAMP=768239455
input archived log thread=1 sequence=9 RECID=7 STAMP=768305386
input archived log thread=1 sequence=10 RECID=8 STAMP=768334227
input archived log thread=1 sequence=11 RECID=9 STAMP=768393025
input archived log thread=1 sequence=12 RECID=10 STAMP=768454251
input archived log thread=1 sequence=13 RECID=11 STAMP=768521484
input archived log thread=1 sequence=14 RECID=12 STAMP=768580566
channel ORA_DISK_1: starting piece 1 at 30-NOV-11
channel ORA_DISK_1: finished piece 1 at 30-NOV-11
piece handle=F:\RMAN_BKP\01MSV6UP_1_1 tag=TAG20111130T143608 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:55
Finished backup at 30-NOV-11
Starting backup at 30-NOV-11
using channel ORA_DISK_1
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=C:\APP\NEERAJS\ORADATA\ORCL\SYSTEM01.DBF
input datafile file number=00002 name=C:\APP\NEERAJS\ORADATA\ORCL\SYSAUX01.DBF
input datafile file number=00006 name=C:\APP\NEERAJS\ORADATA\ORCL\TSPIR.DBF
input datafile file number=00005 name=C:\APP\NEERAJS\ORADATA\ORCL\EXAMPLE01.DBF
input datafile file number=00003 name=C:\APP\NEERAJS\ORADATA\ORCL\UNDOTBS01.DBF
input datafile file number=00004 name=C:\APP\NEERAJS\ORADATA\ORCL\USERS01.DBF
channel ORA_DISK_1: starting piece 1 at 30-NOV-11
channel ORA_DISK_1: finished piece 1 at 30-NOV-11
piece handle=F:\RMAN_BKP\02MSV70H_1_1 tag=TAG20111130T143705 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:55
Finished backup at 30-NOV-11
Starting backup at 30-NOV-11
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=15 RECID=13 STAMP=768580741
channel ORA_DISK_1: starting piece 1 at 30-NOV-11
channel ORA_DISK_1: finished piece 1 at 30-NOV-11
piece handle=F:\RMAN_BKP\04MSV746_1_1 tag=TAG20111130T143901 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 30-NOV-11
Starting Control File and SPFILE Autobackup at 30-NOV-11
piece handle=F:\RMAN_BKP\CF\C-1296005542-20111130-01 comment=NONE
Finished Control File and SPFILE Autobackup at 30-NOV-11
Step 4 : Note the SCN and drop the tablespace
SQL> select current_scn from v$database;
CURRENT_SCN
-----------------
5659022
SQL> drop tablespace tspir including contents and datafiles;
Tablespace dropped.
Step 5 : Connect with rman and perform TSPIR
Here we have used the auxiliary destination with the recover tablespace command because auxiliary destination is an optional disk location where RMAN uses to temporarily store the auxiliary set files. The auxiliary destination is used only when using a RMAN-managed auxiliary instance. Specifying an auxiliary destination with a user-managed auxiliary instance results in an error.
Recovery Manager: Release 11.2.0.1.0 - Production on Wed Nov 30 14:58:11 2011
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL (DBID=1296005542)
RMAN> recover tablespace tspir until scn 5659022 auxiliary destination 'F:\';
Starting recover at 30-NOV-11
using channel ORA_DISK_1
RMAN-05026: WARNING: presuming following set of tablespaces applies to specified point-in-time
List of tablespaces expected to have UNDO segments
Tablespace SYSTEM
Tablespace UNDOTBS1
Creating automatic instance, with SID='nume'
initialization parameters used for automatic instance:
db_name=ORCL
db_unique_name=nume_tspitr_ORCL
compatible=11.2.0.0.0
db_block_size=8192
db_files=200
sga_target=280M
processes=50
db_create_file_dest=F:\
log_archive_dest_1='location=F:\'
#No auxiliary parameter file used
starting up automatic instance ORCL
Oracle instance started
Total System Global Area 292933632 bytes
Fixed Size 1374164 bytes
Variable Size 100665388 bytes
Database Buffers 184549376 bytes
Redo Buffers 6344704 bytes
Automatic instance created
List of tablespaces that have been dropped from the target database:
Tablespace tspir
contents of Memory Script:
{
# set requested point in time
set until scn 5659022;
# restore the controlfile
restore clone controlfile;
# mount the controlfile
sql clone 'alter database mount clone database';
# archive current online log
sql 'alter system archive log current';
# avoid unnecessary autobackups for structural changes during TSPITR
sql 'begin dbms_backup_restore.AutoBackupFlag(FALSE); end;';
}
executing Memory Script
executing command: SET until clause
Starting restore at 30-NOV-11
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=59 device type=DISK
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: reading from backup piece F:\RMAN_BKP\CF\C-1296005542-20111130-01
channel ORA_AUX_DISK_1: piece handle=F:\RMAN_BKP\CF\C-1296005542-20111130-01 tag=TAG20111130T143903
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:04
output file name=F:\ORCL\CONTROLFILE\O1_MF_7FD0QK8S_.CTL
Finished restore at 30-NOV-11
sql statement: alter database mount clone database
sql statement: alter system archive log current
sql statement: begin dbms_backup_restore.AutoBackupFlag(FALSE); end;
contents of Memory Script:
{
# set requested point in time
set until scn 5659022;
# set destinations for recovery set and auxiliary set datafiles
set newname for clone datafile 1 to new;
set newname for clone datafile 3 to new;
set newname for clone datafile 2 to new;
set newname for clone tempfile 1 to new;
set newname for datafile 6 to
"C:\APP\NEERAJS\ORADATA\ORCL\TSPIR.DBF";
# switch all tempfiles
switch clone tempfile all;
# restore the tablespaces in the recovery set and the auxiliary set
restore clone datafile 1, 3, 2, 6;
switch clone datafile all;
}
executing Memory Script
executing command: SET until clause
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
renamed tempfile 1 to F:\ORCL\DATAFILE\O1_MF_TEMP_%U_.TMP in control file
Starting restore at 30-NOV-11
using channel ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00001 to F:\ORCL\DATAFILE\O1_MF_SYSTEM_%U_.DBF
channel ORA_AUX_DISK_1: restoring datafile 00003 to F:\ORCL\DATAFILE\O1_MF_UNDOTBS1_%U_.DBF
channel ORA_AUX_DISK_1: restoring datafile 00002 to F:\ORCL\DATAFILE\O1_MF_SYSAUX_%U_.DBF
channel ORA_AUX_DISK_1: restoring datafile 00006 to C:\APP\NEERAJS\ORADATA\ORCL\TSPIR.DBF
channel ORA_AUX_DISK_1: reading from backup piece F:\RMAN_BKP\02MSV70H_1_1
channel ORA_AUX_DISK_1: piece handle=F:\RMAN_BKP\02MSV70H_1_1 tag=TAG20111130T143705
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:02:15
Finished restore at 30-NOV-11
datafile 1 switched to datafile copy
input datafile copy RECID=5 STAMP=768585055 file name=F:\ORCL\DATAFILE\O1_MF_SYSTEM_7FD0QYNZ_.DBF
datafile 3 switched to datafile copy
input datafile copy RECID=6 STAMP=768585056 file name=F:\ORCL\DATAFILE\O1_MF_UNDOTBS1_7FD0QYRF_.DBF
datafile 2 switched to datafile copy
input datafile copy RECID=7 STAMP=768585056 file name=F:\ORCL\DATAFILE\O1_MF_SYSAUX_7FD0QYPG_.DBF
contents of Memory Script:
{
# set requested point in time
set until scn 5659022;
# online the datafiles restored or switched
sql clone "alter database datafile 1 online";
sql clone "alter database datafile 3 online";
sql clone "alter database datafile 2 online";
sql clone "alter database datafile 6 online";
# recover and open resetlogs
recover clone database tablespace "TSPIR", "SYSTEM", "UNDOTBS1", "SYSAUX" delete archivelog;
alter clone database open resetlogs;
}
executing Memory Script
executing command: SET until clause
sql statement: alter database datafile 1 online
sql statement: alter database datafile 3 online
sql statement: alter database datafile 2 online
sql statement: alter database datafile 6 online
Starting recover at 30-NOV-11
using channel ORA_AUX_DISK_1
starting media recovery
archived log for thread 1 with sequence 15 is already on disk as file D:\ARCHIVE\ORCL_ARCHIVE\ARC0000000015_0768224813.0001
archived log for thread 1 with sequence 16 is already on disk as file D:\ARCHIVE\ORCL_ARCHIVE\ARC0000000016_0768224813.0001
archived log file name=D:\ARCHIVE\ORCL_ARCHIVE\ARC0000000015_0768224813.0001 thread=1 sequence=15
archived log file name=D:\ARCHIVE\ORCL_ARCHIVE\ARC0000000016_0768224813.0001 thread=1 sequence=16
media recovery complete, elapsed time: 00:00:04
Finished recover at 30-NOV-11
database opened
contents of Memory Script:
{
# make read only the tablespace that will be exported
sql clone 'alter tablespace TSPIR read only';
# create directory for datapump import
sql "create or replace directory TSPITR_DIROBJ_DPDIR as ''
F:\''";
# create directory for datapump export
sql clone "create or replace directory TSPITR_DIROBJ_DPDIR as ''
F:\''";
}
executing Memory Script
sql statement: alter tablespace TSPIR read only
sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''F:\''
sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''F:\''
Performing export of metadata...
EXPDP> Starting "SYS"."TSPITR_EXP_nume":
EXPDP> Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
EXPDP> Processing object type TRANSPORTABLE_EXPORT/TABLE
EXPDP> Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
EXPDP> Master table "SYS"."TSPITR_EXP_nume" successfully loaded/unloaded
EXPDP> ******************************************************************************
EXPDP> Dump file set for SYS.TSPITR_EXP_nume is:
EXPDP> F:\TSPITR_NUME_43731.DMP
EXPDP> ******************************************************************************
EXPDP> Datafiles required for transportable tablespace TSPIR:
EXPDP> C:\APP\NEERAJS\ORADATA\ORCL\TSPIR.DBF
EXPDP> Job "SYS"."TSPITR_EXP_nume" successfully completed at 16:20:28
Export completed
contents of Memory Script:
{
# shutdown clone before import
shutdown clone immediate
}
executing Memory Script
database closed
database dismounted
Oracle instance shut down
Performing import of metadata...
IMPDP> Master table "SYS"."TSPITR_IMP_nume" successfully loaded/unloaded
IMPDP> Starting "SYS"."TSPITR_IMP_nume":
IMPDP> Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
IMPDP> Processing object type TRANSPORTABLE_EXPORT/TABLE
IMPDP> Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
IMPDP> Job "SYS"."TSPITR_IMP_nume" successfully completed at 16:21:48
Import completed
contents of Memory Script:
{
# make read write and offline the imported tablespaces
sql 'alter tablespace TSPIR read write';
sql 'alter tablespace TSPIR offline';
# enable autobackups after TSPITR is finished
sql 'begin dbms_backup_restore.AutoBackupFlag(TRUE); end;';
}
executing Memory Script
sql statement: alter tablespace TSPIR read write
sql statement: alter tablespace TSPIR offline
sql statement: begin dbms_backup_restore.AutoBackupFlag(TRUE); end;
Removing automatic instance
Automatic instance removed
auxiliary instance file F:\ORCL\DATAFILE\O1_MF_TEMP_7FD0Y3PY_.TMP deleted
auxiliary instance file F:\ORCL\ONLINELOG\O1_MF_4_7FD0XROZ_.LOG deleted
auxiliary instance file F:\ORCL\ONLINELOG\O1_MF_3_7FD0XK9R_.LOG deleted
auxiliary instance file F:\ORCL\ONLINELOG\O1_MF_2_7FD0X9RF_.LOG deleted
auxiliary instance file F:\ORCL\ONLINELOG\O1_MF_1_7FD0X2LK_.LOG deleted
auxiliary instance file F:\ORCL\DATAFILE\O1_MF_SYSAUX_7FD0QYPG_.DBF deleted
auxiliary instance file F:\ORCL\DATAFILE\O1_MF_UNDOTBS1_7FD0QYRF_.DBF deleted
auxiliary instance file F:\ORCL\DATAFILE\O1_MF_SYSTEM_7FD0QYNZ_.DBF deleted
auxiliary instance file F:\ORCL\CONTROLFILE\O1_MF_7FD0QK8S_.CTL deleted
Finished recover at 30-NOV-11
RMAN>
Step 6 : Check the tablepsace status and existance
SQL> select tablespace_name,status from dba_tablespaces;
TABLESPACE_NAME STATUS
------------------------- -----------
SYSTEM ONLINE
SYSAUX ONLINE
UNDOTBS1 ONLINE
TEMP ONLINE
USERS ONLINE
EXAMPLE ONLINE
TSPIR OFFLINE
Since, we find the tablepspace "TSPIR" is offline . So bring the tablespace online .
SQL> alter tablespace tspir online;
Tablespace altered.
SQL> alter database datafile 'C:\app\neerajs\oradata\orcl\tspir.dbf' online;
Database altered.
SQL> select table_name from dba_tables where tablespace_name='TSPIR';
TABLE_NAME
-----------------
TEST
EMP
SQL> select * from tspir.test;
ID
----------
12
121
SQL> select count(*) from tspir.emp;
COUNT(*)
----------
2
Hence , we find that both the tables are recovered
Enjoy :-)
No comments:
Post a Comment