Export and Import Backup and Restore
Logical backup: Normal export/import and Data pump
Data pump has many features. Like (PARALLEL, Job,NETWORK_LINK,REMAP DATAFILE & tablespace, exclude, include, version, extimate_only)
It mostly done in the DB server itself. It access data by direct path and external tables a master process, and worker processes. Whereas normal export/import uses Conventional path.
DataPump: Faster, Filtering option (remaps, compression & parallel), job, & self-tuning etc
| Data Pump Export/Import | Normal Export/Import |
| Filtering of data and metadata and remaps etc | Not much |
| Change PARTITION_OPTIONS while import | No |
| Two process at DB server level Master & Worker | It runs on DB & client level |
| Data Pump jobs runs server, can start detach and resume later | no |
| Data Pump parameters can be altered at run time | no |
| Data Pump will recreate the user | DBA need to create |
| does not use the BUFFERS parameter | It will |
| Impdp/Expdp has self-tuning like BUFFER and RECORDLENGTH | No |
| Data Pump create dump as XML documents rather than as DDL | DDL format |
| Impdp/Expdp use parallel execution rather than a single stream | Single thread |
| Expdp/Impdp create group of files rather than on a single dump | Single file |
| Lower to higher vice Vera | No |
| It will compress data, if enabled | It won’s compress |
| schema import recreate user will add security privileges also | No |
| Sequential media, such as tapes and pipes not supported | Support tape, pipes even compress the data |
| It is 5 time faster than normal export | Slow |
| It is 20 time faster than normal import | Slow |
| Even it canceled/closed it will run in background | |
| It will import table using either APPEND or TRUNCATE | Only append |
| Same and cross platform OS | |
| Can be used for upgrade and downgrade after 10.1 | Can be used for upgrade prior 10.1 |
| no | Moving tables of XML/columns from 11.1 to 10.2 |
| Transfer data by database link, without dump file | no |
| DB directories path | Client specified path |
These are utilities. The followings are keywords.
Export – EXP
Import – IMP
Backup Types for both:
| Full DB |
| Tablespace |
| Users/ Schema |
| Table |
Methods of Backup & Recovery:
| Methods | Comments |
| CLI – Command Line Interface | Command line |
| Interactive mode | It will ask, we need to enter |
| Parfile | Save as a file and run |
| OEM | GUI |
CLI
$ Exp user/pass parameters 1,2,3 etc
Who can take export/import – exp_full_databse & Imp_full_Database privileged users.
Export parameters:
| NO | Export Parameters | Comments |
| 1 | Full=y/n | All objects or any particular |
| 2 | Owner=<Username> | Particular user |
| 3 | Tables=<user.tablename> | Particular Table |
| 4 | Tablespace=<Tablespacename> | Particular Tablespace |
| 5 | Transport_tablespace=y/n | Used for different DB names, Migration |
| 6 | Grant = y/n | Privilege backup |
| 7 | Indexes =y/n | Index Backup |
| 8 | Constraints =y/n | Constraint backup |
| 9 | Rows= y/n | Structure +Data Backup |
| 10 | Consistent=y/n | In case of SCN same no |
| 11 | Compress =y/n | Compress data, will be faster |
| 12 | Direct = y/n | Conventional & Direct path |
| 13 | Statistics=<none> | Yes, will be slow |
| 14 | Feedback = <number> 100 | Give a feedback for every 100 rows |
| 15 | File=<Dumpname.dump> | Binary File |
| 16 | Log = <Logname.log> | Export info will log here |
For the first 4 parameters, only one can be possible at a time.
Conventional path – It will take a data from data file –DB buffer cache — Export. Will be slow.
Direct path – It will take a data from data file –OS cache — Export. Will be fast.
Import parameters:
| NO | Import Parameters | Comments |
| 1 | Full=y/n | All objects or any particular |
| 2 | Fromuser=<Export User> | Export user name |
| 3 | Touser=<Import User> | Import user name |
| 4 | Tables=<tablename> | Particular Table |
| 5 | Tablespace=<Tablespacename> | Particular Tablespace |
| 6 | Transport_tablespace=y/n | Used for different DB names, Migration |
| 7 | Grant = y/n | Privilege backup |
| 8 | Indexes =y/n | Index Backup |
| 9 | Constraints =y/n | Constraint backup |
| 10 | Rows= y/n | Structure +Data Backup |
| 11 | Statistics=<none> | Yes, will be slow |
| 12 | Feedback = <number> 100 | Give a feedback for every 100 rows |
| 13 | File=<Dumpname.dump> | Binary File |
| 14 | Log = <Logname.log> | Export info will log here |
| 15 | Shows=Y/N | It will generate a log file, to know the dump info |
| 16 | Ignore = Y/N | Ignore and append the data, duplicates also |
| 17 | Commit = Y/N | Y- Will be fast, Since it will not wait for the commit |
Note: Import will load lots of data, and archive log might full, we need to monitor this and sometimes may change the modes.
We need to verify the log for the import/export success & failures.
Export / Import Workouts and Scenarios
CLI method – Full DB export
$ Exp system/manager file =’/opt/full.dump’ full =y log =/opt/full.log
$ Imp system/manager file =’/opt/full.dump’ full =y log =/opt/full_imp.log
Import only particular tables & Users etc
$ Imp system/manager file =’/opt/full.dump’ fromuser =Muthu touser=rajesh log =/opt/full_imp.log ignore=y
$ Imp system/manager file =’/opt/full.dump’ fromuser =Muthu tables=emp touser=rajesh log =/opt/full_imp.log
(Workout): Export only particular tables & Users and then import it
$ Exp
$ Imp
Export and Import tablespace
Check the tablespace violation. Sys users tables do not exit into the tablespace, if any we need to move it.
Violation Check
Sql > exec dbms.tts_transport_set_check(‘tools’,true); — It will display the sys users table
Sql > Select * from transport_violation;
Sql > Alter tablespace Muthu read only;
$ Exp “sys/sys as sysdba” file =’/opt/tools_tbs.dump’ tablespace=tools transport _tablespace=y log=/opt/tools_tbs.log
Parfile:
userid = sys/sys
file =/opt/backup/export/muthu_new_tbs.dump tablespaces=muthu_new transport_tablespace=y grants=y log=/opt/backup/export/muthu_new_tbs.log
Sys – Only can export tablespace.
Note: We should backup data files also, since the tablespace belongs to data files
$ mkdir –p /backup/export/Muthu
$ cd /backup/export/Muthu
$ cp toolso1.dbf /backup/export/Muthu
Sql > Alter tablespace Muthu read write;
Import – By default, tablespace can be imported as read only, even if we backup in read write.
$ Imp “sys/sys as sysdba” file =’/opt/tools_tbs.dump’ tablespace=tools transport _tablespace=y log=/opt/imp_tools_tbs.log datafile=’/backup/export/Muthu/tools.dbf’
Sql > Alter tablespace set read write;
Parfile:
userid = sys/sys
file =/opt/backup/export/muthu_new_tbs.dump tablespaces=muthu_new transport_tablespace=y grants=y log=/opt/backup/export/muthu_new_tbs.log datafiles=/opt/backup/export/muthu_new_01.dbf
imp parfile =/opt/backup/common/muthu_imp.par
Interactive Mode
We need to manually enter parameter one by one.
$ exp UN: Pass: file: etc..
Parfile Method
Open a notepad and write all commands and save it as .par
====================
User id =system/manager
File= /opt/full.dump
Full = y
Log =/opt/full.log
====================
Save /opt/full.par
$ exp parfile = /opt/full.par
$ Imp parfile = /opt/full.par
OEM – GUI Method
To Get help of EXP/IMP parameters, use exp help =y
Data Pump Export/Import – New feature from 10g
It is a utility.
Key Words: Expdp / Impdb
It will backup all like a normal export import. But, it will be faster.
Syntax: $ expdp User/pass parameters 1,23 etc
Who can take export/import – datapump_exp_full_database & datapump _imp_full_Database privileged users.
Export Parameters
| No | Datapump Exp Parameters | Comments |
| 1 | Attach=<Jobname> | Runs a job and can be pause and resume |
| 2 | Contents=<all /metadata_only/data_only | Structure and data etc |
| 3 | Compression=<All//metadata_only/data_only/none | 10g metadata can compress, 11g both |
| 4 | Directory=<dirname> | DB level directory creation |
| 5 | Dumpfile=<name.dump> | Binary dump name |
| 6 | Estimate=blocks/statistics | Estimate the data+statistics size |
| 7 | Estimate_only=y/n | Calculate the dumpfile size only |
| 8 | Exclude=<table/index/constraint/profile etc..> | Index:/”like/ind.eno”/”/ |
| 9 | Flashback_time=to_timestamp(‘2015-09-20 10:00:00’) | It will backup before that |
| 10 | Flashback_scn=SCN | Either time or SCN can be used |
| 11 | Full=y/n | |
| 12 | file_size=<integer> like 6GB | File will be divided by given bytes , 2file 3gb & 3gb |
| 13 | Include=<table/index/constraint/profile etc..> | table:”in(’emp’)” |
| 14 | Job_name=<jobname> | By default sys_operation_mode_nn – Job name |
| 15 | logfile=<logname.log> | By default export.log / import.log |
| 16 | nologfile=y/n | No log file will generate |
| 17 | Network_lonk=<Dblinkname> | Objects can be exported b/w DB servers |
| 18 | Parallel=<no.of CPU> | |
| 19 | Parfile=<filename.par> | Save the script to run |
| 20 | query=muthu.emp:”where salary>2000″ | Condition can be given |
| 21 | Schema=<username> | |
| 22 | Status=<no.of Seconds> | It will display the export status in seconds |
| 23 | Tables=<user.tablename> | Particular table |
| 24 | Tablespace=<tablespacename> | |
| 25 | Transport_full_Check=y/n | Check the table & index in different tablespace or not |
| 26 | Transport_tablespaces=<TBS name> | Only backup metadata, It cannot resume, if stopped |
For DB link, we need a net service name and tns entry to do a export / import.
Sql> create databse link test_ln connect to Muthu identified by pass using ‘testDB’ testDB – netservice
Sql> select * from emptiest_ln;
If we use, estimate_only,content=metadata_only & transport_tablespace – Query parameter cannot be used.
Import Parameters
| No | Datapump Exp Parameters | Comments |
| 1 | Attach=<Jobname> | Runs a job and can be pause and resume |
| 2 | Contents=<all /metadata_only/data_only | Structure and data etc |
| 3 | Directory=<dirname> | DB level directory creation |
| 4 | Dumpfile=<name.dump> | Binary dump name |
| 5 | Estimate=blocks/statistics | Estimate the data+statistics size |
| 6 | Exclude=<table/index/constraint/profile etc..> | Index:/”like/ind.eno”/”/ |
| 7 | Flashback_time=to_timestamp(‘2015-09-20 10:00:00’) | It will backup before that |
| 8 | Flashback_scn=SCN | Either time or SCN can be used |
| 9 | Full=y/n | |
| 10 | Include=<table/index/constraint/profile etc..> | table:”in(’emp’)” |
| 11 | Job_name=<jobname> | By default sys_operation_mode_nn – Job name |
| 12 | logfile=<logname.log> | By default export.log / import.log |
| 13 | Parallel=<no.of CPU> | |
| 14 | Parfile=<filename.par> | Save the script to run |
| 15 | query=muthu.emp:”where salary>2000″ | Condition can be given |
| 16 | Schema=<username> | |
| 17 | Status=<no.of Seconds> | It will display the export status in seconds |
| 18 | Tables=<user.tablename> | Particular table |
| 19 | Tablespace=<tablespacename> | |
| 20 | Transport_full_Check=y/n | Check the table & index in different tablespace or not |
| 21 | Transport_tablespace=<TBS name> | Only backup metadata, It cannot resume, if stopped |
| 22 | remap_schemas=muthu:rajesh | |
| 23 | remap_tables=emp:empnew | 11g feature, rename in destination |
| 24 | remap_datafile=’/opt/tools.dbf’:’/data/tools.dbf’ | |
| 25 | skip_unusable_indexes=y/n | Skip the unused indexes |
| 26 | table_exits_action=[skip/append/truncate/replace] | skip will comeout, if table is avilable |
How to comeout & comein of data pump
Comeout: Ctrl+C –Export> exit_client
Comein:
$ expdp system/manager attach=sys_full_export_01
Export> continue_client
Find the job name
SELECT owner_name, job_name, operation, job_mode, state FROM dba_datapump_jobs;
| Exit | Exit_Client |
| Status can be see log file only | We can resume job status @ any point |
How to stop & resume the job of data pump
Export> stop_job
Export> exit
$ expdp system/manager attach=sys_full_export_01
Export> start_job
Note: We can add any parameter while the job running (Ctrl+C)
Ex; Export> status =300
How to Terminate job of data pump
Export> kill_job
Export and Import data by data pump
$ mkdir –p /backup/export/Muthu
Sql> conn system/manager
Sql> create directory exp_dir as ‘/backup/export/Muthu’;
Sql> conn system/sys as sysdba
Sql> grant read,write on directory exp_dir to system;
Sql> select * from dba_directories;
Sql> select * from all_directories;
Sql> drop directory exp_dir;
By default if there is no directory, it will save in ddump
Full DB
$ expdp system/manager dumpfile=full.dump full=y logfile=full.log directory=exp_dir
$ impdp system/manager dumpfile=full.dump full=y logfile=imp_full.log directory=exp_dir
Schema
Same schema name
$ impdp system/manager dumpfile=full.dump schemas=muthu logfile=Schema.log directory=exp_dir
Different schema name
$ impdp system/manager dumpfile=full.dump remap_schemas=Muthu:rajesh logfile=Sehema1.log directory=exp_dir
Table
$ impdp system/manager dumpfile=full.dump tables=muthu logfile=tbl.log directory=exp_dir
How to view the backup of objects and what has been backed up
Sql> select * from dba_export_jobs
Best Practice: Use parfile method, Since it stores the scripts and can be verified.
$ expdb parfile =/opt/Muthu.par
Open a notepad and save it a scripts.
Parameters help – $ expdp help =y & $ Impdp help =y
(Workout): Export only particular tables & Users and then import it.
