Linux设置每晚定时备份Oracle数据表的操作命令

export ORACLE_BASE=/home/oracle/app
export ORACLE_HOME=$ORACLE_BASE/oracle/product/11.2.0/dbhome_1
export ORACLE_SID=orcl
export PATH=$PATH:$HOME/bin:$ORACLE_HOME/bin
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/usr/lib
d=$(date '+%Y%m%d')
expdp MPMS_NY/MPMS@ORCL DIRECTORY=DATA_PATH DUMPFILE=$d.DMP LOGFILE=$d.LOG CLUSTER=N
zip -m /home/oracle/backup/$d.zip /home/oracle/backup/$d.DMP /home/oracle/backup/$d.LOG

--创建数据表空间

createtablespace test_data

logging

datafile '/u01/app/oradata/test/TEST.dbf'

size32m

autoextendon

next32m maxsize 2048m

extent managementlocal;

--创建用户并指定表空间

createuserTEST identifiedby123

defaulttablespace test_data

temporarytablespacetemp;

--给用户授予权限

grantconnect,resourcetoTEST;

createtablet1(

Idvarchar(50)primarykey,

titlevarchar(50)

);
insertintot1values(sys_guid(),'t1');

insertintot1values(sys_guid(),'t2');
commit;

export ORACLE_BASE=/u01/app

export ORACLE_HOME=/u01/app/oracle

export ORACLE_SID=TEST

export PATH=$ORACLE_HOME/bin:$PATH

d=$(date '+%Y%m%d')

exp TEST/123@TEST file=/home/oracle/backup/$d.dmp log=/home/oracle/backup/$d.log owner=TEST

zip -m /home/oracle/backup/$d.zip /home/oracle/backup/$d.dmp /home/oracle/backup/$d.log

chmod +x backup.sh

[oracle@localhost backup]$ crontab -e

42 13 * * * /home/oracle/backup/backup.sh