Use of recyclebin in Oracle 11g Recycle Bin

Oracle 11g Recycle BinThe use of (recyclebin) is the content we will introduce in this article. We know that Flashback is based on the content in the undo segment. Therefore, restricted by the undo_retenton parameter, flashback features should be used, automatic tablespace revocation must be enabled.

In Oracle 11g, a new feature emerged. oracle flashback data archive (FDA) Stores changed data to another created flashback archive ), different from undo, you can set a separate storage policy so that it can return to the old data before the specified time without affecting the undo policy. In Oracle 11g, The recyclebin parameter changes slightly.

This supported parameter was introduced in Oracle 10.2.0

Version	Parameter Name	Data Type	Session Modifiable	System Modifiable
10.2.0	Recyclebin	String	TRUE	IMMEDIATE
11.1.0	Recyclebin	String	TRUE	DEFERRED

We can see this change description in the document. In the 11g, this parameter can still be modified immediately in the session and affect the current session, but if it is modified at the system level, the deferred parameter must be added, which does not affect the currently connected sesion, but the session of the new connection will be affected.

Flashback does not support sys users. Objects in the system tablespace cannot be obtained from the recycle bin. Therefore, show recyclebin is empty when using sys or system user logon.

To start a database flash:

1. Enable archive mode: alter database archivelog;

2. Configure the flash back area: alter system set db_recovery_file_dest = '/../'

3. Configure the flash back retention time. The default value is 1440 minutes: alter system set db_flashback_retention_target = '123 ';

4. Start the flash back database:

Shutdown immediate; startup mount

Alter database flashback on/off;

5. Perform the flash back operation:

Shutdown immediate; startup mount;

Flashback database to timestamp to_date ('2017-02-03 ', 'yyyy-mm-dd hh24: mi: ss ');

Or: flashback database to scn 2323534;

Alter database open ready only; check whether the flash time is the required data. If not, you can continue to modify it.

After the check, shutdown immediate; startup mount; alter database open resetlogs; resetlogs synchronizes the scn # In the control file with the scn # in the data file to eliminate the time interval.

In the V $ flashback_database_log view, you can view the space occupied by the flash log, the first scn to be returned, and the first time point to be returned.

7. view the recycle bin: show recyclebin; or select * from sys. recyclebin $ to check all the recycle bins.

8. Restore the deleted table: flashback table t_name to before drop. if the name of the flashed table is the same as that of the current table, you need to rename the table to flash back.

Flashback table t_name to before drop rename to t_new;

9. flash back to the specified table

View the table structure: desc "BIN $ q1_yuhuvothgqab/AQAQ3w ==0 0 ";

View table records: select count (*) from "BIN $ q1_yuhuvothgqab/AQAQ3w ==$ 0 ";

Restore the installation table name: flashback table "BIN $ q1_yuhuvothgqab/AQAQ3w = $0" to before drop;

Delete directly from the recycle bin: drop table t purge; drop user cascade; drop tablespace users including contents;

Clear the recycle bin: purge index idx_1; purge user_recyclebin; purge dba_recyclebin;

When db_recovery_file_dest is set to null, you can also cancel the flash back area. If flashback database is enabled, this method cannot be canceled.

View the current scn: select current_scn from v $ database;

Check which objects can be stored in the Flash recovery zone:

 
 

  
  
SQL> select file_type from v$flash_recovery_area_usage;  
  
  
FILE_TYPE  
  
  
--------------------  
  
  
CONTROL FILE  
  
  
REDO LOG  
  
  
ARCHIVED LOG  
  
  
BACKUP PIECE  
  
  
IMAGE COPY  
  
  
FLASHBACK LOG  
  
  
FOREIGN ARCHIVED LOG  
  
  
7 rows selected. 
 
 

View the space usage in the Flash recovery zone:

 
 

  
  
SQL> select * from v$flash_recovery_area_usage;  
  
  
FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE  
  
  
-------------------- ------------------ -------------------------  
  
  
NUMBER_OF_FILES  
  
  
---------------  
  
  
CONTROL FILE 0 0  
  
  
0  
  
  
REDO LOG 0 0  
  
  
0  
  
  
ARCHIVED LOG 0 0  
  
  
0  
  
  
FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE  
  
  
-------------------- ------------------ -------------------------  
  
  
NUMBER_OF_FILES  
  
  
---------------  
  
  
BACKUP PIECE 0 0  
  
  
0  
  
  
IMAGE COPY 0 0  
  
  
0  
  
  
FLASHBACK LOG 0 0  
  
  
0  
  
  
FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE  
  
  
-------------------- ------------------ -------------------------  
  
  
NUMBER_OF_FILES  
  
  
---------------  
  
  
FOREIGN ARCHIVED LOG 0 0  
  
  
0  
  
  
7 rows selected. 
 
 

Computing space occupied by flash recovery area:

 
 

  
  
SQL> select sum(percent_space_used)*3/100 from v$flash_recovery_area_usage;  
  
  
SUM(PERCENT_SPACE_USED)*3/100  
  
  
-----------------------------  
  
  
0 
 
 

If the flash recovery area space is insufficient, the database cannot be opened or the hang is occupied. Solution: Alter system set db_recovery_file_dest_size = 3G scope = spfiel;

Here is an introduction to the use of the Oracle 11g Recycle Bin (recyclebin). I hope this introduction will be helpful to you!