Saturday, April 19, 2014

log file switch (archiving needed)

I was running a test in Oracle 11.2 and the SQL below, which usually takes around 1 minute, seemed to be running forever:

SQL> begin
  2   for a in 1..8 loop
  3    insert into tab1 select * from tab1;
  4   end loop;
  5  end;
  6  /

I looked up the session's SID in V$SESSION to see if there was a row with wait_time set to zero. This means that the session is currently waiting on the event concerned. I saw a wait event I had not seen before:

SQL> select event, seconds_in_wait
  2  from v$session
  3  where sid = 191
  4  and wait_time = 0
  5  /

EVENT                               SECONDS_IN_WAIT
----------------------------------- ---------------
log file switch (archiving needed)             2596

SQL>

To double check the diagnosis, I waited for 10 seconds:

SQL> exec dbms_lock.sleep(10);

PL/SQL procedure successfully completed.

SQL>

... and when I looked again, the seconds_in_wait had increased by 10 seconds too, proving that the session was spending all its time waiting on this event:

SQL> select event, seconds_in_wait
  2  from v$session
  3  where sid = 191
  4  and wait_time = 0
  5  /

EVENT                               SECONDS_IN_WAIT
----------------------------------- ---------------
log file switch (archiving needed)             2606

SQL>

In most situations, you would have to clear some space from the archive area to allow the log file switch to proceed. However, the test I was running did not need the database to be in ARCHIVELOG mode so I closed the database and mounted it:

SQL> shutdown abort
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area  523108352 bytes
Fixed Size                  1375704 bytes
Variable Size             318767656 bytes
Database Buffers          197132288 bytes
Redo Buffers                5832704 bytes
Database mounted.
SQL>

However, when I tried to put the database in NOARCHIVELOG mode, I saw an error which I had not seen before - 2 new things in 1 day!

SQL> alter database noarchivelog;
alter database noarchivelog
*
ERROR at line 1:
ORA-38774: cannot disable media recovery - flashback
database is enabled

SQL>

Well, I was doing some flahsback testing about a week ago but that is finished for now so I turned it off:

SQL> alter database flashback off
  2  /

Database altered.

SQL>

Then I was able to put the database in NOARCHIVELOG mode and get on with my original test, which I hope to report in a future post:

SQL> alter database noarchivelog
  2  /

Database altered.

SQL> alter database open
  2  /

Database altered.

SQL>

ORA-00942

These examples were tested on Oracle 11.2. If you get an ORA-00942, this might mean that a table does not exist:
 
SQL> conn / as sysdba
Connected.
SQL> drop user andrew cascade
  2  /
 
User dropped.
 
SQL> drop user john cascade
  2  /
 
User dropped.
 
SQL> grant dba to andrew identified by reid
  2  /
 
Grant succeeded.
 
SQL> conn andrew/reid
Connected.
SQL> select count(*) from blah
  2  /
select count(*) from blah
                     *
ERROR at line 1:
ORA-00942: table or view does not exist
 
SQL> 

If that is the case, creating the table will make the error go away: 

SQL> create table blah (col1 number)
  2  /
 
Table created.
 
SQL> select count(*) from blah
  2  /
 
  COUNT(*)
----------
         0
 
SQL> 

But in the next example, table ANDREW.BLAH exists as we have just created it. The problem is that JOHN does not have permission to look at it: 

SQL> grant create session to john
  2  identified by smith
  3  /
 
Grant succeeded.
 
SQL> conn john/smith
Connected.
SQL> select count(*) from andrew.blah
  2  /
select count(*) from andrew.blah
                            *
ERROR at line 1:
ORA-00942: table or view does not exist
 
SQL> 

When that happens, you have to GRANT appropriate access to make the error disappear: 

SQL> conn andrew/reid
Connected.
SQL> grant select on blah to john
  2  /
 
Grant succeeded.
 
SQL> conn john/smith
Connected.
SQL> select count(*) from andrew.blah
  2  /
 
  COUNT(*)
----------
         0
 
SQL>