Search

Top 60 Oracle Blogs

Recent comments

Oakies Blog Aggregator

External table preprocessor on Windows

There are plenty of blog posts about using the pre-processor facility in external tables to get OS level information available from inside the database. Here’s a simple example of getting a directory listing:


run_os.bat
==========
@echo off
cd \oracle
dir /b

SQL> create table fs_size (
  2   disk varchar2(64)
  3  )
  4  organization external
  5  (  type oracle_loader
  6     default directory temp
  7     access parameters
  8     ( records delimited by newline
  9       preprocessor  temp:'run_os.bat'
 10     )
 11     location ( temp:'empty.txt' )
 12  ) reject limit unlimited ;

Table created.

SQL>
SQL> select * from fs_size;

DISK
------------------------------------------------
18c_cloud
admin
adw_cloud
atp_cloud
audit
cfgtoollogs
checkpoints
datamodeler
dbsat
diag
instantclient
jdbc183
ords.184
ords122
ords181
ords184
ordsconf
product
sql
sqlcl
sqldeveloper
swingbench
wallet

So far so good. But on Windows, it can be quite particular about the runtime environment. For example, if I fire up a command prompt, as a logged in user I can easily get a listing of disk partitions on my machine:


C:\>wmic logicaldisk get caption
Caption
C:
D:
E:
F:
G:
M:
P:
X:
Z:

It would seem logical that this would be a simple drop-in replacement for the directory listing batch file I used before. I’ll amend the batch file to list out disk partitions


run_os.bat
==========
@echo off
wmic logicaldisk get caption

and now I’ll run my query again.


SQL> select * from fs_size;

no rows selected

Hmmm….that didn’t go as well as planned Smile. Looking in the log file, there are no clues about the cause.


 LOG file opened at 03/06/19 14:33:42

KUP-05007:   Warning: Intra source concurrency disabled because the preprocessor option is being used.

Field Definitions for table FS_SIZE
  Record format DELIMITED BY NEWLINE
  Data in file has same endianness as the platform
  Rows with all null fields are accepted

  Fields in Data Source: 

    DISK                            CHAR (255)
      Terminated by ","
      Trim whitespace same as SQL Loader

The issue here is that many Windows programs require a minimum environment configuration before they will run. In this case, I need to let Windows know the location of the OS installation.


run_os.bat
==========
@echo off
set SystemRoot=C:\WINDOWS
wmic logicaldisk get caption

And voila! I get access to many more Windows command to probe out information from the Operating System.


SQL> select * from fs_size;

DISK
--------------------------------
Caption
C:
D:
E:
F:
G:
M:
P:
X:
Z:

Oracle Security Training in London with Oracle University

I have just agreed some training dates with Oracle University in London and I will be teaching my very popular two day class How to Perform a security audit of an Oracle database on the 29th and 30th April 2019....[Read More]

Posted by Pete On 07/03/19 At 12:15 PM

Connections with a wallet – redux

Wow…it is nearly 4 years ago now that I wrote an article on connecting to the database via a wallet to avoid having to hard code passwords into script. That article is here:

https://connor-mcdonald.com/2015/09/21/connection-shortcuts-with-a-wallet/

So I went to do a similar exercise on my new 18c Windows database today, and to my surprise things went pear shaped at the very first step


c:\oracle\product\18\bin>mkstore -create -wrl c:\temp\wallet
The syntax of the command is incorrect.

Not a lot of feedback there Smile

Unfortunately it’s just a small error in the script (See MOS Note 2406404.1 ). All you need to do copy the mkstore.bat from as 12.1 client and you’ll be fine. I called mine mkstore121.bat.


c:\oracle\product\18\bin>mkstore121.bat -create -wrl c:\temp\wallet
Oracle Secret Store Tool Release 18.0.0.0.0 - Production
Version 18.1.0.0.0
Copyright (c) 2004, 2017, Oracle and/or its affiliates. All rights reserved.

Enter password: *********
Enter password again: *********

Oracle 19c Data Guard sandbox created by DBCA -createDuplicateDB

Here are the commands I use to create a sandbox on Linux with a CDB1 database in a Data Guard configuration. I use the latest (19c) DBCA features to create the Primary and duplicate to the Standby.

I’m doing all in a VM which is a Compute Instance provisioned in the Oracle Cloud. In this example, I have an Oracle Linux 7.6 VM.DenseIO2.24 shape with 320GB RAM and 24 cores but remember that you will not be able to scale up/down so choose according to your credits...

I have 40GB in the / filesystem

OS and filesystem installation

I’ve installed the prerequisites as root (preinstall package, sudo and HugePages — here 200GB out of the 314GB I have):

sudo su
yum -y update
yum -y install -y oracle-database-preinstall-18c
grep "oracle" /etc/sudoers || echo "oracle  ALL=(ALL)       NOPASSWD: ALL" >> /etc/sudoers
echo "vm.nr_hugepages=102400" >> /etc/sysctl.conf
sysctl -p

I’ve attached a 8TB block device:

I create a /u01 filesystem on it for my Oracle installation

sudo iscsiadm -m node -o new -T iqn.2015-12.com.oracleiaas:a9a2...
sudo iscsiadm -m node -o update -T iqn.2015-12.com.oracleiaas:a91...
sudo iscsiadm -m node -T iqn.2015-12.com.oracleiaas:a9a21f4...
mkfs.xfs /dev/oracleoci/oraclevdb
mkdir /u01
mount /dev/oracleoci/oraclevdb /u01
echo "/dev/oracleoci/oraclevdb /u01 xfs defaults,_netdev,nofail 0 0" >> /etc/fstab
mkdir -p /u01/app/oracle/product/DB192
chown -R oracle:dba /u01

Oracle software

First, I install the Oracle Home (unzip V981623–01.zip in /u01/app/oracle/product/DB192 and run runInstaller).

su - oracle
mkdir -p /u01/oradata /u01/fast_recovery_area /u01/app/oracle
unzip -d /u01/app/oracle/product/DB192 V981623-01.zip
cat > /u01/oradata/DB192.rsp <oracle.install.responseFileVersion=/oracle/install/rspfmt_dbinstall_response_schema_v19.0.0
oracle.install.option=INSTALL_DB_SWONLY
UNIX_GROUP_NAME=oinstall
INVENTORY_LOCATION=/u01/app/oraInventory
ORACLE_BASE=/u01/app/oracle
ORACLE_HOME=/u01/app/oracle/product/DB192
oracle.install.db.InstallEdition=EE
oracle.install.db.OSDBA_GROUP=dba
oracle.install.db.OSOPER_GROUP=oper
oracle.install.db.OSBACKUPDBA_GROUP=backupdba
oracle.install.db.OSDGDBA_GROUP=dgdba
oracle.install.db.OSKMDBA_GROUP=kmdba
oracle.install.db.OSRACDBA_GROUP=dba
oracle.install.db.rootconfig.executeRootScript=true
oracle.install.db.rootconfig.configMethod=SUDO
oracle.install.db.rootconfig.sudoPath=/bin/sudo
oracle.install.db.rootconfig.sudoUserName=oracle
END
/u01/app/oracle/product/DB192/runInstaller -silent \
-responseFile /u01/oradata/DB192.rsp

You may see that sudo is not accepted without entering the oracle password, so just in case, you can run it manually:

sudo /u01/app/oracle/product/DB192/root.sh

Create the databases

I add a ‘#’ in front of the text for easy copy/paste of the whole setup.

# Set the environment and create the directories

ORACLE_HOME=/u01/app/oracle/product/DB192
mkdir -p /u01/oradata /u01/fast_recovery_area

# Divide the huge pages to fit equally 4 instances

SGA_MB=$(awk '/Hugepagesize.*kB/{s=$2} /HugePages_Total/ {f=$2} END{print s*f/1024/4} ' /proc/meminfo)

# Here is a script to create the standby logs after the creation of the primary database as this is not done by DBCA (one more than online redo logs and no multiplexing). Mention the tread# or you will have some unexpected warnings with validate database.

cat > /u01/oradata/add_standby_log.sql <<'SQL'
exec for i in (select t,g+rownum g,s from (select thread# t,max(bytes) s,count(*)+1 c from v$log group by thread#),(select rownum n from xmltable('1 to 100')),(select max(group#) g from v$log) where n<=c) loop execute immediate 'alter database add standby logfile thread '||i.t||' group '||i.g||' size '||i.s; end loop;
exit
SQL

# Primary database creation

grep ^CDB1A: /etc/oratab || $ORACLE_HOME/bin/dbca -silent \
-createDatabase -gdbName CDB1 -sid CDB1A \
-createAsContainerDatabase true -numberOfPdbs 1 -pdbName PDB1 \
-sysPassword oracle -systemPassword oracle \
-pdbAdminPassword oracle \
-datafileDestination /u01/oradata -useOMF true -storageType FS \
-recoveryAreaDestination /u01/fast_recovery_area \
-recoveryAreaSize 10240 -enableArchive true \
-memoryMgmtType AUTO_SGA -totalMemory ${SGA_MB:=2048} \
-createListener LISTENER1:1521 \
-emConfiguration EMEXPRESS -emExpressPort 5501 \
-templateName General_Purpose.dbc \
-databaseType OLTP -sampleSchema true -redoLogFileSize 100 \
-customScripts /u01/oradata/add_standby_log.sql \
-initParams \
db_unique_name=CDB1A,dg_broker_start=true,shared_pool_size=600M

# Database duplication for the standby — this is possible from DBCA in 19c:

grep ^CDB1B: /etc/oratab || $ORACLE_HOME/bin/dbca -silent \
-createDuplicateDB -gdbName CDB1 -sid CDB1B \
-createAsStandby -dbUniqueName CDB1B -sysPassword oracle \
-primaryDBConnectionString $(hostname):1521/CDB1A \
-datafileDestination /u01/oradata -useOMF true -storageType FS \
-recoveryAreaDestination /u01/fast_recovery_area \
-recoveryAreaSize 10240 -enableArchive true \
-createListener LISTENER2:1522

# fix local listener (I see no reason to resolve it in tnsnames.ora and anyway DBCA fails to update it correctly). Note that, being on the same host, I need two listeners because the same services (the PDB names) are registered by both instances.

. oraenv <<< CDB1A
ORACLE_SID=CDB1A sqlplus / as sysdba <<<"alter system set local_listener='//$(hostname):1521';"
ORACLE_SID=CDB1B sqlplus / as sysdba <<<"alter system set local_listener='//$(hostname):1522';"

# Enable flashback (I’ll use FSFO)

for i in CDB1{B,A} ; do ORACLE_SID=$i rman target / <<<'alter database flashback on;' ; done

# Listener static entries (I remove first the SID_LIST added by DBCA)

sed -ie '/^SID_LIST/,$d' $ORACLE_HOME/network/admin/listener.ora
cat >>$ORACLE_HOME/network/admin/listener.ora <SID_LIST_LISTENER1=(SID_LIST=(SID_DESC=(GLOBAL_DBNAME=CDB1A_DGMGRL)(SID_NAME=CDB1A)(ORACLE_HOME=$ORACLE_HOME)))
SID_LIST_LISTENER2=(SID_LIST=(SID_DESC=(GLOBAL_DBNAME=CDB1B_DGMGRL)(SID_NAME=CDB1B)(ORACLE_HOME=$ORACLE_HOME)))
CAT
for i in {1521..1522} ; do lsnrctl reload //$(hostname):$i ; done

# A minimal Data Guard Broker configuration that allows FSFO

cat > /u01/oradata/configure_dgb.sql <
create configuration CDB1 as primary database is CDB1A connect identifier is '//$(hostname):1521/CDB1A_DGMGRL';
add database CDB1B as connect identifier is '//$(hostname):1522/CDB1B_DGMGRL';
edit database CDB1A set property LogXptMode='SYNC';
edit database CDB1B set property LogXptMode='SYNC';
enable configuration;
edit database CDB1A set property StandbyFileManagement='AUTO';
edit database CDB1B set property StandbyFileManagement='AUTO';
edit database CDB1B set property FastStartFailoverTarget='CDB1A';
edit database CDB1A set property FastStartFailoverTarget='CDB1B';
edit configuration set protection mode as MaxAvailability;
enable fast_start failover observe only;
DGB
dgmgrl sys/oracle </u01/oradata/configure_dgb.sql

# Set ‘Y’ in oratab for dbstart (but you will have to lsnrstart LISTENER1 and LISTERNER2 first anyway because dbstart starts only the default LISTENER)

sudo sed -ie '/^CDB1[AB]:/s/:N$/:Y/' /etc/oratab

# archivelog deleted on both side without need to backup them (as long as there are no gap)

for i in CDB1{B,A} ; do ORACLE_SID=$i rman target / <<<'configure archivelog deletion policy to applied on all standby;' ; done

# Some verifications and testing two switchovers so that we know it works

for i in {1521..1522} ; do lsnrctl status //$(hostname):$i ; done
grep ^CDB1[AB] /etc/oratab
sleep 15
dgmgrl sys/oracle <<<'show configuration'
dgmgrl sys/oracle <<<'show database verbose CDB1B'
sleep 15
dgmgrl sys/oracle <<<'validate database CDB1B' | grep -EC99 "Ready for Switchover: *Yes" && dgmgrl sys/oracle <<<'switchover to CDB1B'
sleep 15
dgmgrl sys/oracle <<<'validate database CDB1A' | grep -EC99 "Ready for Switchover: *Yes" && dgmgrl sys/oracle <<<'switchover to CDB1A'

# dgmgrl can take a command as argument, but I like bash Here strings.

Drop the databases

The following will clean everything (drop databases CDB1A and CDB1B and listeners)

exit # this to prevent and eager copy/paste
ORACLE_HOME=/u01/app/oracle/product/DB192
mkdir -p /u01/oradata /u01/fast_recovery_area
for i in $(awk -F: '/^CDB1[A-B]:/{print $1}' /etc/oratab | sort -r)
do
$ORACLE_HOME/bin/dbca -silent -deleteDatabase -sourceDB $i \
-forceArchiveLogDeletion -sysDBAPassword oracle -sysDBAUserName sys
done
for i in {1521..1522} ; do lsnrctl stop //$(hostname):$i ; done
rm -rf /u01/oradata/CDB1* \
/u01/fast_recovery_area/CDB1* \
$ORACLE_HOME/network/admin/{listener,tnsnames,sqlnet}.ora \
/u01/app/oracle/cfgtoollogs/dbca/CDB1{A,B} \
/u01/app/oracle/diag/rdbms/*/CDB1{A,B} \
$ORACLE_HOME/dbs/*{A,B}*\
$ORACLE_HOME/rdbms/log/*{A,B}*\
$ORACLE_HOME/rdbms/audit/*{A,B}*

I do the same with a CDB2 database and that’s my sandbox for many demos.

In summary, DBCA automates the duplicate for standby. It would be nice to get the listeners created properly with static strings, and the Data Guard Broker configuration setup as well. And also standby redo log creation.

19c Observe-Only Data Guard FSFO: no split-brain risk in manual failover

Fast-Start Failover (FSFO) is an amazing feature of Oracle Data Guard Broker which brings High Availability (HA)features in addition to the Disaster Recovery (DR) one.

Data Guard as an HA solution

By default, a physical standby database protects from Disaster Recovery (like when your Data Center is on fire or underwater, or with a power cut,…). But it requires a manual action to do the failover. Then, even if the failover is quick (seconds to minutes) and there’s no loss of data (if in SYNC), it cannot be considered as HA because of the manual decision which can take hours. The idea of the manual decision is to understand the cause as it may be better to just wait in case of a transient failure. Especially if the standby site is less powerful and application performance will be degraded.

With FSFO, the failure of the primary database is automatically detected (with an observer process constantly testing the connection from another site) and then the failover to a designated standby is initiated. If Transparent Application Failover (TAF) is correctly configured, the application will directly reconnect to the new primary without your intervention.

How does the observer decide that the primary is down? By default, the failover is triggered when the primary is not reachable by both the observer and by the standby. In 12c, thanks to the ObserverOverride property, it is also possible to get the observer issuing the failover as soon as it cannot connect to the primary, even when the standby can see the primary. This can be used when the observer is on the application side.

12cR2 brought new additional possibilities for complex Data Guard configurations, such as defining multiple observers and multiple targets.

No human decision?

However, FSFO is faster than a manual decision but may initiate a failover when we do not want it. If the observer is on the same site as the primary (bad idea) then a network connection between the two sites will trigger the failover. When running in FSFO you must be sure that all your infrastructure is ok so that no undesired failover is initiated, and that you have no manual tasks to do in order to get the applications running again.

Enable FSFO in observe-only mode

If it is not the case, you will not decide to automate the failover. This is where 19c ‘observe only’ mode is interesting. The observer will report the failure but will not initiate a failover. I enable FSFO in this mode:

[oracle@cloud ~]$ dgmgrl / "enable fast_start failover observe only"
DGMGRL for Linux: Release 19.0.0.0.0 - Production on Wed Mar 6 10:02:01 2019
Version 19.2.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.
Welcome to DGMGRL, type "help" for information.
Connected to "CDB1A"
Connected as SYSDG.
Enabled in Observe-Only Mode.

And start the observer (you should use the full syntax to run it in background with a log directory, but this is just for a small test which logs on my screen):

[oracle@cloud ~]$ dgmgrl sys/oracle "start observer" &
[1] 26589
[oracle@cloud ~]$ DGMGRL for Linux: Release 19.0.0.0.0 - Production on Wed Mar 6 10:04:50 2019
Version 19.2.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.
Welcome to DGMGRL, type "help" for information.
Connected to "CDB1A"
Connected as SYSDBA.
Observer 'cloud' started
[W000 2019-03-06T10:04:50.342+00:00] Observer trace level is set to USER
[W000 2019-03-06T10:04:50.342+00:00] Try to connect to the primary.

Now if I simulate a crash of the primary database, killing the PMON process:

[oracle@cloud ~]$ kill -9 $(pgrep -f ora_pmon_CDB1A)

This immediately logs the following from the observer:

[oracle@cloud ~]$ [W000 2019-03-06T10:37:50.198+00:00] Primary database cannot be reached.
[W000 2019-03-06T10:37:50.198+00:00] Fast-Start Failover threshold has not exceeded. Retry for the next 30 seconds

The default 30 seconds threshold is there to avoid a failover decision in case of a transient network failure.

After this threshold, a fast-start failover would have been initiated in normal FSFO mode, but here it is only reported in the log:


[W000 2019-03-06T10:37:51.198+00:00] Try to connect to the primary.
ORA-12537: TNS:connection closed
Unable to connect to database using //cloud:1521/CDB1A_DGMGRL
[W000 2019-03-06T10:37:51.235+00:00] Primary database cannot be reached.
[W000 2019-03-06T10:37:52.235+00:00] Try to connect to the primary.
[W000 2019-03-06T10:37:53.287+00:00] Primary database cannot be reached.
[W000 2019-03-06T10:37:54.288+00:00] Try to connect to the primary.
[W000 2019-03-06T10:38:17.902+00:00] Primary database cannot be reached.
[W000 2019-03-06T10:38:17.902+00:00] Fast-Start Failover threshold has not exceeded. Retry for the next 3 seconds
[W000 2019-03-06T10:38:18.902+00:00] Try to connect to the primary.
[W000 2019-03-06T10:38:19.952+00:00] Primary database cannot be reached.
[W000 2019-03-06T10:38:19.952+00:00] Fast-Start Failover threshold has not exceeded. Retry for the next 1 second
[W000 2019-03-06T10:38:20.952+00:00] Try to connect to the primary.
[W000 2019-03-06T10:38:22.003+00:00] Primary database cannot be reached.
[W000 2019-03-06T10:38:22.003+00:00] Fast-Start Failover threshold has expired.
[W000 2019-03-06T10:38:22.003+00:00] Try to connect to the standby.
[W000 2019-03-06T10:38:22.003+00:00] Making a last connection attempt to primary database before proceeding with Fast-Start Failover.
[W000 2019-03-06T10:38:22.003+00:00] Check if the standby is ready for failover.
[W000 2019-03-06T10:38:22.006+00:00] A fast-start failover would have been initiated...
[W000 2019-03-06T10:38:22.006+00:00] Unable to failover since this observer is in observe-only mode
[W000 2019-03-06T10:38:22.006+00:00] Fast-Start Failover is not possible because observe-only mode.
[W000 2019-03-06T10:38:23.007+00:00] Try to connect to the primary.
[W000 2019-03-06T10:38:24.058+00:00] Primary database cannot be reached.
[W000 2019-03-06T10:38:25.058+00:00] Try to connect to the primary.
[W000 2019-03-06T10:38:26.110+00:00] Primary database cannot be reached.
[W000 2019-03-06T10:38:27.110+00:00] Try to connect to the primary.
[W000 2019-03-06T10:38:50.727+00:00] Primary database cannot be reached.
[W000 2019-03-06T10:38:50.727+00:00] Fast-Start Failover threshold has not exceeded. Retry for the next 3 seconds
[W000 2019-03-06T10:38:51.727+00:00] Try to connect to the primary.
[W000 2019-03-06T10:38:52.777+00:00] Primary database cannot be reached.
[W000 2019-03-06T10:38:52.777+00:00] Fast-Start Failover threshold has not exceeded. Retry for the next 1 second
...

Of course, this is something you should monitor, and have a manual decision about it. This is useful to run the FSFO in ‘dry-run’ mode where you want to be sure that your infrastructure is ok, without false alerts, before having it fully automated. But even when you don’t want to go to fully automated FSFO, this mode is very helpful for the post-failover tasks.

Let’s say I decide to failover. I do not stop the observer. I do not disable FSFO. I just initiate the failover manually:

DGMGRL> failover to cdb1b;
Failover succeeded, new primary is "cdb1b"

The observer is still running and detects that the primary has changed:

[W000 2019-03-06T13:13:25.182+00:00] Primary database cannot be reached.
[W000 2019-03-06T13:13:25.182+00:00] Fast-Start Failover threshold has not exceeded. Retry for the next 2 seconds
[W000 2019-03-06T13:13:26.183+00:00] Try to connect to the primary.
[W000 2019-03-06T13:13:27.233+00:00] Primary database cannot be reached.
[W000 2019-03-06T13:13:27.233+00:00] Fast-Start Failover threshold has expired.
[W000 2019-03-06T13:13:27.233+00:00] Try to connect to the standby.
[W000 2019-03-06T13:13:27.233+00:00] Making a last connection attempt to primary database before proceeding with Fast-Start Failover.
[W000 2019-03-06T13:13:27.280+00:00] Check if the standby is ready for failover.
[W000 2019-03-06T13:13:27.285+00:00] A fast-start failover would have been initiated...
[W000 2019-03-06T13:13:27.285+00:00] Unable to failover since this observer is in observe-only mode
[W000 2019-03-06T13:13:27.285+00:00] Fast-Start Failover is not possible because observe-only mode.
[W000 2019-03-06T13:13:28.284+00:00] Try to connect to the primary.
[W000 2019-03-06T13:13:28.284+00:00] Primary database cannot be reached.
[W000 2019-03-06T13:13:28.284+00:00] Fast-Start Failover observe-only mode enabled.
[W000 2019-03-06T13:13:28.284+00:00] Will not attempt a Fast-Start Failover.
[W000 2019-03-06T13:13:28.284+00:00] Retry connecting to primary.
[W000 2019-03-06T13:13:29.284+00:00] Try to connect to the primary.
[W000 2019-03-06T13:13:30.285+00:00] Primary database has changed to cdb1b.
[W000 2019-03-06T13:13:30.337+00:00] Try to connect to the primary.
[W000 2019-03-06T13:13:30.337+00:00] Try to connect to the primary //instance-20190305-2110:1522/CDB1B_DGMGRL.
[W000 2019-03-06T13:13:30.380+00:00] The standby cdb1a needs to be reinstated
[W000 2019-03-06T13:13:30.380+00:00] Try to connect to the new standby cdb1a.
[W000 2019-03-06T13:13:30.380+00:00] Connection to the primary restored!
[W000 2019-03-06T13:13:32.380+00:00] Connection to the new standby restored!
[W000 2019-03-06T13:13:32.380+00:00] Disconnecting from database //instance-20190305-2110:1522/CDB1B_DGMGRL.
[W000 2019-03-06T13:13:33.384+00:00] Failed to ping the new standby.
[W000 2019-03-06T13:13:34.385+00:00] Try to connect to the new standby cdb1a.
[W000 2019-03-06T13:13:36.385+00:00] Connection to the new standby restored!
[W000 2019-03-06T13:13:36.388+00:00] Failed to ping the new standby.
[W000 2019-03-06T13:13:37.389+00:00] Try to connect to the new standby cdb1a.

Reinstate old primary as new standby

This is the other automation of FSFO: as soon as the old site comes up again, this situation is detected. It is mandatory in the fully automated failover because you don’t want the old desynchronized primary to come up and have applications connecting to it again, which is a case of split-brain. FSFO detects this and can even automatically reinstate this old primary as a new standby (this is why FSFO requires the database to be in FLASHBACK ON).

Let’s see it, I Startup CDB1A — the one I killed before

ORACLE instance started.
Total System Global Area 4.2950E+10 bytes
Fixed Size 30386848 bytes
Variable Size 8187281408 bytes
Database Buffers 3.4628E+10 bytes
Redo Buffers 103829504 bytes
Database mounted.
ORA-16649: possible failover to another database prevents this database from being opened

Impossible to open, to prevent against split brain.

And then I do nothing, just let the observer do its magic:

SQL> [W000 2019-03-06T13:17:32.728+00:00] Try to connect to the primary //instance-20190305-2110:1522/CDB1B_DGMGRL.
[W000 2019-03-06T13:17:33.728+00:00] Connection to the primary restored!
[W000 2019-03-06T13:17:33.728+00:00] Wait for new primary to be ready to reinstate.
[W000 2019-03-06T13:17:33.731+00:00] New primary is now ready to reinstate.
[W000 2019-03-06T13:17:34.732+00:00] Issuing REINSTATE command.
2019-03-06T13:17:34.732+00:00
Initiating reinstatement for database "cdb1a"...
Reinstating database "cdb1a", please wait...
[W000 2019-03-06T13:17:48.752+00:00] The standby cdb1a is ready to be a FSFO target
Reinstatement of database "cdb1a" succeeded
2019-03-06T13:18:17.381+00:00
[W000 2019-03-06T13:18:17.789+00:00] Successfully reinstated database cdb1a.

Now the old primary is up in a standby role, fully synchronized. This is awesome. Just imagine the following situation. You have a few hours of power cut. You decided to failover. This is one command only with Data Guard Broker, but you have probably a lot of work to do with all the other systems.

And then you will postpone the reinstate of the primary databases. And maybe you will forget it after that exhausting day. And then you run unprotected… Now, Murphy’s law breaks the DR site… and you have lost everything. If you run FSFO, then the old site has been synchronized again without your intervention and is ready for a new failover. And in 19c this is possible even if you want full control with the manual decision to failover.

12c Snapshots

I published a note a few years ago about using the 12c “with function” mechanism for writing simple SQL statements to takes deltas of dynamic performance views. The example I supplied was for v$event_histogram but I’ve just been prompted by a question on ODC to supply a couple more – v$session_event and v$sesstat (joined to v$statname) so that you can use one session to get an idea of the work done and time spent by another session – the first script reports wait time:


rem
rem     Program:        12c_with_function_2.sql
rem     Dated:          July 2013
rem
rem     See also
rem     12c_with_function.sql
rem     https://jonathanlewis.wordpress.com/2013/06/30/12c-fun/
rem
rem     Notes:
rem             Reports session WAIT time
rem             Modify the list of SIDs of interest
rem             Set the time in seconds
rem

define m_snap_time = 60
define m_sid_list  = '3, 4, 121, 127'

set timing on
set sqlterminator off

set linesize 180

break on sid skip 1

with
        function wait_row (
                i_secs  number, 
                i_return        number
        ) return number
        is
        begin
                dbms_lock.sleep(i_secs);
                return i_return;
        end;
select
        sid, 
        sum(total_waits),
        sum(total_timeouts), 
        sum(time_waited), 
        event
from    (
        select
                sid, event_id, 
                -total_waits total_waits, 
                -total_timeouts total_timeouts, 
                -time_waited time_waited, 
                -time_waited_micro time_waited_micro, 
                event
        from    v$session_event
        where   sid in ( &m_sid_list )
        union all
        select
                null, null, null, null, null, wait_row(&m_snap_time, 0), null
        from    dual
        union all
        select
                sid, event_id, total_waits, total_timeouts, time_waited, time_waited_micro, event
        from    v$session_event
        where   sid in ( &m_sid_list )
        )
where
        time_waited_micro != 0
group by
        sid, event_id, event
having
        sum(time_waited) != 0
order by
        sid, sum(time_waited) desc
/


And this one reports session activity:

rem
rem     Program:        12c_with_function_3.sql
rem     Dated:          July 2013
rem
rem     See also
rem     12c_with_function.sql
rem     https://jonathanlewis.wordpress.com/2013/06/30/12c-fun/
rem
rem     Notes:
rem             Reports session stats
rem             Modify the list of SIDs of interest
rem             Set the time in seconds
rem

define m_snap_time = 60
define m_sid_list  = '3, 4, 13, 357'


set timing on
set sqlterminator off

set linesize 180

break on sid skip 1
column name format a64

with
        function wait_row (
                i_secs  number, 
                i_return        number
        ) return number
        is
        begin
                dbms_lock.sleep(i_secs);
                return i_return;
        end;
select
        sid, 
        name,
        sum(value)
from    (
        select
                ss.sid, 
                ss.statistic#,
                sn.name,
                -ss.value value
        from
                v$sesstat       ss,
                v$statname      sn
        where   ss.sid in ( &m_sid_list )
        and     sn.statistic# = ss.statistic#
        union all
        select
                null, null, null, wait_row(&m_snap_time, 0)
        from    dual
        union all
        select
                ss.sid, ss.statistic#, sn.name, ss.value value
        from
                v$sesstat       ss,
                v$statname      sn
        where   ss.sid in ( &m_sid_list )
        and     sn.statistic# = ss.statistic#
        )
where
        value != 0
group by
        sid, statistic#, name
having
        sum(value) != 0
order by
        sid, statistic#
/


You’ll notice that I’ve used dbms_lock.sleep() in my wait function – and the session running the SQL can be granted the execute privilege on the package through a role to make this work – but if you’re running Oracle 18 then you’ve probably noticed that the sleep() function and procedure have been copied to the dbms_session package.

 

Getting locale warnings when logging on to Linux

This blogpost is about the reason and solving getting the following message, or messages alike these when logging i to a linux box using ssh:

-bash: warning: setlocale: LC_CTYPE: cannot change locale (UTF-8): No such file or directory

However, this is a warning. Please mind such an issue might be come up in another way, which can be more disrupting; at least in the past I had issues running perl for the same issue:

[root@dmcel01 ~]# /usr/local/bin/ipconf -verify
perl: warning: Setting locale failed.
perl: warning: Please check that your locale settings:
LANGUAGE = "en_US.UTF-8",
LC_ALL = "UTF-8",
LC_CTYPE = "UTF-8",
LANG = "en_US.UTF-8"
are supported and installed on your system.
perl: warning: Falling back to the standard locale ("C").

This is a utility that comes with exadata, and I needed it during patching, but it didn’t execute because of it. This was more than 4 years ago, and I actually created a SR for it. But because I was patching during a downtime window, I had to quickly solve it. I managed to find it, and reported it back to Oracle, which was turned into a MOS note (however, as Oracle seems to have a habit of) without proper attribution to me, and a bit wrong. You can see the note here (MOS account required) Running /Usr/Local/Bin/Ipconf -Verify Results In ‘Perl: Warning: Setting Locale Failed.’ (Doc ID 1393517.1)

Anyway, today I ran into this again. The issue is the CLIENT (the machine from which an ssh session is created) explicitly requested locale settings on the machine it connected to. This is done in the client’s global ssh settings in a file called ssh_config.

I should add I use MacOS/OSX, which is derived from BSD, which is unix/linux like.

The file ssh_config was found in /etc/ssh (/etc/ssh/ssh_config), but at least on 10.14.3 (Mojave) this has changed to /private/etc/ssh/ssh_config. The MOS note describes /etc/ssh_config, but I don’t think ssh_config is present in /etc directly in any OS by default, and probably mostly found in /etc/ssh.

In my case, after updating to OSX Mojave, the ssh_config file is overwritten, giving me back the locale issue. This is how the original default host settings look like:

Host *
SendEnv LANG LC_*

To solve the locale issues (again, for me), change it very simply to:

Host *
SendEnv LANG

Disabled EZCONNECT

Just a normal start to the day today…I had my coffee

coffee_gif

and then started working on some AskTOM questions. Naturally pretty much the first thing I needed to do is connect to my database, and then this happened:


C:\oracle\product\18\bin>sqlplus scott/tiger@//gtx:1518/pdb1

SQL*Plus: Release 18.0.0.0.0 - Production on Wed Mar 6 09:23:09 2019
Version 18.5.0.0.0

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

ERROR:
ORA-12154: TNS:could not resolve the connect identifier specified

I made the standard assumptions

  • I had set ORACLE_HOME wrong….checked that, nope!
  • I had my TNS_ADMIN set wrong….nope!
  • Listener not configured corrrectly….nope!
  • Database not started or not registered with listener….nope!
  • Had my good friends at Microsoft restarted the PC with Windows Update without telling me?…nope!

so once I’d ticked off the normal culprits, I then tried with other software installations on the same machine


C:\oracle\instantclient>sqlplus scott/tiger@//gtx:1518/pdb1

SQL*Plus: Release 12.1.0.2.0 Production on Wed Mar 6 09:21:44 2019

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

Last Successful login time: Wed Mar 06 2019 09:13:31 +08:00

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

C:\oracle\product\xe\18\dbhomeXE\bin>sqlplus scott/tiger@//gtx:1518/pdb1

SQL*Plus: Release 18.0.0.0.0 - Production on Wed Mar 6 09:22:38 2019
Version 18.4.0.0.0

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

Last Successful login time: Wed Mar 06 2019 09:21:44 +08:00

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

and voila! Obviously everything is OK from the listener and database perspective.

So back to the original installation, and I had the light bulb moment. I had been tinkering with sqlnet.ora to help out with a different question yesterday, and I had isolated the connection options down to only TNSNAMES


NAMES.DIRECTORY_PATH= (TNSNAMES)

In such a scenario, you need to explicitly allow for EZCONNECT method to allow the connection to work.


NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

and then everything was fine back in the original installation directory.


C:\oracle\product\18\bin>sqlplus scott/tiger@//gtx:1518/pdb1

SQL*Plus: Release 18.0.0.0.0 - Production on Wed Mar 6 09:50:52 2019
Version 18.5.0.0.0

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

Last Successful login time: Wed Mar 06 2019 09:45:06 +08:00

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

If you have never touched your sqlnet.ora, it probably looks like this:


NAMES.DIRECTORY_PATH= (TNSNAMES, ONAMES, HOSTNAME)

but there is no need to panic. Those default values allow EZCONNECT as well.

My next Conferences in 2019

In my 2019 talks, a lot of performance stuff for DBA and Developers:

This year started with the amazing OBUG Belgium Tech Days 2019

The co-location of data and code, in present and future (like the MLE engine running JavaScript or Python in the database)

The most relevant statistics gathering in the 12.2 database (from 12cR2, 18c, 19c)

A different view on Join Methods by tracing the internal functions.

The Riga Dev Days in Latvia:

Riga Dev Days | Tech conference and the annual meeting point for developers in Baltic States.

Where I talk about microservices, and data/code co-location

Riga, 31.05.2019, 11:00–12:00
(Microservices: Get Rid of Your DBA and Send the DB into Burnout)

And the major conference about Oracle Performance:

Performance Days: Where ORACLE Database Specialists meet

Where I talk on statistic gathering best practices and new features (19c) and I explain join methods and optimization with a full demo session

Zurich, 27.09. 2019, 15:20–16:40
Join: Methods and Optimization
Zurich, 27.09. 2019, 11:00–12:20
Do You Gather the Statistics in the Way the Optimizer Expects Them?

I will add more of course and do not hesitate to follow me on Twitter (@FranckPachot) for news, comments, and questions

Cartesian Join

I wrote this note a little over 4 years ago (Jan 2015) but failed to publish it for some reason. I’ve just rediscovered it and it’s got a couple of details that are worth mentioning, so I’ve decided to go ahead and publish it now.

A recent [ed: 4 year old] question on the OTN SQL forum asked for help in “multiplying up” data – producing multiple rows from a single row source. This is something I’ve done fairly often when modelling a problem, for example by generating an orders table and then generating an order_lines table from the orders table, and there are a couple of traps to consider.

The problem the OP had was that their base data was the result set from a complex query – which ran “fine”, but took 10 minutes to complete when a Cartesian join to a table holding just three rows was included. Unfortunately the OP didn’t supply, or even comment on, the execution plans. The obvious guess, of course, is that the extra table resulted in a completely different execution plan rather than the expected “do the original query then multiply by 3” plan, in which case the solution to the problem is (probably) simple – stick the original query into a non-mergeable view before doing the join.

Assume we have the following tables, t1 has 100,000 rows (generated from the SQL in this article), t2 has 4 rows where column id2 has the values from 1 to 4, t3 is empty – we can model the basic requirement with the query shown below:


SQL> desc t1
 Name                    Null?    Type
 ----------------------- -------- ----------------
 ID                               NUMBER
 C1                               CHAR(2)
 C2                               CHAR(2)
 C3                               CHAR(2)
 C4                               CHAR(2)
 PADDING                          VARCHAR2(100)

SQL> desc t2
 Name                    Null?    Type
 ----------------------- -------- ----------------
 ID2                              NUMBER

SQL> desc t3
 Name                    Null?    Type
 ----------------------- -------- ----------------
 ID                               NUMBER
 ID2                              NUMBER
 C1                               CHAR(2)
 C2                               CHAR(2)
 C3                               CHAR(2)
 C4                               CHAR(2)
 PADDING                          VARCHAR2(100)


insert into t3
select
        t1.id, t2.id2, t1.c1, t1.c2, c3, t1.c4, t1.padding
from
       (select * from t1) t1,
        t2
;

If we “lose” the plan for the original “select * from t1” (assuming t1 was really a complicated view) when we extend to the Cartesian join all we need to do is the following:


insert into t3
select
        /*+ leading(t1 t2) */
        t1.id, t2.id2, t1.c1, t1.c2, c3, t1.c4, t1.padding
from
        (select /*+ no_merge */ * from t1) t1,
        t2
;

This is where the problem starts to get a little interesting. The /*+ no_merge */ hint is (usually) a winner in situations like this – but why have I included a /*+ leading() */ hint choosing to put t2 (the small table) second in the join order? It’s because of the way that Cartesian Merge Joins work, combined with an eye to where my most important resource bottleneck is likely to be. Here’s the execution plan taken from memory after executing this statement with statistics_level set to all. (11.2.0.4):


SQL_ID  azu8ntfjg9pwj, child number 0
-------------------------------------
insert into t3 select   /*+ leading(t1 t2) */  t1.id, t2.id2, t1.c1,
t1.c2, c3, t1.c4, t1.padding from  (select /*+ no_merge */ * from t1)
t1,   t2

Plan hash value: 1055157753

----------------------------------------------------------------------------------------------------------------------
| Id  | Operation                | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT         |      |      1 |        |      0 |00:00:10.28 |   48255 |       |       |          |
|   1 |  LOAD TABLE CONVENTIONAL |      |      1 |        |      0 |00:00:10.28 |   48255 |       |       |          |
|   2 |   MERGE JOIN CARTESIAN   |      |      1 |    400K|    400K|00:00:06.30 |    1727 |       |       |          |
|   3 |    VIEW                  |      |      1 |    100K|    100K|00:00:00.94 |    1725 |       |       |          |
|   4 |     TABLE ACCESS FULL    | T1   |      1 |    100K|    100K|00:00:00.38 |    1725 |       |       |          |
|   5 |    BUFFER SORT           |      |    100K|      4 |    400K|00:00:01.78 |       2 |  3072 |  3072 | 2048  (0)|
|   6 |     TABLE ACCESS FULL    | T2   |      1 |      4 |      4 |00:00:00.01 |       2 |       |       |          |
----------------------------------------------------------------------------------------------------------------------

Let’s try that again (building from scratch, of course) with the table order reversed in the leading() hint:


SQL_ID  52qaydutssvn5, child number 0
-------------------------------------
insert into t3 select   /*+ leading(t2 t1) */  t1.id, t2.id2, t1.c1,
t1.c2, c3, t1.c4, t1.padding from  (select /*+ no_merge */ * from t1)
t1,   t2

Plan hash value: 2126214450

----------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  | Writes |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT         |      |      1 |        |      0 |00:00:12.29 |   48311 |   6352 |   1588 |       |       |          |
|   1 |  LOAD TABLE CONVENTIONAL |      |      1 |        |      0 |00:00:12.29 |   48311 |   6352 |   1588 |       |       |          |
|   2 |   MERGE JOIN CARTESIAN   |      |      1 |    400K|    400K|00:00:06.64 |    1729 |   6352 |   1588 |       |       |          |
|   3 |    TABLE ACCESS FULL     | T2   |      1 |      4 |      4 |00:00:00.01 |       2 |      0 |      0 |       |       |          |
|   4 |    BUFFER SORT           |      |      4 |    100K|    400K|00:00:04.45 |    1727 |   6352 |   1588 |    13M|  1416K| 9244K (0)|
|   5 |     VIEW                 |      |      1 |    100K|    100K|00:00:00.80 |    1725 |      0 |      0 |       |       |          |
|   6 |      TABLE ACCESS FULL   | T1   |      1 |    100K|    100K|00:00:00.28 |    1725 |      0 |      0 |       |       |          |
----------------------------------------------------------------------------------------------------------------------------------------

There’s one important detail that’s not explicit in the execution plans – I’ve set the workarea_size_policy to manual and the sort_area_size to 10MB to demonstrate the impact of having a dataset that is too large for the session’s workarea limit.

The results, in terms of timing, are border-line. With the “correct” choice of order the completion time is 10.28 seconds compared to 12.29 seconds, though if you look at the time for the Merge Join Cartesian operation the difference is much less significant. The critical point, though, appears at operation 4 – the Buffer Sort. I set my sort_area_size to something that I know was smaller than the data set I needed to buffer – so the operation had to spill to disc. Ignoring overheads and rounding errors the data from the 1,727 blocks I read from the table at pctfree = 10 were dumped to the temporary space in 1,588 packed blocks (sanity check: 1,727 * 0.9 = 1,554); and then those blocks were read back once for each row from the driving t2 table (sanity check: 1,588 * 4 = 6,352).

With my setup I had a choice of bottlenecks:  scan a very small data set in memory 100,000 times to burn CPU, or scan a large data set from disc 4 times. There wasn’t much difference in my case: but the difference could be significant on a full-scale production system.  By default the optimizer happened to pick the “wrong” path with my data sets.

But there’s something even more important than this difference in resource usage to generate the data: what does the data look like after it’s been generated.  Here’s a simple query to show you the first few rows of the stored result sets in the two different test:


SQL> select id, id2, c1, c2, c3, c4 from t3 where rownum <= 8;

Data from leading (t1 t2)
=========================
        ID        ID2 C1 C2 C3 C4
---------- ---------- -- -- -- --
         1          1 BV GF JB LY
         1          2 BV GF JB LY
         1          3 BV GF JB LY
         1          4 BV GF JB LY
         2          1 YV LH MT VM
         2          2 YV LH MT VM
         2          3 YV LH MT VM
         2          4 YV LH MT VM


Data from leading (t2 t1)
=========================
        ID        ID2 C1 C2 C3 C4
---------- ---------- -- -- -- --
         1          1 BV GF JB LY
         2          1 YV LH MT VM
         3          1 IE YE TS DP
         4          1 DA JY GS AW
         5          1 ZA DC KD CF
         6          1 VJ JI TC RI
         7          1 DN RY KC BE
         8          1 WP EQ UM VY

If we had been using code like this to generate an order_lines table from an orders table, with  leading(orders t2) we would have “order lines” clustered around the “order number” – which is a realistic model; when we have leading(t2 orders) the clustering disappears (technically the order numbers are clustered around the order lines). It’s this aspect of the data that might have a much more important impact on the suitability (and timing) of any testing you may be doing rather than a little time lost or gained in generating the raw data.

Footnote

If you try to repeat this test on your own system don’t expect my timing to match yours. Bear in mind, also, that with statistics_level set to all there’s going to be a CPU overhead that varies between the two options for the leading() hint – the CPU usage on rowsource execution stats could be much higher for the case where one of the operations starts 100,000 times.