Oracle Data Guard 12c: ORA-01110: data file 0000: ‘$ORACLE_HOME/dbs/UNNAMED0ZZZ’

“Opinions expressed are solely my own and do not express the views or opinions of my employer.”

Oracle Data Guard is a great Oracle product that ensures high availability, data protection, and disaster recovery for enterprise data. However, as with any other product, sometimes there are some issues that appear and it is needed to be solved. Therefore, the post today will be about the error “ORA-01110: data file 0000: ‘$ORACLE_HOME/dbs/UNNAMED0ZZZ'”. Many causes can lead to this error but one that I can point out is when the primary database sends archive to the source database but the allocated area is full and then reporting the error “ORA-17502: ksfdcre:4 Failed to create file +DISKGROUPNAME ORA-15041: diskgroup “” space exhausted” for instance.

Some people might think that only releasing or adding space would solve the problem, but the answer is no. One option to solve this problem is to follow the following steps below:

  • Check previous configuration of the Data Guard Command-Line Interface Reference (DGMGRL) , in case of fallback: To be able to do that you can connect to the DGMRL console and run the command “SHOW CONFIGURATION VERBOSE” as the example below:
[oracle@]$ dgmgrl
DGMGRL for Linux: Release 12.2.0.1.0 - Production on Wed Mar 9 08:43:42 2022

Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.

Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys
Password:
Connected to "tech_dataDG"
Connected as SYSDG.

DGMGRL>SHOW CONFIGURATION VERBOSE;

Configuration - tech_broker

  Protection Mode: MaxPerformance
  Members:
  tech_dataPR1 - Primary database
    Error: ORA-16778: redo transport error for one or more members

    tech_dataDG - Physical standby database
      Error: ORA-16810: multiple errors or warnings detected for the member

  Properties:
    FastStartFailoverThreshold      = '30'
    OperationTimeout                = '60'
    TraceLevel                      = 'SUPPORT'
    FastStartFailoverLagLimit       = '30'
    CommunicationTimeout            = '90'
    ObserverReconnect               = '0'
    FastStartFailoverAutoReinstate  = 'TRUE'
    FastStartFailoverPmyShutdown    = 'TRUE'
    BystandersFollowRoleChange      = 'ALL'
    ObserverOverride                = 'FALSE'
    ExternalDestination1            = ''
    ExternalDestination2            = ''
    PrimaryLostWriteAction          = 'CONTINUE'
    ConfigurationWideServiceName    = ''

Fast-Start Failover: DISABLED

Configuration Status:
ERROR


DGMGRL> show database verbose tech_dataDG;

Database - tech_broker

  Role:               PHYSICAL STANDBY
  Intended State:     APPLY-ON
  Transport Lag:      8 hours 48 minutes 16 seconds (computed 4 seconds ago)
  Apply Lag:          2 days 18 hours 1 minute 49 seconds (computed 0 seconds ago)
  Average Apply Rate: 2.08 MByte/s
  Active Apply Rate:  13.24 MByte/s
  Maximum Apply Rate: 16.19 MByte/s
  Real Time Query:    OFF
  Instance(s):
    tech_dataDG1 (apply instance)
    tech_dataDG2

  Database Error(s):
    ORA-16766: Redo Apply is stopped

  Database Warning(s):
    ORA-16853: apply lag has exceeded specified threshold
    ORA-16855: transport lag has exceeded specified threshold

  Properties:
    DGConnectIdentifier             = 'tech_dataDG'
    ObserverConnectIdentifier       = ''
    LogXptMode                      = 'ASYNC'
    RedoRoutes                      = ''
    DelayMins                       = '0'
    Binding                         = 'optional'
    MaxFailure                      = '0'
    MaxConnections                  = '1'
    ReopenSecs                      = '300'
    NetTimeout                      = '30'
    RedoCompression                 = 'DISABLE'
    LogShipping                     = 'on'
    PreferredApplyInstance          = 'tech_dataDG2'
    ApplyInstanceTimeout            = '0'
    ApplyLagThreshold               = '30'
    TransportLagThreshold           = '30'
    TransportDisconnectedThreshold  = '30'
    ApplyParallel                   = 'AUTO'
    ApplyInstances                  = '0'
    StandbyFileManagement           = 'auto'
    ArchiveLagTarget                = '0'
    LogArchiveMaxProcesses          = '4'
    LogArchiveMinSucceedDest        = '1'
    DataGuardSyncLatency            = '0'
    DbFileNameConvert               = ''
    LogFileNameConvert              = ''
    FastStartFailoverTarget         = ''
    InconsistentProperties          = '(monitor)'
    InconsistentLogXptProps         = '(monitor)'
    SendQEntries                    = '(monitor)'
    LogXptStatus                    = '(monitor)'
    RecvQEntries                    = '(monitor)'
    PreferredObserverHosts          = ''
    StaticConnectIdentifier(*)
    StandbyArchiveLocation(*)
    AlternateLocation(*)
    LogArchiveTrace(*)
    LogArchiveFormat(*)
    TopWaitEvents(*)
    (*) - Please check specific instance for the property value

  Log file locations(*):
    (*) - Check specific instance for log file locations.

Database Status:
ERROR

  • Change the property StandbyFileManagement to manual if your enviroment is using (DGMGRL) or STANDBY_FILE_MANAGEMENT on the database:
##using  (DGMGRL)
DGMGRL> edit database 'tech_dataDG' set property StandbyFileManagement=manual;
Property "standbyfilemanagement" updated

#STANDBY_FILE_MANAGEMENT on the database
SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=MANUAL scope=both sid='*';

System altered.
SQL> show parameters STANDBY_FILE_MANAGEMENT

NAME                                 TYPE
------------------------------------ --------------------------------
VALUE
------------------------------
standby_file_management              string
MANUAL
  • Find the datafile with the wrong name. These datafiles have their name starting with ‘UNNAMED’:
SQL>  select name from v$datafile where name like '%UNNA%';

NAME
--------------------------------------------------------------------------------
$ORACLE_HOME/dbs/UNNAMED01464

  • Create the datafile with alter command “alter database create datafile”;

#Example for a database using ASM

SQL> alter database create datafile '$ORACLE_HOME/dbs/UNNAMED01464' as new;

Database altered.

  • Return the old configurations:
##using  (DGMGRL)
DGMGRL> edit database 'tech_dataDG' set property StandbyFileManagement=AUTO;
Property "standbyfilemanagement" updated
 
#STANDBY_FILE_MANAGEMENT on the database
SQL> show parameters STANDBY_FILE_MANAGEMENT
 
NAME                                 TYPE
------------------------------------ --------------------------------
VALUE
------------------------------
standby_file_management              string
MANUAL
SQL>
 
SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO scope=both sid='*';
 
System altered.
 
SQL> show parameters STANDBY_FILE_MANAGEMENT
 
NAME                                 TYPE
------------------------------------ --------------------------------
VALUE
------------------------------
standby_file_management              string
AUTO



Start the MRP process:

#Using Real Time Apply
SQL> alter database recover managed standby database using current logfile disconnect;
 
 
SQL> select process, status , sequence# from v$managed_standby;
 
PROCESS   STATUS        SEQUENCE#
--------- ------------ ----------
ARCH      CLOSING         1515227
DGRD      ALLOCATED             0
DGRD      ALLOCATED             0
ARCH      CLOSING         1450350
ARCH      CLOSING         1515228
ARCH      CLOSING         1515230
MRP0      APPLYING_LOG    1514194
RFS       IDLE                  0
RFS       IDLE                  0
RFS       IDLE                  0
RFS       IDLE                  0
 
PROCESS   STATUS        SEQUENCE#
--------- ------------ ----------
RFS       IDLE                  0
RFS       IDLE                  0
RFS       IDLE                  0
RFS       IDLE                  0
RFS       IDLE                  0
RFS       IDLE                  0
RFS       IDLE                  0
RFS       IDLE                  0
RFS       IDLE                  0
RFS       IDLE                  0
RFS       IDLE                  0
 
PROCESS   STATUS        SEQUENCE#
--------- ------------ ----------
RFS       IDLE                  0
RFS       IDLE            1450361
 
24 rows selected.


If the issue persists, I recommend you to check the notes (Doc ID 2715234.1) and (Doc ID 739618.1) for more information.

I hope this post helps you!

Related posts

Leave a Comment