DBA Scripts

DATA Guard Oueries

-    Start Real Time Apply Recovery
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;

-    Start Apply Recovery
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE NODELAY ;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE NODELAY  DISCONNECT FROM SESSION;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE  DISCONNECT FROM SESSION;


-    Stop Apply Recovery 
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

-    Info of DB
SELECT DATABASE_ROLE, DB_UNIQUE_NAME INSTANCE, OPEN_MODE, PROTECTION_MODE, PROTECTION_LEVEL, SWITCHOVER_STATUS FROM V$DATABASE;

-    Data Guard Status
select NAME Name, VALUE Value, UNIT Unit from v$dataguard_stats
 union
 select null,null,' ' from dual
 union
 select null,null,'Time Computed: '||MIN(TIME_COMPUTED)
 from v$dataguard_stats;


-    Check Archivelog in Stand By 
SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

SELECT THREAD#, MAX(SEQUENCE#) AS "LAST_APPLIED_LOG" FROM V$LOG_HISTORY GROUP BY THREAD#;

-    Last Applied Archive log
select max(sequence#) from v$archived_log where applied='YES';

-    Apply and Checked Archive Logs
SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

SELECT THREAD#, MAX(SEQUENCE#) AS "LAST_APPLIED_LOG" FROM V$LOG_HISTORY GROUP BY;
SELECT thread#, sequence#  FROM v$archived_log  where applied='yes'  and deleted= 'no' ;

-    Info of RFS and MRP process in StandBy
SELECT PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS FROM V$MANAGED_STANDBY;

-    Messages of Data Guard Status
SELECT MESSAGE FROM V$DATAGUARD_STATUS;

-    Determining Which Log Files Were Not Received by the Standby Site.
SELECT LOCAL.THREAD#, LOCAL.SEQUENCE# FROM (SELECT THREAD#, SEQUENCE# FROM V$ARCHIVED_LOG WHERE DEST_ID=1) LOCAL WHERE LOCAL.SEQUENCE# NOT IN (SELECT SEQUENCE# FROM V$ARCHIVED_LOG WHERE DEST_ID=2 AND THREAD# = LOCAL.THREAD#);

- Check v$managed_standby
select process, status, sequence# from v$managed_standby;
OR alternatively:
select name, applied from v$archived_log;

-    Data Guard Performance
COLUMN NAME FORMAT A20
COLUMN VALUE FORMAT A25
COLUMN TIME_COMPUTED FORMAT A24
SELECT * FROM V$DATAGUARD_STATS;

-    Archive Log Register 
alter database register logfile 'path_file';

-       Time of Applied and Received Logs
select 'Last applied : ' Logs, to_char(next_time,'DD-MON-YY:HH24:MI:SS') Time from v$archived_log 
where sequence# = (select max(sequence#) from v$archived_log where applied='YES')
union
select 'Last received : ' Logs, to_char(next_time,'DD-MON-YY:HH24:MI:SS') Time  from v$archived_log
where sequence# = (select max(sequence#) from v$archived_log);

- Check Delay in Stand by
SELECT DELAY_MINS FROM V$MANAGED_STANDBY WHERE PROCESS = 'MRP0';

 -    Apply Speed Of Archive Log 
select APPLY_RATE  from v$STANDBY_APPLY_SNAPSHOT;

Set Linesize 400
Col Values For A65
Col Recover_start For A21
Select To_char(START_TIME,'Dd.Mm.Yyyy Hh24:Mi:ss') "Recover_start",To_char(Item)||' = '||To_char(Sofar)||' '||
To_char(Units)||' '|| To_char(TIMESTAMP,'Dd.Mm.Yyyy Hh24:Mi') "Values" From V$Recovery_progress Where Start_time=(Select Max(Start_time) From V$Recovery_progress);




Hiç yorum yok:

Yorum Gönder