Tsavo

I have so far been lucky in not having to get too deep into Oracle since my dealings with Oracle mostly involved running other people’s scripts. But my luck finally run out and here’s a short tutorial based on my experiences…

ORACLE INSTALLATION SUMMARY

1. Create oracle user
#useradd oracle (more options needed if using ldap)

2. Groups: dba, oinstall

#groupadd dba
#groupadd oinstall
#usermod -g oinstall -G dba oracle
#passwd oracle (more options needed if using ldap)

In my case I installed oracle on the oracle home directory /opt/oracle, so change oracle directory ownership:
#chown oracle.dba /opt/oracle
#chmod 755 /opt/oracle

Don’t forget to add user nobody (ie ‘useradd nobody’), but is normally there by default
#su - oracle
#xhost +

oracle@yourmachine>cd databases
oracle@yourmachine>./runInstaller

3. Start and Stop oracle:
oracle@yourmachine>$ORACLE_HOME/bin/dbstart
oracle@yourmachine>$ORACLE_HOME/bin/dbshut

Start and Stop listener:
oracle@yourmachine>$ORACLE_HOME/bin/lsnrctl start
oracle@yourmachine>$ORACLE_HOME/bin/lsnrctl stop

4. Connect to sql database
#sqlplus /nolog
>connect / as sysdba (connect ben/passwd as sysdba)
>startup [nomount]

Or go directly…
#sqlplus ben/passwd@OurTest

5. For Suse Linux;
>Edit /etc/oratab appropriately.
>Edit /etc/profile.d/oracle.sh appropriately especially ensure the $ORACLE_SID is correct
>Edit /etc/init.d/oracle appropriately
>Edit the $ORACLE_HOME_LISTNER variable in $ORACLE_HOME/bin/dbstart file.
Set it to the value of your $ORACLE_HOME directory
>Edit /etc/sysconfig/oracle appropriately

6. Edit
>network/admin/[tnsnames.ora|listener.ora|sqlnet.ora]
and
>db_1/dbs/init.ora
(Under the References below, go to the cuddletech website for a tutorial on how to do so)

7. Oracle Database Management URLs:
> http://localhost:5560/isqlplus (Load and execute scripts)
> http://localhost:5560/isqlplus/dba
> http://localhost:1158/em (Start/Stop oracle instance. Instance start/stop interface)


8.Check version of oracle:
>select banner from v$version;

9.Yasql examples:

#yasql -d 1 -s loyalty sys/passwd@loyalty as sysdba
#yasql -d 1 -s loyalty sys/passwd@loyalty as sysdba @/home/sysadmin/ourtest.sql

10. All databases are created in:/opt/oracle/oradata,
where /opt/oracle was the oracle home directory I chose in my case.
Yours could be different.

11. You have to run dbca(oracle tool to help you create your database) and netca(oracle tool to help you configure your oracle tns and listeners) as root or else it will fail to run.

12. Oracle strace to trace what is happening:
#strace sqlplus sys/passwd@loyalty

13. Test to see if tnsping is working properly for a given database
#tnsping [databasename]

14. You may add the following ORACLE variables to you bash profile eg /etc/profile:

export ORACLE_HOME=/opt/oracle/product/10.2/db_1
export ORACLE_BASE=/opt/oracle
export ORACLE_SID=OurTest
#export LD_LIBRARY_PATH=$ORACLE_HOME/lib
export PATH=$PATH:$ORACLE_HOME/bin

15. Perform an ldconfig for oracle libs:
#echo $ORACLE_HOME >> /etc/ld.so.conf
#ldconfig

CREATE A NEW DATABASE
1. Copy sample init.ora and only change SID (System Identification).
$cp init.ora initOurTest.ora, where OurTest=SID

Change DB_NAME to the DB_NAME you wish to use.
You might possibly need to change your shared_pool_size value.

2.
#sqlplus /nolog
>connect sys/passwd as sysdba
>CREATE SPFILE='/opt/oracle/product/10.2/db_1/dbs/spfileOurTest.ora'
FROM PFILE='/opt/oracle/product/10.2/db_1/dbs/initOurTest.ora';

Make sure initOurTest.ora is owned by oracle (ie chown oracle.users initOurTest.ora)

The SPFILE is a binary used directly by Oracle that stores even temporary changes you may have made to your database (ie ALTER sql statements). That way, even supposedly transient changes can be saved and used the next time Oracle is started so you as the DBA don’t always have to remember what “alter”rations/optimisations you have done and copy it to PFILE. If you wish to revert to the old PFILE, then create SPFILE from PFILE and remount your database with your old SPFILE. You can only edit the PFILE.

3. To see the SPFILE currently in use by Oracle instance:
>SHOW PARAMETERS SPFILE
View the location of your SPFILE
>select name,value from v$parameter where name='spfile';

4. Startup new instance with your own custom spfile:
>SHUTDOWN IMMEDIATE
>STARTUP NOMOUNT \
PFILE='/opt/oracle/product/10.2/db_1/dbs/initOurTest.ora'

(STARTUP NOMOUNT will use the default PFILE or SPFILE)


Examples:

——————————————————–

create database mydb1 controlfile reuse
Logfile
Group 1 ('/mydatabases/mydb1/log01a.log',
'/mydatabase02/mydb1/log01b.log') size 50M,
Group 2 ('/mydatabases/mydb1/log02a.log',
'/mydatabase02/mydb1/log02b.log') size 50M,
Group 3 ('/mydatabases/mydb1/log03a.log',
'/mydatabase02/mydb1/log03b.log') size 50M,
Group 4 ('/mydatabases/mydb1/log04a.log',
'/mydatabase02/mydb1/log04b.log') size 50M
Datafile '/mydatabases/mydb1/mydb1_system.dbf' 250M
AUTOEXTEND ON MAXSIZE 500M
Undo tablespace mydb1_undo1
Datafile '/mydatabases/mydb1/mydb1_undo1.dbf' 50M
Default temporary tablespace mydb1temp tempfile
'/mydatabases/mydb1/mydb1temp1.dbf' size 75M
Extent management local
Character set US7ASCII
MAXLOGFILES 10
MAXLOGMEMBERS 10
MAXLOGHISTORY 1
MAXDATAFILES 500;

———————————————————
——————————————————–

CREATE DATABASE OurTest
USER SYS IDENTIFIED BY passwd
USER SYSTEM IDENTIFIED BY passwd
LOGFILE GROUP 1 ('/opt/oracle/oradata/OurTest/redo01.log') SIZE 10M,
GROUP 2 ('/opt/oracle/oradata/OurTest/redo02.log') SIZE 10M,
GROUP 3 ('/opt/oracle/oradata/OurTest/redo03.log') SIZE 10M
MAXLOGFILES 5
MAXLOGMEMBERS 5
MAXLOGHISTORY 1
MAXDATAFILES 100
MAXINSTANCES 1
CHARACTER SET US7ASCII
NATIONAL CHARACTER SET AL16UTF16
DATAFILE '/opt/oracle/oradata/OurTest/system01.dbf' SIZE 32M REUSE
EXTENT MANAGEMENT LOCAL
SYSAUX DATAFILE '/opt/oracle/oradata/OurTest/sysaux01.dbf' SIZE 32M REUSE
DEFAULT TABLESPACE tbs_1
DEFAULT TEMPORARY TABLESPACE tempts1
TEMPFILE '/opt/oracle/oradata/OurTest/temp01.dbf' SIZE 20M REUSE
UNDO TABLESPACE undotbs
DATAFILE '/opt/oracle/oradata/OurTest/undotbs01.dbf'
SIZE 20M REUSE AUTOEXTEND ON MAXSIZE 500M;

——————————————————–

——————————————————-

INSTALL PHP OCI8 EXTENSION

1. Download Oracle Instant Client from here:
(http://www.oracle.com/technology/tech/oci/instantclient/index.html)
if you do not have oracle installed on the machine. If you have Oracle already installed, skip this step.

#mkdir -p /opt/oracle/instantclient
#cd /opt/oracle/instantclient
#unzip instantclient-basic-linux32-10.2.0.1-20050713.zip
#unzip instantclient-sdk-linux32-10.2.0.1-20050713.zip
#echo /opt/oracle/instantclient >> /etc/ld.so.conf
#ldconfig
#ln -s libclntsh.so.10.1 libclntsh.so
#ln -s libocci.so.10.1 libocci.so


2. Download php oci8 module from here:
http://pecl.php.net/package/oci8,
or use:
#pear5 download oci8
3. untar, get in and