ctrl_file_recov_path - Database control file recovery path configuration parameter

This parameter specifies a filesystem path where a copy of critical database control files will reside.

Configuration type: Database
Parameter type: Configurable
Default [range]: Null [any valid path or device]

Terminology

Database path: The hierarchical directory where database informational files and folders are retained. For more information, refer to Database directories and files.
Primary control files: The Db2® database engine stores internal state and control information in files within the database path. A description of these files can be found in Database directories and files. The information in these files is required for database operation. These internal files are often referred to as 'control files', and are fully managed by the Db2 database engine, with no user or administration required.
Recovery path: The directory specified by the ctrl_file_recov_path database configuration parameter.
Recovery database path: The database specific hierarchical directory created within the recovery path, where copies of primary control files (known as recovery control files) are maintained. See Usage for the hierarchical directory structure.
Recovery control files (or recovery control file copies): A copy of the primary control files, maintained for redundancy purposes, in the path specified by the ctrl_file_recov_path database configuration parameter.
FODC_RecovPath: A first occurrence data capture (FODC) package that is created for informational purposes, when a backup of the recovery database directory may be useful for future reference or problem determination, or during problems or inconsistencies detected in the recovery database path.

Description

In the event that primary control files are inadvertently deleted, corrupted, or no longer accessible within the database path, the database will no longer function properly, and a database restore may be required.

By specifying ctrl_file_recov_path, a copy of the primary control files is also placed in the specified path, and maintained during runtime. Any runtime changes to the primary control files will also be applied to the recovery control files. In the event that the control files are deleted, corrupted, or no longer accessible, IBM® Support can provide guidance to recover from the situation using the recovery control file copies.

Restrictions

The feature is currently not supported in Db2 pureScale® environments.

Usage

Since the objective of specifying a control file recovery path is to provide redundancy in the event that primary control files within the database path are deleted, corrupted, or no longer accessible, the specified ctrl_file_recov_path cannot overlap with a database path (for details see db_path). For added resiliency, it is recommended to specify a control file recovery path which is on a different filesystem than the database path.

In a Database Partitioning Feature (DPF) environment, the specified ctrl_file_recov_path must be accessible to each database partition. A partition is allowed to have a specific recovery path which only exists on the current partition.

The specified ctrl_file_recov_path will be appended automatically with the instance-home directory, instance-name subdirectory, partition-global directory, and member-specific directory, in the following manner:

<PATH>/<instance_home_dir>/<instance_owner>/NODExxxx/SQLyyyyy/MEMBERzzzz/

For example:

<PATH>/home/db2inst1/db2inst1/NODE0000/SQL00001/MEMBER0000/

When the ctrl_file_recov_path is first set, or an existing path is modified, validation and configuration of the newly specified path will occur during the next database activation, and the primary control files will be copied from the database path into the recovery database path. All subsequent updates to the primary control files will also be made in the recovery control file copies.

When the ctrl_file_recov_path path is reset to NULL, deletion of the database-specific sub-folders and recovery control file copies within the previously specified recovery database path will occur during the next database activation. Sub-folders and files within the previously specified recovery path that belong to other databases or database partitions are not affected.

If the specified ctrl_file_recov_path is a symbolic link, the symbolic link will be saved to ctrl_file_recov_path.

Examples

Enabling the recovery control file path

When the ctrl_file_recov_path database configuration value is configured, database activation will print an informational db2diag.log entry indicating "Control file recovery startup processing has completed." with "Effective control file recovery path:<path>" indicating the path which has been successfully initialized. For example:

$ db2 UPDATE DB CFG FOR dbname1 USING CTRL_FILE_RECOV_PATH /some_filesystem/recov_path_dbname1
     DB20000I  The UPDATE DATABASE CONFIGURATION command completed successfully.
     SQL1363W  One or more of the parameters submitted for immediate modification were not changed dynamically. For these configuration parameters, the database must be shutdown and reactivated before the configuration parameter changes become effective.

$ db2 deactivate db dbname1
     DB20000I  The DEACTIVATE DATABASE command completed successfully.

$ db2 activate db dbname1
     DB20000I  The ACTIVATE DATABASE command completed successfully.

$ less db2diag.log
     2021-09-27-16.35.45.721088-420 E609341E618           LEVEL: Info
     PID     : 26473                TID : 140325389592320 PROC : db2sysc
     INSTANCE: db2inst1             NODE : 000            DB   : DBNAME1
     APPHDL  : 0-217                APPID: *LOCAL.db2inst1.210927233802
     AUTHID  : DB2INST1             HOSTNAME: hostname1
     EDUID   : 38                   EDUNAME: db2agent (DBNAME1)
     FUNCTION: DB2 UDB, base sys utilities, sqleInitCtrlFileRecovPath, probe:1423
     DATA #1 : <preformatted>
     Control file recovery startup processing has completed. 
       Effective control file recovery path: /some_filesystem/recov_path_dbname1

Disabling the recovery control file path

When the ctrl_file_recov_path is disabled by setting the value to NULL, database activation will print an informational db2diag.log entry indicating "Control file recovery startup processing has completed", with "Control file recovery path not enabled". For example:

$ db2 UPDATE DB CFG FOR dbname1USING CTRL_FILE_RECOV_PATH NULL
     DB20000I  The UPDATE DATABASE CONFIGURATION command completed successfully.
     SQL1363W  One or more of the parameters submitted for immediate modification were not changed dynamically. For these configuration parameters, the database must be shutdown and reactivated before the configuration parameter changes become effective.

$ db2 deactivate db dbname1
     DB20000I  The DEACTIVATE DATABASE command completed successfully.

$ db2 activate db dbname1
     DB20000I  The ACTIVATE DATABASE command completed successfully.

$ less db2diag.log
     2021-09-27-16.35.45.721088-420 E609341E618           LEVEL: Info
     PID: 26473                     TID : 140325389592320 PROC : db2sysc
     INSTANCE: db2inst1             NODE : 000            DB   : DBNAME1
     APPHDL  : 0-217                APPID: *LOCAL.db2inst1.210927233802
     AUTHID  : DB2INST1             HOSTNAME: hostname1
     EDUID: 38                      EDUNAME: db2agent (DBNAME1)
     FUNCTION: DB2 UDB, base sys utilities, sqleInitCtrlFileRecovPath, probe:1443
     DATA #1 : <preformatted>
     Control file recovery startup processing has completed.
       Control file recovery path not enabled.

Troubleshooting

Note: The db2diag.log and administration notification (.nfy) log entries with function token "CtrlFileRecov" are associated with ctrl_file_recov_path conditions.

Missing or inaccessible primary control files causing database activation or deactivation to fail

In the event that one or more primary control files within the database path are missing or are no longer accessible (even if the recovery control files are accessible), database activation may fail with an SQL1036C error, and db2diag.log entries indicating an error opening a control file. For example:

$ db2 activate db db1157
     SQL1036C  An I/O error occurred while accessing the database.  SQLSTATE=58030

$ less db2diag.log
     2021-09-28-23.13.13.573497-240 E2319E730             LEVEL: Severe
     PID     : 10963                TID : 140045067478784 KTID : 12078
     PROC    : db2sysc
     INSTANCE: db2inst1             NODE : 000            DB   : DBNAME1
     APPHDL  : 0-62                 APPID: *LOCAL.db2inst1.210929031313
     AUTHID  : DB2INST1             HOSTNAME: hostname1
     EDUID   : 193                  EDUNAME: db2agent (DBNAME1)
     FUNCTION: DB2 UDB, global services, sqlzCtrlFile::Open, probe:983
     MESSAGE : ZRC=0x860F000A=-2045837302=SQLO_FNEX "File not found.
               "DIA8411C A file "" could notbe found.
     DATA #1 : String, 40 bytes
     Could not open the primary control file.
     DATA #2 : String, 53 bytes
     /home/db2inst1/db2inst1/NODE0000/SQL00004/SQLOGCTL.GLFH.1

To recover from this situation, IBM Support can provide guidance with using the recovery control file copies to re-create the missing or damaged primary control files.

Missing or inaccessible recovery control files cause database activation to fail

In the event that one or more recovery control files within the ctrl_file_recov_path are no longer accessible or missing (even if the primary control files are accessible), database activation will fail with an SQL1051N error. The administration notification (.nfy) log will contain ADM1716C and ADM14001C messages. In addition, an FODC_RecovPath_<timestamp>/ folder is generated within the database’s DIAGPATH with internal information which may be useful for IBM Support, including a saved copy of all available recovery control files. For example the log may contain:

$ db2 activate db dbname1
     SQL1051N  The path "/some_filesystem/recov_path_dbname1/db2inst1/NODE0000/SQL00004/SQLOGCTL.GLFH." does not exist or is not valid.  SQLSTATE=57019

$ less db2inst1.nfy

     2021-09-28-08.25.15.781991   Instance:dsciaraf   Node:000
     PID:5501(db2agent (DBNAME1))   TID:139853882713856   Appid:*LOCAL.dsciaraf.210928152515
     base sys utilities  sqleValidateActCtrlFileRecovDbDir Probe:1893   Database:DBNAME1
     
     ADM1716C  The database manager cannot start the database on database partition"0" because the database manager cannot open the recovery control file"/some_filesystem/recov_path_dbname1/db2inst1/NODE0000/SQL00004/SQLOGCTL.GLFH.1".

     2021-09-28-08.25.15.782361   Instance:dsciaraf   Node:000
     PID:5501(db2agent (DBNAME1))   TID:139853882713856   Appid:*LOCAL.dsciaraf.210928152515
     base sys utilities  sqleGenerateCtrlFileRecovFODC Probe:10   Database:DBNAME1
     
     ADM14001C  An unexpected and critical error has occurred: "RecovPath". The instance may have been shutdown as a result. "Automatic" FODC (First OccurrenceData Capture) has been invoked and diagnostic information has been recorded in directory"/home/db2inst1/sqllib/db2dump/FODC_RecovPath_2021-09-28-08.25.15.782163_0000/". 
     Please look in this directory for detailed evidence about what happened and contact IBM support if necessary to diagnose the problem.

$ less db2diag.log
     
     2021-09-28-23.33.20.358437-240 E60306E739            LEVEL: Severe
     PID     : 15372                TID : 140430251386624 KTID : 16386
     PROC    : db2sysc
     INSTANCE: db2inst1             NODE : 000            DB   : DBNAME1
     APPHDL  : 0-28                 APPID: *LOCAL.DB2.210929033218
     AUTHID  : DB2INST1             HOSTNAME: hostname1
     EDUID   : 179                  EDUNAME: db2stmm (DBNAME1)
     FUNCTION: DB2 UDB, global services,sqlzCtrlFile::Open, probe:1208
     MESSAGE : ZRC=0x860F000A=-2045837302=SQLO_FNEX "File not found.
               "DIA8411C A file "" could not be found.
     DATA #1 : String, 41 bytes
     Could not open the recovery control file.
     DATA #2 : String, 64 bytes/some_filesystem/recov_path_dbname1/db2inst1/NODE0000/SQL00004/SQLOGCTL.GLFH.1

When this condition occurs, to proceed with database activation the ctrl_file_recov_path must first be disabled by setting the value to NULL, followed by a database activation. For example:

$ db2 UPDATE DB CFG FOR dbname1 USING CTRL_FILE_RECOV_PATH NULL
     DB20000I  The DEACTIVATE DATABASE command completed successfully.

$ db2 activate db dbname1
     DB20000I  The ACTIVATE DATABASE command completed successfully.

Then the ctrl_file_recov_path can be re-enabled as previously described. For example:

$ db2 UPDATE DB CFG FOR dbname1 USING CTRL_FILE_RECOV_PATH /some_filesystem/recov_path_dbname1
     DB20000I  The UPDATE DATABASE CONFIGURATION command completed successfully.
     SQL1363W  One or more of the parameters submitted for immediate modification were not changed dynamically. For these configuration parameters, the database must be shutdown and reactivated before the configuration parameter changes become effective.

$ db2 deactivate db dbname1
     DB20000I  The DEACTIVATE DATABASE command completed successfully.

$ db2 activate db dbname1
     DB20000I  The ACTIVATE DATABASE command completed successfully.

Database deactivation fails due to missing or inaccessible recovery control files

In the event that one or more recovery control files within the ctrl_file_recov_path are no longer accessible or missing (even if the primary control files are accessible), database deactivation may fail with an SQL1042C or similar error. The db2diag.log will contain entries indicative of a failure to access a control file path or file. For example:

$ db2 deactivate db db1157
     SQL1042C  An unexpected system error occurred.  SQLSTATE=58004

$ less db2diag.log
     2021-09-27-16.35.45.721088-420 E609341E618           LEVEL: Error
     PID     : 26473                TID : 140325389592320 PROC : db2sysc
     INSTANCE: db2inst1             NODE : 000            DB   : DBNAME1
     APPHDL  : 0-217                APPID: *LOCAL.db2inst1.210927233802
     AUTHID  : DB2INST1             HOSTNAME: hostname1
     EDUID   : 38                   EDUNAME: db2agent (DBNAME1)
     FUNCTION: DB2 UDB, global services, sqlzCtrlFile::Open, probe:1200
     MESSAGE : ZRC=0x870F0011=-2029060079=SQLO_PATH "aninvalid path"
               DIA8514C An invalid file path, "", was specified.
     DATA #1 : String, 42 bytes
     /home/db2inst1/db2inst1/NODE0000/SQL00004/
     DATA #2 : sqlzCtrlFile, PD_TYPE_sqlzCtrlFile, 13120 bytes

When this condition occurs, a db2stop force operation can be used to force the database instance down. The db2stop force operation will attempt to deactivate the database first, then wait the number of minutes defined by the start_stop_time dbm configuration parameter, before forcibly stopping the database instance, returning an SQL6037N error. For example:

$ db2stop force
     SQL6037N  START or STOP DATABASE MANAGER timeout value was reached.

Then the ctrl_file_recov_path must then be disabled by setting the value to NULL, followed by a database activation. For example:

$ db2 UPDATE DB CFG FOR dbname1 USING CTRL_FILE_RECOV_PATH NULL
     DB20000I  The DEACTIVATE DATABASE command completed successfully.

$ db2 activate db dbname1
     DB20000I  The ACTIVATE DATABASE command completed successfully.

The ctrl_file_recov_path can be re-enabled as previously described.

The ctrl_file_recov_path is a symbolic link whose resolved path is modified

If the ctrl_file_recov_path was previously enabled using a path which is a symbolic link, and the symbolic link is modified at the OS, or filesystem, level to point to a new resolved path but the contents of the previous recovery path are not copied to the new location, then the next database activation will fail with an SQL1051N error because the recovery control file copies can not be found within the new resolved path. In this scenario, issue an UPDATE DB CFG for <dbname> using ctrl_file_recov_path NULL command and reactivate the database to disable control file recovery, and then issue an UPDATE DB CFG for <dbname> using ctrl_file_recov_path <symlink path> command and reactivate the database to re-enable the feature, which will refresh the recovery control files in the new resolved path.