Search This Blog

Total Pageviews

Tuesday, 26 January 2010

useful Oracle link

useful link

General DBA
Amit Bansal, Saurabh Sood, LeoSanil http://askdba.org/weblog
Arul Arogyanathan http://arulselvaraj.blogspot.com
Arup Nanda http://arup.blogspot.com
Bradley D. Brown http://bradleydbrown.blogspot.com
CERN Physics Services Support wiki https://twiki.cern.ch/twiki/bin/view/PSSGroup
Chen Shapira http://prodlife.wordpress.com
Connor McDonald http://www.oracledba.co.uk
Coskan Gundogar http://coskan.wordpress.com
Dominic Delmolino http://www.oraclemusings.com
Eygle Gai http://www.eygle.com
Feng Tai-fai http://www.dbanotes.net
Geert De Paep – DBA Village http://www.dba-village.com, http://geertdepaep.wordpress.com
Husnu Sensoy http://husnusensoy.wordpress.com
Iggy Fernandez http://iggyfernandez.wordpress.com, http://www.dbspecialists.com/blog
Jared Still http://jkstill.blogspot.com
Jason Arneil http://jarneil.wordpress.com
Jeff Hunter http://www.idevelopment.info/data/RSSFeeds/rss_iDevelopment_News.xml
Julien Gabel http://blog.thilelli.net
Laurent Schneider http://laurentschneider.com
Lutz Hartmann http://sysdba.wordpress.com
Martin Berger http://berxblog.blogspot.com
Martin Decker http://www.ora-solutions.net/web/
Mike Dietrich – Database Upgrade Development http://blogs.oracle.com/UPGRADE
Miracle Benelux http://www.miraclebenelux.nl
Mogens Nørgaard http://wedonotuse.blogspot.com
Paul Gallagher http://tardate.blogspot.com
Pythian Group Blog http://feeds2.feedburner.com/PythianGroupBlog
Ramasundaram Perumal http://perumal.org
Robyn http://adhdocddba.blogspot.com
R. Wang http://oraclepoint.com
Sandeep Redkar http://sandeepredkar.blogspot.com
So What Co-operative http://marist89.blogspot.com
Syed Jaffar Hussain http://jaffardba.blogspot.com
Tim Hall http://www.oracle-base.com/blog
Tom Kyte http://tkyte.blogspot.com Asktom http://asktom.oracle.com
Toon Koppelaars http://thehelsinkideclaration.blogspot.com, http://web.inter.nl.net/users/T.Koppelaars
Vitaliy Mogilevskiy http://www.dbatoolz.com
Werner Puschitz http://www.puschitz.com
Performance, Capacity Planning, and Internals
Alberto Dell’Era http://www.adellera.it/blog
Andy Rivenes http://www.appsdba.com/blog, http://www.appsdba.com
Anjo Kolk http://blog.miraclebenelux.nl
AshMasters http://ashmasters.com
Cary Millsap http://carymillsap.blogspot.com
Christian Antognini http://antognini.ch/feed
Christian Bilien http://christianbilien.wordpress.com
Christopher Lawson http://www.oraclemagician.com
Craig Shallahamer http://www.orapub.com
Confio Performance Blog http://support.confio.com/rss_feed.php
Danisment Gazi Unal http://www.ubtools.com, http://www.unal-bilisim.com, MRPP
Dennis Yurichev http://blogs.conus.info
Dion Cho http://dioncho.wordpress.com
Dominic Giles http://www.dominicgiles.com
Doug Burns http://oracledoug.com
Edward Whalen http://ewhalen.blogspot.com
Egor Starostin http://www.oracledba.ru/orasrp, http://oracledba.ru/blog
Fuyuncat http://www.hellodba.com/blog/rss.php
Gary Little http://super-user.org/blog
Gints Plivna http://www.gplivna.eu/papers_e.htm
Glenn Fawcett http://blogs.sun.com/glennf, http://glennfawcett.wordpress.com
Greg Rahn http://structureddata.org
Guy Harrison http://guyharrison.squarespace.com/blog, http://guyharrison.typepad.com
Hemant Chitale http://hemantoracledba.blogspot.com
Hotsos Happenings http://hotsoseducation.blogspot.com/feeds/posts/default
H. Tonguc Yilmaz http://tonguc.wordpress.com
James Koopmann http://www.jameskoopmann.com
James Morle http://jamesmorle.wordpress.com, http://www.scaleabilities.co.uk, http://www.scaleabilities.co.uk/index.php/Whitepapers
Jeremiah Wilton http://www.bluegecko.net, http://oradeblog.blogspot.com
John Brady http://databaseperformance.blogspot.com
Jonah Harris http://www.oracle-internals.com
Jonathan Lewis http://jonathanlewis.wordpress.com
Karen Morton http://karenmorton.blogspot.com
Kerry Osborne http://kerryosborne.oracle-guy.com
Krishna Manoharan http://dsstos.blogspot.com
Kurt Van Meerbeeck http://www.ora600.be, http://www.ora600.be/rss/blog
Kyle Hailey http://www.perfvision.com/tools.php, http://db-optimizer.blogspot.com, http://sites.google.com/site/embtdbo
Martin Widlake http://mwidlake.wordpress.com, http://www.ora600.org.uk
Miladin Modrakovic http://oraclue.wordpress.com
Monty Orme – Hotsos http://montyorme.blogspot.com
Neil Gunther http://perfdynamics.blogspot.com
Optimizer team http://optimizermagic.blogspot.com
Peter Stalder http://pstalder.blogspot.com
Randolf Geist http://oracle-randolf.blogspot.com/feeds/posts/default
Rick Minutella – Hotsos http://rickminutella.blogspot.com
Ric Van Dyke – Hotsos http://ricramblings.blogspot.com
Richard Foote http://richardfoote.wordpress.com
Riyaj Shamsudeen http://orainternals.wordpress.com, http://www.orainternals.com
Steve Adams http://www.ixora.com.au
Tanel Poder http://blog.tanelpoder.com
Wolfgang Breitling http://www.centrexcc.com
Yaping Chen http://yaping123.wordpress.com
Yong Huang http://yong321.freeshell.org/computer.html
RAC and Infra
Alejandro Vargas http://blogs.oracle.com/AlejandroVargas
Bhavin Hingu http://www.oracledba.org
Dan Norris http://www.dannorris.com
Fairlie Rego http://el-caro.blogspot.com
Jakub Wartak http://vnull.pcnet.com.pl/blog/?feed=rss2
Jeremy Schneider http://www.ardentperf.com
Julian Dyke http://www.juliandyke.com
Kevin Closson http://kevinclosson.wordpress.com
Oracle XPS The Netherlands on HA http://blogs.oracle.com/XPSONHA/xml/rss.xml
Rajeev Ramdas Thottathil http://dbastreet.com/blog
Ronny Egner http://blog.ronnyegner-consulting.de
Simon Thorpe http://blogs.oracle.com/simonthorpe
Steve Shaw http://www.sourceora.com
Wim Coekaerts http://blogs.oracle.com/wim
Security
Alexander Kornbrust http://blog.red-database-security.com/feed
David Litchfield http://www.davidlitchfield.com/blog/davidlitchfield.rss
Oracle Global Product Security Blog http://blogs.oracle.com/security/xml/rss.xml
Paul Wright http://www.oracleforensics.com/wordpress/index.php/feed
Pete Finnigan http://www.petefinnigan.com
Stephen Kost http://www.integrigy.com/oracle-security-blog/rss.xml
Enterprise Manager
Porus Homi Havewala http://enterprise-manager.blogspot.com
Enterprise Manager Widgets http://blogs.oracle.com/emwidgets
BI and Data Warehouse
Mark Rittman http://www.rittmanmead.com/blog
Tim Gorman http://www.evdbt.com
Kavin Mehta http://blogs.oracle.com/kavinmehta
Oracle BI Publisher Blog http://blogs.oracle.com/xmlpublisher
Development
Adrian Billington http://www.oracle-developer.net
AppsLab http://theappslab.com
Eddie Awad http://awads.net/wp
Steven Feuerstein http://feuerthoughts.blogspot.com
Michael McLaughlin http://blog.mclaughlinsoftware.com
E-Business Suite
Advait Deo http://advait.wordpress.com
E-Business Suite Blog http://feeds.feedburner.com/OracleE-BusinessSuiteTechnology
Online APPS DBA http://feeds.feedburner.com/OnlineAppsDba
PeopleSoft
David Kurtz http://blog.psftdba.com, http://blog.go-faster.co.uk
Knowledge Depot
PSOUG http://psoug.org/library.html
OTN RSS link http://www.oracle.com/rss/index.html
SQL Best Practices with humor http://www.roughsea.com/vids/SQL_Best_Practices.html
Akadia http://www.akadia.com/html/ora_scripts.html, http://www.akadia.com/html/publications.html
The Guerrilla Manifesto http://www.perfdynamics.com/Manifesto/gcaprules.html
Yong Huang’s Computer Page http://yong321.freeshell.org/computer.html
Basic SQL Join Semantics http://blog.mclaughlinsoftware.com/oracle-sql-programming/basic-sql-join-semantics
http://www.konagora.com http://www.konagora.com/tutorials/courseintro.php?id=1, http://www.konagora.com/tutorials/courseintro.php?id=2
Michelson Awards http://www.cmg.org/national/michelson-awards.html
DOAG http://mydoag.doag.org/rss/get_rss.xml
Community Sites
Oracle-l http://www.freelists.org/archives/oracle-l/feed.rss
OTN Forums http://forums.oracle.com
Oracle Mix https://mix.oracle.com
Posted by at No comments:

Friday, 22 January 2010

user which holds objects grants DBMS_METADATA.GET_GRANTED_DDL

The user hold any object grants.

which will generate you a list of DBMS_METADATA.GET_GRANTED_DDL statements for every user which holds objects grants .....

set pagesize 100
set linesize 200
set long 5000
select 'select DBMS_METADATA.GET_GRANTED_DDL(''OBJECT_GRANT'','||''''||a.username||''''||') from dual;'
from dba_users a, (select distinct(grantee) from dba_tab_privs) b
where a.username = b.grantee
/

then run the script
Posted by at No comments:

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;
-- 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#
;



















Posted by



at





No comments:
  

































        

      









Subscribe to:
Posts (Atom)







Oracle DBA



My photo








Essex, United Kingdom











anuj blog Archive