oracle强制日志需要重启吗,Oracle 强制日志何时生效的分析

关于Nologging何时才会生效的问题，需要分数据库是否是在归档模式下运行。在归档模式下，如果DB設置為no force logging模式,並且建立的Table为Nologging，则在Insert时，加上append隐函数，则可以大大减少redo size的数量，没有加append隐函数，则跟普通的insert table没有区别。如果建立的Table没有Nologging参数，在执行Insert语句时，无论是否加apend隐函数，均视为正常insert，产生的redo size不会减少。在非归档模下，无论建立Table时，是否有加Nologging参数，执行Inert时，使用了append隐函数，都可以大大减少redo size的数量，不使用append隐函数，redo size数量相当。

创建一个查询每步操作产生的redo size的view。创建redo_size

view的sql是

CREATE VIEW

redo_size

AS

SELECT VALUE

FROM v$mystat, v$statname

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

ANDv$statname.NAME =

'redo size';

以下测试为DB在归档模式下进行

SQL>

archive log list;

Database log modeArchive Mode

Automatic

archivalEnabled

Archive

destination/data/orcl/arch

Oldest

online log sequence 150

Next

log sequence to archive152

Current

log sequence152

1.DB在no force logging模式

SQL>

col force_logging format a20

SQL>

select force_logging from v$database;

FORCE_LOGGING

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

NO

创建logging模式表

SQL>

select value "a0" from redo_size;

a0

----------

0

SQL>

CREATE TABLE redo_test01AS SELECT *

FROM dba_objects WHERE 1=2;

SQL>

select value "a1"from redo_size;

a1

----------

22516

SQL>

insert /*+append*/ intoredo_test01

select * from dba_objects;

72813

rows created.

SQL>

select value "a2" from redo_size;

a2

----------

8593328

SQL>

commit;

Commit

complete.

A2-a1=8570812

SQL>

insert /*+append*/ intoredo_test01

select * from dba_objects;

72813

rows created.

SQL>

select value "a3" from redo_size;

a3

----------

17140116

SQL>

commit;

Commit

complete.

A3-a2=8546788

SQL>

insert intoredo_test01 select * from

dba_objects;

72813

rows created.

SQL>

select value "a4" from redo_size;

a4

----------

25623484

SQL>

commit;

Commit

complete.

A4-a3=8483368

SQL>

insert intoredo_test01 select * from

dba_objects;

72813

rows created.

SQL>

select value "a5" from redo_size;

a5

----------

34103760

SQL>

commit;

Commit

complete.

A5-a4=8480276

创建nologging模式表

SQL>

select value "a0" from redo_size;

a0

----------

0

SQL>

CREATE TABLE redo_test02 nologging AS SELECT *

FROM dba_objects WHERE 1=2;

Table

created.

SQL>

select value "a1"from redo_size;

a1

----------

22576

SQL>

insert /*+append*/ intoredo_test02

select * from dba_objects;

72814

rows created.

SQL>

select value "a2" from redo_size;

a2

----------

63324

SQL>

commit;

Commit

complete.

A2-a1=40748

SQL>

insert /*+append*/ intoredo_test02

select * from dba_objects;

72814

rows created.

SQL>

select value "a3" from redo_size;

a3

----------

79964

SQL>

commit;

Commit

complete.

A3-a2=16640

SQL>

insert intoredo_test02 select * from

dba_objects;

72814

rows created.

SQL>

select value "a4" from redo_size;

a4

----------

8562516

SQL>

commit;

Commit

complete.

A4-a3=8482552

SQL>

insert intoredo_test02 select * from

dba_objects;

72814

rows created.

SQL>

select value "a5" from redo_size;

a5

----------

17044204

SQL>

commit;

Commit

complete.

A5-a4=8481688

综上所述,(1)在DB为no force logging模式,并且所建table为logging(默认值),在插入数据时指定apend隐函数与不指定apend隐函数(/*+append*/),产生的redo相当.

(2)在DB为no force logging模式,并且所建table为nologging,在插入数据时指定apend隐函数(/*+append*/),产生较少的redo,若不指定apend隐函数(/*+append*/),则产生较大的redo.

2.DB在force logging模式

SQL> select force_logging from v$database;

FORCE_LOGGING

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

NO

SQL>

alter database force logging;

Database

altered.

SQL>

select force_logging from v$database;

FORCE_LOGGING

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

YES

创建logging模式表

SQL>

select value "a0" from redo_size;

a0

----------

0

SQL>

CREATE TABLE redo_test03AS SELECT *

FROM dba_objects WHERE 1=2;

Table

created.

SQL>

select value "a1"from redo_size;

a1

----------

23616

SQL>

insert /*+append*/ intoredo_test03

select * from dba_objects;

72812

rows created.

SQL>

select value "a2" from redo_size;

a2

----------

8594428

SQL>

commit;

Commit

complete.

A2-a1=8570812

SQL>

insert /*+append*/ intoredo_test03

select * from dba_objects;

72812

rows created.

SQL>

select value "a3" from redo_size;

a3

----------

17141276

SQL>

commit;

Commit

complete.

A3-a2=8546848

SQL>

insert intoredo_test03 select * from

dba_objects;

72812

rows created.

SQL>

select value "a4" from redo_size;

a4

----------

25624000

SQL>

commit;

Commit

complete.

A4-a3=8482724

SQL>

insert intoredo_test03 select * from

dba_objects;

72812

rows created.

SQL>

select value "a5" from redo_size;

a5

----------

34103920

SQL> commit;

Commit complete.

A5-a4=8479920

创建nologging模式表

SQL>

select value "a0" from redo_size;

a0

----------

0

SQL>

CREATE TABLE redo_test04 nologging AS SELECT * FROM dba_objects WHERE 1=2;

Table

created.

SQL>

select value "a1"from redo_size;

a1

----------

25796

SQL>

insert /*+append*/ intoredo_test04

select * from dba_objects;

72813

rows created.

SQL>

select value "a2" from redo_size;

a2

----------

8596608

SQL>

commit;

Commit

complete.

A2-a1=8570812

SQL>

insert /*+append*/ intoredo_test04

select * from dba_objects;

72813

rows created.

SQL>

select value "a3" from redo_size;

a3

----------

17143368

SQL>

commit;

Commit

complete.

A3-a2=8546760

SQL>

insert intoredo_test04 select * from

dba_objects;

72813

rows created.

SQL>

select value "a4" from redo_size;

a4

----------

25625820

SQL>

commit;

Commit

complete.

A4-a3=8482452

SQL>

insert intoredo_test04 select * from

dba_objects;

72813

rows created.

SQL>

select value "a5" from redo_size;

a5

----------

34106556

SQL>

commit;

Commit

complete.

A5-a4=8480736

综上所述,在DB为force logging模式下,不论所建table为logging(默认值),还是nologging,在插入数据时指定apend隐函数与不指定apend隐函数(/*+append*/),产生的redo相当.