May 21

SQL Monitoring in SQL Developer

In my last article for SearchOracle (referenced in my last post to this blog) I showed an example of how I quickly got to the root cause of a poor performing query using SQL Monitoring in Enterprise Manager. Today, That Jeff Smith of SQL Developer fame showed me a blog entry he wrote last October on using SQL Monitoring in SQL Developer. Pretty cool stuff! I can’t explain it any better than he can so please click on the last link there to see a video of it in action.

May 19

Proper Tools Make Tuning Fast Work

I am starting to write articles for publishing on SearchOracle.com. I have been loosely affiliated with them since 2001, mostly in their Ask The Experts section. My first article is on the benefits that tools can play in making your tuning life much easier. This article is posted here: http://searchoracle.techtarget.com/tip/Proper-tools-make-fast-work-of-SQL-performance-tuning

I’m excited to publish more articles. While I get about 40,000 hits per month on this blog, SearchOracle.com gets so much more.

May 15

SEC_CASE_SENSITIVE_LOGON Deprecated in 12c

I’m working on getting my companies databases upgraded to Oracle 12.1.0.2 before our 11.2.0.4 databases lose free Extended Support in Jan 2016. One of the “gotchas” for us is that the SEC_CASE_SENSITIVE_LOGON parameter is deprecated in 12c. It’s still there, but who knows for how long.

Before I arrived at this company, they upgraded to Oracle 11.1 when this parameter was introduced. The DBA at the time used the parameter to kick the proverbial can down the road and avoid having to deal with changing the application to use case-sensitive logons. So now its time for me to make sure it gets handled before the parameter is extinct.

May 07

SQL Developer 4.1 Released

SQL Developer 4.1 was released on Monday. Here is the link to download the latest/greatest version.

 

ThatJeffSmith has a nice recap of the new features on his blog. I like the new formatting options and the Multi-Cursor Support is awesome! The Instance Viewer is cool, but I typically have a session on in EM12c which meets most of my needs.

 

 

Apr 23

Oracle RAC on my Laptop with Virtual Box

This really isn’t a new topic. I’m not first one to publish something like this. But I had a need to get these instructions posted on my blog. So here is how I created a two-node Oracle RAC testbed on my laptop. An earlier blog post shows why I think testbeds are important. When I wrote Oracle RAC Performance Tuning, I used such a testbed for much of the material I put in the book. At one point, I had a 3-node RAC cluster on my laptop by adding another VM and adding it to the cluster.

My instructions show how I got Oracle RAC 12.1.0.2 on my MacBook Pro with Virtual Box. If you use another VM hypervisor, then you can most likely get instructions with a simple Google search.

The instructions were too big for a blog entry, so I put it in a Word document (9.63MB).

http://www.peasland.net/documents/Build_Oracle_RAC_VMs.docx

Apr 09

SQL Server Missing Log File

We recently had one of our SQL Server instances experience a very large volume of change. Not suprisingly, the transaction log grew really big and was very close to filling up the disk. I had our SysAdmins add more space to the transaction log disk. The process that generated the large volume of transactions is a one-off process and I wanted to shrink the transaction log and let the SysAdmin take his space back to the storage pool. So I did as any SQL Server DBA might do. I took a backup of the transaction log. After that, I attempted the shrink operation.

Note: I normally don’t shrink the transaction logs. I only do this when I now a non-regular process grew it too big and I want to get back to something more normal.

 

When I attempted the shrink operation on the transaction log file, I received this error:

Msg 8985, Level 16, State 1, Line 1
Could not locate file 'xxx_Log' for database 'master' in sys.database_files. 
The file either does not exist, or was dropped.

Hmm…the only thing I did was to backup the transaction log. So this is odd. I check the file system and sure enough, the file was there. So why doesn’t SQL Server see it? I discovered my answer when I queried sys.master_files. This view had no knowledge of any transaction log for that database. The database is in FULL recovery mode so my first check was to make sure that transactions could occur in the database. They could so my end users don’t notice the issue. The application appears to be fine. So how do I get the logfile back?

The first thing I did was take an backup of the instance. That way, if my method to fix screws it all up, I can at least go back to this known state. I then detached the problematic database. I then use the sp_attach_single_file_db procedure to re-attach the database as follows:

USE [master]
GO
EXEC sp_attach_single_file_db @dbname='xxx',
@physname=N'P:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\xxx.mdf'
GO
File activation failure. The physical file name "V:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\Tfs_NAUCollection_Log.ldf" may be incorrect.
New log file 'V:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\xxx_log.ldf' was created.

 

SQL Server knew that the transaction log was missing so it created it for me. I was back in business! The last thing was to take another backup that contained my working, well-functioning database.

Apr 06

EM12c Now Allows DB12c for Repos

It is now possible to use Oracle 12c as a repository database for Enterprise Manager 12c Cloud Control. Oracle 12c is almost 2 years old and the first patch set is almost 1 year old. So its about time in my opinion.

If you are planning on using DB12c for your EM12c repository, then make sure you read Note 1987905.1 for important information on the patches you’ll need to apply.

Mar 02

GWFG in Oracle RAC

I have a deadlock being reported in my 3-node Oracle RAC database (version 11.2.0.4) as can be seen in the Alert Log. Being that this is an Oracle RAC database, resources are managed globally and the Lock Manager Daemon (LMD) gets involved. The message in the alert log pointed me to a LMD trace file which contained this Global Wait-For-Graph (GWFG).

*** 2015-02-27 04:16:33.183
Submitting asynchronized dump request [1c]. summary=[ges process stack dump (kjdglblkrdm1)].
Global blockers dump end:-----------------------------------
Global Wait-For-Graph(WFG) at ddTS[0.394d] :
BLOCKED 0x551b2c698 2 wq 2 cvtops x1 TM 0x11ffa3.0x0(ext 0x0,0x0)[6B000-0004-0000022D] inst 4
BLOCKER 0x5513ed318 2 wq 2 cvtops x1 TM 0x11ffa3.0x0(ext 0x0,0x0)[B6000-0006-00000099] inst 6
BLOCKED 0x5513ed318 4 wq 2 cvtops x1 TM 0x11ffa3.0x0(ext 0x0,0x0)[B6000-0006-00000099] inst 6
BLOCKER 0x5513ef5b8 4 wq 2 cvtops x1 TM 0x11ffa3.0x0(ext 0x0,0x0)[66000-0005-00000FDB] inst 5
BLOCKED 0x5513ef5b8 4 wq 2 cvtops x1 TM 0x11ffa3.0x0(ext 0x0,0x0)[66000-0005-00000FDB] inst 5
BLOCKER 0x551b2c698 4 wq 2 cvtops x1 TM 0x11ffa3.0x0(ext 0x0,0x0)[6B000-0004-0000022D] inst 4
* Cancel deadlock victim lockp 0x551b2c698

 

Note:  The actual SQL and a few other details may have been changed to protect the innocent.

So there I have 3 sessions involved in the deadlock. One each on instance id 4, 5, and 6.

 

I was looking at the trace file generated on instance id 4. Above the GWFG was this information:

 

user session for deadlock lock 0x551b2c698
 sid: 1727 ser: 539 audsid: 996549224 user: 13944/MP_SYS
 flags: (0x41) USR/- flags_idl: (0x1) BSY/-/-/-/-/-
 flags2: (0x40009) -/-/INC
 pid: 107 O/S info: user: oracle, term: UNKNOWN, ospid: 11229
 image: oracle@myhost04.naucom.com
 client details:
 O/S info: user: web-svc, term: web-server1, ospid: 4276:864
 machine: DOMAIN\web-server1 program: iis.exe
 client info: user: WEBSERVICE
 current SQL:
 INSERT INTO MP_SYS.T2( column_list) SELECT column_list FROM MP_SYS.T1 WHERE MP_SYS.T1.P_ID=:B1
DUMP LOCAL BLOCKER: initiate state dump for DEADLOCK
 possible owner[107.11229] on resource TM-0011FFA3-00000000

 

So I can see information about the machine, program, and SQL statement being executed. The user session identifier in red above corresponds to the id value in the GWFG. Lets look at the first two lines of the GFWG again.

BLOCKED 0x551b2c698 2 wq 2 cvtops x1 TM 0x11ffa3.0x0(ext 0x0,0x0)[6B000-0004-0000022D] inst 4
BLOCKER 0x5513ed318 2 wq 2 cvtops x1 TM 0x11ffa3.0x0(ext 0x0,0x0)[B6000-0006-00000099] inst 6

So the SQL statement and session details match this first line. This session is blocked on inst 4. The blocker is on inst6 and is identified as 0x5513ed318. Well who is this? There are no other details in this GWFG to help us with the BLOCKER.

To learn more about the blocker, I went to inst 6 and did this:

cd /u01/app/oracle/diag/rdbms/admin/orcl/orcl6/trace
grep 0x5513ed318 *

So I just did a grep on the session identifying value and was given a LMD trace file. Looking in that LMD trace file on the other instance gives me details about the blocking session.

user session for deadlock lock 0x5513ed318
 sid: 1206 ser: 2673 audsid: 996459926 user: 13944/MP_SYS
 flags: (0x41) USR/- flags_idl: (0x1) BSY/-/-/-/-/-
 flags2: (0x40009) -/-/INC
 pid: 182 O/S info: user: oracle, term: UNKNOWN, ospid: 7049
 image: oracle@myhost06.naucom.com
 client details:
 O/S info: user: web-svc, term: web-server2, ospid: 4276:864
 machine: DOMAIN\web-server2 program: iis.exe
 client info: user: WEBSERVICE
 current SQL:
 DELETE FROM MP_SYS.T1 WHERE P_ID = :B1
DUMP LOCAL BLOCKER: initiate state dump for DEADLOCK
 possible owner[182.7049] on resource TM-0011FFA3-00000000

I can see that the blocked session was issuing a INSERT statement on a table and the blocker was issuing a DELETE on the same table.

There is another session involved but at this point, it is elementary to get session details using the same steps above.

 

Hopefully, this blog entry has shown how to use the GWFG to diagnose the SQL statements and the object involved in the global deadlock. I know know the exact SQL statements involved in the deadlock, and by extension, the objects involved as well. The resolution to the problem is no different than deadlock resolution in single-instance databases.

 

For more information about Oracle RAC Global Enqueue Services (GES), read Chapter 2 of my Oracle RAC Performance Tuning book.

 

 

 

 

 

 

Feb 12

Oracle RAC Performance Tuning

I am proud to announce that my book is now available for purchase! Please order Oracle RAC Performance Tuning today!

http://www.amazon.com/Oracle-RAC-Performance-Tuning–Focus/dp/0986119415/ref=sr_1_2?ie=UTF8&qid=1423756491&sr=8-2&keywords=peasland

 

Writing this book was a great experience for me and I hope the readers get value out of it.

 

 

Jan 27

Sample Schemas on GitHub

Oracle’s sample schemas are an extra download. For Oracle 12c, the sample schemas are in file 7 of 8 of the downloads. So if you want the sample schemas, you have to go to OTN or Metalink for the patchset version and download the file.

Someone also posted the sample schemas on GitHub here: https://github.com/oracle/

To give full credit, this was not anything done by me. The blog that brought this to my attention is here: https://blogs.oracle.com/opal/entry/a_personal_victory_oracle_database

Older posts «