Best Practice Examples For EXPDP and IMPDP Jobs:-
By default everything in database is exported and imported you can choose for your needs parameters.
select * from all_directories where directory_name='&directory';
-----------------------------------------------------------------------------
How to use export import utility from linux terminal :-
$ expdp user_who_started_job/password attach=job_name
How to export / import using "sysdba"
$ expdp \"/ as sysdba\"
$ impdp \"/ as sysdba\"
$ impdp user_who_started_job/password attach=job_name
export > status
import > status
export > exit
import > exit
import / export > help
Note:-
Default behaviour of impdp:-
1. by default imports all objects
2. impdp customize using 'include' and 'exclude' if you don't specify include/exclude then
impdp will imports all objects from the dmp files.
** all objects :- tables, indexes, views, triggers, procedures, functions, packages, constraints, types, etc.**
3.'impdp also imports others related data, such as constraints, grants and synonyms.
4. target db already contains objects with the same name as the imported objects, default behaviour is
to overwrite the existing objects with the imported objects. you can change behaviour using remap_table,
remap_schema, remap_
when to use remap_* :-
schema consolidation, renaming objects, data migration, filtering data
remap_data
remap_datafile
remap_schema
remap_table
remap_tablespace
On SOURCE DB:-
sudo -iu oracle
$ nohup expdp user/pwd directory=DATAPUMP dumpfile=file.dmp schemas=schema_name exclude=statistics logfile=file_expdp.log &
or
$ nohup expdp parfile=filename.par &
ON TARGET DB:-
sudo -iu oracle
$ nohup impdp user/pwd directory=DATAPUMP dumpfile=file.dmp logfile=file_impdp.log &
or
$ nohup impdp parfile=filename.par &
schema_objects:-
**** If you want to empty schema then drop below objects for that schemas *********
tables, views, indexes, constraints, procedures, functions, packages, triggers and synonyms
Hints: --->
In toad, go to "schema browser"
In SQLDEVELOPER, go to left pane, (+) button on each connection then search for object and drop
---------------------------------------------------------------------------------------------- ********************************* SCHEMA EXPORT ********************************* [user@hostname]$ cat schema_export_prod_<date-time>.par DIRECTORY=DATA_PUMP SCHEMAS=<schema_name> DUMPFILE=exp_prod_01132023_%U.dmp LOGFILE=exp_prod_schema_01132023.log PARALLEL=6 FILESIZE=10G EXCLUDE=STATISTICS COMPRESSION=ALL CLUSTER=N STATUS=30 JOB_NAME=exp_prod_schema_01132023 ************************************ TABLE EXPORT ********************************** [user@hostname]$ cat schema_prod_expdp_table_tablename.par DIRECTORY=DATA_PUMP SCHEMAS=<schema_name> DUMPFILE=exp_prod_<schema_name>_<table_name>_%U.dmp LOGFILE=exp_prod_<schema_name>_<table_name>.log PARALLEL=12 FILESIZE=15G COMPRESSION=ALL CLUSTER=N STATUS=30 #JOB_NAME=exp_prod_<schema_name>_<table_name>_01132023 INCLUDE=TABLE:"IN('<table_name>')" ************************************ SCHEMA import ********************************** [user@hostname]$ cat imp_<schema_name>.par DIRECTORY=DATA_PUMP_DIR DUMPFILE=exp_prod_<schema_name>_01282023_%U.dmp LOGFILE=IMP_<schema_name>_<table_name>_02022023.log TABLE_EXISTS_ACTION=REPLACE #JOB_NAME=<schema_name>_<table_name> EXCLUDE=VIEW,PROCEDURE,PACKAGE,FUNCTION,TRIGGER #schemas=<schema_name> EXCLUDE=SCHEMA:"IN('<schema1_name>','<schema2_name>')" Note:- Practice on home lab environment and observer take notes for different scenarios. PARALLEL=32 CLUSTER=N FULL=Y ************************************ SCHEMA import ********************************** [user@hostname]$ cat <schema_name>_<host_name>.par DIRECTORY=DATA_PUMP_DIR DUMPFILE=<schema_name>_<table_name>_%U.dmp LOGFILE=IMP_<schema_name>_<table_name>_10022018.log EXCLUDE=SCHEMA:"IN('<schema_name>','<schema_name>')" SCHEMAS=<schema_name> ##SCHEMAS=<schema_name> parallel=25 TABLE_EXISTS_ACTION=REPLACE CLUSTER=N ************************************ SCHEMA import ********************************** [user@hostname]$ cat <schema_name>.par DIRECTORY=DATA_PUMP_DIR DUMPFILE=<schema_name>_<table_name>_%U.dmp LOGFILE=IMP_<schema_name>_<table_name>.log EXCLUDE=SCHEMA:"IN('<schema_name>','<schema_name>')" SCHEMAS=<schema_name> ##SCHEMAS=<schema_name> parallel=25 TABLE_EXISTS_ACTION=REPLACE CLUSTER=N *********************** FULL DATABASE EXPORT ******************************** [user@hostname]$ cat <database>_FULL_EXP.par DIRECTORY=DATA_PUMP_DIR DUMPFILE=Full_database_%U.dmp LOGFILE=Full_<schema_name>_<table_name>.log ##JOB_NAME=03282016_IMP_EXPORT_ST1_NEW FILESIZE=10G CLUSTER=N PARALLEL=4 full=y EXCLUDE=SCHEMA:"IN('<schema_name>',...,'<schema_name>)" ********************************** FULL DATABASE EXPORT ******************] [user@hostname]$ cat <database>_FULL_EXP.par DIRECTORY=DATA_PUMP_DIR DUMPFILE=Full_DATABASE_exp_%U.dmp LOGFILE=Full_database_exp.log ##JOB_NAME=<full_database_exp> FILESIZE=10G CLUSTER=N PARALLEL=4 full=y EXCLUDE=SCHEMA:"IN('<schema_name>',...,'<schema_name>')" *********************************** table index create script ************************** [user@hostname]$ cat DATABASE_TABLE_INDEX_CREATE_SCRIPT.par DIRECTORY=DATA_PUMP_DIR DUMPFILE=NAME_%U.dmp LOGFILE=database_INDEX.log JOB_NAME=database_INDEX CLUSTER=N PARALLEL=24 INCLUDE=INDEX sqlfile=DATABASE_INDEXES_CREATE.sql *****************************************Schema exports ******************************** [user@hostname]$ cat <schema_name>_export_prod.par DIRECTORY=DATA_PUMP_DIR SCHEMAS=<schema_name> DUMPFILE=exp_prod_<schema_name>_%U.dmp LOGFILE=exp_prod_<schema_name>.log PARALLEL=12 FILESIZE=15G EXCLUDE=STATISTICS COMPRESSION=ALL CLUSTER=N STATUS=30 EXCLUDE=TABLE:"IN ('<table_name>',...,'<table_name>')" JOB_NAME=exp_prod_<schema_name>_<table_name> **************************************************************************************
No comments:
Post a Comment