當前位置：首頁 > 编程资源 > 编程问答 >内容正文

编程问答

dg备库怎么逻辑导出exp/expdp?

發布時間：2023/12/14 编程问答 37 豆豆

生活随笔收集整理的這篇文章主要介紹了 dg备库怎么逻辑导出exp/expdp? 小編覺得挺不錯的,現在分享給大家,幫大家做個參考.

以下技能在備庫部署ogg初始化時可用到。

exp可直接執行:

exp jyc/jyc@pdbdgorcl buffer=409600000 file=dg_jyc.dmp log=dg_jyc.log owner=jyc

[oracle@dgrac2 ~]$ exp jyc/jyc@pdbdgorcl buffer=409600000 file=dg_jyc.dmp log=dg_jyc.log owner=jyc

Export: Release 19.0.0.0.0 - Production on Wed Feb 16 09:52:56 2022
Version 19.5.0.0.0

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.5.0.0.0
Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set

About to export specified users ...
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user JYC?
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user JYC?
About to export JYC's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export JYC's tables via Conventional Path ...
. . exporting table ? ? ? ? ? ? ? ? ? ? ? ? ? TEST ? ? ? ? ?2 rows exported
. exporting synonyms
. exporting views
. exporting stored procedures
. exporting operators
. exporting referential integrity constraints
. exporting triggers
. exporting indextypes
. exporting bitmap, functional and extensible indexes
. exporting posttables actions
. exporting materialized views
. exporting snapshot logs
. exporting job queues
. exporting refresh groups and children
. exporting dimensions
. exporting post-schema procedural objects and actions
. exporting statistics
Export terminated successfully without warnings.

expdp需通過主庫或其它庫的network_link方式導出

expdp jyc/jyc@pdborcl network_link=expdp_primary directory=dmp schemas=JYC dumpfile=dg_jyc.dmp logfile=dg_jyc.log cluster=n exclude=statistics

備庫操作報錯如下：

[oracle@dgrac1 trace]$ expdp jyc/jyc@pdbdgorcl directory=dmp schemas=JYC dumpfile=dg_jyc.dmp logfile=dg_jyc.log cluster=n exclude=statistics

Export: Release 19.0.0.0.0 - Production on Wed Feb 16 09:42:03 2022
Version 19.5.0.0.0

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
ORA-31626: job does not exist
ORA-31633: unable to create master table "JYC.SYS_EXPORT_SCHEMA_05"
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPV$FT", line 1163
ORA-16000: database or pluggable database open for read-only access
ORA-06512: at "SYS.KUPV$FT", line 1056
ORA-06512: at "SYS.KUPV$FT", line 1044

主庫操作：（主備庫先創建好dmp目錄）

[oracle@rac1 ~]$ sqlplus jyc/jyc@pdborcl

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Feb 16 09:32:25 2022
Version 19.5.0.0.0

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.5.0.0.0

SQL> select table_name from user_tables;

no rows selected

SQL> create table test (id int);

Table created.

SQL> insert into test values(1);

1 row created.

SQL> insert into test values(2);

1 row created.

SQL> commit;

Commit complete.

SQL> set line 130
SQL> set wrap off
SQL> col DIRECTORY_NAME for a30
SQL> select * from dba_directories;
rows will be truncated

OWNER DIRECTORY_NAME ? ? ? ? ? ? ? ? DIRECTORY_PATH
----- ------------------------------ ---------------------------------------------------------------------------------------------
SYS ? SDO_DIR_WORK
SYS ? SDO_DIR_ADMIN ? ? ? ? ? ? ? ? ?/oracle/app/oracle/product/19c/dbhome_1/md/admin
SYS ? XMLDIR ? ? ? ? ? ? ? ? ? ? ? ? /oracle/app/oracle/product/19c/dbhome_1/rdbms/xml
SYS ? XSDDIR ? ? ? ? ? ? ? ? ? ? ? ? /oracle/app/oracle/product/19c/dbhome_1/rdbms/xml/schema
SYS ? OPATCH_INST_DIR ? ? ? ? ? ? ? ?/oracle/app/oracle/product/19c/dbhome_1/OPatch
SYS ? ORACLE_OCM_CONFIG_DIR2 ? ? ? ? /oracle/app/oracle/product/19c/dbhome_1/ccr/state
SYS ? ORACLE_BASE ? ? ? ? ? ? ? ? ? ?/oracle/app/oracle
SYS ? ORACLE_HOME ? ? ? ? ? ? ? ? ? ?/oracle/app/oracle/product/19c/dbhome_1
SYS ? ORACLE_OCM_CONFIG_DIR ? ? ? ? ?/oracle/app/oracle/product/19c/dbhome_1/ccr/state
SYS ? DATA_PUMP_DIR ? ? ? ? ? ? ? ? ?/oracle/app/oracle/product/19c/dbhome_1/rdbms/log/A0044C1C44692EE6E0539634A8C0A37C
SYS ? DBMS_OPTIM_LOGDIR ? ? ? ? ? ? ?/oracle/app/oracle/product/19c/dbhome_1/cfgtoollogs

15 rows selected.

SQL> !mkdir -p /oracle/app/oracle/dmp

SQL> create directory dmp as '/oracle/app/oracle/dmp';
grant read,write on directory dmp to public;

Directory created.

SQL>?
Grant succeeded.

此處開始備庫操作：先取消同步，這樣備庫數據不變情況下導出對比主庫新增記錄的情況：

[oracle@dgrac1 trace]$ dgmgrl sys/oracle@dgorcl
DGMGRL for Linux: Release 19.0.0.0.0 - Production on Wed Feb 16 09:43:20 2022
Version 19.5.0.0.0

Welcome to DGMGRL, type "help" for information.
Connected to "dgorcl"
Connected as SYSDBA.

DGMGRL> show CONFIGURATION;

Configuration - adg

? Protection Mode: MaxPerformance
? Members:
? orcl ? - Primary database
? ? dgorcl - Physical standby database?

Fast-Start Failover: ?Disabled

Configuration Status:
SUCCESS ? (status updated 11 seconds ago)

DGMGRL> show database verbose dgorcl

Database - dgorcl

? Role: ? ? ? ? ? ? ? PHYSICAL STANDBY
? Intended State: ? ? APPLY-ON
? Transport Lag: ? ? ?0 seconds (computed 0 seconds ago)
? Apply Lag: ? ? ? ? ?0 seconds (computed 0 seconds ago)
? Average Apply Rate: 42.00 KByte/s
? Active Apply Rate: ?1.45 MByte/s
? Maximum Apply Rate: 1.63 MByte/s
? Real Time Query: ? ?ON
? Instance(s):
? ? orcl1
? ? orcl2 (apply instance)

? Properties:
? ? DGConnectIdentifier ? ? ? ? ? ? = 'dgorcl'
? ? ObserverConnectIdentifier ? ? ? = ''
? ? FastStartFailoverTarget ? ? ? ? = ''
? ? PreferredObserverHosts ? ? ? ? ?= ''
? ? LogShipping ? ? ? ? ? ? ? ? ? ? = 'ON'
? ? RedoRoutes ? ? ? ? ? ? ? ? ? ? ?= ''
? ? LogXptMode ? ? ? ? ? ? ? ? ? ? ?= 'ASYNC'
? ? DelayMins ? ? ? ? ? ? ? ? ? ? ? = '0'
? ? Binding ? ? ? ? ? ? ? ? ? ? ? ? = 'optional'
? ? MaxFailure ? ? ? ? ? ? ? ? ? ? ?= '0'
? ? ReopenSecs ? ? ? ? ? ? ? ? ? ? ?= '300'
? ? NetTimeout ? ? ? ? ? ? ? ? ? ? ?= '30'
? ? RedoCompression ? ? ? ? ? ? ? ? = 'DISABLE'
? ? PreferredApplyInstance ? ? ? ? ?= ''
? ? ApplyInstanceTimeout ? ? ? ? ? ?= '0'
? ? ApplyLagThreshold ? ? ? ? ? ? ? = '30'
? ? TransportLagThreshold ? ? ? ? ? = '30'
? ? TransportDisconnectedThreshold ?= '30'
? ? ApplyParallel ? ? ? ? ? ? ? ? ? = 'AUTO'
? ? ApplyInstances ? ? ? ? ? ? ? ? ?= '0'
? ? StandbyFileManagement ? ? ? ? ? = ''
? ? ArchiveLagTarget ? ? ? ? ? ? ? ?= '0'
? ? LogArchiveMaxProcesses ? ? ? ? ?= '0'
? ? LogArchiveMinSucceedDest ? ? ? ?= '0'
? ? DataGuardSyncLatency ? ? ? ? ? ?= '0'
? ? LogArchiveTrace ? ? ? ? ? ? ? ? = '0'
? ? LogArchiveFormat ? ? ? ? ? ? ? ?= ''
? ? DbFileNameConvert ? ? ? ? ? ? ? = ''
? ? LogFileNameConvert ? ? ? ? ? ? ?= ''
? ? ArchiveLocation ? ? ? ? ? ? ? ? = ''
? ? AlternateLocation ? ? ? ? ? ? ? = ''
? ? StandbyArchiveLocation ? ? ? ? ?= ''
? ? StandbyAlternateLocation ? ? ? ?= ''
? ? InconsistentProperties ? ? ? ? ?= '(monitor)'
? ? InconsistentLogXptProps ? ? ? ? = '(monitor)'
? ? LogXptStatus ? ? ? ? ? ? ? ? ? ?= '(monitor)'
? ? SendQEntries ? ? ? ? ? ? ? ? ? ?= '(monitor)'
? ? RecvQEntries ? ? ? ? ? ? ? ? ? ?= '(monitor)'
? ? HostName(*)
? ? StaticConnectIdentifier(*)
? ? TopWaitEvents(*)
? ? SidName(*)
? ? (*) - Please check specific instance for the property value

? Log file locations(*):
? ? (*) - Check specific instance for log file locations.

Database Status:
SUCCESS

DGMGRL> edit database 'dgorcl' set state='apply-off';
Succeeded.
DGMGRL> show database verbose dgorcl

Database - dgorcl

? Role: ? ? ? ? ? ? ? PHYSICAL STANDBY
? Intended State: ? ? APPLY-OFF
? Transport Lag: ? ? ?0 seconds (computed 1 second ago)
? Apply Lag: ? ? ? ? ?12 seconds (computed 1 second ago)
? Average Apply Rate: (unknown)
? Active Apply Rate: ?(unknown)
? Maximum Apply Rate: (unknown)
? Real Time Query: ? ?OFF
? Instance(s):
? ? orcl1
? ? orcl2 (apply instance)

? Log file locations(*):
? ? (*) - Check specific instance for log file locations.

Database Status:
SUCCESS

DGMGRL> show database dgorcl

Database - dgorcl

? Role: ? ? ? ? ? ? ? PHYSICAL STANDBY
? Intended State: ? ? APPLY-OFF
? Transport Lag: ? ? ?0 seconds (computed 0 seconds ago)
? Apply Lag: ? ? ? ? ?24 seconds (computed 0 seconds ago)
? Average Apply Rate: (unknown)
? Real Time Query: ? ?OFF
? Instance(s):
? ? orcl1
? ? orcl2 (apply instance)

Database Status:
SUCCESS

DGMGRL>?

此處開始主庫操作：主庫增加3條記錄，這樣主庫是5條，備庫是2條

SQL>?
SQL> insert into test values(3);

1 row created.

SQL> insert into test values(4);

1 row created.

SQL> insert into test values(5);

1 row created.

SQL> commit;

Commit complete.

SQL> create database link expdp_primary connect to jyc identified by jyc using 'pdbdgorcl';

Database link created.

SQL> select db_unique_name from v$database;

DB_UNIQUE_NAME
------------------------------
orcl

SQL> select db_unique_name from v$database@expdp_primary;

DB_UNIQUE_NAME
------------------------------
dgorcl

SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.5.0.0.0
[oracle@rac1 ~]$ expdp jyc/jyc@pdborcl network_link=expdp_primary directory=dmp schemas=JYC dumpfile=dg_jyc.dmp logfile=dg_jyc.log cluster=n exclude=statistics

Export: Release 19.0.0.0.0 - Production on Wed Feb 16 09:49:45 2022
Version 19.5.0.0.0

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
FLASHBACK automatically enabled to preserve database integrity.
Starting "JYC"."SYS_EXPORT_SCHEMA_01": ?jyc/********@pdborcl network_link=expdp_primary directory=dmp schemas=JYC dumpfile=dg_jyc.dmp logfile=dg_jyc.log cluster=n exclude=statistics?
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
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/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
. . exported "JYC"."TEST" ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?5.5 KB ? ? ? 2 rows
Master table "JYC"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for JYC.SYS_EXPORT_SCHEMA_01 is:
? /oracle/app/oracle/dmp/dg_jyc.dmp
Job "JYC"."SYS_EXPORT_SCHEMA_01" successfully completed at Wed Feb 16 09:50:33 2022 elapsed 0 00:00:39

[oracle@rac1 ~]$ expdp jyc/jyc@pdborcl directory=dmp schemas=JYC dumpfile=pri_jyc.dmp logfile=pri_jyc.log cluster=n exclude=statistics

Export: Release 19.0.0.0.0 - Production on Wed Feb 16 09:50:58 2022
Version 19.5.0.0.0

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
FLASHBACK automatically enabled to preserve database integrity.
Starting "JYC"."SYS_EXPORT_SCHEMA_01": ?jyc/********@pdborcl directory=dmp schemas=JYC dumpfile=pri_jyc.dmp logfile=pri_jyc.log cluster=n exclude=statistics?
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
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/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/DB_LINK
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
. . exported "JYC"."TEST" ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?5.093 KB ? ? ? 5 rows
Master table "JYC"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for JYC.SYS_EXPORT_SCHEMA_01 is:
? /oracle/app/oracle/dmp/pri_jyc.dmp
Job "JYC"."SYS_EXPORT_SCHEMA_01" successfully completed at Wed Feb 16 09:51:54 2022 elapsed 0 00:00:48

相關參考：

【DG】怎么從Oracle備庫導出數據_ITPUB博客

總結

以上是生活随笔為你收集整理的dg备库怎么逻辑导出exp/expdp?的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。

上一篇：使用python把txt文件转为csv文
下一篇： python读取cad_SmartSof