Oracle DBA – A lifelong learning experience

Getting sql statements out of a trace file

Posted by John Hallas on March 15, 2015

The focus on this post started off in one direction and ended up in another. Originally I had been running a drop user script which had hung and even when I killed the process I could not drop the users as it gave a “ORA-01940: cannot drop a user that is currently connected” – despite the users having left the company months ago and there being no chance of them actually having connected sessions. My suspicions were that the drop user command actually took a lock on the users or connected as them whilst dropping them.  I was also intrigued by the length of time it took to drop users who had no objects.  Therefore I created a user, dropped it and traced the session to see what was happening. I was amazed by the size of the output file and that is where the direction changed. I wanted to find an easy way to get all the lines of code out of  trace file so that I could review them quickly.

SQL*Plus: Release 11.2.0.2.0 Production on Sat Mar 14 06:55:08 2015
Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production

create user test identified by test;
User created.
set timing on
ALTER SESSION SET sql_trace=TRUE;
Session altered.
Elapsed: 00:00:00.00
drop user test;
User dropped.
Elapsed: 00:00:00.26
ALTER SESSION SET sql_trace=FALSE;
Session altered.
Elapsed: 00:00:00.00

I now had a trace file and I used the insert=filename parameter of tkprof to produce a script containing all the sql_statements in the trace file, in the same order.

tkprof xe_ora_5796.trc xe_ora_5796.tkp insert=xe_ora_5796.sql

Edit the sql file that is produced (xe_ora_57967.sql in my example) , changing the field SQL_STATEMENT from LONG to a varchar2(4000) Read the rest of this entry »

Posted in Oracle | Tagged: , , , , , , , , , | 1 Comment »

Running two oracle installations from the same terminal

Posted by John Hallas on February 23, 2015

Two posts from me on the same day. The other one about Datapatch is about a brand new utility in 12c and is probably new to most people. This post caused mixed reactions when I mentioned it at work last week. Some people laughed at my naivety in not knowing about it, others took the same view as me and were interested to hear about it as it may prove useful one day.

A colleague had a double installation of new Oracle binaries on primary and standby servers and I suggested he get someone else to run one in parallel as he could not run two installers at the same time. He came back later on to show how it could be done and prove me wrong.

When starting up Xming using the Launch script the first window that comes up has a Display number of 0 at the bottom

xming_1

 

That maps to the :0 in the DISPLAY command you export

export DISPLAY=10.3.127.4:0.0

 So if you want a second installer to run you change the display number to 1 and use

export DISPLAY=10.3.127.4:1.0

And voila you can have 2 xming installer sessions running in parallel

The last 0 (.0) is used to have multiple screens within the same display – something that is not used very frequently these days.

So if you think I have wasted your time feel free to ignore this post and if you have found it useful I would happy to get comments saying so.

 

Posted in Oracle | Tagged: , , , , | Leave a Comment »

Issue with Datapatch – AKA SQL Patching Tool after cloning a database

Posted by John Hallas on February 23, 2015

There have been a few changes in the way patches are managed and monitored in 12c and whilst looking at this I found a potential problem that might occur when you clone or copy databases around, or even build them from a template file.

Firstly when you apply a PSU and run an opatch lsinventory command you now see a description of the patch rather than just a patch number – here showing that PSU 1 has been applied. This came in at 11.2.0.3 and in my opinion is really helpful. Read the rest of this entry »

Posted in 12c new features, Oracle | Tagged: , , , , , | Leave a Comment »

opatch lsinventory gives “line 384: [: =: unary operator expected”

Posted by John Hallas on February 19, 2015

I noticed the error message when running lsinventory against a  12.1.0.2 Oracle_Home. As the command worked I didn’t think anymore of it until on the same server against an 11.2.0.1 home I got the same error message.

opatch lsinventory
 tr: extra operand `y'
 Try `tr --help' for more information.
 /app/oracle/product/11.2.0.1/dbhome_1/OPatch/opatch: line 384: [: =: unary operator expected

There is a Mos note which provides a solution – 1551584.1

Modify following line (line number 384) in file $ORACLE_HOME/OPatch/opatch
if [ `echo $arg | tr [A-Z] [a-z]` = "-invptrloc" ]; then
to
if [ `echo $arg | tr A-Z a-z` = "-invptrloc" ]; then

However the real problem is caused by the presence of a file with a single character name in the current directory. Indeed there was such a file ‘x’ and once that was removed then the opatch lsinventory command worked as normal.

This bug appears when a new version of opatch is installed , in my case I had just added opatch version 12.1.0.6

Posted in Oracle | Tagged: , | 2 Comments »

Changing a database link password

Posted by John Hallas on February 13, 2015

I recently found out  that it is possible to change a database link password without dropping and recreating a database link in its entirety.

To be honest I thought this might have existed forever and I had just never come across it but it actually come out in 11GR2

The ALTER DATABASE LINK statement can be used and you do not need to specify the target service either  – all you need  is to run the following command from the user that owns a pre-existing database link

ALTER DATABASE LINK JOHN connect to USER identified by  PASSWORD;

I know it is not a major change but a quick canvas amongst fellow DBAs and nobody had noticed it’s arrival either so a heads-up might be helpful to someone

Posted in 11g new features, Oracle | Tagged: , | 2 Comments »

New ASM power levels in 11.2.0.2 and beyond

Posted by John Hallas on February 13, 2015

I recently saw the following command in a script that was to be run and thought an error had been made and the power level should have been 5 not 500.

ALTER DISKGROUP DATA REBALANCE POWER 500;

Upon doing some research it was not a mistype but a new method of disk balancing which came in from 11.2.0.2

Previously setting the power limit from 0 to 11 basically caused an additional number of ARBx process to be created to match the power level and these were removed once the rebalance had finished.

That was a nice simple situation which I had no problem with. The range was adequate and I normally used between 4 and 7 depending on the usage of the system and any performance impact that might be caused. The impact was easy to monitor using a variety of tools as top or glance on a *nix platform

Now from 11GR2 onwards and when a database has disk group ASM compatibility set to 11.2.0.2 or greater the operational range of values is 0 to 1024 for the rebalance power. Note that if the value of the POWER clause is specified larger than 11 for a disk group with ASM compatibility set to less than 11.2.0.2, then a warning is displayed and a POWER value equal to 11 is used for rebalancing. Second point to note is that if a disk group is altered to a higher RDBMS value this operation cannot be reversed.

So what does that mean in practise? Well in my eyes it seems to be a basic change but it now seems very hard, well-nigh impossible to see the impact that the re-balance is having on the server and consequently I do not see the advantages of it other than possibly on massively high-end systems. Read the rest of this entry »

Posted in 11g new features, ASM, Oracle | Tagged: , , | 2 Comments »

Stopping one ASM listener in Flex ASM environment takes down ASM instance

Posted by John Hallas on January 6, 2015

Stopping one ASM listener in Flex ASM environment takes down ASM instance

This is a heads-up about behaviour we are seeing during OAT testing on a 12c environment. We are running OAT tests on a new 12.1.0.2 Grid Infrastructure environment using Flex ASM (OEL 5.8) and when taking down one of the two ASMNETLSNR listeners on one of the nodes, the ASM instance running on that node is taken down. We assume this is due to a hard dependency between the listener and the ASM instance. Unfortunately this does not give us a great deal of resilience: as we have two ASMNETLSNR listeners normally running on each node we would expect to be able to lose one of them without losing an ASM instance.

ps -ef | grep tns
root 769 2 0 Nov10 ? 00:00:00 [netns]
oracle 8386 1 0 Nov19 ? 00:00:09 /app/gridsoft/12.1.0.2/bin/tnslsnr ASMNET1LSNR_ASM -no_crs_notify -inherit
oracle 8389 1 0 Nov19 ? 00:00:12 /app/gridsoft/12.1.0.2/bin/tnslsnr LISTENER_DG -inherit
oracle 8500 1 0 Nov19 ? 00:00:51 /app/gridsoft/12.1.0.2/bin/tnslsnr LISTENER -no_crs_notify -inherit
oracle 8621 1 0 Nov19 ? 00:00:13 /app/gridsoft/12.1.0.2/bin/tnslsnr LISTENER_SCAN1 -no_crs_notify -inherit
oracle 117429 1 0 13:57 ? 00:00:00 /app/gridsoft/12.1.0.2/bin/tnslsnr ASMNET2LSNR_ASM -no_crs_notify -inherit 

We are stopping the listener in a consistent and standard manner

srvctl stop listener ASMNET... -n node_name –f

 

Oracle did refer us to the following note.
Bug 14155526 : [12100-LIN64] BC: FAILED TO STOP ASM LISTENER WITH DEPENDENCY OF “ORA.ASM” RES >> this bug is closed as not bug . since its expected behavior.

This is expected behavior. ASM cannot be stopped on the node where the CRSD PE is running.

However we thought that you should be able to take an ASM listener down without forcing the database down as well. We have HAIP with two ASM Listeners per node. If we take one of the ASM listeners on a node down surely the other ASM Listener should be able to service the ASM instance on that node without the need for ASM going down. So why is there a hard dependency for the ASM listener?

 

Oracle said that this was expected behaviour but we still did not agree. They took the view that it works as per the design – confirmed by their dev team.

Based on the internal   <a href="https://support.oracle.com/epmos/faces/BugDisplay?id=14347014">Bug 14347014</a> : LNX64-12.1-SRVM-FPF:NEED AN OPTION TO REMOVE ASM LISTENER ALSO UPDATE DEPENDENCY

there is option provided by dev team to remove asm listener and update the dependency

A new command

srvctl update listener [-listener  -asm -remove [-force]]

is provided to remove asm listener and update dependency

note: srvctl update listener -listener listener_asm3 -remove -asm [-force]

If '-remove' and '-asm' are not specified together, then we should error out

 

We were  still not satisfied that this is how it should work if there are multiple ASM listeners/interfaces.
I can understand that if there is only one interface for the ASM instances to communicate on via the ASM listener, then it could be argued that taking the listener down should take down the non-critical ASM instances. But we have set up HAIP and are utilising two network interfaces, the whole point of this configuration is to provide greater resilience, therefore we should be able to lose one listener, either through failure or a need to restart it, without killing ASM instances.
It looks like the hard dependencies have been configured so that ASM will fail if ANY of the ASM listeners are not present, it should be configured so that ASM should only fail if ALL of the ASM listeners are not present.

Oracle have now confirmed that in 12.2 there will be an enhancement to change the dependencies between ASM and the ASM listener.

If you are testing resilience between nodes on a new 12c cluster then it might be worth stopping an ASM listener and seeing what the impact is.

Posted in 12c new features, ASM, Oracle | Tagged: , | 3 Comments »

My bizarre question of 2015 already

Posted by John Hallas on January 5, 2015

To be honest it was asked 2 years ago in a blog about ASM and rebalancing and someone asked the following question

Can I upgrade ASM from 10205 to 11203 while ASM rebalancing is in progress?
and the answer, unsurprisingly, was
No.
Why would anyone even think of asking that question never mind contemplating the idea of running an upgrade under such circumstances.
Needless to say my next blog post is about ASM related activities – coming very shortly

Posted in ASM, Oracle | Tagged: | Leave a Comment »

Using grant connect through to manage database links

Posted by John Hallas on December 4, 2014

Nobody can say that I am not current and topical with my posts. This post refers to functionality that was introduced in 9i, however I have just come across it and thought it useful enough to blog about it.

The command ALTER USER USERB GRANT CONNECT THROUGH USERA allows a proxy connection to be made using the username and password of USERA but  to connect in as USERB. The purpose is so that management of a user can be done without knowing that users password or changing it. This is most commonly going to be used by support teams.

I will give an example

SQL> create user test1 identified by password;

User created.

SQL> grant create session , resource , unlimited tablespace to test1;

Grant succeeded.

SQL> create user test2 identified by password;

User created.

SQL> grant create session , resource , unlimited tablespace to test2;

Grant succeeded.

SQL> alter user test2 grant connect through test1;

User altered.

SQL> connect test1[test2]/password
Connected.
SQL> show user
USER is "TEST2"

Here is a way that I can create a database link as user2 without knowing that users password. User2 has to have create database link privilege.

SQL> connect / as sysdba
Connected.
SQL> grant create database link to test2;

Grant succeeded.

SQL> connect test1[test2]/password
Connected.
SQL> show user
USER is "TEST2"
SQL> create database link TSTAUDIT connect to remote1 identified by password using 'TSTSAUDIT';

By the way as a privileged user you can apparerently create a link in another schema

SQL> connect / as sysdba
Connected.
SQL> create database link test2.TSTAUDIT connect to remote1 identified by password using 'TSTSAUDIT';

Database link created.

But the link is still owned by SYS but has the name TEST2.TSTAUDIT

SQL> select owner, DB_LINK username, host from dba_db_links;

OWNER                          USERNAME    HOST
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SYS                            TEST2.TSTAUDIT TSTSAUDIT

drop database link "TEST2.TSTAUDIT";

 

 

Posted in Oracle | Tagged: , , | 1 Comment »

Controlling RMAN channels on RAC

Posted by John Hallas on December 3, 2014

This was sent to me for posting  by my friend and ex-colleague Vitaly Kaminsky ….

I have recently worked with the customer where standard RMAN backups of production 2-node cluster (11.2.0.3) were getting too big and taking longer than 24 hours to run.

The problem with this particular cluster was the fact that ALLOCATION of RMAN connections to the instances of the cluster was controlled by SCAN and driven by the load-balancing algorithm of SCAN.

The result was rather undesirable allocation of all channels to a single instance and the only way to control it was to reduce the workload on all nodes at the time when backup is started which was unacceptable on this 24/7 constant workload system.

This single-node allocation caused the network connections (10GB network) to become saturated, throttling the backup rates and degrading the “user experience” for user sessions on the same node.

After considerable search, I found a way of controlling this allocation I did not know about before (just showing my lack of attention when reading low-level manuals).

The way RMAN is using parameters is as follows:

  1. Use the instructions from the script
  2. If there are no script instructions, use the defaults.

How to force RMAN to “distribute” the channels across instances:

  1. Set required degree of parallelism in default parameters:

 

CONFIGURE DEVICE TYPE DISK PARALLELISM 4 BACKUP TYPE TO BACKUPSET;

RMAN> show all

CONFIGURE DEVICE TYPE DISK PARALLELISM 4 BACKUP TYPE TO BACKUPSET;
  1. “Hard-code” the channel allocation in the defaults:
CONFIGURE CHANNEL 1 DEVICE TYPE DISK CONNECT='sys/password@node1:1521/clusterservice' FORMAT='/backup/FULL/%d_%U';

CONFIGURE CHANNEL 2 DEVICE TYPE DISK CONNECT='sys/password@node1:1521/clusterservice' FORMAT='/backup/FULL/%d_%U';

CONFIGURE CHANNEL 3 DEVICE TYPE DISK CONNECT='sys/password@node2:1521/clusterservice' FORMAT='/backup/FULL/%d_%U';

CONFIGURE CHANNEL 4 DEVICE TYPE DISK CONNECT='sys/password@node2:1521/clusterservice' FORMAT='/backup/FULL/%d_%U';
  1. DO NOT allocate channels in the script:
RMAN> run {

2> backup as COMPRESSED BACKUPSET tag ‘FULL_112014100000' database;

3> }
  1. Check how channels are allocated:
RMAN> backup as COMPRESSED BACKUPSET tag ‘FULL_112014100000' database;

Starting backup at 24-NOV-14

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=871 instance=racservice1 device type=DISK

allocated channel: ORA_DISK_2

channel ORA_DISK_2: SID=970 instance=racservice1 device type=DISK

allocated channel: ORA_DISK_3

channel ORA_DISK_3: SID=1453 instance=racservice2 device type=DISK

allocated channel: ORA_DISK_4

channel ORA_DISK_4: SID=399 instance=racservice2 device type=DISK

channel ORA_DISK_1: starting compressed full datafile backup set

 

There are 2 issues I can think of with this approach:

I am not keen on passing the passwords to any services, but RMAN is smart enough to cater for this:

RMAN> show all;

using target database control file instead of recovery catalog

RMAN configuration parameters for database with db_unique_name RACSERVICE are:

CONFIGURE DEVICE TYPE DISK PARALLELISM 4 BACKUP TYPE TO BACKUPSET;

CONFIGURE CHANNEL 1 DEVICE TYPE DISK CONNECT '*' FORMAT   '/backup/FULL/%d_%U';

CONFIGURE CHANNEL 2 DEVICE TYPE DISK CONNECT '*' FORMAT   '/backup/FULL/%d_%U';

CONFIGURE CHANNEL 3 DEVICE TYPE DISK CONNECT '*' FORMAT   '/backup/FULL/%d_%U';

CONFIGURE CHANNEL 4 DEVICE TYPE DISK CONNECT '*' FORMAT   '/backup/FULL/%d_%U';
  1. Correcting mistakes.

While typing, I made some mistakes and ended up with the wrong CONFIGURE CHANNEL lines.

How do you delete those lines?  Simples:

CONFIGURE CHANNEL 1 DEVICE TYPE DISK CLEAR;

The result of the above?

We now have even distribution of RMAN workload and much higher utilisation of available network bandwidth on both nodes!

 

Posted in Oracle | Tagged: , , , | Leave a Comment »

 
Follow

Get every new post delivered to your Inbox.

Join 230 other followers