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;
-- with con_id
select fhdbn,fhdbi,hxfil,fhsta,fhscn,fhafs,fhrba_seq,fhtnm tbs_name ,con_id 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 -
=====
set linesize 300 pagesize 100
col inst_id for 9999999 heading 'Instance #'
col file_nr for 9999999 heading 'File #'
col file_name for A70 heading 'File name'
col checkpoint_change_nr for 99999999999999 heading 'Checkpoint #'
col checkpoint_change_time for A20 heading 'Checkpoint time'
col last_change_nr for 99999999999999 heading 'Last change #'
col SCNStatus for a15
SELECT
fe.inst_id,
fe.CON_ID, ---- for >12c
fe.fenum file_nr,
fn.fnnam file_name,
TO_NUMBER (fe.fecps) checkpoint_change_nr,
fe.fecpt checkpoint_change_time,
fe.fests last_change_nr,
NVL2(fe.fecps, '<-Good', 'Recover to this scn') SCNStatus, ---- Recover to max 'scn'
DECODE (
fe.fetsn,
0, DECODE (BITAND (fe.festa, 2), 0, 'SYSOFF', 'SYSTEM'),
DECODE (BITAND (fe.festa, 18),
0, 'OFFLINE',
2, 'ONLINE',
'RECOVER')
) status
FROM x$kccfe fe, x$kccfn fn
WHERE ( (fe.fepax != 65535 AND fe.fepax != 0 ) OR (fe.fepax = 65535 OR fe.fepax = 0) )
AND fn.fnfno = fe.fenum
AND fe.fefnh = fn.fnnum
AND fe.fedup != 0
AND fn.fntyp = 4
AND fn.fnnam IS NOT NULL
AND BITAND (fn.fnflg, 4) != 4
ORDER BY fe.fenum
;
select inst_id,hxfil,
decode(hxerr, 0,decode(bitand(fhsta, 1), 0,'NOT ACTIVE','ACTIVE'),
1,'FILE MISSING',
2,'OFFLINE NORMAL',
3,'NOT VERIFIED',
4,'FILE NOT FOUND',
5,'CANNOT OPEN FILE',
6,'CANNOT READ HEADER',
7,'CORRUPT HEADER',
8,'WRONG FILE TYPE',
9,'WRONG DATABASE',
10,'WRONG FILE NUMBER',
11,' WRONG FILE CREATE',
12,'WRONG FILE CREATE',
16,'DELAYED OPEN', 'UNKNOWN ERROR'),
to_number(fhbsc),
to_date(fhbti,'MM/DD/RR HH24:MI:SS','NLS_CALENDAR=Gregorian'),
con_id
from x$kcvfhonl
select count(*) from v$backup where status != 'NOT ACTIVE' ;
col checkpoint_change# format 999999999999999
prompt The following should return one distinct number
set numf 99999999999999999
col checkpoint_change# format 999999999999999
col name for a15
Select distinct d.CON_ID,c.name ,max(checkpoint_change#) max_checkpoint_change#, min(checkpoint_change#) min_checkpoint_change# from v$datafile d ,V$CONTAINERS c
where 1=1
and d.con_id=c.con_id
group by d.CON_ID,c.name
order by 1
;
set numf 99999999999999999999 pagesize 500
col STATUS for 99999
select hxfil FILENUMBER, fhsta STATUS, fhscn SCN, fhrba_seq SEQUENCE from x$kcvfh ;
select con_id,max(fhsta) STATUS, max(fhscn) SCN, max(fhrba_seq) SEQUENCE from x$kcvfh
group by con_id;
col CHECKPOINT_TIME for a30
select distinct con_id ,to_char(CHECKPOINT_TIME,'DD-MON-YYYY HH24:MI:SS') CHECKPOINT_TIME from v$datafile_header;
prompt This should return "0" and "8192" as output
select distinct con_id, fhsta from x$kcvfh
order by 1;
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/
set linesize 300 pagesize 20000
<------ br="" for="" format="" html="" report="">
set pause off
set serveroutput on
set feedback on
set echo on
set numformat 999999999999999
alter session set NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS';
select name, CREATED, controlfile_type, open_mode, checkpoint_change#, ARCHIVE_CHANGE#, FLASHBACK_ON, FORCE_LOGGING from v$database;
select incarnation#,resetlogs_change#,resetlogs_time,prior_resetlogs_change#,prior_resetlogs_time,status from v$database_incarnation;
select substr(name, 1, 70), status from v$datafile;
select substr(name,1,70), recover, fuzzy, checkpoint_change# from v$datafile_header;
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$BACKUP_CORRUPTION;
select * from V$COPY_CORRUPTION;
select * from V$DATABASE_BLOCK_CORRUPTION;
SELECT * FROM v$recovery_log;
SELECT f.name,b.status,b.change#,b.time FROM v$backup b,v$datafile f WHERE b.file# = f.file# AND b.status='ACTIVE';
col file_name for a70
SELECT con_id,hxfil file_num,substr(hxfnm,1,70) file_name,fhtyp type,hxerr validity, fhscn chk_ch#, fhtnm tablespace_name,
fhsta status,fhrba_seq sequence FROM x$kcvfh;
col con_id for a10
select '' "con_id",'controlfile' "SCN location",'SYSTEM checkpoint' name,checkpoint_change#
from v$database
union
select to_char(con_id),'file in controlfile',to_char(count(*)),checkpoint_change#
from v$datafile
group by to_char(con_id),checkpoint_change#
union
select to_char(con_id),'file header',to_char(count(*)),checkpoint_change#
from v$datafile_header
group by to_char(con_id),checkpoint_change#;
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;
-- The query below checks if all of the online datafiles are synchronized in terms of their SCN (system change number),
you can normally open your database your database if the SCNs are synced and redo from redo logs are applied
select status, checkpoint_change#,
to_char(checkpoint_time, 'DD-MON-YYYY HH24:MI:SS') as checkpoint_time,
count(*)
from v$datafile_header
group by status, checkpoint_change#, checkpoint_time
order by status, checkpoint_change#, checkpoint_time;
select con_id,status, checkpoint_change#,
to_char(checkpoint_time, 'DD-MON-YYYY HH24:MI:SS') as checkpoint_time,
count(*)
from v$datafile_header
group by con_id,status, checkpoint_change#, checkpoint_time
order by con_id,status, checkpoint_change#, checkpoint_time;
-- The query below checks for offline datafiles
select file#, name from v$datafile
where file# in (select file# from v$datafile_header
where status='OFFLINE');
-- The query below checks if the required redo log sequence# is still available in the online redo logs and the corresponding redo log member is still physically existing on the disk
set echo on feedback on pagesize 100 numwidth 16
alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS';
col MEMBER for a50
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#;
-- Once the redo log member is identified, execute the recover command and apply the redo log member to fully recover the database
SET PAGESIZE 20000
SET LINESIZE 1000
SET TRIMSPOOL ON
SET PAUSE OFF
SET SERVEROUTPUT ON
SET FEEDBACK ON
SET ECHO ON
SET NUMFORMAT 999999999999999
COL TABLESPACE_NAME FORMAT A50
COL FILE_NAME FORMAT A50
COL NAME FORMAT A50
COL MEMBER FORMAT A50
col DFILE_CHKP_CHANGE format a40
col DFILE_HED_CHKP_CHANGE format a40
ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MM-YYYY HH24:MI:SS';
ARCHIVE LOG LIST;
SELECT *
FROM v$instance;
SELECT dbid,
name,
TO_CHAR(created, 'DD-MM-YYYY HH24:MI:SS') created,
open_mode,
log_mode,
TO_CHAR(checkpoint_change#, '999999999999999') as checkpoint_change#,
controlfile_type,
TO_CHAR(controlfile_change#, '999999999999999') as controlfile_change#,
TO_CHAR(controlfile_time, 'DD-MM-YYYY HH24:MI:SS') controlfile_time,
TO_CHAR(resetlogs_change#, '999999999999999') as resetlogs_change#,
TO_CHAR(resetlogs_time, 'DD-MM-YYYY HH24:MI:SS') resetlogs_time
FROM v$database;
SELECT *
FROM v$recover_file;
SELECT *
FROM v$recovery_log;
SELECT f.name, b.status, b.change#, b.time
FROM v$backup b,
v$datafile f
WHERE b.file# = f.file#
AND b.status = 'ACTIVE';
set linesize 800
col name for a90
SELECT name,
file#,
status,
enabled,
creation_change#,
TO_CHAR(creation_time, 'DD-MM-YYYY HH24:MI:SS') as creation_time,
TO_CHAR(checkpoint_change#, '999999999999999') as checkpoint_change#,
TO_CHAR(checkpoint_time, 'DD-MM-YYYY HH24:MI:SS') as checkpoint_time,
TO_CHAR(offline_change#, '999999999999999') as offline_change#,
TO_CHAR(online_change#, '999999999999999') as online_change#,
TO_CHAR(online_time, 'DD-MM-YYYY HH24:MI:SS') as online_time,
TO_CHAR(bytes, '9,999,999,999,990') as bytes
FROM v$datafile
ORDER BY checkpoint_change#;
SELECT name,
file#,
status,
error,
creation_change#,
TO_CHAR(creation_time, 'DD-MM-YYYY HH24:MI:SS') as creation_time,
TO_CHAR(checkpoint_change#, '999999999999999') as checkpoint_change#,
TO_CHAR(checkpoint_time, 'DD-MM-YYYY HH24:MI:SS') as checkpoint_time,
TO_CHAR(resetlogs_change#, '999999999999999') as resetlogs_change#,
TO_CHAR(resetlogs_time, 'DD-MM-YYYY HH24:MI:SS') as resetlogs_time,
TO_CHAR(bytes, '9,999,999,999,990') as bytes
FROM v$datafile_header
ORDER BY checkpoint_change#;
SELECT con_id,status,
checkpoint_change#,
TO_CHAR(checkpoint_time, 'DD-MM-YYYY HH24:MI:SS') as checkpoint_time,
count(*)
FROM v$datafile_header
GROUP BY con_id,status, checkpoint_change#, checkpoint_time
ORDER BY con_id,status, checkpoint_change#, checkpoint_time;
SELECT dd.FILE#,
dd.NAME,
dd.STATUS,
to_char(dd.checkpoint_change#,'999999999999999') dfile_chkp_change,
to_char(dh.checkpoint_change#,'999999999999999') dfile_hed_chkp_change,
dh.recover,
dh.fuzzy
FROM v$datafile dd,
v$datafile_header dh
WHERE dd.FILE#=dh.FILE#;
select * from v$database_incarnation;
SELECT hxfil file_num,
hxfnm file_name,
fhtyp type,
hxerr validity,
fhscn scn,
fhtnm tablespace_name,
fhsta status ,
fhthr thread,
fhrba_seq sequence
FROM x$kcvfh
order by scn;
SELECT fhthr thread,
fhrba_seq sequence,
fhscn scn,
fhsta status,
count(*)
FROM x$kcvfh
group by fhthr,fhrba_seq,fhscn,fhsta;
SELECT hxfil file_num,
fhscn scn,
fhsta status ,
fhthr thread,
fhrba_seq sequence
FROM x$kcvfh
order by scn;
SELECT group#,
thread#,
sequence#,
members,
archived,
status,
TO_CHAR(first_change#, '999999999999999') as first_change#
FROM v$log;
SELECT group#,
member
FROM v$logfile;
SELECT a.recid,
a.thread#,
a.sequence#,
a.name,
tO_CHAR(a.first_change#, '999999999999999') as first_change#,
to_char(a.NEXT_CHANGE#, '999999999999999') as next_change# ,
a.archived,
a.deleted,
TO_DATE(a.completion_time, 'DD-MM-YYYY HH24:MI:SS') as completed
FROM v$archived_log a, v$log l
WHERE a.thread# = l.thread#
AND a.sequence# = l.sequence#;
SELECT a.recid,
a.thread#,
a.sequence#,
a.name,
tO_CHAR(a.first_change#, '999999999999999') as first_change#,
to_char(a.NEXT_CHANGE#, '999999999999999') as next_change# ,
a.archived,
a.deleted,
TO_DATE(a.completion_time, 'DD-MM-YYYY HH24:MI:SS') as completed
FROM v$archived_log a, v$recovery_log l
WHERE a.thread# = l.thread#
AND a.sequence# = l.sequence#;
SELECT recid,
thread#,
sequence#,
name,
tO_CHAR(first_change#, '999999999999999') as first_change#,
to_char(NEXT_CHANGE#, '999999999999999') as next_change# ,
archived,
deleted,
TO_DATE(completion_time, 'DD-MM-YYYY HH24:MI:SS') as completed,
blocks,
block_size
FROM v$archived_log;
==============
alter session set container='dd-mm-yyyy hh24:mi';
set linesize 300 pagesize 100
col inst_id for 9999999 heading 'Instance #'
col file_nr for 9999999 heading 'File #'
col file_name for A50 heading 'File name'
col checkpoint_change_nr for 99999999999999 heading 'Checkpoint #'
col checkpoint_change_time for A20 heading 'Checkpoint time'
col last_change_nr for 99999999999999 heading 'Last change #'
SELECT
-- fe.inst_id,
fn.con_id,
fe.fenum file_nr,
fn.fnnam file_name,
TO_NUMBER (fe.fecps) checkpoint_change_nr,
to_date(fe.fecpt,'mm/dd/yyyy hh24:mi:ss') checkpoint_change_time,
fe.fests last_change_nr,
DECODE ( fe.fetsn,0, DECODE (BITAND (fe.festa, 2), 0, 'SYSOFF', 'SYSTEM'), DECODE (BITAND (fe.festa, 18), 0, 'OFFLINE',2, 'ONLINE','RECOVER')
) status
, FECRC_RBA_SEQ
FROM x$kccfe fe, x$kccfn fn
WHERE ( (fe.fepax != 65535 AND fe.fepax != 0 )
OR (fe.fepax = 65535 OR fe.fepax = 0)
)
AND fn.fnfno = fe.fenum
AND fe.fefnh = fn.fnnum
AND fe.fedup != 0
AND fn.fntyp = 4
AND fn.fnnam IS NOT NULL
AND BITAND (fn.fnflg, 4) != 4
ORDER BY con_id,fe.fenum
;
set linesize 300 pagesize 200
col name for a50
select hxfil file_num,substr(hxfnm,1,40) name,fhtyp type,hxerr validity, fhscn scn, fhtnm tablespace_name,fhsta status ,fhrba_seq sequence, fhthr thread
from x$kcvfh
where 1=1
-- and fhrba_seq =xx
RMAN> list backup of datafile 1;
alter session set nls_date_format='DD-MON-RRRR HH24:MI:SS';
col first_change# for 9999999999999999
col next_change# for 9999999999999999
define SCN_bkupstart=994376036921
select name, thread#, sequence#, status, first_time, next_time, first_change#, next_change#
from v$archived_log
where &SCN_bkupstart between first_change# and next_change#;
prompt This should return "0" and "8192" as output
select distinct con_id, fhsta from x$kcvfh
order by 1;
https://github.com/carlos-sierra/cscripts/blob/master/cs_table.sql
FNNAM
select fe.inst_id,fe.fenum,to_number(fe.fecrc_scn), to_date(fe.fecrc_tim,'MM/DD/RR HH24:MI:SS','NLS_CALENDAR=Gregorian'),
fe.fetsn,
fe.ferfn,
decode(fe.fetsn,0,decode(bitand(fe.festa,2),0,'SYSOFF','SYSTEM'),
decode(bitand(fe.festa,18),0,'OFFLINE',2,'ONLINE','RECOVER')),
decode(fe.fedor,2,'READ ONLY', decode(bitand(fe.festa, 12), 0,'DISABLED',4,'READ ONLY',12,'READ WRITE','UNKNOWN')),
to_number(fe.fecps),
to_date(fe.fecpt,'MM/DD/RR HH24:MI:SS','NLS_CALENDAR=Gregorian'),
to_number(fe.feurs),
to_date(fe.feurt,'MM/DD/RR HH24:MI:SS','NLS_CALENDAR=Gregorian'),
to_number(fe.fests),
decode(fe.fests,NULL,to_date(NULL),
to_date(fe.festt,'MM/DD/RR HH24:MI:SS','NLS_CALENDAR=Gregorian')),
to_number(fe.feofs),
to_number(fe.feonc_scn),
to_date(fe.feonc_tim,'MM/DD/RR HH24:MI:SS','NLS_CALENDAR=Gregorian'),
fh.fhfsz*fe.febsz,
fh.fhfsz,
fe.fecsz*fe.febsz,
fe.febsz,
fn.fnnam,
fe.fefdb,
fn.fnbof,
decode(fe.fepax,0, 'UNKNOWN', 65535, 'NONE', fnaux.fnnam),
to_number(fh.fhfirstunrecscn), to_date(fh.fhfirstunrectime,'MM/DD/RR HH24:MI:SS','NLS_CALENDAR=Gregorian'),
fe.fepdi,
fe.fefcrs,
fe.fefcrt,
decode(fe.fefdb, 1, 'YES', 'NO'),
fe.feplus,
fe.feprls,
fe.feprlt
from x$kccfe fe, x$kccfn fn, x$kccfn fnaux, x$kcvfh fh
where
((fe.fepax!=65535 and fe.fepax!=0 and fe.fepax=fnaux.fnnum) or ((fe.fepax=65535 or fe.fepax=0) and fe.fenum=fnaux.fnfno
and fnaux.fntyp=4
and fnaux.fnnam is not null
and bitand(fnaux.fnflg, 4) != 4
and fe.fefnh=fnaux.fnnum))
and fn.fnfno=fe.fenum
and fn.fnfno=fh.hxfil
and fe.fefnh=fn.fnnum
and fe.fedup!=0
and fn.fntyp=4
and fn.fnnam is not null
and bitand(fn.fnflg, 4) != 4
order by fe.fenum
/
checkpoint_change_nr
alter session set nls_date_format='dd-mm-yyyy hh24:mi';
set linesize 900
set numf 9999999999999999999
col INST_ID for 99
col FENUM for 999999
col file_status for a15
col "file_status?" for a15
col checkpoint_change_nr for 99999999999999 heading 'Checkpoint #'
col checkpoint_change_time for A20 heading 'Checkpoint time'
col last_change_nr for 99999999999999 heading 'Last change #'
select fe.inst_id,
fh.CON_ID,
fe.fenum,
to_number(fe.fecrc_scn) fecrc_scn,
to_date(fe.fecrc_tim,'MM/DD/RR HH24:MI:SS','NLS_CALENDAR=Gregorian') fecrc_tim
,fe.fetsn
,fe.ferfn
,decode(fe.fetsn,0,decode(bitand(fe.festa,2),0,'SYSOFF','SYSTEM')
,decode(bitand(fe.festa,18),0,'OFFLINE',2,'ONLINE','RECOVER')) "file_status?"
,decode(fe.fedor,2,'READ ONLY', decode(bitand(fe.festa, 12), 0,'DISABLED',4,'READ ONLY',12,'READ WRITE','UNKNOWN')) file_status,
to_number(fe.fecps) checkpoint_change_nr,
to_date(fe.fecpt,'MM/DD/RR HH24:MI:SS','NLS_CALENDAR=Gregorian'),
to_number(fe.feurs),
to_date(fe.feurt,'MM/DD/RR HH24:MI:SS','NLS_CALENDAR=Gregorian'),
to_number(fe.fests), decode(fe.fests,NULL,to_date(NULL),
to_date(fe.festt,'MM/DD/RR HH24:MI:SS','NLS_CALENDAR=Gregorian')),
to_number(fe.feofs),
to_number(fe.feonc_scn),
to_date(fe.feonc_tim,'MM/DD/RR HH24:MI:SS','NLS_CALENDAR=Gregorian'),
fh.fhfsz*fe.febsz,
fh.fhfsz,
fe.fecsz*fe.febsz,
fe.febsz,
--fn.fnnam,
fe.fefdb,
fn.fnbof,
--decode(fe.fepax,0, 'UNKNOWN', 65535, 'NONE', fnaux.fnnam),
to_number(fh.fhfirstunrecscn),
to_date(fh.fhfirstunrectime,'MM/DD/RR HH24:MI:SS','NLS_CALENDAR=Gregorian'),
fe.fepdi,
fe.fefcrs,
fe.fefcrt,
decode(fe.fefdb, 1, 'YES', 'NO'),
fe.feplus,
fe.feprls,
fe.feprlt
from x$kccfe fe, x$kccfn fn, x$kccfn fnaux, x$kcvfh fh
where
((fe.fepax!=65535 and fe.fepax!=0 and fe.fepax=fnaux.fnnum) or ((fe.fepax=65535 or fe.fepax=0)
and fe.fenum=fnaux.fnfno
and fnaux.fntyp=4
--and fnaux.fnnam is not null
and bitand(fnaux.fnflg, 4) != 4
and fe.fefnh=fnaux.fnnum))
and fn.fnfno=fe.fenum
and fn.fnfno=fh.hxfil
and fe.fefnh=fn.fnnum
and fe.fedup!=0
and fn.fntyp=4
--and fn.fnnam is not null
and bitand(fn.fnflg, 4) != 4
order by fe.fenum
;
alter session set container='dd-mm-yyyy hh24:mi';
set linesize 300 pagesize 100
col inst_id for 9999999 heading 'Instance #'
col file_nr for 9999999 heading 'File #'
col file_name for A50 heading 'File name'
col checkpoint_change_nr for 99999999999999 heading 'Checkpoint #'
col checkpoint_change_time for A20 heading 'Checkpoint time'
col last_change_nr for 99999999999999 heading 'Last change #'
SELECT
-- fe.inst_id,
fn.con_id,
fe.fenum file_nr,
fn.fnnam file_name,
TO_NUMBER (fe.fecps) checkpoint_change_nr,
to_date(fe.fecpt,'mm/dd/yyyy hh24:mi:ss') checkpoint_change_time,
fe.fests last_change_nr,
DECODE ( fe.fetsn,0, DECODE (BITAND (fe.festa, 2), 0, 'SYSOFF', 'SYSTEM'), DECODE (BITAND (fe.festa, 18), 0, 'OFFLINE',2, 'ONLINE','RECOVER')
) status
, FECRC_RBA_SEQ
FROM x$kccfe fe, x$kccfn fn
WHERE ( (fe.fepax != 65535 AND fe.fepax != 0 )
OR (fe.fepax = 65535 OR fe.fepax = 0)
)
AND fn.fnfno = fe.fenum
AND fe.fefnh = fn.fnnum
AND fe.fedup != 0
AND fn.fntyp = 4
AND fn.fnnam IS NOT NULL
AND BITAND (fn.fnflg, 4) != 4
ORDER BY con_id,fe.fenum
;
set linesize 300 pagesize 200
col name for a50
select hxfil file_num,substr(hxfnm,1,40) name,fhtyp type,hxerr validity, fhscn scn, fhtnm tablespace_name,fhsta status ,fhrba_seq sequence, fhthr thread
from x$kcvfh
where 1=1
-- and fhrba_seq =xx
select count(*) from v$backup where status != 'NOT ACTIVE' ;
col checkpoint_change# format 999999999999999
prompt The following should return one distinct number
set numf 99999999999999999
col checkpoint_change# format 999999999999999
col name for a15
Select distinct d.CON_ID,c.name ,max(checkpoint_change#) max_checkpoint_change#, min(checkpoint_change#) min_checkpoint_change# from v$datafile d ,V$CONTAINERS c
where 1=1
and d.con_id=c.con_id
group by d.CON_ID,c.name
order by 1
;
set numf 99999999999999999999 pagesize 500
col STATUS for 99999
select hxfil FILENUMBER, fhsta STATUS, fhscn SCN, fhrba_seq SEQUENCE from x$kcvfh ;
select con_id,max(fhsta) STATUS, max(fhscn) SCN, max(fhrba_seq) SEQUENCE from x$kcvfh
group by con_id;
col NAME for a25
select distinct kc.con_id,p.name, kc.fhsta from x$kcvfh kc,v$pdbs p
where 1=1
and kc.con_id=p.con_id(+)
order by 1
set linesize 300
col DATAFILE_NAME for a70
col CONTAINER for a20
select hxfil file#, d.name datafile_name , c.name container, fhsta fh_status, fhscn fh_scn, fhrba_seq fh_seq , fhafs fh_abs
from x$kcvfh x, v$datafile d, v$containers c
where d.file# = x.hxfil
and d.con_id = c.con_id
order by 5;
col CHECKPOINT_TIME for a30
select distinct con_id ,to_char(CHECKPOINT_TIME,'DD-MON-YYYY HH24:MI:SS') CHECKPOINT_TIME from v$datafile_header;
col NAME for a25
col 'file-Status' for a25
select distinct kc.con_id,p.name
, kc.fhsta ,
decode(kc.fhsta ,0,'consistent- mounted',
1,'database backup mode(DB needs more recovery)',
4,'online fuzzy',
64 ,'fuzzy needs recovery',
8192,'internal',
8193,'database backup mode(datafile 1 only)',
8196 , 'online fuzzy'
) "file-Status"
from x$kcvfh kc,v$pdbs p
where 1=1
and kc.con_id=p.con_id(+)
order by 1
set numwidth 30 pagesize 50000
alter session set nls_date_format = 'DD-MON-RRRR HH24:MI:SS';
select
con_id,
status,checkpoint_change#,checkpoint_time, resetlogs_change#, resetlogs_time, count(*), fuzzy
from v$datafile_header
group by con_id,status,checkpoint_change#,checkpoint_time, resetlogs_change#, resetlogs_time, fuzzy;
select min(fhrba_Seq), max(fhrba_Seq) from X$KCVFH
where con_id!=2;
MIN(FHRBA_SEQ) MAX(FHRBA_SEQ)
---------------- ----------------
20610 20610
-------------
SET PAGESIZE 20000
SET LINESIZE 1000
SET TRIMSPOOL ON
SET PAUSE OFF
SET SERVEROUTPUT ON
SET FEEDBACK ON
SET ECHO ON
SET NUMFORMAT 999999999999999999999999
ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MM-YYYY HH24:MI:SS';
spool datafile.html
SET MARKUP HTML ON SPOOL ON PREFORMAT OFF ENTMAP ON -
HEAD "<TITLE>Datafile Report</TITLE> -
<STYLE type='text/css'> -<!-- BODY {background: #FFFFFF} --> -
</STYLE>" -
BODY "TEXT='#000FF'" -
TABLE "WIDTH='90%' BORDER='5'"
SET MARKUP HTML ENTMAP OFF
PROMPT <H2><center><b>Datafile Report </b></center></H2>
SET MARKUP HTML ENTMAP ON
SET VERIF off feedback off
ARCHIVE LOG LIST;
SELECT * FROM v$instance;
SELECT dbid,
name,
created created,
open_mode,
log_mode,
checkpoint_change# as checkpoint_change#,
controlfile_type,
controlfile_change# as controlfile_change#,
controlfile_time controlfile_time,
resetlogs_change# as resetlogs_change#,
resetlogs_time resetlogs_time
FROM v$database;
SELECT * FROM v$recover_file;
SELECT * FROM v$recovery_log;
SELECT f.name, b.status, b.change#, b.time FROM v$backup b,
v$datafile f
WHERE b.file# = f.file#
AND b.status = 'ACTIVE';
SELECT name,
file#,
status,
enabled,
creation_change#,
creation_time as creation_time,
checkpoint_change# as checkpoint_change#,
checkpoint_time as checkpoint_time,
offline_change# as offline_change#,
online_change# as online_change#,
online_time as online_time,
bytes as bytes
FROM v$datafile
ORDER BY checkpoint_change#;
SELECT name,
file#,
status,
error,
creation_change#,
creation_time as creation_time,
checkpoint_change# as checkpoint_change#,
checkpoint_time as checkpoint_time,
resetlogs_change# as resetlogs_change#,
resetlogs_time as resetlogs_time,
bytes as bytes
FROM v$datafile_header
ORDER BY checkpoint_change#;
SELECT status,
checkpoint_change#,
checkpoint_time as checkpoint_time,
count(*)
FROM v$datafile_header
GROUP BY status, checkpoint_change#, checkpoint_time
ORDER BY status, checkpoint_change#, checkpoint_time;
SELECT dd.FILE#,
dd.NAME,
dd.STATUS,
dd.checkpoint_change# dfile_chkp_change,
dh.checkpoint_change# dfile_hed_chkp_change,
dh.recover,
dh.fuzzy
FROM v$datafile dd,
v$datafile_header dh
WHERE dd.FILE#=dh.FILE#;
SELECT name,
file#,
status,
enabled,
creation_change#,
creation_time as creation_time,
checkpoint_change# as checkpoint_change#,
checkpoint_time as checkpoint_time,
offline_change# as offline_change#,
online_change# as online_change#,
online_time as online_time,
bytes as bytes
FROM v$datafile
ORDER BY checkpoint_change#;
select * from v$database_incarnation;
SELECT hxfil file_num,
hxfnm file_name,
fhtyp type,
hxerr validity,
fhscn scn,
fhtnm tablespace_name,
fhsta status ,
fhthr thread,
fhrba_seq sequence
FROM x$kcvfh
order by scn;
SELECT fhthr thread,
fhrba_seq sequence,
fhscn scn,
fhsta status,
count(*)
FROM x$kcvfh
group by fhthr,fhrba_seq,fhscn,fhsta;
SELECT hxfil file_num,
fhscn scn,
fhsta status ,
fhthr thread,
fhrba_seq sequence
FROM x$kcvfh
order by scn;
select
con_id,
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 con_id,status, resetlogs_change#, resetlogs_time, checkpoint_change#, checkpoint_time
order by status, checkpoint_change#, checkpoint_time ;
select con_id,FHTNM TABLESPACE_NAME,HXFIL File_num,HXFNM File_name,FHTYP Type,HXERR Validity,FHSCN SCN, FHSTA status ,FHRBA_SEQ Sequence from X$KCVFH order by 1;
SELECT nvl(c.name,'ROOT$') name , d.con_id, file#, status, checkpoint_change#, checkpoint_time, resetlogs_change#, resetlogs_time, fuzzy
FROM v$datafile_header d, v$pdbs c
where d.con_id=c.con_id(+)
order by con_id, file#;
SELECT group#,
thread#,
sequence#,
members,
archived,
status,
first_change# as first_change#
FROM v$log;
SELECT group#,
member
FROM v$logfile;
SELECT a.recid,
a.thread#,
a.sequence#,
a.name,
a.first_change# as first_change#,
a.NEXT_CHANGE# as next_change# ,
a.archived,
a.deleted,
a.completion_time as completed
FROM v$archived_log a, v$log l
WHERE a.thread# = l.thread#
AND a.sequence# = l.sequence#;
SELECT a.recid,
a.thread#,
a.sequence#,
a.name,
a.first_change# as first_change#,
a.NEXT_CHANGE# as next_change# ,
a.archived,
a.deleted,
a.completion_tim as completed
FROM v$archived_log a, v$recovery_log l
WHERE a.thread# = l.thread#
AND a.sequence# = l.sequence#;
SELECT recid,
thread#,
sequence#,
name,
first_change# as first_change#,
NEXT_CHANGE# as next_change# ,
archived,
deleted,
completion_time as completed,
blocks,
block_size
FROM v$archived_log;
spool off
exit
---------------------------------------------------------------------------
another !!!!!!
SET PAGESIZE 20000
SET LINESIZE 1000
SET TRIMSPOOL ON
SET PAUSE OFF
SET SERVEROUTPUT ON
SET FEEDBACK ON
SET ECHO ON
SET NUMFORMAT 999999999999999999999999
ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MM-YYYY HH24:MI:SS';
set termout off feedback off
set markup html on head "<title>SQL*Plus Output &_user@&_connect_identifier &_date</title> -
<!-- Generated by html.sql on &_DATE for user &_USER.@&_CONNECT_IDENTIFIER --> -
<style> -
html { -
font-family: consolas, monospace; -
font-size: 9pt; -
background-color: #dce1e9; -
} -
table, td, th { -
vertical-align: top; -
border: 1px solid #808090; -
background: white; -
padding: .5em .6em; -
} -
table { -
border-collapse: collapse; -
margin-top: 1.2em; /* space above table itself */ -
margin-bottom: 1.2em; -
border-width: 3px; -
margin-bottom: 1em; -
} -
td { -
margin: .2em; -
font-size: 80%; -
} -
th { -
background: #f0f4fd; -
font-weight: bold; -
font-size: 95%; -
margin: .2em; -
padding-bottom: .4em; -
} -
</style>" -
body "" -
table "align='center' summary='Script output'" -
spool on entmap on preformat off
spool datafile.html
SET MARKUP HTML ENTMAP OFF
PROMPT <H2><center><b>Datafile Report </b></center></H2>
SET MARKUP HTML ENTMAP ON
SET VERIF off feedback off
ARCHIVE LOG LIST;
SELECT * FROM v$instance;
SELECT dbid,
name,
created created,
open_mode,
log_mode,
checkpoint_change# as checkpoint_change#,
controlfile_type,
controlfile_change# as controlfile_change#,
controlfile_time controlfile_time,
resetlogs_change# as resetlogs_change#,
resetlogs_time resetlogs_time
FROM v$database;
SELECT * FROM v$recover_file;
SELECT * FROM v$recovery_log;
SELECT f.name, b.status, b.change#, b.time FROM v$backup b,
v$datafile f
WHERE b.file# = f.file#
AND b.status = 'ACTIVE';
SELECT name,
file#,
status,
enabled,
creation_change#,
creation_time as creation_time,
checkpoint_change# as checkpoint_change#,
checkpoint_time as checkpoint_time,
offline_change# as offline_change#,
online_change# as online_change#,
online_time as online_time,
bytes as bytes
FROM v$datafile
ORDER BY checkpoint_change#;
SELECT name,
file#,
status,
error,
creation_change#,
creation_time as creation_time,
checkpoint_change# as checkpoint_change#,
checkpoint_time as checkpoint_time,
resetlogs_change# as resetlogs_change#,
resetlogs_time as resetlogs_time,
bytes as bytes
FROM v$datafile_header
ORDER BY checkpoint_change#;
SELECT status,
checkpoint_change#,
checkpoint_time as checkpoint_time,
count(*)
FROM v$datafile_header
GROUP BY status, checkpoint_change#, checkpoint_time
ORDER BY status, checkpoint_change#, checkpoint_time;
SELECT dd.FILE#,
dd.NAME,
dd.STATUS,
dd.checkpoint_change# dfile_chkp_change,
dh.checkpoint_change# dfile_hed_chkp_change,
dh.recover,
dh.fuzzy
FROM v$datafile dd,
v$datafile_header dh
WHERE dd.FILE#=dh.FILE#;
SELECT name,
file#,
status,
enabled,
creation_change#,
creation_time as creation_time,
checkpoint_change# as checkpoint_change#,
checkpoint_time as checkpoint_time,
offline_change# as offline_change#,
online_change# as online_change#,
online_time as online_time,
bytes as bytes
FROM v$datafile
ORDER BY checkpoint_change#;
select * from v$database_incarnation;
SELECT hxfil file_num,
hxfnm file_name,
fhtyp type,
hxerr validity,
fhscn scn,
fhtnm tablespace_name,
fhsta status ,
fhthr thread,
fhrba_seq sequence
FROM x$kcvfh
order by scn;
SELECT fhthr thread,
fhrba_seq sequence,
fhscn scn,
fhsta status,
count(*)
FROM x$kcvfh
group by fhthr,fhrba_seq,fhscn,fhsta;
SELECT hxfil file_num,
fhscn scn,
fhsta status ,
fhthr thread,
fhrba_seq sequence
FROM x$kcvfh
order by scn;
select
con_id,
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 con_id,status, resetlogs_change#, resetlogs_time, checkpoint_change#, checkpoint_time
order by status, checkpoint_change#, checkpoint_time ;
select con_id,FHTNM TABLESPACE_NAME,HXFIL File_num,HXFNM File_name,FHTYP Type,HXERR Validity,FHSCN SCN, FHSTA status ,FHRBA_SEQ Sequence from X$KCVFH order by 1;
SELECT nvl(c.name,'ROOT$') name , d.con_id, file#, status, checkpoint_change#, checkpoint_time, resetlogs_change#, resetlogs_time, fuzzy
FROM v$datafile_header d, v$pdbs c
where d.con_id=c.con_id(+)
order by con_id, file#;
---- validate sequence no
set linesize 400
col FILE_NAME for a90
col TABLESPACE_NAME for a20
select con_id, TABLESPACE_NAME, File_num,File_name,Type,Validity ,SCN,status,Sequence,datafilerank from (select con_id,FHTNM TABLESPACE_NAME,HXFIL File_num,HXFNM File_name,FHTYP Type,HXERR Validity,FHSCN SCN, FHSTA status ,FHRBA_SEQ Sequence
,dense_rank() over (partition by con_id order by FHRBA_SEQ) as datafilerank from X$KCVFH order by 1)
where 1=1
--and datafilerank!=1
;
CON_ID TABLESPACE_NAME FILE_NUM FILE_NAME TYPE VALIDITY SCN STATUS SEQUENCE DATAFILERANK
---------- -------------------- ---------- ------------------------------------------------------------------------------------------ ---------- ---------- -------------------- ---------- ---------- ------------
1 SYSTEM 1 +DATA/VIHCDBD8/DATAFILE/system.1414.1133670265 3 0 348007923 40964 2096 1
1 SYSAUX 3 +DATA/VIHCDBD8/DATAFILE/sysaux.1381.1133670265 3 0 348007923 32772 2096 1
1 UNDOTBS1 4 +DATA/VIHCDBD8/DATAFILE/undotbs1.4555.1133670265 3 0 348007923 32772 2096 1
1 USERS 7 +DATA/VIHCDBD8/DATAFILE/users.1420.1133670265 3 0 348007923 32772 2096 1
1 UNDOTBS2 13 +DATA/VIHCDBD8/DATAFILE/undotbs2.1314.1133670265 3 0 348007923 32772 2096 1
---- validate scn no
set linesize 400
col FILE_NAME for a90
col TABLESPACE_NAME for a20
select con_id, TABLESPACE_NAME, File_num,File_name,Type,Validity ,SCN,status,Sequence,datafilerank from (select con_id,FHTNM TABLESPACE_NAME,HXFIL File_num,HXFNM File_name,FHTYP Type,HXERR Validity,FHSCN SCN, FHSTA status ,FHRBA_SEQ Sequence
,dense_rank() over (partition by con_id order by FHSCN) as datafilerank from X$KCVFH order by 1)
where 1=1
--and datafilerank!=1
;
CON_ID TABLESPACE_NAME FILE_NUM FILE_NAME TYPE VALIDITY SCN STATUS SEQUENCE DATAFILERANK
---------- -------------------- ---------- ------------------------------------------------------------------------------------------ ---------- ---------- -------------------- ---------- ---------- ------------
1 SYSTEM 1 +DATA/VIHCDBD8/DATAFILE/system.1414.1133670265 3 0 348007923 40964 2096 1
1 SYSAUX 3 +DATA/VIHCDBD8/DATAFILE/sysaux.1381.1133670265 3 0 348007923 32772 2096 1
1 UNDOTBS1 4 +DATA/VIHCDBD8/DATAFILE/undotbs1.4555.1133670265 3 0 348007923 32772 2096 1
SELECT group#,
thread#,
sequence#,
members,
archived,
status,
first_change# as first_change#
FROM v$log;
SELECT group#,
member
FROM v$logfile;
SELECT a.recid,
a.thread#,
a.sequence#,
a.name,
a.first_change# as first_change#,
a.NEXT_CHANGE# as next_change# ,
a.archived,
a.deleted,
a.completion_time as completed
FROM v$archived_log a, v$log l
WHERE a.thread# = l.thread#
AND a.sequence# = l.sequence#;
SELECT a.recid,
a.thread#,
a.sequence#,
a.name,
a.first_change# as first_change#,
a.NEXT_CHANGE# as next_change# ,
a.archived,
a.deleted,
a.completion_tim as completed
FROM v$archived_log a, v$recovery_log l
WHERE a.thread# = l.thread#
AND a.sequence# = l.sequence#;
SELECT recid,
thread#,
sequence#,
name,
first_change# as first_change#,
NEXT_CHANGE# as next_change# ,
archived,
deleted,
completion_time as completed,
blocks,
block_size
FROM v$archived_log;
spool off
set markup html off spool off
set termout on feedback on
exit
end ---
-------------------------------------
GV$DATAFILE_HEADER';
VIEW_DEFINITION
--------------------------------------------------------------------------------
select inst_id,
hxfil,
decode(hxons, 0, 'OFFLINE', 'ONLINE'),
decode(hxerr,0,NULL,
1,'FILE MISSING',
2,'OFFLINE NORMAL',
3,'NOT VERIFIED',
4,'FILE NOT FOUND',
5,'CANNOT OPEN FILE',
6,'CANNOT READ HEADER',
7,'CORRUPT HEADER',
8,'WRONG FILE TYPE',
9,'WRONG DATABASE',
10,'WRONG FILE NUMBER',
11,'WRONG FILE CREATE',
12,'WRONG FILE CREATE',
16,'DELAYED OPEN',
14,'WRONG RESETLOGS',
15,'OLD CONTROLFILE',
'UNKNOWN ERROR'),
hxver,
decode(hxnrcv, 0, 'NO', 1, 'YES', NULL),
decode(hxifz, 0, 'NO', 1, 'YES', NULL),
to_number(fhcrs),
to_date(fhcrt, 'MM/DD/RR HH24:MI:SS', 'NLS_CALENDAR=Gregorian'),
fhtnm,
fhtsn,
fhrfn,
to_number(fhrls),
to_date(fhrlc, 'MM/DD/RR HH24:MI:SS', 'NLS_CALENDAR=Gregorian'),
to_number(fhscn),
to_date(fhtim, 'MM/DD/RR HH24:MI:SS', 'NLS_CALENDAR=Gregorian'),
fhcpc,
fhfsz * fhbsz,
fhfsz,
hxfnm,
decode(hxlmdba, 0, NULL, hxlmdba),
decode(hxlmld_scn, to_number('0'), NULL, hxlmld_scn),
decode(hxuopc_scn, 0, NULL, hxuopc_scn),
con_id
from x$kcvfh====
with file status ----
select inst_id,
hxfil,
decode(hxons, 0, 'OFFLINE', 'ONLINE'),
decode(hxerr,0,NULL,
1,'FILE MISSING',
2,'OFFLINE NORMAL',
3,'NOT VERIFIED',
4,'FILE NOT FOUND',
5,'CANNOT OPEN FILE',
6,'CANNOT READ HEADER',
7,'CORRUPT HEADER',
8,'WRONG FILE TYPE',
9,'WRONG DATABASE',
10,'WRONG FILE NUMBER',
11,'WRONG FILE CREATE',
12,'WRONG FILE CREATE',
16,'DELAYED OPEN',
14,'WRONG RESETLOGS',
15,'OLD CONTROLFILE',
'UNKNOWN ERROR'),
hxver,
decode(hxnrcv, 0, 'NO', 1, 'YES', NULL),
decode(hxifz, 0, 'NO', 1, 'YES', NULL),
to_number(fhcrs),
to_date(fhcrt, 'MM/DD/RR HH24:MI:SS', 'NLS_CALENDAR=Gregorian'),
fhtnm,
fhtsn,
fhrfn,
to_number(fhrls),
to_date(fhrlc, 'MM/DD/RR HH24:MI:SS', 'NLS_CALENDAR=Gregorian'),
to_number(fhscn),
to_date(fhtim, 'MM/DD/RR HH24:MI:SS', 'NLS_CALENDAR=Gregorian'),
fhcpc,
fhfsz * fhbsz,
fhfsz,
hxfnm,
decode(hxlmdba, 0, NULL, hxlmdba),
decode(hxlmld_scn, to_number('0'), NULL, hxlmld_scn),
decode(hxuopc_scn, 0, NULL, hxuopc_scn),
decode(fhsta ,0,'consistent- mounted',
1,'database backup mode(DB needs more recovery)',
4,'online fuzzy',
64 ,'fuzzy needs recovery',
8192,'internal',
8193,'database backup mode(datafile 1 only)',
8196 , 'online fuzzy'
) "file-Status",
con_id
from x$kcvfh
from matalink !!!!!!!!!!
select max(sequence#) from v$archived_log L, v$database D
where L.resetlogs_change# = D.resetlogs_change# and
thread#=1;
MAX(SEQUENCE#)
--------------
25
SQL> select max(sequence#) from v$archived_log L, v$database D
where L.resetlogs_change# = D.resetlogs_change# and
thread#=2;
MAX(SEQUENCE#)
--------------
13
b. Next is to find the thread with lowest NEXT_CHANGE# scn.
SQL> select sequence#, thread#, first_change#, next_change#
from v$archived_log L, v$database D
where L.resetlogs_change# = D.resetlogs_change# and
sequence# in (13,25);
SEQUENCE# THREAD# FIRST_CHANGE# NEXT_CHANGE#
-------------------- -------------- ------------------------- -------------------------
25 1 1744432 1744802
13 2 1744429 1744805
SQL> select sequence#, thread#, first_change#, next_change#
from v$backup_redolog
where sequence# in (13,25);
SEQUENCE# THREAD# FIRST_CHANGE# NEXT_CHANGE#
-------------------- -------------- ------------------------- -------------------------
25 1 1744432 1744802 <<< check lower NEXT_CHANGE# imp !!!
13 2 1744429 1744805
In this case the next_change# SCN in thread 1 sequence 25 is lower than sequence 13 thread 2. In a RAC environment, we use the lower to ensure we have the redo required from BOTH threads. In other words, we use the lower (thread# 1) to ensure that ALL scn (s) in thread #1 exist in the available sequence for thread #2.
So we will set sequence 26 for thread 1 for RMAN 'until sequence' recovery, because RMAN stops the recovery before applying the indicated sequence. Log sequence for recovery needs always be sequence+1 to end at +1 after applying the prior sequence. I.e.:
SET UNTIL SEQUENCE 26 THREAD 1;
======
to find scn no
select thread#, max(sequence#) sequence# , max(first_change#) first_change# , max(next_change# ) next_change# from v$archived_log L, v$database D
where L.resetlogs_change# = D.resetlogs_change#
group by thread#
order by 4 desc;
THREAD# SEQUENCE# FIRST_CHANGE# NEXT_CHANGE#
---------- ---------- ------------- ------------
2 1063 7486098565 7487519227 <<<< recovery till 7487519227+1
1 1674 7486028712 7486098568
----
alter session set nls_date_format='DD-MON-YYYY HH24:MI:SS' ;
set linesize 300 pagesize 300
select con_id,count(*),fhsta from x$kcvfh group by con_id,fhsta order by 1;
select con_id,count(*),fhrba_seq from x$kcvfh group by con_id,fhrba_seq order by 1;
select con_id,count(*),fhscn from x$kcvfh group by con_id,fhscn order by 1;
select con_id,count(*),fhafs from x$kcvfh group by fhafs,con_id order by 1;
select con_id,min(FHSCN) "LOW FILEHDR SCN" , max(FHSCN) "MAX FILEHDR SCN", max(FHAFS) "Min PITR ABSSCN" from X$KCVFH group by con_id order by 1;
select con_id,fhdbn,fhdbi,hxfil,fhsta,fhscn,fhafs,fhrba_seq,fhtnm tbs_name from x$kcvfh order by 1;
set linesize 300 pagesize 300
set numf 99999999999999999999999999999999
col ERROR for a20
select con_id,fuzzy, status, error, recover, checkpoint_change#, checkpoint_time, count(*) from v$datafile_header
group by con_id,fuzzy, status, error, recover, checkpoint_change#, checkpoint_time
order by 1;
set linesize 400
col NAME for a50
select con_id,hxfil file#, substr(hxfnm, 1, 50) name, fhscn checkpoint_change#, fhafs Absolute_Fuzzy_SCN, max(fhafs) over () Min_PIT_SCN
from x$kcvfh
where fhafs!=0 ;
col recover for foe a10
select con_id,fuzzy, status, error, recover, checkpoint_change#, checkpoint_time, count(*) from v$datafile_header
group by con_id,fuzzy, status, error, recover, checkpoint_change#, checkpoint_time
order by 1;
select con_id,fuzzy, status, error, recover, checkpoint_change#, checkpoint_time, count(*)
from v$datafile_header group by con_id,fuzzy, status, error, recover, checkpoint_change#, checkpoint_time
order by 1;
alter session set nls_date_format='DD-MON-YYYY HH24:MI:SS' ;
select max(checkpoint_time) max_checkpoint_time from (select max( checkpoint_time) checkpoint_time , count(*)
from v$datafile_header group by fuzzy, status, error, recover, checkpoint_change#, checkpoint_time );
/*
ALTER SESSION SET NLS_DATE_FORMAT='DD-MON-RR HH24:MI:SS';
SELECT THREAD#, SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG
WHERE '13-AUG-2024 02:00:02' BETWEEN FIRST_TIME AND NEXT_TIME;
*/
ALTER SESSION SET NLS_DATE_FORMAT='DD-MON-RR HH24:MI:SS';
SELECT THREAD#, SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG
WHERE (select max(checkpoint_time) max_checkpoint_time from (select max( checkpoint_time) checkpoint_time , count(*) from v$datafile_header
group by fuzzy, status, error, recover, checkpoint_change#, checkpoint_time )) BETWEEN FIRST_TIME AND NEXT_TIME;
=====
SQL> select min(FHSCN) "LOW FILEHDR SCN"
, max(FHSCN) "MAX FILEHDR SCN"
, max(FHAFS) "Min PITR ABSSCN"
from X$KCVFH ;LOW FILEHDR SCN MAX FILEHDR SCN Min PITR ABSSCN
---------------- ---------------- ----------------
2446300 2472049 0-- Example output explained:
--
-- "LOW FILEHDR SCN" - this is the SCN at which recovery process starts
-- "MAX FILEHDR SCN" - this is the SCN we must recover to to get all datafiles consistent
--
-- IF "Min PITR ABSSCN" != 0 AND > "MAX FILEHDR SCN"
-- THEN "Min PITR ABSSCN" is the SCN we must recover to to get all datafiles consistent
***************
set pagesize 20000 linesize 300 pause off serveroutput on feedback on echo on numformat 999999999999999
col name for a50
col member for a60
-- Spool recovery_info.txt
select min(fhrba_Seq), max(fhrba_Seq) from X$KCVFH;
select substr(name, 1, 50) name, status from v$datafile;
select substr(name,1,40) name , recover, fuzzy, checkpoint_change# from v$datafile_header;
select GROUP#,substr(member,1,60) member from v$logfile;
select * from v$recover_file;
select distinct status from v$backup;
select hxfil FILENUMBER, fhsta STATUS, fhscn SCN, FHAFS ABSSCN , fhrba_Seq SEQUENCE from x$kcvfh;
select distinct (fuzzy) from v$datafile_header;
-- spool off
-- exit;
****************************
set pagesize 20000
set linesize 180
set pause offset
set serveroutput on
set feedback on
set echo on
set numformat 999999999999999
alter session set nls_date_format = 'dd-mon-yyyy hh24:mi:ss';
archive log list;
select INSTANCE_NAME, STATUS from v$instance;
select database_role from v$database;
select name,dbid,controlfile_type,open_mode,log_mode,checkpoint_change#,archive_change# from v$database;
select name,dbid,current_scn,log_mode,open_mode from v$database;
select * from v$database_incarnation;
col name for a75
select * from v$restore_point;
select flashback_on from v$database;
select parallel from v$instance;
select protection_level from v$database;
select * from dba_streams_administrator;
select file#,name,status,checkpoint_change#,enabled from v$datafile;
select file#,name,status,enabled from v$tempfile;
select TS#,NAME,INCLUDED_IN_DATABASE_BACKUP,FLASHBACK_ON from v$tablespace order by TS#;
select * from v$recover_file;
select * from v$backup;
select * from v$log;
select * from v$logfile;
select sequence#, first_change#, first_time, status from v$archived_log;
select file#,name,recover,fuzzy,resetlogs_change#,checkpoint_change#,creation_change#,checkpoint_time,creation_time,RESETLOGS_TIME,status from v$datafile_header;
select status, to_char(checkpoint_change#), to_char(checkpoint_time, 'DD-MON-YYYY HH24:MI:SS') as checkpoint_time,
count(*) from v$datafile_header
group by status, checkpoint_change#, checkpoint_time order by status, checkpoint_change#, checkpoint_time;
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 min(FHSCN) "LOW FILEHDR SCN" , max(FHSCN) "MAX FILEHDR SCN", max(FHAFS) "Min PITR ABSSCN" from X$KCVFH ;
prompt con_id
select con_id,count(*),fhsta from x$kcvfh group by con_id,fhsta;
select con_id,count(*),fhrba_seq from x$kcvfh group by con_id,fhrba_seq;
select con_id,count(*),fhscn from x$kcvfh group by con_id,fhscn;
select con_id,count(*),fhafs from x$kcvfh group by con_id,fhafs;
select con_id,min(FHSCN) "LOW FILEHDR SCN" , max(FHSCN) "MAX FILEHDR SCN", max(FHAFS) "Min PITR ABSSCN" from X$KCVFH group by con_id;
select con_id,fhdbn,fhdbi,hxfil,fhsta,fhscn,fhafs,fhrba_seq,fhtnm tbs_name from x$kcvfh;
select name, status , sequence#, thread#,
TO_CHAR(first_change#, '999999999999999999') as first_change#,
TO_CHAR(next_change#, '999999999999999999') next_change#,
to_char(completion_time,'DD-MON-YYYY HH24:MI:SS') completion_time
from v$archived_log where (select min(checkpoint_change#) from v$datafile_header) between first_change# and next_change#
;
