Use RMAN KEEP Backup To Roll Back From an Upgrade

Posted in: Technical Track

There is a requirement to back up a database before an application upgrade to be able to rollback. Guarantee Restore Point (GRP) was first considered by us; however, it looks like there is insufficient space from Flash Recovery Area (FRA).

Further, the duration before the decision to rollback application upgrade is unknown, as it can be a week or a month. We made a decision to create RMAN KEEP UNTIL TIME backup to prevent deletion based on RETENTION POLICY.

Here is a demo showing how to perform a RMAN KEEP backup and to restore from a RMAN KEEP backup. Please note there is no standby configuration for this environment.

Please reference for standby environment Recovering Through the OPEN RESETLOGS Statement.

Perform RMAN KEEP backup:
[oracle@db-fs-1 ~]$ export NLS_DATE_FORMAT="DD-MON-YYYY HH24:MI:SS"
[oracle@db-fs-1 ~]$ rman @ backup_keep.rman
Recovery Manager: Release 12.2.0.1.0 - Production on Thu Feb 20 13:42:07 2020
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
RMAN> spool log to rman_backup_keep_upgrade.log
2> connect target;
3> set echo on
4> show all;
5> run {
6> allocate channel c1 device type disk format '/u01/backup/%d_%I_%T_%U_UPGRADE_%s' MAXPIECESIZE 2G MAXOPENFILES 1;
7> allocate channel c2 device type disk format '/u01/backup/%d_%I_%T_%U_UPGRADE_%s' MAXPIECESIZE 2G MAXOPENFILES 1;
8> allocate channel c3 device type disk format '/u01/backup/%d_%I_%T_%U_UPGRADE_%s' MAXPIECESIZE 2G MAXOPENFILES 1;
9> backup as compressed backupset incremental level 0
10> filesperset 1 check logical database tag='UPGRADE'
11> plus archivelog filesperset 8 tag='UPGRADE'
12> keep until time 'ADD_MONTHS(SYSDATE,6)';
13> }
14> run {
15> allocate channel d1 device type disk format '/u01/backup/CF_%d_%I_%T_%U_UPGRADE_%s';
16> backup current controlfile
17> keep until time 'ADD_MONTHS(SYSDATE,6)' tag='UPGRADE';
18> }
19> list backup of database summary tag='UPGRADE';
20> list backup of archivelog all summary tag='UPGRADE';
21> list backup of controlfile tag='UPGRADE';
22> list backup of spfile tag='UPGRADE';
23> report schema;
24> restore database validate preview from tag='UPGRADE';
25> exit
[oracle@db-fs-1 ~]$
Rename backup directory to prevent accidental deletion from RMAN:
[oracle@db-fs-1 ~]$ mv /u01/backup/ /u01/backup_keep
Review RMAN KEEP backup:
[oracle@db-fs-1 ~]$ ls -lrt /u01/backup_keep/
total 345040
-rw-r----- 1 oracle oinstall 1111552 Feb 20 13:42 HAWK_3291536664_20200220_0hup0qth_1_1_UPGRADE_17
-rw-r----- 1 oracle oinstall 4096 Feb 20 13:42 HAWK_3291536664_20200220_0iup0qth_1_1_UPGRADE_18
-rw-r----- 1 oracle oinstall 3042304 Feb 20 13:42 HAWK_3291536664_20200220_0gup0qth_1_1_UPGRADE_16
-rw-r----- 1 oracle oinstall 1138688 Feb 20 13:42 HAWK_3291536664_20200220_0lup0qti_1_1_UPGRADE_21
-rw-r----- 1 oracle oinstall 1073152 Feb 20 13:42 HAWK_3291536664_20200220_0mup0qtl_1_1_UPGRADE_22
-rw-r----- 1 oracle oinstall 112328704 Feb 20 13:42 HAWK_3291536664_20200220_0kup0qti_1_1_UPGRADE_20
-rw-r----- 1 oracle oinstall 213147648 Feb 20 13:42 HAWK_3291536664_20200220_0jup0qti_1_1_UPGRADE_19
-rw-r----- 1 oracle oinstall 7168 Feb 20 13:42 HAWK_3291536664_20200220_0nup0quf_1_1_UPGRADE_23
-rw-r----- 1 oracle oinstall 10665984 Feb 20 13:42 CF_HAWK_3291536664_20200220_0pup0qui_1_1_UPGRADE_25
-rw-r----- 1 oracle oinstall 8192 Feb 20 13:42 CF_HAWK_3291536664_20200220_0qup0quk_1_1_UPGRADE_26
-rw-r----- 1 oracle oinstall 114688 Feb 20 13:42 CF_HAWK_3291536664_20200220_0rup0qul_1_1_UPGRADE_27
-rw-r----- 1 oracle oinstall 10665984 Feb 20 13:42 CF_HAWK_3291536664_20200220_0sup0qum_1_1_UPGRADE_28
[oracle@db-fs-1 ~]$
Delete RMAN KEEP backup to demonstrate backup is still safe:
[oracle@db-fs-1 ~]$ rman target /
Recovery Manager: Release 12.2.0.1.0 - Production on Thu Feb 20 13:47:07 2020
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
connected to target database: HAWK (DBID=3291536664)
RMAN> list backup summary;
using target database control file instead of recovery catalog
List of Backups
===============
Key TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
------- -- -- - ----------- -------------------- ------- ------- ---------- ---
13 B A A DISK 20-FEB-2020 13:41:06 1 1 YES UPGRADE
14 B A A DISK 20-FEB-2020 13:41:06 1 1 YES UPGRADE
15 B A A DISK 20-FEB-2020 13:42:09 1 1 YES UPGRADE
16 B A A DISK 20-FEB-2020 13:42:09 1 1 YES UPGRADE
17 B A A DISK 20-FEB-2020 13:42:09 1 1 YES UPGRADE
18 B 0 A DISK 20-FEB-2020 13:42:11 1 1 YES UPGRADE
19 B 0 A DISK 20-FEB-2020 13:42:14 1 1 YES UPGRADE
20 B 0 A DISK 20-FEB-2020 13:42:29 1 1 YES UPGRADE
21 B 0 A DISK 20-FEB-2020 13:42:34 1 1 YES UPGRADE
22 B A A DISK 20-FEB-2020 13:42:39 1 1 YES UPGRADE
23 B F A DISK 20-FEB-2020 13:42:40 1 1 NO TAG20200220T134240
24 B F A DISK 20-FEB-2020 13:42:43 1 1 NO UPGRADE
25 B A A DISK 20-FEB-2020 13:42:44 1 1 NO UPGRADE
26 B F A DISK 20-FEB-2020 13:42:45 1 1 NO UPGRADE
27 B F A DISK 20-FEB-2020 13:42:47 1 1 NO UPGRADE
RMAN> list backupset 13;
List of Backup Sets
===================
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ --------------------
13 1.06M DISK 00:00:00 20-FEB-2020 13:41:06
BP Key: 13 Status: AVAILABLE Compressed: YES Tag: UPGRADE
Piece Name: /u01/backup/HAWK_3291536664_20200220_0fup0qri_1_1_UPGRADE_15
Keep: BACKUP_LOGS Until: 20-AUG-2020 13:41:06
List of Archived Logs in backup set 13
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- -------------------- ---------- ---------
1 2 1527374 20-FEB-2020 13:37:48 1528069 20-FEB-2020 13:41:06
RMAN> delete force noprompt backup;
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=31 device type=DISK
List of Backup Pieces
BP Key BS Key Pc# Cp# Status Device Type Piece Name
------- ------- --- --- ----------- ----------- ----------
13 13 1 1 AVAILABLE DISK /u01/backup/HAWK_3291536664_20200220_0fup0qri_1_1_UPGRADE_15
14 14 1 1 AVAILABLE DISK /u01/backup/HAWK_3291536664_20200220_0eup0qri_1_1_UPGRADE_14
15 15 1 1 AVAILABLE DISK /u01/backup/HAWK_3291536664_20200220_0hup0qth_1_1_UPGRADE_17
16 16 1 1 AVAILABLE DISK /u01/backup/HAWK_3291536664_20200220_0iup0qth_1_1_UPGRADE_18
17 17 1 1 AVAILABLE DISK /u01/backup/HAWK_3291536664_20200220_0gup0qth_1_1_UPGRADE_16
18 18 1 1 AVAILABLE DISK /u01/backup/HAWK_3291536664_20200220_0lup0qti_1_1_UPGRADE_21
19 19 1 1 AVAILABLE DISK /u01/backup/HAWK_3291536664_20200220_0mup0qtl_1_1_UPGRADE_22
20 20 1 1 AVAILABLE DISK /u01/backup/HAWK_3291536664_20200220_0kup0qti_1_1_UPGRADE_20
21 21 1 1 AVAILABLE DISK /u01/backup/HAWK_3291536664_20200220_0jup0qti_1_1_UPGRADE_19
22 22 1 1 AVAILABLE DISK /u01/backup/HAWK_3291536664_20200220_0nup0quf_1_1_UPGRADE_23
23 23 1 1 AVAILABLE DISK /u02/oradata/HAWK/autobackup/2020_02_20/o1_mf_s_1032874960_h4wzp07t_.bkp
24 24 1 1 AVAILABLE DISK /u01/backup/CF_HAWK_3291536664_20200220_0pup0qui_1_1_UPGRADE_25
25 25 1 1 AVAILABLE DISK /u01/backup/CF_HAWK_3291536664_20200220_0qup0quk_1_1_UPGRADE_26
26 26 1 1 AVAILABLE DISK /u01/backup/CF_HAWK_3291536664_20200220_0rup0qul_1_1_UPGRADE_27
27 27 1 1 AVAILABLE DISK /u01/backup/CF_HAWK_3291536664_20200220_0sup0qum_1_1_UPGRADE_28
deleted backup piece
backup piece handle=/u01/backup/HAWK_3291536664_20200220_0fup0qri_1_1_UPGRADE_15 RECID=13 STAMP=1032874866
deleted backup piece
backup piece handle=/u01/backup/HAWK_3291536664_20200220_0eup0qri_1_1_UPGRADE_14 RECID=14 STAMP=1032874866
deleted backup piece
backup piece handle=/u01/backup/HAWK_3291536664_20200220_0hup0qth_1_1_UPGRADE_17 RECID=15 STAMP=1032874929
deleted backup piece
backup piece handle=/u01/backup/HAWK_3291536664_20200220_0iup0qth_1_1_UPGRADE_18 RECID=16 STAMP=1032874929
deleted backup piece
backup piece handle=/u01/backup/HAWK_3291536664_20200220_0gup0qth_1_1_UPGRADE_16 RECID=17 STAMP=1032874929
deleted backup piece
backup piece handle=/u01/backup/HAWK_3291536664_20200220_0lup0qti_1_1_UPGRADE_21 RECID=18 STAMP=1032874930
deleted backup piece
backup piece handle=/u01/backup/HAWK_3291536664_20200220_0mup0qtl_1_1_UPGRADE_22 RECID=19 STAMP=1032874933
deleted backup piece
backup piece handle=/u01/backup/HAWK_3291536664_20200220_0kup0qti_1_1_UPGRADE_20 RECID=20 STAMP=1032874930
deleted backup piece
backup piece handle=/u01/backup/HAWK_3291536664_20200220_0jup0qti_1_1_UPGRADE_19 RECID=21 STAMP=1032874930
deleted backup piece
backup piece handle=/u01/backup/HAWK_3291536664_20200220_0nup0quf_1_1_UPGRADE_23 RECID=22 STAMP=1032874959
deleted backup piece
backup piece handle=/u02/oradata/HAWK/autobackup/2020_02_20/o1_mf_s_1032874960_h4wzp07t_.bkp RECID=23 STAMP=1032874960
deleted backup piece
backup piece handle=/u01/backup/CF_HAWK_3291536664_20200220_0pup0qui_1_1_UPGRADE_25 RECID=24 STAMP=1032874963
deleted backup piece
backup piece handle=/u01/backup/CF_HAWK_3291536664_20200220_0qup0quk_1_1_UPGRADE_26 RECID=25 STAMP=1032874964
deleted backup piece
backup piece handle=/u01/backup/CF_HAWK_3291536664_20200220_0rup0qul_1_1_UPGRADE_27 RECID=26 STAMP=1032874965
deleted backup piece
backup piece handle=/u01/backup/CF_HAWK_3291536664_20200220_0sup0qum_1_1_UPGRADE_28 RECID=27 STAMP=1032874967
Deleted 15 objects
RMAN> exit
Recovery Manager complete.
[oracle@db-fs-1 ~]$
[oracle@db-fs-1 ~]$ date; ls -lrt /u01/backup_keep/
Thu Feb 20 13:49:32 CET 2020
total 345040
-rw-r----- 1 oracle oinstall 1111552 Feb 20 13:42 HAWK_3291536664_20200220_0hup0qth_1_1_UPGRADE_17
-rw-r----- 1 oracle oinstall 4096 Feb 20 13:42 HAWK_3291536664_20200220_0iup0qth_1_1_UPGRADE_18
-rw-r----- 1 oracle oinstall 3042304 Feb 20 13:42 HAWK_3291536664_20200220_0gup0qth_1_1_UPGRADE_16
-rw-r----- 1 oracle oinstall 1138688 Feb 20 13:42 HAWK_3291536664_20200220_0lup0qti_1_1_UPGRADE_21
-rw-r----- 1 oracle oinstall 1073152 Feb 20 13:42 HAWK_3291536664_20200220_0mup0qtl_1_1_UPGRADE_22
-rw-r----- 1 oracle oinstall 112328704 Feb 20 13:42 HAWK_3291536664_20200220_0kup0qti_1_1_UPGRADE_20
-rw-r----- 1 oracle oinstall 213147648 Feb 20 13:42 HAWK_3291536664_20200220_0jup0qti_1_1_UPGRADE_19
-rw-r----- 1 oracle oinstall 7168 Feb 20 13:42 HAWK_3291536664_20200220_0nup0quf_1_1_UPGRADE_23
-rw-r----- 1 oracle oinstall 10665984 Feb 20 13:42 CF_HAWK_3291536664_20200220_0pup0qui_1_1_UPGRADE_25
-rw-r----- 1 oracle oinstall 8192 Feb 20 13:42 CF_HAWK_3291536664_20200220_0qup0quk_1_1_UPGRADE_26
-rw-r----- 1 oracle oinstall 114688 Feb 20 13:42 CF_HAWK_3291536664_20200220_0rup0qul_1_1_UPGRADE_27
-rw-r----- 1 oracle oinstall 10665984 Feb 20 13:42 CF_HAWK_3291536664_20200220_0sup0qum_1_1_UPGRADE_28
[oracle@db-fs-1 ~]$
Create table after backup as a marker and table should not exist after restore from RMAN KEEP backup:
13:50:21 SYS @ HAWK:HAWK:>create table t as select name from v$database;
Table created.
13:50:54 SYS @ HAWK:HAWK:>desc t;
Name Null? Type
----------------------------------------- -------- ----------------------------
NAME VARCHAR2(9)

Shutdown database:

13:50:59 SYS @ HAWK:HAWK:>shu abort;
ORACLE instance shut down.
13:51:09 SYS @ HAWK:HAWK:>exit
Perform RESTORE from RMAN KEEP backup:
[oracle@db-fs-1 ~]$ export NLS_DATE_FORMAT="DD-MON-YYYY HH24:MI:SS"
[oracle@db-fs-1 ~]$ rman target /
Recovery Manager: Release 12.2.0.1.0 - Production on Thu Feb 20 13:51:47 2020
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
connected to target database (not started)
Startup database:
RMAN> startup force nomount;
Oracle instance started
Total System Global Area 805306368 bytes
Fixed Size 8625856 bytes
Variable Size 314573120 bytes
Database Buffers 473956352 bytes
Redo Buffers 8151040 bytes
Restore controlfile:
RMAN> restore controlfile from '/u01/backup_keep/CF_HAWK_3291536664_20200220_0sup0qum_1_1_UPGRADE_28';
Starting restore at 20-FEB-2020 13:53:04
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=256 device type=DISK
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/u02/fra/HAWK/controlfile/o1_mf_h4vvt81n_.ctl
Finished restore at 20-FEB-2020 13:53:05
Mount database:
RMAN> alter database mount;
Statement processed
released channel: ORA_DISK_1
Catalog backup:
RMAN> catalog start with '/u01/backup_keep' noprompt;
Starting implicit crosscheck backup at 20-FEB-2020 13:53:49
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=259 device type=DISK
Crosschecked 14 objects
Finished implicit crosscheck backup at 20-FEB-2020 13:53:49
Starting implicit crosscheck copy at 20-FEB-2020 13:53:49
using channel ORA_DISK_1
Finished implicit crosscheck copy at 20-FEB-2020 13:53:49
searching for all files in the recovery area
cataloging files...
no files cataloged
searching for all files that match the pattern /u01/backup_keep
List of Files Unknown to the Database
=====================================
File Name: /u01/backup_keep/HAWK_3291536664_20200220_0lup0qti_1_1_UPGRADE_21
File Name: /u01/backup_keep/HAWK_3291536664_20200220_0jup0qti_1_1_UPGRADE_19
File Name: /u01/backup_keep/HAWK_3291536664_20200220_0iup0qth_1_1_UPGRADE_18
File Name: /u01/backup_keep/HAWK_3291536664_20200220_0hup0qth_1_1_UPGRADE_17
File Name: /u01/backup_keep/HAWK_3291536664_20200220_0gup0qth_1_1_UPGRADE_16
File Name: /u01/backup_keep/HAWK_3291536664_20200220_0mup0qtl_1_1_UPGRADE_22
File Name: /u01/backup_keep/CF_HAWK_3291536664_20200220_0sup0qum_1_1_UPGRADE_28
File Name: /u01/backup_keep/CF_HAWK_3291536664_20200220_0qup0quk_1_1_UPGRADE_26
File Name: /u01/backup_keep/CF_HAWK_3291536664_20200220_0pup0qui_1_1_UPGRADE_25
File Name: /u01/backup_keep/HAWK_3291536664_20200220_0nup0quf_1_1_UPGRADE_23
File Name: /u01/backup_keep/HAWK_3291536664_20200220_0kup0qti_1_1_UPGRADE_20
File Name: /u01/backup_keep/CF_HAWK_3291536664_20200220_0rup0qul_1_1_UPGRADE_27
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: /u01/backup_keep/HAWK_3291536664_20200220_0lup0qti_1_1_UPGRADE_21
File Name: /u01/backup_keep/HAWK_3291536664_20200220_0jup0qti_1_1_UPGRADE_19
File Name: /u01/backup_keep/HAWK_3291536664_20200220_0iup0qth_1_1_UPGRADE_18
File Name: /u01/backup_keep/HAWK_3291536664_20200220_0hup0qth_1_1_UPGRADE_17
File Name: /u01/backup_keep/HAWK_3291536664_20200220_0gup0qth_1_1_UPGRADE_16
File Name: /u01/backup_keep/HAWK_3291536664_20200220_0mup0qtl_1_1_UPGRADE_22
File Name: /u01/backup_keep/CF_HAWK_3291536664_20200220_0sup0qum_1_1_UPGRADE_28
File Name: /u01/backup_keep/CF_HAWK_3291536664_20200220_0qup0quk_1_1_UPGRADE_26
File Name: /u01/backup_keep/CF_HAWK_3291536664_20200220_0pup0qui_1_1_UPGRADE_25
File Name: /u01/backup_keep/HAWK_3291536664_20200220_0nup0quf_1_1_UPGRADE_23
File Name: /u01/backup_keep/HAWK_3291536664_20200220_0kup0qti_1_1_UPGRADE_20
File Name: /u01/backup_keep/CF_HAWK_3291536664_20200220_0rup0qul_1_1_UPGRADE_27
Preview restore to determine restore point:
RMAN> restore database preview summary from tag='UPGRADE';
Starting restore at 20-FEB-2020 13:54:24
using channel ORA_DISK_1
List of Backups
===============
Key TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
------- -- -- - ----------- -------------------- ------- ------- ---------- ---
21 B 0 A DISK 20-FEB-2020 13:42:34 1 1 YES UPGRADE
20 B 0 A DISK 20-FEB-2020 13:42:29 1 1 YES UPGRADE
18 B 0 A DISK 20-FEB-2020 13:42:11 1 1 YES UPGRADE
19 B 0 A DISK 20-FEB-2020 13:42:14 1 1 YES UPGRADE
List of Archived Log Copies for database with db_unique_name HAWK
=====================================================================
Key Thrd Seq S Low Time
------- ---- ------- - --------------------
8 1 4 A 20-FEB-2020 13:42:08
Name: /u02/oradata/HAWK/archivelog/2020_02_20/o1_mf_1_4_h4wzoywg_.arc
9 1 5 A 20-FEB-2020 13:42:38
Name: /u02/oradata/HAWK/archivelog/2020_02_20/o1_mf_1_5_h4wzp43z_.arc
recovery will be done up to SCN 1528156
Media recovery start SCN is 1528156
Recovery must be done beyond SCN 1528160 to clear datafile fuzziness
Finished restore at 20-FEB-2020 13:54:24
Restore database:
RMAN> restore database from tag='UPGRADE';
Starting restore at 20-FEB-2020 13:54:35
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00004 to /u02/oradata/HAWK/datafile/o1_mf_undotbs1_h4vxh5k4_.dbf
channel ORA_DISK_1: reading from backup piece /u01/backup_keep/HAWK_3291536664_20200220_0lup0qti_1_1_UPGRADE_21
channel ORA_DISK_1: piece handle=/u01/backup_keep/HAWK_3291536664_20200220_0lup0qti_1_1_UPGRADE_21 tag=UPGRADE
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00007 to /u02/oradata/HAWK/datafile/o1_mf_users_h4vxh8lx_.dbf
channel ORA_DISK_1: reading from backup piece /u01/backup_keep/HAWK_3291536664_20200220_0mup0qtl_1_1_UPGRADE_22
channel ORA_DISK_1: piece handle=/u01/backup_keep/HAWK_3291536664_20200220_0mup0qtl_1_1_UPGRADE_22 tag=UPGRADE
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00003 to /u02/oradata/HAWK/datafile/o1_mf_sysaux_h4vxh9n4_.dbf
channel ORA_DISK_1: reading from backup piece /u01/backup_keep/HAWK_3291536664_20200220_0kup0qti_1_1_UPGRADE_20
channel ORA_DISK_1: piece handle=/u01/backup_keep/HAWK_3291536664_20200220_0kup0qti_1_1_UPGRADE_20 tag=UPGRADE
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /u02/oradata/HAWK/datafile/o1_mf_system_h4vxhroh_.dbf
channel ORA_DISK_1: reading from backup piece /u01/backup_keep/HAWK_3291536664_20200220_0jup0qti_1_1_UPGRADE_19
channel ORA_DISK_1: piece handle=/u01/backup_keep/HAWK_3291536664_20200220_0jup0qti_1_1_UPGRADE_19 tag=UPGRADE
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:25
Finished restore at 20-FEB-2020 13:55:18
Recover database:
RMAN> list incarnation of database;
List of Database Incarnations
DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1 1 HAWK 3291536664 PARENT 1 26-JAN-2017 13:52:29
2 2 HAWK 3291536664 PARENT 1408558 20-FEB-2020 03:30:34
3 3 HAWK 3291536664 CURRENT 1422706 20-FEB-2020 04:00:39
RMAN> recover database until scn 1528161;
Starting recover at 20-FEB-2020 13:55:57
using channel ORA_DISK_1
starting media recovery
archived log for thread 1 with sequence 4 is already on disk as file /u02/oradata/HAWK/archivelog/2020_02_20/o1_mf_1_4_h4wzoywg_.arc
archived log file name=/u02/oradata/HAWK/archivelog/2020_02_20/o1_mf_1_4_h4wzoywg_.arc thread=1 sequence=4
media recovery complete, elapsed time: 00:00:01
Finished recover at 20-FEB-2020 13:55:58
Open database:
RMAN> alter database open resetlogs;
Statement processed
Review databases:
RMAN> report schema;
Report of database schema for database with db_unique_name HAWK
List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 800 SYSTEM YES /u02/oradata/HAWK/datafile/o1_mf_system_h4vxhroh_.dbf
3 470 SYSAUX NO /u02/oradata/HAWK/datafile/o1_mf_sysaux_h4vxh9n4_.dbf
4 70 UNDOTBS1 YES /u02/oradata/HAWK/datafile/o1_mf_undotbs1_h4vxh5k4_.dbf
7 5 USERS NO /u02/oradata/HAWK/datafile/o1_mf_users_h4vxh8lx_.dbf
List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 20 TEMP 32767 /u02/oradata/HAWK/datafile/o1_mf_temp_h4vxlyl7_.tmp
RMAN> exit
Recovery Manager complete.
[oracle@db-fs-1 ~]$
Verify restore to confirm table created after backup does not exists:
13:57:34 SYS @ HAWK:HAWK:>desc t;
ERROR:
ORA-04043: object t does not exist
13:57:53 SYS @ HAWK:HAWK:>

In conclusion, RMAN KEEP backup is a good alternative to rollback changes when Guarantee Restore Point is not available.

 

email

Interested in working with Michael? Schedule a tech call.

No comments

Leave a Reply

Your email address will not be published. Required fields are marked *