Oracle 12cR2 silent installation

Step-1 : download theOracle 12cR2 software files, copied it to Linux server and unziped the files.
Step-2: Create response file under /tmp location with below details 
vi /tmp/12cR2_response_silentinstall.rsp

Note:  Please replace ORACLE_HOSTNAME, ORACLE_HOME and ORACLE_BASE location in the below file

oracle.install.responseFileVersion=/oracle/install/rspfmt_dbinstall_response_schema_v12.2.0
oracle.install.option=INSTALL_DB_SWONLY
ORACLE_HOSTNAME= ip-172-3-16-9
UNIX_GROUP_NAME=oinstall
INVENTORY_LOCATION=/u01/app/oraInventory
SELECTED_LANGUAGES=en
ORACLE_HOME=/u01/app/oracle/product/12.2.0/dbhome_1
ORACLE_BASE=/u01/app/oracle
oracle.install.db.InstallEdition=EE
oracle.install.db.OSDBA_GROUP=dba
oracle.install.db.OSOPER_GROUP=dba
oracle.install.db.OSBACKUPDBA_GROUP=dba
oracle.install.db.OSDGDBA_GROUP=dba
oracle.install.db.OSKMDBA_GROUP=dba
oracle.install.db.OSRACDBA_GROUP=dba
SECURITY_UPDATES_VIA_MYORACLESUPPORT=false
DECLINE_SECURITY_UPDATES=true
oracle.installer.autoupdates.option=SKIP_UPDATES

STEP -3 : Go to 12cR2 installation software location and run the runInstaller in silent mode
cd  <12c software copied location>/database

STEP -4 :  run below script for silent installation.
./runInstaller -ignoreSysPrereqs -showProgress -silent -responseFile /tmp/12cR2_response_silentinstall.rsp

HOW to FIND long RUNNING TRANSACTIONS in ORACLE database?

SELECT t.start_time,
  s.sid,
  s.serial#,
  s.username,
  s.status,
  s.schemaname,
  s.osuser,
  s.process,
  s.machine,
  s.terminal,
  s.program,
  s.module,
  s.type,
  TO_CHAR(s.logon_time,'DD/MON/YY HH24:MI:SS') logon_time
FROM v$transaction t,
  v$session s
WHERE s.saddr = t.ses_addr
AND s. status = 'ACTIVE'
AND s.username IS NOT NULL
ORDER BY start_time desc;

DATA GUARD Monitoring Scripts

Starting Redo Apply on standby database

Without Real Time Apply (archives applying)on standby database:

alter database recover managed standby database disconnect from session;

with real time apply (redolog):

alter database recover managed standby database using current logfile disconnect

Stopping Redo Apply on standby database:

alter database recover managed standby database cancel;

Monitoring Redo Apply on Physical Standby Database:

SELECT arch.thread# "Thread",
  arch.sequence# "LastSequenceReceived",
  appl.sequence# "LastSequenceApplied",
  (arch.sequence# - appl.sequence#) "Difference"
FROM
  (SELECT thread#,
    sequence#
  FROM v$archived_log
  WHERE (thread#, first_time) IN
    (SELECT thread#, MAX(first_time) FROM v$archived_log GROUP BY thread#
    )
  ) arch,
  (SELECT thread#,
    sequence#
  FROM v$log_history
  WHERE (thread#, first_time) IN
    (SELECT thread#, MAX(first_time) FROM v$log_history GROUP BY thread#
    )
  ) appl
WHERE arch.thread# = appl.thread#;

Standby database process status
select distinct process, status, thread#, sequence#, block#, blocks from v$managed_standby ;

If using real time apply
select TYPE, ITEM, to_char(TIMESTAMP, 'DD-MON-YYYY HH24:MI:SS') from v$recovery_progress where ITEM='Last Applied Redo';

or
select recovery_mode from v$archive_dest_status where dest_id=1;

Troubleshooting Log transport services
1) Verify that the primary database is in archive log mode and has automatic
archiving enabled:

select log_mode from v$database;

2) Verify that the sufficient space exist in the local archive destination as
well as all destinations marked as mandatory. The following query can be
used to determine all local and mandatory destinations that need to be
checked:

select dest_id,destination from v$archive_dest
where schedule=’ACTIVE’
and (binding=’MANDATORY’ or target=’PRIMARY’);

3) Determine if the last log switch to any remote destinations resulted in an
error. Immediately following a log switch run the following query:

select dest_id,status,error from v$archive_dest
where target=’STANDBY’;

Address any errors that are returned in the error column. Perform a log
switch and re-query to determine if the issue has been resolved.

4) Determine if any error conditions have been reached by querying the
v$dataguard_status view (view only available in 9.2.0 and above):

select message, to_char(timestamp,’HH:MI:SS’) timestamp
from v$dataguard_status
where severity in (‘Error’,’Fatal’)
order by timestamp

5) Gather information about how the remote destinations are performing the
archival:

select dest_id,archiver,transmit_mode,affirm,net_timeout,delay_mins,async_blocks
from v$archive_dest where target=’STANDBY’

6) Run the following query to determine the current sequence number, the last
sequence archived, and the last sequence applied to a standby:

select ads.dest_id,
max(sequence#) “Current Sequence”,
max(log_sequence) “Last Archived”,
max(applied_seq#) “Last Sequence Applied”
from v$archived_log al, v$archive_dest ad, v$archive_dest_status ads
where ad.dest_id=al.dest_id
and al.dest_id=ads.dest_id
group by ads.dest_id

If you are remotely archiving using the LGWR process then the archived
sequence should be one higher than the current sequence. If remotely
archiving using the ARCH process then the archived sequence should be equal
to the current sequence. The applied sequence information is updated at
log switch time.



Troubleshooting Redo Apply services
1. Verify that the last sequence# received and the last sequence# applied to
standby database by running the following query:

select max(al.sequence#) “Last Seq Recieved”,
max(lh.sequence#) “Last Seq Applied”
from v$archived_log al, v$log_history lh

If the two numbers are the same then the standby has applied all redo sent
by the primary. If the numbers differ by more than 1 then proceed to step
2.

2. Verify that the standby is in the mounted state:

select open_mode from v$database;

3. Determine if there is an archive gap on your physical standby database by
querying the V$ARCHIVE_GAP view as shown in the following query:

select * from v$archive_gap;

The V$ARCHIVE_GAP fixed view on a physical standby database only returns
the next gap that is currently blocking redo apply from continuing. After
resolving the identified gap and starting redo apply, query the
V$ARCHIVE_GAP fixed view again on the physical standby database to
determine the next gap sequence, if there is one. Repeat this process
until there are no more gaps.



If v$archive_gap does’nt exists:

with prod as (select max(sequence#) as seq from v_$archived_log where RESETLOGS_TIME = (select RESETLOGS_TIME from v_$database)), stby as (select max(sequence#) as seq,dest_id dest_id from v_$archived_log where first_change# > (select resetlogs_change# from v_$database) and applied = ‘YES’ and dest_id in (1,2) group by dest_id) select prod.seq-stby.seq,stby.dest_id from prod, stby

4. Verify that managed recovery is running:

select process,status from v$managed_standby;

When managed recovery is running you will see an MRP process. If you do not see an MRP process then start managed recovery by issuing the following
command:

recover managed standby database disconnect;

Some possible statuses for the MRP are listed below:

ERROR – This means that the process has failed. See the alert log or v$dataguard_status for further information.

WAIT_FOR_LOG – Process is waiting for the archived redo log to be completed. Switch an archive log on the primary and query v$managed_standby to see if the status changes to APPLYING_LOG.

WAIT_FOR_GAP – Process is waiting for the archive gap to be resolved. Review the alert log to see if FAL_SERVER has been called to resolve the gap.

APPLYING_LOG – Process is applying the archived redo log to the standby database.à



Troubleshooting SQL Apply services
1. Verify that log apply services on the standby are currently running.

To verify that logical apply is currently available to apply changes perform the following query:

SQL> SELECT PID, TYPE, STATUS, HIGH_SCN
2> FROM V$LOGSTDBY;

When querying the V$LOGSTDBY view, pay special attention to the HIGH_SCN column. This is an activity indicator. As long as it is changing each time you query the V$LOGSTDBY view, progress is being made. The STATUS column
gives a text description of the current activity.

If the query against V$LOGSTDBY returns no rows then logical apply is not running. Start logical apply by issuing the following statement:

SQL> alter database start logical standby apply;

If the query against V$LOGSTDBY continues to return no rows then proceed to step 2.

2. To determine if there is an archive gap in your dataguard configuration;  query the DBA_LOGSTDBY_LOG view on the logical standby database.

SQL> SELECT SUBSTR(FILE_NAME,1,25) FILE_NAME, SUBSTR(SEQUENCE#,1,4) “SEQ#”,
2> FIRST_CHANGE#, NEXT_CHANGE#, TO_CHAR(TIMESTAMP, ‘HH:MI:SS’) TIMESTAMP,
3> DICT_BEGIN BEG, DICT_END END, SUBSTR(THREAD#,1,4) “THR#”
4> FROM DBA_LOGSTDBY_LOG ORDER BY SEQUENCE#;

Copy the missing logs to the logical standby system and register them using the ALTER DATABASE REGISTER LOGICAL LOGFILE statement on your logical standby database. For example:

SQL> ALTER DATABASE REGISTER LOGICAL LOGFILE ‘/u01/oradata/arch/1_57.arc’;

After you register these logs on the logical standby database, you can restart log apply services. The DBA_LOGSTDBY_LOG view on a logical standby database only returns the next gap that is currently blocking SQL apply operations from continuing. After resolving the identified gap and starting log apply services, query the DBA_LOGSTDBY_LOG view again on the logical standby database to determine the next gap sequence, if there is one. Repeat this process until there are no more gaps.

6. Determine is logical apply is receiving errors while performing apply operations.

Log apply services cannot apply unsupported DML statements, DDL statements and Oracle supplied packages to a logical standby database in SQL apply mode. When an unsupported statement or package is encountered, SQL apply
operations stop. To determine if SQL apply has stopped due to errors you should query the DBA_LOGSTDBY_EVENTS view. When querying the view, select the columns in order by EVENT_TIME. This ordering ensures that a shutdown
failure appears last in the view. For example:

SQL> SELECT XIDUSN, XIDSLT, XIDSQN, STATUS, STATUS_CODE
2> FROM DBA_LOGSTDBY_EVENTS
3> WHERE EVENT_TIME =
4> (SELECT MAX(EVENT_TIME)
5> FROM DBA_LOGSTDBY_EVENTS);

If an error requiring database management occurred (such as adding a tablespace, datafile, or running out of space in a tablespace), then you can fix the problem manually and resume SQL apply.

If an error occurred because a SQL statement was entered incorrectly,conflicted with an existing object, or violated a constraint then enter the correct SQL statement and use the DBMS_LOGSTDBY.SKIP_TRANSACTION procedure
to ensure that the incorrect statement is ignored the next time SQL apply operations are run.

7. Query DBA_LOGSTDBY_PROGRESS to verify that log apply services is progressing.

The DBA_LOGSTDBY_PROGRESS view describes the progress of SQL apply operations on the logical standby databases. For example:

SQL> SELECT APPLIED_SCN, APPLIED_TIME, READ_SCN, READ_TIME,
2> NEWEST_SCN, NEWEST_TIME
3> FROM DBA_LOGSTDBY_PROGRESS;

The APPLIED_SCN indicates that committed transactions at or below that SCN have been applied. The NEWEST_SCN is the maximum SCN to which data could be applied if no more logs were received. This is usually the MAX(NEXT_CHANGE#)-1
from DBA_LOGSTDBY_LOG when there are no gaps in the list. When the value of NEWEST_SCN and APPLIED_SCN are the equal then all available changes have been applied. If you APPLIED_SCN is below NEWEST_SCN and is increasing then
SQL apply is currently processing changes.

8. Verify that the table that is not receiving rows is not listed in the DBA_LOGSTDBY_UNSUPPORTED.

The DBA_LOGSTDBY_USUPPORTED view lists all of the tables that contain datatypes not supported by logical standby databases in the current release. These tables are not maintained (will not have DML applied) by the logical
standby database. Query this view on the primary database to ensure that those tables necessary for critical applications are not in this list. If the primary database includes unsupported tables that are critical, consider using a physical standby database.

Run a SQL Tuning Advisor For A given Sql_id

When we run SQL tuning advisor against a SQL statement or sql_id, it provides tuning recommendations that can be done that query to improve performance.
 It might give suggestion to create few indexes or accepting a SQL profile.

Diagnostic and Tuning license is required to use this feature

In this below tutorial we will explain how to run sql tuning advisor against sql_Id.

Suppose the sql id is – dtj3d4das6a9a

a) Create Tuning Task

DECLARE
  l_sql_tune_task_id  VARCHAR2(100);
BEGIN
  l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
                          sql_id      => 'dtj3d4das6a9a',
                          scope       => DBMS_SQLTUNE.scope_comprehensive,
                          time_limit  => 500,
                          task_name   => 'dtj3d4das6a9a_tuning_task11',
                          description => 'Tuning task1 for statement dtj3d4das6a9a');
  DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
END;
/

b). Executing Tuning task:
begin DBMS_SQLTUNE.execute_tuning_task(task_name => 'dtj3d4das6a9a_tuning_task11'); end;

c). Get the Tuning advisor report.
select dbms_sqltune.report_tuning_task('dtj3d4das6a9a_tuning_task11') from dual;

d). 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 ;

e).Drop a tuning task:
execute dbms_sqltune.drop_tuning_task('dtj3d4das6a9a_tuning_task11');

What if the sql_id is not present in the cursor , but present in AWR snap?
SQL_ID =0u676p5cvfxz4

First we need to find the begin snap and end snap of the sql_id.
select a.instance_number inst_id, a.snap_id,a.plan_hash_value, to_char(begin_interval_time,'dd-mon-yy hh24:mi') btime, abs(extract(minute from (end_interval_time-begin_interval_time)) + extract(hour from (end_interval_time-begin_interval_time))*60 + extract(day from (end_interval_time-begin_interval_time))*24*60) minutes,
executions_delta executions, round(ELAPSED_TIME_delta/1000000/greatest(executions_delta,1),4) "avg duration (sec)" from dba_hist_SQLSTAT a, dba_hist_snapshot b
where sql_id='&sql_id' and a.snap_id=b.snap_id
and a.instance_number=b.instance_number
order by snap_id desc, a.instance_number;

From here we can get the begin snap and end snap of the sql_id.

begin_snap -> 870
end_snap -> 910

1. Create the tuning task:
DECLARE
  l_sql_tune_task_id  VARCHAR2(100);
BEGIN
  l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
                          begin_snap  => 870,
                          end_snap    => 910,
                          sql_id      => '0u676p5cvfxz4',
                          scope       => DBMS_SQLTUNE.scope_comprehensive,
                          time_limit  => 60,
                          task_name   => '0u676p5cvfxz4_AWR_tuning_task',
                          description => 'Tuning task for statement 0u676p5cvfxz4  in AWR');
  DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
END;
/
 2. Execute the tuning task:
 EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => '0u676p5cvfxz4_AWR_tuning_task');
 3. Get the tuning task recommendation report
 SELECT DBMS_SQLTUNE.report_tuning_task('0u676p5cvfxz4_AWR_tuning_task') AS recommendations FROM dual;

Drop SQL Baselines In Oracle

1.Get the sql_handle and sql_baseline name of the sql_id:


SELECT sql_handle, plan_name FROM dba_sql_plan_baselines WHERE signature IN ( SELECT exact_matching_signature FROM gv$sql WHERE sql_id='&SQL_ID')

SQL_HANDLE                                    PLAN_NAME
--------------------------------------------- ----------------------------------------------------
SQL_a7ac813cbf25e65f                          SQL_PLAN_agb417kzkbtkz479e6372

2. Drop the baseline:

SQL> select sql_handle,plan_name from dba_sql_plan_baselines where plan_name='SQL_PLAN_agb417kzkbtkz479e6372';

SQL_HANDLE                                    PLAN_NAME
--------------------------------------------- -------------------------------------------------------------------
SQL_a7ac813cbf25e65f                          SQL_PLAN_agb417kzkbtkz479e6372


declare
drop_result pls_integer;
begin
drop_result := DBMS_SPM.DROP_SQL_PLAN_BASELINE(
sql_handle => 'SQL_a7ac813cbf25e65f',
plan_name => 'SQL_PLAN_agb417kzkbtkz479e6372');
dbms_output.put_line(drop_result);
end;
/

PL/SQL procedure successfully completed.

SQL> select sql_handle,plan_name from dba_sql_plan_baselines where plan_name='SQL_PLAN_agb417kzkbtkz479e6372';

no rows selected

A  sql_handle can have multiple sql baselines attached, So if you want to drop all the sql baselines of that handle, then drop the sql handle without adding plan_name.


declare
 drop_result pls_integer;
 begin
 drop_result := DBMS_SPM.DROP_SQL_PLAN_BASELINE(
 sql_handle => 'SQL_a7ac813cbf25e65f');
 dbms_output.put_line(drop_result); 
 end;
/

Using SQL Plan Baseline to make optimizer to choose better execution plan without changing the Query/adding the hints.

Being a DBA, we might face a question, asking for tuning an SQL statement without changing the code or adding hints or adding/removing joins.Lets see how we can achieve this.Here, I will  demonstrate an example of forcing the optimizer to change the execution plan of a SQL statement without changing the SQL itself.

PROCEDURE-1)  I have created two tables EMPLOYEE and DEPARTMENT having EMP_ID and   DEPT_ID respectively as primary keys and I am creating an index on DEPT_ID  column in EMPLOYEE table and it is foreign key to DEPARTMENT.

PROCEDURE-2) The SQL statement in the example is from a large batch jobs running daily in an     Oracle database. During the tuning process it was found that adding a composite index to a big table can significantly improve the query performance. However this is a “popular” table in the database and is being used by many different modules and processes. To maintain the stability of the system we only want the new index being used by the tuned SQL, not any other SQL statements.To achieve this purpose we create the index as INVISIBLE so it is not used by the optimizer for any other SQL 

statement. For this SQL statement we add USE_INVISIBLE_INDEX hint so that the index is only used by the optimizer for this particular SQL. The problem is we are not allowed to change the code.Therefore adding the hint to the original SQL is not feasible. In order to force the original SQL statement to use an execution plan in which the invisible index is used, we use an Oracle database feature named SQL Plan Baseline, which was introduced in 11g. We can create plan baseline for the original SQL statement and for the one modified with the hint added. Then we replace the plan baseline of the original SQL with the one of the modified. Next time the original SQL runs the optimizer will use the execution plan from the modified with hint. Therefore the invisible index is used for this SQL.Oracle SQL Plan Management ensures that you get the desirable plan which will evolve over time as optimizer discovers better ones.

STEP-1)creating table and inserting some records
drop table department;
create table department(dept_id number primary key, dept_name char(100));
drop table EMPLOYEE;
create table EMPLOYEE(emp_id number primary key, emp_name char(100), dept_id number references department(dept_id));
create index empidx1 on EMPLOYEE(emp_id);
insert into department select rownum, 'DEPARTMENT'||rownum from all_objects;
insert into EMPLOYEE select rownum, 'dept'||rownum, dept_id from department;
update EMPLOYEE set dept_id = 500 where dept_id > 100;

STEP-2) Gather table stats
begin dbms_stats.gather_table_stats (USER, 'EMPLOYEE', cascade=> true); end;
begin dbms_stats.gather_table_stats (USER, 'department', cascade=> true); end;

STEP-3)let us have a look at the undesirable plan which does not use the index.
select emp_name, dept_name
    from EMPLOYEE c, department p
    where c.dept_id = p.dept_id
    and c.dept_id = :dept_id;

select * from table (dbms_xplan.display_cursor());

 SQL_ID  0u676p5cvfxz4, child number 0
-------------------------------------
select emp_name, dept_name     from EMPLOYEE c, department p     where
c.dept_id = p.dept_id     and c.dept_id = :dept_id

Plan hash value: 341203176

---------------------------------------------------------------------------------------------
| Id  | Operation                    | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |              |       |       |   479 (100)|          |
|   1 |  NESTED LOOPS                |              |    19 |  4370 |   479   (1)| 00:00:06 |
|   2 |   TABLE ACCESS BY INDEX ROWID| DEPARTMENT   |     1 |   115 |     1   (0)| 00:00:01 |
|*  3 |    INDEX UNIQUE SCAN         | SYS_C0023547 |     1 |       |     1   (0)| 00:00:01 |
|*  4 |   TABLE ACCESS FULL          | EMPLOYEE     |    19 |  2185 |   478   (1)| 00:00:06 |
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("P"."DEPT_ID"=TO_NUMBER(:DEPT_ID))
   4 - filter("C"."DEPT_ID"=TO_NUMBER(:DEPT_ID))

Note
-----
   - dynamic sampling used for this statement (level=2)
 
STEP-4) Load above undesirable plan into baseline to establish a SQL plan baseline for this query into which the desired plan will be loaded later.
 DECLARE
  n1 NUMBER;
BEGIN
  n1 := dbms_spm.load_plans_from_cursor_cache(sql_id => '0u676p5cvfxz4');
END;

 select  sql_text, sql_handle, plan_name, enabled
  from     dba_sql_plan_baselines
where lower(sql_text) like   '%emp_name%';

STEP-5)Disable undesirable plan so that this plan will not be used

declare
    cnt number;
   begin
   cnt := dbms_spm.alter_sql_plan_baseline (
    SQL_HANDLE => 'SQL_c17c9f7d83124502',
    PLAN_NAME => 'SQL_PLAN_c2z4zgq1j4j823ed2aa92',
    ATTRIBUTE_NAME => 'enabled',
    ATTRIBUTE_VALUE => 'NO');
    end;


 check enabled is NO 
select  sql_text, sql_handle, plan_name, enabled
    from     dba_sql_plan_baselines
    where lower(sql_text) like   '%emp_name%';

STEP-6)  Now we use hint in the above SQL to generate the optimal plan which uses index

select /*+ index(e)*/ emp_name, dept_name
 from EMPLOYEE e, department d
where e.dept_id = d.dept_id
    and e.dept_id = :dept_id;
 
select * from table (dbms_xplan.display_cursor());

STEP-7)Now we will load the hinted plan  into baseline, Note that we have SQL_ID and PLAN_HASH_VALUE of the hinted statement and SQL_HANDLE for the unhinted statement i.e. we are associating hinted plan with unhinted statement

   
DECLARE
cnt NUMBER;
BEGIN
  cnt := dbms_spm.load_plans_from_cursor_cache(sql_id => 'dtj3d4das6a9a', plan_hash_value => 2379270125, sql_handle => 'SQL_c17c9f7d83124502');
END;


STEP-8)Verify that there are now two plans loaded for that SQL statement:
 Unhinted sub-optimal plan is disabled  Hinted optimal plan which even though is for a  “different query,”  can work with earlier unhinted query (SQL_HANDLE is same)  is enabled.

 select  sql_text, sql_handle, plan_name, enabled
    from     dba_sql_plan_baselines
    where lower(sql_text) like   '%emp_name%';


STEP-9)Verify that hinted plan is used even though we do not use hint in the query.The note confirms that baseline has been used for this statement

   select emp_name, dept_name
    from EMPLOYEE c, department p
    where c.dept_id = p.dept_id
    and c.dept_id = :dept_id; 
 
   select * from table (dbms_xplan.display_cursor());
  select * FROM table(dbms_xplan.display_cursor(format=>'typical +peeked_binds'));

 select  sql_text, sql_handle, plan_name, enabled
    from     dba_sql_plan_baselines
    where lower(sql_text) like   '%emp_name%';
 
    select plan_table_output from table(dbms_xplan.display('plan_table',null,'typical -cost -bytes'));

Summary: Using this method, you can swap  the plan for only a query which is fundamentally same i.e. you should get the desirable plan by adding hints, modifying  an optimizer setting, playing around with statistics etc. and
then associate sub-optimally performing statement with the optimal plan.


PROCEDURE-2) Generate SQL Plan Baseline for the Original SQL
In an 11g database, by default Oracle does not collect SQL plan baselines automatically unless you set init.ora parameter optimizer_capture_sql_plan_baseline to TRUE. So if the plan baseline does not exist for the original SQL statement, we need to generate it.

1) Create a SQL tuning set. Give it a name and description that suit your situation.

BEGIN
DBMS_SQLTUNE.CREATE_SQLSET(
sqlset_name => '0u676p5cvfxz4_tuning_set',
description =>  ‘Shadow Process’);
END;
/
2) If the SQL statement was run recently, get the starting and ending AWR snapshot numbers for the time period when the SQL was run. Also using the SQL ID, get the plan hash value from DBA_HIST_SQLSTAT and DBA_HIST_SNAPSHOT views。

3) Load the tuning set with the execution plan extracted from AWR, using the AWR snapshot numbers, the SQL_ID and the plan hash value.

DECLARE
cur SYS_REFCURSOR;
BEGIN
OPEN cur FOR
SELECT value(p)
FROM TABLE(DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY(
           begin_snap => 469,
           end_snap => 472,
           basic_filter => 'sql_id = ''0u676p5cvfxz4''
                            and plan_hash_value = 341203176')
           ) p;
DBMS_SQLTUNE.LOAD_SQLSET('0u676p5cvfxz4_tuning_set', cur);
CLOSE cur;
END;
/
4) Create SQL plan baseline from the loaded SQL tuning set

DECLARE
my_plans PLS_INTEGER;
BEGIN
my_plans := DBMS_SPM.LOAD_PLANS_FROM_SQLSET( sqlset_name =>'0u676p5cvfxz4_tuning_set');
END;
/
5) Check the newly created plan baseline

select  sql_handle, plan_name, origin, enabled, accepted, fixed, sql_text, created, last_executed
from dba_sql_plan_baselines
where created > sysdate -1/24
order by sql_handle, plan_name;
SQL_HANDLE                PLAN_NAME                      ORIGIN         ENA ACC FIX CREATED
------------------------- ------------------------------ -------------- --- --- --- -------------------
SQL_ef88a476fc38c5af      SQL_PLAN_fz254fvy3jjdgc4138c40 MANUAL-LOAD    YES YES NO  13-MAY-16 10.57.36.000000 AM
Modify SQL Statement and Generate Its Plan Baseline

1) Add USE_INVISIBLE_INDEX hint to the original SQL statement.

2) Change session parameter to catch plan baseline automatically

ALTER SESSION SET optimizer_capture_sql_plan_baselines = TRUE;
3) Catch SQL plan baseline of the modified SQL by running it twice.

4) Check the plan baseline to make sure it is caught.

select  sql_handle, plan_name, origin, enabled, accepted, fixed, sql_text, created, last_executed
from dba_sql_plan_baselines
where created > sysdate -1/24
order by sql_handle, plan_name;
SQL_HANDLE                PLAN_NAME                      ORIGIN         ENA ACC FIX CREATED
------------------------- ------------------------------ -------------- --- --- --- -------------------
SQL_a7ac813cbf25e65f      SQL_PLAN_agb417kzkbtkz479e6372 AUTO-CAPTURE   YES YES NO  13-MAY-16 11.06.22.000000 AM

SQL_ef88a476fc38c5af      SQL_PLAN_fz254fvy3jjdgc4138c40 MANUAL-LOAD    YES YES NO  13-MAY-16 10.57.36.000000 AM
5) Get SQL_ID of the modified SQL

select distinct sql_id, plan_hash_value, sql_text
from v$sql
where sql_text like ‘%USE_INVISIBLE_INDEX%';
SQL_ID        PLAN_HASH_VALUE SQL_TEXT
------------- --------------- -------------------------------------------------------------------------

dtj3d4das6a9a       544808499 SELECT /*+ USE_INVISIBLE_INDEXES INDEX (OKLS IDX_COLL_OKL_S_01) USE_NL ( XICO OKLS )...
Create an Accepted Plan Baseline for the Original SQL Using that of Modified SQL
Now we have two newly created SQL plan baselines, one for the original SQL statement and the other for the modified SQL with hint. And we know the performance of the later is much better than the former. So we want Oracle to use the execution plan from the SQL with hint (modified SQL) when the original SQL is run from the application. To achieve this, we need to create a new SQL plan baseline for the original SQL and make it ACCEPTED. Following PL/SQL block will do the task. Here SQL_ID and PLAN_HASH_VALUE are from modified SQL statement. PLAN_HANDLE is the one of the original SQL, into which the plan baseline should be implemented. Note here we also make this plan baseline FIXED, meaning the optimizer will give preference to it over non-FIXED plans.

set serveroutput on
DECLARE
v_cnt PLS_INTEGER;
BEGIN
v_cnt := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE (
SQL_ID => 'dtj3d4das6a9a ',
PLAN_HASH_VALUE => 544808499,
SQL_HANDLE => ' SQL_ef88a476fc38c5af ',
FIXED => 'YES',
ENABLED => 'YES');
DBMS_OUTPUT.PUT_LINE ('Plan loaded: '||v_cnt);
END;
/
Now check the SQL plan baselines again to verify a new baseline is indeed created for the original SQL.

SQL_HANDLE                PLAN_NAME                      ORIGIN         ENA ACC FIX CREATED
------------------------- ------------------------------ -------------- --- --- --- -------------------
SQL_a7ac813cbf25e65f      SQL_PLAN_agb417kzkbtkz479e6372 AUTO-CAPTURE   YES YES NO  13-MAY-16 11.06.22.000000 AM

SQL_ef88a476fc38c5af      SQL_PLAN_fz254fvy3jjdg479e6372 MANUAL-LOAD    YES YES YES 13-MAY-16 11.15.02.000000 AM
                          SQL_PLAN_fz254fvy3jjdgc4138c40 MANUAL-LOAD    YES YES NO  13-MAY-16 10.57.36.000000 AM
Run explain plan for the original SQL statement, you should see following line included in the result:

- SQL plan baseline "SQL_PLAN_fz254fvy3jjdg479e6372" used for this statement
Summary
By using SQL Plan Baseline we can force the optimizer to use the execution plan for a modified SQL (added hint).

Configure DB Console

PreReq:

Set environments (with example values):

export ORACLE_HOME=/oracle/11.2.0.4

export ORACLE_SID=orcl

export ORACLE_HOSTNAME=dbserver

Create the repository and configure the DB Console:

% emca -config dbcontrol db -repos create

parameters:

- database SID

- listener's port

- password for SYS

- password for SYSMAN

- password for DBSNMP

Drop the repository and deconfig the DB Console:

% emca -deconfig dbcontrol db -repos drop

parameters:

- database SID

- listener's port

- password for SYS

- password for SYSMAN

Drop the DB Console (manually):

Warning: this command puts the database in Quiesce Mode for the DB Control Releases 10.x to 11.1.x. 

Starting with DB Control Release 11.2.x, the database is no longer put in quiesce mode.

SQL> conn / as sysdba 

SQL> drop user sysman cascade;

SQL> drop role MGMT_USER;

SQL> drop user MGMT_VIEW cascade;

SQL> drop public synonym MGMT_TARGET_BLACKOUTS;

SQL> drop public synonym SETEMVIEWUSERCONTEXT;

% emca -deconfig dbcontrol db -repos drop

Link for using DBConsole: 

https://servername:port/em