Skip navigation.

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

A Repeatable Test Environment with Terraform and Chef-solo

Pythian Group - Mon, 2016-02-29 08:57

I have been refreshing my Chef skills, and have been looking for ways to incorporate simple strategies for deployment of temporary lab environments in the cloud, that provide a secure, flexible, repeatable, and cost-effective set of resources for testing code. One of the challenges of working in a Vagrant environment is how to protect our client’s intellectual property (i.e. the code we wish to test, in our case a Chef cookbook) when it is distributed around the globe on our consultants’ laptops. I wanted to tackle the problem simply, one step at a time.

As I see it, cloud services offer a flexible and secure environment in which policies can be managed, costs can be minimized, and access can be controlled. Three things are needed:

  1. A standard, flexible, easy to use deployment framework and template / example environment library
  2. An access control and environmental separation framework that allows lab resources to be deployed securely
  3. A set of templates and policies to manage and control shared infrastructure dependencies and to retire idle resources

Number one is how I spent my Sunday afternoon last week, with this as the result:

https://github.com/kpeder/theseeker

Following the README should get you started with deploying the host if you are familiar with Terraform already. This template uses AWS services, but the concept is portable to any of the multiple providers supported by Terraform.

What this is good for:

I think that the benefits of this approach to testing deployment code are clear, but to highlight:

  • We can add the cloud provider’s management framework and services to our test framework, providing a secure location and environmental separation as needed for testing of intellectual property (i.e. code).
  • We can store our test data in the cloud securely, and access it when required by keeping it on persistent storage (EBS in this case).
  • We use templated environments to ensure consistent and repeatable deployments for developers and engineers to collaborate on, as we would with a locally deployed Vagrant test environment.
  • We leverage scale and flexibility in the cloud to create practical testing environments that match our needs and those of our customers.
  • We leverage automation and environmental policies to ensure resource usage is minimized to that required for testing activities; systems are shut down and minimal storage costs incurred when the environments are not in use.

Prerequisites:

In preparation, you require an Amazon Web Services account with:
– an EC2 keypair with the private key available on your local host (usually in ~/.ssh/ somewhere).
– an IAM access key and secret access key for the AWS account that will be used to deploy.

To deploy the lab:

  1. Install Terraform (http://terraform.io) from Hashicorp (http://hashicorp.com) and ensure that the ‘terraform’ directory is somewhere in your path.
  2. Create a build directory and change into it. Clone the repository and change to the project directory. I always set up a remote (origin) at this point in git. We’ll be using the master branch.
  3. Per the README, ensure that you create a ‘terraform.tfvars’ file with 600 permissions and add your authentication token data in the format provided. (This file is excepted in the ‘.gitignore’ to prevent accidental upload of authentication tokens to the repository).
  4. Run ‘terraform plan’ and review the output. If no errors, run ‘terraform apply’. Your environment should start to build in AWS, and Terraform will create some local state files in the working project directory.
  5. Provisioning output will be displayed as the build and bootstrap progresses, with output of the commands run in the remote-exec provisioner section of template file ‘theseeker.tf’. (The Seeker‘ is a song by The Who if you are wondering, and IMO the best name ever given to a test host). Review this file to see what’s happening.
  6. When provisioning completes, we should have a functioning instance running Amazon Linux in US-West/Oregon, by default. If you change the region (in ‘variables.tf’) then don’t forget that you must also update your AMIs (also in ‘variables.tf’) to match the proper region. I’ve used this AMI in my example, specifically the HVM EBS-backed image.
  7. The public DNS address is output by Terraform. Connect to this using your private EC2 key using ‘ssh -i ~/.ssh/myec2key -l ec2-user fully.qualified.name’.

Note:

If you inspect the template noted in step 5 you will notice that the host is configured to not delete the EBS volume on termination, meaning that after instance termination the EBS volume will be preserved and can be booted from again (so we don’t lose our chef cookbook work between deployments). Do note however that for each node that is freshly provisioned using Terraform, a new volume will be created. If you wish to destroy the volume on creation, update the following section and change the value to ‘true’, however keep in mind that terminating the instance will terminate the data in your ‘chef-repo’.

root_block_device {
delete_on_termination = false
}

Chef-solo usage:

Now that we’ve logged into our host, let’s change directory to ‘chef-repo’ and have a look…

  1. From the ‘chef-repo’ directory, knife and knife-solo are already configured. You can see the configuration in ‘.chef/knife.rb’.
  2. The ‘cookbooks’ and ‘site-cookbooks’ directories are in the default cookbook path. No roles are configured. There is one node, ‘localhost’, under the nodes directory.
  3. To see the node configuration, run ‘knife node –local-mode list’… ‘localhost‘ is returned.
  4. Let’s have a look at the run list for this node, using ‘knife node –local-mode show localhost’… ‘recipe[ntp::default]‘ is returned in the Run List.
  5. We can see from the output that the run list for the node contains the ‘ntp::default’ recipe as configured by the remote-exec provisioner in the template.
  6. If we wanted to add additional recipes to the run list for the node, we can use ‘knife node –local-mode run_list add localhost ‘recipe[cookbook::recipe]”.
  7. Finally, we can apply the ntp recipe to the node localhost using ‘knife solo cook ec2-user@localhost -i ~/.ssh/mykey’. Note that a second run of the same command will yield a different result, as the new configuration has already been applied by Chef.
  8. When done with the environment, log out of the host and issue a ‘terraform destroy’ command. Type ‘yes’ in response to the confirmation message and Terraform will dismantle the lab and terminate the instance.

Other notes:

Don’t forget that you can now boot the instance from the EC2 console by selecting the volume we created as the new instance’s root (boot) volume.

Chef-solo also supports Berkshelf for dependency management, just install to your ‘chef-repo’ directory and chef-solo will act accordingly.

It’s worth noting that in my research I found that the the chef client now supports local mode operation and will eventually replace chef-solo for headless operation.

A great next step to get going with Terraform templates would be to create a new template file to deploy a second aws instance and then configure it with knife-solo from our first host. To ensure connectivity between nodes, ssh access must be available. To accomplish this simply, we can add the new host to the existing host group, ‘sg_theseeker_access’ which allows ssh login from anywhere by default. Again, refer to the instance template in step 5 in the deployment section above for an example of this.

In my next blog I will be describing a similar, templated Terraform environment that uses Ansible and Ambari Blueprints to deploy a 6+ node Hortonworks Hadoop cluster in ~45 minutes, ready to receive data and code for testing. For a sneak preview, see this github repository and dive into the README.

Have fun!

Categories: DBA Blogs

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

Categories: DBA Blogs

Compression -- 1b : (more on) BASIC Table Compression

Hemant K Chitale - Sun, 2016-02-28 04:38
In the previous blog, I demonstrated creating an empty table with BASIC Compression and then populating it.

What if you have a pre-existing table with data that you want to compress ?

Here I start with a table that already has rows but doesn't have compression configured.

PDB1@ORCL> select tablespace_name, segment_type, bytes/1048576
2 from user_segments
3 where segment_name = 'REGULAR_1';

TABLESPACE_NAME SEGMENT_TYPE BYTES/1048576
------------------------------ ------------------ -------------
HEMANT TABLE 49

PDB1@ORCL> select pct_free, blocks, num_rows, blocks*8192/1048576
2 from user_tables
3 where table_name = 'REGULAR_1';

PCT_FREE BLOCKS NUM_ROWS BLOCKS*8192/1048576
---------- ---------- ---------- -------------------
10 6224 364496 48.625

PDB1@ORCL>


I then proceed to rebuild it as a COMPRESSed table.

PDB1@ORCL> alter table regular_1 move compress;

Table altered.

PDB1@ORCL> exec dbms_stats.gather_table_stats('','REGULAR_1');

PL/SQL procedure successfully completed.

PDB1@ORCL> select tablespace_name, segment_type, bytes/1048576
2 from user_segments
3 where segment_name = 'REGULAR_1';

TABLESPACE_NAME SEGMENT_TYPE BYTES/1048576
------------------------------ ------------------ -------------
HEMANT TABLE 12

PDB1@ORCL> select pct_free, blocks, num_rows, blocks*8192/1048576
2 from user_tables
3 where table_name = 'REGULAR_1';

PCT_FREE BLOCKS NUM_ROWS BLOCKS*8192/1048576
---------- ---------- ---------- -------------------
0 1448 364496 11.3125

PDB1@ORCL>


Note how not only did the table size shrink to less than 12MB, the PCT_FREE also got  reset to 0 !
.
.
.

Categories: DBA Blogs

One Million Page Views

Oracle in Action - Fri, 2016-02-26 02:06

RSS content

Yesterday page views of  my blog touched the magical figure of 1 Million.  I had started blogging in November 2012 with the aim of saving whatever I learn, on the web in a language simple enough to be understood by even a beginner. Well, I myself was a beginner then and even now I am a beginner.  I think I am a bit skeptical – not easily convinced until I get a proof. So, in most of my posts , I try to verify the facts mentioned in oracle documentation and I presume that’s what has clicked with the readers of my blog.

I would like to thank all the readers of my blog  who spent their precious time in going through my posts and giving their valuable feedback.

Keep visiting my blog…



Tags:  

Del.icio.us
Digg

Comments:  16 comments on this item
You might be interested in this:  
Copyright © ORACLE IN ACTION [One Million Page Views], All Right Reserved. 2016.

The post One Million Page Views appeared first on ORACLE IN ACTION.

Categories: DBA Blogs

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

Categories: DBA Blogs

Oracle’s CREATE INDEX command can take a hint

Pythian Group - Thu, 2016-02-25 15:39

Here’s something really useful I discovered by accident when playing with Auto DOP (parallel_degree_policy) in 12c.

The “create index” command can take a hint – in particular the PARALLEL hint. The syntax is as you would expect:

create /*+PARALLEL*/ index tab_ind1 on tab(col1);

Of course, you can specify the number of parallel servers to be used by specifying PARALLEL(24) for example for 24 threads. The really interesting thing about using a hint vs. the documented syntax ("create index tab_ind1 on tab(col1) parallel 24;") is that once created – the index doesn’t have a default degree of parallelism. So you don’t need a second command to make the index noparallel.

Note that if you put the hint and use the “noparallel” attribute like so:

create /*+PARALLEL*/ index tab_ind1 on tab(col1) noparallel;

Then no parallelism will be used.

I tried using hints like FULL(t) to force an index create to use a full table scan instead of an existing index – but that doesn’t seem to work.

I discovered this under really interesting circumstances. I was testing some unrelated functionality that required some indexes created on my play table called CKK.

Here’s the SQL for the CKK table, which will create a 40 GB table with 2 rows per block:

create table ckk nologging tablespace ckk as
select rownum id, mod(rownum,5) mod5_id, mod(rownum,5000) mod5000_id, sysdate dt_fixed, sysdate - rownum/24/60 dt_dec, sysdate + rownum/24/60 dt_pos, sysdate + ora_hash(rownum,65,535)/24 dt_rand, sysdate+mod(rownum,10) dt_mod10, rpad('x',3500,'x') filler
from (select rownum r from dual connect by level <= 10000) r1, (select rownum r from dual connect by level <= 1000)
;

Then when I attempted to create an index on the table in parallel, Oracle refused to do so:

create index ckk$id on ckk(id) parallel 24; --DOES NOT run in parallel

Instead it created the index with 1 thread only, and then set the parallel degree policy to 24. I have tracked this problem down to the Auto DOP feature. If I turn it off via parallel_degree_policy=manual – the problem goes away. But I never expected this feature to turn off parallelism for index creation when explicitly requested.

Here’s the kicker – once any index is created on the table, future index creations will be automatically done in parallel, regardless if parallel was requested.

For example, this index would be now created in parallel:

create index ckk$mod5_id on ckk(mod5_id);

While before creating the index “ckk$id” – this index would refuse to get created in parallel – when using the parallel attribute.

That’s when I said to myself, “it’s almost like there’s a hint.” I took the hint, and discovered it does work, and it works more consistently than the attribute.

Categories: DBA Blogs

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

Pythian Group - Thu, 2016-02-25 15:07

This Log Buffer Edition covers Oracle, SQL Server and MySQL blog posts listing down few new tricks, tips and workarounds plus the news.

Oracle:

Displaying CPU Graphs For Different Time Ranges in Enterprise Manager

One of the cool things in 12c is that (finally after all these years) a sequence can be assigned as the default value for a column.

Jonathan Lewis demonstrates connect by after receiving an email.

Oracle 12c – PL/SQL “White List” via ACCESSIBLE BY

Oracle DBA, IT Manager, or Something Else

SQL Server:

Using R Machine Learning Script as a Power BI Desktop Data Source

SQL Authentication Via AD Groups Part II: Who has what access?

Using R Machine Learning Script as a Power BI Desktop Data Source

Connect to on premise data sources with Power BI Personal Gateway

Exploring SQL Server 2016 Dynamic Data Masking – Part One – Creating a Table that uses Dynamic Data Masking

MySQL:

Data Encryption at Rest

Planets9s: Download the new ClusterControl for MySQL, MongoDB & PostgreSQL

Automate your Database with CCBot: ClusterControl Hubot integration

Loading JSON into MariaDB or even MySQL – mysqljsonimport 2.0 is available

Privileges in MySQL and MariaDB: The Weed Of Crime Bears Bitter Fruit

Categories: DBA Blogs