Home » RDBMS Server » Server Administration » tool to extract scn from datafile header
tool to extract scn from datafile header [message #266030] Sun, 09 September 2007 03:31 Go to next message
orcl dba
Messages: 68
Registered: August 2005
Member
Does anyone know of any tool that can extract an scn number from the datafiles headers? I want to know if all my datafiles have consistent scn numbers.
I am using oracle 10.2.0.2

[Updated on: Sun, 09 September 2007 03:41]

Report message to a moderator

Re: tool to extract scn from datafile header [message #266031 is a reply to message #266030] Sun, 09 September 2007 03:36 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

SQL> select current_scn from v$database;

CURRENT_SCN
-----------
     445716


Are you talking about system change number (SCN)

Regards
Taj
Re: tool to extract scn from datafile header [message #266032 is a reply to message #266031] Sun, 09 September 2007 03:42 Go to previous messageGo to next message
orcl dba
Messages: 68
Registered: August 2005
Member
I want to know the scn of individual datafiles. I want to make sure they all have the same scn number.
Re: tool to extract scn from datafile header [message #266035 is a reply to message #266031] Sun, 09 September 2007 04:16 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> desc v$DATAFILE_HEADER
 Name                             Null?    Type
 -------------------------------- -------- ----------------------
 FILE#                                     NUMBER
 STATUS                                    VARCHAR2(7 CHAR)
 ERROR                                     VARCHAR2(18 CHAR)
 FORMAT                                    NUMBER
 RECOVER                                   VARCHAR2(3 CHAR)
 FUZZY                                     VARCHAR2(3 CHAR)
 CREATION_CHANGE#                          NUMBER
 CREATION_TIME                             DATE
 TABLESPACE_NAME                           VARCHAR2(30)
 TS#                                       NUMBER
 RFILE#                                    NUMBER
 RESETLOGS_CHANGE#                         NUMBER
 RESETLOGS_TIME                            DATE
 CHECKPOINT_CHANGE#                        NUMBER
 CHECKPOINT_TIME                           DATE
 CHECKPOINT_COUNT                          NUMBER
 BYTES                                     NUMBER
 BLOCKS                                    NUMBER
 NAME                                      VARCHAR2(513)
 SPACE_HEADER                              VARCHAR2(40 CHAR)
 LAST_DEALLOC_SCN                          VARCHAR2(16 CHAR)

Regards
Michel
Re: tool to extract scn from datafile header [message #266039 is a reply to message #266035] Sun, 09 September 2007 04:46 Go to previous messageGo to next message
orcl dba
Messages: 68
Registered: August 2005
Member
But there is no column indicating the scn number in the v$datafile_header view
Re: tool to extract scn from datafile header [message #266044 is a reply to message #266030] Sun, 09 September 2007 05:07 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

Adding another,
Select dbms_flashback.get_system_change_number from dual;
Re: tool to extract scn from datafile header [message #266045 is a reply to message #266039] Sun, 09 September 2007 05:11 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
But there is no column indicating the scn number in the v$datafile_header view

What is CHECKPOINT_CHANGE#?
Or what do you call SCN for datafile header?

Regards
Michel
Re: tool to extract scn from datafile header [message #266047 is a reply to message #266030] Sun, 09 September 2007 05:12 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

I think he wanted the mean SCN of database.
Re: tool to extract scn from datafile header [message #266055 is a reply to message #266047] Sun, 09 September 2007 06:29 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Arju wrote on Sun, 09 September 2007 12:12
I think he wanted the mean SCN of database.

I don't think so as he posted:
orcl dba wrote on Sun, 09 September 2007 10:42
I want to know the scn of individual datafiles. I want to make sure they all have the same scn number.

Now we have to know he means with "scn of individual datafiles".

Regards
Michel

Re: tool to extract scn from datafile header [message #266086 is a reply to message #266055] Sun, 09 September 2007 10:31 Go to previous message
mson77
Messages: 208
Registered: August 2007
Location: Brazil
Senior Member
Hello orcl_dba,

SCN = SYSTEM change number
and... system means system... not tablespace/session/... neither datafile.

checkpoint (alter system checkpoint) ==> means checkpoint of the whole database... locks db until finished the checkpoint.
Quote:
Oracle Checkpoint

A checkpoint performs the following three operations:

1. Every dirty block in the buffer cache is written to the data files. That is, it synchronizes the datablocks in the buffer cache with the datafiles on disk.
It's the DBWR that writes all modified databaseblocks back to the datafiles.
2. The latest SCN is written (updated) into the datafile header.
3. The latest SCN is also written to the controlfiles.

The update of the datafile headers and the control files is done by the LGWR(CKPT if CKPT is enabled). As of version 8.0, CKPT is enabled by default.



Maybe are you talking about CKPT as below??
Quote:
Checkpoint Process (CKPT)

When a checkpoint occurs, Oracle must update the headers of all datafiles to record the details of the checkpoint. This is done by the CKPT process. The CKPT process does not write blocks to disk; DBWn always performs that work.

The statistic DBWR checkpoints displayed by the System_Statistics monitor in Enterprise Manager indicates the number of checkpoint requests completed.



Regarding Michel Cadot comment... what do you mean about "SCN of individual datafiles"?


Regards,


mson77
Previous Topic: Listener error
Next Topic: Can I install Oracle DB 10g on HP UX PA-RISC without XWindow?
Goto Forum:
  


Current Time: Thu Dec 08 18:41:01 CST 2016

Total time taken to generate the page: 0.18611 seconds