Infrastructure at your Service

Franck Pachot

Data Guard: always set db_create_file_dest on the standby

By July 9, 2018 Oracle 6 Comments

By Franck Pachot

.
The file name convert parameters are not dynamic and require a restart of the instance. An enhancement request was filled in 2011. I mentioned recently on Twitter that it can be annoying with Active Data Guard when a file on the primary server is created on a path that has no file name conversion. However, Ian Baugaard mentioned that there is a workaround for this specific case because db_create_file_dest is dynamic:

I’ve quickly created a 18c Data Guard configuration on the Oracle Cloud DBaaS to test it and here it is.

In the primary database and the standby database, here are the datafiles:


RMAN> report schema;
 
Report of database schema for database with db_unique_name ORCL_01
 
List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    830      SYSTEM               YES     /u02/app/oracle/oradata/ORCL/system01.dbf
3    510      SYSAUX               NO      /u02/app/oracle/oradata/ORCL/sysaux01.dbf
4    60       UNDOTBS1             YES     /u02/app/oracle/oradata/ORCL/undotbs01.dbf
5    340      PDB$SEED:SYSTEM      NO      /u02/app/oracle/oradata/ORCL/pdbseed/system01.dbf
6    620      PDB$SEED:SYSAUX      NO      /u02/app/oracle/oradata/ORCL/pdbseed/sysaux01.dbf
7    5        USERS                NO      /u02/app/oracle/oradata/ORCL/users01.dbf
8    200      PDB$SEED:UNDOTBS1    NO      /u02/app/oracle/oradata/ORCL/pdbseed/undotbs01.dbf
12   340      PDB1:SYSTEM          YES     /u02/app/oracle/oradata/ORCL/PDB1/system01.dbf
13   620      PDB1:SYSAUX          NO      /u02/app/oracle/oradata/ORCL/PDB1/sysaux01.dbf
14   200      PDB1:UNDOTBS1        YES     /u02/app/oracle/oradata/ORCL/PDB1/undotbs01.dbf
15   50       PDB1:USERS           NO      /u02/app/oracle/oradata/ORCL/PDB1/PDB1_users01.dbf
 
List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1    33       TEMP                 32767       /u04/app/oracle/oradata/temp/temp01.dbf
2    62       PDB$SEED:TEMP        32767       /u04/app/oracle/oradata/temp/pdbseed_temp012018-02-08_13-49-27-256-PM.dbf
4    62       PDB1:TEMP            32767       /u04/app/oracle/oradata/temp/temp012018-02-08_13-49-27-256-PM.dbf

The properties of the standby database define no DbFileNameConvert because the directory structure is supposed to be the same:


DGMGRL> show configuration
 
Configuration - fsc
 
  Protection Mode: MaxPerformance
  Members:
  ORCL_01 - Primary database
    ORCL_02 - Physical standby database
 
Fast-Start Failover: DISABLED
 
Configuration Status:
SUCCESS   (status updated 45 seconds ago)
 
 
DGMGRL> show database verbose 'ORCL_02';
 
Database - ORCL_02
 
  Role:               PHYSICAL STANDBY
  Intended State:     APPLY-ON
  Transport Lag:      0 seconds (computed 0 seconds ago)
  Apply Lag:          0 seconds (computed 0 seconds ago)
  Average Apply Rate: 15.00 KByte/s
  Active Apply Rate:  532.00 KByte/s
  Maximum Apply Rate: 535.00 KByte/s
  Real Time Query:    ON
  Instance(s):
    ORCL
 
  Properties:
    DGConnectIdentifier             = 'ORCL_02'
...
    DbFileNameConvert               = ''
    LogFileNameConvert              = 'dummy, dummy'
...
 
  Log file locations:
    Alert log               : /u01/app/oracle/diag/rdbms/orcl_02/ORCL/trace/alert_ORCL.log
    Data Guard Broker log   : /u01/app/oracle/diag/rdbms/orcl_02/ORCL/trace/drcORCL.log
 
Database Status:
SUCCESS

You can see that Oracle defines a dummy log file name convert. This a good idea to avoid some RMAN duplicate issues.

On the standby server, I have no db_create_file_dest defined:


SQL> show parameter create%dest
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_create_file_dest                  string
db_create_online_log_dest_1          string      .
db_create_online_log_dest_2          string
db_create_online_log_dest_3          string
db_create_online_log_dest_4          string
db_create_online_log_dest_5          string

Note that the Oracle Cloud DBaaS defines it. I’ve reset it for the purpose of this demo.

New filesystem on Primary server only

I create a new filesystem on the primary server:


[[email protected] opc]# mkdir /DATA ; chown oracle:dba /DATA

I create a datafile on this new filesystem:


SQL> alter session set container=PDB1;
Session altered.
 
SQL> create tablespace FRANCK datafile '/DATA/franck.dbf' size 100M;
Tablespace created.

The apply is stuck:


DGMGRL> show database 'ORCL_02';
 
Database - ORCL_02
 
  Role:               PHYSICAL STANDBY
  Intended State:     APPLY-ON
  Transport Lag:      0 seconds (computed 2 seconds ago)
  Apply Lag:          11 seconds (computed 2 seconds ago)
  Average Apply Rate: 16.00 KByte/s
  Real Time Query:    OFF
  Instance(s):
    ORCL
 
  Database Error(s):
    ORA-16766: Redo Apply is stopped
 
Database Status:
ERROR

The standby alert.log shows the error about the impossibility to create the datafile:


2018-07-06T08:04:59.077730+00:00
Errors in file /u01/app/oracle/diag/rdbms/orcl_02/ORCL/trace/ORCL_pr00_29393.trc:
ORA-01274: cannot add data file that was originally created as '/DATA/franck.dbf'
2018-07-06T08:04:59.111881+00:00
Background Media Recovery process shutdown (ORCL)

db_file_name_convert

The first idea is to set a db_file_name_convert, however, this requires an instance restart, which means downtime when you have sessions on the Active Data Guard standby:


DGMGRL> edit database 'ORCL_02' set property DbFileNameConvert='/DATA,/u02/app/oracle/oradata/ORCL';
Warning: ORA-16675: database instance restart required for property value modification to take effect
 
Property "dbfilenameconvert" updated
 
DGMGRL> show database 'ORCL_02';
 
Database - ORCL_02
 
  Role:               PHYSICAL STANDBY
  Intended State:     APPLY-ON
  Transport Lag:      0 seconds (computed 2 seconds ago)
  Apply Lag:          3 minutes 32 seconds (computed 2 seconds ago)
  Average Apply Rate: 16.00 KByte/s
  Real Time Query:    OFF
  Instance(s):
    ORCL
      Warning: ORA-16675: database instance restart required for property value modification to
 take effect
      Warning: ORA-16714: the value of property DbFileNameConvert is inconsistent with the member setting
 
  Database Error(s):
    ORA-16766: Redo Apply is stopped
 
  Database Warning(s):
    ORA-16853: apply lag has exceeded specified threshold
 
Database Status:
ERROR

db_create_file_dest

The solution is set db_create_file_dest which, on the standby, has higher priority than the convert:


SQL> alter system set db_create_file_dest='/u02/app/oracle/oradata';
System altered.

I restart the apply:


DGMGRL> edit database 'ORCL_02' set state=apply-on;
Succeeded.

No need to restart and future datafile creations will be created there. However, it is too late for this datafile as it has already been created as UNNAMED in the controlfile:


ORA-01186: file 18 failed verification tests
ORA-01157: cannot identify/lock data file 18 - see DBWR trace file
ORA-01111: name for data file 18 is unknown - rename to correct file
ORA-01110: data file 18: '/u01/app/oracle/product/18.0.0/dbhome_1/dbs/UNNAMED00018'

Manual CREATE DATAFILE

Then I must manually create it, but I cannot do that while I am in standby_file_management=auto:


SQL> alter session set container=PDB1;
Session altered.
 
SQL> alter database create datafile '/u01/app/oracle/product/18.0.0/dbhome_1/dbs/UNNAMED00018' as '/u02/app/oracle/oradata/ORCL/franck.dbf';
alter database create datafile '/u01/app/oracle/product/18.0.0/dbhome_1/dbs/UNNAMED00018' as '/u02/app/oracle/oradata/ORCL/franck.dbf'
*
ERROR at line 1:
ORA-01275: Operation CREATE DATAFILE is not allowed if standby file management
is automatic.

This can be changed dynamically:


DGMGRL> edit database 'ORCL_02' set property StandbyFileManagement=manual;
Property "standbyfilemanagement" updated

And then the creation is possible:


SQL> alter session set container=PDB1;
Session altered.
 
SQL> alter database create datafile '/u01/app/oracle/product/18.0.0/dbhome_1/dbs/UNNAMED00018' as new;
Database altered.

You can see that because I have defined db_create_file_dest, I don’t need to name the datafile and create it as OMF with the ‘new’ keyword.

Now I can start the apply and it will resolve the gap:


DGMGRL> edit database 'ORCL_02' set state=apply-on;
Succeeded.
 
DGMGRL> show database 'ORCL_02';
 
Database - ORCL_02
 
  Role:               PHYSICAL STANDBY
  Intended State:     APPLY-ON
  Transport Lag:      0 seconds (computed 3 seconds ago)
  Apply Lag:          0 seconds (computed 3 seconds ago)
  Average Apply Rate: 22.00 KByte/s
  Real Time Query:    ON
  Instance(s):
    ORCL
      Warning: ORA-16675: database instance restart required for property value modification to take effect
      Warning: ORA-16714: the value of property DbFileNameConvert is inconsistent with the member setting
 
Database Status:
WARNING

Do not forget to put back standby_file_management”to auto:


DGMGRL> edit database 'ORCL_02' set property StandbyFileManagement=auto;
Property "standbyfilemanagement" updated

So, now that db_create_file_dest is set, new datafiles will be created automatically as OMF (Oracle Managed Files), without caring about file name conversion:


SQL> alter session set container=PDB1;
Session altered.
 
SQL> alter tablespace FRANCK add datafile '/DATA/franck2.dbf' size 100M;
Tablespace altered.

This is confirmed from the standby alert.log:


(4):Datafile 19 added to flashback set
(4):Successfully added datafile 19 to media recovery
(4):Datafile #19: '/u02/app/oracle/oradata/ORCL_02/7050211FE75F26FAE05392781D0AADAA/datafile/o1_mf_franck_fmybw332_.dbf'

Conclusion

Always define db_create_file_dest in the standby database so that datafiles will be created. Better to have them at the wrong place rather than stopping the apply. And anyway, if you don’t like the OMF names, and you are at least in 12c Enterprise Edition, you can change their name later with online move:


SQL> alter session set container=PDB1;
Session altered.
 
SQL> alter database move datafile '/u02/app/oracle/oradata/ORCL_02/7050211FE75F26FAE05392781D0AADAA/datafile/o1_mf_franck_fmybw332_.dbf' to '/u02/app/oracle/oradata/ORCL/franck2.dbf';
Database altered.

6 Comments

  • Charles says:

    Well so what happens if the files cannot fit on a single filesystem (or ASM diskgroup) on standby??

  • Franck Pachot says:

    You can change to a filesystem with enough space. Note that the idea here is to define a destination in case a file is created outside of the convert pattern scope.

  • Raj Patel says:

    I have convert all defined and create dest set to null, but still dataguard ignores convert and create a dummy
    File in dbs path. This is 19c

  • Franck Pachot says:

    Then that’s probably a bug. If you show the initial file name and convert strings I can test.

  • Raj Patel says:

    I raised ticket with MOS and investigated – its not bug. With ASM and OMF, standby_file_management=AUTO is the only option and vice versa. From Oracle Support:
    1.) DB_CREATE_FILE_DEST is the only solution if we ASM Disk groups are used on both primary and standby sites
    IF you have multiple Diskgroups, and you add a datafile to a DG which is different to the one defined in DB_CREATE_FILE_DEST, then it ignore any convert as DB_CREATE_FILE_DEST takes precedence and file will be added to the DG defined.
    in case you have the parameter == > standby_file_management == > being set to AUTO
    2.) what IS important == > when you use a set of disks == > all data files comprising the database shall reside on this set of disks
    == > otherwise, you will have a maintenance NIGHTMARE on your hands

  • Franck Pachot says:

    Hi Raj, thanks for the info. Franck.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Franck Pachot
Franck Pachot

Principal Consultant / Database Evangelist
 Oracle ACE Director
 Oracle Database OCM 12c certified
 AWS Database Specialty certified
 Oak Table member

RSS for this blog: feed
Twitter: @FranckPachot
LinkedIn: www.linkedin.com/in/franckpachot
Podcast en français: DBPod