Nethence Newdoc Olddoc Lab Your IP BBDock  


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

UnixWindowsOracleObsoleteHardwareDIYMechanicsScriptsConfigs

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,
unset ORACLE_SID
which dbca
vi /oracle/9.2/ora92/bin/dbca
:128
change,
$JRE_DIR/bin/jre -native ...
Ref http://ftp.novell.com/partners/oracle/docs/9205_sles9_install.pdf
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),
dbca
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,
unset ORACLE_SID
dbca
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,
(SID_DESC =
(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
add,
dbname =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = HOSTNAME)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(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 base0.sh dbname.sh
for file in *.sh; do
  sed 's/base0/dbname/g' $file > $file.tmp
  mv -f $file.tmp $file
  chmod +x $file
done
for file in *.sql; do
  sed 's/base0/dbname/g' $file > $file.tmp
  mv -f $file.tmp $file
done
grep base0 *
grep BASE0 *
create the new database,
./dbname.sh >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 10.2.0.1 to 10.2.0.4, upgrade the database,
shutdown immediate
startup upgrade
@/u01/product/10.2.0/ora1020/rdbms/admin/utlu102i.sql
spool /u01/upgrade.log
@/u01/product/10.2.0/ora1020/rdbms/admin/catupgrd.sql
spool off
shutdown immediate
startup
spool /u01/upgradepost.log
@/u01/product/10.2.0/ora1020/rdbms/admin/utlrp.sql
spool off
exit
 

(obsolete, see the new doc)