Control File Character Set and Recovery in Oracle 26ai

What the Character-Set Clause Provides

Identify the Existing Database Character Set

When the database is open and you have appropriate access, inspect its persistent NLS properties. The following SQL query distinguishes the database character set from the national character set:

SELECT parameter, value
FROM nls_database_parameters
WHERE parameter IN ('NLS_CHARACTERSET', 'NLS_NCHAR_CHARACTERSET')
ORDER BY parameter;

NLS_CHARACTERSET describes the database character set used for ordinary character data. NLS_NCHAR_CHARACTERSET describes the national character set associated with national character datatypes. Neither a client's display encoding nor a session language preference establishes the database character set. Changing a client setting does not convert the database.

Record the database identity and connection context alongside the query output. In a multitenant deployment, note whether you are connected to the CDB root or a particular PDB. A control-file recovery procedure concerns the physical database and must use the appropriate CDB-level information. A query performed in an application PDB should not silently become the source of assumptions about every container or the CDB control file.

If the database cannot open, do not guess the value from a regional operating-system setting. Consult previously captured metadata, the reviewed reconstruction script and available diagnostic records. Verify that those records belong to the same database and predate no relevant migration or structural change. This is one reason to keep recovery documentation outside the storage whose failure could make the database unavailable.

Choose the Appropriate Control-File Recovery Method

Re-creating a control file is exceptional. First determine whether a current, intact multiplexed copy is available. If one active copy is damaged and another remains intact, Oracle documents recovery using the good copy with the instance shut down. The exact location and restart procedure depend on whether the failed destination can be reused or must be replaced. This case differs from restoring an older backup that requires subsequent recovery.

If all current copies are unavailable but an appropriate binary control-file backup exists, evaluate the documented RMAN restore procedure. The backup age, database identity, available redo and recovery target all matter. A trace-generated reconstruction script is another resource, but it should not displace a usable recovery method merely because it is easy to read.

Oracle limits CREATE CONTROLFILE to specific situations, such as loss of all control files with no backup available, or certain deliberate configuration changes. The statement can overwrite existing control files when REUSE is specified and can affect access to database files if its inventory is wrong. Do not use it as a routine character-set maintenance operation. For a planned database rename, consult the separate DBNEWID guidance rather than treating a rename as part of this lesson.

Modern Reconstruction Workflow

The figure combines the earlier sequence into one workflow. Its upper path describes exceptional control-file reconstruction; its lower path describes actual character-set migration. The steps below explain the decisions behind the reconstruction path. They are preparation guidance, not a universal script that can be pasted into a production session.

1. Protect the Database and Capture Its Inventory

Before a planned reconstruction, establish suitable backups and a recovery target. Capture the database identity, current datafile inventory, redo-log groups and members, control-file destinations, archive mode and relevant logging settings. Keep copies of initialization settings and the operational recovery plan. For a CDB, account for its root, seed and PDB files instead of adapting a short example from an older non-CDB database.

The following queries illustrate inventory collection when the required views are available. They do not create backups or certify that recovery will succeed:

SELECT name FROM v$datafile;
SELECT member FROM v$logfile;
SELECT value FROM v$parameter WHERE name = 'control_files';

The old instruction to prefer a cold backup in every case is too broad. A planned offline preservation procedure and an RMAN online backup serve different purposes. An appropriately configured ARCHIVELOG database can be protected with RMAN online backups and the required archived redo. RMAN does not back up online redo logs, so a database backup command is not equivalent to an offline copy of every file named in an older checklist.

2. Export Reconstruction SQL to a Trace File

While an existing control file is usable, an authorized SQL session can generate the reconstruction statements:

ALTER DATABASE BACKUP CONTROLFILE TO TRACE;

This is SQL, commonly issued through SQL*Plus. It is not a standalone RMAN command. The generated trace contains SQL text that can be reviewed to reproduce the control file; it is not a binary control-file backup. Binary backups, datafile backups and archived redo remain separate recovery resources. Store the reviewed trace script with the recovery documentation and refresh it after relevant structural changes.

Locate the actual diagnostic output rather than assuming that every 26ai installation writes to an old fixed directory. The diagnostic view can identify the current session's default trace file:

SELECT value
FROM v$diag_info
WHERE name = 'Default Trace File';

Use the alert log and diagnostic tools as needed to confirm the generated file. This path is on the database server, not necessarily the machine running your SQL client. Confirm that the file contains the expected database identity and current statement before retaining it as recovery material.

3. Review the Statement and Recovery Branch

Review the real trace-generated statement against the captured inventory. Check database identity, file paths, redo members, archive mode and the existing character set. For ASM or Oracle Managed Files, use the documented filename forms appropriate to the operation. Preserve required logging behavior, including FORCE LOGGING when that setting must continue after reconstruction.

Oracle's statement has special handling for read-only files and temporary files. Do not add tempfiles to its DATAFILE clause or assume that copying every filename into one list is sufficient. Follow the documented procedure for restoring access to read-only tablespaces and re-establishing temporary files. Review the actual CDB inventory and generated script instead of inventing omitted entries.

For a database whose existing character set is AL32UTF8, the relevant fragment would be:

-- Illustrative fragment within a complete, reviewed statement:
-- CHARACTER SET AL32UTF8;

This commented fragment is not an executable reconstruction command. Use AL32UTF8 here only if it is the database's actual character set. Substituting it for another encoding would misdescribe existing data. The complete statement must also specify the correct recovery branch and other required clauses.

NORESETLOGS and RESETLOGS are alternative choices tied to redo availability and the recovery scenario. A slash-separated pair in a teaching diagram is not executable SQL. Current online redo is required for the documented NORESETLOGS branch. Do not choose RESETLOGS merely because a replacement control file is being created.

4. Shut Down and Start Without Mounting

For planned reconstruction, coordinate the outage and follow the reviewed shutdown procedure. Oracle's control-file guidance prefers a normal shutdown when possible. Before reconstruction, preserve the files required by that procedure and verify the initialization settings that the next startup will use. In RAC, ensure no other instance has the database mounted.

STARTUP NOMOUNT

This SQL*Plus command starts the instance without mounting or opening the database. It does not read the control file to mount the database. That is the state required before issuing the reviewed CREATE CONTROLFILE statement. Startup success alone does not prove that the datafiles or recovery resources are usable.

Check that CONTROL_FILES, or applicable Oracle Managed Files destination settings, point to the intended active control-file locations. A retained offline backup is a recovery resource, not an additional live file to include in the active parameter list. Avoid overwriting surviving evidence or a usable current copy while investigating an incident.

5. Re-create and Recover According to the Plan

Issue the complete, reviewed reconstruction statement only after its prerequisites are satisfied. Successful control-file creation mounts the database, so do not automatically append another mount command copied from a different recovery recipe. The next action depends on the selected branch and the consistency of the available database files.

Perform the media recovery required by the scenario. The recovery procedure must account for backup age, archived redo, current redo availability and any missing or restored files. If the operation reports a missing file or redo requirement, reconcile it with the inventory and plan. Repeatedly changing clauses to bypass an error can conceal the original problem and make the eventual result harder to assess.

The exact commands for a lost control file, incomplete recovery and loss of online redo differ. Keep the chosen reconstruction procedure together with its recovery instructions. A valid CHARACTER SET clause addresses one metadata requirement; it does not repair missing datafiles, supply unavailable redo or replace a complete recovery strategy.

6. Open, Verify and Renew Protection

Open normally or with RESETLOGS as required by the completed recovery procedure. These statements illustrate the alternatives; they are not successive steps:

-- Normal open, when the recovery procedure permits it:
ALTER DATABASE OPEN;

-- Alternative open, only when RESETLOGS is required:
-- ALTER DATABASE OPEN RESETLOGS;

Review the alert log for discrepancies between control-file records and the dictionary. Investigate missing-file entries and verify the expected datafiles, redo groups, tablespaces and containers. Check temporary-file availability and any read-only files handled separately. Confirm archive mode and logging settings before returning the database to normal workload.

After successful recovery, establish fresh backups and reviewed reconstruction documentation. Verify the intended multiplexed control-file copies and their locations. Record the recovery outcome and any incarnation change associated with RESETLOGS. A successful open is a significant milestone, but application checks, backup verification and a tested future recovery path are still part of operational acceptance.

Keep Multiplexing and Backups Distinct

Oracle recommends at least two control-file copies on different physical disks. Multiplexing maintains active copies during normal database operation; it protects against loss of an individual copy or destination. It does not preserve an earlier point in time or replace retained backups. Copies sharing one underlying failure domain provide less protection than their different directory names suggest.

Back up control files after structural changes and verify that the recovery team can locate the backups. A binary backup retains control-file contents, while a trace records reconstruction SQL. Keep both concepts clear in scripts and documentation. The Oracle 26ai control-file administration guide explains these procedures.

When the Goal Is Actual Character-Set Migration

If the goal is to change the database character set, prepare a separate migration project. Assess the source encoding, stored data, target repertoire and container configuration. Scan for invalid representations and values that would expand beyond column limits. Resolve those findings before conversion, then validate representative application data and behavior after migration.

Oracle documents supported Unicode migration with the Database Migration Assistant for Unicode where applicable, and export/import approaches using Data Pump. Check the chosen tool's release, source and target requirements before selecting a method. Oracle 26ai desupports the original Export utility, so an old export command should not be assumed to be a current migration solution. Review the 26ai character-set migration guidance.

The control-file clause cannot perform this conversion. A backup also preserves the original data representation; it does not automatically convert it when restored. Treat migration validation and recovery preparation as related responsibilities, with separate evidence for each.

Lesson Checklist

• Record the actual database character set and the relevant container context.
• Choose an intact copy or backup restore when appropriate; justify exceptional reconstruction.
• Retain suitable backups, current inventory and reviewed trace-generated SQL.
• Verify the character-set clause, file locations, logging settings and recovery branch.
• Re-create only from the required NOMOUNT state, then recover and open as the scenario requires.
• Verify the result and renew control-file, database and documentation protection.

The next lesson introduces RMAN's command-line interface for database recovery. Carry forward the distinction between SQL reconstruction statements, binary recovery resources and character-set migration when choosing which operation to perform.