Oracle Installation in Red Hat Linux 4 (Using VM Box)

Oracle Installation in Red Hat Linux 4

OS : Red Hat Linux 4 (Throw VM Box)
DB Version: 10.2.0.1

Pre-Installation:

Memory verification
To check RPM Package.

Memory Verification:


[root@localhost proc]# cat meminfo grep Free
MemFree: 45428 kB
HighFree: 0 kB
LowFree: 45428 kB
SwapFree: 1048408 kB
HugePages_Free: 0

[root@localhost proc]# cat meminfo grep Total
MemTotal: 515676 kB
HighTotal: 0 kB
LowTotal: 515676 kB
SwapTotal: 1048568 kB
VmallocTotal: 499704 kB
HugePages_Total: 0

PS: MemTotal & SwapTotal memory available

[root@localhost proc]# free
total used free shared buffers cached
Mem: 515676 471592 44084 0 33716 280136
-/+ buffers/cache: 157740 357936
Swap: 1048568 160 1048408

[root@localhost etc]# cat redhat-release
Red Hat Enterprise Linux ES release 4 (Nahant Update 2)

To find Kernel Version


[root@localhost etc]# uname -r
2.6.9-22.EL

[root@localhost etc]# uname
Linux

[root@localhost etc]# uname -a
Linux localhost.localdomain 2.6.9-22.EL #1 Mon Sep 19 18:20:28 EDT 2005 i686 i686 i386 GNU/Linux

PS: As per Kernel version; need to download rpm packages from http://rpmfind.net


To Check RPM Packages

[root@localhost etc]# rpm -q make gcc
make-3.80-5
gcc-3.4.4-2

[root@localhost etc]# rpm -q glibc
glibc-2.3.4-2.13

[root@localhost etc]# rpm -q compat
package compat is not installed

[root@localhost etc]# rpm -q compat-db
compat-db-4.1.25-9

[root@localhost etc]# rpm -q compat-gcc
package compat-gcc is not installed

[root@localhost etc]# rpm -q compat-gcc-c++
package compat-gcc-c++ is not installed

[root@localhost etc]# rpm -q compat-libstdc
package compat-libstdc is not installed

[root@localhost etc]# rpm -q open
open-1.4-21

[root@localhost etc]# rpm -q setarch
setarch-1.6-1

From my environment there are four packages missing.

How to install RPM Package

I have downloaded missing package from http://rpmfind.net

[root@localhost Desktop]# ls -lr
total 5604
-rw-r--r-- 1 root root 359948 Mar 7 14:36 compat-libstdc++-devel-7.3-2.96.118 .i386.rpm
-rw-r--r-- 1 root root 1054177 Mar 7 14:36 compat-libstdc++-7.3-2.96.118.i386. rpm
-rw-r--r-- 1 root root 1848405 Mar 7 14:35 compat-gcc-c++-7.3-2.96.118.i386.rp m
-rw-r--r-- 1 root root 2437064 Mar 7 14:33 compat-gcc-7.3-2.96.118.i386.rpm

[root@localhost Desktop]# rpm -ivh compat-gcc-7.3-2.96.118.i386.rpm –aid -force
warning: compat-gcc-7.3-2.96.118.i386.rpm: V3 DSA signature: NOKEY, key ID db42a 60e
Preparing... ########################################### [100%]
1:compat-gcc ########################################### [100%]

[root@localhost Desktop]# rpm -q compat-gcc
compat-gcc-7.3-2.96.118
[root@localhost Desktop]# rpm -ivh compat-gcc-c++-7.3-2.96.118.i386.rpm –aid -force
warning: compat-gcc-c++-7.3-2.96.118.i386.rpm: V3 DSA signature: NOKEY, key ID d b42a60e
error: Failed dependencies:
compat-libstdc++ = 7.3 is needed by compat-gcc-c++-7.3-2.96.118.i386
compat-libstdc++-devel = 7.3 is needed by compat-gcc-c++-7.3-2.96.118.i3 86
[root@localhost Desktop]# rpm -ivh compat-libstdc++-7.3-2.96.118.i386.rpm
warning: compat-libstdc++-7.3-2.96.118.i386.rpm: V3 DSA signature: NOKEY, key ID db42a60e
Preparing... ########################################### [100%]
file /usr/lib/libstdc++-2-libc6.1-1-2.9.0.so from install of compat-libs tdc++-7.3-2.96.118 conflicts with file from package compat-libstdc++-296-2.96-13 2.7.2
file /usr/lib/libstdc++-3-libc6.2-2-2.10.0.so from install of compat-lib stdc++-7.3-2.96.118 conflicts with file from package compat-libstdc++-296-2.96-1 32.7.2

[root@localhost Desktop]# rpm -e compat-libstdc++-296-2.96-132.7.2

PS: Getting error; that’s the reason just removing package and then installing.

[root@localhost Desktop]#
[root@localhost Desktop]# rpm -ivh compat-libstdc++-7.3-2.96.118.i386.rpm –aid -force
warning: compat-libstdc++-7.3-2.96.118.i386.rpm: V3 DSA signature: NOKEY, key ID db42a60e
Preparing... ########################################### [100%]
1:compat-libstdc++ ########################################### [100%]
[root@localhost Desktop]#
[root@localhost Desktop]#
[root@localhost Desktop]# rpm -ivh compat-libstdc++-devel-7.3-2.96.118 –aid -force
error: open of compat-libstdc++-devel-7.3-2.96.118 failed: No such file or directory
[root@localhost Desktop]# ls -lr
total 5604
-rw-r--r-- 1 root root 359948 Mar 7 14:36 compat-libstdc++-devel-7.3-2.96.118.i386.rpm
-rw-r--r-- 1 root root 1054177 Mar 7 14:36 compat-libstdc++-7.3-2.96.118.i386.rpm
-rw-r--r-- 1 root root 1848405 Mar 7 14:35 compat-gcc-c++-7.3-2.96.118.i386.rpm
-rw-r--r-- 1 root root 2437064 Mar 7 14:33 compat-gcc-7.3-2.96.118.i386.rpm
[root@localhost Desktop]# rpm -ivh compat-libstdc++-devel-7.3-2.96.118.i386.rpm –aid -force
warning: compat-libstdc++-devel-7.3-2.96.118.i386.rpm: V3 DSA signature: NOKEY, key ID db42a60e
Preparing... ########################################### [100%]
1:compat-libstdc++-devel ########################################### [100%]
[root@localhost Desktop]#
[root@localhost Desktop]# rpm -ivh compat-gcc-c++-7.3-2.96.118.i386.rpm –aid -force
warning: compat-gcc-c++-7.3-2.96.118.i386.rpm: V3 DSA signature: NOKEY, key ID db42a60e
Preparing... ########################################### [100%]
1:compat-gcc-c++ ########################################### [100%]




From Operating System level user creation and modification

To Check Oracle User and DBA Group already exist or not.

[root@localhost etc]# grep dba /etc/group

[root@localhost etc]# grep dba /etc/group

[root@localhost etc]# grep oinstall /etc/group

[root@localhost sbin]# usermod -g oinstall -G dba oracle
usermod: user oracle does not exist



User and Group does’t available; so; I’m creating user and group

[root@localhost sbin]# groupadd dba
[root@localhost sbin]#
[root@localhost sbin]# groupadd oinstall

[root@localhost sbin]# id
uid=0(root) gid=0(root) groups=0(root),1(bin),2(daemon),3(sys),4(adm),6(disk),10(wheel) context=root:system_r:unconfined_t

[root@localhost sbin]#
[root@localhost sbin]# useradd -g oinstall -G dba oracle
[root@localhost sbin]# passwd oracle
Changing password for user oracle.
New UNIX password:
BAD PASSWORD: it is based on a dictionary word
Retype new UNIX password:
passwd: all authentication tokens updated successfully.
[root@localhost sbin]# id nobody
uid=99(nobody) gid=99(nobody) groups=99(nobody)

Checking Kernel Parameters


For Oracle Installaion; Need to configure kernel parameter from SysCtl.conf

Just add the below parameter in /etc/sysctl.conf using vi editor

kernel.core_uses_pid = 1
#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
rmem_default = 262144
rmem_max = 262144
wmem_default = 262144
wmem_max = 262144

#/usr/bin/sysctl –p ( for effecting the kernel parameters-no need for reboot)
Once everything completed just re-boot your operating system because you have changed kernel parameter from sysctl.conf

After re-booting operating system try to login using “Oracle” User And try to install Oracle database using

[oracle@localhost dbback]$ ls
archive data software u01


[oracle@localhost dbback]$

[oracle@localhost /home/oracle]$ vi .bash_profile

export ORACLE_HOME=/u01/oracle/product/10.2.0.1
export ORACLE_BASE=/u01/oracle
export LD_LIBRARY_PATH=/u01/oracle/product/10.2.0.1/lib
export LD_ASSUNE_KERNEL=2.4.19
export PATH=$PATH:/u01/oracle/product/10.2.0.1/bin

:wq! (save and exit)
%s/oldvalue/newvlaue/g --- replace in vi



[oracle@localhost dbback]$ cd software/

[oracle@localhost software]$ ls
10201_database_linux32.zip database

[oracle@localhost software]$ cd database/

[oracle@localhost database]$ ls -lr
total 52
-rwxrwxr-x 1 oracle oinstall 5213 Jul 2 2005 welcome.html
drwxrwxr-x 9 oracle oinstall 4096 Jul 2 2005 stage
-rwxrwxr-x 1 oracle oinstall 1327 Jul 2 2005 runInstaller
drwxrwxr-x 2 oracle oinstall 4096 Jul 2 2005 response
drwxrwxr-x 5 oracle oinstall 4096 Jul 2 2005 install
drwxrwxr-x 9 oracle oinstall 4096 Jul 2 2005 doc

[oracle@localhost database]$ ./runInstaller
Starting Oracle Universal Installer...

Checking installer requirements...


Once successfully install oracle you many need configure two scripts from root user.

/home/oracle/oraInventory/orainstRoot.sh
/dbback/u01/app/oracle/product/10.2.0/db_1/root.sh

Like


[oracle@localhost ~]$ su - root
Password:

[root@localhost ~]# id

uid=0(root) gid=0(root) groups=0(root),1(bin),2(daemon),3(sys),4(adm),6(disk),10(wheel) context=root:system_r:unconfined_t

[root@localhost ~]# /home/oracle/oraInventory/orainstRoot.sh
Changing permissions of /home/oracle/oraInventory to 770.
Changing groupname of /home/oracle/oraInventory to oinstall.
The execution of the script is complete
[root@localhost ~]# /dbback/u01/app/oracle/product/10.2.0/db_1/root.sh
Running Oracle10 root.sh script...

The following environment variables are set as:
ORACLE_OWNER= oracle
ORACLE_HOME= /dbback/u01/app/oracle/product/10.2.0/db_1

Enter the full pathname of the local bin directory: [/usr/local/bin]:
Copying dbhome to /usr/local/bin ...
Copying oraenv to /usr/local/bin ...
Copying coraenv to /usr/local/bin ...


Creating /etc/oratab file...
Entries will be added to the /etc/oratab file as needed by
Database Configuration Assistant when a database is created
Finished running generic part of root.sh script.
Now product-specific root actions will be performed.
Feel free to post your comments...

Simple Streams Implementaion

SQL> conn system@knbdb
Connected.
SQL>
SQL> select name from v$database;

NAME
---------
KNBDB

SQL>
SQL> create table scott.hr (eno int constraint hr_pk primary key ,ename varchar2(22))
2 /

Table created.

SQL> conn strmadmin@knbdb
Connected.
SQL> EXEC DBMS_STREAMS_ADM.SET_UP_QUEUE();

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.


SQL>
SQL> conn system@rdb
Connected.
SQL>
SQL> create table scott.hr (eno int constraint hr_pk primary key ,ename varchar2(22))
2 /

Table created.

SQL> conn strmadmin@knbdb
Connected.
SQL>

SQL> BEGIN
2 DBMS_STREAMS_ADM.ADD_TABLE_PROPAGATION_RULES(
3 table_name => 'scott.hr',
4 streams_name => 'knbdb',
5 source_queue_name => 'strmadmin.streams_queue',
6 destination_queue_name => 'strmadmin.streams_queue@rdb',
7 include_dml => true,
8 include_ddl => true,
9 source_database => 'knbdb',
10 inclusion_rule => true,
11 queue_to_queue => true);
12* END;
SQL> /

PL/SQL procedure successfully completed.

SQL> alter table scott.hr add supplemental log data (primary key,unique) columns;

Table altered.

SQL>
SQL> BEGIN
2 DBMS_STREAMS_ADM.ADD_TABLE_RULES(
3 table_name => 'scott.hr',
4 streams_type => 'capture',
5 streams_name => 'capture_streams',
6 queue_name => 'strmadmin.streams_queue',
7 include_dml => true,
8 include_ddl => true,
9 inclusion_rule => true);
10 END;
11 /

PL/SQL procedure successfully completed.

SQL>

SQL> ed
Wrote file afiedt.buf

1 DECLARE
2 iscn NUMBER; -- Variable to hold instantiation SCN value
3 BEGIN
4 iscn := DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER();
5 DBMS_APPLY_ADM.SET_TABLE_INSTANTIATION_SCN@rdb(
6 source_object_name => 'scott.hr',
7 source_database_name => 'knbdb',
8 instantiation_scn => iscn);
9* END;
SQL> /
DECLARE
*
ERROR at line 1:
ORA-04052: error occurred when looking up remote object
STRADMIN.DBMS_APPLY_ADM@RDB
ORA-00604: error occurred at recursive SQL level 1
ORA-01017: invalid username/password; logon denied
ORA-02063: preceding line from RDB


SQL> select * from dual@rdb;
select * from dual@rdb
*
ERROR at line 1:
ORA-01017: invalid username/password; logon denied
ORA-02063: preceding line from RDB


SQL> select name from v$database;

NAME
---------
KNBDB

SQL> create database link rdb connect to strmadmin identified by admin using 'RDB';

Database link created.

SQL> select * from dual@rdb;

D
-
X

SQL> DECLARE
2 iscn NUMBER; -- Variable to hold instantiation SCN value
3 BEGIN
4 iscn := DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER();
5 DBMS_APPLY_ADM.SET_TABLE_INSTANTIATION_SCN@rdb(
6 source_object_name => 'scott.hr',
7 source_database_name => 'knbdb',
8 instantiation_scn => iscn);
9 END;
10 /

PL/SQL procedure successfully completed.

SQL> set serveroutput on
SQL> ;
1 DECLARE
2 iscn NUMBER; -- Variable to hold instantiation SCN value
3 BEGIN
4 iscn := DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER();
5 DBMS_APPLY_ADM.SET_TABLE_INSTANTIATION_SCN@rdb(
6 source_object_name => 'scott.hr',
7 source_database_name => 'knbdb',
8 instantiation_scn => iscn);
9* END;
SQL> /

PL/SQL procedure successfully completed.

SQL>

SQL> conn strmadmin@rdb
Connected.
SQL>
SQL>
SQL> EXEC DBMS_STREAMS_ADM.SET_UP_QUEUE();

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.

SQL>
SQL> create database link knbdb connect to strmadmin identified by admin using 'knbdb';

Database link created.

SQL> BEGIN
2 DBMS_STREAMS_ADM.ADD_TABLE_RULES(
3 table_name => 'scott.hr',
4 streams_type => 'apply',
5 streams_name => 'apply_simp',
6 queue_name => 'strmadmin.streams_queue',
7 include_dml => true,
8 include_ddl => true,
9 source_database => 'knbdb',
10 inclusion_rule => true);
11 END;
12 /

PL/SQL procedure successfully completed.

SQL> BEGIN
2 DBMS_APPLY_ADM.SET_PARAMETER(
3 apply_name => 'apply_simp',
4 parameter => 'disable_on_error',
5 value => 'n');
6 END;
7 /

PL/SQL procedure successfully completed.

SQL> BEGIN
2 DBMS_APPLY_ADM.START_APPLY(
3 apply_name => 'apply_simp');
4 END;
5 /

PL/SQL procedure successfully completed.

SQL> conn strmadmin@knbdb
Connected.
SQL>
SQL>
SQL> BEGIN
2 DBMS_CAPTURE_ADM.START_CAPTURE(
3 capture_name => 'capture_streams');
4 END;
5 /

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.

SQL>
SQL> spool off

SQL>
SQL> conn scott@knbdb
Connected.
SQL>
SQL> select * from hr;

no rows selected

SQL>
SQL> insert into hr values (101,'babu');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from hr;

ENO ENAME
---------- ----------------------
101 babu

SQL>
SQL> conn hr@rdb
ERROR:
ORA-01005: null password given; logon denied


Warning: You are no longer connected to ORACLE.
SQL> conn scott@rdb
ERROR:
ORA-28000: the account is locked


SQL> system@rdb
SP2-0042: unknown command "system@rdb" - rest of line ignored.
SQL> conn system@rdb
Connected.
SQL> alter user scott account unlock;

User altered.

SQL> alter user scott identified by tiger;

User altered.

SQL> conn scott@rdb
Connected.
SQL>
SQL> select * from hr;

ENO ENAME
---------- ----------------------
101 babu

SQL>
SQL> insert into hr values (102,'taj');

1 row created.

SQL> commit;

Commit complete.

SQL> udpate hr set ename='bAbu-taj' where eno=101;
SP2-0734: unknown command beginning "udpate hr ..." - rest of line ignored.
SQL>
SQL> update hr set ename='babu-taj' where eno=101;

1 row updated.

SQL> commit;

Commit complete.

SQL> alter table hr add column sal int;
alter table hr add column sal int
*
ERROR at line 1:
ORA-00904: : invalid identifier


SQL> alter table hr add sal int;

Table altered.

SQL> select * from hr;

ENO ENAME SAL
---------- ---------------------- ----------
101 babu-taj
102 taj

SQL> show user
USER is "SCOTT"
SQL>
SQL> conn scott@knbdb
Connected.
SQL>
SQL> select * from hr;

ENO ENAME
---------- ----------------------
101 babu

SQL>
SQL> alter table hr add (sal number (4));

Table altered.

SQL> conn scott@rdb
Connected.
SQL>
SQL> select * from hr;

ENO ENAME SAL
---------- ---------------------- ----------
101 babu-taj
102 taj

SQL> conn scott@knbdb
Connected.
SQL> alter table hr add (sal1 number (4));

Table altered.

SQL> conn scott@rdb
Connected.
SQL>
SQL> select * from hr;

ENO ENAME SAL SAL1
---------- ---------------------- ---------- ----------
101 babu-taj
102 taj

SQL> spool off

As per my understanding; This is explain how replication working between oracle database; Even the above example will help you read-only replication/streams concept.

Feel free your comment about my understanding...

Streams Setup between oracle database.

Os : Windows XP
Version: 10.2.0.1
Database Mode: ArchiveLog

As per my understanding from oracle documentation; I configured this streams setup between two oracle databases.

Pre-Request Configuration:

Source Database Name : KnbDb
Downstream (Remote) database name: RDB

Before implementing streams you should configure the below database parameter in source and remote database.

SQL> show parameter db_unique

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_unique_name string knbdb

SQL> show parameter global

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
global_names boolean TRUE

SQL> show parameter log_archive_dest

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_1 string location=(D:\ArchiveLog\KnbDb)


NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_state_1 string enable

SQL> show parameter archive

NAME TYPE VALUE
------------------------------------ ----------- ----------------------
archive_lag_target integer 1800
log_archive_config string dg_config=(knbdb,rdb)


SQL> show parameter job

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
job_queue_processes integer 10

PS: my database configured automatic shared management; It’s automatically take care streams pool size

Database Configuration

Once completed your pre-request configuration; three are few steps you need to configure.

1. Create Steams Tablespace

CREATE TABLESPACE Streams DATAFILE '/usr/oracle/dbs/streams_tbs.dbf'
SIZE 25M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;

2. Create Steams User

CREATE USER strmadmin IDENTIFIED BY admin
DEFAULT TABLESPACE streams
QUOTA UNLIMITED ON streams;

GRANT DBA TO strmadmin;

3. Grant Admin Privilege

BEGIN
DBMS_STREAMS_AUTH.GRANT_ADMIN_PRIVILEGE(
grantee => 'strmadmin',
grant_privileges => true);
END;
/


CREATE DIRECTORY admin_dir AS '/usr/admin';

BEGIN
DBMS_STREAMS_AUTH.GRANT_ADMIN_PRIVILEGE(
grantee => 'strmadmin',
grant_privileges => false,
file_name => 'grant_strms_privs.sql',
directory_name => 'admin_dir');
END;
/


Run the GRANT_ADMIN_PRIVILEGE procedure to generate a script named grant_strms_privs.sql and place this script in the /usr/admin directory on your computer system:

.
4. Execute the script in SQL*Plus:

SET ECHO ON
SPOOL grant_strms_privs.out
@/usr/admin/grant_strms_privs.sql
SPOOL OFF


PS: As per your environment repeat the above configuration from downstream and source database.

Network Configuration

Source Database:

1. Connect Strmadmin User

2. Create database link which pointing to downstream database

Remote database:

1. Connect Strmadmin User

2. Create database link which pointing to source database

Script:

Create database link <> Connect to <> identified by <> using <>