Migration of an 11gr2 database to 12cr2, using Duplicate with the new feature NOOPEN
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:
We assume:
|
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.
|
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.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.
|
