Archives For September 2012

while learning more about postgresql I came across the default case in postgresql ( it is lower case ). so, when querying the dictionary/catalog you’ll have to provide the lower case names to get any results:

postgres=# create table test1 ( a numeric );
CREATE TABLE
postgres=# select relname from pg_class where relname = 'TEST1';
 relname 
---------
(0 rows)
postgres=# select relname from pg_class where relname = 'test1';
 relname 
---------
 test1
(1 row)

in oracle you’ll need to use upper case by default:

SQL> create table test1 ( a number );
Table created.
SQL> select table_name from dba_tables where table_name = 'TEST1';
TABLE_NAME
------------------------------
TEST1
SQL> select table_name from dba_tables where table_name = 'test1';
no rows selected
SQL> 

if you want postgresql to respect the case when creating objects you’ll need to put double quotes around the names:

postgres=# create table "TEST2" ( a numeric );
CREATE TABLE
postgres=# select relname from pg_class where relname = 'TEST2';
 relname 
---------
 TEST2
(1 row)
postgres=# select relname from pg_class where relname = 'test22';
 relname 
---------
(0 rows)

same in oracle:

SQL> create table "test2" ( a number );
Table created.
SQL> select table_name from dba_tables where table_name = 'test2';
TABLE_NAME
------------------------------
test2
SQL> select table_name from dba_tables where table_name = 'TEST2';
no rows selected

knowing this, is it possible to create identical tables which just differ in the case of their name ? :

postgresql:

postgres=# create table test3 ( a numeric );
CREATE TABLE
postgres=# create table "Test3" ( a numeric );
CREATE TABLE
postgres=# create table "TesT3" ( a numeric );
CREATE TABLE
postgres=# select relname from pg_class where upper(relname) like 'TEST3%';
 relname 
---------
 Test3
 TesT3
 test3
(3 rows)

not an issue with postgresql. what about oracle ?

SQL> create table test3 ( a number );
Table created.
SQL> create table "Test3" ( a number );
Table created.
SQL> create table "TesT3" ( a number );
Table created.
SQL> select table_name from dba_tables where upper(table_name) like 'TEST3%';
TABLE_NAME
------------------------------
TesT3
Test3
TEST3

same behaviour. If someone had asked me if this is possible in oracle before, I would have said: no, definitely not. lessons learned ? :)

going further: what about constraint names ?
in postgresql:

postgres=# alter table test3 add constraint c1 check ( a is not null );
ALTER TABLE
postgres=# alter table test3 add constraint "C1" check ( a > 5 );
ALTER TABLE
postgres=# select conname,consrc from pg_constraint where upper(conname) = 'C1';
 conname |       consrc       
---------+--------------------
 c1      | (a IS NOT NULL)
 C1      | (a > (5)::numeric)

ok, this is consistent. what about oracle ? :

SQL> alter table test3 add constraint c1 check ( a is not null );
Table altered.
SQL> alter table test3 add constraint "C1" check ( a > 5 );
alter table test3 add constraint "C1" check ( a > 5 )
                                *
ERROR at line 1:
ORA-02264: name already used by an existing constraint

what about indexes ? postgresql:

postgres=# create index i1 on test3(a);
CREATE INDEX
postgres=# create index "i1" on test3(a);
ERROR:  relation "i1" already exists
postgres=# create index "I1" on test3(a);
CREATE INDEX
postgres=# select indexname,indexdef from pg_indexes where upper(indexname) = 'I1';
 indexname |                  indexdef                  
-----------+--------------------------------------------
 i1        | CREATE INDEX i1 ON test3 USING btree (a)
 I1        | CREATE INDEX "I1" ON test3 USING btree (a)
(2 rows)

oracle:

SQL> create index i1 on test3 ( a );
Index created.
SQL> create index "i1" on test3 ( a );
create index "i1" on test3 ( a )
                            *
ERROR at line 1:
ORA-01408: such column list already indexed

as oracle checks if an index is defined on the same column(s) this is not possible. slightly modified test:

SQL> alter table test3 add ( b number );
Table altered.
SQL> create index "I1" on test3 ( b );
create index "I1" on test3 ( b )
            *
ERROR at line 1:
ORA-00955: name is already used by an existing object

still not possible.

I did not check all the objects but it seems that oracle is not as consistent as postgresql in this case.

Advertisements

if you see errors like this in the alert-log of your standby database:

MRP0: Background Media Recovery terminated with error 1274
Errors in file /oradata/dbs100/admin/diag/rdbms/dbs100dg2/dbs100dg2/trace/dbs100dg2_mrp0_4371.trc:
ORA-01274: cannot add datafile '+DBS100_DATA_DG/dbs100dg1/datafile/tbs1.276.793899763' - file could not be created
Managed Standby Recovery not using Real Time Apply
Recovery interrupted!
Recovery stopped due to failure in applying recovery marker (opcode 17.30).
Datafiles are recovered to a consistent state at change 10967593 but controlfile could be ahead of datafiles.
MRP0: Background Media Recovery process shutdown (dbs100dg2)

or this:

Errors in file /oradata/dbs100/admin/diag/rdbms/dbs100dg2/dbs100dg2/trace/dbs100dg2_mrp0_4981.trc:
ORA-01111: name for data file 6 is unknown - rename to correct file
ORA-01110: data file 6: '/opt/oracle/product/base/11.2.0.3.1/dbs/UNNAMED00006'
ORA-01157: cannot identify/lock data file 6 - see DBWR trace file
ORA-01111: name for data file 6 is unknown - rename to correct file
ORA-01110: data file 6: '/opt/oracle/product/base/11.2.0.3.1/dbs/UNNAMED00006'

… don’t panic. it is easy to fix.

first of all this happens if the STANDBY_FILE_MANAGEMENT parameter is set to “MANUAL” on the standby database. but how do you fix it ? the first option would be to re-create the standby database, but this would be a lot of work and depending on the size of the database could require a huge amount of time.

the easy way is to do it like this ( on the standby database, of course ):

SQL> ALTER DATABASE CREATE DATAFILE '/opt/oracle/product/base/11.2.0.3.1/dbs/UNNAMED00006' AS '+DBS100_DATA_DG';
Database altered.
SQL> alter database recover managed standby database disconnect from session;
Database altered.
SQL> alter system set standby_file_management='AUTO' scope=both;
System altered.

don’t forget to set the STANDBY_FILE_MANAGEMENT to “AUTO” after you created the file so that you don’t have to worry about such things in the future…

this is work in progress, but shall show the similarities and differences between postgresql and oracle in regards to implementing schemas and code. I will try to add more and more things in the future and update this post and the samples accordingly. documentation in the scripts is not very well at the moment but it should be enough to start.

for now the samples include:

  • tables: standard columns and arrays
  • constraints: primary keys, foreign keys, check constraints
  • triggers
  • sequences
  • indexes
  • views
  • loading blobs / clobs
  • plsql packages -> pgsql
  • materialzed views
  • partitioning
  • anonymous plsql / pgplsql blocks

did you know ( I didn’t ) that you can provide a star (*) when defining a number column ?

SQL> create table t1 ( a number(*), b number(*,10) );
Table created.
SQL> desc t1;
 Name	     Null?    Type
 ----------- -------- ----------------------------
 A                    NUMBER
 B                    NUMBER(38,10)

just noticed that there is a template script in the GI_HOME to enable RAC:

cat $GI_HOME/crs/config/relink_rac_on
#!/bin/sh
#
MAKE=/usr/bin/make
ECHO=/bin/echo
if [ $# -lt 1 ]; then
  $ECHO "Usage: relink_rac_on "
  exit 1
fi
ORACLE_HOME=$1
if [ $# -gt 1 ]; then
  MAKE=$2
else
  $ECHO "Make path has not been passed from command line."
fi
$ECHO "Checking for writable permission on $ORACLE_HOME/lib and $ORACLE_HOME/bin."
if [ -w $ORACLE_HOME/bin -a -w $ORACLE_HOME/lib ]; then
  $ECHO "running rac_on make target.."
  $MAKE -f $ORACLE_HOME/rdbms/lib/ins_rdbms.mk ORACLE_HOME=$ORACLE_HOME rac_on ioracle
  exit $?
else
  $ECHO "$ORACLE_HOME/lib and $ORACLE_HOME/bin directories are not writable, hence skipping relink operation."
fi
exit 0

this can be useful if you installed the ORACLE_HOME without enabling RAC and want to turn it on afterwards …

because various people come to this blog when they search for “acfs-9459: advm/acfs is not supported on this os version” I want to give some information on how you may tweak this ( if you really want to test ACFS/ADVM on an OS that is _not_ supported and there really is no need for this as oracle linux is free for testing ).

but before you really do this, keep in mind:

  • this is just for educational purposes
  • this is in no way supported by oracle
  • don’t do this on a production system
  • there is no guarantee that this works for you os

before executing the “roothas.pl” or “rootcrs.pl” scripts check the following file in your GI_HOME:

$GI_HOME/lib/osds_acfslib.pm

scroll down to where the various checks are performed. the lines should look similar to this:

# Testing for "oraclelinux_release", etc prevents us from dropping
# into the code below from other RH based distros like CentOS, for example.
  if ((defined($release)) &&                     # Redhat or OEL if defined
      (($release =~ /^redhat-release/) ||        # straight RH
       ($release =~ /^enterprise-release/) ||    # Oracle Enterprise Linux
       ($release =~ /^oraclelinux-release/)))    # Oracle Linux
  {
    my ($major, $minor, $patch, $vendor, $variation) = split /\./, $kver;
    my ($micro_number, $patch_level) = split /-/, $patch;    # e.g., 100 and 32
    if ($release =~ /AXS/)                 # Asianux Not supported
    {
      $vers = "ASIANUX";
      $supported = 0;
...

here you can tweak the code so that your os reports to be supported and you should be fine …

if you work with perl and want to connect to oracle you might want to DBD::Oracle. this is a oracle database driver for the perl DBI module.

there is no need to install this on a server which hosts an oracle instance. all you need from an oracle perspective is the oracle instant client.

to prepare the installation download the following files ( for your architecture ) from the above link:

  • instantclient-basic-linux.x64-11.2.0.3.0.zip
  • instantclient-sqlplus-linux.x64-11.2.0.3.0.zip
  • instantclient-sdk-linux.x64-11.2.0.3.0.zip

as I did a default mint install I additionally had to install the “Linux kernel AIO access library” package which is required for the instant client to work:

sudo apt-get install libaio1

all you need to do for getting the instant client to work is to unzip the downloaded files and setup your environment. in may case I unzipped the files to my home directory in a folder called oracle:

ls -la ~/oracle/instantclient_11_2/
total 185012
drwxr-xr-x 3 daniel daniel      4096 Aug 31 06:57 .
drwxr-xr-x 3 daniel daniel      4096 Aug 31 06:51 ..
-rwxrwxr-x 1 daniel daniel     25308 Sep 17  2011 adrci
-rw-rw-r-- 1 daniel daniel       437 Sep 17  2011 BASIC_README
-rwxrwxr-x 1 daniel daniel     46228 Sep 17  2011 genezi
-r--r--r-- 1 daniel daniel       368 Sep 17  2011 glogin.sql
lrwxrwxrwx 1 daniel daniel        56 Aug 31 06:57 libclntsh.so -> /home/daniel/oracle/instantclient_11_2/libclntsh.so.11.1
-rwxrwxr-x 1 daniel daniel  52761218 Sep 17  2011 libclntsh.so.11.1
-r-xr-xr-x 1 daniel daniel   7955322 Sep 17  2011 libnnz11.so
-rwxrwxr-x 1 daniel daniel   1971762 Sep 17  2011 libocci.so.11.1
-rwxrwxr-x 1 daniel daniel 118408281 Sep 17  2011 libociei.so
-r-xr-xr-x 1 daniel daniel    164836 Sep 17  2011 libocijdbc11.so
-r-xr-xr-x 1 daniel daniel   1503303 Sep 17  2011 libsqlplusic.so
-r-xr-xr-x 1 daniel daniel   1477446 Sep 17  2011 libsqlplus.so
-r--r--r-- 1 daniel daniel   2095661 Sep 17  2011 ojdbc5.jar
-r--r--r-- 1 daniel daniel   2714016 Sep 17  2011 ojdbc6.jar
drwxrwxr-x 4 daniel daniel      4096 Sep 17  2011 sdk
-r-xr-xr-x 1 daniel daniel      9352 Sep 17  2011 sqlplus
-rw-rw-r-- 1 daniel daniel       441 Sep 17  2011 SQLPLUS_README
-rwxrwxr-x 1 daniel daniel    191237 Sep 17  2011 uidrvci
-rw-rw-r-- 1 daniel daniel     66779 Sep 17  2011 xstreams.jar

for the environment I created a little file and sourced it:

echo"ORACLE_HOME=/home/daniel/oracle/instantclient_11_2
LD_LIBRARY_PATH=$ORACLE_HOME:$LD_LIBRARY_PATH
PATH=$ORACLE_HOME:$PATH
TNS_ADMIN=$ORACLE_HOME/network/admin
export ORACLE_HOME LD_LIBRARY_PATH PATH TNS_ADMIN" > ~/ora_env.sh 
mkdir -p ~/oracle/instantclient_11_2/network/admin
. ./ora_env.sh

before going on you should test if you can execute sqlplus ( if this does not work something is wrong with your environment ):

sqlplus
SQL*Plus: Release 11.2.0.3.0 Production on Fri Aug 31 07:26:46 2012
Copyright (c) 1982, 2011, Oracle.  All rights reserved.
Enter user-name: 

as a final test create a tnsnames.ora file with the connection parameters for your database and check if you can establish a connection:

echo "my_oracle_db=
  (DESCRIPTION=
    (ADDRESS=
      (PROTOCOL=TCP)
      (HOST=192.168.10.39)
      (PORT=1530)
    )
    (CONNECT_DATA=
      (GLOBAL_NAME=dbs300.local)
      (SID=dbs300)
    )
  )" >> ~/oracle/instantclient_11_2/network/admin/tnsnames.ora

let’s see if it works:

sqlplus my_oracle_usr/oracle@my_oracle_db
SQL*Plus: Release 11.2.0.3.0 Production on Fri Aug 31 07:53:44 2012
Copyright (c) 1982, 2011, Oracle.  All rights reserved.
|-----------------------------------------------------------------|
| This system is for the use of authorized users only.		  |
| Individuals using this computer system without authority, or in |
| excess of their authority, are subject to having all of their   |
| activities on this system monitored and recorded by system	  |
| personnel.							  |
|-----------------------------------------------------------------|
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options

as expected.

so far for the oracle part. because DBD::Oracle is a driver for the DBI package you probably need to install it first. you should have cpan installed along with your perl distribution.

which cpan
/usr/local/bin/cpan

if it’s not there search with your package manager and install it. for ubuntu/debian based distributions:

apt search cpan

for rpm based distributions:

yum search cpan

cpan requires an initial setup, but as this is straight forward I’ll just mention the important questions/answers:

cpan
Would you like to configure as much as possible automatically? [yes] yes
What approach do you want?  (Choose 'local::lib', 'sudo' or 'manual')
 [local::lib] sudo

that’s it. now we are ready to install the DBI module:

cpan
install DBI
quit

for the DBD::Oracle module I decided to just download the source file and to do a manual compilation/installation:

cpan
get DBD::Oracle
quit
cd ~/.cpan/build/DBD-Oracle-1.50*
perl Makefile.PL
make
sudo make install

easy, isn’t it ?

create a little perl script and see if you can execute a test-statement:

echo "#!/usr/bin/perl
use strict;
use warnings;
use DBI;
my @results;
my $db = DBI->connect ( 'dbi:Oracle:host=192.168.10.39;sid=dbs300;port=1530', 'my_oracle_usr/oracle', '' )
         or die "Connection failed: ". DBI->errstr;
my $sql = $db->prepare('SELECT \'connected\' FROM DUAL' )
         or die "Statement preparation failed: ". $db->errstr;
$sql->execute()
      or die "Statement execution failed:".$sql->errstr;
while ( @results = $sql->fetchrow_array() ) {
   print "$results[0] \n";
}
1;" >> oracle_test.pl
chmod u+x oracle_test.pl
./oracle_test.pl 
connected 

time to have fun with perl and oracle :)