En un artículo anterior detallé los pasos a seguir para acometer un parche de la Grid Infrastructure Out of Place, ahora compartiré los pasos para ejecutarlo a nivel de BBDD .
Me parece interesante mostrar que, además, si disponemos de una Standby en la que podamos permitirnos abrirla durante un periodo de tiempo, podemos probar el parche previamente y realizar pruebas antes de ejecutarlo en la primaria.
El HOME actual es: /u01/app/oracle/product/19.22.0
El nuevo HOME será: /u01/app/oracle/product/19.24.0
En este caso los parches que aplicaremos serán los siguientes:
Patch 36414915: OJVM RELEASE UPDATE 19.24.0.0.0
Patch 36582629: GI RELEASE UPDATE 19.24.0.0.0
Los cuales descomprimiré en el directorio /u01/software/RU_24/Parches para su aplicación.
En mi caso se trata de un DG en RAC, por lo que tendremos dos nodos. Muestro salida de uno de ellos:
[root@rac-standby-001 RU_24]# ls -lrt Parches/
total 0
drwxr-xr-x 4 oracle oinstall 67 jun 12 10:32 36414915
drwxr-x--- 8 oracle oinstall 159 jul 13 23:20 36582629
Como siempre, el primer pasó será la actualización del Opatch a nivel de binarios en el HOME actual.
[oracle@rac-standby-001 ~]$ $ORACLE_HOME/OPatch/opatch version
OPatch Version: 12.2.0.1.43
OPatch succeeded.
[oracle@rac-standby-002 ~]$ $ORACLE_HOME/OPatch/opatch version
OPatch Version: 12.2.0.1.43
OPatch succeeded.
Como buena práctica recompilaremos todos los objetos a nivel de CDB y PDB si aplica en la primaria.
SQL> @?/rdbms/admin/utlrp
Haremos un backup de la configuración inicial del inventario:
[oracle@rac-standby-001 RU_24]$ $ORACLE_HOME/OPatch/opatch lsinventory -detail -oh /u01/app/oracle/product/19.22.0 >> backup_config_previa/inventory_detail_BBDD.log
[oracle@rac-standby-002 RU_24]$ $ORACLE_HOME/OPatch/opatch lsinventory -detail -oh /u01/app/oracle/product/19.22.0 >> backup_config_previa/inventory_detail_BBDD.log
Chequearemos conflictos:
$ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir /u01/software/RU_24/Parches/36582629/36582781
$ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir /u01/software/RU_24/Parches/36582629/36587798
$ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir /u01/software/RU_24/Parches/36582629/36590554
$ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir /u01/software/RU_24/Parches/36582629/36648174
$ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir /u01/software/RU_24/Parches/36582629/36758186
La salida en todos los casos deberá ser:
Invoking prereq "checkconflictagainstohwithdetail"
Prereq "checkConflictAgainstOHWithDetail" passed.
OPatch succeeded.
Una vez que hemos realizado los pasos previos nos ponemos al lío… Este paso no conlleva corte, se puede realizar días previos al parche para ahorrar tiempo de intervención.
Comenzaré por uno de los nodos con el análisis previo.
[root@rac-standby-002 RU_24]# $ORACLE_HOME/OPatch/opatchauto apply -phBaseDir /u01/software/RU_24/Parches -prepare-clone -logLevel FINEST -log /u01/software/RU_24/analisis.log -oh $ORACLE_HOME -analyze -force_conflict
OPatchauto session is initiated at Thu Sep 19 13:19:41 2024
System initialization log file is /u01/app/oracle/product/19.22.0/cfgtoollogs/opatchautodb/systemconfig2024-09-19_01-19-43PM.log.
El archivo de log de sesión es /u01/software/RU_24/analisis.log
El ID para esta sesión es ZVLW
Please press ENTER button to accept system generated default clone path.
Please enter clone path [/u01/app/oracle/product/19.22.0_2 ] :
/u01/app/oracle/product/19.24.0
Executing OPatch prereq operations to verify patch applicability on home /u01/app/oracle/product/19.22.0
Patch applicability verified successfully on home /u01/app/oracle/product/19.22.0
Executing patch validation checks on home /u01/app/oracle/product/19.22.0
Patch validation checks successfully completed on home /u01/app/oracle/product/19.22.0
Verifying SQL patch applicability on home /u01/app/oracle/product/19.22.0
No sqlpatch prereq operations are required on the local node for this home
No step execution required.........
OPatchAuto correcto.
--------------------------------Summary--------------------------------
Out of place patching clone home(s) summary
____________________________________________
Host : rac-standby-002
Actual Home : /u01/app/oracle/product/19.22.0
Version:19.0.0.0.0
Clone Home Path : /u01/app/oracle/product/19.24.0
Analysis for applying patches has completed successfully:
Host:rac-standby-002
RAC Home:/u01/app/oracle/product/19.22.0
Version:19.0.0.0.0
==Following patches were SKIPPED:
Patch: /u01/software/RU_24/Parches/36582629/36590554
Reason: This patch is not applicable to this specified target type - "rac_database"
Patch: /u01/software/RU_24/Parches/36582629/36758186
Reason: This patch is not applicable to this specified target type - "rac_database"
Patch: /u01/software/RU_24/Parches/36582629/36648174
Reason: This patch is not applicable to this specified target type - "rac_database"
==Following patches were SUCCESSFULLY analyzed to be applied:
Patch: /u01/software/RU_24/Parches/36414915
Log: /u01/app/oracle/product/19.22.0/cfgtoollogs/opatchauto/core/opatch/opatch2024-09-19_13-20-22PM_1.log
Patch: /u01/software/RU_24/Parches/36582629/36587798
Log: /u01/app/oracle/product/19.22.0/cfgtoollogs/opatchauto/core/opatch/opatch2024-09-19_13-20-22PM_1.log
Patch: /u01/software/RU_24/Parches/36582629/36582781
Log: /u01/app/oracle/product/19.22.0/cfgtoollogs/opatchauto/core/opatch/opatch2024-09-19_13-20-22PM_1.log
OPatchauto session completed at Thu Sep 19 13:21:26 2024
Time taken to complete the session 1 minute, 45 seconds
Perfecto, revisamos que el análisis ha ido OK y procedemos al clonado del home. Nos pedirá en este primer nodo la nueva ruta de los binarios.
[root@rac-standby-002 RU_24]# $ORACLE_HOME/OPatch/opatchauto apply -phBaseDir /u01/software/RU_24/Parches -prepare-clone -logLevel FINEST -log /u01/software/RU_24/clonado.log -oh $ORACLE_HOME -force_conflict
OPatchauto session is initiated at Thu Sep 19 13:24:41 2024
System initialization log file is /u01/app/oracle/product/19.22.0/cfgtoollogs/opatchautodb/systemconfig2024-09-19_01-24-43PM.log.
El archivo de log de sesión es /u01/software/RU_24/clonado.log
El ID para esta sesión es RBIB
Please press ENTER button to accept system generated default clone path.
Please enter clone path [/u01/app/oracle/product/19.22.0_2 ] :
/u01/app/oracle/product/19.24.0
Executing OPatch prereq operations to verify patch applicability on home /u01/app/oracle/product/19.22.0
Patch applicability verified successfully on home /u01/app/oracle/product/19.22.0
Executing patch validation checks on home /u01/app/oracle/product/19.22.0
Patch validation checks successfully completed on home /u01/app/oracle/product/19.22.0
Verifying SQL patch applicability on home /u01/app/oracle/product/19.22.0
No sqlpatch prereq operations are required on the local node for this home
No step execution required.........
Copying the files from the existing oracle home /u01/app/oracle/product/19.22.0 to a new location. Please wait...
Clone of oracle home /u01/app/oracle/product/19.22.0 is /u01/app/oracle/product/19.24.0 on host rac-standby-002
Copying the files from the existing oracle home /u01/app/oracle/product/19.22.0 to a new location is successful.
Creating clone for oracle home /u01/app/oracle/product/19.22.0.
Clone operation successful for oracle home /u01/app/oracle/product/19.22.0.
Performing post clone operation for oracle home /u01/app/oracle/product/19.22.0.
Performing post clone operation was successful for oracle home /u01/app/oracle/product/19.22.0.
Performing prepatch operation on home /u01/app/oracle/product/19.24.0
Prepatch operation completed successfully on home /u01/app/oracle/product/19.24.0
Start applying binary patch on home /u01/app/oracle/product/19.24.0
Binary patch applied successfully on home /u01/app/oracle/product/19.24.0
Running rootadd_rdbms.sh on home /u01/app/oracle/product/19.24.0
Successfully executed rootadd_rdbms.sh on home /u01/app/oracle/product/19.24.0
Performing postpatch operation on home /u01/app/oracle/product/19.24.0
Postpatch operation completed successfully on home /u01/app/oracle/product/19.24.0
Preparing home /u01/app/oracle/product/19.24.0 after database service restarted
No step execution required.........
OPatchAuto correcto.
--------------------------------Summary--------------------------------
Patching is completed successfully. Please find the summary as follows:
Host:rac-standby-002
RAC Home:/u01/app/oracle/product/19.22.0
Version:19.0.0.0.0
Summary:
==Following patches were SKIPPED:
Patch: /u01/software/RU_24/Parches/36582629/36590554
Reason: This patch is not applicable to this specified target type - "rac_database"
Patch: /u01/software/RU_24/Parches/36582629/36758186
Reason: This patch is not applicable to this specified target type - "rac_database"
Patch: /u01/software/RU_24/Parches/36582629/36648174
Reason: This patch is not applicable to this specified target type - "rac_database"
==Following patches were SUCCESSFULLY applied:
Patch: /u01/software/RU_24/Parches/36414915
Log: /u01/app/oracle/product/19.24.0/cfgtoollogs/opatchauto/core/opatch/opatch2024-09-19_13-27-21PM_1.log
Patch: /u01/software/RU_24/Parches/36582629/36582781
Log: /u01/app/oracle/product/19.24.0/cfgtoollogs/opatchauto/core/opatch/opatch2024-09-19_13-27-21PM_1.log
Patch: /u01/software/RU_24/Parches/36582629/36587798
Log: /u01/app/oracle/product/19.24.0/cfgtoollogs/opatchauto/core/opatch/opatch2024-09-19_13-27-21PM_1.log
Out of place patching clone home(s) summary
____________________________________________
Host : rac-standby-002
Actual Home : /u01/app/oracle/product/19.22.0
Version:19.0.0.0.0
Clone Home Path : /u01/app/oracle/product/19.24.0
OPatchauto session completed at Thu Sep 19 13:32:54 2024
Time taken to complete the session 8 minutes, 13 seconds
Comprobaremos el nivel de parcheo del nuevo home:
[oracle@rac-standby-002 ~]$ /u01/app/oracle/product/19.24.0/OPatch/opatch lspatches
36587798;OCW RELEASE UPDATE 19.24.0.0.0 (36587798)
36582781;Database Release Update : 19.24.0.0.240716 (36582781)
36414915;OJVM RELEASE UPDATE: 19.24.0.0.240716 (36414915)
OPatch succeeded.
Bien! tenemos todos los que queremos. Ahora repetiremos la operación para el primero de los nodos. En este paso ya no nos va a solicitar el nuevo HOME, utilizando en que se ha especificado en el primero de los nodos.
[root@rac-standby-001 RU_24]# $ORACLE_HOME/OPatch/opatchauto apply -phBaseDir /u01/software/RU_24/Parches -prepare-clone -logLevel FINEST -log /u01/software/RU_24/analisis.log -oh $ORACLE_HOME -analyze -force_conflict
OPatchauto session is initiated at Thu Sep 19 13:35:11 2024
System initialization log file is /u01/app/oracle/product/19.22.0/cfgtoollogs/opatchautodb/systemconfig2024-09-19_01-35-13PM.log.
El archivo de log de sesión es /u01/software/RU_24/analisis.log
El ID para esta sesión es X3YC
Executing OPatch prereq operations to verify patch applicability on home /u01/app/oracle/product/19.22.0
Patch applicability verified successfully on home /u01/app/oracle/product/19.22.0
Executing patch validation checks on home /u01/app/oracle/product/19.22.0
Patch validation checks successfully completed on home /u01/app/oracle/product/19.22.0
Verifying SQL patch applicability on home /u01/app/oracle/product/19.22.0
No sqlpatch prereq operations are required on the local node for this home
No step execution required.........
OPatchAuto correcto.
--------------------------------Summary--------------------------------
Out of place patching clone home(s) summary
____________________________________________
Host : rac-standby-001
Actual Home : /u01/app/oracle/product/19.22.0
Version:19.0.0.0.0
Clone Home Path : /u01/app/oracle/product/19.24.0
Analysis for applying patches has completed successfully:
Host:rac-standby-001
RAC Home:/u01/app/oracle/product/19.22.0
Version:19.0.0.0.0
==Following patches were SKIPPED:
Patch: /u01/software/RU_24/Parches/36582629/36590554
Reason: This patch is not applicable to this specified target type - "rac_database"
Patch: /u01/software/RU_24/Parches/36582629/36758186
Reason: This patch is not applicable to this specified target type - "rac_database"
Patch: /u01/software/RU_24/Parches/36582629/36648174
Reason: This patch is not applicable to this specified target type - "rac_database"
==Following patches were SUCCESSFULLY analyzed to be applied:
Patch: /u01/software/RU_24/Parches/36414915
Log: /u01/app/oracle/product/19.22.0/cfgtoollogs/opatchauto/core/opatch/opatch2024-09-19_13-35-43PM_1.log
Patch: /u01/software/RU_24/Parches/36582629/36587798
Log: /u01/app/oracle/product/19.22.0/cfgtoollogs/opatchauto/core/opatch/opatch2024-09-19_13-35-43PM_1.log
Patch: /u01/software/RU_24/Parches/36582629/36582781
Log: /u01/app/oracle/product/19.22.0/cfgtoollogs/opatchauto/core/opatch/opatch2024-09-19_13-35-43PM_1.log
OPatchauto session completed at Thu Sep 19 13:36:43 2024
Time taken to complete the session 1 minute, 32 seconds
Como en el caso anterior, validamos que el análisis ha ido ok y procedemos al clonado:
[root@rac-standby-001 RU_24]# $ORACLE_HOME/OPatch/opatchauto apply -phBaseDir /u01/software/RU_24/Parches -prepare-clone -logLevel FINEST -log /u01/software/RU_24/clonado.log -oh $ORACLE_HOME -force_conflict
OPatchauto session is initiated at Thu Sep 19 13:38:46 2024
System initialization log file is /u01/app/oracle/product/19.22.0/cfgtoollogs/opatchautodb/systemconfig2024-09-19_01-38-48PM.log.
El archivo de log de sesión es /u01/software/RU_24/clonado.log
El ID para esta sesión es 9QZ8
Executing OPatch prereq operations to verify patch applicability on home /u01/app/oracle/product/19.22.0
Patch applicability verified successfully on home /u01/app/oracle/product/19.22.0
Executing patch validation checks on home /u01/app/oracle/product/19.22.0
Patch validation checks successfully completed on home /u01/app/oracle/product/19.22.0
Verifying SQL patch applicability on home /u01/app/oracle/product/19.22.0
No sqlpatch prereq operations are required on the local node for this home
No step execution required.........
Copying the files from the existing oracle home /u01/app/oracle/product/19.22.0 to a new location. Please wait...
Clone of oracle home /u01/app/oracle/product/19.22.0 is /u01/app/oracle/product/19.24.0 on host rac-standby-001
Copying the files from the existing oracle home /u01/app/oracle/product/19.22.0 to a new location is successful.
Creating clone for oracle home /u01/app/oracle/product/19.22.0.
Clone operation successful for oracle home /u01/app/oracle/product/19.22.0.
Performing post clone operation for oracle home /u01/app/oracle/product/19.22.0.
Performing post clone operation was successful for oracle home /u01/app/oracle/product/19.22.0.
Performing prepatch operation on home /u01/app/oracle/product/19.24.0
Prepatch operation completed successfully on home /u01/app/oracle/product/19.24.0
Start applying binary patch on home /u01/app/oracle/product/19.24.0
Binary patch applied successfully on home /u01/app/oracle/product/19.24.0
Running rootadd_rdbms.sh on home /u01/app/oracle/product/19.24.0
Successfully executed rootadd_rdbms.sh on home /u01/app/oracle/product/19.24.0
Performing postpatch operation on home /u01/app/oracle/product/19.24.0
Postpatch operation completed successfully on home /u01/app/oracle/product/19.24.0
Preparing home /u01/app/oracle/product/19.24.0 after database service restarted
No step execution required.........
OPatchAuto correcto.
--------------------------------Summary--------------------------------
Patching is completed successfully. Please find the summary as follows:
Host:rac-standby-001
RAC Home:/u01/app/oracle/product/19.22.0
Version:19.0.0.0.0
Summary:
==Following patches were SKIPPED:
Patch: /u01/software/RU_24/Parches/36582629/36590554
Reason: This patch is not applicable to this specified target type - "rac_database"
Patch: /u01/software/RU_24/Parches/36582629/36758186
Reason: This patch is not applicable to this specified target type - "rac_database"
Patch: /u01/software/RU_24/Parches/36582629/36648174
Reason: This patch is not applicable to this specified target type - "rac_database"
==Following patches were SUCCESSFULLY applied:
Patch: /u01/software/RU_24/Parches/36414915
Log: /u01/app/oracle/product/19.24.0/cfgtoollogs/opatchauto/core/opatch/opatch2024-09-19_13-41-07PM_1.log
Patch: /u01/software/RU_24/Parches/36582629/36582781
Log: /u01/app/oracle/product/19.24.0/cfgtoollogs/opatchauto/core/opatch/opatch2024-09-19_13-41-07PM_1.log
Patch: /u01/software/RU_24/Parches/36582629/36587798
Log: /u01/app/oracle/product/19.24.0/cfgtoollogs/opatchauto/core/opatch/opatch2024-09-19_13-41-07PM_1.log
Out of place patching clone home(s) summary
____________________________________________
Host : rac-standby-001
Actual Home : /u01/app/oracle/product/19.22.0
Version:19.0.0.0.0
Clone Home Path : /u01/app/oracle/product/19.24.0
OPatchauto session completed at Thu Sep 19 13:46:45 2024
Time taken to complete the session 8 minutes, 0 second
8 minutos en total. Que ni tan mal… :) Comprobaremos que el nuevo home tiene aplicados los parches correctamente:
[oracle@rac-standby-001 RU_24]$ /u01/app/oracle/product/19.24.0/OPatch/opatch lspatches
36587798;OCW RELEASE UPDATE 19.24.0.0.0 (36587798)
36582781;Database Release Update : 19.24.0.0.240716 (36582781)
36414915;OJVM RELEASE UPDATE: 19.24.0.0.240716 (36414915)
OPatch succeeded.
Ahora llega el paso que haremos el día de la intervención. Éste consistirá en una parada controlada de forma rolling y levantaremos la BBDD con los nuevos binarios.
Tendremos en cuenta si es necesario movimiento previo de servicios (por ejemplo si da servicio la Standby con Real Time Query).
Cambiamos la configuración de BBDD previamente:
[oracle@rac-standby-001 ~]$ srvctl config database -d cdbstb
[oracle@rac-standby-001 ~]$ srvctl modify database -db cdbstb -oraclehome /u01/app/oracle/product/19.24.0
[oracle@rac-standby-001 ~]$ srvctl config database -d cdbstb
[oracle@rac-standby-001 ~]$ srvctl config database -d cdbstb
Nombre único de la base de datos: cdbstb
Nombre de la base de datos:
Directorio raíz de Oracle: /u01/app/oracle/product/19.24.0
Usuario de Oracle: oracle
Spfile: +DG_XXX_DATA/CDBSTB/spfilecdbstb.ora
Archivo de contraseña:
Dominio: xxxxxxx.xxxx
Opciones de inicio: read only
Opciones de parada: immediate
Rol de base de datos: PHYSICAL_STANDBY
Política de gestión: AUTOMATIC
Pools de servidores:
Grupos de discos: DG_XXX_DATA,DG_XXX_REDO,DG_XXX_FRA
Rutas de acceso del punto de montaje:
Servicios: xxxx,xxxxxxx,xxxxxx,xxx,xxxxx,xxxxx
Tipo: RAC
Iniciar simultaneidad:
Parar simultaneidad:
Grupo de OSDBA: dba
Grupo de OSOPER: oper
Instancias de base de datos: cdbstb1,cdbstb2
Nodos configurados: rac-standby-001,rac-standby-002
Crítico para CSS: no
Recuento de CPU: 0
Destino de Memoria: 0
Máximo de Memoria: 0
Número de red por defecto para los servicios de base de datos:
La base de datos está gestionada por el administrador
Ahora simplemente haremos una parada y arranque ordenado de cada una de las instancias, las cuales levantarán en el nuevo HOME.
[oracle@rac-standby-001 ~]$ srvctl stop service -d cdbstb -i cdbstb2
[oracle@rac-standby-001 ~]$ srvctl stop instance -d cdbstb -i cdbstb2
[oracle@rac-standby-001 ~]$ srvctl start instance -d cdbstb -i cdbstb2
[oracle@rac-standby-001 ~]$ srvctl stop service -d cdbstb -i cdbstb1
[oracle@rac-standby-001 ~]$ srvctl stop instance -d cdbstb -i cdbstb1
[oracle@rac-standby-001 ~]$ srvctl start instance -d cdbstb -i cdbstb1
Comprobamos a nivel de instancias que está ejecutándose en el nuevo HOME.
SQL> select * from gv$diag_info where name='ORACLE_HOME';
INST_ID NAME VALUE CON_ID
__________ ______________ __________________________________ _________
1 ORACLE_HOME /u01/app/oracle/product/19.24.0 0
2 ORACLE_HOME /u01/app/oracle/product/19.24.0 0
Modificaremos en oratab en ambos nodos con el nuevo HOME.
grep -i cdbstb /etc/oratab
cdbstb:/u01/app/oracle/product/19.24.0:N
Actualizaremos dependencias que puedan existir tanto en scripts, listener estático en la configuración del listener, targets de cloudcontrol…
En el caso de CloudControl podremos consultar en el repositorio aquellos que se ven afectados de la siguiente manera. Filtrar a conveniencia.
select t.target_name,
p.property_name,
p.property_type,
p.property_value
from sysman.mgmt_target_properties p,sysman.mgmt_targets t
where p.property_name in ('DBVersion','OracleHome')
and t.target_guid=p.target_guid
and property_value = '/u01/app/oracle/product/19.22.0'
and upper(target_name) like '%STB%';
Hasta aquí ya hemos parcheado nuestra BBDD en la Standby, como se puede ver lleva solo el tiempo que nos lleve la parada, y al ser rolling no hemos perdido ni replicación desde primaria ni pérdida de servicio.
Vamos a la parte que me gusta… Abriremos la Standby para probar el datapatch! Así nos anticiparemos a problemas que podramos tener en día que toque el parche en la primaria. Interesante verdad?
Comprobaciones previas:
select action_time,
action,
status,
patch_id,
description
from dba_registry_sqlpatch order by ACTION_TIME desc;
ACTION_TIME ACTION STATUS PATCH_ID DESCRIPTION
__________________________________ _________ __________ ___________ ________________________________________________________
15-MAR-24 03.03.20.144721000 PM APPLY SUCCESS 35943157 Database Release Update : 19.22.0.0.240116 (35943157)
SELECT substrb(Comp_ID,1,10)Comp_ID,
substrb(Status,1,8)Status,
substrb(Version,1,12)Version,
substrb(Comp_Name,1,35)Comp_Name
FROM dba_registry
ORDER by 1,2;
COMP_ID STATUS VERSION COMP_NAME
__________ _________ _____________ _____________________________________
CATALOG VALID 19.0.0.0.0 Oracle Database Catalog Views
CATJAVA VALID 19.0.0.0.0 Oracle Database Java Packages
CATPROC VALID 19.0.0.0.0 Oracle Database Packages and Types
CONTEXT VALID 19.0.0.0.0 Oracle Text
JAVAVM VALID 19.0.0.0.0 JServer JAVA Virtual Machine
OWM VALID 19.0.0.0.0 Oracle Workspace Manager
RAC VALID 19.0.0.0.0 Oracle Real Application Clusters
XDB VALID 19.0.0.0.0 Oracle XML Database
XML VALID 19.0.0.0.0 Oracle XDK
Abrimos nuestra Standby…
DGMGRL> convert database CDBSTB to snapshot standby;
Converting database "cdbstb" to a Snapshot Standby database, please wait...
Database "cdbstb" converted successfully
DGMGRL> show configuration lag
Configuration - Config_DR
Protection Mode: MaxPerformance
Members:
CDBPRIM - Primary database
CDBOCI - Physical standby database
Transport Lag: 0 seconds (computed 1 second ago)
Apply Lag: 1 second (computed 1 second ago)
CDBSTB - Snapshot standby database
Transport Lag: 0 seconds (computed 1 second ago)
Apply Lag: 11 minutes 36 seconds (computed 1 second ago)
Fast-Start Failover: Disabled
Configuration Status:
SUCCESS (status updated 51 seconds ago)
SQL> select DATABASE_ROLE, open_mode from v$database;
DATABASE_ROLE OPEN_MODE
___________________ _____________
SNAPSHOT STANDBY READ WRITE
Comprobamos datapatch… y falla!!! Nos hemos anticipado a la primaria :)
SQL Patching tool version 19.24.0.0.0 Production on Thu Sep 23 08:56:58 2024
Copyright (c) 2012, 2024, Oracle. All rights reserved.
Log file for this invocation: /u01/app/oracle/cfgtoollogs/sqlpatch/sqlpatch_1999219_2024_09_19_16_56_58/sqlpatch_invocation.log
Connecting to database...OK
Gathering database info...done
Note: Datapatch will only apply or rollback SQL fixes for PDBs
that are in an open state, no patches will be applied to closed PDBs.
Please refer to Note: Datapatch: Database 12c Post Patch SQL Automation
(Doc ID 1585822.1)
Bootstrapping registry and package to current versions...done
Determining current state...done
Current state of interim SQL patches:
Interim patch 36414915 (OJVM RELEASE UPDATE: 19.24.0.0.240716 (36414915)):
Binary registry: Installed
PDB CDB$ROOT: Not installed
PDB PDB$SEED: Not installed
PDB PDB_PAYTEF: Not installed
Current state of release update SQL patches:
Binary registry:
19.24.0.0.0 Release_Update 240627235157: Installed
PDB CDB$ROOT:
Applied 19.22.0.0.0 Release_Update 240104023954 successfully on 15-MAR-24 03.03.20.144721 PM
PDB PDB$SEED:
Applied 19.22.0.0.0 Release_Update 240104023954 successfully on 15-MAR-24 03.03.22.322637 PM
PDB PDB_PAYTEF:
Applied 19.22.0.0.0 Release_Update 240104023954 successfully on 15-MAR-24 03.03.22.322637 PM
Adding patches to installation queue and performing prereq checks...done
Installation queue:
For the following PDBs: CDB$ROOT PDB$SEED PDB_PAYTEF
No interim patches need to be rolled back
Patch 36582781 (Database Release Update : 19.24.0.0.240716 (36582781)):
Apply from 19.22.0.0.0 Release_Update 240104023954 to 19.24.0.0.0 Release_Update 240627235157
The following interim patches will be applied:
36414915 (OJVM RELEASE UPDATE: 19.24.0.0.240716 (36414915))
DBD::Oracle::st bind_param failed: ORA-01652: unable to extend temp segment by 128 in tablespace TEMP (DBD ERROR: OCILobWrite in dbd_rebind_ph_lob) [for Statement "BEGIN
INSERT INTO sys.dba_registry_sqlpatch_ru_info
(patch_id,
patch_uid,
patch_descriptor,
ru_version,
ru_build_description,
ru_build_timestamp,
patch_directory)
VALUES
(:patch_id,
:patch_uid,
:patch_descriptor,
:ru_version,
:ru_build_description,
TO_TIMESTAMP(:ru_build_timestamp, 'YYMMDDHH24MISS'),
:patch_directory);
COMMIT;
END;" with ParamValues: :patch_descriptor=OCIXMLTypePtr=SCALAR(0x509fb78), :patch_directory='PK........���X��'
�...b�......36582781_rollback.sql�.]s.ֺ.��+��S�3i÷�dzf��.x�..����s�A.e....�d��. .� nm.�9��(�-=~x��Z�._I.�.�/�?\�.p%....\�O��$ ?K...I.]ǩ.8�{#i�a�3Ky�.3C�u�����㳹.ƫ� �� .~.~P..^..|8=.�۰��E�.���ߋ���.�M��.��.].o....�N..?��O�u.�Y�{��������.oqxvr�xst5_...��p�v���������Y������/.�C���.����~q~*���srg�d��B����.�.�.�.��x���..Kr�`.s'�.�J.����B��R�1]�����t~yut�~��m.����R�..��o.Ϫ��.Q�..�/��%X.y�.�..�dQ.��}¯.�P|��K���~.��.�.._Dt��..D.���x�.^.4�!X.O��.�JB�W.vE��ѣ.��EQ(//.�ϤR��...����w�ӣ�|.6_=�.^..P=�R�KNj.���f�P�.�+��H�(..��.~)>Y<��?���.{.%.�=;�8��d.�/.�/..KjJ��g���.<|*W.�E.�.W.�.�..�ato�8�)�7������U�P.f�
SM�LC�ͱ..�..�S3.�@�V�.����\...PS/�1.[h.#3@U�..�e.uv"2m..Y��X�&�..��[�L)x�.��.�i�.�.��.���� -���ʪn*|�..�0�
�.m8?��.F�O]1My�.�PE�..��P,Ea.�...', :patch_id="36582781", :patch_uid="25751445", :ru_build_description="Release_Update", :ru_build_timestamp="240627235157", :ru_version="19.24.0.0.0"] at /u01/app/oracle/product/19.24.0/sqlpatch/sqlpatch.pm line 5298.
Please refer to MOS Note 1609718.1 and/or the invocation log
/u01/app/oracle/cfgtoollogs/sqlpatch/sqlpatch_1999219_2024_09_23_08_56_58/sqlpatch_invocation.log
for information on how to resolve the above errors.
SQL Patching tool complete on Thu Sep 23 08:57:25 2024
En este caso revisando en soporte:
SOLUTION:
you need to resize the TEMP data file by increasing it in PDB$SEED database
Ya sabemos lo que nos hemos podido encontrar…
Corregimos y volvemos a lanzar
[oracle@rac-standby-001 OPatch]$ cd $ORACLE_HOME/OPatch
[oracle@rac-standby-001 OPatch]$ ./datapatch -verbose
SQL Patching tool version 19.24.0.0.0 Production on Mon Sep 23 11:11:21 2024
Copyright (c) 2012, 2024, Oracle. All rights reserved.
Log file for this invocation: /u01/app/oracle/cfgtoollogs/sqlpatch/sqlpatch_2184895_2024_09_23_11_11_21/sqlpatch_invocation.log
Connecting to database...OK
Gathering database info...done
Note: Datapatch will only apply or rollback SQL fixes for PDBs
that are in an open state, no patches will be applied to closed PDBs.
Please refer to Note: Datapatch: Database 12c Post Patch SQL Automation
(Doc ID 1585822.1)
Bootstrapping registry and package to current versions...done
Determining current state...done
Current state of interim SQL patches:
Interim patch 36414915 (OJVM RELEASE UPDATE: 19.24.0.0.240716 (36414915)):
Binary registry: Installed
PDB CDB$ROOT: Not installed
PDB PDB$SEED: Not installed
PDB PDB_PAYTEF: Not installed
Current state of release update SQL patches:
Binary registry:
19.24.0.0.0 Release_Update 240627235157: Installed
PDB CDB$ROOT:
Applied 19.22.0.0.0 Release_Update 240104023954 successfully on 15-MAR-24 03.03.20.144721 PM
PDB PDB$SEED:
Applied 19.22.0.0.0 Release_Update 240104023954 successfully on 15-MAR-24 03.03.22.322637 PM
PDB PDB_PAYTEF:
Applied 19.22.0.0.0 Release_Update 240104023954 successfully on 15-MAR-24 03.03.22.322637 PM
Adding patches to installation queue and performing prereq checks...done
Installation queue:
For the following PDBs: CDB$ROOT PDB$SEED PDB_PAYTEF
No interim patches need to be rolled back
Patch 36582781 (Database Release Update : 19.24.0.0.240716 (36582781)):
Apply from 19.22.0.0.0 Release_Update 240104023954 to 19.24.0.0.0 Release_Update 240627235157
The following interim patches will be applied:
36414915 (OJVM RELEASE UPDATE: 19.24.0.0.240716 (36414915))
Installing patches...
Patch installation complete. Total patches installed: 6
Validating logfiles...done
Patch 36582781 apply (pdb CDB$ROOT): SUCCESS
logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/36582781/25751445/36582781_apply_CDBPRO_CDBROOT_2024Sep23_11_12_06.log (no errors)
Patch 36414915 apply (pdb CDB$ROOT): SUCCESS
logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/36414915/25700138/36414915_apply_CDBPRO_CDBROOT_2024Sep23_11_12_06.log (no errors)
Patch 36582781 apply (pdb PDB$SEED): SUCCESS
logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/36582781/25751445/36582781_apply_CDBPRO_PDBSEED_2024Sep23_11_13_14.log (no errors)
Patch 36414915 apply (pdb PDB$SEED): SUCCESS
logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/36414915/25700138/36414915_apply_CDBPRO_PDBSEED_2024Sep23_11_13_04.log (no errors)
Patch 36582781 apply (pdb PDB_PAYTEF): SUCCESS
logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/36582781/25751445/36582781_apply_CDBPRO_PDB_PAYTEF_2024Sep23_11_13_04.log (no errors)
Patch 36414915 apply (pdb PDB_PAYTEF): SUCCESS
logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/36414915/25700138/36414915_apply_CDBPRO_PDB_PAYTEF_2024Sep23_11_13_04.log (no errors)
SQL Patching tool complete on Mon Sep 23 11:14:00 2024
Listo! Compilamos objetos inválidos, revisamos logs y comprobamos en todos los contenedores:
-- Compruebo para todos los contenedores
select action_time,
action,
status,
patch_id,
description
from dba_registry_sqlpatch order by ACTION_TIME desc;
ACTION_TIME ACTION STATUS PATCH_ID DESCRIPTION
__________________________________ _________ __________ ___________ ________________________________________________________
23-SEP-24 11.13.36.238648000 AM APPLY SUCCESS 36582781 Database Release Update : 19.24.0.0.240716 (36582781)
23-SEP-24 11.13.14.443894000 AM APPLY SUCCESS 36414915 OJVM RELEASE UPDATE: 19.24.0.0.240716 (36414915)
15-MAR-24 03.03.22.322637000 PM APPLY SUCCESS 35943157 Database Release Update : 19.22.0.0.240116 (35943157)
SELECT substrb(Comp_ID,1,10)Comp_ID,
substrb(Status,1,8)Status,
substrb(Version,1,12)Version,
substrb(Comp_Name,1,35)Comp_Name
FROM DBA_Registry
ORDER by 1,2;
COMP_ID STATUS VERSION COMP_NAME
__________ _________ _____________ _____________________________________
CATALOG VALID 19.0.0.0.0 Oracle Database Catalog Views
CATJAVA VALID 19.0.0.0.0 Oracle Database Java Packages
CATPROC VALID 19.0.0.0.0 Oracle Database Packages and Types
CONTEXT VALID 19.0.0.0.0 Oracle Text
JAVAVM VALID 19.0.0.0.0 JServer JAVA Virtual Machine
OWM VALID 19.0.0.0.0 Oracle Workspace Manager
RAC VALID 19.0.0.0.0 Oracle Real Application Clusters
XDB VALID 19.0.0.0.0 Oracle XML Database
XML VALID 19.0.0.0.0 Oracle XDK
SQL> select owner, object_name from dba_objects where status !='VALID';
no se ha seleccionado ninguna fila
Lanzamos pruebas pertinentes a nivel de aplicativo si es necesario. En mi caso tengo un servicio creado que únicamente levanta cuando la BBDD es Snapshot Standby.
Volvemos a convertir la Standby a Physical y aquí no ha pasado nada.
DGMGRL> convert database CDBSTB to physical standby;
Converting database "cdbstb" to a Physical Standby database, please wait...
Oracle Clusterware is restarting database "CDBSTB" ...
Connected to "cdbstb"
Continuing to convert database "cdbstb" ...
Database "cdbstb" converted successfully
DGMGRL> show configuration lag;
Configuration - COnfig_DR
Protection Mode: MaxPerformance
Members:
CDBPRIM - Primary database
CDBOCI - Physical standby database
Transport Lag: 0 seconds (computed 0 seconds ago)
Apply Lag: 0 seconds (computed 0 seconds ago)
CDBSTB - Physical standby database
Transport Lag: 0 seconds (computed 0 seconds ago)
Apply Lag: 0 seconds (computed 0 seconds ago)
Fast-Start Failover: Disabled
Configuration Status:
SUCCESS (status updated 15 seconds ago)
A que ahora se respira tranquilidad relativo al día del parcheo en la primaria?
Solo nos quedaría elminar los binarios antiguos, pero eso ya otro día… primero dejemos pasa unos días.
export ORACLE_HOME=/u01/app/oracle/product/19.22.0
cd /u01/app/oracle/product/19.22.0/deinstall/
./deinstall
Un saludo y happy patching!!
