Thinking Out Loud

November 21, 2020

Upgrade DB from 11.2 to 19.8 Using dbua silent

Filed under: 19c,dbua,upgrade — mdinh @ 1:58 am

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 ~]$

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Blog at WordPress.com.

%d bloggers like this: