{"id":5031,"date":"2026-01-15T12:46:53","date_gmt":"2026-01-15T07:16:53","guid":{"rendered":"https:\/\/w3buddy.com\/?p=5031"},"modified":"2026-01-20T10:13:57","modified_gmt":"2026-01-20T04:43:57","slug":"how-to-export-oracle-standby-data-via-network-link","status":"publish","type":"post","link":"https:\/\/w3buddy.com\/blog\/how-to-export-oracle-standby-data-via-network-link\/","title":{"rendered":"How to Export Oracle Standby Data via Network Link"},"content":{"rendered":"\n<h2 class=\"wp-block-heading\">Introduction<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">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.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">What You&#8217;ll Learn<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">This tutorial covers exporting data from Oracle standby databases using the network link method. You&#8217;ll protect production performance while efficiently extracting the data you need.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">The Problem<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">Standby databases run in READ ONLY mode, preventing direct Data Pump exports.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">The Solution<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">Run Data Pump from your primary database but pull data from the standby using NETWORK_LINK. All resource usage happens on the standby server.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">What You Need<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">Check these requirements before starting:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Active Data Guard with physical standby database<\/li>\n\n\n\n<li>SYSDBA access on both primary and standby<\/li>\n\n\n\n<li>TNS connectivity between servers<\/li>\n\n\n\n<li>Disk space on primary server for export files<\/li>\n<\/ul>\n\n\n\n<h2 class=\"wp-block-heading\">Step 1: Check Standby Database Status<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">Connect to your standby database and verify its mode.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>SQL> SELECT name, open_mode, database_role FROM v$database;\n\nNAME              OPEN_MODE                  DATABASE_ROLE\n<em>---------------------------------------------------------------<\/em>\nFINANCEDB_DR      READ ONLY WITH APPLY       PHYSICAL STANDBY<\/code><\/pre>\n\n\n\n<h3 class=\"wp-block-heading\">What to Look For<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">Your standby needs these settings:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>OPEN_MODE shows READ ONLY WITH APPLY<\/li>\n\n\n\n<li>DATABASE_ROLE shows PHYSICAL STANDBY<\/li>\n<\/ul>\n\n\n\n<p class=\"wp-block-paragraph\">This confirms Active Data Guard is working correctly.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Step 2: Set Up Database Link<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">Switch to your primary database server. You need a connection from primary to standby.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Verify Primary Database<\/h3>\n\n\n\n<pre class=\"wp-block-code\"><code>SQL> SELECT name, open_mode, database_role FROM v$database;\n\nNAME              OPEN_MODE       DATABASE_ROLE\n<em>-------------------------------------------------<\/em>\nFINANCEDB_PROD    READ WRITE      PRIMARY<\/code><\/pre>\n\n\n\n<h3 class=\"wp-block-heading\">Create the Link<\/h3>\n\n\n\n<pre class=\"wp-block-code\"><code>SQL> CREATE PUBLIC DATABASE LINK finance_standby_link \n     CONNECT TO backupuser IDENTIFIED BY Backup2024Secure \n     USING 'FINANCEDB_DR';\n\nDatabase link created.<\/code><\/pre>\n\n\n\n<h3 class=\"wp-block-heading\">Understanding the Components<\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li>finance_standby_link: Your chosen link name<\/li>\n\n\n\n<li>backupuser: User on standby database<\/li>\n\n\n\n<li>Backup2024Secure: User password<\/li>\n\n\n\n<li>FINANCEDB_DR: TNS alias for standby<\/li>\n<\/ul>\n\n\n\n<h3 class=\"wp-block-heading\">Test the Connection<\/h3>\n\n\n\n<pre class=\"wp-block-code\"><code>SQL> SELECT SYSDATE FROM dual@finance_standby_link;\n\nSYSDATE\n<em>---------<\/em>\n20-DEC-24<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">If you see today&#8217;s date, the link works. If you get errors, check your tnsnames.ora file.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Step 3: Create Export Directory<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">Data Pump needs a location for dump files. Create this on the primary server.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Create OS Directory<\/h3>\n\n\n\n<pre class=\"wp-block-code\"><code>&#91;oracle@financedb-prod ~]$ mkdir -p \/backup\/standby_exports\n&#91;oracle@financedb-prod ~]$ ls -ld \/backup\/standby_exports\ndrwxr-xr-x 2 oracle oinstall 4096 Dec 20 09:30 \/backup\/standby_exports<\/code><\/pre>\n\n\n\n<h3 class=\"wp-block-heading\">Create Database Directory<\/h3>\n\n\n\n<pre class=\"wp-block-code\"><code>SQL> CREATE DIRECTORY standby_dump_dir AS '\/backup\/standby_exports\/';\n\nDirectory created.<\/code><\/pre>\n\n\n\n<h3 class=\"wp-block-heading\">Grant Permissions<\/h3>\n\n\n\n<pre class=\"wp-block-code\"><code>SQL> GRANT READ, WRITE ON DIRECTORY standby_dump_dir TO sys;\nGrant succeeded.\n\nSQL> GRANT READ, WRITE ON DIRECTORY standby_dump_dir TO system;\nGrant succeeded.<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">Remember: The directory must exist on the primary server, not the standby.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Step 4: Run the Export<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">Now you can execute the export. Using nohup runs it in background mode.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Navigate to Directory<\/h3>\n\n\n\n<pre class=\"wp-block-code\"><code>&#91;oracle@financedb-prod ~]$ cd \/backup\/standby_exports<\/code><\/pre>\n\n\n\n<h3 class=\"wp-block-heading\">Execute Export Command<\/h3>\n\n\n\n<pre class=\"wp-block-code\"><code>&#91;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 &amp;<\/code><\/pre>\n\n\n\n<h3 class=\"wp-block-heading\">Parameter Breakdown<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">Here&#8217;s what each parameter does:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>nohup<\/strong>: Keeps the job running if you disconnect<\/li>\n\n\n\n<li><strong>&#8220;\/ as sysdba&#8221;<\/strong>: Connects without password prompt<\/li>\n\n\n\n<li><strong>DIRECTORY<\/strong>: Where dump files go<\/li>\n\n\n\n<li><strong>NETWORK_LINK<\/strong>: Pulls data from standby (this is the key)<\/li>\n\n\n\n<li><strong>DUMPFILE<\/strong>: Output file name with %U for parallel files<\/li>\n\n\n\n<li><strong>LOGFILE<\/strong>: Progress log location<\/li>\n\n\n\n<li><strong>SCHEMAS<\/strong>: Which schema to export<\/li>\n\n\n\n<li><strong>COMPRESSION<\/strong>: Saves disk space<\/li>\n\n\n\n<li><strong>PARALLEL<\/strong>: Number of workers (faster exports)<\/li>\n\n\n\n<li><strong>REUSE_DUMPFILES<\/strong>: Overwrites existing files<\/li>\n\n\n\n<li><strong>&amp;<\/strong>: Runs in background<\/li>\n<\/ul>\n\n\n\n<p class=\"wp-block-paragraph\">The %U creates numbered files like finance_schema_20dec2024_01.dmp when using parallel processing.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Monitor Progress<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">Check background job status:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>&#91;oracle@financedb-prod standby_exports]$ jobs\n&#91;1]+  Running    nohup expdp ...<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">Watch real-time progress:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>&#91;oracle@financedb-prod standby_exports]$ tail -f nohup.out<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">Or check the log file:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>&#91;oracle@financedb-prod standby_exports]$ tail -f finance_schema_20dec2024.log\n```\n\n---\n\n<em>## Export Output Example<\/em>\n\nHere's what a successful export looks like:\n```\nExport: Release 11.2.0.4.0 - Production on Fri Dec 20 10:45:22 2024\n\nCopyright (c) 1982, 2011, Oracle and\/or its affiliates. All rights reserved.\n\nConnected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production\n\nStarting \"SYS\".\"SYS_EXPORT_SCHEMA_01\": DIRECTORY=standby_dump_dir \nNETWORK_LINK=finance_standby_link DUMPFILE=finance_schema_20dec2024_%U.dmp \nLOGFILE=finance_schema_20dec2024.log SCHEMAS=finance_app COMPRESSION=ALL PARALLEL=4\n\nEstimate in progress using BLOCKS method...\nTotal estimation using BLOCKS method: 245.8 MB\n\nProcessing object type SCHEMA_EXPORT\/USER\nProcessing object type SCHEMA_EXPORT\/TABLE\/TABLE\n\n. . exported \"FINANCE_APP\".\"TRANSACTIONS\"      156.8 MB   1245890 rows\n. . exported \"FINANCE_APP\".\"ACCOUNTS\"           45.2 MB    234567 rows\n. . exported \"FINANCE_APP\".\"CUSTOMERS\"          32.5 MB     89456 rows\n. . exported \"FINANCE_APP\".\"AUDIT_LOG\"          11.3 MB     45678 rows\n\nDump file set for SYS.SYS_EXPORT_SCHEMA_01 is:\n  \/backup\/standby_exports\/finance_schema_20dec2024_01.dmp\n  \/backup\/standby_exports\/finance_schema_20dec2024_02.dmp\n  \/backup\/standby_exports\/finance_schema_20dec2024_03.dmp\n  \/backup\/standby_exports\/finance_schema_20dec2024_04.dmp\n\nJob \"SYS\".\"SYS_EXPORT_SCHEMA_01\" successfully completed at Fri Dec 20 11:12:47 2024 \nelapsed 0 00:27:25<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">Notice that PARALLEL=4 created four separate files.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Verify Results<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">Check that files were created successfully:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>&#91;oracle@financedb-prod standby_exports]$ ls -lh\ntotal 246M\n-rw-r----- 1 oracle oinstall  62M Dec 20 11:12 finance_schema_20dec2024_01.dmp\n-rw-r----- 1 oracle oinstall  61M Dec 20 11:12 finance_schema_20dec2024_02.dmp\n-rw-r----- 1 oracle oinstall  62M Dec 20 11:12 finance_schema_20dec2024_03.dmp\n-rw-r----- 1 oracle oinstall  61M Dec 20 11:12 finance_schema_20dec2024_04.dmp\n-rw-r--r-- 1 oracle oinstall  18K Dec 20 11:12 finance_schema_20dec2024.log\n-rw------- 1 oracle oinstall  15K Dec 20 11:12 nohup.out<\/code><\/pre>\n\n\n\n<h2 class=\"wp-block-heading\">Other Export Options<\/h2>\n\n\n\n<h3 class=\"wp-block-heading\">Export Multiple Schemas<\/h3>\n\n\n\n<pre class=\"wp-block-code\"><code>&#91;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 &amp;<\/code><\/pre>\n\n\n\n<h3 class=\"wp-block-heading\">Export Specific Tables<\/h3>\n\n\n\n<pre class=\"wp-block-code\"><code>&#91;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 &amp;<\/code><\/pre>\n\n\n\n<h3 class=\"wp-block-heading\">Full Database Export<\/h3>\n\n\n\n<pre class=\"wp-block-code\"><code>&#91;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 &amp;<\/code><\/pre>\n\n\n\n<h2 class=\"wp-block-heading\">Monitor Long Exports<\/h2>\n\n\n\n<h3 class=\"wp-block-heading\">Check Job Status in SQL<\/h3>\n\n\n\n<pre class=\"wp-block-code\"><code>SQL> SELECT owner_name, job_name, state, degree \n     FROM dba_datapump_jobs \n     WHERE state = 'EXECUTING';<\/code><\/pre>\n\n\n\n<h3 class=\"wp-block-heading\">View Progress Percentage<\/h3>\n\n\n\n<pre class=\"wp-block-code\"><code>SQL> SELECT username, opname, sofar, totalwork, \n            ROUND(sofar\/totalwork*100,2) AS percent_complete\n     FROM v$session_longops\n     WHERE opname LIKE 'EXPORT%'\n     AND sofar &lt;> totalwork;<\/code><\/pre>\n\n\n\n<h3 class=\"wp-block-heading\">Stop Running Export<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">Find the process:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>&#91;oracle@financedb-prod ~]$ ps -ef | grep expdp<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">Kill the process:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>&#91;oracle@financedb-prod ~]$ kill -9 &lt;PID><\/code><\/pre>\n\n\n\n<h2 class=\"wp-block-heading\">Key Points<\/h2>\n\n\n\n<h3 class=\"wp-block-heading\">Where Things Happen<\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>Job runs<\/strong>: Primary database server<\/li>\n\n\n\n<li><strong>Data comes from<\/strong>: Standby database via NETWORK_LINK<\/li>\n\n\n\n<li><strong>Files stored<\/strong>: Primary database server<\/li>\n\n\n\n<li><strong>Production impact<\/strong>: Zero<\/li>\n<\/ul>\n\n\n\n<h3 class=\"wp-block-heading\">How It Works<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">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.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Troubleshooting Guide<\/h2>\n\n\n\n<h3 class=\"wp-block-heading\">Connection Issues<\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>Error<\/strong>: ORA-02019 connection description not found<\/li>\n\n\n\n<li><strong>Fix<\/strong>: Check TNS alias spelling in database link and tnsnames.ora<\/li>\n\n\n\n<li><strong>Error<\/strong>: ORA-12514 listener does not know service<\/li>\n\n\n\n<li><strong>Fix<\/strong>: Verify standby listener is running and service is registered<\/li>\n<\/ul>\n\n\n\n<h3 class=\"wp-block-heading\">Performance Issues<\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>Problem<\/strong>: Export runs slowly<\/li>\n\n\n\n<li><strong>Fix<\/strong>: Check network bandwidth between servers and increase PARALLEL value<\/li>\n<\/ul>\n\n\n\n<h3 class=\"wp-block-heading\">Configuration Issues<\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>Error<\/strong>: Directory does not exist<\/li>\n\n\n\n<li><strong>Fix<\/strong>: Create directory on PRIMARY server filesystem<\/li>\n\n\n\n<li><strong>Error<\/strong>: ORA-39002 invalid operation with PARALLEL<\/li>\n\n\n\n<li><strong>Fix<\/strong>: Use %U in dumpfile name for parallel operations<\/li>\n\n\n\n<li><strong>Problem<\/strong>: Job stops when terminal closes<\/li>\n\n\n\n<li><strong>Fix<\/strong>: Use nohup at start and &amp; at end of command<\/li>\n<\/ul>\n\n\n\n<h2 class=\"wp-block-heading\">Summary<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">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.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Benefits<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">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.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Important Note<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">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.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>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&#8217;ll Learn This tutorial covers exporting data from Oracle standby databases using the network link method. You&#8217;ll [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":5033,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"googlesitekit_rrm_CAowu461DA:productID":"","footnotes":""},"categories":[1225],"tags":[],"class_list":["post-5031","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-database"],"_links":{"self":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/posts\/5031","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/comments?post=5031"}],"version-history":[{"count":1,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/posts\/5031\/revisions"}],"predecessor-version":[{"id":5032,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/posts\/5031\/revisions\/5032"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/media\/5033"}],"wp:attachment":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/media?parent=5031"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/categories?post=5031"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/tags?post=5031"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}