DBA Tips Archive for Oracle


Adding Datafiles to the Primary Database

by Jeff Hunter, Sr. Database Administrator


  1. Introduction
  2. Adding Datafiles to the Primary Database


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;

Copyright (c) 1998-2018 Jeffrey M. Hunter. All rights reserved.

All articles, scripts and material located at the Internet address of http://www.idevelopment.info is the copyright of Jeffrey M. Hunter and is protected under copyright laws of the United States. This document may not be hosted on any other site without my express, prior, written permission. Application to host any of the material elsewhere can be made by contacting me at jhunter@idevelopment.info.

I have made every effort and taken great care in making sure that the material included on my web site is technically accurate, but I disclaim any and all responsibility for any loss, damage or destruction of data or any other property which may arise from relying on it. I will in no case be liable for any monetary damages arising from such loss, damage or destruction.

Last modified on
Thursday, 21-Jun-2007 16:31:48 EDT
Page Count: 38446