OCP 11gR2: Administration II 練習筆記 (六)

Lesson 06

===============================================================================

01. Oracle Total Recall (flashback data archive, recycle-bin)

- Flashback archive enable at table level with specified retention period (year).

- FDA contents cannot be modifying directly.

- For audit or historical report

- view "dba_flashback_archive", "dba_flashback_archive_ts", "dba_flashback_archive_tables" or user_

- When data modified > record in undo tbs > when undo expired > record trans to FDA (compress)

- Better create tbs to hold FDA

- Table not support by exadata

- FDA tablespace cannot transport to other server by oracle transport (use RMAN backup & restore)

- You can grant flashback archive administrator to user

e.g   create tablespace FDA_DATA_1 datafile '/location' size 5g autoextend on;

        create flashback archive fda1 tablespace FDA_DATA_1 retention 1 year;

        (don't set quota, if quota is full, the new transaction will stop !)

        alter table 'name' flashback archive fda1;

        - if FDA_DATA_1 tbs full, you may create another tbs and add in to fda1

        create tablespace FDA_DATA_2 datafile 'location' size 5g autoextend on;

        alter flashback archive fda1 add tablespace FDA_DATA_2;

Schema evolution

- after enable FDA, DDL may not support (drop , add column, truncate)

- disassociate and associate table with FDA by dbms_flashback_archive

- if source table DDL change, FDA table also need too

e.g.  exec dbms_flashback_archive.disassociate_FBA('schema','table');

        exec dbms_flashback_archive_reassociate_FBA('schema','table');

* remember to reassociate

* use SCN for first queries

* before flashback, commit or rollback first

02. Flashback Drop and recycle bin

- recycle bin default is on, drop table just rename table

- database will clean recycle bin when space not enough

- flashback drop will restore table object (index, trigger….)

e.g   flashback table 'name' to before drop;

                or

        flashback table 'name' to before drop [rename to 'name'];

- manual remove recycle bin by purge command "purge table 'name';"

- to bypass recycle bin use command "drop table 'name' purge;"

- check view by "dba_recyclebin" or user_

03. Flashback database

- like rewind database (normally flashback logs keep 1 to 2 days)

- used in case of logical data corruption made by user (truncate table)

- flashback opposite is "RMAN recover" (when you give up to flashback database)

- cannot perform when controlfile corrupted

- drop tablespace cannot flashback

- flashback database will also use RMAN backupset, archivelog, redo and undo.

- flashback database retention should not over RMAN retention

- enable flashback log in FRA will overhead system 10% performance

e.g.  shutdown immediate;

        startup mount exclusive; <- exclusive = dba single connection

        alter system set db_flashback_retention_target=2880; <- 2 days

        alter database flashback on;

        alter database open;

        select flashback_on from v$database;

        create restore point 'name' guarantee flashback database;

        truncate table 'name';

        shutdown immediate;

        startup mount exclusive;

        <RMAN / SQL> in exclusive mode;

        flashback database to until time "to_date('20150505 23:00:00','yyyymmdd hh24:mi:ss')";

                Or

        flashback database to until scn xxxxxxx;

                or

        flashback database to restore point 'name';

        alter database open read only;

        - check data

        alter database open resetlogs;

- flashback database view by "v$flashback_database_log;"

OCP 11gR2: Administration II 練習筆記 (七)

OCP 11gR2: Administration II 練習筆記 (五)