Blog Aggregator - Amardeep Sidhu

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).

Welcome to the 152^nd 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!

This two-page quick reference card written by Cary Millsap sums up computer software performance the Method R way. The first page lists definitions of the terms you need to know: efficiency, knee, load, response time, and so on. The second page lists ten principles that are vital to your ability to think clearly about software performance. This document contains meaningful insight in a format that's compact enough to hang on your wall.

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.

Today marks 2 anniversaries for me. It’s my 40th birthday, and it’s exactly 1 year since I gave up eating chocolate.

They say life begins at 40. I’m staying in all day today because I don’t to be out in case I miss it…

Cheers

Tim…

In the last 2 blog entries, i had covered 2 new features of EPM 11 Essbase. They were Format Strings and Varying Attributes. In today’s blog entry we shall see another good feature that was introduced in the EPM 11 release called as XOLAP. Though I have covered this before here, i  thought it would make sense if i introduce this again in the context of the BI EE – Essbase connectivity.

Prior to XOLAP, Essbase supported HOLAP (still does) wherein one can drill from an Essbase cube to a relational source(only on BSO cubes) thereby providing a drill-through. It also supported something called as LRO’s in BSO cubes wherein one can attach an artifact to a database cell. What was not possible though was visualizing relational and Essbase data together. For example, we might have Actuals loaded inside Essbase but Budget might be obtained directly from a relational source. In such cases HOLAP cannot be used directly(though some workarounds are possible). With the advent of BI EE – Essbase connectivity in the 10.1.3.3.2 release, such complex integration cases have been made possible within BI EE framework using conforming dimensions. For details on how this is done, check out the ODTUG white paper here that Mark and myself had created. But what if we want this kind of reporting in Excel-addin or smart view or any other downstream tools that use Essbase. This is where XOLAP can be very helpful.

For the sake of demonstration, i would use the Global schema here. Lets first start with building a XOLAP cube using the Essbase studio. Start with importing the data source and then creating the model.

Then build your hierarchies and deploy the cube as a XOLAP cube. Remember whenever a XOLAP cube is created, it is an ASO cube. Also, it gets created with “Duplicate Members” turned on.

Deploy this cube. Once the deployment is done, you can login to Excel-add in and view the data.

We now can report directly on a relational source through Essbase from Excel-add in. Our idea is to have a similar reporting structure but also have one more measure called Price which would be coming in directly from Essbase itself. In order to achieve this, create another ASO cube directly in EAS or in the Studio with a similar dimensional structure. It is not necessary that the ASO cube should have an exact dimensional structure as the XOLAP cube. But in our case for demonstration, we would create an exact similar structure. There would be another measure in the ASO cube called as Price.

Now load some data into Price measure alone and aggregate it.

Basically we have 2 cubes, one reporting on relational data using XOLAP and the other is a normal Essbase ASO cube. Now, in order to have a report with both Units and Price measure together, we need to create an additional ASO cube called GlobTarg which will be fed by the XOLAP and the ASO cube through transparent partition. So, lets first create the outline of GlobTarg first. Ensure that it has both Price and Units measures.

Now create 2 transparent Partitions,one with the XOLAP cube as the source and GlobTarg as the target and the other with the Price ASO cube as the source and GlobTarg as the target.

While creating the partition, map the corresponding source measures to the target measure. Once this is done, you can report directly on GlobTarg ASO cube. And you should be able to report both on the ASO as well as the relational source together.