UNNAMED file in Standby(PDB) after adding new file to Primary(PDB)
In the year 2012 I’ve written similar blog for database pre-12c and it was well taken , viewed and received nice response of the post with 3200+ views in this year 2015. Now am writing the similar one but this post applicable to 12c Pluggable database.
In fact i was working with Data Guard configuration of 12c Container database with pluggable database, I’ve added a new datafile at PDB level and unfortunately the STANDBY_FILE_MANAGEMENT on standby was set to default value(MANUAL) where it should be “AUTO” to avoid such issues. The procedure to fix in pre-12c and for PDB there are few changes and i want to show how to fix them.
Trace from Standby Database after adding new datafile on primary PDB and caused termination of Media recovery process.
Media Recovery Log /u02/app/oracle/fast_recovery_area/INDIA/archivelog/2015_04_18/o1_mf_1_39_bm4jlvs3_.arc File #11 added to control file as 'UNNAMED00011' because the parameter STANDBY_FILE_MANAGEMENT is set to MANUAL The file should be manually created to continue. Errors with log /u02/app/oracle/fast_recovery_area/INDIA/archivelog/2015_04_18/o1_mf_1_39_bm4jlvs3_.arc MRP0: Background Media Recovery terminated with error 1274 Sun Apr 19 12:59:30 2015 Errors in file /u01/app/oracle/diag/rdbms/india/drmcdb/trace/drmcdb_mrp0_26699.trc: ORA-01274: cannot add datafile '/u02/app/oracle/oradata/mcdb/mpdb/rcat01.dbf' - file could not be created Managed Standby Recovery not using Real Time Apply Recovery interrupted!
If i verify the parameter STANDBY_FILE_MANAGEMENT on standby it shows as Manual. So that’s caused failure in creating the datafile on standby.
SQL> show parameter standby
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
standby_archive_dest string ?/dbs/arch
standby_file_management string MANUAL
SQL>
As usual we need to identify the valid path on primary database to crosscheck file name and number
SQL> select file#,name from v$datafile where file#=11;
FILE# NAME
---------- --------------------------------------------------
11 /u02/app/oracle/oradata/mcdb/mpdb/rcat01.dbf
SQL>
Let’s describe the database files from standby database(ran this query from root level, so it shows mix of both PDB and container)
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/u02/app/oracle/oradata/mcdb/system01.dbf
/u02/app/oracle/oradata/mcdb/sysaux01.dbf
/u02/app/oracle/oradata/mcdb/undotbs01.dbf
/u02/app/oracle/oradata/mcdb/pdbseed/system01.dbf
/u02/app/oracle/oradata/mcdb/users01.dbf
/u02/app/oracle/oradata/mcdb/pdbseed/sysaux01.dbf
/u02/app/oracle/oradata/mcdb/mpdb/system01.dbf
/u02/app/oracle/oradata/mcdb/mpdb/sysaux01.dbf
/u02/app/oracle/oradata/mcdb/mpdb/mpdb_users01.dbf
/u02/app/oracle/product/12.1.0.1/db_1/dbs/UNNAMED00011
In order to fix this issue of PDB datafile we have to connect to the PDB database to create the file but not from root container database, because the datafile is not belongs to root database.
SQL> alter session set container=mpdb; Session altered. SQL> alter database create datafile '/u02/app/oracle/product/12.1.0.1/db_1/dbs/UNNAMED00011' as '/u02/app/oracle/oradata/mcdb/mpdb/rcat01.dbf'; Database altered. SQL>
After we able to create datafile successfully, we can check the fresh status of the PDB datafiles
SQL> select file#,name from v$datafile; FILE# NAME ---------- -------------------------------------------------- 8 /u02/app/oracle/oradata/mcdb/mpdb/system01.dbf 9 /u02/app/oracle/oradata/mcdb/mpdb/sysaux01.dbf 10 /u02/app/oracle/oradata/mcdb/mpdb/mpdb_users01.dbf 11 /u02/app/oracle/oradata/mcdb/mpdb/rcat01.dbf SQL>
So we are in good to go to start MRP, but do not forget to set STANDBY_FILE_MANAGEMENT to AUTO if you don’t want to run into this issue again 😉
SQL> conn / as sysdba Connected. SQL> alter system set standby_file_management=auto; System altered. SQL>
Start MRP, As am using Data Guard broker so used DGMGRL
DGMGRL> edit database india set state='APPLY-ON'; Succeeded. DGMGRL>
As soon as the MRP started and now Standby is able to accept the archive logs and able to sync with primary database.
Sun Apr 19 13:10:33 2015
Media Recovery Log /u02/app/oracle/fast_recovery_area/INDIA/archivelog/2015_04_19/o1_mf_1_64_bm6pdcvg_.arc
Sun Apr 19 13:10:34 2015
Media Recovery Log /u02/app/oracle/fast_recovery_area/INDIA/archivelog/2015_04_19/o1_mf_1_65_bm6pdqf8_.arc
Media Recovery Waiting for thread 1 sequence 66 (in transit)
Sun Apr 19 13:10:35 2015
Recovery of Online Redo Log: Thread 1 Group 5 Seq 66 Reading mem 0
Mem# 0: /u02/app/oracle/fast_recovery_area/INDIA/onlinelog/o1_mf_5_bm4gpp4f_.log
Verify the Sync status with Primary
SQL> SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference" FROM (SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH, (SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL WHERE ARCH.THREAD# = APPL.THREAD# ORDER BY 1; Thread Last Sequence Received Last Sequence Applied Difference ---------- ---------------------- --------------------- ---------- 1 65 65 0
Conclusion: Mistakes may be same but the solution/fix is depend based on the feature 😀
