Oracle中哪些操作不被log

sevenchina

Examples of logging operations

alter database datafile '/oracle/oradata/tqdb/users01.dbf' resize 30M;

truncate table t1;

alter tablespace users add datafile '/oracle/oradata/tqdb/users02.dbf' size 5M;

create table t3 as select * from dba_objects;
Examples of nologging operations
Below is a list of examples that can be used for testing purposes.   The database must be in ARCHIVELOG mode and must allow nologging operations to see the effect of nologging changes:

    1. insert /*+ APPEND */ into scott.emp select * from sys.emp2;
    2. create table emp nologging as select * from sys.emp;
    3. create index emp_i on emp(empno) nologging;
    4. sqlload operation with unrecoverable option


Subject:
The Gains and Pains of Nologging Operations

Doc ID
:
Note:290161.1
Type:
BULLETIN

Last Revision Date:
09-NOV-2005
Status:
PUBLISHED
THE GAINS AND PAINS OF NOLOGGING OPERATIONS
OverviewWhereas a logged INSERT operation has to generate redo for every change data or undo block, nologging operations indicate that the database operation is not logged in the online redo log file. Even though a small invalidation redo record¹ is still written to the online redo log file, nologging operations skip the redo generation of the corresponding inserted data.  Nologging can be extremely beneficial for the following reasons:

• data written to the redo is minimized dramatically
  
• time to insert into a large table or index or LOB can be reduced dramatically
  
• performance improves for parallel creation of large tables or indices

However, NOLOGGING is intended for configurations in which media recovery or the recovery of the corresponding object is not important. Thus, if the disk or tape or storage media fails, you will not be able to recover your changes from the redo because the changes were never logged.  
Nologging operations are invoked by any of the following:

• SQL*Loader direct load operations
  
• Direct load INSERT operations from CREATE TABLE | INDEX or INSERT commands
  
• Loading into an object containing LOB data when its object’s segment characteristic is NOCACHE NOLOGGING
  

For databases in ARCHIVELOG mode, nologging operations can only occur for a particular object if and only if:

• Database allows for nologging (ALTER DATABASE NO FORCE LOGGING) and
  
• Tablespace allows for nologging (ALTER TABLESPACE  NO FORCE LOGGING) and
  
• Object allows for nologging (ALTER TABLE  NOLOGGING)
  

This paper will cover the following topics:

• examples of nologging operations
  
• prevention of nologging operations
  
• detection of nologging operations on the primary and standby databases
  
• repair of nologged changes on the physical and logical standby databases

Examples of nologging operationsBelow is a list of examples that can be used for testing purposes.   The database must be in ARCHIVELOG mode and must allow nologging operations to see the effect of nologging changes:

    1. insert /*+ APPEND */ into scott.emp select * from sys.emp2;
    2. create table emp nologging as select * from sys.emp;
    3. create index emp_i on emp(empno) nologging;
    4. sqlload operation with unrecoverable option
Prevention of nologging operationsWhen a standby database exists or if you want all transactions to be recoverable on a database, tablespace or object-wide perspective, it is recommended that you prevent nologging operations by issuing the relevant options.  These options include:

• ALTER DATABASE FORCE LOGGING (database level) or
  
• ALTER TABLESPACE  FORCE LOGGING (tablespace level) on the relevant tablespaces you want to protect or
  
• [CREATE | ALTER] TABLE  FORCE LOGGING (example of object level) on the relevant objects you want to protect
  

This ensures that all transactions are logged and can be recovered through media recovery or Redo Apply or SQL Apply assuming appropriate data type support.
Detection of Nologging Operations On the Primary and Standby DatabasesOn the primary database, you can monitor for the most recent nologging operation that occurred in the database by issuing the following query:

         SELECT NAME, UNRECOVERABLE_CHANGE#,               
         TO_CHAR (UNRECOVERABLE_TIME,'DD-MON-YYYY HH:MI:SS')
         FROM V$DATAFILE;
The above primary database’s query dictates when the most recent nologging operation occurred and when the invalidation redo was written to the redo.   
Once Redo Apply (or Media Recovery) processes the invalidation redo, it marks all the corresponding data blocks corrupt.  You will detect encounter corrupted blocks on the physical standby database when you query any data that references these data blocks.   You will receive the following errors:  
                ORA-01578: ORACLE data block corrupted (file # 3, block # 514)
                ORA-01110: data file 3: '/u01/lto_linux9206/dbs/users.dbf'
                ORA-26040: Data block was loaded using the NOLOGGING option
You can proactively catch some of these corrupted blocks on Redo Apply (or media recovery) instance by running DBVERIFY on the data files.
            
        $ dbv file=users.dbf
        DBVERIFY - Verification starting : FILE = users.dbf
        DBV-00200: Block, dba 12583426, already marked corrupted
        DBV-00200: Block, dba 12583427, already marked corrupted
        DBV-00200: Block, dba 12583428, already marked corrupted
SQL apply ignores the invalidation redo since it cannot convert it to any reasonable SQL; so, the logical standby will not receive any immediate errors.   If future transactions reference the missing data, then apply slave will receive an ORA-01403 in the alert.log.   For example, the following UPDATE statement failed on the logical standby because it was referencing  “nologged” rows that do not exist on the logical standby database.
        LOGSTDBY stmt: update "SCOTT"."NOLOG"
          set
            "SAL" = 810
          where
            "EMPNO" = 7369 and
            "ENAME" = 'SMITH' and
            "JOB" = 'CLERK' and
            "MGR" = 7902 and
            "HIREDATE" = TO_DATE('17-DEC-80', 'DD-MON-RR') and
            "SAL" = 800 and
            "COMM" IS NULL and
            "DEPTNO" = 20 and
            ROWID = 'AAAAAAAAEAAAACRAAA'
        LOGSTDBY status: ORA-01403: no data found
        LOGSTDBY PID 21733, oracle@dlsun1917 (P004)
        LOGSTDBY XID 0x0001.010.00000cf3, Thread 1, RBA 0x038b.00000826.1a4
        Tue Nov  2 18:26:51 2004
        Errors in file /private/oracle/app/admin/tens/bdump/tens_lsp0_20328.trc:
        ORA-12801: error signaled in parallel query server P004
        ORA-01403: no data found
        LOGSTDBY Reader P003 pid=27 OS id=21729 stopped
Currently in Oracle 9i and Oracle 10gR1, only the primary’s database V$DATAFILE view reflects nologging operations..   In 10gR2, the V$DATAFILE view will be enhanced to include information regarding when an invalidation redo is applied and the aforementioned corrupted blocks are written to the corresponding data file on a Redo Apply (or media recovery or standby) instance.  
Repair of Nologged Changes on the Physical and Logical Standby DatabasesAfter a nologged operation on the primary is detected, it is recommended to create a backup immediately if you want to recover from this operation in the future.  However there are additional steps required if you have an existing physical or logical standby database.    This is crucial if you want to preserve the data integrity of your standby databases.
For a physical standby database, Redo Apply will process the invalidation redo and mark the corresponding data blocks corrupt.
For a physical standby database, follow these steps
²
to reinstantiate the relevant data files .
    1. stop Redo Apply (recover managed standby database cancel)
    2. offline corresponding datafile(s) (alter database datafile  offline drop;)
    3. start Redo Apply (recover managed standby database disconnect)
    4. copy the appropriate backup datafiles over from the primary database (e.g. use RMAN to backup datafiles and copy them)
    5. stop Redo Apply (recover managed standby database cancel)
    6. online corresponding data files (alter database datafile  online;)
    7. start Redo Apply (recover managed standby database disconnect)
For a logical standby database, SQL Apply skips over the invalidation redo completely; so, the subsequent corresponding table or index will not be updated.   However, future reference to missing data will result in ORA-1403 (no data found).   In order to resynchronize the table with the primary table, you need to re-create it from the primary database.   Follow the steps described in Oracle Data Guard Concepts and Administration Section 9.1.7.   Basically, you will be using the DBMS_LOGSTDBY.INSTANTIATE_TABLE procedure.
¹Invalidation redo containing information about the nologging operation and the range of blocks it affects.  
²Please also refer to the Data Guard documentation: http://download-west.oracle.com/docs/cd/B14117_01/server.101/b10823/scenarios.htm#1015741


本文来自ChinaUnix博客，如果查看原文请点：http://blog.chinaunix.net/u1/46451/showart_575713.html