alexblyth.net

Category: 18c

  • Oracle ExaCS – creating Oracle Homes with older RU/BPs

    While I still have access to an ExaCS, I wanted to check out the new feature available in the OCI console to patch an Oracle DB by moving it from one home to another (ie 19.4 to 19.6, not 12.2.0.1 to 19.x…).

    In reality, you might want to create an older home so you can restore a backup of a database, test patching procedures, performance and stability (using Real Application Testing) – to name a few ideas.

    Sounds all well and good, but I currently have a 19.6 DB, running on a 19.6 GI/ASM stack and am not in the position to patch to 19.7 at the moment. So what can I do?

    So we need to create a new Oracle Home – one with an RU (release update) of lower than 19.6.

    In the console, when I hit “Create Database Home”, it gives me the below options – note that they do not list the specific Release Update or Bundle Patch version.

    Create Database Home dialog

    As I mentioned, this is an ExaCS instance I created with 19.6 GI/ASM and 19.6 Database (currently the default RU version on this particular instance).

    What I need to do is download an earlier RU for ExaCS, activate it as the default, and then create a new Oracle Home either through the above console or using OCI CLI. So lets look at whats already on the ExaCS instance.

    Log on to one of the nodes as opc and sudo su - (sudo to root)

    We’re then going to fire up the dbaascli tool to check work with the ExaCS environment

    [root@exacs-asxyq1 ~]# dbaascli
DBAAS CLI version 19.4.4.0.0DBGI
DBAAS>

    First, lets check what Database Home images have already been downloaded to the ACFS software library using the dbimage list command

    DBAAS> dbimage list
Executing command dbimage list

INFO : Log file => /var/opt/oracle/log/dbimage_list/dbimage_list_2020-05-12_14:31:36.56429682748.log
INFO : dbimage fixup executed.

################## List of Available dbimages on acfs  ###################

-JAN2020 (For DB Versions 11204, 12102, 12201, 18000, 19000) => 24G consumed on acfs
-OCT2019 (For DB Versions 19000) => 4.0G consumed on acfs

######################## Default BP on acfs #############################

-11204 => JAN2020
-12102 => JAN2020
-12201 => JAN2020
-18000 => JAN2020
-19000 => JAN2020

################## Acfs disk space usage ######################

 Filesystem              Size  Used Avail Use% Mounted on
/dev/asm/acfsvol01-377  560G   45G  516G   8% /acfs01

################# /var/opt/oracle/dbaas_acfs/dbnid space usage ###################

 29G

    As you can see from lines 9 & 10, I currently have Oracle Home images for the JAN2020 RU/BPs for versions 11.2, 12.1, 12.2, 18c & 19c. I also have the OCT2019 RU for 19c already downloaded.

    Also note that the current default RU/BPs for each release are listed as:

    • 11204 => JAN2020
    • 12102 => JAN2020
    • 12201 => JAN2020
    • 18000 => JAN2020
    • 19000 => JAN2020

    Now lets check what Oracle Home images are available to download using the cswlib list command. 

    DBAAS>cswlib list
Executing command cswlib list

INFO : Log file => /var/opt/oracle/log/list/list_2020-05-12_14:25:49.32901082748.log
INFO : dbimage fixup executed.

############ List of Available BP #############
-APR2017 (For DB Versions  12201 12102 11204)
-JAN2018 (For DB Versions  12201 12102 11204)
-APR2018 (For DB Versions  12201 12102 11204)
-JUL2018 (For DB Versions  18000 12201 12102 11204)
-OCT2018 (For DB Versions  18000 12201 12102 11204)
-JAN2019 (For DB Versions  18000 12201 12102 11204)
-APR2019 (For DB Versions  18000 12201 12102 11204 19000)
-JUL2019 (For DB Versions  18000 12201 12102 11204 19000)
-OCT2019 (For DB Versions  18000 12201 12102 11204 19000)
-JAN2020 (For DB Versions  18000 12201 12102 11204 19000)

############ List of Available NONCDB BP #######
-APR2018 (For DB Versions  12201 12102)
-JAN2019 (For DB Versions  12201 12102)
-APR2019 (For DB Versions  12201 12102)
-JUL2019 (For DB Versions  12201 12102)
-OCT2019 (For DB Versions  12102 19000)
-JAN2020 (For DB Versions  12102 19000)

    UPDATE – 29th September 2020 – dbaascli cswlib list|download now only allows you to operate on the current, and two most recent RUs – ie, N, N-1 and N-2. See the screen grab below

    DBAAS>cswlib list
Executing command cswlib list

INFO : Log file => /var/opt/oracle/log/list/list_2020-09-29_15:08:33.757113220980.log
INFO : dbimage fixup executed.

############ List of Available BP #############
-JAN2020 (For DB Versions  18000 12201 12102 11204 19000)
-APR2020 (For DB Versions  18000 12201 12102 11204 19000)
-JUL2020 (For DB Versions  18000 12201 12102 11204 19000)

############ List of Available NONCDB BP #######
-JAN2020 (For DB Versions  12102 19000)
-APR2020 (For DB Versions  12102 19000)
-JUL2020 (For DB Versions  12102 19000)

    Without going through each matrix in detail, you can see that there are two kinds of images – CDB (the top list) and NONCDB (second list, and for use during migrations to OCI).

    As I already have the JAN2020 & OCT2019 RU for 19c downloaded, lets grab the APR2019 RU using the cswlib download command (help for cswlib download can be found here)

    DBAAS>cswlib download --version 19000 --bp APR2019
Executing command cswlib download --version 19000 --bp APR2019
INFO : Download running for BP APR2019 and version 19000, it may take few mins...
INFO: CSWLIB update APR2019 db image bits
INFO: Log file is: /var/opt/oracle/log/misc/cswlib/cswlib_2020-05-12_14:41:33.397675181223.log
INFO : dbimage fixup executed.
INFO: CSWLIB update_bits of APR2019 19000 succeded !
INFO: dbaascli dbimage activateBP needs to be run to make the downloaded BP the “default” !

    The least amount of information needed to download the RU/BP in question is the version & the BP name. In our case, we’re working with Oracle 19c (19000) and the ARP2019 RU.

    Once this has been downloaded you can rerun dbimage list to check it is available on the ExaCS. Also note that the current default is has not been changed as part of the download process.

    DBAAS>dbimage list
Executing command dbimage list

INFO : Log file => /var/opt/oracle/log/dbimage_list/dbimage_list_2020-05-12_15:59:24.01757782748.log
INFO : dbimage fixup executed.

################## List of Available dbimages on acfs  ###################

-JAN2020 (For DB Versions 11204, 12102, 12201, 18000, 19000) => 24G consumed on acfs
-OCT2019 (For DB Versions 19000) => 4.0G consumed on acfs
-APR2019 (For DB Versions 19000) => 3.6G consumed on acfs

######################## Default BP on acfs #############################

-11204 => JAN2020
-12102 => JAN2020
-12201 => JAN2020
-18000 => JAN2020
-19000 => JAN2020

################## Acfs disk space usage ######################

 Filesystem              Size  Used Avail Use% Mounted on
/dev/asm/acfsvol01-377  560G   49G  512G   9% /acfs01

################# /var/opt/oracle/dbaas_acfs/dbnid space usage ###################

 32G

    Next, we need to Activate the ARP2019 image as the default so we can then kick off the deployment of the home to the two ExaCS compute nodes. We do this using the dbimage activateBP command.

    DBAAS>dbimage activateBP --version 19000 --bp APR2019
Executing command dbimage activateBP --version 19000 --bp APR2019
INFO : Log file is => /var/opt/oracle/log/dbimage_activateBP/dbimage_activateBP_2020-05-12_16:01:50.51664582748.log
INFO : dbimage fixup executed.
INFO : activateBP completed

    Checking the dbimage list again, we can see the APR2019 image is now the default for 19000

    DBAAS>dbimage list
Executing command dbimage list
...
snipped for brevity
...
######################## Default BP on acfs #############################

-11204 => JAN2020
-12102 => JAN2020
-12201 => JAN2020
-18000 => JAN2020
-19000 => APR2019
...

    Now that we have activated to APR2019 RU, we can deploy the home. Unfortunately (or maybe fortunately) this is not done through the dbaascli. Instead, we can use either the OCI browser console or using the OCI CLI. Lets use the browser console.

    Navigate to your ExaCS -> Database Homes and click Create Database Home.

    ExaCS – Create Database Home

    As shown above, when you create a new Database on the ExaCS, you choose the major database version, not the RU. We’ll choose 19c – given thats what we downloaded.

    Once you’ve give the home a name (or used the auto-generated one). Press Create.

    Complete

    Once created, you can see the Database Home Path and the Database Version. Note that it takes a little while for the console to reflect the actual version – in this case it will reflect 19.3.0.0.

    We can check the version before proceeding by running opatch lsinventory from the newly created Database Home (after setting the ORACLE_HOME accordingly).

    [oracle@exacs-asxyq1 ~]$ export ORACLE_HOME=/u02/app/oracle/product/19.0.0.0/dbhome_3
[oracle@exacs-asxyq1 ~]$ $ORACLE_HOME/OPatch/opatch lsinventory
Oracle Interim Patch Installer version 12.2.0.1.17
Copyright (c) 2020, Oracle Corporation.  All rights reserved.


Oracle Home       : /u02/app/oracle/product/19.0.0.0/dbhome_3
Central Inventory : /u01/app/oraInventory
   from           : /u02/app/oracle/product/19.0.0.0/dbhome_3/oraInst.loc
OPatch version    : 12.2.0.1.17
OUI version       : 12.2.0.7.0
Log file location : /u02/app/oracle/product/19.0.0.0/dbhome_3/cfgtoollogs/opatch/opatch2020-05-12_17-03-26PM_1.log

Lsinventory Output file location : /u02/app/oracle/product/19.0.0.0/dbhome_3/cfgtoollogs/opatch/lsinv/lsinventory2020-05-12_17-03-26PM.txt

--------------------------------------------------------------------------------
Local Machine Information::
Hostname: exacs-asxyq1.exacsclient.suncorpmelvcn.oraclevcn.com
ARU platform id: 226
ARU platform description:: Linux x86-64

Installed Top-level Products (1):

Oracle Database 19c                                                  19.0.0.0.0
There are 1 products installed in this Oracle Home.


Interim patches (3) :

Patch  29548437     : applied on Sat Apr 20 07:53:25 AEST 2019
Unique Patch ID:  22861714
Patch description:  "OJVM RELEASE UPDATE: 19.3.0.0.190416 (29548437)"
   Created on 11 Apr 2019, 02:24:07 hrs PST8PDT
   Bugs fixed:
     29540327, 29254623, 29445548

Patch  29585399     : applied on Thu Apr 18 17:21:33 AEST 2019
Unique Patch ID:  22840393
Patch description:  "OCW RELEASE UPDATE 19.3.0.0.0 (29585399)"
   Created on 9 Apr 2019, 19:12:47 hrs PST8PDT
   Bugs fixed:
     SNIPPED

Patch  29517242     : applied on Thu Apr 18 17:21:17 AEST 2019
Unique Patch ID:  22862832
Patch description:  "Database Release Update : 19.3.0.0.190416 (29517242)"
   Created on 17 Apr 2019, 23:27:10 hrs PST8PDT
   SNIPPED



--------------------------------------------------------------------------------

OPatch succeeded.

    Lets create our database now. Press Create Database and fill in the subsequent dialog as you see fit. Perhaps something like the following:

    Create Database

    I also enabled Automatic Backups with a 7 Day Retention Period

    Once the database has been created (and the auto backup finished) you’ll see your database is now available and ready to go. Note again that it will take a little while for the console to reflect that the database version is 19.3.0.0.

    Database creation complete

    And that’s it!!

    Cheers

    Alex

  • Create an Oracle Database System in Oracle Cloud Infrastructure

    This guide walks you through creating an Oracle 18c Database System in Oracle Cloud Infrastructure (OCI).

    Note: I am assuming that you have already created all the necessary networking components in your OCI tenancy.


    OCI Home Page

    Starting from the OCI Home page, hit the Menu link in the top left-hand corner, and the select “Bare Metal, VM, and Exadata” under the Database heading.

    Check you’re in the right compartment

    Hit the blue “Launch DB System” button and fill in the form and press “Launch DB System” at the bottom

    After you hit “Launch DB System”, the console will report the system is provisioning

    Database system being provisioned

    Time to grab a cuppa or lunch and leave this to run for a while. You’ll know when its done when the orange box with “DBS” in it goes a lovely green colour.

    And you’re done. You can now ssh to the VM and start using the database.

    To connect applications, SQL Developer etc, you’ll have to add port 1521 to the subnet Security List the Database System was provisioned in. Port 1521 is already open on the VM firewall.

  • Oracle 18c != Autonomous Database, But Autonomous needs 18c

    When Oracle (yes, I work for the big O) announced that it was building out its Autonomous Database Platform – starting with the Autonomous Data Warehouse Cloud (ADWC) – I started hearing a lot of statements that Oracle 18c would be Autonomous in and of itself. This is not the case.

    The very excellent Tim Hall “ranted” (not really) about this late last year after he got an early view of the Autonomous platform at OOW17. Read his view here -> Tim Hall – Oracle Autonomous Database and the Death of the DBA.

    So what is 18c? Its the latest version of the Oracle Database – also known as 12.2.0.2. That is its the 12.2 database with what Oracle used to call a patchset. As of 18c, Oracle has moved a “yearly” release cycle with quarterly release updates and release update revisions in between. I’ll write more on this another time.

    As with releases of Oracle Database over the past 20-25 years (remember that 9i was the “Self Managing , 18c includes, adds and/or enhances a vast array of features that are designed to reduce the amount of time DBAs need to design and build the database and server infrastructure, keep the database up and running, make it perform, and keep it patched and upgraded. Features in this category include Automatic Storage Management, SQL Tuning Sets, SQL Plan Management, Automatic Memory Management, Automatic Data Optimisation and and and…

    The issue with the above is that you had to choose to use almost all of them. Yes, part of the “choice” was that you had to choose to buy the license, such as Advanced Compression to get access to Automatic Data Optimisation, but putting that aside for a minute, you had to choose to use whatever feature might help you in a given situation.

    Oracle Autonomous Database effectively takes the need to choose away from the user, by turning on all the automated features that Oracle have been building into Oracle Database, in favour of providing a standardised database platform that tunes, upgrades, patches and secures itself in an ongoing manner. Add into the mix an autonomous management engine to keep everything on track.

    Oracle has stated that the Autonomous Database services will be available in Oracle Cloud and, over time, on the Cloud @ Customer platforms. 18c Database is the underlying database version for Autonomous.

    18c will, as have past releases, be available for DBAs, devs and anyone else who is interested, to download and install on their own infrastructure. But the parts that make an Autonomous service will be cloud only offerings.

    As Tim put it so succinctly,

    Less time on boring shit. More time on important shit!

    Hear, hear!! Stop doing the stuff you did as a junior DBA, and get stuck into the interesting stuff. Help devs write better SQL, learn features and tools you haven’t needed or didn’t have licenses to use in the past – features like Spatial & Graph, R, Database In-Memory – and apply what you discover to the problems in the context of your business and its unique needs.

    BUT, this doesn’t mean that knowing how to build a database, when to choose to use a feature and when not to is not also still important. You can’t abdicate your responsibility as a consumer to ensure that you are using the right service for the right purpose at the right time. You’ll just have to do less of the grunt work over time.

    In my view, Autonomous Databases are the next evolution of data management platforms and a welcome one. The less process work that needs to be done manually the better. Let’s get back to the interesting shit!

     

  • Oracle Database 18c Available

    A quick note to say that Oracle Database 18c is now available on Oracle Cloud and Engineered Systems.

    Please read Dom Giles overview post of 18c at: https://blogs.oracle.com/database/oracle-database-18c-:-now-available-on-the-oracle-cloud-and-oracle-engineered-systems

    Dom covers some of the key features introduced or improved in this release.

    In particular, there are some interesting improvements that I anticipate the Autonomous Database Services to use heavily under the covers. These include the Zero Impact Patching for Grid Infrastructure, Online PDB relocate – including connection redirection, improvements to patching Java in the database (OJVM) and more.

    The 18c New Features Guide is a great place to start getting more detail.