News
Perth, Australia
+ (61) 417713124
prosolutions@gavinsoorma.com

Minimal downtime database upgrade from Oracle 10g to Oracle 12c

  • Posted by Gavin Soorma
  • On December 8, 2014
  • 0 Comments

This note describes the procedure of migrating as well as upgrading an Oracle 10g Release 2 database to Oracle 12c (12.1.0.2) using a combination of RMAN Incremental backups and the new Oracle 12c command line parallel upgrade utility.

This method minimises the outage required for the migration as well as database upgrade and the outage is limited to the time it takes to backup the last set of archive log files generated since the last Level 1 incremental backup and the time taken to run the catupgrd.sql upgrade script (which can now be run from the command line in parallel).

In this example we are moving a database from one Linux host 5.3 to another Linux 5.7 host, moving from ASM to non-ASM as well as upgrading the database.

Let us take a look at the steps involved.

Copy the new 12c Pre-Upgrade scripts to the Oracle 10g database server and execute it in the Oracle 10.2.0.5 database

[oracle@cls18 ~]$ mkdir preupgrade
[oracle@cls18 ~]$ cd preupgrade/
[oracle@cls18 preupgrade]$ scp -rp oracle@198.168.82.211:/app/oracle/product/12.1.0.2/dbhome_1/preupgrade/* .
oracle@198.168.82.211's password:
emremove.sql	100%   19KB  19.2KB/s   00:00
preupgrd.sql 	100%   14KB  13.8KB/s   00:00
utluppkg.sql	100%  484KB 483.9KB/s   00:00


[oracle@cls18 preupgrade]$ sqlplus sys as sysdba

SQL*Plus: Release 10.2.0.5.0 - Production on Mon Dec 1 09:25:44 2014

Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.

Enter password:

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options

SQL> @preupgrd.sql

Loading Pre-Upgrade Package...


***************************************************************************
Executing Pre-Upgrade Checks in DEV18...
***************************************************************************


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

                  ====>> ERRORS FOUND for DEV18 <<====

 The following are *** ERROR LEVEL CONDITIONS *** that must be addressed
                    prior to attempting your upgrade.
            Failure to do so will result in a failed upgrade.


 1) Check Tag:    COMPATIBLE_PARAMETER
    Check Summary: Verify compatible parameter value is valid
    Fixup Summary:
     ""compatible" parameter must be increased manually prior to upgrade."
    +++ Source Database Manual Action Required +++


 2) Check Tag:    PURGE_RECYCLEBIN
    Check Summary: Check that recycle bin is empty prior to upgrade
    Fixup Summary:
     "The recycle bin will be purged."

           You MUST resolve the above errors prior to upgrade

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

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

              ====>> PRE-UPGRADE RESULTS for DEV18 <<====

ACTIONS REQUIRED:

1. Review results of the pre-upgrade checks:
 /app/DEV18/product/product/10.2.0/db_1/cfgtoollogs/DEV18/preupgrade/preupgrade.log

2. Execute in the SOURCE environment BEFORE upgrade:
 /app/DEV18/product/product/10.2.0/db_1/cfgtoollogs/DEV18/preupgrade/preupgrade_fixups.sql

3. Execute in the NEW environment AFTER upgrade:
 /app/DEV18/product/product/10.2.0/db_1/cfgtoollogs/DEV18/preupgrade/postupgrade_fixups.sql

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

***************************************************************************
Pre-Upgrade Checks in DEV18 Completed.
***************************************************************************

***************************************************************************
***************************************************************************
SQL>

Review the preupgrade.log file


[oracle@cls18 preupgrade]$ cat /app/DEV18/product/product/10.2.0/db_1/cfgtoollogs/DEV18/preupgrade/preupgrade.log
Oracle Database Pre-Upgrade Information Tool 12-01-2014 09:26:09
Script Version: 12.1.0.2.0 Build: 006
**********************************************************************
   Database Name:  DEV18
  Container Name:  Not Applicable in Pre-12.1 database
    Container ID:  Not Applicable in Pre-12.1 database
         Version:  10.2.0.5.0
      Compatible:  10.2.0.3.0
       Blocksize:  8192
        Platform:  Linux x86 64-bit
   Timezone file:  V14
**********************************************************************
                           [Update parameters]

--> If Target Oracle is 64-bit, refer here for Update Parameters:
WARNING: --> "sga_target" needs to be increased to at least 650117120
**********************************************************************
**********************************************************************
                          [Renamed Parameters]
                     [No Renamed Parameters in use]
**********************************************************************
**********************************************************************
                    [Obsolete/Deprecated Parameters]
--> background_dump_dest         11.1       DESUPPORTED  replaced by  "diagnostic_dest"
--> user_dump_dest               11.1       DESUPPORTED  replaced by  "diagnostic_dest"

        [Changes required in Oracle Database init.ora or spfile]

**********************************************************************
                            [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]  VALID
--> Oracle Workspace Manager               [upgrade]  VALID
--> OLAP Analytic Workspace                [upgrade]  VALID
--> Oracle Enterprise Manager Repository   [upgrade]  VALID
--> Oracle Text                            [upgrade]  VALID
--> Oracle XML Database                    [upgrade]  VALID
--> Oracle Java Packages                   [upgrade]  VALID
--> Oracle Multimedia                      [upgrade]  VALID
--> Oracle Spatial                         [upgrade]  VALID
--> Data Mining                            [upgrade]  VALID
--> Expression Filter                      [upgrade]  VALID
--> Rule Manager                           [upgrade]  VALID
--> Oracle OLAP API                        [upgrade]  VALID
**********************************************************************


                              [Tablespaces]
**********************************************************************
--> SYSTEM tablespace is adequate for the upgrade.
     minimum required size: 1259 MB
--> UNDOTBS1 tablespace is adequate for the upgrade.
     minimum required size: 400 MB
--> SYSAUX tablespace is adequate for the upgrade.
     minimum required size: 1552 MB
--> TEMP tablespace is adequate for the upgrade.
     minimum required size: 60 MB

                      [No adjustments recommended]

**********************************************************************
**********************************************************************
                          [Pre-Upgrade Checks]
**********************************************************************
ERROR: --> Compatible set too low

     "compatible" currently set at 10.2.0.3.0 and must
     be set to at least 11.0.0 prior to upgrading the database.
     Do not make this change until you are ready to upgrade
     because a downgrade back to 10.2 is not possible once compatible
     has been raised.

     Update your init.ora or spfile to make this change.

WARNING: --> "ORACLE_OCM" user found in database

     This is an internal account used by Oracle Configuration Manager.
     Please drop this user prior to upgrading.

WARNING: --> Enterprise Manager Database Control repository found in the database

     In Oracle Database 12c, Database Control is removed during
     the upgrade. To save time during the Upgrade, this action
     can be done prior to upgrading using the following steps after
     copying rdbms/admin/emremove.sql from the new Oracle home
   - Stop EM Database Control:
    $> emctl stop dbconsole

   - Connect to the Database using the SYS account AS SYSDBA:

   SET ECHO ON;
   SET SERVEROUTPUT ON;
   @emremove.sql
     Without the set echo and serveroutput commands you will not
     be able to follow the progress of the script.

WARNING: --> "DMSYS" schema exists in the database

     The DMSYS schema (Oracle Data Mining) will be removed
     from the database during the database upgrade.
     All data in DMSYS will be preserved under the SYS schema.
     Refer to the Oracle Data Mining User's Guide for details.

WARNING: --> 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.

INFORMATION: --> OLAP Catalog(AMD) exists in database

     Starting with Oracle Database 12c, OLAP Catalog component is desupported.
     If you are not using the OLAP Catalog component and want
     to remove it, then execute the
     ORACLE_HOME/olap/admin/catnoamd.sql script before or
     after the upgrade.

INFORMATION: --> Older Timezone in use

     Database is using a time zone file older than version 18.
     After the upgrade, it is recommended that DBMS_DST package
     be used to upgrade the 10.2.0.5.0 database time zone version
     to the latest version which comes with the new release.
     Please refer to My Oracle Support note number 977512.1 for details.

ERROR: --> RECYCLE_BIN not empty.
     Your recycle bin contains 56 object(s).
     It is REQUIRED that the recycle bin is empty prior to upgrading.
     Immediately before performing the upgrade, execute the following
     command:
       EXECUTE dbms_preup.purge_recyclebin_fixup;

WARNING: --> Existing schemas with network ACLs exist

     Database contains schemas with objects dependent on network packages.
     Refer to the Upgrade Guide for instructions to configure Network ACLs.
     USER UTILITY has dependent objects.
     USER DBAMON has dependent objects.
     USER ORACLE_OCM has dependent objects.

INFORMATION: --> There are existing Oracle components that will NOT be
     upgraded by the database upgrade script.  Typically, such components
     have their own upgrade scripts, are deprecated, or obsolete.
     Those components are:  OLAP Catalog


**********************************************************************
                      [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  ************

 2 ERRORS exist that must be addressed prior to performing your upgrade.
 5 WARNINGS that Oracle suggests are addressed to improve database performance.
 3 INFORMATIONAL messages that should be reviewed prior to your upgrade.

 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
                   ***********************************

In the Oracle 10g database execute the Pre-Upgrade fixup scripts

SQL>   EXECUTE dbms_preup.purge_recyclebin_fixup;

PL/SQL procedure successfully completed.

SQL> EXECUTE dbms_stats.gather_dictionary_stats;

PL/SQL procedure successfully completed.


SQL> SET ECHO ON;
SQL> SET SERVEROUTPUT ON;
SQL> @emremove.sql

.....

Dropping synonym : MGMT$TARGET_PROPERTIES ...
Dropping synonym : MGMT$TARGET_TYPE ...
Finished phase 5
Starting phase 6 : Dropping Oracle Enterprise Manager related other roles ...
Finished phase 6
The Oracle Enterprise Manager related schemas and objects are dropped.
Do the manual steps to studown the DB Control if not done before running this
script and then delete the DB Control configuration files

PL/SQL procedure successfully completed.

Take a level 0 RMAN Incremental backup

RMAN> run {
2> allocate channel c1 type disk;
3> backup incremental level 0 database format '/app/DEV18/oradump/bkp_lev0.%U';
4> release channel c1;
5> }

released channel: ORA_SBT_TAPE_1
released channel: ORA_SBT_TAPE_2
released channel: ORA_SBT_TAPE_3
released channel: ORA_SBT_TAPE_4
released channel: ORA_SBT_TAPE_5
allocated channel: c1
channel c1: sid=266 instance=DEV181 devtype=DISK

Starting backup at 01-DEC-14
channel c1: starting incremental level 0 datafile backupset
channel c1: specifying datafile(s) in backupset
input datafile fno=00010 name=+DEV18_DATA_01/DEV18/datafile/utility_data.266.798291365
input datafile fno=00003 name=+DEV18_DATA_01/DEV18/datafile/sysaux.257.747180651
...
...

input datafile fno=00015 name=+DEV18_DATA_01/DEV18/datafile/rpo_data.276.798291365
input datafile fno=00020 name=+DEV18_DATA_01/DEV18/datafile/dw_data.282.821809927
channel c1: starting piece 1 at 01-DEC-14
channel c1: finished piece 1 at 01-DEC-14
piece handle=/app/DEV18/oradump/bkp_lev0.8ppp2gus_1_1 tag=TAG20141201T094147 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:25
Finished backup at 01-DEC-14

Starting Control File and SPFILE Autobackup at 01-DEC-14
piece handle=/app/DEV18/product/product/10.2.0/db_1/dbs/c-1508569629-20141201-00 comment=NONE
Finished Control File and SPFILE Autobackup at 01-DEC-14

released channel: c1

Copy the password file and init.ora from 10g source to Oracle 12c target database environment

oracle@cls18 dbs]$ scp -rp orapwDEV181 oracle@198.168.82.211:/app/oracle/product/12.1.0/dbhome_1/dbs
oracle@198.168.82.211's password:
orapwDEV181  100% 1536     1.5KB/s   00:00

 [oracle@cls18 dbs]$ scp -rp initDEV181.ora oracle@198.168.82.211:/app/oracle/product/12.1.0/dbhome_1/dbs
oracle@198.168.82.211's password:
initDEV181.ora            

Edit the init.ora parameter file in the 12c database environment and make changes like ….

remove deprecated 10g parameters like background and core dumps

change compatible

add diagnostic_dest

change control file location as in this example we are moving from ASM source on 10g to non-ASM target in 12c

add db_file_name_convert and log_file_name_convert

In the Oracle 12c environment start the instance in NOMOUNT state

[oracle@vosap02 dbs]$ export ORACLE_SID=DEV181
[oracle@vosap02 dbs]$ sqlplus sys as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Sun Nov 30 23:08:13 2014

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Enter password:
Connected to an idle instance.

SQL> startup nomount;
ORACLE instance started.

Total System Global Area  880803840 bytes
Fixed Size                  2930416 bytes
Variable Size             469764368 bytes
Database Buffers          402653184 bytes
Redo Buffers                5455872 bytes

Restore control file backup


[oracle@vosap02 backup]$ pwd
/app/oracle/oradata/DEV181/backup
[oracle@vosap02 backup]$ ls
bkp_lev0.8ppp2gus_1_1  c-1508569629-20141201-00


[oracle@vosap02 backup]$ rman target /

Recovery Manager: Release 12.1.0.2.0 - Production on Sun Nov 30 23:14:52 2014

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

connected to target database: DEV18 (not mounted)

RMAN> restore controlfile from '/app/oracle/oradata/DEV181/backup/c-1508569629-20141201-00';

Starting restore at 30-NOV-14
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=12 device type=DISK

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/app/oracle/oradata/DEV181/control01.ctl
output file name=/app/oracle/oradata/DEV181/control02.ctl
output file name=/app/oracle/oradata/DEV181/control03.ctl
Finished restore at 30-NOV-14


RMAN> alter database mount;

Statement processed
released channel: ORA_DISK_1




RMAN> catalog start with '/app/oracle/oradata/DEV181/backup';

searching for all files that match the pattern /app/oracle/oradata/DEV181/backup

List of Files Unknown to the Database
=====================================
File Name: /app/oracle/oradata/DEV181/backup/bkp_lev0.8ppp2gus_1_1
File Name: /app/oracle/oradata/DEV181/backup/c-1508569629-20141201-00

Do you really want to catalog the above files (enter YES or NO)? YES
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /app/oracle/oradata/DEV181/backup/bkp_lev0.8ppp2gus_1_1
File Name: /app/oracle/oradata/DEV181/backup/c-1508569629-20141201-00Regards,

Restore Level 0 backup

In this example we are changing the data file names at the database level via the SET NEWNAME FOR DATABASE command.

We can also change the data file names at the individual data file level using the SET NEWNAME FOR DATAFILE command

RMAN>  run {
2> allocate channel c1 type disk;
3> allocate channel c2 type disk;
4> SET NEWNAME FOR DATABASE   TO  '/app/oracle/oradata/DEV181/%b';
5> SET NEWNAME FOR tempfile  1 TO  '/app/oracle/oradata/DEV181/%b';
6> restore database;
7> switch datafile all;
8> switch tempfile all;
9> release channel c1;
10> release channel c2;
11> }

allocated channel: c1
channel c1: SID=12 device type=DISK

allocated channel: c2
channel c2: SID=249 device type=DISK

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 30-NOV-14

channel c1: starting datafile backup set restore
channel c1: specifying datafile(s) to restore from backup set
channel c1: restoring datafile 00001 to /app/oracle/oradata/DEV181/system.256.747180649
channel c1: restoring datafile 00002 to /app/oracle/oradata/DEV181/undotbs1.258.747180651
channel c1: restoring datafile 00003 to /app/oracle/oradata/DEV181/sysaux.257.747180651
...
...

channel c1: reading from backup piece /app/DEV18/oradump/bkp_lev0.8ppp2gus_1_1
channel c1: errors found reading piece handle=/app/DEV18/oradump/bkp_lev0.8ppp2gus_1_1
channel c1: failover to piece handle=/app/oracle/oradata/DEV181/backup/bkp_lev0.8ppp2gus_1_1 tag=TAG20141201T094147
channel c1: restored backup piece 1
channel c1: restore complete, elapsed time: 00:00:15
Finished restore at 30-NOV-14

datafile 1 switched to datafile copy
input datafile copy RECID=18 STAMP=865034590 file name=/app/oracle/oradata/DEV181/system.256.747180649
datafile 2 switched to datafile copy
input datafile copy RECID=19 STAMP=865034590 file name=/app/oracle/oradata/DEV181/undotbs1.258.747180651
datafile 3 switched to datafile copy
input datafile copy RECID=20 STAMP=865034591 file name=/app/oracle/oradata/DEV181/sysaux.257.747180651
datafile 4 switched to datafile copy
input datafile copy RECID=21 STAMP=865034591 file name=/app/oracle/oradata/DEV181/users.259.747180651
...
...
...
input datafile copy RECID=33 STAMP=865034591 file name=/app/oracle/oradata/DEV181/dw_data.282.821809927
datafile 21 switched to datafile copy
input datafile copy RECID=34 STAMP=865034591 file name=/app/oracle/oradata/DEV181/gg_data.283.838134609

renamed tempfile 1 to /app/oracle/oradata/DEV181/temp.264.747180711 in control file
renamed tempfile 2 to /app/oracle/oradata/DEV181/temp_others.281.798291417 in control file

released channel: c1

released channel: c2

Take a level 0 RMAN Incremental backup

At this stage there is no application outage and to simulate that we make some changes in the source 10g database


SQL> create table system.myobjects_1
  2  tablespace users as select * from dba_objects;

Table created.

[oracle@cls18 oradump]$ rman target /

Recovery Manager: Release 10.2.0.5.0 – Production on Mon Dec 1 12:29:49 2014

Copyright (c) 1982, 2007, Oracle. All rights reserved.

connected to target database: DEV18 (DBID=1508569629)

RMAN> run {
allocate channel c1 type disk;
backup incremental level 1 database format ‘/app/DEV18/oradump/bkp_lev1.%U’;
release channel c1;
}
2> 3> 4> 5>
using target database control file instead of recovery catalog
allocated channel: c1
channel c1: sid=238 instance=DEV181 devtype=DISK

Starting backup at 01-DEC-14
channel c1: starting incremental level 1 datafile backupset
channel c1: specifying datafile(s) in backupset
input datafile fno=00010 name=+DEV18_DATA_01/DEV18/datafile/utility_data.266.798291365
input datafile fno=00003 name=+DEV18_DATA_01/DEV18/datafile/sysaux.257.747180651

input datafile fno=00020 name=+DEV18_DATA_01/DEV18/datafile/dw_data.282.821809927
channel c1: starting piece 1 at 01-DEC-14
channel c1: finished piece 1 at 01-DEC-14
piece handle=/app/DEV18/oradump/bkp_lev1.8rpp2qri_1_1 tag=TAG20141201T123040 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:07
Finished backup at 01-DEC-14

Starting Control File and SPFILE Autobackup at 01-DEC-14
piece handle=/app/DEV18/product/product/10.2.0/db_1/dbs/c-1508569629-20141201-01 comment=NONE
Finished Control File and SPFILE Autobackup at 01-DEC-14

released channel: c1

Copy the level 1 incremental backup to target and register

[oracle@cls18 oradump]$ scp -rp bkp_lev1.8rpp2qri_1_1 oracle@198.168.82.211:/app/oracle/oradata/DEV181/backup                   oracle@198.168.82.211's password:
bkp_lev1.8rpp2qri_1_1   100%  108MB  18.0MB/s   00:06



RMAN> catalog start with '/app/oracle/oradata/DEV181/backup';

searching for all files that match the pattern /app/oracle/oradata/DEV181/backup

List of Files Unknown to the Database
=====================================
File Name: /app/oracle/oradata/DEV181/backup/bkp_lev1.8rpp2qri_1_1

Do you really want to catalog the above files (enter YES or NO)? YES
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /app/oracle/oradata/DEV181/backup/bkp_lev1.8rpp2qri_1_1RMAN>

Recover the database – note it will fail when it tries to apply a non-existent archive log file

RMAN> run {
2> allocate channel c1 type disk;
3> recover database;
4> }

allocated channel: c1
channel c1: SID=12 device type=DISK

Starting recover at 30-NOV-14
channel c1: starting incremental datafile backup set restore
channel c1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00001: /app/oracle/oradata/DEV181/system.256.747180649
destination for restore of datafile 00002: /app/oracle/oradata/DEV181/undotbs1.258.747180651
destination for restore of datafile 00003: /app/oracle/oradata/DEV181/sysaux.257.747180651…
...
...
...
destination for restore of datafile 00021: /app/oracle/oradata/DEV181/gg_data.283.838134609
channel c1: reading from backup piece /app/oracle/oradata/DEV181/backup/bkp_lev1.8rpp2qri_1_1
channel c1: piece handle=/app/oracle/oradata/DEV181/backup/bkp_lev1.8rpp2qri_1_1 tag=TAG20141201T123040
channel c1: restored backup piece 1
channel c1: restore complete, elapsed time: 00:00:03

starting media recovery

unable to find archived log
archived log thread=1 sequence=53490
released channel: c1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 11/30/2014 23:36:09
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 53490 and starting SCN of 5982863583825

RMAN>

Let us make some more changes in the database – perform a final archive logfile switch to ensure all changes written to disk from log buffer cache

OUTAGE STARTS NOW!

SQL> create table system.myobjects_2
  2  tablespace users as select * from dba_objects;

Table created.

SQL> alter system switch logfile;

System altered

Take a backup of the archive log files generated since the last Level 1 incremental backup using the command

BACKUP ARCHIVELOG ALL NOT BACKED UP 1 TIMES

Copy the archivelog backup to target and register


[oracle@cls18 oradump]$ scp -rp bkp_arch.8tpp2rb6_1_1 oracle@198.168.82.211:/app/oracle/oradata/DEV181/backup
oracle@198.168.82.211's password:
bkp_arch.8tpp2rb6_1_1   100% 2101MB  19.3MB/s   01:49


RMAN> catalog start with '/app/oracle/oradata/DEV181/backup';

searching for all files that match the pattern /app/oracle/oradata/DEV181/backup

List of Files Unknown to the Database
=====================================
File Name: /app/oracle/oradata/DEV181/backup/bkp_arch.8tpp2rb6_1_1

Do you really want to catalog the above files (enter YES or NO)? YES
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /app/oracle/oradata/DEV181/backup/bkp_arch.8tpp2rb6_1_1

Run a LIST BACKUP OF ARCHIVELOG command and note the last archive log file which has been backed up


RMAN> list backup of archivelog all;

...
...
...

  1    53495   5982863638922 01-DEC-14 5982863650457 01-DEC-14
  1    53496   5982863650457 01-DEC-14 5982863662947 01-DEC-14
  1    53497   5982863662947 01-DEC-14 5982863673387 01-DEC-14
  1    53498   5982863673387 01-DEC-14 5982863674424 01-DEC-14
  1    53499   5982863674424 01-DEC-14 5982863674684 01-DEC-14

Recover the database until the sequence number noted above + 1 (53499+1)


oracle@vosap02 backup]$ rman target /

Recovery Manager: Release 12.1.0.2.0 - Production on Sun Nov 30 23:44:43 2014

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

connected to target database: DEV18 (DBID=1508569629, not open)

RMAN> run {
2> allocate channel c1 type disk;
3> set until sequence 53500 thread 1;
4> recover database;
5> }

using target database control file instead of recovery catalog
allocated channel: c1
channel c1: SID=12 device type=DISK

executing command: SET until clause

Starting recover at 30-NOV-14

starting media recovery

channel c1: starting archived log restore to default destination
channel c1: restoring archived log
archived log thread=1 sequence=53490
channel c1: restoring archived log
...
...

channel c1: restoring archived log
archived log thread=1 sequence=53498
channel c1: restoring archived log
archived log thread=1 sequence=53499
channel c1: reading from backup piece /app/oracle/oradata/DEV181/backup/bkp_arch.8tpp2rb6_1_1
channel c1: piece handle=/app/oracle/oradata/DEV181/backup/bkp_arch.8tpp2rb6_1_1 tag=TAG20141201T123859
channel c1: restored backup piece 1
channel c1: restore complete, elapsed time: 00:00:03
archived log file name=/app/oracle/oradata/DEV181/arch/1_53490_747180704.log thread=1 sequence=53490
archived log file name=/app/oracle/oradata/DEV181/arch/1_53491_747180704.log thread=1 sequence=53491
archived log file name=/app/oracle/oradata/DEV181/arch/1_53492_747180704.log thread=1 sequence=53492
archived log file name=/app/oracle/oradata/DEV181/arch/1_53493_747180704.log thread=1 sequence=53493
archived log file name=/app/oracle/oradata/DEV181/arch/1_53494_747180704.log thread=1 sequence=53494
archived log file name=/app/oracle/oradata/DEV181/arch/1_53495_747180704.log thread=1 sequence=53495
archived log file name=/app/oracle/oradata/DEV181/arch/1_53496_747180704.log thread=1 sequence=53496
archived log file name=/app/oracle/oradata/DEV181/arch/1_53497_747180704.log thread=1 sequence=53497
archived log file name=/app/oracle/oradata/DEV181/arch/1_53498_747180704.log thread=1 sequence=53498
archived log file name=/app/oracle/oradata/DEV181/arch/1_53499_747180704.log thread=1 sequence=53499
media recovery complete, elapsed time: 00:00:02
Finished recover at 30-NOV-14
released channel: c1

Open the database with RESETLOGS UPGRADE

Ignore the error related to block change tracking – we will disable that.


RMAN> alter database open resetlogs upgrade;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of sql statement command at 11/30/2014 23:47:30
ORA-19751: could not create the change tracking file
ORA-19750: change tracking file: '+DEV18_DATA_01/DEV18/changetracking/ctf.278.864472519'
ORA-17502: ksfdcre:1 Failed to create file +DEV18_DATA_01/DEV18/changetracking/ctf.278.864472519
ORA-17501: logical block size 4294967295 is invalid
ORA-29701: unable to connect to Cluster Synchronization Service
ORA-17503: ksfdopn:2 Failed to open file +DEV18_DATA_01/DEV18/changetracking/ctf.278.864472519
ORA-15001: diskgroup "DEV18_DATA_01" does not exist or


SQL> alter database disable block change tracking;

Database altered.

Shut down the database and open it in the Oracle 12c environment in STARTUP UPGRADE mode

[oracle@vosap02 backup]$ sqlplus sys as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Mon Dec 1 02:08:11 2014

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Enter password:

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> shutdown immediate;
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.


SQL> startup upgrade;
ORACLE instance started.

Total System Global Area  880803840 bytes
Fixed Size                  2930416 bytes
Variable Size             469764368 bytes
Database Buffers          402653184 bytes
Redo Buffers                5455872 bytes
Database mounted.
Database opened.

Execute the catctl.pl perl script which will call the catupgrd.sql in parallel mode


[oracle@vosap02 oracle]$ cd $ORACLE_HOME/rdbms/admin


 $ORACLE_HOME/perl/bin/perl catctl.pl -n 4 -l /app/oracle catupgrd.sql

[oracle@vosap02 admin]$ $ORACLE_HOME/perl/bin/perl catctl.pl -n 4 -l /app/oracle catupgrd.sql

Argument list for [catctl.pl]
SQL Process Count     n = 4
SQL PDB Process Count N = 0
Input Directory       d = 0
Phase Logging Table   t = 0
Log Dir               l = /app/oracle
Script                s = 0
Serial Run            S = 0
Upgrade Mode active   M = 0
Start Phase           p = 0
End Phase             P = 0
Log Id                i = 0
Run in                c = 0
Do not run in         C = 0
Echo OFF              e = 1
No Post Upgrade       x = 0
Reverse Order         r = 0
Open Mode Normal      o = 0
Debug catcon.pm       z = 0
Debug catctl.pl       Z = 0
Display Phases        y = 0
Child Process         I = 0

catctl.pl version: 12.1.0.2.0
Oracle Base           = /app/oracle

Analyzing file catupgrd.sql
Log files in /app/oracle
catcon: ALL catcon-related output will be written to /app/oracle/catupgrd_catcon_26248.lst
catcon: See /app/oracle/catupgrd*.log files for output generated by scripts
catcon: See /app/oracle/catupgrd_*.lst files for spool files, if any
Number of Cpus        = 2
SQL Process Count     = 4

------------------------------------------------------
Phases [0-73]
Serial   Phase #: 0 Files: 1     Time: 138s
Serial   Phase #: 1 Files: 5     Time: 31s
Restart  Phase #: 2 Files: 1     Time: 0s
Parallel Phase #: 3 Files: 18    Time: 9s
Restart  Phase #: 4 Files: 1     Time: 0s
Serial   Phase #: 5 Files: 5     Time: 14s
Serial   Phase #: 6 Files: 1     Time: 10s
Serial   Phase #: 7 Files: 4     Time: 5s
Restart  Phase #: 8 Files: 1     Time: 1s
Parallel Phase #: 9 Files: 62    Time: 26s
Restart  Phase #:10 Files: 1     Time: 0s
Serial   Phase #:11 Files: 1     Time: 10s
Restart  Phase #:12 Files: 1     Time: 0s
Parallel Phase #:13 Files: 91    Time: 9s
Restart  Phase #:14 Files: 1     Time: 0s
Parallel Phase #:15 Files: 111   Time: 14s
Restart  Phase #:16 Files: 1     Time: 0s
Serial   Phase #:17 Files: 3     Time: 0s
Restart  Phase #:18 Files: 1     Time: 0s
Parallel Phase #:19 Files: 32    Time: 19s
Restart  Phase #:20 Files: 1     Time: 0s
Serial   Phase #:21 Files: 3     Time: 5s
Restart  Phase #:22 Files: 1     Time: 0s
Parallel Phase #:23 Files: 23    Time: 69s
Restart  Phase #:24 Files: 1     Time: 0s
Parallel Phase #:25 Files: 11    Time: 39s
Restart  Phase #:26 Files: 1     Time: 0s
Serial   Phase #:27 Files: 1     Time: 0s
Restart  Phase #:28 Files: 1     Time: 0s
Serial   Phase #:30 Files: 1     Time: 0s
Serial   Phase #:31 Files: 257   Time: 15s
Serial   Phase #:32 Files: 1     Time: 0s
Restart  Phase #:33 Files: 1     Time: 0s
Serial   Phase #:34 Files: 1     Time: 3s
Restart  Phase #:35 Files: 1     Time: 0s
Restart  Phase #:36 Files: 1     Time: 0s
Serial   Phase #:37 Files: 4     Time: 44s
Restart  Phase #:38 Files: 1     Time: 0s
Parallel Phase #:39 Files: 13    Time: 50s
Restart  Phase #:40 Files: 1     Time: 0s
Parallel Phase #:41 Files: 10    Time: 6s
Restart  Phase #:42 Files: 1     Time: 0s
Serial   Phase #:43 Files: 1     Time: 6s
Restart  Phase #:44 Files: 1     Time: 0s
Serial   Phase #:45 Files: 1     Time: 39s
Serial   Phase #:46 Files: 1     Time: 0s
Restart  Phase #:47 Files: 1     Time: 0s
Serial   Phase #:48 Files: 1     Time: 99s
Restart  Phase #:49 Files: 1     Time: 0s
Serial   Phase #:50 Files: 1     Time: 113s
Restart  Phase #:51 Files: 1     Time: 0s
Serial   Phase #:52 Files: 1     Time: 21s
Restart  Phase #:53 Files: 1     Time: 0s
Serial   Phase #:54 Files: 1     Time: 162s
Restart  Phase #:55 Files: 1     Time: 0s
Serial   Phase #:56 Files: 1     Time: 48s
Restart  Phase #:57 Files: 1     Time: 0s
Serial   Phase #:58 Files: 1     Time: 154s
Restart  Phase #:59 Files: 1     Time: 0s
Serial   Phase #:60 Files: 1     Time: 242s
Restart  Phase #:61 Files: 1     Time: 0s
Serial   Phase #:62 Files: 1     Time: 36s
Restart  Phase #:63 Files: 1     Time: 0s
Serial   Phase #:64 Files: 1     Time: 2s
Serial   Phase #:65 Files: 1 Calling sqlpatch with LD_LIBRARY_PATH=/app/oracle/product/12.1.0/dbhome_1/lib; export LD_LIBRARY_PATH;/app/oracle/product/12.1.0/dbhome_1/perl/bin/perl -I /app/oracle/product/12.1.0/dbhome_1/rdbms/admin -I /app/oracle/product/12.1.0/dbhome_1/rdbms/admin/../../sqlpatch /app/oracle/product/12.1.0/dbhome_1/rdbms/admin/../../sqlpatch/sqlpatch.pl -verbose -upgrade_mode_only > /app/oracle/catupgrd_datapatch_upgrade.log 2> /app/oracle/catupgrd_datapatch_upgrade.err
returned from sqlpatch
    Time: 21s
Serial   Phase #:66 Files: 1     Time: 31s
Serial   Phase #:68 Files: 1     Time: 0s
Serial   Phase #:69 Files: 1 Calling sqlpatch with LD_LIBRARY_PATH=/app/oracle/product/12.1.0/dbhome_1/lib; export LD_LIBRARY_PATH;/app/oracle/product/12.1.0/dbhome_1/perl/bin/perl -I /app/oracle/product/12.1.0/dbhome_1/rdbms/admin -I /app/oracle/product/12.1.0/dbhome_1/rdbms/admin/../../sqlpatch /app/oracle/product/12.1.0/dbhome_1/rdbms/admin/../../sqlpatch/sqlpatch.pl -verbose > /app/oracle/catupgrd_datapatch_normal.log 2> /app/oracle/catupgrd_datapatch_normal.err
returned from sqlpatch
    Time: 32s
Serial   Phase #:70 Files: 1     Time: 73s
Serial   Phase #:71 Files: 1     Time: 0s
Serial   Phase #:72 Files: 1     Time: 0s
Serial   Phase #:73 Files: 1     Time: 21s

Grand Total Time: 1620s

LOG FILES: (catupgrd*.log)

Upgrade Summary Report Located in:
/app/oracle/product/12.1.0/dbhome_1/cfgtoollogs/DEV18/upgrade/upg_summary.log

Grand Total Upgrade Time:    [0d:0h:27m:0s]

Review the upgrade summary log file


[oracle@vosap02 oracle]$ cat /app/oracle/product/12.1.0/dbhome_1/cfgtoollogs/DEV18/upgrade/upg_summary.log


Oracle Database 12.1 Post-Upgrade Status Tool           12-01-2014 02:37:12

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

Oracle Server                          UPGRADED      12.1.0.2.0  00:09:22
JServer JAVA Virtual Machine              VALID      12.1.0.2.0  00:01:38
Oracle Real Application Clusters     OPTION OFF      12.1.0.2.0  00:00:02
Oracle Workspace Manager                  VALID      12.1.0.2.0  00:00:34
OLAP Analytic Workspace                   VALID      12.1.0.2.0  00:00:19
OLAP Catalog                         OPTION OFF      10.2.0.5.0  00:00:00
Oracle OLAP API                           VALID      12.1.0.2.0  00:00:22
Oracle XDK                                VALID      12.1.0.2.0  00:01:52
Oracle Text                               VALID      12.1.0.2.0  00:00:37
Oracle XML Database                       VALID      12.1.0.2.0  00:02:05
Oracle Database Java Packages             VALID      12.1.0.2.0  00:00:10
Oracle Multimedia                         VALID      12.1.0.2.0  00:02:33
Spatial                                UPGRADED      12.1.0.2.0  00:04:01
Final Actions                                                    00:00:50
Post Upgrade                                                     00:01:09

Total Upgrade Time: 00:25:57

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.12
Grand Total Upgrade Time:    [0d:0h:27m:0s]

Run post-upgrade steps

Upgrade timezone data to version 18

SQL> startup upgrade
ORACLE instance started.

Total System Global Area  880803840 bytes
Fixed Size                  2930416 bytes
Variable Size             469764368 bytes
Database Buffers          402653184 bytes
Redo Buffers                5455872 bytes
Database mounted.
Database opened.
SQL> SELECT * FROM v$timezone_file;

FILENAME                VERSION     CON_ID
-------------------- ---------- ----------
timezlrg_14.dat              14          0

SQL> EXEC DBMS_DST.BEGIN_UPGRADE(18);

PL/SQL procedure successfully completed.

SQL> SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME LIKE 'DST_%'
ORDER BY PROPERTY_NAME;  2    3    4

PROPERTY_NAME
--------------------------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------
DST_PRIMARY_TT_VERSION
18

DST_SECONDARY_TT_VERSION
14

DST_UPGRADE_STATE
UPGRADE


SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup;
ORACLE instance started.

Total System Global Area  880803840 bytes
Fixed Size                  2930416 bytes
Variable Size             469764368 bytes
Database Buffers          402653184 bytes
Redo Buffers                5455872 bytes
Database mounted.
Database opened.
SQL> set serverout on
SQL> VAR numfail number
BEGIN
DBMS_DST.UPGRADE_DATABASE(:numfail,
parallel => TRUE,
log_errors => TRUE,
log_errors_table => 'SYS.DST$ERROR_TABLE',
log_triggers_table => 'SYS.DST$TRIGGER_TABLE',
error_on_overlap_time => FALSE,
error_on_nonexisting_time => FALSE);
DBMS_OUTPUT.PUT_LINE('Failures:'|| :numfail);
END;
/SQL>   2    3    4    5    6    7    8    9   10   11
Table list: "ROSODS"."STS_TEST"
Number of failures: 0
Table list: "RH_SCHEDULE"."SCHED_TRAIN_LOCATION"
Number of failures: 0
Table list: "RH_SCHEDULE"."SCHED_RPO"
Number of failures: 0
Table list: "RH_SCHEDULE"."SCHED_MASTER_TRAIN_BUNDLE"
Number of failures: 0
Table list: "RH_SCHEDULE"."SCHED_MASTER_TRAIN"
Number of failures: 0
Table list: "RH_SCHEDULE"."SCHED_CHANGE_REASON"
Number of failures: 0
Table list: "STG_OWNER"."STG_TRAIN_SCHED_RPO"
Number of failures: 0
Table list: "STG_OWNER"."STG_TRAIN_SCHEDULE_LOCATION"
Number of failures: 0
Table list: "STG_OWNER"."STG_TRAIN_SCHEDULE"
Number of failures: 0
Table list: "STG_OWNER"."STG_OUT_TRAIN_SCHED_MQ2_EXT"
Number of failures: 0
Table list: "STG_OWNER"."STAGING_WEIGHBRIDGE_VEHICLE"
Number of failures: 0
Table list: "STG_OWNER"."STAGING_WEIGHBRIDGE_READING"
Number of failures: 0
Table list: "STG_OWNER"."STAGING_TRAIN_RUNNING"
Number of failures: 0
Table list: "STG_OWNER"."STAGING_SPEED_RESTRICTION_LOC"
Number of failures: 0
Table list: "STG_OWNER"."STAGING_SPEED_RESTRICTION"
Number of failures: 0
Table list: "STG_OWNER"."STAGING_OUT_VEHICLES_TRIP_EXT"
Number of failures: 0
Table list: "STG_OWNER"."STAGING_LOAD_DUMP"
Number of failures: 0
Table list: "STG_OWNER"."STAGING_CONSIST_TRAIN"
Number of failures: 0
Table list: "STG_OWNER"."STAGING_CONSIST_RAKE"
Number of failures: 0
Table list: "STG_OWNER"."STAGING_CONSIST_AEI_VEHICLE"
Number of failures: 0
Table list: "STG_OWNER"."STAGING_CONSIST"
Number of failures: 0
Table list: "RPO"."STG_RPO_PLANNING_CHANGE"
Number of failures: 0
Table list: "GSMADMIN_INTERNAL"."AQ$_CHANGE_LOG_QUEUE_TABLE_S"
Number of failures: 0
Table list: "GSMADMIN_INTERNAL"."AQ$_CHANGE_LOG_QUEUE_TABLE_L"
Number of failures: 0
Failures:0

PL/SQL procedure successfully completed.

SQL> VAR fail number
BEGIN
DBMS_DST.END_UPGRADE(:fail);
DBMS_OUTPUT.PUT_LINE('Failures:'|| :fail);
END;
/SQL>   2    3    4    5
An upgrade window has been successfully ended.
Failures:0

PL/SQL procedure successfully completed.

SQL> SELECT * FROM v$timezone_file;

FILENAME                VERSION     CON_ID
-------------------- ---------- ----------
timezlrg_18.dat              18          0

Recompile INVALID Objects

Run utlrp followed by utluiobj.sql


SQL> @utluiobj.sql
.
Oracle Database 12.1 Post-Upgrade Invalid Objects Tool 12-01-2014 19:21:50
.
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
.

PL/SQL procedure successfully completed.

A best practice is to gather system statistics 24-48 hours after the upgrade

SQL> exec DBMS_STATS.GATHER_SYSTEM_STATS('start'); 

<< Run it for several hours during periods of normal workload>> 

SQL> exec DBMS_STATS.GATHER_SYSTEM_STATS('stop');
 1

0 Comments

Leave Reply

Your email address will not be published. Required fields are marked *