There was a debate as to whether the parameter -useGRP UPGRADE19C for dbua is necessary where UPGRADE19C is the name for the restore point created prior to upgrading the database.
Although it’s not necessary, it is beneficial for dbua to automate the restore process.
When -useGRP UPGRADE19C is used, restore.sh is created to restore the database using guarantee restore point specified.
If -useGRP is not used, then dbua will not create restore.sh script. While I have not personally tested this, I did check for restore.sh script for a recent upgrade and did not find one.
Why not use dbua to its full potential?
DEMO:
### 11.2 database:
[oracle@ol7-112-dg1 ~]$ $ORACLE_HOME/OPatch/opatch lspatches
There are no Interim patches installed in this Oracle Home.
[oracle@ol7-112-dg1 ~]$ $ORACLE_HOME/OPatch/opatch lsinventory|grep Database
Oracle Database 11g 11.2.0.4.0
[oracle@ol7-112-dg1 ~]$
### 19c database:
[oracle@ol7-112-dg1 ~]$ $ORACLE_HOME/OPatch/opatch lspatches
31305087;OCW RELEASE UPDATE 19.8.0.0.0 (31305087)
31281355;Database Release Update : 19.8.0.0.200714 (31281355)
OPatch succeeded.
[oracle@ol7-112-dg1 ~]$
### Copy emremove.sql from 19c to 11.2 DB home:
cp -fv /u01/app/oracle/product/19.3.0.0/db_1/rdbms/admin/emremove.sql /u01/app/oracle/product/11.2.0.4/dbhome_1/rdbms/admin
### Remove EM and OLAP:
set echo on serveroutput on
@?/rdbms/admin/emremove.sql
@?/olap/admin/catnoamd.sql
@?/rdbms/admin/utlrp.sql
### Create guarantee restore point UPGRADE19C:
[oracle@ol7-112-dg1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Sat Nov 21 00:23:56 2020
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> @/sf_working/sql/restore_point_upgrade19c.sql
SQL> drop restore point UPGRADE19C;
drop restore point UPGRADE19C
*
ERROR at line 1:
ORA-38780: Restore point 'UPGRADE19C' does not exist.
SQL> alter system set db_recovery_file_dest_size=1m scope=both sid='*';
System altered.
SQL> alter system set db_recovery_file_dest_size=9000m scope=both sid='*';
System altered.
SQL> select sum(flashback_size)/1024/1024/1024 gb from v$flashback_database_log;
GB
"----------"
SQL> select flashback_on from v$database;
FLASHBACK_ON
"----------"
NO
SQL> create restore point UPGRADE19C guarantee flashback database;
Restore point created.
SQL> select flashback_on from v$database;
FLASHBACK_ON
"------------------"
RESTORE POINT ONLY
SQL> select name, time, guarantee_flashback_database from v$restore_point order by 1,2;
NAME TIME GUA
"------------------------------ ---------------------------------------- ---"
UPGRADE19C 21-NOV-20 12.24.19.000000000 AM YES
SQL> select sum(flashback_size)/1024/1024/1024 gb from v$flashback_database_log;
GB
.048828125
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@ol7-112-dg1 ~]$
### Upgrade DB using dbua silent: -useGRP UPGRADE19C
[oracle@ol7-112-dg1 ~]$ echo $ORACLE_SID $ORACLE_HOME
testdb /u01/app/oracle/product/11.2.0.4/dbhome_1
[oracle@ol7-112-dg1 ~]$ ./run_dbua.sh
/u01/app/oracle/product/19.3.0.0/db_1/bin/dbua -silent \
-sid testdb \
-oracleHome /u01/app/oracle/product/11.2.0.4/dbhome_1 \
-useGRP UPGRADE19C \
-recompile_invalid_objects TRUE \
-upgradeTimezone TRUE \
-emConfiguration NONE \
-skipListenersMigration \
-createListener FALSE \
-upgrade_parallelism 8
Logs directory: /u01/app/oracle/cfgtoollogs/dbua/upgrade2020-11-21_12-27-10AM
Performing Pre-Upgrade Checks…
PRE- and POST- FIXUP ACTIONS
/u01/app/oracle/cfgtoollogs/dbua/upgrade2020-11-21_12-27-10AM/testdb/upgrade.xml
/u01/app/oracle/cfgtoollogs/dbua/upgrade2020-11-21_12-27-10AM/testdb/preupgrade_fixups.sql
/u01/app/oracle/cfgtoollogs/dbua/upgrade2020-11-21_12-27-10AM/testdb/postupgrade_fixups.sql
[WARNING] [DBT-20060] One or more of the pre-upgrade checks on the database have resulted into warning conditions that require manual intervention. It is recommended that you address these warnings as suggested before proceeding.
ACTION: Refer to the pre-upgrade results location for details: /u01/app/oracle/cfgtoollogs/dbua/upgrade2020-11-21_12-27-10AM/testdb
12% complete
15% complete
25% complete
77% complete
87% complete
Database upgrade has been completed successfully, and the database is ready to use.
100% complete
[oracle@ol7-112-dg1 ~]$
### DBUA Logs:
[oracle@ol7-112-dg1 ~]$ ls -l /u01/app/oracle/cfgtoollogs/dbua/upgrade2020-11-21_12-27-10AM/testdb/
total 76340
-rw-r-----. 1 oracle oinstall 0 Nov 21 00:27 Backup.log
-rw-r-----. 1 oracle oinstall 48860899 Nov 21 01:03 catupgrd0.log
-rw-r-----. 1 oracle oinstall 6740107 Nov 21 01:03 catupgrd1.log
-rw-r-----. 1 oracle oinstall 3759694 Nov 21 01:03 catupgrd2.log
-rw-r-----. 1 oracle oinstall 5391694 Nov 21 01:03 catupgrd3.log
-rw-r-----. 1 oracle oinstall 2974948 Nov 21 01:03 catupgrd4.log
-rw-r-----. 1 oracle oinstall 2127696 Nov 21 01:03 catupgrd5.log
-rw-r-----. 1 oracle oinstall 3975631 Nov 21 01:03 catupgrd6.log
-rw-r-----. 1 oracle oinstall 3411705 Nov 21 01:03 catupgrd7.log
-rw-------. 1 oracle oinstall 528 Nov 21 00:28 catupgrd_catcon_7841.lst
-rw-r-----. 1 oracle oinstall 0 Nov 21 00:54 catupgrd_datapatch_upgrade.err
-rw-r-----. 1 oracle oinstall 1306 Nov 21 01:01 catupgrd_datapatch_upgrade.log
-rw-r-----. 1 oracle oinstall 38676 Nov 21 01:03 catupgrd_stderr.log
-rw-r-----. 1 oracle oinstall 1 Nov 21 00:27 checksBuffer.tmp
-rw-r-----. 1 oracle oinstall 41134 Nov 21 00:27 components.properties
-rwxr-xr-x. 1 oracle oinstall 320 Nov 21 00:27 createSPFile_testdb.sql
-rw-r-----. 1 oracle oinstall 15085 Nov 21 00:27 dbms_registry_extended.sql
-rwxr-xr-x. 1 oracle oinstall 120 Nov 21 00:27 grpOpen_testdb.sql
-rw-r-----. 1 oracle oinstall 942 Nov 21 00:27 init.ora
-rw-r-----. 1 oracle oinstall 69 Nov 21 00:28 Migrate_Sid.log
drwxr-x---. 3 oracle oinstall 21 Nov 21 00:27 oracle
-rw-r-----. 1 oracle oinstall 10409 Nov 21 01:04 Oracle_Server.log
-rw-r-----. 1 oracle oinstall 14051 Nov 21 00:27 parameters.properties
-rw-r-----. 1 oracle oinstall 8580 Nov 21 00:27 postupgrade_fixups.sql
-rw-r-----. 1 oracle oinstall 301 Nov 21 01:10 PostUpgrade.log
-rw-r-----. 1 oracle oinstall 7884 Nov 21 00:27 preupgrade_driver.sql
-rw-r-----. 1 oracle oinstall 8514 Nov 21 00:27 preupgrade_fixups.sql
-rw-r-----. 1 oracle oinstall 443 Nov 21 00:28 PreUpgrade.log
-rw-r-----. 1 oracle oinstall 99316 Nov 21 00:27 preupgrade_messages.properties
-rw-r-----. 1 oracle oinstall 457732 Nov 21 00:27 preupgrade_package.sql
-rw-r-----. 1 oracle oinstall 1464 Nov 21 00:27 PreUpgradeResults.html
-rwxr-xr-x. 1 oracle oinstall 42 Nov 21 00:27 shutdown_testdb.sql
-rw-r-----. 1 oracle oinstall 94342 Nov 21 01:10 sqls.log
-rwxr-xr-x. 1 oracle oinstall 35 Nov 21 00:27 startup_testdb.sql
-rwxr-xr-x. 1 oracle oinstall 2070 Nov 21 00:27 testdb_restore.sh
drwxr-x---. 3 oracle oinstall 24 Nov 21 00:27 upgrade
-rw-r-----. 1 oracle oinstall 5287 Nov 21 01:10 UpgradeResults.html
-rw-r-----. 1 oracle oinstall 2920 Nov 21 01:09 UpgradeTimezone.log
-rw-r-----. 1 oracle oinstall 11264 Nov 21 00:27 upgrade.xml
-rw-r-----. 1 oracle oinstall 1583 Nov 21 01:04 upg_summary_CDB_Root.log
-rw-r-----. 1 oracle oinstall 115 Nov 21 01:07 Utlprp.log
[oracle@ol7-112-dg1 ~]$
### Script testdb_restore.sh:
[oracle@ol7-112-dg1 sql]$ cat /u01/app/oracle/cfgtoollogs/dbua/upgrade2020-11-21_12-27-10AM/testdb/testdb_restore.sh
!/bin/sh
-- Run this Script to Restore Oracle Database Instance testdb
echo -- Bringing up the database from the source oracle home
ORACLE_HOME=/u01/app/oracle/product/11.2.0.4/dbhome_1; export ORACLE_HOME
LD_LIBRARY_PATH=/u01/app/oracle/product/11.2.0.4/dbhome_1/lib:$LD_LIBRARY_PATH; export LD_LIBRARY_PATH
ORACLE_SID=testdb; export ORACLE_SID
/u01/app/oracle/product/11.2.0.4/dbhome_1/bin/sqlplus /nolog @/u01/app/oracle/cfgtoollogs/dbua/upgrade2020-11-21_12-27-10AM/testdb/shutdown_testdb.sql
echo -- Bringing down the database from the new oracle home
ORACLE_HOME=/u01/app/oracle/product/19.3.0.0/db_1; export ORACLE_HOME
LD_LIBRARY_PATH=/u01/app/oracle/product/19.3.0.0/db_1/lib:$LD_LIBRARY_PATH; export LD_LIBRARY_PATH
ORACLE_SID=testdb; export ORACLE_SID
/u01/app/oracle/product/19.3.0.0/db_1/bin/sqlplus /nolog @/u01/app/oracle/cfgtoollogs/dbua/upgrade2020-11-21_12-27-10AM/testdb/shutdown_testdb.sql
echo -- Removing database instance from new oracle home …
echo You should Remove this entry from the /etc/oratab: testdb:/u01/app/oracle/product/19.3.0.0/db_1:N
echo -- Bringing up the database from the source oracle home
unset LD_LIBRARY_PATH; unset LD_LIBRARY_PATH_64; unset SHLIB_PATH; unset LIB_PATH
LD_LIBRARY_PATH=/u01/app/oracle/product/11.2.0.4/dbhome_1/lib:$LD_LIBRARY_PATH; export LD_LIBRARY_PATH
ORACLE_HOME=/u01/app/oracle/product/11.2.0.4/dbhome_1; export ORACLE_HOME
ORACLE_SID=testdb; export ORACLE_SID
rm /u01/app/oracle/product/19.3.0.0/db_1/dbs/spfiletestdb.ora
echo You should Add this entry in the /etc/oratab: testdb:/u01/app/oracle/product/11.2.0.4/dbhome_1:Y
cd /u01/app/oracle/product/11.2.0.4/dbhome_1
/u01/app/oracle/product/11.2.0.4/dbhome_1/bin/sqlplus /nolog @/u01/app/oracle/cfgtoollogs/dbua/upgrade2020-11-21_12-27-10AM/testdb/createSPFile_testdb.sql
/u01/app/oracle/product/11.2.0.4/dbhome_1/bin/sqlplus /nolog @/u01/app/oracle/cfgtoollogs/dbua/upgrade2020-11-21_12-27-10AM/testdb/grpOpen_testdb.sql
RESTORE_RESULT=$?
echo -- Execution of restore script for the database TESTDB completed.
exit $(($RESTORE_RESULT|$?))
[oracle@ol7-112-dg1 sql]$
### grpOpen_testdb.sql: flashback database to restore point UPGRADE19C;
[oracle@ol7-112-dg1 ~]$ grep -i upgrade19c /u01/app/oracle/cfgtoollogs/dbua/upgrade2020-11-21_12-27-10AM/testdb/*.sql
/u01/app/oracle/cfgtoollogs/dbua/upgrade2020-11-21_12-27-10AM/testdb/grpOpen_testdb.sql:flashback database to restore point UPGRADE19C;
[oracle@ol7-112-dg1 ~]$
### Restore database back to 11.2:
[oracle@ol7-112-dg1 ~]$ /u01/app/oracle/cfgtoollogs/dbua/upgrade2020-11-21_12-27-10AM/testdb/testdb_restore.sh
-- Bringing up the database from the source oracle home
SQL*Plus: Release 11.2.0.4.0 Production on Sat Nov 21 04:28:33 2020
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to an idle instance.
ORACLE instance shut down.
Disconnected
-- Bringing down the database from the new oracle home
SQL*Plus: Release 19.0.0.0.0 - Production on Sat Nov 21 04:28:33 2020
Version 19.8.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected.
ORACLE instance shut down.
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.8.0.0.0
-- Removing database instance from new oracle home …
You should Remove this entry from the /etc/oratab: testdb:/u01/app/oracle/product/19.3.0.0/db_1:N
-- Bringing up the database from the source oracle home
You should Add this entry in the /etc/oratab: testdb:/u01/app/oracle/product/11.2.0.4/dbhome_1:Y
SQL*Plus: Release 11.2.0.4.0 Production on Sat Nov 21 04:28:38 2020
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to an idle instance.
ORACLE instance started.
Total System Global Area 1603411968 bytes
Fixed Size 2253664 bytes
Variable Size 520096928 bytes
Database Buffers 1073741824 bytes
Redo Buffers 7319552 bytes
File created.
ORA-01507: database not mounted
ORACLE instance shut down.
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL*Plus: Release 11.2.0.4.0 Production on Sat Nov 21 04:28:46 2020
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to an idle instance.
ORACLE instance started.
Total System Global Area 1603411968 bytes
Fixed Size 2253664 bytes
Variable Size 520096928 bytes
Database Buffers 1073741824 bytes
Redo Buffers 7319552 bytes
Database mounted.
Flashback complete.
Database altered.
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
-- Execution of restore script for the database TESTDB completed.
[oracle@ol7-112-dg1 ~]$
Thinking Out Loud 