Manual database upgrade from Oracle 12.1.0.1 to 12.1.0.2

Manual database upgrade from Oracle 12.1.0.1 to  12.1.0.2

Remark

A lot of Oracle DBA’s doesn’t use the dbua to upgrade an existing database to a new release. They would like to control the upgrade process by themselves.

Following I did an “manually upgrade” to 12.1.0.2. The upgrade based on the Oracle MOS note 1932762.1.

My test environment is OEL 6.5 and 12.1.0.1 software installation. I have a container database “CDB” and some pluggable databases “PDB1, PDB2, PDB3”.

I used this environment for my OCP 12c certification upgrade. This OCP exam actually based on the Oracle  release 12.1.0.1.

I passed the OCP exam and so I could upgrade my environment to test the new features in 12.1.0.2. :-)

 

Setup and installation of the Oracle 12.1.0.2 software

 First of all I download the Patchset from MOS

It is Patchset 17694377

Unzip the archives and create a new directory structure. Keep in mind that Oracle recommend to do an „out of place upgrade“ which means install in an new directory „structure“.


/u01/app/oracle/product/12.1.0.2/db_home1

From the temporary directory I start the installer


./runInstaller

Here are some screenshots from the software installation

12.1.0.2_setup

runinstaller_1

runinstaller_2

runinstaller_3

runinstaller_4

runinstaller_5

runinstaller_finish

The software installation was done which is the base for starting the database upgrade

 

Pre-Steps

Ensure that all database components/objects are “VALID”  in the source database  before starting the upgrade

Check for invalid objects:


select object_name, object_type from all_objects where status = ‘INAVLID’;

SQL> alter pluggable database all open;

Pluggable database altered.

SQL> select CON_ID, NAME, OPEN_MODE from V$PDBS;

   CON_ID NAME                     OPEN_MODE

---------- ------------------------------ ----------

        2 PDB$SEED                 READ ONLY

        3 PDB1                     READ WRITE

        4 PDB2                     READ WRITE

        5 PDB3                     READ WRITE

SQL> Select object_name, object_type from all_objects where status = 'INVALID';

no rows selected

SQL> alter session set container=PDB1;

Session altered.

SQL> Select object_name, object_type from all_objects where status = 'INVALID';

no rows selected

SQL> alter session set container=PDB2;

Session altered.

SQL> Select object_name, object_type from all_objects where status = 'INVALID';

no rows selected

SQL> alter session set container=PDB3;

Session altered.

SQL> Select object_name, object_type from all_objects where status = 'INVALID';

no rows selected

 

Check the database status

It should be open and read/write


SQL> select NAME, DECODE(CDB, 'YES', 'Multitenant Option enabled', 'Regular 12c Database: ') "Multitenant Option ?" , OPEN_MODE, CON_ID from V$DATABASE;

NAME     Multitenant Option ?         OPEN_MODE                  CON_ID
--------- -------------------------- --------------------     ----------
CDB      Multitenant Option enabled   READ WRITE                    0

 

Please take a backup of the whole database before you start

In my example I don’t use an rman catalog

rman target /

backup database <CDB> tag before-upgrade-12102

 

Pre-Upgrade Steps

From the 12.1.0.2 directory run the preupgrd.sql script

PDB status should be in read / write status

sql> show pdbs;

To make it read write

sql> alter pluggable database <PDB_Name> open;

cd $ORACLE_HOME/rdbms/admin

$ORACLE_HOME/perl/bin/perl catcon.pl -d <directory location of preupgrade scripts> -l <log location of preupgrade script> -c <PDB_Name> -b <preupgrade_log_base_name> preupgrd.sql

For using the catcon.pl script please refer:

MOS note 1932340.1 How to execute sql scripts in Multitenant environment (catcon.pl)

Some remarks to the catcon.pl script at the end.


[oracle@srv1 ~]$ env
ORACLE_SID=CDB
ORACLE_BASE=/u01/app/oracle
ORACLE_HOME=/u01/app/oracle/product/12.1.0.1/db_home1

[oracle@srv1 ~]$
cd $ORACLE_HOME/rdbms/admin

/u01/app/oracle/product/12.1.0.1/db_home1/perl/bin/perl catcon.pl -d /u01/app/oracle/product/12.1.0.2/db_home1/rdbms/admin -l /home/oracle/preupgrd_log -c 'PDB1 PDB2 PDB3' -b preupgrd_12102 preupgrd.sql

After running the preupgrade check the logfile


[oracle@srv1 pdbfiles]$ more preupgrade_fixups.PDB1.log

Oracle Database Pre-Upgrade Information Tool 12-10-2014 09:30:51

Script Version: 12.1.0.2.0 Build: 006

**********************************************************************

Database Name:  CDB
Container Name:  PDB1
Container ID:  3
Version:  12.1.0.1.0
Compatible:  12.1.0.0.0
Blocksize:  8192
Platform:  Linux x86 64-bit
Timezone file:  V18

**********************************************************************

[Update parameters]

[No parameters to update]

**********************************************************************

**********************************************************************

[Renamed Parameters]
[No Renamed Parameters in use]

**********************************************************************

**********************************************************************

[Obsolete/Deprecated Parameters]
[No Obsolete or Desupported Parameters in use]

**********************************************************************

[Component List]

**********************************************************************

--> Oracle Catalog Views                   [upgrade]  VALID
--> Oracle Packages and Types              [upgrade]  VALID
--> JServer JAVA Virtual Machine           [upgrade]  VALID
--> Oracle XDK for Java                    [upgrade]  VALID
--> Real Application Clusters              [upgrade]  OPTION OFF
--> Oracle Workspace Manager               [upgrade]  VALID
--> OLAP Analytic Workspace                [upgrade]  VALID
--> Oracle Label Security                  [upgrade]  VALID
--> Oracle Database Vault                  [upgrade]  VALID

..

**********************************************************************

[Pre-Upgrade Recommendations]

**********************************************************************

*****************************************

********* Dictionary Statistics *********
*****************************************
Please gather dictionary statistics 24 hours prior to
upgrading the database.
To gather dictionary statistics execute the following command
while connected as SYSDBA:
EXECUTE dbms_stats.gather_dictionary_stats;

^^^ MANUAL ACTION SUGGESTED ^^^

**********************************************************************

[Post-Upgrade Recommendations]

**********************************************************************

*****************************************
******** Fixed Object Statistics ********
*****************************************

Please create stats on fixed objects two weeks

after the upgrade using the command:

EXECUTE DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;

^^^ MANUAL ACTION SUGGESTED ^^^

**********************************************************************

************  Summary  ************
<strong>0 ERRORS exist in your database.</strong>
<strong>0 WARNINGS exist in your database.</strong>
<strong>1 INFORMATIONAL message that should be reviewed prior to your upgrade.</strong>
After your database is upgraded and open in normal mode you must run
rdbms/admin/catuppst.sql which executes several required tasks and completes
the upgrade process.
You should follow that with the execution of rdbms/admin/utlrp.sql, and a
comparison of invalid objects before and after the upgrade using
rdbms/admin/utluiobj.sql
If needed you may want to upgrade your timezone data using the process
described in My Oracle Support note 1509653.1
***********************************

 There will be logfiles for all PDB’s. Take a look and process the recommendations

Next step

Collect statistics

Oracle recommends collecting statistics the night before starting the upgrade to decrease the amount of downtime

sqlplus "/as sysdba"

SQL> exec dbms_stats.gather_dictionary_stats;

 

Check if the statistics where collected by query the dba_optstat_operations view.

 


SQL> select id,operation,target, START_TIME,END_TIME from dba_optstat_operations

 

Verify that Materialized View Refreshes have completed before upgrading the database

 


SQL> select s.obj#,o.obj#,s.containerobj#,lastrefreshdate,pflags,xpflags,o.name,o.owner#, bitand(s.mflags, 8) from obj$ o, sum$ s where o.obj# = s.obj# and o.type# = 42 AND bitand(s.mflags, 8) = 8;

 

Ensure that no files need media recovery before upgrading

SQL> select * from v$recover_file;

 

Resolve outstanding distributed transactions before upgrading

 

SQL> select * from dba_2pc_pending;

if the query has some results please check with the following queries.

select  local_tran_id FROM dba_2pc_pending;

SQL> execute  dbms_transaction.purge_lost_db_entry('');

SQL> commit;

 

Purge the database recycle bin before upgrading

 


SQL> purge dba_recyclebin;

 

Disable all batch and cron jobs.

cron jobs or external jobs controlled at the OS level please contact the unix administrator

For jobs initiated by Oracle the following packages DBMS_JOB, DBMS_SCHEDULER can be used.

Please check the notes for details information

MOS note 404238.1 : How to Disable an Entry from DBMS_SCHEDULER

MOS note 1335741.1 : How To Stop A Running Job Using DBMS_JOB

The following query will give an idea if there are any problems


select LOG_DATE, OWNER,JOB_NAME, STATUS,ERROR#,REQ_START_DATE,ACTUAL_START_DATE,
ADDITIONAL_INFO from DBA_SCHEDULER_JOB_RUN_DETAILS

 

Verify SYS and SYSTEM Default tablespace

Check for all PDB’s


SQL> alter session set container=<PDB_Name>;

SQL> SELECT username, default_tablespace FROM dba_users  WHERE username in ('SYS','SYSTEM');

Review and Remove any unnecessary hidden/underscore parameters


SQL> alter session set container=<PDB_Name>;

SQL> SELECT name, value from SYS.V$PARAMETER WHERE name LIKE '\_%' ESCAPE '\' order by name;

Requirements and recommendations for target environment

Ensure that newly installed Oracle Home doesn’t have relinking errors

Install the latest opatch available for your platform and database version

Check for the latestet version of opatch and install it if necessary

Again be sure that you have a backup of your source database

 

Preparing new oracle home for upgrading

Copy the old spfile to the new software location

If using a oracle password file please copy this also to the new location

While upgrading an RAC database set the cluster_database = FALSE

Create a pfile from spfile to adjust new parameters. It makes live easier

 

 

After running the preupgrade without errors I start the upgrade procedure

 

Upgrade the CDB database to 12.1.0.2

Shutdown the database either CDB or NON-CDB

SQL> shutdown immediate

Changing the oratab


CDB:/u01/app/oracle/product/12.1.0.2/db_home1:N

Set the new environment


[oracle@srv1 dbs]$ . oraenv

ORACLE_SID = [CDB] ? CDB
The Oracle base remains unchanged with value /u01/app/oracle

[oracle@srv1 dbs]$ env|grep ORA
ORACLE_SID=CDB
ORACLE_BASE=/u01/app/oracle
ORACLE_HOME=/u01/app/oracle/product/12.1.0.2/db_home1


SQL> CONNECT / AS SYSDBA
Startup in upgrade mode

SQL> startup upgrade pfile=<pfile location>
Start all PDB in upgrade mode
SQL> alter pluggable database all open upgrade;

Exit SQL*Plus before proceeding to the next step.

SQL> exit;

 

Executing the upgrade procedure


[oracle@srv1 dbs]$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Wed Dec 10 11:18:26 2014
Copyright (c) 1982, 2014, Oracle.  All rights reserved.
Connected to an idle instance.

SQL> startup upgrade pfile='/u01/app/oracle/product/12.1.0.1/db_home1/dbs/initCDB.ora';

ORACLE instance started.
Total System Global Area 1862270976 bytes
Fixed Size              2925648 bytes
Variable Size               553651120 bytes
Database Buffers    1291845632 bytes
Redo Buffers           13848576 bytes
Database mounted.

alter pluggable database all open upgrade;
Database opened.

SQL> select name,open_mode from v$pdbs;
NAME                       OPEN_MODE
------------------------------ ----------
PDB$SEED                   MIGRATE
PDB1                       MIGRATE
PDB2                       MIGRATE
PDB3                       MIGRATE

Starting the upgrade script

Here a part of the logfile during the upgrade process. If I compare it to older versions the output in the logfile gives more detailed informations.


ORACLE_SID=CDB
ORACLE_BASE=/u01/app/oracle
ORACLE_HOME=/u01/app/oracle/product/12.1.0.2/db_home1

 


[oracle@srv1 admin]$ $ORACLE_HOME/perl/bin/perl catctl.pl -l /home/oracle/migrate_log catupgrd.sql

Example output

catctl.pl version: 12.1.0.2.0
Oracle Base           = /u01/app/oracle
Analyzing file catupgrd.sql
Log files in /home/oracle/migrate_log

catcon: ALL catcon-related output will be written to /home/oracle/migrate_log/catupgrd_catcon_5289.lst

catcon: See /home/oracle/migrate_log/catupgrd*.log files for output generated by scripts

catcon: See /home/oracle/migrate_log/catupgrd_*.lst files for spool files, if any

Number of Cpus        = 1

Parallel PDB Upgrades = 2

SQL PDB Process Count = 2

SQL Process Count     = 0

New SQL Process Count = 1

[CONTAINER NAMES]
CDB$ROOT
PDB$SEED
PDB1
PDB2
PDB3
PDB Inclusion:[PDB$SEED PDB1 PDB2 PDB3] Exclusion:[]

------------------------------------------------------

Phases [0-73]
Container Lists Inclusion:[CDB$ROOT] Exclusion:[]
Serial   Phase #: 0 Files: 1     Time: 7s    CDB$ROOT
Serial   Phase #: 1 Files: 5

…

Here starts the upgrade of PDB3
Total Upgrade Time:          [0d:0h:32m:10s]
Time: 154s  PDB3
Restart  Phase #:61 Files: 1     Time: 0s    PDB3
Serial   Phase #:62 Files: 1     Time: 81s   PDB3
Restart  Phase #:63 Files: 1     Time: 0s    PDB3
Serial   Phase #:64 Files: 1     Time: 1s    PDB3
Serial   Phase #:65 Files: 1 Calling sqlpatch with LD_LIBRARY_PATH=/u01/app/oracle/product/12.1.0.2/db_home1/lib; export LD_LIBRARY_PATH;/u01/app/oracle/product/12.1.0.2/db_home1/perl/bin/perl -I /u01/app/oracle/product/12.1.0.2/db_home1/rdbms/admin -I /u01/app/oracle/product/12.1.0.2/db_home1/rdbms/admin/../../sqlpatch /u01/app/oracle/product/12.1.0.2/db_home1/rdbms/admin/../../sqlpatch/sqlpatch.pl -verbose -upgrade_mode_only -pdbs PDB3 > /home/oracle/migrate_log/catupgrdpdb3_datapatch_upgrade.log 2> /home/oracle/migrate_log/catupgrdpdb3_datapatch_upgrade.err

returned from sqlpatch

Time: 7s    PDB3
Serial   Phase #:66 Files: 1     Time: 2s    PDB3
Serial   Phase #:68 Files: 1     Time: 3s    PDB3
Serial   Phase #:69 Files: 1 Calling sqlpatch with LD_LIBRARY_PATH=/u01/app/oracle/product/12.1.0.2/db_home1/lib; export LD_LIBRARY_PATH;/u01/app/oracle/product/12.1.0.2/db_home1/perl/bin/perl -I /u01/app/oracle/product/12.1.0.2/db_home1/rdbms/admin -I /u01/app/oracle/product/12.1.0.2/db_home1/rdbms/admin/../../sqlpatch /u01/app/oracle/product/12.1.0.2/db_home1/rdbms/admin/../../sqlpatch/sqlpatch.pl -verbose -pdbs PDB3 > /home/oracle/migrate_log/catupgrdpdb3_datapatch_normal.log 2> /home/oracle/migrate_log/catupgrdpdb3_datapatch_normal.err

returned from sqlpatch
Time: 8s    PDB3
Serial   Phase #:70 Files: 1     Time: 5s    PDB3
Serial   Phase #:71 Files: 1     Time: 0s    PDB3
Serial   Phase #:72 Files: 1     Time: 1s    PDB3
Serial   Phase #:73 Files: 1     Time: 0s    PDB3

Grand Total Time: 1657s PDB3
LOG FILES: (catupgrdpdb3*.log)
Upgrade Summary Report Located in:
/u01/app/oracle/product/12.1.0.2/db_home1/cfgtoollogs/CDB/upgrade/upg_summary.log

Total Upgrade Time:          [0d:0h:27m:37s]
Time: 1285s For CDB$ROOT
Time: 4150s For PDB(s)
Grand Total Time: 5435s
LOG FILES: (catupgrd*.log)

upgrade Summary Report Located in:

/u01/app/oracle/product/12.1.0.2/db_home1/cfgtoollogs/CDB/upgrade/upg_summary.log

Grand Total Upgrade Time:    [0d:1h:30m:35s]

Summary of the upgrade process

 


Oracle Database 12.1 Post-Upgrade Status Tool           12-10-2014 13:01:25

[PDB3:5]

Component                               Current         Version  Elapsed Time
Name                                    Status          Number   HH:MM:SS

&nbsp;

Oracle Server                          UPGRADED      12.1.0.2.0  00:14:36
JServer JAVA Virtual Machine              VALID      12.1.0.2.0  00:01:24
Oracle Real Application Clusters     OPTION OFF      12.1.0.2.0  00:00:01
Oracle Workspace Manager                  VALID      12.1.0.2.0  00:01:38
OLAP Analytic Workspace                   VALID      12.1.0.2.0  00:00:26
Oracle OLAP API                           VALID      12.1.0.2.0  00:00:10
Oracle Label Security                     VALID      12.1.0.2.0  00:00:08
Oracle XDK                                VALID      12.1.0.2.0  00:00:54
Oracle Text                               VALID      12.1.0.2.0  00:00:10
Oracle XML Database                       VALID      12.1.0.2.0  00:00:59
Oracle Database Java Packages             VALID      12.1.0.2.0  00:00:15
Oracle Multimedia                         VALID      12.1.0.2.0  00:02:12
Spatial                                UPGRADED      12.1.0.2.0  00:02:33
Oracle Application Express                VALID     4.2.5.00.08  00:01:10
Oracle Database Vault                     VALID      12.1.0.2.0  00:00:17
Final Actions                                                    00:00:07
Post Upgrade                                                     00:00:02

Total Upgrade Time: 00:27:15 [PDB3]

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.10

Last step from the upgrade process

 


[oracle@srv1 admin]$ $ORACLE_HOME/perl/bin/perl catcon.pl -n 1 -e -b utlu121s -d '''.''' utlu121s.sql

catcon: ALL catcon-related output will be written to utlu121s_catcon_17161.lst

catcon: See utlu121s*.log files for output generated by scripts

catcon: See utlu121s_*.lst files for spool files, if any

catcon.pl: completed successfully

At this point the manual upgrade is finished.

Please check that you have the correct spfile if not recreate a spfile. Also if needed upgrade the timezone and the statistics tables. (see below)

Upgrade timezone after database upgrade if necessary

If the pre upgrade give you some information for doing an timezone update  DBMS_DST PL/SQL package.

MOS note 1585343.1 : Scripts to automatically update the RDBMS DST (timezone) version in an 11gR2 or 12cR1 database

 

Upgrade to 12.1.0.2 finished

Recommended tasks if needed

Upgrade rman catalog

If you use a rman catalog you have to do an upgrade to be sure that your catalog will support the new Oracle release.

By the way please do a backup of the database newly upgraded database.

 

Conclusion

The upgrade procedure running manually via scripts takes a little bit more time if you do this the first time. By doing it in this way you learn a lot about how Oracle handle in Version 12c scripts for a multitenant environment. I show it here again with a simple SQL script.

Example for running scripts in multitenant environments

While using multitenant option the “catcon.pl” program will be your best friend in the future.

In my example I start a  SQL-Script against all PDB’s (PDB1, PDB2, PDB3)


/home/oracle/Stefan/sel_check_user.sql

How to start a individual script

$ /u01/app/oracle/product/12.1.0.1/db_home1/perl/bin/perl catcon.pl -d /home/oracle/stefan -l /home/oracle/preupgrd_log -c 'PDB1 PDB2 PDB3' -b preupgrd_12102 sel_check_user.sql

-d directory where the script exists

-l directory for logfiles

-c the pluggable database where the script has to be run here PDB1, PDB2, PDB3

-b base name for log and spool files (here preupgrd_12102)

 

Try and test it. Have fun. :-)

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Advertisements

Über spa

Oracle and Unix Professional, main focus on Oracle HA - Systems also an Exadata enthusiasts
Dieser Beitrag wurde unter General abgelegt und mit , , verschlagwortet. Setze ein Lesezeichen auf den Permalink.

Kommentar verfassen

Trage deine Daten unten ein oder klicke ein Icon um dich einzuloggen:

WordPress.com-Logo

Du kommentierst mit Deinem WordPress.com-Konto. Abmelden / Ändern )

Twitter-Bild

Du kommentierst mit Deinem Twitter-Konto. Abmelden / Ändern )

Facebook-Foto

Du kommentierst mit Deinem Facebook-Konto. Abmelden / Ändern )

Google+ Foto

Du kommentierst mit Deinem Google+-Konto. Abmelden / Ändern )

Verbinde mit %s