Friday, December 27, 2013

ORA-01078

If you get an ORA-01078 when trying to open a database, this means there is an invalid value in the database’s parameter file:
 
Oracle 9: sqlplus '/ as sysdba'
 
SQL*Plus: Release 9.2.0.5.0 - Production on Fri Dec 27 17:21:19 2013
 
Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.
 
Connected to an idle instance.
 
SQL> startup
ORA-01078: failure in processing system parameters
LRM-00110: syntax error at 'Andrew'
SQL>
 
When this happens, you should normally locate the line concerned in the parameter file, correct it then try again. However, on this occasion, I closed the database and added the invalid value myself. I then started copying the database to another server. The line I added was as follows, right at the start of the parameter file (although you can add it wherever you like):
 
Oracle 9: cat initORCL.ora
Andrew is copying this so it is down.
Etc
db_name=’ORCL’
Etc
Oracle 9:
 
I did this to stop another DBA opening the database by mistake. Once the copy has completed, I will remove the offending line from the parameter file and open the database again.

Thursday, December 26, 2013

ORA-12154

You get this error when you try to connect to a database which is not in your tnsnames.ora file as you can see in the example below:
 
I set my TNS_ADMIN variable to the current directory:
 
C:\Users\J0294094>set TNS_Admin=C:\Users\J0294094
 
C:\Users\J0294094>
 
I started with an empty tnsnames.ora file:
 
C:\Users\J0294094>more tnsnames.ora
 
C:\Users\J0294094>
 
I tried to connect to a database but got an ORA-12154:
 
C:\Users\J0294094>sqlplus system@busdpt1
 
SQL*Plus: Release 11.2.0.3.0 Production on Mon Nov 18 11:14:00 2013
 
Copyright (c) 1982, 2011, Oracle.  All rights reserved.
 
Enter password:
ERROR:
ORA-12154: TNS:could not resolve the connect identifier specified
 
Enter user-name:
 
I added the database’s details to the tnsnames.ora file:
 
C:\Users\J0294094>more tnsnames.ora
BUSDPT1.world=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(Host=zge-mktred-dpd1)(Port=1529)))(CONNECT_DATA=(SID=BUSDPT1)))
 
C:\Users\J0294094>
 
… and the error went away:
 
C:\Users\J0294094>sqlplus system@busdpt1
 
SQL*Plus: Release 11.2.0.3.0 Production on Mon Nov 18 11:20:32 2013
 
Copyright (c) 1982, 2011, Oracle.  All rights reserved.
 
Enter password:
 
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
 
SQL>

Friday, December 20, 2013

Recursive Procedure Compilation

This was tested on Oracle 11.2. You cannot compile a procedure if anybody is running it so, if you write a procedure which compiles itself, it hangs:
 
SQL> conn andrew/reid
Connected.
SQL> select distinct sid from v$mystat
  2  /
 
       SID
----------
       296
 
SQL> create procedure blah
  2  is
  3  begin
  4  execute immediate 'alter procedure blah compile';
  5  end;
  6  /
 
Procedure created.
 
SQL> exec blah;
 
This appears in V$SESSION_EVENT as a library cache pin:
 
SQL> conn / as sysdba
Connected.
SQL> undefine sid
SQL> select time_waited/100
  2  from v$session_event
  3  where event = 'library cache pin'
  4  and sid = '&&sid'
  5  /
Enter value for sid: 296
old   4: and sid = '&&sid'
new   4: and sid = '296'
 
TIME_WAITED/100
---------------
         459.91
 
SQL> exec sys.dbms_lock.sleep(10);
 
PL/SQL procedure successfully completed.
 
SQL> select time_waited/100
  2  from v$session_event
  3  where event = 'library cache pin'
  4  and sid = '&&sid'
  5  /
old   4: and sid = '&&sid'
new   4: and sid = '296'
 
TIME_WAITED/100
---------------
         468.94
 
SQL>
 
… and, if you look in x$kglpn, you see that SID 296 is blocking and waiting simultaneously:
 
SQL> conn / as sysdba
Connected.
SQL> select s.sid, kglpnmod "Mode", kglpnreq "Req"
  2  from x$kglpn p, v$session s, v$session_wait w
  3  where p.kglpnuse = s.saddr
  4  and p.kglpnhdl = w.p1raw
  5  and w.sid = &sid
  6  /
Enter value for sid: 296
old   5: and w.sid = &sid
new   5: and w.sid = 296
 
       SID       Mode        Req
---------- ---------- ----------
       296          0          3
       296          2          0
 
SQL>

ORA-00360

Oracle returns an ORA-00360 if you try to drop a redo logfile member which does not exist: 

SQL> select group#, member from v$logfile
  2  order by 1, 2;
 
    GROUP# MEMBER
---------- ----------------------------------------
         1 /disk1/redo/log1a.rdo
         1 /disk2/redo/log1b.rdo
         2 /disk1/redo/log2a.rdo
 
SQL> alter database drop logfile member
  2  '/disk2/redo/log2b.rdo';
alter database drop logfile member
*
ERROR at line 1:
ORA-00360: not a logfile member: /disk2/redo/log2b.rdo
 
SQL>

Library Cache Pin and Library Cache Lock Wait Events

A developer was trying to compile a procedure without success recently so I decided to reproduce the problem for future reference. I did this in an Oracle 11.2 database. First I created a user then I used it to login, display my SID, create a long running procedure and kick it off. You can see this in the red session below:
 
SQL> conn / as sysdba
Connected.
SQL> create user andrew identified by reid
  2  /
 
User created.
 
SQL> grant create session, create procedure,
  2  select any dictionary to andrew
  3  /
 
Grant succeeded.
 
SQL> grant execute on dbms_lock to andrew
  2  /
 
Grant succeeded.
 
SQL> conn andrew/reid
Connected.
SQL> select distinct sid from v$mystat
  2  /
 
       SID
----------
       296
 
SQL> create procedure lcp
  2  is
  3  begin
  4  sys.dbms_lock.sleep(3600);
  5  end;
  6  /
 
Procedure created.
 
SQL> exec lcp;
 
While this was happening, I logged in with the same user and tried to compile the procedure in the blue session below. You cannot compile a procedure while another session is running it so the compilation had to wait:
 
SQL> conn andrew/reid
Connected.
SQL> select distinct sid from v$mystat
  2  /
 
       SID
----------
       490
 
SQL> alter procedure lcp compile
  2  /
 
I connected to the database as SYS in the green session below and used the SID to determine that the blue session was waiting for a library cache pin:
 
SQL> conn / as sysdba
Connected.
SQL> undefine sid
SQL> select time_waited/100
  2  from v$session_event
  3  where event = 'library cache pin'
  4  and sid = '&&sid'
  5  /
Enter value for sid: 490
old   4: and sid = '&&sid'
new   4: and sid = '490'
 
TIME_WAITED/100
---------------
          30.07
 
SQL> exec sys.dbms_lock.sleep(10);
 
PL/SQL procedure successfully completed.
 
SQL> select time_waited/100
  2  from v$session_event
  3  where event = 'library cache pin'
  4  and sid = '&&sid'
  5  /
old   4: and sid = '&&sid'
new   4: and sid = '490'
 
TIME_WAITED/100
---------------
          39.08
 
SQL>
 
I logged in as ANDREW and tried to compile the procedure again in the purple session which follows:
 
SQL> conn andrew/reid
Connected.
SQL> select distinct sid from v$mystat
  2  /
 
       SID
----------
       394
 
SQL> alter procedure lcp compile
  2  /
 
I connected as an administrator then used the SID to determine that the purple session was waiting for a library cache lock. This happens when more than one session tries to compile the same procedure simultaneously. You can see this in the green session which follows:
 
SQL> undefine sid
SQL> select time_waited/100
  2  from v$session_event
  3  where event = 'library cache lock'
  4  and sid = '&&sid'
  5  /
Enter value for sid: 394
old   4: and sid = '&&sid'
new   4: and sid = '394'
 
TIME_WAITED/100
---------------
          24.05
 
SQL> exec sys.dbms_lock.sleep(10);
 
PL/SQL procedure successfully completed.
 
SQL> select time_waited/100
  2  from v$session_event
  3  where event = 'library cache lock'
  4  and sid = '&&sid'
  5  /
old   4: and sid = '&&sid'
new   4: and sid = '394'
 
TIME_WAITED/100
---------------
          33.07
 
SQL>
 
Finally, I used the script below to locate the session causing the problem. I gave it the SID of the session waiting for the library cache pin. It showed that SID 296 was holding a lock and SID 490 was waiting for one. Teminating SID 296 would allow both compilations to run:
 
SQL> conn / as sysdba
Connected.
SQL> select s.sid, kglpnmod "Mode", kglpnreq "Req"
  2  from x$kglpn p, v$session s, v$session_wait w
  3  where p.kglpnuse = s.saddr
  4  and p.kglpnhdl = w.p1raw
  5  and w.sid = &sid
  6  /
Enter value for sid: 490
old   5: and w.sid = &sid
new   5: and w.sid = 490
 
       SID       Mode        Req
---------- ---------- ----------
       490          0          3
       296          2          0
 
SQL>

ORA-00068

This example, tested on an Oracle 9 database, shows the largest permitted value for the max_enabled_roles parameter:

SQL> alter system set max_enabled_roles = 150 scope = spfile
  2  /
alter system set max_enabled_roles = 150 scope = spfile
*
ERROR at line 1:
ORA-00068: invalid value 150 for parameter max_enabled_roles, must be between 1 and 148

SQL> alter system set max_enabled_roles = 148 scope = spfile
  2  /

System altered.

SQL>

Wednesday, December 18, 2013

SQL MINUS Operator

If you join two SELECT statements together with a MINUS, the output from the second SELECT is removed from the output from the first SELECT before the results are displayed. As always, it is easier to explain by using an example. First create a table for odd and even numbers and another table just for odd numbers:

SQL> create table odd_and_even
  2  (col1 number)
  3  /

Table created.

SQL> create table odd
  2  (col1 number)
  3  /

Table created.


SQL>


Now add some odd and even numbers to the first table then insert the contents again so that each number is stored twice (you will see why later):

SQL> insert into odd_and_even values (1);

1 row created.

SQL> insert into odd_and_even values (2);

1 row created.

SQL> insert into odd_and_even values (3);

1 row created.

SQL> insert into odd_and_even values (4);

1 row created.

SQL> insert into odd_and_even select * from odd_and_even;

4 rows created.

SQL> select * from odd_and_even
  2  /

      COL1
----------
         1
         2
         3
         4
         1
         2
         3
         4

8 rows selected.

SQL>

Now insert some odd numbers into the second table but do not replicate the values:

SQL> insert into odd values (1);

1 row created.

SQL> insert into odd values (3);

1 row created.

SQL> select * from odd
  2  /

      COL1
----------
         1
         3

SQL>

Now display the contents of the first table but before displaying the results, use the MINUS statement to remove any values which appear in the second table:

SQL> select col1 from odd_and_even
  2  minus
  3  select col1 from odd
  4  /

      COL1
----------
         2
         4

SQL>
  
Note that there were four odd numbers in the first table i.e. 1, 3, 1, 3 but only two odd numbers in the second table i.e. 1, 3. Even so, all the odd numbers have been subtracted from the output. This is equivalent to either of the following two SQL statements: 

SQL> select distinct col1 from odd_and_even
  2  where col1 not in
  3  (select col1 from odd)
  4  /

      COL1
----------
         2
         4

SQL> select distinct col1 from odd_and_even x
  2  where not exists
  3  (select col1 from odd
  4   where col1 = x.col1)
  5  /

      COL1
----------
         2
         4

SQL>

ORA-00301 and ORA-27038

If you try to add a member to a redo log group, and the file already exists at the UNIX level,
you get an ORA-00301 and an ORA-27038:
 
SQL> select group#, member from v$logfile;
 
    GROUP# MEMBER
---------- ----------------------------------------
         2 /disk1/redo/log2a.rdo
         1 /disk1/redo/log1a.rdo
         1 /disk2/redo/log1b.rdo
 
SQL> alter database add logfile member
  2  '/disk2/redo/log2b.rdo'
  3  to group 2;
alter database add logfile member
*
ERROR at line 1:
ORA-00301: error in adding log file
'/disk2/redo/log2b.rdo' - file cannot be
created
ORA-27038: skgfrcre: file exists
 
SQL>
 
If you specify the reuse option, the error message does not appear:
 
SQL> alter database add logfile member
  2  '/disk2/redo/log2b.rdo'
  3  reuse to group 2;
 
Database altered.
 
SQL> select group#, member from v$logfile;
 
    GROUP# MEMBER
---------- ----------------------------------------
         2 /disk1/redo/log2a.rdo
         1 /disk1/redo/log1a.rdo
         1 /disk2/redo/log1b.rdo
         2 /disk2/redo/log2b.rdo
 
SQL>

Sunday, December 15, 2013

redo log space wait time, redo buffer allocation retries, redo blocks written and log_buffer

The redo log space wait time statistic records the total amount of time spent (since the instance was started) waiting for space in the redo log buffer. It is recorded in hundredths of a second:
 
SQL> select to_number(value)/100 "Seconds Waited"
  2  from v$sysstat
  3  where name = 'redo log space wait time'
  4  /
 
Seconds Waited
--------------
         96.63
 
SQL>
 
The redo buffer allocation retries statistic shows the total number of times a user process has had to wait for space in the redo log buffer (since instance startup again):
 
SQL> select value from v$sysstat
  2  where name = 'redo buffer allocation retries'
  3  /
 
VALUE
------
2463
 
SQL>
 
The lower these two statistics are, the better. However, you need to look at them in relation to the length of time the instance has been running. The figures above would be bad in an instance started 10 minutes ago but this Oracle 10.2.0.1.0 one was started in June:
 
SQL> select startup_time from v$instance
  2  /
 
STARTUP_TIME
------------
15-JUN-13
 
SQL>
 
…and it is December now so the instance has been up for almost six months:
 
SQL> select sysdate from dual
  2  /
 
SYSDATE
---------
13-DEC-13
 
SQL>
 
You also need to compare them with the amount of redo activity. This instance has had its fair share so the wait statistics start to look even better:
 
SQL> select value from v$sysstat
  2  where name = 'redo blocks written'
  3  /
 
VALUE
----------
167877393
 
SQL>
 
Finally, you need to check whether the statistics are currently increasing. If they are high but stable right now, they may have caused a performance problem in the past but they are not causing one at present.
 
If these statistics caused problems in Oracle 9, you could try increasing the size of the log_buffer initialization parameter. You can check its value as follows:
 
SQL> select value from v$parameter
  2  where name = 'log_buffer'
  3  /
 
VALUE
------------------------------
2104320
 
SQL>
 
However, in more recent versions, Oracle calculates the value of this parameter for you. Fortunately, these statistics have never caused a problem in any databases I have had to monitor.