Database Technologies...!

Was doing a POC on Galera cluster on MariaDB 10.1 and below are some of the points where we had to troubleshoot when setting up based on below Mariadb document -- https://mariadb.com/resources/blog/setting-mariadb-enterprise-cluster-part-1-how-set-virtual-machine-template Above document is based on MariaDB 5.5 hence the below changes were required. 1. From new version " wsrep_on=ON " parameter must be set. In all cluster nodes /etc/my.cnf.d/server.cnf should be edited to set   wsrep_on=ON. Other wise  wsrep_ready parameter will be in off status and cluster will not be functional. 2. When bootstraping the cluster first node must be started with "galera_new_cluster" command.  mysqld --wsrep-new-cluster is not supported in new version. 3. Both SElinux and firewalld needs to configured (Stopped) to allow required communication between cluster nodes. If there any " [ERROR] WSREP: wsrep::connect(gcomm:" errors recorded in the logs that can be...

SSD vs Flash based SANs – I/O comparison During a storage migration project from SSD based SAN   and Flash based SAN,   we have performed following tests to compare the I/O performance. On each of the tests it was observed flash was outperforming the SSD SAN on various magnitudes. This is not a comprehensive load test, but this acted more as a POC to go ahead with the flash implementation. 1.       File Creation using “ dd” command 10 G File Creation root@prt51fin2 # date;time dd if=/dev/zero of=/xiv-test1/xivtest count=10240 bs=1048576;date Wed Jan 24 17:20:32 IST 2018 10240+0 records in 10240+0 records out real 1m16.735s user 0m0.043s sys 0m8.783s Wed Jan 24 17:21:49 IST 2018 root@prt51fin2 # ---------------------------------------------------------------------------------- root@prt51fin2 # date;time dd if=/dev/zero of=/flash-test/flashtest count=10240 bs=1048576;date Wed Jan 24 17:23:35 IST 2018 10240+0 records i...

This is a result of silly mistake where legacy exp syntax is used with expdp. The issue here is when expdp with executed with exp parameters it runs with expdp conversion where applicable or ignore the legacy syntax. In this particular scenario expdp was called with  i ndexes=n compress=y parameter for table export. Now the expdp has converted indexes=n to  exclude=index but has ignored compress=y , most probably since expdp compression has three options (none, meta_data, all). This is recorded in expdp log as below. Legacy Mode Active due to the following parameters: Legacy Mode Parameter: "compress=TRUE" Location: Command Line, ignored. Legacy Mode Parameter: "indexes=FALSE" Location: Command Line, Replaced with: "exclude=index" Legacy Mode has set reuse_dumpfiles=true parameter. Starting "DBA00000"."SYS_EXPORT_TABLE_01": Although this situation is due to a syntax error, it is not clear at a first glance since w...

Although it not very common to manually change the AWS RDS availability zone we have come across two situation where it was required. 1. Application was using RDS IP, during a AWS maintenance windows RDS was moved to secondary availability zone. Quick code change was not an option so we had to manually switch to original availability zone 2. After a reboot of the RDS instance there were some I/O performance degradation was monitored. We thought it might be due to host system change on AWS and decided to do a manual switch to passive availability zone. There we haven't seen I/O issue. It is straight forward to do a manual availability zone switch using the console. Go to, Instance actions -- Reboot -- Make sure to tick the "Reboot With Failover"

Apparently alter system kill session is failing with ORA-01031: insufficient privileges. In Oracle RDS even if you are using privileged account ( rdsadmin ) you still cannot use the default syntax. Instead we need to execute  rdsadmin.rdsadmin_util.kill procedure. Example below. SQL> alter system kill session '1261,5589' immediate; alter system kill session '1261,5589' immediate * ERROR at line 1: ORA-01031: insufficient privileges SQL> ------------------------------- SQL> begin 2 rdsadmin.rdsadmin_util.kill( 3 sid => 1261, 4 serial => 5589, 5 method => 'IMMEDIATE'); 6 end; 7 / PL/SQL procedure successfully completed. SQL>

DB Version - Cloud control 12c Release 5 Setting up alerts is a must for any monitoring tool and when it comes to Oracle cloud control email is the only straight forward configuration. There is a possibility to configure sms by using mail to sms service but for most shops this is bit unpractical. Mainly due to security reasons to use a public service (e.g Google) to convert email to sms. But most shops does have their own sms gateways where they get data from a database or files and sent it via sms. In our case we did have a sms gateway so the challenge was to forward sms alerts to sms gateway source database tables. Below is the basic configuration setup. All the alerts generated by cloud control are stored in the cloud control database. What we need to do is transfer those alert data from cloud control db to sms gateway db. Some digging in the cloud control documentation and googling helped us to come up with below steps to accomplish this. In summary there will be...

We can use dba_source and  dbms_metadata.get_ddl when extracting code from db objects. Something that wasn't clear was code for scheduler jobs. Below is the syntax for extracting scheduler job code using dbms_metadata.get_ddl. select dbms_metadata.get_ddl('PROCOBJ','-SCH_JOB_NAME-','-OWNER-') from dual; Not sure where they came up with PROCOBJ. :D

DB Version - Cloud control 12c Release 5 Ok, this seems straight forward enough, but for me it took some digging to find the proper method. These report are already built in to cloud control 12c. report names are as follows and they provide all the required information on tablespace growth. Report Path -- Enterprise > Information Publisher Reports > Oracle Database Space Usage Under this section you can find both Oracle Database Tablespace Daily Space Usage and Oracle Database Tablespace Monthly Space Usage reports.

Ever come across a situation where you need to perform data pump operation on object owned by SYS?. This is not allowed in data pump and your operation should fail with ORA-39166.The old exp/imp was able to perform operation on SYS owned objects. Ideally you should not be in an situation to data pump on SYS. but in our case it was a data migration from Oracle 8i to 11g, and there was an intermediate state where some data modification to be performed. So when importing 8i data using old imp it was by mistake moved to SYS. And these were some huge tables with some data modification already done. We had the option of using old imp/exp but was preferring parallel data pump due to the performance. So what are options to quickly change ownership SYS owned table without recreating the tables. Enter - Partition exchange for rescue. This method is a quick workaround to change ownership of Oracle table without time consuming recreates. Below is an example where we change table owner ship ...