数据库ORA-03113排查
提示ORA-03113:通信通道的文件結尾解決?
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@CP07_NV1_DB ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Mon Dec 12 13:48:35 2016
Copyright (c) 1982, 2011, Oracle. ?All rights reserved.
Connected to an idle instance.
SQL> startup mount
ORACLE instance started.
Total System Global Area 4275781632 bytes
Fixed Size ? ? ? ? ? ? ? ? ?2235208 bytes
Variable Size ? ? ? ? ? ? 822084792 bytes
Database Buffers ? ? ? ? 3439329280 bytes
Redo Buffers ? ? ? ? ? ? ? 12132352 bytes
Database mounted.
SQL> select * from v$log;
? ? GROUP# ? ?THREAD# ?SEQUENCE# ? ? ?BYTES ?BLOCKSIZE ? ?MEMBERS ARC
---------- ---------- ---------- ---------- ---------- ---------- ---
STATUS ? ? ? ? ? FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------------- ------------- --------- ------------ ---------
? ? ? ? ?1 ? ? ? ? ?1 ? ? ?88281 ? 52428800 ? ? ? ?512 ? ? ? ? ?1 NO
INACTIVE ? ? ? ? ? ?1179936249 12-DEC-16 ? 1179945253 12-DEC-16
? ? ? ? ?4 ? ? ? ? ?1 ? ? ?88283 ? 57671680 ? ? ? ?512 ? ? ? ? ?1 NO
CURRENT ? ? ? ? ? ? 1179952814 12-DEC-16 ? 2.8147E+14
? ? ? ? ?3 ? ? ? ? ?1 ? ? ?88280 ? 52428800 ? ? ? ?512 ? ? ? ? ?1 NO
INACTIVE ? ? ? ? ? ?1179929281 12-DEC-16 ? 1179936249 12-DEC-16
? ? GROUP# ? ?THREAD# ?SEQUENCE# ? ? ?BYTES ?BLOCKSIZE ? ?MEMBERS ARC
---------- ---------- ---------- ---------- ---------- ---------- ---
STATUS ? ? ? ? ? FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------------- ------------- --------- ------------ ---------
? ? ? ? ?2 ? ? ? ? ?1 ? ? ?88282 ? 52428800 ? ? ? ?512 ? ? ? ? ?1 NO
INACTIVE ? ? ? ? ? ?1179945253 12-DEC-16 ? 1179952814 12-DEC-16
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01139: RESETLOGS option only valid after an incomplete database recovery
SQL> recover database until time ?'2016-12-11';?
Media recovery complete.
SQL> alter database open resetlogs;
Database altered.
SQL> select open_mode from $database;
select open_mode from $database
? ? ? ? ? ? ? ? ? ? ? *
ERROR at line 1:
ORA-00911: invalid character
SQL> select * from v$log;
? ? GROUP# ? ?THREAD# ?SEQUENCE# ? ? ?BYTES ?BLOCKSIZE ? ?MEMBERS ARC
---------- ---------- ---------- ---------- ---------- ---------- ---
STATUS ? ? ? ? ? FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------------- ------------- --------- ------------ ---------
? ? ? ? ?1 ? ? ? ? ?1 ? ? ? ? ?1 ? 52428800 ? ? ? ?512 ? ? ? ? ?1 NO
CURRENT ? ? ? ? ? ? 1179956666 12-DEC-16 ? 2.8147E+14
? ? ? ? ?2 ? ? ? ? ?1 ? ? ? ? ?0 ? 52428800 ? ? ? ?512 ? ? ? ? ?1 YES
UNUSED ? ? ? ? ? ? ? ? ? ? ? 0 ? ? ? ? ? ? ? ? ? ? ?0
? ? ? ? ?3 ? ? ? ? ?1 ? ? ? ? ?0 ? 52428800 ? ? ? ?512 ? ? ? ? ?1 YES
UNUSED ? ? ? ? ? ? ? ? ? ? ? 0 ? ? ? ? ? ? ? ? ? ? ?0
? ? GROUP# ? ?THREAD# ?SEQUENCE# ? ? ?BYTES ?BLOCKSIZE ? ?MEMBERS ARC
---------- ---------- ---------- ---------- ---------- ---------- ---
STATUS ? ? ? ? ? FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------------- ------------- --------- ------------ ---------
? ? ? ? ?4 ? ? ? ? ?1 ? ? ? ? ?0 ? 57671680 ? ? ? ?512 ? ? ? ? ?1 YES
UNUSED ? ? ? ? ? ? ? ? ? ? ? 0 ? ? ? ? ? ? ? ? ? ? ?0
重置日志的序列號
SQL> select member from v$logfile;
MEMBER
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/CP07NV1D/redo03.log
/u01/app/oracle/oradata/CP07NV1D/redo02.log
/u01/app/oracle/oradata/CP07NV1D/redo01.log
/u01/app/oracle/oradata/CP07NV1D/redo04.log
[oracle@CP07_NV1_DB ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Mon Dec 12 14:07:10 2016
Copyright (c) 1982, 2011, Oracle. ?All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select status from v$instance;
STATUS
------------
OPEN
SQL> select ?* from scott.emp;
? ? ?EMPNO ENAME ? ? ?JOB ? ? ? ? ? ? ?MGR HIREDATE ? ? ? ? SAL ? ? ? COMM
---------- ---------- --------- ---------- --------- ---------- ----------
? ? DEPTNO
----------
? ? ? 7369 SMITH ? ? ?CLERK ? ? ? ? ? 7902 17-DEC-80 ? ? ? ?800
? ? ? ? 20
? ? ? 7499 ALLEN ? ? ?SALESMAN ? ? ? ?7698 20-FEB-81 ? ? ? 1600 ? ? ? ?300
? ? ? ? 30
? ? ? 7521 WARD ? ? ? SALESMAN ? ? ? ?7698 22-FEB-81 ? ? ? 1250 ? ? ? ?500
SQL> select group#,sequence#,bytes,members,status from v$log;
? ? GROUP# ?SEQUENCE# ? ? ?BYTES ? ?MEMBERS STATUS
---------- ---------- ---------- ---------- ----------------
? ? ? ? ?1 ? ? ? ? ?1 ? 52428800 ? ? ? ? ?1 INACTIVE
? ? ? ? ?2 ? ? ? ? ?2 ? 52428800 ? ? ? ? ?1 CURRENT
? ? ? ? ?3 ? ? ? ? ?0 ? 52428800 ? ? ? ? ?1 UNUSED
? ? ? ? ?4 ? ? ? ? ?0 ? 57671680 ? ? ? ? ?1 UNUSED
SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.
SQL> SQL> select open_mode from v$database;
OPEN_MODE
--------------------
READ WRITE
SQL> select open_mode from v$database;
OPEN_MODE
--------------------
READ WRITE
SQL> select group#,sequence#,bytes,members,status from v$log;
? ? GROUP# ?SEQUENCE# ? ? ?BYTES ? ?MEMBERS STATUS
---------- ---------- ---------- ---------- ----------------
? ? ? ? ?1 ? ? ? ? ?5 ? 52428800 ? ? ? ? ?1 INACTIVE
? ? ? ? ?2 ? ? ? ? ?6 ? 52428800 ? ? ? ? ?1 INACTIVE
? ? ? ? ?3 ? ? ? ? ?7 ? 52428800 ? ? ? ? ?1 CURRENT
? ? ? ? ?4 ? ? ? ? ?4 ? 57671680 ? ? ? ? ?1 INACTIVE
SQL> select member from v$logfile;
MEMBER
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/CP07NV1D/redo03.log
/u01/app/oracle/oradata/CP07NV1D/redo02.log
/u01/app/oracle/oradata/CP07NV1D/redo01.log
/u01/app/oracle/oradata/CP07NV1D/redo04.log
SQL> select file#,checkpoint_change# from v$datafile;
? ? ?FILE# CHECKPOINT_CHANGE#
---------- ------------------
? ? ? ? ?1 ? ? ? ? 1180436432
? ? ? ? ?2 ? ? ? ? 1180436432
? ? ? ? ?3 ? ? ? ? 1180436432
? ? ? ? ?4 ? ? ? ? 1180436432
? ? ? ? ?5 ? ? ? ? 1180436432
? ? ? ? ?6 ? ? ? ? 1180436432
6 rows selected.
SQL> select file#,checkpoint_change# from v$datafile_header;
? ? ?FILE# CHECKPOINT_CHANGE#
---------- ------------------
? ? ? ? ?1 ? ? ? ? 1180452436
? ? ? ? ?2 ? ? ? ? 1180452436
? ? ? ? ?3 ? ? ? ? 1180452436
? ? ? ? ?4 ? ? ? ? 1180452436
? ? ? ? ?5 ? ? ? ? 1180452436
? ? ? ? ?6 ? ? ? ? 1180452436
6 rows selected.
SQL>?
第二步：select * from v$recovery_file_dest;alter system set db_recovery_file_dest_size=10737418240 ---這里是改為10G。alter database openexit第三步：rman target /進入rman工具窗口rman target /RMAN>crosscheck archivelog all;? -- 運行這個命令可以把無效的expired的archivelog標出來。RMAN>delete expired archivelog all; -- 直接全部刪除過期的歸檔日志。RMAN>delete noprompt archivelog until time "sysdate -3";? -- 也可以直接用一個指定的日期來刪除。 ?
重新打開數據庫就可以正常操作了。?
總結
以上是生活随笔為你收集整理的数据库ORA-03113排查的全部內容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： openlayer 3 在layer上添
- 下一篇： Jenkisn之JDK-MVN-ANT-