“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!
Hi! I am Bruno, a Brazilian born and bred. Former Oracle ACE, Computer Scientist, MSc in Data Science, over ten years of experience in companies such as IBM, Epico Tech, and Playtech based in three different countries (Brazil, Hungary, and Sweden) and joined projects remotely in many others. I am super excited to show you my interest in Databases, Cloud, Data Science, Data Engineering, Bigdata, AI, Programming, Software Engineering, and data in general.
(Continue reading)