Creating Script Files for EVM-Oracle Jobs

Creating EVM-Oracle Script Files

You can easily create EVM-Oracle script files using a text editor. The following procedures and examples illustrate how to design and implement script files for EVM jobs.

 

Running EVM-Oracle Script Files

When you create an EVM-Oracle job you can specify script files to be run by EVM’s Application I/O Handler. When the EVM job runs, the Application I/O Handler passes control to Oracle’s SQLPLUS command processor, which then runs the script file.

When specifying a script file in the Create EVM Job wizard, be sure to include the full path and script file name and enclose the entire entry in quotes. For example, to run a script file named evm-pre.sql, you might enter:

"/$ORACLE_HOME/bin/sqlplus "@evm-pre" > pre.log"

In this example the output from the script file is redirected to a file named pre.log. For many types of jobs this is an important step that will help you identify the Oracle redo logs that must be backed up with the tablespaces.

 

Overall Design and Testing Approach

Begin by planning the EVM-Oracle job. Is the job for: tape backups, testing, data mining or system migration? Identify the Oracle files to replicate. If necessary, configure the storage units to optimize speed and disk resources. Next, design the required script files.

Thoroughly test the script files and EVM-Oracle job under controlled conditions before running them in a production environment. You should carefully plan and evaluate changes that you might make to the database configuration, or to backup and recovery plans.


IMPORTANT: Many Oracle commands can disrupt database operations if not used correctly. If you are not familiar with Oracle commands, or do not have database administrator privileges, you should not design and implement EVM-Oracle SQL script files without proper technical assistance.


 

An Approach for EVM-Oracle Tape Backup Jobs

Identify which Oracle tablespaces and other files are to be replicated in the job. Be sure to plan for the inclusion of archived redo logs to restore the databases.

You should put tablespaces into Oracle’s online (hot) backup mode before the EVM job splits the BCV units. This ensures that replicated files can be recovered by Oracle and that dirty buffers are flushed to disk. You should remove tablespaces from Oracle’s online backup mode after the EVM job splits the BCV units. This causes Oracle to return to normal operation.

 

Sample Pre-split Script File

The following pre-split script file is part of an EVM-Oracle job that is designed to replicate Oracle tablespaces USR and WEB and back them up to tape. It is a conceptual example that you can use as a starting point to develop your own SQL script files. To use the following script, you must put the database into the archivelog mode.

Oracle script file name = evm-pre.sql

    ****
    connect internal/letmein;

    archive log list;

    alter tablespace USR begin backup;

    alter tablespace WEB begin backup;

    quit;

    ****

Line 1. The connect command is issued to the Oracle database with a specified username and password. In this example user = internal and password = letmein.

Line 2. The archive log command causes the current redo log to be closed and archived. The list parameter causes redo log information to be sent to SQLPLUS.

Line 3. The alter tablespace and begin backup commands start Oracle’s online backup mode for the USR table.

Line 4. The alter tablespace and begin backup commands start Oracle’s online backup mode for the WEB table.

Line 5. The quit command ends the Oracle script file, exits SQLPLUS and returns control to the EVM job.

 

Sample Archive Command Output

Archiving the Oracle redo log is an important step that produces output like:

    Database log mode Archive Mode
    Automatic archival Enabled

    Archive destination /archive

    Oldest online log sequence 266

    Next log sequence to archive 267

    Current log sequence 267

When an archive log command is issued, the current log sequence (267) is archived. Note that the oldest online log sequence number (266) is required for you to determine which archived redo logs should be backed up.

When the pre-split script file is through, control is returned to the EVM job which can then split the mirror set to create the new BCV units.

 

Sample Post-split Script File

The following post-split script file is part of an EVM-Oracle job that is designed to replicate Oracle tablespaces USR and WEB and back them up to tape. It is a conceptual example that you can use as a starting point to develop your own SQL script files. To use the following script, you must put the database into the archivelog mode.

 Oracle script file name = evm-post.sql

****
connect internal/letmein;

alter tablespace USR end backup;

alter tablespace WEB end backup;

archive log list;

alter system switch logfile;

quit;

****

Line 1. The connect command is issued to the Oracle database with a specified username and password. In this example user = internal and password = letmein.

Line 2. The alter tablespace and end backup commands stop Oracle’s online backup mode for the USR table.

Line 3. The alter tablespace and end backup commands stop Oracle’s online backup mode for the WEB table.

Line 4. The archive log command causes the current redo log to be closed and archived. The list parameter causes log information to be sent to SQLPLUS.

Line 5. The alter system and switch logfile commands stop the recording of the current log file and force Oracle to switch to the next sequential log file.

Line 6. The quit command ends the Oracle SQL script file, exits SQLPLUS and returns control to the EVM job.

 

Sample Archive Command Output

As with the corresponding pre-split script file, you must archive the database redo log. Output might now look like:

    Database log mode Archive Mode
    Automatic archival Enabled

    Archive destination /archive

    Oldest online log sequence 312

    Next log sequence to archive 313

    Current log sequence 313

When the archive log command is issued, the current log sequence (313) is archived. The current log sequence number (313) is required for you to determine which archived redo logs should be backed up.

When the post-split script file is through, control is returned to the EVM job which can then mount the BCV unit on the specified tape backup server.