Tuesday, May 23, 2017

How to load sql plan baseline from AWR

1. Find out the SQL ID and Number of SQL Plan exist in AWR History.

SELECT SQL_ID, COUNT (*)
    FROM (SELECT DISTINCT SQL_ID, PLAN_HASH_VALUE FROM DBA_HIST_SQL_PLAN
    WHERE SQL_ID='&SQL_ID')
GROUP BY SQL_ID
ORDER BY 2 DESC;



2. Find out the SQL Plan Hash Value using SQL ID.

SELECT DISTINCT PLAN_HASH_VALUE,SQL_ID  FROM DBA_HIST_SQLSTAT
WHERE SQL_ID='&SQL_ID';

3. Check the SQL Execution Plan Cost and choose the best one. Note that you have to select Hash Vale and SNAP_ID for which you want to load into your Plan Baseline.

SELECT SS.SNAP_ID,
     SS.INSTANCE_NUMBER,
     BEGIN_INTERVAL_TIME,
     SQL_ID,
     PLAN_HASH_VALUE,OPTIMIZER_COST,
     DISK_READS_TOTAL,
     BUFFER_GETS_TOTAL,
     ROWS_PROCESSED_TOTAL,
     CPU_TIME_TOTAL,
     ELAPSED_TIME_TOTAL,
     IOWAIT_TOTAL,
     NVL (EXECUTIONS_DELTA, 0) EXECS,
       (  ELAPSED_TIME_DELTA
        / DECODE (NVL (EXECUTIONS_DELTA, 0), 0, 1, EXECUTIONS_DELTA))
     / 1000000
        AVG_ETIME,
     (  BUFFER_GETS_DELTA
      / DECODE (NVL (BUFFER_GETS_DELTA, 0), 0, 1, EXECUTIONS_DELTA))
        AVG_LIO
FROM DBA_HIST_SQLSTAT S, DBA_HIST_SNAPSHOT SS
WHERE     SQL_ID = '&SQL_ID'
     AND SS.SNAP_ID = S.SNAP_ID
     AND SS.INSTANCE_NUMBER = S.INSTANCE_NUMBER
     AND EXECUTIONS_DELTA > 0
ORDER BY 1, 2, 3;


4. Check that STS has already existed or not in history if it is exist chose a different name of drop the existing one.

SELECT * FROM TABLE(DBMS_SQLTUNE.SELECT_SQLSET(sqlset_name => 'STS_f9948h4y2aa22'));

BEGIN
   DBMS_SQLTUNE.DROP_SQLSET (sqlset_name => 'STS_f9948h4y2aa22');
END;


5. Create STS.

BEGIN
  DBMS_SQLTUNE.CREATE_SQLSET(
    sqlset_name => 'STS_f9948h4y2aa22',
    description => 'SQL Tuning Set for loading plan into SQL Plan Baseline');
END;

6. Load STS 

DECLARE
  cur sys_refcursor;
BEGIN
  OPEN cur FOR
    SELECT VALUE(P)
    FROM TABLE(
       dbms_sqltune.select_workload_repository(begin_snap=>6450, end_snap=>6451,basic_filter=>'sql_id = ''f9948h4y2aa22''',attribute_list=>'ALL')
              ) p;
     DBMS_SQLTUNE.LOAD_SQLSET( sqlset_name=> 'STS_f9948h4y2aa22', populate_cursor=>cur);
  CLOSE cur;
END;

7. Check the loaded Plan details.

SELECT
  first_load_time          ,
  executions as execs              ,
  parsing_schema_name      ,
  elapsed_time  / 1000000 as elapsed_time_secs  ,
  cpu_time / 1000000 as cpu_time_secs           ,
  buffer_gets              ,
  disk_reads               ,
  direct_writes            ,
  rows_processed           ,
  fetches                  ,
  optimizer_cost           ,
  sql_plan                ,
  plan_hash_value          ,
  sql_id                   ,
  sql_text
   FROM TABLE(DBMS_SQLTUNE.SELECT_SQLSET(sqlset_name => 'STS_f9948h4y2aa22')
             );


8. In final Stage Load SQL Plan from STS (AWR History) using plan Hash Value.

DECLARE
my_plans pls_integer;
BEGIN
  my_plans := DBMS_SPM.LOAD_PLANS_FROM_SQLSET(
    sqlset_name => 'STS_f9948h4y2aa22', 
    basic_filter=>'plan_hash_value = ''3166698470'''
    );
END;

9. Check your loaded plan from baseline.

SELECT * FROM dba_sql_plan_baselines ;


Sunday, May 14, 2017

Resolve Standby Gaps by Applying Archive log File

Description: In my database archive log file are deleted from the primary database after taken RMAN backup. In physical standby database, there is log gap which is already deleted from the primary database and MRP process is waiting for the log file.

Solution:
1. Find out the backup archive log file.
list backup of archivelog sequence 8760;
2. Transfer backup archive log file into Standby database.
scp 
3. Restore archive log file using log sequence number.
restore archivelog from sequence 8760 until sequence 8760 thread=1;

Checking in Standby Database for find out the archive log file details.

SQL> SELECT PROCESS, PID, STATUS, CLIENT_PROCESS, CLIENT_PID, RESETLOG_ID,
 THREAD#, SEQUENCE#, BLOCK#, BLOCKS, DELAY_MINS
 FROM V$MANAGED_STANDBY;

PROCESS          PID STATUS       CLIENT_P CLIENT_PID    RESETLOG_ID    THREAD#  SEQUENCE#     BLOCK#     BLOCKS DELAY_MINS
--------- ---------- ------------ -------- ------------- ----------- ---------- ---------- ---------- ---------- ----------
ARCH         5636392 CLOSING      ARCH     5636392         928567359          2       9357      75776        317          0
ARCH         2097262 CLOSING      ARCH     2097262         928567359          1       9461     552960        541          0
ARCH         8061076 CLOSING      ARCH     8061076         928567359          2       9359     364544       1996          0
ARCH         4390984 CONNECTED    ARCH     4390984                 0          0          0          0          0          0
MRP0         8323150 WAIT_FOR_GAP N/A      N/A             928567359          1       8760          0          0          0
RFS          7536982 IDLE         ARCH     8257884                 0          0          0          0          0          0
RFS          2818346 IDLE         ARCH     10682474                0          0          0          0          0          0
RFS          6356994 IDLE         LGWR     14876892        928567359          2       9360     461397          2          0
RFS          9175274 IDLE         LGWR     8061206         928567359          1       9462    1506619         12          0
RFS         10289552 IDLE         UNKNOWN  10223730                0          0          0          0          0          0

10 rows selected.

SQL> SELECT NAME
  FROM V$ARCHIVED_LOG
 WHERE THREAD# = 1
AND DEST_ID = 1
AND SEQUENCE# BETWEEN 8760 AND 8760;  2    3    4    5

no rows selected

Checking in primary database is there any archive log file exist or not if it does not then find the backup file and transfer into the standby database.

SQL> SELECT NAME, STANDBY_DEST, DELETED
  FROM GV$ARCHIVED_LOG
 WHERE THREAD# = 1
 AND DEST_ID = 1
 AND SEQUENCE# BETWEEN 8760 AND 8760;  2    3    4    5

NAME                                                         STA DEL
------------------------------------------------------------ --- ---
                                                             NO  YES
                                                             NO  YES

SQL>

Find out the archive log file backup location

bash-4.3$ rman target/

Recovery Manager: Release 11.2.0.4.0 - Production on Sun May 14 12:54:18 2017

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ******* (DBID=******* )

RMAN> list backup of archivelog sequence 8760 thread 1;

using target database control file instead of recovery catalog

List of Backup Sets
===================


BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
3308    3.22G      DISK        00:06:39     09-MAY-17
        BP Key: 3308   Status: AVAILABLE  Compressed: YES  Tag: ORCL_ARCHIVE
        Piece Name: /rmanbackup/RMAN_ARC_inc_******* _943495084_3496_1_d8s3p5tc_1_1.arch

  List of Archived Logs in backup set 3308
  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
  ---- ------- ---------- --------- ---------- ---------
  1    8760    12151347030306 08-MAY-17 12151347165919 08-MAY-17

BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
3323    13.44G     SBT_TAPE    00:05:57     09-MAY-17
        BP Key: 3323   Status: AVAILABLE  Compressed: NO  Tag: TAG20170509T051719
        Handle: dks3piuo_1_1   Media: 2851

  List of Archived Logs in backup set 3323
  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
  ---- ------- ---------- --------- ---------- ---------
  1    8760    12151347030306 08-MAY-17 12151347165919 08-MAY-17

RMAN> exit


Recovery Manager complete.
bash-4.3$

Transfer into standby database 

scp /rmanbackup/RMAN_ARC_inc_******* _943495084_3496_1_d8s3p5tc_1_1.arch oracle@****:/rman/RMAN_ARC_inc_******* _943495084_3496_1_d8s3p5tc_1_1.arch

Connect RMAN from Standby database and restore archive log file 

bash-4.3$ rman target/

Recovery Manager: Release 11.2.0.4.0 - Production on Sun May 14 13:36:45 2017

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ***** (DBID=******* )

RMAN> catalog backuppiece '/rman/RMAN_ARC_inc_******* _943495084_3496_1_d8s3p5tc_1_1.arch';

cataloged backup piece
backup piece handle=/rman/RMAN_ARC_inc_******* _943495084_3496_1_d8s3p5tc_1_1.arch RECID=170 STAMP=943969336

RMAN> restore archivelog from sequence 8760 until sequence 8760 thread=1;

Starting restore at 14-MAY-17
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=6114 device type=DISK

channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=8760
channel ORA_DISK_1: reading from backup piece /rman/RMAN_ARC_inc_******* _943495084_3496_1_d8s3p5tc_1_1.arch
channel ORA_DISK_1: piece handle=/rman/RMAN_ARC_inc_******* _943495084_3496_1_d8s3p5tc_1_1.arch tag=ORCL_ARCHIVE
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:08:05
Finished restore at 14-MAY-17

RMAN> exit


Recovery Manager complete.

Now Standby database will start applying the archive log file. If it is not started just stop and start recovery of the standby database.

Checking the applying start or not.


SQL> SELECT NAME
  FROM V$ARCHIVED_LOG
 WHERE THREAD# = 1
AND DEST_ID = 1
AND SEQUENCE# BETWEEN 8760 AND 8760;  2    3    4    5

NAME
--------------------------------------------------------------------------------
+FRA/****/archivelog/2017_05_14/thread_1_seq_8760.2508.943969813

SQL>
SQL> SELECT PROCESS, PID, STATUS, CLIENT_PROCESS, CLIENT_PID, RESETLOG_ID,
 THREAD#, SEQUENCE#, BLOCK#, BLOCKS, DELAY_MINS
 FROM V$MANAGED_STANDBY;   2    3

PROCESS          PID STATUS       CLIENT_P CLIENT_PID                               RESETLOG_ID    THREAD#  SEQUENCE#     BLOCK#     BLOCKS DELAY_MINS
--------- ---------- ------------ -------- ---------------------------------------- ----------- ---------- ---------- ---------- ---------- ----------
ARCH         5636392 CLOSING      ARCH     5636392                                    928567359          2       9282     129024        828          0
ARCH         2097262 CLOSING      ARCH     2097262                                    928567359          1       9462    1767424       1537          0
ARCH         8061076 CLOSING      ARCH     8061076                                    928567359          2       9359     364544       1996          0
ARCH         4390984 CONNECTED    ARCH     4390984                                            0          0          0          0          0          0
MRP0         8323150 APPLYING_LOG N/A      N/A                                        928567359          1       8799      12876     944628          0
RFS          7536982 RECEIVING    UNKNOWN  8257884                                    928567359          1       8941      32769       2048          0
RFS         10355038 IDLE         ARCH     10682474                                           0          0          0          0          0          0
RFS          8454490 IDLE         LGWR     14876892                                   928567359          2       9362     442233          4          0
RFS          9175274 IDLE         LGWR     8061206                                    928567359          1       9463    1334595          5          0
RFS         10289552 IDLE         UNKNOWN  10223730                                           0          0          0          0          0          0
RFS          2818374 RECEIVING    ARCH     14877160                                           0          0          0          0          0          0

PROCESS          PID STATUS       CLIENT_P CLIENT_PID                               RESETLOG_ID    THREAD#  SEQUENCE#     BLOCK#     BLOCKS DELAY_MINS
--------- ---------- ------------ -------- ---------------------------------------- ----------- ---------- ---------- ---------- ---------- ----------
RFS          6095152 RECEIVING    UNKNOWN  29163686                                   928567359          2       9296     761858       2048          0
RFS          7733286 RECEIVING    UNKNOWN  10092786                                   928567359          1       8940      49153       2048          0

13 rows selected.

SQL>