- 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:
- 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_LINKparameter and run the expdp job from any non-standby database(OPEN database)
Steps are as Follows :
- 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 -------------- ---------------- -------------------- STANDBY PHYSICAL STANDBY READ ONLY WITH APPLY
- 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-SEP-23
- Create database directory on non-standby server:
create directory ERIC_DUMP as '/data/dump/ERIC_DUMP'
- 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 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.