Friday, September 26, 2008

Create readonly user in oracle?

This article is tested in oracle9i/oracle10g.

Sometime, DBA requires to create a readonly schema to allow non-DBA users to monitor the database or application user wanted to only view the application data . When we create readonly user, it should have an ability to access all the DB dictionary includes v$ tables. We can also grant only select access to the application schema tables.

Here is the steps to setup the readonly schema. The below steps needs to be done in DBA users.

Step 1
create user readonly
identified by readonly;

Step 2
grant create synonym,
create session,
select any dictionary to readonly;

Step 3
create the synonym in readonly schema for application tables.

Now if we connect readonly schema, you can see application tables as well as all the database dictionary tables. You can also monitor the database. If you are using any version below oracle9i, you need to grant SELECT ANY CATALOG role instead of SELECT ANY DICTIONARY privilege.

How do we kill inactive session in oracle?

This article is witten in oracle9i. It should apply to oracle1og and further release too.

Inactive session
Oracle Marks the session as INACTIVE when the moment, session is ideal. These sessions are remain connected to the database with a status in v$session of INACTIVE. A user starts a program/session, then leaves it running and idle for an extended period of time. Some time, user connect to the database and go for coffee and chat with somebody and come back to desk and check mails. During this time, the session becomes INACTIVE.

Dead Connection
Sometime, user shutdown the machine without logout the database connection or network prevent to connect the database, then connection becomes dead connection. SQL NET detect the dead connection and release the resource. Please refer metalink id (Note:395505.1, Note:151972.1) for more details about DCD(dead connection deduction).

How do we kill the inactive session?

There are couple of ways to kill the inactive session.

Option 1
We can write the script to kill the session. The below script kills any session which is INACTIVE.

SELECT 'ALTER SYSTEM KILL SESSION '''''sid','serial#''''' immediate;'
FROM v$session
WHERE status ='INACTIVE'

In case, if you need to kill all the session which are inactive for last four hours, then the above script is not an option. The above command kills all the session regardless of when it becomes inactive. Next option is best option to kill the session which are inactive for certain period of time.

Option 2
We can disconnect the inactive session through Oracle profile. We can change the idle_time in the user profile. Here are the steps to change the idle_time.

Step1 First we need to change the resource_limit to TRUE. By default, it is FALSE.

SQL> select value from gv$parameter
2 where name='resource_limit';

VALUE
--------------------------------------------------------------------------------
FALSE

SQL> ALTER SYSTEM SET resource_limit=TRUE;
System altered.

Step2 Alter the profile to change the idle_time. Here i am using custom profile named developer.

SQL> ALTER profile developer limit idle_time 240;

Profile altered.

Step 3 If you are not using developer profile for application schema, then assign this profile to application schema.

SQL> alter user app_data profile developer;

User altered.

Tuesday, September 23, 2008

Steps to build Oracle Table level Streams?

Oracle Streams were introduced in oracle 9.2. Oracle Streams are a generic mechanism to replicate data between one database to another database. The processing of streams is divided into three main processes(Capture, Staging and Apply).

This article provides step by step instruction to build the three way streams. This article is tested in oracle 9.2.0.8. We have three database DB1, DB2, DB3. The steam is setup on dept table for scott schema on all three instance. Any changes(DML/DDL) made on any of the database will be replicate to other two database.

Instance Setup

The below init parmeters needs to be set on intance level for all three database(DB1,DB2,DB3).

ALTER SYSTEM SET JOB_QUEUE_PROCESSES = 20;
ALTER SYSTEM SET AQ_TM_PROCESSES = 2;
ALTER SYSTEM SET GLOBAL_NAMES = TRUE;
ALTER SYSTEM SET COMPATIBLE='9.2.0' SCOPE = SPFILE;
ALTER SYSTEM SET LOG_PARALLELISM=1 SCOPE = SPFILE;
SHUTDOWN IMMEDIATE;
STARTUP;

Stream admin Setup on DB1

CONNECT SYS/password@db1 AS SYSDBA;


CREATE TABLESPACE LOGMNRTS DATAFILE 'C:/ORACLE/ORADATA/DB1/logmnrts.dbf' SIZE 25M
AUTOEXTEND ON MAXSIZE UNLIMITED
/

BEGIN
DBMS_LOGMNR_D.SET_TABLESPACE('LOGMNRTS');
END;
/

create user strmadmin identified by strmadmin
/

alter user strmadmin default tablespace LOGMNRTS
/

grant connect,resource,aq_administrator_role,dba to strmadmin
/

GRANT SELECT ANY DICTIONARY TO STRMADMIN;

GRANT EXECUTE ON DBMS_AQ TO STRMADMIN;
GRANT EXECUTE ON DBMS_AQADM TO STRMADMIN;
GRANT EXECUTE ON DBMS_FLASHBACK TO STRMADMIN;
GRANT EXECUTE ON DBMS_STREAMS_ADM TO STRMADMIN;
GRANT EXECUTE ON DBMS_CAPTURE_ADM TO STRMADMIN;
GRANT EXECUTE ON DBMS_APPLY_ADM TO STRMADMIN;
GRANT EXECUTE ON DBMS_RULE_ADM TO STRMADMIN;
GRANT EXECUTE ON DBMS_PROPAGATION_ADM TO STRMADMIN;


BEGIN
DBMS_AQADM.GRANT_SYSTEM_PRIVILEGE(
privilege => 'ENQUEUE_ANY',
grantee => 'STRMADMIN',
admin_option => FALSE);
END;
/

BEGIN
DBMS_AQADM.GRANT_SYSTEM_PRIVILEGE(
privilege => 'DEQUEUE_ANY',
grantee => 'STRMADMIN',
admin_option => FALSE);
END;
/

BEGIN
DBMS_AQADM.GRANT_SYSTEM_PRIVILEGE(
privilege => 'MANAGE_ANY',
grantee => 'STRMADMIN',
admin_option => TRUE);
END;
/

BEGIN
DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
privilege => DBMS_RULE_ADM.CREATE_EVALUATION_CONTEXT_OBJ,
grantee => 'STRMADMIN',
grant_option => TRUE);
DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
privilege => DBMS_RULE_ADM.CREATE_RULE_SET_OBJ,
grantee => 'STRMADMIN',
grant_option => TRUE);
DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
privilege => DBMS_RULE_ADM.CREATE_RULE_OBJ,
grantee => 'STRMADMIN',
grant_option => TRUE);
END;
/

BEGIN
DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
privilege => DBMS_RULE_ADM.CREATE_ANY_RULE_SET,
grantee => 'STRMADMIN',
grant_option => TRUE);
DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
privilege => DBMS_RULE_ADM.ALTER_ANY_RULE_SET,
grantee => 'STRMADMIN',
grant_option => TRUE);
DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
privilege => DBMS_RULE_ADM.EXECUTE_ANY_RULE_SET,
grantee => 'STRMADMIN',
grant_option => TRUE);
DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
privilege => DBMS_RULE_ADM.CREATE_ANY_RULE,
grantee => 'STRMADMIN',
grant_option => TRUE);
DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
privilege => DBMS_RULE_ADM.ALTER_ANY_RULE,
grantee => 'STRMADMIN',
grant_option => TRUE);
DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
privilege => DBMS_RULE_ADM.EXECUTE_ANY_RULE,
grantee => 'STRMADMIN',
grant_option => TRUE);
END;
/

BEGIN
DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
privilege => DBMS_RULE_ADM.EXECUTE_ANY_EVALUATION_CONTEXT,
grantee => 'STRMADMIN',
grant_option => TRUE);
END;
/

Stream admin Setup on DB2

CONNECT SYS/PASSWORD@DB2 AS SYSDBA;

CREATE TABLESPACE LOGMNRTS DATAFILE 'C:/ORACLE/ORADATA/DB2/logmnrts.dbf' SIZE 25M
AUTOEXTEND ON MAXSIZE UNLIMITED
/


BEGIN
DBMS_LOGMNR_D.SET_TABLESPACE('LOGMNRTS');
END;
/

create user strmadmin identified by strmadmin
/

alter user strmadmin default tablespace LOGMNRTS
/

grant connect,resource,aq_administrator_role,dba to strmadmin
/

GRANT SELECT ANY DICTIONARY TO STRMADMIN;

GRANT EXECUTE ON DBMS_AQ TO STRMADMIN;
GRANT EXECUTE ON DBMS_AQADM TO STRMADMIN;
GRANT EXECUTE ON DBMS_FLASHBACK TO STRMADMIN;
GRANT EXECUTE ON DBMS_STREAMS_ADM TO STRMADMIN;
GRANT EXECUTE ON DBMS_CAPTURE_ADM TO STRMADMIN;
GRANT EXECUTE ON DBMS_APPLY_ADM TO STRMADMIN;
GRANT EXECUTE ON DBMS_RULE_ADM TO STRMADMIN;
GRANT EXECUTE ON DBMS_PROPAGATION_ADM TO STRMADMIN;


BEGIN
DBMS_AQADM.GRANT_SYSTEM_PRIVILEGE(
privilege => 'ENQUEUE_ANY',
grantee => 'STRMADMIN',
admin_option => FALSE);
END;
/

BEGIN
DBMS_AQADM.GRANT_SYSTEM_PRIVILEGE(
privilege => 'DEQUEUE_ANY',
grantee => 'STRMADMIN',
admin_option => FALSE);
END;
/

BEGIN
DBMS_AQADM.GRANT_SYSTEM_PRIVILEGE(
privilege => 'MANAGE_ANY',
grantee => 'STRMADMIN',
admin_option => TRUE);
END;
/

BEGIN
DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
privilege => DBMS_RULE_ADM.CREATE_EVALUATION_CONTEXT_OBJ,
grantee => 'STRMADMIN',
grant_option => TRUE);
DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
privilege => DBMS_RULE_ADM.CREATE_RULE_SET_OBJ,
grantee => 'STRMADMIN',
grant_option => TRUE);
DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
privilege => DBMS_RULE_ADM.CREATE_RULE_OBJ,
grantee => 'STRMADMIN',
grant_option => TRUE);
END;
/

BEGIN
DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
privilege => DBMS_RULE_ADM.CREATE_ANY_RULE_SET,
grantee => 'STRMADMIN',
grant_option => TRUE);
DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
privilege => DBMS_RULE_ADM.ALTER_ANY_RULE_SET,
grantee => 'STRMADMIN',
grant_option => TRUE);
DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
privilege => DBMS_RULE_ADM.EXECUTE_ANY_RULE_SET,
grantee => 'STRMADMIN',
grant_option => TRUE);
DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
privilege => DBMS_RULE_ADM.CREATE_ANY_RULE,
grantee => 'STRMADMIN',
grant_option => TRUE);
DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
privilege => DBMS_RULE_ADM.ALTER_ANY_RULE,
grantee => 'STRMADMIN',
grant_option => TRUE);
DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
privilege => DBMS_RULE_ADM.EXECUTE_ANY_RULE,
grantee => 'STRMADMIN',
grant_option => TRUE);
END;
/

BEGIN
DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
privilege => DBMS_RULE_ADM.EXECUTE_ANY_EVALUATION_CONTEXT,
grantee => 'STRMADMIN',
grant_option => TRUE);
END;
/

Stream admin Setup on DB3

CONNECT SYS/password@db3 AS SYSDBA;

CREATE TABLESPACE LOGMNRTS DATAFILE 'C:/ORACLE/ORADATA/DB3/logmnrts.dbf' SIZE 25M
AUTOEXTEND ON MAXSIZE UNLIMITED
/

BEGIN
DBMS_LOGMNR_D.SET_TABLESPACE('LOGMNRTS');
END;
/

create user strmadmin identified by strmadmin
default tablespace LOGMNRTS
/

grant connect,resource,aq_administrator_role,dba to strmadmin
/

GRANT SELECT ANY DICTIONARY TO STRMADMIN;

GRANT EXECUTE ON DBMS_AQ TO STRMADMIN;
GRANT EXECUTE ON DBMS_AQADM TO STRMADMIN;
GRANT EXECUTE ON DBMS_FLASHBACK TO STRMADMIN;
GRANT EXECUTE ON DBMS_STREAMS_ADM TO STRMADMIN;
GRANT EXECUTE ON DBMS_CAPTURE_ADM TO STRMADMIN;
GRANT EXECUTE ON DBMS_APPLY_ADM TO STRMADMIN;
GRANT EXECUTE ON DBMS_RULE_ADM TO STRMADMIN;
GRANT EXECUTE ON DBMS_PROPAGATION_ADM TO STRMADMIN;


BEGIN
DBMS_AQADM.GRANT_SYSTEM_PRIVILEGE(
privilege => 'ENQUEUE_ANY',
grantee => 'STRMADMIN',
admin_option => FALSE);
END;
/

BEGIN
DBMS_AQADM.GRANT_SYSTEM_PRIVILEGE(
privilege => 'DEQUEUE_ANY',
grantee => 'STRMADMIN',
admin_option => FALSE);
END;
/

BEGIN
DBMS_AQADM.GRANT_SYSTEM_PRIVILEGE(
privilege => 'MANAGE_ANY',
grantee => 'STRMADMIN',
admin_option => TRUE);
END;
/

BEGIN
DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
privilege => DBMS_RULE_ADM.CREATE_EVALUATION_CONTEXT_OBJ,
grantee => 'STRMADMIN',
grant_option => TRUE);
DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
privilege => DBMS_RULE_ADM.CREATE_RULE_SET_OBJ,
grantee => 'STRMADMIN',
grant_option => TRUE);
DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
privilege => DBMS_RULE_ADM.CREATE_RULE_OBJ,
grantee => 'STRMADMIN',
grant_option => TRUE);
END;
/

BEGIN
DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
privilege => DBMS_RULE_ADM.CREATE_ANY_RULE_SET,
grantee => 'STRMADMIN',
grant_option => TRUE);
DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
privilege => DBMS_RULE_ADM.ALTER_ANY_RULE_SET,
grantee => 'STRMADMIN',
grant_option => TRUE);
DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
privilege => DBMS_RULE_ADM.EXECUTE_ANY_RULE_SET,
grantee => 'STRMADMIN',
grant_option => TRUE);
DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
privilege => DBMS_RULE_ADM.CREATE_ANY_RULE,
grantee => 'STRMADMIN',
grant_option => TRUE);
DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
privilege => DBMS_RULE_ADM.ALTER_ANY_RULE,
grantee => 'STRMADMIN',
grant_option => TRUE);
DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
privilege => DBMS_RULE_ADM.EXECUTE_ANY_RULE,
grantee => 'STRMADMIN',
grant_option => TRUE);
END;
/

BEGIN
DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
privilege => DBMS_RULE_ADM.EXECUTE_ANY_EVALUATION_CONTEXT,
grantee => 'STRMADMIN',
grant_option => TRUE);
END;
/

Define and start the Apply Process in DB1
connect strmadmin/strmadmin@db1;

CREATE DATABASE LINK DB2
CONNECT TO STRMADMIN IDENTIFIED BY STRMADMIN USING
' (DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = NYC-LAPTOP05)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = db2)
)
)'
/

CREATE DATABASE LINK DB3
CONNECT TO STRMADMIN IDENTIFIED BY STRMADMIN USING
' (DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = NYC-LAPTOP05)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = db3)
)
)'
/


begin
dbms_streams_adm.set_up_queue(
queue_table => 'DB_STREAMS_QUEUE_TABLE',
storage_clause => 'TABLESPACE TOOLS',
queue_name => 'DB_STREAMS_IN_QUEUE',
queue_user => 'STRMADMIN' );
end;
/

begin
dbms_streams_adm.set_up_queue(
queue_table => 'DB_STREAMS_QUEUE_TABLE',
storage_clause => 'TABLESPACE TOOLS',
queue_name => 'DB_STREAMS_OUT_QUEUE',
queue_user => 'STRMADMIN' );
end;
/


BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_RULES(
table_name => 'SCOTT.DEPT',
streams_type => 'APPLY',
streams_name => 'DB_APPLY_DB2',
queue_name => 'STRMADMIN.DB_STREAMS_IN_QUEUE',
include_dml => true,
include_ddl => true,
source_database => 'DB2');
END;
/

BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_RULES(
table_name => 'SCOTT.DEPT',
streams_type => 'APPLY',
streams_name => 'DB_APPLY_DB3',
queue_name => 'STRMADMIN.DB_STREAMS_IN_QUEUE',
include_dml => true,
include_ddl => true,
source_database => 'DB3');
END;
/


BEGIN
dbms_apply_adm.alter_apply(
apply_name => 'DB_APPLY_DB2',
apply_user => 'SCOTT');

dbms_apply_adm.alter_apply(
apply_name => 'DB_APPLY_DB3',
apply_user => 'SCOTT');

END;
/

BEGIN
DBMS_APPLY_ADM.SET_PARAMETER(
apply_name => 'DB_APPLY_DB2',
parameter => 'DISABLE_ON_ERROR',
value => 'N' );

DBMS_APPLY_ADM.SET_PARAMETER(
apply_name => 'DB_APPLY_DB3',
parameter => 'DISABLE_ON_ERROR',
value => 'N' );

END;
/


declare
v_started number;
begin
select decode(status,'ENABLED',1,0) into v_started
from dba_apply
where apply_name = 'DB_APPLY_DB2';

if ( v_started = 0 ) then
dbms_apply_adm.start_apply( apply_name => 'DB_APPLY_DB2' );
end if;

select decode(status,'ENABLED',1,0) into v_started
from dba_apply
where apply_name = 'DB_APPLY_DB3';

if ( v_started = 0 ) then
dbms_apply_adm.start_apply( apply_name => 'DB_APPLY_DB3' );
end if;

end;
/


Define and start the Apply Process in DB2

CONNECT strmadmin/strmadmin@DB2;

CREATE DATABASE LINK DB1
CONNECT TO STRMADMIN IDENTIFIED BY STRMADMIN USING
' (DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = NYC-LAPTOP05)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = db1)
)
)'
/


CREATE DATABASE LINK DB3
CONNECT TO STRMADMIN IDENTIFIED BY STRMADMIN USING
' (DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = NYC-LAPTOP05)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = db3)
)
)'
/


begin
dbms_streams_adm.set_up_queue(
queue_table => 'DB_STREAMS_QUEUE_TABLE',
storage_clause => 'TABLESPACE TOOLS',
queue_name => 'DB_STREAMS_IN_QUEUE',
queue_user => 'STRMADMIN' );
end;
/

begin
dbms_streams_adm.set_up_queue(
queue_table => 'DB_STREAMS_QUEUE_TABLE',
storage_clause => 'TABLESPACE TOOLS',
queue_name => 'DB_STREAMS_OUT_QUEUE',
queue_user => 'STRMADMIN' );
end;
/


BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_RULES(
table_name => 'SCOTT.DEPT',
streams_type => 'APPLY',
streams_name => 'DB_APPLY_DB1',
queue_name => 'STRMADMIN.DB_STREAMS_IN_QUEUE',
include_dml => true,
include_ddl => true,
source_database => 'DB1');
END;
/

BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_RULES(
table_name => 'SCOTT.DEPT',
streams_type => 'APPLY',
streams_name => 'DB_APPLY_DB3',
queue_name => 'STRMADMIN.DB_STREAMS_IN_QUEUE',
include_dml => true,
include_ddl => true,
source_database => 'DB3');
END;
/


BEGIN
dbms_apply_adm.alter_apply(
apply_name => 'DB_APPLY_DB1',
apply_user => 'SCOTT');

dbms_apply_adm.alter_apply(
apply_name => 'DB_APPLY_DB3',
apply_user => 'SCOTT');

END;
/

BEGIN
DBMS_APPLY_ADM.SET_PARAMETER(
apply_name => 'DB_APPLY_DB1',
parameter => 'DISABLE_ON_ERROR',
value => 'N' );

DBMS_APPLY_ADM.SET_PARAMETER(
apply_name => 'DB_APPLY_DB3',
parameter => 'DISABLE_ON_ERROR',
value => 'N' );

END;
/


declare
v_started number;
begin
select decode(status,'ENABLED',1,0) into v_started
from dba_apply
where apply_name = 'DB_APPLY_DB1';

if ( v_started = 0 ) then
dbms_apply_adm.start_apply( apply_name => 'DB_APPLY_DB1' );
end if;

select decode(status,'ENABLED',1,0) into v_started
from dba_apply
where apply_name = 'DB_APPLY_DB3';

if ( v_started = 0 ) then
dbms_apply_adm.start_apply( apply_name => 'DB_APPLY_DB3' );
end if;

end;
/


Define and start the Apply Process in DB3

CONNECT strmadmin/strmadmin@DB3;

CREATE DATABASE LINK DB1
CONNECT TO STRMADMIN IDENTIFIED BY STRMADMIN USING
' (DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = NYC-LAPTOP05)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = db1)
)
)'
/


CREATE DATABASE LINK DB2
CONNECT TO STRMADMIN IDENTIFIED BY STRMADMIN USING
' (DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = NYC-LAPTOP05)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = db2)
)
)'
/


begin
dbms_streams_adm.set_up_queue(
queue_table => 'DB_STREAMS_QUEUE_TABLE',
storage_clause => 'TABLESPACE TOOLS',
queue_name => 'DB_STREAMS_IN_QUEUE',
queue_user => 'STRMADMIN' );
end;
/

begin
dbms_streams_adm.set_up_queue(
queue_table => 'DB_STREAMS_QUEUE_TABLE',
storage_clause => 'TABLESPACE TOOLS',
queue_name => 'DB_STREAMS_OUT_QUEUE',
queue_user => 'STRMADMIN' );
end;
/


BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_RULES(
table_name => 'SCOTT.DEPT',
streams_type => 'APPLY',
streams_name => 'DB_APPLY_DB1',
queue_name => 'STRMADMIN.DB_STREAMS_IN_QUEUE',
include_dml => true,
include_ddl => true,
source_database => 'DB1');
END;
/

BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_RULES(
table_name => 'SCOTT.DEPT',
streams_type => 'APPLY',
streams_name => 'DB_APPLY_DB2',
queue_name => 'STRMADMIN.DB_STREAMS_IN_QUEUE',
include_dml => true,
include_ddl => true,
source_database => 'DB2');
END;
/



BEGIN
dbms_apply_adm.alter_apply(
apply_name => 'DB_APPLY_DB1',
apply_user => 'SCOTT');

dbms_apply_adm.alter_apply(
apply_name => 'DB_APPLY_DB2',
apply_user => 'SCOTT');

END;
/

BEGIN
DBMS_APPLY_ADM.SET_PARAMETER(
apply_name => 'DB_APPLY_DB1',
parameter => 'DISABLE_ON_ERROR',
value => 'N' );

DBMS_APPLY_ADM.SET_PARAMETER(
apply_name => 'DB_APPLY_DB2',
parameter => 'DISABLE_ON_ERROR',
value => 'N' );

END;
/



declare
v_started number;
begin
select decode(status,'ENABLED',1,0) into v_started
from dba_apply
where apply_name = 'DB_APPLY_DB1';

if ( v_started = 0 ) then
dbms_apply_adm.start_apply( apply_name => 'DB_APPLY_DB1' );
end if;

select decode(status,'ENABLED',1,0) into v_started
from dba_apply
where apply_name = 'DB_APPLY_DB2';

if ( v_started = 0 ) then
dbms_apply_adm.start_apply( apply_name => 'DB_APPLY_DB2' );
end if;

end;
/

Setup Propagation Rule for DB1


CONNECT SYS/PASSWORD@DB1 AS SYSDBA
ALTER TABLE SCOTT.DEPT DROP SUPPLEMENTAL LOG GROUP log_group_dept_pk;

ALTER TABLE SCOTT.DEPT ADD SUPPLEMENTAL LOG GROUP log_group_dept_pk (DEPTNO,DNAME,LOC) ALWAYS;

CONNECT STRMADMIN/STRMADMIN@DB1

BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_RULES(
table_name => 'SCOTT.DEPT',
streams_type => 'CAPTURE',
streams_name => 'DB_CAPTURE',
queue_name => 'STRMADMIN.DB_STREAMS_OUT_QUEUE',
include_dml => true,
include_ddl => true,
source_database => 'DB1');
END;
/


BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_PROPAGATION_RULES(
table_name => 'SCOTT.DEPT',
streams_name => 'DB_PROPAGATE_DB2',
source_queue_name => 'STRMADMIN.DB_STREAMS_OUT_QUEUE',
destination_queue_name => 'STRMADMIN.DB_STREAMS_IN_QUEUE@DB2',
include_dml => true,
include_ddl => true,
source_database => 'DB1');
END;
/

BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_PROPAGATION_RULES(
table_name => 'SCOTT.DEPT',
streams_name => 'DB_PROPAGATE_DB3',
source_queue_name => 'STRMADMIN.DB_STREAMS_OUT_QUEUE',
destination_queue_name => 'STRMADMIN.DB_STREAMS_IN_QUEUE@DB3',
include_dml => true,
include_ddl => true,
source_database => 'DB1');
END;
/

Setup Propagation Rule for DB2


CONNECT SYS/PASSWORD@DB2 AS SYSDBA

ALTER TABLE SCOTT.DEPT DROP SUPPLEMENTAL LOG GROUP log_group_dept_pk;

ALTER TABLE SCOTT.DEPT ADD SUPPLEMENTAL LOG GROUP log_group_dept_pk (DEPTNO,DNAME,LOC) ALWAYS;


CONNECT STRMADMIN/STRMADMIN@DB2

BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_RULES(
table_name => 'SCOTT.DEPT',
streams_type => 'CAPTURE',
streams_name => 'DB_CAPTURE',
queue_name => 'STRMADMIN.DB_STREAMS_OUT_QUEUE',
include_dml => true,
include_ddl => true,
source_database => 'DB2');
END;
/


BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_PROPAGATION_RULES(
table_name => 'SCOTT.DEPT',
streams_name => 'DB_PROPAGATE_DB1',
source_queue_name => 'STRMADMIN.DB_STREAMS_OUT_QUEUE',
destination_queue_name => 'STRMADMIN.DB_STREAMS_IN_QUEUE@DB1',
include_dml => true,
include_ddl => true,
source_database => 'DB2');
END;
/

BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_PROPAGATION_RULES(
table_name => 'SCOTT.DEPT',
streams_name => 'DB_PROPAGATE_DB3',
source_queue_name => 'STRMADMIN.DB_STREAMS_OUT_QUEUE',
destination_queue_name => 'STRMADMIN.DB_STREAMS_IN_QUEUE@DB3',
include_dml => true,
include_ddl => true,
source_database => 'DB2');
END;
/

Setup Propagation Rule for DB3

CONNECT SYS/PASSWORD@DB3 AS SYSDBA

ALTER TABLE SCOTT.DEPT DROP SUPPLEMENTAL LOG GROUP log_group_dept_pk;
ALTER TABLE SCOTT.DEPT ADD SUPPLEMENTAL LOG GROUP log_group_dept_pk (DEPTNO,DNAME,LOC) ALWAYS;


CONNECT STRMADMIN/STRMADMIN@DB3

BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_RULES(
table_name => 'SCOTT.DEPT',
streams_type => 'CAPTURE',
streams_name => 'DB_CAPTURE',
queue_name => 'STRMADMIN.DB_STREAMS_OUT_QUEUE',
include_dml => true,
include_ddl => true,
source_database => 'DB3');
END;
/


BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_PROPAGATION_RULES(
table_name => 'SCOTT.DEPT',
streams_name => 'DB_PROPAGATE_DB1',
source_queue_name => 'STRMADMIN.DB_STREAMS_OUT_QUEUE',
destination_queue_name => 'STRMADMIN.DB_STREAMS_IN_QUEUE@DB1',
include_dml => true,
include_ddl => true,
source_database => 'DB3');
END;
/

BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_PROPAGATION_RULES(
table_name => 'SCOTT.DEPT',
streams_name => 'DB_PROPAGATE_DB2',
source_queue_name => 'STRMADMIN.DB_STREAMS_OUT_QUEUE',
destination_queue_name => 'STRMADMIN.DB_STREAMS_IN_QUEUE@DB2',
include_dml => true,
include_ddl => true,
source_database => 'DB3');
END;
/
Setup Instantiation SCN for DB1

CONNECT STRMADMIN/STRMADMIN@DB1

set echo on
set feedback on
set serverout on

declare
iscn number;
begin
iscn := DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER();
DBMS_OUTPUT.PUT_LINE('Instantiation SCN is: 'iscn );
end;
/

CONNECT STRMADMIN/STRMADMIN@DB2

BEGIN
DBMS_APPLY_ADM.SET_TABLE_INSTANTIATION_SCN(
source_object_name => 'SCOTT.DEPT',
source_database_name => 'DB1',
instantiation_scn => iscn);
END;
/

CONNECT STRMADMIN/STRMADMIN@DB3

BEGIN
DBMS_APPLY_ADM.SET_TABLE_INSTANTIATION_SCN(
source_object_name => 'SCOTT.DEPT',
source_database_name => 'DB1',
instantiation_scn => iscn);
END;
/

CONNECT STRMADMIN/STRMADMIN@DB1

EXEC DBMS_CAPTURE_ADM.START_CAPTURE( CAPTURE_NAME => 'DB_CAPTURE')
/
Setup Instantiation SCN for DB2

CONNECT STRMADMIN/STRMADMIN@DB2

set echo on
set feedback on
set serverout on

declare
iscn number;
begin
iscn := DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER();
DBMS_OUTPUT.PUT_LINE('Instantiation SCN is: 'iscn );
end;
/

CONNECT STRMADMIN/STRMADMIN@DB1

BEGIN
DBMS_APPLY_ADM.SET_TABLE_INSTANTIATION_SCN(
source_object_name => 'SCOTT.DEPT',
source_database_name => 'DB2',
instantiation_scn => vscn);
END;
/

CONNECT STRMADMIN/STRMADMIN@DB3

BEGIN
DBMS_APPLY_ADM.SET_TABLE_INSTANTIATION_SCN(
source_object_name => 'SCOTT.DEPT',
source_database_name => 'DB2',
instantiation_scn => vscn);
END;
/

CONNECT STRMADMIN/STRMADMIN@DB2

EXEC DBMS_CAPTURE_ADM.START_CAPTURE( CAPTURE_NAME => 'DB_CAPTURE')
/
Setup Instantiation SCN for DB3

CONNECT STRMADMIN/STRMADMIN@DB3

set echo on
set feedback on
set serverout on

declare
iscn number;
begin
iscn :=
DBMS_OUTPUT.PUT_LINE('Instantiation SCN is: 'iscn );
end;
/

CONNECT STRMADMIN/STRMADMIN@DB1

BEGIN
DBMS_APPLY_ADM.SET_TABLE_INSTANTIATION_SCN(
source_object_name => 'SCOTT.DEPT',
source_database_name => 'DB3',
instantiation_scn => vscn);
END;
/

CONNECT STRMADMIN/STRMADMIN@DB2

BEGIN
DBMS_APPLY_ADM.SET_TABLE_INSTANTIATION_SCN(
source_object_name => 'SCOTT.DEPT',
source_database_name => 'DB3',
instantiation_scn => vscn);
END;
/

CONNECT STRMADMIN/STRMADMIN@DB3

EXEC DBMS_CAPTURE_ADM.START_CAPTURE( CAPTURE_NAME => 'DB_CAPTURE')
/
Stream Testing

Scenario 1

Here is the records in dept table for DB1 instance
scott@DB1.US.ORACLE.COM> select * from dept;

DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON

scott@DB1.US.ORACLE.COM>

Now insert one record in DB1 instance and check this record is replicated in DB2, DB3 instance.

scott@DB1.US.ORACLE.COM> insert into
2 dept values(50,'IT','HOUSTON');

1 row created.

scott@DB1.US.ORACLE.COM> COMMIT;

Commit complete.

scott@DB1.US.ORACLE.COM>

scott@DB2.US.ORACLE.COM> SELECT * FROM DEPT;

DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
50 IT HOUSTON

scott@DB2.US.ORACLE.COM>

scott@DB3.US.ORACLE.COM> SELECT * FROM DEPT;

DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
50 IT HOUSTON

scott@DB3.US.ORACLE.COM
>

Now the changes in DB1 is replicated to DB2, DB3.

Scenario 2

Let us update the record in DB2 and see the changes are replicating to DB1, DB3.

scott@DB2.US.ORACLE.COM> UPDATE dept
2 set dname='Testing'
3 where deptno=50;

1 row updated.

scott@DB2.US.ORACLE.COM> commit;

Commit complete.

scott@DB2.US.ORACLE.COM>

scott@DB1.US.ORACLE.COM> SELECT * FROM DEPT;

DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
50 Testing HOUSTON

scott@DB1.US.ORACLE.COM>

scott@DB3.US.ORACLE.COM> SELECT * FROM DEPT;

DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
50 Testing HOUSTON

scott@DB3.US.ORACLE.COM>

Now the changes in DB2 is replicated to DB1, DB3.

Scenario 3

Now delete one record in DB3 instance and check this record is replicated in DB1, DB2 instance.

scott@DB3.US.ORACLE.COM> delete dept
2 where deptno=50;

1 row deleted.

scott@DB3.US.ORACLE.COM> commit;

Commit complete.

scott@DB3.US.ORACLE.COM>
scott@DB1.US.ORACLE.COM> select * from dept;

DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON

scott@DB1.US.ORACLE.COM>
scott@DB2.US.ORACLE.COM> select * from dept;

DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON

scott@DB2.US.ORACLE.COM>

Optimizer Mode - FIRST_ROWS Vs ALL_ROWS

What circumstances we use ALL_ROWS and what circumstances we use FIRST_ROWS optimizer mode? This article is written in oracle9i.

First_rows attempts to optimize the query to get the very first row back to the client as fast as possible. This is good for an interactive client server environment where the client runs a query and shows the user the first 10 rows or so and waits for them to page down to get more.

All_rows attempts to optimize the query to get the very last row as fast as possible. This makes sense in a stored procedure for example where the client does not regain control until the stored procedure completes. You don't care if you have to wait to get the first row if the last row gets back to you twice as fast. In a client server/interactive application you may well care about that.

In TOAD or SQL Navigator, When we select the data, it display immediately. But it does not mean that, it is faster. If we scroll down, it might be fetching the data in the background mode. First_rows is best place for OLTP environment. Also in some reporting environment, if user wants to see initial data first and later see the rest of the data, then first_rows is good option. When we run the query in the stored procedure, first_rows would not be a good choice, all_rows is good option here, because, there is no use to fetch the first few records immediatley inside the stored procedure.

Let us demonstrate the FIRST_ROWS/ALL_ROWS optimizer hint.

Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 - Production

SQL> create table testtable as select * from user_objects;

Table created.

SQL> create index idx on testtable(object_type);

Index created.

SQL> EXECUTE DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => 'SCREPORT',TABNAME => 'TESTTABLE',ESTIMATE_PER
CENT => 10, METHOD_OPT => 'FOR ALL COLUMNS SIZE 1', CASCADE => TRUE);

PL/SQL procedure successfully completed.

SQL> select count(*) from testtable;

COUNT(*)
----------
5619712

SQL> select count(*) from testtable where object_type='TABLE';

COUNT(*)
----------
2392064

SQL> set autotrace traceonly exp;

SQL> select /*+ all_rows */ * from testtable where object_type='TABLE';

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=HINT: ALL_ROWS (Cost=4316 Card=62
3914 Bytes=53032690)

1 0 TABLE ACCESS (FULL) OF 'TESTTABLE' (Cost=4316 Card=623914
Bytes=53032690)

In TESTTABLE table, we have around 5 million records, the above query returns half of the records. Optimizer use full table scan when we use all_rows hint. Because, it needs to read all the rows before it display the data in the screen. The cost for the all_rows is 4316.


SQL> select /*+ first_rows */ * FROM TESTTABLE where object_type='TABLE';

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=HINT: FIRST_ROWS (Cost=51502 Card
=623914 Bytes=53032690)

1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TESTTABLE' (Cost=51502 C
ard=623914 Bytes=53032690)

2 1 INDEX (RANGE SCAN) OF 'IDX' (NON-UNIQUE) (Cost=1604 Card
=623914)

Optimizer use the index scan when we use the first_rows hint. Because, it use the index scan and reads first few rows to display in the screen, then it reads the rest of the data. The cost is 51502.
so first_rows hint looks faster, but it is really not. In this example, the cost is 12 times more in index scan(first_rows) when we compared to All_rows(full table scan).

When do we use FIRST_ROWS?

To answer this question, we can use first_rows when user want to see the first few rows immediately. It is mostly used in OLTP, some reporting environment.

When do we use ALL_ROWS?

To answer this question, we can use all_rows when user want to process all the rows before we see the output.. Mostly used in OLAP. All_rows use less resource when compared to first_rows.

Important factor in FIRST_ROWS

1. It prefer to use the index scan
2. It prefer to use nested loop join over hash joins. Because, nested loop joins data as selected. but hash join hashes the data in hash table which takes time.
3. Good for OLTP

Important factor in ALL_ROWS

1. It use both index scan & full table scan depends on how many blocks optimizer is reading in the table.
2. Good for OLAP
3. It most likly to use hash join, again depends upon other factors.

Saturday, September 13, 2008

Nested Loop, Hash Join, Sort Merge Join, Cartesian join difference

This article is written in oracle 9.2.0.8.

Nested loop Joins The optimizer uses nested loop joins when joining small number of rows, with a good driving condition between the two tables. It drives from the outer loop to the inner loop. The inner loop is iterated for every row returned from the outer loop, ideally by an index scan. It is inefficient when join returns large number of rows (typically, more than 10,000 rows is considered large), and the optimizer might choose not to use it.

The cost is calculated as below.
cost = access cost of A + (access cost of B * no_of_rows from A)

A nested loop join involves the following steps:

1. The optimizer determines the driving table and designates it as the outer table.
2. The other table is designated as the inner table.
3. For every row in the outer table, Oracle accesses all the rows in the inner table.

For instance,
scott@DB1.US.ORACLE.COM> SELECT emp.empno, dept.dname
2 FROM EMP , DEPT
3 WHERE dept.deptno = 10
4 AND emp.deptno = dept.deptno
5 /

EMPNO DNAME
---------- --------------
7782 ACCOUNTING
7839 ACCOUNTING
7934 ACCOUNTING

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=5 Bytes=85)
1 0 NESTED LOOPS (Cost=3 Card=5 Bytes=85)
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'DEPT' (Cost=1 Card=1 Bytes=10)
3 2 INDEX (UNIQUE SCAN) OF 'PK_DEPT' (UNIQUE)
4 1 TABLE ACCESS (FULL) OF 'EMP' (Cost=2 Card=5 Bytes=35)

We can also force the Nested loop join hint as below.

SELECT /*+ USE_NL(emp dept) */ emp.empno, dept.dname
FROM EMP , DEPT WHERE dept.deptno = 10
AND emp.deptno = dept.deptno

Hash Join Hash joins are used for joining large data sets. The optimizer uses the smaller of two tables or data sources to build a hash table on the join key in memory. It then scans the larger table, probing the hash table to find the joined rows. This method is best used when the smaller table fits in available memory. The optimizer uses a hash join to join two tables if they are joined using an equijoin(joins with equals predicates) and large amount of data needs to be joined.

The cost of a Hash loop join is calculated by the following formula:
cost=(access cost of A*no_of_hash partitions of B) + access cost of B

For instance,
scott@DB1.US.ORACLE.COM> ;
1 SELECT emp.empno, dept.dname
2 FROM EMP , DEPT
3* WHERE emp.deptno = dept.deptno
scott@DB1.US.ORACLE.COM>
/
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=5 Card=14 Bytes=238)
1 0 HASH JOIN (Cost=5 Card=14 Bytes=238)
2 1 TABLE ACCESS (FULL) OF 'DEPT' (Cost=2 Card=7 Bytes=70)
3 1 TABLE ACCESS (FULL) OF 'EMP' (Cost=2 Card=14 Bytes=98)

We can also force the Hash join hint as below.
SELECT /*+USE_HASH(emp dept) */ emp.empno, dept.dname
FROM EMP , DEPT
WHERE emp.deptno = dept.deptno

Sort-Merge Join Sort merge joins can be used to join rows from two independent sources. Hash joins generally perform better than sort merge joins. On the other hand, sort merge joins can perform better than hash joins if both of the following conditions exist:

1. The row sources are sorted already.
2. A sort operation does not have to be done.

Sort merge joins are almost exclusively used for non-equi joins (>, <, BETWEEN). Sort merge joins perform better than nested loop joins for large data sets. (You cannot use hash joins unless there is an equality condition). In a merge join, there is no concept of a driving table.
The join consists of two steps:
Sort join operation: Both the inputs are sorted on the join key.
Merge join operation: The sorted lists are merged together.

The optimizer can choose a sort merge join over a hash join for joining large amounts of data if any of the following conditions are true:

1. The join condition between two tables is not an equi-join.
2. OPTIMIZER_MODE is set to RULE.
3. HASH_JOIN_ENABLED is false.
4. Because of sorts already required by other operations, the optimizer finds it is cheaper to use a sort merge than a hash join.
5. The optimizer thinks that the cost of a hash join is higher, based on the settings of HASH_AREA_SIZE and SORT_AREA_SIZE.

The cost of a sort merge join is calculated by the following formula:
cost = access cost of A + access cost of B + (sort cost of A + sort cost of B)

For instance, scott@DB1.US.ORACLE.COM> SELECT emp.empno, dept.dname
2 FROM EMP , DEPT
3 WHERE emp.deptno < dept.deptno

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=6 Card=5 Bytes=85)
1 0 MERGE JOIN (Cost=6 Card=5 Bytes=85)
2 1 SORT (JOIN) (Cost=2 Card=7 Bytes=70)
3 2 TABLE ACCESS (BY INDEX ROWID) OF 'DEPT' (Cost=2 Card=7 Bytes=70)
4 3 INDEX (FULL SCAN) OF 'PK_DEPT' (UNIQUE) (Cost=1 Card =7)
5 1 SORT (JOIN) (Cost=4 Card=14 Bytes=98)
6 5 TABLE ACCESS (FULL) OF 'EMP' (Cost=2 Card=14 Bytes=98)

We can also force the Hash join hint as below.

scott@DB1.US.ORACLE.COM> SELECT /*+USE_MERGE(emp dept) */
emp.empno, dept.dname
2 FROM EMP , DEPT
3 WHERE emp.deptno < dept.deptno

Cartesian join A Cartesian join is used when one or more of the tables does not have any join conditions to any other tables in the statement. The optimizer joins every row from one data source with all the row from the other data source, creating the Cartesian product of the two sets.

For instance,
scott@DB1.US.ORACLE.COM> select * from emp,dept;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=16 Card=98 Bytes=5194)
1 0 MERGE JOIN (CARTESIAN) (Cost=16 Card=98 Bytes=5194)
2 1 TABLE ACCESS (FULL) OF 'DEPT' (Cost=2 Card=7 Bytes=112)
3 1 BUFFER (SORT) (Cost=14 Card=14 Bytes=518)
4 3 TABLE ACCESS (FULL) OF 'EMP' (Cost=2 Card=14 Bytes=518)

Friday, September 12, 2008

Shutdown startup procedure for Standby database

This article is written  and tested in oracle 9.2.0.8. Please remember, Oracle11g has Active Data Guard and it will be different steps. Please do not follow this topic for Oracle 11g and above version!

Please refer Click for Oracle11g standby database bounce.

Startup and shutdown the standby database is different then the Primary database. If we want to shutdown the primary and standby database both, it would be better to shutdown primary DB first, then shutdown the standby DB. If we shutdown the PRIMARY DB first, then all the changes will be shipped to standby.
Shutdown procedure for Primary/standby
Step1 : Tail the alert log for primary and standby.
Login to primary and standby server and tail the alert log as below.
tail -f  alert_$ORACLE_SID.log

Step2 : Make sure application is down and no activity on the database.

Step3 :  To be safer side, switch the log file few times and flush out the redo logs. Then shutdown the primary database.
alter system archive log current;
alter system archive log current;
alter system archive log current;
shutdown immediate;
Just make sure all the archive logs are shipped to standby.

Step4 : Once the primary DB shutdown is successful, then steps to shutdown standby DB.
alter database recover managed standby database cancel;
shutdown immediate

Startup procedure for Primary/standby
Step1 :  Start the primary database
startup
Application team can start the application against the primary database.
Enable the log shipping in primary database.
alter system set log_archive_dest_state_2='ENABLE' scope=both;
At this point, you might see some error message on alert log. since primary is trying to connect standby and still standby is not yet up and running.

Step2 : Start the standby database
startup nomount
alter database mount standby database;
alter database recover managed standby database disconnect from session

Watch out the alert log and make sure log shipping is working fine and no error on the alert log. Verify the v$archived_log and make sure archive logs are applying on standby database.
Please remember, the above steps works till Oracle10g. Starting from Oracle11g, Oracle introduced Active Data Guard and it will be different steps to bounce the standby data base. Please click here to view the steps to bounce the Oracle11g Active Data Guard.