Oracle RDBMS

Oracle Database Appliance X6 – resize your /u01

Basically on an ODA X6-L the directory /u01 has the size of 100 GB. With seven different ORACLE_HOME directories for the Oracle databases and the Grid Infrastructure software, there is no disk space left for patching the Oracle Database Appliance. ODA patches require between 13 GB and 15 GB free space on /u01 for the new software. Just FYI – during the patch process a new stage directory called /u01/patching is created where the new software is temporarily located.

Patch Apply failed – Not enough free Space

If there is not enough free space available, update processes like updating a database home fail.

[root@oda-srv2046 ~]# odacli describe-job -i f024f27d-7c9a-43fc-9ae8-4f99ef809280

Job details
----------------------------------------------------------------
ID: f024f27d-7c9a-43fc-9ae8-4f99ef809280
Description: DB Home Patching: Home Id is 92c3af4e-1ef0-4ec6-8245-57b995a045d7
Status: Failure
Created: June 12, 2018 4:42:09 PM CEST
Message: DCS-10001:Internal error encountered: Failed to apply the patch 26925263 on the database home 92c3af4e-1ef0-4ec6-8245-57b995a045d7.

Task Name Start Time End Time Status
---------------------------------------- ----------------------------------- ----------------------------------- ----------
DB Home Patching June 12, 2018 4:42:09 PM CEST June 12, 2018 4:48:37 PM CEST Failure
DB Home Patching June 12, 2018 4:42:09 PM CEST June 12, 2018 4:48:37 PM CEST Failure
clusterware patch verification June 12, 2018 4:42:09 PM CEST June 12, 2018 4:42:13 PM CEST Success
Patch location validation June 12, 2018 4:42:13 PM CEST June 12, 2018 4:42:19 PM CEST Success
Opatch updation June 12, 2018 4:43:31 PM CEST June 12, 2018 4:43:34 PM CEST Success
Patch conflict check June 12, 2018 4:43:34 PM CEST June 12, 2018 4:45:24 PM CEST Success
task:TaskSequential_4338 June 12, 2018 4:45:24 PM CEST June 12, 2018 4:48:37 PM CEST Failure
db upgrade June 12, 2018 4:45:24 PM CEST June 12, 2018 4:48:37 PM CEST Failure
Verify the actual Situation and the Disk Configuration

There is not enough free space for the update process – but we see that /u01 is a logical volume. 

[root@oda-srv2046 ~]# df -m /u01
Filesystem 1M-blocks Used Available Use% Mounted on
/dev/mapper/VolGroupSys-LogVolU01
100666 85926 9621 90% /u01

Let’s verify the physical volume for free space, we can see 7214 free physical extents are available.

[root@oda-srv2046 ~]# pvdisplay
--- Physical volume ---
PV Name /dev/sda2
VG Name VolGroupSys
PV Size 439.45 GiB / not usable 16.00 MiB
Allocatable yes
PE Size 32.00 MiB
Total PE 14062
Free PE 7214
Allocated PE 6848
PV UUID 3NtnHJ-Cg1Z-wS8c-4ADC-7465-FMUN-df2Gu1

This is the actual size of the logical volume, the actual logical volume size is 100 GB.

[root@oda-srv2046 ~]# lvdisplay /dev/mapper/VolGroupSys-LogVolU01
--- Logical volume ---
LV Path /dev/VolGroupSys/LogVolU01
LV Name LogVolU01
VG Name VolGroupSys
LV UUID lKPvcP-w0xv-Gezy-v6Pj-gtdU-TMMz-xtaj7s
LV Write Access read/write
LV Creation host, time localhost.localdomain, 2017-03-31 12:13:14 +0200
LV Status available
# open 1
LV Size 100.00 GiB
Current LE 3200
Segments 1
Allocation inherit
Read ahead sectors auto
- currently set to 256
Block device 249:2
Extend /u01

Now we extend online the LVM with the lvextend command from 100 GB to 150 GB.

[root@oda-srv2046 ~]# lvextend --size +50G /dev/VolGroupSys/LogVolU01
Size of logical volume VolGroupSys/LogVolU01 changed from 100.00 GiB (3200 extents) to 150.00 GiB (4800 extents).
Logical volume LogVolU01 successfully resized.

The logical volume is now 150 GB.

[root@oda-srv2046 ~]# lvdisplay /dev/mapper/VolGroupSys-LogVolU01
--- Logical volume ---
LV Path /dev/VolGroupSys/LogVolU01
LV Name LogVolU01
VG Name VolGroupSys
LV UUID lKPvcP-w0xv-Gezy-v6Pj-gtdU-TMMz-xtaj7s
LV Write Access read/write
LV Creation host, time localhost.localdomain, 2017-03-31 12:13:14 +0200
LV Status available
# open 1
LV Size 150.00 GiB
Current LE 4800
Segments 2
Allocation inherit
Read ahead sectors auto
- currently set to 256
Block device 249:2

Finally the filesystem has to be resized.

[root@oda-srv2046 ~]# resize2fs /dev/VolGroupSys/LogVolU01
resize2fs 1.43-WIP (20-Jun-2013)
Filesystem at /dev/VolGroupSys/LogVolU01 is mounted on /u01; on-line resizing required
old_desc_blocks = 7, new_desc_blocks = 10
Performing an on-line resize of /dev/VolGroupSys/LogVolU01 to 39321600 (4k) blocks.
The filesystem on /dev/VolGroupSys/LogVolU01 is now 39321600 blocks long.

Now we have enough space for patching.

[root@oda-srv2046 ~]# df -m /u01
Filesystem 1M-blocks Used Available Use% Mounted on
/dev/mapper/VolGroupSys-LogVolU01
151062 85933 57451 60% /u01
Summary

Resizing the /u01 on an Oracle Database Appliance is very easy and straightforward. There are no special actions required, it’s just a logical volume, no downtime required. And there is more space left on the physical volume for other resize actions.

Get your Oracle 18c Instance in the Oracle Infrastructure Cloud OCI Classic

Do you want to work with Oracle 18c in the Oracle Cloud but the database version is not selectable in the webinterface? You can create an 18c instance in the command-line interface with the PaaS Service Manager (psm). The installation is very well described here, for example you need Python and OpenSSL. My personal installation of the psm executable runs in the Windows 10 integrated Ubuntu system.

Link to the PaaS Service Manager: https://docs.oracle.com/en/cloud/paas/java-cloud/pscli/abouit-paas-service-manager-command-line-interface.html

After the successful psm setup, you can create an DBaaS instance with this command

$ psm dbcs create-service --config-payload db18c-ee.json

The file db18c-ee.json contains all the information you need to create an 18c instance. Here is my example – I have created a cloud storage container called dbcsbackup in advance because I want to use the OCI backup service.

{
"description": "18c Instance",
"edition": "EE",
"level": "PAAS",
"serviceName": "db18c-mbg-01",
"shape": "oc3",
"subscriptionType": "HOURLY",
"version": "18.0.0.0",
"vmPublicKeyText": "<copy_here_your_oci_ssh_public_kex",
"parameters": [
{
"type": "db",
"usableStorage": "15",
"adminPassword": "Welcome_1",
"sid": "ORCL",
"pdbName": "MYPDB",
"failoverDatabase": "no",
"backupDestination": "BOTH",
"cloudStorageContainer": "Storage-<my_identitydomain>\/dbcsbackup",
"cloudStorageUser": "<my_login>",
"cloudStoragePwd": "<my_password>"
}
]
}

Some minutes later you can login by terminal and user SQL*Plus. Oracle 18c: Here we are!

And in the OCI dashboard it looks fine too.

Addtional Info: If you want a Standard Edition, just replace the line “edition”: “SE”, happy 18c.

 

DATABASE PATCH SET UPDATE 12.1.0.2.170117 apply fails – catconInit failed, exiting

Last weekend was patchday. The goal was to apply the patch 24732082 (DATABASE PATCH SET UPDATE 12.1.0.2.170117) to a 12.1.0.2 database on AIX. The OPatch precheck returned no error and OPatch apply was ok. The problem was the post step, the datapatch command failed with the message catconInit failed, exiting.

oracle@srvaix101:/u00/app/oracle/product/12.1.0.2/dbhome_1/OPatch/ [STRSP01] ./datapatch -verbose
SQL Patching tool version 12.1.0.2.0 Production on Sun Apr  2 08:39:35 2017
Copyright (c) 2012, 2016, Oracle.  All rights reserved.

Log file for this invocation: /u00/app/oracle/cfgtoollogs/sqlpatch/sqlpatch_45548440_2017_04_02_08_39_35/sqlpatch_invocati                                               on.log

Connecting to database...OK

catconInit failed, exiting

Please refer to MOS Note 1609718.1 and/or the invocation log
/u00/app/oracle/cfgtoollogs/sqlpatch/sqlpatch_45548440_2017_04_02_08_39_35/sqlpatch_invocation.log
for information on how to resolve the above errors.

SQL Patching tool complete on Sun Apr  2 08:39:36 2017

The solution was described in this My Oracle Support Note: Datapatch fails with “catconInit: database is not open on the default instance” (Doc ID 2003488.1)

In the glogin.sql file  located in ORACLE_HOME/sqlplus/admin were two lines:

set lines 400
set pages 0

After I have commented out these lines, everything runs ok.

oracle@srvaix101:/u00/app/oracle/product/12.1.0.2/dbhome_1/OPatch/ [STRSP01] ./datapatch -verbose
SQL Patching tool version 12.1.0.2.0 Production on Sun Apr  2 08:52:42 2017
Copyright (c) 2012, 2016, Oracle.  All rights reserved.

Log file for this invocation: /u00/app/oracle/cfgtoollogs/sqlpatch/sqlpatch_45351856_2017_04_02_08_52_42/sqlpatch_invocation.log

Connecting to database...OK
Bootstrapping registry and package to current versions...done
Determining current state...done

Current state of SQL patches:
Bundle series PSU:
  ID 170117 in the binary registry and not installed in the SQL registry

Adding patches to installation queue and performing prereq checks...
Installation queue:
  Nothing to roll back
    24732082 (DATABASE PATCH SET UPDATE 12.1.0.2.170117)

Installing patches...
Patch installation complete.  Total patches installed: 1

Validating logfiles...
Patch 24732082 apply: SUCCESS
  logfile: /u00/app/oracle/cfgtoollogs/sqlpatch/24732082/20919987/24732082_apply_STRSP01_2017Apr02_08_53_02.log (no errors)
SQL Patching tool complete on Sun Apr  2 08:53:39 2017

Summary: Two small lines, a huge effect.

Summary: Two small lines, a big impact.

Oracle 12.2 – how to get access to Enterprise Manager 12c Database Express

Today I have built in my OL 7.3 VM two container databases to verify the new feature in the Enterprise Manager 12c Database Express login screen where I can go direct into a container. The benefit is that I don’t have to set a separate port for each pluggable database anymore.

The DBCA has created me a fresh container database called ZH38 and a pluggable database ZHPDB01. I have enable the checkbox to configure EM 12c Database Express for Port 5501. After the database creation was finished, I was able to see the login screen, but the login was not possible. I got an XDB login prompt. SYS, SYSTEM and other users where I tried were not accepted.

I found  the solution in the Database 2 Day DBA Guide – http://docs.oracle.com/database/122/ADMQS/getting-started-with-database-administration.htm#ADMQS003 :

SQL> exec dbms_xdb_config.setglobalportenabled(TRUE);

PL/SQL procedure successfully completed.

After the execution of this command in the CDB$ROOT container, logins into EM 12c Database Express of the CDB$ROOT and the pluggable database ZHPDB01 were possible. No restart of the container database was required. If once set, it works for all existing and new created pluggable databases, the PDB$SEED included.

If you want to disable this access method to a specific pluggable database, execute the command above with the FALSE flag.

How can you find out if the parameter is set? Thanks to my Trivadis collegue Philipp Salvisberg / https://www.salvis.com/blog who has provided me this PL/SQL code:

SQL> WITH
  2  FUNCTION b2vc (in_bool_expr VARCHAR2) RETURN VARCHAR2 IS
  3     l_bool  BOOLEAN;
  4     l_plsql VARCHAR2(32767);
  5     l_ret   VARCHAR2(5);
  6  BEGIN
  7     l_plsql := 'BEGIN :l_bool := ' || in_bool_expr || '; END;';
  8     EXECUTE IMMEDIATE l_plsql USING OUT l_bool;
  9     IF l_bool IS NOT NULL THEN
 10        IF l_bool THEN
 11           l_ret := 'TRUE';
 12        ELSE
 13           l_ret := 'FALSE';
 14        END IF;
 15     END IF;
 16     RETURN l_ret;
 17  END b2vc;
 18  SELECT b2vc('DBMS_XDB_CONFIG.ISGLOBALPORTENABLED') FROM dual;
 19  /

B2VC('DBMS_XDB_CONFIG.ISGLOBALPORTENABLED')
--------------------------------------------------------------------------------
TRUE

 

Summary: Do you have any problems with the new release? First read the new documentation.

OPatch 13.9 in EM13c – Say Goodbye to Unzip, Copy & Paste

Yesterday I wanted to apply a brand new patch to customer’s Enterprise Manager 13cR2 OMS running on Linux. First I updated the OMSPatcher as described here: How to upgrade the 13.1 Cloud Control OMSPatcher to latest version of OMSPatcher (Doc ID 2135028.1). This update was easy. Download, transfer, unzip and copy the OMSPatcher files to the Enterprise Manager ORACLE_HOME directory.

Then the patch apply results in an error. OMSPatcher is based on OPatch, he needs an update too.

We need a new OPatch Version

This was error message for patch apply with omspatcher when I executed the apply command.

[Dec 1, 2016 10:17:38 AM]    Prerequisite check "CheckMinimumOPatchVersion" failed.
                             The details are:

                             The OPatch being used has version 13.8.0.0.0 while the following patch(es) require higher versions:
                             Patch 24914115 requires OPatch version 13.9.1.0.0.
                             Please download latest OPatch from My Oracle Support.

My actual EM13cR2 OPatch version was 13.8.0.0.0.

oracle@kestenholz:/u00/app/oracle/product/em13cr2/OPatch/ [oms13cr2] ./opatch version
OPatch Version: 13.8.0.0.0

OPatch succeeded.

Get the new Version

In My Oracle Support I found the newest version of OPatch, Download.

opatch_01

Transfer and extract

The next step was to extract and to transfer the package to the target server.And then I was wondering that not like in versions before an OPatch directory was created, now there is a JAR file inside the package. Copy & Paste of an OPatch directory is no more possible anymore.

oracle@kestenholz:/u00/app/oracle/stage/OPatch/ [oms13cr2] unzip p6880880_139000_Generic.zip
Archive:  p6880880_139000_Generic.zip
   creating: 6880880/
  inflating: 6880880/README.txt
  inflating: 6880880/opatch_generic.jar
  inflating: 6880880/version.txt

Installation Routine – README.txt

Let’s take a look in the README.txt – there is an installation manual.

oracle@kestenholz:/u00/app/oracle/stage/OPatch/6880880/ [oms13cr2] cat README.txt
Installation
----------------------------------
- Backup your <ORACLE_HOME>

- Unzip this patch into your staging directory PATCH_HOME

- Install the software via:
    java -jar <PATCH_HOME>/6880880/opatch_generic.jar -silent oracle_home=ORACLE_HOME

- To validate the installation:
    cd <ORACLE_HOME>/OPatch
    opatch version
    opatch lspatches

Installation – 1st run – where is my JDK ?

oracle@kestenholz:~/ [oms13cr2] java -jar /u00/app/oracle/stage/6880880/opatch_generic.jar -silent oracle_home=/u00/app/oracle/roduct/em13cr2
Launcher log file is /tmp/OraInstall2016-12-02_10-00-10AM/launcher2016-12-02_10-00-10AM.log.
Extracting the installer . . . . Done
This installer must be executed using a Java Development Kit (JDK)
but /usr/lib/jvm/java-1.8.0-openjdk-1.8.0.65-3.b17.el7.x86_64/jre is not a valid JDK Java Home.
The log is located here: /tmp/OraInstall2016-12-02_10-00-10AM/launcher2016-12-02_10-00-10AM.log.

Installation – 2nd run – JRE is not good enough

I tried to use the existing JDK from my Oracle 12.1 installation

oracle@kestenholz:/u00/app/oracle/stage/6880880/ [oms13cr2] export JAVA_HOME=/u00/app/oracle/product/12.1.0.2/dbhome_1/jdk/bin
oracle@kestenholz:/u00/app/oracle/stage/6880880/ [oms13cr2] $JAVA_HOME/java -jar /u00/app/oracle/stage/6880880/opatch_generic.jar -silent oracle_home=/u00/app/oracle/product/em13cr2
Exception in thread "main" java.lang.UnsupportedClassVersionError: com/oracle/cie/nextgen/launcher/Launcher : Unsupported major.minor version 51.0
        at java.lang.ClassLoader.defineClass1(Native Method)
        at java.lang.ClassLoader.defineClassCond(ClassLoader.java:637)
        at java.lang.ClassLoader.defineClass(ClassLoader.java:621)
        at java.security.SecureClassLoader.defineClass(SecureClassLoader.java:141)
        at java.net.URLClassLoader.defineClass(URLClassLoader.java:283)
        at java.net.URLClassLoader.access$000(URLClassLoader.java:58)
        at java.net.URLClassLoader$1.run(URLClassLoader.java:197)
        at java.security.AccessController.doPrivileged(Native Method)
        at java.net.URLClassLoader.findClass(URLClassLoader.java:190)
        at java.lang.ClassLoader.loadClass(ClassLoader.java:306)
        at sun.misc.Launcher$AppClassLoader.loadClass(Launcher.java:301)
        at java.lang.ClassLoader.loadClass(ClassLoader.java:247)
Could not find the main class: com.oracle.cie.nextgen.launcher.Launcher. Program will exit.

Installation – 3rd run – JDK 1.7 rocks

Now I have downloaded and configured JDK 1.7. Another try, and now it works. Here is a short summary of the output. In the background now the Oracle Universal Installer is started.

Thanks to Gokhan Atil – http://www.gokhanatil.com – for the second possibility, you can use the JDK from the OMS too.

oracle@kestenholz:~/ [oms13cr2] export JAVA_HOME=/u00/app/oracle/product/jdk1.7.0_79/bin
oracle@kestenholz:~/ [oms13cr2] $JAVA_HOME/java -jar /u00/app/oracle/stage/6880880/opatch_generic.jar -silent oracle_home=/u00/app/oracle/product/em13cr2
Launcher log file is /tmp/OraInstall2016-12-02_10-05-14AM/launcher2016-12-02_10-05-14AM.log.
Extracting the installer . . . . Done
Checking if CPU speed is above 300 MHz.   Actual 2793.546 MHz    Passed
Checking swap space: must be greater than 512 MB.   Actual 2047 MB    Passed
Checking if this platform requires a 64-bit JVM.   Actual 64    Passed (64-bit not required)
Checking temp space: must be greater than 300 MB.   Actual 37290 MB    Passed


Preparing to launch the Oracle Universal Installer from /tmp/OraInstall2016-12-02_10-05-14AM
Installation Summary


Disk Space : Required 6 MB, Available 37,253 MB
Feature Sets to Install:
        Next Generation Install Core 13.9.1.0.0
        OPatch 13.9.1.0.0
        OPatch Auto OPlan 13.9.1.0.0
Session log file is /tmp/OraInstall2016-12-02_10-05-14AM/install2016-12-02_10-05-14AM.log

Loading products list. Please wait.
 1%
 40%

Loading products. Please wait.
 43%
 46%
 ...
 99%
Updating Libraries
Starting Installations
 1%
 2%
 3%
 ...
 91%
 92%

Install pending
Installation in progress
 Component : oracle.swd.opatch 13.9.1.0.0
Copying files for 'oracle.swd.opatch 13.9.1.0.0 '
 Component : oracle.glcm.osys.core 13.9.1.0.0
...
 Feature Set : oracle.glcm.opatchauto.core.actions.classpath
Post Feature installing 'oracle.glcm.opatchauto.core.actions.classpath'
Post feature install complete
String substitutions pending
...
Setting up 'oracle.glcm.opatchauto.core 13.9.1.0.0 '
Setup successful
Save inventory pending
Saving inventory
 93%
Saving inventory complete
 94%
Configuration complete
Logs successfully copied to /u00/app/oraInventory/logs.

Job done

Now the version was ok, the patch could be applied.

oracle@kestenholz:/u00/app/oracle/product/em13cr2/OPatch/ [oms13cr2] ./opatch version
OPatch Version: 13.9.1.0.0

OPatch succeeded.

Additional Information: If the installer does not find your inventory location (was happened to me on an AIX system), just execute the install command with the parameter -invPtrLoc.

java -jar /u00/app/oracle/stage/6880880/opatch_generic.jar -silent oracle_home=/u00/app/oracle/product/oms13cr2 -invPtrLoc /etc/oraInst.loc

The OMSPatcher can be updated like before, but the other important component in the patch process OPatch has has to be installed. From my point of view this is a step back, I don’t know what was the idea behind to change it. Now you have to be sure, that there is an existing JDK 1.7 available on your system. This means that applying a patch on the express way is not possible anymore, you have to fulfill the prerequisite before you have an actual OPatch. And at the moment, there is no other documentation than the README.txt available, the hint with the inventory parameter came from My Oracle Support after I opened a SR.

Say good-bye to Unzip, Copy & Paste.