Archives For demonstration

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.

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…

recently a customer had the request to export a column to a flat file. nothing special, but the request was to encrypt the column as the data needs to be delivered to a third site. as you might know oracle provides the dbms_obfuscation_toolkit package which might be used for requests like this. so, here is an example ( as wordpress does not allow to upload scripts, sorry for the long pasting :) ):

/**
Export a column for a given table and schema in encrypted format to csv
PARAMETERS: OWNER
            TABLE
            COLUMN
            ENRYPTION-KEY
**/
SET LINES 164 VERIFY OFF PAGES 999 SERVEROUT ON FEEDBACK OFF
VARIABLE owner VARCHAR2(30)
VARIABLE table_name VARCHAR2(30)
VARIABLE column_name VARCHAR2(30)
VARIABLE encryption_key VARCHAR2(100)
VARIABLE continue VARCHAR2(1);
EXEC dbms_output.put_line ( chr(13) );
EXEC dbms_output.put_line ( '------------------------------------------------------' );
EXEC dbms_output.put_line ( '-- INFO: Starting encrypted column export  ');
EXEC dbms_output.put_line ( chr(13) );
/**
do some sanity checks
**/
DECLARE
  lv_owner all_users.username%TYPE;
  lv_directory all_directories.directory_path%TYPE;
  FUNCTION schema_exists ( pv_schema IN all_users.username%TYPE )
                         RETURN BOOLEAN
  IS
    CURSOR cur_schema
    IS SELECT 'ok'
         FROM all_users
        WHERE username = pv_schema
    ;
    lv_cursor_result VARCHAR2(2);
  BEGIN
    OPEN cur_schema;
      FETCH cur_schema INTO lv_cursor_result;
    CLOSE cur_schema;
    RETURN NVL ( lv_cursor_result, 'xx' ) = 'ok';
  END schema_exists;
  FUNCTION table_exists ( pv_schema IN all_users.username%TYPE
                        , pv_table IN all_tables.table_name%TYPE
                        ) RETURN BOOLEAN
  IS
    CURSOR cur_table
    IS SELECT 'ok'
         FROM all_tables
        WHERE owner = pv_schema
          AND table_name = pv_table
    ;
    lv_cursor_result VARCHAR2(2);
  BEGIN
    OPEN cur_table;
      FETCH cur_table INTO lv_cursor_result;
    CLOSE cur_table;
    RETURN NVL ( lv_cursor_result, 'xx' ) = 'ok';
  END table_exists;
  FUNCTION column_exists ( pv_schema IN all_users.username%TYPE
                         , pv_table IN all_tables.table_name%TYPE
                         , pv_column IN all_tab_columns.column_name%TYPE
                         ) RETURN BOOLEAN
  IS
    CURSOR cur_column
    IS SELECT 'ok'
         FROM all_tab_columns
        WHERE owner = pv_schema
          AND table_name = pv_table
          AND column_name = pv_column
    ;
    lv_cursor_result VARCHAR2(2);
  BEGIN
    OPEN cur_column;
      FETCH cur_column INTO lv_cursor_result;
    CLOSE cur_column;
    RETURN NVL ( lv_cursor_result, 'xx' ) = 'ok';
  END column_exists;
  FUNCTION exp_directory_exists RETURN BOOLEAN
  IS
    CURSOR cur_directory
    IS SELECT directory_path
         FROM all_directories
        WHERE directory_name = 'DATA_PUMP_DIR'
    ;
  BEGIN
    OPEN cur_directory;
      FETCH cur_directory INTO lv_directory;
    CLOSE cur_directory;
    RETURN lv_directory IS NOT NULL;
  END exp_directory_exists;
BEGIN
   :owner := UPPER('&1');
   :table_name := UPPER('&2'); 
   :column_name := UPPER('&3');
   :encryption_key := '&4';
   IF :encryption_key IS NULL
      OR
      -- encryption key must be at least 80 characters
      length ( :encryption_key ) < 80
   THEN
     raise_application_error ( -20001
                             , '--- CHECK INPUT: The encryption key "'||:encryption_key||'" is too short or empty ! ');
     :continue := 'N';
   END IF;
   IF NOT ( schema_exists ( pv_schema => :owner ) )
   THEN
     raise_application_error ( -20001
                             , '--- CHECK INPUT: The schema '||:owner||' does not exist in this database ! ');
     :continue := 'N';
   END IF;
   IF NOT ( table_exists ( pv_schema => :owner
                         , pv_table => :table_name
                         ) )
   THEN
     raise_application_error ( -20001
                             , '--- CHECK INPUT: The table '||:table_name||' does not exist for the schema '||:owner||' ! ');
     :continue := 'N';
   END IF;
   IF NOT ( column_exists ( pv_schema => :owner
                          , pv_table => :table_name
                          , pv_column => :column_name
                          ) )
   THEN
     raise_application_error ( -20001
                             , '--- CHECK INPUT: The column '||:column_name||' does not exist for the table '||:table_name||' in schema '||:owner||'! ');
     :continue := 'N';
   END IF;
   IF NOT ( exp_directory_exists )
   THEN
     raise_application_error ( -20001
                             , '--- CHECK DIRECTORY: Can not find directory DATA_PUMP_DIR ! ');
     :continue := 'N';
   ELSE
     dbms_output.put_line (' --- INFO: File will be located here : '||lv_directory );
   END IF;
   :continue := 'Y';
END;
/
DECLARE
  lv_owner all_users.username%TYPE := UPPER(:owner);
  lv_table all_tables.table_name%TYPE := UPPER(:table_name);
  lv_column all_tab_columns.column_name%TYPE := UPPER(:column_name);
  lt_file_type utl_file.file_type;
  lv_statement VARCHAR2(4000);
  ln_cursor NUMBER;
  ln_amount_of_rows INTEGER := 0;
  lv_value VARCHAR2(4000);
  lv_seed VARCHAR2(4000) := :encryption_key;
BEGIN
  IF ( :continue = 'Y' ) 
  THEN
    dbms_output.put_line (' --- INFO: selected schema is : '||lv_owner );
    dbms_output.put_line (' --- INFO: selected table is  : '||lv_table );
    dbms_output.put_line (' --- INFO: selected column is : '||lv_column );
    dbms_output.put_line (' --- INFO: exporting to       : DATA_PUMP_DIR ');
    -- open file for writing
    lt_file_type := utl_file.fopen ( location => 'DATA_PUMP_DIR'
                                   , filename => 'exp_'||lv_owner||'_'||lv_table||'_'||lv_column||'_'
                                                  ||TO_CHAR(sysdate,'DD.MM.YYYY HH24:MI:SS')||'.csv'
                                   , open_mode => 'w'
                                   );
    lv_statement := 'SELECT '||lv_column||' FROM '||lv_owner||'.'||lv_table;
    dbms_output.put_line (' --- INFO: statement is: '||lv_statement );
    -- prepare statement
    ln_cursor := dbms_sql.open_cursor;
    dbms_sql.parse ( c => ln_cursor
                   , statement => lv_statement
                   , language_flag => dbms_sql.v7 
                   );
    dbms_sql.define_column ( c => ln_cursor
                           , position => 1
                           , column => lv_column
                           , column_size => 4000
                           );
    ln_amount_of_rows := dbms_sql.execute ( c => ln_cursor ); 
    LOOP
      -- fetch all the rows
      IF dbms_sql.fetch_rows ( c => ln_cursor ) = 0
      THEN
        EXIT;
      ELSE
        ln_amount_of_rows := ln_amount_of_rows + 1;
        -- get the column value
        dbms_sql.column_value ( c => ln_cursor
                              , position => 1
                              , value => lv_value 
                              ); 
        -- input must be a multiple of 8 bytes, so rpadding with blanks
        dbms_obfuscation_toolkit.desencrypt ( input_string => rpad ( lv_value
                                                                   , ( trunc ( length ( lv_value ) / 8 ) + 1 ) * 8
                                                                   , chr(0)
                                                                   )
                                            , key_string => lv_seed 
                                            , encrypted_string  => lv_value
                                            );
        -- write to output file
        utl_file.put_line ( file => lt_file_type 
                          , buffer => utl_raw.cast_to_raw ( lv_value )
                          );
      END IF;
    END LOOP;
    -- close cursor
    dbms_sql.close_cursor ( c => ln_cursor );
    -- close file handle
    utl_file.fclose ( file => lt_file_type );
    dbms_output.put_line (' --- Exported '||ln_amount_of_rows||' rows !!! ');
  END IF;
END;
/

for decrypting one could use this snippet:

/**
Decrypt the encrypted data in the exported csv files
**/
SET LINES 164 VERIFY OFF PAGES 999 SERVEROUT ON FEEDBACK OFF
VARIABLE filename VARCHAR2(4000)
VARIABLE encryption_key VARCHAR2(100)
VARIABLE continue VARCHAR2(1);
EXEC dbms_output.put_line ( chr(13) );
EXEC dbms_output.put_line ( '------------------------------------------------------' );
EXEC dbms_output.put_line ( '-- INFO: Starting decryption  ');
EXEC dbms_output.put_line ( chr(13) );
/**
do some sanity checks
**/
DECLARE
  lv_directory all_directories.directory_path%TYPE;
  FUNCTION exp_directory_exists RETURN BOOLEAN
  IS
    CURSOR cur_directory
    IS SELECT directory_path
         FROM all_directories
        WHERE directory_name = 'DATA_PUMP_DIR'
    ;
  BEGIN
    OPEN cur_directory;
      FETCH cur_directory INTO lv_directory;
    CLOSE cur_directory;
    RETURN lv_directory IS NOT NULL;
  END exp_directory_exists;
BEGIN
  :filename := '&1';
  :encryption_key := '&2';
  IF ( length ( :encryption_key ) < 80 ) 
  THEN
     raise_application_error ( -20001
                             , '--- CHECK INPUT: The encryption key "'||:encryption_key||'" is too short or empty ! ');
     :continue := 'N';
  END IF;
  IF NOT ( exp_directory_exists )
  THEN
    raise_application_error ( -20001
                            , '--- CHECK DIRECTORY: Can not find directory DATA_PUMP_DIR ! ');
    :continue := 'N';
  ELSE
    dbms_output.put_line (' --- INFO: File to be loaded : '||lv_directory );
  END IF;
  :continue := 'Y';
END;
/
DECLARE
  lt_file_type utl_file.file_type;
  lv_buffer VARCHAR2(4000);
  lv_buffer_raw RAW(4000);
  lraw_decrypted_value RAW(4000);
  lv_value RAW(32767);
BEGIN
  IF ( :continue = 'Y' ) 
  THEN
    -- open file handle
    lt_file_type := utl_file.fopen ( location => 'DATA_PUMP_DIR'
                                   , filename => :filename
                                   , open_mode => 'r'
                          --         , max_linesize => 4000
                                   );
    -- read through the file line by line
    LOOP
      BEGIN
        utl_file.get_line ( file => lt_file_type
                         ,  buffer => lv_buffer
                         );
        lv_buffer_raw := lv_buffer;
        dbms_obfuscation_toolkit.desdecrypt ( input => lv_buffer_raw
                                            , key => utl_raw.cast_to_raw ( :encryption_key )
                                            , decrypted_data => lraw_decrypted_value
                                            );
        dbms_output.put_line ( 'encrypted value: '||utl_raw.cast_to_varchar2(lraw_decrypted_value) );
      EXCEPTION
        WHEN no_data_found THEN EXIT;
      END;
    END LOOP;
    -- close file handle
    utl_file.fclose ( file => lt_file_type );
  ELSE
    NULL;
  END IF;
END;
/

for encrypting and decrypting two simple calls now do the trick:

# to encrypt:
@encrypted_column_export.sql USER TABLE_NAME COLUMN_NAME ENCRYPTION_KEY
# to decrypt
@decrypt_exported_csv.sql FILE_NAME ENCRYPTION_KEY

asm workshop

May 17, 2012 — Leave a comment

if you think about switching your databases to asm, want to boot your people or just want to do a workshop which offers a lot of practice and less slides feel free to contact me.

what you’ll get:

your own virtual box image, containing:

  • pre-installed oracle linux
  • software only installation of the grid infrastructure
  • all scripts and solutions for the topics discussed
  • reset scripts for each topic, so you may re-do each topic as much as you want

.. and the full set of slides, of course ( although the workshop is much more about practice you need to know the theoretical fundamentals )

some of the topics are:

  • storage preparation for asm
  • asmlib, use it or not ?
  • configuration of the cluster stack and asm
  • troubleshooting techniques
  • best practices
  • command line tools
  • patching

it’s up to you if you’d like to do the workshop in-house or at a location organized by my company. you may even reuse the slides and virtual image for your own internal workshops, if you want.

due to some network changes we were forced to adjust the interconnect configuration of one our managed grid infrastructure installations today. surprisingly this was a really smooth task, and all that needs to to be done is:

before doing any changes to the network configuration and while the rac databases and grid infrastructure are still running:

backup the current gpng ( grid plug and play ) profile in case you need to restore the old configuration:

cd $GRID_HOME/gpnp/[hostname]/profiles/peer
cp profile.xml profile.xml_bak

shutdown the rac database and listener:

srvctl stop database -d db_name
srvctl stop listener -l listener_name

make the changes to the interconnect configuration:

oifcfg setif -global eth1/10.167.77.0:cluster_interconnect
oifcfg getif

… where eth1 is the interface name for the interconnect and 10.167.77.0 is the new network to use

as root shutdown and disable the cluster stack:

crsctl stop crs
crsctl disable crs

shutdown the servers and perform the physical network changes, if any, start them up again and adjust the hosts file and the interface configurations.

as root user enable and start crs:

crsctl enable crs
crsctl start crs

wait a few minutes and check the cluster status:

oifcfg getif
crsctl status resource -t

that’s it.

a probably less known feature of the oracle database is the database filesystem (dbfs). in short dbfs provides a filesystem interface on top of database tables accessible through standard operating system commands. storing files inside the database becomes as easy as creating files on the operating system level. there is no need to use a programming language to store blobs/clobs inside the database.

for demonstrating dbfs this post will show how to create a subversion repository which actually resides in the oracle database. this is not intended for any production use and shall only show that is basically works.

because I am on linux and want to mount the database filesystem on the operating system I will need to install the kernel-devel and fuse packages and give execute privileges to the fusermount command:

su-
yum install kernel-devel fuse fuse-libs
chmod +x /bin/fusermount

next I’ll create the configuration file for fuse:

touch /etc/fuse.conf
echo "user_allow_other" > /etc/fuse.conf

the mountpoint for holding my database filesystem and subversion repository will be /oradata/DB112/svn owned by the database owner:

su - oracle
mkdir /oradata/DB112/svn

as all the files will reside inside the oracle database I will create a separate user:

su - oracle
sqlplus / as sysdba
CREATE USER svn
IDENTIFIED by "svn"
DEFAULT TABLESPACE USERS
TEMPORARY TABLESPACE TEMP;
GRANT CREATE SESSION TO svn;
GRANT dbfs_role TO svn;
GRANT RESOURCE TO svn;

the script that creates the dbfs “MYSVNFS” which will be stored in the “USERS” tablespace is:

connect svn/svn
@$ORACLE_HOME/rdbms/admin/dbfs_create_filesystem.sql USERS MYSVNFS

the output should be similar to this:

No errors.
--------
CREATE STORE:
begin dbms_dbfs_sfs.createFilesystem(store_name => 'FS_MYSVNFS', tbl_name =>
'T_MYSVNFS', tbl_tbs => 'USERS', lob_tbs => 'USERS', do_partition => false,
partition_key => 1, do_compress => false, compression => '', do_dedup => false,
do_encrypt => false); end;
--------
REGISTER STORE:
begin dbms_dbfs_content.registerStore(store_name=> 'FS_MYSVNFS', provider_name
=> 'sample1', provider_package => 'dbms_dbfs_sfs'); end;
--------
MOUNT STORE:
begin dbms_dbfs_content.mountStore(store_name=>'FS_MYSVNFS',
store_mount=>'MYSVNFS'); end;
--------
CHMOD STORE:
declare m integer; begin m := dbms_fuse.fs_chmod('/MYSVNFS', 16895); end;
No errors.

the client program used to mount the filesystem is called dbfs_client and is located under $ORACLE_HOME/bin. the proper way in regards to security would be to use an oracle wallet for storing the password but as this is only a test I will do it the easy way ( if someone is interested on how to setup the wallet way, just let me know ):

echo "svn" > /home/oracle/passwordfile
nohup dbfs_client svn@DB112 /oradata/DB112/svn < /home/oracle/passwordfile &

if everything worked the new mount is now visible with the os commands:

df -h
Filesystem Size Used Avail Use% Mounted on
/dev/hdc1 28G 11G 15G 43% /
tmpfs 741M 456M 286M 62% /dev/shm
dbfs-svn@DB112:/ 1.1G 160K 1.1G 1% /oradata/DB112/svn
##
ls -la /oradata/DB112/svn/MYSVNFS/
total 0
drwxrwxrwx 3 root root 0 May 4 01:10 .
drwxr-xr-x 3 root root 0 May 4 01:15 ..
drwxr-xr-x 7 root root 0 May 4 01:10 .sfs

now I am ready to create the subversion repository:

su - oracle
cd /oradata/DB112/svn/MYSVNFS/
svnadmin create MYREPO

for being able to access the repository later on from another user I’ll add the user to the subversion password file:

cat MYREPO/conf/passwd
[users]
svn = svn

… and uncomment the following line in the svnserve.conf file:

password-db = passwd

… and start the subversion deamon:

svnserve -d -r /oradata/DB112/svn/MYSVNFS

to simulate real-life subversion usage I will create a new os user and do some work on the subversion repository ( I could have done this from another workstation, too ):

su -
useradd svn
passwd svn
su - svn

the first thing I need to do is to checkout the repository:

svn co svn://localhost/MYREPO/
cd MYREPO
ls -la

lets copy some files ( text and binary ) to populate the repository ( don’t care if not all files are copied because of permissions ):

cp /etc/*.conf .
cp /bin/cpio .
ls -la

as I now have some files I’ll add them to the repository and commit ( commit means pushing to the subversion server here ):

svn add *
svn commit -m "blabla" *.conf --username=svn
svn commit -m "blabla" cpio

done. all files committed to the subversion server and stored inside the oracle database. don’t believe it ?

su - oracle
sqlplus / as sysdba
set lines 164
col object_name for a20
col object_type for a15
SELECT object_name,object_type FROM all_objects WHERE owner = 'SVN';
##
OBJECT_NAME          OBJECT_TYPE
-------------------- ---------------
T_MYSVNFS            TABLE
IG_SFS$_FST_19       INDEX
IP_SFS$_FST_19       INDEX
IPG_SFS$_FST_19      INDEX
SFS$_FSTP_19         TABLE
SYS_C005052          INDEX
##
col item for a30
col pathname for a40
select item ,PATHNAME from svn.T_MYSVNFS;
##
ITEM PATHNAME
------------------------------ ----------------------------------------
MYREPO /MYREPO
locks /MYREPO/locks
db.lock /MYREPO/locks/db.lock
db-logs.lock /MYREPO/locks/db-logs.lock
hooks /MYREPO/hooks
start-commit.tmpl /MYREPO/hooks/start-commit.tmpl
pre-commit.tmpl /MYREPO/hooks/pre-commit.tmpl
post-revprop-change.tmpl /MYREPO/hooks/post-revprop-change.tmpl
conf /MYREPO/conf
authz /MYREPO/conf/authz
db /MYREPO/db
fs-type /MYREPO/db/fs-type
revs /MYREPO/db/revs
0 /MYREPO/db/revs/0
revprops /MYREPO/db/revprops
0 /MYREPO/db/revprops/0
transactions /MYREPO/db/transactions
txn-protorevs /MYREPO/db/txn-protorevs
write-lock /MYREPO/db/write-lock
uuid /MYREPO/db/uuid
0 /MYREPO/db/revs/0/0
0 /MYREPO/db/revprops/0/0
fsfs.conf /MYREPO/db/fsfs.conf
rep-cache.db /MYREPO/db/rep-cache.db
min-unpacked-rev /MYREPO/db/min-unpacked-rev
txn-current-lock /MYREPO/db/txn-current-lock
format /MYREPO/db/format
format /MYREPO/format
passwd /MYREPO/conf/passwd
svnserve.conf /MYREPO/conf/svnserve.conf
pre-revprop-change.tmpl /MYREPO/hooks/pre-revprop-change.tmpl
pre-lock.tmpl /MYREPO/hooks/pre-lock.tmpl
pre-unlock.tmpl /MYREPO/hooks/pre-unlock.tmpl
ROOT /
.sfs /.sfs
attributes /.sfs/attributes
tools /.sfs/tools
snapshots /.sfs/snapshots
RECYCLE /.sfs/RECYCLE
content /.sfs/content
post-commit.tmpl /MYREPO/hooks/post-commit.tmpl
post-lock.tmpl /MYREPO/hooks/post-lock.tmpl
post-unlock.tmpl /MYREPO/hooks/post-unlock.tmpl
README.txt /MYREPO/README.txt
1 /MYREPO/db/revs/0/1
txn-current /MYREPO/db/txn-current
2 /MYREPO/db/revs/0/2
1 /MYREPO/db/revprops/0/1
current /MYREPO/db/current
2 /MYREPO/db/revprops/0/2

that’s the subversion repository stored in the oracle database.

remember this is only for educational purposes, but imagine what you could do with it:

  • back up the repository with rman
  • use rman to duplicate the database for testing purposes
  • setup rac one node, integrate the subversion daemon with the oracle grid infrastructure and make the repository high available
  • create multiple dbfs and multiple svn repositories in one oracle database
  • enable deduplication for the repository files ( see * )
  • enable compression for the repository files ( see ** )

oracle uses secure files to store the files located in the dbfs. this means you may use all the features secure files provide. check the examples for an overview.

*

alter table svn.T_MYSVNFS modify lob(FILEDATA) (deduplicate);

**

alter table svn.T_MYSVNFS modify lob(FILEDATA) (compress high);

as said above, this is only educational but i’d be happy to start a discussion about the pros and cons ( beside the licensing costs, of course ) …. what would be a good use case to use dbfs for ?

this post will continue the introduction post to linux processes and shows how the listener handles connection requests to the database.

let’s check the listeners pid:

ps -ef | grep tns
grid 2646 1 0 10:47 ? 00:00:03 /opt/oracle/product/base/11.2.0.3/bin/tnslsnr LISTENER_DB112 -inherit

as the listener needs to handle connections there must be some sorts of open files for the listener process:

[root@oracleplayground ~]# ls -la /proc/2646/fd/
total 0
dr-x------ 2 grid oinstall 0 Apr 4 10:47 .
dr-xr-xr-x 6 grid oinstall 0 Apr 4 10:47 ..
lrwx------ 1 grid oinstall 64 Apr 4 15:29 0 -> /dev/null
lrwx------ 1 grid oinstall 64 Apr 4 15:29 1 -> /dev/null
lrwx------ 1 grid oinstall 64 Apr 4 15:29 10 -> socket:[7875]
lrwx------ 1 grid oinstall 64 Apr 4 15:29 11 -> socket:[7877]
lrwx------ 1 grid oinstall 64 Apr 4 15:29 12 -> socket:[7949]
lrwx------ 1 grid oinstall 64 Apr 4 15:29 13 -> socket:[7950]
lrwx------ 1 grid oinstall 64 Apr 4 15:29 15 -> socket:[12719]
lrwx------ 1 grid oinstall 64 Apr 4 15:29 2 -> /dev/null
lr-x------ 1 grid oinstall 64 Apr 4 15:29 3 -> /opt/oracle/product/base/11.2.0.3/rdbms/mesg/diaus.msb
lr-x------ 1 grid oinstall 64 Apr 4 15:29 4 -> /proc/2646/fd
lr-x------ 1 grid oinstall 64 Apr 4 15:29 5 -> /opt/oracle/product/base/11.2.0.3/network/mesg/nlus.msb
lr-x------ 1 grid oinstall 64 Apr 4 15:29 6 -> pipe:[7822]
lr-x------ 1 grid oinstall 64 Apr 4 15:29 7 -> /opt/oracle/product/base/11.2.0.3/network/mesg/tnsus.msb
lrwx------ 1 grid oinstall 64 Apr 4 15:29 8 -> socket:[7873]
l-wx------ 1 grid oinstall 64 Apr 4 15:29 9 -> pipe:[7823]
[root@oracleplayground ~]# 

this tells us that the listener has 15 open file descriptors of which 8,10-15 are sockets and 9 is a pipe.

let’s request a connection to the database through the listener and trace the listener process in parallel:

Session 1 ( as root ):

strace -p 2646

Session 2 ( as oracle ):

sqlplus a/a@DB112

the strace output ( i have number the lines ) should look similar to this:

1: poll([{fd=8, events=POLLIN|POLLRDNORM}, {fd=11, events=POLLIN|POLLRDNORM}, {fd=12, events=POLLIN|POLLRDNORM}, {fd=13, events=POLLIN|POLLRDNORM}, {fd=15, events=POLLIN|POLLRDNORM}, {fd=-1}, {fd=-1}], 7, -1) = 1 ([{fd=13, revents=POLLIN|POLLRDNORM}])
2: times({tms_utime=76, tms_stime=325, tms_cutime=0, tms_cstime=1}) = 431191568
3: getsockname(13, {sa_family=AF_INET, sin_port=htons(1521), sin_addr=inet_addr("127.0.0.1")}, [11087335753955409936]) = 0
4: getpeername(13, 0x7fffc6b5d1a8, [11087335753955409936]) = -1 ENOTCONN (Transport endpoint is not connected)
5: accept(13, {sa_family=AF_INET, sin_port=htons(23139), sin_addr=inet_addr("127.0.0.1")}, [68719476752]) = 14
6: getsockname(14, {sa_family=AF_INET, sin_port=htons(1521), sin_addr=inet_addr("127.0.0.1")}, [68719476752]) = 0
7: fcntl(14, F_SETFL, O_RDONLY|O_NONBLOCK) = 0
8: getsockopt(14, SOL_SOCKET, SO_SNDBUF, [-4128159149999471140], [4]) = 0
9: getsockopt(14, SOL_SOCKET, SO_RCVBUF, [-4128159149999434336], [4]) = 0
10: setsockopt(14, SOL_TCP, TCP_NODELAY, [1], 4) = 0
11: fcntl(14, F_SETFD, FD_CLOEXEC) = 0
12: times({tms_utime=76, tms_stime=325, tms_cutime=0, tms_cstime=1}) = 431191568
13: rt_sigaction(SIGPIPE, {0x1, ~[ILL ABRT BUS FPE SEGV USR2 XCPU XFSZ SYS RTMIN RT_1], SA_RESTORER|SA_RESTART|SA_SIGINFO, 0x32e840ebe0}, {0x1, ~[ILL ABRT BUS FPE KILL SEGV USR2 STOP XCPU XFSZ SYS RTMIN RT_1], SA_RESTORER|SA_RESTART|SA_SIGINFO, 0x32e840ebe0}, 8) = 0
14: times({tms_utime=76, tms_stime=325, tms_cutime=0, tms_cstime=1}) = 431191568
15: times({tms_utime=76, tms_stime=325, tms_cutime=0, tms_cstime=1}) = 431191568
16: poll([{fd=8, events=POLLIN|POLLRDNORM}, {fd=11, events=POLLIN|POLLRDNORM}, {fd=12, events=POLLIN|POLLRDNORM}, {fd=13, events=POLLIN|POLLRDNORM}, {fd=15, events=POLLIN|POLLRDNORM}, {fd=14, events=POLLIN|POLLRDNORM}, {fd=-1}], 7, 60000) = 1 ([{fd=14, revents=POLLIN|POLLRDNORM}])
17: read(14, "\335\1\1:\1,\fA \377\377\177\10\1\243:\10"..., 8208) = 221
18: fcntl(14, F_GETFL) = 0x802 (flags O_RDWR|O_NONBLOCK)
19: fcntl(14, F_SETFL, O_RDWR) = 0
20: times({tms_utime=76, tms_stime=325, tms_cutime=0, tms_cstime=1}) = 431191569
21: fcntl(14, F_SETFD, 0) = 0
22: pipe([16, 17]) = 0
23: pipe([18, 19]) = 0
24: clone(child_stack=0, flags=CLONE_CHILD_CLEARTID|CLONE_CHILD_SETTID|SIGCHLD, child_tidptr=0x2ac09be5bb80) = 5894
25: wait4(5894, [{WIFEXITED(s) && WEXITSTATUS(s) == 0}], 0, NULL) = 5894
26: close(16) = 0
27: close(19) = 0
28: fcntl(18, F_SETFD, FD_CLOEXEC) = 0
29: fcntl(17, F_SETFD, FD_CLOEXEC) = 0
30: fcntl(14, F_SETFD, FD_CLOEXEC) = 0
31: poll([{fd=8, events=POLLIN|POLLRDNORM}, {fd=11, events=POLLIN|POLLRDNORM}, {fd=12, events=POLLIN|POLLRDNORM}, {fd=13, events=POLLIN|POLLRDNORM}, {fd=15, events=POLLIN|POLLRDNORM}, {fd=18, events=POLLIN|POLLRDNORM}, {fd=17, events=0}], 7, -1) = 1 ([{fd=18, revents=POLLIN|POLLRDNORM}])
32: read(18, "NTP0 5895\n", 64) = 10
33: write(17, ";", 4) = 4
34: write(17, "(ADDRESS=(PROTOCOL=tcp)(DEV=14)("..., 59) = 59
35: write(17, "\1\4", 8) = 8
36: read(18, "", 4) = 4
37: read(18, "*1", 4) = 4
38: write(14, "\10\v", 8) = 8
39: close(17) = 0
40: close(18) = 0
41: close(14) = 0
42: lseek(7, 19968, SEEK_SET) = 19968
43: read(7, "\f005\4P006\4j007\4\206008\4\240009\4\335"..., 512) = 512
44: poll([{fd=8, events=POLLIN|POLLRDNORM}, {fd=11, events=POLLIN|POLLRDNORM}, {fd=12, events=POLLIN|POLLRDNORM}, {fd=13, events=POLLIN|POLLRDNORM}, {fd=15, events=POLLIN|POLLRDNORM}, {fd=-1}, {fd=-1}], 7, -1) = 1 ([{fd=15, revents=POLLIN|POLLRDNORM}])
45: Process 2646 detached

a lot of cryptic output, isn’t it? let’s take a closer look on what’s happening:

on line one you can see a call to “poll”. poll waits for some events on the file descriptors. as connections are files, you may say it waits for some sort of connections.

on line two you can see a call to times, which returns several process times.

on line three there is a call to getsockname which returns the socket name for this address: {sa_family=AF_INET, sin_port=htons(1521), sin_addr=inet_addr(“127.0.0.1”)}, which is
the listener on port 1521 ( sa_family: this is the address family, AF_INET is the one used for IP, sin_port: is the port, sin_addr: is the address, in this case localhost ).

the call on line four ( getpeername ) does what it is called: get the name of the connected peer. it returns with ENOTCONN ( the socket is not connected ). next, one line five, a connection to the socket is accepted. notice that a new file descriptor is created ( 14 ) and passed to the getsockname call on line six. getsockname returns with code 0, which is success. now there is a connected endpoint which is our request to the listener.

the call to fcntl on line 7 modifies the file descriptor: set the file status flags ( F_SETFL ) to read only ( O_RDONLY ) and block system calls ( O_NONBLOCK ).

on line 8 and 9 the values of the sockets receive and sent buffers are read and on line 10 the TCP option TCP_NODELAY ( which disables Nagle’s algorithm ) is set.

next, on line 11, the socket is modified to close when a call to an exec function is performed. from now on I will ignore the calls to times, as I have described it above.

on line 13 rt_sigaction changes the action taken by the process on receipt of a signal and line 17 reads from the file associated with the file
descriptor ( ignore line 14,15,16 ). line 18 again reads the file descriptor flags and line 19 sets the flag for read/write ( O_RDWR ) and line 21
resets the file descriptor flags to the defaults.

line 22 and 23 create a pair of file descriptors pointing to a pipe inode ( the first is for reading, the second for writing ). the clone call on line 24 does interesting stuff, it creates a new process with is 5894 and line 25 waits for the new process to change its state ( the child_stack=0 indicates that a process is created, not a thread ), in other words it waits for the process to exit.

if we now do a check if the process is there, you will notice that no process with this PID exists ( this is probably because it is the PID of the clone itself ):

ps -ef | grep 5894
root 5984 5611 0 15:58 pts/3 00:00:00 grep 5894

but what exists, is PID+1 ( which you can see on line 32 ):

ps -ef | grep 5895
oracle 5895 1 0 15:44 ? 00:00:00 oracleDB112 (LOCAL=NO)
root 5997 5611 0 16:00 pts/3 00:00:00 grep 5895

… which is our connection to the database. if you check this process you will see that the socket 14 is now available in the newly created process:

ls -la /proc/5895/fd
total 0
dr-x------ 2 root root 0 Apr 4 15:44 .
dr-xr-xr-x 6 oracle asmadmin 0 Apr 4 15:44 ..
lr-x------ 1 root root 64 Apr 4 16:00 0 -> /dev/null
l-wx------ 1 root root 64 Apr 4 16:00 1 -> /dev/null
lrwx------ 1 root root 64 Apr 4 16:00 14 -> socket:[102504]
l-wx------ 1 root root 64 Apr 4 16:00 2 -> /dev/null
lr-x------ 1 root root 64 Apr 4 16:00 3 -> /dev/null
lr-x------ 1 root root 64 Apr 4 16:00 4 -> /dev/null
lr-x------ 1 root root 64 Apr 4 16:00 5 -> /opt/oracle/product/base/11.2.0.3/rdbms/mesg/oraus.msb
lr-x------ 1 root root 64 Apr 4 16:00 6 -> /proc/5895/fd
lr-x------ 1 root root 64 Apr 4 16:00 7 -> /dev/zero

the remaining lines will close some files ( including the listeners file descriptor 14 ) and write some data. i will ignore the rest of the output as it should be clear now, how the listener hands off the connections to the database: it listens to incoming requests on the defined port and creates a new process which is the database connection. that’s all the listener does. once the connection is established there is no more work to do for the listener and it looses control of the newly created process.

just one more thing of interest: the lseek and read calls to file descriptor 7 ( lines 42 and 43 ) are positioning and reading the file containing the tns messages. you can check this with:

strings /opt/oracle/product/base/11.2.0.3/network/mesg/tnsus.msb

this are the messages the listener returns.

happy listening …