Single instance database upgrade from 11.2.0.3 to 12.1.0.1.0.
There’s plenty of install posts already out there; hence, I am not going to blog about it.
If you find a post on DBUA Silent mode, then please let me know.
Oracle Database 12c Release 1 (12.1) DBUA in Silent mode [ID 1516616.1]
First attempt failed with errors:
ORA-00823: Specified value of sga_target greater than sga_max_size
ORA-01078: failure in processing system parameters
ORA-00823 – Specified value of sga_target greater than sga_max_size Error During Upgrade Using DBUA [ID 1439671.1]
As an alternative solution SGA_MAX_SIZE can be removed as it is not mandatory while upgrade
Is Oracle lying to me? Let’s look at spfile before and after.
$ tail spfiledb01.ora.orig
*.processes=50
*.remote_logCC"-in_passwordfile='EXCLUSIVE'
*.sec_case_sensitive_logon=FALSE
*.sga_max_size=512M
*.sga_target=512M
*.timed_statistics=TRUE
*.undo_management='AUTO'
*.undo_tablespace='undotbs'
*.workarea_size_policy='AUTO'
$ tail spfiledb01.ora
*.pga_aggregate_target=128M
*.processes=50
*.remoCC"n&te_login_passwordfile='EXCLUSIVE'
*.sec_case_sensitive_logon=FALSE
*.sga_target=512M
*.timed_statistics=TRUE
*.undo_management='AUTO'
*.undo_tablespace='undotbs'
*.workarea_size_policy='AUTO'
[oracle@lax:db01]/home/oracle
$ /u01/app/oracle/product/12.1.0/dbhome_1/bin/dbua -silent \
> -sid db01 \
> -oracleHome /u01/app/oracle/product/11.2.0.3/db_1 \
> -diagnosticDest /u01/app/oracle \
> -autoextendFiles \
> -recompile_invalid_objects true \
> -degree_of_parallelism 2 \
> -upgradeTimezone \
> -emConfiguration NONE \
> -keepHiddenParams \
> -gatheringStatistics \
> -createGRP true \
> -upgrade_parallelism 2
Log files for the upgrade operation are located at: /u01/app/oracle/cfgtoollogs/dbua/lax_db01/upgrade1
Performing Database Backup
16% complete
Performing Pre Upgrade
20% complete
33% complete
Performing RDBMS Upgrade
34% complete
49% complete
50% complete
Performing Post Upgrade
51% complete
53% complete
66% complete
Upgrading Timezone
83% complete
Generating Summary
Database upgrade has been completed successfully, and the database is ready to use.
100% complete
Check the log file "/u01/app/oracle/cfgtoollogs/dbua/logs/silent.log_1372773594442" for upgrade details.
DBUA performed the following actions:
Pre-Upgrade Checks
Database contains INVALID objects prior to upgrade.
The list of invalid SYS/SYSTEM objects was written to registry$sys_inv_objs.
The list of non-SYS/SYSTEM objects was written to registry$nonsys_inv_objs unless there were over 5000.
Use utluiobj.sql after the upgrade to identify any new invalid objects due to the upgrade.
It is required that the recycle bin is empty prior to upgrading your database.
Run "PURGE DBA_RECYCLEBIN" command before continuing with your upgrade.
Initialization Parameter changes
The following changes will be made in the initialization parameters:
sga_target 536870912|624951296 (old|new)
processes 50|300
Obsolete Parameters to be removed
sec_case_sensitive_logon
Timezone Upgrade
The time zone version and TIMESTAMP WITH TIME ZONE data of the Database will be upgraded from version 14 to version 18.
Recovery options
Create a new guaranteed restore point before upgrade and flashback the database in case of any upgrade failure.
Upgrade Details
The following summary lists the steps performed during the upgrade process.
Log files for all the steps, as well as this summary, are available at “/u01/app/oracle/cfgtoollogs/dbua/lax_db01/upgrade1”.
Database Backup -Backup.log
Pre Upgrade -PreUpgrade.log (PreUpgradeResults.html is a lot better)
RDBMS Upgrade -Oracle_Server.log
Post Upgrade -PostUpgrade.log
Timezone Upgrade -UpgradeTimezone.log
Generate Summary -generateSummary.log (does not exist)
Let’s review the logs.
$ORACLE_BASE=/u01/app/oracle
[oracle@lax:db01]/u01/app/oracle/cfgtoollogs/dbua/logs
$ ls -l
total 64
-rw-r----- 1 oracle oinstall 3592 Jul 2 10:48 silent.log_1372773594442
-rw-r----- 1 oracle oinstall 17721 Jul 2 07:02 sqls.log
-rw-r----- 1 oracle oinstall 38554 Jul 2 07:02 trace.log
[oracle@lax:db01]/u01/app/oracle/cfgtoollogs/dbua/logs
$ head -20 silent.log_1372773594442
Upgrading the database dbName="db01"
Database contains INVALID objects prior to upgrade. The list of invalid SYS/SYSTEM objects was written to registry$sys_inv_objs. The list of non-SYS/SYSTEM objects was written to registry$nonsys_inv_objs unless there were over 5000. Use utluiobj.sql after the upgrade to identify any new invalid objects due to the upgrade.
Your recycle bin contains objects. It is required that the recycle bin is empty prior to upgrading your database. Run "PURGE DBA_RECYCLEBIN" command before continuing with your upgrade.
Supported upgrade version check succeeded.
The oratab permission check succeeded.
Oracle Home Owner Check succeeded.
Database does not use ASM storage.
Space usage summary
Space usage reason: Upgrade
Tablespace Name: SYSTEM
Additional space required: 159 MB
Datafile: /oracle/oradata/db02/system_1.dbf
Auto Extensible: Yes
Tablespace Name: SYSAUX
Additional space required: 244 MB
Datafile: /oracle/oradata/db02/sysaux_1.dbf
Auto Extensible: Yes
[oracle@lax:db01]/u01/app/oracle/cfgtoollogs/dbua/logs
$ tail -20 silent.log_1372773594442
UPGRADE_PROGRESS : 48%
UPGRADE_PROGRESS : 48%
UPGRADE_PROGRESS : 48%
UPGRADE_PROGRESS : 49%
UPGRADE_PROGRESS : 49%
UPGRADE_PROGRESS : 49%
UPGRADE_PROGRESS : 50%
Performing Post Upgrade
UPGRADE_PROGRESS : 51%
UPGRADE_PROGRESS : 53%
UPGRADE_PROGRESS : 66%
Upgrading Timezone
UPGRADE_PROGRESS : 83%
Generating Summary
UPGRADE_PROGRESS : 100%
Database upgrade has been completed successfully, and the database is ready to use.
The following document describes important behavioral changes from previous database releases:
/u01/app/oracle/product/12.1.0/dbhome_1/assistants/dbua/doc/DefaultBehaviorChanges_<NLS_LANG>.html
[oracle@lax:db01]/u01/app/oracle/cfgtoollogs/dbua/lax_db01/upgrade1
$ ls -alrt
total 24208
-rw-r----- 1 oracle oinstall 0 Jul 2 07:02 trace.log.lck
drwxr-x--- 3 oracle oinstall 4096 Jul 2 07:02 ..
-rw-r----- 1 oracle oinstall 2595 Jul 2 07:04 upgrade.xml1
-rw-r----- 1 oracle oinstall 2256 Jul 2 07:07 upgrade.xml
-rw-r----- 1 oracle oinstall 4504 Jul 2 07:07 PreUpgradeResults.html
-rw-r----- 1 oracle oinstall 0 Jul 2 07:07 Backup.log
-rw-r----- 1 oracle oinstall 1168 Jul 2 07:07 init.ora
-rwxr-xr-x 1 oracle oinstall 173 Jul 2 07:07 createSPFile_db01.sql
-rwxr-xr-x 1 oracle oinstall 127 Jul 2 07:07 grpOpen_db01.sql
-rwxr-xr-x 1 oracle oinstall 1257 Jul 2 07:07 db01_restore.sh
-rwxr-xr-x 1 oracle oinstall 42 Jul 2 07:07 shutdown_db01.sql
-rwxr-xr-x 1 oracle oinstall 35 Jul 2 07:07 startup_db01.sql
-rw-r----- 1 oracle oinstall 486 Jul 2 07:10 PreUpgrade.log
-rw-r----- 1 oracle oinstall 8407445 Jul 2 10:16 catupgrd1.log
-rw-r----- 1 oracle oinstall 15815542 Jul 2 10:16 catupgrd0.log
-rw-r----- 1 oracle oinstall 152750 Jul 2 10:20 Oracle_Server.log
-rw-r----- 1 oracle oinstall 9703 Jul 2 10:34 PostUpgrade.log
-rw-r----- 1 oracle oinstall 364 Jul 2 10:48 UpgradeTimezone.log
-rw-r----- 1 oracle oinstall 6323 Jul 2 10:48 UpgradeResults.html
-rw-r----- 1 oracle oinstall 212540 Jul 2 10:48 trace.log
-rw-r----- 1 oracle oinstall 101483 Jul 2 10:48 sqls.log
drwxr-x--- 2 oracle oinstall 4096 Jul 2 14:38 .
-createGRP true (NF – create a guaranteed restore point when database is in archive log and flashback mode)
[oracle@lax:db01]/u01/app/oracle/cfgtoollogs/dbua/lax_db01/upgrade1
$ cat db01_restore.sh
#!/bin/sh
# -- Run this Script to Restore Oracle Database Instance db01
echo -- Bringing down the database from the new oracle home
ORACLE_HOME=/u01/app/oracle/product/12.1.0/dbhome_1; export ORACLE_HOME
ORACLE_SID=db01; export ORACLE_SID
/u01/app/oracle/product/12.1.0/dbhome_1/bin/sqlplus /nolog @/u01/app/oracle/cfgtoollogs/dbua/lax_db01/upgrade1/shutdown_db01.sql
echo You should Remove this entry from the /etc/oratab: db01:/u01/app/oracle/product/12.1.0/dbhome_1:Y
echo -- Bringing up the database from the source oracle home
ORACLE_HOME=/u01/app/oracle/product/11.2.0.3/db_1; export ORACLE_HOME
ORACLE_SID=db01; export ORACLE_SID
unset LD_LIBRARY_PATH; unset LD_LIBRARY_PATH_64; unset SHLIB_PATH; unset LIB_PATH
echo You should Add this entry in the /etc/oratab: db01:/u01/app/oracle/product/11.2.0.3/db_1:Y
cd /u01/app/oracle/product/11.2.0.3/db_1
echo -- Removing /u01/app/oracle/cfgtoollogs/dbua/logs/Welcome_db01.txt file
rm -f /u01/app/oracle/cfgtoollogs/dbua/logs/Welcome_db01.txt ;
/u01/app/oracle/product/11.2.0.3/db_1/bin/sqlplus /nolog @/u01/app/oracle/cfgtoollogs/dbua/lax_db01/upgrade1/createSPFile_db01.sql
/u01/app/oracle/product/11.2.0.3/db_1/bin/sqlplus /nolog @/u01/app/oracle/cfgtoollogs/dbua/lax_db01/upgrade1/grpOpen_db01.sql
[oracle@lax:db01]/u01/app/oracle/cfgtoollogs/dbua/lax_db01/upgrade1
$ cat /u01/app/oracle/cfgtoollogs/dbua/lax_db01/upgrade1/shutdown_db01.sql
connect / as sysdba
shutdown abort;
exit;
[oracle@lax:db01]/u01/app/oracle/cfgtoollogs/dbua/lax_db01/upgrade1
$ cat /u01/app/oracle/cfgtoollogs/dbua/lax_db01/upgrade1/createSPFile_db01.sql
connect / as sysdba
CREATE SPFILE='/u01/app/oracle/product/11.2.0.3/db_1/dbs/spfiledb01.ora' from pfile='/u01/app/oracle/cfgtoollogs/dbua/lax_db01/upgrade1/init.ora';
exit;
[oracle@lax:db01]/u01/app/oracle/cfgtoollogs/dbua/lax_db01/upgrade1
$ cat /u01/app/oracle/cfgtoollogs/dbua/lax_db01/upgrade1/grpOpen_db01.sql
connect / as sysdba
startup mount;
flashback database to restore point grp_1372774067744;
alter database open resetlogs;
exit;
Improved sysresv
$ sysresv
IPC Resources for ORACLE_SID "db01" :
Maximum shared memory segment size (shmmax): 4398046511104 bytes
Total system shared memory (shmall): 4398046511104 bytes
Total system shared memory count (shmmni): 4096
Total /dev/shm size: 1581465600 bytes, used: 278528 bytes
Shared Memory:
ID KEY
24084484 0x450f83c4
24117253 0x00000000
Semaphores:
ID KEY
1867778 0x0c97f794
1900547 0x0c97f795
1933316 0x0c97f796
Oracle Instance alive for sid "db01"
Don’t forget to change compatible
$ sqlplus “/ as sysdba”
SQL*Plus: Release 12.1.0.1.0 Production on Tue Jul 2 14:54:29 2013
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
LAX:(SYS@db01)> show parameter compat
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
compatible string 11.2.0.3
noncdb_compatible boolean FALSE
plsql_v2_compatibility boolean FALSE
LAX:(SYS@db01)> exit