ITPub博客
首页 博客 专栏 学院 社区 IT新媒体评选
发博

首页 > Linux操作系统 > Linux操作系统 > oracle 9i数据库夯住无法归档

oracle 9i数据库夯住无法归档

原创 Linux操作系统 作者：tian1982tian 时间：2012-09-19 15:14:46 0 删除 编辑
oracle 9i数据库，在库里插入数据夯住，用spotlight监控redo无法归档，
在数据库里：SQL> alter system switch logfile;命令发出之后一小时
甚至更长时间夯住不动
[oracle@oracle9ivm ~]$ sqlplus /nolog
SQL*Plus: Release 9.2.0.4.0 - Production on Mon Sep 19 09:59:35 2011
Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.
SQL> conn / as sysdba
Connected.
SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Disabled
Archive destination            /u01/oracle/dbs/arch
Oldest online log sequence     148
Next log sequence to archive   148
Current log sequence           150
SQL> show parameter archive
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
archive_lag_target                   integer     0
log_archive_dest                     string
log_archive_dest_1                   string
log_archive_dest_10                  string
log_archive_dest_2                   string
log_archive_dest_3                   string
log_archive_dest_4                   string
log_archive_dest_5                   string
log_archive_dest_6                   string
log_archive_dest_7                   string
log_archive_dest_8                   string
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_9                   string
log_archive_dest_state_1             string      enable
log_archive_dest_state_10            string      enable
log_archive_dest_state_2             string      enable
log_archive_dest_state_3             string      enable
log_archive_dest_state_4             string      enable
log_archive_dest_state_5             string      enable
log_archive_dest_state_6             string      enable
log_archive_dest_state_7             string      enable
log_archive_dest_state_8             string      enable
log_archive_dest_state_9             string      enable
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_duplex_dest              string
log_archive_format                   string      %t_%s.dbf
log_archive_max_processes            integer     2
log_archive_min_succeed_dest         integer     1
log_archive_start                    boolean     FALSE
log_archive_trace                    integer     0
remote_archive_enable                string      true
standby_archive_dest                 string      ?/dbs/arch
经查看：Automatic archival 为 Disabled
        归档路径为默认路径
以下试图启用归档：
SQL> alter system set log_archive_start=true;
alter system set log_archive_start=true
                 *
ERROR at line 1:
ORA-02095: specified initialization parameter cannot be modified

SQL> alter system set log_archive_start=true scop=spfile;
alter system set log_archive_start=true scop=spfile
                 *
ERROR at line 1:
ORA-02095: specified initialization parameter cannot be modified

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area  236000356 bytes
Fixed Size                   451684 bytes
Variable Size             201326592 bytes
Database Buffers           33554432 bytes
Redo Buffers                 667648 bytes
Database mounted.
SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Disabled
Archive destination            /u01/oracle/dbs/arch
Oldest online log sequence     148
Next log sequence to archive   148
Current log sequence           150
SQL> show parameter archive
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
archive_lag_target                   integer     0
log_archive_dest                     string
log_archive_dest_1                   string
log_archive_dest_10                  string
log_archive_dest_2                   string
log_archive_dest_3                   string
log_archive_dest_4                   string
log_archive_dest_5                   string
log_archive_dest_6                   string
log_archive_dest_7                   string
log_archive_dest_8                   string
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_9                   string
log_archive_dest_state_1             string      enable
log_archive_dest_state_10            string      enable
log_archive_dest_state_2             string      enable
log_archive_dest_state_3             string      enable
log_archive_dest_state_4             string      enable
log_archive_dest_state_5             string      enable
log_archive_dest_state_6             string      enable
log_archive_dest_state_7             string      enable
log_archive_dest_state_8             string      enable
log_archive_dest_state_9             string      enable
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_duplex_dest              string
log_archive_format                   string      %t_%s.dbf
log_archive_max_processes            integer     2
log_archive_min_succeed_dest         integer     1
log_archive_start                    boolean     FALSE
log_archive_trace                    integer     0
remote_archive_enable                string      true
standby_archive_dest                 string      ?/dbs/arch
SQL> alter database archivelog;
Database altered.
SQL> alter system set log_archive_dest_1='LOCTION=/u01/oradata/ora10g/arch';
alter system set log_archive_dest_1='LOCTION=/u01/oradata/ora10g/arch'
*
ERROR at line 1:
ORA-32017: failure in updating SPFILE
ORA-16179: incremental changes to "log_archive_dest_1" not allowed with SPFILE

SQL> alter database open;
Database altered.
SQL> alter system set log_archive_dest_1='LOCATION=/u01/oradata/ora10g/arch';
System altered.
SQL>  show parameter archive
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
archive_lag_target                   integer     0
log_archive_dest                     string
log_archive_dest_1                   string      LOCATION=/u01/oradata/ora10g/a
                                                 rch
log_archive_dest_10                  string
log_archive_dest_2                   string
log_archive_dest_3                   string
log_archive_dest_4                   string
log_archive_dest_5                   string
log_archive_dest_6                   string
log_archive_dest_7                   string
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_8                   string
log_archive_dest_9                   string
log_archive_dest_state_1             string      enable
log_archive_dest_state_10            string      enable
log_archive_dest_state_2             string      enable
log_archive_dest_state_3             string      enable
log_archive_dest_state_4             string      enable
log_archive_dest_state_5             string      enable
log_archive_dest_state_6             string      enable
log_archive_dest_state_7             string      enable
log_archive_dest_state_8             string      enable
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_state_9             string      enable
log_archive_duplex_dest              string
log_archive_format                   string      %t_%s.dbf
log_archive_max_processes            integer     2
log_archive_min_succeed_dest         integer     1
log_archive_start                    boolean     FALSE
log_archive_trace                    integer     0
remote_archive_enable                string      true
standby_archive_dest                 string      ?/dbs/arch
SQL>
SQL>
SQL> SET linesize 400
SQL> select * from v$log;
    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
         1          1        149  104857600          1 NO  INACTIVE               5698651 19-SEP-11
         2          1        150  104857600          1 NO  CURRENT                5719360 19-SEP-11
         3          1        148  104857600          1 NO  INACTIVE               5667386 19-SEP-11
SQL> alter system switch logfile;
alter system switch logfile
*
ERROR at line 1:
ORA-01013: user requested cancel of current operation

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Disabled
Archive destination            /u01/oradata/ora10g/arch
Oldest online log sequence     148
Next log sequence to archive   148
Current log sequence           150
SQL> alter system set log_archive_start=true;
alter system set log_archive_start=true
                 *
ERROR at line 1:
ORA-02095: specified initialization parameter cannot be modified

SQL> alter system set log_archive_start=true scope=both;
alter system set log_archive_start=true scope=both
                 *
ERROR at line 1:
ORA-02095: specified initialization parameter cannot be modified
将log_archive_start 值由 FALSE 修改为true写入spfile,重启动数据库生效
SQL> alter system set log_archive_start=true scope=spfile;
System altered.
SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Disabled
Archive destination            /u01/oradata/ora10g/arch
Oldest online log sequence     148
Next log sequence to archive   148
Current log sequence           150
SQL> shutdown immediaet;
SP2-0717: illegal SHUTDOWN option
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area  236000356 bytes
Fixed Size                   451684 bytes
Variable Size             201326592 bytes
Database Buffers           33554432 bytes
Redo Buffers                 667648 bytes
Database mounted.
Database opened.
SQL> select * from v$log;
    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
         1          1        149  104857600          1 YES INACTIVE               5698651 19-SEP-11
         2          1        150  104857600          1 NO  CURRENT                5719360 19-SEP-11
         3          1        148  104857600          1 YES INACTIVE               5667386 19-SEP-11
SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u01/oradata/ora10g/arch
Oldest online log sequence     148
Next log sequence to archive   150
Current log sequence           150
SQL> alter system switch logfile;
System altered.
SQL>  select * from v$log;
    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
         1          1        149  104857600          1 YES INACTIVE               5698651 19-SEP-11
         2          1        150  104857600          1 YES ACTIVE                 5719360 19-SEP-11
         3          1        151  104857600          1 NO  CURRENT                5753805 19-SEP-11
SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
Session altered.
SQL> select * from v$log;
    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- -------------------
         1          1        149  104857600          1 YES INACTIVE               5698651 2011-09-19 06:19:08
         2          1        150  104857600          1 YES ACTIVE                 5719360 2011-09-19 06:21:01
         3          1        151  104857600          1 NO  CURRENT                5753805 2011-09-19 10:11:50
SQL> alter system switch logfile;
System altered.
SQL> select * from v$log;
    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- -------------------
         1          1        152  104857600          1 NO  CURRENT                5753971 2011-09-19 10:12:56
         2          1        150  104857600          1 YES INACTIVE               5719360 2011-09-19 06:21:01
         3          1        151  104857600          1 YES ACTIVE                 5753805 2011-09-19 10:11:50
SQL> alter system switch logfile;
System altered.
SQL> select * from v$log;
    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- -------------------
         1          1        152  104857600          1 YES ACTIVE                 5753971 2011-09-19 10:12:56
         2          1        153  104857600          1 NO  CURRENT                5754053 2011-09-19 10:13:31
         3          1        151  104857600          1 YES INACTIVE               5753805 2011-09-19 10:11:50
SQL>
至此，redo 日志可以正常的归档了，且用spotlight观察也不会再出现警告了

来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/16400082/viewspace-744235/ ，如需转载，请注明出处，否则将追究法律责任。

0
0
上一篇： 关于append sql hint的实验
下一篇： 关机后redo文件丢失重建方法
请登录后发表评论 登录
全部评论
tian1982tian

注册时间：2010-12-29

  • 博文量
    70
  • 访问量
    176790

最新文章

支持我们 作者招募 用户协议 FAQ Contact Us

北京盛拓优讯信息技术有限公司. 版权所有  京ICP备16024965号-8 北京市公安局海淀分局网监中心备案编号：11010802021510 niuxiaotong@pcpop.com 17352615567 未成年人举报专区

广播电视节目制作经营许可证(京) 字第1234号 中国互联网协会会员

违法和不良信息举报电话:18101376593