oracle backup and restore,Oracle RMAN Backup and Restore with EMC networker

DBA Notes: 2011/10/25

Cheng Li

Oracle Backup & Restore by using EMC networker

Backup Strategy for Oracle Database

Backup Method: Online database backup

Type: Physical

Requirements:RMAN

The database environment has been implemented on a UNIX platform. In the production platform,the database is operated in ARCHIVELOG mode,a cron jobis set to run at night to backup the database files into the NetWorker server. The following types of files should be backed up for each backup cycle:

§Datafiles

§Control Files

§Redo Log Files

§Archive Log Files

§Parameter Files

§Password Files

In order to minimize the data loss due to database failures, another cron job is used tocopy the archive log filesinto the NetWorker server constantly.

Backup Scripts Appied with crontab:

Backup Scripts:

$ cat rman_level0_backup_db_using_compressed

spool log to '/u01/app/oracle/product/10.2.0/rman_script/log/rman_level0_backup.log' append;

connect target sys/veritas@TRSPRD;

CONFIGURE CONTROLFILE AUTOBACKUP ON;

run {

set command id to 'rman_ctmtrs';

allocate channel t1 type 'SBT_Tape';

send 'NSR_ENV=(NSR_DATA_VOLUME_POOL=6Year Backup)';

backup as compressed backupsetskip readonly incremental level = 0 filesperset 4

format '/TRS10gLVL0_%d_%U/'

(database) ;

backup as compressed backupset(CURRENT CONTROLFILE format '/df_U%U_1/');

sql 'alter system archive log current';

change archivelog all crosscheck;

backup as compressed backupsetfilesperset 10

(archivelog all delete input format '/al_%s_%p/');

release channel t1;

}

Comments for backup scripts:

1. Set Command ID

Run the SET COMMAND ID command in the RMAN job, then the CLIENT_INFO column displays in the following format:rman channel=channel_id

For example, the following shows sample output:SID SPID CLIENT_INFO

---- ------------ ------------------------------

14 8374 rman channel=rman_ctmtrs

If more than one RMAN session is active, it is possible for theV$SESSION.CLIENT_INFOcolumn to yield the same information for a channel in each session. For example:SID SPID CLIENT_INFO

---- ------------ ------------------------------

14 8374 rman channel=rman_ctmtrs

9 8642 rman channel=rman_ctmcms

2. EMC networker "Send" Commandsend 'NSR_ENV=(NSR_DATA_VOLUME_POOL=6Year Backup)'

To send the data from a manual or scheduled Oracle backup to a specific pool,the NSR_DATA_VOLUME_POOL variable can be set in the RMAN backupsession, in this case "6year Backup" pool.

Restoration From NetWorker

The restroration of the backup files from Networker takes the following steps:

a.Start a X-session to the MPSDB server and execute the Networker recover process:

/usr/opt/networker/bin/nwrecover &

Recovery in Oracle database

After the required files are restored, the database recovery can be carried out by the following steps:

a.Duplicate backup control file:

cd /u01/app/oracle/admin/MPSPRD/udump

cp control.20060515153904 /u01/oradata/MPSPRD/control01.ctl

cp control.20060515153904 /u01/oradata/MPSPRD/control02.ctl

cp control.20060515153904 /u01/oradata/MPSPRD/control03.ctl

b. Mount the database by using SQL*Plus:

mpsdb.ctm.plc.cwintra.com> sqlplus /nolog

SQL*Plus: Release 10.1.0.3.0 - Production on Tue May 16 13:06:05 2006

Copyright (c) 1982, 2004, Oracle.All rights reserved.

SQL> conn / as sysdba

Connected to an idle instance.

SQL> startup

ORACLE instance started.

Total System Global Area524288000 bytes

Fixed Size1303744 bytes

Variable Size145234752 bytes

Database Buffers377487360 bytes

Redo Buffers262144 bytes

Database mounted.

ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

c. Start the database recovery:

SQL> recover database using backup controlfile until cancel;

ORA-00279: change 9497257 generated at 05/15/2006 15:39:05 needed for thread 1

ORA-00289: suggestion : /u01/oradata/MPSPRD/archive/MPSPRD_585601254_1_538.arc

ORA-00280: change 9497257 for thread 1 is in sequence #538

d. Select automatic recovery:

Specify log: {=suggested | filename | AUTO | CANCEL}

auto

ORA-00279: change 9498856 generated at 05/15/2006 15:59:13 needed for thread 1

ORA-00289: suggestion : /u01/oradata/MPSPRD/archive/MPSPRD_585601254_1_539.arc

ORA-00280: change 9498856 for thread 1 is in sequence #539

ORA-00278: log file '/u01/oradata/MPSPRD/archive/MPSPRD_585601254_1_538.arc' no

longer needed for this recovery

ORA-00279: change 9500763 generated at 05/15/2006 16:19:42 needed for thread 1

ORA-00289: suggestion : /u01/oradata/MPSPRD/archive/MPSPRD_585601254_1_540.arc

ORA-00280: change 9500763 for thread 1 is in sequence #540

ORA-00278: log file '/u01/oradata/MPSPRD/archive/MPSPRD_585601254_1_539.arc' no

longer needed for this recovery

e. Reset the log sequence number and open the database:

ORA-00279: change 9501713 generated at 05/15/2006 16:34:34 needed for thread 1

ORA-00289: suggestion : /u01/oradata/MPSPRD/archive/MPSPRD_585601254_1_548.arc

ORA-00280: change 9501713 for thread 1 is in sequence #548

ORA-00278: log file '/u01/oradata/MPSPRD/archive/MPSPRD_585601254_1_547.arc' no

longer needed for this recovery

ORA-00308: cannot open archived log

'/u01/oradata/MPSPRD/archive/MPSPRD_585601254_1_548.arc'

ORA-27037: unable to obtain file status

Compaq Tru64 UNIX Error: 2: No such file or directory

Additional information: 3

SQL> alter database open resetlogs;

Database altered.

f. Create the Temporary tablespace:

SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/oradata/MPSPRD/temp01.dbf'

2SIZE 2297M REUSE AUTOEXTEND ON NEXT 655360MAXSIZE 32767M;

Tablespace altered.

g. Restart database:

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

Total System Global Area524288000 bytes

Fixed Size1303744 bytes

Variable Size145234752 bytes

Database Buffers377487360 bytes

Redo Buffers262144 bytes

Database mounted.

Database opened.

SQL>

Reference: