[20171219]expdp备份一致性问题.txt

[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条,说明日志已经应用.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/267265/viewspace-2148877/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/267265/viewspace-2148877/