Oracle’s ddl_lock_timeout in Version 12.2.0.1

Oracle provides a way to clone a new database home from an existing installation and, for the most part, it works well. On x86_64, however, it appears that the cloning process can miss something that creates unexpected errors when attempting to set init parameters. Let’s go through the process to see what is involved and provide a possible solution to the issue.

The most likely reason to clone an existing home is to patch it to the latest CPU so the original home can be preserved should the patch cause problems. Taking that as an example let’s clone an imaginary home for patching. The database will be named PLONK (as a reminder that this is not an actual database) and the original database home will be /u01/zapp/oracle/product/12.2.0/plonk. The April 2018 CPU will be applied so the cloned home will be /u01/app/oracle/product/12.2.0/plonk_apr2018, to make it obvious that it contains software patched with the April 2018 CPU.

The destination directory is created, and the original database home files are copied to an archive — pax, tar, cpio, gzip can all be used to create this archive. Into the new directory the archive is unpacked; the software will be there, but it won’t really be functional until the Oracle-supplied clone process is executed. To make it easier to manage, the clone process can be saved as a script (possibly named clone_home.sh):


$ORACLE_HOME/perl/bin/perl $ORACLE_HOME/clone/bin/clone.pl ORACLE_BASE="/u01/app/oracle/" ORACLE_HOME="/u01/app/oracle/product/12.2.0/plonk_apr2018" 
OSDBA_GROUP=dba OSOPER_GROUP=oper -defaultHomeName

For readability the command has been split into two lines; it must be a single line in the script or it won’t execute.

The environment must be set to ‘point’ to the home being cloned, then the script can be executed. Prodigious output is generated that won’t be provided here; the ‘problems’ begin after the home is cloned and the root.sh script has been run. Once cloned utilities like sqlplus will perform generally as expected until it becomes desirable or necessary to set a dynamic init parameter, like ddl_lock_timeout, when an unexpected ORA-00068 error is thrown, even when the parameter value is within the accepted range:


SQL> alter session set ddl_lock_timeout = 5;
ERROR:
ORA-00068: invalid value "" for parameter ddl_lock_timeout, must be
between 0 and 1000000

The clue appears to be in the value reported by Oracle; something in the linking step of the cloning process apparently failed. What that may be is not clear and Oracle Support has no document providing any explanation other than an upgrade process was not completed. Fortunately, this home will be patched in the next step, which requires the most recent release of OPatch (patch number p6880880) which simply needs to be unzipped into the cloned home. Using the current OPatch the cloned home is processed:


$ cd 
$ $ORACLE_HOME/OPatch/opatch apply
...

As if by magic the ORA-00068 errors disappear. In reality the patch process applies software updates amd re-links the software in a manner different than the Perl script used to clone the home, which executes the setup command in $ORACLE_HOME/oui/bin. It isn’t clear WHAT is different between the two processes, but no errors are thrown after patching is completed.

It’s truly an oddity; Oracle supplied tools and utilities are expected to work and produce usable results. In this case something was missed in the overall cloning process and for those cloning an existing ORACLE_HOME for another database (which isn’t necessary but isn’t wrong) because they are running Oracle on an Oracle VM built from a template and have no software distribution to use, this could be a major problem.

Take care when using this cloning process as things may not be as they seem on the surface. Many bug fixes have gone into the Perl script Oracle provides yet it appears there is at least one that hasn’t been addressed (possibly because it hasn’t been reported). This has been reported to Oracle support so hopefully an answer is forthcoming. In the interim if a home is cloned it should be patched to apply the latest security patches and to avoid the ORA-00068 surprises.

See all articles by David Fitzjarrell

David Fitzjarrell
David Fitzjarrell
David Fitzjarrell has more than 20 years of administration experience with various releases of the Oracle DBMS. He has installed the Oracle software on many platforms, including UNIX, Windows and Linux, and monitored and tuned performance in those environments. He is knowledgeable in the traditional tools for performance tuning – the Oracle Wait Interface, Statspack, event 10046 and 10053 traces, tkprof, explain plan and autotrace – and has used these to great advantage at the U.S. Postal Service, American Airlines/SABRE, ConocoPhilips and SiriusXM Radio, among others, to increase throughput and improve the quality of the production system. He has also set up scripts to regularly monitor available space and set thresholds to notify DBAs of impending space shortages before they affect the production environment. These scripts generate data which can also used to trend database growth over time, aiding in capacity planning. He has used RMAN, Streams, RAC and Data Guard in Oracle installations to ensure full recoverability and failover capabilities as well as high availability, and has configured a 'cascading' set of DR databases using the primary DR databases as the source, managing the archivelog transfers manually and montoring, through scripts, the health of these secondary DR databases. He has also used ASM, ASMM and ASSM to improve performance and manage storage and shared memory.

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles