Increase JVM Free Memory for Apache Tomcat 6

1) Go to $CATALINA_HOME/bin

2) Create a file setenv.sh (in Linux) or setenv.bat (in Windows)

3) Enter the line:

JAVA_OPTS="-Xms256m -Xmx512m -XX:MaxPermSize=256m"

4) Restart the server

5) Go to your Tomcat manager >> Complete Server Status and verify if the memory has increased or not.

This will increase free memory available and avoid having the Out of Memory error.

EXP-00091: Exporting questionable statistics.

If you receive this warning message when doing database export, you can turn it off by setting STATISTICS=NONE avoid exporting these statistics during export.

Allowing VNC connection in Oracle Enterprise Linux

If you cant remote connect to your database or to VNC it may be due to firewall restrictions.

1) Start VNC server and check which port its connected on by running: netstat -tlnp
2) Edit the iptables file to allow that port.

$ vim /etc/sysconfig/iptables
3) add this line:
RH-Firewall-1-INPUT -p tcp -m state --state NEW -m tcp --dport xxxx -j ACCEPT
BEFORE
RH-Firewall-1-INPUT -j REJECT --reject-with icmp-host-prohibited

Replace the xxxx with the port number you found in step 1.

4) Restart your iptables

$ service iptables restart
If you can't connect to database via SQL developer (ie you are getting a network adaptor error) then try this step as well to allow outside connection.


5) Start VNC Server

$ service vncserver restart

Linux: Find out who is listening on ports

 If you suspect the port you are trying to connect to is being blocked, you can check who is listening on that port using netstat -tnlp.

Better to connect as root for a more comprehensive listing. 

bash-3.2$ netstat -tnlp 


tcp     0      0 0.0.0.0:22              0.0.0.0:*               LISTEN      545/sshd       
tcp        0      0 127.0.0.1:631           0.0.0.0:*               LISTEN      705/cupsd      
tcp6       0      0 :::22                   :::*                    LISTEN      545/sshd       
tcp6       0      0 ::1:631                 :::*                    LISTEN      705/cupsd      

Now you have list of ports that are being used. If you want to find out more about the process that's running on 705, try running:

bash-3.2$ ps -aux | grep 705
 root       705  0.0  0.2   6792  2436 ?        Ss   09:33   0:00 /usr/sbin/cupsd -C /etc/cups/cupsd.conf

Creating a Database Link in Oracle

CREATE DATABASE LINK "TESTLINK"
   CONNECT TO "schema_name" IDENTIFIED BY VALUES 'schema_password'
   USING '(DESCRIPTION =
       (ADDRESS_LIST =
         (ADDRESS = (PROTOCOL = TCP)(HOST = 101.102.103.104)(PORT = 1521))
       )
       (CONNECT_DATA =
         (SID = XE)
       )
     )';
 


1) Replace the schema_name, schema_password, Host and port with your setup.

2) Login to your schema, and run the query above.

Test it by querying "Select * from dual@TESTLINK".

Resolving SP2-0750: You may need to set ORACLE_HOME to your Oracle software directory

If you set up Oracle Database XE 11G in Linux and you encounter the following error when starting up SQL Plus:

$ sqlplus
SP2-0667: Message file sp1<lang>.msb not found
SP2-0750: You may need to set ORACLE_HOME to your Oracle software directory

To resolve it try the following. It's assumed Oracle XE 11G is installed at:

/u01/app/oracle/product/11.2.0/xe/

If you have a different set up, adjust your directory path accordingly.

1) Find out the path to sqlplus
    bash-3.2$ which sqlplus
   /u01/app/oracle/product/11.2.0/xe/bin/sqlplus

2) Set your Oracle_HOME (upto xe directory)

   bash-3.2$ export ORACLE_HOME=/u01/app/oracle/product/11.2.0/xe/

3) Set the path to the directory where sqlplus is located to your PATH variable:

    bash-3.2$ export PATH=/u01/app/oracle/product/11.2.0/xe/bin:$PATH

 
You should now be able to use sqlplus. 

4) Now you need to edit you bash_profile so all these takes effect everytime you log in.

bash-3.2$ cd ~/
bash-3.2$ vim .bash_profile

5) In the .bash_profile you will need to add those export lines so every time you log in the same settings take effect.

bash-3.2$ vim .bash_profile
# .bash_profile

# Get the aliases and functions
if [ -f ~/.bashrc ]; then
        . ~/.bashrc
fi

# User specific environment and startup programs
ORACLE_SID=XE
export ORACLE_SID
ORACLE_HOME=/u01/app/oracle/product/11.2.0/xe
export ORACLE_HOME
PATH=$ORACLE_HOME/bin:$PATH
export PATH
unset USERNAME


6) Run the following:
bash-3.2$ . .bash_profile
 
After following these 6 steps you should be able to connect to sqlplus any time.

Starting and Stopping Apache HTTPD Server in Linux

To stop HTTPD Service:

$ /sbin/service httpd stop

To Start HTTPD Service:

$ /sbin/service httpd start

ORA-01031: insufficient privileges when importing dump files in Oracle Database

If you create a new user/schema having trouble importing a database dump file (.dmp) because you are encountering:

ORA-01031: insufficient privileges

OR, simple getting this error when creating tables etc.

Just connect so SQLPLUS or SQL Developer as SYSDBA and run the command:

GRANT RESOURCE to schema_name;

You can then run import statements or connect as the new user/schema without encountering 0RA-01031.

PRE-REQUISITE: Must run the grant command from SYSDBA

Hashing values in Oracle Database

ORA_HASH( ) function allows you hash a value. This is one way hash meaning you cannot decrypt it back to its original value.

Example,




SELECT ORA_HASH(column_name) FROM table_name;




If you want to hash a particular string (not column value) then do it this way: 




SELECT ora_hash('some text') FROM DUAL;


This will hash the text 'some text' for you. The value you will get is: 2387593664 which is the hash of 'some text'.