Script:收集介质恢复诊断信息

以下脚本可以用于收集介质恢复诊断信息(recovery_info.sql):


--- begin [recovery_info.sql] ---
define spoolfile = &1
spool &spoolfile
alter session set nls_date_format = 'YYYY-MM-DD HH24:MI:SS';
alter session set timed_statistics = true;
alter session set max_dump_file_size = UNLIMITED;
set feedback on
set term on
set wrap on
set trimspool on
set pagesize 1000
set linesize 100
set numwidth 10
select to_char(sysdate) start_time from dual;
column host_name format a20 tru
select instance_name, host_name, version, status, startup_time from v$instance;
set echo on
select * from v$database;
select * from v$controlfile;
select * from v$tablespace;
select * from v$datafile;
select * from v$datafile_header;
select * from v$tempfile;
select * from v$thread;
select * from v$log;
select * from v$logfile;
select * from v$archived_log;
alter session set events 'immediate trace name file_hdrs level 3';
alter session set events 'immediate trace name redohdr level 3';
set echo off
Prompt;
Prompt Output file name is:;
define spoolfile
Prompt;
Prompt ALERT.LOG and TRACE FILES are located in:;
column host_name format a12 tru
column name format a20 tru
column value format a60 tru
select distinct i.host_name, p.name, p.value from v$instance i, v$parameter p
 where p.name like '%_dump_dest'
   and p.name != 'core_dump_dest';
select to_char(sysdate) end_time from dual;
spool off
exit
--- end [recovery_info.sql] ---