Oracle auto upgrade 12c to 19c
Step 1. Install 12 c software and create a new database.
Step 2. Download the 19c database software and execute run installer and install software only.
Step 3. Create a env for both 12c and 19c homes.
Step 4. Soure 12 c env and enable archive log mode and flashback
To check archive log mode
SQL> archive log list
SQL> archive log list
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 1
Current log sequence 1
Enable archive log mode
SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 1543503872 bytes
Fixed Size 8621040 bytes
Variable Size 989856784 bytes
Database Buffers 536870912 bytes
Redo Buffers 8155136 bytes
Database mounted.
SQL> alter database archivelog;
Database altered.
Enable flashback
SQL> alter database flashback on;
Database altered.
SQL> alter database open;
Database altered.
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 1
Next log sequence to archive 1
Current log sequence 1
SQL>
Step 5; To set db_recovery_dest size as 50G
SQL> alter system set db_recovery_file_dest_size=50g scope=both;
System altered.
SQL> show parameter db_recovery_file_dest_size
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest_size big integer 50G
Purge recycle bin
SQL> purge recyclebin ;
Recyclebin purged.
Execute gather stats;
SQL> EXECUTE DBMS_STATS.GATHER_DICTIONARY_STATS;
PL/SQL procedure successfully completed.
SQL> select name from v$database;
NAME
---------
VBTTEST
SQL>
Step 6. Create a autoupgrade directory and file
[oracle@vignesh ~]$ cd /u01/
[oracle@vignesh u01]$
[oracle@vignesh u01]$
[oracle@vignesh u01]$ mkdir autoupgrade
[oracle@vignesh u01]$
[oracle@vignesh u01]$ cd autoupgrade/
[oracle@vignesh autoupgrade]$
[oracle@vignesh autoupgrade]$
Create a upgrade file and give correct db name and db homes
[oracle@vignesh autoupgrade]$ vi DB121C.cfg
[oracle@vignesh autoupgrade]$
[oracle@vignesh autoupgrade]$ cat DB121C.cfg
global.autoupg_log_dir=/u01/autoupgrade
upg1.dbname=vbttest
upg1.start_time=NOW
upg1.source_home=/u01/app/oracle/product/12.2.0/dbhome_1/
upg1.target_home=/u01/app/oracle/product/19.0.0/dbhome_1/
upg1.sid=vbttest
upg1.log_dir=/u01/autoupgrade
upg1.upgrade_node=localhost
upg1.target_version=19.3
upg1.run_utlrp=yes
upg1.timezone_upg=yes
[oracle@vignesh autoupgrade]$
Step 7: soure 19c env and execute prerequest checks
[oracle@vignesh ~]$ . vbttest19c.env
[oracle@vignesh ~]$
[oracle@vignesh ~]$
[oracle@vignesh ~]$ cd /u01/autoupgrade/
[oracle@vignesh autoupgrade]$
[oracle@vignesh autoupgrade]$
[oracle@vignesh autoupgrade]$ ls
DB121C.cfg
[oracle@vignesh autoupgrade]$ java -jar $ORACLE_HOME/rdbms/admin/autoupgrade.jar -config DB121C.cfg -mode analyze -noconsole
Autoupgrade tool launched with default options
+--------------------------------+
| Starting AutoUpgrade execution |
+--------------------------------+
1 databases will be analyzed
Job 100 for VBTTEST FINISHED
[oracle@vignesh autoupgrade]$
Step 8: check the preupgrade log is there any error found fix it then will do upgrade
[oracle@vignesh autoupgrade]$ cd 100/
[oracle@vignesh 100]$
[oracle@vignesh 100]$ ls -lrth
total 76K
-rw-rw-r--. 1 oracle oracle 0 Jan 20 11:05 autoupgrade_err.log
-rw-rw-r--. 1 oracle oracle 84 Jan 20 11:05 dbupgrade_20230120_user.log
drwxrwxr-x. 2 oracle oracle 4.0K Jan 20 11:05 prechecks
-rw-rw-r--. 1 oracle oracle 68K Jan 20 11:05 dbupgrade_20230120.log
[oracle@vignesh 100]$ cd prechecks
[oracle@vignesh prechecks]$ ls
prechecks_vbttest.log vbttest_checklist.cfg vbttest_checklist.json vbttest_checklist.xml vbttest_preupgrade.html vbttest_preupgrade.log
[oracle@vignesh prechecks]$ cat vbttest_preupgrade.log
[dbname] [VBTTEST]
==========================================
[container] [VBTTEST]
==========================================
[checkname] DICTIONARY_STATS
[stage] PRECHECKS
[fixup_available] YES
[runfix] YES
[severity] RECOMMEND
[rule] Dictionary statistics help the Oracle optimizer find efficient SQL execution plans and are essential for proper upgrade timing. Oracle recommends gathering dictionary statistics in the last 24 hours before database upgrade.<br><br>For information on managing optimizer statistics, refer to the 12.2.0.1 Oracle Database SQL Tuning Guide.
[broken rule] Dictionary statistics do not exist or are stale (not up-to-date).
[action] Gather stale data dictionary statistics prior to database upgrade in off-peak time using:<br><br> EXECUTE DBMS_STATS.GATHER_DICTIONARY_STATS;
----------------------------------------------------
Step 9: execute auto-upgrade file to upgrade database 12 c to 19 c
[oracle@vignesh autoupgrade]$ java -jar $ORACLE_HOME/rdbms/admin/autoupgrade.jar -config DB121C.cfg -mode deploy
Autoupgrade tool launched with default options
+--------------------------------+
| Starting AutoUpgrade execution |
+--------------------------------+
1 databases will be processed
Enter some command, type 'help' or 'exit' to quit
upg>
upg> lsj
+----+-------+---------+---------+-------+--------------+--------+--------+---------------+
|JOB#|DB NAME| STAGE|OPERATION| STATUS| START TIME|END TIME| UPDATED| MESSAGE|
+----+-------+---------+---------+-------+--------------+--------+--------+---------------+
| 101|VBTTEST|PRECHECKS|PREPARING|RUNNING|23/01/20 11:11| N/A|11:12:22|Remaining 67/72|
+----+-------+---------+---------+-------+--------------+--------+--------+---------------+
Total jobs 1
upg>
To restart job;
upg> /
+----+-------+---------+---------+------+--------------+--------+--------+--------+
|JOB#|DB NAME| STAGE|OPERATION|STATUS| START TIME|END TIME| UPDATED| MESSAGE|
+----+-------+---------+---------+------+--------------+--------+--------+--------+
| 101|VBTTEST|PREFIXUPS| STOPPED| ERROR|23/01/20 11:11| N/A|11:16:59|UPG-1316|
+----+-------+---------+---------+------+--------------+--------+--------+--------+
Total jobs 1
upg> resume -job 101
Resuming job: [101][VBTTEST]
upg>
upg>
upg>
upg> lsj
+----+-------+---------+---------+-------+--------------+--------+--------+-------+
|JOB#|DB NAME| STAGE|OPERATION| STATUS| START TIME|END TIME| UPDATED|MESSAGE|
+----+-------+---------+---------+-------+--------------+--------+--------+-------+
| 101|VBTTEST|PREFIXUPS|EXECUTING|RUNNING|23/01/20 11:11| N/A|11:23:34| |
+----+-------+---------+---------+-------+--------------+--------+--------+-------+
Total jobs 1
Upgrade started
Step 10: validate database home and compatible
SQL> select version from v$instance;
VERSION
-----------------
19.0.0.0.0
Drop restore point
Change 19c compatible:
