Oracle backup and recovery scripts, part III: Backup and recovery under Windows NT

This is the third of a series of excerpts from "Oracle DBA automation scripts" by Rajendra Gutta. Click for part one on cold backup under Unix and part two on hot backup under Unix. You can purchase the book here.

This section presents and explains the scripts for taking a backup and recovering a database in the Windows NT environment. We use the DOS Shell batch programming techniques to automate the backup process. After the backup is complete, it is important to check the backup status by reviewing log and error files generated by the scripts.

Cold Backup

Listing 3.9 performs a cold backup of a database under the Windows NT environment. The cold backup script takes SID, the instance to be backed up, as the input parameter. The general steps to write a backup script in Unix and Windows NT are the same. The only difference is that we will be using commands that are understood by Windows NT. Figure 3.6 shows the functionality of a cold backup program under Windows NT. Each box represents a corresponding section in the program. For example, the Parameter Checking section checks for the necessary input parameters and also checks for the existence of the backup directories.

[IMAGE]

Listing 3.9 coldbackup_nt.bat

Cold Backup Script for Windows NT Checklist

• Check to see that ORA_HOME, BACKUP_DIR, and TOOLS are set to correct values according to your directory structure. These variables are highlighted in the script.

• Verify that CONNECT_USER is set to correct the username and password.

• Define the INIT_FILE variable to the location of the Init.ora file.

• Be sure that the user running the program has Write access to backup directories.

• When you run the program, pass SID as a parameter.

Cold ...

To continue reading for free, register below or login

Requires Membership to View

To gain access to this and all member only content, please provide the following information:

Email Address:

By joining SearchOracle.com you agree to receive email updates from the TechTarget network of sites, including updates on new content, magazine or event notifications, new site launches and market research surveys. Please verify all information and selections above. You may unsubscribe at any time from one or more of the services you have selected by editing your profile or unsubscribing via email.

TechTarget cares about your privacy. Read our Privacy Policy

To read more you must become a member of SearchOracle.com

As an existing member of the TechTarget network please activate your SearchOracle.com account 

By joining SearchOracle.com you agree to receive email updates from the TechTarget network of sites, including updates on new content, magazine or event notifications, new site launches and market research surveys. Please verify all information and selections above. You may unsubscribe at any time from one or more of the services you have selected by editing your profile or unsubscribing via email.

TechTarget cares about your privacy. Read our Privacy Policy

Digg This!     StumbleUpon     Del.icio.us

RELATED CONTENT Hot backups Hot backups of Oracle E-Business Suite Questions about user-managed hot backups Taking a hot backup on Linux Performing a hot backup with RMAN Questions about backup and recovery Advantages of using RMAN in a 24x7 environment Step-by-step backup procedures Try this simple hot backup script Oracle backup and recovery scripts, part II: Hot backup under Unix Hot backup and recovery with Perl Oracle database administrator Understanding SQL string functions What is the difference between a database engineer, architect and administrator? Import on one table from dump file Error during RMAN backup Can I drop a column in SYS schema? STATSPACK tool: transaction vs. execution measurement Should I port from Microsoft Access? How can I find statistics on total memory usage and database connections? Installing multiple Oracle homes Modifying SYS password in a RAC environment Cold backups Is a cold backup needed in addition to daily hot backups? Cold backup on Unix server What files need to be backed up under 9i for a cold backup? Back up an Oracle database on a Linux platform Moving datafiles from production to development by cold backup and renaming Cold backup using RMAN Step-by-step backup procedures Try this simple cold backup script Oracle backup and recovery scripts, part I: Cold backup under Unix

RELATED RESOURCES 2020software.com, trial software downloads for accounting software, ERP software, CRM software and business software systems Search Bitpipe.com for the latest white papers and business webcasts Whatis.com, the online computer dictionary

Backup under Windows NT Troubleshooting and Status Check

Backup log files defined by LOG_FILE contain detailed information about each step of the backup process. This is a very good place to start investigating why a backup has failed or for related errors. This file will also have the start and end time of backup. ERR_FILE has error information.

A single line about the success or failure of backup is appended to the SID.log file every time a backup is performed. This file is located under the directory defined by the LOGDIR variable. The messages for a cold backup are 'COLDBACKUP_FAIL', if the cold backup failed, and 'Cold Backup Completed successfully', if the backup completes successfully.

You can schedule automatic backups using the 'at' command, as shown in the following:

This command runs a backup at 23:00 hours every Monday, Tuesday, Wednesday, Thursday, and Friday.

The "Create Dynamic Files" section in the coldbackup_nt.bat program creates the coldbackup.sql file (see Listing 3.10) under the log directory. coldbackup.sql is called from coldbackup_nt.bat and generates a list of data, control, and redo log files to be backed up from the database. A sample coldbackup.sql is shown in Listing 3.10 for your understanding. The contents of this file are derived based on the structure of the database.

Listing 3.10 coldbackup.sql

When the coldbackup.sql file is called from the coldbackup_nt.bat program, it spools output to the coldbackup_list.bat DOS batch file (see Listing 3.11). This file has the commands necessary for performing the cold backup.

This is only a sample file. Note that in the contents of file data, control, redo log, and Init.ora files are copied to respective backup directories.

Listing 3.11 coldbackup_list.bat

Hot Backup

The hot backup program (see Listing 3.12) performs a hot backup of a database under the Windows NT environment. The hot backup script takes SID, the instance to be backed up, as the input parameter.

Listing 3.12 hotbackup_nt.bat

Hot backup program functionality can be shown with the similar diagram as for a cold backup. The sections and their purposes in the program are the same as for a cold backup.

Hot Backup Script under Windows NT Checklist

• Check to see that ORA_HOME, BACKUP_DIR, and TOOLS are set to the correct values according to your directory structure. These variables are highlighted in the script.

• Verify that CONNECT_USER is set to the correct username and password.

• Define the INIT_FILE variable to the location of the Init.ora file.

• Define the ARC_DEST variable to the location archive destination.

• Be sure that the user running the program has Write access to the backup directories.

• When you run the program, pass SID as a parameter.

Hot Backup under Windows NT Troubleshooting and Status Check

The backup log file defined by LOG_FILE contains detailed information about each step of the backup process. This is a very good place to start investigating why a backup has failed or for related errors. This file will also have the start and end time of backup. ERR_FILE has error information.

A single line about the success or failure of backup is appended to the SID.log file every time a backup is performed. This file is located under the directory defined by the LOGDIR variable. The messages for a hot backup are 'HOTBACKUP_FAIL', if a hot backup failed, and 'Hot Backup Completed successfully', if a backup completes successfully.

The "Create Dynamic Files" section, in the hotbackup_nt.bat creates the hotbackup.sql file (see Listing 3.13) under the log directory. This generates a list of tablespaces, data, control, and redo log files from the database. It is called from the hotbackup_nt.bat program.

Listing 3.13 hotbackup.sql

The hotbackup.sql file is called from hotbackup_nt.bat and it spools output to the hotbackup_list.sql SQL file (see Listing 3.14). This file has the commands necessary for performing a hot backup.

This is only a sample file. Note in the file that the data, control, archive log, and Init.ora files are copied to their respective backup directories. First, it puts the tablespace into Backup mode, copies the corresponding files to backup location, and then turns off the Backup mode for that tablespace. This process is repeated for each tablespace, and each copy command puts the status of the copy operation to hbackup.log and reports any errors to the herrors.log file.

Listing 3.14 is generated based on the structure of the database. In a real environment, the database structure changes as more data files or tablespaces get added. Because of this, it is important to generate the backup commands dynamically, as shown in hotbackup_list.sql. It performs the actual backup and is called from hotbackup_nt.bat.

Listing 3.14 hotbackup_list.sql

Export

The export program (see Listing 3.15) performs a full export of the database under a Windows NT environment. The export script takes SID, the instance to be backed up, as the input parameter.

Listing 3.15 export_nt.bat

This program performs an export of the database by using the parameter file specified by export_par.txt. In Listing 3.16 is a sample parameter file that performs a full export of the database. You can modify the parameter file to suit to your requirements.

Listing 3.16 export_par.txt

Export Script under Windows NT Checklist

• Check to see that ORA_HOME and BACKUP_DIR, TOOLS are set to correct values according to your directory structure. These variables are highlighted in the program.

• Verify that CONNECT_USER is set to the correct username and password.

• Be sure that the user running the program has Write access to the backup directories.

• Edit the parameter file to your specific requirements. Specify the full path of the location of your parameter file in the program.

• When you run the program, pass SID as a parameter.

Export under Windows NT Troubleshooting and Status Check

The log file specified in the parameter file contains detailed information about each step of the export process. This is a very good place to start investigating why an export has failed or for related errors.

A single line about the success or failure of export is appended to the SID.log file every time an export is performed. This file is located under the directory defined by the LOGDIR variable. The messages for an export are 'EXPORT_FAIL', if the export failed, and 'Export Completed successfully', if the export completes successfully.

Reader Feedback:

Frank P. writes: Your article on Windows backups for Oracle is incorrect. In a hot backup, Oracle recommends the use of the supplied OCOPY utility, NOT Windows copy. See Metalink note #139327.1. Also, I believe that the scripts are using O/S authentication. This has major security risks and should be noted. Otherwise, good tip.

Do you have feedback to share? Send it in!

Rate this Tip To rate tips, you must be a member of SearchOracle.com. Register now to start rating these tips. Log in if you are already a member. DISCLAIMER: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.