Protection for issues during upgrade
In this part you’ll use two techniques to protect your database for issues happening during the upgrade. Or simply, if you’d like to test multiple times.
You will evaluate two options: Partial Offline Backups and Guaranteed Restore Points.
1. Partial Offline Backup
A partial offline backup is used for protection against failures during the upgrade or for testing purposes to avoid the restoration of an entire database environment. You can change the COMPATIBLE parameter if you want with this technique. But don’t do it in the lab now as you’ll use two techniques in parallel.
- Very large databases where restoring just a small piece of the database is faster than an entire restore
- Databases who are – on purpose – in
NOARCHIVELOGmode, hence you can’t do an online backup and restore
- Standard Edition databases where you can’t use Guaranteed Restore Points
For a Partial Offline Backup as fallback strategy, you’ll have to put all your user and data tablespaces into read-only mode, then create an offline backup of the “heart” of your database.
You’ll do the following exercises with the FTEX database in the Hands-On Lab.
- At first, set the USERS tablespace read-only and then
- Then copy the “heart of the database” to a backup location
Execute the “
## ## This is for INFORMATION only ## Don't execute - this is all done by the copyFTEX.sh above ## -- ## The script copies redologs, controlfiles and all files for UNDO, TEMP, SYSTEM and SYSAUX ## to: /home/oracle/fast_recovery_area/FTEX/bck cp /u02/oradata/FTEX/*.ctl /home/oracle/fast_recovery_area/FTEX/bck cp /u02/oradata/FTEX/*.log /home/oracle/fast_recovery_area/FTEX/bck cp /u02/oradata/FTEX/sys*.dbf /home/oracle/fast_recovery_area/FTEX/bck cp /u02/oradata/FTEX/temp*.dbf /home/oracle/fast_recovery_area/FTEX/bck cp /u02/oradata/FTEX/undo*.dbf /home/oracle/fast_recovery_area/FTEX/bck
- Startup the database in 18c and upgrade it
- After 5 minutes, CTRL-C the upgrade and try the fallback
Once the upgrade is running, wait until the first phases have been completed, then hit:
... ************** Catproc Procedures ************** Parallel Phase #:13 [FTEX] Files:94 Time: 12s Restart Phase #:14 [FTEX] Files:1 Time: 0s Parallel Phase #:15 [FTEX] Files:117 Time: 20s Restart Phase #:16 [FTEX] Files:1 Time: 1s Serial Phase #:17 [FTEX] Files:17 Time: 3s Restart Phase #:18 [FTEX] Files:1 Time: 0s ***************** Catproc Views **************** Parallel Phase #:19 [FTEX] Files:32 ^Ccatcon::catcon_HandleSigINT: Signal INT was received.
The upgrade failed.
- Now first of all, try out the RESUME of the upgrade driver:
The upgrade should start from where it had been stopped.
*******Upgrade being restarted on database FTEX from failed phase 19******* ------------------------------------------------------ Phases [19-108] Start Time:[2018_08_03 15:24:17] ------------------------------------------------------ Time: 2s ***************** Catproc Views **************** Parallel Phase #:19 [FTEX] Files:32 Time: 31s Restart Phase #:20 [FTEX] Files:1 Time: 0s Serial Phase #:21 [FTEX] Files:3 Time: 19s Restart Phase #:22 [FTEX] Files:1 Time: 0s
- Let the upgrade fail a second time:
Restart Phase #:20 [FTEX] Files:1 Time: 0s Serial Phase #:21 [FTEX] Files:3 Time: 19s Restart Phase #:22 [FTEX] Files:1 Time: 0s Parallel Phase #:23 [FTEX] Files:24 Time: 283s Restart Phase #:24 [FTEX] Files:1 Time: 1s Parallel Phase #:25 [FTEX] Files:12 ^Ccatcon::catcon_HandleSigINT: Signal INT was received.
SHUTDOWNthe database and RESTORE it.
Check the content of the
Now you completed the first exercise.
You can watch the video as well:
2. Flashback to a Guaranteed Restore Point
By far the best and most simple technique to protect your databases are Guaranteed Restore Points. But it can only used when the following requirements are all met:
- Database must be in
- Enterprise Edition database (or XE or PE)
- Don’t change
This is the overview on how to fallback with a guaranteed restore point GRP1 which allows you to flashback your database – many times.
- Turn on
- Set a Guaranteed Restore Point
- Switch to the 18c environment and upgrade the FTEX database:
This will now take 15-30 minutes depending on your hardware equipment..
- Set a new GRP and then flashback to the GRP to before-upgrade. The
FLASHBACK DATABASEhappens in the new environment, the
OPENof the database (in this case
READ ONLY– alternative would be
OPEN RESETLOGS) in the source environment:
Do you recognize that the database has been flashed back to “before upgrade” in less than a minute? You could open it
RESETLOGSand repeat the upgrade.
FLASHBACK DATABASEworks in all directions, backwards and forward (even though forward may take a bit longer now):
Even though you opened the database with
OPEN RESETLOGSyou can repeat the
FLASHBACK DATABASEoperations as often as you’d like.
- Take also note of the components which exist now in the database:
- Clean up and drop the restore points as otherwise at some point you’ll run out of archive space. In addition you’ll turn off
ARCHIVELOGmode now as we won’t need it for the next exercises:
You successfully completed the second part of the Fallback Strategies lab.
Finally you may please watch the Youtube video as well:
- ==> NEXT: Protection for issues after upgrade