Prod/Primary:
---------->
alter session set nls_date_format='DD-MON-YYYY HH24:MI:SS';
set lin 500
select t.sequence#,t.applied,t.first_time,t.next_time,t.completion_time,t.DEST_ID from
gv$archived_log t where first_time >sysdate -8 and dest_id=2 order by t.sequence# asc;
set lin 80
select process, client_process, thread#, sequence#, status from v$managed_standby;
alter session set nls_date_format='DD-MON-YYYY HH24:MI:SS';
set lin 500
select t.sequence#,t.applied,t.first_time,t.next_time,t.completion_time,t.DEST_ID from
gv$archived_log t where first_time >sysdate -1/16 and dest_id=2 order by t.sequence# asc;
set lin 80
select process, client_process, thread#, sequence#, status from v$managed_standby;
Archive Sequence Lag
select a.dest_id,a.thread#, applied "Last Applied Seq", b.notappld "Current Seq", b.notappld-applied "Difference" from
(select dest_id,thread#, max(sequence#) applied from gv$archived_log where applied='YES' and RESETLOGS_TIME=(select RESETLOGS_TIME from v$database) group by dest_id,thread#) A, (select thread#,max(sequence#) notappld from gv$archived_log where RESETLOGS_TIME=(select RESETLOGS_TIME from v$database) and dest_id=1 group by thread#) B where a.thread# = b.thread# order by 1,2;
Stdby
------------>>
alter session set nls_date_format='DD-MON-YYYY HH24:MI:SS';
set lin 500
select t.sequence#,t.applied,t.first_time,t.next_time,t.completion_time,t.DEST_ID from
gv$archived_log t where first_time >sysdate -1/18 order by t.sequence# asc;
set lin 80
Standby Database Time Lag
set lin 500
col NAME for a25
col VALUE for a20
col UNIT for a30
col TIME_COMPUTED for a25
select NAME,VALUE,UNIT,TIME_COMPUTED from v$dataguard_stats;
set lin 80
select process, status, sequence# from v$managed_standby;
SELECT MESSAGE FROM V$DATAGUARD_STATUS;
MRP Process Status
select inst_id,process, client_process, thread#, sequence#, status from gv$managed_standby;
select inst_id,process, client_process, thread#, sequence#, status from gv$managed_standby where process like 'MRP%';
>>>>>
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#
ORDER BY 1;
Redo Apply Rate:
-----------------
select to_char(snapshot_time, 'dd-mon-rr hh24:mi:ss') snapshot_time,
thread#, sequence#, applied_scn, apply_rate from v$standby_apply_snapshot;
alter database recover managed standby database cancel;
alter database recover managed standby database disconnect;
alter system set log_archive_dest_2='service=splp-el1-d001:1521/POELO03_OOR.WORLD ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) db_unique_name=POELO03_OOR';
This comment has been removed by a blog administrator.
ReplyDeleteselect
ReplyDeletesql_id,
(case command_type
when 1 then 'CREATE TABLE'
when 2 then 'INSERT'
when 3 then 'SELECT'
when 6 then 'UPDATE'
when 7 then 'DELETE'
when 9 then 'Create Index'
when 11 then 'ALTER INDEX'
when 25 then 'ALTER PROCEDURE'
when 26 then 'LOCK Table'
when 42 then 'ALTER_SESSION'
when 44 then 'COMMIT'
when 45 then 'Rollback'
when 46 then 'Savepoint'
when 47 then 'PL SQL BLOCK'
when 48 then 'Set transaction'
when 50 then 'Explain'
when 62 then 'Analyze table'
when 90 then 'Set Constraints'
when 170 then 'Call'
when 189 then 'Merge'
end ) Command_Type,
(select substr(sql_text,1,200) from dba_hist_sqltext m where m.sql_id=A.sql_id and DBID=(select dbid from v\$database)) sql_text,
(select max(PARSING_SCHEMA_NAME) from dba_hist_sqlstat k where k.sql_id=a.sql_id ) USER_NAME,
ELAP_SEC ,
executions TOTAL_EXECUTIONS,
pio_per_exec,
lio_per_exec
from (
select
q.sql_id,H.command_type
, sum(q.EXECUTIONS_DELTA) executions
, round(sum(DISK_READS_delta)/greatest(sum(executions_delta),1),1) pio_per_exec
, round(sum(BUFFER_GETS_delta)/greatest(sum(executions_delta),1),1) lio_per_exec
, round(sum(ELAPSED_TIME_delta),1) ELAPSED_TIME_delta
, round((sum(ELAPSED_TIME_delta)/greatest(sum(executions_delta),1)/1000000),2) ELAP_SEC
from dba_hist_sqlstat q, dba_hist_snapshot s, dba_hist_sqltext H
where s.snap_id = q.snap_id
and s.begin_interval_time>=sysdate-1
--and q.Module='JDBC Thin Client'
and q.parsing_schema_name not in ( 'SYS')
and s.dbid = q.dbid
and s.instance_number = q.instance_number
and q.dbid=h.dbid
and q.sql_id=H.sql_id
group by q.sql_id,h.command_type
) A where ELAP_SEC >1 and command_type <>47 order by 5 desc);