Oracle Data Pump Guide data (EXPDP,IMPDP)

Directory:
First, cold backup derivative considerations
Second, data pump derivative considerations

First, cold backup derivative considerations
1. Cold Recovery steps:
A. Close the Source Library
B. Transfer data files, control files, pfile, log files to the target repository from the source library, and give Oracle user rights
C, open the transferred pfile from the target library, modify the instance name, control file path, dump (Adump, bdump ... ) Class folder path
D. Start database to mount,rename data file and log file path
E. Start the database to the open state
F, configuration monitoring, work done
2, Cold Restore common Command memo:
--The steps to delete the target database when the target side has a database
Ps-ef | grep Local=no--see if there are client connections to the database
Lsnrctl Stop listener name--Stop listening
Ps-ef | grep Local=no | awk ' {print ($)} ' | Xargs kill-9--kill Client Connection
Shutdown immediate--Close the database
Startup exclusive restrict Mount; --Start database to proprietary restriction mode
drop database; --Delete Database

--rname commands for data files and log files
Alter DATABASE rename file ' xxx.dbf ' to ' xxx.dbf ';
Alter DATABASE rename file ' xxx.dbf ' to ' xxx.dbf ';

Second, data pump derivative considerations
--use Parfile to write import and export statements, because sometimes as long as the export or just import, with pfile write convenient for other colleagues to see your derivative parameters.
--When importing the whole library data without importing the table structure, set up the table space corresponding to the source library in the target library and allocate a good size.
1. Export and Import data
Export:
VI expdp_full_ date. par
Userid= '/As Sysdba '
directory= derivative catalogue
# #该路径可以通过查询dba_directoires表得到;
# #建立路径的语法为: Create directory directory name as ' directory path ';
# #对目录路径要授予相应的权限: chown-r oracle:dba/directory path
Dumpfile=expdp_full_ date. dmp
Logfile=expdp_full_ date. Log
parallel=2--Assigning two channels
# #通道分配数量的大小要参考逻辑CPU的数量来确定, use the Nmon command to see the number of logical CPUs, parallel cannot exceed half of the logical CPU, common settings are 2, 4, 8, 16. Sometimes parallel and filesize This parameter conflict, if found that the statement written correctly, but there are unknown reasons for the error, you can try to remove the FileSize parameter test.
filesize=5g--size of each dump file

--Export the whole library structure
Add parameter: full=y
Content=metadata_only

--Export All-Library data non-conductive structure
Add parameter: full=y
Content=data_only

--Export schemas
Add parameter: schemas=xxx

--Export table
Add parameter: tables=xxx,xxx,xxx

Import:
VI impdp_full_ date. par
Userid= '/As Sysdba '
directory= derivative catalogue
Dumpfile=expdp_full_ date. dmp
Logfile=expdp_full_ date. Log
parallel=2--How many parallel are exported pfile, and how much is the import parallel

--Import the whole library structure
Add parameter: content=metadata_only

--Export All-Library data non-conductive structure
Add parameter: content=data_only

2. Precautions
(in the case of FCR, the FCR library is relatively specific)
1, IMPDP abnormal termination of the processing steps:
First step: View derivative tasks in the database (run under Sqlplus)
Set Line 200
Col Owner_name for A20
Col Operation for A30
Col State for A30
Select Owner_name,job_name,operation,state,degree from Dba_datapump_jobs;
Step Two: View IMPDP import details (run in operating system command mode)
IMPDP \ '/as Sysdba\ ' attach= ' sys_import_schema_01 '
Step Three: Stop job
IMPDP \ '/as Sysdba\ ' attach= ' sys_export_schema_01 '
Impdp> Stop_job
Fourth step: Kill Job
IMPDP \ '/as sysdba\ ' attach= ' sys_export_schema_01 '
Impdp> Kill_job

2. When you import data without importing a struct, you need to disable the constraints and triggers that are involved in the object, as follows:
--Close the trigger before closing the constraint to prevent the trigger from inserting data outside the constraint.
--Close Trigger
Cat >start_dis_tirger.sql
Spool/home/oracle/dis_tirger.sql
Select ' Alter TRIGGER ' | | Owner | | '. ' | | trigger_name| | ' ENABLE; ' From Dba_triggers
where status= ' DISABLED ' and owner in (' Fcrbrndata ', ' fcrhstdata ');
Spool off;

--Close constraint
Cat >start_dis_const.sql
Spool/home/oracle/dis_const.sql
SELECT ' ALTER TABLE ' | | t.owner| | '. ' | | t.table_name| | ' Enable constraint ' | | t.constraint_name| | '; '
From dba_constraints s, dba_constraints t
where S.owner = T.r_owner and S.constraint_name = T.r_constraint_name and T.owner in (' Fcrbrndata ', ' fcrhstdata ')
and t.status= ' DISABLED ';
Spool off;

--After the derivative work is done, replace the keyword of the above statement, open the constraint and then open the trigger to prevent the trigger from inserting the data outside the constraint.

3, the Guide some of the time may exist resource consumption, query statements as follows:
Set pages 130;
Set lines 1300;
Col object_name for A20;
Col Machine for A20;
Col program for A20;
Col killid for A30;
Col os_pid format A20;
Select object_name, Machine, S.program, s.sid| | ', ' | | s.serial# as Killid,
P.spid as Os_pid, s.sql_address,l.locked_mode,s.username,s.process,s.sql_id
From V$locked_object l,dba_objects O, v$session S, v$process p
where l.object_id=o.object_id and L.session_id=s.sid and S.PADDR=P.ADDR
and Object_name=upper (' &tablename ') and O.owner=upper (' &owner ') order by 1;

4, import the whole library data without importing the structure, there will be many invalid objects, invalid object compilation process is as follows:



Appendix II: Quick Delete Index statements
Appendix III: Invalid object compilation related scripts
Appendix IV: Disabling the handling of constraints and triggers
Appendix V: Building Index statements from the entire library structure DMP file


Appendix I: Derivative Case inquiry




--Get Object definition
Set Lin PageSize 500
Set Long 99999
Set LONGC 99999
Select DBMS_METADATA.GET_DDL (Upper (' &object_type '), Upper (' &object_name '), Upper (' &username ')) from Dual


Appendix II: Quick Delete Index statements
--Delete Global index
SELECT distinct ' drop index ' | | i.owner| | '. ' | | i.index_name| | '; ' from dba_indexes I
where i.table_owner= ' Fcrhstdata ' and I.table_name in (
' Ch_acct_ledg ',
' Rec_txn_log ',
' Td_dep_prorated_tax_dtls ',
' Td_dep_prorated_tax_dtls ',
' Xface_audit_trail_log ',
' Xf_ol_st_txnlog_mmdd '
);

--Delete partition index
SELECT distinct ' drop index ' | | p.index_owner| | '. ' | | p.index_name| | '; ' from dba_ind_partitions p,dba_indexes i
WHERE P.index_owner=i.owner and I.index_name=p.index_name and i.table_owner= ' Fcrhstdata ' and
I.status= ' n/a ' and i.table_name in (
' Xf_stcap_gl_txns_mmdd ', ' xf_st_cap_input_txn_temp ', ' ch_acct_int_breakup ', ' Xface_audit_trail_log ',
' Et_alerts_log_1 ', ' xf_ol_st_sclog_mmdd ', ' ch_arrears_table ', ' et_alert_publish ', ' Td_nobook ',
' Ci_custmast ', ' ch_acct_int_breakup_deleted ', ' td_audit_trail ', ' Xface_fcr_logger ',
' Ch_acct_mast ', ' ba_change_record_log ', ' rec_txn_log_purge ', ' ch_acct_balance_dtls_mmdd ', ' Xf_ol_st_txnlog_stl_ MMDD ',
' ch_tmp_rch405 ', ' ch_acct_capitalization_history ', ' Xf_stcap_gl_txns '
);


Appendix III: Invalid object compilation related scripts

1, System user Invalid object compilation: @?/rdbms/admin/utlrp.sql

2, General user Invalid object compilation

--Source Library Invalid object acquisition script Start_privs.sql, generate authorization script Privs.sql
Cat >start_privs.sql

#####
--start$$
Spool/home/oracle/privs.sql
--create role$$
Select ' Create role ' | | role| | '; ' from Dba_roles;
Select ' Grant ' | | privilege| | ' On ' | | owner| | '. ' | | table_name| | ' to ' | | role| | '; ' from Role_tab_privs
UNION ALL
Select ' Grant ' | | privilege| | ' To ' | | role| | '; ' from Role_sys_privs
UNION ALL
Select ' Grant ' | | granted_role| | ' To ' | | role| | '; ' from Role_role_privs;
--grant privs$$
Select ' Grant ' | | privilege| | ' to ' | | grantee| | '; '
From Dba_sys_privs
where GRANTEE in (select username from dba_users where username isn't in (' Appqossys ', ' dbsnmp ', ' DIP ', ' Exfsys ', ' Wmsys ', ' SYSTEM ', ' SYS ', ' sdbxts ', ' outln ', ' oracle_ocm ')
UNION ALL
Select ' Grant ' | | granted_role| | ' to ' | | grantee| | '; '
From Dba_role_privs
where GRANTEE in (select username from dba_users where username isn't in (' Appqossys ', ' dbsnmp ', ' DIP ', ' Exfsys ', ' Wmsys ', ' SYSTEM ', ' SYS ', ' sdbxts ', ' outln ', ' oracle_ocm ')
UNION ALL
Select ' Grant ' | | privilege| | ' On ' | | owner| | '. ' | | table_name| | ' to ' | | grantee| | '; '
From Dba_tab_privs
where GRANTEE in (select username from dba_users where username isn't in (' Appqossys ', ' dbsnmp ', ' DIP ', ' Exfsys ', ' Wmsys ', ' SYSTEM ', ' SYS ', ' sdbxts ', ' outln ', ' oracle_ocm ')
;
--compile objects$$
Select ' Alter VIEW ' | | owner| | '. ' | | object_name| | ' compile; ' from dba_objects where object_type= ' VIEW ' and status= ' INVALID ' and owner <> ' Impuser '
UNION ALL
Select ' Alter PROCEDURE ' | | owner| | '. ' | | object_name| | ' compile; ' from dba_objects where object_type= ' PROCEDURE ' and status= ' INVALID ' and owner <> ' Impuser '
UNION ALL
Select ' Alter FUNCTION ' | | owner| | '. ' | | object_name| | ' compile; ' from dba_objects where object_type= ' FUNCTION ' and status= ' INVALID ' and owner <> ' Impuser '
UNION ALL
Select ' Alter TRIGGER ' | | owner| | '. ' | | object_name| | ' compile; ' from dba_objects where object_type= ' TRIGGER ' and status= ' INVALID ' and owner <> ' Impuser '
UNION ALL
Select ' Alter Package ' | | owner| | '. ' | | object_name| | ' compile body; ' from dba_objects where object_type= ' package BODY ' and status= ' INVALID ' and owner <> ' I Mpuser '
UNION ALL
Select ' Alter Package ' | | owner| | '. ' | | object_name| | ' compile; ' from dba_objects where object_type= ' package ' and status= ' INVALID ' and owner <> ' Impuser '
UNION ALL
SELECT ' CREATE OR REPLACE public synonym ' | | a.object_name| | ' For ' | | b.table_owner| | '. ' | | b.table_name| | '; '
From Dba_objects a,dba_synonyms B
WHERE B.synonym_name=a.object_name and b.owner= ' public ' and a.object_type= ' synonym ' and a.status= ' INVALID ' and OWNER &lt ;> ' Impuser '
UNION ALL
SELECT ' CREATE OR REPLACE synonym ' | | a.owner| | '. ' | | a.object_name| | ' For ' | | b.table_owner| | '. ' | | b.table_name| | '; '
From Dba_objects a,dba_synonyms B
WHERE B.synonym_name=a.object_name and A.owner=b.owner and b.owner<> ' public ' and a.object_type= ' synonym ' and A. Status= ' INVALID ' and A.owner <> ' Impuser '
;
--end$$
Spool off;
####

--Target Library run invalid object compile acquisition script Start_compile.sql, generate invalid object compilation script Compile.sql

Cat >start_compile.sql
#####
Set Line 1000
Set pages 20000
Spool/home/oracle/compile.sql
--compile objects$$
Select ' Alter VIEW ' | | owner| | '. ' | | object_name| | ' compile; ' from dba_objects where object_type= ' VIEW ' and status= ' INVALID ' and owner<> ' Impuser '
UNION ALL
Select ' Alter PROCEDURE ' | | owner| | '. ' | | object_name| | ' compile; ' from dba_objects where object_type= ' PROCEDURE ' and status= ' INVALID ' and owner<> ' Impuser '
UNION ALL
Select ' Alter FUNCTION ' | | owner| | '. ' | | object_name| | ' compile; ' from dba_objects where object_type= ' FUNCTION ' and status= ' INVALID ' and owner<> ' Impuser '
UNION ALL
Select ' Alter TRIGGER ' | | owner| | '. ' | | object_name| | ' compile; ' from dba_objects where object_type= ' TRIGGER ' and status= ' INVALID ' and owner<> ' Impuser '
UNION ALL
Select ' Alter Package ' | | owner| | '. ' | | object_name| | ' compile body; ' from dba_objects where object_type= ' package BODY ' and status= ' INVALID ' and owner<> ' IM Puser '
UNION ALL
Select ' Alter Package ' | | owner| | '. ' | | object_name| | ' compile; ' from dba_objects where object_type= ' package ' and status= ' INVALID ' and owner<> ' Impuser '
UNION ALL
Select ' Alter type ' | | owner| | '. ' | | object_name| | ' compile body; ' from dba_objects where object_type= ' TYPE body ' and status= ' INVALID ' and owner<> ' Impus ER '
UNION ALL
SELECT ' CREATE OR REPLACE public synonym ' | | a.object_name| | ' For ' | | b.table_owner| | '. ' | | b.table_name| | '; '
From Dba_objects a,dba_synonyms B
WHERE B.synonym_name=a.object_name and b.owner= ' public ' and a.object_type= ' synonym ' and a.status= ' INVALID ' and a.owner& Lt;> ' Impuser '
UNION ALL
SELECT ' CREATE OR REPLACE synonym ' | | a.owner| | '. ' | | a.object_name| | ' For ' | | b.table_owner| | '. ' | | b.table_name| | '; '
From Dba_objects a,dba_synonyms B
WHERE B.synonym_name=a.object_name and A.owner=b.owner and b.owner<> ' public ' and a.object_type= ' synonym ' and A. Status= ' INVALID ' and b.owner<> ' impuser ';
--end$$
Spool off;
#####


Appendix IV: Disabling the handling of constraints and triggers

1. Turn off triggers and constraints when importing a database


2. Opening constraints and triggers when exporting a database

--Open constraint
Cat >start_en_const.sql
Set Line 1800
Spool/home/oracle/en_const.sql
SELECT ' ALTER TABLE ' | | t.owner| | '. ' | | t.table_name| | ' Enable constraint ' | | t.constraint_name| | '; '
From dba_constraints s, dba_constraints t
where S.owner = T.r_owner and S.constraint_name = T.r_constraint_name and t.status= ' DISABLED ' and t.validated= ' VALIDATED '
UNION ALL
SELECT ' ALTER TABLE ' | | t.owner| | '. ' | | t.table_name| | ' Enable Novalidate constraint ' | | t.constraint_name| | '; '
From dba_constraints s, dba_constraints t
where S.owner = T.r_owner and S.constraint_name = T.r_constraint_name and t.status= ' DISABLED ' and t.validated= ' not validat ED ';
Spool off;

--Open Trigger
Cat >start_en_tirger.sql
Set Line 1800
Spool/home/oracle/en_tirger.sql
Select ' Alter TRIGGER ' | | Owner | | '. ' | | trigger_name| | ' ENABLE; ' From Dba_triggers
where status= ' DISABLED ';
Spool off;


Appendix V: Building Index statements from the entire library structure DMP file
The following statement is the index statement for the specified table, which is also available for the whole library, and after generating the statement, you can modify the concurrency number within the generated script to make the index creation faster.
Userid= '/As SYSDBA '
Directory=expdp_fulldata
Dumpfile=fulldatabase_0604.dmp
Logfile=index_import_full.log
Tables= (
Fcrhstdata. Actb_history,
)
Include=index
Sqlfile=index.sql

This article is from the "SYSDBA" blog, make sure to keep this source http://sysdba.blog.51cto.com/10492366/1704269

Oracle Data Pump Guide data (EXPDP,IMPDP)