Category Archives: flashback

Step To Configure Oracle Flashback In Asm Database

By
1. Ensure db_recovery_file_dest is set.
sqlplus ‘/ as sysdba’
SQL> alter system set db_recovery_file_dest=’+’ SCOPE=spfile;
2. Ensure db_recovery_file_dest_size is set
SQL> alter system set db_recovery_file_dest_size=100G SCOPE=spfile;
3. Stop and start the database
sqlplus ‘/ as sysdba’
SQL> shutdown immediate;
SQL> startup mount;
If flashback to any previous point in time is required, then turn flashback on using the following command
SQL> alter database flashback on;
SQL> alter database open;
SQL> alter system set db_flashback_retention_target=2880;
NOTES
Set the db_recovery_file_dest to an appropriate location for the flashback recovery files.
Set the db_recovery_file_dest_size to an appropriate size for the amount and size of the testing required.
Set the db_flashback_retention_target to an appropriate time, in mins, to retain flashbackability.
Only run alter database flashback on; if there is a requirement to flashback to ANY previous point in time.
Determine if Flashback Database is Already Enabled.
sqlplus ‘/ as sysdba’
SQL> select flashback_on from v$database;
Creating and Using Flashback Restore points.
This worked example assumes the database is using ASM to manage its storage.
Createing a Restore point
Create a restore point whenever the database is at a state that it may needed to be flashed back to. Use the optional GUARANTEE FLASHBACK DATABASE clause to ensure that the restore point is not aged out of the flashback recovery area (FRA) as dictated by the db_flashback_retention_target parameter.
1. You may want to create the restore point in mount mode. If so, put the database into mount mode now.
2. Create a restore point
sqlplus ‘/ as sysdba’
SQL> create restore point [GUARANTEE FLASHBACK DATABASE];
Rolling Back to a Restore Point
1. Identify the Restore point
sqlplus ‘/ as sysdba’
SQL> select name, time,guarantee_flashback_databse from v$restore_point;
SQL> quit
2. For a non RAC environment use the following commands to flashback to a restore point.
sqlplus ‘/ as sysdba’
SQL> shutdown immediate;
SQL> startup mount;
SQL> flashback database to restore point ;
SQL> alter database open resetlogs;
3. For RAC instances use the following commands.
One one of the nodes run, srvctl stop database -d -o immediate
sqlplus ‘/ as sysdba’
SQL> startup mount;
SQL> flashback database to restore point ;
SQL> alter database open resetlogs;
SQL> shutdown immediate;
srvctl start database -d
Run crs_stat -t to confirm that the database is backup okay.
NOTES
Any tables created and updated without the LOGGING option will be suseptable to block curruption errors when the database is flashed back. These can be remedied by issuing the TRUNCATE TABLE command against the affected object(s).
Dropping a Restore Point
Restore points can be dropped with the database open using the following commands
sqlplus ‘/ as sysdba’
SQL> drop restore poijnt ;
Monitoring Flashback Logging
After enabling flashback logging, Oracle keeps track of the amount of logging generated. This can be queried from v$flashback_database_log, the estimate gets better with age. Note that this is the size of the flashback logs only and does not include space used by archive logs and RMAN backups.
sqlplus ‘/ as sysdba’
SQL> select estimated_flashback_size/1024/1024/1024 “EST_FLASHBACK_SIZE(GB)” from v$flashback_database_log;
Finding the Earliest Flashback Point
Querying V$flashback_database_log will show you the earliest point you can flashback your database to based on the size of the FRA and the currently available flashback logs.
sqlplus ‘/ as sysdba’
SQL> alter session set nls_date_format=’dd/mm/yy hh24:mi:ss’;
SQL> select oldest_flashback_scn,oldest_flashback_time from v$flashback_database_log;
Disabling Flashback Database
Full any previous point in time flashback can be disabled with the database open. Any unused Flashback logs will be automatically removed at this point and a message detailing the file deletion written to the alert log.
sqlplus ‘/ as sysdba’
SQL> ALTER DATABASE FLASHBACK OFF;
mostly comming Errors
ORA-38754: FLASHBACK DATABASE not started; required redo log is not available
Cause:-
Oracle needs to have the required archive logs in the archive destination at the time of flashback.
Solution:-
 rman target /
  run{
     allocate channel t1 device type ‘sbt_tape’;
     FLASHBACK DATABASE TO RESTORE POINT ;
     }

learing abbout postgresql database and oracle database and cricket Entertainment

Fully Concept Of Flashback Oracle ,Practical With Demo Error And Solution Also

By
Oracle Flashback Technology is a group of Oracle Database features that let you view past states of database objects or to return database objects to a previous state without using point-in-time media recovery.
Flashback feature was introduced in Oracle9i; it was limited to Flashback Query only
Great improvements have been made in the Flashback functions in Oracle Database 10g.
Flashback functionalities provide fast and flexible data recovery
TYPES OF FLASHBACK :-
1.     flashback query         —we can show  only the  past state of the table
2.     flashback version query   —show the the table transaction time and scn
3.     flashback transaction query —it show what operation is  done and which user is performed
4.     flashback table   — to flash back a table to an earlier scn or timestamp
5.     flashback drop — flashback table to before drop using recylebin
6.     flashback database — to flash back a table to an earlier scn,timestamp or restore point
Flashback Works On Two Parameter
1.     scn
2.     timestamp
FLASHBACK STATUS:-
Flashback status of a database can be checked from the below query and system parameters.
           SQL> select NAME, FLASHBACK_ON from v$database;
        SQL> archive log list
        SQL> show parameter undo_retention
        SQL> show parameter  db_recovery_file_dest
        SQL> show parameter  db_recovery_file
FLASHBACK ENABLE:-
The Database must be started through SPFILE
SQL > show parameter spfile
NAME                       TYPE     VALUE
Step to Flashback Practical
1)undo_retention seconds, set to one hour
SQL> SELECT tablespace_name,retention FROM dba_tablespaces;
TABLESPACE_NAME             RETENTION
—————————— ———–
SYSTEM                         NOT APPLY
SYSAUX                         NOT APPLY
UNDOTBS1                    NOGUARANTEE
TEMP                           NOT APPLY
USERS                          NOT APPLY
TESTTBS                        NOT APPLY
6 ROWS selected.
SQL> ALTER TABLESPACE UNDOTBS1 retention guarantee;
TABLESPACE altered.
SQL> SELECT tablespace_name,retention FROM dba_tablespaces;
TABLESPACE_NAME             RETENTION
—————————— ———–
SYSTEM                         NOT APPLY
SYSAUX                         NOT APPLY
UNDOTBS1                    GUARANTEE
TEMP                           NOT APPLY
USERS                          NOT APPLY
TESTTBS                        NOT APPLY
6 ROWS selected.
SQL> ALTER SYSTEM SET undo_retention=3600;
SYSTEM altered.
2)create test1 table and insert some data’s
SQL> CREATE TABLE test1(id NUMBER, descr VARCHAR2(30));
TABLE created.
SQL> INSERT INTO test1 VALUES(1,’One’);
1 ROW created.
SQL> INSERT INTO test1 VALUES(2,’Two’);
1 ROW created.
SQL> INSERT INTO test1 VALUES(3,’Three’);
1 ROW created.
SQL> COMMIT;
COMMIT complete.
3)step to set flashback_on in database
SQL> shutdown IMMEDIATE
DATABASE closed.
DATABASE dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total SYSTEM Global Area 1068937216 bytes
Fixed SIZE                  2233336 bytes
Variable SIZE             796920840 bytes
DATABASE Buffers          260046848 bytes
Redo Buffers                9736192 bytes
DATABASE mounted.
SQL> ALTER DATABASE flashback ON;
DATABASE altered.
SQL> ALTER DATABASE OPEN;
DATABASE altered.
SQL> SELECT flashback_on FROM v$database;
FLASHBACK_ON
——————
YES
You define the flashback database retention with:
SQL> show parameter db_flashback_retention_target
NAME                              TYPE     VALUE
———————————— ———– ——————————
db_flashback_retention_target     INTEGER 1440
4) You are now able to rewind your database at maximum db_flashback_retention_target minutes in the past, if you look into your Fast Recovery Area you see creation of below files:
[oracle@server1 fast_recovery_area]$ pwd
/oracle/fast_recovery_area
[oracle@server1 fast_recovery_area]$ ll
total 20
drwx—— 2 root   root 16384 May 11 17:05 lost+found
drwxr-x— 7 oracle dba   4096 Aug 15 14:23 TEST
[oracle@server1 fast_recovery_area]$ ll TEST
total 20
drwxr-x— 30 oracle dba 4096 Aug 15 00:11 archivelog
drwxr-x—  2 oracle dba 4096 Jul 13 11:22 autobackup
drwxr—–  5 oracle dba 4096 Jul 25 17:07 backupset
drwxr-x—  2 oracle dba 4096 Aug 15 14:23 flashback
drwxr-x—  2 oracle dba 4096 Jul 20 12:07 onlinelog
[oracle@server1 fast_recovery_area]$ ll TEST/flashback
total 16040
-rw-r—– 1 oracle dba 8200192 Aug 15 14:31 o1_mf_74l3ynbt_.flb
-rw-r—– 1 oracle dba 8200192 Aug 15 14:23 o1_mf_74l3yoth_.flb
5)add some values in the tables and just show the scn
SQL> SELECT current_scn, SCN_TO_TIMESTAMP(current_scn) FROM v$database;
CURRENT_SCN         SCN_TO_TIMESTAMP(CURRENT_SCN)
———–                           ——————————-
  29034095                15-AUG-11 02.35.27.000000000 PM
SQL> SELECT * FROM test1;
   ID DESCR
———- ————–
        1 One
        2 Two
        3 Three
SQL> ALTER TABLE test1 ADD (column1 VARCHAR2(20));
TABLE altered.
SQL> UPDATE test1 SET column1=’Temporary’;
3 ROWS updated.
SQL> COMMIT;
COMMIT complete.
SQL> SELECT * FROM test1;
    ID DESCR                       COLUMN1
———- —————-            ————-
        1 One                            TEMPORARY
        2 Two                            TEMPORARY
        3 Three                          TEMPORARY
SQL> SELECT current_scn, SCN_TO_TIMESTAMP(current_scn) FROM v$database;
CURRENT_SCN       SCN_TO_TIMESTAMP(CURRENT_SCN)
———–      ———————————
  29034142       15-AUG-11 02.36.11.000000000 PM
6) flashback query — we can only show  the  past state of the table
SQL> flashback DATABASE as of scn 29034095;
Flashback complete.
SQL> SELECT * FROM nijam.test1;
    ID DESCR
———- ————–
     1 One
     2 Two
     3 Three
7) flashback table   — to flash back a table to an earlier scn or timestamp
Enabling row movement for your test table is mandatory for flashback table:
SQL> ALTER TABLE test1 enable ROW movement;
TABLE altered.
Database must be in mount state to flashback it:
SQL> shutdown IMMEDIATE;
DATABASE closed.
DATABASE dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total SYSTEM Global Area 1068937216 bytes
Fixed SIZE               2233336 bytes
Variable SIZE          796920840 bytes
DATABASE Buffers       260046848 bytes
Redo Buffers                9736192 bytes
DATABASE mounted.
SQL> flashback DATABASE TO scn 29034095;
Flashback complete.
SQL> ALTER DATABASE OPEN resetlogs;
DATABASE altered.
SQL> SELECT * FROM nijam.test1;
          ID DESCR
———- ————–
    1 One
     2 Two
     3 Three
8) Inserting few test rows and performing a “wrong” update:
SQL> SELECT current_scn, SCN_TO_TIMESTAMP(current_scn) FROM v$database;
CURRENT_SCN   SCN_TO_TIMESTAMP(CURRENT_SCN)
———–   ———————————–
  28947307 15-AUG-11 09.35.57.000000000 AM
SQL> SELECT * FROM test1;
ID DESCR
———- ——————
     1 One
     2 Two
     3 Three
SQL> INSERT INTO test1 VALUES(4,’Five’);
1 ROW created.
SQL> SELECT * FROM test1;
    ID DESCR
———- —————–
     1 One
     2 Two
     3 Three
     4 Five
SQL> UPDATE test1 SET descr=’Four’;
4 ROWS updated.
SQL> COMMIT;
COMMIT complete.
SQL> SELECT * FROM test1;
    ID DESCR
  —– ————–
     1 Four
     2 Four
     3 Four
     4 Four
9) Flashing back table to original good state (SCN or timestamp taken in previous step, Oracle suggests to record current SCN before issuing such command):
SQL> SELECT current_scn, SCN_TO_TIMESTAMP(current_scn) FROM v$database;
CURRENT_SCN SCN_TO_TIMESTAMP(CURRENT_SCN)
———– ————————————-
  28947426 15-AUG-11 09.38.03.000000000 AM
SQL> flashback TABLE test1 TO scn 28947307;
Flashback complete.
SQL> SELECT * FROM test1;
    ID DESCR
———- ——————————
     1 One
     2 Two
     3 Three
You can also work with restore point (refer to official documentation for retention policies):
SQL> SELECT * FROM test1;
     ID DESCR
———- ——————————
     1 One
     2 Two
     3 Three
SQL> CREATE restore point before_upgrade;
Restore point created.
SQL> UPDATE test1 SET descr=’Temporary’;
3 ROWS updated.
SQL> COMMIT;
COMMIT complete.
SQL> SELECT * FROM test1;
    ID DESCR
———- ——————————
     1 TEMPORARY
     2 TEMPORARY
     3 TEMPORARY
SQL> SELECT current_scn, SCN_TO_TIMESTAMP(current_scn) FROM v$database;
CURRENT_SCN SCN_TO_TIMESTAMP(CURRENT_SCN)
———– —————————————-
  28949800 15-AUG-11 10.03.02.000000000 AM
SQL> flashback TABLE test1 TO restore point before_upgrade;
Flashback complete.
SQL> SELECT * FROM test1;
   ID DESCR
———- ——————————
     1 One
     2 Two
     3 Three
SQL> DROP restore point before_upgrade;
Restore point dropped.
10) Using flasback technology will create SYS_TEMP_FBT in your schema:
SQL> SELECT table_name FROM user_tables;
TABLE_NAME
——————————
SYS_TEMP_FBT
TEST1
11) flashback drop  — flashback table to before drop using recylebin
SQL> SELECT table_name FROM user_tables;
TABLE_NAME
——————————
SYS_TEMP_FBT
TEST1
SQL> DROP TABLE test1;
TABLE dropped.
SQL> SELECT table_name FROM user_tables;
TABLE_NAME
——————————
SYS_TEMP_FBT
SQL> show recyclebin;
ORIGINAL NAME RECYCLEBIN NAME             OBJECT TYPE  DROP TIME
—————- —————————— ———— ——————-
TEST1         BIN$qogQry1iu6TgQEsKbCUKuA==$0 TABLE     2011-08-15:10:29:44
SQL> SELECT * FROM recyclebin;
OBJECT_NAME                 ORIGINAL_NAME                 OPERATION TYPE                   TS_NAME                     CREATETIME       DROPTIME            DROPSCN
—————————— ——————————– ——— ————————- —————————— ——————- ——————- ———-
PARTITION_NAME                CAN CAN RELATED BASE_OBJECT PURGE_OBJECT   SPACE
——————————– — — ———- ———– ———— ———-
BIN$qogQry1iu6TgQEsKbCUKuA==$0 TEST1                         DROP   TABLE                  USERS                       2011-08-15:09:35:12 2011-08-15:10:29:44   28951308
                             YES YES   71097    71097     71097       8
12) Once the object is dropped you cannot access it but you can still query its recyclebin counterpart and/or restore it:
SQL> SELECT * FROM test1;
SELECT * FROM test1
          *
ERROR AT line 1:
ORA-00942: TABLE OR VIEW does NOT exist
SQL> SELECT * FROM “BIN$qogQry1iu6TgQEsKbCUKuA==$0”;
    ID DESCR
———- ——————————
     1 One
     2 Two
     3 Three
SQL> flashback TABLE test1 TO before DROP;
Flashback complete.
SQL> show recyclebin;
SQL> SELECT * FROM test1;
    ID DESCR
———- ——————————
     1 One
     2 Two
     3 Three
You can also purge it with:
SQL> purge recyclebin;
Recyclebin purged.
13) flashback query         —we can show  only the  past state of the table
SQL> SELECT * FROM test1;
    ID DESCR
———- ——————————
     1 One
     2 Two
     3 Three
SQL> SELECT table_name FROM user_tables;
TABLE_NAME
——————————
SYS_TEMP_FBT
TEST1
SQL> SELECT current_scn, SCN_TO_TIMESTAMP(current_scn),TO_CHAR(SYSDATE,’dd-mon-yyyy hh24:mi:ss’) AS current_time FROM v$database;
CURRENT_SCN SCN_TO_TIMESTAMP(CURRENT_SCN)                                           CURRENT_TIME
———– ————————————————————————— —————————–
  28954179 15-AUG-11 11.03.48.000000000 AM                                          15-aug-2011 11:03:48
SQL> UPDATE test1 SET descr=’Temporary’;
3 ROWS updated.
SQL> COMMIT;
COMMIT complete.
SQL> SELECT * FROM test1;
   ID DESCR
———- ——————————
     1 TEMPORARY
     2 TEMPORARY
     3 TEMPORARY
The flashback query feature works with AS OF SCN and AS OF TIMESTAMP in SELECT statement:
SQL> SELECT * FROM test1
AS OF scn 28954179;
     ID DESCR
———- ——————————
     1 One
     2 Two
     3 Three
SQL> SELECT * FROM test1
AS OF TIMESTAMP TO_TIMESTAMP(’15-AUG-11 11.03.48.000000000 AM’);
    ID DESCR
———- ——————————
     1 One
     2 Two
     3 Three
SQL> SELECT * FROM test1
AS OF TIMESTAMP SYSTIMESTAMP – INTERVAL ’10’  MINUTE
    ID DESCR
———- ——————————
     1 One
     2 Two
     3 Three
You can also use DBMS_FLASHBACK package for flashback query. This can as well been done with SCN using ENABLE_AT_SYSTEM_CHANGE_NUMBER procedure or timestamp using ENABLE_AT_TIME procedure:
SQL> EXEC dbms_flashback.enable_at_system_change_number(28954179);
PL/SQL PROCEDURE successfully completed.
SQL> SELECT * FROM test1;
    ID DESCR
———- ——————————
     1 One
     2 Two
     3 Three
SQL> EXEC dbms_flashback.disable;
PL/SQL PROCEDURE successfully completed.
SQL> SELECT * FROM test1;
    ID DESCR
———- ——————————
     1 TEMPORARY
     2 TEMPORARY
     3 TEMPORARY
Finally you can flashback your table using flashback table technology (or insert using AS SELECT in a subquery):
SQL> flashback TABLE test1 TO scn 28954179;
Flashback complete.
SQL> SELECT * FROM test1
ID DESCR
———- ——————————
     1 One
     2 Two
     3 Three
Remark:
This flashback query technology can also be used in export utility (exp and expdp) using FLASHBACK_SCN and FLASHBACK_TIME parameters.
14) flashback version query   —show the the table transaction time and scn
SQL> SELECT * FROM test1;
   ID DESCR
———- ——————————
     1 One
     2 Two
     3 Three
SQL> SELECT current_scn, SCN_TO_TIMESTAMP(current_scn) FROM v$database;
CURRENT_SCN SCN_TO_TIMESTAMP(CURRENT_SCN)
———– —————————————————————————
  29006118 15-AUG-11 12.51.21.000000000 PM
SQL> UPDATE test1 SET descr=’The one’ WHERE id=1;
1 ROW updated.
SQL> COMMIT;
COMMIT complete.
SQL> UPDATE test1 SET descr=’The only one’ WHERE id=1;
1 ROW updated.
SQL> COMMIT;
COMMIT complete.
SQL> SELECT current_scn, SCN_TO_TIMESTAMP(current_scn) FROM v$database;
CURRENT_SCN SCN_TO_TIMESTAMP(CURRENT_SCN)
———– —————————————————————————
  29006142 15-AUG-11 12.52.03.000000000 PM
note:
Please note the commit after each update to generate multiple transactions.
You can now see past figures:
SQL> SELECT versions_startscn, versions_starttime, versions_endscn, versions_endtime, versions_xid, versions_operation, id, descr
FROM test1
VERSIONS BETWEEN SCN 29006118 AND 29006142
WHERE id = 1;
VERSIONS_STARTSCN VERSIONS_STARTTIME    VERSIONS_ENDSCN VERSIONS_ENDTIME      VERSIONS_XID V      ID DESCR
—————– ———————— ————— ———————— —————- – ———- ——————————
     29006133 15-AUG-11 12.51.45 PM                                          0A001500B99F0000 U       1 The only one
     29006128 15-AUG-11 12.51.36 PM        29006133 15-AUG-11 12.51.45 PM 060017000A100000 U       1 The one
                                              29006128 15-AUG-11 12.51.36 PM                             1 One
SQL> SELECT * FROM test1 AS OF scn 29006128;
     ID DESCR
———- ——————————
     1 The one
     2 Two
     3 Three
SQL> SELECT * FROM test1 AS OF scn 29006118;
    ID DESCR
———- ——————————
     1 One
     2 Two
     3 Three
SQL> SELECT * FROM test1;
     ID DESCR
———- ——————————
     1 The only one
     2 Two
     3 Three
Note:
Same as DBMS_FLASHBACK package you may work with timestamp or SCN
15) flashback transaction query —it show what operation is  done and which user is performed
SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
DATABASE altered.
If we use flashback query example:
SQL> SELECT xid, operation, start_scn, commit_scn, logon_user, undo_sql
FROM flashback_transaction_query
WHERE xid = HEXTORAW(‘0A001500B99F0000’);
no ROWS selected
SQL> SELECT xid, operation, start_scn, commit_scn, logon_user, undo_sql
FROM flashback_transaction_query
WHERE xid = HEXTORAW(‘060017000A100000’);
XID           OPERATION                      START_SCN COMMIT_SCN LOGON_USER
—————- ——————————– ———- ———- ——————————
UNDO_SQL
——————————————————————————————————————————————————————————————————–
060017000A100000 UPDATE                          29006126   29006128 YJAQUIER
UPDATE “YJAQUIER”.”TEST1″ SET “DESCR” = ‘One’ WHERE ROWID = ‘AAARW7AAEAABn6GAAA’;
060017000A100000 BEGIN                           29006126   29006128 YJAQUIER
First strange thing is missing information for one of the DML statement… After a while if you again select the existing one:
SQL> SELECT xid, operation, start_scn, commit_scn, logon_user, undo_sql
FROM flashback_transaction_query
WHERE xid = HEXTORAW(‘060017000A100000’);
no ROWS selected
Behavior is really erratic and this is explained by bugs, apparently corrected in 11.2.0.2.2, so not really mature so far:
Bug 10358019 – Queries against FLASHBACK_TRANSACTION_QUERY return wrong results [ID 10358019.8]
Note:
Oracle 11gR2 extend this functionality with flashback transaction backout by flashing back a specific transactions and all its dependent transactions.
16) Flashback Data Archive
Flashback Data Archive (FDA) technology is an extension of flashback versions query technology and extend Undo functionality (bypassing UNDO_RETENTION parameter) by keeping figures for fixed pre-defined period (if enough available space obviously). On the paper it’s perfect: transparent, efficient, old figures stored in compressed format, answers to SOX requirements. Small drawback is the cost as you must purchase Oracle Total Recall option to use it…
First start by creating a dedicated tablespace (you may instead use an existing one):
SQL> CREATE TABLESPACE fda datafile ‘/oracle/data01/test/fda01.dbf’ SIZE 100m
extent management local SEGMENT SPACE management auto;
TABLESPACE created.
Then create a default flashback data archive (then no need to specify one when activating FDA on your tables), no quota to use whole tablespace and retention set to one month:
SQL> CREATE flashback archive DEFAULT fla1
TABLESPACE fda
retention 1 MONTH;
Flashback archive created.
Then modify your table:
SQL> ALTER TABLE test1 flashback archive;
TABLE altered.
Note:
To see what’s activated and your FDA you may use the following queries (USER and ALL counterparts may also be used)
SELECT * FROM dba_flashback_archive;
SELECT * FROM dba_flashback_archive_ts;
SELECT * FROM dba_flashback_archive_tables;
Let’s modify the table and see past figures, same as versions query and you may use SCN or timestamp. From pure SQL standpoint you don’t see difference with versions query, except in retention policy…
SQL> SELECT * FROM test1;
    ID DESCR
———- ——————————
     1 One
     2 Two
     3 Three
SQL> SELECT current_scn, SCN_TO_TIMESTAMP(current_scn) FROM v$database;
CURRENT_SCN SCN_TO_TIMESTAMP(CURRENT_SCN)
———– —————————————————————————
  29045024 15-AUG-11 04.22.20.000000000 PM
SQL> UPDATE test1 SET descr=’The one’ WHERE id=1;
1 ROW updated.
SQL> COMMIT;
COMMIT complete.
SQL> SELECT * FROM test1;
    ID DESCR
———- ——————————
     1 The one
     2 Two
     3 Three
SQL> SELECT * FROM test1 AS OF scn 29045024;
     ID DESCR
———- ——————————
     1 One
     2 Two
     3 Three
SQL> SELECT current_scn, SCN_TO_TIMESTAMP(current_scn) FROM v$database;
CURRENT_SCN SCN_TO_TIMESTAMP(CURRENT_SCN)
———– —————————————————————————
  29045079 15-AUG-11 04.23.29.000000000 PM
SQL> UPDATE test1 SET descr=’The only one’ WHERE id=1;
1 ROW updated.
SQL> COMMIT;
COMMIT complete.
SQL> SELECT * FROM test1 AS OF scn 29045079;
   ID DESCR
———- ——————————
     1 The one
     2 Two
    3 Three
In this example past version of the table will be kept for one month and then automatically deleted.
With only few exceptions DDLs are supported and for unsupported ones you may use DBMS_FLASHBACK_ARCHIVE PL/SQL package:
SQL> ALTER TABLE test1 ADD (column1 VARCHAR2(20));
TABLE altered.
SQL> SELECT * FROM test1;
     ID DESCR                       COLUMN1
———- —————————— ——————–
     1 The only one
     2 Two
     3 Three
SQL> SELECT * FROM test1 AS OF scn 29045024;
    ID DESCR
———- ——————————
     1 One
     3 Three
        2 Two

learing abbout postgresql database and oracle database and cricket Entertainment