trace of db_directories after system copy

[KMK]

Hello All,

We did a system copy last week (PRD to TST). In TST, in one of st04 functions we receive the following errors:

ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error

KUP-04040: file alert_PRD.log in EXT_DIR_DBAC_ALERT not found

After checking db_directories, we found some traces of PRD in TST (see below query). The mentioned directories don't exist anymore in TST. Do you have any idea how I can do the clean-up?

SQL> select * from dba_directories;

OWNER                          DIRECTORY_NAME

------------------------------ ------------------------------

DIRECTORY_PATH

--------------------------------------------------------------------------------

SYS                            DATA_PUMP_DIR

/oracle/admin/PRD/dpdump/

SYS                            EXT_DIR_DBAC_CLIENT

/usr/sap/PRD/SYS/profile/oracle

SYS                            ORACLE_OCM_CONFIG_DIR

/oracle/PRD/112_64/ccr/state

OWNER                          DIRECTORY_NAME

------------------------------ ------------------------------

DIRECTORY_PATH

--------------------------------------------------------------------------------

SYS                            EXT_DIR_DBAC_ALERT

/oracle/PRD/saptrace/diag/rdbms/prd/PRD/trace

SYS                            EXT_DIR_DBAC_SERVER

/oracle/PRD/112_64/network/admin

Thank you,

Kamand

[Former Member · Accepted solution]

Change LOCATION and possibly DEFAULT_DIRECTORY for external tables to reflect TST system not PRD. Please read Managing External Tables for more info.

[KMK · Accepted solution]

how can I find out which tables should be changed?

[Former Member · Accepted solution]

In addition to dba_directories please check also dba_external_tables, dba_external_locations.

[KMK · Accepted solution]

SQL> select * from dba_external_locations;

SYS                            EXT_TAB_DBAC_TNSNAMES

tnsnames.ora

SYS EXT_DIR_DBAC_CLIENT

SYS                            EXT_TAB_DBAC_SQLNET

sqlnet.ora

SYS EXT_DIR_DBAC_CLIENT

SYS                            EXT_TAB_DBAC_LISTENER

listener.ora

SYS EXT_DIR_DBAC_SERVER

SYS                            EXT_TAB_DBAC_ALERT

alert_PRD.log

SYS EXT_DIR_DBAC_ALERT

AND

SQL> select * from dba_external_tables;

SYS                            EXT_TAB_DBAC_TNSNAMES          SYS

ORACLE_LOADER                  SYS EXT_DIR_DBAC_CLIENT

0                                        CLOB

RECORDS DELIMITED BY NEWLINE

      NOLOGFILE

ALL

SYS                            EXT_TAB_DBAC_SQLNET            SYS

ORACLE_LOADER                  SYS EXT_DIR_DBAC_CLIENT

0                                        CLOB

RECORDS DELIMITED BY NEWLINE

      NOLOGFILE

ALL

SYS                            EXT_TAB_DBAC_LISTENER          SYS

ORACLE_LOADER                  SYS EXT_DIR_DBAC_SERVER

0                                        CLOB

RECORDS DELIMITED BY NEWLINE

      NOLOGFILE

ALL

SYS                            EXT_TAB_DBAC_ALERT             SYS

ORACLE_LOADER                  SYS EXT_DIR_DBAC_ALERT

0                                        CLOB

RECORDS DELIMITED BY NEWLINE

      NOLOGFILE

ALL

[Former Member · Accepted solution]

You need to recreate external tables with correct locations and directories. According to official oracle documentation it's impossible with ALTER TABLE clause. I'm not so familiar with external tables may be exists another more simpler solution.

[KMK · Accepted solution]

Thank you for your help, I'll post a ticket on sap portal.

[KMK · Accepted solution]

I should have applied SAP note 1546456 to recreate dba_directories in the new system.

Thank you all for your help,

Kamand