Problem Description:
Oracle DTC Restore Failing with an error Failed to recover controlfile. ORA-00059: maximum number of DB_FILES exceeded
Cause
The ORA-00059: maximum number of DB_FILES exceeded error indicates that the number of database files (datafiles) exceeds the maximum allowed by your Oracle database configuration. By default, Oracle has a limit on the number of datafiles, and when this limit is reached, you will encounter this error.
Traceback
Oracle.log
released channel: ch0\nRMAN-00571: ===========================================================
\nRMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
\nRMAN-00571: ===========================================================
\nRMAN-03002: failure of sql statement command at 10/18/2024 08:54:36
\nORA-00059: maximum number of DB_FILES exceeded
\n\nRMAN> \n\nRecovery Manager complete.\n: exit status 1"
Resolution
To resolve the ORA-00059 error during the DTC restore, follow the steps below:
1. Check the Current Limit of DB_FILES
If the source database is available, connect to the Oracle instance using SQL*Plus as SYSDBA:
sqlplus / as sysdba
Run the following command to check the current value of the DB_FILES parameter:
SHOW PARAMETER db_files;
Example Output:
NAME TYPE VALUE
--------- ----------- ------
db_files integer 500
This output shows the current limit for datafiles (in this example, 500).
2. Modify the DB_FILES Parameter in Restore Process
When performing an automatic DTC restore, Oracle typically creates a temporary pfile (parameter file) but does not specify a datafile limit. By default, Oracle allows up to 150 datafiles if the DB_FILES parameter is not explicitly set in the pfile.
If your source database has more than 150 datafiles (e.g., 500 datafiles), you must set the DB_FILES parameter explicitly during the restore process.
In the Restore Window, under Other Server Parameters, specify the DB_FILES limit that corresponds to your source database.
For example, if your source database has 500 datafiles, set DB_FILES = 500 in the restore parameters.
3. Restoring with SPFile(In case customers source DB down )
If the source database is down and the DBA team does not know the limit for DB_FILES, you can enable the Restore SPFile option in the restore window.
Restore SPFile option ensures that the instance is started with the same parameters (including DB_FILES) as the source database.
Check the Restore SPFile checkbox during the restore process.
This will restore the SPFile from the source database and automatically configure the correct number of datafiles.
