Home » RDBMS Server » Backup & Recovery » Earliest SCN query question
Earliest SCN query question [message #222286] Fri, 02 March 2007 12:03 Go to next message
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.
Re: Earliest SCN query question [message #222301 is a reply to message #222286] Fri, 02 March 2007 13:18 Go to previous messageGo to next message
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;
Re: Earliest SCN query question [message #222581 is a reply to message #222301] Mon, 05 March 2007 09:20 Go to previous messageGo to next message
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?
Re: Earliest SCN query question [message #222585 is a reply to message #222581] Mon, 05 March 2007 09:38 Go to previous messageGo to next message
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
Re: Earliest SCN query question [message #222621 is a reply to message #222585] Mon, 05 March 2007 12:35 Go to previous messageGo to next message
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?
Re: Earliest SCN query question [message #222637 is a reply to message #222585] Mon, 05 March 2007 14:11 Go to previous messageGo to next message
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.
Re: Earliest SCN query question [message #222641 is a reply to message #222637] Mon, 05 March 2007 14:32 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
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
Re: Earliest SCN query question [message #222827 is a reply to message #222641] Tue, 06 March 2007 09:38 Go to previous messageGo to next message
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;
Re: Earliest SCN query question [message #222832 is a reply to message #222827] Tue, 06 March 2007 09:43 Go to previous message
Mahesh Rajendran
Messages: 10672
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.
Previous Topic: Online backup on standby database
Next Topic: flash recovery area on nfs share?
Goto Forum:
  


Current Time: Tue Dec 06 00:31:24 CST 2016

Total time taken to generate the page: 0.05344 seconds