OraFAQ Forum: Backup & Recovery » Earliest SCN query question

Home » RDBMS Server » Backup & Recovery » Earliest SCN query question

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

Earliest SCN query question [message #222286]

Fri, 02 March 2007 12:03

Rustican
Messages: 51
Registered: July 2006

Member

If i wanted to find out what the earliest SCN (Status Change Number) was in the database, what do i query against? Thanks.

Report message to a moderator

Re: Earliest SCN query question [message #222301 is a reply to message #222286]

Fri, 02 March 2007 13:18

ebrian
Messages: 2794
Registered: April 2006

Senior Member

In SQL*Plus, you could issue:

SQL> select RESETLOGS_CHANGE# from v$database;

or in RMAN:

RMAN> list incarnation;

Report message to a moderator

Re: Earliest SCN query question [message #222581 is a reply to message #222301]

Mon, 05 March 2007 09:20

Rustican
Messages: 51
Registered: July 2006

Member

ebrian wrote on Fri, 02 March 2007 13:18

In SQL*Plus, you could issue:

SQL> select RESETLOGS_CHANGE# from v$database;

or in RMAN:

RMAN> list incarnation;

Gave it a try and got a ORA-08181: specified number is not a valid system change number.

How can i get the earliest SCN number that still valid in the database?

Report message to a moderator

Re: Earliest SCN query question [message #222585 is a reply to message #222581]

Mon, 05 March 2007 09:38

Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE

Senior Member

Hi,

Try this
SQL> select dbms_flashback.get_system_change_number from dual;

GET_SYSTEM_CHANGE_NUMBER
------------------------
15358914

regards
Taj

Report message to a moderator

Re: Earliest SCN query question [message #222621 is a reply to message #222585]

Mon, 05 March 2007 12:35

ebrian
Messages: 2794
Registered: April 2006

Senior Member

Mohammad Taj wrote on Mon, 05 March 2007 10:38

SQL> select dbms_flashback.get_system_change_number from dual;

GET_SYSTEM_CHANGE_NUMBER
------------------------
15358914

This will provide the MOST recent (latest) database SCN, however the OP requested the earliest SCN. It may be that the OP was unclear in his request.

Rustican wrote on Mon, 05 March 2007 10:20

Gave it a try and got a ORA-08181: specified number is not a valid system change number.

How can i get the earliest SCN number that still valid in the database?

Not sure what you mean by this. Where did you give it a try? Are you truly looking for the earliest SCN or the latest SCN? Do you want the SCN for a PITR?

Report message to a moderator

Re: Earliest SCN query question [message #222637 is a reply to message #222585]

Mon, 05 March 2007 14:11

Rustican
Messages: 51
Registered: July 2006

Member

Mohammad Taj wrote on Mon, 05 March 2007 09:38

Hi,

Try this
SQL> select dbms_flashback.get_system_change_number from dual;

GET_SYSTEM_CHANGE_NUMBER
------------------------
15358914

regards
Taj

Thanks, but the query you suggested gives the latest scn, latest as the current date.

My fault for not being clear enough. I'm looking for the earliest scn that's still valid in the database. So records that were entered in previously into the database.

Report message to a moderator

Re: Earliest SCN query question [message #222641 is a reply to message #222637]

Mon, 05 March 2007 14:32

Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

In that case, EBrian gave you the answer.

dbadmin@xxx > select dbid,name,resetlogs_change#,resetlogs_time from v$database;

      DBID NAME      RESETLOGS_CHANGE# RESETLOGS
---------- --------- ----------------- ---------
1164977494 xxx             2.2655E+10 20-FEB-07

RMAN> list incarnation;

List of Database Incarnations
DB Key  Inc Key DB Name  DB ID            CUR Reset SCN  Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1       1       xxx     1164977494       YES 22654699050 FEB 20 2007 16:37:50

Report message to a moderator

Re: Earliest SCN query question [message #222827 is a reply to message #222641]

Tue, 06 March 2007 09:38

Rustican
Messages: 51
Registered: July 2006

Member

Mahesh Rajendran wrote on Mon, 05 March 2007 14:32

In that case, EBrian gave you the answer.

dbadmin@xxx > select dbid,name,resetlogs_change#,resetlogs_time from v$database;

      DBID NAME      RESETLOGS_CHANGE# RESETLOGS
---------- --------- ----------------- ---------
1164977494 xxx             2.2655E+10 20-FEB-07

RMAN> list incarnation;

List of Database Incarnations
DB Key  Inc Key DB Name  DB ID            CUR Reset SCN  Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1       1       xxx     1164977494       YES 22654699050 FEB 20 2007 16:37:50

Thanks for the help. I'm trying to write a PL/SQL procedure to pull the last SCN number using what you showed above. I'm getting the following errors though:

ORA-00942: table or view does not exist
ORA-06512: at "FLEET_MOD.TEST", line 32
ORA-06512: at line 8

Below is my procedure:
CREATE OR REPLACE PROCEDURE TEST AS

c_tstcd NUMBER;
sql_selTestcd VARCHAR2(350) :=
'SELECT RESETLOGS_CHANGE# ' ||
'FROM v$database ';

BEGIN
EXECUTE IMMEDIATE sql_selTestcd INTO c_tstcd;
DBMS_OUTPUT.PUT_LINE(c_tstcd);
END;

Report message to a moderator

Re: Earliest SCN query question [message #222832 is a reply to message #222827]

Tue, 06 March 2007 09:43

Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

The user needs a select privilege.

sys@9i > grant select on v_$database to scott;

Grant succeeded.

sys@9i > connect scott/tiger
Connected.
sys@9i > CREATE OR REPLACE PROCEDURE TEST AS

c_tstcd NUMBER;
sql_selTestcd VARCHAR2(350) :=
'SELECT RESETLOGS_CHANGE# ' ||
'FROM v$database ';

BEGIN
EXECUTE IMMEDIATE sql_selTestcd INTO c_tstcd;
DBMS_OUTPUT.PUT_LINE(c_tstcd);
END;  2    3    4    5    6    7    8    9   10   11
 12  /

Procedure created.
sys@9i > set serveroutput  on
sys@9i > exec test;
686670

PL/SQL procedure successfully completed.

Report message to a moderator

Previous Topic:	Online backup on standby database
Next Topic:	flash recovery area on nfs share?

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Jan 11 03:38:20 CST 2025