oracle 9i資料庫夯住無法歸檔
oracle 9i資料庫，在庫裡插入資料夯住，用spotlight監控redo無法歸檔，
在資料庫裡：SQL> alter system switch logfile;命令發出之後一小時
甚至更長時間夯住不動
在資料庫裡：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
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
------------------------------------ ----------- ------------------------------
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
------------------------------------ ----------- ------------------------------
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
------------------------------------ ----------- ------------------------------
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;
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
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
------------------------------------ ----------- ------------------------------
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
------------------------------------ ----------- ------------------------------
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
------------------------------------ ----------- ------------------------------
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
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
------------------------------------ ----------- ------------------------------
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
------------------------------------ ----------- ------------------------------
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;
------------------------------------ ----------- ------------------------------
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
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
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
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;
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.
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;
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
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
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;
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
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
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
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- -------------------
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
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- -------------------
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
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- -------------------
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部落格 ” ，連結：http://blog.itpub.net/16400082/viewspace-744235/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- oracle adg備庫歸檔滿了無法同步Oracle
- oracle 9i資料庫做spaOracle資料庫
- oracle adg主庫通過rman無法刪除歸檔Oracle
- Oracle資料庫歸檔模式的切換ELOracle資料庫模式
- oracle資料庫歸檔日誌量陡增分析Oracle資料庫
- Oracle資料庫日常問題-歸檔異常增長Oracle資料庫
- Oracle資料庫歸檔模式的開啟和關閉Oracle資料庫模式
- SQL資料庫怎麼進行資料歸檔和歸檔管理？SQL資料庫
- Oracle資料庫分割槽表SPLIT操作導致歸檔瘋漲Oracle資料庫
- RAC開啟資料庫歸檔資料庫
- Oracle日常問題-資料庫無法啟動(案例二)Oracle資料庫
- Oracle日常問題處理-資料庫無法啟動Oracle資料庫
- Oracle 12.2應用PSU後資料庫無法啟動Oracle資料庫
- Oracle 19.3資料庫impdp匯入view時hang住Oracle資料庫View
- 資料庫資料恢復-oracle資料庫報錯無法開啟的如何恢復資料？資料庫資料恢復Oracle
- Polardb-O資料庫歸檔配置資料庫
- oracle歸檔Oracle
- ORACLE DSG資料同步軟體程式導致資料庫無法正常關閉Oracle資料庫
- oracle資料庫的配置檔案Oracle資料庫
- 【資料庫資料恢復】Oracle ASM例項無法掛載的資料恢復案例資料庫資料恢復OracleASM
- Sybase或SQLServer資料庫分批歸檔方案SQLServer資料庫
- 資料庫監聽夯故障分析資料庫
- iis網站資料庫無法連線資料庫網站資料庫
- 無外網Oracle資料庫遷移Oracle資料庫
- file-max設定過小導致oracle資料庫hang住Oracle資料庫
- oracle uncatalog資料庫備份檔案Oracle資料庫
- 達夢資料庫歸檔方式及其配置方法資料庫
- Oralce資料庫關閉歸檔日誌並且刪除歸檔日誌資料庫
- Oracle檢視歸檔是否被備庫應用Oracle
- oracle歸檔日誌Oracle
- LightBD/PostgreSQL資料庫設定歸檔保留時間SQL資料庫
- oracle dg庫資料檔案空間不足Oracle
- 3節點RAC資料庫夯故障分析資料庫
- [重慶思莊每日技術分享]-RMAN-08137 主庫無法刪除歸檔檔案
- jdbc連線oracle rac資料庫的寫法JDBCOracle資料庫
- Oracle 9i 11g歷史庫升級遷移資料至19c CDBOracle
- 【YashanDB資料庫】PHP無法透過ODBC連線到資料庫資料庫PHP
- GreatSQL資料庫DROP表後無法重建SQL資料庫
- MYSQL資料庫服務無法啟動MySql資料庫