Nologging到底何时才能生效?

solaris8

最初由 grassbell 发布
[B]对于每个数据文件，都有一个unrecoverable SCN 记录。

执行nologging以后，会在控制文件中相应的数据文件部分记录下这个unrecoverable SCN ，表示这个数据文件从这个scn后是不可恢复的。主要用于RMAN做恢复时的判断。


SQL> create table nologtab nologging
  2  as select * from dba_users;

Table created.

***************************************************************************
DATA FILE RECORDS
***************************************************************************
(blkno = 0x16, size = 180, max = 254, in-use = 4, last-recid= 3)
DATA FILE #1:
  (name #6) D:\ORACLE\ORADATA\DONNYDB2\SYSTEM01.DBF
creation size=0 block size=8192 status=0xe head=6 tail=6 dup=1
tablespace 0, index=1 krfil=1 prev_file=0
[B]unrecoverable scn: 0x0000.0064a5db[/B] 07/18/2004 16:02:45
Checkpoint cnt:830 scn: 0x0000.00649757 07/18/2004 14:52:50 [/B]

这个unrecoverable SCN 是不是设置了10359事件后,就不会更新控制文件?

http://www.itpub.net/showthread.php?s=&threadid=550045

clar

好文章，顶

zixin31

学习到东西了，那如何控制delete不产生redo哪？？

rollingpig

基本不可能。

处理历史数据建议使用partition

最初由 zixin31 发布
[B]学习到东西了，那如何控制delete不产生redo哪？？ [/B]

Steven_1981

HYF on 2007-04-05 09:01:13 >conn / as sysdba
Connected.
SYS on 2007-04-05 09:01:18 >archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /SERVER/ora10/arc
Oldest online log sequence     235
Next log sequence to archive   237
Current log sequence           237
SYS on 2007-04-05 09:01:22 >conn hyf/hyf
Connected.
HYF on 2007-04-05 09:01:28 >create table h nologging as select * from dba_objects where 1=2;

Table created.

HYF on 2007-04-05 09:01:47 >select logging from dba_tables where table_name='H';

LOG
---
NO

1 row selected.

HYF on 2007-04-05 09:02:03 >select * from redo_size;

     VALUE
----------
     20096

1 row selected.

HYF on 2007-04-05 09:02:12 >insert into h select * from dba_objects;

50102 rows created.

HYF on 2007-04-05 09:02:33 >select * from redo_size;

     VALUE
----------
   5687740

1 row selected.

HYF on 2007-04-05 09:02:36 >insert /* + append */ into h select * from dba_objects;

50102 rows created.

HYF on 2007-04-05 09:02:52 >select * from redo_size;

     VALUE
----------
  11309548

1 row selected.

HYF on 2007-04-05 09:02:55 >select 11309548-5687740,5687740-20096 from dual;

11309548-5687740 5687740-20096
---------------- -------------
         5621808       5667644

1 row selected.

Steven_1981

HYF on 2007-04-05 09:22:17 >select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0      Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

HYF on 2007-04-05 09:26:23 >host uname -a
Linux localhost.localdomain 2.4.21-4.EL #1 Fri Oct 3 17:52:56 EDT 2003 i686 athlon i386 GNU/Linux

Steven_1981

Microsoft Windows XP [版本 5.1.2600]
(C) 版权所有 1985-2001 Microsoft Corp.

C:\Documents and Settings\Steven_he>sqlplus user/passwd@abcd

SQL*Plus: Release 9.0.1.0.1 - Production on 星期四 4月 5 09:31:39 2007

(c) Copyright 2001 Oracle Corporation.  All rights reserved.


连接到:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production

SQL> archive log list;
ORA-01031: insufficient privileges  (确定是归档.生产库)

SQL> create table hyf_test nologging as select * from dba_objects where 1=2;

表已创建。

SQL> select  b.value from v$statname a, v$mystat b
  2  where a.statistic#=b.statistic# and a.name='redo size';

     VALUE
----------
     40580

SQL> select logging from dba_tables where table_name='HYF_TEST';

LOG
---
NO

SQL> insert into hyf_test select * from dba_objects;

已创建10451行。

SQL> select  b.value from v$statname a, v$mystat b
  2  where a.statistic#=b.statistic# and a.name='redo size';

     VALUE
----------
   1156140

SQL> insert /* + append */ into hyf_test select * from dba_objects;

已创建10451行。

SQL> select  b.value from v$statname a, v$mystat b
  2  where a.statistic#=b.statistic# and a.name='redo size';

     VALUE
----------
   2231840

SQL> select 2231840-1156140,1156140-40580 from dual;

2231840-1156140 1156140-40580
--------------- -------------
        1075700       1115560

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
PL/SQL Release 9.2.0.4.0 - Production
CORE    9.2.0.3.0       Production
TNS for Linux: Version 9.2.0.4.0 - Production
NLSRTL Version 9.2.0.4.0 - Production

SQL>

Steven_1981

顶上来,

NinGoo

楼上的，你的库是不是设置过force logging？
select force_logging from v$database;

Steven_1981

SQL> select force_logging from v$database;

FOR
---
NO

SQL>