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