Search This Blog

Total Pageviews

Wednesday 20 January 2010

Oracle Datafile need recovery ?

If SCN No are not same then yes ...
RECOVERY RELATED EVENTS
recovery datafile

fuzzy datafile ?????????

datafile need recovery !!!!!!!!!!!!


run following SQL ....


online read-write datafile is essentially to be " fuzzy "




alter session set nls_date_format = 'dd-mm-yyyy mi:ss';
set pagesize 20000  linesize 300
set pause off
set serveroutput on
set feedback on
set echo on
set numformat 999999999999999
col name for a60
select name,dbid,controlfile_type,open_mode,checkpoint_change#,archive_change# from v$database;
col name for a75
select fuzzy, count(fuzzy) from v$datafile_header group by fuzzy;
select count(*) from v$datafile_header;
select file#,name,status,enabled from v$datafile;
select file#,name,recover,fuzzy,checkpoint_change#,creation_change#,creation_time from v$datafile_header;

select substr(name,1,60) name, recover, fuzzy, checkpoint_change#, resetlogs_change#,resetlogs_time from v$datafile_header;
select count(*),fhsta from x$kcvfh group by fhsta;
select count(*),fhrba_seq from x$kcvfh group by fhrba_seq;
select count(*),fhscn from x$kcvfh group by fhscn;
select count(*),fhafs from x$kcvfh group by fhafs;
select fhdbn,fhdbi,hxfil,fhsta,fhscn,fhafs,fhrba_seq,fhtnm tbs_name from x$kcvfh;

 


set linesize 200
col file_name format a65
col scn format a20
col TABLESPACE format a25
col fuzzy format a10
col need_recovery format a10
prompt COMPATIBLE is set to 10.0.0.0 or higher X$KCVFH.FHSTA column will show 8196 for system prompt datafile
prompt COMPATIBLE is set to 9.2.0 (lowest possible value for Oracle 10g),
prompt the FHSTA column for system datafile will have a value of 4.
prompt So the value of 8196 for the fhsta (status) column for the first
prompt system tablespace datafile
prompt is normal.
prompt show parameter COMPATIBLE




select HXFIL File_num,substr(HXFNM,1,60) File_name, FHSCN SCN, FHSTA status ,FHRBA_SEQ Sequence from X$KCVFH
/
select name, open_mode, checkpoint_change#, ARCHIVE_CHANGE# from v$database;
select substr(name,1,60) file_name , recover, fuzzy, checkpoint_change# from v$datafile_header;



prompt HXIFZ NUMBER File is fuzzy (YES | NO),decode(hxifz, 0,'NO', 1,'YES', NULL)
prompt HXNRCV NUMBER File needs media recovery (YES | NO)


set linesize 120;
select hxfil File_num ,fhsta STAT,fhscn SCN, fhthr thr, fhrba_Seq SEQUENCE,fhtnm TABLESPACE,
decode(hxifz, 0,'NO', 1,'YES', NULL) fuzzy ,
decode(hxnrcv, 0,'NO', 1,'YES', NULL) need_recovery
from x$kcvfh
order by 1;

=========

set pagesize 9999
set numwidth 20
alter session set nls_date_format='dd-mon-yyyy hh24:mi:ss';
spool archived_log.html
set markup html on; <<<<<<------ br="" for="" format="" html="" report="">

=========





set linesize 180
set pause off
set serveroutput on
set feedback on
set echo on
set numformat 999999999999999
Spool recovery_info.txt
select substr(name, 1, 50), status from v$datafile;
select substr(name,1,40), recover, fuzzy, checkpoint_change# from v$datafile_header;
select GROUP#,substr(member,1,60) from v$logfile;
select * from v$recover_file;
select distinct status from v$backup;
select hxfil FILENUMBER,fhsta STATUS,fhscn SCN,fhrba_Seq SEQUENCE from x$kcvfh;
select distinct (fuzzy) from v$datafile_header;
spool off




col fuzz# format 99999999999999999999999999
col chkpnt# format 99999999999999999999999999
select max(absolute_fuzzy_change#) fuzz#, max(checkpoint_change#) chkpnt# from
(select file#, completion_time, checkpoint_change#, absolute_fuzzy_change# from v$backup_datafile
where incremental_level = 0
and trunc(completion_time) = to_date('JUN-20-2010','MON-DD-YYYY')
and file# <> 0
order by completion_time desc
);



SQL> select checkpoint_change# , current_scn from V$database;

CHECKPOINT_CHANGE# CURRENT_SCN
------------------ -----------
2935719 2950511


SQL> select rtckp_scn from x$kccrt;

RTCKP_SCN
----------------
2935719

SQL> select checkpoint_change# from V$datafile;

CHECKPOINT_CHANGE#
------------------
2935719
2935719
2935719
2935719
1124477
2935719

6 rows selected.


alter session set events 'immediate trace name controlf level 3'


cmd>sqlplus "/as sysdba"
sql>oradebug setmypid
sql>oradebug dump confilef 3;




SQL> select max(next_change#) from v$log_history;

MAX(NEXT_CHANGE#)
-----------------
12374090


cmd>sqlplus "/as sysdba"
sql>oradebug setmypid
sql>oradebug dump confilef 3;


============



set linesize 150
prompt crash recovery needed
SELECT
a.thread#, b.open_mode, a.status,
CASE
WHEN ((b.open_mode='MOUNTED') AND (a.status='OPEN')) THEN 'Crash Recovery req.'
WHEN ((b.open_mode='MOUNTED') AND (a.status='CLOSED')) THEN 'No Crash Rec. req.'
WHEN ((b.open_mode='READ WRITE') AND (a.status='OPEN')) THEN 'Inst. already open'
ELSE 'huh?'
END STATUS
FROM v$thread a,
v$database b,
v$instance c
WHERE a.thread# = c.thread#;





set linesize 150
col name format a70
prompt Media recovery needed

SELECT
a.name,
a.checkpoint_change#,
b.checkpoint_change#,
CASE
WHEN ((a.checkpoint_change# - b.checkpoint_change#) = 0) THEN 'Startup Normal'
WHEN ((a.checkpoint_change# - b.checkpoint_change#) > 0) THEN 'Media Recovery'
WHEN ((a.checkpoint_change# - b.checkpoint_change#) < 0) THEN 'Old Control File'
ELSE 'what the ?'
END STATUS
FROM v$datafile a, -- control file SCN for datafile
v$datafile_header b -- datafile header SCN
WHERE a.file# = b.file#;



col name format a50
select 'controlfile' "SCN location",'SYSTEM checkpoint' name,checkpoint_change# from v$database
union
select 'file in controlfile',name,checkpoint_change# from v$datafile
union
select 'file header',name,checkpoint_change# from v$datafile_header
order by 2;


SCN location NAME CHECKPOINT_CHANGE#
------------------- -------------------------------------------------- ------------------
file header /opt/app/oracle/oradata/orcl/anujtest.dbf 14562941
file in controlfile /opt/app/oracle/oradata/orcl/anujtest.dbf 14562941
file header /opt/app/oracle/oradata/orcl/example01.dbf 14562941
file in controlfile /opt/app/oracle/oradata/orcl/example01.dbf 14562941
file header /opt/app/oracle/oradata/orcl/rman.dbf 14562941
file in controlfile /opt/app/oracle/oradata/orcl/rman.dbf 14562941
file header /opt/app/oracle/oradata/orcl/sysaux01.dbf 14562941
file in controlfile /opt/app/oracle/oradata/orcl/sysaux01.dbf 14562941
file header /opt/app/oracle/oradata/orcl/system01.dbf 14562941
file in controlfile /opt/app/oracle/oradata/orcl/system01.dbf 14562941
file header /opt/app/oracle/oradata/orcl/test.dbf 14562941
file in controlfile /opt/app/oracle/oradata/orcl/test.dbf 14562941
file header /opt/app/oracle/oradata/orcl/tsapexf01.dbf 14562941
file in controlfile /opt/app/oracle/oradata/orcl/tsapexf01.dbf 14562941
file header /opt/app/oracle/oradata/orcl/tsapexu01.dbf 14562941
file in controlfile /opt/app/oracle/oradata/orcl/tsapexu01.dbf 14562941
file header /opt/app/oracle/oradata/orcl/undotbs01.dbf 14562941
file in controlfile /opt/app/oracle/oradata/orcl/undotbs01.dbf 14562941
file header /opt/app/oracle/oradata/orcl/users01.dbf 14562941
file in controlfile /opt/app/oracle/oradata/orcl/users01.dbf 14562941
controlfile SYSTEM checkpoint 14562941

21 rows selected.



prompt get distinct checkpoint_change#
SQL> select checkpoint_change#, 'SYSTEM checkpoint in controlfile' "SCN location" from v$database
union
select distinct checkpoint_change#, 'file in controlfile' from v$datafile
union
select distinct checkpoint_change#, 'file header' from v$datafile_header;

CHECKPOINT_CHANGE# SCN location
------------------ --------------------------------
14562941 SYSTEM checkpoint in controlfile
14562941 file header
14562941 file in controlfile

3 rows selected.



prompt get distinct datafile count
SQL> select 'controlfile' "SCN location",'SYSTEM checkpoint' name,checkpoint_change# from v$database
union
select 'file in controlfile',to_char(count(*)),checkpoint_change# from v$datafile
group by checkpoint_change#
union
select 'file header',to_char(count(*)),checkpoint_change# from v$datafile_header
group by checkpoint_change#;


SCN location NAME CHECKPOINT_CHANGE#
------------------- -------------------------------------------------- ------------------
controlfile SYSTEM checkpoint 14562941
file header 10 14562941
file in controlfile 10 14562941

3 rows selected.





set linesize 200
prompt info from x$kcvfh (All file headers)
col FILE_NAME format a50
col TABLESPACE_NAME format a20
SELECT hxfil file_num,substr(hxfnm,1,40) file_name,fhtyp type,hxerr validity, fhscn chk_ch#,
fhtnm tablespace_name,fhsta status,fhrba_seq sequence
FROM x$kcvfh;

SQL> /

FILE_NUM FILE_NAME TYPE VALIDITY CHK_CH# TABLESPACE_NAME STATUS SEQUENCE
---------- -------------------------------------------------- ---------- ---------- ---------------- -------------------- ---------- ----------
1 /opt/app/oracle/oradata/orcl/system01.db 3 0 14562941 SYSTEM 8196 42
2 /opt/app/oracle/oradata/orcl/sysaux01.db 3 0 14562941 SYSAUX 4 42
3 /opt/app/oracle/oradata/orcl/undotbs01.d 3 0 14562941 UNDOTBS1 4 42
4 /opt/app/oracle/oradata/orcl/users01.dbf 3 0 14562941 USERS 4 42
5 /opt/app/oracle/oradata/orcl/example01.d 3 0 14562941 EXAMPLE 4 42
6 /opt/app/oracle/oradata/orcl/anujtest.db 3 0 14562941 ANUJTEST 4 42
7 /opt/app/oracle/oradata/orcl/tsapexf01.d 3 0 14562941 TSAPEXF 4 42
8 /opt/app/oracle/oradata/orcl/tsapexu01.d 3 0 14562941 TSAPEXU 4 42
9 /opt/app/oracle/oradata/orcl/test.dbf 3 0 14562941 TEST 4 42
10 /opt/app/oracle/oradata/orcl/rman.dbf 3 0 14562941 RMAN 4 42





STATUS

0 - Recovery complete.
1 - Still needs recovery. Apply more archives
4 - FUZZY state. One or more datafiles are in a FUZZY state i.e. good

SQL> select hxfil FILENUMBER,fhsta STATUS,fhscn SCN,fhrba_Seq SEQUENCE from x$kcvfh;

FILENUMBER STATUS SCN SEQUENCE
---------- ---------- ---------------- ----------
1 8196 14562941 42
2 4 14562941 42
3 4 14562941 42
4 4 14562941 42
5 4 14562941 42
6 4 14562941 42
7 4 14562941 42
8 4 14562941 42
9 4 14562941 42
10 4 14562941 42

10 rows selected.

=======================
QUICK CHECK ON BACKUP/RECOVERY RELATED EVENTS:


SELECT * FROM v$backup;

SELECT file#, status, substr(name, 1, 70), checkpoint_change# FROM v$datafile;

SELECT file#, status, checkpoint_change# FROM v$datafile_header;

SELECT substr(name,1,60), recover, fuzzy, checkpoint_change#, resetlogs_change#, resetlogs_time FROM v$datafile_header;

SELECT name, open_mode, checkpoint_change#, ARCHIVE_CHANGE# FROM v$database;

SELECT GROUP#,THREAD#,SEQUENCE#,MEMBERS,ARCHIVED,STATUS,FIRST_CHANGE# FROM v$log;

SELECT GROUP#,substr(member,1,70) FROM v$logfile;

SELECT * FROM v$log_history;
SELECT * FROM v$recover_file;
SELECT * FROM v$recovery_log;

SELECT first_change#, next_change#, sequence#, archived, substr(name, 1, 50) FROM V$ARCHIVED_LOG;

SELECT status,resetlogs_change#,resetlogs_time,checkpoint_change#,to_char(checkpoint_time, 'DD-MON-YYYY HH24:MI:SS') as checkpoint_time,count(*)
FROM v$datafile_header
group by status, resetlogs_change#, resetlogs_time, checkpoint_change#, checkpoint_time
order by status, checkpoint_change#, checkpoint_time ;

SELECT LF.member, L.group#, L.thread#, L.sequence#, L.status,L.first_change#, L.first_time, DF.min_checkpoint_change#
FROM v$log L, v$logfile LF,
(select min(checkpoint_change#) min_checkpoint_change#
from v$datafile_header
where status='ONLINE') DF
WHERE LF.group# = L.group#
AND L.first_change# >= DF.min_checkpoint_change#;

SELECT * FROM V$RECOVERY_FILE_DEST;

SELECT * FROM V$FLASH_RECOVERY_AREA_USAGE;

SELECT V1.GROUP#, MEMBER, SEQUENCE#, FIRST_CHANGE#
FROM V$LOG V1, V$LOGFILE V2
WHERE V1.GROUP# = V2.GROUP# ;

SELECT FILE#, CHANGE# FROM V$RECOVER_FILE;

select al.sequence# from v$archived_log al, v$log rl
where al.sequence# = rl.sequence# (+)
and al.thread# = rl.thread# (+)
and ( rl.status = 'INACTIVE' or rl.status is null )
and al.deleted = 'NO'
order by al.sequence#


SELECT RECOVERY_ESTIMATED_IOS FROM V$INSTANCE_RECOVERY;

======================



SQL> select hxfil FILENUMBER, fhsta STATUS, fhscn SCN, fhrba_seq SEQUENCE from x$kcvfh
2 /

FILENUMBER STATUS SCN SEQUENCE
---------- ---------- ---------------- ----------
1 8196 17062091 56
2 4 17062091 56
3 4 17062091 56
4 4 17062091 56
5 4 17062091 56
6 4 17062091 56
7 4 17062091 56
8 4 17062091 56
9 4 17062091 56
10 4 17062091 56
11 4 17062091 56
12 4 17062091 56


The STATUS is 0 when the recovery is sufficient and it is safe to OPEN the database.

The fhsta can have one of the below values:

0 – DB is consistent. No more recovery required.
1 – DB needs more recovery. It’s time to apply more archives.
4 – DB is in a FUZZY state. Was the backup good?
8192 -
8196 -




The table X$KCVFH is a fixed table in Oracle. It stores the file headers along with their statuses. The name is derived from:

K – Kernel layer
C – Cache layer
V – RecoVery component
FH - File Header

from
http://drdatabase.wordpress.com/2010/07/09/how-much-recovery-is-enough-recovery/

Oracle DBA

anuj blog Archive