Exporting Data from a Physical Standby Database Using Data Pump

A physical standby database is typically in READ-ONLY WITH APPLY mode (Active Data Guard). Since Oracle Data Pump (expdp) requires a read-write database, you cannot run expdp directly on a physical standby database.

Solution

To export data from a physical standby database, we use the NETWORK_LINK parameter to run the expdp job from a different open (primary or non-standby) database.

Step-by-Step Implementation

Step 1: Verify that the Standby Database is in Read-Only Mode

Run the following SQL command on the standby database:


SQL> SELECT database_name, database_role, open_mode FROM v$database;

Example Output:


DATABASE_NAME         DATABASE_ROLE            OPEN_MODE
-------------------   ----------------------   -----------------------
PROD2025             PHYSICAL STANDBY          READ ONLY WITH APPLY

The database role should be PHYSICAL STANDBY.
The open mode should be READ ONLY WITH APPLY.

Step 2: Create a Database Link on the Primary or Another Open Database

Since we cannot run expdp directly on the standby database, we create a database link to connect from an open database.

Run this command on the primary (or non-standby) database:


SQL> CREATE PUBLIC DATABASE LINK PROD_STANDBY_LINK 
CONNECT TO DBEXP_USER IDENTIFIED BY DBEXP_PASS 
USING 'PROD_STANDBY';

Check if the database link is working:


SQL> SELECT sysdate FROM dual@PROD_STANDBY_LINK;

Example Output:


SYSDATE
------------
01-JAN-25

Step 3: Create a Directory for Data Pump Export

Now, on the primary or non-standby database, create a directory for the export dump file:


CREATE DIRECTORY PROD_DUMP_DIR AS '/backup/dump/PROD_DUMP';

Check if the directory is created successfully:


SELECT * FROM dba_directories WHERE directory_name='PROD_DUMP_DIR';

Step 4: Run Data Pump Export (expdp) from the Non-Standby Database

Now, we run expdp on the primary database, specifying:

directory=PROD_DUMP_DIR (created in Step 3)
network_link=PROD_STANDBY_LINK (created in Step 2)
Target table or schema from the standby database

Example expdp Command:


expdp directory=PROD_DUMP_DIR \
      network_link=PROD_STANDBY_LINK \
      dumpfile=standby_data_2025.dmp \
      logfile=standby_data_2025.log \
      tables=SALES.USER_TRANSACTIONS

Step 5: Verify the Export Job

When the export runs, you will see output similar to:

Export: Release 19.12.0.0.0 - Production on Wed Jan 1 12:00:00 2025 Copyright (c) 1982, 2025, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 19c Enterprise Edition Release 19.12.0.0.0 - 64bit Production FLASHBACK automatically enabled to preserve database integrity. Starting "SYS"."SYS_EXPORT_TABLE_01": /******** AS SYSDBA directory=PROD_DUMP_DIR network_link=PROD_STANDBY_LINK dumpfile=standby_data_2025.dmp logfile=standby_data_2025.log tables=SALES.USER_TRANSACTIONS Estimate in progress using BLOCKS method... Processing object type TABLE_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 1024 KB . . exported "SALES"."USER_TRANSACTIONS" 10 MB 100000 rows Master table "SYS"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded ****************************************************************************** Dump file set for SYS.SYS_EXPORT_TABLE_01 is: /backup/dump/PROD_DUMP/standby_data_2025.dmp Job "SYS"."SYS_EXPORT_TABLE_01" successfully completed at Wed Jan 1 12:30:00 2025 elapsed 00:30:00

The export job completed successfully. The dump file is stored at /backup/dump/PROD_DUMP/standby_data_2025.dmp.

Please do like and subscribe to my youtube channel: https://www.youtube.com/@foalabs If you like this post please follow,share and comment

Exporting Data from a Physical Standby Database Using Data Pump