Oracle DBA – A lifelong learning experience

  • Meta

  • Categories

  • Blog Stats

    • 1,653,093 hits
  • Enter your email address to subscribe to this blog and receive notifications of new posts by email.

    Join 377 other followers

  • Advertisements

Archive for the ‘12c new features’ Category

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 and in my opinion is really helpful. Read the rest of this entry »


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

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 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/ ASMNET1LSNR_ASM -no_crs_notify -inherit
oracle 8389 1 0 Nov19 ? 00:00:12 /app/gridsoft/ LISTENER_DG -inherit
oracle 8500 1 0 Nov19 ? 00:00:51 /app/gridsoft/ LISTENER -no_crs_notify -inherit
oracle 8621 1 0 Nov19 ? 00:00:13 /app/gridsoft/ LISTENER_SCAN1 -no_crs_notify -inherit
oracle 117429 1 0 13:57 ? 00:00:00 /app/gridsoft/ 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="">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: , | 5 Comments »

Progress update on my AWR repository

Posted by John Hallas on November 20, 2014

I received an email from a team member yesterday

Have you seen this?

Interesting idea – ever thought of implementing it?

Well of course I have implemented a AWR data repository and I thought I would catch-up on where I am with it and how it is being used.

The database started out on HPUX on about 2 years ago and is now on Linux on The repository now holds a total of 139 DBIDs and we have data going back 400 days for the majority of them. The storage is over 2Tb, of which the SYSAUX tablespace contains about 95% – we are compressing some data but that is an area I need to look into further

Why 400 days you might ask – well I work in a Retail organisation and as Easter is a movable feast (pun intended) and that period allows to provide data covering Easter whenever it might fall. It is possible that we would not have data for Easter if we only kept a 365 day period and Easter is a key trading period, second only to Xmas.

I suppose the obvious question is how we have used the data and was it all worth the effort.

The repository has been used for workload forecasting, security reviews, auditing, performance reviews and validation of our existing systems.

Workload forecasting is an interesting and unexpected benefit of the repository. When I originally proposed the idea it wasn’t something that had occurred to me but it has proved very useful. The process is to review how much work a system has done a year ago, look at current usage, calculate the difference and then use that to project forward. For the system that I am discussing which looks at how long it takes us to process sales data from stores we have used a combination of metrics including db time, disk read times, both sequential and scattered to produce a magic number which is what we are working against. That forecast is now being mapped against actuals and the forecast is proving quite accurate. That has allowed us to prepare the database server to support increased volumes by changing disk, adding CPU and memory to maintain and improve against last year’s performance with increased data capture and store numbers. Read the rest of this entry »

Posted in 11g new features, 12c new features, Oracle, security | Tagged: , | 5 Comments »

Another really enjoyable UKOUG Database SIG

Posted by John Hallas on May 8, 2014

Today’s venue was the Metropole Hotel in the centre of Leeds and there was a good attendance, encouraged by a strong agenda.

After introductions, health and safety and UKOUG information it was straight into techie talk with Phil Davies from Oracle doing his normal support update (although he does share duties with Owen Ireland). Invariably I make more notes from this session than most others I hear because of the wealth of information it contains. Snippets I jotted down that are worthy of sharing are :- RDBMS is due out Jul-Dec 2014 and is likely to contain new functionality that did not get into the first 12c release – I think in-memory will be one such feature. A straw poll of members saw very little uptake of 12c in production and some but not much take-up in non-production.

Oracle Clud Control (OEM) is due very soon – Q2 2014

I was intrigued enough about MoS note 1557478.1 about a bug with transparent huge pages to make a note of it for further investigation and the same goes for 17761775.8 – Bug 17761775  ORA-600 [kclchkblkdma_3] ORA-600 [3020] or ORA-600 [kcbchg1_16] Join of temp and permanent table in RAC might lead to corruption

Next up was Phil Brown of E-DBA who ran an open forum where the audience picked from a selection of topics.

The three topics covered in about 70 minutes were

1)      Consolidation v Virtualisation

2)      The worst SQL statement in the world

3)      DBA interviews


All three allowed plenty of audience participation and competing views and I think almost everyone in the room provided input. My input regarding DBA interviews was a tip given to me several years ago which I think is very valid. Even if you are not thinking of moving on keep updating your CV so it is an accurate record of what you have been doing over the last couple of years. Read the rest of this entry »

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

Oracle Restart – going , going , gone?

Posted by John Hallas on January 13, 2014

Oracle restart is an 11GR2 feature which ensures that all services on a standalone installation start up in the correct order. As such it seems to work well. One bugbear I have with it is that it changes the order of entries in the /etc/oratab file. Personally I like my oratab to be ordered in terms of database (most used first), ASM, then agents. In that way when I logon to a box and it automatically sets the SID it picks up the first entry which is commonly the database I want to work with.

Technically it is the oraagent process, which is new in 11GR2 which updates the oratab file, but as that sits under the ohasd directory in the GI home then I consider it to be part of the Restart process. As a bye you can make your oratab file read only as long as you remember to change permissions when adding a new databases or removing one.

To the main purpose of this post. It appears that Oracle Restart is deprecated in 12c although no replacement is on the horizon.

“Oracle Restart is deprecated in Oracle Database 12c. Oracle Restart is currently restricted to manage single-instance Oracle databases and Oracle ASM instances only, and is subject to desupport in future releases. Oracle continues to provide Oracle ASM as part of the Oracle Grid Infrastructure installation for Standalone and Cluster deployments.”

That is a bit of a shock, as it is still fully documented in the Oracle 12c docs for installation etc. The MoS note Support Impact of the Deprecation Announcement of Oracle Restart with Oracle Database 12c (Doc ID 1584742.1) gives more detail and tantalisingly seems to suggest that the deprecation may be revoked.

“While typically, a feature is only deprecated once the superseding feature has been announced, on occasion this order cannot be kept. In this case, one may assume that the deprecation announcement will last – without effect – until the superseding feature has been released or the deprecation is revoked. In case of the deprecation announcement of Oracle Restart, the superseding feature has not been released yet. Therefore, Oracle will maintain Oracle Restart in its current form and will continue to fix BUGs for this product until Oracle Restart is officially de-supported, which would constitute the removal of the code.”

It would be reasonable to think that Oracle intended having a replacement ready for the initial release of 12C but it was not available in time and will come out in 12CR2

Posted in 12c new features, ASM, Oracle | Tagged: , , , | Leave a Comment » new features and a OPTIMIZER_DYNAMIC_SAMPLING change

Posted by John Hallas on December 9, 2013

As of 27th August  2013,, the final release of 11GR2 was made available – a new features document is available . I will give a quick bullet list of the new features and then discuss one very important one that is not mentioned.

  •  Oracle Data Redaction – provides a new ASO (cost £££) option to redact specified information, even at runtime, from within the database
  •  Trace File Analyzer and Collector, is a diagnostic collection utility to simplify diagnostic data collection on Oracle Clusterware, Oracle Grid Infrastructure  – looks well worth investigating
  •  RACcheck – The Oracle RAC Configuration Audit Tool – to perform regular health checks as well as pre- and post-upgrade best practices assessments. Only written for linux so needed adapting for HPUX and needs to have the root password – an issue for many DBAs
  •  Database Replay Support for Database Consolidation – run lots of scenarios- batch, online, monthly process all at the same time even though captured at different periods.
  •  Optimization for Flashback Data Archive History Tables – use the OPTIMIZE DATA clause when creating or altering a flashback data archive.

So the one that has not appeared in that list, probably because it is not a new feature as such is tan additional value for the init.ora parameter OPTIMIZER_DYNAMIC_SAMPLING. This comes into play when a table does not have any statistics and the parameter is enabled. The previous default setting of 2  was to use dynamic statistics if at least one table in the statement has no statistics and the number of blocks that the statistics would be based on would be 64 and the value could range between 2 and 10 , each value doubling  the number of blocks that will be sampled. The new value of 11  means that the optimizer will gather dynamic statistics automatically whenever the optimizer deems it necessary and based on the number of blocks it thinks appropriate.

My testing has shown a couple of anomalies between the two versions it exists on (, Read the rest of this entry »

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

UKOUG – Tech13 – day 3 review

Posted by John Hallas on December 5, 2013

Another busy day at the Manchester Central complex. An early start with Martin Nash talking about RAC connectivity issues and bugettes. Very interesting but I don’t think my brain was fully in gear at 08:30 after a big hotel breakfast.

Straight into another two-man CERN presentation on Lost Writes. The first half was  about a scenario they had seen where an index has lost some data and frankly there was no explanation for what caused it to happen. However the second part was around how you might deal with the scenario and that was very good and gave a lot of food for thought.

Jonathan Lewis then spoke for an hour about histograms and how they had developed in 12c. There were some really good points made but I suspect he lost some of the audience about half way through – including myself – I did notice a man in front of me who was doing a Sudoku throughout the presentation.

I did my AWR talk and felt it went OK but because there was no time to talk to anyone afterwards and as I did not get  much audience feedback it was quite hard to judge how well it went. The first time  I did it was at a SIG and I think the low-key, less time-pressured environment suited the talk much  more than the conference.

That was the end of the conference for me. I caught up with a  couple of people and then made my way home. All in all it was a very well run conference and the Manchester venue was better than Birmingham in my opinion.  I really cannot see the exhibition carrying on though. I did not look at one stand and I suspect there were many others who did not either. I cannot see what  the exhibitors got out of it.

I must mention the entertainment on Tuesday  evening which was a drinks reception sponsored by Enkitec and Delphix at the National Football Museum. I was very pleased to get my photograph taken holding the actual ball used in the 1966 World Cup Final. I think everyone was asked to wear gloves but it might just have been me!!


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

UKOUG Tech13 – day 2 review

Posted by John Hallas on December 4, 2013

UKOUG conference day 2 opened up with Tom Kyte talking about 5 PL/SQL things you probably didn’t know. It did have 12c in the title but I think 4 out of the 5 were pre 12C. Implicit conversions are EVIL is my main take-away and he made me aware of the power of the PL/SQL warning framework to highlight any implicit conversions and other no-nos. Another important warning was when you code a WHEN OTHERS statement but do not follow it with a RAISE statement. Overall, an interesting discussion which I enjoyed despite only having ever written about 200 lines of code in my whole life.

Larry Carpenter then talked about new Active Dataguard new features in 12C. To be honest I could not see a use for most of them at my current workplace but they were obviously delivered to meet a demand. One feature was DataGuard Fast Sync and that seemed to be available when using normal (inactive??) DG on 12c so it might be something to look at. Larry also mentioned that Oracle would be announcing g a database backup appliance in the near future.

Two roundtables followed, both of which were a bit like the curate’s egg – good in parts.

The first was around Oracle’s OEM offering. I was hoping it would be a meeting where the audience shared some good things they had discovered about OEM (around 60% of the 30 strong audience were on 12c OEM). My humble offerings might have documentation  which provides as list of all the views on the sysman tables used to manage OEM.

However it really turned out to be of benefit to the two Oracle product managers in attendance as they just soaked up a list of issues from the audience – licensing difficulties and poor documentation mostly. There was one good tip on the help capability. If you type in backup or whatever else you want help on in the field on the home page where you put the target name in that will take you to help functionality that quite often has a direct link to the right page to get started.

One comment from the Oracle people was that they had an OEM channel on YouTube with 71 videos on it. All very good but anybody running a significant estate from OEM is probably a corporate and they are likely to have YouTube blocked at work. Nothing to stop you looking at home of course but it did seem a bit of a contradiction to me.

Then a three way round table on virtualisation that involved Microsoft, VMware and Oracle. The surprising thing was that they seemed to agree that the real benefit, above cost was agility. In fact they all seemed to agree on everything, especially the fact that there was no reason why the whole enterprise could not be virtualised. I raised the point that why would  I risk putting a Tier 1 Oracle RAC database on virtual when Oracle state that in the event of a problem they might insist on getting it on physical before they could help further. I think I was seen as a Luddite with my head buried in the sand. However, given that it could take days to provision a physical RAC environment and get a copy database deployed how could I justify that to management. Microsoft and VMware said they would have their own level 2 /3 support who would help out but that is hardly the same as working with the main supplier.

Lunch was spent talking to a friend around disk latencies, , UKOUG and Apex. An enjoyable and productive 45 minutes.

In the afternoon the best talk was by Pythian’s very enthusiastic Marc Fielding on Private cloud.  A few thoughts struck me on the container database and pluggable databases. Firstly it seems to be where SQLServer has been for years. The idea that you get try one pluggable database without licensing is bizarre because most of the things you would wish to test require more than one PBD. My two overall questions would be around memory management and security. With a single SGA that is not resource managed how does one stop one PBD filling the buffer cache after running FTS or producing a fragmented library cache with multiple parsing of sql – if you flush the shared pool does it do it across all the databases in the container. My main concern would be around security, a user on one PBD would be able to see the sql-text and v$session views of another PBD and therefore see data. I have not investigated CPD or PDBs at all so I don’t have the answers but I will be looking at it shortly.

Posted in 12c new features, Oracle, UKOUG | Tagged: , | 2 Comments »

Recover table from RMAN in 12c

Posted by John Hallas on October 21, 2013

When  I read the list of 12c new features the one that interested me immediately was the ability to be able to recover a table from an RMAN backup. This seemed to be quite challenging as RMAN is an image copy of blocks and a table is normally copied using a logical Datapump export.

What Oracle have delivered is a packaged technique which recovers only the necessary tables so as to get the data dictionary and any undo/redo segments necessary to get all the data back to a  specific point in time whether that be a SCN or a timestamp.

The whole process is very neat and it creates a new database, recovers the necessary datafiles and then creates a dumpfile of the necessary table, puts the data back to wherever you want and then deletes all evidence and traces of what it has done.

I have provided a full example of such a recovery below and it is well worth looking at to see how it all works.

A few things seem worthy of note and even further  investigation. Read the rest of this entry »

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