[Linux]Installing Oracle 11g on Ubuntu
Intro
A small tip by wjaouadi, describing the installation of Oracle on Linux Ubuntu Hardy Heron 8.04
Download UBUNTU
The installation was made on a UBUNTU 8.0.4.1 with 2.6.24-24-generic kernel available for download from the following link
Download Oracle
The Oracle version used is 11gr2 available for download from this link
X server configuration
An X server is required for this installation of Oracle. Note that we must disable the "Deny TCP connections to X server"feature to enable the server database to connect to the desktop.
To disable this option from your desktop: go to menu System/Administration/Login Window, then at the Security tab uncheck the option Deny TCP connections to X server. Once this is done it will restart the X server.
Updating packages
We must update UBUNTU anf all packages:
- Connect via ssh
- Switch to root
#sudo -s
- Launch commands
#apt-get update #apt-get dist-upgrade
- Restart
#reboot
Installing of packages needed for pre-installation
- A number of package is required for installing Oracle:
build-essential, libaio1, gawk, ksh, libmotif3, alien, libtool, and lsb-rpm.
*
- To install simply connect through ssh, switch to root and issue the command
#apt-get install build-essential libaio1 gawk ksh libmotif3 alien libtool lsb-rpm
Update shell
It is necessary to update the shell to bash. To do this, just follow these steps:
# cd /bin # ls -l /bin/sh lrwxrwxrwx 1 root root 4 2008-04-28 19:59 /bin/sh -> dash # ln -sf bash /bin/sh # ls -l /bin/sh lrwxrwxrwx 1 root root 4 2008-05-01 22:51 /bin/sh -> bash
Update the configuration of system users
It is necessary to make some changes to system configuration.
- Go to the root directory level
# cd # pwd /root
- Add groups: oinstall, dba, nobody
addgroup oinstall
Adding group 'oinstall' (GID 1001) ...
Done.
# addgroup dba
Adding group 'dba' (GID 1002) ...
Done.
# addgroup nobody
Adding group 'nobody' (GID 1003) ...
Done.
# usermod -g nobody nobody
- Add oracle user
# useradd -g oinstall -G dba -p password -d /home/oracle -s /bin/bash oracle
# passwd oracle
Password changed.
# mkdir /home/oracle
# chown -R oracle:dba /home/oracle
- Creating symbolic links
# ln -s /usr/bin/awk /bin/awk # ln -s /usr/bin/rpm /bin/rpm # ln -s /usr/bin/basename /bin/basename # mkdir /etc/rc.d # for i in 0 1 2 3 4 5 6 S ; do ln -s /etc/rc$i.d /etc/rc.d/rc$i.d ; done
- Creating ORACLE_BASE
# mkdir -p /u01/app/oracle # chown -R oracle:dba /u01
Updating the system configuration
We must also make some change to some system files by adding number of parameters such as size of shared memory to the /etc/sysctl.conf file:
fs.file-max = 65535 kernel.shmall = 2097152 kernel.shmmax = 2147483648 kernel.shmmni = 4096 kernel.sem = 250 32000 100 128 net.ipv4.ip_local_port_range = 1024 65535 net.core.rmem_default = 1048576 net.core.rmem_max = 1048576 net.core.wmem_default = 262144 net.core.wmem_max = 262144
Furthermore, we must integrate the following to the /etc/security/limits.confce file:
oracle soft nproc 2047 oracle hard nproc 16383 oracle soft nofile 1023 oracle hard nofile 65535
In the end it will add the following to /etc/pam.d/login to validate parameters /etc/security/limits.conf:
session required /lib/security/pam_limits.so session required pam_limits.so
Before proceeding to the next stage either restart the system, which is recommended, or force the handling of parameters added via the command sysctl-p:
sysctl -p: # sysctl -p kernel.printk = 4 4 1 7 kernel.maps_protect = 1 fs.inotify.max_user_watches = 524288 vm.mmap_min_addr = 65536 net.ipv4.conf.default.rp_filter = 1 net.ipv4.conf.all.rp_filter = 1 fs.file-max = 65535 kernel.shmall = 2097152 kernel.shmmax = 2147483648 kernel.shmmni = 4096 kernel.sem = 250 32000 100 128 net.ipv4.ip_local_port_range = 1024 65535 net.core.rmem_default = 1048576 net.core.rmem_max = 1048576 net.core.wmem_default = 262144 net.core.wmem_max = 262144
Installing Oracle
Once the above steps completed, simply log on as Oracle user and launch the downloaded the Oracle runInstaller executable (at directory level) and follow the installation steps.
Post-installation configuration
Once installation is completed it will make some updates to the system configuration file:
- Add the following lines to /etc/profile:
export ORACLE_HOME=/u01/app/oracle/product/11.X.Y/db_home1 export PATH=$PATH:/u01/app/oracle/product/11.X.Y/db_home1/bin
- Add the following environment variables:
export ORACLE_BASE=/u01/app/oracle export ORACLE_HOME=/u01/app/oracle/product/11.X.Y/db_home1 export ORACLE_OWNR=oracle export PATH=$PATH:$ORACLE_HOME/bin
[Oracle]Query Optimization
Oracle comes with an optimizer to optimize the plan executing a query. Sometimes the data features in the database are rapidly changing, so that the optimizer (the stats) is updated. In this case, the hints may help.. It is embedded in a query as follows:
SELECT /* + hint(table) */ column1, column2 FROM table WHERE condition;
The hints (hint) can be classified as follows:
- 1. hint for optimizing results:
- ALL_ROWS: it is usually used for batch processing systems or data warehousing. ALL_ROWS tells optimizer to ensure the minimum use of resource for release the full results.
- FIRST_ROWS : The optimizer's goal is to return the first line of the request with a minimum response time.
- CHOOSE: takes into account the statistics if they exist and uses optimizer based on costs.
- RULE: The optimizer determines the execution plan only through strict rules regardless of context (statistics and costs of access) or other hints specified in the request.
- 2. hint for paths:
- CLUSTER: Request the optimizer to reduce the data table clustered.
- FULL: full itinerary for the table.
- ROWID: Research lines by rowid
- INDEX (index) to force the use of the index "index".
- INDEX to calculate the cost for each index and uses the available best.
- INDEX_ASC, INDEX_COMBINE, INDEX_DESC, INDEX_FFS, INDEX_JOIN, NO_INDEX,HASH, AND_EQUAL.
- 3. hint for processing querys: FACT, MERGE, NO_EXPAND, NO_EXPAND_GSET_TO_UNION, NO_FACT, NO_MERGE, NOREWRITE, REWRITE,STAR_TRANSFORMATION, USE_CONCAT.
- 4. hint to the SQL join operations: DRIVING_SITE, HASH_AJ, HASH_SJ, LEADING, MERGE_AJ, MERGE_SJ, NL_AJ, NL_SJ, USE_HASH, USE_MERGE, USE_NL.
- 5. hint for parallel execution: NOPARALLEL, PARALLEL,NOPARALLEL_INDEX, PARALLEL_INDEX, PQ_DISTRIBUTE
- 6. Additional hint: ANTIJOIN, APPEND, BITMAP, BUFFER, CACHE, CARDINALITY, CPU_COSTING,DYNAMIC_SAMPLING, INLINE, MATERIALIZE, NO_ACCESS, NO_BUFFER, NO_MONITORING, NO_PUSH_PRED, NO_PUSH_SUBQ, NO_QKN_BUFF, NO_SEMIJOIN, NOAPPEND, NOCACHE, OR_EXPAND, ORDERED, ORDERED_PREDICATES, PUSH_PRED, PUSH_SUBQ, QB_NAME, RESULT_CACHE, SELECTIVITY, SEMIJOIN, SEMIJOIN_DRIVER, STAR, SWAP_JOIN_INPUTS, USE_ANTI, USE_SEMI.
Making a backup the database managed by the user
Check out the views to get file information database
- Use the V$DATAFILE to obtain the names and states of all data files
- Use the V$CONTROLFILE to view all control files
- Use the V$LOGFILE view to display all the names of redo log files
- Use the view V$TABLESPACE and V$DATAFILE to obtain a list of all data files and their respective tablespaces
Backup the closed database
Includes all data files and control files that constitute the Oracle database while it is closed
The steps of the backup are:
- Compile the updated list of all files to backup
- Stop the Oracle instance using the command
SHUTDOWN NORMAL/IMMEDIATE /TRANSACTIONAL
- Back up all data files and control files using a backup utility operating system.
- Restart the Oracle instance.
Using SQLPlus under Linux
Intro
You would be surprised to see that you can't rollback your command with the up arrow (last command entered) or the backspace button (actual command correction).Special characters are displayed.
Some existing commands
- Review the last command entered:
L
- Redo last command entered:
R
- Correcting last command entered:
c/text_tobe_corrected/text_corrected
- Type R to have the command executed.
Using rlwrap
- However, for those who want to return to a more classic methodology, you can download the rlwrap package:
apt-get install rlwrap sur Debian, etc.
- Log as follows:
rlwrap sqlplus user/pass@SID
- You can now use your arrow keys and backspace. Commands (R, L and c) mentioned above can be used too.
Automate the use of rlwrap
- To automate the use of rlwrap, add the following line in your .bashrc file.:
alias sqlplus='rlwrap sqlplus'
- Run the following command for the changes are taken into account:
source ~/.bashrc
- Now you no longer need to specify rlwrap: the "sqlplus" command will automatically be interpreted as "rlwrap sqlplus".
