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

Backup $ORACLE_HOME/OPatch/ folder.

Unzip of the OPatch zip

Extract from the OPatch zip into $ORACLE_HOME/OPatch/

=======================
Steps to verify Oracle patch
=======================

To verify that a patch can be applied to a specific Oracle home, use the —report argument to the opatch apply command.

This command displays the actions that will be taken by the patch, but does not actually apply the patch. 

As a result, it allows you to verify that the prerequisites for the patch have been met.

unzip -d 7626014 p7626014_111070_Linux-x86-64.zip

cd 7626014/7626014
$ORACLE_HOME/OPatch/opatch apply -report

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

cd <PATCH_TOP>
For Example:

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 -report
$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

Copyright (c) 2009, Oracle Corporation.  All rights reserved.

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

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)

                OR

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;

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}

fi

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}

fi

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

echo Check Connections Finish

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

exit 0

Large Incident files with error: kksfbc-new-child-thresh-exceeded

===========================
General
===========================
Large incident files with error kksfbc-new-child-thresh-exceeded are generated under incident/ folder

=========================
Oracle Documentation

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

This is a is known bug:
ORA-600 [kksfbc-new-child-thresh-exceeded] Error In 11.1 (Doc ID 1200693.1)

CAUSE
This is most likely caused by unpublished Bug 7626014 fixed in 11.2 and PSU 11.1.0.7.5. 
The error occurs against a heavily used cursor which has multiple children with high concurrency.  
This problem can also cause unnecessary additional child cursors to be created.

SOLUTION
To fix this issue apply one of the following solutions:

Either Upgrade to 11.2.0.1, or

Install Patch 7626014 on top of 11.1.0.7.

Install the 11.1.0.7 PSU #5 or higher like 11.1.0.7 PSU #6 which contain the fix for this defect.

On Windows, install our 11.1.0.7.BP 22 or above - preferably the most recent one.

If this fix does not exist on top of the latest available patchset for your platform, please log a new Service Request and provide evidences that you are encountering this issue and request for an interim patch for your platform.

Note:

This fix alters the method used if a session cannot find an existing child cursor that can be shared. 

With this fix, builds of a new child cursor are serialized.

===========================
Example
===========================

*** 2018-12-23 17:44:55.692

*** SESSION ID:(440.41588) 2018-12-23 17:44:55.692

*** CLIENT ID:() 2018-12-23 17:44:55.692

*** SERVICE NAME:(SYS$USERS) 2018-12-23 17:44:55.692

*** MODULE NAME:(JDBC Thin Client) 2018-12-23 17:44:55.692

*** ACTION NAME:() 2018-12-23 17:44:55.692

Dump continued from file: /software/oracle/diag/rdbms/igt/igt/trace/igt_ora_17188.trc

ORA-00600: internal error code, arguments: [kksfbc-new-child-thresh-exceeded], [], [], [], [], [], [], [], [], [], [], []

========= Dump for incident 212913 (ORA 600 [kksfbc-new-child-thresh-exceeded]) ========

*** 2018-12-23 17:44:55.692

----- Current SQL Statement for this session (sql_id=6ug9rkhbfzgsa) -----

MERGE into SGA_W_PSMS_SUBSCRIBER o using (select :1  MSISDN from dual) o1 on (o.MSISDN = o1.MSISDN) when matched then update set o.IMSI= :2 ,o.CURRENT_COUNTRY= :3 ,o.CURRENT_NETWORK_ID = :4  , o.CURRENT_VLR = :5  ,o.CURRENT_MSC = :6  ,o.TS_FIRST_REGISTRATION = :7  ,o.TS_E_LAST_LU = :8  ,o.IS_ROAMING = :9 , o.CURRENT_SGSN = :10   , o.PREVIOUS_VLR=:11 , o.PREVIOUS_SGSN=:12 , o.TS_E_LAST_CANCEL=:13 ,o.TS_VISIT_START=:14 , o.TS_VISIT_END=:15 , o.TS_LAST_MODIFIED=:16 ,o.MAP_CAMEL_SERVICE_KEY=:17 , o.SCENARIO_HISTORY =:18 , o.RE_ACTIVATION_HISTORY =:19 , o.CAMPAIGN_HISTORY =:20  , o.PREVIOUS_VISIT_END_TIME=:21 ,o.CURRENT_SCENARIO_START_TIME=:22 ,o.SCENARIO_ID=:23 ,o.SPARX_BW_LIST_CHECK_RESULT=:24 ,o.VISIT_ID=:25 , o.LOCATION_GROUP_HISTORY=:26 , o.MVNO_HISTORY=:27 , o.IS_SUBSCRIBER=:28 , o.IS_LTE_USER =:29 ,  o.CURRENT_LTE_MCC_MNC=:30 , o.LTE_CANCELLATION_TYPE=:31 , o.LTE_SUBSCRIBER_STATUS=:32 , o.TS_E_LAST_LU_LTE=:33 , o.TS_E_LAST_CANCEL_LTE=:34 , o.CURRENT_MME=:35 , o.CURRENT_TECHNOLOigt_ora_17188_i212913.trc 

incdir_212889

Dump continued from file: /software/oracle/diag/rdbms/igt/igt/trace/igt_ora_17164.trc

ORA-00600: internal error code, arguments: [kksfbc-new-child-thresh-exceeded], [], [], [], [], [], [], [], [], [], [], []

========= Dump for incident 212889 (ORA 600 [kksfbc-new-child-thresh-exceeded]) ========

*** 2018-12-23 15:54:12.188

----- Current SQL Statement for this session (sql_id=6ug9rkhbfzgsa) -----

MERGE into SGA_W_PSMS_SUBSCRIBER o using (select :1  MSISDN from dual) o1 on (o.MSISDN = o1.MSISDN) when matched then update set o.IMSI= :2 ,o.CURRENT_COUNTRY= :3 ,o.CURRENT_NETWORK_ID = :4  , o.CURRENT_VLR = :5  ,o.CURRENT_MSC = :6  ,o.TS_FIRST_REGISTRATION = :7  ,o.TS_E_LAST_LU = :8  ,o.IS_ROAMING = :9 , o.CURRENT_SGSN = :10   , o.PREVIOUS_VLR=:11 , o.PREVIOUS_SGSN=:12 , o.TS_E_LAST_CANCEL=:13 ,o.TS_VISIT_START=:14 , o.TS_VISIT_END=:15 , o.TS_LAST_MODIFIED=:16 ,o.MAP_CAMEL_SERVICE_KEY=:17 , o.SCENARIO_HISTORY =:18 , o.RE_ACTIVATION_HISTORY =:19 , o.CAMPAIGN_HISTORY =:20  , o.PREVIOUS_VISIT_END_TIME=:21 ,o.CURRENT_SCENARIO_START_TIME=:22 ,o.SCENARIO_ID=:23 ,o.SPARX_BW_LIST_CHECK_RESULT=:24 ,o.VISIT_ID=:25 , o.LOCATION_GROUP_HISTORY=:26 , o.MVNO_HISTORY=:27 , o.IS_SUBSCRIBER=:28 , o.IS_LTE_USER =:29 ,  o.CURRENT_LTE_MCC_MNC=:30 , o.LTE_CANCELLATION_TYPE=:31 , o.LTE_SUBSCRIBER_STATUS=:32 , o.TS_E_LAST_LU_LTE=:33 , o.TS_E_LAST_CANCEL_LTE=:34 , o.CURRENT_MME=:35 , o.CURRENT_TECHNOLOigt_ora_17164_i212889.trc 

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

Solution
===========================
As a workaround, I have tried to Flush the Shared pool for the sql_id entries in question

Per some tech-notes, it is possible to flush specific sql_id.

But when running this code, I could not see that the child cursors was flushed.

As they still are present in table V$SQL_BIND_CAPTURE.

As a workaround, I have Flushed Shared Pool, and this has resolved the issue.

SELECT sql_id, count(*) AS child_records
  FROM V$SQL_BIND_CAPTURE
 GROUP BY sql_id
HAVING  count(*) > 400
ORDER BY count(*) DESC;

SQL_ID        CHILD_RECORDS
------------- -------------
6ug9rkhbfzgsa         52584
akh1m5g2waych         13254
7dh0ddg65qpbv          3528
b8ya3z8kq3c6r          2086
adbf1h2dx6m81          1800
7ng34ruy5awxq           880
7y6yta0cajvr5           735
5yv0nj5hwfz5d           725
fwb25vcr3c2wk           688
dwypdxsjg4juq           624
2q93zsrvbdw48           478
6aq34nj2zb2n7           477

SELECT ADDRESS, HASH_VALUE from V$SQLAREA 
WHERE SQL_Id IN ('6ug9rkhbfzgsa','akh1m5g2waych','7dh0ddg65qpbv','b8ya3z8kq3c6r','adbf1h2dx6m81');

ADDRESS          HASH_VALUE
---------------- ----------
0000000257DEEAE0 2614316289
0000000253EC17B0 3428537723
0000000253AE12A8 3318053264
0000000253D3EB30  627159255
0000000257DAF2C0  384810762

BEGIN
  DBMS_SHARED_POOL.PURGE ('0000000257DEEAE0,2614316289','C');
  DBMS_SHARED_POOL.PURGE ('0000000253EC17B0,3428537723','C');
  DBMS_SHARED_POOL.PURGE ('0000000253AE12A8,3318053264','C');
  DBMS_SHARED_POOL.PURGE ('0000000253D3EB30,627159255','C');
  DBMS_SHARED_POOL.PURGE ('0000000257DAF2C0,384810762','C');
END;
/  

SELECT sql_id, count(*) AS child_records
  FROM V$SQL_BIND_CAPTURE
 GROUP BY sql_id
HAVING  count(*) > 400
ORDER BY count(*) DESC;

SQL_ID        CHILD_RECORDS
------------- -------------
6ug9rkhbfzgsa         52584
akh1m5g2waych         13254
7dh0ddg65qpbv          3528
b8ya3z8kq3c6r          2086
adbf1h2dx6m81          1800
7ng34ruy5awxq           880
7y6yta0cajvr5           735
5yv0nj5hwfz5d           725
fwb25vcr3c2wk           688
dwypdxsjg4juq           624
2q93zsrvbdw48           478
6aq34nj2zb2n7           477

Since this did not wirk, I have Flushed the Shared Pool.
Flush Shared Pool has resolved the issue.

ALTER SYSTEM FLUSH SHARED_POOL;

Oracle SCN - System Change Number

==============================
Oracle SCN - System Change Number
==============================
SCN is an internal time stamp used by Oracle Database. 

Oracle Database uses SCNs to mark the SCN before which all changes are known to be on disk so that recovery avoids applying unnecessary redo. 

The database also uses SCNs to mark the point at which no redo exists for a set of data so that recovery can stop.

SCNs occur in a monotonically increasing sequence. 

Oracle Database can use an SCN like a clock because an observed SCN indicates a logical point in time and repeated observations return equal or greater values. 
If one event has a lower SCN than another event, then it occurred at an earlier time with respect to the database.
Several events may share the same SCN, which means that they occurred at the same time with respect to the database.

Every transaction has an SCN. 

For example, if a transaction updates a row, then the database records the SCN at which this update occurred. 

Other modifications in this transaction have the same SCN. 

When a transaction commits, the database records an SCN for this commit.

Oracle Database increments SCNs in the system global area (SGA). 

When a transaction modifies data, the database writes a new SCN to the undo data segment assigned to the transaction. 

The log writer process then writes the commit record of the transaction immediately to the online redo log. 

The commit record has the unique SCN of the transaction. 

Oracle Database also uses SCNs as part of its instance recovery and media recovery mechanisms.

==============================
At commit time
==============================
A system change number (SCN) is generated for the COMMIT.

The internal transaction table for the associated undo tablespace records that the transaction has committed. 
The corresponding unique SCN of the transaction is assigned and recorded in the transaction table. 

The log writer (LGWR) process writes remaining redo log entries in the redo log buffers to the online redo log and writes the transaction SCN to the online redo log. 

This atomic event constitutes the commit of the transaction.

==============================
How to find current SCN
==============================
Option 1.
SELECT CURRENT_SCN FROM V$DATABASE;
294565574

Option 2.

SELECT TIMESTAMP_TO_SCN(SYSDATE) FROM DUAL;
294565581

Option 3.
SELECT DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER AS SCN FROM DUAL;
ORA-00904: : invalid identifier


oracle@my_server:~>% sqlplus / as sysdba

SQL> GRANT EXECUTE ON DBMS_FLASHBACK to RI_CENTRAL;

Grant succeeded.

sqlplus RI_CENTRAL/RI_CENTRAL@ora_inst
SELECT DBMS_FLASHBACK.get_system_change_number AS SCN FROM DUAL;

       SCN
----------
 294565682

==============================
SCN to Time
==============================
SQL> SELECT SCN_TO_TIMESTAMP(294565682) AS SCN_TIME FROM DUAL;

SCN_TIME
---------------------------------
09-DEC-18 04.34.28.000000000 PM

SQL> SELECT TO_CHAR(SCN_TO_TIMESTAMP(294565682),'YYYYMMDD hh24:mi:ss') AS SCN_TIME FROM DUAL;

SCN_TIME
------------------------------

20181209 16:34:28

==============================
Script to get current SCN

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


CURRENT_SCN=`echo -e "set head off term off echo off feed off ver off space 1 linesize 1000\ncol  DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER() format 999999999999999999999999999999999999999\nselect DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER() from dual;" | sqlplus -S $MY_USER/$MY_PASS@$MY_CONNECTSTR`


echo CURRENT_SCN is: $CURRENT_SCN  

Move Datafile from Location to a new Location

==================================
General
==================================
Example of moving Datafile from Location A to Location B

==================================
Example
==================================
Steps
Check Database Archive Mode
SQL> SELECT log_mode FROM V$DATABASE;

LOG_MODE
---------------------------
ARCHIVELOG

==================================
Database is in ARCHIVELOG mode
==================================
- Take the datafile offline.
ALTER DATABASE DATAFILE '/old/datafile/location/datafile_01.dbf' OFFLINE;

SQL>Database Altered

- Copy or move the datafile to its new location.
dd if=/old/location/datafile_01.dbf of=new/location/datafile_01.dbf bs='4096
or
cp -p /old/location/datafile_01.dbf /new/location/datafile_01.dbf

- Point Database to the new datafile location.
ALTER DATABASE RENAME FILE '/old/location/datafile_01.dbf' TO 'new/location/datafile_01.dbf';

SQL>Database altered.

- Recover new datafile.

RECOVER DATAFILE '/new/location/datafile_01.dbf';

SQL>Media recovery complete.

- Bring Datafile Online.

ALTER DATABASE DATAFILE 'new/location/datafile_01.dbf' ONLINE;

SQL>Database altered.

Delete datafile from old location
rm -f /old/location/datafile_01.dbf

==================================
Database is in NOARCHIVELOG mode
==================================

- Shutdown the database

SHUTDOWN IMMEDIATE;

- Copy or move the datafile to its new location.
dd if=/old/location/datafile_01.dbf of=new/location/datafile_01.dbf bs=4096
or
cp -p /old/location/datafile_01.dbf /new/location/datafile_01.dbf

- STARTUP MOUNT
STARTUP MOUNT;

- Rename the file:
ALTER DATABASE RENAME FILE '/old/location/datafile_01.dbf' TO '/new/location/datafile_01.dbf';

-Open The Database
ALTER DATABASE OPEN;