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)
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.
I don’t undestand why the title of this article is “How To Use Expdp To Export Data From Physical Standby Database” when you use the non standby database to do that.????
expdp will be initiated on primary , however the everything will be run on standby only . In this way you can reduce the server load from primary for export jobs
$ expdp network_link=EXPDP_LINK dumpfile=tab1.dmp logfile=tab1.log tables=U1.tab1 directory=dpdir reusedumpfile=yes
LRM-00101: unknown parameter name ‘reusedumpfile’
[oracle@cdc1 ~]$ expdp network_link=EXPDP_LINK dumpfile=tab1.dmp logfile=tab1.log tables=U1.tab1 directory=dpdir
Export: Release 12.1.0.2.0 – Production on Sat Oct 13 17:59:01 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, OLAP, Advanced Analytics and Real Application Testing options
Starting “SYS”.”SYS_EXPORT_TABLE_05″: /******** AS SYSDBA network_link=EXPDP_LINK dumpfile=tab1.dmp logfile=tab1.log tables=U1.tab1 directory=dpdir
ORA-39126: Worker unexpected fatal error in KUPW$WORKER.MAIN [INSERT INTO SYS.KU$_LIST_FILTER_TEMP_2@EXPDP_LINK (process_order,duplicate,object_schema,object_name,base_process_order,parent_process_order) SELECT process_order,duplicate,object_schema,object_name,base_process_order,parent_process_order FROM “SYS”.”SYS_EXPORT_TABLE_05″ WHERE process_order = :1]
ORA-08176: consistent read failure; rollback data not available
ORA-02063: preceding line from HRMS
ORA-02063: preceding 2 lines from EXPDP_LINK
ORA-06512: at “SYS.DBMS_SYS_ERROR”, line 95
ORA-06512: at “SYS.KUPW$WORKER”, line 11259
—– PL/SQL Call Stack —–
object line object
handle number name
0x952443a0 27116 package body SYS.KUPW$WORKER
0x952443a0 11286 package body SYS.KUPW$WORKER
0x952443a0 2135 package body SYS.KUPW$WORKER
0x7a875ae0 2 anonymous block
ALTER SESSION ENABLE PARALLEL DML
ALTER SESSION ENABLE PARALLEL DML
KUPP$PROC.WHATS_MY_ID
KUPP$PROC.WHATS_MY_ID
worker max message number: 1000
Full cluster access allowed
Testing ku$_list_filter_temp_2 on remote database
bind variable is: -55
Remote temp filter list stmt is: INSERT INTO SYS.KU$_LIST_FILTER_TEMP_2@EXPDP_LINK (process_order,duplicate,object_schema,object_name,base_process_order,parent_process_order) SELECT process_order,duplicate,object_schema,object_name,base_process_order,parent_process_order FROM “SYS”.”SYS_EXPORT_TABLE_05″ WHERE process_order = :1
In procedure DETERMINE_FATAL_ERROR with ORA-08176: consistent read failure; rollback data not available
ORA-02063: preceding line from HRMS
ORA-02063: preceding 2 lines from EXPDP_LINK
Job “SYS”.”SYS_EXPORT_TABLE_05″ stopped due to fatal error at Sat Oct 13 17:59:11 2018 elapsed 0 00:00:03
Hi
Can we run expdp from 3rd DB ?
Suppose I create a db link on 3rd DB , which is not in DR configuration but db link is created to point Standby DB.
My intention was
Simply expdp will be initiated from 3rd (which will attack physical standby) but 3rd DB should not part of DR setup.
Thanks
Yes, you can use network_link parameter to achive this