Customise Consent Preferences

We use cookies to help you navigate efficiently and perform certain functions. You will find detailed information about all cookies under each consent category below.

The cookies that are categorised as "Necessary" are stored on your browser as they are essential for enabling the basic functionalities of the site. ... 

Always Active

Necessary cookies are required to enable the basic features of this site, such as providing secure log-in or adjusting your consent preferences. These cookies do not store any personally identifiable data.

No cookies to display.

Functional cookies help perform certain functionalities like sharing the content of the website on social media platforms, collecting feedback, and other third-party features.

No cookies to display.

Analytical cookies are used to understand how visitors interact with the website. These cookies help provide information on metrics such as the number of visitors, bounce rate, traffic source, etc.

No cookies to display.

Performance cookies are used to understand and analyse the key performance indexes of the website which helps in delivering a better user experience for the visitors.

No cookies to display.

Advertisement cookies are used to provide visitors with customised advertisements based on the pages you visited previously and to analyse the effectiveness of the ad campaigns.

No cookies to display.

How To Use Expdp To Export Data From Physical Standby Database

Breadcrumb Abstract Shape
Breadcrumb Abstract Shape
Breadcrumb Abstract Shape
Breadcrumb Abstract Shape
Breadcrumb Abstract Shape
Breadcrumb Abstract Shape
  • User AvatarKiran Dalvi
  • 11 Oct, 2023
  • 0 Comments
  • 2 Mins Read

How To Use Expdp To Export Data From Physical Standby Database

How To Use Expdp To Export Data From Physical Standby Database

Data export from production is not recommended at peak times.

(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_LINKparameter and run the expdp job from any non-standby database(OPEN database)

Steps are as Follows :

  1. Check whether standby database is in read only mode:
1
2
3
4
5
SQL> select database_name,DATABASE_ROLE,open_mode from v$database;
 
DATABASE_NAME DATABASE_ROLE OPEN_MODE
-------------- ---------------- --------------------
STANDBY PHYSICAL STANDBY READ ONLY WITH APPLY
  1. create a database link on non standby database.[POINTING TO STANDBY DB]
1
2
3
4
5
6
7
8
9
10
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-SEP-23
  1. Create database directory on non-standby server:
1
create directory ERIC_DUMP as '/data/dump/ERIC_DUMP'
  1. Run expdp from non-standby database server:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
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 SEP 30 10:38:59 2023
 
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:
/data/dump/ERIC_DUMP/standby_schema.dmp
Job "SYS"."SYS_EXPORT_TABLE_01" successfully completed at Tue Jan 30 1

We have successfully generated the export dump from standby database.

Hope it Helps!