RMAN backup & amp; Recovery diagnostic script set

User Managed Backup & Recovery Diagnostic Collection

Note:

Log on to SQL * PLUS with SYSDBA permission for execution
The instance must be mounted or OPEN mode.
Generate the result file: 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/, 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 ------------------