Best Practices: Oracle 9i User Managed Backup & Recovery

Tips:
Oracle Certification - Tips

DBA Preparation - Tips

Practical DBA - Tips




In this article i will descripe some good scnerios of backup and recovery option with Oracle 9i.

Backup and Recovery is a primary work for Oracle DBA. so every DBA must know every possible way to recover and backup their database.

There is mainly two option for taking HOT backup of Oracle Database.
1. RMAN
2. User Managed

NOTE: Both method required ARCHIVELOG mode.

1. How to enable Archivelog Mode

2. What is HOT backup?

In this article I will show you following point regarding backup and recovery in practical view.

1. How to take Online User Managed backup in Oracle 9ir1

After taking Proper database backup it is always recommended backup testing, becuase if backup is not valid then it is useless.

Through below two method we can use for TEST database backup which taken by User Managed.

1. Best Practices is to restore and recover backup on different host

2. Using DBVERFIY utility check physical corruption of backup

Oracle 9i Restore Backup on different host

As a DBA this task we perform so many times.

In the following cases.

1. Create CLONE database

2. Create same copy as PRODUCTION database

3. TEST Production Database Backup

4. Update TEST & UAT database with Production Database

To complete above fours point we require Production Database Backup, In am using Oracle 9ir1 backup for this demostration.

How to Take Oracle 9i Online User Managed Backup

The following steps will required to complete above task.

1. Create Oracle Services

2. Create Password file

3. Edit following parameter location

1. bdump 2. udump 3. cdump 4. log_archive_dest 5. controlfile location

4. connect with SYSDBA user and create spfile from backup pfile

then startup database in nomount mode

5. Restore controlfile from backup and mount the database

6. If source and target database file directory structure is different then rename all datafile to new target location.

7. after rename all datafile & redolog files location restore all archivelog files from backup and open database with UNTIL CANCEL option.

(In this demo target or source db location is same)

8. open database with RESETLOGS.

C:\Ora9ihome\BIN>oradim -new -sid ORA9I

C:\Ora9ihome\BIN>orapwd file=C:\Ora9ihome\database\pwdORA9I.ora password=oracle entries=5

C:\Ora9ihome\BIN>set oracle_sid=ora9i

C:\Ora9ihome\BIN>sqlplus /nolog
SQL*Plus: Release 9.0.1.0.1 - Production on Sat Aug 2 17:21:38 2008
(c) Copyright 2001 Oracle Corporation.

All rights reserved.

SQL> conn sys as sysdba

Enter password:

Connected to an idle instance.

SQL> create spfile from pfile='e:\ora9i\backup\init.ora';
File created.

SQL> startup nomount

ORACLE instance started.
Total System Global Area 118255568 bytes

Fixed Size 282576 bytes

Variable Size 83886080 bytes

Database Buffers 33554432 bytes

Redo Buffers 532480 bytes

SQL> host copy e:\ora9i\backup\*.ctl c:\ora9ihome\oradata\ora9i

e:\ora9i\backup\CONTROL01.CTL

e:\ora9i\backup\CONTROL02.CTL

e:\ora9i\backup\CONTROL03.CTL

3 file(s) copied.

SQL> alter database mount;
Database altered.

SQL> host copy e:\ora9i\backup\*.dbf c:\ora9ihome\oradata\ora9i

e:\ora9i\backup\INDX01.DBF

e:\ora9i\backup\SYSTEM01.DBF

e:\ora9i\backup\TOOLS01.DBF

e:\ora9i\backup\UNDOTBS01.DBF

e:\ora9i\backup\USERS01.DBF

5 file(s) copied.

SQL> set autorecovery on

SQL> recover database using backup controlfile until cancel;

SQL> alter database open resetlogs;
Database altered.

Oracle 9i Online User Managed Backup

Online backup through User Managed.

Oracle Version: 9.0.1.1.1

OS: Windows (But you can apply below procedure for all platforms)

Step:1

Before going further understand what is important files for backup.

1. Controlfile

2. Datafiles

3. SPFILE/PFILE

4. Redolog files

5. Tempfiles

First three points and files are most important and part of the database. so in online hot backup we need to take only first tell files backup.

Forget about Point 4,5, why...? becuase redolog files copy we get as a "archivelog files" and tempfile is temparory files, this file doesn't store user data. and doesn't important & required for database. suppose if we lost tempfile then we create new tempfile for database.

So we need to consider only three points from 1 to 3 (above).

The procedure is follow

1. Put tablespace in backup mode.

2. copy the datafiles which tablespace in backup mode

3. once copy process is complete putout the tablespace from backup mode

4. switch the logfile.

For example:

The following is the tablespace which we need to take backup.

SQL> select tablespace_name from dba_data_files;
TABLESPACE_NAME

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

SYSTEM

UNDOTBS

INDX

TOOLS

USERS

SQL> alter tablespace SYSTEM begin backup;
Tablespace altered.

SQL> host copy c:\ora9ihome\oradata\ora9i\system01.dbf e:\ora9i\backup

1 file(s) copied.

SQL> --we can check which tablespace/datafile is currently in backup mode

SQL> select * from v$backup;
FILE# STATUS CHANGE# TIME

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

1 ACTIVE 120148 02-AUG-08

2 NOT ACTIVE 0

3 NOT ACTIVE 0

4 NOT ACTIVE 0

5 NOT ACTIVE 0

SQL> alter tablespace SYSTEM end backup;
Tablespace altered.

SQL> select * from v$backup;
FILE# STATUS CHANGE# TIME

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

1 NOT ACTIVE 120148 02-AUG-08

2 NOT ACTIVE 0

3 NOT ACTIVE 0

4 NOT ACTIVE 0

5 NOT ACTIVE 0

Repeat above steps for all tablespace which we want to take backup. always check v$backup view for any datafile is still in backup mode, if you found any which datafile already copied to backup location and still showing in backup mode then putout that tablespace in backup mode through "alter tablespace TABLESPACE NAME end backup;"

Once you complete all datafile backup then force log switch.

SQL> ALTER SYSTEM SWITCH LOGFILE;
System altered.

After taking all datafiles backup now turn is controlfile backup.

SQL> alter database backup controlfile to

'e:\ora9i\backup\control01.ctl';

Database altered.

Above statement create BINARY controlfile backup copy.

After datafiles and controlfile backup now turn is init.ora (spfile) backup.

SQL> create pfile='e:\ora9i\backup\init.ora' from spfile;
File created.

---here Oracle 9i backup procedure is complete---

Verifying User-Managed Backups

We should periodically verify our backups to ensure that they are usable for recovery.

Testing the Restore of Backups

Running the DBVERIFY Utility

Making User-Managed Backups

Making User-Managed Backups of Archived Redo Logs

To save disk space in our primary location, we may need to backup archived log file to tape drive or alternative disk.
if we have multiple archivelog location then only back up one copy of each log sequence number.

To back up archived redo logs:
To determine which archived redo log files that the database has generated

SELECT THREAD#,SEQUENCE#,NAME FROM V$ARCHIVED_LOG;

NOte: backup up copy of archivelog through OS utility.


Making Logical Backups with Oracle Export Utilities

Oracle import and export utilities move Oracle data in and out of Oracle databases. Export utilities write exported database objects to operating system files in an Oracle-proprietary format. Import utilities can read the files produced by export utilities and re-create database objects. Logical exports of data can be a useful supplement to physical database backups in some situations, especially in backing up recovery catalog databases.

There are two sets of Oracle database import and export utilities: Original Import and Export (which were used in previous releases(<= 10g) and Data Pump Import and Export (new for Oracle Database Release 10g). The Data Pump utilities offer better performance and more complete support of features of Oracle Database Release 10g.

Export
Import
Export Dump Pump


Making User-Managed Backups of Miscellaneous Oracle Files

Always back up initialization parameter files, networking and configuration files, and password files. If a media failure destroys these files, then you may have difficulty re-creating your environment. For example, if you back up the database and server parameter file but do not back up the networking files, then you can restore and recover the database but will not be able to authenticate users through Oracle Net until you re-create the networking files.

INIT PARAMETER FILES


Keeping Records of Current and Backup Database Files

One of the most important aspects of user-managed backup and recovery is keeping records of all current database files as well as the backups of these files. For example, you should have records for the location of the following files:

Datafiles and control files
Online and archived redo logs (note that online logs are never backed up)
Initialization parameter files
Password files
Networking-related files


Recording the Locations of Datafiles, Control Files, and Online Redo Logs


select name from v$datafile
union all
select name from v$controlfile
union all
select member from v$logfile;


Recording the Locations of Archived Redo Logs


SELECT NAME, VALUE
FROM V$PARAMETER
WHERE NAME LIKE 'log_archive_dest_%'
AND VALUE IS NOT NULL


Determine the format for archived logs by running SHOW as follows


SHOW PARAMETER LOG_ARCHIVE_FORMAT

Making User-Managed Backups of Online Tablespaces and Datafiles

We can take all or individual datafiles backup when database is open but the procedure is differs depending on whether the online tablespace is READ-WRITE or READ-ONLY mode.

Note: You should not backup temporary tablespace

Tablespace is READ-WRITE mode.
we must put tablespace in backup mode when tablespace is read-write mode, online and database is open.

alter tablespace tablespace_name begin backup;
through OS utility copy datafiles to backup location
alter tablespace tablespace_name end backup;
alter system archive log current;

For multiple tablespace backup we can use

alter database begin backup;
through OS utility copy all datafiles to backup location
alter database end backup;
alter system archive log current;


Ending a Backup After an Instance Failure or SHUTDOWN ABORT

During backup period if database instance failure or power failure or shutdown abort command is issue. and when we try to open database after fix mention error then we will get below error message

ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: 'C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\SYSTEM01.DBF'

Then first check V$BACKUP views.

SQL> select * from v$backup;

FILE# STATUS CHANGE# TIME
---------- ------------------ ---------- ---------
1 ACTIVE 3224323 27-SEP-07
2 ACTIVE 3224323 27-SEP-07
3 ACTIVE 3224323 27-SEP-07
4 ACTIVE 3224323 27-SEP-07

All datafiles is still in backup mode...oh, so we need to take out all datafiles from backup mode.

SQL> alter database end backup;

Database altered.

SQL> select * from v$backup;

FILE# STATUS CHANGE# TIME
---------- ------------------ ---------- ---------
1 NOT ACTIVE 3224323 27-SEP-07
2 NOT ACTIVE 3224323 27-SEP-07
3 NOT ACTIVE 3224323 27-SEP-07
4 NOT ACTIVE 3224323 27-SEP-07

SQL> alter database open;

Database altered.


Keep in mind

1. Do not use ALTER DATABASE END BACKUP if you have restored any of the affected files from a backup.

2. We can only use "alter database end backup" statement when database is mount mode.

3. We can also use "RECOVER DATABASE" command instead of "alter database end backup" statement but it is slow process.

Making User-Managed Backups of Read-Only Tablespaces

We tablespace is READ-ONLY mode then no need to put tablespace in BACKUP mode. becuase oracle server already prevent to make changes on tablespace.

Making User-Managed Backups of Offline Tablespaces and Datafiles

We can take all or individual tablespace backup while tablespace is OFFLINE. and all other tablespace is remain available and open for systemwide use.


Procedure

Identify datafiles associate with tablespace

SQL> select tablespace_name,file_name
2 from dba_data_files
3 where tablespace_name = 'USERS';

TABLESPACE_NAME FILE_NAME
-------------------- --------------------------------------------------
USERS C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\USERS01.DBF

SQL> alter tablespace users offline normal;

Tablespace altered.

SQL> --copy datafiles to backup location
SQL> alter tablespace users online;

Tablespace altered.

SQL> alter system archive log current;

System altered.


Keep in mind

1. We can't take system tablespace or any active undo segment tablespace OFFLINE.
2. Assume that a table is in tablespace Primary and its index is in tablespace Index. Taking tablespace Index offline while leaving tablespace Primary online can cause errors when DML is issued against the indexed tables located in Primary. The problem only manifests when the access method chosen by the optimizer needs to access the indexes in the Index tablespace.
3. If you took the tablespace offline using temporary or immediate priority, then you cannot bring the tablespace online unless you perform tablespace recovery.

Making User-Managed Backups of the Whole Database

We can take two types of backup
1. Consistent / coldbackup / offline
2. Inconsistent / hotbackup / online

How to take consistent backup and what is consistent backup.

Consistent backup means all datafile , controlfile, redolog file having same SCN number. and consistent must be taken after database shutdown with NORMAL, TRANSACTIONAL, IMMEDIATE options.

For consistent backup no need to operate database in archivelog mode or we can say if database running in no archivelog mode then we can use COLDBACKUP/CONSISTENT/OFFLINE backup method.


Procedure


1. shutdown IMMEDIATE;
2. COPY ALL *.dbf, *.log, *.ctl files
3. COPY spfilesid.ora,initsid.ora files
4. startup


Inconsistent or hotbackup or online backup

When database availability require 24x7 so we have to use ONLINE backup method.
and when we take online backup that backup called is inconsistent backup.
for online backup database MUST BE running in archivelog mode.

Control files play a crucial role in database restore and recovery. For databases running in ARCHIVELOG mode, Oracle Corporation recommends that you back up control files with the ALTER DATABASE BACKUP CONTROLFILE TO 'filename' statement.


Procedure

Hotbackup on Linux

Querying V$ Views to Obtain Backup Information

Before making a backup, you must identify all the files in your database and decide what to back up. Several V$ views can provide the necessary information.

Listing Database Files Before a Backup


select name from v$datafile
union all
select member from v$logfile
union all
select name from v$controlfile;


Determining Datafile Status for Online Tablespace Backups

You can check V$BACKUP views to determine datafile is part of backup or not.
it is also usefull when you database startup after instance failure through this view you can know datafile is part of online backup when database is open.

V$backup view is not useful when current controlfile is created from restored backup or RE-CREATE statment becuase it is not content information about backup datafile.

Through below query we can know status of datafile backup status

SELECT t.name AS "TB_NAME", d.file# as "DF#", d.name AS "DF_NAME", b.status
FROM V$DATAFILE d, V$TABLESPACE t, V$BACKUP b
WHERE d.TS#=t.TS#
AND b.FILE#=d.FILE#
AND b.STATUS='ACTIVE'