Posts Tagged ‘oracle’
These are a bit of a pain as their location isn’t intuitive. So to remind me where they are:
Login to the server(s) as the grid owner and check the scan listener status. This will show you the location of the listener log. cd to just below the diag directory and you’re off!:
server-name:/u01/grid>ps -ef | grep SCAN grid 8542 8282 0 10:20 pts/0 00:00:00 grep SCAN grid 9349 1 0 Mar07 ? 00:07:33 /u01/app/11g/grid/bin/tnslsnr LISTENER_SCAN1 -inherit
server-name:/u01/grid>lsnrctl status LISTENER_SCAN1
LSNRCTL for Linux: Version 188.8.131.52.0 - Production on 28-MAY-2014 10:20:12
Copyright (c) 1991, 2013, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN1))) STATUS of the LISTENER ------------------------ Alias LISTENER_SCAN1 Version TNSLSNR for Linux: Version 184.108.40.206.0 - Production Start Date 07-MAR-2014 17:27:50 Uptime 81 days 15 hr. 52 min. 21 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /u01/app/11g/grid/network/admin/listener.ora Listener Log File /u01/app/11g/grid/log/diag/tnslsnr/server-name/listener_scan1/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER_SCAN1))) (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.6.148.141)(PORT=1521))) Services Summary... Service "FRONT_APP_DB_SVC.WORLD" has 2 instance(s). Instance "ORCL1", status READY, has 1 handler(s) for this service... Instance "ORCL2", status READY, has 1 handler(s) for this service... Service "ORCL.WORLD" has 2 instance(s). Instance "ORCL1", status READY, has 1 handler(s) for this service... Instance "ORCL2", status READY, has 1 handler(s) for this service... Service "ORCLXDB.WORLD" has 2 instance(s). Instance "ORCL1", status READY, has 1 handler(s) for this service... Instance "ORCL2", status READY, has 1 handler(s) for this service... The command completed successfully
ADRCI: Release 220.127.116.11.0 - Production on Wed May 28 10:22:47 2014
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
ADR base = "/u01/app/11g/grid/log" adrci> show homes ADR Homes: diag/asmcmd/user_grid/server-name diag/tnslsnr/server-name/listener_scan3 diag/tnslsnr/server-name/listener_scan2 diag/tnslsnr/server-name/listener_scan1 diag/asmtool/user_root/host_3797755080_80
adrci> show alert
Choose the alert log from the following homes to view:
1: diag/asmcmd/user_grid/server-name 2: diag/tnslsnr/server-name/listener_scan3 3: diag/tnslsnr/server-name/listener_scan2 4: diag/tnslsnr/server-name/listener_scan1 5: diag/asmtool/user_root/host_3797755080_80 Q: to quit
Please select option: 4
and there we are…
remember to be on the correct host for each scan,
otherwise the alert (listener) log file will be out of date.
When you end up spending a far great a percentage of your day than seems sensible killing off Java connections that Developers have carelessly left lying around, locking objects all over the place, you need a solution to get them to go away. The solution is to let them do it themselves!
I’m not advocating granting ALTER SYSTEM to Developers! That way madness lies, or certainly some unintended consequences. I’m all for Dev’s having a lot of freedom in the database, just not freedom with the database.
So, creating a stored procedure (in this example as sys, but as any user with an explicit ALTER SYSTEM privilege granted will do) to kill sessions without allowing too much latitude to do anything else seems appropriate. Here’s one I built earlier:
create or replace procedure sys.kill_session ( p_sid IN number, p_serial IN number, p_instance IN number) as -- Neil Chandler. Grant the ability to kill session on a restricted basis. 21.07.2010 l_username varchar2(30) := null; l_priv number := 1; begin -- Who owns the session? select username into l_username from gv$session where sid = p_sid and serial#=p_serial and inst_id = p_instance; -- Check for DBA role select count(*) into l_priv from dba_role_privs where grantee = l_username and granted_role = 'DBA'; -- If the user has the DBA priv, deny the kill request if l_priv > 0 or l_username is null then dbms_output.put_line ('User request to kill session '||p_sid||','||p_serial||',@'||p_instance|| ' denied. Session is for privileged user '||l_username||'.'); else dbms_output.put_line ('Killing user '||l_username||' - '||p_sid||','||p_serial||',@'||p_instance); execute immediate 'alter system disconnect session '''|| p_sid||','||p_serial||',@'||p_instance|| ''' immediate'; end if; end; / -- and let the proc be seen and used create or replace public synonym kill_session for sys.kill_session; grant execute on kill_session to (whomever); Then a nifty bit of sql to generate the kill commands for the Developers. Please include your favourite columns from gv$session: select username,status,blocking_session, 'exec kill_session ('|| sid||','||serial#||','||inst_id||')' Kill_Command from gv$session where username is not null and type <> 'BACKGROUND' / USERNAME STATUS BLOCKING_SESSION KILL_COMMAND --------- ------- ---------------- ------------------------------------ SYS ACTIVE exec kill_session (31,65,1) SYSTEM INACTIVE exec kill_session (58,207,1) USER_1 INACTIVE exec kill_session (59,404,1) USER_2 INACTIVE exec kill_session (72,138,1) USER_2 INACTIVE exec kill_session (46,99,2)
May the odds be forever in your favour. Let the killing commence…
When installing Oracle Grid Infrastructure 11.2 (and all other releases), you need to make sure that you have all of the server setting correct and to standard before you do the install. One that bit me recently was the timezone setting. The Red Hat 6.4 server(s) in question has the correct file in /etc/localtime (copied from /usr/share/zoneinfo/whatever). If I type in date, I get the reply in the correct timezone (GMT/BST as I’m in London), so all seems correct.
However, the slack Unix Sysadmin (which might or might not have been me) had not put the correct setting in /etc/sysconfig/clock. Unfortunately, when you install Grid Infrastructure, the setting is read from /etc/sysconfig/clock and embedded into a Grid Inforastructure config file. $GRID_HOME/crs/install/s_crsconfig_hostname_env.txt
### This file can be used to modify the NLS_LANG environment variable, which determines the charset to be used for messages.
### For example, a new charset can be configured by setting NLS_LANG=JAPANESE_JAPAN.UTF8
### Do not modify this file except to change NLS_LANG, or under the direction of Oracle Support Services
If you change this entry, and you should check with Oracle Support if this is OK for your site, and you will need to restart Grid Infrastructure. The one thing about this that I really don’t like is that Oracle is storing a runtime configuration file in a an install directory. Does it do that anywhere else?
So, you’re creating (or rebuilding) an index ONLINE on a busy system. Your session dies, or it becomes necessary to kill the command, you may find that Oracle does not (always manage to) automatically clean up after itself.
CREATE INDEX my_ind ON my_table (mycol ASC) LOCAL LOGGING COMPRESS 1 ONLINE; (ctrl-c) ORA-01013: user requested cancel of current operation select * from user_indexes where index_name = 'my_ind'; INDEX_NAME INDEX_TYPE my_ind NORMAL
OMG! WTF! TLA’s! The index is there, even though I cancelled the create statement! Lets drop it…
drop index my_ind; * ERROR at line 1: ORA-08104: this index object <B>79722</B> is being online built or rebuilt
So, HOW do I sort out this mess? Use DBMS_REPAIR!
1 declare 2 lv_ret BOOLEAN; 3 begin 4 lv_ret := dbms_repair.online_index_clean(79722); 5* end; select * from user_indexes where index_name = 'ind_name'; no rows selected
Bang! and the index (or, rather, left-over temporary extents from the build attempt) is gone, ready for you to try again.
In Oracle 11G, Oracle introduced SQL Plan Management (SPM). It is excellent (I love it to bits). It allows you to create Baselines against SQL which lock-down the SQL execution plan. No more plan flips. More consistency. Perfect.
Whenever some Baselined SQL is ran, Oracle still parses it and compares the parsed output to the accepted (Evolved) baselines. If the newly parsed plan is better, a new baseline is added to DBA_SQL_PLAN_BASELINES but is NOT accepted. This means that you need to spend time manually accepting the baseline; running the command DBMS_SPM.EVOLVE_SQL_BASELINE plan and checking the new plan. IF you want it, and/or Oracle evaluates that is it a better plan for that particular set of bind variables, the plan is accepted and becomes a candidate to be used by future execution of your SQL. Complete control over your execution plans.
So, Oracle, what’s all this about in Oracle 12C, eh?
In Oracle 12C there’s a new SPM Evolve advisor task. “By default,
SYS_AUTO_SPM_EVOLVE_TASK runs daily in the scheduled maintenance window” - So, it runs every night and by default it runs DBMS_SPM.EVOLVE_SQL_BASELINE for all new baselines created today and automatically accepts the new plans.
BY DEFAULT? NO! the Oracle, NO!
That is precisely what I don’t want from baselines – Oracle making it’s own mind up about plans without any input from me. I’m using baselines to stop Oracle changing its mind. To explicitly limit the number of paths allowed by the Optimizer to ones I know about and with which I am comfortable. Don’t introduce functionality to do the opposite.
So, immediately following the installation of 12C, I would recommend running (you need to be SYS for this):
SELECT PARAMETER_NAME, PARAMETER_VALUE AS "VALUE" FROM DBA_ADVISOR_PARAMETERS WHERE ( (TASK_NAME = 'SYS_AUTO_SPM_EVOLVE_TASK') AND ( (PARAMETER_NAME = 'ACCEPT_PLANS') OR (PARAMETER_NAME = 'TIME_LIMIT') ) ); PARAMETER_NAME VALUE ------------------------- ---------- ACCEPT_PLANS TRUE TIME_LIMIT 3600
BEGIN DBMS_SPM.SET_EVOLVE_TASK_PARAMETER('SYS_AUTO_SPM_EVOLVE_TASK', 'ACCEPT_PLANS', 'false'); END; /
OK, back where we were, with any baselines fixed in place and doing what I want them to do! Not change.
I attended the UKOUG Tech13 conference this week, along with over 1,000 other Oracle Technical Professionals (and some less-professional too), to learn what I could about how everyone else is doing stuff with Oracle, and how it works. And to share some experiences.
I wasn’t disappointed.
As with everything these days, there was a lot of activity on Twitter about the 4 day conference, and one of the sponsors, RittmanMead, had knocked up a rather wonderful tweet visualisation (click here to see how – magical):
As you can see, there are some of the worlds best-known Oracle techies on the list of top speaker tweets, but I am as pleased as anything to have been in the top 10 most tweeted speakers at the end of the 1st day (although I suspect that’s because I knew where the pub was, rather than too much about my 1st presentation)
Get involved with the Oracle community, in whatever form. It leads to professional and personal development, real insights about what Oracle really does (rather than what the marketeers say), and some good friendships. And possibly liver disease judging by the amount of alcohol consumed by all. :-)
When using SQL Developer, there is a feature called Completion Insight which help you write SQL and PL/SQL in the SQL Worksheets. It will auto-pop helpful things, like object name when typing a SELECT statement, column names if it is aware of the object which you are accessing and things like that.
The pop-up seems to pop-up inconsistently, appearing when I don’t want it and not appearing when I do. I finally snapped and decided to work out (look up) exactly how this feature works. And it’s like this.
1. Make sure your SQL is correctly formed. If you have multiple bits of SQL in the Worksheet, and they are not terminated with a “;” or “/“, then it can get confused about where you are contextually within a statement and not pop-up.
2. The Auto-popup has a delay of either 0.3s (SQL) or 0.6s (PL/SQL). This can be changed in Tools -> Preferences -> Code Editor -> Completion Insight. I have now set a 1.0s delay so it’s not quite so eager to appear.
3. You want the pop-up to appear? Press CTRL + SPACE. If it still does not appear, then it cannot determine your SQL Context (see point 1) or there’s nothing to pop.
Hope this helps you. It’s certainly helped me.