Ivan Kartik - Oracle and Linux Blog

Configuring automatic startup of Oracle Database under systemd on RHEL 7/OEL 7/CentOS 7

There are several methods how to start Oracle Database automatically during/after OS boot. You can use Oracle CRS, other clusterware or init implemented in Linux. Starting RHEL 7 SysV init has been replaced by systemd or simply said systemd is the new init system.

In these days not only group of Linux users became polarized but also whole Linux world became polarized as well. Mostly Redhat based distributions have adopted systemd, other distributions are reluctant to implement systemd and either are continuing to use SysV init or migrated to another solutions e.g. Upstart. Despite this fact most of the Oracle certified Linux distributions (such as Redhat or SuSE and their (even not certified) clones) are using the systemd.

This post briefly shows how to configure systemd service for automatic start of Oracle Databases and Listener and these steps are applicable for Redhat Enterprise Linux 7, Oracle Enterprise Linux 7, CentOS 7 or SuSE Linux Enteprise Server 12 and Fedora 15 (or later).

Typically systemd startup configuration consists of two parts:

  • unit file - using ".service" suffix (in case of service), typically stored in /usr/lib/systemd/system or /etc/systemd/system directory for units provided by installed packages or /usr/lib/systemd/user or /etc/systemd/user directory for units installed by administrator
  • environment file (optional) - typically stored in /etc/sysconfig directory on RHEL and it's clones. We don't need it in our case.

Creating the unit for automatic startup/shutdown of Oracle Database manually

Logon as root user create and edit /etc/systemd/system/oracle-rdbms.service and add following content:

# /etc/systemd/system/oracle-rdbms.service
#   Invoking Oracle scripts to start/shutdown Instances defined in /etc/oratab
#   and starts Listener

[Unit]
Description=Oracle Database(s) and Listener
Requires=network.target

[Service]
Type=forking
Restart=no
ExecStart=/opt/oracle/12102/bin/dbstart /opt/oracle/12102
ExecStop=/opt/oracle/12102/bin/dbshut /opt/oracle/12102
User=oracle

[Install]
WantedBy=multi-user.target

 Note that this configuration assumes that our ORACLE_HOME is /opt/oracle/12102. It's recommended to use PIDFile while using "forking" type but we don't need it. As you can see well known scripts (shipped with Oracle Database) are executed for startup/shutdown using path to Oracle Home in order to specify the Oracle Home for Listener process. As shown these scripts are executed under "oracle" user account/privileges. More over service can be started once network is cofigured (started) and service starts in multi-user level (more less equivalent of runlevel 3 in SysV init)

Now we have to reload systemd in order to register unit file (as root) and enable the service.

systemctl daemon-reload
systemctl enable oracle-rdbms

 So, now the startup service should be created and enabled but to be sure we can check it by following command (Note: first line is OS command, other lines is the output):

systemctl status oracle-rdbms
oracle-rdbms.service - Oracle Database(s) and Listener
   Loaded: loaded (/etc/systemd/system/oracle-rdbms.service; enabled)

According to output our service is enabled succesfully and should be started on next OS boot. To start the service without reboot of machine you can use following command:

systemctl start oracle-rdbms

 

Creating the unit for automatic startup/shutdown of Oracle Database using script

I have created a simple script which automatically performs above tasks for creating and enabling startup service. This scripts contains simple checks (as I've tried to make the script bulletproof), then lists available Oracle homes that exist on OS and then asks to specify Oracle home from which the Listener will be started. Note that it's important to specify Oracle home for the highest version of Oracle software as Listener will be handling connections for all Oracle homes.

#!/usr/bin/bash

# This script configures systemd startup service for Oracle Databases and Listener
# Ivan Kartik http://ivan.kartik.sk

if [ `whoami` != "root" ]; then
   echo "root login required!"
   exit
fi

if [ `uname -s` != "Linux" ]; then
   echo "This is not Linux!"
   exit
fi

if [ `ps -e|grep " 1 ?"|cut -d " " -f15` != "systemd" ]; then
   echo "Systemd is not present, use Init scripts instead!"
   exit
fi

echo "List of existing Oracle Homes:"
echo "------------------------------"
cat `cat /etc/oraInst.loc|grep inventory_loc|cut -d '=' -f2`/ContentsXML/inventory.xml|grep "HOME NAME"|cut -d '"' -f 4
echo 

echo "Enter ORACLE_HOME of Oracle Listener [$ORACLE_HOME]:"
read NEWHOME

case "$NEWHOME" in
     "")         ORAHOME="$ORACLE_HOME" ;;
     *)          ORAHOME="$NEWHOME" ;;
esac

if [ -z $ORAHOME ]; then
   echo "Error: Missing value!"
   exit
fi

if [ -f $ORAHOME/bin/lsnrctl ]; then
   echo '# /etc/systemd/system/oracle-rdbms.service
# Ivan Kartik http://ivan.kartik.sk
#   Invoking Oracle scripts to start/shutdown Instances defined in /etc/oratab
#   and starts Listener

[Unit]
Description=Oracle Database(s) and Listener
Requires=network.target

[Service]
Type=forking
Restart=no
ExecStart='$ORAHOME'/bin/dbstart '$ORAHOME'
ExecStop='$ORAHOME'/bin/dbshut '$ORAHOME'
User=oracle

[Install]
WantedBy=multi-user.target' > /etc/systemd/system/oracle-rdbms.service

    systemctl daemon-reload
    systemctl enable oracle-rdbms
    echo "Done! Service oracle-ordbms has been configured and will be started during next boot."
    echo "If you want to start service now, execute: systemctl start oracle-rdbms"
else
   echo "Error: No Listener script under specified ORACLE_HOME: $ORAHOME"
   exit
fi
 

You either can copy/paste this code or download here: http://ivan.kartik.sk/scripts/oracle_systemd_service.sh

Final check of service and started databases (Note: Output from systemctl status has been shortened):

# cat /etc/oratab |grep :Y
ORA12CR1:/opt/oracle/12102:Y
ORA11GR2:/opt/oracle/11204:Y

# systemctl status oracle-rdbms
oracle-rdbms.service - Oracle Database(s) and Listener
   Loaded: loaded (/etc/systemd/system/oracle-rdbms.service; enabled)
   Active: active (running) since Mon 2015-11-15 14:51:13 CET; 54s ago
  Process: 425 ExecStart=/opt/oracle/12102/bin/dbstart /opt/oracle/12102 (code=exited, status=0/SUCCESS)
   CGroup: /system.slice/oracle-rdbms.service
           ├─ 452 /opt/oracle/12102/bin/tnslsnr LISTENER -inherit
           ├─1155 ora_pmon_ORA12CR1
           ├─1171 ora_vktm_ORA12CR1
           ├─1177 ora_gen0_ORA12CR1
           ├─1181 ora_mman_ORA12CR1
           ├─1183 ora_diag_ORA12CR1
           ├─1185 ora_dbrm_ORA12CR1
           ├─1195 ora_ckpt_ORA12CR1
           ├─1197 ora_smon_ORA12CR1
           ├─1199 ora_reco_ORA12CR1
           ├─1201 ora_lreg_ORA12CR1
           ├─1289 ora_mman_ORA11GR2
.....   
           ├─1291 ora_dbw0_ORA11GR2
           ├─1293 ora_lgwr_ORA11GR2
           ├─1295 ora_ckpt_ORA11GR2
           ├─1297 ora_smon_ORA11GR2
           ├─1299 ora_reco_ORA11GR2
           ├─1301 ora_mmon_ORA11GR2
           ├─1303 ora_mmnl_ORA11GR2
           ├─1350 ora_qmnc_ORA11GR2
           └─1450 ora_q001_ORA11GR2

Nov 15 14:50:57 oel01 dbstart[425]: Processing Database instance "ORA12CR1": log file /opt/oracle/12102/startup.log
Nov 15 14:51:07 oel01 dbstart[425]: Processing Database instance "ORA11GR2": log file /opt/oracle/11204/startup.log
Nov 15 14:51:13 oel01 systemd[1]: Started Oracle Database(s) and Listener.

For little comparison of difference commands or usage regarding SysV init and systemd, here is very nice cheat sheet created by guys from Linoxide.com it's downloadable here: http://images.linoxide.com/systemd-vs-sysVinit-cheatsheet.pdf

Systemd Homepage: http://www.freedesktop.org/wiki/Software/systemd/

 

Upgrade to 12c: Put a spotlight on Database client

It's been almost one year I had touched for last time draft version of this article and thanks to couple days of free time I could finish it so I'm happy that it will not remain as unfinished symphony :-). Everybody talks today about upgrade Oracle Database to 12c or discussing many new features of 12c, no matter if they are interesting or not so interesting, really important or not so important, extra paid or not.
You've decided that you can rely on new version, it seems stable, you gained sufficient information about new and obsoleted/deprecated stuff and made couple of tests of upgrade process on your testing environment. Everything looks fine, so you proceed to real upgrade. Database is upgraded and everything seems to be OK but your applications and colleagues are reporting problems with access to freshly upgraded database. Well... database upgrade is not just about database upgrade.

Case 1: ORA-28040: No matching authentication protocol

This error is thrown when used version of authentication protocol by client does not meet minimum authentication protocol allowed for clients.
In 12cR1 comes with default settings for minimal version of authentication protocol set to 11, thus all clients which are using older version (older than 11g) will produce above error.

Workaround: Add following line to sqlnet.ora (on server side)

SQLNET.ALLOWED_LOGON_VERSION_CLIENT=8


Case 2: ORA-28040: No matching authentication protocol or ORA-03134: Connections to this server version are no longer supported

This error is thrown when you are using database link to older release for the same reason as in previous case.

Workaround: Add following line to sqlnet.ora

SQLNET.ALLOWED_LOGON_VERSION_SERVER=8

Case 3: Segmentation fault

Solution:
Password versions problem. Upgrade your client (at least to 10gR1 version) as it's obviously older than old.

If you hit one of above problems it means that you didn't pay (so much) attention (or you weren't so precise) to clients and 3rd party software that is connecting to your database and the quality of your preparation (I will discuss the preparation phase in one of future posts) for upgrade is... questionable because you probably didn't (and you definitely should) read this:

Client / Server Interoperability Support Matrix for Different Oracle Versions (Doc ID 207303.1)
and this:
Database Net Services Reference - https://docs.oracle.com/database/121/NETRF/sqlnet.htm#NETRF006

Note that above parameters in sqlnet.ora are just bloody workaround, it's not a solution. By setting of lower version by these parameters you've just bought a uncertain amount of time. Again I suggest to check the MOS Doc ID 207303.1.

Getting more information about Database client

In this part we take a look at some possibilities how to get more information about clients in order to identify who and what is using old client and which version client is used for particular session.
For that purpose we can use (G)V$SESSION_CONNECT_INFO (view based on X$KSUSECON) which since 11gR1 version is a great source of additional information about connections. This view (and X$ table) has been present even in earlier versions of Oracle Database (AFAIK I've seen it in 9i) but since 11gR1 provides more detailed thus useful information. For our report we will use it in combination with (G)V$SESSION view.

SYS@ORA11GR2:SQL>
SELECT
      /* a.sid, a.serial#, */ b.username,
      a.client_version, a.osuser,
      b.machine, b.module, b.program,
      a.client_charset , a.client_driver,
      substr(a.client_connection, 0, 4) client_conn,
      b.service_name,
      substr(b.server,0,1) server
FROM gv$session_connect_info a, gv$session b
WHERE a.sid = b.sid
      and a.serial# = b.serial#
      and b.module <> 'DBMS_SCHEDULER'
      and network_service_banner like 'TCP%'
 14  ORDER BY client_version, username;

USERNAME  CLIENT_VER OSUSER     MACHINE       MODULE                       PROGRAM                        CLIENT_CHARSET  CLIENT_DRI CLIE SERVICE_NAME  S
--------- ---------- ---------- ------------- ---------------------------- ------------------------------ --------------- ---------- ---- ------------- -
TEST1     11.1.0.7.0 oracle     lab01         JDBC Thin Client             JDBC Thin Client               Unknown         jdbcthin   Hete MYSERV        N
TEST1     11.2.0.2.0 updater    lab01         JDBC Thin Client             JDBC Thin Client               Unknown         jdbcthin   Hete ORA11GR2.TEST N
TEST2     11.2.0.2.0 oracle     lab01         JDBC Thin Client             JDBC Thin Client               Unknown         jdbcthin   Hete ORA11GR2.TEST D
TEST2     11.2.0.3.0 root       lab01         java@lab01 (TNS V1-V3)       java@lab01 (TNS V1-V3)         UTF8            jdbcoci    Hete ORA11GR2.TEST N
TEST3     11.2.0.4.0 kartik     MYDOM\LAB10   ngator.exe                   ngator.exe                     EE8MSWIN1250               Hete ORA11GR2.TEST D
TEST3     12.1.0.2.0 oracle     lab00         oracle@lab00 (TNS V1-V3)     oracle@lab00 (TNS V1-V3)       EE8ISO8859P2               Homo ORA11GR2.TEST N
TEST4     12.1.0.2.0 oracle     lab00         oracle@lab00 (TNS V1-V3)     oracle@lab00 (TNS V1-V3)       AL32UTF8                   Hete ORA11GR2.TEST D
TEST4     Unknown    oracle     oel01         httpd@oel01 (TNS V1-V3)      httpd@oel01 (TNS V1-V3)        Unknown                    Hete MYSERV        N
TEST5     Unknown    gtw        oel01         dbgtw.bin@oel01 (TNS V1-V3)  dbgtw.bin@oel01 (TNS V1-V3)    Unknown                    Homo ORA11GR2.TEST D
TEST5     Unknown    apache     oel02         httpd@oel02 (TNS V1-V3)      httpd@oel02 (TNS V1-V3)        Unknown                    Hete ORA11GR2.TEST N
TEST6     Unknown    apache     lab01         httpd@lab01 (TNS V1-V3)      httpd@lab01 (TNS V1-V3)        Unknown                    Hete ORA11GR2.TEST N
TEST6     Unknown    apache     oel02         httpd@oel02 (TNS V1-V3)      httpd@oel02 (TNS V1-V3)        Unknown                    Hete ORA11GR2.TEST N
TEST7     Unknown    kartik     mybook        SQL*Plus                     sqlplus@mybook (TNS V1-V3)     Unknown                    Homo ORA11GR2.TEST D

 According to above output we can easily identify servers which are using older version of Oracle Database client, older than 11gR1 to be precise. That session have the "Unknown" value in second column. And also we have other useful informations about client usage. For example, when we look at columns MACHINE, MODULE, PROGRAM, CLIENT_CONNECTION (CLIE column in above output) we know that sessions from OEL01 server are established from an Apache server which is running application (we can guess that there is some fastcgi, PHP, PL/SQL or other Apache module) and some other (probably standalone) application and both of them are using OCI connection. OK, this report is fine but it covers only current sessions but we can have clients/application/users which are connecting iregulary or occasionally.
So we need to be sure that we can identify the most of older clients as we can but this good concept for further step which will be automatic gathering of information.

For purpose of recording information about old clients we may use AFTER LOGON trigger.

CREATE TABLE systools.t_oclient_info
(
    version        varchar2(40),
    username       varchar2(30),
    osuser         varchar2(30),
    machine        varchar2(64),
    hostip         varchar2(15),
    module         varchar2(48),
    terminal       varchar2(30),
    oci_library    varchar2(27),
    client_charset varchar2(40),
    client_driver  varchar2(9),
    client_conn    varchar2(13),
    service_name   varchar2(64)
) TABLESPACE users;

CREATE OR REPLACE TRIGGER al_tr_log_oclient_info
AFTER LOGON ON DATABASE
WHEN (USER != 'SYS')
BEGIN
  INSERT INTO systools.t_oclient_info
  SELECT a.client_version,
         sys_context('USERENV', 'SESSION_USER') username,
         sys_context('USERENV', 'OS_USER') osuser,
         sys_context('USERENV', 'HOST') machine,
         sys_context('USERENV', 'IP_ADDRESS', 15),
         sys_context('USERENV', 'MODULE') module,
         sys_context('USERENV', 'TERMINAL') terminal,
         a.client_oci_library oci_library,
         a.client_charset,
         a.client_driver,
         substr(a.client_connection, 0, 4) client_conn,
         sys_context('USERENV', 'SERVICE_NAME') service_name
  FROM v$session_connect_info a
  WHERE  a.sid = sys_context('USERENV', 'SID')
         and a.network_service_banner like 'TCP%'
         and a.client_version = 'Unknown'
         and not exists (SELECT null
                          FROM systools.t_oclient_info
                          WHERE osuser = sys_context('USERENV', 'OS_USER')
                            and username = sys_context('USERENV', 'SESSION_USER')
                            and hostip = sys_context('USERENV', 'IP_ADDRESS', 15)
                            and module = sys_context('USERENV', 'MODULE'));
END;
/

Note that On-logon trigger can produce/add some overhead (in case of frequently connecting applications), so it's important to keep it as minimal as it's possible and do only necessary work. Logging is executed for all users except SYS, you might consider to reduce number of users if you are sure that no old client is connecting to particular user.
I've used information from SYS_CONTEXT instead of joining the V$SESSION view. The side effect of this change is that we lost PROGRAM column but at the same time we've got HOSTIP column with an IP address.
Also you should keep the T_OCICLIENT_INFO table as small as possible and that's the reason why I added the "not exists" condition (you can remove it, so no checking for existing entry, just pure insert for each session) and also you can limit number of rows in table by removing "OSUSER=sys_context('USERENV', 'OS_USER')" from conditions. Also keep on mind that only 10gR2 and older clients will be logged (as the client version number is returned for 11gR1). And finally note that this is a my way, perhaps it might not be a best way but from my point this was effective solution, as always you can choose your own way.

Now you need to give some time in order to log most (ideally and hopefully all) of the clients.

SYS@ORA11GR2:SQL> select * from systools.t_oclient_info;

VERSION    USERNAME  OSUSER     MACHINE   HOSTIP          MODULE                         TERMINAL   OCI_LIBRARY  CLIENT_CHARSET  CLIENT_DRI CLIE SERVICE_NAME
---------- --------- ---------- --------- --------------- ------------------------------ ---------- ------------ --------------- ---------- ---- -------------
Unknown    TEST7     kartik     mybook    192.168.56.1    sqlplus@mybook (TNS V1-V3)     pts/6      Unknown      Unknown                    Homo ORA11GR2.TEST
Unknown    TEST4     oracle     oel01     192.168.56.101  httpd@oel01 (TNS V1-V3)                                Unknown                    Hete MYSERV
Unknown    TEST5     gtw        oel01     192.168.56.101  dbgtw.bin@oel01 (TNS V1-V3)    pts/5      Unknown      Unknown                    Homo ORA11GR2.TEST
Unknown    TEST5     apache     oel02     192.168.56.102  httpd@oel02 (TNS V1-V3)                                Unknown                    Hete ORA11GR2.TEST
Unknown    TEST6     apache     oel02     192.168.56.102  httpd@oel02 (TNS V1-V3)                                Unknown                    Hete ORA11GR2.TEST
Unknown    TEST6     apache     lab01     192.168.56.111  httpd@lab01 (TNS V1-V3)                                Unknown                    Hete ORA11GR2.TEST

 Deep diving aka Oracle Client version identification and tracing for "Unknown"s

OK, we have got identified all clients older than 11gR1 and I hope (or believe) that previous part is sufficient for you. But there could be some reasons when you might need to know exact version of Oracle database client.
For instance you don't have access to remote computer which is connecting to your database and it might be very time consuming to gather this information or you have some legacy application
which can work with (or supports) the 12cR1 database but it's not so easy to replace the client or there could be another issue or reason typical for corporate environment (My story was that somebody didn't do his job by providing required information/action and I couldn't wait with the upgrade any longer so I had to know what to expect after upgrade thus prevent some unexpected behaviour due to old client).
By knowing of exact version we can better predict and address potential implications, issues or bugs (for example by checking existing bug database or knowledge base at MOS site) for that client version.

Question is how we can detect version remotely? What exactly we are looking for? And thanks to my little research (for security related reason) more than ten years ago I knew where to start. For better understanding we have to get answer for those two questions and to answer these questions we can use output from good old SQLNet (server) tracing. I've used 10gR2 client and 12cR1 database for that purpose. In produced trace file we will focus on handshake/negotiation phase where can find the needed hint(s) (Note I'm not going to explain whole structure of trace information nor detailed description of packets, it's not an topic and also it would need separate article for full topic coverage and that's not my intention).

undefined

So we've got first interesting line/entry:
nsconneg: vsn=313, lov=300, opt=0xc01, sdu=2048, tdu=32767, ntc=0x7f08

Where VSN represents the protocol version which can help to identify release of the client. So we can easily find that:

Version         Dec      Hex
-----------------------------
8               310     0x136
9iR1            311     0x137
9iR2            312     0x138
10gR1/10gR2     313     0x139
11gR1/11gR2     314     0x13a
12cR1           315     0x13b

But this method is not sufficient as:
1. This information is not reliable as the value may change. For instance 11.2.0.4 JDBC (in this case provided by Instant client package) contained following entry:
nsconneg: vsn=310, lov=300, opt=0xc41, sdu=8192, tdu=32767, ntc=0x4f98

2. This information doesn't provide exact version number.


So we have the look further and we can see that there is another interesting part wich comes with other two lines and this is the information we are looking for:
nau_rpv: Version received for Linux: Version 10.2.0.5.0 - UNKNOWN
nau_rpv: Connection version for Linux: Version 10.2.0.5.0 - UNKNOWN

Now let's check the trace file again and the corresponding packet in order to find out the location of this information.

undefined

If you look at the above picture you can see the TNS packet and the version in red rectangles. Also note the "DEADBEEF" sequence which is the way how we can identify the right packet in order to get this information. Using utility I've created years ago I translated the Hex value from red rectangles to Dec value and version value in order to check the version:

$ ./oravsn.sh a200500

HEX: A200500 VSNUM: 169870592 VER: 10.2.0.5.0

 And you can do the same thing by different way for example by using the Oracle Database:

SQL> select sys_op_version(to_number('a200500', 'xxxxxxxxx')) version from dual;

VERSION
----------------------------------------
10.2.0.5.0

SQL> select to_number('b200400', 'xxxxxxxxx') vsnum from dual;

     VSNUM
----------
 186647552

 Now we can use this information as input to search in the MOS bug database or knowledge base to find out existing or known issues associated with this version.

Maybe you ask why Oracle clients version 10gR2 and below have "Unknown" value instead of version number. Answer for this question can be found in trace file as well but we have to use 11gR1 client or later. For this purpose I've created another trace file using the 11gR2 client.

undefined

As you can see at the picture above there is SESSION_CLIENT_VERSION sent with the value 186647552 which is decimal representation of Oracle version.

$ ./oravsn.sh 186647552

HEX: b200400 VSNUM: 186647552 VER: 11.2.0.4.0

When you check SQLNet server trace files for clients older than 11gR1 you will find that this information is missing there and that's the reason for an "Unknown" value.

Automatic information gathering for "Unknown"s

Now we have to solve the question how to collect these information automatically and what tools we need to use for that purpose.
Firstly we need to think about the rules and known facts. For instance:
a) overhead must be as low as possible
b) we need to limit information gathering for specific hosts only
c) output should be as minimal as possible
d) Database is running on Linux

Based on above points it's clear that using of server side sqlnet tracing is pointless as it conflicts with a, b and c.
As the Database is running on Linux box with the 2.6 kernel so I chose the solution based on combination of iptables/netfilter (local firewall) and wireshark. Of course in case of different OS I would have to choose different solution.

So let's see the setup:

# iptables -N ORATRACE
# iptables -A ORATRACE -p tcp -m string --hex-string "|deadbeef|" --algo kmp --from 50 --to 110 -j NFLOG --nflog-group 15
# iptables -A ORATRACE -p tcp -j ACCEPT
# iptables -A INPUT -s 192.168.56.1 -p tcp -m state --state ESTABLISHED -m tcp --dport 1521 -j ORATRACE
# iptables -A INPUT -s 192.168.56.101 -p tcp -m state --state ESTABLISHED -m tcp --dport 1521 -j ORATRACE
# iptables -A INPUT -s 192.168.56.102 -p tcp -m state --state ESTABLISHED -m tcp --dport 1521 -j ORATRACE

I've created a new ORATRACE chain. All established connections from three specific hosts (logged in systools.t_oclient_info - see the first section of the article) which are connection to port 1521 (where the Oracle Listener is listening) are redirected to ORATRACE chain. Each packet which comes to ORATRACE chain is checked (using of Knuth–Morris–Pratt string searching algorithm) for presence of the "DEADBEEF" sequence. If this sequence has been found packet is logged using NFLOG. Note that this setting is not permanent use iptables-save in order to save this configuration.

Using a Wireshark tool we read the specific NFLOG group (15) and write it to file in Hexadecimal form as a text to file:

# tshark -i nflog:15 -x >> /home/oracle/oracle_clients.log &

Now we can periodically check the oracle_clients.log file and if there is existing entry for specific source IP address we can remove specific rule for that address from the iptables (You can eventually write and setup script for that purpose).
Now we check what we've got.

At first content of one of logged packet:

 24.460554 192.168.56.1 -> 192.168.56.103 TNS 292 Request, Data (6), SNS

0000  02 00 00 0f 08 00 01 00 08 00 01 02 05 00 0a 00   ................
0010  00 00 00 00 08 00 04 00 00 00 00 02 10 00 08 00   ................
0020  00 06 20 02 0a 00 27 00 00 00 45 81 06 00 0f 00   .. ...'...E.....
0030  00 01 00 00 06 00 11 00 00 0e 00 00 12 00 10 00   ................
0040  08 00 27 1e 87 94 0a 00 27 00 00 00 08 00 00 00   ..'.....'.......
0050  d4 00 09 00 45 00 00 d0 13 b4 40 00 40 06 34 bb   ....E.....@.@.4.
0060  c0 a8 38 01 c0 a8 38 67 c4 d5 05 f1 83 88 54 66   ..8...8g......Tf
0070  8d 96 78 7b 80 18 00 e5 0b 57 00 00 01 01 08 0a   ..x{.....W......
0080  04 f4 41 41 01 ae a9 05 00 9c 00 00 06 00 00 00   ..AA............
0090  00 00 de ad be ef 00 92 0a 20 04 00 00 04 00 00   ......... ......
00a0  04 00 03 00 00 00 00 00 04 00 05 0a 20 04 00 00   ............ ...
00b0  08 00 01 00 00 76 4f fd 8f 35 88 00 12 00 01 de   .....vO..5......
00c0  ad be ef 00 03 00 00 00 04 00 04 00 01 00 01 00   ................
00d0  02 00 01 00 03 00 00 00 00 00 04 00 05 0a 20 04   .............. .
00e0  00 00 02 00 03 e0 e1 00 02 00 06 fc ff 00 02 00   ................
00f0  02 00 00 00 00 00 04 00 05 0a 20 04 00 00 0c 00   .......... .....
0100  01 00 11 06 10 0c 0f 0a 0b 08 02 01 03 00 03 00   ................
0110  02 00 00 00 00 00 04 00 05 0a 20 04 00 00 03 00   .......... .....
0120  01 00 03 01

And now we use grep for shortening the output in order for better reading:

$ cat oracle_clients.log | egrep 'TNS|be ef'| grep -v 00c0
 21.740456 192.168.56.102 -> 192.168.56.103 TNS 292 Request, Data (6), SNS
0090  00 00 de ad be ef 00 92 09 20 08 00 00 04 00 00   ......... ......
 24.460554 192.168.56.1 -> 192.168.56.103 TNS 292 Request, Data (6), SNS
0090  00 00 de ad be ef 00 92 0a 20 04 00 00 04 00 00   ......... ......
 27.850304 192.168.56.101 -> 192.168.56.103 TNS 292 Request, Data (6), SNS
0090  00 00 de ad be ef 00 92 0a 10 05 00 00 04 00 00   ................

$ ./oravsn.sh a200400

HEX: a200400 VSNUM: 169870336 VER: 10.2.0.4.0

$ ./oravsn.sh a100500

HEX: a100500 VSNUM: 168822016 VER: 10.1.0.5.0

$ ./oravsn.sh 9200800

HEX: 9200800 VSNUM: 153094144 VER: 9.2.0.8.0

According to above output host 192.168.56.1 is connecting using 10.2.0.4 version of client, host 192.168.56.101 using 10.1.0.5 and third host 192.168.56.102 shows is using 9.2.0.8 version of client (which makes him #1 candidate for client upgrade).

 Final thoughts

Be aware of that as Oracle Database comes with new features by each release, it also comes with changes in SQLNet protocol (since 11gR1 it has changed significantly), changes of default settings (SDU for example) and changes in authentication. Keep on mind these facts when you plan to upgrade your Database so you have to put a spotlight on Database client as well. It's possible to upgrade clients after upgrade of Database but I suggest to do that as first step (before Database upgrade) whenever it's possible. Otherwise you should be at least aware of possible consequences and implications of using older client version.

 

Be(a)ware of Licensing policy changes regarding Infrastructure Repository Databases

Oracle Database 12c comes with another significant change regarding using of Infrastructure Repository Databases and which could eventually make things more complicated. Before upgrade of Oracle Database, Database Administrator or a person responsible for Software Asset Management (and Licensing) should be familiar with changes in Licensing policy which come with new release and their implications. At first let me start with little comparison of probably the most frequently used versions in production 10gR2, 11gR2 and new 12cR1 to see an "evolution".

 

Here is an excerpt from the Licensing Guide and Enterprise Manager Licensing Information Guide for 10gR2:

"Infrastructure Repository Databases

A separate Oracle Database can be installed and used as a Recovery Manager (RMAN) repository without additional license requirements, provided that all the Oracle databases managed in this repository are correctly licensed. This repository database may also be used for the Oracle Enterprise Grid Control repository. It may not be used or deployed for other uses.

A separate Oracle Database can be installed and used as an Oracle Enterprise Manager Grid Control (OEM Grid Control) repository without additional license requirements, provided that all the targets (databases, applications, and so forth) managed in this repository are correctly licensed. This database may also be used for the RMAN repository. It may not be used or deployed for other uses.

Enterprise Manager Restricted-use License

Enterprise Manager includes a restricted-use license of the Oracle Database for use as the Oracle Management Repository only."

 

Here is the excerpt from the same guides for 11gR2:

"Infrastructure Repository Databases

A separate Oracle Database can be installed and used as a Recovery Manager (RMAN) repository without additional license requirements, provided that all the Oracle databases managed in this repository are correctly licensed. This repository database may also be used for the Oracle Enterprise Grid Control repository. It may not be used or deployed for other uses.

A separate Oracle Database can be installed and used as an Oracle Enterprise Manager Grid Control (OEM Grid Control) repository without additional license requirements, provided that all the targets (databases, applications, and so forth) managed in this repository are correctly licensed. This database may also be used for the RMAN repository. It may not be used or deployed for other uses.

 Enterprise Manager Restricted-use License

Enterprise Manager includes a restricted-use license of the Oracle Database for use as the Oracle Management Repository only.

Additional database options or additional servers for disaster recovery require separate licensing. Customers receive one single-instance database with the Grid Control, or RMAN, repository. To protect the repository with Data Guard, customers need to purchase a license for the standby site. To protect the repository with Oracle Real Application Clusters, customers must license the second node for the database, and both nodes require an Oracle Real Application Clusters license."

 

And here is the excerpt from the Licensing Information Guide guide for 12cR1:

"Infrastructure Repository Databases

A separate single instance Oracle Database can be installed and used as an infrastructure repository for RMAN, Oracle Enterprise Manager Cloud Control, Automatic Workload Repository (AWR) Warehouse, Global Data Services Catalog, and Grid Infrastructure Management Repository without additional license requirements, provided that all the targets are correctly licensed. It may not be used or deployed for other uses.

The infrastructure repositories for RMAN, Oracle Enterprise Manager Cloud Control, AWR Warehouse, and Global Data Services Catalog can be shared in one database, or deployed in separate databases. Enterprise Edition must be used for the infrastructure repository database(s).

If Oracle RAC is used for the infrastructure database, then Oracle RAC licenses for all servers, and Enterprise Edition licenses for all but the first server, are required.

If Data Guard is used for the infrastructure database, then an Enterprise Edition license for the standby server is required."

 

By comparison of above excerpts where you can see differences and changes regarding Infrastructure Repository Databases (later just IRD) for particular release. Since 12c (12cR1) there is a significant change regarding edition of the Oracle Database. Current 12c Guide simply says:

  1. If Standard Edition is used as IRD and you want to upgrade the repository database to 12c, then you have to migrate it to Enterprise Edition (later just EE)
  2. If current repository database is running in RAC mode, second or any additional node node is extra licensed (EE), moreover all servers must have RAC option licensed
  3. If current repository database is using Data Guard, a standby server has to be licensed extra licensed

 Well honestly in days of "cost cutting" where many companies are more and more looking at/for alternative solutions or even they are already in process of migration from Oracle to alternative solutions, claiming that "Oracle is expensive", then this is definitely backward step from the Oracle Corp. Oracle Database 12c comes with quite interesting and very useful features regarding Recovery Manager (RMAN) which is teasing us to use new version (of course for manipulation witch PDB's 12c version of RMAN is a must). But still there is a trade-off. To stay with the 11gR2 Recovery Manager thus use old 11gR2 Infrastructure Repository Database and as long as possible (forget about 12c RMAN features including multitenant) or even trying some other workarounds (e.g. relocate RMAN repository to some fully licensed servers or divide centralized repository (catalog) to several (repositories) catalogs and spread it across licensed HA databases) in order to prevent pay additional fees for high available Infrastructure Database. Honestly I would expect a little favor here.

But enough of blaming Oracle and let's see an example why Recovery Manager requires Enterprise Edition and what would happen when you try to use Standard Edition as a RMAN repository.

I have prepared fresh 12.1.0.1 Standard edition (12.1.0.2 isn't still available) using DB_NAME=RMANDB as a repository for RMAN catalog(s) in order to see whether EE is explicitly requested.

12.2.0.2 Enterprise Edition (EE) is used as "target" database using DBNAME=ORA12CR1

 

RMANDB:SQL> select banner from v$version where rownum = 1;

BANNER
--------------------------------------------------------------------------------
Oracle Database 12c Release 12.1.0.1.0 - 64bit Production


RMANDB:SQL> create user rman12 identified by rman default tablespace data;

User created.

RMANDB:SQL> grant recovery_catalog_owner, unlimited tablespace to rman12;

Grant succeeded.

 

Used rman command on future "target" DB host (12.1.0.2 EE):

RMAN> connect catalog rman12/rman@rmandb

connected to recovery catalog database
ORACLE error from recovery catalog database: ORA-06550: line 1, column 41:
PLS-00201: identifier 'DBMS_RCVCAT.GETPACKAGEVERSION' must be declared
ORA-06550: line 1, column 34:
PL/SQL: Statement ignored

If you got this error then you have to execute @?/rdbms/admin/dbmsrmansys.sql in order to fix this issue (I had to use this script from 12.1.0.2 home as my SE is 12.1.0.1).

 

Let's continue with creating a catalog:

RMAN> connect catalog rman12/rman@rmandb

connected to recovery catalog database

RMAN> create catalog;

error creating create_deleted_object_seq
ORACLE error from recovery catalog database: ORA-00439: feature not enabled: Partitioning

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-06433: error installing recovery catalog

If you've got error as above, it means you aren't using Enteprise Edition as Partitioning requires EE or Partitioning option is not enabled.

 

RMAN> connect catalog rman12/rman@rmandb

connected to recovery catalog database

RMAN> create catalog

recovery catalog created
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-06004: ORACLE error from recovery catalog database: ORA-00439: feature not enabled: Fine-grained access control

And finally if you've got this one, it means that you aren't using Enteprise Edition (as Fine-grained access control is available in EE) and moreover you did very nasty thing in order to overcome previous error.

 New RMAN features rely on Partitioning, Virtual Private Database and Fine-grained access control (which is a part of VPD) which definitely require Enterprise Edition. If you are using centralized repository for RMAN, you should be aware of this important change which might cause additional costs. While VPD (Fine-grained access control) is not extra licensed , question whether Partitioning Option needs to be licensed remain unanswered. As always, you should contact your Oracle Sales representative in order to consult all question regarding licensing and to learn all implications according the changes.

 

Fun part: Creating 12c RMAN catalog Oracle Database 12c Standard Edition (just illustration for fun, don't use it in production!):

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SYS@ORA12CR1:SQL> create user rmanbckp identified by rmanbckp;

User created.

SYS@ORA12CR1:SQL> grant sysbackup to rmanbckp;

Grant succeeded.

SYS@ORA12CR1:SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
[oracle@lab00 ~]$ sqlplus "sys@rmandb / as sysdba"

SQL*Plus: Release 12.1.0.2.0 Production on Wed Jul 29 20:46:54 2015

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

Enter password:

Connected to:
Oracle Database 12c Release 12.1.0.1.0 - 64bit Production

SYS@RMANDB:SQL> select edition from v$instance;

EDITION
-------
SE

SYS@RMANDB:SQL> create user rman12 identified by rman default tablespace data;

User created.

SYS@RMANDB:SQL> grant recovery_catalog_owner, unlimited tablespace to rman12;

Grant succeeded.

SYS@RMANDB:SQL> exit
Disconnected from Oracle Database 12c Release 12.1.0.1.0 - 64bit Production
[oracle@lab00 ~]$ rman catalog rman12/rman@rmandb

Recovery Manager: Release 12.1.0.2.0 - Production on Wed Jul 29 20:50:49 2015

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

connected to recovery catalog database

RMAN> create catalog

recovery catalog created

RMAN> connect target "rmanbckp/rmanbckp@ora12cr1 as sysbackup"

connected to target database: ORA12CR1 (DBID=1346475582)

RMAN> register database;

database registered in recovery catalog
starting full resync of recovery catalog
full resync complete

RMAN> backup database;

Starting backup at 29-JUL-15
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=42 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00004 name=/storage/oradata00/ORA12CR1/data01.dbf
input datafile file number=00001 name=/storage/oradata00/ORA12CR1/system01.dbf
input datafile file number=00002 name=/storage/oradata00/ORA12CR1/sysaux01.dbf
input datafile file number=00003 name=/storage/oradata00/ORA12CR1/undo01.dbf
channel ORA_DISK_1: starting piece 1 at 29-JUL-15
channel ORA_DISK_1: finished piece 1 at 29-JUL-15
piece handle=/storage/oradata01/ORA12CR1/ARC/ORA12CR1/backupset/2015_07_29/o1_mf_nnndf_TAG20150729T205310_bvl88pd1_.bkp tag=TAG20150729T205310 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
channel ORA_DISK_1: starting piece 1 at 29-JUL-15
channel ORA_DISK_1: finished piece 1 at 29-JUL-15
piece handle=/storage/oradata01/ORA12CR1/ARC/ORA12CR1/backupset/2015_07_29/o1_mf_ncnnf_TAG20150729T205310_bvl896n3_.bkp tag=TAG20150729T205310 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 29-JUL-15

RMAN>

 

As you can see using some (secret) tweaks I was able to create Oracle 12c Standard Edition for RMAN catalog. Of course this is not valid solution and it is not possible to use it for production as SE is not supported and it in fact violates the license. So no SE officially. But technically with couple of really nasty tweaks it is possible use SE as RMAN repository. ;-)

 

Change (or bug) regarding parsing of tnsnames.ora in 12c

We have experienced problem with invalid database links after upgrade of one our databases to 12cR1. Certain applications which used to work properly for many years and many releases of Oracle Database begun throwing an well known error: ORA-12154: TNS:could not resolve the connect identifier specified. We were sure that neither tnsnames.ora or sqlnet.ora had been modified. Let see the typical settings of tnsnames.ora used in our case:

[oracle@lab00 ~]$ cat tnsnames.ora
ORA12CR1.test, ORA12CR1, OTHERALIAS.test =
 (DESCRIPTION =
   (ADDRESS_LIST =
     (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.100)(PORT = 1521))
   )
 (CONNECT_DATA =
   (SERVICE_NAME = ORA12CR1.test)
 )
)

What we've got here. One (apparently valid) entry which has three aliases.

Now we'll create database links for each of aliases and we perform simple test of validity. We'll use the same file for 11gR2 and 12cR1 database.

12cR1 first:

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options


SYS@ORA12CR1:SQL> CREATE DATABASE LINK link_a CONNECT TO test IDENTIFIED BY test USING 'ora12cr1';

Database link created.

SYS@ORA12CR1:SQL> CREATE DATABASE LINK link_b CONNECT TO test IDENTIFIED BY test USING 'ora12cr1.test';

Database link created.

SYS@ORA12CR1:SQL> CREATE DATABASE LINK link_c CONNECT TO test IDENTIFIED BY test USING 'otheralias.test';

Database link created.

SYS@ORA12CR1:SQL> select sysdate from dual@link_a;

SYSDATE
---------
06-JUN-15

SYS@ORA12CR1:SQL> select sysdate from dual@link_b;
select sysdate from dual@link_b
                         *
ERROR at line 1:
ORA-12154: TNS:could not resolve the connect identifier specified


SYS@ORA12CR1:SQL> select sysdate from dual@link_c;

SYSDATE
---------
06-JUN-15

Now 11gR2:

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SYS@ORA11GR2:SQL> CREATE DATABASE LINK link_a CONNECT TO test IDENTIFIED BY test USING 'ora12cr1';

Database link created.

SYS@ORA11GR2:SQL> CREATE DATABASE LINK link_b CONNECT TO test IDENTIFIED BY test USING 'ora12cr1.test';

Database link created.

SYS@ORA11GR2:SQL> CREATE DATABASE LINK link_c CONNECT TO test IDENTIFIED BY test USING 'otheralias.test';

Database link created.

SYS@ORA11GR2:SQL> select sysdate from dual@link_a;

SYSDATE
---------
06-JUN-15

SYS@ORA11GR2:SQL> select sysdate from dual@link_b;

SYSDATE
---------
06-JUN-15

SYS@ORA11GR2:SQL> select sysdate from dual@link_c;

SYSDATE
---------
06-JUN-15

 When you compare above results all three links work on 11gR2 while link_b on 12cR1 doesn't. 12cR1 simply is unable to recognize ORA12CR1.TEST alias in tnsnames.ora entry. Ok, that was about server software, what about client software? I expected the same results but as always I prefer test before making a conclusion.

12cR1 client - same problem:

[oracle@lab00 ~]$ tnsping ora12cr1

TNS Ping Utility for Linux: Version 12.1.0.2.0 - Production on 06-JUN-2015 14:47:30

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

Used parameter files:


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.100)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = ORA12CR1.test)))
OK (10 msec)
[oracle@lab00 ~]$ tnsping ora12cr1.test

TNS Ping Utility for Linux: Version 12.1.0.2.0 - Production on 06-JUN-2015 14:47:36

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

Used parameter files:

TNS-03505: Failed to resolve name

11gR2 client - works well:

[oracle@lab00 ~]$ tnsping ora12cr1

TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 06-JUN-2015 14:48:18

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

Used parameter files:


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.100)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = ORA12CR1.test)))
OK (0 msec)
[oracle@lab00 ~]$ tnsping ora12cr1.test

TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 06-JUN-2015 14:48:25

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

Used parameter files:


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.100)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = ORA12CR1.test)))
OK (0 msec)

11gR1 client - works well (skipped output from ora12cr1)

[oracle@lab00 ~]$ tnsping ora12cr1.test

TNS Ping Utility for Linux: Version 11.1.0.7.0 - Production on 06-JUN-2015 14:49:54

Copyright (c) 1997, 2008, Oracle.  All rights reserved.

Used parameter files:


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.100)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = ORA12CR1.test)))
OK (0 msec)

10gR2 client - works well (skipped output from ora12cr1

[oracle@lab00 ~]$ tnsping ora12cr1.test

TNS Ping Utility for Linux: Version 10.2.0.5.0 - Production on 06-JUN-2015 14:50:34

Copyright (c) 1997,  2010, Oracle.  All rights reserved.

Used parameter files:


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.100)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = ORA12CR1.test)))
OK (0 msec)

9iR2 client - works well (skipped output from ora12cr1)

[oracle@lab00 ~]$ tnsping ora12cr1.test

TNS Ping Utility for Linux: Version 9.2.0.8.0 - Production on 06-JUN-2015 14:51:20

Copyright (c) 1997, 2006, Oracle Corporation.  All rights reserved.

Used parameter files:


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.100)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = ORA12CR1.test)))
OK (0 msec)

As I said earlier this thing used to work for many years and releases. Question is whether it is a bug in parsing or planned change since 12c. In my opinion it's a bug in probably in parsing because the OTHERALIAS.test has worked fine. Obviously you now can't have the short name alias along with alias with domain in one entry because in that case the alias with domain name is being "ignored" while short name alias works. Let's do another test as a proof of this statement. We put another alias OTHERALIAS (without domain name) for existing entry and we create another database link (named link_d) using that alias. Expected result of test is link_b and link_c will get an ORA-12154 error (note that link_c has worked before).

[oracle@lab00 ~]$ cat tnsnames.ora 
ORA12CR1.test, ORA12CR1, OTHERALIAS.test, OTHERALIAS =
 (DESCRIPTION =
   (ADDRESS_LIST =
     (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.100)(PORT = 1521))
   )
 (CONNECT_DATA =
   (SERVICE_NAME = ORA12CR1.test)
 )
)
[oracle@lab00 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Tue Jun 6 15:11:10 2015

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SYS@ORA12CR1:SQL> CREATE DATABASE LINK link_d CONNECT TO test IDENTIFIED BY test USING 'otheralias';

Database link created.

SYS@ORA12CR1:SQL> select sysdate from dual@link_a;

SYSDATE
---------
06-JUN-15

SYS@ORA12CR1:SQL> select sysdate from dual@link_b;
select sysdate from dual@link_b
                         *
ERROR at line 1:
ORA-12154: TNS:could not resolve the connect identifier specified


SYS@ORA12CR1:SQL> select sysdate from dual@link_c;
select sysdate from dual@link_c
                         *
ERROR at line 1:
ORA-12154: TNS:could not resolve the connect identifier specified


SYS@ORA12CR1:SQL> select sysdate from dual@link_d;

SYSDATE
---------
06-JUN-15

Solution is quite simple. You can't mix shortname (e.g. ORA12CR1) aliases with the same aliases with domain suffix (ORA12CR1.TEST). So in this case we need to create another separate entry for conflicting aliases.

[oracle@lab00 ~]$ cat tnsnames.ora 
ORA12CR1.test, OTHERALIAS.test =
 (DESCRIPTION =
   (ADDRESS_LIST =
     (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.100)(PORT = 1521))
   )
 (CONNECT_DATA =
   (SERVICE_NAME = ORA12CR1.test)
 )
)

ORA12CR1, OTHERALIAS =
 (DESCRIPTION =
   (ADDRESS_LIST =
     (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.100)(PORT = 1521))
   )
 (CONNECT_DATA =
   (SERVICE_NAME = ORA12CR1.test)
 )
)
[oracle@lab00 admin]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Tue Jun 6 15:39:49 2015

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SYS@ORA12CR1:SQL> select sysdate from dual@link_a;

SYSDATE
---------
06-JUN-15

SYS@ORA12CR1:SQL> select sysdate from dual@link_b;
                                                                                                                                                                                            
SYSDATE                                                                                                                                                                                     
---------                                                                                                                                                                                   
06-JUN-15                                                                                                                                                                                   
                                                                                                                                                                                            
SYS@ORA12CR1:SQL> select sysdate from dual@link_c;                                                                                                                                          
                                                                                                                                                                                            
SYSDATE                                                                                                                                                                                     
---------                                                                                                                                                                                   
06-JUN-15                                                                                                                                                                                   
                                                                                                                                                                                            
SYS@ORA12CR1:SQL> select sysdate from dual@link_d;

SYSDATE
---------
06-JUN-15

 As you can see this solution works...

Beware of Adaptive features in 12cR1 RAC

After upgrade of our testing database to 12cR1 (12.1.0.2 + PSU April 15) I've got a claim that database is not performing well, moreover login process takes significant long time.

I did several upgrades before but this one was first RAC database. During review of performance I've found a main difference between single instance database and RAC. Each session has performed these two queries:

 

SQL_ID        SQL_FULLTEXT
------------- --------------------------------------------------------------------------------
frjd8zfy2jfdq SELECT executions, end_of_fetch_count,              elapsed_time/px_servers elap
              sed_time,        cpu_time/px_servers     cpu_time,            buffer_gets/execut
              ions  buffer_gets   FROM (SELECT sum(executions)   as executions,
                           sum(case when px_servers_executions > 0
                then px_servers_executions                                  else executions en
              d) as px_servers,                sum(end_of_fetch_count) as end_of_fetch_count,
                           sum(elapsed_time) as elapsed_time,                 sum(cpu_time)
                as cpu_time,                     sum(buffer_gets)  as buffer_gets            F
              ROM   gv$sql                                      WHERE executions > 0
                                     AND sql_id = :1                                    AND pa
              rsing_schema_name = :2)



SQL_ID        SQL_FULLTEXT
------------- --------------------------------------------------------------------------------
4b4wp0a8dvkf0 SELECT executions, end_of_fetch_count,              elapsed_time/px_servers elap
              sed_time,        cpu_time/px_servers     cpu_time,            buffer_gets/execut
              ions  buffer_gets   FROM (SELECT sum(executions_delta) as EXECUTIONS,
                                 sum(case when px_servers_execs_delta > 0
                            then px_servers_execs_delta
              else executions_delta end) as px_servers,                sum(end_of_fetch_count_
              delta) as end_of_fetch_count,              sum(elapsed_time_delta) as ELAPSED_TI
              ME,              sum(cpu_time_delta) as CPU_TIME,                      sum(buffe
              r_gets_delta) as BUFFER_GETS          FROM   DBA_HIST_SQLSTAT s,
                                 V$DATABASE d,                                         DBA_HIS
              T_SNAPSHOT sn                           WHERE  s.dbid = d.dbid
                              AND  bitand(nvl(s.flag, 0), 1) = 0                    AND  sn.en
              d_interval_time >                                   (select systimestamp at TIME
               ZONE dbtimezone                  from dual) - 7                               A
              ND  s.sql_id = :1                                    AND  s.snap_id = sn.snap_id
                                         AND  s.instance_number = sn.instance_number
               AND  s.dbid = sn.dbid                                 AND  parsing_schema_name
              = :2)

 

 

 

About 12c new feature: Online Datafile move

I consider as best of the new features those which allow to perform a maintenance or structure changes with no requirement to close the database or putting any part of it offline and especially when this feature is not bound to an extra paid option.
One of those comes with Oracle Database 12cR1 version and it can help reduce downtime for operation during which until 11gR2 needed downtime of database or tablespace needed to be offline. Simply said now you can move datafile online while the database/tablespace is open/online.

So we've got new command which we can use either to rename or relocate datafile:

ALTER DATABASE MOVE DATAFILE 'oldfilename' TO 'newfilename';

- Before moving or renaming of datafile ensure that you have sufficient free disk space that at minimum equals to size of moved datafile.

- Above command works for datafiles and it doesn't work for control files, temporary files or redo logs

- Command works irrespective of ARCHIVELOG or NOARCHIVELOG mode

- FLASHBACK DATABASE doesn't have any impact to this operation

- As always, do full backup of database after operation  (and do backup before as well)

Anyway, the idea behind this post is not to replace documentation (for detailed information about command and it's options take a look here: http://docs.oracle.com/database/121/ADMIN/dfiles.htm#ADMIN13837) but to test how much time this operation needs to complete (or what is the expected overhead) and comparing to existing solution(s) used until now. I've skipped the rename of datafile on OS level as it doesn't have sense because operation using "mv" command is being done within a second while using MOVE DATAFILE method performs a full copy of datafile first. So this test consist of moving datafile only.

At first, the baseline using dd and cp commands which are common methods in existing solutions prior 12c (skipped shutdown/startup mount, tablespace offline/online operations and move/rename commands):

[oracle@lab00 ORA12CR1]$ time dd if=data01.dbf of=/storage/oradata01/ORA12CR1/newdata01.dbf bs=16384
1966081+0 records in
1966081+0 records out
32212271104 bytes (32 GB) copied, 29.0502 s, 1.1 GB/s

real    0m29.051s
user    0m0.286s
sys     0m28.719s

[oracle@lab00 ORA12CR1]$ time cp data02.dbf /storage/oradata01/ORA12CR1/newdata02.dbf

real    0m27.204s
user    0m0.074s
sys     0m27.094s

 Second test using DBMS_FILE_TRANSFER package:

SYS@ORA12CR1:SQL> create directory SRCDIR as '/storage/oradata00/ORA12CR1';

Directory created.

SYS@ORA12CR1:SQL> create directory TGTDIR as '/storage/oradata01/ORA12CR1';

Directory created.

SYS@ORA12CR1:SQL> set timing on
SYS@ORA12CR1:SQL> ALTER TABLESPACE TEST offline;

Tablespace altered.

Elapsed: 00:00:00.11
SYS@ORA12CR1:SQL> BEGIN DBMS_FILE_TRANSFER.COPY_FILE('SRCDIR','data01.dbf', 'TGTDIR', 'newdata01.dbf'); END;  
  2  /

PL/SQL procedure successfully completed.

Elapsed: 00:01:15.76
SYS@ORA12CR1:SQL> ALTER TABLESPACE test RENAME DATAFILE '/storage/oradata00/ORA12CR1/data01.dbf' TO '/storage/oradata01/ORA12CR1/newdata01.dbf';

Tablespace altered.

Elapsed: 00:00:00.06
SYS@:SQL> ALTER TABLESPACE data ONLINE;

Tablespace altered.

Elapsed: 00:00:00.08

Now with new feature, online move operation:

SYS@ORA12CR1:SQL> set timing on
SYS@ORA12CR1:SQL> ALTER DATABASE MOVE DATAFILE '/storage/oradata00/ORA12CR1/data01.dbf' TO '/storage/oradata01/ORA12CR1/newdata01.dbf';

Database altered.

Elapsed: 00:07:42.79

 We can see significant difference between old method (offline tablespace) and new feature (online datafile move). I would say it's a tax or penalty for online operation.

According to times as above I would personally choose the "offline" method in case of huge or massive migration of datafiles and especially when time matters. But in case of mission critical system where of course the uptime is the main target and in case serveral changes have to be made or mean time for the operation is not important then this option becomes very handy.

 

SecureFiles - using, licensing and changes in 12c

SecureFiles were introduced in 11gR1 as a replacement  for (or better enhancement of) existing LOB storage architecture (now known as BasicFiles) which used to be the default storage until 12cR1. You can of course use both types together in the same table.

Idea behind this post is to discuss changes in12c and also to eliminate confusion which is still around this feature and this confusion is very often created due to misinterpretation of Licensing terms.

The irony is that there are some companies specialized for Software Asset Management are very often providing partial or vague information which creates misinterpretations and confusion on customer's side or even worse they are presenting these misinterpretations to customers.

First of all there is no licensing implication in use of SecureFiles (you don't have to pay additional fees) unless you are using any of option such as Compression, Deduplication or Encryption.

According to Oracle Database documentation and Oracle Database Licensing information you need license only for:

  • Compression - Advanced Compression option
  • Deduplication - Advanced Compression option
  • Encryption - Advanced Security option

 Let's create a tables using standard command without additional options.

SYS@ORA11GR2:SQL> CREATE TABLE test.t1 ( a CLOB, b BLOB) LOB(a, b) STORE AS SECUREFILE;

Table created.

SYS@ORA11GR2:SQL> col table_name format a15
SYS@ORA11GR2:SQL> col segment_name format a32
SYS@ORA11GR2:SQL> select table_name, segment_name, securefile, encrypt, compression, deduplication from dba_lobs where owner = 'TEST';

TABLE_NAME      SEGMENT_NAME                     SEC ENCR COMPRE DEDUPLICATION
--------------- -------------------------------- --- ---- ------ ---------------
T1              SYS_LOB0000024831C00001$$        YES NO   NO     NO
T1              SYS_LOB0000024831C00002$$        YES NO   NO     NO

If you see similar output everything is fine and you're not required to pay any additional licensed option. There is a quotation of Kevin Jernigan, Senior Director Product Management at Oracle which confirm this conclusion:

"The change we made in Oracle Database 12c wrt SecureFiles and LOBs is that SecureFiles is now the default. So, when you create a LOB column in a table, if you don't specify BasicFiles or SecureFiles, it will default to SecureFiles. There is no change to licensing requirements - SecureFiles is available in all editions of Oracle Database 12c with no licensing requirements beyond the database license, and using the compression and deduplication features of SecureFiles LOBs requires licensing the Advanced Compression Option.

-KJ"

 Above statement talks about another important change which comes with 12cR1. SecureFiles is now default LOB storage. Until now you had to specify using of SecureFiles explicitly, now it's implicit. Let's check it.

SYS@ORA12CR1:SQL> CREATE TABLE test.t1 ( a CLOB, b BLOB);

Table created.

SYS@ORA12CR1:SQL> col table_name format a15
SYS@ORA12CR1:SQL> col segment_name format a32
SYS@ORA12CR1:SQL> select table_name, segment_name, securefile, encrypt, compression, deduplication from dba_lobs where owner = 'TEST';

TABLE_NAME      SEGMENT_NAME                     SEC ENCR COMPRE DEDUPLICATION
--------------- -------------------------------- --- ---- ------ ---------------
T1              SYS_LOB0000024836C00001$$        YES NO   NO     NO
T1              SYS_LOB0000024836C00002$$        YES NO   NO     NO

 This behaviour is caused by change of default value of DB_SECUREFILE instance parameter from PERMITTED to PREFERRED.

 Note that this parameter affects results of upgrade to 12c. Consider to remove (if explicitly set) this parameter before upgrade database.

Results of upgrade when DB_SECUREFILE is set to PERMITTED (default value for 11gR1 and 11gR2), PREFERRED (default value for 12cR1) and ALWAYS:

SYS@ORA12CR1> col owner format a20
SYS@ORA12CR1> select distinct owner, count(*) from dba_lobs where securefile = 'YES' group by owner;

OWNER                  COUNT(*)
-------------------- ----------
GSMADMIN_INTERNAL             1
XDB                         291
SYS                          19

Results of upgrade when DB_SECUREFILE is set to NEVER or IGNORE:

SYS@ORA12CR1> col owner format a20
SYS@ORA12CR1> select distinct owner, count(*) from dba_lobs where securefile = 'YES' group by owner;

no rows selected

 

Another change which comes with Oracle Database 12c is support for parallel DML operations for LOB columns stored as SecureFiles LOBs in non-partitioned tables and Direct load support for SecureFiles LOB columns that have context index defined on them. This extends Parallel DML functionality introduced in Oracle Database 11gR2. Note Parallel DML support applies also to BasicFiles in case of partitioned tables.

To be continued...

Home