Oakies Blog Aggregator

Franck Pachot's picture

18c Read Only Oracle Home

Capture18c000This is the big new feature of Oracle 18c about database software installation. Something that was needed for decades for the ease of software deployment. Piet de Visser raised this to Oracle a long time ago, and we were talking about that recently when discussing this new excitement to deploy software in Docker containers. Docker containers are by definition immutable images. You need a Read Only Oracle Home, all the immutable files (configuration, logs, database) being in an external volume. Then, to upgrade the software, you just open this volume with an image of the new database version.


In 12.2 you may have seen a ‘roohctl’ script in ORACLE_HOME/bin. The help explains that ‘rooh’ stands for Read-Only Oracle Home:

[oracle@VM122 ~]$ roohctl -help
Usage: roohctl [] [ ]
Following are the possible flags:
Following are the possible commands:
-enable Enable Read-only Oracle Home
-disable Disable Read-only Oracle Home

Note that in 18c the help does not show ‘-disable’ even if it is accepted….
So in 12cR2 you were able to run ‘roohctl -enable’ but the only thing it did was changing the Yes/No flag in orabasetab:

cat $ORACLE_HOME/install/orabasetab
#orabasetab file is used to track Oracle Home associated with Oracle Base

Oracle 18

Here is an Oracle 18 that I re-installed (as an Oracle Home Clone) with the following:

runInstaller -clone ORACLE_HOME=/u01/app/oracle/product/181 ORACLE_HOME_NAME=O181 ORACLE_BASE=/u00/app/oracle

My idea is to be able to easily differentiate the different paths (ORACLE_HOME under /u01 and ORACLE_BASE under /u00)

The $ORACLE_HOME/install/orabasetab records the ORACLE_HOME, ORACLE_BASE and ORACLE_HOME_NAME:

[oracle@VM181 18c]$ cat $ORACLE_HOME/install/orabasetab
#orabasetab file is used to track Oracle Home associated with Oracle Base

ORACLE_HOME: This may seem useless because this file is under ORACLE_HOME, so if you read it you are supposed to know the ORACLE_HOME. However, you may find it from different paths (symbolic links, /../.) and this is a good way to normalize it.

ORACLE_BASE: This will be used to externalize the mutable files outside of the ORACLE_HOME

ORACLE_HOME_NAME: is the name of Oracle Home that you provide when installing and you can find in the Oracle Inventory.

The last field is ‘N’ when the mutable files are under ORACLE_HOME and ‘Y’ when they are externalized to have an immutable Read Only Oracle Home.

We are not supposed to use this file directly. It is modified by runInstaller and roohctl. And it is read by orabasehome and orabaseconfig

orabasehome and orabaseconfig

We have two new location name derived from the orabasetab content.

One is the ‘Oracle Base Config’ which is mostly there to find the configuration files (.ora, .dat) in the /dbs subdirectory. With Read Only Oracle Home, this is set to the ORACLE_BASE:

[oracle@VM181 18c]$ orabaseconfig

Most of the files in /dbs have the ORACLE_SID in their name, which is unique in the host, and this is why they can all go into the same directory. However, I would prefer a subdirectory per database. When you move a database from one system to another, it is easier to move a directory. You can do per-file symbolic links but be sure to maintain them as they may be re-created as files.

The other is the ‘Oracle Base Home’ which is mostly there for the /network subdirectory (with the SQL*Net configuration files, logs and trace) and the /assistant (DBCA templates) and /install ones. With Read Only Oracle Home, this goes to a /homes subdirectory of ORACLE_BASE

[oracle@VM181 18c]$ orabasehome

As you see, there is an additional subdirectory with the name of the Oracle Home. In my opinion, it is not a good idea to put sqlnet.ora, tnsnames.ora and listener.ora here. It is better to have one common TNS_ADMIN. However, because the default was one directory per Oracle Home, the Read Only Oracle Home feature had to keep this possibility. In 12.2 an ORACLE_HOME/env.ora was introduced to set TNS_ADMIN in a consistent way.

With Read Only Oracle Home enabled, I strace-ed a ‘startup’ to show which files are read:

[oracle@VM181 18c]$ ORACLE_SID=CDB18 strace -e trace=file -f sqlplus / as sysdba <<&1 | grep /u00
open("/u00/app/oracle/homes/O181/network/admin/oraaccess.xml", O_RDONLY) = -1 ENOENT (No such file or directory)
access("/u00/app/oracle/homes/O181/network/admin/oraaccess.xml", F_OK) = -1 ENOENT (No such file or directory)
open("/u00/app/oracle/homes/O181/network/admin/oraaccess.xml", O_RDONLY) = -1 ENOENT (No such file or directory)
access("/u00/app/oracle/homes/O181/network/admin/sqlnet.ora", F_OK) = -1 ENOENT (No such file or directory)
access("/u00/app/oracle/homes/O181/network/admin/sqlnet.ora", F_OK) = -1 ENOENT (No such file or directory)
access("/u00/app/oracle/homes/O181/network/admin/intchg.ora", F_OK) = -1 ENOENT (No such file or directory)
access("/u00/app/oracle/homes/O181/network/admin/tnsnav.ora", F_OK) = -1 ENOENT (No such file or directory)
open("/u00/app/oracle/dbs/cm_CDB18.dat", O_RDONLY|O_SYNC) = -1 ENOENT (No such file or directory)
[pid 15339] access("/u00/app/oracle/homes/O181/network/admin/sqlnet.ora", F_OK) = -1 ENOENT (No such file or directory)
[pid 15339] stat("/u00/app/oracle/dbs/spfileCDB18.ora", 0x7ffe6a5785b8) = -1 ENOENT (No such file or directory)
[pid 15339] open("/u00/app/oracle/dbs", O_RDONLY) = 8
[pid 15339] stat("/u00/app/oracle/dbs/spfileCDB18.ora", 0x7ffe6a578010) = -1 ENOENT (No such file or directory)
[pid 15339] stat("/u00/app/oracle/homes/O181/dbs/spfile.ora", 0x7ffe6a5785b8) = -1 ENOENT (No such file or directory)
[pid 15339] open("/u00/app/oracle/homes/O181/dbs", O_RDONLY) = 8
[pid 15339] stat("/u00/app/oracle/homes/O181/dbs/spfile.ora", 0x7ffe6a578010) = -1 ENOENT (No such file or directory)
[pid 15339] access("/u00/app/oracle/dbs/initCDB18.ora", F_OK) = -1 ENOENT (No such file or directory)
[pid 15339] open("/u00/app/oracle/dbs/initCDB18.ora", O_RDONLY) = -1 ENOENT (No such file or directory)
LRM-00109: could not open parameter file '/u00/app/oracle/dbs/initCDB18.ora'

The files were not there as I’ve not created any database here. The goal is to show that there is no attempt to read any configuration file under ORACLE_HOME.

You can also see that DBCA will search for templates in this new directory:


I mentioned network and assistant subdirectories. But it concerns all directories where the instance can write files:

[oracle@VM181 18c]$ du $ORACLE_BASE/homes
4 /u01/app/oracle/homes/O181/assistants/dbca/templates
8 /u01/app/oracle/homes/O181/assistants/dbca
12 /u01/app/oracle/homes/O181/assistants
4 /u01/app/oracle/homes/O181/network/trace
4 /u01/app/oracle/homes/O181/network/admin
4 /u01/app/oracle/homes/O181/network/log
16 /u01/app/oracle/homes/O181/network
4 /u01/app/oracle/homes/O181/dbs
4 /u01/app/oracle/homes/O181/install
64 /u01/app/oracle/homes/O181/rdbms/log
72 /u01/app/oracle/homes/O181/rdbms/audit
140 /u01/app/oracle/homes/O181/rdbms
180 /u01/app/oracle/homes/O181
184 /u01/app/oracle/homes

You may wonder why we see a /dbs subdirectory here as the instance configuration files are in the common /u01/app/oracle/dbs. The /dbs is also the current working directory for oracle processes. And this one will be set to ORACLE_BASE/homes/oracle_home_name/dbs.

We can also see /rdbms/log here. I opened a bug 2 years ago about SBTIO.LOG not going to the right place under ADR_HOME, but going to ORACLE_HOME/rdbms/log (Bug 23559013 USER_DUMP_DEST VALUE NOT IGNORED EVEN THOUGH DIAGNOSTIC_DEST IS SET). I’ve no idea about the status of the bug, but at least this will not go to Oracle Home anymore. Even if you don’t really have the need to have a Read Only Oracle Home, this feature is a good way to ensure that it will not grow and fill the filesystem.

Enable Read Only Oracle Home

You enable this feature with ‘roohctl -enable’ after software installation and before any creation of databases or listeners:

[oracle@VM181 18c]$ roohctl -enable
Enabling Read-Only Oracle home.
Update orabasetab file to enable Read-Only Oracle home.
Orabasetab file has been updated successfully.
Create bootstrap directories for Read-Only Oracle home.
Bootstrap directories have been created successfully.
Bootstrap files have been processed successfully.
Read-Only Oracle home has been enabled successfully.
Check the log file /u01/app/oracle/cfgtoollogs/roohctl/roohctl-180217PM111551.log.

If the utility tool finds an existing database or listener related to this Oracle Home, it will return this kind of error:

Cannot enable Read-Only Oracle home in a configured Oracle home.
The Oracle Home is configured with databases 'CDB18'.
The Oracle Home is configured with listeners 'LISTENER'.

There is an undocumented ‘-force’ parameter to add to ‘roohctl -enable’ which can proceed anyway, but it will not move the configuration files.

I have not tested all possibilities because the General Availability of 18c is currently limited to Exadata and Oracle Public Cloud. But it seems that this roohctl will work the same on Windows (with /database instead of /dbs and with registry settings instead of orabasetab) and with Grid Infrastructure (there’s a -nodeList argument).

I mentioned above that the ORACLE_HOME/install goes to $(orabasehome)/install. I don’t know which files go there when ROOH is enabled. The orabasetab remains under ORACLE_HOME, of course. And some logs, such as re-running root.sh, still go to ORACLE_HOME/install:

[oracle@VM181 ~]$ sudo $ORACLE_HOME/root.sh
Check /u01/app/oracle/product/181/install/root_VM181_2018-02-18_19-06-23-833474515.log for the output of root script

This looks strange, but remember that the idea of a Read Only Oracle Home is to ship it after all changes are done. If you have something to change (patch, re-link, …) that will go to another Oracle Home. Maybe cloned from the other, then made Read Only after the changes.


Do you use the question mark as a shortcut to ORACLE_HOME? This does not change and remains the ORACLE_HOME:

[oracle@VM181 ~]$ sqlplus / as sysdba
SQL*Plus: Release Production on Sun Feb 18 20:26:33 2018
Copyright (c) 1982, 2017, Oracle. All rights reserved.
Connected to an idle instance.
SQL> start ?
SP2-0310: unable to open file "/u01/app/oracle/product/181.sql"
SQL> exit

This is ok as I mostly use it to read files from the software distribution (such as ?/rdbms/admin/awrrpt)

If you use it in database configuration files, then be careful. Here I have enabled ROOH and defined a pfile mentioning the spfile with the ‘?’ shortcut

[oracle@VM181 ~]$ orabaseconfig
[oracle@VM181 ~]$ cat $(orabaseconfig)/dbs/init$ORACLE_SID.ora

However, the ‘?’ is resolved to ORACLE_HOME and not Oracle Base Config:

[oracle@VM181 ~]$ strace -f sqlplus / as sysdba <<&1 | grep xxx
[pid 1898] read(10, "spfile=?/dbs/xxx\n", 4096) = 17
[pid 1898] stat("/u01/app/oracle/product/181/dbs/xxx", 0x7ffc5ac1c758) = -1 ENOENT (No such file or directory)
[pid 1898] stat("/u01/app/oracle/product/181/dbs/xxx", 0x7ffc5ac1c1b0) = -1 ENOENT (No such file or directory)
ORA-01565: error in identifying file '?/dbs/xxx'

So what?

Having a read-only Oracle Home, or at least be sure that you have no files written into it, is a good idea. Easier to manage space. Easier to deploy by cloning Oracle Home. Good practice to separate big software directory from small configuration files. And to have the current working directory outside of that. Having configuration files at the same place as the software is always a bad idea (and reminds me the .ini files in C:\WIN directory a long time ago). So, even if it is not enabled by default, Read Only Oracle Home is the way to go.

I think the risks are very limited once tested, as it is just changing the directories and any problem can be worked around with symbolic links on directories. However, this may change some habits and scripts. Not finding the right configuration file in a stressful situation may be annoying.

So, don’t wait, and even in 12c, you can change your habits and replace all references to ${ORACLE_HOME}/dbs by $(orabaseconfig)/dbs and other ${ORACLE_HOME} to $(orabasehome). In 12c they will go to the same ORACLE_HOME. And they you will be ready to enable ROOH in 18c.


Cet article 18c Read Only Oracle Home est apparu en premier sur Blog dbi services.

morten's picture

Update to RANDOM_NINJA. Enables you to create even more random production like test data using pl/sql.

Had a chance to finish the data generators that I had on my list for release 1.4 of
. New data domain additions include random science data, random game data, investment data and medical data.

Read below for a full list of the current functionality. More will be added for release 1.5.

morten's picture

Update to RANDOM_NINJA. Enables you to create even more random production like test data using pl/sql.

Had a chance to finish the data generators that I had on my list for release 1.4 of
. New data domain additions include random science data, random game data, investment data and medical data.

Read below for a full list of the current functionality. More will be added for release 1.5.

connor_mc_d's picture

Oracle Database 18c

Yup…it’s arrived!

New name obviously, because we’ve jumped to our new naming model to align with the calendar year as opposed to version number.  You might be thinking “So what?” but it’s a significant change in the way we getting software to customer.  Mike Dietrich blogged about this at length here so I won’t repeat what has been said, but in TL;DR form:

More frequent releases, with smaller amounts of change per release

In this way, the approach lets us focus more of solidifying existing features, and being able to quickly respond to bugs that arise.

So 18c is more an incremental release on 12.2 (in fact, internally it was referred to as “” for most of it’s build cycle) focussed on stability and hardening of existing features.

Don’t worry, we still managed to pack some cool new things in there – which you can read about in the New Features guide.

I’ll be doing some blog posts and videos on the 18c content soon, but here’s something to whet your appetite Smile

dbakevlar's picture

SQL Server Operations Studio

Since I reformatted my Surface Pro 4 so I could have my Docker running again, I also needed to recover my local SQL Server instance I like to have available on my PC.

This was a perfect time to get everything up and running, but instead of my standard way of doing this, use SQL Server Operations Studio, (SSOS).

The Lesson

Most SQL Server DBAs have a GUI took to assist in managing their database environment and unlike Oracle DBAs, (at least before multi-tenant and the cloud) it was common for SQL Server DBAs, (on average) to have considerably more databases to manage on average than Oracle DBAs have.  Some of this is due to the architectural differences, such as a single SQL Server having many user databases vs. Oracle having one database instance with many schemas inside a database.  If you visualize this difference, you realize that the maintenance and backups of each user database would be higher than the single database instance for the Oracle DBA.  These types of differences required the SQL Server DBA to rely on automation and tools to ease their day-to-day demands.

From Microsoft, the SQL Server Management Studio, (aka SSMS and who’s ancestor was called Enterprise Manager) is provided with the installation or as an add on in recent versions.  Numerous vendors have provided incredible products to monitor, manage and support 100’s to 1000’s of databases, including Idera, who I proudly serve as a 2018 ACE.

There are still presentations offered at SQL Saturdays offering tips and tricks with SSMS, even though it’s a 32-bit product, which tells you how important a management tool is to a DBAs sanity, (remember, I made my name on the Oracle side with their Enterprise Manager, so Oracle DBAs may be hesitant to admit it, but they depend on one almost as often!)  With the fact that it is a 32-bit tool and the importance of the tool, Microsoft launched SQL Server Ops Studio.  It reminds me of the love child between SSMS and the original Oracle SQL Developer, which isn’t a bad thing.  Consider what Oracle SQL Developer is today, so that’s where I’m going here.

Not Your Father’s SSMS

The first thing you notice is that you are now working with a modern application that is 64-bit and built for extensibility.  No, it doesn’t have the legacy features that many DBAs will require to get them to transition over immediately, but you can build metrics and widgets to do much of it right now.  It also supports today’s hybrid-  both Azure and On-premises environments, which is something SSMS just isn’t prepared for.

I’ve already reached out to the SSOS group on Twitter to find out how I can offer my feedback to help.  I was part of the Customer Advisory Board for Enterprise Manager before I joined Oracle on the EM team, so I’m aware that this experience could be exceptionally helpful.  It is crucial to have customer feedback to build a product to fulfill what an cloud management and infrastructure tool features.

So, if you haven’t downloaded it and tested it out, consider it.  I’ll be blogging a few posts here and there to offer some insight on my own experiences with the product going forward.

Tags:  , , ,






Copyright © DBA Kevlar [SQL Server Operations Studio], All Right Reserved. 2018.

The post SQL Server Operations Studio appeared first on DBA Kevlar.

mwidlake's picture

Will I Be The Next President Of The UK Oracle User Group?

I’ve decided to put myself forward to be President Elect of the UK Oracle User Group (UKOUG). The position of President Elect is, in effect, President-in-waiting. You shadow the current president before taking over the role when their term comes to an end. In this case, that will be in a year.

https://mwidlake.files.wordpress.com/2018/02/screenhunter_298-feb-16-11-... 136w, https://mwidlake.files.wordpress.com/2018/02/screenhunter_298-feb-16-11-... 280w" sizes="(max-width: 272px) 100vw, 272px" />

I think this is a very sensible manner in which to introduce a new person into the role of President. The UKOUG is one of the largest Oracle user groups in the world. It is in effect a small company with permanent staff and a large number of interested parties, the members. About 1000 companies have at least one membership with the UKOUG, some hold several (as each membership comes with conference passes). The position of President comes with 3 main duties:

  • Representing all members of the users group – end users, partners, sponsors. There are two other positions on the board of Member Advocate, so the president is one of three (out of a total of 6) representing the membership.
  • Being the ambassador for the UKOUG. This is partly being the “friendly public face” of the organisation but, as President, you represent the UKOUG to other user groups, Oracle Corporation and the press.
  • To ensure that the UKOUG meets it’s requirements as a company and has the correct governance in place. For the UKOUG a lot of the governance is about ensuring the board is selected or appointed correctly, legal requirements are met, and that the user group is run in an open and fair manner.

Why would I want to take this on? It is not a paid position, it is voluntary.

(I should maybe be a little clearer here on pay – voted positions on the board, i.e. member advocate and president, are not salaried. But expenses are paid and there is provision for some payment for specific project work, or if the demands of a role exceeds a number of hours in a given month. But you would be unable to live on it, no matter how frugal you are!)

Well, as many of you know, I’ve been an active volunteer for the UKOUG for a long time, it’s actually over 10 years. I present at nearly every annual conference, at a couple of the Special Interest Groups (SIGs) each year and I’ve chaired or deputy chaired SIGs since 2009. I don’t just do the “standing up and being noticed” stuff, I help out with the organisational work. I was in charge of the Database content at Tech14 & Tech15 and all the content of Tech16. I’ve sat on strategy committees, reviewed submissions, analysed speaker scores… I’m currently editor of the UKOUG magazine, Oracle Scene. I know some people think of me as “that guy from the UKOUG”. Maybe being President would be less work!

https://mwidlake.files.wordpress.com/2018/02/screenhunter_299-feb-16-11-... 150w" sizes="(max-width: 278px) 100vw, 278px" />

When the UKOUG announced that the position of President Elect was open, it seemed natural to try and take that final step up the Volunteer ladder to become a member of the board.

When it comes down to it, I love being in the Oracle community. I’ve made so many friends across the globe through not just the UKOUG but by going to the conferences & meetings of other national Oracle User Groups. I have learnt so much from user groups, not just from lectures but directly from the people I meet. The majority of people who get involved in user groups are not only intelligent and wanting to learn, they are also willing to share and teach.

Another part of my wanting to be the President (eventually) is that I don’t think the UKOUG is perfect. The organisation does evolve and change as the technology and market shifts. But I’d like to try and shake things up a bit and slightly alter where it’s focus currently is. I won’t say any more on that for now.

There are also big changes for some Oracle customer, namely Cloud, Chatbots, AI and the fact that hardware is shifting. Solid State storage and Oracle’s own in-memory tech is making some things possible that were impossible with the old physical storage and row-based processing. But soon we will have storage that is an order of magnitude faster than current SSD, almost as fast as main memory.

Oddly enough, one problem I see a lot is that there is too much focus on some of those new areas. Many people are still running systems where cloud and SSD are not part of their world. Yes, they would probably all like to move forward but if the systems they have can’t move on, they still need to get the most out of them now. User groups are not just for those chasing the latest-greatest, they are just as much for those who need help keeping the wheels on. I think the user group needs to reach slightly back before we can help them forward.

Many of you won’t be able to vote for me as only members of the UKOUG can vote. But if you can, I’d appreciate your vote. And I will need those votes.

https://mwidlake.files.wordpress.com/2018/02/screenhunter_295-feb-15-14-... 478w, https://mwidlake.files.wordpress.com/2018/02/screenhunter_295-feb-15-14-... 120w" sizes="(max-width: 239px) 100vw, 239px" />

There is one slight oddity. I am the only person standing for the position of President Elect (the position of Member Advocate is also open and being voted for at the moment, for which there are three candidates). However, there is still a vote, I will not take the position uncontested. The vote is a yes/no/abstain one, so you can either support my bid to be the President Elect or voice your opposition. There are issues with yes/no votes but over all the UKOUG board felt that as the user group is run on democratic principles, the members should be able to have their say over if they feel I am suitable to eventually become their President or not. If the number of votes are low, it edges things in the favour of “no” so I still need to campaign.

(If you can vote, you can do so Here)

As for the contest for the position of Member Advocate, I’ve voted for Neil Chandler. I know Neil well and he is just as passionate about the UKOUG as I am and I know he will work hard to keep it moving forward and improving.

Let’s see what happens come the conclusion of voting in March.

dbakevlar's picture

Docker and Windows 10 1709 Patch, The End

So if you read my last post on my challenges with Docker containers running on Windows 10 after the 1709 patch, I thought I was close to solving it once I was granted the admin password to disable and uninstall Sophos Endpoint.

Once I uninstalled Sophos, I noted I ended up with a different line number error.  The question was, has the problem shifted from Sophos to a new issue or is it more complex than just the one application?  We’d already seen that it was complicated, including the Hyper-V with the 1709 patch that was part of the problem.

As I researched the network issue farther, noticed that, although I’d uninstalled Docker and Sophos, there were considerable files leftover and registry entries that had roots throughout the system.  I also became aware of how many changes were cross referencing and sharing the same DLLs.

I’m an expert of scouring Oracle from a Windows server, but there’s nothing like a reminder of the ease of management with Linux and power of an “rm -rf” command vs. the challenges of shared files and registry entries.

In the end, it became apparent that I could be shooting myself in the foot trying to find the trip wire that caused the complex problem that I was experiencing, (along with lacking logging from Docker) as well as the time it was taking to get me back up and running my containers.

At 4pm last night, I decided to backup my files to my external SSD and then reformat my Microsoft Surface Pro 4.  All my licensing is stored for my PC in my account when I simply run a erase and reload the same machine.  I chose to erase all of my data and apps.

Once I reloaded, I chose the following:

  1.  Locked the Windows updates before the 1709 patch and I’m not letting any patches be applied until a new patch comes out that fixes this.  Trust me, after all this research, I know where to look… <br />
    <div class=»
connor_mc_d's picture

AskTOM Office Hours for DBA’s

We had the first AskTOM Office Hours Q&A for Database Administrators yesterday.  Thanks to everyone that showed up, and thanks for the questions.

If you missed it, you can catch a replay here

pete's picture

Pete Finnigan Presented About Oracle Database Vault and Oracle Security

I have not added much here on my site for some time due to a serious health issue taking a lot of my time with a close family member. So please bear with me if you email or contact me....[Read More]

Posted by Pete On 15/02/18 At 08:44 PM

Franck Pachot's picture


Do you have complex connection strings with DESCRIPTION_LIST, DESCRIPTION, ADDRESS_LIST, ADDRESS and a nice combination of FAILOVER and LOAD_BALANCE? You probably checked the documentation, telling you that FAILOVER=YES is the default at all levels, but LOAD_BALANCE=YES is the default only for DESCRIPTION_LIST. But when disaster recovery and availability is concerned, the documentation is not sufficient. I want to test it. And here is how I do it.

I don’t want to test it with the real configuration and stop the different instances. And I don’t need to. My way to test an address list is to define a tnsnames.ora with the connection string, such as the following:


I used localhost because I know it’s there and I don’t want to wait for the TCP timeout. But I use fake ports, which do not exist. So finally, a connection will never be established but I will be able to see all that are tried. I check them with strace on the connect() system call, with the following script:

for i in {1..10}
TNS_ADMIN=/tmp strace -T -e trace=connect sqlplus -s -L sys/oracle@NET_SERVICE_NAME as sysdba <<< "" 2>&1 | awk '
/sa_family=AF_INET, sin_port=htons/{
gsub(/[()]/," ") ; printf "%s ",$5
print ""
done | sort | uniq

So, I used meaningful numbers for my fake ports: 101 and 102 for the addresses in the first description of the description list, and 201 and 202 for the address list in the second description. The awk script shows the sequence that was tried. And, because of the random round robin, I run them in a loop several times to see all patterns, aggregated by sort|uniq

So here is the result from the connection string above using the defaults for load balancing and failover:

101 102 201 202
201 202 101 102

The sequence within the address list is always in order (101,102 and 201,202) because LOAD_BALANCE=NO is the default there. But I have two combinations for the descriptions because LOAD_BALANCE=YES is the default in DESCRIPTION_LIST. Finally, all adresses are tried because FAILOVER=YES is the default at all levels.


If I define LOAD_BALANCE at all levels, such as:


The result shows that all combinations can be tried in any order:

101 102 201 202
101 102 202 201
102 101 201 202
102 101 202 201
201 202 101 102
201 202 102 101
202 201 101 102
202 201 102 101

By running it in a large loop you will confirm that any address will be tried at most once.


Now, If I set FAILOVER=NO within the first description:


the first attempt can be 101 or 102 (because of LOAD_BALANCING) but only one will be tried in this address list, because of no failover. Then, the second description is attempted (because FAILOVER=YES at description list level) and with all addresses there (because of LOAD_BALANCING=YES). The result of all possible combinations is:

101 201 202
102 201 202
102 202 201

So here it is. You can test any complex connection description to check what will be the possible connections and in which order they will be tried. From this, you can infer what will happen with a real configuration: the wait for TCP timeout for addresses tested on hosts that are not up, and the load balancing given be the different possible combinations.


Cet article (DESCRIPTION_LIST=(DESCRIPTION=(ADDRESS_LIST=(FAILOVER=YES)(LOAD_BALANCE=NO) est apparu en premier sur Blog dbi services.