_disable_logging参数研究系列（一）

ORACLE 的隐含参数_disable_logging主要用于禁用日志生成（一般建议在oracle support的指导下用于非生产环境下），不难想象，如果禁止日志生成，那么必然导致事务的不可恢复性。

对于这个参数的一些破坏作用做了一些测试：

SQL> select * from v$instance;

INSTANCE_NUMBER INSTANCE_NAME    HOST_NAME                                                        VERSION           STARTUP_T
--------------- ---------------- ---------------------------------------------------------------- ----------------- ---------
STATUS       PAR    THREAD# ARCHIVE LOG_SWITCH_ LOGINS     SHU DATABASE_STATUS   INSTANCE_ROLE      ACTIVE_ST
------------ --- ---------- ------- ----------- ---------- --- ----------------- ------------------ ---------
              1 TSMISC02         ts01                                                             9.2.0.6.0         30-NOV-05
OPEN         NO           1 STARTED             ALLOWED    NO ACTIVE            PRIMARY_INSTANCE   NORMAL

Elapsed: 00:00:00.00
SQL>
SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /oracle/oradata/TSMISC02/archive
Oldest online log sequence     270
Next log sequence to archive   271
Current log sequence           271
SQL>

SQL> select ksppinm,ksppdesc from x$ksppi where ksppinm like '%logging';

KSPPINM KSPPDESC
---------------------------------------------------------------- ----------------------------------------------------------------
_disable_logging Disable logging

Elapsed: 00:00:00.00
SQL>
SQL> alter system set "_disable_logging"=true scope=both;

System altered.

Elapsed: 00:00:00.00
SQL>
SQL> create table t as select * from user_tables;

Table created.

Elapsed: 00:00:00.25
SQL> select count(*) from t;

COUNT(*)
----------
340

System altered.

Elapsed: 00:00:01.49
SQL> /

hang。。。。。。。

检查日志：
Thu Dec 1 08:14:24 2005
ARC1: Evaluating archive   log 1 thread 1 sequence 269
ARC1: Beginning to archive log 1 thread 1 sequence 269
Creating archive destination LOG_ARCHIVE_DEST_1: '/oracle/oradata/TSMISC02/archive/1_269.dbf'
ARC1: Completed archiving log 1 thread 1 sequence 269
Thu Dec 1 08:14:27 2005
Thread 1 cannot allocate new log, sequence 271
Checkpoint not complete
Current log# 2 seq# 270 mem# 0: /oracle/oradata/TSMISC02/redo02.log
Thread 1 advanced to log sequence 271
Current log# 1 seq# 271 mem# 0: /oracle/oradata/TSMISC02/redo01.log
Thu Dec 1 08:14:28 2005
ARC0: Evaluating archive   log 2 thread 1 sequence 270
ARC0: Beginning to archive log 2 thread 1 sequence 270
Creating archive destination LOG_ARCHIVE_DEST_1: '/oracle/oradata/TSMISC02/archive/1_270.dbf'
ARC0: Completed archiving log 2 thread 1 sequence 270
Thu Dec 1 08:22:15 2005
ALTER SYSTEM SET _disable_logging=TRUE SCOPE=BOTH;
Thu Dec 1 08:24:43 2005
Thread 1 advanced to log sequence 272
Current log# 2 seq# 272 mem# 0: /oracle/oradata/TSMISC02/redo02.log
Thu Dec 1 08:24:43 2005
ARC1: Evaluating archive   log 1 thread 1 sequence 271
ARC1: Beginning to archive log 1 thread 1 sequence 271
Creating archive destination LOG_ARCHIVE_DEST_1: '/oracle/oradata/TSMISC02/archive/1_271.dbf'
ARC1: Log corruption near block 2749 change 0 time ?
ARC1: All Archive destinations made inactive due to error 354
Thu Dec 1 08:24:43 2005
Errors in file /oracle/admin/TSMISC02/bdump/tsmisc02_arc1_1706.trc:
ORA-00354: corrupt redo log block header
ORA-00353: log corruption near block 2749 change 0 time 12/01/2005 08:22:01
ORA-00312: online log 1 thread 1: '/oracle/oradata/TSMISC02/redo01.log'
ARC1: Archiving not possible: error count exceeded
ARC1: Failed to archive log 1 thread 1 sequence 271
ARCH: Archival stopped, error occurred. Will continue retrying
Thu Dec 1 08:24:43 2005
ORACLE Instance TSMISC02 - Archival Error
ARCH: Connecting to console port...
Thu Dec 1 08:24:43 2005
ORA-16038: log 1 sequence# 271 cannot be archived
ORA-00354: corrupt redo log block header
ORA-00312: online log 1 thread 1: '/oracle/oradata/TSMISC02/redo01.log'
ARCH: Connecting to console port...
ARCH:
Thu Dec 1 08:24:43 2005
ORA-16038: log 1 sequence# 271 cannot be archived
ORA-00354: corrupt redo log block header
ORA-00312: online log 1 thread 1: '/oracle/oradata/TSMISC02/redo01.log'
Thu Dec 1 08:24:43 2005
Errors in file /oracle/admin/TSMISC02/bdump/tsmisc02_arc1_1706.trc:
ORA-16038: log 1 sequence# 271 cannot be archived
ORA-00354: corrupt redo log block header
ORA-00312: online log 1 thread 1: '/oracle/oradata/TSMISC02/redo01.log'
ARC1: Evaluating archive   log 1 thread 1 sequence 271
ARC1: Archiving not possible: No primary destinations
ARC1: Failed to archive log 1 thread 1 sequence 271
ARCH: Archival stopped, error occurred. Will continue retrying
Thu Dec 1 08:24:47 2005
ORACLE Instance TSMISC02 - Archival Error
ARCH: Connecting to console port...
Thu Dec 1 08:24:47 2005
ORA-16014: log 1 sequence# 271 not archived, no available destinations
ORA-00312: online log 1 thread 1: '/oracle/oradata/TSMISC02/redo01.log'
ARCH: Connecting to console port...
ARCH:
Thu Dec 1 08:24:47 2005
ORA-16014: log 1 sequence# 271 not archived, no available destinations
ORA-00312: online log 1 thread 1: '/oracle/oradata/TSMISC02/redo01.log'
Thu Dec 1 08:24:47 2005
Errors in file /oracle/admin/TSMISC02/bdump/tsmisc02_arc1_1706.trc:
ORA-16014: log 1 sequence# 271 not archived, no available destinations
ORA-00312: online log 1 thread 1: '/oracle/oradata/TSMISC02/redo01.log'
Thu Dec 1 08:24:52 2005
ARC0: Evaluating archive   log 1 thread 1 sequence 271
ARC0: Archiving not possible: No primary destinations
ARC0: Failed to archive log 1 thread 1 sequence 271
Thu Dec 1 08:24:52 2005
Errors in file /oracle/admin/TSMISC02/bdump/tsmisc02_arc0_1704.trc:
ORA-16014: log 1 sequence# 271 not archived, no available destinations
ORA-00312: online log 1 thread 1: '/oracle/oradata/TSMISC02/redo01.log'
Thu Dec 1 08:24:57 2005
ARC1: Evaluating archive   log 1 thread 1 sequence 271
ARC1: Archiving not possible: No primary destinations
ARC1: Failed to archive log 1 thread 1 sequence 271
Thu Dec 1 08:24:57 2005
Errors in file /oracle/admin/TSMISC02/bdump/tsmisc02_arc1_1706.trc:
ORA-16014: log 1 sequence# 271 not archived, no available destinations
ORA-00312: online log 1 thread 1: '/oracle/oradata/TSMISC02/redo01.log'
Thu Dec 1 08:25:02 2005
ARC0: Evaluating archive   log 1 thread 1 sequence 271
ARC0: Archiving not possible: No primary destinations
ARC0: Failed to archive log 1 thread 1 sequence 271
Thu Dec 1 08:25:02 2005
Errors in file /oracle/admin/TSMISC02/bdump/tsmisc02_arc0_1704.trc:
ORA-16014: log 1 sequence# 271 not archived, no available destinations
ORA-00312: online log 1 thread 1: '/oracle/oradata/TSMISC02/redo01.log'
。。。。。。。

[oracle@ts01 oracle]$ df -k
Filesystem           1K-blocks      Used Available Use% Mounted on
/dev/hda6              1510032    248972   1184352 18% /
/dev/hda1               101089      9194     86676 10% /boot
/dev/hda3              6143268    732928   5098280 13% /home
/dev/hda8              2016016     33656   1879948   2% /opt
/dev/hda2             20161204   6880756 12256308 36% /oracle
none                    514804         0    514804   0% /dev/shm
/dev/hda9               443345      8811    411643   3% /tmp
/dev/hda5              6048352   2060476   3680636 36% /usr
[oracle@ts01 oracle]$

取消了切换日志的操作，检查日志文件的状态：
SQL> /

alter system switch logfile
*
ERROR at line 1:
ORA-01013: user requested cancel of current operation

Elapsed: 00:13:57.77

SQL> select * from v$log;

    GROUP#    THREAD# SEQUENCE#      BYTES    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
         1          1        271    2097152          1 NO INACTIVE                873783 01-DEC-05
         2          1        272    2097152          1 NO CURRENT                 874641 01-DEC-05

Elapsed: 00:00:00.01
SQL> col member for a50
SQL> select * from v$logfile;

    GROUP# STATUS TYPE    MEMBER
---------- ------- ------- --------------------------------------------------
         1         ONLINE /oracle/oradata/TSMISC02/redo01.log
         2         ONLINE /oracle/oradata/TSMISC02/redo02.log

Elapsed: 00:00:00.00
SQL>
SQL> alter database add logfile group 3 ('/oracle/oradata/TSMISC02/redo03.log') size 2M;

Database altered.

Elapsed: 00:00:00.13
SQL> select * from v$log;

Elapsed: 00:00:00.00
SQL> select * from v$logfile;

Elapsed: 00:00:00.00
SQL>
SQL> alter system switch logfile;

System altered.

Elapsed: 00:00:00.01
SQL> /
hang。。。。。

检查日志，和前面的错误差不多，取消掉归档
SQL> alter system switch logfile
2 /
alter system switch logfile
*
ERROR at line 1:
ORA-01013: user requested cancel of current operation

Elapsed: 00:00:01.37

SQL> create table tt as select * from dba_users;

Table created.

Elapsed: 00:00:00.07
SQL> alter system switch logfile;
alter system switch logfile
*
ERROR at line 1:
ORA-01013: user requested cancel of current operation

Elapsed: 00:00:56.51

SQL>
SQL> select * from v$log;

Elapsed: 00:00:00.00
SQL> select * from v$logfile;

Elapsed: 00:00:00.00
SQL>
SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /oracle/oradata/TSMISC02/archive
Oldest online log sequence     271
Next log sequence to archive   271
Current log sequence           273
SQL>

检查日志：
参见《alertlog2》

秒客网

_disable_logging参数研究系列（一）

相关文章