Skip navigation.

DBA Blogs

Renaming #EM12c / #EM13c Targets

DBASolved - Tue, 2016-03-08 09:20

Oracle Enterprise Manager is a complex piece of software that many organizations are running now. Some organizations set out with a formalized naming standard; some do not. Those who do not ofter end up identifying a naming standard later down the road and then making requests to change the names of the targets being monitored. In order to do this, there are two ways:

1. Delete and rediscover the target and rename at time of discovery
2. Change the name from the backend using EMCLI

The first way is painful to say the least, especially when you have thousands upon thousands of targets. So this post is going to focus on how to change the name from the backend using EMCLI and a few other little tips.

EMCLI is a nice tool to use. It provides two options for renaming of targets. The first option is rename_target and the second is modify_target. The rename_target option is used to rename the target on the repository side, while the modify_target option is used to rename at the agent level. Both options are required when renaming a target because the target needs to stay in-sync to retain the history of the target.

To make this process a bit more automated, I’ve created a perl script that will do the renaming for me based on information in a space delimited flat file. The script is as follows:

#!/usr/bin/perl -w
use strict;
use warnings;

##########################
#Notes
##########################
#
#To help with renaming the entity_name in the repository database,
#comment out block of code in SYSMAN.EM_TARGET from line 8028 thru 8035
#
##########################
#GLOBAL Variables
##########################
my $oem_home_bin = "";
my $time_now = localtime();
my ($variable, $sysman_pwd) = @ARGV;
my $count = 0;
my @columns;

##########################
#Main Program
##########################

open (FILE, "< $variable") or die "$!\n";
@columns = ("", 0, 0, 0);
print "\nStart time: ".$time_now."\n";
emcli_login();
while()
{
	my $line = $_;
	@columns = split(' ',$line, 4);
	rename_target(@columns);
	$count = $count+1;
} #end file read
close (FILE) or die "$!\n";
my $end_time=localtime();
print "\nNumber of changes: ".$count;
print "\nEnd time: ".$end_time."\n";
emcli_logout();

##########################
#Sub-Programs
##########################
sub emcli_login{
	print "\n";
	system($oem_home_bin.'/emcli login -username=sysman -password='.$sysman_pwd);
	system($oem_home_bin.'/emcli sync');
	print "\n";
}

sub emcli_logout{
	print "\n";
	system($oem_home_bin.'/emcli logout');
	print "\n";
}

sub rename_target{
	#Parameters
	my ($target_name, $target_type, $server_name )=@columns;
	my $mod_target;
	my $new_name;
	my $cmd;
	my $cmd1;

	if ($target_type =~ /rac_database/)
	{
		chomp($target_name);
		chomp($server_name);
		$mod_target = $target_name;
		$target_name = substr($target_name, 0, -4);
		$new_name = $target_name."_".$server_name;
		#print $new_name;
		print "\n";
		$cmd = 'emcli modify_target -name="'.$mod_target.'" -type="'.$target_type.'" -display_name="'.$new_name.'" -on_agent';
		print $cmd."\n";
		#print "\n!!!!Executing on agent side!!!!\n";
		#system($oem_home_bin.'/'.$cmd);
		$cmd1 = 'emcli rename_target -target_type="'.$target_type.'" -target_name="'.$mod_target.'" -new_target_name="'.$new_name.'"';
		print $cmd1."\n";
		#print "\n!!!!Executing on repository side!!!!\n";
		#system($oem_home_bin.'/'.$cmd);
	}
}

Notice that I’m doing the renaming at the agent side along with the repository side. Although this looks pretty simple and straight forward, I’ve found that the EMCLI command to rename (rename_target) is actually driven by the package EM_TARGET in the SYSMAN schema. There is a small set of code in this package that will prevent renaming of certain target types if they are currently being monitored and managed by OEM.

To identify what targets are managed, the following SQL can be used:

SELECT ENTITY_TYPE, ENTITY_NAME, DISPLAY_NAME FROM EM_MANAGEABLE_ENTITIES 
WHERE ENTITY_TYPE='oracle_database' and promote_status=3 and manage_status=‘2';

The SQL above will provide you with the target type (entity_type), name (entity_name), and display name (display_name). These three columns are important because they directly correlate to what you will see in OEM. About 90% of the screen in OEM use the display_name column. The other 10% of the screens use the entity_name. When you start renaming, you will want these names to match, just keep in mind they may not over the long haul.

Now, back to the code in the EM_TARGET package. When renaming targets, some target will report back that the target cannot be changed. This is due to the target already being managed by OEM. In order to by-pass this, you need to update the EM_TARGET package body and comment out a small set of code (make sure you back up the package before doing anything). The lines of code that need to be commented out are between 8028 and 8035.

-- we will implement rename of agent side targets when it is fully
     -- supported by agent
    --IF ( l_trec.manage_status = MANAGE_STATUS_MANAGED AND
    --     l_trec.emd_url IS NOT NULL) 
    --THEN
    --  raise_application_error(MGMT_GLOBAL.INVALID_PARAMS_ERR,
    --      MGMT_GLOBAL.INVALID_PARAMS_ERR||' Not allowed') ;
    --END IF ;

After commenting out these lines of code, recompile the package. Then you will be able to rename repository targets using EMCLI even though they are already managed targets. This will effect the entity_name column and allow you to update the other 10% of pages that are not immediately changed.
 
Another way to change names of targets once the EM_TARGET package has been updated, is to use SQL to make the changes.

exec sysman.em_target.rename_target(target_type, current_name, new_name, new_name);
commit;

Once the commit has happened, then the OEM pages can be refreshed and the new entity_name will be displayed.

Well, I hope this has provided you some explanation on how to change existing targets within the EM framework.

Enjoy!

about.me: http://about.me/dbasolved


Filed under: EMCLI, OEM
Categories: DBA Blogs

Renaming #EM12c / #EM13c Targets

DBASolved - Tue, 2016-03-08 09:20

Oracle Enterprise Manager is a complex piece of software that many organizations are running now. Some organizations set out with a formalized naming standard; some do not. Those who do not ofter end up identifying a naming standard later down the road and then making requests to change the names of the targets being monitored. In order to do this, there are two ways:

1. Delete and rediscover the target and rename at time of discovery
2. Change the name from the backend using EMCLI

The first way is painful to say the least, especially when you have thousands upon thousands of targets. So this post is going to focus on how to change the name from the backend using EMCLI and a few other little tips.

EMCLI is a nice tool to use. It provides two options for renaming of targets. The first option is rename_target and the second is modify_target. The rename_target option is used to rename the target on the repository side, while the modify_target option is used to rename at the agent level. Both options are required when renaming a target because the target needs to stay in-sync to retain the history of the target.

To make this process a bit more automated, I’ve created a perl script that will do the renaming for me based on information in a space delimited flat file. The script is as follows:

#!/usr/bin/perl -w
use strict;
use warnings;

##########################
#Notes
##########################
#
#To help with renaming the entity_name in the repository database,
#comment out block of code in SYSMAN.EM_TARGET from line 8028 thru 8035
#
##########################
#GLOBAL Variables
##########################
my $oem_home_bin = "";
my $time_now = localtime();
my ($variable, $sysman_pwd) = @ARGV;
my $count = 0;
my @columns;

##########################
#Main Program
##########################

open (FILE, "< $variable") or die "$!\n";
@columns = ("", 0, 0, 0);
print "\nStart time: ".$time_now."\n";
emcli_login();
while()
{
	my $line = $_;
	@columns = split(' ',$line, 4);
	rename_target(@columns);
	$count = $count+1;
} #end file read
close (FILE) or die "$!\n";
my $end_time=localtime();
print "\nNumber of changes: ".$count;
print "\nEnd time: ".$end_time."\n";
emcli_logout();

##########################
#Sub-Programs
##########################
sub emcli_login{
	print "\n";
	system($oem_home_bin.'/emcli login -username=sysman -password='.$sysman_pwd);
	system($oem_home_bin.'/emcli sync');
	print "\n";
}

sub emcli_logout{
	print "\n";
	system($oem_home_bin.'/emcli logout');
	print "\n";
}

sub rename_target{
	#Parameters
	my ($target_name, $target_type, $server_name )=@columns;
	my $mod_target;
	my $new_name;
	my $cmd;
	my $cmd1;

	if ($target_type =~ /rac_database/)
	{
		chomp($target_name);
		chomp($server_name);
		$mod_target = $target_name;
		$target_name = substr($target_name, 0, -4);
		$new_name = $target_name."_".$server_name;
		#print $new_name;
		print "\n";
		$cmd = 'emcli modify_target -name="'.$mod_target.'" -type="'.$target_type.'" -display_name="'.$new_name.'" -on_agent';
		print $cmd."\n";
		#print "\n!!!!Executing on agent side!!!!\n";
		#system($oem_home_bin.'/'.$cmd);
		$cmd1 = 'emcli rename_target -target_type="'.$target_type.'" -target_name="'.$mod_target.'" -new_target_name="'.$new_name.'"';
		print $cmd1."\n";
		#print "\n!!!!Executing on repository side!!!!\n";
		#system($oem_home_bin.'/'.$cmd);
	}
}

Notice that I’m doing the renaming at the agent side along with the repository side. Although this looks pretty simple and straight forward, I’ve found that the EMCLI command to rename (rename_target) is actually driven by the package EM_TARGET in the SYSMAN schema. There is a small set of code in this package that will prevent renaming of certain target types if they are currently being monitored and managed by OEM.

To identify what targets are managed, the following SQL can be used:

SELECT ENTITY_TYPE, ENTITY_NAME, DISPLAY_NAME FROM EM_MANAGEABLE_ENTITIES 
WHERE ENTITY_TYPE='oracle_database' and promote_status=3 and manage_status=‘2';

The SQL above will provide you with the target type (entity_type), name (entity_name), and display name (display_name). These three columns are important because they directly correlate to what you will see in OEM. About 90% of the screen in OEM use the display_name column. The other 10% of the screens use the entity_name. When you start renaming, you will want these names to match, just keep in mind they may not over the long haul.

Now, back to the code in the EM_TARGET package. When renaming targets, some target will report back that the target cannot be changed. This is due to the target already being managed by OEM. In order to by-pass this, you need to update the EM_TARGET package body and comment out a small set of code (make sure you back up the package before doing anything). The lines of code that need to be commented out are between 8028 and 8035.

-- we will implement rename of agent side targets when it is fully
     -- supported by agent
    --IF ( l_trec.manage_status = MANAGE_STATUS_MANAGED AND
    --     l_trec.emd_url IS NOT NULL) 
    --THEN
    --  raise_application_error(MGMT_GLOBAL.INVALID_PARAMS_ERR,
    --      MGMT_GLOBAL.INVALID_PARAMS_ERR||' Not allowed') ;
    --END IF ;

After commenting out these lines of code, recompile the package. Then you will be able to rename repository targets using EMCLI even though they are already managed targets. This will effect the entity_name column and allow you to update the other 10% of pages that are not immediately changed.
 
Another way to change names of targets once the EM_TARGET package has been updated, is to use SQL to make the changes.

exec sysman.em_target.rename_target(target_type, current_name, new_name, new_name);
commit;

Once the commit has happened, then the OEM pages can be refreshed and the new entity_name will be displayed.

Well, I hope this has provided you some explanation on how to change existing targets within the EM framework.

Enjoy!

about.me: http://about.me/dbasolved


Filed under: EMCLI, OEM
Categories: DBA Blogs

Sources of Inspiration on International Women’s Day

Pythian Group - Tue, 2016-03-08 08:58

True inspiration comes in many forms and I consider myself fortunate to be inspired every day by the women around me. International Women’s Day is the perfect opportunity to reflect on the women in our lives who positively influence us.

This post is my heartfelt thank you to the women in my ‘circle’ who have made an indelible mark in my life. They are women who continue to inspire, challenge and motivate me.

The women on Pythian HR’s team: These women continually teach me valuable life lessons. They are mothers, partners, sisters, care providers, aunts, cousins, and friends to many. They are strong, spirited, supportive and have generous natures that are contagious. They demonstrate an unwavering commitment to working hard, they’re incredibly talented and they have a steady focus on doing what’s best for our employees. These women go above and beyond and approach every puzzle with optimism.

My mother:  My mother is the most positive and ‘glass half full’ person that I know. She is a person who never fails to find the bright side to life’s most thought-provoking issues and one of her favourite questions to ask her loved ones is “Are You Happy?” (Spoiler alert: she’s not satisfied unless the answer is a truthful “yes”). Her love, guidance and support have helped sustain me through so much and over the years she has evolved into my BFF.

My friend, Jen:  Jen is a breast cancer survivor who decided to fight back and co-found Vixens Victorious. In October 2015, the dynamic duo of Vixens Victorious successfully launched Lights! Camera! CURE! which showcases female film makers from Canada and the proceeds go to support the Ottawa Regional Cancer Society. Jen’s positive spirit and take charge attitude empowers everyone who meets her.

My friend, Kate:  Kate moved to Canada with her three month old daughter to start a new journey with her husband. She took the initiative to make new friends, develop a network and often navigate a new city on her own when her partner travelled for work. Kate isn’t one to complain about adversities in life; she is courageous and gratefully embraces her adventure.

My fitness trainer JulesJules gets out of bed every morning, puts on her workout gear and travels across Ottawa to provide the most fun and effective workouts to her clients. She generously shares her own personal health journey and always finds a way to connect with her clients so they can experience the one on one attention they need. She is full of greatness.

Our family physician, Dr. Judy:  Dr. Judy’s medical practice is thriving because of her commitment to patient care. She ensures you are her priority in the moments that you are with her. She makes each of her patients feel important, cared for and heard. Dr. Judy emulates a kind and caring nature that everyone could benefit from.

My neighbor, Anne Marie:  In her late forties, Anne Marie taught herself to swim so she could begin competing in triathlons. She now travels internationally to compete in races. I’m inspired by her hard work, determination and strategic ability to set and meet goals.

The influences (sometimes subtle) of these women make an impact on how I choose to live my life. I am thankful for all of them.

On this International Women’s Day, I encourage you to think about who inspires you and why. Bonus points if you honour them with words of appreciation!

Categories: DBA Blogs

SQL Server on Linux – No April fools

Pythian Group - Mon, 2016-03-07 20:58

Years ago, I got burned by an “April Fools” joke published by Steve Jones on sqlservercentral.com. He republished it as one of his favorites here.

Naturally, I had to rub my eyes today when I read that Microsoft announced that SQL Server 2016 would be coming to Linux.

There were mixed reactions on the internal SQL Server teams. I was afraid to respond to the thread, fearing I would get burned again. I quickly checked the date to confirm that the article hadn’t been resurrected.

One of the sentiments expressed in our internal chatter was that some of the DBAs love “Satya’s Microsoft” and I agree. I like what they’re doing, but I am very skeptical about the move to port SQL Server onto Linux.

I doubt this will enable new markets and new customer bases. I don’t think there are any large organizations who will suddenly decide to adopt the product because it will run on Linux.

One theory was that this move was to attract new developers who want to deploy multi-platform tech stacks. That could certainly be right, but I think PaaS satisfies that and many of the startup natures.

Other articles I read theorized it was a move towards SQL Server on Linux-powered containers.

I’m wondering what this will mean for future features. Will PowerShell or .NET be ported to Linux? What will change in the security model? Will clustering be available? Will a more RAC-like feature be available?

These are very interesting times and while this wasn’t a move that I was pining for, or even expected, I am excited to see where this is going.

I “applied” to test one of the early versions, and you can too.

What do you think? Are you excited about running SQL Server on Linux? When would you choose Linux over Windows?

Categories: DBA Blogs

Pythian at the 2016 Percona Live Data Performance Conference

Pythian Group - Mon, 2016-03-07 10:22

The Percona Live Data Performance Conference in Santa Clara is being held April 18-22, 2016. It is quickly approaching, and Pythian is going to show you how we Love Your Data in a big way!

We have an awesome lineup of speakers this year:

Mark these down in your schedule because you are not going to want to miss any of them! Although, you might have a tough time choosing between the tutorials if you can’t clone yourself.

Also, please join us for the 2016 Annual Community Dinner @ Pedro’s. You can register now through EventBrite.

Categories: DBA Blogs

RSA Conference 2016

Pythian Group - Mon, 2016-03-07 08:41

RSAConference 2016 Where the world talks security 

40,000 attendees, 500+ vendors and 700 sessions

RSAC is my annual check in to learn new approaches to information security, discover new technology, learn from industry experts and build my community.

In the three years that I have been attending RSAC, I have learned that Pythian is unique and so are our clients. Each year, we continue to improve our security program with our clients in mind.

RSAC Day 1

It’s Day 1 of the RSAConference 2016. Monday’s are typically a quiet day with vendors setting up in the expo halls, conference staff getting organized, attendees registering and a few press/analysts looking for optimal interview spots. It has been the calm before the storm of attendees descend on San Francisco and RSAC.

This Monday was a whirlwind of activity; CSA Summit, DevOps Connect, Information Security Leadership Development and IAPP: Privacy and Security to name only a few. Chances are you may have missed sessions if you weren’t early enough.

Privacy and Security were hot topics given the European General Data Protection Regulation (GDPR) agreement reached December 2015.

For more information on the event visit the RSAC website, or follow the conference on Twitter.

 

 

 

 

Categories: DBA Blogs

COMPRESSION -- 2 : Compressed Table Partitions

Hemant K Chitale - Sun, 2016-03-06 09:38
A Partitioned Table can choose to have a mix of COMPRESS and NOCOMPRESS Partitions.

As in this case where the first 3 partitions are defined as COMPRESS and the last as NOCOMPRESS :  :

[oracle@localhost Hemant]$ sqlplus hemant/hemant@orcl

SQL*Plus: Release 12.1.0.2.0 Production on Sun Mar 6 23:20:19 2016

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

Last Successful login time: Sun Mar 06 2016 23:19:11 +08:00

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

SQL> @create_SALESHIST
SQL> spool create_SALESHIST
SQL>
SQL> drop table SALESHIST;

Table dropped.

SQL>
SQL> alter session set "_partition_large_extents"=FALSE;

Session altered.

SQL>
SQL> create table SALESHIST
2 (
3 PROD_ID NUMBER NOT NULL ,
4 CUST_ID NUMBER NOT NULL ,
5 TIME_ID DATE NOT NULL ,
6 CHANNEL_ID NUMBER NOT NULL ,
7 PROMO_ID NUMBER NOT NULL ,
8 QUANTITY_SOLD NUMBER(10,2) NOT NULL ,
9 AMOUNT_SOLD NUMBER(10,2) NOT NULL
10 )
11 NOCOMPRESS LOGGING
12 TABLESPACE USERS
13 PARTITION BY RANGE (TIME_ID)
14 (
15 PARTITION SALES_1998
16 VALUES LESS THAN (TO_DATE('1999-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
17 'NLS_CALENDAR=GREGORIAN'))
18 SEGMENT CREATION IMMEDIATE
19 COMPRESS BASIC NOLOGGING
20 TABLESPACE SALES_1998 ,
21 PARTITION SALES_1999
22 VALUES LESS THAN (TO_DATE('2000-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
23 'NLS_CALENDAR=GREGORIAN'))
24 SEGMENT CREATION IMMEDIATE
25 COMPRESS BASIC NOLOGGING
26 TABLESPACE SALES_1999 ,
27 PARTITION SALES_2000
28 VALUES LESS THAN (TO_DATE('2001-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
29 'NLS_CALENDAR=GREGORIAN'))
30 SEGMENT CREATION IMMEDIATE
31 COMPRESS BASIC NOLOGGING
32 TABLESPACE SALES_2000 ,
33 PARTITION SALES_2001
34 VALUES LESS THAN (TO_DATE('2002-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
35 'NLS_CALENDAR=GREGORIAN'))
36 SEGMENT CREATION IMMEDIATE
37 NOCOMPRESS NOLOGGING
38 TABLESPACE SALES_2001 )
39 /

Table created.

SQL>
SQL> spool off
SQL>
SQL> col partition_name format a30
SQL> select partition_name, compression, compress_for
2 from user_tab_partitions
3 where table_name = 'SALESHIST'
4 order by partition_position;

PARTITION_NAME COMPRESS COMPRESS_FOR
------------------------------ -------- ------------------------------
SALES_1998 ENABLED BASIC
SALES_1999 ENABLED BASIC
SALES_2000 ENABLED BASIC
SALES_2001 DISABLED

SQL>
SQL> select compression, compress_for
2 from user_tables
3 where table_name = 'SALESHIST';

COMPRESS COMPRESS_FOR
-------- ------------------------------


SQL>


The Compression attributes actually apply to the Segments so the Partition Segments have the definition but the Table, being segmentless, does not show the definition.

Note that I am still demonstrating BASIC Compression. So the compression is applied only on Direct Path INSERT.  As in :

SQL> insert /*+ APPEND */ into saleshist select * from sh.sales;

918843 rows created.

SQL> commit;

Commit complete.

SQL>
SQL> select partition_name, tablespace_name, bytes/1024
2 from user_segments
3 where segment_name = 'SALESHIST'
4 and segment_type = 'TABLE PARTITION'
5 order by 1;

PARTITION_NAME TABLESPACE_NAME BYTES/1024
------------------------------ ------------------------------ ----------
SALES_1998 SALES_1998 3072
SALES_1999 SALES_1999 4096
SALES_2000 SALES_2000 4096
SALES_2001 SALES_2001 11264

SQL>


This demonstrates that it is possible to
a) Have some Partitions defined as COMPRESSed and others as NOCOMPRESSed
b) Create different Partitions in different Tablespaces

Before the advent of (and still without using) the 12c ILM features, this was and is a method to manage historical data with compression and moving or placing data (i.e. the respective tablespace datafile(s)) in low-cost storage as desired.

Note : For the purposes of this demo, I used the parameter "_partition_large_extents"=FALSE.  Oracle's default Partition size since 11.2.0.2 has been 8MB for Table Partitions and that would have been excessively large for this demo.  If you are using Hybrid Columnar Compression and/or Exadata, Oracle advises against this.
.
.
.

Categories: DBA Blogs

Links for 2016-03-03 [del.icio.us]

Categories: DBA Blogs

2016 Annual Pythian MySQL Community Dinner

Pythian Group - Thu, 2016-03-03 10:26

Once again, Pythian is organizing an event that by now may be considered a tradition: The MySQL community dinner at Pedro’s! This dinner is open to all MySQL community members since many of you will be in town for Percona Live that week. Here are the details:

What: The MySQL Community Dinner

When: Tuesday April 19, 2016 –  7:00 PM at Pedro’s (You are welcome to show up later, too!)

Where: Pedro’s Restaurant and Cantina – 3935 Freedom Circle, Santa Clara, CA 95054

Cost: Tickets are $40 USD, Includes Mexican buffet, non-alcoholic drinks, taxes, and gratuities (see menu)

How: Purchase your ticket below or RSVP through Eventbrite

Powered by Eventbrite

Pythian Attendees:

Derek Downey
Alkin Tezuysal
Okan Buyukyilmaz
Emanuel Calvo
John Schulz
Martin Arrieta
Gabriel Cicilliani
Christos Soulios
Theresa Nova

Categories: DBA Blogs

MySQL on FreeBSD: old genes

Pythian Group - Thu, 2016-03-03 10:02

Maintaining mission critical databases on our pitchfork wielding brother, the “Daemon” of FreeBSD, seems quite daunting, or even absurd, from the perspective of a die-hard Linux expert, or from someone who has not touched it in a long time. The question we ask when we see FreeBSD these days is “why?”.  Most of my own experience with FreeBSD was obtained 10-15 years ago.  Back then, in the view of the team I was working on, a custom compiled-from-source operating system like FreeBSD 5.x or 6.x was superior to a Linux binary release.

Package managers like YUM and APT were not as good.  They did not always perform MD5 checks and use SSL like today’s versions. RedHat wasn’t releasing security updates 5 minutes after a vulnerability was discovered. Ubuntu didn’t exist. Debian stable would get so very old before receiving a new version upgrade. FreeBSD was a great choice for a maintainable, secure, free open source UNIX-like OS with tight source control and frequent updates.

Most people do not understand why FreeBSD remains a great choice for security and stability. The main reason is that the entire source of the base OS and the kernel (not just the kernel) are tightly maintained and tested as a whole, monolithic, distribution.

FreeBSD 10.2 is different than versions I worked on many years ago, in a good way, at least from the standpoint of getting started. First, “pkg” has gotten quite an overhaul, making installing packages on FreeBSD as easy as with YUM or APT.  portsnap and portmaster make port upgrades much easier than they used to be. freebsd-update can take care of wholesale updates of the operating system from trusted binary sources without having to “build the world”. These are welcome changes; ones that make it easier to get to production with FreeBSD, and certainly made the task of rapidly building and updating a couple of “lab” virtual machines easier.

In my effort to get re-acquainted with FreeBSD, I hit some snags. However, once I was finished with this exercise, FreeBSD had re-established itself in my mind as a decent flavor to host a mission critical database on. Open Source enthusiasts should consider embracing it without (much) hesitation. Is there some unfamiliar territory for those who only use MySQL on MacOS and Linux? Sure. But it is important to remember that BSD is one of the oldest UNIX like operating systems. The OSS world owes much heritage to it. It is quite stable and boring, perhaps even comfortable in its own way.

Problem 1: forcing older versions of MySQL

I needed to install MySQL 5.5 first, in order to test a mysql upgrade on FreeBSD.  However, when installing percona-toolkit either via “pkg install” (binary) or /usr/ports (source), the later 5.6 version of the mysql client would inevitably be installed as a dependency. After that point, anything relating to MySQL 5.5 would conflict with the 5.6 client. If I installed in the opposite order, server first, percona-toolkit second, the percona-toolkit installation would ask me if it is OK to go ahead and upgrade both server and client to 5.6.

TIP: don’t forget make.conf

my /etc/make.conf:
MYSQL_DEFAULT?= 5.5

Once I added MYSQL_DEFAULT into make.conf, the installations for MySQL 5.5 became seamless. Note: if you want another flavor of MySQL server such as Percona Server, install the server “pkg install percona55-server” prior to “pkg install percona-toolkit” so that the client dependencies are met prior to installation.

Problem 2: Some tools don’t work

pt-diskstats does not work, because it reads from /proc/diskstats, which does not exist on FreeBSD. Other favorites like htop don’t work right out of the box. So far I have had good luck with the rest of the Percona toolkit besides pt-diskstats, but here’s how you get around the htop issue (and perhaps others).

TIP: Get the linux /proc mounted

dynamic commands:
# kldload linux
# mkdir -p /compat/linux/proc
# mount -t linprocfs linproc /compat/linux/proc

to make permanent:
# vi /boot/loader.conf (and add the following line)
linux_load="YES"
# vi /etc/fstab (and add the following line)
linproc /compat/linux/proc linprocfs rw 0 0

As you may have determined, these commands ensure that the linux compatibility kernel module is loaded into the kernel, and that the linux style /proc is mounted in a different location than you might be used to “/compat/linux/proc”. The FreeBSD /proc may also be mounted.

Problem 3: I want bash

# pkg install bash
… and once that’s done
# pw user mod root -s /usr/local/bin/bash
…and repeat ^^ for each user you would like to switch. It even comes with a prompt that looks like CentOS/RHEL.
[root@js-bsd1 ~]#

Problem 4: I can’t find stuff

BSD init is much simpler than SysV and upstart init frameworks so your typical places to look for start files are /etc/rc.d and /usr/local/etc/rc.d. To make things start on boot, it’s inevitably a line in /etc/rc.conf.

In our case, for MySQL, our start file is /usr/local/etc/rc.d/mysql-server. To have MySQL start on boot, your rc.conf line is:
mysql_enable="YES".

If you do not wish to make MySQL start on boot, you may simply say "/usr/local/etc/rc.d/mysql-server onestart"

Notes on binary replacement

Please note, just like in the Linux world, MariaDB and Percona Server are drop in replacements for MySQL so, the startfiles and enable syntax does not change. Your default location for my.cnf is /etc/my.cnf just like in the rest of the known universe.

This command lists all installed packages.
pkg info -a

use pkg remove and pkg install to add new versions of your favorite mysql software.

I ran into no greater issues with pkg than I would with yum or apt doing binary removals and installations, and no issues at all with mysql_upgrade. Remember: If you had to alter make.conf like I did earlier, remember to update it to reflect versions you want to install.

For those who like ZFS, the FreeBSD handbook has a very detailed chapter on this topic. I for one like plain old UFS. It might be the oldest filesytem that supports snapshots and can be implemented very simplistically for those who like low overhead.

Happy tinkering with FreeBSD and MySQL, and thanks for reading!

Categories: DBA Blogs

Sydney Gets New AWS Availability Zone

Pythian Group - Thu, 2016-03-03 09:47

On a scorching November day in 2012, Sydneysiders were bracing themselves for yet another heat wave when all of a sudden they became pleasantly surprised as an elastic cloud occupied the tech skies. On November 12, 2012, Amazon announced  the New Asia Pacific (Sydney) Region in Australia.

Before that, Australian customers had to reach out to Japan or Singapore for their cloud needs. That was not really feasible, as it increased up-front expenses, long-term commitments, and scaling challenges. Amazon recognized that and Sydney became another region in the world.

They have now taken it a step further. They have rendered a new Availability Zone (AZ) in Sydney. Availability zone (AZ) is basically an isolated location within data centre regions from which public cloud services originate and operate.

The new availability zone is ap-southeast-2c. This is all set to provide enhanced performance and sociability to Australian customers. This will enable them to fully leverage the potential of technologies like Lambda, the Elastic File System shared filesystem, and Amazon RDS for MS SQL Server.

Pythian’s established presence in Australia and New Zealand coupled with round the clock and world class support for AWS, SQL Server, and other cloud technologies, enables it to support Australian and New Zealand customers from the word go.

Categories: DBA Blogs

Links for 2016-03-02 [del.icio.us]

Categories: DBA Blogs

SQL Injection with MySQL SLEEP()

Pythian Group - Wed, 2016-03-02 11:40

Recently we’ve received an alert from one of our clients that running threads are high on one of their servers. Once we logged in, we noticed that all the selects were waiting for table level read lock. We scrolled through the process list, and found the selects which were causing the problems. After killing it, everything went back to normal.
At first we couldn’t understand why the query took so long, as it looked like all the others. Then we noticed, that one of the WHERE clauses was strange. There, we found a SLEEP(3) attached with OR to the query. Obviously, this server was the victim of a SQL injection attack.

What is SQL injection?

I think most of us know what SQL injection is, but as a refresher, SQL injection is when someone provides malicious input into WHERE, to run their own statements as well.
Typically this occurs when you ask a user for input, like username, but instead of a real name they give you a MySQL statement that will be run by your server without you knowing it.
Exploits of a Mom
Based on the picture, let’s see a few examples.
We have a simple table:

mysql> describe post;
+-------+------------------+------+-----+---------+----------------+
| Field | Type             | Null | Key | Default | Extra          |
+-------+------------------+------+-----+---------+----------------+
| id    | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| test  | varchar(127)     | YES  |     | NULL    |                |
+-------+------------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

mysql> select * from post;
+----+--------+
| id | test   |
+----+--------+
|  1 | text1  |
|  2 | text2  |
|  3 | text3  |
|  4 | text4  |
|  5 | text5  |
|  6 | text6  |
|  7 | text7  |
|  8 | text8  |
|  9 | text9  |
| 10 | text10 |
+----+--------+
10 rows in set (0.00 sec)

Lets run a select with LIKE, which we know for sure won’t have a match:

mysql> select * from post where test like '%nomatch%';
Empty set (0.00 sec)

But what, happens if we don’t filter the inputs and someone wants to get all the data?
mysql> select * from post where test like '%nomatch ' || '1==1' && '1%';
+----+--------+
| id | test   |
+----+--------+
|  1 | text1  |
|  2 | text2  |
|  3 | text3  |
|  4 | text4  |
|  5 | text5  |
|  6 | text6  |
|  7 | text7  |
|  8 | text8  |
|  9 | text9  |
| 10 | text10 |
+----+--------+
10 rows in set, 2 warnings (0.00 sec)

That was a very mild injection, but it could be much more malicious: we could drop another table!

mysql> show tables;
+----------------------+
| Tables_in_injecttest |
+----------------------+
| game                 |
| post                 |
+----------------------+
2 rows in set (0.01 sec)

mysql> select * from post where test like '%nomatch'; drop table game;-- %';
Empty set (0.00 sec)

Query OK, 0 rows affected (0.28 sec)

mysql> show tables;
+-----------------------+
| Tables_in_inject_test |
+-----------------------+
| post                  |
+-----------------------+
1 row in set (0.00 sec)

mysql>

If we don’t know the name of the table, we can still cause trouble by blocking access to the database
If we insert SLEEP() in the WHERE part, then it will be executed for every matching row… if we inject it like: “OR SLEEP(n)”, it will be executed to every row in the table!
Okay, this will be “just” a long running select. It shouldn’t cause much trouble thanks to InnoDB and transaction isolation, unless something needs a table lock.

Some common examples of what causes table locks are:

  • explicit lock table
  • insert/update/delete on MyISAM
  • ALTER table on InnoDB

Once statements start waiting for lock on the table, all proceeding selects will wait for the previous locking statement to finish

Terminal 1:
mysql> select * from post where test like '%nomatch ' OR sleep(300) AND '1%';
….
Terminal 2:
mysql> alter table post engine=innodb;
...
Terminal 3:
mysql> select SQL_NO_CACHE count(*) from post;
…
Processlist:
+----------+----------------------+-----------+--------------------+---------+-------+---------------------------------+-----------------------------------------------------------------------+
| Id       | User                 | Host      | db                 | Command | Time  | State                           | Info                                                                  |
+----------+----------------------+-----------+--------------------+---------+-------+---------------------------------+-----------------------------------------------------------------------+
| 17170817 | root                 | localhost | janitest           | Query   |    19 | User sleep                      | select * from post where test like '%nomatch ' OR sleep(300) AND '1%' |
| 17170918 | root                 | localhost | janitest           | Query   |    11 | Waiting for table metadata lock | alter table post engine=innodb                                        |
| 17170957 | root                 | localhost | janitest           | Query   |     4 | Waiting for table metadata lock | select * from post                                                    |
+----------+----------------------+-----------+--------------------+---------+-------+---------------------------------+-----------------------------------------------------------------------+
3 rows in set (0.00 sec)

As we see in the example, ALTER table will wait until it can get a lock on post table, and this blocks every other select from now on to the table.
Or, if you are using MyISAM table, a simple update/insert will block access to the table, because it needs table level lock during them.

How can we defend ourselves from SQL injection?

There are several ways to secure yourself from SQL injection.

  • First of all, validate the input. If you expect only letters and numbers, filter it with regexp for example, to make sure there are no special characters there. Also escape the inputs on application side; programming languages have built-in function to do that (eg.: mysql_real_escape_string() in PHP)
  • Use prepared statement! It won’t allow 2 clause if you specified only 1. When you use prepared statements, the variables are transmitted as MySQL variables. Even if the string is not escaped, it will end up in one variable, and MySQL treats is as a longer string.
    (For more details see: http://dev.mysql.com/doc/refman/5.7/en/sql-syntax-prepared-statements.html )
  • Use a tool like MySQL Enterprise Firewall, which is a plugin for MySQL and can filter your statements to make sure there are no things like: || 1==1

I would like to start a little talk about this, so if you encountered SQL injection before, would you share it with us, how they did it, or in general how do you prevent SQL injections in your application?

 

Categories: DBA Blogs

prvf-0002 : could not retrieve local node name

Learn DB Concepts with me... - Wed, 2016-03-02 09:43

prvf-0002 : could not retrieve local node name


PRVF-0002 : could not retrieve local node name
check if the hostname is correct in sysconfig/network & /etc/hosts files:
[oracle@Linux03 ~]$ cat /etc/sysconfig/network | grep HOSTNAME
HOSTNAME=Linux03

[oracle@Linux03 ~]$ cat /etc/hosts
127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4
::1         localhost localhost.localdomain localhost6 localhost6.localdomain6

[oracle@Linux03 ~]$ vi /etc/hosts

[oracle@Linux03 ~]$ su root
Password:
[root@Linux03 oracle]# vi /etc/hosts

[root@Linux03 oracle]# cat /etc/hosts
127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4
::1         localhost localhost.localdomain localhost6 localhost6.localdomain6
10.215.xxx.xx Linux03   <<<--- added hostname and ip


An alternate solution is to add the hostname in bash_profile file in home dir:
[oracle@Linux03 ~]$ cat .bash_profile | grep HOSTNAME
export ORACLE_HOSTNAME=Linux03
Categories: DBA Blogs

Oracle Infrastructure Cloud Partner Briefings

Oracle Infrastructure Cloud  Webcasts - Partner Briefings ...

We share our skills to maximize your revenue!
Categories: DBA Blogs

Log Buffer #463: A Carnival of the Vanities for DBAs

Pythian Group - Tue, 2016-03-01 15:27

As the winter in the Northern hemisphere is giving way to spring, slowly but surely, blog posts are blooming in the gardens of Oracle, SQL Server and MySQL. This Log Buffer plucks some of them for your reading pleasure.

Oracle:

Providing A Persistent Data Volume to EMC XtremIO Using ClusterHQ Flocker, Docker And Marathon

There is sliced bread in SQL.

Oracle Cloud – Your service is suspended due to exceeding resource quota !

EM12c Compliance ‘Required Data Available’ flag – Understanding and Troubleshooting

How can I see my invisible columns

SQL Server:

Auto Generate Your Database Documentation

A Lightweight, Self-adjusting, Baseline-less Data Monitor

Keeping POST and GET Separated

How often should I run DBCC CHECKDB?

Disabling SQL Server Optimizer Rules with QUERYRULEOFF

MySQL:

MySQL Contributions status

Planets9s: Building scalable database infrastructures with MariaDB & HAProxy

High availability with asynchronous replication… and transparent R/W split

mysql_real_connect is not thread safe

Now available in swanhart-tools: NATIVE asynchronous query execution for any MySQL client!

Categories: DBA Blogs

GoldenGate 12.2 Big Data Adapters: part 1 – HDFS

Pythian Group - Mon, 2016-02-29 10:27

December 2015 brought us a new version of GoldenGate, and a new version for Big Data adapters for the GoldenGate. Let’s have a look at what we have now and how it works. I am going to start from the HDFS adapter.

As a first step, we need to prepare our source database for replication. It becomes easier with every new GoldenGate version. We will need to perform several steps:
a) Enable archive logging on our database. This particular step requires downtime.

orcl> alter database mount;

Database altered.

orcl> alter database archivelog;

Database altered.

orcl> alter database open;

b) Enable force logging and minimal supplemental logging. No need to shutdown database for this.

orcl> alter database add supplemental log data;

Database altered.

orcl> alter database force logging;

Database altered.

orcl> SELECT supplemental_log_data_min, force_logging FROM v$database;

SUPPLEME FORCE_LOGGING
-------- ---------------------------------------
YES	 YES

c) Switch parameter “enable_goldengate_replication” to “TRUE”. Can be done online.

orcl> alter system set enable_goldengate_replication=true sid='*' scope=both;

System altered.

orcl>

And we are almost done. Now we can create a schema for a GoldenGate administrator, and provide required privileges. I’ve just granted DBA role to the user to simplify process. In any case you will need it in case of integrated capture. For a production installation I advise you to have a look at the documentation to verify necessary privileges and roles.

orcl> create user ogg identified by welcome1 default tablespace users temporary tablespace temp;
orcl> grant connect, dba to ogg;

Let’s create a test schema to be replicated. We will call it schema on the source as ggtest and I will name the destination schema as bdtest. It will allow us also to check how the mapping works in our replication.

orcl> create tablespace ggtest; -- optional step 
orcl> create user ggtest identified by welcome1 default tablespace ggtest temporary tablespace temp;
orcl> grant connect, resource to ggtest;

Everything is ready on our source database for the replication.
Now we are installing Oracle GoledenGate for Oracle to our database server. We can get the software from the Oracle site on the download page in the Middleware section, GoldenGate, Oracle GoldenGate for Oracle databases. We are going to use 12.2.0.1.1 version of the software. The installation is easy – you need to unzip the software and run Installer which will guide you through couple of simple steps. The installer will unpack the software to the destination location, create subdirectories, and register GoldenGate in the Oracle global registry.

[oracle@sandbox distr]$ unzip fbo_ggs_Linux_x64_shiphome.zip
[oracle@sandbox distr]$ cd fbo_ggs_Linux_x64_shiphome/Disk1/
[oracle@sandbox Disk1]$ ./runInstaller

We continue by setting up parameters for Oracle GoldenGate (OGG) manager and starting it up. You can see that I’ve used a default blowfish encryption for the password. In a production environment you may consider another encryption like AES256. I’ve also used a non-default port for the manager since I have more than one GoldenGate installation on my test sandbox.

[oracle@sandbox ~]$ export OGG_HOME=/u01/oggora
[oracle@sandbox ~]$ cd $OGG_HOME
[oracle@sandbox oggora]$ ./ggsci

GGSCI (sandbox.localdomain) 1> encrypt password welcome1 BLOWFISH ENCRYPTKEY DEFAULT
Using Blowfish encryption with DEFAULT key.
Encrypted password:  AACAAAAAAAAAAAIARIXFKCQBMFIGFARA
Algorithm used:  BLOWFISH

GGSCI (sandbox.localdomain) 2> edit params mgr
PORT 7829
userid ogg@orcl,password AACAAAAAAAAAAAIARIXFKCQBMFIGFARA, BLOWFISH, ENCRYPTKEY DEFAULT
purgeoldextracts /u01/oggora/dirdat/*, usecheckpoints


GGSCI (sandbox.localdomain) 3> start manager
Manager started.

Let’s prepare everything for initial load, and later online replication.
I’ve decided to use GoldenGate initial load extract as the way for initial load for the sake of consistency for the resulted dataset on Hadoop.
I prepared the parameter file to replicate my ggtest schema and upload all data to the trail file on remote site. I’ve used a minimum number of options for all my processes, providing only handful of parameters required for replication. Extract options is a subject deserving a dedicated blog post. Here is my simple initial extract:

[oracle@sandbox oggora]$ cat /u01/oggora/dirprm/ini_ext.prm
SOURCEISTABLE
userid ogg@orcl,password AACAAAAAAAAAAAIARIXFKCQBMFIGFARA, BLOWFISH, ENCRYPTKEY DEFAULT
--RMTHOSTOPTIONS
RMTHOST sandbox, MGRPORT 7839
RMTFILE /u01/oggbd/dirdat/initld, MEGABYTES 2, PURGE
--DDL include objname ggtest.*
TABLE ggtest.*;

Then we run the initial load extract in passive node and it will create a trail file with the data. The trail file will be used later for our initial load on the target side.

[oracle@sandbox oggora]$ ./extract paramfile dirprm/ini_ext.prm reportfile dirrpt/ini_ext.rpt
[oracle@sandbox oggora]$ ll /u01/oggbd/dirdat/initld*
-rw-r-----. 1 oracle oinstall 3028 Feb 16 14:17 /u01/oggbd/dirdat/initld
[oracle@sandbox oggora]$

We can also prepare our extract on the source site as well. I haven’t used datapump in my configuration limiting the topology only by simplest and strait-forward extract to replicat configuration. Of course, in any production configuration I would advise using datapump on source for staging our data.
Here are my extract parameters, and how I added it. I am not starting it yet because I must have an Oracle GoldenGate Manager running on the target, and the directory for the trail file should be created. You may have guessed that the Big Data GoldenGate will be located in /u01/oggbd directory.

[oracle@sandbox oggora]$ ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 12.2.0.1.1 OGGCORE_12.2.0.1.0_PLATFORMS_151211.1401_FBO
Linux, x64, 64bit (optimized), Oracle 12c on Dec 12 2015 02:56:48
Operating system character set identified as UTF-8.

Copyright (C) 1995, 2015, Oracle and/or its affiliates. All rights reserved.



GGSCI (sandbox.localdomain) 1> edit params ggext

extract ggext
userid ogg@orcl,password AACAAAAAAAAAAAIARIXFKCQBMFIGFARA, BLOWFISH, ENCRYPTKEY DEFAULT
--RMTHOSTOPTIONS
RMTHOST sandbox, MGRPORT 7839
RMTFILE /u01/oggbd/dirdat/or, MEGABYTES 2, PURGE
DDL include objname ggtest.*
TABLE ggtest.*;


GGSCI (sandbox.localdomain) 2> dblogin userid ogg@orcl,password AACAAAAAAAAAAAIARIXFKCQBMFIGFARA, BLOWFISH, ENCRYPTKEY DEFAULT
Successfully logged into database.

GGSCI (sandbox.localdomain as ogg@orcl) 3> register extract GGEXT database

2016-02-16 15:37:21  INFO    OGG-02003  Extract GGEXT successfully registered with database at SCN 17151616.

GGSCI (sandbox.localdomain as ogg@orcl) 4> add extract ggext, INTEGRATED TRANLOG, BEGIN NOW
EXTRACT (Integrated) added.


Let’s leave our source site for a while and switch to the target . Our target is going to be a box where we have hadoop client and all requirement java classes.
I used the same box just to save resources on my sandbox environment. You may run different GoldeGate versions on the same box considering, that Manager ports for each of them will be different.
Essentially we need a Hadoop client on the box, which can connect to HDFS and write data there. Installation of Hadoop client is out of the scope for this article, but you can easily get all necessary information from the Hadoop home page .

Having all required Hadoop classes we continue by installing Oracle GoldenGate for Big Data, configuring and starting it up. In the past I received several questions from people struggling to find the exact place where all adapters could be uploaded. The Adapters were well “hidden” on “Oracle edelivery”, but now it is way simpler. You are going to GoldenGate download page on Oracle site and find the section “Oracle GoldenGate for Big Data 12.2.0.1.0” where you can choose the OGG for Linux x86_64, Windows or Solaris. You will need an Oracle account to get it. We upload the file to our linux box, unzip and unpack the tar archive. I created a directory /u01/oggbd as our GoldenGate home and unpacked the tar archive there.
The next step is to create all necessary directories. We start command line utility and create all subdirectories.

[oracle@sandbox ~]$ cd /u01/oggbd/
[oracle@sandbox oggbd]$ ./ggsci

Oracle GoldenGate Command Interpreter
Version 12.2.0.1.0 OGGCORE_12.2.0.1.0_PLATFORMS_151101.1925.2
Linux, x64, 64bit (optimized), Generic on Nov 10 2015 16:18:12
Operating system character set identified as UTF-8.

Copyright (C) 1995, 2015, Oracle and/or its affiliates. All rights reserved.



GGSCI (sandbox.localdomain) 1> create subdirs

Creating subdirectories under current directory /u01/oggbd

Parameter files                /u01/oggbd/dirprm: created
Report files                   /u01/oggbd/dirrpt: created
Checkpoint files               /u01/oggbd/dirchk: created
Process status files           /u01/oggbd/dirpcs: created
SQL script files               /u01/oggbd/dirsql: created
Database definitions files     /u01/oggbd/dirdef: created
Extract data files             /u01/oggbd/dirdat: created
Temporary files                /u01/oggbd/dirtmp: created
Credential store files         /u01/oggbd/dircrd: created
Masterkey wallet files         /u01/oggbd/dirwlt: created
Dump files                     /u01/oggbd/dirdmp: created


GGSCI (sandbox.localdomain) 2>

We are changing port for our manager process from default and starting it up. I’ve already mentioned that the port was changed due to the existence off several GoldenGate managers running from different directories.

GGSCI (sandbox.localdomain) 2> edit params mgr
PORT 7839
.....

GGSCI (sandbox.localdomain) 3> start manager
Manager started.

Now we have to prepare parameter files for our replicat processes. Let’s assume the environment variable OGGHOME represents the GoldenGate home and in our case it is going to be /u01/oggbd.
Examples for the parameter files can be taken from $OGGHOME/AdapterExamples/big-data directories. There you will find examples for flume, kafka, hdfs, hbase and for metadata providers. Today we are going to work with HDFS adapter.
I copied files to my parameter files directory ($OGGHOME/dirprm) and modified them accordingly:

oracle@sandbox oggbd]$ cp /u01/oggbd/AdapterExamples/big-data/hdfs/* /u01/oggbd/dirprm/
oracle@sandbox oggbd]$ vi /u01/oggbd/dirprm/hdfs.props

Here are my values for the hdfs.props file:

[oracle@bigdata dirprm]$ cat hdfs.props

gg.handlerlist=hdfs

gg.handler.hdfs.type=hdfs
gg.handler.hdfs.includeTokens=false
gg.handler.hdfs.maxFileSize=1g
gg.handler.hdfs.rootFilePath=/user/oracle/gg
gg.handler.hdfs.fileRollInterval=0
gg.handler.hdfs.inactivityRollInterval=0
gg.handler.hdfs.fileSuffix=.txt
gg.handler.hdfs.partitionByTable=true
gg.handler.hdfs.rollOnMetadataChange=true
gg.handler.hdfs.authType=none
gg.handler.hdfs.format=delimitedtext
#gg.handler.hdfs.format.includeColumnNames=true

gg.handler.hdfs.mode=tx

goldengate.userexit.timestamp=utc
goldengate.userexit.writers=javawriter
javawriter.stats.display=TRUE
javawriter.stats.full=TRUE

gg.log=log4j
gg.log.level=INFO

gg.report.time=30sec

gg.classpath=/usr/lib/hadoop/*:/usr/lib/hadoop/lib/*:/usr/lib/hadoop-hdfs/*:/usr/lib/hadoop/etc/hadoop/:/usr/lib/hadoop/lib/native/*

javawriter.bootoptions=-Xmx512m -Xms32m -Djava.class.path=ggjava/ggjava.jar

You can find information about all those parameters in oracle documentation here, but there are parameters you will most likely need to change from default:

  • gg.handler.hdfs.rootFilePath – it will tell where the directories and files have to be written on HDFS.
  • gg.handler.hdfs.format – you can setup one of the four formats supported by adapter.
  • goldengate.userexit.timestamp – it will depend from your preferences for transactions timestamps written to your hdfs files.
  • gg.classpath – it will depend from location for your hadoop jar classes and native libraries.

You can see I’ve mentioned the gg.handler.hdfs.format.includeColumnNames parameter. It is supposed to put column name before each value in the output file on HDFS. It may be helpful in some cases, but at the same time it makes the file bigger. If you are planning to create an external Hive table, you may consider commenting on it as I have.
The next parameter file is for our data initialization replicat file. You may consider using a Sqoop or another way to make the initial load for your tables, but I think it makes sense to use the GG replicat if the table size is relatively small. It makes the resulting file-set more consistent with the following replication, since it will be using the same engine and format. So, here is my replicat for initial load:

[oracle@sandbox dirprm]$ cat /u01/oggbd/dirprm/irhdfs.prm
--passive REPLICAT for initial load irhdfs
-- Trail file for this example is located in "dirdat/initld" 
-- Command to run REPLICAT:
-- ./replicat paramfile dirprm/irhdfs.prm reportfile dirrpt/ini_rhdfs.rpt
SPECIALRUN
END RUNTIME
EXTFILE /u01/oggbd/dirdat/initld
--DDLERROR default discard
setenv HADOOP_COMMON_LIB_NATIVE_DIR=/usr/lib/hadoop/lib/native
DDL include all
TARGETDB LIBFILE libggjava.so SET property=dirprm/hdfs.props
REPORTCOUNT EVERY 1 MINUTES, RATE
GROUPTRANSOPS 10000
MAP ggtest.*, TARGET bdtest.*;

I was running the initial load in passive mode, without creating a managed process and just running it from command line. Here is an example:

[oracle@sandbox oggbd]$ ./replicat paramfile dirprm/irhdfs.prm reportfile dirrpt/ini_rhdfs.rpt
[oracle@sandbox oggbd]$ hadoop fs -ls /user/oracle/gg/
Found 2 items
drwxr-xr-x   - oracle oracle          0 2016-02-16 14:37 /user/oracle/gg/bdtest.test_tab_1
drwxr-xr-x   - oracle oracle          0 2016-02-16 14:37 /user/oracle/gg/bdtest.test_tab_2
[oracle@sandbox oggbd]$ hadoop fs -ls /user/oracle/gg/bdtest.test_tab_1
Found 1 items
-rw-r--r--   1 oracle oracle        624 2016-02-16 14:37 /user/oracle/gg/bdtest.test_tab_1/bdtest.test_tab_1_2016-02-16_14-37-43.376.txt
[oracle@sandbox oggbd]$ hadoop fs -tail /user/oracle/gg/bdtest.test_tab_1/bdtest.test_tab_1_2016-02-16_14-37-43.376.txt
IBDTEST.TEST_TAB_12016-02-16 19:17:40.7466992016-02-16T14:37:43.37300000000000-100000020121371O62FX2014-01-24:19:09:20RJ68QYM52014-01-22:12:14:30
IBDTEST.TEST_TAB_12016-02-16 19:17:40.7466992016-02-16T14:37:44.89600000000000-100000021552371O62FX2014-01-24:19:09:20HW82LI732014-05-11:05:23:23
IBDTEST.TEST_TAB_12016-02-16 19:17:40.7466992016-02-16T14:37:44.89600100000000-100000022983RXZT5VUN2013-09-04:23:32:56RJ68QYM52014-01-22:12:14:30
IBDTEST.TEST_TAB_12016-02-16 19:17:40.7466992016-02-16T14:37:44.89600200000000-100000024414RXZT5VUN2013-09-04:23:32:56HW82LI732014-05-11:05:23:23
[oracle@sandbox oggbd]$

You can see the Hadoop directories and files created by the initial load.
As soon as the initial load has run we can start our extract and replicat to keep the destination side updated.
We are moving to source and starting our extract prepared earlier.

GGSCI (sandbox.localdomain as ogg@orcl) 6>start extract ggext

Sending START request to MANAGER ...
EXTRACT GGEXT starting

So, moving back to target and preparing our replicat. I used the replicat with the following parameters:

[oracle@sandbox oggbd]$ cat /u01/oggbd/dirprm/rhdfs.prm
REPLICAT rhdfs
-- Trail file for this example is located in "dirdat/or" directory
-- Command to add REPLICAT
-- add replicat rhdfs, exttrail dirdat/or
--DDLERROR default discard
setenv HADOOP_COMMON_LIB_NATIVE_DIR=/usr/lib/hadoop/lib/native
DDL include all
TARGETDB LIBFILE libggjava.so SET property=dirprm/hdfs.props
REPORTCOUNT EVERY 1 MINUTES, RATE
GROUPTRANSOPS 10000
MAP ggtest.*, TARGET bdtest.*;

We are adding the replicat to our configuration and it is going to carry on the replication.

GGSCI (sandbox.localdomain) 1> add replicat rhdfs, exttrail dirdat/or
REPLICAT added.


GGSCI (sandbox.localdomain) 2> start replicat rhdfs

Sending START request to MANAGER ...
REPLICAT RHDFS starting


GGSCI (sandbox.localdomain) 3>

Our replication is up and running, the initial load worked fine, and we can test and see what we have on source and target.
Here is the data in one of our source tables:

orcl> select * from ggtest.test_tab_1;

	   PK_ID RND_STR    USE_DATE	      RND_STR_1  ACC_DATE
---------------- ---------- ----------------- ---------- -----------------
	       1 371O62FX   01/24/14 19:09:20 RJ68QYM5	 01/22/14 12:14:30
	       2 371O62FX   01/24/14 19:09:20 HW82LI73	 05/11/14 05:23:23
	       3 RXZT5VUN   09/04/13 23:32:56 RJ68QYM5	 01/22/14 12:14:30
	       4 RXZT5VUN   09/04/13 23:32:56 HW82LI73	 05/11/14 05:23:23

orcl>

I’ve created an external Hive table for the table test_tab_1 to have better look.

hive> CREATE EXTERNAL TABLE BDTEST.TEST_TAB_1  (tran_flag string, tab_name string, tran_time_utc timestamp, tran_time_loc string,something string, something1 string,
    > PK_ID INT, RND_STR VARCHAR(10),USE_DATE string,RND_STR_1 string, ACC_DATE string)
    > stored as textfile location '/user/oracle/gg/bdtest.test_tab_1';
OK
Time taken: 0.327 seconds
hive> select * from BDTEST.TEST_TAB_1;
OK
I	BDTEST.TEST_TAB_1	2016-02-16 19:17:40.746699	2016-02-16T14:37:43.373000	00000000-10000002012		1	371O62FX	2014-01-24:19:09:20	RJ68QYM5	2014-01-22:12:14:30
I	BDTEST.TEST_TAB_1	2016-02-16 19:17:40.746699	2016-02-16T14:37:44.896000	00000000-10000002155		2	371O62FX	2014-01-24:19:09:20	HW82LI73	2014-05-11:05:23:23
I	BDTEST.TEST_TAB_1	2016-02-16 19:17:40.746699	2016-02-16T14:37:44.896001	00000000-10000002298		3	RXZT5VUN	2013-09-04:23:32:56	RJ68QYM5	2014-01-22:12:14:30
I	BDTEST.TEST_TAB_1	2016-02-16 19:17:40.746699	2016-02-16T14:37:44.896002	00000000-10000002441		4	RXZT5VUN	2013-09-04:23:32:56	HW82LI73	2014-05-11:05:23:23
Time taken: 0.155 seconds, Fetched: 4 row(s)
hive>

You can see the table definition is a bit different from what we have on the source site and you will see why.
We got additional columns on the destination side. Interesting that while some of them have a pretty clear purpose, the other columns are not totally clear and have null values.
The first column is a flag for operation, and it shows what kind of operation we have gotten in this row. It can be “I” for insert, “D” for delete and “U” for an update. The second column is table name. The third one is a timestamp in UTC showing when the transaction occurred. The next one is another time in local timezone informing the time of commit, and the next column has a commit sequence number. Those columns can help you to construct the proper data set for any given time.
Let’s insert and update some row(s) on source and see how it will show up on the target:

orcl> insert into ggtest.test_tab_1 values (5,'TEST_1',sysdate,'TEST_1',sysdate);

1 row created.

orcl> commit;

orcl> update ggtest.test_tab_1 set RND_STR='TEST_1_1' where PK_ID=5;

1 row updated.

orcl> commit;

Commit complete.

orcl>

Let’s check how it is reflected on the destination site. We see two new rows, where each row represents a DML operation. One was for the insert and the second one was for the update.

[highlight="7,8"
hive> select * from BDTEST.TEST_TAB_1;
OK
I	BDTEST.TEST_TAB_1	2016-02-16 19:17:40.746699	2016-02-16T14:37:43.373000	00000000-10000002012		1	371O62FX	2014-01-24:19:09:20	RJ68QYM5	2014-01-22:12:14:30
I	BDTEST.TEST_TAB_1	2016-02-16 19:17:40.746699	2016-02-16T14:37:44.896000	00000000-10000002155		2	371O62FX	2014-01-24:19:09:20	HW82LI73	2014-05-11:05:23:23
I	BDTEST.TEST_TAB_1	2016-02-16 19:17:40.746699	2016-02-16T14:37:44.896001	00000000-10000002298		3	RXZT5VUN	2013-09-04:23:32:56	RJ68QYM5	2014-01-22:12:14:30
I	BDTEST.TEST_TAB_1	2016-02-16 19:17:40.746699	2016-02-16T14:37:44.896002	00000000-10000002441		4	RXZT5VUN	2013-09-04:23:32:56	HW82LI73	2014-05-11:05:23:23
I	BDTEST.TEST_TAB_1	2016-02-16 20:43:32.000231	2016-02-16T15:43:37.199000	00000000000000002041		5	TEST_1	2016-02-16:15:43:25	TEST_1	2016-02-16:15:43:25
U	BDTEST.TEST_TAB_1	2016-02-16 20:43:53.000233	2016-02-16T15:43:56.056000	00000000000000002243		5	TEST_1_1
Time taken: 2.661 seconds, Fetched: 6 row(s)
hive>

It work for deletes too, only flag will be “D” instead of “I” for insert and “U” for updates.

What about DDL support? Let’s truncate the table.

orcl> truncate table ggtest.test_tab_1;

Table truncated.

orcl>

And here, there is nothing in our hdfs files. Maybe I’ve missed something, but it looks like the truncate operation is not creating any record. I need to dig a bit more. I will try to make a separate blog post about DDL support for the Big Data Adapters.
It works pretty well when we create a new table and insert new rows.

It also works if we change one of the existing tables, adding or dropping a column. Let’s try to create a new table here:

orcl> create table ggtest.test_tab_4 (pk_id number, rnd_str_1 varchar2(10),acc_date date);

Table created.

orcl> insert into ggtest.test_tab_4 select * from ggtest.test_tab_2;

1 row created.

orcl> commit;

Commit complete.

orcl>

You can see that it has created a new directory and file for the new table. Additionally, if you add a column the new file will be used for all DML operations for the updated table. It will help to separate tables with different structure.

[oracle@sandbox oggbd]$ hadoop fs -ls /user/oracle/gg/
Found 3 items
drwxr-xr-x   - oracle oracle          0 2016-02-16 15:43 /user/oracle/gg/bdtest.test_tab_1
drwxr-xr-x   - oracle oracle          0 2016-02-16 14:37 /user/oracle/gg/bdtest.test_tab_2
drwxr-xr-x   - oracle oracle          0 2016-02-16 15:56 /user/oracle/gg/bdtest.test_tab_4
[oracle@sandbox oggbd]$ hadoop fs -ls /user/oracle/gg/bdtest.test_tab_4/
Found 1 items
-rw-r--r--   1 oracle oracle        127 2016-02-16 15:56 /user/oracle/gg/bdtest.test_tab_4/bdtest.test_tab_4_2016-02-16_15-56-50.373.txt
[oracle@sandbox oggbd]$ hadoop fs -tail /user/oracle/gg/bdtest.test_tab_4/bdtest.test_tab_4_2016-02-16_15-56-50.373.txt
IBDTEST.TEST_TAB_42016-02-16 20:56:47.0009532016-02-16T15:56:50.371000000000000000000068327IJWQRO7T2013-07-07:08:13:52
[oracle@sandbox oggbd]$

At first glance it looks good, but let’s try to create a table as select.

orcl> create table ggtest.test_tab_3 as select * from ggtest.test_tab_2;

Table created.

orcl>

Not sure if it is an expected behavior or bug, but apparently it is not working. Our replicat is broken and complains that “DDL Replication is not supported for this database”.

[oracle@sandbox oggbd]$ tail -5 ggserr.log
2016-02-16 15:43:37  INFO    OGG-02756  Oracle GoldenGate Delivery, rhdfs.prm:  The definition for table GGTEST.TEST_TAB_1 is obtained from the trail file.
2016-02-16 15:43:37  INFO    OGG-06511  Oracle GoldenGate Delivery, rhdfs.prm:  Using following columns in default map by name: PK_ID, RND_STR, USE_DATE, RND_STR_1, ACC_DATE.
2016-02-16 15:43:37  INFO    OGG-06510  Oracle GoldenGate Delivery, rhdfs.prm:  Using the following key columns for target table bdtest.TEST_TAB_1: PK_ID.
2016-02-16 15:48:49  ERROR   OGG-00453  Oracle GoldenGate Delivery, rhdfs.prm:  DDL Replication is not supported for this database.
2016-02-16 15:48:49  ERROR   OGG-01668  Oracle GoldenGate Delivery, rhdfs.prm:  PROCESS ABENDING.
[oracle@sandbox oggbd]$

[oracle@sandbox oggbd]$ hadoop fs -ls /user/oracle/gg/
Found 2 items
drwxr-xr-x   - oracle oracle          0 2016-02-16 15:43 /user/oracle/gg/bdtest.test_tab_1
drwxr-xr-x   - oracle oracle          0 2016-02-16 14:37 /user/oracle/gg/bdtest.test_tab_2
[oracle@sandbox oggbd]$

What can we say in summary? The replication works, and supports all DML, and some DDL commands. You will need to prepare to get consistent datasets for any given time using flag for operation, and time for the transaction. In my next few posts, I will cover other Big Data adapters for GoldenGate.

Categories: DBA Blogs