在ADG只读备库使用数据泵导出数据

实际场景中存在某些需求，客户害怕在主库使用数据泵导出数据会对主库的运行造成影响，所以要求从备库导出数据。但数据泵无法直接从只读备库进行导出，需要借助一个中间库来实现。

针对在ADG只读备库环境无法使用数据泵进行导出的问题，现将解决方法整理成册，方便后续使用。参考MOS文档在片尾。

一、操作过程

2.1 ADG备库端

2.1.1 查看备库状态

select open_mode from v$database;

OPEN_MODE

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

READ ONLY

2.1.2 在备库端新建单实例数据库（expdb）用来导出

虽然主库建dblink也可以，但是传输是通过数据中心外面网络了。

2.1.3 在新建数据库中创建dblink连接备库

create database link expdp_standby connect to system identified by <password> using ‘standby_database’;

测试连接：

select db_unique_name from v$database@expdp_standby;

2.1.4 在新建数据库中创建directory目录（用来导出数据）

create directory datapump as ‘/tmp’;

2.1.5 在备库端执行导出命令

切换实例到刚刚新建的expdb数据库

export ORACLE_SID=expdb

执行导出命令：采用network_link参数用dblink的方式导出

expdp system/<password> directory=datapump network_link=expdp_standby full=y dumpfile=standby_database.dmp logfile=standby_database.log

三、附件

参考来源：MOS文档Doc ID 1356592.1

https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=246551496499405&id=1356592.1&_afrWindowMode=0&_adf.ctrl-state=2tnu12ysc_4

Prerequisites

It's important to know that Data Pump Export (expdp) cannot be executed directly on the Physical Standby database. This is due to the fact that Data Pump Export needs to create and maintain a Master Table which requires that a database would be open in "READ WRITE" mode. Therefore it is necessary to connect from a "non-Standby" database (which will maintain the Master Table) to the Physical Standby database using parameter NETWORK_LINK.

The NETWORK_LINK parameter initiates an export by using a valid database link. This means that the system to which the expdp client is connected contacts the Physical Standby database referenced by the source_database_link, retrieves data from it, and writes the data to a dump file set back on the connected system.

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

SQL> select instance_name, status from v$instance;

INSTANCE_NAME    STATUS
---------------- ------------
<STDBY_DB_NAME>  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
---------------- ------------
<STDBY_DB_NAME>  OPEN


SQL> select open_mode from v$database;

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

• "Non Standby" Database

-- create DB Link, Oracle Directory and test it

SQL> create database link expdp_primary connect to system identified by <password> using ‘standby_database’;

SQL> select db_unique_name from v$database;

SQL> select db_unique_name from v$database@expdp_primary;

SQL> create directory datapump as ‘/tmp’;


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

expdp system/<password> directory=datapump network_link=expdp_primary full=y dumpfile=standby_database.dmp logfile=standby_database.log