Archive for March 2016

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

IOUG Spotlight for March 2016   Leave a comment

Each month, the Independent Oracle Users Group ( IOUG)  spotlights  and give an inside look at their life.  It is  a great honor that  IOUG picked me for the March 2016 IOUG Member Spotlight as this link: http://www.ioug.org/page/ioug-member-spotlight. Here are some of contents from the link:

IOUG Member Spotlight

kai_yu.jpg

Join IOUG each month as we shine the spotlight on one of our members. Learn fun facts about what inspires our members, what they do for fun and how they got started in their careers.

March 2016 – Kai Yu

What’s your current job and title?

I am a Senior Principal Engineer at Dell Enterprise Solutions Engineering. My responsibilities include architecting, implementing enterprise database solutions and cloud based solutions.

What is your favorite moment of your career so far?

Over two decades, I have worked on various technology and enjoyed every moment when my work was put into products or engineering solutions. One particular example was that in 2008, I put together a database consolidation model and performed the performance studies that helped a customer consolidate a few dozens of databases from multiple small IT sites into a centralized Oracle Grid architecture in the data center. For this I received the “Customer Experience Legend Award,” the most prestigious award from the Dell Senior Executives.

What was your childhood dream job?

Mathematician or aerospace engineer, but ended up computer science career which I found more interesting.

How long have you been a member of IOUG? What’s your best experience thus far?

Since 2008, when I started to get involved with the IOUG Oracle RAC SIG. Since then I have really enjoyed engaging with IOUG activities such as running a few IOUG technology boot camps at COLLABORATE conferences. One of my best experience was to co-found the IOUG Cloud Computing SIG. I got a chance to work with many great talents and industry experts to establish this important platform that is dedicated to sharing expertise and experiences on Cloud Computing.

What advice do you wish someone had given you when you first started your career as a technologist?

I wish someone had given me some advice about the direction which technology was moving to and how to better pursue the technical career.

What features or products are on your wish list and why?

As SSD (Solid State Drive) is getting more and more adapted as the database storage, I would like to see that database systems introduce some new architecture and features that are specially designed to better leverage SSDs great performance.

What is the biggest technical and/or business challenge you have faced, and how did you overcome it?

It can be challenging to implement some of the latest technology for business. One particular case was implementing a cloud service POC system using a beta release of Oracle Enterprise Manager 12c Cloud Management.  Luckily, I worked with the Oracle EM12c engineering team very closely and we were able to overcome many technical challenges and successfully complete the POC. As a result, we presented the POC at Oracle OpenWorld 2012 when Oracle released Oracle EM12c Cloud Control.

How do you stay active? How did you discover that activity?

I enjoy travelling to different places around the world to learn different culture and making friends in the global technology community. Besides that, my favorite activity is swimming. Although I’ve liked swimming since my childhood, I really found myself passionate about it when I learned how to build the endurance for a distance swimming.

What’s your favorite film? Any memorable moments? Any favorite quotes?

The 2014 film: “The Theory of Everything”

A favorite quote: “There should be no boundaries to human endeavor. However bad life may seem, where there is life, there is hope.”

What piques your passion in technology?

When I was in a software developer role for more than 20 some years, I was fascinated by Oracle Database technology. Ten years ago, after moving to the enterprise engineering role, I found myself a new interest on infrastructure that Oracle database is based on such as Linux OS, servers, storage and networking. These experiences allow me to see the big picture of computing technology and how these technologies are moving forward—that is truly fascinating.

LinkedIn: Kai Yu

Twitter: ky_austin1

Blog: https://kyuoracleblog.wordpress.com/

Posted March 3, 2016 by kyuoracleblog in Uncategorized