Home » RDBMS Server » Backup & Recovery » scn in oracle
scn in oracle [message #222792] Tue, 06 March 2007 06:49 Go to next message
p.bhaskar
Messages: 33
Registered: February 2007
Location: kolkata
Member
pls tell me daetails of scn number and what is the purpose of using of this number in flashback query
Re: scn in oracle [message #222825 is a reply to message #222792] Tue, 06 March 2007 09:33 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
The documentation Querying Data at a Point in Time (Flashback Query) explains the purpose.
Re: scn in oracle [message #223192 is a reply to message #222825] Wed, 07 March 2007 23:25 Go to previous messageGo to next message
babuknb
Messages: 1734
Registered: December 2005
Location: NJ
Senior Member

hi,

I know flashback query using timestamp,but i don't know scn concept. can you give me some example.?

Babu
Re: scn in oracle [message #223193 is a reply to message #222792] Wed, 07 March 2007 23:30 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
Whnen all else fails you could tr reading the fine manual(s) found at http://tahiti.oracle.com

http://download-west.oracle.com/docs/cd/B19306_01/server.102/b14220/transact.htm#sthref633

When a transaction is committed, the following occurs:

The internal transaction table for the associated undo tablespace records that the transaction has committed, and the corresponding unique system change number (SCN) of the transaction is assigned and recorded in the table.

The log writer process (LGWR) writes redo log entries in the SGA's redo log buffers to the redo log file. It also writes the transaction's SCN to the redo log file. This atomic event constitutes the commit of the transaction.

Oracle releases locks held on rows and tables.

Oracle marks the transaction complete.

Re: scn in oracle [message #223195 is a reply to message #223193] Wed, 07 March 2007 23:53 Go to previous messageGo to next message
babuknb
Messages: 1734
Registered: December 2005
Location: NJ
Senior Member

Hi,

Thanks for your response. see, the scn Transaction Management

is ok. I need example. like i have one table

>>create table emp (empno int,ename varchar(25))

In this table five records. like 101 to 105 in empno column.

Here, Yesterday or before one hour (i have bo backup) i have delete

one record like 105. I need to reterive this records using scn.

(Don't use timestamp)Give me example..

Babu
Re: scn in oracle [message #223199 is a reply to message #222792] Wed, 07 March 2007 23:59 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
LOGMINER is one way to identify the desired SCN.
Re: scn in oracle [message #223203 is a reply to message #223195] Thu, 08 March 2007 00:17 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

Hi,
Try this
SQL> create table SCN_TEST (no number, name varchar2(20));

Table created.

SQL> insert into scn_test values (1,'a');

1 row created.

SQL> insert into scn_test values (2,'b');

1 row created.

SQL> insert into scn_test values (3,'c');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from scn_test;

        NO NAME
---------- --------------------
         1 a
         2 b
         3 c

SQL> show parameter undo_retention

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_retention                       integer     22719
SQL> select dbms_flashback.get_system_change_number from dual;

GET_SYSTEM_CHANGE_NUMBER
------------------------
                15893109

SQL> delete from scn_test where no = 2;

1 row deleted.

SQL> commit;

Commit complete.

SQL> select * from scn_test AS OF SCN 15893109 where no = 2;

        NO NAME
---------- --------------------
         2 b

SQL> insert into scn_test ( select * from scn_test AS OF SCN 15893109
  2                         where no = 2);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from scn_test;

        NO NAME
---------- --------------------
         1 a
         3 c
         2 b

SQL>


Note :

Read before using FLASHBACK feature.
http://download-uk.oracle.com/docs/cd/B14117_01/appdev.101/b10795/adfns_fl.htm#1009344




regards
Taj

[Updated on: Thu, 08 March 2007 00:21]

Report message to a moderator

Re: scn in oracle [message #223294 is a reply to message #222825] Thu, 08 March 2007 06:15 Go to previous messageGo to next message
p.bhaskar
Messages: 33
Registered: February 2007
Location: kolkata
Member
Dear Sir,
i have undo_retention 10800.Pls tell me to which
time period i can revert data?
Re: scn in oracle [message #223303 is a reply to message #223294] Thu, 08 March 2007 07:15 Go to previous messageGo to next message
babuknb
Messages: 1734
Registered: December 2005
Location: NJ
Senior Member



I need just one day before. (1440)

Babu
Re: scn in oracle [message #223305 is a reply to message #223294] Thu, 08 March 2007 07:18 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

Hi,

i have undo_retention 10800 >>> mention value in second.
10800/60 = 180 minus
180/60 = 3 hrs.


regards
Taj
Re: scn in oracle [message #225989 is a reply to message #223203] Thu, 22 March 2007 03:42 Go to previous message
Dipali Vithalani
Messages: 278
Registered: March 2007
Location: India
Senior Member

Hi mohhammad,
Thanks..
Because ur answers helped me clear my concepts..
Thanks
Previous Topic: Re:RMAN Queries
Next Topic: Urgent: Archiver Hung 10g
Goto Forum:
  


Current Time: Sun Dec 11 00:36:19 CST 2016

Total time taken to generate the page: 0.05587 seconds