Oakies Blog Aggregator

Changes to the Oracle preinstall RPMs in OL 7.3 and OL 7.4

For quite some time now Oracle has documented the use of the so-called preinstall RPMs to prepare Oracle Linux for the installation of the Oracle database software. I think that’s a great idea if the settings applied by the RPM fit your environment. If I find the time, I’ll write a blog post about what it does specifically in a little while. It definitely fits my lab environment, and I regularly kickstart my OL 7 VMs specifying the preinstall RPM in the %packages section.

When upgrading the current base image from Oracle Linux 7.2 to Oracle 7.4/Oracle 12.2 I noticed a few changes to the preinstall RPMs.

IMPORTANT NOTE If you find this post via an Internet search engine please bear in mind that Oracle could have changed the preinstall RPM since this article was posted in September 2017. Make sure you check with the most current version to see the problem I ran into still exists

Different name

The first surprise came when I changed the virt-install command to reference the Oracle Linux 7.4 ISO instead of 7.2. During the installation session a warning appeared stating that there wasn’t a package named oracle-rdbms-server-12cR1-preinstall anywhere to be found. Really? OK … that was a bit strange. I went straight off to check the public-yum repository for Oracle Linux 7.4 and indeed, there wasn’t the RPM I was looking for. All I could find was a package named oracle-database-server-12cR2-preinstall. All right then, I’ll use that instead. A change to the kickstart file was all that needed to be done, and subsequent installations completed ok.

Except for this other problem …

No more dependencies on the C compiler suite

The other night I wanted to apply a patch to the 12.2 lab cluster using opatchauto. The patching process ran into an error on node 2, and then failed completely trying to roll the patch back. Usually that’s a sad story (and the reason why you create backups before patching) but this time I was lucky and didn’t have to restore. Well, lucky in a way.

Going through the opatch log files (the error reported had something to do with a failure to relink a target in ins_rdbms.mk) I noticed that Oracle complained about not finding gcc. That struck me as odd, and I thought I’d actually seen gcc installed on my 7.2 base image with the 12.1 preinstall RPM.

A quick check reveals that the 12.1 preinstall RPM (taken from the Oracle Linux 7.2 base repository that I used previously) actually defines a dependency on gcc:

[oracle@server1 ~]$ rpm -qpi --requires /tmp/oracle-rdbms-server-12cR1-preinstall-1.0-4.el7.x86_64.rpm
Name        : oracle-rdbms-server-12cR1-preinstall
Version     : 1.0
Release     : 4.el7
Architecture: x86_64
Install Date: (not installed)
Group       : Test Environment/Libraries
Size        : 44346
License     : GPLv2
Signature   : RSA/SHA256, Wed 14 Oct 2015 02:18:22 AM EDT, Key ID 72f97b74ec551f03
Source RPM  : oracle-rdbms-server-12cR1-preinstall-1.0-4.el7.src.rpm
Build Date  : Wed 14 Oct 2015 02:18:19 AM EDT
Build Host  : x86-ol7-builder-01.us.oracle.com
Relocations : (not relocatable)
Vendor      : Oracle
Summary     : Sets the system for Oracle Database single instance 
              and Real Application Cluster install for Oracle Linux 7
Description :
The Oracle Preinstallation RPM package installs software packages and 
sets system parameters required for Oracle Database single instance and 
Oracle Real Application Clusters installations for Oracle Linux Release 7

Files affected: /etc/sysctl.conf, /boot/grub/menu.lst OR /boot/grub2/grub.cfg
Files added: /etc/security/limits.d/oracle-rdbms-server-12cR1-preinstall.conf
/bin/bash
/bin/sh
/bin/sh
/bin/sh
/bin/sh
/etc/redhat-release
bind-utils
binutils
compat-libcap1
compat-libstdc++-33
config(oracle-rdbms-server-12cR1-preinstall) = 1.0-4.el7
ethtool
gcc
gcc-c++
glibc
glibc-devel
initscripts
kernel-uek
ksh
libaio
libaio-devel
libgcc
libstdc++
libstdc++-devel
make
module-init-tools
nfs-utils
openssh-clients
pam
procps
psmisc
rpmlib(CompressedFileNames) <= 3.0.4-1
rpmlib(FileDigests) <= 4.6.0-1
rpmlib(PayloadFilesHavePrefix) <= 4.0-1
smartmontools
sysstat
util-linux-ng
xorg-x11-utils
xorg-x11-xauth
rpmlib(PayloadIsXz) <= 5.2-1

As you can see, gcc (and even gcc-c++) feature quite prominently in the list of requirements.

The change must have come with Oracle Linux 7.3, but I haven’t noticed it until now. I checked the Oracle Linux 7.4 base repository on public-yum.oracle.com and noticed the following:

  • The 11g Release 2 preinstall RPM is gone (it was still available in the OL 7.3 repository)
  • The 12c Release 1 preinstall RPM is gone, too (it had already disappeared in OL 7.3 but I hadn’t noticed)
  • There is a new RPM for 12.2 named oracle-database-server-12cR2-preinstall

Interestingly enough all 3 preinstall RPMs (11.2/12.1/12.2) exist in the “latest” channel for OL7.

Looking at the new oracle-database-server-12cR2-preinstall.x86_64 RPM I noticed that gcc wasn’t referenced anymore:

[oracle@server1 ~]$ rpm -qi --requires oracle-database-server-12cR2-preinstall
Name        : oracle-database-server-12cR2-preinstall
Version     : 1.0
Release     : 3.el7
Architecture: x86_64
Install Date: Thu 31 Aug 2017 04:38:58 AM EDT
Group       : Test Environment/Libraries
Size        : 56561
License     : GPLv2
Signature   : RSA/SHA256, Mon 10 Jul 2017 06:27:07 AM EDT, Key ID 72f97b74ec551f03
Source RPM  : oracle-database-server-12cR2-preinstall-1.0-3.el7.src.rpm
Build Date  : Mon 10 Jul 2017 06:26:59 AM EDT
Build Host  : x86-ol7-builder-02.us.oracle.com
Relocations : (not relocatable)
Vendor      : Oracle
Summary     : Sets the system for Oracle Database single instance and
              Real Application Cluster install for Oracle Linux 7
Description :
The Oracle Preinstallation RPM package installs software packages and 
sets system parameters required for Oracle Database single instance and 
Oracle Real Application Clusters installations for Oracle Linux Release 7

Files affected: /etc/sysctl.conf, /boot/grub/menu.lst OR /boot/grub2/grub.cfg
Files added: /etc/security/limits.d/oracle-database-server-12cR2-preinstall.conf
/bin/bash
/bin/sh
/bin/sh
/bin/sh
/bin/sh
/etc/redhat-release
bind-utils
binutils
compat-libcap1
compat-libstdc++-33
config(oracle-database-server-12cR2-preinstall) = 1.0-3.el7
ethtool
glibc
glibc-devel
initscripts
kernel-uek
ksh
libaio
libaio-devel
libgcc
libstdc++
libstdc++-devel
make
module-init-tools
net-tools
nfs-utils
openssh-clients
oraclelinux-release
pam
procps
psmisc
rpmlib(CompressedFileNames) <= 3.0.4-1
rpmlib(FileDigests) <= 4.6.0-1
rpmlib(PayloadFilesHavePrefix) <= 4.0-1
smartmontools
sysstat
unzip
util-linux-ng
xorg-x11-utils
xorg-x11-xauth
rpmlib(PayloadIsXz) <= 5.2-1

Interesting! I thought I’d post this just to let you know.

When planning on installing Enterprise Manager agents you might also notice the absence of gcc: the agent deployment prerequisite check for OEM 13.2 complained for this exact reason.

Full comparison between 12.1 and 12.2

I ran a quick diff to compare the dependencies between oracle-database-server-12cR2-preinstall-1.0-3.el7.x86_64 and oracle-rdbms-server-12cR1-preinstall-1.0-4.el7.x86_64.rpm. Here is the result:

[oracle@server1 ~]$ rpm -q --requires oracle-database-server-12cR2-preinstall > /tmp/12cR2-preinstall.txt
[oracle@server1 ~]$ rpm -qp --requires oracle-rdbms-server-12cR1-preinstall-1.0-4.el7.x86_64.rpm > /tmp/12cR1-preinstall.txt

[oracle@server1 ~]$ diff --width 100 -y /tmp/12cR1-preinstall.txt /tmp/12cR2-preinstall.txt 
/bin/bash                                       /bin/bash
/bin/sh                                         /bin/sh
/bin/sh                                         /bin/sh
/bin/sh                                         /bin/sh
/bin/sh                                         /bin/sh
/etc/redhat-release                             /etc/redhat-release
bind-utils                                      bind-utils
binutils                                        binutils
compat-libcap1                                  compat-libcap1
compat-libstdc++-33                             compat-libstdc++-33
config(oracle-rdbms-server-12cR1-preinstall)  | config(oracle-database-server-12cR2-preinstal
ethtool                                         ethtool
gcc                                           <
gcc-c++                                       <
glibc                                           glibc
glibc-devel                                     glibc-devel
initscripts                                     initscripts
kernel-uek                                      kernel-uek
ksh                                             ksh
libaio                                          libaio
libaio-devel                                    libaio-devel
libgcc                                          libgcc
libstdc++                                       libstdc++
libstdc++-devel                                 libstdc++-devel
make                                            make
module-init-tools                               module-init-tools
                                              > net-tools
nfs-utils                                       nfs-utils
openssh-clients                                 openssh-clients
                                              > oraclelinux-release
pam                                             pam
procps                                          procps
psmisc                                          psmisc
rpmlib(CompressedFileNames) <= 3.0.4-1          rpmlib(CompressedFileNames) <= 3.0.4-1
rpmlib(FileDigests) <= 4.6.0-1                  rpmlib(FileDigests) <= 4.6.0-1
rpmlib(PayloadFilesHavePrefix) <= 4.0-1         rpmlib(PayloadFilesHavePrefix) <= 4.0-1
smartmontools                                   smartmontools
sysstat                                         sysstat
                                              > unzip
util-linux-ng                                   util-linux-ng
xorg-x11-utils                                  xorg-x11-utils
xorg-x11-xauth                                  xorg-x11-xauth
rpmlib(PayloadIsXz) <= 5.2-1                    rpmlib(PayloadIsXz) <= 5.2-1

It came as a bit of a surprise to see the (deprecated) net-tools again. Maybe it has to do with OSWatcher, as it likes netstat for example. I personally made the switch from net-tools to iproute some time ago and haven’t missed anything. I appreciate the addition of unzip to the list of required packages.

Partitioning Guide updated

partitioning2

Just a quick note to say I’ve updated our Getting Started With Partitioning Guide! 9 easy to follow demonstrations to get developers up to speed with handling large databases. Check it out here

Richard Foote Consulting Has Arrived (New Angels Of Promise)

Today is the official launch of Richard Foote Consulting, my new independent company in which I’ll be providing specialist Oracle Database consulting and training services, focusing on database performance tuning, database problem resolutions and database health check assessments (at least until everyone moves across to the new self-tuning Oracle Cloud database). Based on all the […]

Active Data Guard services in Multitenant

A database (or the CDB in multitenant) registers its name as the default service. When a standby database is on the same server, or same cluster, you have no problem because this database name is the db_unique_name which is different between the primary and the standby(s).

In multitenant, in addition to that, each PDB registers its name as a service. But the PDB name is the same in the primary and the standby database. This means that we have the same service name registered for the PDB in primary and standby:

Service "pdb1" has 2 instance(s).
Instance "CDB2A", status READY, has 1 handler(s) for this service...
Instance "CDB2B", status READY, has 1 handler(s) for this service...

We cannot change that, and then it is strongly recommended to create different services for the PDB in primary and standby.

The PDB default service name

Here is what we want to avoid.
I’ve a container database (db_name=CDB2) with its primary (db_unique_name=CDB2A) and standby (db_unique_name=CDB2B) on the same server, registered to the same listener:

Service "59408d6bed2c1c8ee0536a4ea8c0cfa9" has 2 instance(s).
Instance "CDB2A", status READY, has 1 handler(s) for this service...
Instance "CDB2B", status READY, has 1 handler(s) for this service...
Service "CDB2A" has 1 instance(s).
Instance "CDB2A", status READY, has 1 handler(s) for this service...
Service "CDB2AXDB" has 1 instance(s).
Instance "CDB2A", status READY, has 1 handler(s) for this service...
Service "CDB2A_DGB" has 1 instance(s).
Instance "CDB2A", status READY, has 1 handler(s) for this service...
Service "CDB2A_DGMGRL" has 1 instance(s).
Instance "CDB2A", status UNKNOWN, has 1 handler(s) for this service...
Service "CDB2B" has 1 instance(s).
Instance "CDB2B", status READY, has 1 handler(s) for this service...
Service "CDB2BXDB" has 1 instance(s).
Instance "CDB2B", status READY, has 1 handler(s) for this service...
Service "CDB2B_DGB" has 1 instance(s).
Instance "CDB2B", status READY, has 1 handler(s) for this service...
Service "CDB2B_DGMGRL" has 1 instance(s).
Instance "CDB2B", status UNKNOWN, has 1 handler(s) for this service...
Service "CDB2_CFG" has 2 instance(s).
Instance "CDB2A", status READY, has 1 handler(s) for this service...
Instance "CDB2B", status READY, has 1 handler(s) for this service...
Service "pdb1" has 2 instance(s).
Instance "CDB2A", status READY, has 1 handler(s) for this service...
Instance "CDB2B", status READY, has 1 handler(s) for this service...

The PDB1 service is registered from both instances, and then when I use it in my connection string I’m connected at random to the primary or the standby:

22:27:46 SQL> connect sys/oracle@//localhost:1522/pdb1 as sysdba
Connected.
22:27:51 SQL> select * from v$instance;
 
INSTANCE_NUMBER INSTANCE_NAME HOST_NAME VERSION STARTUP_TIME STATUS PARALLEL THREAD# ARCHIVER LOG_SWITCH_WAIT LOGINS SHUTDOWN_PENDING DATABASE_STATUS INSTANCE_ROLE ACTIVE_STATE BLOCKED CON_ID INSTANCE_MODE EDITION FAMILY DATABASE_TYPE
--------------- ------------- --------- ------- ------------ ------ -------- ------- -------- --------------- ------ ---------------- --------------- ------------- ------------ ------- ------ ------------- ------- ------ -------------
1 CDB2B VM106 12.2.0.1.0 15-SEP-17 OPEN NO 1 STARTED ALLOWED NO ACTIVE PRIMARY_INSTANCE NORMAL NO 0 REGULAR EE SINGLE
 
22:28:00 SQL> connect sys/oracle@//localhost:1522/pdb1 as sysdba
Connected.
22:28:06 SQL> /
 
INSTANCE_NUMBER INSTANCE_NAME HOST_NAME VERSION STARTUP_TIME STATUS PARALLEL THREAD# ARCHIVER LOG_SWITCH_WAIT LOGINS SHUTDOWN_PENDING DATABASE_STATUS INSTANCE_ROLE ACTIVE_STATE BLOCKED CON_ID INSTANCE_MODE EDITION FAMILY DATABASE_TYPE
--------------- ------------- --------- ------- ------------ ------ -------- ------- -------- --------------- ------ ---------------- --------------- ------------- ------------ ------- ------ ------------- ------- ------ -------------
1 CDB2A VM106 12.2.0.1.0 15-SEP-17 OPEN NO 1 STARTED ALLOWED NO ACTIVE PRIMARY_INSTANCE NORMAL NO 0 REGULAR EE SINGLE
 
22:28:07 SQL> connect sys/oracle@//localhost:1522/pdb1 as sysdba
Connected.
22:28:10 SQL> /
 
INSTANCE_NUMBER INSTANCE_NAME HOST_NAME VERSION STARTUP_TIME STATUS PARALLEL THREAD# ARCHIVER LOG_SWITCH_WAIT LOGINS SHUTDOWN_PENDING DATABASE_STATUS INSTANCE_ROLE ACTIVE_STATE BLOCKED CON_ID INSTANCE_MODE EDITION FAMILY DATABASE_TYPE
--------------- ------------- --------- ------- ------------ ------ -------- ------- -------- --------------- ------ ---------------- --------------- ------------- ------------ ------- ------ ------------- ------- ------ -------------
1 CDB2B VM106 12.2.0.1.0 15-SEP-17 OPEN NO 1 STARTED ALLOWED NO ACTIVE PRIMARY_INSTANCE NORMAL NO 0 REGULAR EE SINGLE
 
22:28:11 SQL> connect sys/oracle@//localhost:1522/pdb1 as sysdba
Connected.
22:28:13 SQL> /
 
INSTANCE_NUMBER INSTANCE_NAME HOST_NAME VERSION STARTUP_TIME STATUS PARALLEL THREAD# ARCHIVER LOG_SWITCH_WAIT LOGINS SHUTDOWN_PENDING DATABASE_STATUS INSTANCE_ROLE ACTIVE_STATE BLOCKED CON_ID INSTANCE_MODE EDITION FAMILY DATABASE_TYPE
--------------- ------------- --------- ------- ------------ ------ -------- ------- -------- --------------- ------ ---------------- --------------- ------------- ------------ ------- ------ ------------- ------- ------ -------------
1 CDB2A VM106 12.2.0.1.0 15-SEP-17 OPEN NO 1 STARTED ALLOWED NO ACTIVE PRIMARY_INSTANCE NORMAL NO 0 REGULAR EE SINGLE

I don’t want to use a service that connects at random and then I need to create different services.

Read-Only service for the Active Data Guard standby

I’m in Oracle Restart and I create the service with srvctl (but you can also create it with dbms_service when not running with Grid Infrastructure):


srvctl add service -db cdb2b -service pdb1_ro -pdb pdb1 -role physical_standby

This creates the service for the standby database (CDB2B) to be started when in physical standby role, and the service connects to the pluggable database PDB1.
But I cannot start it:

srvctl start service -db cdb2b -service pdb1_ro -pdb pdb1
 
 
PRCD-1084 : Failed to start service pdb1_ro
PRCR-1079 : Failed to start resource ora.cdb2b.pdb1_ro.svc
CRS-5017: The resource action "ora.cdb2b.pdb1_ro.svc start" encountered the following error:
ORA-16000: database or pluggable database open for read-only access
ORA-06512: at "SYS.DBMS_SERVICE", line 5
ORA-06512: at "SYS.DBMS_SERVICE", line 288
ORA-06512: at line 1
. For details refer to "(:CLSN00107:)" in "/u01/app/12.2/diag/crs/vm106/crs/trace/ohasd_oraagent_oracle.trc".
 
CRS-2674: Start of 'ora.cdb2b.pdb1_ro.svc' on 'vm106' failed

The reason is that the service information must be stored in the dictionary, SYS.SERVICE$ table, and you cannot do that on a read-only database.

This has been explained a long time ago by Ivica Arsov on his blog: https://iarsov.com/oracle/data-guard/active-services-on-physical-standby-database/ and nothing has changed. You need to create the service on the primary so that the update of SYS.SERVICE$ is propagated to the standby database through log shipping:


srvctl add service -db cdb2a -service pdb1_ro -pdb pdb1 -role physical_standby

This is not sufficient because the insert in SYS.SERVICE$ does not occur yet:

SQL> alter session set container=PDB1;
 
Session altered.
 
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
------ -------- --------- ----------
3 PDB1 READ WRITE NO
 
SQL> select * from service$;
 
SERVICE_ID NAME NAME_HASH NETWORK_NAME CREATION_DATE CREATION_DATE_HASH DELETION_DATE FAILOVER_METHOD FAILOVER_TYPE FAILOVER_RETRIES FAILOVER_DELAY MIN_CARDINALITY MAX_CARDINALITY GOAL FLAGS EDITION PDB RETENTION_TIMEOUT REPLAY_INITIATION_TIMEOUT SESSION_STATE_CONSISTENCY SQL_TRANSLATION_PROFILE MAX_LAG_TIME GSM_FLAGS PQ_SVC STOP_OPTION FAILOVER_RESTORE DRAIN_TIMEOUT
---------- ---- --------- ------------ ------------- ------------------ ------------- --------------- ------------- ---------------- -------------- --------------- --------------- ---- ----- ------- --- ----------------- ------------------------- ------------------------- ----------------------- ------------ --------- ------ ----------- ---------------- -------------
14 pdb1 1888881990 pdb1 15-SEP-17 1332716667 136 PDB1

As explained by Ivica in his blog post, we need to start the service once to have the row inserted in SERVICE$:

srvctl start service -db cdb2a -service pdb1_ro -pdb pdb1
srvctl stop service -db cdb2a -service pdb1_ro

Now the service information is persistent in the dictionary:

SQL> alter session set container=PDB1;
Session altered.
 
SQL> show pdbs
 
CON_ID CON_NAME OPEN MODE RESTRICTED
------ -------- ---- ---- ----------
3 PDB1 READ WRITE NO
 
SQL> select * from service$;
 
SERVICE_ID NAME NAME_HASH NETWORK_NAME CREATION_DATE CREATION_DATE_HASH DELETION_DATE FAILOVER_METHOD FAILOVER_TYPE FAILOVER_RETRIES FAILOVER_DELAY MIN_CARDINALITY MAX_CARDINALITY GOAL FLAGS EDITION PDB RETENTION_TIMEOUT REPLAY_INITIATION_TIMEOUT SESSION_STATE_CONSISTENCY SQL_TRANSLATION_PROFILE MAX_LAG_TIME GSM_FLAGS PQ_SVC STOP_OPTION FAILOVER_RESTORE DRAIN_TIMEOUT
---------- ---- --------- ------------ ------------- ------------------ ------------- --------------- ------------- ---------------- -------------- --------------- --------------- ---- ----- ------- --- ----------------- ------------------------- ------------------------- ----------------------- ------------ --------- ------ ----------- ---------------- -------------
14 pdb1 1888881990 pdb1 15-SEP-17 1332716667 136 PDB1
1 pdb1_ro 1562179816 pdb1_ro 15-SEP-17 1301388390 0 0 0 8 PDB1 86400 300 DYNAMIC ANY 0 0 0 0

This is from the primary, but after the redo has been transported and applied, I have the same on the standby. Now I can start the service I’ve created for the standby:

srvctl start service -db cdb2b -service pdb1_ro -pdb pdb1

Here is the new service registered on the listener, which I can use to connect to the read-only PDB1 on the Active Data Guard standby:

Service "pdb1" has 2 instance(s).
Instance "CDB2A", status READY, has 1 handler(s) for this service...
Instance "CDB2B", status READY, has 1 handler(s) for this service...
Service "pdb1_ro" has 1 instance(s).
Instance "CDB2B", status READY, has 1 handler(s) for this service...
Service "pdb1_rw" has 1 instance(s).

Read-Write service for the primary

You can see above that in order to select from SERVICE$ I connected to CDB$ROOT and switched to PDB1 with ‘set container’. There’s no other choice because using the service name directs me at random to any instance. Then, I need a service to connect to the primary only, and I’ll call it PDB1_RW as it is opened in Read Write there.

srvctl add service -db cdb2a -service pdb1_rw -pdb pdb1 -role primary
srvctl start service -db cdb2a -service pdb1_rw

Finally, here are the services registered from the listener:

Service "pdb1" has 2 instance(s).
Instance "CDB2A", status READY, has 1 handler(s) for this service...
Instance "CDB2B", status READY, has 1 handler(s) for this service...
Service "pdb1_ro" has 1 instance(s).
Instance "CDB2B", status READY, has 1 handler(s) for this service...
Service "pdb1_rw" has 1 instance(s).
Instance "CDB2A", status READY, has 1 handler(s) for this service...

I’ll probably never use the ‘PDB1′ service because I want to know where I connect to.

In case of switchover, I also create the Read Write service in for the standby:

srvctl add service -db cdb2b -service pdb1_rw -pdb pdb1 -role primary

Here are the resources when CDB2A is the primary:

$ crsctl stat resource -t -w "TYPE = ora.service.type"
--------------------------------------------------------------------------------
Name Target State Server State details
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.cdb2a.pdb1_ro.svc
1 OFFLINE OFFLINE STABLE
ora.cdb2a.pdb1_rw.svc
1 ONLINE ONLINE vm106 STABLE
ora.cdb2b.pdb1_ro.svc
1 ONLINE ONLINE vm106 STABLE
ora.cdb2b.pdb1_rw.svc
1 OFFLINE OFFLINE STABLE
--------------------------------------------------------------------------------

I test as switchover to CDB2B:

$ dgmgrl sys/oracle
DGMGRL for Linux: Release 12.2.0.1.0 - Production on Fri Sep 15 23:41:26 2017
 
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
 
Welcome to DGMGRL, type "help" for information.
Connected to "CDB2B"
Connected as SYSDG.
DGMGRL> switchover to cdb2b;
Performing switchover NOW, please wait...
New primary database "cdb2b" is opening...
Oracle Clusterware is restarting database "cdb2a" ...
Switchover succeeded, new primary is "cdb2b"

Here are the services:

[oracle@VM106 blogs]$ crsctl stat resource -t -w "TYPE = ora.service.type"
--------------------------------------------------------------------------------
Name Target State Server State details
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.cdb2a.pdb1_ro.svc
1 ONLINE ONLINE vm106 STABLE
ora.cdb2a.pdb1_rw.svc
1 OFFLINE OFFLINE STABLE
ora.cdb2b.pdb1_ro.svc
1 OFFLINE OFFLINE STABLE
ora.cdb2b.pdb1_rw.svc
1 ONLINE ONLINE vm106 STABLE
--------------------------------------------------------------------------------

So what?

The recommendations are not new here:

  • Always do the same on the primary and the standby. Create services on both sites, then have started them depending on the role
  • Always use one or several application services rather than the default one, in order to have better control and flexibility on where you connect

In multitenant, because services are mandatory to connect to a container with a local user, all the recommendations about services are even more important than before. If you follow them, you will see that multitenant is not difficult at all.

This case may seem improbable, because you probably don’t put the standby on the same server or cluster as the primary. But you may have several standby databases on the same server. About the service registered from the PDB name, just don’t use it. I’m more concerned by the GUID service name (here 59408d6bed2c1c8ee0536a4ea8c0cfa9) which is also declared by both databases. If you plan to use online PDB relocate in a Data Guard configuration then be careful with that. I’ve not tested it, but it is probably better to keep the standby PDB closed, or at least do not register it on the same listener.

 

Cet article Active Data Guard services in Multitenant est apparu en premier sur Blog dbi services.

Death of the DBA, Long Live the DBA

I planned on finishing up and publishing a different post today, but after a number of conversations with DBAs in the community, this topic took precedence.

https://i0.wp.com/dbakevlar.com/wp-content/uploads/2017/09/Screen-Shot-2... 300w, https://i0.wp.com/dbakevlar.com/wp-content/uploads/2017/09/Screen-Shot-2... 768w, https://i0.wp.com/dbakevlar.com/wp-content/uploads/2017/09/Screen-Shot-2... 1540w, https://i0.wp.com/dbakevlar.com/wp-content/uploads/2017/09/Screen-Shot-2... 1312w" sizes="(max-width: 600px) 100vw, 600px" data-recalc-dims="1" />

Yesterday the above announcement came out with the Oracle earnings call, along with the following:

“In a couple of weeks, we will announce the world’s first fully autonomous database cloud service,” said Oracle Chairman and CTO, Larry Ellison. “Based on machine learning, the latest version of Oracle is a totally automated “self-driving” system that does not require human beings to manage or tune the database. Using AI to eliminate most sources of human error enables Oracle to offer database SLA’s that guarantee 99.995% reliability while charging much less than AWS.”
With DBAs that have been around a while, we know the idea that you don’t need a DBA has been around since Oracle 7, the “self-healing database”.  I received numerous emails and messages and even a phone call from folks in the community, concerned about the announcement, along with the rebranding of Oracle Technology Network to Oracle Development Community.

The Role is Changing

I’ve been presenting on the topic of the changing role of the DBA for almost a year now.  It’s not an unknown, folks.  We all realize that the influencer has broadened and for cloud initiatives, what better way to introduce it into the business than to give it to developers and let them build out environments that require very little input or guidance from operations?
The demand on the development cycle has increased exponentially.  None of us can deny that and as much as we know that data friction is the cause of this, many times we DBAs felt fulfilled by that friction.  The best DBAs all have a few control issues and the ability to control the environment was essential to ensuring stability and consistency of design, platform, etc. occurred. We may not like to hear it, but it’s just how it is.  It was part of my job to slow things down when I noted issues that could impact uptime, performance or accessibility.
With the introduction of cloud, particularly SaaS, the need for operations, (DBA, administrative and network support) is little to not required.  IaaS is still a powerful opportunity for the business to have more control over the environment and expertise, but it’s going to look awfully attractive to companies to pay more monthly for a SaaS offering when the business is sold on the idea, “You won’t need all those DBAs, server and network administrators any longer.”  The extra cost, when billed monthly is easier to “swallow” than annual licensing fees and salaries.
DBAs are now recommended to look to cloud providers to utilize their skills or move to a development role.  I’ve been proposing a third option of DevOps, as I’ve stated time and time again. The DBA role is changing and we just need to admit that we’re not needed for much of what we once were.
Or are we?

History

Oracle is not the only one to claim that you won’t need a DBA, nor have they done it as successfully as Microsoft SQL Server.  At the release of SQL Server 7.0, there was a push of marketing that stated a DBA was no longer necessary.  Windows Admins and developers were installing SQL Server and taking over management of the database tier.  It resulted in a temporary lull in available positions for SQL Server DBAs and as I worked for a company whose largest environment resided on SQL Server, I remember interviewing candidates who couldn’t tell me what an “LSN” was, how lock escalation worked or even how data was written to the SQL Server transaction log.  We quickly exhausted candidates searching for a qualified DBA.  Luckily, Microsoft learned the error of their ways and within two years, all those databases installed by non-DBAs came to a breaking point and DBAs were re-introduced in mass quantity.  The SQL Server DBA community is thriving and has some incredibly skilled administrators that understand the database engine, design and code.
I foresee this as a potential scenario for the Oracle database community now and this is why.

Clouding Your Way Out of a Software Problem

Any DBA who specializes in optimization knows that hardware offers around 15% overall opportunity for improvement.  My favorite quote from Cary Millsap, “You can’t hardware your way out of a software problem” is quite fitting, too.  A hardware upgrade can offer a quick increase in performance, only to find that the problem seemingly returns after a period of time.  As we’ve discussed in previous posts.  The natural life of a database is growth-  growth in data, growth in processing, growth in users.  This growth requires more resources and if the environment is not performing as optimally and efficiently as possible, more resources will always be required.
When we attack an optimization project at the code, design and application level, we have over an 80% opportunity for improvement.  The improvements are long term and can serve future projects, as well.
Having this information in our pockets, let’s now add the reality of the cloud.  Depending on the provider, we purchase compute and storage “packages” to serve the best needs of the environment.  It may not be the most optimal configuration, but if we need more, it’s easy enough to scale up.  All of this comes with a cost.  Anyone who thinks they’re going to save money going to the cloud really shouldn’t make this the reason for going to the cloud.  One of the largest surprises for companies who migrated to the cloud early on, although the monthly costs looked promising, the overall annual cost realized no savings and often increased expenditures.  Your benefit of the cloud is easy access, easy scaling when needed, for most primary systems, it won’t result in savings., The real question is, “should you scale and cost the business money just because it’s there?”
As I stated earlier, you can’t hardware your way out of a software problem, but can you cloud your way out of a software problem?  Cloud vendors will be all too happy to scale you to the next higher cloud package offering.  They will be very happy to do it transparently for you, but this will be a required, regular process if you don’t have someone optimizing your environment.
Are you really expecting your developers to be skilled in identifying performance issues and optimization of code and design? 

Long Live the DBA

Developers are expected to do more in a shorter cycle and with less every day.  Agile is here and with the introduction of DevOps, there is structure around agile development to demand even more from them.  The skills and the depth of their development knowledge is already vast and that will result in them being stretched to fulfill the demands from standard development tasks.
This will result in a high demand for DBAs knowledge of database engine, the optimizer and how to optimize environments.  Those DBAs with advanced skills in these areas will have plenty of work to keep them busy and if Larry is successful with the bid to rid companies of their DBAs for a period of time, they’ll be very busy cleaning up the mess afterwards.



Tags:  


Del.icio.us



Facebook

TweetThis

Digg

StumbleUpon




Copyright © DBA Kevlar [Death of the DBA, Long Live the DBA], All Right Reserved. 2017.

The post Death of the DBA, Long Live the DBA appeared first on DBA Kevlar.

Free Webinar – How Oracle Works!

Next Tuesday (19th September) I am doing a free webinar for ProHuddle. It lasts under an hour and is an introduction to how some of the core parts of the Oracle RDBMS work, I call it “The Heart of Oracle: How the Core RDBMS Works”. Yes, I try and explain all of the core Oracle RDBMS in under an hour! I’m told I just about manage it. You can see details of the event and register for it here. I’ve done this talk a few times at conferences now and I really like doing it, partly as it seems to go down so well and people give me good feedback about it (and occasionally bad feedback, but I’ll get on to that).

https://mwidlake.files.wordpress.com/2017/09/screenhunter_231-sep-15-12-... 920w, https://mwidlake.files.wordpress.com/2017/09/screenhunter_231-sep-15-12-... 150w, https://mwidlake.files.wordpress.com/2017/09/screenhunter_231-sep-15-12-... 300w, https://mwidlake.files.wordpress.com/2017/09/screenhunter_231-sep-15-12-... 768w" sizes="(max-width: 460px) 100vw, 460px" />

The idea behind the presentation is not to do the usual “Intro” and list what the main Oracle operating systems processes – SMON, PMON, RECO etc – are or what the various components of the shared memory do. I always found those talks a little boring and they do not really help you understand why Oracle works the way it does when you use it. I aim to explain what redo is, why it is so important, what actually happens when you commit, how data is written to and read from storage to the cache – and what is actually put in the buffer cache. I explain the concept of point-in-time view, how Oracle does it and why it is so fantastic. And a few other bits and pieces.

I’m not trying to explain to people the absolute correct details of what goes on with all these activities that the database does for you. I’m attempting to give people an understanding of the principles so that more advanced topics make more sense and fit together. The talk is, of course, aimed at people who are relatively new to Oracle – students, new DBAS or developers who have never had explained to them why Oracle works the way it does. But I have found that even some very experienced DBA-types have learnt the odd little nugget of information from the talk.

Of course, in an hour there is only so much detail I can go into when covering what is a pretty broad set of topics. And I lie about things. I say things that are not strictly true, that do not apply if more advanced features of Oracle are used, or that ignore a whole bucket full of exceptions. But it’s like teaching astrophysics at school. You first learn about how the Sun is at the centre of the solar system, all the planets & moons revolve around each other due to gravity and the sun is hot due to nuclear fusion. No one mentions how the earth’s orbit varies over thousands and millions of years until you have the basics. Or that GPS satellites have to take into account the theory of relativity to be as accurate as they are. Those finer details are great to learn but they do not change the fundamental principles of planets going around suns and rocks falling out of the sky – and you need to know the simpler overall “story” to slot in the more complex information.

I start off the talk explaining this simplification and I do try to indicate where people will need to dig deeper if they, for example, have Exadata – but with a webinar I am sure people will join late, drop in and out and might miss that. I must remember to keep reminding people I’m ignoring details. And amongst the audience will be people who know enough to spot some of these “simplifications” and I think the occasional person might get upset. Remember I mentioned the bad feedback? I got accosted at a conference once after I had done this talk by a couple of experts, who were really angry with me that I had said something that was not accurate. But they had missed the start of the talk and my warnings of simplification and did not seem to be able to understand that I would have needed half an hour to explain the details of that on thing that they knew – but I had only 50 minutes in total for everything!

As I said, this is the first Webinar I will have done. I am sure it will be strange for me to present with “no audience” and I’m sure I’ll trip up with the pointer and the slides at some point. I usually have some humour in my presentations but that might not work with no crowd feedback and a worldwide audience. We will see. But I am excited about doing it and, if it works, I may well offer to do more.

As a taster, I explain the above diagram. A lot. I mostly just talk about pictures, there will be very few “wordy” slides.

I invite you all to register for the talk – as I said, it is free – and please do spread the word.

click here to register for the Webinar

Where is Goth Geek Girl, Week 37

It was a really busy summer and ended with me returning after a week of vacation in Singapore.  What should I do after a 17hr flight and jet lag?  Two webinars and a SQL Saturday event!  What better way to get over jet lag and get my game back on and just jump back in!

I started out by having a webinar this morning on “DBA to DevOps to DataOps- the Revolution.”  I had a feeling with the jet lag, I’d be done faster than I’d hoped, but with the amount of questions from the over 400 attendees, it was an awesome one hour with everyone.  I focused on the important topic of data gravity and how the role of the DBA can evolve to be more productive for the business.

There were reference links that I knew were important and the PDF slide deck doesn’t provide that, so please refer to the following links below to catch up with all the delphix blog posts I’ve written on this topic:

Blog Posts-

FYI-  there are two more blog posts that will be published shortly on delphix.com, so stay tuned for those.

Webinar Recordings

On Thursday, I’ll be presenting with Oracle, “The DBA Diaries” focused on the cloud.  It should be a great conversation on where DBAs are in the scheme of the cloud and how our role is evolving.

To round up the week, I’ll be presenting at SQL Saturday Denver, my local SQL Saturday event for the SQL Server community!  Delphix is sponsoring this awesome event and I’m looking forward to presenting, (as is Tim at this event.)

Sunday–  I SLEEP!  No, I lie… I’ll be uploading all my code, video and content for ODTUG’s Geekathon.   Then I sleep. </p />
</p></div></div>

    	  	<div class=

How to automatically build any recent version of the Oracle database.

There are many situations where you want to use a very specific configuration of the Oracle database, for example when a client has an issue and is still on EL5, or gets disk errors on a filesystem that is ext3, or is using ASM and gets weird IO patterns. Other examples are: you want to test the newest PSU to see if responds differently to an issue you are working on, or you want to test a combination of the Oracle database version 11.2.0.3 and grid infrastructure 12.1.0.2.

Of course you can just go and install a virtual machine, install all the different bits and pieces. Doing so manually kills vast amounts of time. By doing that, you will end up with a lot of virtual machines, for which at a certain point in time you have to make a decision to remove some of these.

Also a lot of people use a (virtual) machine with a couple of database versions installed, and test on these. In that case you sometimes have to ignore details like filesystemASM, or specific PSU level, it’s hard to keep that updated, but when a client case is in a lower version, in general you don’t go back in PSU level (although not impossible). One thing I ran into frequently is that it’s easy to get caught in side effects because of changes and settings made for earlier test cases (often underscore parameters).

This blogpost introduces my project ‘vagrant-builder’ which allows you to build a virtual machine with Oracle and optionally clusterware installed in any version you specify. The provisioning will download all software and patches (except for the 12.2.0.1 media, which needs to be provided in the ‘files’ directory) fully automatic for you. These are the options:

Linux version:
Oracle linux version 5, 6 or 7 (limited by boxes build by the box-cutter project).
The Actual versions currently existing are ol5.11, ol6.6/7/8, ol7.0/1/2/3. I am awaiting the boxcutter project to produce ol6.9 and ol7.4.

Filesystems:
Filesystem types for u01 and for oradata (when no ASM is used): xfs, ext4, ext3.

Kernel:
Oracle linux 5: latest redhat kernel, latest UEK2 kernel.
Oracle linux 6: any exadata kernel version (if made available on public-yum), latest redhat/UEK2/UEK3/UEK4 kernel.
Oracle linux 7: latest redhat kernel, latest UEK3 or UEK 4 kernel.

ASM:
No ASM install.
12.2.0.1 no patches, PSU: 170620, 170718, 170814
12.1.0.2 no patches, PSU: 1, 2, 3, 4, 5, 160119, 160419, 160719, 161019, 170117, 170418, 170718, 170814
11.2.0.4 no patches, PSU: 1, 2, 3, 4, 5, 6, 7, 8, 160119, 160419, 160719, 161019, 170117, 170418, 170718, 170814

Database:
No database install.
12.2.0.1 no patches, PSU: 170620, 170718, 170814
12.1.0.2 no patches, PSU: 1, 2, 3, 4, 5, 160119, 160419, 160719, 161019, 170117, 170418, 170718, 170814
11.2.0.4 no patches, PSU: 1, 2, 3, 4, 5, 6, 7, 8, 160119, 160419, 160719, 161019, 170117, 170418, 170718, 170814
11.2.0.3 PSU 15 only.
11.2.0.2 PSU 12 only.

Database:
By specifying a database name, a database will be created with that name. Of course the dictionary part of the patching will be applied to the database!

How does this work? This works using the combination of the following pieces of software:
– Virtualbox
– Vagrant
– Ansible
Plus the vagrant-builder repository: https://gitlab.com/FritsHoogland/vagrant-builder

If you don’t have Virtualbox, Vagrant or Ansible installed, follow the installation procedure in this blog article; it’s a bit older, so versions of the software components will be higher, you should simply install the latest versions. There is quite an important caveat (sadly): Ansible in principle does not run on windows. You can made it working on windows by using Cygwin, but officially it doesn’t support windows. If you can get the provisioning using Ansible to fully work on windows please share how you did that.

Once you got all the software components installed, another thing you might want to do first is to move your default virtual box directory to a place where you got enough space to hold virtual machines.

Then, clone the vagrant-builder repository into a directory (git clone https://gitlab.com/FritsHoogland/vagrant-builder.git myvm, for example), go into that directory and edit the Vagrantfile to set:
– hostonly_network_ip_address
– mos username & password
– database_name (if you want a database)
– linux (choose one by removing the hash sign in front of it)
– kernel
– asm_version (set a version if you want clusterware “siha” and ASM, if a database_version is set and asm_version is empty, you get a filesystem based database)
– database_version (set a version if you want the database software to be installed)
– vm_cpus (number of CPUs visible/made available to the VM)
– vm_memory (amount of memory made available ot the VM)
– vm_hostname (if you want multiple VMs, you need multiple vm_hostnames set!)
– perl_l4cache_workaround (if you got a newer CPU with a level 4 cache, set this to Y (yes), otherwise set this to N (no))

Save the changes, and startup the virtual machine: ‘vagrant up’. This will pull the operating system image, add a disk for the database, startup linux, setup and configure linux, download the database and grid software version (except for version 12.2.0.1, for which the installation media needs to be staged in the files dictory), install it, download the patches, install these and create a database, without manual intervention.

Tagged: ansible, ASM, automation, grid, install, oracle, vagrant, virtualbox

The AskTOM team at OpenWorld 2017

The AskTOM team will be out and about at OpenWorld in October, so if you are at the conference as a full attendee, or just with a Discovery pass, please come up and say Hi!  As well as our session talks, we’ll be doing impromptu discussions and mini-sessions during the week either in the Developer Lounge area or the Exhibition Hall, so keep your eyes open on the OpenWorld twitter feeds during the week.  You can see our session times below. 

Obviously OpenWorld is a big event, and since sports TV stations go totally over the top with promotion when big events are coming up, I thought to myself: “Why should they have all the fun ?”.  So crank up the volume to maximum, and get down with our pumping AskTOM promo video for OpenWorld ! Smile

Our Sessions

Connor McDonald

War of the Worlds: DBAs Versus Developers

Wednesday, Oct 04, 1:00 p.m. – 1:45 p.m. | Moscone West – Room 3014
Don’t let the name deceive you – this is a technical session covering a myriad of 12c Release 2 features for both DBAs and Developers. For decades, developers and DBAs have battled over who controls the world. As cloud databases now free DBAs from the mundane the battle flares again, as developers need greater flexibility, less structure, and faster turnaround to deploy. DBAs use a more methodical approach, worried about the wreckage that may result from playing so fast and loose. But does that just make the DBA an inhibitor to your productivity? Who is right? Perhaps both? Can developers and DBAs peacefully coexist? Maybe they just need the right technology. In this session see how to integrate the demands of the modern-day developer with the Oracle Database (and the modern-day administrators). 

Click here to register.

Leaner, Faster Code with Advanced SQL Techniques

Tuesday, Oct 03, 1:15 p.m. – 2:00 p.m. | Moscone West – Room 2002
Most SQL is so simple to write that we can hand off the job to an ORM tool or a similar mechanism. But by delving a little deeper into more-advanced SQL facilities, we can get performance benefits and write a lot less middle-tier code. This session highlights some SQL techniques for solving problems that would otherwise require a lot of complex coding. Learn how to become a more productive developer by expanding your knowledge of the SQL language.

Click here to register
 
Using Advanced SQL Techniques for Faster Applications

Tuesday, Oct 03, 5:45 p.m. – 6:30 p.m. | Moscone West – Room 3014
Most SQL is so simple to write that you can hand off the job to an object-relational mapping tool or similar mechanism. But by delving a little deeper into more advanced SQL techniques, we can get performance benefits and write a lot less middle-tier code. This session highlights some SQL techniques to solve problems that would otherwise require a lot of complex coding. Learn how to become a more productive developer by expanding your knowledge of the SQL language.

Click here to register

 

Also, I’ll be doing some mini-lessons in The Exchange

Ask TOM’s Favorite Multitenant/In-Memory Features in Oracle Database 12c R2

Monday, Oct 02, 11:30 a.m. – 11:50 a.m. | The Exchange @ Moscone West – Showcase Theater 2

Click here to register

 

Ask TOM Hard Core SQL: Common Table Expressions and Hierarchy Processing

Monday, Oct 02, 3:30 p.m. – 3:50 p.m. | The Exchange @ Moscone West – Showcase Theater 2

Click here to register

 

Chris Saxon

12 Things Developers Will Love About Oracle Database 12c Release 2

Tuesday, Oct 03, 3:45 p.m. – 4:30 p.m. | Moscone West – Room 3014
Oracle Database 12c Release 2 is here. The headline features are sharding, multitenant, and application containers. But it’s the small changes to SQL that make a real difference in your day-to-day life work with Oracle Database. In this session get an overview of the changes to SQL and PL/SQL that will help you build faster, more robust database applications. If you’re a developer or DBA who regularly writes SQL or PL/SQL and wants to keep up to date this session is for you.
 
How to Hack into Your Oracle Database via Node.js, Using SQL Injection

Wednesday, Oct 04, 8:30 a.m. – 9:15 a.m. | Moscone West – Room 2004
Hackers are constantly searching for personal data they can use to exploit people. And they’re often successful. Each week brings new stories of large-scale data breaches. A common attack vector is SQL injection. If your application is vulnerable to this, hackers can get whatever they want from your database. This session shows you how easy it is to access private data with SQL injection and how to change your code to stop it. It ends with a discussion of further recommendations for writing secure code. This is a must-attend session for all developers who write database access code.
(co-speaker Dan Mcghan)

 

Maria Colgan

Oracle Database Features Every Developer Should Know About

Wednesday, Oct 04, 12:00 p.m. – 12:45 p.m. | Moscone West – Room 3020
Over the years, a lot of new features have been introduced in Oracle Database to save developers time and to spare them from having to reinvent the wheel. Many of these features are actively highlighted via conference sessions or blogs when a new release is launched, but over time are forgotten before they can be put to good use. In this session learn about the critical features already existing in Oracle Database that every developer should not only know about but also utilize. Using easy-to-follow examples, see when and where you should take advantage of these features to make your life easier and help you get the most out of Oracle Database, regardless of whether you are a DBA or a developer.
(co-speaker Gerald Venzl)

Five Things You Might Not Know About Oracle Database

Wednesday, Oct 04, 2:00 p.m. – 2:45 p.m. | Moscone West – Room 3020
In this session take a look at five things you might not have known about Oracle Database—or that you might have known about but never realized how it could benefit you. Get a detailed explanation of each feature’s functionality and see the benefits through real-world examples. The topics covered are for Oracle Database 11g and Oracle Database, Standard Edition 12c, with only a couple of minor exceptions. Come away with a better understanding of these features and how they can benefit you and your organization.
(co-speaker Dominic Giles)

Oracle Database and the Internet of Things

Tuesday, Oct 03, 11:30 a.m. – 12:15 p.m. | Moscone West – Room 3011
In recent years there has been a rapid surge in the adoption of smart devices. Everything, from phones and tablets to smart meters and fitness devices, connect to the Internet and share data. With all of these smart devices comes a huge increase in the frequency and volume of data being ingested into and processed by databases. But, what most folks forget is that industries like telecoms and manufacturing have been dealing with these challenges for years with the help of their Oracle Database. This session provides step-by-step instructions for deploying a high-ingest, mission critical IoT workload on Oracle Database.
(co-speaker Dominic Giles)

Sergiusz Wolicki

New Paradigm for Case-Sensitivity and Collation on Oracle Database 12c Release 2

Monday, Oct 02, 11:00 a.m. – 11:45 a.m. | Moscone West – Room 3008
Historically, the way an Oracle Database compares and sorts character data is controlled in a pretty coarse way by the parameters NLS_SORT and NLS_COMP. This session presents a new feature of Oracle Database 12c Release 2 (12.2) that introduces a declarative method of associating collation with particular data. Using the new mechanism, data-bound collation (DBC), the user can precisely control how any given character data is compared depending just on the source of the data. For example, content of given columns may be declared as case-insensitive. The new functionality is based on the ISO/IEC SQL standard, which simplifies application porting to Oracle Database from other RDBMS systems that support similar functionality

12c Access Control Lists

There is already enough information about the new simplified 12c way to define Access Control Lists, such as in oracle-base.
I’m just posting my example here to show how it is easy.

If, as a non-SYS user you want to connect to a host with TCP, you get an error:

SQL> connect DEMO1/demo@//localhost/PDB1
Connected.
SQL>
SQL>
SQL> declare
2 c utl_tcp.connection;
3 n number:=0;
4 begin
5 c:=utl_tcp.open_connection(remote_host=>'towel.blinkenlights.nl',remote_port=>23);
6 end;
7 /
 
Error starting at line : 27 File @ /media/sf_share/122/blogs/12cacl.sql
In command -
declare
c utl_tcp.connection;
n number:=0;
begin
c:=utl_tcp.open_connection(remote_host=>'towel.blinkenlights.nl',remote_port=>23);
end;
Error report -
ORA-24247: network access denied by access control list (ACL)
ORA-06512: at "SYS.UTL_TCP", line 19
ORA-06512: at "SYS.UTL_TCP", line 284
ORA-06512: at line 5
24247. 00000 - "network access denied by access control list (ACL)"
*Cause: No access control list (ACL) has been assigned to the target
host or the privilege necessary to access the target host has not
been granted to the user in the access control list.
*Action: Ensure that an access control list (ACL) has been assigned to
the target host and the privilege necessary to access the target
host has been granted to the user.
SQL>

Here are the ACLs defined by default:

SQL> connect sys/oracle@//localhost/PDB1 as sysdba
Connected.
 
SQL> select * from dba_host_acls;
 
HOST LOWER_PORT UPPER_PORT ACL ACLID ACL_OWNER
---- ---------- ---------- --- ----- ---------
* NETWORK_ACL_4700D2108291557EE05387E5E50A8899 0000000080002724 SYS
 
SQL> select * from dba_host_aces;
 
HOST LOWER_PORT UPPER_PORT ACE_ORDER START_DATE END_DATE GRANT_TYPE INVERTED_PRINCIPAL PRINCIPAL PRINCIPAL_TYPE PRIVILEGE
---- ---------- ---------- --------- ---------- -------- ---------- ------------------ --------- -------------- ---------
* 1 GRANT NO GSMADMIN_INTERNAL DATABASE RESOLVE
* 2 GRANT NO GGSYS DATABASE RESOLVE

So, I add an ACL to access to towel.blinkenlights.nl on telnet port (23) for my user DEMO1:

SQL> exec dbms_network_acl_admin.append_host_ace(host=>'towel.blinkenlights.nl',lower_port=>23,upper_port=>23,ace=>xs$ace_type(privilege_list =>xs$name_list('connect'),principal_name=>'DEMO1',principal_type =>xs_acl.ptype_db));
 
PL/SQL procedure successfully completed.
 
SQL> select * from dba_host_acls;
 
HOST LOWER_PORT UPPER_PORT ACL ACLID ACL_OWNER
---- ---------- ---------- --- ----- ---------
towel.blinkenlights.nl 23 23 NETWORK_ACL_5876ADC67B6635CEE053684EA8C0F378 000000008000281F SYS
* NETWORK_ACL_4700D2108291557EE05387E5E50A8899 0000000080002724 SYS
 
SQL> select * from dba_host_aces;
 
HOST LOWER_PORT UPPER_PORT ACE_ORDER START_DATE END_DATE GRANT_TYPE INVERTED_PRINCIPAL PRINCIPAL PRINCIPAL_TYPE PRIVILEGE
---- ---------- ---------- --------- ---------- -------- ---------- ------------------ --------- -------------- ---------
* 1 GRANT NO GSMADMIN_INTERNAL DATABASE RESOLVE
* 2 GRANT NO GGSYS DATABASE RESOLVE
towel.blinkenlights.nl 23 23 1 GRANT NO DEMO1 DATABASE CONNECT

Now I can connect from my user:

SQL> connect DEMO1/demo@//localhost/PDB1
Connected.
 
SQL> declare
2 c utl_tcp.connection;
3 n number:=0;
4 begin
5 c:=utl_tcp.open_connection(remote_host=>'towel.blinkenlights.nl',remote_port=>23);
6 end;
7 /
 
PL/SQL procedure successfully completed.

If you don’t know why I used towel.blinkenlights.nl, then just try to telnet to it and have fun…

 

Cet article 12c Access Control Lists est apparu en premier sur Blog dbi services.