Wednesday, November 12, 2008

Adding Datafile in Standby Database

We are having our DR/Standby database getting sync with Production instance. Whenever we add any datafile to Production we have to manually add datafile in Standby. Yup that's the process and we have to do that, but would like to share with you all what needs to be done step by step.:

1. Add a datafile in Production "alter tablespace TBS add datafile 'Path/file.dbf' size 2000M;"
2. Wait for log switch or do it.
3. Move the arch file to standby/DR location.
4. Recover standby "Recover database using backup controlfile until cancel;"
5. Above step will fail and give you one unamed file#. You can query the tablespace name etc like "select TB.NAME,DF.NAME from v$tablespace TB , v$datafile DF where TB.TS#=DF.TS# and DF.File#=345;"
6. Create datafile in Standby/DR instance with unamed file like "Alter database create datafile '/OH/dbs/UNNAMED00527' as '/Path/file.dbf';"
7. Recover standby/DR again "Recover database using backup controlfile until cancel;"

Happy Troubleshooting !!!


Anonymous said...

Did you already try setting standby_file_management = auto.

I've had no problem with it working correctly in 10g on Linux and Unix.

moses said...


How about adding standby_management=auto parameter to init.ora parameter?


Sundeep Dubey said...

Actually we do not have any setting in our Production instance and just syncing our DR/standby instance by moving arch files to DR/standby server and then manually recovering them.
Thanks for your suggestions will try this out and let you know.


Anonymous said...


I get this on my standby :

SQL> Alter database create datafile 'F:\ORACLE\FRS\DATAFILES\UNNAMED00012' as 'F:\ORACLE\FRS\DATAFILES\USERS03.DBF';
Alter database create datafile 'F:\ORACLE\FRS\DATAFILES\UNNAMED00012' as 'F:\ORACLE\FRS\DATAFILES\USERS03.DBF'
ERROR at line 1:
ORA-01516: nonexistent log file, datafile, or tempfile

Do I run the select TB.NAME... on the primary ?