Re-Configuring Apex after host and DB SID change

Share on:

There are certain instances when you will have to make changes to your Oracle APEX server due to organizational requirements or other factors.

The following are the prevailing conditions:

  • Oracle HTTP Server
  • Oracle DB 11.2.0.3.0
  • Oracle Clusterware 19.10.0.0.0 for Oracle Restart
  • ORDS 3.0.11.180.12.34 installed
  • Apache Tomcat 7.0.59 installed

 

The assumption in this scenario is that the following have been completed:

  • Server Hostname changed
  • Server IP changed

 

To begin

Ensure the APEX services are shut down and you have conducted the necessary backups of the APPS install and Database (DB) 

 

Shutdown Apex Services on APEX PROD VM

  • Stop HTTP Server

[root@apexprod01 ~]# su – oraapex
Last login: Thu Jan 6 16:11:52 EST 2022 on pts/3
[oraapex@apexprod01 ~]$ $HTTP_INSTANCE/bin/opmnctl status

Processes in Instance: instance1
———————————+——————–+———+———
ias-component | process-type | pid | status
———————————+——————–+———+———
ohs1 | OHS | 27019 | Alive

[oraapex@apexprod01 ~]$ $HTTP_INSTANCE/bin/opmnctl stopproc ias-component=ohs1
opmnctl stopproc: stopping opmn managed processes…
[oraapex@apexprod01 ~]$ $HTTP_INSTANCE/bin/opmnctl stopall
opmnctl stopall: stopping opmn and all managed processes…
[oraapex@apexprod01 ~]$ $HTTP_INSTANCE/bin/opmnctl status
opmnctl status: opmn is not running.

 

  • Stop HTTP Server

[oraaxpd@apexprod01 ~]$ $CATALINA_HOME/bin/shutdown.sh
Using CATALINA_BASE: /home/tomcat/apache-tomcat-7.0.59
Using CATALINA_HOME: /home/tomcat/apache-tomcat-7.0.59
Using CATALINA_TMPDIR: /home/tomcat/apache-tomcat-7.0.59/temp
Using JRE_HOME: /
Using CLASSPATH: /home/tomcat/apache-tomcat-7.0.59/bin/bootstrap.jar:/home/tomcat/apache-tomcat-7.0.59/bin/tomcat-juli.jar

Check if Tomcat is still running

[oraaxpd@apexprod01 ~]$ ps -ef | grep tomcat
oraaxpd 26798 26304 0 21:03 pts/1 00:00:00 grep –color=auto tomcat
[oraaxpd@apexprod01 ~]$ exit
logout
[root@apexprod01 ~]# netstat -nlp | grep 8443
[root@apexprod01 ~]#

 

 

Re-configuration of Oracle Restart to support hostname change 

  • De-configure Oracle Restart Stack

As a root user, run the following

[root@apexprod01 install]# cd /u01/app/19c/grid/crs/install
[root@apexprod01 install]# ./roothas.sh -deconfig -force
Using configuration parameter file: /u01/app/19c/grid/crs/install/crsconfig_params
The log of current session can be found at:
/u01/app/oracle/crsdata/apexprod01/crsconfig/hadeconfig.log
PRCR-1070: Failed to check if resource ora.asm is registered
CRS-5168 : unable to communicate with ohasd
2022/01/08 14:17:08 CLSRSC-180: An error occurred while executing the command ‘/u01/app/19c/grid/bin/srvctl remove asm -force’
PRCR-1070: Failed to check if resource ora.ons is registered
CRS-5168: unable to communicate with ohasd
2022/01/08 14:17:09 CLSRSC-180: An error occurred while executing the command ‘/u01/app/19c/grid/bin/srvctl config ons’
CLSU-00107: operating system function: opendir; failed with error data: 2; at location: scrsearch1
CLSU-00101: operating system error message: No such file or directory
CLSU-00104: additional error information: cant open scr home dir scls_scr_getval
CRS-4639: Could not contact Oracle High Availability Services
CRS-4000: Command Stop failed, or completed with errors.
Redirecting to /bin/systemctl restart rsyslog.service
2022/01/08 14:17:22 CLSRSC-337: Successfully deconfigured Oracle Restart stack

 

  • Re-Configure Oracle Restart Stack

[root@apexprod01 grid]# cd /u01/app/19c/grid
[root@apexprod01 grid]# /u01/app/19c/grid/root.sh
Performing root user operation.

The following environment variables are set as:
ORACLE_OWNER= oracle
ORACLE_HOME= /u01/app/19c/grid

Enter the full pathname of the local bin directory: [/usr/local/bin]:
The file “dbhome” already exists in /usr/local/bin. Overwrite it? (y/n)
[n]: y
Copying dbhome to /usr/local/bin …
The file “oraenv” already exists in /usr/local/bin. Overwrite it? (y/n)
[n]: y
Copying oraenv to /usr/local/bin …
The file “coraenv” already exists in /usr/local/bin. Overwrite it? (y/n)
[n]: y
Copying coraenv to /usr/local/bin …

Entries will be added to the /etc/oratab file as needed by
Database Configuration Assistant when a database is created
Finished running generic part of root script.
Now product-specific root actions will be performed.
Using configuration parameter file: /u01/app/19c/grid/crs/install/crsconfig_params
The log of the current session can be found at:
/u01/app/oracle/crsdata/apexprod01/crsconfig/roothas_2022-01-08_02-40-31PM.log
Redirecting to /bin/systemctl restart rsyslog.service
LOCAL ADD MODE
Creating OCR keys for user ‘oracle’, privgrp ‘oinstall’..
The operation is successful.
LOCAL ONLY MODE
Successfully accumulated necessary OCR keys.
Creating OCR keys for user ‘root’, privgrp ‘root’..
The operation is successful.
CRS-4664: Node apexprod01 successfully pinned.
2022/01/08 14:41:00 CLSRSC-330: Adding Clusterware entries to file ‘oracle-ohasd.service’

apexprod01 2022/01/08 14:44:13 /u01/app/oracle/crsdata/apexprod01/olr/backup_20220108_144413.olr 1944883066
2022/01/08 14:44:13 CLSRSC-327: Successfully configured Oracle Restart for a standalone server

 

Start HAS

Switch to oracle user and start HAS

[root@apexprod01 grid]#
[root@apexprod01 grid]# su – oracle
Last login: Sat Jan 8 14:44:13 EST 2022 on pts/0
[oracle@apexprod01 ~]$ ps -ef | grep pmon
oracle 28459 28381 0 14:49 pts/0 00:00:00 grep –color=auto pmon
[oracle@apexprod01 ~]$ . oraenv
ORACLE_SID = [APXPRD] ? +ASM
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@apexprod01 ~]$ cd $ORACLE_HOME/bin/
[oracle@apexprod01 bin]$ ./crsctl stop has
CRS-2791: Starting shutdown of Oracle High Availability Services-managed resources on ‘apexprod01’
CRS-2673: Attempting to stop ‘ora.evmd’ on ‘apexprod01’
CRS-2677: Stop of ‘ora.evmd’ on ‘apexprod01’ succeeded
CRS-2793: Shutdown of Oracle High Availability Services-managed resources on ‘apexprod01’ has been completed
CRS-4133: Oracle High Availability Services has been stopped.
[oracle@apexprod01 bin]$ pwd
/u01/app/19c/grid/bin
[oracle@apexprod01 bin]$ ./crsctl start has
CRS-4123: Oracle High Availability Services has been started.
[oracle@apexprod01 bin]$

  • Enable Oracle Cluster Synchronization Service daemon to start automatically 

[oracle@apexprod01 bin]$ ./crsctl modify resource “ora.cssd” -init -attr “AUTO_START=1” -unsupported
[oracle@apexprod01 bin]$ ./crsctl stop has
CRS-2791: Starting shutdown of Oracle High Availability Services-managed resources on ‘apexprod01’
CRS-2673: Attempting to stop ‘ora.evmd’ on ‘apexprod01’
CRS-2677: Stop of ‘ora.evmd’ on ‘apexprod01’ succeeded
CRS-2793: Shutdown of Oracle High Availability Services-managed resources on ‘apexprod01’ has been completed
CRS-4133: Oracle High Availability Services has been stopped.

[oracle@apexprod01 bin]$ ./crsctl start has
CRS-4123: Oracle High Availability Services has been started.
[oracle@apexprod01 bin]$ ./crsctl check has
CRS-4638: Oracle High Availability Services is online
[oracle@apexprod01 bin]$ ./crsctl check css
CRS-4529: Cluster Synchronization Services is online

  • Add the Listener

Rename existing listeners and run the following

[oracle@apexprod01 bin]$ srvctl add the listener
PRCN-2061: Failed to add listener ora.LISTENER.lsnr
PRCN-2065: Ports 1521 are not available on the nodes given
PRCN-2067: Port 1521 is not available on nodes: apexprod01.eclipsys.com

Should you run into the error above, check whether there are any listener services running and kill them

[oracle@apexprod01 bin]$ ps -ef | grep tns
root 37 2 0 14:01 ? 00:00:00 [netns]
oracle 2030 28381 0 15:07 pts/0 00:00:00 grep –color=auto tns
oracle 12473 1 0 14:04 ? 00:00:00 /u01/app/oracle/product/19c/dbhome_1/bin/tnslsnr LISTENER -inherit

[oracle@apexprod01 bin]$ kill -9 12473
[oracle@apexprod01 bin]$ ps -ef | grep tns
root 37 2 0 14:01 ? 00:00:00 [netns]
oracle 2165 28381 0 15:07 pts/0 00:00:00 grep –color=auto tns

[oracle@apexprod01 bin]$ netstat -nltp
(Not all processes could be identified, non-owned process info
will not be shown, and you would have to be root to see it all.)
Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address Foreign Address State PID/Program name
tcp 0 0 192.168.122.1:53 0.0.0.0:* LISTEN –
tcp 0 0 0.0.0.0:22 0.0.0.0:* LISTEN –
tcp 0 0 127.0.0.1:2016 0.0.0.0:* LISTEN 31115/oraagent.bin
tcp6 0 0 :::22 :::* LISTEN –
tcp6 0 0 ::1:2016 :::* LISTEN 31115/oraagent.bin
tcp6 0 0 :::3872 :::* LISTEN 31770/java

[oracle@apexprod01 bin]$ . oraenv
ORACLE_SID = [APXPRD] ? +ASM
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@apexprod01 bin]$ cd $ORACLE_HOME/bin
[oracle@apexprod01 bin]$ pwd
/u01/app/19c/grid/bin
[oracle@apexprod01 bin]$ srvctl add listener
[oracle@apexprod01 bin]$
[oracle@apexprod01 bin]$ srvctl start listener
[oracle@apexprod01 bin]$ srvctl status listener
Listener LISTENER is enabled
Listener LISTENER is running on node(s): apexprod01

 

  • Create an ASM PFILE to help start ASM instance

[oracle@apexprod01 ~]$ cd $ORACLE_HOME/dbs
[oracle@apexprod01 dbs]$ pwd
/u01/app/19c/grid/dbs

[oracle@apexprod01 dbs]$ vi init+ASM.ora

+ASM.asm_diskgroups=’DATA’,’FLASH’,’RECO’,’REDO’
*.asm_diskstring=’/dev/oracleasm/disks/*’
*.asm_power_limit=1
*.instance_type=’asm’

Start ASM instance with previously created PFILE

[oracle@apexprod01 dbs]$ sqlplus / as sysasm

SQL*Plus: Release 19.0.0.0.0 – Production on Sat Jan 4 22:10:08 2019
Version 19.10.0.0.0

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

Connected to an idle instance.

SQL> startup pfile=’/u01/app/19c/grid/dbs/init+ASM.ora’;
ASM instance started

Total System Global Area 1137173312 bytes
Fixed Size 8905536 bytes
Variable Size 1103101952 bytes
ASM Cache 25165824 bytes
ASM diskgroups mounted
ASM diskgroups volume enabled
SQL> select instance_name,status from gv$Instance;

INSTANCE_NAME STATUS
—————- ————
+ASM STARTED

SQL> select path from v$asm_disk;

PATH
——————————————————————————–
/dev/oracleasm/disks/REDO_01
/dev/oracleasm/disks/RECO_01
/dev/oracleasm/disks/FLASH_01
/dev/oracleasm/disks/DATA_03
/dev/oracleasm/disks/DATA_02
/dev/oracleasm/disks/DATA_04
/dev/oracleasm/disks/DATA_01

7 rows selected.

Create SPFILE from PFILE

SQL> create spfile from pfile;
create spfile from pfile
*
ERROR at line 1:
ORA-29786: Oracle Restart attribute GET failed with error [Attribute ‘SPFILE’
sts[200] lsts[0]]

Should you encounter the error above, add the ASM cluster resource and then retry SPFILE creation

[oracle@apexprod01 dbs]$ srvctl config asm
PRCR-1001: Resource ora.asm does not exist
[oracle@apexprod01 dbs]$ srvctl add asm

[oracle@apexprod01 dbs]$ srvctl modify asm -diskstring /dev/oracleasm/disks
[oracle@apexprod01 dbs]$ sqlplus / as sysasm

SQL*Plus: Release 19.0.0.0.0 – Production on Sat Jan 8 22:38:57 2022
Version 19.10.0.0.0

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

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 – Production
Version 19.10.0.0.0

SQL> create spfile from pfile;

File created.

SQL>

Modify asm config to include SPFILE location

[oracle@apexprod01 dbs]$ asmcmd
ASMCMD> ls
DATA/
FLASH/
RECO/
REDO/
ASMCMD> cd DATA
ASMCMD> cd ASM
ASMCMD> ls
ASMPARAMETERFILE/
ASMCMD> cd ASMPARAMETERFILE/
ASMCMD> ls
REGISTRY.253.1093474283
ASMCMD> pwd
+DATA/ASM/ASMPARAMETERFILE

oracle@apexprod01 dbs]$ srvctl modify asm -spfile +DATA/ASM/ASMPARAMETERFILE/REGISTRY.326.109347456783
[oracle@apexprod01 dbs]$ srvctl config asm
ASM home: <CRS home>
Password file:
Backup of Password file:
ASM listener: LISTENER
Spfile: +DATA/ASM/ASMPARAMETERFILE/REGISTRY.326.109347456783
ASM diskgroup discovery string: /dev/oracleasm/disks/*

 

Restart HAS

[oracle@apexprod01 dbs]$ crsctl stop has
CRS-2791: Starting shutdown of Oracle High Availability Services-managed resources on ‘apexprod01’
CRS-2673: Attempting to stop ‘ora.evmd’ on ‘apexprod01’
CRS-2673: Attempting to stop ‘ora.LISTENER.lsnr’ on ‘apexprod01’
CRS-2677: Stop of ‘ora.LISTENER.lsnr’ on ‘apexprod01’ succeeded
CRS-2677: Stop of ‘ora.evmd’ on ‘apexprod01’ succeeded
CRS-2673: Attempting to stop ‘ora.cssd’ on ‘apexprod01’
CRS-2677: Stop of ‘ora.cssd’ on ‘apexprod01’ succeeded
CRS-2793: Shutdown of Oracle High Availability Services-managed resources on ‘apexprod01’ has been completed
CRS-4133: Oracle High Availability Services has been stopped.

[oracle@apexprod01 dbs]$ crsctl start has
CRS-4123: Oracle High Availability Services has been started.

           Start ASM

[oracle@apexprod01 dbs]$ srvctl start asm
[oracle@apexprod01 dbs]$ srvctl status asm
ASM is running on apexprod01
[oracle@apexprod01 dbs]$ crsctl stat res -t
——————————————————————————–
Name Target State Server State details
——————————————————————————–
Local Resources
——————————————————————————–
ora.DATA.dg
ONLINE ONLINE apexprod01 STABLE
ora.FLASH.dg
ONLINE ONLINE apexprod01 STABLE
ora.LISTENER.lsnr
ONLINE ONLINE apexprod01 STABLE
ora.RECO.dg
ONLINE ONLINE apexprod01 STABLE
ora.REDO.dg
ONLINE ONLINE apexprod01 STABLE
ora.asm
ONLINE ONLINE apexprod01 Started, STABLE
ora.ons
OFFLINE OFFLINE apexprod01 STABLE
——————————————————————————–
Cluster Resources
——————————————————————————–
ora.cssd
1 ONLINE ONLINE apexprod01 STABLE
ora.diskmon
1 OFFLINE OFFLINE STABLE
ora.evmd
1 ONLINE ONLINE apexprod01 STABLE

 

Change DB SID

  • Startup DB in mount mode

SYS@APXPRD > select dbid,open_mode from v$database;

DBID OPEN_MODE
———- ——————–
54678976 READ WRITE

SYS@APXPRD > shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SYS@APXPRD > startup mount
ORACLE instance started.

Total System Global Area 1.2827E+10 bytes
Fixed Size 2240344 bytes
Variable Size 9865003176 bytes
Database Buffers 2952790016 bytes
Redo Buffers 7335936 bytes
Database mounted.
SYS@APXPRD > quit

  • Run NID utility to change DB NAME where “apxnprd” is the future instance name

[oracle@apexprod01 bin]$ nid target=sys dbname=apxnprd setname=YES

DBNEWID: Release 11.2.0.3.0 – Production on Mon Jan 10 12:34:42 2022

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

Password:
Connected to database APXPRD (DBID=67546789)

Connected to server version 11.2.0

Control Files in database:
+RECO/APXPRD/controlfile/control01.ctl
+REDO/APXPRD/controlfile/control02.ctl
+DATA/APXPRD/controlfile/control03.ctl

Change database name of database APXPRD to apxnprd? (Y/[N]) => Y

Proceeding with operation
Changing database name from APXPRD to apxnprd
Control File +RECO/APXPRD/controlfile/control01.ctl – modified
Control File +REDO/APXPRD/controlfile/control02.ctl – modified
Control File +DATA/APXPRD/controlfile/control03.ctl – modified
Datafile +DATA/APXPRD/system01.db – wrote new name
Datafile +DATA/APXPRD/sysaux01.db – wrote new name
Datafile +DATA/APXPRD/undotbs01.db – wrote new name
Datafile +DATA/APXPRD/users01.db – wrote new name
Datafile +DATA/APXPRD/xmldb_tbs01.db – wrote new name
Datafile +DATA/APXPRD/apex_tbs01.db – wrote new name
Datafile +DATA/APXPRD/apex_245240916148259127.db – wrote new name
Datafile +DATA/APXPRD/apex_245240916148259127_1.db – wrote new name
Datafile +DATA/APXPRD/apex_245240916148259127_2.db – wrote new name
Datafile +DATA/APXPRD/apex_245240916148259127_3.db – wrote new name
Datafile +DATA/APXPRD/apex_245240916148259127_4.db – wrote new name
Datafile +DATA/APXPRD/apex_245240916148259127_5.db – wrote new name
Datafile +DATA/APXPRD/tempfile/temp.285.107614779 – wrote new name
Datafile +DATA/APXPRD/tempfile/temp.284.107257847 – wrote new name
Control File +RECO/APXPRD/controlfile/control01.ctl – wrote new name
Control File +REDO/APXPRD/controlfile/control02.ctl – wrote new name
Control File +DATA/APXPRD/controlfile/control03.ctl – wrote new name
Instance shut down

Database name changed to apxnprd.
Modify the parameter file and generate a new password file before restarting.
Successfully changed database name.
DBNEWID – Completed successfully.

[oracle@apexprod01 bin]$

Shutdown and start DB in mount mode

[oracle@apexprod01 dbs]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Mon Jan 10 12:49:24 2022

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

Connected to an idle instance.

SYS@APXPRD > shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

 

SYS@APXPRD > startup mount
ORACLE instance started.

Total System Global Area 1.2827E+10 bytes
Fixed Size 2240344 bytes
Variable Size 9663676584 bytes
Database Buffers 3154116608 bytes
Redo Buffers 7335936 bytes
Database mounted.

Modify SPFILE entries

SYS@APXNPRD > alter system set db_name=APXNPRD scope=spfile;

System altered.

SYS@APXNPRD > ALTER SYSTEM SET LOCAL_LISTENER='(ADDRESS=(PROTOCOl=TCP)(HOST=apexprod01.eclipsys.com)(PORT=1521))’ SCOPE=BOTH;

System altered.

SYS@APXNPRD > shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

Create Password File

[oracle@apexprod01 dbs]$ pwd
/u01/app/oracle/product/11.2.0/dbs
[oracle@apexprod01 dbs]$ echo $ORACLE_HOME
/u01/app/oracle/product/11.2.0
[oracle@apexprod01 dbs]$ orapwd file=$ORACLE_HOME/dbs/orapwAPXNPRD entries=10

Enter password for SYS:
[oracle@apexprod01 dbs]$ ls -ltr
total 35892
-rw-r—–. 1 oracle oinstall 2560 Jan 10 13:27 orapwAPXNPRD

 

Rename SPFILE to reflect the new SID

[oracle@apexprod01 dbs]$ mv spfileAPXPRD.ora spfileAPXNPRD.ora
-rw-r—–. 1 oracle oinstall 6656 Jan 10 13:25 spfileAPXNPRD.ora

 

Modify .bash_profile and oratab to reflect new SID

[oracle@apexprod01 ~]$ cp .bash_profile .bash_profile_PREFERRED_DATE
[oracle@apexprod01 ~]$ vi .bash_profile

ORACLE_SID=APXNPRD
export ORACLE_SID

 

[oracle@apexprod01 ~]$ vi /etc/oratab

APXNPRD:/u01/app/oracle/product/11.2.0:N

 

Modify host file to contain the updated hostname information

[root@apexprod01 ~]# cp /etc/hosts /etc/hosts_PREFERRED_DATE
[root@apexprod01 ~]# vi /etc/hosts
[root@apexprod01 ~]# more /etc/hosts
# Created by DB/RAC OVM at Wed Apr 10 16:11:26 EST 2021
127.0.0.1 localhost localhost.localdomain localhost4
::1 localhost localhost.localdomain localhost6

xx.xx.xx.xx apexprod01.eclipsys.com apexprod01

 

Modify Listener as follows

#Backup file is /u01/app/oracle/crsdata/apexprod01/output/listener.ora.bak.apexprod01.oracle line added by Agent
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = apexprod01.eclipsys.com)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)

SID_LIST_listener=
(SID_LIST=
(SID_DESC=
(GLOBAL_DBNAME=APXNPRD)
(SID_NAME=APXNPRD)
(ORACLE_HOME=/u01/app/oracle/product/11.2.0)
)
)

ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON # line added by Agent
VALID_NODE_CHECKING_REGISTRATION_LISTENER=ON # line added by Agent

 

Restart Listener

[oracle@apexprod01 admin]$ srvctl stop listener
[oracle@apexprod01 admin]$ srvctl start listener
[oracle@apexprod01 admin]$ srvctl status listener
Listener LISTENER is enabled
Listener LISTENER is running on node(s): apexprod01
[oracle@apexprod01 admin]$

 

Modify tnsnames file to reflect SID changes

APXNPRD =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = apexprod01.eclipsys.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = APXNPRD)
)
)

 

Check the modified instance name if it has changed

SYS@APXNPRD > select instance_name from v$instance;

INSTANCE_NAME
—————-
APXNPRD

SYS@APXNPRD > select dbid,open_mode from v$database;

DBID OPEN_MODE
———- ——————–
6578997 READ WRITE

 

Now that the Instance name has been changed, we need to focus on changing the DBID as follows

[oracle@apexprod01 admin]$ nid TARGET=sys/password@APXNPRD

DBNEWID: Release 11.2.0.3.0 – Production on Mon Jan 10 14:51:38 2022

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

Password:
Connected to database APXNPRD (DBID=786578765)

Connected to server version 11.2.0

Control Files in database:
+RECO/testdb/controlfile/control01.ctl
+REDO/testdb/controlfile/control02.ctl
+DATA/testdb/controlfile/control03.ctl

Change database ID of database APXNPRD? (Y/[N]) => Y

Proceeding with operation
Changing database ID from 786578765 to 1201880829
Control File +RECO/testdb/controlfile/control01.ctl – modified
Control File +REDO/testdb/controlfile/control02.ctl – modified
Control File +DATA/testdb/controlfile/control03.ctl – modified
Datafile +DATA/testdb/system01.db – dbid changed
Datafile +DATA/testdb/sysaux01.db – dbid changed
Datafile +DATA/testdb/undotbs01.db – dbid changed
Datafile +DATA/testdb/users01.db – dbid changed
Datafile +DATA/testdb/xmldb_tbs01.db – dbid changed
Datafile +DATA/testdb/apex_tbs01.db – dbid changed
Datafile +DATA/testdb/repdvold01.db – dbid changed
Datafile +DATA/testdb/repdv01.db – dbid changed
Datafile +DATA/testdb/repdv2_01.db – dbid changed
Datafile +DATA/testdb/indxdv_01.db – dbid changed
Datafile +DATA/testdb/indxdv2_01.db – dbid changed
Datafile +DATA/testdb/apps_ts_tx_data01.db – dbid changed
Datafile +DATA/testdb/apps_ts_tx_idx01.db – dbid changed
Datafile +DATA/testdb/apps_ts_tx_idx02.db – dbid changed
Datafile +DATA/testdb/apps_ts_tx_data02.db – dbid changed
Datafile +DATA/testdb/apps_ts_tx_data03.db – dbid changed
Datafile +DATA/testdb/apps_ts_tx_data04.db – dbid changed
Datafile +DATA/testdb/apps_ts_tx_data05.db – dbid changed
Datafile +DATA/testdb/apps_ts_tx_data06.db – dbid changed
Datafile +DATA/testdb/repdv2_02.db – dbid changed
Datafile +DATA/testdb/perfstat_data01.db – dbid changed
Datafile +DATA/testdb/apex_245240916148259127.db – dbid changed
Datafile +DATA/testdb/apex_245240916148259127_1.db – dbid changed
Datafile +DATA/testdb/apex_245240916148259127_2.db – dbid changed
Datafile +DATA/testdb/apex_245240916148259127_3.db – dbid changed
Datafile +DATA/testdb/apex_245240916148259127_4.db – dbid changed
Datafile +DATA/testdb/apex_245240916148259127_5.db – dbid changed
Datafile +DATA/testdb/tempfile/temp.285.107614779 – dbid changed
Datafile +DATA/testdb/tempfile/temp.284.107257847 – dbid changed
Control File +RECO/testdb/controlfile/control01.ctl – dbid changed
Control File +REDO/testdb/controlfile/control02.ctl – dbid changed
Control File +DATA/testdb/controlfile/control03.ctl – dbid changed
Instance shut down

Database ID for database APXNPRD changed to 67897654.
All previous backups and archived redo logs for this database are unusable.
The database is not aware of previous backups and archived logs in Recovery Area.
The database has been shut down, and open the database with the RESETLOGS option.
Succesfully changed database ID.
DBNEWID – Completed succesfully.

 

Restart database

[oracle@apexprod01 admin]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Mon Jan 10 14:52:25 2022

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

Connected to an idle instance.

SYS@APXNPRD > shutdown immediate
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
Linux-x86_64 Error: 2: No such file or directory
SYS@APXNPRD > startup mount
ORACLE instance started.

Total System Global Area 1.2827E+10 bytes
Fixed Size 2240344 bytes
Variable Size 7113539752 bytes
Database Buffers 5704253440 bytes
Redo Buffers 7335936 bytes
Database mounted.
SYS@APXNPRD > ALTER DATABASE OPEN RESETLOGS;

Database altered.

 

Check DBID and instance name to confirm changes. you will realise below that instance ID has also changed

SYS@APXNPRD > select dbid,open_mode from v$database;

DBID OPEN_MODE
———- ——————–
67897654 READ WRITE

SYS@APXNPRD > select instance_name from v$instance;

INSTANCE_NAME
—————-
APXNPRD

 

Reconfiguration of Apex to point to new DB SID

  • Ensure Oracle HTTP server and tomcat are down

$HTTP_INSTANCE/bin/opmnctl stopall
$HTTP_INSTANCE/bin/opmnctl status

$CATALINA_HOME/bin/shutdown.sh

 

  • Clear unwanted directories and files from locations below

[oraaxpd@apexprod01 ords]$ cd /home/tomcat/ords
[oraaxpd@apexprod01 ords]$ rm -rf *.war
[oraaxpd@apexprod01 ords]$ rm -rf /home/tomcat/ords/conf/*

[oraaxpd@apexprod01 ords]$ cd /home/tomcat/apache-tomcat-7.0.59/webapps/
[oraaxpd@apexprod01 webapps]$ rm -rf *.war
[oraaxpd@apexprod01 webapps]$ rm -rf apex ords

 

  • Redeploy ords

Unzip ords.3.0.11.180.12.34.zip

[oraaxpd@apexprod01 webapps]$ cd /home/tomcat/ords
[oraaxpd@apexprod01 ords]$ unzip ords.3.0.11.180.12.34.zip

 

Rename ords.war to apex.war

cp ords.war apex.war

Begin deployment

[oraaxpd@apexprod01 ords]$ java -jar apex.war install advanced
Enter the name of the database server [localhost]:apexprod01
Enter the database listen port [1521]:
Enter 1 to specify the database service name, or 2 to specify the database SID [1]:2
Enter the database SID [xe]:APXNPRD
Enter 1 if you want to verify/install Oracle REST Data Services schema or 2 to skip this step [1]:
Enter the database password for ORDS_PUBLIC_USER:
Confirm password:
Enter 1 if you want to use PL/SQL Gateway or 2 to skip this step.
If using Oracle Application Express or migrating from mod_plsql then you must enter 1 [1]:
Enter the PL/SQL Gateway database user name [APEX_PUBLIC_USER]:
Enter the database password for APEX_PUBLIC_USER:
Confirm password:
Enter 1 to specify passwords for Application Express RESTful Services database users (APEX_LISTENER, APEX_REST_PUBLIC_USER) or 2 to skip this step [1]:
Enter the database password for APEX_LISTENER:
Confirm password:
Enter the database password for APEX_REST_PUBLIC_USER:
Confirm password:
Jan 10, 2022 7:24:31 PM
INFO: Updated configurations: defaults, apex, apex_pu, apex_al, apex_rt
Jan 10, 2022 7:24:31 PM oracle.dbtools.rt.config.setup.SchemaSetup install
INFO: Oracle REST Data Services schema version 3.0.11.180.12.34 is installed.
Enter 1 if you wish to start in standalone mode or 2 to exit [1]:2

 

Copy deployed APEX war file to apache deployment location

cp apex.war /home/tomcat/apache-tomcat-7.0.59/webapps/

 

Startup Oracle HTTP server and Tomcat services

$HTTP_INSTANCE/bin/opmnctl startall
[oraaxpd@apexprod01 ords]$ $HTTP_INSTANCE/bin/opmnctl status

Processes in Instance: instance1
———————————+——————–+———+———
ias-component | process-type | pid | status
———————————+——————–+———+———
ohs1 | OHS | 24619 | Alive

[oraaxpd@apexprod01 ords]$ $CATALINA_HOME/bin/startup.sh
Using CATALINA_BASE: /home/tomcat/apache-tomcat-7.0.59
Using CATALINA_HOME: /home/tomcat/apache-tomcat-7.0.59
Using CATALINA_TMPDIR: /home/tomcat/apache-tomcat-7.0.59/temp
Using JRE_HOME: /
Using CLASSPATH: /home/tomcat/apache-tomcat-7.0.59/bin/bootstrap.jar:/home/tomcat/apache-tomcat-7.0.59/bin/tomcat-juli.jar
Tomcat started.

 

  • Test proper functioning of APEX

Navigate to the Tomcat url in the browser

 

https://apexprod01.eclipsys.com:8443/

 

Apache default page

 

Navigate to APEX URL

https://apexprod01.eclipsys.com:8443/apex

 

 

Apex login page

 

 

Share on:

More from this Author

Troubleshooting Oracle Database Environments Navigating Through inventory.xml Corruption Issues

Troubleshooting Oracle Database Environments: Navigating Through inventory.xml Corruption Issues

Encountering errors can often lead us down a rabbit hole of troubleshooting and investigative work. Recently, a scenario encountered highlighted the ... Read More

Troubleshooting and Resolving ORA 00283 and ORA 28374 Errors During Remote Pluggable Database Cloning

Troubleshooting and Resolving ORA-00283 and ORA-28374 Errors During Remote Pluggable Database Cloning

Introduction: Creating a clone of a remote pluggable database across a database link can be a powerful tool for managing and replicating data across ... Read More

Back to Top