10 Feb 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
[email protected] [RENE] /home/oracle
oracle $ echo $ORACLE_HOME
/u01/app/oracle/product/11.2.0
[email protected] [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
[email protected] [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 B
locksize: 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
[email protected] [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 completedVamos 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
[email protected] [RENE] /home/oracle
oracle $ cp /u01/app/oracle/product/11.2.0/dbs/orapwRENE /u01/app/oracle/product/12.1.0.2/dbs/
[email protected] [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
[email protected] [RENE] /u01/app/oracle/product/12.1.0.2/rdbms/admin
oracle $ export ORACLE_HOME="/u01/app/oracle/product/12.1.0.2"
[email protected] [RENE] /u01/app/oracle/product/12.1.0.2/rdbms/admin
oracle $ echo $ORACLE_HOME
/u01/app/oracle/product/12.1.0.2Ya que cambiamos el ORACLE_HOME, vamos a levantar la instancia en mode upgrade y vamos a cambiarnos a la ruta $ORACLE_HOME/rdbms/admin
[email protected] [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
[email protected] [RENE] /home/oracle
oracle $ cd $ORACLE_HOME/rdbms/adminY 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
[email protected] [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]
[email protected] [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
[email protected] [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.sqlAhora 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 0Espero 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.
Sorry, the comment form is closed at this time.