Oracle ошибка ora 01110

Hello DBA-Mates,

We are back after a long time with ORA-01110: data file 1:
‘/u01/oracle/oradata/ORA-DATA3/system01.dbf’. Got so many messages and
comments as why stopped writing but we didn’t stopped , there was some bad time
which slowly passing away and we came back.

Thank you all for your supporting
and loving.

Recently, one of our friend faced this below issue and
they were not able to recover the database. So, friends before playing with
database take the backup always either online or offline.

Here, we have just shown as if we
missed system datafile or deleted, how can we recover it and start the
database.

Take the offline backup.

SQL> select name,open_mode,log_mode,flashback_on from
v$database;

NAME
OPEN_MODE LOG_MODE FLASHBACK_ON

——— ———- ———— ——————

ORA-DATA3 READ
WRITE ARCHIVELOG NO

SQL> archive log list

Database log mode Archive Mode

Automatic archival Enabled

Archive destination USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence 2

Next log sequence to archive 4

Current log sequence 4

******************************************

Make some transactions inorder to generate archive logs

(if account is locked then):

SQL> alter user scott identified by tiger account
unlock;

SQL> conn scott/tiger

Connected.

SQL> create table student(name varchar2(50))
tablespace users;

Table created.

SQL> begin

2 for i in 1..300000 loop

3 insert into student values(’10g DBA Training
and R12 Apps DBA Training student’);

4 end loop;

5 commit;

6 end;

7 /

*************************************************************

sql>conn /as sysdba;

SQL> select *
from v$recover_file;

no rows selected

SQL> column name format a40

SQL> select name,file#,status from v$datafile;

NAME FILE# STATUS

—————————————- ———-
——-

/u01/oracle/oradata/ORA-DATA3/system01.dbf 1 SYSTEM

/u01/oracle/oradata/ORA-DATA3/undotbs01.dbf 2 ONLINE

/u01/oracle/oradata/ORA-DATA3/sysaux01.dbf 3 ONLINE

/u01/oracle/oradata/ORA-DATA3/users01.dbf 4 ONLINE

SIMULATE THE FAILURE OF SYSTEM DATAFILE LOSS

[oracle@sku ORA-DATA3]$ clear

[oracle@sku ORA-DATA3]$ pwd

/u01/oracle/oradata/ORA-DATA3

[oracle@sku ORA-DATA3]$ ls -l syst*

-rw-r—— 1 oracle oinstall 503324672 Sep 14 23:37
system01.dbf

[oracle@sku ORA-DATA3]$ ls

control01.ctl
control04.ctl redo01.log redo05a.log
sysaux01.dbf

control01.ctl_old
control04.ctl_old redo02a redo05.log system01.dbf

control02.ctl
example01.dbf
redo02a.log redo06a.log temp01.dbf

control02.ctl_old
indx01.dbf redo02.log redo06.log
undotbs01.dbf

control03.ctl
redo01a
redo03a.log redo07a.log users01.dbf

control03.ctl_old
redo01a.log redo03.log redo07.log

[oracle@sku ORA-DATA3]$ mv
system01.dbf system01.dbf_old

[oracle@sku ORA-DATA3]$

*********************************************

SQL> shutdown immediate

ORA-01116: error in opening database file 1

ORA-01110: data file 1: ‘/u01/oracle/oradata/ORA-DATA3/system01.dbf’

ORA-27041: unable to open file

Linux-x86_64 Error: 2: No such file or
directory

Additional information: 3

SQL> shutdown abort

ORACLE instance shut down.

SQL>

SQL> startup

ORACLE instance started.

Total System Global Area 1090519040 bytes

Fixed Size 2020128 bytes

Variable Size
301993184 bytes

Database Buffers
771751936 bytes

Redo Buffers
14753792 bytes

Database mounted.

ORA-01157: cannot identify/lock data file 1 —
see DBWR trace file

ORA-01110: data file 1:
‘/u01/oracle/oradata/ORA-DATA3/system01.dbf’

SQL> shutdown immediate

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

SQL> startup mount

ORACLE instance started.

Total System Global Area 1090519040 bytes

Fixed Size 2020128 bytes

Variable Size
301993184 bytes

Database Buffers
771751936 bytes

Redo Buffers
14753792 bytes

Database mounted.

SQL>

SQL> select * from v$recover_file;

FILE#
ONLINE ONLINE_

———- ——- ——-

ERROR
CHANGE#

——————————————————————
———-

TIME

———

1
ONLINE ONLINE

535300

15-SEP-11

Restore the system01.dbf from
Offline Backup

[oracle@sku ORA-DATA3]$ pwd

/u01/oracle/oradata/ORA-DATA3

$cp
/u01/oracle/offline_backup/system01.dbf .
##(This will also work if you restore system01.dbf from online backup)

[oracle@sku ORA-DATA3]$ ls -l *dbf

-rw-r—— 1 oracle oinstall 104865792 Sep 14 23:50
example01.dbf

-rw-r—— 1 oracle oinstall 4202496 Sep 14 23:50 indx01.dbf

-rw-r—— 1 oracle oinstall 251666432 Sep 14 23:50
sysaux01.dbf

-rw-r—— 1 oracle oinstall 503324672 Sep 14 23:56
system01.dbf

-rw-r—— 1 oracle oinstall 20979712 Sep 14 20:12 temp01.dbf

-rw-r—— 1 oracle oinstall 26222592 Sep 14 23:50 undotbs01.dbf

-rw-r—— 1 oracle oinstall 10493952 Sep 14 23:50 users01.dbf

******************

[oracle@sku 2011_09_14]$ pwd

/u01/oracle/flash_recovery_area/ORA-DATA3/archivelog/2011_09_15

[oracle@sku 2011_09_15]$ ls -alrt

total 33708

drwxr-x— 3 oracle oinstall 4096 Sep 15 02:47 ..

-rw-r—— 1 oracle oinstall 34460160 Sep 15 02:47
o1_mf_1_1_7726hlvy_.arc

-rw-r—— 1 oracle oinstall 1024 Sep 15 02:48 o1_mf_1_2_7726j9bo_.arc

drwxr-x— 2 oracle oinstall 4096 Sep 15 02:48 .

*************************************

SQL> recover database;

ORA-00279: change 680758 generated at 09/14/2011 20:31:38
needed for thread 1

ORA-00289: suggestion :

/u01/oracle/flash_recovery_area/ORA-DATA3/archivelog/2011_09_15/o1_mf_1_9_%u_.arc

ORA-00280: change 680758 for thread 1 is in sequence #9

Specify log: {<RET>=suggested | filename | AUTO |
CANCEL}

AUTO

ORA-00279: change 681408 generated at 09/14/2011 20:45:01
needed for thread 1

ORA-00289: suggestion :

/u01/oracle/flash_recovery_area/ORA-DATA3/archivelog/2011_09_15/o1_mf_1_10_%u_.arc

ORA-00280: change 681408 for thread 1 is in sequence #10

ORA-00278: log file

‘/u01/oracle/flash_recovery_area/ORA-DATA3/archivelog/2011_09_14/o1_mf_1_9_771kpqbq_.

arc’ no longer needed for this recovery

ORA-00279: change 681427 generated at 09/14/2011 20:53:03
needed for thread 1

ORA-00289: suggestion :

/u01/oracle/flash_recovery_area/ORA-DATA3/archivelog/2011_09_15/o1_mf_1_11_%u_.arc

ORA-00280: change 681427 for thread 1 is in sequence #11

ORA-00278: log file

‘/u01/oracle/flash_recovery_area/ORA-DATA3/archivelog/2011_09_14/o1_mf_1_10_771kpqrc_

.arc’ no longer needed for this recovery

ORA-00279: change 684925 generated at 09/14/2011 22:00:07
needed for thread 1

ORA-00289: suggestion :

/u01/oracle/flash_recovery_area/ORA-DATA3/archivelog/2011_09_15/o1_mf_1_12_%u_.arc

ORA-00280: change 684925 for thread 1 is in sequence #12

ORA-00278: log file

‘/u01/oracle/flash_recovery_area/ORA-DATA3/archivelog/2011_09_14/o1_mf_1_11_771onh9m_

.arc’ no longer needed for this recovery

Log applied.

Media recovery complete.

SQL> alter database open;

Database altered.

SQL> select * from v$recover_file;

no rows selected

SQL> select file#,name,status from v$datafile;

FILE#
NAME
STATUS

———- —————————————-
——-

1
/u01/oracle/oradata/ORA-DATA3/system01.dbf
SYSTEM

2
/u01/oracle/oradata/ORA-DATA3/undotbs01.dbf
ONLINE

3
/u01/oracle/oradata/ORA-DATA3/sysaux01.dbf
ONLINE

4
/u01/oracle/oradata/ORA-DATA3/users01.dbf
ONLINE

===============================================

Database mounted.

ORA-01113: file 1 needs media recovery

ORA-01110: data file 1:
‘/opt/oracle/oradata/orcl/system01.dbf’

SQL> recover datafile 1;

Media recovery complete.

SQL> alter database open;

Database altered.

SQL>

Источник

If you cannot recover data by yourself, ask Parnassusdata, the professional ORACLE database recovery team for help.

Parnassusdata Software Database Recovery Team

Service Hotline: +86 13764045638 E-mail: service@parnassusdata.com

Oracle Database — Enterprise Edition — Version 9.0.1.0 to 11.2.0.3 [Release 9.0.1 to 11.2]

Information in this document applies to any platform.

GOAL

The goal of this article is to assist DBAs who encounter the ORA-01110, and to point them in the right direction.

Several notes have been referenced depending on the subsequent errors.

If the DBA is unable to resolve the issue after reading the appropriate note, a script to collect diagnostic information has been provided below. The output of this script should be uploaded to the service request.

SOLUTION

Definition

Error: ORA-01110
Text: datafile <name>: <str>
——————————————————————————-
Cause : This message reports the filename involved with other messages.
Action: See the associated messages for a description of the problem.

The ORA-01110 displays the physical datafile in which Oracle is having a problem accessing.

The ORA-01110 is accompanied by one or more messages. These messages may be Oracle specific messages or be related to the operating system.

The first aim is to identify all error messages encounted prior to addressing the issue.

Below is a list of the common errors that may follow the ORA-01110.

ORA-01157    «cannot identify datafile <name> — file not found»
ORA-01578    «ORACLE data block corrupted (file # %s, block # %s)»
ORA-00376    «file <name> cannot be read at this time»
ORA-01194    «file <name> needs more recovery to be consistent»
ORA-01547    «warning: RECOVER succeeded but OPEN RESETLOGS would get error»
ORA-01135 «file %s accessed for DML/query is offline»

Addressing an ORA-01157 (cannot identify datafile <name> — file not found)

— Does the datafile in question exist at the os level ?

If yes ensure that it is valid. Check the permissions & state of the file. Run an RMAN DBV or RMAN validate on the file/s this may provide additional diagnostics.

If not locate the file or restore from backup.

Addressing an ORA-01578 (ORACLE data block corrupted (file # %s, block # %s))

— Identify the object flagged as corurpted:

                SELECT tablespace_name, segment_type, owner, segment_name
                FROM dba_extents
                WHERE file_id = &AFN
                and &BL between block_id AND block_id + blocks — 1;

— Determine if the corruption is wide spread

Options to scan DB ( using DBV or RMAN)

% dbv userid=<username/password> file=<full ptah to corrupted file> blocksize=<tablespace block size>

RMAN> backup validate check logical database; ( this will scan the entire Database)

RMAN> backup validate check logical tablespace <TS_NAME> ; ( this will scan the entire tablespace listed)

RMAN> backup validate check logical datafile X; (Where X is the datafile in question)

When either RMAN command is complete review:

SQL> select * from v$database_block_corruption ( This will list corrupted blocks found within DB)

— The following note provided avenues into resolving the corruption:

Handling Oracle Block Corruptions in Oracle7/8/8i/9i/10g/11g (Doc ID 28814.1)

Addressing an ORA-00376 (file <name> cannot be read at this time)

— If the file is physically present then ensure that the permissions and size of file is as expected.

— Check to see if the file is online — look at v$recover_file. If the file is offline you will need to recover and online it assuming you are running in archivelog mode.

— To assist Oracle supporting you please upload the results of the script below

Addressing an ORA-01194 (file <name> needs more recovery to be consistent) & ORA-01547 (warning: RECOVER succeeded but OPEN RESETLOGS would get error)

— The error indicates that the recovery may still have files which are in an inconsistent state. This may be due to datafiles being restored at different times or we have not applied enough achivelogs following a backup.

— At a minimum, all archivelogs that were generated during an online backup must be applied during recovery.

Addressing an ORA-01135 (file %s accessed for DML/query is offline)

   — The File is OFFLINE, and you must set in Online to be accessible

      Example:
   SQL> recover datafile ‘%s’;
   SQL> alter database datafile ‘%s’ online;

To assist Oracle support diagnose the issue please provide the following:

Please provide the output in the service request that you may raise. (upload recovery_info.txt)

set pagesize 20000
set linesize 180
set pause off
set serveroutput on
set feedback on
set echo on
set numformat 999999999999999
Spool recovery_info.txt
select substr(name, 1, 50), status from v$datafile;
select substr(name,1,40), recover, fuzzy, checkpoint_change# from v$datafile_header;
select GROUP#,substr(member,1,60) from v$logfile;
select * from v$recover_file;
select distinct status from v$backup;
select hxfil FILENUMBER,fhsta STATUS,fhscn SCN,fhrba_Seq SEQUENCE from x$kcvfh;
select distinct (fuzzy) from v$datafile_header;
spool off
exit

Источник

The term media recovery means the restoration of files that have been lost or damaged, owing to the failure of the underlying storage media (usually a disk of some sort) or accidental removal of files. Usually, you know that media recovery is required through an error such as the following:

ORA-01157: cannot identify/lock data file 1 - see DBWR trace file

ORA-01110: data file 1: '/u01/dbfile/O12C/system01.dbf'

The error may be displayed on your screen when performing DBA tasks, such as stopping and starting the database. Or, you might see such an error in a trace file or the alert.log file. If you don’t notice the issue right away, with a severe media failure, the database will stop processing transactions, and users will start calling you.

To understand how Oracle determines that media recovery is required, you must first understand how Oracle determines that everything is okay. When Oracle shuts down normally (IMMEDIATE, TRANSACTIONAL, NORMAL), part of the shutdown process is to flush all modified blocks (in memory) to disk, mark the header of each data file with the current SCN, and update the control file with the current SCN information.

Upon startup, Oracle checks to see if the SCN in the control file matches the SCN in the header of the data files. If there is a match, then Oracle attempts to open the data files and online redo log files. If all files are available and can be opened, Oracle starts normally. The following query compares the SCN in the control file (for each data file) with the SCN in the data file header:

SET LINES 132

COL name             FORM a40

COL status           FORM A8

COL file#            FORM 9999

COL control_file_SCN FORM 999999999999999

COL datafile_SCN     FORM 999999999999999

--

SELECT

 a.name

,a.status

,a.file#

,a.checkpoint_change# control_file_SCN

,b.checkpoint_change# datafile_SCN

,CASE

   WHEN ((a.checkpoint_change# - b.checkpoint_change#) = 0) THEN 'Startup Normal'

   WHEN ((b.checkpoint_change#) = 0)                        THEN 'File Missing?'

   WHEN ((a.checkpoint_change# - b.checkpoint_change#) > 0) THEN 'Media Rec. Req.'

   WHEN ((a.checkpoint_change# - b.checkpoint_change#) < 0) THEN 'Old Control File'

   ELSE 'what the ?'

 END datafile_status

FROM v$datafile        a -- control file SCN for datafile

    ,v$datafile_header b -- datafile header SCN

WHERE a.file# = b.file#

ORDER BY a.file#;

If the control file SCN values are greater than the data file SCN values, then media recovery is most likely required. This would be the case if you restored a data file from a backup, and the SCN in the restored data file had an SCN less than the data file in the current control file.

Tip The V$DATAFILE_HEADER view uses the physical data file on disk as its source. The V$DATAFILE view uses the control file as its source.

You can also directly query the V$DATAFILE_HEADER for more information. The ERROR and RECOVER columns report any potential problems. For example, a YES or null value in the RECOVER column indicates that there is a problem:

SQL> select file#, status, error, recover from v$datafile_header;

Here is some sample output:

     FILE# STATUS  ERROR                REC

---------- ------- -------------------- ---

         1 ONLINE  FILE NOT FOUND

         2 ONLINE                       NO

         3 ONLINE                       NO

Вас заинтересует / Intresting for you:

Источник

ORA-01110 & ORA-01180: can not create datafile in Oracle Database

ORA-01110 ORA-01180 Oracle Errors

Ok, so this one’s interesting..

This happened with me while creating a Oracle Standby where I was replicating the Primary Database to Standby with the RMAN Backup. I copied the backup pieces to the Standby Server after I had finished all the prerequisites of Installing Oracle Database in UNIX/LINUX Systems (IBM AIX 64-bit in my case). After that instance was brought to nomount state with primary database PFILE once it was made sure that all the necessary changes for the Standby Database in the PFILE were done. Controlfile backup for Standby Database was restored and the database was brought more further i.e. to mount state. Only restore was left so I ran the restore command and this happened,

Starting restore at 13-OCT-17
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
using channel ORA_DISK_4
using channel ORA_DISK_5
using channel ORA_DISK_6
using channel ORA_DISK_7
using channel ORA_DISK_8

creating datafile file number=1 name=/data/dcstndby/system.279.915710127
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 10/13/2017 07:08:04
ORA-01180: can not create datafile 1
ORA-01110: data file 1: ‘/data/dcstndby/system.279.915710127’

RMAN>

Resolution:

I am mentioning below 3 main scenarios which majorly brings these errors,

1. There’s something wrong with the database incarnation. Either you need to fix your oracle database’s incarnation or the issue might be related to the parameter file that you are using to bring the instance up. To understand this, please follow Oracle Docs — Doc IDs 1573040.1 and Doc ID 727655.1

2. The problem can be related to privilege issues majorly in UNIX/LINUX systems. Make sure you are not making such kind of mistake.

3. RMAN doesn’t know of the database backups. This was my case where I forgot to catalog the backup files in Oracle RMAN repository from where I needed to perform the restore.

I hope this helps !!

Источник

“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&gt;
 
SQL&gt; ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO scope=both sid='*';
 
System altered.
 
SQL&gt; 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)

Источник

November 1^st, 2012 | Posted by Rajeev Jha in Data Guard

Case Study

Today I got a new problem in one of our banking clients. After verifying the error, alert.log & Trace file I got the actual problem that on production database one datafile is created which is not created on the desired location. Now we have to solve the error because a bank needs to synchronize the database on an urgent basis.

Note: If your STANDBY_FILE_MANAGEMENT parameter is not configured Auto in Data Guard environment and the file is created then the file will be created on standby server with unnamed file name in DBS directory.

This is what happened in this case. Now I am going to show you how to solve the problem.

How to resolve ORA-01111, ORA-01110, ORA-01157

There are many reasons for a file being created as UNNAMED or MISSING in the standby database, including insufficient disk space on standby site (or) improper parameter settings related to file management.

STANDBY_FILE_MANAGEMENT enables or disables automatic standby file management. When automatic standby file management is enabled, operating system file additions and deletions on the primary database are replicated on the standby database.

For example if we add a data file on the Primary when parameter STANDBY_FILE_MANAGEMENT on standby set to MANUAL, While recovery process(MRP) is trying to apply archives, Due to that parameter setting it will create an Unnamed file in $ORACLE_HOME/dbs and it will cause to kill MRP process and Errors will be as below.

Errors Received at the time of Recovery:-

SQL> RECOVER STANDBY DATABASE;

ORA-00283: recovery session canceled due to errors