media recovery oracle media recovery

以下脚本可以用于收集介质恢复诊断信息(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] ---