Converting EBS Database To RAC with in ASM File system

 

List of Topics (Linux, Database, RAC, EBS)

Hi, In this topic we will convert EBS database from single node which is running in ASM and Cluster services are already installed on both nodes to RAC DB and will configure Application server to EBS RAC DB

Note: in future use scanport on 1531 during installation so ASM Listener will run on 1521 port it will be easy to enable scan for db connection.

Converting EBS Database To RAC with in ASM File system, here we will be using ports for different listeners (ASM,DB,SCAN)

ASM and DB listeners are configured on same port 1521

Scan listeners configured on port 1531

[grid@uatdb01 ~]$ srvctl config listener  (ASM-listener port)

Name: LISTENER

Type: Database Listener

Network: 1, Owner: grid

Home: <CRS home>

End points: TCP:1521

Listener is enabled.

[oracle@uatdb01 ~]$ lsnrctl status $ORACLE_SID   (db-listener port)

  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=uatdb01-vip.ahc.edu.sa)(PORT=1521)))

[root@uatdb01 ~]# srvctl config scan_listener   (scan listener port)

SCAN Listener LISTENER_SCAN1 exists. Port: TCP:1531

[root@uatdb01 ~]# srvctl config scan   (scan name/ remotelistener)

SCAN name: scan-uat, Network: 1

################################################################################################################

################################################################################################################

SQL> create pfile='$ORACLE_HOME/dbs/initUAT_b4rac.ora' from spfile;

SQL> shut immeidiate

[oracle@uatdb01 dbs]$ cd $ORACLE_HOME/appsutil/clone/bin

If got below error while executing adcfg** script

APPS Password : Log file located at /u01/app/oracle/product/12.1.0/dbhome_1/appsutil/log/UAT1_uatdb01/ApplyDBTechStack_10010911.log

 |      0% completed       RC-00110: Fatal: Error occurred while relinking of ApplyDBTechStack

ERROR while running Apply...

 Please check logfile.

[oracle@uatdb01 bin]$

[oracle@uatdb01 bin]$ vi /u01/app/oracle/product/12.1.0/dbhome_1/appsutil/log/UAT1_uatdb01/ApplyDBTechStack_10010911.log

Sun Oct  1 09:12:13 2023WARNING: [AutoConfig Error Report]

The following report lists errors AutoConfig encountered during each

phase of its execution.  Errors are grouped by directory and phase.

The report format is:

      <filename>  <phase>  <return code where appropriate>

  [APPLY PHASE]

  AutoConfig could not successfully execute the following scripts:

    Directory: /u01/app/oracle/product/12.1.0/dbhome_1/perl/bin/perl -I /u01/app/oracle/product/12.1.0/dbhome_1/perl/lib/5.14.1 -I /u01/app/oracle/product/12.1.0/dbhome_1/perl/lib/site_perl/5.14.1 -I /u01/app/oracle/product/12.1.0/dbhome_1/appsutil/perl /u01/app/oracle/product/12.1.0/dbhome_1/appsutil/clone

      ouicli.pl               INSTE8_APPLY       1

AutoConfig is exiting with status 1

WARNING: RC-50013: Fatal: Instantiate driver did not complete successfully.

/u01/app/oracle/product/12.1.0/dbhome_1/appsutil/driver/regclone.drv

[oracle@uatdb01 bin]$

[oracle@uatdb01 bin]$

[oracle@uatdb01 ~]$ $ORACLE_HOME/appsutil/jre/bin/java -version

java version "1.7.0_17"

Java(TM) SE Runtime Environment (build 1.7.0_17-b02)

Java HotSpot(TM) 64-Bit Server VM (build 23.7-b01, mixed mode)

[oracle@uatdb01 ~]$ $ORACLE_HOME/jdk/jre/bin/java -version

java version "1.6.0_75"

Java(TM) SE Runtime Environment (build 1.6.0_75-b13)

Java HotSpot(TM) 64-Bit Server VM (build 20.75-b01, mixed mode)

[oracle@uatdb01 ~]$

[oracle@uatdb01 ~]$ du -sh $ORACLE_HOME/appsutil/jre

132M    jre

[oracle@uatdb01 ~]$ du -sh $ORACLE_HOME/jdk/jre

95M     jre_old/

[oracle@uatdb01 ~]$

[oracle@uatdb01 ~]$ mv $ORACLE_HOME/jdk/jre $ORACLE_HOME/jdk/jre_old

[oracle@uatdb01 ~]$ cp -r $ORACLE_HOME/appsutil/jre/ $ORACLE_HOME/jdk/

[oracle@uatdb01 ~]$

[oracle@uatdb01 bin]$ ./adcfgclone.pl dbTechStack

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

Target Instance is RAC (y/n) [y] : n

Target System Database SID : UAT1

Target System Base Directory : /u01/app/oracle

Target System utl_file_dir Directory List : /usr/tmp

Number of DATA_TOP's on the Target System [3] : 1

Target System DATA_TOP Directory 1 : +DATA

Target System RDBMS ORACLE_HOME Directory [/u01/app/oracle/12.1.0] : /u01/app/oracle/product/12.1.0/dbhome_1

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

Target System Display [uatdb01:0.0] :

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

Target System Port Pool [0-99] : 0

Checking the port pool 0

RC-50221: Warning: Port Pool 0 is not free. Please check logfile /u01/app/oracle/product/12.1.0/dbhome_1/appsutil/clone/bin/CloneContext_1127135426.log for conflicts.

[oracle@uatdb01 ~]$ lsnrctl stop UAT  ------->>>>>>>> if UAT is on 1521

[grid@uatdb01 ~]$ lsnrctl stop --------------------------------->>>>>>>> if interested to use grid port 1521 then stop grid listener and select 0 again

Target System Port Pool [0-99] : 0

Checking the port pool 0

done: Port Pool 0 is free

Report file located at /u01/app/oracle/product/12.1.0/dbhome_1/appsutil/temp/portpool.lst

The new database context file has been created :

  /u01/app/oracle/product/12.1.0/dbhome_1/appsutil/UAT1_uatdb01.xml

Check Clone Context logfile /u01/app/oracle/product/12.1.0/dbhome_1/appsutil/clone/bin/CloneContext_1127135426.log for details.

Running Rapid Clone with command:

[grid@uatdb01 ~]$ lsnrctl start

[oracle@uatdb01 dbs]$ cp $ORACLE_HOME/dbs/initUAT1.ora $ORACLE_HOME/dbs/initUAT1.ora_orig

[oracle@uatdb01 dbs]$ vi initUAT_b4rac.ora

#--- Modifying Parameters only for RAC

*.control_files='+DATA/cntrl01.dbf'

*.undo_management='AUTO'# Required 11i setting

#--- Modifying Parameters for ADOP failuer

db_recovery_file_dest_size=1500G

pga_aggregate_target=16G

sga_max_size=16G

sga_target=12G

#--- Disabling Parameters

#*.cluster_database=TRUE# Mandatory if restoring from RMAN bkp pcs.

#*.local_listener='UAT_LOCAL'

#*.undo_tablespace='APPS_UNDOTS1'# Required 11i setting

#--- New parameter to convert to RAC

cluster_database_instances=2

#*.instance_name=UAT1

#*.instance_name=UAT2

UAT1.instance_number=1

UAT2.instance_number=2

#--- comment remo&local listener if db not startup and add with below alter command

#remote_listener='scan-uat:1531'#enable scan on db and apps tns (provide scan-port not DB-port)

#UAT1.local_listener='(ADDRESS=(PROTOCOL=TCP)(HOST=uatdb01-vip.ahc.edu.sa)(PORT=1521))'#use DB-port not scan port

#UAT2.local_listener='(ADDRESS=(PROTOCOL=TCP)(HOST=uatdb02-vip.ahc.edu.sa)(PORT=1521))'#use DB-port not scan port

#--- Database Initialization Parameters for Oracle E-Business Suite Release 12 (Doc ID 396009.1)

*.temp_undo_enabled=FALSE #modifying Parameter

*._disable_actualization_for_grant=TRUE

optimizer_adaptive_features=FALSE

filesystemio_options=SETALL

[oracle@uatdb01 dbs]$ cp $ORACLE_HOME/dbs/initUAT_b4rac.ora $ORACLE_HOME/dbs/initUAT1.ora

[oracle@uatdb01 dbs]$

[oracle@uatdb01 dbs]$

Mandatory Parameters to set in DB before RAC Conversion

show parameter _disable_actualization_for_grant

show parameter filesystemio_options

show parameter optimizer_adaptive_features

show parameter remote_listener

show parameter local_listener

if facing issue with init file then comment in pfile and set below parameters manually in spfile after db is up

alter system set "_disable_actualization_for_grant"=true;

ALTER SYSTEM SET FILESYSTEMIO_OPTIONS=SETALL SCOPE=SPFILE;

alter system set optimizer_adaptive_features=false scope=both sid='*';

alter system set remote_listener='scan-uat:1531' scope=both sid='*';

alter system set local_listener='(ADDRESS=(PROTOCOL=TCP)(HOST=uatdb01-vip.ahc.edu.sa)(PORT=1521))' sid='UAT1' scope=spfile; 

alter system set local_listener='(ADDRESS=(PROTOCOL=TCP)(HOST=uatdb02-vip.ahc.edu.sa)(PORT=1521))' sid='UAT2' scope=spfile;

Source new env and create spfile in ASM to continue further

ASMCMD [+data/uat] > mkdir +DATA/UAT/parameterfile

[oracle@uatdb01 dbs]$ . $ORACLE_HOME/dbs/initUAT1.ora

[oracle@uatdb01 dbs]$ echo $ORACLE_SID

UAT1

[oracle@uatdb01 dbs]$

[oracle@uatdb01 dbs]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Sun Nov 20 13:38:31 2022

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> create spfile='+DATA/UAT/parameterfile/spfileUAT.ora' from pfile='/u01/app/oracle/product/12.1.0/dbhome_1/dbs/initUAT1.ora';

File created.

SQL> 

Mandatory step as tech stack has run on current home

[oracle@uatdb01 dbs]$

cd $ORACLE_HOME/rdbms/lib

make -f ins_rdbms.mk rac_on

make -f ins_rdbms.mk ioracle

pwd

[oracle@uatdb01 dbs]$

[oracle@uatdb01 dbs]$ cp $ORACLE_HOME/dbs/initUAT1.ora $ORACLE_HOME/dbs/initUAT1.ora_b4_ASMspf

[oracle@uatdb01 dbs]$

[oracle@uatdb01 dbs]$ echo spfile='+DATA/UAT/parameterfile/spfileUAT.ora' > $ORACLE_HOME/dbs/initUAT1.ora

[oracle@uatdb01 dbs]$ cat $ORACLE_HOME/dbs/initUAT1.ora

spfile='+DATA/UAT/parameterfile/spfileUAT.ora'

[oracle@uatdb01 dbs]$

[oracle@uatdb01 dbs]$ echo $ORACLE_SID

UAT1

[oracle@uatdb01 dbs]$

[oracle@uatdb01 dbs]$

[oracle@uatdb01 dbs]$ !sq

sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Sun Nov 20 14:09:49 2022

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup

ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance

ORACLE instance started.

Total System Global Area 4294967296 bytes

Fixed Size                  2932632 bytes

Variable Size            1660944488 bytes

Database Buffers         2617245696 bytes

Redo Buffers               13844480 bytes

Database mounted.

Database opened.

SQL> 

If not modified in initfile then update remote and local listener parameters to spfile manually

alter system set "_disable_actualization_for_grant"=true;

ALTER SYSTEM SET FILESYSTEMIO_OPTIONS=SETALL SCOPE=SPFILE;

alter system set optimizer_adaptive_features=false scope=both sid='*';

alter system set remote_listener='scan-uat:1531' scope=both sid='*';

alter system set local_listener='(ADDRESS=(PROTOCOL=TCP)(HOST=uatdb01-vip.ahc.edu.sa)(PORT=1521))' sid='UAT1' scope=spfile; 

alter system set local_listener='(ADDRESS=(PROTOCOL=TCP)(HOST=uatdb02-vip.ahc.edu.sa)(PORT=1521))' sid='UAT2' scope=spfile;

show parameter _disable_actualization_for_grant

show parameter filesystemio_options

show parameter optimizer_adaptive_features

show parameter remote_listener

show parameter local_listener

SQL> shut immediate

SQL> startup

SQL> set linesize 300;

col HOST_NAME for a9;

select INSTANCE_NUMBER,INSTANCE_NAME,HOST_NAME,STATUS,DATABASE_STATUS,INSTANCE_ROLE,ACTIVE_STATE,INSTANCE_MODE from gv$instance;

INSTANCE_NUMBER INSTANCE_NAME    HOST_NAME STATUS       DATABASE_STATUS   INSTANCE_ROLE      ACTIVE_ST INSTANCE_MO

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

              1 UAT1             uatdb01.p OPEN         ACTIVE            PRIMARY_INSTANCE   NORMAL    REGULAR

                                 sau.edu.s

                                 a

SQL>

SQL> select NAME,OPEN_MODE,CREATED,LOG_MODE,CONTROLFILE_TYPE,FLASHBACK_ON,DATABASE_ROLE,GUARD_STATUS,PROTECTION_MODE from gv$database;

NAME      OPEN_MODE            CREATED   LOG_MODE     CONTROL FLASHBACK_ON       DATABASE_ROLE    GUARD_S PROTECTION_MODE

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

UAT       READ WRITE           17-NOV-22 ARCHIVELOG   CURRENT NO                 PRIMARY          NONE    MAXIMUM PERFORMANCE

SQL> AlertLog file location

SELECT INSTANCE_NAME, STATUS, DATABASE_STATUS FROM V$INSTANCE;

select * from v$pwfile_users;

SELECT value from v$diag_info where name='Diag Trace';

select 'tail -f '||d.value||'/alert_'||i.INSTANCE_NAME||'.log' from gv$diag_info d,v$instance i where d.name='Diag Trace';

################################################################################################################

run dbTechstack on node2 repeat same as did in node 1

################################################################################################################

[oracle@uatdb02 ~]$ $ORACLE_HOME/appsutil/jre/bin/java -version

java version "1.7.0_17"

Java(TM) SE Runtime Environment (build 1.7.0_17-b02)

Java HotSpot(TM) 64-Bit Server VM (build 23.7-b01, mixed mode)

[oracle@uatdb02 ~]$ $ORACLE_HOME/jdk/jre/bin/java -version

java version "1.6.0_75"

Java(TM) SE Runtime Environment (build 1.6.0_75-b13)

Java HotSpot(TM) 64-Bit Server VM (build 20.75-b01, mixed mode)

[oracle@uatdb02 ~]$ mv $ORACLE_HOME/jdk/jre $ORACLE_HOME/jdk/jre_old

[oracle@uatdb02 ~]$ cp -r $ORACLE_HOME/appsutil/jre/ $ORACLE_HOME/jdk/

[oracle@uatdb02 ~]$

[oracle@uatdb02 ~]$

[oracle@uatdb02 ~]$

[oracle@uatdb02 ~]$

[oracle@uatdb02 ~]$ $ORACLE_HOME/jdk/jre/bin/java -version

java version "1.7.0_17"

Java(TM) SE Runtime Environment (build 1.7.0_17-b02)

Java HotSpot(TM) 64-Bit Server VM (build 23.7-b01, mixed mode)

[oracle@uatdb02 ~]$

[oracle@uatdb02 bin]$ ./adcfgclone.pl dbTechStack

Target System Database SID : UAT2

[oracle@uatdb02 bin]$ cp /u01/app/oracle/product/12.1.0/dbhome_1/UAT2_uatdb02.env ~

[oracle@uatdb02 ~]$ . UAT2_uatdb02.env

[oracle@uatdb02 dbs]$ cp $ORACLE_HOME/dbs/initUAT2.ora $ORACLE_HOME/dbs/initUAT2.ora_orig

[oracle@uatdb02 dbs]$ echo spfile='+DATA/UAT/parameterfile/spfileUAT.ora' > $ORACLE_HOME/dbs/initUAT2.ora

[oracle@uatdb02 dbs]$ cat $ORACLE_HOME/dbs/initUAT2.ora

spfile='+DATA/UAT/parameterfile/spfileUAT.ora'

[oracle@uatdb02 dbs]$

Mandatory step as tech stack has run on current home

cd $ORACLE_HOME/rdbms/lib

make -f ins_rdbms.mk rac_on

make -f ins_rdbms.mk ioracle

pwd

[oracle@uatdb02 dbs]$ cd

[oracle@uatdb02 ~]$ . ~/UAT2_uatdb02.env

[oracle@uatdb02 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Sun Nov 20 14:43:00 2022

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup

ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance

ORACLE instance started.

Total System Global Area 4294967296 bytes

Fixed Size                  2932632 bytes

Variable Size            1677721704 bytes

Database Buffers         2600468480 bytes

Redo Buffers               13844480 bytes

Database mounted.

ORA-01092: ORACLE instance terminated. Disconnection forced

ORA-30013: undo tablespace 'APPS_UNDOTS1' is currently in use

Process ID: 9601

Session ID: 9779 Serial number: 28193

[oracle@uatdb01 dbs]$ grep undo initUAT1.ora_4mv_updated

*.temp_undo_enabled=false

*.undo_management='AUTO'# Required 11i setting

#*.undo_tablespace='APPS_UNDOTS1'# Required 11i setting

[oracle@uatdb01 dbs]$

[oracle@uatdb02 ~]$ !sq

sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Sun Nov 20 14:52:51 2022

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup

ORA-01102: cannot mount database in EXCLUSIVE mode

uatdb01 SQL> alter system set cluster_database=true scope=spfile sid='*';

uatdb01 SQL> shut immeidate

uatdb01 SQL> startup

SQL> startup

ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance

ORACLE instance started.

Total System Global Area 4294967296 bytes

Fixed Size                  2932632 bytes

Variable Size            1677721704 bytes

Database Buffers         2600468480 bytes

Redo Buffers               13844480 bytes

Database mounted.

Database opened.

SQL>

SQL> select inst_id, count(*) from gv$session group by inst_id;

   INST_ID   COUNT(*)

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

         1         72

         2         70

SQL>

################################################################################################################

Update Context File on individual node before auto config ({remote_lis/scan=1531} {local_lis/db=1521})

################################################################################################################

[root@uatdb01 ~]# srvctl config scan

SCAN name: scan-uat, Network: 1

[root@uatdb01 ~]# srvctl config scan_listener

Endpoints: TCP:1531

[root@uatdb01 ~]# srvctl config listener

Name: LISTENER

Network: 1, Owner: grid

End points: TCP:1521

[root@uatdb01 ~]# lsnrctl status

[root@uatdb01 ~]# 

[oracle@uatdb01 ~]$ . UAT1_uatdb01.env

[oracle@uatdb01 ~]$ echo $ORACLE_SID

UAT1

[oracle@uatdb01 appsutil]$ echo $CONTEXT_FILE

/u01/app/oracle/product/12.1.0/dbhome_1/appsutil/UAT1_uatdb01.xml

[oracle@uatdb01 appsutil]$

[oracle@uatdb01 appsutil]$

[oracle@uatdb01 appsutil]$

[oracle@uatdb01 appsutil]$

[oracle@uatdb01 appsutil]$ cp UAT1_uatdb01.xml UAT1_uatdb01.xml_bkp_20112022

[oracle@uatdb01 appsutil]$

<oa_system_name oa_var="s_systemname">UAT</oa_system_name>

      <global_db_name oa_var="s_dbSid">UAT1</global_db_name>

      <db_name_lower oa_var="s_dbSidLower">uat1</db_name_lower>

      <dbname oa_var="s_dbGlnam">UAT</dbname>

      <global_database_name oa_var="s_global_database_name">UAT</global_database_name>

      <cluster_database oa_var="s_dbCluster">TRUE</cluster_database>

      <cluster_database_instances oa_var="s_dbClusterInst">2</cluster_database_instances>

      <pluggable_database oa_var="s_pluggable_database">false</pluggable_database>

  

  <patch_service_name oa_var="s_patch_service_name">UAT_ebs_patch</patch_service_name>

   

  <dbracprefix oa_var="s_RAC_prefix">UAT</dbracprefix>

      <dbracnodes oa_var="s_RAC_nodes"/>

      <instance_name oa_var="s_instName">UAT1</instance_name>

      <instance_number oa_var="s_instNumber">1or2</instance_number>

      <instance_thread oa_var="s_instThread">0</instance_thread>

      <service_names oa_var="s_dbService">UAT</service_names>

      <local_listener oa_var="s_instLocalListener">UAT1_LOCAL</local_listener>

      <remote_listener oa_var="s_instRemoteListener">scan-uat:1531</remote_listener>

    

  <scanName oa_var="s_scan_name">scan-uat</scanName>

         <scanPort oa_var="s_scan_port">1531</scanPort>

         <scanUpdateFlag oa_var="s_update_scan">TRUE</scanUpdateFlag>

 

  <host oa_var="s_hostname">uatdb01</host>

      <host oa_var="s_virtual_hostname">uatdb01-vip</host>

 

grep s_systemname $CONTEXT_FILE

grep s_dbSid $CONTEXT_FILE

grep s_dbSidLower $CONTEXT_FILE

grep s_dbGlnam $CONTEXT_FILE

grep s_global_database_name $CONTEXT_FILE

grep s_dbCluster $CONTEXT_FILE

grep s_dbClusterInst $CONTEXT_FILE

grep s_pluggable_database $CONTEXT_FILE

grep s_patch_service_name $CONTEXT_FILE

grep s_RAC_prefix $CONTEXT_FILE

grep s_RAC_nodes $CONTEXT_FILE

grep s_instName $CONTEXT_FILE

grep s_instNumber $CONTEXT_FILE

grep s_instLocalListener $CONTEXT_FILE

grep s_instRemoteListener $CONTEXT_FILE

grep s_scan_name $CONTEXT_FILE

grep s_scan_port $CONTEXT_FILE

grep s_update_scan $CONTEXT_FILE

grep s_hostname $CONTEXT_FILE

grep s_virtual_hostname $CONTEXT_FILE

################################################################################################################

Mandatory to clean fndnodes before each autoconfig attempt on db01 until success, else it will fail as show below

no need to clean again while running on db02 and app after db01 success

################################################################################################################

WARNING: [AutoConfig Error Report]

The following report lists errors AutoConfig encountered during each

phase of its execution.  Errors are grouped by directory and phase.

The report format is:

      <filename>  <phase>  <return code where appropriate>

  [PROFILE PHASE]

  AutoConfig could not successfully execute the following scripts:

    Directory: /u01/app/oracle/product/12.1.0/dbhome_1/appsutil/install/UAT1_uatdb01

      afdbprf.sh              INSTE8_PRF         1

  [APPLY PHASE]

  AutoConfig could not successfully execute the following scripts:

    Directory: /u01/app/oracle/product/12.1.0/dbhome_1/appsutil/install/UAT1_uatdb01

      adcrobj.sh              INSTE8_APPLY       1

AutoConfig is exiting with status 2

AutoConfig execution completed on Sun Oct  1 12:52:38 2023

Time taken for AutoConfig execution to complete : 0 mins  13 secs

[oracle@uatdb01 dbs]$ grep local initUAT1_P1521.ora

UAT2.local_listener='(ADDRESS=(PROTOCOL=TCP)(HOST=uatdb02-vip.ahc.edu.sa)(PORT=1521))'

UAT1.local_listener='(ADDRESS=(PROTOCOL=TCP)(HOST=uatdb01-vip.ahc.edu.sa)(PORT=1521))'

*.parallel_force_local=TRUE

[oracle@uatdb01 dbs]$

[oracle@uatdb01 appsutil]$ grep scan UAT1_uatdb01.xml

      <remote_listener oa_var="s_instRemoteListener">scan-uat:1531</remote_listener>

         <scanName oa_var="s_scan_name">scan-uat</scanName>

         <scanPort oa_var="s_scan_port">1531</scanPort>

         <scanUpdateFlag oa_var="s_update_scan">TRUE</scanUpdateFlag>

[oracle@uatdb01 appsutil]$

SQL> select THREAD#, STATUS, ENABLED from v$thread;

   THREAD# STATUS ENABLED

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

         1 OPEN   PUBLIC

         2 OPEN   PUBLIC

SQL>

SQL> conn apps/apps

SQL> select node_name from apps.fnd_nodes;

NODE_NAME

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

SCAPPLAUD01

SCAPPLAUD02

AUTHENTICATION

SCREC01

SCREC02

PRODAPPSC01

PRODAPPSC02

PRODDBSC01

PRODDBSC02

UATDB01

10 rows selected.

SQL>

SQL> 

col DB_DOMAIN for a30;

col SCAN_NAME for a25;

select DB_NAME,DB_DOMAIN,SCAN_NAME,SCAN_PORT from apps.fnd_databases;

SQL> exec fnd_conc_clone.setup_clean;

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.

SQL> select node_name from apps.fnd_nodes;

no rows selected

SQL>

[oracle@uatdb01 appsutil]$ $ORACLE_HOME/appsutil/scripts/$CONTEXT_NAME/adautocfg.sh

AutoConfig completed successfully.

[oracle@uatdb01 appsutil]$ 

[oracle@uatdb02 appsutil]$ $ORACLE_HOME/appsutil/scripts/$CONTEXT_NAME/adautocfg.sh

AutoConfig completed successfully.

[oracle@uatdb02 appsutil]$ 

SQL> 

col DB_DOMAIN for a30;

col SCAN_NAME for a25;

select DB_NAME,DB_DOMAIN,SCAN_NAME,SCAN_PORT from apps.fnd_databases;

DB_NAME  DB_DOMAIN                      SCAN_NAME                  SCAN_PORT

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

UAT      ahc.edu.sa                     scan-uat                     1531

if scan details not update in fnd_database update manually and run auto config again

DB_NAME  DB_DOMAIN                      SCAN_NAME                  SCAN_PORT

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

UAT      ahc.edu.sa                                          

SQL> conn apps/apps 

update apps.fnd_databases set SCAN_NAME='scan-uat',SCAN_PORT=1531 where DB_NAME='UAT';

adautocfg.sh

vi $TNS_ADMIN/tnsnames.ora

################################################################################################################

Mandatory else ADOP fails 

ETCC Checks on converted RAC EBS DB on node1 and node1

SQL> select NODE_NAME from apps.TXK_TCC_RESULTS;

[oracle@uatdb01 etcc]$ $ORACLE_HOME/appsutil/etcc/checkDBpatch.sh

[oracle@uatdb02 etcc]$ $ORACLE_HOME/appsutil/etcc/checkDBpatch.sh

[oracle@uatapp01 etcc]$ ./checkMTpatch.sh

################################################################################################################

Mandatory else scan IP cannot be configured

Configure Database to Cluster Services to enable SCAN-IP for db&apps tns:

[oracle@uatdb01 ~]$ srvctl add database -d UAT -o /u01/app/oracle/product/12.1.0/dbhome_1 -p +DATA/UAT/parameterfile/spfileUAT.ora

[oracle@uatdb01 ~]$ srvctl add instance -d UAT  -i UAT1 -n uatdb01

[oracle@uatdb01 ~]$ srvctl add instance -d UAT  -i UAT2 -n uatdb02

[oracle@uatdb01 ~]$ 

[oracle@uatdb01 ~]$ srvctl config database -d $ORACLE_UNQNAME

[oracle@uatdb01 ~]$ srvctl status database -d $ORACLE_UNQNAME

uatdb01 shut immediate

uatdb02 shut immediate

[oracle@uatdb01 ~]$ srvctl start database -d UAT

[oracle@uatdb01 ~]$ srvctl status database -d UAT

################################################################################################################

################################################################################################################

Know issues during Converting: Autoconfig was failed on DB tier need to rebuild Xml as per given doc

################################################################################################################

Autoconfig Fails On RAC Node with java.sql.SQLIntegrityConstraintViolationException: ORA-00001: unique constraint (APPLSYS.FND_DATABASE_INSTANCES_U1) violated (Doc ID 2382040.1)

E-Business Suite Applications Technology Stack Scripts Afmkinit.sh, Afdbprf.sh And Adcrobj.sh Fail While Executing Autoconfig On Database Tier (Doc ID 1097768.1)

$ORACLE_HOME/appsutil/bin/adbldxml.pl

[oracle@uatdb01 bin]$ adbldxml.pl

Starting context file generation for db tier..

Using JVM from /u01/app/oracle/product/12.1.0/dbhome_1/appsutil/jre/bin/java to execute java programs..

APPS Password:

The log file for this adbldxml session is located at:

/u01/app/oracle/product/12.1.0/dbhome_1/appsutil/log/adbldxml_11271335.log

Enter Database Service Name: UAT

Do you want to enable SCAN addresses[N]:

Enter the value for Display Variable:

Invalid input, reenter the value for Display Variable: 0

Context File  /u01/app/oracle/product/12.1.0/dbhome_1/appsutil/UAT1_uatdb01.xml

 already exists.

Overwriting Context File may cause loss of existing settings, hence

 backing it up as: /u01/app/oracle/product/12.1.0/dbhome_1/appsutil/UAT1_uatdb01.xml.bak

The context file has been created at:

/u01/app/oracle/product/12.1.0/dbhome_1/appsutil/UAT1_uatdb01.xml

[oracle@uatdb01 bin]$

[oracle@uatdb01 appsutil]$ 

$ORACLE_HOME/appsutil/scripts/$CONTEXT_NAME/adautocfg.sh

[oracle@uatdb02 appsutil]$ 

$ORACLE_HOME/appsutil/scripts/$CONTEXT_NAME/adautocfg.sh

################################################################################################################

[oracle@uatdb01 appsutil]$ 

$ORACLE_HOME/appsutil/scripts/$CONTEXT_NAME/adautocfg.sh

scapps1

SQL> create spfile='+DATA/UAT/parameterfile/spfileUAT.ora' from pfile='/u01/app/oracle/product/12.1.0/dbhome_1/dbs/initUAT1.ora';

File created.

SQL> 

!echo spfile='+DATA/UAT/parameterfile/spfileUAT.ora' > $ORACLE_HOME/dbs/initUAT1.ora

[oracle@uatdb01 dbs]$ echo spfile='+DATA/UAT/parameterfile/spfileUAT.ora' > $ORACLE_HOME/dbs/initUAT1.ora

################################################################################################################

To enable Scan IP on DB TNS and APPS TNS

Note: in future use scanport on 1531 during installation so ASM Listener will run on 1521 port it will be easy to configure scan

1 remote&local listener parameter to spfile;

2 configure and add database to SRVCTL services

3 update db-context for parameter s_update_scan true before every db-autoconfig

grep s_update_scan $CONTEXT_FILE

4 run autoconfig on DB node1 --> node 2 --> node1 (after setting above 1,2,3)

-4.1 in autoconfig log search adgentns.pl log file --> NetServiceHandler.log --> (verify0) FND_DATABASES updated successfully

-4.2 mandatory to have scan details in apps.fnd_database after autoconfig. If not updated the insert record manually as shown and run autoconfig again

Node1:

[oracle@uatdb01 ~]$ srvctl config database

UAT

[oracle@uatdb01 ~]$ srvctl status database -d $ORACLE_UNQNAME

Instance UAT1 is running on node uatdb01

Instance UAT2 is running on node uatdb02

[oracle@uatdb01 ~]$ 

show parameter listener

NAME                                 TYPE        VALUE

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

listener_networks                    string

local_listener                       string      (ADDRESS=(PROTOCOL=TCP)(HOST=

                                                 uatdb01-vip.ahc.edu.sa)(

                                                 PORT=1521))

remote_listener                      string      scan-uat:1531

SQL>

[oracle@uatdb01 ~]$ grep s_update_scan $CONTEXT_FILE

         <scanUpdateFlag oa_var="s_update_scan">TRUE</scanUpdateFlag>

[oracle@uatdb01 ~]$

[oracle@uatdb01 ~]$ $ORACLE_HOME/appsutil/scripts/$CONTEXT_NAME/adautocfg.sh

[oracle@uatdb01 ~]$ tnsping $ORACLE_SID

[oracle@uatdb01 ~]$ vi $TNS_ADMIN/tnsnames.ora

Node2:

[oracle@uatdb02 ~]$ grep s_update_scan $CONTEXT_FILE

         <scanUpdateFlag oa_var="s_update_scan">TRUE</scanUpdateFlag>

[oracle@uatdb02 ~]$ $ORACLE_HOME/appsutil/scripts/$CONTEXT_NAME/adautocfg.sh

[oracle@uatdb02 ~]$ tnsping $ORACLE_SID

[oracle@uatdb02 ~]$ vi $TNS_ADMIN/tnsnames.ora

Node1:

[oracle@uatdb01 ~]$ grep s_update_scan $CONTEXT_FILE

         <scanUpdateFlag oa_var="s_update_scan">TRUE</scanUpdateFlag>[oracle@uatdb01 ~]$ $ORACLE_HOME/appsutil/scripts/$CONTEXT_NAME/adautocfg.sh

App Node2:

To Enable SCAN-IP on APP node set db context parameter and run auto config after successful generation of db node tns with scan IP's

[oracle@uatdb01 ~]$ grep s_update_scan $CONTEXT_FILE

         <scanUpdateFlag oa_var="s_update_scan">TRUE</scanUpdateFlag>

[oracle@uatdb02 ~]$ grep s_update_scan $CONTEXT_FILE

         <scanUpdateFlag oa_var="s_update_scan">TRUE</scanUpdateFlag>

[applmgr@testapp02 ~]$ adautocfg.sh

[applmgr@testapp02 ~]$ vi $TNS_ADMIN/tnsnames.ora

[applmgr@testapp02 ~]$ tnsping $TWO_TASK

Conversion of EBS DB to RAC is till here only:

################################################################################################################

################################################################################################################

Below is just for informational purpose only

Configure Application Tier to the RAC Database

################################################################################################################

Experimental: If running adcfg now to complete Apps clone to 2 node RAC db follow below

free -h -w   ----(if required free up the memory of server with echo 3 command)

[root@ebstestapp ~]# screen -S dualfs 

su - applmgr

export TIMEDPROCESS_TIMEOUT=-1

export CONFIG_JVM_ARG="-Xms1024m -Xmx2048m"

export CONFIG_JVM_ARG="-Xms2048m -Xmx4096m"

echo $CONFIG_JVM_ARG

[applmgr@testapp02 bin]$ perl adcfgclone.pl appsTier dualfs

                     Copyright (c) 2002, 2015 Oracle Corporation

                        Redwood Shores, California, USA

                        Oracle E-Business Suite Rapid Clone

                                 Version 12.2

                      adcfgclone Version 120.63.12020000.56

Enter the APPS password :

Enter the Weblogic AdminServer password :

Do you want to add a node (yes/no) [no] :

Running: Context clone...

Log file located at /u01/UAT/fs2/EBSapps/comn/clone/bin/CloneContext_1128170652.log

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

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

Target System Database SID : UAT

Target System Database Server Node [testapp02] : uatdb01

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

Target System Port Pool [0-99] : 50

Checking the port pool 50

done: Port Pool 50 is free

Report file located at /u01/UAT/fs2/inst/apps/UAT_testapp02/admin/out/portpool.lst

RC-40201: Unable to connect to Database UAT.

Enter the Database listener port [1571] : 1521

RC-40201: Unable to connect to Database UAT.

Enter the Database listener port [1521] : 1521

RC-40201: Unable to connect to Database UAT.

Enter the Database listener port [1521] : 1521

RC-40201: Unable to connect to Database UAT.

update Listener.ora file on uatdb01 node and add below address list if application side adcfg unable to connect to UAT-DB for node1 connection when using same scan port

[oracle@uatdb01 ~]$ vi $ORACLE_HOME/network/admin/$CONTEXT_NAME/listener.ora

      (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = TCP)(HOST = uatdb01-vip.ahc.edu.sa)(PORT = 1521)))

      (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = TCP) (HOST = uatdb02-vip) (PORT = 1521)))

[grid@uatdb01 ~]$ lsnrctl stop

[grid@uatdb01 ~]$ lsnrctl start

Enter the Database listener port [1521] : 1521

UTL_FILE_DIR on database tier consists of the following directories.

1. /usr/tmp

2. /usr/tmp

3. /u01/app/oracle/product/12.1.0/dbhome_1/appsutil/outbound/UAT_uatdb01

4. /usr/tmp

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

The new APPL_TOP context file has been created :

################################################################################################################

################################################################################################################

Need further clarification and update on this two_task section

################################################################################################################

[applmgr@testapp02 ~]$

[applmgr@testapp02 ~]$ grep twotask $CONTEXT_FILE

         <TWO_TASK oa_var="s_tools_twotask" osd="unix" customized="yes">UAT_BALANCE</TWO_TASK>

         <CP_TWOTASK oa_var="s_cp_twotask" customized="yes">UAT_BALANCE</CP_TWOTASK>

         <TWO_TASK oa_var="s_weboh_twotask" osd="unix" customized="yes">UAT_BALANCE</TWO_TASK>

[applmgr@testapp02 ~]$

[applmgr@testapp02 admin]$ cat $TNS_ADMIN/tnsnames.ora

[applmgr@testapp02 ~]$ echo $TWO_TASK

UAT_BALANCE

[applmgr@testapp02 ~]$

[applmgr@testapp02 ~]$ adautocfg.sh

UAT_BALANCE

[applmgr@testapp02 ~]$

[applmgr@testapp02 ~]$ tnsping UAT

Attempting to contact (DESCRIPTION_LIST= (LOAD_BALANCE=NO) (FAILOVER=NO) 

[applmgr@testapp02 ~]$ tnsping $TWO_TASK

Attempting to contact (DESCRIPTION= (ADDRESS_LIST= (LOAD_BALANCE=YES) (FAILOVER=YES)

[applmgr@testapp02 ~]$ 

all tns connections in App server like UAT,UAT1,UAT1_FO,UAT2,UAT2_FO,UAT_FO,UAT_BALANCE,UAT_patch,UAT1_patch,UAT1_FO_patch,UAT2_patch,UAT2_FO_patch,UAT_FO_patch  should have Scan IP instead of hostname/hostname-vip

[applmgr@uatapp01 admin]$ tnsping UAT

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION= (ADDRESS=(PROTOCOL=tcp)(HOST=1.*.*.63)(PORT=1521)) (ADDRESS=(PROTOCOL=tcp)(HOST=1.*.*.64)(PORT=1521)) (ADDRESS=(PROTOCOL=tcp)(HOST=1.*.*.65)(PORT=1521)) (CONNECT_DATA= (SERVICE_NAME=UAT) (INSTANCE_NAME=UAT1)))

OK (0 msec)

[applmgr@uatapp01 admin]$

[applmgr@uatapp01 admin]$ tnsping UAT_BALANCE

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION= (ADDRESS_LIST= (LOAD_BALANCE=YES) (FAILOVER=YES) (ADDRESS=(PROTOCOL=tcp)(HOST=1.*.*.63)(PORT=1521)) (ADDRESS=(PROTOCOL=tcp)(HOST=1.*.*.64)(PORT=1521)) (ADDRESS=(PROTOCOL=tcp)(HOST=1.*.*.65)(PORT=1521))) (CONNECT_DATA= (SERVICE_NAME=UAT)))

OK (0 msec)

[applmgr@uatapp01 admin]$

Need Verify TNS Connection details on db and apps node(need to update form any source and add as sample for reference)

verify UAT,UAT_BALANCE are configured with SCAN or VIP Address details

Verified: updated with scan IP and scan Port 1521 for DB connections on tnsnames.ora of DB1,DB2 and App nodes when added remote_listener parameter in db

[oracle@uatdb01 ~]$ cat $TNS_ADMIN/tnsnames.ora

[oracle@uatdb02 ~]$ cat $TNS_ADMIN/tnsnames.ora

[applmgr@testapp02 ~]$ cat $TNS_ADMIN/tnsnames.ora

################################################################################################################

apps autoconfig faileing with ORA-04063 error txkGenADOPWrapper.pl because of invalid object "APPS.AD_ZD_ADOP"

Running AutoConfig generates ORA-04063: Package Body "APPS.AD_ZD_ADOP" Has Errors (Doc ID 2175698.1)

################################################################################################################

################################################################################################################

[root@testapp02 ~]# vi /u01/UAT/fs2/inst/apps/UAT_testapp02/admin/log/11291017/adconfig.log

Refer log file /u01/UAT/fs2/inst/apps/UAT_testapp02/logs/appl/rgf/TXK/txkGenADOPWrapper_Tue_Nov_29_10_20_20_2022/txkGenADOPWrapper_Tue_Nov_29_10_20_20_2022.log for details

Script Name    : txkGenADOPWrapper.pl

Script Version : 120.0.12020000.4

ERROR DESCRIPTION:

(*******FATAL ERROR*******

PROGRAM : (/u01/UAT/fs2/inst/apps/UAT_testapp02/admin/install/txkGenADOPWrapper.pl)

TIME    : Tue Nov 29 10:20:21 2022

FUNCTION: TXK::SQLPLUS::_doExecute [ Level 3 ]

MESSAGES:

SQLPLUS error: buffer=

SQL*Plus: Release 10.1.0.5.0 - Production on Tue Nov 29 10:20:21 2022

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

SQL> SQL> Connected.

SQL> SELECT ad_zd_adop.get_node_type('testapp02') FROM DUAL

*

ERROR at line 1:

ORA-04063: package body "APPS.AD_ZD_ADOP" has errors

select * from all_objects where status like 'INVALID' and owner='APPS' and object_name='AD_ZD_ADOP'

[oracle@uatdb01 UAT1_uatdb01]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Tue Nov 29 10:57:35 2022

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,

Advanced Analytics and Real Application Testing options

SQL> grant execute on DBMS_METADATA_UTIL to apps;

Grant succeeded.

SQL> alter package APPS.AD_ZD_ADOP compile body;

Package body altered.

SQL> commit;

Commit complete.

SQL>

################################################################################################################

Adding Load Balancer to Application Node with ssl certificate

after LB configured inform network team to proper policy to avoid WAF error (SQL ID)

to avoid ID block for html pages as "The requested URL was rejected. Please consult with your administrator."

Your support ID is xxxxxxxxxxx

LB need to configured with proper policy to avoid WAF block.

workaround 1: until then keep DBA PC IP address as an exception.

workaround 2:The policy is already in learning mode and its need more tuning because now its acting like new application, but the policy in blocking mode and to prevent from getting block page need to revert to Transparent mode required.

configure Load Balancer url for uatapp01 & uatpp02 servers with 8000 port with network team with ssl certificate

then change the below parameter in application context file.

[oracle@uatapp01 ~]$ grep httplistenparameter $CONTEXT_FILE

         <httplistenparameter oa_var="s_http_listen_parameter">8000</httplistenparameter>

[oracle@uatapp01 ~]$

[oracle@uatapp01 ~]$ adstpall.sh appps/apps

[applmgr@uatapp01 ~]$ grep prodahc $CONTEXT_FILE

grep -in 'prodahc\|activewebport\|activewebport\|webentryurlprotocol\|sslterminator' $CONTEXT_FILE

         <externURL oa_var="s_external_url">https://prodahc.ahc.edu.sa</externURL>

         <webentryhost oa_var="s_webentryhost">prodahc</webentryhost>

         <login_page oa_var="s_login_page">https://prodahc.ahc.edu.sa/OA_HTLM/AppsLogin</login_page>

      <activewebport oa_var="s_active_webport" oa_type="DUP_PORT" base="8000" step="1" range="-1" label="Active Web Port">443</activewebport>

         <webentryurlprotocol oa_var="s_webentryurlprotocol">https</webentryurlprotocol>

         <sslterminator oa_var="s_enable_sslterminator"/>

[applmgr@uatapp01 ~]$

[oracle@uatapp01 ~]$ adautocfg.sh

[oracle@uatapp01 ~]$ adstrtal.sh

now Login with loadbalncer url

################################################################################################################

Thanks for Reading

Regards,

Mohammed Areefuddin.

Suggested Topics :

Linux	DATABASE	RMAN	RAC	EBS
5.7 Install	DATABASE Install		11201 Install	R1226 Install
Open Filer Instl	Active DataGaurd	Rman DataGuard	12102 RAC INSTAL	R1229 M7 Clone
DNS in Linux 7	WIN-LINUX MIGRATE	RMAN DB CLONE	RAC DataGuard	Pluggable DB Clone
lpadmin cups	ASMCMD ASM Migrt	RMAN ASM Migrt	RAC DB Clone	appsutil for DB
	11204 M-Upgrade		RAC - RAC Clone	JDK JRE upgrade
	12102 M-Upgrade		12C-19C RAC UPGRADE	Add EBS Node
	Flashback DB			ADOP in R12.2.9
				12102 EBS UPGRADE
Linux Basics	DATABASE Basics	RMAN Basics	RAC Basic	EBS Basics