DBA Blogs

One of the ways to migrate a database to ASM is to use the Rman “Backup as Copy” command to create a database copy into ASM storage and then switch the database to the copy. This technique can be used,... alejandro.vargas

Oracle Input Output Numbers, ORION, provides a simple way to check storage performance before the Oracle Database is created, that gives us a clean storage performance report that can be compared with the awr IO statistics once the database is... alejandro.vargas

On this Lab we create the ASM instance on a single instance environment, and create ASM diskgroups in several different ways using different kind of devices. Note that these exercises provide some degree of confidence on managing devices but are... alejandro.vargas

Using OLE DB to get SQL Server to connect to Oracle servers can be done quite easily, but there are a few little tricks you should know to make it go smoothly. Once it’s working it seems to work quite well. I hope this blog post will save you a few headaches.

Recently a client asked me to create a simple SSIS package that would connect to Oracle, pick up some data with queries they provided, import it to SQL Server, and eventually export the data as flat, delimited text files.

With SSIS you can use the OLE DB provider that Oracle provides. If your SQL Server is 32-bit, you can install the 32-bit Oracle client and stop there.

If it’s 64-bit, there are a couple different ways to get the Oracle providers working. The method I’ve had the most success with, is to install both the 32- and 64-bit Oracle clients (in separate directories). I’m not sure why you have to have both clients. The only explanation I’ve seen is that part of SQL Server 64 is still 32-bit. I’m not sure if this is true, but if you look at the shortcuts, SSMS and Visual Studio’s EXEs reside in the 32-bit Program Files folder:

SQL Server Management Studio and DTEXECUI.exe can be found in: C:\Program Files (x86)\Microsoft SQL Server\90\Tools\binn\VSShell\Common7\IDE\

Visual Studio: C:\Program Files (x86)\Microsoft Visual Studio 8\Common7\IDE\Devenv.exe

If you look at SQL Server and SQL Agent, they both reside in the 64-bit folder: C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn\

Once both clients were installed, I was able to access the provider in SSIS, and also successfully schedule and run package. The trouble started when the requirements for the project changed, and we decided to use SQL in a stored procedure with a linked server instead of SSIS. I thought this would be very simple to get working, since I already had the providers installed and working in SSIS. I was wrong.

We created the linked server and used OPENROWSET to pass in the queries that the client had written and tested in SQLPlus running against their test system. Now it was time to try out our linked server. I passed it a simple query and instead of seeing some records, got this error:

Msg 7399, Level 16, State 1, Line 1
The OLE DB provider “OraOLEDB.Oracle” for linked server [Oracle Server Name Withheld] reported an error. Access denied.

Msg 7301, Level 16, State 2, Line 1
Cannot obtain the required interface (”IID_IDBCreateCommand”) from OLE DB provider “OraOLEDB.Oracle” for linked server [Oracle Server Name Witheld]

Naturally, I thought something had changed with our credentials used to access Oracle, so we tested the query and credentials in SQLPlus, which worked. Thinking something had gotten messed up on the SQL Server, I decided to check the providers, and ran the query from SSIS against the provider/Oracle data source. This worked.

We tried creating the linked servers with different settings, and even tried creating them on different servers. Nothing worked.

At this point, I was very frustrated, and thinking about how best to do what we wanted in SSIS. I did some research on the web and eventually stumbled on OPENQUERY - Access denied which suggested the “Allow InProcess” option be enabled on the provider. I enabled this option (instructions below), closed all open query windows, opened a new one and then re-tested my simple/test query using

OPENROWSET

and everything worked like a charm.

Happy cross-platform querying.
Chris.

Here are the step by step instructions to enable this:

1. Expand Linked Servers | Providers.
2. Right click on the Ora provider and go to Properties.
3. Find the “Allow InProcess” option and tick it.
4. Then make a new connection window and run your query (I had to close my open connection windows).

This post from Patrick is very useful when trying to identify the column ID of the tabular form. But when column order has been moved up/down or some are marked not shown, the column ID might be difficult to determine.

This simple technique of using Javascript event on the Element Attributes of the needed columns can be used to quickly identify the column ID.

onmouseover="alert(this.id);"

You can definitely choose a different event that fits your situation.

See sample here.

Welcome to the 152nd edition of Log Buffer, the weekly review of database blogs.

PostgreSQL

Courtesy the United States PostgreSQL Association, the big news: PostgreSQL 8.4 Released!.

Josh Berkus writes, “Now that PostgreSQL 8.4 is out, I thought I’d write a little about my favorite 8.4 feature. As Mr. Performance Whack-a-Mole, what makes me happy about 8.4 is the ability to whack moles faster … which is why I’m very fond of pg_stat_statements.”

On ad’s corner, Andreas Scherbaum says, “Up to PostgreSQL 8.3 it was only possible to grant (and revoke) permissions on the entire table. If column level permissions were needed, a workaround like a view solved (more or less) the problem . . .  This . . . is uneloquent, error prone and does not scale well.  . . .  PostgreSQL 8.4 solves the problem with a shiny new feature: column level permissions.”

David Fetter looks into WITH (so much drama in the CTE): ” By now, you’ve probably seen that PostgreSQL 8.4 can produce Mandelbrot sets . . . but what are Common Table Expressions really about? [They] are just views you create at run time. Oh, and they can refer to themselves. That latter capability is powerful and flexible (read confusing), so I’ll go over that one later. Run-time views are pretty handy, though.”

Andrew’s PostgreSQL blog introduces parallel pg_restore for PostgreSQL 8.4: “I try to complete at least one significant feature item per PostgreSQL release. This time the feature is making pg_restore run in parallel. This is quite important for many users, particularly some large enterprise users.”

SQL Server

Eric Johnson introduces SSIS 2008 and the new lookup: “SSIS is a pretty useful tool for designing ETL processes. One of the transformations I was disappointed with in 2005 was the lookup. I found it a little better than the lookup functionality in DTS, but not too much better. Well, Microsoft must have read my mind because SQL Server 2008 has a new shiny Lookup transformation that is, in my opinion, much improved.”

Kimberly L. Tripp was thinking about looking for security vulnerabilities in database code. “I’ve always been concerned with security and I’ve always stressed the importance of auditing the REAL user context not just the current user . . .  So, I generally try to avoid using dynamic string execution and if necessary create well tested/protected parameters . . .  Having said that, what if I’m looking at a database for the first time . . .  I’ve come up with a quick query… And, while it’s not going to “solve” your problem . . . or even truly verify if you’re vulnerable, it gives you a ‘quick list’ of where you should look first! ”

Adam Machanic exposed the hidden costs of INSERT EXEC, beginning, “INSERT and EXEC: relational brothers in arms, helping you get your data and then put it somewhere. But like all brothers, their relationship has its ups and downs and sometimes you need to look just below the surface to see the real issues. In this post I will cover hidden penalties that a decision–seemingly purely aesthetic–can bring to the fore.”

Martin Bell offered the reminder, after disabling TDE you still requires the certificate to restore the database.

Data Management has a first-rate HOWTO on dynamic column names and fields in SSRS. “I had no choice but to do work with creating dynamic column headings and dynamically determine what field in my dataset should go where in a report today. Sense [sic] this is the second time I’ve gone through this exercise and knowing the lack of information out there on really how to do it, I thought it deserves a blog entry.  . . .  So here is the basis of the requirements. You have a query that uses PIVOT but thrown into the mix is the need for dynamic columns in the PIVOT.”

MySQL

Here on the Pythian Blog, Sheeri Cabral expressed some concerns and what does not work in XtraDB backup.

On xaprb, Baron Schwartz has a review of MySQL Administrator’s Bible. “I think that MySQL Administrator’s Bible is a very good complement to High Performance MySQL, 2nd Edition. The former is useful to people who have varying levels of knowledge, while the latter assumes a lot of experience and doesn’t cover introductory material much.  . . .  So if you’re new to databases, or if you’re new to MySQL, you might do well to start with Sheeri’s book . . . ”

Over on the MySQL Performance Blog, Baron looked into gathering queries from a server with Maatkit and tcpdump: “For the last couple of months, we’ve been quietly developing a MySQL protocol parser for Maatkit. It isn’t an implementation of the protocol: it’s an observer of the protocol. This lets us gather queries from servers that don’t have a slow query log enabled, at very high time resolution.”

Ronald Bradford looked at verifying MySQL replication in action, with “ . . . a very simple test to show MySQL replication in action and to also better understand the basics of MySQL Replication. With a configured MySQL environment we can run the following test on your MySQL master, and monitor the MySQL slave.”

The MySQL Workbench Team Blog says, Time To Upgrade - MySQL Workbench 5.1 Is Here on Win, Mac & Linux. Perty pictures follow.

Oracle

H. Tonguç Yilmaz asserted, Instrumented Code is Better: An Example on How to Generate Session Level ASH Reports. ” After 10g if you have its license ASH based performance monitoring is an alternative to the traditional SQL Tracing in my opinion. You may either use EM to generate ASH reports or directly query ASH dictionary. The handy additional hint here is that you can set filters to ASH data in order to focus on a specific session, the filter options at ASH report screen at EM can be used for this purpose or below is an example to monitor the waits from v$active_session_history view.”

Doug Burns responded with his item on session-level ASH reports: “I think [Tonguç's] post is really showing two different things, one more successfully than the other.”

Randolf Geist reports a Dynamic sampling and set current_schema anomaly: “If I’m now supposed to analyse a rather complex SQL with dozens of table and/or view references I like to use the ALTER SESSION SET CURRENT_SCHEMA command . . .  This basically works just fine, but I recently have come across an interesting anomaly in 10.2.0.4: If the optimizer uses dynamic sampling as part of the parsing then it doesn’t do so anymore if the CURRENT_SCHEMA has been altered to a non-default schema.”

On the Oramoss Blog, Jeff Moss looks at the case of no pruning for MIN/MAX of partition key column: “Recently, I wanted to work out the maximum value of a column on a partitioned table. The column I wanted the maximum value for, happened to be the (single and only) partition key column. The table in question was range partitioned on this single key column, into monthly partitions for 2009, with data in all the partitions behind the current date, i.e. January through mid June were populated. There were no indexes on the table.” Jeff and his readers examine the evidence and discuss some workarounds to the problem.

Oracle, MySQL, PostgreSQL, SQL Server. How’bout . . . 

NoSQL

(No SQL?!? Edwards, you’re mad!) Well, it’s not me. Here’s Curt Monash on NoSQL: “Eric Lai emailed today to ask what I thought about the NoSQL folks, and especially whether I thought their ideas were useful for enterprises in general, as opposed to just Web 2.0 companies. That was the first I heard of NoSQL, which seems to be a community discussing SQL alternatives popular among the cloud/big-web-company set, such as BigTable, Hadoop, Cassandra and so on.”

PostgreSQL stuff also had some thoughts on those who say No to SQL. “Every time I see something or hear something like this I sigh a little bit. Not only when it’s related to SQL but in the world of computer professionals in general. ‘The right tool for the job’ seems to be a hard concept to understand sometimes. I wonder why?”

Back to Oracle for a moment. Tanel Poder has a secret preview of Oracle 12g CBO leaked from Oracle labs. Would you like fries with your cost-based optimizer?

That’s all for now. See you in a week’s time!

ASMLib provide an easy way to present storage to ASM on Linux, it takes care of device labeling and improves the performance of open-close calls to the devices. Is Oracle Best Practice for managing disks on Linux. Lab2-Install-and-Configure-ASMLib.pdf The following... alejandro.vargas

Recently, I tested a switchover on Oracle 11g SE1.

As you know, Oracle Database Standard Edition One—as well as Standard Edition—does not have the Data Guard feature. Therefore, I had to do everything manually.

The whole process took less than 15 minutes. This includes less than five minutes of full downtime to restart the database in READ-ONLY mode, and less than 10 minutes of READ-ONLY downtime.

Of course, it depends on the size of Redo logs and the network speed to move Redo logs from the primary server to standby.

Here is what I had. The primary database and one physical standby database:

• OS - SUSE Linux ES10 (SP2) x86_64
• Oracle - Release 11.1.0.7.0 64bit SE1

First of all, I switched the standby database to the primary role.

Step 1. Shutdown the primary database

oracle@ora1 /u01/app/oracle/testdb/oradata> sqlplus / as sysdba

SQL*Plus: Release 11.1.0.7.0 - Production

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

Connected to:
Oracle Database 11g Release 11.1.0.7.0 - 64bit Production

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

Step 2. Make a copy of the control file, the spfile, and the redo logs:

SQL> !cp control01.ctl copy/control01.ctl.primary
SQL> !cp /u01/app/oracle/product/11.1.0/db_1/dbs/spfiletestdb.ora copy/spfiletestdb.ora.primary
SQL> !cp *.log copy/

Step 3. Startup the primary database in READ-ONLY mode:

SQL> startup mount
ORACLE instance started.

Total System Global Area 1269366784 bytes
Fixed Size                  2159312 bytes
Variable Size             754978096 bytes
Database Buffers          503316480 bytes
Redo Buffers                8912896 bytes
Database mounted.
SQL> alter database open read only;

Database altered.
SQL> exit
Disconnected from Oracle Database 11g Release 11.1.0.7.0 - 64bit Production

Step 4. Copy all needed archive logs, the copy of the control file, the spfile, and the redo logs to the standby server ora2:

oracle@ora1 /u01/app/oracle/testdb/oradata> scp ../archivelogs/1_152_333215132.dbf oracle@ora2:/u01/app/oracle/testdb/archivelogs/
oracle@ora1 /u01/app/oracle/testdb/oradata> scp copy/control01.ctl.primary oracle@ora2:/u01/app/oracle/testdb/oradata/copy/
oracle@ora1 /u01/app/oracle/testdb/oradata> scp copy/spfilepb.ora.primary oracle@ora2:/u01/app/oracle/testdb/oradata/copy/
oracle@ora1 /u01/app/oracle/testdb/oradata> scp copy/redo01.log oracle@ora2:/u01/app/oracle/testdb/oradata/
oracle@ora1 /u01/app/oracle/testdb/oradata> scp copy/redo02.log oracle@ora2:/u01/app/oracle/testdb/oradata/
oracle@ora1 /u01/app/oracle/testdb/oradata> scp copy/redo03.log oracle@ora2:/u01/app/oracle/testdb/oradata/

Step 5. Apply all needed archive logs on the standby database:

oracle@ora2 /u01/app/oracle/testdb/oradata> sqlplus / as sysdba

SQL*Plus: Release 11.1.0.7.0 - Production

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

Connected to:
Oracle Database 11g Release 11.1.0.7.0 - 64bit Production

SQL> recover standby database until cancel;
ORA-00279: change 2244877 generated at 10/07/2008 14:40:18 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/testdb/archivelogs/1_152_333215132.dbf
ORA-00280: change 2244877 for thread 1 is in sequence #152

Specify log: {=suggested | filename | AUTO | CANCEL}

ORA-00279: change 2245162 generated at 10/07/2008 14:53:27 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/testdb/archivelogs/1_153_333215132.dbf
ORA-00280: change 2245162 for thread 1 is in sequence #153
ORA-00278: log file '/u01/app/oracle/testdb/archivelogs/1_152_333215132.dbf' no
longer needed for this recovery

Specify log: {=suggested | filename | AUTO | CANCEL}
CANCEL
Media recovery cancelled.

Step 6. Shutdown the standby database:

SQL> shutdown
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.

Step 7. Make a copy of the controlfile and the spfile.

To make it easier I just switched spfiles.

SQL> !cp control01.ctl copy/control01.ctl.stndby
SQL> !scp copy/control01.ctl.stndby oracle@ora1:/u01/app/oracle/testdb/oradata/copy/
SQL> !cp /u01/app/oracle/product/11.1.0/db_1/dbs/spfiletestdb.ora copy/spfiletestdb.ora.stndby
SQL> !scp copy/spfilepb.ora.stndby oracle@ora1:/u01/app/oracle/testdb/oradata/copy/

Step 8. Replace the standby controlfile and spfile with the copy of the primary control file and spfile:

SQL> !rm control*.ctl
SQL> !rm /u01/app/oracle/product/11.1.0/db_1/dbs/spfiletestdb.ora
SQL> !cp copy/spfiletestdb.ora.primary /u01/app/oracle/product/11.1.0/db_1/dbs/spfiletestdb.ora
SQL> !cp copy/control01.ctl.primary control01.ctl
SQL> !cp copy/control01.ctl.primary control02.ctl
SQL> !cp copy/control01.ctl.primary control03.ctl

Step 9. Switch the standby database to the primary role:

SQL> startup mount
ORACLE instance started.

Total System Global Area 1269366784 bytes
Fixed Size                  2159312 bytes
Variable Size             754978096 bytes
Database Buffers          503316480 bytes
Redo Buffers                8912896 bytes
Database mounted.
SQL> recover database;
Media recovery complete.
SQL> alter database tempfile '/u01/app/oracle/testdb/oradata/temp01.dbf' drop;

Database altered.

SQL> alter database open;

Database altered.

SQL> alter tablespace temp add tempfile '/u01/app/oracle/testdb/oradata/temp01.dbf' size 100m reuse autoextend on next 100m maxsize 2048m;

Tablespace altered.

SQL> select CONTROLFILE_TYPE, OPEN_MODE, DATABASE_ROLE, STANDBY_BECAME_PRIMARY_SCN from v$database;

CONTROL OPEN_MODE  DATABASE_ROLE    STANDBY_BECAME_PRIMARY_SCN
------- ---------- ---------------- --------------------------
CURRENT READ WRITE PRIMARY                             2244877

SQL> exit
Disconnected from Oracle Database 11g Release 11.1.0.7.0 - 64bit Production

And finally, I switched the old primary database to the standby role.

Step 10. Shutdown the old primary database:

oracle@ora1 /u01/app/oracle/testdb/oradata> sqlplus / as sysdba

SQL*Plus: Release 11.1.0.7.0 - Production

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

Connected to:
Oracle Database 11g Release 11.1.0.7.0 - 64bit Production

SQL> shutdown
Database closed.
Database dismounted.
ORACLE instance shut down.

Step 11. Replace the old primary control file and spfile with the copy of the standby control file and spfile

SQL> !rm control*.ctl
SQL> !rm /u01/app/oracle/product/11.1.0/db_1/dbs/spfiletestdb.ora
SQL> !rm temp01.dbf
SQL> !rm *.log
SQL> !cp copy/spfiletestdb.ora.stndby /u01/app/oracle/product/11.1.0/db_1/dbs/spfiletestdb.ora
SQL> !cp copy/control01.ctl.stndby control01.ctl
SQL> !cp copy/control01.ctl.stndby control02.ctl
SQL> !cp copy/control01.ctl.stndby control03.ctl

Step 12. Switch the old primary database to the standby role:

SQL> startup mount
ORACLE instance started.

Total System Global Area 1269366784 bytes
Fixed Size                  2159312 bytes
Variable Size             754978096 bytes
Database Buffers          503316480 bytes
Redo Buffers                8912896 bytes
Database mounted.

SQL> select CONTROLFILE_TYPE, OPEN_MODE, DATABASE_ROLE from v$database;

CONTROL OPEN_MODE  DATABASE_ROLE
------- ---------- ----------------
STANDBY MOUNTED    PHYSICAL STANDBY

SQL> exit
Disconnected from Oracle Database 11g Release 11.1.0.7.0 - 64bit Production

So you can see that Oracle 11g SE1 (SE) switchover of primary database and one physical database located on different servers is a very simple process. But if you have the physical standby on the same server and use OMF, you should consider two things.

1. How to change the path to the datafiles and redo logs in the control files;

   Solution:In Step 9, after the database is mounted and before it is opened, all datafiles and redo logs should be renamed using the ALTER DATABASE RENAME FILE command.

2. How not to remove the old datafiles. OMF will remove the old files after the RENAME command.

   Workaround: Move the files to a temporary directory and move them back after the RENAME command. The old primary database should be down.

The same process can be done with a RAC database.

And do not forget to make a database backup in case something goes wrong.

A short time ago I posted how I was Using XtraDB Backup to backup InnoDB. Overall, the blog post was positive, but experiences that others have had (and commented to on that blog post) have made me want to put out another short article about using XtraDB backup.

The first few points remain the same — the backup process is stable, we were able to use the binaries without compiling, and using Innobackupex as the wrapper script, analogous to Innobackup.pl.

However, we did figure out why Xtrabackup had to be run as the mysql user:

Xtrabackup writes to the data dictionary file (ibdata1, for example). We have not examined if it also writes to the data and index files (still ibdata1 by default, or the .ibd files when using innodb_file_per_table). [EDIT: The authors of Xtrabackup have commented below as to why the write occurs:

xtrabackup is kind of very small and restricted instance of InnoDB, and reuses a lot of InnoDB code.

InnoDB by default requires O_RDWR option on ibdata1 files at start, and xtrabackup therefore also did that. In the latest push to trunk it was fixed, now xtrabackup opens file with O_RDONLY flag.

When the new version is released, we will be sure to test it so that we can run the backup as a read-only user, and report back.]

On the one hand, Xtrabackup is a free tool. On the other hand, modifying InnoDB’s underlying files risks corrupting all the InnoDB tables in the system. Which is a tricky situation when it is your backup tool that might cause corruption that is beyond repair, as you do not know if you can trust your backups.

Regarding the complaint Dan R commented on the previous post that innobackupex could stream the backup to gzip, and helpfully gave the syntax. Shlomi Noach also pointed out streaming as a feature of Xtrabackup that ibbackup does not have. However, Gerry Narvaja, a co-worker noted (and commented):

I’ve been trying to install xtrabackup/innobackupex for a customer and I’m finding a few glitches, especially w/ streaming:

We use innoback(ex) wrapped in our own scripts to allow for rotation and other operations. We also use ZRM for some installations, so this would apply to integration with this tool as well. These are the glitches I found:

1. Using streaming by piping it into ‘gzip’ masks the return code from innobackupex.pl. Since gzip will almost always return 0, you can’t rely on it to determine backup success.

2. The next alternative would be to review the innobackupex.pl’s output for the OK at the end. But since it redirects the output to ’stderr’ to allow for streaming, you need to add “2> innobackupex.log” before piping and grep for the “OK” at the end.

and noted that there were some limitations:

innobackupex script is limited in the type of options you can specify compared to what the xtrabackup executable supports. I find this annoying since it limits the directories where you can have the backups, data directories and logs.

Xtrabackup doesn’t work for MySQL v4.1. In the Percona forums there was a suggestion that the 5.0 patch should work. This is true, but xtrabackup.c has other dependencies on 5.x definitions and structures I didn’t have time to review. Baron Schwartz correctly suggested that these dependencies might be trivial in a tweet directed to myself. I’ll post my findings to the Percona forums and hopefully we can soon have a patched version.

Hi All!

This year, the International Free Software Forum celebrated its 10th anniversary. It happened last week in Porto Alegre.

Pythian presented a session on Thursday called 8 Rules for Designing More Secure Applications with MySQL.

As promised, here are the slides we used on that session: 8 Simple Rules to Design Secure Apps with MySQL (PDF).

Cheers!

I’ve just recently returned from a really enjoyable trip to the USA to attend an Oracle ACE Director briefing at Redwood Shores and the ODTUG Kaleidoscope Conference in Monterey, CA. During the week, I had the opportunity to finally meet some of the other Oracle ACE Directors such as Tim Hall, Mark Rittman, Lucus Jellema, Lonneke Dikmans, [...]

After I read about Redo & Undo in Expert Oracle Database Architecture book by Kyte, He told about NOLOGGING...
Nologging doesn't mean all operations in that object will not generate redo log.
And Francisco Munoz A 's Paper told ... Nologging will generate a minimal number of redo log entries in order to protect the data dictionary.
That make my curious about nologging mode with DML. I use it on some tables and some indexes... So I should know a real thing about it by myself.
Francisco Munoz A 's Paper and Oracle Docs Idea ... INSERT/UPDATE/DELETE will generate a real redo with table/index NOLOGGING mode, except INSERT /*+APPEND+/
Begin test... NOLOGGING + DML on 11g Archivelog Mode.
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled

Script:

--new.sql
column OLD_VALUE new_value OLD_VALUE
select value OLD_VALUE
from v$mystat, v$statname
where v$mystat.statistic# = v$statname.statistic#
and v$statname.name = 'redo size';

--diff.sql
select (value - &OLD_VALUE) OLD_VALUE
from v$mystat, v$statname
where v$mystat.statistic# = v$statname.statistic#
and v$statname.name = 'redo size';

- Create TABLE -

SQL> @begin

OLD_VALUE
----------
0

SQL> create table T_NOLOG nologging as select * from all_objects;

Table created.

SQL> @diff
old 1: select (value - &OLD_VALUE) OLD_VALUE
new 1: select (value - 0) OLD_VALUE

OLD_VALUE
----------
133120

REDO SIZE=133120

SQL> @begin

OLD_VALUE
----------
133120

SQL> create table T_LOG logging as select * from all_objects;

Table created.

SQL> @diff
old 1: select (value - &OLD_VALUE) OLD_VALUE
new 1: select (value - 133120) OLD_VALUE

OLD_VALUE
----------
8748596

REDO SIZE=8748596

Create table with NOLOGGING... not generate redo log (just generate redo log for data dictionary)After create table... It's time to test with DML:
- DELETE -

SQL> @begin

OLD_VALUE
----------
8881716

SQL> DELETE FROM T_NOLOG NOLOGGING;

70999 rows deleted.

SQL> @diff
old 1: select (value - &OLD_VALUE) OLD_VALUE
new 1: select (value - 8881716) OLD_VALUE

OLD_VALUE
----------
27076168

REDO SIZE=27076168

SQL> @begin

OLD_VALUE
----------
35958052

SQL> DELETE FROM T_LOG;

71000 rows deleted.

SQL> @diff
old 1: select (value - &OLD_VALUE) OLD_VALUE
new 1: select (value - 35958052) OLD_VALUE

OLD_VALUE
----------
27076692

REDO SIZE=27076692

- INSERT -

SQL> @begin

OLD_VALUE
----------
63034912

SQL> INSERT INTO T_NOLOG NOLOGGING SELECT * FROM ALL_OBJECTS;

71000 rows created.

SQL> @diff
old 1: select (value - &OLD_VALUE) OLD_VALUE
new 1: select (value - 63034912) OLD_VALUE

OLD_VALUE
----------
8493412

REDO SIZE=8493412

SQL> @begin

OLD_VALUE
----------
71528324

SQL>INSERT INTO T_LOG SELECT * FROM ALL_OBJECTS;

71000 rows created.

SQL> @diff
old 1: select (value - &OLD_VALUE) OLD_VALUE
new 1: select (value - 71528324) OLD_VALUE

OLD_VALUE
----------
8493360

REDO SIZE=8493360

- UPDATE -

SQL> @begin

OLD_VALUE
----------
80021684

SQL> UPDATE T_NOLOG NOLOGGING SET OBJECT_ID=1;

71000 rows updated.

SQL> @diff
old 1: select (value - &OLD_VALUE) OLD_VALUE
new 1: select (value - 80021684) OLD_VALUE

OLD_VALUE
----------
24671048

REDO SIZE=24671048

SQL> @begin

OLD_VALUE
----------
104692732

SQL> UPDATE T_LOG SET OBJECT_ID=1;

71000 rows updated.

SQL> @diff
old 1: select (value - &OLD_VALUE) OLD_VALUE
new 1: select (value - 104692732) OLD_VALUE

OLD_VALUE
----------
20911424

REDO SIZE=20911424

On DML INSERT/UPDATE/DELETE ... Oracle generated redo log on nologging mode not difference... on logging mode.
And I need to know about it on INSERT /*+ APPEND */:
- INSERT "APPEND" hints -

- table NOLOGGING mode and not use APPEND hints

SQL> @begin

OLD_VALUE
----------
125604156

SQL> INSERT INTO T_NOLOG NOLOGGING SELECT * FROM ALL_OBJECTS;

71000 rows created.

SQL> @diff
old 1: select (value - &OLD_VALUE) OLD_VALUE
new 1: select (value - 125604156) OLD_VALUE

OLD_VALUE
----------
8586036

REDO SIZE=8586036

SQL> @begin

OLD_VALUE
----------
142830588

SQL> INSERT /*+ APPEND */ INTO T_NOLOG NOLOGGING SELECT * FROM ALL_OBJECTS;

71000 rows created.

SQL> @diff
old 1: select (value - &OLD_VALUE) OLD_VALUE
new 1: select (value - 142830588) OLD_VALUE

OLD_VALUE
----------
29448

REDO SIZE=29448


- table LOGGING mode, and use APPEND hints

SQL> @begin

OLD_VALUE
----------
134190192

SQL> INSERT /*+ APPEND */ INTO T_LOG NOLOGGING SELECT * FROM ALL_OBJECTS;

71000 rows created.

SQL> @diff
old 1: select (value - &OLD_VALUE) OLD_VALUE
new 1: select (value - 134190192) OLD_VALUE

OLD_VALUE
----------
8640396

REDO SIZE=8640396

*** make table logging to nologging ***

SQL> alter table t_log nologging ;

Table altered.

SQL> @begin

OLD_VALUE
----------
142874676

SQL> INSERT /*+ APPEND */ INTO T_LOG NOLOGGING SELECT * FROM ALL_OBJECTS;

71000 rows created.

SQL> @diff
old 1: select (value - &OLD_VALUE) OLD_VALUE
new 1: select (value - 142874676) OLD_VALUE

OLD_VALUE
----------
27956

REDO SIZE=27956
APPEND hints & sql(nologging) on table "LOGGING" mode "select logging from dba_tables" (NO) ... not difference (generate redo)
If "alter table nologging" before, and then insert (append)So, Oracle will generate a minimal number of redo log... with INSERT /*+ APPEND */ when table be nologging ... select logging from dba_tables (NO)That just testing... But helpful to understand something ;)reference: http://docs.google.com/View?id=dhg2wncg_30mpkpd5ccWritten By: Surachart Opun http://surachartopun.com

After I read on otn forums.
They told they see high cpu usage, when use "olsnodes" command.
$ time olsnodes
node01
node02

real 0m0.153s
user 0m0.082s
sys 0m0.068sAnd then ... solve this by delete * files at ORA_CRS_HOME/log/hostname/client/ path.
$ rm -f $ORA_CRS_HOME/log/node01/client/*

$ time olsnodes
node01
node02

real 0m0.037s
user 0m0.017s
sys 0m0.014s

$ ls $ORA_CRS_HOME/log/node01/client/*
css.log

$ time olsnodes
node01
node02

real 0m0.045s
user 0m0.016s
sys 0m0.015s

$ ls $ORA_CRS_HOME/log/node01/client/*
css1.log css.log

What about idea?
When use "olsnodes" command successfully, it scans $CRS_HOME/log/hostname/client PATH and generates a new cssxxx.log file.metalink:729349.1 ...said it's a bug:
Olsnodes produces CPU spikes with many css*.log files in ORA_CRS_HOME/log/nodename/client directory

fixed in 11.1.0.7 and 10.2.0.4 CRS bundle Patch #2 onwards. Bundle Patch information is available in Note 756671.1
Try to check on 11.1.0.7 :
$ crsctl query crs softwareversion
Oracle Clusterware version on node [test01] is [11.1.0.7.0]

$ ls $ORA_CRS_HOME/log/test01/client/ | wc -l
32738

$ time olsnodes
test01
test02

real 0m0.030s
user 0m0.015s
sys 0m0.014sOK... No problem on 11.1.0.7Written By: Surachart Opun http://surachartopun.com

Welcome to the 151st edition of Log Buffer, the weekly review of database blogs. We’re going to take a fast tour through the best blogs from the week gone by, beginning this time, with Oracle.

Jonathan Lewis writes, “It occurred to me recently that I might be making casual use of terms that weren’t necessarily very well known to the less experienced user. So I’ve decided to build a glossary of terms – and I’ll try to add to it from time to time whenever I have a few minutes.”

Jonathan might want to add “Method R” to the glossary. Cary Millsap was making it understood, as he shows in Profiling with my Boy: “Today I’m going to raise the stakes, because yesterday I think I explained Method R so that an eleven year-old could understand it.”

Vivek Sharma offers a thorough look at the Cost-Based Optimizer: Inefficient Input yields Inefficient Output. Vivek begins, “Cost Based Optimizer has always been a mystery for most of the Database Administrators and Developers.  . . .  Cost Based Optimizer has improved a lot in previous few versions. Therefore, it can be said that CBO is still undergoing some enhancements. With these enhancements, Oracle Users have accepted the fact that the Application Performance might degrade after version upgrade . . . ”

Randolf Geist had some info to share on the matter of locked table statistics and subsequent create index. “ . . . in 10g and later index statistics are generated along with an index creation  . . .  so a newly created index usually has computed statistics. 10g also introduced the option to lock table statistics. Now if you lock statistics in 10g in later  . . .  and create an index on a locked table the statistics for the index will not be generated along with the CREATE INDEX command.”

Rob van Wijk gave us part three of his series on fast refreshable materialized view errors: aggregate MV’s. “In the third part I’m going to examine all restrictions for aggregate materialized views, as described in the documentation.  . . .  So this will be quite a lengthy and even tedious post, as you can imagine by the list above … but for a good cause.”

In MySQL blogs, the MySQL Performance Blog announced Percona.tv. Ryan Loew writes, “We’ll be uploading technical screencasts, conference video, and anything else cool we can think up.”

The Open Query blog published Good Practice/Bad Practice: Table Aliases. “When writing queries, try making a habit out of using short table aliases, no matter how small the query is.” An example using the World DB follows, as does a worthwhile discussion.

Roland Bouman examined MySQL stored functions: the impact of DECLARE HANDLER on performance, responding to Get the error return value in a variable by Peter Gulutzan.

Ronald Bradford looked into using statpack with SHOW STATUS. He writes: “Mark Leith, on of the MySQL Support Team managers wrote some time ago a very nice utility I use often called Statpack.  . . .  Over time I’ve grown to love it’s simplicity, but notice a number of shortcomings.  . . .  This post is more about detailing those little annoyances that I’d like to improve, or see improved.”

Morgan Tocker writes, “I think 5.1 gets some bad press for not being a compelling upgrade. It’s not the big features that make the difference, but the subtle ones. I wanted to highlight some of the these that may make your life easier when it’s time to upgrade . . . ” The post is Hidden gems in 5.1.

Let’s turn to SQL Server now, starting with a post from Aaron Alton, The HOBT. He writes, “If you’ve been working with SQL Server for any length of time, you’ve probably seen a number of different variations of the UPDATE command.  . . .  Anyway, today’s blog post is more like a public service announcement. It seems that all too many developers are still dangerously unaware of the ability to create very ambiguous UPDATE statements in TSQL.” Click through for Tricky Updates: Weird, Wacky, and Wonderful UPDATE Syntax Variations in SQL Server.

Louis Davidson looked into the question of read/write ratio versus read/write ratio. That’s what it says, really.

Alexander Kuznetsov posted one of those skill-testing quizzes—calculating third Wednesday of the month with inline UDFs. Alexander begins, “Problems such as calculating third Wednesday of the month or the last day of the month are very common. Recently Peter Larsson posted a scalar UDF that solves problems such as ‘third Wednesday of the month’, and Uri Dimant added a comment with a solution by Steve Kass. Unfortunately, all these solutions use scalar UDFs and as such are very slow, so I decided to post some very fast inline UDFs that I find very useful.”

Linchi Shea also was working the paradoxes: Performance impact: Logical scan fragmentation — 100% may not be as bad as 85%. “In [a] previous post I asked whether 100% logical scan fragmentation is always worse than 85% local scan fragmentation for table/index scans.  . . .  The answer is no. 100% logical scan fragmentation is not always worse than 85% logical scan fragmentation in terms of table or clustered index scans. And the following is an example.”

Reading is cool. I learned this from the Fonz. But look at this, Jeremiah Peschka says there are circumstances when less reading is a good thing. I’m confused! What else does he say? “Less reading is a good thing when you’re reading from a physical disk.” Ahhh. “During some recent performance tuning, I stumbled across a little gem of a stored procedure that was producing between 4,000 and 11,000 physical reads every time it was called.  . . .  It returns a list of state abbreviations. I’m not proud to say that I wrote it, but I have to come clean: I wrote it. So, how did I fix this travesty of my youthful ignorance?” I bet he hits his server machine in just the right way.

In PostgreSQL blogs, David Wheeler and his readers discussed the pgTAP Set-Testing Update.

The Postgres OnLine Journal exposed a restore of functional indexes gotcha.

In the DB2 world, Craig Mullins advised: know your ISOLATION levels. Craig writes, “Did you know that DB2 provides a way to change the way that a program or SQL statement acquires locks? That way is known as the isolation level and it can be set to specify the locking behavior for a transaction or statement.”

Willie Favero published the second of a couple articles collecting resources for both DB2 z/OS and DB2 LUW.

That’s all for now. Please add your favourite blogs from this week to the comments. And of course, don’t forget that you can publish an edition of Log Buffer on your own blog. Just send me an email to get started.

Till next time!

I’m not sure who originally wrote this function (probably Tom Kyte), but I needed it yesterday and couldn’t find it. Thankfully Chris Beck tracked it down in some other code I wrote.  I made a few enhancements, but the bulk of the function is the same.  The output is almost identical to the “SINCE” date [...]

Reanimating the Sydney MySQL User Group!

What: Sydney MySQL User Group meetup #7 - The Reboot

When: July 16, 2009 5:30 PM (please don’t forget to RSVP yes/no/maybe)

Where: Sydney, CBD - join the meetup for exact location.

We are back! After 3 years of being silent, SMUG (can I call it so? I know there are conflicts with other acronyms) resurrects the meetings.

The logistic of the meetup is the following:

• 5:30pm — the gathering starts and we have pizza and beers and talking your peers
• 6:00pm — we start the presentation
• 7:00pm — break and peer networking consuming the leftovers
• 7:30pm — we have an open (but slightly moderated) discussion. Topic is TBC. Perhaps, we can talk about future of MySQL now that Oracle’s bought Sun?

The schedule is very approximate and we will be quite flexible. We should finish about 8:30pm.

Presentation: Migrating 1 Terabyte Database to MySQL — Case Study by Alex Gorbachev (well, that’s me).

This presentation is based on the real project migrating from RDB on OpenVMS to MySQL on Linux. We will review the migration of the data itself and what infrastructure has been setup — high availability cluster with Heartbeat, DR site replication, organizing backup for 1 TB database. The system has been successfully put in production few months ago.

The attendance is completely free but of course someone need to cover the venue and that great stuff we are going to eat and drink so…

My company, The Pythian Group, is sponsoring the SMUG for now. You probably heard of Pythian, the leading provider of database services for MySQL as well as Oracle and SQL Server.

Nice people from PremiumSoft, the creator of Navicat, kindly offered to join us sponsoring the pizza and drinks. If you happen to manage MySQL databases you probably heard for Navicat, an award-winning database administration tool. More details - http://www.navicat.com/en/products/navicat_mysql/mysql_overview.html

Those of you in Sydney — looking forward to see you all there.

Data-Guard is often used for disaster planning, where the primary server is usual very far from the failover. Often on a different continent.

We are also planning to data-guard, but for a different disaster. We move about 90% of our operation to a new data center. About 3000 miles away. We have 4 hours of downtime. There is an old Russian saying – “two moves equal one fire”, so I guess we are planning for 50% disaster

One of the questions that pop-up a lot is the question of network delays. Its not a very well defined question. The person asking usually starts with “A change was done on production at 3pm. When will it be applied on the failover?” and the next question is: “How much time out of this gap is spent on network-related waits, rather than oracle-waits or disk-waits”?

There are 3 important factors that will influence the answer:

1. Network Latency. The time it takes to send 1 packet of data from target to destination. It is the easiet to measure – just ping the destination, and it is influenced mostly by the physical distance between the two locations and the speed of light.
2. Bandwidth utilization. The time it takes to send 1 packet is interesting, but we are more interested in the time it takes to send 500M of redo log. We have a nice OC3, theoretically capable of doing 155Mb/s. So theoretically 500M should around take around 15 seconds? Not really. First of all, network is a queue system, and we all know that we shouldn’t really run our queue system at 100% capacity, so we can’t use all 155 Mb/s.  TCP has congestion control implemented, so it wouldn’t let you send all your data at once, it will make you wait for the other side to start acknowledging first and carefully control the amounts of data you are allowed to send at one time.  Oh, and maybe other applications are also using the line. Given all that, it should be obvious that the percentage of the given bandwidth that you manage to actually use has a huge impact on the transfer rates. I’ve no idea how to check the line utilization myself – but my network manager can send me very nice graphs 
3. Congestion and errors. You have an SLA with your provider and all, but its a fact of life that not all the packets that leave source arrive safely to destination. Sometimes they get lost, or arrive in the wrong order. All these errors have an proportional impact on transfer time – 1% lost packets can cause 200% change in transfer times. Because once TCP has to retransmit lost packets it will start sending data very slowly, waiting to make sure it is received on the other side before sending more – and the utilization will drop like a rock.

If you are in the business of getting data across the ocean at a decent speed, you should also know that there are some companies that do WAN acceleration by addressing the causes for delay I mentioned about, and by introducing compression and proxies to the game. Its worth taking a look.

Most of all, don’t estimate delays by pinging remote machines. Talk to your network manager.