Saturday, October 27, 2007



1.  When applying patches, where should the patch files reside for adpatch

     to find them?

    The patches can be copied anywhere as long as the environment is set correctly when

    executing adpatch. You may usually find that these are in a directory under $APPL_TOP.

2. Which drivers should be run first?  If they are run in the wrong order can

    they be run again?

    Always read the readme.txt.  In general for Release 11, patches can include three

    different drivers: "c", "d" and "g". The "C" drivers (copy) are file system drivers, these

    are used to update the file system with the new versions of certain files.  The "D"

    drivers are database drivers and they run SQL scripts that update database tables and

    objects.  The "G" (generate) drivers are also file system drivers, they generate forms

    and reports.  These drivers are run in alphabetical order c, d, then g.  If they are run

   out of order, rerun them in the correct order.

3. Where (on which tier) do you run the three different patch drivers?

   The "c" and "g" drivers are run on all tiers.  The "d" driver is only run on the database

   tier. In Applications Release 11, there can be five different tiers: concurrent processing

   server, forms server, administration server, web server, and database server.  When

   adpatch is run the answers to the configuration questions will determine the actions

   adpatch takes. The configuration questions are the same questions asked by adadmin

   and adaimgr. 

  3a.  Do you currently have files used for installing or upgrading the database installed

         in this APPL_TOP?

  3b.  Do you currently have Java and HTML files for Self-Service Applications installed

         in this APPL_TOP?

  3c.  Do you currently have Oracle Applications forms files installed in this APPL_TOP?

  3d.  Do you currently have concurrent program files installed in this APPL_TOP?

4. What is the basic process that adpatch runs with the 'c' driver?

    The "c" (copy) driver updates the file system.  Below is a portion of a patch and an

    explanation of the activities  within the patch.

               copy            fnd    admin/sql   fnddold.sql  110.4
               copy            fnd    forms/US    FNDADDTC.fmb 110.4
               libout          fnd    lib               afdict.o
               copy            fnd    lib               afdict.o110.1
               libin             fnd    lib              afdict.o
               forcecopy    fnd    media        FNDIEROR.gif
               link              fnd    bin             FNDCLOAD

4a. copy   fnd  forms/US  FNDADDTC.fmb  110.4

ADPATCH performs version checks allowing it to not overlay a higher version object

or sql files.  If the version on the file system is the same or higher it skips the copy. 

In the above example, notice the version to the right of the filename.  It uses this

version to do the compare.  The "fnd" next to the copy tells ADPATCH what applications

top to use and "forms" points adpatch to the proper directory. With the above statement

ADPATCH will copy the file FNDADDTC.fmb (from patch directory) to $FND_TOP/forms. 

The copy actually moves the existing FNDADDTC.fmb file to FNDADDTC.fmbO and then

physically copies the new file version to FNDADDTC.fmb.  The file FNDADDTC.fmbO will

remain on the file system.

4b.  libout   fnd   lib   afdict.o

Libout is a utility that removes files from the library archive.  The archive file resides in

the lib directory under the TOP directory of the application.  The name of the file is

lib<application>.a.  So the archive file for FND is located in $FND_TOP/lib and is named

libfnd.a. The actual command that libout executes is:

     ar -d libfnd.a afdict.o

This removes the object file (afdict.o) from the library file (libfnd.a).

4c. libin  fnd  lib  afdict.o

Libin is the utility that adds an object file to the library file.  The actual command that

libin executes is:

     ar -rv libfnd.a afdict.o

The object file (afdict.o) is appended to the bottom of the library file. Libin is executed

even if the copy did NOT take place because the information was already removed with

the libout command.

4d.  forcecopy    fnd  media  FNDIEROR.gif

Similar to copy, but no version checking is done. Forcecopy copies the target file

irregardless of the presence or version of the file currently on the target system.

4e.  link  fnd  bin  FNDCLOAD

The link process is similar to a compile.  It links new object files together with the other

objects files to create the executable.  The command that the above link is using:

      adrelink force=y "fnd FNDCLOAD"

The "fnd" is the top that the executable is being relinked in.  The executable FNDCLOAD

has to be in the proper case that it would normally appear in the $FND_TOP/bin directory.

5. What does adpatch actually do with a 'd' driver?

    The "d" (database) drivers alter the tables in the database. These patches can create

    tables, add indexes and alter the APPS schema. The only way to back-out a 'd' driver is

    with a database backup. Even if the executables are restored (on the file system), they

    will not work because information in the database has changed.

6. What does adpatch actually do with a 'g' driver?

     The "g" (generate) drivers generate forms, .plls (PL/SQL libraries) reports and menus.

     The following is an excerpt from a "g" driver:

                 genform      fnd    forms/US         FNDWFNOT.fmb
                 genrep        fnd    reports            FNDCPWKM.rdf
                 genfpll        fnd    resource          JL.pll
                 genmenu    fnd    resource/US    FNDMENU.mmb 

6a.  genform uses f60gen to generate forms.

       Syntax is:
                 f60gen userid=APPS/APPS module=<form name>.fmb
                 output_file=<full path of schema top>/forms/US/<form name>.fmx
                 module_type=form batch=no compile_all=special

6b.  genrep uses rwcon60 to generate reports

       Syntax is:
     rwcon60 userid=APPS/APPS
     source=/<full path of schema top>/reports/<report name>.rdf
     dest=/<full path of schema top>/reports/<report name>.rdf stype=rdffile
     dtype=rdffile logfile=<report name>.log overwrite=yes batch=yes dunit=character

6c.  genfpll uses f60gen to generate .plls (forms libraries)

       Syntax is:
          f60gen userid= APPS/APPS module=<library name>.pll module_type=library    
          output_file=/<full path of schema top> /resource/<library name>.pll

6d.  genmenu uses FNDMDGEN to generate menus
       Syntax is:
                 FNDMDGEN APPS/APPS 0 Y <language codename>
                 DB_TO_RUNTIME  <application shortname> <message filename>.msb

7.  What happens when a patch is applied where the version is lower than the

      current version?

     Higher versions contained within the patch are copied, the lower versions

     are ignored.

8.  Which patches can be backed out should there be a problem?

     It is always recommended that you perform a cold backup of a system prior

     to applying any patch.  It is also recommended that you apply patches to a

     test system first.  However, for those cases where you have not done these

     two steps; you should know that database drivers 'd' CANNOT be backed out

     without a system backup. If the "c" or "g" drivers need to be backed out,

     reverse the steps from the "c" driver and relink, then reverse the steps from

    the "g" driver. This will require you to manually review the driver files and

    create commands that will do the opposite of the process.  There is no utility

    that you can use to reverse the patch. For the section of the above patch, you

    need to do the following:

        libout  fnd    lib    afdict.o
        copy    fnd    lib    afdict.o    110
        libin     fnd    lib    afdict.o

   Dearchive afdict.o from libfnd.a, then copy rename afdict.oO to afdict.o  and

   then archive afdict.o into the library file.

8a. libout    fnd   lib  afdict.o

      ar -dv  libfnd.a  afdict.o

8b. copy   fnd   lib  afdict.o  110

      To reverse this:

      cp afdict.o0  afdict.o

8c. libin   fnd    lib  afdict.o

      ar -rv libfnd.a afdict.o

      This will place the old object file back into the library file.

8d.  link  fnd  bin  FNDCLOAD

       Now the executable needs to be relinked.

       adrelink force=y ranlib=y "fnd FNDCLOAD

8e.  copy fnd  forms/US  FNDADDTC.fmb 110.4

       This was the set that copied the file from the patch directory to  $FND_TOP/forms. 

       To reverse it:

        cp  FNDADDTC.fmbO  FNDADDTC.fmb

       Adpatch command to generate the form:

       genform   fnd  forms   FNDADDTC.fmb

       To reverse it:

        f45gen userid=APPS/APPS module= FNDADDTC.fmb
        output_file=$FND_TOP/forms/US/ FNDADDTC.fmx
        module_type=form batch=no compile_all=special

9a.  You notice that while running ADPATCH you encounter an error and

       you are being prompted if you want to proceed.  What are the types

       of errors and what should you do?

       There are two types of errors: Fatal in which ADPATCH halts and Non-Fatal

       errors where users is prompted if they want to proceed. If the user answer

       Yes, adpatch skips the operation it was trying to complete and notes in the

       $APPL_TOP/install/log/adpatch.log file that it was completed. This could be

       the cause of problems later on.

9b.  Should you proceed? 

        This is a very hard question. On some patches the answer is NO on others

        it does not matter. This has to be determined on a case by case basis.

10.  You answered "Yes" to proceed with the patch when it encountered a

       Non-Fatal error. How can you get ADPATCH to execute that operation again?

        The only way to get back to the same point is to restart the patch from the beginning.

11.  What log files are generated by ADPATCH and how are they named?

        The log file created by ADPATCH is named by the user when they start ADPATCH. 

       So the log filename is user defined.  If the person applying patches does not give

       it a unique name each time, the log information is appended to the end of the

       existing file.  That file is located in $APPL_TOP/admin/log and is named adpatch.log.

       This means that the ADPATCH log file could have references to many patches. Some

       patches may spawn workers and log file will be generated for each worker. These

       log files are named in the following way: 

       adwork01.log, adwork02.log, etc... and are located in $APPL_TOP/admin/log.

12. How is the environment set and what kind of errors would ADPATCH

      generate if there was an issue with the environment?

      ADPATCH should be run as APPLMGR, the owner of the APPL_TOP.  The login

      should execute the environment ile so ADPATCH should have this set. The errors

      you might encounter with an environment problem would be:

      ADPATCH will not know how to copy and will not be able to  relink. 

      In that case, you might  get messages like:

      Don't know how to make.

13. What happens if adpatch fails on a PL/SQL script?

      If adpatch fails on a PL/SQL script you can try running it from the command line. 

      If it runs, then you can  continue as if successful. You can get the syntax and

      parameters for the script from the ADPATCH.log file.  This will help you know who

      the user was who ran the  script and where the script is located.

14.  What happens if the patch fails?  You are being prompted to restart

       the patch when you start ADPATCH.

       If the patch fails you can try to restart the patch.  The patch will start from

       where it failed.  The other option will be to start over.  There are two questions

      ADPATCH prompts for when restating a patch.

       Do you want to continue with the previous session?
       Are you sure you don't want to continue with the previous install?

       The default is to restart the patch, so if you want to decline this option they

       will have to answer  "NO" and then "YES".

15.  You have decided to start all over and resubmit ADPATCH from the

       beginning, but you get the error that FND_INSTALL_PROCESSES already

      exits. How is FND_INSTALL_PROCESSES related and why does ADPATCH

       have a problem with it?

       FND_INSTALL_PROCESSES is a table that is created by RAPID INSTALL, ADPATCH

      and ADADMIN  utilities. Normally the above utilities will remove this table after

      they complete. This table might exist if you are running any of the above processes. 

      So, if you are also applying  another application patch at the same time, the table

      might exist for the other patch. Dropping the FND_INSTALL_PROCESSES table should

      only be done if you are NOT in the middle of running ADADMIN or applying another

      patch. Consider this scenario:  You are running the install and you get instructions to

      obtain a patch (usually this would be an FND patch) and you attempt to use ADPATCH

      to install the patch. If you  were to drop  FND_INSTALL_PROCESSES at this point, you

      would have to start your install over.  However, you could backup FND_INSTALL_PROCESSES

      and the restart files.  Then run adpatch to install the patch.  Then restore

      FND_INSTALL_PROCESSES and the restart files, and continue the installation.

      If you want to drop the table, you will need to login as the owner of the FND tables.

      Normally this is APPLSYS. Then type the following:

                drop table fnd_install_processes;

16.  ADPATCH has failed and it indicated there was an error with a worker. 

      What steps can be taken to investigate this problem?

        When ADPATCH is using workers, it (ADPATCH) keeps track of what jobs the

        workers are doing with a table in the database called FND_INSTALL_PROCESSES.

        Look at the bottom of the log file associated with the worker that failed

       (i.e. adwork01.log or adwork02.log  or adwork03.log)

        There is a utility ADCTRL that can be used to update FND_INSTALL_PROCESSES. 

        This utility will allow you to reset a flag in FND_INSTALL_PROCESSES.  This will allow

        ADPATCH to restart the failed step.  ADCTRL has a menu with 7 options.

       ( 8th option is Hidden and it is to skip a worker )

        Choose option one on the ADCTRL menu to review the worker status. Other

        workers will wait for the failed  worker.

        Choose option two on the menu to fix the worker.  Place in the worker number

        and hit return.

        Check the status of worker again using option one.  The status should have

        changed to "fixed restart."

        Try restarting the patch again.  (This assumes you have fixed the problem that

        caused the worker to fail, increased rollback segments, max extents, etc...)

17.  ADPATCH continues to fail and you have successfully completed the sql

       statement from the command line. Is there any way to direct the patch

       to skip this sql and continue?

        Insert the following at the very top of the script that failed:

             exit ;

        Go into ADCTRL and set the worker that failed to fixed/restart (as  above).

        Then restart ADPATCH.

18.  Your patch is erroring with an Undefined Symbol.  What does this mean and 

       what can you do to resolve this issue?

        The error generally looks like this:

        Can't relink APVOID.ABI

        An undefined symbol means ADPATCH cannot find a reference to a symbol in

        the archive file (ex. $FND_TOP/lib/libfnd.a) which was archived from the .o file

        contained in the patch library directory.  The undefined  symbols that it cannot

        fine are listed following the error message. You can try the nm command searching

        the library for something that references that symbol:

        nm *.o |grep I afmall   (afmall in this case is the undefined symbol)

        The object files are binary.  Use the following command to put the libfnd.a file

        in a text version that you can read:

        nm libfnd.a > tmp

        Use VI to edit the .tmp file and search for the undefined symbol.  When  you view

        the file you will notice that each .o is listed and there are a large number of objects.

        A symbol will only be listed once in each object file (.o). The .o file is listed at the

        top and the columns are listed below.

        The name column will contain the names of the symbols.  The Shndx column will

        contain the text UNDEF or it will contain a number.  The number is referencing

        what the symbol means. Use the find command in VI to locate the symbol where

        a number appears in there Shndx column next to the symbol that is erring.  You

        shouldn't find one that is what is causing the undefined symbol. You will need to

        find the object file that defines this symbol.  If this was working and you were

        applying a patch start  with the patch.  Also path problems can cause this type of  error.

19.  You want to know if you can reapply a patch.  Will this cause problems?

        You can almost always reapply patches without a problem.  If a patch can not be

        reapplied, there will be a warning in the README.  However, problems may exist

        if you are trying to reapply a patch where you have already applied a later version

        of the same patch.  One example is applying FND Patchset of lower version,then FND

       Patchset of higher verion and then trying to reapply FND of Patchset lower version.

20.  How to apply a patch when adpatch is already running

      Applying an 11i patch when adpatch is already running

How to Apply an 11i Patch When adpatch is Already Running

1.  Using the adctrl utility, shutdown the workers.
a. adctrl
b. Select option 3 "Tell worker to shutdown/quit"
2. Backup the FND_INSTALL_PROCESSES table which is owned by the APPLSYS schema
a. sqlplus applsys/<password>
b. create table fnd_Install_processes_back
as select * from fnd_Install_processes;
c. The 2 tables should have the same number of records.
select count(*) from fnd_Install_processes_back;
select count(*) from fnd_Install_processes;
3. Backup the AD_DEFERRED_JOBS table.
a. sqlplus applsys/<password>
b. create table AD_DEFERRED_JOBS_back
as select * from AD_DEFERRED_JOBS;
c. The 2 tables should have the same number of records.
select count(*) from AD_DEFERRED_JOBS_back;
select count(*) from AD_DEFERRED_JOBS;
4. Backup the .rf9 files located in $APPL_TOP/admin/<SID>/restart directory.
At this point, the adpatch session should have ended and the cursor should
be back at the Unix prompt.
a. cd $APPL_TOP/admin/<SID>
b. mv restart restart_back
c. mkdir restart
5. Drop the FND_INSTALL_PROCESSES table and the AD_DEFERRED_JOBS table.
a. sqlplus applsys/<password>
c. drop table AD_DEFERRED_JOBS;
6. Apply the new patch.
7. Restore the .rf9 files located in $APPL_TOP/admin/<SID>/restart_back
a. cd $APPL_TOP/admin/<SID>
b. mv restart restart_<patchnumber>
c. mv restart_back restart
8. Restore the FND_INSTALL_PROCESSES table which is owned by the APPLSYS
a. sqlplus applsys/<password>
b. create table fnd_Install_processes
as select * from fnd_Install_processes_back;
c. The 2 tables should have the same number of records.
select count(*) from fnd_Install_processes;
select count(*) from fnd_Install_processes_back;
9. Restore the AD_DEFERRED_JOBS table.
a. sqlplus applsys/<password>
b. create table AD_DEFERRED_JOBS
as select * from AD_DEFERRED_JOBS_back;
c. The 2 tables should have the same number of records.
select count(*) from AD_DEFERRED_JOBS_back;
select count(*) from AD_DEFERRED_JOBS;
10. Re-create synonyms
a. sqlplus apps/apps
11. Start adpatch, it will resume where it stopped previously.

Thursday, October 25, 2007

Oracle Database 11g Certification : New Features for Administrators 1Z0-050



Oracle University has announced the availability of the

Oracle Database 11g: New Features for Administrators (1Z0-050) in the beta version.

This exam is an excellent opportunity to upgrade your certification and skills to Oracle Database 11g.

Oracle Database 11g extends Oracle's unique ability to deliver the benefits of grid computing with

increased self-management and automation. Oracle Database 11g Administrator Certification combines

training, experience, and testing to ensure that you have a strong foundation and expertise in the

industry's most advanced database management system.

By participating in beta exams, you have the opportunity to provide the Oracle Certification program

with feedback about exam content, which is integral to the process of exam development. We depend

on the contributions of experienced professionals and developers as we continually improve exam

content and maintain the value of Oracle certifications.

Why take a Beta Exam?

Significantly reduced cost! $50 USD (savings of $75 USD)

If you pass the beta exam, your record will indicate a passing score and you will receive credit toward

certification for this exam. Therefore, you will not need to retake the final released version of the exam.

Beta exam is a great way to identify your strengths and weaknesses in the exam content being tested.


Pre-Registration begins:
September 29, 2007

You may register for all Oracle certification exams with Thomson Prometric.

Oracle Collaboration Suite 10.1.2 Installation on Red Hat Linux AS/EL 3.0



1. Verify the hardware requirements on your machine :


2. Verify the software requirements on your machine :


3. Verify the kernel parameter settings:


4. To improve the performance or the software on Linux systems, you must increase the

    following shell limits for the oracle user, depending on the user's default shell.

     To increase the shell limits, complete the following steps:



To begin an installation of OCS, perform the following steps:

1. At the prompt, invoke the Installation wizard by executing the following command:

>$ /stage/DVD_1/runInstaller

2. The Welcome page appears. Select Advanced Installation. Click Next to continue.

3. On the Specify Inventory directory and credentials window, you can set the full path of the inventory

    directory and the operating system group name. For this example, all of the default values are used.

    Click Next.

4. During the installation process, a script needs to be run in order to continue

5. Open a new window and log in as root. Navigate to the oraInventory directory and execute the script by typing the commands listed below.

>$ cd /u01/app/oracle/oraInventory

6. Once the script has finished, return to the Installation Wizard and click Continue.

7. In the Specify File Locations window, set both the Source and Destination file locations of the products

    that you want to install. Click Next.

8. The Select a Product to Install window gives you three installation options. Select Oracle Collaboration

    Suite Infrastructure and Applications Click Next.

9. The Oracle Universal Installer performs product-specific prerequisite checks. In order to proceed, click Next.

10. You can select the language in which Oracle Collaboration Suite will run. To select all the available languages,

      click the >> button.

11. Click Next to continue.

12. Because a single box installation was selected in the previous window, the Oracle Collaboration Suite

      Infrastructure and Applications Methodology window appears. From this window, you can verify the

      order and the location where OCS will be installed. Click Next.

13. On the Select Components to Configure window, all of the OCS components are selected by default.

       You can deselect any if you would like to configure them at a later time. Click Next.

14. The Specify Namespace in Internet Directory window allows you to specify a location, or namespace,

       in the Oracle Internet Directory. By default, the Suggested Namespace is selected. Click Next.

15. On the Specify Database Configuration Options window, input the Global Database Name and the SID.

      Under the Database Character Set region, Oracle Collaboration Suite defaults to the Database Character

      set used for this installation. For this example, all of the default values are accepted. Click Next.

16. Within the Specify Database Schema Passwords window, you have the choice of setting different

      passwords for each of the pre-loaded schemas (this is the default value), or to use the same password

      for all of the accounts. This example uses the Use the same password for all of the above accounts

      option. Input and confirm the necessary password. Note that the password has to be alphanumeric.

      Click Next.

17. On the Specify Application Passwords window, you have the choice of using different passwords

      (the default) or using the same password for the administrative accounts associated with OCS. For

      this example, the Use the same password for all of the above accounts is selected. Input and confirm

      the necessary password. Note that the password has to be alphanumberic. Click Next.

18. The next window, Specify E-Mail Domain Information, allows you to specify the E-mail domain served

       by this E-Mail server installation. Input your domain in the E-mail Domain field. Click Next.

19. There are a number of ports that need to be configured for OCS. On the Specify Port Configuration

      Options window, you can either chose to have the ports configured automatically (the default value)

      or manually. For this example, Automatic Port Selection is kept as the default. Click Next.

20. The Summary window appears. From this window you can view the Global Settings, Product Languages,

      Space Requirements, and New Installations. If you would like to change any of the configurations, you

      can select the Back button. Otherwise, click Install to begin the installation process.

21. During the installation process, a script needs to be run as root in order to continue:

22. Open a new window and log in as root. Navigate to the infra directory and execute the

       script by typing the commands listed below:

>$ cd /u01/app/oracle/product/10.1.2/ocs_1/infra

23. Once the script has finished, return back to the Installation Wizard and click OK.

24. The next window, Configuration Assistants, configures and starts the components  selected earlier.

25. The Database Configuration Assistant starts, and will close when configuration completes.

26. Next begins the installation of the Mid-Tier applications.

27. During this installation process, a script needs to be run in order to continue.

28. Open a new window and log in as root. Navigate to the apps directory and execute the  

      script by typing the commands listed below.

>$ cd /u01/app/oracle/product/10.1.2/ocs_1/apps

29. Once the script has finished, return back to the Installation Wizard and click OK.

30. Next window, Configuration Assistants, configures, starts the components that were selected earlier.

31. When the installation of OCS has completed successfully, the End of Installation appears.

       Click Exit to quit the Installation wizard.

32. Click Yes to confirm.


Post Installation Steps


Start the Oracle Mail Listener

The Oracle Mail listener, listener_es, listens for requests to send e-mail. Because the Email listener

listens on the privileged port 25, a privileged user, such as root, must start the listener. To start this

listener, perform the following steps:

1. Login as root to your machine hosting your OCS installation and set the ORACLE_HOME variable:

>$ export ORACLE_HOME=/u01/app/oracle/product/10.1.2/ocs_1/apps

2. Find out the ID and group ID of the oracle user who installed OCS:

>$ id oracle

uid=502(oracle) gid=502(oinstall) groups=502(oinstall)

3. Start the E-Mail listener using the following command:

>$ $ORACLE_HOME/bin/tnslsnr listener_es -user 502 -group 502 &

4. Verify the listener is running:

>$ ps -ef | grep listener_es
oracle 1765 1 Oct01 ? 00:00:00 /u01/app/oracle/product/10.1.1
/ocs_1/apps/bin/tnslsnr listener_es -user 502 -group502


Provision orclguest for Content Services and Real-Time Collaboration

To provision orclguest for Content Services and Real-Time Collaboration, do the following steps:

1.Open a browser window and access the Oracle Internet Directory Self Service Console.

   Click the Login link.

2.Login as orcladmin. Click Login.

3.Click the Directory tab.

4.Enter orclguest in the Search box, and then click Go.

5.Select orclguest, and then click Edit.

6.Click Next.

7.Note that Content Services and Real-Time Collaboration (RTC) are available to be provisioned. Click Next.

8.Click Next.

9.Scroll down the page and verify that Content Services and RTC are listed in the Applications to

   be provisioned section.

10.Click Finish.

11.Note that the alert that orclguest account was successfully modified.

Tuesday, October 23, 2007

How to determine if you are on the latest Autoconfig related patches

This applies to Applications Version: 11.5.1 to 11.5.10


Please run below queries in SQLPLUS connected as APPS user:

SET head off Lines 120 pages 100
col n_patch format A65
col bug_number format A10
col patch_name format A10
spool LACF_ptch_level.txt
select ' LACF ' FROM dual;
select bug_number, decode(bug_number,
'2488995' ,'11i.ADX.A'
,'2682177' ,'11i.ADX.B'
,'2682863' ,'11i.TXK-C'
,'2757379' ,'11i.TXK-D'
,'2902755' ,'11i.TXK-E'
,'3002409' ,'11i.ADX.C'
,'3104607' ,'11i.TXK-F'
,'3219567' ,'11i.TXK-B'
,'3239694' ,'11i.TXK-G'
,'3271975' ,'11i.ADX.E'
,'3416234' ,'11i.TXK-H'
,'3453499' ,'11i.ADX.F'
,'3594604' ,'11i.TXK-I'
,'3817226' ,'11i.ADX.E.1'
,'3950067' ,'11i.TXK-J'
,'4104924' ,'11i.TXK-K'
,'4367673' ,'11i.TXK-J.1'
,'4717668' ,'11i.TXK-M'
,'5035661' ,'11i.One_off'
,'5107107' ,'11i.TXK-N ROLLUP PATCH (AUG 2'
,'5225940' ,'11i.POST ADX.F'
,'5456078' ,'11i.One_off_a'
,'5473858' ,'11i.ATG_PF.H RUP5'
,'5478710' ,'11i.TXK-O'
,'5759055' ,'11i.TXK-P'
,'5985992' ,'11i.TXK-Q'
) n_patch, last_update_date
FROM ad_bugs
WHERE bug_number IN ( '2488995' , '2682177' , '2682863' , '2757379' , '2902755' , '3002409' , '3104607' ,

'3219567' , '3239694' , '3271975' , '3416234' , '3453499' , '3594604' , '3817226' , '3950067' , '4104924' ,

'4367673' , '4717668' , '5035661' , '5107107' , '5225940' , '5456078' , '5473858' , '5478710' , '5759055' , '5985992' );



set serveroutput on size 100000
TYPE p_patch_array_type is varray(100) of varchar2(10);
TYPE a_abstract_array_type is varray(100) of varchar2(60);
p_patchlist p_patch_array_type;
a_abstract a_abstract_array_type;
p_appltop_name VARCHAR2(50);
p_patch_status VARCHAR2(15);
p_appl_top_id NUMBER;
CURSOR alist_cursor IS
SELECT appl_top_id, name
FROM ad_appl_tops;
procedure println(msg in varchar2)
open alist_cursor;
p_patchlist := p_patch_array_type( '2488995'
a_abstract := a_abstract_array_type( '11i.ADX.A'
,'11i.POST ADX.F'
,'11i.ATG_PF.H RUP5'
FETCH alist_cursor INTO p_appl_top_id, p_appltop_name;
EXIT WHEN alist_cursor%NOTFOUND;
IF p_appltop_name NOT IN ('GLOBAL','*PRESEEDED*')
println(p_appltop_name || ':');
for i in 1..p_patchlist.count
p_patch_status := ad_patch.is_patch_applied('11i', p_appl_top_id, p_patchlist(i));
println('..Patch ' || a_abstract(i)||' '||p_patchlist(i)||' was '||p_patch_status);
close alist_cursor;

Maintenance Mode - A New Feature in 11.5.10

Maintenance Mode is a new mode of operation introduced with Release 11.5.10, in which the

Oracle Applications system is made accessible only for patching activities not allowing the users

to login to any responsibility. This provides optimal performance for AutoPatch sessions, and

minimizes downtime needed.

1. Scheduling System Downtime

Administrators can schedule 'System Downtime' using Oracle Applications Manager (OAM):

Site Map --> Maintenance --> Manage Downtime Schedules

When the System has been scheduled for 'Downtime', Apache should be re-started on Restricted Mode

by using the Script (  By doing this, users attempting to log on to Oracle Applications will

be automatically redirected to a System Downtime URL showing a message similar to the following one:

Scheduled Downtime Details
Start Time       : 17:30:00 12/11/2004
Expected Up Time : 09:00:00 12/12/2004
For Updates      :
The system is currently undergoing a scheduled maintenance.
<Current Status>

This message can be customized with any text message.  If No Downtime has been specified, and the users

try to access the Applications, the following message might also appear:

! Warning
The system has not been taken off maintenance mode completely.
Please contact your System Administrator.

2. Advantages

There are several practical points relating to the use of Maintenance Mode:

  • You can toggle Maintenance Mode between Enabled and Disabled using the new Change Maintenance

           Mode menu in AD Administration, or the equivalent function in Oracle Applications Manager.

  • Although you can run AutoPatch with Maintenance Mode disabled, there will be a significant

          degradation in performance.

  • There is a separate logon page for Restricted Mode access while the system is in Maintenance Mode.

For more Information on Restricted Mode Access

3. Enabling and Disabling Maintenance Mode

Maintenance mode is Enabled or Disabled from adadmin.

When you Enable or Disable 'Maintenance Mode', adadmin will execute the script:

$AD_TOP/patch/115/sql/adsetmmd.sql sending the parameter 'ENABLE' or 'DISABLE' :

sqlplus <APPS_Schema name>/<APPS Password>@adsetmmd.sql ENABLE | DISABLE

    ENABLE  -   Enable Maintenance Mode .
    DISABLE -   Disable Maintenance Mode.

When adsetmmd.sql runs, it sets the Profile Option 'Applications Maintenance Mode'

(APPS_MAINTENANCE_MODE) to 'MAINT' to Enable 'Maintenance Mode' and to 'NORMAL' to Disable it.

4.  Determining if Maintenance Mode is Running

A quick way to verify if the Environment is on Maintenance Mode or not, is by checking the value of this

Profile Option as follows:

sqlplus apps/apps

SQL> select fnd_profile.value('APPS_MAINTENANCE_MODE') from dual;

If the query returns 'MAINT', then Maintenance Mode has been Enabled and the Users will not be able to

Login.  If the query returns 'NORMAL' then Maintenance Mode has been De-Activated and the Users will be

able to use the application.

Note:  Maintenance Mode is only needed for AutoPatch Sessions. Other AD utilities do not require

Maintenance Mode to be enabled. Maintenance Mode must be 'Enabled' before running AutoPatch

and 'Disabled' after the patch application was completed.

When Maintenance Mode is disabled, you can still run Autopatch by using options=hotpatch on the

command line, if necessary.  However, doing so can cause a significant degradation of performance.

5. Error Messages

Always remember to Disable Maintenance Mode after any Patch application. If Maintenance Mode is not

Disabled, the Application will not allow the users to use the system.  Take note that Apache must be

re-started in normal mode after disabling 'Maintenance Mode' by using the Script (or

As explained before, when 'Maintenance Mode' is enabled, a Downtime should be Scheduled from OAM and

Apache should be started on Restricted Mode by using the Script (

If a 'DownTime' is not Scheduled from OAM and Apache has not been re-started on Restricted Mode,

the Application will allow the users to Login, but it might experience unusual behaviors afterwards

depending on the Patch Level.

Here are some examples of the possible error messages:

  • When clicking on a Responsibility from the PHP

There are no applications available for this responsibility.  Please click on a different responsibility

link to display the list of available applications.


You are not authorized to access the function Applications Home Page.  Please contact your System


  • When trying to access to the Application via CGI directly (not supported):

There are no valid navigations for this responsibility
Cause: The menu compilation has failed.
Cause: There is not valid menu defined for this responsibility.
Cause: There are no navigable forms associated with this responsibility.
Action: Contact your system administrator. Ensure that a valid menu,
containing navigable forms, is defined for the responsibility.
Ensure that the menu is correctly compiled.

Note:  In some cases, the behavior is slightly different.  Instead of showing the above messages, the

Application might not show any Responsibilities listed for the user at all.

6. Step by Step Process

1.  Schedule the 'System Downtime' from OAM

OAM: Site Map --> Maintenance --> Manage Downtime Schedules

At the moment of the downtime, do the following:

2.  Shutdown Apache (on Normal Mode): stop
   or <apps_user>/<apps_pwd>

3.  Enable 'Maintenance Mode' from adadmin

   adadmin: Options 5, 1

4. Start Apache (on Restricted Mode) start

5. Apply the Patch with adpatch
6.  Stop Apache (on Restricted Mode) stop

7.  Disable 'Maintenance Mode' from adadmin

   adadmin: Options 5, 2

8.  Start Apache (on Normal Mode): start
  or <apps_user>/<apps_pwd>

Managing Downtime in Restricted Mode from Oracle Application Manager

In Restricted Mode, only valid database users are allowed to login into OAM via a special URL and

are allowed to access a limited set of features. The database role AD_MONITOR_ROLE has access

to all the required database objects for Restricted Mode features. However if a valid database user

who does not have the AD_MONITOR_ROLE may have further limited access to OAM functionality

based on the database objects to which this user has access. In 11.5.10, monitoring in-progress AD

utilities is the only feature that is accessible.

How to Implement Restricted Mode

1) Record the name of the PC you want to give access to the restricted mode
       login to OAM.
a. Right click the "My Computer ICON" on the Desktop
b. Arrow down to Properties
c. Select "Computer Name" or "Network Identification" Tab
d. Record the computer name -- ie:
2) Use OAM Autoconfig editor to edit the variable "s_trusted_admin_client_nodes"
to include the list of client machines that can access OAM in restricted mode.
Run autoconfig on the APPL_TOP to ensure that the new settings take effect.
a. In OAM > SiteMap > System Configuration > AutoConfig > Edit Parameters >
   Choose "System" parameters > Drill down on the "oa_web_server" title
   search down for s_trusted_admin_client_nodes
Enter comma separated values consisting of fully qualified node names
(use IP address only if the address the address is static IP for a client)
of client machines which will be allowed to administer E-business instance.
Make sure the server can resolve the machine names.
Note: to verify that the trusted client has been added, check the
$iAS_TOP/Apache/Apache/conf/apps.conf for the entries that have been added
for the "RESTRICT to the trusted.conf"
ie: include "/u00/oracle/prodora/iAS/Apache/Apache/conf/trusted.conf"
<Location /servlets/weboamLocal>
Order deny,allow
Deny from all
Allow from system1-sun
Allow from
Allow from
3) Ensure that you have enabled the the monitoring user account by unlocking
the "ad_monitor" user account and setting the password by using the following
$ sqlplus system/manager
SQL> alter user ad_monitor account unlock;
SQL> commit;
By default this ad_monitor password is set to "lizard"
the first time the password is unlocked - change the password
4) Shutdown the application Services
5) Restart the apache server in Restricted Mode using the 'adaprstctl' script
$ adaprstctl start
6) Launch OAM Restricted Mode using the OAM Restricted Mode URL:
If an error occurs when accessing the URL
ex: You are not authorized to view this page
HTTP - error 403 - Forbidden
Check the Internet Explorer Tools > Internet Options > Connections > LAN Settings
- Uncheck Automatically detect settings
- Check Use Automatic configuration script
- Uncheck Proxy Server
- Delete Cookies
- Delete files and all files content
7) Login using the ad_monitor account and new password
8) You are now in the OAM Restricted Mode. You can now access utilities on the Maintenance
    tab of the Site Map.
   You can monitor progress of your patching utilities here.
9) After patch completion, bring down the Apache Server in Restricted Mode using the script 'adaprstctl'.
10) If the patch requires AutoConfig be run, run AutoConfig.
11) Start the system using the script $COMMON_TOP/admin/scripts/<context name>/
      Apache and items such as concurrent managers, forms will be started.

Enabling f60cgi direct login in Oracle Apps

Accessing f60cgi is disabled in 11.5.10


With increased security in Oracle EBusiness Suite 11.5.10, the ability to connect directly to forms via f60cgi

has been disabled. By default, a user will see the following error after entering their username and password:

APP-FND-01542: This Applications Server is not authorized to access this database. This is expected functionality.


Enabling f60cgi direct login


It is possible to login however this method should only be used when debugging problems.


1. Backup and open $APPL_TOP/admin/<SID>_<hostname>.xml context file

2. Update the context variable:


    By default in 11.5.10, this is set to SECURE.

    In previous 11i versions, this was set to OFF. For debug purposes, you can use ON or OFF.


    - ON : Partial

    - SECURE : activates full server security (SECURE mode)

    - OFF : deactivates server security

3. Run Autoconfig to instantiate the change.

    You should now be able to access forms directly again using the f60cgi call.

4. After you have finished your Forms debugging, please reset

    s_appserverid_authentication to SECURE and re-run Autoconfig.


Alternative option


Running Autoconfig is the preferred method of updating


If you are unable to run Autoconfig during troubleshooting, you can run the the following commands instead

from $FND_TOP/secure directory:


java apps/apps \

AUTHENTICATION OFF DBC=host_visdemo1.dbc


To activate basic server security, from the command line, enter:

jre apps/apps \


To activate full server security (SECURE mode), from the command line, enter:

jre apps/apps \


Check the status:

java apps/apps \

STATUS DBC=host_visdemo1.dbc

Monday, October 22, 2007

Creating a Custom Application in Oracle Applications 11i

Custom Applications are required if you are creating new forms, reports, etc.   
This allows you to segregate your custom written files from the standard seeded functionality that
Oracle Applications provide.
Customizations can therefore be preserved when applying patches or upgrades to your environment. 
1)  Make the directory structure for your custom application files.
               cd $APPL_TOP
               mkdir fz
               mkdir fz/11.5.0
               mkdir fz/11.5.0/admin
               mkdir fz/11.5.0/admin/sql
               mkdir fz/11.5.0/admin/odf
               mkdir fz/11.5.0/sql
               mkdir fz/11.5.0/bin
               mkdir fz/11.5.0/reports
               mkdir fz/11.5.0/reports/US
               mkdir fz/11.5.0/forms
               mkdir fz/11.5.0/forms/US 
               mkdir fz/11.5.0/$APPLLIB
               mkdir fz/11.5.0/$APPLOUT
               mkdir fz/11.5.0/$APPLLOG 
2)  Add the custom module into the environment
Apply ADX.E.1 and add the entry to topfile.txt as a standard product top entry .   
Customised environment variables can be added to AutoConfig by using the filename specificed by s_custom_file, 
which is then called from the APPSORA.env file. 
If using Forms Listener Servlet, you may also need to add $CUSTOM_TOP to formsservlet.ini in
3)  Create new tablespace for database objects
     create tablespace FZ datafile '/u01/oracle/visdata/fz.dbf' size 10M default storage(initial 10k next 10k)
4)  Create schema
               create user fz identified by fz
                               default tablespace fz
                               temporary tablespace temp
                               quota unlimited on fz
                               quota unlimited on temp;
               grant connect, resource to fz;
5)  Register your Oracle Schema. 
     Login to Applications with System Administrator responsibility
     Navigate to Application-->Register
               Application = FZ Custom
               Short Name  = FZ
               Basepath    = FZ_TOP
               Description = FZ Custom Application
6)  Register Oracle User
     Naviate to Security-->Oracle-->Register
               Database User Name = FZ
               Password           = FZ
               Privilege          = Enabled
               Install Group      = 0
               Description        = FZ Custom Application User
7)  Add Application to a Data Group
     Navigate to Security-->Oracle-->DataGroup
               Data Group  = FZGroup
               Description = FZ Custom Data Group
      Click on "Copy Applications from" and pick Standard data Group, then add the following entry.
               Application = FZ Custom
               Oracle ID   = APPS
               Description = FZ Custom Application
8)  Create custom request group
     This will act as a placeholder for any custom reports we wish to make available for the Custom Responsibility 
      Navigate to Security-->responsbility-->Request
               Group       = FZ Request Group
               Application = FZ Custom
               Code        = FZ
               Description = FZ Custom Requests
       We will not define any requests to add to the group at this stage, but you can add some now if required.
9)  Create custom menu
      This will act as a placeholder for any menu items we wish to make available for the Custom Responsibility. 
      We will create two menus, one for Core Applications and one for Self Service.
       Navigate to Application-->Menu
               Menu           = FZ_CUSTOM_MENU
               User Menu Name = FZ Custom Application
               Menu Type      = <leave blank>
               Description    = FZ Custom Application Menu
               Seq         = 100
               Prompt      = View Requests
               Submenu     = <leave blank>
               Function    = View All Concurrent Requests
               Description = View Requests
               Seq         = 110
               Prompt      = Run Requests
               Submenu     = <leave blank>
               Function    = Requests: Submit
               Description = Submit Requests
               Menu           = FZ_CUSTOM_MENU_SSWA
               User Menu Name = FZ Custom Application SSWA
               Menu Type      = <leave blank>
               Description    = FZ Custom Application Menu for SSWA 
10)  Create new responsibility.   One for Core Applications and One for Self Service (SSWA)
       Navigate to Security-->Responsibility-->Define
               Responsibility Name       = FZ Custom
               Application               = FZ Custom
               Responsibility Key        = FZCUSTOM
               Description               = FZ Custom Responsibility
               Available From            = Oracle Applications
               Data Group Name           = fzGroup
               Data Group Application    = FZ Custom
               Menu                      = FZ Custom Application
               Request Group Name        = FZ Request Group
               Responsibility Name       = FZ Custom SSWA
               Application               = FZ Custom
               Responsibility Key        = FZCUSTOMSSWA
               Description               = FZ Custom Responsibility SSWA
               Available From            = Oracle Self Service Web Applications
               Data Group Name           = fzGroup
               Data Group Application    = FZ Custom
               Menu                      = FZ Custom Application SSWA
               Request Group Name        = FZ Request Group
11)  Add responsibility to user
       Navigate to Security-->User-->Define
       Add FZ Custom responsibility to users as required.
12)  Other considerations
       You are now ready to create your database Objects, custom Reports, Forms, Packages, etc
Create the source code files in the FZ_TOP directory appropriate for the type of object.   For example forms
would be located in $FZ_TOP/forms/US or package source code in $FZ_TOP/admin/sql for example.
Database Objects, such as tables, indexes and sequences should be created in the FZ schema, then you need to 
               a) Grant all privilege from each custom data object to the APPS schema.
                               For example :  logged in as FZ user
                                              grant all privileges on myTable to apps;
               b) Create a synonym in APPS for each custom data object
                               For example :  logged in as APPS user
                                              create synonym myTable for fz.myTable;
Other database objects, such as views and packages should be created directly in the APPS schema.

Sunday, October 21, 2007

Relinking Oracle Application Server 10g R2 (10.1.2) and 10g R3 (10.1.3) on UNIX

All Oracle Application Server 10g processes from the Oracle Home to be relinked need to be stopped using normal

administrative steps prior to beginning the relink.

Setup your environment correctly. Since both OracleAS 10g R2 and R3 are based on Oracle 10.1 database software,

pick the environment variable settings that apply to this version.

Relink Oracle Application Server 10g R2 (10.1.2/

Relinking the Infrastructure (Identity Management (IM) + Metadata Repository (MR), or MR Only)

  1. Relink Database software
    $ORACLE_HOME/bin/relink all
    cd $ORACLE_HOME/ldap/lib (Not required for MR Only)
    make -f install
    make -f hragentinstall
  2. Relink Application Server Control
    cd $ORACLE_HOME/sysman/lib
    make -f agent
  3. As root execute $ORACLE_HOME/, and $ORACLE_HOME/ if it exists.

This is needed to correctly set the permissions on the OID, and Application Server Control executables.

Relinking the Infrastructure (IM Only)

  1. Relink Database software
    cd $ORACLE_HOME/network/lib
    make -f client_sharedlib
    make -f install
    cd $ORACLE_HOME/rdbms/lib
    make -f utilities
    cd $ORACLE_HOME/plsql/lib
    make -f install
    cd $ORACLE_HOME/sqlplus/lib
    make -f install
    cd $ORACLE_HOME/ldap/lib
    make -f install
    make -f hragentinstall
  2. Relink Application Server Control
    cd $ORACLE_HOME/sysman/lib
    make -f agent
  3. As root execute $ORACLE_HOME/, and $ORACLE_HOME/ if it exists.

This is needed to correctly set the permissions on the OID, and Application Server Control executables.

Relinking the Middle Tier

  1. Relink Database software:
    cd $ORACLE_HOME/network/lib
    make -f client_sharedlib
    make -f install
    make -f install (Business Intelligence & Forms only)
    cd $ORACLE_HOME/rdbms/lib (Not required for J2EE & Web Cache)
    make -f iexp
    make -f iimp
    make -f iloadpsp
    cd $ORACLE_HOME/plsql/lib (Not required for J2EE & Web Cache)
    make -f install
    cd $ORACLE_HOME/sqlplus/lib
    make -f install
    cd $ORACLE_HOME/ldap/lib (Not required for J2EE & Web Cache)
    make -f toolsinstall
  2. Relink Application Server Control
    cd $ORACLE_HOME/sysman/lib
    make -f agent
  3. Relink Web Cache
    cd $ORACLE_HOME/webcache/lib
    make -f install
  4. Relink Forms (Business Intelligence & Forms only)
    cd $ORACLE_HOME/forms/lib
    make -f frmweb_install
    make -f frmcmp_install
    make -f frmcmpb_install
  5. Relink Reports (Business Intelligence & Forms only)
    cd $ORACLE_HOME/reports/lib
    make -f install
  6. As root execute $ORACLE_HOME/, and $ORACLE_HOME/ if it exists.

This is needed to correctly set the permissions on the Application Server Control executables.

If permissions were previously modified for Web Cache, you will need to re-run any scripts (or apply manual changes)

to obtain any original functionality. For more information about,

Please refer to the following documentation:
Oracle Application Server Web Cache Administrator's Guide 10g (10.1.2)
8 Setup and Configuration
Running webcached with Root Privilege

Relink Oracle Application Server 10g R3 (10.1.3)

Relinking the Middle Tier

  1. Relink Database software:
    cd $ORACLE_HOME/network/lib
    make -f client_sharedlib
    make -f install
    cd $ORACLE_HOME/sqlplus/lib
    make -f install

2. As root execute $ORACLE_HOME/, and $ORACLE_HOME/ if it exists. This is needed to correctly

set the permissions on the newly relinked executables.

How to Clean 11i Apps Concurrent Manager Tables

This can be used as a method to clear the errors upon bringing the internal manager back up.

Use this method for 11.5.7+ instances provided the managers are down and no FNDLIBR processes are
still running.
Update process status codes to TERMINATED
Updating invalid process status codes in FND_CONCURRENT_PROCESSES

SELECT concurrent_queue_name manager,
concurrent_process_id pid,
process_status_code pscode
FROM fnd_concurrent_queues fcq, fnd_concurrent_processes fcp
WHERE process_status_code not in ('K', 'S')
AND fcq.concurrent_queue_id = fcp.concurrent_queue_id
AND fcq.application_id = fcp.queue_application_id;

UPDATE  fnd_concurrent_processes
SET process_status_code = 'K'
WHERE process_status_code not in ('K', 'S');
Set all managers to 0 processes
Updating running processes in FND_CONCURRENT_QUEUES
Setting running_processes = 0 and max_processes = 0 for all managers

UPDATE fnd_concurrent_queues
SET running_processes = 0, max_processes = 0;
Reset control codes
Updating invalid control_codes in FND_CONCURRENT_QUEUES

SELECT  concurrent_queue_name manager,
control_code ccode
FROM fnd_concurrent_queues
WHERE control_code not in ('E', 'R', 'X')
AND control_code IS NOT NULL;

UPDATE fnd_concurrent_queues
SET control_code = NULL
WHERE control_code not in ('E', 'R', 'X')
AND control_code IS NOT NULL;
Also null out target_node for all managers
UPDATE  fnd_concurrent_queues
SET target_node = null;

Set all 'Terminating' requests to Completed/Error
Also set Running requests to completed, since the managers are down
Updating any Running or Terminating requests to Completed/Error

SELECT  request_id request,
phase_code pcode,
status_code scode
FROM fnd_concurrent_requests
WHERE status_code = 'T' OR phase_code = 'R'
ORDER BY request_id;

UPDATE fnd_concurrent_requests
SET phase_code = 'C', status_code = 'E'
WHERE status_code ='T' OR phase_code = 'R';
Set all Runalone flags to 'N'
Updating any Runalone flags to 'N'
set serveroutput on
set feedback off
c pls_integer := dbms_sql.open_cursor;
upd_rows pls_integer;
vers varchar2(50);
tbl varchar2(50);
col varchar2(50);
statement varchar2(255);

select substr(release_name, 1, 2)
into vers
from fnd_product_groups;

if vers >= 11 then
tbl := 'fnd_conflicts_domain';
col := 'runalone_flag';
tbl := 'fnd_concurrent_conflict_sets';
col := 'run_alone_flag';
end if;

statement := 'update ' || tbl || ' set ' || col || '=''N'' where ' || col || ' = ''Y''';
dbms_sql.parse(c, statement, dbms_sql.native);
upd_rows := dbms_sql.execute(c);
dbms_output.put_line('Updated ' || upd_rows || ' rows of ' || col || ' in ' || tbl || ' to ''N''');
Now you can start with your Concurrent Managers...!!!!!!!!

Saturday, October 20, 2007


Before copying the database to a new location, it is necessary to perform a full cold backup of the database,
whilst the database is shutdown. This will ensure that no data will be lost if the copying of the database is


Creating a copy of a database involves usage of the CREATE CONTROLFILE command (explained below).
If this command is not executed correctly it could corrupt the production database. This will mean that
the original database will need to be restored from a backup.


In order to move the database, it is necessary to create a script containing information about the files
of the database.

This is done by executing the following commands.

a. sqlplus /nolog

b. SQL> connect / as sysdba


This will create a trace file in the trace file directory. The file will have the extension .trc, and is located
either in the directory defined by the initialization parameter 'user_dump_dest', or, if this is not defined,
in ORACLE_HOME/rdbms/log. The file should be copied to a name such as ccf<NEW_SID>.sql, where
<NEW_SID> is to be the ORACLE_SID of the copied database.


a. Identify database and log files

The CREATE CONTROLFILE command in the file ccf<NEW_SID>.sql can then be used to identify the
various database files and redo log files that need to be backed up. The file names will be in single quotes
and separated by commas after the words LOGFILE and DATAFILE, e.g:

) SIZE 50K,
) SIZE 50K
'/oracle/tberryha/fritz/fritz_system/famy_system01.dbf' SIZE 8M,
'/oracle/tberryha/fritz/fritz_data/famy_data01.dbf' SIZE 20M,
'/oracle/tberryha/fritz/fritz_rollback/famy_rollback01.dbf' SIZE 20M,
'/oracle/tberryha/fritz/fritz_temp/famy_temp01.dbf' SIZE 20M,
'/oracle/tberryha/fritz/fritz_data/famy_data02.dbf' SIZE 5M,
'/oracle/d2/V7141/dbs/x' SIZE 1M
It is also possible to obtain a listing of the files of the database by executing the following sql commands:

SQLPLUS username/password
Note: The user must have sufficient privileges to be able to see the dba views 'sys.dba_data_files',
'sys.v$logfile' and in addition the database must be open.

SPOOL files.log

SQL> SELECT file_name FROM sys.dba_data_files ORDER BY tablespace_name;

SQL> SELECT member FROM sys.v$logfile

Note: This will create a spool file called 'files.log' which will record the results of the previous commands.

b. Identify controlfiles

This can be done either by referring to the init<SID>.ora 'control_files' parameter, or from 7.0.16
onwards, the table sys.v$controlfile can be used to identify the controlfiles of the database via the following

SPOOL control.log
SELECT name FROM v$controlfile;

This will create a file called control.log in the current directory which will contain the names of the
controlfiles for the database.


Shutdown instance via SHUTDOWN NORMAL, and then take full cold backup of:

a. All the files identified in step 2 above.

b. All parameter files.
Note: the main parameter file will usually be called init<SID>.ora, in addition to which there may
also be other parameter files. These will be identified by 'ifile' (included file) parameters in the
init<SID>.ora. These additional parameter files are usually called config<SID>.ora.

Shutdown instance via SQLPlus, SHUTDOWN NORMAL.

Copy all parameter files, controlfiles, and all files noted in step 2 above to their new location taking care
to preserve ownership and permissions. When the database has been copied, it will not be possible to use
the same SID and therefore the ORACLE_SID environment variable must be changed to a new SID, and the
copied init<SID>.ora must be renamed to init<NEW_SID>.ora, and any parameter files pointed
to by an 'ifile' parameter (e.g. parameter files such as config<SID>.ora) should be renamed to incorporate
the NEW_SID (i.e. config<NEW_SID>.ora).

---------------------------------------------- -------------------------

Edit the value of the control_files parameter in the init<NEW_SID>.ora to be the name and
location that you want to use for the new control files. The controlfiles should be given a different
name to distinguish them from the old database. In addition, change the DB_NAME parameter in
the init<NEW_SID>.ora to be an appropriate name for the new database. Any 'ifile' parameters
of the parameter file will need to be edited to point to the new name of the include file in the new
location.Ensure that the parameter DB_BLOCK_SIZE is the same as the originating database, otherwise
when creating the controlfile you may receive: ORA-01160 "file is not a %s"


In order to establish the new database in the new location, the CREATE CONTROLFILE command in the
file ccf<NEW_SID>.sql should be executed. The following steps illustrate how CREATE CONTROLFILE
command is prepared.

a. The file ccf<NEW_SID>.sql must be edited before use. The CREATE CONTROLFILE command will
be preceded by a series of comments and a STARTUP NOMOUNT command. These need to be stripped
out of the file. In addition, after the create controlfile command, there will be a number of comments
and the commands RECOVER DATABASE and ALTER DATABASE OPEN, which should also be stripped out,
leaving just the create controlfile
       command itself. 

b. The CREATE CONTROLFILE command itself should also be edited. Change the CREATE CONTROLFILE
command in 'ccf<NEW_SID>.sql' to have the new database name, and add the word 'SET', e.g:


c. The CREATE CONTROLFILE command also specifies the files which make up the database, and these
must also be changed to name the files of the new database in the new location, e.g:

) SIZE 50k
would become:
) SIZE 50k
'/old_path/old_file_name1' SIZE 5M,
'/old_path/old_file_name2' SIZE 10M
would become:
'/new_path/new_file_name1' SIZE 5M,
'/new_path/new_file_name2' SIZE 10M

d. After Recreating Controlfile

o Tablespace TEMP is available
o DBA_TEMP_FILES does not show a tempfile

the Datafile for Tablespace TEMP (TEMPFILE) is not listed in


You must add the TEMPFILE manaually after Creating the Controlfile

> Alter database backup controlfile to trace ;

You should check Tracefile: (in udump) to get Statement on how to Add the Tempfile to temp Tablespace

--> see end of tracefile

Create Controlfile .....
# Commands to add tempfiles to temporary tablespaces.
# Online tempfiles have complete space information.
# Other tempfiles may require adjustment.
# End of tempfile additions.

Simply adjust this command (path/name) to your requirements and do for example:


Prior to executing the create controlfile script, check whether the initialisation parameter
REMOTE_LOGIN_PASSWORDFILE is set to exclusive or shared. If this is set, then a valid
passwordfile should exist in ORACLE_HOME/dbs or created using orapwd, otherwise you will
receive an ora-600 [kzsrsdn:1].

Having prepared the create controlfile script, it is now necessary to run the script from within
the new instance. This is done by executing the following:

a. at the operating system prompt, change the value of the environment variable ORACLE_SID
from OLD_SID to NEW_SID.
b. SQLPlus
SQL> STARTUP NOMOUNT PFILE=/<full path>/init<NEW_SID>.ora
d. SQL> @ccf<NEW_SID>

Note: if any files which should be specified in the CREATE CONTROLFILE command are omitted,
these files cannot be added to the new database at a later date. In addition, if any of the files
specified in the CREATE CONTROLFILE command are NOT changed from their original names, then
the corresponding files of the original database will become part of the copied database, and it will
not be possible to restore them to the original database. If this happens, and if the files in question
are important, this will mean that the original database will need to be restored from a backup.

When recreating the controlfile, it is possible to encounter ORA-1565, ORA-1503 and ORA-9782,
"another instance has this database mounted", the ORACLE_SID of the original database is still in the
header of the first datafile of the copy and oracle checks if an sgadef<ORACLE_SID>.dbf still exists
in $ORACLE_HOME/dbs, to workaround this problem shutdown the other database of which you made a
copy prior to issueing the create controlfile command.

NOTE: If you are using online 'hot' backup at this point you should issue:

recover database until cancel using backup controlfile;

Apply all the archives you have


----------------------------------------- -------------------------------

SHUTDOWN and take a full cold backup of the database in the new location. The full cold backup can be
done as detailed in steps 2 and 3.