ORA-29701: Unable to connect to Cluster Manager

ORA-29701: Unable to connect to Cluster Manager

Every time I would try to start an ASM instance I would get this error:


Today In my ASM environment; When I try to start asm instance i got the below error message.

ORA-29701: Unable to connect to Cluster Manager

This is error releated to cluster manager goes down. We need to start manager using "localconfig add/delete"

1/ Log in as roor user
2/ Set your ORACLE_HOME
3/ execute the below command

$ORACLE_HOME/bin/localconfig delete
$ORACLE_HOME/bin/localconfig add

In Windows

C:\Documents and Settings\Baskar>SET ORACLE_SID=+ASM

C:\Documents and Settings\Baskar>set ORACLE_HOME=f:\oracle\product\10.2.0\db_1

C:\Documents and Settings\Baskar>%ORACLE_HOME%\bin\localconfig delete
Step 1: stopping local CSS stack
Step 2: deleting OCR repository
successfully deleted local CSS setup


C:\Documents and Settings\Baskar>
C:\Documents and Settings\Baskar>%ORACLE_HOME%\bin\localconfig add
Step 1: creating new OCR repository
Successfully accumulated necessary OCR keys.
Creating OCR keys for user 'baskar', privgrp ''..
Operation successful.
Step 2: creating new CSS service
successfully created local CSS service
successfully added CSS to home



Feel free post your comments.

"Marking disk "VOL1" as an ASM disk: [FAILED]"

Kernel : 2.6.9-42.EL-2.0.3

OS : Red Hat Enterprise Linux 4 AS

Database: 10.2.0.1


While doing ASM Configuraion, I got below error message

"Marking disk "VOL1" as an ASM disk: [FAILED]"

Not able to create ASM Disk's

[oracle@localhost ~]$ su - root

Password:

[root@localhost Desktop]# uname -r

2.6.9-42.0.0.0.1.ELhugemem

[root@localhost ~]#

[root@localhost ~]# cd Desktop/

[root@localhost Desktop]# ls -lrt

total 8

drwx------ 2 root root 4096 Mar 22 04:15 Trash

[root@localhost Desktop]# cd /home/oracle/Desktop/

[root@localhost Desktop]# ls -lrt

total 268

drwx------ 2 oracle oinstall 4096 Mar 22 14:20 Trash

-rw-r--r-- 1 oracle oinstall 4298 Mar 22 14:20 Home.desktop

-rw-r--r-- 1 oracle oinstall 82256 Mar 22 14:40 oracleasm-support-2.1.2-1.el4.i386.rpm

-rw-r--r-- 1 oracle oinstall 12948 Mar 22 14:40 oracleasmlib-2.0.2-1.i386.rpm

-rw-r--r-- 1 oracle oinstall 127412 Mar 22 14:41 oracleasm-2.6.9-42.EL-2.0.3-1.i686.rpm

[root@localhost Desktop]#

[root@localhost Desktop]# rpm -Uvh oracleasm-2.6.9-42.EL-2.0.3-1.i686.rpm \

> oracleasmlib-2.0.2-1.i386.rpm \

> oracleasm-support-2.1.2-1.el4.i386.rpm

Preparing... ########################################### [100%]

1:oracleasm-support ########################################### [ 33%]

2:oracleasm-2.6.9-42.EL ########################################### [ 67%]

3:oracleasmlib ########################################### [100%]

[root@localhost Desktop]#

[root@localhost Desktop]# /etc/init.d/oracleasm scandisks

Scanning the system for Oracle ASMLib disks: [ OK ]

[root@localhost Desktop]#

[root@localhost Desktop]# /etc/init.d/oracleasm configure

Configuring the Oracle ASM library driver.

This will configure the on-boot properties of the Oracle ASM library

driver. The following questions will determine whether the driver is

loaded on boot and what permissions it will have. The current values

will be shown in brackets ('[]'). Hitting without typing an

answer will keep that current value. Ctrl-C will abort.

Default user to own the driver interface []: oracle

Default group to own the driver interface []: dba

Start Oracle ASM library driver on boot (y/n) [n]: y

Scan for Oracle ASM disks on boot (y/n) [y]: y

Writing Oracle ASM library driver configuration: done

Initializing the Oracle ASMLib driver: [ OK ]

Scanning the system for Oracle ASMLib disks: [ OK ]

[root@localhost Desktop]#

[root@localhost Desktop]# /etc/init.d/oracleasm start

Initializing the Oracle ASMLib driver: [ OK ]

Scanning the system for Oracle ASMLib disks:

^[[A

[ OK ]

[root@localhost Desktop]#

[root@localhost Desktop]# /etc/init.d/oracleasm start

Initializing the Oracle ASMLib driver: [ OK ]

Scanning the system for Oracle ASMLib disks: [ OK ]

[root@localhost Desktop]#

[root@localhost Desktop]# /etc/init.d/oracleasm enable

Writing Oracle ASM library driver configuration: done

Initializing the Oracle ASMLib driver: [ OK ]

Scanning the system for Oracle ASMLib disks: [ OK ]

[root@localhost Desktop]#

[root@localhost Desktop]# /etc/init.d/oracleasm createdisk /dev/sda6

Action "createdisk" requires two arguments

[root@localhost Desktop]#

[root@localhost Desktop]# /etc/init.d/oracleasm createdisk VOL1 /dev/sda6

Marking disk "VOL1" as an ASM disk: [FAILED]

Loading module "oracleasm": Unable to load module "oracleasm"

OS: Red Hat Enterprise Linux 4

Kernel : 2.6.9-22.EL

Database Version: 10.2.0.1

When I try to configure ASM disk I got below error message.

"Loading module "oracleasm": Unable to load module "oracleasm"

As per metalink "Doc ID: 338770.1". Need to upgrade kernel version

Uname –r
2.6.9-22.EL



[root@localhost Desktop]# rpm -Uvh

oracleasm-2.6.9-11.EL-2.0.3-1.i686.rpm \ >

oracleasmlib-2.0.2-1.i386.rpm \

> oracleasm-support-2.0.3-1.i386.rpm

Preparing... ########################################### [100%]

1:oracleasm-support ########################################### [ 33%]
2:oracleasm-2.6.9-11.EL ########################################### [ 67%]
3:oracleasmlib ########################################### [100%]



[root@localhost ~]# /etc/init.d/oracleasm configure

Configuring the Oracle ASM library driver.



This will configure the on-boot properties of the Oracle ASM library
driver. The following questions will determine whether the driver is
loaded on boot and what permissions it will have. The current values
will be shown in brackets ('[]'). Hitting without typing an
answer will keep that current value. Ctrl-C will abort.


Default user to own the driver interface []: oracle
Default group to own the driver interface []: dba
Start Oracle ASM library driver on boot (y/n) [y]: y
Fix permissions of Oracle ASM disks on boot (y/n) [y]: y
Writing Oracle ASM library driver configuration: [ OK ]
Loading module "oracleasm": Unable to load module "oracleasm"

[FAILED]

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