Discover What's Possible
Header

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 :D

Follow

Get every new post on this blog delivered to your Inbox.

Join other followers:

Paste your AdWords Remarketing code here