user managed backup & recovery diagnostic collection
注意事项：
需要以sysdba权限登录sql*plus执行
实例需要 mount or open mode.
生成结果文件：recovery_diagnostics.out (default location is /tmp)
----------------- start ------------------
set echo on
set linesize 200 trimspool on
col name form a60
col status form a10
col dbname form a15
col member form a80
col inst_id form 999
col resetlogs_time form a25
col created form a25
col db_unique_name form a15
col stat form 9999999999
col thr form 99999
col uptime form a80
col file# form 999999
col checkpoint_change# form 999999999999999
col first_change# form 999999999999999
col change# form 999999999999999
set pagesize 50000;
alter session set nls_date_format = 'dd-mon-rrrr hh24:mi:ss';
spool '/tmp/recovery_diagnostics.out';
show user
select inst_id, instance_name, status, startup_time || ' - ' ||
trunc(sysdate-(startup_time) ) || ' day(s), ' || trunc(24*((sysdate-startup_time) -
trunc(sysdate-startup_time)))||' hour(s), ' || mod(trunc(1440*((sysdate-startup_time) - trunc(sysdate-startup_time))), 60) ||' minute(s), ' || mod(trunc(86400*((sysdate-startup_time) - trunc(sysdate-startup_time))), 60) ||' seconds' uptime
from gv$instance
order by inst_id
/
select dbid, name, database_role, created, resetlogs_change#, resetlogs_time, open_mode, log_mode, checkpoint_change#, controlfile_type, controlfile_change#, controlfile_time from v$database;
archive log list;
select * from v$controlfile;
select distinct(status), count(*) from v$backup group by status;
select file#, f.name, t.name, f.status, checkpoint_change#
from v$datafile f, v$tablespace t where f.ts#=t.ts#;
select file#, status, checkpoint_change#, checkpoint_time, resetlogs_change#, resetlogs_time, fuzzy from v$datafile_header;
select status,checkpoint_change#,checkpoint_time, resetlogs_change#,
resetlogs_time, count(*), fuzzy from v$datafile_header
group by status,checkpoint_change#,checkpoint_time, resetlogs_change#,
resetlogs_time, fuzzy;
select distinct(fhrba_seq) sequence, count(*) from x$kcvfh group by fhrba_seq;
select v1.thread#, v1.group#, v1.sequence#, v1.first_change#, v1.first_time, v1.next_time,
v1.archived, v1.status,v2.member
from v$log v1, v$logfile v2 where v1.group#=v2.group#
order by v1.first_time;
select * from v$recover_file order by 1;
select distinct(status)from v$datafile;
select round(sum(bytes)/1024/1024/1024,0) db_size_gb from v$datafile;
select fhsta, count(*) from x$kcvfh group by fhsta;
select min(fhrba_seq), max(fhrba_seq) from x$kcvfh;
spool off
----------------- end ------------------
，