1/8/11

Installing Oracle 11g on Ubuntu

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
 

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".
 

Connecting to Oracle via php




Intro


Below is an article based on an example of connection to an Oracle database through a php script. However it is not designed to configure your oracle server and oracle client. It ensure that you can access your oracle server from the web server using the SQLPlus.
We can normally connect to a server based on Oracle 8i.

Requirements


It is essential to assemble PHP with Oracle8i module. Under windows, it is crucial to the php.ini to include the line extension=php_oci8.dll

Example of code


$c1 = ocilogon("scott", "tiger", $db); $c2 = ocilogon("scott", "tiger", $db); function create_table($conn) $stmt = ociparse($conn, "create table scott.hallo (test varchar2(64))"); ociexecute($stmt); echo $conn . " created table\n\n"; function drop_table($conn) $stmt = ociparse($conn, "drop table scott.hallo"); ociexecute($stmt); echo $conn . " dropped table\n\n"; function insert_data($conn) $stmt = ociparse($conn, "insert into scott.hallo values('$conn' || ' ' || to_char(sysdate,'DD-MON-YY HH24:MI:SS'))"); ociexecute($stmt, OCI_DEFAULT); echo $conn . " inserted hallo\n\n";


This is one of the easiest way to access Oracle amongst others
 
 

Backing up the database managed by the user




1. Query views for information on database files

  • Use the view V$DATAFILE to obtain a list of names and statements of all data files
  • Use the V$CONTROLFILE to display all the control files
  • Use the V$LOGFILE view to display all the names of redo log files
  • Use the V$TABLESPACE and V$DATAFILE to obtain a list of all data files and their respective tablespaces

2. Backing up the database


Includes all datafiles and control files that constitute the Oracle database when it is closed
The steps of the backup are:
  • Compile the updated list of all files to be backed up
  • Stop the Oracle instance using the command 
  • SHUTDOWN NORMAL/IMMEDIATE /Transactional
    .
  • Back up all data files and control files with a backup utility operating system .
  • Restart Oracle.
 

Display limit of Serveroutput




Issue


Serveroutput has a display limit which is set by default to a total of 1000 records but can be modified by the serveroutput on size XXX command,

Solution


Note that however it will not exceed 3000 record.
To correct this you can use the UTL_FILE package to redirect the result to a file on disk.
 
 

Stored Procedures and Functions




Issue


Under Oracle, the stored functions and procedures may become inactive or non-functional when the tables they run, are deleted or modified.

Solution


To avoid such problems, each time a table is modified or deleted, use the table ALL_DEPENDENCIES to check the dependencies with functions or procedures.

Thanks to wjaouadi for this tip
 

Problems with dbms_output




If the dbms_output begins to create problems and to generate inconsistent results, simply run the script 
    • $ORACLE_HOME/rdbms/admin/dbmsotpt.sql
  • It will recreate and recompile dbms_output.
 

General Statistics




To generate statistics about your database, simply run the script:

$ORACLE_HOME/rdbms/admin/utlbstat.sql

The useful information will be displayed.

Enable auditing on an object


To enable auditing on an object from a schema, simply use the command

UDIT audit_option [ON schema.object_name] [BY username] [BY { SESSION | ACCESS }] [WHENEVER { SUCCESSFUL | NOT SUCCESSFUL }]

To view the results, simply run the command:
SELECT * FROM SYS.AUD$;

Check the free space


To check the free space, run the command:
SELECT * FROM DBA_FREE_SPACE ORDER BY TABLESPACE_NAME, BYTES;

Change the name of the database


To rename a database:
  • Create a new database with the new name.
  • Export the old database.
  • Import the old base to the new base.
  • Delete the old database and it's done.


Original FAQ by wjaouadi on CCM!
 
 

ORA-00942: table or view does not exist




Issue


In the case you encounter the following error message: a table or view entered does not exist, or unauthorized synonym was used, or an expression of view was used instead of a table name.

Solution

  • You must verify the following items:
    • the name of the view or the tables used
    • that name of a view is used instead of a table name
    • the name of the view or the table exists.
  • To verify the existence of view, table or synonym:


SELECT * FROM DBA_TABLES WHERE TABLE_NAME = 'table_name'; SELECT * FROM DBA_SYNONYM WHERE SYNONYM_NAME = 'synonym_name'; SELECT * FROM DBA_VIEWS WHERE VIEW_NAME = 'view_name';

Note that


In the case that the table needs to be created or if user or application privileges are needed to access the table, please scontact the database Administrator.
 
 

ORA-00600 internal error code, arguments




This error code is native to internal exceptions from the Oracle programs. It indicates that the process has encountered an unexpected low. It may be due to



To solve this please check the ../bdump, ../cdump et ../udump directories. Check the log files that may indicate what caused the error and to send these files to Oracle Support.
 
 

Start a database with a missing data file




In order to start an Oracle database with a misssing data file:
  • First get connected to SQL*DBA or Server Manager and run "shutdown abort"
  • Run startup mount
  • Run the following command to replace the datafile (eg using... /oradata/temp_01.dbf):



Change database datafile: datafile `.../oradata/temp_01.dbf' offline drop;
  • alter database open;
  • drop tablespace TEMP including contents.

ORA-01630 : max # extents (extent) reached in temp segment






ORA-01630 : max # extents (extent) reached in temp segment in tablespace name_tab_space

It may be due to the fact that a temporary segment is trying to extend MAXEXTENTS.
TO solve this, if MAXEXTENTS for the tablespace is less than the maximum limit of the system, you can raise or increase the PCTINCREASE for the target tablespace


Resetting a sequence


  • To reset a sequence SEQ created through the command: 
    • CREATE SEQUENCE seq;
  • Search for its current value via the command: 
    • SEQ.CURRVAL SELECT FROM DUAL;
  • Then change the sequence by adding the option by adding increment value VAL(current value - 1) to reset the sequence to 1: 
    • ALTER SEQUENCE SEQ INCREMENT by -VAL;
  • Then run the command that will reset the sequence: 
    • SEQ.NEXTVAL SELECT FROM DUAL;
  • To restore the increment of the sequence: 
    • ALTER SEQUENCE SEQ INCREMENT by 1;



Example: 
SQL> create sequence seq;   

Sequence created.   

SQL> select seq.nextval from dual;   

   NEXTVAL  
----------  
         1  

SQL> select seq.nextval from dual;  

   NEXTVAL  
----------  
         2  

SQL> select seq.nextval from dual;  

   NEXTVAL  
----------  
         3  

SQL> select seq.currval from dual;   

    CURRVAL   
----------   
          3   
SQL> alter sequence seq increment by -2;  // 2=SEQ.CURRVAL-1  

Sequence altered.   

SQL> select seq.nextval from dual;   

    NEXTVAL  
----------   
          1   

SQL> alter sequence seq increment by 1;   

Sequence altered.


Making use of special characters




Intro


For some SQL queries, we need to search for strings containing special characters like '%'.
  • Considering the below example, the need is to find records containing the middle character '%': 


Select *  
From table  
where fields like '%%%';

Solution

  • To do this you must know how to seperate the special characters.
  • First of all we must give a value to SQL ESCAPE parameter, for example '^': 

SQL>SET ESCAPE ^
  • From there, the character '^' can be used to identify and seperate special characters. 



Select *  
From table  
where fields like '%^%%';

Managing SQL parameters



  • SQL has a number of parameters at Oracle level that can be viewed it through the following command: 
    • SHOW ALL
  • To change the value of a parameter you can use the command: 
    • SET NAME_PARAM VALUE

Optimize import/export processes






Optimizing the performance of the Import/Export
  • Make use of "commit = y"
  • Adjust the buffer size needed to « BUFFER=.... »
    • Note that its recommended that you disable the triggers, constraints and remove the index prior to import and recreate them after loading
    • Break down the export into multiple files (eg a script for each user). Then import them in parallel.

Creating a listener in the Oracle server



  • In order to create a listener at a database server Oracle simply edit the listener.ora tnsnames.ora files in the directory $ORACLE_HOME/network/admin.
    • To start the listener you must use the snrctl start command.

Detecting objects that needs defragmentation due MAXEXTENT




To detect objects requiring defragmentation because of an overflow due to predefined MAXEXTENT, simply run the following command: 


SELECT SUBSTR(owner||'.'||segment_name,1,50) OBJECT,  EXTENTS, MAX_EXTENTS 
FROM DBA_SEGMENTS 
WHERE MAX_EXTENTS - EXTENTS < accepted_range 
ORDER BY EXTENTS;


Note that: the variable accepted_range represents the overflow we wish to detect (it may take a value of 4, 5 for example)

Display name of the Oracle database




To view the name of the Oracle databases and data related to them, simply run the command: 
SELECT * FROM V$DATABASE;


Display parameters




In addition to the file init.ora, it is possible for you to consult the database to be check out parameters such as max_open_cursors ,size of a block ...

Simply run the following command: 

SELECT name, value 
FROM v$parameter

 

Optimizing sorting operations




Issue


You may certainly have noticed that when performing a sorting operation, there is an increase of the number of input/output to disk.

Solution


This will somehow slow down the performance of our requests and to counter this we must increase the value of the SORT_AREA_SIZE parameter. This parameter specifies the maximum size in bytes of memory that Oracle can use to perform the sorting. Once the sorting operation performed, Oracle frees the memory allocated for sorting except the memory specified by the parameter SORT_AREA_RETAINED_SIZE (of course before returning the results). After the return of the last row of the result of the query, Oracle frees the rest from memory.

Note that: the sorting operations are included in the SELECT DISTINCT, MINUS, INTERSECT, UNION, min (), max () and count ()
 

The lifecycle of a cursor




Intro


A good knowledge of the life cycle and the functioning of cursors will allow you to optimize applications running SQL queries.

Note that: "A cursor is a procedure allowing you to assign a name to a "SELECT statement" and ammend the information within the SQL statement"

Getting started


The steps in the execution of a cursor are:
  • Opening a cursor: memory is allocated for the cursor in the private memory of the server process (on the server side) associated with the session (UGA User Global Area). Note that no SQL query is associated with the cursor for now.
  • Cursor path: an SQL query is associated with it. The representation of the route of the request, including its implementation plan, which describes how the SQL engine executes the SQL statement is loaded into the Shared Pool, in the library cache. The structure in the UGA is updated to store a pointer to the area associated with the cursor in the library cache.
  • Definition of output variables: the SQL query returns data, so the variables related to the must be defined. This definition is also required for DELETE, INSERT and UPDATE requests (using the RETURNING clause).
  • Linking input variables: if the SQL query uses variables, they must be provided. No verification is done at the link. If invalid values are passed, a runtime error will be triggered.
  • Execution of the cursor: at this stage, the SQL is executed. Depending on the database engine, the phase of actual implementation may be deferred at the time of fetch.
  • Fetch cursor (recovery of content): If the SQL query returns a result,it is recovered. In general it is at this stage that treatment occurs.
  • Closing the cursor: the cursor associated with the resources at the UGA are automatically released. The cursor in the library cache is not deleted in response to possible future uses.
 
 Components of the Oracle architecture



The Oracle server is the system that manages databases and provides comprehensive and integrated information management.
An Oracle server consists of an instance and a database.

Oracle Instance


The Oracle instance allows you to access the Oracle database ( opening only one database).
The Oracle instance consists of:
  • Background process : manage and implement the relationships between physical structures and memory structures. There are two categories
    • Mandatory background processes: DBWN, PMON, CKPT, LGWR, SMON
    • Optional background process: ARCn, LMDn, RECO, CJQ0, LMON, Snnn, Dnnn, Pnnn, LCKn, QMNn
  • Memory structures consisting of essentially of two memory areas:
  • Memory area allocated to the SGA (System Global Area) : allocated to start the instance and represents a fundamental component of an Oracle instance. It consists of several memory areas:
    • The shared memory area
    • The buffer cache of the database
    • Buffer logging and other structures for the management of internal and external locks, statistical data, etc ...
    • The LARGE POOL memory area
    • The Java memory area
  • Memory allocated for the PGA (Program Global Area) is allocated at the start of the process server. It is reserved for each user process that connects to the Oracle database and is released at the end of the process.

The user process


The program which interacts with the database by starting a connection. It communicates only with the process server.

Process Servers


Represents the program that interact directly with the Oracle server. It responds to all requests and return results. It can be dedicated to a client or a server shared by many.

Oracle database


The Oracle database is a collection of data treated as a single entity and consists of three types of files including:
  • Control files
  • Data Files
  • Log files
More Next »
Người đăng: vào lúc

Bài đăng phổ biến