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.

Friday, December 20, 2013

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>

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:


This appears in V$SESSION_EVENT as a library cache pin:



… and, if you look in x$kglpn, you see that SID 296 is blocking and waiting simultaneously: 

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>

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.

ORA-02289

This was tested on Oracle 11.2. You can create a sequence and select the next value from it like this: 

SQL> create sequence seq1
  2  /
 
Sequence created.
 
SQL> select seq1.nextval from dual
  2  /
 
   NEXTVAL
----------
         1
 
SQL> 

… but if you try to use a sequence which does not exist, you get an ORA-02289: 

SQL> select blah.nextval from dual
  2  /
select blah.nextval from dual
       *
ERROR at line 1:
ORA-02289: sequence does not exist
 
SQL>

Wednesday, December 11, 2013

How to Provide User Input to PL/SQL

Here are some simple examples tested on Oracle 11.2. You can use a variable beginning with one ampersand:
 
SQL> !cat accept1.sql
begin
dbms_output.put_line('Hello &your_name');
end;
/
 
If you do this, you are asked to input the data again when you rerun the script:
 
SQL> set echo on
SQL> set serveroutput on
SQL> @accept1
SQL> begin
  2  dbms_output.put_line('Hello &your_name');
  3  end;
  4  /
Enter value for your_name: Andrew
old   2: dbms_output.put_line('Hello &your_name');
new   2: dbms_output.put_line('Hello Andrew');
Hello Andrew
 
PL/SQL procedure successfully completed.
 
SQL> @accept1
SQL> begin
  2  dbms_output.put_line('Hello &your_name');
  3  end;
  4  /
Enter value for your_name: Brian
old   2: dbms_output.put_line('Hello &your_name');
new   2: dbms_output.put_line('Hello Brian');
Hello Brian
 
PL/SQL procedure successfully completed.
 
SQL>
 
You can use a variable beginning with two ampersands:
 
SQL> !cat accept2.sql
begin
dbms_output.put_line('Hello &&first_name');
end;
/
 
If you do this, PL/SQL remembers the value of the variable between one execution and the next:
 
SQL> @accept2
SQL> begin
  2  dbms_output.put_line('Hello &&first_name');
  3  end;
  4  /
Enter value for first_name: Colin
old   2: dbms_output.put_line('Hello &&first_name');
new   2: dbms_output.put_line('Hello Colin');
Hello Colin
 
PL/SQL procedure successfully completed.
 
SQL> @accept2
SQL> begin
  2  dbms_output.put_line('Hello &&first_name');
  3  end;
  4  /
old   2: dbms_output.put_line('Hello &&first_name');
new   2: dbms_output.put_line('Hello Colin');
Hello Colin
 
PL/SQL procedure successfully completed.
 
SQL>
 
Later, if you want to provide a new value, you can UNDEFINE the variable between executions:
 
SQL> undefine first_name
SQL> @accept2
SQL> begin
  2  dbms_output.put_line('Hello &&first_name');
  3  end;
  4  /
Enter value for first_name: David
old   2: dbms_output.put_line('Hello &&first_name');
new   2: dbms_output.put_line('Hello David');
Hello David
 
PL/SQL procedure successfully completed.
 
SQL> undefine first_name
SQL> @accept2
SQL> begin
  2  dbms_output.put_line('Hello &&first_name');
  3  end;
  4  /
Enter value for first_name: Elvis
old   2: dbms_output.put_line('Hello &&first_name');
new   2: dbms_output.put_line('Hello Elvis');
Hello Elvis
 
PL/SQL procedure successfully completed.
 
SQL>
 
You can accept the variable beforehand in SQL*Plus as follows:
 
SQL> !cat accept3.sql
accept christian_name prompt "Who are you? "
begin
  dbms_output.put_line('Hello &christian_name');
end;
/ 

SQL> @accept3
SQL> accept christian_name prompt "Who are you? "
Who are you? Finbar
SQL> begin
  2    dbms_output.put_line('Hello &christian_name');
  3  end;
  4  /
old   2:   dbms_output.put_line('Hello &christian_name');
new   2:   dbms_output.put_line('Hello Finbar');
Hello Finbar
 
PL/SQL procedure successfully completed.
 
SQL>
 
If you don’t want Oracle to display the old and new values of the variable, you can stop this happening by running set verify off beforehand:
 
SQL> set verify off
SQL> @accept3
SQL> accept christian_name prompt "Who are you? "
Who are you? Gordon
SQL> begin
  2    dbms_output.put_line('Hello &christian_name');
  3  end;
  4  /
Hello Gordon
 
PL/SQL procedure successfully completed.
 
SQL>