As part of the data center migration, when migration performed with RMAN there is no need to migrate the SQL profiles from source to target. If the Migration option Expdp/impdp used then manually we have to export SQL Profiles from source and import into the target. So that profiles will be used.
First step is to create the staging table to store the Sql Profiles as Pack
exec DBMS_SQLTUNE.CREATE_STGTAB_SQLPROF(table_name=>'PBRPROFILE',schema_name=>'SYSTEM');Now export all the required SQL pfiles using below syntax and example
exec DBMS_SQLTUNE.PACK_STGTAB_SQLPROF (staging_table_name =>'PBRPROFILE',STAGING_SCHEMA_OWNER => 'SYSTEM',profile_name=>'coe_5b8kbkh51uhh4_1821954918');
exec DBMS_SQLTUNE.PACK_STGTAB_SQLPROF (staging_table_name =>'PBRPROFILE',STAGING_SCHEMA_OWNER => 'SYSTEM',profile_name=>'coe_cz363fhs2rkkh_1200659525');
exec DBMS_SQLTUNE.PACK_STGTAB_SQLPROF (staging_table_name =>'PBRPROFILE',STAGING_SCHEMA_OWNER => 'SYSTEM',profile_name=>'coe_8szq8f5x9007z_2328025242');
exec DBMS_SQLTUNE.PACK_STGTAB_SQLPROF (staging_table_name =>'PBRPROFILE',STAGING_SCHEMA_OWNER => 'SYSTEM',profile_name=>'coe_dhmmudtcng0sv_2504315874');
exec DBMS_SQLTUNE.PACK_STGTAB_SQLPROF (staging_table_name =>'PBRPROFILE',STAGING_SCHEMA_OWNER => 'SYSTEM',profile_name=>'coe_gz12pf7ybcvy6_3080528474');
exec DBMS_SQLTUNE.PACK_STGTAB_SQLPROF (staging_table_name =>'PBRPROFILE',STAGING_SCHEMA_OWNER => 'SYSTEM',profile_name=>'coe_dpawpyp1nr69c_870566398');
exec DBMS_SQLTUNE.PACK_STGTAB_SQLPROF (staging_table_name =>'PBRPROFILE',STAGING_SCHEMA_OWNER => 'SYSTEM',profile_name=>'coe_cw961437ud121_1305706923');
exec DBMS_SQLTUNE.PACK_STGTAB_SQLPROF (staging_table_name =>'PBRPROFILE',STAGING_SCHEMA_OWNER => 'SYSTEM',profile_name=>'coe_3r7fmt1sjx3n7_1719987465');
exec DBMS_SQLTUNE.PACK_STGTAB_SQLPROF (staging_table_name =>'PBRPROFILE',STAGING_SCHEMA_OWNER => 'SYSTEM',profile_name=>'coe_b1xamas81dc2g_2719016920');
PL/SQL procedure successfully completed.
SQL>
PL/SQL procedure successfully completed.
SQL>
PL/SQL procedure successfully completed.
SQL>
PL/SQL procedure successfully completed.
SQL>
PL/SQL procedure successfully completed.
SQL>
PL/SQL procedure successfully completed.
SQL>
PL/SQL procedure successfully completed.
SQL>
PL/SQL procedure successfully completed.
SQL>
PL/SQL procedure successfully completed.Perform the export of the staging table where we have stored all the Tuning packs
[oracle@nmetldbprd01 ~]$ expdp directory=MIGRATION dumpfile=pbrsqlprofile.dmp logfile=pbrsqlprofile.log tables=SYSTEM.PBRPROFILE
Export: Release 12.1.0.2.0 - Production on Mon Oct 25 08:00:14 2021
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
Username: / as sysdba
Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Advanced Analytics
and Real Application Testing options
Starting "SYS"."SYS_EXPORT_TABLE_01": /******** AS SYSDBA directory=MIGRATION dumpfile=pbrsqlprofile.dmp logfile=pbrsqlprofile.log tables=SYSTEM.PBRPROFILE
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 3.062 MB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
. . exported "SYSTEM"."PBRPROFILE" 100.8 KB 9 rows
Master table "SYS"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_TABLE_01 is:
/u02/app/oracle/oradata/datastore/odsprd/pbrsqlprofile.dmp
Job "SYS"."SYS_EXPORT_TABLE_01" successfully completed at Mon Oct 25 08:00:44 2021 elapsed 0 00:00:23
Perform the SCP of the dump file from source to target as below
[oracle@ORA-OLD ~]$ scp /u02/app/oracle/oradata/datastore/odsprd/pbrsqlprofile.dmp oda-x1node0:/home/oracle/
oracle@oda-x1node0's password:
pbrsqlprofile.dmp 100% 360KB 360.0KB/s 00:00
[oracle@ORA-OLD ~]$After copying now import the dump file into the target system as below
[oracle@oda-x1node0 ~]$ impdp directory=IMPDPNL dumpfile=pbrsqlprofile.dmp logfile=pbrsqlprofile.log table_exists_action=replace
Import: Release 12.1.0.2.0 - Production on Mon Oct 25 08:05:34 2021
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
Username: / as sysdba
Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options
Master table "SYS"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_FULL_01": /******** AS SYSDBA directory=IMPDPNL dumpfile=pbrsqlprofile.dmp logfile=pbrsqlprofile.log table_exists_action=replace
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "SYSTEM"."PBRPROFILE" 100.8 KB 9 rows
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Job "SYS"."SYS_IMPORT_FULL_01" successfully completed at Mon Oct 25 08:05:59 2021 elapsed 0 00:00:22
[oracle@oda-x1node0 ~]$Now unpack the staging table using the below command
SQL> EXEC DBMS_SQLTUNE.UNPACK_STGTAB_SQLPROF(REPLACE => TRUE,staging_table_name => 'PBRPROFILE');
PL/SQL procedure successfully completed.
Let’s review the unpacked SQL Profiles
SQL> select name,sql_text,STATUS from dba_sql_profiles;
NAME SQL_TEXT STATUS
------------------------------ -------------------------------------------------------------------------------- ----------
coe_5b8kbkh51uhh4_1821954918 SELECT distinct STAGE_SKU_VARIATION.WEB_ADV_VARIATION1 AS WEB_ADV_VARIATION1, ENABLED
coe_cz363fhs2rkkh_1200659525 SELECT /*+ parallel(c,2) */ DISTINCT LKP.PRODUCT_KEY,SKU.SKU_KEY , ENABLED
MAX (D
coe_8szq8f5x9007z_2328025242 SELECT PRODUCT_SKU.SKU_KEY AS SKU_KEY, PRODUCT_SKU.PRODUCT_KEY AS PRODUCT_KEY ENABLED
coe_dhmmudtcng0sv_2504315874 SELECT DISTINCT SKU_DIMENSION.SKU_ID, ENABLED
SKU_DIMENSION.SKU_KEY,
coe_gz12pf7ybcvy6_3080528474 SELECT DISTINCT ENABLED
SKU_FACT.SKU_KEY AS SKU_KEY,
SKU_DIMENSION.SKU
coe_dpawpyp1nr69c_870566398 SELECT STAGE_PRODUCT_SKU.PRODUCT_ID AS PRODUCT_ID FROM ((select distinct b.prod ENABLED
coe_cw961437ud121_1305706923 SELECT DISTINCT ENABLED
A.PARENT_CATEGORY_ID, PARENT_DISPLAY_NAME, B.END_CATEGORY_ID,
coe_3r7fmt1sjx3n7_1719987465 SELECT /*+ PARALLEL(2) */ SKU.SKU_ID AS SKU_ID FROM STAGE_SKU_VARIATION INFOMV, ENABLED
coe_b1xamas81dc2g_2719016920 SELECT DISTINCT LKP.PRODUCT_KEY,SKU.SKU_KEY , ENABLED
MAX (DECODE (DIMENSION_ID,
9 rows selected.
SQL>Finally perform the test the responsible job for all the profiles, to ensure the SQL profiles were used when creating plan by optimizer.
