How do I delete an entire database (UNIX only)?


The following shows the steps to drop a database in Unix enviroment. In order to delete a database, there are few things need to be taken care of. First, all the database related files eg *.dbf, *.ctl, *.rdo, *.arc need to be deleted. Then, the entry in listener.ora and tnsnames.ora need to be removed. Third, all the database links need to be removed since it will be invalid anyways.

It depends how you login to oracle account in Unix, you should have environment set for the user oracle. To confirm that the environment variable is set, do a env|grep ORACLE and you will notice that your ORACLE_SID=SOME_SID and ORACLE_HOME=SOME_PATH. If you do not already have the ORACLE_SID and ORACLE_HOME set, do it now.

Make sure also, that you set the ORACLE_SID and ORACLE_HOME correct else you will end up deleting other database. Next, you will have to query all the database related files from dictionaries in order to identify which files to delete. Do the following:

01. Login as connect / as sysdba at svrmgrl
02. startup the database if it’s not already started. The database must at least mounted.
03. spool /tmp/deletelist.lst
04. select name from v$datafile; (This will get all the datafiles; alternatively, you can select file_name from dba_data_files)
05. select member from v$logfile;
06. select name from v$controlfile;
07. archive log list (archive_log_dest is where the archived destination is)
08. locating ifile by issuing show parameter ifile (alternatively, check the content of init.ora)
09. spool off
10. Delete in O/S level the files listed in /tmp/deletelist.lst
11. remove all the entries which refer to the deleted database in tnsnames.ora and listener.ora (located in $ORACLE_HOME/network/admin)
12. remove all database links referring to the deleted database.
13. check “/var/opt/oracle/oratab” to make sure there is no entry of the database deleted. If yes, remove it.
14. DONE

Advertisements

Question with Answer on Oracle database Patches

Patches are a small collection of files copied over to an existing installation. They are associated with particular versions of Oracle products.

The discussion will especially help for those beginners who are preparing for interview and inexperienced to apply the patches. In this article you will find all those things briefly with an example. For more details please study the oracle documentation and try to search with separate topics on this blog.

What are different Types of Patches?

Regular Patcheset: To upgrade to higher version we use database patchset. Please do not confuse between regular patchests and patch set updates (PSU). Consider the regular patchset is super set of PSU. Regular Patchset contain major bug fixes. In comparison to regular patch PSU will not change the version of oracle binaries such as sqlplus, import/export etc. The importance of PSU is automatically minimized once a regular patchset is released for a given version. It is mainly divided into two types:

Security or Critical Patch Update (CPU): Critical patch update quarterly delivered by oracle to fix security issues.

Patch set updated (PSU): It include CPU and bunch of other one-off patches. It is also quarterly delivered by oracle.

Interim (one-off) Patch: It is also known as patchset exception or one-off patch or interim patch. This is usually a single fix for single problem or enhancement. It released only when there is need of immediate fix or enhancement that cannot wait until for next release of patchset or bundle patch. It is applied using OPATCH utility and is not cumulative.

Bundle Patches: Bundle Patches includes both the quarterly security patches as well as recommended fixes (for Windows and Exadata only). When you try to download this patch you will find bundle of patches (different set of file) instead of single downloaded file (usually incase patchset).

Is Opatch (utility) is also another type of patch?

OPatch is utility from oracle corp. (Java based utility) that helps you in applying interim patches to Oracle’s software and rolling back interim patches from Oracle’s software. Opatch also able to Report already installed interim patch and can detect conflict when already interim patch has been applied. This program requires Java to be available on your system and requires installation of OUI. Thus from the above discussion coming to your question it is not ideal to say OPATCH is another patch.

When we applying single Patch, can you use OPATCH utility?

Yes, you can use Opatch incase of single patch. The only type of patch that cannot be used with OPatch is a patchset

When you applying Patchsets, You can use OUI.

Yes, Patcheset uses OUI. A patch set contains a large number of merged patches, to change the version of the product or introduce new functionality. Patch sets are cumulative bug fixes that fix all bugs and consume all patches since the last base release. Patch sets and the Patch Set Assistant are usually applied through OUI-based product specific installers.

Can you Apply OPATCH without downtime?

As you know for apply patch your database and listener must be down. When you apply OPTACH it will update your current ORACLE_HOME. Thus coming to your question to the point in fact it is not possible in case of single instance but in RAC you can Apply Opatch without downtime as there will be more separate ORACLE_HOME and more separate instances (running once instance on each ORACLE_HOME).

You have collection of patch (nearly 100 patches) or patchset. How can you apply only one patch from patcheset or patch bundle at ORACLE_HOME?
With Napply itself (by providing patch location and specific patch id) you can apply only one patch from a collection of extracted patch. For more information check the opatch util NApply –help. It will give you clear picture.

For Example:

opatch util napply <patch_location> -id 9 -skip_subset -skip_duplicate

This will apply only the patch id 9 from the patch location and will skip duplicate and subset of patch installed in your ORACLE_HOME.

How can you get minimum/detail information from inventory about patches applied and components installed?

You can try below command for minimum and detail information from inventory
C:ORACLE_HOMEOpatchopatch lsinventory –invPtrLoc “location of oraInst.loc file”

$ORACLE_HOMEOPatchopatch lsinventory -detail -invPtrLoc “location of oraInst.loc file”
Differentiate Patcheset, CPU and PSU patch? What kind of errors usually resolved from them?

Critical Patch Update (CPU) was the original quarterly patches that were released by oracle to target the specific security fixes in various products. CPU is a subset of patchset updates (PSU). CPU are built on the base patchset version where as PSU are built on the base of previous PSU

Patch Set Updates (PSUs) are also released quarterly along with CPU patches are a superset of CPU patches in the term that PSU patch will include CPU patches and some other bug fixes released by oracle. PSU contain fixes for bugs that contain wrong results, Data Corruption etc but it doe not contain fixes for bugs that that may result in: Dictionary changes, Major Algorithm changes, Architectural changes, Optimizer plan changes

Regular patchset: Please do not confuse between regular patchests and patch set updates (PSU). Consider the regular patchset is super set of PSU. Regular Patchset contain major bug fixes. The importance of PSU is minimizing once a regular patchset is released for a given version. In comparison to regular patch PSU will not change the version of oracle binaries such as sqlplus, import/export etc.

If both CPU and PSU are available for given version which one, you will prefer to apply?
From the above discussion it is clear once you apply the PSU then the recommended way is to apply the next PSU only. In fact, no need to apply CPU on the top of PSU as PSU contain CPU (If you apply CPU over PSU will considered you are trying to rollback the PSU and will require more effort in fact). So if you have not decided or applied any of the patches then, I will suggest you to go to use PSU patches. For more details refer: Oracle Products [ID 1430923.1], ID 1446582.1

PSU is superset of CPU then why someone choose to apply a CPU rather than a PSU?

CPUs are smaller and more focused than PSU and mostly deal with security issues. It seems to be theoretically more consecutive approach and can cause less trouble than PSU as it has less code changing in it. Thus any one who is concerned only with security fixes and not functionality fixes, CPU may be good approach.

How can you find the PSU installed version?

PSU references at 5th place in the oracle version number which makes it easier to track such as (e.g. 10.2.0.3.1). To determine the PSU version installed, use OPATCH utility:

OPATCH lsinv -bugs_fixed | grep -i  PSU

To find from the database:

Select substr(action_time,1,30) action_time, substr(id,1,10) id, substr(action,1,10) action,substr(version,1,8) version, substr(BUNDLE_SERIES,1,6) bundle, substr(comments,1,20) comments from registry$history;

Note: You can find the details from the above query if you already executed the catbundle.sql

Click to Check Existing Oracle Database Patch Status

Will Patch Application affect System Performance?
Sometimes applying certain patch could affect Application performance of SQL statements. Thus it is recommended to collect a set of performance statistics that can serve as a baseline before we make any major changes like applying a patch to the system.

Can you stop applying a patch after applying it to a few nodes? What are the possible issues?

Yes, it is possible to stop applying a patch after applying it to a few nodes. There is a prompt that allows you to stop applying the patch. But, Oracle recommends that you do not do this because you cannot apply another patch until the process is restarted and all the nodes are patched or the partially applied patch is rolled back.

How you know impact of patch before applying a patch?

OPATCH <option> -report

You can use the above command to know the impact of the patch before actually applying it.

How can you run patching in scripted mode?

opatch <option> -silent

You can use the above command to run the patches in scripted mode.

Can you use OPATCH 10.2 to apply 10.1 patches?

No, Opatch 10.2 is not backward compatible. You can use Opatch 10.2 only to apply 10.2 patches.

What you will do if you lost or corrupted your Central Inventory?

In that case when you lost or corrupted your Central Inventory and your ORACLE_HOME is safe, you just need to execute the command with –attachHomeflag, OUI automatically setup the Central Inventory for attached home.

What you will do if you lost your Oracle home inventory (comps.xml)?

Oracle recommended backup your ORACLE_HOME before applying any patchset. In that case either you can restore your ORACLE_HOME from the backup or perform the identical installation of the ORACLE_HOME.

When I apply a patchset or an interim patch in RAC, the patch is not propagated to some of my nodes. What do I do in that case?

In a RAC environment, the inventory contains a list of nodes associated with an Oracle home. It is important that during the application of a patchset or an interim patch, the inventory is correctly populated with the list of nodes. If the inventory is not correctly populated with values, the patch is propagated only to some of the nodes in the cluster.

OUI allows you to update the inventory.xml with the nodes available in the cluster using the -updateNodeList flag in Oracle Universal Installer.

When I apply a patch, getting the following errors:

“Opatch Session cannot load inventory for the given Oracle Home <Home_Location> Possible causes are: No read or write permission to ORACLE_HOME/.patch_storage; Central Inventory is locked by another OUI instance; No read permission to Central Inventory; The lock file exists in ORACLE_HOME/.patch_storage; The Oracle Home does not exist in Central Inventory”. What do I do?

This error may occur because of any one or more of the following reasons:

  • – The ORACLE_HOME/.patch_storage may not have read/write permissions. Ensure that you give read/write permissions to this folder and apply the patch again.
  • – There may be another OUI instance running. Stop it and try applying the patch again.
  • – The Central Inventory may not have read permission. Ensure that you have given read permission to the Central Inventory and apply the patch again.
  • – The ORACLE_HOME/.patch_storage directory might be locked. If this directory is locked, you will find a file named patch_locked inside this directory. This may be due to a previously failed installation of a patch. To remove the lock, restore the Oracle home and remove the patch_locked file from the ORACLE_HOME/.patch_storage
  • – The Oracle home may not be present in the Central Inventory. This may be due to a corrupted or lost inventory or the inventory may not be registered in the Central Inventory.

We should check for the latest security patches on the Oracle metalink website http://metalink.oracle.com/ and we can find the regular security alert at the location http://technet.oracle.com/deploy/security/alert.htm

Caution: It is not advisable to apply the patches directly into the production server. The ideal solution is to apply or test the patches in test server before being moved into the production system.

For more about oracle Patch:

About Patching in Oracle

How to Apply Critical Patch Update on RAC

Apply patches on 9.2.0.1.0 to upgrade 9.2.0.7.0

How to Create Oracle database Manually

There are basically three ways to create database:
Using the database configure Assistance (DBCA)
DBCA can be used to create the new database at the time of oracle installation as well as later at any time as a standalone tool, which provide a graphical interface (GUI) that guide you through the creation of database.
With the SQL create database statement.
You can use the CREATE DATABASE script to create the database manuallly from command prompt. For that you must have created previously your environment as a part of oracle installation (Install oracle software only).
Through upgrading an existing database.
If you are already using a previous release of oracle, You can upgrade your existing database and use it with new release of oracle software
This article basically focusing on the second option (only). It can be completed on the command line that is without any GUI.
Database creation prepares several operating system files to work together as an Oracle database. You need only create a database once, Thus you must carefully plan your database structure before creating a database such as:
1. Plan the database tables and indexes and estimate the amount of space they will require.
2. Plan the layout of underlying operating system. Proper distribution of I/O will improve your database performance. For example: Place redolog files and datafiles on seperate disks. Placing datafiles on seperate disk will reduce contention problem.
3. Consider using OMF feature to create and manage the operating system file that comprise your database storage.
4. Select the global database name, which is the name (DBNAME) and location (DOMAIN_NAME) of database within the network structure.
5. Develop good understanding of Pfile or spfile parameters.
6. You must select the database character set. All characters including data in data dictionary, is stored in database character set
7. Consider what time zones your database must support.
8. Select the standard database block size. This is specified at database creation by the DB_BLOCK_SIZE initialization parameter and cannot be changed after the database is created. The SYSTEM tablespace and most other tablespaces use the standard block size. Additionally, you can specify up to four
non-standard block sizes when creating tablespaces.
9. Use an UNDO tablespace to manage your undo records, rather than rollback segments.
10. Develop a backup and recovery strategy to protect the database failure.
                                                                **Step to Create Database Manually**
Step1: Create all the necessary directories.
Step2: Prepare the database Script.
Step3: Prepare the init.ora file.
Step4: Startup created database with init.ora file.
Step5: Finally run the catalog.sql and catproc.sql scripts.

Step1: First create all the required directory on the destination server such as: Admin, adump, bdump, cdump, udump, Archive etc.
Step2: Next Prepare the database creation script such as:
Create Database Script on Windows Environment
—————————————————————————————————-
Create database MY_DB
MAXLOGFILES 5
MAXLOGMEMBERS 5
MAXDATAFILES 100
MAXINSTANCES 1
MAXLOGHISTORY 292
logfile group 1 (‘D:oracleMY_DBredo1.log’) size 10M,
group 2 (‘D:oracleMY_DBredo2.log’) size 10M,
group 3 (‘D:oracleMY_DBredo3.log’) size 10M
character set WE8ISO8859P1
national character set utf8
datafile ‘D:oracleMY_DBsystem_01.dbf’
size 50M autoextend on
next 20M maxsize unlimited
extent management local
sysaux datafile ‘D:oracleMY_DBsysaux_01.dbf’
size 10M autoextend on
next 10M maxsize unlimited
undo tablespace undotbs1
datafile ‘D:oracleMY_DBundotbs1_01.dbf’ size 10M
default temporary tablespace temp
tempfile ‘D:oracleMY_DBtemp_01.dbf’ size 10M;
Note: On windows environment you need to create services using oradim such as:
CMD> ORADIM -NEW -SID MY_DB -PFILE=’D:oracleadminSADHANpfileinitSADHAN.ora’;
Create Database Script on Linux Environment
—————————————————————————————————-
Create database MY_DB
MAXLOGFILES 5
MAXLOGMEMBERS 5
MAXDATAFILES 100
MAXINSTANCES 1
MAXLOGHISTORY 292
logfile group 1 (‘/u01/../redo1.log’) size 10M, group 2 (‘/u01/../redo2.log’) size 10M,
group 3 (‘/u01/../redo3.log’) size 10M
character set WE8ISO8859P1
national character set utf8
datafile ‘/u01/../system_01.dbf’ size 50M autoextend on next 20M maxsize unlimited
extent management local
sysaux datafile ‘/u01/../sysaux_01.dbf’ size 10M autoextend on next 10M maxsize unlimited
undo tablespace undotbs1
datafile ‘/u01/../undotbs1_01.dbf’ size 10M
default temporary tablespace temp
tempfile ‘/u01/../temp_01.dbf’ size 10M;
Step3: Prepare the init.ora file such as:
audit_file_dest=’/u01/../MY_DB/admin/adump’
background_dump_dest=’/u01/../MY_DB/admin/bdump’
compatible=’10.2.0.3.0′
control_files=’/u01/../MY_DB/control01.ctl’, ‘/u01/../MY_DB/control02.ctl’,’/u01/../MY_DB/control03.ctl’
core_dump_dest=’/u01/../MY_DB/admin/cdump’
db_block_size=8192
db_domain=”
db_file_multiblock_read_count=16
db_name=’MY_DB’
dispatchers='(PROTOCOL=TCP) (SERVICE=my_dbXDB)’
job_queue_processes=10
log_archive_dest_1=’LOCATION=/u01/../MY_DB/archive’
log_archive_format=’%t_%s_%r.dbf’
open_cursors=300
pga_aggregate_target=220200960
processes=150
remote_login_passwordfile=’EXCLUSIVE’
sga_target=629145600
undo_management=’AUTO’
undo_tablespace=’UNDOTBS’
user_dump_dest=’/u01/../MY_DB/admin/udump’
db_recovery_file_dest=’/u02/../MY_DB/backup’
db_recovery_file_dest_size=230686720
Step4: Now start the newly created database in nomount phase with the help of init.ora file.
$ export ORACLE_SID=my_db
$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.3.0 – Production on Thu Jun 21 10:26:54 2012
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
Connected to an idle instance.
SQL> Startup Pfile=/u01/app/oracle/product/10.2.0/db_1/dbs/initmy_db.ora nomount; 
ORACLE instance started.
Total System Global Area 629145600 bytes
Fixed Size 1443789 bytes
Variable Size 168878648 bytes
Database Buffers 447849588 bytes
Redo Buffers 7340032 bytes
SQL> @My_db.sql 
Database created.
Step5: Finally run the catalog.sql and catproc.sql scripts.
Thus the database is created now. you just need to run the catalog.sql and catproc.sql scripts. You will find these script on the location: $ORACLE_HOME/rdbms/admin
SQL>@/u01/app/oracle/product/10.2.0/db_1/rdbms/admin/catalog.sql
SQL>@/u01/app/oracle/product/10.2.0/db_1/rdbms/admin/catproc.sql
SQL> select name from v$database;
NAME
———
MY_DB
Finally now your database is ready to use.

How to Install Oracle 11g XE on Linux Fedora 17/18

Installing Oracle Database 11g XE on Fedora 17/18

[Oracle 11g Logo]
The following are the steps to install a Oracle 11g in fedora.
Step1:  Downloading the Software
Download the oracle-xe-11.2.0-1.0.x86_64.rpm.zip file from follwing url
Step2: Extracting the content
Extract the zip into any location. Here i am extracting the above zip into following location.
[ranga@ranga Disk1]$ pwd

/home/ranga/install/Disk1
[ranga@ranga Disk1]$ ls
oracle-xe-11.2.0-1.0.x86_64.rpm  response  upgrade
[ranga@ranga Disk1]$
Step3: Install libaio

[root@ranga Disk1]#  yum install libaio
Step4: Installing the oracle by using rpm command.
 
[root@ranga Disk1]# rpm -i oracle-xe-11.2.0-1.0.x86_64.rpm
Preparing… ########################################### [100%] 1:oracle-xe ########################################### [100%] Executing post-install steps… You must run ‘/etc/init.d/oracle-xe configure’ as the root user to configure the database.
Your installation done but u need configure
Step5: Configuring the database
[root@ranga Disk1]# /etc/init.d/oracle-xe configure
Oracle Database 11g Express Edition Configuration ————————————————- This will configure on-boot properties of Oracle Database 11g 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]: 9090 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 11g Express Edition to be started on boot (y/n) [y]:y Starting Oracle Net Listener…Done Configuring database…Done Starting Oracle Database 11g Express Edition instance…Done Installation completed successfully.
In the above configuration, i am given port number is 9090 by default 8080 and database listener is same and you need to enter SYS or SYSTEM password. If you want while booting it self starting the database your giving ‘y’ in boot option. Finaly your database configured successfully. This installation created under /u01 directory.
Starting the Database manually : 
To start the database manually, run this command as root user:
# /etc/init.d/oracle-xe start
or
# /usr/lib/oracle/xe/app/oracle/product/11.2.0/server/bin/lsnrctl start
Stopping the Database manually:
To stop the database manually, run the following command as root user:

# /etc/init.d/oracle-xe stop

Un Install the Oracle 11g: 

Step1: First you need to check which oracle version is installed.
[ranga@ranga ~]$ rpm -qa | grep oracle
oracle-xe-11.2.0-1.0.x86_64
Step2:Uninstall the oracle by using rpm -e with which version it is installed.
[ranga@ranga ~]$ rpm -e

oracle-xe-11.2.0-1.0.x86_64

Installing Oracle 11g R2 Express Edition on Ubuntu 64-bit

              But at first, I must inform you about the limitations of Oracle 11g R2 Express Edition (XE) which are as follows :
  • The Installer released by Oracle is only meant for 64-bit (x86_64) systems. If you wish to install the 32-bit version , see installing oracle xe on ubuntu 32-bit
  • It will consume, at most, processing resources equivalent to one CPU.
  • Only one installation of Oracle Database XE can be performed on a single computer.
  • The maximum amount of user data in an Oracle Database XE database cannot exceed 11 GB.
  • The maximum amount of RAM that Oracle XE uses cannot exceed 1 GB, even if more is available.
Now the steps for Installation :
1) Download the Oracle 11gR2 express edition installer from the link given below:
( You will need to create a free oracle web account if you don’t already have it )
2) Unzip it :
unzip oracle-xe-11.2.0-1.0.x86_64.rpm.zip
3) Install the following packages :
sudo apt-get install alien libaio1 unixodbc vim
4) Convert the red-hat ( rpm ) package to Ubuntu-package :
sudo alien –scripts -d oracle-xe-11.2.0-1.0.x86_64.rpm
(Note: this may take a while , till that time you can go for step 5 )
5) Do the following pre-requisite things:
a) Create a special chkconfigscript :
The Red Hat based installer of Oracle XE 11gR2 relies on /sbin/chkconfig, which is not used in Ubuntu. The chkconfig package available for the current version of Ubuntu produces errors and my not be safe to use. Below is a simple trick to get around the problem and install Oracle XE successfully:
sudo vim /sbin/chkconfig

(copy and paste the following into the file )
#!/bin/bash
# Oracle 11gR2 XE installer chkconfig hack for Ubuntu
file=/etc/init.d/oracle-xe
if [[ ! `tail -n1 $file | grep INIT` ]]; then
echo >> $file
echo ‘### BEGIN INIT INFO’ >> $file
echo ‘# Provides: OracleXE’ >> $file
echo ‘# Required-Start: $remote_fs $syslog’ >> $file
echo ‘# Required-Stop: $remote_fs $syslog’ >> $file
echo ‘# Default-Start: 2 3 4 5’ >> $file
echo ‘# Default-Stop: 0 1 6’ >> $file
echo ‘# Short-Description: Oracle 11g Express Edition’ >> $file
echo ‘### END INIT INFO’ >> $file
fi
update-rc.d oracle-xe defaults 80 01

Save the above file and provide appropriate execute privilege :
       chmod 755 /sbin/chkconfig
b) Set the Kernel parameters :
Oracle 11gR2 XE requires to set the following additional kernel parameters:
sudo vim /etc/sysctl.d/60-oracle.conf
(Enter the following)
# Oracle 11g XE kernel parameters
fs.file-max=6815744
net.ipv4.ip_local_port_range=9000 65000
kernel.sem=250 32000 100 128
kernel.shmmax=536870912
(Save the file)
Note: kernel.shmmax = max possible value , e.g. size of physical RAM ( in bytes e.g. 512MB RAM == 512*1024*1024 == 536870912 bytes )
Verify the change :
sudo cat /etc/sysctl.d/60-oracle.conf
Load newkernel parameters:
sudo service procps start
Verify: sudo sysctl -q fs.file-max
       -> fs.file-max = 6815744
c) Increase the system swap space :Analyze your current swap space by following command :
           free -m
Minimum swap space requirement of Oracle 11gR2 XE is 2 GB . In case, your is lesser , you can increase it by following steps in my one of previous post .
d) make some more required changes :
i) ln -s /usr/bin/awk /bin/awk
ii) mkdir /var/lock/subsys
iii) touch /var/lock/subsys/listener
6) Now you are ready to install Oracle 11gR2 XE. Go to the directory where you created the ubuntu package file in Step 4 and enter following commands in terminal :
i) sudo dpkg –install oracle-xe_11.2.0-2_amd64.deb
Update : Before you proceed to next step , do have a look at this trouble-shooting post in order to avoid MEMORY TARGET or any other “shared memory” error.
ii) sudo /etc/init.d/oracle-xe configure
Enter the following configuration information:
  • A valid HTTP port for the Oracle Application Express (the default is 8080)
  • A valid port for the Oracle database listener (the default is 1521)
  • A password for the SYS and SYSTEM administrative user accounts
  • Confirm password for SYS and SYSTEM administrative user accounts
  • Whether you want the database to start automatically when the computer starts (next reboot).
7) Before you start using Oracle 11gR2 XE you have to set-up more things :
a) Set-up the environmental variables :
Add following lines to your .bashrc :
export ORACLE_HOME=/u01/app/oracle/product/11.2.0/xe
export ORACLE_SID=XE
export NLS_LANG=`$ORACLE_HOME/bin/nls_lang.sh`
export ORACLE_BASE=/u01/app/oracle
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH
export PATH=$ORACLE_HOME/bin:$PATH
b) execute your .profile to load the changes:
          . ./.profile
8) Start the Oracle 11gR2 XE :
sudo service oracle-xe start
The output should be similar to following :
user@machine:~$ sudo service oracle-xe start
Starting Oracle Net Listener.
Starting Oracle Database 11g Express Edition instance.
user@machine:~$
8) Create your user :
a) start sqlplus and login as sys :
sqlplus sys as sysdba
( provide the password you gave while configuring the oracle in Step 6 (ii) ).
This should come to following :
SQL*Plus: Release 11.2.0.2.0 Production on Wed May 9 12:12:16 2012
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Enter password:
Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 – 64bit Production
SQL>
b) Enter following on the sql prompt : Replace username and password by your desired ones.
SQL> create user username identified by password;
User created.
SQL> grant connect,resource to username;
Grant succeeded. 
9) Now as you have created the user , you can login to it :
user@machine:~$ sqlplus
SQL*Plus: Release 11.2.0.2.0 Production on Wed May 9 12:28:48 2012
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Enter user-name: temp
Enter password:
Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 – 64bit Production
SQL> select 2+2 from dual;
2+2
———-
4
SQL>

Removing Oracle Database XE from Linux

First off, check to see what Oracle RPMs are installed, and try removing them.

rpm -qa | grep oracle

rpm -e oracle-xe-univ

If you get an error here, move on to the next steps to manually remove XE. Remove the XE directories.

rm -Rf /usr/lib/oracle/xe
rm -Rf /etc/oratab
rm -Rf /etc/init.d/oracle-xe
rm -Rf /etc/sysconfig/oracle-xe
Your system no longer has any XE files and you can start again.

Installation of Oracle 10g Release 2 (10.2.0.1.0) on Fedora Core Linux 6

Dear friends,

Operating System Details:
Linux testserver 2.6.18-1.2798.fc6 #1 SMP Mon Oct 16 14:54:20 EDT 2006 i686 i686 i386 GNU/Linux

Database Verison Details:
Oracle 10g Release 2 (10.2.0.1.0)

Download Software: (10201_database_linux32.zip) http://www.oracle.com/technology/software/products/database/oracle10g/htdocs/10201linuxsoft.html

Pre-Installation tasks:

Creating DBA group and ORACLE user account in Linux

Login to the System as ROOT and execute the below commands.

$ su – root
Password:

# groupadd dba
# useradd –d “/home/oracle” –m –g dba oracle
# passwd oracle

Configuring Kernel Parameters

Check and edit the /etc/sysctl.conf file with the following lines.

kernel.shmall = 2097152
kernel.shmmax = 2147483648
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
fs.file-max = 65536
net.ipv4.ip_local_port_range = 1024 65000
net.core.rmem_default = 1048576
net.core.rmem_max = 1048576
net.core.wmem_default = 262144
net.core.wmem_max = 262144

Note: Make sure to reboot the system or run the command ” /sbin/sysctl –p” to change the kernel parameters.

Add the following lines to the /etc/security/limits.conf file:

oracle soft nproc 2047
oracle hard nproc 16384
oracle soft nofile 1024
oracle hard nofile 65536

Add or edit the following line in the /etc/pam.d/login file, if it does not already exist:

session required /lib/security/pam_limits.so

Make sure that ” SELINUX=disabled” in the the /etc/selinux/config file

Edit the /etc/redhat-release file replacing the current release information (Fedora Core release 6 (Zod)) with redhat-4

Creating an Oracle Base Directory

# mkdir –p /u01/app/oracle
# mkdir –p /u01/app/oradata
# mkdir –p /u02/app/oradata

# chown –R oracle:dba /u01/app/oracle
# chown –R oracle:dba /u0 1/app/oradata /u02/app/oradata
# chmod –R 775 /u01/app/oracle
# chmod –R 775 /u0 1/app/oradata /u02/app/oradata

Setting Oracle Environment

Login as Oracle Unix user and change the .bash_profile with the following variables.

Su – oracle
Password:

ORACLE_HOME=/u01/app/oracle
ORACLE_SID=mydb
LD_LIBRARY_PATH=$ORACLE_HOME/lib
TNS_ADMIN=$ORACLE_HOME/network
PATH=$PATH:$ORACLE_HOME/bin

Export ORACLE_HOME ORACLE_SID LD_LIBRARY_PATH TNS_ADMIN

Save the bash profile after changing: wq!

Insallation

Login as oracle and unzip the Oracle 10g software

Su – oracle
Password:

$ unzip 10201_database_linux32.zip

Check all pre-req packages are installed, if not install them

To check the packages information: # rpm –q
To install the packages: # rpm –q
Require packages for installation:

rpm -q binutils gcc glibc glibc-headers glibc-kernheaders glibc-devel compat-libstdc++ cpp compat-gcc make compat-db compat-gcc-c++ compat-libstdc++ compat-libstdc++-devel openmotif openmotif21 setarch pdksh libaio libaio-devel

Important: For successful installation, the rpms “libXp-1.0.0-8.i386.rpm and libaio-0.3.106-3.2.i386.rpm” must have installed. Download these from : http://download.fedora.redhat.com/pub/fedora/linux/core/6/i386/os/Fedora/RPMS

Go to database directory and launch the installer.

$./runInstaller

Provide the required information asked by wizards during installation.

Post – Installation tasks:

Edit the /etc/redhat-release file restoring the original release information:
Fedora Core release 6 (Zod)

I didn’t let DBCA to create the default database during the installation; I selected Install Software Only option. After the successful installation of Oracle 10g Software, I created the database manually with my requiremtns. The following are the particulars.

Login as oracel

Make sure that all the required environment variables are Set i.e. ORACLE_HOME and ORACLE_SID etc..

Prepare the initialization parameter with required values

Connect to SQL*Plus as sysdba

$ sqlplus /nolog
SQL> connect /as sysdba

Create the database with the following script.

SQL> startup nomount pfile= /initmydb.ora;

SQL> @$HOME/create_db_manually.sql

create_db_manually.sql file contains

CREATE DATABASE unic
USER SYS IDENTIFIED BY sys
USER SYSTEM IDENTIFIED BY manager
LOGFILE GROUP 1 (‘/u01/app/oradata/unic/redo01.log’) SIZE 50M,
GROUP 2 (‘/u01/app/oradata/unic/redo02.log’) SIZE 50M,
GROUP 3 (‘/u01/app/oradata/unic/redo03.log’) SIZE 50M
MAXLOGFILES 5
MAXLOGMEMBERS 5
MAXLOGHISTORY 1
MAXDATAFILES 100
MAXINSTANCES 1
CHARACTER SET WE8ISO8859P1
NATIONAL CHARACTER SET AL16UTF16
DATAFILE ‘/u02/app/oradata/unic/system01.dbf’ SIZE 325M REUSE
EXTENT MANAGEMENT LOCAL
SYSAUX DATAFILE ‘/u02/app/oradata/unic/sysaux01.dbf’ SIZE 325M REUSE
DEFAULT TABLESPACE users
datafile ‘/u02/app/oradata/unic/users01.dbf’
SIZE 20M REUSE
DEFAULT TEMPORARY TABLESPACE temp
TEMPFILE ‘/u02/app/oradata/unic/temp01.dbf’
SIZE 200M REUSE
UNDO TABLESPACE undotbs1
DATAFILE ‘/u02/app/oradata/unic/undotbs01.dbf’
SIZE 200M REUSE AUTOEXTEND ON MAXSIZE 2000m;

Run Scripts to Build Data Dictionary Views

CONNECT SYS/password AS SYSDBA
SQL>@$ORACLE_HOME rdbmsadmincatalog.sql
SQL>@$ORACLE_HOME rdbmsadmincatproc.sql

Errors generated during the installation

>>Checking operating system version: must be redhat-3, SuSE-9, redhat-4, UnitedLinux-1.0, asianux-1 or asianux-2Failed

Edit the /etc/redhat-release file replacing the current release information (Fedora Core release 6 (Zod)) with redhat-4

>> Exception java.lang.UnsatisfiedLinkError: /tmp/OraInstall2007-02-25_03-22-29PM/jre/1.4.2/lib/i386/libawt.so: libXp.so.6: cannot open shared object file: No such file or directory occurred..java.lang.UnsatisfiedLinkError: /tmp/OraInstall2007-02-25_03-22-29PM/jre/1.4.2/lib/i386/libawt.so: libXp.so.6: cannot open shared object file: No such file or directoryat java.lang.ClassLoader$NativeLibrary.load(Native Method)at java.lang.ClassLoader.loadLibrary0(Unknown Source)
……
……

Download and install the libXp-1.0.0-8.i386.rpm
http://download.fedora.redhat.com/pub/fedora/linux/core/6/i386/os/Fedora/RPMS/libXp-1.0.0-8.i386.rpm

#rpm –ivh libXp-1.0.0-8.i386.rpm

>> error while loading shared libraries: libaio.so.1: cannot open shared object file: No such file or directory

Download and install the libaio-0.3.106-3.2.i386.rpm
http://download.fedora.redhat.com/pub/fedora/linux/core/6/i386/os/Fedora/RPMS/libXp-1.0.0-8.i386.rpm

# rpm -ivh libaio-0.3.106-3.2.i386.rpm

References:

http://download-uk.oracle.com/docs/cd/B19306_01/install.102/b15660/toc.htm

http://www.puschitz.com/InstallingOracle10g.shtml

http://ivan.kartik.sk/oracle/install_ora10gR2_fedora.html