Flash back archive (11G new feature) Stay away from ORA-01555 errors

Flash back archive (11G new feature) Stay away from ORA-01555 errors

1.1.1.1 flash back Archiving

The previous flash back is implemented based on undo flash back, because our flash back is not sure of time, so it will lead to ORA-01555 (snapshot is too old or overwritten), ORA-01466 (table definition is changed), ORA-08180 (no snapshot) and other errors.

1.1.1.1.1 create a tablespace. The default size is 500 mb.

SQL> CREATE TABLESPACEhis_data_1 datafile '+ DATA' size 500 m;

Tablespacecreated.

1.1.1.1.2 default flash back archive is created, and the storage days are 7 days.

SQL> create flashback archivedefault fba1 tablespace his_data_1 retention 7 day;

Flashbackarchive created.

1.1.1.1.3 create a flashback archive. The storage duration is 30 days and the quota is 200 MB.

SQL> create flashback archive fba2 tablespace his_data_1 QUOTA 200 mRETENTION 30 day;

Flashbackarchive created.

1.1.1.1.4 authorize the user

SQL> grant flashback archive onfba1 to scott;

Grantsucceeded.

SQL> grant flashback archiveon fba2 to scott;

Grantsucceeded.

1.1.1.1.5 create a table and enable flashback Archiving

SQL> create tableempsal_his (empno number, ename varchar2 (10), sal number) flashback archive; (fba1 by default)

Tablecreated.

SQL> create table t (id int) flashback archive fba2;

Tablecreated.

1.1.1.1.6 insert data into the table and view the ALERT Log

17:07:57 SQL> insert into Scott. t select object_id from dba_objects;

75465 rows created.

17:09:12 SQL> commit;

Commitcomplete

[Oracle @ oracle trace] $ tail-falert_oracle007.log

Thread1 advanced to log sequence 63 (LGWR switch)

Current log #3 seq #63 mem #0:/u01/app/oracle/oradata/oracle007/redo03.log

ThuMay 07 17:12:06 2015

ArchivedLog entry 60 added for thread 1 sequence 62 ID 0x958cb7c1 dest 1:

ThuMay 07 16:46:52 2015

Starting background process FBDA

Thu May 07 16:46:52 2015

FBDA started with pid = 27, OS id = 35751

1.1.1.1.7 recreate the UNDO tablespace

17:11:59 SQL> create undo tablespaceundotbs2 datafile '+ data' size 100 m;

Tablespacecreated.

1.1.1.1.8 change the system undo tablespace

17:14:59 SQL> alter system setundo_tablespace = 'undotbs2 ';

Systemaltered.

1.1.1.1.9 Delete the system undo tablespace and database files (originally)

17:56:27 SQL> drop tablespace undotbs1including contents and datafiles;

Tablespacedropped.

1.1.1.1.10 view the table content at a certain time point

View the content in the table when no content is added to the created table.

SQL> select id from t as of timestampto_timestamp ('2017-5-7 17:05:57 ', 'yyyy-MM-DD hh24: mi: ss ');

Norows selected

Add a data entry to the table

SQL> insert into t values (1 );

1row created.

SQL> commit;

Commitcomplete.

View the number of rows in the table before adding data

SQL> select count (id) from t asof timestamp to_timestamp ('2017-5-7 18:07:04 ', 'yyyy-MM-DD hh24: mi: ss ');

COUNT (ID)

----------

75465

View the number of rows in the table after adding data

SQL> select count (id) from t;

COUNT (ID)

----------

75466

Norows selected

1.1.1.1.11 view the size and quota of the flashback archive tablespace

SQL> select flashback_archive_name, tablespace_name, quota_in_mb fromdba_flashback_archive_ts;

FLASHBACK_ARCHIVE_NAME TABLESPACE_NAME QUOTA_IN_MB

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

FBA1 HIS_DATA_1

FBA2 HIS_DATA_1 200

1.1.1.1.12 view the flashback archiving date, storage time, and default flashback Archiving

SQL> selectflashback_archive_name, to_char (CREATE_TIME, 'dd-mm_yyyy ') created, retention_in_days, statusfrom dba_flashback_archive;

FLASHBACK_ARCHIVE_NAME CREATED RETENTION_IN_DAYS STATUS

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

FBA1 07-05_2015 7 DEFAULT

FBA2 07-05_2015 30

1.1.1.1.13 view the information of the flashback archived table,

SQL> select * from dba_flashback_archive_tables;

TABLE_NAME OWNER_NAME FLASHBACK_ARCHIVE_NAME ARCHIVE_TABLE_NAME STATUS

-------------------------------------------------------------- ------------------------------ Accept ------------------------------------------------------------------------------------------------------------------------------------------------------------

EMPSAL_HIS SCOTT FBA1 SYS_FBA_HIST_78982 (the following number corresponds to the object_id in dba_object, which corresponds to the historical database block) ENABLED

T scott FBA2 SYS_FBA_HIST_78983 ENABLED

1.1.1.1.14 view the base table for flash back Archiving

SQL> selecttable_name, tablespace_name from user_tables where table_name like '% FBA % ';

TABLE_NAME TABLESPACE_NAME

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

SYS_FBA_DDL_COLMAP_78983 (record DDL operation) HIS_DATA_1

SYS_FBA_TCRV_78983 (every thing, changed information) HIS_DATA_1

SYS_FBA_HIST_78983 (records the actually changed data in the table)

1.1.1.1.15 modify the flashback archive quota to 200 MB

SQL> alterflashback archive fba1 modify tablespace his_data_1 quota 200 MB;

Flashback archive altered.

1.1.1.1.16 modify the retention time of the flash archive to one day

SQL> alterflashback archive fba1 modify retention1 day;

Flashback archive altered.

1.1.1.1.17 Delete the flashback Archive

SQL> drop flashback archive fba1;

Flashback archive dropped.

1.1.1.1.18 manually clear all archives

SQL> alterflashback archive fba2 purge all;

Flashback archive altered.

1.1.1.1.19 manually clear flash archive for a certain period of time

SQL> alterflashback archive fba2 purge before timestamp (systimestamp-interval '1' day); (clear the day before, month, year );

Flashback archive altered.

1.1.1.1.20 manually clear SCN-based archives.

SQL> alter flashback archive fba2 purgebefor scn 1111