Month: October 2024

Oracle EBS R12 | Monitoring tablespace | Sending email using utl_smtp package

On 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”. 

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 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.

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.

#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

Oracle EBS R12 12.2 database cloning

On 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.

#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”

#!/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.

#!/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 ;) 

#!/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.

#!/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.

#!/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.

#!/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.

#!/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.

#!/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.

#!/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 By windows7bugsIn Windows, Windows 1142 Comments

This post was last updated on 24th March 2025. One of the visitors commented that VBS was disabled by turning off Tamper Protection. I received a confirmation from other visitor, using Windows 11 Home Edition.

This post was last updated on 5th February 2025 to confirm that the below hacks are not applicable for Windows Home Editions. If we come across any, will duly share them. Cheers!

This post was last updated on 27th January 2025 to include additional information with another laptop HP Probook 450 G10 running Windows 11 23H2 and VBS was already enabled when we received this device at work. I observed the below while disabling VBS on the same.

  • No need to disable Kernel DMA Support
  • Secure boot disabled

To disable VBS, this time I approached the below sequence

  • Disabled Core Isolation->Memory Integrity->Reboot
  • Ran Device Guard Readiness script with switch “Disabled” (Explained below)->Reboot. Accepted the prompts to disable both Credential Guard & VBS opt-outs and VBS was disabled completely.

This post was last updated on 15th January 2025 to include my experiments with a new Dell G16 7630 gaming laptop that I changed my six years old laptop with. For the same, I have disabled 2 things on BIOS prior continuing with other attempts from the OS.

  • Kernel DMA Support
  • Secure Boot

Once again, the methods that I listed below should only be referred as workaround solutions and you must let Microsoft handle such stuffs the best ways it suits to offer maximum security and stability for your devices.

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.

Before trying to execute the PowerShell script, make sure the execution policy has been set as “unrestricted”. Without, the script will execute and show some information, that wouldn’t really tell you what went wrong.

Now open an elevated command prompt/PowerShell, switch to the path where you have extracted the file that you downloaded earlier. If you are using PowerShell, switch to the path first then type the first couple of letters, for example “DG” and tap the tab key, that will fetch the full name of the script. Ignore the error messages.

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. The first prompt asks for Credential Guard opt-out confirmation, that you should accept by pressing “F3” key.

The same will be confirmed in the next screen.

Now “Virtualization Based Security” opt-out will be presented. Once you again press “F3” to proceed.

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.

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.

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

Bash script for starting and stopping Oracle Application (EBS) R12 12.2

On 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!

#!/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