Objective: Recreate standby controlfile in oracle
Situation which demands the controlfile recreation:
1. Changing backup retention policy
2. Errors in controlfile in standby
3. Oracle support suggested you
etc..
Server: Common for both primary and standby
Primary DB Name: GGSRC04T
Standby DB Uniq Name: GGSRC04TSB1
Reference:
https://www.vinoddbasupport.com/post/recreate-control-file-in-standby
Step By Step Guide On How To Recreate Standby Control File When Datafiles Are On ASM And Using Oracle Managed Files (Doc ID 734862.1)
Steps 1) Perform DB healthcheck in primary and standby+collect the db layout
set lines 1200 pages 30000 colsep , time on timing on trim on trims on long 30000
alter session set nls_Date_format='DD/MON/YYYY HH24:MI:SS';
col process for a10
col client_process for a15
col host_name for a40
select name,open_mode,database_role,db_unique_name,switchover_status,protection_mode,protection_level,log_mode,force_logging,flashback_on from v$database;
select inst_id,instance_name,status,host_name from gv$instance;
select distinct(status) from v$datafile;
select distincT(status) from dba_registry;
select distinct(status) from dba_indexes where owner in ('SYS','SYSTEM');
sho parameter standby_file_management
col member for a100
select * from V$logfile order by 1;
select * from V$log order by sequence#;
select * from V$standby_log order by sequence#;
sho parameter convert;
sho parameter control;
sho parameter pfile;
create pfile='<>' from spfile;
alter database backup controlfile to trace as '<>';
--- check the alert log.
report schema; -- from rman
Primary Output:
08:00:15 SQL> select name,open_mode,database_role,db_unique_name,switchover_status,protection_mode,protection_level,log_mode,force_logging,flashback_on from v$database; NAME ,OPEN_MODE ,DATABASE_ROLE ,DB_UNIQUE_NAME ,SWITCHOVER_STATUS ,PROTECTION_MODE ,PROTECTION_LEVEL ,LOG_MODE ,FORCE_LOGGING ,FLASHBACK_ON ---------,--------------------,----------------,------------------------------,--------------------,--------------------,--------------------,------------,---------------------------------------,------------------ GGSRC04T ,READ WRITE ,PRIMARY ,GGSRC04T ,TO STANDBY ,MAXIMUM PERFORMANCE ,MAXIMUM PERFORMANCE ,ARCHIVELOG ,YES ,NO Elapsed: 00:00:00.05 08:00:25 SQL> select inst_id,instance_name,status,host_name from gv$instance; INST_ID,INSTANCE_NAME ,STATUS ,HOST_NAME ----------,----------------,------------,---------------------------------------- 1,GGSRC04T ,OPEN ,vcentos79-oracle-ggsrc Elapsed: 00:00:00.01 08:01:06 SQL> select distinct(status) from v$datafile; STATUS ------- ONLINE SYSTEM Elapsed: 00:00:00.01 08:01:13 SQL> select distincT(status) from dba_registry; STATUS -------------------------------------------- VALID Elapsed: 00:00:00.07 08:01:18 SQL> select distinct(status) from dba_indexes where owner in ('SYS','SYSTEM'); STATUS -------- VALID N/A Elapsed: 00:00:00.09 08:01:22 SQL> sho parameter standby_file_management NAME ,TYPE ,VALUE ------------------------------------,-----------,------------------------------ standby_file_management ,string ,AUTO 08:01:29 SQL> col member for a100 08:01:40 SQL> select * from V$logfile order by 1; GROUP#,STATUS ,TYPE ,MEMBER ,IS_, CON_ID ----------,-------,-------,----------------------------------------------------------------------------------------------------,---,---------- 1, ,ONLINE ,/oradata/GGSRC04T/redo01.log ,NO , 0 2, ,ONLINE ,/oradata/GGSRC04T/redo02.log ,NO , 0 3, ,ONLINE ,/oradata/GGSRC04T/redo03.log ,NO , 0 4, ,STANDBY,/oradata/GGSRC04T/sbyredo04.log ,NO , 0 5, ,STANDBY,/oradata/GGSRC04T/sbyredo05.log ,NO , 0 6, ,STANDBY,/oradata/GGSRC04T/sbyredo06.log ,NO , 0 7, ,STANDBY,/oradata/GGSRC04T/sbyredo07.log ,NO , 0 7 rows selected. Elapsed: 00:00:00.01 08:01:44 SQL> select * from V$log order by sequence#; GROUP#, THREAD#, SEQUENCE#, BYTES, BLOCKSIZE, MEMBERS,ARC,STATUS ,FIRST_CHANGE#,FIRST_TIME ,NEXT_CHANGE#,NEXT_TIME , CON_ID ----------,----------,----------,----------,----------,----------,---,----------------,-------------,--------------------,------------,--------------------,---------- 3, 1, 57, 209715200, 512, 1,YES,INACTIVE , 1392965,04/JUN/2024 11:00:48, 1570215,24/JUN/2024 14:04:40, 0 1, 1, 58, 209715200, 512, 1,YES,INACTIVE , 1570215,24/JUN/2024 14:04:40, 1603798,05/AUG/2024 06:23:23, 0 2, 1, 59, 209715200, 512, 1,NO ,CURRENT , 1603798,05/AUG/2024 06:23:23, 1.8447E+19, , 0 Elapsed: 00:00:00.01 08:02:07 SQL> select * from V$standby_log order by sequence#; GROUP#,DBID , THREAD#, SEQUENCE#, BYTES, BLOCKSIZE, USED,ARC,STATUS ,FIRST_CHANGE#,FIRST_TIME ,NEXT_CHANGE#,NEXT_TIME ,LAST_CHANGE#,LAST_TIME , CON_ID ----------,----------------------------------------,----------,----------,----------,----------,----------,---,----------,-------------,--------------------,------------,--------------------,------------,--------------------,---------- 4,UNASSIGNED , 0, 0, 209715200, 512, 0,YES,UNASSIGNED, , , , , , , 0 7,UNASSIGNED , 0, 0, 209715200, 512, 0,YES,UNASSIGNED, , , , , , , 0 6,UNASSIGNED , 0, 0, 209715200, 512, 0,YES,UNASSIGNED, , , , , , , 0 5,UNASSIGNED , 0, 0, 209715200, 512, 0,YES,UNASSIGNED, , , , , , , 0 Elapsed: 00:00:00.01 08:02:24 SQL> sho parameter convert; NAME ,TYPE ,VALUE ------------------------------------,-----------,------------------------------ db_file_name_convert ,string , log_file_name_convert ,string , pdb_file_name_convert ,string , 08:02:33 SQL> sho parameter control; NAME ,TYPE ,VALUE ------------------------------------,-----------,------------------------------ control_file_record_keep_time ,integer ,60 control_files ,string ,/oradata/GGSRC04T/control01.ct , ,l, /oradata/GGSRC04T/control02 , ,.ctl control_management_pack_access ,string ,DIAGNOSTIC+TUNING 08:02:46 SQL> sho parameter pfile; NAME ,TYPE ,VALUE ------------------------------------,-----------,------------------------------ spfile ,string ,/u01/app/oracle/product/12.2.0 , ,.1/db_1/dbs/spfileGGSRC04T.ora 08:02:53 SQL> !mkdir -p ~/dba/sbyctrlcrecreate/ 08:03:26 SQL> !cd ~/dba/sbyctrlcrecreate/ 08:03:31 SQL> ! [oracle@vcentos79-oracle-ggsrc dba]$ cd ~/dba/sbyctrlcrecreate/ [oracle@vcentos79-oracle-ggsrc sbyctrlcrecreate]$ pwd /home/oracle/dba/sbyctrlcrecreate [oracle@vcentos79-oracle-ggsrc sbyctrlcrecreate]$ exit exit 08:03:40 SQL> create pfile='/home/oracle/dba/sbyctrlcrecreate/pfileGGSRC04T_05aug2024_premod.ora' from spfile; File created. Elapsed: 00:00:00.02 08:04:09 SQL> 08:04:14 SQL> alter database backup controlfile to trace as '/home/oracle/dba/sbyctrlcrecreate/controlGGSRC04T_05aug2024_pre.trc'; Database altered. Elapsed: 00:00:00.08 08:04:51 SQL> exit Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production [oracle@vcentos79-oracle-ggsrc dba]$ rman target / Recovery Manager: Release 12.2.0.1.0 - Production on Mon Aug 5 08:04:59 2024 Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved. connected to target database: GGSRC04T (DBID=4198404018) RMAN> report schema; using target database control file instead of recovery catalog Report of database schema for database with db_unique_name GGSRC04T List of Permanent Datafiles =========================== File Size(MB) Tablespace RB segs Datafile Name ---- -------- -------------------- ------- ------------------------ 1 700 SYSTEM YES /oradata/GGSRC04T/system01.dbf 2 550 SYSAUX NO /oradata/GGSRC04T/sysaux01.dbf 3 285 UNDOTBS1 YES /oradata/GGSRC04T/undotbs01.dbf 4 5 USERS NO /oradata/GGSRC04T/users01.dbf 5 1 ENCRYPT_TS1 NO /oradata/GGSRC04T/encrypt_ts1_01.dbf 6 100 GG_DATA NO /oradata/GGSRC04T/gg_data_01.dbf 7 100 GG_DATA2 NO /oradata/GGSRC04T/gg_data2_01.dbf 8 100 TBSPC_ABMR_26MAY NO /oradata/GGSRC04T/datafile/o1_mf_tbspc_ab_m564t46q_.dbf 9 100 TBSPC_ABMR_INDX_26MAY NO /oradata/GGSRC04T/datafile/o1_mf_tbspc_ab_m564tbpb_.dbf List of Temporary Files ======================= File Size(MB) Tablespace Maxsize(MB) Tempfile Name ---- -------- -------------------- ----------- -------------------- 1 20 TEMP 32767 /oradata/GGSRC04T/temp01.dbf RMAN> exit Recovery Manager complete. [oracle@vcentos79-oracle-ggsrc dba]$ |
SBY output:
08:06:30 SQL> select name,open_mode,database_role,db_unique_name,switchover_status,protection_mode,protection_level,log_mode,force_logging,flashback_on from v$database; NAME ,OPEN_MODE ,DATABASE_ROLE ,DB_UNIQUE_NAME ,SWITCHOVER_STATUS ,PROTECTION_MODE ,PROTECTION_LEVEL ,LOG_MODE ,FORCE_LOGGING ,FLASHBACK_ON ---------,--------------------,----------------,------------------------------,--------------------,--------------------,--------------------,------------,---------------------------------------,------------------ GGSRC04T ,READ ONLY ,PHYSICAL STANDBY,GGSRC04TSB1 ,NOT ALLOWED ,MAXIMUM PERFORMANCE ,MAXIMUM PERFORMANCE ,ARCHIVELOG ,YES ,NO Elapsed: 00:00:00.02 08:06:35 SQL> select inst_id,instance_name,status,host_name from gv$instance; INST_ID,INSTANCE_NAME ,STATUS ,HOST_NAME ----------,----------------,------------,---------------------------------------- 1,GGSRC04TSB1 ,OPEN ,vcentos79-oracle-ggsrc Elapsed: 00:00:00.01 08:06:56 SQL> select distinct(status) from v$datafile; STATUS ------- ONLINE SYSTEM RECOVER Elapsed: 00:00:00.00 08:07:02 SQL> select distincT(status) from dba_registry; STATUS -------------------------------------------- VALID Elapsed: 00:00:00.13 08:07:09 SQL> select distinct(status) from dba_indexes where owner in ('SYS','SYSTEM'); STATUS -------- VALID N/A Elapsed: 00:00:00.23 08:07:17 SQL> sho parameter standby_file_management NAME ,TYPE ,VALUE ------------------------------------,-----------,------------------------------ standby_file_management ,string ,AUTO 08:07:22 SQL> 08:07:32 SQL> col member for a100 08:07:34 SQL> select * from V$logfile order by 1; GROUP#,STATUS ,TYPE ,MEMBER ,IS_, CON_ID ----------,-------,-------,----------------------------------------------------------------------------------------------------,---,---------- 1, ,ONLINE ,/oradata/GGSRC04TSB1/redo01.log ,NO , 0 2, ,ONLINE ,/oradata/GGSRC04TSB1/redo02.log ,NO , 0 3, ,ONLINE ,/oradata/GGSRC04TSB1/redo03.log ,NO , 0 4, ,STANDBY,/oradata/GGSRC04TSB1/sbyredo04.log ,NO , 0 5, ,STANDBY,/oradata/GGSRC04TSB1/sbyredo05.log ,NO , 0 6, ,STANDBY,/oradata/GGSRC04TSB1/sbyredo06.log ,NO , 0 7, ,STANDBY,/oradata/GGSRC04TSB1/sbyredo07.log ,NO , 0 7 rows selected. Elapsed: 00:00:00.01 08:07:39 SQL> select * from V$log order by sequence#; GROUP#, THREAD#, SEQUENCE#, BYTES, BLOCKSIZE, MEMBERS,ARC,STATUS ,FIRST_CHANGE#,FIRST_TIME ,NEXT_CHANGE#,NEXT_TIME , CON_ID ----------,----------,----------,----------,----------,----------,---,----------------,-------------,--------------------,------------,--------------------,---------- 1, 1, 0, 209715200, 512, 1,YES,UNUSED , 600558,04/SEP/2023 11:05:52, 1.8447E+19, , 0 3, 1, 0, 209715200, 512, 1,YES,UNUSED , 0, , 0, , 0 2, 1, 0, 209715200, 512, 1,YES,UNUSED , 0, , 0, , 0 Elapsed: 00:00:00.01 08:07:52 SQL> select * from V$standby_log order by sequence#; GROUP#,DBID , THREAD#, SEQUENCE#, BYTES, BLOCKSIZE, USED,ARC,STATUS ,FIRST_CHANGE#,FIRST_TIME ,NEXT_CHANGE#,NEXT_TIME ,LAST_CHANGE#,LAST_TIME , CON_ID ----------,----------------------------------------,----------,----------,----------,----------,----------,---,----------,-------------,--------------------,------------,--------------------,------------,--------------------,---------- 4,UNASSIGNED , 1, 0, 209715200, 512, 0,NO ,UNASSIGNED, , , , , , , 0 7,UNASSIGNED , 0, 0, 209715200, 512, 0,YES,UNASSIGNED, , , , , , , 0 6,UNASSIGNED , 0, 0, 209715200, 512, 0,YES,UNASSIGNED, , , , , , , 0 5,4198404018 , 1, 59, 209715200, 512, 24878592,YES,ACTIVE ,1603798,05/AUG/2024 06:23:23, , , 1621236,05/AUG/2024 08:08:03, 0 Elapsed: 00:00:00.01 08:08:03 SQL> sho parameter convert; NAME ,TYPE ,VALUE ------------------------------------,-----------,------------------------------ db_file_name_convert ,string ,/oradata/GGSRC04T, /oradata/GG , ,SRC04TSB1 log_file_name_convert ,string ,/oradata/GGSRC04T, /oradata/GG , ,SRC04TSB1 pdb_file_name_convert ,string , 08:08:09 SQL> sho parameter control; NAME ,TYPE ,VALUE ------------------------------------,-----------,------------------------------ control_file_record_keep_time ,integer ,7 control_files ,string ,/oradata/GGSRC04TSB1/control01 , ,_GGSRC04TSB1.ctl, /oradata/GGS , ,RC04TSB1/control02_GGSRC04TSB1 , ,.ctl control_management_pack_access ,string ,DIAGNOSTIC+TUNING 08:08:24 SQL> sho parameter pfile; NAME ,TYPE ,VALUE ------------------------------------,-----------,------------------------------ spfile ,string ,/u01/app/oracle/product/12.2.0 , ,.1/db_1/dbs/spfileGGSRC04TSB1. , ,ora 08:08:50 SQL> create pfile='/home/oracle/dba/sbyctrlcrecreate/pfileGGSRC04TSB1_premod.ora' from spfile; File created. Elapsed: 00:00:00.00 08:09:26 SQL> alter database backup controlfile to trace as '/home/oracle/dba/sbyctrlcrecreate/controlGGSRC04TSB1_premod.trc'; Database altered. Elapsed: 00:00:00.00 08:09:51 SQL> exit Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production [oracle@vcentos79-oracle-ggsrc ~]$ rman target / Recovery Manager: Release 12.2.0.1.0 - Production on Mon Aug 5 08:10:32 2024 Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved. connected to target database: GGSRC04T (DBID=4198404018) RMAN> report schema; using target database control file instead of recovery catalog RMAN-06139: warning: control file is not current for REPORT SCHEMA Report of database schema for database with db_unique_name GGSRC04TSB1 List of Permanent Datafiles =========================== File Size(MB) Tablespace RB segs Datafile Name ---- -------- -------------------- ------- ------------------------ 1 700 SYSTEM YES /oradata/GGSRC04TSB1/system01.dbf 2 550 SYSAUX NO /oradata/GGSRC04TSB1/sysaux01.dbf 3 285 UNDOTBS1 YES /oradata/GGSRC04TSB1/undotbs01.dbf 4 5 USERS NO /oradata/GGSRC04TSB1/users01.dbf 5 1 ENCRYPT_TS1 NO /oradata/GGSRC04TSB1/encrypt_ts1_01.dbf 6 100 GG_DATA NO /oradata/GGSRC04TSB1/gg_data_01.dbf 7 100 GG_DATA2 NO /oradata/GGSRC04TSB1/datafile/o1_mf_gg_data2_lw6xzbqr_.dbf 8 100 TBSPC_ABMR_26MAY NO /oradata/GGSRC04TSB1/datafile/o1_mf_tbspc_ab_m564t4q5_.dbf 9 100 TBSPC_ABMR_INDX_26MAY NO /oradata/GGSRC04TSB1/datafile/o1_mf_tbspc_ab_m564tcq0_.dbf List of Temporary Files ======================= File Size(MB) Tablespace Maxsize(MB) Tempfile Name ---- -------- -------------------- ----------- -------------------- 1 20 TEMP 32767 /oradata/GGSRC04TSB1/temp01.dbf RMAN> exit Recovery Manager complete. [oracle@vcentos79-oracle-ggsrc ~]$ [oracle@vcentos79-oracle-ggsrc sbyctrlcrecreate]$ ls -altr total 36 drwxr-xr-x. 13 oracle oinstall 4096 Aug 5 08:03 .. -rw-r--r--. 1 oracle oinstall 1730 Aug 5 08:04 pfileGGSRC04T_05aug2024_premod.ora -rw-r--r--. 1 oracle oinstall 8418 Aug 5 08:04 controlGGSRC04T_05aug2024_pre.trc -rw-r--r--. 1 oracle oinstall 1872 Aug 5 08:09 pfileGGSRC04TSB1_premod.ora drwxr-xr-x. 2 oracle oinstall 161 Aug 5 08:09 . -rw-r--r--. 1 oracle oinstall 9711 Aug 5 08:09 controlGGSRC04TSB1_premod.trc [oracle@vcentos79-oracle-ggsrc sbyctrlcrecreate]$ |
Step 2) Perform dataguard health check or standby health check
set lines 1200 pages 3000 colsep , time on timing on trim on trims on long 30000
alter session set nls_date_format='DD/MON/YYYY HH24:MI:SS';
select name,open_mode from v$database;
select instance_name,status,startup_time from gv$instance;
show parameter config;
show parameter log_archive_dest;
select process,status,client_process,sequence# from gv$managed_standby;
select scn,GUARANTEE_FLASHBACK_DATABASE,NAME from V$RESTORE_POINT;
select * from v$log;
select * from v$standby_log;
alter database recover automatic managed standby database disconnect;
select process,status,client_process,sequence# from gv$managed_standby;
select thread#, sequence#,first_Time "sync time" from v$log_history
where (thread#, sequence#) in
(select thread#, max(sequence#)
from v$log_history group by thread#);
select arch.thread# "Thread",
arch.sequence# "Last Sequence Received",
appl.sequence# "Last Sequence Applied",
(arch.sequence# - appl.sequence#) "Difference"
from (select thread#, sequence# from v$archived_log
where (thread#, first_time) in
(select thread#, max(first_time)
from v$archived_log group by thread#)) arch,
(select thread#, sequence# from v$log_history
where (thread#, first_time) in
(select thread#, max(first_time)
from v$log_history group by thread#)) appl
where arch.thread# = appl.thread#;
select count(*) from v$archive_gap;
Output:
SQL> set lines 1200 pages 3000 colsep , time on timing on trim on trims on long 30000 alter session set nls_date_format='DD/MON/YYYY HH24:MI:SS';08:11:51 SQL> Session altered. Elapsed: 00:00:00.00 08:11:51 SQL> select name,open_mode from v$database; NAME ,OPEN_MODE ---------,-------------------- GGSRC04T ,READ ONLY Elapsed: 00:00:00.00 08:11:56 SQL> select instance_name,status,startup_time from gv$instance; INSTANCE_NAME ,STATUS ,STARTUP_TIME ----------------,------------,-------------------- GGSRC04TSB1 ,OPEN ,05/AUG/2024 06:23:54 Elapsed: 00:00:00.01 08:11:59 SQL> show parameter config; NAME ,TYPE ,VALUE ------------------------------------,-----------,------------------------------ dg_broker_config_file1 ,string ,/u01/app/oracle/product/12.2.0 , ,.1/db_1/dbs/dr1GGSRC04TSB1.dat dg_broker_config_file2 ,string ,/u01/app/oracle/product/12.2.0 , ,.1/db_1/dbs/dr2GGSRC04TSB1.dat log_archive_config ,string ,DG_CONFIG=(GGSRC04T,GGSRC04TSB , ,1) 08:12:03 SQL> show parameter log_archive_dest; NAME ,TYPE ,VALUE ------------------------------------,-----------,------------------------------ log_archive_dest ,string , log_archive_dest_1 ,string ,LOCATION=USE_DB_RECOVERY_FILE_ , ,DEST VALID_FOR=(ALL_LOGFILES,A , ,LL_ROLES) DB_UNIQUE_NAME=GGSR , ,C04TSB1 .. log_archive_dest_2 ,string ,service=GGSRC04T ASYNC valid_f , ,or=(ONLINE_LOGFILE,PRIMARY_ROL , ,E) db_unique_name=GGSRC04T log_archive_dest_20 ,string , .. 08:12:13 SQL> select process,status,client_process,sequence# from gv$managed_standby; PROCESS ,STATUS ,CLIENT_P, SEQUENCE# ---------,------------,--------,---------- ARCH ,CONNECTED ,ARCH , 0 DGRD ,ALLOCATED ,N/A , 0 DGRD ,ALLOCATED ,N/A , 0 ARCH ,CONNECTED ,ARCH , 0 ARCH ,CLOSING ,ARCH , 55 ARCH ,CONNECTED ,ARCH , 0 RFS ,IDLE ,Archival, 0 RFS ,IDLE ,LGWR , 59 RFS ,IDLE ,UNKNOWN , 0 RFS ,IDLE ,UNKNOWN , 0 RFS ,IDLE ,UNKNOWN , 0 11 rows selected. Elapsed: 00:00:00.00 08:12:35 SQL> select scn,GUARANTEE_FLASHBACK_DATABASE,NAME from V$RESTORE_POINT; no rows selected Elapsed: 00:00:00.02 08:12:41 SQL> select * from v$log; GROUP#, THREAD#, SEQUENCE#, BYTES, BLOCKSIZE, MEMBERS,ARC,STATUS ,FIRST_CHANGE#,FIRST_TIME ,NEXT_CHANGE#,NEXT_TIME , CON_ID ----------,----------,----------,----------,----------,----------,---,----------------,-------------,--------------------,------------,--------------------,---------- 1, 1, 0, 209715200, 512, 1,YES,UNUSED , 600558,04/SEP/2023 11:05:52, 1.8447E+19, , 0 2, 1, 0, 209715200, 512, 1,YES,UNUSED , 0, , 0, , 0 3, 1, 0, 209715200, 512, 1,YES,UNUSED , 0, , 0, , 0 Elapsed: 00:00:00.00 08:12:47 SQL> select * from v$standby_log; GROUP#,DBID , THREAD#, SEQUENCE#, BYTES, BLOCKSIZE, USED,ARC,STATUS ,FIRST_CHANGE#,FIRST_TIME ,NEXT_CHANGE#,NEXT_TIME ,LAST_CHANGE#,LAST_TIME , CON_ID ----------,----------------------------------------,----------,----------,----------,----------,----------,---,----------,-------------,--------------------,------------,--------------------,------------,--------------------,---------- 4,UNASSIGNED , 1, 0, 209715200, 512, 0,NO ,UNASSIGNED, , , , , , , 0 5,4198404018 , 1, 59, 209715200, 512, 25395712,YES,ACTIVE ,1603798,05/AUG/2024 06:23:23, , , 1622236,05/AUG/2024 08:12:53, 0 6,UNASSIGNED , 0, 0, 209715200, 512, 0,YES,UNASSIGNED, , , , , , , 0 7,UNASSIGNED , 0, 0, 209715200, 512, 0,YES,UNASSIGNED, , , , , , , 0 Elapsed: 00:00:00.00 08:12:53 SQL> alter database recover automatic managed standby database disconnect; Database altered. Elapsed: 00:00:06.03 08:13:19 SQL> select process,status,client_process,sequence# from gv$managed_standby; PROCESS ,STATUS ,CLIENT_P, SEQUENCE# ---------,------------,--------,---------- ARCH ,CONNECTED ,ARCH , 0 DGRD ,ALLOCATED ,N/A , 0 DGRD ,ALLOCATED ,N/A , 0 ARCH ,CONNECTED ,ARCH , 0 ARCH ,CLOSING ,ARCH , 55 ARCH ,CONNECTED ,ARCH , 0 RFS ,IDLE ,Archival, 0 RFS ,IDLE ,LGWR , 59 RFS ,IDLE ,UNKNOWN , 0 RFS ,IDLE ,UNKNOWN , 0 RFS ,IDLE ,UNKNOWN , 0 MRP0 ,APPLYING_LOG,N/A , 59 12 rows selected. Elapsed: 00:00:00.03 08:13:54 SQL> select thread#, sequence#,first_Time "sync time" from v$log_history where (thread#, sequence#) in (select thread#, max(sequence#) from v$log_history group by thread#);08:14:15 2 08:14:15 3 08:14:15 4 THREAD#, SEQUENCE#,sync time ----------,----------,-------------------- 1, 58,24/JUN/2024 14:04:40 Elapsed: 00:00:00.02 08:14:16 SQL> select arch.thread# "Thread", arch.sequence# "Last Sequence Received", appl.sequence# "Last Sequence Applied", (arch.sequence# - appl.sequence#) "Difference" from (select thread#, sequence# from v$archived_log where (thread#, first_time) in (select thread#, max(first_time) from v$archived_log group by thread#)) arch, (select thread#, sequence# from v$log_history where (thread#, first_time) in (select thread#, max(first_time) from v$log_history group by thread#)) appl where arch.thread# = appl.thread#;08:14:28 2 08:14:28 3 08:14:28 4 08:14:28 5 08:14:28 6 08:14:28 7 08:14:28 8 08:14:28 9 08:14:28 10 08:14:28 11 08:14:28 12 08:14:28 13 Thread,Last Sequence Received,Last Sequence Applied,Difference ----------,----------------------,---------------------,---------- 1, 58, 58, 0 Elapsed: 00:00:00.25 08:14:29 SQL> select count(*) from v$archive_gap; COUNT(*) ---------- 0 Elapsed: 00:00:00.05 08:14:36 SQL> alter database recover managed standby database cancel; Database altered. Elapsed: 00:00:01.01 08:14:43 SQL> |
Step 3) Stop the recovery
alter database recover managed standby database cancel;
select process,status,client_process,sequence# from gv$managed_standby;
Output:
08:14:36 SQL> alter database recover managed standby database cancel; Database altered. Elapsed: 00:00:01.01 08:14:43 SQL> 08:14:43 SQL> select process,status,client_process,sequence# from gv$managed_standby; PROCESS ,STATUS ,CLIENT_P, SEQUENCE# ---------,------------,--------,---------- ARCH ,CONNECTED ,ARCH , 0 DGRD ,ALLOCATED ,N/A , 0 DGRD ,ALLOCATED ,N/A , 0 ARCH ,CONNECTED ,ARCH , 0 ARCH ,CLOSING ,ARCH , 55 ARCH ,CONNECTED ,ARCH , 0 RFS ,IDLE ,Archival, 0 RFS ,IDLE ,LGWR , 59 RFS ,IDLE ,UNKNOWN , 0 RFS ,IDLE ,UNKNOWN , 0 RFS ,IDLE ,UNKNOWN , 0 11 rows selected. Elapsed: 00:00:00.00 08:18:11 SQL> |
Step 4) backup current controlfile in primary db for standby
backup current controlfile for standby format '/tmp/control_GGSRC04T_4sby.bkp';
Output:
RMAN> backup current controlfile for standby format '/tmp/control_GGSRC04T_4sby.bkp'; Starting backup at 05-AUG-24 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=28 device type=DISK channel ORA_DISK_1: starting full datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set including standby control file in backup set channel ORA_DISK_1: starting piece 1 at 05-AUG-24 channel ORA_DISK_1: finished piece 1 at 05-AUG-24 piece handle=/tmp/control_GGSRC04T_4sby.bkp tag=TAG20240805T081520 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01 Finished backup at 05-AUG-24 Starting Control File and SPFILE Autobackup at 05-AUG-24 piece handle=/oraarch/GGSRC04T/autobackup/2024_08_05/o1_mf_s_1176192922_mc0z0bpq_.bkp comment=NONE Finished Control File and SPFILE Autobackup at 05-AUG-24 RMAN> exit |
Step 5) Copy the backup controlfile to standby server
>> here same server.
Step 6) shutdown the standby db
shu immediate;
step 7) preserve the controlfile,redo, sbyredo on disk to another location. Remove the old files.
mkdir -p ~/dba/sbyctrlcrecreate/SBYInfo
cp <> ~/dba/sbyctrlcrecreate/
rm <>
ls -altr /oradata/GGSRC04TSB1/control01_GGSRC04TSB1.ctl
/oradata/GGSRC04TSB1/control02_GGSRC04TSB1.ctl
Output:
[oracle@vcentos79-oracle-ggsrc sbyctrlcrecreate]$ mkdir -p ~/dba/sbyctrlcrecreate/SBYInfo [oracle@vcentos79-oracle-ggsrc sbyctrlcrecreate]$ cd ~/dba/sbyctrlcrecreate/SBYInfo [oracle@vcentos79-oracle-ggsrc SBYInfo]$ pwd /home/oracle/dba/sbyctrlcrecreate/SBYInfo [oracle@vcentos79-oracle-ggsrc SBYInfo]$ cp /oradata/GGSRC04TSB1/redo* . [oracle@vcentos79-oracle-ggsrc SBYInfo]$ cp /oradata/GGSRC04TSB1/sbyredo* . [oracle@vcentos79-oracle-ggsrc SBYInfo]$ ls -altr /oradata/GGSRC04TSB1/control01_GGSRC04TSB1.ctl -rw-r-----. 1 oracle oinstall 10633216 Aug 5 08:18 /oradata/GGSRC04TSB1/control01_GGSRC04TSB1.ctl [oracle@vcentos79-oracle-ggsrc SBYInfo]$ cp /oradata/GGSRC04TSB1/control01_GGSRC04TSB1.ctl . [oracle@vcentos79-oracle-ggsrc SBYInfo]$ ls -altr total 1444012 drwxr-xr-x. 3 oracle oinstall 176 Aug 5 08:20 .. -rw-r-----. 1 oracle oinstall 209715712 Aug 5 08:20 redo01.log -rw-r-----. 1 oracle oinstall 209715712 Aug 5 08:20 redo02.log -rw-r-----. 1 oracle oinstall 209715712 Aug 5 08:20 redo03.log -rw-r-----. 1 oracle oinstall 209715712 Aug 5 08:20 sbyredo04.log -rw-r-----. 1 oracle oinstall 209715712 Aug 5 08:20 sbyredo05.log -rw-r-----. 1 oracle oinstall 209715712 Aug 5 08:20 sbyredo06.log -rw-r-----. 1 oracle oinstall 209715712 Aug 5 08:20 sbyredo07.log drwxr-xr-x. 2 oracle oinstall 177 Aug 5 08:21 . -rw-r-----. 1 oracle oinstall 10633216 Aug 5 08:21 control01_GGSRC04TSB1.ctl [oracle@vcentos79-oracle-ggsrc SBYInfo]$ ls -altr /oradata/GGSRC04TSB1/redo* -rw-r-----. 1 oracle oinstall 209715712 Sep 4 2023 /oradata/GGSRC04TSB1/redo02.log -rw-r-----. 1 oracle oinstall 209715712 Sep 4 2023 /oradata/GGSRC04TSB1/redo03.log -rw-r-----. 1 oracle oinstall 209715712 Sep 4 2023 /oradata/GGSRC04TSB1/redo01.log [oracle@vcentos79-oracle-ggsrc SBYInfo]$ ls -altr /oradata/GGSRC04TSB1/sbyredo* -rw-r-----. 1 oracle oinstall 209715712 Sep 3 2023 /oradata/GGSRC04TSB1/sbyredo06.log -rw-r-----. 1 oracle oinstall 209715712 Sep 3 2023 /oradata/GGSRC04TSB1/sbyredo07.log -rw-r-----. 1 oracle oinstall 209715712 Aug 5 07:49 /oradata/GGSRC04TSB1/sbyredo04.log -rw-r-----. 1 oracle oinstall 209715712 Aug 5 08:18 /oradata/GGSRC04TSB1/sbyredo05.log [oracle@vcentos79-oracle-ggsrc SBYInfo]$ rm /oradata/GGSRC04TSB1/redo* [oracle@vcentos79-oracle-ggsrc SBYInfo]$ rm /oradata/GGSRC04TSB1/sbyredo* [oracle@vcentos79-oracle-ggsrc SBYInfo]$ rm /oradata/GGSRC04TSB1/control01_GGSRC04TSB1.ctl /oradata/GGSRC04TSB1/control02_GGSRC04TSB1.ctl [oracle@vcentos79-oracle-ggsrc SBYInfo]$ ls -altr total 1444012 drwxr-xr-x. 3 oracle oinstall 176 Aug 5 08:20 .. -rw-r-----. 1 oracle oinstall 209715712 Aug 5 08:20 redo01.log -rw-r-----. 1 oracle oinstall 209715712 Aug 5 08:20 redo02.log -rw-r-----. 1 oracle oinstall 209715712 Aug 5 08:20 redo03.log -rw-r-----. 1 oracle oinstall 209715712 Aug 5 08:20 sbyredo04.log -rw-r-----. 1 oracle oinstall 209715712 Aug 5 08:20 sbyredo05.log -rw-r-----. 1 oracle oinstall 209715712 Aug 5 08:20 sbyredo06.log -rw-r-----. 1 oracle oinstall 209715712 Aug 5 08:20 sbyredo07.log drwxr-xr-x. 2 oracle oinstall 177 Aug 5 08:21 . -rw-r-----. 1 oracle oinstall 10633216 Aug 5 08:21 control01_GGSRC04TSB1.ctl [oracle@vcentos79-oracle-ggsrc SBYInfo]$ ls -altr /oradata/GGSRC04TSB1/redo* ls: cannot access /oradata/GGSRC04TSB1/redo*: No such file or directory [oracle@vcentos79-oracle-ggsrc SBYInfo]$ ls -altr /oradata/GGSRC04TSB1/sbyredo* ls: cannot access /oradata/GGSRC04TSB1/sbyredo*: No such file or directory [oracle@vcentos79-oracle-ggsrc SBYInfo]$ ls -altr /oradata/GGSRC04TSB1/control01_GGSRC04TSB1.ctl /oradata/GGSRC04TSB1/control02_GGSRC04TSB1.ctl ls: cannot access /oradata/GGSRC04TSB1/control01_GGSRC04TSB1.ctl: No such file or directory ls: cannot access /oradata/GGSRC04TSB1/control02_GGSRC04TSB1.ctl: No such file or directory [oracle@vcentos79-oracle-ggsrc SBYInfo]$ |
Step 8) startup nomount the standby instance
startup nomount;
sho parameter pfile;
Output:
[oracle@vcentos79-oracle-ggsrc SBYInfo]$ sqlplus '/as sysdba' SQL*Plus: Release 12.2.0.1.0 Production on Mon Aug 5 10:37:20 2024 Copyright (c) 1982, 2016, Oracle. All rights reserved. Connected to an idle instance. SQL> startup nomount; ORACLE instance started. Total System Global Area 3221225472 bytes Fixed Size 8625856 bytes Variable Size 1442840896 bytes Database Buffers 1761607680 bytes Redo Buffers 8151040 bytes SQL> sho parameter pfile; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ spfile string /u01/app/oracle/product/12.2.0 .1/db_1/dbs/spfileGGSRC04TSB1. ora SQL> exit Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production |
Step 9) restore the standby controlfile
piece name: /tmp/control_GGSRC04T_4sby.bkp
restore standby controlfile from '/tmp/control_GGSRC04T_4sby.bkp';
Output:
[oracle@vcentos79-oracle-ggsrc SBYInfo]$ ls -altr /tmp/control_GGSRC04T_4sby.bkp -rw-r-----. 1 oracle oinstall 10764288 Aug 5 08:15 /tmp/control_GGSRC04T_4sby.bkp [oracle@vcentos79-oracle-ggsrc SBYInfo]$ rman target / Recovery Manager: Release 12.2.0.1.0 - Production on Mon Aug 5 10:40:40 2024 Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved. connected to target database: GGSRC04T (not mounted) RMAN> restore standby controlfile from '/tmp/control_GGSRC04T_4sby.bkp'; Starting restore at 05-AUG-24 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=35 device type=DISK channel ORA_DISK_1: restoring control file channel ORA_DISK_1: restore complete, elapsed time: 00:00:01 output file name=/oradata/GGSRC04TSB1/control01_GGSRC04TSB1.ctl output file name=/oradata/GGSRC04TSB1/control02_GGSRC04TSB1.ctl Finished restore at 05-AUG-24 RMAN> |
Step 10) Mount the standby database
alter database mount;
Output:
[oracle@vcentos79-oracle-ggsrc SBYInfo]$ sqlplus '/as sysdba' SQL*Plus: Release 12.2.0.1.0 Production on Mon Aug 5 10:42:42 2024 Copyright (c) 1982, 2016, Oracle. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production SQL> select instance_name,status from v$instance; INSTANCE_NAME STATUS ---------------- ------------ GGSRC04TSB1 STARTED SQL> alter database mount; Database altered. SQL> select instance_name,status from v$instance; INSTANCE_NAME STATUS ---------------- ------------ GGSRC04TSB1 MOUNTED SQL> select name,open_mode from v$database; NAME OPEN_MODE --------- -------------------- GGSRC04T MOUNTED SQL> select databasE_role from v$database; DATABASE_ROLE ---------------- PHYSICAL STANDBY SQL> |
Step 11) Catalog all the datafiles (OMF)
catalog start with '<>/datafile/';
actual:
catalog start with '/oradata/GGSRC04TSB1/';
Output:
[oracle@vcentos79-oracle-ggsrc SBYInfo]$ rman target / Recovery Manager: Release 12.2.0.1.0 - Production on Mon Aug 5 10:43:46 2024 Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved. connected to target database: GGSRC04T (DBID=4198404018, not open) RMAN> catalog start with '/oradata/GGSRC04TSB1/'; Starting implicit crosscheck backup at 05-AUG-24 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=42 device type=DISK Crosschecked 15 objects Finished implicit crosscheck backup at 05-AUG-24 Starting implicit crosscheck copy at 05-AUG-24 using channel ORA_DISK_1 Finished implicit crosscheck copy at 05-AUG-24 searching for all files in the recovery area cataloging files... cataloging done List of Cataloged Files ======================= File Name: /oraarch/GGSRC04TSB1/archivelog/2023_09_04/o1_mf_1_24_lhckf89o_.arc File Name: /oraarch/GGSRC04TSB1/archivelog/2023_09_04/o1_mf_1_23_lhckf8m5_.arc File Name: /oraarch/GGSRC04TSB1/archivelog/2023_09_04/o1_mf_1_25_lhcwn1fl_.arc File Name: /oraarch/GGSRC04TSB1/archivelog/2023_09_04/o1_mf_1_26_lhcwoc0v_.arc File Name: /oraarch/GGSRC04TSB1/archivelog/2023_09_04/o1_mf_1_27_lhcwqw2p_.arc File Name: /oraarch/GGSRC04TSB1/archivelog/2023_09_04/o1_mf_1_28_lhcwvzqt_.arc File Name: /oraarch/GGSRC04TSB1/archivelog/2023_09_25/o1_mf_1_31_lk3btwlf_.arc File Name: /oraarch/GGSRC04TSB1/archivelog/2023_09_25/o1_mf_1_30_lk3btwr2_.arc File Name: /oraarch/GGSRC04TSB1/archivelog/2023_09_25/o1_mf_1_29_lk3btx7x_.arc File Name: /oraarch/GGSRC04TSB1/archivelog/2023_09_25/o1_mf_1_32_lk3c2trn_.arc File Name: /oraarch/GGSRC04TSB1/archivelog/2023_09_25/o1_mf_1_33_lk3c2vod_.arc File Name: /oraarch/GGSRC04TSB1/archivelog/2023_09_25/o1_mf_1_34_lk3c2y7c_.arc File Name: /oraarch/GGSRC04TSB1/archivelog/2024_02_07/o1_mf_1_36_lw6xxprk_.arc File Name: /oraarch/GGSRC04TSB1/archivelog/2024_02_07/o1_mf_1_37_lw6xxpt3_.arc File Name: /oraarch/GGSRC04TSB1/archivelog/2024_02_07/o1_mf_1_38_lw6xxv7h_.arc File Name: /oraarch/GGSRC04TSB1/archivelog/2024_02_07/o1_mf_1_39_lw6xxvg5_.arc File Name: /oraarch/GGSRC04TSB1/archivelog/2024_02_07/o1_mf_1_35_lw6xxvj1_.arc File Name: /oraarch/GGSRC04TSB1/archivelog/2024_02_07/o1_mf_1_40_lw6xxvk2_.arc File Name: /oraarch/GGSRC04TSB1/archivelog/2024_02_07/o1_mf_1_41_lw6xxwp3_.arc File Name: /oraarch/GGSRC04TSB1/archivelog/2024_02_07/o1_mf_1_42_lw6xxwz0_.arc File Name: /oraarch/GGSRC04TSB1/archivelog/2024_02_07/o1_mf_1_43_lw6xxx44_.arc File Name: /oraarch/GGSRC04TSB1/archivelog/2024_02_07/o1_mf_1_44_lw6xxyqf_.arc File Name: /oraarch/GGSRC04TSB1/archivelog/2024_02_07/o1_mf_1_45_lw6xxyv4_.arc File Name: /oraarch/GGSRC04TSB1/archivelog/2024_02_07/o1_mf_1_46_lw6xxz0q_.arc File Name: /oraarch/GGSRC04TSB1/archivelog/2024_02_07/o1_mf_1_47_lw6xxzf2_.arc File Name: /oraarch/GGSRC04TSB1/archivelog/2024_02_07/o1_mf_1_48_lw6xxzg9_.arc File Name: /oraarch/GGSRC04TSB1/archivelog/2024_02_07/o1_mf_1_49_lw6xxzk7_.arc File Name: /oraarch/GGSRC04TSB1/archivelog/2024_02_07/o1_mf_1_50_lw6xxzyh_.arc File Name: /oraarch/GGSRC04TSB1/archivelog/2024_02_07/o1_mf_1_51_lw6xy04j_.arc File Name: /oraarch/GGSRC04TSB1/archivelog/2024_05_26/o1_mf_1_52_m560t9qq_.arc File Name: /oraarch/GGSRC04TSB1/archivelog/2024_05_26/o1_mf_1_53_m563fdw8_.arc File Name: /oraarch/GGSRC04TSB1/archivelog/2024_05_26/o1_mf_1_54_m567jh9r_.arc File Name: /oraarch/GGSRC04TSB1/archivelog/2024_08_05/o1_mf_1_57_mc0xhv30_.arc File Name: /oraarch/GGSRC04TSB1/archivelog/2024_08_05/o1_mf_1_56_mc0xhv57_.arc File Name: /oraarch/GGSRC04TSB1/archivelog/2024_08_05/o1_mf_1_55_mc0xhvms_.arc File Name: /oraarch/GGSRC04TSB1/archivelog/2024_08_05/o1_mf_1_58_mc0xhvqc_.arc File Name: /oraarch/GGSRC04TSB1/autobackup/2023_09_04/o1_mf_s_1146654891_lhccjw20_.bkp File Name: /oraarch/GGSRC04TSB1/autobackup/2023_09_04/o1_mf_s_1146655442_lhcff4yp_.bkp File Name: /oraarch/GGSRC04TSB1/SnapshotBackup/rman_bkp_GGSRC04T_3i25h5j2_1_1 File Name: /oraarch/GGSRC04TSB1/SnapshotBackup/rman_bkp_GGSRC04T_3j25h5j3_1_1 File Name: /oraarch/GGSRC04TSB1/SnapshotBackup/rman_bkp_GGSRC04T_3k25h60r_1_1 File Name: /oraarch/GGSRC04TSB1/SnapshotBackup/rman_bkp_GGSRC04T_3l25h60r_1_1 File Name: /oraarch/GGSRC04TSB1/SnapshotBackup/rman_bkp_GGSRC04T_3m25h60r_1_1 File Name: /oraarch/GGSRC04TSB1/SnapshotBackup/o1_mf_s_1146655442_lhcff4yp_.bkp searching for all files that match the pattern /oradata/GGSRC04TSB1/ List of Files Unknown to the Database ===================================== File Name: /oradata/GGSRC04TSB1/control01.ctl File Name: /oradata/GGSRC04TSB1/control02.ctl File Name: /oradata/GGSRC04TSB1/datafile/o1_mf_gg_data2_lw6xzbqr_.dbf File Name: /oradata/GGSRC04TSB1/datafile/o1_mf_tbspc_ab_m564t4q5_.dbf File Name: /oradata/GGSRC04TSB1/datafile/o1_mf_tbspc_ab_m564tcq0_.dbf Do you really want to catalog the above files (enter YES or NO)? YES cataloging files... cataloging done List of Cataloged Files ======================= File Name: /oradata/GGSRC04TSB1/control01.ctl File Name: /oradata/GGSRC04TSB1/control02.ctl File Name: /oradata/GGSRC04TSB1/datafile/o1_mf_gg_data2_lw6xzbqr_.dbf File Name: /oradata/GGSRC04TSB1/datafile/o1_mf_tbspc_ab_m564t4q5_.dbf File Name: /oradata/GGSRC04TSB1/datafile/o1_mf_tbspc_ab_m564tcq0_.dbf RMAN> exit Recovery Manager complete. [oracle@vcentos79-oracle-ggsrc SBYInfo]$ |
Step 12) Validate the count of datafile vs datafile_copy which we just catalogued
select count(1) from v$datafile;
select count(1) from v$datafile_copy where status='A';
-- if non OMF and OMF togather... we will probably need to peform manual datafile rename.
col stmt for a200;
select a.file#,a.name,b.name,'alter database rename file '||a.file#||' to '||''''||b.name||''''||';' stmt
from v$datafile a
,v$datafile_copy b
,v$datafile_header c
where b.status='A'
and c.error='FILE NOT FOUND'
and a.file#=b.file#
and a.file#=c.file#
;
Output:
col stmt for a200; select a.file#,a.name,b.name,'alter database rename file '||a.file#||' to '||''''||b.name||''''||';' stmt from v$datafile a ,v$datafile_copy b ,v$datafile_header c where b.status='A' and c.error='FILE NOT FOUND' and a.file#=b.file# and a.file#=c.file# ; FILE#,NAME ,NAME ,STMT ----------,----------------------------------------------------------------------------------------------------,----------------------------------------------------------------------------------------------------,-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 7,/oradata/GGSRC04TSB1/gg_data2_01.dbf ,/oradata/GGSRC04TSB1/datafile/o1_mf_gg_data2_lw6xzbqr_.dbf ,alter database rename file 7 to '/oradata/GGSRC04TSB1/datafile/o1_mf_gg_data2_lw6xzbqr_.dbf'; 8,/oradata/GGSRC04TSB1/datafile/o1_mf_tbspc_ab_m564t46q_.dbf ,/oradata/GGSRC04TSB1/datafile/o1_mf_tbspc_ab_m564t4q5_.dbf ,alter database rename file 8 to '/oradata/GGSRC04TSB1/datafile/o1_mf_tbspc_ab_m564t4q5_.dbf'; 9,/oradata/GGSRC04TSB1/datafile/o1_mf_tbspc_ab_m564tbpb_.dbf ,/oradata/GGSRC04TSB1/datafile/o1_mf_tbspc_ab_m564tcq0_.dbf ,alter database rename file 9 to '/oradata/GGSRC04TSB1/datafile/o1_mf_tbspc_ab_m564tcq0_.dbf'; Elapsed: 00:00:00.02 select a.file#,a.name,b.name,'alter database rename file '||''''||a.name||''''||' to '||''''||b.name||''''||';' stmt from v$datafile a ,v$datafile_copy b ,v$datafile_header c where b.status='A' and c.error='FILE NOT FOUND' and a.file#=b.file# and a.file#=c.file# ; == the standby file management needs to be changed to manual. 11:31:55 SQL> alter system set standby_file_management=MANUAL; System altered. Elapsed: 00:00:00.02 11:32:02 SQL> alter database rename file '/oradata/GGSRC04TSB1/gg_data2_01.dbf' to '/oradata/GGSRC04TSB1/datafile/o1_mf_gg_data2_lw6xzbqr_.dbf'; Database altered. Elapsed: 00:00:00.06 11:32:11 SQL> alter database rename file '/oradata/GGSRC04TSB1/datafile/o1_mf_tbspc_ab_m564t46q_.dbf' to '/oradata/GGSRC04TSB1/datafile/o1_mf_tbspc_ab_m564t4q5_.dbf'; Database altered. Elapsed: 00:00:00.04 11:32:16 SQL> alter database rename file '/oradata/GGSRC04TSB1/datafile/o1_mf_tbspc_ab_m564tbpb_.dbf' to '/oradata/GGSRC04TSB1/datafile/o1_mf_tbspc_ab_m564tcq0_.dbf'; Database altered. Elapsed: 00:00:00.03 11:32:21 SQL> |
Step 13) If the count matches then commit the changes
switch database to copy;
-- skipping because there is a mixed df layout.
Step 14) If flashback was enabled before, reenable in standby
-- db never had flashback - so skip
alter database flashback off;
alter database flashback on;
Step 15) Verify and clear the redo and standby redo, such that they recreated fine
col member for a100
select * from V$logfile order by 1;
select * from V$log order by sequence#;
select * from V$standby_log order by sequence#;
alter database clear logfile group <group n>;
-- not needed below. Looks like all the files are created by the controfile was restored; since we had Filename conversion mentioned in pfile.
alter database add standby logfile group <group n> size m;
Output:
11:34:04 SQL> select * from V$logfile order by 1; GROUP#,STATUS ,TYPE ,MEMBER ,IS_, CON_ID ----------,-------,-------,----------------------------------------------------------------------------------------------------,---,---------- 1, ,ONLINE ,/oradata/GGSRC04TSB1/redo01.log ,NO , 0 2, ,ONLINE ,/oradata/GGSRC04TSB1/redo02.log ,NO , 0 3, ,ONLINE ,/oradata/GGSRC04TSB1/redo03.log ,NO , 0 4, ,STANDBY,/oradata/GGSRC04TSB1/sbyredo04.log ,NO , 0 5, ,STANDBY,/oradata/GGSRC04TSB1/sbyredo05.log ,NO , 0 6, ,STANDBY,/oradata/GGSRC04TSB1/sbyredo06.log ,NO , 0 7, ,STANDBY,/oradata/GGSRC04TSB1/sbyredo07.log ,NO , 0 7 rows selected. Elapsed: 00:00:00.01 11:34:08 SQL> select * from V$log order by sequence#; GROUP#, THREAD#, SEQUENCE#, BYTES, BLOCKSIZE, MEMBERS,ARC,STATUS ,FIRST_CHANGE#,FIRST_TIME ,NEXT_CHANGE#,NEXT_TIME , CON_ID ----------,----------,----------,----------,----------,----------,---,----------------,-------------,--------------------,------------,--------------------,---------- 3, 1, 57, 209715200, 512, 1,YES,INACTIVE , 1392965,04/JUN/2024 11:00:48, 1570215,24/JUN/2024 14:04:40, 0 1, 1, 58, 209715200, 512, 1,YES,INACTIVE , 1570215,24/JUN/2024 14:04:40, 1603798,05/AUG/2024 06:23:23, 0 2, 1, 59, 209715200, 512, 1,NO ,CURRENT , 1603798,05/AUG/2024 06:23:23, 1.8447E+19, , 0 Elapsed: 00:00:00.01 11:34:18 SQL> select * from V$standby_log order by sequence#; GROUP#,DBID , THREAD#, SEQUENCE#, BYTES, BLOCKSIZE, USED,ARC,STATUS ,FIRST_CHANGE#,FIRST_TIME ,NEXT_CHANGE#,NEXT_TIME ,LAST_CHANGE#,LAST_TIME , CON_ID ----------,----------------------------------------,----------,----------,----------,----------,----------,---,----------,-------------,--------------------,------------,--------------------,------------,--------------------,---------- 4,UNASSIGNED , 1, 0, 209715200, 512, 0,NO ,UNASSIGNED, , , , , , , 0 7,UNASSIGNED , 0, 0, 209715200, 512, 0,NO ,UNASSIGNED, , , , , , , 0 6,UNASSIGNED , 0, 0, 209715200, 512, 0,NO ,UNASSIGNED, , , , , , , 0 5,4198404018 , 1, 60, 209715200, 512, 5779968,YES,ACTIVE ,1644278,05/AUG/2024 10:43:11, , , 1651878,05/AUG/2024 11:34:25, 0 Elapsed: 00:00:00.01 11:34:26 SQL> alter database clear logfile group 1; Database altered. Elapsed: 00:00:00.89 11:37:28 SQL> !oerr ora 1110 01110, 00000, "data file %s: '%s'" // *Cause: Reporting file name for details of another error. The reported // name can be of the old file if a data file move operation is // in progress. // *Action: See associated error message. 11:38:14 SQL> alter database clear logfile group 2; Database altered. Elapsed: 00:00:00.83 11:38:32 SQL> alter database clear logfile group 3; Database altered. Elapsed: 00:00:00.92 11:38:40 SQL> !ls -altr /oradata/GGSRC04TSB1/redo01.log /oradata/GGSRC04TSB1/redo02.log /oradata/GGSRC04TSB1/redo03.log -rw-r-----. 1 oracle oinstall 209715712 Aug 5 11:37 /oradata/GGSRC04TSB1/redo01.log -rw-r-----. 1 oracle oinstall 209715712 Aug 5 11:38 /oradata/GGSRC04TSB1/redo02.log -rw-r-----. 1 oracle oinstall 209715712 Aug 5 11:38 /oradata/GGSRC04TSB1/redo03.log 11:38:54 SQL> !ls -altr /oradata/GGSRC04TSB1/sbyredo0* -rw-r-----. 1 oracle oinstall 209715712 Aug 5 10:43 /oradata/GGSRC04TSB1/sbyredo07.log -rw-r-----. 1 oracle oinstall 209715712 Aug 5 10:43 /oradata/GGSRC04TSB1/sbyredo04.log -rw-r-----. 1 oracle oinstall 209715712 Aug 5 10:43 /oradata/GGSRC04TSB1/sbyredo06.log -rw-r-----. 1 oracle oinstall 209715712 Aug 5 11:39 /oradata/GGSRC04TSB1/sbyredo05.log 11:39:03 SQL> alter database clear logfile group 4; Database altered. Elapsed: 00:00:01.11 11:39:56 SQL> alter database clear logfile group 5; Database altered. Elapsed: 00:00:00.84 11:39:59 SQL> alter database clear logfile group 6; Database altered. Elapsed: 00:00:00.82 11:40:02 SQL> alter database clear logfile group 7; Database altered. Elapsed: 00:00:00.83 11:40:05 SQL> !ls -altr /oradata/GGSRC04TSB1/sbyredo0* -rw-r-----. 1 oracle oinstall 209715712 Aug 5 11:39 /oradata/GGSRC04TSB1/sbyredo04.log -rw-r-----. 1 oracle oinstall 209715712 Aug 5 11:39 /oradata/GGSRC04TSB1/sbyredo05.log -rw-r-----. 1 oracle oinstall 209715712 Aug 5 11:40 /oradata/GGSRC04TSB1/sbyredo06.log -rw-r-----. 1 oracle oinstall 209715712 Aug 5 11:40 /oradata/GGSRC04TSB1/sbyredo07.log |
Step 16) shutdown and start the standby db in mount state;
shu immediate;
startup mount;
Output:
11:40:09 SQL> shu immediate; ORA-01109: database not open Database dismounted. ORACLE instance shut down. 11:40:44 SQL> startup mount; ORACLE instance started. Total System Global Area,3221225472,bytes Fixed Size , 8625856,bytes Variable Size ,1442840896,bytes Database Buffers ,1761607680,bytes Redo Buffers , 8151040,bytes Database mounted. 11:41:49 SQL> |
Step 17) Validate the health of the standby db and then initiate the recovery
alter database recover automatic managed standby database disconnect; -- this is conventional archivelog based reco.
alter database recover automatic managed standby database disconnect using current logfile; -- using sby redo
Output:
Error: 2024-08-05 12:21:05.665000 +01:00 alter database recover automatic managed standby database disconnect Attempt to start background Managed Standby Recovery process (GGSRC04TSB1) Starting background process MRP0 MRP0 started with pid=25, OS id=2823 MRP0: Background Managed Standby Recovery process started (GGSRC04TSB1) 2024-08-05 12:21:10.694000 +01:00 Serial Media Recovery started Managed Standby Recovery starting Real Time Apply Warning: Recovery target destination is in a sibling branch of the controlfile checkpoint. Recovery will only recover changes to datafiles. Datafile 1 (ckpscn 1622483) is orphaned on incarnation#=1 MRP0: Detected orphaned datafiles! Recovery will possibly be retried after flashback... Errors in file /u01/app/oracle/diag/rdbms/ggsrc04tsb1/GGSRC04TSB1/trace/GGSRC04TSB1_mrp0_2823.trc: ORA-19909: datafile 1 belongs to an orphan incarnation ORA-01110: data file 1: '/oradata/GGSRC04TSB1/system01.dbf' Managed Standby Recovery not using Real Time Apply |
Reading: MOS NOtE 1509932.1
We had 2 incarnation in standby site, 1 coming from Sep2023 vs primary has only 1 incarnation coming from Aug23.
I remember performing snapshot standby last year for one exercise and the backups associated with the files were still kept in disk, this ideally should have reverted the incarnation to a wrong cycle.
Post I reset the incarnation, the db is back to apply changes using MRP.
PRMRY: List of Database Incarnations DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time ------- ------- -------- ---------------- --- ---------- ---------- 1 1 GGSRC04T 4198404018 CURRENT 1 24-AUG-23 SBY: RMAN> list incarnation of database; using target database control file instead of recovery catalog List of Database Incarnations DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time ------- ------- -------- ---------------- --- ---------- ---------- 1 1 GGSRC04T 4198404018 PARENT 1 24-AUG-23 2 2 GGSRC04T 4198404018 CURRENT 600558 04-SEP-23 RMAN> exit Fix: Deleted all the archives which were implictly catalogued and deleted all the files in reco space (including archives, ideally we should skip new archives) Perform reset of incarnation: RMAN> reset database to incarnation 1; using target database control file instead of recovery catalog database reset to incarnation 1 RMAN> list incarnation of database; List of Database Incarnations DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time ------- ------- -------- ---------------- --- ---------- ---------- 1 1 GGSRC04T 4198404018 CURRENT 1 24-AUG-23 2 2 GGSRC04T 4198404018 ORPHAN 600558 04-SEP-23 RMAN> exit Recovery Manager complete. [oracle@vcentos79-oracle-ggsrc SBYInfo]$ |
No comments:
Post a Comment