Neil Chandler's DBA Blog

A resource for Database Professionals

Posts Tagged ‘oracle

The trouble with Timezones & Grid Infrastructure

leave a comment »

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

TZ=Europe/London

NLS_LANG=AMERICAN_AMERICA.AL32UTF8

TNS_ADMIN=

ORACLE_BASE=


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?

Written by Neil Chandler

11th April 2014 at 10:51

Online index problem – ORA-08104

with 3 comments

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.

Written by Neil Chandler

12th March 2014 at 23:57

SQL Plan Management – 12C dumb feature

leave a comment »

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

Then run:

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.

Written by Neil Chandler

8th December 2013 at 09:57

UKOUG Tech13

leave a comment »

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):

UKOUG13_Tweet_Feed

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. :-)

@chandlerdba

Written by Neil Chandler

7th December 2013 at 10:03

Posted in Management, UKOUG

Tagged with , , ,

Oracle’s Locking Model – Multi Version Concurrency Control

leave a comment »

mvcc

Written by Neil Chandler

1st December 2013 at 15:42

UKOUG Tech13 launch last night

with 2 comments

UKOUG Tech13 agenda launch last night. Feeling energised today if a little delicate.

Looks like I’m giving 2 presentations this year.

20130906-091952.jpg

20130906-091925.jpg

Written by Neil Chandler

6th September 2013 at 08:23

Posted in UKOUG

Tagged with ,

SQL Developer insight

leave a comment »

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.

Written by Neil Chandler

5th September 2013 at 07:43

Maslow’s Hierarchy of Needs

with 2 comments

Maslow updated – you all know somebody who believes this. If you don’t, it’s probably you :-) :

Maslo

For those of you who are unaware of Maslow

Written by Neil Chandler

30th August 2013 at 13:50

Posted in Prevarication, Uncategorized

Tagged with , ,

SYSMAN password change

leave a comment »

Some days you just forget the dot all of the i’s.

I had just installed a new RAC cluster, got it all up and running and was using DBCONSOLE to check the system out – no access to the Production Grid Control for this cluster yet. I then made a few more configuration changes and restarted one of the nodes. I was rather surprised that the console could no longer access the system. It was claiming the instance was down, and asking for server logins to allow restart. I was quite sure the instance was available, mainly because I was connected using SQL Developer and executing queries.

So, what went wrong? What config had changed before I restarted the nodes? I checked my notes and… I was hardening passwords. One of the passwords I changed was the SYSMAN password. However, I had completely neglected to inform the EM agent for the console that I had changed the password! Idiot.

cd $ORACLE_HOME/<node_database>/sysman/config
vi emoms.properties
change:
 - oracle.sysman.eml.mntr.emdRepPwd=<clear-text-password>
 - oracle.sysman.eml.mntr.emdRepPwdEncrypted=FLASE

emctl stop dbconsole
emctl start dbconsole
…and all is well again

This blog entry was brought to you by Pierrot.

Written by Neil Chandler

29th August 2013 at 05:51

RMAN Incarnations revisited (11G)

with one comment

Time for an update to a older post. I have previously talked about the annoyance of connecting to RMAN with a duplicated database where the DBID has not been changed. RMAN happily breaks the catalog by assuming the “new” database is a new incarnation, and prevents the previous owner of the catalog from using the backups.

I wrote a blog post a while ago about hacking your way past this problem, but was recently informed by Martin Bach that there was actually an RMAN command to fix the Incarnation problem I had encountered, so I though I had better take a look and see if it worked!

Well, the first thing I noticed was that Oracle 11G does not break when connecting from a different database with the same DBID the way it did in Oracle 10G:

[oracle@localhost ~]$ rman target system/oracle catalog rman/rman@orcl1
Recovery Manager: Release 11.2.0.2.0 - Production on Sat Jan 26 12:26:10 2013
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL1 (DBID=1229390655)
connected to recovery catalog database

RMAN> list incarnation;
starting full resync of recovery catalog
full resync complete
List of Database Incarnations
DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1 18 ORCL1 1229390655 PARENT 1 13/08/09 23:00:48
1  2 ORCL1 1229390655 CURRENT 754488 30/10/09 11:38:43

RMAN> list backup summary;

List of Backups
===============
Key TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
------- -- -- - ----------- ----------------- ------- ------- ---------- ---
331 B A A DISK 26/01/13 08:55:32 1 1 NO BACKUP1
375 B A A DISK 26/01/13 09:06:44 1 1 NO BACKUP2
400 B A A DISK 26/01/13 09:07:02 1 1 NO BACKUP3
587 B F A DISK 26/01/13 11:20:09 1 1 YES FULL BACKUP
609 B F A DISK 26/01/13 11:20:11 1 1 NO TAG20130126T112010

And on the alternate database:

[oracle@localhost ~]$ rman target system/oracle catalog rman/rman@orcl1
Recovery Manager: Release 11.2.0.2.0 - Production on Sat Jan 26 12:15:07 2013
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL2 (DBID=1229390655)
connected to recovery catalog database

RMAN> list incarnation;
starting full resync of recovery catalog
full resync complete
List of Database Incarnations
DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1 18 ORCL2 1229390655 PARENT 1 13/08/09 23:00:48
1  2 ORCL2 1229390655 CURRENT 754488 30/10/09 11:38:43

RMAN> list backup summary;

specification does not match any backup in the repository

RMAN>

Whilst the incarnations look a little incorrect (referring to ORCL2), the system does not break. So, no more need to hack around with incarnations if the system breaks accidentally. However, what if you register the other database…

[oracle@localhost ~]$ rman target system/oracle catalog rman/rman@orcl1
Recovery Manager: Release 11.2.0.2.0 - Production on Sun Jan 27 05:44:06 2013
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL2 (DBID=1229390655)
connected to recovery catalog database
RMAN> register database;
starting full resync of recovery catalog
full resync complete
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of register command on default channel at 01/27/2013 05:44:12
RMAN-20002: target database already registered in recovery catalog

So, after a little effort it would appear I can’t easily break the incarnations in Oracle 11G. So let’s try. I recovered the ORCL1 database to create a new incarnation to see how ORCL2 would behave when connected:

on ORCL1:

[oracle@localhost ~]$ rman target system/oracle catalog rman/rman@orcl1
Recovery Manager: Release 11.2.0.2.0 - Production on Sun Jan 27 12:32:09 2013
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL1 (DBID=1229390655)
 connected to recovery catalog database


RMAN> list incarnation;
 List of Database Incarnations
 DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
 ------- ------- -------- ---------------- --- ---------- ----------
 1 18 ORCL1 1229390655 PARENT 1 13/08/09 23:00:48
 1 2 ORCL1 1229390655 PARENT 754488 30/10/09 11:38:43
 1 921 ORCL1 1229390655 CURRENT 10215936 27/01/13 12:27:12 <- new incarnation

<BR>

And now ORCL2 behaves a little differently, recognising the ORCL1 incarnations correctly, and throwing an error:

[oracle@localhost ~]$ rman target system/oracle catalog rman/rman@orcl1
Recovery Manager: Release 11.2.0.2.0 - Production on Sun Jan 27 12:19:27 2013
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL2 (DBID=1229390655)
 connected to recovery catalog database

RMAN> list incarnation;
 List of Database Incarnations
 DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
 ------- ------- -------- ---------------- --- ---------- ----------
 1 18 ORCL1 1229390655 PARENT 1 13/08/09 23:00:48
 1 2 ORCL1 1229390655 PARENT 754488 30/10/09 11:38:43
 1 921 ORCL1 1229390655 CURRENT 10215936 27/01/13 12:27:12

RMAN> list backup summary;
RMAN-00571: ===========================================================
 RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
 RMAN-00571: ===========================================================
 RMAN-03002: failure of list command at 01/27/2013 12:19:38
 RMAN-06004: ORACLE error from recovery catalog database: RMAN-20004: target database name does not match name in recovery catalog

So, what if I change the name of ORCL2 back to ORCL1. Can I reproduce my error then?

[oracle@localhost dbs]$ sqlplus / as sysdba
SQL*Plus: Release 11.1.0.7.0 - Production on Sun Jan 27 12:23:29 2013
Copyright (c) 1982, 2008, Oracle. All rights reserved.
Connected to:
 Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
 With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> shutdown immediate;
 Database closed.
 Database dismounted.
 ORACLE instance shut down.

SQL> startup mount;
 ORACLE instance started.
Total System Global Area 456146944 bytes
 Fixed Size 1344840 bytes
 Variable Size 381684408 bytes
 Database Buffers 67108864 bytes
 Redo Buffers 6008832 bytes
 Database mounted.

 SQL> exit
 Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
 With the Partitioning, OLAP, Data Mining and Real Application Testing options

[oracle@localhost dbs]$ nid target=system/oracle dbname=orcl1 setname=yes
DBNEWID: Release 11.2.0.2.0 - Production on Sun Jan 27 12:24:10 2013
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to database ORCL2 (DBID=1229390655)
Connected to server version 11.2.0
Control Files in database:
 /home/oracle/app/oracle/oradata/orcl/control01.ctl
 /home/oracle/app/oracle/flash_recovery_area/orcl/control02.ctl
Change database name of database ORCL2 to ORCL1? (Y/[N]) => Y
Proceeding with operation
 Changing database name from ORCL2 to ORCL1
 Control File /home/oracle/app/oracle/oradata/orcl/control01.ctl - modified
 Control File /home/oracle/app/oracle/flash_recovery_area/orcl/control02.ctl - modified
 Datafile /home/oracle/app/oracle/oradata/orcl/system01.db - wrote new name
 Datafile /home/oracle/app/oracle/oradata/orcl/sysaux01.db - wrote new name
 Datafile /home/oracle/app/oracle/oradata/orcl/undotbs01.db - wrote new name
 Datafile /home/oracle/app/oracle/oradata/orcl/users01.db - wrote new name
 Datafile /home/oracle/app/oracle/oradata/orcl/example01.db - wrote new name
 Datafile /home/oracle/app/oracle/oradata/orcl/APEX_1246426611663638.db - wrote new name
 Datafile /home/oracle/app/oracle/oradata/orcl/APEX_1265209995679366.db - wrote new name
 Datafile /home/oracle/app/oracle/oradata/orcl/temp01.db - wrote new name
 Control File /home/oracle/app/oracle/oradata/orcl/control01.ctl - wrote new name
 Control File /home/oracle/app/oracle/flash_recovery_area/orcl/control02.ctl - wrote new name
 Instance shut down
Database name changed to ORCL1.
 Modify parameter file and generate a new password file before restarting.
 Succesfully changed database name.
 DBNEWID - Completed succesfully.
[note: I have already got the relevant init.ora and oratab setup]
[oracle@localhost dbs]$ . oraenv
 ORACLE_SID = [orcl2] ? orcl1
 The Oracle base has been set to /home/oracle/app/oracle

 [oracle@localhost dbs]$ sqlplus / as sysdba
SQL*Plus: Release 11.1.0.7.0 - Production on Sun Jan 27 12:24:31 2013
Copyright (c) 1982, 2008, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup mount;
 ORACLE instance started.
Total System Global Area 456146944 bytes
 Fixed Size 1344840 bytes
 Variable Size 381684408 bytes
 Database Buffers 67108864 bytes
 Redo Buffers 6008832 bytes
 Database mounted.
SQL> alter database open;
Database altered.

SQL >exit

[oracle@localhost dbs]$ rman target system/oracle catalog rman/rman@orcl1

Recovery Manager: Release 11.2.0.2.0 – Production on Sun Jan 27 12:52:45 2013

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

connected to target database: ORCL1 (DBID=1229390655)
connected to recovery catalog database

RMAN> list incarnation;

database reset to incarnation 2
starting full resync of recovery catalog

List of Database Incarnations
DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
——- ——- ——– —————- — ———- ———-
1 18 ORCL1 1229390655 PARENT 1 13/08/09 23:00:48
1 2 ORCL1 1229390655 CURRENT 754488 30/10/09 11:38:43
1 921 ORCL1 1229390655 ORPHAN 10215936 27/01/13 12:27:12

So, the newly rename-to ORCL1 thinks we are at incarnation 2. However, log back into the original ORCL1 and it resets the incarnation back to 921. Still no corruption, still no problem!

So, I still can’t prove whether the ALTER DATABASE SET INCARNATION command will work as mentioned to me, or whether it’s just something that allows me to recover across a resetlogs command. Looks like I’ll have to reinstall Oracle 10G… tomorrow.

Written by Neil Chandler

13th February 2013 at 22:50

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: