Wednesday, July 26, 2017

Hanging ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL

Cause: Archive log are not applying into standby database but recovery process (MRP) are active, in this situation I decided to restart recovery process but whenever I am applying  ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL, the command itself hanging situation.

Solution: Rather than bouncing the standby database just find and kill the MRP process and start the media recovery process.



SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

Database altered.

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION
*
ERROR at line 1:
ORA-01153: an incompatible media recovery is active


SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

Database altered.

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;

Database altered.

SQL>

Log Details:

Archived Log entry 20997 added for thread 1 sequence 11504 ID 0xffffffffaec2031b dest 1:
Thu Jul 27 10:33:23 2017
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL
Thu Jul 27 10:45:35 2017
Managed Standby Recovery Canceled (erpdb)
Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL
Thu Jul 27 10:46:23 2017
Recovery coordinator died, shutting down parallel recovery
Thu Jul 27 10:46:25 2017
RFS[516]: Selected log 13 for thread 1 sequence 11506 dbid -********** branch 928567359
Thu Jul 27 10:46:28 2017
Archived Log entry 20998 added for thread 1 sequence 11505 ID 0xffffffffaec2031b dest 1:
Thu Jul 27 10:46:41 2017
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION
ORA-1153 signalled during: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION...
Thu Jul 27 10:46:59 2017
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL
Thu Jul 27 10:47:24 2017
RFS[514]: Selected log 21 for thread 2 sequence 11340 dbid -********** branch 928567359
Thu Jul 27 10:47:27 2017
Archived Log entry 20999 added for thread 2 sequence 11339 ID 0xffffffffaec2031b dest 1:
Thu Jul 27 10:47:35 2017
Logmerger died, shutting down parallel recovery slaves
Recovery interrupted!
Recovery interrupted!
Thu Jul 27 10:47:37 2017
Managed Standby Recovery Canceled (erpdb)
Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL
Thu Jul 27 10:48:08 2017
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION
Attempt to start background Managed Standby Recovery process (erpdb)
Thu Jul 27 10:48:08 2017
MRP0 started with pid=38, OS id=9764900
MRP0: Background Managed Standby Recovery process started (erpdb)
 started logmerger process
Thu Jul 27 10:48:14 2017
Managed Standby Recovery starting Real Time Apply
Parallel Media Recovery started with 32 slaves
Waiting for all non-current ORLs to be archived...
All non-current ORLs have been archived.
Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION
Media Recovery Log +DG_FRA/erpdb/archivelog/2017_07_25/thread_2_seq_11299.1849.950272469
Media Recovery Log +DG_FRA/erpdb/archivelog/2017_07_25/thread_1_seq_11451.1854.950272385

Monday, June 5, 2017

MRP0: Background Media Recovery process shutdown

Errors with log +FRA/erpdrdb/archivelog/2017_06_02/thread_2_seq_9952.2503.945625685
MRP0: Background Media Recovery terminated with error 10485
Errors in file /u01/app/oracle/diag/rdbms/erpdrdb/erpdrdb/trace/erpdrdb_pr00_9109800.trc:
ORA-10485: Real-Time Query cannot be enabled while applying migration redo.
Recovery interrupted!
MRP0: Background Media Recovery process shutdown (erpdrdb)


Cause: After starting the database as upgrade mode you have to disconnect the recovery database.

Solution: Disconnect the recovery database, shutdown, startup and start the recovery process.

SQL> alter database recover managed standby database disconnect ;

Database altered.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area 6.3069E+10 bytes
Fixed Size                  2393728 bytes
Variable Size            8388610432 bytes
Database Buffers         5.4660E+10 bytes
Redo Buffers               18116608 bytes
Database mounted.
SQL> alter database recover managed standby database using current logfile disconnect ;

Database altered.

SQL>

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 ;