SQL Shared Area - BIND MISMATCH

select reason_not_shared, count(*) cursors, count(distinct sql_id) sql_ids
from v$sql_shared_cursor
unpivot(val for reason_not_shared in(
 UNBOUND_CURSOR,SQL_TYPE_MISMATCH,OPTIMIZER_MISMATCH,OUTLINE_MISMATCH,
  STATS_ROW_MISMATCH,LITERAL_MISMATCH,FORCE_HARD_PARSE,EXPLAIN_PLAN_CURSOR,
  BUFFERED_DML_MISMATCH,PDML_ENV_MISMATCH,INST_DRTLD_MISMATCH,SLAVE_QC_MISMATCH,
  TYPECHECK_MISMATCH,AUTH_CHECK_MISMATCH,BIND_MISMATCH,DESCRIBE_MISMATCH,
  LANGUAGE_MISMATCH,TRANSLATION_MISMATCH,BIND_EQUIV_FAILURE,INSUFF_PRIVS,
  INSUFF_PRIVS_REM,REMOTE_TRANS_MISMATCH,LOGMINER_SESSION_MISMATCH,INCOMP_LTRL_MISMATCH,
  OVERLAP_TIME_MISMATCH,EDITION_MISMATCH,MV_QUERY_GEN_MISMATCH,USER_BIND_PEEK_MISMATCH,
  TYPCHK_DEP_MISMATCH,NO_TRIGGER_MISMATCH,FLASHBACK_CURSOR,ANYDATA_TRANSFORMATION,
  PDDL_ENV_MISMATCH,TOP_LEVEL_RPI_CURSOR,DIFFERENT_LONG_LENGTH,LOGICAL_STANDBY_APPLY,
  DIFF_CALL_DURN,BIND_UACS_DIFF,PLSQL_CMP_SWITCHS_DIFF,CURSOR_PARTS_MISMATCH,
  STB_OBJECT_MISMATCH,CROSSEDITION_TRIGGER_MISMATCH,PQ_SLAVE_MISMATCH,TOP_LEVEL_DDL_MISMATCH,
  MULTI_PX_MISMATCH,BIND_PEEKED_PQ_MISMATCH,MV_REWRITE_MISMATCH,ROLL_INVALID_MISMATCH,
  OPTIMIZER_MODE_MISMATCH,PX_MISMATCH,MV_STALEOBJ_MISMATCH,FLASHBACK_TABLE_MISMATCH,
  LITREP_COMP_MISMATCH,PLSQL_DEBUG,LOAD_OPTIMIZER_STATS,ACL_MISMATCH,
  FLASHBACK_ARCHIVE_MISMATCH,LOCK_USER_SCHEMA_FAILED,REMOTE_MAPPING_MISMATCH,LOAD_RUNTIME_HEAP_FAILED,
  HASH_MATCH_FAILED,PURGED_CURSOR,BIND_LENGTH_UPGRADEABLE,USE_FEEDBACK_STATS
))
where val = 'Y'
group by reason_not_shared

order by 2 desc, 3, 1;

---->See output as below and see the issues with BIND MISMATCH and other MISMATCH

BIND_MISMATCH 111124 300

LANGUAGE_MISMATCH 6181 3023

AUTH_CHECK_MISMATCH 5073 2613

TRANSLATION_MISMATCH 5069 2611

BIND_LENGTH_UPGRADEABLE 1663 863

BIND_EQUIV_FAILURE 181 51

BIND_UACS_DIFF 69 69

OPTIMIZER_MISMATCH 51 45

LOAD_OPTIMIZER_STATS 33 29

Solution --To clean the queries periodically every 4 hours or so, you can write job also with procedure to execute in regular interval with sharable_mem > 400MB as an example.

SELECT *
          FROM   v$sqlarea
          WHERE  version_count > 100
          ORDER BY sharable_mem desc;

-->Take top 5 shareble_memory sql ids and purge them to release shared pool area (SGA).

 select 'exec sys.dbms_shared_pool.purge ('''||address||','||hash_value||''', ''C'');'

from v$sqlarea
where sql_id='656u1muc0d4h0';

-->Run below to purge

exec sys.dbms_shared_pool.purge ('0700010300A34620,2550567424', 'C');

--We also implemented below in 11gr2 in db

alter system set “_optimizer_use_feedback” = false scope=both;

alter system set "_optimizer_adaptive_cursor_sharing"=false scope=both;

alter system set "_optimizer_extended_cursor_sharing_rel"=none scope=both;

--Extra - to check _ underscore hidden parameter values

col parameter for a35

col "Session value" for a10

col "Instance value" for a10

SELECT x.ksppinm "Parameter", Y.ksppstvl "Session Value",Z.ksppstvl "Instance Value" FROM x$ksppi X, x$ksppcv Y, x$ksppsv Z WHERE x.indx = Y.indx AND x.indx = z.indx AND x.ksppinm like '_optimizer%' order by x.ksppinm;

----What is Cardinality feedback introduced in 11gr2
Cardinality Feedback (Oracle Database 10g to 11g Change)
Cardinality feedback is an enhancement made to the Optimizer in Oracle Database 11g Release
2. Cardinality feedback compares cardinality estimates used to derive the plan with the actual
cardinality seen in the first execute. If the estimate is 2X off, the cursor is marked for hard parse
next time around. The cardinality information seen at first execute is supplied at the next hard
parse thus allowing the Optimizer an opportunity to improve on the plan now that it knows more
about the actual cardinality seen in the query. If cardinality feedback is used, it is displayed in the
section of the execution plan. Cardinality feedback works for predicates on tables, indexes and
group by clauses. It does not help for cardinality mis-estimates for joins. Feedback is not
persistent on disk, it resides in memory only. The Optimizer will need to “relearn” something if the
database is shutdown and restarted. (Related to _optimizer_use_feedback parameter.)

ORA-20005: object statistics are locked (stattype = ALL) - Solution

After creation index as below gather index giving error

 create index VIJ.VIJ_SALES_INFO_IDX1 on VIJ.VIJ_SALES_INFO(VALUE1) ;

EXEC DBMS_STATS.gather_index_stats('VIJ', 'VIJ_SALES_INFO_IDX1');

--Error while gather_index_stats--------

Error starting at line : 5 in command -

BEGIN DBMS_STATS.gather_index_stats('VIJ', 'VIJ_SALES_INFO_IDX1'); END;

Error report -

ORA-20005: object statistics are locked (stattype = ALL)

ORA-06512: at "SYS.DBMS_STATS", line 18701

ORA-06512: at "SYS.DBMS_STATS", line 18724

ORA-06512: at line 1

Resolution :

Cause

SQL> select owner,table_name,STATTYPE_LOCKED from dba_tab_statistics where table_name='VIJ_SALES_INFO' and owner='VIJ';

You would get STATTYPE_LOCKED  as value - ALL

STATTYPE_LOCKED – ALL stats are locked for the table
STATTYPE_LOCKED – NULL stats are not locked

EXEC DBMS_STATS.unlock_table_stats('VIJ','VIJ_SALES_INFO');

After unlock table stats , able to do gather index stats.

EXEC DBMS_STATS.gather_index_stats('VIJ', 'VIJ_SALES_INFO_IDX1');

PL/SQL procedure successfully completed.

FAQ - On Materialized Views and DBMS_JOB Jobs

 Refer Oracle Support Note - FAQ: On Materialized Views and DBMS_JOB Jobs (Doc ID 1365482.1)

Oracle expdp - impdp tricks

Getting started

1) Create expimpuser user (In place of expimpuser you can give any name or use existing database user name, don't use sys)

   and give connect, resource to expimpuser

CREATE USER expimpuser IDENTIFIED BY passwowrd123 DEFAULT TABLESPACE APPS_TS;

GRANT CONNECT, RESOURCE TO expimpuser;

2) Create physical directory on the system like /u01/expimpdir -change path accroding to your system.

mkdir /u01/expimpdir

chmod -R 777 /u01/expimpdir

3) Create DBA dump directory where yyo would be placining all exmport dmp filies.

CREATE OR REPLACE DIRECTORY DATA_PUMP AS '/u01/expimpdir';

SELECT * FROM DBA_DIRECTORIES;

GRANT READ,WRITE ON DIRECTORY DATA_PUMP TO expimpuser;

GRANT READ,WRITE ON DIRECTORY DATA_PUMP TO system;

Dirrerent export scenarios

1) Export full schema from source to target server

  Lets say you want to export all the hr user data 

  GRANT  DATAPUMP_EXP_FULL_DATABASE  TO  hr;

  GRANT READ,WRITE ON DIRECTORY DATA_PUMP TO hr;

    ->If using container architectre use @PDB1(PDB1 is name of pluggable db) to connect to specific pluggable database.

%U would append numbers to dump directory once the filesize reaches 100GB.

Use PARALLEL to make it fast and compress to reduce the dump file size.

expdp hr/h#rc#01@PDB1 directory=DATA_PUMP dumpfile=HR_PDB1_%U.dmp 

COMPRESS=Y filesize=100G PARALLEL=5 logfile=EXPDP_HR_PDB1_20Jan23.log

-> Non PDB , don't use @PDB1 (PDB1 is name of pluggable db)

expdp hr/h#rc#01@PDB1 directory=DATA_PUMP dumpfile=HR_PDB1_%U.dmp 

COMPRESS=Y filesize=100G PARALLEL=5 logfile=EXPDP_HR_PDB1_20Jan24.log

->To import , make user hr user exists in new database , copy the dump files to target server /u01/expimpdir

impdp hr/h#rc#01@PDB1 directory=DATA_PUMP dumpfile=HR_PDB1_%U.dmp 

PARALLEL=5 logfile=IMPDP_HR_PDB1_20Jan24.log

2) Export View as table

expdp system/passwowrd##@PDB1  directory=DATA_PUMP dumpfile=HR_HR_VIEW1_%U.dmp 

COMPRESS=Y filesize=100G views_as_tables=HR.HR_VIEW PARALLEL=5 logfile=OHR_HR_VIEW1_17Jan24.log

Exclude indexes

Monitor jobs using

 select * from DBA_DATAPUMP_JOBS;

---Tips 

Always use METRICS=YES

and LOGTIME=ALL

--Monitoring

Monitor a Data Pump process in V$SESSION_LONGOPS

$ expdp ... job_name=DUMPJOB

SQL> select sid, serial#, sofar, totalwork

from v$session_longops

where opname = 'DUMPJOB' and sofar != totalwork;

sofar Shows how much work in MB has been done so far in relation to totalwork

totalwork Shows the total amount of work in MB

---Current status

SELECT sl.sid, sl.serial#, sl.sofar, sl.totalwork, dp.owner_name, dp.state, dp.job_mode

     FROM v$session_longops sl, v$datapump_job dp

     WHERE sl.opname = dp.job_name

     AND sl.sofar != sl.totalwork;

select * from dba_datapump_jobs;

--Run time details

SELECT b.username, a.sid, b.opname, b.target,

            round(b.SOFAR*100/b.TOTALWORK,0) || '%' as "%DONE", b.TIME_REMAINING,

            to_char(b.start_time,'YYYY/MM/DD HH24:MI:SS') start_time

     FROM v$session_longops b, v$session a

     WHERE a.sid = b.sid      ORDER BY 6;

--sofar work done

SELECT sl.sid, sl.serial#, sl.sofar, sl.MESSAGE,sl.totalwork, dp.owner_name, dp.state, dp.job_mode

FROM v$session_longops sl, v$datapump_job dp

WHERE sl.opname = dp.job_name;

Oracle Tuning Advisor - Running SQL Tuning advisor steps

 1. Create Tuning Task

 

DECLARE l_sql_tune_task_id  VARCHAR2(100);

BEGIN

  l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (

                          sql_id      => '4us78thunhn00',

                          scope       => DBMS_SQLTUNE.scope_comprehensive,

                          time_limit  => 500,

                          task_name   => '4us78thunhn00_tuning_task1',

                          description => 'Tuning task1 for statement 4us78thunhn00');

  DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);

END;

/

 

 

SQL> begin   

DBMS_SQLTUNE.SET_TUNING_TASK_PARAMETER(task_name => '4us78thunhn00_tuning_task1', parameter => 'TIME_LIMIT', value => 15000);   

end;   

PL/SQL procedure successfully completed. 

 

2. Execute Tuning task:

 

EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => '4us78thunhn00_tuning_task1');

 

3. Get the Tuning advisor report.

set long 65536

set longchunksize 65536

set linesize 600

set pagesize 9000

select dbms_sqltune.report_tuning_task('4us78thunhn00_tuning_task1') from dual;

 

 

4. Get list of tuning task present in database:

 

We can get the list of tuning tasks present in database from DBA_ADVISOR_LOG

 

SELECT TASK_NAME, STATUS FROM DBA_ADVISOR_LOG WHERE TASK_NAME ;

5. Drop a tuning task:

execute dbms_sqltune.drop_tuning_task('4us78thunhn00_tuning_task1');

Linux Network Bandwidth Monitoring Tools

https://www.tecmint.com/linux-network-bandwidth-monitoring-tools/

19c Refreshable Clone - Alternate to Data Guard and High Availability Solution for Oracle Standard Edition

19c Refreshable Clone - Alternate to Data Guard and High Availability Solution for Oracle Standard Edition

Use Cases

1) Only HA solution for Standard Edition as Data Guard is not supported in Standard Edition.

2) Same process can be used in Enterprise Edition also.

3) Only supported in CDB - 19c architecture

4) Fast cloning process without using RMAN backup / restore process.

5) Easy to implement ,  even by junior DBA's

----------------------------Steps------------------

 

Source CDB (hostname)	Target CDB (hostname)	Source PDB	Target PDB
SRCCDB	TRGCDB	PROD	PRODREF
sourceserver	targetserver

 

Oracle Database 19c Standard Edition 2 Release 19.0.0.0.0 - Production

Version 19.10.0.0.0

Steps to create Refreshable PDB

1)      Create new CDB (Same OS, Same Patch set level) on target host where you wanted to create refreshable PDB or you can use any existing CDB database on any other server.

2)      Add tns entry in each other’s tnsnames.ora

3)      Creation of a refreshable clone PDB requires a database link in the source and target CDB root container, create db link in both sides.

4)      Create a user in the source CDB SRCCDB (host-sourceserver) for database link purpose

                     sqlplus as sysdba

SQL> create user c##clone_user identified by <pass> temporary tablespace temp container=ALL;

User created.

 

SQL> grant create session, create pluggable database, sysoper to c##clone_user container=ALL;

Grant succeeded.

 

5)      In the target server cdb – TRGCDB (Host- targetserver) create database link pointing to source CDB using c##clone_user created above in the source CDB as above

 First add below entry in tnsnames.ora in target server

-->Refreshable PDB - CDB tns entry in target node

 SRCCDB_ sourceserver =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = sourceserver.vij.com)(PORT = 1521))

      (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = SRCCDB.vij.com)

    )

  )

 àcreate database link , connect as sysdba to cdb

 SQL> create database link clonesource connect to c##clone_user identified by <pass> using 'SRCCDB_sourceserver';

 Database link created.

 SQL> select * from dual@clonesource;

D

-

X

 

6)      Create a refreshable clone on target , connect to CDB as sysdba

 --target - create below directory on target

 /u01/oradata/PRODREF/datafiles

 --Create Refreshable DB took 5 Min - DB size 8 GB

-bash-4.2$ sqlplus "/as sysdba"

SQL*Plus: Release 19.0.0.0.0 - Production on Tue Sep 23 11:04:05 2022

Version 19.10.0.0.0

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

Connected to:

Oracle Database 19c Standard Edition 2 Release 19.0.0.0.0 - Production

Version 19.10.0.0.0

SQL> create pluggable database PRODREF from PROD@clonesource refresh mode manual FILE_NAME_CONVERT=('/u01/oradata/PROD/datafiles/','/u01/oradata/PRODREF/datafiles/');

Pluggable database created.

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED

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

         2 PDB$SEED                       READ ONLY  NO

         3 PRODREF                       MOUNTED

         4 PRODSTG                       READ WRITE NO

 

SQL> select PDB_NAME,REFRESH_MODE,REFRESH_INTERVAL,LAST_REFRESH_SCN from dba_pdbs;

 PDB_NAME

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

REFRES REFRESH_INTERVAL LAST_REFRESH_SCN

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

PRODREF

MANUAL                         142927690

PDB$SEED

NONE

PRODSTG

NONE

7)      We can open refreshable clone READ ONLY to query from the tables to verify below as an example

--------on source

-bash-4.2$ sqlplus "/as sysdba"

SQL*Plus: Release 19.0.0.0.0 - Production on Tue Sep 23 12:30:18 2022

Version 19.10.0.0.0

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

Connected to:

Oracle Database 19c Standard Edition 2 Release 19.0.0.0.0 - Production

Version 19.10.0.0.0

SQL> show pdbs

 

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED

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

         2 PDB$SEED                       READ ONLY  NO

         3 PROD                          READ WRITE NO

SQL> alter session set container=PROD;

 Session altered.

 SQL> show user

USER is "SYS"

SQL> desc test

ERROR:

ORA-04043: object test does not exist

 SQL> create table test(id number);

 Table created.

 SQL> insert into test values (1);

 1 row created.

 SQL> commit;

 Commit complete.

 SQL>

 -------------------------on target check the table values

SQL> show pdbs

     CON_ID CON_NAME                       OPEN MODE  RESTRICTED

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

         2 PDB$SEED                       READ ONLY  NO

         3 PRODREF                       MOUNTED

         4 PRODSTG                       READ WRITE NO

SQL>

 SQL> show con_name

 CON_NAME

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

CDB$ROOT

 SQL> alter pluggable database PRODREF refresh;

 Pluggable database altered.

 SQL>

-----------------at the same time alert log in the target

2022-09-23T11:29:39.739979-04:00

alter pluggable database PRODREF refresh

2022-09-23T11:29:40.040005-04:00

Applying media recovery for pdb-4099 from SCN 142927690 to SCN 142933068

Remote log information: count-1

thr-1,seq-3315,logfile-/u01/oradata/archives/TRGCDB/foreign_archivelog/PROD/2022_08_23/o1_mf_1_3315_kj9x2wg2_.arc,los-142884167,nxs-18446744073709551615,maxblks-78629

PRODREF(3):Media Recovery Start

2022-09-23T11:29:40.040482-04:00

PRODREF(3):Serial Media Recovery started

PRODREF(3):max_pdb is 5

2022-09-23T11:29:40.067924-04:00

PRODREF(3):Media Recovery Log /u01/oradata/archives/TRGCDB/foreign_archivelog/PROD/2022_08_23/o1_mf_1_3315_kj9x2wg2_.arc

2022-09-23T11:29:40.335010-04:00

PRODREF(3):Incomplete Recovery applied until change 142933068 time 08/23/2022 11:38:04

2022-09-23T11:29:40.339377-04:00

PRODREF(3):Media Recovery Complete (TRGCDB)

Completed: alter pluggable database PRODREF refresh

 -----------------------------target

SQL> show pdbs

 

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED

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

         2 PDB$SEED                       READ ONLY  NO

         3 PRODREF                       MOUNTED

         4 PRODSTG                       READ WRITE NO

SQL>

 

Let’s now open PRODREF in Read Only mode to verify the refresh

 SQL> alter pluggable database PRODREF open read only;

 Pluggable database altered.

 SQL> show pdbs

     CON_ID CON_NAME                       OPEN MODE  RESTRICTED

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

         2 PDB$SEED                       READ ONLY  NO

         3 PRODREF                       READ ONLY  NO

         4 PRODSTG                       READ WRITE NO

SQL>

 SQL> alter session set container=PRODREF;

 Session altered.

 SQL> select * from test;

 

        ID

----------

         1

 

SQL>

 

SQL> alter pluggable database PRODREF close immediate;

 Pluggable database altered.

 SQL> show pdbs

     CON_ID CON_NAME                       OPEN MODE  RESTRICTED

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

         2 PDB$SEED                       READ ONLY  NO

         3 PRODREF                       MOUNTED

         4 PRODSTG                       READ WRITE NO

 

8)      Set the refresh mode to refresh at every 4 minutes

 

SQL> alter pluggable database PRODREF refresh mode every 4 minutes;

 Pluggable database altered.

 SQL> select PDB_NAME,REFRESH_MODE,REFRESH_INTERVAL,LAST_REFRESH_SCN from dba_pdbs where PDB_NAME='PRODREF';

 PDB_NAME

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

REFRES REFRESH_INTERVAL LAST_REFRESH_SCN

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

PRODREF

AUTO                  4        142935544

 SQL>

SQL> select name from v$active_services;

 

NAME

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

TRGCDB.vij.com

PRODSTG

TRGCDBXDB

PRODREF

SYS$BACKGROUND

SYS$USERS

 

9)      We can follow above step no 7 to open it in Read only and query the data, once done need to close it again for refresh to start automatically every 4 minutes

10)   Note that the automatic refresh will success only if the PDB clone is mounted.

11)   Note also that a manual refresh can be done even if the auto refresh is configured.

12)   We can open the refreshable PDB in Read Write as below steps (Not performed in testing phase) , but once opened in Read Write can not be put back to refreshable mode and need to rebuild the refreshable clone. Used dummy PDB name - PDBRW as an example to show the commands to avoid accidental open of PRODREF.

 

To open the database in a read write mode, we have to set the refresh mode to none, below steps taken from the doc, not tested in our environment, change the PDB name accordingly.

SQL> alter pluggable database PDBRW refresh mode none;

alter pluggable database PDBRW refresh mode none

*

ERROR at line 1:

ORA-65025: Pluggable database PDBRW is not closed on all instances.

 SQL> alter pluggable database PDBRW close immediate;

 Pluggable database altered.

 SQL> alter pluggable database PDBRW refresh mode none;

 Pluggable database altered.

 SQL> col pdb_name for a15

SQL> select PDB_NAME,REFRESH_MODE,REFRESH_INTERVAL,LAST_REFRESH_SCN from dba_pdbs where PDB_NAME='PDBRW';

 

PDB_NAME        REFRES REFRESH_INTERVAL LAST_REFRESH_SCN

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

PDBRW                 NONE                            39272683

 

SQL> alter pluggable database PDBRW open read write;

 Pluggable database altered.

 SQL> show pdbs;

     CON_ID CON_NAME                       OPEN MODE  RESTRICTED

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

         2 PDB$SEED                       READ ONLY  NO

         3 PDBRW                             READ WRITE NO

13)   One other usage of refreshable PDB is that the clone can be used as a golden master for snapshots at PDB level. And these snapshots can be used for cloning environments for developers.

14)   You can create another PDB from the Refreshed PDB and open it Read Write , thus your refreshable  PDB would be intact and would be in refreshable mode only.

 E.g – On target server where you have created PRODREF refreshable PDB. Create Read Write PDB.

 Sqlplus “/as sysdba”

 Create pluggable database PRODRW from PRODREF;

 alter pluggable database PRODRW open read write;

 If there is Disaster at the source – primary server this can be served as Standby database for High Availability.

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