Use RMAN KEEP Backup To Roll Back From an Upgrade

10 min read

Feb 25, 2020

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:

[code] [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 ~]$ [/code]

Rename backup directory to prevent accidental deletion from RMAN:

[code] [oracle@db-fs-1 ~]$ mv /u01/backup/ /u01/backup_keep [/code]

Review RMAN KEEP backup:

[code] [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 ~]$ [/code]

Delete RMAN KEEP backup to demonstrate backup is still safe:

[code] [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 ~]$ [/code]

Create table after backup as a marker and table should not exist after restore from RMAN KEEP backup:

[code] 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) [/code] Shutdown database: [code] 13:50:59 SYS @ HAWK:HAWK:>shu abort; ORACLE instance shut down. 13:51:09 SYS @ HAWK:HAWK:>exit [/code]

Perform RESTORE from RMAN KEEP backup:

[code] [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) [/code]

Startup database:

[code] 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 [/code]

Restore controlfile:

[code] 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 [/code]

Mount database:

[code] RMAN> alter database mount; Statement processed released channel: ORA_DISK_1 [/code]

Catalog backup:

[code] 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 [/code]

Preview restore to determine restore point:

[code] 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 [/code]

Restore database:

[code] 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 [/code]

Recover database:

[code] 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 [/code]

Open database:

[code] RMAN> alter database open resetlogs; Statement processed [/code]

Review databases:

[code] 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 ~]$ [/code]

Verify restore to confirm table created after backup does not exists:

[code] 13:57:34 SYS @ HAWK:HAWK:>desc t; ERROR: ORA-04043: object t does not exist 13:57:53 SYS @ HAWK:HAWK:> [/code] In conclusion, RMAN KEEP backup is a good alternative to rollback changes when Guarantee Restore Point is not available.

← PART 4: Implementing Oracle Database Single Sign-on Using Kerberos, Active Directory, and Oracle CMU

How to Quickly Ignore ORA Error on Agent Layer →

Get Email Notifications

No Comments Yet

Let us know what you think