Flying to Oracle database cloud 12c Season 1 episode 3

In the last episode we saw how the test configuration was set up, pre-checked and how we made some performance auditing.
Now it’s time to start the upgrade process. We are ready to go, fasten your seat belt, get ready for takeoff.

Our first objective is to use Oracle standard tool dbua to do the job, there are some real pros to use it :

  • Everything is done by the tool, you will not forget any step
  • oratab is modified with the new Oracle HOME
  • tnsnames.ora is updated
  • Database Express console is built for you
  • We can use it in silent mode (no need to have graphical packages and library on the server)
  • Parallel processing (New) can be activated, reducing time processing

Here is the whole process:

  • Check /etc/oratab before upgrading ora112 :
ora112:/u01/app/oracle/product/11.2.0.3:N
  • Check that the target database is up and running under the current release:
SQL> select * from v$version ;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE    11.2.0.3.0      Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production
  • Be sure to switch to the appropriate environment, the new 12c release:

[oracle@easydirtech ~]$ . oraenv
ORACLE_SID = [oracle] ? ora121 –> We use ora121 12c database already configured
The Oracle base has been set to /u01/app/oracle
[oracle@easydirtech ~]$ echo $ORACLE_HOME
/u01/app/oracle/product/12.1.0.1

  • run dbua and perform the migration in one step :
$dbua -silent -sid ora112 -oracleHome /u01/app/oracle/product/11.2.0.3
Log files for the upgrade operation are located at: /u01/app/oracle/cfgtoollogs/dbua/ora112/upgrade16
Performing Pre Upgrade
4% complete
20% complete
Performing RDBMS Upgrade
20% complete
…/…
39% complete
40% complete
Performing Post Upgrade
42% complete
44% complete
60% complete
Configuring Database with Enterprise Manager
62% complete
64% complete
80% 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_1375282039360" for upgrade details.
  • Done, 73 minutes later, you’re in the air with the 12c release!

Let’s have some more details about it. First, take a look at the main logfile  :

[oracle@easydirtech bin]$ ls -ltr /u01/app/oracle/cfgtoollogs/dbua/logs/silent.log_1375282039360
-rw-r-----. 1 oracle oinstall 3557 Jul 31 12:01 /u01/app/oracle/cfgtoollogs/dbua/logs/silent.log_1375282039360
[oracle@easydirtech bin]$cat /u01/app/oracle/cfgtoollogs/dbua/logs/silent.log_1375282039360
Upgrading the database dbName="ora112"
Database contains schemas with objects dependent on DBMS_LDAP package. Refer to the Oracle Database Upgrade Guide for instructions to configure Network ACLs.
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.
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:  507 MB
Datafile:  /u01/app/oracle/oradata/ora112/system01.dbf
Auto Extensible:  Yes
Tablespace Name:  SYSAUX
Additional space required:  905 MB
Datafile:  /u01/app/oracle/oradata/ora112/sysaux01.dbf
Auto Extensible:  Yes
Tablespace Name:  UNDOTBS1
Additional space required:  250 MB
Datafile:  /u01/app/oracle/oradata/ora112/undotbs01.dbf
Auto Extensible:  Yes
Tablespace Name:  TEMP
Additional space required:  0 MB
Datafile:  /u01/app/oracle/oradata/ora112/temp01.dbf
Auto Extensible:  Yes
Tablespace Name:  EXAMPLE
Additional space required:  0 MB
Datafile:  /u01/app/oracle/oradata/ora112/example01.dbf
Auto Extensible:  Yes
Space usage reason: Log and Trace files
Directory:  /u01/app/oracle/cfgtoollogs/dbua/ora112/upgrade16
Required space:  160 MB
Space usage reason: Archive Logs and Flashback Logs
Fast Recovery Area:  /u01/app/oracle/fast_recovery_area
Estimated space required:  0 MB
Disk space usage summary
/u01/ has enough space. Required space is 1822 MB , available space is 11186 MB.
Performing Pre Upgrade
UPGRADE_PROGRESS : 4%
UPGRADE_PROGRESS : 20%
Performing RDBMS Upgrade
UPGRADE_PROGRESS : 20%
…/…
UPGRADE_PROGRESS : 39%
UPGRADE_PROGRESS : 40%
Performing Post Upgrade
UPGRADE_PROGRESS : 42%
UPGRADE_PROGRESS : 44%
UPGRADE_PROGRESS : 60%
Configuring Database with Enterprise Manager
UPGRADE_PROGRESS : 62%
UPGRADE_PROGRESS : 64%
UPGRADE_PROGRESS : 80%
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.1/assistants/dbua/doc/DefaultBehaviorChanges_<NLS_LANG>.html

The tool runs the precheck step once more and if you haven’t seen it already, the script utluiobj.sql (present since 11.1) could be used to find differences between invalid objects before and after the upgrade.  This is especially useful if you are a consultant inside the client’s place and responsible for any change after an upgrade. It is a lifesaver.
There is one log directory created each time you run dbua and several files generated inside it:

[oracle@easydirtech oracle]$ cd /u01/app/oracle/cfgtoollogs/dbua/ora112/upgrade16
[oracle@easydirtech upgrade16]$ ls -ltr
total 256392
-rw-r-----. 1 oracle oinstall         0 Jul 31 10:47 trace.log.lck
-rw-r-----. 1 oracle oinstall      4382 Jul 31 10:47 upgrade.xml
-rw-r-----. 1 oracle oinstall      6632 Jul 31 10:47 PreUpgradeResults.html
-rw-r-----. 1 oracle oinstall       947 Jul 31 10:48 PreUpgrade.log
-rw-r-----. 1 oracle oinstall         0 Jul 31 10:48 Oracle_Text.log
-rw-r-----. 1 oracle oinstall   5653116 Jul 31 11:47 catupgrd3.log
-rw-r-----. 1 oracle oinstall   5105060 Jul 31 11:47 catupgrd2.log
-rw-r-----. 1 oracle oinstall   4769539 Jul 31 11:47 catupgrd1.log
-rw-r-----. 1 oracle oinstall 246483416 Jul 31 11:47 catupgrd0.log
-rw-r-----. 1 oracle oinstall    152599 Jul 31 11:50 Oracle_Server.log
-rw-r-----. 1 oracle oinstall     10275 Jul 31 12:01 PostUpgrade.log
-rw-r-----. 1 oracle oinstall      5658 Jul 31 12:01 UpgradeResults.html
-rw-r-----. 1 oracle oinstall    213195 Jul 31 12:01 trace.log
-rw-r-----. 1 oracle oinstall     86291 Jul 31 12:01 sqls.log

The four catupgrdn.log files (catupgrd0.log to catupgrd3.log), and the complete schedule trace in Oracle_Server.log show us that the tool use a parallel level of 4, which is its default value.
So, if for any reason you want to disable it, you have to specify a degree of one (1).
Restarting the process (restoring my database to previous release 11.2.0.3, in order to redo all the preliminary steps, I used this command :

[oracle@easydirtech ~]$ time dbua -silent -sid ora112 -oracleHome /u01/app/oracle/product/11.2.0.3 -upgrade_parallelism 1

Now I have no catupgrdn.log file, all the logs are on Oracle_Server.log and at the end of it, have a look at the following breakdown for each component.

Component                               Current         Version  Elapsed Time
Name                                    Status          Number   HH:MM:SS
.
Oracle Server                            UPGRADED      12.1.0.1.0  00:13:04
JServer JAVA Virtual Machine                VALID      12.1.0.1.0  00:02:46
Oracle Workspace Manager                    VALID      12.1.0.1.0  00:01:16
OLAP Analytic Workspace                     VALID      12.1.0.1.0  00:00:32
Oracle OLAP API                             VALID      12.1.0.1.0  00:00:27
Oracle XDK                                  VALID      12.1.0.1.0  00:00:46
Oracle Text                                 VALID      12.1.0.1.0  00:00:51
Oracle XML Database                         VALID      12.1.0.1.0  00:04:18
Oracle Database Java Packages               VALID      12.1.0.1.0  00:00:17
Oracle Multimedia                           VALID      12.1.0.1.0  00:02:40
Spatial                                     VALID      12.1.0.1.0  00:05:49
Oracle Application Express                  VALID     4.2.0.00.27  00:22:32
Final Actions                                                      00:02:07
Total Upgrade Time: 00:57:54

This time, the time spent during post upgrade wasn’t taken into account, but we could still see that it was as quick as our first run (with parallel value set to 4 by default) and that the APEX upgrade is taking most of the time.
The total execution time with my first configuration was 1 hour and 10 minutes (using default parallel degree to 4).
Please keep in mind that during this process, your database is totally unavailable for your applications.
Let see if it’s possible to reduce the amount of time taken by increasing the parallel level.
Restoring my database to previous release 11.2.0.3 I have done all the preliminary steps again. I upgraded my server by adding some more CPU power, going from 2 vCPUS to 6. As it is a virtual ma               chine server, it is quite easy using Oracle VM Manager to stop and modify the number of CPUs and to restart the virtual machine.
The upgrade now begins with the following:

[oracle@easydirtech ~]$ time dbua -silent -sid ora112 -oracleHome /u01/app/oracle/product/11.2.0.3 -upgrade_parallelism 6
Log files for the upgrade operation are located at: /u01/app/oracle/cfgtoollogs/dbua/ora112/upgrade17
Performing Pre Upgrade
4% complete
20% complete
Performing RDBMS Upgrade
20% complete
20% complete
../…
39% complete
39% complete
40% complete
Performing Post Upgrade
42% complete
44% complete
60% complete
Configuring Database with Enterprise Manager
62% complete
64% complete
80% 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_1377001183880" for upgrade details.
real    69m52.873s
user    0m26.307s
sys     0m8.848s

Nearly the same time as before, no improvements here.
The parallel processes are apparently far from being overloaded, they did not have much to do.
Before using another upgrade method, let’s look at some results after the dbua runs :

  • The oratab file was updated to the current release for sid ora112 :
ora112:/u01/app/oracle/product/12.1.0.1:N
  • We have new entries dedicated to sid ora112 in tnsnames.ora (this is used by the local_listener in the database, associated with the new listener):
LISTENER_ORA112 =
(ADDRESS = (PROTOCOL = TCP)(HOST = easydirtech.easyteam.fr)(PORT = 1521))
ORA112 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = easydirtech.easyteam.fr)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ora112)
)
)

At this point let’s focus on the differences between the two release of dbua,  a diff on the help output give the following :

[oracle@easydirtech ~]$ diff /tmp/dbua112 /tmp/dbua121
8a9
>     [-auditFileDest <Specify Database Audit File Destination > ]
24,28c25
<     [-sysauxTablespace
<            -datafileName <Specify the complete path of datafile for creating SYSAUX tablespace.>
<            -datafileSize <Specify the size of datafile for creating SYSAUX tablespace {500M, 10G}.> ]
<            -datafileSizeNext <Specify the next extent for the datafile.> ]
<            -datafileSizeMax <Specify the max size of datafile.> ]
---
>     [-preUpgradeScripts <Specify a comma separated list of SQL scripts with their complete pathnames. These scripts will be executed before the upgrade.> ]
29a27
>     [-ignoreScriptErrors <Specify this flag for ignoring ORA errors during custom scripts> ]
31a30,32
>     [-changeUserTablespacesReadOnly <Change user tablespaces read only for the duration of the upgrade.> ]
>     [-gatheringStatistics <Gathering statistics before upgrade database.> ]
>     [-upgrade_parallelism <Specify number of CPU's to be used for parallel upgrade> ]
36,43d36
<     [-emConfiguration <CENTRAL|LOCAL|ALL|NONE>
<            -dbsnmpPassword<DBSNMP user password>
<            -sysmanPassword<SYSMAN user password>
<            -asmPassword<SYS password for ASM instance>
<            -hostUserName<Host user name for EM backup job>
<            -hostUserPassword<Host user password for EM backup job>
<            -backupSchedule <Daily backup schedule in the form of hh:mm>]
<           [-centralAgent <Enterprise Manager central agent location>]]
45a39,49
>     [-emConfiguration <DBEXPRESS|CENTRAL|BOTH|NONE>]
>       -dbsnmpPassword < DBSNMP user password>
>       [-omsHost < EM management server host name>
>        -omsPort < EM management server port number>
>        -emUser < EM Admin username to add or modify targets>
>        -emPassword < EM Admin user password>]
>     [-createGRP <To create a guaranteed restore point when database is in archive log and flashback mode> ]
>     [-useGRP <To restore the database using specified guaranteed restore point> ]
>     [-useExistingBackup <To restore database using existing RMAN backup> ]
>     [-listeners <To register the database with existing listeners, specify listeners by comma separated listenerName:Oracle Home. Listeners from lower release home are migrated to newer release home. Specifying  -listeners lsnrName1,lsnrName2, DBUA searches specified listeners from GI home (if configured), target home and source home.> ]
>     [-createListener <To create a listener in newer release Oracle home specify listenrName:lsnrPort> ]
pecify listenrName:lsnrPort> ]

The ‘<’ caracter at the beginning of the line is for old 11.2 releases, the ‘>’ is for the new options. Apart from the fact that DBEXRESS replace LOCAL for EM configuration, we can now use our own preupgrade script. We can secure the operations by taking users tablespaces in readonly mode, automating the gathering of statistics before  upgrading, using it in parallel (see below) , creating and using restore points for backup, registering database with specific listener or creating a new listener.
Migrate in manual mode
It is relatively straightforward to use dbua, but what if we want to have some more control on all the stages of the upgrade? Let’s go for it, start again and now use the upgrade script.

  • cleanup

Some cleanup is necessary under the 12G Oracle home dbs directory: removing all the files (spfile , password file) related to ora112 and generated by my numerous attempts.
Removing all references of it in tnsnames.ora .
We are nearly ready to go, remember that we have to do all this little steps before we can accomplish the goal we seek, if we miss just one, we may have to restart the process all over again.

  • Shut it all down

The first stage is to shut down our 11.2 ora112 database. At that point, our applications are stopped and we do not have any access to them.
Be sure to stop it cleanly, if transactions or rollback are pending you will have to wait.

[oracle@easydirtech ~]$ . oraenv
ORACLE_SID = [ora121] ? ora112     -->  switch environnement to 11.2 ORACLE_HOME
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@easydirtech ~]$ echo $ORACLE_HOME
/u01/app/oracle/product/11.2.0.3
[oracle@easydirtech ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Fri Aug 23 05:37:53 2013
Copyright (c) 1982, 2011, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> create pfile='/home/oracle/initora112.ora' from spfile ;   -->  In case we need pfile for safety
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>

Stop the listener to be sure any login is denied (remember our listener is 12c, it might not be the case for you).

[oracle@easydirtech ~]$ . oraenv
ORACLE_SID = [oracle] ? ora121
[oracle@easydirtech ~]$ lsnrctl stop
LSNRCTL for Linux: Version 12.1.0.1.0 - Production on 23-AUG-2013 05:35:53
Copyright (c) 1991, 2013, Oracle.  All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
The command completed successfully
  • Startup for ugrade

Take care of /etc/oratab and update the line for ora112 to read the path of 12G release. Be aware that this file belongs to the root user.

ora112:/u01/app/oracle/product/12.1.0.1:N

Setup env to 12C and startup our database for upgrading:
Hold on a second! We need a parameter file!  We will use the previously created one as we can’t use pfile from previous release due to SP-0172 error:

SQL> startup upgrade spfile='/u01/app/oracle/product/11.2.0.3/dbs/spfileora112.ora' ;
SP2-0714: invalid combination of STARTUP options
SQL> startup upgrade pfile='/home/oracle/initora112.ora' ;
ORACLE instance started.
Total System Global Area 1252663296 bytes
Fixed Size                  2287864 bytes
Variable Size             822085384 bytes
Database Buffers          419430400 bytes
Redo Buffers                8859648 bytes
Database mounted.
Database opened.
SQL>exit

The main difference between the previous manual upgrade is that we now use the perl script catctl.pl  and we are not directly calling catupgrd.sql
The reason for that is related to the new parallelization process and the specific scheduling of the different tasks, let’s see what’s on it:

#    NAME
#      catctl.pl - CATalog ConTroL PerL program
#
#    DESCRIPTION
#      This perl program processes sqlplus files and organizes
#      them for parallel processing based on annotations within
#      the files.
#
#    NOTES
#      Used by catupgrd shell/bat scripts to run parallel upgrades
#      Connects to database specified by ORACLE_SID environment variable

Several options are available, you can obtain a brief description by calling it without arguments:

[oracle@easydirtech ~]$ cd $ORACLE_HOME/rdbms/admin
[oracle@easydirtech admin]$ $ORACLE_HOME/perl/bin/perl catctl.pl
Usage: catctl [-u username] [-n processes] [-d directory]
[-t table] [-l directory] [-s script]
[-e] [-i] [-c] filename
Supported Options:
-u username (prompts for password)
-n the number of processes to use for parallel operations (default 4)
-d directory containing the files to be run
-t table name for phase logging
-l directory to use for spool log files
-s SQL script to initialize sessions
-e sets echo off while running the scripts
-p restart phase (skip successful phases on a rerun)
-i identifier to use when creating spool log files
-y display phases only

If for some reason, you don’t want to use perl, you can try to call it under SQL*PLUS using :

@catpugrd.sql PARALLEL=NO

Here are some excerpt of catupgrd.sql

em    NAME
Rem      catupgrd.sql - CATalog UPGraDe to the new release
Rem
Rem    DESCRIPTION
Rem     This script is to be used for upgrading a 9.2, 10.1 or 10.2
Rem     database to the new release.  This script provides a direct
Rem     upgrade path from these releases to the new Oracle release.
Rem
Rem      The upgrade is partitioned into the following 5 stages:
Rem        STAGE 1: call the "i" script for the oldest supported release:
Rem                 This loads all tables that are necessary
Rem                 to perform basic DDL commands for the new release
Rem        STAGE 2: call utlip.sql to invalidate PL/SQL objects
Rem        STAGE 3: Determine the original release and call the
Rem                 c0x0x0x0.sql for the release.  This performs all
Rem                 necessary dictionary upgrade actions to bring the
Rem                 database from the original release to new release.
../…
######################################################################
######################################################################
NOTE
The catupgrd.sql is being deprecated in the 12.1 release of the
Oracle Database.  Customers are encouraged to use catctl.pl as
the replacement for catupgrd.sql when upgrading the database dictionary.
cd $ORACLE_HOME/rdbms/admin
$ORACLE_HOME/perl/bin/perl catctl.pl -n 4 catupgrd.sql
Refer to the Oracle Database Upgrade Guide for more information.
This database upgrade procedure must be called with the following
argument when invoking from the SQL prompt:
@catupgrd.sql PARALLEL=NO
  • Upgrade time

Let start the upgrade as explained by the documentation:

[oracle@easydirtech admin]$ time $ORACLE_HOME/perl/bin/perl catctl.pl catupgrd.sql
Analyzing file catupgrd.sql
14 scripts found in file catupgrd.sql
Next path: catalog.sql
32 scripts found in file catalog.sql
Next path: catproc.sql
37 scripts found in file catproc.sql
Next path: catptabs.sql
61 scripts found in file catptabs.sql
Next path: catpdbms.sql
205 scripts found in file catpdbms.sql
Next path: catpdeps.sql
77 scripts found in file catpdeps.sql
Next path: catpprvt.sql
260 scripts found in file catpprvt.sql
Next path: catpexec.sql
26 scripts found in file catpexec.sql
Next path: cmpupgrd.sql
16 scripts found in file cmpupgrd.sql

…./… followed by detailed steps, execution times:

*** WARNING: ERRORS FOUND DURING UPGRADE ***
Due to errors found during the upgrade process, the post
upgrade actions in catuppst.sql have not been automatically run.
*** THEREFORE THE DATABASE UPGRADE IS NOT YET COMPLETE ***
1. Evaluate the errors found in the upgrade logs (*.log) and determine the proper action.
2. Execute the post upgrade script as described in Chapter 3 of the Database Upgrade Guide.
     Time: 226s
Grand Total Time: 3612s
real    60m11.795s
user    0m23.185s
sys     0m1.352s
  • Post upgrade analysis

To evaluate the errors, there is no need to dig in the log file, you just have to run utlu121s.sql (calling utlusts.sql – Utility Upgrade Status) which summarizes the results stored in dba_registry_log and registry$ for you.
Don’t forget that the database is down at the end of the catctl.pl perl script, you need to open it and at that time we still don’t have any pfile :

SQL> startup pfile='/home/oracle/initora112.ora'
SQL> @utlu121s.sql
Oracle Database 12.1 Post-Upgrade Status Tool           08-23-2013 08:17:47
Component                               Current         Version  Elapsed Time
Name                                    Status          Number   HH:MM:SS
Oracle Server                          UPGRADED      12.1.0.1.0  00:11:39
JServer JAVA Virtual Machine              VALID      12.1.0.1.0  00:02:59
Oracle Workspace Manager                  VALID      12.1.0.1.0  00:01:53
OLAP Analytic Workspace                   VALID      12.1.0.1.0  00:00:35
Oracle OLAP API
.   SP2-0310: unable to open file "xoqsys.sql"
.   ORA-01917: user or role "OLAPSYS" does not exist
.   ORA-00942: table or view does not exist
.   ORA-00942: table or view does not exist
.   ORA-00942: table or view does not exist
.   ORA-00942: table or view does not exist
.   ORA-00942: table or view does not exist
.                                         VALID      12.1.0.1.0  00:00:27
Oracle XDK                                VALID      12.1.0.1.0  00:00:46
Oracle Text                               VALID      12.1.0.1.0  00:00:55
Oracle XML Database                       VALID      12.1.0.1.0  00:04:21
Oracle Database Java Packages             VALID      12.1.0.1.0  00:00:17
Oracle Multimedia                         VALID      12.1.0.1.0  00:02:28
Spatial                                   VALID      12.1.0.1.0  00:05:59
Oracle Application Express                VALID     4.2.0.00.27  00:23:37
Final Actions                                                    00:03:25
Total Upgrade Time: 00:59:47
PL/SQL procedure successfully completed.

There are errors regarding OLAP API, as we’ve seen before (have a look at the second episode), this sounds OK and we continue:

  • Post upgrade actions

The next manual step is to run catupsst.sql, it should have be run if no error were detected.

Rem    NAME
Rem      catuppst.sql - CATalog UPgrade PoST-upgrade actions
Rem
Rem    DESCRIPTION
Rem      This post-upgrade script performs remaining upgrade actions that
Rem      do not require that the database be open in UPGRADE mode.
Rem      Automatically apply the latest PSU.

Here’s the result:

SQL> @?/rdbms/admin/catuppst.sql 
Session altered.
Session altered.
Session altered.
TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP POSTUP_BGN 2013-08-23 08:29:13
TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP CATREQ_BGN 2013-08-23 08:29:13
PL/SQL procedure successfully completed.
catrequtlmg: b_StatEvt     = TRUE
catrequtlmg: b_SelProps    = FALSE
catrequtlmg: b_UpgradeMode = FALSE
catrequtlmg: b_InUtlMig    = FALSE
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP CATREQ_END 2013-08-23 08:29:13
catuppst: Dropping library DBMS_DDL_INTERNAL_LIB
PL/SQL procedure successfully completed.
catuppst: Dropping view _CURRENT_EDITION_OBJ_MIG
PL/SQL procedure successfully completed.
catuppst: Dropping view _ACTUAL_EDITION_OBJ_MIG
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
This script will migrate the Baseline data on a pre-11g database
to the 11g database.
...                                       ...
... Completed Moving the Baseline Data    ...
...                                       ...
... If there are no Move BL Data messages ...
... above, then there are no renamed      ...
... baseline tables in the system.        ...
...                                       ...
...                                       ...
... Completed the Dropping of the         ...
... Renamed Baseline Tables               ...
...                                       ...
... If there are no Drop Table messages   ...
... above, then there are no renamed      ...
... baseline tables in the system.        ...
...                                       ...
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
0 rows created.
Commit complete.
Table created.
2 rows created.
1 row updated.
2 rows updated.
0 rows updated.
Table dropped.
Commit complete.
0 rows updated.
Commit complete.
0 rows updated.
Commit complete.
0 rows updated.
Commit complete.
0 rows created.
Commit complete.
0 rows created.
Commit complete.
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
catuppst: Gathering fixed objects stats now...
catuppst: Gathering fixed objects stats done.
PL/SQL procedure successfully completed.
TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP POSTUP_END 2013-08-23 08:31:30
PL/SQL procedure successfully completed.
Session altered.
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
Generating apply and rollback scripts...
Check the following file for errors:
/u01/app/oracle/cfgtoollogs/catbundle/catbundle_PSU_ORA112_GENERATE_2013Aug23_08_31_37.log
Apply script: /u01/app/oracle/product/12.1.0.1/rdbms/admin/catbundle_PSU_ORA112_APPLY.sql
Rollback script: /u01/app/oracle/product/12.1.0.1/rdbms/admin/catbundle_PSU_ORA112_ROLLBACK.sql
PL/SQL procedure successfully completed.
Executing script file...
SQL> alter session set "_ORACLE_SCRIPT" = true;
Session altered.
SQL> COLUMN spool_file NEW_VALUE spool_file NOPRINT
SQL> SELECT '/u01/app/oracle/cfgtoollogs/catbundle/' || 'catbundle_PSU_' || name || '_APPLY_' || TO_CHAR(SYSDATE, 'YYYYMonDD_hh24_mi_ss', 'NLS_DATE_LANGUAGE=''AMERICAN''') || '.log' AS spool_file FROM v$database;
SQL> SPOOL &spool_file
SQL> exec dbms_registry.set_session_namespace('SERVER')
PL/SQL procedure successfully completed.
SQL> ALTER SESSION SET current_schema = SYS;
Session altered.
SQL> PROMPT Updating registry...
Updating registry...
SQL> INSERT INTO registry$history
  2    (action_time, action,
  3     namespace, version, id,
  4     bundle_series, comments)
  5  VALUES
  6    (SYSTIMESTAMP, 'APPLY',
  7     SYS_CONTEXT('REGISTRY$CTX','NAMESPACE'),
  8     '12.1.0.1',
  9     0,
10     'PSU',
11     'Patchset 12.1.0.0.0');
1 row created.
SQL> COMMIT;
Commit complete.
SQL> SPOOL off
SQL> SET echo off
Check the following log file for errors:
/u01/app/oracle/cfgtoollogs/catbundle/catbundle_PSU_ORA112_APPLY_2013Aug23_08_31_38.log
Session altered.
Session altered.
Currently our database is now upgraded to release 12.1.0.1
Check with :
SQL>select comp_id,comp_name,version,status from dba_registry ;
COMP_ID         COMP_NAME                                VERSION                        STATUS
--------------- ---------------------------------------- ------------------------------ --------------------------------------------
APEX            Oracle Application Express               4.2.0.00.27                    VALID
OWB             OWB                                      11.2.0.3.0                     VALID
SDO             Spatial                                  12.1.0.1.0                     VALID
ORDIM           Oracle Multimedia                        12.1.0.1.0                     VALID
XDB             Oracle XML Database                      12.1.0.1.0                     VALID
CONTEXT         Oracle Text                              12.1.0.1.0                     VALID
OWM             Oracle Workspace Manager                 12.1.0.1.0                     VALID
CATALOG         Oracle Database Catalog Views            12.1.0.1.0                     UPGRADED
CATPROC         Oracle Database Packages and Types       12.1.0.1.0                     UPGRADED
JAVAVM          JServer JAVA Virtual Machine             12.1.0.1.0                     VALID
XML             Oracle XDK                               12.1.0.1.0                     VALID
CATJAVA         Oracle Database Java Packages            12.1.0.1.0                     VALID
APS             OLAP Analytic Workspace                  12.1.0.1.0                     VALID
XOQ             Oracle OLAP API                          12.1.0.1.0                     VALID

We did it, a little bit longer but we are there in the sky with a cloud database !

  • Best practises

Run optional but best practices:

SQL> execute dbms_stats.gather_fixed_objects_stats;
PL/SQL procedure successfully completed.

Takes 2 minutes to run.
Recompile all :

SQL> @utlrp.sql
No Object or recompilation in error state ,   summary of the database objects :
...Database user "SYS", database schema "APEX_040200", user# "117" 09:31:13
...Compiled 0 out of 2998 objects considered, 0 failed compilation 09:31:14
...263 packages
...255 package bodies
...453 tables
...11 functions
...16 procedures
...3 sequences
...458 triggers
...1322 indexes
...207 views
...0 libraries
...6 types
...0 type bodies
...0 operators
...0 index types
...Begin key object existence check 09:31:14
...Completed key object existence check 09:31:14
...Setting DBMS Registry 09:31:14
...Setting DBMS Registry Complete 09:31:14
...Exiting validate 09:31:14

It takes 3 more minutes

It’s now time to establish a list of all possible invalid objects that after upgrading:

SQL> @utluiobj.sql
Oracle Database 12.1 Post-Upgrade Invalid Objects Tool 08-23-2013 09:34:10
This tool lists post-upgrade invalid objects that were not invalid
prior to upgrade (it ignores pre-existing pre-upgrade invalid objects).
.
Owner                     Object Name                     Object Type
.
The table registry$sys_inv_objs does not exist. The pre-upgrade tool,
utlu111i.sql, creates and populates registry$sys_inv_objs. To use this
post-upgrade tool, you must have run utlu111i.sql prior to upgrading
the database.

As you can see, I haven’t run it at the beginning, don’t miss it!!

  • Final steps

Create an spfile , verify the listener configuration (if the listener is not listening on the 1521 port).

SQL>  create spfile from pfile='/home/oracle/initora112.ora' ;
File created.
SQL> show parameter listener
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
listener_networks                    string
local_listener                       string
remote_listener                      string

Start the listener and applications to be able to access the database and manually register the database to avoid registration delay:

SQL> !lsnrctl start
LSNRCTL for Linux: Version 12.1.0.1.0 - Production on 23-AUG-2013 09:41:02
Copyright (c) 1991, 2013, Oracle.  All rights reserved.
Starting /u01/app/oracle/product/12.1.0.1/bin/tnslsnr: please wait...
TNSLSNR for Linux: Version 12.1.0.1.0 - Production
System parameter file is /u01/app/oracle/product/12.1.0.1/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/easydirtech/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=easydirtech.easyteam.fr)(PORT=1521)))
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 12.1.0.1.0 - Production
Start Date                23-AUG-2013 09:41:02
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/12.1.0.1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/easydirtech/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=easydirtech.easyteam.fr)(PORT=1521)))
The listener supports no services
The command completed successfully
SQL> alter system register ;
System altered.
SQL> !lsnrctl status
LSNRCTL for Linux: Version 12.1.0.1.0 - Production on 23-AUG-2013 09:41:20
Copyright (c) 1991, 2013, Oracle.  All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 12.1.0.1.0 - Production
Start Date                23-AUG-2013 09:41:02
Uptime                    0 days 0 hr. 0 min. 18 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/12.1.0.1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/easydirtech/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=easydirtech.easyteam.fr)(PORT=1521)))
Services Summary...
Service "ora112" has 1 instance(s).
  Instance "ora112", status READY, has 1 handler(s) for this service...
Service "ora112XDB" has 1 instance(s).
  Instance "ora112", status READY, has 1 handler(s) for this service...
The command completed successfully

What shall we do next? We may need to configure the APEX Database Express Console.
We can check that it is not yet configured:

SQL> select dbms_xdb_config.gethttpport() from dual;
DBMS_XDB_CONFIG.GETHTTPPORT()
-----------------------------
                            0

Do it now on port 808 :

SQL> exec dbms_xdb_config.sethttpport(8080);
PL/SQL procedure successfully completed.
SQL> select dbms_xdb.gethttpport() from dual;
DBMS_XDB.GETHTTPPORT()
----------------------
                  8080
SQL> !lsnrctl status
LSNRCTL for Linux: Version 12.1.0.1.0 - Production on 23-AUG-2013 09:52:07
Copyright (c) 1991, 2013, Oracle.  All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 12.1.0.1.0 - Production
Start Date                23-AUG-2013 09:41:02
Uptime                    0 days 0 hr. 11 min. 4 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/12.1.0.1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/easydirtech/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=easydirtech.easyteam.fr)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=easydirtech.easyteam.fr)(PORT=8080))(Presentation=HTTP)(Session=RAW))
Services Summary...
Service "ora112" has 1 instance(s).
  Instance "ora112", status READY, has 1 handler(s) for this service...
Service "ora112XDB" has 1 instance(s).
  Instance "ora112", status READY, has 1 handler(s) for this service...
The command completed successfully

That’s all you need, you can access “Enterprise Manager Database Express 12c using the url http://yourhostname:8080/em/ login and play with it now!
So, this is all great and everything if you really trust Oracle and that you are confident that this release will improve or at least maintain your performance level, but if like me you tend to be cautious, you might want to run all of this in a qualification, test environment by checking all critical parts of your applications with it.
We will learn how to do that next time with our swingbench application test.

Laisser un commentaire

Votre adresse e-mail ne sera pas publiée. Les champs obligatoires sont indiqués avec *