Shulou(Shulou.com)05/31 Report--

This article mainly shows you the "oracle 12c use a data pump to replicate the database structure of what matters needing attention", the content is easy to understand, clear, hope to help you solve your doubts, the following let the editor lead you to study and learn "oracle 12c use a data pump to replicate the database structure of what matters needing attention" this article.

1. Create a directory

Create or replace directory dp_dir as'/ u02Universe Files`

two。 Export all the structures of the user SH,HR without data

Vi scott_meta.par

Userid= "/ as sysdba"

Directory=dp_dir

Dumpfile=scott_expdp.dmp

Schemas=sh,hr

Content=metadata_only

Enable 4 parallelism to perform export

Expdp parfile=scott_meta.par parallel=4

3. Export database table space structure

Vi scott_tbs.par

Userid= "/ as sysdba"

Directory=dp_dir

Dumpfile=scott_tbs.dmp

Include=tablespace

Full=y

Content=metadata_only

Open 2 parallelism to perform export

Expdp parfile=scott_tbs.par parallel=2

4. Copy password verification rule script

The source library uses password rules, which can be obtained by modifying the following file.

Cd $ORACLE_HOME/rdbms/admin

Cp utlpwdmg.sql scott_utlpwdmg.sql

Modify the content of the script as needed, and if you just need a password authentication, you can do so in the following way.

Vi scott_utlpwdmg.sql

... Omit part of the content

ALTER PROFILE DEFAULT LIMIT

/ *

PASSWORD_LIFE_TIME 90

PASSWORD_GRACE_TIME 3

PASSWORD_REUSE_TIME 365

PASSWORD_REUSE_MAX 20

FAILED_LOGIN_ATTEMPTS 3

PASSWORD_LOCK_TIME 1

, /

PASSWORD_VERIFY_FUNCTION ora12c_verify_function

... Omit part of the content

5. Copy profile script

Source database users have app_profile restrictions, which need to be exported manually

SYS@cdb > select distinct profile from dba_profiles

PROFILE

-

APP_PROFILE

DEFAULT

Select profile,resource_name,limit from dba_profiles where profile='APP_PROFILE'

Modify the script through the query structure above to

Vi app_profile.sql

CREATE PROFILE "APP_PROFILE" LIMIT

CPU_PER_SESSION UNLIMITED

CPU_PER_CALL UNLIMITED

CONNECT_TIME UNLIMITED

IDLE_TIME UNLIMITED

SESSIONS_PER_USER UNLIMITED

LOGICAL_READS_PER_SESSION UNLIMITED

LOGICAL_READS_PER_CALL UNLIMITED

PRIVATE_SGA UNLIMITED

COMPOSITE_LIMIT UNLIMITED

FAILED_LOGIN_ATTEMPTS UNLIMITED

PASSWORD_LOCK_TIME UNLIMITED

PASSWORD_GRACE_TIME UNLIMITED

PASSWORD_LIFE_TIME UNLIMITED

PASSWORD_REUSE_MAX UNLIMITED

PASSWORD_REUSE_TIME UNLIMITED

PASSWORD_VERIFY_FUNCTION NULL

Scp tablespace structure script, schema script, password verification script, profile script to target database

Scp scott_tbs.dmp scott_expdp.dmp scott_utlpwdmg.sql app_profile.sql oracle@:/home/oracle/files

Target end

5. Create a directory

Create or replace directory dp_dir as'/ home/oracle/files'

6. Export tablespace statements using the sqlfile parameter of the data pump

Impdp\'/ as sysdba\ 'directory=dp_dir dumpfile=scott_tbs.dmp sqlfile=scott_tbs.sql

The script content is create tablespace xxx...

After the statement is executed, a script named scott_tbs.sql is generated, which contains the statements that create all the tablespaces in the database. We need to extract the script for the tablespace required by the business (excluding the system/sysaux/undo/users/temp that comes with the installation of the system). If the disk space on the target side is small, you need to reduce the data file before running it.

7. Create a tablespace

SQL > @ / home/oracle/files/scott_tbs.sql

8. Create a password constraint

SQL > @ / home/oracle/files/scott_utlpwdmg.sql

9. Create a user profile. Without this step, the profile will not be found and an error will be reported when the import script creates the user.

SQL > @ / home/oracle/files/app_profile.sql

10. Import the table structure. In this step, you will create users, authorizations, create tables, and so on.

Impdp\'/ as sysdba\ 'directory=dp_dir dumpfile=scott_expdp.dmp logfile=scott_expdp.log parallel 4

Summary:

The content=metadata_only option of the data pump provides convenience for the requirements of the export structure. The exported objects can be full library full=y, table space inclue=tablespace, and so on.

Use the sqlfile parameter of impdp to export the creation statement in the dmp file. You can also export table spaces and table creation statements using dbms_metadata.get_ddl.

Before importing the specific user structure, you need to create the password rules and related profile needed to create the user and then impdp the import to avoid errors in creating the user.

The above is all the contents of the article "what are the considerations for oracle 12c to copy database structure using data pump?" Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

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.