Thursday 24 January 2019

Oracle Fusion Fixed Asset Retirement Reason LOV list addition


1)Login to Cloud 
Navigate to   
Setup and Maintenance

2)  Select financial scope and search for the Task "Manage Fixed Assets Lookups"
3)  Search for the Lookup Type "FA_RETIREMENT_TYPE"
4)  Click on the "+" icon and add value and save

Monday 21 January 2019

Migration of Oracle EBS R12 From 32 bit to 64 bit


Migration of Oracle EBS R12 From 32 bit to 64 bit
(Worked on R12.1.3 Application & 11.1.0.7 Database)


Please refer to metalink note 
Migrating OLAP From 32 To 64 Bits [352306.1]
Using Oracle EBS R12 with a Database Tier Only Platform on Oracle 11gR1 [762669.1]
Migrating Oracle E-Business Suite R12 from Linux 32-bit to Linux 64-bit [471566.1]       
DB CONVERSION: 32 bit -->64 Bit Broke OLAP OPTION [386990.1]


The steps followed during the migration were (Step should be followed as per the TOC is given):
1. Database Source Instance (32-bit)
     1.1 Shut down the application tier services
     1.2 De-register the original database server machine
       1.3   Create a .trc file to use as template to re-create the control files on the target machine.
       1.4   Migrating OLAP From 32 To 64 Bits
             1.4.1 To migrate from a 32bit installation to a 64bit installation of Oracle OLAP, the basic steps are:
             1.4.2 On the original 32-bit installation, export user created AWs and then delete these AWs.
             1.4.3 Remove OLAP from the 32bit installation using the Oracle Universal Installer.
             1.4.4 For the new 64-bit installation make certain Oracle OLAP is installed.
             1.4.5  Restore OLAP to the migrated database mean 32bit instance.
             1.4.6  Import all AWs from the 32bit installation.
       1.5 Shut down the original (source) database.

 2.  Database Target Instance (64-bit)
       2.1    Install 64-bit Apps Database.
     2.2   After Installation of Database remove the *.dbf file from data directory.
       2.3   Copy *.dbf file from the source instance (32-bit) to target instance (64-bit).
       2.4   Modify configuration files on the target machine to reflect the new environment.
       2.5   Startup the instance using trace file which is been created on source instance (32-bit).
       2.6   Invalid all the objects using utlirp.sql.
       2.7  Compile all the invalid objects using utlrp.sql.
       2.8  Add OLAP back into the database (64-bit Instance) by connecting to the database '/ as sysdba'.
       2.9  Run catalog and catproc
       2.10 Drop the dblink and  add the dblink again
       2.11  

 3. Application
        3.1    Copy the Entire APPS  from the source application (32-bit) to target application (64-bit).
        3.2    Update environment variable & CTXORIG.xml
        3.3    Apply cloning to the system to start up the system.
        3.4    Use adadmin to perform the following

Detailed steps for Database step 1.2:
      A)      De-register the original database server machine
De-register the original database server machine from the AutoConfig repository in the Applications database. As the ORACLE user, log on to the machine with the existing database and run the following commands:
 
$ cd <RDBMS ORACLE_HOME>/appsutil/bin 
$ perl adgentns.pl appspass=<APPSpwd> contextfile=<CONTEXT_FILE> -removeserver
 
Detailed steps for Database step 1.3:
       A)      Create a .trc file to use as template to re-create the control files on the target machine.
SQL> alter database backup control file to trace.

Detailed steps for Database step 1.4:
A)  To obtain the list of AWs that need to be exported execute this command and then proceed to export the AWs that are NOT owned by 0:      
 SQL> col owner format a15

 SQL> col aw_name format a15

 SQL> select OWNER, AW_NAME ,PAGESPACES from dba_aws where owner != 'SYS' order by 1,2;
 
OWNER           AW_NAME         PAGESPACES
---------------      ---------------         --------------------
APPS                ODPCODE               1697
APPS               XWDEVKIT             1082
FPA                 FPAPJP                       504
ZPB                ZPBANNOT                    7
ZPB                ZPBCODE                   458
ZPB                ZPBDATA                      7
6 rows selected.

The columns 'OWNER' and 'AW_NAME' will be needed in the further steps.
The 'pagespaces' give you a rough indication of the size of an AW. This way  you get a general idea about which are the largest AW's that will take the longest to export.
The AW's belonging to SYS are not included as they are re-created when OLAP is installed.
So there is no need to export them.
B)  Use the following SQL commands, for each AW that was identified in step #A (NOTE: All quotes are single quotes):
--- Replace OWNER.AW_NAME with the the actual OWNER.AW_NAME name

SQL> exec dbms_aw.execute('aw attach OWNER.AW_NAME rw');
Ex - SQL> exec dbms_aw.execute('aw attach APPS.ODPCODE rw');
 
 
--- (NOTE: this following statement is critical so everything in the AW is in STATUS)
SQL> exec dbms_aw.execute('allstat');

 
--- Replace ALIAS_DIR with a defined directory alias
--- Replace export_file_name.eif with a valid eif file name
SQL> exec dbms_aw.execute('export all to eif file ''ALIAS_DIR/export_file_name.eif '''); 
 
                --If  you get the following error while exporting:
ERROR at line 1:
ORA-33390: There are no objects to export.
ORA-06512: at "SYS.DBMS_AW", line 93
ORA-06512: at "SYS.DBMS_AW", line 122
ORA-06512: at line 1
                --It means the AW is empty and there is nothing to export. You can safely delete the AW.
 
 
--- Replace OWNER.AW_NAME with the actual OWNER.AW_NAME name

SQL> exec dbms_aw.execute('aw detach OWNER.AW_NAME');
Ex - exec dbms_aw.execute('aw detach APPS.ODPCODE');


C)  Delete each AW that has been exported in step #B, using the SQL command:
--- Replace OWNER.AW_NAME with the actual OWNER.AW_NAME name

SQL> exec dbms_aw.execute('aw delete OWNER.AW_NAME');
Ex - exec dbms_aw.execute('aw delete APPS.ODPCODE');
D)  Remove OLAP from the 32bit database and cleanup any INVALID OLAP related objects by executing the following commands:
      $ cd $ORACLE_HOME/olap/admin   
        --->>> required to locate all embedded calls to other scripts
               
$ sqlplus /nolog  
                     
> conn / as sysdba
              
SQL> @?/olap/admin/catnoamd.sql
              
SQL> @?/olap/admin/olapidrp.plb
              
SQL> @?/olap/admin/catnoaps.sql
              
SQL> @?/olap/admin/catnoxoq.sql
              
SQL> @?/rdbms/admin/utlrp.sql
              
SQL> select owner, object_name, object_type from dba_objects where status <> 'VALID';

                Remove any INVALID OLAP related objects
E)  Install the 64bit version of Oracle, include the Oracle OLAP Option, and migrate the database to 64bits.
F)  Add OLAP back into the database (32-bit Instance) by connecting to the database '/ as sysdba' and executing  
      (Can be or Cannot be Perform)     SQL> @?/olap/admin/olap.sql SYSAUX TEMP;

G)  Use the following SQL commands to import each of the exported AWs. 
NOTE: All quotes are single quotes and make sure that you use the right combination of EIF file and OWNER.AW_NAME
                    SQL > exec dbms_aw.execute('aw create OWNER.AW_NAME');
                    Ex - exec dbms_aw.execute('aw create APPS.ODPCODE');

                    --- Replace ALIAS_DIR with a defined directory alias
                    SQL> exec dbms_aw.execute('import all from eif file ''ALIAS_DIR/export_file_name.eif'' data dfns');

                    SQL> exec dbms_aw.execute('update');

                    SQL> commit;

--- Replace OWNER.AWNAME with the actual OWNER.AW_NAME name
SQL> exec dbms_aw.execute('aw detach OWNER.AW_NAME);
        Ex - exec dbms_aw.execute('aw detach APPS.ODPCODE');

Detailed steps for Database step 2.4:
         A)   Modify configuration files on the target machine to reflect the new environment.
Set _system_trig_enabled=false


Detailed steps for Database step 2.5:
      A)   Startup the instance using trace file which is been created on source instance (32-bit).
                  a.      Recreate the database control files
Edit the trace file name which was copied from source to target DB [SID_ora_<Number>.trc]
                              i.                    Remove all the line before Startup nomount.
                              ii.                   Replace REUSE to SET
                              iii.                  Replace source DB SID to target DB SID
                              iv.                 Replace NORESTLOGS to RESTLOGS
                              v.                  Replace ARCHIVELOG to NOARCHIVELOG
                              vi.                Remove all the line after CHARACTER SET statement
                              vii.               Replace the source mount point to target server mount point.

                  b.      Delete the control files and  temp files from the data directory.

                  c.      Start up the database in nomount mode.
       # su - db_user
                                       $ sqlplus '/as sysdba'
                                       SQL> startup nomount ;
                                       SQL> @SID_ora_<number>.trc    [Modified control tracefile]
                                                 {It will create new control file them database will be mounted}
                                       SQL> alter database open resetlogs;

                  d.     Set the temporary tablespace to TEMP1
                       SQL> alter database default temporary tablespace TEMP1;
                       SQL> alter tablespace TEMP1 add tempfile '<Path of  temp03.dbf>' size 1024M;
                       SQL> alter tablespace TEMP1 add tempfile '<Path of  temp04.dbf>' size 1024M;

Detailed steps for Database step 2.6:
          A)      Invalid all the objects using utlirp.sql.
                       a.   shutdown immediate
                       b.   startup upgrade
                       c.    SQL> @?/rdbms/admin/utlirp.sql
                       d.   shutdown immediate
                       e.    startup
                       f.     startup database listener.


Detailed steps for Database step 2.7:

            A)    Compile all the invalid objects using utlrp.sql.
                      a.    shutdown immediate
                      b.   startup
                      c.    startup database listener.
                      d.    #sqlplus ‘/as sydba’
                      e.    SQL> @?/rdbms/admin/utlrp.sql

Detailed steps for Database step 2.8:
            A)    Add OLAP back into the database (64-bit Instance) by connecting to the database '/ as sysdba'.
                   SQL> @?/olap/admin/olap.sql SYSAUX TEMP;

            B)    Use the following SQL commands to import each of the exported AWs. 
NOTE: All quotes are single quotes and make sure that you use the right combination of EIF file and OWNER.AW_NAME
    SQL > exec dbms_aw.execute('aw create OWNER.AW_NAME');
    Ex - exec dbms_aw.execute('aw create APPS.ODPCODE');

    --- Replace ALIAS_DIR with a defined directory alias
    SQL> exec dbms_aw.execute('import all from eif file ''ALIAS_DIR/export_file_name.eif'' data dfns');

    SQL> exec dbms_aw.execute('update');

    SQL> commit;

--- Replace OWNER.AWNAME with the actual OWNER.AW_NAME name

SQL> exec dbms_aw.execute('aw detach OWNER.AW_NAME);
    Ex - exec dbms_aw.execute('aw detach APPS.ODPCODE');


Detailed steps for Database step 2.7:
            A)      Compile all the invalid objects using utlrp.sql.
                     a.    SQL> @?/rdbms/admin/utlrp.sql

Detailed steps for Database step 2.9:
           A)     Run catalog and catproc.
                     a.     shutdown immediate
                     b.    startup upgrade
<!--[if !supportLists]-->c.        <!--[endif]-->run catalog and catproc script 
        SQL> @?/rdbms/admin/catalog.sql
        SQL> @?/rdbms/admin/catproc.sql
<!--[if !supportLists]-->d.       <!--[endif]-->shutdown immediate
<!--[if !supportLists]-->e.        <!--[endif]-->startup
<!--[if !supportLists]-->f.        <!--[endif]--> run utlrp.sql
<!--[if !supportLists]-->g.        <!--[endif]-->select comp_name,version,status from sys.dba_registry;

<!--[if !supportLists]-->B)      <!--[endif]-->Drop the dblink and add the dblink again.

# sqlplus apps/apps
SQL> select db_link, username, host from dba_db_links;

SQL> drop database link [custom database link];
Ex - SQL> drop database link EDW_APPS_TO_WH.US.ORACLE.COM;
Ex - SQL> drop database link EDW_APPS_TO_WH.KROSS.COM;
Ex - SQL> drop database link EDW_APPS_TO_WH.CONACENT.COM;
Ex - SQL> drop database link APPS_TO_APPS.US.ORACLE.COM;
Ex - SQL> drop database link APPS_TO_APPS.KROSS.COM;
Ex - SQL> drop database link APPS_TO_APPS.CONACENT.COM;


SQL> create database link [custom database link] connect to [user]
identified by [password] using'(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=[hostname])
(PORT=[portnumber]))(CONNECT_DATA=(SID=[ORACLE_SID])))';

Ex - SQL> create database link APPS_TO_APPS.CONACENT.COM connect to APPS identified by kdropss using'(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=test.conacent.com) (PORT=1571))(CONNECT_DATA=(SID=TEST)))';

Ex - SQL> create database link EDW_APPS_TO_WH.CONACENT.COM connect to APPS identified by kdropss using'(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=test.conacent.com) (PORT=1571))(CONNECT_DATA=(SID=TEST)))';

<!--[if !supportLists]-->C)      <!--[endif]-->Run adautoconfig

<!--[if !supportLists]-->a.       <!--[endif]-->./ adautocfg.sh
<!--[if !supportLists]-->b.       <!--[endif]-->run utlrp again.
        SQL> @?/rdbms/admin/utlrp.sql
<!--[if !supportLists]-->c.        <!--[endif]-->If you get an error while running utlrp “UTLPRP.SQL failes from ORA-00904: "FALSE"; INVALID IDENTIFIER”. You have to apply a opatch patch number 8264899 metalink id 735276.1


Detailed steps for Application step 3.2:
<!--[if !supportLists]-->A)    <!--[endif]-->Update environment variable
On the target system, set the environment variable LDEMULATION to elf_i386.
LDEMULATION=elf_i386
export LDEMULATION
<!--[if !supportLists]-->B)    <!--[endif]-->Update CTXORIG.xml
Update the the value of the s_platform parameter in CTXORIG.xml from Linux to LINUX_X86-64. The CTXORIG.xml file is located in the <COMMON_TOP>/clone/context/apps directory of the target system. A sample entry is shown below:
                         <platform oa_var="s_platform" osd="LINUX_X86-64">LINUX_X86-64</platform>  
                       (Linux is replaced by LINUX_X86-64)


Detailed steps for Application step 3.3:

<!--[if !supportLists]-->A)      <!--[endif]--> Apply adcfgclone.pl appsTier procedure to start the new instance.


Detailed steps for Application step 3.4:

<!--[if !supportLists]-->A)      <!--[endif]--> Use adadmin to perform the following:

<!--[if !supportLists]-->1.       <!--[endif]-->Relink AD executables (Use AD Relink for this step)
<!--[if !supportLists]-->2.       <!--[endif]-->Generate message files.
<!--[if !supportLists]-->3.       <!--[endif]-->Generate form files.
<!--[if !supportLists]-->4.       <!--[endif]-->Generate report files.
<!--[if !supportLists]-->5.       <!--[endif]-->Generate product JAR files.
<!--[if !supportLists]-->6.       <!--[endif]-->Run autoconfig.



O/S MIGRATION FROM RHEL 4 TO RHEL 5 FOR Oracle EBS 11i



O/S MIGRATION FROM RHEL 4 TO RHEL 5 FOR ORACLE EBS 11i
                                                                            
Source:     RHEL 4

Step 1.    Shutdown Application.
Step 2.    Shutdown Database.
Step 3.    Take Backup Db and Application.

                                                                                
Target:      RHEL5

Step 4.    Copy Db and Application from RHEL 4  to  RHEL 5.
Step 5.    Change owner and permission.
Step 6.    Create soft link.
            # cd /usr/lib
            # ln -s libstdc++-libc6.2-2.so.3 libstdc++-libc6.1-1.so.2
              
 Note: If soft link is not set than give error:-
               ERROR: Unable to set CLASSPATH
               /b02/db11i/db/oaproddb/9.2.0/appsutil/clone/bin/../jlib/classes111.zip: is missing
               In /tmp/adcfgclone.err
               error while loading shared libraries: libstdc++-libc6.1-1.so.2.

 Step  7.   Copy these file from RHEL4 to RHEL5 oratab, oraInst.loc and oraInventory.
                 and change ownership and permission.

 Step 8.   Below error appears while running the concurrent request
                       ar60run: relocation error: /jre11/lib/i686/native_threads/libjava.so: symbol __libc_wait,  
                       version GLIBC_2.0 not defined in file libc.so.6
              Check the following
                     $ rpm -qa|grep libcwait

               if not present please applying the following rpm
                     compat-libcwait-2.1-1.i386.rpm


Database:

Step 8.     Edit the <9i RDBMS_ORACLE_HOME>/appsutil/bin/adgetlnxver.sh file.
   #vi $ORACLE_HOME/appsutil/bin/adgetlnxver.sh

   Replace all occurrences of LD_ASSUME_KERNEL with XD_ASSUME_KERNEL.

Step 9.     Login to Db user

                           # su - oratest
                # vi .bash_profile
                         export KSH_VERSION='@(#)PD KSH v5.2.14 99/07/13.2'
                         PATH=/usr/kerberos/bin:/usr/local/bin:/bin:/usr/bin:/home/db11i/bin:/bin/ksh

Note: If .bash_profile is not set than give error:-
Checking for make...    found - /usr/bin/make
Checking for ld...     found - /usr/bin/ld
Checking for cc...     found - /usr/bin/cc
Checking for ar...     found - /usr/bin/ar
Checking for ksh...
Unable to find 'kshell' in path
Unable to locate all utilities with system path.

Step 10.  Login to Db user and runs adcfgclone.pl
  # su - oratest
              # perl adcfgclone.pl dbTier.


Application:

Step 11.  Login to application user
               # su - appltest
   # vi .bash_profile
export KSH_VERSION='@(#)PD KSH v5.2.14 99/07/13.2'

Step 12.  Edit the $AD_TOP/bin/adgetlnxver.sh file.
               # vi $AD_TOP/bin/adgetlnxver.sh

  Replace all occurrences of LD_ASSUME_KERNEL with XD_ASSUME_KERNEL.

Step 13.  Error while running the auto-config in appsTier
             [appl11i@demo DB11i_demo]$ ./adautocfg.sh
             /a01/appl11i/appl/oaprodora/iAS/Apache/perl/bin/perl: error while loading shared libraries:  

             libdb.so.3: cannot open shared object file: No such file or directory 

             Solution:- 
             ln -s /usr/lib/libdb.so.2 /usr/lib/libdb.so.3
Step 14.  Login App user and runs adcfgclone.pl
                # su - appltest
               # perl adcfgclone.pl appsTier.