Event incident

In a recent thread on oracle-l, Mikhail Velikikh has pointed me to an interesting but scarcely documented Oracle feature. The only documentation available is in oradebug:

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> oradebug doc event action incident
incident 
	- Create an Incident
Usage
-------
incident( label 	  <string>[32])
This is not much of a documentation, but the usage is extremely interesting so I’ll give my best to shed some light on it.
SQL> alter system set events='942 incident("Table Missing")';

System altered.

So,  the syntax is extremely simple. What does that do for me? OK, let’s try it out:

[oracle@ora122 admin]$ sql system/qwerty@pdbtest

SQLcl: Release 12.2.0.1.0 RC on Sat Aug 24 11:13:23 2019

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

Last Successful login time: Sat Aug 24 2019 11:13:23 -04:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> alter system set events='942 incident("Missing Table")';
System SET altered.
SQL> 
SQL> connect scott/tiger@pdbtest
Connected.
SQL> select * from xxx;

Error starting at line : 1 in command -
select * from xxx
Error at Command Line : 1 Column : 15
Error report -
SQL Error: ORA-00942: table or view does not exist
00942. 00000 -  "table or view does not exist"
*Cause:    
*Action:
SQL>

This is completely standard error message.  However, here is where the things get interesting: the error is now visible in the ADRCI utility. The string used as an argument to the event option is used as a tag in the ADRCI utility:

adrci> show incident

ADR Home = /oracle/diag/rdbms/o122/o122:
*************************************************************************
INCIDENT_ID PROBLEM_KEY CREATE_TIME 
-------------------- ----------------------------------------------------------- ---------------------------------------- 
24065 ORA 603 2018-12-04 21:06:29.676000 -05:00 
38689 ORA 603 2019-01-06 13:52:33.003000 -05:00 
96065 ORA 603 2019-03-08 09:38:01.810000 -05:00 
298073 ORA 600 [17090] 2019-08-24 11:11:15.478000 -04:00 
298121 ORA 700 [EVENT_CREATED_INCIDENT] [0] [Missing Table] 2019-08-24 11:15:04.857000 -04:00 
298122 ORA 700 [EVENT_CREATED_INCIDENT] [942] [Missing Table] 2019-08-24 11:15:05.433000 -04:00 
298123 ORA 700 [EVENT_CREATED_INCIDENT] [0] [Missing Table] 2019-08-24 11:15:06.017000 -04:00 
298124 ORA 700 [EVENT_CREATED_INCIDENT] [942] [Missing Table] 2019-08-24 11:15:06.726000 -04:00 
8 rows fetched

The incident can also be searched by the tag, which is stored in ERROR_ARG3 column of the INCIDENT table:

adrci> show incident -p "error_arg3='Missing Table'"

ADR Home = /oracle/diag/rdbms/o122/o122:
*************************************************************************
INCIDENT_ID          PROBLEM_KEY                                                 CREATE_TIME                              
-------------------- ----------------------------------------------------------- ---------------------------------------- 
298121               ORA 700 [EVENT_CREATED_INCIDENT] [0] [Missing Table]        2019-08-24 11:15:04.857000 -04:00       
298122               ORA 700 [EVENT_CREATED_INCIDENT] [942] [Missing Table]      2019-08-24 11:15:05.433000 -04:00       
298123               ORA 700 [EVENT_CREATED_INCIDENT] [0] [Missing Table]        2019-08-24 11:15:06.017000 -04:00       
298124               ORA 700 [EVENT_CREATED_INCIDENT] [942] [Missing Table]      2019-08-24 11:15:06.726000 -04:00       

ADR Home = /oracle/diag/clients/user_oracle/host_2384743142_107:
*************************************************************************
0 rows fetched

Not only that,  the error can now be examined using the “show incident -mode detail” command:

adrci> show incident -mode detail -p "incident_id=298124"

ADR Home = /oracle/diag/rdbms/o122/o122:
*************************************************************************

**********************************************************
INCIDENT INFO RECORD 1
**********************************************************
INCIDENT_ID 298124
STATUS ready
CREATE_TIME 2019-08-24 11:15:06.726000 -04:00
PROBLEM_ID 4
CLOSE_TIME <NULL>
FLOOD_CONTROLLED none
ERROR_FACILITY ORA
ERROR_NUMBER 700
ERROR_ARG1 EVENT_CREATED_INCIDENT
ERROR_ARG2 942
ERROR_ARG3 Missing Table
ERROR_ARG4 <NULL>
ERROR_ARG5 <NULL>
ERROR_ARG6 <NULL>
ERROR_ARG7 <NULL>
ERROR_ARG8 <NULL>
ERROR_ARG9 <NULL>
ERROR_ARG10 <NULL>
ERROR_ARG11 <NULL>
ERROR_ARG12 <NULL>
SIGNALLING_COMPONENT rdbms_event
SIGNALLING_SUBCOMPONENT <NULL>
SUSPECT_COMPONENT <NULL>
SUSPECT_SUBCOMPONENT <NULL>
ECID <NULL>
IMPACTS 0
CON_UID 197631990
PROBLEM_KEY ORA 700 [EVENT_CREATED_INCIDENT] [942] [Missing Table]
FIRST_INCIDENT 298122
FIRSTINC_TIME 2019-08-24 11:15:05.433000 -04:00
LAST_INCIDENT 298124
LASTINC_TIME 2019-08-24 11:15:06.726000 -04:00
IMPACT1 0
IMPACT2 0
IMPACT3 0
IMPACT4 0
KEY_NAME Client ProcId
KEY_VALUE oracle@ora122 (TNS V1-V3).5985_140554998153024
KEY_NAME Module
KEY_VALUE java@ora122 (TNS V1-V3)
KEY_NAME Service
KEY_VALUE pdbtest.home.com
KEY_NAME PdbName
KEY_VALUE PDBTEST
KEY_NAME PQ
KEY_VALUE (0, 1566659705)
KEY_NAME ProcId
KEY_VALUE 65.8
KEY_NAME SID
KEY_VALUE 345.30675
OWNER_ID 1
INCIDENT_FILE /oracle/diag/rdbms/o122/o122/trace/o122_ora_5985.trc
OWNER_ID 1
INCIDENT_FILE /oracle/diag/rdbms/o122/o122/incident/incdir_298124/o122_ora_5985_i298124.trc
1 row fetched

The event contains a trace file. Actually, it has generated two trace files. The full SQL that has generated the event  is in the first one. However the feature mechanism is revealed in the second one. So, let’s examine it:

adrci> show trace /oracle/diag/rdbms/o122/o122/trace/o122_ora_5985.trc

The interesting parts of the second  trace file look like this:

1>     ***** Error Stack *****
       ORA-00700: soft internal error, arguments: [EVENT_CREATED_INCIDENT], [0], [Missing Table], [], [], [], [], [], [], [], [], []
1<     ***** Error Stack *****
      about to signal 942
      Name: XXX

So, this not only tells me which table is missing but it also tells me the mechanism employed in order to do this: the instance generates a “soft internal error”, an interesting name, to ask  Oracle kernel  to “signal 942”.

This mechanism is roughly analogous to Unix system service mechanism: a system trap “change mode kernel” is triggered with the arguments telling kernel to perform read, write or any other system service. My guess is that the “soft internal error” was actually named after the “soft interrupt” mechanism on Unix.

The implementation is rather clumsy and awkward.  Four incidents are generated for a single error. The last one is the one that is the most useful.  So why is this good? This is good because ADR incidents are flood controlled: 5 in an hour, 25 in a day. If you turn on tracing for a busy system,  it will not flood your trace directory with a ton of trace files, risking further problem.

Turning the event off is standard:

SQL> alter system set events='942 trace name context off';

System SET altered.

Please note that “incident” is not an event, it’s an option to event. If the event tracing is turned off by using “context off”,  then the incident generation is turned off. No further incident will be generated. This is Oracle 12.2, the most prevalent version today. Oracle 19c is essentially the same.  Generating only a single incident per event, instead of 4, would be nice…

 

 

Advertisements
Posted in Uncategorized | Leave a comment

I lost a database, for the 1st time since 1992.

Well, I lost a pluggable database, not the real instance.  What I did is the following:

ALTER PLUGGABLE DATABASE PDB_NAME UNPLUG INTO ‘/my/dir/PDB_NAME.pdb’;

So far, so good. However, the pluggable database was around 800 GB and was getting unplugged  at the whopping speed of 12 GB per hour. Seeing that this will take forever, I pressed Ctrl-C and killed the “unplug” process. The result was a disaster: database was recorded as unplugged and couldn’t be open or unplugged. In other words, it was dead as a dodo. I had a backup done by

backup pluggable database PDB_NAME format “/backup/dir/DB_%U.bak’

from RMAN. However, it is not possible to restore it either to the same database or to  another database. The original database is a development database and is running in NOARCHIVELOG mode.

From now on, I will be very careful with pluggable databases. It seems that the feature still needs some development.

Posted in Uncategorized | 2 Comments

Systemd service to start/stop Oracle

In recent days, I’ve been privy to several discussions of systemd service to start/stop oracle instance. The venerable “init” program that was developed a long time ago, with the Unix system itself has grown old. The problem with the /sbin/init was that it was hard to maintain dependencies and that it was using a single thread to start the system, thereby slowing it down.  Few years ago, systemd program was introduced as a solution. The “systemd” program is not very well documented, like /etc/init.d structure was, so learning it presents a problem. I resolved the problem mostly by googling and reading articles with the script examples. Now, I was able to create a systemd service that behaves correctly. The main tool for managing the systemd services is called “systemctl”. It takes the following 3 forms:

  1. systemctl start/stop <service name>
  2. systemctl enable/disable <service name>. Enable/disable enables or disable the service at the time of booting the machine.
  3. systemctl status <service name>

This is a bit simplified version, a good tutorial is available here:

https://www.digitalocean.com/community/tutorials/how-to-use-systemctl-to-manage-systemd-services-and-units

Similar to init, systemd also uses shell scripts to start or stop services. Unlike the init program, the controlling scripts reside in /etc/systemd/system directory and have the extension “.service”. So, the service to start/stop oracle on boot or shutdown should be named something like oracle.service. So here is how we would check the status of oracle service:

root@ora18c oracle]# systemctl status oracle
● oracle.service - oracle
Loaded: loaded (/etc/systemd/system/oracle.service; enabled; vendor preset: disabled)
Active: active (exited) since Sun 2019-05-05 17:07:28 EDT; 14min ago
Docs: https://docs.oracle.com
Process: 3581 ExecStart=/usr/local/bin/dbstart $ORACLE_HOME (code=exited, status=0/SUCCESS)
Main PID: 3581 (code=exited, status=0/SUCCESS)
Tasks: 84
CGroup: /system.slice/oracle.service
├─ 3727 /oracle/product/18.0.0/dbhome_1/bin/tnslsnr LISTENER -inhe...
├─ 7850 ora_pmon_orcl
├─ 7853 ora_clmn_orcl
├─ 7858 ora_psp0_orcl
├─ 7865 ora_vktm_orcl
├─ 7883 ora_gen0_orcl
├─ 7914 ora_mman_orcl
├─ 7922 ora_gen1_orcl
├─ 7965 ora_diag_orcl
├─ 7971 ora_ofsd_orcl
├─ 8000 ora_dbrm_orcl
├─ 8010 ora_vkrm_orcl
├─ 8020 ora_svcb_orcl
├─ 8027 ora_pman_orcl
├─ 8036 ora_dia0_orcl
├─ 8050 ora_dbw0_orcl
├─ 8062 ora_lgwr_orcl
├─ 8066 ora_ckpt_orcl
├─ 8084 ora_lg00_orcl
├─ 8090 ora_smon_orcl
├─ 8096 ora_lg01_orcl
├─ 8107 ora_smco_orcl
├─ 8118 ora_w000_orcl
├─ 8130 ora_reco_orcl
├─ 8146 ora_w001_orcl
├─ 8157 ora_lreg_orcl
├─ 8169 ora_pxmn_orcl
├─ 8185 ora_mmon_orcl
├─ 8198 ora_mmnl_orcl
├─ 8206 ora_d000_orcl
├─ 8212 ora_s000_orcl
├─ 8222 ora_tmon_orcl
├─10662 ora_m000_orcl
├─12204 ora_tt00_orcl
├─12208 ora_arc0_orcl
├─12213 ora_tt01_orcl
├─12228 ora_arc1_orcl
├─12236 ora_arc2_orcl
├─12246 ora_arc3_orcl
├─12270 ora_tt02_orcl
├─14188 ora_aqpc_orcl
├─14363 ora_p000_orcl
├─14365 ora_p001_orcl
├─14367 ora_p002_orcl
├─14369 ora_p003_orcl
├─14371 ora_p004_orcl
├─14373 ora_p005_orcl
├─14376 ora_w002_orcl
├─14535 ora_cjq0_orcl
├─14604 ora_w003_orcl
├─15835 ora_w004_orcl
├─15921 ora_m001_orcl
├─15923 ora_m002_orcl
├─15929 ora_m003_orcl
├─15970 ora_m004_orcl
├─15978 ora_qm02_orcl
├─15981 ora_q001_orcl
├─15985 ora_q003_orcl
├─15987 ora_q004_orcl
├─15989 ora_q005_orcl
├─16017 ora_q006_orcl
├─16019 ora_q007_orcl
├─16021 ora_q008_orcl
├─16028 ora_q009_orcl
├─16030 ora_q00a_orcl
├─16032 ora_q00b_orcl
├─16034 ora_q00c_orcl
├─16036 ora_q00d_orcl
├─16038 ora_q00e_orcl
├─16040 ora_q00f_orcl
├─16042 ora_q00g_orcl
├─16044 ora_q00h_orcl
├─16046 ora_q00i_orcl
├─16048 ora_q00j_orcl
├─16050 ora_q00k_orcl
├─16052 ora_q00l_orcl
├─16054 ora_q00m_orcl
├─16703 ora_w005_orcl
├─16707 ora_w006_orcl
├─16712 ora_w007_orcl
└─16941 ora_qm03_orcl

May 05 17:07:28 ora18c.home.com systemd[1]: Started oracle.
May 05 17:07:29 ora18c.home.com dbstart[3581]: Processing Database instance ...g
Hint: Some lines were ellipsized, use -l to show in full.
[root@ora18c oracle]#

As you can see, this is an oracle service running. I used Oracle 18c as a basis for the service. From this it is visible that oracle was started from /usr/local/bin, not from $ORACLE_HOME/bin. The reason was that I was too lazy to type the entire string for $ORACLE_HOME,  so I created the following symbolic links:

root@ora18c oracle]# ls -l /usr/local/bin/dbs*
lrwxrwxrwx 1 root root 42 May 5 04:03 /usr/local/bin/dbshut -> /oracle/product/18.0.0/dbhome_1/bin/dbshut
lrwxrwxrwx 1 root root 43 May 5 04:03 /usr/local/bin/dbstart -> /oracle/product/18.0.0/dbhome_1/bin/dbstart

That should not be a problem.  The next thing to create is our service script:

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

[Service]
WorkingDirectory=/oracle/product/18.0.0/dbhome_1

User=oracle
Group=oinstall
EnvironmentFile=/etc/default/oracle

ExecStart=/usr/local/bin/dbstart $ORACLE_HOME
ExecStop=/usr/local/bin/dbshut $ORACLE_HOME
RemainAfterExit=yes

# Disable timeout logic and wait until process is stopped
TimeoutStopSec=300
SendSIGKILL=no

[Install]
WantedBy=multi-user.target

# Built for ${project.name}-${project.version} (${project.name})

The service script contains several interesting elements:

  1. Wants/After lines determine when will systemd attempt to start oracle. I selected the phase after the network start. It doesn’t make much sense to start an instance if it isn’t possible to access it.
  2. The environment file is defined as /etc/default/oracle.  It contains variables defined using standard shell syntax.
  3. ExecStart/ExecStop lines define what should the service run when it receives the command to run. The RemainAfterExit means that the service will be regarded as active. Without that, it is not possible to shut it down. I found it the long way, after some trials and errors.

The environment file looks like this:

[root@ora18c oracle]# cat /etc/default/oracle
ORACLE_BASE=/oracle
ORACLE_HOME=/oracle/product/18.0.0/dbhome_1
LD_LIBRARY_PATH=/oracle/product/18.0.0/dbhome_1/lib

The only useful variable in here is ORACLE_HOME, because it allows me to use $ORACLE_HOME in the script itself.  When the file oracle.service is copied into /etc/systemd/system directory, it becomes easy to do the following:

[root@ora18c oracle]# systemctl stop oracle
[root@ora18c oracle]# systemctl status oracle
● oracle.service - oracle
Loaded: loaded (/etc/systemd/system/oracle.service; enabled; vendor preset: disabled)
Active: inactive (dead) since Sun 2019-05-05 17:46:02 EDT; 8s ago
Docs: https://docs.oracle.com
Process: 18609 ExecStop=/usr/local/bin/dbshut $ORACLE_HOME (code=exited, status=0/SUCCESS)
Process: 3581 ExecStart=/usr/local/bin/dbstart $ORACLE_HOME (code=exited, status=0/SUCCESS)
Main PID: 3581 (code=exited, status=0/SUCCESS)

May 05 17:07:28 ora18c.home.com systemd[1]: Started oracle.
May 05 17:07:29 ora18c.home.com dbstart[3581]: Processing Database instance ...g
May 05 17:45:26 ora18c.home.com systemd[1]: Stopping oracle...
May 05 17:45:26 ora18c.home.com dbshut[18609]: Processing Database instance ...g
May 05 17:46:02 ora18c.home.com systemd[1]: Stopped oracle.
Hint: Some lines were ellipsized, use -l to show in full.
[root@ora18c oracle]#

And, of course, we can start it back:

root@ora18c oracle]# systemctl start oracle
[root@ora18c oracle]# systemctl status oracle
● oracle.service - oracle
Loaded: loaded (/etc/systemd/system/oracle.service; enabled; vendor preset: disabled)
Active: active (running) since Sun 2019-05-05 17:47:00 EDT; 6s ago
Docs: https://docs.oracle.com
Process: 18609 ExecStop=/usr/local/bin/dbshut $ORACLE_HOME (code=exited, status=0/SUCCESS)
Main PID: 18860 (dbstart)
Tasks: 40
CGroup: /system.slice/oracle.service
├─18860 /bin/sh /usr/local/bin/dbstart /oracle/product/18.0.0/dbho...
├─18876 /oracle/product/18.0.0/dbhome_1/bin/tnslsnr LISTENER -inhe...
├─18914 /bin/sh /usr/local/bin/dbstart /oracle/product/18.0.0/dbho...
├─18966 sqlplus 
├─18975 ora_pmon_orcl
├─18977 ora_clmn_orcl
├─18979 ora_psp0_orcl
├─18982 ora_vktm_orcl
├─18986 ora_gen0_orcl
├─18988 ora_mman_orcl
├─18990 ora_sa00_orcl
├─18992 ora_gen1_orcl
├─18995 ora_diag_orcl
├─18997 ora_ofsd_orcl
├─19000 ora_dbrm_orcl
├─19002 ora_vkrm_orcl


....

For this article to be complete, I have to show the protection of the two involved files, the service file and the environment file:

[root@ora18c oracle]# ls -l /etc/systemd/system/oracle.service
-rw-r--r-- 1 root root 598 May 5 10:17 /etc/systemd/system/oracle.service
[root@ora18c oracle]# ls -l /etc/default/oracle
-rw-r--r-- 1 root root 116 May 5 09:10 /etc/default/oracle
[root@ora18c oracle]#

That is all. If anyone has a better script, I would gladly hear it. This article was motivated by the very recent post on oracle-l.  The usefulness of this article is limited because both main Linux platforms for running Oracle support SYSV init emulation, which means that old was still works well.

Posted in Uncategorized | Leave a comment

S3 at home

I have recently encountered a S3 storage simulator. It can turn any old home computer into an AWS S3 storage. The software is called “MinIO” and can be found here: https://min.io/

It is available for Linux, Windows, MacOS and Docker. There is also the source code if anyone wants to compile it from scratch.

The installation is simple: all that is needed is to download the executable “minio” , copy it to /usr/local/bin and make executable:

chmod +x /usr/local/bin/minio

Now, we can start minio by issuing the following command:

minio server /my/dir

MinIO will start a server, which doesn’t go into the background utilizing /my/dir as the S3 storage. MinIO should not be run as “root”, creating a dedicated user is recommended. However, There are still few things left to do:

MinIO needs some additional configuration to support https. In particular, it needs certificates. When minio is first started, it creates the directory .minio in the $HOME directory of the user who started it. I used very creatively selected username “minio”:

[mgogala@medo ~]$ grep minio /etc/passwd
minio:x:1001:1001::/home/minio:/bin/bash

In order to make MinIO support https,  we need to create the certificate and the key in $HOME/.minio/certs directory. I did that by using the following commands:

cd $HOME/.minio/certs
openssl req -new -newkey rsa:4096 -x509 -sha256 -days 365 -nodes -out public.crt -keyout private.key

MinIO server needs to be restarted after that. Now, we can attempt login. The first thing to do is to try with your favorite browser. I mostly use Chrome:

minio1

MinIO runs on the port 9000. The security risk that Chrome warned me about is caused by self-signed certificate generated above, by using “openssl” command. After “accepting the risk” and proceeding to the site, the following screen is shown:

minio2

The login key requires two pieces of information: access key and secret key. They can be found in the directory which MinIO presents as S3 storage. We started our MinIO by issuing the following command:

minio server /my/dir

MinIO will automatically create sub-directory /my/dir/.minio.sys.  The content of that directory looks like this:

[mgogala@medo s3store]$ cd .minio.sys/
[mgogala@medo .minio.sys]$ ls
config format.json multipart tmp

The sub-directory “config” contains the file named “config.json”.  The content of the config.json file looks like this:

{
"version": "33",
"credential": {
"accessKey": "ZW7R5TIAZUKSWLYQ3GEF",
"secretKey": "Hm4AEETYL+wfEDXjhz814t9MZFpV3I4UfTIFd1Om",
"expiration": "1970-01-01T00:00:00Z",
"status": "enabled"
},
"region": "",
"worm": "off",
"storageclass": {
"standard": "",
"rrs": ""
},

The keys needed for login are at the very beginning of the file. Now, we can login and configure AWS CLI. However, with the configuration so far, we need to start MinIO manually every time the system is rebooted. The “minio.service” file, needed to start MinIO as an OS service, using systemd can be found here:

https://github.com/minio/minio-service

The site also contains the instructions how to customize the service. Now, we have  S3 storage in a box and can play with AWS CLI:

mgogala@medo config]$ systemctl status minio
● minio.service - MinIO
Loaded: loaded (/etc/systemd/system/minio.service; enabled; vendor preset: disabled)
Active: active (running) since Mon 2019-04-29 06:33:11 EDT; 39min ago
Docs: https://docs.min.io
Process: 6058 ExecStartPre=/bin/bash -c if [ -z "${MINIO_VOLUMES}" ]; then echo "Variable MINIO_VOLUMES not set in /etc/default/minio"; exit 1; fi (code=exited, status=0/SUCCESS)
Main PID: 6064 (minio)
Tasks: 19
CGroup: /system.slice/minio.service
└─6064 /usr/local/bin/minio server /data1/s3store

Apr 29 06:43:45 medo.home.com minio[6064]: 2019/04/29 06:43:45 http: TLS han...e
Apr 29 06:43:45 medo.home.com minio[6064]: 2019/04/29 06:43:45 http: TLS han...e
Apr 29 06:44:18 medo.home.com minio[6064]: 2019/04/29 06:44:18 http: TLS han...e
Apr 29 06:44:18 medo.home.com minio[6064]: 2019/04/29 06:44:18 http: TLS han...e
Apr 29 06:45:18 medo.home.com minio[6064]: 2019/04/29 06:45:18 http: TLS han...e
Apr 29 06:45:18 medo.home.com minio[6064]: 2019/04/29 06:45:18 http: TLS han...e
Apr 29 06:50:18 medo.home.com minio[6064]: 2019/04/29 06:50:18 http: TLS han...e
Apr 29 06:50:18 medo.home.com minio[6064]: 2019/04/29 06:50:18 http: TLS han...e
Apr 29 06:53:36 medo.home.com minio[6064]: 2019/04/29 06:53:36 http: TLS han...e
Apr 29 06:53:36 medo.home.com minio[6064]: 2019/04/29 06:53:36 http: TLS han...e
Hint: Some lines were ellipsized, use -l to show in full.
[mgogala@medo config]$

There is also something called s3fs-fuse, which is a user mode file system module that presents S3 bucket as a network disk. Since my whole purpose was to play with AWS CLI, I didn’t install it.

For practicing AWS CLI, one should also download the MinIO client called “mc”.  The original AWS CLI cannot be pointed to the local host, the URL “https://s3.amazonaws.com&#8221; is hard-coded. The “mc” client uses a very decent subset of the AWS CLI:

NAME:
mc - MinIO Client for cloud storage and filesystems.

USAGE:
mc [FLAGS] COMMAND [COMMAND FLAGS | -h] [ARGUMENTS...]

COMMANDS:
ls list buckets and objects
mb make a bucket
rb remove a bucket
cat display object contents
head display first 'n' lines of an object
pipe stream STDIN to an object
share generate URL for temporary access to an object
cp copy objects
mirror synchronize object(s) to a remote site
find search for objects
sql run sql queries on objects
stat show object metadata
diff list differences in object name, size, and date between two buckets
rm remove objects
event configure object notifications
watch listen for object notification events
policy manage anonymous access to buckets and objects
admin manage MinIO servers
session resume interrupted operations
config configure MinIO client
update update mc to latest release
version show version info

GLOBAL FLAGS:
--config-dir value, -C value path to configuration folder (default: "/home/mgogala/.mc")
--quiet, -q disable progress bar display
--no-color disable color theme
--json enable JSON formatted output
--debug enable debug output
--insecure disable SSL certificate verification
--help, -h show help
--version, -v print the version

VERSION:
RELEASE.2019-04-24T00-09-41Z
mgogala@umajor:~$

However, if you want https to work, you will have to copy the certificate generated for the MinIO server to $HOME/.mc/certs/CAs:mgogala@umajor:~$

ls ~/.mc/certs/CAs/
public.crt
mgogala@umajor:~$

Now, you can add the host running MinIO like this:

mc config host add minio https://medo:9000 'ZW7R5TIAZUKSWLYQ3GEF' 'Hm4AEETYL+wfEDXjhz814t9MZFpV3I4UfTIFd1OmT

The syntax is self evident. The  string “minio” is the name for your server, the host names, port and access keys are added. Your AWS CLI is ready, you can  plow away.  Instead of using “aws s3 ls” you will be using “mc minio ls” and the same for the other commands that are implemented in mc. The “mc” command can also access the real S3 storage. Enjoy.

Posted in Uncategorized | Leave a comment

Private Temporary Tables (Oracle 18c)

I was very excited when I learned about the new feature in Oracle 18c, namely the private temporary tables. The idea was to do the same thing as with SQL Server, which is able to index those tables and use them for reports. This works in SQL Server:
select * into #test1 from emp;
create index jobs_t on #test1(job);

The “#” prefix means that I am creating a temporary table. I am able to create indexes on the SQL Server temporary tables, for reporting purposes. Let’s show it:

mgogala@mssql:~$ sqlcmd -U scott -P tiger -d scott
1> select * into #test1 from emp;
2> go

(14 rows affected)
1> create index jobs_t on #test1(job);
2> go
1> select * from #test1 where job=’CLERK’;
2> go
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
———– ————————————————– ————————————————– ———– —————- ———– ———– ——
7369 SMITH CLERK 7902 1980-12-17 800 NULL 20
7876 ADAMS CLERK 7788 1987-05-23 1100 NULL 20
7900 JAMES CLERK 7698 1981-12-03 950 NULL 30
7934 MILLER CLERK 7782 1982-01-23 1300 NULL 10

(4 rows affected)

The table #test1 vanishes automatically after disconnecting from SQL Server:

mgogala@mssql:~$ sqlcmd -U scott -P tiger -d scott
1> select * from #test1;
2> go
Msg 208, Level 16, State 1, Server mssql, Line 1
Invalid object name ‘#test1’.
1>

However, the same thing doesn’t work with Oracle 18c:

1 CREATE PRIVATE TEMPORARY TABLE ORA$PTT_EMP(
2 “EMPNO” NUMBER(4,0),
3 “ENAME” VARCHAR2(10),
4 “JOB” VARCHAR2(9),
5 “MGR” NUMBER(4,0),
6 “HIREDATE” DATE,
7 “SAL” NUMBER(7,2),
8 “COMM” NUMBER(7,2),
9 “DEPTNO” NUMBER(2,0))
10* ON COMMIT PRESERVE DEFINITION
SQL> /

Table created.

Elapsed: 00:00:00.00

SQL> create index ora$ptt_jobs on ora$ptt_emp(job);
create index ora$ptt_jobs on ora$ptt_emp(job)
*
ERROR at line 1:
ORA-14451: unsupported feature with temporary table

What does that mean? It is possible to create private temporary table but restrictions on them are such that they are almost useless. It is not possible to index or compress private temporary table  which means that it is not possible to use Oracle private temporary tables the same way we use SQL Server temporary tables, for reporting. There is also a funny thing with the “ON COMMIT DROP DEFINITION” clause. Remember, the first phase of every DDL is execute a “COMMIT” statement. An attempt to index private temporary table defined with “ON COMMIT DROP DEFINITION” will actually drop the private temporary table because the first thing that “CREATE INDEX” statement actually does is – COMMIT.

So, while it is encouraging to see Oracle trying to catch up  with some other competing database systems, the private temporary tables are very far away from being ready for the prime time. For now, I don’t see much use for the Oracle private temporary tables. In order  for private temporary tables to be useful, it must be possible to index them. Unfortunately, that is not possible now. It would also help to be able to compress them, which also doesn’t work.

Posted in Uncategorized | Leave a comment

IO Calibration

One of the long time problems with Oracle is IO calibration. I am talking, of course, about DBMS_RESOURCE_MANAGER.CALIBRATE_IO procedure. The most problematic aspect of this procedure is the fact that the results are not repeatable. It’s like rolling of the dice, it doesn’t really measure anything. I hoped that Oracle 18c will finally fix that, but no such luck:

[oracle@ora18c ~]$ sqlplus / as sysdba
SQL*Plus: Release 18.0.0.0.0 – Production on Tue Sep 25 16:33:46 2018
Version 18.3.0.0.0
Copyright (c) 1982, 2018, Oracle.  All rights reserved.
Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 – Production
Version 18.3.0.0.0
SQL> set serveroutput on
set timing on
DECLARE
   lat  INTEGER;
   iops INTEGER;
   mbps INTEGER;
 BEGIN
— DBMS_RESOURCE_MANAGER.CALIBRATE_IO (<DISKS>, <MAX_LATENCY>, iops, mbps, lat);
   DBMS_RESOURCE_MANAGER.CALIBRATE_IO (1, 20, iops, mbps, lat);
   DBMS_OUTPUT.PUT_LINE (‘max_iops = ‘ || iops);
   DBMS_OUTPUT.PUT_LINE (‘latency  = ‘ || lat);
   DBMS_OUTPUT.PUT_LINE (‘max_mbps = ‘ || mbps);
end;
/
SQL> SQL>   2    3    4    5    6    7    8    9   10   11   12  
max_iops = 154
latency  = 19
max_mbps = 18579
Note: The high I/O latencies from the calibration run indicate that the
calibration I/Os are being serviced mostly from disk. If your storage has a
cache, you may achieve better results by rerunning.  Rerunning will warm or
populate the storage cache.
max_iops = 154
latency  = 19
max_mbps = 18579
PL/SQL procedure successfully completed.
Elapsed: 00:04:31.86
SQL> SQL> /
max_iops = 1353418
latency  = 0
max_mbps = 18850
max_iops = 1353418
latency  = 0
max_mbps = 18850
PL/SQL procedure successfully completed.
Elapsed: 00:08:30.15
SQL> /
max_iops = 1351136
latency  = 0
max_mbps = 18738
max_iops = 1351136
latency  = 0
max_mbps = 18738
PL/SQL procedure successfully completed.
Elapsed: 00:09:00.19
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 3707762528 bytes
Fixed Size    8902496 bytes
Variable Size  855638016 bytes
Database Buffers 2835349504 bytes
Redo Buffers    7872512 bytes
Database mounted.
Database opened.
SQL> set serveroutput on
set timing on
DECLARE
   lat  INTEGER;
   iops INTEGER;
   mbps INTEGER;
 BEGIN
— DBMS_RESOURCE_MANAGER.CALIBRATE_IO (<DISKS>, <MAX_LATENCY>, iops, mbps, lat);
   DBMS_RESOURCE_MANAGER.CALIBRATE_IO (1, 20, iops, mbps, lat);
   DBMS_OUTPUT.PUT_LINE (‘max_iops = ‘ || iops);
   DBMS_OUTPUT.PUT_LINE (‘latency  = ‘ || lat);
   DBMS_OUTPUT.PUT_LINE (‘max_mbps = ‘ || mbps);
end;
/
SQL> SQL>   2    3    4    5    6    7    8    9   10   11   12  
max_iops = 274
latency  = 22
max_mbps = 83
Note: The high I/O latencies from the calibration run indicate that the
calibration I/Os are being serviced mostly from disk. If your storage has a
cache, you may achieve better results by rerunning.  Rerunning will warm or
populate the storage cache.
max_iops = 274
latency  = 22
max_mbps = 83
PL/SQL procedure successfully completed.
Elapsed: 00:08:35.87
SQL> SQL> 
I’ve run 4 iterations, with an instance restart between the 3rd and the 4th iteration. If you check timing, one iteration takes between 8 and 9 minutes. However, all the results are drastically different, despite the fact that the measurements were taken on an idle instance on a virtual machine. To make things worse, calibration only remembers the last run:

SQL> desc RESOURCE_IO_CALIBRATE$
Name Null? Type
—————————————– ——– —————————-
START_TIME TIMESTAMP(6)
END_TIME TIMESTAMP(6)
MAX_IOPS NUMBER
MAX_MBPS NUMBER
MAX_PMBPS NUMBER
LATENCY NUMBER
NUM_DISKS NUMBER
ADDITIONAL_INFO VARCHAR2(1024)

SQL> select count(*) from RESOURCE_IO_CALIBRATE$;

COUNT(*)
———-
1

There is only a single row in RESOURCE_IO_CALIBRATE$  and this is only the last row:

SQL> set sqlformat ansiconsole
SQL> select * from RESOURCE_IO_CALIBRATE$;
START_TIME END_TIME MAX_IOPS MAX_MBPS MAX_PMBPS LATENCY NUM_DISKS ADDITIONAL_INFO
25-SEP-18 05.05.05.955884000 PM 25-SEP-18 05.13.41.349030000 PM 274 83 71 22 12
Note: The high I/O latencies from the calibration run indicate that the calibration I/Os are being serviced mostly from disk. If your storage has a cache, you may achieve better results by rerunning. Rerunning will warm or populate the storage cache.

The IO calibration helps determine the automatic degree of parallelism, which makes it a rather critical procedure. However, the calibrating procedure doesn’t measure anything and the results are not repeatable. Unfortunately, even with 18c, we’ll have to do what I’ve been doing for some time: run Kevin Closson’s SLOB and update the table manually with the SLOB results.
Posted in Uncategorized | Leave a comment

Upgrade to Oracle 12.1.0.2 problems

I was recently tasked with upgrading  an Oracle RDBMS 11.2.0.4 to 12.1.0.2. The platform is Windows 2012 R2. I know about not being able to move the listener to the new 12c home, which prevents me from using the upgrade utility DBUA.  However, this time there was a nasty surprise in store for me. I ran all the pre-upgrade steps and done all the pre-upgrade fixes and it was time to run catctl.pl. Here is what happened:

C:\app\oracle\product\12.1.0\dbhome_1\RDBMS\ADMIN>%ORACLE_HOME%\perl\bin\perl catctl.pl catupgrd.sql
Undefined subroutine &Tie::Hash::NamedCapture::flags called at C:\app\oracle\age
nt12c\core\12.1.0.5.0\perl\lib/Tie/Hash/NamedCapture.pm line 9.
Compilation failed in require at C:\app\oracle\agent12c\core\12.1.0.5.0\perl\lib/English.pm line 148.
BEGIN failed--compilation aborted at C:\app\oracle\agent12c\core\12.1.0.5.0\perl
\lib/English.pm line 148.
Compilation failed in require at catctl.pl line 322.
BEGIN failed--compilation aborted at catctl.pl line 322
.

The line 322 of catctl.pl is “use English;”, a normal and very frequently used Perl directive. Fortunately, I am not completely unfamiliar with Perl, so I was able to figure out the problem. The problem was in OEM:

C:\app\oracle\product\11.2.0\db_1\NETWORK\ADMIN>echo %PERL5LIB%
C:\app\oracle\agent12c\core\12.1.0.5.0\perl\lib;C:\app\oracle\agent12c\core\12.1
.0.5.0\perl\site\lib;C:\app\oracle\agent12c\core\12.1.0.5.0\sysman\admin\scripts

The variable PERL5LIB tells the Perl interpreter where to look for modules. This variable is obviously set on the system-wide basis and is set to OEM 12c directories.  Apparently, there is something in the OEM version of the English.pm module that Perl that comes in Oracle 12c home didn’t like. As soon as I’ve unset this in the CMD windown, by executing set PERL5LIB=<CR>, the upgrade script started working as advertised:
C:\app\oracle\product\12.1.0\dbhome_1\RDBMS\ADMIN>%ORACLE_HOME%\perl\bin\perl catctl.pl catupgrd.sql

Argument list for [catctl.pl]
SQL Process Count     n = 0
SQL PDB Process Count N = 0
Input Directory       d = 0
Phase Logging Table   t = 0
Log Dir               l = 0
Script                s = 0
Serial Run            S = 0
Upgrade Mode active   M = 0
Start Phase           p = 0
End Phase             P = 0
Log Id                i = 0
Run in                c = 0
Do not run in         C = 0
Echo OFF              e = 1
No Post Upgrade       x = 0
Reverse Order         r = 0
Open Mode Normal      o = 0
Debug catcon.pm       z = 0
Debug catctl.pl       Z = 0
Display Phases        y = 0
Child Process         I = 0

catctl.pl version: 12.1.0.2.0
Oracle Base           = c:\app\oracle

Analyzing file catupgrd.sql
Log files in C:/app/oracle/product/12.1.0/dbhome_1/RDBMS/ADMIN
catcon: ALL catcon-related output will be written to catupgrd_catcon_4296.lst
catcon: See catupgrd*.log files for output generated by scripts
catcon: See catupgrd_*.lst files for spool files, if any
Number of Cpus        = 8
SQL Process Count     = 0
New SQL Process Count = 4

------------------------------------------------------
Phases [0-73]         Start Time:[2018_03_30 21:25:39]
------------------------------------------------------
Serial   Phase #: 0      Files: 1     Time: 258s
Serial   Phase #: 1      Files: 5     Time: 75s
Restart  Phase #: 2      Files: 1     Time: 1s
Parallel Phase #: 3      Files: 18    Time: 20s
Restart  Phase #: 4      Files: 1     Time: 0s
Serial   Phase #: 5      Files: 5     Time: 31s
Serial   Phase #: 6      Files: 1     Time: 24s
Serial   Phase #: 7      Files: 4     Time: 15s
Restart  Phase #: 8      Files: 1     Time: 1s
Parallel Phase #: 9      Files: 62    Time: 56s
Restart  Phase #:10      Files: 1     Time: 0s
Serial   Phase #:11      Files: 1     Time: 27s
Restart  Phase #:12      Files: 1     Time: 0s
Parallel Phase #:13      Files: 91    Time: 32s
Restart  Phase #:14      Files: 1     Time: 0s
Parallel Phase #:15      Files: 111   Time: 44s
Restart  Phase #:16      Files: 1     Time: 1s
Serial   Phase #:17      Files: 3     Time: 3s
Restart  Phase #:18      Files: 1     Time: 0s
Parallel Phase #:19      Files: 32    Time: 31s
Restart  Phase #:20      Files: 1     Time: 0s
Serial   Phase #:21      Files: 3     Time: 17s
Restart  Phase #:22      Files: 1     Time: 0s
Parallel Phase #:23      Files: 23    Time: 125s
Restart  Phase #:24      Files: 1     Time: 1s
Parallel Phase #:25      Files: 11    Time: 49s
Restart  Phase #:26      Files: 1     Time: 0s
Serial   Phase #:27      Files: 1     Time: 3s
Restart  Phase #:28      Files: 1     Time: 0s
Serial   Phase #:30      Files: 1     Time: 0s
Serial   Phase #:31      Files: 257   Time: 47s
Serial   Phase #:32      Files: 1     Time: 0s
Restart  Phase #:33      Files: 1     Time: 1s
Serial   Phase #:34      Files: 1     Time: 10s
Restart  Phase #:35      Files: 1     Time: 0s
Restart  Phase #:36      Files: 1     Time: 1s
Serial   Phase #:37      Files: 4

The upgrade was completed successfully and the database is up and running as a 12c database. This, however, was the first time I ran into an intereference from OEM 12c agent. Apparently, the OEM 12c Perl modules are incompatible with the version delivered in Oracle 12.1.0.2. Go figure.

 

Posted in Uncategorized | Leave a comment