oracle-Data Pump Real ____oracle

Overview the role of data pump:1. Logical backup and logical recovery 2. Move object 3 between database users. Move object 4 between databases. Implementing Tablespace Transfer characteristics of data pump and the difference between traditional export and import1.EXP and IMP are client segment tool programs, EXPDP and IMPDP are server-side tool programs 2.EXP and imp less efficient. EXPDP and IMPDP efficiency 3. Data pump features powerful parallel, filtering, conversion, compression, encryption, interaction, etc. 4. Data pump does not support the previous version of 9i, exp/imp in the short term or more applicable to 5. With EXP/IMP data pump export includes export table, export scheme, export table space, 4 ways to export a database.

Oracle Official Guidance Document requirements Description

198 The total amount of data under CC users of the test environment needs to be synchronized to 1971 parts of the quasi production environment Scenario Analysis

After the database version is 11.2 (greater than oracle10g) initially determine the way to use the data pump to import 197 database operation steps from 198 after export EXPDP

SQL window do the following 1. To create a logical directory

To create a logical directory

sql>  Create directory DIR_DP as '/oracle/oracle11 ';

2. View Table of Contents

also see if the operating system exists, if it does not exist, mkdir-p new directory or error

SELECT privilege, Directory_name, directory_path from
  User_tab_privs T, all_directories D
 WHERE t.table_name (+ = D.directory_name
 ORDER by 2, 1;

Or you can view

SELECT * from Dba_directories;

Data_pump_dir is the directly available dump directory, the export files and import files are placed on the above path, if not, you need to manually create and assign read/write permissions.

If there is no query, refresh the

Sql> Host Ls/oracle/oracle11

3. Give CC users the right to operate in the specified directory

Given by an administrator such as a DBA user

Sql>  Grant read,write on directory DIR_DP to CC;

4. Export Data

Oracle users using the host

Specify schema by user Guide/export:

Oracle@entel2:[/oracle] $EXPDP cc/password@//10.45.7.198:1521/cc schemas=cc DIRECTORY=DIR_DP dumpfile =expdp_ Test1.dmp Logfile=expdp_test1.log;


...........

Dump file set for CC. SYS_EXPORT_SCHEMA_01 is:
  /oracle/oracle11/expdp_test1.dmp
Job "CC". " Sys_export_schema_01 "successfully completed at Sat Aug 12:02:13 2016 elapsed 0 00:01:33

Parameter resolution: schemas: Database user alias instead of instance name CC/PASSWORD@//10.45.7.198:1521/CC port Specifies the instance name, the first user name and password SCP DMP to the target host

[Root@entel2 ~]# SCP expdp_198.dmp oracle@10.45.7.197:/oracle/oracle11

IMPDP Delete User

sql> DROP USER cc CASCADE;

Create a user

Sql>create USER cc identified by password
DEFAULT tablespace tab_cc;

Give permission

Sql>grant CONNECT, RESOURCE to CC;
Sql>grant all privileges to CC;
Sql>grant DBA to CC;

Importing with Oracle users the same schema

Oracle@entel1:[/oracle] $IMPDP cc/password@//10.45.7.197:1521/cc schemas=cc directory=dir_dp_198 dumpfile =expdp_ 198.dmp Logfile=expdp_198.log;

a different schema

Oracle@entel1:[/oracle] $IMPDP entelcc_st4/password@//10.45.7.197:1521/cc schemas=cc remap_schema=cc:entelcc_st4 directory=dir_dp_198 dumpfile =expdp_198.dmp Logfile=expdp_198.log;

Description: directory on the target host will also need to be established and given read and write access, and if not specified, the default directory is used for Data_pump_dir

--View the default directory
select * from dba_directories where directory_name= ' Data_pump_dir ';

Data_pump_dir This directory, is the system default directory, such as not a separate directory, DMP file will be here, but the default this directory other users do not have permissions. DMP files need to be stored in the corresponding directory directory, no person to import the error

If the schema of the target library is consistent with the schema of the source library, follow the above script, and the inconsistency needs to be specified and the syntax is as follows: Schemas=old_schema Remap_schema=old_schema:new_schema

No person complains:

Ora-39002:invalid Operation
Ora-39166:object Entelcc_st4 is not found.

Remap_schema, Remap_tablespace, Remap_datafile

In addition to Remap_schema, there are remap_tablespace, remap_datafile, etc.

1, Remap_schema
This option is used to load all objects of the source scheme into the target scenario: Remap_schema=source_schema:target_schema
2, Remap_tablespace
Import all objects from the source table space into the target tablespace: remap_tablespace=source_tablespace:target:tablespace
3, remap_datafile This option is used to convert the source data file name to the destination data file name and may be required to move the table space between different platforms.
Remap_datafiel=source_datafie:target_datafile dump files under different paths to import

Need to create multiple directory

Specify dumpfile=dir1:1.dmp,dir2:2.dm View Scripts

View the size of each schema footprint

Select Owner,round (SUM (bytes/1024/1024), 2) | | MB ' as schema_size from Dba_segments Group by owner ORDER by schema_size Desc;  

To query the number of objects that have already been imported

Select Owner,count (*) from dba_objects where owner like ' entelcc_st4% ' group by owner;  

Here the different schemas, which had not been noticed before, led to a constant error, and saw YFLENG2002 's blog Oracle schema-level data migration, praise one. Schema Interpretation

A schema is a collection of the database objects (used by a user.).
Schema objects are the logical structures that directly to the database ' s data.
A user is a name defined in the database that can connect to and Access objects.
Schemas and users help the database administrators manage database security.

A user typically corresponds to a schema that has a schema name equal to the user name and serves as the default schema for that user.

A schema cannot be created in an Oracle database, and to create a schema can only be resolved by creating a single user (although there is a CREATE SCHEMA statement in Oracle, it is not used for creating a schema). Create a user with a schem with the same name as the user's default shcema for that user.

That is, the number of schemas is the same as the number of user, and the schema name corresponds to the user name one by one, and all of the aliases we can call schema the user, although this is not accurate, is easier to understand.

A user has a default schema whose schema name equals the user name, and of course a user can use another schema. If we visit a table without specifying which schema the table belongs to, the system automatically adds the default Sheman name to the table. For example, when we visit the database, we visit the EMP table under Scott's user, through the select * from EMP; In fact, this SQL statement is fully written as SELECT * from Scott.emp. The full name of an object in the database is Schema.object, not user.object. Similarly, if we do not specify a schema for the object when we create the object, the schema of the object is the user's default schema. It's like a user has a default table space, but the user can also use other tablespace, if we do not specify the table space when we create the object, the object is stored in the default tablespace, and to have the object stored in another tablespace, we need to specify the table space for the object when we create the object.

The schema above explains what the schema in Oracle refers to. , thanks for sharing notes

EXPDP and IMPDP are service-side tools that can only be used on the Oracle server and cannot be used on the client

The low version is not able to import the high version dmp, and you need to specify the version number export when the EXPDP export is high. Low version IMPDP do not need to specify a version.
For example: 11.2.0.4 Import to 10.2.0.5

EXPDP Username/password Directory=dump_dir dumpfile=test.dmp version=10.2.0.5.0

Export Import to Whole library Entire Library export

Oracle@entel2:[/oracle] $EXPDP \ '/as Sysdba\ ' DIRECTORY=DIR_DP full=y dumpfile=fulldb.dmp parallel=2; "Start execution ..... The above order can also be added to the logfile and other discretionary "Export:release 11.2.0.4.0-production on Mon Oct 18:47:01 2016 Copyright (c) 1982, Ora  CLE 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, Da Ta Mining and real application testing options starting "SYS". Sys_export_full_01 ":"/******** as SYSDBA "DIRECTORY=DIR_DP full=y dumpfile=fulldb.dmp parallel=2 Estimate in Progress U
Sing BLOCKS method ... Processing object type Database_export/schema/table/table_data total estimation using BLOCKS method:2.294 GB processing O Bject type Database_export/tablespace. . Exported "ZMC". Nm_alarm_event "317.5 MB 467108 Rows Processing object Type Database_export/profile processing objec T type Database_export/sys_user/user processing object type Database_export/schemA/user Processing Object Type Database_export/role processing object type DATABASE_EXPORT/GRANT/SYSTEM_GRANT/PROC_ System_grant Processing Object Type Database_export/schema/grant/system_grant processing object type database_export/ Schema/role_grant Processing Object Type Database_export/schema/default_role processing object type database_export/


 Schema/tablespace_quota ........

PARALLEL change the number of active workers for the current job. Number of Parallel=<worker >. Specifies the number of parallel processes that perform the export operation, with a default value of 1

Full exports the entire database defaults to n. Whole Library Import

$ IMPDP \ '/as Sysdba\ ' DIRECTORY=DIR_DP full=y dumpfile=fulldb.dmp parallel=2;

More details Reference