Size of the processes structure in SGA

I have recently recommended a client to increase PROCESSES parameter from 2000 to 8000. The question was how much memory will that use? The answer is surprisingly small. The first snippet shows the initial state:

QL> show parameter processes

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
aq_tm_processes 		     integer	 1
db_writer_processes		     integer	 1
gcs_server_processes		     integer	 0
global_txn_processes		     integer	 1
job_queue_processes		     integer	 80
log_archive_max_processes	     integer	 4
processes			     integer	 320

SQL> select name,value from v$sga
  2  /

NAME			  VALUE
-------------------- ----------
Fixed Size		9144984
Variable Size	     2214592512
Database Buffers     2734686208
Redo Buffers		7630848

After that, I increased the PROCESSES for 1000 and restarted the instance:

SQL> alter system set processes=1320 scope=spfile;

System altered.

SQL> startup force
ORACLE instance started.

Total System Global Area 4966052456 bytes
Fixed Size		    9146984 bytes
Variable Size		 2214592512 bytes
Database Buffers	 2734686208 bytes
Redo Buffers		    7626752 bytes
Database mounted.
Database opened.

With 320 processes, the fixed size was 9144984 bytes. With 1320 processes, it was 9146984 bytes. The difference is 2000 bytes. So, it gives only 2 bytes per process. That looks a bit too small. Alternatively, we could sum the column sizes from V$PROCESS. Unfortunately, that too gives an unreliable result

SQL> select sum(vsize(column_name)) from user_tab_columns where table_name='V_$PROCESS';
   SUM(VSIZE(COLUMN_NAME)) 
__________________________ 
                       200 

Elapsed: 00:00:00.003

Just the trace file name can take up to 513 bytes so 200 bytes is not a credible result. Unfortunately, V_$PROCESS is a view, not a table, so it is not possible to extract AVG_ROW_LEN from DBA_TABLES. To calculate the best possible size, we need a little script:

#!/usr/bin/env python3
""" This script prints the row size of V$PROCESS table
Author: Mladen Gogala
Date  : 04/07/2021
Version: 1.0
"""
import argparse
import sys
import re
import cx_Oracle

cols = []
parser = argparse.ArgumentParser(
    description='V_PROCESS: describe and print the row size of V$PROCESS table')
parser.add_argument("-u", "--username", dest="username", required=True,
                    help='Oracle connection using <usr>/<pwd>@<tns> syntax')
args = parser.parse_args()
row_size = 0
try:
    con = cx_Oracle.connect(args.username)
    query = con.cursor()
    query.execute("select * from v$process")
    for entry in query.description:
        srch=re.search("DB_TYPE_([A-Z]+)",str(entry[1]))
        ftype=srch.group(1)
        print(
            f"Column name: {entry[0]} Type: {ftype} Size: {entry[2]}")
        row_size += int(entry[2])
    print(f"Row size={row_size}")
except cx_Oracle.DatabaseError as ora_err:
    oerr = ora_err.args[0]
    print(f"Oracle error: {oerr}")
    sys.exit(-1)

This will give a lavish upper estimate on the size of the V$PROCESS table. This table is allocated when the instance is started, based on the PROCESSES parameter and cannot be changed until the the instance is restarted. So, here is the output:

mgogala@umajor python]$ ./v_process.py -u system/qwerty@ora19c
Column name: ADDR Type: RAW Size: 8
Column name: PID Type: NUMBER Size: 127
Column name: SOSID Type: VARCHAR Size: 24
Column name: SPID Type: VARCHAR Size: 24
Column name: STID Type: VARCHAR Size: 24
Column name: EXECUTION_TYPE Type: VARCHAR Size: 20
Column name: PNAME Type: VARCHAR Size: 5
Column name: USERNAME Type: VARCHAR Size: 15
Column name: SERIAL# Type: NUMBER Size: 127
Column name: TERMINAL Type: VARCHAR Size: 30
Column name: PROGRAM Type: VARCHAR Size: 48
Column name: TRACEID Type: VARCHAR Size: 255
Column name: TRACEFILE Type: VARCHAR Size: 513
Column name: BACKGROUND Type: VARCHAR Size: 1
Column name: LATCHWAIT Type: VARCHAR Size: 16
Column name: LATCHSPIN Type: VARCHAR Size: 16
Column name: PGA_USED_MEM Type: NUMBER Size: 127
Column name: PGA_ALLOC_MEM Type: NUMBER Size: 127
Column name: PGA_FREEABLE_MEM Type: NUMBER Size: 127
Column name: PGA_MAX_MEM Type: NUMBER Size: 127
Column name: NUMA_DEFAULT Type: NUMBER Size: 127
Column name: NUMA_CURR Type: NUMBER Size: 127
Column name: CPU_USED Type: NUMBER Size: 127
Column name: CON_ID Type: NUMBER Size: 127
Row size=2269

This was done on Oracle 19.10 on Oracle Linux 7.9 x86_64. So, increasing the PROCESSES parameter for 1000 increases the memory consumption at maximum for 2.2 MB. This is a bit too much since V$ tables map SGA structures. SGA structures don’t use database representation of numbers they use 64 bit integers, which means that every “NUMBER Size: 127” in the output above should be replaced by “8”. However, I will take it at face value since even with 127 bytes per number it really isn’t all that much today, when memory is counted in Gigabytes. It is hard to understand reluctance of some database administrators to increase the processes parameter.

For those who would like to know what the number looks like when “127” is replaced by “8”, it’s 1079. The above script can be trivially modified to replace “127” by “8”. However, that may not be the whole story since there may be some unmapped fields in the SGA structure. That is why I choose to operate with 2KB per row in the process table.

Posted in Uncategorized | Leave a comment

Unplug problem

I was asked to refresh one of the development PDB tenants from the corresponding production PDB. The platform is Linux x86_64 (OL 7.7) with Oracle 19.10 (Jan 2021 RU). Unfortunately, due to security measures, the only open port between the production server and the development server is 22. That eliminates remote clone. The tenant size in production is around 1TB. I opted for unplug pluggable database into ‘/tmp/tenant.pdb’. That took almost 2 hours with very fast disk storage (Pure array) and a powerful CPU. It was single threaded. Is there any way I can use multiple threads? The .pdb archive is compressed and my bottleneck was it being run on a single CPU. Is there any trick to do that with RMAN? The production has 144 cores and I could easily afford running 12 threads at a time. Unfortunately, I can’t restore pluggable database into another CDB using rman. This was like pulling teeth. I couldn’t get SQL*Net port (it isn’t 1521) open so I had to unplug. Fortunately, I knew what was I in for so I first did unplug to XML, dropped the pluggable database with “keep datafiles” and plugged it back in using XML. The whole thing is extremely awkward. The funny thing was that “CREATE PLUGGABLE DATABASE TESTDB3 USING ‘/tmp/tenant.pdb'” used 2 cores. It was 4 times as fast as creating the PDB archive. Oracle’s multi-tenant leaves a lot to be improved. I hope that Oracle 21 might address some of these issues.

Posted in Uncategorized | Leave a comment

PITR and BIGFILE tablespaces

I was lazy and I created a BIGFILE tablespace to alleviate the pain with constant space monitoring and adding 32767M files to the tablespace. However, there was a logical corruption caused by running a wrong script on the schema, so I was told to restore the pluggable database to the point in time before the script was run. And that is where the trouble started:

915389 alter database recover datafile list clear
915390 Completed: alter database recover datafile list clear
915391 2021-03-03T22:13:09.935629-05:00
915392 RMAN In-place recover pluggable database id 3 to scn 0
915393 Pluggable Database PSH_8141 (3) incomplete recovery start set at change 45008648710, at 03/03/2021 10:30:55
915394 Pluggable Database Media Recovery Start
915395 2021-03-03T22:13:09.939521-05:00
915396 Serial Media Recovery started
915397 Pluggable Database Media Recovery Log /data/archive/1_65868_1031066218.dbf
915398 2021-03-03T22:13:12.996469-05:00
915399 Incomplete Recovery applied until change 44962012457 time 03/03/2021 09:00:15
915400 Errors in file /app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_86417_86514.trc (incident=13548103) (PDBNAME=CDB$ROOT):
915401 ORA-00600: internal error code, arguments: [kcpSetPDBIncompleteRecoverySCN-1], [44962012457], [45008648710], [], [], [], [], [], [], [], [], []
915402 Incident details in: /app/oracle/diag/rdbms/orcl/orcl/incident/incdir_13548103/orcl_ora_86417_86514_i13548103.trc
915403 2021-03-03T22:13:14.351287-05:00
915404 Some DDE async actions failed or were cancelled
915405 2021-03-03T22:13:14.407330-05:00
915406 Use ADRCI or Support Workbench to package the incident.

Long story short, the tenant was lost. It was impossible to do PITR on the tablespace with 8.7 TB file. I even tried recovering the whole database. I contacted the Oracle support but they weren’t able to help. The Oracle version is 12.2.0.1, the platform is Linux x86_64. Be aware that bigfile tablespaces can result in the inability to do point in time recovery. I haven’t tried the complete recovery, because that would also bring back the results of the “masking” script. Apparently, Oracle isn’t testing backup and recovery with the bigfile tablespaces, so surprises are possible. I thought that the feature will be stable by 12.2, but alas, no luck.

Posted in Uncategorized | Leave a comment

Delayed cleanout

Delayed cleanout is a long standing feature of Oracle RDBMS. I remember it from version 7. Essentially, Oracle doesn’t have a classic lock manager with queueing, lock conversion and lock escalation, like some other databases. Oracle creates a transaction entry in the in-memory copy of the Oracle block and then adds the pointer to that transaction entry to the row header. That’s it. The next part of the story happens when there is not enough room in the SGA so DBWR has to make some room by writing a batch of blocks out to the disk. DBWR will write the entire block, including the transaction entries and row headers, thereby including the row locks, to the database file.

The next time the block is brought back into the SGA, it will be automatically checked up. The transaction entries not pointing to the valid transactions will be cleaned up and so will any accompanying pointers in the row headers. That process briefly locks the rows being cleaned up and is known to sometimes cause deadlocks. The block is then flagged as dirty (modified) and sent to the closest DBWR to write it down to disk when the time comes. The usual advice was to do “select * from <table_name>” immediately aftet the large transaction, to clean out the invalid lock entries.

Now, why did I write “brought back into the SGA” in the bold font? Here come Oracle 11g and later versions which will sometimes read the table into the PGA, not SGA. DBWR doesn’t have access to the PGA and so the delayed block cleanout is not performed when full table scan is done. The ITL entries (aka “transaction entries”) will survive the full table scan and the delayed cleanout will be done at the least opportune moment, probably when an application needs to update the row. In Oracle 12c and later, there are two mechanisms for forcing the block cleanup:

  • ALTER TABLE MOVE
  • Big table caching

Alter table move is classic, if expensive, method to force block cleanup. Big table caching is new, in Oracle 12c. Arup Nanda, my former colleague and tech lead from Oxford Health Plans, has written an article about big table caching in his inimitable style:

https://blogs.oracle.com/oraclemagazine/big-table-caching

The information in the article is complete and useful. However, the point of this post is that the old advice about performing a full table scan is no longer valid because of the new method of doing the full table scan.

Posted in Uncategorized | Leave a comment

Starting oracle with huge pages

Last year I blogged about the systemd service to start and stop oracle:

https://dbwhisperer.wordpress.com/2019/05/05/systemd-service-to-start-stop-oracle/

Last week, I noticed that my Oracle 19.9 virtual machines have started swapping. That was strange. There are only test instances on those boxes, memory has been carefully balanced and swapping shouldn’t have occurred. Then I noticed that huge pages are not being used at all when I started Oracle from systemd service. They have been used normally when I started the instance manually, from sqlplus. So I’ve set “use_large_pages=only”, restarted the system and lo and behold, here is what I got:

Available system pagesizes:
   4K, 2048K
  Supported system pagesize(s):
   PAGESIZE  AVAILABLE_PAGES  EXPECTED_PAGES  ALLOCATED_PAGES  ERROR(s)
      2048K             2536            2370               0   ORA-27125
  Reason for not supporting certain system pagesizes:
   4K - Large pagesizes only
 RECOMMENDATION:
 Increase per process memlock (soft) limit to at least 4740MB
 to lock 100% of SHARED GLOBAL AREA (SGA) pages into physical memory 
 
 SGA: Realm creation failed

So, systemd cannot start Oracle with huge pages but sqlplus from the command line can. I have set up the limits in /etc/security/limits.conf, that wasn’t the problem. For some reason, processes spawned by systemd weren’t using the limits. I searched Oracle support and came up with the following page:

How to set ulimit values to take effect for systemd service (Doc ID 2681853.1)

Unfortunately, the prescriptions from that page haven’t worked. I modified the service the way that Oracle Support recommended:

Unit]
 Description=oracle
 Documentation=https://docs.oracle.com
 LimitNOFILE=131072
 LimitMEMLOCK=6000000
 Wants=network-online.target
 After=network-online.target
 AssertFileIsExecutable=/usr/local/bin/dbstart

That didn’t work. Then, I did the same thing with /etc/systemd/system.conf and rebooted the system. That didn’t work either. Unsurprisingly, systemd is buggy and doesn’t do what it’s supposed to do. The solution was to add the startup to /etc/rc.local:

-bash-4.2$ cat /etc/rc.local
 !/bin/bash
 THIS FILE IS ADDED FOR COMPATIBILITY PURPOSES
 #
 It is highly advisable to create own systemd services or udev rules
 to run scripts during boot instead of using this file.
 #
 In contrast to previous versions due to parallel execution during boot
 this script will NOT be run after all other services.
 #
 Please note that you must run 'chmod +x /etc/rc.d/rc.local' to ensure
 that this script will be executed during boot.
 touch /var/lock/subsys/local
 export ORACLE_HOME=/opt/oracle/product/19c/dbhome_1
 su - oracle -c "dbstart $ORACLE_HOME"

That gave me the best of the both worlds: systemd would still shut the database down orderly when it was started and it would start using the large pages:

-bash-4.2$ grep -i huge /proc/meminfo
 AnonHugePages:         0 kB
 ShmemHugePages:        0 kB
 FileHugePages:         0 kB
 HugePages_Total:    2536
 HugePages_Free:      168
 HugePages_Rsvd:        2
 HugePages_Surp:        0
 Hugepagesize:       2048 kB
 Hugetlb:         5193728 kB
 -bash-4.2$ 

Since my machines are small virtual machines in VirtualBox, 16GB RAM each, I’d rather have Oracle not start at all than to start without configured huge memory. Until systemd is fixed, this is the solution that I will adopt. It does start Oracle with huge pages and it shuts it down orderly when the machine is shut down. The Linux that I am using is the latest Oracle Linux 7:

-bash-4.2$ cat /etc/oracle-release 
 Oracle Linux Server release 7.9
 -bash-4.2$ uname -r
 5.4.17-2036.100.6.1.el7uek.x86_64
 -bash-4.2$ 

Things may be better on OL 8.3 but I am too lazy to upgrade all my machines to the new Linux. It’s a lot of work and there is no reason to do that yet. For now, the /etc/rc.local trick will have to do.

Posted in Uncategorized | Leave a comment

PyODBC fast_executemany and Oracle RDBMS

Oracle has array interface well over two decades. The purpose of the array interface is to reduce the number of network trips needed for performing batch operations like fetching multiple row at once or inserting multiple rows at once. ODBC interface, however, is another story. ODBC interface is meant to be portable and database agnostic. In other words, ODBC operates with the smallest common denominator of the available features in various programmatic interfaces for relational databases.

Being an Oracle DBA, I use ODBC primarily to load data from an Oracle database into another type of database, like MySQL, PostgreSQL or SQL Server. However, some scripts are just performing general functions, like loading a CSV file into a database, so it’s nice to have one script to rule them all. These days, I mostly script in Python, due to the market demand, so Pyodbc library will be used to demonstrate the script.

This article is about the “fast_executemany” property of a Pyodbc connection. The script I used is called load_csv.py and it looks like this:

#!/usr/bin/env python3
""" This script loads the content of a CSV file into a table
This script uses ODBC connection and can be used to load table in
any database that supports ODBC. The connection syntax is Oracle
like, with the ODBC DSN instead of an Oracle TNS descriptor.
Author: Mladen Gogala
Version: 1.0
Date  : 11/08/2020

"""
import argparse
import csv
import sys
import re
import pyodbc

parser = argparse.ArgumentParser(description='LOAD_CSV load a CSV into table')
parser.add_argument("-u", "--username", dest="username", required=True,
                    help='ODBC connection using <usr>/<pwd>@<DSN syntax')
parser.add_argument("-t", "--table", dest="table", required=True,
                    help='Table to be loaded')
parser.add_argument("-d", "--delimiter", dest="delimiter", default=',',
                    help="CSV delimiter character. The default is ','")
parser.add_argument("--noquote", dest="noquote", action="store_true",
                    help="Fields in CSV file are not quoted")
parser.add_argument("-F", "--fast-execmany", dest="fast_exec", action="store_true",
                    help="Use fast_executemany flag")
parser.add_argument("-i", dest="file", required=True,
                    help="File to load into the database")
parser.add_argument("-n", "--arraysize", dest="nrows", default=1024, type=int,
                    help="Batch size for bulk insert")
args = parser.parse_args()
(uid, pwd, dsn) = (None, None, None)
# Rows are read from the CSV file and inserted into the "batch_ins" list.
# The resulting list is then inserted into the database using "executemany"
# Pyodbc function. However, Pyodbc cheats and internally runs a loop which sends
# the rows to the database one by one instead of sending the rows in batches,
# unless the driver supports "fast_executemany" flag. 
batch_ins = []
# ODBC has some strange philosophy when it comes to counting rows, it's better
# to do it programmatically.
rows = 0
# The insert statement needs to be constructed for the table. The table name is
# a script argument. The number of the bind variables is determined by counting
# the fields in the CSV file.
insert_str = f"insert into {args.table} values(?"
str_complete = False
parse_usr = re.search(r"(\w+)/(.*)@(.*)", args.username)
if parse_usr:
    uid = parse_usr.group(1)
    pwd = parse_usr.group(2)
    dsn = parse_usr.group(3)
if (uid is None) or (pwd is None) or (dsn is None):
    print("Incorrect connect string syntax. <username>/<password>@DSN expected")
    print("DSN is the name of the ODBC connection")
    sys.exit(-1)
try:
    conn = pyodbc.connect(f"DSN={dsn};UID={uid};PWD={pwd}")
    conn.autocommit = False
    cursor = conn.cursor()
# If the driver supports "fast_executemany" flag, use it. So far, only the original
# Microsoft ODBC SQL Server drivers, both on Windows and Linux support that flag.
    if args.fast_exec:
        cursor.fast_executemany = True
    with open(args.file, "r") as f:
        if args.noquote:
            reader = csv.reader(f, delimiter=args.delimiter,
                                quoting=csv.QUOTE_NONE)
        else:
            reader = csv.reader(f, delimiter=args.delimiter,
                                quoting=csv.QUOTE_ALL)
        for row in reader:
            if not str_complete:
                for i in range(1, len(row)):
                    insert_str += ",?"
                insert_str += ")"
                str_complete = True
            batch_ins.append(row)
            rows += 1
            if not len(batch_ins) % args.nrows:
                cursor.executemany(insert_str, batch_ins)
                conn.commit()
                batch_ins = []
    if len(batch_ins) > 0:
        cursor.executemany(insert_str, batch_ins)
        conn.commit()
except pyodbc.Error as odbc_exc:
    sqlstate = odbc_exc.args[0]
    print("Error: SQLSTATE =", sqlstate)
    sys.exit(255)
except OSError as oserr:
    os_err = oserr.args
    print("Cannot open file:", args.file)
    print(f"Error:{os_err}")
print(rows, "rows loaded")

The “fast_executemany” flag activates the array interface that saves a lot of time and network trips. The cursor.executemany() function internally performs a loop and sends rows one by one, unless “fast_executemany” flag is specified. If the flag is specified and if the drivers supports it, the database will perform the operation (insert or update) on the entire array in a single operation. Without that flag, the array size, specified by the “-n” argument doesn’t have too much impact on the performance:

mgogala@umajor:~/work/python$ time ./load_csv.py -u scott/tiger@Ora-19 -t test_tab -i /tmp/test_tab.csv 
 5000000 rows loaded
 real    18m30.113s
 user    1m7.887s
 sys    0m49.030s
 mgogala@umajor:~/work/python$ time ./load_csv.py -u scott/tiger@Ora-19 -t test_tab -i /tmp/test_tab.csv -n 16384
 5000000 rows loaded
 real    16m22.102s
 user    1m7.751s
 sys    0m45.439s

Loading 5,000,000 rows into a table takes 18.5 minutes with the default 1024 rows batch and 16 minutes 22 seconds with 16,384 rows batch. Nothing to write home about. MyODBC DSN is Ora-19 and the driver is Instant Client 19.9 ODBC driver. Interestingly, this wasn’t working with the Instant Client 19.8 driver. My /etc/odbcinst.ini looks like this:

[Ora19c]
 Description=Oracle ODBC driver for Oracle 19
 Driver=/usr/lib/instantclient_19_9/libsqora.so.19.1
 Setup=
 FileUsage=
 CPTimeout=
 CPReuse=

Now, let’s truncate the table and repeat the test with “fast_executemany”, controlled by the “-F” argument:

gogala@umajor:~/work/python$ time ./load_csv.py -u scott/tiger@Ora-19 -t test_tab -i /tmp/test_tab.csv  -F
 5000000 rows loaded
 real    1m1.061s
 user    0m10.475s
 sys    0m0.523s

mgogala@umajor:~/work/python$ time ./load_csv.py -u scott/tiger@Ora-19 -t test_tab -i /tmp/test_tab.csv  -F -n 16384
 5000000 rows loaded
 real    0m49.057s
 user    0m11.165s
 sys    0m6.441s
 mgogala@umajor:~/work/python

Now, that is much better.Please note that increasing the array size from 1024 rows to 16384 rows has increased performance for about 15%. Performance would be better still if I haven’t kept Golden Gate on, but I wanted to see how fast will GG replicate the load. Golden Gate did that practically in real time:

2020-11-21T15:29:04.201-0500  INFO    OGG-25222  Oracle GoldenGate Delivery for Oracle, rtgt1.prm:  Large transaction completed. Total records: 16,384.
 2020-11-21T15:29:04.404-0500  INFO    OGG-25221  Oracle GoldenGate Delivery for Oracle, rtgt1.prm:  Processing transaction (XID: 6.33.6307, Seqno: 54, RBA: 447591827) larger than eager size (15,100).
 2020-11-21T15:29:04.405-0500  INFO    OGG-25222  Oracle GoldenGate Delivery for Oracle, rtgt1.prm:  Large transaction completed. Total records: 16,384.
 2020-11-21T15:29:04.577-0500  INFO    OGG-25221  Oracle GoldenGate Delivery for Oracle, rtgt1.prm:  Processing transaction (XID: 4.2.6255, Seqno: 54, RBA: 449607101) larger than eager size (15,100).
 2020-11-21T15:29:04.577-0500  INFO    OGG-25222  Oracle GoldenGate Delivery for Oracle, rtgt1.prm:  Large transaction completed. Total records: 16,384.
 2020-11-21T15:29:11.240-0500  INFO    OGG-25221  Oracle GoldenGate Delivery for Oracle, rtgt1.prm:  Processing transaction (XID: 7.32.6253, Seqno: 54, RBA: 451622374) larger than eager size (15,100).
 2020-11-21T15:29:12.157-0500  INFO    OGG-25222  Oracle GoldenGate Delivery for Oracle, rtgt1.prm:  Large transaction completed. Total records: 16,384.
 2020-11-21T15:29:12.256-0500  INFO    OGG-25221  Oracle GoldenGate Delivery for Oracle, rtgt1.prm:  Processing transaction (XID: 10.29.6255, Seqno: 54, RBA: 453637648) larger than eager size (15,100).
 2020-11-21T15:29:12.256-0500  INFO    OGG-25222  Oracle GoldenGate Delivery for Oracle, rtgt1.prm:  Large transaction completed. Total records: 16,384.

The GG log clearly shows 16K rows transactions being processed. However, GG is not the subject of this story, the “fast_executemany” flag is. Unfortunately, the “fast_executemany” is not compatible with all drivers. The wiki for Pyodbc fast_executemany flag support is here

https://github.com/mkleehammer/pyodbc/wiki/Driver-support-for-fast_executemany

Some drivers, like FreeTDS driver for SQL Server will even crash the Python interpreter. For the drivers that do work, the benefits are immense.

Posted in Uncategorized | Leave a comment

Flash cache

I have recently had a question about flash cache. That feature is not very popular because it was only available on Oracle engineered systems, until very recently. It is available on Oracle 12.2 and newer systems, I haven’t tested the older ones.  Flash cache is an extension for SGA, a bit slower write back cache which speeds I/O for the designated tables.  It is usually a SSD which is much faster than rotational disks and much slower than memory. It’s usually implemented as 256 GB or 512 GB NVME device. Here is an example:

SQL> show parameter db_flash_cache
NAME                TYPE        VALUE                    
------------------- ----------- ------------------------ 
db_flash_cache_file string      /dev/oracleasm/asm-disk1 
db_flash_cache_size big integer 127G                     
SQL>

This database is neither RAC nor Oracle Restart, it is a plain vanilla database, with an NVME file added as a device to the virtual machine housing the database. The device was then made available to Oracle, using UDEV Linux mechanism. When the device file is available, Oracle needs to be made aware of it, by using the parameters above. After that, we get to pick and choose the tables you want cached in the flash cache:

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.8.0.0.0

SQL> alter table test_tab storage(flash_cache keep);

Table altered.

And that is it. Unfortunately, flash cache is not very well instrumented, it can be monitored using V$FLASHFILESTAT which doesn’t contain all that much useful information.

SQL> select name,enabled,bytes,singleblkrds,singleblkrdtim_micro
  2  from v$flashfilestat
  3  order by singleblkrds;


                       NAME    ENABLED           BYTES    SINGLEBLKRDS    SINGLEBLKRDTIM_MICRO 
___________________________ __________ _______________ _______________ _______________________ 
/dev/oracleasm/asm-disk1             1    136365211648            1094                  440167 


Elapsed: 00:00:00.554
SQL> /

The information returned is how many single block reads were performed and how many microseconds were spent on that. The number is cumulative, the system has spent 440167 microseconds to perform 1094 physical reads. Fortunately, sar -d can return better information:

4:10:01 PM       DEV       tps  rd_sec/s  wr_sec/s  avgrq-sz  avgqu-sz     await     svctm     %util
04:20:01 PM   dev11-0      0.00      0.00      0.00      0.00      0.00      0.00      0.00      0.00
04:20:01 PM    dev8-0     15.20    257.51     90.10     22.86      0.02      1.14      0.78      1.18
04:20:01 PM   dev8-16      0.12      1.49      0.48     16.00      0.00      0.32      0.09      0.00
04:20:01 PM  dev252-0     14.97    257.18     89.69     23.17      0.02      1.17      0.79      1.18
04:20:01 PM  dev252-1      0.00      0.00      0.00      0.00      0.00      0.00      0.00      0.00
04:20:01 PM  dev252-2      0.05      0.33      0.41     14.32      0.00      1.26      1.16      0.01
04:30:01 PM   dev11-0      0.00      0.00      0.00      0.00      0.00      0.00      0.00      0.00
04:30:01 PM    dev8-0     15.14    173.35    204.40     24.95      0.02      1.35      0.75      1.13
04:30:01 PM   dev8-16      0.08      1.36      0.00     16.00      0.00      0.04      0.02      0.00
04:30:01 PM  dev252-0     14.67    173.19     87.19     17.75      0.02      1.10      0.75      1.10
04:30:01 PM  dev252-1      0.00      0.00      0.00      0.00      0.00      0.00      0.00      0.00
04:30:01 PM  dev252-2      0.35      0.16    117.22    338.60      0.01     16.11      1.04      0.04
04:40:01 PM   dev11-0      0.00      0.00      0.00      0.00      0.00      0.00      0.00      0.00
04:40:01 PM    dev8-0     14.97    214.76     90.04     20.37      0.02      1.09      0.76      1.13
04:40:01 PM   dev8-16      0.20      1.73      1.55     16.00      0.00      1.00      0.15      0.00
04:40:01 PM  dev252-0     14.82    214.76     89.90     20.56      0.02      1.11      0.76      1.13
04:40:01 PM  dev252-1      0.00      0.00      0.00      0.00      0.00      0.00      0.00      0.00
04:40:01 PM  dev252-2      0.02      0.00      0.14      8.20      0.00      1.40      1.40      0.00

 

There are few reads and writes because it’s hard to produce relevant example on a small sample database. Another method is to use iostat:

-bash-4.2$ iostat -x 10 3
Linux 4.14.35-1902.305.4.el7uek.x86_64 (ora19c.home.com) 	08/30/2020 	_x86_64_	(4 CPU)

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
           1.47    0.00    0.37    0.42    0.00   97.73

Device:         rrqm/s   wrqm/s     r/s     w/s    rkB/s    wkB/s avgrq-sz avgqu-sz   await r_await w_await  svctm  %util
scd0              0.00     0.00    0.00    0.00     0.00     0.00     8.00     0.00    0.00    0.00    0.00   0.00   0.00
sda              20.23     0.26   12.21   17.78   594.46   894.60    99.33     0.08    2.66    1.06    3.76   0.84   2.51
sdb               1.46     0.00    2.99   11.39    36.01    91.16    17.68     0.05    3.18    2.03    3.49   0.11   0.16
dm-0              0.00     0.00   32.37   17.58   592.04   892.24    59.43     0.12    2.41    1.46    4.16   0.50   2.51
dm-1              0.00     0.00    0.01    0.00     0.13     0.00    50.09     0.00    0.20    0.20    0.00   0.14   0.00
dm-2              0.00     0.00    0.01    0.03     0.33     2.25   152.73     0.00    7.00    0.83    8.97   1.28   0.00

I don’t have any real life examples but my experience with real life flash is very good.  It really improves performance. The price for a small NVME device is low, flash cache can be a cheap way of significantly improving database performance.

Posted in Uncategorized | Leave a comment

Data Guard 19c

Oracle 19c has introduced significant changes into DG Broker. Many known and frequently used properties are deprecated or even de-supported. The following Oracle Data Guard broker properties are deprecated in Oracle Database 19c:

  • ArchiveLagTarget
  • DataGuardSyncLatency
  • LogArchiveMaxProcesses
  • LogArchiveMinSucceedDest
  • LogArchiveTrace
  • StandbyFileManagement
  • DbFileNameConvert
  • LogArchiveFormat
  • LogFileNameConvert

https://docs.oracle.com/en/database/oracle/oracle-database/19/upgrd/behavior-changes-deprecated-desupport-oracle-database.html#GUID-67D84AC9-4E9A-4F10-82C4-0FCF40AD16E1

Deprecated  instance properties are mainly instance parameters.  Instead of setting instance properties in DGMGRL, the following is now recommended:

DGMGRL> edit database orclcdb set parameter archive_lag_target=600;
Parameter "archive_lag_target" updated
DGMGRL>

That is completely equivalent to the corresponding “ALTER SYSTEM”. That is rather practical because it is possible to change parameters for multiple database without reconnecting:

Welcome to DGMGRL, type "help" for information.
Connected to "ORCLCDB"
Connected as SYSDBA.
DGMGRL> edit database orclcdb set parameter fal_client=stby;
Parameter "fal_client" updated
DGMGRL> edit database stby set parameter fal_client=orclcdb;
Connected to "STBY"
Parameter "fal_client" updated
DGMGRL> edit database stby set parameter fal_server=orclcdb;
Connected to "STBY"
Parameter "fal_server" updated
DGMGRL> edit database orclcdb set parameter fal_server=stby;
Parameter "fal_server" updated

Unfortunately, there is no “show parameter” command in DGMGRL. It is not possible to list database parameter from DGMGRL, which is rather awkward. Alert log shows what is happening in the background:

2020-08-14 18:01:06.370000 -04:00
ALTER SYSTEM SET fal_client='orclcdb' SCOPE=BOTH;
2020-08-14 18:01:27.001000 -04:00
ALTER SYSTEM SET fal_server='orclcdb' SCOPE=BOTH;
2020-08-14 18:02:33.341000 -04:00

DG broker will now keep most of the instance related information in the database itself, instead of the configuration files. Validate database command, which is very useful, still has the same problem with identifying the standby redo log files

Managed by Clusterware:
orclcdb: NO 
stby : NO 
Validating static connect identifier for the primary database orclcdb...
The static connect identifier allows for a connection to database "orclcdb".

Log Files Cleared:
orclcdb Standby Redo Log Files: Cleared
stby Online Redo Log Files: Not Cleared
stby Standby Redo Log Files: Available

Current Log File Groups Configuration:
Thread # Online Redo Log Groups Standby Redo Log Groups Status 
(orclcdb) (stby) 
1 3 3 Insufficient SRLs

Future Log File Groups Configuration:
Thread # Online Redo Log Groups Standby Redo Log Groups Status 
(stby) (orclcdb) 
1 3 0 Insufficient SRLs
Warning: standby redo logs not configured for thread 1 on orclcdb

Both instances have 4 standby log files and 3 redo log files, 200 MB each.

There is also a significant bug: add database will not populate LOG_ARCHIVE_DEST_<n> parameter. That will not be a problem until the switch-over is performed. Then, the newly minted primary database will not know where to send the log files:

SQL> show parameter log_archive_dest_2

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_2 string
log_archive_dest_20 string

In Oracle 18c  and earlier versions, this parameter was populated during the “add database” command. Not just that, this command is explicitly cleared when the standby is restarted:

Starting background process ARC2
ARC2 started with pid=43, OS id=15502 
Starting background process ARC3
ARC3 started with pid=44, OS id=15504 
TMON (PID:15478): ARC1: Archival started
TMON (PID:15478): ARC2: Archival started
TMON (PID:15478): ARC3: Archival started
TMON (PID:15478): STARTING ARCH PROCESSES COMPLETE
2020-08-14 19:04:47.924000 -04:00
rfs (PID:15512): Primary database is in MAXIMUM PERFORMANCE mode
rfs (PID:15512): Selected LNO:10 for T-1.S-92 dbid 2778897253 branch 1007835883
Starting Data Guard Broker (DMON)
Starting background process INSV
INSV started with pid=47, OS id=15515 
2020-08-14 19:04:51.672000 -04:00
Starting background process NSV0
NSV0 started with pid=48, OS id=15519 
2020-08-14 19:04:54.903000 -04:00
Starting background process RSM0
RSM0 started with pid=49, OS id=15524 
2020-08-14 19:04:59.168000 -04:00
RSM0 (PID:15524): Using STANDBY_ARCHIVE_DEST parameter default value as /ora/arch [krsd.c:18141]
ALTER SYSTEM SET log_archive_dest_2='' SCOPE=BOTH;

This is strange.  If the parameter is empty, validate database gives back a strange warning:

2020-08-14 19:15:20.258000 -04:00
SWITCHOVER VERIFY: Send VERIFY request to switchover target orclcdb
SWITCHOVER VERIFY WARNING: switchover target has no standby database defined in LOG_ARCHIVE_DEST_n parameter. If the switchover target is converted to a primary database, the new primary database will not be protected.
Data Guard Broker: Switchover processing will set LOG_ARCHIVE_DEST_n parameter. Continuing switchover

However, switchover succeeds in both directions, even without the LOG_ARCHIVE_DEST_2. The most important thing is that  switch-over succeeds. The log destination blues will probably be resolved in a future release. By the way, this test was done with Oracle 19.8 on Oracle Linux 7.8:

-bash-4.2$ sql sys/Qwerty123%@stby as sysdba

SQLcl: Release 19.1 Production on Fri Aug 14 19:43:17 2020

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

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.8.0.0.0

SQL>

This is the latest and the greatest version as of the moment when this is being written. Data guard has obviously been significantly rewritten in the Oracle 19c. The rough edges will probably be fixed in the next few release updates.

 

 

 

 

Posted in Uncategorized | Leave a comment

Oracle 19c datapatch problem

I’ve installed July 2020 RU to one of my databases and have had a heck of a time to figure it out. I was installing the RU on top of Oracle 19.7 EE. The “opatch apply” went without a hitch but datapatch -verbose was failing with the following message:

catconInit: unexpected error in get_instance_status_and_name

The log files were as useless as useless can be, without the least indication as to what may be the problem. I sometimes think that Oracle developers really hate database administrators and want to exact revenge on them for the sins committed by our forefathers, somebody like the last common ancestor of Tom Kyte and me. I am sure that such a creature will one day be found somewhere in the Great Rift Valley. However, Oracle Support saved the day:

Datapatch fails with catconInit: unexpected error in get_instance_status_and_name (Doc ID 2409301.1)

It turns out that modified glogin.sql can break the datapatch utility. People will soon start installing this around and glogin.sql is a very frequently modified file. So, if you don’t want to hit the same problem, here are the cause and the solution:

Cause

The issue was caused by customization to the <ORACLE_HOME>/sqlplus/admin/glogin.sql

The datapatch script connects to the database to apply the patch and it expects a certain response to verify that it has connected, but since the glogin was customized datapatch does not understand the response and so it fails.

Solution

1. Temporarily rename <ORACLE_HOME>/sqlplus/admin/glogin.sql

2. Retry datapatch

3. Rename glogin.sql back to the original name

Posted in Uncategorized | Leave a comment

DG Broker: VALIDATE DATABASE

DG broker has a very useful command but I think I’ve found a some holes in it:

oracle@vbox3 scripts]$ dgmgrl sys/sys@prim
DGMGRL for Linux: Release 19.0.0.0.0 - Production on Sun Mar 8 13:40:59 2020
Version 19.6.0.0.0

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

Welcome to DGMGRL, type "help" for information.
Connected to "prim"
Connected as SYSDBA.
DGMGRL> show configuration

Configuration - prim

Protection Mode: MaxPerformance
Members:
prim - Primary database
stby - Physical standby database

Fast-Start Failover: Disabled

Configuration Status:
SUCCESS (status updated 9 seconds ago)

DGMGRL> validate database verbose stby

Database Role: Physical standby database
Primary Database: prim

Ready for Switchover: Yes
Ready for Failover: Yes (Primary Running)

Flashback Database Status:
prim : Off
stby: Off

Capacity Information:
Database Instances Threads
prim 1 1
stby 1 1

Managed by Clusterware:
prim : NO
stby: NO
Validating static connect identifier for the primary database prim...
The static connect identifier allows for a connection to database "prim".

Temporary Tablespace File Information:
prim TEMP Files: 5
stby TEMP Files: 5

Data file Online Move in Progress:
prim: No
stby: No

Standby Apply-Related Information:
Apply State: Running
Apply Lag: 0 seconds (computed 1 second ago)
Apply Delay: 0 minutes

Transport-Related Information:
Transport On: Yes
Gap Status: No Gap
Transport Lag: 0 seconds (computed 0 seconds ago)
Transport Status: Success

Log Files Cleared:
prim Standby Redo Log Files: Cleared
stby Online Redo Log Files: Cleared
stby Standby Redo Log Files: Available

Current Log File Groups Configuration:
Thread # Online Redo Log Groups Standby Redo Log Groups Status
(prim) (stby)
1 4 4 Insufficient SRLs

Future Log File Groups Configuration:
Thread # Online Redo Log Groups Standby Redo Log Groups Status
(stby) (prim)
1 4 2 Insufficient SRLs

Current Configuration Log File Sizes:
Thread # Smallest Online Redo Smallest Standby Redo
Log File Size Log File Size
(prim) (stby)
1 1024 MBytes 1024 MBytes

Future Configuration Log File Sizes:
Thread # Smallest Online Redo Smallest Standby Redo
Log File Size Log File Size
(stby) (prim)
1 1024 MBytes 1024 MBytes

Apply-Related Property Settings:
Property prim Value stby Value
DelayMins 0 0
ApplyParallel AUTO AUTO
ApplyInstances 0 0

Transport-Related Property Settings:
Property prim Value stby Value
LogShipping ON ON
LogXptMode ASYNC ASYNC
Dependency <empty> <empty>
DelayMins 0 0
Binding optional optional
MaxFailure 0 0
ReopenSecs 300 300
NetTimeout 30 30
RedoCompression DISABLE DISABLE

DGMGRL>

Where does the confusion begin? Well, the output tells me that I have insufficient SRLs (“SRL = Stanby Redo Logs”),  but both databases have the same number of primary and standbye redo logs. This command is counting them incorrectly. Other than that, the command is very useful and should be executed before switchover.

 

Posted in Uncategorized | 2 Comments