Thinking Out Loud

July 3, 2013

Upgrade to 12c using DBUA Silent (shhh) Mode

Filed under: 12c,upgrade — mdinh @ 1:52 am

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
About these ads

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

The Rubric Theme. Create a free website or blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 272 other followers

%d bloggers like this: