Tuesday, June 07, 2016

ARCHIVE_LAG_TARGET on Oracle 11.2.0.4

I read about this parameter, which is supposed to force a redo log switch after a certain number of seconds. I decided to try it out in an Oracle 11.2.0.4 database. This is the record from the alert log showing me setting the parameter:

Wed May 25 18:00:39 2016
ALTER SYSTEM SET archive_lag_target=1800 SCOPE=BOTH;
Wed May 25 18:00:39 2016


I expected this to force a redo log switch every 30 minutes but Oracle did the switches every 15 minutes instead. The database was doing no work at the time:

Wed May 25 18:00:39 2016
Thread 1 advanced to log sequence 77 (LGWR switch)
  Current log# 2 seq# 77 mem# 0: /database/ANDREW/DB1/redo02a.log
  Current log# 2 seq# 77 mem# 1: /database/ANDREW/DB1/redo02b.log
Wed May 25 18:15:39 2016
Thread 1 advanced to log sequence 78 (LGWR switch)
  Current log# 3 seq# 78 mem# 0: /database/ANDREW/DB1/redo03a.log
  Current log# 3 seq# 78 mem# 1: /database/ANDREW/DB1/redo03b.log
Wed May 25 18:30:39 2016
Thread 1 advanced to log sequence 79 (LGWR switch)
  Current log# 1 seq# 79 mem# 0: /database/ANDREW/DB1/redo01a.log
  Current log# 1 seq# 79 mem# 1: /database/ANDREW/DB1/redo01b.log
Etc
Etc


I looked on the Internet for a bug which might explain this but could not find one. Then I decided it was time for a bit of trial and error investigation. I dropped one member from each redo log group. I assume you know how to do this if you are reading this blog. Just don’t forget that you cannot drop members from the current redo log group:

SQL> alter database drop logfile member
  2  '/database/ANDREW/DB1/redo03b.log'
  3  /
alter database drop logfile member
*
ERROR at line 1:
ORA-01609: log 3 is the current log for thread 1 - cannot drop members
ORA-00312: online log 3 thread 1: '/database/ANDREW/DB1/redo03a.log'
ORA-00312: online log 3 thread 1: '/database/ANDREW/DB1/redo03b.log'

SQL> alter system switch logfile
  2  /

System altered.

SQL> alter database drop logfile member
  2  '/database/ANDREW/DB1/redo03b.log'
  3  /

Database altered.

SQL>


However, the redo log switches still occurred every 15 minutes:

Thu May 26 10:24:16 2016
Thread 1 advanced to log sequence 143 (LGWR switch)
  Current log# 2 seq# 143 mem# 0: /database/ANDREW/DB1/redo02a.log
Thu May 26 10:39:15 2016
Thread 1 advanced to log sequence 144 (LGWR switch)
  Current log# 3 seq# 144 mem# 0: /database/ANDREW/DB1/redo03a.log
Thu May 26 10:54:15 2016
Thread 1 advanced to log sequence 145 (LGWR switch)
  Current log# 1 seq# 145 mem# 0: /database/ANDREW/DB1/redo01a.log


I repeated the test on an Oracle 9.2.0.7 database but the parameter worked as advertised i.e. the redo log switches occurred at 30 minute intervals:

Thu May 26 10:28:17 2016
ALTER SYSTEM SET archive_lag_target=1800 SCOPE=MEMORY;
Thu May 26 10:58:08 2016
Thread 1 advanced to log sequence 3
  Current log# 2 seq# 3 mem# 0: /cisdpt/livdpt1/liv_redo1/redo2_1.dbf
  Current log# 2 seq# 3 mem# 1: /cisdpt/livdpt1/liv_redo2/redo2_2.dbf
Thu May 26 11:28:08 2016
Thread 1 advanced to log sequence 4
  Current log# 3 seq# 4 mem# 0: /cisdpt/livdpt1/liv_redo1/redo3_1.dbf
  Current log# 3 seq# 4 mem# 1: /cisdpt/livdpt1/liv_redo2/redo3_2.dbf


I noticed that the Oracle 9 database had db_writer_processes set to 1 whereas in the Oracle 11.2.0.4 database, it was set to 2. I changed it to 1 but this made no difference either. At this point I decided this was taking more time than I wanted to spend on it so I stopped. If anybody has any idea where I am going wrong, perhaps they could let me know.

Thursday, June 02, 2016

ORA-01109

I read that you cannot take a tablespace offline if the database is only mounted so I decided to test this in an Oracle 11.2.0.4 database. I mounted the database and tried to take the USERS tablespace offline. Oracle returned an ORA-01109. I opened the database then I was able to take the tablespace offline:
 
SQL> startup mount
ORACLE instance started.

Total System Global Area  521936896 bytes
Fixed Size                  2252448 bytes
Variable Size             310378848 bytes
Database Buffers          201326592 bytes
Redo Buffers                7979008 bytes
Database mounted.
SQL> alter tablespace users offline
  2  /
alter tablespace users offline
*
ERROR at line 1:
ORA-01109: database not open

SQL> alter database open
  2  /

Database altered.

SQL> alter tablespace users offline
  2  /

Tablespace altered.

SQL> alter tablespace users online
  2  /

Tablespace altered.

SQL>