Skip navigation.

DBA Blogs

Troubleshooting a Multipath Issue

Pythian Group - Tue, 2015-01-06 09:37

Multipathing allows to configure multiple paths from servers to storage arrays. It provides I/O failover and load balancing. Linux uses device mapper kernel framework to support multipathing.

In this post I will explain the steps taken to troubleshoot a multipath issue. This should provide an glimpse into the tools and technology involved. Problem was reported in a RHEL6 system in which a backup software is complaining that the device from which /boot is mounted does not exist.

Following is the device. You can see the device name is a wwid.

# df
Filesystem 1K-blocks Used Available Use% Mounted on
[..]
/dev/mapper/3600508b1001c725ab3a5a49b0ad9848ep1
198337 61002 127095 33% /boot

File /dev/mapper/3600508b1001c725ab3a5a49b0ad9848ep1 is missing under /dev/mapper.

# ll /dev/mapper/
total 0
crw-rw—- 1 root root 10, 58 Jul 9 2013 control
lrwxrwxrwx 1 root root 7 Jul 9 2013 mpatha -> ../dm-1
lrwxrwxrwx 1 root root 7 Jul 9 2013 mpathap1 -> ../dm-2
lrwxrwxrwx 1 root root 7 Jul 9 2013 mpathb -> ../dm-0
lrwxrwxrwx 1 root root 7 Jul 9 2013 mpathc -> ../dm-3
lrwxrwxrwx 1 root root 7 Jul 9 2013 mpathcp1 -> ../dm-4
lrwxrwxrwx 1 root root 7 Jul 9 2013 mpathcp2 -> ../dm-5
lrwxrwxrwx 1 root root 7 Jul 9 2013 vgroot-lvroot -> ../dm-6
lrwxrwxrwx 1 root root 7 Jul 9 2013 vgroot-lvswap -> ../dm-7

From /ect/fstab, it is found that UUID of the device is specified.

UUID=6dfd9f97-7038-4469-8841-07a991d64026 /boot ext4 defaults 1 2

From blkid, we can see the device associated with the UUID. blkid command prints the attributes of all block device in the system.

# blkid
/dev/mapper/mpathcp1: UUID=”6dfd9f97-7038-4469-8841-07a991d64026″ TYPE=”ext4″

Remounting the /boot mount point shows user friendly name /dev/mapper/mpathcp1.

# df
Filesystem 1K-blocks Used Available Use% Mounted on
[..]
/dev/mapper/mpathcp1 198337 61002 127095 33% /boot

From this far, we can understand that the system is booting with wwid as device name. But later the device name is converted into user friendly name. In multipath configuration user_friendly_names is enabled.

# grep user_friendly_names /etc/multipath.confuser_friendly_names yes

As per Red Hat documentation,

“When the user_friendly_names option in the multipath configuration file is set to yes, the name of a multipath device is of the form mpathn. For the Red Hat Enterprise Linux 6 release, n is an alphabetic character, so that the name of a multipath device might be mpatha or mpathb. In previous releases, n was an integer.”

As the system is mounting the right disk after booting up, problem should be with the user friendly name configuration in initramfs. Extracting the initramfs file and checking the multipath configuration shows that user_friendly_names parameter is enabled.

# cat initramfs/etc/multipath.conf
defaults {
user_friendly_names yes

Now the interesting point is that, /etc/multipath/bindings is missing in initramfs. But the file is in the system. /etc/multipath/bindings file is used to refer wwid with alias.

# cat /etc/multipath/bindings
# Multipath bindings, Version : 1.0
# NOTE: this file is automatically maintained by the multipath program.
# You should not need to edit this file in normal circumstances.
#
# Format:
# alias wwid
#
mpathc 3600508b1001c725ab3a5a49b0ad9848e
mpatha 36782bcb0005dd607000003b34ef072be
mpathb 36782bcb000627385000003ab4ef14636

initramfs can be created using dracut command.

# dracut -v -f test.img 2.6.32-131.0.15.el6.x86_64 2> /tmp/test.out

Building a test initramfs file shows that a newly created initramfs is including /etc/multipath/bindings.

# grep -ri bindings /tmp/test.out
I: Installing /etc/multipath/bindings

So this is what is happening,
When system boots up, initramfs looks for /etc/multipath/bindings for aliases in initramfs to use for user friendly names. But it could not find it and and uses wwid. After system boots up /etc/multipath/bindings is present and device names are changed to user friendly names.

Looks like the /etc/multipath/bindings file is created after kernel installation and initrd generation. This might have happened as multipath configuration was done after kernel installation. Even if the system root device is not on multipath, it is possible for multipath to be included in the initrd. For example, this can happen of the system root device is on LVM. This should be the reason why multupath.conf was included in the initramfs and not /etc/multipath/bindings.

To solve the issue we can to rebuild the initrd and restart the system. Re-installing existing kernel or installing new kernel would also fix the issue as the initrd would be rebuilt in both cases..

# dracut -v -f 2.6.32-131.0.15.el6.x86_64
Categories: DBA Blogs

Access Oracle GoldenGate JAgent XML from browser

DBASolved - Tue, 2015-01-06 09:26

There are many different ways of monitoirng Oracle GoldenGate; I have posted about many of these in earlier blog posts.  Additionally, I have talked about the different ways of monitoring Oracle GoldenGate at a few conferences as well.  (The slides can be found on my slideshare site if wanted).  In both my blog and presentations I highlight many different approaches; yet I forgot one that I think is really cool!  This one was shown to me by an Oracle Product Manager before Oracle Open World 2014 back in October (yes, I’m just now getting around to writing about it).  

This approach is using the Oracle GoldenGate Manager (port) to view a user friendly version of the XML that is passed by the Oracle Monitor Agent (JAgent) to monitoring tools like Oracle Enterprise Manager or Oracle GoldenGate Director.  This approach will not work with older versions of the JAgent.

Note: The Oracle Monitor Agent (JAgent) used in this approach is version 12.1.3.0.  It can be found here.  

Note: There is a license requirement to use this approach since this is part of the Management Pack for Oracle GoldenGate.  Contact you local sales rep for more info.

After the Oracle Monitor Agent (JAgent) is configured for your environment, the XML can be accessed via any web browser.  Within my test enviornment, I have servers named OEL and FRED.  The URLs needed to to view this cool feature are:

OEL:
http://oel.acme.com:15000/groups

FRED:
http://fred.acme.com:15000/groups

As you can see, by using the port number (15000) of the Manager process, I can directly tap into the information being feed to the management tools for monitoring.  The “groups” directory places you at the top level of the monitoring stack.  By clicking on a process groups, this will take you down into the process group and show additional items being monitored by the JAgent.

In this example, you are looking at the next level down for the process EXT on OEL.  At this point, you can see what is available: monitoring points, messages, status changes and associated files for the extract process.

OEL:
http://oel.acme.com:15000/groups/EXT


Digging further into the stack, you can see what files are associated with the process.  (This is an easy way to identify parameter files without having to go directly to the command line).

OEL:
http://oel.acme.com:15000/groups/EXT/files

OEL:
http://oel.acme.com:15000/groups/EXT/files/dirprm



As you can see, the new Oracle Monitor Agent (JAgent) provides you another way of viewing your Oracle GoldenGate environment without needing direct access to the server.  Although this is a cool way of looking at a Oracle GoldenGate environment, it does not replace traditionall monitoring approaches.  

Cool Tip: The OS tool “curl” can be used to dump similar XML output to a file (showed to me by the product team).

$ curl --silent http://oel.acme.com:15000/registry | xmllint --format -

<?xml version="1.0"?>
<?xml-stylesheet type="text/xsl" href="/style/registry.xsl"?>
<registry xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="http://oel.acme.com:15000/schema/registry.xsd">
<process name="PMP" type="4" status="3"/>
<process name="EXT" type="2" mode="1" status="3"/>
<process name="MGR" type="1" status="3"/>
</registry>

In my opinion, many of the complants about the original version of the JAgent have been addressed with the latest release of the Oracle Monitor Agent (JAgent).  Give it a try!
 
Enjoy!

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


Filed under: Golden Gate
Categories: DBA Blogs

Who is a DBA Leader?

Pakistan's First Oracle Blog - Tue, 2015-01-06 06:00
Sitting behind a big mahogany table, smoking Cuban Cigar, glaring at the person sitting across, one hand taking the receive of black phone to right ear, and the other hand getting the mobile phone off the left ear can be the image of a DBA boss in any white elephant government outfit, but it certainly cannot work in organization made up of professionals like database administrators. And if such image or similar image is working in any such company then that company is not great. It's as simple as that.






So who is DBA leader? The obvious answer is the person who leads a team of database administrators. Sounds simple enough, but it takes a lot to be a true leader. There are many DBA bosses at various layers, DBA managers at various layers, but being a DBA leader is very different. If you are a DBA leader, then you should be kinda worshiped. If you work in a team which has a DBA leader, then you are a very lucky person.

A DBA leader is the one who leads by an example. He walks the talk. He is the doer and not just talker. He inspires, motivates, and energizes the team members to follow him and then exceed his example. For instance, when client asks to improve that performance issue in the RAC cluster, the DBA leader would first jump in at the problem and start collaborating with team. He would analyze the problem, would present his potential solutions or at least line of action. He would engage the team and would listen to them. He won't just assing the problem to somebody, then disappear, and come back at 5pm asking about status. DBA leader is not super human, so he will get problems of which he won't have any knowledge. He will research the the problem with team and will learn and grow with them. That approach would electrify the team.

A DBA leader is a grateful person. He doesn't seem to thank his team enough for doing a great job. When under the able leadership of the DBA leader, team would reach to a solution, then regardless of his contribution, a DBA leader would make his team look awesome. That will generate immense prestige for the DBA leader at the same time, while making team looking great. Team would cherish the fact that solution was reached after deep insights of the DBA leader, and yet leader gave credit to them.

A DBA leader is the one who is always there. He falls before the team falls, and doesn't become aloof when things don't go well. Things will go wrong and crisis will come. In such situations, responsibility is shared and DBA leader doesn't shirk from it. In the team of DBA leader, there are no scapegoats.

A leader of DBAs keeps both big piture and ther details in perspective at the same time. He provides the vision and lives the vision from the front. He learns and then he leads. He does all of this and does it superbly and that is why he is the star and such a rare commodity, and that is why he he is the DBA LEADER.

Categories: DBA Blogs

MySQL Locking

Kubilay Çilkara - Tue, 2015-01-06 03:53
MySQL and Row Level Locking? Or why are you getting the error:

ERROR 1205 (HY000) : Lock wait timeout exceeded; try restarting transaction

You get the error because your allocated time to hold a DML lock in a transaction exceeds the set limit. Usually the default limit to hold a DML row lock, set by innodb_lock_wait_timeout db parameter, is 50 seconds. If your transaction doesn't commit/rollback within 50 seconds you will get this error. We don't want to hold locks for longer than 50 seconds anyway, throughput would be affected.

And yes MySQL in innodb uses row level locking. Since MySQL 5.1+ (time Oracle took over) it does row level locking in its InnoDB tables. That means only the rows which are selected . . . FOR UPDATE . . . are locked and not the  whole table. To see the threads (sessions) which are locking other threads and which queries are locking, use the following INFORMATION_SCHEMA dictionary SQL query as DBA.  You will be able to see blockers and waiters of transactions  waiting on locks. Run it as is using INFORMATION_SCHEMA schema, no modifications.

Use this SQL query to monitor locks and transactions and note that query will return data only when there are locks!


SELECT 
    r.trx_id AS wating_trx_id,
    r.trx_mysql_thread_id AS waiting_thread,
    TIMESTAMPDIFF(SECOND,
        r.trx_wait_started,
        CURRENT_TIMESTAMP) AS wait_time,
    r.trx_query AS waiting_query,
    l.lock_table AS waiting_table_lock,
    b.trx_id AS blocking_trx_id,
    b.trx_mysql_thread_id AS blocking_thread,
    SUBSTRING(p.host,
        1,
        INSTR(p.host, ':') - 1) AS blocking_host,
    SUBSTRING(p.host,
        INSTR(p.host, ':') + 1) AS blocking_port,
    IF(p.command = 'Sleep', p.time, 0) AS idle_in_trx,
    b.trx_query AS blocking_query
FROM
    information_schema.innodb_lock_waits AS w
        INNER JOIN
    information_schema.innodb_trx AS b ON b.trx_id = w.blocking_trx_id
        INNER JOIN
    information_schema.innodb_trx AS r ON r.trx_id = w.requesting_trx_id
        INNER JOIN
    information_schema.innodb_locks AS l ON w.requested_lock_id - l.lock_id
        LEFT JOIN
    information_schema.PROCESSLIST AS p ON p.id = b.trx_mysql_thread_id
ORDER BY wait_time DESC;

Categories: DBA Blogs

WARNING!!! Maximum Load 800 lbs

Pythian Group - Mon, 2015-01-05 13:45

Is it just my recently discovered dumbness or am I really on to something here? I just returned home from a ritzy shopping mall and had to ride down an elevator with a brimming shopping trolly filled with items which my companion miraculously managed to buy from every sale from every corner of that mall. Anyway before I recall that shopping bill and weep copiously, I just wanted to share something which always bugs me when I use elevators.

Inside the elevators, there are only two things to do. Either stare at the faces of other riders and enjoy the embarrassment of shifting eyes from face to face or the other option is to look onto the walls. Like others, I always select the latter option. One thing which every elevator says to me is something like ‘Maximum Load 800 lbs’ or any other number.

Now that not only baffles me but also creates a passive panic. I quickly calculate my weight, then frantically and stealthily glance at other bodies inside and then guess the total weight in there. More often than not, it turns out that the weight of bodies plus their heavyset trollies exceed or teeter on the edge of the warning lbs.

On this very moment, the other elevator hobbits notice my ashen face, violently trembling body, sunken eyes and follow them to the warning. Some get the point and follow the ritual of getting panicked, some try to recall emergency ambulance number after watching us, and some just don’t give the flying heck.

My question here is why on Earth they write it inside the elevator when its too late to do anything about it. Do they really write it seriously or they have just assumed that never that weight would be reached? I personally know a group of people including me who for sure can easily break that weight record. They all live nearby and use the same shopping center and elevator of course. There is every chance that one day they all will come on same time and use that elevator. What happens then? Or I am just paranoid beyond cure? May be if elevator guys would write it outside it, and place a weighing machine on entry of elevator. The machine would calculate the weight as people would move in, and will close its door as soon as it reaches near it.

Interestingly enough, I have seen databases being used as elevators by the applications and data loaders. Applications and data loading without any consideration of design, scalability or performance or capacity management just throng the database. Unlike elevators, I have seen databases break down or coming to grinding halt due to runaway or in other words over-weight applications.

That panics me in the same way as elevators do.

Categories: DBA Blogs

Partner Webcast – Oracle Enterprise Metadata Management and its impact on Oracle Partner business

On the 20th of October 2014, Oracle announced the general availability of Oracle Enterprise Metadata Management (OEMM), Oracle's comprehensive Metadata Management technology for Data Governance....

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

Script for Exadata I/O Report

Pakistan's First Oracle Blog - Mon, 2015-01-05 00:02
select function_name,sum(SMALL_READ_MEGABYTES)/1024 SM_Read_GB,
sum(SMALL_WRITE_MEGABYTES)/1024 SM_Write_GB,
sum(LARGE_READ_MEGABYTES)/1024 LG_Read_GB, sum(LARGE_WRITE_MEGABYTES)/1024 LG_Write_GB,
sum(LARGE_READ_REQS) LG_Read_Requests,
sum(LARGE_Write_REQS) LG_Write_Requests
from v$iostat_function_detail
group by function_name;
Categories: DBA Blogs

Log Buffer #404, A Carnival of the Vanities for DBAs

Pakistan's First Oracle Blog - Mon, 2015-01-05 00:01

With new year already in fast gear, bloggers are sparing no stone unturned to come up with innovative ideas. This Log Buffer edition is keeping pace with them as always.
Oracle:

While playing with 12c Scott tried the upgrade to the DEFAULT column syntax that now allows sequences.
This is an age old question and of course the answer depends on how you say “SQL”.
Happy New Year! Upgraded 12.1.0.1 Grid Infrastructure to 12.1.0.2 and applied the Oct 2014 PSU. Had an error during rootupgrade.sh as well, due to the ASM spfile being on disk instead of on ASM diskgroup.
If you (already) created your first Oracle Service Bus 12c application/project with SOAP webservices and tried to deploy it to your IntegratedWeblogic server you might be familiar with this error.
Using Drag-Drop functionality in af:treeTable to move data between nodes.

SQL Server:

Hadoop has been making a lot of noise in the Big Data world.
Lets look at two different ways of creating an HDInsight Cluster: Creating an HDInsight Cluster through Azure Management Portal, and creating an HDInsight Cluster through Windows Azure PowerShell.
Why you need test driven development.
SQL Server Data Import System to Alert For Missed Imports.
Create stunning visualizations with Power View in 20 minutes or less!

MySQL:

So assume you just uploaded the certificate you use to identify yourself to the MySQL server to Github or some other place it doesn’t belong…and there is no undelete.
MySQL Plugin for Oracle Enterprise Manager on VirtualBox: installation gotchas.
MariaDB slave restore using GTID & xtrabackup bug.
How small changes impact complex systems – MySQL example.
In this post, Louis talk about MHA GTID behavior, we test different cases and find something is different from previous versions.

Also Published at Pythian Blog.
Categories: DBA Blogs

Partner Webcast – Extending Oracle Applications with Oracle Fusion Middleware Platform

Oracle Fusion Middleware technologies can help strengthen your investments in Oracle Applications, as we’ve discussed on the Oracle AppAdvantage program, by delivering a superior experience,...

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

Log Buffer #404, A Carnival of the Vanities for DBAs

Pythian Group - Fri, 2015-01-02 09:35

With new year already in fast gear, bloggers are sparing no stone unturned to come up with innovative ideas. This Log Buffer edition is keeping pace with them as always.

Oracle:

While playing with 12c Scott tried the upgrade to the DEFAULT column syntax that now allows sequences.

This is an age old question and of course the answer depends on how you say “SQL”.

Happy New Year! Upgraded 12.1.0.1 Grid Infrastructure to 12.1.0.2 and applied the Oct 2014 PSU. Had an error during rootupgrade.sh as well, due to the ASM spfile being on disk instead of on ASM diskgroup.

If you (already) created your first Oracle Service Bus 12c application/project with SOAP webservices and tried to deploy it to your IntegratedWeblogic server you might be familiar with this error.

Using Drag-Drop functionality in af:treeTable to move data between nodes.

SQL Server:

Hadoop has been making a lot of noise in the Big Data world.

Lets look at two different ways of creating an HDInsight Cluster: Creating an HDInsight Cluster through Azure Management Portal, and creating an HDInsight Cluster through Windows Azure PowerShell.

Why you need test driven development.

SQL Server Data Import System to Alert For Missed Imports.

Create stunning visualizations with Power View in 20 minutes or less!

MySQL:

So assume you just uploaded the certificate you use to identify yourself to the MySQL server to Github or some other place it doesn’t belong…and there is no undelete.

MySQL Plugin for Oracle Enterprise Manager on VirtualBox: installation gotchas.

MariaDB slave restore using GTID & xtrabackup bug.

How small changes impact complex systems – MySQL example.

In this post, Louis talk about MHA GTID behavior, we test different cases and find something is different from previous versions.

Categories: DBA Blogs

Oracle Fusion Middleware EMEA Partner Community Forum 2015

Take this opportunity and register now for the Oracle Fusion Middleware Partner Community Forum XX Budapest in on March 3th & 4th 2015. with hands-on training delivered on March...

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

Oracleinaction.com in 2014 : A review

Oracle in Action - Wed, 2014-12-31 10:17

RSS content

The Louvre Museum has 8.5 million visitors per year. This blog was viewed about 320,000 times in 2014 with an average of 879 page views per day. If it were an exhibit at the Louvre Museum, it would take about 14 days for that many people to see it.

The busiest day of the year was December 1st with 1,656 views. The most popular post that day was ORACLE CHECKPOINTS.

These are the posts that got the most views on ORACLE IN ACTION in 2014.

The blog was visited by readers from 194 countries in all!
Most visitors came from India. The United States & U.K. were not far behind.

Thanks to all the visitors.

Keep visiting and giving your valuable feedback.

Wish you all a Very Happy New Year 2015  !!!!!



Tags:  

Del.icio.us
Digg

Comments:  0 (Zero), Be the first to leave a reply!
You might be interested in this:  
Copyright © ORACLE IN ACTION [Oracleinaction.com in 2014 : A review], All Right Reserved. 2015.

The post Oracleinaction.com in 2014 : A review appeared first on ORACLE IN ACTION.

Categories: DBA Blogs

uhesse.com in 2014 – a Review

The Oracle Instructor - Wed, 2014-12-31 09:56
Better than ever

2014 brought a new high water mark with 282,000 hits!

Meanwhile, uhesse.com gets over 1,000 hits per day on average.

Who are the referrers?

The vast majority of visitors came to uhesse.com via google searches: 163,000

On a distant second place is Twitter: 2,500 visitors came from there

Facebook and LinkedIn each lead 800 visitors to uhesse.com

400 visitors came from jonathanlewis.wordpress.com – thank you, Jonathan!

Where did uhesse.com refer to?

As a good employee, I sent 1,500 visitors to education.oracle.com and 1,300 to oracle.com :-)

About 600 each went to jonathanlewis.wordpress.com and blog.tanelpoder.com, while richardfoote.wordpress.com got 350 visitors

Nationality of the visitors

They came from almost everywhere, the full list contains 200 countries in total – isn’t the internet a great thing?

Vistors of uhesse.com by nationality

Thank you all for visiting – I hope you come back in 2015 again :-)


Categories: DBA Blogs

What it’s Like to Intern at Pythian

Pythian Group - Tue, 2014-12-30 14:00

About eight months ago I started working as a Global Talent Acquisition Intern here at Pythian. It was my first co-op work placement, and my first experience in a professional work setting. I had never done anything like it before and was definitely nervous yet excited for the opportunity. As any eager but nervous person, I researched as much as I could to prepare myself for what I’d be doing. I distinctly remember searching on the Internet; What does an intern do?” and was a little unnerved by some of the results that I came across. Being an intern couldn’t be that bad now could it? Surely what I was reading was a collection of misconceptions—people wouldn’t lie and exaggerate things on the Internet now would they? After having worked at Pythian for this long, I can finally put those misconceptions to rest and reveal firsthand what it’s really like to be an intern—well, here at Pythian at least.

“You don’t get to choose the people you work with.”

I worked as an intern in Talent Acquisition, the functional department within Human Resources whose main task is to acquire and retain the most important resource for Pythian; its people. Working in TA, I got see exactly what Pythian meant when it said it hires only the top 5%. For a company that hires talent from all around the world, it takes a special group of people who understand the complexities of knowing not only where to look, but also who to look for in order to hire the right person. As a result, not only are the people being hired exceptionally talented across a vast spectrum of technologies, but the team doing the hiring, Talent Acquisition itself, is amazingly talented at what they do (they even won an award for it this year!).

Whether you’re an intern or a regular employee, there is one thing that both have in common, and it’s that you don’t get to choose the people you work with. As soon as I walked through the doors of the TA office, I knew that I was in good hands. My colleagues respected me and treated me as one of them from the get-go and sometimes even came to me for advice and assistance. It was humbling and a true reflection of the type of people who work at Pythian. I never once was treated like an intern, nor did I ever feel like one. There were no coffee runs that I had to do and there was never a situation where I had to push papers all day. This was nothing like what my Google searches had led me to believe.

“What do I know? I’m only an intern!”

Speaking of pushing papers all day and running coffee errands, many people often get the impression that working as an intern means having to do the work that no one else wants to do. In other words, an intern is simply there to do the mundane. That may be true for internships elsewhere but here at Pythian, the work I was doing was far from mundane—it was both challenging and quite interesting. There were times where I couldn’t believe what I was asked to do. Not because I didn’t want to do it, but because I couldn’t conceive why something so important was being given to me to figure out. What do I know? I’m only an intern! Within the first week I found myself working on projects whose impact would be felt by the entire team. I wasn’t being given work for the sake of work to keep me busy, rather, the work I was doing had a sense of tangibility to it. I was able to see the impact before, during and after and each time I knew that what I was doing was truly going to make a difference in the end.

True to their nature, internships allow for lots of opportunities when it comes to learning. Everywhere you turn in Pythian there is something to learn and someone willing to teach. As a Commerce student, there is only so much you can learn about organizations and how they operate just by sitting in a lecture. At Pythian, I got to experience it. Each and every day I was learning something new, something that I didn’t know before. I had the opportunity to really see the inner workings of a successful organization and understand how all the pieces of the company worked together towards a common purpose. There were also opportunities to participate in workshops with other co-op students on topics that ranged from business acumen to time management. These workshops concluded with a final session where each of us got to present some of the things we had been working on in front of our managers and supervisors. This experience was one of the many highlights of my time at Pythian, as it was a great way to interact and learn from other students like myself.

“Get comfortable at being uncomfortable.”

If there is one thing that I could tell future co-op students or interns here at Pythian it is to not be afraid to leave your comfort zone. Pythian’s CEO at the time, Andrew Waitman, said it best when he said, “get comfortable at being uncomfortable.” When given an opportunity to do something you haven’t done before, don’t think about whether or not you can do it—just go for it. You’ll be surprised what you are capable of when you get past that feeling of uncertainty and doubt. Making mistakes is inevitable, but it is also a necessary step in order to learn and grow. Always ask questions and have an open mind in everything that you do. When all is said and done, you will leave Pythian smarter, more confident, and more prepared for anything that comes your way.

Categories: DBA Blogs

Watch: Riak vs. Oracle

Pythian Group - Tue, 2014-12-30 11:58

Every data platform has its value, and deciding which one will work best for your big data objectives can be tricky—Alex Gorbachev, Oracle ACE Director, Cloudera Champion of Big Data, and Chief Technology Officer at Pythian, has recorded a series of videos comparing the various big data platforms and presents use cases to help you identify which ones will best suit your needs.

Riak and Oracle are completely different platforms. Alex explains that “Oracle database is a very feature-rich platform,” while Riak, Alex explains, “…is a very simple model… with very minimalistic features.” Riak is excellent for a public cloud infrastructure because it provides elasticity and scalability on demand. Learn about more use cases from Alex’s video Riak vs. Oracle.

Note: You may recognize this series, which was originally filmed back in 2013. After receiving feedback from our viewers that the content was great, but the video and sound quality were poor, we listened and re-shot the series.

Find the rest of the series here

 

Pythian is a global leader in data consulting and managed services. We specialize in optimizing and managing mission-critical data systems, combining the world’s leading data experts with advanced, secure service delivery. Learn more about Pythian’s Big Data expertise.

Categories: DBA Blogs

Calculating Business Days in HiveQL

Pythian Group - Tue, 2014-12-30 09:07

One of the common tasks in data processing is to calculate the number of days between two given dates. You can easily achieve this by using Hive DATEDIFF function. You can also get weekday number by using this more obscure function:

SELECT FROM_UNIXTIME(your_date,'u') FROM some_table;

This will return 1 for Monday, 7 for Sunday and is based on Java SimpleDateFormat —
http://docs.oracle.com/javase/7/docs/api/java/text/SimpleDateFormat.html

It becomes more challenging if you need to calculate the number of business days (excluding Saturdays and Sundays) between two dates. There is no built-in function in Hive to perform such calculation, but fortunately it is rather simple to write your own Hive UDFs. I couldn’t quickly find any existing open source functions to solve this problem, so I wrote my own using Scala — https://github.com/pythian/hive-udfs

There are actually three functions in hive-udfs package: CountBusinessDays, CountSaturdays and CountSundays. These functions accept start date and end date as UNIX_TIMESTAMP and return the count of different types of full days in this interval, excluding start and end points.

Here is how you can use this UDF in Hive:

ADD JAR hdfs:///user/hive/udfs/pythian-hive-udfs-assembly-0.1.jar;
CREATE TEMPORARY FUNCTION count_business_days AS 'com.pythian.udf.CountBusinessDays';
CREATE TEMPORARY FUNCTION count_saturdays AS 'com.pythian.udf.CountSaturdays';
CREATE TEMPORARY FUNCTION count_sundays AS 'com.pythian.udf.CountSundays';

SELECT count_business_days(UNIX_TIMESTAMP(start_date), UNIX_TIMESTAMP(end_date)) FROM some_table;

The code is open source and free to use. Comments and suggestions are always welcome.

Categories: DBA Blogs

How to Migrate a Database Using GoldenGate

Pythian Group - Tue, 2014-12-30 09:06

There are many ways to migrate a database from A server to B server like Datapump, RMAN,etc. Using the combination of datapump and GoldenGate to migrate your database on cross-platform will minimize your down-time to even three minutes.

This method can be used for any size database from MB to TB level. Here is a simple sample to demonstrate this idea.

The prerequisites I assume that the GoldenGate has been configured in the source database and target database. To simulate the OLTP database, in my source database “SOURCE” there is a job will keep inserting a record into the table HOWIE.TEST as shown below.

CREATE PROCEDURE howie.insert_test
IS
BEGIN
   insert into test values(test_seq.nextval,sysdate);
   commit;
END;
/ 

SQL> SELECT * FROM HOWIE.TEST ORDER BY ID;

        ID MOD_DATE
---------- -------------------
         1 12/13/2014 21:19:17
         2 12/13/2014 21:24:03
         3 12/13/2014 21:31:11
         
		 .....................
           
        21 12/15/2014 19:14:25
        22 12/15/2014 19:15:25
        23 12/15/2014 19:16:25

23 rows selected.

2nd step, you need to start capture process on the source database and stop replicate process on the target database

SOURCE:

GGSCI (11gGG1) 4> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
EXTRACT     RUNNING     EXT1        44:56:46      00:00:01

TARGET:

GGSCI (11gGG2) 6> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     STOPPED
REPLICAT    STOPPED     REP1        00:00:00      00:00:53

3rd step, export the source database using datapump with flashback_scn

SQL> select current_scn from v$database;

CURRENT_SCN
-----------
     284867
	 
[ggadmin@11gGG1 11.2.0]$ expdp directory=DATA_PUMP_DIR dumpfile=source.dmp logfile=source.log schemas=HOWIE flashback_scn=284867




4th step, transferred the dumpfile to the target server

[ggadmin@11gGG1 11.2.0]$ scp /u01/app/oracle/admin/SOURCE/dpdump/source.dmp 11gGG2:/u01/app/oracle/admin/TARGET/dpdump/

5th step, import the dumpfile into the target database.

[ggadmin@11gGG2 11.2.0]$ impdp directory=DATA_PUMP_DIR dumpfile=source.dmp logfile=source.log schemas=HOWIE

6th step, verify the data in the target database

SQL> SELECT * FROM HOWIE.TEST ORDER BY ID;

        ID MOD_DATE
---------- -------------------
         1 12/13/2014 21:19:17
         2 12/13/2014 21:24:03
         3 12/13/2014 21:31:11

		 ...............

    	21 12/15/2014 19:14:25
        22 12/15/2014 19:15:25
        23 12/15/2014 19:16:25

23 rows selected.

7th step, start replicate process on the target database using ATCSN

GGSCI (11gGG2) 8> start rep rep1 atcsn 284867

Sending START request to MANAGER ...

8th step, confirm the data has been synced

SQL> SELECT * FROM HOWIE.TEST ORDER BY ID;

        ID MOD_DATE
---------- -------------------
         1 12/13/2014 21:19:17
         2 12/13/2014 21:24:03
         3 12/13/2014 21:31:11
         4 12/13/2014 21:44:33
         5 12/13/2014 21:45:33
         6 12/13/2014 21:46:33
         7 12/13/2014 21:47:33

		 ...............

        60 12/15/2014 19:53:33
        61 12/15/2014 19:54:33
        62 12/15/2014 19:55:33
        63 12/15/2014 19:56:33

63 rows selected.

Action plan Summary

Step Source Target Source DB (11g) Target DB (11g) 1 Configure goldengate for capture processes. Configure goldengate for Replicate processes. 2 Start capture processes. Don’t start replicate now. 3 start export from the source database (Mark SCN when export started.) 4 Export completed. start SCP of dumpfile to target server. 5 SCP completed. Start Import on Target database using dumpfiles. 6 Import Finished. 7 Start replicat using atcsn 8 Replicate applied all changes 9 when lag is zero for capture,stop capture wait till replicate apply all changes , lag should be zero for replicate. After this stop replicate. 10 Redirect db connection point to target db. Redirect db connection point to target db.
Categories: DBA Blogs

Merging Apps Patches in Oracle EBS R12.2

Pythian Group - Tue, 2014-12-30 09:04

It’s public knowledge that the traditional patching tool in Oracle EBS “adpatch” is replaced with “adop” utility. It’s also known that adop utility automatically merges patches when more than one patch is specified in the command line arguments. So whats the need for blog post on merging patches when its taken care automatically?

This blog is for people who like to dig little deep into EBS to shave off some downtime during the upgrades. We save some downtime if we merge the patches ahead of time instead of letting adop do it during the upgrade window. This is especially true when you are applying big patches like 12.2.4.

Merging patches is done using same utility as in earlier versions called “admrgpch”. Except that there are few extra steps needed after merging the patches.

In EBS 12.2 after merging the patches using admrgpch, we need to copy the actual unzipped patches that we merged also into the destination  directory. This is required as adop utility seems to be looking for these patches during the prepare phase. If you don’t copy the unzipped patch directories, you can still apply the patches. But when you run adop=prepare during next patching cycle,  it will fail as it will look for actual patch directories inside the merged patch dir.

Here is how a sample merging procedure will look like in EBS R12.2

# merge patches 111111 & 222222
$ pwd
  /u01/EBS/fs_ne/EBSapps/patch
$ ls
  111111 222222 
$ mkdir dest
$ admrgpch -s /u01/EBS/fs_ne/EBSapps/patch -d /u01/EBS/fs_ne/EBSapps/patch/dest
$ cd dest
$ pwd
  /u01/R122_EBS/fs_ne/EBSapps/patch/dest
$ ls
  fnd u_merged.drv 

# After admrgpch is finished, we need to copy patch directories into the dest dir

$ cd ..
$ mv 111111 /u01/EBS/fs_ne/EBSapps/patch/dest
$ mv 222222 /u01/EBS/fs_ne/EBSapps/patch/dest
$ cd /u01/EBS/fs_ne/EBSapps/patch/dest
$ ls
  111111 222222 fnd u_merged.drv

# Now you can the patches using adop=apply
Categories: DBA Blogs

Log Buffer #403, A Carnival of the Vanities for DBAs

Pythian Group - Tue, 2014-12-30 09:02

As the 2014 is drawing to its end, the Log Buffer edition is looking back proudly at some of the blog posts from this week looking at whats happening in around database field.

Oracle:

Fusion Applications provides web services that allow external systems to integrate with Fusion Applications.

OEM 12c Release 4 has several new EM CLI verbs, including manage_agent_partnership.

To reflect the Oracle Retail enterprise applications newest code base, the 14.1 release of the Oracle Retail application enterprise includes new End User documents, considerable updates to existing End User documentation sets, and a wide range of new White Papers and Technical Papers.

If you programmatically change data in your Oracle MAF application then you need to ensure the UI reflects those data changes.

Configuring MDS Customisation Layer and Layer Value Combination in ADF.

SQL Server:

15 Quick Short Interview Questions Useful When Hiring SQL Developers.

Business Intelligence architect, Analysis Services Maestro, and author Bill Pearson exposes the DAX SUM() and SUMX() functions, comparing and contrasting the two.

SQL Server Data Aggregation for Data with Different Sampling Rates.

SSRS continues to use SET FMTONLY ON even though it has many problems. How can we cope?

When a hospital’s mission-critical database fails at Christmas, disaster for the hospital – and its hapless DBA – seems certain.

MySQL:

Does your dataset consist of InnoDB tables with large BLOB data such that the data is stored in external BLOB pages?

InnoDB crash recovery speed in MySQL 5.6.

Somebody wanted to know how to find any non-unique indexes in information_schema of the MySQL.

What is a data type?

File carving methods for the MySQL DBA.

Categories: DBA Blogs

Can A Background Process Impact A Foreground Process And Its Database Time?

This page has been permanently moved. Please CLICK HERE to be redirected.

Thanks, Craig.Can A Background Process Impact A Foreground Process And Its Database Time?
Have you ever heard someone say, "Background processes do not impact foreground processes because they run in the background and in parallel with foreground processes." I've heard this hundreds of times!

While doing some performance research I came across a great example of how an Oracle Database background process can directly and significantly impact a foreground process.

The above quote represents a masterfully constructed lie; it contains both a lie and a truth. The mix of a truth and a lie make understanding the reality of the situation difficult. In this post, I'll explain the truth, delve into the lie and relate it all to foreground process database time.

By the way, I am in no way saying there is something wrong with or incorrect about DB Time. I want to ensure this is clear from the very beginning of this post.

Just so there is no confusion, an Oracle foreground process is sometimes also called a server process or a shadow process. These can terms can be used interchangeably in this post.

The Truth
Clearly background and foreground processes operate in parallel. I don't think any DBA would deny this. As I frequently say, "serialization is death and parallelism is life!" A simple "ps" command will visually show both Oracle background and foreground processes at work. But this in no way implies they do not impact each other's activity and performance.

In fact, we hope they do impact each other! Can you imagine what performance would be with the background processes NOT running in parallel?! What a performance nightmare that would be. But this where the "no impact" lie lives.

The Lie
Most senior DBAs can point to a specific situation where Oracle cache buffer chain latch contention affected multiple foreground sessions. In this situation, foreground sessions were franticly trying to acquire a popular cache buffer chain latch. But this is a foreground session versus foreground session situation. While this is example is important, this post is about when a background process impacts a foreground process.

Have you every committed a transaction and it hangs while the foreground process is waiting on "log file switch (checkpoint incomplete)" or even worse "log file switch (archiving needed)" event? All the foreground process knows is that its statement can't finish because a required log switch has not occurred because a checkpoint is incomplete. What the server process does not know is the checkpoint (CKPT), the database writer (DBWR) and the log writer (LGWR) background processes are involved. There is a good chance the database writer is frantically writing dirty buffers to the database (dbf) files so the LGWR can safely overwrite the associated redo in the next online redo log.

For example, if a server process issued a commit during the checkpoint, it will wait until the checkpoint is complete and the log writer has switched and can write into the next redo log. So, while the log writer background processes is probably waiting on "log file parallel write" and the database writer is burning CPU and waiting on "db file parallel write", the foreground processes are effectively hung.

This is a classic example of how a background process can impact the performance of a foreground process.

A Demonstration Of The Lie
Here's a quick demonstration of the above situation. On an existing database in my lab, I created two 4MB redo logs and dropped all the other redo logs. I started a DML intensive workload. According to the alert.log file, the redo logs where switching every couple of seconds! Take a look at this:
$ tail -f /home/oracle/base/diag/rdbms/prod30/prod30/trace/alert*log
Thread 1 cannot allocate new log, sequence 2365
Checkpoint not complete
Current log# 4 seq# 2364 mem# 0: /home/oradata/prod30/redoA1.log
Mon Dec 29 11:02:09 2014
Thread 1 advanced to log sequence 2365 (LGWR switch)
Current log# 5 seq# 2365 mem# 0: /home/oradata/prod30/redoA2.log
Thread 1 cannot allocate new log, sequence 2366
Checkpoint not complete
Current log# 5 seq# 2365 mem# 0: /home/oradata/prod30/redoA2.log
Thread 1 advanced to log sequence 2366 (LGWR switch)
Current log# 4 seq# 2366 mem# 0: /home/oradata/prod30/redoA1.log
Thread 1 cannot allocate new log, sequence 2367
Checkpoint not complete
Current log# 4 seq# 2366 mem# 0: /home/oradata/prod30/redoA1.log
Thread 1 advanced to log sequence 2367 (LGWR switch)
Current log# 5 seq# 2367 mem# 0: /home/oradata/prod30/redoA2.log
Thread 1 cannot allocate new log, sequence 2368
Checkpoint not complete
Current log# 5 seq# 2367 mem# 0: /home/oradata/prod30/redoA2.log
Mon Dec 29 11:02:20 2014

Obviously not what you want to see on a production Oracle system! (But my guess many of you have.)

Using my OSM realtime session sampler tool (rss.sql - related blog posting HERE) I sampled the log writer every half a second. (There is only one log writer background process because this is an Oracle 11g database, not an Oracle Database 12c system.) If the log writer session showed up in v$session as an active session, it would be picked up by rss.sql.  Both "ON CPU" and "WAIT" states are collected. Here is a sample of the output.


It's very obvious the log writer is doing some writing. But we can't tell from the above output if the process is impacting other sessions. It would have also been very interesting to sample the database writer also, but I didn't do that. To determine if the background processes are impacting other sessions, I needed to find a foreground session that was doing some commits. I noticed that session 133, a foreground process was busy doing some DML and committing as it processed its work. Just as with the log writer background process, I sampled this foreground process once every 0.5 second. Here's a sample of the output.


Wow. The foreground process is waiting a lot for the current checkpoint to be completed! So... this means the foreground process is being effectively halted until the background processes involved with the checkpoint have finished their work.
This is a great example of how Oracle background processes can impact the performance of an Oracle foreground process.

But let's be clear. Without the background processes, performance would be even worse. Why? Because all that work done in parallel and in the background would have to be done by each foreground process AND all that work would have to be closely controlled and coordinated. And that, would be a performance nightmare!
DB Time Impact On The Foreground Process
Just for the fun of it, I wrote a script to investigate DB Time, CPU consumption, non-idle wait time and the wait time for the "log file switch wait (checkpoint incomplete)" wait event for the foreground process mentioned above (session 133). The script simply gathers some session details, sleeps for 120 seconds, again gathers some session details, calculates the differences and displays the results. You can download the script HERE. Below is the output for the foreground process, session 133.
SQL> @ckpttest.sql 133

Table dropped.

Table created.

PL/SQL procedure successfully completed.

CPU_S_DELTA NIW_S_DELTA DB_TIME_S_DELTA CHECK_IMPL_WAIT_S
----------- ----------- --------------- -----------------
2.362 117.71 119.973692 112.42

1 row selected.

Here is a quick description of the output columns.

  • CPU_S_DELTA is the CPU seconds consumed by session 133, which is the time model statistic DB CPU.
  • NIW_S_DELTA is the non-idle wait time for session 133, in seconds.
  • DB_TIME_S_DELTA is the DB Time statistic for session 133, which is the time model statistic DB Time.
  • CHECK_IMPL_WAIT_S is the wait time only for event "log file switch (checkpoint incomplete)" for session 133, in seconds.

Does the time fit together as we expect? The "log file switch..." wait time is part of the non-idle wait time. The DB Time total is very close to the CPU time plus the non-idle wait time. Everything seems to add up nicely.

To summarize: Oracle background processes directly impacted the database time for a foreground process.

In Conclusion...
First, for sure Oracle foreground and background processes impact each other...by design for increased performance. Sometimes on real production Oracle Database systems things get messy and work that we hoped would be done in parallel must become momentarily serialized. The log file switch example above, is an example of this.

Second, the next time someone tells you that an Oracle background process does not impact the performance of a foreground process, ask them if they have experienced a "log file switch checkpoint incomplete" situation. Pause until they say, "Yes." Then just look at them and don't say a word. After a few seconds you may see a "oh... I get it." look on their face. But if not, simply point them to this post.

Thanks for reading and enjoy your work!

Craig.




Categories: DBA Blogs