Oracle DBA – A lifelong learning experience

Flashback database – dont forget the standby

Posted by John Hallas on September 21, 2016

Today an  application team had a problem with a production system and they wanted the database restored to a point a couple of days ago. It is a configuration management system and they figured they could lose any changes . Their call.

I did all the good things

  • Checked we had a change in place
  • Blacked out primary and standby in OEM
  • Checked flashback was on  –
select flashback_on from v$database;
  • Checked how far we could flashback to
set lines 120
col oldest_flashback_scn for 99999999999999999999
col oldest_flashback_time for a30
to_char(oldest_flashback_time, 'DD-MM-YYYY HH24:MI:SS')
from v$flashback_database_log;
  • Checked that standby had flashback on.

I stopped managed recovery on standby, shutdown that database, stopped primary and mounted and flashed back to my desired time. Everything was fine.

I went to standby, mounted it and ran

flashback standby database to timestamp
to_timestamp ('19/09/2016 12:00:00','dd/mm/yyyy hh24:mi:ss');
ERROR at line 1:
 ORA-38729: Not enough flashback database log data to do FLASHBACK.


That stumped me. Obviously I knew what the message meant and I knew I had not run the script to see how far we could flashback to on standby but why did we have a problem?

Flashback retention was set to 1440  – 24 hours on both primary and standby but there had been no space pressure on primary so it had retained sufficient  flashback logs to go back 6 days if required

It did not take a lot of digging around to find the explanation. Normally we have one database per server (physical or virtual) in production. We then map that  to a dedicated standby server. In this case we had 3 databases running. Due to performance considerations we run 2 primaries and a standby on one side and 2 standbys and a primary on the other. This database was not the key/busiest database and so the standby was sharing with another standby and the primary for the biggest database. That had generated a lot of logfiles which had caused space to be consumed and the flashback logs had been tidied to meet the 1440 minute parameter setting.

A rebuild of standby is under way currently.

I always try to learn lessons from incidents like this. In this case there were two unusual requirements – flashback production and go back 48 hours.

My lessons learned

Use db_recovery_file_dest_size for each database where we have more than one and set them appropriately to the size of each database.

Could we have some sort of automated routine that creates a guaranteed restore point every hour and then drops it 48 hours later. That needs thinking about as we could risk running out of space.



Posted in Oracle | Tagged: , | 3 Comments »

Commas at the beginning or end of a sql code line

Posted by John Hallas on August 18, 2016

Back in Nov 2015 I commented on a Oracle-L discussion about What happened to SQL*Developer SQL Formatter asking why people liked to see a comma at the beginning of the line in sql code and there was a fair bit of input into how it was easier to use and amend  – think of it like bullet points was one comment.

Fair enough I though and I have used that technique when I remember ever since.

Then I saw a post by Connor McDonald on how the syntax formatter  – or your own hand formatted code might give the wrong results if you are not careful about the placing of commas.

Looks like it is back to the drawing board for me then and commas on the right



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

ASM disks – lsdg compared with the v$asm_diskgroup view

Posted by John Hallas on July 8, 2016

What is the difference between the summaries of disk space on these two systems? Look at the free_usable_file_MB column and free space

System 1

ASMCMD [+] > lsdg
State    Type    Rebal  Sector  Block       AU   Total_MB   Free_MB  Req_mir_free_MB  Usable_file_MB  Offline_disks  Voting_files  Name
MOUNTED  NORMAL  N         512   4096  4194304   72769536   2031032           433152          798940              0             N  DATA_PEX0/
MOUNTED  NORMAL  N         512   4096  4194304  128286720  20004028          2672640         8665694              0             N  DATA_PEX1/
         Group                 Diskgroup     Total  Req'd     Free   Percent Disk Size Percent Percent  Disk
     Group Name       State      Redundancy       GB     GB       GB Imbalance  Variance    Free    Free Count
---------- ---------- ---------- ---------- -------- ------ -------- --------- --------- ------- ------- -----
         1 DATA_PEX0  MOUNTED    NORMAL       71,064    423    1,560       1.4        .0     2.2     3.5   168
         2 DATA_PEX1  MOUNTED    NORMAL      125,280  2,610   16,925        .1        .0    15.6    15.6    48

System 2

ASMCMD [+] > lsdg
State    Type    Rebal  Sector  Block       AU  Total_MB  Free_MB  Req_mir_free_MB  Usable_file_MB  Offline_disks  Voting_files  Name
MOUNTED  EXTERN  N         512   4096  1048576   2621412   595744                0          595744              0             N  DATA/
MOUNTED  EXTERN  N         512   4096  1048576   1048568   700029                0          700029              0             N  FRA/

                                                     Mirror                      Percent Minimum Maximum
           Group                 Diskgroup     Total  Req'd     Free   Percent Disk Size Percent Percent  Disk
     Group Name       State      Redundancy       GB     GB       GB Imbalance  Variance    Free    Free Count
---------- ---------- ---------- ---------- -------- ------ -------- --------- --------- ------- ------- -----
         1 DATA       MOUNTED    EXTERN        2,560      0      582        .2        .0    22.7    22.8    20
         2 FRA        MOUNTED    EXTERN        1,024      0      684        .0        .0    66.8    66.8     8

System 1 is an Exadata stack with NORMAL redundancy whereas System 2 uses EXTERNAL redundancy. All of our systems bar Exadata are configured with dynamic multi-pathing with external redundancy to ensure high availability. – we allow the SAN to manage redundancy

In system 1 when using the second query to interrogate the diskgroups it would appear that we have 1.5Tb of free space in DATA_PEX0  and yet the lsdg command indicates we only have 800Gb free. Quite a significant difference when we have a weekly growth rate of ~250Gb in that diskgroup

SELECT g.group_number  "Group" ,          "Group Name" ,      g.state         "State" ,      g.type          "Type" ,      g.total_mb/1024 "Total GB" ,      g.free_mb/1024  "Free GB" ,      100*(max((d.total_mb-d.free_mb)/d.total_mb)-min((d.total_mb-d.free_mb)/d.total_mb))/max((d.total_mb-d.free_mb)/d.total_mb) "Imbalance" ,      100*(max(d.total_mb)-min(d.total_mb))/max(d.total_mb) "Variance" ,      100*(min(d.free_mb/d.total_mb)) "MinFree" ,      100*(max(d.free_mb/d.total_mb)) "MaxFree" ,      count(*)        "DiskCnt" FROM v$asm_disk d, v$asm_diskgroup g WHERE d.group_number = g.group_number and d.group_number <> 0 and d.state = 'NORMAL' and d.mount_status = 'CACHED' GROUP BY g.group_number,, g.state, g.type, g.total_mb, g.free_mb ORDER BY 1;

PS the code comes from a very good ASM script from this very blog

This blog by Harald van Breederode explains in a much better way than I could why the mirroring in normal redundancy uses additional space .

As we are quite space challenged on the Exadata storage at the moment I have been asked several times to explain the different values of free space that are being reported – now I can just point them to this blog entry which will re-direct them to Harald’s very good explanation.

Job done





Posted in ASM, Blogroll, Oracle | Tagged: , , , , | 6 Comments »

Impact of BREXIT on Oracle price list in the UK

Posted by John Hallas on July 7, 2016

I saw this from an Oracle account manager who I deal with.

Oracle’s regional price lists are based on a constant Dollar based price list, and these are usually recalculated at the beginning of each financial quarter. Given the recent sharp decline in the £ against the $, I’m expecting an increase of roughly 15% for Oracle’s next financial quarter, beginning September.

For illustration, a single Oracle DB EE license has a current GBP list price of £31,597. Based on the constant Dollar price list and today’s exchange rates, this price should be £36,862.

I am sure the rate will stabilise soon but it is yet another indicator of the impact of allowing the masses to decide on national matters. Needless to say I voted REMAIN. I also suspect that if we had the vote again it would be 60% in favour of staying – but alas too late now




Posted in Oracle | Leave a Comment »

SQLplus command line password no longer shown on screen

Posted by John Hallas on July 6, 2016

I was showing some basic sqlplus connection tips to a new DBA starter today and my normal warning about putting the password on the line when calling sqlplus didn’t produce what I expected.

For ever and a day if you have entered a line within a server session such as when using putty or similar to this :-

sqlplus john/N0tverysafe 

and then ‘host’ to the unix shell and run a ps -ef|grep sqlplus you will see the password and so will anybody else who looks

Oracle Database 11g Enterprise Edition Release - 64bit Production

With the Partitioning, Automatic Storage Management and OLAP options


SQL> host

$ps -ef|grep sqlplus
oracle   21519 21165  0  Jun 27  ?        00:00:00 sqlplus -s /nolog
oracle   19165 18798  0  Jun 25  ?        00:00:00 sqlplus -s /nolog
oracle  8516  8514  0 18:33:42 pts/0    00:00:00 grep sqlplus
oracle   24027 23642  0  Jul  2  ?        00:00:00 sqlplus -s /nolog
oracle  8511  7910  0 18:33:28 pts/0    00:00:00 sqlplus john/N0tVERYsafe
I performed more experimentation and realised it was still unsecure at however at it had become secure
create user john identified by "N0tVERYsafe"  profile m_std_profile;
grant create session to john;
[sysopr65@teora01x][TST11204]/home/sysopr65 $sqlplus john/N0tVERYsafe

SQL*Plus: Release Production on Wed Jul 6 19:03:53 2016

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release - 64bit Production
With the Partitioning, Automatic Storage Management and OLAP options

SQL> host
[TST11204]/home/sysopr65 $ps -ef|grep john
sysopr65 28309 28306  0 19:04 pts/0    00:00:00 grep john
[TST11204]/home/sysopr65 $ps -ef|grep sqlplus
sysopr65 28256 22815  0 19:03 pts/0    00:00:00 sqlplus
[TST11204]/home/sysopr65 $exit

I will still try to instill good practise by ensuring people do not put username/password as a command string but the loophole does appear to  be fixed now.

I must check whether SQL Loader has had the security modification as well,  as the issue used to be seen when calling a controlfile embedded with a username and password. Although I must admit I tend to run controlfiles interactively from the unix account without a userid in and just use / as sysdba  when asked for a password.  That probably isn’t best practise but do as I say not as I do.

I have just finished typing this up and I do feel that I have written something like this up before. It might be worth me checking my own blog history.


Posted in Oracle, security | Leave a Comment »

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.

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

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 »