Recover an Oracle database after restoring from Zmanda Pro Backup Manager

Oracle Database recovery steps using RMAN post Zmanda Pro restoration.

Recovering an Oracle database after restoring from Zmanda Pro involves several steps to ensure your data is properly restored and the database is operational. This guide will walk you through the process, covering two main scenarios:

1. Recovering to the original server.

2. Recovering to a new instance.

Assuming you have all the backup pieces ready in a specific directory on the client, follow these steps to restore the database on the original server:

Case 1: Recovering to the Original Database or Server

If you're restoring to the original database or server, follow these steps:

1. Connect to RMAN using either of these commands:

$ rman target /

or

$ rman target <db_username>/<password>@<SID>

1. Restore Control File (Optional)

If you need to restore the control file from backup:

RMAN> startup nomount;
RMAN> restore controlfile from '<absolute_path_for_controlfile>';

1. Mount the Database

RMAN> alter database mount;

1. Catalog Backup Pieces (If the Control file is restored) Ensure DB is mounted The CATALOG BACKUPPIECE command in Oracle Recovery Manager (RMAN) is used to register or catalog backup pieces that are not currently recorded in the RMAN repository. When you restore backup pieces from a location that RMAN is not aware of, you need to catalog these backup pieces to make them known to RMAN so that they can be used for recovery operations.

   To catalog all the backup-pieces in a directory together , you can use the below command

RMAN> catalog start with '/path/to/backuppieces';

1. Restore Database

RMAN> restore database;

• The RESTORE DATABASE command is used to restore the physical database files (datafiles, control files, and redo log files) from backup sets or backup pieces to their original locations on disk.

• When you execute RESTORE DATABASE, RMAN identifies and restores the necessary backup sets or backup pieces required to rebuild the physical structure of the database.

• However, RESTORE DATABASE does not apply redo logs or perform any media recovery. It only restores the database files to the state they were in at the time the backup was taken.

1. Recover Database

RMAN> recover database;

• The RECOVER DATABASE command is used to apply redo logs to the restored database files to bring them to a consistent state and perform media recovery.

• After executing RESTORE DATABASE to restore the database files, you need to execute RECOVER DATABASE to apply archived redo logs and/or online redo logs to roll forward the changes made to the database since the time of the backup.

• RECOVER DATABASE applies redo logs to recover the database to a specified point in time or up to the most recent redo log available, depending on the recovery options specified.

1. Open the Database

RMAN> alter database open resetlogs;

Choosing Between ALTER DATABASE OPEN and ALTER DATABASE OPEN RESETLOGS

When recovering your Oracle database, you'll need to choose between two commands to open it: ALTER DATABASE OPEN or ALTER DATABASE OPEN RESETLOGS. Your choice depends on your specific recovery scenario.

1. ALTER DATABASE OPEN: The Standard Approach

Use this command when:

• This command is used to open the database in normal mode after a successful restore and recovery operation.

• It is typically used when the database is consistent and does not require any additional recovery or resetting of the redo logs.

• You can use this command when you have performed a complete restore and recovery without any missing or corrupted redo logs.

1. ALTER DATABASE OPEN RESETLOGS: The Fresh Start

This command is necessary when:

• This command is used to open the database and reset the redo log sequence after an incomplete recovery or when the control file has been restored from a backup.

• It is necessary to use RESETLOGS when the database is in an inconsistent state or when there is a need to create a new incarnation of the database.

• Scenarios that require using RESETLOGS include:

  • Restoring a control file from a backup: When you restore a control file from a backup, the database loses its current redo log information. Opening the database with RESETLOGS creates a new redo log sequence and a new incarnation of the database.

  • Performing an incomplete recovery: If you have performed an incomplete recovery, such as restoring until a specific point in time or restoring only a subset of datafiles, opening the database with RESETLOGS is necessary to create a new redo log sequence.

  • Recovering from a backup that is not the latest: If you restore from a backup that is not the most recent one, and there are no available archived redo logs to apply, opening the database with RESETLOGS is required.

Important: Using RESETLOGS is like starting a new book in a series. It creates a new "incarnation" of your database. This means:

• Old backups and logs won't work with this new version

• You should take a full backup right after using RESETLOGS

To use either option, the command looks like this:

ALTER DATABASE OPEN [RESETLOGS | NORESETLOGS];

Its important to note that RESETLOGS starts a new chapter for your database, resetting the redo log sequence to "1". It's a fresh start, ensuring your database is consistent and ready for action.

Case 2: Restore on Another Database (New Instance)

Purpose

You might need to restore a database to a new server or instance for reasons such as:

1. Validating your disaster recovery plan.

2. Moving the database to a new server.

Assumptions

1. The new and original hosts are running the same OS and RDBMS patch sets. Restoring to a different platform is complex and beyond the scope of this guide.

2. All datafiles and tablespaces in the backup are online and accessible.

Restoring the Backup to a New Instance

Once the backup pieces are restored to the new host, proceed with the database restore.

Steps to Perform the Restore on the New Host

---

Step 1: Confirm Database Name Alignment

The database name of the target(new) server and the database being restored must match. If they don't, follow these steps to change the database name.

1.1 Mount the Database

Start by mounting the database using sqlplus:

SQL> STARTUP MOUNT

Check the database name and mode:

SQL> select name, open_mode from v$database;

1.2 Run the NID Utility to Change the Database Name

If the target database name differs, use the NID utility to rename the database:

nid target=sys/password@CURRENT_DBNAME DBNAME=NEW_DBNAME

Sample output:

DBNEWID: Release 12.1.0.1.0 - Production
...
Change database name from P2PRMD2 to P1D2ST? (Y/[N]) => Y
...
Database name changed to P1D2ST.

This changes the database name and DBID. After the change, the database is shut down.

---

Step 2: Restore the SPFILE

Restore the SPFILE from the backup:

RMAN> startup nomount;
RMAN> restore spfile from '<absolute_path_to_backed_up_spfile>';

If you encounter an error like "use TO clause," use:

RMAN> restore spfile to '<abosolute_path_of_your_existing_spfile_of_target_db>' from 'absolute_path_for_backed_up_spfile'path>';

Important:

If the directory structures of the original and new instances are different, you will need to either:

• Edit the parameter file to update the paths, or

• Ensure the required paths exist before mounting the database.

---

Step 3: Edit and Create SPFILE from PFILE

To edit the SPFILE, create a PFILE:

So, if you want to view or edit the "spfile" parameters, you must create "pfile" from it which you can view or edit. This is because, "spfile" is oracle specific crucial encoded file and this file is not directly editable.

To edit the "spfile" parameters, you must create "pfile" using "spfile", later edit this "pfile" as required, and create back the "spfile" using the edited "pfile" .

The below is way to generate the parameter file (pfile) from the restored spfile.

SQL> create pfile='/path/to/pfile.ora' from spfile;

Shutdown the database after creating the pfile:

SQL> shutdown immediate;

Edit the generated PFILE to match the required paths (e.g., controlfile paths, datafile locations), then create a new SPFILE from the edited PFILE:

RMAN> startup nomount pfile='/path/to/edited_pfile.ora';
RMAN> create spfile from pfile='/path/to/edited_pfile.ora';

---

Step 4: Restore the Control File and Mount the Database

Restore the control file from the backup:

RMAN> restore controlfile from '<absolute_path_to_controlfile>';
RMAN> alter database mount;

Change the db_name in spfile using below command

alter system set db_name=<you_new_db_name> scope=spfile;

Rename the spfile to new DB name

mv spfileOldDBNAME.ora spfileNewDbName.ora

Create the password file before proceeding

orapwd file=<abosulte_path_for_password_file> password=Oracle12345 force=y entries=10 format=12

Shutdown and start the database back

shutdown immediate;
startup nomount;
alter database mount;
alter database open resetlogs;
alter system register;

---

Step 5: Catalog the Backup Pieces

If the backup pieces are not yet known to RMAN, use the following command to catalog them:

RMAN> catalog start with '/path/to/backuppieces/';

---

Step 6: Restore the Database

Before restoring, you can preview the restore operation:

RMAN> restore database preview summary;

To restore the database:

RMAN> run {
  SET NEWNAME FOR DATABASE TO '<new_file_path>';
  restore database;
  switch datafile all;
  switch tempfile all;
}

If you don't need to change the file paths, simply run:

RMAN> restore database;

---

Step 7: Recover the Database

Depending on your recovery strategy, perform one of the following:

Complete Recovery

Recover the database to the most recent consistent state:

RMAN> recover database;

Point-in-Time Recovery

If required, recover the database to a specific point in time:

RMAN> recover database until time 'YYYY-MM-DD HH24:MI:SS';

SCN-Based Recovery

Recover the database up to a specific SCN:

RMAN> recover database until scn 123456;

---

Step 8: Open the Database with RESETLOGS

After the recovery is complete, open the database with RESETLOGS:

RMAN> alter database open resetlogs;

---

Step 9: Post-Restore Tasks (Optional)

If the old database will remain online, use the NID utility to change the database name or DBID to avoid confusion:

nid target=sys/password@old_db_name DBNAME=new_db_name