My journey to the Cloud – Bringing it together

My journey moves on. Today I’ll show you my first use case inside the cloud. As you may know, the Autonomous Databases within the Free Tier will be shut down after 7 days of inactivity. Because this is my cloud testing environment, this may happen quite often depending on how many time I can spend, or better, cannot spend. So I will use my Compute Instance to connect to my Autonomous Database on a regular basis.

First, I have to prepare my VM to do that. So what will I need? Of cause an Oracle Client. For this example I chose to use the Instant Client installed from a RPM package, here is a short overview from Oracle. First, I added the Instant Client Repo to my Yum repositories.

[root@dbamarco-02 ~]# cat /etc/yum.repos.d/oracle-inst-client.repo

[ol7_instant_client]
name=Oracle Instant Client ($basearch)
baseurl=http://yum.oracle.com/repo/OracleLinux/OL7/oracle/instantclient/x86_64
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-oracle
gpgcheck=1
enabled=1

Given that, I am now able to install the Instant Client from that repo. This of cause requires internet access of your compute instance.

[root@dbamarco-02 ~]# yum install oracle-instantclient19.6-sqlplus
Loaded plugins: langpacks, ulninfo
Resolving Dependencies
--> Running transaction check
---> Package oracle-instantclient19.6-sqlplus.x86_64 0:19.6.0.0.0-1 will be installed
--> Processing Dependency: oracle-instantclient19.6-basic >= 19.6.0.0.0 for package: oracle-instantclient19.6-sqlplus-19.6.0.0.0-1.x86_64
--> Running transaction check
---> Package oracle-instantclient19.6-basic.x86_64 0:19.6.0.0.0-1 will be installed
--> Finished Dependency Resolution

Dependencies Resolved

==========================================================================================
 Package                           Arch      Version        Repository               Size
==========================================================================================
Installing:
 oracle-instantclient19.6-sqlplus  x86_64    19.6.0.0.0-1   ol7_instant_client      687 k
Installing for dependencies:
 oracle-instantclient19.6-basic    x86_64    19.6.0.0.0-1   ol7_instant_client       52 M

Transaction Summary
==========================================================================================
Install  1 Package (+1 Dependent package)

Total download size: 52 M
Installed size: 229 M
Is this ok [y/d/N]: y
Downloading packages:
(1/2): oracle-instantclient19.6-sqlplus-19.6.0.0.0-1.x86_64.rpm               | 687 kB  00:00:01
(2/2): oracle-instantclient19.6-basic-19.6.0.0.0-1.x86_64.rpm                 |  52 MB  00:02:19
-----------------------------------------------------------------------------------------------------
Total                                                                385 kB/s |  52 MB  00:02:19
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
  Installing : oracle-instantclient19.6-basic-19.6.0.0.0-1.x86_64                                1/2
  Installing : oracle-instantclient19.6-sqlplus-19.6.0.0.0-1.x86_64                              2/2
  Verifying  : oracle-instantclient19.6-sqlplus-19.6.0.0.0-1.x86_64                              1/2
  Verifying  : oracle-instantclient19.6-basic-19.6.0.0.0-1.x86_64                                2/2

Installed:
  oracle-instantclient19.6-sqlplus.x86_64 0:19.6.0.0.0-1

Dependency Installed:
  oracle-instantclient19.6-basic.x86_64 0:19.6.0.0.0-1

Complete!

To connect to my ATP service, I need the connection information. This can be downloaded from the OCI Web. The ZIP can then be transferred to the VM. So I did that and extracted the ZIP.

[opc@dbamarco-02 ~]$ mkdir ADB
[opc@dbamarco-02 ~]$ unzip -d ADB Wallet_MMIATP.zip
Archive:  Wallet_MMIATP.zip
  inflating: ADB/cwallet.sso
  inflating: ADB/tnsnames.ora
  inflating: ADB/truststore.jks
  inflating: ADB/ojdbc.properties
  inflating: ADB/sqlnet.ora
  inflating: ADB/ewallet.p12
  inflating: ADB/keystore.jks

To use this configuration, I needed to change the SQLNET.ORA to reflect the location of the wallet files. The orignal line:

WALLET_LOCATION = (SOURCE = (METHOD = file) (METHOD_DATA = (DIRECTORY="?/network/admin")))

now has the absolute path:

WALLET_LOCATION = (SOURCE = (METHOD = file) (METHOD_DATA = (DIRECTORY="/home/opc/ADB")))

Once I point my TNS_ADMIN to the right directory, I am now able to connect to my Autonomous Database Service.

[opc@dbamarco-02 ~]$ export TNS_ADMIN=/home/opc/ADB
[opc@dbamarco-02 ~]$ sqlplus admin/**********@mmiatp_high

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Jun 3 11:02:57 2020
Version 19.6.0.0.0

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

Last Successful login time: Tue May 05 2020 13:47:50 +00:00

Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.4.0.0.0

SQL> exit

The last step is to automate this. I will do this simply with a daily cron job that calls a shell script which basically runs the above.

[opc@dbamarco-01 ~]$ crontab -l
0 * * * * /home/opc/ADB/atp_keep_alive.sh
[opc@dbamarco-01 ~]$ cat /home/opc/ADB/atp_keep_alive.sh
#!/bin/bash
export ORACLE_HOME=/opt/oracle/product/18c/dbhomeXE
export PATH=$ORACLE_HOME/bin:$PATH
export TNS_ADMIN=/home/opc/ADB

$ORACLE_HOME/bin/sqlplus -s dbamarco/********@mmiatp_high << EOF > /dev/null
select user from dual;
exit
EOF

As you can see, the cronjob connects to my Autonomous Database once a day. This is sufficient to keep my database up and running. It will never be shut down by OCI as long as the job runs.

My journey to the Cloud – Autonomous Database

Now that I have my OCI network created and launched a compute instance, it is now time to create what Oracle stands for since decades, a database. For the Always Free tier there is only one option for that, an Autonomous Database. I go straight through the dialog, there are still some options even though it is autonomous.

First, I choose a name and the type of database. This time I create an Autonomous Data Warehouse on shared infrastructure.

Next, I choose the version. At the time of screenshotting this, the only option was 18c. And as you can see, there is no chance to modify the resources, they are fixed for the Always Free offerings. And note the paragraph, if the database is not used for 7 days in a row, the database will be shut down. After another 3 months it will then get deleted. But you’ll get emails beforehand that warn you. Ok, next.

Now I define my admin password and the way you connect to the database. I want my database to be accessible from everywhere since I do not have a private connection to my cloud resources.

And that’s it. After a (short) while, I get my autonomous database and can now access it. But how? Quite easily. I download the connection description and just use it. A click on “DB Connection” opens the download dialog.

What I get is a ZIP file that contains everything I need. I can use this ZIP directly from SQL*Developer:

Now I am ready to start my first real things in the Oracle Cloud. A good thing to start with is APEX. It comes right with your autonomous database.

You see, I have another autonomous database of type Transaction Processing running in my cloud.

But there are still some open points, how can I keep my databae up and running? And how do I get some data in there? I’ll answer these questions in some future blog posts.

My journey to the Cloud – Compute Instance

In my previous post I finished the setup of my initial network in my OCI environment. Given that, I am now ready to create my first cloud service that will be really usable at the end. On the dashboard, that you get when you login into OCI, I am offered several services that are free tier eligible. First I’ll go with the compute instances of which I am allowed to have two with the free tier.

On that initial screen, I simply choose “Create a VM instance” which will start a wizard to guide me through the creation of my first compute instance. At first I need a name for my VM and a SSH key. There is no option to create one, so I created it myself.

$ ssh-keygen -t rsa -f my-oci-key
Generating public/private rsa key pair.
Enter passphrase (empty for no passphrase):
Enter same passphrase again:
Your identification has been saved in my-oci-key.
Your public key has been saved in my-oci-key.pub.
The key fingerprint is:

$ ls -l my-oci*
-rw-r--r--    1 marco.mi UsersGrp      1675 Mar 16 09:08 my-oci-key
-rw-r--r--    1 marco.mi UsersGrp       404 Mar 16 09:08 my-oci-key.pub

Now I can use this public key to create my VM. For the other settings I just go with anything that is “Always Free Eligible”.

Next step is to define the network settings. Since I already prepared my network, I simply choose this network. Otherwise the cloud would create some default network for me which is not what I want it to do.

Another quite important setting is the hostname property. Per default, this is not the name of the service but something artificial. So specify the hostname to match my service name.

After all that I end up with my compute instance up and running. You can see from my screenshot, that it was not my first VM at all, I already had another.

Since my VM has a public IP, I can now connect to it.

$ ssh -X -I my-oci-key opc@***.***.***.32
[opc@dbamarco-01 ~]$ uname -a
Linux dbamarco-01 4.14.35-1902.10.8.el7uek.x86_64 #2 SMP Thu Feb 6 11:02:28 PST 2020 x86_64 x86_64 x86_64 GNU/Linux
[opc@dbamarco-01 ~]$ cat /etc/os-release
NAME="Oracle Linux Server"
VERSION="7.7"
ID="ol"
ID_LIKE="fedora"
VARIANT="Server"
VARIANT_ID="server"
VERSION_ID="7.7"
PRETTY_NAME="Oracle Linux Server 7.7"
ANSI_COLOR="0;31"
CPE_NAME="cpe:/o:oracle:linux:7:7:server"
HOME_URL="https://linux.oracle.com/"
BUG_REPORT_URL="https://bugzilla.oracle.com/"

ORACLE_BUGZILLA_PRODUCT="Oracle Linux 7"
ORACLE_BUGZILLA_PRODUCT_VERSION=7.7
ORACLE_SUPPORT_PRODUCT="Oracle Linux"
ORACLE_SUPPORT_PRODUCT_VERSION=7.7

The “opc” user has privileges to “sudo” to any user.

[opc@dbamarco-01 ~]$ sudo su -
Last login: Wed Jun  3 09:56:14 GMT 2020 on pts/0
[root@dbamarco-01 ~]#

That’s it for today. My next step will be the creation of an Autonomous Database.

My journey to the Cloud – OCI Networking II

In the previous post I explained how I created my initial networks in the OCI. The story continues in this post when I enable my network to connect to the world. To achieve that, I need basically three things. First I need some instance that establishes the connection, this will be the gateway. Second I need some rules to route the traffic from my cloud network through the gateway and third I need some firewall rules to allow only specific protocols.

For the gateway I have to choose from

  • Internet Gateway – this allows connections from/to the internet
  • NAT Gateway – this allows connections from the cloud to the internet
  • Dynamic Routing Gateway – allows connection from/to local to the cloud network via VPN
  • Local Peering Gateway – connects two VCNs
  • Service Gateway – connects to a service in the cloud

For my purpose I will use an Internet Gateway. My subnet allows for public IPs and I do not have a dedicated VPN connection to my cloud network.

Now that I have my gateway, I can continue to create some routing rules for my subnet. As mentioned earlier, I already have a “Default Route Table” for my subnet. But it has no rules yet.

So I add a rule that routes all traffic from that subnet to the internet gateway. This is the only rule I’ll need for my purposes.

So that’s it. Last thing is to create some security rules. Here I have two options. Option one is Security Lists, the other option is Security Groups. A Security List defines ingress and egress rules at the subnet level whilst a Security Group defines this rules at the service (compute instance, database instance, …) level. At the end, both security definitions are joined together and applied to the network. For my purposes and to be a little more safe, I’ll use Security Groups. But before I do so, I need to create a compute instance. This I’ll describe in the nextblog post.

My journey to the Cloud – OCI Networking I

My journey to the (Oracle) Cloud started roughly two years ago. Since then I wanted to share my experiences and now I finally managed to start this series of blog posts. But things change quickly in the cloud, so don’t blame me if there are screenshots or code snippets that are not up-to-date anymore. On the other hand I am quite sure, that the basics and concepts are recognizable anyways. For my learning and testing I am using the Always Free Tier of Oracle Cloud Infrastructure (OCI) which offers quite a lot of basic functionality and allows for quick testing.

At the end of the day the cloud shall provide some services. That might be a server, a database or a complete application or anything else you can imagine. For my examples I’ll focus on servers and databases. But before I come to the point where I will create such ressources, I need to create the infrastructure for that. The cloud is nothing else than a data center that is just located somewhere. Oracle organizes these into Regions, Availability Domains and Fault Domains. You may reas more about that in the documentation. I am using the Frankfurt region for my services.

Before I create any service in my cloud environment, I gave to prepare some basic inftrastructure. That is basically a network. The whole thing is called a Virtual Cloud Network (VCN) which can consist of one or more Subnets. If you create an always-free compute instance, Oracle will create these ressources for you, but this is not what I want it to be. So I create my VCN at first. Right now there is nothing at all.

During creation of the VCN, I need to name it and define the IP range the VCN will have. Do not size it too small since all the Subnets in this VCN will use a subset of this IP range.

There is noting more to do. Once the VCN is created, I see it in the list and I also see that a default route table was created automatically. I’ll come back to this later.

Next step is to create a subnet. Again I need a name for it and an IP range. Beside that, I can choose between public and private. I chose public since I want to be able to connect to my ressources without having to establish some kind of VPN.

Here you see that there is also a Default Security List in place which I did not create myself. The next steps will be to create some rules to allow and route the IP traffic from and to my newly created cloud network. This will be part of the next blog post.

One final statement at this point. The setup of your cloud networks is essential for everythings that follows. You cannot change a networks properties once it is created. The only way is to drop and create the networks from scratch. It is obvious that at that time, resources must not be using these networks. To cut a long story short, it is the same as if you would plan a “physical” data center. The network is the basis for everything. So be very very careful when planning and creating your cloud networks.

Restore Points and Upgrade – not for SE2

This time I want to tell something about Oracle database upgrades and what to do if something goes wrong during the upgrade. In that case the database needs to be reset to the time before the upgrade started. The Database Upgrade Assistant and also the Autoupgrade Tool can create a guaranteed restore point for that. That means, even with Flashback Database not enabled, the database will generate flashback logs beginning with the creation of that restore point. These flashback logs will get deleted only when the guaranteed restore point is dropped. For Standard Edition 2, Autoupgrade does not create a restore point and there is a reason for that. I tried that before doing a manual upgrade and these are my results.

First, I checked the database and created a guaranteed restore point.

[oracle@vm123 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Wed Feb 10 08:08:16 2021

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


Connected to:
Oracle Database 11g Release 11.2.0.4.0 - 64bit Production

SQL> col name format a60
SQL> set lines 200
SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------------------------------------------
NO

SQL> create restore point upgrade_db guarantee flashback database;

Restore point created.

SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------------------------------------------
RESTORE POINT ONLY

SQL> select * from v$flashback_database_logfile;

NAME                                                               LOG#    THREAD#  SEQUENCE#      BYTES FIRST_CHANGE# FIRST_TIME          TYPE
------------------------------------------------------------ ---------- ---------- ---------- ---------- ------------- ------------------- ---------------------------
/u01/app/oracle/fra/ORCL112B/flashback/o1_mf_j271mq1h_.flb            1          1          1 1073741824      46358813 2021-02-10 08:08:44 NORMAL
/u01/app/oracle/fra/ORCL112B/flashback/o1_mf_j271mzof_.flb            2          1          1 1073741824             0                     RESERVED

SQL> select NAME, TIME, GUARANTEE_FLASHBACK_DATABASE, PRESERVED from v$restore_point;

NAME                                                         TIME                                                                        GUARANTEE PRESERVED
------------------------------------------------------------ --------------------------------------------------------------------------- --------- ---------
UPGRADE_DB                                                   10.02.21 08:08:38,000000000                                                 YES       YES

You see, even in Standard Edition I can create a restore point and V$DATBASE reflects this and tells us, that Flashback is enabled just for the guaranteed restore point. Also we have some flashback logs being created to hold the data neccessary for doing the flashback. So everything looks fine at this point.

But what will happen, when I try to rewind my database to that restore point? Let’s have a look.

[oracle@vm123 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Wed Feb 10 08:16:00 2021

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


Connected to:
Oracle Database 11g Release 11.2.0.4.0 - 64bit Production

SQL> select NAME, TIME, GUARANTEE_FLASHBACK_DATABASE, PRESERVED from v$restore_point;

NAME          TIME                           GUARANTEE PRESERVED
------------- ------------------------------ --------- ---------
UPGRADE_DB    10.02.21 08:08:38,000000000    YES       YES

SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------------------------------------------
RESTORE POINT ONLY

SQL> select status from v$instance;

STATUS
------------------------------------
MOUNTED

SQL> flashback database to restore point upgrade_db;
flashback database to restore point upgrade_db
*
ERROR at line 1:
ORA-00439: feature not enabled: Flashback Database

So even if we have everything in place to do the Flashback operation, the database denies that. And that is, because Flashback Database is an Enterprise Edition feature. The fact, that we can easily create a guaranteed restore point in a Standard Edition database is a little misleading, be aware of that. In my opinion it would be better to get the error message right when creating such a restore point like when we try to enable Flashback Database in general.

SQL> alter database flashback on;
alter database flashback on
*
ERROR at line 1:
ORA-00439: feature not enabled: Flashback Database

In case you have a single strategy for upgrading your databases and have both Enterprise and Standard Edition in place, do not rely on the successful creation of a restore point as your fallback. Do differentiate between both Editions and choose your backup wisely.

Misleading ORA-1555

Recently a colleague of mine was upgrading a database from 12.1.0.2 non-CDB to 19c PDB. Since the database was more than 1TB he used the following approach:

  1. Create a 19c CDB without any PDBs
  2. startup old 12.1 database read only
  3. create XML for plugging in the database
  4. stop the old 12.1 database
  5. plugin the database into the 19c CDB using the XML and NOCOPY option
  6. Upgrade the new PDB

So let me outline that a bit more in detail. This is what was done at the source database.

SQL> shutdown immediate
SQL> startup open read only

SQL> begin
2      dbms_pdb.describe(pdb_descr_file => '/u01/software/dbp/xxxx.xml');
3    end;
4    /

SQL> shutdown immediate

Having that, the next steps were done at the destination 19c CDB.

SQL> CREATE PLUGGABLE DATABASE XXXX USING '/u01/software/dbp/xxxx.xml'
2    NOCOPY
3    TEMPFILE REUSE;

SQL> alter pluggable database xxxx open upgrade;

$ cd $ORACLE_HOME/rdbms/admin
$ $ORACLE_HOME/perl/bin/perl catctl.pl -c "xxxx" -l /u01/oracle catupgrd.sql

And then, after rebooting everythin, the fun started. Nearly every application that connected to the new PDB including SQL*Developer got an ORA-1555 error:

ORA-00604: error occurred at recursive SQL level 1
ORA-01555: snapshot too old: rollback segment number 10 with name "_SYSSMU10_2194929394$" too small

Since this looked like an undo retention issue somehow, we checked the undo_retention parameter, set it to a higher value but without any change. Next, we checked all the LOBs inside the database and set their retention accordingly, but still no change. We even created a new UNDO tablespace and switched the PDB to that newly created tablespace, but still no change. Also the alert.log had no additional information. That’s why we then added some events to get traces. There is a nice explanation here: https://blogs.oracle.com/db/master-note-for-ora-1555-errors.

SQL> alter system set events '1555 trace name errorstack forever, level 3';
SQL> alter system set events '10442 trace name context forever, level 10';

We then raised the error again and searched for the traces. But there were no traces at all. At least not where we would expected them.

I tried to find any traces in ADR that were created in the last 60 minutes.

$ find $ORACLE_BASE -mmin -60 -name "*ora*trc" -type f

And I found one, but in the directory of the old 12.1 database:

Trace file /u01/app/oracle/diag/rdbms/xxxx/XXXX/trace/XXXX_ora_12346.trc
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options
ORACLE_HOME = /u01/app/oracle/product/12.1.0/dbhome_1
System name:    Linux
Node name:      xxxx0003
Release:        4.12.14-95.6-default
Version:        #1 SMP Thu Jan 17 06:04:39 UTC 2019 (6af4ef8)
Machine:        x86_64
Instance name: XXXX
Redo thread mounted by this instance: 1
Oracle process number: 33
Unix process pid: 12346, image: oracle@xxxx0003


*** 2021-01-30 18:32:16.011
*** SESSION ID:(695.19613) 2021-01-30 18:32:16.011
*** CLIENT ID:() 2021-01-30 18:32:16.011
*** SERVICE NAME:(XXXX) 2021-01-30 18:32:16.011
*** MODULE NAME:(RCP:xxxxx:64040) 2021-01-30 18:32:16.011
*** CLIENT DRIVER:(jdbcthin : 19.3.0.0.0) 2021-01-30 18:32:16.011
*** ACTION NAME:() 2021-01-30 18:32:16.011

Attempt to read  undo record @ tsn: 188 uba: 0x01410eff.22ec.3e for CR-application for  xid: 0x000a.005.00007679 failed

And then I had the strange feeling, that there might be something else accessing the database. A quick check revealed. that the old 12.1 database was running again, for whatever reason.

My assumption is, that due to the plugin operation, the database got new SYSTEM and UNDO tablespaces, the controlfiles are from the CDB anyway. So the old 12.1 non-CDB and the new 19c PDB shared only the user datafiles and could startup concurrently without any trouble. Only when accessing the user data, the instance wants to perform a rollback for read consistency and was not able to find the propper undo segment since that undo segment resided in another place. That is my theory about that.

Finally, we stopped and disabled the old 12.1 database (instance). From that point on the PDB worked like a charme and happily there were no corruptions at all as far as we checked.

Conclusion: If you migrate from non-CDB to PDB on the same host using the NOCOPY option, prevent the old instance from ever starting again. You might run into serious trouble.

Dynamic CPU Scaling

Starting with Oracle Database 19.4 a new feature called “Dynamic CPU Scaling” got introduced. There is an announcement from Oracle about this feature and also a couple of other blog posts related to this new feature. The basic idea is, that with the new Multitenant architecture all pluggable databases (PDBs) share the CPUs defined by CPU_COUNT in the container database (CDB). By default each and every PDB has the same value for CPU_COUNT in the CDB. Basically this is an over-allocation of CPU resources. That’s why Oracle introduced a new parameter called CPU_MIN_COUNT that shall be available to a specific PDB in any case to preserve a minimum of CPU capacity.

Since this new feature might become very handy sometime in the future, I wanted to try it out and see how it actually works. My setup consists of one CDB version 19.9 and two PDBs. For both PDBs I have a SwingBench SOE benchmark with 20 concurrent sessions prepared to put some CPU load on them.

SQL> show pdbs

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1                           READ WRITE NO
         4 PDB2                           READ WRITE NO

SQL>  select CON_ID, name, value from v$system_parameter where name like 'cpu%count' and con_id in (0,3,4);

    CON_ID NAME                 VALUE
---------- -------------------- ----------
         0 cpu_count            16
         0 cpu_min_count        16
         3 cpu_min_count        16
         4 cpu_min_count        16

6 rows selected.

My CPU_COUNT equals 16 which is by funny coinsidence the limit that a Standard Edition 2 has due to license restrictions.

Having that, let’s put some load on PDB1 and see what happens. For the whole blog post, PDB1 graphs will be on the left window, PDB2 graphs on the right window.

We see, we get some steady load for PDB1 with that 20 sessions. Actually we have 16 active sessions and 4 waiting for CPU. That is what I wanted to achieve. Now I start the other SwinBench run which starts another 20 sessions but againts PDB2.

Unfortunately the graphs are sorted in a different manner but we can see, that the throughput of PDB1 drops because PDB2 now requires some CPU resources too. Interresting to see, that the throughput does not decrease to a certain level and then stays there constantly. It just flips up and down for both PDBs. If PDB1 is in favour, PDB2 suffers. And some seconds later this changes to the opposite and PDB2 is in favour. And some more seconds later it changes again… and again… and again, see:

Now I reduce the CPU_MIN_COUNT for PDB1

SQL> alter session set container=pdb1
  2  /

Session altered.

SQL> alter system set cpu_min_count=10 container=current;

System altered.

As you can see there is not really a change in behaviour. It just stumbles a bit and then goes on as nothing had changed.

So my next step was to reduce the CPU_MIN_COUNT for PDB2 down to 2. From the CPU_MIN_COUNT perspective that means we are not overallocating anymore.

SQL> alter session set container=pdb2
  2  /

Session altered.

SQL> alter system set cpu_min_count=2 container=current;

System altered.

Now the throughput of PDB1 raises again and the the one for PDB2 goes down. Obviously the database is now able to manage the load according to the shares that I defined by setting the CPU_MIN_COUNT, maybe because I am not over-commiting anymore.

My next step was to increase the CPU_MIN_COUNT for PDB2 again to 6. That means, I have now 16 CPUs in minimum over both PDBs.

SQL> alter session set container=pdb2
  2  /

Session altered.

SQL> alter system set cpu_min_count=6 container=current;

System altered.

Again, the throughput of PDB1 goes down to allow for more throughput on PDB2. And after a while, it is flipping again.

It is also notable, that the numbers are really similar even though the CPU_MIN_COUNTs are different. Remember, at this point PDB1 has 10 CPUs in minimum whilst PDB2 has 6 CPUs in minimum.

Finally, I reduced the CPU_MIN_COUNT for PDB1 down to 2.

SQL> alter session set container=pdb1
  2  /

Session altered.

SQL> alter system set cpu_min_count=2 container=current;

System altered.

Now I am not over-commiting anymore and the throughput for both PDBs reaches a steady level again without any flipping.

At the end this means for me, that the CPU scaling occurs only if the sum of all CPU_MIN_COUNTs over all PDBs is lower than the actual CPU_COUNT defined in the CDB. Or at least, it seems to work as I would have expected in that case. But nevertheless it is a handy feature to limit CPU resources and noisy neighbor issues in a multitenant environment.

Oracle ASM and PDB Snapshot Copy

With the introduction of the Multitenant Architecture for Oracle databases came several ways how a new Pluggable Database (PDB) can be created. One of those ways is the “Snapshot Copy”. That way, the new PDB is not fully persisted, only the changes are written to disk. This saves some (or a lot) storage and is fast. In case the underlying filesystem supports storage snapshot, Oracle makes use of this feature. ACFS can do this for instance. Otherwise the filesystem must support sparseness which is creating a file in size X but only allocating a tiny bit of storage. Oracle then uses a copy-on-write mechanism to store changes to the PDB snapshot copy.

During my presentation about 19c Standard Edition 2 at DOAG Conference 2020 the question came up, if those PDB Snapshot Copies can be created on ASM to. I could not answer that question, so let’s try it.

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDBASM1                        READ WRITE NO
SQL> show parameter clonedb

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
clonedb                              boolean     FALSE
clonedb_dir                          string
SQL> select file_name from cdb_data_files;

FILE_NAME
--------------------------------------------------------------------------------
+DATA/ASMSE2/DATAFILE/undotbs1.293.1056972257
+DATA/ASMSE2/DATAFILE/system.284.1056972207
+DATA/ASMSE2/DATAFILE/sysaux.292.1056972243
+DATA/ASMSE2/DATAFILE/users.304.1056973161
+DATA/ASMSE2/B48865836FE9D1C6E0533E24100AE822/DATAFILE/system.301.1056972901
+DATA/ASMSE2/B48865836FE9D1C6E0533E24100AE822/DATAFILE/sysaux.302.1056972901
+DATA/ASMSE2/B48865836FE9D1C6E0533E24100AE822/DATAFILE/undotbs1.300.1056972901
+DATA/ASMSE2/B48865836FE9D1C6E0533E24100AE822/DATAFILE/users.305.1056973161

8 rows selected.

SQL> create pluggable database pdbasm2 from pdbasm1 snapshot copy;
create pluggable database pdbasm2 from pdbasm1 snapshot copy
*
ERROR at line 1:
ORA-65169: error encountered while attempting to copy file
+DATA/ASMSE2/B48865836FE9D1C6E0533E24100AE822/DATAFILE/users.305.1056973161
ORA-17517: Database cloning using storage snapshot failed on file
8:+DATA/ASMSE2/B48865836FE9D1C6E0533E24100AE822/DATAFILE/users.305.1056973161

Ok, this is kind of obvious since ASM itself does not support storage snapshots. So I will now set “clonedb=true”, restart the Container Database to activeate the change and try again.

SQL> alter system set clonedb=true scope=spfile;

System altered.

SQL> startup force
ORACLE instance started.

Total System Global Area 4294963760 bytes
Fixed Size                  9142832 bytes
Variable Size             855638016 bytes
Database Buffers         3405774848 bytes
Redo Buffers               24408064 bytes
Database mounted.
Database opened.
SQL> show parameter clonedb

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
clonedb                              boolean     TRUE
clonedb_dir                          string
SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDBASM1                        READ WRITE NO

SQL> create pluggable database pdbasm2 from pdbasm1 snapshot copy;
create pluggable database pdbasm2 from pdbasm1 snapshot copy
*
ERROR at line 1:
ORA-65081: database or pluggable database is not open in read only mode

Again it fails, because the source PDB is still opened read-write. If the filesystem does not support snapshots, I have to open the source PDB in read-only mode and leave it in that mode for the entire lifetime of any PDB snapshot copy that depends on that source PDB. So next try.

SQL> alter pluggable database pdbasm1 close;

Pluggable database altered.

SQL> alter pluggable database pdbasm1 open read only;

Pluggable database altered.

SQL> create pluggable database pdbasm2 from pdbasm1 snapshot copy;
create pluggable database pdbasm2 from pdbasm1 snapshot copy
*
ERROR at line 1:
ORA-65169: error encountered while attempting to copy file
+DATA/ASMSE2/B48865836FE9D1C6E0533E24100AE822/DATAFILE/users.305.1056973161
ORA-17517: Database cloning using storage snapshot failed on file
8:+DATA/ASMSE2/B48865836FE9D1C6E0533E24100AE822/DATAFILE/users.305.1056973161

And it fails again. So failing three times is enough. Obviously there is no way to create a PDB as a snapshot copy when pure ASM storage is used. That means in turn, that it might be a good idea to use ACFS for storing database files. Doing so will enable us to make full use of the new Multitenant architecture.

Setting Env Vars for Oracle Database

Recently I was coping with a system that required special environment variables, TNS_ADMIN in this case. The reason behind that is quite simple. There are some old applications that access the database, so it was necessary to set SQLNET.ALLOWED_LOGON_VERSION_SERVER to a lower value. Since the database shared the Oracle Home with other databases, I pointed the database to another location using TNS_ADMIN. With Grid Infrastructure, this is quite simple. (Remark: The examples show version 11.2.0.4, but the issue was initially encountered in 12.2.0.1 and could also be reproduced in 18c. Due to the nature of the issue, I asume it is generic.) To check the variables in use, I used the /proc filesystem. Every running process has an “eviron” property there which holds all the environment variables. Unfortunately the variable/value-pairs are separated by NULs, so I used sed and awk to replace NUL with newlines.

[oracle@vm123 ~]$ ps -ef|grep smon|grep -v grep
oracle   19630     1  0 12:38 ?        00:00:00 ora_smon_orcl112
[oracle@vm123 ~]$ cat /proc/19630/environ | sed 's/\o0/NEWLINE/g' | awk -F 'NEWLINE' '{$1=$1}1' OFS='\n' |grep TNS
[oracle@vm123 ~]$ srvctl setenv database -d orcl112b -T "TNS_ADMIN=/home/oracle/network/admin"
[oracle@vm123 ~]$ srvctl stop database -d orcl112b
[oracle@vm123 ~]$ srvctl start database -d orcl112b
[oracle@vm123 ~]$ ps -ef|grep smon|grep -v grep
oracle   19869     1  0 12:39 ?        00:00:00 ora_smon_orcl112
[oracle@vm123 ~]$ cat /proc/19869/environ | sed 's/\o0/NEWLINE/g' | awk -F 'NEWLINE' '{$1=$1}1' OFS='\n' |grep TNS
TNS_ADMIN=/home/oracle/network/admin

So far, so good, the TNS_ADMIN setting is in effect. But now follows the fun part. There was a need to do a restore/recover operation after which the setting was gone. This is simply because the database was started using RMAN rather than “srvctl”.

[oracle@vm123 ~]$ rman target sys/oracle@vm123:1521/orcl112b

Recovery Manager: Release 11.2.0.4.0 - Production on Wed Sep 2 12:41:02 2020

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

connected to target database: ORCL112 (DBID=3566530886, not open)

RMAN> shutdown immediate

using target database control file instead of recovery catalog
database dismounted
Oracle instance shut down

RMAN> startup

connected to target database (not started)
Oracle instance started
database mounted
database opened

Total System Global Area    1068937216 bytes

Fixed Size                     2260088 bytes
Variable Size                335545224 bytes
Database Buffers             725614592 bytes
Redo Buffers                   5517312 bytes

RMAN> exit


Recovery Manager complete.
[oracle@vm123 ~]$ ps -ef|grep smon|grep -v grep
oracle   20122     1  0 12:41 ?        00:00:00 ora_smon_orcl112
[oracle@vm123 ~]$ cat /proc/20122/environ | sed 's/\o0/NEWLINE/g' | awk -F 'NEWLINE' '{$1=$1}1' OFS='\n' |grep TNS
TNS_ADMIN=/u01/app/grid/18/network/admin/

See, now the instance has the TNS_ADMIN pointing to the Grid Infrastructure Home. This is because I used a SID_LIST and a connection using TNS. It looked a little different when I used a local connection.

[oracle@vm123 ~]$ rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Wed Sep 2 12:42:43 2020

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

connected to target database: ORCL112 (DBID=3566530886)

RMAN> shutdown immediate

using target database control file instead of recovery catalog
database closed
database dismounted
Oracle instance shut down

RMAN> startup

connected to target database (not started)
Oracle instance started
database mounted
database opened

Total System Global Area    1068937216 bytes

Fixed Size                     2260088 bytes
Variable Size                335545224 bytes
Database Buffers             725614592 bytes
Redo Buffers                   5517312 bytes

RMAN>

Recovery Manager complete.
[oracle@vm123 ~]$ ps -ef|grep smon|grep -v grep
oracle   20382     1  0 12:43 ?        00:00:00 ora_smon_orcl112
[oracle@vm123 ~]$ cat /proc/20382/environ | sed 's/\o0/NEWLINE/g' | awk -F 'NEWLINE' '{$1=$1}1' OFS='\n' |grep TNS
[oracle@vm123 ~]$ 

With a local connection, the TNS_ADMIN variable was totally gone. Again, this is due to the fact, that the instance inherited its settings from my shell.

This all makes sense somehow. The instance just inherits its environment variables from the process that forks the instance. When using “srvctl” it is the oraagent of the Grid Infrastructure which applies the settings from “srvctl setenv”. When using RMAN via a TNS connection, it inherits the settings from the listener which forks the process. And if using a local connection, its the shell that provides the values. The same is true when using SQL*Plus:

[oracle@vm123 ~]$ ps -ef|grep smon|grep -v grep
oracle   21905     1  0 13:00 ?        00:00:00 ora_smon_orcl112
[oracle@vm123 ~]$
[oracle@vm123 ~]$ cat /proc/21905/environ | sed 's/\o0/NEWLINE/g' | awk -F 'NEWLINE' '{$1=$1}1' OFS='\n' |grep TNS
TNS_ADMIN=/home/oracle/network/admin


[oracle@vm123 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Wed Sep 2 12:58:54 2020

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


Connected to:
Oracle Database 11g Release 11.2.0.4.0 - 64bit Production

SQL> shutdown immediate
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 1068937216 bytes
Fixed Size                  2260088 bytes
Variable Size             335545224 bytes
Database Buffers          725614592 bytes
Redo Buffers                5517312 bytes
Database mounted.
Database opened.
SQL> Disconnected from Oracle Database 11g Release 11.2.0.4.0 - 64bit Production
[oracle@vm123 ~]$ ps -ef|grep smon|grep -v grep
oracle   21695     1  0 12:59 ?        00:00:00 ora_smon_orcl112
[oracle@vm123 ~]$ cat /proc/21695/environ | sed 's/\o0/NEWLINE/g' | awk -F 'NEWLINE' '{$1=$1}1' OFS='\n' |grep TNS
[oracle@vm123 ~]$ 

At the end, this all is totally clear to me. But I still have no clue how to consistently set the necessary environment variables for my instance. Any ideas are highly appreciated.

Update 2020-09-15

Just an update on how I set my environment for the database in a consistent way. As explained, the database processes inherit the environment from the process that initially forks the instance processes. There are three possible parents:

  1. The interactive shell
  2. The Listener
  3. The Grid Infrastructure

For the Grid Infrastrucutre I use “srvctl setenv database” as explained. Setting the shell environment is also easy using “export TNS_ADMIN=blabla” for instance. And the Listener just gets an update through listener.ora with the environment variables, that I require.

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = orcl112b)
      (ORACLE_HOME = /u01/app/oracle/product/db/11.2.0.4)
      (SID_NAME = orcl112)
      (ENVS = "TNS_ADMIN=/home/oracle/network/admin")
    )
  )