Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
This article contains information about table point in time recovery in 12c and later versions and examples.
As database administrators, we used to do a lot of superfluous work when it was necessary to return a deleted table. With Table Point In Time Recovery in 12C, we had the chance to return the table from the backup with a single action. Yes it is an automatic table recovery in oracle 12c.
In today’s article, we will recognize this feature that can be used in Oracle Database 12C and above versions.
The process works as follows; It opens a new instance for you and registers a database with your table on that instance. After registration, the table is taken out and imported into your existing database. You can do this by going to a certain date or according to the SCN or Sequence number, but there are some things you should pay attention to.
When you use the RECOVER command to recover tables or table partitions contained in an RMAN backup, the following limitations exist.
- The database must be in archivemode.
- Tables and table partitions belonging to the SYS scheme cannot be recovered.
- Tables and table partitions cannot be recovered from SYSTEM and SYSAUX tablespaces.
- Tables and table partitions in standyby databases cannot be recovered.
- Tables with NOT NULL constraints cannot be recovered with the REMAP option.
Table Point in Time Recovery Example
Let’s explain with a small example. Since I am working on the test database, I will take a fresh backup and perform the process. If you have a backup available, you can skip this step.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 | [oracle@node1 ~]$ rman target / RMAN> backup database plus archivelog; Starting backup at 16-SEP-20 current log archived allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=406 instance=BUGRADB1 device type=DISK channel ORA_DISK_1: starting archived log backup set channel ORA_DISK_1: specifying archived log(s) in backup set input archived log thread=1 sequence=19 RECID=1 STAMP=1051267492 channel ORA_DISK_1: starting piece 1 at 16-SEP-20 channel ORA_DISK_1: finished piece 1 at 16-SEP-20 piece handle=+FRA/BUGRADB/BACKUPSET/2020_09_16/annnf0_tag20200916t104453_0.261.1051267493 tag=TAG20200916T104453 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01 Finished backup at 16-SEP-20 . . . . . . Starting Control File and SPFILE Autobackup at 16-SEP-20 piece handle=+FRA/BUGRADB/AUTOBACKUP/2020_09_16/s_1051267503.265.1051267505 comment=NONE Finished Control File and SPFILE Autobackup at 16-SEP-20 RMAN> Recovery Manager complete. |
Now let’s prepare a suitable environment for recovery. For the example, I will recover according to the date. I will check my database time and delete a sample table.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 | [oracle@node1 ~]$ !sql sqlplus / as sysdba SQL*Plus: Release 19.0.0.0.0 - Production on Wed Sep 16 10:45:58 2020 Version 19.3.0.0.0 Copyright (c) 1982, 2019, Oracle. All rights reserved. Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.3.0.0.0 SQL> alter session set nls_date_format = 'dd/mm/yyyy hh24:mi:ss'; Session altered. SQL> select sysdate from dual; SYSDATE ------------------- 16/09/2020 10:59:12 |
My database time is “16/09/2020 10:59:12”. I want to return to this hour. There is a directory named “/ u01 / tempdata / bgrdb” on file system. The database will temporarily use this location.
First of all, I delete my table according to the scenario.
1 2 3 4 5 6 7 | SQL> drop table bugra.personel; Table dropped. SQL> commit; Commit complete. |
How To Recover Dropped Table in Oracle 12c
Now we deleted our test table. Lets start to recover dropped table in oracle 12c with date.
1 | RMAN> recover table "BUGRA"."PERSONEL" until time "to_date('16/09/2020 10:59:12','dd/mm/yyyy hh24:mi:ss')" AUXILIARY DESTINATION '/u01/tempdata/bgrdb'; |
If you want to do it with SCN;
1 | RMAN> recover table "BUGRA"."PERSONEL" UNTIL SCN 238107 AUXILIARY DESTINATION '/u01/tempdata/bgrdb'; |
Recovery begins.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 | Starting recover at 16-SEP-20 current log archived using channel ORA_DISK_1 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 UNDOTBS1 Creating automatic instance, with SID='Aglq' initialization parameters used for automatic instance: db_name=BUGRADB db_unique_name=Aglq_pitr_BUGRADB compatible=19.0.0 db_block_size=8192 db_files=200 diagnostic_dest=/u01/app/oracle/database/19.3.0 _system_trig_enabled=FALSE sga_target=3536M processes=200 db_create_file_dest=/u01/tempdata/bgrdb log_archive_dest_1='location=/u01/tempdata/bgrdb' #No auxiliary parameter file used starting up automatic instance BUGRADB Oracle instance started Total System Global Area 3707763120 bytes Fixed Size 8903088 bytes Variable Size 721420288 bytes Database Buffers 2969567232 bytes Redo Buffers 7872512 bytes Automatic instance created contents of Memory Script: { # set requested point in time set until time "to_date('16/09/2020 10:59:12','dd/mm/yyyy hh24:mi:ss')"; # restore the controlfile restore clone controlfile; . . . . . . IMPDP> . . imported "BUGRA"."PERSONEL":"SYS_P317" 9.890 KB 5 rows IMPDP> . . imported "BUGRA"."PERSONEL":"SYS_P318" 9.968 KB 6 rows IMPDP> . . imported "BUGRA"."PERSONEL":"SYS_P319" 9.906 KB 5 rows IMPDP> . . imported "BUGRA"."PERSONEL":"SYS_P320" 10.03 KB 7 rows IMPDP> Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX IMPDP> Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT IMPDP> Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS IMPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS IMPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER IMPDP> Job "SYS"."TSPITR_IMP_Aglq_spDm" successfully completed at Wed Sep 16 11:04:59 2020 elapsed 0 00:00:49 Import completed Removing automatic instance Automatic instance removed auxiliary instance file /u01/tempdata/bgrdb/BUGRADB/datafile/o1_mf_temp_hp3qqmch_.tmp deleted auxiliary instance file /u01/tempdata/bgrdb/FGVT_PITR_BUGRADB/onlinelog/o1_mf_3_hp3qtobf_.log deleted auxiliary instance file /u01/tempdata/bgrdb/FGVT_PITR_BUGRADB/onlinelog/o1_mf_2_hp3qto97_.log deleted auxiliary instance file /u01/tempdata/bgrdb/FGVT_PITR_BUGRADB/onlinelog/o1_mf_1_hp3qto86_.log deleted auxiliary instance file /u01/tempdata/bgrdb/FGVT_PITR_BUGRADB/datafile/o1_mf_users_hp3qtgm4_.dbf deleted auxiliary instance file /u01/tempdata/bgrdb/BUGRADB/datafile/o1_mf_sysaux_hp3qq94b_.dbf deleted auxiliary instance file /u01/tempdata/bgrdb/BUGRADB/datafile/o1_mf_undotbs1_hp3qq948_.dbf deleted auxiliary instance file /u01/tempdata/bgrdb/BUGRADB/datafile/o1_mf_system_hp3qq945_.dbf deleted auxiliary instance file /u01/tempdata/bgrdb/BUGRADB/controlfile/o1_mf_hp3qq29q_.ctl deleted auxiliary instance file tspitr_fgvt_61116.dmp deleted Finished recover at 16-SEP-20 |
The whole process is complete. As you can see, Oracle itself handles what we used to do manually. And with IMPDP, which we don’t like :))
As an example, the instance added while processing;
Our table has been successfully recovered with the two examples we have given. If you want, you can export for this table instead of performing recovery.
1 | RMAN> recover table "BUGRA"."PERSONEL" UNTIL SCN 238107 AUXILIARY DESTINATION '/u01/tempdata/bgrdb' DATAPUMP DESTINATION '/tempdata/export' DUMP FILE 'personel_yedek.dmp' NOTABLEIMPORT; |
Or you can recover the table with a different name.
1 | RMAN> recover table "BUGRA"."PERSONEL" until time "to_date('16/09/2020 10:59:12','dd/mm/yyyy hh24:mi:ss')" AUXILIARY DESTINATION '/u01/tempdata/bgrdb' REMAP TABLE 'BUGRA'.'PERSONEL':'PERSONEL_BCK'; |
We have come to the end of this article where we discuss the issue of recovering a table in Oracle Database with the help of RMAN.
Hope to see you again,
Source :
https://docs.oracle.com/database/121/BRADV/rcmresind.htm#BRADV859
