« PREVIOUS « |
MAIN INDEX 19C HOL | » FB: AFTER UPGRADE » |
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
NOARCHIVELOG
mode, 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 thenSHUTDOWN
the database:
.. ftex
sqlplus / as sysdba
startup
alter tablespace USERS read only;
insert into SYSTEM.TRACKING_TAB values (1,'partial offline backup'
);
commit;
shutdown immediate
exit - Then copy the “heart of the database” to a backup location
Execute the “backupFTEX.sh
” script:
.. /home/oracle/scripts/backupFTEX.sh
## ## 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/*.log /home/oracle/FTEX/bck cp /u02/oradata/FTEX/*.ctl /home/oracle/FTEX/bck cp /u02/oradata/FTEX/sys*.dbf /home/oracle/FTEX/bck cp /u02/oradata/FTEX/temp*.dbf /home/oracle/FTEX/bck cp /u02/oradata/FTEX/undo*.dbf /home/oracle/FTEX/bck
- Startup the database in 19c and upgrade it
.cd $ORACLE_HOME/dbs
. ftex19
cp spfileFTEX.ora $ORACLE_HOME/dbs
cp orapwFTEX $ORACLE_HOME/dbs
sqlplus / as sysdba
startup upgrade
exitdbupgrade -l /home/oracle/logs
- After 2-3 minutes,
CTRL-C
the upgrade and try the fallback
.
Once the upgrade is running, wait until the first phases have been completed, then hit:CTRL+C
... ************** 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:
dbupgrade -R -l /home/oracle/logs
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:
CTRL+C
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.
- Then
SHUTDOWN
the database and RESTORE it.
Check the content of theTRACKING_TAB
:sqlplus / as sysdba
shutdown immediate
exit. /home/oracle/scripts/restoreFTEX.sh
. ftex
sqlplus / as sysdba
startup
select * from TRACKING_TAB;
exit
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
ARCHIVELOG
mode - Enterprise Edition database (or XE or PE)
- Don’t change
COMPATIBLE
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
ARCHIVELOG
mode:
.. ftex
sqlplus / as sysdba
shutdown immediate;
startup mount
alter database archivelog;
alter database open;
archive log list
- Set a Guaranteed Restore Point
.insert into SYSTEM.TRACKING_TAB values (2,
'guaranteed restore point'
);
commit;
create restore point GRP1 guarantee flashback database;
shutdown immediate
exit - Switch to the 19c environment and upgrade the FTEX database:
.. ftex19
sqlplus / as sysdba
startup upgrade
exit
dbupgrade -l /home/oracle/logs -n 2
This will now take 15-30 minutes depending on your hardware equipment.
If you don’t want to wait so long, you can
CTRL-C
the upgrade at any point, andFLASHBACK
to the restore point without taking the second one. u .sqlplus / as sysdba
@?/rdbms/admin/utlrp.sql
@/u01/app/oracle/cfgtoollogs/FTEX/preupgrade/postupgrade_fixups.sql
insert into SYSTEM.TRACKING_TAB values (3,'upgrade completed'
);
commit;
select * from TRACKING_TAB;
- Set a new GRP and then flashback to the GRP to before-upgrade. The
FLASHBACK DATABASE
happens in the new environment, theOPEN
of the database (in this caseREAD ONLY
– alternative would beOPEN RESETLOGS
) in the source environment:
.create restore point GRP2 guarantee flashback database;
shutdown immediate
startup mount
flashback database to restore point GRP1;
shutdown immediate
exit
. ftex
sqlplus / as sysdba
startup open read only;
select * from TRACKING_TAB;
Do you recognize that the database has been flashed back to “before upgrade” in less than a minute? You could open it
RESETLOGS
and repeat the upgrade. - But
FLASHBACK DATABASE
works in all directions, backwards and forward (even though forward may take a bit longer now):
.shutdown immediate
exit
. ftex19
sqlplus / as sysdba
startup mount
flashback database to restore point GRP2;
alter database open resetlogs;
select * from TRACKING_TAB;
Even though you opened the database with
OPEN RESETLOGS
you can repeat theFLASHBACK DATABASE
operations as often as you’d like. - Take also note of the components which exist now in the database:
.select COMP_ID, STATUS from DBA_REGISTRY order by COMP_ID;
- 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
ARCHIVELOG
mode now as we won’t need it for the next exercises:
.drop restore point GRP1;
drop restore point GRP2;
shutdown immediate
startup mount
alter database noarchivelog;
alter database open;
archive log list
exit
You successfully completed the second part of the Fallback Strategies lab.
Finally you may please watch the Youtube video as well:
« PREVIOUS « |
MAIN INDEX 19C HOL | » FB: AFTER UPGRADE » |