Purging ADR home of upgraded Grid Infrastructure might fail

The second blog post today is about housekeeping. I typically use the “purgeLogs” script from MOS Doc-ID 2081655.1. It was intially designed for engineered systems but it works well on classic systems too. But today I found an error in the output:

2019-06-21 00:00:06: I adrci GI purging diagnostic destination diag/crs/odax6m/crs
2019-06-21 00:00:06: I ... purging ALERT older than 8 days
2019-06-21 00:00:06: W Not able to purge ALERT, due to error: DIA-49803: Purge not possible due to incompatible schema version.

Typically this happens, when you use an “adrci” that does not match the ADR home you are trying to purge. That’s maybe the reason, why “purgeLogs” tries different adrci’s to purge the RDBMS homes.
But what is wrong here? Let’s try the manual way.

oracle@odax6m ~> adrci

ADRCI: Release 18.0.0.0.0 - Production on Fri Jun 21 13:53:17 2019

Copyright (c) 1982, 2018, Oracle and/or its affiliates.  All rights reserved.

ADR base = "/u01/app/oracle"
adrci> set home crs
adrci> purge
DIA-49803: Purge not possible due to incompatible schema version.

When I put that message in MOS, I quickly found this bug: Bug 28375106 – ADRCI Purge of Grid Home Fails With “DIA-49803: Purge not possible due to incompatible schema version” After Upgrade from 12.1 (Doc ID 28375106.8). So I crosschecked that.

adrci> show version schema
Schema version (on-disk): 107
Schema version (library): 110

And indeed, the versions are different. Obviously the issue is related to
upgrades. In my case this is an Oracle Database Appliance, that has been patched from 18.3 to 18.5. Unfortunately MOS has no workaround for this. But let’s check what “adrci” may can do for me.

adrci> help extended

 HELP [topic]
   Available Topics:
        BEGIN BACKUP
        CD
        CREATE STAGING XMLSCHEMA
        CREATE VIEW
        DDE
        DEFINE
        DELETE
        DESCRIBE
        DROP VIEW
        END BACKUP
        INSERT
        LIST DEFINE
        MERGE ALERT
        MERGE FILE
        MERGE LOG
        MIGRATE
        QUERY
        REPAIR
        SET COLUMN
        SHOW CATALOG
        SHOW DUMP
        SHOW SECTION
        SHOW TRACE
        SHOW TRACEMAP
        SWEEP
        UNDEFINE
        UPDATE
        VIEW

The “MIGRATE” sounds interresting.

adrci> help migrate

  Usage: MIGRATE [RELATION  | SCHEMA] [-DOWNGRADE | -RECOVER]

  Purpose: Upgrades the relation or schema to the latest version

  Options:
    [-downgrade] Downgrade schema to previous version
    [-recover]   Recover schema from failed migrate

  Examples:
    migrate schema
    migrate relation incident

Sounds like I do want to do exactly this. Give it a try.

adrci> migrate schema
Schema migrated.
adrci> show version schema
Schema version (on-disk): 110
Schema version (library): 110

And voilĂ , now I can purge the Grid Inftrastructure ADR home again.

adrci> purge
adrci>

Since the bug is about the behaviour after upgrading from 12.1, this might apply to other upgrade or update paths too. Be aware of that and monitor your housekeeping scripts. And check your ADR schema version after you upgraded or patched your Grid Infrastructure installation.

Advertisements

Duplicate Column Names?

Recently, at a customers site, we stumbled upon an error in one of the databases alert.log. It turned out, that it was caused by the nightly statistics gathering job. There was one table for which statistics could not be generated.

SQL> exec dbms_stats.gather_table_stats('BASIS','VG_XXXXXXXXXXX', method_opt=>'for all columns size 1');

BEGIN dbms_stats.gather_table_stats('BASIS','VG_XXXXXXXXXXX', method_opt=>'for all columns size 1'); END;
 
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-00001: unique constraint (SYS.I_WRI$_OPTSTAT_HH_OBJ_ICOL_ST) violated
ORA-06512: at "SYS.DBMS_STATS", line 34850
ORA-06512: at line 1

So we first tried to remove the statistics, but we were unable to.

SQL> exec dbms_stats.delete_table_stats('BASIS','VG_XXXXXXXXXXX');
BEGIN dbms_stats.delete_table_stats('BASIS','VG_XXXXXXXXXXX'); END;
 
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-00001: unique constraint (SYS.I_WRI$_OPTSTAT_HH_OBJ_ICOL_ST) violated
ORA-06512: at "SYS.DBMS_STATS", line 17279
ORA-06512: at line 1
 
SQL> exec dbms_stats.delete_table_stats('BASIS','VG_XXXXXXXXXXX',force=>true);
BEGIN dbms_stats.delete_table_stats('BASIS','VG_XXXXXXXXXXX',force=>true); END;
 
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-00001: unique constraint (SYS.I_WRI$_OPTSTAT_HH_OBJ_ICOL_ST) violated
ORA-06512: at "SYS.DBMS_STATS", line 17279
ORA-06512: at line 1

So we started to dig deeper into that issue. We found out, that there is a duplicate column name when querying the Data Dictionary. But on the other hand, a simple “describe” does not show that.

SQL> desc owner.vg_xxxxxxxxxx

Name                                                  Null?    Type
----------------------------------------------------- -------- -----------------
[...]
XXXXXXXXXXXXXXX_ID                                             NUMBER(19)
XXXXXXXXXXXXXXXXX_XXX                                          NUMBER(19)
XXXXXXXXXXXXXXXXX_ID                                           NUMBER(19)
NAMENSZUSATZ                                                   NVARCHAR2(4000)
ENTITY_TIMESTAMP                                               TIMESTAMP(9)
OPTLOCKVERSION                                        NOT NULL NUMBER(19)
DATENUEBERNAHMETYP                                             NVARCHAR2(4000)
ZUGANGGEBUCHT                                         NOT NULL NUMBER(1)
UEBERSTELLER                                          NOT NULL NUMBER(1)
XXXXXXXXXXXXXX                                        NOT NULL NUMBER(1)
 
 
SQL> select column_id, column_name  from dba_tab_columns where table_name='VG_XXXXXXXXXXX' and column_name like 'Z%'
 
COLUMN_ID  COLUMN_NAME
---------- --------------------
        59 ZUGANGGEBUCHT
        59 ZUGANGGEBUCHT

Next step was to find out, where the data from the dictionary view is comming from.

SQL> select text from dba_views where view_name='DBA_TAB_COLUMNS';

TEXT
--------------------------------------------------------------------------------
select OWNER, TABLE_NAME,
       COLUMN_NAME, DATA_TYPE, DATA_TYPE_MOD, DATA_TYPE_OWNER,
       DATA_LENGTH, DATA_PRECISION, DATA_SCALE, NULLABLE, COLUMN_ID,
       DEFAULT_LENGTH, DATA_DEFAULT, NUM_DISTINCT, LOW_VALUE, HIGH_VALUE,
       DENSITY, NUM_NULLS, NUM_BUCKETS, LAST_ANALYZED, SAMPLE_SIZE,
       CHARACTER_SET_NAME, CHAR_COL_DECL_LENGTH,
       GLOBAL_STATS, USER_STATS, AVG_COL_LEN, CHAR_LENGTH, CHAR_USED,
       V80_FMT_IMAGE, DATA_UPGRADED, HISTOGRAM, DEFAULT_ON_NULL,
       IDENTITY_COLUMN, SENSITIVE_COLUMN,
       EVALUATION_EDITION, UNUSABLE_BEFORE, UNUSABLE_BEGINNING
  from DBA_TAB_COLS
where USER_GENERATED = 'YES'


SQL> select text from dba_views where view_name='DBA_TAB_COLS';

TEXT
--------------------------------------------------------------------------------
select
     OWNER, TABLE_NAME,
     COLUMN_NAME, DATA_TYPE, DATA_TYPE_MOD, DATA_TYPE_OWNER,
     DATA_LENGTH, DATA_PRECISION, DATA_SCALE, NULLABLE, COLUMN_ID,
     DEFAULT_LENGTH, DATA_DEFAULT, NUM_DISTINCT, LOW_VALUE, HIGH_VALUE,
     DENSITY, NUM_NULLS, NUM_BUCKETS, LAST_ANALYZED, SAMPLE_SIZE,
     CHARACTER_SET_NAME, CHAR_COL_DECL_LENGTH,
     GLOBAL_STATS,
     USER_STATS, AVG_COL_LEN, CHAR_LENGTH, CHAR_USED,
     V80_FMT_IMAGE, DATA_UPGRADED, HIDDEN_COLUMN, VIRTUAL_COLUMN,
     SEGMENT_COLUMN_ID, INTERNAL_COLUMN_ID, HISTOGRAM, QUALIFIED_COL_NAME,
     USER_GENERATED, DEFAULT_ON_NULL, IDENTITY_COLUMN, SENSITIVE_COLUMN,
     EVALUATION_EDITION, UNUSABLE_BEFORE, UNUSABLE_BEGINNING
from dba_tab_cols_v$


SQL> select text from dba_views where view_name='DBA_TAB_COLS_V$';

TEXT
--------------------------------------------------------------------------------
select u.name, o.name,
       c.name,

[...]

Ok, DBA_TAB_COLUMNS gets its data from DBA_TAB_COLS which in turn gets its data from DBA_TAB_COLS_V$. That view has the final SQL, that is being used to retrieve the data, I shortened it, you get the idea.
We then used the SQL from DBA_TAB_COLS_V$ to find the root cause of our duplicate column.

SQL> select
  2    , sys."_CURRENT_EDITION_OBJ" o
  3    , sys.hist_head$ h
  4    c.col#, c.obj#, c.name,  c.intcol#
  5    , sys.tab$ t
  6  where o.obj# = c.obj#
  7    , h.obj#, h.intcol#
  8  from sys.col$ c
  9        or
 10        (o.type# = 2     /* tables, excluding iot - overflow and nested tables */
 11          and
 12   , sys."_CURRENT_EDITION_OBJ" o
 13                         and (bitand(t.property, 512) = 512 or
 14                              bitand(t.property, 8192) = 8192))))
 15    , sys.hist_head$ h
 16    , sys.user$ u
 17    , sys.coltype$ ac
 18    , sys.obj$ ot
 19    , sys."_BASE_USER" ut
 20    , sys.tab$ t
 21  where o.obj# = c.obj#
 22    and o.owner# = u.user#
 23    and o.obj# = t.obj#(+)
 24    and c.obj# = h.obj#(+) and c.intcol# = h.intcol#(+)
 25    and c.obj# = ac.obj#(+) and c.intcol# = ac.intcol#(+)
 26    and ac.toid = ot.oid$(+)
 27    and ot.type#(+) = 13
 28    and ot.owner# = ut.user#(+)
 29    and (o.type# in (3, 4)                                     /* cluster, view */
 30         or
 31         (o.type# = 2     /* tables, excluding iot - overflow and nested tables */
 32          and
 33          not exists (select null
 34                        from sys.tab$ t
 35                       where t.obj# = o.obj#
 36                         and (bitand(t.property, 512) = 512 or
 37                              bitand(t.property, 8192) = 8192))))
 38    and c.name='ZUGANGGEBUCHT';

      COL#       OBJ# NAME                    INTCOL#       OBJ#    INTCOL#
---------- ---------- -------------------- ---------- ---------- ----------
        60      95556 ZUGANGGEBUCHT                60
        68      95523 ZUGANGGEBUCHT                68
        59      94177 ZUGANGGEBUCHT                59      94177         59   
        59      94177 ZUGANGGEBUCHT                59      94177         59   
        74      95522 ZUGANGGEBUCHT                74
        74      95550 ZUGANGGEBUCHT                74

6 rows selected.

Have a look at the highlighted rows, there’s the duplicate column. Obviously the join to HIST_HEAD$ introduced the second row. That means, there must be something wrong inside that table. And that turned out to be true.

SQL> desc hist_head$
Name                        Null?    Type
--------------------------- -------- -------------
OBJ#                        NOT NULL NUMBER
COL#                        NOT NULL NUMBER
BUCKET_CNT                  NOT NULL NUMBER
ROW_CNT                     NOT NULL NUMBER
CACHE_CNT                            NUMBER
NULL_CNT                             NUMBER
TIMESTAMP#                           DATE
SAMPLE_SIZE                          NUMBER
MINIMUM                              NUMBER
MAXIMUM                              NUMBER
DISTCNT                              NUMBER
LOWVAL                               RAW(1000)
HIVAL                                RAW(1000)
DENSITY                              NUMBER
INTCOL#                     NOT NULL NUMBER
SPARE1                               NUMBER
SPARE2                               NUMBER
AVGCLN                               NUMBER
SPARE3                               NUMBER
SPARE4                               NUMBER

SQL> select  COL#, BUCKET_CNT, ROW_CNT, NULL_CNT, TIMESTAMP#, SAMPLE_SIZE from  hist_head$ where obj#=94177 and intcol#=59;

      COL# BUCKET_CNT    ROW_CNT   NULL_CNT TIMESTAMP#          SAMPLE_SIZE
---------- ---------- ---------- ---------- ------------------- -----------
         0          1          0      35656 2017-01-05 22:00:23        5502
         0          1          0      35383 2017-01-05 22:00:23        5775

A quick MOS-search revealed two notes, that describe the issue, Alter Table Drop Column Failing With ORA-00600[16515] (Doc ID 2375301.1) and DBMS_STATS.DELETE_TABLE_STATS Fails With ORA-600 [16515] (Doc ID 1233745.1). The latter one has the final solution for this issue.
We first identified the older of the two rows which we then deleted.

SQL> SELECT rowid,obj#,intcol#,timestamp# FROM hist_head$ WHERE obj#=94177 and intcol#=59 order by timestamp#;

ROWID                    OBJ#    INTCOL# TIMESTAMP#
------------------ ---------- ---------- -------------------
AAAABEAABAAAWh0AAj      94177         59 2017-01-05 22:00:23
AAAABEAABAAAWh0AAi      94177         59 2017-01-05 22:00:23

SQL> DELETE FROM hist_head$ WHERE ROWID='AAAABEAABAAAWh0AAj';

1 row deleted.

SQL>  COMMIT;

Commit complete.

After this, DBA_TAB_COLUMNS is back to distinct column names.

SQL> select column_id, column_name  from dba_tab_columns where table_name='VG_XXXXXXXXXXX' and column_name like 'Z%';

COLUMN_ID COLUMN_NAME
---------- --------------------
        59 ZUGANGGEBUCHT

And of cause, the statistics gathering was and is running fine again.

Auto Resize and Shrink ACFS Volumes

Today I want to write a little about ACFS. In case you are using the latest and greatest Oracle Grid Infrastructure and you still run 11.2 databases, you are required to use ACFS. This especially applies to the Database Appliances, ExaDatas and for the Cloud Services, if you use Grid Infrastructure there. Having that, you might run into situations where your ACFS filesystem grows which it does automatically. But what if you want to shrink the filesystem to it’s original or at least a smaller size? With version 18.3 this should work quite well. Version before did had some issues when there was no contigous free space at the end of the volume to do the shrinking. Read more about that in MOS Note “ODA (Oracle Database Appliance): Resizing ACFS DBStorage File System (Doc ID 1990430.1)“.
Basically, there are two major topics. First, the shrinking of ACFS volumes and second, limiting the auto resize of ACFS.

Current situation

Given a customers ODA, this is the current situation.

[root@oda-x7m ~]# df -h
Filesystem            Size  Used Avail Use% Mounted on
/dev/mapper/VolGroupSys-LogVolRoot
                       30G  3.4G   25G  13% /
tmpfs                 189G  1.3G  187G   1% /dev/shm
/dev/md0              477M   40M  412M   9% /boot
/dev/sda1             500M  320K  500M   1% /boot/efi
/dev/mapper/VolGroupSys-LogVolOpt
                       59G   24G   33G  43% /opt
/dev/mapper/VolGroupSys-LogVolU01
                       99G   78G   16G  84% /u01
/dev/asm/commonstore-241
                      5.0G  319M  4.7G   7% /opt/oracle/dcs/commonstore
/dev/asm/dattfstar01-241
                      170G  158G   13G  93% /u02/app/oracle/oradata/TFSTAR01
/dev/asm/reco-135     883G  693G  191G  79% /u03/app/oracle
/dev/asm/datxvgvg-241
                      100G  5.9G   95G   6% /u02/app/oracle/oradata/XVGVG
/dev/asm/datxdovg-241
                      410G  399G   12G  98% /u02/app/oracle/oradata/XDOVG
/dev/asm/datxvgovg-241
                      100G  5.7G   95G   6% /u02/app/oracle/oradata/XVGOVG
/dev/asm/datxdoovg-241
                      390G  376G   15G  97% /u02/app/oracle/oradata/XDOOVG
/dev/asm/datxvgfg-241
                      100G  5.1G   95G   6% /u02/app/oracle/oradata/XVGFG
/dev/asm/datxdofg-241
                      100G   46G   55G  46% /u02/app/oracle/oradata/XDOFG
/dev/asm/datsfstar01-241
                      100G  6.3G   94G   7% /u02/app/oracle/oradata/SFSTAR01
/dev/asm/dattvgfg01-241
                      100G  5.1G   95G   6% /u02/app/oracle/oradata/TVGFG01
/dev/asm/dattfstar03-241
                      100G  4.9G   96G   5% /u02/app/oracle/oradata/TFSTAR03
/dev/asm/dattfstar04-241
                      100G  7.0G   94G   7% /u02/app/oracle/oradata/TFSTAR04
/dev/asm/dattfstar02-241
                      1.9T  1.9T   76M 100% /u02/app/oracle/oradata/TFSTAR02
/dev/asm/dattdomea01-241
                      100G   20G   81G  20% /u02/app/oracle/oradata/TDOMEA01
/dev/asm/dattfstar05-241
                      100G  5.2G   95G   6% /u02/app/oracle/oradata/TFSTAR05
/dev/asm/datifstar01-241
                      100G  5.1G   95G   6% /u02/app/oracle/oradata/IFSTAR01A
/dev/asm/datrfstar01-241
                      1.1T  1.1T  9.4G 100% /u02/app/oracle/oradata/RFSTAR01A
jus-srv-rz50.justiz.sachsen.de:/u01/app/DB-dumps
                      3.0T  2.0T  924G  68% /u99/nfs/DB-dumps
/dev/asm/datsfstar02-241
                      100G  3.6G   97G   4% /u02/app/oracle/oradata/SFSTAR02

Shrinking an ACFS Filesystem

As to be seen above, there are quite a lot of databases that have a lot of free space in their filesystems. The initial size is defined by the shapes that an ODA provides. But in our case, even 100GB are too much, so we wanted to reduce the occupied space.
First, we get some basic information.

[oracle@oda-x7m ~]$ /sbin/advmutil volinfo /dev/asm/datxvgvg-241
Device: /dev/asm/datxvgvg-241
Size (MB): 102400
Resize Increment (MB): 512
Redundancy: mirror
Stripe Columns: 8
Stripe Width (KB): 1024
Disk Group: DATA
Volume: DATXVGVG
Compatible.advm: 12.2.0.1.0

The “resize” feature requires the ADVM compatibility to be at least 12.2.0.1, so this is the case. Now let’s do the shrink.

[oracle@oda-x7m ~]$ acfsutil size -h
Usage: acfsutil [-h] size [[-|+]nnn[K|M|G|T|P]] [-a nnn[K|M|G|T|P]] [-x nnn[K|M|G|T|P]] [-d ]  
                                         - Resize file system and configure auto-resize
                [-a]                     - Auto-resize increment
                [-x]                     - Auto-resize maximum
                [-d]                     - Resize only this device

[oracle@oda-x7m ~]$ acfsutil size -80G /u02/app/oracle/oradata/XVGVG
acfsutil size: new file system size: 21474836480 (20480MB)

After that, the volume is at the desired size of 20GB.

[oracle@oda-x7m ~]$ /sbin/advmutil volinfo /dev/asm/datxvgvg-241
Device: /dev/asm/datxvgvg-241
Size (MB): 20480
Resize Increment (MB): 512
Redundancy: mirror
Stripe Columns: 8
Stripe Width (KB): 1024
Disk Group: DATA
Volume: DATXVGVG
Compatible.advm: 12.2.0.1.0

Limiting auto increment of ACFS

The automatic increment of ACFS volumes has two implications. First, it makes monitoring difficult because you never know is the filesystem is really full or will it automatically resize if needed. To figure out, if you are having a critical issue, you need to perform several steps. Implementing this with a monitoring system will be cumbersome in most cases. The other thing is, managing the storage resources is not straight-forward. You might overcommit your storage. My approach and advise is, to disable automatic resizing by setting the ACFS size and maximum size to the value, that you commited for this specific database. Let’s check the setup before doing that.

[oracle@oda-x7m ~]$ acfsutil info fs /u02/app/oracle/oradata/ICM01A
/u02/app/oracle/oradata/ICM01A
    ACFS Version: 12.2.0.1.0
    on-disk version:       46.0
    compatible.advm:       12.2.0.0.0
    ACFS compatibility:    12.2.0.0.0
    flags:        MountPoint,Available,AutoResizeEnabled
    mount time:   Fri Mar 15 10:09:28 2019
    mount sequence number: 4
    allocation unit:       4096
    metadata block size:   4096
    volumes:      1
    total size:   107374182400  ( 100.00 GB )
    total free:   91318923264  (  85.05 GB )
    auto-resize increment:   10737418240  (  10.00 GB )
    auto-resize maximum:     0  ( 0.00 )
    file entry table allocation: 262144
    primary volume: /dev/asm/daticm01-411
        label:
        state:                 Available
        major, minor:          246, 210435
        logical sector size:   512
        size:                  107374182400  ( 100.00 GB )
        free:                  91318923264  (  85.05 GB )
        metadata read I/O count:         54838
        metadata write I/O count:        51022
        total metadata bytes read:       242651136  ( 231.41 MB )
        total metadata bytes written:    233385984  ( 222.57 MB )
        ADVM diskgroup:        DATA
        ADVM resize increment: 536870912
        ADVM redundancy:       mirror
        ADVM stripe columns:   8
        ADVM stripe width:     1048576
    number of snapshots:  0
    snapshot space usage: 0  ( 0.00 )
    replication status: DISABLED
    compression status: DISABLED

So I set the maximum size to the current size.

[oracle@oda-x7m ~]$ acfsutil size -x 100G /u02/app/oracle/oradata/ICM01A
acfsutil size: ACFS-03642: successfully updated auto-resize settings

Just crosscheck that.

[oracle@oda-x7m ~]$ acfsutil info fs /u02/app/oracle/oradata/ICM01A
/u02/app/oracle/oradata/ICM01A
    ACFS Version: 12.2.0.1.0
    on-disk version:       46.0
    compatible.advm:       12.2.0.0.0
    ACFS compatibility:    12.2.0.0.0
    flags:        MountPoint,Available,AutoResizeEnabled
    mount time:   Fri Mar 15 10:09:28 2019
    mount sequence number: 4
    allocation unit:       4096
    metadata block size:   4096
    volumes:      1
    total size:   107374182400  ( 100.00 GB )
    total free:   91318923264  (  85.05 GB )
    auto-resize increment:   10737418240  (  10.00 GB )
    auto-resize maximum:     107374182400  ( 100.00 GB )
    file entry table allocation: 262144
    primary volume: /dev/asm/daticm01-411
        label:
        state:                 Available
        major, minor:          246, 210435
        logical sector size:   512
        size:                  107374182400  ( 100.00 GB )
        free:                  91318923264  (  85.05 GB )
        metadata read I/O count:         54840
        metadata write I/O count:        51022
        total metadata bytes read:       242659328  ( 231.42 MB )
        total metadata bytes written:    233385984  ( 222.57 MB )
        ADVM diskgroup:        DATA
        ADVM resize increment: 536870912
        ADVM redundancy:       mirror
        ADVM stripe columns:   8
        ADVM stripe width:     1048576
    number of snapshots:  0
    snapshot space usage: 0  ( 0.00 )
    replication status: DISABLED
    compression status: DISABLED

You could also combine the resize operation which returns the final ACFS size which in turn can be used for setting the maximum size.
And that’s basically it, there is nothing more to do.