Search

Top 60 Oracle Blogs

Recent comments

Oakies Blog Aggregator

World Backup Day- Backing up an Oracle Database using RMAN to Azure Blob Storage

A DBA is only as good as their last backup…or more so, their last recovery.

To celebrate #WorldBackupDay on Twitter, I’m blogging on how to backup an Oracle database directly to Azure Blob storage.  Yes, you could backup on a managed disk directly connected to the VM, then copy it off, but Azure Blob Storage is inexpensive and provides considerable speed and opportunity to create an NSF mount to use the backups with other Oracle hosts for cloning, recovery, etc.

Configure Blob Storage in the Azure Portal

In this example, we have an Oracle database running on an Azure IaaS VM and need to backup the database with RMAN.  No RMAN repository catalog will be used, but yes, you can use one-  no problem.

If you don’t already have a database and want to play along, you can perform the following blog post to create one.

Create blob storage that you want to use with the database, you can do this in the portal, go to Storage Accounts and click on +Add:

https://dbakevlar.com/wp-content/uploads/2020/03/Blob_storage1-300x298.jpg 300w, https://dbakevlar.com/wp-content/uploads/2020/03/Blob_storage1-150x150.jpg 150w, https://dbakevlar.com/wp-content/uploads/2020/03/Blob_storage1-768x763.jpg 768w, https://dbakevlar.com/wp-content/uploads/2020/03/Blob_storage1.jpg 1206w" sizes="(max-width: 800px) 100vw, 800px" />

Click on Next and choose Public Endpoints and choose the defaults for the rest of the tabs and create.

Once created, go into the storage account and on the left blade, choose containers.  Add a container, choosing a name that is descriptive and choose blob and anonymous read access.  You’ll require this name later for your blobfuse creation.

https://dbakevlar.com/wp-content/uploads/2020/03/blob_storage2-300x34.jpg 300w, https://dbakevlar.com/wp-content/uploads/2020/03/blob_storage2-768x87.jpg 768w, https://dbakevlar.com/wp-content/uploads/2020/03/blob_storage2-1536x174.jpg 1536w, https://dbakevlar.com/wp-content/uploads/2020/03/blob_storage2-2048x232.jpg 2048w" sizes="(max-width: 800px) 100vw, 800px" />

Install BlobFuse on the VM Host

Once you have your Oracle VM and your storage set up, you need to get a clone for the Blob Fuse install.  Follow these instructions to complete this step from the VM Host as Root.

For our new VM, Oracle Linux requires the same installation as RHEL:

sudo rpm -Uvh https://packages.microsoft.com/config/rhel/7/packages-microsoft-prod.rpm

Run the Install:

sudo yum install blobfuse

Create the directories and ensure the ownership is correct on the mount

sudo mkdir /mnt/blobfuse -p

mkdir /mnt/blobfuse/orabcntr1
sudo chown oracle /mnt/blobfuse/

You should now see your new mount point:

Next you need to create a configuration file that will contain the vital information for Blobfuse to log in.  You can find the values in the Azure Portal -> Storage Accounts -> click your storage account -> Access keys. The container name is the name of the virtual folder that you’re mounting. This container should already have been created either on in the Azure Portal or the Azure Storage Explorer):

touch ~/connection.cfg

chmod 600 ~/connection.cfg
vi ~/connection.cfg

The file will contain three lines in it and look similar to the following:

accountName orabkup2
accountKey 5RD7lzbNJOABSC3/V6E3hsU1aXSRlksjdf8762lks7lksh39JKSINE-
containerName orabcntr1
Save the file.

Mount the storage to be used:

blobfuse mnt/blobfuse/orabcntr1 --tmp-path=/mnt/ramdisk/blobfusetmp --config-file=/fuse_connection.cfg -o#333333; text-transform: none; text-indent: 0px; letter-spacing: normal; font-size: 16px; font-style: normal; font-variant: normal; font-weight: 400; text-decoration: none; word-spacing: 0px; display: inline !important; white-space: normal; cursor: text; orphans: 2; float: none; -webkit-text-stroke-width: 0px; background-color: #ffffff;"> attr_timeout=240 -o entry_timeout=240 -o negative_timeout=120 --config-file=../connection.cfg --log-level=LOG_DEBUG --file-cache-timeout-in-seconds=120

You can now view the mount point on the host:

df -h
blobfuse         30G   17G   11G  61% /mnt/blobfuse/orabcntr1

Test that you can write to the blob storage as Oracle:

su - oracle
cd /mnt/blobfuse/orabcntr1
mkdir test
echo "hello world" > test/blob.txt

RMAN Backup with Blob Storage

First step is to install Blob Fuse on the Linux VM to be used in conjunction with the blog storage by RMAN:

export ORACLE_HOME=/u01/app/oracle/product/18.0.0/dbhome_1
export ORACLE_SID=cdb1
RMAN
connect target /
#000000; text-transform: none; line-height: 1.615; text-indent: 0px; letter-spacing: normal; font-family: courier new,courier; font-size: 13px; font-style: normal; font-variant: normal; text-decoration: none; word-spacing: 0px; display: inline !important; white-space: pre; orphans: 2; float: none; -webkit-text-stroke-width: 0px; overflow-wrap: normal; background-color: #f9f9fb;">
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/mnt/blobfuse/orabcntr1/snapcf_ev.f';
CONFIGURE CHANNEL 1 DEVICE TYPE DISK CONNECT '*' FORMAT   '/mnt/blobfuse/orabcntr1/DB_NAME/%U';
new RMAN configuration parameters are successfully stored
CONFIGURE CHANNEL 2 DEVICE TYPE DISK CONNECT '*' FORMAT   '/mnt/blobfuse/orabcntr1/DB_NAME/%U';
new RMAN configuration parameters are successfully stored

If you do a the following:

SHOW ALL;

The following will be seen as part of the RMAN configuration:

CONFIGURE CHANNEL 2 DEVICE TYPE DISK CONNECT '*' FORMAT   '/mnt/blobfuse/orabcntr1/DB_NAME/%U';
CONFIGURE CHANNEL 1 DEVICE TYPE DISK CONNECT '*' FORMAT   '/mnt/blobfuse/orabcntr1/DB_NAME/%U';
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/mnt/blobfuse/orabcntr1/snapcf_ev.f';

Now run the backup:

#444444; text-transform: none; text-indent: 0px; letter-spacing: normal; text-decoration: none; word-spacing: 0px; display: inline !important; white-space: normal; orphans: 2; float: none; -webkit-text-stroke-width: 0px; background-color: #ffffff;">run
#444444; text-transform: none; text-indent: 0px; letter-spacing: normal; text-decoration: none; word-spacing: 0px; display: inline !important; white-space: normal; orphans: 2; float: none; -webkit-text-stroke-width: 0px; background-color: #ffffff;"> {
#444444; text-transform: none; text-indent: 0px; letter-spacing: normal; text-decoration: none; word-spacing: 0px; display: inline !important; white-space: normal; orphans: 2; float: none; -webkit-text-stroke-width: 0px; background-color: #ffffff;"> BACKUP AS COMPRESSED BACKUPSET ARCHIVELOG ALL NOT BACKED UP FILESPERSET 10 DELETE ALL INPUT TAG ‘ARCH’;
#444444; text-transform: none; text-indent: 0px; letter-spacing: normal; text-decoration: none; word-spacing: 0px; display: inline !important; white-space: normal; orphans: 2; float: none; -webkit-text-stroke-width: 0px; background-color: #ffffff;"> }

https://dbakevlar.com/wp-content/uploads/2020/03/blob_storage3-300x27.jpg 300w, https://dbakevlar.com/wp-content/uploads/2020/03/blob_storage3-768x68.jpg 768w, https://dbakevlar.com/wp-content/uploads/2020/03/blob_storage3.jpg 1080w" sizes="(max-width: 800px) 100vw, 800px" />

Once the backup has succeeded, you can then schedule backups of both the database + archive logs and regular archive logs to the blob storage in CRON.

 

 

 

 



Tags:  , , ,


Del.icio.us



Facebook

TweetThis

Digg

StumbleUpon




Copyright ©  [World Backup Day- Backing up an Oracle Database using RMAN to Azure Blob Storage], All Right Reserved. 2020.

Create a Simple Oracle VM on Azure IaaS

Use the following shell script to create your Oracle VM.  I chose the following parameters to create mine:

Using Azure Cloud Shell and with persistent storage, which are linked on the github page, I uploaded the mk_oravm.sh script and run it after changing the permissions.

chmod 744 mk_oravm.sh

Provision the VM

Run the script:

 ./mk_oravm.sh

Anwser the questions from the script:

What is the name for the resource group to create the deployment in? Example: ORA_GRP
Enter your Resource Group name:
orabkup_grp

Here's the installation version urns available, including Oracle and Oracle Linux
Urn
-----------------------------------------------------------
Oracle:Oracle-Database-Ee:18.3.0.0:18.3.20181213

Enter the urn you'd like to install, feel free to copy from the list and paste here:
Oracle:Oracle-Database-Ee:18.3.0.0:18.3.20181213

What unique name your Oracle database server? This will be used for disk naming, must be unique.  Example: ora122db1
Enter the DB Server name:
ora183db1

What size deployment would you like, choose from the following: StandardSSD_LRS, Standard_LRS, UltraSSD_LRS ?  Example:
Enter the Size name from above:
StandardSSD_LRS

Choose an Admin user to manage your server.  This is not the ORACLE user for the box, but an ADMIN user
Enter in the admin user name, example: azureuser
azureuser

You must choose a location region to deploy your resources to.  The list as follows:
centralus
eastus
eastus2
westus
northcentralus
southcentralus
westcentralus
westus2
Enter the zone from the list above:
westus2

The script will then deploy your VM in Azure:

{
  "id": "/subscriptions/73aa270e-fffd-411a-b368-b44263f61deb/resourceGroups/orabkup_grp",
  "location": "westus2",

.......

      "provisioningState": "Succeeded",
      "resourceGroup": "orabkup_grp",
      "sourceAddressPrefix": "*",
      "sourceAddressPrefixes": [],
      "sourceApplicationSecurityGroups": null,
      "sourcePortRange": "*",
      "sourcePortRanges": [],
      "type": "Microsoft.Network/networkSecurityGroups/securityRules"
    }
  ],
  "subnets": null,
  "tags": {},
  "type": "Microsoft.Network/networkSecurityGroups"
}
Deployment of Oracle VM ora183db1 in resource group orabkup_grp Complete
Keys generated for authentication
Admin name is azureuser

Setup Environment

#ffffff; color: #333333; cursor: text; font-family: Georgia,'Times New Roman','Bitstream Charter',Times,serif; font-size: 16px; font-style: normal; font-variant: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: none; text-indent: 0px; text-transform: none; -webkit-text-stroke-width: 0px; white-space: normal; word-spacing: 0px;">Now, log into the new VM, set your Oracle home, start the listener and SID

Create your data directory.  For this example, I’m just going to add a second directory in the /u01, but you can add a disk and place it there:

sudo mkdir /u01/data
chown oracle:oinstall /u01/data

Switch over to Oracle and start the listener:

#ffffff; color: #333333; cursor: text; font-family: Georgia,'Times New Roman','Bitstream Charter',Times,serif; font-size: 16px; font-style: normal; font-variant: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: none; text-indent: 0px; text-transform: none; -webkit-text-stroke-width: 0px; white-space: normal; word-spacing: 0px;">sudo su - oracle
export ORACLE_HOME= /u01/app/product/18.3.0.0/dbhome_1
lsnrctl start

Create your Oracle 18c Database:

dbca -silent \
       -createDatabase \
       -templateName General_Purpose.dbc \
       -gdbname cdb1 \
       -sid cdb1 \
       -responseFile NO_VALUE \
       -characterSet AL32UTF8 \
       -sysPassword OraPasswd1 \
       -systemPassword OraPasswd1 \
       -createAsContainerDatabase true \
       -numberOfPDBs 1 \
       -pdbName pdb1 \
       -pdbAdminPassword OraPasswd1 \
       -databaseType MULTIPURPOSE \
       -automaticMemoryManagement false \
       -storageType FS \
       -datafileDestination /u01/data \
       -ignorePreReqs

The output will look like the following:

Prepare for db operation
8% complete
Copying database files
31% complete
Creating and starting Oracle instance
32% complete
...
77% complete
Executing Post Configuration Actions
100% complete
Database creation complete. For details check the logfiles at:
 /u01/app/oracle/cfgtoollogs/dbca/cdb1.
Database Information:
Global Database Name:cdb1
System Identifier(SID):cdb1
Look at the log file "/u01/app/oracle/cfgtoollogs/dbca/cdb1/cdb1.log" for further details.

Once the database creation is complete, then a few steps can be performed to set up the TNSNAMES, etc. or you can proceed to simple log in and work with this new database using a bequeath connection.

 



Tags:  , ,


Del.icio.us



Facebook

TweetThis

Digg

StumbleUpon




Copyright ©  [Create a Simple Oracle VM on Azure IaaS], All Right Reserved. 2020.

Silent installation: Oracle Restart 19c, ASM Filter Driver, RHCK edition

As promised in the earlier post here are my notes about installing Oracle Restart 19c on Oracle Linux 7.7 using the RedHat compatible kernel (RHCK). Please consult the ACFS/ASMFD compatibility matrix, My Oracle Support DocID 1369107.1 for the latest information about ASMFD compatibility with various kernels as well.

Why am I starting the series with a seemingly “odd” kernel, at least from the point of view of Oracle Linux? If you try to install the Oracle Restart base release with UEK 5, you get strange error messages back from gridSetup telling you about invalid ASM disks. While that’s probably true, it’s a secondary error. The main cause of the problem is this:

[root@server5 bin]# ./afddriverstate supported
AFD-620: AFD is not supported on this operating system version: '4.14.35-1902.300.11.el7uek.x86_64'
AFD-9201: Not Supported
AFD-9294: updating file /etc/sysconfig/oracledrivers.conf 

Which is easy to run into since gridSetup.sh doesn’t validate this for you when running in silent mode. The GUI version of the installer protects you from the mistake though. Upgrading to the latest UEK 5 doesn’t change this message, you need to check the certification matrix to learn that Oracle Restart 19.4.0 and later are required for UEK 5 if you’d like to use ASMFD (or ACFS for that matter). This scenario will be covered in a later post.

Using the Red Hat Compatible Kernel alleviates this problem for me. Just be aware of the usual caveats when using the Red Hat Kernel on Oracle Linux such as YUM changing the default kernel during yum upgrade etc. I’d also like to iterate that this post isn’t an endorsement for ASM Filter Driver, but since the documentation was a little unclear I thought I’d write up how I got to a working installation. It is up to you to ensure that ASMFD is a workable solution for your environment by following industry best known practices.

Configuration Options

In the post introducing this series I claimed to have identified 2 options for installing Oracle Restart 19c using ASMFD: the first one is to use UDEV to prepare ASM block devices, the second one is to label the ASM disks using asmcmd afd_label.

Huh, UDEV? That hasn’t really been blogged about at all in the context of ASMFD, or at least I didn’t find anyone who did. I’m inferring the possibility of using UDEV from “Configuring Oracle ASM Filter Driver During Installation” (link to documentation):

“If you do not use udev on the system where the Oracle Grid Infrastructure is installed, then you can also complete the following procedure to provision disks for Oracle ASMFD before the installer is launched”

You actually only have to choose one of them. Let’s start with the more frequently covered approach of labelling disks using asmcmd.

My environment

I have applied all the patches to this environment up to March 26th to my lab enviroment. The Oracle Linux release I’m using is 7.7:

[root@server4 ~]# cat /etc/oracle-release
Oracle Linux Server release 7.7 

The KVM VM I’m using for this blog post uses the latest Red Hat Compatible Kernel at the time of writing (kernel-3.10.0-1062.18.1.el7.x86_64). You will notice that I’m using the virtio driver, leading to “strange” device names. Instead of /dev/sd it’s /dev/vd. My first two block devices are reserved for the O/S and Oracle, the remaining ones are going to be used for ASM. I have an old (bad?) habit of partitioning block devices for ASM as you might notice. Most of the Oracle setup is done by the 19c preinstall RPM, which I used.

I created a grid owner – grid – to own the Oracle Restart installation. Quite a few blog posts I came across referenced group membership, and I’d like to do the same:

[root@server4 ~]# id -a grid 
uid=54322(grid) gid=54321(oinstall) groups=54321(oinstall),54322(dba),54328(asmadmin),54327(asmdba) 

The block devices I’m intending to use for ASM are /dev/vdc to /dev/vdf – the first 2 are intended for +DATA, the other 2 will become part of +RECO. As you can see they are partitioned:

[root@server4 ~]# lsblk --ascii
NAME                  MAJ:MIN RM  SIZE RO TYPE MOUNTPOINT
vdf                   251:80   0   10G  0 disk 
`-vdf1                251:81   0   10G  0 part 
vdd                   251:48   0   10G  0 disk 
`-vdd1                251:49   0   10G  0 part 
vdb                   251:16   0   50G  0 disk 
`-vdb1                251:17   0   50G  0 part 
  `-oraclevg-orabinlv 252:2    0   50G  0 lvm  /u01
sr0                    11:0    1 1024M  0 rom  
vde                   251:64   0   10G  0 disk 
`-vde1                251:65   0   10G  0 part 
vdc                   251:32   0   10G  0 disk 
`-vdc1                251:33   0   10G  0 part 
vda                   251:0    0   12G  0 disk 
|-vda2                251:2    0 11.5G  0 part 
| |-rootvg-swaplv     252:1    0  768M  0 lvm  [SWAP]
| `-rootvg-rootlv     252:0    0 10.8G  0 lvm  /
`-vda1                251:1    0  500M  0 part /boot  

With all that out of the way it is time to cover the installation.

Labeling disks

I’m following the procedure documented in the 19c Administrator’s Guide chapter 20, section “Configuring Oracle ASM Filter Driver During Installation”. I have prepared my environment up to the step where I’d have to launch gridSetup.sh. This is a fairly well known process, and I won’t repeat it here.

Once the 19c install image has been extracted to my future Grid Home, the first step is to check if my system is supported:

[root@server4 ~]# cd /u01/app/grid/product/19.0.0/grid/bin
[root@server4 bin]# ./afddriverstate supported
AFD-9200: Supported 
[root@server4 bin]# uname -r
3.10.0-1062.18.1.el7.x86_64 

“AFD-9200: Supported” tells me that I can start labeling disks. This requires me to be root, and I have to set ORACLE_HOME and ORACLE_BASE. For some reason, the documentation suggests using /tmp as ORACLE_BASE, which I’ll use as well:

[root@server4 bin]# pwd
/u01/app/grid/product/19.0.0/grid/bin
[root@server4 bin]# export ORACLE_BASE=/tmp
[root@server4 bin]# export ORACLE_HOME=/u01/app/grid/product/19.0.0/grid
[root@server4 bin]# ./asmcmd afd_label DATA1 /dev/vdc1 --init
[root@server4 bin]# ./asmcmd afd_label DATA2 /dev/vdd1 --init 

[root@server4 bin]# ./asmcmd afd_lslbl /dev/vdc1
--------------------------------------------------------------------------------
Label                     Duplicate  Path
================================================================================
DATA1                                 /dev/vdc1

[root@server4 bin]# ./asmcmd afd_lslbl /dev/vdd1
--------------------------------------------------------------------------------
Label                     Duplicate  Path
================================================================================
DATA2                                 /dev/vdd1  

Note the use of the –init flag. This is only needed if Grid Infrastructure isn’t installed yet.

Labeling the disks did not have an effect on the block devices’ permissions. Right after finishing the 2 calls to label my 2 block devices, this is the output from my file system:

[root@server4 bin]# ls -l /dev/vd[c-d]*
brw-rw----. 1 root disk 252, 32 Mar 27 09:46 /dev/vdc
brw-rw----. 1 root disk 252, 33 Mar 27 12:55 /dev/vdc1
brw-rw----. 1 root disk 252, 48 Mar 27 09:46 /dev/vdd
brw-rw----. 1 root disk 252, 49 Mar 27 12:58 /dev/vdd1
[root@server4 bin]#  

The output of afd_lslbl indicated that both of my disks are ready to become part of an ASM disk group, so let’s start the installer.

Call gridSetup.sh

I haven’t been able to make sense of the options in the response file until I started the installer in GUI mode and created a response file based on my choices. To cut a long story short, here is my call to gridSetup.sh:

[grid@server4 ~]$ /u01/app/grid/product/19.0.0/grid/gridSetup.sh -silent \
> INVENTORY_LOCATION=/u01/app/oraInventory \
> SELECTED_LANGUAGES=en \
> ORACLE_BASE=/u01/app/grid \
> ORACLE_HOME_NAME=ASMFD_RHCK \
> -waitforcompletion -ignorePrereqFailure -lenientInstallMode \
> oracle.install.option=HA_CONFIG \
> oracle.install.asm.OSDBA=asmdba \
> oracle.install.asm.OSASM=asmadmin \
> oracle.install.asm.diskGroup.name=DATA \
> oracle.install.asm.diskGroup.disks=/dev/vdc1,/dev/vdd1 \
> oracle.install.asm.diskGroup.diskDiscoveryString=/dev/vd* \
> oracle.install.asm.diskGroup.redundancy=EXTERNAL \
> oracle.install.asm.diskGroup.AUSize=4 \
> oracle.install.asm.configureAFD=true \
> oracle.install.crs.rootconfig.executeRootScript=false \
> oracle.install.asm.SYSASMPassword=thinkOfASuperSecretPassword \
> oracle.install.asm.monitorPassword=thinkOfASuperSecretPassword
Launching Oracle Grid Infrastructure Setup Wizard...

The response file for this session can be found at:
 /u01/app/grid/product/19.0.0/grid/install/response/grid_2020-03-27_01-06-14PM.rsp

You can find the log of this install session at:
 /tmp/GridSetupActions2020-03-27_01-06-14PM/gridSetupActions2020-03-27_01-06-14PM.log

As a root user, execute the following script(s):
        1. /u01/app/oraInventory/orainstRoot.sh
        2. /u01/app/grid/product/19.0.0/grid/root.sh

Execute /u01/app/grid/product/19.0.0/grid/root.sh on the following nodes:
[server4]

Successfully Setup Software.
As install user, execute the following command to complete the configuration.
/u01/app/grid/product/19.0.0/grid/gridSetup.sh -executeConfigTools -responseFile /u01/app/grid/product/19.0.0/grid/install/response/grid_2020-03-27_01-06-14PM.rsp [-silent]
Note: The required passwords need to be included in the response file.
Moved the install session logs to:
/u01/app/oraInventory/logs/GridSetupActions2020-03-27_01-06-14PM
[grid@server4 ~]$

It took a little while to work out that despite labeling the disks for ASMFD I didn’t have to put any reference to AFD into the call to gridSetup.sh. Have a look at the ASM disk string and the block devices: that’s what I’d use if I were using UDEV rules for device name persistence. The syntax might appear counter-intuitive. However there’s a “configureAFD” flag you need to set to true.

Since this is a lab environment I’m ok with external redundancy. Make sure you pick a redundancy level appropriate for your use case.

Running the configuration tools

The remaining steps are identical to a non ASMFD setup. First you run orainstRoot.sh followed by root.sh. The output of the latter showed this for me, indicating success:

[root@server4 ~]# /u01/app/grid/product/19.0.0/grid/root.sh
Check /u01/app/grid/product/19.0.0/grid/install/root_server4_2020-03-27_13-11-05-865019723.log for the output of root script

[root@server4 ~]#
[root@server4 ~]# cat /u01/app/grid/product/19.0.0/grid/install/root_server4_2020-03-27_13-11-05-865019723.log
Performing root user operation.

The following environment variables are set as:
    ORACLE_OWNER= grid
    ORACLE_HOME=  /u01/app/grid/product/19.0.0/grid
   Copying dbhome to /usr/local/bin ...
   Copying oraenv to /usr/local/bin ...
   Copying coraenv to /usr/local/bin ...


Creating /etc/oratab file...
Entries will be added to the /etc/oratab file as needed by
Database Configuration Assistant when a database is created
Finished running generic part of root script.
Now product-specific root actions will be performed.
Using configuration parameter file: /u01/app/grid/product/19.0.0/grid/crs/install/crsconfig_params
The log of current session can be found at:
  /u01/app/grid/crsdata/server4/crsconfig/roothas_2020-03-27_01-11-06PM.log
2020/03/27 13:11:13 CLSRSC-363: User ignored prerequisites during installation
LOCAL ADD MODE
Creating OCR keys for user 'grid', privgrp 'oinstall'..
Operation successful.
LOCAL ONLY MODE
Successfully accumulated necessary OCR keys.
Creating OCR keys for user 'root', privgrp 'root'..
Operation successful.
CRS-4664: Node server4 successfully pinned.
2020/03/27 13:13:55 CLSRSC-330: Adding Clusterware entries to file 'oracle-ohasd.service'

server4     2020/03/27 13:16:59     /u01/app/grid/crsdata/server4/olr/backup_20200327_131659.olr     724960844
2020/03/27 13:17:54 CLSRSC-327: Successfully configured Oracle Restart for a standalone server
[root@server4 ~]# 

Well that looks ok, now on to the final step, configuration! As indicated in the output, you need to update the response (/u01/app/grid/product/19.0.0/grid/install/response/grid_2020-03-27_01-06-14PM.rsp) file with the required passwords. For me that was oracle.install.asm.monitorPassword and oracle.install.asm.SYSASMPassword. Once the response file was updated, I called gridSetup.sh once again:

[grid@server4 ~]$ /u01/app/grid/product/19.0.0/grid/gridSetup.sh -executeConfigTools -responseFile /u01/app/grid/product/19.0.0/grid/install/response/grid_2020-03-27_01-06-14PM.rsp -silent
Launching Oracle Grid Infrastructure Setup Wizard...

You can find the logs of this session at:
/u01/app/oraInventory/logs/GridSetupActions2020-03-27_01-20-47PM

You can find the log of this install session at:
 /u01/app/oraInventory/logs/UpdateNodeList2020-03-27_01-20-47PM.log
Successfully Configured Software. 

And that’s it! The software has been configured successfully. Don’t forget to remove the passwords from the response file!

Verification

After a little while I have been able to configure Oracle Restart 19c/ASMFD on Oracle Linux 7.7/RHCK. Let’s check what this implies.

I’ll first look at the status of ASM Filter Driver:

[grid@server4 ~]$ . oraenv
ORACLE_SID = [grid] ? +ASM
The Oracle base has been set to /u01/app/grid
[grid@server4 ~]$ afddriverstate installed
AFD-9203: AFD device driver installed status: 'true'
[grid@server4 ~]$ afddriverstate loaded
AFD-9205: AFD device driver loaded status: 'true'
[grid@server4 ~]$ afddriverstate version
AFD-9325:     Driver OS kernel version = 3.10.0-862.el7.x86_64.
AFD-9326:     Driver build number = 190222.
AFD-9212:     Driver build version = 19.0.0.0.0.
AFD-9547:     Driver available build number = 190222.
AFD-9548:     Driver available build version = 19.0.0.0.0.
[grid@server4 ~]$  

That’s encouraging: ASMFD is loaded and works on top of kernel-3.10 (RHCK)

I am indeed using the base release (and have to patch now!)

[grid@server4 ~]$ $ORACLE_HOME/OPatch/opatch lspatches
29585399;OCW RELEASE UPDATE 19.3.0.0.0 (29585399)
29517247;ACFS RELEASE UPDATE 19.3.0.0.0 (29517247)
29517242;Database Release Update : 19.3.0.0.190416 (29517242)
29401763;TOMCAT RELEASE UPDATE 19.0.0.0.0 (29401763)

OPatch succeeded. 

And … I’m also using ASMFD:

SQL> col name for a20
SQL> col path for a10
SQL> col library for a50
SQL> set lines 120
SQL> select name, path, library from v$asm_disk where group_number <> 0;

NAME                 PATH       LIBRARY
-------------------- ---------- --------------------------------------------------
DATA1                AFD:DATA1  AFD Library - Generic , version 3 (KABI_V3)
DATA2                AFD:DATA2  AFD Library - Generic , version 3 (KABI_V3)

SQL> show parameter asm_diskstring

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
asm_diskstring                       string      /dev/vd*, AFD:*
SQL>  

This concludes the setup of my lab environment.

Oracles Free TNS Firewall - VALIDNODE_CHECKING

I said in a post a couple of days ago that my overall plan to secure an Oracle database; actually my plan is to secure the data in an Oracle database not blindly just secure Oracle. We must focus on....[Read More]

Posted by Pete On 31/03/20 At 12:26 PM

Where to add Index on Postgres

 

Summary

Goal in this post is to layout a methodology that can be implemented to scan the explain plans and identify opportunities to optimize the sql execution with additional indexes.

The missing index opportunities outlined here are

  1. Indexes that would avoid full table scan with a predicate filter that filters out most of the returned rows
  2. Joins that are missing index on the join field
  3. Index range scans that could be made more efficient by addition of a field to existing index

Also for future consideration, but not yet outline here

  • Recommending covering index where the addition of fields to an index avoids table access all together
  • Indexes that support the order by clause

Basic procedure is to take the output from “explain” command.
In the explain output only look at nodes that are at least say 10-30% or more of total cost of the whole query.
Look for full table scans , these are nodes with the following entry : “Node Type”: “Seq Scan”
If it’s in the second node of a nested loop, suggest an index on the join field
If it’s is the most expensive node of a Hash join suggest an index on the join field
If it’s the most expense node of Merge Join suggest an index on the join field.
If is followed by a filter, suggest an index on the filter and join column.

If if the filter is not the most expensive node in a HJ or it’s the first node in a NL, and that node is expensive and the filter is expected to return X% ( for example 10% ) or less of the table after applying the filter, then suggest an index on that filter column.
Additionally Index range scans that could be improved by adding fields to the index will require using “explain analyze”. With explain analyze we can see how many rows were filtered out after the index access.

Get plan with “Explain” and text from SQL statement

EXPLAIN (COSTS true, FORMAT json, verbose true)
select id from hypg1 where id = 2006;
                QUERY PLAN                 
-------------------------------------------
 [                                        +
   {                                      +
     "Plan": {                            +
       "Node Type": "Gather",             +
       "Parallel Aware": false,           +
       "Startup Cost": 1000.00,           +
       "Total Cost": 10633.43,            +
       "Plan Rows": 1,                    +
       "Plan Width": 4,                   +
       "Output": ["id"],                  +
       "Workers Planned": 2,              +
       "Single Copy": false,              +
       "Plans": [                         +
         {                                +
           "Node Type": "Seq Scan",       +
           "Parent Relationship": "Outer",+
           "Parallel Aware": true,        +
           "Relation Name": "hypg1",      +
           "Schema": "public",            +
           "Alias": "hypg1",              +
           "Startup Cost": 0.00,          +
           "Total Cost": 9633.33,         +
           "Plan Rows": 1,                +
           "Plan Width": 4,               +
           "Output": ["id"],              +
           "Filter": "(hypg1.id = 2006)"  +
         }                                +
       ]                                  +
     }                                    +
   }                                      +
 ]

 

Cost in Explain Plan

In all cases we should sort nodes by cost and only look at nodes that take up a certain percentage or more of the cost.
Each node has a “cost”. We need to extract the “total cost” at each node in the plan and compare that to the “total cost” of the whole query (top node) and calculate % of the “total cost” at a node compared whole query and only look at nodes that represent some value say 10% or more of total cost. Costs include the sum of all indented nodes, so to get the per node time or cost we have subtract out the child nodes.

If we can run “explain analyze” we can get the actual time per node. The command “explain analyze” actually runs the query . I would say we should plan on “explain analyze” with certain constraints. If the query is already being run multiple times a minute, then running it once more for tuning information is probably worth it. Time is just like cost where time includes times of child nodes, so we have subtract out the child nodes to see the time elapsed at every node.

Here is an example of the costs at nodes and showing the child nodes are subcomponents of the parent nodes cost

 

[                                                               +
   {                                                             +
     "Plan": {                                                   +
       "Total Cost": 22308.54,                                   +
       "Plans": [                                                +
         {                                                       +
           "Total Cost": 11675.10,                               +
             {                                                   +
               "Total Cost": 10675.00,                           +
             }                                                   +
         },                                                      +
         {                                                       +
           "Total Cost": 10633.43,                               +
             {                                                   +
               "Total Cost": 9633.33,                            +
             }                                                   +
           ]                                                     +
         }                                                       +
       ]                                                         +
     }                                                           +
   }                                                             +
 ]

for example, subtracting out the child node costs to get the per node costs

[                                                               +
   {                                                             +
     "Plan": {                                                   +
       "Total Cost": 22308.54,  --  node cost 22308.54 - (11675.10+10633.43) = 0.01
       "Plans": [                                                +
         {                                                       +
           "Total Cost": 11675.10, --  node cost 11675.10 - 10675.00 = 1000.1
             {                                                   +
               "Total Cost": 10675.00,                           +
             }                                                   +
         },                                                      +
         {                                                       +
           "Total Cost": 10633.43, --  node cost   10633.43 - 9633.33 = 1000.1
             {                                                   +
               "Total Cost": 9633.33,                            +
             }                                                   +
           ]                                                     +
         }                                                       +
       ]                                                         +
     }                                                           +
   }                                                             +
 ]

Predicate filters missing indexes

For predicate filters that can use indexes scan the explain plan for the following string

           "Node Type": "Seq Scan",       +

and see if they are followed by a “Filter”.

           "Total Cost": 9633.33,         +
           "Plan Rows": 1,                +
           "Filter": "(hypg1.id = 2006)"  +

Verify that the node is a significant cost of overall cost of query. This node takes up most to the cost of the plan 9633.33 out of 10633.43 so it is the most important node to optimize.
We should only look at nodes that take up a certain percentage of total cost, say 10% or more. ( value should be configurable in our code, as it is debatable where the cut off point is)

We can compare rows returned (“Plan Rows”) to total rows in the table statistics
If the filter eliminates a large amount of rows, then it is a candidate.
The following query had to be run in the database that the table is in, thus have to collect database as part of the sampling of the load data.

SELECT reltuples FROM pg_class WHERE relname = 'hypg1';

reltuples
-----------
 1e+06

% filtered = 100 * ( 1e+06 – 1 ) / 1e+06 = 99.99% rows are filtered out, so it’s the perfect candidate.

We should only recommend index the filter returns 10% or less of the table (i.e. 90% of rows returned from full table scanned are filtered out) and in the advisor recommendation for the index we should indicate much of the table is returned via the filter. We can give some recommendations based on the % for example
low recommendation 5%-10% of table returned after apply filter
medium recommendation 1%-5% of table returned after apply filter
high recommendation < 1% of table returned after apply filter

The filter column is hypg1.id thus we suggest an index on hypg1.id

Joins missing indexes

Using the following query in the examples below

explain (FORMAT json)
select max (t1.data)
from t1, t2
where t1.id = t2.id
and t1.clus = 10
and t2.val > 1001
;

HASH JOIN

Look for nodes of type “Hash Cond”: ” that are followed by a “Seq Scan”
If we find a “Seq Scan” is preceded by a Hash JOIN and cost is a significant amount of total query, then suggest an index on the join column
If both nodes of the hash join have Seq Scans , suggest the index join column from the more expensive node.
If that node also contains a filter, suggest including the filter in the index.

There is “Node Type”: “Seq Scan”
table “Relation Name”: “t2″,
cost “Total Cost”: 13512.67″
looking the hash join above the Seq Scan node, the join condition is

"Hash Cond": "(t2.id = t1.id)",  

suggest index on t2.id

              "Plans": [                                                 +
                 {                                                        +
                   "Node Type": "Hash Join",                              +
                   "Parent Relationship": "Outer",                        +
                   "Parallel Aware": false,                               +
                   "Join Type": "Inner",                                  +
                   "Startup Cost": 11.40,                                 +
                   "Total Cost": 15086.97,                                +
                   "Plan Rows": 41,                                       +
                   "Plan Width": 33,                                      +
                   "Output": ["t1.data"],                                 +
                   "Inner Unique": false,                                 +
                   "Hash Cond": "(t2.id = t1.id)",                        +
                   "Plans": [                                             +
                     {                                                    +
                       "Node Type": "Seq Scan",                           +
                       "Parent Relationship": "Outer",                    +
                       "Parallel Aware": true,                            +
                       "Relation Name": "t2",                             +
                       "Schema": "public",                                +
                       "Alias": "t2",                                     +
                       "Startup Cost": 0.00,                              +
                       "Total Cost": 13512.67,                            +
                       "Plan Rows": 416667,                               +
                       "Plan Width": 4,                                   +
                       "Output": ["t2.id", "t2.clus", "t2.val", "t2.data"]+
                     },

Full explain

total cost of query is 41534.23
HJ node cost is 40532.27 on condition “Hash Cond”: “(t2.id = t1.id)”,
Most expensive node is second node of hash join, “Total Cost”: 24346.00
Second node , the expensive node, is doing full scan on “Relation Name”: “t1″
Thus suggest an index on t1.id, the join condition of the hash join.
The T1 node also contains a filter that returns “Plan Rows”: 5000,” i.e. 5000 rows of 1M in the table ( we look up total rows in pg_class) . The filter is on “Filter”: “((clus) = 10)” so we suggest an index on the combine id & clus

recommend index on t1 ( id, clus)

                                                         +
 [                                                        +
   {                                                      +
     "Plan": {                                            +
       "Node Type": "Aggregate",                          +
       "Strategy": "Plain",                               +
       "Partial Mode": "Finalize",                        +
       "Parallel Aware": false,                           +
       "Startup Cost": 41534.23,                          +
       "Total Cost": 41534.24,                            +
       "Plan Rows": 1,                                    +
       "Plan Width": 32,                                  +
       "Plans": [                                         +
         {                                                +
           "Node Type": "Gather",                         +
           "Parent Relationship": "Outer",                +
           "Parallel Aware": false,                       +
           "Startup Cost": 41534.01,                      +
           "Total Cost": 41534.22,                        +
           "Plan Rows": 2,                                +
           "Plan Width": 32,                              +
           "Workers Planned": 2,                          +
           "Single Copy": false,                          +
           "Plans": [                                     +
             {                                            +
               "Node Type": "Aggregate",                  +
               "Strategy": "Plain",                       +
               "Partial Mode": "Partial",                 +
               "Parent Relationship": "Outer",            +
               "Parallel Aware": false,                   +
               "Startup Cost": 40534.01,                  +
               "Total Cost": 40534.02,                    +
               "Plan Rows": 1,                            +
               "Plan Width": 32,                          +
               "Plans": [                                 +
                 {                                        +
                   "Node Type": "Hash Join",              +
                   "Parent Relationship": "Outer",        +
                   "Parallel Aware": false,               +
                   "Join Type": "Inner",                  +
                   "Startup Cost": 24408.50,              +
                   "Total Cost": 40532.27,                +
                   "Plan Rows": 695,                      +
                   "Plan Width": 33,                      +
                   "Inner Unique": false,                 +
                   "Hash Cond": "(t2.id = t1.id)",        +
                   "Plans": [                             +
                     {                                    +
                       "Node Type": "Seq Scan",           +
                       "Parent Relationship": "Outer",    +
                       "Parallel Aware": true,            +
                       "Relation Name": "t2",             +
                       "Alias": "t2",                     +
                       "Startup Cost": 0.00,              +
                       "Total Cost": 15596.00,            +
                       "Plan Rows": 138889,               +
                       "Plan Width": 4,                   +
                       "Filter": "((val + 0) > 1001)"     +
                     },                                   +
                     {                                    +
                       "Node Type": "Hash",               +
                       "Parent Relationship": "Inner",    +
                       "Parallel Aware": false,           +
                       "Startup Cost": 24346.00,          +
                       "Total Cost": 24346.00,            +
                       "Plan Rows": 5000,                 +
                       "Plan Width": 37,                  +
                       "Plans": [                         +
                         {                                +
                           "Node Type": "Seq Scan",       +
                           "Parent Relationship": "Outer",+
                           "Parallel Aware": false,       +
                           "Relation Name": "t1",         +
                           "Alias": "t1",                 +
                           "Startup Cost": 0.00,          +
                           "Total Cost": 24346.00,        +
                           "Plan Rows": 5000,             +
                           "Plan Width": 37,              +
                           "Filter": "((clus) = 10)"      +
                         }                                +
                       ]                                  +
                     }                                    +
                   ]                                      +
                 }                                        +
               ]                                          +
             }                                            +
           ]                                              +
         }                                                +
       ]                                                  +
     }                                                    +
   }                                                      +                                                   +

Nested Loops

Similar we find a Nest Loops join where the second node has a Seq Scan, look to see if an index can be added to support the join. Only look at Nest Loop nodes that are a significant % of total query cost.

Most of the cost of the query is the next loops:

          "Node Type": "Nested Loop",        +
  ...
          "Total Cost": 1320240.33,          + 

The first node in the NL select rows that are looked up in the second node.
The second node is doing a full table scan:

                     "Node Type": "Seq Scan",       +
                       "Relation Name": "t2",         +
                       "Plan Rows": 1000000,          +

We want to avoid Full table scans on the second node of NL.
Every row in the first node will do a full table scan on the second node.
The join is on

                   "Join Filter": "(t1.id = t2.id)",  +

So we suggest an index on t2.id

[                                                    +
   {                                                  +
     "Plan": {                                        +
       "Node Type": "Aggregate",                      +
       "Strategy": "Plain",                           +
       "Partial Mode": "Finalize",                    +
       "Parallel Aware": false,                       +
       "Startup Cost": 1321240.65,                    +
       "Total Cost": 1321240.66,                      +
       "Plan Rows": 1,                                +
       "Plan Width": 32,                              +
       "Plans": [                                     +
         {                                            +
           "Node Type": "Gather",                     +
           "Parent Relationship": "Outer",            +
           "Parallel Aware": false,                   +
           "Startup Cost": 1321240.44,                +
           "Total Cost": 1321240.65,                  +
           "Plan Rows": 2,                            +
           "Plan Width": 32,                          +
           "Workers Planned": 2,                      +
           "Single Copy": false,                      +
           "Plans": [                                 +
             {                                        +
               "Node Type": "Aggregate",              +
               "Strategy": "Plain",                   +
               "Partial Mode": "Partial",             +
               "Parent Relationship": "Outer",        +
               "Parallel Aware": false,               +
               "Startup Cost": 1320240.44,            +
               "Total Cost": 1320240.45,              +
               "Plan Rows": 1,                        +
               "Plan Width": 32,                      +
               "Plans": [                             +
                 {                                    +
                   "Node Type": "Nested Loop",        +
                   "Parent Relationship": "Outer",    +
                   "Parallel Aware": false,           +
                   "Join Type": "Inner",              +
                   "Startup Cost": 0.00,              +
                   "Total Cost": 1320240.33,          +
                   "Plan Rows": 41,                   +
                   "Plan Width": 33,                  +
                   "Inner Unique": false,             +
                   "Join Filter": "(t1.id = t2.id)",  +
                   "Plans": [                         +
                     {                                +
                       "Node Type": "Seq Scan",       +
                       "Parent Relationship": "Outer",+
                       "Parallel Aware": true,        +
                       "Relation Name": "t1",         +
                       "Alias": "t1",                 +
                       "Startup Cost": 0.00,          +
                       "Total Cost": 14554.33,        +
                       "Plan Rows": 41,               +
                       "Plan Width": 37,              +
                       "Filter": "(clus = 10)"        +
                     },                               +
                     {                                +
                       "Node Type": "Seq Scan",       +
                       "Parent Relationship": "Inner",+
                       "Parallel Aware": false,       +
                       "Relation Name": "t2",         +
                       "Alias": "t2",                 +
                       "Startup Cost": 0.00,          +
                       "Total Cost": 19346.00,        +
                       "Plan Rows": 1000000,          +
                       "Plan Width": 4                +
                     }                                +
                   ]                                  +
                 }                                    +
               ]                                      +
             }                                        +
           ]                                          +
         }                                            +
       ]                                              +
     }                                                +
   }                                                  +

 

Merge Join

similar for a merge join

-- to create a merge join example 
 set enable_nestloop to 'off';
 SET enable_hashjoin TO off;

 

explain (FORMAT json)
select max (t1.data)
from t1, t2
where t1.id = t2.id
and t1.clus = 10
and t2.val  > 1001
;

We want to turn the merge join into a NL or HJ.
In order to do so efficiently we need an index on the join on one table.
We want to have the index on the table with the more rows to analyze.

t1 returns 5000 rows

                           "Relation Name": "t1",         +
                           "Plan Rows": 5000,             +

t2 returns 138889

                          "Relation Name": "t2",         +
                           "Plan Rows": 138889,           +

and join on T2 is on

                   "Merge Cond": "(t2.id = t1.id)",       +

thus suggest index on t2.id

  [                                                        +
   {                                                      +
     "Plan": {                                            +
       "Node Type": "Aggregate",                          +
       "Strategy": "Plain",                               +
       "Partial Mode": "Finalize",                        +
       "Parallel Aware": false,                           +
       "Startup Cost": 55741.63,                          +
       "Total Cost": 55741.64,                            +
       "Plan Rows": 1,                                    +
       "Plan Width": 32,                                  +
       "Plans": [                                         +
         {                                                +
           "Node Type": "Gather",                         +
           "Parent Relationship": "Outer",                +
           "Parallel Aware": false,                       +
           "Startup Cost": 55741.42,                      +
           "Total Cost": 55741.63,                        +
           "Plan Rows": 2,                                +
           "Plan Width": 32,                              +
           "Workers Planned": 2,                          +
           "Single Copy": false,                          +
           "Plans": [                                     +
             {                                            +
               "Node Type": "Aggregate",                  +
               "Strategy": "Plain",                       +
               "Partial Mode": "Partial",                 +
               "Parent Relationship": "Outer",            +
               "Parallel Aware": false,                   +
               "Startup Cost": 54741.42,                  +
               "Total Cost": 54741.43,                    +
               "Plan Rows": 1,                            +
               "Plan Width": 32,                          +
               "Plans": [                                 +
                 {                                        +
                   "Node Type": "Merge Join",             +
                   "Parent Relationship": "Outer",        +
                   "Parallel Aware": false,               +
                   "Join Type": "Inner",                  +
                   "Startup Cost": 54013.29,              +
                   "Total Cost": 54739.68,                +
                   "Plan Rows": 695,                      +
                   "Plan Width": 33,                      +
                   "Inner Unique": false,                 +
                   "Merge Cond": "(t2.id = t1.id)",       +
                   "Plans": [                             +
                     {                                    +
                       "Node Type": "Sort",               +
                       "Parent Relationship": "Outer",    +
                       "Parallel Aware": false,           +
                       "Startup Cost": 29360.10,          +
                       "Total Cost": 29707.32,            +
                       "Plan Rows": 138889,               +
                       "Plan Width": 4,                   +
                       "Sort Key": ["t2.id"],             +
                       "Plans": [                         +
                         {                                +
                           "Node Type": "Seq Scan",       +
                           "Parent Relationship": "Outer",+
                           "Parallel Aware": true,        +
                           "Relation Name": "t2",         +
                           "Alias": "t2",                 +
                           "Startup Cost": 0.00,          +
                           "Total Cost": 15596.00,        +
                           "Plan Rows": 138889,           +
                           "Plan Width": 4,               +
                           "Filter": "((val ) > 1001)" +
                         }                                +
                       ]                                  +
                     },                                   +
                     {                                    +
                       "Node Type": "Sort",               +
                       "Parent Relationship": "Inner",    +
                       "Parallel Aware": false,           +
                       "Startup Cost": 24653.19,          +
                       "Total Cost": 24665.69,            +
                       "Plan Rows": 5000,                 +
                       "Plan Width": 37,                  +
                       "Sort Key": ["t1.id"],             +
                       "Plans": [                         +
                         {                                +
                           "Node Type": "Seq Scan",       +
                           "Parent Relationship": "Outer",+
                           "Parallel Aware": false,       +
                           "Relation Name": "t1",         +
                           "Alias": "t1",                 +
                           "Startup Cost": 0.00,          +
                           "Total Cost": 24346.00,        +
                           "Plan Rows": 5000,             +
                           "Plan Width": 37,              +
                           "Filter": "((clus + 0) = 10)"  +
                         }                                +
                       ]                                  +
                     }                                    +
                   ]                                      +
                 }                                        +
               ]                                          +
             }                                            +
           ]                                              +
         }                                                +
       ]                                                  +
     }                                                    +
   }                                                      +

 

3. Mixed cases – filter and join indexing opportunities

If a filter is missing an index causing a full scan (i.e. “Seq Scan”) and the table being scanned is also used in a join (hash join, merge join, nested loops join), suggest an index on both the filter column and
In the following there is a Seq Scan on T2 followed by a filter.
The filter returns an estimated 375K rows or about 1/3 of the table , i.e. not a good candidate for an index
The Seq Scan is part of a HASH JOIN that represents most of the cost of the query. The hash join is on t2.id.

cost total query 16973.03
t2 access for the hash join cost: 14554.33
t2 has 1M rows ( have to collect this from table stats)
access is on t2.id , “Hash Cond”: “(t2.id = t1.id)”,

suggest index on t2 id and val to avoid the full table scan.

 [                                                        +
   {                                                      +
     "Plan": {                                            +
       "Node Type": "Aggregate",                          +
       "Strategy": "Plain",                               +
       "Partial Mode": "Finalize",                        +
       "Parallel Aware": false,                           +
       "Startup Cost": 16973.02,                          +
       "Total Cost": 16973.03,                            +
       "Plan Rows": 1,                                    +
       "Plan Width": 32,                                  +
       "Plans": [                                         +
         {                                                +
           "Node Type": "Gather",                         +
           "Parent Relationship": "Outer",                +
           "Parallel Aware": false,                       +
           "Startup Cost": 16972.81,                      +
           "Total Cost": 16973.02,                        +
           "Plan Rows": 2,                                +
           "Plan Width": 32,                              +
           "Workers Planned": 2,                          +
           "Single Copy": false,                          +
           "Plans": [                                     +
             {                                            +
               "Node Type": "Aggregate",                  +
               "Strategy": "Plain",                       +
               "Partial Mode": "Partial",                 +
               "Parent Relationship": "Outer",            +
               "Parallel Aware": false,                   +
               "Startup Cost": 15972.81,                  +
               "Total Cost": 15972.82,                    +
               "Plan Rows": 1,                            +
               "Plan Width": 32,                          +
               "Plans": [                                 +
                 {                                        +
                   "Node Type": "Hash Join",              +
                   "Parent Relationship": "Outer",        +
                   "Parallel Aware": false,               +
                   "Join Type": "Inner",                  +
                   "Startup Cost": 11.40,                 +
                   "Total Cost": 15972.72,                +
                   "Plan Rows": 37,                       +
                   "Plan Width": 33,                      +
                   "Inner Unique": false,                 +
                   "Hash Cond": "(t2.id = t1.id)",        +
                   "Plans": [                             +
                     {                                    +
                       "Node Type": "Seq Scan",           +
                       "Parent Relationship": "Outer",    +
                       "Parallel Aware": true,            +
                       "Relation Name": "t2",             +
                       "Alias": "t2",                     +
                       "Startup Cost": 0.00,              +
                       "Total Cost": 14554.33,            +
                       "Plan Rows": 375098,               +
                       "Plan Width": 4,                   +
                       "Filter": "(val > 1001)"           +
                     },                                   +
                     {                                    +
                       "Node Type": "Hash",               +
                       "Parent Relationship": "Inner",    +
                       "Parallel Aware": false,           +
                       "Startup Cost": 10.16,             +
                       "Total Cost": 10.16,               +
                       "Plan Rows": 99,                   +
                       "Plan Width": 37,                  +
                       "Plans": [                         +
                         {                                +
                           "Node Type": "Index Scan",     +
                           "Parent Relationship": "Outer",+
                           "Parallel Aware": false,       +
                           "Scan Direction": "Forward",   +
                           "Index Name": "t1_clus",       +
                           "Relation Name": "t1",         +
                           "Alias": "t1",                 +
                           "Startup Cost": 0.42,          +
                           "Total Cost": 10.16,           +
                           "Plan Rows": 99,               +
                           "Plan Width": 37,              +
                           "Index Cond": "(clus = 10)"    +
                         }                                +
                       ]                                  +
                     }                                    +
                   ]                                      +
                 }                                        +
               ]                                          +
             }                                            +
           ]                                              +
         }                                                +
       ]                                                  +
     }                                                    +
   }                                                      +

4. Index Range scans

To see if an index needs more columns to be efficient looks like ti will require “explain analyze” and not just “explain”

select count(*)
from t1
where t1.clus >= 10 and t1.clus < 15
and t1.val = 1001
;

There is an index on t1.clus and the explain shows it is used
but we don’t know how many rows were retrieved by the index vs filtered out by the Filter

[                                                        +
   {                                                      +
     "Plan": {                                            +
       "Node Type": "Aggregate",                          +
       "Strategy": "Plain",                               +
       "Partial Mode": "Simple",                          +
       "Parallel Aware": false,                           +
       "Startup Cost": 27.14,                             +
       "Total Cost": 27.15,                               +
       "Plan Rows": 1,                                    +
       "Plan Width": 8,                                   +
       "Plans": [                                         +
         {                                                +
           "Node Type": "Index Scan",                     +
           "Parent Relationship": "Outer",                +
           "Parallel Aware": false,                       +
           "Scan Direction": "Forward",                   +
           "Index Name": "t1_clus",                       +
           "Relation Name": "t1",                         +
           "Alias": "t1",                                 +
           "Startup Cost": 0.42,                          +
           "Total Cost": 27.13,                           +
           "Plan Rows": 1,                                +
           "Plan Width": 0,                               +
           "Index Cond": "((clus >= 10) AND (clus < 15))",+
           "Filter": "(val = 1001)"                       +
         }                                                +
       ]                                                  +
     }                                                    +
   }                                                      +

but it doesn’t show how many rows are retrieved by the index and how many are post processed filtered out by the Filter.
Using explain analyze does.
In the following output from “explain analyze”
Only 1 run is returned after index access and filter
but filter takes out 499 rows

"Filter": "(val = 1001)",                      +
           "Rows Removed by Filter": 499                  +

Thus suggest to add val to the existing index

           "Index Name": "t1_clus",                       +

 

[                                                        +
   {                                                      +
     "Plan": {                                            +
       "Node Type": "Aggregate",                          +
       "Strategy": "Plain",                               +
       "Partial Mode": "Simple",                          +
       "Parallel Aware": false,                           +
       "Startup Cost": 27.14,                             +
       "Total Cost": 27.15,                               +
       "Plan Rows": 1,                                    +
       "Plan Width": 8,                                   +
       "Actual Startup Time": 0.279,                      +
       "Actual Total Time": 0.279,                        +
       "Actual Rows": 1,                                  +
       "Actual Loops": 1,                                 +
       "Plans": [                                         +
         {                                                +
           "Node Type": "Index Scan",                     +
           "Parent Relationship": "Outer",                +
           "Parallel Aware": false,                       +
           "Scan Direction": "Forward",                   +
           "Index Name": "t1_clus",                       +
           "Relation Name": "t1",                         +
           "Alias": "t1",                                 +
           "Startup Cost": 0.42,                          +
           "Total Cost": 27.13,                           +
           "Plan Rows": 1,                                +
           "Plan Width": 0,                               +
           "Actual Startup Time": 0.053,                  +
           "Actual Total Time": 0.273,                    +
           "Actual Rows": 1,                              +
           "Actual Loops": 1,                             +
           "Index Cond": "((clus >= 10) AND (clus < 15))",+
           "Rows Removed by Index Recheck": 0,            +
           "Filter": "(val = 1001)",                      +
           "Rows Removed by Filter": 499                  +
         }                                                +
       ]                                                  +
     },                                                   +
     "Planning Time": 0.270,                              +
     "Triggers": [                                        +
     ],                                                   +
     "Execution Time": 0.388                              +
   }                                                      +

 

Check if INDEX we suggest already exists

look up existence of index ( https://stackoverflow.com/questions/2204058/list-columns-with-indexes-in-postgresql)

select
    t.relname as table_name,
    i.relname as index_name,
    array_to_string(array_agg(a.attname), ', ') as column_names
from
    pg_class t,
    pg_class i,
    pg_index ix,
    pg_attribute a
where
    t.oid = ix.indrelid
    and i.oid = ix.indexrelid
    and a.attrelid = t.oid
    and a.attnum = ANY(ix.indkey)
    and t.relkind = 'r'
    and t.relname like 'auth%'
group by
    t.relname,
    i.relname
order by
    t.relname,
    i.relname;
    

 table_name | index_name | column_names 
------------+------------+--------------
 authors    | authors_id | id

 

 

Creating data for test cases above

drop table t1;
 
 create table t1 ( 
    id  int,
    clus int,
    val  int,
    data VARCHAR(40)
 );
 
 insert into t1 (
    id, clus , val, data
)
  select 
  i,
  trunc(i/100),
  mod(i,10000),
  left(md5(random()::text), 40) 
from generate_series(1, 1000000) s(i);
  
select count(*) from t1 where clus = 1 ;
100
select count(*) from t1 where val =1 ;
100

create table t2 as select * from t1;  
  

explain (analyze,verbose,buffers)
select max(t1.data) from  t1, t2
where t1.id = t2.id 
and t1.clus = 1
;

Parameters that affect the explain plan

SET max_parallel_workers_per_gather = 0;
 set enable_mergejoin to 'off';
 set enable_nestloop to 'off';
 SET enable_hashjoin TO off;

Add A SQL*Net Security Banner And Audit Notice

I would have to say whilst I see security banners on customers Unix boxes when I am allowed to log in as part of a security audit I canot ever remember seeing a security banner when I log into a....[Read More]

Posted by Pete On 30/03/20 At 02:02 PM

Combinations and consequences

Fellow Perth techie Scott Wesley sent me this interesting puzzle recently. He was using the long awaited feature of being (finally) able to assign a sequence value via the DEFAULT clause in a table definition.

The problem was … the sequence was NOT being assigned. And since that column was defined as NOT NULL, his application was breaking. We had some to-and-fro and finally I managed to reduce it down a very small test case, which I’ll build from scratch here.



SQL>
SQL> create sequence seq;

Sequence created.

SQL>
SQL> create table t1
  2    ( c1  number not null enable,
  3      id  number default seq.nextval not null enable
  4     );

Table created.

Here is the reason I wanted to blog about this particular example. Just like our own applications, the Oracle database is an application. A very large, impressive and complex application, but an application nonetheless. And generally, the places where (any) application code encounters problems is when separate components are brought together in ways that are not expected. For example, if my application has 5 components, then I probably only need 5 unit tests to prove that those 5 components work. But if I want to test the combinations of those 5 components interacting together, now I need 31 tests, and that is not considering the sequencing of those 5 components, in which case it explodes to near 200. Expand an application out to 1000’s of components, and it is impossible to prove every possible permutation of usage.

Which brings us back to the example Scott sent me. Let’s do some testing of INSERT-SELECT components in isolation.



SQL>
SQL> --
SQL> -- INSERT-SELECT is fine!
SQL> --
SQL>
SQL> insert into t1  (c1)
  2  select 11713
  3  from ( select 1 x, 2 y from dual union all select 3,4 from dual ) p;

2 rows created.


SQL>
SQL> --
SQL> -- INSERT-SELECT with ORDER BY is fine!
SQL> --
SQL>
SQL> insert into t1  (c1)
  2  select 11713
  3  from ( select 1 x, 2 y from dual union all select 3,4 from dual ) p
  4  order by x,y;

2 rows created.


SQL>
SQL> --
SQL> -- INSERT-SELECT with a BIND is fine!
SQL> --
SQL>
SQL> variable x number
SQL> exec :x := 11713;

PL/SQL procedure successfully completed.

SQL>
SQL> insert into t1  (c1)
  2  select :x
  3  from ( select 1 x, 2 y from dual union all select 3,4 from dual ) p;

2 rows created.

But when we bring all those components together…



SQL> --
SQL> -- INSERT-SELECT with a BIND and ORDER BY .... uh oh
SQL> --
SQL>
SQL> insert into t1  (c1)
  2  select :x
  3  from ( select 1 x, 2 y from dual union all select 3,4 from dual ) p
  4  order by x,y;
insert into t1  (c1)
*
ERROR at line 1:
ORA-01400: cannot insert NULL into ("MCDONAC"."T1"."ID")

Chatting to the Optimizer team in Oracle, its probable that this bug has been present almost all the way back to early 11g versions, and yet has never been noticed until Scott chanced upon it.

A simple workaround is to wrap the SELECT part within an WITH statement



SQL> insert into t1  (c1)
  2  with ttt as ( select :x
  3  from ( select 1 x, 2 y from dual union all select 3,4 from dual ) p
  4  order by x,y
  5  )
  6  select * from ttt;
 
2 rows created.

but the message of this post is not about this particular idiosyncrasy, but more to always be on the lookout for where you might be “mixing and matching” elements of the Oracle database application in ways that perhaps are not considered typical. That’s why you are most likely to hit boundary cases such as this one.

Oracle 19c Automatic Indexing: Index Created But Not Actually Used (Because Your Young)

    The following is an interesting example of how Oracle Automatic Indexing is currently implemented that can result in an Automatic Index being created but ultimately ignored by the CBO. To illustrate, we begin by creating a simple little table that has two columns of particular interest, CODE2 which has 100 distinct values and […]

Oracle recovery concepts

I’ve published a while ago a twitter thead on some Oracle recovery concepts. For those who are not following twitter, I’m putting the whole thread here:
 

</p />
</p></div>

    	  	<div class=

Locating Oracle Enterprise Linux Images for Azure

Just like with other UI’s, the Azure portal may not show you the wealth of offerings that can be found in the Azure catalog.  For those Oracle DBAs hoping to build an IaaS VM image from a certified OS version, this means they need Oracle Enterprise Linux and are frustrated when they can’t locate it in the portal.

Empower with the Cloud Shell

Oracle DBAs rarely are fascinated with user interfaces or portals, so when you offer us something like the Azure Cloud Shell, it’s something we should embrace.  I’ve spoke about it before and I will rave about it again here.  If you haven’t set it up, skip the download to your desktop and simply go to Azure Cloud Shell and connect to your Azure account.  Once there, ensure you’ve set the CLI to BASH and use this just as you would Putty or Xterminal.

https://dbakevlar.com/wp-content/uploads/2020/03/blog_0327-300x112.jpg 300w, https://dbakevlar.com/wp-content/uploads/2020/03/blog_0327-768x286.jpg 768w, https://dbakevlar.com/wp-content/uploads/2020/03/blog_0327.jpg 1170w" sizes="(max-width: 800px) 100vw, 800px" />

If you configure it with Azure Cloud Storage, you can even use it as if it were a jump box, housing your scripts, just as I do.  I not only store scripts to automate my Azure processing, I also connect to any VM from this point.

https://dbakevlar.com/wp-content/uploads/2020/03/blog_03271-300x42.jpg 300w, https://dbakevlar.com/wp-content/uploads/2020/03/blog_03271-768x106.jpg 768w, https://dbakevlar.com/wp-content/uploads/2020/03/blog_03271-1536x213.jpg 1536w, https://dbakevlar.com/wp-content/uploads/2020/03/blog_03271-2048x284.jpg 2048w" sizes="(max-width: 800px) 100vw, 800px" />

Listing Images

You can also run AZ commands from the Azure Cloud Shell prompt.  The following prompt will return a list of all Oracle Enterprise Linux Images:

az vm image list \
   --offer Oracle-Linux \
   --all \
   --publisher Oracle \
   --output table

The output will look like the following:

https://dbakevlar.com/wp-content/uploads/2020/03/blog_03272-300x202.jpg 300w, https://dbakevlar.com/wp-content/uploads/2020/03/blog_03272-768x518.jpg 768w, https://dbakevlar.com/wp-content/uploads/2020/03/blog_03272-1536x1036.jpg 1536w, https://dbakevlar.com/wp-content/uploads/2020/03/blog_03272-2048x1382.jpg 2048w" sizes="(max-width: 800px) 100vw, 800px" />

Create an OEL VM

From this list, you can choose the version of Oracle Linux you want and then use the “Urn” to create a VM using the image.  In our example, we’ll create an Oracle Enterprise Linux version 7.6 and request the latest patched version from the catalog:

az vm create \
   --resource-group orHGrp1 \
   --name oraVM1 \
   --image Oracle:Oracle-Linux:7.6:7.6.3:latest \
   --size Standard_DS2_v2 \
   --admin-username azureuser \
   --generate-ssh-keys

Once this is complete, you’ll have an Oracle Enterprise Manager Linux VM, using Standard SSD disk, with the SSH keys set for your user and it will reside in a resource group called oraHGrp1 and be named oraVM1.

There are more steps to be performed before this is ready for Oracle or anything else, but for those wanting to know how to locate the OEL images, here’s your starting point.

Have a great weekend!

 



Tags:  , ,


Del.icio.us



Facebook

TweetThis

Digg

StumbleUpon




Copyright ©  [Locating Oracle Enterprise Linux Images for Azure], All Right Reserved. 2020.