NOARCHIVELOG Recovery in Oracle 23c/23ai (Modern Guide)

In NOARCHIVELOG mode, filled online redo logs are reused and never archived. This enables simple setups and some performance savings, but it eliminates point-in-time recovery beyond the most recent consistent backup. The only safe strategy is a scheduled series of consistent (cold) backups taken while the database is shut down or mounted.

What you can (and cannot) recover

• Can recover: The database to the time of the last consistent backup.
• Cannot recover: Any transactions after that backup (no archived redo to roll forward).
• Implication: A media failure after the last backup results in loss of all changes since that backup.

Modern prerequisites and terminology (23c/23ai)

• Use SQL*Plus and RMAN; legacy tools like SVRMGR and CONNECT INTERNAL are obsolete.
• Prefer SPFILE over text init.ora. Edit persistent parameters via ALTER SYSTEM or by recreating SPFILE when necessary.
• Migrate legacy LONG/LONG RAW columns to LOB types where possible; it helps with manageability and backup/restore operations.

Cold (consistent) backup with RMAN — supported approach

In NOARCHIVELOG mode, take backups with the database down or mounted to ensure consistency:

-- As Oracle software owner
$ rman target /

-- Ensure a clean checkpoint and consistent state
RMAN> SHUTDOWN IMMEDIATE;
RMAN> STARTUP MOUNT;

-- Optional: validate space/paths here

-- Take a consistent whole-database backup (datafiles + controlfile + spfile)
RMAN> BACKUP AS BACKUPSET DATABASE INCLUDE CURRENT CONTROLFILE PLUS ARCHIVELOG DELETE INPUT;
-- In NOARCHIVELOG, the "PLUS ARCHIVELOG" will back up any stray archivelogs
-- from prior ARCHIVELOG runs; it's harmless and ensures controlfile capture.

-- Create a control file copy for disaster recovery (optional but recommended)
RMAN> BACKUP CURRENT CONTROLFILE;

-- Done
RMAN> SHUTDOWN;

Store the backup sets and the tuned SPFILE off the server. For small labs, you may also use image copies (BACKUP AS COPY) for fast restore.

Restore scenario (media loss) in NOARCHIVELOG

If a datafile is lost and you are in NOARCHIVELOG mode, restore the last consistent backup and open with RESETLOGS:

$ rman target /

-- Ensure instance is down and files are not busy
RMAN> SHUTDOWN IMMEDIATE;
RMAN> STARTUP NOMOUNT;

-- If control file is lost, restore it first
RMAN> RESTORE CONTROLFILE FROM AUTOBACKUP;

-- Mount the database so datafiles are known
RMAN> ALTER DATABASE MOUNT;

-- Restore all files from the last consistent backup
RMAN> RESTORE DATABASE;

-- No archived redo available to roll forward
-- So recover step will be minimal / metadata only
RMAN> RECOVER DATABASE NOREDO;

-- Open with resetlogs to create a new incarnation
RMAN> ALTER DATABASE OPEN RESETLOGS;

Result: Database returns to the exact point of the last backup; all later transactions are lost.

Recommended operational pattern

1. Schedule frequent cold backups during maintenance windows that your workload can accept (e.g., nightly/weekly).
2. Snapshot/Clone for test: For dev/test, you can snapshot the storage or keep an image copy to rapidly reset the environment.
3. Document RTO/RPO: Make sure business owners accept the data loss (RPO) and downtime (RTO) implied by NOARCHIVELOG.
4. Monitor backup success: Validate backups with RESTORE ... VALIDATE and periodic test restores.

Why most production databases should use ARCHIVELOG

• Enables online (hot) backups with users connected.
• Enables point-in-time recovery to SCN/time before a failure.
• Allows block media recovery and granular restore options.

If you need any of these, switch to ARCHIVELOG and design an RMAN strategy with archived redo destinations, monitoring, and retention.

Quick sanity check (23c/23ai)

-- Check log mode
SQL> SELECT log_mode FROM v$database;

-- Confirm dictionary components are VALID after restore
SQL> SELECT comp_name, status FROM dba_registry ORDER BY 1;

-- Capture a fresh baseline after OPEN RESETLOGS
BEGIN
  DBMS_STATS.GATHER_DICTIONARY_STATS;
END;
/

Key takeaways

• NOARCHIVELOG = cold backups only and no PITR.
• Always OPEN RESETLOGS after a NOARCHIVELOG restore.
• Keep SPFILE, control file autobackups, and RMAN logs off-host.

Mini-glossary (updated)

• Restore: Copy backed-up files back to disk.
• Recover: Apply redo to make files consistent. In NOARCHIVELOG you typically have no redo to apply.
• RESETLOGS: Opens a database with a new incarnation after incomplete or NOARCHIVELOG recovery.
• LGWR: Process that writes redo from memory to online redo logs; they are reused in NOARCHIVELOG.

Next module: Recovering databases in ARCHIVELOG mode (hot backups, PITR, and roll-forward with archived redo).

Recovery Scenario - Quiz