Added Datafile on wrong mountpoint in Dataguard

Oracle Data Guard: Datafile Management in Logical and Physical Standby

This document outlines the procedures for managing datafiles in Oracle Data Guard environments, covering both Logical Standby and Physical Standby databases. It addresses common scenarios such as moving a datafile and registering newly added or moved datafiles.

1. Moving a Datafile in a Logical Standby Database

When you need to move a datafile to a new location on a Logical Standby database, you must temporarily stop the logical standby apply process, move the file at the OS level, and then rename the datafile within the database.

Steps:

1. Stop Logical Standby Apply: Before making any changes to datafiles, you must stop the logical standby apply process to ensure consistency.

   ALTER DATABASE STOP LOGICAL STANDBY APPLY;
   
   

2. Take the Tablespace Offline (Optional but Recommended): Taking the tablespace offline ensures that no operations attempt to access the datafiles during the move.

   ALTER TABLESPACE your_tablespace_name OFFLINE NORMAL; -- Replace your_tablespace_name
   
   

3. Copy the Datafile at the Operating System Level: Use your operating system's copy command to move the datafile from the old (incorrect) location to the new (correct) location.

   cp /wrong/mount/point/old.dbf /right/mount/point/new.dbf
   
   

   (Replace /wrong/mount/point/old.dbf and /right/mount/point/new.dbf with your actual file paths.)

4. Rename the Datafile within the Database: Inform the Oracle database about the new location of the datafile.

   ALTER TABLESPACE your_tablespace_name RENAME DATAFILE '/wrong/mount/point/old.dbf' TO '/right/mount/point/new.dbf'; -- Replace paths
   
   

5. Bring the Tablespace Online: Once the datafile is moved and renamed, bring the tablespace back online.

   ALTER TABLESPACE your_tablespace_name ONLINE;
   
   

6. Start Logical Standby Apply: Finally, restart the logical standby apply process.

   ALTER DATABASE START LOGICAL STANDBY APPLY;
   
   

2. Registering a Datafile in a Physical Standby Database (Addressing ORA-01111/ORA-01157)

When a new datafile is added on the primary database, or an existing datafile is moved, a Physical Standby database might encounter errors like ORA-01111: name for data file is unknown or ORA-01157: cannot identify/lock data file if STANDBY_FILE_MANAGEMENT is set to MANUAL or if the file was not automatically created.

Scenario: You observe errors in the alert log indicating an unknown or unidentifiable datafile, such as:

ORA-01111: name for data file 65 is unknown - rename to correct file
ORA-01110: data file 65: '/UNNAMED00065'
ORA-01157: cannot identify/lock data file 65 - see DBWR trace file

Steps to Resolve and Register the Datafile:

1. Cancel Managed Recovery on Standby: You need to stop the redo apply process to make changes to the standby database.

   ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
   
   

2. Start Managed Recovery (Disconnected Session) (Optional, if you want to keep it running but need to switch to MANUAL first): If you need to ensure the apply process is running in the background but want to switch to manual file management, you might issue this. However, for the CREATE DATAFILE step, STANDBY_FILE_MANAGEMENT must be MANUAL.

   ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
   
   

3. Set STANDBY_FILE_MANAGEMENT to MANUAL: This parameter allows you to manually add or rename datafiles on the standby database.

   ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=MANUAL;
   
   

4. Create/Register the Datafile: Use the ALTER DATABASE CREATE DATAFILE command to register the unknown datafile with its correct path on the standby.

   ALTER DATABASE CREATE DATAFILE '/UNNAMED00065' AS '/new/path/to/datafile.dbf'; -- Replace with your actual path
   
   

   (Note: The /UNNAMED00065 is the temporary name Oracle assigns when it can't find the file. The AS '/new/path/to/datafile.dbf' is the actual correct path on your standby system.)

5. Set STANDBY_FILE_MANAGEMENT back to AUTO: It is highly recommended to set this back to AUTO after manual operations to ensure that future datafiles added on the primary are automatically created on the standby.

   ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;
   
   

6. Restart Managed Recovery: Resume the redo apply process on the physical standby.

   ALTER DATABASE RECOVER MANAGED STANDBY DATABASE;
   
   

   (For background recovery, you can add DISCONNECT FROM SESSION.)

By following these steps, you can effectively manage datafile locations and resolve common datafile-related errors in your Oracle Data Guard configurations.