Oracle: Standby Automatic File Management

So you have followed the recipes to create a standby database, setup the database to switch logs automatically, and now, as with any good database, the space required to support the application grows and grows and grows.

Eventually, using your monitoring software (in my case, Pythian’s avail or dailies), you get an alert suggesting that you will need to add additional space. In this case you are going to add a datafile to a tablespace, or add a brand new tablespace.

You add the datafile on the primary and minutes later are paged with these kind of errors:

File #5 added to control file as 'UNNAMED00005' because
the parameter STANDBY_FILE_MANAGEMENT is set to MANUAL
The file should be manually created to continue.
MRP0: Background Media Recovery terminated with error 1274
Fri Apr  8 10:00:22 2005
Some recovered datafiles maybe left media fuzzy
Media recovery may continue but open resetlogs may fail

Basically, the standby is having some trouble applying the changes it has received from the primary redo log because the datafile is not there.

You can use the Oracle parameter standby_file_management along as:

1. Your standby database doesn’t have major differences in datafile locations.
2. The standby database is not using raw devices to store your datafiles.

This parameter set to AUTO means that Oracle will handle creating the new datafile on the standby if you add one or more on the primary.

But right now you are stuck with this new datafile and nowhere to go.

Method 1

Add the datafile to the standby database.

Oracle documentation: 9iR2, 10gR1 and 10gR2.

1. Determine the impact of taking the tablespace offline!
2. Take the tablespace offline OR put in backup mode: ALTER TABLESPACE name BEGIN BACKUP;
3. Make a copy of the file locally.
4. Bring the tablespace online OR take out of backup mode: ALTER TABLESPACE name END BACKUP;
5. Copy the file to the standby server.
6. ALTER SYSTEM ARCHIVE LOG CURRENT;

Method 2

ALTER DATABASE CREATE DATAFILE (on the standby).

1. RECOVER MANAGED STANDBY DATABASE CANCEL;
2. select file#,name from v$datafile where name like '%UNNAMED%';
3. ALTER DATABASE CREATE DATAFILE 'unnamed datafile' AS 'proper filename';
4. RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

Save yourself the hassle, just alter system set STANDBY_FILE_MANAGEMENT = AUTO;

Update: Thanks to Sverre for pointing out you can use tablespace hotbackup mode instead of tablespace offline.

Have Fun!
Paul.

Related Articles:

RMAN Duplicate command to create physical standby

Using ARCHIVE_LAG_TARGET to automatically switch logs