Transport Good Execution Plan(Hash Plan) from one database to another database

1. Create Empty Tuning Set

BEGIN

  DBMS_SQLTUNE.CREATE_SQLSET (

    sqlset_name  => 'SQLT_WKLD_STS', 

    description  => 'STS to store SQL from the private SQL area' 

);

END;

2. Load the SQL Information for the SQL ID into this Tuning Set

DECLARE c_sqlarea_cursor DBMS_SQLTUNE.SQLSET_CURSOR; BEGIN OPEN c_sqlarea_cursor FOR SELECT VALUE(p) FROM TABLE( DBMS_SQLTUNE.SELECT_CURSOR_CACHE( ' sql_id = ''2t8gdjy0pbs57'' AND plan_hash_value = ''618955923'' ') ) p; -- load the tuning set DBMS_SQLTUNE.LOAD_SQLSET ( sqlset_name => 'SQLT_WKLD_STS' , populate_cursor => c_sqlarea_cursor ); END; /

3. Display the content of Sql Tuning set

SELECT SQL_ID, PARSING_SCHEMA_NAME AS "SCH", SQL_TEXT, ELAPSED_TIME AS "ELAPSED", BUFFER_GETS FROM TABLE( DBMS_SQLTUNE.SELECT_SQLSET( 'SQLT_WKLD_STS' ) ); SQL_ID SCH SQL_TEXT ELAPSED BUFFER_GETS ------------- ------------------------- -------------------------------------------------------------------------------- ---------- ----------- 2t8gdjy0pbs57 TESTUSER INSERT /*+ APPEND */ INTO W_L_T.PROJECT as SELECT….. 24844903 2577896

SELECT sql_id, parsing_schema_name as "SCH", sql_text,buffer_gets as "B_GETS",disk_reads, ROUND(disk_reads/buffer_gets*100,2) "%_DISK" FROM TABLE( DBMS_SQLTUNE.SELECT_SQLSET( 'SQLT_WKLD_STS', '(disk_reads/buffer_gets) >= 0.50' ) ); SQL_ID SCH SQL_TEXT B_GETS DISK_READS %_DISK ------------- ------------------------- -------------------------------------------------------------------------------- ---------- ---------- ---------- 2t8gdjy0pbs57 TESTUSER INSERT /*+ APPEND */ INTO W_L_T.PROJECT as SELECT….. 2577896 2435837 94.49

4. Create a Staging table to hold the exported SQL Tuning set

BEGIN DBMS_SQLTUNE.CREATE_STGTAB_SQLSET (table_name => 'MY_TUNING_SET', schema_name => 'PDBADMIN'); END; /

5. Load the SQL Tuning Set information to the Staging Table

BEGIN DBMS_SQLTUNE.PACK_STGTAB_SQLSET ( sqlset_name => 'SQLT_WKLD_STS', sqlset_owner => 'PDBADMIN', staging_table_name => 'MY_TUNING_SET', staging_schema_owner => 'PDBADMIN' ); END; /

6. Export The table

nohup expdp userid="USERNAME/PASSWORD@lnx01.oraclevcn.com:1521/PDB1S001.oraclevcn.com" parfile/u01/dumps/mytunigset_table.par &

7. Import of the Staging table

nohup impdp userid="USERNAME/PASSWORD@lnx02.oraclevcn.com:1521/PDB1P001.oraclevcn.com" parfile/u01/dumps/mytunigset_table.par &

8. Unpack the SQL Tuning set from the staging table to the destination server

BEGIN DBMS_SQLTUNE.UNPACK_STGTAB_SQLSET ( sqlset_name => '%' , sqlset_owner => 'PDBADMIN' , replace => true , staging_table_name => 'MY_TUNING_SET' , staging_schema_owner => 'PDBADMIN'); END; /

9. Load the plan from SQL Tuning Set to SQL Plan Baseline

VARIABLE v_plan_cnt NUMBER EXECUTE :v_plan_cnt := DBMS_SPM.LOAD_PLANS_FROM_SQLSET( - sqlset_name => 'SQLT_WKLD_STS', - sqlset_owner => 'PDBADMIN', - basic_filter => 'sql_id = ''2t8gdjy0pbs57'' AND plan_hash_value = 618955923' );

10. Flush the two SQLs from the shared pool, so that the optimizer will pick the new plan

select 'exec DBMS_SHARED_POOL.PURGE ('''||ADDRESS||', '||HASH_VALUE||''', ''C'');' from V$SQLAREA where SQL_ID in ('2t8gdjy0pbs57'); exec SYS.DBMS_SHARED_POOL.PURGE ('0000001006B396C8, 2113289046', 'C');

11. To verify the Baseline created in the database

select sql_handle, plan_name, enabled, accepted, fixed from dba_sql_plan_baselines;

12. Drop SQL tuning Set from Source

BEGIN DBMS_SQLTUNE.drop_sqlset (sqlset_name => 'SQLT_WKLD_STS'); END;

I hope this article helped you. Your suggestions/feedback are most welcome.

Keep learning... Have a great day!!!

Thank you,
Amit Pawar
Email: amitpawar.dba@gmail.com
WhatsApp No: +91-8454841011

Datapump FILESIZE parameter

FILESIZE :

• It specifies the maximum size of each dump file. If the size is reached for any member of the dump file set, that file is closed and an attempt is made to create a new file, if the file specification contains a substitution variable.
• You can set filesize parameter limits for the dumpfile size same as below,[FILESIZE=integer[B | K | M | G] where B is bytes, K is KB, M is MB and G is GB]
• The below command will create each dumpfile of size 50ok.If the export data is more than 500k then you specify dumpfile as wildcard “%U” to create multiple files.

[oracle@srv1 ~]$ expdp amit/oracle directory=test_dir dumpfile=amit_data%U.dmp logfile=amit_data.log schemas=amit filesize=500k Export: Release 18.0.0.0.0 - Production on Fri Aug 10 11:54:40 2018 Version 18.3.0.0.0 Copyright (c) 1982, 2018, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production Starting "AMIT"."SYS_EXPORT_SCHEMA_03": amit/******** directory=test_dir dumpfile=amit_data%U.dmp logfile=amit_data.log schemas=amit filesize=500k Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS Processing object type SCHEMA_EXPORT/STATISTICS/MARKER Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA Processing object type SCHEMA_EXPORT/TABLE/TABLE Processing object type SCHEMA_EXPORT/TABLE/COMMENT Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT . . exported "AMIT"."EMP" 873.1 KB 100000 rows . . exported "AMIT"."SYS_EXPORT_SCHEMA_01" 0 KB 0 rows . . exported "AMIT"."SYS_EXPORT_SCHEMA_02" 434.3 KB 1445 rows Master table "AMIT"."SYS_EXPORT_SCHEMA_03" successfully loaded/unloaded ****************************************************************************** Dump file set for amit.SYS_EXPORT_SCHEMA_03 is: /u01/shared_datapump/amit_data01.dmp /u01/shared_datapump/amit_data02.dmp /u01/shared_datapump/amit_data03.dmp /u01/shared_datapump/amit_data04.dmp Job "amit"."SYS_EXPORT_SCHEMA_03" successfully completed at Fri Nov 10 11:55:21 2018 elapsed 0 00:00:41

I hope this article helped you. Your suggestions/feedback are most welcome.

Keep learning... Have a great day!!!

Thank you,
Amit Pawar
Email: amitpawar.dba@gmail.com
WhatsApp No: +91-8454841011

Install Oracle GoldenGate 19c on Linux 7

STEPS :

Environment
Download oracle 19c goldengate software

Create a separate directory to copy and unzip the software
Install Oracle 19c Goldengate using the install wizard
Verify Central Inventory
Set Environmental Variables
Login to ggsci prompt and verify the manager process status

Environment

Server Name                 : Standby OS Version                 : Red Hat Enterprise Linux Server release 7.5 Database Version         : 19.3.0.0 GoldenGate Version         : 19.1.0.0.4 for oracle db 19 Oracle Home                 : /u02/app/oracle/product/19c/dbhome_1 GoldenGate Home : /u01/app/GG_HOME

Download oracle 19c goldengate software

http://www.oracle.com/technetwork/middleware/goldengate/downloads/index.html

or

https://edelivery.oracle.com >> Select a Product Pack: Oracle Fusion Middleware

Create a separate directory to copy and unzip the software

[oracle@standby ~]$ cd /u01/

[oracle@standby u01]$ mkdir -p /u01/GG_SOFT

[oracle@standby u01]$ cd /u01/GG_SOFT

[oracle@standby GG_SOFT]$ ls

V983658-01.zip

[oracle@standby GG_SOFT]$ unzip V983658-01.zip

Install Oracle 19c Goldengate using the install wizard

[oracle@standby u01]$ mkdir -p /u01/app/GG_HOME

Install Oracle 19c Goldengate using the install wizard

 Verify Central Inventory

[oracle@standby ContentsXML]$ pwd /u01/app/oraInventory/ContentsXML [oracle@standby ContentsXML]$ [oracle@standby ContentsXML]$ cat inventory.xml | grep -i /u01/app/GG_HOME <HOME NAME="OraHome1" LOC="/u01/app/GG_HOME" TYPE="O" IDX="2"/> [oracle@standby ContentsXML]$

Set Environmental Variables

[oracle@standby ~]$ cat 19cogg.env GG19C=/u01/app/GG_HOME; export GG19C ORACLE_HOME=/u02/app/oracle/product/19c/dbhome_1; export ORACLE_HOME ORACLE_SID=standby; export ORACLE_SID PATH=$PATH:/u01/app/GG_HOME; export PATH LD_LIBRARY_PATH=$ORACLE_HOME/lib:/u01/app/GG_HOME; export LD_LIBRARY_PATH alias ggsci='cd $GG19C; ./ggsci' [oracle@ standby ~]$

Login to ggsci prompt and verify the manager process status

[oracle@standby ~]$ ggsci Oracle GoldenGate Command Interpreter for Oracle Version 19.1.0.0.4 OGGCORE_19.1.0.0.0_PLATFORMS_191017.1054_FBO Linux, x64, 64bit (optimized), Oracle 19c on Oct 18 2019 01:38:51 Operating system character set identified as UTF-8. Copyright (C) 1995, 2019, Oracle and/or its affiliates. All rights reserved. Login to ggsci prompt and verify the manager status GGSCI (standby) 1> GGSCI (standby) 2> info mgr Manager is running (IP port TCP:standby.7809, Process ID 14663). GGSCI (standby) 3>

Oracle GoldenGate 19c is installed successfully

I hope this article helped you. Your suggestions/feedback are most welcome.

Keep learning... Have a great day!!!

Thank you,
Amit Pawar
Email: amitpawar.dba@gmail.com
WhatsApp No: +91-8454841011