Importing from Oracle to PostgreSQL using CSV file OR How to get Oracle data without a Foreign Data Wrapper

Hi, folks.

At my work, my boss needed urgently a report involving PostgreSQL and Oracle Databases. As almost the situations that already uses data from both are gathered by PHP, I’ve never had the need to install oracle_fdw. So I started to install it and to make the SQL. At some point, I encountered a problem with a library.

ERROR: could not load library "/usr/local/pg936/lib/postgresql/oracle_fdw.so": libclntsh.so.12.1: cannot open shared object file: No such file or directory

The problem, in fact, is not a problem. You just need to restart the database.

– Wait a minute! You just said the word “restart”??? (Ernst-Georg Schmid commented that not in all cases we need to restart, so please check the comment out to see if it fits in your case)

Well, I had that problem too. There was no possibility to restart anything at the very moment and my boss was waiting for the report. So I needed to do it by another way.

Doing two queries and copy into an electronic sheet could be a possibility, but not very effective when you need to do a lot of references between them, so I tried another solution.

All this text was to give some basis to the following, don’t worry, I will be more technical from now on.

Continue reading

Advertisements

Audit Log and Partitioning

Hi all,

For those who have to log high load databases, and worse, need to keep this logs for a long time and always available, partitioning is the way.

Of course, you can create various log tables, splitting by hand, but it is not efficient.

To solve this demand, I’ve used two very well explained tutorials with some modifications. The first is the Audit Trigger from the official Wiki. The second is the Scaling PostgreSQL Performance Using Table Partitioning from Engine Yard Blog.

In this tutorial, I will create a log that records every data change on the tables where the audit trigger is enabled and it will be partitioned by month and year. Feel free to use the original tutorials, as they cover more situations, especially the Engine Yard’s one, that has a solution to store the old data in files after some time.

Continue reading

How to solve the problem when Enterprise Manager refuses to start and all you have tried didn’t work

Hello.

Some days ago I had some problems with Enterprise Manager. After a problem with space on disk, one of the instances of the EM refused to start after cleaned the logs that was filling the system disk.

As other instances came back to work normally, I thought that the problem was in the configuration and I reset it using EMCA. It didn’t work. I tried to solve every error that appeared in logs and anything worked. Every time I tried to start it, it took about 10 minutes to show me the failed status.

Finally, I have reached an uncommon error saying that a configuration file was given a wrong parameter. I revised it a lot of times and didn’t find the wrong parameter. As a last try, I changed some parameters to force an error and possibly get some advice from the logs. For my surprise, the user oracle couldn’t save the file. I was using vim at this time. So I change the ownership of the file to oracle and voilá, EM started. So, it’s a reminder to review my permissions.