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> ]
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.
