Export
Generating an export from a Data Guard database using NETWORK_LINK.
Category: Database Author: Andre Luiz Dutra Ontalba (Board Member) Date: 5 years ago Comments: 0

Generating an export from a Data Guard database using NETWORK_LINK

This article explains how we can export Data Guard using NETWORK_LINK.
 
The Physical Standby database must be opened in “READ ONLY” mode.

Steps to execute to export from Physical Standby Database

Physical Standby Database

— Connect to Physical Standby database and check its status

 

 

[oracle@vm1 admin] sqlplus / as sysdba


SQL> select instance_name, status from v$instance;

INSTANCE_NAME STATUS
---------------- ------------
PHYSTBY MOUNTED

-- Cancel managed recovery and open database in "READ ONLY" mode.


SQL> alter database recover managed standby database cancel;



SQL> alter database open read only;




-- Verify database status

SQL> select instance_name, status from v$instance;

INSTANCE_NAME STATUS
---------------- ------------
VTABOLDG OPEN


SQL> select open_mode from v$database;

OPEN_MODE
--------------------
READ ONLY


 

“Non Standby” Database           

On the same machine I created a new instance just to serve as a bridge to perform the procedure.

-- create DB Link, Oracle Directory.


[oracle@vm1 admin] sqlplus / as sysdba


SQL> create database link expd connect to system identified by oracle using ‘DG_VTABOL’;

SQL> select db_unique_name from v$database;

DB_UNIQUE_NAME
--------------------
DP

SQL> select db_unique_name from v$database@exp;

DB_UNIQUE_NAME
--------------------
VTABOL

SQL> create directory datapump as ‘/tmp’;

 


Use NETWORK_LINK to database link above to connect to the Physical Standby database:

 

[oracle@vm1 admin] expdp DIRECTORY=DATAPUMP network_link=EXP DUMPFILE=DG_EXPDP.dmp SCHEMAS=DBA_DISCOVER,ITAMARATI,VENDASEXTERNAS,VTABOL,ABSTRATOS logfile=expdp.log PARALLEL=4



Export: Release 12.1.0.2.0 - Production on Fri Sep 6 17:32:54 2019

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, Automatic Storage Management, OLAP, Data Mining

and Real Application Testing options

FLASHBACK automatically enabled to preserve database integrity.

Starting "SYS"."SYS_EXPORT_SCHEMA_02":  sys/******** AS SYSDBA DIRECTORY=DATAPUMP network_link=EXP DUMPFILE=AWS.dmp SCHEMAS=DBA_DISCOVER,ITAMARATI,VENDASEXTERNAS,VTABOL,ABSTRATOS logfile=expdp.log PARALLEL=4

Estimate in progress using BLOCKS method...

Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 84.29 GB

Processing object type SCHEMA_EXPORT/USER

Processing object type SCHEMA_EXPORT/SYSTEM_GRANT

Processing object type SCHEMA_EXPORT/ROLE_GRANT

Processing object type SCHEMA_EXPORT/DEFAULT_ROLE

Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA

Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA

Processing object type SCHEMA_EXPORT/SYNONYM/SYNONYM

Processing object type SCHEMA_EXPORT/DB_LINK

Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE

Processing object type SCHEMA_EXPORT/SEQUENCE/GRANT/OWNER_GRANT/OBJECT_GRANT

Processing object type SCHEMA_EXPORT/TABLE/TABLE

Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT

Processing object type SCHEMA_EXPORT/TABLE/COMMENT

Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_SPEC

Processing object type SCHEMA_EXPORT/PACKAGE/GRANT/OWNER_GRANT/OBJECT_GRANT

Processing object type SCHEMA_EXPORT/FUNCTION/FUNCTION

Processing object type SCHEMA_EXPORT/FUNCTION/GRANT/OWNER_GRANT/OBJECT_GRANT

Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE

Processing object type SCHEMA_EXPORT/PROCEDURE/GRANT/OWNER_GRANT/OBJECT_GRANT

Processing object type SCHEMA_EXPORT/PACKAGE/COMPILE_PACKAGE/PACKAGE_SPEC/ALTER_PACKAGE_SPEC

Processing object type SCHEMA_EXPORT/FUNCTION/ALTER_FUNCTION

Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE

Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX

Processing object type SCHEMA_EXPORT/TABLE/INDEX/FUNCTIONAL_INDEX/INDEX

Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT

Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS

Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/FUNCTIONAL_INDEX/INDEX_STATISTICS

Processing object type SCHEMA_EXPORT/VIEW/VIEW

Processing object type SCHEMA_EXPORT/VIEW/GRANT/OWNER_GRANT/OBJECT_GRANT

Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_BODY

Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT

Processing object type SCHEMA_EXPORT/TABLE/TRIGGER

Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

Processing object type SCHEMA_EXPORT/MATERIALIZED_VIEW

Processing object type SCHEMA_EXPORT/JOB

Processing object type SCHEMA_EXPORT/REFRESH_GROUP

Processing object type SCHEMA_EXPORT/POST_SCHEMA/PROCOBJ




Dump file set for SYS.SYS_EXPORT_SCHEMA_02 is:

  /tmp/DG_EXPDP.dmp

Job "SYS"."SYS_EXPORT_SCHEMA_02" completed with 0 error(s) at Fri Sep 6 19:55:35 2019 elapsed 0 02:22:29


 

On Physical Standby Database

[oracle@vm1 admin] sqlplus / as sysdba


SQL> shutdown immediate



SQL> startup mount




SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;




SQL> SELECT
       ARCH.THREAD# "Thread",
       ARCH.SEQUENCE# "Last Sequence Received",
       APPL.SEQUENCE# "Last Sequence Applied",
       (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference"
FROM
       (SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN
       (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,
       (SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN
       (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL
  WHERE ARCH.THREAD# = APPL.THREAD# ORDER BY 1;

  2    3    4    5    6    7    8    9   10   11

    Thread Last Sequence Received Last Sequence Applied Difference

---------- ---------------------- --------------------- ----------

         1                  72153                 72153          0

         2                  67021                 67021          0




SQL>

 

I hope this helps you!!!

 

Andre Luiz Dutra Ontalba 
 

Disclaimer: “The postings on this site are my own and don’t necessarily represent may actual employer positions, strategies or opinions. The information here was edited  to be useful for general purpose, specific data and identifications was removed to allow reach generic audience and to be useful.”