Tutorial of Setting Timing Task Backup Database in Oracle under Linux

1. View the character set of the database

The character set of the database must be set with the environment variable 1 set under Linux, otherwise there will be garbled codes.

The following two sql statements can be found:

select * from nls_database_parameters t where t.parameter='NLS_CHARACTERSET';
select * from v$nls_parameters where parameter='NLS_CHARACTERSET';

The query result is: NLS_CHARACTERSET AL32UTF8

2. Log in to the oracle server with oracle user and create relevant directories

Directory for creating backup data mkdir-p/home/oracle/dbbak/data

Create the backup script directory mkdir-p/home/oracle/dbbak/shell

Create the Export Data Log Directory mkdir/home/oracle/dbbak/log

3. Create a script file in the script directory

vi /home/oracle/dbbak/shell/dbbak.sh

Content in the script:

#[plain]view plaincopy
#!/bin/sh
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/dbhome_1
export ORACLE_SID=orcl
export PATH=$PATH:$HOME/bin:$ORACLE_HOME/bin
export DATA_DIR=/home/oracle/dbbak/data
export LOGS_DIR=/home/oracle/dbbak/log
export DELTIME=`date -d "30 days ago" +%Y%m%d`
export BAKUPTIME=`date +%Y%m%d%H%M%S`
export NLS_LANG=AMERICAN_AMERICA.AL32UTF8
mkdir -p $DATA_DIR
mkdir -p $LOGS_DIR
echo "Starting bakup..."
echo "Bakup filepath$DATA_DIR/$BAKUPTIME.dmp"
exp user/pwd@orcl file=$DATA_DIR/orcl$BAKUPTIME.dmp log=$LOGS_DIR/orcl$BAKUPTIME.log
echo "Delete the file bakup before 30days...filepath: $DATA_DIR/orcl$DELTIME*.dmp "
rm -rf $DATA_DIR/orcl$DELTIME*.dmp
rm -rf $LOGS_DIR/orcl$DELTIME*.log
echo "Delete the file bakup successfully."
echo "Bakup completed."

Modify the relevant contents according to your own needs:

export DELTIME=`date -d "30 days ago" +%Y%m%d`  Configure the time to retain data, where it is retained 30 Days, according to their own needs and the storage space of the server can be modified accordingly; 
export NLS_LANG=AMERICAN_AMERICA.AL32UTF8  Character encoding format, which needs to be followed by the 1 Result preservation in step query 1 To; 
exp DISPLAY/DISPLAY@orcl file=$DATA_DIR/orcl$BAKUPTIME.dmp log=$LOGS_DIR/orcl$BAKUPTIME.log

Export statement, full table export, can be modified as needed, exp username/password @ SID file=dmp file full path log=log file full path

4. Modify the execution permissions of script files

chmod 777 dbbak.sh  

Can the test be executed:./dbbak. sh

Export terminated successfully without warnings. Indicates that there is no problem with export. If there is a problem, solve it

5. Add Timing Tasks

Under Oracle user

crontab �e

Insertion: 59 23 ***/home/oracle/dbbak/shell/dbbak. sh

Indicates that the script is executed at 23:59 every day

Restart crond:

service crond restart 

6. Import the dmp file

Before importing, you need to delete all tables under the corresponding users to avoid problems in table data after importing:

sqlplus user/pwd  //  User name and password login database 
SQL> SELECT 'drop table ' || table_name || ';' FROM USER_TABLES ORDER BY TABLE_NAME;

Then execute the query results

Execute this line to avoid garbled table field comments:

    export NLS_LANG=AMERICAN_AMERICA.AL32UTF8;
 Import data: 
imp DISPLAY/DISPLAY file=/home/oracle/dbbak/data/orcl20191121113703.dmp fromuser=DISPLAY touser=DISPLAY ignore=y;
// ignore=y  Ignore the error and continue 

Summarize