Oracle DBA by Example. Code samples, Scripts, Reference.: January 2019

Thursday, January 24, 2019

Oracle opatch

=======================
URLs

=======================

Search

Search by Patch

https://updates.oracle.com/Orion/PatchDetails/switch_to_simple

Download

Download a Patch

https://updates.oracle.com/download/20761024.html

Upgrade Opatch Utility

Download a Patch

https://updates.oracle.com/download/6880880.html

How to search:

In Oracle Support:

Patches & Updates Tab ->

Patch Search ->

Product or Family (Advanced) ->

Select Product -> Oracle Global Lifecycle Management OPatch

Select Release ->

Select Platform -> Linux x86-64

After unzip of the OPatch zip, backup $ORACLE_HOME/OPatch/ folder. And extract from the OPatch zip into $ORACLE_HOME/OPatch/

=======================
Steps to apply Oracle patch
=======================

Per Oracle documentation

1. For non-recommended patches, you must have the exact symptoms
described in the service request (SR).

2. Confirm the version of Perl installed and configured for the ORACLE_HOME.
- source the ORACLE_HOME environment
- execute "perl -v"
- verify that the returned version of Perl is 5.00503 or higher

Verify the OUI Inventory.
$ORACLE_HOME/OPatch/opatch lsinventory

3. Create a location for storing the unzipped patch.
This location will be referred to later in the document as <PATCH_TOP>.

4. Unzip the patch zip file into the <PATCH_TOP>.

unzip -d <PATCH_TOP> p7626014_111070_Linux-x86-64.zip

unzip -d 33494256 19_12_p33494256_190000_Linux-x86-64.zip

cd 33494256/33494256

5. Shut down services running from the ORACLE_HOME.

Before applying this patch, do a clean shut down of all services
running from the ORACLE_HOME.
Shut down all services that are running from this ORACLE_HOME.
Confirm the return status from each shutdown command to verify
the shutdown is successful and there are no errors.

Meaning, shut down Oracle listener and Oracle service.

lsnrctl stop;

shutdown immediate;

Once done, you can apply the patch:
cd to the patch folder, created during unzip step (to the folder containing README.txt)

apply patch by running:
$ORACLE_HOME/OPatch/opatch lsinventory
$ORACLE_HOME/OPatch/opatch apply
$ORACLE_HOME/OPatch/opatch lsinventory

Start Oracle server and listener.

=================================
Patch Installation Steps by example:
=================================

1. Stop Oracle and Listener
lsnrctl stop
ALTER SYSTEM CHECKPOINT;
SHUTDOWN ABORT;

2. Unzip patch to a directory
<PATCH_TOP>/7626014

3. Set your current directory to the directory where the patch is located.

cd <PATCH_TOP>/7626014/

(you should be in same directory with file README.txt)

4. Apply the patch.

Use the following command to apply the patch to the ORACLE_HOME:

$ORACLE_HOME/OPatch/opatch apply

5. Check patch was applied

$ORACLE_HOME/OPatch/opatch lsinventory

Post Patch Installation Steps:
-------------------------

Startup Oracle and Listener

STARTUP

lsnrctl start

=================================
Patch Rollback Steps by example:
=================================

$ORACLE_HOME/OPatch/opatch lsinventory

$ORACLE_HOME/OPatch/opatch rollback -id 7626014

$ORACLE_HOME/OPatch/opatch lsinventory

Patch 7626014 should not be in the list

=======================
Opatch Utility versions
=======================

Opatch Utility has version as well.

Opatch Utility version, should be compatible with the applied patch, as newer patched require newer version on Opatch utility.

In case Opatch utility is too old version, following error would come during patch installation:

oracle@my_server:~/OPATCH_p20761024/20761024>% $ORACLE_HOME/OPatch/opatch apply

Invoking OPatch 11.1.0.6.8

Oracle Interim Patch Installer version 11.1.0.6.8

Oracle Home : /software/oracle/111

Central Inventory : /software/oracle/installers/111

from : /etc/oraInst.loc

OPatch version : 11.1.0.6.8

OUI version : 11.1.0.7.0

OUI location : /software/oracle/111/oui

Log file location : /software/oracle/111/cfgtoollogs/opatch/opatch2019-04-23_23-20-01PM.log

Patch history file: /software/oracle/111/cfgtoollogs/opatch/opatch_history.txt

ApplySession applying interim patch '20761024' to OH '/software/oracle/111'

ApplySession failed: ApplySession failed to prepare the system.

Patch 20761024 requires OPatch version 11.1.0.8.2.

The OPatch version being used (11.1.0.6.8) doesn't meet the minimum version required by the patch(es). Please download latest OPatch from My Oracle Support.

System intact, OPatch will not attempt to restore the system

OPatch failed with error code 73

How to check current Opatch version:

$ORACLE_HOME/OPatch opatch version

How to check current Opatch version:

Inside readme.txt of a patch, refer to Prerequisites section.

For example:

2 Prerequisites

2.1 OPatch Utility

You must use the OPatch utility version 11.1.0.8.2 or later to apply this patch.
Oracle recommends that you use the latest released OPatch version for 11.1, which is available for download from My Oracle Support patch 6880880 by selecting the 11.1.0.0.0 release.

=================================
Upgrade Opatch Utility
=================================
Upgrade Opatch Utility is done via implementing patch Patch 6880880
Patch 6880880 is a general Patch for the OPatch utility
How to apply:

From readme.txt inside Patch 6880880

How to install the utility:
---------------------------

To install this patch, Please extract the file "zipped file" using unzip or winzip,
depending upon the platform.
You should extract the zip file directly under the
ORACLE_HOME.
Please follow the following steps for extracting the zip file of OPatch.

(1) Please take a backup of ORACLE_HOME/OPatch into a dedicated backup
location.
(2) Please make sure no directory ORACLE_HOME/OPatch exist.
(3) Please unzip the OPatch downloaded zip into ORACLE_HOME directory.

To check the version of the opatch utility installed in the above step,

go to the OPatch directory and run "opatch version".

For example:
cd %ORACLE_HOME

rm -rf OPatch
unzip 12_2_1_30_p6880880_190000_Linux-x86-64.zip

check:
$ORACLE_HOME/OPatch/opatch version

Tuesday, January 15, 2019

Send file from Linux bash using scp.

==============================
General
==============================
Send file from Linux bash using scp

==============================
Description
==============================
A process is creating a big Report file(s) and additional file with the report(s) file(s) name(s).
A crontab task reads the "Parent" file
Process the file line by line
For each line scp the Reports listed in "Parent" file to another server using scp.

==============================
code
==============================
bash code

/usr/iu/workarea/scripts/MEGA_EVENT/move_report_to_mng_server.sh

#!/bin/bash

#scp -r /starhome/iu/workarea/ora_exp/Export_Outbound_Roamers_* my_server:/starhome/data_export/ita-vod/megaevent/ && rm /starhome/iu/workarea/ora_exp/Export_Outbound_Roamers_*

WORK_DIR=/starhome/iu/workarea/scripts/MEGA_EVENT
FILES_DIR=/starhome/iu/workarea/ora_exp
LOG_FILE=${WORK_DIR}/SCP_VRS_REPORTS.log

TARGET_SERVER=my_server
TARGET_USER=iu
TARGET_PATH=/starhome/data_export/ita-vod/megaevent
SCP_FILE_NAME=scp_to_${TARGET_SERVER}.sh
PARENT_FILE=${FILES_DIR}/megaevent_created.txt

#main()
RUN_DATE=`date "+%Y%m%d"_"%H%M%S" `
touch ${LOG_FILE}
echo "===============================================" >> $LOG_FILE
echo "Starting at $RUN_DATE" >> $LOG_FILE

if [[ -f ${PARENT_FILE} ]]; then
REPORTS_FILES=`less ${PARENT_FILE} | wc -l`
else
echo "No Input Files to Process. ${PARENT_FILE} does not exists " >> ${LOG_FILE}
echo "Finished at $RUN_DATE" >> $LOG_FILE
echo "===============================================" >> $LOG_FILE
exit 0
fi

if [[ $REPORTS_FILES -eq 0 ]]; then
echo "No Input Files to Process. ${PARENT_FILE} is empty " >> ${LOG_FILE}
echo "Finished at $RUN_DATE" >> $LOG_FILE
echo "===============================================" >> $LOG_FILE
exit 0
else
echo "Got $REPORTS_FILES Input Files to Process. " >> ${LOG_FILE}
fi

REPORTS_FILE_NAME=`less ${PARENT_FILE}`

while IFS='' read -r line || [[ -n "$line" ]]; do
REPORTS_FILE_NAME=$line
echo sending ${REPORTS_FILE_NAME} >> $LOG_FILE
echo "scp ${FILES_DIR}/${REPORTS_FILE_NAME} ${TARGET_USER}@${TARGET_SERVER}:${TARGET_PATH}/." > ${WORK_DIR}/${SCP_FILE_NAME}
chmod +x ${WORK_DIR}/${SCP_FILE_NAME}
echo "Running ${WORK_DIR}/${SCP_FILE_NAME} " >> $LOG_FILE
${WORK_DIR}/${SCP_FILE_NAME}
rm -f ${FILES_DIR}/${REPORTS_FILE_NAME}
done < ${PARENT_FILE}

mv -f ${PARENT_FILE} ${WORK_DIR}/megaevent_sent_${RUN_DATE}

RUN_DATE=`date "+%Y%m%d"_"%H%M%S" `
echo "Finished at $RUN_DATE" >> $LOG_FILE
echo "===============================================" >> $LOG_FILE
exit 0

PL/SQL code

PROCEDURE Export_Subscribers_Report(pVlrs IN varchar2, pCountries IN varchar2) IS

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

-- Propose : Procedure to export data to csv file.

-- Parameters : pVlrIds - List of VLRs.

-- pCountryIds - List of Coutnries.

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

CURSOR ResultCrs (pVlrIds IN VARCHAR2, pCountryIds IN VARCHAR2) IS

SELECT s.msisdn || ','|| s.imsi || ','|| s.current_vlr ||','|| c.country_name AS TEXT

FROM SGA_W_PSMS_SUBSCRIBER s, GSM_COUNTRIES c

WHERE ( s.current_vlr IN

(SELECT regexp_substr(txt, '[^,]+', 1, level)

FROM (SELECT pVlrIds AS txt FROM DUAL)

CONNECT BY LEVEL <= LENGTH(REGEXP_REPLACE(txt, '[^,]')) + 1)

s.current_country IN

(SELECT regexp_substr(txt, '[^,]+', 1, level)

FROM (SELECT pCountryIds AS txt FROM DUAL)

CONNECT BY LEVEL <= LENGTH(REGEXP_REPLACE(txt, '[^,]')) + 1))

AND s.is_subscriber = 1

AND s.is_roaming = 1

and s.current_country = c.country_id ;

CURSOR AllResultCrs IS

SELECT s.msisdn || ','|| s.imsi ||','|| s.current_vlr ||','|| c.country_name AS TEXT

FROM SGA_W_PSMS_SUBSCRIBER s, GSM_COUNTRIES c

WHERE s.is_subscriber = 1

AND s.is_roaming = 1

and s.current_country = c.country_id ;

NewFile UTL_FILE.FILE_TYPE;

vPath VARCHAR2(30);

vFileName VARCHAR2(200) := '';

vStartDateString VARCHAR2(19);

vSqlerrm VARCHAR2(2000);

ParentFile UTL_FILE.FILE_TYPE;

v_parent_file VARCHAR2(100);

BEGIN

vStartDateString := TO_CHAR(SYSDATE,'YYYYMMDD_HH24MISS');

vPath := 'IG_EXP_DIR';

vFileName := 'Export_Outbound_Roamers_' || vStartDateString|| '.csv';

v_parent_file := 'megaevent_created.txt';

BEGIN

NewFile := UTL_FILE.FOPEN(vPath, vFileName, 'W', 10000);

EXCEPTION

WHEN OTHERS THEN

UTL_FILE.FCLOSE(NewFile);

END;

IF pVlrs IS NULL AND pCountries IS NULL THEN

FOR ResultRec IN AllResultCrs LOOP

UTL_FILE.PUT_LINE(NewFile, ResultRec.Text);

END LOOP;

ELSE

FOR ResultRec IN ResultCrs(pVlrs,pCountries) LOOP

UTL_FILE.PUT_LINE(NewFile, ResultRec.Text);

END LOOP;

END IF;

UTL_FILE.FCLOSE(NewFile);

BEGIN

ParentFile := UTL_FILE.FOPEN(vPath, v_parent_file, 'A', 10000);

UTL_FILE.PUT_LINE(ParentFile, vFileName);

UTL_FILE.FCLOSE(ParentFile);

EXCEPTION

WHEN OTHERS THEN

sga_pkg.write_sga_w_log('PSMS_SUBSCRIBER_PKG.Export_Subscribers_Report', 'Unexpected Error. Failed to close Parent File: ' ||v_parent_file);

sga_pkg.write_sga_w_log('PSMS_SUBSCRIBER_PKG.Export_Subscribers_Report', 'Unexpected Error: ' || sqlerrm);

END;

EXCEPTION

WHEN OTHERS THEN

sga_pkg.write_sga_w_log('PSMS_SUBSCRIBER_PKG.Export_Subscribers_Report', 'Unexpected Error: ' || sqlerrm);

UTL_FILE.FCLOSE(NewFile);

END Export_Subscribers_Report;

Monday, January 14, 2019

Check Connection from bash

===========================
General
===========================

Check Database Connection details, provided in parameter file (set_vipuser.sql)
Check connection is done in bash script, returning invalid status if provided credentials were wrong.

in main bash

#--------------------------------------
#Validate parameters in set_vipuser.sql
./chk_connection.sh
status=$?
if [[ $status != 0 ]];then
echo
echo Check Connection Settings Failed!
echo Please Correct Parameter Values in set_vipuser.sql
echo
fi
#--------------------------------------

in chk_connection.sh

#!/bin/bash

#---------------------------------

#functions

#---------------------------------

report_error(){

db_user=$1

echo ==========================================

echo Error!!!

echo Connection Error for User $db_user

echo ERROR: ORA-01017: invalid username/password. logon denied

echo Exiting script!

echo ==========================================

exit 1

}

report_success(){

db_user=$1

echo OK! User $db_user passed Connection Check

}

#---------------------------------

#main

#---------------------------------

echo ==========================================

echo Check Connections Start

echo ==========================================

export OUTPUT_FILE=/tmp/chk_connections.log

VIPUSER=`grep vipuser ../set_vipuser.sql |awk -F= '{print $2}'`

VIPPASS=`grep vippass ../set_vipuser.sql |awk -F= '{print $2}'`

ADMINUSER=`grep adminUser ../set_vipuser.sql |awk -F= '{print $2}'`

ADMINPASS=`grep adminPass ../set_vipuser.sql |awk -F= '{print $2}'`

CONNECTSTR=`grep "connectstr=" ../set_vipuser.sql |awk -F= '{print $2}'`

CONNECTSTRRMT=`grep "connectstrrmt=" ../set_vipuser.sql |awk -F= '{print $2}'`

rm -f $OUTPUT_FILE

touch $OUTPUT_FILE

echo Check Connection for: ${VIPUSER}/${VIPPASS}@${CONNECTSTR} >> $OUTPUT_FILE

sqlplus -s ${VIPUSER}/${VIPPASS}@${CONNECTSTR} >> $OUTPUT_FILE << EOD

set serveroutput on

set heading off linesize 130 pagesize 1000 feedback off

SELECT 'Connection is OK for '||USER FROM DUAL;

EOD

echo Check Connection for: ${VIPUSER}/${VIPPASS}@${CONNECTSTRRMT} >> $OUTPUT_FILE

sqlplus -s ${VIPUSER}/${VIPPASS}@${CONNECTSTRRMT} >> $OUTPUT_FILE << EOD

set serveroutput on

set heading off linesize 130 pagesize 1000 feedback off

SELECT 'Connection is OK for '||USER FROM DUAL;

EOD

echo Check Connection for: ${ADMINUSER}/${ADMINPASS}@${CONNECTSTR} >> $OUTPUT_FILE

sqlplus -s ${ADMINUSER}/${ADMINPASS}@${CONNECTSTR} >> $OUTPUT_FILE << EOD

set serveroutput on

set heading off linesize 130 pagesize 1000 feedback off

SELECT 'Connection is OK for '||USER FROM DUAL;

EOD

#----------------------------

# Analyze Connection Results

#----------------------------

connection_ok=`grep "Connection is OK for" $OUTPUT_FILE | grep -i ${VIPUSER} | wc -l`

if [[ $connection_ok != 2 ]];then

report_error ${VIPUSER}

else

report_success ${VIPUSER}

connection_ok=`grep "Connection is OK for" $OUTPUT_FILE | grep -i ${ADMINUSER} | wc -l`

if [[ $connection_ok != 1 ]];then

report_error ${ADMINUSER}

else

report_success ${ADMINUSER}

echo ==========================================

echo Check Connections Finish

echo ==========================================

exit 0

Pages

Thursday, January 24, 2019

Oracle opatch

Tuesday, January 15, 2019

Send file from Linux bash using scp.

Monday, January 14, 2019

Check Connection from bash