实验说明为什么DataGuard需要设置force logging

原文地址：http://blog.itpub.net/28719055/viewspace-1250522/

首先简单说明一下记录日志的三种模式

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

//使用主库的数据文件user01.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