Knowledge rests
not upon truth alone,
but upon error also.
– Carl Jung
Cloning a OAM enabled EBS 12.2 environment
This article assumes assumes that cloning to target system from source system has been completed and integration of cloned target instance with OID (Oracle Internet Directory) has already been performed.
High Level Steps:
- Delink all Oracle E-Business Suite users that were linked to the original Oracle Internet Directory users
- On the target system, start only the Admin Server
- Deregister the cloned Oracle E-Business Suite from Oracle Access Manager
- Remove Oracle E-Business Suite AccessGate from the cloned Oracle E-Business Suite instance.
- Integrate EBS 12.2 again with Oracle Access Manager using note 1576425.1
Detailed steps:
Delink all Oracle E-Business Suite users that were linked to the original Oracle Internet Directory users
The SQL script $FND_TOP/patch/115/sql/fndssouu.sql can be used by system administrators to unlink the user’s GUID information from an FND_USER account. This script removes the link between the Oracle E-Business Suite and Oracle Internet Directory.
As the APPS user, run the script to unlink all Oracle E-Business Suite users:
SQL>@$FND_TOP/patch/115/sql/fndssouu.sql %
Deregister the cloned Oracle E-Business Suite from Oracle Access Manager
Execute the following command to deregister Oracle E-Business Suite from Oracle Access Manager.
$ txkrun.pl -script=SetOAMReg -deregisteroam=yes
The script will now perform the following main tasks automatically:
- Deregister Oracle E-Business Suite AccessGate with Oracle Access Manager.
- Disable WebGate in your Oracle E-Business Suite webtier.
- Clear Oracle E-Business Suite profile options Application Authenticate Agent (APPS_AUTH_AGENT) and Applications SSO Type (APPS_SSO) to switch back to local login. If you registered the instance with -ebsProfileLevel=Site (default), deregistration will clear the profiles at SITE level. If you registered the instance with -ebsProfileLevel=Server, deregistration will clear the profiles at SERVER level
The script must complete successfully. Review the log files for any error messages.
The script will not automatically delete the following entries, as you may have also used these for other partner applications:
- Authentication Scheme (by default named EBSAuthScheme)
- Authentication Module (by default named LDAP_EBS)
- Identity Store (by default named OIDIdentityStore)
Remove Oracle E-Business Suite AccessGate from the cloned Oracle E-Business Suite instance.
1)
Delete your Oracle E-Business Suite AccessGate using your WebLogic Administration Console
Delete Oracle E-Business Suite AccessGate deployment using your WebLogic Administration Console
http://ebshost.example.com:7001/console
In the WebLogic Administration Console, navigate to EBS_domain_sid > Deployments, stop then delete the Oracle E-Business Suite AccessGate application named “accessgate”. Ensure that you click ‘Activate Changes’ in the ‘Change Center’ pane, for the changes to take effect.
2)
Delete the datasource “OAEADatasource”
Navigate to EBS_domain_sid > Services > Data Sources, and delete data source “OAEADatasource”. Ensure that you click ‘Activate Changes’ in the ‘Change Center’ pane, for the changes to take effect.
3)
Delete the managed server on which accessgate was deployed
- If the managed server oaea_server1 is currently running, shut it down as follows:
$ sh $ADMIN_SCRIPTS_HOME/admanagedsrvctl.sh stop oaea_server1
The script will prompt for the following passwords:
- Enter the WebLogic Admin password.
Enter the required information when prompted.
- Run the command below on the application tier node where the oaea_server1 managed server resides. This will delete the managed server, and also update the respective context variables that contain references to the deleted managed server:
$ perl $AD_TOP/patch/115/bin/adProvisionEBS.pl \
ebs-delete-managedserver \
-contextfile=$CONTEXT_FILE -managedsrvname=oaea_server1
4)
Remove the managed server and port from the mod_wl_ohs.conf configuration:
$ perl $FND_TOP/patch/115/bin/txkSetAppsConf.pl \
-contextfile=$CONTEXT_FILE \
-configoption=removeMS \
-accessgate=<host>.<domain>:<port>
To determine the value of the Port that was used for the oaea_server1 managed server, search for ‘s_wls_oaeaport’ in $CONTEXT_FILE.
Then follow usual way of integrating the Cloned Oracle E-Business Suite Instance with Oracle Access Manager using note 1576425.1
DBMS_SQLTUNE.CREATE_TUNING_TASK enhancement in 12c database
Running sql tuning advisor has become easy using Oracle Enterprisor Manager.
We can run sql tuning advisor for any query that exists in shared pool.
It can also performed manually from back end using DBMS_SQLTUNE (CREATE_TUNING_TASK and EXECUTE_TUNING_TASK).
Till 11GR2 the restriction for running sql tuning advisor is that sql should exist in shared pool.
In 12c this restriction has been removed and now it is not necessary the sql to be avilable in shared pool.
However we need to know and provide the period with which the sql was executed.
This can be given in for of snap_id of AWR.
Now the modified CREATE_TUNING_TASK function is available in database 12c is as follows.
declare
stmt_task VARCHAR2(40);
begin
stmt_task :=DBMS_SQLTUNE.CREATE_TUNING_TASK(begin_snap=>&big_snap,end_snap=>&en_snap,sql_id => ‘&sql_id’,time_limit=>3600);
DBMS_OUTPUT.put_line(‘task_id: ‘ || stmt_task );
end;
/
DBMS_SQLTUNE.CREATE_TUNING_TASK is also available without begin_snap and end_snap However as stated sql statement should exist in shared pool.
We can now execute statements in following sequence in order to run sql tuning advisor from sqlplus.
1. Create the tuning task
set serveroutput on
declare
stmt_task VARCHAR2(40);
begin
stmt_task :=DBMS_SQLTUNE.CREATE_TUNING_TASK(begin_snap=>&big_snap,end_snap=>&en_snap,sql_id => ‘&sql_id’,time_limit=>3600);
DBMS_OUTPUT.put_line(‘task_id: ‘ || stmt_task );
end;
/
task_id: TASK_33469
2. Execute the tuning task
begin
DBMS_SQLTUNE.EXECUTE_TUNING_TASK(task_name => ‘TASK_33469’);
end;
/
3. Report tuning advisor recommendations.
set lines 200
set long 2000000
set longchunksize 200000
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK(‘TASK_33471’) AS recommendations FROM dual;
We can also check status of run status from DBA_ADVISOR_LOG as follows.
SELECT TASK_NAME, STATUS FROM DBA_ADVISOR_LOG WHERE TASK_NAME = ‘TASK_33469’;
OID 11g not starting ORA-28002, ORA-28002: the password will expire within 7 days
Starting OID 11g failed with following
./opmnctl startall
opmnctl startall: starting opmn and all managed processes…
Response: 0 of 1 processes started.
ias-instance id=asinst_1
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
——————————————————————————–
ias-component/process-type/process-set:
oid1/oidmon/OID/
Error
–> Process (index=1,uid=512980697,pid=31131)
time out while waiting for a managed process to start
Log:
/home/orafmw/FMW/Middleware/asinst_1/diagnostics/logs/OID/oid1/console~OID~1.log
./opmnctl status
Processes in Instance: asinst_1
———————————+——————–+———+———
ias-component | process-type | pid | status
———————————+——————–+———+———
ohs1 | OHS | 29120 | Alive
ovd1 | OVD | 29119 | Alive
oid1 | oidldapd | N/A | Down
oid1 | oidldapd | N/A | Down
oid1 | oidmon | N/A | Down
EMAGENT | EMAGENT | 29118 | Alive
When checked oidmon-0000.log . It was throwing following error messages.
[pid: 32581] [tid: 0] Guardian: [gsdsiConnect] ORA-28002, ORA-28002: the password will expire within 7 days
Found a note to change ODS password which was expired.
Changing PASSWORD_LIFE_TIME did not work. Since password was already expired.
SQL> ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;
Profile altered.
SQL> select USERNAME,EXPIRY_DATE,PASSWORD_VERSIONS from dba_users where USERNAME in (‘ODS’,’ODSSM’);
USERNAME EXPIRY_DA PASSWORD_VER
——————————————————————————————————————————– ——— ————
ODS 27-NOV-17 10G 11G 12C
ODSSM 10G 11G 12C
Checked password versions from dba_users view. Since password version is still 10g We can change ODS password to have the same password .
SQL> set long 200000
SQL> set longchunksize 2000000
SQL> SELECT ‘ALTER USER ‘|| name ||’ IDENTIFIED BY VALUES ‘||chr(39)|| spare4 ||’;’|| password ||chr(39)||’;’ FROM user$ WHERE name=’ODS’;
‘ALTERUSER’||NAME||’IDENTIFIEDBYVALUES’||CHR(39)||SPARE4||’;’||PASSWORD||CHR(39)||’;’
——————————————————————————————————————————————————————————————————–
ALTER USER ODS IDENTIFIED BY VALUES ‘S:EB336044123CED1E74A3F8CFDA4F92CEF7190AE297EB2755FCE8CB493C09;T:1BDA18A95116064D9C76C284BD9336B170A96169AF2AE8D02A0B621F932F221F31C716EC1A7F94C55A48FF9DC1017514BC
549613DDE2EEC3D2C3AA139DA719ED4835EAC8E04312F53CA09EFC565C9BBB;B455D552053C38AB’;
SQL> ALTER USER ODS IDENTIFIED BY VALUES ‘S:EB336044123CED1E74A3F8CFDA4F92CEF7190AE297EB2755FCE8CB493C09;T:1BDA18A95116064D9C76C284BD9336B170A96169AF2AE8D02A0B621F932F221F31C716EC1A7F94C55A48FF9DC1017514BC549613DDE2EEC3D2C3AA139DA719ED4835EAC8E04312F53CA09EFC565C9BBB;B455D552053C38AB’;
User altered.
SQL> select USERNAME,EXPIRY_DATE,PASSWORD_VERSIONS from dba_users where USERNAME in (‘ODS’,’ODSSM’);
USERNAME EXPIRY_DA PASSWORD_VER
——————————————————————————————————————————– ——— ————
ODS 10G 11G 12C
ODSSM 10G 11G 12C
Npw ODS password will have the same password which was expired one.
Now all the services are started successfully.
$ ./opmnctl startall
opmnctl startall: starting opmn and all managed processes…
]$ ./opmnctl status
Processes in Instance: asinst_1
———————————+——————–+———+———
ias-component | process-type | pid | status
———————————+——————–+———+———
ohs1 | OHS | 767 | Alive
ovd1 | OVD | 766 | Alive
oid1 | oidldapd | 992 | Alive
oid1 | oidldapd | 882 | Alive
oid1 | oidmon | 765 | Alive
EMAGENT | EMAGENT | 764 | Alive
adop phase=prepare failed on running autoconfig on patch edition with ORA-20001: Synonym does not point to an editioning view: ADOP_VALID_NODES
Running autoconfig as part of adop phase=prepare failed on running txkGenADOPValidNodes.sql
It failed with following message
SQL*Plus: Release 10.1.0.5.0 – Production on Sun Nov 12 22:50:04 2017
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Enter value for 1: Enter value for 2: Enter value for 3: Connected.
Number of entries for syerpgold in ADOP_VALID_NODES = 1
DECLARE
*
ERROR at line 1:
ORA-20001: Synonym does not point to an editioning view: ADOP_VALID_NODES
ORA-06512: at “APPS.AD_ZD”, line 26
ORA-06512: at “APPS.AD_ZD_SEED”, line 65
ORA-06512: at “APPS.AD_ZD_SEED”, line 102
ORA-06512: at “APPS.AD_ZD_SEED”, line 1656
ORA-06512: at line 38
When I checked the synonym, it was pointing to applsys.ADOP_VALID_NODES# correctly.
However some how Oracle failed to recognized it as editioned view.
It may happen if an editioned view is dropped manually and a view with similar name ADOP_VALID_NODES# created manually.
We need to create a editioned view applsys.ADOP_VALID_NODES#.
It can be done by running ad_zd_table.upgrade.
It will create editioned view and synonym both in APPS schema.
SQL> exec ad_zd_table.upgrade(‘APPLSYS’, ‘ADOP_VALID_NODES’);
BEGIN ad_zd_table.upgrade(‘APPLSYS’, ‘ADOP_VALID_NODES’); END;
*
ERROR at line 1:
ORA-42305: only an Editioning view may replace an Editioning view
ORA-06512: at “APPS.AD_ZD_TABLE”, line 1035
ORA-06512: at “APPS.AD_ZD_TABLE”, line 1151
ORA-06512: at line 1
If you are getting above error message whlie executing ad_zd_table.upgrade then drop the view first.
SQL> drop view ADOP_VALID_NODES#;
View dropped.
SQL> exec ad_zd_table.upgrade(‘APPLSYS’, ‘ADOP_VALID_NODES’);
PL/SQL procedure successfully completed.
Now running ADZDSHOWTAB.sql for ADOP_VALID_NODES will show the editioned mapping for table
SQL> @ADZDSHOWTAB.sql ADOP_VALID_NODES
— Synonym
SYNONYM_NAME -> TABLE_OWNER TABLE_NAME
—————————— —- ——————– ——————————
ADOP_VALID_NODES -> APPLSYS ADOP_VALID_NODES#
— EV Column Mapping
VIEW_COLUMN -> TABLE_COLUMN DATA_TYPE NN DATA_DEFAULT
—————————— —- —————————— —————- — ————————
NODE_NAME ===> NODE_NAME#1 VARCHAR2(30)
CONTEXT_NAME = CONTEXT_NAME VARCHAR2(50) NN
ZD_EDITION_NAME = ZD_EDITION_NAME VARCHAR2(30) NN ‘V_20140325_0113’
ZD_SYNC = ZD_SYNC VARCHAR2(30) NN
<none> NODE_NAME VARCHAR2(30)
— Indexes
— Triggers
OWNER TRIGGER_NAME TABLE_OWNER TABLE_NAME CROSSED STATUS
——————– —————————— ——————– —————————— ——- ———-
APPS ADOP_VALID_NODES+ APPLSYS ADOP_VALID_NODES# NO ENABLED
— VPD policies
OBJECT_OWNER OBJECT_NAME POLICY_NAME FUNCTION ENA
——————– —————————— ——————– —————————— —
APPLSYS ADOP_VALID_NODES# ZD_SEED ADOP_VALID_NODES= YES
— related objects
OWNER OBJECT_NAME OBJECT_TYPE STATUS
——————– —————————— ——————– ———-
APPLSYS ADOP_VALID_NODES# VIEW VALID
APPS ADOP_VALID_NODES= FUNCTION VALID
APPS ADOP_VALID_NODES+ TRIGGER VALID
____________________________________________________________________________________________
SQL> @ADZDSHOWEV.sql ADOP_VALID_NODES
=========================================================================
= EV Column Mapping
=========================================================================
VIEW_COLUMN -> TABLE_COLUMN
—————————— —- ——————————
NODE_NAME ===> NODE_NAME#1
CONTEXT_NAME = CONTEXT_NAME
ZD_EDITION_NAME = ZD_EDITION_NAME
ZD_SYNC = ZD_SYNC
Now we can run adop phase=prepare again. It will complete autoconfig successfully.
Applied patches in downtime mode and hotpatch mode in EBS 12.2
If you have applied a EBS patch taking MT services down or applied a patch in hotpatch mode and you query for patch application using “Applied Patches” patch, It will not be displayed.
Oracle provides a patch 20549698 in order to resolve it.
You need not to apply the patch and you would still be able to see applied patches using “Oracle Application Manager”.
This is displayed under “software Updates” section of Applications Dashboard” page.
You can also check for applied patches on this page and it displays a patch applied which is applied in downtime mode or hotpatch mode while querying.
Design consideration for creating standby middle tier for EBS 12.2 single node middle tier
In Oracle EBS 12.2 , We know it maintains 2 file systems (run and patch file system) and if we apply a patch using ADOP online patching cycle these file systems would be flipped so that after patching cycle completes, the patch file system becomes new run file system and vice versa.
Also the directories on middle tier file system is created in form of InstanceName_Hostname (without domain name)
One consideration is to clone existing middle tier using same Instance name on different host.
And database can be refreshed on the standby server with existing production database and autoconfig can be run on database and middle tier file systems (RUN and PATCH).
It will work fine until a patch is applied to the original production and files systems are modified and flipped.
In this case we need to clone (create) middle tier again in order to maintain RUN and patch file system which is now interchanged.
However Oracle provides a way to use virtual host name on the servers.
In this case actual (physical) host name can be same on two servers that would be act as virtual host.
However a different hostname would be maintained in DNS.
So that the hostname would only be resolved on the server only not from DNS.
From R12.AD.C.Delta.9 onwards Oracle also provides a way to use logical hostname (that can be a not existing hostname) that can only be maintained using local host /etc/hosts file.
eg. actual (virtual) hostname on server is physical.domain.com defined in /etc/sysconfig/network
logical hostname written is /etc/hosts is logical.domain.com
In both cases (either in virtual hostname or logical hostname) we need to utilize a load balancer as suggested by Oracle to hide actual hostname that can be same on actual middle tier an standby middle tier).
ip/address could be different for both the hosts (actual and standby MT servers)
In this can we need only to RSYNC the file system(s) from actual MT server to standby MT server (as each has same hostname which can be either virtual or logical).
However using a load balancer even in case of single node MT server is mandatoty.
Also if we have a different ip/address for standby MT server then it is mandatory to run autoconfig on standby MT file systems before actual switching of servers.
Automatic EBS instance startup script to start with OS startup.
Sometimes there could be a need to automatic startup of a EBS instance with OS startup.
However downside is APPS and weblogic passwords would be exposed.
Oracle does not provide the solution and it is pending with Develoment with an enhancement request
This could be because of APPS and WEBLOGIC passwords would be compromised.
we can keep the password in a hidden file, However we need to write the file while providing password to script.
In this case, the instance would be gracefully down before shutting down the OS.
This is the reason we may only need to start the instance with OS startup and stop(shutdown) the instance manually.
Only we need to ensure that CM(s) are properly shutdown before proceeding to shutdown the database.
We can do it using a stop scripts
script: prod (to be created in /etc/init.d directory)
and rc.d startup script can be used for different run level by creating a link in rc.d directory as follows
ln -s ../init.d/prod S99prod
To do a manual shutdown we can use
/etc/init.d/prod stop
To do a manual restart we can use
/etc/init.d/prod restart
Following script can be used to stop and start the instance
we need to precreate log file /var/log/prod.log
prod:
#!/bin/bash
# script to startup PROD instance at OS startup
start() {
echo -n “Starting Oracle PROD ”
echo “—————————————————-” >> /var/log/prod.log
date +”! %T %a %D : Starting PROD instance as part of system up.” >> /var/log/prod.log
echo “—————————————————-” >> /var/log/prod.log
su – oraprod -c ‘. /home/oraprod/scripts/startprod.sh’ >> /var/log/prod.log
echo “”
echo “—————————————————-” >> /var/log/prod.log
date +”! %T %a %D : Finished.” >> /var/log/prod.log
echo “—————————————————-” >> /var/log/prod.log
}
stop() {
echo -n “Stopping Oracle PROD ”
echo “—————————————————-” >> /var/log/prod.log
date +”! %T %a %D : Stopping PROD instance as part of system shutdown.” >> /var/log/prod.log
echo “—————————————————-” >> /var/log/prod.log
su – oraprod -c ‘. /home/oraprod/scripts/shutprod.sh’ >> /var/log/prod.log
echo “”
echo “—————————————————-” >> /var/log/prod.log
date +”! %T %a %D : Finished.” >> /var/log/prod.log
echo “—————————————————-” >> /var/log/prod.log
}
case “$1” in
start)
start
;;
stop)
stop
;;
restart)
echo -n “Restarting Oracle PROD ”
echo “Restarting Oracle PROD ” >> /var/log/prod.log
stop
sleep 120
start
;;
*)
echo “Usage: prod {start|stop|restart}”
exit 1
esac
startprod.sh:
. /home/oraprod/PROD.env
lsnrctl start prod
sqlplus -s “/ as sysdba” << EOF
startup;
alter system register;
exit
EOF
. /home/oraprod/EBSapps.env run
cd $ADMIN_SCRIPTS_HOME
./adstrtal.sh apps/appspwd < weblogicpwd.txt
shutprod.sh:
cd $ADMIN_SCRIPTS_HOME
./adstpall.sh apps/appspwd < weblogicpwd.txt
echo -n “Waiting for Concurrent Manager to go down”
while true; do
$FND_TOP/bin/FNDSVCRG STATUS > /tmp/icmstatus2.txt
if test `cat /tmp/icmstatus2.txt | grep “Internal Concurrent Manager is Active” | wc -l` -eq 0 ; then
echo
echo -n “Concurrent Manager is down now”;
break;
fi
sleep 60;
done
. /home/oraprod/PROD.env
lsnrctl stop prod
sqlplus -s “/ as sysdba” << EOF
shut immediate;
exit
EOF
Network ACL (Access control list) after database upgrade to 11g or 12c in EBS database
As we are aware of network ACL is required fron Oracle database 11g onwards to access a URL.
Also we are aware the ACL is created when we run autoconfig on database tier if one does not exist
and connect and resolve privileges are granted to user APPS.
We had a specific script txkcreateACL.sh to do it.
It is explained in note used to upgrade database to 11.2.0.3 with EBS 12 and it suggests same that you need not to create ACL separately as it is taken care by Autoconfig using script txkcreateACL.sh.
It creates ACL for the database host using EBS port. port
We had specific requirement to use any host with any port and we had to assign any host with any port.
THen we need to assign ACL and unassigned ACL using following script after autoconfig on database tier.
EXEC dbms_network_acl_admin.assign_acl (acl => ‘OracleEBS.xml’,host => ‘*’);
EXEC DBMS_NETWORK_ACL_ADMIN.UNASSIGN_ACL (acl => ‘OracleEBS.xml’,host => ‘syerpgold’,lower_port => 8005,upper_port => 8005);
COMMIT;
Upgrading database to 12.1.0.2 introduces a new ACL with host * and no port.
It is needed by GSADMIN_INTERNAL user used by Global data services with Oracle database 12.1.0.2.
Running autoconfig on db tier grans CONNECT and RESOLVE on this ACL to Apps user.
However it is observed that running autoconfig again creates a new ACL /sys/acl/OracleEBS.xml and grants CONNECT and RESOLVE on this ACL to Apps user.
Since we need exactly one ACL created by sys user in database this extra ACL OracleEBS.xml can be safely dropped.
Script to daily automatic refresh of database from production to virtual production in EBS 12.2
AppsShutDbDelete() (
. /home/oraprod/EBSapps.env run
echo “—————————————————-” >> /home/oraprod/scripts/refresh.log
echo “Stopping Appication Middle Tier services” >> /home/oraprod/scripts/refresh.log
echo “—————————————————-” >> /home/oraprod/scripts/refresh.log
cd $ADMIN_SCRIPTS_HOME
./adstpall.sh apps/`cat /home/oraprod/scripts/appspwd.txt` < /home/oraprod/scripts/weblogicpwd.txt >> /home/oraprod/scripts/refresh.log
. /home/oraprod/PROD.env
echo “—————————————————-” > /home/oraprod/scripts/refresh.log
echo “Stopping database services” >> /home/oraprod/scripts/refresh.log
echo “—————————————————-” > /home/oraprod/scripts/refresh.log
sqlplus -s “/ as sysdba” << EOF >> /home/oraprod/scripts/refresh.log
shut abort;
exit;
EOF
lsnrctl stop prod >> /home/oraprod/scripts/refresh.log
echo “—————————————————-” > /home/oraprod/scripts/refresh.log
echo “Deleting database files” >> /home/oraprod/scripts/refresh.log
echo “—————————————————-” > /home/oraprod/scripts/refresh.log
cd /home/oraprod/PROD/data
rm -f *
echo “—————————————————-” > /home/oraprod/scripts/refresh.log
echo “Copying RMAN backup pieces from actual production” >> /home/oraprod/scripts/refresh.log
echo “—————————————————-” > /home/oraprod/scripts/refresh.log
cd /home/oraprod/backups/RMAN/PROD
scp syerpprod:/home/oraprod/backups/RMAN/PROD/* .
}
dbrefresh() {
echo “—————————————————-” >> /home/oraprod/scripts/refresh.log
date +”! %T %a %D : Starting database refresh” >> /home/oraprod/scripts/refresh.log
echo “—————————————————-” >> /home/oraprod/scripts/refresh.log
. /home/oraprod/PROD.env
cd /home/oraprod/backups/RMAN/PROD
echo “restore controlfile from ‘/home/oraprod/backups/RMAN/PROD/`ls control*`’ ;” > /tmp/restorectlfile.sql
sqlplus -s “/ as sysdba” << EOF >> /home/oraprod/scripts/refresh.log
startup nomount;
exit;
EOF
$ORACLE_HOME/bin/rman target / << EOF >> /home/oraprod/scripts/refresh.log
@/tmp/restorectlfile.sql
exit
EOF
sqlplus -s “/ as sysdba” << EOF >> /home/oraprod/scripts/refresh.log
alter database mount;
exit;
EOF
$ORACLE_HOME/bin/rman target / << EOF >> /home/oraprod/scripts/refresh.log
@/home/oraprod/scripts/restoredb.sql
exit
EOF
sqlplus -s “/ as sysdba” << EOF >> /home/oraprod/scripts/refresh.log
alter database open resetlogs;
exit;
EOF
lsnrctl start prod >> /home/oraprod/scripts/refresh.log
sqlplus -s apps/`cat /home/oraprod/scripts/appspwd.txt` << EOF >> /home/oraprod/scripts/refresh.log
execute fnd_conc_clone.setup_clean;
exit;
EOF
cd /home/oraprod/PROD/11.2.0/appsutil/scripts/PROD_syerpstandby
./adautocfg.sh < /home/oraprod/scripts/appspwd.txt >> /home/oraprod/scripts/refresh.log
echo “” >> /home/oraprod/scripts/refresh.log
echo “—————————————————-” >> /home/oraprod/scripts/refresh.log
date +”! %T %a %D : Finished database refresh” >> /home/oraprod/scripts/refresh.log
echo “—————————————————-” >> /home/oraprod/scripts/refresh.log
}
AppsTierconfigure() {
echo “” >> /home/oraprod/scripts/refresh.log
echo “—————————————————-” >> /home/oraprod/scripts/refresh.log
date +”! %T %a %D : Starting Configuration of Apps Tier” >> /home/oraprod/scripts/refresh.log
echo “—————————————————-” >> /home/oraprod/scripts/refresh.log
echo “—————————————————-” >> /home/oraprod/scripts/refresh.log
date +”! %T %a %D : Running Autoconfig on RUN file system” >> /home/oraprod/scripts/refresh.log
echo “—————————————————-” >> /home/oraprod/scripts/refresh.log
. /home/oraprod/EBSapps.env run
cd $ADMIN_SCRIPTS_HOME
./adautocfg.sh < /home/oraprod/scripts/appspwd.txt >> /home/oraprod/scripts/refresh.log
echo “—————————————————-” >> /home/oraprod/scripts/refresh.log
date +”! %T %a %D : Running Autoconfig on PATCH file system” >> /home/oraprod/scripts/refresh.log
echo “—————————————————-” >> /home/oraprod/scripts/refresh.log
. /home/oraprod/EBSapps.env patch
cd $ADMIN_SCRIPTS_HOME
./adautocfg.sh < /home/oraprod/scripts/appspwd.txt >> /home/oraprod/scripts/refresh.log
echo “” >> /home/oraprod/scripts/refresh.log
echo “—————————————————-” >> /home/oraprod/scripts/refresh.log
date +”! %T %a %D : Finished Configuration of Apps Tier” >> /home/oraprod/scripts/refresh.log
echo “—————————————————-” >> /home/oraprod/scripts/refresh.log
echo “—————————————————-” >> /home/oraprod/scripts/refresh.log
date +”! %T %a %D : Starting Apps Tier services” >> /home/oraprod/scripts/refresh.log
echo “—————————————————-” >> /home/oraprod/scripts/refresh.log
. /home/oraprod/EBSapps.env run
cd $ADMIN_SCRIPTS_HOME
./adstrtal.sh apps/`cat /home/oraprod/scripts/appspwd.txt` < /home/oraprod/scripts/weblogicpwd.txt >> /home/oraprod/scripts/refresh.log
sqlplus -s apps/`cat /home/oraprod/scripts/appspwd.txt` << EOF >> /home/oraprod/scripts/refresh.log
delete from adop_valid_nodes where NODE_NAME=’syerpprod’;
commit;
exit;
EOF
}
#Main Program
echo “—————————————————-” > /home/oraprod/scripts/refresh.log
echo “Starting PROD refresh process” >> /home/oraprod/scripts/refresh.log
echo “—————————————————-” >> /home/oraprod/scripts/refresh.log
AppsShutDbDelete;
dbrefresh;
AppsTierconfigure;
echo “”
echo “—————————————————-” >> /home/oraprod/scripts/refresh.log
date +”! %T %a %D : Finished PROD refresh process ” >> /home/oraprod/scripts/refresh.log
echo “—————————————————-” >> /home/oraprod/scripts/refresh.log
txkSetADOPPatchSrvName.sh script and context variable s_patch_service_name after AD.C.Delta.9 in EBS 12.2
The Grid Listener requires all registered services names to be unique. In AD-TXK Delta 8 and earlier, the service name for connections to the patch edition of the database was always ‘ebs_patch’. In AD-TXK Delta.9, the service name to connect to the patch edition has been changed to ‘instancename_ebs_patch’.
txkSetADOPPatchSrvName.sh which is wrapper over txkSetADOPPatchSrvName.pl performs it on database tier.
It does following.
It changes service_name in init.ora parameter to include ‘instancename_ebs_patch’.
If spfile is not used then it modifies init.ora file to include service_name parameter with ‘instancename_ebs_patch’.
If spfile is used service_name is modified in spfile.
After AD.C.Delta.9, service name for connections to the patch edition of the database becomes ‘instancename_ebs_patch’
And this should also be reflected in database tier context file , where s_patch_service_name should be set to ‘instancename_ebs_patch’.
Otherwise any adop action on Apps tier (MT) will fail with following.
[ERROR]: The value of s_patch_service_name is not set correctly in atleast one of the context files.
This is reason we need to explicitly to run autoconfig again on DB tier (database tier). It also mentioned in post AD.C.Delta.9 AD and Tech stack patches.
Autoconfig automatically runs in process of applying AD and TXK patches on Apps Tier (MT) which populates s_patch_service_name to ‘_ebs_patch’ in Apps Tier context files.
Oracle E-Business Suite and database Blog 