Oracle Flashback Detailed

Oracle Flashback is a fast and easy database recovery technology, it does not use backup files, through the flashback log can restore the database to a state of the past, when the user logic error (delete table data, table, table space, etc.) need to quickly restore the database, you can use flashback Realize.

1 Enabling the Flashback database

Oracle does not start the Flashback database by default, and the flashback technology must first start the database to flashback state, and the database must be in archive mode, and the Flashback recovery area is enabled because the flashback log file must be stored in the Flashback recovery area.

1) Check the archive status of the database

sql> archive log list;
Database Log Mode Archive mode
Automatic Archival Enabled
Archive Destination Use_db_recovery_file_dest
Oldest online log sequence 4
Next Log sequence to archive 6
Current Log sequence 6
Sql>

If the database does not boot into archive mode, it needs to be manually booted into mode.

sql> shutdown Immediate

Sql> Startup Mount

sql> ALTER DATABASE Archivelog

2) Determine the directory of the Flashback recovery area

Sql> Show parameter db_recovery_file_dest;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
Db_recovery_file_dest String/usr/oracle/app/flash_recovery
_area
Db_recovery_file_dest_size Big Integer 3882M

3) View and set db_flashback_retention_target parameters

The parameter is a number that is divided into units, which by default is 1440 minutes, indicating the maximum amount of time that a database can be flashed back to the past from the current start.

Sql> Show parameter db_flashback_retention_target;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
Db_flashback_retention_target integer 2880

It must be clarified here that Oracle is not guaranteed to be able to flash back to a point in time, because the flashback log is automatically maintained by the Flash recovery area, and if disk space is not available due to backup database or other operation, the flashback log will be deleted, so we should evaluate the space of the Flash recovery area when the database is running. The following parameters can be used to view

4) Assess the space in the Flash recovery area

You can evaluate the desired flash recovery area space by accessing the data dictionary V$flashback_database_log.

Sql> SELECT *
2 from V$flashback_database_log;

OLDEST_FLASHBACK_SCN oldest_flashback_time retention_target flashback_size estimated_flashback_size
-------------------- --------------------- ---------------- -------------- ------------------------
6202363 2017/6/19 Monday 22:24:4 2880 92143616 546471936

Estimated_flashback_size indicates that the size of the system's estimated flash recovery area is 546471936 bytes, and Flashback_size indicates that the current flashback data is 92143616 bytes in size.

5) Start the database to a flash back state

To start the flashback database, you must first boot the system into Mount state and then start the flashback database

Sql> Startup Mount

Sql> ALTER DATABASE Flashback on

Sql> Alter DATABSE Open

To see if the current database has a flashback status, you can access the data dictionary V$database

Sql> Select dbid, Name, flashback_on from V$database;

DBID NAME flashback_on
---------- --------- ------------------
1452257309 ORCL YES

6) Whether the query table space is in a flash-back protection state

Sql> select Name, flashback_on
2 from V$tablespace;

NAME flashback_on
------------------------------ ---------------
SYSTEM YES
Sysaux YES
UNDOTBS1 YES
USERS YES
TEMP YES
Ctrr_data YES
Apsalu3_data YES
Default_tablespace YES
TEMP02 YES
Rman_ts YES

7) Turn on or off a table space flash back status

First the database must be in Mount state to do this

Sql> Startup Mount

Sql> alter TABLESPACE users flashback on/off

2 implementation steps for the Flash back database

1) database level flash back

Use the Scott user to create a test table to insert part of the data and then delete.

Get a point in time before deletion for flashback recovery

Select To_char (sysdate, ' Yyyy-mm-dd hh24:mi:ss ') from dual;

2017-06-21 12:36:49

Delete a test table

drop table T_test1;
drop table t_test3;
Commit

Shutdown database 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 450953216 bytes
Fixed Size 2214256 bytes
Variable Size 348128912 bytes
Database buffers 96468992 bytes
Redo buffers 4141056 bytes
Database mounted.

Use the Flashback command to restore a database to a point in time before the table is deleted

Flashback database to timestamp to_timestamp (' 2017-06-21 12:36:49 ', ' yyyy-mm-dd hh24:mi:ss ');

Sql> Flashback database to timestamp to_timestamp (' 2017-06-21 12:36:49 ', ' yyyy-mm-dd hh24:mi:ss ');

Flashback complete.

Then use Read only mode to open the database to verify that the deleted table has been restored

ALTER DATABASE open Read only

After verifying the error and then restarting the data to mount state, and then using Resetlogs to open the database, the flashback log is still valid, and we can continue to flash back to a point in time before resetlogs, but it needs to be clear that the database can no longer flash back to Resetlogs Some time later, for example, this time we flashed back: 2017-06-21 12:36:49, we can only flash back to a point in time before this point in time, and then the point of time can no longer flash back.

Sql> Startup Mount

sql> ALTER DATABASE open Resetlogs

View the two previously deleted tables have been restored back.

2) Delete user test

New user

Create user Latiny identified by XXXXX
Default Tablespace default_tablespace
Temporary tablespace temp;

Grant Connect, resource to Latiny;

CREATE TABLE Insert test data

CREATE TABLE T_test1 (ID number not NULL primary key);

Loop Insert 1000 Data

Gets the current time of the system to remove the flash back recovery point

Select To_char (sysdate, ' Yyyy-mm-dd hh24:mi:ss ') from dual;

2017-06-21 12:36:49

Latiny user log out, use sys user to delete new user

Drop user Latiny cascade;

Shut down database 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 450953216 bytes
Fixed Size 2214256 bytes
Variable Size 352323216 bytes
Database buffers 92274688 bytes
Redo buffers 4141056 bytes
Database mounted.

Restore the database to the moment before the Latiny user is deleted

Sql> Flashback database to timestamp to_date (' 2017-06-21 12:36:49 ', ' yyyy-mm-dd hh24:mi:ss ');

Flashback complete.

Read-only to open the data check whether the deleted user Latiny has been restored.

Sql> ALTER DATABASE open read only;

Database altered.

Both user and user-created tables have been restored

Close the database, boot to mount state, and then boot to open state in Resetlogs state

sql> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
sql> startup Mount;
ORACLE instance started.

Total System Global area 450953216 bytes
Fixed Size 2214256 bytes
Variable Size 352323216 bytes
Database buffers 92274688 bytes
Redo buffers 4141056 bytes
Database mounted.
sql> ALTER DATABASE open resetlogs;

Database altered.

2 Flash Back Delete

If the user uses the drop TABLE command, the table will not be deleted immediately, but will remain in place, and the deleted table information stored in the Recycle Bin retains the original table name and a new name is given, obviously the space occupied by the deleted table is not immediately released, and the recorded information is retained for a period of time in the Recycle Bin. Until the Recycle Bin is out of space or use the purge instruction to permanently delete records from the Recycle Bin.

The Recycle Bin is a logical structure that does not have a physical data structure, as long as the deleted table information is recorded in it can be recovered by flashback technology.

1) Check whether the database flashback delete is started

Sql> Show parameter RecycleBin;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
RecycleBin string on

Started, you can use the following command if not started

Sql> alter system set Recyclebin=on Scope=both;

2) Use of the Recycle Bin

By querying the data dictionary User_recyclebin and Dba_recyclebin can get all the information of the Recycle Bin delete table

SELECT *
From Dba_recyclebin;

SELECT *
From User_recyclebin;

Delete a test table created under the Latiny user

sql> drop table T_test1;

Table dropped

Sql> commit;

Commit Complete

Then, using SQL to view the Recycle Bin information, you can find the table information that was deleted.

Recovering a deleted table

Sql> Flashback table T_test1 to before drop;

Done

You can see that the table has been restored.

It is important to note that after the table is deleted the related database objects (indexes, triggers) and so on will be deleted after the flash back, these objects will also be restored, but the name is no longer the name is not deleted, it will change, is the system automatically from the name, difficult to read comprehension, need to manually modify.

3) Multiple table restores with the same name

In actual use, may be different libraries or the same library at different times to delete the same name of the table, then before the recovery we need to use the structure of the table, delete time, etc. to determine which table is really need to restore the table.

Build the test table T_test1, and insert some data

CREATE TABLE T_test1 (ID number not NULL primary key);

and then delete

Build the test table T_test1, and insert some data

CREATE TABLE T_test1 (ID number not NULL primary key, name VARCHAR2 (25));

See two identical tables found in Recycle Bin

Sql> Select object_name, Original_name, type, droptime
2 from User_recyclebin;

object_name original_name TYPE Droptime
------------------------------ -------------------------------- ------------------------- -------------------
Bin$unef8ouhylbguab/aqbq2g==$0 T_test1 TABLE 2017-06-21:19:36:23
Bin$unef8ougylbguab/aqbq2g==$0 sys_c0012828 INDEX 2017-06-21:19:36:23
Bin$unef8oukylbguab/aqbq2g==$0 sys_c0012830 INDEX 2017-06-21:19:36:39
Bin$unef8oulylbguab/aqbq2g==$0 T_test1

Use DESC to view the table structure, it is important to use this command in the system through Sqlplus, using PL/SQL or the command window of other remote connection tools to query the table structure, you will be prompted object_name the corresponding table name does not exist.

sql> desc "bin$unef8ouhylbguab/aqbq2g==$0";
Name Null? Type
----------------------------------------- -------- ----------------------------
ID not NULL number

sql> desc "bin$unef8oulylbguab/aqbq2g==$0";
Name Null? Type
----------------------------------------- -------- ----------------------------
ID not NULL number
NAME VARCHAR2 (25)

Using object_name to recover a table

Sql> Flashback table "Bin$unef8oulylbguab/aqbq2g==$0" to before drop rename to t_test1_new;

Flashback complete.

The query recovery table exists and is the previously established t_test1 with two fields;

4) Purge Permanently delete table

If you are very sure that a table is useless and can be permanently deleted, you can use the Purge command, and this command removes any records from the table that the Recycle Bin does not save.

sql> drop table T_test1_new purge;

Table dropped

If a table is already in the Recycle Bin to permanently delete, you can use the following command

Sql> Purge table t_test1;

Done

There is no information at this time to view the Recycle Bin because the previous two test table information has been deleted and emptied with the purge command

Sql> show RecycleBin;
Sql>

Oracle Flashback Detailed