Rename Datafile on Physical Data Guard Fixing Error ORA-01111 and ORA-01110

Publicado em por Jone Jarouj

Sometimes, because you forget to set standby_file_management to AUTO for example, or any other reason you can get the issues below on alert log or starting database, you can rename datafile with a new feature on Data Guard.

ORA-01111: name for data file 40 is unknown - rename to correct file
ORA-01110: data file 40: '/u01/oracle/product/19.3.0.0/dbs/UNNAMED00040'
ORA-01157: cannot identify/lock data file 40 - see DBWR trace file

This tip help you fix the issue without copy datafiles via RMAN or have to set tablespaces in offline.

First, lets take APPLY-OFF on Data Guard:

dgmgrl "/as sysdba"

edit database ORDG set state='APPLY-OFF';

Set standby_file_management to MANUAL if is not set yet:

sqlplus "/as sysdba"

show parameter standby_file_management

alter system set standby_file_management=MANUAL;

After that, executing create datafile command below. You can set a new location, specifying a new location or just setting as new if you are using OMF:

alter database create datafile 'Unnamed datafile' as 'New location';
alter database create datafile 'Unnamed datafile' as New;

Example:

alter database create datafile '/u01/oracle/product/19.3.0.0/dbs/UNNAMED00040' as '+DATA/ORDG/datafile/datafile40.dbf';
alter database create datafile '/u01/oracle/product/19.3.0.0/dbs/UNNAMED00040' as New;

Set standby_file_management to AUTO:

alter system set standby_file_management=AUTO scope=BOTH;

And take Data Guard to APPLY-ON again:

dgmgrl "/as sysdba"

edit database ORDG set state='APPLY-ON';

Deixe um comentário