Senthil Rajendran

Subscribe to Senthil Rajendran feed
The voice in my head may not be real , but they have some good ideas !!!
Fail Fast, Fail Forward, Fail Often, Fail Better , Standup Every Time
Every problem has at least one solution. Only some solutions are harder to find.Senthil Rajendranhttp://www.blogger.com/profile/15572088214110845083noreply@blogger.comBlogger333125
Updated: 13 hours 20 min ago

First Build, DevOps Journey Starts here

Mon, 2021-07-12 11:57

 My first successful build in Oracle Cloud Services. My DevOps Journey Starts here.


Useful Script to Analyze last 30 days of OutofMemory Error on Oracle E-Business Suite OPP

Mon, 2021-03-15 02:54

 

Useful Script to Analyze last 30 days of OutofMemory Error on Oracle E-Business Suite OPP ( Output Post Processor ) Logs

First Spool the logfile that is required for analysis. Here I am spooling from fnd tables. you can use find command to get the last 30 days of OPP logs.

cd /tmp;mkdir OPPAnalysis;cd OPPAnalysis
sqlplus apps
spool opp_last30DaysLog.lst
set linesize 1000 pagesize 0
select logfile_name from fnd_concurrent_processes where trunc(creation_date)>trunc(sysdate-30) and logfile_name like '%OPP%';
spool off

Script below gets into each of the logs generated above and finds the OutOfMemoryError Tag then pulls the Request ID. Then for each of the Request  ID

cat OPPOutOfMemoryDiag.sh
for log in $(cat opp_last30DaysLog.lst|grep OPP|grep txt)
do
 opp_request=$(grep -i UNEXPECTED $log|grep -i OutOfMemoryError|grep ":RT"|cut -d'[' -f4|cut -d']' -f1|cut -d':' -f2|cut -c 3-)
 if [ "$1" = "-debug" ]
  then
   echo
   echo $log
   echo $opp_request
  fi
 if [ ! -z $opp_request ]
 then
  echo $(echo $log|rev|cut -d'/' -f1|rev)" - "$(stat $log|grep Modify|cut -d":" -f2-)" - "$(grep -b1 $opp_request $log|grep -i Template|cut -d'-' -f2)
 fi
done



[oracle@testserver OPPAnalysis]$ sh OPPOutOfMemoryDiag.sh
FNDOPP548310.txt -  2091-11-04 07:05:02.190691000 -0600 - Template code: EXCELLOAD
FNDOPP548311.txt -  2091-11-04 07:06:32.195461000 -0600 - Template code: EXCELLOAD
FNDOPP548345.txt -  2091-11-04 09:50:20.855625000 -0600 - Template code: EXCELLOAD
FNDOPP547385.txt -  2091-11-23 11:50:16.076419000 -0600 - Template code: PDFOUTUT
FNDOPP548385.txt -  2091-11-06 07:20:16.937702000 -0600 - Template code: EXCELLOAD
FNDOPP548395.txt -  2091-11-06 07:20:19.119176000 -0600 - Template code: EXCELLOAD
FNDOPP548320.txt -  2091-11-07 07:20:12.012239000 -0600 - Template code: EXCELLOAD
FNDOPP548347.txt -  2091-11-03 08:27:41.649406000 -0600 - Template code: EXCELLOAD
FNDOPP548359.txt -  2091-11-08 09:02:02.276215000 -0600 - Template code: EXCELLOAD
FNDOPP548368.txt -  2091-11-08 10:22:16.784284000 -0600 - Template code: EXCELLOAD
FNDOPP548377.txt -  2091-11-08 10:35:22.024312000 -0600 - Template code: PDFOUTUT
FNDOPP548393.txt -  2091-11-03 09:18:51.866628000 -0600 - Template code: PDFOUTUT
FNDOPP548392.txt -  2091-11-08 12:47:33.709433000 -0600 - Template code: PDFOUTUT
FNDOPP548311.txt -  2091-11-09 07:08:49.778074000 -0600 - Template code: PDFOUTUT
FNDOPP548312.txt -  2091-11-09 07:17:04.803232000 -0600 - Template code: PDFOUTUT
FNDOPP548320.txt -  2091-11-10 07:11:10.239378000 -0600 - Template code: PDFOUTUT
FNDOPP548325.txt -  2091-11-10 07:15:11.158641000 -0600 - Template code: PDFOUTUT
FNDOPP549346.txt -  2091-11-12 07:19:48.004048000 -0600 - Template code: PDFOUTUT
FNDOPP549349.txt -  2091-11-12 07:09:20.415082000 -0600 - Template code: EXCELLOAD
FNDOPP549368.txt -  2091-11-13 07:20:13.214100000 -0600 - Template code: EXCELLOAD
FNDOPP549375.txt -  2091-11-13 07:15:32.330791000 -0600 - Template code: PDFOUTUT
FNDOPP549391.txt -  2091-11-14 07:16:11.077493000 -0600 - Template code: EXCELLOAD
FNDOPP549394.txt -  2091-11-14 07:09:31.057564000 -0600 - Template code: EXCELLOAD
[oracle@testserver OPPAnalysis]$ 

sh OPPOutOfMemoryDiag.sh -debug 
the above -debug options helps get more details

Next Steps
Now that you have found the template that is causing Heap Out Of Memory Error , the next step is to fix it.

There are two options
Option 1: more Heap upto 2gb , if you need more than 2gb Heap move to 64 bit Java
Option 2: move from ram to disk based procesing

Both these options are covered in detail
E-Business Suite Output BI Publisher Post Processor (OPP) Log Contains Error "java.lang.OutOfMemoryError: Java heap space" (Doc ID 1268217.1)

The best recommended for EXCEL and BULK programs is to move to Disk Based Processing.

Configure the XML Publisher Administrator Configuration settings.
  1. As XML Publisher Administrator navigate to Administration->Configuration.
  2. Under Temporary Directory pick a temporary file location on your concurrent processing node. This should be at least 5GB or 20x larger than largest XML data file you generate
  3. Under FO Processing, set:
    • Use XML Publisher's XSLT processor set to True
    • Confirm the profile 'scalable feature of XSLT processor' is set to False at the global level
    • Enable profile 'scalable feature of XSLT processor' be setting to True at the individual template level for the failing template
    • Enable XSLT runtime optimization set to True

Grafana for OCI Monitoring in Docker running on Windows

Thu, 2021-03-11 05:45

 Grafana for OCI Monitoring in Docker on Windows


Step 1: Install Docker Desktop on Windows

https://docs.docker.com/docker-for-windows/install/


Step 2: Download and Install WSL 2

https://docs.microsoft.com/en-us/windows/wsl/install-win10


Step 3: Make Sure Docker Desktop on Windows is Running


Step 4: Sign-in to Docker [ optional ]


Step 5: Install Grafana on Docker

https://grafana.com/docs/grafana/latest/administration/configure-docker/


Run Grafana container with persistent storage (recommended)

# create a persistent volume for your data in /var/lib/grafana (database and plugins)

docker volume create grafana-storage


# start grafana

docker run -d -p 3000:3000 --name=grafana -v grafana-storage:/var/lib/grafana grafana/grafana



Step 6 : Launch URL and Validate



Step 7 : Launch CLI



Install the Data Source Plugin. From the command line, use grafana-cli to install the plugin

# grafana-cli plugins install oci-metrics-datasource



Step 8 : Setup OCI - CLI on Docker


Follow Instruction

https://blogs.oracle.com/developers/get-going-quickly-with-command-line-interface-for-oracle-cloud-infrastructure-using-docker-container


Step 9 : Move the .oci folder from host to grafana docker image


PS C:\Users\skrajend> docker ps

CONTAINER ID   IMAGE             COMMAND     CREATED             STATUS          PORTS                    NAMES

e647e4022bc0   grafana/grafana   "/run.sh"   About an hour ago   Up 17 minutes   0.0.0.0:3000->3000/tcp   grafana

PS C:\Users\skrajend>


PS C:\Users\skrajend> docker cp .oci e647e4022bc0:/home/grafana


Step 10 : Configure the key_file in OCI config file


Login into the docker container as root user


PS C:\Users\skrajend> docker exec -it --user root e647e4022bc0 /bin/sh

/usr/share/grafana # cd /home/grafana


Use vi editor and \uUpdate key_file with the correct path and save the config file


/home/grafana/.oci # cat config|grep -i key

key_file=/home/grafana/.oci/oci_api_key.pem

/home/grafana/.oci #


Step 11 : Change Owner of the .oci folder to grafana


/home/grafana/ # chown -Rf grafana:root .oci



Step 12: Restart grafana docker



Step 13 : Login into Grafana and Validate OCI Data Source


Step 14 : Configure the Tenancy in the Data Source


 

Step 15 : Loading OCI Compute Instance Monitoring Plugin from Grafana Plugin Website




Import the Plugin via the ID 


Select the data source as Oracle Cloud Infrastructure Metric



Validate the Grafana Dashboard




Happy Learning with Grafana






OCI CLI to List all the Boot Volumes Backup Policy Assignment

Wed, 2021-02-10 00:07
OCI CLI to List all the Boot Volumes Backup Policy Assignment

This bash scripts needs the tenancy ID to be updated. It loops through all the compartments and then it all availability-domain ,  then fetches the boot volume details. From the boot volume it fetches the asset-id and then validates block volume policy assignment. If there is no policy assigned then the policy status is marked as FAILURE. If you intend to have backups enabled , create a new backup policy of your choice or assign already existing one.


ociCompartmentList=$(oci iam compartment list --compartment-id ocid1.tenancy.oc1..aaaa1111222334)
for c in $(echo "$ociCompartmentList" | jq '.data | keys | .[]')
do
        compartment_ocid=$(echo "$ociCompartmentList" | jq -r ".data[$c].\"id\"")
        ociadList=$(oci iam availability-domain list)
        for a in $(echo "$ociadList" | jq '.data | keys | .[]')
        do
                ociAD=$(echo "$ociadList" | jq -r ".data[$a].\"name\"")
                ocibootvList=$(oci bv boot-volume list -c $compartment_ocid --availability-domain $ociAD)
                for i in $(echo "$ocibootvList" | jq '.data | keys | .[]')
                do
                        bvID=$(echo "$ocibootvList" | jq -r ".data[$i].\"id\"")
                        bvName=$(echo "$ocibootvList" | jq -r ".data[$i].\"display-name\"")
                        lifecycle=$(echo "$ocibootvList" | jq -r ".data[$i].\"lifecycle-state\"")
                        bvPolicyAList=$(oci bv volume-backup-policy-assignment get-volume-backup-policy-asset-assignment --asset-id $bvID)
                        ociAssignedPolicyID=$(echo "$bvPolicyAList" | jq -r ".data[].\"id\"")
                        if [ -z "$ociAssignedPolicyID" ]
                        then
                                echo "BootVolume : $bvName | Backup Policy : FAILURE"
                        else
                                echo "BootVolume : $bvName | Backup Policy : AVAILABLE"
                        fi
                done
        done
done 

Boot Volume: ebstestserver-001(Boot Volume)| Backup Policy: AVAILABLE
Boot Volume: ebstestserver-002(Boot Volume)| Backup Policy: AVAILABLE
Boot Volume: ebstestserver-003(Boot Volume)| Backup Policy: AVAILABLE
Boot Volume: ebstestserver-001(Boot Volume)| Backup Policy: AVAILABLE
Boot Volume: ebstestserver-004(Boot Volume)| Backup Policy: AVAILABLE
Boot Volume: ebstestserver-005(Boot Volume)| Backup Policy: AVAILABLE
Boot Volume: ebstestserver-001(Boot Volume)| Backup Policy: AVAILABLE
Boot Volume: ebstestserver-006(Boot Volume)| Backup Policy: AVAILABLE
Boot Volume: ebstestserver-006(Boot Volume)| Backup Policy: AVAILABLE
Boot Volume: ebstestserver-007(Boot Volume)| Backup Policy: AVAILABLE
Boot Volume: ebstestserver-008(Boot Volume)| Backup Policy: AVAILABLE
Boot Volume: ebstestserver-009(Boot Volume)| Backup Policy: AVAILABLE
Boot Volume: ebstestserver-010(Boot Volume)| Backup Policy: AVAILABLE
Boot Volume: ebstestserver-011(Boot Volume)| Backup Policy: AVAILABLE
Boot Volume: ebstestserver-012(Boot Volume)| Backup Policy: AVAILABLE
Boot Volume: ebstestserver-013(Boot Volume)| Backup Policy: AVAILABLE
Boot Volume: ebstestserver-014(Boot Volume)| Backup Policy: AVAILABLE
Boot Volume: ebstestserver-015(Boot Volume)| Backup Policy: AVAILABLE
Boot Volume: ebstestserver-016(Boot Volume)| Backup Policy: AVAILABLE
Boot Volume: ebstestserver-017(Boot Volume)| Backup Policy: AVAILABLE
Boot Volume: ebstestserver-018(Boot Volume)| Backup Policy: FAILURE
Boot Volume: ebstestserver-019(Boot Volume)| Backup Policy: FAILURE
Boot Volume: ebstestserver-020(Boot Volume)| Backup Policy: FAILURE
 

OCI CLI to List all the Boot Volumes and its LifeCycle State in the Tenancy

Mon, 2021-02-08 06:17
OCI CLI to List all the Boot Volumes and its LifeCycle State in the Tenancy

This bash scripts needs the tenancy ID to be updated. It loops through all the compartments and then it all availability-domain ,  then fetches the boot volume details.


ociCompartmentList=$(oci iam compartment list --compartment-id ocid1.tenancy.oc1..aaaa1111222334)
for c in $(echo "$ociCompartmentList" | jq '.data | keys | .[]')
do
 compartment_ocid=$(echo "$ociCompartmentList" | jq -r ".data[$c].\"id\"")
 ociadList=$(oci iam availability-domain list)
 for a in $(echo "$ociadList" | jq '.data | keys | .[]')
 do
  ociAD=$(echo "$ociadList" | jq -r ".data[$a].\"name\"")
  ocibootvList=$(oci bv boot-volume list -c $compartment_ocid --availability-domain $ociAD)
  for i in $(echo "$ocibootvList" | jq '.data | keys | .[]')
  do
  bvName=$(echo "$ocibootvList" | jq -r ".data[$i].\"display-name\"")
  lifecycle=$(echo "$ocibootvList" | jq -r ".data[$i].\"lifecycle-state\"")
  echo "BootVolume : $bvName | LifeCycle: $lifecycle"
  done
 done
done

Boot Volume: ebstestserver-001(Boot Volume)| LifeCycle: RUNNING
Boot Volume: ebstestserver-002(Boot Volume)| LifeCycle: RUNNING
Boot Volume: ebstestserver-003(Boot Volume)| LifeCycle: RUNNING
Boot Volume: ebstestserver-004(Boot Volume)| LifeCycle: RUNNING
Boot Volume: ebstestserver-005(Boot Volume)| LifeCycle: RUNNING
Boot Volume: ebstestserver-006(Boot Volume)| LifeCycle: RUNNING
Boot Volume: ebstestserver-007(Boot Volume)| LifeCycle: RUNNING
Boot Volume: ebstestserver-008(Boot Volume)| LifeCycle: RUNNING
Boot Volume: ebstestserver-009(Boot Volume)| LifeCycle: RUNNING
Boot Volume: ebstestserver-010(Boot Volume)| LifeCycle: RUNNING
Boot Volume: ebstestserver-011(Boot Volume)| LifeCycle: RUNNING
Boot Volume: ebstestserver-012(Boot Volume)| LifeCycle: RUNNING
Boot Volume: ebstestserver-013(Boot Volume)| LifeCycle: RUNNING
Boot Volume: ebstestserver-014(Boot Volume)| LifeCycle: RUNNING
Boot Volume: ebstestserver-015(Boot Volume)| LifeCycle: RUNNING
Boot Volume: ebstestserver-016(Boot Volume)| LifeCycle: RUNNING
Boot Volume: ebstestserver-017(Boot Volume)| LifeCycle: RUNNING
Boot Volume: ebstestserver-018(Boot Volume)| LifeCycle: RUNNING
Boot Volume: ebstestserver-019(Boot Volume)| LifeCycle: RUNNING
Boot Volume: ebstestserver-020(Boot Volume)| LifeCycle: RUNNING
 

OCI CLI to List all the Database and the Backup Status

Sat, 2021-01-30 03:03

OCI CLI to List all the Database and the Backup Status


This bash scripts needs the tenancy ID to be updated. It loops through all the compartments and then fetches the backup details of each database.  It validates if the database is a Standby and skips validation. It pulls only database with role as PRIMARY. It validates if Auto Backup is enabled and also it validate if recovery window is 30 days. Feel free to customize it to your requirement. It is not coded for RAC Instances , please customize it.


ociCompartmentList=$(oci iam compartment list --compartment-id ocid1.tenancy.oc1..aaaa1111222334)
for c in $(echo "$ociCompartmentList" | jq '.data | keys | .[]')
do
        compartment_ocid=$(echo "$ociCompartmentList" | jq -r ".data[$c].\"id\"")
        ocidbList=$(oci db database list -c $compartment_ocid)
        for i in $(echo "$ocidbList" | jq '.data | keys | .[]')
        do
                dbid=$(echo "$ocidbList" | jq -r ".data[$i].\"id\"")
                dbrole=$(oci db data-guard-association list --database-id $dbid| jq -r ".data[].\"role\"")
                if [    -z $dbrole ]
                then
                        echo "Stand alone Database , No Role Assigned , Setting to Primary"
                        dbrole="PRIMARY"
                fi
                if [ $dbrole == "PRIMARY" ]
                then
                        dbname=$(echo "$ocidbList" | jq -r ".data[$i].\"db-name\"")
                        dbsystemid=$(echo "$ocidbList" | jq -r ".data[$i].\"db-system-id\"")
                        dbbackupconfig=$(echo "$ocidbList" | jq -r ".data[$i].\"db-backup-config\"")
                        dbautoBackupEnabled=$(echo "$dbbackupconfig" | jq -r ".\"auto-backup-enabled\"")
                        dbrecoveryWindow=$(echo "$dbbackupconfig" | jq -r ".\"recovery-window-in-days\"")
                        hostname=$(oci db node list -c $compartment_ocid --db-system-id $dbsystemid| jq -r ".data[].\"hostname\"")
                        if [ $dbautoBackupEnabled == "true" ]
                        then
                                echo "Hostname: $hostname | Database: $dbname | AutoBackup: $dbautoBackupEnabled | SUCCESS"
                        else
                                echo "Hostname: $hostname | Database: $dbname | AutoBackup: $dbautoBackupEnabled | FAILURE"
                        fi
                        if [ $dbrecoveryWindow == "30" ]
                        then
                                echo "Hostname: $hostname | Database: $dbname | RecoveryWindow: $dbrecoveryWindow | SUCCESS"
                        else
                                echo "Hostname: $hostname | Database: $dbname | RecoveryWindow: $dbrecoveryWindow | FAILURE"
                        fi
                else
                        echo "Hostname: $hostname | Database: $dbname | Role : $dbrole - Skipping"
                fi
        done
done 


Hostname: ebstestserver-001| Database: TEST1 |AutoBackup: true |SUCCESS
Hostname: ebstestserver-001| Database: TEST1 |RecoveryWindow: 30 |SUCCESS
Hostname: ebstestserver-002| Database: TEST2 |AutoBackup: false |FAILURE
Hostname: ebstestserver-003| Database: TEST3 |AutoBackup: true |SUCCESS
Hostname: ebstestserver-001| Database: TEST3 |RecoveryWindow: 30 |SUCCESS
Hostname: ebstestserver-004| Database: TEST4 |AutoBackup: true |SUCCESS
Hostname: ebstestserver-005| Database: TEST5 |AutoBackup: true |SUCCESS
Hostname: ebstestserver-001| Database: TEST5 |RecoveryWindow: 10 |FAILURE

OCI CLI to List all the Database and its Host Lifecycle

Wed, 2021-01-27 23:05

 OCI CLI to List all the Database and its Host Lifecycle


This bash scripts needs the tenancy ID to be updated. It loops through all the compartments and then fetches the life cycle of each database and it respective hosts. 

The below code fetches lifecycle of  the hosts where the database is running.

ociCompartmentList=$(oci iam compartment list --compartment-id ocid1.tenancy.oc1..aaaa1111222334)
for c in $(echo "$ociCompartmentList" | jq '.data | keys | .[]')
do
 compartment_ocid=$(echo "$ociCompartmentList" | jq -r ".data[$c].\"id\"")
 ocidbList=$(oci db database list -c $compartment_ocid)
 for i in $(echo "$ocidbList" | jq '.data | keys | .[]')
 do
ocidbnodeList=$(oci db node list -c $compartment_ocid --db-system-id $(echo $ocidbList | jq -r ".data[$i].\"db-system-id\""))
hostname=$(echo "$ocidbnodeList" | jq -r ".data[].\"hostname\"")
lifecycle=$(echo "$ocidbnodeList" | jq -r ".data[].\"lifecycle-state\"")
echo "Hostname: $hostname | LifeCycle: $lifecycle"
 done
done

Hostname: ebstestserver-001| LifeCycle: RUNNING
Hostname: ebstestserver-002| LifeCycle: RUNNING
Hostname: ebstestserver-003| LifeCycle: RUNNING
Hostname: ebstestserver-004| LifeCycle: RUNNING
Hostname: ebstestserver-005| LifeCycle: RUNNING

The below code fetches the lifecycle of the database along with the hostnames

ociCompartmentList=$(oci iam compartment list --compartment-id ocid1.tenancy.oc1..aaaa1111222334)
for c in $(echo "$ociCompartmentList" | jq '.data | keys | .[]')
do
 compartment_ocid=$(echo "$ociCompartmentList" | jq -r ".data[$c].\"id\"")
 ocidbList=$(oci db database list -c $compartment_ocid)
 for i in $(echo "$ocidbList" | jq '.data | keys | .[]')
 do
dbname=$(echo "$ocidbList" | jq -r ".data[$i].\"db-name\"")
dbsystemid=$(echo "$ocidbList" | jq -r ".data[$i].\"db-system-id\"")
lifecycle=$(echo "$ocidbList" | jq -r ".data[$i].\"lifecycle-state\"")
hostname=$(oci db node list -c $compartment_ocid --db-system-id $dbsystemid| jq -r ".data[].\"hostname\"")
echo "Hostname: $hostname | Database: $dbname | LifeCycle: $lifecycle"
 done
done

Hostname: ebstestserver-001| Database: TEST1 |LifeCycle: RUNNING
Hostname: ebstestserver-002| Database: TEST2 |LifeCycle: RUNNING
Hostname: ebstestserver-003| Database: TEST3 |LifeCycle: RUNNING
Hostname: ebstestserver-004| Database: TEST4 |LifeCycle: RUNNING
Hostname: ebstestserver-005| Database: TEST5 |LifeCycle: RUNNING

Note : The script is helps to get the state of single instance vm db systems. for RAC you may have to modify it.

OCI CLI to List all the Hosts Lifecycle

Wed, 2021-01-27 09:57
OCI CLI to List all the Hosts Lifecycle

This bash scripts needs the tenancy ID to be updated. It loops through all the compartments and then fetches the life cycle of each hosts. 

ociCompartmentList=$(oci iam compartment list --compartment-id ocid1.tenancy.oc1..aaaa1111222334)
for c in $(echo "$ociCompartmentList" | jq '.data | keys | .[]')
do
 compartment_ocid=$(echo "$ociCompartmentList" | jq -r ".data[$c].\"id\"")
 ociinstanceList=$(oci compute instance list -c $compartment_ocid)
 for i in $(echo "$ociinstanceList" | jq '.data | keys | .[]')
 do
   hostname=$(echo "$ociinstanceList" | jq -r ".data[$i].\"display-name\"")
   lifecycle=$(echo "$ociinstanceList" | jq -r ".data[$i].\"lifecycle-state\"")
   echo "Hostname: $hostname | LifeCycle: $lifecycle"
 done
done

Hostname: ebstestserver-001| LifeCycle: RUNNING
Hostname: ebstestserver-002| LifeCycle: RUNNING
Hostname: ebstestserver-003| LifeCycle: RUNNING
Hostname: ebstestserver-004| LifeCycle: RUNNING
Hostname: ebstestserver-005| LifeCycle: RUNNING
Hostname: ebstestserver-006| LifeCycle: RUNNING
Hostname: ebstestserver-007| LifeCycle: RUNNING
Hostname: ebstestserver-008| LifeCycle: RUNNING
Hostname: ebstestserver-009| LifeCycle: RUNNING
Hostname: ebstestserver-010| LifeCycle: RUNNING
Hostname: ebstestserver-011| LifeCycle: RUNNING
Hostname: ebstestserver-012| LifeCycle: RUNNING
Hostname: ebstestserver-013| LifeCycle: RUNNING
Hostname: ebstestserver-014| LifeCycle: RUNNING
Hostname: ebstestserver-015| LifeCycle: RUNNING
Hostname: ebstestserver-016| LifeCycle: RUNNING
Hostname: ebstestserver-017| LifeCycle: RUNNING
Hostname: ebstestserver-018| LifeCycle: RUNNING
Hostname: ebstestserver-019| LifeCycle: RUNNING
Hostname: ebstestserver-020| LifeCycle: RUNNING

Hope it helps.

Ignore Python Warning

Sun, 2021-01-10 00:30

 Ignore Python Warning


# compartmentList=$(oci iam compartment list)

/usr/lib/python3.6/site-packages/oci/packages/cryptography/hazmat/bindings/openssl/binding.py:179: CryptographyDeprecationWarning: OpenSSL version 1.0.2 is no longer supported by the OpenSSL project, please upgrade. The next version of cryptography will completely remove support for it.

  utils.CryptographyDeprecationWarning,


here is a way to ignore the warning if you have updated packages

# export PYTHONWARNINGS="ignore"

# compartmentList=$(oci iam compartment list)

#


EBS 12.2 ADOP SQL to Find Session Details

Mon, 2021-01-04 22:20
EBS 12.2 ADOP SQL to Find Session Details 

A Simple and Useful SQL to find the list of Patches and Actions that are performed in each Patching Cycle. Hope it helps.


set pagesize 200;
set linesize 200;
column adop_session_id format 999999999999;
column activity format a15;
column status format a15;
column applied_fs format a10;
column patch_fs format a10;
column options format a10;
column node format a15;
column start_date format a17;
column end_date format a17;
column clone_status format a20;
column adop_session_id new_value sid noprint
ttitle left 'Session ID : ' sid skip 2
break on adop_session_id skip page
select 
adop_session_id,
node_name node,
decode(length(trim(translate(bug_number, ' +-.0123456789', ' '))),null,'Patch-'||bug_number,bug_number) activity,
status ,
upper(decode(substr(applied_file_system_base,1,1),'/',substr(applied_file_system_base,-3),applied_file_system_base)) applied_fs,
upper(decode(substr(patch_file_system_base,1,1),'/',substr(patch_file_system_base,-3),patch_file_system_base)) patch_fs,
upper(adpatch_options) options,
to_char(start_date,'DD-Mon-YYYY HH24:MI') start_date,
to_char(end_date,'DD-Mon-YYYY HH24:MI') end_date,
clone_status,
autoconfig_status
from apps.ad_adop_session_patches
order by adop_session_id,start_date; 


File System for Oracle EBS Release 12

Sun, 2021-01-03 23:39

Well there are many ways to get Oracle EBS Release 12 installed, I am here going to write about Enterprise Deployments. 

Shared vs Non-Shared - when enterprise deployments are considered you may need multiple nodes for your internal Private tier and for your external DMZ tier. The code for EBS has to be available on all the nodes so that it can be accessed by end users through various services like http , forms , concurrent manager... The code becomes easy to maintain if it is Shared and one single repository is used by all these multi-node in an enterprise deployments. Config becomes easy , Patching becomes easy... Having them in the Non-Shared isolates the code and becomes tought to maintain it. Though EBS 12 provides various tools to keep them in sync but it gets complex as you grow. So choosing a Shared file system is a good decision for enterprise deployments.

Shared Storage that are commonly available are NFS/ZFS , in OCI we have FSS 

Here is another option that might be interesting in OCI which is attaching a volume to multiple instances.






Please be aware of the limitation and do review before considering this solution.

- Concurrent write operations to block volumes are not provided so basically you can have only one instance write to it at a time. well there must be a solution for this... not one there are many... Cluster Aware Solution. There are many options listed from differnet vendors which you can find here https://en.wikipedia.org/wiki/Clustered_file_system 

Oracle Cluster File System so called as OCFS2 (version 2) is a Cluster Aware solution provided by Oracle. Similar solutions are provided by GlusterFS , Veritas , GFS2 , VMFS

In OCI there are three simple steps to get this for your Enterprise Deployments

  • Create your EBS deployments and attach block volume to the middleware instances as Read/Write-Sharable
  • Setup OCFS2 
  • Create File Systems on OCFS2
  • Mount the File System 
  • Install/Clone EBS with Shared APPL_TOP Option
Detailed documentation on how to setup OCFS2 is available in the Linux Admin Guide

Also there is a video guide by Oracle on how to setup OCFS2 on OCI



Happy New Year 2021

Sat, 2021-01-02 09:12

 


Correct Way to Make Python3 Default on Linux

Sun, 2020-12-27 20:37

 Correct Way to Make Python3 Default on Linux


# update-alternatives --install /usr/bin/python python /usr/bin/python3 1

# update-alternatives --list|grep -i python

python  auto    /usr/bin/python3

# python
Python 3.6.8 (default, Oct  1 2020, 20:32:44)


EBS Upgrade Part 10 - 11.5.9 Database 9i RHEL 4 to EBS 12.2 Database 19c OEL 7

Thu, 2020-12-03 01:35

EBS Upgrade Part 10 - 11.5.9 Database 9i RHEL 4 to EBS 12.2 Database 19c OEL 7

Now the current state of the EBS and DB is 12.1.3 / 19c multi-tenant running on OEL6.

At this stage you can move upgrade the OS from OEL6 to OEL7 and then proceed further. If upgrade is not possible you can perform a outplace migration from OEL6 to OEL7 and then proceed further.

Upgrade from 12.1.3 to 12.2

  • Software / Patch Download and Software Staging
  • Source Environment Review
  • Backup
  • Gather Database Stats -- exec dbms_stats.gather_database_stats(estimate_percent => 15);
  • Implement fix : Fresh Install of EBS 12.2 File System Failed With RW-50004 On /tmp Directory (Doc ID 2311816.1) -- correct the file system
  • Gather System Stats  -- exec dbms_stats.gather_system_stats
  • Rapid Insall - Create Upgrade File System  
  • ETCC to the base 12.2 ( weblogic bsu slowness fixed by a patch)
  • Gather SYS, Fixed Object and Dictionary Statistics (required) -- Page 4-39 -- run this task and perform the validation
  • Gather Schema Stats - skip
  • Drop event alert triggers in custom schemas - no custom top prefixed with XX found - skipping this step
  • OATM verification - verified
  • Application DBA (AD) Tasks Matching - $AD_TOP/sql/adutconf.sql - skipping because version is 12.1.3
  • Oracle E-Business Suite Integrated SOA Gateway - skipping because integration is not in use
  • GUEST user_id - matching to 6 and end_date is null
  • Customer Relationship Management Tasks - does not apply to 12.1.3
  • Financials and Procurement Tasks - Advanced Collections - IEX module is Inactive in FND_APPLICATIONS
  • Financials and Procurement Tasks - Subledger Accounting - XLA.B.3 is Shared - apply Patch 13420532:R12.XLA.B for R12.1.x - Patched
  • Human Resource Management (HRMS) - Object Name : INSERT_POSITION_TXN_INFO -- modified the package from LONG to CLOB
  • Supply Chain Management Tasks - Install Base is Inactive --skipping
  • Supply Chain Management Tasks - Order Management is Shared -- affected column is with NON-NULL status --- verified
  • Supply Chain Management Tasks - Product Hub is Inactive --skipping
  • Supply Chain Management Tasks - Packing Hierarchy Structure Type --skipping
  • Supply Chain Management Tasks - Backing Up User Defined Attributes Data for Items --skipping
  • Supply Chain Management Tasks - Checking for Duplicate Records --validated , as per note and patch instructions there are no duplication rows
  • select 'exec dbms_ijob.broken('||job||',true);' from dba_jobs where broken='N';  , change db to spfile , restart , unlock all the locked stats table
  • Database Initialization Parameters for Oracle E-Business Suite Release 12 (Doc ID 396009.1) - validation
  • create table bkup_dba_objects_pre_upg_122 as select * from dba_objects;
  • Disable AOL Audit Trail (conditional)
  • Shutdown Apps
  • Download PSU and Apply Latest PSU to Both the RAC Database [ p29141015_121020_Linux-x86-64.zip] and run datapatch post install
  • Install ETCC and Apply all Patches p29408621_12102190416_Linux-x86-64.zip
  • Run DB ETCC to verify recommendations
  • JDK 1.7 Upgrade
  • Init.ora verified
  • Set FAILED_LOGIN_ATTEMPTS to UNLIMITED
  • Maintenance Mode Enabled
  • Trigger Disabled
  • recycle bin verification
  • Apply AD 12.2 upgrade driver , merge with AD Consolidated Upgrade Patch , for adgrants.sql use the one from the merge driver folder + adadmin post install 
  • prepare the CPU + PreInstall driver and apply in preinstall mode
  • Review  tablespace  and TEMP
  • Apply known issues for 12.2 
  • Apply Consolidated Upgrade Patch 12.2.0 after merging with preinstall drivers 
  • create table bkup_dba_objects_post_upg_122 as select * from dba_objects; --- 263 invalids
  • Disable Maintenance Mode
  • DB appsutil and autoconfig
  • JRE on DB home
  • Configuring the Oracle E-Business Suite Release 12.2 Application Tier
  • Enable Online Patching , ETCC ( db only , mt done during staging )
  • Gather system stats
  • Apply GSCC patch and report review and fix violation action
  • create table bkup_dba_objects_post_adop_122 as select * from dba_objects;
  • Review and Extend APPS_TS_TX_DATA & APPS_TS_TX_IDX
  • Review and run the fix by note 2066607.1 for AD ZD invalid objects
  • Apply Online Patching adpatch
  • alter system set cluster_database=true scope=spfile; / srvctl stop database -d prod ; srvctl start database -p prod
  • Page 4-64 Database Initialization Parameters
  • Compile Invalids
  • page 4-62 GSCC reports
  • DB Parameters settings
  • AD and TXK for Release 12.2
  • RUP 12.2.10
  • Product Interoperatability Patch - oracle workflow


Post Upgrade Task

  • Start services , Health Check
  • Concurrent Manager Post Upgrade Programs
  • check for running job and make sure all finish
  • Obsolete Schema drop
  • Drop dangling synonyms
  • shutdown application
  • Online help patch 12.2.0
  • Online help patch 12.2.10


NLS task

  • apply base NLS patch 12.2.0
  • apply NLS and TXK AR patch
  • apply NLS patch 12.2.10
  • apply base NLS help patch 12.2.0 
  • apply base NLS help patch 12.2.10
  • wf patch


Post Upgrade Release Task

  • Application Customization Migration 
  • Additional Licensing
  • Patch 28984562  for workflow
  • Patch as per 2357700.1  for workflow
  • Connection Leak Patches
  • Instance Stabilization - oacore sizing 4 per node with 2gb heap , 2 forms servlet engine per node with 2gb heap
  • run fs_clone force=yes
  • Create Mainfest File for NLS
  • hrglobal
  • dummy cutover
  • Mailer Configuration
  • UTL Folder Update
  • Node Addition
  • LB Configuration
With this we are done with the upgrade to EBS 12.2 on Database 19c

Refer to the Index Page to see the full flow of the upgrade. 


EBS Upgrade Part 9 - 11.5.9 Database 9i RHEL 4 to EBS 12.2 Database 19c OEL 7

Thu, 2020-12-03 01:23

EBS Upgrade Part 9 - 11.5.9 Database 9i RHEL 4 to EBS 12.2 Database 19c OEL 7

Now the current state of the EBS and DB is 12.1.3 / 11.2.0.4 running on OEL6. Well to get to EBS 12.2 Database 19c there are two ways. 

  • The best way is to keep 11.2.0.4 database and upgrade EBS 12.1.3 to EBS 12.2. This is stable and kind of been done for some years now.
  • The recent supported way is to get the database upgrade to 19c multi-tenant architecture on EBS 12.1.3 and then run upgrade EBS to 12.2
Well the second is being certified but not many customers have gone through this in 2020. So I would suggest to choose option 1. But what if you are in EBS 12.1.3 and you have upgraded your database to 19c. You have to go through option 2.

So I have decided to go by Option 2 to see what is the complication.

Here are the steps involved to upgrade to 19c
  • Pre - Database Upgrade From 11.2.0.4 to 19c (19.3)
  • Database Installaion 19c (19.3)
  • Database Upgrade19c (19.3)
  • Post Database Upgrade19c (19.3)
  • Convert Database to Multitenant Architecture

Refer to the Index Page to see the full flow of the upgrade. 


EBS Upgrade Part 8 - 11.5.9 Database 9i RHEL 4 to EBS 12.2 Database 19c OEL 7

Thu, 2020-12-03 00:27

EBS Upgrade Part 8 - 11.5.9 Database 9i RHEL 4 to EBS 12.2 Database 19c OEL 7

Now we are in 12.1.1 , we can directly move to 12.1.3 or 12.2. This step can be differed if you want to move to 12.2

Upgrade Application 12.1.1 to 12.1.3

  • Complete Action as per Document 761570.1
  • Database Specific Patches Applied as per Document 761570.1
  • Upgrading to the OracleAS 10g 10.1.3.4  Doc ID 728531.1
  • Apply R12.AD.B.DELTA.3 Patch 9239089
  • mv /dev/random /dev/random_original;ln -s /dev/urandom /dev/random
  • Relink Error Fix 2057857.1
  • Use AutoPatch to apply  12.1.3 Patch 9239090 + post Patch
  • Online Help Patch 9239095
  • Apply Patch 9817770 and Patch 9966055
  • Run DB and MT Autoconfig with appsutil configuration
  • Run adpreclone on DB and MT
  • Disable Maintenance Mode
  • Start Services and Health Check
  • Gather Schema Stats - Run Now and Schedule Every Week
  • Configure Database RMAN and MT Backup

Post Upgrade 12.1.3 Open Issues

  • Missing System Administrator Responsibility for SYSADMIN , recompile JSP
  • CM Not Coming up , rebuild CM view using cpadmin
  • Slow Standard Manager , run GSS
  • Tablespace Space Addition
  • Apply Patch 9707056 as per 1559441.1
  • Apply patch as per Note 1581955.1 and 1489097.1
  • Set SGA to 8 GB , enable AMM
  • WF_DEFERRED rebuild as per 286394.1
  • WF_CONTROL rebuild as per 1663093.1
  • Start Workflow Agents
  • Run Bulk Responsibilities Sync Custom Program
  • Run Sync Workflow Concurrent Program
With this EBS 12.1.3 migration is completed. We can open the system for users. I am taking this forward to EBS 12.2

Refer to the Index Page to see the full flow of the upgrade. 

EBS Upgrade Part 7 - 11.5.9 Database 9i RHEL 4 to EBS 12.2 Database 19c OEL 7

Thu, 2020-12-03 00:22

 EBS Upgrade Part 7 - 11.5.9 Database 9i RHEL 4 to EBS 12.2 Database 19c OEL 7

Now we have moved database form 9i RHEL 4 to 11gR2 OEL 6. We should be good to start with the 12.1 Upgrade. The below procedure will help Upgrading Application from 11.5.9 to 12.1.1

  • Stage CD Creation
  • Source adjkey –initialize
  • Restart DB with 1000 as process parameter
  • Apply 11i.AD.I.6 , Superseeded by 11i.AD.I.7
  • Run TUMS utility , install patch 7705743 
  • Convert to Multiple Organizations architecture
  • Drop event alert triggers in custom schemas
  • Review sizes of old and new tablespaces
  • Run AD preparation scripts
  • Convert Oracle Alert E-mail Processing to the WF Mailer 
  • Customer Relationship Management Tasks
  • Incentive Compensation
  • TeleService
  • Mobile Field Service
  • Financials and Procurement Tasks
  • General Ledger
  • iPayments
  • Internet Expenses
  • Subledger Accounting
  • Leasing and Finance Management Tasks
  • Projects Tasks
  • Public Sector/University Tasks
  • Supply Chain Management Tasks
  • Depot Repair
  • Enterprise Asset Management
  • Process Manufacturing
  • Service Contracts
  • Drop MRC Schema
  • Enable Maintenance Mode
  • Gather schema statistics
  • Build Repo and  enable addons channel in .repo file
  • yum install oracle-ebs-server-R12-preinstall -y
  • mv /usr/bin/unzip /usr/bin/unzip-ver6
  • replace unzip with https://oss.oracle.com/el4/unzip/unzip.tar
  • Run Rapid Install to install the upgrade file system
  • Enable Maintenance Mode
  • Perform Task from 2057857.1 to avoid relink errors
  • Merge Upgrade Driver 7461070,9474320,9179588
  • ADPatch Merged Upgrade Driver from /u03/soft/upgrade_driver
  • Download Apply PreInstall patch as per Note : 1448102.2
  • mv /dev/random /dev/random_original;ln -s /dev/urandom /dev/random
  • SQL> GRANT UNLIMITED TABLESPACE TO apps and applsys
  • Apply PreInstall + u6678700.drv -- following Note : 1448102.2
  • Disable Maintenance Mode
  • Run Rapid Install to Configuration the Application 12.1.1
  • Apply FIx 879522.1
  • Copy JRE from MT to DB keep under appsutil folder
  • Perform Finish the Upgrade Task
  • Help Patch u6746000.drv
  • Backup

Refer to the Index Page to see the full flow of the upgrade. 

EBS Upgrade Part 6 - 11.5.9 Database 9i RHEL 4 to EBS 12.2 Database 19c OEL 7

Thu, 2020-12-03 00:17

EBS Upgrade Part 6 - 11.5.9 Database 9i RHEL 4 to EBS 12.2 Database 19c OEL 7

With the Database now running on OEL5 , the 11g Database did not open properly so decided to move to OEL6 server. Now follow the below procedure to migrate Database 11gR2 from OEL5 64Bit to OEL6 64Bit. 

  • Shutdown DB
  • Backup Database and OraData
  • Move Binaries from OEL5 to OEL6
  • Create User/Group and RPM/Kernel Settings
  • Unzip Oracle Home and OraData , Create User/Groups
  • Cloning an Oracle Home
  • Set Environment for SID EBSDB
  • Create spfile to pfile
  • Modify Refrences for file system mount
  • Create pfile from spfile
  • ReCreate Control File by taking trace
  • Validate Alert.log
  • Start Database
  • alter system set compatible='11.2.0.4' scope=spfile;
  • Restart Database and validate alert.log
  • alter system set optimizer_features_enable='11.2.0.4' scope=spfile;
  • Restart Database and validate alert.log
  • Configure Listener
  • Backup Database and OraData
  • Transfer and untar appsutil from 9i MT to 11g MT , NO Autoconfig
  • Update EBS 11.5.9 Context File with the New Database Hostname
  • Update /etc/hosts with the IP and Hostname
  • Run Autoconfig and verify logs for no errors
  • Validate apps login from MT 11.5.9
  • --DO NOT START APPLICATION 11.5.9 Services---
  • Shutdown DB and Apply 11.2.0.4 RDBMS patch ( 7 patches )

Refer to the Index Page to see the full flow of the upgrade. 

EBS Upgrade Part 5 - 11.5.9 Database 9i RHEL 4 to EBS 12.2 Database 19c OEL 7

Thu, 2020-12-03 00:09

 EBS Upgrade Part 5 - 11.5.9 Database 9i RHEL 4 to EBS 12.2 Database 19c OEL 7

Well now we are on OEL5.11 Server and this would be a best option to move 10g to 11gR2. Here are the below steps that was performed to Upgrade Database 10gR2 to 11gR2

  • Install 11.2.0.4 DB Home
  • Install Sample CD for 11.2.0.4
  • Create nls/data/9idata directory
  • Apply 11.2.0.4 RDBMS patch ( 7 patches )
  • Perform DB upgrade task as per note 1058763.1 (10-16)
  • Skip Post Upgrade Task -- will perform after moving to OEL 6

Refer to the Index Page to see the full flow of the upgrade. 

EBS Upgrade Part 4 - 11.5.9 Database 9i RHEL 4 to EBS 12.2 Database 19c OEL 7

Thu, 2020-12-03 00:06

EBS Upgrade Part 4 - 11.5.9 Database 9i RHEL 4 to EBS 12.2 Database 19c OEL 7

This is going to be challenging. These decisions involve a lot of understanding about the complexity underlying. So we are on 10g 32-bit on RHEL 4 and want to move to 11g. The best possible route after reading a bunch of oracle documentation came to a conclusion that will build a RHEL 5 OS and do a OS migration. Then post the migration decided to upgrade the database to 11g.

Well there was a limitation for me and may not for all. I need a RHEL 5 server which I do not have. So I had another laptop where I spinned off a Virtual Box and Installed a Guest OS 5.11.

Migrate Database 10gR2 from RHEL4 32Bit to OEL5 64Bit

  • Move the 10g64 Cold Backup RHEL to oel5 stage folder
  • Install RPM and Kernel Settings
  • Install Base 10.2.0.1 64 bit
  • Install Companion 10.2.0.1 64 bit
  • Install PatchSet 10.2.0.5
  • Backup Oracle Home
  • Copy OH,ORADATA,CONTROL file from 32bit to 64bit
  • Pretask for 64bit Migration
  • Set Environment ORACLE_HOME,ORACLE_SID,PATH
  • Migrate to 64bit - recreate control file
  • Recompile Invalids
  • Post Activity
  • Backup Oracle Home and OraData Cold Backup

Refer to the Index Page to see the full flow of the upgrade. 

Pages