Category: scn

Recovering Through the OPEN RESETLOGS Statement

Data Guard allows recovery on a physical standby database to continue after the primary database has been opened with the RESETLOGS option. When an ALTER DATABASE OPEN RESETLOGS statement is issued on the primary database, the incarnation of the database changes, creating a new branch of redo data.When a physical standby database receives a new branch of redo data, Redo Apply automatically takes the new branch of redo data. For physical standby databases, no manual intervention is required if the standby database did not apply redo data past the new resetlogs SCN (past the start of the new branch of redo data). The following table describes how to resynchronize the standby database with the primary database branch.

image

See Oracle Database Backup and Recovery Advanced User’s Guide for more information about database incarnations, recovering through an OPEN RESETLOGS operation, and
Flashback Database.

ref : http://docs.oracle.com/cd/B19306_01/server.102/b14239/toc.htm

Advertisements

How to get SCN from database. Timestamp against SCN

In many cases we need to know SCN of database. Also we may need to know the previous SCN of the database suppose before 10 minutes ago. I will try to write we can can get these two and how is this correlated.


A)Determine current SCN of The database.

You can query from V$DATABASE or using GET_SYSTEM_CHANGE_NUMBER procedure of DBMS_FLASHBACK package.

1)SQL> SELECT DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER() FROM DUAL;
DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER()
—————————————–
1053658

2)SQL> SELECT CURRENT_SCN FROM V$DATABASE;
CURRENT_SCN
———–
1053668
B)Determine current timestamp value.

SQL> SELECT SYSTIMESTAMP FROM DUAL;
SYSTIMESTAMP
—————————————————————————
14-MAY-08 11.00.08.374107 PM -04:00
C)Convert SCN to Timestamp.

SQL> SELECT SCN_TO_TIMESTAMP(1053639) FROM DUAL;
SCN_TO_TIMESTAMP(1053639)
—————————————————————————
14-MAY-08 10.52.15.000000000 PM
D)Convert Timestamp to SCN

SQL> SELECT TIMESTAMP_TO_SCN(’14-MAY-08 11.00.08.374107 PM’) FROM DUAL;
TIMESTAMP_TO_SCN(’14-MAY-0811.00.08.374107PM’)
———————————————-
1054516