Database Technologies...!

Prior to Oracle 12C when ever a database migration which involves an IP change was planned, we used to query the session history to find out the possible sources from which connection are established to databases. So that application changes (connections strings) required and firewall rules to be implemented can be identified. Normally a variant of below query was used which is based on dba_hist_active_sess_history (licensable), SELECT machine, program, username, COUNT (*) FROM (SELECT a.machine, a.program, b.username FROM dba_hist_active_sess_history a, dba_users b WHERE username IS NOT NULL AND A.USER_ID = B.USER_ID AND machine != '<hostname>') GROUP BY machine, program, username ORDER BY 1, 2, 3; Among the identified sources, one of the main contributor was  db_links in many of the distributed systems. Although the information returned from above query was informative, we always

This post is more or less for self reference. Mainly in the hope of avoiding the struggle with ', " and ' ' during the job_action definition. :) So the lesson i always forget, when defining stats jobs never try to define the job action inside DBMS_SCHEDULER.CREATE_JOB, insted create a procedure first and then create stored procedure job type. Create the job based on stats parameters required, e.g. - create or replace procedure sunday_stats is BEGIN DBMS_STATS.GATHER_SCHEMA_STATS ('SCOTT',method_opt=>'FOR ALL COLUMNS SIZE REPEAT',degree=>1,granularity=>'AUTO',cascade=>true); END; / Create the job using the above procedure with required frequency, BEGIN SYS.DBMS_SCHEDULER.CREATE_JOB ( job_name => 'scott_stat_sunday' ,start_date => SYSTIMESTAMP ,repeat_interval => 'FREQ=WEEKLY;BYDAY=SUN;BYHOUR=11;BYMINUTE=21;BYSECOND=0' ,end_date => NULL

When a comparatively slow performance of particular query/process is observed, one of the first things to do is to check for any plan changes. Below are the steps taken when a plan change was identified and rectified by loading sql plan from AWR via SQL Tuning Set.( STS). Check whether a plan change is impacting the performance for a given sql_id in question, select ss.snap_id, ss.instance_number node, begin_interval_time, sql_id, plan_hash_value, 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; Below is a scenario where Oracle is using a bad plan, with higher execution time and cost. Wit

In a SPARC Solaris system (Ldom) which runs multiple database instances on 12.2.0.2, the alerts logs were flooded with  ORA-27103 [ ] and ORA-6544 [pevm_peruws_callback-1] [27103] error messages. Database operations were impacted, new connection requests were starting to timeout. 2020-06-01T12:47:06.635714+05:30 Errors in file /u01/app/oracle/diag/rdbms/memdb/memdb1/trace/memdb1_dbw0_19325.trc: ORA-27103: internal error Additional information: 11818 Additional information: 9 ORA-27103: internal error SVR4 Error: 11: Resource temporarily unavailable Additional information: 7175 Additional information: 1114112 Errors in file /u01/app/oracle/diag/rdbms/memdb/memdb1/trace/memdb1_dbw0_19325.trc (incident=173114) (PDBNAME=CDB$ROOT): ORA-27103 [] [] [] [] [] [] [] [] [] [] [] [] Incident details in: /u01/app/oracle/diag/rdbms/memdb/memdb1/incident/incdir_173114/memdb1_dbw0_19325_i173114.trc 2020-06-01T12:47:06.651393+05:30 Errors in file /u01/a