Relocate all data files by re-creating controlfile

Today I installed Oracle 9.2.0.4 software on CentOS4.7. During the installation, when starting the runInstaller, I encounter the following error:

Error occurred during initialization of VM
Unable to load native library: /tmp/OraInstall2003-10-25_03-14-57PM/jre/lib/i386/libjava.so:
symbol __libc_wait, version GLIBC_2.0 not defined in file libc.so.6 with link time reference

By google, I found the following info:

"To resolve the __libc_wait symbol issue, download the p3006854_9204 patch p3006854_9204_LINUX.zip from http://metalink.oracle.com. See bug 3006854 for more information."

So I downloaded and applied that patch. The installation succeeded.

Alter creating a 9i database using DBCA, I found that all files are located at '/u03/oracle/oradata/DBT92/DBT92'

for example:

'/u03/oracle/oradata/DBT92/DBT92/system01.dbf',
'/u03/oracle/oradata/DBT92/DBT92/undotbs01.dbf',
'/u03/oracle/oradata/DBT92/DBT92/cwmlite01.dbf',
'/u03/oracle/oradata/DBT92/DBT92/drsys01.dbf',
'/u03/oracle/oradata/DBT92/DBT92/example01.dbf',
'/u03/oracle/oradata/DBT92/DBT92/indx01.dbf',
'/u03/oracle/oradata/DBT92/DBT92/odm01.dbf',
'/u03/oracle/oradata/DBT92/DBT92/tools01.dbf',
'/u03/oracle/oradata/DBT92/DBT92/users01.dbf',
'/u03/oracle/oradata/DBT92/DBT92/xdb01.dbf'

I decided to relocate them to /u03/oracle/oradata/DBT92/.

The following steps were taken to achieve this goal:

1. create pfile from spfile
edit pfile, modify control file path by removing one 'DBT92'

2. Backup control file to trace, edit the trace file, name it as cr_ctrl.sql.
---- contents of cr_ctrl.sql -----

CREATE CONTROLFILE DATABASE "DBT92" RESETLOGS NOARCHIVELOG
-- SET STANDBY TO MAXIMIZE PERFORMANCE
MAXLOGFILES 50
MAXLOGMEMBERS 5
MAXDATAFILES 100
MAXINSTANCES 1
MAXLOGHISTORY 226
LOGFILE
GROUP 1 '/u03/oracle/oradata/DBT92/redo01.log' SIZE 100M,
GROUP 2 '/u03/oracle/oradata/DBT92/redo02.log' SIZE 100M,
GROUP 3 '/u03/oracle/oradata/DBT92/redo03.log' SIZE 100M
-- STANDBY LOGFILE
DATAFILE
'/u03/oracle/oradata/DBT92/system01.dbf',
'/u03/oracle/oradata/DBT92/undotbs01.dbf',
'/u03/oracle/oradata/DBT92/cwmlite01.dbf',
'/u03/oracle/oradata/DBT92/drsys01.dbf',
'/u03/oracle/oradata/DBT92/example01.dbf',
'/u03/oracle/oradata/DBT92/indx01.dbf',
'/u03/oracle/oradata/DBT92/odm01.dbf',
'/u03/oracle/oradata/DBT92/tools01.dbf',
'/u03/oracle/oradata/DBT92/users01.dbf',
'/u03/oracle/oradata/DBT92/xdb01.dbf'
CHARACTER SET WE8ISO8859P1
;
--- end of cr_ctrl.sql --------------

2. shutdown database
move all data files, redo log file to /u03/oracle/oradata/DBT92/

3. startup nomount with the pfile

4. Execute cr_ctrl.sql

5. Issue: alter database open resetlogs

6. Add tempfile
SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '/u03/oracle/oradata/DBT92/temp01.dbf'
SIZE 41943040 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;

7. create spfile from pfile
bounce the db to use spfile

SSH tunneling

Our team is taking over a new application. This application is hosted on the servers that adopt SSH tunneling for remote access. This feature I have never experienced before.

Here is a good arcticle to get started: Securing Oracle Network Traffic by Roger Schrag

My simple test succeeded:

#####  -- create the tunnel

ssh2 -l username -L 9902: remote_host:1521 remote_host


##### --  test remote connection

C:\Documents and Settings\Yu>sqlplus system@DBCONNSTRG

SQL*Plus: Release 10.2.0.1.0 - Production on Fri Feb 6 13:25:20 2009

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

Enter password:

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select name from v$database;

NAME
---------
xxxx


####  My tnsname.ora entry:  I use 9902 because 1521 is for my local database lisenter

DBCONNSTRG =
   (DESCRIPTION =
      (ADDRESS_LIST =  (ADDRESS = (PROTOCOL= TCP)
                                  (HOST = 127.0.0.1)
                                   (PORT = 9902)
                        ) )
      (CONNECT_DATA = (SID = DBSIDNAME)(SERVER = DEDICATED)))

[Ed. Sep 17 2009: to make SSH tunneling work, on the server side we may want to do:
in /etc/ssh/sshd_config change AllowTcpForwarding from no to yes then recycle sshd ]

Email attachment from Unix server

I found that I need to do this. Googled, found this document: Creating email with a text message and an attachment from standard UNIX.

I tested on my server, if only need the attachment, I can do:

$ uuencode pfile_sac.txt pfile_sac.txt | mailx -s "pfile_sac" myname@mycompany.com

shell script tip: wait

Shell scripting tip: wait

I have a task to clone two databases. I need to scp data files with 5 parallel sessions. This can be accomplished by using 'wait'.

The script looks like:

#!/bin/ksh

echo " tbs 1 starts  `date` "
scp /v03/oradata/mydb/tbs34.dbf   myserver01:/v03/oradata/mydb/  &                
scp /v04/oradata/mydb/tbs05.dbf   myserver01:/v04/oradata/mydb/  &                
scp /v04/oradata/mydb/tbs06.dbf   myserver01:/v04/oradata/mydb/  &                
scp /v04/oradata/mydb/tbs07.dbf   myserver01:/v04/oradata/mydb/  &                
scp /v04/oradata/mydb/tbs08.dbf   myserver01:/v04/oradata/mydb/  &                
wait

echo " tbs 2 starts  `date` "
scp /v04/oradata/mydb/tbs09.dbf   myserver01:/v04/oradata/mydb/  &                
scp /v04/oradata/mydb/tbs10.dbf   myserver01:/v04/oradata/mydb/  &                
scp /v04/oradata/mydb/tbs11.dbf   myserver01:/v04/oradata/mydb/  &                
scp /v04/oradata/mydb/tbs12.dbf   myserver01:/v04/oradata/mydb/  &                
scp /v04/oradata/mydb/tbs04.dbf   myserver01:/v04/oradata/mydb/  &                
wait

echo " tbs 3 starts  `date` "
scp /v04/oradata/mydb/tbs17.dbf   myserver01:/v04/oradata/mydb/  &                
scp /v04/oradata/mydb/tbs26.dbf   myserver01:/v04/oradata/mydb/  &                
scp /v04/oradata/mydb/tbs27.dbf   myserver01:/v04/oradata/mydb/  &                
scp /v04/oradata/mydb/tbs28.dbf   myserver01:/v04/oradata/mydb/  &                
scp /v04/oradata/mydb/tbs16.dbf   myserver01:/v04/oradata/mydb/  &                
wait


--- truncated -------

In this script, five processes will be executed at background parallelly. The next five processes will start until all the previous background processes finish.

ORA-27101: shared memory realm does not exist

I created a 10g database manually on the CentOS4.7 virtual machine. I have no problem to connect as SYS or SYSTEM on the server,
however, when I configured the listner and tried to connect remotely or on the server with the connection string, I got ORA-27101 error.
i.e.

vmlinux1:/app/oracle/product/10.2.0/db_1/network/admin [dbt10g] $ sqlplus system@dbt10g 

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Jan 27 22:04:34 2009

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

Enter password:
ERROR:
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
Linux Error: 2: No such file or directory

It took me a while to figure out the reason. My ORACLE_SID is in upper case, however I used lower case sid name in the listener.ora, i.e.

$ cat listener.ora      
LISTENER =
  (ADDRESS_LIST=
        (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.233.128)(PORT=1521))
        (ADDRESS=(PROTOCOL=ipc)(KEY=PNPKEY)))

SID_LIST_LISTENER=
   (SID_LIST=
        (SID_DESC=
         (GLOBAL_DBNAME=dbt10g)
          (SID_NAME=dbt10g)
        )

   )

Atfer changing them to upper case, I fixed the problem.