DBA Blogs

Kindly suggest how many modules in Oracle DBA and suggest best to become best DBA associate

Tom Kyte - Thu, 2017-03-30 17:46
Dear Colleague, I am manoj sai subbarao, from india hyderabad and currently working as trainee junior data analyst in data operations in production side having on hands experience on oracle database from one year and having sound knowledge in O...
Categories: DBA Blogs

Migration from 11g to 12c

Tom Kyte - Thu, 2017-03-30 17:46
We are migrating from 11g to 12c. We need to the object coverage (Oracle dependencies), as we external applications (mainly Java applications) calling our Oracle package, function procedures. So what is best way to find calling hierarchy whi...
Categories: DBA Blogs

rename datafile with dummy characters after .dbf

Tom Kyte - Thu, 2017-03-30 17:46
<i>Hi tom, i've run into an issue out of the ordinary. while adding datafile to an existing tablespace we had fews trailing characters inbedded (by mistake) at the end of the .dbf extension. We have dbvisit replication broken due to this since...
Categories: DBA Blogs

Information Technology Jobs Posted

Bobby Durrett's DBA Blog - Thu, 2017-03-30 10:15

My company, US Foods, has posted a number of information technology jobs that are in Chicago (Rosemont)  or Phoenix (Tempe). Here is the web site:


Enter Information Technology as the Job Field to see all the posted IT jobs.


Categories: DBA Blogs

Links for 2017-03-29 [del.icio.us]

Categories: DBA Blogs

Convert the varchar vaules into number

Tom Kyte - Wed, 2017-03-29 23:26
Hi Tom, I am created the column "seqnum" as varchar2 . I need to show the values are order by ascending while selecting the values 100_1 10004_3 1002_21 123_2 10_45 2_3 10004_1 10004_5 I need to show the results as 2_3 10...
Categories: DBA Blogs

Table configuration

Tom Kyte - Wed, 2017-03-29 23:26
Hi Tom, Below is two tables where initial data will be near 47 Millon and all foreign table data will max 10000 thousand ,then per day transaction will be 1 millon. Is my below structure is ok or need some changes to support this process?Also I ...
Categories: DBA Blogs

Very slow queries after nightly statistics gathering job runs

Tom Kyte - Wed, 2017-03-29 23:26
We have an application that loads data from files and runs validation queries on the loaded data. We have observed very slow validation queries on the first file loaded after the nightly statistics job has run (and cleared all of the plans for the ta...
Categories: DBA Blogs

SYSTEM Tablespace-a second datafile

Tom Kyte - Wed, 2017-03-29 23:26
Hi, Is it possible to add a second datafile to a SYSTEM tablesapce of database, when this tablespace is almost full with few bytes free. (This tablespace is AutoExtensible with Unlimited Maxbytes and Maxblocks) Appreciate your response ASAP ...
Categories: DBA Blogs

Is PGA memory allocated from SGA ?

Tom Kyte - Wed, 2017-03-29 23:26
Hi Tom, Is PGA memory allocated from SGA? In my database SGA_MAX_SIZE is 1504M SQL> sho parameter sga_max_size NAME TYPE VALUE ------------------------------------ ----------- ----------------------...
Categories: DBA Blogs

Display all dates data of a given period name

Tom Kyte - Wed, 2017-03-29 23:26
Hi Tom, I need to pass a gl period_name eg.:-('JAN-17') to the query and get sum of debit and credit hit to each and every account. But here a problem is that I also need to display the output of any date which is not having the data. If an...
Categories: DBA Blogs

Security Monitoring and Compliance

Oracle is introducing two new security cloud services, built upon Oracle’s secure unified big data platform, the Oracle Management Cloud. Oracle Management Cloud (OMC) is a is a suite of...

We share our skills to maximize your revenue!
Categories: DBA Blogs

pl/sql code debugging - long running block

Tom Kyte - Wed, 2017-03-29 05:06
hi - our developers have a package that they are running within a begin... end block. They are telling us that this block seems to run for a very long time. this is the only thing that is running in the database and using over 90% of cpu. we ran the ...
Categories: DBA Blogs

equivalent of DBMS_XMLGEN.GETXML to generate json

Tom Kyte - Wed, 2017-03-29 05:06
We are currently generating XML data based on DBMS_XMLGEN.GETXML() to send it to client. Instead of xml , we want to send data as JSON. Is there anything similar to DBMS_XMLGEN.GETXML to generate json ?
Categories: DBA Blogs

Extents Tablas Oracle

Tom Kyte - Wed, 2017-03-29 05:06
Tom, because when I create a table with the following values ??in each of its partitions, I generate 195 extents? If the partitions have an INITIAL if the size of the partition. COMPRESS BASIC STORAGE ( INITIAL 7748954362 - 7.2 GB NE...
Categories: DBA Blogs

oracle 12.2 approximate functions

Tom Kyte - Wed, 2017-03-29 05:06
When I run the new Oracle 12c approximate square root function on a negative number, I keep getting the same answer. What's going on? <code> SQL> select approximate_sqrt(-1) from dual; APPROXIMATE_SQRT(-1) ---------------------- ...
Categories: DBA Blogs

Reclaim/reuse LOB space

Tom Kyte - Wed, 2017-03-29 05:06
Hi, We have a table with a BLOB column, general observation - about 50% to 60% of total DB space is occupied by this table. In order to reduce overall DB space, we run purge program which will selectively delete older entries. However we observe...
Categories: DBA Blogs

Apex 5.1.1 released

Flavio Casetta - Wed, 2017-03-29 02:53
Categories: DBA Blogs

How to Patch an Exadata (Part 6) – Timing

Pythian Group - Tue, 2017-03-28 15:25

Quick links to Part 1 / Part 2 / Part 3 / Part 4 / Part 5 / Part 6


6: Timing

Now that we know how to patch every component and the different options available to do so (rolling, non-rolling), which one is the best? How much time does it take?

The answer is obviously “it depends” but I will try to bring few insights so you can have a bright answer when your manager inevitably asks you “How long will that patch be? I need to negotiate the window maintenance with the business… they aren’t happy…” ;)
Here is a summary of the length of the patch application in a Rolling fashion and in a Non-Rolling fashion (as well as the downtime for each method). Please note that I put in green what I recommend.


  • Rolling : 1h30 x number of cells
  • Rolling downtime : 0 minute
  • Non-rolling : 2h (1h30 to patch a cell + 30 minutes to stop and start everything before and after the patch)
  • Non-rolling downtime : 2h

Note : Refer to my notes at the end of this page about this choice
IB Switches

  • Rolling : 45 minutes per switch then 1h30 total
  • Rolling downtime : 0 minute
  • Non-rolling : not available
  • Non-rolling downtime : not available

Note: There’s no non-rolling method for the IB Swicthes then here the choice is an easy one!
Database Servers

Note: Refer to my notes at the end of this page about this choice

Note: No green color here? To patch the grid, I recommend to go for a mix like:

  • Rebalance the services away from node 1
  • Patch the node 1
  • Verify that everything is well restarted on the node 1
  • Move all the services to the node 1 (if it is possible that only one node can handle the whole activity – but usually we patch during a quiet period)
  • Apply the patch in a non-rolling method (for the Grid it means launching the patch manually in parallel on the remaining nodes)
  • Once the grid has been patched on all the nodes, restart all the services as they were before the patch

Databases Oracle homes

  • Rolling: 20 – 30 minutes per node + ~ 20 minutes per database for the post installation steps
  • Rolling downtime:

    – Can be 0 minute if you rebalance the services before patching a node (as described here for the Grid patching, you can apply the same concept for the database patching as well) + ~ 20 minutes per database for the post installation steps.

    Please note that if you have 30 databases sharing the same ORACLE_HOME, you won’t be able to easily apply 30 post-install steps at the same time then the 30th database will suffer a bigger outage than the 1st one you restart on the patched ORACLE_HOME. This is why I strongly recommend the use of this quicker method.

    – An ~ 20 minutes downtime per database you can chose when using the quicker way !

  • Non-rolling: 20 – 30 minutes
  • Non-rolling downtime: 20 – 30 minutes for all the databases running on the patched Oracle home + ~ 20 minutes per database for the post installation steps. Note that if you have 30 databases sharing the same ORACLE_HOME, you won’t be able to apply 30 post-install steps at the same time then the 30th database will suffer a bigger outage than the 1st one you restart on the patched ORACLE_HOME.

Note: In this instance, I will definitely go for the quicker way ! : clone the Oracle home you want to patch to another one, apply the patch and move the databases one by one to the new patched Oracle home

Notes on my recommendations

Yes, I always prefer the rolling method for the Infrastructure components (Grid and Database Servers). This is because I can mitigate the outage and I’m also sure to avoid any outage created by the patch or anything preventing for example a reboot as we do not reboot those servers frequently.

Imagine if you go for a cell rolling upgrade and one cell does not reboot after the patch. You’ll have no issue here as the patch will stop automatically; everything will work as before with one cell down, no one will notice anything, you are still supported as it is supported to run different version across different servers. You can then quietly check the troubleshooting section of this blog or go to the pool while Oracle finds a solution for you.

It happened to us on production (it didn’t happen on the DEV on QA Exadatas before…), we warned the client and it took few days to Oracle to provide an action plan. All ran perfectly during a week with a cell down, we then applied the Oracle action plan during the next week-end and could properly finish the patch. The result here is that we applied the patch successfully. We had an issue that caused no outage nor performance degradation and we still fit in the maintenance window – very good job from a client and process point of view !

But if you go for a non-rolling cell patching and all your cells (or few of them) do not reboot after the patch, then you are in trouble and you will lose ten times the time you think you could have won by doing a non-rolling manner. You will most likely have a failed patch outside of the maintenance window, a Root Cause Analysis to provide to the process guys and you probably won’t patch this Exadata any more for a while as the client will be… hmmm… a bit chilly about that question in the future.

And this risk is the same for the databases servers.
I do not say that the Bundle won’t work and create a big outage, it is just all about risk mitigation. But remember: “highest level of patch = highest level of bug” :)
If you’ve reached this point, I hope that you enjoyed this Odyssey into the Exadata patching world as much as I enjoy working with it on a daily basis!

Quick links to Part 1 / Part 2 / Part 3 / Part 4 / Part 5 / Part 6

Categories: DBA Blogs

How to Patch an Exadata (Part 1) – Introduction and Prerequisites

Pythian Group - Tue, 2017-03-28 12:30

Once you have installed your new Exadata machine will come a time where you’ll be asked :

shouldn’t we patch the Exadata” ?

And the answer is “yes, definitely“.

Indeed, Oracle releases huges (~ 10 GB) “Quarterly Full Stack” patches (aka Bundles) every quarter (for example : Patch 24436624 – Quarterly Full Stack Download For Oracle Exadata (Oct 2016 –; these Bundles contain all the patches for all the components that make an Exadata. You will need (almost :)) nothing else to be able to patch your whole Exadata.
Even if it looks a tough operation at first sight, it is not that much. And this blog’s aim is to clearly describe every step to make it easier for all of us. Let’s start with a preview of this patching with the order we will be proceeding and the tools we will be using :

As it is quite a long odyssey, I will split this blog in different parts which are also a logic order to patch all the components :
0/ An advice

1/ General Information

2/ Some prerequisites it is worth doing before the maintenance

3/ The patching procedure

3.1/ Patching the cells (aka Storage servers)
3.2/ Patching the IB switches
3.3/ Patching the Database servers (aka Compute Nodes)
3.4/ Patching the Grid Infrastructure
3.5/ Patching the databases ORACLE_HOMEs

4/ The Rollback procedure

4.1/ Cell Rollback
4.2/ DB nodes Rollback
4.3/ IB Switches Rollback

5/ Troubleshooting

5.1/ Cell patching issue
5.2/ CRS does not restart issue
5.3/ A procedure to add instances to a database
5.4/ OPatch resume

6/ Timing



0/ An advice

First of all, please strongly keep in mind this advice :

Do NOT continue to the next step before a failed step is properly resolved.

Indeed, everything that needs to be redundant is redundant and it is supported to run different versions between servers. In the MOS note “Exadata Patching Overview and Patch Testing Guidelines (Doc ID 1262380.1)“, we can read that :

It is supported to run different Exadata versions between servers. For example, some storage servers may run while others run, or all storage servers may run while database servers run However, it is highly recommended that this be only a temporary configuration that exists for the purpose and duration of rolling upgrade.

Then if when patching your cells one cell is not rebooting, stop here, do not continue, do not force patch the next one. Indeed, everything will still be working fine and in a supported manner with one cell down (I did it on production, no user could notice anything), it will most likely not be the case with 2 cells down. If this kind of issue happens, have a look at the troubleshooting section of this blog and open a MOS Sev 1.


1/ General Information

Some information you need to know before starting to patch your Exadata :

  • It is better to have a basic understanding of what is an Exadata before jumping to this patch procedure
  • This procedure does not apply to an ODA (Oracle Database Appliance)
  • I will use the /patches/OCT2016_bundle_patch FS to save the Bundle in the examples of this blog
  • I use the “DB node” term here, it means “database node“, aka “Compute node“; the nodes where the Grid Infrastructure and the database are running, I will also use the db01 term for the database node number 1, usually named db01
  • I use the “cell” word aka “storage servers“, the servers that manage your storage. I will also use cel01 for the storage server number 1, usually named cel01
  • It is good to have the screen utility installed; if not, use nohup
  • Almost all the procedure will be executed as root
  • I will patch the IB Switches from the DB node 1 server
  • I will patch the cells from the DB node 1 server
  • I will patch the DB nodes from the cel01 server


1/ Some prerequisites it is worth doing before the maintenance 1.1/ Download and unzip the Bundle

Review the Exadata general note (Exadata Database Machine and Exadata Storage Server Supported Versions (Doc ID 888828.1)) to find the latest Bundle, download, unzip it; be sure that every directory is owned by oracle:dba to avoid any issue in the future :

/patches/OCT2016_bundle_patch >
oracle@myclusterdb01) ls -ltr
total 9609228
-rw-r--r-- 1 oracle oinstall 560430690 Nov 16 18:24 p24436624_121020_Linux-x86-64_10of10.zip
-rw-r--r-- 1 oracle oinstall 1030496554 Nov 16 18:26 p24436624_121020_Linux-x86-64_1of10.zip
-rw-r--r-- 1 oracle oinstall 1032681260 Nov 16 18:27 p24436624_121020_Linux-x86-64_2of10.zip
-rw-r--r-- 1 oracle oinstall 1037111138 Nov 16 18:29 p24436624_121020_Linux-x86-64_3of10.zip
-rw-r--r-- 1 oracle oinstall 1037009057 Nov 16 18:31 p24436624_121020_Linux-x86-64_4of10.zip
-rw-r--r-- 1 oracle oinstall 1037185003 Nov 16 18:33 p24436624_121020_Linux-x86-64_5of10.zip
-rw-r--r-- 1 oracle oinstall 1026218494 Nov 16 18:35 p24436624_121020_Linux-x86-64_6of10.zip
-rw-r--r-- 1 oracle oinstall 1026514887 Nov 16 18:36 p24436624_121020_Linux-x86-64_7of10.zip
-rw-r--r-- 1 oracle oinstall 1026523343 Nov 16 18:39 p24436624_121020_Linux-x86-64_8of10.zip
-rw-r--r-- 1 oracle oinstall 1025677014 Nov 16 18:41 p24436624_121020_Linux-x86-64_9of10.zip

/patches/OCT2016_bundle_patch >
oracle@myclusterdb01) for I in `ls p24436624_121020_Linux-x86-64*f10.zip`
unzip $I
Archive: p24436624_121020_Linux-x86-64_10of10.zip
 inflating: 24436624.tar.splitaj
Archive: p24436624_121020_Linux-x86-64_9of10.zip
 inflating: 24436624.tar.splitai

/patches/OCT2016_bundle_patch >
oracle@myclusterdb01) cat *.tar.* | tar -xvf -



1.2/ SSH keys

For this step, if you are not confident with the dbs_group, cell_group, etc… files,  here is how to create them as I have described it in this post (look for “dbs_group” in the post).

[root@myclusterdb01 ~]# ibhosts | sed s'/"//' | grep db | awk '{print $6}' | sort > /root/dbs_group
[root@myclusterdb01 ~]# ibhosts | sed s'/"//' | grep cel | awk '{print $6}' | sort > /root/cell_group
[root@myclusterdb01 ~]# cat /root/dbs_group ~/cell_group > /root/all_group
[root@myclusterdb01 ~]# ibswitches | awk '{print $10}' | sort > /root/ib_group
[root@myclusterdb01 ~]#

We would need few SSH keys deployed in order to ease the patches application :

  • root ssh keys deployed from the db01 server to the IB Switches (you will have to enter the root password once for each IB Switch)
[root@myclusterdb01 ~]# cat ~/ib_group
[root@myclusterdb01 ~]# dcli -g ~/ib_group -l root -k -s '-o StrictHostKeyChecking=no'
root@myclustersw-ib3's password:
root@myclustersw-ib2's password:
myclustersw-ib2: ssh key added
myclustersw-ib3: ssh key added
[root@myclusterdb01 ~]#
  • root ssh keys deployed from the cel01 server to all the database nodes (you will have to enter the root password once for each database server)
[root@myclustercel01 ~]# cat ~/dbs_group
[root@myclustercel01 ~]# dcli -g ~/dbs_group -l root -k -s '-o StrictHostKeyChecking=no'
root@myclusterdb01's password:
root@myclusterdb03's password:
root@myclusterdb04's password:
root@myclusterdb02's password:
myclusterdb01: ssh key added
myclusterdb02: ssh key added
myclusterdb03: ssh key added
myclusterdb04: ssh key added
[root@myclustercel01 ~]#
  • root ssh keys deployed from the db01 server to all the cells (you will have to enter the root password once for each cell)
[root@myclusterdb01 ~]# dcli -g ~/cell_group -l root hostname
myclustercel01: myclustercel01.mydomain.com
myclustercel02: myclustercel02.mydomain.com
myclustercel03: myclustercel03.mydomain.com
myclustercel04: myclustercel04.mydomain.com
[root@myclusterdb01 ~]# dcli -g ~/cell_group -l root -k -s '-o StrictHostKeyChecking=no'
root@myclustercel04's password:
root@myclustercel03's password:
myclustercel01: ssh key added
myclustercel06: ssh key added
[root@myclusterdb01 ~]#
1.3/ Upgrade opatch

It is highly recommended to upgrade opatch before any patching activity and this Bundle is not an exception. Please find the procedure to quickly upgrade opatch with dcli in this post.

Please note that upgrading opatch will also allow you to be ocm.rsp-free !


1.4/ Run the prechecks

It is very important to run those prechecks and take a good care of the outputs. They have to be 100% successful to ensure a smooth application of the patches.

  • Cell patching prechecks (launch them from the DB Node 1 as you will patch them from here)
[root@myclusterdb01 ~]# cd /patches/OCT2016_bundle_patch/24436624/Infrastructure/
[root@myclusterdb01 ~]# ./patchmgr -cells ~/cell_group -patch_check_prereq -rolling


  • DB Nodes prechecks (launch them from the cel01 server as you will patch them from here)

As we will use the cell node 1 server to patch the databases servers, we first need to copy patchmgr and the ISO file to this server

[root@myclusterdb01 ~]#  scp /patches/OCT2016_bundle_patch/24436624/Infrastructure/SoftwareMaintenanceTools/DBServerPatch/5.161014/p21634633_121233_Linux-x86-64.zip root@myclustercel01:/tmp/.                    # This is patchmgr
[root@myclusterdb01 ~]#  scp /patches/OCT2016_bundle_patch/24436624/Infrastructure/ root@myclustercel01:/tmp/.                               # This is the ISO file, do NOT unzip it
[root@myclusterdb01 ~]#  ssh root@myclustercel01
[root@myclustercel01 ~]#  cd /tmp
[root@myclustercel01 ~]#  nohup unzip p21634633_121233_Linux-x86-64.zip &
[root@myclustercel01 ~]# cd /tmp/dbserver_patch_5.5.161014
[root@goblxdex02cel01 dbserver_patch_5.161014]# ./patchmgr -dbnodes ~/dbs_group -precheck -iso_repo /tmp/p24669306_121233_Linux-x86-64.zip -target_version
[root@goblxdex02cel01 dbserver_patch_5.161014]#

Note : if you have some NFS mounted, you will have some error messages, you can ignore them at this stage, we will umount the NFS before patching the DB nodes

  • IB Switches prechecks (launch them from the DB Node 1 as you will patch them from here)
[root@myclusterdb01]# cd /patches/OCT2016_bundle_patch/24436624/Infrastructure/
[root@myclusterdb01]# patch_12.]# ./patchmgr -ibswitches ~/ib_group -upgrade -ibswitch_precheck


  • Grid Infrastructure prechecks
[root@myclusterdb01]# . oraenv <<< +ASM1
[root@myclusterdb01]# $ORACLE_HOME/OPatch/opatchauto apply /patches/OCT2016_bundle_patch/24436624/Database/ -oh /u01/app/ -analyze

Notes :

  • You will most likely see some warnings here, check the logfiles and they will probably be due to some patches that will be rolled back as they will not be useful any more.


[root@myclusterdb01]# $ORACLE_HOME/OPatch/opatch prereq CheckSystemSpace -phBaseFile /tmp/patch_list_gihome.txt

With the following /tmp/patch_list_gihome.txt file (check the README as the patch numbers will change with the versions)

[root@myclusterdb01]#cat /tmp/patch_list_gihome.txt


  • Database patch prechecks
[oracle@myclusterdb01]$ . oraenv <<< A_DATABASE_WITH_THE_ORACLE_HOME_YOU_WANT_TO_PATCH
[oracle@myclusterdb01]$ $ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir /patches/OCT2016_bundle_patch/24436624/Database/ $ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir /patches/OCT2016_bundle_patch/24436624/Database/ $ORACLE_HOME/OPatch/opatch prereq CheckSystemSpace -phBaseFile /tmp/patch_list_dbhome.txt

The file /tmp/patch_list_dbhome.txt containing (check the README, the patch numbers will change depending on the versions) :



  • OJVM prechecks
[oracle@myclusterdb01]$ cd /patches/OCT2016_bundle_patch/24436624/Database/
[oracle@myclusterdb01]$ $ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -ph ./

- Do a lsinventory -all_nodes before patching and save the output somewhere

[oracle@myclusterdb01]$ $ORACLE_HOME/OPatch/opatch lsinventory -all_nodes


  • Check disk_repair_time and set it to 24h

Oracle recommends to set this parameter to 8h. As we had issues in the past with a very long cell patching, we now use to set this parameter to 24h as Oracle has recommended us.
Please note that this prerequisite is only needed for a rolling patch application.

SQL> select dg.name as diskgroup, a.name as attribute, a.value from v$asm_diskgroup dg, v$asm_attribute a where dg.group_number=a.group_number and (a.name like '%repair_time' or a.name = 'compatible.asm');

-------------------- ---------------------------------------- ----------------------------------------
DATA disk_repair_time 3.6h
DATA compatible.asm
DBFS_DG disk_repair_time 3.6h
DBFS_DG compatible.asm
RECO_ disk_repair_time 3.6h
RECO compatible.asm

6 rows selected.

SQL> connect / as sysasm
SQL> ALTER DISKGROUP DATA SET ATTRIBUTE 'disk_repair_time' = '24h' ;

Diskgroup altered.

SQL> ALTER DISKGROUP DBFS_DG SET ATTRIBUTE 'disk_repair_time' = '24h' ;

Diskgroup altered.

SQL> ALTER DISKGROUP RECO SET ATTRIBUTE 'disk_repair_time' = '24h' ;

Diskgroup altered.


If one of this precheck points a problem, resolve it before heading to the next steps.
Now that everything is downloaded, unzipped, updated, we can safely jump to the patching procedure in part 2 !

Quick links to Part 1 / Part 2 / Part 3 / Part 4 / Part 5 / Part 6

Categories: DBA Blogs


Subscribe to Oracle FAQ aggregator - DBA Blogs