Description:-
1. Physical standby database should be in READ-ONLY MODE (ACTIVE DATA GUARD)
2.We cannot run expdp directly on physical standby database. Because data pump 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)
Let start Demo:-
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
————– —————- ——————–
PROD PHYSICAL STANDBY READ ONLY WITH APPLY
2. create a database link on non standby database.[POINTING TO STANDBY DB]
SQL> create public database link RAM_NWLINK connect to DBWRDBA identified by DBWRDBA1 using ‘DBdbwr’;
Database link created.
SQL> select sysdate from dual@RAM_NWLINK;
SYSDATE
———
23-JUL-19
3. Create db directory on non-standby server:
create directory RAM_DUMP as ‘/dumparea/dump/RAM_DUMP’
4. Run expdp from non-standby database server:
expdp directory=RAM_DUMP network_link=RAM_NWLINK dumpfile=standby_schema.dmp logfile=standby_schema.log tables=EMP_USER.CHANNEL_NUMBER
Export: Release 19.2.0.0.0 – Production on Tue Jul 23 09:48:59 2018
Copyright (c) 1982, 2018, Oracle and/or its affiliates. All rights reserved.
Username: / as sysdba
Connected to: Oracle Database 19c Enterprise Edition Release 19.2.0.0.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=RAM_DUMP network_link=RAM_NWLINK 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: 620 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” 971.2 KB 80000 rows
Master table “SYS”.”SYS_EXPORT_TABLE_01″ successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_TABLE_01 is:
/dumparea/dump/RAM_DUMP/standby_schema.dmp
Job “SYS”.”SYS_EXPORT_TABLE_01″ successfully completed at Tue Jul 23 10:47:54 2018 elapsed 0 00:29:14
We have successfully generated the export dump from standby database.
Catch Me On:- Hariprasath Rajaram
LinkedIn: https://www.linkedin.com/in/hari-prasath-aa65bb19/
Facebook: https://www.facebook.com/HariPrasathdba
FB Group: https://www.facebook.com/groups/894402327369506/
FB Page : https://www.facebook.com/dbahariprasath/?
Twitter : https://twitter.com/hariprasathdba
