Oracle maintenance window -- Change scheduled Window time

 

As most of us are  aware Oracle  default maintenance window starts from 10pm server time for weekdays and   at 6am  for Weekends .   There  are situations where this time clashes with Application  jobs and we need to change default maintenance window time .

Below is step we followed to change window time for all days .
 

What Happens During maintenance Widow : 

In oracle Database 10g, we had only the first two automatic maintenance tasks mentioned below. With Oracle 11g, we have a third task name  “Automatic SQL Tunning Advisor”

Automatic Optimizer Statistics Collection:

Collects optimizer statistics for all schema objects in the database for which there are no statistics or only stale statistics. The statistics gathered by this task are used by the SQL query optimizer to improve the performance of SQL execution.
 
Automatic Segment Advisor:
Identifies segments that have space available for reclamation, and makes recommendations on how to de-fragment those segments.
 
Automatic SQL Tuning Advisor :
Examines the performance of high-load SQL statements, and makes recommendations on how to tune those statements.

To view the task, run the below SQL

 SQL> select client_name from DBA_AUTOTASK_CLIENT;

 
CLIENT_NAME
----------------------------------------------------------------
auto optimizer stats collection
auto space advisor
sql tuning advisor
 
 SELECT client_name, status, consumer_group, window_group
FROM dba_autotask_client
ORDER BY client_name;

 SELECT client_name, status FROM dba_autotask_operation;
CLIENT_NAME                                                      STATUS  
---------------------------------------------------------------- --------
auto optimizer stats collection                                  ENABLED  
auto space advisor                                                    ENABLED  
sql tuning advisor                                                     ENABLED  

Check existing window  timing .

set line 400
col window_name format a20 
select window_name,window_next_time from DBA_AUTOTASK_WINDOW_CLIENTS;
select window_name, REPEAT_INTERVAL, DURATION, NEXT_START_DATE from DBA_SCHEDULER_WINDOWS; 
 

Change  maintenance window  schedule as below  to  2pm    Server  time 

SQL> select window_name,window_next_time from DBA_AUTOTASK_WINDOW_CLIENTS;

WINDOW_NAME                    WINDOW_NEXT_TIME
------------------------------ --------------------------------------
MONDAY_WINDOW                  14-MAY-12 10.00.00.000000 PM EST5EDT
TUESDAY_WINDOW                 08-MAY-12 10.00.00.000000 PM EST5EDT
WEDNESDAY_WINDOW               09-MAY-12 10.00.00.000000 PM EST5EDT
THURSDAY_WINDOW                10-MAY-12 10.00.00.000000 PM EST5EDT
FRIDAY_WINDOW                  11-MAY-12 10.00.00.000000 PM EST5EDT
SATURDAY_WINDOW                12-MAY-12 06.00.00.000000 AM EST5EDT
SUNDAY_WINDOW                  13-MAY-12 06.00.00.000000 AM EST5EDT
TEST_WINDOW                    09-MAY-12 05.00.00.000000 AM EST5EDT

BEGIN
DBMS_SCHEDULER.set_attribute (
name => 'MONDAY_WINDOW',
attribute => 'repeat_interval',
VALUE => 'freq=daily;byday=MON;byhour=14;byminute=0;bysecond=0');
END;
/
BEGIN
DBMS_SCHEDULER.set_attribute (
name => 'TUESDAY_WINDOW',
attribute => 'repeat_interval',
VALUE => 'freq=daily;byday=TUE;byhour=14;byminute=0;bysecond=0');
END;
/

BEGIN
DBMS_SCHEDULER.set_attribute (
name => 'WEDNESDAY_WINDOW',
attribute => 'repeat_interval',
VALUE => 'freq=daily;byday=WED;byhour=14;byminute=0;bysecond=0');
END;
/



BEGIN
DBMS_SCHEDULER.set_attribute (
name => 'THURSDAY_WINDOW',
attribute => 'repeat_interval',
VALUE => 'freq=daily;byday=THU;byhour=14;byminute=0;bysecond=0');
END;
/


BEGIN
DBMS_SCHEDULER.set_attribute (
name => 'FRIDAY_WINDOW',
attribute => 'repeat_interval',
VALUE => 'freq=daily;byday=FRI;byhour=14;byminute=0;bysecond=0');
END;
/


BEGIN
DBMS_SCHEDULER.set_attribute (
name => 'SATURDAY_WINDOW',
attribute => 'repeat_interval',
VALUE => 'freq=daily;byday=SAT;byhour=14;byminute=0;bysecond=0');
END;
/


BEGIN
DBMS_SCHEDULER.set_attribute (
name => 'SUNDAY_WINDOW',
attribute => 'repeat_interval',
VALUE => 'freq=daily;byday=SUN;byhour=14;byminute=0;bysecond=0');
END;
/

Best practice is  that you must use the DBMS_SCHEDULER.DISABLE subprogram to disable the window before making changes to it, and then re-enable the window with DBMS_SCHEDULER.ENABLE when you are finished. If you change a window when it is currently open, the change does not take effect until the next time the window opens.

BEGIN 

dbms_scheduler.disable( name => 'MONDAY_WINDOW');

dbms_scheduler.set_attribute( name => 'MONDAY_WINDOW', 

attribute => 'DURATION',

 value => numtodsinterval(3, 'hour')); 

dbms_scheduler.enable( name => 'MONDAY_WINDOW');

END;

See if there is a window currently open. If there is, you can try closing it to see if that helps .

select * from DBA_SCHEDULER_GLOBAL_ATTRIBUTE where attribute_name='CURRENT_OPEN_WINDOW';

exec DBMS_SCHEDULER.close_window ('WEEKNIGHT_WINDOW');

Creating the new Maintenance Window
 
To create a maintenance window, you must create a Scheduler window and then add it to the window group MAINTENANCE_WINDOW_GROUP
 
 
The DBMS_SCHEDULER PL/SQL package provides the ADD_WINDOW_GROUP_MEMBER subprogram, which adds a window to a window group
 
 BEGIN
dbms_scheduler.create_window(
    window_name     => 'TEST_WINDOW',
    duration        =>  numtodsinterval(1, 'hour'),
    resource_plan   => 'DEFAULT_MAINTENANCE_PLAN',
    repeat_interval => 'FREQ=DAILY;BYHOUR=5;BYMINUTE=0;BYSECOND=0');
    dbms_scheduler.add_window_group_member(
    group_name  => 'MAINTENANCE_WINDOW_GROUP',
    window_list => 'TEST_WINDOW');
END;

Change Run Duration of Window : 

In case we want to control window how long 

EXEC DBMS_SCHEDULER.SET_ATTRIBUTE('WEEKNIGHT_WINDOW', 'duration', '+000 06:00:00');

SELECT *

FROM USER_SCHEDULER_JOB_RUN_DETAILS 

ORDER BY LOG_DATE DESC  ;

How to configure (disable/enable) the Automated Maintenance Tasks
 
DBMS_AUTO_TASK_ADMIN PL/SQL package can be used to do the following.
  
To enable the task:
 
BEGIN
dbms_auto_task_admin.disable(
    client_name => 'sql tuning advisor',
    operation   => NULL,
    window_name => NULL);
END;
 
 
To enable it
 
BEGIN
dbms_auto_task_admin.enable(
    client_name => 'sql tuning advisor',
    operation   => NULL,
    window_name => NULL);
END;

Reference : 

How to Change The Automated Maintenance Tasks Weekend Schedule (Doc ID 2036557.1)

Oracle Database Services - Types and Relation with load balancing advisor

Basically what figured out database load balancing advisor and its what is hort and long service .  Hence  thought of documenting  it .

In this  Blog we will try to cover  below points

1) How To Create and managed services 

2)  Services and load balancing advisor 

3) Short and Long services .

4)  run time load balancing 

Create and Manage  services : 

srvctl add service -d PWIC -s CUHISTORY_SRVC -r PWIC1 -a PWIC2,PWIC3,PWIC4

srvctl add service -d QWIC -s CUHISTORY_SRVC -r QWIC1 -a QWIC2

srvctl add service -d db_unique_name -s service_name -r preferred_list [-a available_list]

srvctl config database -d ORCL

srvctl status service -d CCIHC

srvctl modify service -d PRDBC -s BATCHRPT_SRVC -n -i PRDBC2,PRDBC3 -a PRDBC

srvctl relocate service -d PRDBC -s BATCHRPT_SRVC -i PRDBC4 -t PRDBC2

crsctl relocate resource resource_name –n node_name

srvctl start service -d QGISC -s ora.QGISC.INS_FGEA.QGISC2.srv

srvctl start service -d PRDBC -s BATCHRPT_SRVC -i PRDBC3

srvctl start service –d pmpic –s pmpic_gen

srvctl stop service -d QGISC -s ora.QGISC.INS_FGEA.QGISC2.srv

srvctl stop service –d pmpic –s pmpic_gen

 
crsctl stop resource ora.QGISC.INS_WFC.QGISC2.srv       
crsctl stop resource ora.QGISC.QGISC_INS_FGEA.QGISC2.srv
 
 
 
SELECT username, program, machine, service_name FROM gv$session;
 
select name,network_name,enabled , failover_method,failover_type ,failover_retries
,failover_delay from dba_services ;
 
select name,network_name,enabled ,goal,min_cardinality,max_cardinality  from dba_services ;
 
select inst_id, name,network_name,blocked from gv$active_services ;
 
select service_id, name , goal from v$active_services;
 
show parameter service_name
 
 
SELECT sid, serial#,
            client_identifier, service_name, action, module
       FROM V$SESSION
 
 
select inst_id,service_id,name,network_name
from gv$services
order by 1,2;
pause
select inst_id,service_id,name,network_name
from gv$active_services
order by 1,2;
 
 
col name for a16
col network_name for a16
col failover_method for a16
col failover_type for a16
col service_name for a20
col stat_name for a35
pause see all services that are defined
select service_id,name,network_name,creation_date,
failover_type,failover_method
from dba_services;
 
select service_name,stat_name,value
from v$service_stats
order by service_name
/
  
select INST_ID,VALUE from gv$parameter  where NAME='service_names';
 
alter system set service_names='CFEED_SRVC, BATCHRPT_SRVC,PRDBC.envoy.net, DBR_SRVC'
scope=memory sid='PRDBC2';
 
select inst_id, username, program , logon_time from gv$session where service_name =
'SLICER_SRVC' order by inst_id ;
 
lsnrctl services > ab.log
lsnrctl services | grep service2
 
srvctl status service -d PRDBC
srvctl config service -d PRDBC
srvctl modify service -d PRDBC -s BATCHRPT_SRVC -n -i PRDBC2,PRDBC3 -a PRDBC4
srvctl relocate service -d PRDBC -s BATCHRPT_SRVC -i PRDBC4 -t PRDBC2
srvctl start service -d PRDBC -s BATCHRPT_SRVC -i PRDBC3

Services and Load Balancing Advisor : 

Load balancing distributes work across all of the available Oracle RAC database instances. Oracle recommends that applications use connection pools with persistent connections that span the instances that offer a particular service. When using persistent connections, connections are created infrequently and exist for a long duration. Work comes into the system with high frequency, borrows these connections, and exists for a relatively short duration. The load balancing advisory provides advice about how to direct incoming work to the instances that provide the optimal quality of service for that work. This minimizes the need to relocate the work later.

By using the Load Balancing Advisory and run-time connection load balancing goals, feedback is built in to the system. Work is routed to provide the best service times globally, and routing responds gracefully to changing system conditions. In a steady state, the system approaches equilibrium with improved throughput across all of the Oracle RAC instances.

Standard architectures that can use the load balancing advisory include connection load balancing, transaction processing monitors, application servers, connection concentrators, hardware and software load balancers, job schedulers, batch schedulers, and message queuing systems. All of these applications can allocate work.

The load balancing advisory is deployed with key Oracle clients, such as a listener, the JDBC universal connection pool, OCI session pool, Oracle WebLogic Server Active GridLink for Oracle RAC, and the ODP.NET Connection Pools. Third-party applications can also subscribe to load balancing advisory events by using JDBC and Oracle RAC FAN API or by using callbacks with OCI.

The load balancing advisory has the task of advising the direction of incoming work to the RAC instances that provide optimal quality of service for that work.

To test the load balancing, you can use the scripts in the appendix to apply load and the following section for monitoring.

-- view load balancing gaol of a current service

-- NONE means load balancing advisory is disabled

SELECT NAME, GOAL, CLB_GOAL FROM DBA_SERVICES;

-- SERVICE TIME: response-time based, like online shopping 

Page 481 Oracle DBA Code Examples

execute dbms_service.modify_service (service_name => 'hrserv' -

 , goal => dbms_service.goal_service_time -

 , clb_goal => dbms_service.clb_goal_short);

-- THROUGHPUT: rat at which jobs are completed, like batch processing

execute dbms_service.modify_service (service_name => 'hrserv' -

 , goal => dbms_service.goal_throughput -

 , clb_goal => dbms_service.clb_goal_long);

-- NONE: LBA disabled

execute dbms_service.modify_service (service_name => 'hrserv' -

 , goal => dbms_service.goal_none -

 , clb_goal => dbms_service.clb_goal_long); 

Monitoring Load Balancing Advisory

V$SERVICEMETRIC : service metrics on the most 5-second and 1-minute intervals

V$SERVICEMETRIC_HISTORY : recent history of the metric values

SELECT

 service_name

 ,TO_CHAR(begin_time,'hh24:mi:ss') beg_hist

 ,TO_CHAR(end_time,'hh24:mi:ss') end_hist

 ,inst_id

 ,goodness

 ,delta

 ,flags

 ,cpupercall

 ,dbtimepercall

 ,callspersec

 ,dbtimepersec

 FROM gv$servicemetric

 WHERE service_name ='hrserv'

 ORDER BY service_name, begin_time DESC, inst_id;

SELECT SERVICE_NAME,

 to_char(BEGIN_TIME,'hh24:mi:ss') BEGIN_TIME,

 to_char(END_TIME,'hh24:mi:ss') END_TIME,

 INTSIZE_CSEC, GROUP_ID "Service Metric Group",

 CPUPERCALL "CPU time per call",

 DBTIMEPERCALL "Elapsed time per call",

 CALLSPERSEC "User Calls/s",

 DBTIMEPERSEC "DB Time/s"

from V$SERVICEMETRIC_HISTORY

order by SERVICE_NAME, BEGIN_TIME desc;

-- aggregated

SELECT

 SERVICE_NAME,

 GROUP_ID "Service Metric Group",

 round(sum(CPUPERCALL),2) "CPU time per call",

 round(sum(DBTIMEPERCALL),2) "Elapsed time per call",

 round(sum(CALLSPERSEC),2) "User Calls/s",

 round(sum(DBTIMEPERSEC),2) "DB Time/s"

from V$SERVICEMETRIC_HISTORY

group by SERVICE_NAME, GROUP_ID

order by SERVICE_NAME; 

Short and Long services : 

For each service, you can define the connection load-balancing goal that you want the listener to use. You can use a goal of either long or short for connection load balancing. These goals have the following characteristics:

Short Connections are distributed across instances based on the amount of time that the service is used. Use the short connection load-balancing goal for applications that have connections of brief duration.

Long Connections are distributed across instances based on the number of sessions in each instance, for each instance that supports the service. Use the long connection load-balancing goal for applications that have connections of long duration.

$ srvctl modify service -db db_unique_name -service oltpapp -clbgoal SHORT

 

$ srvctl modify service -db db_unique_name -service batchconn -clbgoal LONG

select service_name , CLB_GOAL  from all_services;

Run Time Load balancing  : 

Typically, there are two types of load balancing:
 
Connection Load Balancing (CLB)
Run-time Load Balancing (RTLB)
  
RTLB has come into existence from 10gR2 which can be either set to “SERVICE_TIME” or “THROUGHPUT”. CLB can be configured at Client-Side or at Server-Side. Of which, Server-Side load balancing is recommended and have better functionality over Client-Side.
 
When a service is created by default Connection Load Balancing is enabled and set to “LONG” and Run-Time Load Balancing is disabled. Irrespective of Connection Load Balancing Goal “SHORT” or “LONG” you can enable Runtime Load Balancing Goal to “SERVICE_TIME” or “THROUGHPUT”.
 
 With Connection Load Balancing goal set to LONG, do not configure Run-time Load Balancing as it is only applicable to applications where next session or job starts only after the current one ends which is not practical. This is the reason you must have read that Run-time Load Balancing must be enabled with CLB goal set to SHORT.
 
 
 
[oracle@RAC1 ~]$ srvctl modify service -d RACDB -s LBASRV1 -B SERVICE_TIME
[oracle@RAC1 ~]$ srvctl config service -d RACDB -s LBASRV1
 
[oracle@RAC1 ~]$ srvctl modify service -d RACDB -s LBASRV1 -B THROUGHPUT
[oracle@RAC1 ~]$ srvctl config service -d RACDB -s
 
  
Disabling CLB on server-side is equivalent to disabling LBA (Load Balancing Advisory). You will not find an option to do it if you search in “srvctl” help.
Oracle document says that configuring GOAL to NONE will disable Load Balancing Advisory (LBA) on the service. Let us try doing it and see what exactly it is.
 
 
I will try to use DBMS_SERVICE package to modify this service and to disable LBA.
 
SQL> EXECUTE DBMS_SERVICE.MODIFY_SERVICE (service_name => ‘LBASRV1’, goal => DBMS_SERVICE.GOAL_NONE);
 
 
SQL> select GOAL,CLB_GOAL from dba_services where name=’LBASRV1′;
 
GOAL         CLB_G
———— —–
NONE         LONG
 
 
But as soon as we restart the service using srvctl GOAL has come back to THROUGHPUT.
 
[oracle@RAC1 ~]$ srvctl stop service -s LBASRV1 -d RACDB
[oracle@RAC1 ~]$ srvctl start service -s LBASRV1 -d RACDB
 
 
 
SQL> select GOAL,CLB_GOAL from dba_services where name=’LBASRV1′;
 
GOAL             CLB_G
————             —–
THROUGHPUT   LONG
 
 
 select user_data from sys.sys$service_metrics_tab order by enq_time;

Manage service using DBMS_SERVICE for Non Rac 
 

exec DBMS_SERVICE.CREATE_SERVICE('service2','service2');
exec dbms_service.start_service('rdbprod.envoy.net');
exec dbms_service.stop_service('bb', dbms_service.all_instances)
 
 
begin
 dbms_service.create_service (
 service_name =>'ABC' , network_name => 'src_net_name' ,
  failover_type => DBMS_SERVICE.FAILOVER_TYPE_SELECT, -
        failover_retries => 10, -
      failover_delay => 1, -
) ;
dbms_service.start_service('abc',dbms_service.all_instances);
end ;
/
 
 
exec DBMS_SERVICE.MODIFY_SERVICE( -
        service_name => 'o11gr1', -
         goal => DBMS_SERVICE.GOAL_THROUGHPUT, -
    failover_method => DBMS_SERVICE.FAILOVER_METHOD_BASIC, -
       failover_type => DBMS_SERVICE.FAILOVER_TYPE_SELECT, -
        failover_retries => 10, -
      failover_delay => 1, -
    clb_goal => DBMS_SERVICE.CLB_GOAL_LONG);

 
Attaching services to jobs ::
 

-- Create OLTP and BATCH job classes.
BEGIN
  DBMS_SCHEDULER.create_job_class(
    job_class_name => 'OLTP_JOB_CLASS',
    service        => 'OLTP_SERVICE');
 
  DBMS_SCHEDULER.create_job_class(
    job_class_name => 'BATCH_JOB_CLASS',
    service        => 'BATCH_SERVICE');
END;
/
 
-- Make sure the relevant users have access to the job classes.
GRANT EXECUTE ON sys.oltp_job_class TO my_user;
GRANT EXECUTE ON sys.batch_job_class TO my_user;
 
 
$sqlplus ‘/as sysdba’
SQL> select * from DBA_SCHEDULER_JOB_CLASSES;
SQL> exec DBMS_SCHEDULER.CREATE_JOB_CLASS (job_class_name => 'AUDITING_JOB_CLASS', service
=> 'AUDITING_SERVICE', comments => 'THIS IS AUDIT SERVICE JOB CLASS');
PL/SQL procedure successfully completed.
 
SQL> select * from DBA_SCHEDULER_JOB_CLASSES; à Entry should be available now
 
 
-- Create a job associated with a job class.
BEGIN
  DBMS_SCHEDULER.create_job (
    job_name        => 'my_user.oltp_job_test',
    job_type        => 'PLSQL_BLOCK',
    job_action      => 'BEGIN NULL; END;',
    start_date      => SYSTIMESTAMP,
    repeat_interval => 'FREQ=DAILY;',
    job_class       => 'SYS.OLTP_JOB_CLASS',
    end_date        => NULL,
    enabled         => TRUE,
    comments        => 'Job linked to the OLTP_JOB_CLASS.');
END;
/
 
-- Assign a job class to an existing job.
EXEC DBMS_SCHEDULER.set_attribute('MY_BATCH_JOB', 'JOB_CLASS', 'BATCH_JOB_CLASS');
 
 
 

  
Add TAF Service :
 
srvctl status service -d devdb
 
srvctl add service -d devdb -s devdb_oltp -r devdb1,devdb2 -P BASIC
 
srvctl start service -d devdb -s devdb_oltp
 
srvctl status service -d devdb
 
 
set line 130
 
col SERVICE_ID for 999
col NAME for a35
col NETWORK_NAME for a35
col FAILOVER_METHOD for a30
 
select SERVICE_ID, NAME, NETWORK_NAME, FAILOVER_METHOD FROM DBA_SERVICES;

 
Failover  Configuration : 

select failover_type, failover_method, failed_over from v$session where username='SCOTT';

 
exec dbms_service.modify_service( service_name => 'devdb_oltp' , aq_ha_notifications => true, failover_method => dbms_service.failover_method_basic, failover_type => dbms_service.failover_type_select, failover_retries =>180, failover_delay =>5);
 
 
 
exec dbms_service.modify_service( service_name => 'devdb_oltp' -
, aq_ha_notifications => true -
, failover_method => dbms_service.failover_method_basic -
, failover_type => dbms_service.failover_type_select -
, failover_retries =>180 -
, failover_delay =>5 -
)
;
 
 
++++++++++++++
 
++++++++++++++
procedure disconnect_session( service_name in varchar2);
procedure create_service( service_name in varchar2, network_name in varchar2, goal in number, dtp in boolean, aq_ha_notifications in boolean, failover_method in varchar2, failover_type in varchar2, failover_retries in number, failover_delay in number, clb_goal in number);
procedure modify_service( service_name in varchar2, goal in number, dtp in boolean, aq_ha_notifications in boolean, failover_method in varchar2, failover_type in varchar2, failover_retries in number, failover_delay in number, clb_goal in number);
procedure delete_service( service_name in varchar2);
procedure start_service( service_name in varchar2, instance_name in varchar2);
procedure stop_service( service_name in varchar2, instance_name in varchar2);
 
 
connect system/devdb@crm
 
connect system/devdb@devdb_oltp
 
select instance_number instance#, instance_name, host_name, status from v$instance;
 
 
: Remove TAF Service
 
srvctl status service -d devdb
 
srvctl stop service -d devdb -s devdb_oltp
 
srvctl remove service -d devdb -s devdb_oltp
 
srvctl status service -d devdb
 
 
SQL > exec dbms_service.delete_service( service_name => 'devdb_oltp');
 
 
exec dbms_service.modify_service( service_name => 'CRM' , aq_ha_notifications => true, failover_method => dbms_service.failover_method_basic, failover_type => dbms_service.failover_type_select, failover_retries =>180, failover_delay =>5);
 
 

 Using  TNS with Service name : 

 

OLTP =
  (DESCRIPTION =
    (LOAD_BALANCE = ON)
    (FAILOVER = ON)
    (ADDRESS = (PROTOCOL = TCP)(HOST = server01)(PORT = 1521))
    (ADDRESS = (PROTOCOL = TCP)(HOST = server02)(PORT = 1521))
    (ADDRESS = (PROTOCOL = TCP)(HOST = server03)(PORT = 1521))
    (CONNECT_DATA =
      (SERVICE_NAME = OLTP_SERVICE)
      (FAILOVER_MODE =
        (TYPE = SELECT)
        (METHOD = BASIC)
        (RETRIES = 20)
        (DELAY = 1)
      )
    )
  )
 
 
BATCH =
  (DESCRIPTION =
    (LOAD_BALANCE = ON)
    (FAILOVER = ON)
    (ADDRESS = (PROTOCOL = TCP)(HOST = server01)(PORT = 1521))
    (ADDRESS = (PROTOCOL = TCP)(HOST = server02)(PORT = 1521))
    (ADDRESS = (PROTOCOL = TCP)(HOST = server03)(PORT = 1521))
    (CONNECT_DATA =
      (SERVICE_NAME = BATCH_SERVICE)
      (FAILOVER_MODE =
        (TYPE = SELECT)
        (METHOD = BASIC)
        (RETRIES = 20)
        (DELAY = 1)
      )
    )
  ) 

DEVDB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = rac1-vip.hclt.com)(PORT = 1521))
    (ADDRESS = (PROTOCOL = TCP)(HOST = rac2-vip.hclt.com)(PORT = 1521))
    (LOAD_BALANCE = no)
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = devdb_oltp)
    )
  )
 
 
DEVDB_OLTP =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = rac1-vip.hclt.com)(PORT = 1521))
    (ADDRESS = (PROTOCOL = TCP)(HOST = rac2-vip.hclt.com)(PORT = 1521))
    (LOAD_BALANCE = yes)
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = devdb_oltp)
      (FAILOVER_MODE =
        (TYPE = SELECT)
        (METHOD = BASIC)
        (RETRIES = 180)
        (DELAY = 5)
      )
    )
  )

Troubleshooting  Service Connection : 

Note for DBA TEAM: application teams use application specific RAC services to connect to the database.

Following are the tnsname entries they use SS indicates Shared Server and non SS means Dedicated Server.

MARGIN-PRD.world, MARGIN-SS-PRD.world, RISKVIEW-PRD.world,RISKVIEW-SS-PRD.world,

RISKCALC-PRD.world,RISKCALC-SS-PRD.world,MULTIVAL-PRD.world,MULTIVAL-SS-PRD.world,

VARMARGIN-PRD.world,VARMARGIN-SS-PRD.world,CMA-PRD.world,CMA-SS-PRD.world.

 

If application team complains about connection issue to the database first thing ensure

  archive area is not full. Then try connecting using above tnsname entries from global tnsnames.

If you get error like listener currently does not know service specified then follow

below step to start the services :

 

1. check if both the instances are up and running.

2. check all the application specific RAC services are up and running as below : (This command can be run from any node)

 

xstmc021001por(oracle):MFXPRDI1:misc$ $GRID_HOME/bin/srvctl status service -d MFXPRD_xstmc021 -s "MARGIN,RISKVIEW,RISKCALC,MULTIVAL,VARMARGIN,CMA"

Service MARGIN is running on instance(s) MFXPRDI2

Service RISKVIEW is running on instance(s) MFXPRDI1

Service RISKCALC is running on instance(s) MFXPRDI1

Service MULTIVAL is running on instance(s) MFXPRDI1

Service VARMARGIN is running on instance(s) MFXPRDI1

Service CMA is running on instance(s) MFXPRDI1

 

 

3. If not then bring the services on specific instance as per above list.

   Please note MARGIN service runs on MFXPRDI2 and rest all run on MFXPRDI1

eg, to bring up one service as

$GRID_HOME/bin/srvctl start service -d MFXPRD_xstmc021 -s CMA -i MFXPRDI1

 

to bring up all the services in one command

 

$GRID_HOME/bin/srvctl start service -d MFXPRD_xstmc021 -s "CMA,MULTIVAL,RISKCALC,RISKVIEW,VARMARGIN" -i MFXPRDI1

$GRID_HOME/bin/srvctl start service -d MFXPRD_xstmc021 -s MARGIN -i MFXPRDI2

 

4. Wait for sometime and Run listener services from both the nodes and check all the services are registered to listener with   shared servers (dispatcher entries in listener services output).

 

TNS_ADMIN=$GRID_HOME/network/admin $GRID_HOME/bin/lsnrctl services listener_mfxprd

 

5. If not then check service_names parameter for both the instances. And ensure above services   are properly registered.

   If not then use below command to add services to service_name

ALTER SYSTEM SET service_names='CMA','RISKVIEW','RISKCALC','MULTIVAL','VARMARGIN','MFXPRD' SCOPE=BOTH SID='MFXPRDI1';
ALTER SYSTEM SET service_names='MARGIN','MFXPRD' SCOPE=BOTH SID='MFXPRDI2';

 

Measuring Performance by Service Using the Automatic Workload Repository

Services add a new dimension for performance tuning. With services, workloads are visible and measurable, and therefore resource consumption and wait times are attributable by application. Tuning by using "service and SQL" replaces tuning by "session and SQL" in the majority of systems where all sessions are anonymous and shared.
The AWR maintains performance statistics that include information about response time, throughput, resource consumption, and wait events for all services and work that a database performs. Oracle Database also maintains metrics, statistics, wait events, wait classes, and SQL-level traces for services. You can optionally augment these statistics by defining modules within your application to monitor certain statistics. You can also define the actions within those modules that business critical transactions should execute in response to particular statistical values.


/* Enabling Gather statiscitc on Service-Module-Action combination */
-- to display currently connected services, modules and actions
select SID, USERNAME, SERVICE_NAME, MODULE, ACTION from V$SESSION
where SERVICE_NAME in ('hrserv','oeserv')
-- service name and module name are mandatory
begin
 DBMS_MONITOR.SERV_MOD_ACT_STAT_ENABLE(SERVICE_NAME => 'hrserv',
 MODULE_NAME=>'PAYROLL',
 ACTION_NAME => 'EXCEPTIONS PAY');
end;
/


-- gather stats for PAYROLL module and ACTION whose name is null
begin
 DBMS_MONITOR.SERV_MOD_ACT_STAT_ENABLE(SERVICE_NAME => 'hrserv', 
Page 489 Oracle DBA Code Examples
 MODULE_NAME=>'PAYROLL',
 ACTION_NAME => NULL);
end;
/

-- gather stats for PAYROLL module and All its ACTIONs
begin
 DBMS_MONITOR.SERV_MOD_ACT_STAT_ENABLE(SERVICE_NAME => 'hrserv',
 MODULE_NAME=>'PAYROLL',
 ACTION_NAME => '###ALL_ACTIONS');
end;
/

-- to view enabled monitorings
-- types: SERVICE, SERVICE_MODULE, SERVICE_MODULE_ACTION
select A.AGGREGATION_TYPE, A.PRIMARY_ID , A.QUALIFIER_ID1 , A.QUALIFIER_ID2
from DBA_ENABLED_AGGREGATIONS a


-- to view gathered stats
select S.AGGREGATION_TYPE, S.SERVICE_NAME, S.MODULE, S.ACTION, N.CLASS,
 decode(n.CLASS,
'1','User','2','Redo','4','Enqueue','8','Cache','16','OS','32','RAC','64','SQL
','128','Debug', N.CLASS) STAT_CLASS,
 S.STAT_NAME, S.VALUE
from V$SERV_MOD_ACT_STATS s, V$STATNAME n
where S.STAT_ID = N.STAT_ID
order by N.CLASS, S.STAT_ID

-- call times and performance statistics views:
V$SERVICE_STATS
V$SERVICE_EVENTS
V$SERVICE_WAIT_CLASSES
V$SERVICEMETRIC
V$SERVICEMETRIC_HISTORY
/* To Disable Cumulative Stats */
-- stats will be removed from V$SERV_MOD_ACT_STATS
begin
 DBMS_MONITOR.SERV_MOD_ACT_STAT_DISABLE(SERVICE_NAME => 'hrserv',
 MODULE_NAME=>'PAYROLL',
 ACTION_NAME => 'EXCEPTIONS PAY');
end;
/


/* Service Quality Statistics */
-- script from Oracle documentation
-- provides service quality statistics every five seconds
SET PAGESIZE 60 COLSEP '|' NUMWIDTH 8 LINESIZE 132 VERIFY OFF FEEDBACK OFF
COLUMN service_name FORMAT A20 TRUNCATED HEADING 'Service'
COLUMN begin_time HEADING 'Begin Time' FORMAT A10
COLUMN end_time HEADING 'End Time' FORMAT A10
COLUMN instance_name HEADING 'Instance' FORMAT A10
COLUMN service_time HEADING 'Service Time|mSec/Call' FORMAT 999999999
COLUMN throughput HEADING 'Calls/sec'FORMAT 99.99
BREAK ON service_name SKIP 1
SELECT 
Page 490 Oracle DBA Code Examples
service_name
, TO_CHAR(begin_time, 'HH:MI:SS') begin_time
, TO_CHAR(end_time, 'HH:MI:SS') end_time
, instance_name
, elapsedpercall service_time
, callspersec throughput
FROM gv$instance i
, gv$active_services s
, gv$servicemetric m
WHERE s.inst_id = m.inst_id
 AND s.name_hash = m.service_name_hash
 AND i.inst_id = m.inst_id
 AND m.group_id = 10
 ORDER BY service_name , i.inst_id , begin_time ;

Reference : 

https://docs.oracle.com/en/database/oracle/oracle-database/12.2/racad/workload-management-with-dynamic-database-services.html#GUID-095B67FB-3E3A-44BE-84A4-321174015A08

 https://docs.oracle.com/database/121/RACAD/GUID-559FB230-857B-4D97-B36A-F4F76C3A1D47.htm#RACAD7127

Oracle HangAnalyze and SystemState Dump

 We usually  come situation where we need to gather system state and Hanganalyze dump for performance analyze and to upload  to Oracle support .


Let see some insight .  Below is  how i personally capture hanganalyze and system state  dump . Will explain  in detail for each component in trail blog . 

Collection commands for Hanganalyze and Systemstate: RAC with fixes for bug 11800959 and bug 11827088

For 11g:
Sqlplus '/as sysdba'
Oradebug setorapname reco
Oradebug unlimit
Oradebug-g all hanganalyze 3
Oradebug-g all dump systemstate 267
oradebug tracefile_name
…….. Wait at least 1 min
Oradebug -g all hanganalyze 3
Oradebug-g all dump systemstate 267
oradebug tracefile_name
Exit


Collection commands for Hanganalyze and Systemstate: RAC without fixes for Bug 11800959 and Bug 11827088

Sqlplus '/as sysdba'
Oradebug setorapname reco
Oradebug unlimit
xOradebug-g all hanganalyze 3
Oradebug-g all dump systemstate 258
…….. Wait at least 1 min
Oradebug-g all hanganalyze 3
Oradebug-g all dump systemstate 258
Exit


For 10g, run oradebug setmypid instead of oradebug setorapname reco:
Sqlplus '/as sysdba'
Oradebug setmypid
Oradebug unlimit
Oradebug-g all hanganalyze 3
Oradebug-g all dump systemstate 258
…….. Wait at least 1 min
Oradebug-g all hanganalyze 3
Oradebug-g all dump systemstate 258
Exit

In RAC environment, a dump will be created for all RAC instances in the DIAG trace file for each instance.



##############################################
##############################################

Oracle Preliminary connection
________________________

When database i hung  at times we cannot  login to database . In  Such situation we need to  use preliminary connection to  connect to datbase .

sqlplus -prelim / as sysdba

sqlplus /nolog
set _prelim on
connect / as sysdba




##############################################
##############################################

System state Dump 

A system state dump contains the process state for every process.

Every state object for every process is dumped.

A state object represents the state of a database resource including:

processes

sessions

enqueues (locks)

buffers

State objects are held in the SGA

A system state dump does not represent a snapshot of the instance because the database is not frozen for the duration of the dump. The start point of the dump will be earlier than the end point.

Oracle recommends the use of system state dumps to diagnose:

hanging databases

slow databases

database errors

waiting processes

blocking processes

resource contention

Logon to sqlplus as sysdba
SQL> oradebug setmypid
SQL> oradebug unlimit
SQL> oradebug dump systemstate 266
…….. Wait at least 1 min
SQL> oradebug dump systemstate 266
…….. Wait at lease 1 min
SQL> oradebug dump systemstate 266
SQL> oradebug tracefile_name




With releases 11g & RAC and above You should attach to the DIAG process and change its file size limit.
sqlplus "/ as sysdba"
select SPID from v$process where program like '%DIAG%';
oradebug setospid <OS_PID> -- pid of the diag process
oradebug unlimit
oradebug dump systemstate 266
oradebug tracefile_name
exit



Collection commands for Hanganalyze and Systemstate: RAC
There are 2 bugs affecting RAC that without the relevant patches being applied on your system, make using level 266 or 267 very costly. Therefore without these fixes in place it highly unadvisable to use these level
For information on these patches see:
Document 11800959.8 Bug 11800959-a systemstate dump with level> = 10 in RAC dumps huge busy global cache elements-can hang/crash instances
Document 11827088.8 Bug 11827088-Latch 'gc element' contention, LMHB terminates the instance



Systemstate dump has multiple levels:
2: dump (excluding lock element)
10: dump
11: dump + global cache of RAC
256: short stack (function stack)
258: 256 + 2 --> short stack + dump (excluding lock element)
266: 256 + 10 --> short stack + dump
267: 256 + 11 --> short stack + dump + global cache of RAC


Level 11 and 267 will dump the global cache and generate a large trace file, which is generally not recommended. In general, if the process is not too many, we recommend that you use 266 because it can dump the function stack of the process and analyze what operations the process is performing. However, it takes more than 30 minutes to generate a short stack. If there are many processes, such as 2000. In this case, level 10 or level 258 can be generated. level 258 will collect more short stacks than level 10, but some lock element data will be collected less than level 10.
Although process-related information is collected through system state dump, how to effectively interpret relevant information and diagnose and analyze problems is a great challenge!

Reading and Understanding Systemstate Dumps (Doc ID 423153.1) 
How to Collect Systemstate Dumps When you Cannot Connect to Oracle (Doc ID 121779.1)

##############################################

##############################################

Hanganalyze 

Oracle notes that HANGANALYZE run at levels higher that 3 may generate a huge number of trace files for large systems. Do not use levels higher than 3 without discussing their effects with Oracle Technical Support.

Database HANG live is a headache. How to find the reason for HANG live is a problem that DBA must face. When the database HANG lives, most DBAs are often analyzed through the V $ SESSION_WAIT view. In fact, Oracle has a very effective tool-hanganalyze. HANGANALYZE can tell the DBA the information about HANG very clearly, which is convenient for further analysis.

From 19c Oracle has come up with  emergency monitoring report to replace Hanganalyze . 

Below are level we can enable hanganalyze 

     10     Dump all processes (IGN state)

     5      Level 4 + Dump all processes involved in wait chains (NLEAF state)

     4      Level 3 + Dump leaf nodes (blockers) in wait chains (LEAF,LEAF_NW,IGN_DMP state)

     3      Level 2 + Dump only processes thought to be in a hang (IN_HANG state)

   1-2    Only HANGANALYZE output, no process dump at all

Single Instace : 
_________________________
Logon to sqlplus as sysdba
oradebug setmypid;
oradebug unlimit;
oradebug hanganalyze 3;
oradebug tracefile_name


Rac : 
_________________________
Logon to sqlplus as sysdba
oradebug setmypid;
oradebug unlimit;
oradebug setinst all
oradebug -g def hanganalyze 3
oradebug tracefile_name

##############################################

##############################################

Tracing for a Specific ORA-nnnn Error

alter system set events '4021 errorstack(3)  systemstate_global(258)  hanganalyze_global(3) ' ; 

a;ter system set  events '4021 trace name all off'; 

##############################################

##############################################

 v$wait_chains.



From 11gR2 onwards, oracle has provided a dynamic performance view called v$wait_chains. This also contain same information which we gather by running the hanganalyze command. So instead of using hanganalyze, you can use the below query to find wait chains
This is done using dia0 background processes starts collecting hanganalyze information and stores this in memory in the “hang analysis cache”. It does this every 3 seconds for local hanganalyze information and every 10 seconds for global (RAC) hanganalyze information


There is no gv$ equivalent as v$wait_chains would report on multiple instances in a multi-instance (RAC) environment
Some queries for this view
SQL> SELECT chain_id, num_waiters, in_wait_secs, osid, blocker_osid, substr(wait_event_text,1,30)
 FROM v$wait_chains; 



SELECT decode( a.blocker_sid , NULL , '<chain id#' ||a.chain_id||'>' ) chain_id,
RPAD( '+' , LEVEL , '-' ) ||a.sid sid,
RPAD( ' ' , LEVEL , ' ' ) ||a.wait_event_text wait_event
FROM V$WAIT_CHAINS a
CONNECT BY PRIOR a.sid=a.blocker_sid
AND PRIOR a.sess_serial#=a.blocker_sess_serial#
AND PRIOR a.instance = a.blocker_instance START WITH a.blocker_is_valid='FALSE'
ORDER BY a.chain_id ,
LEVEL
/



Query for Top 100 wait chain processes
set pages 1000
set lines 120
set heading off
column w_proc format a50 tru
column instance format a20 tru
column inst format a28 tru
column wait_event format a50 tru
column p1 format a16 tru
column p2 format a16 tru
column p3 format a15 tru
column Seconds format a50 tru
column sincelw format a50 tru
column blocker_proc format a50 tru
column fblocker_proc format a50 tru
column waiters format a50 tru
column chain_signature format a100 wra
column blocker_chain format a100 wra
SELECT * 
FROM (SELECT 'Current Process: '||osid W_PROC, 'SID '||i.instance_name INSTANCE, 
 'INST #: '||instance INST,'Blocking Process: '||decode(blocker_osid,null,'',blocker_osid)|| 
 ' from Instance '||blocker_instance BLOCKER_PROC,
 'Number of waiters: '||num_waiters waiters,
 'Final Blocking Process: '||decode(p.spid,null,'',
 p.spid)||' from Instance '||s.final_blocking_instance FBLOCKER_PROC, 
 'Program: '||p.program image,
 'Wait Event: ' ||wait_event_text wait_event, 'P1: '||wc.p1 p1, 'P2: '||wc.p2 p2, 'P3: '||wc.p3 p3,
 'Seconds in Wait: '||in_wait_secs Seconds, 'Seconds Since Last Wait: '||time_since_last_wait_secs sincelw,
 'Wait Chain: '||chain_id ||': '||chain_signature chain_signature,'Blocking Wait Chain: '||decode(blocker_chain_id,null,
 '',blocker_chain_id) blocker_chain
FROM v$wait_chains wc,
 gv$session s,
 gv$session bs,
 gv$instance i,
 gv$process p
WHERE wc.instance = i.instance_number (+)
 AND (wc.instance = s.inst_id (+) and wc.sid = s.sid (+)
 and wc.sess_serial# = s.serial# (+))
 AND (s.final_blocking_instance = bs.inst_id (+) and s.final_blocking_session = bs.sid (+))
 AND (bs.inst_id = p.inst_id (+) and bs.paddr = p.addr (+))
 AND ( num_waiters > 0
 OR ( blocker_osid IS NOT NULL
 AND in_wait_secs > 10 ) )
ORDER BY chain_id,
 num_waiters DESC)
WHERE ROWNUM < 101;

##############################################

##############################################

Reference :

Reading and Understanding Systemstate Dumps (Doc ID 423153.1)

How to Collect Systemstate Dumps When you Cannot Connect to Oracle (Doc ID 121779.1)

How to Collect Diagnostics for Database Hanging Issues (Doc ID 452358.1)

Oracle Rac Administrator Managed VS Policy Managed ( Server pool )

There are two methods to manage your cluster database.

1) Administrator Managed database

2) Policy Managed Database ( Introduced in 11g R2 ) 

Administrator Managed database

Administrator Managed Database : In Administrator Managed database , a DBA manages each instance of the database by defining specific instances to run on specific nodes in the cluster.It is traditional way of cluster management.

When you define a service for an administrator managed database, you define which instances support that service. These are known as the PREFERRED instances. 

You can also define other instances to support a service if the service’s preferred instance fails. These are known as AVAILABLE instances.

Policy Managed Database

Policy Managed Database : It’s a new method to manage clustered database.It is introduced to help implement dynamic grid configurations. 

In Policy Managed database , DBA is required only to define the cardinality(number of database required).  

Oracle Clusterware manages the allocation of nodes to run the instances and Oracle RAC allocates the required redo threads and undo tablespaces as needed.

Service for Policy managed database are defined as UNIFORM and SINGLETON.

UNIFORM : Running on all instances in the server pool

SINGLETON : Running on only one instance in the server pool.For singleton services, RAC chooses on which instance in the server pool the service is active. 

If that instance fails, then the service fails over to another instance in the pool. A service can only run in one server pool.

 

Some benefits of the Policy-Managed configuration:

Provides distribution according to demand.

Manages business requirements effectively.

Scales just in time.

Maximizes data center efficiency.

By default, there are two server pools, GENERIC and FREE. We can display existing pools as follows.

-bash-4.3$ srvctl config srvpool

Server pool name: Free

Importance: 0, Min: 0, Max: -1

Candidate server names: 

Server pool name: Generic

Importance: 0, Min: 0, Max: -1

Candidate server names: racdb1,racdb2

Features of server pools :

min : (-l)

The minimum number of servers that must be protected in the server pool.

max : (-u)

Maximum number of servers allowed in server pool.

imp : (-i)

By following these steps, you can convert a database that is created as Admin-Managed to a Policy-Managed database.

First, let’s look at our current configuration.

$ srvctl config database -d RACDB

Database unique name: RACDB

Database name: RACDB

Oracle home: /u01/app/oracle/product/11.2.0/dbhome_1

Oracle user: oracle

Spfile: +DATA/RACDB/spfileRACDB.ora

Domain: 

Start options: open

Stop options: immediate

Database role: PRIMARY

Management policy: AUTOMATIC

Server pools: RACDB

Database instances: RACDB1,RACDB2

Disk Groups: DATA,RECO

Mount point paths: 

Services: 

Type: RAC

Database is administrator managed

$ srvctl config database -d RACDB

Database unique name: RACDB

Database name: RACDB

Oracle home: /u01/app/oracle/product/11.2.0/dbhome_1

Oracle user: oracle

Spfile: +DATA/RACDB/spfileRACDB.ora

Domain: 

Start options: open

Stop options: immediate

Database role: PRIMARY

Management policy: AUTOMATIC

Server pools: RACDB

Database instances: RACDB1,RACDB2

Disk Groups: DATA,RECO

Mount point paths: 

Services: 

Type: RAC

Database is administrator managed

Let’s create a new pool. The min and max values for the new pool are set to 2.

$ srvctl add serverpool -g testpool -l 2 -u 2

$ srvctl add serverpool -g testpool -l 2 -u 2

When we view existing pools, the pool we just created is visible.

$ srvctl config srvpool

Server pool name: Free

Importance: 0, Min: 0, Max: -1

Candidate server names: 

Server pool name: Generic

Importance: 0, Min: 0, Max: -1

Candidate server names: racdb1,racdb2

Server pool name: testpool

Importance: 0, Min: 2, Max: 2

Candidate server names:

$ srvctl config srvpool

Server pool name: Free

Importance: 0, Min: 0, Max: -1

Candidate server names: 

Server pool name: Generic

Importance: 0, Min: 0, Max: -1

Candidate server names: racdb1,racdb2

Server pool name: testpool

Importance: 0, Min: 2, Max: 2

Candidate server names:

Now, we can convert our database.

$ srvctl modify database -d RACDB -g testpool

$ srvctl modify database -d RACDB -g testpool

The database configuration will now appear as policy managed.

$ srvctl config database -d RACDB

Database unique name: RACDB

Database name: RACDB

Oracle home: /u01/app/oracle/product/11.2.0/dbhome_1

Oracle user: oracle

Spfile: +DATA/RACDB/spfileRACDB.ora

Domain: 

Start options: open

Stop options: immediate

Database role: PRIMARY

Management policy: AUTOMATIC

Server pools: testpool

Database instances: 

Disk Groups: DATA,RECO

Mount point paths: 

Services: 

Type: RAC

Database is policy managed

$ srvctl config database -d RACDB

Database unique name: RACDB

Database name: RACDB

Oracle home: /u01/app/oracle/product/11.2.0/dbhome_1

Oracle user: oracle

Spfile: +DATA/RACDB/spfileRACDB.ora

Domain: 

Start options: open

Stop options: immediate

Database role: PRIMARY

Management policy: AUTOMATIC

Server pools: testpool

Database instances: 

Disk Groups: DATA,RECO

Mount point paths: 

Services: 

Type: RAC

Database is policy managed

Now our database is policy managed. Our database will run when there is an appropriate server for the testpool we created above. 

We can check the status of the server repositories as follows.

$ srvctl status serverpool

Server pool name: Free

Active servers count: 0

Server pool name: Generic

Active servers count: 0

Server pool name: testpool

Active servers count: 2

$ srvctl status serverpool

Server pool name: Free

Active servers count: 0

Server pool name: Generic

Active servers count: 0

Server pool name: testpool

Active servers count: 2

Oracle Asynchronous Global Index Maintenance jobs for DROP and TRUNCATE Partition in Oracle Database 12c Release 1

 
This new feature in Oracle 12C is as default always on. Each TRUNCATE or DROP commands performed on a partition automatically triggers asynchronous global index maintenance. It means that you don’t need to wait for global INDEX maintenance 

The column DBA_INDEXES.ORPHANED_ENTRIES shows that Oracle is aware that the index may have keys referring to dropped partitions. There is a pre-seeded daily job that tidies them up; by default, it runs every night from 22:00. 

Global index maintenance is decoupled from the DROP and TRUNCATE partition maintenance operation without rendering a global index unusable. Index maintenance is done asynchronously and can be delayed to a later point-in-time.
 
Delay global index maintenance to off-peak hours without affecting index availability, and reduce and truncate partition and sub-partition maintenance operations faster and with fewer resources at the point in time for partition maintenance operations.
 
When combined with the update index clause, the DROP partition and the TRUNCATE partition command will result in metadata index maintenance. This feature is only used for heap tables and does not support object types, domain indexes, or tables owned by SYS.

For backward compatibility you still need to specify UPDATE INDEXES clause.

Limitations of asynchronous global index maintenance:

Only performed on heap tables

No support for tables with object types

No support for tables with domain indexes

Not performed for the user SYS

Asynchronous cleanup orphans in an index can be done:

Automatically by Oracle job SYS.PMO_DEFERRED_GIDX_MAINT_JOB

Manually

just run above job SYS.PMO_DEFERRED_GIDX_MAINT_JOB

run procedure DBMS_PART.CLEANUP_GIDX

run sql statement ALTER INDEX REBUILD [PARTITION] – the same like in previous releases

run sql statement ALTER INDEX [PARTITION] COALESCE CLEANU

 select job_name , start_date,enabled,state,comments

  2  from dba_scheduler_jobs

  3  where job_name ='PMO_DEFERRED_GIDX_MAINT_JOB';

JOB_NAME             START_DATE           ENABL STATE           COMMENTS

-------------------- -------------------- ----- --------------- --------------------

PMO_DEFERRED_GIDX_MA 29-JUN-13 02.00.00.6 TRUE  SCHEDULED       Oracle defined autom

INT_JOB              00000 AM US/CENTRAL                                                            atic index cleanup f

                                                                                                                                    or partition mainten

                                                                                                                                        ance operations with

                                                                                                                                 deferred global ind

                                                                                                                                    ex maintenance

Tradional Way : 

________________________

ALTER TABLE t1 TRUNCATE PARTITION part_2014 DROP STORAGE UPDATE INDEXES;

ALTER TABLE t1 DROP PARTITION part_2014 UPDATE INDEXES;

To Run Job manually 

________________________

select enabled,run_count from dba_scheduler_jobs where job_name='PMO_DEFERRED_GIDX_MAINT_JOB';

exec dbms_scheduler.run_job('SYS.PMO_DEFERRED_GIDX_MAINT_JOB')

Manually trigger the index maintenance.

________________________

EXEC DBMS_PART.cleanup_gidx(USER, 't1');

To Enable Logging 

________________________

By default run history for this job wont be visible . We need  to force enable its logging as per Doc 2506878.1 

sqlplus / as sysdba

SQL> select job_name from dba_Scheduler_jobs where job_class = 'SCHED$_LOG_ON_ERRORS_CLASS'

exec dbms_scheduler.set_attribute('<JOB_NAME>', 'logging_level',DBMS_SCHEDULER.LOGGING_FULL);

  

Disable Job 

________________________

This was main reason for me  to write Blog on this . We were facing  locking issue in most of database  so we planned to disable  this job 

select job_name, enabled from DBA_SCHEDULER_JOBS WHERE job_name = 'SPMO_DEFERRED_GIDX_MAINT_JOB';

exec dbms_scheduler.run_job('PMO_DEFERRED_GIDX_MAINT_JOB')

EXEC SYS.DBMS_SCHEDULER.DISABLE ('PMO_DEFERRED_GIDX_MAINT_JOB'); 

In case we are planing to disable all scheduled jobs we can use below  : 

  col ATTRIBUTE_NAME for a30

  col VALUE for a60

  set lines 180

  set pages 999

  select * from DBA_SCHEDULER_GLOBAL_ATTRIBUTE;

  exec dbms_scheduler.set_scheduler_attribute('SCHEDULER_DISABLED','FALSE');

Reference: 

1) New Oracle Provided 12C Jobs are Not Showing any Execution Run History Details (Doc ID 2506878.1) 

2) https://docs.oracle.com/database/121/VLDBG/GUID-087B87A6-959A-40C6-82AF-36E401FD089B.htm#VLDBG14107