Effect of Creating Tablespace / Datafile on Primary when Logical Standby in Place (ORA-01119)

Effect of Creating Tablespace / Datafile on Primary when Logical Standby in Place (ORA-01119)

SYMPTOMS

Dataguard environment with Logical standby. 

When we add a new datafile on primary and both primary and standby are in different file name directory, SQL APPLY from production database to Logical Standby database will fail with ORA-01119.

CAUSE

1. If databse is OMF on both primary and standby then no issues in creating datafile at logical standby side.

2. If no OMF then Apply terminate with (ORA-1119) below errors, Because logcal standby will not honour the db_file_name_convert. 

SOLUTION

• Use OMF as a proactive measure.

• If not run skip handler.

WORKOUT

On Production

• create a tablespace as below,

PRODDB>create tablespace tbstest2 datafile ‘/oradata1/PRODDB/tbstestt201.dbf’ size 1m autoextend on next 1m maxsize unlimited;

Tablespace created.

PRODDB>select name from v$tablespace where name =’TBSTEST2′;

NAME

——————————

TBSTEST2

On Logical Standby database

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

LOGSTDB-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 apply process.

LOGSTDB-LOGICAL>alter database stop logical standby apply;

Database altered.

• Execute the below procedure.

LOGSTDB-LOGICAL>CREATE OR REPLACE PROCEDURE SYS.HANDLE_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/PRODDB’,’/oradata2/LOGSTDB’);

ACTION := DBMS_LOGSTDBY.SKIP_ACTION_REPLACE;

EXCEPTION

WHEN OTHERS THEN

ACTION := DBMS_LOGSTDBY.SKIP_ACTION_ERROR;

NEW_STMT := NULL;

END HANDLE_TBS_DDL;

/  2    3    4    5    6    7    8    9   10   11   12   13   14   15   16   17   18   19   20   21   22   23   24   25   26   27   28   29   30   31   32   33   34   35   36   37   38   39  

Procedure created.

• After creating the procedure, Execute the below dbms statement.

LOGSTDB-LOGICAL>EXECUTE DBMS_LOGSTDBY.SKIP (stmt => ‘TABLESPACE’,proc_name => ‘sys.handle_tbs_ddl’);

PL/SQL procedure successfully completed.

• Start the logical standby apply process.

LOGSTDB-LOGICAL>ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;

Database altered.

• Check whether the tablespace is replicated in logical standby site.

LOGSTDB-LOGICAL>select name from v$tablespace where name=’TBSTEST2′;

NAME

——————————

TBSTEST2