Oracle 12c R1- Actualización de tu BD de 11.2.0.4 a 12.1.0.2
Como muchos ya saben , ahora en Enero del 2015 se acabo el soporte Premier de la Version 11.2 de la BD de Oracle, así que es el momento de pensar en actualizar tus BDs a la ultima version , que es la 12.1.0.2 . Aquí no voy a entrar a detalle de como instalar los binarios de Oracle 12c , eso lo puedes tomar del post de RAC Attack .Lo primero que tenemos que hacer es correr el script preupgrd.sql que viene en los binarios de 12.1 como sysdba
oracle@localhost.localdomain [RENE] /home/oracle oracle $ echo $ORACLE_HOME /u01/app/oracle/product/11.2.0 oracle@localhost.localdomain [RENE] /home/oracle oracle $ sqlplus / as sysdba SQL*Plus: Release 11.2.0.4.0 Production on Tue Feb 10 19:39:05 2015 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, Oracle Label Security, OLAP, Data Mining and Real Application Testing options SQL> @/u01/app/oracle/product/12.1.0.2/rdbms/admin/preupgrd.sql
Y el resultado va a ser una serie de recomendaciones que tienes que hacer antes y despues de hacer la actualización a 12c
... *************************************************************************** Executing Pre-Upgrade Checks in RENE... *************************************************************************** ************************************************************ ====>> ERRORS FOUND for RENE <<==== 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: OLS_SYS_MOVE Check Summary: Check if SYSTEM.AUD$ needs to move to SYS.AUD$ before upgrade Fixup Summary: "Execute olspreupgrade.sql script prior to upgrade." +++ Source Database Manual Action Required +++ You MUST resolve the above error prior to upgrade ************************************************************ ************************************************************ ====>> PRE-UPGRADE RESULTS for RENE <<==== ACTIONS REQUIRED: 1. Review results of the pre-upgrade checks: /u01/app/oracle/cfgtoollogs/RENE/preupgrade/preupgrade.log ...
Así que lo primero que vamos a hacer es revisar el archivo preupgrade.log para ver que tenemos que corregir antes de continuar, aqui no voy a poner todo, pero si un pequeño resumen de lo que es el archivo
oracle@localhost.localdomain [RENE] /home/oracle oracle $ cat /u01/app/oracle/cfgtoollogs/RENE/preupgrade/preupgrade.log Oracle Database Pre-Upgrade Information Tool 02-10-2015 19:39:32 Script Version: 12.1.0.2.0 Build: 006 ********************************************************************** Database Name: RENE Container Name: Not Applicable in Pre-12.1 database Container ID: Not Applicable in Pre-12.1 database Version: 11.2.0.4.0 Compatible: 11.2.0.4.0 Blocksize: 8192 Platform: Linux x86 64-bit Timezone file: V14 ********************************************************************** ... ********************************************************************** [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 --> Oracle Workspace Manager [upgrade] VALID --> OLAP Analytic Workspace [upgrade] VALID --> Oracle Label Security [upgrade] VALID --> Oracle Text [upgrade] VALID --> Oracle XML Database [upgrade] VALID --> Oracle Java Packages [upgrade] VALID --> Expression Filter [upgrade] VALID --> Rule Manager [upgrade] VALID --> Oracle OLAP API [upgrade] VALID ... ********************************************************************** [Pre-Upgrade Checks] ********************************************************************** WARNING: --> Process Count may be too low Database has a maximum process count of 78 which is lower than the default value of 300 for this release. You should update your processes value prior to the upgrade to a value of at least 300. For example: ALTER SYSTEM SET PROCESSES=300 SCOPE=SPFILE or update your init.ora file. ERROR: --> SYSTEM.AUD$ (audit records) Move Oracle requires that records in the audit table SYSTEM.AUD$ be moved to SYS.AUD$ prior to upgrading.. The Database has 38 rows in SYSTEM.AUD$ which will be moved during the upgrade. The downtime during the upgrade will be affected if there are a large number of rows to be moved. The audit data can be moved manually prior to the upgrade by using the script: rdbms/admin/olspreupgrade.sql which is part of the Oracle Database 12c software. Please refer to the Label Security Administrator guide or the Database Upgrade guide. 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 11.2.0.4.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. 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 ************ 1 ERROR exist that must be addressed prior to performing your upgrade. 1 WARNING 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 ***********************************
Ya que verificamos el archivo, lo primero que vamos a quitar es el componente del Catalogo OLAP (AMD), ya que este no es soportado e incluido en 12c, así que es algo que tienes que tomar en cuenta cuando hagas tu actualización
oracle@localhost.localdomain [RENE] /home/oracle oracle $ sqlplus / as sysdba SQL*Plus: Release 11.2.0.4.0 Production on Tue Feb 10 19:41:51 2015 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, Oracle Label Security, OLAP, Data Mining and Real Application Testing options SQL> @/u01/app/oracle/product/12.1.0.2/olap/admin/catnoamd.sql  Synonym dropped. ... Synonym dropped. PL/SQL procedure successfully completed. 1 row deleted. SQL> commit; Commit complete.
Lo siguiente es que vamos a mover los registros con el script olspreupgrade.sql que se encuentran en la tabla AUD$ debido al componente Oracle Label Security
SQL> @/u01/app/oracle/product/12.1.0.2/rdbms/admin/olspreupgrade.sql Function created. No errors. Function created. No errors. Function created. No errors. 2015-02-10 19:42:17 ******* BEGINNING OLS PRE RENEADE SCRIPT ******** The amount of FREE space required = 131072 Bytes Free space available on SYSTEM tablespace= 33798078464 Bytes 2015-02-10 19:42:17 ******** PROCEEDING WITH OLS PRE RENEADE ******* Audit records successfully moved to SYS.PREUPG_AUD$ PL/SQL procedure successfully completed. No errors. Total number of rows in SYS.PREUPG_AUD$: 38 2015-02-10 19:42:18 ******* FINISHING OLS PRE RENEADE SCRIPT ******** PL/SQL procedure successfully completed.
Para continuar, vamos a hacer unos cambios en el spfile para poder hacer nuestra actualización
SQL> create pfile from spfile; File created. SQL> alter system set processes=300 scope=spfile; System altered. SQL> alter system set COMPATIBLE='12.1.0' scope=spfile; System altered. SQL> EXECUTE dbms_stats.gather_dictionary_stats; PL/SQL procedure successfully completed.
El ultimo paso para empezar nuestra actualización , vamos a correr el preupgrade_fixups.sql, aqui me esta marcando el error en OLS_SYS_MOVE, pero si reviso el log me dice que tengo 0 registros que mover
SQL> @/u01/app/oracle/cfgtoollogs/RENE/preupgrade/preupgrade_fixups.sql Pre-Upgrade Fixup Script Generated on 2015-02-10 19:39:30 Version: 12.1.0.2 Build: 006 Beginning Pre-Upgrade Fixups... Executing in container RENE ... ********************************************************************** [Pre-Upgrade Recommendations] ********************************************************************** ********************************************************************** Check Tag: OLS_SYS_MOVE Check Summary: Check if SYSTEM.AUD$ needs to move to SYS.AUD$ before upgrade Fix Summary: Execute olspreupgrade.sql script prior to upgrade. ********************************************************************** Fixup Returned Information: ERROR: --> SYSTEM.AUD$ (audit records) Move Oracle requires that records in the audit table SYSTEM.AUD$ be moved to SYS.AUD$ prior to upgrading.. The Database has 0 rows in SYSTEM.AUD$ which will be moved during the upgrade. The downtime during the upgrade will be affected if there are a large number of rows to be moved. The audit data can be moved manually prior to the upgrade by using the script: rdbms/admin/olspreupgrade.sql which is part of the Oracle Database 12c software. Please refer to the Label Security Administrator guide or the Database Upgrade guide. ********************************************************************** ... ***************************************** ********* 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 ^^^ ************************************************** ************* Fixup Summary ************ 2 fixup routines returned INFORMATIONAL text that should be reviewed. 1 ERROR LEVEL check returned INFORMATION that must be acted on prior to upgrade. ************************************************************ ====>> USER ACTION REQUIRED <<==== ************************************************************ 1) Check Tag: OLS_SYS_MOVE failed. Check Summary: Check if SYSTEM.AUD$ needs to move to SYS.AUD$ before upgrade Fixup Summary: "Execute olspreupgrade.sql script prior to upgrade." ^^^ MANUAL ACTION REQUIRED ^^^ ************************************************** You MUST resolve the above error prior to upgrade ************************************************** **************** Pre-Upgrade Fixup Script Complete ********************* PL/SQL procedure successfully completed.
Vamos a bajar nuestra instancia y copiar los archivos de passwords y spfile
SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> exit Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, Oracle Label Security, OLAP, Data Mining and Real Application Testing options oracle@localhost.localdomain [RENE] /home/oracle oracle $ cp /u01/app/oracle/product/11.2.0/dbs/orapwRENE /u01/app/oracle/product/12.1.0.2/dbs/ oracle@localhost.localdomain [RENE] /home/oracle oracle $ cp /u01/app/oracle/product/11.2.0/dbs/spfileRENE.ora /u01/app/oracle/product/12.1.0.2/dbs/
Lo que tenemos que hacer ahora es cambiar el ORACLE_HOME para que apunte a los nuevos binarios
oracle@localhost.localdomain [RENE] /u01/app/oracle/product/12.1.0.2/rdbms/admin oracle $ export ORACLE_HOME="/u01/app/oracle/product/12.1.0.2" oracle@localhost.localdomain [RENE] /u01/app/oracle/product/12.1.0.2/rdbms/admin oracle $ echo $ORACLE_HOME /u01/app/oracle/product/12.1.0.2
Ya que cambiamos el ORACLE_HOME, vamos a levantar la instancia en mode upgrade y vamos a cambiarnos a la ruta $ORACLE_HOME/rdbms/admin
oracle@localhost.localdomain [RENE] /home/oracle oracle $ sqlplus / as sysdba SQL*Plus: Release 12.1.0.2.0 Production on Tue Feb 10 19:47:47 2015 Copyright (c) 1982, 2014, Oracle. All rights reserved. Connected to an idle instance. SQL> startup upgrade ORACLE instance started. Total System Global Area 826277888 bytes Fixed Size 2929792 bytes Variable Size 322964352 bytes Database Buffers 494927872 bytes Redo Buffers 5455872 bytes Database mounted. Database opened. SQL> exit Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options oracle@localhost.localdomain [RENE] /home/oracle oracle $ cd $ORACLE_HOME/rdbms/admin
Y ahora vamos a correr la nueva manera de actualizar nuestras BDs, que es con el script de perl catctl.pl, este va a correr en 73 fases, algunos en modo serial , algunos otros en modo paralelo, como lo puedes ver en el ejemplo de abajo
oracle@localhost.localdomain [RENE] /u01/app/oracle/product/12.1.0.2/rdbms/admin
oracle $ $ORACLE_HOME/perl/bin/perl catctl.pl catupgrd.sql
perl: warning: Setting locale failed.
perl: warning: Please check that your locale settings:
LANGUAGE = (unset),
LC_ALL = (unset),
LC_CTYPE = "UTF-8",
LANG = "en_US.UTF-8"
are supported and installed on your system.
perl: warning: Falling back to the standard locale ("C").
Argument list for [catctl.pl]
SQL Process Count n = 0
SQL PDB Process Count N = 0
Input Directory d = 0
Phase Logging Table t = 0
Log Dir l = 0
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 = /u01/app/oracle
Analyzing file catupgrd.sql
Log files in /u01/app/oracle/product/12.1.0.2/rdbms/admin
catcon: ALL catcon-related output will be written to catupgrd_catcon_4207.lst
catcon: See catupgrd*.log files for output generated by scripts
catcon: See catupgrd_*.lst files for spool files, if any
Number of Cpus = 2
SQL Process Count = 0
New SQL Process Count = 4
------------------------------------------------------
Phases [0-73]
Serial Phase #: 0 Files: 1
Time: 146s
Serial Phase #: 1 Files: 5 Time: 53s
Restart Phase #: 2 Files: 1 Time: 0s
Parallel Phase #: 3 Files: 18 Time: 13s
...
Serial Phase #:65 Files: 1 Calling sqlpatch with LD_LIBRARY_PATH=/u01/app/oracle/product/12.1.0.2/lib; export LD_LIBRARY_PATH;/u01/app/oracle/product/12.1.0.2/perl/bin/perl -I /u01/app/oracle/product/12.1.0.2/rdbms/admin -I /u01/app/oracle/product/12.1.0.2/rdbms/admin/../../sqlpatch /u01/app/oracle/product/12.1.0.2/rdbms/admin/../../sqlpatch/sqlpatch.pl -verbose -upgrade_mode_only > catupgrd_datapatch_upgrade.log 2> catupgrd_datapatch_upgrade.err
returned from sqlpatch
Time: 29s
Serial Phase #:66 Files: 1 Time: 52s
Serial Phase #:68 Files: 1 Time: 0s
Serial Phase #:69 Files: 1 Calling sqlpatch with LD_LIBRARY_PATH=/u01/app/oracle/product/12.1.0.2/lib; export LD_LIBRARY_PATH;/u01/app/oracle/product/12.1.0.2/perl/bin/perl -I /u01/app/oracle/product/12.1.0.2/rdbms/admin -I /u01/app/oracle/product/12.1.0.2/rdbms/admin/../../sqlpatch /u01/app/oracle/product/12.1.0.2/rdbms/admin/../../sqlpatch/sqlpatch.pl -verbose > catupgrd_datapatch_normal.log 2> catupgrd_datapatch_normal.err
returned from sqlpatch
Time: 59s
Serial Phase #:70 Files: 1 Time: 72s
Serial Phase #:71 Files: 1 Time: 0s
Serial Phase #:72 Files: 1 Time: 0s
Serial Phase #:73 Files: 1 Time: 44s
Grand Total Time: 1742s
LOG FILES: (catupgrd*.log)
Upgrade Summary Report Located in:
/u01/app/oracle/product/12.1.0.2/cfgtoollogs/RENE/upgrade/upg_summary.log
Grand Total Upgrade Time: [0d:0h:29m:2s]
oracle@localhost.localdomain [RENE] /u01/app/oracle/product/12.1.0.2/rdbms/admin
oracle $ cat /u01/app/oracle/product/12.1.0.2/cfgtoollogs/RENE/upgrade/upg_summary.log
Oracle Database 12.1 Post-Upgrade Status Tool 02-10-2015 20:18:15
Component Current Version Elapsed Time
Name Status Number HH:MM:SS
Oracle Server RENEADED 12.1.0.2.0 00:14:46
JServer JAVA Virtual Machine VALID 12.1.0.2.0 00:03:52
Oracle Workspace Manager VALID 12.1.0.2.0 00:00:58
OLAP Analytic Workspace VALID 12.1.0.2.0 00:00:37
Oracle OLAP API VALID 12.1.0.2.0 00:00:22
Oracle Label Security VALID 12.1.0.2.0 00:00:11
Oracle XDK VALID 12.1.0.2.0 00:00:40
Oracle Text VALID 12.1.0.2.0 00:00:57
Oracle XML Database VALID 12.1.0.2.0 00:01:36
Oracle Database Java Packages VALID 12.1.0.2.0 00:00:17
Final Actions 00:01:20
Post Upgrade 00:01:10
Total Upgrade Time: 00:27:13
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.14
Grand Total Upgrade Time: [0d:0h:29m:2s]
Como puedes ver todo es valido, pero vamos a compilar todos nuestros objetos con utlrp.sql
oracle@localhost.localdomain [RENE] /u01/app/oracle/product/12.1.0.2/rdbms/admin oracle $ sqlplus / as sysdba SQL*Plus: Release 12.1.0.2.0 Production on Tue Feb 10 20:21:53 2015 Copyright (c) 1982, 2014, Oracle. All rights reserved. Connected to an idle instance. SQL> startup ORACLE instance started. Total System Global Area 826277888 bytes Fixed Size 2929792 bytes Variable Size 432016256 bytes Database Buffers 385875968 bytes Redo Buffers 5455872 bytes Database mounted. Database opened. SQL> @?/rdbms/admin/utlrp.sql
Ahora lo que tenemos que hacer es correr el script postupgrade_fixups.sql
SQL> @/u01/app/oracle/cfgtoollogs/RENE/preupgrade/postupgrade_fixups.sql Post Upgrade Fixup Script Generated on 2015-02-10 19:39:30 Version: 12.1.0.2 Build: 006 Beginning Post-Upgrade Fixups... ********************************************************************** Check Tag: OLD_TIME_ZONES_EXIST Check Summary: Check for use of older timezone data file Fix Summary: Update the timezone using the DBMS_DST package after upgrade is complete. ********************************************************************** Fixup Returned Information: 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 12.1.0.2.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. ********************************************************************** ********************************************************************** Check Tag: NOT_UPG_BY_STD_RENED Check Summary: Identify existing components that will NOT be upgraded Fix Summary: This fixup does not perform any action. ********************************************************************** Fixup Returned Information: This fixup does not perform any action. If you want to upgrade those other components, you must do so manually. ********************************************************************** ********************************************************************** [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 ^^^ ************************************************** ************* Fixup Summary ************ 2 fixup routines generated INFORMATIONAL messages that should be reviewed. *************** Post Upgrade Fixup Script Complete ******************** PL/SQL procedure successfully completed.
Como puedes ver, lo unico que falta es cambiar el archivo de las zonas de tiempo, que aqui no voy a entrar en detalle, ya que eso lo puedes ver en la nota 977512.1, pero que sepas que es algo que tienes que hacer, pero aqui te voy a mostrar que pase de la version 14 a la version 18
... PROPERTY_NAME VALUE ------------------------------------------------------------------------------------- DST_PRIMARY_TT_VERSION 18 DST_SECONDARY_TT_VERSION 14 DST_UPGRADE_STATE UPGRADE ... PROPERTY_NAME VALUE ------------------------------------------------------------------------------------- DST_PRIMARY_TT_VERSION 18 DST_SECONDARY_TT_VERSION 0 DST_UPGRADE_STATE NONE FILENAME VERSION CON_ID ------------------------------------------------------------ ---------- ---------- timezlrg_18.dat 18 0
Espero que este pequeño post te ayude en tu próxima actualización de tu Base de Datos y si tienes alguna pregunta o duda, hazmelo saber.
Nice blog.
ResponderBorraralso, check Java training in Pune