Esta semana estava conversando com alguns amigos DBAs e me surgiu a dúvida, é possível fazer upgrade do Oracle database XE 11g para Standard Edition 11g? Ninguém soube responder se funcionaria na prática, esta pergunta ficou martelando na minha cabeça, então montei um laboratório para realizar os testes e acabar com esta dúvida.
Fui pesquisar no MOS, encontrei a nota Doc ID 1924556.1, esta nota afirma que é possível realizar o upgrade da XE para EE usando o DBUA, mas não encontrei nada relacionado ao upgrade para Standard Edition, sabemos que possivelmente algumas features que existem na XE podem não existir na Standard Edition, e por essas limitações apenas o upgrade para EE é recomendado e homologado e suportado pela Oracle.
Obs: Não realizem esse upgrade em produção, lembrem que não é homologado ou suportado pela Oracle o upgrade de XE para Standard Edition.
Entendo que as limitações de features possam impossibilitar a compatibilidade e o upgrade do Oracle XE para Santandard Edition, mas não custa nada fazer o teste em um laboratório controlado deste upgrade. Abaixo segue o processo e validação que fiz antes e depois do upgrade da XE para Santandard Edition:
1- Utilizei um Oracle Database XE 11.2.0.2.0, abaixo seguem as validações do dicionário atual antes do upgrade para 11.2.0.4.0 Standard Edtion.
DATABASE_HOST DB_NAME DB_UNIQUE_NAME DATABASE_ROLE Version OPEN_MODE LOG_MODE STARTUP_TIME
------------------------- ---------------- ------------------------- ---------------- ----------------- -------------------- ------------ --------------------
lab11g XE XE PRIMARY 11.2.0.2.0 READ WRITE NOARCHIVELOG 02/09/2022 18:59:55
-- DICIONARIO
SET PAGESIZE 60
SET LINESIZE 300
SET VERIFY OFF
COL comp_name FOR a44 HEA 'Component'
COL version FOR a17 HEA 'Version'
COL status FOR a17 HEA 'Status'
SELECT comp_name, version, status FROM dba_registry;
Component Version Status
-------------------------------------------- ----------------- -----------------
Oracle Application Express 4.0.2.00.09 VALID
Oracle XML Database 11.2.0.2.0 VALID
Oracle Text 11.2.0.2.0 VALID
Oracle Database Catalog Views 11.2.0.2.0 VALID
Oracle Database Packages and Types 11.2.0.2.0 VALID
SQL> spool hcheck.log
SQL> @hcheck.sql
HCheck Version 07MAY18 on 02-SEP-2022 19:12:01
----------------------------------------------
Catalog Version 11.2.0.2.0 (1102000200)
db_name: XE
Catalog Fixed
Procedure Name Version Vs Release Timestamp
Result
------------------------------ ... ---------- -- ---------- --------------
------
.- LobNotInObj ... 1102000200 <= *All Rel* 09/02 19:12:01 PASS
.- MissingOIDOnObjCol ... 1102000200 <= *All Rel* 09/02 19:12:01 PASS
.- SourceNotInObj ... 1102000200 <= *All Rel* 09/02 19:12:01 PASS
.- OversizedFiles ... 1102000200 <= *All Rel* 09/02 19:12:01 PASS
.- PoorDefaultStorage ... 1102000200 <= *All Rel* 09/02 19:12:01 PASS
.- PoorStorage ... 1102000200 <= *All Rel* 09/02 19:12:01 PASS
.- TabPartCountMismatch ... 1102000200 <= *All Rel* 09/02 19:12:01 PASS
.- OrphanedTabComPart ... 1102000200 <= *All Rel* 09/02 19:12:01 PASS
.- MissingSum$ ... 1102000200 <= *All Rel* 09/02 19:12:01 PASS
.- MissingDir$ ... 1102000200 <= *All Rel* 09/02 19:12:01 PASS
.- DuplicateDataobj ... 1102000200 <= *All Rel* 09/02 19:12:02 PASS
.- ObjSynMissing ... 1102000200 <= *All Rel* 09/02 19:12:02 PASS
.- ObjSeqMissing ... 1102000200 <= *All Rel* 09/02 19:12:02 PASS
.- OrphanedUndo ... 1102000200 <= *All Rel* 09/02 19:12:02 PASS
.- OrphanedIndex ... 1102000200 <= *All Rel* 09/02 19:12:02 PASS
.- OrphanedIndexPartition ... 1102000200 <= *All Rel* 09/02 19:12:02 PASS
.- OrphanedIndexSubPartition ... 1102000200 <= *All Rel* 09/02 19:12:02 PASS
.- OrphanedTable ... 1102000200 <= *All Rel* 09/02 19:12:02 PASS
.- OrphanedTablePartition ... 1102000200 <= *All Rel* 09/02 19:12:02 PASS
.- OrphanedTableSubPartition ... 1102000200 <= *All Rel* 09/02 19:12:02 PASS
.- MissingPartCol ... 1102000200 <= *All Rel* 09/02 19:12:02 PASS
.- OrphanedSeg$ ... 1102000200 <= *All Rel* 09/02 19:12:02 PASS
.- OrphanedIndPartObj# ... 1102000200 <= *All Rel* 09/02 19:12:02 PASS
.- DuplicateBlockUse ... 1102000200 <= *All Rel* 09/02 19:12:02 PASS
.- FetUet ... 1102000200 <= *All Rel* 09/02 19:12:02 PASS
.- Uet0Check ... 1102000200 <= *All Rel* 09/02 19:12:02 PASS
.- SeglessUET ... 1102000200 <= *All Rel* 09/02 19:12:02 PASS
.- BadInd$ ... 1102000200 <= *All Rel* 09/02 19:12:02 PASS
.- BadTab$ ... 1102000200 <= *All Rel* 09/02 19:12:02 PASS
.- BadIcolDepCnt ... 1102000200 <= *All Rel* 09/02 19:12:02 PASS
.- ObjIndDobj ... 1102000200 <= *All Rel* 09/02 19:12:02 PASS
.- TrgAfterUpgrade ... 1102000200 <= *All Rel* 09/02 19:12:02 PASS
.- ObjType0 ... 1102000200 <= *All Rel* 09/02 19:12:02 PASS
.- BadOwner ... 1102000200 <= *All Rel* 09/02 19:12:02 PASS
.- StmtAuditOnCommit ... 1102000200 <= *All Rel* 09/02 19:12:02 PASS
.- BadPublicObjects ... 1102000200 <= *All Rel* 09/02 19:12:02 PASS
.- BadSegFreelist ... 1102000200 <= *All Rel* 09/02 19:12:02 PASS
.- BadDepends ... 1102000200 <= *All Rel* 09/02 19:12:02 PASS
.- CheckDual ... 1102000200 <= *All Rel* 09/02 19:12:02 PASS
.- ObjectNames ... 1102000200 <= *All Rel* 09/02 19:12:02 PASS
.- BadCboHiLo ... 1102000200 <= *All Rel* 09/02 19:12:02 PASS
.- ChkIotTs ... 1102000200 <= *All Rel* 09/02 19:12:02 PASS
.- NoSegmentIndex ... 1102000200 <= *All Rel* 09/02 19:12:02 PASS
.- BadNextObject ... 1102000200 <= *All Rel* 09/02 19:12:02 PASS
.- DroppedROTS ... 1102000200 <= *All Rel* 09/02 19:12:02 PASS
.- FilBlkZero ... 1102000200 <= *All Rel* 09/02 19:12:02 PASS
.- DbmsSchemaCopy ... 1102000200 <= *All Rel* 09/02 19:12:02 PASS
.- OrphanedObjError ... 1102000200 > 1102000000 09/02 19:12:02 PASS
.- ObjNotLob ... 1102000200 <= *All Rel* 09/02 19:12:02 PASS
.- MaxControlfSeq ... 1102000200 <= *All Rel* 09/02 19:12:02 PASS
.- SegNotInDeferredStg ... 1102000200 > 1102000000 09/02 19:12:02 PASS
.- SystemNotRfile1 ... 1102000200 > 902000000 09/02 19:12:02 PASS
.- DictOwnNonDefaultSYSTEM ... 1102000200 <= *All Rel* 09/02 19:12:02 PASS
.- OrphanTrigger ... 1102000200 <= *All Rel* 09/02 19:12:02 PASS
.- ObjNotTrigger ... 1102000200 <= *All Rel* 09/02 19:12:02 PASS
---------------------------------------
02-SEP-2022 19:12:02 Elapsed: 1 secs
---------------------------------------
Found 0 potential problem(s) and 0 warning(s)
PL/SQL procedure successfully completed.
Statement processed.
Complete output is in trace file:
/u01/app/oracle/diag/rdbms/xe/XE/trace/XE_ora_3656_HCHECK.trc2 – Realizei a instalação do binário do Oracle database Standard Edition 11.2.0.4.0, após a instalação realizei o upgrade usando o dbua.
3 – Validação do dicionário após o upgrade já na versão Oracle Standard Edition 11.2.0.4.0.
[oracle@lab11g database]$ dbua
Database upgrade has been completed successfully, and the database is ready to use.
The following document describes important behavioral changes from previous database releases:
/u01/app/oracle/product/11.2.0/db_1/assistants/dbua/doc/DefaultBehaviorChanges.html
-- DICIONARIO
DATABASE_HOST DB_NAME DB_UNIQUE_NAME DATABASE_ROLE VERSION OPEN_MODE LOG_MODE STARTUP_TIME
------------------------- ---------------- ------------------------- ---------------- ----------------- -------------------- ------------ --------------------
lab11g XE XE PRIMARY 11.2.0.4.0 READ WRITE NOARCHIVELOG 02/09/2022 19:36:08
Component Version Status
-------------------------------------------- ----------------- -----------------
Oracle Application Express 4.0.2.00.09 VALID
Oracle XML Database 11.2.0.4.0 VALID
Oracle Text 11.2.0.4.0 VALID
Oracle Database Catalog Views 11.2.0.4.0 VALID
Oracle Database Packages and Types 11.2.0.4.0 VALID
Connected to:
Oracle Database 11g Release 11.2.0.4.0 - 64bit Production
SQL> spool hcheck.log
SQL> @hcheck.sql
HCheck Version 07MAY18 on 02-SEP-2022 19:41:27
----------------------------------------------
Catalog Version 11.2.0.4.0 (1102000400)
db_name: XE
Catalog Fixed
Procedure Name Version Vs Release Timestamp
Result
------------------------------ ... ---------- -- ---------- --------------
------
.- LobNotInObj ... 1102000400 <= *All Rel* 09/02 19:41:27 PASS
.- MissingOIDOnObjCol ... 1102000400 <= *All Rel* 09/02 19:41:27 PASS
.- SourceNotInObj ... 1102000400 <= *All Rel* 09/02 19:41:27 PASS
.- OversizedFiles ... 1102000400 <= *All Rel* 09/02 19:41:27 PASS
.- PoorDefaultStorage ... 1102000400 <= *All Rel* 09/02 19:41:27 PASS
.- PoorStorage ... 1102000400 <= *All Rel* 09/02 19:41:27 PASS
.- TabPartCountMismatch ... 1102000400 <= *All Rel* 09/02 19:41:27 PASS
.- OrphanedTabComPart ... 1102000400 <= *All Rel* 09/02 19:41:27 PASS
.- MissingSum$ ... 1102000400 <= *All Rel* 09/02 19:41:27 PASS
.- MissingDir$ ... 1102000400 <= *All Rel* 09/02 19:41:27 PASS
.- DuplicateDataobj ... 1102000400 <= *All Rel* 09/02 19:41:27 PASS
.- ObjSynMissing ... 1102000400 <= *All Rel* 09/02 19:41:27 PASS
.- ObjSeqMissing ... 1102000400 <= *All Rel* 09/02 19:41:27 PASS
.- OrphanedUndo ... 1102000400 <= *All Rel* 09/02 19:41:27 PASS
.- OrphanedIndex ... 1102000400 <= *All Rel* 09/02 19:41:27 PASS
.- OrphanedIndexPartition ... 1102000400 <= *All Rel* 09/02 19:41:27 PASS
.- OrphanedIndexSubPartition ... 1102000400 <= *All Rel* 09/02 19:41:27 PASS
.- OrphanedTable ... 1102000400 <= *All Rel* 09/02 19:41:27 PASS
.- OrphanedTablePartition ... 1102000400 <= *All Rel* 09/02 19:41:27 PASS
.- OrphanedTableSubPartition ... 1102000400 <= *All Rel* 09/02 19:41:27 PASS
.- MissingPartCol ... 1102000400 <= *All Rel* 09/02 19:41:27 PASS
.- OrphanedSeg$ ... 1102000400 <= *All Rel* 09/02 19:41:27 PASS
.- OrphanedIndPartObj# ... 1102000400 <= *All Rel* 09/02 19:41:27 PASS
.- DuplicateBlockUse ... 1102000400 <= *All Rel* 09/02 19:41:27 PASS
.- FetUet ... 1102000400 <= *All Rel* 09/02 19:41:27 PASS
.- Uet0Check ... 1102000400 <= *All Rel* 09/02 19:41:27 PASS
.- SeglessUET ... 1102000400 <= *All Rel* 09/02 19:41:27 PASS
.- BadInd$ ... 1102000400 <= *All Rel* 09/02 19:41:27 PASS
.- BadTab$ ... 1102000400 <= *All Rel* 09/02 19:41:27 PASS
.- BadIcolDepCnt ... 1102000400 <= *All Rel* 09/02 19:41:27 PASS
.- ObjIndDobj ... 1102000400 <= *All Rel* 09/02 19:41:27 PASS
.- TrgAfterUpgrade ... 1102000400 <= *All Rel* 09/02 19:41:27 PASS
.- ObjType0 ... 1102000400 <= *All Rel* 09/02 19:41:27 PASS
.- BadOwner ... 1102000400 <= *All Rel* 09/02 19:41:27 PASS
.- StmtAuditOnCommit ... 1102000400 <= *All Rel* 09/02 19:41:27 PASS
.- BadPublicObjects ... 1102000400 <= *All Rel* 09/02 19:41:27 PASS
.- BadSegFreelist ... 1102000400 <= *All Rel* 09/02 19:41:27 PASS
.- BadDepends ... 1102000400 <= *All Rel* 09/02 19:41:27 PASS
.- CheckDual ... 1102000400 <= *All Rel* 09/02 19:41:27 PASS
.- ObjectNames ... 1102000400 <= *All Rel* 09/02 19:41:27 PASS
.- BadCboHiLo ... 1102000400 <= *All Rel* 09/02 19:41:27 PASS
.- ChkIotTs ... 1102000400 <= *All Rel* 09/02 19:41:27 PASS
.- NoSegmentIndex ... 1102000400 <= *All Rel* 09/02 19:41:27 PASS
.- BadNextObject ... 1102000400 <= *All Rel* 09/02 19:41:27 PASS
.- DroppedROTS ... 1102000400 <= *All Rel* 09/02 19:41:27 PASS
.- FilBlkZero ... 1102000400 <= *All Rel* 09/02 19:41:27 PASS
.- DbmsSchemaCopy ... 1102000400 <= *All Rel* 09/02 19:41:27 PASS
.- OrphanedObjError ... 1102000400 > 1102000000 09/02 19:41:27 PASS
.- ObjNotLob ... 1102000400 <= *All Rel* 09/02 19:41:27 PASS
.- MaxControlfSeq ... 1102000400 <= *All Rel* 09/02 19:41:27 PASS
.- SegNotInDeferredStg ... 1102000400 > 1102000000 09/02 19:41:27 PASS
.- SystemNotRfile1 ... 1102000400 > 902000000 09/02 19:41:27 PASS
.- DictOwnNonDefaultSYSTEM ... 1102000400 <= *All Rel* 09/02 19:41:27 PASS
.- OrphanTrigger ... 1102000400 <= *All Rel* 09/02 19:41:27 PASS
.- ObjNotTrigger ... 1102000400 <= *All Rel* 09/02 19:41:27 PASS
---------------------------------------
02-SEP-2022 19:41:27 Elapsed: 0 secs
---------------------------------------
Found 0 potential problem(s) and 0 warning(s)
PL/SQL procedure successfully completed.
Statement processed.
Complete output is in trace file:
/u01/app/oracle/diag/rdbms/xe/XE/trace/XE_ora_26305_HCHECK.trc
SQL> spool off
SQL>