ORACLE Nologging相关知识

1) Nologging跟数据库的运行模式有关，8i和9i的默认安装都是非归档模式，并且自动归档默认是禁用

SQL> archive log list;

Database log mode             No Archive Mode

Automatic archival             Unabled

Archive destination            C:/oracle/ora92/RDBMS

Oldest online log sequence     85

Current log sequence           87

 

2)关闭数据库

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

 

3)

SQL> startup mount

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

 

Database mounted.

 

4)修改为归档模式

SQL> alter database archivelog;

Database altered.

 

5)打开数据库

SQL> alter database open;

Database altered.

 

6)

SQL> archive log list;

Database log mode             Archive Mode

Automatic archival             Unabled

Archive destination            C:/oracle/ora92/RDBMS

Oldest online log sequence     85

Next online log sequence     87

Current log sequence           87

 

7)由于Automatic archivalà Unabled

SQL> archive log start;

SQL> archive log list;

Database log mode             Archive Mode

Automatic archival             Enabled

Archive destination            C:/oracle/ora92/RDBMS

Oldest online log sequence     85

Next online log sequence     87

Current log sequence           87

 

8)此时推荐关闭数据库，然后进行一下备份

SQL> shutdown immediate

 

9)

SQL> startup

 

10)如果在9i环境中并设置了FORCE LOGGING，则nologging操作是无效的，并不会加快，可以通过如下语句设置为NO FORCE LOGGING。

SQL>Alter database no force logging;

是否开启了FORCE LOGGING，可以用如下语句查看

SQL> select force_logging from v$database;

 

FORCE_

------

NO

11) 一个使用CTAS nologging并比较redo size的例子

SQL> CREATE OR REPLACE VIEW redo_size

  2  AS

  3     SELECT VALUE

  4       FROM v$mystat, v$statname

  5      WHERE v$mystat.statistic# = v$statname.statistic#

  6        AND v$statname.NAME = 'redo size';

 

View created.

 

SQL> connect sys/sys@oratest as sysdba;

 

SQL> create table t nologging as select * from dba_objects;

 

Table created.

 

--T产生的Redo

SQL> select * from redo_size;

 

     VALUE

----------

     48504

 

SQL> create table tt as select * from dba_objects;

 

Table created.

 

SQL> select * from redo_size;

 

     VALUE

----------

   3457796

 

--TT产生的Redo

SQL> select 3457796 - 48504 from dual;

 

3457796-48504

-------------

      3409292

 

SQL> select table_name,logging from dba_tables where table_name in ('T','TT');

 

TABLE_NAME                                               LOGGIN

------------------------------------------------------------ ------

T                                                            NO

TT                                                           YES

　　

参考来源：http://www.itpub.net/showthread.php?s=&threadid=609347&perpage=10&pagenumber=2

 

12)index上使用nologging属性的例子

--创建Table T

colm@COLM> create table t as select * from all_objects;

colm@COLM> connect colm/colm@colm

已连接。

--查看一下当前redo size

colm@COLM> @c:/script/mystat "redo size";

colm@COLM> set echo off

NAME     VALUE

--------------------------

redo size     560

--创建一个索引

colm@COLM> create index t_idx on t(object_name);

 

--查看一下此时redo size

colm@COLM> @c:/script/mystat "redo size";

colm@COLM> set echo off

NAME       VALUE

--------------------------------

redo size      1252680

 

--重建该index

colm@COLM> alter index t_idx rebuild；

索引已更改。

 

--与之前的产生的redo size进行比较，V代表目前的redo ,Diff代表重建所产生的redo,可以发现creat index与alter index产生的redol量基本相等

colm@COLM> @c:/script/mystat2

colm@COLM> set echo off

 

NAME-      V            DIFF

---------- ------------------------------------------

redo size    2511720        1,259,040

 

--alter index nologging

colm@COLM> alter index t_idx nologging;

 

索引已更改。

colm@COLM> @c:/script/mystat2;

colm@COLM> set echo off

 

NAME         V             DIFF

---------- ------------------

redo size       2515968        2,124

 

--再一次重建索引,45K的redo size与之前的1.2M相比少了许多

colm@COLM> alter index t_idx rebuild;

 

索引已更改。

 

colm@COLM> @c:/script/mystat2;

colm@COLM> set echo off

 

NAME              V                DIFF

---------- ------------------

redo size            2559308           45,464

 

附：上面用到的2个脚本

--mystat.sql

set echo off

set verify off

column value new_val V

define S="&1"

 

set autotrace off

select a.name, b.value

from v$statname a, v$mystat b

where a.statistic# = b.statistic#

and lower(a.name) like '%' || lower('&S')||'%'

/

set echo on

 

--mystat2.sql

set echo off

set verify off

column diff format a18

select a.name, b.value V, to_char(b.value-&V,'999,999,999,999') diff

from v$statname a, v$mystat b

where a.statistic# = b.statistic#

and lower(a.name) like '%' || lower('&S')||'%'

/

set echo on

 

13）关于Direct insert append产生redo量的问题， eygle有做过测试

a). 数据库运行在非归档模式下:

create table test as select * from dba_objects where 1=0;

 

insert into test select * from dba_objects;

 

insert

/*+ append */

into test select * from dba_objects;

在Noarchivelog模式下,对于常规表的Direct insert append只产生少量redo

 

create table test nologging as select * from dba_objects where 1=0;

 

insert into test select * from dba_objects;

 

insert

/*+ append */

into test select * from dba_objects;

在Noarchivelog模式下,对于nologging表的Direct insert append也只产生少量redo

 

b).在归档模式下:

create table test as select * from dba_objects where 1=0;

 

insert into test select * from dba_objects;

 

insert

/*+ append */

into test select * from dba_objects;

在归档模式下,对于常规表的insert append产生和insert同样的redo

此时的insert append实际上并不会有性能提高.

但是此时的append是生效了的

 

create table test nologging as select * from dba_objects where 1=0;

 

insert into test select * from dba_objects;

 

insert

/*+ append */

into test select * from dba_objects;

在archivelog模式下,对于nologging表的Direct insert append只产生少量redo，

在这种情况下等价于在Noarchivelog模式下Direct insert append

参考信息：http://www.itpub.net/showthread.php?s=&postid=1618916

 

 

14）关于nologging，direct insert，UNDO三者之间的关系，biti_rainy有过这样的解释

不管表是否在nologging 下，只要是 direct insert，就不会对数据内容生成undo，也就是不会为insert而记录 rowid

在 direct insert 后回滚数据，实际上并没有进行数据的 删除操作

而是仅仅对空间进行了回收。若是删除，不可能只产生这么少的 redo，这里从另一个侧面证明

即使 logging 下的 direct insert 对于回滚信息，也是不会对数据产生 undo 而仅仅产生空间变化的 undo

参考信息http://www.itpub.net/showthread.php?threadid=217094

 

 

小结:

Nologging模式可以使用的操作

1．   CTAS创建表

2．   Index的创建和重建

3．  

Insert

/*+ append */ into tes

t

select

—

直接路径插入

4．   Alter Table操作，如remove,split

 

更多信息http://search.itpub.net/search.php?s=5ee7a3ad7109a32ae7033f52d68be149&action=showresults&searchid=399212&sortby=lastpost&sortorder=descending

 

另外在自己测试时可能觉得需要把buffer cache清空一下，可以参考。

 

 

 

=======================NOLOGGING&&FORCE LOGGING=================================

 

关于nologging:

 

You can create tables and indexes with the CREATE TABLE AS SELECT statement. You can also specify that the database  create them with the NOLOGGING option. When you create a table or index as NOLOGGING, the database does not generate  redo log records for the operation. Thus, you cannot recover objects created with NOLOGGING, even if you are  running in ARCHIVELOG mode.

Be aware that when you perform media recovery, and some tables or indexes are created normally whereas others are created  with the NOLOGGING option, the NOLOGGING objects are marked logically corrupt by the RECOVER operation.
Any attempt to access the unrecoverable objects returns an ORA-01578 error message. Drop the NOLOGGING objects and re-create  them if needed.

关于Force logging
Specifying FORCE LOGGING Mode
Some data definition language statements (such as CREATE TABLE) allow the NOLOGGING clause, which causes some database operations not to generate redo records in the database redo log. The NOLOGGING setting can speed up operations that can be easily recovered outside of the database recovery mechanisms, but it can negatively affect media recovery and standby databases.

Oracle Database lets you force the writing of redo records even when NOLOGGING has been specified in DDL statements. The database never generates redo records for temporary tablespaces and temporary segments, so forced logging has no affect for objects.

 

nologging,logging,force logging分为对象级(DDL创建表或索引的时候使用了nologging或其他)，表空间级，数据库级。force logging会比nologging“权利" 更大

force logging就是忽略nologging

 

查询数据库当前FORCE_LOGGING的设置

SQL> select force_logging from v$database;

FOR
---
NO

SQL>