DBA Hub

📋Steps in this guide1/2

How To Use Expdp To Export Data From Physical Standby Database

Data export from production is not recommended at peak times.

oracle configurationintermediate
by OracleDba
15 views
1

Overview

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. - Physical standby database should be in READ-ONLY MODE ( ACTIVE DATAGUARD ) - 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) - Check whether standby database is in read only mode: - create a database link on non standby database.[POINTING TO STANDBY DB]

Code/Command (click line numbers to comment):

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
2

Section 2

- Create database directory on non-standby server: - Run expdp from non-standby database server: We have successfully generated the export dump from standby database.

Code/Command (click line numbers to comment):

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
29
30
31
32
33
34
35
36
37
38
39
40
41
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

create directory ERIC_DUMP as '/data/dump/ERIC_DUMP'

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

Comments (0)

Please to add comments

No comments yet. Be the first to comment!