Mục đích: Vào giờ hành chính khi export trên database Production sẽ gây CAO TẢI, đôi lúc TREO DB nên cần export ở Physical Standby, tuy nhiên khi export tại đây sẽ báo lỗi không cho export trên database READ ONLY

Giải pháp: Export trên 1 database khác có kết nối database link đến physical standby sử dụng NETWORK_LINK trong câu lệnh export

Chi tiết:

1. Check standby database:

SQL> select database_name,DATABASE_ROLE,open_mode from v$database;

DATABASE_NAME DATABASE_ROLE OPEN_MODE
-------------- ---------------- --------------------
SALEPROD PHYSICAL STANDBY READ ONLY WITH APPLY

2. Tạo database link trên standby database TRỎ TỚI STANDBY DATABASE:

SQL> create public database link LINK_EXPDP connect to BINHTV identified by Binhtv$123 using '(DESCRIPTION=
    (LOAD_BALANCE=yes)
    (ADDRESS_LIST=
      (ADDRESS=
        (PROTOCOL=TCP)
        (HOST=192.168.1.58)
        (PORT=1521)
      )
      (ADDRESS=
        (PROTOCOL=TCP)
        (HOST=192.168.1.63)
(PORT=1521)
      )
    )
    (CONNECT_DATA=
      (SERVER=dedicated)
      (SERVICE_NAME=SALEDR)
    )
  )';

Database link created.


SQL> select sysdate from dual@LINK_EXPDP;

SYSDATE
---------
30-JAN-21  

3. Tạo thư mục lưu file export trên máy chủ non-standby:

create directory ERIC_DUMP as '/dumparea/dump/BINH_DUMP'

4. Chạy expdp trên máy chủ non-standby database:

expdp directory=BINH_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 2021  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 2021 elapsed 0 00:07:14

Hy vọng hữu ích cho bạn.

=============================