During peak business hours, It is not advised to export data from production .(as it might impact the performance).
So if we have a physical standby database, we can export data using datapump from standby database.

PRE-REQUISITES:

1. Physical standby database should be in READ-ONLY MODE (ACTIVE DATAGUARD)
2.We cannot run expdp directly on physical standby database. Because datapump job can be created only on a read-write database(OPEN).
So to achieve this, we will use the NETWORK_LINK parameter and run the expdp job from any non-standby database(OPEN database)

 

Refer open physical standby database in read only mode 

STEPS:

1. Check whether standby database is in read only mode:

SQL> select database_name,DATABASE_ROLE,open_mode  from v$database;

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

2. create a database link on non standby database.[POINTING TO STANDBY DB]

SQL>  create public database link LINK_EXPDP connect to BSSDBA identified by BSSDBA442 using 'SALdbr';

Database link created.


SQL> select sysdate from dual@LINK_EXPDP;

SYSDATE
---------
30-JAN-18

3. Create db directory on non-standby server:

create directory ERIC_DUMP as '/dumparea/dump/ERIC_DUMP'

4. Run expdp from non-standby database server:

expdp  directory=ERIC_DUMP network_link=LINK_EXPDP dumpfile=standby_schema.dmp logfile=standby_schema.log tables=EMP_USER.CHANNEL_NUMBER


Export: Release 12.1.0.2.0 - Production on Tue Jan 30 10:38:59 2018

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

Username: / as sysdba


Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options
FLASHBACK automatically enabled to preserve database integrity.
Starting "SYS"."SYS_EXPORT_TABLE_01":  /******** AS SYSDBA directory=ERIC_DUMP network_link=LINK_EXPDP dumpfile=standby_schema.dmp logfile=standby_schema.log tables=EMP_USER.CHANNEL_NUMBER
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 320 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
. . exported "EMP_USER"."CHANNEL_NUMBER"                 171.2 KB   20000 rows
Master table "SYS"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_TABLE_01 is:
  /dumparea/dump/ERIC_DUMP/standby_schema.dmp
Job "SYS"."SYS_EXPORT_TABLE_01" successfully completed at Tue Jan 30 10:47:54 2018 elapsed 0 00:07:14

We have successfully generated the export dump from standby database.

datapumpEXPDP DATAGUARD


Related Topics

How to recreate physical standby controlfile
How to disable enable log shipping in standby using dgmgrl
How to add a tempfile in primary database in dataguard
How to use expdp to export data from physical standby database
Flashback primary database in dataguard environment
Useful DGMGRL commands in oracle dataguard
Standby redologs in oracle dataguard
How to enable active dataguard in physical standby database
How to setup dataguard broker configuration (DG broker) in 12c

You May Also Like

how to Print execution time of tasks in ansible
Useful flashback related commands
How to run expdp in pluggable database(PDB)
TNS-12542: TNS:address already in use
How to monitor parallel queries in oracle db
Find user commits per minute in oracle database
Apply patch on oracle 12.2 database ( Release update)
Apply database proactive bundle patch in RAC using manual process
Hive Installation with derby
Lock account automatically with INACTIVE_ACCOUNT_TIME

From This Website

How to move spfile from file system to ASM in RAC
How to create user in MySQL Database
Script to get cpu usage and wait event information in oracle database
ORA-20101: TABLESPACE is not a ASSM (Automatic Segment Space Management) tablespace
Standby redologs in oracle dataguard
Setting up Table replication in oracle goldengate
Steps for changing public hostname for a standalone grid infrastructure
How to generate AWR report in RAC
How to generate ADDM report
Install oracle enterprise manager cloud control 12c