Oracle 11g


Upgrade to 19c: only 6 months left with support from Oracle

Oracle updated on 7 June 2023 the Release Schedule of Current Database Releases (Doc ID 742060.1) was updated, clarifying that only until December 2023 is possible to get support from Oracle ACS to perform the Upgrade the databases to the version 19c, the current stable release.

Below the updated slide from Oracle showing the support timelines of each version. Oracle 19c has also “Waived Extended Fee” support until 30 April 2025, even if that is not yet marked in the slide.

Don’t miss this opportunity!


Explore multiple optimizer features and fixes with SQLT Xplore

Yesterday I described how I come to SQLT Xplore and it helped me to find out, on Oracle 12.2.0.1, that optimizer_features_enable=8.1.3 decreased the parsing time of a query from 5 seconds to 0.2 seconds. Today I show how to use SQLT Xplore.

What is SQLT Xplore?

SQL Xplore automatises the test of almost 2’000 optimizer parameters and bug fixes control against one query, allowing to discover which parameter was eventually the reason of a performance regression.

(more…)

Long parsing on Oracle 12.2 and the discovery of SQLT Xplore

Today I discovered a fantastic free tool from Oracle: SQLT Xplore!

At a client I had one query that took long time parsing. The tkprof result of the 10046 trace showed it took 5 seconds to parse:

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      5.17       5.30          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        1      0.00       0.00          0        665          0          26
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      5.17       5.30          0        665          0          26

Looking around I’ve noticed that disabling the cost based transformation:

ALTER SESSIONS SET "_optimizer_cost_based_transformation"=off;  

…the query was much faster to parse: only 0,5 seconds.

(more…)

Which OPatch to use? One OPatch version fits (almost) all.

The download page of Oracle OPatch has quite some room for improvement: put some ‘order by’ on the version and platform would be welcome. Also, make clear that there are very few versions of it.

In fact, for database, there are just two versions of OPatch! One OPatch version that covers all database supported versions from 12.1 to 20c. For paid long-term supported Oracle 11.2 there is another version.

So, in summary, here the OPatch version you need to patch your DBs:

Oracle Database versionOPatch version
11.211.2.x
12.112..2.x
12.212..2.x
18c12..2.x
19c12..2.x
20c12..2.x

The download link is this one:

https://support.oracle.com/epmos/faces/ui/patch/PatchDetail.jspx?patchId=6880880

For Enterprise Manager (middleware) there is another OPatch version, 13.9.x which I don’t have experience with.

The information about which OPatch versions is needed to apply the Database RU, RUR, is now part of the Patch Availability Document. For instance for OCtober 2020, this is what we can see:

Note 1: For Enterprise Manager (middleware) there is another OPatch version, 13.9.x which I don’t have experience with.

Note 2 – for Oracle guys out there: when we see the current size of the Release Updates, maybe it would be worth to include the latest version of OPatch within it. It would not increase so much the size and avoid the need of checking if we have the latest OPatch.


Oracle 11.1 on Windows – ORA-02778

I’ve a funny challenge these days:

Recover a Oracle 11.1.0.6 on Windows installation. I’ve all files in an external drive and I’m trying to make the DB to run on my laptop.

  • I’ve copied to my local disk;
  • Added the entries to the registry that found necessary
  • Re-created the Inventory
  • Re-created a init file with correct locations from the spfile

Now I had still had the error:

SQL> startup nomount
ORA-02778: Name given for the log directory is invalid

Nothing on the logs, all directories mentioned on pfile exist and are writable.

I’ve downloaded the Microsoft ProcessMonitor, configured to trace only Oracle company processes and looked what it tried to read.

Finally I found the culprit: %ORACLE_HOME%\RDBMS\log was missing. Just the log subfolder.

This was during the xcopy to the local disk, I excluded all “log” files, as there were some huge ones. Unfortunately it also excluded this folder. Now looks better:

SQL> startup nomount
ORACLE instance started.

Total System Global Area 1720328192 bytes
Fixed Size                  2130160 bytes
Variable Size             989859600 bytes
Database Buffers          721420288 bytes
Redo Buffers                6918144 bytes
SQL>

Average size of set of rows from a table

Sometimes users have strange requests that I avoid asking the reason.

Today a user asked me how he could find the average size of a set of rows inside a table. Not only for one column and not for the whole table.

This reminded me from university times, when doing SQL exercises.

With Oracle 11g and listagg function, it is quite easy to fulfill the request:

set serveroutput on;

declare
  col_list varchar(2500);
  result number;
begin
select 'select round('||listagg('nvl(avg(length('||column_name||')),0)','+') within group (order by column_id)||') 
  from MYAPP.mytable
  where creationdate > to_date(''01.01.2018'',''dd.mm.yyyy'') 
    and creationdate < to_date(''31.12.2018'',''dd.mm.yyyy'')' into col_list 
from all_tab_columns where owner='MYAPP' and table_name='MYTABLE' ;
execute immediate (col_list) into result;
dbms_output.put_line(result);
end;
/

DGMGRL error: Object “a1021p_site1” was not found

While testing some scripts, I was getting an error on DGMGRL, while querying some databases:

DGMGRL> show database A1021P_SITE1
Object "a1021p_site1" was not found

But it would work if I put single quotes around:

DGMGRL> show database 'A1021P_SITE1'

Database - A1021P_SITE1

  Role:            PRIMARY
  Intended State:  TRANSPORT-ON
  Instance(s):
    A1021P

Database Status:
SUCCESS

The problem is on the way the database was added to the configuration, and it is quite simple to fix it…

  1. after disabling configuration
  2. and connecting with password to the database
  3. then just rename the database to a name without using quotes.
DGMGRL> edit database 'A1021P_SITE1' rename to a1021p_site1;
Error: ORA-16602: database must be disabled to perform this operation
Failed.

DGMGRL> disable fast_start failover;
Disabled.

DGMGRL> disable configuration
Disabled.

DGMGRL> edit database 'A1021P_SITE1' rename to a1021p_site1;
ORA-16541: database is not enabled
Configuration details cannot be determined by DGMGRL

DGMGRL> edit database 'A1021P_SITE1' rename to a1021p_site1;
ORA-16541: database is not enabled
Configuration details cannot be determined by DGMGRL

DGMGRL> connect sys/XXXX@a1021p_site1
Connected.

DGMGRL> edit database 'A1021P_SITE1' rename to a1021p_site1;
Succeeded.

DGMGRL> show database A1021P_SITE1

Database - a1021p_site1

  Role:            PRIMARY
  Intended State:  TRANSPORT-ON
  Instance(s):
    A1021P

Database Status:
DISABLED

DGMGRL> enable configuration;
Enabled.

DGMGRL> enable fast_start failover
Enabled.

DGMGRL> show configuration

Configuration - A1021P

  Protection Mode: MaxAvailability
  Databases:
    a1021p_site1 - Primary database
    a1021p_site2 - (*) Physical standby database

Fast-Start Failover: ENABLED

Configuration Status:
SUCCESS

Index columns query

Query to show all indexes, its characteristics and respective columns from a table.
Note: It does not filter on owner.

select c.index_name,
       c.table_name||'('||listagg(column_name,',') within group (order by column_position)||')' as columns,
       decode(status,'VALID',null,status||' ')
       ||decode(visibility,'VISIBLE',null,visibility||' ')
       ||decode(segment_created,'YES',null,'EMPTY ')
       ||decode(uniqueness,'NONUNIQUE',null,uniqueness||' ')
       ||funcidx_status as STATUS
 from dba_ind_columns c, dba_indexes i
where i.table_name=c.table_name and i.index_name=c.index_name
  and c.table_name='&TABLE_NAME'
group by c.table_name,c.index_name,i.status,visibility, segment_created,uniqueness,funcidx_status
order by c.table_name,c.index_name;

Example of output:

INDEX_NAME                     COLUMNS                                 STATUS  
------------------------------ --------------------------------------- ----------
IDX_LIB_VERSION_PROFILE_ID     LIB_VERSION(PROFILE_ID)                       
INX_LIB_VERSION_SRC            LIB_VERSION(DATA_RESOURCE,SOURCE)             
LIB_VERSION_NAME_IDX           LIB_VERSION(VERSION_NAME)                     
LIB_VERSION_PK                 LIB_VERSION(ID)                         UNIQUE
LIB_VERSION_UK1                LIB_VERSION(PROFILE_ID,VERSION_NAME)    UNIQUE

Dataguard – convert from MaxPerformance to MaxAvailability

We just found that one of the setups was wrongly configured as MaxPerformance. Since Oracle 11.2 it is possible to increase the protection mode without restarting the primary (if going from MaxPerformance to MaxProtection you need to do in two steps, through MaxPerformance).

Here how to move from MaxPerformance to MaxAvailability in a config with DataGuard Broker (removed some lines to make it shorter):

DGMGRL> connect /
Connected.

DGMGRL> show configuration

Configuration - azores

  Protection Mode: MaxPerformance
  Databases:
    azores_site1 - Primary database
    azores_site2 - (*) Physical standby database

Fast-Start Failover: ENABLED

Configuration Status:
SUCCESS

DGMGRL> EDIT CONFIGURATION SET PROTECTION MODE AS MAXAVAILABILITY;
Error: ORA-16654: fast-start failover is enabled

Failed.

DGMGRL> stop observer
Done.
DGMGRL> show configuration

Configuration - azores

  Protection Mode: MaxPerformance
  Databases:
    azores_site1 - Primary database
      Warning: ORA-16819: fast-start failover observer not started

    azores_site2 - (*) Physical standby database
      Warning: ORA-16819: fast-start failover observer not started

Fast-Start Failover: ENABLED

Configuration Status:
WARNING

DGMGRL>  DISABLE FAST_START FAILOVER
Disabled.
DGMGRL> show configuration

Configuration - azores

  Protection Mode: MaxPerformance
  Databases:
    azores_site1 - Primary database
    azores_site2 - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

DGMGRL> EDIT CONFIGURATION SET PROTECTION MODE AS MAXAVAILABILITY;
Error: ORA-16627: operation disallowed since no standby databases would remain to support protection mode
Failed.

DGMGRL> show database verbose azores_site1

Database - azores_site1

  Role:            PRIMARY
  Intended State:  TRANSPORT-ON
  Instance(s):
    azores

  Properties:
    DGConnectIdentifier             = 'azores_site1.portugal'
    ObserverConnectIdentifier       = ''
    LogXptMode                      = 'async'
	...
	
Database Status:
SUCCESS

DGMGRL> show database verbose azores_site2

Database - azores_site2

  Role:            PHYSICAL STANDBY
  Intended State:  APPLY-ON
  Transport Lag:   0 seconds (computed 2 seconds ago)
  Apply Lag:       0 seconds (computed 2 seconds ago)
  Apply Rate:      1.84 MByte/s
  Real Time Query: OFF
  Instance(s):
    azores

  Properties:
    DGConnectIdentifier             = 'azores_site2.portugal'
    ObserverConnectIdentifier       = ''
    LogXptMode                      = 'async'
	...
	
Database Status:
SUCCESS

DGMGRL> edit database azores_site2 set state=APPLY-OFF;
Succeeded.
DGMGRL> edit database azores_site2 set property LogXptMode='SYNC';
Property "logxptmode" updated
DGMGRL> edit database azores_site1 set property LogXptMode='SYNC';
Property "logxptmode" updated
DGMGRL> EDIT CONFIGURATION SET PROTECTION MODE AS MAXAVAILABILITY;
Succeeded.
DGMGRL> show configuration;

Configuration - azores

  Protection Mode: MaxAvailability
  Databases:
    azores_site1 - Primary database
    azores_site2 - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

DGMGRL> enable fast_start failover;
Enabled.

DGMGRL> edit database azores_site2 set state=APPLY-ON;
Succeeded.

DGMGRL> show database verbose azores_site2

Database - azores_site2

  Role:            PHYSICAL STANDBY
  Intended State:  APPLY-ON
  Transport Lag:   0 seconds (computed 1 second ago)
  Apply Lag:       0 seconds (computed 1 second ago)

Database Status:
SUCCESS

DGMGRL> show configuration;

Configuration - azores

  Protection Mode: MaxAvailability
  Databases:
    azores_site1 - Primary database
    azores_site2 - (*) Physical standby database

Fast-Start Failover: ENABLED

Configuration Status:
SUCCESS

Index Logical Corruption!

Arriving to my client on Monday, I see the local DBA already with two people behind and him concerned about something. He was doing a new failover of a critical database that had problems during the weekend due to a change on a firewall. The timeline was somehow like this:

  • Friday afternoon – firewall change
  • Saturday morning – many syslog errors and almost impossible to use the DB (due to audit to OS level)
  • Saturday afternoon – failover of the database to secondary site + set syslogd to restart every 5 minutes
  • Sunday morning – reinstate old primary as standby (firewall change was fixed/rolledback)
  • Monday morning – failover of database to primary site + reinstate DB on secondary site

All looked back fine by end of the morning, we were happy that the reinstate worked fine, even though it was necessary to do it using sqlplus, as the broker was giving errors.

Monday midday the users complaint they are receiving ORA-01555 for some queries.

Mon Aug 20 11:21:35 2018
ORA-01555 caused by SQL statement below (SQL ID: cg61q9avtk3ta, Query Duration=1 sec, SCN: 0x01a9.3ae6da9e):
delete from child_table where parent_id = :parent and child_id in (:child1)

We check alertlog and see the ORA-01555 on two queries filtering on the same key-value pair. Probably a wrong plan was generated and undo exploded, I think. But the undo tablespace is not even half used. And query duration is 1 second, on the alertlog message.

This is strange. Also more strange is that few time after we also get ORA-00600 on the alertlog:

Mon Aug 20 16:16:54 2018
Errors in file /u00/app/oracle/diag/rdbms/db1_site1/DB1/trace/DB1_ora_13217.trc  (incident=1543593):
ORA-00600: internal error code, arguments: [ktbdchk1: bad dscn], [], [], [], [], [], [], [], [], [], [], []
Incident details in: /u00/app/oracle/diag/rdbms/db1_site1/DB1/incident/incdir_1543593/DB1_ora_13217_i1543593.trc

This is case for Metalink. And there we find the reason:

ALERT Bug 22241601 ORA-600 [kdsgrp1] / ORA-1555 / ORA-600 [ktbdchk1: bad dscn] / ORA-600 [2663] due to Invalid Commit SCN in INDEX (Doc ID 1608167.1)

Index corruption, nice. It could be worse, the workaround is to recreate the problematic indexes.

Ok, then we should run DB Verify to validate all indexes. There is this new option to run DBV on specific segments, which is great, and DBV was even enhanced to find this specific corruption:

Bug 7517208 – DBV enhanced to identify Logical SCN Block corruptions (Doc ID 7517208.8)

But trying to do on datafiles on ASM is not easy, does not seem to work… until we discover that:

Bug 18353157 – DBVERIFY (DBV) does not show SCN mismatch for datafiles in ASM (Doc ID 18353157.8)

Next step is to try to do with a simple ANALYZE INDEX. After some minutes the alert log starts showing a lot of:

Tue Aug 21 10:36:43 2018
ORA-01555 caused by SQL statement below (SQL ID: 6c3gh913k55pr, SCN: 0x01a9.3b4ea9e1):
ANALYZE INDEX USER1.IX_ABC VALIDATE STRUCTURE ONLINE
ORA-01555 caused by SQL statement below (SQL ID: 6c3gh913k55pr, SCN: 0x01a9.3b4ea9ee):
ANALYZE INDEX USER1.IX_ABC VALIDATE STRUCTURE ONLINE
…

Unfortunately the errors do not appear on sqlplus, so we need to have a tail -f on the alertlog, keep the eyes open and then cancel the ANALYZE INDEX.

As there seemed to be corruption to several indexes of USER1, we decided that it was just easier to directly rebuild all of them. The index rebuild need to be ONLINE. Not because we can’t support having locks on the tables of the application, but because only the ONLINE rebuild will do a full table scan to recreate the index. A normal rebuild uses the index itself, which is not good when it has some corruption.

One mistake we did at some point was to ANALYZE the primary key of one table and start an INDEX REBUILD ONLINE of an index on the same table. The ANALYZE and INDEX REBUILD, were locking each other (or advancing very slowly). So it is better not to perform both at the same time.

When we tried to rebuild online the primary key, we got the error:

ORA-08108: may not build or rebuild this type of index online

First we tought it would be due the fact it is a Primary Key, but soon we tumbled upon Jonnathan Lewis blog entry about this: https://jonathanlewis.wordpress.com/2012/09/04/online-rebuild-2/

Our Primary key is DEFERRABLE! Oups! The only option is now to change or deactivate the constraint, rebuild the index and go on. But this is not possible with the application running. So it implies an emergency change and a lot of actions. Here the technical summary on this Oracle Restart environment:

-- Stop service:
$ srvctl stop service -d db1_site1 -s db1_service_rw
$ srvctl status service -d db1_site1

-- Kill sessions using service and/or from users:
set pages 0
spool /tmp/to_kill.sql
select 'alter system disconnect session '''||sid||','||serial#||''' immediate;' from v$session where service_name='DB1_SERVICE_RW';
spool off

$ vi /tmp/to_kill.sql

SQL> @/tmp/to_kill.sql

-- Confirm no other sessions:
SQL> select username, program,machine from v$session where service_name='DB1_SERVICE_RW';

-- Recreate index:
SET TIME ON
SET TIMING ON
ALTER SYSTEM SET job_queue_processes=0;
ALTER SESSION ENABLE PARALLEL DDL;
ALTER TABLE user1.child_table DISABLE CONSTRAINT pk_child_table KEEP INDEX;
ALTER INDEX user1.pk_child_table REBUILD ONLINE PARALLEL 12;
ALTER TABLE user1.child_table ENABLE CONSTRAINT pk_child_table;
ALTER INDEX user1.pk_child_table NOPARALLEL;
ALTER SYSTEM SET job_queue_processes=10;

-- Restart service:
$ srvctl start service -d db1_site1 -s db1_service_rw
$ srvctl status service -d db1_site1

After all this finally we hoped that our database was back to normal. Application reconnects, we cross our fingers and since then no more errors. We are happy that this was just a light corruption.