【分享】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;"