Symfony 2 meets Oracle (part 2)

If you have followed the part 1 and downloaded the VM I suggested, it is important to know that your database name is called “XE”. It will be useful in next chapter on Symfony configuration.

Oracle configuration

Open a terminal and start the configuration process with the following command

> /etc/init.d/oracle-xe configure

 Create a worspace with Application Express (simple mode)

Go to Applications > Oracle Database 11G … > Get Started

Click on Application Express and login with the admin user you have previously configured (default user is sys)

Follow the process to create the environment and keep a note of your username and password.

Create a user, tablespace and permissions with the commande line (advanced mode)

Go to Applications > Oracle Database 11G … > Run SQL command line

CREATE USER DUMMYUSER IDENTIFIED BY password
DEFAULT TABLESPACE "YOURTABLESPACE" 
TEMPORARY TABLESPACE "TEMP";

ALTER USER DUMMYUSER QUOTA UNLIMITED ON FFFCNF;

GRANT create procedure, create session, create table,
      create type, create view, create synonym, create trigger, resource TO DUMMYUSER;

Environment variables configuration

Add to /etc/profile the following configuration

ORACLE_HOME=/u01/app/oracle/product/11.2.0/xe
PATH=$PATH:$ORACLE_HOME/bin
export ORACLE_HOME
export ORACLE_SID=XE
export PATH

Then reload your profile with

> source /etc/profile

Finally restart Oracle with the following command

> /etc/init.d/oracle-xe restart

You can test the connection with the following PHP script

$conn = oci_connect('username', 'password', 'IP:1521/XE');
if (!$conn) {
    $e = oci_error();
    trigger_error(htmlentities($e['message'], ENT_QUOTES), E_USER_ERROR);
}

If you have a listener error message read the next paragraph

Listener configuration

We are going to register the listener dynamically so it can start with our Oracle instance.

Stop the listener

> lsnrctl stop

Remove the listener.ora (just move it to keep a copy)

> mv /u01/app/oracle/product/11.2.0/xe/network/admin/listener.ora /u01/app/oracle/product/11.2.0/xe/network/admin/listener.ora.old

Restart the service

> lsnrctl start

Connect to the database

> sqlplus
Enter user-name: sys as sysdba

(Re)start an Oracle instance (shutdown first if there is one running)

SQL> shutdown
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  204591104 bytes
Fixed Size                  2225032 bytes
Variable Size             171969656 bytes
Database Buffers           25165824 bytes
Redo Buffers                5230592 bytes
Database mounted.
Database opened.
SQL> exit
Disconnected from Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production

Check the status listener

> lsnrctl status

The following line should appear

Service "XE" has 1 instance(s).
Instance "XE", status READY, has 1 handler(s) for this service...

Troubleshooting

If you get the following error

ORA-04031: unable to allocate 3896 bytes of shared memory ("shared pool","update seq$ set increment$=:...","sga heap(1,0)","kglsim object batch") 

Connect with sqlplus and run the following queries

SQL> alter system set SHARED_POOL_RESERVED_SIZE = '64M' scope=spfile;
SQL> alter system set SHARED_POOL_SIZE = '200M' scope=spfile;

If you have messed up with your Oracle configuration and cannot start the server anymore you can access your configuration file with the following procedure

SQL> create pfile='/tmp/pfile' from spfile;
Editer le fichier /tmp/pfile en modifiant les valeurs concernées
SQL> create spfile from pfile='/tmp/pfile';
SQL> startup

Tip to know all the size parameters

SQL> show parameter size;

Références

http://jamessmith73.wordpress.com/james-smiths-java/just-like-that/oracle-xe-database-on-ubuntu/
http://edstevensdba.wordpress.com/2011/07/30/exploring-the-local_listener-parameter/
http://www.nielskrijger.com/2012/06/creating-tablespace-and-user-in-oracle.html
https://forums.oracle.com/forums/thread.jspa?threadID=2421429
http://oraclequirks.blogspot.co.uk/2008/05/ora-02095-specified-initialization.html