Nethence Newdoc Olddoc Lab Your IP BBDock  

Warning: those guides are mostly obsolete, please have a look at the new documentation.


Oracle database creation
In brief
- create a database
- create tablespaces and schema
- configure the listener and tnsnames
- relocate 3rd control file
- rename .log to .rdo
- add new members to the redo log groups
(-) database creation scripts
Database creation (9i)
Modify the DB assistant script,
which dbca
vi /oracle/9.2/ora92/bin/dbca
$JRE_DIR/bin/jre -native ...
Note. otherwise it may crash like,
/oracle/9.2/ora92/bin/dbca: line 124: 1789 Segmentation fault $JRE_DIR/bin/jre -DORACLE_HOME=$OH -DJDBC_PROTOCOL=thin -mx64m -classpath $CLASSPATH oracle.sysman.assistants.dbca.Dbca $ARGUMENTS
Launch the DB create assistant (with X11 forwarding enabled),
with e.g. those settings,
database templates: new database
database features: uncheck everything but examples schemas
database features / standard database features: uncheck everything
connection options: dedicated server mode
initialization parameters / memory: custom
. shared pool: 80 MB
. buffer cache: 24 MB
. java pool: 0 MB
. large pool: 8 MB
. pga: 24 MB
initialization parameters / character sets: choose: UTF8
initialization parameters / db sizing: block size: 8 KB
initialization parameters / short area size: 524288 Bytes
creation options: +generate database creation scripts
Note. if you select 'typical', choose OLTP for real-time insert, update and delete queries
Note. if you select 'typical', choose Data Wharehousing for housing data and complex queries
Database creation (10gR2/11gR1)
As oracle user with X11 forwarding enabled, launch the DB assistant,
change e.g.,
database templates: Custom Database
management options: uncheck enterprise manager
database content (9/12) 10g > database components tab: uncheck everything
database content (9/12) 11g > database components tab > standard database components
uncheck everything
initialization parameters (10/12) > character sets > character sets: AL32UTF8
initialization parameters (10/12) > character sets > default language: American
initialization parameters (10/12) > character sets > default territory: United States
creation options (12/12): +generate database creation scripts
Note. if you want ASM, it's preferable to let it have its own Oracle home first
Note. it creates the server parameter file,
ls -l $ORACLE_HOME/dbs/spfiledbname.ora
Note. it adds database entries into the "oratab" file.
Configure the listener and tnsnames
Add dbname to the listener,
cd $ORACLE_HOME/network/admin
vi listener.ora
add into the SID_LIST statement,
(GLOBAL_DBNAME = dbname)
(ORACLE_HOME = /u01/product/10.2.0/ora1020)
(SID_NAME = dbname)
note. change the path for ORACLE_HOME accordingly
note. otherwise,
netmgr > Local > Listeners
Database services > Add Database
Add dbname to tnsnames,
cd $ORACLE_HOME/network/admin
vi tnsnames.ora
dbname =
(SERVICE_NAME = dbname)
note. change HOSTNAME accordingly
Database creation script
Use an existing script,
cd /oracle/admin/
#rm -rf dbname/
mkdir -p dbname/
cp -R base0/scripts/ dbname/
cd dbname/scripts/
rm -f init.ora*
mv -f
for file in *.sh; do
  sed 's/base0/dbname/g' $file > $file.tmp
  mv -f $file.tmp $file
  chmod +x $file
for file in *.sql; do
  sed 's/base0/dbname/g' $file > $file.tmp
  mv -f $file.tmp $file
grep base0 *
grep BASE0 *
create the new database,
./ >dbname.log 2>dbname.error.log
Ready to go
Check everything's fine :
- shutdown the database
- startup the database
- stop the listener
- start the listener
Or proceed with the init script,
su -
ps aux | grep ora
service dbora stop
cat /u01/product/11.2.0/ora1120/shutdown.log
ps aux | grep ora
service dbora start
cat /u01/product/11.2.0/ora1120/startup.log
ps aux | grep ora
Or simply reboot the server and try to connect using tns (@dbname),
sqlplus USERNAME/PASSWORD@dbname
Minor Oracle version upgrade
After an upgrade e.g. from to, upgrade the database,
shutdown immediate
startup upgrade
spool /u01/upgrade.log
spool off
shutdown immediate
spool /u01/upgradepost.log
spool off

(obsolete, see the new doc)