[20200309]expdp 與read only.txt

[20200309]expdp 與read only.txt

--//連結：http://www.itpub.net/thread-2128382-1-1.html問的問題。
--//請教各位大佬，在dataguard的read only模式下，不做轉換，如何使用expdp匯出資料？

--//在本地read only的情況下，不能使用expdp匯出資料的。。

1.環境：
SYS@bookdg> select open_mode from v$database ;
OPEN_MODE
--------------------
READ ONLY WITH APPLY

$ expdp scott/book tables=T
Export: Release 11.2.0.4.0 - Production on Mon Mar 9 10:09:15 2020
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORA-31626: job does not exist
ORA-31633: unable to create master table "SCOTT.SYS_EXPORT_TABLE_05"
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPV$FT", line 1038
ORA-16000: database open for read-only access
--//無法建立master table SCOTT.SYS_EXPORT_TABLE_05.

2.可以找一臺讀寫的機器，透過dblink實現到處，甚至匯入。

CREATE PUBLIC DATABASE LINK TEST040
 CONNECT TO SCOTT
 IDENTIFIED BY book
 USING 'bookdg';

SYS@book> select sysdate from dual@test040;
SYSDATE
-------------------
2020-03-09 10:14:23

SYS@book> alter system set log_archive_dest_state_2=defer scope=memory;
System altered.

SYS@book> alter system archive log current ;
System altered.

SCOTT@book> insert into t  select * from t where rownum=1;
1 row created.

SCOTT@book> commit ;
Commit complete.

SCOTT@book> select count(*) from t;
COUNT(*)
----------
     85034

$ expdp scott/book NETWORK_LINK=test040  tables=T  DUMPFILE=t.dmp
Export: Release 11.2.0.4.0 - Production on Mon Mar 9 10:21:10 2020
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SCOTT"."SYS_EXPORT_TABLE_01":  scott/a*** NETWORK_LINK=test040 tables=T DUMPFILE=t.dmp
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 10 MB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "SCOTT"."T"                                 8.244 MB   85033 rows
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
--//我在主庫匯出，你可以發現我停止日誌應用，備庫少1條記錄。
Master table "SCOTT"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.SYS_EXPORT_TABLE_01 is:
  /u01/app/oracle/admin/book/dpdump/t.dmp
Job "SCOTT"."SYS_EXPORT_TABLE_01" successfully completed at Mon Mar 9 10:21:16 2020 elapsed 0 00:00:05
--//OK,匯出成功。

3.你還可以直接匯入：

$ impdp scott/book NETWORK_LINK=test040  tables=T  REMAP_TABLE=scott.T:scott.T1
Import: Release 11.2.0.4.0 - Production on Mon Mar 9 10:24:12 2020
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SCOTT"."SYS_IMPORT_TABLE_01":  scott/a* NETWORK_LINK=test040 tables=T REMAP_TABLE=scott.T:scott.T1
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 10 MB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . imported "SCOTT"."SCOTT.T1"                           85033 rows
Job "SCOTT"."SYS_IMPORT_TABLE_01" successfully completed at Mon Mar 9 10:24:18 2020 elapsed 0 00:00:06
--//昏，語法有問題，不過還是匯入成功了。不需要在後面schema。

SYS@book> select count(*) from scott."SCOTT.T1";
  COUNT(*)
----------
     85033

SYS@book> select count(*) from SCOTT.T;
  COUNT(*)
----------
     85034