Oracle's ddl_lock_timeout in Version

Monday Aug 27th 2018 by David Fitzjarrell

With some ports of Oracle 12.2, attempting to set ddl_lock_timeout at the session level can throw an ORA-00068.  Read on to see one way this can be fixed.

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;
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

Mobile Site | Full Site