AbdulWasiq: April 2019

Thursday 25 April 2019

All about Opatch

All about Opatch
+++++++++++++++
Opatch utility is used to apply database patches.
Opatch utility does not make changes to the database files (*.dbf) but make changes the binaries and libraries of $ORACLE_HOME.

Difference between opatch /adpatch
+++++++++++++++++++++++++++++++++

1)Opatch utility is used to apply database patches.
adpatch utility is used to apply application patches.

2)Pre reqsuites to apply application patches is database and database listener should be up and running.
When applying database patches database and database listener should be down.

3)Database patches can be rollback but we can't rollback application patches.

How to check opatch version
+++++++++++++++++++++++++++++
opatch version

Where OPatch is located
++++++++++++++++++++++++
$ORACLE_HOME/OPatch

EXPORT OPTACH UTILITY
+++++++++++++++++++++
export PATH=$PATH:$ORACLE_HOME/Opatch

How to go for help on opatch
++++++++++++++++++++++++++++
opatch -help

How to apply single database patch
++++++++++++++++++++++++++++++
opatch apply

How to apply multiple database patches
++++++++++++++++++++++++++++++++++++++
opatch napply

How to check a patch is applied to database
++++++++++++++++++++++++++++++++++++++++++
OPatch lsinventory |grep -i patch

How to check multiple patches applied to database in one command
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
opatch lsinventory|egrep -i 'patchno1|patchno2'

To check either patch can be apply online or not
--------------------------------------------
opatch query -is_online_patch 17230530

Check if the patch having any conflicts
================================
/d01/erpapp/patches/CPU_PATCHES/16902043
$ opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir ./

How to rollback a patch
+++++++++++++++++++++++++++++++++
$ opatch rollback -id 14153246

opatch rollback -id 17230530 -no_sysmod

Check whether the patch has been rolled back
++++++++++++++++++++++++++++++++++++++++++++
$ opatch lsinventory |grep 14153246

D. Cleanup Patch Storage to Reclaim Space
+++++++++++++++++++++++++++++++++++++++++++++++
OPatch can now determine and cleanup files in the patch storage that are no longer required. To do so run the following:
SYNTAX / EXAMPLE:

[oracle@lnx01] $ORACLE_HOME/OPatch/opatch util cleanup

B. List Detailed Information About a Patch Before Applying It
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
The OPatch utility can be used to verify details of a patch before proceeding to apply to the Oracle Home directory.
Such information that can verified but not limited to are:
• Check if patch is a Rolling Patch
• Check if patch is a Patchset Update (PSU)
• Check if patch can be run with “opatch auto” option
• Check OS platform the patch can be applied on
• Actual actions and/or steps that patch contains without applying the patch. This include detailed information of files it touches, copies and relinks.

SYNTAX:

$ORACLE_HOME/OPatch/opatch query -all |more

[oracle@lnx01] cd /home/oracle/download/patches/12311357
[oracle@lnx01] $ORACLE_HOME/OPatch/opatch query -all |more

How to determine if a patch is a "rolling patch" or not?
++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Based on the oracle database version we can determine whether a patch is a rolling patch or not.

- For oracle version 9i or 10gR1 issue,
$ opatch query -is_rolling

what is the pre requisite to apply database patches?
++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Database and database listener must be down.

RMAN Archive Log Delete

Archive Log List:

RMAN> list archivelog all;
RMAN> list copy of archivelog until time ‘SYSDATE-10′;
RMAN> list copy of archivelog from time ‘SYSDATE-10′
RMAN> list copy of archivelog from time ‘SYSDATE-10′ until time ‘SYSDATE-2′;
RMAN> list copy of archivelog from sequence 1100;
RMAN> list copy of archivelog until sequence 1300;
RMAN> list copy of archivelog from sequence 1000 until sequence 1500;

Archive Log Delete:

RMAN> delete archivelog all;
RMAN> delete archivelog until time ‘SYSDATE-10′;
RMAN> delete archivelog from time ‘SYSDATE-10′
RMAN> delete archivelog from time ‘SYSDATE-10′ until time ‘SYSDATE-2′;
RMAN> delete archivelog from sequence 1100;
RMAN> delete archivelog until sequence 1300;
RMAN> delete archivelog from sequence 1000 until sequence 1500;

select name from SYS.V_$ARCHIVED_LOG;
I use the following archive log delete command, and that solve my purpose:

RMAN> delete noprompt archivelog all completed before 'SYSDATE-3' backed up 01 times to device type disk;
RMAN> DELETE FORCE NOPROMPT OBSOLETE RECOVERY WINDOW OF 2 DAYS DEVICE TYPE DISK;
RMAN> DELETE FORCE NOPROMPT OBSOLETE REDUNDANCY = 2 DEVICE TYPE DISK;
RMAN> DELETE NOPROMPT FORCE ARCHIVELOG UNTIL TIME 'SYSDATE-1' DEVICE TYPE DISK;
RMAN> DELETE NOPROMPT FORCE ARCHIVELOG UNTIL TIME 'SYSDATE-1/2' DEVICE TYPE DISK;
RMAN> DELETE NOPROMPT FORCE ARCHIVELOG UNTIL SEQUENCE 260 DEVICE TYPE DISK;

How to determine if OACore JVM Sizing is Appropriate or Not

cd $LOG_HOME/ora/10.1.3/opmn/
ls -ltr default_group~oacore*

grep 'Full GC' default_group~oacore* | wc -l
grep 'Unloading class' default_group~oacore* | grep 'Full GC' | wc -l

If you see there are multiple cases when Full GC is happening then, it is understood that JVM sizing in not proper.

Take following actions for this:
1. Increase the Number of OACore JVMs
2. Increase the JVM Heap Size
3. Make sure you are not using many parallel thread for GC.

Flush Shared Pool and Buffer Cache

SQL> show user;
USER is "SYS"

SQL> alter system flush buffer_cache;
System altered.

SQL> alter system flush shared_pool;
System altered.

Wednesday 24 April 2019

Troubleshooting Oracle E Business Suite Workflow Mailer and Notification issues

1.Run below query

SELECT component_name, component_status FROM fnd_svc_components WHERE component_type = 'WF_MAILER';

SQL> SELECT component_name, component_status FROM fnd_svc_components WHERE component_type = 'WF_MAILER';

COMPONENT_NAME COMPONENT_STATUS
-------------------------------------------------------------------------------- ------------------------------
Workflow Notification Mailer RUNNING

SQL>

2. Check the log file of workflow mailer

Please run the following query to locate all current Workflow Mailer Service log:

set linesize 155;

set pagesize 200;

set verify off;

column MANAGER format a15;

column MEANING format a15;

SELECT concurrent_queue_name manager, fcp.last_update_date, fcp.concurrent_process_id pid, meaning, fcp.logfile_name

FROM fnd_concurrent_queues fcq, fnd_concurrent_processes fcp, fnd_lookups flkup

WHERE concurrent_queue_name in ('WFMLRSVC')

AND fcq.concurrent_queue_id = fcp.concurrent_queue_id

AND fcq.application_id = fcp.queue_application_id

AND flkup.lookup_code=fcp.process_status_code

AND lookup_type ='CP_PROCESS_STATUS_CODE'

AND meaning='Active';

SQL> @wfmailer_log.sql

MANAGER LAST_UPDA PID MEANING

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

LOGFILE_NAME

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

WFMLRSVC 24-APR-19 2714081 Active

/prodapps/inst/apps/PROD_abcapps/logs/appl/conc/log/FNDCPGSC2714081.txt

After setting app env file run the following command to collect the logs

grep ":ERROR:" $APPLCSF/$APPLLOG/FNDCPGSC2714081.txt > mlrerr.log

grep "Exception:" $APPLCSF/$APPLLOG/FNDCPGSC2714081.txt > mlrexc.log

grep ":UNEXPECTED:" $APPLCSF/$APPLLOG/FNDCPGSC2714081.txt > mlrunexp.log

3. Check If the notification for the user is going to discard folder

- First check, If the notifications approved through email are going to Discard folder for that user.

- Try to approve the notification from workflow worklist from Oracle EBS ERP and see if its going to Discard folder or processing successfully.

Please run below command to see notifications moved to Discard folder

grep ":DISCARD:" $APPLCSF/$APPLLOG/FNDCPGSC2714081.txt > mlrdiscard.log

grep "Approver:" $APPLCSF/$APPLLOG/FNDCPGSC2714081.txt > mlrapprover.log

4. Check below queries if the messages are in ready state

select NVL(substr(wfe.corrid,1,50),'NULL - No Value') corrid,

decode(wfe.state,

0,' 0 = Ready',

1,'1 = Delayed',

2,'2 = Retained',

3,'3 = Exception',

to_char(substr(wfe.state,1,12))) State,

count(*) COUNT

from applsys.wf_notification_out wfe

group by wfe.corrid, wfe.state;

CORRID STATE COUNT

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

APPS:POAPPRV:EMAIL_PO_PDF 3 = Exception 1

APPS:XXAIGCOS:APPROVED_FYI_MSG 2 = Retained 31

APPS:OEOH:ORDER_REJECTED 2 = Retained 1

APPS:REQAPPRV:PO_REQ_RETURNED_BY_BUYER_JRAD 2 = Retained 1

APPS:XXAIGCOS:APPROVAL_MESSAGE 2 = Retained 36

APPS:REQAPPRV:PO_REQ_APPROVED_JRAD 2 = Retained 32

APPS:FNDCMMSG:REQ_COMPLETION_W_URL 3 = Exception 2

APPS:OEOH:OM_APPROVED 2 = Retained 193

APPS:OEOH:APPROVAL_REQUIRED 2 = Retained 50

APPS:POAPPRV:PO_PO_APPROVE_PDF 2 = Retained 19

APPS:REQAPPRV:PO_REQ_APPROVE_JRAD 3 = Exception 2

APPS:POAPPRV:PO_PO_HAS_BEEN_APPROVE 2 = Retained 49

APPS:REQAPPRV:PO_REQ_APPROVE_JRAD 2 = Retained 53

APPS:WFERROR:RESET_ERROR_MESSAGE 2 = Retained 3

APPS:XXAIGCOS:POST_FYI_MESSAGE 2 = Retained 30

APPS:POAPPRV:PO_PO_HAS_BEEN_APPROVE 3 = Exception 1

SQL> select tab.msg_state, count(*) from applsys.aq$wf_notification_out tab group by tab.msg_state ;

MSG_STATE COUNT(*)

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

EXPIRED 6

PROCESSED 497

SQL> select count(*) from APPLSYS.AQ$WF_NOTIFICATION_OUT

where msg_state in ('READY','WAIT'); 2

COUNT(*)

----------

SQL>

Restart Oracle Physical Standby Database and MRP Process

Shutdown Physical Standby Database and MRP:

Please execute below on physical standby database to stop the Managed Recovery Process(MRP) and shutdown physical standby database:

# connect / as sysdba

STANDBY> alter database recover managed standby database cancel;

STANDBY> recover standby database until cancel;
auto

STANDBY> shutdown immediate

STANDBY> exit

Startup Physical Standby Database and MRP:

Please execute below commands on physical standby database to start physical standby database and the Managed Recovery Process(MRP) :

# connect / as sysdba

STANDBY> startup nomount

STANDBY> alter database mount standby database;

STANDBY> alter database recover managed standby database disconnect from session;

STANDBY> exit

Defer or Disable Log shipping on the Primary server:

If you want to Enable/Disable Archive Log shipping from Primary to Standby Server then execute below command on Primary Node

Defer or Disable Log shipping on the Primary server:

# connect / as sysdba

PRIMARY> ALTER SYSTEM SET log_archive_dest_state_2 = DEFER;

Enable Log shipping on the Primary server:

# connect / as sysdba

PRIMARY> ALTER SYSTEM SET log_archive_dest_state_2 = ENABLE;

Tuesday 23 April 2019

script to track oracle growth

select b.tsname tablespace_name , MAX(b.used_size_mb) cur_used_size_mb , round(AVG(inc_used_size_mb),2)avg_increas_mb from ( SELECT a.days,a.tsname , used_size_mb , used_size_mb - LAG (used_size_mb,1) OVER ( PARTITION BY a.tsname ORDER BY a.tsname,a.days) inc_used_size_mb
from ( SELECT TO_CHAR(sp.begin_interval_time,'MM-DD-YYYY') days ,ts.tsname ,MAX(round((tsu.tablespace_usedsize* dt.block_size )/(1024*1024),2)) used_size_mb
from dba_hist_tbspc_space_usage tsu , dba_hist_tablespace_stat ts ,dba_hist_snapshot sp, dba_tablespaces dt where tsu.tablespace_id= ts.ts# AND tsu.snap_id = sp.snap_id
AND ts.tsname = dt.tablespace_name AND sp.begin_interval_time > sysdate-7
GROUP BY TO_CHAR(sp.begin_interval_time,'MM-DD-YYYY'), ts.tsname
ORDER BY ts.tsname, days ) a
) b GROUP BY b.tsname ORDER BY b.tsname;

Extract pinging objects from database

We will take the backup of the frequently used objects before we bounce the database and push it back once it come alive.

select 'execute dbms_shared_pool.keep('''||owner||'.'||name||''','''||decode(type,'PACKAGE BODY','P','PACKAGE','P','FUNCTION','P','PROCEDURE','P','TRIGGER','R','TYPE','T','SEQUENCE','Q')||''');' FROM v$db_object_cache
WHERE type NOT IN ('NOT LOADED','NON-EXISTENT','VIEW','TABLE','SEQUENCE','INVALID TYPE','CURSOR')
AND loads>1 AND executions>loads AND executions>100 AND kept='NO'ORDER BY owner,namespace,type,executions desc;

How to find out the file system type in linux and solaris

You can determine a file system's type by using one of the following:

The FS type field in the virtual file system table (the /etc/vfstab file)

The /etc/default/fs file for local file systems

The /etc/dfs/fstypes file for NFS file systems

oracle@ctscq6810 $ cat /etc/default/fs
LOCAL=ufs

++++++++
Linux
+++++++

[oracle@ctscp5211 OPatch]$ df -T /persoad1/oracle
Filesystem Type 1K-blocks Used Available Use% Mounted on
/dev/mapper/vg_ctscp5211_persoad1_oracle-persoad1_oracle
ext3 41264448 30593920 8574400 79% /persoad1/oracle
[oracle@ctscp5211 OPatch]$ uname -a
Linux ctscp5211 2.6.32-400.33.3.el5uek #1 SMP Tue Nov 26 10:37:33 PST 2013 x86_64 x86_64 x86_64 GNU/Linux
[oracle@ctscp5211 OPatch]$

+++++++
solaris
+++++++

oracle@ctscq6810 $ df -n /persoaq1/oracle
/persoaq1/oracle : vxfs
oracle@ctscq6810 $

oracle@ctscq6810 $ df -n|grep -i /usr
/usr/nbadmin : nfs
oracle@ctscq6810 $ df -n|grep -i /persoaq1/oracle
/persoaq1/oracle : vxfs
oracle@ctscq6810 $

Disable and Enable RHEL7 firewall

To disable Firewall
systemctl disable firewall

To enable Firewall
systemctl enable firewall

vncserver: couldn't find "xauth" on your PATH.

Issue : - vncserver: couldn't find "xauth" on your PATH.

Solution :-

PATH=$PATH:/usr/X11/bin:/usr/openwin/bin
export PATH

Starting the VNC

$ vncserver :1

You will require a password to access your desktops.

Password:

Verify:

xauth: creating new authority file /home/applprod/.Xauthority

New 'ctssp:1 ()' desktop isctscp:1

Creating default startup script /home/applprod/.vnc/xstartup

Starting applications specified in /home/applprod/.vnc/xstartup

Log file is /home/applprod/.vnc/ctssp:1.log

$ vncserver :1

You will require a password to access your desktops.

Password:

Verify:

xauth: creating new authority file /home/applprod/.Xauthority

New 'aigappsrv:1 ()' desktop is ctssp:1.log

Creating default startup script /home/applprod/.vnc/xstartup

Starting applications specified in /home/applprod/.vnc/xstartup

Log file is /home/applprod/.vnc/ctssp:1.log:1.log

Change or Re set weblogic password in Oracle EBS Suite 12.2.0 or later

1. On the primary node, run the command:
$<ADMIN_SCRIPTS_HOME>/adstpall.sh -skipNM -skipAdmin

2. Change the Oracle WebLogic Server Administration User password by performing
the following steps on the run file system of the primary node.

1. Source the environment on the run file system.
2. Run the commands appropriate for your platform

appldev@ctssd:/backup/DEVAPP/fs1/inst/apps/DEV_ctssd/admin/scripts$ perl $FND_TOP/patch/115/bin/txkUpdateEBSDomain.pl -action=updateAdminPassword

Program: txkUpdateEBSDomain.pl started at Tue Aug 22 14:06:02 2017

AdminServer will be re started after changing WebLogic Admin Password
All Mid Tier services should be SHUTDOWN before changing WebLogic Admin Password
Confirm if all Mid Tier services are in SHUTDOWN state. Enter "Yes" to proceed or anything else to exit: yes

Enter the full path of Applications Context File [DEFAULT - /backup/DEVAPP/fs1/inst/apps/DEV_ctssd/appl/admin/DEV_ctssd.xml]:
Enter the WLS Admin Password:
Enter the new WLS Admin Password:
Enter the APPS user password:

Executing: /backup/DEVAPP/fs1/FMW_Home/webtier/perl/bin/perl /backup/DEVAPP/fs1/EBSapps/appl/ad/12.0.0/patch/115/bin/adProvisionEBS.pl ebs-get-serverstatus -contextfile=/backup/DEVAPP/fs1/inst/apps/DEV_ctssd/appl/admin/DEV_ctssd.xml -servername=AdminServer -promptmsg=hide -logfile=/backup/DEVAPP/fs1/inst/apps/DEV_ctssd/logs/appl/rgf/TXK/txkUpdateEBSDomain_Tue_Aug_22_14_06_02_2017/EBSProvisioner.log
Verifying if ssh is enabled for secondary nodes
Executing: /backup/DEVAPP/fs1/FMW_Home/webtier/perl/bin/perl /backup/DEVAPP/fs1/EBSapps/appl/ad/12.0.0/patch/115/bin/txkRunSSHSetup.pl verifyssh -contextfile=/backup/DEVAPP/fs1/inst/apps/DEV_ctssd/appl/admin/DEV_ctssd.xml -invalidnodefile=/backup/DEVAPP/fs1/inst/apps/DEV_ctssd/logs/appl/rgf/TXK/txkUpdateEBSDomain_Tue_Aug_22_14_06_02_2017/invalidnodefile.txt -hosts=
Log: /backup/DEVAPP/fs1/inst/apps/DEV_ctssd/logs/appl/rgf/TXK/verifyssh.log
Output: /backup/DEVAPP/fs1/inst/apps/DEV_ctssd/logs/appl/rgf/TXK/out.xml

Updating Weblogic Domain in online mode
---------------------------------------
Backup Location : /backup/DEVAPP/fs1/inst/apps/DEV_ctssd/logs/appl/rgf/TXK/txkUpdateEBSDomain_Tue_Aug_22_14_06_02_2017
Logfile Location: /backup/DEVAPP/fs1/inst/apps/DEV_ctssd/logs/appl/rgf/TXK/txkUpdateEBSDomain_Tue_Aug_22_14_06_02_2017/updateAdminPassword.log

Executing: /backup/DEVAPP/fs1/EBSapps/comn/util/jdk/jre/bin/java -classpath :/backup/DEVAPP/fs1/FMW_Home/wlserver_10.3/server/lib/weblogic.jar:/backup/DEVAPP/fs1/FMW_Home/Oracle_EBS-app1/oui/jlib/srvm.jar:/backup/DEVAPP/fs1/EBSapps/comn/java/classes:/backup/DEVAPP/fs1/EBSapps/comn/shared-libs/ebs-3rdparty/WEB-INF/lib/ebs3rdpartyManifest.jar:/backup/DEVAPP/fs1/FMW_Home/Oracle_EBS-app1/shared-libs/ebs-appsborg/WEB-INF/lib/ebsAppsborgManifest.jar oracle.apps.ad.util.UpdateEBSDomain updateAdminPassword -contextfile /backup/DEVAPP/fs1/inst/apps/DEV_ctssd/appl/admin/DEV_ctssd.xml -promptmsg hide -logdir /backup/DEVAPP/fs1/inst/apps/DEV_ctssd/logs/appl/rgf/TXK/txkUpdateEBSDomain_Tue_Aug_22_14_06_02_2017 -logfile /backup/DEVAPP/fs1/inst/apps/DEV_ctssd/logs/appl/rgf/TXK/txkUpdateEBSDomain_Tue_Aug_22_14_06_02_2017/updateAdminPassword.log
appldev@ctssd:/backup/DEVAPP/fs1/inst/apps/DEV_ctssd/admin/scripts$

Workflow Notification Mailer Configuration Setup

Configuration for Workflow notification mailer setup in OracleApplications

Go through below link

https://bhanuchander.wordpress.com/2010/02/01/workflow-notification-mailer-configuration-setup/

Mailer down with stopped Error in EBS suite.

http://dbatips.jcltek.com/appsr12/workflow/troubleshoot/mailerdown/

Checking the status of Workflow Mailer and other Workflow Components

Today I am going to post some handy scripts which you can use to run a quick status check on Oracle Apps Workflow mailer and other workflow components:

1. Workflow Mailer: Run the following scripts as apps:

set linesize 100
col Component format a40
SELECT component_name as Component, component_status as Status FROM fnd_svc_components WHERE component_type = 'WF_MAILER';

SQL> set linesize 100
col Component format a40
SELECT component_name as Component, component_status as Status FROM fnd_svc_components WHERE component_type = 'WF_MAILER';SQL> SQL>

COMPONENT STATUS
---------------------------------------- ------------------------------
Workflow Notification Mailer RUNNING

SQL>

set linesize 150
set pagesize 9999
col COMPONENT_NAME format a50
col COMPONENT_STATUS format a50
select SC.COMPONENT_TYPE, SC.COMPONENT_NAME, FND_SVC_COMPONENT.Get_Component_Status(SC.COMPONENT_NAME) COMPONENT_STATUS from FND_SVC_COMPONENTS SC order by 1, 2;

SQL> set linesize 150
SQL> set pagesize 9999
SQL> col COMPONENT_NAME format a50
SQL> col COMPONENT_STATUS format a50
select SC.COMPONENT_TYPE, SC.COMPONENT_NAME, FND_SVC_COMPONENT.Get_Component_Status(SC.COMPONENT_NAME) COMPONENT_STATUS from FND_SVC_COMPONENTS SC order by 1, 2;SQL>

COMPONENT_TYPE COMPONENT_NAME COMPONENT_STATUS
------------------------------ -------------------------------------------------- --------------------------------------------------
WF_AGENT_LISTENER ECX Inbound Agent Listener STOPPED
WF_AGENT_LISTENER ECX Transaction Agent Listener STOPPED
WF_AGENT_LISTENER Workflow Deferred Agent Listener RUNNING
WF_AGENT_LISTENER Workflow Deferred Notification Agent Listener DEACTIVATED_USER
WF_AGENT_LISTENER Workflow Error Agent Listener RUNNING
WF_AGENT_LISTENER Workflow Inbound JMS Agent Listener STOPPED
WF_AGENT_LISTENER Workflow Inbound Notifications Agent Listener RUNNING
WF_DOCUMENT_WEB_SERVICES Web Services OUT Agent STOPPED
WF_JAVA_AGENT_LISTENER WF_JMS_IN Listener(M4U) RUNNING
WF_JAVA_AGENT_LISTENER Web Services IN Agent STOPPED
WF_JAVA_AGENT_LISTENER Workflow Java Deferred Agent Listener RUNNING
WF_JAVA_AGENT_LISTENER Workflow Java Error Agent Listener RUNNING
WF_MAILER Workflow Notification Mailer RUNNING

13 rows selected.

SQL>

Monday 22 April 2019

How to change APPLSYS password in ORACLE APPLICATION 12.2.0 or later?

Steps to change the APPS, APPLSYS, and APPS_NE password using FNDCPASS or AFPASSWD for EBS 12.2 (Doc ID 1674462.1)

Important: These steps must be carried out on the run file system.

sh $INST_TOP/admin/scripts/adstpall.sh

j Change the APPLSYS password

R Run autoconfig with the newly changed password.

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

5. Change the "apps" password in WLS Datasource as follows:

a. Log in to WLS Administration Console.

http://test.example.com:7003/console

b. 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.

f. Enter the new password in the "Password" field.

g. Enter the new password in the "Confirm Password" field.

h. Click Save.

6. Start all the application tier services using the below script

$INST_TOP/admin/scripts/adstrtal.sh

libmawt.so: libXtst.so.6: cannot open shared object file: No such file or directory

Issue : When running rapidwiz thrown below error message.

rapidwiz]# Exception in thread "main" java.lang.UnsatisfiedLinkError: /u01/ebs_12.2.0/startCD/Disk1/rapidwiz/jre/Linux_x64/1.6.0/lib/i386/xawt/libmawt.so: libXtst.so.6: cannot open shared object file: No such file or directory
at java.lang.ClassLoader$NativeLibrary.load(Native Method)
at java.lang.ClassLoader.loadLibrary0(Unknown Source)
at java.lang.ClassLoader.loadLibrary(Unknown Source)
at java.lang.Runtime.load0(Unknown Source)
at java.lang.System.load(Unknown Source)
at java.lang.ClassLoader$NativeLibrary.load(Native Method)
at java.lang.ClassLoader.loadLibrary0(Unknown Source)
at java.lang.ClassLoader.loadLibrary(Unknown Source)
at java.lang.Runtime.loadLibrary0(Unknown Source)
at java.lang.System.loadLibrary(Unknown Source)
at sun.security.action.LoadLibraryAction.run(Unknown Source)
at java.security.AccessController.doPrivileged(Native Method)
at sun.awt.NativeLibLoader.loadLibraries(Unknown Source)
at sun.awt.DebugHelper.<clinit>(Unknown Source)
at java.awt.Component.<clinit>(Unknown Source)
Could not find the main class: oracle.apps.ad.rapidwiz.RIWizard. Program will exit.

Solution :

[root@ebsdbdev01 ~]# yum install libXtst.i686
Loaded plugins: refresh-packagekit, security, ulninfo
Setting up Install Process
Resolving Dependencies
--> Running transaction check
---> Package libXtst.i686 0:1.2.2-2.1.el6 will be installed
--> Processing Dependency: libXi.so.6 for package: libXtst-1.2.2-2.1.el6.i686
--> Running transaction check
---> Package libXi.i686 0:1.7.2-2.2.el6 will be installed
--> Finished Dependency Resolution

Dependencies Resolved

=======================================================================================================================================================================================
Package Arch Version Repository Size
=======================================================================================================================================================================================
Installing:
libXtst i686 1.2.2-2.1.el6 public_ol6_latest 18 k
Installing for dependencies:
libXi i686 1.7.2-2.2.el6 public_ol6_latest 36 k

Transaction Summary
=======================================================================================================================================================================================
Install 2 Package(s)

Total download size: 54 k
Installed size: 86 k
Is this ok [y/N]: y
Downloading Packages:
(1/2): libXi-1.7.2-2.2.el6.i686.rpm | 36 kB 00:00
(2/2): libXtst-1.2.2-2.1.el6.i686.rpm | 18 kB 00:00
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Total 43 kB/s | 54 kB 00:01
Running rpm_check_debug
Running Transaction Test
Transaction Test Succeeded
Running Transaction
Installing : libXi-1.7.2-2.2.el6.i686 1/2
Installing : libXtst-1.2.2-2.1.el6.i686 2/2
Verifying : libXi-1.7.2-2.2.el6.i686 1/2
Verifying : libXtst-1.2.2-2.1.el6.i686 2/2

Installed:
libXtst.i686 0:1.2.2-2.1.el6

Dependency Installed:
libXi.i686 0:1.7.2-2.2.el6

Complete!
[root@ebsdbdev01 ~]# find / -iname libXtst.so.6
/usr/X11R6/lib/libXtst.so.6
/usr/lib64/libXtst.so.6
/usr/lib/libXtst.so.6
[root@ebsdbdev01 ~]#

when trying to login to EBS suite with sysadmin the same page shows again

Issue

Solution : When i checked the alert log file FND_LOGIN table in tablespace APPS_TS_ARCHIVE has left no space.

Incremental checkpoint up to RBA [0x587.5007a.0], current log tail at RBA [0x587.507bb.0]
Thu Mar 31 19:10:27 2016
ORA-1654: unable to extend index APPLSYS.FND_LOGINS_U1 by 128 in tablespace APPS_TS_ARCHIVE
ORA-1654: unable to extend index APPLSYS.FND_LOGINS_U1 by 128 in tablespace APPS_TS_ARCHIVE
Thu Mar 31 19:10:47 2016
ORA-1654: unable to extend index APPLSYS.FND_LOGINS_U1 by 128 in tablespace APPS_TS_ARCHIVE
ORA-1654: unable to extend index APPLSYS.FND_LOGINS_U1 by 128 in tablespace APPS_TS_ARCHIVE
Thu Mar 31 19:13:08 2016
ORA-1654: unable to extend index APPLSYS.FND_LOGINS_U1 by 128 in tablespace APPS_TS_ARCHIVE
ORA-1654: unable to extend index APPLSYS.FND_LOGINS_U1 by 128 in tablespace APPS_TS_ARCHIVE
Thu Mar 31 19:20:28 2016
ORA-1654: unable to extend index APPLSYS.FND_LOGINS_U1 by 128 in tablespace APPS_TS_ARCHIVE
ORA-1654: unable to extend index APPLSYS.FND_LOGINS_U1 by 128 in tablespace APPS_TS_ARCHIVE

Move data files to ASM from cook Filesystem /unix file system

set lines 200
set pages 50
set feed off
set head off
spool /tmp/move_dbfiles.sql
select 'ALTER DATABASE MOVE DATAFILE '''||name||''' TO ''+DATA'';' from v$datafile order by con_id;

set lines 200
set pages 50
set feed off
set head off
spool /tmp/move_dbfiles.sql
select 'ALTER DATABASE MOVE DATAFILE '''||name||''' TO ''+DATA'';' from v$datafile order by con_id;

ALTER DATABASE MOVE DATAFILE '/u01/oracle/oradata/BIDEV/system01.dbf' TO '+DATA';
ALTER DATABASE MOVE DATAFILE '/u02/oracle/oradata/BIDEV/DEV_biplatform02.dbf' TO '+DATA';
ALTER DATABASE MOVE DATAFILE '/u01/oracle/oradata/BIDEV/sysaux01.dbf' TO '+DATA';
ALTER DATABASE MOVE DATAFILE '/u01/oracle/oradata/BIDEV/undotbs01.dbf' TO '+DATA';
ALTER DATABASE MOVE DATAFILE '/u01/oracle/oradata/BIDEV/DEV_mds.dbf' TO '+DATA';
ALTER DATABASE MOVE DATAFILE '/u01/oracle/oradata/BIDEV/users01.dbf' TO '+DATA';
ALTER DATABASE MOVE DATAFILE '/u01/oracle/oradata/BIDEV/DEV_dwstage.dbf' TO '+DATA';
ALTER DATABASE MOVE DATAFILE '/u01/oracle/oradata/BIDEV/DEV_odi.dbf' TO '+DATA';
ALTER DATABASE MOVE DATAFILE '/u01/oracle/oradata/BIDEV/DEV_dwindex.dbf' TO '+DATA';
ALTER DATABASE MOVE DATAFILE '/u02/oracle/oradata/BIDEV/DEV_dwdata.dbf' TO '+DATA';
ALTER DATABASE MOVE DATAFILE '/u01/oracle/oradata/BIDEV/DEV_biacomp.dbf' TO '+DATA';
ALTER DATABASE MOVE DATAFILE '/u02/oracle/oradata/BIDEV/system02.dbf' TO '+DATA';
ALTER DATABASE MOVE DATAFILE '/u02/oracle/oradata/BIDEV/sysaux02.dbf' TO '+DATA';
ALTER DATABASE MOVE DATAFILE '/u02/oracle/oradata/BIDEV/DEV_mds02.dbf' TO '+DATA';
ALTER DATABASE MOVE DATAFILE '/u02/oracle/oradata/BIDEV/DEV_dwdata02.dbf' TO '+DATA';
ALTER DATABASE MOVE DATAFILE '/u02/oracle/oradata/BIDEV/DEV_biacomp02.dbf' TO '+DATA';
ALTER DATABASE MOVE DATAFILE '/u02/oracle/oradata/BIDEV/DEV_dwstage02.dbf' TO '+DATA';
ALTER DATABASE MOVE DATAFILE '/u02/oracle/oradata/BIDEV/DEV_odi02.dbf' TO '+DATA';
ALTER DATABASE MOVE DATAFILE '/u01/oracle/oradata/BIDEV/DEV_biplatform.dbf' TO '+DATA';
SQL>

SQL> select count(*) from dba_data_files;

19
SQL>

SQL> select count(member) from v$logfile;

3
SQL>
SQL> select count(name) from v$controlfile;

1
SQL>

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u01/oracle/oradata/BIDEV/system01.dbf
/u01/oracle/oradata/BIDEV/DEV_biplatform.dbf
/u01/oracle/oradata/BIDEV/sysaux01.dbf
/u01/oracle/oradata/BIDEV/undotbs01.dbf
/u01/oracle/oradata/BIDEV/DEV_mds.dbf
/u01/oracle/oradata/BIDEV/users01.dbf
/u01/oracle/oradata/BIDEV/DEV_dwstage.dbf
/u01/oracle/oradata/BIDEV/DEV_odi.dbf
/u01/oracle/oradata/BIDEV/DEV_dwindex.dbf
/u02/oracle/oradata/BIDEV/DEV_dwdata.dbf
/u01/oracle/oradata/BIDEV/DEV_biacomp.dbf

NAME
--------------------------------------------------------------------------------
/u02/oracle/oradata/BIDEV/system02.dbf
/u02/oracle/oradata/BIDEV/sysaux02.dbf
/u02/oracle/oradata/BIDEV/DEV_mds02.dbf
/u02/oracle/oradata/BIDEV/DEV_dwdata02.dbf
/u02/oracle/oradata/BIDEV/DEV_biacomp02.dbf
/u02/oracle/oradata/BIDEV/DEV_dwstage02.dbf
/u02/oracle/oradata/BIDEV/DEV_odi02.dbf
/u02/oracle/oradata/BIDEV/DEV_biplatform02.dbf

19 rows selected.

SQL> select member from v$logfile;

MEMBER
--------------------------------------------------------------------------------
/u01/oracle/oradata/BIDEV/redo03.log
/u01/oracle/oradata/BIDEV/redo02.log
/u01/oracle/oradata/BIDEV/redo01.log

SQL>

Exception occurred: java.sql.SQLException: ORA-28040: No matching authentication protocol

Issue :

Exception occurred while preseeding variables in the context file: java.sql.SQLException: Could not get connection to the database

StackTrace:

java.sql.SQLException: Could not get connection to the database

at oracle.apps.ad.tools.configuration.CVMHelper.processNewContextFile(CVMHelper.java:887)

at oracle.apps.ad.context.CtxValueMgt.preSeed2Ctx(CtxValueMgt.java:1815)

at oracle.apps.ad.context.CtxValueMgt.processCtxFile(CtxValueMgt.java:1610)

at oracle.apps.ad.clone.ApplyApplTop.runCVM(ApplyApplTop.java:510)

at oracle.apps.ad.clone.ApplyApplTop.runAutoConfig(ApplyApplTop.java:552)

at oracle.apps.ad.clone.ApplyApplTop.doConf(ApplyApplTop.java:339)

at oracle.apps.ad.clone.ApplyApplTop.doApply(ApplyApplTop.java:382)

at oracle.apps.ad.clone.ApplyApplTop.<init>(ApplyApplTop.java:267)

at oracle.apps.ad.clone.ApplyAppsTier.<init>(ApplyAppsTier.java:105)

at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)

at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39)

at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)

at java.lang.reflect.Constructor.newInstance(Constructor.java:494)

at oracle.apps.ad.clone.util.CloneProcessor.run(CloneProcessor.java:67)

at java.lang.Thread.run(Thread.java:595)

Solution :

Got above issue when running perl adcfgclone.pl appsTier

/u03/TEST/inst/apps/TEST_CTSST/ora/10.1.2/network/admin

$ cat sqlnet.ora

SQLNET.ALLOWED_LOGON_VERSION=8

ebs suite 12.1.3 cold backup /Tape backup clone procedure

Few of the customer in Middle-east uses the tape to backup the database and application binaries. whenever they need clone just they restore the backup from tape to the target server.

Here is the high level steps to do the clone of ERP from tape backup.

1) Drop the database

2) Database and binaries will be restore from Tape

3)Startup nomount

4)Take backup of controlfile from production and transfer it to target database

Production

alter database backup controlfile to trace as '/u01/backup/ctrl.sql';

Transfer to clone to server.

Copy the control file script

cp -pr ctrl.sql control_7july2018.sql

open the file "control_7july2018.sql" and check datafile path , controlfile path ,set dbname and create the controlfile.

SQL> @control_7july2018.sql

Control file created.

SQL> select open_mode from v$database;

OPEN_MODE

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

MOUNTED

SQL> alter database open resetlogs;

Database altered.

SQL> SQL> select name,created from v$database;

NAME CREATED

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

DEV 07-JUL-18

SQL>

Application Cloning

$ perl adcfgclone.pl appsTier

Redwood Shores, California, USA

Oracle Applications Rapid Clone

Version 12.0.0

adcfgclone Version 120.31.12010000.12

Enter the APPS password :

Running:

/u03/dev/apps/apps_st/comn/clone/bin/../jre/bin/java -Xmx600M -cp /u03/dev/apps/apps_st/comn/clone/jlib/java:/u03/dev/apps/apps_st/comn/clone/jlib/xmlparserv2.jar:/u03/dev/apps/apps_st/comn/clone/jlib/ojdbc14.jar oracle.apps.ad.context.CloneContext -e /u03/dev/apps/apps_st/comn/clone/bin/../context/apps/CTXORIG.xml -validate -pairsfile /tmp/adpairsfile_3652.lst -stage /u03/dev/apps/apps_st/comn/clone 2> /tmp/adcfgclone_3652.err; echo $? > /tmp/adcfgclone_3652.res

Log file located at /u03/dev/apps/apps_st/comn/clone/bin/CloneContext_0924114239.log

Provide the values required for creation of the new APPL_TOP Context file.

Target System Hostname (virtual or normal) [ctssd] :

Target System Domain Name : abc.com.bh

Target System Database SID : DEV

Target System Database Server Node [ctssd] :

Target System Database Domain Name [abc.com.bh] :

Target System Base Directory : /u03/dev

Target System Tools ORACLE_HOME Directory [/u03/dev/apps/tech_st/10.1.2] :

Target System Web ORACLE_HOME Directory [/u03/dev/apps/tech_st/10.1.3] :

Target System APPL_TOP Directory [/u03/dev/apps/apps_st/appl] :

Target System COMMON_TOP Directory [/u03/dev/apps/apps_st/comn] :

Target System Instance Home Directory [/u03/dev/inst] :

Username for the Applications File System Owner [appldev] :

Target System Root Service [enabled] :

Target System Web Entry Point Services [enabled] :

Target System Web Application Services [enabled] :

Target System Batch Processing Services [enabled] :

Target System Other Services [No] : YES

Do you want to preserve the Display [ykapps:0.0] (y/n) : n

Target System Display [ctssd:0.0] :

Do you want the the target system to have the same port values as the source system (y/n) [y] ? : n

Target System Port Pool [0-99] : 10

Checking the port pool 10

done: Port Pool 10 is free

Report file located at /u03/dev/inst/apps/DEV_ctssd/temp/portpool.lst

Complete port information available at /u03/dev/inst/apps/DEV_ctssd/temp/portpool.lst

UTL_FILE_DIR on database tier consists of the following directories.

1. /usr/tmp

2. /usr/tmp

3. /u21/oradb/db/tech_st/11.2.0/appsutil/outbound/DEV_ykr12erp

4. /usr/tmp

Choose a value which will be set as APPLPTMP value on the target node [1] : 1

Backing up /u03/dev/inst/apps/DEV_ctssd/appl/admin/DEV_ctssd.xml to /u03/dev/inst/apps/DEV_ctssd/appl/admin/DEV_ctssd.xml.bak

Creating the new APPL_TOP Context file from :

/u03/dev/apps/apps_st/appl/ad/12.0.0/admin/template/custom/adxmlctx.tmp

The new APPL_TOP context file has been created :

/u03/dev/inst/apps/DEV_ctssd/appl/admin/DEV_ctssd.xml

Log file located at /u03/dev/apps/apps_st/comn/clone/bin/CloneContext_0924114239.log

Check Clone Context logfile /u03/dev/apps/apps_st/comn/clone/bin/CloneContext_0924114239.log for details.

Running Rapid Clone with command:

perl /u03/dev/apps/apps_st/comn/clone/bin/adclone.pl java=/u03/dev/apps/apps_st/comn/clone/bin/../jre mode=apply stage=/u03/dev/apps/apps_st/comn/clone component=appsTier method=CUSTOM appctxtg=/u03/dev/inst/apps/DEV_ctssd/appl/admin/DEV_ctssd.xml showProgress contextValidated=true

Running:

APPS Password :

Beginning application tier Apply - Sun Sep 24 11:44:08 2017

/u03/dev/apps/apps_st/comn/clone/bin/../jre/bin/java -Xmx600M -DCONTEXT_VALIDATED=true -Doracle.installer.oui_loc=/oui -classpath /u03/dev/apps/apps_st/comn/clone/jlib/xmlparserv2.jar:/u03/dev/apps/apps_st/comn/clone/jlib/ojdbc14.jar:/u03/dev/apps/apps_st/comn/clone/jlib/java:/u03/dev/apps/apps_st/comn/clone/jlib/oui/OraInstaller.jar:/u03/dev/apps/apps_st/comn/clone/jlib/oui/ewt3.jar:/u03/dev/apps/apps_st/comn/clone/jlib/oui/share.jar:/u03/dev/apps/apps_st/comn/clone/jlib/oui/srvm.jar:/u03/dev/apps/apps_st/comn/clone/jlib/ojmisc.jar oracle.apps.ad.clone.ApplyAppsTier -e /u03/dev/inst/apps/DEV_ctssd/appl/admin/DEV_ctssd.xml -stage /u03/dev/apps/apps_st/comn/clone -showProgress

APPS Password : Log file located at /u03/dev/inst/apps/DEV_ctssd/admin/log/ApplyAppsTier_09241144.log

- 73% completed

Completed Apply...

Sun Sep 24 12:06:39 2017

Do you want to startup the Application Services for DEV? (y/n) [y] : n

Services not started

Pages

Thursday 25 April 2019

Wednesday 24 April 2019

Tuesday 23 April 2019

Monday 22 April 2019