This week a colleague of mine was building a physical standby. The method she had chosen was to copy the backups from the primary server to the standby server and then restore the database followed by a recover. The database was on the larger size; approximately 4 TB. After the restore completed successfully, she issued the recover database command and Oracle responded that data file 114 needed to be restored. At this point in time I got involved and issued a restore datafile 114 command which completed successfully.
We then tried the recover database command and were informed that data file 177 needed to be restored. We issued a restore data file 177 command which also completed successfully. The next recover database command pointed to a different file that needed to be restored. This continued alternating between a series of six data files.
At this point in time we looked at the V$datafile table with the below SQL
select name, count(*) from v$datafile group by name having count(*) > 1;
The SQL stated indicated that there were three pairs of data files, each with two identically named filenames. This has occurred because the source database had many more ASM disk groups than the target (i.e. the standby database). As a result of this mismatch between the number of ASM disk groups, three pairs of files were acquiring identical names in the same ASM disk groups on the standby. Each time we restored one of the duplicate pairs the other file of the duplicate pair would then need to be restored causing us to continuously loop between restores and recovers.
We resolve the issues by issuing set new name for file ID into new location commands and then restoring and recovering the database.
