Adding Datafiles to the Primary Database

Contents

1. Introduction
2. Adding Datafiles to the Primary Database


Introduction

This article provides detailed instructions and notes for adding a new datafile (or new tablespace) to the primary database in a standby database configuration.

The Standby Database feature of Oracle (Oracle7 and higher) allows the DBA to maintain a duplicate, or standby, copy of a database at a remote site to provide continued primary database availability in the event of failure. The standby database is created with a special copy of the control file from the primary database. The standby database is kept in close synchronization with the primary database by applying the primary database's archived log files to the standby database. It is therefore necessary to operate the primary database in Archivelog Mode to avail the benefit of a standby database.

A standby database is generally used where high availability of data is required. A standby database is one way of quickly providing access to data if the primary database fails and recovery will take longer than the desired time.


Adding Datafiles to the Primary Database

The control file of the standby database may need to be refreshed (or recreated) when the control file on the primary database is changed. This is generally caused when altering the physical structure of the primary database like when adding datafiles and/or tablespaces. The steps below detail how to refresh the control file and add a datafile to a standby database.

1. Before you add a datafile to the primary database, you should CANCEL your recovery on standby database.

SQL> recover managed standby database cancel;

Media recovery complete.

NOTE: If you don't cancel, you will get an error on the standby when it hits any redo for that datafile. This won't be a big deal because at that point you can cancel and create the new datafile.

2. Perform a SHUTDOWN IMMEDIATE on the standby database (if needed). It is possible, however, that you have the standby database configured to shutdown the standby database instance when managed recovery is cancelled. In this case, you will not need to perform a SHUTDOWN IMMEDIATE.

SQL> shutdown immediate

3. Create the new tablespace -or- add the new datafile on the primary database as usual. For example:

SQL> alter tablespace users add datafile '/u06/app/oradata/ORA817/users02.dbf' size 10m;

Tablespace altered.

4. Create a new standby controlfile on the primary database.

SQL> alter database create standby controlfile as '/u01/app/oracle/control01.ctl';

Database altered.

5. Archive the current online logs of the primary database. Insure consistency in the backup datafiles, standby controlfile and logfiles. From the primary database:

SQL> alter system archive log current;

System altered.

6. Transfer the new standby controlfile and all new archived redo logs to the standy machine.

% rcp control01.ctl linux4:/u03/app/oradata/ORA817/control01.ctl
% rcp control01.ctl linux4:/u04/app/oradata/ORA817/control02.ctl
% rcp control01.ctl linux4:/u05/app/oradata/ORA817/control03.ctl

% rcp /u06/app/oradata/ORA817/archive/* linux4:/u06/app/oradata/ORA817/archive/

7. Mount the standby database. Keep in mind that after mounting the database with the new control file, the new datafile(s) will be in the new control file. On the standby database, perform the following:

SQL> connect / as sysdba
Connected to an idle instance.

SQL> startup nomount
ORACLE instance started.

Total System Global Area 252777660 bytes
Fixed Size 451772 bytes
Variable Size 218103808 bytes
Database Buffers 33554432 bytes
Redo Buffers 667648 bytes

SQL> alter database mount standby database;

Database altered.

8. Create the datafile on the standby database. The following command will create the physical OS file on disk. For example, issue:

SQL> alter database create datafile '/u06/app/oradata/ORA817/users02.dbf'
2 as '/u06/app/oradata/ORA817/users02.dbf';

Database altered.

9. On the standby site, continue by applying archived redo logs by issuing the following statement. Keep in mind that when prompted for the first archived redo log, you can type in "auto" to Oracle to automatically apply all required archived redo logs:

SQL> recover standby database until cancel;

10. You can now resume the standby database by putting it back into managed recovery mode:

SQL> recover managed standby database;