[20171219]expdp备份一致性问题.txt
--//别人问的问题,如何使用expdp备份保持相关数据的一直性问题,实际上就是导出使用参数FLASHBACK_SCN,FLASHBACK_TIME.
--//但是如果备份时间很长,很难满足需求.因为要回滚一些信息,可能会报ora-01555错误.
--//实际上还有一种方式利用dg(特别是11G,可以支持只读应用),停止日志应用来完成这个工作.执行expdp,完成后在继续日志应用.
1.环境:
主库: 192.168.100.78 db_unique_name=book
备库: 192.168.100.40 db_unique_name=bookdg
SYS@book> @ &r/ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
--//备库打开READ ONLY WITH APPLY.
SYS@bookdg> select open_mode from v$database;
OPEN_MODE
--------------------
READ ONLY WITH APPLY
--//注:你不能直接在备库执行expdp,因为你无法只读的数据库建立master table,一般命令SYS_EXPORT_SCHEMA_0N.
--//在主库建立db_link:
CREATE PUBLIC DATABASE LINK BOOK40
CONNECT TO SCOTT
IDENTIFIED BY <PWD>
USING '192.168.100.40:1521/bookdg';
--//注意不推荐直接使用数字作为db_link name,例子(假如定义dblink name=40):
SCOTT@book> select sysdate from dual@40;
select sysdate from dual@40
*
ERROR at line 1:
ORA-01729: database link name expected
SCOTT@book> select sysdate from dual@"40";
SYSDATE
-------------------
2017-12-19 15:35:51
--//测试:
SCOTT@book> select sysdate from dual@book40;
SYSDATE
-------------------
2017-12-19 15:37:55
2.导出测试:
--//在备库停止日志应用:
SYS@bookdg> alter database recover managed standby database cancel ;
Database altered.
SYS@bookdg> @ &r/dg/dg
PROCESS PID STATUS CLIENT_P GROUP# THREAD# SEQUENCE# BLOCK# BLOCKS DELAY_MINS
--------- ------- ------------ -------- ------ ------- ---------- ---------- ---------- ----------
RFS 16625 IDLE ARCH N/A 0 0 0 0 0
RFS 16615 IDLE UNKNOWN N/A 0 0 0 0 0
RFS 16613 IDLE UNKNOWN N/A 0 0 0 0 0
RFS 16623 IDLE LGWR 1 1 749 50066 1 0
ARCH 23659 CLOSING ARCH 4 1 747 1 15 0
ARCH 23665 CLOSING ARCH 4 1 735 2048 1292 0
ARCH 23663 CLOSING ARCH 5 1 748 1 120 0
ARCH 23661 CLOSING ARCH 7 1 698 1 1997 0
8 rows selected.
--//日志应用已经停止,但是主库的日志还是不断接收过来,只不过不应用.
--//在主库执行一些dml操作.
SCOTT@book> select count(*) from t1;
COUNT(*)
----------
31
SCOTT@book> delete from t1 where a=3;
10 rows deleted.
SCOTT@book> commit ;
Commit complete.
SCOTT@book> select count(*) from t1;
COUNT(*)
----------
21
--//在主库执行,这里由于机器限制,实际上可以在任意机器上做这个操作,建立好db_link就ok了.
$ expdp system/oracle DUMPFILE=scott.dp SCHEMAS=scott NETWORK_LINK=book40
Export: Release 11.2.0.4.0 - Production on Tue Dec 19 15:45:25 2017
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
UDE-28002: operation generated ORACLE error 28002
ORA-28002: the password will expire within 7 days
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 "SYSTEM"."SYS_EXPORT_SCHEMA_01": system/a**** DUMPFILE=scott.dp SCHEMAS=scott NETWORK_LINK=book40
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 1.187 MB
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/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/AUDIT_OBJ
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/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/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/JAVA_SOURCE/JAVA_SOURCE
Processing object type SCHEMA_EXPORT/JAVA_CLASS/JAVA_CLASS
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "SCOTT"."SESSION_WAIT_RECORD" 386.7 KB 8122 rows
. . exported "SCOTT"."LOCK_OBJECT_RECORD" 308.4 KB 8122 rows
. . exported "SCOTT"."DEPT" 5.929 KB 4 rows
. . exported "SCOTT"."EMP" 8.562 KB 14 rows
. . exported "SCOTT"."SALGRADE" 5.859 KB 5 rows
. . exported "SCOTT"."T1" 6.648 KB 31 rows
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
. . exported "SCOTT"."BONUS" 0 KB 0 rows
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
/u01/app/oracle/admin/book/dpdump/scott.dp
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at Tue Dec 19 15:46:15 2017 elapsed 0 00:00:48
--//注意看下划线,可以发现T1表导出31行.
3.在备库开启应用:
SYS@bookdg> alter database recover managed standby database using current logfile disconnect ;
Database altered.
SYS@bookdg> select count(*) from scott.t1;
COUNT(*)
----------
21
--//已经21条,说明日志已经应用.