Table Recovery Using RMAN Backup
Below steps are
used to recover the table records by using RMAN backup
Step 1:- User creation
SQL> create user vignesh
identified by vbt default tablespace users temporary tablespace temp profile
default account unlock;
User created.
Step 2:- Provide grant privileges to user –vignesh
SQL> grant create session,
create table to vignesh;
Grant succeeded.
SQL> alter user vignesh quota
500M on users;
User altered.
Step 3:- Table creation
SQL> conn vigneh/vbt
connected
SQL> create
table emp(id number);
Table created.
Insert 100000 records to the emp table
SQL> begin
for i in 1 .. 100000 loop
insert into emp values(i);
end loop;
end;
/
PL/SQL procedure successfully
completed.
SQL> commit;
Commit complete.
SQL> select count(*)from emp;
COUNT(*)
----------
100000
Get current system date and time
SQL> SELECT TO_CHAR (SYSDATE,
'MM-DD-YYYY HH24:MI:SS') "NOW" FROM DUAL;
NOW
-------------------
09-08-2021 03:51:13
Check archive log mode enabled
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 11
Next log sequence to archive 13
Current log sequence 13
SQL> exit
Step -4 Connect
rman and backup the full database with archivelogs
[oracle@vignesh dbs]$ rman target/
Recovery Manager: Release
19.0.0.0.0 - Production on Wed Sep 8 03:53:19 2021
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle
and/or its affiliates. All rights
reserved.
connected to target database:
LIVEDB (DBID=3038400691)
RMAN> backup database plus
archivelog;
Starting backup at 08-SEP-21
current log archived
using target database control
file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=86 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=5 RECID=1 STAMP=1082689816
input archived log thread=1
sequence=6 RECID=2 STAMP=1082689893
input archived log thread=1
sequence=7 RECID=3 STAMP=1082690477
input archived log thread=1
sequence=8 RECID=4 STAMP=1082690478
input archived log thread=1
sequence=9 RECID=5 STAMP=1082690480
input archived log thread=1
sequence=10 RECID=6 STAMP=1082690866
input archived log thread=1
sequence=11 RECID=7 STAMP=1082690927
input archived log thread=1
sequence=12 RECID=8 STAMP=1082691628
input archived log thread=1
sequence=13 RECID=9 STAMP=1082692404
channel ORA_DISK_1: starting
piece 1 at 08-SEP-21
channel ORA_DISK_1: finished
piece 1 at 08-SEP-21
piece
handle=/u01/app/oracle/fast_recovery_area/LIVEDB/backupset/2021_09_08/o1_mf_annnn_TAG20210908T035324_jmhslx4y_.bkp
tag=TAG20210908T035324 comment=NONE
channel ORA_DISK_1: backup set
complete, elapsed time: 00:00:15
Finished backup at 08-SEP-21
Starting backup at 08-SEP-21
using channel ORA_DISK_1
channel ORA_DISK_1: starting full
datafile backup set
channel ORA_DISK_1: specifying datafile(s)
in backup set
input datafile file number=00001
name=/u01/app/oracle/oradata/LIVEDB/datafile/o1_mf_system_jmho76m8_.dbf
input datafile file number=00003
name=/u01/app/oracle/oradata/LIVEDB/datafile/o1_mf_sysaux_jmho8n57_.dbf
input datafile file number=00004
name=/u01/app/oracle/oradata/LIVEDB/datafile/o1_mf_undotbs1_jmho9f8x_.dbf
input datafile file number=00007
name=/u01/app/oracle/oradata/LIVEDB/datafile/o1_mf_users_jmho9gcd_.dbf
channel ORA_DISK_1: starting
piece 1 at 08-SEP-21
channel ORA_DISK_1: finished
piece 1 at 08-SEP-21
piece
handle=/u01/app/oracle/fast_recovery_area/LIVEDB/backupset/2021_09_08/o1_mf_nnndf_TAG20210908T035340_jmhsmf58_.bkp
tag=TAG20210908T035340 comment=NONE
channel ORA_DISK_1: backup set
complete, elapsed time: 00:00:56
Finished backup at 08-SEP-21
Starting backup at 08-SEP-21
current log archived
using channel ORA_DISK_1
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=14
RECID=10 STAMP=1082692477
channel ORA_DISK_1: starting
piece 1 at 08-SEP-21
channel ORA_DISK_1: finished
piece 1 at 08-SEP-21
piece
handle=/u01/app/oracle/fast_recovery_area/LIVEDB/backupset/2021_09_08/o1_mf_annnn_TAG20210908T035437_jmhso67v_.bkp
tag=TAG20210908T035437 comment=NONE
channel ORA_DISK_1: backup set
complete, elapsed time: 00:00:01
Finished backup at 08-SEP-21
Starting Control File and SPFILE
Autobackup at 08-SEP-21
piece
handle=/u01/app/oracle/fast_recovery_area/LIVEDB/autobackup/2021_09_08/o1_mf_s_1082692480_jmhso8gf_.bkp
comment=NONE
Finished Control File and SPFILE
Autobackup at 08-SEP-21
RMAN> exit
Recovery Manager complete.
Once backup completed drop the table under the user for the
recovery purpose
SQL> drop table vignesh.emp;
Table dropped.
Step 5 -Connect
rman and recover the table data until mention the time of table record inserted
mention the auxiliary destination
[oracle@vignesh ~]$ rman target /
connected to target database:
LIVEDB (DBID=3038400691)
RMAN> recover table vignesh.emp until time
"to_date('2021-09-08 03:51:13','yyyy-mm-dd:hh24:mi:ss')" auxiliary
destination '/u01/ARC_BKP';
Starting recover at 08-SEP-21
using target database control
file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=57 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='hAij'
initialization parameters used
for automatic instance:
db_name=LIVEDB
db_unique_name=hAij_pitr_LIVEDB
compatible=19.0.0
db_block_size=8192
db_files=200
diagnostic_dest=/u01/app/oracle
_system_trig_enabled=FALSE
sga_target=1648M
processes=200
db_create_file_dest=/u01/ARC_BKP
log_archive_dest_1='location=/u01/ARC_BKP'
#No auxiliary parameter file used
starting up automatic instance
LIVEDB
Oracle instance started
Total System Global Area 1728050048 bytes
Fixed Size 8897408 bytes
Variable Size 402653184 bytes
Database Buffers 1308622848 bytes
Redo Buffers 7876608 bytes
Automatic instance created
contents of Memory Script:
{
# set requested point in time
set until time "to_date('2021-09-08
03:51:13','yyyy-mm-dd:hh24:mi:ss')";
# 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 08-SEP-21
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/fast_recovery_area/LIVEDB/autobackup/2021_09_08/o1_mf_s_1082690928_jmhr4ryc_.bkp
channel ORA_AUX_DISK_1: piece
handle=/u01/app/oracle/fast_recovery_area/LIVEDB/autobackup/2021_09_08/o1_mf_s_1082690928_jmhr4ryc_.bkp
tag=TAG20210908T032848
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/ARC_BKP/LIVEDB/controlfile/o1_mf_jmhstlpr_.ctl
Finished restore at 08-SEP-21
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 time "to_date('2021-09-08 03:51:13','yyyy-mm-dd:hh24:mi:ss')";
# 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/ARC_BKP/LIVEDB/datafile/o1_mf_temp_%u_.tmp in control file
Starting restore at 08-SEP-21
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 00001 to /u01/ARC_BKP/LIVEDB/datafile/o1_mf_system_%u_.dbf
channel ORA_AUX_DISK_1: restoring
datafile 00004 to /u01/ARC_BKP/LIVEDB/datafile/o1_mf_undotbs1_%u_.dbf
channel ORA_AUX_DISK_1: restoring
datafile 00003 to /u01/ARC_BKP/LIVEDB/datafile/o1_mf_sysaux_%u_.dbf
channel ORA_AUX_DISK_1: reading
from backup piece
/u01/app/oracle/fast_recovery_area/LIVEDB/backupset/2021_09_08/o1_mf_nnndf_TAG20210908T032750_jmhr2yv3_.bkp
channel ORA_AUX_DISK_1: piece
handle=/u01/app/oracle/fast_recovery_area/LIVEDB/backupset/2021_09_08/o1_mf_nnndf_TAG20210908T032750_jmhr2yv3_.bkp
tag=TAG20210908T032750
channel ORA_AUX_DISK_1: restored
backup piece 1
channel ORA_AUX_DISK_1: restore
complete, elapsed time: 00:01:06
Finished restore at 08-SEP-21
datafile 1 switched to datafile
copy
input datafile copy RECID=4
STAMP=1082692724 file name=/u01/ARC_BKP/LIVEDB/datafile/o1_mf_system_jmhsttmb_.dbf
datafile 4 switched to datafile
copy
input datafile copy RECID=5
STAMP=1082692724 file
name=/u01/ARC_BKP/LIVEDB/datafile/o1_mf_undotbs1_jmhsttog_.dbf
datafile 3 switched to datafile
copy
input datafile copy RECID=6
STAMP=1082692724 file
name=/u01/ARC_BKP/LIVEDB/datafile/o1_mf_sysaux_jmhsttob_.dbf
contents of Memory Script:
{
# set requested point in time
set until time "to_date('2021-09-08
03:51:13','yyyy-mm-dd:hh24:mi:ss')";
# 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 08-SEP-21
using channel ORA_AUX_DISK_1
starting media recovery
archived log for thread 1 with
sequence 11 is already on disk as file
/u01/app/oracle/fast_recovery_area/LIVEDB/archivelog/2021_09_08/o1_mf_1_11_jmhr4q32_.arc
archived log for thread 1 with
sequence 12 is already on disk as file
/u01/app/oracle/fast_recovery_area/LIVEDB/archivelog/2021_09_08/o1_mf_1_12_jmhrtn65_.arc
archived log for thread 1 with
sequence 13 is already on disk as file /u01/app/oracle/fast_recovery_area/LIVEDB/archivelog/2021_09_08/o1_mf_1_13_jmhslv5x_.arc
archived log file
name=/u01/app/oracle/fast_recovery_area/LIVEDB/archivelog/2021_09_08/o1_mf_1_11_jmhr4q32_.arc
thread=1 sequence=11
archived log file name=/u01/app/oracle/fast_recovery_area/LIVEDB/archivelog/2021_09_08/o1_mf_1_12_jmhrtn65_.arc
thread=1 sequence=12
archived log file
name=/u01/app/oracle/fast_recovery_area/LIVEDB/archivelog/2021_09_08/o1_mf_1_13_jmhslv5x_.arc
thread=1 sequence=13
media recovery complete, elapsed
time: 00:00:08
Finished recover at 08-SEP-21
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/ARC_BKP/LIVEDB/controlfile/o1_mf_jmhstlpr_.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 1728050048 bytes
Fixed Size 8897408 bytes
Variable Size 402653184 bytes
Database Buffers 1308622848 bytes
Redo Buffers 7876608 bytes
sql statement: alter system
set control_files = ''/u01/ARC_BKP/LIVEDB/controlfile/o1_mf_jmhstlpr_.ctl''
comment= ''RMAN set'' scope=spfile
Oracle instance shut down
connected to auxiliary database
(not started)
Oracle instance started
Total System Global Area 1728050048 bytes
Fixed Size 8897408 bytes
Variable Size 402653184 bytes
Database Buffers 1308622848 bytes
Redo Buffers 7876608 bytes
sql statement: alter database
mount clone database
contents of Memory Script:
{
# set requested point in time
set until time "to_date('2021-09-08
03:51:13','yyyy-mm-dd:hh24:mi:ss')";
# set destinations for recovery
set and auxiliary set datafiles
set newname for datafile 7 to new;
# restore the tablespaces in the
recovery set and the auxiliary set
restore clone datafile 7;
switch clone datafile all;
}
executing Memory Script
executing command: SET until
clause
executing command: SET NEWNAME
Starting restore at 08-SEP-21
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 00007 to /u01/ARC_BKP/HAIJ_PITR_LIVEDB/datafile/o1_mf_users_%u_.dbf
channel ORA_AUX_DISK_1: reading from
backup piece
/u01/app/oracle/fast_recovery_area/LIVEDB/backupset/2021_09_08/o1_mf_nnndf_TAG20210908T032750_jmhr2yv3_.bkp
channel ORA_AUX_DISK_1: piece
handle=/u01/app/oracle/fast_recovery_area/LIVEDB/backupset/2021_09_08/o1_mf_nnndf_TAG20210908T032750_jmhr2yv3_.bkp
tag=TAG20210908T032750
channel ORA_AUX_DISK_1: restored
backup piece 1
channel ORA_AUX_DISK_1: restore
complete, elapsed time: 00:00:01
Finished restore at 08-SEP-21
datafile 7 switched to datafile
copy
input datafile copy RECID=8 STAMP=1082692811
file name=/u01/ARC_BKP/HAIJ_PITR_LIVEDB/datafile/o1_mf_users_jmhszlhd_.dbf
contents of Memory Script:
{
# set requested point in time
set until time "to_date('2021-09-08
03:51:13','yyyy-mm-dd:hh24:mi:ss')";
# online the datafiles restored
or switched
sql clone "alter database
datafile 7 online";
# recover and open resetlogs
recover clone database
tablespace "USERS",
"SYSTEM", "UNDOTBS1", "SYSAUX" delete archivelog;
alter clone database open
resetlogs;
}
executing Memory Script
executing command: SET until
clause
sql statement: alter database
datafile 7 online
Starting recover at 08-SEP-21
using channel ORA_AUX_DISK_1
starting media recovery
archived log for thread 1 with
sequence 11 is already on disk as file /u01/app/oracle/fast_recovery_area/LIVEDB/archivelog/2021_09_08/o1_mf_1_11_jmhr4q32_.arc
archived log for thread 1 with
sequence 12 is already on disk as file
/u01/app/oracle/fast_recovery_area/LIVEDB/archivelog/2021_09_08/o1_mf_1_12_jmhrtn65_.arc
archived log for thread 1 with
sequence 13 is already on disk as file
/u01/app/oracle/fast_recovery_area/LIVEDB/archivelog/2021_09_08/o1_mf_1_13_jmhslv5x_.arc
archived log file
name=/u01/app/oracle/fast_recovery_area/LIVEDB/archivelog/2021_09_08/o1_mf_1_11_jmhr4q32_.arc
thread=1 sequence=11
archived log file
name=/u01/app/oracle/fast_recovery_area/LIVEDB/archivelog/2021_09_08/o1_mf_1_12_jmhrtn65_.arc
thread=1 sequence=12
archived log file
name=/u01/app/oracle/fast_recovery_area/LIVEDB/archivelog/2021_09_08/o1_mf_1_13_jmhslv5x_.arc
thread=1 sequence=13
media recovery complete, elapsed
time: 00:00:00
Finished recover at 08-SEP-21
database opened
contents of Memory Script:
{
# create directory for datapump
import
sql "create or replace
directory TSPITR_DIROBJ_DPDIR as ''
/u01/ARC_BKP''";
# create directory for datapump
export
sql clone "create or replace
directory TSPITR_DIROBJ_DPDIR as ''
/u01/ARC_BKP''";
}
executing Memory Script
sql statement: create or replace
directory TSPITR_DIROBJ_DPDIR as ''/u01/ARC_BKP''
sql statement: create or replace
directory TSPITR_DIROBJ_DPDIR as ''/u01/ARC_BKP''
Performing export of tables...
EXPDP> Starting "SYS"."TSPITR_EXP_hAij_tuvv":
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/TABLE
EXPDP> . . exported "VIGNESH"."EMP" 873.1 KB 100000 rows
EXPDP> Master table "SYS"."TSPITR_EXP_hAij_tuvv"
successfully loaded/unloaded
EXPDP>
******************************************************************************
EXPDP> Dump file set for SYS.TSPITR_EXP_hAij_tuvv is:
EXPDP>
/u01/ARC_BKP/tspitr_hAij_54955.dmp
EXPDP> Job "SYS"."TSPITR_EXP_hAij_tuvv"
successfully completed at Wed Sep 8 04:01:35 2021 elapsed 0 00:00:37
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_hAij_erbn"
successfully loaded/unloaded
IMPDP> Starting "SYS"."TSPITR_IMP_hAij_erbn":
IMPDP> Processing object type TABLE_EXPORT/TABLE/TABLE
IMPDP> Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
IMPDP> . . imported "VIGNESH"."EMP" 873.1 KB 100000 rows
IMPDP> Processing object type
TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
IMPDP> Job "SYS"."TSPITR_IMP_hAij_erbn"
successfully completed at Wed Sep 8 04:01:44 2021 elapsed 0 00:00:03
Import completed
Removing automatic instance
Automatic instance removed
auxiliary instance file
/u01/ARC_BKP/LIVEDB/datafile/o1_mf_temp_jmhsx8cg_.tmp deleted
auxiliary instance file /u01/ARC_BKP/HAIJ_PITR_LIVEDB/onlinelog/o1_mf_3_jmhszzv5_.log
deleted
auxiliary instance file
/u01/ARC_BKP/HAIJ_PITR_LIVEDB/onlinelog/o1_mf_2_jmhszobv_.log deleted
auxiliary instance file
/u01/ARC_BKP/HAIJ_PITR_LIVEDB/onlinelog/o1_mf_1_jmhszo49_.log deleted
auxiliary instance file
/u01/ARC_BKP/HAIJ_PITR_LIVEDB/datafile/o1_mf_users_jmhszlhd_.dbf deleted
auxiliary instance file
/u01/ARC_BKP/LIVEDB/datafile/o1_mf_sysaux_jmhsttob_.dbf deleted
auxiliary instance file
/u01/ARC_BKP/LIVEDB/datafile/o1_mf_undotbs1_jmhsttog_.dbf deleted
auxiliary instance file
/u01/ARC_BKP/LIVEDB/datafile/o1_mf_system_jmhsttmb_.dbf deleted
auxiliary instance file
/u01/ARC_BKP/LIVEDB/controlfile/o1_mf_jmhstlpr_.ctl deleted
auxiliary instance file
tspitr_hAij_54955.dmp deleted
Finished recover at 08-SEP-21
RMAN> exit
step 6- connect
database and the user to check the table can be recoverd
[oracle@vignesh ~]$ export ORACLE_SID=livedb
[oracle@vignesh ~]$ sqlplus / as
sysdba
SQL*Plus: Release 19.0.0.0.0 -
Production on Wed Sep 8 04:01:56 2021
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> select count(*)from
vignesh.emp;
COUNT(*)
----------
100000
SQL>
No comments:
Post a Comment