20. Logical backup : backup of a single query - sayanbhattacharya1234/Oracle_DBA GitHub Wiki

Logical backup i want to take backup of a single query

conn to HR and query : select employee_id from employees where department_id=10;

expdp system/oracle directory=expbackup dumpfile=query.dmp logfile=qry.log tables=hr.employees query='hr.employees:"where department_id = 10" '

.

.

We have a PAR file its a plain text file , and the entire thing can be written inside the PARfile , and we just call the PARfile by export. it is like a script .

.

we create a file , vi exp.par

userid=system/oracle

directory=expbackup

dumpfile=query1.dmp

logfile=qry2.log

tables=hr.employees

query='hr.employees:"where department_id=20" ' save and exit

.

for execute > expdp parfile=exp.par

vi exp.par

IMPORT A BACKUP impdp

i have a backup now i want to import that backup

suppose i have a schema backup , now i want to restore this in database

drop the user hr ;

check - select username from dba_users where username =hr;

now i want to restore this hr user from my expdp backup - we create a new directory for import so [ mkdir /u01/imp_restore ]

cp /u01/exp_backup/schema_backup.dmp /u01/imp_restore [ need only the dmp file ]

for imp_restore i need to create a logical directory , [ SQL >create or replace directory imprestore as '/u01/imp_restore';

give grant [ grant read,write on directory imprestore to system; ]

now $> impdp system/oracle directory=imprestore dumpfile=schema_backup.dmp logfile=sclog.log schemas=hr .

##.

Restore hr user with different username like harry >

impdp system/oracle directory=imprestore dumpfile=schema_backup.dmp logfile=resclog.log remap_schema=hr:harry .

##.

we cannot take a backup or restore of a tablespace directly , first we create a tablespace and make sure a datafile exist.

how we know and check what tablespace backup i have , for this

suppose we backup prod tablespace on database A and restore database B , here prod tablesapce doesn't exist, so we can't restore it , so we manually create prod tablespace and we can restore.