Procedure to restore selected list of archivelogs in oracle

Procedure to restore selected list of archivelogs

Steps overview:

0. Verify the control_file_record_keeptime parameter

1. Find out the list of archivelogs really available

2. Now define the goal of which archives we needed to restore

3. Verify if we have backup for the archvielog

4. Kick off the restore from rman

5. Verify if the archivelogs are now showing in v$archived_log

Execution:

0. Verify the control_file_record_keep_time parameter

show parameter control_file_record_keep_time;

Output:

SQL> show parameter control_file_record_keep_time; NAME                                 TYPE        VALUE ------------------------------------ ----------- ------------------------------ control_file_record_keep_time        integer     60

1. Find out the list of archivelogs really available

export NLS_DATE_FORMAT='DD/MON/YYYY HH24:MI:SS';

rman> crosscheck archivelog all;

alter session set nls_date_format='DD/MON/YYYY HH24:MI:SS';

set lines 200

set pages 3000

set colsep ,

select sequence#,status,completion_time,first_change#,next_change# from v$archived_log order by 1;

Output:

RMAN> crosscheck archivelog all; using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=73 device type=DISK validation succeeded for archived log archived log file name=/oraarch/GGSRC04T/archivelog/2023_08_26/o1_mf_1_15_lgncg2vp_.arc RECID=7 STAMP=1145901138 validation succeeded for archived log archived log file name=/oraarch/GGSRC04T/archivelog/2023_09_03/o1_mf_1_16_lh9r7shc_.arc RECID=8 STAMP=1146602380 validation succeeded for archived log ... validation succeeded for archived log archived log file name=/oraarch/GGSRC04T/archivelog/2023_09_30/o1_mf_1_35_lkhq2q2r_.arc RECID=41 STAMP=1148894951 validation succeeded for archived log archived log file name=/oraarch/GGSRC04T/archivelog/2023_09_30/o1_mf_1_36_lkj0lyq4_.arc RECID=42 STAMP=1148904688 Crosschecked 22 objects RMAN> SQL> alter session set nls_date_format='DD/MON/YYYY HH24:MI:SS'; Session altered. SQL> select sequence#,status,completion_time,first_change#,next_change# from v$archived_log order by sequence#;  SEQUENCE#,S,COMPLETION_TIME     ,FIRST_CHANGE#,NEXT_CHANGE# ----------,-,--------------------,-------------,------------         15,A,26/AUG/2023 17:52:18,       459810,      460509         16,A,03/SEP/2023 20:39:40,       460509,      577599         17,A,03/SEP/2023 20:57:41,       577599,      586943         18,A,03/SEP/2023 21:19:23,       586943,      590545         19,A,03/SEP/2023 21:21:02,       590545,      590839         20,A,03/SEP/2023 21:21:03,       590839,      590846         21,A,03/SEP/2023 21:24:51,       590846,      591366 ..         33,A,25/SEP/2023 16:50:51,       689053,      689058         34,A,25/SEP/2023 16:50:53,       689058,      689067         34,A,25/SEP/2023 16:50:54,       689058,      689067         35,A,30/SEP/2023 09:29:11,       689067,      721250         36,A,30/SEP/2023 12:11:28,       721250,      772138 36 rows selected.

2. Now define the goal of which archives we needed to restore

We need archives covering change 459732, this needs to be accessible from logminer. So I am going to restore archive which has this change-1 (459731).

>> goal defined

3. Verify if we have backup for the archvielog

list backup of archivelog from scn 459730;

output:

RMAN> list backup of archivelog from scn 459730; using target database control file instead of recovery catalog List of Backup Sets =================== BS Key  Size       Device Type Elapsed Time Completion Time ------- ---------- ----------- ------------ -------------------- 10      655.50K    DISK        00:00:00     26/AUG/2023 17:52:19         BP Key: 10   Status: AVAILABLE  Compressed: NO  Tag: TAG20230826T175219         Piece Name: /oraarch/GGSRC04T/DBBackup/rman_arch_GGSRC04T_3024q42j_1_1   List of Archived Logs in backup set 10   Thrd Seq     Low SCN    Low Time             Next SCN   Next Time   ---- ------- ---------- -------------------- ---------- ---------   1    14      458962     26/AUG/2023 17:42:45 459810     26/AUG/2023 17:48:39   1    15      459810     26/AUG/2023 17:48:39 460509     26/AUG/2023 17:52:18

4. Kick off the restore from rman

restore archivelog from scn 459730;

output:

RMAN> restore archivelog from scn 459730; Starting restore at 30/SEP/2023 12:25:28 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=68 device type=DISK archived log for thread 1 with sequence 15 is already on disk as file /oraarch/GGSRC04T/archivelog/2023_08_26/o1_mf_1_15_lgncg2vp_.arc archived log for thread 1 with sequence 16 is already on disk as file /oraarch/GGSRC04T/archivelog/2023_09_03/o1_mf_1_16_lh9r7shc_.arc archived log for thread 1 with sequence 17 is already on disk as file /oraarch/GGSRC04T/archivelog/2023_09_03/o1_mf_1_17_lh9s9nyw_.arc ... khq2q2r_.arc archived log for thread 1 with sequence 36 is already on disk as file /oraarch/GGSRC04T/archivelog/2023_09_30/o1_mf_1_36_lkj0lyq4_.arc channel ORA_DISK_1: starting archived log restore to default destination channel ORA_DISK_1: restoring archived log archived log thread=1 sequence=14 channel ORA_DISK_1: reading from backup piece /oraarch/GGSRC04T/DBBackup/rman_arch_GGSRC04T_3024q42j_1_1 channel ORA_DISK_1: piece handle=/oraarch/GGSRC04T/DBBackup/rman_arch_GGSRC04T_3024q42j_1_1 tag=TAG20230826T175219 channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: restore complete, elapsed time: 00:00:01 Finished restore at 30/SEP/2023 12:25:30 RMAN>

5. Verify if the archivelogs are now showing in v$archived_log

alter session set nls_date_format='DD/MON/YYYY HH24:MI:SS';

select sequence#,status,completion_time,first_change#,next_change# from v$archived_log order by 1;

Output:

SQL> select sequence#,status,completion_time,first_change#,next_change# from v$archived_log order by 1;  SEQUENCE#,S,COMPLETION_TIME     ,FIRST_CHANGE#,NEXT_CHANGE# ----------,-,--------------------,-------------,------------         14,A,30/SEP/2023 12:25:29,       458962,      459810         15,A,26/AUG/2023 17:52:18,       459810,      460509         16,A,03/SEP/2023 20:39:40,       460509,      577599         17,A,03/SEP/2023 20:57:41,       577599,      586943 ..         34,A,25/SEP/2023 16:50:54,       689058,      689067         35,A,30/SEP/2023 09:29:11,       689067,      721250         36,A,30/SEP/2023 12:11:28,       721250,      772138 37 rows selected. SQL> select name from v$archived_log where sequence#=14; NAME ------------------------------------------------------------------ /oraarch/GGSRC04T/archivelog/2023_09_30/o1_mf_1_14_lkj1f92g_.arc SQL>

YouTube Video: