Shulou(Shulou.com)06/01 Report--

1. Consistent export

In OGG synchronized projects, there is always a failure of silent table synchronization that requires table-level initialization. At this time, using the data pump to import and export needs to consider the problem of data consistency, and make sure that the data we export is based on a certain scn or a certain timestamp, so that the stopped replication process (replicat) knows where to start to append database changes.

The data pump in 12c provides us with two consistent export parameters, one is FLASHBACK_SCN based on scn, and the other is FLASHBACK_TIME based on timestamp.

Scott@clonepdb_plugPDB > select * from test

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO 7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20 7499 ALLEN SALESMAN 7698 1981-02-20 00:00:00 1600 300 30 7521 WARD SALESMAN 7698 1981-02-22 00:00:00 1250 7566 7566 JONES MANAGER 7839 1981-04-02 00:00:00 2975 20 7654 MARTIN SALESMAN 7698 1981-09-28 00:00:00 1250 1400 30 7698 BLAKE MANAGER 78391981-05-01 00:00:00 2850 30 7782 CLARK MANAGER 7839 1981-06-09 00:00:00 2450 10 7839 KING PRESIDENT 1981-11-17 00:00:00 5000 10 7844 TURNER SALESMAN 7698 1981-09-08 00:00:00 1500 0 30 7900 JAMES CLERK 7698 2450 12-03 00:00:00 950 30 7902 FORD ANALYST 7566 1981-12-03 00:00:00 3000 20 EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO 7934 MILLER CLERK 7782 1982-01-23 00:00:00 1300 10

12 rows selected.

Elapsed: 00:00:00.03

Scott@clonepdb_plugPDB > select to_char (sysdate, 'yyyy-mm-dd hh34:mi:ss') time, to_char (dbms_flashback.get_system_change_number) scn from dual

TIME SCN

2018-01-23 10:15:22 7069818

Elapsed: 00:00:00.01

Scott@clonepdb_plugPDB > truncate table test

Table truncated.

Elapsed: 00:00:00.18

Scott@clonepdb_plugPDB > select * from test

No rows selected

Elapsed: 00:00:00.01

C:\ Users\ Administrator > expdp scott/tiger@clonepdb_plug dumpfile=systemdmp:expdp

.dmp logfile=systemdmp:expdp.log REUSE_DUMPFILES=y tables=test flashback_scn=70

72121

Export: Release 12.2.0.1.0-Production on Tuesday January 23 10:20:01 2018

Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.

Connect to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0-64bit Produc

Tion

Start "SCOTT". "SYS_EXPORT_TABLE_01": scott/****@clonepdb_plug dumpfile=syste

Mdmp:expdp.dmp logfile=systemdmp:expdp.log REUSE_DUMPFILES=y tables=test flashba

Ck_scn=7072121

Dealing with object type TABLE_EXPORT/TABLE/TABLE_DATA

Dealing with object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

Dealing with object type TABLE_EXPORT/TABLE/STATISTICS/MARKER

Dealing with object type TABLE_EXPORT/TABLE/TABLE

ORA-31693: the table data object "SCOTT". "TEST" cannot be loaded / unloaded and is skipped with the following error:

ORA-02354: error exporting / importing data

ORA-01466: unable to read data-table definition changed

C:\ Users\ Administrator > expdp scott/tiger@clonepdb_plug dumpfile=systemdmp:expdp

.dmp logfile=systemdmp:expdp.log REUSE_DUMPFILES=y tables=test flashback_scn=70

74190

Export: Release 12.2.0.1.0-Production on Tuesday January 23 10:22:29 2018

Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.

Connect to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0-64bit Produc

Tion

Start "SCOTT". "SYS_EXPORT_TABLE_01": scott/****@clonepdb_plug dumpfile=syste

Mdmp:expdp.dmp logfile=systemdmp:expdp.log REUSE_DUMPFILES=y tables=test flashba

Ck_scn=7074190

Dealing with object type TABLE_EXPORT/TABLE/TABLE_DATA

Dealing with object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

Dealing with object type TABLE_EXPORT/TABLE/STATISTICS/MARKER

Dealing with object type TABLE_EXPORT/TABLE/TABLE

. . Exported "SCOTT". "TEST" 8.695 KB 12 lines

The primary table "SCOTT". "SYS_EXPORT_TABLE_01" has been successfully loaded / unloaded

The dump file set for SCOTT.SYS_EXPORT_TABLE_01 is:

C:\ ORACLEBACK\ DATAPUMP\ EXPDP.DMP

Assignment "SCOTT". "SYS_EXPORT_TABLE_01" on Tuesday January 23 10:22:40 2018 elapsed

Completed successfully at 00:11

Truncate table cannot be used, del can be used, update can be used, and insert can be used.

two。 Clone a user

Export everything from schema to HR

C:\ Users\ Administrator > expdp system/xxxx@pdbtest logfile=systemdmp:expd

P.log REUSE_DUMPFILES=y DUMPFILE=systemdmp:tablespace.dmp schemas=hr

Export: Release 12.2.0.1.0-Production on Tuesday January 23 10:39:03 2018

Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.

Connect to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0-64bit Produc

Tion

FLASHBACK is automatically enabled to maintain database integrity.

Start "SYSTEM". "SYS_EXPORT_SCHEMA_01": system/****@pdbtest logfile=systemdmp

: expdp.log REUSE_DUMPFILES=y DUMPFILE=systemdmp:tablespace.dmp schemas=hr

Dealing with object type SCHEMA_EXPORT/TABLE/TABLE_DATA

Dealing with object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS

Dealing with object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

Dealing with object type SCHEMA_EXPORT/STATISTICS/MARKER

Dealing with object type SCHEMA_EXPORT/USER

Dealing with object type SCHEMA_EXPORT/SYSTEM_GRANT

Dealing with object type SCHEMA_EXPORT/ROLE_GRANT

Dealing with object type SCHEMA_EXPORT/DEFAULT_ROLE

Dealing with object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA

Dealing with object type SCHEMA_EXPORT/SYNONYM/SYNONYM

Dealing with object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE

Dealing with object type SCHEMA_EXPORT/TABLE/TABLE

Dealing with object type SCHEMA_EXPORT/TABLE/COMMENT

Dealing with object type SCHEMA_EXPORT/FUNCTION/FUNCTION

Dealing with object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE

Dealing with object type SCHEMA_EXPORT/FUNCTION/ALTER_FUNCTION

Dealing with object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE

Dealing with object type SCHEMA_EXPORT/VIEW/VIEW

Dealing with object type SCHEMA_EXPORT/TABLE/INDEX/INDEX

Dealing with object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT

Dealing with object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT

Dealing with object type SCHEMA_EXPORT/TABLE/TRIGGER

. . Exported "HR". "EMPLOYEES" 17.08 KB 107lines

. . Exported "HR". "EMPLOYEES_TEST" 17.09 KB 107lines

. . Exported "HR". "LOCATIONS" 8.429 KB 23 lines

. . Exported "HR". "JOB_HISTORY" 7.187 KB 10 lines

. . Exported "HR". "JOBS" 7.101 KB 19 lines

. . Exported "HR". "DEPARTMENTS" 7.117 KB 27 lines

. . Exported "HR". "COUNTRIES" 6.359 KB 25 lines

. . Exported "HR". "REGIONS" 5.539 KB 4 lines

. . Exported "HR". "INVISIBLE_T" 5.914 KB 1 line

The primary table "SYSTEM". "SYS_EXPORT_SCHEMA_01" has been successfully loaded / unloaded

The dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:

C:\ ORACLEBACK\ DATAPUMP\ TABLESPACE.DMP

Assignment "SYSTEM". "SYS_EXPORT_SCHEMA_01" on Tuesday January 23 10:40:09 2018 elapsed 0

Completed successfully at 00:00:58

Import everything whose schema is HR, and if there is no hr data pump in the database at this time, it will be created automatically.

C:\ Users\ Administrator > impdp system/****@clonepdb_plug logfile=systemdm

P:expdp.log DUMPFILE=systemdmp:tablespace.dmp remap_schema=hr:hr

Import: Release 12.2.0.1.0-Production on Tuesday January 23 10:43:30 2018

Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.

Connect to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0-64bit Produc

Tion

The primary table "SYSTEM". "SYS_IMPORT_FULL_01" has been successfully loaded / unloaded

Start "SYSTEM". "SYS_IMPORT_FULL_01": system/****@clonepdb_plug logfile=syste

Mdmp:expdp.log DUMPFILE=systemdmp:tablespace.dmp remap_schema=hr:hr

Dealing with object type SCHEMA_EXPORT/USER

Dealing with object type SCHEMA_EXPORT/SYSTEM_GRANT

Dealing with object type SCHEMA_EXPORT/ROLE_GRANT

Dealing with object type SCHEMA_EXPORT/DEFAULT_ROLE

Dealing with object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA

Dealing with object type SCHEMA_EXPORT/SYNONYM/SYNONYM

Dealing with object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE

Dealing with object type SCHEMA_EXPORT/TABLE/TABLE

Dealing with object type SCHEMA_EXPORT/TABLE/TABLE_DATA

. . Imported "HR". "EMPLOYEES" 17.08 KB 107lines

. . Imported "HR". "EMPLOYEES_TEST" 17.09 KB 107lines

. . Imported "HR". "LOCATIONS" 8.429 KB 23 lines

. . Imported "HR". "JOB_HISTORY" 7.187 KB 10 lines

. . Imported "HR". "JOBS" 7.101 KB 19 lines

. . Imported "HR". "DEPARTMENTS" 7.117 KB 27 lines

. . Imported "HR". "COUNTRIES" 6.359 KB 25 lines

. . Imported "HR". "REGIONS" 5.539 KB 4 lines

. . Imported "HR". "INVISIBLE_T" 5.914 KB 1 line

Dealing with object type SCHEMA_EXPORT/TABLE/COMMENT

Dealing with object type SCHEMA_EXPORT/FUNCTION/FUNCTION

Dealing with object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE

Dealing with object type SCHEMA_EXPORT/FUNCTION/ALTER_FUNCTION

Dealing with object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE

Dealing with object type SCHEMA_EXPORT/VIEW/VIEW

Dealing with object type SCHEMA_EXPORT/TABLE/INDEX/INDEX

Dealing with object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT

Dealing with object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS

Dealing with object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT

Dealing with object type SCHEMA_EXPORT/TABLE/TRIGGER

Dealing with object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

Dealing with object type SCHEMA_EXPORT/STATISTICS/MARKER

Assignment "SYSTEM". "SYS_IMPORT_FULL_01" on Tuesday January 23 10:44:01 2018 elapsed

Completed successfully at 00:25

Sys@pdbtestPDB > create directory systemdmp as'C:\ oracleback\ datapump'

2

Directory created.

Elapsed: 00:00:00.83

Sys@pdbtestPDB > conn hr/hr@clonepdb_plug

Connected.

Hr@clonepdb_plugPDB > select count (*) from jobs

COUNT (*) 19

Elapsed: 00:00:00.01

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.