PDA

View Full Version : Manually adding datafiles to the standby (Return code=412 or Return code=410)


Arjen Visser
10-24-2008, 10:08 AM
Problem:
When using Dbvisit Standby prior to version 6.0.28 with a primary database in a different structure to the standby database, any new tablespaces or datafiles will not be added automatically to the standby database.
For example:
1) In ASM MODE ( Primary Database )
2) In Normal Mode ( Standby Database )

When a new tablespace is created in the primary database which is in ASM, the datafile will not be created on the standby because this database is using regular filesystem.
SPECIAL NOTE: As of Dbvisit Standby 6.0.28 datafiles will be added automatically to the standby database for all datafiles (ASM, OMF, regular). The location can also be different between the primary and standby database.

This solution can also be used when Dbvisit Standby fails to add the datafile to the standby database. This can be Return code = 412 or Return code = 410.

Solution:
Find out what the missing datafile is on the standby database. Do this by comparing the datafiles between primary and standby database.
This can be done by running the following command on both primary and standby server:
dbv_functions -F <database>

Run the following SQL on the standby database:
SQL> select name, file# from v$datafile where status = 'RECOVER';

This will give you the internal name of the datafile on the standby database.
Example, this may give you the name: /oracle/testdev/product/10.2.0.1/dbs/UNNAMED00012

Then you can rename the datafile to what you like with the following SQL:

SQL> alter database create datafile
'/oracle/testdev/product/10.2.0.1/dbs/UNNAMED00012'
as
'/oradata/testdev/dbs/ucsc_dev01.dbf';

Where /oradata/testdev/dbs/ucsc_dev01.dbf
is the file name for your standby database.

Note:
If the following statement does not return any rows:
SQL> select name, file# from v$datafile where status = 'RECOVER';

Then the rename SQL command is as follows:
SQL> alter database create datafile
'/oradata/testdev/dbs/ucsc_dev01.dbf'
as
'/oradata/testdev/dbs/ucsc_dev01.dbf';

Dbvisit Standby can now be run again on the standby server to continue to update the standby database.

Arjen Visser
07-29-2012, 01:19 PM
The Dbvisit Standby trace can show more information as to what the status is of the standby datafiles:

In this case it shows that files 249-280 are corrupted/do not exist (zero sizes ans SCN from v$datafile_header), but they are recorded in the standby controlfile (v$datafile):
20120728 14:14:29 main::cmn_cat-> 249 ONLINE 12577331880095 270712 21:30
20120728 14:14:29 main::cmn_cat-> 250 ONLINE 12577331880095 270712 21:30
20120728 14:14:29 main::cmn_cat-> 251 ONLINE 12577331880095 270712 21:30
20120728 14:14:29 main::cmn_cat-> 252 ONLINE 12577331880095 270712 21:30
20120728 14:14:29 main::cmn_cat-> 253 ONLINE 12577331880095 270712 21:30
20120728 14:14:29 main::cmn_cat->
20120728 14:14:29 main::cmn_cat-> FILE# STATUS CHECKPOINT_CHANGE# CHECKPOINT_T
20120728 14:14:29 main::cmn_cat-> ---------- ------- ------------------------------- ------------
20120728 14:14:29 main::cmn_cat-> 254 ONLINE 12577331880095 270712 21:30
20120728 14:14:29 main::cmn_cat-> 255 ONLINE 12577331880095 270712 21:30
20120728 14:14:29 main::cmn_cat-> 256 ONLINE 12577331880095 270712 21:30
20120728 14:14:29 main::cmn_cat-> 257 ONLINE 12577331880095 270712 21:30
20120728 14:14:29 main::cmn_cat-> 258 ONLINE 12577331880095 270712 21:30
20120728 14:14:29 main::cmn_cat-> 259 ONLINE 12577331880095 270712 21:30
20120728 14:14:29 main::cmn_cat-> 260 ONLINE 12577331880095 270712 21:30
20120728 14:14:29 main::cmn_cat-> 261 ONLINE 12577331896487 270712 21:31
20120728 14:14:29 main::cmn_cat-> 262 ONLINE 12577331896487 270712 21:31
20120728 14:14:29 main::cmn_cat-> 263 ONLINE 12577331896487 270712 21:31
20120728 14:14:29 main::cmn_cat-> 264 ONLINE 12577331896487 270712 21:31
20120728 14:14:29 main::cmn_cat->
20120728 14:14:29 main::cmn_cat-> FILE# STATUS CHECKPOINT_CHANGE# CHECKPOINT_T
20120728 14:14:29 main::cmn_cat-> ---------- ------- ------------------------------- ------------
20120728 14:14:29 main::cmn_cat-> 265 ONLINE 12577331896487 270712 21:31
20120728 14:14:29 main::cmn_cat-> 266 ONLINE 12577331896487 270712 21:31
20120728 14:14:29 main::cmn_cat-> 267 ONLINE 12577331896487 270712 21:31
20120728 14:14:29 main::cmn_cat-> 268 ONLINE 12577331896487 270712 21:31
20120728 14:14:29 main::cmn_cat-> 269 ONLINE 12577331896487 270712 21:31
20120728 14:14:29 main::cmn_cat-> 270 ONLINE 12577331896487 270712 21:31
20120728 14:14:29 main::cmn_cat-> 271 ONLINE 12577331896487 270712 21:31
20120728 14:14:29 main::cmn_cat-> 272 ONLINE 12577331896487 270712 21:31
20120728 14:14:29 main::cmn_cat-> 273 ONLINE 12577331896487 270712 21:31
20120728 14:14:29 main::cmn_cat-> 274 ONLINE 12577331896487 270712 21:31
20120728 14:14:29 main::cmn_cat-> 275 ONLINE 12577331896487 270712 21:31
20120728 14:14:29 main::cmn_cat->
20120728 14:14:29 main::cmn_cat-> FILE# STATUS CHECKPOINT_CHANGE# CHECKPOINT_T
20120728 14:14:29 main::cmn_cat-> ---------- ------- ------------------------------- ------------
20120728 14:14:29 main::cmn_cat-> 276 ONLINE 12577331896487 270712 21:31
20120728 14:14:29 main::cmn_cat-> 277 ONLINE 12577331896487 270712 21:31
20120728 14:14:29 main::cmn_cat-> 278 ONLINE 12577331896487 270712 21:31
20120728 14:14:29 main::cmn_cat-> 279 ONLINE 12577331896487 270712 21:31
20120728 14:14:29 main::cmn_cat-> 280 ONLINE 12577331896487 270712 21:31

From this looks like the standby control file has been recreated - that is why no files with status=RECOVER.
(The standby controlfile can be re-created with dbv_functions -Q command run on the primary server)

The solution is to add the datafiles one by one with command:
SQL> alter database create datafile
'/oracle/PRD/sapdata7/sr3jvso1d_24/sr3jvso1d.data17'
as
'/oracle/PRD/sapdata7/sr3jvso1d_24/sr3jvso1d.data17';


NOTE: Make sure the automatic scheduling of Dbvisit Standby is turned off when adding datafiles.
Also ensure there is enough space on the standby server to accommodate the new datafiles.

Run the normal Dbvisit Standby process on the standby server after adding each datafile to see if Oracle has accepted the datafile and has included the new datafile in the recovery process. This can be seen by the fact that Dbvisit Standby will not error on this particular datafile anymore.

If standby datafiles are OMF, then use:
SQL> alter database create datafile
'/oracle/PRD/sapdata7/sr3jvso1d_24/sr3jvso1d.data17'
as
NEW;