数据库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*fromv$recovery_file_dest;altersystemsetdb_recovery_file_dest_size=10737418240---这里是改为10G。alterdatabaseopenexit第三步：rman target /进入rman工具窗口rman target /RMAN>crosscheck archivelogall;-- 运行这个命令可以把无效的expired的archivelog标出来。RMAN>deleteexpired archivelogall;-- 直接全部删除过期的归档日志。RMAN>deletenoprompt archivelog untiltime"sysdate -3";-- 也可以直接用一个指定的日期来删除。重新打开数据库就可以正常操作了。