Migration of an 11gr2 database to 12cr2, using Duplicate with the new feature NOOPEN

1.          INTRODUCTION

The goal of this article is to show you how i have migrated an 11gr2 database to 12cr2, using Duplicate with the new feature NOOPEN.

We assume:

Source 11gr2 : 11.2.0.4 Target 12cr2 MY_DB / SERVER1 MY_DB / SERVER2

 

2.         Check list to do ON Source Database

2.1.     Pre-Upgrade Steps:

2.1.1.  Download preupgrade.jar 

Download the new tool: preupgrade.jar  è MOS 884522.1

Copy it on the source server: for example: /oradata_MY/data/Pre-upgrade/

On the source database, Run the Pre-Upgrade Information Tool for Collecting Pre-Upgrade Information:

ssh oracle@ SERVER1

mkdir  /oradata_MY/data/Pre-upgrade/recommendation $ORACLE_HOME/jdk/bin/java -jar /oradata_MY/data/Pre-upgrade/preupgrade.jar -FILE DIR /oradata_MY/data/Pre-upgrade/recommendation cd /oradata_MY/data/Pre-upgrade/recommandation drwxr-xr-x    3 ora112   dba             256 Oct 30 16:26 .. drwxr-xr-x    3 ora112   dba             256 Oct 30 16:32 oracle -rw-r--r--    1 ora112   dba            5373 Oct 30 16:32 dbms_registry_basic.sql -rw-r--r--    1 ora112   dba           12693 Oct 30 16:32 dbms_registry_extended.sql -rw-r--r--    1 ora112   dba            7027 Oct 30 16:32 preupgrade_driver.sql -rw-r--r--    1 ora112   dba          412333 Oct 30 16:32 preupgrade_package.sql drwxr-xr-x    3 ora112   dba             256 Oct 30 16:32 upgrade -rw-r--r--    1 ora112   dba           62509 Oct 30 16:32 preupgrade_messages.properties drwxr-xr-x    4 ora112   dba            4096 Oct 30 16:34 . -rw-r--r--    1 ora112   dba            9252 Oct 30 16:34 preupgrade_fixups.sql  -rw-r--r--    1 ora112   dba            6892 Oct 30 16:34 postupgrade_fixups.sql -rw-r--r--    1 ora112   dba            9405 Oct 30 16:34 preupgrade.log

preupgrade_fixups.sql   :  to be executed on the source database (11gr2)

postupgrade_fixups.sql  :  to be executed in the target database (12cr2)

2.1.2. Apply preupgrade.jar 

On the source database, Run the Pre-Upgrade Information Tool for Collecting Pre-Upgrade Information:

ssh oracle@ SERVER1

SQL> @preupgrade_fixups.sql Executing Oracle PRE-Upgrade Fixup Script Auto-Generated by:       Oracle Preupgrade Script                          Version: 12.2.0.1.0 Build: 9 Generated on:            2017-10-30 16:34:10 For Source Database:     MY_DB Source Database Version: 11.2.0.3.0 For Upgrade to Version:  12.2.0.1.0                           Fixup Check Name                Status  Further DBA Action ----------                ------  ------------------ purge_recyclebin          Passed  None em_present                Failed  Manual fixup recommended. invalid_objects_exist     Failed  Manual fixup recommended. amd_exists                Failed  Manual fixup recommended. dictionary_stats          Passed  None trgowner_no_admndbtrg     Failed  Manual fixup recommended. apex_upgrade_msg          Failed  Manual fixup recommended. PL/SQL procedure successfully completed. SQL> spool off @?/rdbms/admin/utlrp.sql SQL> SET SERVEROUTPUT ON; SQL> EXECUTE DBMS_PREUP.INVALID_OBJECTS; SYS/SYSTEM INVALID OBJECTS OWNER                         |OBJECT_NAME                                                                    |OBJECT_TYPE  -------------------------------------------------------------------------------------------------------------------------------- NON SYS/SYSTEM INVALID OBJECTS OWNER                         |OBJECT_NAME                                                                    |OBJECT_TYPE  -------------------------------------------------------------------------------------------------------------------------------- ADMIN_COPIE_TEMPLATE_F        AUTO_REPOSITORY                                                                 TRIGGER ADMIN_MERCURY_DIMENSION_DB    AUTO_REPOSITORY                                                                 TRIGGER ADMIN_MERCURY_LOADRUNNER_DB   AUTO_REPOSITORY                                                                 TRIGGER ADMIN_MERCURY_TMPLT_TST_DB    AUTO_REPOSITORY                                                                 TRIGGER ADMIN_TEMPLATE_FINAL_0        AUTO_REPOSITORY                                                                 TRIGGER ADMIN_TEMPLATE_FINAL_D        AUTO_REPOSITORY                                                                 TRIGGER ADMIN_TEST_MIGRATION_Q        AUTO_REPOSITORY                                                                 TRIGGER CP_FIR_STORELINE_DB           AUTO_REPOSITORY                                                                 TRIGGER DEFAULT_REF_DB                AUTO_REPOSITORY                                                                 TRIGGER FIR_ABONNEMENT_DB             AUTO_REPOSITORY                                                                 TRIGGER FIR_ALIZE_DB                  AUTO_REPOSITORY                                                                 TRIGGER FIR_ARGOS_DB                  AUTO_REPOSITORY                                                                 TRIGGER FIR_ARGOS_RECUTIL_DB          AUTO_REPOSITORY                                                                 TRIGGER FIR_BNC_V2_DB                 AUTO_REPOSITORY                                                                 TRIGGER FIR_CADENCIER_PRESTIGE_DB     AUTO_REPOSITORY                                                                 TRIGGER FIR_DIMENSIONS_DB             AUTO_REPOSITORY                                                                 TRIGGER FIR_ENVOI_MESSAGE_DB0         AUTO_REPOSITORY                                                                 TRIGGER FIR_E_COMMERCE_DB             AUTO_REPOSITORY                                                                 TRIGGER FIR_E_COMMERCE_V2_DB          AUTO_REPOSITORY                                                                 TRIGGER FIR_GCOM_DB                   AUTO_REPOSITORY                                                                 TRIGGER FIR_INTEGRATION_CNUF_AVAL_    AUTO_REPOSITORY                                                                 TRIGGER FIR_INTERPRETE_DB             AUTO_REPOSITORY                                                                 TRIGGER FIR_LEONI_DB                  AUTO_REPOSITORY                                                                 TRIGGER FIR_LOI_SUR_LEAU_DB           AUTO_REPOSITORY                                                                 TRIGGER FIR_MENUS_TRAITEURS_DB        AUTO_REPOSITORY                                                                 TRIGGER FIR_ORADEM_DB                 AUTO_REPOSITORY                                                                 TRIGGER FIR_OUTIL_DE_VENTE_DB         AUTO_REPOSITORY                                                                 TRIGGER FIR_PUBLINET_DB               AUTO_REPOSITORY                                                                 TRIGGER FIR_RAMSES_DB                 AUTO_REPOSITORY                                                                 TRIGGER FIR_RISTOURNES_DB             AUTO_REPOSITORY                                                                 TRIGGER FIR_SAFIR_DB                  AUTO_REPOSITORY                                                                 TRIGGER FIR_SIF1_DB                   AUTO_REPOSITORY                                                                 TRIGGER FIR_SIMPLCIT_E_DB             AUTO_REPOSITORY                                                                 TRIGGER FIR_SRVONDEMAND_DB            AUTO_REPOSITORY                                                                 TRIGGER FIR_TISTEL_ERELAIS_DB         AUTO_REPOSITORY                                                                 TRIGGER FIR_WEBPAGEA_DB               AUTO_REPOSITORY                                                                 TRIGGER FLUX_FINANCIER_NFF_DB         AUTO_REPOSITORY                                                                 TRIGGER FLUX_MARCHAN_ARGOS_ART_DB     AUTO_REPOSITORY                                                                 TRIGGER FLUX_MARCHAN_E_COMMERCE_V     AUTO_REPOSITORY                                                                 TRIGGER FLUX_NTIC_ADMINLDAP_DB        AUTO_REPOSITORY                                                                 TRIGGER FLUX_NTIC_FRAMEWORK_DB        AUTO_REPOSITORY                                                                 TRIGGER FORMATION_FORMATION_DB        AUTO_REPOSITORY                                                                 TRIGGER REF_TEST_ARCHITECTURE_        AUTO_REPOSITORY                                                                 TRIGGER REF_TEST_ARCHIV_REF_T0        AUTO_REPOSITORY                                                                 TRIGGER REF_TEST_ARCHIV_REF_T1        AUTO_REPOSITORY                                                                 TRIGGER REF_TEST_ARCHIV_REF_TE        AUTO_REPOSITORY                                                                 TRIGGER REF_TEST_REF_ESIAM_POL        AUTO_REPOSITORY                                                                 TRIGGER REF_TEST_REF_GCOM_DB          AUTO_REPOSITORY                                                                 TRIGGER REF_TEST_REF_MERCALYS         AUTO_REPOSITORY                                                                 TRIGGER REF_TEST_REF_STORELINE        AUTO_REPOSITORY                                                                 TRIGGER REF_TEST_STORELINE_DB         AUTO_REPOSITORY                                                                 TRIGGER PL/SQL procedure successfully completed. @?/olap/admin/catnoamd.sql SQL> EXECUTE DBMS_STATS.GATHER_DICTIONARY_STATS; SQL>  SELECT OWNER,  TRIGGER_NAME FROM DBA_TRIGGERS WHERE BASE_OBJECT_TYPE='DATABASE' AND     OWNER NOT IN (SELECT GRANTEE FROM DBA_SYS_PRIVS WHERE PRIVILEGE='ADMINISTER DATABASE TRIGGER'); OWNER                          TRIGGER_NAME ------------------------------ ------------------------------ MDSYS                          SDO_DROP_USER MDSYS                          SDO_ST_SYN_CREATE MDSYS                          SDO_TOPO_DROP_FTBL MDSYS                          SDO_GEOR_BDDL_TRIGGER MDSYS                          SDO_GEOR_ADDL_TRIGGER MDSYS                          SDO_NETWORK_DROP_USER SYSMAN                         MGMT_STARTUP 7 rows selected. SQL> grant ADMINISTER DATABASE TRIGGER to MDSYS; Grant succeeded. SQL> grant ADMINISTER DATABASE TRIGGER to SYSMAN; Grant succeeded. SQL> PURGE DBA_RECYCLEBIN; DBA Recyclebin purged.

3.          Rman Backup

Take and RMAN backup on the first database MY_DB.

rman "target / nocatalog" RUN { ALLOCATE CHANNEL chan_name TYPE DISK; BACKUP DATABASE FORMAT '<db_backup_directory>%U' TAG before_upgrade; BACKUP CURRENT CONTROLFILE TO '<controlfile_backup_directory>'; }

Move the backup to the second server: for example on /apps/oracle/Backup

On the second database SERVER2 (target), restore the backup using the Duplicate command.

Startup  nomount MY_DB with appropriate 12c parameters: especially compatible set to 12.2.0 or higher.

. oraenv MY_DB startup nomount pfile='…/initMY_DB.ora'; rman auxiliary / RMAN> run {  allocate auxiliary channel chnl1 device type disk;  DUPLICATE DATABASE TO MY_DB  nofilenamecheck  NOOPEN BACKUP LOCATION '/apps/oracle/Backup/' NOREDO ;  }

We use NOOPEN : so RMAN stops at the MOUNT step.

We use NOREDO if the backup was COLD.

At the end : SQL> shutdown immediate; And create spfile from pfile … SQL> startup mount SQL> alter database open resetlogs upgrade;

4.          MIGRATION

4.1.1. Execute catctl.pl

On the Target server:

Use catctl.pl to upgrade the database.

Run the catctl.pl script from the new Oracle home.

In this release, the new Upgrade Utility, catctl.pl, replaces catupgrd.sql.

mkdir /oradata_MY/data/migration/ cd $ORACLE_HOME/rdbms/admin $ORACLE_HOME/perl/bin/perl catctl.pl -n 6 -l  /oradata_MY/data/migration/ catupgrd.sql catcon: See [/oradata_MY/data/migration//catupgrd*.log] files for output generated by scripts catcon: See [/oradata_MY/data/migration//catupgrd_*.lst] files for spool files, if any Number of Cpus        = 8 Database Name         = MY_DB DataBase Version      = 11.2.0.3.0 Parallel SQL Process Count            = 6 Components in [MY_DB]     Installed [APEX APS CATALOG CATJAVA CATPROC CONTEXT EM JAVAVM ORDIM OWM SDO XDB XML XOQ] Not Installed [DV MGW ODM OLS RAC WK] ------------------------------------------------------ Phases [0-115]         Start Time:[2017_11_14 15:04:05] ------------------------------------------------------ ***********   Executing Change Scripts   *********** Serial   Phase #:0    [MY_DB] Files:1    Time: 391s ***************   Catalog Core SQL   *************** Serial   Phase #:1    [MY_DB] Files:5    Time: 63s Restart  Phase #:2    [MY_DB] Files:1    Time: 1s ***********   Catalog Tables and Views   *********** Parallel Phase #:3    [MY_DB] Files:19   Time: 16s Restart  Phase #:4    [MY_DB] Files:1    Time: 1s *************   Catalog Final Scripts   ************ Serial   Phase #:5    [MY_DB] Files:6    Time: 25s *****************   Catproc Start   **************** Serial   Phase #:6    [MY_DB] Files:1    Time: 22s … ****************   Summary report   **************** Serial   Phase #:113  [MY_DB] Files:1    Time: 3s Serial   Phase #:114  [MY_DB] Files:1    Time: 0s Serial   Phase #:115  [MY_DB] Files:1     Time: 19s ------------------------------------------------------ Phases [0-115]         End Time:[2017_11_14 17:25:57] ------------------------------------------------------ Grand Total Time: 8540s

4.1.2. Execute utlu122s.sql

Run the Post-Upgrade Status Tool $ORACLE_HOME/rdbms/admin/utlu122s.sql which provides a summary of

The upgrade at the end of the spool log.

It displays the status of the database components in the upgraded database and the time required to

complete each component upgrade.

Any errors that occur during the upgrade are listed with each component and must be addressed.

SQL> shutdown immediate; SQL> STARTUP .. cd $ORACLE_HOME/rdbms/admin/ SQL> @utlu122s.sql .. SQL> @catuppst.sql … SQL> @utlrp.sql …

4.1.3. Execute postupgrade_fixups

postupgrade_fixups.sql was generated on the step 2.1. PRE-UPGRADE STEPS

@postupgrade_fixups.sql

4.1.4. Verification

SQL*Plus: Release 12.2.0.1.0 Production on Wed Nov 15 15:41:35 2017 Copyright (c) 1982, 2017, Oracle.  All rights reserved. select COMP_NAME, VERSION, STATUS from dba_registry; COMP_NAME                                                                        VERSION                        STATUS -------------------------------------------------------------------------------- ------------------------------ ------------------------------ Oracle Database Catalog Views                                                    12.2.0.1.0                     VALID Oracle Database Packages and Types                                               12.2.0.1.0                     VALID JServer JAVA Virtual Machine                                                     12.2.0.1.0                     VALID Oracle XDK                                                                       12.2.0.1.0                     VALID Oracle Database Java Packages                                                    12.2.0.1.0                     VALID OLAP Analytic Workspace                                                          11.2.0.3.0                     OPTION OFF Oracle OLAP API                                                                  11.2.0.3.0                     OPTION OFF Oracle Workspace Manager                                                         12.2.0.1.0                     VALID Oracle Text                                                                      12.2.0.1.0                     VALID Oracle XML Database                                                              12.2.0.1.0                     VALID Oracle Multimedia                                                                12.2.0.1.0                     VALID COMP_NAME                                                                        VERSION                        STATUS -------------------------------------------------------------------------------- ------------------------------ ------------------------------ Spatial                                                                          12.2.0.1.0                     OPTION OFF OLAP Catalog                                                                     11.2.0.3.0                     OPTION OFF Oracle Application Express                                                       5.0.4.00.12                    VALID 14 rows selected.