12c New Features – Table PITR in Oracle 12c


As we all know that from Oracle 12c we can recover a table with RMAN which was not possible until 11g (using a direct command). The internals behind this is exactly the same as it was in 11g tablespace PITR. Lets recall the steps performed during Tablespace PITR:-

Following are the excerpts from my posts on Tablespace PITR

  • Creates an auxiliary instance, starts it, and connects to it
  • Takes offline tablespaces that are involved with TSPITR
  • Restores the backup control file that corresponds to the target restore time
  • Restores datafiles to a destination specified by your AUXILIARY DESTINATION
  • Recovers restored datafiles in an auxiliary location
  • Opens an auxiliary database with the open resetlogs command
  • Exports the auxiliary data dictionary metadata about recovered tablespaces and shuts down the auxiliary database
  • Issues a switch command on the target database to update the control file to point at the recovered auxiliary datafiles
  • Imports objects associated with recovered tablespaces into the target database
  • Deletes the auxiliary datafiles

In table point in time recovery once the auxiliary database with the mentioned tablespace is completed  and the auxiliary database is opened a datapump dump of the table in concern is taken (with timestamp/scn to what is mentioned in the recover table command) and then the auxiliary database is dropped along with datafiles.

Finally the dump is imported into the original database and thus your TABLE PITR is completed.

To extract the SCN for your table recovery please read my post Restore database in case of dropped table.

RMAN> RECOVER TABLE SCOTT.EMP UNTIL SCN <SCN OF INTEREST> auxiliary destination ‘<PATH FOR AUXILIARY FILES>’;

Starting recover at 20-DEC-15
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=’Fkzx’

initialization parameters used for automatic instance:
db_name=O12102NP
db_unique_name=Fkzx_pitr_O22302NP
compatible=12.1.0.2.0
db_block_size=8192
db_files=200
diagnostic_dest=<PATH FOR AUXILIARY FILES>
_system_trig_enabled=FALSE
sga_target=1024M
processes=200
db_create_file_dest=<PATH FOR AUXILIARY FILES>
log_archive_dest_1=’location=<PATH FOR AUXILIARY FILES>’
#No auxiliary parameter file used




Performing import of tables…
IMPDP> Master table “SYS”.”TSPITR_IMP_Fkzx_kvas” successfully loaded/unloaded
IMPDP> Starting “SYS”.”TSPITR_IMP_Fkzx_kvas”:
IMPDP> Processing object type TABLE_EXPORT/TABLE/TABLE
IMPDP> Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
IMPDP> . . imported “SCOTT”.”EMP” 3.476 KB 1 rows
IMPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
IMPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
IMPDP> Job “SYS”.”TSPITR_IMP_Fkzx_kvas” successfully completed at Tue Dec 20 22:13:05 2015 elapsed 0 00:00:11
Import completed
Removing automatic instance
Automatic instance removed
auxiliary instance file /<PATH FOR AUXILIARY FILES>/O12102NP/datafile/o1_mf_temp_basw2co8_.tmp deleted
auxiliary instance file /<PATH FOR AUXILIARY FILES>/Fkzx_PITR_O12102NP/onlinelog/o1_mf_3_basw4md4_.log deleted
auxiliary instance file /<PATH FOR AUXILIARY FILES>/Fkzx_PITR_O12102NP/onlinelog/o1_mf_2_basw4m1t_.log deleted
auxiliary instance file /<PATH FOR AUXILIARY FILES>/Fkzx_PITR_O12102NP/onlinelog/o1_mf_1_basw4lhq_.log deleted
auxiliary instance file /<PATH FOR AUXILIARY FILES>/Fkzx_PITR_O12102NP/datafile/o1_mf_users_basw4gos_.dbf deleted
auxiliary instance file /<PATH FOR AUXILIARY FILES>/O12102NP/datafile/o1_mf_sysaux_basvy598_.dbf deleted
auxiliary instance file /<PATH FOR AUXILIARY FILES>/O12102NP/datafile/o1_mf_undotbs1_basvy590_.dbf deleted
auxiliary instance file /<PATH FOR AUXILIARY FILES>/O12102NP/datafile/o1_mf_system_basvy59h_.dbf deleted
auxiliary instance file /<PATH FOR AUXILIARY FILES>/O12102NP/controlfile/o1_mf_basvxw4w_.ctl deleted
auxiliary instance file tspitr_Fkzx_10996.dmp deleted
Finished recover at 20-Dec-15

That’s it and you table recovery has been completed.

Please comment below if you need details of the steps involved in recovering a table to a point in time.

 

Advertisements