探索_disable_logging的损坏作用--结论1

原创 于 2006-02-24 07:56:00 发布 · 2.6k 阅读 · 0 ·
CC 4.0 BY-SA版权
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
文章标签：

#logging #archive #thread #database #sql #system

本文记录了一次解决Oracle数据库归档错误的过程，通过调整_disable_logging参数解决了因无法归档导致的数据库挂起问题。

摘要生成于 C知道 ，由 DeepSeek-R1 满血版支持， 前往体验 >

SQL> alter database clear logfile group 1;
alter database clear logfile group 1
*
ERROR at line 1:
ORA-00350: log 1 of thread 1 needs to be archived
ORA-00312: online log 1 thread 1: '/oracle/oradata/TSMISC02/redo01.log'


Elapsed: 00:00:00.00
SQL>
SQL> alter database clear unarchived  logfile group 1;

Database altered.

Elapsed: 00:00:00.48
SQL>

SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
         1          1          0    2097152          1 YES UNUSED                  873783 01-DEC-05
         2          1        272    2097152          1 NO  INACTIVE                874641 01-DEC-05
         3          1        273    2097152          1 NO  CURRENT                 875346 01-DEC-05

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

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

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

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

Elapsed: 00:00:00.00
SQL>
SQL> select count(*) from tt;

  COUNT(*)
----------
         4

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

System altered.

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


Elapsed: 00:00:44.13


SQL>

检查日志，参见《alertlog3》

SQL> alter database clear unarchived  logfile group 2;

Database altered.

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

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
         1          1        274    2097152          1 NO  CURRENT                 876000 01-DEC-05
         2          1          0    2097152          1 YES UNUSED                  874641 01-DEC-05
         3          1        273    2097152          1 NO  INACTIVE                875346 01-DEC-05

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

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

Elapsed: 00:00:00.00
SQL>

此时，我没有进行任何操作，但是alert中不停的产生类似下面的信息:
Thu Dec  1 09:01:24 2005
ARC1: Evaluating archive   log 3 thread 1 sequence 273
ARC1: Archiving not possible: No primary destinations
ARC1: Failed to archive log 3 thread 1 sequence 273
Thu Dec  1 09:01:24 2005
Errors in file /oracle/admin/TSMISC02/bdump/tsmisc02_arc1_1706.trc:
ORA-16014: log 3 sequence# 273 not archived, no available destinations
ORA-00312: online log 3 thread 1: '/oracle/oradata/TSMISC02/redo03.log'
详细的参见《alertlog4》

SQL> alter system set "_disable_logging"=false scope=memory;

System altered.

Elapsed: 00:00:00.00
SQL> alter database clear unarchived  logfile group 3;

Database altered.

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

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
         1          1        274    2097152          1 NO  CURRENT                 876000 01-DEC-05
         2          1          0    2097152          1 YES UNUSED                  874641 01-DEC-05
         3          1          0    2097152          1 YES UNUSED                  875346 01-DEC-05

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

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

Elapsed: 00:00:00.01
SQL>


再看日志：
Thu Dec  1 09:15:22 2005
alter database clear unarchived  logfile group 3
Thu Dec  1 09:15:22 2005
WARNING! CLEARING REDO LOG WHICH HAS NOT BEEN ARCHIVED. BACKUPS TAKEN
    BEFORE 12/01/2005 09:00:04 (CHANGE 876000) CANNOT BE USED FOR RECOVERY.
Clearing online log 3 of thread 1 sequence number 273
Completed: alter database clear unarchived  logfile group 3
Thu Dec  1 09:15:24 2005
Archiver process freed from errors. No longer stopped

可以看见，上面的归档错误不再出现了。

现在，重新设置隐含参数：
SQL> alter system set "_disable_logging"=false scope=both;

System altered.

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     274
Next log sequence to archive   274
Current log sequence           274
SQL> alter system switch logfile;

System altered.

Elapsed: 00:00:00.01
SQL> /

System altered.

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

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
         1          1        274    2097152          1 NO  INACTIVE                876000 01-DEC-05
         2          1        275    2097152          1 NO  INACTIVE                876698 01-DEC-05
         3          1        276    2097152          1 NO  CURRENT                 876700 01-DEC-05

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

此时看日志：
Thu Dec  1 09:18:43 2005
Thread 1 advanced to log sequence 275
  Current log# 2 seq# 275 mem# 0: /oracle/oradata/TSMISC02/redo02.log
Thu Dec  1 09:18:43 2005
ARC0: Evaluating archive   log 1 thread 1 sequence 274
ARC0: Archiving not possible: No primary destinations
ARC0: Failed to archive log 1 thread 1 sequence 274
ARCH: Archival stopped, error occurred. Will continue retrying
Thu Dec  1 09:18:43 2005
ORACLE Instance TSMISC02 - Archival Error
ARCH: Connecting to console port...
Thu Dec  1 09:18:43 2005
ORA-16014: log 1 sequence# 274 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 09:18:43 2005
ORA-16014: log 1 sequence# 274 not archived, no available destinations
ORA-00312: online log 1 thread 1: '/oracle/oradata/TSMISC02/redo01.log'
Thu Dec  1 09:18:43 2005
Errors in file /oracle/admin/TSMISC02/bdump/tsmisc02_arc0_1704.trc:
ORA-16014: log 1 sequence# 274 not archived, no available destinations
ORA-00312: online log 1 thread 1: '/oracle/oradata/TSMISC02/redo01.log'
Thu Dec  1 09:18:45 2005
Thread 1 advanced to log sequence 276
  Current log# 3 seq# 276 mem# 0: /oracle/oradata/TSMISC02/redo03.log
Thu Dec  1 09:18:45 2005
ARC0: Evaluating archive   log 1 thread 1 sequence 274
ARC0: Archiving not possible: No primary destinations
ARC0: Failed to archive log 1 thread 1 sequence 274
Thu Dec  1 09:18:45 2005
Errors in file /oracle/admin/TSMISC02/bdump/tsmisc02_arc0_1704.trc:
ORA-16014: log 1 sequence# 274 not archived, no available destinations
ORA-00312: online log 1 thread 1: '/oracle/oradata/TSMISC02/redo01.log'
ARC0: Evaluating archive   log 1 thread 1 sequence 274
ARC0: Archiving not possible: No primary destinations
ARC0: Failed to archive log 1 thread 1 sequence 274
Thu Dec  1 09:18:46 2005
Errors in file /oracle/admin/TSMISC02/bdump/tsmisc02_arc0_1704.trc:
ORA-16014: log 1 sequence# 274 not archived, no available destinations
ORA-00312: online log 1 thread 1: '/oracle/oradata/TSMISC02/redo01.log'
Thu Dec  1 09:18:51 2005
ARC1: Evaluating archive   log 1 thread 1 sequence 274
ARC1: Archiving not possible: No primary destinations
ARC1: Failed to archive log 1 thread 1 sequence 274
Thu Dec  1 09:18:51 2005
Errors in file /oracle/admin/TSMISC02/bdump/tsmisc02_arc1_1706.trc:
ORA-16014: log 1 sequence# 274 not archived, no available destinations
ORA-00312: online log 1 thread 1: '/oracle/oradata/TSMISC02/redo01.log'
。。。。。。。。

SQL> alter database clear unarchived  logfile group 1;

Database altered.

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

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
         1          1          0    2097152          1 YES UNUSED                  876000 01-DEC-05
         2          1        275    2097152          1 NO  INACTIVE                876698 01-DEC-05
         3          1        276    2097152          1 NO  CURRENT                 876700 01-DEC-05

Elapsed: 00:00:00.00
SQL>

相关日志：
Thu Dec  1 09:20:53 2005
alter database clear unarchived  logfile group 1
Thu Dec  1 09:20:53 2005
WARNING! CLEARING REDO LOG WHICH HAS NOT BEEN ARCHIVED. BACKUPS TAKEN
    BEFORE 12/01/2005 09:18:43 (CHANGE 876698) CANNOT BE USED FOR RECOVERY.
Clearing online log 1 of thread 1 sequence number 274
Completed: alter database clear unarchived  logfile group 1
Thu Dec  1 09:21:24 2005
ARC1: Evaluating archive   log 2 thread 1 sequence 275
ARC1: Archiving not possible: No primary destinations
ARC1: Failed to archive log 2 thread 1 sequence 275
ARCH: Archival stopped, error occurred. Will continue retrying
Thu Dec  1 09:21:24 2005
ORACLE Instance TSMISC02 - Archival Error
ARCH: Connecting to console port...
Thu Dec  1 09:21:24 2005
ORA-16014: log 2 sequence# 275 not archived, no available destinations
ORA-00312: online log 2 thread 1: '/oracle/oradata/TSMISC02/redo02.log'
ARCH: Connecting to console port...
ARCH:
Thu Dec  1 09:21:24 2005
ORA-16014: log 2 sequence# 275 not archived, no available destinations
ORA-00312: online log 2 thread 1: '/oracle/oradata/TSMISC02/redo02.log'
Thu Dec  1 09:21:24 2005
Errors in file /oracle/admin/TSMISC02/bdump/tsmisc02_arc1_1706.trc:
ORA-16014: log 2 sequence# 275 not archived, no available destinations
ORA-00312: online log 2 thread 1: '/oracle/oradata/TSMISC02/redo02.log'

我们看见，上面的现象还在不断出现：
SQL> alter database clear unarchived  logfile group 2;

Database altered.

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

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
         1          1          0    2097152          1 YES UNUSED                  876000 01-DEC-05
         2          1          0    2097152          1 YES UNUSED                  876698 01-DEC-05
         3          1        276    2097152          1 NO  CURRENT                 876700 01-DEC-05

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     0
Next log sequence to archive   276
Current log sequence           276
SQL>

 

再看日志：
Thu Dec  1 09:22:38 2005
alter database clear unarchived  logfile group 2
Thu Dec  1 09:22:38 2005
WARNING! CLEARING REDO LOG WHICH HAS NOT BEEN ARCHIVED. BACKUPS TAKEN
    BEFORE 12/01/2005 09:18:45 (CHANGE 876700) CANNOT BE USED FOR RECOVERY.
Clearing online log 2 of thread 1 sequence number 275
Completed: alter database clear unarchived  logfile group 2
Thu Dec  1 09:23:24 2005
Archiver process freed from errors. No longer stopped


SQL> select * from v$log;                            

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
         1          1          0    2097152          1 YES UNUSED                  876000 01-DEC-05
         2          1          0    2097152          1 YES UNUSED                  876698 01-DEC-05
         3          1        276    2097152          1 NO  CURRENT                 876700 01-DEC-05

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

System altered.

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

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
         1          1        277    2097152          1 NO  CURRENT                 876984 01-DEC-05
         2          1          0    2097152          1 YES UNUSED                  876698 01-DEC-05
         3          1        276    2097152          1 YES ACTIVE                  876700 01-DEC-05

Elapsed: 00:00:00.00
SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /oracle/oradata/TSMISC02/archive
Oldest online log sequence     276
Next log sequence to archive   277
Current log sequence           277
SQL>
SQL> alter system switch logfile;

System altered.

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

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
         1          1        277    2097152          1 YES INACTIVE                876984 01-DEC-05
         2          1        278    2097152          1 NO  CURRENT                 877022 01-DEC-05
         3          1        276    2097152          1 YES INACTIVE                876700 01-DEC-05

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

察看日志：
Thu Dec  1 09:26:33 2005
Thread 1 advanced to log sequence 277
  Current log# 1 seq# 277 mem# 0: /oracle/oradata/TSMISC02/redo01.log
Thu Dec  1 09:26:33 2005
ARC0: Evaluating archive   log 3 thread 1 sequence 276
ARC0: Beginning to archive log 3 thread 1 sequence 276
Creating archive destination LOG_ARCHIVE_DEST_1: '/oracle/oradata/TSMISC02/archive/1_276.dbf'
ARC0: Completed archiving  log 3 thread 1 sequence 276
Thu Dec  1 09:27:37 2005
Thread 1 advanced to log sequence 278
  Current log# 2 seq# 278 mem# 0: /oracle/oradata/TSMISC02/redo02.log
Thu Dec  1 09:27:37 2005
ARC1: Evaluating archive   log 1 thread 1 sequence 277
ARC1: Beginning to archive log 1 thread 1 sequence 277
Creating archive destination LOG_ARCHIVE_DEST_1: '/oracle/oradata/TSMISC02/archive/1_277.dbf'
ARC1: Completed archiving  log 1 thread 1 sequence 277


SQL> alter system switch logfile;

System altered.

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

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
         1          1        277    2097152          1 YES INACTIVE                876984 01-DEC-05
         2          1        278    2097152          1 YES ACTIVE                  877022 01-DEC-05
         3          1        279    2097152          1 NO  CURRENT                 877061 01-DEC-05

Elapsed: 00:00:00.00
SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /oracle/oradata/TSMISC02/archive
Oldest online log sequence     277
Next log sequence to archive   279
Current log sequence           279
SQL> alter system switch logfile;

System altered.

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

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
         1          1        280    2097152          1 NO  CURRENT                 877074 01-DEC-05
         2          1        278    2097152          1 YES INACTIVE                877022 01-DEC-05
         3          1        279    2097152          1 YES INACTIVE                877061 01-DEC-05

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

察看日志：
Thu Dec  1 09:28:36 2005
Thread 1 advanced to log sequence 279
  Current log# 3 seq# 279 mem# 0: /oracle/oradata/TSMISC02/redo03.log
Thu Dec  1 09:28:36 2005
ARC0: Evaluating archive   log 2 thread 1 sequence 278
ARC0: Beginning to archive log 2 thread 1 sequence 278
Creating archive destination LOG_ARCHIVE_DEST_1: '/oracle/oradata/TSMISC02/archive/1_278.dbf'
ARC0: Completed archiving  log 2 thread 1 sequence 278
Thu Dec  1 09:28:50 2005
Thread 1 advanced to log sequence 280
  Current log# 1 seq# 280 mem# 0: /oracle/oradata/TSMISC02/redo01.log
Thu Dec  1 09:28:50 2005
ARC1: Evaluating archive   log 3 thread 1 sequence 279
ARC1: Beginning to archive log 3 thread 1 sequence 279
Creating archive destination LOG_ARCHIVE_DEST_1: '/oracle/oradata/TSMISC02/archive/1_279.dbf'
ARC1: Completed archiving  log 3 thread 1 sequence 279


可见，数据库的归档已经正常了。
由此基本断定出，上述问题都是_disable_logging参数的问题。
也就是说，在归档模式的数据库中，如果设置"_disable_logging"=true，
那么，数据库就不能做任何和归档有关的操作，如果数据需要归档，那么数据库就会因为不能归档而挂起。

 

 

 

评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值