PDA

View Full Version : Adding multiple datafiles to the primary database


Geofrey Rainey
01-23-2008, 08:34 AM
Dbvisit has a current limitation that only one data file can be added per time. If you want to add more than one datafile to the primary database, then run Dbvisit on the primary database between adding datafiles.

For example (on the primary):
Step 1: Add datafile 1
Step 2: Run dbvisit on the primary to propagate the changes.
Step 3: Add datafile 2

When encountered the following error in standby database:

SQL> recover standby database until cancel;
ORA-00283: recovery session canceled due to errors
ORA-01111: name for data file 16 is unknown - rename to correct file
ORA-01110: data file 16: '/opt/oracle/product/10g/db/dbs/UNNAMED00016'
ORA-01157: cannot identify/lock data file 16 - see DBWR trace file
ORA-01111: name for data file 16 is unknown - rename to correct file
ORA-01110: data file 16: '/opt/oracle/product/10g/db/dbs/UNNAMED00016'

Two datafiles were added to the primary database:
+DG1/w112p/datafile/w112_data_3_01.dbf and +DG1/w112p/datafile/w112_data_3_02.dbf.

Datafile w112_data_3_02.dbf was created successfully by Dbvisit in ASM on the standby database.

To add the second datafile to the standby database. Run the following SQL command on the standby database:

SQL> alter database create datafile '/opt/oracle/product/10g/db/dbs/UNNAMED00016' as '+DG1/w112p/datafile/w112_data_3_01.dbf';

Add the following SQL to the standby database (for Oracle version 10g and higher) to ensure multiple datafiles are added to the standby database:
SQL> alter system set STANDBY_FILE_MANAGEMENT = auto;

Mike Donovan
07-04-2009, 08:43 AM
For step by step instructions on how to work through this process please see the Solution section in the following post:

http://www.dbvisit.com/forums/showthread.php?t=190