Showing posts with label Oracle. Show all posts
Showing posts with label Oracle. Show all posts

Tuesday, May 14, 2013

Installing Oracle 11g Enterprise Edition on Ubuntu 12.10



Required Software’s

VMware Player

Download and install VMware Player (Non-Commercial Software)

Ubuntu 12.10

Download and install Ubuntu 12.10 32 bit or 64 bit based on your Hardware.

XClock Problem

To setup $DISPLAY across the user try running this as root before you do "su - oracle" or other users.
$>xhost local:oracle
$>xhost local:sirishg

Oracle Installation Process

Follow the below link,
Note:
About link works for Ubuntu 32 Bit as well.

Note: Just ignored all above missing packages.





Note: Run the above commands as root user.

Post Installation Errors

Error Message while starting sqlplus
sqlplus: error while loading shared libraries: libclntsh.so.11.1: cannot open shared object file: No such file or directory.
Reason for the Problem
libclntsh.so.11.1 was missing.
Solution
Below solution solve this problem as well.
Error message while starting Net Manager
oracle@ubuntu:~/app/oracle/product/11.2.0/dbhome_1/bin$ ./netca
UnsatisfiedLinkError exception loading native library: njni11
java.lang.UnsatisfiedLinkError: /home/oracle/app/oracle/product/11.2.0/dbhome_1/lib/libnjni11.so: libclntsh.so.11.1: cannot open shared object file: No such file or directory
java.lang.UnsatisfiedLinkError: jniGetOracleHome
            at oracle.net.common.NetGetEnv.jniGetOracleHome(Native Method)
            at oracle.net.common.NetGetEnv.getOracleHome(Unknown Source)
            at oracle.net.ca.NetCALogger.getOracleHome(NetCALogger.java:230)
            at oracle.net.ca.NetCALogger.initOracleParameters(NetCALogger.java:215)
            at oracle.net.ca.NetCALogger.initLogger(NetCALogger.java:130)
            at oracle.net.ca.NetCA.main(NetCA.java:404)

Error: jniGetOracleHome
Oracle Net Services configuration failed.  The exit code is 1
Reason for the Problem
libclntsh.so.11.1 was missing.
Solution
Go to terminal and locate the missing file libclntsh.so.11.1 using below command,
Copy the above file to $ORACLE_HOME\lib (/home/oracle/app/oracle/product/11.2.0/dbhome_1/lib/)

Post Installation Configurations

Database Listener Configuration

If we need to configure database listener go to $ORACLE_HOME/lib and execute shell called netca

Create new Database

Exception while creating new Database

 

References

This is Important One

Solving compilation/link errors

Some issues should appear during installation.
If you have not created the symbolic links above, you will have to solve error lib//libagtsh.so: undefined reference to `nnfyboot' in make: rdbms/lib/dg4odbc] Error 1. For this, create the symbolic links and execute the commands:
export ORACLE_HOME=/opt/oracle/Oracle11gee/product/11.2.0/dbhome_1
cd $ORACLE_HOME/lib
ln -s libclient11.a libagtsh.a
$ORACLE_HOME/bin/genagtsh $ORACLE_HOME/lib/libagtsh.so 1.0


Now we will encounter many errors due to indirect library linking:
To solve error # libnnz11.so: could not read symbols: Invalid operation /sysman/lib/ins_emagent.mk, enter the command:
export ORACLE_HOME=/opt/oracle/Oracle11gee/product/11.2.0/dbhome_1
sed -i 's/^\(\s*\$(MK_EMAGENT_NMECTL)\)\s*$/\1 -lnnz11/g' $ORACLE_HOME/sysman/lib/ins_emagent.mk

To solve error # nsglsn.c:(.text+0xc29): undefined reference to `ons_subscriber_close' /network/lib/ins_net_server.mk, enter the command:
sed -i 's/^\(TNSLSNR_LINKLINE.*\$(TNSLSNR_OFILES)\) \(\$(LINKTTLIBS)\)/\1 -Wl,--no-as-needed \2/g' $ORACLE_HOME/network/lib/env_network.mk
To solve error # libocrutl11.so: undefined reference to `lfifcp' rdbms/lib/ins_rdbms.mk, enter the commands:
sed -i 's/^\(ORACLE_LINKLINE.*\$(ORACLE_LINKER)\) \(\$(PL_FLAGS)\)/\1 -Wl,--no-as-needed \2/g' $ORACLE_HOME/rdbms/lib/env_rdbms.mk

You also need to execute the following commands to prevent from later errors:
sed -i 's/^\(\$LD \$LD_RUNTIME\) \(\$LD_OPT\)/\1 -Wl,--no-as-needed \2/g' $ORACLE_HOME/bin/genorasdksh
sed -i 's/^\(\s*\)\(\$(OCRLIBS_DEFAULT)\)/\1 -Wl,--no-as-needed \2/g' $ORACLE_HOME/srvm/lib/ins_srvm.mk

Starting up the database

Once your server have been restarted, your database may not start. To solve this issue, first check in /etc/oratab that it has the 'Y' flag, if not, set it.
sudo gedit /etc/oratab

And replace N by Y
orcl:/opt/oracle/Oracle11gee/product/11.2.0/dbhome_1:Y

Manually starting up the database

Finally, to manually start Oracle, run these commands as oracle user
dbstart $ORACLE_HOME

Manually shutting down the database


dbshut $ORACLE_HOME

Friday, October 22, 2010

oracle queries

Covert Date to Timestamp
  1. TO_TIMESTAMP(SYSDATE,'DD-MM-RRRR HH24:MI:SS') FROM DUAL;

Oracle Dates difference in Days

create or replace
function date_diff( p_date1 DATE , p_date2 DATE)
return char
is
 Years        NUMBER;
 months       NUMBER;
 days         NUMBER;
 day_fraction NUMBER;
 hrs          NUMBER;
 mints        NUMBER;
 sec          NUMBER;
begin
 Years :=trunc( months_between( p_date2 , p_date1 ) /12 );
 months:=mod( trunc( months_between( p_date2, p_date1 ) ), 12 );
 days  :=trunc(p_date2 - add_months(p_date1,trunc(months_between(p_date2,p_date1) )));
 day_fraction:= (p_date2-p_date1)-trunc(p_date2-p_date1);
 hrs   :=trunc(day_fraction*24);
 mints :=trunc((((day_fraction)*24)-(hrs))*60);
 sec   :=trunc(mod((p_date2-p_date1)*86400,60));
--Just Retrun days as of now, igonore rest output.
 --return(years||' Years '||months||' Months '||days||' Days '||hrs||' Hours '||mints||' Minutes '||sec||' Seconds');
 return(days);
end;

Oracle timestamp difference in Days

create or replace
FUNCTION timestamp_diff
(
start_time_in TIMESTAMP
, end_time_in TIMESTAMP
)
-- RETURN NUMBER
RETURN VARCHAR
AS
l_days NUMBER;
l_hours NUMBER;
l_minutes NUMBER;
l_seconds NUMBER;
l_milliseconds NUMBER;
BEGIN
SELECT extract(DAY FROM end_time_in-start_time_in)
, extract(HOUR FROM end_time_in-start_time_in)
, extract(MINUTE FROM end_time_in-start_time_in)
, extract(SECOND FROM end_time_in-start_time_in)
INTO l_days, l_hours, l_minutes, l_seconds
FROM dual;
--l_milliseconds := l_seconds*1000 + l_minutes*60*1000 + l_hours*60*60*1000 + l_days*24*60*60*1000;
--RETURN ' Milliseconds ' || l_milliseconds;
l_milliseconds := (l_seconds - FLOOR(l_seconds) ) * 1000000 ; -- + l_minutes*60*1000 + l_hours*60*60*1000 + l_days*24*60*60*1000;
--RETURN 'Days '|| l_days ||' Hours '|| l_hours||' Minutes '||l_minutes||' Seconds '||FLOOR(l_seconds)||' Milliseconds '|| l_milliseconds;
--Just return days from now.
RETURN l_days;
END;