Copy the daily EXPDP export file of an Oracle database to a remote server using shell scripting

Copy the daily EXPDP export file of an Oracle database to a remote server using shell scripting

Zhao Full Text Network name: Guestart

We have a production environment Oracle database, although the daily Rman backup, but also overwhelmed by the developer go out said to restore a few days before the table data into the production environment, in response to such requirements, using Rman to recover a few tables of data is particularly laborious. So I decided to use the logical Backup tool of the Oracle database EXPDP (data pump export) to specifically export data for all tables under a particular user, and to do so at 4 o'clock in the morning every day, scheduled execution of the crontab of the Linux operating system. In this way, developers need to restore a few tables, directly using the IMPDP (Data Pump Import) tool to import some tables. here, it is important to note that when importing, it is necessary to add parameter remap_table, which is intended to be an easy-to-distinguish name from the table to be imported and to avoid overwriting the data of the original table. The recommended naming method is the current date of the original table name, which makes it more readable. See parameter remap_table instructions for use:

650) this.width=650; "Src=" https://s1.51cto.com/wyfs02/M02/8D/FF/wKioL1iy2vnh8ll-AAA9Nj2z6tA976.png-wh_500x0-wm_ 3-wmp_4-s_4282534755.png "title=" 11.png "alt=" Wkiol1iy2vnh8ll-aaa9nj2z6ta976.png-wh_50 "/>

650) this.width=650; "Src=" https://s2.51cto.com/wyfs02/M00/8D/FF/wKioL1iy2xWiC9W6AAAUT1bE6AE284.png-wh_500x0-wm_ 3-wmp_4-s_139014653.png "title=" 12.png "alt=" Wkiol1iy2xwic9w6aaaut1be6ae284.png-wh_50 "/>

Above is a solution to the problem that can satisfy the developer to restore some tables, but such an export file (*. DMP) generated a large amount of capacity per day, and is stored locally, it is conceivable that the local storage capacity is limited, and can not keep the DMP files generated every day, long time, the server disk storage space will be a burst of drops. Therefore, the use of a DMP file purge retention policy, that is, only one copy of the current day of the DMP data pump export file, the same day's DMP file through the SCP command remote replication to another Linux server (dedicated to the DMP file, the capacity of 50T, 3.6T per disk, a total of 14 disks) , after copying, and also clear yesterday's DMP files, so that the local server always keep a copy of the day's DMP files, so that the local server disk space capacity problem is solved.

If the daily use of the SCP command to copy, as to see how many copies, when the end of the copy, is an uncontrolled link. Here the operation of the SCP command is written into a shell script to generate a copy of the progress of the log file, and every day to execute the SH script, without manual intervention, so that the efficiency is greatly improved.

Because the remote server disk space is also about 50T, where each disk has 3.6T, when the first disk space is not much, such as yesterday's DMP file copied to this disk, its free space has accounted for 93%, that is, the remaining space is only 7%, The DMP file generated today should be stored on the second disk. Therefore, the condition should be judged in the sh script. That is, to see how much of the remaining space is currently used on the disk, and how many grams of dmp files are generated on that day, the capacity of the former should be greater than the capacity of the latter to save the DMP files on this disk. Otherwise, it indicates that the block has insufficient disk space and that the DMP file must be saved on the next disk.

Of course, to read the remote server's current save DMP file on the disk of the remaining space capacity, use SSH to connect to the remote server, if the local server does not configure the SSH Trust remote server function, to SSH to connect to the remote server, you need to enter the remote server password to connect, This is known as the interaction between servers. There is no interaction in the SH script we are going to show below, otherwise the automation of the script is lost.

To do this, we first configure the local server SSH to trust the remote server function, so that the local server using SSH command to connect directly to the remote server. The procedure is shown in the following steps:

650) this.width=650; "Src=" https://s2.51cto.com/wyfs02/M00/8D/FF/wKioL1iy3CuR1caSAABZ_8m2Pho848.png-wh_500x0-wm_ 3-wmp_4-s_1448388675.png "title=" 1.png "alt=" Wkiol1iy3cur1casaabz_8m2pho848.png-wh_50 "/>

The above arrows indicate 3 places, all directly to enter. An error occurred when copying the contents of the SSH public key file generated by the local server to the remote server.

650) this.width=650; "Src=" https://s3.51cto.com/wyfs02/M02/8D/FF/wKioL1iy3IaA-Kz0AAANV9uVTuo602.png-wh_500x0-wm_ 3-wmp_4-s_3959494843.png "title=" 3.png "alt=" Wkiol1iy3iaa-kz0aaanv9uvtuo602.png-wh_50 "/>

after querying the Google Docs, it was found that the SSH public key file generated by the local server was not specified with parameter-I, and that the current server's operating system is Redhat Linux Server 5.4 x86_64. (while on the CentOS 6.6 Server x86_64 operating system, directly can use the Ssh-copy-id remote host name without error) of which, the query Google operation steps See,

650) this.width=650; "Src=" https://s4.51cto.com/wyfs02/M00/8E/01/wKiom1iy3PKAxXNVAACgVX0TTmI873.png-wh_500x0-wm_ 3-wmp_4-s_3357526183.png "title=" 4.png "alt=" Wkiom1iy3pkaxxnvaacgvx0ttmi873.png-wh_50 "/>

650) this.width=650; "Src=" https://s1.51cto.com/wyfs02/M02/8E/01/wKiom1iy3RKx5NpAAAEU7bXTUEk171.png-wh_500x0-wm_ 3-wmp_4-s_331821710.png "title=" 5.png "alt=" Wkiom1iy3rkx5npaaaeu7bxtuek171.png-wh_50 "/>

650) this.width=650; "Src=" https://s4.51cto.com/wyfs02/M00/8D/FF/wKioL1iy3TDRH2_jAACF0PDKZ7I431.png-wh_500x0-wm_ 3-wmp_4-s_702992998.png "title=" 6.png "alt=" Wkiol1iy3tdrh2_jaacf0pdkz7i431.png-wh_50 "/>

Now copy the contents of the SSH public key file generated by the local server to the remote server, enter Yes and enter the password for the Oracle user of the remote server. See shown,

650) this.width=650; "Src=" https://s4.51cto.com/wyfs02/M01/8E/01/wKiom1iy3YCRAeprAABgOLJEJIs636.png-wh_500x0-wm_ 3-wmp_4-s_2839507760.png "title=" 7.png "alt=" Wkiom1iy3ycraepraabgoljejis636.png-wh_50 "/>

The following SSH command directly connects to the remote server, for example,

650) this.width=650; "Src=" https://s3.51cto.com/wyfs02/M02/8D/FF/wKioL1iy3l7RI0ZiAAAPmWi4hFs444.png-wh_500x0-wm_ 3-wmp_4-s_1400922439.png "title=" 9.png "alt=" Wkiol1iy3l7ri0ziaaapmwi4hfs444.png-wh_50 "/>

After configuring the local server SSH to trust the remote server's function, the following is to remotely copy the EXPDP file through the SCP command SH script content to show, hey!

Because the sh script has too much content, I divide it into four parts to introduce,

1. Personal information (as a propaganda for yourself), the name of the script and some features and precautions to use, see,

650) this.width=650; "Src=" https://s2.51cto.com/wyfs02/M00/8E/01/wKiom1iy3sjzLU1OAADQafM83V0472.png-wh_500x0-wm_ 3-wmp_4-s_3860437069.png "title=" 13.png "alt=" Wkiom1iy3sjzlu1oaadqafm83v0472.png-wh_50 "/>

2. Import the. Bash_profile environment profile under the Oracle user and the global variable that defines the absolute path of all shell external variables to be used, as shown in

650) this.width=650; "Src=" https://s1.51cto.com/wyfs02/M00/8E/01/wKiom1iy3y3wcHt1AABcPSSknks831.png-wh_500x0-wm_ 3-wmp_4-s_2405507566.png "title=" 14.png "alt=" Wkiom1iy3y3wcht1aabcpssknks831.png-wh_50 "/>

3. Switch to the same directory as the DMP file, the shell global variable that defines the current date and the total directory of the DMP file, the remote server disk string, the log file generated by the SCP command, and so on, see

650) this.width=650; "Src=" https://s4.51cto.com/wyfs02/M00/8D/FF/wKioL1iy33-h7QomAABmZTIINiQ570.png-wh_500x0-wm_ 3-wmp_4-s_3438697065.png "title=" 15.png "alt=" Wkiol1iy33-h7qomaabmztiiniq570.png-wh_50 "/>

4. Define the global function Scp_expdp_parallel (SCP copy with parallel export DMP file) and call it at the end, see,

Of these, there are 3 points to pay attention to,

(1) In the entire function, the use of the two-layer for loop , the outer layer is used to switch the remote server's next disk, the interior is used to replicate the current generation of DMP file content, and the previous day's DMP files are cleared, because the local server disk storage space is limited, Using only the DMP file of the day, so every time you look at the EXPDP directory, you will find only one copy of the day's DMP file, which will achieve the purpose I want to achieve.

(2) The red-labeled if condition judgment Statement is the disk space capacity comparison that I explained earlier, if the remaining free space on the remote server is larger than the total DMP file capacity of the current day on the local server, copy the DMP file to this disk, otherwise, Just execute the continue command in the Else statement, which jumps out of the inner for Loop, reads off a disk, and copies the DMP file to the next disk.

(3) The percentage of replication progress in the execution of the SCP command is changed at all times, and the process cannot be written directly to the log file, so do not trust everyone to try? here, You must use the script command at any time to crawl the value of the SCP command execution and log to the corresponding log log file, that is, after the script command using "-A parameter log file location" to implement the change value is appended to the log file, Instead, use the "-C parameter SCP command" after the script command to implement the remote copy operation.

650) this.width=650; "Src=" https://s2.51cto.com/wyfs02/M02/8E/02/wKiom1iy4DSiRMJsAAB4KMIYLQA123.png-wh_500x0-wm_ 3-wmp_4-s_3664563069.png "title=" 16.png "alt=" Wkiom1iy4dsirmjsaab4kmiylqa123.png-wh_50 "/>

If you feel that this article is helpful to you, please follow the public number: Guestart's DBA study notes, your support is my greatest encouragement!

650) this.width=650; "src=" Https://s2.51cto.com/wyfs02/M00/8E/02/wKiom1iy4KaSp_hbAADnPdGUWRk770.jpg "title=" Subscription number QR code. jpg "alt=" wkiom1iy4kasp_hbaadnpdguwrk770.jpg "/>

This article from the "Blue Melancholy" blog, reproduced please contact the author!

Copy the daily EXPDP export file of an Oracle database to a remote server using shell scripting