Adding Datafiles to the Primary Database

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 '/ora_dat/ssaerp/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 '/ora_dat/drssaerp/xdb_ssaerp121.dbf'
     2  as '/ora_dat/drssaerp/xdb_ssaerp21.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;