Pythian has openings for MySQL and MS SQL Server DBAs in each of our offices in Ottawa, Canada; Boston, USA; Dubai, UAE; and Hyderabad, India. If you are a MySQL and/or SQL Server DBA and would like to evaluate this opportunity, please send us your résumé with an introductory paragraph to hr@pythian.com.
« Log Buffer #104: a Carnival of the Vanities for DBAs
MyISAM Statistics Gathering »

Oracle Apps 11.5.9: Post-Clone Fails if DBA_JOBS Not Turned Off

By Balraj Chahal — July 4th, 2008 at 12:28 pm
Posted in Oracle E-Business Suite
Tags: • • •

We recently had an issue with a client while cloning a huge database. The result was that we had to restore the whole database as the post-clone corrupted the existing database. Pain! It took another fourteen hours to restore.

This may help you to troubleshoot the issue.

/sbox/oracle/product/920/appsutil/clone/bin$ perl adcfgclone.pl dbTier

Errors in the Alert.log after Clone failure

Completed: ALTER DATABASE ENABLE PUBLIC THREAD 3Shutting down instance: further logons disabled
Shutting down instance (normal)License high water mark = 5Mon Apr 14 14:49:00 2008

Errors in file /sbox/oracle/product/920/admin/Prod/bdump/sbox1_j001_2453612.trc:

ORA-12012: error on auto execute of job 16303
ORA-12008: error in materialized view refresh path
ORA-28112: failed to execute policy function
ORA-06512: at “SYS.DBMS_SNAPSHOT”, line 820
ORA-06512: at “SYS.DBMS_SNAPSHOT”, line 877
ORA-06512: at “SYS.DBMS_IREFRESH”, line 683
ORA-06512: at “SYS.DBMS_REFRESH”, line 195ORA-06512: at line 1~

Explanation

The post-clone restarts the database a few times during the process. If DB jobs are scheduled, they don’t allow the database to shutdown, which corrupts the database and hence the clone fails.

Solution

After the database is copied over and you are ready to run the post-clone, perform the following:

  1. Start the database.
  2. Spool the results of the following query to an SQL file. 
    SQL> select 'exec dbms_job.next_date( ' || job || ' , sysdate+7);' from user_jobs;
  3. Execute the spooled SQL file.

This will delay all the scheduled dba jobs which otherwise prevent DB shutdown during the post-clone steps.

These icons link to social bookmarking sites where readers can share and discover new web pages.
  • Google
  • del.icio.us
  • Facebook
  • bodytext
  • Technorati
  • TwitThis
  • Reddit

You can follow any responses to this post through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

One Response to “Oracle Apps 11.5.9: Post-Clone Fails if DBA_JOBS Not Turned Off”

  1. mady Says:
    September 12th, 2008 at 2:38 pm

    Balraj ,

    Thank q so much for your blog , it is quite informative abt delaying the scheduled jobs for a while during the cloning process , but i have small confusion here if we are cloning with rapidclone , we run adcgclone.pl as a part of post-clone on the target system but not on the source system (i.e existing database) , then how come post-clone will corrupt the source system (existing DB), and also you have mentioned during the post-clone DB will restart few times , which DB it is, is it source or target, couls you be more precise and elaborate the whole process taking the two DB’s as example (like PROD and TEST).

    Regards
    mady

Leave a Reply

Filling out the following captcha not only allows us to cut down on automated blogspam but also helps digitize books. Please feel free to send comments on this approach directly to Paul at vallee@pythian.com.

NOTE: After submitting your comment, verify that it is added to the blog. New comments will be marked as "waiting for moderation" (we only moderate for spam). If the level of spam is as low as we hope, we will bypass this step.