Backup Oracle user data through the shell and send patrol results to Windows Springboard

background: The production environment has Oracle server, there are several database users, need to back up the database user data through exp, need to patrol the results of database backup.

Objective: to perform periodic backups via Linux Shell+exp+crontab, and to transfer the results of Linux backup to Windows across systems via SSH commands.

Description

Oracle Client 11g Oracle SERVER:ORACLE11G

Operating system: SENTOS6 or Redhat6

Port: 1521

Oracle _sid = Shwhcq

Operation Process:

First, backup

1. Create a Directory

Log in to the system with an Oracle user

Mkdir/home/oracle/script------Script Directory

MKDIR/IPI_BAK/DB/GG-------Data file backup destination directory (here to Oracle user W permissions)

Note: Here I export data as a user, so create subdirectories under/ipi_bak/db/gg with the names of each user

2. Create a script

Take the XSSP user's export script as an example: VI xssp-exp.sh

#!/bin/Bash Export Oracle_base=/app/Oracleexport Oracle_home= $ORACLE _base/product/11.2.0/Db_1export Oracle_sid=Shwhcq1export LANG=Cexport Nls_lang="simplified Chinese_china. ZHS16GBK"Export Oracle_term=Xtermexport PATH= $ORACLE _home/bin:/usr/sbin: $PATHexport ld_library_path= $ORACLE _home/lib:/lib:/usr/Lib time= ' Date"+%f_%h:%m:%s"`/grid/11.2.0/grid_1/bin/exp Xssp/[email protected]IP/SHWHCQ grants=y file=/ipi_bak/db/gg/xssp/xssp_$time.dmp log=/ipi_bak/db/gg/xssp/xssp_$time.log OWNER=XSSP & >/dev/NULL

To execute permissions: chmod +x xssp-exp.sh

Note: The above code is for Oracle database run account Oracle's system environment variable setting, must be added, otherwise crontab task schedule cannot be executed.

3. Create a Scheduled task

[Email protected] script]$ CRONTAB-E

5 2 * * *        /bin/sh  /home/oracle/script/xssp-exp.sh #每天02:05 points executed 2 *       * * /bin/sh  /home/oracle/script/artcms-exp.sh2 * * *       /bin/sh  /home/ oracle/script/caee_scan-exp.sh2 * * *       /bin/sh  /home/oracle/script/ ipijbpm-exp.sh2 * * *       /bin/sh  /home/oracle/script/ipiqxt-exp.sh

Note: crontab execution record in/var/log/cron

Second, the backup result output to the Windows Springboard machine

1. Output the last line of the backup log to the specified file, and the file is named by date

cat/home/oracle/script/db_bak.sh

#!/bin/Bashtime= ' Date"+%f"' Xssp_log=/ipi_bak/db/gg/xssp/Xssp_$time.logartcms_log=/ipi_bak/db/gg/artcms/Cms_$time.logcaee_log=/ipi_bak/db/gg/caee/Caee_$time.logipiqxt_log=/ipi_bak/db/gg/ipiqxt/Ipiqxt_$time.logipijbpm_log=/ipi_bak/db/gg/ipijbpm/Ipijbpm_$time.logDBLog=/home/oracle/db_baklog/$TIME. Logecho"Xssp_log">>$dblogtail-1$XSSP _log >>$dblogecho"Artcms_log">>$dblogtail-1$artcms _log >>$dblogecho"Caee_log">>$dblogtail-1$caee _log >>$dblogecho"Ipiqxt_log">>$dblogtail-1$IPIQXT _log >>$dblog Echo"Ipijbpm_log">>$dblogtail-1$IPIJBPM _log >> $dblog

2. Use the SSH SCP command on Windows to download the backup execution results log so that you don't have to check in every time you log in

Batch processing: Cat_linux_bak.bat

CD D:\Program Files (x86) \ssh Communications security\ssh Secure Shellscp2.exe [email protected] Database backup server IP:/ Home/oracle/db_baklog/*  D:\ipi_bak\script\linux_dbbak_logs\

Note: You want Linux and win to automatically transfer files through scripting without interacting . Requires trust configuration for Linux and win: http://blog.csdn.net/jiangshouzhuang/article/details/50683049

Brief description: We put the public key generated on Windows to the. SSH directory under the specified user home directory on the Linux server, and add the key contents to the Authorized_keys file under the. SSH directory. If we start the remote Linux (server) over SSH from Windows (client), then the client software makes a request to the server and requests security authentication with the key. After the server receives the request, it looks for the public key in the home directory on the server and compares it to the public key that is sent over. If the two keys are the same, the server encrypts the "challenge" with a public key and sends it to the client software. After the client software receives a "challenge" can be decrypted with the private key to send it to the server, because the key can match, so you can directly log on to the Linux server. 1. Generate the key on Windows native D:\SSHCommunications security\ssh Secure shell>ssh-keygen2-t RSA Note: We use RSA keys here, the default is 2048bit (bit), also we can use the DSA method of the key. When you execute ssh-keygen2-t RSA generate key pair, you will be prompted to enter the value of passphrase, we require password-free login to the server, so directly enter. There are hints: Private Key saved to C:/users/administrator/applicationdata/ssh/userkeys/id_rsa _2048_a Public key saved to C:/users/administrator/applicationdata/ssh/userkeys/id_rsa_ 2048_a.pub Follow the prompts to know the path to the generated key pair, but after I actually find the key pair for my Windows environment is located under: C:\Users\Administrator\AppData\Roaming\SSH\UserKeys. 2. Then we upload the Id_rsa_2048_a.pub file in the C:\Users\Administrator\AppData\Roaming\SSH\UserKeys directory of Windows to the Linux server Home/user Under the/.ssh directory. Because our public key is generated on Windows, the Linux OpenSSH is not recognized, so it needs to be converted and then appended to Authorized_keys: $ ssh-keygen-i-F id_rsa_2048_a.pub >>authorized_keys Quit the SSH Secure Shell client that you signed in with your password, then log in again and select Public key in the Login authentication status bar, so you can password-free. 3. Testing Download the Linux server files using the SCP2 command from the SSH Secure Shell client D:\SSH Communications security\sshsecure shell>scp2.exe-r-d [email Protected]:/home/user/testhivedata D:\test\ .000000_0.CRC |  12B | 12b/s | toc:00:00:01 | 100% 000000_0 |  96B | 96b/s | toc:00:00:01 | 100% Datacity.txt | 95kB | 95kb/s | toc:00:00:01 | 100% You can see that the remote copy does not need to enter a password.

Finally, view the results of the execution: you can easily view Oracle's backup results later

Backup Oracle user data through the shell and send patrol results to Windows Springboard