Adding a new datafile to Oracle standby

Do you have a manual Oracle standby database in place catching archive logs from production site? The process will break if you add a new datafile to the production database due to error

ORA-01244: unnamed datafile(s) added to controlfile by media recovery

And you will find some unnamed datafiles in your standby database:

SELECT name FROM v$datafile;

You cannot apply archive logs until you create a datafile in standby database too. The controlfile has added a new datafile for you in the metadata because it is synchronizing with the production database but this datafile does not exist on disk. So you have to convert that fake datafile to a real one:

ALTER DATABASE CREATE DATAFILE '/name/of/your/unnamed/datafile.dbf' AS '/path/to/real/datafile.dbf' SIZE 1G;

This command will create a datafile on the disk and will change the name/path of the unnamed datafile. If you are using ASM the command will be:

ALTER DATABASE CREATE DATAFILE '/name/of/your/unnamed/datafile.dbf' AS '+YOUR_DISK_GROUP' SIZE 1G;
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s