Backup Options   «Prev  Next»

Lesson 6 Restoring files to an alternative location
Objective Restore files to a different location after media failure.

Restoring Oracle Datafiles to an Alternate Location (19c/23c/23ai)

When storage fails or a filesystems/ASM diskgroup becomes unavailable, you can restore Oracle datafiles to a new location and bring the database back online with minimal data loss. This modern workflow uses RMAN, Oracle Managed Files (OMF) and (optionally) the Fast Recovery Area (FRA). It replaces legacy, manual copy-and-rename steps from older releases (8i/9i/10g) and avoids risky operations like SHUTDOWN ABORT unless strictly required.

Before You Start

Fast Path: Whole-Database Restore to a New Location with SET NEWNAME

1) Mount the database (do not open):


-- If the instance is up with file errors, shut it down cleanly if possible
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;

2) Connect RMAN and point datafiles to the alternate location:


rman TARGET /

RUN {
  -- Example for non-OMF file system restore
  SET NEWNAME FOR DATABASE TO '/u02/oradata/DBNAME/%b';
  RESTORE DATABASE;
  SWITCH DATAFILE ALL;
  RECOVER DATABASE;
}

Notes:

3) Bring the database online:


ALTER DATABASE OPEN;

Targeted Restore: One or More Datafiles Only

If only a subset of datafiles is affected (e.g., a single tablespace), you can restore just those files, reducing downtime:


-- Identify file# and current name
SELECT file#, name, status FROM v$datafile ORDER BY file#;

-- Take the affected tablespace offline immediate (if database is open)
ALTER TABLESPACE sales OFFLINE IMMEDIATE;

-- RMAN partial restore to a new location
rman TARGET /

RUN {
  SET NEWNAME FOR DATAFILE 7 TO '/u02/oradata/DBNAME/sales01.dbf';
  RESTORE DATAFILE 7;
  SWITCH DATAFILE 7;
  RECOVER DATAFILE 7;
}

-- Bring tablespace back
ALTER TABLESPACE sales ONLINE;

Multitenant Considerations (CDB/PDB)

For PDB-local datafiles, you can restrict operations to the affected PDB:


-- At CDB$ROOT, ensure PDB is mounted (not open):
ALTER PLUGGABLE DATABASE mypdb CLOSE IMMEDIATE;
ALTER PLUGGABLE DATABASE mypdb OPEN READ ONLY;  -- optional prechecks
ALTER PLUGGABLE DATABASE mypdb CLOSE IMMEDIATE;
ALTER PLUGGABLE DATABASE mypdb OPEN RESTRICTED; -- or MOUNT if supported

-- Use RMAN to restore only mypdb files with SET NEWNAME (similar to above).

Control Files and Online Redo Logs

If a control file is lost and no multiplexed copy is available:


STARTUP NOMOUNT;
-- Restore control file from autobackup (FRA or backup location)
RESTORE CONTROLFILE FROM AUTOBACKUP;
ALTER DATABASE MOUNT;

-- Continue with RESTORE/RECOVER of datafiles as needed

If online redo logs are lost/unavailable:


-- If the database is mounted, you can re-create logs in a new location
ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP <n>;  -- use with caution
-- Or rename if the files still exist but path changed:
ALTER DATABASE RENAME FILE '/old/log1.rdo' TO '/new/log1.rdo';

Best practice: Multiplex control files and redo logs across distinct devices. Avoid excessive copies of the control file to prevent write contention.

FRA/OMF-Aware Restore (Simplest, Recommended)

When FRA and OMF are configured, Oracle can manage file names and placement:


-- Ensure these instance parameters are set
SHOW PARAMETER db_create_file_dest
SHOW PARAMETER db_recovery_file_dest

-- Then a database-level restore often needs no SET NEWNAME
rman TARGET /

RUN {
  RESTORE DATABASE;
  RECOVER DATABASE;
}
ALTER DATABASE OPEN;

Point-in-Time or SCN/Time-Based Recovery (If Corruption/Deletes Occurred)


STARTUP MOUNT;
rman TARGET /

RUN {
  SET UNTIL TIME "to_date('2025-11-09 18:30:00','YYYY-MM-DD HH24:MI:SS')";
  -- or: SET UNTIL SCN 123456789;
  SET NEWNAME FOR DATABASE TO '/u02/oradata/DBNAME/%b';
  RESTORE DATABASE;
  SWITCH DATAFILE ALL;
  RECOVER DATABASE;
}
-- Point-in-time recoveries typically require RESETLOGS:
ALTER DATABASE OPEN RESETLOGS;

Tempfiles

Tempfiles are not backed up by RMAN. If lost, re-create them at the new location:


ALTER DATABASE TEMPFILE '/old/temp01.dbf' DROP;
ALTER TABLESPACE temp ADD TEMPFILE '/u02/oradata/DBNAME/temp01.dbf' SIZE 10G;

Post-Restore Validation


-- Validate datafiles/controlfiles/logs known to the control file
SELECT name, status FROM v$datafile ORDER BY file#;
SELECT name FROM v$controlfile;
SELECT group#, member FROM v$logfile ORDER BY group#, member;

-- Validate recoverability of backups (optional but recommended)
rman TARGET /
VALIDATE DATABASE;

Operational Tips

Legacy Snippet (For Historical Reference Only)

The following 8i/NT-style transcript represents manual copy/rename techniques that are no longer recommended. Modern practice uses RMAN commands shown above.


SQL> SHUTDOWN ABORT
...
C:\>COPY D:\ORCL_BCKUP\*.* D:\ORCL_NEW
...
SQL> STARTUP MOUNT PFILE=D:\ORANT\DBS\INITORCL.ORA
SQL> ALTER DATABASE RENAME FILE 'C:\ORANT\DATABASE\USR1ORCL.ORA'
  TO 'D:\ORCL_NEW\USR1ORCL.ORA';
...
SQL> ALTER DATABASE OPEN;

Inconsistent Backups (Open Database Backups)

In ARCHIVELOG mode, RMAN can restore from an inconsistent (hot) backup and recover by applying archived redo (and online redo when available). In NOARCHIVELOG mode, you must take consistent (cold) backups; RMAN will not permit inconsistent backups in NOARCHIVELOG.

The next lesson should cover backup validation, restore drills, and runbook automation so that the above flow can be executed predictably under pressure.


SEMrush Software 6 SEMrush Banner 6