OraFAQ Forum: Server Administration » tool to extract scn from datafile header

Home » RDBMS Server » Server Administration » tool to extract scn from datafile header

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

tool to extract scn from datafile header [message #266030]

Sun, 09 September 2007 03:31

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

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

Report message to a moderator

Re: tool to extract scn from datafile header [message #266032 is a reply to message #266031]

Sun, 09 September 2007 03:42

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.

Report message to a moderator

Re: tool to extract scn from datafile header [message #266035 is a reply to message #266031]

Sun, 09 September 2007 04:16

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Re: tool to extract scn from datafile header [message #266039 is a reply to message #266035]

Sun, 09 September 2007 04:46

orcl dba
Messages: 68
Registered: August 2005

Member

But there is no column indicating the scn number in the v$datafile_header view

Report message to a moderator

Re: tool to extract scn from datafile header [message #266044 is a reply to message #266030]

Sun, 09 September 2007 05:07

Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...

Senior Member

Adding another,

Select dbms_flashback.get_system_change_number from dual;

Report message to a moderator

Re: tool to extract scn from datafile header [message #266045 is a reply to message #266039]

Sun, 09 September 2007 05:11

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Re: tool to extract scn from datafile header [message #266047 is a reply to message #266030]

Sun, 09 September 2007 05:12

Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...

Senior Member

I think he wanted the mean SCN of database.

Report message to a moderator

Re: tool to extract scn from datafile header [message #266055 is a reply to message #266047]

Sun, 09 September 2007 06:29

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Re: tool to extract scn from datafile header [message #266086 is a reply to message #266055]

Sun, 09 September 2007 10:31

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

Report message to a moderator

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

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Dec 02 07:37:35 CST 2024