日常操作
5墨值下载
检查 dg
主库：
select thread#,max(sequence#) from v$log where status='CURRENT' group by
thread#;
备库：
select thread#,max(sequence#) from v$archived_log where applied='YES' group by
thread#;
查询角色切换状态
select
dbid,name,db_unique_name,open_mode,switchover_status,database_role,protection_mo
de from v$database;
select * from v$archive_gap;
备库
set linesize 500 pages 200
col NAME for a25
col VALUE for a18
col UNIT for a30
col TIME_COMPUTED for a20
col DATUM_TIME for a20
select * from v$dataguard_stats;
备库
select status,gap_status from v$archive_dest_status where dest_id=2;
select dest_name,status,error from v$archive_dest;
select INST_ID,process,client_process,sequence#,status,block#,blocks from
gv$managed_standby;
================================================================================
================================================================================
=====
巡检
检查连接数
col max_session for a30
select b.INST_ID, a.VALUE as max_session, count(*) as now_session
from gv$parameter a, gv$session b
where a.INST_ID = b.INST_ID
and a.name = 'processes'
group by b.INST_ID, a.VALUE
order by 1;
检查告警日志
cd $ORACLE_BASE/diag/rdbms/db_name/instance_name/trace
tail -10000 alert*.log|grep -E 'ORA-|TNS-'|more
检查 asm 磁盘组
select name,state,type,COMPATIBILITY,database_compatibility,
total_mb,free_mb,OFFLINE_DISKS,VOTING_FILES from V$asm_diskgroup;
select D.GROUP_NUMBER,G.name AS ASM_GROUP_NAME,d.DISK_NUMBER, D.NAME AS
DISK_NAME,D.path,D.os_mb,d.free_mb,D.FAILGROUP,D.FAILGROUP_TYPE,D.MODE_STATUS,D.
mount_status,d.STATE,VOTING_FILE from V$ASM_DISKGROUP G,v$asm_disk D WHERE
G.GROUP_NUMBER=d.GROUP_NUMBER order by ASM_GROUP_NAME,d.FAILGROUP,d.DISK_NUMBER;
检查表空间使用情况
select
a.TABLESPACE_NAME,a.BLOCK_SIZE,a.MAX_SIZE,a.STATUS,a.CONTENTS,a.LOGGING,a.EXTENT
_MANAGEMENT from dba_tablespaces a;
数据文件状态检查：
select b.FILE_ID,b.FILE_NAME,b.TABLESPACE_NAME,b.BYTES,b.STATUS,b.AUTOEXTENSIBLE
from Dba_Data_Files b;
表空间使用率检查：
select tablespace_name,
ts_max_mb,
ts_used_mb,
ts_max_mb - ts_used_mb ts_free_mb,
round(100 * ts_used_mb /ts_max_mb, 2) ts_per
from (select a.tablespace_name tablespace_name,
round((a.bytes_alloc - nvl(b.bytes_free, 0)) / power(2, 20),
2) ts_used_mb,
round(a.maxbytes / power(2, 20), 2) ts_max_mb
from (select f.tablespace_name,
sum(f.bytes) bytes_alloc,
sum(decode(f.autoextensible,
'YES',
f.maxbytes,
'NO',
f.bytes)) maxbytes
from dba_data_files f
group by tablespace_name) a,
(select f.tablespace_name, sum(f.bytes) bytes_free
from dba_free_space f
group by tablespace_name) b
where a.tablespace_name = b.tablespace_name(+)
union all
select h.tablespace_name tablespace_name,
round(sum(nvl(p.bytes_used, 0)) / power(2, 20), 2) ts_used_MB,
round(sum(decode(f.autoextensible,
'YES',
f.maxbytes,
'NO',
f.bytes)) / power(2, 20),
2) ts_max_mb
from v$temp_space_header h, v$temp_extent_pool p, dba_temp_files f
where p.file_id(+) = h.file_id
and p.tablespace_name(+) = h.tablespace_name
and f.file_id = h.file_id
and f.tablespace_name = h.tablespace_name
group by h.tablespace_name)
order by 5 desc ;
重做日志检查
select l.GROUP#,l.THREAD#,l.BYTES,l.MEMBERS,l.STATUS,f.MEMBER from v$log
l,v$logfile f where l.GROUP#=f.GROUP# order by 1;
重做日志切换频率：
select l.GROUP#,l.THREAD#,l.BYTES,l.MEMBERS,l.STATUS,f.MEMBER from v$log
l,v$logfile f where l.GROUP#=f.GROUP# order by 1;
SELECT trunc(first_time) "Date",
to_char(first_time, 'Dy') "Day",THREAD#,
of 24
5墨值下载
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文档的来源（墨天轮），文档链接，文档作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
下载排行榜
评论