Posts Tagged Oracle

INSTALL ZEND FOR ORACLE ON LINUX

(comes with php configured for Oracle by default)

1. Untar and install:

Download Zend for Oracle from the following site:
https://www.zend.com/core/oem_registration.php?access_code=OracleDB

#tar -xzvf ZendCoreForOracle-v1.5.0-Linux-x86.tar.gz
#cd ZendCoreForOracle-v1.5.0-Linux-x86
#./install

Follow the instructions to install.

Note that in our installation, we will be using the default installation path of Zend for oracle (/usr/local/Zend). Yours could be different.
2. Follow instructions to the end. If you are on Debian or Fedora (worked perfect for Suse!), copy the sysv init.d script as follows:
#cp /usr/local/Zend/apache2/bin/apache/apachectl.ZSAV /etc/init.d/apachectl.Zend
#chmod 755 /etc/init.d/apachectl.Zend
#chkconfig 2345 apachectl.Zend on

Look inside the /etc/apachectl.Zend file and change anything that needs to be changed. If you installed Zend for Oracle in the default directory, then you won’t need to change anything.

3. Then;
To start apache:
#service apachectl.Zend start
To stop apache:
#service apachectl.Zend stop

4. Alternatively, you may use a modified apachectl.Zend file. This allows you to start apache with ssl directly. Please replace the existing contents of the apachectl.Zend script with those below:

Replace the option “start” with the following:
start)
$HTTPD -k start -DSSL
ERROR=$?
;;

Replace the option “restart” with the following:
restart)
$HTTPD -k stop
$HTTPD -k start -DSSL
ERROR=$?
;;

Replace the option “graceful” with the following:
graceful)
$HTTPD -k -DSSL graceful
ERROR=$?
;;

CREATE A CERTIFICATE FOR YOUR WEBSITE
In our case, the apache configuration files were installed in the /usr/local/Zend/apache2 directory. Yours are most likely installed in /etc/apache2, so just map the directories as appropriate.

#cd /usr/local/Zend/apache2/conf
#mkdir ssl.crt
#mkdir ssl.key
#mkdir ssl.csr

Option 1 – This will create a certificate key with a password. You are advised to remove the password, so that you don’t have to input it everytime you restart apache. This is because apache might restart in the middle of the night, while there is no one to input the password.
#openssl genrsa -des3 -out ssl.key/yourdomain.co.ke.password.key 1024
(generate key with password)
#openssl rsa -in ssl.key/yourdomain.co.ke.password.key -out ssl.key/yourdomain.co.ke.key
(remove password from key)

Option 2 – This will create a certificate key without a password directly.
#openssl genrsa -out ssl.key/yourdomain.co.ke.key 1024
(generate key without password directly)

Create a certificate request key. This certificate request .csr file is what you will send to Verisign or Thawte so that they can verify it for you. In the meanwhile you complete your application and they send your certificate, we will create one for ourselves by using a self-signed certificate. This will still encrypt the channel for you, even though users will see the certificate as coming from an untrusted source.
#openssl req -new -key ssl.key/yourdomain.co.ke.key -out ssl.csr/yourdomain.co.ke.csr

Create the actual certificate using the certificate request file you created above:
#openssl x509 -req -days 365 -in ssl.csr/yourdomain.co.ke.csr -signkey ssl.key/yourdomain.co.ke.key -out ssl.crt/yourdomain.co.ke.crt

Examine the just created certificates to ensure they were created without errors:
#openssl rsa -noout -text -in ssl.key/yourdomain.co.ke.key
#openssl req -noout -text -in ssl.csr/yourdomain.co.ke.csr
#openssl x509 -noout -text -in ssl.crt/yourdomain.co.ke.crt

Configure your httpd.conf or whatever httpd config file you use to Listen on port 443 ie
Listen 443

Configure the virtual hosts as appropriate for your system, and don’t forget to switch on SSL.
The important options to configure are: SSLEnable, SSLCertificateFile(use the crt file we created above) and SSLCertificateKeyFile(use the key file we created above). Refer to the apache documentation. For example, your options should look like this:

SLCertificateFile /usr/local/Zend/apache2/conf/ssl.crt/yourdomain.co.ke.crt
and
SSLCertificateKeyFile /usr/local/Zend/apache2/conf/ssl.key/yourdomain.co.ke.key

If you are using zend for oracle apache, to start the webserver with ssl support you need to provide a special start option, start-ssl:
#/etc/init.d/apachectl.Zend start-ssl

If you are using our modified script, you can call the options using the service commands as follows:
#service apachectl.Zend start
(this will start apache with SSL support by default)

It is important that you turn off directory listing. To do this, ensure you have the following Options Directive in your httpd.conf or other apache default conf file:
Options -Indexes

REDIRECT USERS DIRECTLY INTO HTTPS
1. Inside your httpd.conf file we need to allow rewrite rules from the .htaccess file. So change:

AllowOverride None
to
AllowOverride AllowOverride FileInfo Options

Now add the following Options directives:
Options +FollowSymLinks +SymLinksIfOwnerMatch

1. Create a .htaccess file in your DocumentRoot
2. Add something similar to this in your file:
In our case the DocumentRoot was /usr/local/Zend/apache2/htdocs. So we did the following:

#cd /usr/local/Zend/apache2/htdocs
#vi .htaccess

Then add the following 3 lines:
RewriteEngine On
RewriteCond %{HTTPS} off
RewriteRule (.*) https://%{HTTP_HOST}%{REQUEST_URI}

All http users will now be redirected to the https page by default.

Comments (4)

Oracle on Linux

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

4.

#phpize
#./configure --with-oci8=shared,instantclient, \
/opt/oracle/instantclient/instantclient_10_2
(to use instant client)
or
./configure --with-oci8=$ORACLE_HOME
(To use oracle home if you have installed Oracle locally)

Debian installation URL:
http://samgerstenzang.com/blog/archives/2006/09/howto-installing-oracle-xe-on-ubuntu-with-php

USE ZENDCORE ALREADY PRECOMPILED OCI8 MODULE
1. Download zendcore:
http://www.zend.com/products/zend_core/zend_core_for_oracle

2. Use Zendcore as it is or alternatively after installation copy it's own oci8.so module into your own distros php extensions directory which in my ase was /usr/lib/php5/extensions:

#cd /usr/local/Zend/Core/lib/php/20050922/
#cp oci8.so /usr/lib/php5/extensions

TOP ORACLE TOOLS FOR LINUX:
After many hours of testing all the tools out there, the following tools got the best revs in my opinion:

1. yaSQL
(Download DBD::Oracle module from http://search.cpan.org/dist/DBD-Oracle/ to use)
2. tORA
3. SuperQuaiL
4. m2o
(mysql sql script to oracle script converter)
5. OracleEditor.php

REFERENCES
1. http://www.cuddletech.com/articles/oracle/node34.html (*****)
2. http://www.oracleadvice.com/Tips/isqlplus.htm
3. http://www.oracle.com/technology/pub/articles/smiley_10gdb_install.html
4. http://www.stanford.edu/dept/itss/docs/oracle/10g/server.101/b12170/ch2.htm
5. http://www.oracleopensource.com/archives/2005/06/index.html
6. http://saturn.uab.es/win.920/a95491/create.htm (Create Oracle database)
7. http://www.idevelopment.info/data/Oracle/DBA_tips/Database_Administration/DBA_37.shtml
8. http://www.examcram2.com/articles/article.asp?p=350920&seqNum=5&rl=1
9. http://orafaq.com/node/6
10. http://www.dbmotive.com/oracle_error_codes.php (Error codes debugging)
11. http://www.stanford.edu/dept/itss/docs/oracle/10g/java.101/b10979/basic.htm (Authentication)
12. http://www.oreillynet.com/oracle/os_dir/commandline.csp
13. http://orafaq.com/faqplus.htm
14. http://www.oracle.com/technology/pub/notes/technote_php_instant.html (php-oracle)
15. http://ubuntuforums.org/showthread.php?t=92528 (php-oracle)
16. http://www.dynamicwebpages.de/tutorial/27/
17. http://creole.phpdb.org/trac/browser/trunk/classes/creole/drivers/oracle?rev=1
18. http://www.psoug.org/reference/system_privs.html (general sql statements)
19. http://www.puschitz.com/InstallingOracle9i.shtml

Comments (5)