Nologging操作对standby的影响

Nologging操作不记录redo,所以就无法传递到standby,导致主备库数据不一致。建议将主库置于force logging模式下，以避免nologging操作带来的麻烦。

一、实验环境
SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
PL/SQL Release 9.2.0.1.0 - Production
CORE 9.2.0.1.0 Production
TNS for 32-bit Windows: Version 9.2.0.1.0 - Production
NLSRTL Version 9.2.0.1.0 - Production

SQL> select force_logging from v$database;

FOR
---
NO

二、Nologging操作

1.在primary上执行
SQL> create table test nologging tablespace test as select * from all_objects where rownum<1001;

Table created.

2.归档当前日志
SQL> alter system archive log current;

System altered.

3.在备库，等刚才传过来的日志应用后，启动到read onlySQL> alter database recover managed standby database finish;

Database altered.

SQL> alter database open read only;

Database altered.

4.执行查询
SQL> select * from test;
select * from test
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 8, block # 10)
ORA-01110: data file 8: 'D:ORACLEORADATATESTTEST01.DBF'
ORA-26040: Data block was loaded using the NOLOGGING option

可以看到，主库的nologging操作不会无法传递到备库来，这样就会造成主备库的数据不一致，丢失了数据。

三、处理方法
在主库上，将受nologging操作影响的datafile做个热备，然后copy到备库覆盖原来的，再重新应用日志

1.查找受nologging操作影响的datafile
SQL> select a.file_name,a.tablespace_name,b.unrecoverable_change# from dba_data_
files a,v$datafile b where a.file_id=b.file#;

FILE_NAME TABLESPACE UNRECOVERABLE_CHANGE#
---------------------------------------- ---------- ---------------------
D:ORACLEORADATANINGSYSTEM01.DBF SYSTEM 0
D:ORACLEORADATANINGUNDOTBS01.DBF UNDOTBS1 0
D:ORACLEORADATANINGCWMLITE01.DBF CWMLITE 0
D:ORACLEORADATANINGEXAMPLE01.DBF EXAMPLE 0
D:ORACLEORADATANINGINDX01.DBF INDX 0
D:ORACLEORADATANINGTOOLS01.DBF TOOLS 0
D:ORACLEORADATANINGUSERS01.DBF USERS 0
D:ORACLEORADATANINGTEST01.DBF TEST 738934

8 rows selected.

2.关闭备库
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

3.从主库备份受影响的test01.dbf到备库

SQL> alter tablespace test begin backup;

Tablespace altered.

在操作系统中复制文件

SQL> alter tablespace test end backup;

Tablespace altered.

4.归档当前日志
SQL> alter system archive log current;

System altered.

5.启动备库并恢复

注意将上一不归档的日志全部copy到备库的归档目的地,需要end backup操作的日志才能将数据库恢复到一致状态
SQL> startup nomount;
ORACLE instance started.

Total System Global Area 135338868 bytes
Fixed Size 453492 bytes
Variable Size 109051904 bytes
Database Buffers 25165824 bytes
Redo Buffers 667648 bytes
SQL> alter database mount standby database;

Database altered.

SQL> alter database open read only;
alter database open read only
*
ERROR at line 1:
ORA-16004: backup database requires recovery
ORA-01195: online backup of file 8 needs more recovery to be consistent
ORA-01110: data file 8: 'D:ORACLEORADATATESTTEST01.DBF'

SQL> recover standby database;
ORA-00279: change 738991 generated at 11/22/2006 10:46:44 needed for thread 1
ORA-00289: suggestion : D:ORACLEARCHTESTARC00100.001
ORA-00280: change 738991 for thread 1 is in sequence #100

Specify log: {=suggested | filename | AUTO | CANCEL}
auto
ORA-00279: change 739327 generated at 11/22/2006 10:53:30 needed for thread 1
ORA-00289: suggestion : D:ORACLEARCHTESTARC00101.001
ORA-00280: change 739327 for thread 1 is in sequence #101
ORA-00278: log file 'D:ORACLEARCHTESTARC00100.001' no longer needed for
this recovery

ORA-00308: cannot open archived log 'D:ORACLEARCHTESTARC00101.001'
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) The system cannot find the file specified.

SQL> alter database open read only;

Database altered.

6.再在备库执行查询

SQL> select count(1) from test;

COUNT(1)
----------
1000

四、将主库置于force logging模式
SQL> alter database force logging;

Database altered.

SQL> select force_logging from v$database;

FOR
---
YES

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/193161/viewspace-50233/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/193161/viewspace-50233/