Oracle Active Data Guard adjustment case [2]

Oracle Active Data Guard adjustment case [2]

The customer's Oracle 11gR2 Active Data Guard environment, the master database's standby_file_management = AUTO, the Standby database's standby_file_management = MANUAL, resulting in the Data file operations added to the tablespace in the master database not synchronized to the standby database, no file similar to UNNAMED00003 is created in the $ ORACLE_HOME/dbs directory. The backup database has the following alarm logs:

Tue Sep 02 17:37:36 2014
File #3 added to control file as 'unnamed00003 'because
The parameter STANDBY_FILE_MANAGEMENT is set to MANUAL
The file shoshould be manually created to continue.
MRP0: Background Media Recovery terminated with error 1274
Errors in file/u01/app/oracle/diag/rdbms/d012dg/d012band/trace/d012band_pr00_5702078.trc:
ORA-01274: cannot add datafile '/oradata1/d012band/tsmisc06.dbf'-file cocould not be created
Managed Standby Recovery not using Real Time Apply
Recovery interrupted!
Recovered data files to a consistent state in change 866102511
Tue Sep 02 17:37:46 2014
MRP0: Background Media Recovery process shutdown (d012band)
Tue Sep 02 17:52:14 2014
RFS [1]: Selected log 8 for thread 1 sequence 19136 dbid 2134147111 branch 809469738
Tue Sep 02 17:52:25 2014
Archived Log entry 511 added for thread 1 sequence 19135 ID 0x7f340827 dest 1:
Tue Sep 02 17:53:23 2014
Alter database recover managed standby database using current logfile disconnect from session
Attempt to start background Managed Standby Recovery process (d012band)
Tue Sep 02 17:53:23 2014
MRP0 started with pid = 42, OS id = 7471452
MRP0: Background Managed Standby Recovery process started (d012band)
Started logmerger process
Tue Sep 02 17:53:29 2014
Managed Standby Recovery starting Real Time Apply
Tue Sep 02 17:53:30 2014
Errors in file/u01/app/oracle/diag/rdbms/d012dg/d012band/trace/d012band_dbw0_4784258.trc:
The ORA-01186: file 3 failed verification tests
ORA-01157: cannot identify/lock data file 3-see DBWR trace file
ORA-01111: name for data file 3 is unknown-rename to correct file
ORA-01110: data file 3: '/u01/app/oracle/product/11.2.0/db_1/dbs/UNNAMED00003'

If you can find the/u01/app/oracle/product/11.2.0/db_1/dbs/UNNAMED00003 file, refer to: 11gR2 Active Data Guard adjustment case [1]

File 3 not verified due to error ORA-01157
MRP0: Background Media Recovery terminated with error 1111
Errors in file/u01/app/oracle/diag/rdbms/d012dg/d012band/trace/d012band_pr00_8716760.trc:
ORA-01111: name for data file 3 is unknown-rename to correct file
ORA-01110: data file 3: '/u01/app/oracle/product/11.2.0/db_1/dbs/UNNAMED00003'
ORA-01157: cannot identify/lock data file 3-see DBWR trace file
ORA-01111: name for data file 3 is unknown-rename to correct file
ORA-01110: data file 3: '/u01/app/oracle/product/11.2.0/db_1/dbs/UNNAMED00003'
Managed Standby Recovery not using Real Time Apply
Slave exiting with ORA-1111 exception
Errors in file/u01/app/oracle/diag/rdbms/d012dg/d012band/trace/d012band_pr00_8716760.trc:
ORA-01111: name for data file 3 is unknown-rename to correct file
ORA-01110: data file 3: '/u01/app/oracle/product/11.2.0/db_1/dbs/UNNAMED00003'
ORA-01157: cannot identify/lock data file 3-see DBWR trace file
ORA-01111: name for data file 3 is unknown-rename to correct file
ORA-01110: data file 3: '/u01/app/oracle/product/11.2.0/db_1/dbs/UNNAMED00003'
Recovery Slave PR00 previusly exited with exception 1111
MRP0: Background Media Recovery process shutdown (d012band)
Completed: alter database recover managed standby database using current logfile disconnect from session

The key to solving this problem is to manually create a new data file. refer to the following content to solve the problem:

How to resolve MRP stuck issues on a physical standby database? (Documentation ID 1221163.1)

......

Solution 10 Add the new datafiles to the standby database manually.

1) Please take a hot backup of new datafiles from the primary database.

2) Create a new standby controlfile from the primary database

 

SQL> alter database create standby controlfile as '/tmp/controlf. ctl ';

If datafiles are on ASM, please follow the note below and you coshould ignore the rest of steps:

Note 734862.1 Step By Step Guide On How To Recreate Standby Control File
When Datafiles Are On ASM And Using Oracle Managed Files

Or you cocould modify the wrong datafile name in the standby controlfile by alter database rename command. For example,

SQL> ALTER DATABASE RENAME FILE '<path/UNNAMED00003>' to '<path/real datafile name> ';

3) If the new datafile location on the primary is different from the standby, please make sure
Db_file_name_convert init parameter is set on the standby database.

Note 47325.1 Init. ora Parameter "DB_FILE_NAME_CONVERT" Reference Note

If db_file_name_convert init parameter has already been set, then you cocould ignore this step.

4) Cancel the managed recovery

 

SQL> alter database recover managed standby database cancel;

5) set standby_file_management = manual on the standby database and shutdown the standby database.

SQL> alter system set standby_file_management = manual sid = '*';
SQL> shutdown immediate;

6) Copy the hot backup of the new datafiles and the new standby controlfile to the standby.
Please make sure the controlfiles are located in the right location with right names
According to the init parameter control_files. Please make sure the copied datafiles are
Located in the right location as well according to name from v $ datafile.

7) startup the standby database in mount mode and set standby_file_management = auto.

SQL> startup mount;
SQL> alter system set standby_file_management = auto sid = '*';

 
8) Start the managed recovery.

SQL> alter database recover managed standby database disconnect;

......

-- End --

Configure Oracle 11g Data Guard based on the same host

Explore Oracle 11g elastic uard

Oracle Data Guard (RAC + DG) archive deletion policies and scripts

Role conversion for Oracle Data Guard

FAL gap in Oracle Data Guard logs

Oracle 11g Data Guard Error 16143 Heartbeat failed to connect to standby