Oracle Applications and Fusion DBA concepts: February 2021

Thursday, February 18, 2021

Steps to change the Oracle Web Logic Server Administration User Password in 12.2.x

Steps -

1. Please check whether the "boot.properties" file exists under $EBS_DOMAIN_HOME/servers/<server name>/data/nodemanager directory. If yes, take a backup of the file, manually remove it.
Verified the all the managed servers under server directory and found one managed server having boot.properties files under “<server_name>/data/nodemanager”. So moved the file as backup.
[applmgr@testapp1 config]$ cd /HW/TST/app/fs2/FMW_Home/user_projects/domains/EBS_domain_UAT/servers/
[applmgr@testapp1 servers]$ ls
AdminServer domain_bak forms-c4ws_server2 forms_server2 oacore_server3 oafm_server1 AdminServerTag forms-c4ws_server1 forms_server1 oacore_server1 oacore_server4 oafm_server2
[applmgr@testapp1 nodemanager]$ pwd
/HW/TST/app/fs2/FMW_Home/user_projects/domains/EBS_domain_UAT/servers/oacore_server3/data/nodemanager
[applmgr@testapp1 nodemanager]$ ls –ltr boot.properties
-rwxrwxrwx 1 applmgr dba 174 Dec 3 18:58 boot.properties
[applmgr@testapp1 nodemanager]$ mv boot.properties boot.properties_old
[applmgr@testapp1 nodemanager]$ ls –ltr boot.properties*
-rwxrwxrwx 1 applmgr dba 174 Dec 3 18:58 boot.properties_old

2. Shut down all application tier services except the Admin Server.

Note: The instructions that follow should be performed on the run file system. The password change will be automatically propagated to the patch file system during the next adop prepare phase or fs_clone operation.
On the primary node, run the command:
$ <ADMIN_SCRIPTS_HOME>/adstpall.sh -skipNM -skipAdmin
On all secondary nodes, run the command:
$ <ADMIN_SCRIPTS_HOME>/adstpall.sh
Node 1:
[applmgr@testapp1 scripts]$ ./adstpall.sh -skipNM –skipAdmin
You are running adstpall.sh version 120.22.12020000.7
Enter the APPS username: apps
Enter the APPS password:
Enter the WebLogic Server password:
The logfile for this session is located at /HW/TST/inst/fs2/inst/apps/UAT_testapp1/logs/appl/admin/log/adstpall.log
Skipping Admin Server shutdown as option -skipAdmin is passed
Skipping Node Manager shutdown as option -skipNM is passed
……..
All enabled services on this node are stopped.
adstpall.sh:Exiting with status 0
adstpall.sh: check the logfile /HW/TST/inst/fs2/inst/apps/UAT_testapp1/logs/appl/admin/log/adstpall.log for more information
Note: Node manager & Admin server should be up and reaming services should be down.
Node 2:
[applmgr@app1 scripts]$ ./adstpall.sh apps/XXXXX
You are running adstpall.sh version 120.22.12020000.7
Enter the WebLogic Server password:
The logfile for this session is located at /HW/TST/inst/fs2/inst/apps/UAT_app1/logs/appl/admin/log/adstpall.log

****************************************************
All enabled services on this node are stopped.
adstpall.sh:Exiting with status 0
adstpall.sh: check the logfile /HW/TST/inst/fs2/inst/apps/UAT_app1/logs/appl/admin/log/adstpall.log for more information ...
Note: All the services should be down.
3. Change the Oracle WebLogic Server Administration User password by performing the following steps on the run file system of the primary node.
Source the environment on the run file system if not sourced previously and run the below command.
$ perl $FND_TOP/patch/115/bin/txkUpdateEBSDomain.pl -action=updateAdminPassword
[applmgr@testapp1 ~]$ perl $FND_TOP/patch/115/bin/txkUpdateEBSDomain.pl -action=updateAdminPassword
Program: txkUpdateEBSDomain.pl started at Wed Feb 15 22:24:41 2017
AdminServer will be re started after changing WebLogic Admin Password
All Mid Tier services should be SHUTDOWN before changing WebLogic Admin Password
Confirm if all Mid Tier services are in SHUTDOWN state. Enter "Yes" to proceed or anything else to exit: Yes
Enter the full path of Applications Context File [DEFAULT - /HW/TST/inst/fs2/inst/apps/UAT_testapp1/appl/admin/UAT_testapp1.xml]: /HW/TST/inst/fs2/inst/apps/UAT_testapp1/appl/admin/UAT_testapp1.xml
Enter the WLS Admin Password: current weblogic password
Enter the new WLS Admin Password: new password
Enter the APPS user password:
……..
adadminsrvctl.sh: check the logfile /HW/TST/inst/fs2/inst/apps/UAT_testapp1/logs/appl/admin/log/adadminsrvctl.txt for more information ...
*************** IMPORTANT ****************
WebLogic Admin Password is changed.
Restart all application tier services using control scripts.
********************************************
Program: txkUpdateEBSDomain.pl completed at Wed Feb 15 22:39:35 2017
Note: Please check the weblogic console login using new password.

4. Start all services on all nodes, using the command
Node 1:
[applmgr@testapp1 scripts]$ ./adstrtal.sh apps/xxxxx
You are running adstrtal.sh version 120.24.12020000.11
Enter the WebLogic Server password:
The logfile for this session is located at /HW/TST/inst/fs2/inst/apps/UAT_testapp1/logs/appl/admin/log/adstrtal.log
Node 2:
[applmgr@app1 scripts]$ ./adstrtal.sh apps/xxxx
You are running adstrtal.sh version 120.24.12020000.11
Enter the WebLogic Server password:
The logfile for this session is located at /HW/TST/inst/fs2/inst/apps/UAT_app1/logs/appl/admin/log/adstrtal.log

Issue after password change: We are unable to open the forms

Solution: Doc id 2131642.1 regenerated the form files. Then issues got resolved.

[applmgr@app1 ~]$ adadmin
1. Generate Applications Files menu
Enter your choice [5] : 1
2. Generate form files
Enter your choice [5] : 2
Enter the number of workers [3] : 6
Do you want to generate Oracle Forms objects using this character set [Yes] ?
Do you want to regenerate Oracle Forms PL/SQL library files [Yes] ?
Do you want to regenerate Oracle Forms menu files [Yes] ?
Do you want to regenerate Oracle Forms executable files [Yes] ?
Enter list of products ('all' for all products) [all] :
Generate specific forms objects for each selected product [No] ?
Enter list of languages ('all' for all of the above) [all] :
You selected the following languages: US
Is this the correct set of languages [Yes] ?

References -
http://docs.oracle.com/cd/E26401_01/doc.122/e22953/T174296T589913.htm#wls_passwd_su
Doc ID 2178617.1: Running txkUpdateEBSDomain.pl -action=updateAdminPassword Command Completes Successfully, But New Weblogic Password Is Not Recognized
Pre-req: txkUpdateEBSDomain.pl command is available only if TXK 7 is applied.

The web services URL for XML Gateway it is returning a 404.

ISSUE:
the web services URL for XML Gateway it is returning a 404.

STEP1: Set profile Applications SSO Login Types (APPS_SSO_LOCAL_LOGIN) for ASADMIN user to 'Local'.

STEP2: Change the ASADMIN password from User Management as follows
a) Log in using sysadmin/<sysadmin_pw>
b) Choose responsibility 'User Management'
c) Find user ASADMIN
d) Reset Password (‘ASADMIN’)
e) Remove Active To date if set.
f) Apply

STEP3: Confirm the definition for ASADMIN user in $INST_TOP/ora/10.1.3/j2ee/oafm/application-deployments/oafm/data-sources.xml is defined correctly:
user="ASADMIN"
password="->ASADMIN"

STEP4: Change password for ASADMIN user in $ORA_CONFIG_HOME/10.1.3/j2ee/oafm/config/system-jazn-data.xml to be !<New ASADMIN Password> (! in front is mandatory)
Mention: ! in front of password is mandatory and this sign means that password will be encrypted

STEP5: Shutdown and restart the OAFM container and re-test:
sh $ADMIN_SCRIPTS_HOME/adoafmctl.sh stop
sh $ADMIN_SCRIPTS_HOME/adoafmctl.sh start

References:
• Verify that ASADMIN EBS User for ISG and XML Gateway JDBC Connections to the Database is Correctly Implemented. (Doc ID 1089197.1)
• Cannot Start OXTA After Upgrade To R12.1.x (Doc ID 1087499.1)
• OXTA Does Not Start after upgrading database to 12.x (Doc ID 2005459.1)

ORA-04031: unable to allocate 32 bytes of shared memory

Issue: ORA-04031: unable to allocate ORA-04031: unable to allocate 32 bytes of shared memory ("shared pool","unknown object","KGLH0^d60c1b2d","kglHeapInitialize:temp")

Solution: Download and apply Patch:13456573 for non-Exadata database.

References -
ORA-04031 in 11g & 11gR2, Excess "KGH: NO ACCESS" Memory Allocation ( Doc ID 1127833.1 )
Severe Performance Problems Due to Sudden drop/resize/shrink of buffer cache ( Doc ID 1344228.1 )
ORA-4031 Against Shared Pool With Huge Memory Allocations To KGLH0 ( Doc ID 1621829.1 )
ORA-04031 Due To Excessive Growth In KGLH0 Heaps ( Doc ID 1351675.1 )

adoafm.ctl unable to start

Error:
opmn id=:6200
0 of 1 processes started.
ias-instance id=app1
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
--------------------------------------------------------------------------------
ias-component/process-type/process-set:
default_group/oafm/default_group/
Error
--> Process (index=1,uid=2117948558,pid=4957)
time out while waiting for a managed process to start
Log:
/u01/app/applmgr/inst/apps/app1/logs/ora/10.1.3/opmn/default_group~oafm~default_group~1.log

11/20/16-04:24:18 :: adoafmctl.sh: exiting with status 152

SOLUTION
1. Manually edit the $ORA_CONFIG_HOME/10.1.3/j2ee/oafm/config/system-jazn-data.xml file and change the following section for the ASADMIN user:
FROM:
<user>
<name>ASADMIN</name>
<display-name>Default Apps SOA User</display-name>
<description>Used by SOAProvider for DB connection</description>
<credentials>asadmin</credentials>
</user>
TO:
<user>
<name>ASADMIN</name>
<display-name>Default Apps SOA User</display-name>
<description>Used by SOAProvider for DB connection</description>
<credentials>!asadmin</credentials>
</user>
NOTE: The change is adding the exclamation mark (!) "!" before the password.

2. Then try starting OAFM and check results:
$ADMIN_SCRIPTS_HOME/adoafmctl.sh start
Reference note id:1599784.1

Unable to bring up DATA diskgroup in ASM

Error :
ORA-15032: not all alterations performed
ORA-15040: diskgroup is incomplete
ORA-15042: ASM disk "1" is missing from group number "2"
Solution :
1.) ls -ltr /dev/sdb*
2.) As root,
- cd <asm home>/bin and create a shell file called kfed.sh using the following script
- And change the permission as executable - ex) chmod 755 kfed.sh
vi kfed.sh
#! /bin/sh
rm /tmp/kfed_DH.out /tmp/kfed_FS.out /tmp/kfed_BK.out /tmp/kfed_FD.out /tmp/kfed_DD.out
for i in `ls kfed read /dev/sd*`
do
echo $i >> /tmp/kfed_DH.out
./kfed read $i >> /tmp/kfed_DH.out
echo $i >> /tmp/kfed_FS.out
./kfed read $i blkn=1 >> /tmp/kfed_FS.out
echo $i >> /tmp/kfed_BK.out
./kfed read $i aun=1 blkn=254 >> /tmp/kfed_BK.out
echo $i >> /tmp/kfed_FD.out
./kfed read $i aun=2 blkn=1 >> /tmp/kfed_FD.out
echo $i >> /tmp/kfed_DD.out
./kfed read $i aun=2 blkn=2 >> /tmp/kfed_DD.out
echo $i >> /tmp/kfed_PST.out
./kfed read $i aun=1 blkn=2 >> /tmp/kfed_PST.out
done
----
- Run kfed.sh in <asm home>/bin
#chmod u+x kfed.sh
# kfed.sh
- Upload /tmp/kfed_DH.out, /tmp/kfed_FS.out, /tmp/kfed_BK.out, /tmp/kfed_FD.out, /tmp/kfed_DD.out and /tmp/kfed_PST.out
_+_)_+_+_+_+_+__+_+_+_+__+_+_+_+++__+_+_++_

alter session set nls_date_format='DD-MON-YYYY HH24:MI:SS';
select 'THIS ASM REPORT WAS GENERATED AT: ==)> ' , sysdate " " from dual;
select 'HOSTNAME ASSOCIATED WITH THIS ASM INSTANCE: ==)> ' , MACHINE " " from v$session where program like '%SMON%';
select * from v$asm_diskgroup;
SELECT * FROM V$ASM_DISK ORDER BY GROUP_NUMBER,DISK_NUMBER;
select * from gv$asm_operation;
select * from v$version;
show parameter

4.)kfod asm_diskstring='<diks_path>' disks=all

#> ls -ltr /dev/sdb*
brw-r----- 1 root disk 8, 20 Dec 22 17:50 /dev/sdb4
brw-r----- 1 root disk 8, 17 Dec 22 17:50 /dev/sdb1
brw-r----- 1 root disk 8, 16 Dec 22 17:50 /dev/sdb
brw-rw---- 1 root dba 8, 18 Dec 22 19:46 /dev/sdb2
brw-rw---- 1 root dba 8, 19 Dec 22 20:12 /dev/sdb3

$> ls -ltr /dev/sdb2
brw-rw---- 1 root dba 8, 18 Dec 22 22:02 /dev/sdb2
[oracle@db1:~]
$> ls -ltr /dev/sdb4
brw-rw---- 1 root disk 8, 20 Dec 22 17:50 /dev/sdb4
[oracle@db1:~]
$> ls -ltr /dev/sdb3
brw-rw---- 1 root dba 8, 19 Dec 22 22:10 /dev/sdb3
Login as root and change the ownership
#> chown -R root:dba /dev/sdb4
[oracle@db1:~]
$> ls -ltr /dev/sdb4
brw-rw---- 1 root dba 8, 20 Dec 22 17:50 /dev/sdb4

$> kfed read /dev/sdb4
kfbh.endian: 1 ; 0x000: 0x01
kfbh.hard: 130 ; 0x001: 0x82
kfbh.type: 1 ; 0x002: KFBTYP_DISKHEAD

Unable to view/list diskgroups in asm command prompt

ASM Disk Group issue:

Issue : Unable to view/list diskgroups in asm command prompt

Cause: asm_diskstring is set as "/dev/sd*" in asm pfile
Identified the labelled disks from the below queries
#> oracleasm querydisk /dev/sdb1
Device "/dev/sdb1" is marked an ASM disk with the label "DATA"
[root@app1:/dev]
#> oracleasm querydisk /dev/sdb2
Device "/dev/sdb2" is marked an ASM disk with the label "FRA"

Resolution: Modified the pfile with labelled disks as below and brought up the asm services
asm_diskstring = "/dev/sdb*"

Now, able to view/list the asm disks

RAC 11g, 12c day to day commands for DBA

How to stop Oracle RAC (11g, 12c)?

You must perform these steps in the order listed to stop Oracle RAC:

emctl stop dbconsole (11c only. In 12c DB Express replaces dbconsole and doesn’t have to be stopped )
srvctl stop listener [-listener listener_name] [-node node_name] [-force] (stops all listener services)
srvctl stop database -db db_unique_name [-stopoption stop_options] [-eval(12c only)] [-force] [-verbose]
srvctl stop asm [-proxy] [-node node_name] [-stopoption stop_options] [-force]
srvctl stop nodeapps [-node node_name] [-gsdonly] [-adminhelper] [-force] [-relocate] [-verbose]
crsctl stop crs

How to Start Oracle RAC (11g, 12c)?

You must perform these steps in the order listed to start Oracle RAC:

1. crsctl start crs
2. crsctl start res ora.crsd -init
3. srvctl start nodeapps [-node node_name] [-gsdonly] [-adminhelper] [-verbose]
4. srvctl start asm [-proxy] [-node node_name [-startoption start_options]]
5. srvctl start database -db db_unique_name [-eval(12c only)]] [-startoption start_options] [-node node_name]
6. srvctl start listener [-node node_name] [-listener listener_name] (start all listener services)
7. emctl start dbconsole (11c only)

To start resources of your HA environment if that are still down(e.g. ora.ons, Listener):
crsctl start resource -all

DEBUG

Starting with Oracle 12c, the log and trace files of the clusterware files are stored in the Automatic Diagnostic Repository (ADR) under the ADR_HOME location $ADR_BASE/diag/crs/`hostname`/crs.

$ adrci
adrci> show homes

Manage low level cluster resources: CRS, HAS, cluster

How to display the status of resources in RAC?

Clusterware Resource Status Check : crsctl status resource -t (or shorter: crsctl stat res -t)

Find offline resources: crs_stat -t | grep -i offline

How to check the current status of a cluster?

crsctl check cluster
CRS-4537: Cluster Ready Services is online
CRS-4529: Cluster Synchronization Services is online
CRS-4533: Event Manager is online

To know the cluster name: olsnodes -c

How to check the current status of CRS?

crsctl check crs
CRS-4638: Oracle High Availability Services is online (has)
CRS-4537: Cluster Ready Services is online (crs)
CRS-4529: Cluster Synchronization Services is online (css)
CRS-4533: Event Manager is online

How to Stop/Start the local node?

crsctl stop has
This command will also abort the database and CRS. Local Listeners will stop and VIP listeners will migrate elsewhere.

crsctl start has
This command will start all the CRS components, listeners and the database.

How to Stop/Start the whole cluster?

crsctl stop cluster -all
crsctl start cluster -all

How to To start and stop oracle clusterware (CRS)?

crsctl stop crs
crsctl start crs

Manage Network components

How to display global public and global cluster_interconnect?

C:\Windows\system32>oifcfg ge34f
Heartbeat 194.56.67.0 global cluster_interconnect,asm
Production 10.356.3.0 global public

How to check if nodeapps running on a node?

srvctl status nodeapps [-n my-node]
For each VIP address: network enabled/disabled, running on node host1 or not running.

Nodeapps are standard set of oracle application services which are started automatically for RAC.
Node apps Include:
1) VIP
2) Oracle Net listeners
3) Global Service Daemon
4) Oracle Notification Service (ONS).

Nodeapp Services run on each node of the cluster. They switch over to other nodes through VIP during a failover.

How to check the SCAN Configuration?

The SCAN makes it possible to add or remove nodes from the cluster without needing to reconfigure clients.

Using CLUVFY to Confirm DNS is Correctly Associating the SCAN addresses.

cluvfy comp scan
Verifying Single Client Access Name (SCAN) …PASSED
Verification of SCAN was successful.
CVU operation performed: SCAN
Date: Oct 19, 2017 1:17:59 PM
CVU home: C:\…\grid_home\bin\..\
User: .\VFENOLL

How to display the current configuration of the SCAN VIPs?

srvctl config scan
SCAN name: MY-CLUSTER-SCAN, Network: 1
Subnet IPv4: 10.104.2.0/255.255.255.0/Production, static
Subnet IPv6:
SCAN 1 IPv4 VIP: 10.404.2.677
SCAN VIP is enabled.
SCAN VIP is individually enabled on nodes:
SCAN VIP is individually disabled on nodes:
SCAN 2 IPv4 VIP: 10.404.2.618
SCAN VIP is enabled.
SCAN VIP is individually enabled on nodes:
SCAN VIP is individually disabled on nodes:
SCAN 3 IPv4 VIP: 10.404.2.619
SCAN VIP is enabled.
SCAN VIP is individually enabled on nodes:
SCAN VIP is individually disabled on nodes:

How to display the status of SCAN VIPs and SCAN listeners?

srvctl status scan
SCAN VIP scan1 is enabled
SCAN VIP scan1 is running on node my-node1
SCAN VIP scan2 is enabled
SCAN VIP scan2 is running on node my-node2
SCAN VIP scan3 is enabled
SCAN VIP scan3 is running on node my-node1

If you want to add or modify a scan VIP: srvctl add | modify scan -n my-scan
To delete it: srvctl remove scan

How to display the status of SCAN listeners?

srvctl status scan_listener
SCAN Listener LISTENER_SCAN1 is enabled
SCAN listener LISTENER_SCAN1 is running on node my-node1
SCAN Listener LISTENER_SCAN2 is enabled
SCAN listener LISTENER_SCAN2 is running on node my-node2
SCAN Listener LISTENER_SCAN3 is enabled
SCAN listener LISTENER_SCAN3 is running on node my-node1

If you want to add or remove a scan_listener: srvctl add | remove scan_listener
To change the port: srvctl modify scan_listener -p

Manage the Oracle Cluster Registry (OCR)

How to verify the integrity of OCR?

cluvfy comp ocr -n all -verbose
Verifying OCR Integrity …PASSED
Verification of OCR integrity was successful.
CVU operation performed: OCR integrity
Date: Oct 11, 2017 4:56:01 PM
CVU home: C:\…grid_home\bin\..\
User: \VFENOLL

How to backup the OCR?

Oracle takes physical backup of OCR automatically every 3 hours. Default location is CRS_home/cdata/my_cluster_name/OCRBackup.
The ocrconfig tool is used to make daily copies of the automatically generated backup files.

Show backups:
ocrconfig -showbackup

Change default location of physical OCR copies:
ocrconfig -backuploc

After that, you have to copy these files on tape or in another backup location (cp -p -R CRS_home/cdata/my_cluster_name /u03/backups )

To do a manual backup:
ocrconfig -export /u03/backups/exports/OCR_exportBackup.dmp

How to recover OCR from physical or export backup?

Pre-requisite: All RAC components shutdow

Recover OCR from automatic physical backups:
crconfig -restore CRS_home/cdata/my_cluster_name/OCRBackup/backup00.ocr

Recover OCR from export backup:
ocrconfig -import /u03/backups/exports/OCR_exportBackup.dmp

How to backup the Voting disks?

In older versions of Oracle Clusterware you have to backup voting disks with the dd command.
Starting with Oracle Clusterware 11g Release 2 you no longer need to backup them. Voting disks are automatically backed up as a part of the OCR.

Manage database components

How to find the name of the database?

This name is useful as it is used in RAC commands with -d parameter.
With SQL*Plus:
connect / as sysdba
show parameter db_unique_name
With crsctl:
crsctl status resource -t | grep db

How to inspect the database configuration?

srvctl config database -d my-db-name
Database unique name: my-db-name
Database name: my-db-name
Oracle home: D:\oracle\db\product\12.2.0\dbhome_1
Oracle user: nt authority\system
Spfile: +DATA/my-db-name/PARAMETERFILE/spfile.272.9460543263
Password file: +DATA/my-db-name/PASSWORD/pwdmy-db-name.256.998734039
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools:
Disk Groups: DATA,FRA
Mount point paths:
Services: my-db-name1,my-db-name2,srv1,srv2, srv3
Type: RAC
Start concurrency:
Stop concurrency:
Database instances: my-db-name1,my-db-name2
Configured nodes: node1,node2
CSS critical: no
CPU count: 0
Memory target: 0
Maximum memory: 0
Default network number for database services:
Database is administrator managed

How to display the name and the status of the instances in the RAC?

srvctl status database -d my-db-name
Instance my-db-name1 is running on node node1
Instance my-db-name2 is not running on node node2

To list just active nodes: olsnodes -s –t

How to start|stop the database?

srvctl stop database -d my-db-name -o immediate
srvctl start database -d my-db-name

How to start|stop one instance of the RAC?

srvctl start instance -d my-db-name -i my-db-name1
srvctl stop instance -d my-db-name -i my-db-name1
Use -force if the instance to stop is not on the local server

How to start and stop a PDB in Oracle RAC?

Stop a PDB

On the current node [or on all the nodes]:
ALTER PLUGGABLE DATABASE my-PDB-name CLOSE IMMEDIATE [Instances=all];
This will stop the associated service too.
Manually stopping the associated service will not close the PDB. You have to use this SQL command.

Start a PDB

On the current node [or on all the nodes]:
ALTER PLUGGABLE DATABASE my-PDB-name OPEN [Instances=all;]
You can also start the PDB with the associated service
This will NOT start the service(s) associated with this PDB.

How to stop and start a Listener?

srvctl stop listener -l LISTENER_NAME
srvctl start listener -l LISTENER_NAME