Oracle startup error ORA-03113 resolution

Oracle startup error ORA-03113 resolution

Environment: RHEL6.4 + Oracle 11.2.0.4

Step summary:
1. Start error ORA-03113
2. view the alert Log to find the cause
3. Take reasonable measures according to the actual situation. Here we first increase the size of the flash back area and start the database.
4. Create an archive log deletion policy

1. Start error ORA-03113

$ Sqlplus/as sysdba

SQL * Plus: Release 11.2.0.4.0 Production on Saturday February 28 13:56:44 2015

Copyright (c) 1982,201 3, Oracle. All rights reserved.

Connected to an idle instance.

SQL> startup mount
ORACLE instance started.

Total System Global Area 3206836224 bytes
Fixed Size 2257520 bytes
Variable Size 2365590928 bytes
Database Buffers 822083584 bytes
Redo Buffers 16904192 bytes
Database mounted.
SQL> alter database open;
Alter database open
*
ERROR at line 1:
ORA-03113: End of the file for the communication channel
Process ID 13501
Session ID: 853 serial number: 5

SQL>

2. view the alert Log to find the cause

Sat Feb 28 13:56:52 2015
Starting ORACLE instance (normal)
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
Initial number of CPU is 8
CELL communication is configured to use 0 interface (s ):
Cell ip affinity details:
NUMA status: non-NUMA system
Cellaffinity. ora status: N/
CELL communication will use 1 IP group (s ):
Grp 0:
Picked latch-free SCN scheme 3
Using LOG_ARCHIVE_DEST_1 parameter default value as USE_DB_RECOVERY_FILE_DEST
Autotune of undo retention is turned on.
IMODE = BR
ILAT = 1, 249
LICENSE_MAX_USERS = 0
SYS auditing is disabled
Starting up:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0-64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options.
ORACLE_HOME =/opt/app/oracle/product/11.2.0.4/dbhome_1
System name: Linux
Node name: JJFAB01
Release: 2.6.32-358. el6.x86 _ 64
Version: #1 SMP Tue Jan 29 11:47:41 EST 2013
Machine: x86_64
VM name: Xen Version: 4.1 (PVM)
Using parameter settings in server-side spfile/opt/app/oracle/product/11.2.0.4/dbhome_1/dbs/spfileJJFAB. ora
System parameters with non-default values:
Processes = 1500
Sessions = 2272
Memory_target = 3G
Control_files = "/opt/oradata/gxwj/control01.ctl"
Control_files = "/opt/app/oracle/fast_recovery_area/gxwj/control02.ctl"
DB _ block_size = 8192
Compatible = "11.2.0.0.0"
Db_recovery_file_dest = "/opt/app/oracle/fast_recovery_area"
Db_recovery_file_dest_size = 4122 M
Undo_tablespace = "UNDOTBS1"
Remote_login_passwordfile = "EXCLUSIVE"
Db_domain = ""
Dispatchers = "(PROTOCOL = TCP) (SERVICE = gxwjXDB )"
Audit_file_dest = "/opt/app/oracle/admin/gxwj/adump"
Audit_trail = "DB"
Db_name = "JJFAB"
Open_cursors = 300
Diagnostic_dest = "/opt/app/oracle"
Sat Feb 28 13:56:54 2015
PMON started with pid = 2, OS id = 13461
Sat Feb 28 13:56:54 2015
PSP0 started with pid = 3, OS id = 13463
Sat Feb 28 13:56:55 2015
VKTM started with pid = 4, OS id = 13465 at elevated priority
VKTM running at (1) millisec precision with DBRM quantum (100) MS
Sat Feb 28 13:56:55 2015
GEN0 started with pid = 5, OS id = 13469
Sat Feb 28 13:56:55 2015
DIAG started with pid = 6, OS id = 13471
Sat Feb 28 13:56:55 2015
DBRM started with pid = 7, OS id = 13473
Sat Feb 28 13:56:55 2015
DIA0 started with pid = 8, OS id = 13475
Sat Feb 28 13:56:55 2015
MMAN started with pid = 9, OS id = 13477
Sat Feb 28 13:56:55 2015
DBW0 started with pid = 10, OS id = 13479
Sat Feb 28 13:56:55 2015
LGWR started with pid = 11, OS id = 13481
Sat Feb 28 13:56:55 2015
CKPT started with pid = 12, OS id = 13483
Sat Feb 28 13:56:55 2015
SMON started with pid = 13, OS id = 13485
Sat Feb 28 13:56:55 2015
RECO started with pid = 14, OS id = 13487
Sat Feb 28 13:56:55 2015
MMON started with pid = 15, OS id = 13489
Starting up 1 dispatcher (s) for network address '(ADDRESS = (PARTIAL = YES) (PROTOCOL = TCP ))'...
Sat Feb 28 13:56:55 2015
MMNL started with pid = 16, OS id = 13491
Starting up 1 shared server (s )...
ORACLE_BASE from environment =/opt/app/oracle
Sat Feb 28 13:56:56 2015
ALTER DATABASE MOUNT
Successful mount of redo thread 1, with mount id 3498004520
Database mounted in Exclusive Mode
Lost write protection disabled
Completed: ALTER DATABASE MOUNT
Sat Feb 28 13:57:48 2015
Alter database open
Sat Feb 28 13:57:48 2015
LGWR: STARTING ARCH PROCESSES
Sat Feb 28 13:57:48 2015
ARC0 started with pid = 20, OS id = 13553
ARC0: Archival started
LGWR: STARTING ARCH PROCESSES COMPLETE
ARC0: STARTING ARCH PROCESSES
Sat Feb 28 13:57:49 2015
ARC1 started with pid = 21, OS id = 13555
Sat Feb 28 13:57:49 2015
ARC2 started with pid = 22, OS id = 13557
Errors in file/opt/app/oracle/diag/rdbms/jjfab/JJFAB/trace/JJFAB_ora_13501.trc:
ORA-19815: Warning: db_recovery_file_dest_size bytes (4322230272 bytes in total) are 100.00% in use and 0 bytes are available.
**************************************** ********************************
You have following choices to free up space from recovery area:
1. Consider changing rman retention policy. If you are using Data Guard,
Then consider changing rman archivelog deletion policy.
2. Back up files to tertiary device such as tape using RMAN
Backup recovery area command.
3. Add disk space and increase db_recovery_file_dest_size parameter
Reflect the new space.
4. Delete unnecessary files using rman delete command. If an operating
System command was used to delete files, then use rman crosscheck and
Delete expired commands.
**************************************** ********************************
ARCH: Error 19809 Creating archive log file to '/opt/app/oracle/fast_recovery_area/JJFAB/archivelog/2015_02_28/o1_mf_0000130 _ % u _. arc'
Errors in file/opt/app/oracle/diag/rdbms/jjfab/JJFAB/trace/JJFAB_ora_13501.trc:
ORA-16038: Log 1 sequence #130 cannot be archived
ORA-19809: exceeds the limit on the number of recovery files
ORA-00312: Online log 1 thread 1: '/opt/oradata/gxwj/redo01.log'
Sat Feb 28 13:57:49 2015
ARC3 started with pid = 23, OS id = 13559
USER (ospid: 13501): terminating the instance due to error 16038
Sat Feb 28 13:57:50 2015
System state dump requested by (instance = 1, osid = 13501), summary = [abnormal instance termination].
System State dumped to trace file/opt/app/oracle/diag/rdbms/jjfab/JJFAB/trace/JJFAB_diag_13471_20150228135750.trc
Dumping diagnostic data in directory = [cdmp_20150228135750], requested by (instance = 1, osid = 13501), summary = [abnormal instance termination].
Instance terminated by USER, pid = 13501

The Cause and suggestion are clearly provided in the alarm log.

3. Take reasonable measures according to the actual situation. Here we first increase the size of the flash back area and start the database.

SQL> startup mount
ORA-24324: Service handle not initialized
ORA-01041: Internal error, hostdef extension does not exist
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0-64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
$ Sqlplus/as sysdba

SQL * Plus: Release 11.2.0.4.0 Production on Saturday February 28 14:05:59 2015

Copyright (c) 1982,201 3, Oracle. All rights reserved.

Connected to an idle instance.

SQL> startup mount
ORACLE instance started.

Total System Global Area 3206836224 bytes
Fixed Size 2257520 bytes
Variable Size 2365590928 bytes
Database Buffers 822083584 bytes
Redo Buffers 16904192 bytes
Database mounted.
SQL> show parameter db_recover

NAME TYPE VALUE
-----------------------------------------------------------------------------
Db_recovery_file_dest string/opt/app/oracle/fast_recovery _
Area
Db_recovery_file_dest_size large integer 4122 M
SQL>!
$ Df-h/opt/
File System capacity in use available % mount point
/Dev/mapper/vg_extend-lv_opt
493G 423G 45G 91%/opt
$ Exit
Exit

SQL> alter system set db_recovery_file_dest_size = 10G;

System altered.

SQL> alter database open;

Database altered.

SQL> select status from v $ instance;

STATUS
------------
OPEN

SQL>

4. Create an archive log deletion policy

SQL> set linesize 120
SQL> select * from v $ flash_recovery_area_usage;

FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
------------------------------------------------------------------------------
Control file 0 0 0
Redo log 0 0 0
Archived log 41.54 0 133
Backup piece 0 0 0
Image copy 0 0 0
Flashback log 0 0 0
Foreign archived log 0 0 0
Rows selected.
You can use rman to directly Delete unnecessary archives, such as the archive deleted one day ago.
RMAN> delete noprompt archivelog until time 'sysdate-1 ';
The output of deleting the archive is omitted here ..
RMAN> exit

Recovery Manager complete.

SQL> select * from v $ flash_recovery_area_usage;

FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
------------------------------------------------------------------------------
Control file 0 0 0
Redo log 0 0 0
Archived log. 35 0 1
Backup piece 0 0 0
Image copy 0 0 0
Flashback log 0 0 0
Foreign archived log 0 0 0
Rows selected.

You can also set crontab to delete the archive one day before each day:

$ Crontab-l
04 ***/opt/shell/del_arch.sh
$ More/opt/shell/del_arch.sh
#! /Bin/bash
Export ORACLE_BASE =/opt/app/oracle
Export ORACLE_HOME =/opt/app/oracle/product/11.2.0.4/dbhome_1
Export ORACLE_SID = JJFAB
Export NLS_LANG = "simplified chinese_china.ZHS16GBK"
Export NLS_DATE_FORMAT = "YYYY-MM-DD HH24: Mi: SS"
Export PATH =.:/opt/app/oracle/product/11.2.0.4/dbhome_1:/usr/sbin: $ ORACLE_HOME/bin: $ PATH
Export LD_LIBRARY_PATH = $ ORACLE_HOME/lib

Rman target/log =/opt/backup/del_arch.log <EOF
List archivelog all;
Delete noprompt archivelog until time 'sysdate-1 ';
Show all;
List archivelog all;
EOF

Oracle entry-Oracle startup error ORA-03113"