Manually restore and rename a single datafile from Primary to Standby prior to 6.0.34

Read our docs The datafile /data1/oracle/10.2.0/dbs/UNNAMED00099 was incorrectly added to the Primary database and is showing as 'MISSING' on the standby server.

Problem Details

Problem Statement

Archived logs are not applying to Standby database after datafiles were created on Primary (#11252)

Applicable Error Code

n/a

Affected Versions

Prior to 6.0.34

Affected Platforms

Linux

Description

Standby server is trying to apply archived logs after datafiles were recently added to the Primary database.

Solution

Manually restore a single datafile from Primary to Standby and rename the datafile, if the new datafile does not exist in the Standby database. NB prior to V6.0.34

Steps Performed

1) Start your standby database is in MOUNT state

(startup nomount => alter database mount standby database).

2) Stop the dbvisit schedules

3) Cross-check the datafile locations on primary and standby. Run the following select on primary and standby:

SQL> col name for a60
SQL> select file#,name from v$datafile order by 1

The point here is to pinpoint datafiles with incorrect path on standby. In case, it should be:

/data1/oracle/10.2.0/dbs/UNNAMED00099

The proper path on your primary is:

/data3/oracle/oradata/test/data02.dbf

make note of file# and the proper path.

4) You have to transfer the file which has wrong path to the standby server using RMAN. Execute on primary (change the file# as needed):

RMAN> backup as copy datafile <file#> format '/tmp/data02.bak';

Copy it to standby server using scp to some separate dir, for example:

scp /tmp/data02.bak target_server:/tmp/recover

5) On the standby server we need to restore it to the proper place. Using RMAN:

run
{
catalog start with '/tmp/restore' noprompt;
set newname for datafile <file#> to '/data3/oracle/oradata/test/data02.dbf';
restore datafile <file#>;
}

Now the datafile is restored on the Standby server in the proper location

6) The remaining thing to do, is to change datafile path in standby controlfile.

Execute on the standby:

SQL> alter database rename file '/data1/oracle/10.2.0/dbs/UNNAMED00099' to '/data3/oracle/oradata/test/data02.dbf';

7) Repeat these steps for all datafiles which are "wrong"

8) Execute ./dbvisit <DDC> a few times on primary and standby to send and apply archivelog files. Afterwards, check your standby alert.log - there should be no errors

9) You can verify all is working fine, by opening the standby database in READ-ONLY mode, and selecting any data from any tables which reside in the incorrect datafiles. Carefully review your standby database alert.log.

Another way of verification, is to run the following select on the Standby database:

SQL> select checkpoint_change#, controlfile_change# from v$database;

The SCNs should be aligned.

We're here to help!

Comments