Troubleshooting case study : Oracle RAC database is open but not accessible through listener due to some old unused diskgroup being removed   Leave a comment

The Issue:

I would like to use this blog to show how a recent database issue was resolved. The issue started when the application was not able to connect the database . Here is the part of log from the Oracle OBIEE application:

<Mar 29, 2016 11:48:58 AM CDT> <Warning> <JDBC> <BEA-001129> <Received exception while creating connection for pool “raframework_datasource”: Listener refused the connection with the following error:

ORA-12514, TNS:listener does not currently know of service requested in connect descriptor

The Troubleshooting steps

 1) Test the sqlnet and it didn’t show any error message:

[oracle@inmem2 admin]$ tnsping POCDB

TNS Ping Utility for Linux: Version 12.1.0.2.0 – Production on 26-MAR-2016 20:22 :18

Copyright (c) 1997, 2014, Oracle. All rights reserved.

Used parameter files:

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = inmem-sc an)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = pocdb.dbase.lab)))

OK (0 msec)

2) Test the database connectivity and showed the failed connection to the database:

[oracle@inmem2 admin]$ sqlplus biuser/<password>@pocdb

SQL*Plus: Release 12.1.0.2.0 Production on Sat Mar 26 20:22:23 2016

Copyright (c) 1982, 2014, Oracle. All rights reserved.

ERROR:

ORA-12514: TNS:listener does not currently know of service requested in connect

Descriptor.

3) check listener and it looks fine

[oracle@inmem1 ~]$ lsnrctl status

LSNRCTL for Linux: Version 12.1.0.2.0 – Production on 26-MAR-2016 20:39:13

Services Summary…

Service “+ASM” has 1 instance(s).

Instance “+ASM1”, status READY, has 1 handler(s) for this service…

Service “pocdb.dbase.lab” has 1 instance(s).

Instance “pocdb1”, status READY, has 1 handler(s) for this service…

Service “pocdbXDB.dbase.lab” has 1 instance(s).

Instance “pocdb1”, status READY, has 1 handler(s) for this service…

The command completed successful

4) Check the database sysdba login:

Database is up:

[oracle@inmem1 ~]$ sqlplus

SQL*Plus: Release 12.1.0.2.0 Production on Tue Mar 29 09:41:28 2016

Copyright (c) 1982, 2014, Oracle. All rights reserved.

Enter user-name: / as sysdba

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production

With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,

Advanced Analytics and Real Application Testing option

5) However check the database status from srvctl and found that the database was marked down on both nodes:

[oracle@inmem2 admin]$ srvctl status database -d pocdb

Instance pocdb1 is not running on node inmem1

Instance pocdb2 is not running on node inmem2

Tried to bring it up and failed as it failed to bring up resource ‘ora.DATA.dg’

[oracle@inmem1 ~]$ srvctl start database -d pocdb

PRCR-1079 : Failed to start resource ora.pocdb.db

CRS-2674: Start of ‘ora.DATA.dg’ on ‘inmem2’ failed

CRS-2632: There are no more servers to try to place resource ‘ora.pocdb.db’ on that would satisfy its placement policy

CRS-2674: Start of ‘ora.DATA.dg’ on ‘inmem1’ failed

6)  Check the CRS resource status and found that ora.DATA.dg and ora.pocdb.db were offline:

[grid@inmem1 ~]$ crs_stat

NAME=ora.DATA.dg

TYPE=ora.diskgroup.type

TARGET=ONLINE

STATE=OFFLINE

….

 

NAME=ora.pocdb.db

TYPE=ora.database.type

TARGET=ONLINE

STATE=OFFLINE

Check the dependencies of ora.pocdb.db

7) Check the resource dependency. It shows the database pocdb has dependency on ora.DATa.dg for this startup process.

[grid@inmem1 ~]$ crsctl status resource ora.pocdb.db -f

NAME=ora.pocdb.db

TYPE=ora.database.type

….

START_DEPENDENCIES=hard(ora.DATA.dg, ora.SAN_DATA2.dg) pullup(ora.DATA.dg, ora.SAN_DATA2.dg) weak(type:ora.listener.type,global:type:ora.scan_listener.type,uniform:ora.ons,global:ora.gns)

It shows the POCDB has dependency on DATA diskgroup, but DATA diskgroup is no longer available in ASM instance:

SQL> select NAME, STATE  from v$asm_diskgroup;

NAME STATE

——————————————–

SAN_DATA2   MOUNTED

SAN_OCR   MOUNTED

OCR   MOUNTED

SSD_ACFS   MOUNTED

SAN_DATA   MOUNTED

SAN_ACFS   MOUNTED

6 rows selected.

So we need to remove the this dependency

Solution:

The key is to remove this dependency so that we can restart the database through srvctl and make sure that the status of the database resource ora.pocdb.db is shown as ONLINE

1) Reregister pocdb in as a CRS resource

Remove the database first:

[oracle@inmem1 ~]$  srvctl remove database -d pocdb

Remove the database pocdb? (y/[n]) y

Then add the database back:

[oracle@inmem1 ~]$ srvctl add database -d pocdb -o /u01/app/oracle/product/12.1.0/dbhome_1

[oracle@inmem1 ~]$  srvctl add  instance -d pocdb -i pocdb1 -n inmem1

[oracle@inmem1 ~]$ srvctl add  instance -d pocdb -i pocdb2 -n inmem

2) Check the dependency status:

[grid@inmem1 ~]$ crsctl status resource ora.pocdb.db -f

NAME=ora.pocdb.db

TYPE=ora.database.type

….

START_DEPENDENCIES=weak(type:ora.listener.type,global:type:ora.scan_listener.type,uniform:ora.ons,global:ora.gns)

3) Startup the database :

[oracle@inmem1 ~]$ srvctl start database -d pocdb

Check the database status

[oracle@inmem1 ~]$ srvctl status database -d pocdb

Instance pocdb1 is running on node inmem1

Instance pocdb2 is running on node inmem2

Test the database connectivity

[oracle@inmem1 ~]$ sqlplus biuser/password@pocdb

SQL*Plus: Release 12.1.0.2.0 Production on Tue Mar 29 11:51:39 2016

Copyright (c) 1982, 2014, Oracle. All rights reserved.

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production

With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,

Advanced Analytics and Real Application Testing options

The Oracle BI application is no longer having connectivity issue.

Advertisements

Posted March 29, 2016 by kyuoracleblog in Uncategorized

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: