{"id":5858,"date":"2025-12-01T07:00:00","date_gmt":"2025-12-01T04:00:00","guid":{"rendered":"https:\/\/www.dataplatform.gr\/?p=5858"},"modified":"2025-12-01T18:49:32","modified_gmt":"2025-12-01T15:49:32","slug":"pos-epanaferoyme-mia-oracle-database-poy-vrisketai","status":"publish","type":"post","link":"https:\/\/www.dataplatform.gr\/en\/pos-epanaferoyme-mia-oracle-database-poy-vrisketai\/","title":{"rendered":"How to restore an Oracle Database that is in archive-log mode with RMAN Restore"},"content":{"rendered":"

In previous<\/a> In this article we saw how to backup an Oracle database with RMAN<\/strong>, in this article we will see a simple case of data recovery with RMAN Restore<\/strong>There are many sub-cases with different steps, each of which we will see the steps to recover the database on the same machine with the same name.<\/p>\n\n\n\n

The footsteps<\/h2>\n\n\n\n

First, we run the following query to see the names, location and time of each backup, in our case we will assume that we want to restore the database to 16\/01\/2025 15:29:00 <\/code>:<\/p>\n\n\n\n

select to_char(end_time, 'DY dd\/mm\/yyyy hh24:mi:ss') ended_time, duration,\n       status, type, size_mb\n  from (SELECT \/*+ RULE *\/\n         dt.start_time strt_time, dt.end_time end_time, dt.time_taken_display duration,\n         dt.status, dt.input_type || decode(input_type, 'DB INCR',\n                                                        ' level ' || to_char(bsd.incr_level),\n                                                        '') type,\n         to_char(dt.output_bytes\/1024\/1024, '999g999g990d00', 'NLS_NUMERIC_CHARACTERS=,.') as SIZE_MB\n          from v$rman_backup_job_details dt,\n               (select \/*+ RULE *\/ session_stamp, session_recid, min(incremental_level) incr_level\n                  from v$backup_set_details\n                 group by session_stamp, session_recid) bsd\n         where dt.session_stamp = bsd.session_stamp\n           and dt.session_recid = bsd.session_recid\n        union all\n        select null, p.completion_time, null, null, p.handle,\n               to_char(d.blocks * d.block_size\/1024\/1024, '999g999g990d00', 'NLS_NUMERIC_CHARACTERS=,.') size_mb\n          from v$backup_piece p, v$backup_datafile d\n         where d.set_stamp = p.set_stamp\n           and d.set_count = p.set_count\n           and d.file# = 0)\n where end_time >= sysdate - 7 -- last 7 days\n order by end_time desc nulls last\n\/<\/pre>\n\n\n\n
\"\"
01<\/figcaption><\/figure>\n\n\n\n
Before restoring the database, we must delete all of its files from the operating system, so before downloading it, we run the following query to generate the commands:<\/p>\n\n\n\n
select 1 as type, 'rm \"'||name||'\"' os_cmd from v$controlfile\nunion all\nselect 2, 'rm \"'||member||'\"' from v$logfile\nunion all\nselect 3, 'rm \"'||name||'\"' from v$datafile\nunion all\nselect 4, 'rm \"'||name||'\"' from v$tempfile\nunion all\nselect 5, 'rm -rf \"'|| value ||'\"' from v$parameter where name like '%_dump_dest'\n||case when (select to_number(lpad(version, instr(version, '.', 1, 1)-1)) from v$instance) > 10 then 'garb' else '' end\nunion all\nselect 6, 'mkdir -p \"'|| value ||'\"' from v$parameter where name like '%_dump_dest'\n||case when (select to_number(lpad(version, instr(version, '.', 1, 1)-1)) from v$instance) > 10 then 'garb' else '' end\nunion all\nselect 7, '# rm -rf \"'||substr(value, 1, instr(value, dir_sep, -1)-1) ||'\"'\nfrom v$parameter, (select decode(substr(name, 2, 2), ':\\', '\\', '\\\\', '\\', '\/') as dir_sep from v$datafile where rownum=1) b\nwhere name like 'core_dump_dest'\n||case when (select to_number(lpad(version, instr(version, '.', 1, 1)-1)) from v$instance) < 11 then 'garb' else '' end\nunion all\nselect 8, '# mkdir -p \"'||substr(value, 1, instr(value, dir_sep, -1)-1) ||'\"'\nfrom v$parameter, (select decode(substr(name, 2, 2), ':\\', '\\', '\\\\', '\\', '\/') as dir_sep from v$datafile where rownum=1) b\nwhere name like 'core_dump_dest'\n||case when (select to_number(lpad(version, instr(version, '.', 1, 1)-1)) from v$instance) < 11 then 'garb' else '' end<\/pre>\n\n\n\n
\"\"
02<\/figcaption><\/figure>\n\n\n\n
Download the database after first creating one pfile <\/strong>from the spfile and delete its files with the commands we generated before:<\/p>\n\n\n\n
sqlplus \/ as sysdba\n\ncreate pfile from spfile;\n\nshutdown immediate;\n\nexit<\/pre>\n\n\n\n
Then we lift up to nomount <\/strong>state with pfile:<\/p>\n\n\n\n
sqlplus \/ as sysdba\n\nstartup nomount pfile=$ORACLE_HOME\/dbs\/initoradev.ora;\n\nexit;\n<\/pre>\n\n\n\n
We connect to RMAN<\/strong> and we restore it control file<\/strong> if necessary:<\/p>\n\n\n\n
rman target \/\n\nrestore controlfile from '\/oracle\/app\/backup\/20250116__controldb__ce3fd0vj_1_1';\n\nexit\n<\/pre>\n\n\n\n
We see that we successfully restored it:<\/p>\n\n\n\n
Starting restore at 16\/01\/025 15:53\nusing channel ORA_DISK_1\n\nchannel ORA_DISK_1: restoring control file\nchannel ORA_DISK_1: restore complete, elapsed time: 00:00:00\noutput file name=\/oracle\/app\/oracle\/product\/19.3.0\/dbhome_1\/dbs\/cntrloradev.dbf\nFinished restore at 16\/01\/025 15:53<\/code><\/pre>\n\n\n\n
We open the database in mount <\/strong>state and we do catalogue <\/strong>the location containing the backups:<\/p>\n\n\n\n
alter database mount;\ncatalog start with '\/oracle\/app\/backup';\n<\/pre>\n\n\n\n
<\/p>\n\n\n\n
The restoration<\/h3>\n\n\n\n
We run the following command in RMAN <\/strong>so that we can do restore<\/strong>, in case we restore them to another path then we do set newname<\/code> each datafile number in the new path: <\/p>\n\n\n\n
run{\n allocate channel t1 type disk;\n##SET NEWNAME FOR tempfile 1 to '\/u01\/app\/oracle\/oradata\/...\/TEMP01.DBF';\n##set newname for datafile 1 to '\/u01\/app\/oracle\/oradata\/...\/SYSTEM01.DBF';\nrestore database;\nswitch datafile all;\nswitch tempfile all;\n }\nexit;\n<\/pre>\n\n\n\n
allocated channel: t1\nchannel t1: SID=151 device type=DISK\n\nStarting restore at 16\/01\/025 15:58\n\nchannel t1: starting datafile backup set restore\nchannel t1: specifying datafile(s) to restore from backup set\nchannel t1: restoring datafile 00001 to \/oracle\/oradata\/ORADEV\/system01.dbf\nchannel t1: restoring datafile 00002 to \/oracle\/oradata\/ORADEV\/audit_ts_002.dbf\nchannel t1: restoring datafile 00003 to \/oracle\/oradata\/ORADEV\/sysaux01.dbf\nchannel t1: restoring datafile 00004 to \/oracle\/oradata\/ORADEV\/undotbs01.dbf\nchannel t1: restoring datafile 00005 to \/oracle\/oradata\/ORADEV\/test.dbf\nchannel t1: restoring datafile 00007 to \/oracle\/oradata\/ORADEV\/users01.dbf\nchannel t1: reading from backup piece \/oracle\/app\/backup\/20250116__fulldb__cd3fd0vd_1_1\nchannel t1: piece handle=\/oracle\/app\/backup\/20250116__fulldb__cd3fd0vd_1_1 tag=TAG20250116T151909\nchannel t1: restored backup piece 1\nchannel t1: reading from backup piece \/oracle\/app\/backup\/20250116__fulldb__cd3fd0vd_2_1\nchannel t1: piece handle=\/oracle\/app\/backup\/20250116__fulldb__cd3fd0vd_2_1 tag=TAG20250116T151909\nchannel t1: restored backup piece 2\nchannel t1: reading from backup piece \/oracle\/app\/backup\/20250116__fulldb__cd3fd0vd_3_1\nchannel t1: piece handle=\/oracle\/app\/backup\/20250116__fulldb__cd3fd0vd_3_1 tag=TAG20250116T151909\nchannel t1: restored backup piece 3\nchannel t1: reading from backup piece \/oracle\/app\/backup\/20250116__fulldb__cd3fd0vd_4_1\nchannel t1: piece handle=\/oracle\/app\/backup\/20250116__fulldb__cd3fd0vd_4_1 tag=TAG20250116T151909\nchannel t1: restored backup piece 4\nchannel t1: restore complete, elapsed time: 00:00:04\nFinished restore at 16\/01\/025 15:58\n\nreleased channel: t1\n<\/code><\/pre>\n\n\n\n
Once completed with the following query we see the time point where the database is located:<\/p>\n\n\n\n
set linesize 512 trimspool on pages 1000\ncolumn nxtchng format 99999999999999999999\ncolumn checkpoint_time format a20\ncolumn fuzzy format a5\ncolumn cnt format 999999\nselect \/*+ RULE *\/\nstatus, checkpoint_change# nxtchng,\nto_char(checkpoint_time, 'DD\/MM\/YYYY HH24:MI:SS') as checkpoint_time, fuzzy,\ncount(*) as cnt\nfrom v$datafile_header\ngroup by status, checkpoint_change#, checkpoint_time, fuzzy\norder by status, checkpoint_change#, checkpoint_time, fuzzy;<\/pre>\n\n\n\n
We see that it is at the time 15:19:09 <\/code>and we want to take it 10 minutes later. To do this we will have to do a recovery with set until time<\/code>:<\/p>\n\n\n\n
STATUS                NXTCHNG CHECKPOINT_TIME      FUZZY     CNT\n------- --------------------- -------------------- ----- -------\nONLINE               23933495 16\/01\/2025 15:19:09  NO          6<\/code><\/pre>\n\n\n\n
<\/p>\n\n\n\n
The recovery<\/h3>\n\n\n\n
To do recovery <\/strong>and the database arrives at the time 15:29:00 <\/code>we connect to RMAN<\/strong> and run the following with the parameter set until time<\/code>:<\/p>\n\n\n\n
rman target \/<\/pre>\n\n\n\n
run\n{\nallocate channel ch01 type disk;\nset archivelog destination to '\/oracle\/fast_recovery_area\/ORADEV\/archivelog';\nset until time \"to_date('16\/01\/2025 15:29:00','dd\/mm\/yyyy hh24:mi:ss')\";\nrecover database;\n}\nexit;\n<\/pre>\n\n\n\n
Starting recover at 16\/01\/025 17:26\n\nstarting media recovery\n\nchannel ch01: starting archived log restore to user-specified destination\narchived log destination=\/oracle\/fast_recovery_area\/ORADEV\/archivelog\nchannel ch01: restoring archived log\narchived log thread=1 sequence=121\nchannel ch01: restoring archived log\narchived log thread=1 sequence=122\nchannel ch01: restoring archived log\narchived log thread=1 sequence=123\nchannel ch01: reading from backup piece \/oracle\/app\/backup\/20250116___archivesdb_ci3fd1m7_1_1\nchannel ch01: piece handle=\/oracle\/app\/backup\/20250116___archivesdb_ci3fd1m7_1_1 tag=TAG20250116T153119\nchannel ch01: restored backup piece 1\nchannel ch01: restore complete, elapsed time: 00:00:01\narchived log file name=\/oracle\/fast_recovery_area\/ORADEV\/archivelog\/1_121_1163221998.dbf thread=1 sequence=121\narchived log file name=\/oracle\/fast_recovery_area\/ORADEV\/archivelog\/1_122_1163221998.dbf thread=1 sequence=122\narchived log file name=\/oracle\/fast_recovery_area\/ORADEV\/archivelog\/1_123_1163221998.dbf thread=1 sequence=123\nmedia recovery complete, elapsed time: 00:00:01\nFinished recover at 16\/01\/025 17:26\nreleased channel: ch01<\/code><\/pre>\n\n\n\n
After the recovery is complete, we confirm the database time with the query we ran before:<\/p>\n\n\n\n
set linesize 512 trimspool on pages 1000\ncolumn nxtchng format 99999999999999999999\ncolumn checkpoint_time format a20\ncolumn fuzzy format a5\ncolumn cnt format 999999\nselect \/*+ RULE *\/\nstatus, checkpoint_change# nxtchng,\nto_char(checkpoint_time, 'DD\/MM\/YYYY HH24:MI:SS') as checkpoint_time, fuzzy,\ncount(*) as cnt\nfrom v$datafile_header\ngroup by status, checkpoint_change#, checkpoint_time, fuzzy\norder by status, checkpoint_change#, checkpoint_time, fuzzy;<\/pre>\n\n\n\n
STATUS                NXTCHNG CHECKPOINT_TIME      FUZZY     CNT\n------- --------------------- -------------------- ----- -------\nONLINE               23936249 16\/01\/2025 15:29:00  NO          6<\/code><\/pre>\n\n\n\n
Once completed, we open it in open state<\/strong> with the parameter resetlogs<\/strong> and if everything goes well we restart it:<\/p>\n\n\n\n
sqlplus \/ as sysdba\n\nalter database open resetlogs;\n\nshutdown immediate;\n\nstartup;\n\nexit;<\/pre>\n\n\n\n
<\/p>\n\n\n\n
Sources:<\/h2>\n\n\n\n