Oracle Cluster with DRBD, Pacemaker, and Corosync

In this post, we are going to build an Oracle active-passive cluster using pacemaker, corosync, and DRBD.  Please note that any Oracle licensing comments made in this post are purely my personal opinion, they are not binding on my employer, or Oracle, nor do they have any legal standing.

This was originally intended simply as a thought exercise, could I put together a fairly resilient, fairly highly available, oracle configuration over shared storage without using Oracle RAC?  The answer to that question is that the shared storage piece is impossible without Oracle RAC, however, using DRBD, we can get something close.

DRBD stands for Distributed Replicated Block Device.  It works, as the name implies, by replicating blocks.  One DRBD device is designated as the primary device, additional devices are designated as secondary devices, and blocks are replicated from the primary device to the secondary device.

Pacemaker and Corosync are linux clustering software that allow for communication between the cluster nodes, maintain synchronization for cluster resources, and monitor the resources for availability.  When a resource becomes unavailable, they also manage the failover.

So, lets begin. 

The servers:  VMware workstations running OEL 7.6, 8G RAM.  In addition to a 20G root device, each server has a 20G vmdk for the drbd device for the Oracle database binaries, and another 20G vmdk device for the Oracle database data files. 

The DRBD devices are configured as logical volumes (LVM) in order to make adding space easier. 

The server names are linclust1 and linclust2.  Below are the hosts file, note that the NICs for cluster management are named linclust1-hb and linclust2-hb, and the storage management nics are named lincust1-priv and linclust2-priv.  It is definitely recommended that different NICS be used for the storage and internodal communications.

127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4
::1         localhost localhost.localdomain localhost6 localhost6.localdomain6

10.12.1.58 linclust1 linclust1.localdomain
10.12.1.59 linclust2 linclust2.localdomain

192.168.31.101 linclust1-priv linclust1-priv.localdomain
192.168.31.102 linclust2-priv linclust2-priv.localdomain

192.168.57.101 linclust1-hb linclust1-hb.localdomain
192.168.57.102 linclust2-hb linclust2-hb.localdomain

10.12.1.61 linclust-vip  linclust-vip.localdomain

Before doing any other cluster configuration, we have to configure LVM and DRBD.  The drbd devices show up on each node as /dev/sdb and /dev/sdc.

Create partitions using fdisk.

Verify the partitions available on the server: fdisk -l

Run fdisk /dev/sdb

Type ‘n’ to create a new partition.

Specify where you would like the partition to end and start.  You can set the number of MB of the partition instead of the end cylinder.  For example:  +1000M

Type ‘p’ to view the partition, and type ‘w’ to save the partition

Repeat for /dev/sdc

Since this RHEL/OEL7, the partition will automatically be created aligned on a sector boundary when we run fdisk. In early versions, we would need to align it manually.

Next, we create the logical volumes for each device. 

pvcreate /dev/sdb1
vgcreate shared1 /dev/sdb1
lvcreate –name shared1 -l 100%FREE shared1

pvcreate /dev/sdc1
vgcreate shared2 /dev/sdc1
lvcreate –name shared2 -l 100%FREE shared2

Next we install the required software on both cluster servers:

yum –y install drbd pacemaker corosync pcs pcsd

The above command will install all the required clustering software.

Install the required oracle packages,

yum –y install oracle-database-server-12cR2-preinstall

Now we are ready to configure DRBD. DRBD continuously replicates data from the primary to the secondary device.

1. Edit global_common.conf, it should read as follows:

global {
usage-count no;
}
common {
net {
  protocol C;
}
}

2. Create the actual definition file for our configuration.  In our case, the name of the file is drbd00.res, and has the following lines:

resource drbd00 {
        device /dev/drbd0;
        disk /dev/shared1/shared1;
        meta-disk internal;
        net {
                  allow-two-primaries;
        }
        syncer {
           verify-alg sha1;
        }
        on linclust1.localdomain {
                address 192.168.31.101:7789;
        }
        on linclust2.localdomain {
                address 192.168.31.102:7789;
        }
      }
resource drbd01 {
        device /dev/drbd1;
        disk /dev/shared2/shared2;
        meta-disk internal;
        net {
                  allow-two-primaries;
        }
        syncer {
           verify-alg sha1;
        }
        on linclust1.localdomain {
                address 192.168.31.101:7790;
        }
        on linclust2.localdomain {
                address 192.168.31.102:7790;
        }
      }

3. Copy drbd00.res and global_common.conf to /etc/drbd.d on the second cluster node. 

4. At this point we are ready to start drbd.  Note that we are using the option ‘allow two primaries’.  This is because PCS will manage the mounting of the file system for the software and data.

Run these commands to initialize drbd:

drbdadm create-md drbd00

drbdadm create-md drbd01

The above commands initialize the drbd data.

5. Start drbd

systemctl start drbd.service

systmctl enable drbd.service

6. Run the commands below to assign the primary nodes, Run these only on node 1:

drbdadm primary drbd00 –force

drbdadm primary drbd01 –force

7. The primary command designates the current node as the primary node so that we can make changes to the drbd device attached to this node.  At this point DRBD is running.  We can see the DRBD devices at /dev/drbd0 and /dev/drbd1.

8. Next, we need to create a file system on the drbd devices.  Since we are going to use oracle and active-passive, we will create an xfs file system on each device.  The commands below are run on the primary node:

mkfs –t xfs /dev/drbd0

mkfs –t xfs /dev/drbd1

The mkfs command will not work where the disks are secondary.

9. At this point the replication should be working.  Run the command below to check:

[root@linclust1 ~]# cat /proc/drbd
version: 8.4.5 (api:1/proto:86-101)
srcversion: 1AEFF755B8BD61B81A0AF27
0: cs:Connected ro:Primary/Secondary ds:UpToDate/UpToDate C r—–
    ns:406265 nr:383676 dw:790464 dr:488877 al:35 bm:0 lo:0 pe:0 ua:0 ap:0 ep:1 wo:f oos:0
1: cs:Connected ro:Primary/Secondary ds:UpToDate/UpToDate C r—–
    ns:4318663 nr:5086791 dw:9409141 dr:470229 al:211 bm:0 lo:0 pe:0 ua:0 ap:0 ep:1 wo:f oos:0

Note that if the status does not show UpToDate, wait for a bit, check again, and verify that everything is up to date.

10. DRBD is now fully configured and working.  As part of the cluster configuration we are going to mount these as file systems, so the next step is to create the mount points. I am going to use /u01 and /u02.

mkdir –p /u01

mkdir –p /u02

chown oracle:oinstall /u01

chown oracle:oinstall /u02

Next, we configure the cluster.  The cluster software we installed earlier includes pcs, pacemaker, and corosync.

When we installed pcs, the user hacluster was created.  Modify the /etc/passwd file for the hacluster to allow user login, as shown below:

hacluster:x:189:189:cluster user:/home/hacluster:/bin/bash

Then create the directory for hacluster, and set hacluster:hacluster as the owner:

mkdir –p /home/hacluster

chown hacluster:hacluster /home/hacluster

Next, set the password for the hacluster user using the passwd command:

passwd hacluster

Now, start the cluster services:

systemctl start pcsd.service

systemctl enable pcsd.service

In order for the cluster to manage itself, we have to authorize the access for the cluster manager on each node.  The ‘pcs cluster auth’ command does that:

pcs cluster auth lincust1 linclust2

Next we create the cluster. Note that we are using the interface –hb for cluster management.  This should be an internal only network:

pcs cluster setup –name DRBD_CLUSTER linclust1-hb linclust2-hb

Disable stonith.  We do not want to fence a node that is not working, drdb and pcs should be able to handle it properly:

pcs property set stonith-enabled=FALSE

pcs cluster start –all

At this point, the basic cluster services are configured and the cluster is running. Now its time to configure the required services.

First create the virtual IP address (VIP) that users will use to connect to the database.  The users don’t want to try and figure out which IP is correct, so a simple virtual IP is created that will always run where Oracle is running is created.

pcs resource create ClusterVIP ocf:heartbeat:IPaddr2 ip=10.12.1.61 cidr_netmask=32 op monitor interval=30s

Next, the resources to manage drbd.  Since we have two drbd devices, we will need 4 resource.  The ‘raw’ resource (note, name chosen by me) simply tells PCS to keep track of the DRBD device.  The ‘master’ service tells PCS that it has to manage (master) the drbd service it created:

pcs resource create binraw ocf:linbit:drbd drbd_resource="drbd00" op monitor interval=10s
pcs resource master binmaster binraw master-max=1 master-node-max=1 clone-max=2 clone-node-max=2 notify=true
pcs resource create dataraw ocf:linbit:drbd drbd_resource="drbd01" op monitor interval=10s
pcs resource master datamaster dataraw master-max=1 master-node-max=1 clone-max=2 clone-node-max=2 notify=true

Note this entry: ocf:linbit:drbd.  That is the type of service for PCS to monitor.  DRBD comes with a large collection of services already defined for it to monitor.  The user can also create her own. For a complete list of available services, run the command ‘pcs resource list’.

Next, we mount the oracle binaries and file system and tell PCS to manage it:

pcs resource create BINARIES filesystem device="/dev/drbd0" directory="/u01" fstype="xfs"
pcs resource create DATAFILES filesystem device="/dev/drbd1" directory="/u02" fstype="xfs"

Next, we configure some colocation and startup rules:

pcs constraint colocation add binmaster with ClusterVIP INFINITY
pcs constraint colocation add datamaster with ClusterVIP INFINITY
pcs constraint order ClusterVIP then binmaster then datamaster

pcs constraint colocation add BINARIES with binmaster INFINITY;
pcs constraint colocation add DATAFILES with datamaster INFINITY;
pcs constraint order promote binmaster then start BINARIES
pcs constraint order promote datamaster then start DATAFILES
pcs resource defaults migration-threshold=1
pcs resource group add oracle ClusterVIP BINARIES  DATAFILES

Now, its time to install the Oracle software and create the database.  Just use the regular oracle installer, and dbca to create the database. Remember that the binaries go on /u01 and the data files go on /u02.  You install on only one node. 

Once the oracle installation is complete, and the database is up and running, copy the files coraenv, dbhome, and oraenv from /usr/local/bin on node1 to the same directory on node 2.  Make sure the privileges are copied correctly. 

Copy the /etc/oratab and /etc/oraInst.loc files to node 2.  These five files are the only files that oracle requires that are not in /u01 or /u02.

Edit the file $ORACLE_HOME/network/admin/listener.ora.  Change the IP address of the listener to listen on the VIP for the cluster, in this case 10.12.1.61.  This will allow for the access to the database to fail over.

Create an oracle user to monitor the state of the database, default name is ocfmon:

SQLPLUS> create user ocfmon identified by mypassword;

SQLPLUS>grant create session to OCFMON;

Create the resources to monitor oracle:

pcs resource create oracleDB ocf:heartbeat:oracle sid="drbddb" –group=oracle

pcs resource update oracleDB monuser="ocfmon" monpassword="ocfmon" monprofile="default"

pcs resource create listenerdrbddb ocf:heartbeat:oralsnr sid="drbddb" listener="listener" –group=oracle

At this point, the cluster is created and services are running.

As I understand Oracle licensing, this configuration would require just one node to be licensed for Oracle.  The passive node cannot physically run oracle unless Oracle is shut down on the active node. And the Oracle software is not even viewable on the passive node unless the passive node has become the active node. In effect, Oracle is only installed on the active node, but when failover occurs the active node changes, oracle is dismounted from the passive node and mounted on the new active node.

This is what you should see when you check the status:

[root@linclust1 ~]# pcs status
Cluster name: DRBD_CLUSTER
Stack: corosync
Current DC: linclust1-hb (version 1.1.19-8.el7_6.4-c3c624ea3d) – partition with quorum
Last updated: Thu Feb 28 13:56:36 2019
Last change: Wed Feb 20 17:18:09 2019 by hacluster via crmd on linclust1-hb

2 nodes configured
9 resources configured

Online: [ linclust1-hb linclust2-hb ]

Full list of resources:

Master/Slave Set: binmaster [binraw]
     Masters: [ linclust1-hb ]
     Stopped: [ linclust2-hb ]
Master/Slave Set: datamaster [dataraw]
     Masters: [ linclust1-hb ]
     Stopped: [ linclust2-hb ]
Resource Group: oracle
     ClusterVIP (ocf::heartbeat:IPaddr2):       Started linclust1-hb
     BINARIES   (ocf::heartbeat:Filesystem):    Started linclust1-hb
     DATAFILES  (ocf::heartbeat:Filesystem):    Started linclust1-hb
     oracleDB   (ocf::heartbeat:oracle):        Started linclust1-hb
     listenerdrbddb     (ocf::heartbeat:oralsnr):       Started linclust1-hb

Daemon Status:
  corosync: active/enabled
  pacemaker: active/enabled
  pcsd: active/enabled

Note that this is not true HA.  It takes about 10 minutes for failover to occur and for Oracle to start on the second node.  If you need true HA, you will need to spend the money required for a commercial solution.

Whenever you failover, be sure and check the status of both drbd (cat /proc/drbd) and PCS (pcs status).  Because of slowness I saw in starting drbd, I added the following script to run after bootup to make sure drbd was up and running:

[root@linclust2 startup]# cat postboot.sh
#!/bin/bash
PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin
export PATH
sleep 3m
drbdadm up drbd00
drbdadm up drbd01
cat /proc/drbd > /tmp/drbd.out

To create the script above and ensure it runs each time the system starts, I created it as a service.  To do this, I created a file called postboot.service in /etc/systemd/system/. The file has the following contents:

[root@linclust2 system]# cat postboot.service

[Unit]
Description=Script to run things after everything else starts
After=network.target

[Service]
Type=simple
ExecStart=/root/startup/postboot.sh
TimeoutStartSec=0

[Install]
WantedBy=default.target

Note the file name after ExecStart=.  That is the file that gets executed.  To enable this, run this command:

systemctl enable postboot.service

systemctl start postboot.service

You can also modify the script at this point to include any other commands you need.

In my next blog, I will discuss how to add space to DRBD in lvm in this cluster configuration.

Advertisements

BI-Directional Replication in SymmetricDS and Oracle

Symmetric DS is a very nice replication tool that is fairly easy to use.  It replicates using SQL Apply, similar to what Oracle Golden Gate does.  Oracle Golden Gate is substantially easier to configure, but SymmetricDS also gets the job done, and is available as open source software.

For the purposes of this exercise, I am using SymmetricDS version 3.8.41, and Oracle version 18.3.0, both plain vanilla versions installed with no additional patches.

SymmetricDS uses a group concept to manage replication.  Each node is assigned a group, and each group has a set of replication rules assigned to it.  The rules are defined in the tables created to manage the replication.  The name of each of the management tables  begin with sym_.

Environment Summary

Both servers run Oracle Enterprise Linux 7.5 and and Oracle database version 18.3.  The database sid for store 1 is symdb, the database sid for store 2 is osymdb.  Server names are oelafd1 and oelafd2, and ip address are 10.12.1.220 and 10.12.1.235 respectively. DNSMASQ is used for names resolution.

Tnsnames entries:

STORE1 =
   (DESCRIPTION=
     (ADDRESS=(PROTOCOL=TCP)(HOST=10.12.1.220)(PORT=1521))
         (CONNECT_DATA=
           (SERVER=DEDICATED)
           (SERVICE_NAME=symdb)
         )
        )

STORE2 =
   (DESCRIPTION=
     (ADDRESS=(PROTOCOL=TCP)(HOST=10.12.1.235)(PORT=1521))
         (CONNECT_DATA=
           (SERVER=DEDICATED)
           (SERVICE_NAME=osymdb)
         )
        )

Each store schema has 3 tables, test1, test2, and test3 that should be identical.

Installation:

Installing SymmetricDS is very simple.  Download the zip file from the web site, https://www.symmetricds.org/download.

Put the file in an appropriate directory on your environment (in my case, it is owned by Oracle on the database server), and unzip the file.  Since SymmetricDS uses Java, it is ready to run once unzipped.

Setup

To set up SymmetricDS, there are three areas that must be configured, the engine file that defines the environment for the Java engine to use, the database setup that defines the environment, and the database setup that defines the replication rules.

Node/Group Identification Concepts

The individual nodes are defined in the sym_node table.  This is just a list of the individual nodes involved in the replication process.

The sym_node_security table assigns creates connection information among the nodes.

The sym_node_group table contains the names of the groups involved in replication. In this example, we only have one group.

The sym_node_group_link table defines how data is replicated between groups. If the data_event_action column is set to ‘P’ data is pushed on events. If it is set to ‘W’ the group waits for a pull event. Note that for this example, we only have one group (store) and thus both stores will push data.

Driver Table Concepts

At the lowest level, the sym_channel table allocates a channel for replication.  For large loads, multiple channels can be created. 

Next, the sym_trigger table defines what tables are replicated, what events trigger events, and what channel the event is sent to.

The sym_router table defines routers that route events between groups.  A router can have the same group as the source and destination, this would imply bi-directional replication is configured.

The sym_trigger_router table identifies what events are sent to which routers.

So, the basic concept is that you define a channel, define trigger event and assign it to a channel, define a router, and assign a trigger to a router.

Engine Setup

We have two stores configured for this two way replication, both in group store.  The engine files are store-001.properties and store-002.properties.

store-001.properties:

engine.name=store-001

# The class name for the JDBC Driver
db.driver=oracle.jdbc.driver.OracleDriver

# The JDBC URL used to connect to the database
db.url=jdbc:oracle:thin:@10.12.1.220:1521:symdb

# The user to login as who can create and update tables
db.user=store

# The password for the user to login as
db.password=store4321

# The HTTP URL of the root node to contact for registration
registration.url=http://10.12.1.220:31415/sync/store-001
sync.url=http://10.12.1.220:31415/sync/store-001

# Do not change these for running the demo
group.id=store
external.id=001

# This is how often the routing job will be run in milliseconds
job.routing.period.time.ms=500
# This is how often the push job will be run.
job.push.period.time.ms=1000
# This is how often the pull job will be run.
job.pull.period.time.ms=1000

store-002.properties:

engine.name=store-002

# The class name for the JDBC Driver
#db.driver=com.mysql.jdbc.Driver
db.driver=oracle.jdbc.driver.OracleDriver

# The JDBC URL used to connect to the database
db.url=jdbc:oracle:thin:@10.12.1.235:1521:osymdb

# The user to login as who can create and update tables
db.user=store

# The password for the user to login as
db.password=store4321

# The HTTP URL of the root node to contact for registration
registration.url=http://10.12.1.220:31415/sync/store-001
sync.url=http://10.12.1.220:31415/sync/store-002

# Do not change these for running the demo
group.id=store
external.id=002

# This is how often the routing job will be run in milliseconds
job.routing.period.time.ms=500
# This is how often the push job will be run.
job.push.period.time.ms=1000
# This is how often the pull job will be run.
job.pull.period.time.ms=1000

Please take note of the registration URL and sync URL.  They are very poorly documented by the SymmetricDS documentation, but they are critical for the replication to work.  Both URLS use the IP address where the SymmetricDS engine is running, not the IP address where the database is running.  SymmetricDS does expect one master engine, and that is where the registration URL resides.

Creating the driver tables

The required tables are created by the SymmetricDS engine, with the command create-sym-tables. The syntax is shown below:

./bin/symadmin –engine store-001 create-sym-tables

The output should show the creation of the tables for replication.  This command will also initialize the identification of the source node.  For reference, the commands below will also load the identification tables:

insert into store.sym_node 
(node_id,node_group_id,external_id,sync_enabled,sync_url,schema_version,symmetric_version, 
database_type,database_version,heartbeat_time,timezone_offset,batch_to_send_count, 
batch_in_error_count,created_at_node_id) values 
('001','store','001',1,null,null,null,null,null,current_timestamp,null,0,0,'000');

insert 
into store.sym_node 
(node_id,node_group_id,external_id,sync_enabled,sync_url,schema_version,symmetric_version, 
database_type,database_version,heartbeat_time,timezone_offset,batch_to_send_count, 
batch_in_error_count,created_at_node_id) values 
('002','store','002',1,null,null,null,null,null,current_timestamp,null,0,0,'000');<BR>insert 
into store.sym_node_identity values ('001'); 

insert into 
store.sym_node_security 
(node_id,node_password,registration_enabled,registration_time,initial_load_enabled,initial_load_time, 
created_at_node_id) values 
('001','5d1c92bbacbe2edb9e1ca5dbb0e481',0,current_timestamp,0,current_timestamp,'000'); 

insert 
into store.sym_node_security 
(node_id,node_password,registration_enabled,registration_time,initial_load_enabled,initial_load_time, 
created_at_node_id) values 
('002','5d1c92bbacbe2edb9e1ca5dbb0e481',1,null,1,current_timestamp,'000');

-- note: the insert above sets initial_load_enabled to 1 to enable initial load

insert into sym_node_group (node_group_id) values 
('store'); 

commit;

Be sure and verify that all the data has been properly loaded to the tables above. All tables named sym _ are required for replication to run properly.  Review the output and resolve any errors that occur.

Next, set up the replication rules.  Below is the configuration we use:

-- Configures the type of synch action, in this case we configure a Push 

-- (‘P’) for store to store replication.
insert into sym_node_group_link (source_node_group_id, target_node_group_id, 
data_event_action) values ('store', 'store', 'P');

--Configure a replication channel, this just says we will use the store 
channel for replication

insert into sym_channel (channel_id, processing_order, max_batch_size, 
enabled, description) values('store_outpound_ch', 1, 100000, 1, 'outbound 
channel');

-- Configure one trigger for each table in this case, wildcards may be 
used

insert into sym_trigger (trigger_id, source_table_name, channel_id, 
last_update_time, create_time,sync_on_incoming_batch) values 
('store_outbound_tr_t1', 'TEST1','store_outpound_ch',sysdate,sysdate,1);

insert into sym_trigger (trigger_id, source_table_name, channel_id, 
last_update_time, create_time,sync_on_incoming_batch) values 
('store_outbound_tr_t2', 'TEST2','store_outpound_ch',sysdate,sysdate,1);

insert into sym_trigger (trigger_id, source_table_name, channel_id, 
last_update_time, create_time,sync_on_incoming_batch) values 
('store_outbound_tr_t3', 'TEST3','store_outpound_ch',sysdate,sysdate,1);

-- Router routes data from group to group.

insert into sym_router (router_id,source_node_group_id,target_node_group_id,router_type,create_time,last_update_time) values('store_2_store', 
'store', 'store', 'default',current_timestamp, current_timestamp);

--Identifies which triggers use which routers. 

insert into 
sym_trigger_router (trigger_id,router_id,initial_load_order,last_update_time,create_time) values('store_outbound_tr_t1','store_2_store', 
1, current_timestamp, current_timestamp);

insert into 
sym_trigger_router (trigger_id,router_id,initial_load_order,last_update_time,create_time) values('store_outbound_tr_t2','store_2_store', 
1, current_timestamp, current_timestamp);

insert into 
sym_trigger_router (trigger_id,router_id,initial_load_order,last_update_time,create_time) values('store_outbound_tr_t3','store_2_store', 
1, current_timestamp, current_timestamp);</P>

Note that the sym_trigger table will default to sync on insert, update, or delete.  If you do not want to do that (eg, you may only want to track inserts and updates).

Next, start the replication.

../bin/sym (from the engines subdirectory).  The initialization output will look something like this.  Watch for errors:

[oracle@oelafd1 bin]

$ ./sym

Log output will be written to /home/oracle/symmetric-server-3.8.41/logs/symmetric.log

[startup] – SymmetricWebServer – About to start SymmetricDS web server on host:port 0.0.0.0:31415

[startup] – / – Initializing Spring root WebApplicationContext

[store-002] – AbstractSymmetricEngine – Initializing connection to database

[store-001] – AbstractSymmetricEngine – Initializing connection to database

[store-001] – JdbcDatabasePlatformFactory – Detected database ‘Oracle’, version ’18’, protocol ‘oracle’

[store-001] – JdbcDatabasePlatformFactory – The IDatabasePlatform being used is org.jumpmind.db.platform.oracle.OracleDatabasePlatform

[store-002] – JdbcDatabasePlatformFactory – Detected database ‘Oracle’, version ’18’, protocol ‘oracle’

[store-002] – JdbcDatabasePlatformFactory – The IDatabasePlatform being used is org.jumpmind.db.platform.oracle.OracleDatabasePlatform

[store-002] – OracleSymmetricDialect – The DbDialect being used is org.jumpmind.symmetric.db.oracle.OracleSymmetricDialect

[store-002] – StagingManager – The staging directory was initialized at the following location: /home/oracle/symmetric-server-3.8.41/tmp/store-002

push request from store:001:001.

[store-001] – PushService – Pushed data to node store:002:002. 1 data and 1 batches were processed

[store-001] – StagingManager – Cleaning staging…

[store-002] – StagingManager – Cleaning staging…

[store-002] – StagingManager – Finished cleaning staging in 0 seconds.

[store-001] – StagingManager – Finished cleaning staging in 0 seconds.

[store-002] – RouterService – Routed 1 data events in 264 ms

[store-002] – PushService – Push data sent to store:001:001

[store-001] – DataLoaderService – 1 data and 1 batches loaded during push request from store:002:002.

[store-002] – PushService – Pushed data to node store:001:001. 1 data and 1 batches were processed

[store-001] – RouterService – Routed 1 data events in 330 ms

[store-001] – PushService – Push data sent to store:002:002

[store-002] – DataLoaderService – 1 data and 1 batches loaded during push request from store:001:001.

[store-001] – PushService – Pushed data to node store:002:002. 1 data and 1 batches were processed

The data events are data changes.

At this point, the replication should be loaded.  Run a command on each instance to verify.

On store1:

select * from test1;

1    NAME    ANDY

10    NAME    ANDREW

11    NAME    ANDREW

On store2:

select * from test1;

1    NAME    ANDY

10    NAME    ANDREW

11    NAME    ANDREW

On store1:

insert into test1 (id,name,value) values (12,’LNAME’,’SMITH’);

commit;

select * from test1;

1    NAME    ANDY

10    NAME    ANDREW

11    NAME    ANDREW

12    LNAME    SMITH

store2

select * from test1;

1    NAME    ANDY

11    NAME    ANDREW

10    NAME    ANDREW

12    LNAME    SMITH

insert into test1 (id,name,value) values (14,’LNAME’,’JONES’);

commit;

select * from test1;

1    NAME    ANDY

11    NAME    ANDREW

10    NAME    ANDREW

12    LNAME    SMITH

14    LNAME    JONES

store1:

select * from test1;

1    NAME    ANDY

10    NAME    ANDREW

11    NAME    ANDREW

12    LNAME    SMITH

14    LNAME    JONES

Two way replication is working.  Test further with updates and deletes.

In this blog we went over the creation of basic 2-way replication in SymmetricDS and Oracle.  Note that we have not discussed any of the classic problems of conflict resolution that will arise when running bi-directional replication.

Installing OEM CC Patches (OEM Cloud Control 13.2)

Recently I was asked to patch an OEM Cloud Control 13.2 environment.  So, per the instructions, I updated the opatch to the latest version of OPatch for OEM, and I updated the omspatcher software to the latest omspatcher version.  Both of those steps are well documented in MOS.

The patch I was installing was generic patch 28628403, which is the plugin patch for OEM Cloud Control.

I followed all the instructions, extracted the patch, and of course the first thing I determined is the readme is incorrect.

The readme for Oracle tells us to shut down cloud control.  However, if you do that you will discover that OMS and the database both must be up when you run omspatcher.

Next, the instructions for applying the patch tell you to run the command:

omspatcher apply [–property_file <location of property file>]

Of course it doesn’t tell you what the properly file is. The property file simply contains login information for weblogic.  Its not really necessary, since omspatcher will prompt you for the username and password, so just use the command below:

omspatcher apply

When I tried running the command above, I received this output:

$ omspatcher apply
OMSPatcher Automation Tool
Copyright (c) 2017, Oracle Corporation.  All rights reserved.

OMSPatcher version : 13.8.0.0.3
OUI version        : 13.9.1.0.0
Running from       : /u01/app/oracle/middleware
Log file location  : /u01/app/oracle/middleware/cfgtoollogs/omspatcher/opatch2018-12-04_12-36-08PM_1.log

OMSPatcher failed: Location "/export/home/oracle/patch/28628403" is not a valid System patch location.
Log file location: /u01/app/oracle/middleware/cfgtoollogs/omspatcher/opatch2018-12-04_12-36-08PM_1.log

OMSPatcher failed with error code 73

So that is really strange, why would we get that error message.  Research on the metalink site finally gave me the information.  It was a problem with the locale setting on the server. To fix it run these commands:

export LC_ALL=en_GB.UTF-8
export LANG=en_GB.UTF-8

To see the full details, see metalink note 2321057.1.

While the issue with the locale setting is the major problem, it appears that Oracle documentation for for omspatcher and Oracle cloud control in general is going down hill.  Oracle should direct some new resources at updating their documentation. 

I chose to use oracle OEM CC 13.2 for this installation.  This was because the latest and greatest version, 13.3, seems to have multiple issues with backward compatibility that have never looked at or fixed.  In particular, we ran into issues trying to SSH the agent to SUN servers running older versions of Solaris.  Which is inexcusable since Oracle now owns that software.

Oracle Restart On File System

 

Many people may not be aware of the fact that Oracle restart (OHAS) can be configured on databases that are storing their data on a file system. ASM is not required.

The installation and configuration process took some work to figure out, since Oracle does not document too well, but I did track it down eventually.

In my current environment, I have installed the Oracle database software and created a database using the silent installation method. See the blog here at houseofbrick.com: http://houseofbrick.com/oracle-12-2-0-1-silent-installation/ for instructions on how to do silent installation and database creation.

Next, go to the Oracle website and download the the Grid Infrastructure software.  I noted that for some reason, Oracle has chosen to force you to download what amounts to an entire GI home, so you should place the zip file in the location of your grid directory. eg, if you intend to put the GI home in /u01/app/oracle/grid, place the zip file in /u01/app/oracle/grid.   Then unzip the file, eg:

mkdir –p /u01/app/grid

cp  linuxx64_12201_grid_home.zip /u01/app/grid

unzip linuxx64_12201_grid_home.zip

Next, install the the software.

In the directory /u01/app/grid, run the file gridSetup.sh.  This file requires x-windows.

Choose the setup software only option

 

 

On the next screen, select the server.  The server name should be configured in the hosts file.

On the next screen accept the defaults. 

 

When prompted, choose Yes.

Next verify the installation directory.  Its wrong, move the files and start over.  You cannot change it here.

 

Next, choose the appropriate option for the root scripts.

Verify the prerequisites, correct or ignore as necessary.

Choose install on the next screen.

Once completed, run the root scripts

 

 

[root@rhel7 grid]# ./root.sh
Performing root user operation.

The following environment variables are set as:
    ORACLE_OWNER= oracle
    ORACLE_HOME=  /u01/app/grid/grid

Enter the full pathname of the local bin directory: [/usr/local/bin]:
The contents of "dbhome" have not changed. No need to overwrite.
The contents of "oraenv" have not changed. No need to overwrite.
The contents of "coraenv" have not changed. No need to overwrite.

Entries will be added to the /etc/oratab file as needed by
Database Configuration Assistant when a database is created
Finished running generic part of root script.
Now product-specific root actions will be performed.

To configure Grid Infrastructure for a Cluster or Grid Infrastructure for a Stand-Alone Server execute the following command as oracle user:
/u01/app/grid/grid/gridSetup.sh
This command launches the Grid Infrastructure Setup Wizard. The wizard also supports silent operation, and the parameters can be passed through the response file that is available in the installation media.

At this point, the GI has been installed.  Note that we had to install only because otherwise the installer will assume that you want to install an ASM instance, and there is now way around that through the installer.

So, our next step is to enable oracle HAS. 

su – root.  Set your environment to your newly installed GI home.  In my case, to make things easier, I put this entry in the oratab:

grid:/oracle/app/grid:N

So as root, I just run oraenv and choose grid:

[root@rhel7 grid]# . oraenv
ORACLE_SID = [root] ? grid
The Oracle base has been set to /u01/app/oracle

Next, run this command to configure and enable has for a file system:

$ORACLE_HOME/perl/bin/perl-I$ORACLE_HOME/perl/lib -I$ORACLE_HOME/crs/install $ORACLE_HOME/crs/install/roothas.pl

The output should look like this:

root@rhel7 ~]# $ORACLE_HOME/perl/bin/perl -I$ORACLE_HOME/perl/lib -I$ORACLE_HOME/crs/install $ORACLE_HOME/crs/install/roothas.pl
Using configuration parameter file: /u01/app/grid/grid/crs/install/crsconfig_params
The log of current session can be found at:
  /u01/app/oracle/crsdata/rhel7/crsconfig/roothas_2017-11-06_02-32-44PM.log
2017/11/06 14:32:49 CLSRSC-363: User ignored prerequisites during installation
LOCAL ADD MODE
Creating OCR keys for user ‘oracle’, privgrp ‘oinstall’..
Operation successful.
LOCAL ONLY MODE
Successfully accumulated necessary OCR keys.
Creating OCR keys for user ‘root’, privgrp ‘root’..
Operation successful.
CRS-4664: Node rhel7 successfully pinned.
2017/11/06 14:33:31 CLSRSC-330: Adding Clusterware entries to file ‘oracle-ohasd.service’
2017/11/06 14:35:08 CLSRSC-400: A system reboot is required to continue installing.

Next, per the instructions above, reboot the server.

Once the server has been restarted, as root, start the high availability service:

[root@rhel7 grid]# . oraenv
ORACLE_SID = [root] ? grid
The Oracle base has been set to /u01/app/oracle

crsctl start has

Now, as oracle enable has:

crsctl enable has

Finally, add the database and to OHAS:

srvctl add db -db silent -startoption open -oraclehome /u01/app/oracle/product/12.2.0.1/dbhome_1
srvctl start db -d silent
srvctl add listener -l listener -o $ORACLE_HOME
srvctl start listener

At this point, you have successfully installed Oracle HAS (High Availability Services) for a database running on file system.

DBA Security Frustration

One of the most common frustrations for the Oracle DBA is dealing with security.  There are many common DBA tasks that require root or administrator access, and acquiring that access can be a frustrating and time consuming task, especially in larger organizations.

In the world of virtualization (sometime called the private cloud), those security concerns are much easier to handle, even though the security teams  in large organizations have not caught up with that fact in most cases.  Even the primary providers of the private cloud, VMware, seem to be unaware of the how a major side-effect of their product is a real reduction in security concerns.

A connection to a Virtual Machine (VM) looks exactly like a connection to a physical machine, whether Unix or Windows based.  And when a user is logged into a VM, as with a physical machine, the user can only see and access what is on that particular VM.  This seems obvious, so what is my point, right? 

My point is this:  virtualization allows an organization to create a VM for each individual application within an organization.  The native networking components of virtualization products allow for even more isolation.  Typically, in a large organization, the intent of security is to prevent an individual from accessing data outside his or her particular area of application.  Meaning, for example, the DBA in charge of medical records database should not be able to access a financial database 

Virtualization allows the database for health care records to reside on one VM, while financial data can reside on another VM, even on another network, without increasing the Oracle licensing costs. 

The use of VM’s allows the DBA in charge of health care records database to be restricted to the VM or VM’s on which that data resides.  He or she can be granted full root and Oracle access on that server, without any risk of the DBA being able to access the financial database, which he is not authorized to see.

In order to access the financial data, the health care records DBA would need to acquire a completely different set of passwords and access, which is easy to restrict at the server level.  This means that the security team can grant the DBA full access to every server that his or her data is on, without risking access to data to which he is not entitled. Although the same strategy could be used in the physical world, the cost would soon become prohibitive and extremely inefficient in terms of hardware resources.

Of course this does not alleviate all security concerns. There is always the possibility of a fully compromised server. This is most often the result of an external attack rather than internal. However, a fully compromised server inside the company firewall can be a nightmare. And as the number of servers increase, whether physical or virtual, the chance of a compromised server increases. On the other hand, the chance that a virtual server becomes fully compromised is roughly the same as that of a physical server, regardless of the role based security being used.

In addition, the risks of a compromised server can be minimized by the appropriate use of physical and virtual firewall appliances, and appropriate rules to segregate servers by function. VMware NSX is specifically designed for this sort of network micro-segmentation and isolation, and is the logical method for achieving the required network isolation within a VMware environment.

The use of virtualization as a security method in and of itself is a completely new paradigm for the typical security team.  Normally separation of duties would require that the DBA either have no root access at all, or be restricted to specific commands in order to ensure that they cannot access information to which they are not entitled.  But with the appropriate use of VM’s, the DBA can have full and unfettered access to the server with their data, without putting any other data at risk.

This does not absolve the DBA of basic responsibilities of course.  When installing Oracle patches that require root access, they must still take the basic precautions, backing up the database, snapshotting the operating system drive and perhaps the oracle binaries installation, and taking any other basic precautions that are required.  The DBA should also take the time to learn some operating system basics, and perhaps chat with a system administrator if they are not sure of what they are doing.  At the same time, this high level of access should allow the DBA to get their job done more quickly, and with much less frustration.

In summary, VMware and other suppliers for the private cloud have unintentionally provided an easy and effective method of improving organizational security, with very little additional effort required.

Installing the Oracle Database (Basic Install)

In this post, I am going to cover the simplest of Oracle software installations. A single instance Oracle installation on a file system. The first step is to download the Oracle software installation files. The oracle database software can be downloaded from here: http://www.oracle.com/technetwork/database/enterprise-edition/downloads/index.html. Or, if you have an Oracle CSI and appropriate licensing you can download the latest patchset from My Oracle Support (MOS).

For this installation, we are using file system, so we need the first two files, linuxamd64_12c_database_1of2.zip and linuxamd64_12c_database_2of2.zip. Files 3 and 4 are required if you plan to user ASM, or are installing oracle Grid Infrastructure (GI).

1. Download the files to your database server. I will be using a virtual server that was set up using the methodology described here: https://dbakerber.wordpress.com/2015/09/23/installing-oracle-software-part-1-prerequisites/.

2. Unzip the database files. This will create a directory named ‘database’ under your current directory..

3. Start the installer:

cd database

./runInstaller

Uncheck the box about security updates, if you are already signed up it will just add to your volume of emails, which you probably don’t want.

Then assure Oracle that you really do know what you are doing.

On the next screen, choose what you consider appropriate. For this example, we will go ahead and create and configure a starter database.

Next, choose server class, even if installing on a desktop class vm. The server class option gives more flexibility in installation options.

On the next screen choose advanced install. Once again, this gives us more installation options.

Next choose the appropriate language. This is a US English blog, so that is what we choose.

Next, choose the appropriate edition. The particular download we are using only has enterprise edition, but a full download from otn.oracle.com will have both Enterprise and Standard Edition. For details about the advantages of each, I recommend a contact other than Oracle corp. Oracle will of course recommend the most costly option of Enterprise Edition. One possible source of information is the consulting firm House of Brick Technologies (http://houseofbrick.com/) , though as a consulting firm they do charge for their time. They can discuss the advantages of various Oracle editions, as well as virtualization of your database load.

I normally install in the default directory structure, /u01. Obviously this can change with your preference and company policies. But for this example, we will take the default file system.

On the next screen, also take the default.

Next, choose your database type. This screen will not appear if you did not choose to create a database earlier.

We are not going to choose the default oracle database name. This is one of those well known items that can lead to security holes.

We are going to name our instance dbatest. We will not create it as a container database, you can change this later if you want.

In the next screen, you choose the memory footprint. Note that by default, this installation will use Automatic Shared Memory Management (ASMM), not Automatic Memory Management (AMM). There is substantial ongoing debate among DBA’s on which method is better. If at some point you may want to use hugepages, you must use ASMM. Otherwise, the general opinion among DBA’s is to use ASMM. ASMM generally gives a little more control of memory usage, and most DBA’s prefer to use it.

Click on the character set tab to choose the character set. I generally prefer to use the Unicode character set, AL32UTF8.

Since this is a training exercise, choose the install sample schemas option on the sample schemas tab.

Next, choose the location of the oracle database files. I normally use ASM, but for this example we are using file system, so the default file location is fine. Change it if you require.

On the next screen, if you choose to register the database with OEM Cloud Control, click on the box. If you don’t want to register, or have no idea what it is, leave the box unchecked.

On the next screen, enable recovery if you choose. I am going to go ahead and enable recovery, and choose a location, since I may want to use this instance when I discuss recovery in a later blog, however, generally we don’t bother with recovery on test instances.

On the password screen, it is generally easiest to choose a single password for all schemas, then change them individually per your company’s standards.

Use the default groups. Changing these can sometimes cause problems.

The installer will next verify that the installation requirements have been met, and come up with a summary screens.

I am going to save a response file here, because I plan to do another blog topic on silent installation at a later date.

After clicking ‘install’, the installation commences.

After installation is complete, the prompt to run the root scripts will come up. If you have the appropriate access, run these scripts as the root user, otherwise you will need to have a system administrator or other person with root access run it.

After running the root scripts, click continue, and then the database configuration assistant will start running to create the database that you defined earlier.

After software installation and database creation is completed, a screen will pop up with the database information, including the database express URL.

Save the URL for the em database express, and click ok.

Then click on close.

At this point, your database is up and ready to use.

For an easy to use hypertext management front end, oracle supplies the database express, whose URL you coped earlier, htttps://oel64node.localdomain:5500/em, in my example.

Navigate to the database express URL using your web browser of choice.

If you are new to oracle, I suggest that you explore this page to familiarize yourself with the oracle database.

This then is the easiest and most basic method of installing the Oracle database.

Installing Oracle Software, Part 1 – Prerequisites

Forward

The intent of this series of posts is to cover basic Oracle skills for the beginning DBA. These are follow-on posts to my previous article titled ‘Eleven Critical Skills for the Beginning DBA’  https://dbakerber.wordpress.com/2015/09/14/eleven-critical-skills-for-the-beginning-dba/.

I debated with myself for some time on how detailed I need to be, and at what point in the installation process I should begin this article. After some consideration, I have decided to start this entry with the installation of Oracle required packages.

This article assumes that the Oracle user has been created per the Oracle installation documentation, that transparent hugepages have been disabled, and that the required kernel setting changes have been made for an Oracle installation.

I considered it unlikely that the beginning DBA would be entrusted with those three preliminary tasks in a typical working environment.

Admittedly, the beginning DBA may well not be entrusted with installing packages either, but that was the point that I chose to start this article.

Step 1 – Install the Oracle Required Packages

Installing the Oracle required packages is straightforward. And in fact remarkably easy if you are on Oracle Enterprise Linux (OEL). First of all, your database server must be configured with repository access for its Unix version. Without repository access, none of the yum commands below will work.

Next, if you are on Oracle Enterprise Linux, install this package: oracle-rdbms-server-12cR1-preinstall  or if you are installing 11g, install this package: oracle-rdbms-server-11gR2-preinstall. The command is simply (as root):

# yum install oracle-rdbms-server-12cR1-preinstall

or

# yum install oracle-rdbms-server-11gR2-preinstall

This will install all the oracle required packages, and configure the Oracle user, and set certain OS settings. This is the easiest way to do it. If you are using Oracle Enterprise Linux, you can skip to Part 2: Setting up Xwindows on your Dekstop.

If you are not using Oracle Enterprise Linux, you will need to install the individual packages by themselves, and configure the operating system as described in the remainder of this article.

The current list of required packages from 12c is below (note: tuned and xclock are not required, but are highly recommended):

binutils.x86_64
compat-db43.i686
compat-glibc-headers.x86_64
compat-glibc.x86_64
compat-libcap1.x86_64
compat-libstdc++-33.i686
compat-libstdc++-33.x86_64
elfutils-libelf-devel.x86_64
gcc-c++.x86_64
gcc.x86_64
gdbm.i686
gdbm.x86_64
glibc-common.x86_64
glibc-devel.i686
glibc-devel.x86_64
glibc-headers.x86_64
glibc.i686
glibc.x86_64
kernel-headers
ksh.x86_64
libaio-devel.i686
libaio-devel.x86_64
libaio.i686
libaio.x86_64
libgcc.i686
libgcc.x86_64
libgomp.x86_64
libstdc++-devel.i686
libstdc++-devel.x86_64
libstdc++.i686
libstdc++.x86_64
libXi.i686
libXp.i686
libXp.x86_64
libXtst
make.x86_64
mksh.x86_64
ncompress
nfs-utils
openmotif21.i686
portmap
sysstat.x86_64
unixODBC-devel.i686
unixODBC-devel.x86_64
unixODBC.i686
unixODBC.x86_64
unzip.x86_64
util-linux-ng.x86_64
xauth
xorg-x11-libs-compat.i386
xorg-x11-utils.x86_64
tuned
xclock

In order to install all of these packages quickly (as the root user), just build a shell script with these lines: and run it:

yum -y install binutils.x86_64
yum -y install compat-db43.i686
yum -y install compat-glibc-headers.x86_64
yum -y install compat-glibc.x86_64
yum -y install compat-libcap1.x86_64
yum -y install compat-libstdc++-33.i686
yum -y install compat-libstdc++-33.x86_64
yum -y install elfutils-libelf-devel.x86_64
yum -y install gcc-c++.x86_64
yum -y install gcc.x86_64
yum -y install gdbm.i686
yum -y install gdbm.x86_64
yum -y install glibc-common.x86_64
yum -y install glibc-devel.i686
yum -y install glibc-devel.x86_64
yum -y install glibc-headers.x86_64
yum -y install glibc.i686
yum -y install glibc.x86_64
yum -y install kernel-headers
yum -y install ksh.x86_64
yum -y install libaio-devel.i686
yum -y install libaio-devel.x86_64
yum -y install libaio.i686
yum -y install libaio.x86_64
yum -y install libgcc.i686
yum -y install libgcc.x86_64
yum -y install libgomp.x86_64
yum -y install libstdc++-devel.i686
yum -y install libstdc++-devel.x86_64
yum -y install libstdc++.i686
yum -y install libstdc++.x86_64
yum -y install libXi.i686
yum -y install libXp.i686
yum -y install libXp.x86_64
yum -y install libXtst
yum -y install make.x86_64
yum -y install mksh.x86_64
yum -y install ncompress
yum -y install nfs-utils
yum -y install openmotif21.i686
yum -y install portmap
yum -y install sysstat.x86_64
yum -y install unixODBC-devel.i686
yum -y install unixODBC-devel.x86_64
yum -y install unixODBC.i686
yum -y install unixODBC.x86_64
yum -y install unzip.x86_64
yum -y install util-linux-ng.x86_64
yum -y install xauth
yum -y install xorg-x11-libs-compat.i386
yum -y install xorg-x11-utils.x86_64
yum -y install tuned
yum -y install xclock
The above commands will install the required packages without prompting for further user input.
Part 2: Setting up X-Windows on Your Desktop
Thinking back to my early days as a DBA, I remember getting the X-windows configuration figured out was one of the most irritating pieces of the process. After all, who uses X-windows anymore? The answer of course is Oracle. So here are the steps to set it up on the server and on your desktop.
First of all, make sure X-windows is installed on the Linux/Unix system. The easiest method for doing this is to check for the existence of the X11 packages on the system using the rpm command:
$ rpm -qa | grep X11
libX11-1.6.0-2.2.el6.x86_64
libX11-1.6.0-2.2.el6.i686
libX11-common-1.6.0-2.2.el6.noarch

If these packages do not show up, you will need to install X on the server. This requires root access, which may require discussion with the system administrator. The command to install X11 is this:

# yum install xorg-x11-apps

After identifying the application and all the required pre-requisites, yum will prompt for an OK to install the package. Enter Y and the packages will be installed.

Step 2, Installing an X-Server

Next, you will need an X-Server on your desktop. There are several available, the most common of which are Xming and Cygwin. Generally Xming has the smaller footprint, so it is what I recommend. Xming can be downloaded from here:

http://sourceforge.net/projects/xming/files/

Install xming for your software version onto your Desktop, and also download and install xming fonts.

Next, you will want an SSH software that supports X11 forwarding. The most common software for this is Putty. Putty can be downloaded from here:

http://www.putty.org/

Download the full putty installer for your version of windows, and install putty using the default options.

Next, start up Xming.

Use the X-Launch icon under the xming group, and choose the defaults, but select the box for ‘No Access Control’. Later you can experiment with unchecking the box, but for now turn it on. Once XLaunch finishes, you will see an Xming icon in your running programs, which is what you want.

Next, start up putty. Enter your hostname on the appropriate line.

Then on the left side, under connection, click on SSH, then X11. In the X11 window choose the ‘Enable X11 Forwarding’ box. In the ‘X display location.’ You can enter ‘localhost’, though that is not required.

Next, open your putty session to the database server (click on ‘open’) in the session window, and log in as the Oracle user, if possible.

Then, if you are logged in as the Oracle user, run these commands:

$ xclock

At this point, a small clock should show up in a window showing the current time. This indicates that you have X-Windows properly configured on the Oracle server, and that your Xserver is running properly on your desktop, so you can run the Oracle installer.

If xclock does not start, run this command (on the server):
export DISPLAY=localhost:10.0
And try again.

If you are not logged in as the Oracle user, but as yourself, do the following:

$ xauth list
myhost/unix:10 MIT-MAGIC-COOKIE-1 206e9b7808ad8cbf561dd6b2e42a29a7

Copy the above line (that begins with your host name). If the output has multiple lines, copy the last line to the clipboard. Then, sudo or su to the Oracle user, whichever method is available to you.

After switching to the Oracle user, run this command (replacing everything after add with your output from the above xauth list command):

$ xauth add myhost/unix:10 MIT-MAGIC-COOKIE-1 206e9b7808ad8cbf561dd6b2e42a29a7

$ xclock

At this point, a small clock should show up in a window showing the current time:

Once again, if xclock does not start, run this command (on the server):
export DISPLAY=localhost:10.0
And try again.

If the clock shows up, it indicates that you have X-Windows properly configured on the Oracle server, and that your Xserver is running properly on your desktop.

Now we are ready to install Oracle on Linux using the Oracle installer. Hit ctl-c to exit the xclock.

For my next post, I will continue with the installation instructions using the Oracle GUI installer.

Eleven Critical Skills for the Beginning DBA

I have been in the Oracle Database Administration business since about the year 2000 now, so at this point I feel like I am ready to write my first article ever on the basic DBA skills for a beginning DBA.

These are eleven critical skills every beginning DBA should master quickly if she expects to continue in the business. Please note this is not intended to be comprehensive, no doubt there are other important skills. These are just eleven skills I see as very important.

1. Install Oracle software.

This means install it using the gui, install it using silent install, install it on Windows, Linux, AIX, and install it through OEM Cloud Control. There are several different methods that might be used to install the Oracle software, and you need to know all of them.

2. Troubleshoot SQL Code.

You don’t need to be the SQL code guru, that’s for the senior DBA and senior developers but you do need to be able to understand what the SQL is doing, and make suggestions on how to improve it. That does mean you have to be able to get an ‘explain plan’, and identify the problems.

3. Backup a database

You should know how to back up a database through RMAN, and at least one other method, whether its exp, expdp, or OS utilities. Every good DBA should have two good backups and something in his back pocket to recover data.

4. Backup a table

You need to know how to back up a single table, just like you need to know how to back up a database. There are several different ways to do this, you should know at least three.

5. Restore a database to current/alternate location

This is a critical skill. You must have a good back up, and you must be able to restore the database. When your hardware crashes, you as the DBA better have a plan in place to restore your database. If you don’t, you should put a plan in place to get a new job.

6. Restore tablespace to alternate location

People are fallible, they corrupt their data all the time. It will be your job as a DBA to restore that tablespace and reload those corrupted tables.

7. Restore a table.

When you make a backup of a single table, because you are making changes, you better have a plan in place to restore it. There are several ways to do this, you need to know at least three.

8. Create a user and grant appropriate privileges and quotas.

This is a basic skill, and not hard. But make sure you don’t default to giving everyone unlimited tablespace. Make sure you know how to grant users access to only the tablespaces they need.

9. Read an alert log.

Does that line in the alert log mean anything? I just had a log switch, is it switching logs too frequently? You need to be able to review the last hundred or so lines in the alert log, and figure out right away if you have a problem.

10. Write a shell script.

This is probably the skill the most beginning DBA’s lack. You need to be able to write a shell script (most Oracle installations are on a Unix variant), put it in cron, and know how to send the output to the recipients who need to see it. Whether it is a report or an alert, you need to know this skill.

11. Create a dataguard standby

Failover/HA is big business these days. Oracle dataguard is a very common solution. Be familiar with it.

Those are the skills I see as most important. Some of these topics are discussed in my earlier blog entries. In the future, I will be covering each of these points in more detail, and giving my viewpoints on each of these, from the perspective a DBA that has worked in consulting, as well as in small and large companies.

Dave Welch at House of Brick Technologies Writes a Serious Takedown of a Gartner Article

I don’t normally discuss Oracle licensing on my blog. This is primarily because it is not an area I have spent a lot of time on. However, the licensing experts (note: they are not lawyers) over at House of Brick technologies are very good at explaining licensing issues with Oracle on VMware.

In this paper, Dave Welch at House of Brick writes a fairly severe takedown of a Gartner article on Oracle licensing. And while I did not read the Gartner article ($195 seems a little pricey to me), Welch makes a point repeatedly that is good to remember when dealing with Oracle: Oracle statements and blogs and other similar publications are not legally binding, only what is written in your contract or other supporting legal documents are legally binding. This is especially important when it comes to partitioning your Oracle installation.

Here is the article. http://houseofbrick.com/review-of-gartners-nov-2014-oracle-licensing-paper/

If you are looking for further information on Oracle licensing on VMware, there are several applicable articles on the House of Brick web site: http://houseofbrick.com/blog/

Running Jobs in Parallel using DBMS_SCHEDULER (part 2)

My second example of using DBMS_SCHEDULER to run jobs in parallel involves another common DBA task, rebuilding indices.

Under certain circumstances, deteriorating performance on a database can be traced to fragmented indices. In addition, direct path loads and certain other operations can invalidate indices, thus requiring them to be rebuilt.

In this example, indices that are invalid are identified and jobs submitted to rebuild the index. Not only can the individual rebuild processes run in parallel, but multiple rebuild jobs can be run at the same time by using DBMS_SCHEDULER.

This example does not use a driver table, but rather looks at the Oracle data dictionary tables to identify candidates for rebuild. The changes required to use a driver table containing the indices to rebuild are fairly simple, and I leave that to the reader.

In this example, we create a program that calls a stored procedure, and then individual jobs that call the program.  This allows us to use the arguments of owner and index name to specify each index to be rebuilt.

Below is the driving script, that finds each index needing to be rebuilt, and creates the job to rebuild them.  Typically we will rebuild two indexes at the same time, each with degree of parallelism set to four.

CREATE OR REPLACE
procedure maint_enable_rebuild(num_simul in integer default 2,par_degree in integer default 4) authid current_user
is
owner varchar2(30);
index_name varchar2(30);
type index_record is record
( owner varchar2(30),
index_name varchar2(30));
type index_array_type is varray(10000) of index_record;
index_count integer :=0;
lcounter integer :=0;
sql_cmd varchar2(2000);
index_array index_array_type;
num_running integer :=0;
continue_next varchar2(5) :='FALSE';
exit_now varchar2(5) :='FALSE';
num_ran integer :=0;
num_not_ran integer :=0;
num_short_count integer := num_simul;
begin
begin
maint_drop_rebuild_jobs;
exception
when others then null;
end;
dbms_scheduler.create_program
(program_name=>'REBUILD_INDEX_PROGRAM',
program_type=>'STORED_PROCEDURE',
program_action=>'MAINT_RUN_REBUILD',
number_of_arguments=>3,
enabled=>false,
comments=>'Stored procedure to rebuild unusable indexes asynchronously'
);
dbms_scheduler.define_program_argument (
program_name=>'REBUILD_INDEX_PROGRAM',
argument_name=>'owner',
argument_position=>1,
argument_type=>'varchar2'
);
dbms_scheduler.define_program_argument (
program_name=>'REBUILD_INDEX_PROGRAM',
argument_name=>'index_name',
argument_position=>2,
argument_type=>'varchar2'
);
dbms_scheduler.define_program_argument (
program_name=>'REBUILD_INDEX_PROGRAM',
argument_name=>'par_degree',
argument_position=>3,
argument_type=>'integer',
default_value=>4
);
dbms_scheduler.enable('REBUILD_INDEX_PROGRAM');
dbms_output.enable(100000);
index_array:=index_array_type();
for icursor in
(select owner, index_name from all_indexes where status in ('INVALID','UNUSABLE') and index_type='NORMAL' and owner !='SYS')
--/* testing only */ (select owner, index_name from all_indexes where owner='SCOTT' and table_name like 'E%')
loop
if icursor.owner is not null
then
index_count:=index_count+1;
index_array.extend();
index_array(index_count).owner:=icursor.owner;
index_array(index_count).index_name:=icursor.index_name;
-- dbms_output.put_line(to_char(index_count)||' '||icursor.owner||' '||icursor.index_name);
end if;
end loop;
for lcounter in 1..index_count
-- for lcounter in 1..52
loop
while continue_next = 'FALSE'
loop
if num_short_count <= 0
then
sleep(1);
select count(1) into num_running from all_scheduler_jobs where job_Name like 'REBUILD_UNUSUABLE_INDEXES%' and state='RUNNING';
num_short_count:=num_simul-num_running;
-- dbms_output.put_line(to_char(num_running));
end if;
if num_running <= num_simul
then
if trim(index_array(lcounter).owner) is not null
then
-- dbms_output.put_line(index_array(lcounter).owner||'.'||index_array(lcounter).index_name);
dbms_scheduler.create_job(
job_name=>'REBUILD_UNUSUABLE_INDEXES'||to_char(lcounter),
program_name=>'REBUILD_INDEX_PROGRAM',
comments=>'Unusable index asynch job',
start_date=>sysdate+1000,
enabled=>false,
auto_drop=>false);
dbms_scheduler.set_job_argument_value(
job_name=>'REBUILD_UNUSUABLE_INDEXES'||to_char(lcounter),
argument_position=>1,
argument_value=>index_array(lcounter).owner
);
dbms_scheduler.set_job_argument_value(
job_name=>'REBUILD_UNUSUABLE_INDEXES'||to_char(lcounter),
argument_position=>2,
argument_value=>index_array(lcounter).index_name
);
dbms_scheduler.set_job_argument_value(
job_name=>'REBUILD_UNUSUABLE_INDEXES'||to_char(lcounter),
argument_position=>3,
argument_value=>par_degree
);
dbms_scheduler.enable('REBUILD_UNUSUABLE_INDEXES'||to_char(lcounter));
dbms_scheduler.run_job(job_name=>'REBUILD_UNUSUABLE_INDEXES'||to_char(lcounter), use_current_session=>false);
end if;
num_short_count:=num_short_count-1;
continue_next:='TRUE';
else
sleep(5);
continue_next:='FALSE';
end if;
end loop;
continue_next:='FALSE';
end loop;
while exit_now='FALSE'
loop
select count(1) into num_not_ran from all_scheduler_jobs where job_Name like 'REBUILD_UNUSUABLE_INDEXES%' and run_count=0;
if num_not_ran >0
then
sleep(5);
else
exit_now:='TRUE';
select count(1) into num_ran from all_scheduler_jobs where job_Name like 'REBUILD_UNUSUABLE_INDEXES%' and run_count>0;
dbms_output.put_line('Total Indexes rebuilt: '||to_char(num_ran));
end if;
end loop;
end;
/

Note that in this example, we cannot create a job to run immediately because we need to set the job arguments.  Because of this, the procedure DBMS_SCHEDULER.RUN_JOB is used to execute the job.  It may also be possible to use the DBMS_SCHEDULER.ENABLE procedure to execute them.

At the start of the driving procedure, the procedure below is called to remove any old index rebuild jobs that may exist:

CREATE OR REPLACE
procedure maint_drop_rebuild_jobs  authid current_user
is
begin
for drop_job_cursor in
(select job_name from all_scheduler_jobs where job_name like 'REBUILD_UNUSUABLE_INDEXES%')
loop
begin
dbms_scheduler.drop_job(job_name=>drop_job_cursor.job_name,force=>true);
exception
when others then null;
end;
end loop;
begin
dbms_scheduler.drop_program('REBUILD_INDEX_PROGRAM');
exception
when others then null;
end;
end;
/

Finally, as you review the initial script you will see the stored procedure that is called to rebuild the index, It is a very simple stored procedure:

CREATE OR REPLACE
procedure maint_run_rebuild(owner in varchar2, index_name in varchar2, par_degree in integer default 4) authid current_user is
sql_command varchar2(4000);
begin
sql_command:='alter index '||owner||'.'||index_name||' rebuild online parallel '||to_char(par_degree);
--  dbms_output.put_line(sql_command);
execute immediate sql_command;
end;
/

I definitely recommend keeping the number of indices rebuilt at any one time low until you have determined what kind of load your system can handle.  Needless to say they are highly IO intensive.  An index rebuild will generate a lot of redo. The procedures in this blog do not use the nologging option as we have a physical standby database configured. When a database has a physical standby, the force logging option is set so any nologging options are ignored.

If you have many smaller indices to rebuild, set parallel to 1 or 2, and rebuild more indices at the same time.  If you have fewer, larger, indices rebuild fewer indices simultaneously at a higher degree of parallelism.  If you have many large indices to rebuild, well, just figure out what works best.