Friday, August 28, 2020

Recover dropped tablespace using RMAN tablespace point in time recovery (TSPITR)

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