ODA – cleanup repository

A couple of days ago, Oracle released version 18.7 for the Oracle Database Appliance. The most useful new feature in my opinion is the “Repository Cleanup”. You can have a look at all the other new features in the Realase Notes.

For now, I’ll outline the usage of the cleanup feature. First, let’s have a look at the options:

[root@odax6m ~]# odacli cleanup-patchrepo -h
Usage: cleanup-patchrepo [options]
  Options:
    --clone, -cl
      Flag for clone directory cleanup.
    --component, -comp
      Components:{DB,GI} default{Both DB and GI}
    --help, -h
      get help
    --json, -j
      json output
    --local, -l
      Cleanup Repository for local node.
    --node, -n
      Cleanup Repository for specific nodes
    --version, -v
      ODA Patch Version.

It states, and the documentation does too, the options are all optional but in fact this isn’t true:

[root@odax6m pkgrepos]# odacli cleanup-patchrepo
DCS-10001:Internal error encountered: Invalid Input Parameter to delete the clones and patch component.

I started with the clone files. But I did not manage to delete a single DB clone. These are the clones I had:

[root@odax6m ~]# cd /opt/oracle/oak/pkgrepos/
[root@odax6m pkgrepos]# ll orapkgs/clones/
total 14615580
-r-xr-xr-x 1 root root 2395927731 Jun 17  2018 db112.180417.tar.gz
-r-xr-xr-x 1 root root 4199611255 Jun 17  2018 db121.180417.tar.gz
-rw-r--r-- 1 root root 3857973981 Jun 17  2018 db122.180417.tar.gz
-r-xr-xr-x 1 root root 4498196424 Dec 18  2018 db18.181016.tar.gz

I tried several different combinations:

[root@odax6m pkgrepos]# odacli cleanup-patchrepo -cl -v 18.3.0.0.0
[root@odax6m pkgrepos]# odacli cleanup-patchrepo -cl -v 18.5.0.0.0
[root@odax6m pkgrepos]# odacli cleanup-patchrepo -cl -comp db -v 18.3.0.0.0
[root@odax6m pkgrepos]# odacli cleanup-patchrepo -cl -v 18.3.0.0.181016
DCS-10200:Supplied version: 18.3.0.0 is invalid.

Except the last one, all cleanup jobs run successfully, but still there are all clones:

[root@odax6m pkgrepos]# ll orapkgs/clones/
total 14615580
-r-xr-xr-x 1 root root 2395927731 Jun 17  2018 db112.180417.tar.gz
-r-xr-xr-x 1 root root 4199611255 Jun 17  2018 db121.180417.tar.gz
-rw-r--r-- 1 root root 3857973981 Jun 17  2018 db122.180417.tar.gz
-r-xr-xr-x 1 root root 4498196424 Dec 18  2018 db18.181016.tar.gz

I’ll investigate this later.
Next, I cleaned the Bundlepatches. Since the ODA was freshly imaged with 18.7.0.0 there where just 18.7.0.0.0 and 18.6.0.0.0 patches installed.

[root@odax6m pkgrepos]# ll db/*
db/11.2.0.4.190416:
total 1200324
-r-xr-xr-x 1 root root        873 Sep 12 18:32 componentmetadata.xml
-r-xr-xr-x 1 root root   89475398 Sep 12 18:32 db112.patch.storage.zip
-r-xr-xr-x 1 root root    4953025 Sep 12 18:32 p28602216_112040_Linux-x86-64.zip
-r-xr-xr-x 1 root root 1133472011 Sep 12 18:32 p29255947_112040_Linux-x86-64.zip

db/11.2.0.4.190716:
total 1250008
-r-xr-xr-x 1 root root        949 Sep 12 18:31 componentmetadata.xml
-r-xr-xr-x 1 root root   89475398 Sep 12 18:31 db112.patch.storage.zip
-r-xr-xr-x 1 root root    4953025 Sep 12 18:31 p28602216_112040_Linux-x86-64.zip
-r-xr-xr-x 1 root root 1184297467 Sep 12 18:31 p29698727_112040_Linux-x86-64.zip

db/12.1.0.2.190416:
total 3285576
-r-xr-xr-x 1 root root       1015 Sep 12 18:32 componentmetadata.xml
-r-xr-xr-x 1 root root    3398176 Sep 12 18:32 p28602202_121020_Linux-x86-64.zip
-r-xr-xr-x 1 root root 3357733895 Sep 12 18:32 p29176139_121020_Linux-x86-64.zip

db/12.1.0.2.190716:
total 3635656
-r-xr-xr-x 1 root root       1091 Sep 12 18:30 componentmetadata.xml
-r-xr-xr-x 1 root root    3398176 Sep 12 18:30 p28602202_121020_Linux-x86-64.zip
-r-xr-xr-x 1 root root 3715867922 Sep 12 18:31 p29698629_121020_Linux-x86-64.zip

db/12.2.0.1.190416:
total 1701944
-r-xr-xr-x 1 root root        925 Sep 12 18:31 componentmetadata.xml
-r-xr-xr-x 1 root root    1547429 Sep 12 18:31 p28602183_122010_Linux-x86-64.zip
-r-xr-xr-x 1 root root 1736326653 Sep 12 18:32 p29301687_122010_Linux-x86-64.zip
-r-xr-xr-x 1 root root    3200431 Sep 12 18:31 p29344646_12201190416DBAPR2019RU_Linux-x86-64.zip

db/12.2.0.1.190716:
total 1934444
-r-xr-xr-x 1 root root       1001 Sep 12 18:30 componentmetadata.xml
-r-xr-xr-x 1 root root    1547429 Sep 12 18:30 p28602183_122010_Linux-x86-64.zip
-r-xr-xr-x 1 root root 1977375813 Sep 12 18:30 p29708720_122010_Linux-x86-64.zip

db/18.6.0.0.190416:
total 3364852
-r-xr-xr-x 1 root root       1010 Sep 12 18:31 componentmetadata.xml
-r-xr-xr-x 1 root root 1024357189 Sep 12 18:31 p29173957_186000ACFSRU_Linux-x86-64.zip
-r-xr-xr-x 1 root root  114529524 Sep 12 18:31 p29249584_180000_Linux-x86-64.zip
-r--r--r-- 1 root root 2303327208 Sep 12 18:31 p29764249_180000_Linux-x86-64.zip

db/18.7.0.0.190716:
total 2489356
-r-xr-xr-x 1 root root       1038 Sep 12 18:30 componentmetadata.xml
-r-xr-xr-x 1 root root 2546599237 Sep 12 18:30 p30097923_180000_Linux-x86-64.zip

The only option is to remove the Bundlepatches that came with 18.7.0.0.0.

[root@odax6m pkgrepos]# odacli cleanup-patchrepo -comp GI,DB -v 18.6.0.0.0

This time it removed the patches:

[root@odax6m pkgrepos]# ll db/*
db/11.2.0.4.190716:
total 1250008
-r-xr-xr-x 1 root root        949 Sep 12 18:31 componentmetadata.xml
-r-xr-xr-x 1 root root   89475398 Sep 12 18:31 db112.patch.storage.zip
-r-xr-xr-x 1 root root    4953025 Sep 12 18:31 p28602216_112040_Linux-x86-64.zip
-r-xr-xr-x 1 root root 1184297467 Sep 12 18:31 p29698727_112040_Linux-x86-64.zip

db/12.1.0.2.190716:
total 3635656
-r-xr-xr-x 1 root root       1091 Sep 12 18:30 componentmetadata.xml
-r-xr-xr-x 1 root root    3398176 Sep 12 18:30 p28602202_121020_Linux-x86-64.zip
-r-xr-xr-x 1 root root 3715867922 Sep 12 18:31 p29698629_121020_Linux-x86-64.zip

db/12.2.0.1.190716:
total 1934444
-r-xr-xr-x 1 root root       1001 Sep 12 18:30 componentmetadata.xml
-r-xr-xr-x 1 root root    1547429 Sep 12 18:30 p28602183_122010_Linux-x86-64.zip
-r-xr-xr-x 1 root root 1977375813 Sep 12 18:30 p29708720_122010_Linux-x86-64.zip

db/18.7.0.0.190716:
total 2489356
-r-xr-xr-x 1 root root       1038 Sep 12 18:30 componentmetadata.xml
-r-xr-xr-x 1 root root 2546599237 Sep 12 18:30 p30097923_180000_Linux-x86-64.zip

Finally, we have an option to clean the repository and provide additional space for new patch files. Hopefully your remaining space allows to patch to 18.7.

Update 2019-10-01

I just learned, that the cleanup of RDBMS clones only removes older clone files of a specific relaease. It keeps just the most current clone per RDBMS relaase. In other words, there is no way to get rid of a complete release.

[root@odax6m ~]# ll /opt/oracle/oak/pkgrepos/orapkgs/clones/
total 16961664
-r-xr-xr-x 1 root root 2395927731 Jun 17  2018 db112.180417.tar.gz
-rwxr-xr-x 1 root root 2400037923 Sep  7  2018 db112.180717.tar.gz
-r-xr-xr-x 1 root root 4199611255 Jun 17  2018 db121.180417.tar.gz
-rw-r--r-- 1 root root 3857973981 Jun 17  2018 db122.180417.tar.gz
-r-xr-xr-x 1 root root 4498196424 Dec 18  2018 db18.181016.tar.gz
[root@odax6m ~]# odacli cleanup-patchrepo -cl
{
  "jobId" : "c02181b9-d8f5-40df-90de-82df4ea82f8d",
  "status" : "Created",
  "message" : null,
  "reports" : [ ],
  "createTimestamp" : "October 01, 2019 09:03:35 AM CEST",
  "resourceList" : [ ],
  "description" : "Cleanup patchrepos",
  "updatedTime" : "October 01, 2019 09:03:35 AM CEST"
}
[root@odax6m ~]# odacli describe-job -i "c02181b9-d8f5-40df-90de-82df4ea82f8d"

Job details
----------------------------------------------------------------
                     ID:  c02181b9-d8f5-40df-90de-82df4ea82f8d
            Description:  Cleanup patchrepos
                 Status:  Success
                Created:  October 1, 2019 9:03:35 AM CEST
                Message:

Task Name                                Start Time                          End Time                            Status
---------------------------------------- ----------------------------------- ----------------------------------- ----------
Cleanup Repository                       October 1, 2019 9:03:35 AM CEST     October 1, 2019 9:03:35 AM CEST     Success

[root@odax6m ~]# ll /opt/oracle/oak/pkgrepos/orapkgs/clones/
total 14619596
-rwxr-xr-x 1 root root 2400037923 Sep  7  2018 db112.180717.tar.gz
-r-xr-xr-x 1 root root 4199611255 Jun 17  2018 db121.180417.tar.gz
-rw-r--r-- 1 root root 3857973981 Jun 17  2018 db122.180417.tar.gz
-r-xr-xr-x 1 root root 4498196424 Dec 18  2018 db18.181016.tar.gz

As you can see, only the older clone file of the 11.2 RDBMS release was removed.

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.

ADR Home is growing permanently

Housekeeping is an often underestimated topic when running databases in production. In the blog post about the size based deletion policy I described a new way of keeping the size of an ADR home limited. But what if you are not yet on Oracle Database 12.2+? Then you have to rely on the existing time based deletion policies. That is what we did and do for our customers. A couple of weeks ago one of them experienced space pressure on their /u01 filesystem on one of their ODAs. The automatic purging worked fine, but the filesystem usage was still increasing. The Enterprise Manager metric outlines this behaviour.

adr-growth-01

So we started to investigate this and came to the trace-directory of some databases. This is an example of one of them.

[root@odax7-2m trace]#  du -hs * | sort -h | tail -6

68M     DOMEADD_gen0_82205.trm
69M     DOMEADD_mmon_82354.trm
131M    DOMEADD_ipc0_82195.trm
419M    DOMEADD_gen0_82205.trc
423M    DOMEADD_mmon_82354.trc
462M    DOMEADD_ipc0_82195.trc

We started with the largest trace and had a look at its end.

[root@odax7-2m trace]# tail DOMEADD_ipc0_82195.trc
2019-03-15 09:23:07.749*:kjuinc(): no cluster database, return inc# KSIMINVL

*** 2019-03-15T09:23:10.913816+01:00
2019-03-15 09:23:10.913*:kjuinc(): no cluster database, return inc# KSIMINVL

Having this, we did a quick research on My Oracle Support which revealed Bug 27989556  Excessive Trace Message: no cluster database, return inc# ksiminvl. And of cause there was no fix available. This changed by April 16th, now there’s a fix for 18c available with the 18.6.0 Release Update.

Ok, next file. Let’s have a look.

[root@odax7-2m trace]# tail DOMEADD_mmon_82354.trc
AUTO SGA: kmgs_parameter_update_timeout gen0 0 mmon alive 1

*** 2019-03-15T09:29:55.474098+01:00
AUTO SGA: kmgs_parameter_update_timeout gen0 0 mmon alive 1

*** 2019-03-15T09:29:58.474092+01:00
AUTO SGA: kmgs_parameter_update_timeout gen0 0 mmon alive 1

Again MOS has the answer: AUTO SGA: kmgs_parameter_update_timeout gen0 0 mmon alive 1 Excessive Trace Files were generated on one node after Migration to 12.2 and MMON trace file grows (Doc ID 2298766.1). At least there are patches for that this time.

Since patching would have required a downtime and we might fix only one of two issues, we decided against it. Instead, we created a simple but effective workaround:

for i in $(find /u01/app/oracle/diag/rdbms -type f  -name "*mmon*tr*"); do echo "" > $i; done
for i in $(find /u01/app/oracle/diag/rdbms -type f  -name "*ipc0*tr*"); do echo "" > $i; done

Once this was run, and we now do that regularly, the filesystem usage dropped immediatly. This is what Enterprise Manager showed right after running the workaround.

adr-growth-02

Adding VLANs on an ODA

The Oracle Database Appliances X7-2 come only with one redundant network interface, so it is not possible to separate network traffic between distinct fault-tolerant bonded interfaces. But at least we can use VLANs on the one and only bonded interface that all the ODA X7-2 provide. If you choose to use VLANs, this choice must be taken at the point of installing the ODA. There is no easy way to change that afterwards. So if you use VLANs, you do it right from the beginning when you do the “configure-firstnet”. Once the setup is finished, it will end up in a configuration simiar to this.

[root@odax7-2m ~]# odacli list-networks

ID                                       Name                 NIC        InterfaceType IP Address         Subnet Mask        Gateway            VlanId
---------------------------------------- -------------------- ---------- ---------- ------------------ ------------------ ------------------ ----------
32812bc3-5bb7-4f8e-9070-b011acde665d     Private-network      priv0      DUMMY      192.168.16.24      255.255.255.240
735e2695-d68a-4696-a596-ba39a7180de6     Public-network       btbond1.26 Vlan       172.17.11.165      255.255.255.0      172.17.11.10       26

Now we can add more networks. The additional networks must be added from the operating system point of view which is done by the appliance management “odacli”, and from the Grid Infrastructure point of view which in turn is done by using “srvctl”. Finally, the databases must be configured to use the propper VLANs for their communication. Let’s walk through these steps.

1. Create a new VLAN

[root@odax7-2m ~]# odacli create-network -no-d -g 172.17.2.10 -n btbond1 -t VLAN -p 172.17.2.9 -m lsnr2 -w Database -s 255.255.255.0 -v 21
{
  "jobId" : "bd4d9ba0-7f59-491c-878b-5c03d3be51b6",
  "status" : "Created",
  "message" : null,
  "reports" : [ ],
  "createTimestamp" : "March 15, 2019 10:56:15 AM CET",
  "resourceList" : [ ],
  "description" : "Network service creation with names btbond1:lsnr2 ",
  "updatedTime" : "March 15, 2019 10:56:15 AM CET"
}

[root@odax7-2m ~]# odacli describe-job -i "bd4d9ba0-7f59-491c-878b-5c03d3be51b6"

Job details
----------------------------------------------------------------
                     ID:  bd4d9ba0-7f59-491c-878b-5c03d3be51b6
            Description:  Network service creation with names btbond1:lsnr2
                 Status:  Success
                Created:  March 15, 2019 10:56:15 AM CET
                Message:

Task Name                                Start Time                          End Time                            Status
---------------------------------------- ----------------------------------- ----------------------------------- ----------
Setting network                          March 15, 2019 10:56:15 AM CET      March 15, 2019 10:56:20 AM CET      Success
Setting up Vlan                          March 15, 2019 10:56:15 AM CET      March 15, 2019 10:56:20 AM CET      Success

[root@odax7-2m ~]# odacli list-networks

ID                                       Name                 NIC        InterfaceType IP Address         Subnet Mask        Gateway            VlanId
---------------------------------------- -------------------- ---------- ---------- ------------------ ------------------ ------------------ ----------
32812bc3-5bb7-4f8e-9070-b011acde665d     Private-network      priv0      DUMMY      192.168.16.24      255.255.255.240
735e2695-d68a-4696-a596-ba39a7180de6     Public-network       btbond1.26 Vlan       172.17.11.165      255.255.255.0      172.17.11.10       26
e69efc7a-5973-4911-85c7-9d8e8b4138be     lsnr2                btbond1.21 VLAN       172.17.2.9         255.255.255.0      172.17.2.10        21

[root@odax7-2m ~]# odacli describe-network -i e69efc7a-5973-4911-85c7-9d8e8b4138be

Network details
----------------------------------------------------------------
                     ID:  e69efc7a-5973-4911-85c7-9d8e8b4138be
                   Name:  lsnr2
                    NIC:  btbond1.21
          InterfaceType:  VLAN
             IP Address:  172.17.2.9
            Subnet Mask:  255.255.255.0
                Gateway:  172.17.2.10
                 VlanId:  21
                   Type:  Database
                Default:  false
                Created:  March 15, 2019 10:56:15 AM CET

[root@odax7-2m ~]# odaadmcli show vlan
        NAME                     ID    INTERFACE   CONFIG_TYPE IP_ADDRESS      NETMASK         GATEWAY         NODENUM
        lsnr2                    21    btbond1     Database    172.17.2.9      255.255.255.0   172.17.2.10     0
        vlan26                   26    btbond1     public      172.17.11.165   255.255.255.0   172.17.11.10    0

[root@odax7-2m ~]# ifconfig btbond1.21
btbond1.21 Link encap:Ethernet  HWaddr 00:10:E0:DE:6F:CB
          inet addr:172.17.2.9  Bcast:172.17.2.255  Mask:255.255.255.0
          UP BROADCAST RUNNING MULTICAST  MTU:1500  Metric:1
          RX packets:501 errors:0 dropped:0 overruns:0 frame:0
          TX packets:13 errors:0 dropped:0 overruns:0 carrier:0
          collisions:0 txqueuelen:0
          RX bytes:35567 (34.7 KiB)  TX bytes:720 (720.0 b)

2. Add the VLAN to the Grid Infrastructure
Note, you must set the environment variables to point to the Grid Infrastructure. We have a toolbox for Linux systems, that makes it easy to set and maintain different environment settings. This toolbox provides a function to set the propper Oracle environment which is called “soe” –  set oracle environment. You’ll see it’s usage in the following step.

First, we add the new VLAN as a network resource.

[root@odax7-2m ~]# soe +ASM

Environment :
===================

Setting         Value
--------------- ---------------------------
NLS_DATE_FORMAT "YYYY-MM-DD HH24:MI:SS"
NLS_LANG        "AMERICAN_GERMANY.AL32UTF8"
ORACLE_BASE     /u01/app/oracle
ORACLE_HOME     /u01/app/12.2.0.1/oracle
ORACLE_SID      +ASM1
ORACLE_UNQNAME  +ASM

[root@odax7-2m ~]# srvctl add network -netnum 21 -subnet 172.17.2.0/255.255.255.0/btbond1.21 -nettype static

[root@odax7-2m ~]# srvctl config network
Network 1 exists
Subnet IPv4: 172.17.11.0/255.255.255.0/btbond1.26, static
Subnet IPv6:
Ping Targets:
Network is enabled
Network is individually enabled on nodes:
Network is individually disabled on nodes:
Network 21 exists
Subnet IPv4: 172.17.2.0/255.255.255.0/btbond1.21, static
Subnet IPv6:
Ping Targets:
Network is enabled
Network is individually enabled on nodes:
Network is individually disabled on nodes:
Network 28 exists
Subnet IPv4: 172.17.5.0/255.255.255.0/btbond1.28, static
Subnet IPv6:
Ping Targets:
Network is enabled
Network is individually enabled on nodes:
Network is individually disabled on nodes:

Second, we definde the VIP and the listener. Please be careful, which OS user you use for which command. Have a look at the command prompt.

[root@odax7-2m ~]# srvctl add vip -node odax7-2m -address odax7-2m-lsnr2/255.255.255.0 -netnum 21
[root@odax7-2m ~]# su - oracle
[oracle@odax7-2m ~]$ soe +ASM

Environment :
===================

Setting         Value
--------------- ---------------------------
NLS_DATE_FORMAT "YYYY-MM-DD HH24:MI:SS"
NLS_LANG        "AMERICAN_GERMANY.AL32UTF8"
ORACLE_BASE     /u01/app/oracle
ORACLE_HOME     /u01/app/12.2.0.1/oracle
ORACLE_SID      +ASM1
ORACLE_UNQNAME  +ASM

[oracle@odax7-2m ~]$ srvctl add listener -netnum 21 -endpoints "TCP:1523" -listener listener2
[oracle@odax7-2m ~]$ srvctl start listener -listener listener2
[oracle@odax7-2m ~]$ lsnrctl status listener2

LSNRCTL for Linux: Version 12.2.0.1.0 - Production on 15-MAR-2019 11:10:26

Copyright (c) 1991, 2016, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER2)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER2
Version                   TNSLSNR for Linux: Version 12.2.0.1.0 - Production
Start Date                15-MAR-2019 11:10:03
Uptime                    0 days 0 hr. 0 min. 22 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/12.2.0.1/oracle/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/odax7-2m/listener2/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER2)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=172.17.2.9)(PORT=1523)))
The listener supports no services
The command completed successfully

3. Point a database to the new Listener
To make use of the new listener, the database(s) must register with that listener. So we simply set the “local_listener” parameter to point to the new listener and we are done.

[oracle@odax7-2m ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on MAR-15-2019 11:11:58 

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


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

SQL> alter system set local_listener='odax7-2m-lsnr2:1523';

System altered.

SQL> exit

[oracle@odax7-2m ~]$ soe +ASM -s
[oracle@odax7-2m ~]$ lsnrctl status listener2

LSNRCTL for Linux: Version 12.2.0.1.0 - Production on 15-MAR-2019 11:15:23

Copyright (c) 1991, 2016, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER2)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER2
Version                   TNSLSNR for Linux: Version 12.2.0.1.0 - Production
Start Date                15-MAR-2019 11:10:03
Uptime                    0 days 0 hr. 5 min. 19 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/12.2.0.1/oracle/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/odax7-2m/listener2/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER2)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=172.17.2.9)(PORT=1523)))
Service "MMI.support.robotron.de" has 1 instance(s).
  Instance "MMI", status READY, has 2 handler(s) for this service...
Service "MMIXDB.support.robotron.de" has 1 instance(s).
  Instance "MMI", status READY, has 2 handler(s) for this service...
The command completed successfully

That’s it, it’s as simple as that. 

Instance Caging

Last week we had a funny issue at a customers ODA X7-2M. You may know, that there are different database shapes, each having different settings for CPU_COUNT, memory etc. The documentation is quite clear about that. In this special case they had a database with shape “odb4” which sets CPU_COUNT to 4. Now they went live with their appication on that database and experienced the following behaviour.
cpu_count_instance_caging
You see the CPU limit of and you can also see, that the load goes well over that limit. But why? The answer is quite simple. Setting CPU_COUNT does not neccessarily limit the number of CPUs the database will use. An active Resource Manager Plan is required to achieve that. MOS Note “Why are a Higher Number of CPUs Used Than the CPU_COUNT Parameter Setting (Doc ID 2017395.1)” is explainig that. If you wish to limit the CPU usage, you will have to use Instance Caging which is a combination of setting CPU_COUNT and using Resource Manager. There is another MOS Note “Configuring and Monitoring Instance Caging (Doc ID 1362445.1)” for that.
That means, if you want your databases on ODA be limited to the CPU usage their shape defines, you need to activate a Resource Manager Plan, at least the “DEFAULT_PLAN”. This is not the default, keep that in mind.

Why running “configure-firstnet” must not be run twice (ODA)

Recently I was deplyoing some Oracle Database Appliances X7-2M at a customers site. Everything went quite smooth until we tried to configure the network using “configure-firstnet”. The documentation is quite clear about this, one shall not run this more than once. But in our case we first configured the network without VLANs which ended up with a nice “ifcfg-btbond1” configuration containing the IP information.
But as we figured out we had to use a VLAN so we simply run “configure-firstnet” again. This generated an “ifcfg-btbond1.26” configuration file for our VLAN with ID 26. It had the same IP information in it as the one without VLANs. This works fine since the script is obviously creating the interfaces properly.
But after a

service network restart

The machine was not reachable anymore. As we investigated this, we saw that there are now two bond interfaces with a configured IP which obviously prevents any network communication.

So the solution was to remove all lines/parameters from “ifcfg-btbond1” configuration file, that were IP-related and restart the network again. At the end the issue was quite obvious and easy to remedy but it took us some time which could have been used better. That emphasizes the importance of clear information and a well structured preparation when deplyoing an Oracle Database Appliance (and other appliances too).

Database Upgrade with Standby on ODA

A couple of days ago I did my easiest upgrade of an Oracle Database ever. This is the system:

  • 2 Oracle Database Appliances in two locations
  • Databases running as single instance due to licensing restrictions
  • DataGuard between ODA-1 and ODA-2 for mission-critical databases
  • several databases, version 11.2.0.4 and 12.1.0.2
  • currently running ODA 12.1.2.4, so big excuse for not being up-to-date

The customer wanted me to upgrade one of these 11.2.0.4 databases to 12.1.0.2. So the basic steps are as follows:

  1. Install new Oracle Home for version 12.1.0.2
  2. Disable DataGuard configuration
  3. Stop DataGuard Broker processes
  4. Stop Standby database
  5. Upgrade Primary database
  6. Start Standby database from new home

On an ODA this is quite simple to do. First create a new Oracle Home an both ODAs:

root$ oakcli create dbhome -version 12.1.0.2.3
...
INFO: 2016-03-15 09:25:07: Installing a new Home : OraDb12102_home3 at /u01/app/oracle/product/12.1.0.2/dbhome_3
...
SUCCESS: 2016-03-15 09:30:55: Successfully created the Database Home : OraDb12102_home3

Now there is the new 12c home that we can use for the upgrade. One could also use an existing home that can be chosen from the list:

root$ oakcli show dbhomes -detail

Next step is disabling the DataGuard configuration:

oracle$ . oraenv
oracle$ dgmgrl
DGMGRL> connect sys/****@primary
Connected.
DGMGRL> disable configuration
Disabled.

Now stop the DataGuard Broker processes on both, primary and standby:

oracle$ . oraenv
oracle$ sqlplus / as sysdba
SQL> alter system set dg_broker_start=false scope=both;

System altered.

Stop the Standby database:

oracle$ srvctl stop database -d <dbname>

And now do the upgrade of the Primary to the Oracle Home that was created or chosen in the first step. It will ask for the SYS password, be prepared for that.

root$ oakcli upgrade database -db <dbname> -to OraDb12102_home3
INFO: 2016-03-15 10:30:30: Look at the log file '/opt/oracle/oak/log/<odabase-01>/tools/12.1.2.4.0/dbupgrade_2585.log' for more details 

Please enter the 'SYS'  password : 
Please-enter the 'SYS' password: 
2016-03-15 10:31:29: Upgrading the database <dbname>. It will take few minutes. Please wait... 

 SUCCESS: 2016-03-15 10:50:01: Successfully upgraded the database <dbname>

That’s it for the Primary. Now modify the Standby to use the new 12c Database Home. Copy the password file to the new Oracle Home, and, if it is not in a shared location, the SPfile too.

oracle$ cp <old ORACLE_HOME>/dbs/orapw<dbname> <new ORACLE_HOME>/dbs/ 
oracle$ cp <old ORACLE_HOME>/dbs/spfile<dbname>.ora <new ORACLE_HOME>/dbs/

The database resource needs to be modified in order to use the new Oracle Home. That is the last time that “srvctl” is run from the old Oracle Home:

oracle$ srvctl config database -d <dbname> 
oracle$ srvctl modify database -d <dbname> -o /u01/app/oracle/product/12.1.0.2/dbhome_3

If required, change the SPfile location too:

oracle$ srvctl modify database -d <dbname> -p /u01/app/oracle/product/12.1.0.2/dbhome_3/dbs/spfile<dbname>.ora

Now the Standby can be started again:

oracle$ srvctl start database -d <dbname>

Last step is to start the DataGuard Broker processes and re-enable the configuration.

oracle$ . oraenv
oracle$ sqlplus / as sysdba
SQL> alter system set dg_broker_start=true scope=both;

System altered.
oracle$ . oraenv
oracle$ dgmgrl
DGMGRL> connect sys/****@primary
Connected.
DGMGRL> enable configuration
Enabled.

Very simple and very straight forward. I like ODA 🙂

There is some documentation that has been used: