Posted by John Hallas on June 24, 2015
On various databases, apparently unrelated we have noticed high activity that seems to be associated with the query below. The quieter the database the more the query stands out.
SELECT TO_CHAR(current_timestamp AT TIME ZONE 'GMT', 'YYYY-MM-DD HH24:MI:SS TZD') AS curr_timestamp, COUNT(username) AS failed_count, TO_CHAR(MIN(timestamp), 'yyyy-mm-dd hh24:mi:ss') AS first_occur_time, TO_CHAR(MAX(timestamp), 'yyyy-mm-dd hh24:mi:ss') AS last_occur_time
WHERE returncode != 0 AND timestamp >= current_timestamp - TO_DSINTERVAL('0 0:30:00')
Read the rest of this entry »
Posted in Oracle | Tagged: aud$ table, bug 9556885, dba_audit_session, grid agent causes high cpu, High Cpu Utilization from agent perl process, managing audit data | Leave a Comment »
Posted by John Hallas on June 23, 2015
The following technical detail was put together by a colleague John Evans and have taken it , with his permission, and wrapped some more detail around it as it seemed to be of real value to anybody who might have upgraded an agent to 126.96.36.199
Following an upgrade of the EM agent from 188.8.131.52 (or 184.108.40.206) to 220.127.116.11 after about 90 days of usage we saw a number of agents failing with out of memory errors.
We traced this down to a line in the properties file where the trace level of parameter Logger.sdklog.level=DEBUG rather than INFO Read the rest of this entry »
Posted in Grid control and agents, Oracle | Leave a Comment »
Posted by John Hallas on June 22, 2015
This morning I will pass the 1 million mark for hits on this blog. My first post was written in 2008 and I remember being quite pleased with myself when I reached 5000 hits, I never dreamt of getting 1 million.
The post with the most number of hits is https://jhdba.wordpress.com/2009/05/19/purging-statistics-from-the-sysaux-tablespace/
One that I get still comments on now saying how well it explains what the SQL92_SECURITY parameter actually does
I think my current favourite is a piece that I wrote with no preparation in an hour about what I think a good DBA Manager should be doing. It does not mean I am such a person but it does demonstrate what I think is important about that role
I still use many of the posts myself because the main reason for starting the blog was to capture issues and problems I had come across. One script I find very useful is
and https://jhdba.wordpress.com/2012/03/06/the-mother-of-all-asm-scripts/ is a standard script we use a lot at my site and is invaluable
Thanks to everyone for reading and being followers. I will continue to write new posts just as long as I keep on doing technical hands-on work although I am spending more time managing than doing these days. I still really enjoy being an Oracle DBA and I have never regretted moving back into that area after spending several years as a technical project manager – which I enjoyed a lot and I still do some PM’ing now. I was the saddo who was spotted on a beach in Majorca on a family holiday reading Jonathan Lewis’s 8i book
Posted in Oracle | 1 Comment »
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 18.104.22.168.0 Production on Sat Mar 14 06:55:08 2015
Oracle Database 11g Express Edition Release 22.214.171.124.0 - 64bit Production
create user test identified by test;
set timing on
ALTER SESSION SET sql_trace=TRUE;
drop user test;
ALTER SESSION SET sql_trace=FALSE;
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: a2p, awk trace file, convert awk to perl, dbms_cdc_utility.drop_user, drop user, ORA-01940: cannot drop a user that is currently connected, perl trace file, sql_trace, tkprof, tkprof infile | 2 Comments »
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
That maps to the :0 in the DISPLAY command you export
So if you want a second installer to run you change the display number to 1 and use
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: display window, dual installs using xterm, export DISPLAY, running 2 oracle installations together, xming | Leave a Comment »
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 126.96.36.199 and in my opinion is really helpful. Read the rest of this entry »
Posted in 12c new features, Oracle | Tagged: datapatch, Execute 'select dbms_sqlpatch.verify_queryable_inventory from dual', MOS Note 1609718.1, ORA-22285: non-existent directory or file for FILEEXISTS operation, Queryable inventory could not determine the current opatch status, sql patching tool | Leave a Comment »
Posted by John Hallas on February 19, 2015
I noticed the error message when running lsinventory against a 188.8.131.52 Oracle_Home. As the command worked I didn’t think anymore of it until on the same server against an 184.108.40.206 home I got the same error message.
tr: extra operand `y'
Try `tr --help' for more information.
/app/oracle/product/220.127.116.11/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
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 18.104.22.168
Posted in Oracle | Tagged: line 384: [: =: unary operator expected, opatch lsinventory | 2 Comments »
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: alter database link, change database link password | 2 Comments »