Author: Paul Moen

Over-the-Top Tales from the Trenches: Bringing order to the chaos of every day DBA life – So you have got your nice MySQL Master-Slave replication pair setup. Everything is sweet, then the master dies/restarts or you have a slightly extended network outage. You log into the machine and check out why the slave threw an error or if your monitoring is slow, why the slave is lagging by 2 hours. You run SHOW SLAVE STATUS\G

Do you need to know why the standby periodically lags during the day, if the standby redo apply rate lag during the day and when, or how fast is the current archived redo log being applied? If you answered yes to any of these look no further. The following SQL script will help you answer these questions.

So Management chooses to review the documentation generated from your database failover test and see that the time to switchover is slow and that there is still a possibility of losing some data. Enter standby redo logs. Rather than regurgitate the documentation, I have attached the links to the specific parts of the documentation. Since I’m focused on providing ways for you to “get the job done”, I have also included some SQL that uses dynamic SQL to generate a series of ALTER DATABASE ADD STANDBY LOGFILE commands to run on both the primary and standby databases. Have a look.

So you have followed the recipes to create a standby database, setup the database to switch logs automatically, and now, as with any good database, the space required to support the application grows and grows and grows. Eventually, using your monitoring software (in my case, Pythian’s avail or dailies), you get an alert suggesting that you will need to add additional space. In this case you are going to add a datafile to a tablespace, or add a brand new tablespace.

I was reading a couple of items on gapingvoid which inspired me to use our blog for a conversation with our customers (and with potential Pythian DBAs) about what it’s like to work with Pythian. What exactly does an on-site DBA get from working with Pythian Remote DBAs? Find out here.

So you have swallowed the standby bait. You have used RMAN duplicate to create a sparkling new standby and things are looking rosy. Then, when you check the lag between the primary and standby (in MAXIMUM PERFORMANCE mode), you discover the standby is miles behind. Oracle (at least from 9iG) rides to the rescue again. The parameter ARCHIVE_LAG_TARGET tells Oracle to make sure to switch a log every n seconds.

Over-the-Top Tales from the Trenches: Bringing order to the chaos of every day DBA life. Ever heard this during a recovery of a downed database? The poor thing has either a minor scratch, requires immediate attention, or is dead on arrival (DOA).

Over-the-Top Tales from the Trenches: Bringing order to the chaos of every day DBA life. My aim in any posting is to make your job as a DBA in Oracle, SQLserver and MySQL easier. Ever wondered how to get a quick count of the number of connections per hostname from MySQL? Welcome to the arcane (but extremely powerful) world of the MySQL command line pager.

Filesorts and temp tables are a necessary evil in MySQL, used when MySQL must sort the data before returning the output to the user. They are the most common issue with slow queries in MySQL, the main reason being that if the output is too large, you can kiss goodbye in-memory performance, and say hello to disk access.

Over-the-Top Tales from the Trenches:: Bringing order to the chaos of everyday DBA life.

Do you want to know a secret? Understanding it will prevent pain and gnashing of teeth, and also leave your face comfortably free of egg. Here it is: Some Oracle functions assume there are 31 days for each month of the year! The moral of the story: be careful when using MONTHS_BETWEEN for fractional dates.