# dataguard monitor
# DR notification
SELECT MAX(SEQUENCE#) FROM V$ARCHIVED_LOG WHERE COMPLETION_TIME >= (SYSDATE - 1) GROUP BY THREAD# ORDER BY THREAD#;
SELECT MAX(SEQUENCE#) FROM V$ARCHIVED_LOG WHERE APPLIED='YES' and COMPLETION_TIME
>= (SYSDATE - 1) and DEST_ID=2 GROUP BY THREAD# ORDER BY THREAD#;
#select database role
SELECT database_role, open_mode FROM v$database;
DATABASE_ROLE OPEN_MODE
---------------- --------------------
PHYSICAL STANDBY MOUNTED
#status of background processes in a standby database (on standby)
select process, client_process,thread#,sequence#,status from v$managed_standby;
#FIND THE ARCHIVE LAG BETWEEN PRIMARY AND STANDBY: monitor dataguard
select LOG_ARCHIVED-LOG_APPLIED "LOG_GAP" from
(SELECT MAX(SEQUENCE#) LOG_ARCHIVED
FROM V$ARCHIVED_LOG WHERE DEST_ID=1 AND ARCHIVED='YES'),
(SELECT MAX(SEQUENCE#) LOG_APPLIED
FROM V$ARCHIVED_LOG WHERE DEST_ID=2 AND APPLIED='YES');
#example (Normal)
LOG_GAP
----------
1
#LAST SEQUENCE RECEIVED AND LAST SEQUENCE APPLIED:
SELECT al.thrd "Thread", almax "Last Seq Received", lhmax "Last Seq Applied" FROM
(select thread# thrd, MAX(sequence#) almax FROM v$archived_log WHERE resetlogs_change#=(SELECT resetlogs_change#
FROM v$database) GROUP BY thread#) al, (SELECT thread# thrd, MAX(sequence#) lhmax FROM v$log_history
WHERE resetlogs_change#=(SELECT resetlogs_change# FROM v$database) GROUP BY thread#) lh WHERE al.thrd = lh.thrd;
#CHECK THE MESSAGES/ERRORS IN STNADBY DATABASE: ( Run on standby)
set pagesize 2000
set lines 2000
col MESSAGE for a90
select message,timestamp from V$DATAGUARD_STATUS where timestamp > sysdate - 1/6;
#check lag ( on standby)
set lines 1000
select name,value from v$dataguard_stats;
#Example
NAME VALUE
-------------------------------- ----------------------------------------------------------------
transport lag +00 00:00:00
apply lag +00 00:00:00
apply finish time +00 00:00:00.000
estimated startup time 8
#which archive sequence number comes from the Primary database lastly and which is last applied in Standby ( on standby)
SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last in Sequence", APPL.SEQUENCE# "Last Applied Sequence", (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;
#Example
Thread Last in Sequence Last Applied Sequence Difference
---------- ---------------- --------------------- ----------
1 19642 19642 0
# archive gap in dataguard
SELECT THREAD#, LOW_SEQUENCE#, HIGH_SEQUENCE# FROM V$ARCHIVE_GAP;
#applied archivelogs in dataguard,
select thread#,sequence#,first_time,next_time,applied from gv$archived_log where applied='YES';
# max sequence of dataguard
select thread#,max(sequence#) from gv$archived_log group by thread#;
#To see all parameters of Oracle dataguard, you can execute following query.
set linesize 500 pages 0
col value for a80
col name for a50
select name, value from v$parameter
where name in ('db_name','db_unique_name','log_archive_config',
'log_archive_dest_1','log_archive_dest_2','log_archive_dest_3',
'log_archive_dest_state_1','log_archive_dest_state_2','log_archive_dest_state_3', 'remote_login_passwordfile',
'log_archive_format','log_archive_max_processes','fal_server','fal_client','db_file_name_convert',
'log_file_name_convert', 'standby_file_management') order by 1;
##################################troubleshooting#############################################################################
#stop dataguard
alter database recover managed standby database cancel;
#start dataguard
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
#CHECK THE NUMBER OF ARCHIVES GETTING GENERATING ON HOURLY BASIS:
SELECT TO_CHAR(TRUNC(FIRST_TIME),'Mon DD') "DG Date",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'00',1,0)),'9999') "12AM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'01',1,0)),'9999') "01AM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'02',1,0)),'9999') "02AM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'03',1,0)),'9999') "03AM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'04',1,0)),'9999') "04AM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'05',1,0)),'9999') "05AM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'06',1,0)),'9999') "06AM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'07',1,0)),'9999') "07AM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'08',1,0)),'9999') "08AM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'09',1,0)),'9999') "09AM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'10',1,0)),'9999') "10AM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'11',1,0)),'9999') "11AM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'12',1,0)),'9999') "12PM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'13',1,0)),'9999') "1PM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'14',1,0)),'9999') "2PM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'15',1,0)),'9999') "3PM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'16',1,0)),'9999') "4PM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'17',1,0)),'9999') "5PM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'18',1,0)),'9999') "6PM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'19',1,0)),'9999') "7PM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'20',1,0)),'9999') "8PM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'21',1,0)),'9999') "9PM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'22',1,0)),'9999') "10PM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'23',1,0)),'9999') "11PM"
FROM V$LOG_HISTORY
GROUP BY TRUNC(FIRST_TIME)
ORDER BY TRUNC(FIRST_TIME) DESC
/
#Register Data Guard standby redo log file tips( this will register all arch logs in this path )
http://www.dba-oracle.com/t_register_standby_redo_log_files.htm
LOCALERP - Primary database
Error: ORA-16724: cannot resolve gap for one or more members
LOCALERPST - Physical standby database
Warning: ORA-16809: multiple warnings detected for the member
alter database register logfile '/oradata/proddata19/LOCALERP/archive/1_40863_1077804461.arc';
alter database register logfile '/oradata/proddata19/LOCALERP/archive/1_40864_1077804461.arc';
alter database register logfile '/oradata/proddata19/LOCALERP/archive/1_40865_1077804461.arc';
alter database register logfile '/oradata/proddata19/LOCALERP/archive/1_40866_1077804461.arc';
alter database register logfile '/oradata/proddata19/LOCALERP/archive/1_62960_1077804461.arc';
1_62960_1077804461.arc
rman> catalog start with '/oradata/proddata19/LOCALERP/archive';
catalog start with '/oradata/proddata19/LOCALERP/archive/';
10 16 * * * /bin/sh /home/oraprod/del_arch.sh > /home/oraprod/1
No comments:
Post a Comment