Saturday, 25 April 2020

Impdp fails with error ORA-31604

Issue:
When tried to Import a table from one Oracle database to another using IMPDP utility,encountered the error ORA-31604: invalid transform NAME parameter "MODIFY" for object type PROCACT_INSTANCE in function ADD_TRANSFORM

Impdp logfile has the below error:

Starting "<LOGIN_SCHEMA>"."SYS_IMPORT_FULL_10":  <LOGIN>/******** parfile=<PARFILE_NAME>.par logfile=<LOG_NAME>.log dumpfile=<DUMPFILE_NAME>.dmp parallel=1
Processing object type TABLE_EXPORT/TABLE/PROCACT_INSTANCE
ORA-39126: Worker unexpected fatal error in KUPW$WORKER.LOAD_MD_TRANSFORMS []
ORA-31604: invalid transform NAME parameter "MODIFY" for object type PROCACT_INSTANCE in function ADD_TRANSFORM
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
ORA-06512: at "SYS.KUPW$WORKER", line 8996


Cause: This is a bug in Oracle Database versio 11g, fixed in Oracle Database version 12c

Solution:

As a workaround, implement any of the below solutions:

1. Use an additional parameter that is exclude=PROCACT_INSTANCE during impdp

2. Redo the export with exclude=PROCACT_INSTANCE and perform import using new dumpfiles.


The use of the exclude=PROCACT_SYSTEM will exclude the resource manager objects such as resource plans and groups. We need to re-create resource plans and groups after impdp is successfully completed.


How to check if MRP Process is Running

Below Query is Used to verify if MRP process is running fine on Standby Database, after any maintenance activity that requires disconnect to Primary and Standby database.



SQL> SELECT CLIENT_PROCESS, PROCESS, THREAD#, SEQUENCE#, STATUS FROM V$MANAGED_STANDBY;


Below is the sample output from the query:

SQL> SELECT CLIENT_PROCESS, PROCESS, THREAD#, SEQUENCE#, STATUS FROM V$MANAGED_STANDBY;

CLIENT_P PROCESS      THREAD#  SEQUENCE# STATUS
-------- --------- ---------- ---------- ------------
ARCH     ARCH               2     101072 CLOSING
ARCH     ARCH               0          0 CONNECTED
ARCH     ARCH               1     298101 CLOSING
ARCH     ARCH               2     103073 CLOSING
ARCH     ARCH               3      95398 CLOSING
ARCH     ARCH               1     296100 CLOSING
ARCH     ARCH               3      95399 CLOSING
ARCH     ARCH               3      95400 CLOSING
ARCH     RFS                0          0 IDLE
ARCH     RFS                0          0 IDLE
ARCH     RFS                0          0 IDLE
LGWR     RFS                2     101074 IDLE
LGWR     RFS                1     296102 RECEIVING
UNKNOWN  RFS                0          0 IDLE
UNKNOWN  RFS                0          0 IDLE
LGWR     RFS                3      95401 IDLE
UNKNOWN  RFS                0          0 IDLE


17 rows selected.

Convert Physical database into a Snapshot Standby Database

What are the benefits of Snapshot standby database?

Snapshot Standby Database is introduced from Oracle 11gR1. A Snapshot Standby database is a physical standby that is temporarily disconnected from Data Guard configuration, and started in READ-WRITE mode.It functions as a updateable Stand-alone Database, which is an exact Replica of Primary Database which is normally used for testing purpose. We can revert the Snapshot standby database to Physical Standby database after the testing is completed.


Steps to convert Physical database into a Snapshot Standby Database:

1. First step to convert to Snapshot standby database is to enable FLASHBACK feature on the Physical Standby, if not already enabled.
Ensure there is sufficient space available in Flash Recovery Area (FRA) for Flashback Logs.

2. Stop redo apply on the physical standby database:

SQL>alter database recover managed standby database cancel;

3. Turn on flashback logging:

SQL>alter database flashback on;

4. Convert the standby database into a snapshot standby database:

( For RAC Configurations: Shutdown all other instances except one, which is used as Snapshot standby database)

SQL>alter database convert to snapshot standby;


5.Shut down the snapshot standby database and startup the database (it will be opened for read/write access):

SQL>shutdown immediate;
SQL>startup


After the Snapshot mode is enabled on the database, perform the required testing and revert back to Physical Standby mode after the testing is complete.



Steps to Revert the Physical Standby Database Back to its Original State


1. Change the standby database back into its standby mode (from snapshot mode) as follows:

SQL>startup mount force;
SQL>alter database convert to physical standby;
SQL>shutdown immediate;
SQL>startup nomount;
SQL>alter database mount standby database;


2. Enable redo log apply.

SQL>alter database recover managed standby database using current logfile disconnect;

3.On the primary database, issue the following statement to re-enable archiving to the physical standby database:

SQL>alter system set log_archive_dest_state_2=enable scope=both;