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.

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

My Speaking at this year’s Oracle OpenWorld   Leave a comment

Iamspeaking

At this year Oracle OpenWorld 2015, I will be presenting a technical session at 12pm of Sunday Oct 25. This session is about how to design and implement a Self-Service Enabled Private Cloud with Oracle EM12c,. The session information are in follow:

Session ID: UGF9927

Session Title: Design and Implement a Self-Service–Enabled Private Cloud: Oracle Enterprise Manager 12c

Date and Time Sunday, Oct 25, 12:00 p.m.

Venue/Room: Moscone South—254

Since the  first time in 2006 when Oracle OpenWorld accepted my paper “Implementing Oracle Applications 11i on 10g RAC and ASM“,  I have presented  or co-presented  total 28 technical sessions/ or panels in 11 Oracle OpenWorld conferences (in 2010, I presented at both Oracle OpenWorld Aisa and Oracle OpenWorld SFO). The presentations cover several  several Oracle technology areas such as Oracle Cloud, Oracle VM, Oracle RAC/Oracle database, Oracle Business Intelligence, Oracle Enterprise Manager, etc. The following are the list of these Oracle OpenWorld presentations.

OOW2015:

28: Design & Implement Your Own Self-Service Enabled Private Cloud with Oracle EM12c, Session ID: UGF9927, Oct 25, 12:00 p.m

OOW 2014:

27 :Architect Your Data Center Infrastructure from Scratch, Session #ID: UGF9244, Sunday, Sept 28, 10:00am-10:45am

26: Implement and Manage IaaS and a Self-Service Portal with Oracle Enterprise Manager 12c Session ID: UGF3431: Sunday, Sept 28, 12:00am-12:45am

25: Leveraging New Oracle Database 12c Features to Reduce Storage Costs Session ID: CON5433: Thursday, OCT 9, 9:30am-10:15am, Moscone South – 310

 OOW2013:

24. Clustering/High Availability with Oracle VM and Oracle Enterprise Manager 12c: 360 Degrees [UGF9803] 2:00 PM

23. Cloud Computing, Oracle VM, and Oracle Enterprise Manager 12c Best Practices: Expert Panel [UGF9756] , Sunday, Sep 22, 2:15 PM – 3:15 PM

22. Oracle VM and Virtualization Best Practices: Expert Panel Discussion [UGF9809]  4:30 PM

21. Best Practices for Deploying Highly Scalable Virtualized Oracle Applications [CON11257] , Speakers: Monday, Sep 23, 4:45 PM – 5:45 PM

20. Configuring Database Cloud with Oracle Database 12c on Dell Blades: Methods/Tips session ID: [CON11220] Monday, Sep 23, 3:15 PM – 4:15 PM

19. Accelerating OLTP Databases with Flash-Based Storage: Case Studies Session ID: [CON11235]  Tuesday, Sep 24, 10:30 AM – 11:30 AM

OOW2012:

18. Oracle on Oracle VM: Expert Panel,  Session ID: UGF7700 –,  Sunday Sept 30, 12:30pm-2:00pm,

17:  High-Availability Infrastructure of the Database Cloud: Architecture and Best Practices, Monday Oct 1, 3:15pm-4:15pm,  session #CON3875

16:  Simplify App Deployment in the Cloud with Virtual Assemblies and Oracle Enterprise Manager 12c, Session #CON5206 –

OOW2011:

15 Configuring and Managing a Private Cloud with Oracle Enterprise Manager , session#06980, Tuesday, 05:30 PM 

14. Upgrading Oracle Enterprise Manager, Using Best Practices , Oracle OpenWorld 2011 session#0733, Thursday, 01:30 PM, Intercontinental – Intercontinental Ballroom A

13. Consolidate Oracle E-Business Suite Databases in Oracle Database 11g Release 2 Grid: Case Study, session#0845, Tuesday, 10:15 AM

12. Ensure the High Availability and Stability of Oracle RAC: Storage and Network Side Story ,session #09385, Sunday, 01:30 PM

 OOW2010 Beijing:

11.Oracle11g R2 Clusterware and RAC: Architecture , Configuration, Troubleshooting and Case study

10. Case study: Implementing the Oracle Grid Computing for Multiple ERP Applications

OOW2010:

  1. Oracle RAC on Oracle VM Automated Provisioning with Enterprise Manager 11g ,  Session ID: S316318.

8. Monitoring and Diagnosing Oracle RAC Performance with Oracle Enterprise Manager, Session ID:S316263

OOW2009:

7. Oracle E-Business Suite on Oracle RAC and Oracle VM: Architecture and Implementation

6. Building an Oracle Grid with Oracle VM on Dell Blade Servers and EqualLogic iSCSI storage

5. Provisioning Oracle RAC in a Virtualized Environment, Using Oracle Enterprise Manager

OOW2008:

  1. Case Study: Implementing the Oracle Grid Computing on Dell Hardware for Multiple  ERP Applications

OOW2007:

3. Implementing Oracle E-Business Suite R12 Through 11i to R12 Upgrade

2. Automatic Oracle RAC Deployment and Implementing MegaGrid using Oracle EM Provisioning Pack,

OOW2006:

  1. Implementing Oracle Applications 11i on 10g RAC and ASM

Posted October 21, 2015 by kyuoracleblog in Uncategorized

High Performance Oracle Database with the Dell Acceleration Appliances for Databases   Leave a comment

The performance of many business critical applications like Oracle database is often limited by bottlenecks in the infrastructure components—CPU, memory, network, and storage. Although CPU, memory, and network performance has improved dramatically over time, storage performance improvements have lagged due to the limitations of latency and throughput.
The Dell Acceleration Appliances for Databases (DAAD) combines Dell servers with Sandisk’s flash storage technology to significantly improve storage latency and throughput. The appliance provides an adaptable, flexible, highly available, and scalable storage solution to various IT organizations’ database implementation. The Oracle RAC database on the DAAD delivers the highly available and high-performance database solution to business critical applications

Dell Acceleration Appliances for Databases (DAAD) Overview:
The Dell Acceleration Appliances for Databases (DAAD) is a pre-built, pre-integrated appliance designed to accelerate leading database environments such as Oracle Database. This appliance combines Dell PowerEdge R720 Server, Dell networking, and ioMemory cards and ION Accelerator software from Fusion-io to improve database performance.
DAAD Architecture:
High-level appliance architecture:
• The appliance is built on the industry leading Dell PowerEdge R720 server.
• The appliance supports high-speed Fibre channel or iSCSI networking between database servers and the
appliance nodes.
• Each appliance node is equipped with four cards, each of which has 3.0TB of usable, persistent flash storage
in the PCIe slots of the R720 server. These cards are also known as ioMemory cards.
• The ION Accelerator software installed on each appliance node enables management and configuration including
HA.
• For High Availability, the ION HA Clustering configuration consists of a pair of two appliance nodes
connected with a private 40GbE point-to-point Interconnection network. The appliances provide
scalability across multiple appliance nodes or multiple pairs of HA Clustered appliances.

DAAD1

Oracle RAC Database Reference architecture based on Dell Acceleration Appliances for Databases

To ensure the high availability of the infrastructure, it is recommended that network and storage IO paths are redundant. The following diagram shows the architecture of Oracle RAC database implementation based on DAAD as the shared storage.
Oracle_RAC

The whitepaper that I co-authored with two other engineers in my team last July describes the reference architecture of an Oracle database and DAAD pairing, and showed the analysis of performance improvement of the Oracle database based on this architecture. The solution described in this whitepaper here is a high-availability-enabled, two-node appliance that uses the Fibre Channel storage protocol. Each appliance node is comprised of a Dell PowerEdge R720 Server, four 3.0TB ioMemory flash cards with the ION Accelerator software installed, and high availability ION clustering enabled. The highly available two-node appliance solution provides 12TB of redundant tier-1 storage capacity with a total of 24TB of raw storage capacity. The storage appliance delivers over 1 million random read IOPS with 0.5 millisecond latency for 4k random read which equates to 27 times the IOPS and 4% of the latency that 96x 15k conventional drives can provide. On HammerDB’ s TPC-C-like performance studies, the four-node Oracle RAC database on the two-node appliance can deliver 960,000 peak New Orders per Minute (NOPM) and over 2.5 million Transaction Per Minute (TPM).

To see my other technical white papers, please refer to the publication table of this Oracle blog:
https://kyuoracleblog.wordpress.com/publications/

Posted May 26, 2015 by kyuoracleblog in DAAD, Fusion IO, Oracle RAC, Sandisk

My Upcoming Presentations at Collaborate 15 Conference   Leave a comment

At the upcoming Collaborate 15 conference (April 12-April 16), I will be giving three technical presentations and joining a technical panel as panelist. The presentation schedules and abstracts are listed as follow:

1. Optimize Oracle BI with Oracle 12c in-memory Database option
OAUG presentation, session #10502, Track: Business Analytics
April 13, Monday 12:45 PM – 1:45 PM | Room Jasmine C

Abstract: By adding the in-memory columnar-store feature, the recently released In-Memory Database option is designed to significantly improve query performance for OLAP application. This presentation will explore how to leverage this In-Memory feature on the Oracle OBIEE analytics environment. This presentation will use an financial analytics project as a case study to explore the best practices, lesson learned, performance studies of applying In-Memory database in the business analytic applications.
Session link: http://coll15.mapyourshow.com/6_0/sessions/session-details.cfm?ScheduleID=2129

2. Design and implement your own self service Enabled Private cloud with Oracle Em12c

IOUG presentation and virtual session: session #464, Track: Cloud Computing
April 14, Tuesday 2:00-3:00pm| Room: Palm D
Abstract:: Today when IT organizations plan to deploy Oracle database and applications in cloud, they may look at either using Oracle public cloud service or building their own private cloud. For those who would prefer to building and hosting their own private cloud, there are a quite few Oracle technologies that are designed for this purpose. This session will discuss some of these technologies and methodologies that Oracle used to build the Oracle public cloud: Oracle EM12c cloud management, self-service portal and Oracle Virtual Assemble Builder (OVAB). Using a case study as an example, this session will show how to design and build your private cloud with these technologies and to show how to implement the important cloud service: application self-service provisioning which is featured in Oracle public cloud.
Session Link:: http://coll15.mapyourshow.com/6_0/sessions/session-details.cfm?ScheduleID=3530

3. Zero to 100MPH at the speed of cloud employing Oracle database on public cloud
IOUG presentation and Panel with panelist, session #530, panelists: Charles Kim, Kai Yu,Tariq Farooq
Track: Cloud Computing
April 15, Wednesday 9:15am-10:15am | Room: Reef F

Abstract: The Expert Panelists/Speakers will present a comprehensive overview of the Oracle Database Cloud Service offerings by covering Best Practices, Experiences, Insight, Tips and, a whole lot more about employing/deploying the Oracle Database Server family on Oracle’s Public Cloud.
Session link:: http://coll15.mapyourshow.com/6_0/sessions/session-details.cfm?ScheduleID=3531

4. Implementing oracle database 12c heat map and automatic database optimization for database storage performance and cost
IOUG presentation and virtual session: session #464, Track: Cloud Computing
April 15, Wednesday 3:15-4:15pm | Room: Palm A

Abstract: While SSD (Solid State drive) based storage significantly improves the database performance by removing the storage IO bottlenecks, the cost of SSDs is still much higher than ones of regular spinning disks. As a cost effective way to leverage SSDs performance, the Storage tiering method stores those active data in high performance SSD storage and store those less or inactive data in low speed conventional spinning storage. While the store tiering methods is usually implemented on the storage level and setup by the storage admin, Oracle 12c Heat Map and Automatic Data Optimization (ADO) together called Information lifecycle Management (ILM) provide a great way for DBAs to automate the storage tiering for database segments on database level . This session will show the steps and method by which DBAs can use to implement these Oracle 12c features to automate the compression of database segments as well as movement of them to another tier storage based on the usage of the data.
Session link: http://coll15.mapyourshow.com/6_0/sessions/session-details.cfm?ScheduleID=3555

Upcoming IOUG Cloud Computing SIG Webinar: Oracle Multitenant: The Beating Heart of #DBaaS   1 comment

Title: Oracle Multitenant: The Beating Heart of #DBaaS
Featured Speaker: Debaditya Chatterjee, Product Manager for the Oracle Multitenant
Start time: Dec 09, 2014 01:00 PM America/Eastern
End time: Dec 09, 2014 02:00 PM America/Eastern
Abstract:
The tripod of Oracle database as a service (DBaaS) rests on the strong legs of Oracle Multitenant as the enabling database architecture; Oracle Enterprise Manager for orchestration; and Oracle’s engineered systems, such as Oracle Exadata and Oracle SuperCluster, as the consolidation platforms. This total solution from a single vendor is unmatched. This session explores in detail various key capabilities of the multitenant architecture that deliver on the promise of DBaaS today.

Please sign up this webinar on this registration Link: https://www1.gotomeeting.com/register/911549736
For details, refer to IOUG webinar link: http://www.ioug.org/p/ca/vi/sid=384
Join the IOUG Cloud Computing SIG on http://www.ioug.org/p/cm/ld/fid=148&gid=60
Kai Yu
Vice President of the IOUG Cloud Computing SIG
Oracle ACE Director

Dell announced its Integrated Solution for Oracle Database at OOW14   Leave a comment

 This is the project that I have been working on in last 6 months:

Dell announced Dell Integrated Solution for Oracle Database at OOW14: fully configured infrastructure for Oracle 12C RAC : 2XR920 servers(40 cores each) + FC based 12TB mirrored/24 TB raw Sandisk all flash storage array + network/OS ready :

Dell Integrated Solution for Oracle Database : http://en.community.dell.com/dell-blogs/dell4enterprise/b/dell4enterprise/archive/2014/09/29/dell-integrated-systems-for-oracle-business-analytics

DSC08506      DSC08601

Posted October 5, 2014 by kyuoracleblog in Uncategorized

Follow

Get every new post delivered to your Inbox.

Join 71 other followers