Tips: Improve performance with Oracle Streams
Source Database
1. set capture process parallelism
SQL> exec dbms_capture_adm.set_parameter
(capture_name => ‘name’,parameter => ‘parallelism’,value => 16);
NOTE: Default value is 1.
Target Database
2. set apply process parallelism
SQL> exec dbms_apply_adm.set_parameter
(apply_name => ‘name’,parameter => ‘parallelism’,value => 16);
3. Increase SHARED_POOL_SIZE (prior 10g) & STREAM_POOL_SIZE (10g later)
4. Increase the SDU (Session data unit) in a WAN (wide area network)
5. Implement “heartbeat” table for monitor changes on target database
6. Increase “log_archive_max_processes” parameter
5. Use Frequent Commit Interval in Source database.
Monday, January 28, 2008
Sunday, January 27, 2008
knllgobjinfo: MISSING Streams multi-version data dictionary!!!
Part-I ---- how to setup stream replication between two databases
http://dbataj.blogspot.com/2008/01/oracle-streams-setup-between-two.html
Part-II --- how to add new site in existing stream replication
http://dbataj.blogspot.com/2008/01/oracle-streams-adding-new-site-in.html
New TARGET Database alert
knllgobjinfo: MISSING Streams multi-version data dictionary!!!
Source Database: DB1
Existing TrgtDB: DB2
New Trgt DB: DB3
WORKAROUND:
Existing Target Database: DB2
1. Stop apply process at all existing target sites
SQL> conn strmadmin/strmadmin@db2
Connected.
SQL> exec dbms_apply_adm.stop_apply('apply_stream');
PL/SQL procedure successfully completed.
Source Database: DB1
2. Prepare instantiation SCN
SQL> conn strmadmin/strmadmin@db1
Connected.
SQL> begin
2 dbms_capture_adm.prepare_table_instantiation(table_name => 'scott.taj');
3 end;
4 /
PL/SQL procedure successfully completed.
Existing Target Database: DB2
3. Start apply process at all existing target database
SQL> conn strmadmin/strmadmin@db2
Connected.
SQL> exec dbms_apply_adm.start_apply('apply_stream');
PL/SQL procedure successfully completed.
Saturday, January 26, 2008
Oracle Streams: Adding new site in existing environment
Part-I=How to setup Oracle Streams between two databases
Overview
Database Version: 10.1.0.5.0
Windows XP sp2
Database Name and TNS name
DB1, db1 (source)
DB2, db2 (target) (Existing Target DB)
DB3,db3 (new target database)
1. Enable ARCHIVELOG MODE on both database
Reference: http://dbataj.blogspot.com/2007/09/how-to-enable-archivelog-mode.html
2. Create Stream administrator User
New target database: DB3
SQL> conn sys@db3 as sysdba
Enter password:
Connected.
SQL> create user strmadmin identified by strmadmin;
User created.
SQL> grant connect, resource, dba to strmadmin;
Grant succeeded.
SQL> begin dbms_streams_auth.grant_admin_privilege
2 ( grantee => 'strmadmin',
3 grant_privileges => true);
4 end;
5 /
PL/SQL procedure successfully completed.
3. Setup INIT parameters
SQL> alter system set global_names=true;
System altered.
SQL> alter system set streams_pool_size =100m;
System altered.
4. Create Database Link
SQL> conn strmadmin/strmadmin@db1
Connected.
SQL> create database link DB3
2 connect to strmadmin
3 identified by strmadmin
4 using 'DB3';
Database link created.
SQL> conn strmadmin/strmadmin@db3
Connected.
SQL> create database link db1
2 connect to strmadmin
3 identified by strmadmin
4 using 'DB1';
Database link created.
5. Setup Destination queues
SQL> conn strmadmin/strmadmin@db3
Connected.
SQL> EXEC DBMS_STREAMS_ADM.SET_UP_QUEUE();
PL/SQL procedure successfully completed.
6. Setup Schema for streams
SQL> conn sys@db3 as sysdba
Enter password:
Connected.
SQL> alter user scott account unlock identified by tiger;
User altered.
SQL> conn scott/tiger@db3
Connected.
SQL> create table taj (no number primary key, name varchar2(20), ddate date);
Table created.
7. Configure the propagation process
Source Database: DB1
SQL> conn strmadmin/strmadmin@db1
Connected.
SQL> begin dbms_streams_adm.add_table_propagation_rules
2 ( table_name => 'scott.taj',
3 streams_name => 'DB1_TO_DB3',
4 source_queue_name => 'strmadmin.streams_queue',
5 destination_queue_name => 'strmadmin.streams_queue@DB3',
6 include_dml => true,
7 include_ddl => true,
8 source_database => 'DB1',
9 inclusion_rule => true);
10 end;
11 /
PL/SQL procedure successfully completed.
8. Stop Apply process at existing target database
SQL> conn strmadmin/strmadmin@db2
Connected.
SQL> exec dbms_apply_adm.stop_apply('apply_stream');
PL/SQL procedure successfully completed.
9. Set the instantiation system change number (SCN)
Source Database: DB1
SQL> conn strmadmin/strmadmin@db1
Connected.
PREPARE instantiation system change number (SCN)
SQL> begin
2 dbms_capture_adm.prepare_table_instantiation( table_name => 'scott.taj');
3 end;
4 /
PL/SQL procedure successfully completed.
SQL> declare
2 source_scn number;
3 begin
4 source_scn := dbms_flashback.get_system_change_number();
5 dbms_apply_adm.set_table_instantiation_scn@DB3
6 ( source_object_name => 'scott.taj',
7 source_database_name => 'DB1',
8 instantiation_scn => source_scn);
9 end;
10 /
PL/SQL procedure successfully completed.
10. Configure the apply process at the destination database
SQL> conn strmadmin/strmadmin@db2
Connected.
SQL> exec dbms_apply_adm.start_apply('apply_stream');
PL/SQL procedure successfully completed.
SQL> conn strmadmin/strmadmin@db3
Connected.
SQL> begin dbms_streams_adm.add_table_rules
2 ( table_name => 'scott.taj',
3 streams_type => 'apply',
4 streams_name => 'apply_stream',
5 queue_name => 'strmadmin.streams_queue',
6 include_dml => true,
7 include_ddl => true,
8 source_database => 'DB1',
9 inclusion_rule => true);
10 end;
11 /
PL/SQL procedure successfully completed.
SQL> conn strmadmin/strmadmin@db3
Connected.
SQL> begin dbms_apply_adm.set_parameter
2 (apply_name => 'apply_stream',
3 parameter => 'disable_on_error',
4 value => 'n');
5 end;
6 /
PL/SQL procedure successfully completed.
SQL> begin dbms_apply_adm.start_apply
2 ( apply_name => 'apply_stream');
3 end;
4 /
PL/SQL procedure successfully completed.
NOTE: New site is added in Stream replication environment, just needed to test it.
SQL> conn scott/tiger@db1
Connected.
SQL> alter table taj add (flag char(1));
Table altered.
SQL> desc taj
Name Null? Type
----------------------------------------- -------- ------------------------
NO NOT NULL NUMBER
NAME VARCHAR2(20)
DDATE DATE
FLAG CHAR(1)---new entry
SQL> conn scott/tiger@db2
Connected.
SQL> desc taj
Name Null? Type
----------------------------------------- -------- ------------------------
NO NOT NULL NUMBER
NAME VARCHAR2(20)
DDATE DATE
FLAG CHAR(1) ---new entry
SQL> conn scott/tiger@db3
Connected.
SQL> desc taj
Name Null? Type
----------------------------------------- -------- ------------------------
NO NOT NULL NUMBER
NAME VARCHAR2(20)
DDATE DATE
FLAG CHAR(1) ---new entry
Overview
Database Version: 10.1.0.5.0
Windows XP sp2
Database Name and TNS name
DB1, db1 (source)
DB2, db2 (target) (Existing Target DB)
DB3,db3 (new target database)
1. Enable ARCHIVELOG MODE on both database
Reference: http://dbataj.blogspot.com/2007/09/how-to-enable-archivelog-mode.html
2. Create Stream administrator User
New target database: DB3
SQL> conn sys@db3 as sysdba
Enter password:
Connected.
SQL> create user strmadmin identified by strmadmin;
User created.
SQL> grant connect, resource, dba to strmadmin;
Grant succeeded.
SQL> begin dbms_streams_auth.grant_admin_privilege
2 ( grantee => 'strmadmin',
3 grant_privileges => true);
4 end;
5 /
PL/SQL procedure successfully completed.
3. Setup INIT parameters
SQL> alter system set global_names=true;
System altered.
SQL> alter system set streams_pool_size =100m;
System altered.
4. Create Database Link
SQL> conn strmadmin/strmadmin@db1
Connected.
SQL> create database link DB3
2 connect to strmadmin
3 identified by strmadmin
4 using 'DB3';
Database link created.
SQL> conn strmadmin/strmadmin@db3
Connected.
SQL> create database link db1
2 connect to strmadmin
3 identified by strmadmin
4 using 'DB1';
Database link created.
5. Setup Destination queues
SQL> conn strmadmin/strmadmin@db3
Connected.
SQL> EXEC DBMS_STREAMS_ADM.SET_UP_QUEUE();
PL/SQL procedure successfully completed.
6. Setup Schema for streams
SQL> conn sys@db3 as sysdba
Enter password:
Connected.
SQL> alter user scott account unlock identified by tiger;
User altered.
SQL> conn scott/tiger@db3
Connected.
SQL> create table taj (no number primary key, name varchar2(20), ddate date);
Table created.
7. Configure the propagation process
Source Database: DB1
SQL> conn strmadmin/strmadmin@db1
Connected.
SQL> begin dbms_streams_adm.add_table_propagation_rules
2 ( table_name => 'scott.taj',
3 streams_name => 'DB1_TO_DB3',
4 source_queue_name => 'strmadmin.streams_queue',
5 destination_queue_name => 'strmadmin.streams_queue@DB3',
6 include_dml => true,
7 include_ddl => true,
8 source_database => 'DB1',
9 inclusion_rule => true);
10 end;
11 /
PL/SQL procedure successfully completed.
8. Stop Apply process at existing target database
SQL> conn strmadmin/strmadmin@db2
Connected.
SQL> exec dbms_apply_adm.stop_apply('apply_stream');
PL/SQL procedure successfully completed.
9. Set the instantiation system change number (SCN)
Source Database: DB1
SQL> conn strmadmin/strmadmin@db1
Connected.
PREPARE instantiation system change number (SCN)
SQL> begin
2 dbms_capture_adm.prepare_table_instantiation( table_name => 'scott.taj');
3 end;
4 /
PL/SQL procedure successfully completed.
SQL> declare
2 source_scn number;
3 begin
4 source_scn := dbms_flashback.get_system_change_number();
5 dbms_apply_adm.set_table_instantiation_scn@DB3
6 ( source_object_name => 'scott.taj',
7 source_database_name => 'DB1',
8 instantiation_scn => source_scn);
9 end;
10 /
PL/SQL procedure successfully completed.
10. Configure the apply process at the destination database
SQL> conn strmadmin/strmadmin@db2
Connected.
SQL> exec dbms_apply_adm.start_apply('apply_stream');
PL/SQL procedure successfully completed.
SQL> conn strmadmin/strmadmin@db3
Connected.
SQL> begin dbms_streams_adm.add_table_rules
2 ( table_name => 'scott.taj',
3 streams_type => 'apply',
4 streams_name => 'apply_stream',
5 queue_name => 'strmadmin.streams_queue',
6 include_dml => true,
7 include_ddl => true,
8 source_database => 'DB1',
9 inclusion_rule => true);
10 end;
11 /
PL/SQL procedure successfully completed.
SQL> conn strmadmin/strmadmin@db3
Connected.
SQL> begin dbms_apply_adm.set_parameter
2 (apply_name => 'apply_stream',
3 parameter => 'disable_on_error',
4 value => 'n');
5 end;
6 /
PL/SQL procedure successfully completed.
SQL> begin dbms_apply_adm.start_apply
2 ( apply_name => 'apply_stream');
3 end;
4 /
PL/SQL procedure successfully completed.
NOTE: New site is added in Stream replication environment, just needed to test it.
SQL> conn scott/tiger@db1
Connected.
SQL> alter table taj add (flag char(1));
Table altered.
SQL> desc taj
Name Null? Type
----------------------------------------- -------- ------------------------
NO NOT NULL NUMBER
NAME VARCHAR2(20)
DDATE DATE
FLAG CHAR(1)---new entry
SQL> conn scott/tiger@db2
Connected.
SQL> desc taj
Name Null? Type
----------------------------------------- -------- ------------------------
NO NOT NULL NUMBER
NAME VARCHAR2(20)
DDATE DATE
FLAG CHAR(1) ---new entry
SQL> conn scott/tiger@db3
Connected.
SQL> desc taj
Name Null? Type
----------------------------------------- -------- ------------------------
NO NOT NULL NUMBER
NAME VARCHAR2(20)
DDATE DATE
FLAG CHAR(1) ---new entry
Thursday, January 24, 2008
Oracle Streams Setup between two database
Overview
Database Version: 10.1.0.5.0
Windows XP sp2
Database Name and TNS name
DB1, db1 (source)
DB2, db2 (target)
Set up below parameters on both databases (db1, db2)
1. Enable ARCHIVELOG MODE on both database
Reference: http://dbataj.blogspot.com/2007/09/how-to-enable-archivelog-mode.html
2. Create Stream administrator User
Source Database: DB1
SQL> conn sys@db1 as sysdba
Enter password:
Connected.
SQL> create user strmadmin identified by strmadmin;
User created.
SQL> grant connect, resource, dba to strmadmin;
Grant succeeded.
SQL> begin dbms_streams_auth.grant_admin_privilege
2 (grantee => 'strmadmin',
3 grant_privileges => true);
4 end;
5 /
PL/SQL procedure successfully completed.
SQL> grant select_catalog_role, select any dictionary to strmadmin;
Grant succeeded.
Target Database: DB2
SQL> conn sys@db2 as sysdba
Enter password:
Connected.
SQL> create user strmadmin identified by strmadmin;
User created.
SQL> grant connect, resource, dba to strmadmin;
Grant succeeded.
SQL> begin dbms_streams_auth.grant_admin_privilege
2 (grantee => 'strmadmin',
3 grant_privileges => true);
4 end;
5 /
PL/SQL procedure successfully completed.
SQL> grant select_catalog_role, select any dictionary to strmadmin;
Grant succeeded.
3. Setup INIT parameters
Source Database: DB1
SQL> conn sys@db1 as sysdba
Enter password:
Connected.
SQL> alter system set global_names=true;
System altered.
SQL> alter system set streams_pool_size = 100 m;
System altered.
Target Database: DB2
SQL> conn sys@db2 as sysdba
Enter password:
Connected.
SQL> alter system set global_names=true;
System altered.
SQL> alter system set streams_pool_size = 100 m;
System altered.
4. Create Database Link
Target Database: DB1
SQL> conn strmadmin/strmadmin@db1
Connected.
SQL> create database link db2
2 connect to strmadmin
3 identified by strmadmin
4 using 'DB2';
Database link created.
Source Database: DB2
SQL> conn strmadmin/strmadmin@db2
Connected.
SQL> create database link db1
2 connect to strmadmin
3 identified by strmadmin
4 using 'DB1';
Database link created.
5. Setup Source and Destination queues
Source Database: DB1
SQL> conn strmadmin/strmadmin@db1
Connected.
SQL> EXEC DBMS_STREAMS_ADM.SET_UP_QUEUE();
PL/SQL procedure successfully completed.
Target Database: DB2
SQL> conn strmadmin/strmadmin@db2
Connected.
SQL> EXEC DBMS_STREAMS_ADM.SET_UP_QUEUE();
PL/SQL procedure successfully completed.
6. Setup Schema for streams
Schema: SCOTT
Table: Taj
NOTE: Unlock scott schema because in 10g scott schema is locked by default
Source Database: DB1
SQL> conn sys@db1 as sysdba
Enter password:
Connected.
SQL> alter user scott account unlock identified by tiger;
User altered.
SQL> conn scott/tiger@db1
Connected.
SQL> create table TAJ ( no number primary key,name varchar2(20),ddate date);
Table created.
Target Database: DB2
SQL> conn sys@db2 as sysdba
Enter password:
Connected.
SQL> alter user scott account unlock identified by tiger;
User altered.
SQL> conn scott/tiger@db2
Connected.
SQL> create table TAJ ( no number primary key,name varchar2(20),ddate date);
Table created.
7. Setup Supplemental logging at the source database
Source Database: DB1
SQL> conn scott/tiger@db1
Connected.
SQL> alter table taj
2 add supplemental log data (primary key,unique) columns;
Table altered.
8. Configure capture process at the source database
Source Database: DB1
SQL> conn strmadmin/strmadmin@db1
Connected.
SQL> begin dbms_streams_adm.add_table_rules
2 ( table_name => 'scott.taj',
3 streams_type => 'capture',
4 streams_name => 'capture_stream',
5 queue_name=> 'strmadmin.streams_queue',
6 include_dml => true,
7 include_ddl => true,
8 inclusion_rule => true);
9 end;
10 /
PL/SQL procedure successfully completed.
9. Configure the propagation process
Source Database: DB1
SQL> conn strmadmin/strmadmin@db1
Connected.
SQL> begin dbms_streams_adm.add_table_propagation_rules
2 ( table_name => 'scott.taj',
3 streams_name => 'DB1_TO_DB2',
4 source_queue_name => 'strmadmin.streams_queue',
5 destination_queue_name => 'strmadmin.streams_queue@DB2',
6 include_dml => true,
7 include_ddl => true,
8 source_database => 'DB1',
9 inclusion_rule => true);
10 end;
11 /
PL/SQL procedure successfully completed.
10. Set the instantiation system change number (SCN)
Source Database: DB1
SQL> CONN STRMADMIN/STRMADMIN@DB1
Connected.
SQL> declare
2 source_scn number;
3 begin
4 source_scn := dbms_flashback.get_system_change_number();
5 dbms_apply_adm.set_table_instantiation_scn@DB2
6 ( source_object_name => 'scott.taj',
7 source_database_name => 'DB1',
8 instantiation_scn => source_scn);
9 end;
10 /
PL/SQL procedure successfully completed.
11. Configure the apply process at the destination database
Target Database: DB2
SQL> conn strmadmin/strmadmin@db2
Connected.
SQL> begin dbms_streams_adm.add_table_rules
2 ( table_name => 'scott.taj',
3 streams_type => 'apply',
4 streams_name => 'apply_stream',
5 queue_name => 'strmadmin.streams_queue',
6 include_dml => true,
7 include_ddl => true,
8 source_database => 'DB1',
9 inclusion_rule => true);
10 end;
11 /
PL/SQL procedure successfully completed.
12. Start the capture and apply processes
Source Database: DB1
SQL> conn strmadmin/strmadmin@db1
Connected.
SQL> begin dbms_capture_adm.start_capture
2 ( capture_name => 'capture_stream');
3 end;
4 /
PL/SQL procedure successfully completed.
Target Database: DB2
SQL> conn strmadmin/strmadmin@db2
Connected.
SQL> begin dbms_apply_adm.set_parameter
2 ( apply_name => 'apply_stream',
3 parameter => 'disable_on_error',
4 value => 'n');
5 end;
6 /
PL/SQL procedure successfully completed.
SQL> begin
2 dbms_apply_adm.start_apply
3 ( apply_name => 'apply_stream');
4 end;
5 /
PL/SQL procedure successfully completed.
NOTE: Stream replication environment is ready, just needed to test it.
SQL> conn scott/tiger@db1
Connected.
SQL> --DDL operation
SQL> alter table taj add (flag char(1));
Table altered.
SQL> --DML operation
SQL> begin
2 insert into taj values (1,'first_entry',sysdate,1);
3 commit;
4 end;
5 /
PL/SQL procedure successfully completed.
SQL> conn scott/tiger@db2
Connected.
SQL> --TEST DDL operation
SQL> desc taj
Name Null? Type
----------------------------------------- -------- ----------------------------
NO NOT NULL NUMBER
NAME VARCHAR2(20)
DDATE DATE
FLAG CHAR(1)
SQL> --TEST DML operation
SQL> select * from taj;
NO NAME DDATE F
---------- -------------------- --------- -
1 first_entry 24-JAN-08 1
Reference:
http://www.oracle.com/technology/oramag/oracle/04-nov/o64streams.html
Database Version: 10.1.0.5.0
Windows XP sp2
Database Name and TNS name
DB1, db1 (source)
DB2, db2 (target)
Set up below parameters on both databases (db1, db2)
1. Enable ARCHIVELOG MODE on both database
Reference: http://dbataj.blogspot.com/2007/09/how-to-enable-archivelog-mode.html
2. Create Stream administrator User
Source Database: DB1
SQL> conn sys@db1 as sysdba
Enter password:
Connected.
SQL> create user strmadmin identified by strmadmin;
User created.
SQL> grant connect, resource, dba to strmadmin;
Grant succeeded.
SQL> begin dbms_streams_auth.grant_admin_privilege
2 (grantee => 'strmadmin',
3 grant_privileges => true);
4 end;
5 /
PL/SQL procedure successfully completed.
SQL> grant select_catalog_role, select any dictionary to strmadmin;
Grant succeeded.
Target Database: DB2
SQL> conn sys@db2 as sysdba
Enter password:
Connected.
SQL> create user strmadmin identified by strmadmin;
User created.
SQL> grant connect, resource, dba to strmadmin;
Grant succeeded.
SQL> begin dbms_streams_auth.grant_admin_privilege
2 (grantee => 'strmadmin',
3 grant_privileges => true);
4 end;
5 /
PL/SQL procedure successfully completed.
SQL> grant select_catalog_role, select any dictionary to strmadmin;
Grant succeeded.
3. Setup INIT parameters
Source Database: DB1
SQL> conn sys@db1 as sysdba
Enter password:
Connected.
SQL> alter system set global_names=true;
System altered.
SQL> alter system set streams_pool_size = 100 m;
System altered.
Target Database: DB2
SQL> conn sys@db2 as sysdba
Enter password:
Connected.
SQL> alter system set global_names=true;
System altered.
SQL> alter system set streams_pool_size = 100 m;
System altered.
4. Create Database Link
Target Database: DB1
SQL> conn strmadmin/strmadmin@db1
Connected.
SQL> create database link db2
2 connect to strmadmin
3 identified by strmadmin
4 using 'DB2';
Database link created.
Source Database: DB2
SQL> conn strmadmin/strmadmin@db2
Connected.
SQL> create database link db1
2 connect to strmadmin
3 identified by strmadmin
4 using 'DB1';
Database link created.
5. Setup Source and Destination queues
Source Database: DB1
SQL> conn strmadmin/strmadmin@db1
Connected.
SQL> EXEC DBMS_STREAMS_ADM.SET_UP_QUEUE();
PL/SQL procedure successfully completed.
Target Database: DB2
SQL> conn strmadmin/strmadmin@db2
Connected.
SQL> EXEC DBMS_STREAMS_ADM.SET_UP_QUEUE();
PL/SQL procedure successfully completed.
6. Setup Schema for streams
Schema: SCOTT
Table: Taj
NOTE: Unlock scott schema because in 10g scott schema is locked by default
Source Database: DB1
SQL> conn sys@db1 as sysdba
Enter password:
Connected.
SQL> alter user scott account unlock identified by tiger;
User altered.
SQL> conn scott/tiger@db1
Connected.
SQL> create table TAJ ( no number primary key,name varchar2(20),ddate date);
Table created.
Target Database: DB2
SQL> conn sys@db2 as sysdba
Enter password:
Connected.
SQL> alter user scott account unlock identified by tiger;
User altered.
SQL> conn scott/tiger@db2
Connected.
SQL> create table TAJ ( no number primary key,name varchar2(20),ddate date);
Table created.
7. Setup Supplemental logging at the source database
Source Database: DB1
SQL> conn scott/tiger@db1
Connected.
SQL> alter table taj
2 add supplemental log data (primary key,unique) columns;
Table altered.
8. Configure capture process at the source database
Source Database: DB1
SQL> conn strmadmin/strmadmin@db1
Connected.
SQL> begin dbms_streams_adm.add_table_rules
2 ( table_name => 'scott.taj',
3 streams_type => 'capture',
4 streams_name => 'capture_stream',
5 queue_name=> 'strmadmin.streams_queue',
6 include_dml => true,
7 include_ddl => true,
8 inclusion_rule => true);
9 end;
10 /
PL/SQL procedure successfully completed.
9. Configure the propagation process
Source Database: DB1
SQL> conn strmadmin/strmadmin@db1
Connected.
SQL> begin dbms_streams_adm.add_table_propagation_rules
2 ( table_name => 'scott.taj',
3 streams_name => 'DB1_TO_DB2',
4 source_queue_name => 'strmadmin.streams_queue',
5 destination_queue_name => 'strmadmin.streams_queue@DB2',
6 include_dml => true,
7 include_ddl => true,
8 source_database => 'DB1',
9 inclusion_rule => true);
10 end;
11 /
PL/SQL procedure successfully completed.
10. Set the instantiation system change number (SCN)
Source Database: DB1
SQL> CONN STRMADMIN/STRMADMIN@DB1
Connected.
SQL> declare
2 source_scn number;
3 begin
4 source_scn := dbms_flashback.get_system_change_number();
5 dbms_apply_adm.set_table_instantiation_scn@DB2
6 ( source_object_name => 'scott.taj',
7 source_database_name => 'DB1',
8 instantiation_scn => source_scn);
9 end;
10 /
PL/SQL procedure successfully completed.
11. Configure the apply process at the destination database
Target Database: DB2
SQL> conn strmadmin/strmadmin@db2
Connected.
SQL> begin dbms_streams_adm.add_table_rules
2 ( table_name => 'scott.taj',
3 streams_type => 'apply',
4 streams_name => 'apply_stream',
5 queue_name => 'strmadmin.streams_queue',
6 include_dml => true,
7 include_ddl => true,
8 source_database => 'DB1',
9 inclusion_rule => true);
10 end;
11 /
PL/SQL procedure successfully completed.
12. Start the capture and apply processes
Source Database: DB1
SQL> conn strmadmin/strmadmin@db1
Connected.
SQL> begin dbms_capture_adm.start_capture
2 ( capture_name => 'capture_stream');
3 end;
4 /
PL/SQL procedure successfully completed.
Target Database: DB2
SQL> conn strmadmin/strmadmin@db2
Connected.
SQL> begin dbms_apply_adm.set_parameter
2 ( apply_name => 'apply_stream',
3 parameter => 'disable_on_error',
4 value => 'n');
5 end;
6 /
PL/SQL procedure successfully completed.
SQL> begin
2 dbms_apply_adm.start_apply
3 ( apply_name => 'apply_stream');
4 end;
5 /
PL/SQL procedure successfully completed.
NOTE: Stream replication environment is ready, just needed to test it.
SQL> conn scott/tiger@db1
Connected.
SQL> --DDL operation
SQL> alter table taj add (flag char(1));
Table altered.
SQL> --DML operation
SQL> begin
2 insert into taj values (1,'first_entry',sysdate,1);
3 commit;
4 end;
5 /
PL/SQL procedure successfully completed.
SQL> conn scott/tiger@db2
Connected.
SQL> --TEST DDL operation
SQL> desc taj
Name Null? Type
----------------------------------------- -------- ----------------------------
NO NOT NULL NUMBER
NAME VARCHAR2(20)
DDATE DATE
FLAG CHAR(1)
SQL> --TEST DML operation
SQL> select * from taj;
NO NAME DDATE F
---------- -------------------- --------- -
1 first_entry 24-JAN-08 1
Reference:
http://www.oracle.com/technology/oramag/oracle/04-nov/o64streams.html
Wednesday, January 23, 2008
Oracle Listener Security: Before 10g but not in 10g.
Did you thing about listener security from Unauthorized access from remote site ?
We can protect our oracle listener through password to prevent Unauthorized access.
There is three way to set listener password.
1. Manually (UNENCRYPTED FORMAT)
2. LSNRCTL tools (ENCRYPTED FORMAT)
3. OEM (Oracle Enterprise Manager)
Reference: http://www.petefinnigan.com/ramblings/set_listener_password.htm
Above procedure is useful only for prior 10g release becuase now in 10g and later listener less password is more secure than listener password.
below is some references there discuss security experts about their views
1. http://andrewfraser.wordpress.com/2007/05/24/listener-passwords-always-for-9i-never-for-10g/
2. http://laurentschneider.com/wordpress/2005/12/encrypted-listener-password.html
3. http://www.petefinnigan.com/weblog/archives/00000653.htm
4. http://andrewfraser.wordpress.com/2007/01/11/9i-unix-startup-shutdown-with-listener-password/
5. http://www.petefinnigan.com/forum/yabb/YaBB.cgi?board=ora_sec;action=display;num=1127398031
We can protect our oracle listener through password to prevent Unauthorized access.
There is three way to set listener password.
1. Manually (UNENCRYPTED FORMAT)
2. LSNRCTL tools (ENCRYPTED FORMAT)
3. OEM (Oracle Enterprise Manager)
Reference: http://www.petefinnigan.com/ramblings/set_listener_password.htm
Above procedure is useful only for prior 10g release becuase now in 10g and later listener less password is more secure than listener password.
below is some references there discuss security experts about their views
1. http://andrewfraser.wordpress.com/2007/05/24/listener-passwords-always-for-9i-never-for-10g/
2. http://laurentschneider.com/wordpress/2005/12/encrypted-listener-password.html
3. http://www.petefinnigan.com/weblog/archives/00000653.htm
4. http://andrewfraser.wordpress.com/2007/01/11/9i-unix-startup-shutdown-with-listener-password/
5. http://www.petefinnigan.com/forum/yabb/YaBB.cgi?board=ora_sec;action=display;num=1127398031
Tuesday, January 22, 2008
Connecting with sys user with Or without pwd
As we know "SYS" & "SYSDBA" is superuser privilege in oracle database.
There is two method to connect SYSDBA user.
For example:
SQL> CONN / AS SYSDBA ---without password
For without password need follow thing to be done.
For example:
SQL> CONN SYS/PWD AS SYSDBA --- with password or prevent to connect without password.
There is two method to connect SYSDBA user.
1. WITH PASSWORD
2. WITHOUT PASSWORD
For example:
SQL> CONN / AS SYSDBA ---without password
For without password need follow thing to be done.
1. Oracle Database User must have SYSDBA privileges.
2. Operating System User must add in DBA group.
On windows: ORA_DBA group
On linux: DBA group
3. In sqlnet.ora file must content "NTS" in below line
SQLNET.AUTHENTICATION_SERVICES=(NTS)
For example:
SQL> CONN SYS/PWD AS SYSDBA --- with password or prevent to connect without password.
1. Remove OS user from DBA group
2. Edit sqlnet.ora file and change "NONE" to "NTS" in below line
SQLNET.AUTHENTICATION_SERVICES=(NONE)
Default Value is NTS when sqlnet.ora file created.
Saturday, January 12, 2008
Database & Patch set Upgrade
Database & patch upgrade is one of the most important work for Database Administrator.
Before going further first understand what are “upgrade” & “migration” & “patch set” & “CPU patch” word.
Upgrade:
Usually use for oracle database upgrade.
Eg: upgrade from oracle 9i to oracle 10g.
Migration:
Usually use for migrate non oracle database to oracle database.
Eg: From SQL server database to oracle database
Patch set:
There are two types of patch upgrade
1. Patch set update
From one oracle version to another oracle version
Eg: oracle 9i to oracle 10g
NOTE: Patch set based on oracle base release
Eg: oracle 10gr1 (10.1.0.2.0) and for 10gr2 (10.2.0.1.0)
2. CPU patch (Critical Patch update)
Patches is apply for fix database bug suppose after apply latest patch set for current release if there is any bug occur then oracle release cpu patches in regular interval to fix those bug.
Eg: oracle 10gr1 base rel: 10.1.0.2.0, Latest Patch set: 10.1.0.5.0, Latest CPU patch: Jan-08 CPU patch for 10.1.0.5.0
NOTE: CPU patch based on latest patch set.
For more info: http://www.oracle.com/technology/deploy/security/alerts.htm
How to upgrade database to oracle 10gr2?
Determine the Upgrade Path to the New Oracle Database 10g Release
http://download.oracle.com/docs/cd/B19306_01/server.102/b14238/preup.htm#i1007814
Before upgrading process we need to define upgrade method
There are four methods to upgrade our database
1. DBUA (Database upgrade assistant)
2. Manually upgrade
3. Export/Import
4. Data copying
http://download.oracle.com/docs/cd/B19306_01/server.102/b14238/preup.htm#i694345
Patch set upgrade example:
1. From oracle 9ir2 (9.2.0.2 to 9.2.0.8) on Windows http://babudba.blogspot.com/2007/11/patch-installation.html
2. From oracle 9ir2 (9.2.0.1 to 9.2.0.7) on Solaris
http://sabdarsyed.blogspot.com/2007/02/upgrade-oracle-database-9201-to-9207-on.html
3. From oracle 10gr1 (10.1.0.2 to 10.1.0.5) on windows
http://dbataj.blogspot.com/2007/06/upgrading-database-from-101020-to.html
4. From oracle 10gr2 (10.2.0.2 to 10.2.0.3) on windows
http://babudba.blogspot.com/2007/11/upgrade-oracle-10202-to-10203.html
5. From oracle 10gr2 (10.2.0.1 to 10.2.0.3) on Linux
http://sabdarsyed.blogspot.com/2007/03/upgrading-oracle-database-server-10g.html
Critical Patch update example:
On Oracle 10gr1 (10.1.0.5.0) patch set
http://dbataj.blogspot.com/2007/06/critical-patch-update.html
Before going further first understand what are “upgrade” & “migration” & “patch set” & “CPU patch” word.
Upgrade:
Usually use for oracle database upgrade.
Eg: upgrade from oracle 9i to oracle 10g.
Migration:
Usually use for migrate non oracle database to oracle database.
Eg: From SQL server database to oracle database
Patch set:
There are two types of patch upgrade
1. Patch set update
From one oracle version to another oracle version
Eg: oracle 9i to oracle 10g
NOTE: Patch set based on oracle base release
Eg: oracle 10gr1 (10.1.0.2.0) and for 10gr2 (10.2.0.1.0)
2. CPU patch (Critical Patch update)
Patches is apply for fix database bug suppose after apply latest patch set for current release if there is any bug occur then oracle release cpu patches in regular interval to fix those bug.
Eg: oracle 10gr1 base rel: 10.1.0.2.0, Latest Patch set: 10.1.0.5.0, Latest CPU patch: Jan-08 CPU patch for 10.1.0.5.0
NOTE: CPU patch based on latest patch set.
For more info: http://www.oracle.com/technology/deploy/security/alerts.htm
How to upgrade database to oracle 10gr2?
Determine the Upgrade Path to the New Oracle Database 10g Release
http://download.oracle.com/docs/cd/B19306_01/server.102/b14238/preup.htm#i1007814
Before upgrading process we need to define upgrade method
There are four methods to upgrade our database
1. DBUA (Database upgrade assistant)
2. Manually upgrade
3. Export/Import
4. Data copying
http://download.oracle.com/docs/cd/B19306_01/server.102/b14238/preup.htm#i694345
Patch set upgrade example:
1. From oracle 9ir2 (9.2.0.2 to 9.2.0.8) on Windows http://babudba.blogspot.com/2007/11/patch-installation.html
2. From oracle 9ir2 (9.2.0.1 to 9.2.0.7) on Solaris
http://sabdarsyed.blogspot.com/2007/02/upgrade-oracle-database-9201-to-9207-on.html
3. From oracle 10gr1 (10.1.0.2 to 10.1.0.5) on windows
http://dbataj.blogspot.com/2007/06/upgrading-database-from-101020-to.html
4. From oracle 10gr2 (10.2.0.2 to 10.2.0.3) on windows
http://babudba.blogspot.com/2007/11/upgrade-oracle-10202-to-10203.html
5. From oracle 10gr2 (10.2.0.1 to 10.2.0.3) on Linux
http://sabdarsyed.blogspot.com/2007/03/upgrading-oracle-database-server-10g.html
Critical Patch update example:
On Oracle 10gr1 (10.1.0.5.0) patch set
http://dbataj.blogspot.com/2007/06/critical-patch-update.html
Tuesday, January 8, 2008
How to Drop Datafile from tablespace
Tips:
Oracle Certification - Tips
DBA Preparation - Tips
Practical DBA - Tips
How to Drop Datafile from tablespace.
Datafile is physical part of database and it is take storage device /file system to store/save oracle data.
Whenever datafile grow more space needed, sametime unwanted datafile created, sametime datafile created in wrong tablespace then we need to drop datafile.
Below is overview to drop a datafile from tablespace
NOTE: Use below procedure after test on TESTING database.
Oracle 8i
There is no direct sql command to drop datafile from tablespace.
In that case we need to drop tablespace after move all data to new tablespace.
1. create new tablespace
2. move all table to new tablespace
3. move all index to new tablespace
4. move all other objects to new tablespace
5. drop old tablespace with including contents;
6. through OS command remove all datafiles belongs to droped tablespace.
Oracle 9ir1
Again There is no direct sql command to drop datafile from tablespace.
In that case we need to drop tablespace after move all data to new tablespace.
1. create new tablespace
2. move all table to new tablespace
3. move all index to new tablespace
4. move all other objects to new tablespace
5. drop old tablespace with including contents and datafiles;
NOTE: Oracle9ir1 is introduce “and datafiles” clause with drop tablespace statement which remove datafiles from file system.
Oracle 9ir2
Again There is no direct sql command to drop datafile from tablespace.
In that case we need to drop tablespace after move all data to new tablespace.
1. create new tablespace
2. move all table to new tablespace
3. move all index to new tablespace
4. move all other objects to new tablespace
5. drop old tablespace with including contents and datafiles;
But we can drop TEMPFILE through below statement.
SQL> alter database tempfile ‘/u02/oracle/temp01.dbf’ drop including datafiles;
[not tested]
Oracle 10gr1
Same like Oracle 9ir2
Oracle 10gr2
Now in 10gr2 oracle introduce to drop datafile or tempfile from tablespace through single sql command with few restrictions.
From Oracle Documentation:
You use the DROP DATAFILE and DROP TEMPFILE clauses of the ALTER TABLESPACE command to drop a single datafile or tempfile. The datafile must be empty. (A datafile is considered to be empty when no extents remain allocated from it.) When you drop a datafile or tempfile, references to the datafile or tempfile are removed from the data dictionary and control files, and the physical file is deleted from the file system or Automatic Storage Management (ASM) disk group.
Datafiles:
Alter tablespace data DROP DATAFILE ‘/u02/data01.dbf’;
Tempfiles:
Alter tablespace temp DROP TEMPFILE ‘/u02/temp01.dbf’;
Restrictions for drop datafiles
The following are restrictions for dropping datafiles and tempfiles:
• The database must be open.
• If a datafile is not empty, it cannot be dropped.
If you must remove a datafile that is not empty and that cannot be made empty by dropping schema objects, you must drop the tablespace that contains the datafile.
• You cannot drop the first or only datafile in a tablespace.
This means that DROP DATAFILE cannot be used with a bigfile tablespace.
• You cannot drop datafiles in a read-only tablespace.
• You cannot drop datafiles in the SYSTEM tablespace.
• If a datafile in a locally managed tablespace is offline, it cannot be dropped
Oracle 11gr1
Same like Oracle 10gr2
Reference:
http://www.akadia.com/services/ora_remove_datafile.html
http://download.oracle.com/docs/cd/B19306_01/server.102/b14231/dfiles.htm#sthref1396
Oracle Certification - Tips
DBA Preparation - Tips
Practical DBA - Tips
How to Drop Datafile from tablespace.
Datafile is physical part of database and it is take storage device /file system to store/save oracle data.
Whenever datafile grow more space needed, sametime unwanted datafile created, sametime datafile created in wrong tablespace then we need to drop datafile.
Below is overview to drop a datafile from tablespace
NOTE: Use below procedure after test on TESTING database.
Oracle 8i
There is no direct sql command to drop datafile from tablespace.
In that case we need to drop tablespace after move all data to new tablespace.
1. create new tablespace
2. move all table to new tablespace
3. move all index to new tablespace
4. move all other objects to new tablespace
5. drop old tablespace with including contents;
6. through OS command remove all datafiles belongs to droped tablespace.
Oracle 9ir1
Again There is no direct sql command to drop datafile from tablespace.
In that case we need to drop tablespace after move all data to new tablespace.
1. create new tablespace
2. move all table to new tablespace
3. move all index to new tablespace
4. move all other objects to new tablespace
5. drop old tablespace with including contents and datafiles;
NOTE: Oracle9ir1 is introduce “and datafiles” clause with drop tablespace statement which remove datafiles from file system.
Oracle 9ir2
Again There is no direct sql command to drop datafile from tablespace.
In that case we need to drop tablespace after move all data to new tablespace.
1. create new tablespace
2. move all table to new tablespace
3. move all index to new tablespace
4. move all other objects to new tablespace
5. drop old tablespace with including contents and datafiles;
But we can drop TEMPFILE through below statement.
SQL> alter database tempfile ‘/u02/oracle/temp01.dbf’ drop including datafiles;
[not tested]
Oracle 10gr1
Same like Oracle 9ir2
Oracle 10gr2
Now in 10gr2 oracle introduce to drop datafile or tempfile from tablespace through single sql command with few restrictions.
From Oracle Documentation:
You use the DROP DATAFILE and DROP TEMPFILE clauses of the ALTER TABLESPACE command to drop a single datafile or tempfile. The datafile must be empty. (A datafile is considered to be empty when no extents remain allocated from it.) When you drop a datafile or tempfile, references to the datafile or tempfile are removed from the data dictionary and control files, and the physical file is deleted from the file system or Automatic Storage Management (ASM) disk group.
Datafiles:
Alter tablespace data DROP DATAFILE ‘/u02/data01.dbf’;
Tempfiles:
Alter tablespace temp DROP TEMPFILE ‘/u02/temp01.dbf’;
Restrictions for drop datafiles
The following are restrictions for dropping datafiles and tempfiles:
• The database must be open.
• If a datafile is not empty, it cannot be dropped.
If you must remove a datafile that is not empty and that cannot be made empty by dropping schema objects, you must drop the tablespace that contains the datafile.
• You cannot drop the first or only datafile in a tablespace.
This means that DROP DATAFILE cannot be used with a bigfile tablespace.
• You cannot drop datafiles in a read-only tablespace.
• You cannot drop datafiles in the SYSTEM tablespace.
• If a datafile in a locally managed tablespace is offline, it cannot be dropped
Oracle 11gr1
Same like Oracle 10gr2
Reference:
http://www.akadia.com/services/ora_remove_datafile.html
http://download.oracle.com/docs/cd/B19306_01/server.102/b14231/dfiles.htm#sthref1396
Saturday, January 5, 2008
During import table created in wrong tablespace.
During import table is created in other than default tablespace is happen what is reason.
Suppose we have two databases.
1. Orcl
2. Hgc
Now i created one table in ORCL database in SYSTEM tablespace.
Now in HGC database i import above created table in user default tablespace.
NOTE:
1. I assign default tablespace "USERS" to scott user which import table.
2. Scott user having "connect" & "resource" role privileges
Question: NOW TELL me during IMPORT in which tablespace "USERS" or "SYSTEM" tbs table "test_orcl" created ?
NOW guess your answer..we will check later ...
NOW import table in HGC database.
now we check in which tablespace table is created ?
ofh table is created in SYSTEM tablespace instead of user default tablespace means "USERS' why it so ?
Again import table but now make one changes ...
1. Revoke "resource" role and grant explicit quota on tablespace to user.
NOW import again...
But what is actual reason...?
1. Resource role having "unlimited tablespace" privileges means database user have quota on all tablespace in database if user having "unlimited tablespace" privileges.
2. table is created in "system" tablespace becuase tables is actually created in SYSTEM tablespace on "ORCL" database during first time creation.
Suppose we have two databases.
1. Orcl
2. Hgc
Now i created one table in ORCL database in SYSTEM tablespace.
SQL> create table test_orcl ( no number) tablespace SYSTEM;
Table created.
Now in HGC database i import above created table in user default tablespace.
SQL> conn system/oracle@hgc
Connected.
SQL> alter user scott default tablespace USERS;
User altered.
NOTE:
1. I assign default tablespace "USERS" to scott user which import table.
2. Scott user having "connect" & "resource" role privileges
Question: NOW TELL me during IMPORT in which tablespace "USERS" or "SYSTEM" tbs table "test_orcl" created ?
NOW guess your answer..we will check later ...
SQL> conn scott/tiger@Orcl
Connected.
SQL> host exp scott/tiger@Orcl file=c:\test_orcl.dmp tables=test_orcl
Export: Release 10.1.0.5.0 - Production on Wed Jan 2 11:59:18 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
. . exporting table TEST_ORCL 0 rows exported
Export terminated successfully without warnings.
NOW import table in HGC database.
SQL> conn scott/tiger@HGC
Connected.
SQL> host imp scott/tiger@HGC file=c:\test_orcl.dmp fromuser=scott touser=scott
Import: Release 10.1.0.5.0 - Production on Wed Jan 2 12:02:26 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
. . importing table "TEST_ORCL" 0 rows imported
Import terminated successfully without warnings.
now we check in which tablespace table is created ?
SQL> select table_name,tablespace_name
2 from user_tables
3 where table_name='TEST_ORCL';
TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------
TEST_ORCL SYSTEM
ofh table is created in SYSTEM tablespace instead of user default tablespace means "USERS' why it so ?
Again import table but now make one changes ...
1. Revoke "resource" role and grant explicit quota on tablespace to user.
SQL> conn system/oracle@hgc
Connected.
SQL> revoke resource from scott;
Revoke succeeded.
SQL> alter user scott quota 100m on users;
User altered.
NOW import again...
SQL> conn scott/tiger@hgc
Connected.
SQL> drop table test_orcl purge;
Table dropped.
SQL> host imp scott/tiger@HGC file=c:\test_orcl.dmp fromuser=scott touser=scott
Import: Release 10.1.0.5.0 - Production on Wed Jan 2 12:06:51 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
. . importing table "TEST_ORCL" 0 rows imported
Import terminated successfully without warnings.
SQL> select table_name,tablespace_name
2 from user_tables
3 where table_name='TEST_ORCL';
TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------
TEST_ORCL USERS
But what is actual reason...?
1. Resource role having "unlimited tablespace" privileges means database user have quota on all tablespace in database if user having "unlimited tablespace" privileges.
2. table is created in "system" tablespace becuase tables is actually created in SYSTEM tablespace on "ORCL" database during first time creation.
Export file created by EXPORT:V10.01.00 via conventional path
import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
"CREATE TABLE "TEST_ORCL" ("NO" NUMBER) PCTFREE 10 PCTUSED 40 INITRANS 1 MA"
"XTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL "
"DEFAULT) TABLESPACE "SYSTEM" LOGGING NOCOMPRESS"
. . skipping table "TEST_ORCL"
Subscribe to:
Posts (Atom)