RMAN備份&恢複診斷指令碼集

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 ------------------