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