ADVERTISEMENT

How to Export Oracle Standby Data via Network Link

7 min read

Introduction

Running data exports on your primary Oracle database during business hours can slow down operations and frustrate users. This guide shows you how to use your physical standby database for exports instead, keeping production running smoothly.

What You’ll Learn

This tutorial covers exporting data from Oracle standby databases using the network link method. You’ll protect production performance while efficiently extracting the data you need.

The Problem

Standby databases run in READ ONLY mode, preventing direct Data Pump exports.

The Solution

Run Data Pump from your primary database but pull data from the standby using NETWORK_LINK. All resource usage happens on the standby server.

What You Need

Check these requirements before starting:

  • Active Data Guard with physical standby database
  • SYSDBA access on both primary and standby
  • TNS connectivity between servers
  • Disk space on primary server for export files

Step 1: Check Standby Database Status

Connect to your standby database and verify its mode.

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

NAME              OPEN_MODE                  DATABASE_ROLE
---------------------------------------------------------------
FINANCEDB_DR      READ ONLY WITH APPLY       PHYSICAL STANDBY

What to Look For

Your standby needs these settings:

  • OPEN_MODE shows READ ONLY WITH APPLY
  • DATABASE_ROLE shows PHYSICAL STANDBY

This confirms Active Data Guard is working correctly.

Step 2: Set Up Database Link

Switch to your primary database server. You need a connection from primary to standby.

Verify Primary Database

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

NAME              OPEN_MODE       DATABASE_ROLE
-------------------------------------------------
FINANCEDB_PROD    READ WRITE      PRIMARY

Create the Link

SQL> CREATE PUBLIC DATABASE LINK finance_standby_link 
     CONNECT TO backupuser IDENTIFIED BY Backup2024Secure 
     USING 'FINANCEDB_DR';

Database link created.

Understanding the Components

  • finance_standby_link: Your chosen link name
  • backupuser: User on standby database
  • Backup2024Secure: User password
  • FINANCEDB_DR: TNS alias for standby

Test the Connection

SQL> SELECT SYSDATE FROM dual@finance_standby_link;

SYSDATE
---------
20-DEC-24

If you see today’s date, the link works. If you get errors, check your tnsnames.ora file.

Step 3: Create Export Directory

Data Pump needs a location for dump files. Create this on the primary server.

Create OS Directory

[oracle@financedb-prod ~]$ mkdir -p /backup/standby_exports
[oracle@financedb-prod ~]$ ls -ld /backup/standby_exports
drwxr-xr-x 2 oracle oinstall 4096 Dec 20 09:30 /backup/standby_exports

Create Database Directory

SQL> CREATE DIRECTORY standby_dump_dir AS '/backup/standby_exports/';

Directory created.

Grant Permissions

SQL> GRANT READ, WRITE ON DIRECTORY standby_dump_dir TO sys;
Grant succeeded.

SQL> GRANT READ, WRITE ON DIRECTORY standby_dump_dir TO system;
Grant succeeded.

Remember: The directory must exist on the primary server, not the standby.

Step 4: Run the Export

Now you can execute the export. Using nohup runs it in background mode.

Navigate to Directory

[oracle@financedb-prod ~]$ cd /backup/standby_exports

Execute Export Command

[oracle@financedb-prod standby_exports]$ nohup expdp '"/ as sysdba"' DIRECTORY=standby_dump_dir NETWORK_LINK=finance_standby_link DUMPFILE=finance_schema_20dec2024_%U.dmp LOGFILE=finance_schema_20dec2024.log SCHEMAS=finance_app COMPRESSION=ALL PARALLEL=4 REUSE_DUMPFILES=Y &

Parameter Breakdown

Here’s what each parameter does:

  • nohup: Keeps the job running if you disconnect
  • “/ as sysdba”: Connects without password prompt
  • DIRECTORY: Where dump files go
  • NETWORK_LINK: Pulls data from standby (this is the key)
  • DUMPFILE: Output file name with %U for parallel files
  • LOGFILE: Progress log location
  • SCHEMAS: Which schema to export
  • COMPRESSION: Saves disk space
  • PARALLEL: Number of workers (faster exports)
  • REUSE_DUMPFILES: Overwrites existing files
  • &: Runs in background

The %U creates numbered files like finance_schema_20dec2024_01.dmp when using parallel processing.

Monitor Progress

Check background job status:

[oracle@financedb-prod standby_exports]$ jobs
[1]+  Running    nohup expdp ...

Watch real-time progress:

[oracle@financedb-prod standby_exports]$ tail -f nohup.out

Or check the log file:

[oracle@financedb-prod standby_exports]$ tail -f finance_schema_20dec2024.log
```

---

## Export Output Example

Here's what a successful export looks like:
```
Export: Release 11.2.0.4.0 - Production on Fri Dec 20 10:45:22 2024

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

Starting "SYS"."SYS_EXPORT_SCHEMA_01": DIRECTORY=standby_dump_dir 
NETWORK_LINK=finance_standby_link DUMPFILE=finance_schema_20dec2024_%U.dmp 
LOGFILE=finance_schema_20dec2024.log SCHEMAS=finance_app COMPRESSION=ALL PARALLEL=4

Estimate in progress using BLOCKS method...
Total estimation using BLOCKS method: 245.8 MB

Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/TABLE/TABLE

. . exported "FINANCE_APP"."TRANSACTIONS"      156.8 MB   1245890 rows
. . exported "FINANCE_APP"."ACCOUNTS"           45.2 MB    234567 rows
. . exported "FINANCE_APP"."CUSTOMERS"          32.5 MB     89456 rows
. . exported "FINANCE_APP"."AUDIT_LOG"          11.3 MB     45678 rows

Dump file set for SYS.SYS_EXPORT_SCHEMA_01 is:
  /backup/standby_exports/finance_schema_20dec2024_01.dmp
  /backup/standby_exports/finance_schema_20dec2024_02.dmp
  /backup/standby_exports/finance_schema_20dec2024_03.dmp
  /backup/standby_exports/finance_schema_20dec2024_04.dmp

Job "SYS"."SYS_EXPORT_SCHEMA_01" successfully completed at Fri Dec 20 11:12:47 2024 
elapsed 0 00:27:25

Notice that PARALLEL=4 created four separate files.

Verify Results

Check that files were created successfully:

[oracle@financedb-prod standby_exports]$ ls -lh
total 246M
-rw-r----- 1 oracle oinstall  62M Dec 20 11:12 finance_schema_20dec2024_01.dmp
-rw-r----- 1 oracle oinstall  61M Dec 20 11:12 finance_schema_20dec2024_02.dmp
-rw-r----- 1 oracle oinstall  62M Dec 20 11:12 finance_schema_20dec2024_03.dmp
-rw-r----- 1 oracle oinstall  61M Dec 20 11:12 finance_schema_20dec2024_04.dmp
-rw-r--r-- 1 oracle oinstall  18K Dec 20 11:12 finance_schema_20dec2024.log
-rw------- 1 oracle oinstall  15K Dec 20 11:12 nohup.out

Other Export Options

Export Multiple Schemas

[oracle@financedb-prod standby_exports]$ nohup expdp '"/ as sysdba"' DIRECTORY=standby_dump_dir NETWORK_LINK=finance_standby_link DUMPFILE=multi_schema_20dec2024_%U.dmp LOGFILE=multi_schema_20dec2024.log SCHEMAS=finance_app,hr_app,sales_app COMPRESSION=ALL PARALLEL=4 REUSE_DUMPFILES=Y &

Export Specific Tables

[oracle@financedb-prod standby_exports]$ nohup expdp '"/ as sysdba"' DIRECTORY=standby_dump_dir NETWORK_LINK=finance_standby_link DUMPFILE=specific_tables_20dec2024_%U.dmp LOGFILE=specific_tables_20dec2024.log TABLES=finance_app.transactions,finance_app.accounts COMPRESSION=ALL PARALLEL=2 REUSE_DUMPFILES=Y &

Full Database Export

[oracle@financedb-prod standby_exports]$ nohup expdp '"/ as sysdba"' DIRECTORY=standby_dump_dir NETWORK_LINK=finance_standby_link DUMPFILE=fulldb_20dec2024_%U.dmp LOGFILE=fulldb_20dec2024.log FULL=Y COMPRESSION=ALL PARALLEL=8 REUSE_DUMPFILES=Y &

Monitor Long Exports

Check Job Status in SQL

SQL> SELECT owner_name, job_name, state, degree 
     FROM dba_datapump_jobs 
     WHERE state = 'EXECUTING';

View Progress Percentage

SQL> SELECT username, opname, sofar, totalwork, 
            ROUND(sofar/totalwork*100,2) AS percent_complete
     FROM v$session_longops
     WHERE opname LIKE 'EXPORT%'
     AND sofar <> totalwork;

Stop Running Export

Find the process:

[oracle@financedb-prod ~]$ ps -ef | grep expdp

Kill the process:

[oracle@financedb-prod ~]$ kill -9 <PID>

Key Points

Where Things Happen

  • Job runs: Primary database server
  • Data comes from: Standby database via NETWORK_LINK
  • Files stored: Primary database server
  • Production impact: Zero

How It Works

The primary database manages the job and writes files locally. The NETWORK_LINK parameter redirects all data reading to the standby database. CPU, memory, and disk activity for reading data happens entirely on the standby server.

Troubleshooting Guide

Connection Issues

  • Error: ORA-02019 connection description not found
  • Fix: Check TNS alias spelling in database link and tnsnames.ora
  • Error: ORA-12514 listener does not know service
  • Fix: Verify standby listener is running and service is registered

Performance Issues

  • Problem: Export runs slowly
  • Fix: Check network bandwidth between servers and increase PARALLEL value

Configuration Issues

  • Error: Directory does not exist
  • Fix: Create directory on PRIMARY server filesystem
  • Error: ORA-39002 invalid operation with PARALLEL
  • Fix: Use %U in dumpfile name for parallel operations
  • Problem: Job stops when terminal closes
  • Fix: Use nohup at start and & at end of command

Summary

This method protects production performance by using your standby database for data extraction. The primary database handles job management while the standby does the heavy work.

Benefits

You get zero impact on production users. Standby resources are used effectively. Data consistency is maintained through Active Data Guard. Standard Data Pump tools make implementation simple.

Important Note

The export job completes successfully from the primary database while using standby resources. The network link redirects data reading operations to the standby instance. This approach is ideal when maintaining production responsiveness is critical.

Close ✖