How to completely restore Oracle DB using RMAN

0
votes

I have an oracle 19C database I needed to upgrade for a test application. Before I ran my database upgrade scripts, I used RMAN to make a backup using the following steps:

shutdown immediate;
startup mount;
alter database archivelog;
alter database open;
BACKUP DATABASE PLUS ARCHIVELOG;

This was the output:

piece handle=C:\USERS\ADMINISTRATOR\DOWNLOADS\WINDOWS.X64_193000_DB_HOME\DATABASE\04VFR0AS_1_1   
tag=TAG20201118T115123 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 18-NOV-20

Now I want to restore my backup as the upgrade was not successful and I used these commands:

shutdown immediate;
startup mount;
restore database;
recover database;
alter database open;

Both the restore and recover processes finished successfully but I don't think the database was completely restored to the previous state before the backup. For e.g, some of the newly created tables and altered table spaces etc from the failed upgrade scripts still exist. How do I restore my database to exactly how it was before the attempted upgrade? I want all new tables any new alterations to tablespaces, settings etc reset to the previous state. What I'm I doing wrongly?

2
databaseoraclerestoreoracle19crman

2 Answers

0
votes

You are doing a full restore and recovery, this will include the latest archived logs so you will end up in the same position you already were in. What you want is to do a Database Point In Time Recovery https://docs.oracle.com/en/database/oracle/oracle-database/19/bradv/user-managed-flashback-dbpitr.html#GUID-2BF2BE5E-3D1B-4F0C-9F10-25D5342F6DB3. Essentially, you need to use the until clause of the recover statement.

0
votes
rman> shutdown immediate;
rman> startup mount;
rman> restore database;
rman> run {
set until SCN <specify_your_desired_SCN_here>;
recover database;}
rman> alter database open resetlogs;

Note: to get SCN, run rman>list backup of database;