Flash back to the database

To query the current SCN:

Sql> select Current_scn from V$database;

Current_scn

-----------

1170420

Empty the table (TRUNCATE TABLE is not possible after the table is returned)

sql> truncate TABLE tom.t;

Table truncated.

Sql> select * from tom.t;

No rows selected

Turn off instance boot to mount state:

sql> shutdown Immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

sql> startup Mount;

ORACLE instance started.

Total System Global area 417546240 bytes

Fixed Size 2228944 bytes

Variable Size 318770480 bytes

Database buffers 88080384 bytes

Redo buffers 8466432 bytes

Database mounted.

Flashback database to SNC 1170420:

Sql> Flashback database to SCN 1170420;

Flashback complete.

Read-only to open the database and query whether the data is restored:

Sql> ALTER DATABASE open read only;

Database altered.

Sql> select * from tom.t;

ID NAME

---------- --------------------

3 Mark3

1 Mark1

2 MARK2

Close the database and open the database in Resetlogs mode:

sql> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

sql> startup Mount;

ORACLE instance started.

Total System Global area 417546240 bytes

Fixed Size 2228944 bytes

Variable Size 318770480 bytes

Database buffers 88080384 bytes

Redo buffers 8466432 bytes

Database mounted.

sql> ALTER DATABASE open resetlogs;

Database altered.

Sql> select Current_scn from V$database;

Current_scn

-----------

1170730

sql> archive log list;

Database Log Mode Archive mode

Automatic Archival Enabled

Archive Destination Use_db_recovery_file_dest

Oldest online log sequence 1

Next log sequence to archive 1

Current log sequence 1

Sql>

Flash back to the database