simpleoracle.com Open in urlscan Pro
192.0.78.201  Public Scan

Form analysis
4 forms found in the DOM

GET https://simpleoracle.com/

<form role="search" method="get" class="search-form" action="https://simpleoracle.com/">
  <label>
    <span class="screen-reader-text">Search for:</span>
    <input type="search" class="search-field" placeholder="Search …" value="" name="s" title="Search for:">
  </label>
  <button type="submit" class="search-submit">
    <span class="genericon genericon-search">
      <span class="screen-reader-text">Search</span>
    </span>
  </button>
</form>

GET https://simpleoracle.com/

<form role="search" method="get" class="search-form" action="https://simpleoracle.com/">
  <label>
    <span class="screen-reader-text">Search for:</span>
    <input type="search" class="search-field" placeholder="Search …" value="" name="s" title="Search for:">
  </label>
  <button type="submit" class="search-submit">
    <span class="genericon genericon-search">
      <span class="screen-reader-text">Search</span>
    </span>
  </button>
</form>

POST #

<form action="#" method="post" accept-charset="utf-8" id="subscribe-blog-blog_subscription-3" data-blog="12103537" data-post_access_level="everybody">
  <div id="subscribe-text">
    <p>Enter your email address to subscribe to this blog and receive notifications of new posts by email.</p>
  </div>
  <p id="subscribe-email">
    <label id="jetpack-subscribe-label" class="screen-reader-text" for="subscribe-field-blog_subscription-3"> Email Address </label>
    <input type="email" name="email" required="required" value="" id="subscribe-field-blog_subscription-3" placeholder="Email Address">
  </p>
  <p id="subscribe-submit">
    <input type="hidden" name="action" value="subscribe">
    <input type="hidden" name="source" value="https://simpleoracle.com/">
    <input type="hidden" name="sub-type" value="widget">
    <input type="hidden" name="redirect_fragment" value="subscribe-blog-blog_subscription-3">
    <input type="hidden" id="_wpnonce" name="_wpnonce" value="bd796ce403"><input type="hidden" name="_wp_http_referer" value="/"> <button type="submit" class="wp-block-button__link" name="jetpack_subscriptions_widget"> Sign me up! </button>
  </p>
</form>

<form id="jp-carousel-comment-form">
  <label for="jp-carousel-comment-form-comment-field" class="screen-reader-text">Write a Comment...</label>
  <textarea name="comment" class="jp-carousel-comment-form-field jp-carousel-comment-form-textarea" id="jp-carousel-comment-form-comment-field" placeholder="Write a Comment..."></textarea>
  <div id="jp-carousel-comment-form-submit-and-info-wrapper">
    <div id="jp-carousel-comment-form-commenting-as">
      <fieldset>
        <label for="jp-carousel-comment-form-email-field">Email (Required)</label>
        <input type="text" name="email" class="jp-carousel-comment-form-field jp-carousel-comment-form-text-field" id="jp-carousel-comment-form-email-field">
      </fieldset>
      <fieldset>
        <label for="jp-carousel-comment-form-author-field">Name (Required)</label>
        <input type="text" name="author" class="jp-carousel-comment-form-field jp-carousel-comment-form-text-field" id="jp-carousel-comment-form-author-field">
      </fieldset>
      <fieldset>
        <label for="jp-carousel-comment-form-url-field">Website</label>
        <input type="text" name="url" class="jp-carousel-comment-form-field jp-carousel-comment-form-text-field" id="jp-carousel-comment-form-url-field">
      </fieldset>
    </div>
    <input type="submit" name="submit" class="jp-carousel-comment-form-button" id="jp-carousel-comment-form-button-submit" value="Post Comment">
  </div>
</form>

Text Content

Skip to content


DUH! MICROSOFT DID IT AGAIN

(They always discourage & We always do)


Menu
Search

 * About
 * Contact me
 * Donate, Please

Search for: Search



INSTALL ORACLE APEX & ORDS 24.X.X ON ORACLE LINUX

On November 18, 2024November 19, 2024 By windows7bugsIn Uncategorized2 Comments

As you might have already noticed, mostly I post instructions for installing
Oracle software on Windows. Today, I will try to walk you through the Linux
installations for APEX, ORDS and Apache Tomcat. Please note, my attempts are
against an existing multitenant Oracle EBS database/environment. Extending
Oracle EBS database requires you to strictly adhere to multiple conditions.
Please make sure that you WILL refer this document prior attempting the rest of
the article.

 * Download APEX
 * Download ORDS

Make sure you will be downloading the latest software. If you are using Windows
machine to download the software, move them to a path that your oracle user has
access to, eg(/u01/oracle/Downloads)

Extract the archives to paths without space.

SSH/VNC to your linux box and switch to the path where you have extracted the
APEX installation sources. First of all, let us check whether the PDB already
has an APEX installation

Copy to Clipboard

oracle@apps.example.com:/home/oracle/Downloads/apex-latest/apex>sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Fri Nov 24 18:37:56 2023
Version 19.17.0.0.0

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


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.17.0.0.0

SQL> alter session set container="TEST";

Session altered.

select version from dba_registry where comp_id='APEX';


Normal cases there shouldn’t be a default installation of APEX available with
Oracle EBS database. Next step is to create a tablespace for APEX

Copy to Clipboard

SQL>CREATE TABLESPACE APEX DATAFILE '/u01/oratest/TEST/db/apps_st/data/apex01.dbf' 2G;

Once the new tablespace is created, we are ready to install APEX

Copy to Clipboard

SQL> @apexins.sql APEX APEX TEMP /i/

Depending upon the resources available, this installation could take
considerable time. Once done, execute “apxchpwd.sql” from the same session.

Copy to Clipboard

SYS> @apxchpwd.sql
...set_appun.sql
================================================================================
This script can be used to change the password of an Oracle APEX
instance administrator. If the user does not yet exist, a user record will be
created.
================================================================================
Enter the administrator's username [ADMIN]
User "ADMIN" does not yet exist and will be created.
Enter ADMIN's email [ADMIN] youremail@yourdomain.com
Enter ADMIN's password [] <== Use the same password across for a TEST environment
Created instance administrator ADMIN.




From the same session, execute “apex_rest_config.sql”

Copy to Clipboard

SQL> @apex_rest_config.sql

PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.


Enter a password for the APEX_LISTENER user              [] <== use the common password
Enter a password for the APEX_REST_PUBLIC_USER user              [] <== use the above password

Unlock all APEX database user accounts before moving ahead. We’ve the user
profile DEFAULT set with no password expiry. This is real pain and an expired
password could completely make your day spoiled. Follow PRODUCTION instructions
if you are setting it for it. Basically you only need to unlock the user
“apex_public_user”

Copy to Clipboard

SQL>alter user apex_listener identified by <<common password>> account unlock;
/
SQL>alter user apex_public_user identified by <<common password>> account unlock; 
/
SQL>alter user apex_rest_public_user identified by <<common password>> account unlock;
/


if you have valid Oracle support, download and apply the APEX patch set 6 before
installing ORDS. Please note, Oracle EBS server is a complex environment and
setting up ORDS on the same server is highly discouraged as ORDS software,
network PORTS requirement could cause total service outage. I recommend setting
up a different server for ORDS. Regardless, the following instructions are for
the same TEST server where APEX was installed already.

Oracle Linux comes preinstalled with OpenJDK and ORDS doesn’t support it. Hence
you must remove it and install a supported version. I am using JDK 17 for the
purpose. Please follow the instructions available here




https://support.cbaliveassist.com/hc/en-us/articles/360001020438-How-to-remove-OpenJDK-and-install-Oracle-JDK

or



Oracle EBS database doesn’t allow you to connect as “SYS” from remote locations
& you can override it by adding a password file which I will not encourage. If
you insist, please execute the following instructions.

Copy to Clipboard

cd $ORACLE_HOME/dbs
oracle@apps.example.com:/u01/oratest/TEST/db/tech_st/19.0.0/dbs>orapwd file=orapwTESTCDB  password=<<your password>> entries=5
SQL>alter system set REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE scope=spfile;
SQL>alter user sys identified by <<your password>> account unlock container=all;



Instead, you can “simply” grant “sysdba” role to system account!

Copy to Clipboard

sqlplus / as sysdba
SQL> grant sysdba to system container=all;

Revoke the grants once the activities are over.

Now switch to the path where latest ORDS installation sources were extracted and
execute “ords_installer_privileges.sql”

Copy to Clipboard

sqlplus / as sysdba
SQL> alter session set container="TEST";
SQL> @ords_installer_privileges.sql system


Change the permissions for “ords” executable now.

Copy to Clipboard

oracle@apps.example.com:/home/oracle/Downloads/ords-latest/bin>chmod +x ords

I setup ORDS on /u01/ords path. Hence, the following folders were created for
configuration files and logs. As oracle user

Copy to Clipboard

mkdir -p /u01/ords /u01/ords/config /u01/ords/logs

Now, we are ready to install ORDS interactive mode. I preferred interactive mode
than silent mode for a better understanding. ORDS 24.1 automatically picks up
TNS entries for the CDB, hence make sure to select “S” from the options
provided.

Copy to Clipboard

/home/oracle/Downloads/ords-latest/bin>ords --config /u01/ords/config install --interactive --log-folder /u01/ords/logs

Oracle REST Data Services - Interactive Install

  Enter a number to select the TNS net service name to use from /u01/oratest/TEST/db/tech_st/19.0.0/network/admin/tnsnames.ora or specify the database connection
    [1] TESTCDB      SERVICE_NAME=TESTCDB
    [2] EXTPROC_CONNECTION_DATA SID=PLSExtProc
    [S] Specify the database connection
  Choose [1]: S
  Enter a number to select the database connection type to use
    [1] Basic (host name, port, service name)
    [2] TNS (TNS alias, TNS directory)
    [3] Custom database URL
  Choose [1]:
 Enter the database host name [localhost]: apps.example.com
  Enter the database listen port [1521]: 1526
  Enter the database service name [TESTCDB]: TEST
  Provide database user name with administrator privileges.
    Enter the administrator username: system
  Enter the database password for system:




I’ve created two new tablespaces for ORDS, as version 24.x has many new prompts
& I’ve noticed that the EBS PDB doesn’t have a TEMP tablespace.

Copy to Clipboard

CREATE TABLESPACE ORDS DATAFILE '/u01/oratest/TEST/db/apps_st/data/ords01.dbf' SIZE 1G
/
CREATE TEMPORARY TABLESPACE ORDS_TEMP TEMPFILE '/u01/oratest/TEST/db/apps_st/data/ords_temp_01.dbf' SIZE 1G;
/

The next list of prompts is quite long & mostly we need to deal with 3,4,6,9
which are respectively for setting up ORDS_PUBLIC_USER user password and default
tablespaces followed by whether you want to configure as standalone server.

Copy to Clipboard

Enter a number to update the value or select option A to Accept and Continue
    [1] Connection Type: Basic
    [2] Basic Connection: HOST=apps.example.com PORT=1526 SERVICE_NAME=TEST
           Administrator User: system
    [3] Database password for ORDS runtime user (ORDS_PUBLIC_USER): <generate>
    [4] ORDS runtime user and schema tablespaces:  Default: SYSAUX Temporary TEMP
    [5] Additional Feature: Database Actions
    [6] Configure and start ORDS in Standalone Mode: Yes
    [7]    Protocol: HTTP
    [8]       HTTP Port: 8080
    [9]   APEX static resources location:
    [A] Accept and Continue - Create configuration and Install ORDS in the database
    [Q] Quit - Do not proceed. No changes
  Choose [A]: 3
  Enter your choice for the runtime user password (ORDS_PUBLIC_USER)
    [S] Specify your own password
    [G] Generate password
    [C] Cancel - No Changes
  Choose [G]: S
  Enter the database password for ORDS_PUBLIC_USER:
Confirm password:


Once the password for ORDS_PUBLIC_USER set, select the option 4

Copy to Clipboard

 Enter a number to update the value or select option A to Accept and Continue
    [1] Connection Type: Basic
    [2] Basic Connection: HOST=apps.example.com PORT=1526 SERVICE_NAME=TEST
           Administrator User: system
    [3] Database password for ORDS runtime user (ORDS_PUBLIC_USER): <specified password>
    [4] ORDS runtime user and schema tablespaces:  Default: SYSAUX Temporary TEMP
    [5] Additional Feature: Database Actions
    [6] Configure and start ORDS in Standalone Mode: Yes
    [7]    Protocol: HTTP
    [8]       HTTP Port: 8080
    [9]   APEX static resources location:
    [A] Accept and Continue - Create configuration and Install ORDS in the database
    [Q] Quit - Do not proceed. No changes
  Choose [A]: 4
  Enter the default tablespace for ORDS_METADATA and ORDS_PUBLIC_USER [SYSAUX]: ORDS
  Enter the temporary tablespace for ORDS_METADATA and ORDS_PUBLIC_USER [TEMP]: ORDS_TEMP




Now, we should decide whether to setup ORDS as a standalone server. This means,
ORDS will use built-in jetty server to start the ORDS services, which I don’t
prefer. Let us see how the options switch when we select option 6 (changes
highlighted). For this article I chose 6 to setup Standalone mode.

Copy to Clipboard

Choose [A]: 6
  Enter a number to update the value or select option A to Accept and Continue
    [1] Connection Type: Basic
    [2] Basic Connection: HOST=apps.example.com PORT=1526 SERVICE_NAME=TEST
           Administrator User: system
    [3] Database password for ORDS runtime user (ORDS_PUBLIC_USER): <specified password>
    [4] ORDS runtime user and schema tablespaces:  Default: ORDS Temporary ORDS_TEMP
    [5] Additional Feature: Database Actions
    [6] Configure and start ORDS in Standalone Mode: No
    [A] Accept and Continue - Create configuration and Install ORDS in the database
    [Q] Quit - Do not proceed. No changes

Copy to Clipboard

Choose [A]: 6
  Enter a number to update the value or select option A to Accept and Continue
    [1] Connection Type: Basic
    [2] Basic Connection: HOST=apps.example.com PORT=1526 SERVICE_NAME=TEST
           Administrator User: system
    [3] Database password for ORDS runtime user (ORDS_PUBLIC_USER): <specified password>
    [4] ORDS runtime user and schema tablespaces:  Default: ORDS Temporary ORDS_TEMP
    [5] Additional Feature: Database Actions
    [6] Configure and start ORDS in Standalone Mode: Yes
    [7]    Protocol: HTTP
    [8]       HTTP Port: 8080
    [9]   APEX static resources location:
    [A] Accept and Continue - Create configuration and Install ORDS in the database
    [Q] Quit - Do not proceed. No changes

If we are going ahead with Standalone mode enabled, then we must specify the
APEX static resources location by choosing option 9. Copy the images folder from
APEX installation folder to /u01/ords/

Copy to Clipboard

Choose [A]: 9
  Enter the APEX static resources location: /u01/ords/images

Copy to Clipboard

Enter a number to update the value or select option A to Accept and Continue
    Choose [A]: 9
  Enter the APEX static resources location: /u01/ords/images  

Now we can accept the changes and proceed by selecting the option “A”

Copy to Clipboard

Enter a number to update the value or select option A to Accept and Continue
    [1] Connection Type: Basic
    [2] Basic Connection: HOST=apps.example.com PORT=1526 SERVICE_NAME=TEST
           Administrator User: system
    [3] Database password for ORDS runtime user (ORDS_PUBLIC_USER): <specified password>
    [4] ORDS runtime user and schema tablespaces:  Default: ORDS Temporary ORDS_TEMP
    [5] Additional Feature: Database Actions
    [6] Configure and start ORDS in Standalone Mode: Yes
    [7]    Protocol: HTTP
    [8]       HTTP Port: 8080
    [9]   APEX static resources location: /u01/ords/images
    [A] Accept and Continue - Create configuration and Install ORDS in the database
    [Q] Quit - Do not proceed. No changes
  Choose [A]: A



ORDS installation is pretty straight forward. If you didn’t change the
ORDS_PUBLIC_USER password during the above, please keep in mind that changing
the password later could force you to reconfigure ORDS. Once the installation is
over, ORDS built-in jetty server automatically start. You can access the APEX
interface now from http://localhost:8080/ords. Use ctrl+c to kill the server.

Now we will install and configure Apache Tomcat for the webserver serving ORDS.
Version 9 was the latest one supported for ORDS, hence we will proceed with the
same version. Keep on checking Oracle’s announcements to know when the later
versions are certified for ORDS. Download the tar.gz file from the download
repository & extract to /u01/apache-tomcat/

Now switch to /u01/apache-tomcat/conf path and let us quickly make few
modifications. As my server is already running Oracle EBS on port number 8005, I
had to change this to next available port so that Apache Tomcat could go online.

Edit the “server.xml” file and make the changes as with the image below and make
sure that you are not running other application on the port 8080. If yes, you
will have to reconfigure ORDS once again with the next available port (I am not
sure whether it would truly affect incase if you are not using Standalone mode)

Now we have to copy few files to Apache repositories.

Copy to Clipboard

oracle@apps.example.com:/home/oracle/Downloads/ords-latest>cp ords.war /u01/apache-tomcat/webapps
oracle@apps.example.com:/home/oracle/Downloads/apex-latest/apex>scp -pr images /u01/apache-tomcat/webapps/i



It’s time to start Apache Tomcat server! As user oracle

Copy to Clipboard

oracle@apps.example.com:/u01/apache-tomcat/bin>export ORDS_CONFIG=/u01/ords/config
oracle@apps.example.com:/u01/apache-tomcat/bin>export JAVA_OPTS="-Dconfig.url=${ORDS_CONFIG} -Xms1024M -Xmx1024M"
oracle@apps.example.com:/u01/apache-tomcat/bin>./startup.sh
Using CATALINA_BASE:   /u01/apache-tomcat
Using CATALINA_HOME:   /u01/apache-tomcat
Using CATALINA_TMPDIR: /u01/apache-tomcat/temp
Using JRE_HOME:        /usr
Using CLASSPATH:       /u01/apache-tomcat/bin/bootstrap.jar:/u01/apache-tomcat/bin/tomcat-juli.jar
Using CATALINA_OPTS:
Tomcat started.

If there are no configuration issues, Apache Tomcat server should start serving
now.






UPGRADE & PATCH ORACLE APEX 23.X.X

On November 17, 2024November 24, 2024 By windows7bugsIn APEX, WindowsLeave a
comment

Couple of months back I published an article explaining how to install and
configure Oracle APEX on Windows. This article was targeting the products Oracle
APEX 23.2 with ORDS 23.4. Today we will see how to upgrade the APEX 23.x to APEX
24.1

Please note, APEX 24.1 was recently released and prone to have bugs. You will
not be able to patch the 24.1 to latest builds without a valid Oracle support
license. Hence, think twice before applying the instruction to your PRODUCTION
instances. For me, this attempt is merely for learning purposes.



As mentioned with linked article, I will be always downloading the latest
package from the download page.

Once the installation package downloaded, extract it to a path without spaces.
It is “OKAY” to download the installation guide as well ;)

Now, from an elevated command prompt (not Terminal) window, switch to the path
where you have extracted the APEX package. Please check the below image
carefully.

 1. Switch to the path were latest APEX software was extracted
 2. Connect to Oracle as Sys
 3. Switch to the container where Oracle APEX is already installed
 4. Execute the apexins.sql script

The upgrade process could take considerable time to finish based on the number
of objects already in the instance. If the upgrade process looks stopped over
couple of minutes, tap the Enter key. This should progress the upgrade process.
Usually this happens while recompiling objects.

Once the upgrade process is complete, logon to your ADMIN account.

As on date, set 6 is the latest patch available for APEX 24.1 & as mentioned
earlier, is available for users with valid support contracts.



If you don’t have Oracle support, let us configure ORDS with latest APEX images
now.



Rename/Delete the “images” folder from the ORDS configuration path and copy the
“images” folder from the latest installation source.

If Apache-Tomcat web server is used, replace the “i” folder with the “images”
folder from the latest source before restarting web services once again.

If you see an error like below (Version numbers will differ based on your
previous version and current version), this should be due to browser cache most
of the times. Please clear the browser cache and everything should be fine.

Applying latest APEX Patch for 24.1

As on date, Set 6 (36695709) is the latest patch for APEX

Once downloaded, extract the archive to a path without spaces. Switch to the
path from an elevated command prompt.

 1. Set the NLS language parameter
 2. Connect as SYS
 3. Switch to the container where you have APEX already installed. Please refer
    the readme file for specific instructions in case if the APEX is not
    installed on a PDB
 4. Execute catpatch.sql



This shouldn’t take much time to complete. Once the patching finishes, merge the
images folder with both ORDS and Apache-Tomcat. Restart the web services.

My sincere gratitude goes out to Tim Hall as always for helping me out of
difficult situations.




ORACLE EBS R12 | MONITORING TABLESPACE | SENDING EMAIL USING UTL_SMTP PACKAGE

On October 30, 2024October 30, 2024 By windows7bugsIn Linux, NOT a DBA, Oracle
Applications(R12), Oracle Database 19c, Oracle PL/SQLLeave a comment

Today we will checkout a PL/SQL procedure that will keep monitoring Oracle EBS
application specific Tablespaces and send emails to concerned parties whenever
the free storage falls below a certain percentage. The below stored procedure
checks the free spaces of all Tablespaces that have a name starting with “APPS”.

Copy to Clipboard

create or replace procedure XXTBLSPCE_MAIL( errbuf  OUT    VARCHAR2, retcode   OUT    VARCHAR2) AS
cursor c1 is
SELECT F.TABLESPACE_NAME,
(T.TOTAL_SPACE -  F.FREE_SPACE) "USED_MB",
(F.FREE_SPACE) "FREE_MB",
(T.TOTAL_SPACE) "TOTAL_MB",
(ROUND ((F.FREE_SPACE/T.TOTAL_SPACE)*100)) PER_FREE
 FROM   (
 SELECT       TABLESPACE_NAME,
 ROUND (SUM (BLOCKS*(SELECT VALUE/1024
 FROM V$PARAMETER
 WHERE NAME = 'db_block_size')/1024)
 ) FREE_SPACE
 FROM DBA_FREE_SPACE
 WHERE TABLESPACE_NAME LIKE 'APPS%'
 GROUP BY TABLESPACE_NAME
 ) F,
 (
 SELECT TABLESPACE_NAME,
 ROUND (SUM (BYTES/1048576)) TOTAL_SPACE
 FROM DBA_DATA_FILES
 GROUP BY TABLESPACE_NAME
 ) T
 WHERE F.TABLESPACE_NAME = T.TABLESPACE_NAME
 AND (ROUND ((F.FREE_SPACE/T.TOTAL_SPACE)*100)) < 10;
 
 /* Mail specific variables*/

mailhost VARCHAR2 (30) := 'smtp.yourmaildomain.com';
mail_conn utl_smtp.connection;
crlf VARCHAR2(2) := CHR (13) || CHR (10);
message_1 CLOB;
v_email varchar2(16000);
v_sub varchar2(10000);
vcount NUMBER := 0;

name_array     DBMS_SQL.varchar2_table;

CC_parties     VARCHAR2 (2000) := NULL;

Cursor ec1 is
(select 'rec1.it@yourmaildomain.com'  user_email from dual
union all
select 'rec2.it@yourmaildomain.com'  user_email from dual
);


 v_Mail_Exception Exception;
 
 --15/10/2020
 
   l_host_name varchar2(240);
   l_db_name varchar2(240);
 
 BEGIN
 
  BEGIN
        select sys_context ( 'USERENV', 'DB_NAME' ) db_name,
        sys_context ( 'USERENV', 'SERVER_HOST' ) db_host into l_db_name, l_host_name
        from dual;
        EXCEPTION 
        WHEN NO_DATA_FOUND THEN
        l_host_name := 'NO HOST';
        l_db_name := 'NO DBNAME';
        
        END;
        

mail_conn := utl_smtp.open_connection (mailhost, 25);
utl_smtp.helo (mail_conn, mailhost);
utl_smtp.mail (mail_conn, 'sender@yourmaildomain.com');

FOR m IN ec1
   LOOP
      vcount := vcount + 1;
      name_array (VCOUNT) := m.user_email;
   END LOOP;
   
   

 FOR n IN name_array.FIRST .. name_array.LAST
   LOOP
      CC_parties := CC_parties || ';' || name_array (n);
      UTL_SMTP.Rcpt (mail_conn, name_array (n));
   END LOOP;

if CC_parties is NULL then
RAISE v_Mail_Exception;
end if;

for ts in c1 loop
if (ts.FREE_MB < 1024) then
if (trim(message_1) is not null) then
message_1 := message_1||ts.tablespace_name ||' is running out of storage space. Current free space is '||ts.FREE_MB||' MB. ';
else
message_1 := ts.tablespace_name ||' is running out of storage space. Current free space is '||ts.FREE_MB||' MB. ';
end if;
end if;
end loop;

if (trim(message_1) is not null) then
message_1 := message_1||' ( Note: add datafiles with size nothing less than 2GB for APPS_TS tablespaces.) '; 

v_sub := 'Subject: '||'Table Space(s) running out of space! Urgent(Instance: '||l_host_name||', Database: '||l_db_name||' )';
 message_1 :=  'From: Oracle Database sender  < sender@yourmaildomain.com >' ||crlf||v_sub||crlf||'To:'||CC_parties||crlf||crlf||crlf||message_1;
 
 utl_smtp.data(mail_conn, 'MIME-Version: 1.0' ||CHR(13)||
 CHR(10)||'Content-type: text/html' || CHR(13)||CHR(10)||message_1);
 utl_smtp.quit (mail_conn);
 
 end if;
 

EXCEPTION
WHEN v_Mail_Exception Then
   null;
WHEN OTHERS THEN

--err_code := SQLCODE;
--err_msg := SUBSTR(SQLERRM, 1, 200);
message_1 := 'Failed to send email, error explanation:  '||SQLCODE||'  '||SQLERRM;

v_sub := 'Subject: '||'Table Space(s) running out of space! Urgent';
message_1 := 'From: Oracle Database sender  < sender@yourmaildomain.com >' ||crlf||v_sub||crlf||crlf||message_1;

 utl_smtp.data(mail_conn, 'MIME-Version: 1.0' ||CHR(13)||
 CHR(10)||'Content-type: text/html' || CHR(13)||CHR(10)||message_1);
 utl_smtp.quit (mail_conn);
 

 End;



Create a concurrent program and submit as a scheduled, setting up frequencies
based on your specific requirements. The above program works perfectly under the
below conditions.

Oracle EBS R12 12.2.10, 19c multi-tenant database, Local SMTP server.


WINDOWS BACKUP USING BITLOCKER AND POWERSHELL SECRETS

On October 26, 2024October 27, 2024 By windows7bugsIn PowerShell, Windows,
Windows 11Leave a comment

Definitely not for an environment that boasts TBs of data that should be backed
up the most efficient way. This solution is best suitable for environments where
backups sizes are not exceeding couple of TBs in size as the BitLocker
encryption will slow down the entire process by encrypting each new file during
the initial copying. Once the first sync is over, consecutive synching should be
far faster as only new and modified files will be copied & encrypted to the
destination volume.

Further, the performance of the entire process depends upon the hardware
resources available also. We deployed this solution for one of our businesses
that has approximately 500GB total size, consist of hundreds of thousands of
small files. The first robocopy run on each external disk over USB 3.0 took
approximately 10-12 hours and the consecutive runs completed within 20-22
minutes. We used Tandberg RDX Quickstor External solution for this purpose. This
time could be brought down to couple of hours if both source and destination
volumes are based on SSD.

The solution approach was like this. All RDX tapes for 6 days per week were
formatted as NTFS volumes, enabled BitLocker on them immediately after
formatting.

The same machine Powershell was configured to run remote signed scripts.
Powershell sample as below.

Copy to Clipboard

PS C:\Users\rajesh> Get-ExecutionPolicy
RemoteSigned
PS C:\Users\rajesh> $secretPW = "MySecretPassword123@" | ConvertTo-SecureString -AsPlainText -Force
PS C:\Users\rajesh> $secretPW | Export-Clixml -Path C:\Scripts\default.xml
PS C:\Users\rajesh> $MySecret = Import-Clixml -Path C:\Scripts\default.xml
PS C:\Users\rajesh> echo $MySecret
System.Security.SecureString
PS C:\Users\rajesh> Unlock-Bitlocker F: -Password $MySecret

Interested about what happens when your password is converted to Secure String?
Check the image below. That’s how a secure string looks & someone who has access
to your computer/server could still be able to convert it to plain text and get
your password. Hence, this is not a 100% fail safe solution, however it could be
pretty effective against robots/malicious codes.

The above exercises were to confirm everything is in place and working properly
before developing the script that will be used for regular backups. Name it
anything and refer it inside the scheduled job.

Copy to Clipboard

#DailyBackup.ps1
#Author: Rajesh Thampi
#Date: 14.10.2024

#Read the BitLocker password from the xml file
$Secret = Import-Clixml -Path C:\Scripts\default.xml

#Unlock the volume that is BitLocer protected.
Unlock-Bitlocker F: -Password $Secret

#Setup source and destination paths.
$source='D:\Some_Folder'
$destination='F:'

#Start robocopy. Use /ZB switches to avoid recyclebin related issues.
Robocopy.exe $source $destination /E /DCOPY:DAT /XO /ZB

#After the copying, lock the drive immediately.
manage-bde -lock F: -ForceDismount




By the way, RDX hardware is not cheap. Hence you should consider alternative
mediums. The advantage of a BitLocker protected volume is, even during a
ransomware attack these volumes could be completely immune, unless the volume is
open for backups during the attack. Well, you never know.

References

 * https://www.ajni.it/2020/05/powershell-encrypt-and-store-your-credentials-securely/
 * https://superuser.com/questions/1836226/powershell-how-to-unlock-bitlocker-encrypted-drive-simple-way
 * https://winaero.com/how-to-lock-bitlocker-encrypted-drive-in-windows-10/
 * https://stackoverflow.com/questions/68947647/create-secretstore-vault-without-a-password




ORACLE EBS R12 12.2 DATABASE CLONING

On October 24, 2024October 28, 2024 By windows7bugsIn Linux, NOT a DBA, Oracle
Applications(R12), Oracle Database 19c, Oracle relatedLeave a comment

I am not a certified Database/APPS DBA. I wanted to develop a shell/bash script
or set of scripts that could duplicate the Oracle EBS PRODUCTION database on
demand on a test instance. I thought of doing it as a single script, step by
step and later decided to go with multiple scripts to narrow down the damages/or
better error correction. Please note, you cannot just copy and start using these
scripts at your environment already! You may have to modify many of the
parameters & kindly be reminded that, these scripts were developed for an
environment that was being cloned repeatedly. Remember to run the main script as
“root”

Environment: OS: Oracle Linux 7, Oracle EBS R12 12.2.10, Oracle database 19c,
multi-tenant. Approximate size 700GB



Let us create a parameter file for the duplication of EBS database on the TEST
instance. The hostname and few other details will be automatically picked up by
the shell scripts, however, few other details are provided by the parameter
file. This approach gives me greater flexibility to utilize the scripts with
different hostnames and contexts. I call this parameter file as
“restoreparam.txt”. If you are planning to change the name, you have to replace
it in each and every other bash script that is used within the main script.

Copy to Clipboard

#Oracle base
s_target_base=/u01/test/
# Source CDB sid
s_source_cdbSid=PRODCDB
# Target CDB sid
s_target_cdbSid=DEVPCDB
# Source EBS database sid (PDB)
s_source_dbSid=PROD
# Target EBS database sid (PDB)
s_target_dbSid=DEVP
# We have database files for both CBD and EBS kept in different paths
# Defining these paths and modifying the RMAN run instructions are crucial for successful restoration and cloning.
s_source_data_path_1=/u01/prod/oradata/PRODCDB/
s_source_data_path_2=/u01/prod/PROD/db/apps_st/data/
# Target data paths
s_data_path_1=/u01/test/oradata/DEVPCDB/
s_data_path_2=/u01/test/DEVP/db/apps_st/data/
# Target PDB Service requires it's own listener information
s_listener_port=1526
# Oracle and Appl manager OS level users
s_db_user=oracle
s_appl_manager=applmgr
# EBS user, by default it is "apps"
s_apps_user=apps
s_apps_pass=********
# Oracle database system account password
s_system_pass=*******
# Path where RMAN backup chunks are kept
s_rman_path=/u04/RMAN/DAILYBKP
# Path where this file be created.
s_rman_param_path=/u04/RMAN/RESTORE
# Target system Oracle inventory path
s_ora_invenoty_path=/u01/test/oraInventory
# Oracle inventory entry identifier for the database. Using this string, entry for the database will be
# deleted from the inventory file. So don't make mistakes
s_ora_inv_dbString=DEVP_DB__u01_test_DEVP_db_tech_st_19_0_0



You can’t make mistakes with the above parameter file. Now we will create the
main shell script “doclone.sh”

Copy to Clipboard

#!/bin/sh

: <<'END'
This script & associated must be executed as root
This script is meant for single instances.
This script expects both ORACLE, APPLMGR users bash profiles sourcing environment files.
This script was last tested against Oracle EBS R12 12.2.10/19c
Last modified on: 21st October 2024
Author: Rajesh Thampi
License: Public
END

me=$(basename "$0")
parameter_file="/u04/RMAN/RESTORE/restoreparam.txt"
ORACLE_USER=`grep 's_db_user' $parameter_file | cut -d "=" -f2`
APPS_USER=`grep 's_appl_manager' $parameter_file | cut -d "=" -f2`
db_path_1=`grep 's_data_path_1' $parameter_file | cut -d "=" -f2`
db_path_2=`grep 's_data_path_2' $parameter_file | cut -d "=" -f2`
rman_path=`grep 's_rman_path' $parameter_file | cut -d "=" -f2`

ORACLE_HOME=$(su - ${ORACLE_USER} -c 'echo "$ORACLE_HOME"')
ORACLE_SID=$(su - ${ORACLE_USER} -c 'echo "$ORACLE_SID"')
ORACLE_CONTEXT=$(su - ${APPS_USER} -c 'echo "$CONTEXT_NAME"')
APPS_SCRIPTS_HOME=$(su - ${APPS_USER} -c 'echo "$RUN_BASE/inst/apps/$CONTEXT_NAME/admin/scripts"')
DB_SCRIPTS_HOME=$(su - ${ORACLE_USER} -c 'echo "$ORACLE_HOME"')/appsutil/scripts/$ORACLE_CONTEXT



# We'll stop the application and database instances now.
echo "We'll shutdown the EBS instace now"

sh startstopebsr12.sh stop
if [ $? -eq 0 ]; then
echo "We'll shutdown the EBS instace now"
sleep 60
else
echo "There was an error shutting down the instance."
exit 1
fi

# As we are cloning the instance, we can kill all ORACLE & APPLMGR processes immediately
pkill -9 -u ${APPS_USER}
pkill -9 -u ${ORACLE_USER}
echo "All services for ORACLE, APPLMGR are terminated now"

# remove the datafiles from data tops now
rm -rf ${db_path_1}*
rm -rf ${db_path_2}*
echo "All data files are removed from the data tops now"

#change of the ownership of the rman chunks now
chown -R ${ORACLE_USER}:oinstall ${rman_path}

sleep 10

# Start the database as not mounted

sh startdbnomount.sh
if [ $? -eq 0 ]; then
    echo "Database started nomount"
else
    echo "There were some errors starting the database in the nomount status"
    exit 1
fi

# Start RMAN Restore

sleep 10
echo "Starting RMAN restore processes now, this could take many hours based on the current size of the database"
sh dormanrestore.sh

if [ $? -eq 0 ]; then
    echo "RMAN restore completed"
else
    echo "There were some errors starting the database in the nomount status"
    exit 1
fi
sleep 10

# Startup the database and disable archive logging

sh startdbmount.sh
if [ $? -eq 0 ]; then
    echo "Starting database and disabling archivelog. Sleep 15 seconds"
else
    exit 1
fi
sleep 15
# We will create a backup for the oracle inventory file and remove the database entry before running the adcfgclone
sh dofirstclone.sh
if [ $? -eq 0 ]; then
    echo "Initial Cloning completed. Sleep 15 seconds"
else
    exit 1
fi
sleep 15

# Setup the PDB and related services now
sh dodbsetup.sh
if [ $? -eq 0 ]; then
    echo "PDB recreated and all services are restarted, will sleep 15 seconds"
else
    exit 1
fi
sleep 15

# We will setup the UTL files and run the adcfgclone
sh doutlfile.sh
if [ $? -eq 0 ]; then
    echo "UTL directories set, final cloning completed. Will sleep 15 seconds"
else
    exit 1
fi
sleep 15

# Finally we will run the autoconfig on database and both application file systems
sh doautoconfig.sh
if [ $? -eq 0 ]; then
    echo "All done. You can try to restart the applicatio now. Sleeping 10"
else
    exit 1
fi



Now we will create other shells scripts that are called from the main script and
I will try to explain them briefly as much of them will be pretty familiar for a
seasoned DBA. Script “startstopebsr12.sh” accepts one input value, either
“start” or “stop”. This script is used to stop the instance from the main script
and not parameterized because I use it for multiple contexts.

Copy to Clipboard

#!/bin/sh

: <<'END'
This script is meant for single instances.
This script expects both ORACLE, APPLMGR users bash profiles sourcing environment files.
This script was last tested against Oracle EBS R12 12.2.10/19c
Last modified on: 2nd October 2024
Author: Rajesh Thampi
License: Public
END


# I will not be using the clone parameter file as this script is multiple places in different contexts.
# If you want to use it exclusively for the cloning purpose, please refer it here.

me=$(basename "$0")
ORACLE_SID=$(su - oracle -c 'echo "$ORACLE_SID"')
APPS_ORACLE_CONTEXT=$(su - applmgr -c 'echo "$CONTEXT_NAME"')
APPS_SCRIPTS_HOME=$(su - applmgr -c 'echo "$RUN_BASE/inst/apps/$CONTEXT_NAME/admin/scripts"')
DB_SCRIPTS_HOME=$(su - oracle -c 'echo "$ORACLE_HOME"')/appsutil/scripts/$APPS_ORACLE_CONTEXT

if [[ -z $1 ]]; then
    echo "No parameter was passed"
    exit 1
else
    if [[ "$1" == "start" ]]; then
        echo "All Oracle EBS R12 Services will be started now."
        su - oracle -c "sh $DB_SCRIPTS_HOME/adcdbctl.sh start;"
        if [ $? -ne 0 ]; then
            echo "Couldn't start the database services successfully. Aborting"
            exit 1
        else
            su - oracle -c "sh $DB_SCRIPTS_HOME/adcdblnctl.sh start $ORACLE_SID;"
        fi
        if [ $? -ne 0 ]; then
            echo "Couldn't start the listener services successfully. Aborting"
            exit 1
        else
            su - applmgr -c "cd $APPS_SCRIPTS_HOME;{ echo apps; echo apps; echo welcome123; } | adstrtal.sh;"
        fi
        if [ $? -ne 0 ]; then
            echo "Couldn't start the Application services successfully. Check log files for errors and try again"
        else
            echo "All EBS Services were successfully started."
        fi

    elif [[ "$1" == "stop" ]]; then
        echo "All Oracle EBS R12 Services will be stopped now."
        su - applmgr -c "cd $APPS_SCRIPTS_HOME;{ echo apps; echo apps; echo welcome123; } | adstpall.sh;"
        if [ $? -ne 0 ]; then
            echo "Couldn't stop the application services successfully. Aborting"
            exit 1
        else
            su - oracle -c "sh $DB_SCRIPTS_HOME/adcdblnctl.sh stop $ORACLE_SID;"
        fi
        if [ $? -ne 0 ]; then
            echo "Couldn't stop the Listener services successfully. Aborting"
            exit 1
        else
            su - oracle -c "sh $DB_SCRIPTS_HOME/adcdbctl.sh stop immediate;"
        fi
        if [ $? -ne 0 ]; then
            echo "Couldn't stop the Database services successfully. Check log files for errors and try again."
            exit 1
        else
            echo "All EBS Services were successfully stopped."
        fi
    else
        echo "Syntax: sh $me start/stop"

    fi
fi



The main script will continue once after both application and database instances
are stopped. data files from the data paths will be deleted & ownership of the
RMAN files will be set for Oracle user. Oracle services will restarted and the
database will not be mounted using the script “startdbnomount.sh”. I didn’t feel
parameterization was necessary for this script ;)

Copy to Clipboard

#!/bin/sh
su - oracle -c "sqlplus / as sysdba <<EOF
startup nomount;
quit;
EOF"

Now, the main script will call “dormanrestore.sh” script, which is one of the
most critical scripts, duplicating the PRODUCTION database. You have to
carefully adjust the below to ensure that your database is duplicated
successfully. Please give special attentions to comments provided inside the
script.

Copy to Clipboard

#!/bin/sh

parameter_file="/u04/RMAN/RESTORE/restoreparam.txt"
rman_param_path=$(grep 's_rman_param_path' $parameter_file | cut -d "=" -f2)
target_cdbsid=$(grep 's_target_cdbSid' $parameter_file | cut -d "=" -f2)
rman_path=$(grep 's_rman_path' $parameter_file | cut -d "=" -f2)
s_data_path_1=$(grep 's_source_data_path_1' $parameter_file | cut -d "=" -f2)
s_data_path_2=$(grep 's_source_data_path_2' $parameter_file | cut -d "=" -f2)
t_data_path_1=$(grep 's_data_path_1' $parameter_file | cut -d "=" -f2)
t_data_path_2=$(grep 's_data_path_2' $parameter_file | cut -d "=" -f2)

LOGFILE=${rman_param_path}"/full_restore_`date +%d%b%y_%H%M%S`.log"

# Make sure that db_file_name_convert & number of channels are adjusted based on your specific environments.
# Keeping multiple channels open could directly affect the restoration and overall performance of the process.
# Adjust the number of redo log files, size & path based on your specific requirements.
echo "RMAN restore will start now"

su - oracle <<EOF
rman auxiliary / log='$LOGFILE' <<RMN
run
{
ALLOCATE AUXILIARY CHANNEL c1 DEVICE TYPE disk;
ALLOCATE AUXILIARY CHANNEL c2 DEVICE TYPE disk;
ALLOCATE AUXILIARY CHANNEL c3 DEVICE TYPE disk;
ALLOCATE AUXILIARY CHANNEL c4 DEVICE TYPE disk;
duplicate database to "${target_cdbsid}" backup location '${rman_path}' nofilenamecheck
db_file_name_convert=('${s_data_path_1}','${t_data_path_1}','${s_data_path_2}','${t_data_path_2}')
LOGFILE
GROUP 1 (
'${t_data_path_1}redo01a.log',
'${t_data_path_1}redo01b.log'
) SIZE 1200M ,
GROUP 2 (
'${t_data_path_1}redo02a.log',
'${t_data_path_1}redo02b.log'
) SIZE 1200M ,
GROUP 3 (
'${t_data_path_1}redo03a.log',
'${t_data_path_1}redo03b.log'
) SIZE 1200M ,
GROUP 4 (
'${t_data_path_1}redo04a.log',
'${t_data_path_1}redo04b.log'
) SIZE 1200M ;
}
RMN
EOF



RMAN duplication could take hours based on multiple factors. Size of the
database, channels and hardware efficiency. The log file will bear the data and
time stamp, keep monitoring it for the progress. Once RMAN successfully
completes, main script will call the script “startdbmount.sh”. This script will
start the database and mount it, without opening it. The archive logging will be
disabled during this stop for the duplicated database.

Copy to Clipboard

#!/bin/sh

su - oracle -c "sqlplus / as sysdba <<EOF
shutdown immediate;
startup mount;
alter database noarchivelog;
shutdown immediate;
quit;
EOF"

Now the main script will call “dofirstclone.sh”, that will do the initial clone
of the database. A number of parameters are referenced from the parameter file,
hence make sure that your parameter is constructed with utmost attention.

Copy to Clipboard

#!/bin/sh

parameter_file="/u04/RMAN/RESTORE/restoreparam.txt"
rman_param_path=$(grep 's_rman_param_path' $parameter_file | cut -d "=" -f2)
ora_inventory_path=$(grep 's_ora_invenoty_path' $parameter_file | cut -d "=" -f2)
ora_inv_dbString=$(grep 's_ora_inv_dbString' $parameter_file | cut -d "=" -f2)
ORACLE_USER=$(grep 's_db_user' $parameter_file | cut -d "=" -f2)
APPS_USER=$(grep 's_appl_manager' $parameter_file | cut -d "=" -f2)

ORACLE_HOME=$(su - ${ORACLE_USER} -c 'echo "$ORACLE_HOME"')
ORACLE_SID=$(su - ${ORACLE_USER} -c 'echo "$ORACLE_SID"')
ORACLE_CONTEXT=$(su - ${APPS_USER} -c 'echo "$CONTEXT_NAME"')
APPS_SCRIPTS_HOME=$(su - ${APPS_USER} -c 'echo "$RUN_BASE/inst/apps/$CONTEXT_NAME/admin/scripts"')
DB_SCRIPTS_HOME=$(su - ${ORACLE_USER} -c 'echo "$ORACLE_HOME"')/appsutil/scripts/$ORACLE_CONTEXT

# Application user password

APPS_PASSWD=$(grep 's_apps_pass' $parameter_file | cut -d "=" -f2)

su - ${ORACLE_USER} <<EOF
cd ${ora_inventory_path}/ContentsXML
cp -f inventory.xml inventory_backup.xml
awk '!/'${ora_inv_dbString}'/' inventory.xml  > tmpfile.xml && mv tmpfile.xml inventory.xml
EOF

su - ${ORACLE_USER} <<EOF
cd $ORACLE_HOME/appsutil/clone/bin
{ echo $APPS_PASSWD; } | perl adcfgclone.pl dbTechStack $ORACLE_HOME/appsutil/${ORACLE_CONTEXT}.xml
EOF

echo "Cloning successfully completed, will sleep 10 seconds"
sleep 10

su - ${ORACLE_USER} <<EOF
cd $ORACLE_HOME/appsutil/install/$ORACLE_CONTEXT
sqlplus / as sysdba;
startup;
@adupdlib.sql so;
shutdown immediate;
EOF
echo "Database shutdown completed. Sleep 15 seconds"
sleep 15

su - ${ORACLE_USER} <<EOF
sqlplus / as sysdba;
startup;
quit;
EOF
echo "Database restart completed."



Initial cloning should happen without throwing errors. Once the initial cloning
done, next script “dodbsetup.sh” setup will drop the PDB from PRODUCTION and
recreate the TEST PDB instance, rename the services, reconfigure the local
listener for PDB etc.

Copy to Clipboard

#!/bin/sh
ORACLE_SID=$(su - oracle -c 'echo "$ORACLE_SID"')
ORACLE_HOME=$(su - oracle -c 'echo "$ORACLE_HOME"')
parameter_file="/u04/RMAN/RESTORE/restoreparam.txt"
SOURCE_SID=`grep 's_source_dbSid' $parameter_file | cut -d "=" -f2`
TARGET_SID=`grep 's_target_dbSid' $parameter_file | cut -d "=" -f2`
ORACLE_BASE=`grep 's_target_base' $parameter_file | cut -d "=" -f2`
ORACLE_USER=`grep 's_db_user' $parameter_file | cut -d "=" -f2`
LISTENER_PORT=`grep 's_listener_port' $parameter_file | cut -d "=" -f2`
TARGET_LISTNER=$HOSTNAME:$LISTENER_PORT

source_ebs_patch=$SOURCE_SID"_ebs_patch"
target_ebs_patch=$TARGET_SID"_ebs_patch"

su - ${ORACLE_USER} <<EOF
sqlplus / as sysdba
alter pluggable database ${SOURCE_SID} unplug into '${ORACLE_HOME}/dbs/${SOURCE_SID}.xml';
drop pluggable database ${SOURCE_SID};
create pluggable database ${TARGET_SID} using '${ORACLE_HOME}/dbs/${SOURCE_SID}.xml' NOCOPY SERVICE_NAME_CONVERT=('ebs_${SOURCE_SID}','ebs_${TARGET_SID}','${SOURCE_SID}_ebs_patch', '${TARGET_SID}_ebs_patch');
alter pluggable database all open read write services=all;
alter pluggable database all save state;
alter session set container=${TARGET_SID};
alter system set local_listener='${TARGET_LISTNER}' scope=spfile;
shutdown immediate;
startup;
exit;
EOF

echo "Finished setting up PDB. Sleep 15 seconds after shutdown"
sleep 15

Creation of the PDB and rest shouldn’t take too much time. Now it is time for
the final cloning, that will setup the UTL directories and more. Let us see the
“doutlfile.sh” now. Please note, prior executing the below script, you must
create a copy of PDBNAME_utlfiledir.txt that is available in $ORACLE_HOME/dbs/
path to PDBNAME_utlfiledir.txt.backup (example: DEVP_utlfiledir.txt to
DEVP_utlfiledir.txt.backup). This backup will be used for replacing the UTL file
directory paths setup within this cycle.

Copy to Clipboard

#!/bin/sh

parameter_file="/u04/RMAN/RESTORE/restoreparam.txt"
target_dbsid=$(grep 's_target_dbSid' $parameter_file | cut -d "=" -f2)

ORACLE_USER=`grep 's_db_user' $parameter_file | cut -d "=" -f2`
APPS_USER=`grep 's_appl_manager' $parameter_file | cut -d "=" -f2`
echo ${ORACLE_USER}

APPS_PASSWD=`grep 's_apps_pass' $parameter_file | cut -d "=" -f2`
EBS_USER=`grep 's_apps_user' $parameter_file | cut -d "=" -f2`
SYSTEM_PASSWD=`grep 's_system_pass' $parameter_file | cut -d "=" -f2`

ORACLE_HOME=$(su - ${ORACLE_USER} -c 'echo "$ORACLE_HOME"')
ORACLE_SID=$(su - ${ORACLE_USER} -c 'echo "$ORACLE_SID"')
ORACLE_CONTEXT=$(su - ${APPS_USER} -c 'echo "$CONTEXT_NAME"')
APPS_SCRIPTS_HOME=$(su - ${APPS_USER} -c 'echo "$RUN_BASE/inst/apps/$CONTEXT_NAME/admin/scripts"')
DB_SCRIPTS_HOME=$(su - ${ORACLE_USER} -c 'echo "$ORACLE_HOME"')/appsutil/scripts/$ORACLE_CONTEXT


# We will use a copy of the utilfile for repeated cloning.

su - ${ORACLE_USER} <<EOF
cd ${ORACLE_HOME}
source ${ORACLE_CONTEXT}.env
sh $DB_SCRIPTS_HOME/adcdblnctl.sh start ${ORACLE_SID}


{ echo $APPS_PASSWD; } | perl ${ORACLE_HOME}/appsutil/bin/txkCfgUtlfileDir.pl -contextfile=${ORACLE_HOME}/appsutil/${ORACLE_CONTEXT}.xml -oraclehome=${ORACLE_HOME} -outdir=${ORACLE_HOME}/appsutil/log -mode=getUtlFileDir
cp -f ${ORACLE_HOME}/dbs/${target_dbsid}_utlfiledir.txt.backup ${ORACLE_HOME}/dbs/${target_dbsid}_utlfiledir.txt
{ echo $APPS_PASSWD; echo $SYSTEM_PASSWD; } | perl ${ORACLE_HOME}/appsutil/bin/txkCfgUtlfileDir.pl -contextfile=${ORACLE_HOME}/appsutil/${ORACLE_CONTEXT}.xml -oraclehome=${ORACLE_HOME} -outdir=${ORACLE_HOME}/appsutil/log -mode=setUtlFileDir -servicetype=onpremise
{ echo $APPS_PASSWD; } | perl ${ORACLE_HOME}/appsutil/bin/txkCfgUtlfileDir.pl -contextfile=${ORACLE_HOME}/appsutil/${ORACLE_CONTEXT}.xml -oraclehome=${ORACLE_HOME} -outdir=${ORACLE_HOME}/appsutil/log -mode=syncUtlFileDir -skipautoconfig=yes
cd ${ORACLE_HOME}/appsutil/clone/bin
{ echo $APPS_PASSWD; } | perl adcfgclone.pl dbconfig ${ORACLE_HOME}/appsutil/${ORACLE_CONTEXT}.xml


su - ${APPS_USER} -c "sqlplus ${EBS_USER}/${APPS_PASSWD}@${target_dbsid} <<EOF
EXEC FND_CONC_CLONE.SETUP_CLEAN;
Commit;
quit;
EOF"



Now we will do the autoconfiguration on both database and application tiers
using the script “doautoconfig.sh”. I’m leaving the application env file path
hardcoded as I missed parameterizing it in the file. Add a new value to the
parameter file if you want to refer it from the it. Please note, both my Oracle
and Applmgr users bash profiles are modified to call the respective environment
files. Missing them in the bash profiles could be one of the reasons for the
scripts failing.

Copy to Clipboard

#!/bin/sh

parameter_file="/u04/RMAN/RESTORE/restoreparam.txt"
ORACLE_USER=`grep 's_db_user' $parameter_file | cut -d "=" -f2`
APPS_USER=`grep 's_appl_manager' $parameter_file | cut -d "=" -f2`
APPS_PASSWD=`grep 's_apps_pass' $parameter_file | cut -d "=" -f2`
SYSTEM_PASSWD=`grep 's_system_pass' $parameter_file | cut -d "=" -f2`

ORACLE_SID=$(su - ${ORACLE_USER} -c 'echo "$ORACLE_SID"')
ORACLE_HOME=$(su - ${ORACLE_USER} -c 'echo "$ORACLE_HOME"')
ORACLE_CONTEXT=$(su - ${APPS_USER} -c 'echo "$CONTEXT_NAME"')
DB_SCRIPTS_HOME=$(su - ${ORACLE_USER} -c 'echo "$ORACLE_HOME"')/appsutil/scripts/$ORACLE_CONTEXT

su - ${ORACLE_USER} <<EOF
cd $DB_SCRIPTS_HOME
{ echo $APPS_PASSWD; } | sh adautocfg.sh
EOF


su - ${APPS_USER} <<EOF
{ echo $APPS_PASSWD; } | adautocfg.sh;
sqlplus system/${SYSTEM_PASSWD};
alter trigger ebs_logon disable;
quit;
EOF

su - ${APPS_USER} <<EOF
source /u02/oracle/apps/EBSapps.env patch;
{ echo $APPS_PASSWD; } | adautocfg.sh;
sqlplus system/${SYSTEM_PASSWD};
alter trigger ebs_logon enable;
quit;
EOF

su - ${APPS_USER} <<EOF
source /u02/oracle/apps/EBSapps.env run;
EOF

That’s all. If there were no errors, it is okay to start the test instance now.
If you are coming across issues that I have overlooked, do let me through
comments.


HOW TO DISABLE WINDOWS VIRTUALIZATION/DEVICE BASED SECURITY #VBS

On October 9, 2024October 16, 2024 By windows7bugsIn Windows, Windows 116
Comments

I use virtual machines almost everyday & recently I took the risk of upgrading
my finely tuned Windows 11 23H2 development laptop to 24H2 using my insider
account. The upgrade was smooth without any troubles and none of the existing
software that I use reported issues. Then I wanted to use my virtual machines!

As usual, multiple security related features are added or enhanced in 24H2 build
and many of them are totally depending upon Hyper-V, Microsoft’s own
virtualization platform. So, after a successful upgrade to 24H2, don’t be
surprised if your find Hyper-V running in the background while so called
“Windows Features are still disabled for it”. Read more about VBS here.



As our primary objective is to disable the VBS so that we can go back with our
virtual machines, you should know the most disappointing thing at this point,
there are no toggle switches available to completely disable VBS. You have to
toggle few switches, run some scripts, fiddle around with group policy etcetera
to get the task done.

You can use good old “System Information” to check whether VBS is
running/enabled. I’ve enabled VBS once again to demonstrate how to disable it
for this article. Please note, depending upon your hardware, you may see few or
more details than that are visible in the picture below.

As per the system information gathered, my computer is currently enabled with
VBS. This laptop has a TPM 2.0 chip & UEFI secure boot disabled.

Based on whether the secure boot is enabled, disabling VBS can become pretty
complex. I will share some links to Microsoft articles explaining how to deal
with such situations as well.

First we will see how to disable the virtualization based security when the
secured boot is disabled. Go to “Settings->Privacy & Security->Device Security”

Toggle the Memory integrity to turned off.

Reboot & check whether the VBS is disabled. If not, proceed to next step.



Go to this link Download Device Guard and Credential Guard hardware readiness
tool from Official Microsoft Download Center, download the archive file and
extract it to a folder.



Now open an elevated command prompt, switch to the path where you have extracted
the file that you downloaded earlier.

Accept the prompts & restart your computer. Restarting is a must after every
attempt. There will be two prompts asking you whether to disable two different
features, which should be accepted. Unfortunately I couldn’t do a screen capture
for them while I was trying to fix my PC. Much of the times, this should resolve
the issue & VBS should be disabled.

If the VBS is not yet disabled, try setting up the Group policy. I truly hope
you know what you are doing!

Open group policy editor, Local Computer Policy->Computer
Configuration->Administrative Templates->System->Device Guard->Turn On
Virtualization Based Security->Disabled

Now restart your computer once again. Check whether the VBS is disabled or not.

Still having troubles? Let’s check few more things.

Run “System Information” once again & check the elements as marked in the image
below.

If VBS is still running and “A hypervisor has been detected. Features required
for Hyper-V will not displayed” is shown, it means Hyper-V is still running
after the above exercises. We can try to disable Hyper-V from the boot now.

Open powershell/Terminal as Administrator & execute the following command.

Copy to Clipboard

bcdedit /enum | findstr -i hypervisorlaunchtype



If running the command returns nothing, it means hypervisor is turned off in the
boot & fixing the VBS looks almost impossible (in case if all the above were
already tried). On the other hand, if it returns one of the below

 * hypervisorlaunchtype Auto
 * hypervisorlaunchtype On

Then you still have a chance to fix the VBS issues. Execute the below command in
the same powershell session.

Copy to Clipboard

bcdedit /set hypervisorlaunchtype off

Reboot and check the system information window once again and you should see
something similar to below image.

If VBS is shown as “Not Enabled” or “Not Running”, then you are all good. Now,
the million dollar question is, should you disable Hyper-V at all? Windows is
building many features on top of Hyper-V that will provide a sandboxed
environment for the OS. Tomorrow, Microsoft might decide not to let the users
disable Hyper-V using hacks. Regardless, let us all hope Oracle VirtualBox or
VMWare tweaks their hypervisors so that they can coexist with Hyper-V and still
ensure the same performance.

Let’s see what Copilot has to tell us about the potential risks associated with
disabling VBS

References

 * https://superuser.com/questions/1778061/green-turtle-snail-mode-slow-performance-indicator-on-virtualbox-vm
 * https://www.elevenforum.com/t/virtualization-based-security-cannot-be-disabled-in-23h2.25259
 * https://learn.microsoft.com/en-us/windows/security/identity-protection/credential-guard/configure?tabs=gpo#disable-credential-guard
 * https://www.elevenforum.com/t/enable-or-disable-hyper-v-in-windows-11.4177/
 * https://learn.microsoft.com/en-us/troubleshoot/windows-client/application-management/virtualization-apps-not-work-with-hyper-v
 * https://answers.microsoft.com/en-us/windowserver/forum/all/features-required-for-hyper-v-will-not-be/3be3baeb-2d32-4a2a-8b8f-93501e620338?page=2
 * https://learn.microsoft.com/en-us/answers/questions/1693500/hyper-v-processes-still-running-after-being-disabl
 * https://community.broadcom.com/vmware-cloud-foundation/discussion/disabling-hyper-v-hypervisor-on-windows-11-pro-host-to-get-vmware-17s-cpl0-vs-ulm-monitor-mode
 * https://pureinfotech.com/install-windows-subsystem-linux-2-windows-10/
 * https://learn.microsoft.com/en-us/answers/questions/1336873/how-to-solve-wsl-service-createinstance-createvm-h






BASH SCRIPT FOR STARTING AND STOPPING ORACLE APPLICATION (EBS) R12 12.2

On October 2, 2024October 2, 2024 By windows7bugsIn NOT a DBALeave a comment

I am working on a new script/set of scripts for single-instance database cloning
and came across the need to shut down and restart the application by calling a
second script. Here, I am sharing it with you today. Copy it to a
fresh .sh file, name it whatever you want, and enjoy! :) Do not forget to adjust
the passwords!

Copy to Clipboard

#!/bin/sh

: <<'END'
This script is ran as "root"
This script is meant for single instances.
This script expects both ORACLE, APPLMGR users bash profiles sourcing environment files.
This script was last tested against Oracle EBS R12 12.2.10/19c
Last modified on: 2nd October 2024
Author: Rajesh Thampi
License: Public
END
me=$(basename "$0")
ORACLE_SID=$(su - oracle -c 'echo "$ORACLE_SID"')
APPS_ORACLE_CONTEXT=$(su - applmgr -c 'echo "$CONTEXT_NAME"')
APPS_SCRIPTS_HOME=$(su - applmgr -c 'echo "$RUN_BASE/inst/apps/$CONTEXT_NAME/admin/scripts"')
DB_SCRIPTS_HOME=$(su - oracle -c 'echo "$ORACLE_HOME"')/appsutil/scripts/$APPS_ORACLE_CONTEXT

if [[ -z $1 ]]; then
    echo "No parameter was passed"
    exit 1
else
    if [[ "$1" == "start" ]]; then
        echo "All Oracle EBS R12 Services will be started now."
        su - oracle -c "sh $DB_SCRIPTS_HOME/adcdbctl.sh start;"
        if [ $? -ne 0 ]; then
            echo "Couldn't start the database services successfully. Aborting"
            exit 1
        else
            su - oracle -c "sh $DB_SCRIPTS_HOME/adcdblnctl.sh start $ORACLE_SID;"
        fi
        if [ $? -ne 0 ]; then
            echo "Couldn't start the listener services successfully. Aborting"
            exit 1
        else
            su - applmgr -c "cd $APPS_SCRIPTS_HOME;{ echo apps; echo apps; echo password123; } | adstrtal.sh;"
        fi
        if [ $? -ne 0 ]; then
            echo "Couldn't start the Application services successfully. Check log files for errors and try again"
        else
            echo "All EBS Services were successfully started."
        fi

    elif [[ "$1" == "stop" ]]; then
        echo "All Oracle EBS R12 Services will be stopped now."
        su - applmgr -c "cd $APPS_SCRIPTS_HOME;{ echo apps; echo apps; echo password123; } | adstpall.sh;"
        if [ $? -ne 0 ]; then
            echo "Couldn't stop the application services successfully. Aborting"
            exit 1
        else
            su - oracle -c "sh $DB_SCRIPTS_HOME/adcdblnctl.sh stop $ORACLE_SID;"
        fi
        if [ $? -ne 0 ]; then
            echo "Couldn't stop the Listener services successfully. Aborting"
            exit 1
        else
            su - oracle -c "sh $DB_SCRIPTS_HOME/adcdbctl.sh stop immediate;"
        fi
        if [ $? -ne 0 ]; then
            echo "Couldn't stop the Database services successfully. Check log files for errors and try again."
            exit 1
        else
            echo "All EBS Services were successfully stopped."
        fi
    else
        echo "Syntax: sh $me start/stop"

    fi
fi




That’s all folks. Have comments? let me know


CAN’T FIND FILE TBADDINSETUP64.MSI | ORACLE ANALYTICS PUBLISHER DESKTOP
INSTALLATION

On September 4, 2024September 4, 2024 By windows7bugsIn Oracle Analytics
PublisherLeave a comment

Installing Oracle software on Windows is always fun. One time it gets installed
& the other times it won’t. As simple as that. For our Oracle E-Business suite,
we adapted XML reporting from last many years & faced one or more issues with
almost every version of Microsoft Office. Sometimes the 64Bit version of the
Analytics Publisher will not work with the version of Office you have, forcing
you to install the 32Bit Office. The most perplexing is when the add-in gets
disabled once after the software crashes. I will get back to that area by the
end of this article.

Oracle has two support documents explaining how to resolve this installation
issue. Let’s see the beauty of their documentation.

Starting from the point number 3 everything is mere bullshit as uninstalling the
software deletes all executables from the paths mentioned. They are missing the
reference for the patch and points 5,6,7?? Anyway, discussing about Oracle
documents is like opening a can of worms.

So, what is the immediate solution if all of a sudden you find the “Publisher”
stopped appearing over the menu area? Well, the simplest solution is to
uninstall Office & re-install it. Then try to install Oracle publisher. As
simple as it is. This hack was tested with Office 2021 Professional, both 32 &
64Bit. You may follow the below sequence to ensure a successful installation.



 * Uninstall Microsoft Office & all other office products like OneNote
 * Uninstall Oracle Analytics Publisher Desktop
 * RESTART your computer!!! If you don’t, it’s certain that your next attempt
   will also fail.
 * Install Microsoft office first, then proceed with installing Oracle software.

Now, coming back to the Add-in getting disabled, especially from Microsoft Word.
I’ve came across some convincing Oracle community discussions & confirming that,
it’s due to the latest Microsoft Office updates, Add-in for Word gets disabled.
Oracle has a bug logged and they are investigating the issues. Rolling back to a
previous build of Office will resolve this issue. Please check the following
links for more details. Please note, you must use the exact version number for
the update rollback mentioned in the articles. For example, Office 2019, 2021 &
365 bear the version number “16” & you can get the exact version number from the
about product page. Please disable the automatic update for Office products
prior you attempt the below given instructions.

 * Oracle discussion
 * Microsoft instructions about how to downgrade Office

Based on the Oracle discussions, the safest version was 2406 & If your Office
version is 16.0.x.x, from an elevated command prompt execute the following
command:

Copy to Clipboard

C:\Program Files\Common Files\microsoft shared\ClickToRun\OfficeC2RClient.exe /update user updatetoversion=16.0.17726.20160

After downgrading your Office, open Microsoft Word as Administrator (right
click, run as administrator). Navigate to File->Options. Below image shows
“Oracle Analytics Publisher Desktop” as disabled.

Change the Manage option to “Disabled” and click the “Go” button.

(Please don’t mind the Add-in appearing twice in the below image, be sure I did
something wrong ;) )

Select the Add-in “tbaddin64” and try to enable, that closes the window. Close
Word and open again. This should bring back the “Publisher” back in the menu.








HACK FOR WORDPRESS WP-ADMIN REDIRECT LOOP

On July 24, 2024August 26, 2024 By windows7bugsIn wordpressLeave a comment

WordPress wp-admin redirect loop is that one dreaded issue, leaves you without
much help from anyone, trust me, literally no one gives you a proper answer,
including wordpress.com. To add more frustration, think of using .htaccess to
redirect because the site files are inside a sub-folder.

I had a strange situation with our corporate website, wp-admin redirecting to
the login page once after authentication is successfully done. I couldn’t load
this page successfully from any of the browsers, however I was able to login
using my mobile phone’s chrome browser!

So WordPress works this way, user tries to access the admin page using
yoursite.com/wp-admin that calls the wp-login.php page and after the successful
authentication, user is redirected to WordPress admin page, aka wp-admin!

For some reasons that I don’t understand, this gets into a loop! wp-admin loads
the login page, login page gets redirected to wp-admin page which wouldn’t load
because the authentication didn’t happen(?)…

After couple of hours trying to understand and modify the .htaccess file, I
noticed that whenever I tried to access mysite.com/wp-admin, the request was
getting redirected to mysite.com/wp-login.php & I decided to proceed with
mysite.com/wp-login.php instead of mysite.com/wp-admin

Well, that stopped the redirect loop. I was able to successfully authenticate
and access the admin page this way. Are you experiencing the same issue? give it
a try and share your experience with us.


WINDOWS 11 #24H2

On July 11, 2024July 12, 2024 By windows7bugsIn Windows, Windows 11Leave a
comment

I’ve been a Windows insider from the very beginning. However, my insider
experiments were always limited to a virtual machine as release previews
previously broke my fine tuned environments. This time I couldn’t get the
Windows 11 VM upgraded due to some unknown issues preventing VirtualBox getting
stuck at boot after the update installed. So, I decided to upgrade my
development machine. One of the funniest situations I came across was the
upgrade not being offered after setting up “insider program”

Let us check how to get the update step by step this time.

You must share diagnostic data with Microsoft for the insider program to work
properly.

Once set, you can go to Windows update and enroll for insider program. Setting
up the insider account could get stuck, just wait for few minutes and try again.
It should be fixed. As I was only interested about #24H2, I’ve opted for
unenrollment when the update is generally available for everyone.

Well, setting up and restarting and checking for new updates may not show you
the #24H2 update yet, you need to enable one more setting. You need to turn on
“Get the latest updates as soon as they’re available” option. Do another round
of check for updates and #24H2 will be definitely offered to you. Happy
upgrading.

If you are a seasoned Windows OS user, every update opens room for more bugs &
release previews WILL not be anything different. If you don’t have the luxury to
reset if required, wait for the final version of the update is available. That
could save you some frustrations.


POSTS NAVIGATION

Older posts
Search for: Search


WE ARE ON TWITTER



November 2024 S M T W T F S  12 3456789 10111213141516 17181920212223
24252627282930

« Oct    


TOP POSTS

 * Install Oracle forms & reports 12.2.1.4 with Weblogic 12c 12.2.1.4
 * How to disable Windows Virtualization/Device Based Security #VBS
 * ASP.NET Core | HttpContext | Get current windows username
 * Windows 11 22H2 "Windows 11 installation has failed"
 * Oracle E-Business Suite R12 12.1.3, build a Vision Instance using Oracle
   Virtual Box
 * Windows | Upgrade Oracle Database 12c to 19c
 * Oracle Applications R12 | Java applet font scaling issues
 * Install Oracle Developer 10G on Windows 11
 * ORA-00800: soft external error, arguments: [Set Priority Failed], [VKTM],
   [Check traces and OS configuration]
 * Install Oracle 21c database on Windows


RECENT POSTS

 * Install Oracle APEX & ORDS 24.x.x on Oracle Linux
 * Upgrade & Patch Oracle APEX 23.x.x
 * Oracle EBS R12 | Monitoring tablespace | Sending email using utl_smtp package
 * Windows backup using bitlocker and powershell secrets
 * Oracle EBS R12 12.2 database cloning


WORD PRESS

 * Log in
 * Entries feed
 * Comments feed
 * WordPress.org


BLOG STATS

 * 1,415,558 hits


RECENT COMMENTS

windows7bugs on How to disable Windows Virtual…windows7bugs on Install Oracle
APEX & ORDS…How to disable Virtu… on How to disable Windows Virtual…Rachmat
Febrianto on How to disable Windows Virtual…Rachmat Febrianto on How to disable
Windows Virtual…


EMAIL SUBSCRIPTION

Enter your email address to subscribe to this blog and receive notifications of
new posts by email.

Email Address

Sign me up!

Join 118 other subscribers


CATEGORIES

 * .net programming
 * Active Directory
 * Android
 * APEX
 * Articles
 * ASP.NET Core
 * Educational
 * Gadgets
 * Hacks for Practical life
 * Hardware
 * Linux
 * Linux
 * Networking
 * NOT a DBA
 * Notes for personal references
 * Offtopic
 * Oracle Analytics Publisher
 * Oracle Applications (R12.0.4)
 * Oracle Applications(R12)
 * Oracle database 11g
 * Oracle database 12c
 * Oracle Database 19c
 * Oracle Enterprise Linux
 * Oracle Forms
 * Oracle Inventory (R12)
 * Oracle PL/SQL
 * Oracle related
 * Oracle SQL Developer
 * Oracle VirtualBox
 * PowerShell
 * Samsung
 * Security
 * T-SQL
 * Uncategorized
 * Visual Studio(VB.Net)
 * VMWare
 * Weblogic
 * Windows
 * Windows 10
 * Windows 11
 * Windows 2016
 * Windows 7
 * Windows 8
 * Windows 8.1
 * Windows Phone
 * Windows Server 2016
 * wordpress

Powered by WordPress.com.

Duh! Microsoft did it again
Proudly powered by WordPress Theme: Lovecraft.
 

Loading Comments...

 

Write a Comment...
Email (Required) Name (Required) Website