Wednesday, December 26, 2007

Oracle Database 10g Release 2 (10.2.0.1) Installation On Red Hat Enterprise Linux 5.1 (RHEL5)

Oracle Database 10g Release 2 (10.2.0.1) Installation On Red Hat Enterprise Linux 5.1 (RHEL5)

In this document I'll describe the installation of Oracle Database 10g Release 2 (10.2.0.1) on Red Hat Enterprise Linux 5 (RHEL5). With a minimum of 2G swap, secure Linux disabled and the following package groups installed:
GNOME Desktop Environment
Editors
Graphical Internet
Text-based Internet
Development Libraries
Development Tools
Legacy Software Development
Server Configuration Tools
Administration Tools
Base
Legacy Software Support
System Tools
X Window System
Alternative installations may require more packages to be loaded, in addition to the ones listed below.
Get Software
Unpack Files
Hosts File
Set Kernel Parameters
Setup
Installation
Post Installation
1. Get Software
Download the software from www.oracle.com:
Oracle Database 10g Release 2 (10.2.0.1) Software
2. Unpack Files
Unzip the files:
unzip 10201_database_linux32.zip
You should now have a single directory containing installation files. Depending on the age of the download this may either be named "database".
3. Hosts File
The /etc/hosts file must contain a fully qualified name for the server:
XXX.XXX.XXX.XXX TESTBOX.domainname TESTBOX

4. Set Kernel Parameters
Add the following lines to the /etc/sysctl.conf file:
#kernel.shmall = 2097152#kernel.shmmax = 2147483648kernel.shmmni = 4096# semaphores: semmsl, semmns, semopm,
semmnikernel.sem = 250 32000 100 128
#fs.file-max =65536
net.ipv4.ip_local_port_range = 1024 65000
net.core.rmem_default=262144
net.core.rmem_max=262144
net.core.wmem_default=262144
net.core.wmem_max=262144
Run the following command to change the current kernel parameters:
/sbin/sysctl -p
Add the following lines to the /etc/security/limits.conf file:
* soft nproc 2047* hard nproc 16384* soft nofile 1024* hard nofile 65536
Add the following line to the /etc/pam.d/login file, if it does not already exist:
session required /lib/security/pam_limits.so
Disable secure linux by editing the /etc/selinux/config file, making sure the SELINUX flag is set as follows:
SELINUX=disabled
Alternatively, this alteration can be done using the GUI tool (System > Administration > Security Level and Firewall). Click on the SELinux tab and disable the feature.
5. Setup
Install the following packages:
cd /media/cdrom/Serverrpm -Uvh setarch-2*rpm -Uvh make-3*rpm -Uvh glibc-2*binutilscompat-dbcompat-libstdc++control-centergccgcc-c++glibcglibc-commongnome-libslibstdc++libstdc++-develmakepdksh (RHEL 5 ships with ksh instead)sysstatxscreensaversetarchlibXp (to start runInstaller)
For checking user rpm -q and use rpm –Uvh switch and install all these packages.

Create the new groups and users:
--
# groupadd dba
# groupadd oinstall
# useradd -G dba -d /home/oracle -g oinstall oracle
# passwd oracle
--
Create the directories in which the Oracle software will be installed:
mkdir -p /u01/app/oracle/product/10.2.0/db_1chown -R oracle.oinstall /u01
Login as root and issue the following command:
xhost +
Edit the /etc/redhat-release file replacing the current release information (Red Hat Enterprise Linux Server release 5.1 (Tikanga)) with the following:
redhat-4
Login as the oracle user and add the following lines at the end of the .bash_profile file:
# Oracle SettingsTMP=/tmp; export TMPTMPDIR=$TMP; export TMPDIR ORACLE_BASE=/u01/app/oracle; export ORACLE_BASEORACLE_HOME=$ORACLE_BASE/product/10.2.0/db_1; export ORACLE_HOMEORACLE_SID=TSH1; export ORACLE_SIDORACLE_TERM=xterm; export ORACLE_TERMPATH=/usr/sbin:$PATH; export PATHPATH=$ORACLE_HOME/bin:$PATH; export PATH LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib; export LD_LIBRARY_PATHCLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib; export CLASSPATH

6. Installation
Log into the oracle user. If you are using X emulation then set the DISPLAY environmental variable:
DISPLAY=:0.0; export DISPLAY
Start the Oracle Universal Installer (OUI) by issuing the following command in the database directory:
./runInstaller
During the installation enter the appropriate ORACLE_HOME and name then continue installation. For a more detailed look at the installation process, click on the links below to see screen shots of each stage.
Select Installation Method
Specify Inventory Directory and Credentials
Select Installation Type
Specify Home Details
Product-Specific Prerequisite Checks
Select Configuration Option
Select Database Configuration
Specify Database Configuration Options
Select Database Management Option
Specify Database Storage Option
Specify Backup and Recovery Options
Specify Database Schema Passwords
Summary
Install
Configuration Assistants
Database Configuration Assistant
Database Configuration Assistant Password Management
Execute Configuration Scripts
End Of Installation
7. Post Installation
Edit the /etc/redhat-release file restoring the original release information:
Red Hat Enterprise Linux Server release 5.1 (Tikanga)
Edit the /etc/oratab file setting the restart flag for each instance to 'Y':
TSH1:/u01/app/oracle/product/10.2.0/db_1:Y


References:
http://wiki.ittoolbox.com/index.php/Installation_of_Oracle_database_10g_on_RHEL_5
http://www.oracle-base.com/articles/10g/OracleDB10gR2InstallationOnRHEL5.php

Tuesday, December 11, 2007

ORA-00980: synonym translation is no longer valid

ORA-00980: synonym translation is no longer valid
Cause:
The synonym used is based on a table, view, or synonym that no longer exists.
Action:
Replace the synonym with the name of the object it references or re-create the synonym so that it refers to a valid table, view, or synonym.

This problem can come when you make an operation on a synonym that has no base object.
for retification this problem you can recreate synonym with proper base object.

Tuesday, December 4, 2007

Memory Notification: Library Cache Object loaded into SGA

Hi,

I was getting "Memory Notification: Library Cache Object loaded into SGA". If you search the web or metalink account then can find healty knowledge.

If you are using spfile then
alter system set "_kgl_large_heap_warning_threshold"=8388608 scope=spfile ;

If you are using pifle then just add this parameter.
_kgl_large_heap_warning_threshold=8388608

shutdown immediate;

startup from this new pifle.


Cheers

Monday, December 3, 2007

dbca -silent

Hi,
Today I fell with dbca ..... (database configuration assistant)

dbca -silent -createDatabase -templateName "New Database" -gdbName orcl -sid orcl -sysPassword "orcl" -systemPassword "orcl" -emConfiguration NONE -dbsnmpPassword "orcl" -sysmanPassword "orcl" -datafileDestination /u01/app/oracle/orcl/
Database is an invalid command line argument.
Database is an invalid command line argument.


Oracle stores .dbc files against templates. so go for those.......

[root@imran oracle]# find / -name *.dbc -print
/u01/app/oracle/product/10.2.0/db_1/assistants/dbca/templates/Data_Warehouse.dbc
/u01/app/oracle/product/10.2.0/db_1/assistants/dbca/templates/General_Purpose.dbc
/u01/app/oracle/product/10.2.0/db_1/assistants/dbca/templates/Transaction_Processing.dbc

[oracle@imran oracle]$ dbca -silent -createDatabase -templateName /u01/app/oracle/product/10.2.0/db_1/assistants/dbca/templates/Data_Warehouse.dbc -gdbName orcl -sid orcl -sysPassword "orcl" -systemPassword "orcl" -emConfiguration NONE -dbsnmpPassword "orcl" -sysmanPassword "orcl" -datafileDestination /u01/app/oracle/orcl/

Copying database files
1% complete
3% complete
11% complete
18% complete
26% complete
33% complete
37% complete
Creating and starting Oracle instance
40% complete
45% complete
50% complete
55% complete
56% complete
60% complete
62% complete
Completing Database Creation
66% complete
70% complete
73% complete
85% complete
96% complete
100% complete
Look at the log file "/u01/app/oracle/product/10.2.0/db_1/cfgtoollogs/dbca/orcl/orcl.log" for further details.

This was a happy day for me :)

Friday, November 30, 2007

ORA-01102: and ORA-16014:

SQL> startup pfile='/u01/app/oracle/product/10.2.0/db_1/dbs/initorcl.ora';
ORACLE instance started.
Total System Global Area 83886080 bytes
Fixed Size 1217836 bytes
Variable Size 75500244 bytes
Database Buffers 4194304 bytes
Redo Buffers 2973696 bytes
ORA-01102: cannot mount database in EXCLUSIVE mode
Cause:
Can be that you are trying to startup second instance with privously opened database.
If you have only one database and gets this error then that means you are trying to open an opened database!.


2.
Total System Global Area 83886080 bytes
Fixed Size 1217836 bytes
Variable Size 75500244 bytes
Database Buffers 4194304 bytes
Redo Buffers 2973696 bytes
Database mounted.ORA-16014: log 1 sequence# 188 not archived, no available destinationsORA-00312: online log 1 thread 1: '/u01/app/oracle/pkstg102/redo01.log'

Cause: you archives are of size equal to your database parameter DB_RECOVERY_FILE_DEST_SIZE. get rid of this error just increase this parameter's value.

SQL> alter system set DB_RECOVERY_FILE_DEST_SIZE = 6g;
System altered.
SQL> alter database open;
Database altered.

Thursday, November 29, 2007

Automatic Oracle 10g export using cron job.

Hi,
Today, I want to share shell script and steps for automatic export using Oracle 10g datapump.
As Oracle says that datapump is fater than imp/exp 45%. and I admit as I experienced it.
Agenda: automatic export (Oracle says it "logical backup") On Unix/Linux Machine.
Steps:
1. Make directory in oracle (it is mendatory for impdp/expdp).
2. Give read, write privileges on directoy to user.
3. Shell script.
4. Configurations.

1.
connect to oracle as "as sysdba"


I will complete it on monday hopefully....

StatsPack Installation and Use.

Hi,

Today I have installed statpack on a customer production machine for performance checking.
Here are steps.
1. Create tablespace for perfstat user. (optional)

CREATE TABLESPACE perfstat DATAFILE ' path_for_datafile_&_datafile_name.dbf ' SIZE 400M ;

2. Install statspack
$ cd $ORACLE_HOME/rdbms/admin$ sqlplus "/ as sysdba"
SQL> @spcreate.sql
*It will prompt you for perfstat user password, its default tablespace, its temporary tablespace.
** At the end you will receive
No errors.
NOTE:SPCPKG complete. Please check spcpkg.lis for any errors.
*** If there are some errors, then go for spcpkg.lis file.

3. Now time to take snap!
SQL> execute statspack.snap;

PL/SQL procedure successfully completed.

* After 10minutes take another snap!

SQL> execute statspack.snap;

PL/SQL procedure successfully completed.

4. Now get the Report!
SQL> @spreport.sql
* You will get

Listing all Completed Snapshots
SnapInstance DB Name Snap Id Snap Started Level Comment
------------ ------------ --------- ----------------- ----- --------------------
ORCL ORCL 1 29 Nov 2007 02:40 5
2 29 Nov 2007 02:49 5

Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 1
Begin Snapshot Id specified: 1
Enter value for end_snap: 2
End Snapshot Id specified: 2

*At the end you will name the report.
*Now Take report n enjoy.