RAC Backup Scripts for NT Oracle Tips by Burleson Consulting

In order to use the supplied backup scripts for NT, the following procedure must be followed.

1.    Run nt_oline_bu.sql from SQL*Plus Remote DBA account.

2.    Run nt_rec_db.sql script from SQL*Plus Remote DBA account.

3.    Move a copy of the rec_db.bat script generated in step 2 to the backup directory.

4.    From an SQ*LPLUS command-line session (using the e:\orant81\bin\sqlplus executable), run the thot_bu.sql script generated in step 1.

5.    Once step 4 completes (should take less than two hours), copy the backup directory (i:\backup), using the system backup tool, to tape.

6.    Remove the archive logs that were copied from the database archive log destination to tape from the archive log destination.

Recovery 

In order to perform NT recovery using the provided scripts, the following procedure must be followed:

1.    Using the system backup tools, restore the Oracle backup files to the backup location on the database server (for example: i:\backup).

2.    Run the recovered copy of the rec_db.bat script to restore the backup files to their proper locations.

3.    Manually start the Oracle services and the tns listener process using the Control Panel Services icon.

4.    From the command line, use the svrmgrl executable to start up and mount (but not open) the database: 

>svrmgrl
svrmgrl>connect internal@fsys.world
password: xxxxxxxxx
connected to an idle instance
svrmgrl>startup mount pfile=e:\orant\database\init<SID>.ora  (Be sure to use the
location of your initialization file)
<will see normal startup messages>
svrmgrl> recover
<server will prompt for needed files; they should be already copied to machine, so just
press Return at each prompting>
        media recovery complete
svrmgrl> alter database open
        database altered

5.    Shut down and perform a cold backup of all database files (essentially, take the ocopy commands from inside the thot_bu.sql script and run them as a .bat file).  Do not back up the archive logs; after a cold backup they are not needed anymore.

6.    Remove all archive logs from system.

7.    Database has been recovered; resume normal operations.

The actual backup process can be automated on NT using the WINAT scheduler, available from the Microsoft Web site or the Microsoft support or toolkit CD-ROM. A script similar to the one shown in Source 15.5 should be used to start the backup.

SOURCE 15.5 Example of NT .bat script to start backup.

REM do_hot_bu.bat
REM File to generate and execute hot backup script for Oracle
REM Used for ORTEST1 database only
REM Mike Ault DMR Consulting 1/7/99
REM
REM First, generate the thot_bu.sql script
REM
cd c:\sql_scripts
REM
e:\orant81\bin\sqlplus -s system/manager@ortest1.world @c:\sql_scripts\nt_oline_bu.sql
REM
REM Now generate the recovery script so they are in-sync
REM
e:\orant81\bin\sqlplus -s system/manager@ortest1.world @c:\dmr_temp\nt_rec_db.sql
REM
REM Copy the recovery script to the backup destination
REM
copy c:\sql_scripts\rep_out\ortest1\rec_db.bat i:\backup\rec_db.bat
REM
REM Run the backup script
REM
e:\orant81\bin\sqlplus -s system/manager@ortest1.world
@c:\sql_scripts\rep_out\ortest1\thot_bu.sql
REM
REM End of script
REM
Exit

Using NT pass-in variables eliminates the need to store your username and password in the script. For an example of how this is done, look in your database directory for the Oracle shutdown script.

Imports/Exports

Imports and exports extract or insert an Oracle-readable copy of the actual data and structures in the database. The exports can be used to recover single data structures to the date and time the export was taken. Exports come in three types: full, cumulative, and incremental. Full, as its name implies, provides a full logical copy of the database and its structures. A cumulative provides a complete copy of altered structures since the last full or the last cumulative export. Incremental exports provide a complete copy of altered structures since the last incremental, cumulative, or full export.

Limitations on export/import are as follows:

* A database must be running to perform either an export or import.

* Export files shouldn’t be edited and can only be used by import.

* (Import only): Imports full tables; it can’t be used to do a conditional load.

* Exported data is only a logical copy of the data. An export can only allow recovery to the date and time the export was taken.

Imports and exports are accomplished using the Oracle IMPORT and EXPORT utilities.

EXPORT Utility

For exports, the EXPORT utility is used. The format for using this command follows:

Format:  EXP KEYWORD=value -or- KEYWORD=(list of values)

Example: EXP AULT/AUTHOR GRANTS=N TABLES=(CHAPTERS, EDITORS,ADVANCES)

Keyword                 Description (Default)

USERID                  User name/password.

BUFFER                 Size of data buffer.

FILE                       Output file (EXPDAT.DMP).

COMPRESS           Import into one extent (Y).

GRANTS                Export grants (Y).

INDEXES               Export indexes (Y).

ROWS                    Export data rows (Y).

CONSTRAINTS    Export table constraints (Y).

CONSISTENT       Cross-table consistency (N).

LOG                        Log file of screen output (None).

STATISTICS          Analyze objects (ESTIMATE).

DIRECT                  Bypass the SQL command processing layer (N) (new in Oracle8).

feedback           Show a process meter (a dot) every X rows exported (0 – X                                value).
HELP 

MLS, MLS_LABEL_FORMAT   Used with secure Oracle; not covered in this text.

FULL                     Export entire file (N).

OWNER                List of owner user names.

TABLES                List of table names.

RECORDLENGTH            Length of I/O record.

INCTYPE              Incremental export type.

RECORD              Track incremental export (Y).

PARFILE              Parameter file name.

Exports should be automated and scheduled to run automatically. An export methodology should be worked out such that the Remote DBA is reasonably certain a deleted file can be recovered. The parameters for export can either be placed on the command line or in a parameter file, which can then be accessed using the PARFILE command-line option.

IMPORT

The format of the IMPORT command follows:

Format:  IMP KEYWORD=value . . . or . . . KEYWORD=(list of values)

Example: IMP AULT/AUTHOR IGNORE=Y TABLES=(EXPENSES, ADVANCES) FULL=N

Keyword            Description (Default)

USERID            User name/password.

BUFFER           Size of data buffer.

FILE                 Output file (EXPDAT.DMP).

SHOW             Just list file contents (N).

IGNORE          Ignore create errors (N).

RECORDLENGTH      Length of I/O record.

GRANTS          Import grants (Y).

INDEXES         Import indexes (Y).

ROWS              Import data rows (Y).

LOG                 Log file of screen output.

INDEXFILE    Write table/index info to specified file.

FULL               Import entire file (N).

FROMUSER   List of owner user names.

TOUSER         List of user names.

TABLES          List of table names.

feedback    Provide dot status graph (0).

INCTYPE        Incremental import type.

COMMIT        Commit array insert (N).

PARFILE         Parameter file name.

DESTROY      Overwrite tablespace data (N).

CHARSET      Character set of export file (NLS_LANG).

Under Oracle7, the user must be granted the EXP_FULL_DATABASE role
in order to do full exports. In order to perform a full import, the user must have the IMP_FULL_DATABASE role. The users with the Remote DBA role are granted these implicitly.

An example of when the Remote DBA would want to grant these roles to a user would be a user whose password is specified in the command script used for doing the automatic exports. If the only role granted to the user is CREATE_SESSION and EXP_FULL_ DATABASE, even if the user’s password is compromised, he or she won’t be able to do much damage.

Archive Logs

The redo logs store all transactions that alter the database, all committed updates, adds, or deletes of tables, structures, or data. If archiving is disabled, see here for information on disabling archive log mode, only data in the current offline and online redo logs can be recovered. If the system recycles through all redo logs, the old ones are reused, destroying their contents. If  archive logging is enabled, the redo logs are written out to storage before reuse. Archive logging allows recovery to a specific point in time since the last full cold backup or complete offline backup. Under versions after Oracle8i, archive logs can be duplexed.  The initialization parameters that control archive logging are:

Parameter                                          Meaning

LOG_ARCHIVE_START                  If set to TRUE, start archive process.

LOG_ARCHIVE_BUFFERS             Number of log archive buffers.

LOG_ARCHIVE_BUFFER_SIZE     Size of the log archive buffers.

LOG_ARCHIVE_MIN_SUCCEED_DEST  Percentage of archive logs that must reach destinations.

LOG_ARCHIVE_DEST                     Primary archive log location.

LOG_ARCHIVE_DUPLEX_DEST    Secondary archive log location.

LOG_ARCHIVE_DEST_1                 Archive tertiary location 1.

LOG_ARCHIVE_DEST_2                 Archive tertiary location 2.

LOG_ARCHIVE_DEST_3                 Archive tertiary location 3.

LOG_ARCHIVE_DEST_4                 Archive tertiary location 4.

LOG_ARCHIVE_DEST_5                 Archive tertiary location 5.

LOG_ARCHIVE_FORMAT      Specifies the format for archive log names; use the “%s” and “%t” format specifiers to add the sequence and redo thread numbers to the format.

Under Oracle8, Oracle8i, and Oracle9i, redo logs are specified in groups; each group forms a shadow set and is archived together. Archive logs can also be assigned to threads for use in shared or RAC instances. Individual logs are called members. Threads hold groups that hold members. Each member of a redo log group is the same size and should be on separate physical platters or arrays. Oracle automatically synchronizes members of a group into a shadow set.

Redo logs cannot be used to recover a database brought back from a full export.

To switch a database that is not currently using archive logging to use archive logging, the steps are:

1.    Shut down database using immediate or normal options.

2.    Edit the initialization parameter file to include appropriate archive log parameters, at a minimum:

ARCHIVE_LOG_START = TRUE
ARCHIVE_LOG_DEST1 = destination (operating system specific path to archive log destination)
ARCHIVE_LOG_FORMAT = arch_%t_%s.arc 

Usually the defaults for LOG_ARCHIVE_BUFFERS and LOG_ARCHIVE_BUFFER_SIZE are sufficient.

3.    Using the appropriate interface (svrmgrl, sqlplus, OEM) start up the database in mounted mode:

sqlplus> connect sys/password as sysRemote DBA
sqlplus> startup mount (s)pfile=<initialization file location>

4.    Use the ALTER DATABASE command to reset the ARCHIVELOG mode:

sqlplus> ALTER DATABASE ARCHIVELOG;

5.    Use the ALTER DATABASE command to open the database:

sqlplus> ALTER DATABASE OPEN;

6.    Either shut down and perform a cold backup or perform a hot backup. Since this is the first backup, I would suggest a cold backup be used. This is the baseline backup of your database.

7.    Restart the database as you would normally.

Proper use of these backup/recovery tools allows the Remote DBA to recover from any possible failure.

See Code Depot for Full Scripts

---

This is an excerpt from Mike Ault, bestselling author of "Oracle 10g Grid and Real Application Clusters". You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.

Expert Remote DBA BC is America's oldest and largest Remote DBA Oracle support provider.  Get real Remote DBA experts, call BC Remote DBA today.