DOCUMENTATION: How to back up and restore the Oracle control file and SPFILE using NetBackup for Oracle with Oracle's autobackup feature.

Article:TECH36428  |  Created: 2004-01-27  |  Updated: 2013-10-24  |  Article URL http://www.symantec.com/docs/TECH36428
Article Type
Technical Solution

Product(s)

Environment

Issue



DOCUMENTATION: How to back up and restore the Oracle control file and SPFILE using NetBackup for Oracle with Oracle's autobackup feature.

Solution



Oracle Recovery Manager (RMAN) Controlfiles store the metadata about backups, archived redo logs, and RMAN's own activities. The SPFILE (also called server parameter file) is an optional, single file which can be located in a selected path or the path auto-designated by Oracle when the file is created. It is similar to the init.ora file as a repository for initialization parameters, but is a binary file that cannot be browsed or edited with a text editor. If the SPFILE exists, it too will be backed up by the autobackup feature. Oracle's autobackup feature was introduced at Oracle 9i (specifically 9.2) as a method to easily and automatically capture the critical information in the control file and SPFILE to be used for disaster recovery.

How it works
Regardless of NetBackup-scheduled controlfile backups completed within a backup cycle or backup script, RMAN "AUTOBACKUP" will automatically store a copy of the controlfile that supersedes any previous copy.

If autobackup was used to do the controlfile backup, it must also be used for the controlfile restore. If autobackup is not configured correctly in the restore command, the error message "RMAN-06563: controlfile or SPFILE must be restored using FROM AUTOBACKUP." will be found in the NetBackup progress log or stdout.
Note: This document assumes NetBackup is already installed and Oracle database backups and restores are already successfully using NetBackup Media Manager for tape storage. All operations below are done as the Oracle user, unless otherwise noted.

Backup
NetBackup does not need any special configuration as long as NetBackup for Oracle backups are already operational for this client. There is no need to name a new policy "autobackup" for example, but keep in mind that the first available policy (alphabetically) that has an open window and supports Oracle backups and this host will be used for the backup.

Keep in mind that the RMAN config parameters used for backup must also be used for restore. Document any configuration changes for future reference.

Turning on "AUTOBACKUP"
If CONFIGURE CONTROLFILE AUTOBACKUP ON (an RMAN command) is issued (set to ON), RMAN performs a control file autobackup (automatic backup) either after every
* BACKUP or COPY command at the RMAN prompt, or
* BACKUP or COPY command within a RUN block (script) that is not followed by another BACKUP or COPY command

Exact steps:
1. On the Oracle host, start RMAN at the command prompt. You will now have an RMAN> prompt like this:
Recovery Manager: Release 9.2.0.1.0 - Production
Copyright (c) 1995, 2002, Oracle Corporation.  All rights reserved.
RMAN>

2. At the prompt, the current configuration can be displayed by typing this and pressing <Enter>:
show all;

You may get a reply similar to this:
RMAN configuration parameters are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default

3. If "CONFIGURE CONTROLFILE AUTOBACKUP OFF" is shown, it must be turned on. At the RMAN> prompt, type this:
CONFIGURE CONTROLFILE AUTOBACKUP ON; <Enter>

4. Now set the image name format to use "%F" so each image stored in the NetBackup catalog will have an easily identifiable date in the name. At the RMAN> prompt, type this:
SET CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE 'SBT_TAPE' TO '%F'; <Enter>

5. Finally, set 'SBT_TAPE' as the default output device for Oracle AUTOBACKUP backups. This creates the connection to the NetBackup for Oracle agent instead of disk.
At the RMAN> prompt, type this:
SET CONFIGURE DEFAULT DEVICE TYPE TO 'SBT_TAPE'; <Enter>

6. To manually test the control file backup to tape, at the RMAN> prompt, type this:
    backup current controlfile; <Enter>
Or
Run any database backup or script. When the backup completes, AUTOBACKUP will make a backup of the current control file.

*DONE WITH BACKUP


Understanding Oracle tape backup images:

The NetBackup bplist command will produce output that can be used to review all retained backup history.
Note: The control file format using "%F" adds a readable date to the image name. For example "20050718" is July 18, 2005. RMAN increments "1" to the last number string (sequence) for each autobackup that is performed in a day.

Run bplist as the root user on the client host.

# bplist -t 4 -l -R / > bp.list.out
# cat bp.list.out
-rw------- oracle    oracle        2097152 Jul 18 07:49 /c-791235730-20050718-02
-rw------- oracle    oracle        2097152 Jul 18 07:48 /cntrl_31_1_563960898
-rw------- oracle    oracle        2097152 Jul 18 07:47 /c-791235730-20050718-01
-rw------- oracle    oracle         262144 Jul 18 07:45 /al_29_1_563960680
-rw------- oracle    oracle        2097152 Jul 18 07:44 /al_28_1_563960680
-rw------- oracle    oracle        2097152 Jul 18 07:43 /c-791235730-20050718-00
-rw------- oracle    oracle      357826560 Jul 18 07:41 /bk_26_1_563959504
-rw------- oracle    oracle      169607168 Jul 18 07:25 /bk_25_1_563959504
-rw------- oracle    oracle        2097152 Jul 17 12:45 /c-791235730-20050717-00
-rw------- oracle    oracle        2097152 Jul 17 12:41 /0ngpojdq_1_1
-rw------- oracle    oracle        2097152 Jul 12 08:09 /cntrl_14_1_563443748
-rw------- oracle    oracle         262144 Jul 12 08:07 /al_13_1_563443618
-rw------- oracle    oracle       16777216 Jul 12 08:07 /al_12_1_563443618
-rw------- oracle    oracle      345505792 Jul 12 08:04 /bk_11_1_563443402
-rw------- oracle    oracle      163053568 Jul 12 08:03 /bk_9_1_563443307
-rw------- oracle    oracle       20971520 Jul 12 08:01 /bk_10_1_563443307

The same controlfile name format must be used for backup and restore. Here are the effects of two different name format configurations.

1. set controlfile autobackup format for device type sbt_tape to %F;
will produce a controlfile image named:
/c-791235730-20050718-02

2. set controlfile autobackup format for device type sbt_tape to ora_cf%F;
will produce a controlfile image named:
/ora_cfc-791235730-20050718-03


Restore
The RUN statement listed below is required. The commands will fail if run individually.

Also, while the DB is in NOMOUNT state, the configuration that is normally displayed with SHOW ALL; will not be available to RMAN. Therefore, all configuration data must be typed into a script or run command.

The restore will take 2-3 minutes per search date to locate or pass before determining if an "autobackup" image is available. The default is seven days back starting today. It can be adjusted using MAXSEQ and MAXDAYS flags in the restore command.


Restore from "AUTOBACKUP" controlfile
Overview of Steps
1. Log onto the host as the Oracle user
2. Launch sqlplus as "sysdba"
3. Start Oracle in NOMOUNT
4. Launch RMAN
5. Connect to the target database
6. Locate and set the DBID
7. Run the appropriate configuration and restore string
8. Restore database

Restore Example (starting with Oracle shutdown):
# su - oracle
Sun Microsystems Inc.   SunOS 5.8       Generic February 2000
*****************************************
$ sqlplus "/ as sysdba"

SQL*Plus: Release 9.2.0.1.0 - Production on Sun Jul 17 12:49:22 2005

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production

SQL> STARTUP NOMOUNT
ORACLE instance started.

Total System Global Area   43372604 bytes
Fixed Size                   454716 bytes
Variable Size              41943040 bytes
Database Buffers             819200 bytes
Redo Buffers                 155648 bytes
SQL> quit

$ rman

Recovery Manager: Release 9.2.0.1.0 - Production
Copyright (c) 1995, 2002, Oracle Corporation.  All rights reserved.

RMAN> connect target
connected to target database: yourdb (not mounted)

RMAN> SET DBID=791234567        
executing command: SET DBID

***Notes for the restore command string:
Line 3 must be the same as the configuration used for the backup.
Line 4 opens a channel to NetBackup.
Line 5 must be configured with MAXSEQ and MAXDAYS to search to the appropriate date in the NetBackup catalog to locate the desired controlfile backup image.
* MAXSEQ defines the maximum sequence number (default starts with 256).
* MAXDAYS is the number of days from the current date the restore will search back to locate a controlfile autobackup (default of 7, maximum of 366).
* SET UNTIL can also be used to direct the restore to the exact desired backup date.
Line 6 mounts the database so the controlfile data can be verified.
Line 7 [enter] means press the <Enter> key on the keyboard. Don't type [enter].

RMAN> run
2> {
3> set controlfile autobackup format for device type 'sbt_tape' to '%F';
4> allocate channel t1 device type 'sbt_tape';
5> restore controlfile from autobackup MAXSEQ 5 MAXDAYS 9;
6> mount database;
7> } [enter]

executing command: SET CONTROLFILE AUTOBACKUP FORMAT

allocated channel: t1
channel t1: sid=12 devtype=SBT_TAPE
channel t1: VERITAS NetBackup for Oracle - Release 4.5MP6 (00000000)

Starting restore at 01-AUG-05

channel t1: looking for autobackup on day: 20050801
channel t1: looking for autobackup on day: 20050731
channel t1: looking for autobackup on day: 20050730
channel t1: looking for autobackup on day: 20050729
channel t1: looking for autobackup on day: 20050728
channel t1: looking for autobackup on day: 20050727
channel t1: looking for autobackup on day: 20050726
channel t1: looking for autobackup on day: 20050725
channel t1: autobackup found: c-791235730-20050725-02
channel t1: controlfile restore from autobackup complete
replicating controlfile
input filename=/extra/u01/bobsdb/control01.ctl
output filename=/extra/u01/bobsdb/control02.ctl
output filename=/extra/u01/bobsdb/control03.ctl
Finished restore at 01-AUG-05

database mounted
released channel: t1

RMAN>

*DONE WITH RESTORE

Continue restoring databases or tablespaces as needed using the history information in the controlfile.

Note: In Oracle 9.2 and later, an alternative to the above restore procedure could be used by selecting and restoring a backup of a database and controlfile. The restore of the database will also restore the controlfile at the same time. Contact Oracle for details on this streamlined procedure.





Legacy ID



274022


Article URL http://www.symantec.com/docs/TECH36428


Terms of use for this information are found in Legal Notices