Tutorial on setting up scheduled tasks to backup the Oracle database under Linux

1. Check the character set of the database

The character set of the database must be consistent with the environment variables set under Linux, otherwise there will be garbled characters.

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 as the oracle user and create related directories

Create a directory for backup data: mkdir -p /home/oracle/dbbak/data

Create a 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

Contents of 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 backup..."
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 content according to your needs:

export DELTIME=`date -d "30 days ago" +%Y%m%d` configures the time to retain data. Here it is 30 days. You can modify it according to your needs and the storage space of the server;
export NLS_LANG=AMERICAN_AMERICA.AL32UTF8 character encoding format, which must be consistent with the result in the first step;
exp DISPLAY/DISPLAY@orcl file=$DATA_DIR/orcl$BAKUPTIME.dmp log=$LOGS_DIR/orcl$BAKUPTIME.log

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

4. Modify the execution permissions of the script file

chmod 777 dbbak.sh

Test whether it can be executed: ./dbbak.sh

Export terminated successfully without warnings. This means there are no problems with the export. If there are any problems, solve them.

5. Add scheduled tasks

Under Oracle user

crontab –e

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

Indicates that the script will be executed at 23:59 every day

Restart crond:

service crond restart

6. Import dmp file

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

sqlplus user/pwd // User name and password to log in to the 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 errors and continue execution

Summarize

The above is what I introduced to you about setting up scheduled tasks to backup the database for Oracle under Linux. I hope it will be helpful to you. If you have any questions, please leave me a message and I will reply to you in time. I would also like to thank everyone for their support of the 123WORDPRESS.COM website!
If you find this article helpful, please feel free to reprint it and please indicate the source. Thank you!