MASTER TITLE PAGE

making services work

Bjrn Rost

Bjrn Rostabout meConsultantOracle Database HA & PerformanceSolaris and Linux DevOps & automationOracle ACE Director(ex) president of RAC SIGOTN EMEA tour organizer

2016 Pythian

ABOUT PYTHIANPythians 400+ IT professionals help companies adopt and manage disruptive technologies to better compete 2016 Pythian. Confidential3

2016 Pythian. Confidential4

Systems currently managed by PythianEXPERIENCEDPythian experts in 35 countriesGLOBALMillennia of experience gathered and shared over 19 yearsEXPERTS11,8002 400

THE TOP 1.5% GLOBALLY 2016 Pythian. Confidential5

2015 Resumes Reviewed: 12,711

Behavioral Interviews Conducted: 394

Technical TestsSent: 4062 Passed: 562

Job OffersMade: 189Accepted: 174

motivationwhat are servicesfeatures for any database (SI and RAC)features for RACdata guard (broker) usagehow to create and manage services

2016 Pythian

AGENDA

motivation (noun)/mtve()n/AKA: how to tease you enough to pay attention through the next 42 mintues

2016 Pythian

listen to Captain Hindsight

2016 Pythian9If only I knewreduce downtimemore flexibility in managingincrease RAC performancefaster, more efficient troubleshooting

so what are services? 2016 Pythian10

what is a service?EOUC_DEMO = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = hostname) ) (CONNECT_DATA = (SERVICE_NAME = EOUC_DEMO) ) )./sqlcl/bin/sql brost/o@hostname:1521/eouc_demo

2016 Pythian

benefits for all databases

group and filter13 2016 Pythian

V$SESSIONV$SERVICE_EVENTV$SERVICE_WAIT_CLASSV$SERVICE_STATSV$SERVICEMETRICV$SERVICEMETRIC_HISTORY

V$ACTIVE_SESSION_HISTORYDBA_HIST_ACTIVE_SESS_HISTORYDBA_HIST_SERVICE_STATDBA_HIST_SERVICE_WAIT_CLASS

group and filter

2016 Pythian

group and filterSQL> @snapper ash=service_name+wait_class 5 1 allSampling SID all with interval 5 seconds, taking 1 snapshots...

-- Session Snapper v4.15 - by Tanel Poder ( http://blog.tanelpoder.com ) - Enjoy the Most Advanced Oracle Troubleshooting Script on the Planet! :)

-----------------------------------------------------Active% | SERVICE_NAME | WAIT_CLASS----------------------------------------------------- 100% | DEMO_BATCH | Application 47% | DEMO_OLTP | ON CPU 40% | DEMO_OLTP | User I/O 30% | SYS$BACKGROUND | System I/O 14% | DEMO_OLTP | Commit 5% | SYS$BACKGROUND | ON CPU 2% | DEMO_OLTP | Concurrency 2016 Pythian

group and filter

2016 Pythian

quiesce tactically17 2016 Pythian

control scheduler jobs 2016 Pythian

job example setupcreate table heartbeat (id number, ts timestamp, message varchar2(42 char));

create sequence hb_seq;

create or replace procedure hbping ISBEGININSERT INTO heartbeat (id, ts, message) values(hb_seq.nextval, localtimestamp, 'I am alive);COMMIT;END; 2016 Pythian

job class to service mappingBEGINDBMS_SCHEDULER.CREATE_JOB_CLASS (job_class_name => 'SERVICE_JOBS',service=> 'DEMO_SCHEDULER');END;

BEGINDBMS_SCHEDULER.CREATE_JOB (job_name => 'HB_DEMO',job_type => 'STORED_PROCEDURE',job_action => 'BROST.HBPING',job_class => 'SERVICE_JOBS',start_date => systimestamp,repeat_interval => 'freq=secondly;',enabled => TRUE);END; 2016 Pythian

demo disable job through service

migrate transparently 2016 Pythian

DEMO = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP) (HOST = demo.pythian.com) (PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = DEMO.PYTHIAN.COM) ) )BADDEMO = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP) (HOST = 10.10.10.42) (PORT = 1521)) ) (CONNECT_DATA = (SID = ORCL) ) )

avoid static ip and SIDdns and service can move 2016 Pythian

create dns names for each serviceeven for SCAN in RACbut avoid using CNAMEdns aliases 2016 Pythian

connect to PDBs 2016 Pythian

map to resource manager 2016 Pythian

map service to RM consumer groupBEGIN DBMS_RESOURCE_MANAGER.clear_pending_area(); DBMS_RESOURCE_MANAGER.create_pending_area();

DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING( attribute=> 'SERVICE_NAME', value=> 'DEMO_BATCH', consumer_group=> 'BATCH_GROUP');

DBMS_RESOURCE_MANAGER.validate_pending_area(); DBMS_RESOURCE_MANAGER.submit_pending_area(); END; 2016 Pythian

enable editions (EBR) 2016 Pythian

benefits forReal Application Clusters

2016 Pythianalways online in nomount in mount not modifiableno TAFno XAno app continuityno control over instancesdefault service in RAC

existing sessions can not moveload-balancing decision made when connectinglistener plays biggest role in load-balancing

lb determined by:current sessions per node (long)current load avg per node (short)

workload management 2016 Pythian

workload management and load-balancing 2016 Pythian

client-sideserver-sideABpreferred = A, B

tns alias (load-balance)SCAN (by default)

only affects the listener choicedone in (SCAN or VIP) listeneruses goal (short or long)has information about stateuses list of preferred instancesserver-side load-balancingclient-side load-balancing 2016 Pythian

preferred instances and two singleton services 2016 Pythian

ABpreferred = Apreferred = B

2 node RAC on Oracle cloudsimple swingbench OEonly 1GB in size

benchmark setup

demo performance from singleton services 2016 PythianDEMO

redirect new connections 2016 Pythian

ABpreferred = Apreferred = A

reconfigure service to exclude nodewait for old sessions to terminatestop (empty) instanceperform maintenance or patchrestart instancereconfigure service to include node

rolling patching

enabling XA master from singleton

2016 Pythian

in-memory column store distributionRAC challengesno cache fusion for IMCSIMCS distributed across nodesgood: uses sum of all memory areascon: need to have a local process to fetch dataparallel with auto-dopoption to mirror on engineered systems onlycon: wasting resources with redundanciesworkaround: singleton service+parallel_instance_group

static service registration for dgmgrl 2016 Pythian

listener.ora for data guard brokerLISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521)) ) )

SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = ORCL_DGMGRL) (ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1) (SID_NAME = ORCL) ) )

lsnrctl status with static registration[oracle@ol admin]$ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 29-OCT-2016 21:06:01

[...]Services Summary...Service "BLOG_DEMO.PYTHIAN.COM" has 1 instance(s). Instance "ORCL", status READY, has 1 handler(s) for this service...Service "DEMO_BATH.PYTHIAN.COM" has 1 instance(s). Instance "ORCL", status READY, has 1 handler(s) for this service...Service "DEMO_OLTP.PYTHIAN.COM" has 1 instance(s). Instance "ORCL", status READY, has 1 handler(s) for this service...Service "ORCL.PYTHIAN.COM" has 1 instance(s). Instance "ORCL", status READY, has 1 handler(s) for this service...Service "ORCLXDB.PYTHIAN.COM" has 1 instance(s). Instance "ORCL", status READY, has 1 handler(s) for this service...Service "ORCL_DGMGRL" has 1 instance(s). Instance "ORCL", status UNKNOWN, has 1 handler(s) for this service...The command completed successfully

managing services 2016 Pythian

parameter SERVICE_NAMESsimplest waygotcha: no extra properties

DBMS_SERVICEaccess to all propertiesgotcha: must start manuallysrvctlmanages dependencieswith RAC or GIwithout grid infrastructure 2016 Pythian

valid service names

default: DB_UNIQUE_NAME.DB_DOMAINmax(length)=63maximum for all services = 255 charactersavoid underscore as they are invalid in DNS

2016 Pythian

srvctl examplesrvctl add service -db orcl42 \ -pdb pdb1 \ -service demo \ -preferred orcl421 \ -available orcl422 \ -tafpolicy BASIC \ -edition "" \ -clbgoal LONG

srvctl start service -db orcl42 -service demo

srvctl relocate service -db orcl42 \ -service demo \ -oldinst orcl421 -newinst orcl422 2016 Pythian

DBMS_SERVICE exampleSQL> exec DBMS_SERVICE.CREATE_SERVICE('DBMS_DEMO', 'DBMS_DEMO');

SQL> exec DBMS_SERVICE.START_SERVICE('DBMS_DEMO');

SQL> exec DBMS_SERVICE.STOP_SERVICE('DBMS_DEMO');

srvctl example$ srvctl config service \ -db orcl42 \ -service demo

Service name: demoServer pool: Cardinality: 1Disconnect: falseService role: PRIMARYManagement policy: AUTOMATICDTP transaction: falseAQ HA notifications: falseGlobal: falseCommit Outcome: falseFailover type: Failover method: TAF failover retries: TAF failover delay: Connection Load Balancing Goal: LONGRuntime Load Balancing Goal: NONETAF policy specification: BASICEdition: Pluggable database name: pdb1Maximum lag time: ANYSQL Translation Profile: Retention: 86400 secondsReplay Initiation Time: 300 secondsSession State Consistency: GSM Flags: 0Service is enabledPreferred instances: orcl421Available instances: orcl422 2016 Pythian

srvctl example$ srvctl status service -db orcl42 -service demoService demo is running on instance(s) orcl422 2016 Pythian

v$servicesSQL> desc v$services Name Type ------------------------------------- ----------------------- SERVICE_IDNUMBER NAMEVARCHAR2(64) NAME_HASHNUMBER NETWORK_NAMEVARCHAR2(512) CREATION_DATEDATE CREATION_DATE_HASHNUMBER GOALVARCHAR2(12) DTPVARCHAR2(1) AQ_HA_NOTIFICATIONVARCHAR2(3) CLB_GOALVARCHAR2(5) COMMIT_OUTCOME VARCHAR2(3) RETENTION_TIME NUMBER REPLAY_INITIATION_TIMEOUTNUMBER SESSION_STATE_CONSISTENCYVARCHAR2(30) GLOBAL VARCHAR2(3) PDBVARCHAR2(30) SQL_TRANSLATION_PROFILEVARCHAR2(65) MAX_LAG_TIMEVARCHAR2(30) CON_ID NUMBER 2016 Pythian

check status in lsnrctl$ lsnrctl status

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 30-OCT-2016 09:26:46

Services Summary...Service "demo.PYTHIAN.COM" has 1 instance(s). Instance "orcl422", status READY, has 1 handler(s) for this service...Service "orcl42.PYTHIAN.COM" has 2 instance(s). Instance "orcl421", status READY, has 2 handler(s) for this service... Instance "orcl422", status READY, has 1 handler(s) for this service...Service "pdb1.pythian.com" has 2 instance(s). Instance "orcl421", status READY, has 2 handler(s) for this service... Instance "orcl422", status READY, has 1 handler(s) for this service...Service "pdb1_oltp.PYTHIAN.COM" has 2 instance(s). Instance "orcl421", status READY, has 2 handler(s) for this service... Instance "orcl422", status READY, has 1 handler(s) for this service...The command completed successfully 2016 Pythian

$ tnsping localhost/DEMO_OLTP.PYTHIAN.COM

Attempting to contact (DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=DEMO_OLTP.PYTHIAN.COM))(ADDRESS=(PROTOCOL=TCP)(HOST=127.0.0.1)(PORT=1521)))OK (0 msec)$ tnsping localhost/NONEXISTINGSERVICE

Attempting to contact (DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=NONEXISTINGSERVICE))(ADDRESS=(PROTOCOL=TCP)(HOST=127.0.0.1)(PORT=1521)))OK (0 msec)your false friendtnsping 2016 Pythian

create additional service(s)

summary 2016 Pythian54

THANK YOUParagraph55 2016 Pythian