Take advantage of Oracle's own IMPDP and EXPDP for simple backups

There is a need to back up the data from the production library to the backup machine at 23 o'clock every night, the database of the backup machine can be used directly the next day, the data is the data of the production library yesterday. (production data is not much, all data is not more than 3000w).

With no exposure to advanced Oracle knowledge, only simple IMPDP and EXPDP tools are available, so it is intended to be done with this self-brought command and shell script.

Now assume that the production library IP is 192.168.1.20 (abbreviation 20), the backup library IP is 192.168.1.140 (abbreviation 140)

My idea is this: 20 machines perform automatic backups at 11 o'clock in the evening, and then try to copy the backup files to 140 machines, and then 140 machines for import.

Implementation process:

1. Install the NFS file system on 140 and share the specified directory to 20.

2. Add backup script on 20 use Crontab to execute EXPDP statement on time

#!/bin/SH
Oracle_base=/oracle
Export Oracle_base
Oracle_home= $ORACLE _base/product/
Export Oracle_home
Oracle_sid=orcl
Export Oracle_sid
Export path= $PATH: $ORACLE _home/bin
Export Data_dir=/oracle/admin/orcl/dpdump
Export Logs_dir=/oracle/admin/orcl/dpdump
Export Bakuptime= 'Date+%y%m%d%h '
Export Nls_lang=american_america. Al32utf8

Echo"starting Bakup ..."
Echo"bakup file Path/oracle/admin/orcl/dpdump/hjxd_$bakuptime.dmp"

EXPDP hjxd/hjxd Directory=data_pump_dir dumpfile=hjxd_$bakuptime.dmp schemas=hjxd

Echo"Bakup completed."
Echo"Start Delete ten day before."
Find/oracle/admin/orcl/dpdump/-mtime + --type F-name *.dmp[ab]-execRM-f {} \;
Echo"End Delete Ten day before."

3. Add a cron task on 20 copy the backed up files to the NFS shared directory

##!my Bash

myfilepath=/oracle/admin/orcl/dpdump/;
Filename=hjxd_ 'Date-D"1 day ago"+%y%m%d ' at. DMP;
CP/oracle/admin/orcl/dpdump/$filename $myfilepath

4.140 Use the Cron task to import the data files from the NFS shared directory that you copied over to the 140 database

Fullexp.log

Path= $PATH: $HOME/bin
Export PATH
Oracle_base=/oracle
Export Oracle_base
Oracle_home= $ORACLE _base/product/
Export Oracle_home
Oracle_sid=orcl
Export Oracle_sid
Export path= $PATH: $ORACLE _home/bin


Sqlplus sys/123456As Sysdba <<eof
@/oracle/admin/orcl/dpdump/impdp.sql;
Eof

Export Bakuptime= 'Date-D"a day ago"+%y%m%d23 ';
ChownOracle:oinstall/oracle/admin/orcl/dpdump/hjxd_$bakuptime.dmp;
Echo"starting IMPDP ...";
Echo"IMPDP file Path/oracle/admin/orcl/dpdump/hjxd_$bakuptime.dmp";
IMPDP hjxdsas/123456Directory=data_pump_dir dumpfile=hjxd_$bakuptime.dmp logfile=fullexp.log Remap_schema=hjxd:hjxdsas table_exists_ Action=replace

impdp.sql File

DropUserHjxdsasCascade;

CreateUserHjxdsas identified by123456
defaultTablespace hjxd
TemporaryTablespaceTemp;

GrantDbaCreate anyTrigger,Drop anyTable,SELECT anyTable,SELECT anySequenceCreateUser toHjxdsas identified by 123456;
GrantConnect, Resource toHjxdsas;
GrantExp_full_database,imp_full_database toHjxdsas;

At the beginning of the 4th step, the Oracle environment variable was set up because the copy of the file came from the root User (NFS requires that the user with the same uid,20,140 machine has the same Oracle user UID that does not necessarily agree with the root user) , including some errors in the execution of cron tasks with Oracle users, so the Oracle environment variable is also set to root, which is used to import data directly using root.

Take advantage of Oracle's own IMPDP and EXPDP for simple backups