Standby database UNNAMED file error issue

This Scenario was tested in Active Data Guard 11.2.0.4.

Some time, DBA create or add the data file on primary site with out verifying the file system size on standby site. In this case, standby file creation will fail and log shipping will break in standby database. 

Let us test this scenario. For this testing, devdb12 will be primary DB and devdb12_dg will be standby DB. The standby site /data01 file system size is lower than the primary site.

Let us add data file on primary site.

sys@devdb12> create tablespace test datafile

'/data01/oradata/devdb12/test.f01' size 3000m

/

Tablespace created.

sys@devdb12>

The data file was created successfully in primary. But it is failed in standby due to insufficient disk space. We get the below error message in standby alert log. MRP background process shutdown due to this issue.

The data file is created in primary site and not found in standby database.

sys@devdb12> select status,bytes/1024/1024 from
  2   v$datafile where name= '/data01/oradata/devdb12/test.f01';

STATUS                BYTES/1024/1024
--------------------- ---------------
ONLINE                           3000
sys@devdb12>

sys@devdb12_dg> select status,bytes/1024/1024 from
  2   v$datafile where name= '/data01/oradata/devdb12/test.f01';

no rows selected

sys@devdb12_dg>

However, the data file is created in standby site as below.

SQL> select name from v$datafile where name like '%UNNAME%';

NAME
----------------------------------------------------
/ora/app/oracle/product/11.2.0/db_1/dbs/UNNAMED00006

SQL>

Now the Data Guard replication is broken and file was not able to ship to standby due to sufficient space on the /data01 file system.

How do we fix this issue?

Step 1  Add More space in standby /data01 file system and run the below command in standby database. If you don't have luxury to add space, then we can  map the file to different file system in standby database.

Step 2 Execute the below command in standby database. If you decided to create the data file in different file system, then modify the script accordingly. 

shutdown immediate;
startup nomount;
alter database mount standby database;
alter system set standby_file_management='MANUAL' scope=both;
alter database create datafile  '/ora/app/oracle/product/11.2.0/db_1/dbs/UNNAMED00006' as '/data01/oradata/devdb12/test.f01';
alter system set standby_file_management='AUTO' scope=both;
alter database recover managed standby database disconnect from session;
alter database recover managed standby database cancel;
alter database open;
alter database recover managed standby database using current logfile disconnect;

Step 3   Verify the datafile in standby database.

devdb12_dg> select status,bytes/1024/1024 from
v$datafile where name= '/data01/oradata/devdb12/test.f01';
  2
STATUS                BYTES/1024/1024
--------------------- ---------------
ONLINE                           3000

devdb12_dg>

Step 4 Monitor the alert log on primary and standby and  make sure no issues