Showing posts from 2018

Creating SNMP Plug-In for Enterprise Manager Cloud Control

Putting it In my previous blog (Enterprise Manager Cloud Control, SNMP, and Plug-Ins –
The Background), I shared my frustration in trying to create
a plug-in in OEM. Here are the technical steps for a very simple plug-in.
Preparing Plug-in
To build your own plug-in, you need to download a development kit:
To download EDK using UI, from the Setup menu, selectExtensibility, and then select Development Kit.OR
To download EDK using EMCLI, run the following steps:Log in to OEM:$ /oracle/em/middleware/bin/emcli login -username=sysmanEnter password :

Login successful Download the EDK zip archive:$ emcli get_ext_dev_kitDownloading edk.zipFile saved as edk.zipFile(s) downloaded successfully Unpack the downloaded EDK ZIP archive to a directory on your local
system. For example:
$ unzip There are several restrictions and guidelines you should know before you create your plug-in.
Most of them can be found at the following link: About Plug-in Metadata
Restrictions …

Enterprise Manager Cloud Control, SNMP, and Plug-Ins – The Background

I have been using Oracle Enterprise Manager (OEM or Cloud Control) since it was a client application. (I’m not sure whether it was in the era of Oracle 7 or Oracle 8). In the early stages, OEM was compared to other central monitoring systems (at the time, it was HP OpenView, CA Unicenter, and the like). As a DBA who was responsible for dozens of Oracle databases, I needed a main system like this. Since we had the OEM for "free" as part of a site license, we used it heavily. The problem was that the main IT monitoring tool in our company was another system that already monitored appliances, networks, and applications. So, we decided to keep the OEM to the DBAs and send notifications to this system. (It was monitored 24/7 by a control center.) Like today, we could send alerts by email, run an external script, or send an SNMP trap. We chose the SNMP trap since it is in the core of most monitoring systems and simple to define.

OEM needed a lot of attention and maintenance, …

Setup additional local DNS - to add scan addresses

In this document I demonstrate how to add local DNS server to be added additionally to an existing DNS, I had this need for creating some additional IP addresses in the DNS for Real Application Cluster (VIP and SCAN)
My Testing EnvironmentMy Private DNS ServerIP Address:
Host Name: localdns
OS: Oracle Linux Server release 7.5
Client Machine to use DNSIP Address:
Host Name: dbhost
OS: Oracle Linux Server release 7.4
Current Domain DNSPrimary:
Secondary: 1
DNS entry to addIP Address:
Host Name: dbhost-scan

Verify that the above IP address is not being used via ping command
$ ping -c1 -w 1 >/dev/null 2>&1 ; if [ $? -eq 1 ]; then echo "This IP is Not used"; else echo "This IP is used"; fi
This IP is Not usedInstall required packages$ sudo yum install bind* -yDefine zone files in master configuration ‘named.conf‘ file$ sudo vim /etc/named.confChange 127.0.01 to any and at the end zone section…

Oracle open world 2017 short summary

Hi, The main subject in this conference was Autonomous Database, it's engine is used by another buzzword called: Machine Learning. 
Oracle claim they are investing in autonomous to prevent data theft, but it seems as convenience for handling only one version in the cloud.
They will use the same "Machine Learning" engine that they used for the optimizer and memory management decisions (This engine is well known for bad decisions). 18c is an alias for and planned to be available: Autonomous Database for Data Warehouses - by December 2017Autonomous Database for OLTP – by June 2018 A companion solution for the "Machine Learning" will be used via the OEM (Cloud Control) to gather: Infrastructure logs: Network, Server, Storage, VM, OSPlatform logs: Database, Java, Analytics, etc.Application logs: ERP, CX, HCM, Custom, etc. The logs will be gathered to detect and connect anomalous events. SLA Guarantees 99.995% reliability and availability (less than 30 minute…

Oracle Linux ASM docker recipe

General information In this setup we are:
Installing dockerCreating Non-root user (ynixon) with sudo and docker privilegesASM device: /dev/sdb1Enabling sqlnet + ssh to the containerDefault ASM port is 1521ssh port 2222Passwords for root + grid os users in the container are “ynixon”Password for sys ASM user is “ynixon”Grid software is 12.2 without any patchesContainer Operating system is Oracle Linux 7.5Within the container, there is no use of UDEV / ASMLIB or ASMFD – the asm_diskstring='/dev/asm*' ,'/dev/*'All test done on regular Ubuntu 14.04There is a crontab job to keep 15 days of trace files + remove audit files. Prepare host for ASM device Make sure the device has permissions of the same container ids by applying UDEV rules $ vi /etc/udev/rules.d/100-asm.rules
KERNEL=="sdb1", NAME="ASM_DISK", OWNER="54421", GROUP="54421", MODE="0660"

udevadm trigger --sysname-match=sdb1 --verbose Verify the device has ASM lables DI…

Bypassing the Listener and Connecting the Instance without a Password from Java Code

In the past I wrote a post about Bypassing the listener and connecting to ASM without a password from Python code

Now, I've been asked to prove that it can be done in Java. The solution is almost the same.

Java sample code using OCI:

$ vi
import java.sql.*;
class OracleCon {
    public static void main(String args[]) {
        try {
            Connection con = DriverManager.getConnection(
                "jdbc:oracle:oci:@(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL=BEQ) (PROGRAM=oracle) (ARGV0=oracle+ASM) (ARGS='(DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))') (ENVS = 'ORACLE_HOME=/oracle/product/,ORACLE_SID=+ASM'))) (CONNECT_DATA= (SID=+ASM)))", "sys as sysdba", "change on install");
            Statement stmt = con.createStatement();
            ResultSet rs = stmt.executeQuery("select * from v$instance");

Automatic restart in Grid Infrastructure for Far Sync

Checking if the Far Sync instance is configured to restart automatically after reboot / shutdown of the host / cluster:
$ crsctl stat res ora.fdb1.db -f |grep AUTO_START
AUTO_START=restore "restore" means that after restart, the database will remain at the previous state it was before.
Changing the cluster to startup the database in any case.
$ crsctl stat res ora.fdb1.db -f |grep AUTO_START
AUTO_START=always Changing database to start always after reboot
$ crsctl modify resource ora.fdb1.db -attr  AUTO_START=always -unsupported Modify the startup state:
$ srvctl modify database -o mount View modified configuration
$ srvctl config database -d fdb1 -all
Database unique name: fdb1
Database name: fdb1
Oracle home: /oracle/product/
Oracle user: oracle
Password file:
Start options: mount
Stop options: immediate
Database role: FAR_SYNC
Management policy: AUTOMATIC
Disk Groups: DATA
Database is enabled
OSDBA group:
OSOPER group:

Query Historical messages of RMAN

Hi, Recently I realized that this very small and useful feature of RMAN is not known to many of us.
I have just learned about this option just two years ago.
I am talking about the ability to query from database (in-memory view) 32768 lines of RMAN output, even if it was used by crontab or any 3rd-party vendor.
So it is a sample sql command from v$rman_output, in the following example I filter just the last 7 days.
SQL>  col OUTPUT for a135 trunc
SQL> set pages 0
SQL> select output from gv$rman_output where session_recid in (select session_recid from v$rman_status where start_time > sysdate-7) order by recid ;
connected to target database: PDB11 (DBID=3890652951, not open)

crosscheck archivelog all;
delete noprompt archivelog like '+DATA%';
delete noprompt archivelog like '+RECO%';
delete noprompt archivelog like '/DB%';
delete noprompt obsolete;
crosscheck copy;
delete noprompt expired copy;

using target database control file instead of reco…

My impressions from Oracle OpenWorld 2018

Hi, This was my third time at Oracle OpenWorld, and still I was thrilled like the first time. Howard Street was dressed in big, red banners everywhere—just take a picture and share it in a social network.

This time I was also there as part of the Oracle ACE program, a community of Oracle experts and advocates. We all met on several occasions during the conference, giving me the opportunity to meet in person with the people I follow and whose sessions I attend.  

This year’s main keynote by Larry Ellison was about Oracle Generation 2 Cloud, which was built from the ground up for better performance, pricing, and security. I assume that Oracle’s architecture is indeed better than the competitors, as Larry kept saying, because Oracle hired the best minds in the industry - people with deep experience in cloud technology - to build Generation 2. I just hope that Oracle’s implementation will be as good as its design. Oracle announced that version 18c, and later 19c, will be fully independen…

Orphaned Files in ASM

In our lab environments we test Data Guard on a daily basis, and we frequently “play” with failover, switchover, and flashback . The output of this playground is that we have some leftovers in the ASM; we call these leftovers orphan files.
To solve this, I created SQL to query ASM views against database views.
This query should run on the database (not ASM).


   cmd   CLOB;
   FOR c IN (SELECT name Diskgroup
               FROM V$ASM_DISKGROUP)
      FOR l
         IN (SELECT 'rm ' || files files
                    (SELECT '+' || c.Diskgroup || files files, TYPE
                       FROM (    SELECT UPPER
                                           SYS_CONNECT_BY_PATH (, '/')

Data Guard Broker 19c

In an earlier version of Data Guard, when the broker had problems, one of the automatic answers I got from support was to recreate the broker configuration.
In other words:
Drop the configuration. Create the configuration. If your broker is simple, this is not a huge request. Actually, you could follow Step By Step How to Recreate Data Guard Broker Configuration (Doc ID 808783.1).
But in my case, using Active Data Guard with Far Sync, with many fine-tuned configurations, it was not so convenient. I had a number of ways I tried to be efficient:
I could write down all broker commands - to be able to run them again next time.For versions and, I found a way to query the broker and re-create a current configuration creation script (Extract Data Guard Commands).I also had some issues trying to Drop/remove Far-Sync Configurations from broker 12.1.On version, I found out that the broker metadata was changed, so I had to update my previous script for Extract Data …


SLOB is an Oracle I/O workload generation tool kit, supports testing extreme REDO logging I/O (minimal amount of CPU overhead)
The SLOB package can be downloaded from Kevin Closson's Blog: SLOB Resources
SLOB 2.4.2 requires Linux with Oracle Client
InstallationUnzip the SLOB tar.gz file in the desired directory ($SLOB).
Create tablespace for SLOB data (the script creates IOPS)
$ cd $SLOB
$ sqlplus / as sysdba @ misc/tsLoad SLOB data by running script located in the $SLOB directory. Using two mandatory parameters:
Tablespace into which SLOB will create and load the test schemasThe number of schemas to create and load$ ./ IOPS 8configurationOne time compilation
$ cd $SLOB/wait_kit
$ makeEdit runtime parameter in slob.conf configuration file
$ cd $SLOB
$ vi slob.confExecute slob using number of SLOB schemas
$ cd $SLOB
$ ./ 8We can edit the slob.conf file to modify some parameters.
UPDATE_PCT - Percentage of SLOB update operationsSCAN_PCT - percentage of short scan S…

Swingbench Short cookbook commands

In this example we are using Swingbench 2.6 with Order Entry stress test - using a configuration file (SOE_Server_Side_V2.xml)

Connection String:
The connection will always be defined to pluggable database (not CDB) - if installed. //hostname/service_name valid for driver type: Oracle oci Driver & Oracle jdbc Driver (oewizard,sbutil use only this option) .
Make sure the service name is recognized by the listener (lsnrctl services | grep Service) Tnsnames alias valid only for driver type: Oracle oci Driver – requires Oracle 12.2 client Creating the schema:
cd swingbench/bin
$ ./oewizard -dbap change_on_install -ts SOE -nopart -u soe -p soe -cl -df +DATA -create -scale 1 -cs //db_server/db_serviceRunning:
If you need to use Connection Pooling:
Change the driver type to "Oracle oci Driver"Change the format of the connection string to tns aliasFor further needed changes, look at: Application Continuity in Oracle Database 12c ( - character mode benchmark