transport sql tunning sets from 11g to 12c

 

 

1. create sql tunning set

2. add sql to tunning set from AWR

3. check sql tunning set
   SELECT SQL_ID, PARSING_SCHEMA_NAME AS "SCH", SQL_TEXT, 
          ELAPSED_TIME AS "ELAPSED", BUFFER_GETS
   FROM   TABLE( DBMS_SQLTUNE.SELECT_SQLSET( 
                    sqlset_name =>  '<sts_name>', 
                    sqlset_owner => '<sts_owner>' ) );

4. create staging table
   BEGIN
     DBMS_SQLTUNE.CREATE_STGTAB_SQLSET ( 
       table_name  => '<staging_table_name>',   
       schema_name => '<schema>',   
       db_version  => DBMS_SQLTUNE.STS_STGTAB_11_2_VERSION 
     );
   END;
   /

5. pack tunning set
   BEGIN
     DBMS_SQLTUNE.PACK_STGTAB_SQLSET (      
       sqlset_name          => '<sts_name>', 
       sqlset_owner         => '<sts_owner>',   
       staging_table_name   => '<staging_table_name>',   
       staging_schema_owner => '<schema>',   
       db_version           => DBMS_SQLTUNE.STS_STGTAB_11_2_VERSION 
     );
   END;
   / 

6. remap con_dbid
   BEGIN
     DBMS_SQLTUNE.REMAP_STGTAB_SQLSET (
       staging_table_name   => '<staging_table_name>',   
       staging_schema_owner => '<schema>',   
       old_con_dbid         => 1234,   
       new_con_dbid         => 5678
   );

7. expdp/impdp

8. unpack tunning set from staging table
   BEGIN
     DBMS_SQLTUNE.UNPACK_STGTAB_SQLSET (
       sqlset_name        => '%',   
       replace            => true,   
       staging_table_name => '<staging_table_name>');
     END;
   /

certificates, keystores, wallets

cerficicates can be imported into oracle wallet, or java keystore.

option 1: import certificates into oracle wallet,

orapki wallet create -wallet <wallet_location> 

orapki wallet add 
  -wallet <wallet_location> 
  -trusted_cert -cert <certificate_location>

orapki wallet display -wallet <wallet_location> 

./okvutil upload -l "<wallet_location>" 
  -t wallet 
  -g "<virtual_wallet_name>"

option 2: import certificates into java keystore

keytool 
  -import 
  -trustcacerts 
  -alias <alias_name> 
  -file <cert_filename.crt> 
  -keystore <filename.jks>

keytool -list -v -keystore <filename.jks>

./okvutil upload -l "<keystore_location>" 
  -t jks 
  -g "<virtual_wallet_name>"

create materialized view with prebuilt table

-- step 1: create table
create table scott.weekly_orders
...

-- step 2: create materialized view
create materialized view scott.weekly_orders
  ON PREBUILT TABLE WITHOUT REDUCED PRECISION
  USING INDEX 
  REFRESH COMPLETE ON DEMAND
  USING DEFAULT LOCAL ROLLBACK SEGMENT
  USING ENFORCED CONSTRAINTS DISABLE QUERY REWRITE
as
select ...
from scott.orders
where ...
;

duplicate database to another host

pre-duplicate

alter system set cluster_database=false scope=spfile sid='*';

duplicate

-- shutdown auxiliary database
-- startup nomount

-- rman script
run
{
}

-- run command
rman catalog rcat/rcat@rcatdb auxiliary / msglog= cmdfile=

post-duplicate

alter system set cluster_database=true scope=spfile sid='*';

create physical standby in oracle 12

prepare standby

prepare primary

rman duplicate

-- backup primary
run
{
sql "alter system switch logfile";
sql "alter system checkpoint";
backup database;
backup archivelog all delete input;
backup current controlfile for standby;
}

-- duplicate to auxiliary
run
{
  duplicate target database for standby nofilenamecheck dorecover;
}

dataguard broker setup

enable/disable log transport

validation

oracle enable archivelog mode

SQL> ALTER SYSTEM SET log_archive_dest_1='location=+DG1' SCOPE=spfile sid='*';
SQL> ALTER SYSTEM SET log_archive_format='arch_%t_%s_%r.arc' SCOPE=spfile sid='*';

srvctl stop database -d ORCL -o immediate
srvctl start database -d ORCL -o mount

SQL> ALTER DATABASE ARCHIVELOG;

srvctl stop database -d ORCL -o immediate
srvctl start database -d ORCL

oracle emcli usage

emcli get_threshold 
      -target_name="DBNAME.XYZ.COM" 
      -target_type="rac_database" 
      -metric="problemTbsp" 

emcli modify_threshold 
      -target_name='DBNAME.XYZ.COM' 
      -target_type='rac_database' 
      -metric='problemTbsp' 
      -column='bytesFree' 
      -key_columns='USERS;' 
      -warning_threshold='3000' 
      -critical_threshold='1500' 
      -prevent_override='1'