oralce check 命令

set termout off
set pages 9999
SET MARKUP HTML ON
spool my_html.htm

col name for a30
col value for a140
set line 200 pages 100
select inst_id,name,value,decode(name,
'db_name',1,
'db_unique_name',2,
'job_queue_processes',3,
'log_archive_config',4,
'control_files',5,
'log_archive_dest_1',6,
'log_archive_dest_2',7,
'log_archive_dest_state_1',8,
'log_archive_dest_state_2',9,
'remote_login_passwordfile',10,
'db_file_name_convert',11,
'log_file_name_convert',12,
'standby_file_management',13,
'fal_server',14,
'log_archive_max_processes',15,
'compatible',16,
'service_names',17,
'remote_listener',18,
'local_listener',19,
'undo_retention',20,
'undo_tablespace',21,
'cpu_count',22,
'gcs_server_processes',23,
'instance_number',24,
'thread',25,
'db_files',26,
'processes',27,
'log_archive_trace',28,
'event', 29,
'spfile',30,
'memory_max_target',31,
'memory_target',32,
'sga_max_size',33,
'sga_target',34,
'pga_aggregate_target',35,
'log_buffer',36,
'db_cache_size',37,
'shared_pool_size',38,
'shared_pool_reserved_size',39,
'large_pool_size',40,
'java_pool_size',41,
'streams_pool_size',42,
'result_cache_max_size',43,
'db_recovery_file_dest',44,
'db_recovery_file_dest_size' ,45
) parm_order
from gv$parameter
where name in (
'db_name',
'db_unique_name',
'job_queue_processes',
'log_archive_config',
'control_files',
'log_archive_dest_state_1',
'log_archive_dest_state_2',
'log_archive_dest_1',
'log_archive_dest_2',
'remote_login_passwordfile',
'db_file_name_convert',
'log_file_name_convert',
'standby_file_management',
'fal_server',
'log_archive_max_processes',
'compatible',
'service_names',
'remote_listener',
'local_listener',
'undo_retention',
'undo_tablespace',
'cpu_count',
'gcs_server_processes',
'thread',
'instance_number',
'db_files',
'processes',
'log_archive_trace',
'event',
'spfile',
'memory_max_target',
'memory_target',
'sga_max_size',
'sga_target',
'pga_aggregate_target',
'log_buffer',
'db_cache_size',
'shared_pool_size',
'shared_pool_reserved_size',
'large_pool_size',
'java_pool_size',
'streams_pool_size',
'result_cache_max_size',
'db_recovery_file_dest',
'db_recovery_file_dest_size'
)
order by 4,2,1;

select thread#,min(group#) min_group,max(group#) max_group,min(bytes/1024/1024/1204) min_gb,max(bytes)/1024/1024/1204 max_gb,count(*)
from v$log
group by thread#
order by 2,1;

select tablespace_name,sum(bytes)/1024/1024/1024 sizegb,count(*)
from dba_temp_files
group by tablespace_name
order by 1;

select decode(log_mode,'ARCHIVELOG','归档模式','非归档模式','手工模式'),
decode(database_role,'PRIMARY','主库角色','PHYSICAL STANDBY','备库角色','其它角色'),
force_logging
from v$database;

select substr(name,1,40) name,time,RESTORE_POINT_TIME from v$restore_point;

mpr:

set line 200
col value for a26
col unit for a40
select * from gv$dataguard_stats order by 1;
select inst_id,process,status, THREAD#,SEQUENCE#, BLOCK# ,blocks from gV$MANAGED_STANDBY order by 1,2;

SELECT inst_id,RECOVERY_MODE FROM gV$ARCHIVE_DEST_STATUS WHERE DEST_ID=2;

主库检查
SELECT THREAD#, SEQUENCE# FROM V$THREAD;
--备库检查
SELECT THREAD#, MAX(SEQUENCE#) FROM V$ARCHIVED_LOG
WHERE APPLIED = 'YES'
AND RESETLOGS_CHANGE# = (SELECT RESETLOGS_CHANGE#
FROM V$DATABASE_INCARNATION WHERE STATUS = 'CURRENT')
GROUP BY THREAD#;

SELECT NAME FROM V$DATAFILE WHERE STATUS='OFFLINE';

---ASM DISK:
col value for a120
col name for a20
set line 200
select 'DGname' as name,'name:'||name||'-ausize:'|| ALLOCATION_UNIT_SIZE||'-type:'||type||'-total_mb:'||total_mb||'-free_mb:'||free_mb value from v$asm_diskgroup;

col dgname for a20
col attr_name for a20
col value for a20
set line 120
select gg.inst_id,gg.name dgname,ga.name attr_name,value
from gv$asm_attribute ga,gv$asm_diskgroup gg
where gg.inst_id=ga.inst_id and gg.group_number=ga.group_number and
ga.name in (
'compatible.asm',
'compatible.rdbms',
'disk_repair_time',
'au_size'
)
order by 1,2,3;

col name for a30
col value for a140
set line 200 pages 100
select inst_id,name,value,decode(name,
'asm_diskstring', 1,
'memory_max_target',2,
'memory_target',3,
'sga_max_size',4,
'sga_target',5,
'processes',6,
'asm_power_limit', 7,
'_asm_hbeatiowait',8
) parm_order
from gv$parameter
where name in (
'asm_diskstring',
'memory_max_target',
'memory_target',
'sga_max_size',
'sga_target',
'processes',
'asm_power_limit','_asm_hbeatiowait')
order by inst_id,parm_order;

select count(*) from gv$asm_operation;

spool off;