Tales From A Lazy Fat DBA

Faced one more end-of-file communication error with one of my Test ASM Environment. Below are the steps i’ve tried to fix the problem.
Error:
[oracle@localhost bin]$ sqlplus / as sysasm

SQL*Plus: Release 11.2.0.1.0 Production on Tue Jan 8 20:00:25 2013
Copyright (c) 1982, 2009, Oracle.  All rights reserved.
Connected to an idle instance.

SQL> ORA-03113: end-of-file on communication channel
1st Step:

I’ve tried to check the CRS (Cluster Restart Service or Oracle Restart Service) of instance. Using crs_stats you can get a list of all services which are linked with CRS with their respective state and target details.
(Always look for STATE & TARGET out of other stats provided and try to fix ‘Types’ with ‘OFFLINE’ state).

Example:
[oracle@localhost bin]$  crs_stat -t

Name           Type           Target    State     Host
————————————————————
ora.cssd       ora.cssd.type  OFFLINE   OFFLINE
ora.diskmon    ora….on.type OFFLINE   OFFLINE
ora.asm        ora….on      ONLINE    OFFLINE

* Clearly reflects broken ora.asm service is broken and is OFFLINE right now.

2nd Step:
Forcibly de-configure CRS Service via Root user.
(You’ll find the Perl script in GRID Home/crs/install directory)

Example:
[root@localhost install]# ./rootcrs.pl -deconfig -force
2013-01-08 19:53:59: Parsing the host name
2013-01-08 19:53:59: Checking for super user privileges
2013-01-08 19:53:59: User has super user privileges
Using configuration parameter file: ./crsconfig_params
Usage: srvctl <command> <object> [<options>]
commands: enable|disable|start|stop|status|add|remove|modify|getenv|setenv|unsetenv|config
objects: database|service|asm|diskgroup|listener|home|ons|eons
For detailed help on each command and object and its options use:
srvctl <command> -h or
srvctl <command> <object> -h
PRKO-2012 : nodeapps object is not supported in Oracle Restart
ACFS-9200: Supported
CRS-2613: Could not find resource ‘ora.registry.acfs’.
CRS-4000: Command Stop failed, or completed with errors.
CRS-4013: This command is not supported in a single-node configuration.
CRS-4000: Command Stop failed, or completed with errors.
You must kill crs processes or reboot the system to properly
cleanup the processes started by Oracle clusterware
error: package cvuqdisk is not installed
Successfully deconfigured Oracle clusterware stack on this node

Third Step:
Re-run root.sh script using Root account. This will configure OCR (Oracle Cluster Registry) keys for oracle user and restart ohasd (Oracle High Availability Service Deamon) again.

* Oracle Clusterware is run by Cluster Ready Services (CRS) which consisting of  Oracle Cluster Registry (OCR), which records and maintains the cluster and node membership information. Records Disk membership information in case of ASM configured on Non-RAC systems.

Example:
[root@localhost dbhome_1]# ./root.sh
Running Oracle 11g root.sh script…

The following environment variables are set as:
ORACLE_OWNER= oracle
ORACLE_HOME=  /u01/app/oracle/product/11.2.0/dbhome_1

Enter the full pathname of the local bin directory: [/usr/local/bin]:
The file “dbhome” already exists in /usr/local/bin.  Overwrite it? (y/n)
[n]:
The file “oraenv” already exists in /usr/local/bin.  Overwrite it? (y/n)
[n]:
The file “coraenv” already exists in /usr/local/bin.  Overwrite it? (y/n)
[n]:

Entries will be added to the /etc/oratab file as needed by
Database Configuration Assistant when a database is created
Finished running generic part of root.sh script.
Now product-specific root actions will be performed.
2013-01-08 19:55:58: Checking for super user privileges
2013-01-08 19:55:58: User has super user privileges
2013-01-08 19:55:58: Parsing the host name
Using configuration parameter file: /u01/app/oracle/product/11.2.0/dbhome_1/crs/install/crsconfig_params
LOCAL ADD MODE
Creating OCR keys for user ‘oracle’, privgrp ‘oinstall’..
Operation successful.
CRS-4664: Node localhost successfully pinned.
Adding daemon to inittab
CRS-4123: Oracle High Availability Services has been started.
ohasd is starting
acfsroot: ACFS-9301: ADVM/ACFS installation can not proceed:

acfsroot: ACFS-9302: No installation files found at /u01/app/oracle/product/11.2.0/dbhome_1/install/usm/EL5/i386/2.6.18-8/2.6.18-8.el5uek-i686/bin.

localhost     2013/01/08 19:56:20     /u01/app/oracle/product/11.2.0/dbhome_1/cdata/localhost/backup_20130108_195620.olr
Successfully configured Oracle Grid Infrastructure for a Standalone Server
Updating inventory properties for clusterware
Starting Oracle Universal Installer…

Checking swap space: must be greater than 500 MB.   Actual 2047 MB    Passed
The inventory pointer is located at /etc/oraInst.loc
The inventory is located at /u01/app/oraInventory
‘UpdateNodeList’ was successful.
You have new mail in /var/spool/mail/root

Step 4th:
Start cssd and diskmon services.

* Oracle Cluster Synchronization Services daemon (OCSSd) provides basic ‘group services’ support. This is also required in a single instance configuration if Automatic Storage Management (ASM) is use.
* diskmon (Disk Monitor daemon): Responsibel for performing Monitoring activities. The diskmon daemon is always started when ocssd starts.

[oracle@localhost ~]$ cd /u01/app/oracle/product/11.2.0/dbhome_1/bin
[oracle@localhost bin]$ ./crsctl start resource ora.cssd
CRS-2672: Attempting to start ‘ora.cssd’ on ‘localhost’
CRS-2672: Attempting to start ‘ora.diskmon’ on ‘localhost’
CRS-2676: Start of ‘ora.diskmon’ on ‘localhost’ succeeded
CRS-2676: Start of ‘ora.cssd’ on ‘localhost’ succeeded

Step 5th:
Let’s try to Mount the ASM Diskgroup.
ASMCMD> mount DATA

(If not mounted, you’ll probably receive error codes: ORA-15110: ‘no diskgroups mounted’ and will be fixed once you mount Diskgroup).

Step 6th:
Finally start your ASM instance.

[oracle@localhost bin]$ sqlplus / as sysasm

SQL*Plus: Release 11.2.0.1.0 Production on Tue Jan 8 20:00:25 2013
Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – Production
With the Automatic Storage Management option

SQL>

Done…