資料庫歸檔以及flashbak操作

1：列出歸檔資訊日誌

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

 

2:檢視歸檔日誌位置

SQL> select name,value from v$parameter where name =’log_archive_dest1’;

SQL>select name,value from v$parameter where name=’log_archive_format’;

SQL>select name,value from v$parameter where name=’db_recovery_file_dest’;

SQL> select value from v$parameter where name='db_recovery_file_dest';

或SQL>show parameter db_recovery_file_dest;

Db_recovery_file_dest   string  /ora10gBase/flashback_recovery_area

Db_recovery_file_dest_size integer 2G

 

VALUE

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

/ora10gBase/flash_recovery_area

 

3: 將歸檔日誌根據日期存放在不同子目錄下

• /oradata/[SID]/archivelog/[yyyy-mm-dd]/.arc

SQL>alter system set archive_log_format=’/oradata/[SID]/archivelog/[yyyy-mm-dd]/.arc

 

4:flashback 操作

– 啟動flashback

– 加大UNDO_RETENTION的值86400

– 使用Flashback恢復一個被刪除的表

• 設定資料庫引數，

– 啟動歸檔日誌和閃回

» 設定需要閃回的表屬性options

– Enable Row Movement -> yes

• 閃回表

– 刪除一個表中的記錄delete from scott.dept，再閃回

• 執行Flashback Versions Query

– 修改一條記錄，再閃回

SQL>shutdown immediate;

SQL> startup mount exclusive;

ORACLE instance started.

 

Total System Global Area  197132288 bytes

Fixed Size                  1218484 bytes

Variable Size              67110988 bytes

Database Buffers          125829120 bytes

Redo Buffers                2973696 bytes

Database mounted.

SQL> alter system set recyclebin=on scope=both;--開啟recyclebin

 

System altered.

 

SQL> alter system set db_flashback_retention_target=14400 scope=both;

 

System altered.

SQL> alter database archivelog;

 

Database altered.

 

SQL> alter database flashback on;

 

Database altered.

 

SQL> alter database open;

 

Database altered.

 

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

--修改undo_retention 為20分鐘

SQL> select 20*60*60 from dual;

 

  20*60*60

----------

     72000

 

SQL> alter system set undo_retention=72000 scope=both;

 

System altered.

--解鎖使用者scott 進行相關的增加，刪除，更新操作

SQL> alter user scott identified by tiger account unlock;

 

User altered.

 

SQL> grant resource to scott;

 

Grant succeeded.

 

SQL> conn scott/tiger;

Connected.

 

SQL> alter user scott identified by tiger account unlock;

 

User altered.

 

SQL> grant resource to scott;

 

Grant succeeded.

 

SQL> conn scott/tiger;

Connected.

 

--進行flashback操作之前，需要先把row movement 開啟

SQL>alter table dept enable row movement;

 

 

---進行更新刪除操c作

 

 

SQL> create table test(id number(4),name varchar2(30));

 

Table created.

 

SQL> insert into test values(1,'TEST');

 

1 row created.

 

SQL> insert into test values(2,'Fuck');

 

1 row created.

 

SQL> commit;

 

Commit complete.

 

SQL> drop table test;

 

Table dropped.

 

SQL> show recyclebin;

ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME

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

TEST             BIN$Xkza01ABRArgQAB/AQARTw==$0 TABLE        2008-12-18:15:08:08

 

---從recyclebin中查詢被刪除的資料資訊

SQL> select * from "BIN$Xkza01ABRArgQAB/AQARTw==$0";

 

        ID NAME

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

         1 TEST

         2 Fuck

--從recyvlebin中恢復被刪除的表

SQL> flashback table test to before drop;

 

Flashback complete.

 

SQL> select table_name from user_tables;

 

TABLE_NAME

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

DEPT

EMP

BONUS

SALGRADE

TEST

--恢復後recyclebin中的資訊也相應的消失啦

SQL> show recyclebin;

 

 

4:purge 表操作

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/14766526/viewspace-557766/，如需轉載，請註明出處，否則將追究法律責任。