Monday, July 18, 2016

ORA-39710 and ORA-00704

I tried to use dbua to upgrade a database from Oracle 11.2.0.4 to Oracle 12.1.0.2. Part way through, my PC lost connection with the UNIX server hosting the database. I tried to connect to the database but got an ORA-39710 so I forced the database to close with shutdown abort:

NLFINUT1 /export/home/oracle > sqlplus /

SQL*Plus: Release 12.1.0.2.0 Production on Mon Jul 18 14:03:08 2016

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

ERROR:
ORA-39710: only connect AS SYSDBA is allowed when OPEN in UPGRADE mode

Enter user-name: / as sysdba

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> shutdown abort
ORACLE instance shut down.
SQL>


A colleague restored the database for me. dbua had updated /var/opt/oracle/oratab to show the new database version so I changed it back to 11.2.0.4:

NLFINUT1:/oracle/app/oracle/product/11.2.0.4:N

However, I forgot to rerun . oraenv to pick up the old Oracle software so when I tried to open the database, I got an ORA-00704:

NLFINUT1 /export/home/oracle > sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Mon Jul 18 14:13:38 2016

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area  650117120 bytes
Fixed Size                  2917400 bytes
Variable Size             230693864 bytes
Database Buffers          411041792 bytes
Redo Buffers                5464064 bytes
Database mounted.
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00704: bootstrap process failure
ORA-00604: error occurred at recursive SQL level 2
ORA-00904: "I"."UNUSABLEBEGINNING#": invalid identifier
Process ID: 21064
Session ID: 212 Serial number: 60960

SQL>


I reran . oraenv then I was able to open the database:

NLFINUT1 /export/home/oracle > sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Mon Jul 18 14:52:08 2016

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area  622338048 bytes
Fixed Size                  2184112 bytes
Variable Size             327158864 bytes
Database Buffers          285212672 bytes
Redo Buffers                7782400 bytes
Database is aangekoppeld.
Database is geopend.
SQL>


(It is used in the Netherlands so some of the messages are in Dutch.)

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.