User may drop or truncate a table or delete records accidently which need to recover. Even sometimes we may need to recover the data for a table for corrupted data. This is very difficult to restore the full DB if database size is huge like Terabytes. I will demonstrate with this Article how we can recover the single table from backup and archive log up-to point in time or SCN.
Prerequisites:
- Database must be an archive log mode.
- Ensure you have a valid backup of the database that includes the tablespace with all datafiles of the Table which want to recover.
- We need space on filesystem for SYSTEM, SYSAUX, UNDO and target table tablespace where Auxiliary DB files will create.
- Auxiliary Instance will create on server during the recover process and will automatically drop including newly created datafiles once recover is done. No Impact on Target database.
Note: Auxiliary Instance will create on server during the recover process and will automatically drop the Instance including newly created datafiles once recover is done. No much Impacted on target database but Auxiliary DB will consume few GB RAM from server.
Environment Setup:
SQL> archive log list Database log mode Archive Mode Automatic archival Enabled Archive destination USE_DB_RECOVERY_FILE_DEST Oldest online log sequence 5 Next log sequence to archive 7 Current log sequence 7 SQL> create user user_app identified by user_app; User created. SQL> create tablespace tbs_test datafile '/u01/oradata/ORCL/tbstest01.dbf' size 200M; Tablespace created. SQL> create tablespace tbs_apps datafile '/u01/oradata/ORCL/tbsapp01.dbf' size 20M; Tablespace created. SQL> select name from v$tablespace; NAME ------------------------------ SYSAUX SYSTEM UNDOTBS1 USERS TEMP TBS_TEST TBS_APPS 7 rows selected. SQL> select name from v$datafile; NAME -------------------------------------------------------------------------------- /u01/oradata/ORCL/system01.dbf /u01/oradata/ORCL/tbsapp01.dbf /u01/oradata/ORCL/sysaux01.dbf /u01/oradata/ORCL/undotbs01.dbf /u01/oradata/ORCL/tbstest01.dbf /u01/oradata/ORCL/users01.dbf 6 rows selected. SQL> alter user user_app default tablespace tbs_test; User altered. SQL> alter user user_app temporary tablespace temp; User altered. SQL> alter user user_app quota unlimited on tbs_test; User altered. SQL> create table user_app.test as select * from dba_objects; Table created.
Database Backup: (Assumed Production Database has daily backup)
run {
allocate channel c1 type disk format '/u01/backup/c1_%p_%s_%c.rman';
allocate channel c2 type disk format '/u01/backup/c2_%p_%s_%c.rman';
allocate channel c3 type disk format '/u01/backup/c3_%p_%s_%c.rman';
allocate channel c4 type disk format '/u01/backup/c4_%p_%s_%c.rman';
Backup database plus archivelog;
}
Create Table:
SQL> create table user_app.employees as select * from hr.employees;
Table created.
SQL> select count(*) from user_app.employees;
COUNT(*)
----------
107
SQL> select to_char(current_scn) from v$database;
TO_CHAR(CURRENT_SCN)
----------------------------------------
2206889
SQL> select to_char(sysdate, 'MM-DD-YYYY HH24:MI:SS') "DATE" from dual;
DATE
-------------------
10-26-2023 14:03:17
SQL> create table user_app.departments as select * from hr.departments;
Table created.
SQL> select count(*) from user_app.departments;
COUNT(*)
----------
27
SQL> drop table user_app.employees;
Table dropped.
SQL> select count(*) from user_app.employees;
select count(*) from user_app.employees
*
ERROR at line 1:
ORA-00942: table or view does not exist
Recover Table and Import to Database:
The RECOVER TABLE command with AUXILIARY DESTINATION location for the auxiliary database. The point in time can be specified using UNTIL SCN, UNTIL TIME or UNTIL SEQUENCE. In this case, we need to find the time or SCN before dropped or truncated of the table.
Using the REMAP TABLE clause is included to give the recovered table a new name so we can review before finalized the original table. In this example, I recovered using SCN from below command.
[oracle@orcl19c ~]$ cat emp_tbl_restore.sh #!/bin/ksh export ORACLE_SID=ORCL export ORACLE_HOME=/u01/app/oracle/product/19.0.0/dbhome_1 export PATH=$PATH:$ORACLE_HOME/bin rman target / msglog /home/oracle/employees_table_restore.log cmdfile=/home/oracle/emp_table_recover.cmd #Until SCN [oracle@orcl19c ~]$ cat emp_table_recover.cmd recover table user_app.employees until scn 2206889 auxiliary destination '/u01/recover' remap table user_app.employees:emp_prev;
Other Options: using time
# Until Time
[oracle@orcl19c ~]$ cat emp_table_recover.cmd
recover table user_app.employees
until time "to_date('10-26-2023 14:03:17','mm-dd-yyyy:hh24:mi:ss')"
auxiliary destination '/u01/recover'
remap table user_app.employees:emp_prev;
# recover table to dump file
recover table user_app.employees
until scn 2206889
auxiliary destination '/u01/recover'
datapump destination '/u01/export'
dump file 'test_t1_prev.dmp'
notableimport;
Recover script execute:
[oracle@orcl19c ~]$ nohup ./emp_tbl_restore.sh & [1] 6145 [oracle@orcl19c ~]$ nohup: ignoring input and appending output to ânohup.out
Temporary Auxiliary Database Created:
[oracle@orcl19c ~]$ ps -ef | grep smon oracle 3865 1 0 13:42 ? 00:00:00 ora_smon_orcl oracle 6391 3439 0 14:57 pts/1 00:00:00 grep --color=auto smon [oracle@orcl19c ~]$ ps -ef | grep smon oracle 3865 1 0 13:42 ? 00:00:00 ora_smon_orcl oracle 6437 1 0 14:57 ? 00:00:00 ora_smon_saBE oracle 6459 3439 0 14:57 pts/1 00:00:00 grep --color=auto smon [oracle@orcl19c ORCL]$ pwd /u01/recover [oracle@orcl19c recover]$ cd ORCL [oracle@orcl19c ORCL]$ ls controlfile datafile onlinelog
Recovery LOG:
[oracle@orcl19c datafile]$ cat /home/oracle/employees_table_restore.log
Recovery Manager: Release 19.0.0.0.0 - Production on Thu Oct 26 14:56:05 2023
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL (DBID=1678862077)
RMAN> recover table user_app.employees
2> until scn 2206889
3> auxiliary destination '/u01/recover'
4> remap table user_app.employees:emp_prev;
5>
Starting recover at 26-OCT-23
using target database control file instead of recovery catalog
current log archived
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=82 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 UNDOTBS1
Creating automatic instance, with SID='saBE'
initialization parameters used for automatic instance:
db_name=ORCL
db_unique_name=saBE_pitr_ORCL
compatible=19.0.0
db_block_size=8192
db_files=200
diagnostic_dest=/u01/app/oracle
_system_trig_enabled=FALSE
sga_target=3040M
processes=200
db_create_file_dest=/u01/recover
log_archive_dest_1='location=/u01/recover'
#No auxiliary parameter file used
starting up automatic instance ORCL
Oracle instance started
Total System Global Area 3187667928 bytes
Fixed Size 8901592 bytes
Variable Size 637534208 bytes
Database Buffers 2533359616 bytes
Redo Buffers 7872512 bytes
Automatic instance created
contents of Memory Script:
{
# set requested point in time
set until scn 2206889;
# 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';
}
executing Memory Script
executing command: SET until clause
Starting restore at 26-OCT-23
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=34 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/recovery_area/ORCL/autobackup/2023_10_26/o1_mf_s_1151243216_lmof10o5_.bkp
channel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/recovery_area/ORCL/autobackup/2023_10_26/o1_mf_s_1151243216_lmof10o5_.bkp tag=TAG20231026T134656
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/u01/recover/ORCL/controlfile/o1_mf_lmok3319_.ctl
Finished restore at 26-OCT-23
sql statement: alter database mount clone database
sql statement: alter system archive log current
contents of Memory Script:
{
# set requested point in time
set until scn 2206889;
# set destinations for recovery set and auxiliary set datafiles
set newname for clone datafile 1 to new;
set newname for clone datafile 4 to new;
set newname for clone datafile 3 to new;
set newname for clone tempfile 1 to new;
# switch all tempfiles
switch clone tempfile all;
# restore the tablespaces in the recovery set and the auxiliary set
restore clone datafile 1, 4, 3;
switch clone datafile all;
}
executing Memory Script
executing command: SET until clause
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
renamed tempfile 1 to /u01/recover/ORCL/datafile/o1_mf_temp_%u_.tmp in control file
Starting restore at 26-OCT-23
using channel ORA_AUX_DISK_1
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 00004 to /u01/recover/ORCL/datafile/o1_mf_undotbs1_%u_.dbf
channel ORA_AUX_DISK_1: reading from backup piece /u01/backup/c3_1_4_1.rman
channel ORA_AUX_DISK_1: piece handle=/u01/backup/c3_1_4_1.rman tag=TAG20231026T134639
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03
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 00003 to /u01/recover/ORCL/datafile/o1_mf_sysaux_%u_.dbf
channel ORA_AUX_DISK_1: reading from backup piece /u01/backup/c2_1_3_1.rman
channel ORA_AUX_DISK_1: piece handle=/u01/backup/c2_1_3_1.rman tag=TAG20231026T134639
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:07
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 /u01/recover/ORCL/datafile/o1_mf_system_%u_.dbf
channel ORA_AUX_DISK_1: reading from backup piece /u01/backup/c1_1_2_1.rman
channel ORA_AUX_DISK_1: piece handle=/u01/backup/c1_1_2_1.rman tag=TAG20231026T134639
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:15
Finished restore at 26-OCT-23
datafile 1 switched to datafile copy
input datafile copy RECID=4 STAMP=1151247410 file name=/u01/recover/ORCL/datafile/o1_mf_system_lmok3lz3_.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=5 STAMP=1151247410 file name=/u01/recover/ORCL/datafile/o1_mf_undotbs1_lmok38th_.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=6 STAMP=1151247410 file name=/u01/recover/ORCL/datafile/o1_mf_sysaux_lmok3cwn_.dbf
contents of Memory Script:
{
# set requested point in time
set until scn 2206889;
# online the datafiles restored or switched
sql clone "alter database datafile 1 online";
sql clone "alter database datafile 4 online";
sql clone "alter database datafile 3 online";
# recover and open database read only
recover clone database tablespace "SYSTEM", "UNDOTBS1", "SYSAUX";
sql clone 'alter database open read only';
}
executing Memory Script
executing command: SET until clause
sql statement: alter database datafile 1 online
sql statement: alter database datafile 4 online
sql statement: alter database datafile 3 online
Starting recover at 26-OCT-23
using channel ORA_AUX_DISK_1
starting media recovery
archived log for thread 1 with sequence 8 is already on disk as file /u01/app/oracle/recovery_area/ORCL/archivelog/2023_10_26/o1_mf_1_8_lmof0z3s_.arc
archived log for thread 1 with sequence 9 is already on disk as file /u01/app/oracle/recovery_area/ORCL/archivelog/2023_10_26/o1_mf_1_9_lmok2q6g_.arc
archived log file name=/u01/app/oracle/recovery_area/ORCL/archivelog/2023_10_26/o1_mf_1_8_lmof0z3s_.arc thread=1 sequence=8
archived log file name=/u01/app/oracle/recovery_area/ORCL/archivelog/2023_10_26/o1_mf_1_9_lmok2q6g_.arc thread=1 sequence=9
media recovery complete, elapsed time: 00:00:01
Finished recover at 26-OCT-23
sql statement: alter database open read only
contents of Memory Script:
{
sql clone "create spfile from memory";
shutdown clone immediate;
startup clone nomount;
sql clone "alter system set control_files =
''/u01/recover/ORCL/controlfile/o1_mf_lmok3319_.ctl'' comment=
''RMAN set'' scope=spfile";
shutdown clone immediate;
startup clone nomount;
# mount database
sql clone 'alter database mount clone database';
}
executing Memory Script
sql statement: create spfile from memory
database closed
database dismounted
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 3187667928 bytes
Fixed Size 8901592 bytes
Variable Size 637534208 bytes
Database Buffers 2533359616 bytes
Redo Buffers 7872512 bytes
sql statement: alter system set control_files = ''/u01/recover/ORCL/controlfile/o1_mf_lmok3319_.ctl'' comment= ''RMAN set'' scope=spfile
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 3187667928 bytes
Fixed Size 8901592 bytes
Variable Size 637534208 bytes
Database Buffers 2533359616 bytes
Redo Buffers 7872512 bytes
sql statement: alter database mount clone database
contents of Memory Script:
{
# set requested point in time
set until scn 2206889;
# set destinations for recovery set and auxiliary set datafiles
set newname for datafile 5 to new;
# restore the tablespaces in the recovery set and the auxiliary set
restore clone datafile 5;
switch clone datafile all;
}
executing Memory Script
executing command: SET until clause
executing command: SET NEWNAME
Starting restore at 26-OCT-23
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=34 device type=DISK
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 00005 to /u01/recover/SABE_PITR_ORCL/datafile/o1_mf_tbs_test_%u_.dbf
channel ORA_AUX_DISK_1: reading from backup piece /u01/backup/c3_1_4_1.rman
channel ORA_AUX_DISK_1: piece handle=/u01/backup/c3_1_4_1.rman tag=TAG20231026T134639
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03
Finished restore at 26-OCT-23
datafile 5 switched to datafile copy
input datafile copy RECID=8 STAMP=1151247477 file name=/u01/recover/SABE_PITR_ORCL/datafile/o1_mf_tbs_test_lmok62l0_.dbf
contents of Memory Script:
{
# set requested point in time
set until scn 2206889;
# online the datafiles restored or switched
sql clone "alter database datafile 5 online";
# recover and open resetlogs
recover clone database tablespace "TBS_TEST", "SYSTEM", "UNDOTBS1", "SYSAUX" delete archivelog;
alter clone database open resetlogs;
}
executing Memory Script
executing command: SET until clause
sql statement: alter database datafile 5 online
Starting recover at 26-OCT-23
using channel ORA_AUX_DISK_1
starting media recovery
archived log for thread 1 with sequence 8 is already on disk as file /u01/app/oracle/recovery_area/ORCL/archivelog/2023_10_26/o1_mf_1_8_lmof0z3s_.arc
archived log for thread 1 with sequence 9 is already on disk as file /u01/app/oracle/recovery_area/ORCL/archivelog/2023_10_26/o1_mf_1_9_lmok2q6g_.arc
archived log file name=/u01/app/oracle/recovery_area/ORCL/archivelog/2023_10_26/o1_mf_1_8_lmof0z3s_.arc thread=1 sequence=8
archived log file name=/u01/app/oracle/recovery_area/ORCL/archivelog/2023_10_26/o1_mf_1_9_lmok2q6g_.arc thread=1 sequence=9
media recovery complete, elapsed time: 00:00:00
Finished recover at 26-OCT-23
database opened
contents of Memory Script:
{
# create directory for datapump import
sql "create or replace directory TSPITR_DIROBJ_DPDIR as ''
/u01/recover''";
# create directory for datapump export
sql clone "create or replace directory TSPITR_DIROBJ_DPDIR as ''
/u01/recover''";
}
executing Memory Script
sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/u01/recover''
sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/u01/recover''
Performing export of tables...
EXPDP> Starting "SYS"."TSPITR_EXP_saBE_uqhv":
EXPDP> Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
EXPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
EXPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
EXPDP> Processing object type TABLE_EXPORT/TABLE/TABLE
EXPDP> . . exported "USER_APP"."EMPLOYEES" 17.09 KB 107 rows
EXPDP> Master table "SYS"."TSPITR_EXP_saBE_uqhv" successfully loaded/unloaded
EXPDP> ******************************************************************************
EXPDP> Dump file set for SYS.TSPITR_EXP_saBE_uqhv is:
EXPDP> /u01/recover/tspitr_saBE_58687.dmp
EXPDP> Job "SYS"."TSPITR_EXP_saBE_uqhv" successfully completed at Thu Oct 26 14:58:29 2023 elapsed 0 00:00:18
Export completed
contents of Memory Script:
{
# shutdown clone before import
shutdown clone abort
}
executing Memory Script
Oracle instance shut down
Performing import of tables...
IMPDP> Master table "SYS"."TSPITR_IMP_saBE_czvw" successfully loaded/unloaded
IMPDP> Starting "SYS"."TSPITR_IMP_saBE_czvw":
IMPDP> Processing object type TABLE_EXPORT/TABLE/TABLE
IMPDP> Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
IMPDP> . . imported "USER_APP"."EMP_PREV" 17.09 KB 107 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_saBE_czvw" successfully completed at Thu Oct 26 14:58:49 2023 elapsed 0 00:00:15
Import completed
Removing automatic instance
Automatic instance removed
auxiliary instance file /u01/recover/ORCL/datafile/o1_mf_temp_lmok43l2_.tmp deleted
auxiliary instance file /u01/recover/SABE_PITR_ORCL/onlinelog/o1_mf_3_lmok67ov_.log deleted
auxiliary instance file /u01/recover/SABE_PITR_ORCL/onlinelog/o1_mf_2_lmok66j3_.log deleted
auxiliary instance file /u01/recover/SABE_PITR_ORCL/onlinelog/o1_mf_1_lmok66hd_.log deleted
auxiliary instance file /u01/recover/SABE_PITR_ORCL/datafile/o1_mf_tbs_test_lmok62l0_.dbf deleted
auxiliary instance file /u01/recover/ORCL/datafile/o1_mf_sysaux_lmok3cwn_.dbf deleted
auxiliary instance file /u01/recover/ORCL/datafile/o1_mf_undotbs1_lmok38th_.dbf deleted
auxiliary instance file /u01/recover/ORCL/datafile/o1_mf_system_lmok3lz3_.dbf deleted
auxiliary instance file /u01/recover/ORCL/controlfile/o1_mf_lmok3319_.ctl deleted
auxiliary instance file tspitr_saBE_58687.dmp deleted
Finished recover at 26-OCT-23
Recovery Manager complete.
SQL> select count(*) from user_app.emp_prev;
COUNT(*)
----------
107
SQL> select count(*) from user_app.departments;
COUNT(*)
----------
27
SQL> create table user_app.employees as select * from user_app.emp_prev;
Table created.
SQL> select count(*) from user_app.employees;
COUNT(*)
----------
107
