RMAN Auxiliary Instance
If this query returns a row, the
incomplete recovery might be unsuccessful. By investigating the
reason column, the problem can be
solved. To show one of the reasons why we can get a row from this
query, see the following example. Here, we create a table on a
different tablespace and create a relationship between the two
tables using the ID column. As there is no ID column in the
tbl_test1 table, we add it as
follows:
SQL>
alter
table tbl_test1 add (id number);
Table altered.
SQL>
create
table tbl_pr (id number) tablespace users;
Table created.
SQL>
alter
table tbl_pr add primary key (id);
Table altered.
SQL>
alter
table tbl_test1 add foreign key (id) references tbl_pr(id);
Table altered.
SQL>
select
obj1_name, ts1_name, obj2_name, ts2_name, reason
from
sys.ts_pitr_check
where
(ts1_name='TBS' and ts2_name<>'TBS') or (ts2_name='TBS'
and
ts1_name<>'TBS');
OBJ1_NAME TS1_NAME OBJ2_NAME TS2_NAME REASON
----------- ----------- ---------- --------- ----------
tbl_pr users tbl_test1 tbs constraint
between
tables not contained in recovery set
SQL>
If you get this message, then it means
that it is not possible to successfully recover the tablespace at
this point in time. In order to be successful, we need to resolve
the problem and get a no result? message from the above query.
Look at the error you get if you perform an incomplete recovery:
About to export
Tablespace Point-in-time Recovery objects...
EXP-00008: ORACLE error 29308 encountered
ORA-29308: view ts_pitr_check failure
ORA-06512: at "sys.dbms_pitr", line 887
ORA-06512: at line 1
EXP-00000: Export terminated unsuccessfully
host command complete
About to import Tablespace Point-in-time Recovery objects...
import done in WE8ISO8859P1 character set and AL16UTF16 NCHAR
character set
IMP-00009: abnormal end of export file
sql statement: alter tablespace TBS online
RMAN-00571:
===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS
===============
RMAN-00571:
===========================================================
RMAN-03002: failure of recover command at 11/06/2009 11:12:34
RMAN-03015: error occurred in stored script Memory Script
RMAN-03009: failure of sql command on default channel at
11/06/2009 11:12:34
RMAN-11003: failure during parse/execution of SQL statement:
alter tablespace T
BS online
ORA-01190: control file or data file 5 is from before the last
RESETLOGS
ORA-01110: data file 5: 'C:\tbs1.dbf'
RMAN>
This concludes that the recovery process
has failed.
The second check is to look at the
objects that will be lost after the incomplete recovery. All
objects that were created after deletion of those tables, that is,
after the time to which we want to perform tablespace recovery,
will be lost. To see those, query the
ts_pitr_objects_to_be_dropped view as
follows:
SQL>
select
name, to_char(creation_time,'dd.mm.yyyy hh24:mi:ss')
creation_time, tablespace_name
from sys.ts_pitr_objects_to_be_dropped
where
tablespace_name='TBS' and creation_time>to_date('05.11.2009
20.00.00','dd.mm.yyyy hh24:mi:ss')
NAME CREATION_TIME
TABLESPACE_NAME
----------------- --------------------
---------------
test_table 06.11.2009 01:41:04 TBS
SQL>
From the above result, Bob knows that the
table which was created after the point in time to which he wants
to recover the tablespace will be lost.
Now, it is time to perform the recovery.
Here, Bob decides to perform the recovery using the automatic
auxiliary instance created by RMAN.
He creates a folder to keep the auxiliary database files in and
runs the following command from RMAN prompt:
RMAN> recover tablespace tbs until scn
604515 auxiliary destination='c:\aux_destination';
By running the above command, RMAN starts
TSPITR. Examine the output of the recovery process step-by-step:
RMAN creates an automatic auxiliary
instance and starts it using the default initialization parameter
file.
Starting recover at
06-NOV-09
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=139 devtype=disk
List of tablespaces expected to have undo segments
tablespace system
tablespace undotbs1
Creating automatic instance, with SID='zxxu'
initialization parameters used for automatic instance:
db_name=DB1
compatible=10.2.0.1.0
db_block_size=8192
db_files=200
db_unique_name=tspitr_DB1_zxxu
large_pool_size=1M
shared_pool_size=110M
#No auxiliary parameter file used
db_create_file_dest=c:\aux_destination
control_files=c:\aux_destination/cntrl_tspitr_DB1_zxxu.f
starting up automatic instance DB1
Oracle instance started
Total System Global Area 201526592 bytes
Fixed Size 1248092 bytes
Variable Size 146801828 bytes
Database Buffers 50331648 bytes
Redo Buffers 2945024 bytes
Automatic instance
created
It restores the control file from backup
of the target database and mounts an auxiliary database.
set until scn
604515;
restore clone controlfile;
sql clone 'alter database mount clone database';
sql 'alter system archive log current';
It restores datafiles of the system, undo
and the recovered tablespace and recovers them all to a past point
in time. Then it opens the database with the resetlogs
option.
set until scn
604515;
set newname for clone datafile 1 to new;
set newname for clone datafile 2 to new;
set newname for clone tempfile 1 to new;
set newname for datafile 5 to "C:\tbs1.dbf";
set newname for datafile 6 to "C:\tbs2.dbf";
switch clone tempfile all;
restore clone datafile 1, 2, 5, 6;
switch clone datafile all;
sql clone "alter database datafile 1 online";
sql clone "alter database datafile 2 online";
sql clone "alter database datafile 5 online";
sql clone "alter database datafile 6 online";
recover clone database tablespace "tbs", "system", "undotbs1"
delete archivelog;
alter clone database open resetlogs;
It exports metadata of objects of the
recovered tablespace to a dump file. Then it shuts down the
auxiliary instance and imports the data to the target database.
# export the
tablespaces in the recovery set
host 'exp userid
=\"/@(description=(address=(protocol=beq)(program=oracle)(argv0=
oraclezxxu)(args=^'(de
scription=(local=yes)(address=(protocol=beq)))^')(envs=^'oracle_
sid=zxxu^'))(connect_d
ata=(SID=zxxu)))
as sysdba\" point_in_time_recover=y tablespaces=TBS file=tspitr_a.dmp';
shutdown clone immediate
host 'imp userid =\"/@ as sysdba\" point_in_time_recover=y file=tspitr_a.dmp';
It removes the auxiliary instance and
deletes all database files.
Removing automatic
instance
Automatic instance removed
auxiliary instance file C:\aux_destination\cntrl_tspitr_db1_zxxu.f
deleted
auxiliary instance file C:\aux_destination\tspitr_d\datafile\o1_mf_system_5h6lcxpg_.dbf
deleted
auxiliary instance file C:\AUX_destination\tspitr_d\datafile\o1_mf_undotbs1_5h6lcxrw_.dbf
deleted
auxiliary instance file C:\aux_destination\tspitr_d\datafile\o1_mf_temp_5h6lflrw_.tmp
deleted
auxiliary instance file C:\aux_destination\tspitr_d\onlinelog\o1_mf_1_5h6lffbq_.log
deleted
auxiliary instance file C:\aux_destination\tspitr_d\onlinelog\o1_mf_2_5h6lfgrw_.log
deleted
auxiliary instance file C:\aux_destination\tspitr_d\onlinelog\o1_mf_3_5h6lfj4w_.log
deleted
Finished recover at 06-NOV-09
Now, Bob creates the tablespace online
and checks to see whether the dropped tables were recovered or not
as follows:
SQL>
alter
tablespace tbs online;
Tablespace altered.
SQL>
select
count(1)
from
tbl_test1;
COUNT(1)
----------
49746
SQL>
select
count(1)
from
tbl_test2;
COUNT(1)
----------
49747
SQL>
As can be seen, he has successfully
recovered the dropped tables of the tablespace using TSPITR. It
is suggested to back up recovered tablespace after a successful
recovery process.
It is also possible to use a customized
auxiliary instance when the following customizations are desired:
- Using a self-customized different
initialization parameter for an auxiliary instance
- Renaming datafiles of the recovered
tablespace at the target database
- Changing the location of control files
and datafiles of the auxiliary instance
- By using the SET
newname for datafile ....' to ...'
command, the location of datafiles of the recovered tablespace
can be changed.
- By using the
db_file_name_convert and
log_file_name_convert parameters, the name
of datafiles and redo log files of the auxiliary instance can be
changed.
- By using the control_files
parameter in the intialization parameter, the location of
control files of an auxiliary instance can be changed.
If you want to use RMAN automatic
auxiliary instance with our self-customized initialization
parameter, create the file using the different parameters shown
above and define it using the set auxiliary instance
parameter file command before performing
TSPITR.
If you want to use your own auxiliary
instance, you need to create a password file, initialization
parameter file for an auxiliary instance, make changes to the
tnsnames.ora and listener.ora
files, start the auxiliary instance, connect to it from the RMAN
client and perform the recovery.
Starting from Oracle 11g, you can perform
tablespace point-in-time recovery on the dropped tablespace. If
you try to use it in Oracle 10g, you will get the following error:
RMAN-20202:
tablespace not found in the recovery catalog
RMAN-06019: could not translate tablespace name "TBS_TEST"
Another superiority of Oracle 11g is that
you can perform TSPITR multiple times to any scn,
time and sequence.
