Oracle automatic backup and automatic backup steps _oracle

Data is a central part of the application, the program has broken the replacement machine can be republished, but once the data loss, resulting in loss will be irreversible, the program released to production, data backup is particularly important, because not all services have funds to complete advanced backup such as RAC and DG, In our only one database server, temporarily take the simplest backup strategy, export out DMP to save.

First, backup scripts

1, initialization variable, record start log

#变量
sysname= Fill in your own system name
syspath=/home/oracle/databak/$sysname
v_date=$ (date ' +%y%m%d%h%m%s ')
# Log directory
Logfile=${syspath}/backup.${v_date}.log
#打印开始时间
echo backup_time>>${logfile}
Date ' +%y-%m-%d%h:%m:%s ' >> ${logfile}
echo "Backup JCD" >>${logfile}
echo "Start_time" >>${ LogFile}
Date ' +%y-%m-%d%h:%m:%s ' >> ${logfile}

2, enter the Oracle directory, set Oracle parameters

#进入oracle目录, set Oracle variable
cd/home/11g/app/oracle/product/11.2.0
export oracle_home=/home/11g/app/oracle/ Product/11.2.0/db_1
export path= $PATH:/home/11g/app/oracle/product/11.2.0/db_1/bin

3. Export Data

echo $syspath
v_date=$ (date ' +%y%m%d%h%m%s ')
filename= $syspath/jcd${v_date}.dmp
#导出命令
Exp Username/password File=${filename} compress=n >> ${logfile}
echo "End_time" >>${logfile}

4, Compressed Dump package

5. Use the SCP to pass the compressed package to the backup server, noting that the directory on the backup server needs to be created beforehand

Second, set the script execution time

We set the rules to be executed 2 o'clock in the morning every day, using crontab for processing.

Execute CRONTAB-E, set the following rules

0 2 * * * sh/home/oracle/db.sh

Iii. about SCP command password handling

When executing the SCP command, you are prompted to enter the password for the remote host
SCP ${filename}.gz remot_name@remot_ip:/home/weblogic/databak/Backup directory
There are two ways to deal with this:

1, the use of encrypted documents for authentication

1 into the ~/.ssh directory of the Local host

2) Run Ssh-keygen-t [RSA|DSA], will generate the key file and private key file Id_rsa,id_rsa.pub or id_dsa,id_dsa.pub, where we use the RSA method

3 Modify the generated Id_rsa and Id_rsa.pub permissions to 700

4 Copy the. pub file to the remote host's ~/.ssh directory, execute cat id_rsa.pub >> ~/.ssh/authorized_keys, and handcuff the contents to the Authorized_keys file

5) Modify authorized permission to 700

Completion of the above changes, from the local host SCP to the remote host will not need a password, a simple understanding should be the time of communication, the local host to send the SCP instructions, the local private key to sign, the remote host using the public key verification, Judge asked host can trust, open communication services.

2, using the expect command to complete the password interaction

The expect command is primarily used in scripts where manual intervention is necessary, in which case, the simulated scene captures the text that prompts for instructions using the expect command, and automatically uses the Send command to enter commands that require interaction when the text appears.

Use here as:

Expect "*password:" #此处填写系统提示文字
Send "Your password\r" #此处填写我们输入的交互命令
Expect EOF

Let me introduce you to the detailed steps of Oracle automatic backup

Background: Oracle 10g Server, Oracle 10g client, Windows XP operating platform
Requirements: The Oracle database server automatically backs up the database YTCN once a day.
Solution: Automatically generate Oracle backup files based on dates with task scheduling, batch files, and Oracle's EXP export capabilities. Detailed steps:

1 Creating batch Files Ytcn.bat

The details of Ytcn.bat are as follows:

@echo off
Echo is backing up the Silver Network Oracle database, please wait a moment ...
Exp userid= ' YTCN/YTCN@YTCN as Sysdba ' File=e:/bak/ytcn/oracle/ytcn/ytcn%date:~0,4%%date:~5,2%%date:~8,2%.dmp log=e :/bak/ytcn/oracle/ytcn/ytcn%date:~0,4%%date:~5,2%%date:~8,2%.log full=y

Echo Mission complete!
Where the red part is changed according to the need, such as the author's project name "Silver Pass Net", database YTCN username YTCN, password ytcn, to create the form such as "Ytcn20090711.dmp" and "under the Directory" E:/BAK/YTCN/ORACLE/YTCN " Ytcn20090711.log "Backup and log files, full table export.
In addition: The value of%date% in different systems, language versions may not be the same, control panel inside the setting of the regional options will also change the value of%date%. Test the Echo%date% return value on the command line first. %date:~4,10% is the return date function, the first parameter is the starting position to intercept (starting from 0), the second parameter is the length to intercept, if no is intercepted to the last, parameters can be modified as appropriate. If you need accurate time to do the file name, please use the%time% function, parameter ibid.

2 Add a task schedule YTCN

Start > All Programs > Attachments > System Tools > Task Scheduler > Add Task Schedule > Next > Browse to find just written ytcn.bat file > Task Name Input YTCN, perform this task select every day, next > Start 12:00, start date 2009-7-11, Next > enter username and password, username requires Administrator privileges username, Next > complete
After clicking "Finish", a task plan named "YTCN" will be added under the task plan to indicate that it has been configured.

Note: Sometimes after clicking "Finish", the system warns

"A new task has been created, but may not run because account information cannot be set.

The specified error is:

Ox80041315: The Task Scheduler service is not running

This is because the computer's Task Scheduler service did not start up. Start > All Programs > Admin tools > Services, find the "task Scheduler" service, find the startup type is disabled, right-click to change to "auto", and start it up, and then add the task schedule again YTCN on it.