dcsimg
Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
SQL Scripts & Samples
Tips
» Database Forum
» Slideshows
» Sitemap
Free Newsletters:



News Via RSS Feed


Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

Oracle

Posted August 27, 2018

WEBINAR:
On-Demand

Virtual Developer Workshop: Containerized Development with Docker


Oracle's ddl_lock_timeout in Version 12.2.0.1

By David Fitzjarrell

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



Oracle Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
Oracle Forum
Topic By Replies Updated
Oracle Data Mining: Classification jan.hasller 0 July 5th, 07:19 AM
Find duplicates - Unique IDs Lava 5 July 2nd, 08:30 AM
no matching unique or primary key rcanter 1 April 25th, 12:32 PM
Update values of one table based on condition of values in other table using Trigger Gladiator 3 February 29th, 06:01 PM