Apropos – unix command

This isn’t directly Oracle related but it did come to light within an Oracle context

We had a problem with a password file in a RAC cluster and whilst they appeared to be the same on both nodes of a cluster there were issues. I knew we needed to check the size of the files and ensure they were the same but I could not remember the (l)Unix command.

I knew it was to do (more...)

Identifying when a password was last changed

Security asked to determine when certain passwords were last changed and I used the ptime column in user$ table to find out.

set lines 120 set pages 60
col name form a30
col Last_Changed form a12

   SELECT name,ptime "Last_Changed"
   FROM sys.user$ a, dba_users b
   where a.name=b.username
   order by 1;
NAME                           Last_Changed
 ------------------------------ ------------
 ANONYMOUS                      24-AUG-13
 APEX_030200                    24-AUG-13
 APEX_PUBLIC_USER               24-AUG-13
 APPQOSSYS                      24-AUG-13
 CTXSYS                         24-AUG-13
 DBSNMP                         13-MAY-16**
 DIP                            24-AUG-13
 EXFSYS                          (more...)

Killing sessions across multiple instances

I am sure every DBA has used a kill session script before, although a lot probably use OEM as much now as individual scripts.

However I had not realised there was an option to add a 3rd parameter, the instance _id to the command

For example to kill any DBSNMP connections across all nodes of a RAC database

select 'alter system kill session ''' || sid || ', ' || serial# || ', @' ||  (more...)

Greyed out connections in SQL Developer

After upgrading to SQL Developer a number of users noticed that some connections in the left hand pane were greyed out. I had the same experience for myself. There did not seem to be any pattern as to why specific connections were chosen and not others. Once greyed out they remained greyed out permanently – the selection did not change

I have not been able to find out an answer as (more...)

OEM 12 AWR Warehouse – Not quite the finished product (yet)

My colleague John Evans  sits next to me and after hearing another grumble encountered while using the 12c OEM AWR Repository I asked him to write-up some of the issues he had come across and their fixes / workarounds. It does appear to me that the idea is fantastic but it is not been given sufficient exposure to sites that would be wanting to put a lot of data into it from various sources (more...)

2015 in review

The WordPress.com stats helper monkeys prepared a 2015 annual report for this blog.

Here’s an excerpt:

The Louvre Museum has 8.5 million visitors per year. This blog was viewed about 210,000 times in 2015. If it were an exhibit at the Louvre Museum, it would take about 9 days for that many people to see it.

Click here to see the complete report.

ORAchk is released – what does it offer ?

I have looked at the ORAchk tool before and thought that I must do something with it and I have now had a more detailed review of what it offers.

Firstly the history, it started off as RACchk and then morphed into ORAchk with a sibling called EXAchk. The recent release version is which is available for MoS Doc_Id 1268927.2

It can perform an ever-growing list of checks against (more...)

Memory advisors – how reliable?

I like the memory advisors that are available from the OEM GUI or the direct database tables themselves.  If I want a quick overview of whether a memory area (shared_pool, cache or the overall memory target itself) needs reviewing look there first. Note that I did not include PGA here specifically, I am well aware that PGA is included with the MAX_MEMORY_TARGET value   – I am not considering that view in my discussion (more...)

Recruiting for DBAs

My team has vacancies for several permanent DBAs. We are a supermarket chain based in Bradford, West Yorkshire.

The estate is split between HPUX and Linux (mostly OEL but moving to RH). The oracle versions in production are primarily 11Gr1,11GR2 and 12c with a little 10g, all managed through OEM

The key requirements are knowledge of DataGuard, RMAN and RAC.

It is an interesting site and we do a lot of work looking at new (more...)

Managing plans – identifying which plans have been used

This blog has the aim of answering quetions about how a sql statement has performed and which plan it has used. I have asked the type of questions that we as DBAs normally have to answer and shown how an answer may be arrived at.

We know the SQL_ID and we want to see what has been happening

set long 5000 lines 160
select sql_text from dba_hist_sqltext where sql_id = '7vt1xg0afxkba';
 INSERT INTO  (more...)