Recover dropped tablespace using RMAN tablespace point in time recovery (TSPITR)
Step 1: Create tablespace
SQL> conn / as sysdba
Connected.
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/QTEST/system01.dbf
/u01/app/oracle/oradata/QTEST/sysaux01.dbf
/u01/app/oracle/oradata/QTEST/undotbs01.dbf
/u01/app/oracle/oradata/QTEST/users01.dbf
SQL> create tablespace testtbs datafile '/u01/app/oracle/oradata/QTEST/testtbs01.dbf' size 100m;
Tablespace created.
SQL> create user testtbs identified by testtbs default tablespace testtbs;
User created.
SQL> grant dba to testtbs;
Grant succeeded.
SQL> conn testtbs/testtbs;
Connected.
SQL> create table test(empname varchar2(20),city varchar2(20));
Table created.
SQL> insert into test values('qader','bahrain');
1 row created.
SQL> insert into test values('kadar','manama');
1 row created.
SQL> commit;
Step 2: Backup database Plus archivelog
[oracle@node2 ~]$ rman target sys/Welcome1
Recovery Manager: Release 12.1.0.2.0 - Production on Sat Aug 29 01:18:39 2020
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
connected to target database: QTEST (DBID=2589001473)
RMAN> backup database plus archivelog;
Step 3: Note Current SCN
SQL> conn / as sysdba
Connected.
SQL> SQL> select current_scn from v$database;
CURRENT_SCN
-----------
1744628
Step 4: Drop tablespace
SQL> drop tablespace testtbs including contents and datafiles;
Tablespace dropped.
Step 5: Create auxiliary & Recover tablespace using tablespace point in time recovery.
[oracle@node2 ~]$ rman target sys/Welcome1
Recovery Manager: Release 12.1.0.2.0 - Production on Sat Aug 29 01:29:27 2020
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
connected to target database: QTEST (DBID=2589001473)
RMAN> run
{
recover tablespace testtbs
until scn 1744628
auxiliary destination '/u01/app/oracle/fast_recovery_area/QTEST'; ------> backup pieces location
}
initialization parameters used for automatic instance:
db_name=QTEST
db_unique_name=hsbr_pitr_QTEST
compatible=12.1.0.2.0
db_block_size=8192
db_files=200
diagnostic_dest=/u01/app/oracle
_system_trig_enabled=FALSE
sga_target=1168M
processes=200
db_create_file_dest=/u01/app/oracle/fast_recovery_area/QTEST
log_archive_dest_1='location=/u01/app/oracle/fast_recovery_area/QTEST'
#No auxiliary parameter file used
starting up automatic instance QTEST
Oracle instance started
Total System Global Area 1224736768 bytes
Fixed Size 2923824 bytes
Variable Size 335545040 bytes
Database Buffers 872415232 bytes
Redo Buffers 13852672 bytes
Automatic instance created
List of tablespaces that have been dropped from the target database:
Tablespace TESTTBS
contents of Memory Script:
{
# set requested point in time
set until scn 1744628;
sql 'begin dbms_backup_restore.AutoBackupFlag(FALSE); end;';
}
executing Memory Script
executing command: SET until clause
Starting restore at 29-AUG-20
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=23 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: restore complete, elapsed time: 00:00:01
output file name=/u01/app/oracle/fast_recovery_area/QTEST/QTEST/controlfile/o1_mf_hnm1rzmd_.ctl
Finished restore at 29-AUG-20
contents of Memory Script:
{
# set requested point in time
"/u01/app/oracle/oradata/QTEST/testtbs01.dbf";
# switch all tempfiles
switch clone tempfile all;
# restore the tablespaces in the recovery set and the auxiliary set
restore clone datafile 1, 4, 3, 5;
switch clone datafile all;
}
executing Memory Script
renamed tempfile 1 to /u01/app/oracle/fast_recovery_area/QTEST/QTEST/datafile/o1_mf_temp_%u_.tmp in control file
Starting restore at 29-AUG-20
archived log file name=/u01/app/oracle/fast_recovery_area/QTEST/archivelog/2020_08_29/o1_mf_1_37_hnm1rjny_.arc thread=1 sequence=37
media recovery complete, elapsed time: 00:00:00
Finished recover at 29-AUG-20
database opened
contents of Memory Script:
{
sql "create or replace directory TSPITR_DIROBJ_DPDIR as ''
Performing export of metadata...
EXPDP> Starting "SYS"."TSPITR_EXP_hsbr_sAEw":
EXPDP> Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
EXPDP> Processing object type TRANSPORTABLE_EXPORT/TABLE
EXPDP> Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS
EXPDP> Processing object type TRANSPORTABLE_EXPORT/STATISTICS/MARKER
EXPDP> Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
EXPDP> Master table "SYS"."TSPITR_EXP_hsbr_sAEw" successfully loaded/unloaded
EXPDP> ******************************************************************************
EXPDP> Dump file set for SYS.TSPITR_EXP_hsbr_sAEw is:
EXPDP> /u01/app/oracle/fast_recovery_area/QTEST/tspitr_hsbr_12180.dmp
EXPDP> ******************************************************************************
EXPDP> Datafiles required for transportable tablespace TESTTBS:
EXPDP> /u01/app/oracle/oradata/QTEST/testtbs01.dbf
EXPDP> Job "SYS"."TSPITR_EXP_hsbr_sAEw" successfully completed at Sat Aug 29 04:09:12 2020 elapsed 0 00:01:11
Export completed
contents of Memory Script:
# shutdown clone before import
shutdown clone abort
executing Memory Script
Oracle instance shut down
Performing import of metadata...
IMPDP> Master table "SYS"."TSPITR_IMP_hsbr_rhej" successfully loaded/unloaded
IMPDP> Starting "SYS"."TSPITR_IMP_hsbr_rhej":
IMPDP> Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
IMPDP> Processing object type TRANSPORTABLE_EXPORT/TABLE
IMPDP> Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS
IMPDP> Processing object type TRANSPORTABLE_EXPORT/STATISTICS/MARKER
IMPDP> Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
IMPDP> Job "SYS"."TSPITR_IMP_hsbr_rhej" successfully completed at Sat Aug 29 04:09:53 2020 elapsed 0 00:00:32
Import completed
contents of Memory Script:
auxiliary instance file /u01/app/oracle/fast_recovery_area/QTEST/QTEST/onlinelog/o1_mf_1_hnm1sp84_.log deleted
auxiliary instance file /u01/app/oracle/fast_recovery_area/QTEST/QTEST/datafile/o1_mf_sysaux_hnm1s60l_.dbf deleted
auxiliary instance file /u01/app/oracle/fast_recovery_area/QTEST/QTEST/datafile/o1_mf_undotbs1_hnm1s60s_.dbf deleted
auxiliary instance file /u01/app/oracle/fast_recovery_area/QTEST/QTEST/datafile/o1_mf_system_hnm1s60g_.dbf deleted
auxiliary instance file /u01/app/oracle/fast_recovery_area/QTEST/QTEST/controlfile/o1_mf_hnm1rzmd_.ctl deleted
auxiliary instance file tspitr_hsbr_12180.dmp deleted
Finished recover at 29-AUG-20
No comments:
Post a Comment