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

Using OPatch to query inventory

servername:TOY10G > opatch lsinventory -all
Invoking OPatch 10.2.0.3.0

Oracle interim Patch Installer version 10.2.0.3.0
Copyright (c) 2005, Oracle Corporation. All rights reserved..


Oracle Home : /oracle/product/10.2.0/db_1
Central Inventory : /oracle/product/oraInventory
from : /var/opt/oracle/oraInst.loc
OPatch version : 10.2.0.3.0
OUI version : 10.2.0.3.0
OUI location : /oracle/product/10.2.0/db_1/oui
Log file location :
/oracle/product/10.2.0/db_1/cfgtoollogs/opatch/opatch2007-04-07_08-55-45
AM.log

Lsinventory Output file location :
/oracle/product/10.2.0/db_1/cfgtoollogs/opatch/lsinv/lsinventory2007-04-
07_08-55-45AM.txt

---------------------------------------------------------------------
List of Oracle Homes:
Name Location
ORACLE_HOME1 /oracle/product/8.1.7.4
ORACLE_HOME2 /oracle/product/9.2.0
ORACLE_HOME9206 /oracle/product/9.2.0.6
ORACLE_HOME3 /oracle/product/10.2.0/db_1
ORACLE_HOME9208 /oracle/product/9.2.0.8
agent10g /oracle/product/10.2.0/OEM/agent10g

Installed Top-level Products (2):

Oracle Database 10g
10.2.0.1.0
Oracle Database 10g Release 2 Patch Set 2
10.2.0.3.0
There are 2 products installed in this Oracle Home.


Interim patches (3) :

Patch 5752399 : applied on Tue Jan 30 14:16:07 CST 2007
Created on 12 Jan 2007, 20:07:35 hrs US/Pacific
Bugs fixed:
5752399

Patch 5556081 : applied on Tue Jan 30 14:14:16 CST 2007
Created on 9 Nov 2006, 22:20:50 hrs PST8PDT
Bugs fixed:
5556081

Patch 5557962 : applied on Tue Jan 30 14:14:04 CST 2007
Created on 9 Nov 2006, 23:23:06 hrs PST8PDT
Bugs fixed:
4269423, 5557962, 5528974


-----------------------------------------------------------------

OPatch succeeded.

Intelligent Agent starting problem under Linux (FC3)- Applying Patch to fix

Jul 22, 2006 2:22PM
Problem: Trying to start intelligent agent, failed as shown below:
------------------------------------------------------------
[oracle@YS02 bin]$ agentctl start agent
DBSNMP for Linux: Version 9.2.0.4.0 - Production on 22-JUL-2006 14:20:19
Copyright (c) 2003 Oracle Corporation. All rights reserved.
Starting Oracle Intelligent Agent.../opt/app/oracle/product/9.2.0.4/bin/dbsnmpwd : line 156: 3891 Segmentation fault nohup $ORACLE_HOME/bin/dbsnmp $* >>$DB SNMP_WDLOGFILE 2>&1
/opt/app/oracle/product/9.2.0.4/bin/dbsnmpwd: line 156: 3904 Segmentation fault nohup $ORACLE_HOME/bin/dbsnmp $* >>$DBSNMP_WDLOGFILE 2>&1
/opt/app/oracle/product/9.2.0.4/bin/dbsnmpwd: line 156: 3916 Segmentation fault nohup $ORACLE_HOME/bin/dbsnmp $* >>$DBSNMP_WDLOGFILE 2>&1
/opt/app/oracle/product/9.2.0.4/bin/dbsnmpwd: line 156: 3928 Segmentation fault nohup $ORACLE_HOME/bin/dbsnmp $* >>$DBSNMP_WDLOGFILE 2>&1
-----------------------------------------------------------------------------------------------

Solution: Applying Patch

Jul 23, 2006 11:00 AM -- fix the intelligent agent starting problem
(1) Install OPatch user ORACLE_HOME
(2) Applying patch p3238244
% unzip
% cd 3238244
% $ORACLE_HOME/OPatch/opatch apply
(3) To find which makefile handles the linking of dbsnmp:

% find $ORACLE_HOME -name "*.mk" xargs grep -l dbsnmp
/opt/app/oracle/product/9.2.0.4/network/lib/ins_oemagent.mk
/opt/app/oracle/product/9.2.0.4/network/lib/env_oemagent.mk
(4) relink
$ su - oracle
$ cd $ORACLE_HOME/network/lib
$ make -f ins_oemagent.mk install

(5) start the agent successfully
[oracle@YS02 lib]$ agentctl start agent
DBSNMP for Linux: Version 9.2.0.4.0 - Production on 23-JUL-2006 11:13:24
Copyright (c) 2003 Oracle Corporation. All rights reserved.
Starting Oracle Intelligent Agent....
Agent started