Search This Blog

2015/12/05

Installing Oracle 10 g XE on Debian 8

Adding repository to /etc/apt/sources.list using nano
deb http://oss.oracle.com/debian unstable main non-free
Get Signature for repository
wget http://oss.oracle.com/el4/RPM-GPG-KEY-oracle -O- | sudo apt-key add -
Update Package List
apt-get update
Install Oracle 10
apt-get install oracle-xe
When i was installing i got error regarding package libwxbase3.0 then i installed lower version of it libwxbase2.8-0 then re issued
apt-get install oracle-xe
Install Oracle-Xe Client
apt-get install oracle-xe-client
Check if port in use
lsof -i :8080
Configure Oracle-xe to setup password for SYS & SYSTEM
root@mymachine:/home/myuser# sudo /etc/init.d/oracle-xe configure
Output
Oracle Database 10g Express Edition Configuration
-------------------------------------------------
This will configure on-boot properties of Oracle Database 10g Express
Edition. The following questions will determine whether the database should
be starting upon system boot, the ports it will use, and the passwords that
will be used for database accounts. Press <Enter> to accept the defaults.
Ctrl-C will abort.
Specify the HTTP port that will be used for Oracle Application Express [8080]:
Specify a port that will be used for the database listener [1521]:
Specify a password to be used for database accounts. Note that the same
password will be used for SYS and SYSTEM. Oracle recommends the use of
different passwords for each database account. This can be done after
initial configuration:
Confirm the password:
Do you want Oracle Database 10g Express Edition to be started on boot (y/n) [y]:y
Starting Oracle Net Listener...Done
Configuring Database...Done
Starting Oracle Database 10g Express Edition Instance...Done
Installation Completed Successfully.
To access the Database Home Page go to "http://127.0.0.1:8080/apex"
root@mymachine:/home/myuser#
Where to find tnsnames.ora & listener.ora ?
1) tnsnames.ora
/usr/lib/oracle/xe/app/oracle/product/10.2.0/
server/network/admin/tnsnames.ora
2)listener.ora
/usr/lib/oracle/xe/app/oracle/product/10.2.0/
server/network/admin/listener.ora
How to Start Oracle Service?
start
/etc/init.d/oracle-xe start
stop
/etc/init.d/oracle-xe stop
restart
/etc/init.d/oracle-xe restart
If While starting Sqlplus have error
For/usr/lib/oracle/xe/app/oracle/product/10.2.0/server/bin/nls_lang.sh: 114: [[: not found
as root
gedit /usr/lib/oracle/xe/app/oracle/product/10.2.0/server/bin/nls_lang.sh
Scroll down until you find this:
if [[ -n "$LC_ALL" ]]; then
locale=$LC_ALL
elif [[ -n "$LANG" ]]; then
locale=$LANG
else
locale=
fi
Then change it to this (remove a pair of brackets):
if [ -n "$LC_ALL" ]; then
locale=$LC_ALL
elif [ -n "$LANG" ]; then
locale=$LANG
else
locale=
fi
secondly Change #!/bin/sh to #!/bin/bash in nls_lang.sh
Then click SAVE.
Setting Enviornment variables
open bashhrc file as super user
>gedit ~/.bashrc
add enviornment variables as follows at the bottom of the file opened & save.
export ORACLE_HOME=/usr/lib/oracle/xe/app/oracle/product/10.2.0/server
PATH=$PATH:$ORACLE_HOME/bin
export ORACLE_SID=XE
export PATH
export LD_LIBRARY_PATH=/usr/lib/oracle/xe/app/oracle/product/10.2.0/client/lib
Check Enviornment variables
echo the variable are see if desired values saved or not.On bash prompt run each command below one by one
echo $ORACLE_HOME
echo $ORACLE_SID
echo $PATH
check if listener is running
lsnrctl status
output
LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 05-DEC-2015 13:28:37
Copyright (c) 1991, 2005, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC_FOR_XE)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 10.2.0.1.0 - Production
Start Date 05-DEC-2015 09:55:38
Uptime 0 days 3 hr. 32 min. 58 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Default Service XE
Listener Parameter File /usr/lib/oracle/xe/app/oracle/product/10.2.0/server/network/admin/listener.ora
Listener Log File /usr/lib/oracle/xe/app/oracle/product/10.2.0/server/network/log/listener.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC_FOR_XE)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=server1.localhost.com)(PORT=1521)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
To Start Listener
lsnrctl start
To stop listener
lsnrctl stop
To restart listener
lsnrctl reload
set password to oracle account
sudo passwd oracle
Using tnsping to check listner
tnsping localhost :1521/XE
output
TNS Ping Utility for Linux: Version 10.2.0.1.0 - Production on 05-DEC-2015 15:45:55
Copyright (c) 1997, 2005, Oracle. All rights reserved.
Used parameter files:
Used HOSTNAME adapter to resolve the alias
Attempting to contact (DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=))(ADDRESS=(PROTOCOL=TCP)(HOST=127.0.0.1)(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST=127.0.0.1)(PORT=1521)))
OK (0 msec)
Running Oracle
Login as Oracle in shell prompt then issue below command
sqlplus sys as sysdba
will ask for password give password set in re configuring.
Or
sqlplus SYS/sangram@localhost:1521/XE as SYSDBA
Creating test table
SQL> create table MyTable(id int,name varchar2(50));
Table created.
SQL> insert into MyTable Values(1,'sangram');
SQL> insert into MyTable values(2,'sagar');
SQL> select * from MyTable;
Output
ID NAME
---------- --------------------------------------------------
1 sangram
2 sagar
How to install Oracle Developer tool on Debian 
First install sqldeveloper-package on debian using APT.This package is required for converting sqldeveloper-*-no-jre.zip file that is available on oracle site.
Download sqldeveloper-*-no-jre.zip file from oracle
We will create deb package from this zip file.Make sure latest JDK already installed
then try
make-sqldeveloper-package ~/Downloads/sqldeveloper-*-no-jre.zip
it gives some warning but you get required deb package.Run the package and install.
After installing confirm listener.ora has XE as service see following text in file
(SID_DESC =
(SID_NAME = XE)
(ORACLE_HOME = /usr/lib/oracle/xe/app/oracle/product/10.2.0/server)
)
check XE service is running using lsnrctl status command
create a new user in oracle give him sysdba previlages.
Open Sql Developer try to create new connection with newly created user use BASIC connection TYPE while connecting if you got timezone related error check Sql developer's Help->About then in property tab user.timezone if empty we need to set it in sql developer's config file here
/usr/share/sqldeveloper/ide/bin/ide.conf add (for india)
add below line into ide.conf.
AddVMOption -Duser.timezone=GMT+5.30
Restart machine and recheck SQL Developer is connecting to database.
Timezone can be assigned as IST also for list of possible values of timezone check table V$TIMEZONE_NAMES.

SELECT * FROM V$TIMEZONE_NAMES WHERE tzname LIKE 'Asia/Calcutta'

Troubleshooting
Here are some Queries Usefull for troubleshoting
Getting Global Name
select * from global_name;
Get Current USER
select user from dual;
GET LIST OF DATABASES
select name from v$database;
GET CURRENT INSTANCE NAME
select instance from v$thread;
 

No comments:

Post a Comment