Saturday, March 10, 2012

Effect of Creating Tablespace/Datafile on Primary Database when Logical Standby Database in Place


Primary Database:
Create a tablespace as below.

dbPRD>create tablespace tbstest2
              datafile '/oradata1/dbprd/tbstestt201.dbf' size 1m
              autoextend on next 1m maxsize unlimited;
Tablespace created.

dbPRD>select name from v$tablespace where name ='TBSTEST2';

NAME
-------------
TBSTEST2

Logical Standby Database :

Check whether the tablespace has replicated from primary site to logical standby site.

dbREP-LOGICAL>select name from v$database where name='TBSTEST2';
no rows selected

From the above result we can understand that, the tablespace created in primary site did not replicate to logical standby.

The following steps has to be followed to replicate the tablespace to logical standby site.

Stop the Logical Standby Database apply process :
 
dbREP-LOGICAL>alter database stop logical standby apply;
Database altered.

Execute the below procedure.

dbREP-LOGICAL>CREATE OR REPLACE PROCEDURE SYS.CREATE_TBS_DDL (
OLD_STMT IN VARCHAR2,
STMT_TYP  IN VARCHAR2,
SCHEMA    IN VARCHAR2,
NAME         IN VARCHAR2,
XIDUSN     IN NUMBER,
XIDSLT      IN NUMBER,
XIDSQN   IN NUMBER,
ACTION   OUT NUMBER,
NEW_STMT OUT VARCHAR2
)
AS
BEGIN
NEW_STMT := REPLACE(OLD_STMT,'/oradata1/dbprd','/oradata2/dbrep');
ACTION := DBMS_LOGSTDBY.SKIP_ACTION_REPLACE;
EXCEPTION
WHEN OTHERS THEN
ACTION := DBMS_LOGSTDBY.SKIP_ACTION_ERROR;
NEW_STMT := NULL;
END CREATE_TBS_DDL;
/

Procedure created.

After creating the procedure, Execute the below dbms statement.

dbREP-LOGICAL>EXECUTE DBMS_LOGSTDBY.SKIP
(stmt => 'TABLESPACE',
 proc_name => 'sys.CREATE_TBS_DDL');

PL/SQL procedure successfully completed.

Start the Logical Standby Database apply process :

dbREP-LOGICAL>ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;
Database altered.

Check whether the tablespace is replicated in logical standby site.

dbREP-LOGICAL>select name from v$tablespace where name='TBSTEST2';

NAME
-------------
TBSTEST2

1 comment:

  1. Thanks and Regards. Oracle Apps R12 Training Videos at affordable cost. please check oracleappstechnical.com

    ReplyDelete