oracle查看主备库延迟时间,【分享】Oracle 主备库容灾维护常用的九个SQL

1 实例状态

-- Author : haizdl

-- Description : Display instance information for primary and standby database.

-- Last Modified: 15/07/2017

SET LINESIZE 1000 PAGESIZE 1000

COLUMN INSTANCE_NAME FORMAT A20

COLUMN VERSION FORMAT A20

COLUMN STATUS FORMAT A20

COLUMN DATABASE_STATUS FORMAT A20

select instance_name,

version ,

status ,

database_status

from v$instance;"

2 主备库状态

-- Author : haizdl

-- Description : Displays information database status for primary and standby database.

-- Last Modified: 15/07/2017

SET LINESIZE 1000 PAGESIZE 1000

COLUMN NAME FORMAT A10

COLUMN OPEN_MODE FORMAT A20

select name,

log_mode,

open_mode

from v$database;"

3 控制文件状态

-- Author : haizdl

-- Description : Displays information about controll files.

-- Last Modified: 15/07/2017

SET LINESIZE 1000 PAGESIZE 1000

COLUMN STATUS FORMAT A10

COLUMN NAME FORMAT A55

select status,

name

from v$controlfile;"

4 日志文件状态

-- Author : haizdl

-- Description : Displays information about logfiles.

-- Last Modified: 15/07/2017

SET LINESIZE 1000 PAGESIZE 1000

COLUMN MEMBER FORMAT A45

COLUMN STATUS FORMAT A10

COLUMN TYPE FORMAT A10

select group#,

status,

type,

member

from v$logfile;"

5 归档目的地状态

-- Author : haizdl

-- Description : Displays information about archive_dest1 & 2.

-- Last Modified: 15/07/2017

SET LINESIZE 1000 PAGESIZE 1000

COLUMN DEST_NAME FORMAT A20

COLUMN STATUS FORMAT A10

COLUMN DATABASE_MODE FORMAT A20

COLUMN DESTINATION FORMAT A20

select dest_name,status,

database_mode,

destination

from v$archive_dest_status

where dest_id in ('1','2');"

6 当前会话数和历史最高

-- Author : haizdl

-- Description : Displays information about session statistics.

-- Last Modified: 21/12/2004

select sessions_current,

sessions_highwater

from v$license;"

7 主库同步情况(日志号&SCN)

-- Author : haizdl

-- Description : Displays data sync for primary db and standby database.

-- Last Modified: 04/10/2017

SET LINESIZE 1000 PAGESIZE 1000

SELECT m.thread#,

m.sequence#,

first_change#,

next_change#

FROM v$log_history m,

(SELECT thread#, max(sequence#) as sequence#

FROM v$log_history

GROUP BY thread#) t

WHERE m.thread# = t.thread#

AND m.sequence# = t.sequence#;"

8 备库查询没有应用的日志

-- Author : haizdl

-- Description : Displays ASM Disk Groups.

-- Last Modified: 04/10/2017

SET LINESIZE 1000 PAGESIZE 1000

select sequence#,

applied

from v$archived_log

where applied='NO';"

9 备库最近应用的十个日志

-- Author : haizdl

-- Description : Displays tablespace usage pct.

-- Last Modified: 04/10/2017

SET LINESIZE 1000 PAGESIZE 1000

select *

from (select sequence#,

applied

from v$archived_log

order by sequence# desc)

where rownum<=10;"收起