Oracle EBS Script to calculate number of requests run per week

SELECT COUNT(*) Total, 
sum(decode(greatest(0,ceil (sysdate - actual_completion_date)),
least(7,ceil(sysdate-actual_completion_date) ),1,0)) Week4, 
sum(decode(greatest(8,ceil (sysdate - actual_completion_date)), 

least(14,ceil(sysdate-actual_completion_date) ),1,0)) Week3,
sum(decode(greatest(15,ceil (sysdate - actual_completion_date)), 

least(21,ceil(sysdate-actual_completion_date) ),1,0)) Week2,
sum(decode(greatest(22,ceil (sysdate - actual_completion_date)), 

least(28,ceil(sysdate-actual_completion_date) ),1,0)) Week1
FROM FND_CONCURRENT_REQUESTS
WHERE ACTUAL_COMPLETION_DATE is not null;

Implement Password Policy in Oracle Database

Script to implement password policy in Oracle database

CREATE OR REPLACE FUNCTION VERIFY_FUNCTION_11G (username varchar2, password varchar2, old_password varchar2)

  RETURN boolean IS
     
     m                    integer;
     differ               integer;
     isdigit              boolean;
     islowerchar          boolean;
     isupperchar          boolean;
     ispunct              boolean;
     db_name              varchar2(40);
     digitarray           varchar2(20);
     punctarray           varchar2(25);
     lowerchararray       varchar2(30);
     upperchararray       varchar2(30);
     i_char               varchar2(10);
     simple_password      varchar2(10);
     reverse_user         varchar2(32);
     lv_count             number;

BEGIN
   digitarray     := '0123456789';
   lowerchararray := 'abcdefghijklmnopqrstuvwxyz'; 
   upperchararray := 'ABCDEFGHIJKLMNOPQRSTUVWXYZ';
   punctarray     := '!"#$%&()''*+,-/:;<=>?_';
   lv_count       := 0;

   -- Check for the minimum length of the password
   IF length(password) < 8 THEN
      raise_application_error(-20001, 'Password length less than 8');
   END IF;

   -- Check if the password is same as the username or username(1-100)
   IF NLS_LOWER(password) = NLS_LOWER(username) THEN
     raise_application_error(-20002, 'Password same as or similar to user');
   END IF;
   
   FOR i IN 1..100 LOOP
      i_char := to_char(i);
      if NLS_LOWER(username)|| i_char = NLS_LOWER(password) THEN
        raise_application_error(-20005, 'Password same as or similar to user name ');
      END IF;
   END LOOP;
   -- Check if the password is same as the username reversed
   FOR i in REVERSE 1..length(username) LOOP
     reverse_user := reverse_user || substr(username, i, 1);
   END LOOP;
   
   IF NLS_LOWER(password) = NLS_LOWER(reverse_user) THEN
     raise_application_error(-20003, 'Password same as username reversed');
   END IF;
   -- Check if the password is the same as server name and or servername(1-100)
   select name into db_name from sys.v$database;
   
   IF NLS_LOWER(db_name) = NLS_LOWER(password) THEN
      raise_application_error(-20004, 'Password same as or similar to server name');
   END IF;
   
   FOR i IN 1..100 LOOP
      i_char := to_char(i);
      if NLS_LOWER(db_name)|| i_char = NLS_LOWER(password) THEN
        raise_application_error(-20005, 'Password same as or similar to server name ');
      END IF;
   END LOOP;

   -- Check if the password is too simple. A dictionary of words may be
   -- maintained and a check may be made so as not to allow the words
   -- that are too simple for the password.
   IF NLS_LOWER(password) IN ('welcome1', 'database1', 'account1', 'user1234', 'password1', 'oracle123', 'computer1', 'abcdefg1', 'change_on_install') THEN
      raise_application_error(-20006, 'Password too simple');
   END IF;
   -- Check if the password is the same as oracle (1-100)
    simple_password := 'oracle';
    FOR i IN 1..100 LOOP
      i_char := to_char(i);
      IF simple_password || i_char = NLS_LOWER(password) THEN
        raise_application_error(-20007, 'Password too simple ');
      END IF;
    END LOOP;
   -- Check if the password contains at least one letter, one digit
   -- 1. Check for the digit
   isdigit:=FALSE;
   m := length(password);
   FOR i IN 1..10 LOOP
      FOR j IN 1..m LOOP
         IF substr(password,j,1) = substr(digitarray,i,1) THEN
             isdigit:=TRUE;
             EXIT;  -- Exit from Loop
         END IF;
      END LOOP;
   END LOOP;
   IF isdigit = TRUE THEN
     lv_count := lv_count + 1;
   END IF;
   
   -- 2. Check for the lower case character
   islowerchar:=FALSE;
   FOR i IN 1..length(lowerchararray) LOOP
      FOR j IN 1..m LOOP
         IF substr(password,j,1) = substr(lowerchararray,i,1) THEN
            islowerchar:=TRUE;
            EXIT;  -- Exit from Loop
         END IF;
      END LOOP;
   END LOOP;
   
   IF islowerchar = TRUE THEN
      lv_count := lv_count + 1;
   END IF;
   -- 3. Check for the upper case character
   isupperchar:=FALSE;
   FOR i IN 1..length(upperchararray) LOOP
      FOR j IN 1..m LOOP
         IF substr(password,j,1) = substr(upperchararray,i,1) THEN
              isupperchar:=TRUE;
              EXIT;  -- Exit from Loop
         END IF;
      END LOOP;
   END LOOP;
   
   IF isupperchar = TRUE THEN
      lv_count := lv_count + 1;
   END IF;
      
   -- 4. Check for the Special character
   ispunct:=FALSE;
   FOR i IN 1..length(punctarray) LOOP
      FOR j IN 1..m LOOP
         IF substr(password,j,1) = substr(punctarray,i,1) THEN
              ispunct:=TRUE;
              EXIT;  -- Exit from Loop
         END IF;
      END LOOP;
   END LOOP;
   
   IF ispunct = TRUE THEN
      lv_count := lv_count + 1;
   END IF;
   
   IF lv_count < 3 THEN
      raise_application_error(-20008, 'Password must contain one Upper case , one Lower case , one digit and one special character (Any three of four)');
   END IF;
   -- Check if the password differs from the previous password by at least
   -- 3 letters
   IF old_password IS NOT NULL THEN
     differ := length(old_password) - length(password);
     differ := abs(differ);
     IF differ < 3 THEN
       IF length(password) < length(old_password) THEN
         m := length(password);
       ELSE
         m := length(old_password);
       END IF;
       FOR i IN 1..m LOOP
         IF substr(password,i,1) != substr(old_password,i,1) THEN
           differ := differ + 1;
         END IF;
       END LOOP;
       IF differ < 3 THEN
         raise_application_error(-20011, 'Password should differ from the old password by at least 3 characters');
       END IF;
     END IF;
   END IF;
   
   -- Everything is fine; return TRUE ;
   RETURN(TRUE);
END;
/ 

Enable flashback in Oracle standby database

Oracle Flashback Database enables point-in-time-recovery of the entire database without requiring a restore and recovery operation.

It rewinds the entire database to a specified point in time in the past by undoing all the changes that were made since that time

Follow below steps on Standby database to enable flashback

SQL> alter system set db_recovery_file_dest_size=16g scope=both;

SQL> alter system set db_recovery_file_dest='/u01/oracle/flash_recovery_area' scope=both;

SQL> alter system set db_flashback_retention_target=1440 scope=both;

SQL> alter database recover managed standby database cancel;

SQL> alter database flashback on;

SQL> alter database recover managed standby database using current logfile disconnect;

SQL> alter database recover managed standby database cancel;

SQL> Create restore point standby_26062021 guarantee flashback database;

Activate physical standby database to open mode

SQL> alter database activate standby database;

SQL> startup mount force;

SQL> alter database set standby database to maximize performance;

SQL> alter database open;

Enable Audit in Oracle Database

 To enable audit in Oracle database, follow the below steps

Database Steps:

      ·        Check parameter file is pfile or spfile

·        Note down the audit parameters before change

·        Create pfile from spfile and make the below audit parameter changes

·        Shutdown the database

·        Change the audit parameters in pfile.

a.      audit_file_dest =   <provide audit path on O/S>

b.      audit_trail  = os

c.      audit_sys_operations=true

       ·    Create spfile from pfile

       ·     Enable audit on the required users  using the below command.

                audit all by <user name>  by access 

Provisioning Profile Log File Location

In 10g Releases (10.1.4.x)

Oracle Directory Integration Platform server log file : $ORACLE_HOME/ldap/log directory

Individual logs for each profile : $ORACLE_HOME/ldap/odi/log/ directory

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

In 11g Release 1 (11.1.1)

Log files: $MW_HOME/user_projects/domains/DOMAIN_NAME/servers/NAME_OF_MANAGED_SERVER/logs/NAME_OF_MANAGED_SERVER.log

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

DIP log file is located in the $ORACLE_HOME/ldap/log/odisrv<instance number>.log 

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

The provisioning profile logs : $ORACLE_HOME/ldap/odi/log directory. 
Each log file name is of the form: <ApplicationName>_<RealmName>_[I/E].[trc/aud].
Where:
I = INBOUND provisioning event (from Oracle E-Business Suite to Oracle Directory Services)
E = OUTBOUND provisioning event (from Oracle Directory Services to Oracle E-Business Suite)

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

Audit Log Location

In 10g Releases (10.1.4.x):

ORACLE_HOME/ldap/odi/log/PROFILE_NAME.aud

In 11g Release 1 (11.1.1):

DOMAIN_HOME/servers/wls_ods1/logs/auditlogs/DIP/

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

     

How to manage Oracle Internet Directory (OID) using OIDCTL command

On OID server  , make sure the below variables are set

$export ORACLE_INSTANCE=/d01/fmw/oid/ldap
$export INSTANCE_NAME=oid1 
$export COMPONENT_NAME=oid1

$export TNS_ADMIN=$DOMAIN_HOME/config/fmwconfig/ components/OID/config/componentName

View the Status
$ORACLE_HOME/bin/oidctl connect=oiddb status 

Stop the OID
$ORACLE_HOME/bin/oidctl connect=oiddb server=oidldapd instance=1 stop 

Start the OID
$ORACLE_HOME/bin/oidctl connect=oiddb server=oidldapd instance=1 start

Run adop cutover in EBS R12.2 without starting the Application Tier

Run the below command at the time of cutover 

adop phase=cutover mtrestart=no

Login R12.2 WebLogic Console denied from Client Machine

1.Log in to the primary node of the Oracle E-Business Suite instance.
2.Start the Oracle WebLogic Admin Server from the run file system, if it is not already running.
3.Take a backup of the run file system context file.
4.Edit the run file system context file to set the value for the

 s_wls_admin_console_access_nodes context variable to the list of trusted hosts that are allowed to access the Admin Server. 
<s_wls_admin_console_access_nodes oa_var="s_wls_admin_console_access_nodes">ws1.myco.com,ws2.myco.com</s_wls_admin_console_access_nodes>

5.Run AutoConfig.
6.Stop and restart the Oracle WebLogic Admin Server.

If you need to make changes without having access to the Oracle WebLogic Server Administration Console, you can update or remove the connection filter rules by editing the $DOMAIN_HOME/config/config.xml file. However, changes added this way will be overwritten by the next AutoConfig run.

Script to Diagnose adop and Other AD-TXK Issues in Oracle E-Business Suite R12.2

Execute the diagnostic script shipped  as per Note 1901242.1 on all application nodes

To use the script:
1. Unzip the patch to a new folder
2. Confirm the following files are present after unzipping.
        adzddbdetails.sql
        adzddiagnostic.pl
        adzdedndetails.sql

3. Source the Apps Tier run environment file
4. Run the master perl script adzddiagnostic.pl with the command "perl adzddiagnostic.pl"
5. Supply the apps database user password the script prompts for.
6. The script creates a file "diagnostic_info_<hostname>.tar" in the working directory.

Find TXK, AD patch level in R12.2

Run the below sql . Login as apps user

sqlplus apps/*****

SQL> SELECT codelevel 
           FROM AD_TRACKABLE_ENTITIES 
           WHERE abbreviation in ('txk','ad')

CODELEVEL
ad     C.7
txk    C.7

ADOP Analyzer in EBS R12.2

 Refer DOC ID 2379656.1 for ICM ADOP Application Tier Analyzer output

ORA-06598: insufficient INHERIT PRIVILEGES

While running adop phase=abort. Got below error

declare 
*
ERROR at line 1:
ORA-06598: insufficient INHERIT PRIVILEGES privilege
ORA-06512: at "APPS.AD_ZD_LOG", line 52
ORA-06512: at line 14

Solution :

       SQL>GRANT INHERIT ANY PRIVILEGES TO APPS;
       After this grant, run the abort and got completed
       
adop phase=abort

       The problem got resolved and started the patch cycle 

Start admin Server from Patch File System in EBS R12.2

Run  EBSapps.env to set the patch file system environment

$cd $ADMIN_SCRIPTS_HOME

$ sh adadminsrvctl.sh start forcepatchfs

Ansible Script - Start an OCI ( Oracle Cloud Infrastructure) instance

 

Playbook file (instance_mgmt_start.yaml)

- name: instance lifecycle mgmt

  hosts: localhost

  collections:

    - oracle.oci

  tasks:

    - name: start instance

      oci_compute_instance_actions:

        instance_id:                                   

        action: start

      register: output

    - name: Print namespace name

      debug:

        msg: "{{ output }}"

 

Run ansbile-playbook as:

 

$ ansible-playbook  /home/a_prashant/.ansible/instance_mgmt_start.yaml

 

PLAY [Get namespace name] *****************************************************************************

 

TASK [Gathering Facts] *****************************************************************************

ok: [localhost]

 

TASK [Get namespace name1] *****************************************************************************

changed: [localhost]

 

TASK [Print namespace name] ************************************************************ok: [localhost] => {

    "msg": {

        "changed": true,

        "failed": false,

        "instance": {

            "agent_config": {

                "is_management_disabled": false,

                "is_monitoring_disabled": false

            },

            "availability_config": {

                "recovery_action": "RESTORE_INSTANCE"

            },

            "availability_domain": "RJkC:AP-MUMBAI-1-AD-1",

            "compartment_id": "                         ",

            "dedicated_vm_host_id": null,

            "defined_tags": {

                "Operations": {

                    "CostCenter": "56"

                },

                "Oracle-Tags": {                   "CreatedBy":"oracleidentitycloudservice/prashant",

                    "CreatedOn": "2021-02-12T05:12:02.350Z"

                }

            },

            "display_name": "TF_TERRAFORM_NEW",

            "extended_metadata": {},

            "fault_domain": "FAULT-DOMAIN-1",

            "freeform_tags": {},

            "id": "           ",

            "image_id": "         ",

            "instance_options": {

                "are_legacy_imds_endpoints_disabled": false

            },

            "ipxe_script": null,

            "launch_mode": "PARAVIRTUALIZED",

            "launch_options": {

                "boot_volume_type": "PARAVIRTUALIZED",

                "firmware": "UEFI_64",

                "is_consistent_volume_naming_enabled": true,

                "is_pv_encryption_in_transit_enabled": false,

                "network_type": "PARAVIRTUALIZED",

                "remote_data_volume_type": "PARAVIRTUALIZED"

            },

            "lifecycle_state": "RUNNING",

            "metadata": {

                "ssh_authorized_keys": "        \n"

            },

            "region": "ap-mumbai-1",

            "shape": "VM.Standard.E2.1.Micro",

            "shape_config": {

                "gpu_description": null,

                "gpus": 0,

                "local_disk_description": null,

                "local_disks": 0,

                "local_disks_total_size_in_gbs": null,

                "max_vnic_attachments": 1,

                "memory_in_gbs": 1.0,

                "networking_bandwidth_in_gbps": 0.48,

                "ocpus": 1.0,

                "processor_description": "2.0 GHz AMD EPYC™ 7551 (Naples)"

            },

            "source_details": {

                "boot_volume_size_in_gbs": null,

                "image_id": "                 ",

                "kms_key_id": null,

                "source_type": "image"

            },

            "system_tags": {

                "orcl-cloud": {

                    "free-tier-retained": "true"

                }

            },

            "time_created": "2021-02-12T05:12:02.923000+00:00",

            "time_maintenance_reboot_due": null

        }

    }

}

 

PLAY RECAP *****************************************************************************

localhost : ok=3 changed=1 unreachable=0 failed=0 skipped=0 rescued=0  ignored=0 

Register OID with EBS R12.2 during SSO setup

 $FND_TOP/bin/txkrun.pl -script=SetSSOReg -registeroid=yes -appname=R12AHPRODE_AHAPPS01 -svcname=R12AHPRODE_AHAPPS01

You are registering this instance with LDAP Server.

Enter LDAP Host name? AHAPPRODS.myorg.com
Enter the LDAP Port on Oracle Directory server? 3060
Enter the LDAP Directory Administrator (orcladmin) Bind password? oracle321
Enter the instance password that you would like to register this application instance with?  oracle321
Enter Oracle E-Business apps database user password ?

*** Log File = ../logs/appl/rgf/TXK/txkSetSSOReg_Wed_Apr_14_21_21_24_2021.xml

Beginning input parameter validation for LDAP Directory registration.
Input parameters validation for LDAP Directory registration completed.

BEGIN LDAP DIRECTORY REGISTRATION:
Beginning to register Application and Service containers if necessary.
Application and Service containers were created successfully if necessary.
Beginning to register application in the LDAP Directory.
Registration of application in LDAP Directory completed successfully.
[info] -> LOADING:  ../fs2/EBSapps/appl/fnd/12.0.0/admin/template/AppsOIDRegistration.tmp
Apr 14, 2021 9:34:59 PM oracle.ldap.util.LDIFLoader loadOneLdifFile
INFO: -> LOADING:  ../fs2/EBSapps/appl/fnd/12.0.0/admin/template/AppsOIDRegistration.tmp
Beginning to register provisioning profile in the LDAP Directory.
Registration of provisioning profile in the LDAP Directory completed successfully.
Application is now registered successfully with provisioning in the LDAP Directory.

End of ../fs2/EBSapps/appl/fnd/12.0.0/patch/115/bin/txkSetSSOReg.pl : No Errors encountered

EBS R12.2 - Check available Managed Server in Run and Patch filesystem

 In my case application servee is running on two nodes

Patch File System Managed Server on both nodes

SQL> set heading off
SQL> set pagesize 300;

SQL> SELECT
extractValue(XMLType(TEXT),'//oa_service_name[@oa_var="s_adminservername"]'),
extractValue(XMLType(TEXT),'//oacore_server_ports'),
extractValue(XMLType(TEXT),'//forms_server_ports'),
extractValue(XMLType(TEXT),'//oafm_server_ports'),
extractValue(XMLType(TEXT),'//forms-c4ws_server_ports'),
extractValue(XMLType(TEXT),'//oaea_server_ports')
from fnd_oam_context_files
where name not in ('TEMPLATE','METADATA')
and (status is null or status !='H')
and EXTRACTVALUE(XMLType(TEXT),'//file_edition_type')='patch'
and CTX_TYPE = 'A'; 

 Node 1

AdminServer
oacore_server2:7201
forms_server2:7401
oafm_server2:7601
forms-c4ws_server2:7801

Node 2

AdminServer
oacore_server1:7201
forms_server1:7401
oafm_server1:7601
forms-c4ws_server1:7801

Run File System Managed Server on both nodes

SQL>  SELECT
extractValue(XMLType(TEXT),'//oa_service_name[@oa_var="s_adminservername"]'),
extractValue(XMLType(TEXT),'//oacore_server_ports'),
extractValue(XMLType(TEXT),'//forms_server_ports'),
extractValue(XMLType(TEXT),'//oafm_server_ports'),
extractValue(XMLType(TEXT),'//forms-c4ws_server_ports'),
extractValue(XMLType(TEXT),'//oaea_server_ports')
from fnd_oam_context_files
where name not in ('TEMPLATE','METADATA')
and (status is null or status !='H')
and EXTRACTVALUE(XMLType(TEXT),'//file_edition_type')=' run';

Node 1

 AdminServer
oacore_server1:7202,oacore_server3:7204
forms_server1:7402
oafm_server1:7602
forms-c4ws_server1:7802

 Node2

AdminServer
oacore_server2:7202,oacore_server3:7204
forms_server2:7402
oafm_server2:7602
forms-c4ws_server2:7802