u1> drop table emp purge;
u1> select * from tab;
oracle> impdp dumpfile=meta.dmp remap_schema=scott:u1 directory=dp
u1> select * from tab;
u1> desc emp;
u1> drop table emp purge;
u1> select * from tab;
oracle> impdp dumpfile=row.dmp remap_schema=scott:u1 directory=dp
username : / as sysdba
u1> select * from emp;
u1> drop table emp purge;
u1> drop table dept purge;
user should exist in table level import.
oracle> impdp dumpfile=table.dmp remap_schema=scott:u1 directory=dp
username : / as sysdba
u1> select * from tab;
SQL> drop user scott cascade;
oracle> impdp dumpfile=user.dmp remap_schema=scott:scott directory=dp
username : / as sysdba
We no need to create the skeleton user before importing the data like logical traditional importing.
SQL> alter user scott account unlock indentifed by scott;
SQL> conn scott/tiger
scott> select * from tab;
oracle> impdp dumpfile=user.dmp remap_schema=scott:u1 directory=dp
If there is no u1 user,it creates.
u1 takes tiger as password.
oracle> expdp dumpfile=meta.dmp log=meta.log tables=emp rows=n directory=dp job_name=metadata
username : scott/tiger
scott> select count(*) from tab;
oracle> expdp dumpfile=row.dmp log=row.log tables=emp query='\where deptno=20\' directory=dp
username : scott/tiger
scott> select count(*) from tab;
SQL> grant read,write on directory dp to scott;
SQL> conn scott/tiger;
scott> select count(*) from tab;
oracle> expdp dumpfile=table.dmp logfile=table.log tables=emp,dept directory=dp
username : scott/tiger
scott> select count(*) from tab;
oracle> expdp dumpfile=user.dmp logfile=user.log schemas=scott directory=dp
username : / as sysdba
SQL> select count(*) from tab;
Check for the tables before starting the job and after ending the job.
oracle> expdp dumpfile=fulldb.dmp logfile=fulldb.log full=y directory=dp job_name=fulldatabase
username : / as sysdba
SQL> select count(*) from tab;
Check for the tables before starting the job and after ending the job.
stopping a job :
press Ctrl C to stop a job.
export> stop_job=immediate
SQL> select * from dba_datapump_jobs;
SQL> select * from tab;
attaching a job(reinitiating a job):
scott> conn / as sysdba
SQL> select * from dba_datapump_jobs;
oracle> expdp attach=fulldatabase
username : / as sysdba
export> continue_client
SQL> select * from dba_datapump_jobs;
root> su - oracle
oracle> export ORACLE_SID=venu
oracle> sqlplus / as sysdba
SQL> startup
root> su - oracle
oracle> export ORACLE_SID=venu
oracle> expdp dumpfile=sys.dmp logfile=sys.log
username : / as sysdba
SQL> select count(*) from tab;
Check for the tables before starting the job and after ending the job.
default location :
/u01/app/oracle/venu/dpdump/sys.dmp
SQL> select * from dba_directories;
oracle> cd /u01/app/oracle/venu/dpdump/
oracle dpdump> ls
different location :
SQL> create directory dp as '/u03/dpdump';
root> mkdir -p /u03/dpdump
root> chown -R oracle:oinstall /u03/dpdump/
root> chmod -R 775 /u03/dpdump/
root> su - oracle
oracle> export ORACLE_SID=venu
SQL> drop user scott cascade;
oracle logical> imp file=thursday.dmp log=thursday.log inctype=system ignore=y full=y
username : / as sysdba
SQL> conn scott/tiger
oracle logical> imp file=sunday.dmp log=sunday.log inctype=restore ignore=y full=y
username : / as sysdba
root> su - oracle
oracle> export ORACLE_SID=venu
oracle logical> imp file=wedday.dmp log=wedday.log inctype=restore ignore=y full=y
username : / as sysdba
scott> select * from tab;
oracle logical> imp file=thursday.dmp log=thursday.log inctype=restore ignore=y full=y
username : / as sysdba
scott> select * from tab;
root> su - oracle
oracle> export ORACLE_SID=venu
oracle> sqlplus / as sysdba
SQL> startup
oracle logical> rm *
oracle logical> export ORACLE_SID=venu
oracle logical> exp file=sunday.dmp log=sunday.log inctype=complete direct=y statistics=none buffer=200000
username : / as sysdba
direct=y - bypass the SGA layer
inctype=complete - full database backup with timestamp
buffer=200000 - the number of database buffer cache(DBC) buffers to use
SQL> conn scott/tiger
SQL> alter user scott account unlock identified by tiger;
scott> select * from tab;
scott> insert into emp select * from emp;
error : unique constraint violated
scott> alter table emp disable primary key cascade;
scott> insert into emp select * from emp;
scott> commit;
scott> select count(*) from emp;
oracle logical> exp file=monday.dmp log=monday.log inctype=incremental direct=y statistics=none buffer=200000
username : / as sysdba
Here only the emp table is backuped as the data is changed in it.
If any problem occurs, drop the scott schema and recreate the schema again.
SQL> conn scott/tiger
scott> alter table dept disable primary key cascade;
scott> insert into dept select * from dept;
scott> insert into salgrade select * from salgrade;
scott> commit;
scott> select count(*) from dept;
scott> select count(*) from salgrade;
oracle logical> exp file=tuesday.dmp log=tuesday.log inctype=incremental direct=y statistics=none buffer=200000
username : / as sysdba
Here emp,dept and salgrade table will be backuped as there is a relation between emp and dept.
scott> insert into salgrade select * from salgrade;
scott> commit;
scott> select count(*) from salgrade;
oracle logical> exp file=wedday.dmp log=wedday.log inctype=cumulative direct=y statistics=none buffer=200000
username : / as sysdba
Here emp,dept and salgrade tables will bebackuped.
scott> insert into emp select * from emp;
scott> commit;
scott> select count(*) from emp;
oracle logical> exp file=thursday.dmp log=thursday.log inctype=incremental direct=y statistics=none buffer=200000
username : / as sysdba
Here only emp table will be backuped.
scott> conn / as sysdba
SQL> select * from dba_exp_version;
SQL> select * from dba_exp_files;
SQL> select * from dba_exp_objects;
This is one type of logical backup introduced in oracle 10g version.
Utilities of data pump are expdp and impdp.
Data pump is much faster compared to the traditional logical backup since it uses the API's like dbms_datapump and dbms_metadata.
Data pump is a server side utility which means it generates the dump file at the server side irrespective of the location from where we initiated the job.
The pre-requisite for data pump is we need to create a directory at oracle level as well as at operating system level.
By default, a user can take the backup of his own objects, incase of data pump user should have read,write privilege on the directory.
The biggest advantage of data pump is we can stop/detach the job whenever there is a performance issue.
Once we reinitiate the job, it starts from the point where it was stopped earlier(which means we can detach and attach a job).
Once we initiate the data pump job, a master control process gets invoked and it keeps track of the status of the job in a table created by the job name.
Recommanded to give a name for every job that we initiate with data pump.
If we dont specify the job name, oracle creates a table on its own naming convention.
Once the job is completed, table gets dropped automatically.
We can also take the backup parallally with the help of "parallel" parameter.
We can compress metadata as well as business data in the dump file with the help of "compress" paramerer.
We can also estimate the space required in the backup destination without actually taking the backup.
Data pump enables faster movement of data between the databases or across the databases.
Useful in case of database migrations and upgradations.
Also useful in case of schema level refreshes.
Note : If we give the same name for the dump file, exp overrides without any warning messages or error messages. expdp fails with error message "file already exists".
Note : We cannot take incremental backups using data pump.
In case of incremental backups, oracle takes the backup of only modified objects.
Note : The biggest dis-advantage is, even if a single record is modified, it considers the entire segment.
"inctype" is the parameter that we use to take the incremental backups. The possible options for inctype are :
1.complete
2.incremental
3.cumulative
inctype=complete/incremental/cumulative
inctype=complete is the base backup for incremental backups. It takes the backup of entire database and it also maintains some information in the data dictionary tables which is required for next incrementals.
inctype=incremental takes the backup of all modified objects till the last incremental or cumulative or complete whichever appears first.
inctype=cumulative takes the backup of all modified objects till the last cumulative or complete whichever appears first.
SQL> select name from v$tablespace;
SQL> select name from v$datafile;
SQL> drop tablespace ssss including contents and datafiles;
SQL> create tablespace ssss
datafile '/u02/app/oracle/ssss/ssss01.dbf'
size 2m autoextend on;
SQL> alter user u1 default tablespace ssss;
SQL> conn u1/u1
u1> select * from tab;
oracle logical> imp file=ts.dmp full=y
username : / as sysdba
u1> drop table emp purge;
oracle logical> imp file=meta.dmp fromuser=scott touser=u1
username : / as sysdba
u1> select * from tab;
u1> desc emp;
u1> drop table emp purge;
oracle logical> imp file=row.dmp fromuser=scott touser=u1
username : / as sysdba
u1> select * from tab;
u1> select count(*) from emp;
oracle logical> ls
SQL> conn u1/u1
u1> select * from tab;
u1> drop table emp purge;
u1> drop table dept purge;
u1> select * from tab;
oracle logical> imp file=table.dmp fromuser=scott touser=u1
username : / as sysdba
u1> select * from tab;
u1> drop table emp purge;
oracle logical> imp file=table.dmp tables=emp fromuser=scott touser=u1
username : / as sysdba
u1> select * from tab;
root> su - oracle
oracle> export ORACLE_SID=ssss
oracle> sqlplus / as sysdba
SQL> startup
root> su - oracle
oracle> eexport ORACLE_SID=ssss
oracle> ls
oracle> cd logical
SQL> conn scott/tiger
scott> select * from tab;
scott> conn / as sysdba
SQL> drop user scott cascade;
SQL> grant connect,resource to scott identified by tiger;
SQL> conn scott/tiger
scott> select * from tab;
oracle logical> imp file=user.dmp log=user.log fromuser=scott touser=scott ignore=y
username : / as sysdba
scott> select * from tab;
SQL> select name from V$tablespace;
SQL> select name from V$datafile;
SQL> create tablespace ssss
datafile '/u02/app/oracle/ssss/ssss01.dbf'
size 10m autoextend on;
SQL> grant connect,resource to u1 identified by u1;
SQL> alter user u1 default tablespace ssss;
SQL> conn u1/u1;
SQL> create table a(a number(3));
SQL> insert into a values(1);
SQL> /
repeat for many times.
SQL> commit;
SQL> select count(*) from a;
oracle logical> exp file=ts.dmp log=ts.log tablespace=ssss
username : / as sysdba
oracle logical> exp file=met.dmp log=meta.log tables=emp rows=n
username : scott/tiger
rows=n means backup only the meta data not the business data.
oracle logical> exp file=row.dmp log=row.log tables=emp query=\'where deptno=30\'
username : scott/tiger
oracle logical> exp file=table.dmp log=table.log tables=emp,dept
username : scott/tiger
oracle> export ORACLE_SID=ssss
oracle> sqlplus / as sysdba
SQL> alter user scott account unlock
oracle logical> exp file=user.dmp log=user.log owner=scott
username : / as sysdba
SQL> create pfile from spfile;
SQL> shut immediate
oracle> cd $ORACLE_HOME/dbs
oracle> mv spfilessss.ora spfilessss.ora.bkp
oracle> export ORACLE_SID=ssss
oracle> sqlplus / as sysdba
SQL> startup
SQL> show parameter pfile;
SQL> show parameter undo_
SQL> alter system set undo_retention=900 scope=memory;
If we wont specify the scope for dynamic parameter when running with pfile, it take "memory" by default.
"memory" means it is applicable for current.
SQL> show parameter undo_
oracle> export ORACLE_SID=ssss
oracle> sqlplus / as sysdba
SQL> startup
SQL> select name,open_mode,log_mode from V$database;
SQL> select instance_name,status from V$instance;
SQL> show parameter undo_
SQL> desc V$parameter
SQL> select name,value,issys_modifiable from V$parameter where name='undo_retention';
If the issys_mod is true/immediate then its "dynamic parameter".
If issys_mod is false, it means that its a static parameter.
SQL> show parameter spfile
SQL> show parameter pfile
SQL> alter system set undo_retention=1000 scope=both;
If we wont specify the scope for dynamic parameter when running with spfile, it take "both" by default.
"both" means it is applicable for current and future instance.
SQL> show parameter undo_
SQL> show parameter memory_
SQL> select name,value,issys_modifiable from V$parameter where name="memory_max_target";
If issys_mod is false, it means that its a static parameter.
SQL> alter system set memory_max_target=900m scope=spfile;
SQL> show parameter memory_
SQL> shut immediate
SQL> startup
We need to shut and start the database to see the effect of changed static parameter if the database is running with spfile.
SQL> show parameter memory_
