Atualizando Banco de Dados 11G para o 19C através do ODACLI
Como é de conhecimentos de todos, sabemos que o suporte ao banco de dados 11G encerrou dia 31 de dezembro de 2020. Mediante isso, decidi escrever este artigo de como atualizar o banco de dados 11G para o 19C utilizando o ODACLI. Lembrando que este procedimento por enquanto está disponível apenas para Single Instance. Antes de executar quaisquer dos procedimentos abaixo, realize o backup de sua base. Para o upgrade… vamos executar as seguintes etapas.
Criar o Banco de dados UPTO19C na versão 11.2.0.4.
Criar um dbhome na versão 19.8.0.0.
Duplicar o banco para a instancia UPTO19C ainda na versão 11.2.0.4.
Atualizar o Banco de dados 11G para o 19C.
Então vamos dar início ao procedimento:
Parte 1.
[root@oda-x8m ~]# odacli create-database -n UPTO19C -u UPTO19C -r ACFS -s odb2 -cs WE8MSWIN1252 -v 11.2.0.4
Password for SYS,SYSTEM and PDB Admin:
Job details
----------------------------------------------------------------
ID: 54c154ef-61bc-4aca-a0a8-7cdc9bfec42f
Description: Database service creation with db name: UPTO19C
Status: Created
Created: January 21, 2021 10:49:39 PM BRT
Message:
Task Name Start Time End Time Status
---------------------------------------- ----------------------------------- ----------------------------------- ----------
root@oda-x8m ~]# odacli describe-job -i 54c154ef-61bc-4aca-a0a8-7cdc9bfec42f
Job details
----------------------------------------------------------------
ID: 54c154ef-61bc-4aca-a0a8-7cdc9bfec42f
Description: Database service creation with db name: UPTO19C
Status: Success
Created: January 21, 2021 10:49:39 PM BRT
Message:
Task Name Start Time End Time Status
---------------------------------------- ----------------------------------- ----------------------------------- ----------
Validating dbHome available space January 21, 2021 10:49:46 PM BRT January 21, 2021 10:49:46 PM BRT Success
Setting up ssh equivalance January 21, 2021 10:49:47 PM BRT January 21, 2021 10:49:47 PM BRT Success
Create DATA FileGroup DATUPTO19C January 21, 2021 10:49:47 PM BRT January 21, 2021 10:49:48 PM BRT Success
Create RECO FileGroup RDOUPTO19C January 21, 2021 10:49:48 PM BRT January 21, 2021 10:49:49 PM BRT Success
Creating volume datUPTO19C January 21, 2021 10:49:49 PM BRT January 21, 2021 10:50:12 PM BRT Success
Creating volume rdoUPTO19C January 21, 2021 10:50:12 PM BRT January 21, 2021 10:50:33 PM BRT Success
Creating ACFS filesystem for DATA January 21, 2021 10:50:33 PM BRT January 21, 2021 10:50:50 PM BRT Success
Creating ACFS filesystem for RECO January 21, 2021 10:50:50 PM BRT January 21, 2021 10:51:08 PM BRT Success
Validating dbHome available space January 21, 2021 10:51:08 PM BRT January 21, 2021 10:51:08 PM BRT Success
Creating DbHome Directory January 21, 2021 10:51:08 PM BRT January 21, 2021 10:51:08 PM BRT Success
Extract DB clones January 21, 2021 10:51:08 PM BRT January 21, 2021 10:51:55 PM BRT Success
Clone Db home January 21, 2021 10:51:55 PM BRT January 21, 2021 10:52:50 PM BRT Success
Enable DB options January 21, 2021 10:52:50 PM BRT January 21, 2021 10:52:55 PM BRT Success
Run Root DB scripts January 21, 2021 10:52:55 PM BRT January 21, 2021 10:52:55 PM BRT Success
configuring Net Security January 21, 2021 10:52:58 PM BRT January 21, 2021 10:52:58 PM BRT Success
Database Service creation January 21, 2021 10:52:59 PM BRT January 21, 2021 10:57:54 PM BRT Success
Database Creation January 21, 2021 10:52:59 PM BRT January 21, 2021 10:57:22 PM BRT Success
Place SnapshotCtrlFile in sharedLoc January 21, 2021 10:57:22 PM BRT January 21, 2021 10:57:24 PM BRT Success
updating the Database version January 21, 2021 10:57:52 PM BRT January 21, 2021 10:57:54 PM BRT Success
create Users tablespace January 21, 2021 10:57:54 PM BRT January 21, 2021 10:57:56 PM BRT Success
Clear all listeners from Databse January 21, 2021 10:57:56 PM BRT January 21, 2021 10:57:56 PM BRT Success
Como podemos ver, a primeira etapa foi concluída com sucesso. Agora vamos para a próxima etapa.
Parte 2.
[root@oda-x8m ~]# odacli create-dbhome -de EE -v 19.8.0.0
Job details
----------------------------------------------------------------
ID: c22ae319-1dd3-4527-a9c0-984fd80b3899
Description: Database Home OraDB19000_home3 creation with version :19.8.0.0
Status: Created
Created: January 21, 2021 11:01:23 PM BRT
Message: Create Database Home
Task Name Start Time End Time Status
---------------------------------------- ----------------------------------- ----------------------------------- ----------
[root@oda-x8m ~]# odacli describe-job -i c22ae319-1dd3-4527-a9c0-984fd80b3899
Job details
----------------------------------------------------------------
ID: c22ae319-1dd3-4527-a9c0-984fd80b3899
Description: Database Home OraDB19000_home3 creation with version :19.8.0.0
Status: Success
Created: January 21, 2021 11:01:23 PM BRT
Message: Create Database Home
Task Name Start Time End Time Status
---------------------------------------- ----------------------------------- ----------------------------------- ----------
Setting up ssh equivalance January 21, 2021 11:01:23 PM BRT January 21, 2021 11:01:23 PM BRT Success
Removing ssh keys January 21, 2021 11:01:23 PM BRT January 21, 2021 11:03:57 PM BRT Success
Validating dbHome available space January 21, 2021 11:01:23 PM BRT January 21, 2021 11:01:23 PM BRT Success
Creating DbHome Directory January 21, 2021 11:01:23 PM BRT January 21, 2021 11:01:23 PM BRT Success
Extract DB clones January 21, 2021 11:01:23 PM BRT January 21, 2021 11:02:41 PM BRT Success
Clone Db home January 21, 2021 11:02:41 PM BRT January 21, 2021 11:03:35 PM BRT Success
Enable DB options January 21, 2021 11:03:35 PM BRT January 21, 2021 11:03:53 PM BRT Success
Run Root DB scripts January 21, 2021 11:03:53 PM BRT January 21, 2021 11:03:53 PM BRT Success
Banco de dados criado e dbhome 19c finalizado, vamos averiguar como que estão as coisas:
[root@oda-x8m ~]# odacli list-databases
ID DB Name DB Type DB Version CDB Class Shape Storage Status DbHomeID
---------------------------------------- ---------- -------- -------------------- ---------- -------- -------- ---------- ------------ ----------------------------------------
ba14cf8b-676b-4a69-a576-413e31ac6098 UPTO19C Si 11.2.0.4.200714 false Oltp Odb2 Acfs Configured a60a4e83-f265-4fe0-b22a-82770743293c
[root@oda-x8m ~]# odacli list-dbhomes
ID Name DB Version Home Location Status
---------------------------------------- -------------------- ---------------------------------------- --------------------------------------------- ----------
a60a4e83-f265-4fe0-b22a-82770743293c OraDB11204_home5 11.2.0.4.200714 /u01/app/oracle/product/11.2.0.4/dbhome_5 Configured
3920f434-0a4b-44f1-9a31-80bf57351586 OraDB19000_home3 19.8.0.0.200714 /u01/app/oracle/product/19.0.0.0/dbhome_3 Configured
Podemos ver que o dbhome com o final “3c” está vinculado ao banco UPTO19C e que o dbhome 19C com final “86” será nosso target na atualização.
Parte 3.
Agora vamos para parte que duplicamos o database de produção para o banco UPTO19C. Não entrarei em detalhes de como fazer o duplicate neste artigo para não ficar muito longo e perdemos o foco.
Recovery Manager: Release 11.2.0.4.0 - Production on Wed Jan 20 15:12:38 2021
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: PROD (DBID=1261511516)
connected to auxiliary database: UPTO19C (not mounted)
XXXXXXXXXXXXXX
XXXXXXXXXXXXXX
XXXXXXXXXXXXXX
XXXXXXXXXXXXXX
XXXXXXXXXXXXXX
contents of Memory Script:
{
Alter clone database open resetlogs;
}
executing Memory Script
database opened
Finished Duplicate Db at 20/01/2021 16:31:25
Pronto, agora nosso banco UPTO19C tem 8TB e podemos iniciar o procedimento.
SQL*Plus: Release 11.2.0.4.0 Production on Fri Jan 22 00:41:16 2021
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, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
SQL> @dbsize
DB_SIZE_IN_MEGAS
----------------
7,827,649
Antes de iniciar o upgrade gostaria de mencionar um processo anterior que deu falha.
[root@oda-x8m ~]# odacli describe-job -i 45191d6f-ca5f-43cb-9fbe-a155f8432dc2
Job details
----------------------------------------------------------------
ID: 45191d6f-ca5f-43cb-9fbe-a155f8432dc2
Description: Database service upgrade with db ids: [b5157b23-a1c7-49d5-b58a-f23dffdf5ab6]
Status: Failure
Created: January 20, 2021 5:05:27 PM BRT
Message: DCS-10001:Internal error encountered: Databases failed to upgrade are : [b5157b23-a1c7-49d5-b58a-f23dffdf5ab6].
Task Name Start Time End Time Status
---------------------------------------- ----------------------------------- ----------------------------------- ----------
Database Service Upgradation January 20, 2021 5:05:27 PM BRT January 20, 2021 5:06:35 PM BRT Failure
Database Service Upgradation January 20, 2021 5:05:27 PM BRT January 20, 2021 5:06:35 PM BRT Failure
Setting up ssh equivalance January 20, 2021 5:05:27 PM BRT January 20, 2021 5:05:27 PM BRT Success
Clear local and remote listeners from Databse January 20, 2021 5:05:27 PM BRT January 20, 2021 5:05:28 PM BRT Success
Run catnoamd.sql January 20, 2021 5:05:28 PM BRT January 20, 2021 5:05:29 PM BRT Success
Database Upgrade January 20, 2021 5:05:29 PM BRT January 20, 2021 5:06:34 PM BRT Success
Deleting and creating the spfile and pfile January 20, 2021 5:06:34 PM BRT January 20, 2021 5:06:35 PM BRT Success
Removing ssh keys January 20, 2021 5:06:35 PM BRT January 20, 2021 5:06:35 PM BRT Success
Database Upgrade Validation January 20, 2021 5:06:35 PM BRT January 20, 2021 5:06:35 PM BRT Failure
Depois de várias horas olhando os logs encontrei a causa do erro.
Como alguns amigos dizem… “Vá para o alert e lá estarás a verdade” e depois de um tempo encontrei isso aqui:
joxcsys: release mismatch 11.2.0.4.190716 in database (classes.bin) vs 11.2.0.4.200714 in executable.
Caso este seja seu problema, execute:
Database em modo OPEN normal.
sqlplus / as sysdba
@$ORACLE_HOME/rdbms/admin/utlu112s.sql
@$ORACLE_HOME/rdbms/admin/catuppst.sql
@$ORACLE_HOME/rdbms/admin/utlrp.sql
Caso exista alguma entrada "Manual" no arquivo listener.ora vinculada ao database que será atualizado, deverá ser removida para a atualização.
Pronto, depois disso você poderá executar o upgrade sem problemas, caso o seu problema seja este.
Parte 4.
Vamos dar continuidade ao procedimento agora.
[root@oda-x8m ~]# odacli list-databases
ID DB Name DB Type DB Version CDB Class Shape Storage Status DbHomeID
---------------------------------------- ---------- -------- -------------------- ---------- -------- -------- ---------- ------------ ----------------------------------------
ba14cf8b-676b-4a69-a576-413e31ac6098 UPTO19C Si 11.2.0.4.200714 false Oltp Odb2 Acfs Configured a60a4e83-f265-4fe0-b22a-82770743293c
[root@oda-x8m ~]# odacli list-dbhomes
ID Name DB Version Home Location Status
---------------------------------------- -------------------- ---------------------------------------- --------------------------------------------- ----------
a60a4e83-f265-4fe0-b22a-82770743293c OraDB11204_home5 11.2.0.4.200714 /u01/app/oracle/product/11.2.0.4/dbhome_5 Configured
3920f434-0a4b-44f1-9a31-80bf57351586 OraDB19000_home3 19.8.0.0.200714 /u01/app/oracle/product/19.0.0.0/dbhome_3 Configured
Explicando o comando:
odacli upgrade-database -i “ID do Banco de Dados no DCS, que é o ba14cf8b-676b-4a69-a576-413e31ac6098” -from “Home id da versão 11G, onde o banco está vinculado” -to “Home id da versão 19C”
[root@oda-x8m ~]# odacli upgrade-database -i ba14cf8b-676b-4a69-a576-413e31ac6098 -from a60a4e83-f265-4fe0-b22a-82770743293c -to 3920f434-0a4b-44f1-9a31-80bf57351586
{
"jobId" : "82020630-2ab0-4f64-9054-4490a824e62c",
"status" : "Created",
"message" : null,
"reports" : [ ],
"createTimestamp" : "January 27, 2021 15:00:19 PM BRT",
"resourceList" : [ ],
"description" : "Database service upgrade with db ids: [ba14cf8b-676b-4a69-a576-413e31ac6098]",
"updatedTime" : "January 27, 2021 15:00:19 PM BRT"
}
Depois de 31 minutos nosso database está atualizado:
[root@oda-x8m ~]# odacli describe-job -i 82020630-2ab0-4f64-9054-4490a824e62c
Job details
----------------------------------------------------------------
ID: 82020630-2ab0-4f64-9054-4490a824e62c
Description: Database service upgrade with db ids: [ba14cf8b-676b-4a69-a576-413e31ac6098]
Status: Success
Created: January 27, 2021 3:00:19 PM BRT
Message:
Task Name Start Time End Time Status
---------------------------------------- ----------------------------------- ----------------------------------- ----------
Setting up ssh equivalance January 27, 2021 3:00:19 PM BRT January 27, 2021 3:00:19 PM BRT Success
Clear local and remote listeners from Databse January 27, 2021 3:00:19 PM BRT January 27, 2021 3:00:19 PM BRT Success
Run catnoamd.sql January 27, 2021 3:00:20 PM BRT January 27, 2021 3:00:25 PM BRT Success
Database Upgrade January 27, 2021 3:00:25 PM BRT January 27, 2021 3:30:19 PM BRT Success
Deleting and creating the spfile and pfile January 27, 2021 3:30:46 PM BRT January 27, 2021 3:31:32 PM BRT Success
Removing ssh keys January 27, 2021 3:31:32 PM BRT January 27, 2021 3:31:34 PM BRT Success
Database Upgrade Validation January 27, 2021 3:31:34 PM BRT January 27, 2021 3:31:34 PM BRT Success
Podemos ver que o database UPTO19C agora está vinculado ao dbhome 19C:
[root@oda-x8m ~]# odacli list-databases
ID DB Name DB Type DB Version CDB Class Shape Storage Status DbHomeID
---------------------------------------- ---------- -------- -------------------- ---------- -------- -------- ---------- ------------ ----------------------------------------
ba14cf8b-676b-4a69-a576-413e31ac6098 UPTO19C Si 19.8.0.0.200714 false Oltp Odb2 Acfs Configured 3920f434-0a4b-44f1-9a31-80bf57351586
[root@oda-x8m ~]# odacli list-dbhomes
ID Name DB Version Home Location Status
---------------------------------------- -------------------- ---------------------------------------- --------------------------------------------- ----------
a60a4e83-f265-4fe0-b22a-82770743293c OraDB11204_home5 11.2.0.4.200714 /u01/app/oracle/product/11.2.0.4/dbhome_5 Configured
3920f434-0a4b-44f1-9a31-80bf57351586 OraDB19000_home3 19.8.0.0.200714 /u01/app/oracle/product/19.0.0.0/dbhome_3 Configured
E que o Database UPTO19C foi atualizado com sucesso:
[oracle@oda-x8m ~]$ . oraenv
ORACLE_SID = [oracle] ? UPTO19C
The Oracle base has been set to /u01/app/oracle
[oracle@oda-x8m ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Wed Jan 27 16:04:47 2021
Version 19.8.0.0.0
Copyright (c) 1982, 2020, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.8.0.0.0
SQL> select * from v$version ;
BANNER BANNER_FULL BANNER_LEGACY CON_ID
------------------------------------------------------------ ------------------------------------------------------------ ------------------------------------------------------ ----------
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Oracle Database 19c Enterprise Edition Release 19.0.0. 0
Production Production 0.0 - Production
Version 19.8.0.0.0
SQL> select count (*) from dba_objects where status='INVALID' ;
COUNT(*)
----------
0
