實驗說明為什麼DataGuard需要設定force logging

LOGGING：物件屬性，表示在建立物件時是否記錄redo log，

NOLOGGING: 儘可能的記錄最少日誌資訊到聯機日誌檔案，一般不建議使用，在建立索引或者大量資料匯入時可以考慮

FORCE LOGGING：簡言之，強制記錄日誌，即對資料庫中的所有操作都產生日誌資訊，並將該資訊寫入到聯機重做日誌檔案。

FORCE LOGGING可以在資料庫級別、表空間級別進行設定、而LOGGING與NOLOGGING可以在資料物件級別設定。

當資料庫使用FORCE LOGGING時，具有最高優先順序別，其次是表空間級別的FORCE LOGGING。即是當一個物件指定NOLOGGING時，而表空間或資料庫級別的日誌模式為FORCE LOGGING，則該選項不起作用，直到表空間或資料庫級別的FORCE LOGGING解除。

開始試驗：

系統資訊：Linux localhost.localdomain 2.6.18-164.el5 #1 SMP Thu Sep 3 03:33:56 EDT 2009 i686 i686 i386 GNU/Linux

資料庫版本：Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

資料庫名：PHYPRIMA

Primary db_unique_name : phyprimary

standby db_unique_name :- phystandby

1 將primary資料庫設定為no force logging 

SQL> alter database no force logging;

Database altered.

SQL> select NAME,DB_UNIQUE_NAME,DATABASE_ROLE,FORCE_LOGGING from v$database;

NAME      DB_UNIQUE_NAME                 DATABASE_ROLE    FOR
--------- ------------------------------ ---------------- ---
PHYPRIMA  phyprimary                     PRIMARY          NO

2 將試驗表空間USERS設定為no force logging(主庫操作)

SQL> select TABLESPACE_NAME,LOGGING ,FORCE_LOGGING  from dba_tablespaces;

TABLESPACE_NAME                LOGGING   FOR
------------------------------ --------- ---
SYSTEM                         LOGGING   NO
SYSAUX                         LOGGING   NO
UNDOTBS1                       LOGGING   NO
TEMP                           NOLOGGING NO
USERS                          LOGGING   NO
EXAMPLE                        NOLOGGING NO

6 rows selected.

3 將測試表(預設表空間為USERS)設定為nologging(主庫操作)

OWNER                          TABLE_NAME                     LOG TABLESPACE_NAME
------------------------------ ------------------------------ --- ------------------------------
USER01                         TEST03                         NO  USERS

4 採用直接載入方式向表中插入資料，不記錄日誌（主庫操作）

SQL> insert /*+ APPEND*/ into test03 select 6 as n from dual;

1 row created.

當在備庫查詢該表時出現如下錯誤資訊

SQL> select * from test03;

ERROR:
ORA-01578: ORACLE data block corrupted (file # 4, block # 544)
ORA-01110: data file 4: '/data/oracle/oradata/phystandby/users01.dbf'
ORA-26040: Data block was loaded using the NOLOGGING option

no rows selected

5 解決方案

備庫操作如下：

SQL> recover managed standby database cancel;
Media recovery complete.

SQL> alter database datafile '/data/oracle/oradata/phystandby/users01.dbf' offline drop;

Database altered.

SQL> recover managed standby database using current logfile disconnect;
Media recovery complete.

SQL> !ls -l /data/oracle/oradata/phystandby/

total 1682208

drwxr-xr-x 2 oracle oinstall      4096 Aug 12 21:16 archivelog

-rw-r----- 1 oracle oinstall 104865792 Aug 12 21:31 example01.dbf

-rw-r----- 1 oracle oinstall  52429312 Aug  7 19:44 redo01.log

-rw-r----- 1 oracle oinstall  52429312 Aug  7 19:44 redo02.log

-rw-r----- 1 oracle oinstall  52429312 Aug  7 19:44 redo03.log

-rw-r----- 1 oracle oinstall  52429312 Aug 12 21:32 standbyredo01.log

-rw-r----- 1 oracle oinstall  52429312 Aug 12 21:14 standbyredo02.log

-rw-r----- 1 oracle oinstall  52429312 Aug 12 20:19 standbyredo03.log

-rw-r----- 1 oracle oinstall 524296192 Aug 12 21:31 sysaux01.dbf

-rw-r----- 1 oracle oinstall 713039872 Aug 12 21:31 system01.dbf

-rw-r----- 1 oracle oinstall  20979712 Aug  6 03:08 temp01.dbf

-rw-r----- 1 oracle oinstall  57679872 Aug 12 21:31 undotbs01.dbf

-rw-r----- 1 oracle oinstall   5251072 Aug 12 21:16 users01.dbf

SQL> !rm /data/oracle/oradata/phystandby/users01.dbf

 來恢復備庫的

RMAN> backup as copy datafile 4;

Starting backup at 12-AUG-14
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=36 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=/data/oracle/oradata/phyprimary/users01.dbf
output file name=/data/oracle/flash_recovery_area/PHYPRIMARY/datafile/o1_mf_users_9yoxpfg3_.dbf tag=TAG20140812T222533 RECID=4 STAMP=855440733
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 12-AUG-14

RMAN> exit


Recovery Manager complete.
[oracle@localhost ~]$ scp /data/oracle/flash_recovery_area/PHYPRIMARY/datafile/o1_mf_users_9yoxpfg3_.dbf oracle@192.168.248.139:/data/oracle/oradata/phystandby/users01.dbf
oracle@192.168.248.139's password:
o1_mf_users_9yoxpfg3_.dbf           

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount;

ORACLE instance started.

Total System Global Area  422670336 bytes

Fixed Size                  1336960 bytes

Variable Size             310380928 bytes

Database Buffers          104857600 bytes

Redo Buffers                6094848 bytes

Database mounted.

SQL> alter database datafile '/data/oracle/oradata/phystandby/users01.dbf' online;

Database altered.

SQL> alter database open;

Database altered.

// 再次檢視備庫資訊，之前在主庫插入的資料資訊已經成功恢復到了備庫

SQL> conn user01/gaoxu
Connected.
SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
TEST01                         TABLE
TEST02                         TABLE
TEST03                         TABLE

SQL> select * from test03;

        ID
----------     
         6

6. 重新將主庫和備庫設定為force logging 模式

SQL> alter database force logging;

Database altered.

SQL> select instance_name from v$instance;

INSTANCE_NAME
----------------
phyprimary

SQL> alter database force logging;

Database altered.

SQL> select instance_name from v$instance;

INSTANCE_NAME
----------------
phystandby

7 重新模擬插入操作，報錯資訊已經消失

主庫操作：

SQL> conn user01/gaoxu
Connected.
SQL> insert /*+ APPEND*/ into test03 select 7 as n from dual;

1 row created.

SQL> commit;

Commit complete.

備庫查詢：

SQL> conn user01/gaoxu
Connected.
SQL> select * from test03;

        ID
----------
         6
         7