Tablespace Point-In-Time Recovery

RMAN – Tablespace Point-In-Time Recovery

Tablespace Point-In-Time Recovery (tbspitr) is kind of cumbersome RMAN recovery to perform. There are basically three methods by which tbspitr can be achieved. Below expounded is a easiest of them, "fully automated" point in time recovery task, to bring home the basics.


You can play around with the variables for more control of the process in the other two methods of tbspitr with the risk of added complexity

There are various conditions to be met to perform a successful tbspitr. RMAN - Backup and Recovery User Guide is the best source for information on that. 

The database here uses filesystem to store datafiles. Using ASM would not make any difference in the activity.

Step 1: Create tablespace

Create a sample tablespace that can be used for this task. 

CREATE TABLESPACE tbspit DATAFILE
'/home/oracle/rprod/data/rprod/tbspit01.dbf' SIZE 250M AUTOEXTEND OFF
LOGGING
ONLINE
PERMANENT
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M
SEGMENT SPACE MANAGEMENT AUTO;
Step 2: Create user to hold data in this tablespace

SQL> Create user test identified by test123 default tablespace tbspit temporary tablespace temp;

Step 3: Create data to recover

Create a table to store data that we will use during point in time recovery.

SQL> Create table t1 (col1 number, col2 varchar2(20)) tablespace tbspitr;

Insert data into the table t1.

SQL> Conn test/test123

SQL> create sequence seq1; 

Begin
for i in 1..50 loop
insert into t1
select seq1.nextval, to_char(sysdate, 'dd-mon-yy hh24:mi:ss') from dual;
commit;
dbms_lock.sleep(57);
end loop;
end;
/
SQL> select * from t1;
COL1 COL2
---------- --------------------
...
32 23-mar-11 20:23:30
33 23-mar-11 20:24:27 ------ We will recover here.
34 23-mar-11 20:25:24
35 23-mar-11 20:26:21
36 23-mar-11 20:27:18
37 23-mar-11 20:28:15
38 23-mar-11 20:29:12
39 23-mar-11 20:30:09
40 23-mar-11 20:31:06
...
Step 4: Database structure

RMAN> report schema;
Report of database schema for database with db_unique_name RPROD
List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 680 SYSTEM YES /home/oracle/rprod/data/rprod/system01.dbf
2 520 SYSAUX NO /home/oracle/rprod/data/rprod/sysaux01.dbf
3 85 UNDOTBS1 YES /home/oracle/rprod/data/rprod/undotbs01.dbf
4 5 USERS NO /home/oracle/rprod/data/rprod/users01.dbf
5 100 EXAMPLE NO /home/oracle/rprod/data/rprod/example01.dbf
6 250 TBSPIT NO /home/oracle/rprod/data/rprod/tbspit01.dbf

List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 29 TEMP 32767 /home/oracle/rprod/data/rprod/temp01.dbf

RMAN>

Step 5: Check Tablespace Recoverability

SQL> begin
2 dbms_tts.transport_set_check('TBSPIT',TRUE,TRUE);
3 end;
4 /

PL/SQL procedure successfully completed.
SQL> select * from transport_set_violations;

no rows selected

Step 6: Perform Tablespace Point-In-Time Recovery.

Note that you do not allocate maintenance channels and do not connect to auxillary instance.Also database has to be up and accessible when tbspitr is kicked off

The activities that happens upon kicking off the tbspitr rman commands are as listed below:

*Create auxiliary instance – pvqe with db_create_file_dest set to the location we provide on the rman script.
*Reboots the target instance - RPROD
*Run TRANSPORT_SET_CHECK on recovery set tablespaces (here TBSPIT) to check dependency. (We will manually check this before starting tbspitr lest it fails at this point).
*Restore the controlfile and mount the clone database in auxilary location
*Restore SYSTEM, SYSAUX, UNDO datafiles at the auxillary destination.
*Restore TBSPIT datafile at target database (original) destination (RPROD) itself.
*Switch SYSTEM, SYSAUX & UNDO datafiles to clone copy.
*Bring the datafiles of these clone tablespaces online.
*Recover SYSTEM, SYSAUX, UNDO & TBSPIT clone tablespaces.
*Open resetlogs clone database.
*Make TBSPIT tablespace read-only
*Create directory for data pump as the same auxdest location.
*Do a transportable tablespace (Metadata) datapump export
*Shutdown clone database.
*Drop tablespace TBSPIT including contents. Keep datafile (for transport import)
*Do metadata datapump import.
*Make tablespace TBSPIT READ WRITE
*Take tablespace TBSPIT offline.
*Clean up the clone instance – remove restored datafiles, onlinelogs and controlfiles.

Starting RMAN TBSPITR

RMAN> shutdown immediate; -- This is just to show what happens if the db is down.
RMAN> startup mount; 

RMAN> run{
recover tablespace tbspit
until time "to_date('23-mar-11 20:24:27','dd-mon-rr hh24:mi:ss')"
auxiliary destination '/home/oracle/auxdest';
}

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 03/23/2011 20:52:54
RMAN-05010: target database must be opened in READ WRITE mode for Tablespace Point-in-Time Recovery

As said above, the database has to be open and tablespace accessible for tbspitr.

RMAN> sql 'alter database open';

sql statement: alter database open

RMAN> run{
recover tablespace tbspit
until time "to_date('23-mar-11 20:24:27','dd-mon-rr hh24:mi:ss')"
auxiliary destination '/home/oracle/auxdest';
}
2> 3> 4> 5>
Starting recover at 23-MAR-11
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
using channel ORA_DISK_4

Creating automatic instance, with SID='pvqe'

initialization parameters used for automatic instance:
db_name=RPROD
db_unique_name=pvqe_tspitr_RPROD
compatible=11.2.0.0.0
db_block_size=8192
db_files=200
sga_target=280M
processes=50
db_create_file_dest=/home/oracle/auxdest
log_archive_dest_1='location=/home/oracle/auxdest'
#No auxiliary parameter file used

starting up automatic instance RPROD

Oracle instance started

Total System Global Area 292278272 bytes

Fixed Size 2212736 bytes
Variable Size 100666496 bytes
Database Buffers 184549376 bytes
Redo Buffers 4849664 bytes
Automatic instance created
Running TRANSPORT_SET_CHECK on recovery set tablespaces
TRANSPORT_SET_CHECK completed successfully

contents of Memory Script:
{
# set requested point in time
set until time "to_date('23-mar-11 20:24:27','dd-mon-rr hh24:mi:ss')";
# 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;';
# resync catalog
resync catalog;
}
executing Memory Script
executing command: SET until clause

Starting restore at 23-MAR-11
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=81 device type=DISK
allocated channel: ORA_AUX_DISK_2
channel ORA_AUX_DISK_2: SID=5 device type=DISK
allocated channel: ORA_AUX_DISK_3
channel ORA_AUX_DISK_3: SID=30 device type=DISK
allocated channel: ORA_AUX_DISK_4
channel ORA_AUX_DISK_4: SID=56 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 /home/oracle/rprod/flash/RPROD/autobackup/2011_03_23/o1_mf_s_746568895_6ro37z65_.bkp
channel ORA_AUX_DISK_1: piece handle=/home/oracle/rprod/flash/RPROD/autobackup/2011_03_23/o1_mf_s_746568895_6ro37z65_.bkp tag=TAG20110323T201455
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/home/oracle/auxdest/RPROD/controlfile/o1_mf_6ro5nbcq_.ctl
Finished restore at 23-MAR-11

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

contents of Memory Script:
{
# set requested point in time
set until time "to_date('23-mar-11 20:24:27','dd-mon-rr hh24:mi:ss')";
plsql <<<-- tspitr_2
declare
sqlstatement varchar2(512);
offline_not_needed exception;
pragma exception_init(offline_not_needed, -01539);
begin
sqlstatement := 'alter tablespace '|| 'TBSPIT' ||' offline immediate';
krmicd.writeMsg(6162, sqlstatement);
krmicd.execSql(sqlstatement);
exception
when offline_not_needed then
null;
end; >>>;
# 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
"/home/oracle/rprod/data/rprod/tbspit01.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

sql statement: alter tablespace TBSPIT offline immediate

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

renamed tempfile 1 to /home/oracle/auxdest/RPROD/datafile/o1_mf_temp_%u_.tmp in control file

Starting restore at 23-MAR-11
using channel ORA_AUX_DISK_1
using channel ORA_AUX_DISK_2
using channel ORA_AUX_DISK_3
using channel ORA_AUX_DISK_4

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 00006 to /home/oracle/rprod/data/rprod/tbspit01.dbf
channel ORA_AUX_DISK_1: reading from backup piece /home/oracle/rprodbackup/05m7vf4b_1_1
channel ORA_AUX_DISK_2: starting datafile backup set restore
channel ORA_AUX_DISK_2: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_2: restoring datafile 00001 to /home/oracle/auxdest/RPROD/datafile/o1_mf_system_%u_.dbf
channel ORA_AUX_DISK_2: restoring datafile 00003 to /home/oracle/auxdest/RPROD/datafile/o1_mf_undotbs1_%u_.dbf
channel ORA_AUX_DISK_2: reading from backup piece /home/oracle/rprodbackup/03m7vf4b_1_1
channel ORA_AUX_DISK_3: starting datafile backup set restore
channel ORA_AUX_DISK_3: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_3: restoring datafile 00002 to /home/oracle/auxdest/RPROD/datafile/o1_mf_sysaux_%u_.dbf
channel ORA_AUX_DISK_3: reading from backup piece /home/oracle/rprodbackup/04m7vf4b_1_1
channel ORA_AUX_DISK_1: piece handle=/home/oracle/rprodbackup/05m7vf4b_1_1 tag=23MAR2011_BEFORE_BCKP
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:15
channel ORA_AUX_DISK_3: piece handle=/home/oracle/rprodbackup/04m7vf4b_1_1 tag=23MAR2011_BEFORE_BCKP
channel ORA_AUX_DISK_3: restored backup piece 1
channel ORA_AUX_DISK_3: restore complete, elapsed time: 00:00:15
channel ORA_AUX_DISK_2: piece handle=/home/oracle/rprodbackup/03m7vf4b_1_1 tag=23MAR2011_BEFORE_BCKP
channel ORA_AUX_DISK_2: restored backup piece 1
channel ORA_AUX_DISK_2: restore complete, elapsed time: 00:00:25
Finished restore at 23-MAR-11

datafile 1 switched to datafile copy
input datafile copy RECID=5 STAMP=746571371 file name=/home/oracle/auxdest/RPROD/datafile/o1_mf_system_6ro5nl12_.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=6 STAMP=746571371 file name=/home/oracle/auxdest/RPROD/datafile/o1_mf_undotbs1_6ro5nl5q_.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=7 STAMP=746571371 file name=/home/oracle/auxdest/RPROD/datafile/o1_mf_sysaux_6ro5nl1c_.dbf

contents of Memory Script:
{
# set requested point in time
set until time "to_date('23-mar-11 20:24:27','dd-mon-rr hh24:mi:ss')";
# 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 "TBSPIT", "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 23-MAR-11
using channel ORA_AUX_DISK_1
using channel ORA_AUX_DISK_2
using channel ORA_AUX_DISK_3
using channel ORA_AUX_DISK_4

starting media recovery

archived log for thread 1 with sequence 20 is already on disk as file /home/oracle/rprod/archive/1_20_745790363.dbf
archived log for thread 1 with sequence 21 is already on disk as file /home/oracle/rprod/archive/1_21_745790363.dbf
archived log for thread 1 with sequence 22 is already on disk as file /home/oracle/rprod/archive/1_22_745790363.dbf
archived log file name=/home/oracle/rprod/archive/1_20_745790363.dbf thread=1 sequence=20
archived log file name=/home/oracle/rprod/archive/1_21_745790363.dbf thread=1 sequence=21
archived log file name=/home/oracle/rprod/archive/1_22_745790363.dbf thread=1 sequence=22
media recovery complete, elapsed time: 00:00:00
Finished recover at 23-MAR-11

database opened

contents of Memory Script: 
{
# make read only the tablespace that will be exported
sql clone 'alter tablespace TBSPIT read only';
# create directory for datapump import
sql "create or replace directory TSPITR_DIROBJ_DPDIR as ''
/home/oracle/auxdest''";
# create directory for datapump export
sql clone "create or replace directory TSPITR_DIROBJ_DPDIR as ''
/home/oracle/auxdest''";
}
executing Memory Script

sql statement: alter tablespace TBSPIT read only

sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/home/oracle/auxdest''

sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/home/oracle/auxdest''

Performing export of metadata...
EXPDP> Starting "SYS"."TSPITR_EXP_pvqe":
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_pvqe" successfully loaded/unloaded
EXPDP> ******************************************************************************
EXPDP> Dump file set for SYS.TSPITR_EXP_pvqe is:
EXPDP> /home/oracle/auxdest/tspitr_pvqe_91264.dmp
EXPDP> ******************************************************************************
EXPDP> Datafiles required for transportable tablespace TBSPIT:
EXPDP> /home/oracle/rprod/data/rprod/tbspit01.dbf
EXPDP> Job "SYS"."TSPITR_EXP_pvqe" successfully completed at 20:56:57
Export completed

contents of Memory Script:
{
# shutdown clone before import
shutdown clone immediate
# drop target tablespaces before importing them back
sql 'drop tablespace TBSPIT including contents keep datafiles';
}
executing Memory Script

database closed
database dismounted
Oracle instance shut down

sql statement: drop tablespace TBSPIT including contents keep datafiles

Performing import of metadata...
IMPDP> Master table "SYS"."TSPITR_IMP_pvqe" successfully loaded/unloaded
IMPDP> Starting "SYS"."TSPITR_IMP_pvqe":
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_pvqe" successfully completed at 20:57:10
Import completed

contents of Memory Script:
{
# make read write and offline the imported tablespaces
sql 'alter tablespace TBSPIT read write';
sql 'alter tablespace TBSPIT offline';
# enable autobackups after TSPITR is finished
sql 'begin dbms_backup_restore.AutoBackupFlag(TRUE); end;';
# resync catalog
resync catalog;
}
executing Memory Script

sql statement: alter tablespace TBSPIT read write

sql statement: alter tablespace TBSPIT 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 /home/oracle/auxdest/RPROD/datafile/o1_mf_temp_6ro5oh04_.tmp deleted
auxiliary instance file /home/oracle/auxdest/RPROD/onlinelog/o1_mf_3_6ro5og8j_.log deleted
auxiliary instance file /home/oracle/auxdest/RPROD/onlinelog/o1_mf_2_6ro5ofov_.log deleted
auxiliary instance file /home/oracle/auxdest/RPROD/onlinelog/o1_mf_1_6ro5odhf_.log deleted
auxiliary instance file /home/oracle/auxdest/RPROD/datafile/o1_mf_sysaux_6ro5nl1c_.dbf deleted
auxiliary instance file /home/oracle/auxdest/RPROD/datafile/o1_mf_undotbs1_6ro5nl5q_.dbf deleted
auxiliary instance file /home/oracle/auxdest/RPROD/datafile/o1_mf_system_6ro5nl12_.dbf deleted
auxiliary instance file /home/oracle/auxdest/RPROD/controlfile/o1_mf_6ro5nbcq_.ctl deleted
Finished recover at 23-MAR-11

RMAN>

Meanwhile I was checking in another session on the auxillary instance created by the process

oracle:/home/oracle [rprod]$ pmon
grid 3911 1 0 19:45 ? 00:00:00 asm_pmon_+ASM
oracle 4492 1 0 19:49 ? 00:00:00 ora_pmon_mdiuat
oracle 6510 1 0 20:51 ? 00:00:00 ora_pmon_rprod
oracle 6661 1 0 20:53 ? 00:00:00 ora_pmon_pvqe
oracle 6703 4982 0 20:54 pts/3 00:00:00 grep pmon

SQL> select tablespace_name, status from dba_tablespaces where tablespace_name='TBSPIT';

TABLESPACE_NAME STATUS
------------------------------ ---------
TBSPIT OFFLINE

Step 7: Take backup before bringing the tablespace online

RUN {
ALLOCATE CHANNEL ch1 DEVICE TYPE DISK FORMAT = '/home/oracle/rprodbackup/rprod_%U';
ALLOCATE CHANNEL ch2 DEVICE TYPE DISK FORMAT = '/home/oracle/rprodbackup/rprod_%U';
ALLOCATE CHANNEL ch3 DEVICE TYPE DISK FORMAT = '/home/oracle/rprodbackup/rprod_%U';
ALLOCATE CHANNEL ch4 DEVICE TYPE DISK FORMAT = '/home/oracle/rprodbackup/rprod_%U';
backup database tag = '23Mar11_After_tbspitr' include current controlfile;
SQL 'ALTER SYSTEM ARCHIVE LOG CURRENT';
backup archivelog all;
BACKUP CURRENT CONTROLFILE tag = '23Mar11_aft_After_tbspitr_bkup';
}

SQL> alter tablespace tbspit online;

Tablespace altered.

SQL> select tablespace_name, status from dba_tablespaces where tablespace_name='TBSPIT';

TABLESPACE_NAME STATUS
------------------------------ ---------
TBSPIT ONLINE

Step 8: Verify TBSPITR

Check to see if the data in the table is exactly as we expect it to be, which is -> until time "to_date('23-mar-11 20:24:27','dd-mon-rr hh24:mi:ss')"

SQL> select * from t1;

COL1 COL2
---------- --------------------
2 21-mar-11 18:40:06
3 21-mar-11 18:42:03
4 21-mar-11 18:44:00
5 21-mar-11 18:45:57
6 21-mar-11 18:47:54
7 21-mar-11 18:49:51
8 21-mar-11 18:51:48
9 21-mar-11 18:53:45
10 21-mar-11 18:55:42
11 21-mar-11 18:57:39
12 21-mar-11 18:59:36
13 21-mar-11 19:01:33
14 21-mar-11 19:03:30
15 21-mar-11 19:05:27
16 21-mar-11 19:07:25
17 21-mar-11 19:21:17
18 21-mar-11 19:24:34
19 21-mar-11 19:27:51
20 21-mar-11 19:31:09
21 21-mar-11 19:34:26
22 21-mar-11 19:37:43
23 21-mar-11 19:41:00
24 21-mar-11 19:44:17
25 21-mar-11 19:47:34
26 21-mar-11 19:50:51
27 21-mar-11 19:54:08
28 21-mar-11 19:57:25
29 21-mar-11 20:00:42
30 21-mar-11 20:03:59
31 21-mar-11 20:07:16
32 23-mar-11 20:23:30

31 rows selected.

It can be seen that it does not include the time that we used to recover,