Profile cover photo
Profile photo
Yury Velikanov
I like my job, I like what I do, I like to share knowledge, I like to help others to share knowledge, I like to learn, I like my job.
I like my job, I like what I do, I like to share knowledge, I like to help others to share knowledge, I like to learn, I like my job.

Yury's interests
View all
Yury's posts

Post has attachment
How about to meet on the other side of the glass door?

Hello my Oracle DBA friends. Most of us know that it isn't advisable to use the default DB service name to connect to an Oracle database as it reduces workload management options. It is especially true in RAC environments.

I have created the following small after logon trigger to prevent client connecting to a database using the default service name. It may be way too hard option to use it in a production environment as it could stop your clients from connecting to a database. However if DEV or for a new systems it could be a good start :)

create or replace TRIGGER yve_SERV_LOGON_MON 
-- v0.1 Initial version by yvelik on 04/06/2014
-- The trigger prevents default TNS service name to be used by clients to make sure that specific services are in use. Is specifically important to use custom service names in a RAC configuration.
-- The trigger allows exceptions to be introduced on host by host basis. To allow connections to the default host please insert a record to the yve_serv_login_hosts_ext table
-- Information about all sessions that used the default DB service name is logged under yve_serv_login_log table
-- If the trigger raises unhandled exception then a line in the alert log is inserted

--# Objects used by the trigger (create those objects at the same time as the trigger)
--# create table yve_serv_login_log as select * from gv$session where 1=2;
--# create table yve_serv_login_hosts_ext (host varchar2(100), whoinserted varchar2(100), wheninserted date, description varchar2(4000));
--# Example => insert into yve_serv_login_hosts_ext values ('','yvelik',sysdate,'This is database host that we may connect to the default service from');

v_db_unique_name v_$parameter.value%type;
v_comp_db_unique_name v_$parameter.value%type;
v_parm_service_name v_$parameter.value%type;
v_db_domain v_$parameter.value%type;
v_session v_$session%rowtype;
v_msg varchar2(4000);
v_host_count number;

default_service_in_use EXCEPTION;

-- Let's the other world know where the SQLs are comimg from
-- Select service name the session is connected to
select s.* into v_session from v$session s where sid=sys_context('USERENV', 'SID') and rownum<2;
-- Let’s check if hosts exception list. If the session comes the host in the list we don’t execute further checks
select count(host) into v_host_count from yve_serv_login_hosts_ext where HOST = v_session.machine;

if v_host_count = 0

  -- Select the default services name
  select p.value into v_db_unique_name from v$parameter p where name = 'db_unique_name';
  -- Select db_domain from parameters list
  select p.value into v_db_domain from v$parameter p where name = 'db_domain';
  -- Select service_names parameter list
  select p.value into v_parm_service_name from v$parameter p where name = 'service_names';

  -- There is a difference in naming depending if db_domain is set or not
  if v_db_domain is null 
  then v_comp_db_unique_name:=v_db_unique_name;
  else v_comp_db_unique_name:=v_db_unique_name||'.'||v_db_domain;
  end if;

  if upper(v_session.service_name) = upper(v_comp_db_unique_name)
    RAISE default_service_in_use;
  end if;
end if;

-- Let's clean the application info

WHEN default_service_in_use THEN
  -- For debug and log purposes. Let's record information about the session that uses the default TNS service
  insert into dbe_serv_login_log select * from gv$session where sid=sys_context('USERENV', 'SID') and inst_id=sys_context('USERENV', 'INSTANCE') and rownum<2;
  -- Raise an exception
  v_msg := 'yve_SERV_LOGON_MON Trigger: The session is not allowed to connects to the default TNS service '||v_session.service_name||'. Please use one of non-default services '||v_parm_service_name;
  RAISE_APPLICATION_ERROR (num=> -20042, msg=> v_msg);
-- Let's report a problem in the alert log if there is an unhandled exception note: 2 = write to the alert log
dbms_system.ksdwrt(2, 'ORA-20042: AFTER LOGON trigger yve_SERV_LOGON_MON failed with an error: '||SQLERRM);

Post has attachment
Look there are quite several Oracle positions opened at Google at the moment :)

Post has attachment
Someone asked me: Do you suggest RAC certification and would it play a role in an employment process?

Sharing the conversation just because I think some of you may be interested in the answer 

Question: Hello Yury. I have some question about Oracle certification. I have OCP certificate and i'm thinking to take one more, RAC expert certificate. Employers which i had worked never asked me about my certification. And now I think need I one more certificate or not? I want to ask you. Employers in which you had worked asked you about your certificate?

My Answer: I would definitely go for the RAC certification. It is a very good and challenging exam. It is a good way to learn and master RAC related features. 

As to the employment. The certification may play or may not a role. If someone asked me to review a CV and I would see RAC relate certificate I will give to that person an initial advantage comparing to one who doesn't (I just know how challenging the exam is). However it will not be the decider. I would give certification ~5% in terms of CV evaluation.

Post has attachment
Oracle Applications DBA wanted at Google (Mountain View)!

Hey folks! Do you want to work with an awesome team at Google? Here is your chance! Apply and who knows may be I have a pleasure to work with you together  

PS If you have any question do not hesitate to ask. If you think that someone from your friends may be interested feel free to share.

See more details under the following link! See you soon!t=jo&jid=818002

Post has shared content
Look. RAC Attack Ninjas having FUN! Who will join us at Collaborate 2014? :)

Post has attachment
“Oracle Infrastructure Middleware Engineer" position is opened at GOOGLE!

Do you want to work with me in the same team at Google ? Here is your chance!  Refresh your CV/RESUME and apply! Let dream to become true! I am looking forward to work with you in the same team!!t=jo&jid=4355001&

Post has attachment
Look my interview to +IOUG | Independent Oracle Users Group

where I am talking about several things including:
-- If The Oracle ACE Program helped my career
-- What is my biggest achievement as of today
# It isn't a technical project, it is something else 
-- What is my biggest regret
-- Advice to you on how you can advance your career

Post has attachment
How to Download Oracle Software Using WGET?

I am sure many of my friends from Oracle area have their own solutions. Some of them are more elegant some less. Today two of my good friends sharing their great work in that space. André Araújo is talking about what options are available to download SW from Oracle sites Oracle Technology Network, eDelivery, MOS) and Maris Elsins shared his great script that allows you to download patches in CMD mode from My Oracle Support.

Feel free to share among your Oracle peers

How to Download Oracle Software Using WGET or CURL
from +André Araújo – Downloading Patches From My Oracle Support
from +Maris Elsins

Post has attachment
Look. We run into this lovely creature other day at my kids school :)
Wait while more posts are being loaded