Wednesday, February 27, 2013

ORACLE - Data Guard and NOLOGGING

At Primary:alter database no force logging;
select force_logging from v$database;
select tablespace_name from dba_tablespaces where logging = 'NOLOGGING';

create table jtable (num number);
insert /*+ APPEND */ into jtable select 1000 from dual nologging;
commit;

select * from jtable;
1000

select file#, unrecoverable_change#, to_char(unrecoverable_time,'hh:mm:ss') from v$datafile
where UNRECOVERABLE_CHANGE# > 0;

     FILE# UNRECOVERABLE_CHANGE# TO_CHAR(
---------- --------------------- --------
         1               2280040 03:02:43


At Stabdby database:
Note: Convert into READ ONLY mode:

select * from jtable;
ERROR:ORA-01578: ORACLE data block corrupted (file # 1, block # 90387)
ORA-01110: data file 1:
'+DG_HMWHIST_01/atest_stby/datafile/system.274.808333075'
ORA-26040: Data block was loaded using the NOLOGGING option

Alert log:Wed Feb 27 15:10:20 2013
Errors in file /u01/app/oracle/diag/rdbms/atest_stby/ATEST/trace/ATEST_ora_21019.trc  (incident=9873):
ORA-01578: ORACLE data block corrupted (file # 1, block # 90387)
ORA-01110: data file 1: '+DG_JDATA_01/atest_stby/datafile/system.274.808333075'
ORA-26040: Data block was loaded using the NOLOGGING option
Incident details in: /u01/app/oracle/diag/rdbms/atest_stby/ATEST/incident/incdir_9873/ATEST_ora_21019_i9873.trc
Wed Feb 27 15:10:22 2013


Solution:ENABLE DATABASE LEVEL LOGGING
DROP and RE-CREATE TABLE WITH DATA

No comments:

Post a Comment