steps to use expdp to export data from physical standby database

Hello Friends,

In this post we will discuss about taking export backup from standby database

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).

3.So to achieve this, we will use the NETWORK_LINK parameter and run the expdp job from any non-standby database(OPEN database)

STEP1. Check whether standby database is in read only mode:

SQL> set pages 9999 lines 300
col OPEN_MODE for a10
col HOST_NAME for a30
select name DB_NAME,HOST_NAME,DATABASE_ROLE,OPEN_MODE,log_mode,version DB_VERSION,LOGINS,to_char(STARTUP_TIME,'DD-MON-YYYY HH24:MI:SS') "DB UP TIME" from v$database,v$instance;
SQL> SQL> SQL> 
DB_NAME   HOST_NAME			             DATABASE_ROLE	  OPEN_MODE             LOG_MODE	  DB_VERSION	    LOGINS     DB UP TIME
--------- ------------------------------ ---------------- ---------- ------------ ----------------- ---------- -----------------------------
CASHDB	  dm10gg10.database.com          PHYSICAL STANDBY READ ONLY WITH APPLY ARCHIVELOG   12.1.0.2.0	    ALLOWED    16-JUN-2020 11:10:37

STEP2. create a database link on non standby database.POINTING TO STANDBY DB

SQL>  create public database link Stby_link connect to SADBA identified by SADD12554 using 'CASHDR';
 
Database link created.
 
 
SQL> select sysdate from dual@Stby_link;
 
SYSDATE
---------
16-JUN-20

STEP3. Create db directory on Primary server:

create directory CASH_DUMP as '/dumparea/dump/CASH_DUMP'

STEP4. Run expdp from Primary database server:

expdp  directory=CASH_DUMP network_link=Stby_link dumpfile=standby_16062020.dmp logfile=standby_16062020.log tables=SULTAN.TBL1
 
 
Export: Release 12.1.0.2.0 - Production on Mon Jun 16 11:38:59 2020
 
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=CASH_DUMP network_link=Stby_link dumpfile=standby_16062020.dmp logfile=standby_16062020.log tables=SULTAN.TBL1
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 "SULTAN"."TBL1"                 862.2 KB 70000 rows
Master table "SYS"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_TABLE_01 is:
  /dumparea/dump/CASH_DUMP/standby_schema.dmp
Job "SYS"."SYS_EXPORT_TABLE_01" successfully completed at Mon Jun 16 11:44:55 2020 elapsed 0 00:05:20

• Tweet
• Share
• Share
• Share
• Share

Related Post