Friday, July 20, 2018

How to remove and install Oracle XDB

XDB Removal

SQL> spool xdb_removal.log
SQL> set echo on;
SQL> connect / as sysdba
SQL> shutdown immediate;
SQL> startup
SQL> @?/rdbms/admin/catnoqm.sql
SQL> spool off;


XDB Install

SQL> spool xdb_install.log
SQL> set echo on;
SQL> connect / as sysdba
SQL> shutdown immediate;
SQL> startup;
SQL> @?/rdbms/admin/catqm.sql XDB XDB TEMP YES
SQL> @?/rdbms/admin/utlrp.sql
SQL> spool off

Oracle Applications Cloning - EBS R12.2

MOS Note: Cloning Oracle E-Business Suite Release 12.2 with Rapid Clone (Doc ID 1383621.1)

Pre-requisite Tasks
Run AutoConfig on the application tier
sh $ADMIN_SCRIPTS_HOME/adautocfg.sh

Run AutoConfig on the database tier
cd $ORACLE_HOME/appsutil/scripts
sh adautocfg.sh


Prepare the Source System database tier for cloning
1. Log on to the Source System as the oracle user.
2. Source the Database tier environment file.
3. Execute the following commands:

$ cd /appsutil/scripts/
$ perl adpreclone.pl dbTier

Prepare the Source System application tier for cloning
1. Log on to the primary node of the Source System as the applmgr user.
2. Source the environment file of the Run Edition File system.
You can use the following command to confirm that the environment variable FILE_EDITION points to the Run Edition File System:

$ echo $FILE_EDITION
It should return the value: run

3. Execute the following commands:
$ cd /admin/scripts
$ perl adpreclone.pl appsTier

Database Tier Clone 

Copy the database node file system
1. Copy the database (.dbf) files from the Source System to the Target System.
2. Copy the redo log files from the Source System to the Target System
3. Copy the Source System database ORACLE_HOME to the Target System

Configure the Target System database server
Log on to the Target System as the oracle user and enter the following commands:

$ cd /appsutil/clone/bin
$ perl adcfgclone.pl dbTier


Application Tier Clone
Copy the application tier file system from the Source to the Target "Run Edition File System"

Log on to Run Edition File System in the Source System application tier nodes as the applmgr user.
Copy the following application tier directories from the Source Node to the Target Run Edition File System application tier node:





cd $COMMON_TOP/clone/bin  or if it doesn’t exit go to the FS1.

mv FMW_Home FMW_Home_bak on both FS.
Verify /etc/oraInst.loc  points to correct folder

Configure the Target System application tier server nodes
Log on to the Run Edition File System in the Target System as the applmgr user and enter the following commands:
$ cd /clone/bin
$ perl adcfgclone.pl appsTier dualfs

Select No for starting up the services.

Finishing Tasks

update the profile values. replace source information with target details.

Change apps/sysadmin password
FNDCPASS apps/apps 0 Y system/manager SYSTEM APPLSYS newpsswd
FNDCPASS apps/apps 0 Y system/manager USER SYSADMIN newpsswd

Note: if you change apps password, then perform the below:

Start AdminServer using the $INST_TOP/admin/scripts/adadminsrvctl.sh script. Do not start any other application tier services.

Change the “apps” password in WLS Datasource as follows:
Log in to WLS Administration Console.

Click Lock & Edit in Change Center.
In the Domain Structure tree, expand Services, then select Data Sources
On the “Summary of JDBC Data Sources” page, select EBSDataSource
On the “Settings for EBSDataSource” page, select the Connection Pool tab
Enter the new password in the “Password” field.
Enter the new password in the “Confirm Password” field.
Click Save.
Click Activate Changes in Change Center.

Check whether you are able to connect to db using apps
sqlplus apps/ from application tier.

Before Starting all the services. Run Autoconfig.

cd $ADMIN_SCRIPTS_HOME
sh adautocfg.sh

Now start the services

Perform Sanity check

List oracle tables with stale stats


SQL> set pagesize 500 linesize 200
SQL> select owner, table_name, num_rows from dba_tab_statistics where stale_stats = 'YES' order by owner, table_name;

Tuning Concurrent Manager

Tuning the Concurrent Manager

• 1359612.1 Webcast 'E-Business Suite - RAC & Parallel Concurrent Processing
• 1367676.1 Webcast 'E-Business Suite - Concurrent Manager Performance
• 164085.1 Enhancing and Automating Oracle Applications Concurrent Processing
• 1057802.1 Best Practices for Performance for Concurrent Managers
• 1304305.1 E-Business Concurrent Processing Information Center

How to disable OCM scheduled job if you are not using this product


Error:
ORA-12012: error on auto execute of job "ORACLE_OCM"."MGMT_CONFIG_JOB_2_1"

SQL> exec dbms_scheduler.disable(‘ORACLE_OCM.MGMT_CONFIG_JOB’);

PL/SQL procedure successfully completed.

SQL> exec dbms_scheduler.disable(‘ORACLE_OCM.MGMT_STATS_CONFIG_JOB’)

PL/SQL procedure successfully completed.

Oracle CPU patches applied list using opatch


$ORACLE_HOME/OPatch/opatch lsinventory -bugs_fixed | grep MOLECULE

20506699   20449729  Tue Jul 07 12:26:29 GMT 2015   DB-11.2.0.4-MOLECULE-020-CPUAPR2015
20506706   20449729  Tue Jul 07 12:26:29 GMT 2015   DB-11.2.0.4-MOLECULE-021-CPUAPR2015



Identify EBS profile option settings that may be related to performance-degrading debug/trace activity

-- check_ebs_trace_profs.sql
--  Author: John Piwowar
-- Purpose: Identify E-Business Suite system profile option settings that may
--          be related to performance-degrading debug/trace activity
--   Notes: Prompts for a cutoff date for when profile options were set
--          May need additional tweaking for multi-language installations 
set pagesize 9999
set linesize 120
set verify off
 col "Profile Option" for a25
 col "Option Level" for a13
 col "set for" for a20
 col "Value" for a20
 col "Set On" for a11
 col "Blame" for a20
 PROMPT Enter date value in form DD-MON-YYYY for check_since
    select tl.user_profile_option_name "Profile Option"
         , decode( val.level_id
                 , 10001, 'Site'
                 , 10002, 'Application'
                 , 10003, 'Responsibility'
                 , 10004, 'User'
                 , 10005, 'Server'
                 , 10006, 'Organization'
                 , 10007, 'Server+Resp'
                 , 'No idea, boss') "Option Level"
         , decode( val.level_id
                 , 10001
                 , 'EVERYWHERE!'
                 , 10002
                 , (select application_name
                      from fnd_application_tl
                     where application_id = val.level_value)
                 , 10003
                 , (select responsibility_name
                      from fnd_responsibility_tl
                     where responsibility_id = val.level_value
                       and application_id = val.level_value_application_id)
                 , 10004
                 , (select user_name
                      from fnd_user
                     where user_id = val.level_value)
                 , 10005
                 , (select host || '.' || domain
                      from fnd_nodes
                     where node_id = val.level_value)
                 , 10006
                 , (select name
                      from hr_all_organization_units
                     where organization_id = val.level_value)
                 , 10007
                 , 'Look it up' --per specification El-Ay-Zed-why
                 , '''Tis a mystery') "Set for"
         , val.profile_option_value "Value"
         , val.last_update_date "Set on"
         , usr.user_name "Set By"
      from fnd_profile_options opt,
           fnd_profile_option_values val,
           fnd_profile_options_tl tl,
           fnd_user usr
     where opt.profile_option_id = val.profile_option_id
       and opt.profile_option_name = tl.profile_option_name
       and regexp_like( tl.user_profile_option_name
                      , '(trace|log|debug|audit|diag|sql)'
                      , 'i'
                      )
       and not(regexp_like( tl.user_profile_option_name
                          , '(catalog|file|login|utilities)'
                          , 'i'
                          )
              )
       and usr.user_id = val.last_updated_by
       and usr.user_name  not in ( 'AUTOINSTALL'
                                 , 'INITIAL SETUP'
                                 , 'ANONYMOUS')
       and val.last_update_date > '&check_since'
     order by val.last_update_date desc
 ;