RMAN TSPITR(Recovery Manager Tablespace Point In Time Recovery) 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 useful in following scenarios:
- Recover from data loss after DDL as truncate, Drop table with purge.
- Recover from logical corruption of the table.
- Recover from dropped tablespaces.
- Recover from incorrect batch job on table belongs to single tablespace.
Modes:
- Fully Automated
- Automated: RMAN-Managed Auxiliary Instance with User Settings
- Non-Automated: TSPITR and User-Managed Auxiliary Instance
Scenario: There is batch data operation: insert, update into a table. It is noticed that all the data inserted after certain time is incorrect and need to revert it back. The time until which date need to be recovered is termed as Target time. We are using Fully Automated TSPITR to recover tablespace tbs01 till target time.
Create tablespace for Demo propose.
create tablespace tbs01
datafile '/u01/app/oracle/oradata/orcl/tbs01.dbf'
size 10m
autoextend on;
Create user that holds table.
Create user demo
identified by oracle
default tablespace tbs01
quota unlimited on tbs01;
Grant required privileges to the user.
grant connect,resource to demo;
grant execute on dbms_lock to demo;
Connect as user demo and create table that will be used on recovery
conn demo/oracle;
create table tbl_demo(
sn number,
data varchar2(20)
);
Create PL/SQL code to insert data into demo TABLE
declare
a number;
begin
for a in 1..50
loop
insert into tbl_demo values(a,to_char(sysdate,'YYYY-MM-DD HH24:MI:SS'));
commit;
dbms_lock.sleep(5); --sleep for 5 second
end loop;
end;
/
Wait for few minutes to complete this PL/SQL Block.
Right now there are 50 number of rows on the table tbl_demo. Now we are doing
Tablespace point in time recovery till 46th row and data: 2017-07-18 21:16:04
That means we are recovering until Date and Time: 2017-07-18 21:16:05.
select * from tbl_demo;
..............
Querying DBMS_TTS.TRANSPORT_SET_CHECK for a Subset of Tablespaces and check for any violations.
begin
dbms_tts.transport_set_check('TBS01',TRUE,TRUE);
end;
/
select * from transport_set_violations;
Query the TS_PITR_OBJECTS_TO_BE_DROPPED data dictionary view on the primary database to list all the database objects that will be dropped once you perform TSPITR. We will search with the reference of the target time. In our case target time is 2017-07-19 08:00:01.
select owner, name, tablespace_name, to_char(creation_time,'YY-MM-DD:HH24:MI:SS')
from ts_pitr_objects_to_be_dropped
where tablespace_name='TBS01'
and creation_time > to_date('2017-07-19 08:00:01','YYYY-MM-DD HH24:MI:SS')
order by tablespace_name,creation_time;
AUXILIARY DESTINATION parameter is use to set a location for RMAN to use for the auxiliary set data files. Create auxiliary destination for tablespace point in time recovery
mkdir /tmp/auxdest
Execute rman and connect to target Database and execute following command
rman target /
run
{
recover tablespace tbs01
until time "to_date('2017-07-18 21:16:05','YYYY-MM-DD HH24:MI:SS')"
auxiliary destination '/tmp/auxdest';
}
..........
Once recovery is completed the tablespace in our case tbs01 will stay offline. We need to bring it to online before accessing.
alter tablespace tbs01 online;
Now connect user demo and select the table tbl_demo to access the recovered table as we have expected.
select * from tbl_demo;
.........
Following is the output of the RMAN command with detail explantation. Text with background are the output.
Starting Tablespace PITR.
Starting recover at 18-JUL-17
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=27 device type=DISK
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 UNDO01
Creating auxiliary instance with random SID
Creating automatic instance, with SID='btar'
initialization parameters used for automatic instance:
db_name=ORCL
db_unique_name=btar_tspitr_ORCL
compatible=11.2.0
db_block_size=8192
db_files=200
sga_target=280M
processes=50
db_create_file_dest=/tmp/auxdest
log_archive_dest_1='location=/tmp/auxdest'
#No auxiliary parameter file used
starting up automatic instance ORCL
Oracle instance started
Total System Global Area 292933632 bytes
Fixed Size 1336092 bytes
Variable Size 100666596 bytes
Database Buffers 184549376 bytes
Redo Buffers 6381568 bytes
Automatic instance created
Running TRANSPORT_SET_CHECK on recovery set tablespaces
TRANSPORT_SET_CHECK completed successfully
Starting Restoration of database on auxiliary instance with the following RMAN commands.
contents of Memory Script:
{
# set requested point in time
set until time "to_date('2017-07-18 21:16:05','YYYY-MM-DD 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;';
}
executing Memory Script
executing command: SET until clause
Starting restore at 18-JUL-17
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=20 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 /u01/app/oracle/flash_recovery_area/ORCL/autobackup/2017_07_18/o1_mf_s_949698787_dpwc4c64_.bkp
channel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/flash_recovery_area/ORCL/autobackup/2017_07_18/o1_mf_s_949698787_dpwc4c64_.bkp tag=TAG20170718T211307
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/tmp/auxdest/ORCL/controlfile/o1_mf_dpwcskct_.ctl
Finished restore at 18-JUL-17
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 time "to_date('2017-07-18 21:16:05','YYYY-MM-DD HH24:MI:SS')";
plsql <<<-- font="" tspitr_2="">
declare
sqlstatement varchar2(512);
offline_not_needed exception;
pragma exception_init(offline_not_needed, -01539);
begin
sqlstatement := 'alter tablespace '|| 'TBS01' ||' 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 5 to
"/u01/app/oracle/oradata/orcl/tbs01.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, 5;
switch clone datafile all;
}
executing Memory Script
executing command: SET until clause
sql statement: alter tablespace TBS01 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 /tmp/auxdest/ORCL/datafile/o1_mf_temp01_%u_.tmp in control file
Starting restore at 18-JUL-17
using channel ORA_AUX_DISK_1
creating datafile file number=5 name=/u01/app/oracle/oradata/orcl/tbs01.dbf
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 /tmp/auxdest/ORCL/datafile/o1_mf_system_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00003 to /tmp/auxdest/ORCL/datafile/o1_mf_undo01_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00002 to /tmp/auxdest/ORCL/datafile/o1_mf_sysaux_%u_.dbf
channel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/flash_recovery_area/ORCL/backupset/2017_07_17/o1_mf_nnndf_FULL_DB_BACKUP_ON_ST_dpsokmr7_.bkp
channel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/flash_recovery_area/ORCL/backupset/2017_07_17/o1_mf_nnndf_FULL_DB_BACKUP_ON_ST_dpsokmr7_.bkp tag=FULL_DB_BACKUP_ON_START
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:25
Finished restore at 18-JUL-17
datafile 1 switched to datafile copy
input datafile copy RECID=4 STAMP=949699496 file name=/tmp/auxdest/ORCL/datafile/o1_mf_system_dpwcsq07_.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=5 STAMP=949699496 file name=/tmp/auxdest/ORCL/datafile/o1_mf_undo01_dpwcsq0y_.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=6 STAMP=949699496 file name=/tmp/auxdest/ORCL/datafile/o1_mf_sysaux_dpwcsq0t_.dbf
Start recovering auxiliary database instance until the specified time.
contents of Memory Script:
{
# set requested point in time
set until time "to_date('2017-07-18 21:16:05','YYYY-MM-DD 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 5 online";
# recover and open resetlogs
recover clone database tablespace "TBS01", "SYSTEM", "UNDO01", "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 5 online
Starting recover at 18-JUL-17
using channel ORA_AUX_DISK_1
starting media recovery
archived log for thread 1 with sequence 14 is already on disk as file /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2017_07_17/o1_mf_1_14_dpsol2ry_.arc
archived log for thread 1 with sequence 15 is already on disk as file /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2017_07_18/o1_mf_1_15_dpty3y5f_.arc
archived log for thread 1 with sequence 16 is already on disk as file /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2017_07_18/o1_mf_1_16_dpty41xy_.arc
archived log for thread 1 with sequence 17 is already on disk as file /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2017_07_18/o1_mf_1_17_dpv1y2s4_.arc
archived log for thread 1 with sequence 18 is already on disk as file /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2017_07_18/o1_mf_1_18_dpv22p7t_.arc
archived log for thread 1 with sequence 19 is already on disk as file /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2017_07_18/o1_mf_1_19_dpv2c55w_.arc
archived log for thread 1 with sequence 20 is already on disk as file /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2017_07_18/o1_mf_1_20_dpw8rzoy_.arc
archived log for thread 1 with sequence 21 is already on disk as file /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2017_07_18/o1_mf_1_21_dpwcspjx_.arc
archived log file name=/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2017_07_17/o1_mf_1_14_dpsol2ry_.arc thread=1 sequence=14
archived log file name=/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2017_07_18/o1_mf_1_15_dpty3y5f_.arc thread=1 sequence=15
archived log file name=/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2017_07_18/o1_mf_1_16_dpty41xy_.arc thread=1 sequence=16
archived log file name=/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2017_07_18/o1_mf_1_17_dpv1y2s4_.arc thread=1 sequence=17
archived log file name=/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2017_07_18/o1_mf_1_18_dpv22p7t_.arc thread=1 sequence=18
archived log file name=/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2017_07_18/o1_mf_1_19_dpv2c55w_.arc thread=1 sequence=19
archived log file name=/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2017_07_18/o1_mf_1_20_dpw8rzoy_.arc thread=1 sequence=20
archived log file name=/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2017_07_18/o1_mf_1_21_dpwcspjx_.arc thread=1 sequence=21
media recovery complete, elapsed time: 00:00:03
Finished recover at 18-JUL-17
database opened
Making target tablespace on auxiliary instance read only and creating oracle directory and performing transportable tablespace.
contents of Memory Script:
{
# make read only the tablespace that will be exported
sql clone 'alter tablespace TBS01 read only';
# create directory for datapump import
sql "create or replace directory TSPITR_DIROBJ_DPDIR as ''
/tmp/auxdest''";
# create directory for datapump export
sql clone "create or replace directory TSPITR_DIROBJ_DPDIR as ''
/tmp/auxdest''";
}
executing Memory Script
sql statement: alter tablespace TBS01 read only
sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/tmp/auxdest''
sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/tmp/auxdest''
Performing export of metadata...
EXPDP> Starting "SYS"."TSPITR_EXP_btar":
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_btar" successfully loaded/unloaded
EXPDP> ******************************************************************************
EXPDP> Dump file set for SYS.TSPITR_EXP_btar is:
EXPDP> /tmp/auxdest/tspitr_btar_43317.dmp
EXPDP> ******************************************************************************
EXPDP> Datafiles required for transportable tablespace TBS01:
EXPDP> /u01/app/oracle/oradata/orcl/tbs01.dbf
EXPDP> Job "SYS"."TSPITR_EXP_btar" successfully completed at 21:25:12
Export completed
Dropping the target tablespace on the main database and importing the transportable tablespace exported earlier.
contents of Memory Script:
{
# shutdown clone before import
shutdown clone immediate
# drop target tablespaces before importing them back
sql 'drop tablespace TBS01 including contents keep datafiles';
}
executing Memory Script
database closed
database dismounted
Oracle instance shut down
sql statement: drop tablespace TBS01 including contents keep datafiles
Performing import of metadata...
IMPDP> Master table "SYS"."TSPITR_IMP_btar" successfully loaded/unloaded
IMPDP> Starting "SYS"."TSPITR_IMP_btar":
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_btar" successfully completed at 21:25:21
Import completed
Removing and cleaning the auxiliary instance leaving the recovered tablespace offline.
contents of Memory Script:
{
# make read write and offline the imported tablespaces
sql 'alter tablespace TBS01 read write';
sql 'alter tablespace TBS01 offline';
# enable autobackups after TSPITR is finished
sql 'begin dbms_backup_restore.AutoBackupFlag(TRUE); end;';
}
executing Memory Script
sql statement: alter tablespace TBS01 read write
sql statement: alter tablespace TBS01 offline
sql statement: begin dbms_backup_restore.AutoBackupFlag(TRUE); end;
Removing automatic instance
Automatic instance removed
auxiliary instance file /tmp/auxdest/ORCL/datafile/o1_mf_temp01_dpwctmy8_.tmp deleted
auxiliary instance file /tmp/auxdest/ORCL/onlinelog/o1_mf_3_dpwctmn8_.log deleted
auxiliary instance file /tmp/auxdest/ORCL/onlinelog/o1_mf_2_dpwctmgt_.log deleted
auxiliary instance file /tmp/auxdest/ORCL/onlinelog/o1_mf_1_dpwctm49_.log deleted
auxiliary instance file /tmp/auxdest/ORCL/datafile/o1_mf_sysaux_dpwcsq0t_.dbf deleted
auxiliary instance file /tmp/auxdest/ORCL/datafile/o1_mf_undo01_dpwcsq0y_.dbf deleted
auxiliary instance file /tmp/auxdest/ORCL/datafile/o1_mf_system_dpwcsq07_.dbf deleted
auxiliary instance file /tmp/auxdest/ORCL/controlfile/o1_mf_dpwcskct_.ctl deleted
Finished recover at 18-JUL-17
RMAN>
Oracle PITR performs partial restoration of database (SYSTEM, SYSAUX, UNDO,) so it requires free space equal to sum on them.
Oracle PITR performs partial restoration of database (SYSTEM, SYSAUX, UNDO,
This comment has been removed by a blog administrator.
ReplyDelete