x`GAP on DR side resolution steps :
check the current sequence on Primary using :
set time on
set lines 200
select INSTANCE_NAME,HOST_NAME,VERSION,STARTUP_TIME,STATUS from v$instance;
select max(sequence#) from v$log_history ;
select name,dbid,open_mode, log_mode,DB_Unique_name,database_role,switchover_status,protection_mode,flashback_on, force_logging from v$database;
and check the same status on DR - (you will get the difference of archives to be applied on DR)
now check if the archive is available on Primary , if not then we need to restore the archive from rman backup using
RMAN> list backup of archivelog logseq=174111;
--- thread value to get from v$managed_standby using
---------------------------------------------------------------------
TO CHECK DR SYNC STATUS :
select max(sequence#) from v$log_history ;
set lines 200
SELECT thread#, process, pid, status, client_process, sequence#, block# FROM gv$managed_standby ORDER BY thread#, process;
col FIRST for a25
col NEXT for a25
col COMPLETION for a25
select thread#, sequence#, applied,to_char(first_time,'mm/dd/yy hh24:mi:ss') first,to_char(next_time, 'mm/dd/yy hh24:mi:ss') next,to_char(completion_time, 'mm/dd/yy hh24:mi:ss') completion
from v$archived_log where thread# = 1 order by first_time;
SELECT a.thread#, b. last_seq, a.applied_seq, a. last_app_timestamp, b.last_seq-a.applied_seq ARC_DIFF FROM (SELECT thread#, MAX(sequence#) applied_seq, MAX(next_time) last_app_timestamp FROM gv$archived_log WHERE applied = 'YES' GROUP BY thread#) a, (SELECT thread#, MAX (sequence#) last_seq FROM gv$archived_log GROUP BY thread#) b WHERE a.thread# = b.thread#;
select max(sequence#) from v$archived_log;
SELECT /*+ rule */ 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# ORDER BY 1;
---------------------------------------------------------------------
to check last applied archive date:
select max(checkpoint_time) from v$datafile_header;
to check which archive applied at what time - date on the server :
select NAME,THREAD#,SEQUENCE#,ARCHIVED,APPLIED,DELETED,STATUS,COMPLETION_TIME from v$archived_log;
alter system checkpoint : before shutdown DR
rman target /
delete archivelog until time 'sysdate -30';
delete force archivelog until time 'SYSDATE-08';
delete force archivelog all completed before 'SYSDATE-30';
recover automatic standby database
delete force archivelog until time 'SYSDATE-15';
!find /archive_logs/flash_recovery_area/KCECM_PRIM/archivelog/ -name '*588*'
!find /archive_logs/flash_recovery_area/KCECM_STBY/archivelog/ -name '*588*'
!find /oraarchlogsgnb/ephsprdg/EPHSTDRG/archivelog/ -name '*20515*'
!find /oraarchlogsgnb/ephsprdg/EPHSTDRG/archivelog/ -name '*2067*'
---------------------------------
Recovery usage :
------------------------------
col name format a32
col size_mb format 999,999,999
col used_mb format 999,999,999
col pct_used format 999
select
name,
ceil( space_limit / 1024 / 1024) size_mb,
ceil( space_used / 1024 / 1024) used_mb,
decode( nvl( space_used, 0),0, 0,
ceil ( ( space_used / space_limit) * 100) ) pct_used
from
v$recovery_file_dest
order by
name desc;
------------------------------
restore archivelog from sequence 174258 until sequence 174259;
restore archivelog sequence 174596;
find /oraarchlogs/EPHSTDR/archivelog/ -name '*177964*'
GNB DR
find /oraarchlogsgnb/ephsprdg/EPHSTDRG/archivelog/ -name '*19341*'
GNB PROD
find /oraarchlogsgnb/ephsprdg/EPHSPRDG/archivelog/ -name '*19341*'
------------------------------
RMAN> restore archivelog sequence 174112 thread 1;
for Multiple Archives apply together :
RMAN> restore archivelog from sequence 174598 until sequence 174599;
delete archivelog from sequence 186324 until sequence 186336;
Starting restore at 26-AUG-22
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=142 device type=DISK
channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=173548
channel ORA_DISK_1: reading from backup piece /orabckups/ephsprd/ARC_INC_EPHSPRD_t1113699614_s54698_p1.arc
channel ORA_DISK_1: piece handle=/orabckups/ephsprd/ARC_INC_EPHSPRD_t1113699614_s54698_p1.arc tag=TAG20220826T010014
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:35
Finished restore at 26-AUG-22
similarly , we need to restore all the files which are not shipped to the target (DR) and restore it on primary and then it will start applying on DR.
ERROR : on GNB :
------------------------------------------------
ORA-19815: WARNING: db_recovery_file_dest_size of 75161927680 bytes is 100.00% used, and has 0 remaining bytes available.
-------------------------------------------------
at oS level when checked the space was visible but in alert log file when checked using the query
col name format a32
col size_mb format 999,999,999
col used_mb format 999,999,999
col pct_used format 999
select
name,
ceil( space_limit / 1024 / 1024) size_mb,
ceil( space_used / 1024 / 1024) used_mb,
decode( nvl( space_used, 0),0, 0,
ceil ( ( space_used / space_limit) * 100) ) pct_used
from
v$recovery_file_dest
order by
name desc;
it showed that the utilization is 100% , and there was some obsolete backup / old backups , removed some old backup using RMAN and that started appying the log
------------------------------------------------------------------------------------------------
----------------------------------------
to stop DR database :
----------------------------------------
select INSTANCE_NAME,HOST_NAME,VERSION,STARTUP_TIME,STATUS from v$instance;
select name,dbid,open_mode, log_mode,DB_Unique_name,database_role,switchover_status,protection_mode,flashback_on, force_logging from v$database;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
shutdown immediate;
----------------------------------------
to start those database back (DR)
----------------------------------------
startup nomount;
alter database mount standby database;
select INSTANCE_NAME,HOST_NAME,VERSION,STARTUP_TIME,STATUS from v$instance;
select name,dbid,open_mode, log_mode,DB_Unique_name,database_role,switchover_status,protection_mode,flashback_on, force_logging from v$database;
select PROCESS, STATUS,SEQUENCE#,BLOCK#,BLOCKS, DELAY_MINS FROM V$MANAGED_STANDBY;
alter database recover managed standby database disconnect from session;
select PROCESS, STATUS,SEQUENCE#,BLOCK#,BLOCKS, DELAY_MINS FROM V$MANAGED_STANDBY;
select max(sequence#) from v$log_history ;
FIREWALL ISSUE on OPB :
[root@mprvld001 ~]# systemctl stop firewalld
[root@mprvld001 ~]# systemctl status firewalld
● firewalld.service - firewalld - dynamic firewall daemon
Loaded: loaded (/usr/lib/systemd/system/firewalld.service; enabled; vendor preset: enabled)
Active: inactive (dead) since Sat 2023-05-13 07:45:23 EDT; 3s ago
Docs: man:firewalld(1)
Process: 1238 ExecStart=/usr/sbin/firewalld --nofork --nopid $FIREWALLD_ARGS (code=exited, status=0/SUCCESS)
Main PID: 1238 (code=exited, status=0/SUCCESS)
AWEZ Given Queries for DR :
---------------------------------
SELECT dest_id,
sequence#,
applied,
deleted,
TO_CHAR (first_time, 'dd/mm/yyyy hh24:mi:ss') first_time
FROM (SELECT dest_id,
first_time,
sequence#,
applied,
deleted
FROM v$archived_log
ORDER BY first_time DESC, dest_id)
WHERE APPLIED='YES' and ROWNUM <= 100;
set linesize 300
set pages 70
col name for a30
col value for a60
col message for a90
col destination for a35
col dest_name for a40
select name,value from gv$parameter where name in ('db_name','db_unique_name','db_domain','db_file_name_convert','log_file_name_convert','fal_server','fal_client','remote_login_passwordfile','standby_file_management','dg_broker_start','dg_broker_config_file1','dg_broker_config_file2');
select status,instance_name,database_role,open_mode,protection_mode,switchover_status from gv$instance,gv$database;
select name,(space_limit/1024/1024/1024) "Limit in GB",(space_used/1024/1024/1024) "Used in GB" from v$recovery_file_dest;
select thread#,max(sequence#) from gv$archived_log group by thread#;
select thread#,max(sequence#) from gv$archived_log where applied='YES' group by thread#;
select dest_id, dest_name, status, target, archiver , destination from GV$ARCHIVE_DEST where destination IS NOT NULL;
select inst_id,process,status,sequence#,thread#,client_process from gv$managed_standby;
select group#,thread#,status,members,(bytes/1024/1024)"Each ORL File Size in MB" from gv$log;
select group#,thread#,status,(bytes/1024/1024)"Each SRL File Size in MB" from gv$standby_log;
May 12 19:33:04 mprvld001.mail.opb.on.ca systemd[1]: Starting firewalld - dynamic firewall daemon...
May 12 19:33:05 mprvld001.mail.opb.on.ca systemd[1]: Started firewalld - dynamic firewall daemon.
May 13 07:45:22 mprvld001.mail.opb.on.ca systemd[1]: Stopping firewalld - dynamic firewall daemon...
May 13 07:45:23 mprvld001.mail.opb.on.ca systemd[1]: Stopped firewalld - dynamic firewall daemon.
[root@mprvld001 ~]#
set scan on
set feed off
set linesize 200
column thread format a6;
column "PR-Archived" format a13;
column "STBY-Archived" format a15;
column "STBY-Applied" format a14;
column "Shipping GAP " format a16;
column "Applied GAP " format a20;
--ACCEPT DEST PROMPT 'Enter the Standby Archive Log Destination : '
SELECT *
FROM (SELECT LPAD (t1, 4, ' ') "Thread",
LPAD (pricre, 9, ' ') "PR-Archived",
LPAD (stdcre, 10, ' ') "STBY-Archived",
LPAD (stdnapp, 9, ' ') "STBY-Applied",
LPAD (pricre - stdcre, 13, ' ')
"Shipping GAP ",
LPAD (stdcre - stdnapp, 15, ' ')
"Applied GAP "
FROM ( SELECT MAX (sequence#) stdcre, thread# t1
FROM v$archived_log
WHERE standby_dest = 'YES'
AND resetlogs_id IN
(SELECT MAX (RESETLOGS_ID)
FROM v$archived_log)
AND thread# IN (1, 2, 3, 4)
GROUP BY thread#) a,
( SELECT MAX (sequence#) stdnapp, thread# t2
FROM v$archived_log
WHERE standby_dest = 'YES'
AND resetlogs_id IN
(SELECT MAX (RESETLOGS_ID)
FROM v$archived_log)
AND thread# IN (1, 2, 3, 4)
AND applied = 'YES'
GROUP BY thread#) b,
( SELECT MAX (sequence#) pricre, thread# t3
FROM v$archived_log
WHERE standby_dest = 'NO'
AND resetlogs_id IN
(SELECT MAX (RESETLOGS_ID)
FROM v$archived_log)
AND thread# IN (1, 2, 3, 4)
GROUP BY thread#) c
WHERE a.t1 = b.t2 AND b.t2 = c.t3 AND c.t3 = a.t1)
ORDER BY 1
/
set feed on
break on off
Reference DOcuments from Oracle :
REFERENCES
NOTE:734862.1 - Step By Step Guide On How To Recreate Standby Control File When Datafiles Are On ASM And Using Oracle Managed Files
NOTE:549078.1 - ORA-600 [25016] Errors Trying To Recover Managed Standby Database
NOTE:753893.1 - Impact of Very Large Controlfiles on Data Guard Environments
NOTE:387339.1 - MAA - Creating a Single Instance Physical Standby for a RAC Primary
NOTE:404724.1 - ORA-03135 When Connecting to the Database
NOTE:739522.1 - ORA - 03135 : Connection Lost Contact While Shipping from Primary Server to Standby server
NOTE:47325.1 - Init.ora Parameter "DB_FILE_NAME_CONVERT" Reference Note
BUG:6029179 - MRP WAITING FOR LOGS THAT ARE ALREADY APPLIED
NOTE:743101.1 - ORA-01110 ORA-1122 ORA-01251 When Restoring With a Backup from a Standby DB
NOTE:836986.1 - Steps to perform for Rolling Forward a Physical Standby Database using RMAN Incremental Backup.
NOTE:1537316.1 - Data Guard Gap Detection and Resolution Possibilities
NOTE:759181.1 - Dataguard Physical Standby occurs ORA-600 [2130] after added new node to primary RAC database.
NOTE:787354.1 - Troubleshooting ORA-3135 Connection Lost Contact
NOTE:290817.1 - Rolling a Standby Forward using an RMAN Incremental Backup in 9i
NOTE:6004936.8 - Bug 6004936 - Standby apply stops after ORA-16146
NOTE:730066.1 - Troubleshooting ORA-3136 Connection Timeouts Errors - Database Diagnostics
NOTE:799353.1 - How to Resolve Error in Remote Archiving
NOTE:304488.1 - Using standby_file_management with Raw Devices
