Oracle DBA – A lifelong learning experience

EXcellent blog post on SQL Profiles

Posted by John Hallas on July 5, 2016

Sometimes you across a blog entry and you think to yourself – ‘I wish I had written that’.

It is clear, it is on a subject you are interested in and the examples help illustrate the points clearly.

Well I did come across just such an article today. It was written by Franck Pachot and was on the subject of SQL Profiles

It was written 18 months ago and has only had 1800 hits. I think it deserves many more

Posted in 11g new features, 12c new features, Oracle | Leave a Comment »

HP Systems Management vacancies – Bradford , West Yorkshire

Posted by John Hallas on June 23, 2016

I have recently taken over our Systems  Management team and we have several vacancies for people used to working with the HP toolset. I know my blog is read almost entirely by DBAs but I am sure some of you work with Systems management teams and hopefully you can pass a link on to anyone you think might be interested.

The main tools used are

  • HP Service manager 9.x
  • HP Operations manager for Windows, Operations Agent, OMI 10.10
  • HP SIM 7.5
  • HP NNMi 10.10
  • HP uCMDB 10.22
  • HP BSM 9.26
  • HP Operations Orchestration

We are looking for 2 candidates, one to manage the Design/Implementation/Administration of integrated solution and bring expertise in one or more of the above toolsets, the other person wil have experience but probably not to such a great depth

  • Design/Implementation/Administration of HP Ops Bridge / Operations Manager.
  • Design/Implementation/Administration of integrated solutions (Eg. Case to case incident exchange)
  • Understanding of working with an enterprise license agreement

We are also recruiting for 2 contractors for about 6 months worth of backfill – that has gone out to the agencies we normally use.

The permanent vacancy links are  here

Systems Management Technology Specialist

 

 

 

Posted in Oracle | Leave a Comment »

Oracle DBA vacancy – Bradford, West Yorkshire

Posted by John Hallas on June 22, 2016

My team has a permanent vacancy which almost anyone could apply for.

We are looking for a good DBA with RAC, RMAN and Dataguard skills

We  are thinking that we might wish to take on a Junior DBA who has some skills but maybe not all three listed above.

We would also consider someone who has no real experience  – maybe has used RDBMS at University

Whoever we take-on will get a broad experience of a lot of database products running on Oracle 10g-12c (the majority being 11GR2)  on HP, OEL, Red Hat.

http://ldd.tbe.taleo.net/ldd03/ats/careers/requisition.jsp?org=MORRISONSLOCAL&cws=48&rid=7754

Posted in Oracle | Leave a Comment »

Vote for “Create assertion” to be added

Posted by John Hallas on May 31, 2016

There is a standard for SQL code known as ANSI-92  which is adopted by many of the main RDBMS vendors. However each vendor has its own differences from the standard.

Oracle are currently taking votes on a proposal to add the CREATE ASSERTION statement to Oracle SQL on the OTN community page  https://community.oracle.com/ideas/13028

Here are two examples of how the statement could be used

This SQL statement creates an assertion to demand that there’s no more than a single president among the employees:

create assertion AT_MOST_ONE_PRESIDENT as CHECK
((select count(*)
    from EMP e
   where e.JOB = 'PRESIDENT') <= 1
)

This SQL statement creates an assertion to demand that Boston based departments do not employ trainers:

create assertion NO_TRAINERS_IN_BOSTON as CHECK
   (not exists
    (select 'trainer in Boston'
       from EMP e, DEPT d
      where e.DEPTNO = d.DEPTNO
        and e.JOB    = 'TRAINER'
        and d.LOC    = 'BOSTON')
   )

To implement the same functionality now you would use a combination of check constraints and/or triggers.

I have cast my vote in favour

Posted in 11g new features | Tagged: , , , , | Leave a Comment »

Apropos – unix command

Posted by John Hallas on May 17, 2016

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 with checking the bytes in a file and for some reason I was thinking it was to do with md5. Our sysadmin told me of the apropos command.

The OED defines apropos as meaning “With reference to; concerning:“.

apropos is a wrapper for the man -k command and it lists all commands similar to the  parameter passed in.

In my example I typed “apropos sum” and got the following list out and lo and behold there was cksum which I was looking for – along with md5sum

$apropos sum
DES_cbc_cksum [des]  (3ssl)  - DES encryption
DES_quad_cksum [des] (3ssl)  - DES encryption
Xmark                (1x)  - summarize x11perf results
assume_default_colors [default_colors] (3x)  - use terminal's default colors
aureport             (8)  - a tool that produces summary reports of audit daemon logs
cksum                (1)  - checksum and count the bytes in a file
cksum                (1p)  - write file checksums and sizes
getrlimit            (3p)  - control maximum resource consumption
keyctl_assume_authority [keyctl_instantiate] (3)  - Assume the authority to instantiate a key keyctl_instantiate - Instantiate a key keyctl_negate - Negatively instantiate a key
md5sum               (1)  - compute and check MD5 message digest
opreport             (1)  - produce symbol or binary image summaries
pamsumm              (1)  - Summarize the samples in a Netpbm image arithmetically
pamsummcol           (1)  - summarize (sum, average, etc) a Netpbm image by column
repquota             (8)  - summarize quotas for a filesystem
sa                   (8)  - summarizes accounting information
setrlimit [getrlimit] (3p)  - control maximum resource consumption
sha1sum              (1)  - compute and check SHA1 message digest
sha224sum            (1)  - compute and check SHA224 message digest
sha256sum            (1)  - compute and check SHA256 message digest
sha384sum            (1)  - compute and check SHA384 message digest
sha512sum            (1)  - compute and check SHA512 message digest
specspo             (rpm) - Enterprise Linux package descriptions, summaries, and groups.
sum                  (1)  - checksum and count the blocks in a file
xorg-x11-drv-summa  (rpm) - Xorg X11 summa input driver


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

Identifying when a password was last changed

Posted by John Hallas on May 16, 2016

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                         24-AUG-13
 FLOWS_FILES                    24-AUG-13
 JOHN                           13-MAY-16****
 MDDATA                         24-AUG-13
 MDSYS                          24-AUG-13
 MGMT_VIEW                      24-AUG-13
 OLAPSYS                        24-AUG-13
 ORACLE_OCM                     24-AUG-13
 ORDDATA                        24-AUG-13
 ORDPLUGINS                     24-AUG-13
 ORDSYS                         24-AUG-13
 OUTLN                          24-AUG-13
 OWBSYS                         24-AUG-13
 OWBSYS_AUDIT                   24-AUG-13
 SCOTT                          24-AUG-13
 SI_INFORMTN_SCHEMA             24-AUG-13
 SPATIAL_CSW_ADMIN_USR          24-AUG-13
 SPATIAL_WFS_ADMIN_USR          24-AUG-13
 SYS                            13-MAY-16**
 SYSMAN                         24-AUG-13
 SYSTEM                         13-MAY-16**
 WMSYS                          24-AUG-13
 XDB                            24-AUG-13
 XS$NULL                        24-AUG-13

Note that you need to join with DBA_USERS otherwise the USERS$ view shows all the roles as well, such as RECOVERY_CATALOG_ROLE and DBA.

You will see that the date shown for most  users is 24-AUG-13 which is the date the password was created by the seed database provided by Oracle which is used as part of an install. The accounts with two asterisks have their passwords created as part of user interaction when running DBCA

select name,ctime "Created" , ptime "Last_Changed"
FROM sys.user$ a, dba_users b
where a.name=b.username
and a.name =  'SYS'
order by 1
/

 

NAME                           Created   Last_Changed
------------------------------ --------- ------------
SYS                            24-AUG-13 13-MAY-16

 

Posted in Oracle, security | 3 Comments »

Killing sessions across multiple instances

Posted by John Hallas on March 23, 2016

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# || ', @' || inst_id || ''';'

from gv$session where username = DBSNMP' order by logon_time desc;

alter system kill session ‘975, 57707, @2’;
alter system kill session ‘584, 2515, @1’;
alter system kill session ‘589, 4399, @2’;
alter system kill session ‘401, 2081, @1’;
alter system kill session ‘1168, 36153, @2’;
alter system kill session ‘1350, 21961, @1’;

This parameter is only available on RAC databases – which I suppose is quite obvious really.

There is also an option to use the IMMEDIATE keyword as well, this returns control straight back to the user without waiting for the command to complete, which can be quite useful when you have a few iterations of the command to run

alter system kill session '1350, 21961, @1' immediate;

 

Posted in Oracle | Tagged: , , | 4 Comments »

Greyed out connections in SQL Developer

Posted by John Hallas on January 26, 2016

After upgrading to SQL Developer 4.1.2.20 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 to why it is happening, other than it is a bug/feature of that release.

However what I do know is that it is fixable Read the rest of this entry »

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

2015 in review

Posted by John Hallas on January 2, 2016

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.

Posted in Oracle | Leave a Comment »