Problema
Ao tentar executar o datapatch em um Container Database, o mesmo sempre falha com erro ao fazer rollback ou aplicar patches no PDB SEED.
$ ./datapatch -verbose
SQL Patching tool version 12.1.0.2.0 Production on Mon Apr 12 09:05:48 2021
Copyright (c) 2012, 2017, Oracle. All rights reserved.
Log file for this invocation: /u01/app/oracle/cfgtoollogs/sqlpatch/sqlpatch_964_2021_04_12_09_05_48/sqlpatch_invocation.log
Connecting to database...OK
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 SQL patches:
Patch 30128197 (Database PSU 12.1.0.2.191015, Oracle JavaVM Component (OCT2019)):
Installed in the binary registry and CDB$ROOT PDB$SEED TST1
Patch 30290077 (MERGE REQUEST ON TOP OF DATABASE BP 12.1.0.2.191015 FOR BUGS 30209388 28538439):
PDB$SEED (ROLLBACK with errors) only
Bundle series DBBP:
ID 201020 in the binary registry and ID 201020 in PDB CDB$ROOT, ID 191015 in PDB PDB$SEED, ID 201020 in PDB TST1
Adding patches to installation queue and performing prereq checks...
Installation queue:
For the following PDBs: CDB$ROOT TST1
Nothing to roll back
Nothing to apply
For the following PDBs: PDB$SEED
The following patches will be rolled back:
30290077 (MERGE REQUEST ON TOP OF DATABASE BP 12.1.0.2.191015 FOR BUGS 30209388 28538439)
The following patches will be applied:
31511219 (DATABASE BUNDLE PATCH 12.1.0.2.201020)
BOOTSTRAP/PREREQ FAILURES:
The following PDBs encountered an error during the pre-patching
stage and are not being patched. Please review the errors shown
and invoke datapatch for these PDBs after fixing the errors.
PDB$SEED: DBD::Oracle::st execute failed: ORA-01008: not all variables bound (DBD ERROR: OCIStmtExecute) [for Statement "BEGIN
dbms_sqlpatch.set_patch_metadata(
p_patch_id => 31511219,
p_patch_uid => 23840155,
p_superseding => FALSE,
p_patch_descriptor => :descriptor,
p_patch_directory => :directory);
END;" with ParamValues: :descriptor=OCIXMLTypePtr=SCALAR(0x294cbc0), :directory='PK........`▒AQ?ҤV) ..'.......31511219_apply.sql▒._s▒▒..▒▒.}▒.g▒r& 4.{J&▒▒g{<▒.▒▒N▒h▒▒▒k▒O▒▒w.{Y..▒.▒H▒.▒.Q.▒.¬3E55▒t▒▒▒▒z5▒▒.▒▒.&s▒#9a▒<J▒▒▒(▒▒L.+▒▒.r▒sQ▒▒.▒b▒G...Y...▒u:.▒▒▒▒.▒OQ▒w▒▒..ڿ8?▒.▒|;.(.▒.Ngb.▒.)▒.▒▒K▒▒.t%▒▒▒▒.▒.Y-.▒M~▒▒.▒ C▒{▒xɼ..▒.▒.▒.▒▒▒)▒z▒▒.▒q▒▒H▒&dx;.|$r▒r.▒▒▒76J▒.߲(-.▒..,H#▒.▒▒U2▒v▒.▒▒qc.▒.~......▒▒Xl▒.▒ /▒8▒_w▒▒▒:..▒▒\^*.s}▒L&.▒▒n,▒O▒R.▒'▒.▒...▒▒▒.▒[▒37.ι▒.▒h@N6▒Ox▒▒▒▒.▒.▒.▒▒O
7+▒▒=)u.▒▒!<^▒Gc▒}2▒▒▒'▒▒▒▒
▒_▒▒▒▒..▒t>▒߮.▒Z▒,▒.▒.>▒~H8..Ѝ▒?..▒S.▒=▒▒5▒▒N/[.\.Qm.▒/▒▒\..▒r▒▒wOʢ▒.▒▒▒V▒~▒h߭▒^▒.5E.▒f▒}▒▒eݫ..Z(▒.▒j.6▒Q▒▒x▒▒Q~▒9[W.p▒..▒i▒▒▒#▒$▒▒.L▒1o▒&.▒▒..`▒▒..▒▒▒.\\▒▒}ޠ▒▒:.3`/▒.D ^1▒▒▒▒▒b..&▒bh▒..▒j6>▒]▒x▒?Po▒.▒L▒շ▒h▒.<▒▒=..▒...▒hh▒.▒▒ ▒.▒.▒..▒▒8▒▒▒.d
..3i▒\▒.▒hvL3. .f▒▒n▒.▒▒..5[( v▒▒VSTj.▒V6Ҋb▒ӿ▒^▒.<.▒▒.▒r.▒R▒d▒X▒▒.0 ▒▒L..q▒B▒y5..;▒▒
j.▒Aj^▒Z▒
.▒.▒.M▒▒..5RT#A.▒E.▒.▒l7j▒k<▒▒s▒▒GU▒h▒.n.P▒.`▒▒▒+.▒▒0▒-w▒▒.▒O▒. ▒▒-▒h.▒..▒▒,.n▒...']
Installing patches...
Erro destacado:
PDB$SEED: DBD::Oracle::st execute failed: ORA-01008: not all variables bound (DBD ERROR: OCIStmtExecute) [for Statement "BEGIN
dbms_sqlpatch.set_patch_metadata(
p_patch_id => 31511219,
p_patch_uid => 23840155,
p_superseding => FALSE,
p_patch_descriptor => :descriptor,
p_patch_directory => :directory);
END;"
Ao consultar o alert.log do CDB, haviam essas mensagens de erro relacionadas ao TEMPFILE do PDB SEED:
Errors in file /u01/app/oracle/diag/rdbms/cdb1_gru1wn/CDB1/trace/CDB1_ora_1084.trc: ORA-01157: cannot identify/lock data file 1002 - see DBWR trace file ORA-01110: data file 1002: '+DATA/cdb1_gru1wn/96AC4BF27DB87EB5E0536606F40AA90F/datafile/pdbseed_temp012019-12-17_07-36-37-pm.dbf'
A mensagem indica que existe um problema com a tablespace temporária do PDB SEED.
Verificando a tablespace TEMP no PDB SEED:
SYS@CDB1> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 TST1 READ WRITE NO
SYS@CDB1> alter session set container=PDB$SEED;
Session altered.
SYS@CDB1> select name from v$tempfile;
NAME
--------------------------------------------------------------------------------
+DATA/cdb1_gru1wn/96AC4BF27DB87EB5E0536606F40AA90F/datafile/pdbseed_temp012019-12-17_07-36-37-pm.dbf
Anotamos o caminho retornando, o mesmo informado no alert.log:
+DATA/cdb1_gru1wn/96AC4BF27DB87EB5E0536606F40AA90F/datafile/pdbseed_temp012019-12-17_07-36-37-pm.dbfEntão verificamos as tablespaces do PDB SEED e onde os seus datafiles estão localizados:
SYS@CDB1> select name from v$tablespace;
NAME
------------------------------
SYSTEM
TEMP
UNDOTBS1
SYSAUX
SYS@CDB1> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
+DATA/CDB1_GRU1WN/DATAFILE/undotbs1.328.1056983359
+DATA/CDB1_GRU1WN/99EC724D1AB35C34E0530402340ADF28/DATAFILE/system.321.1056983385
+DATA/CDB1_GRU1WN/99EC724D1AB35C34E0530402340ADF28/DATAFILE/sysaux.320.1056983387
Primeira diferença que notamos é o caminho dos arquivos no diskgroup, em que os datafiles da SYSAUX e SYSTEM estão abaixo do diretório 99EC724D1AB35C34E0530402340ADF28 e não 96AC4BF27DB87EB5E0536606F40AA90F.
Então verifiquei se o caminho existe no diskgroup:
$ asmcmd ASMCMD> cd +DATA/cdb1_gru1wn/96AC4BF27DB87EB5E0536606F40AA90F/datafile/ ASMCMD-8002: entry '96AC4BF27DB87EB5E0536606F40AA90F' does not exist in directory '+DATA/cdb1_gru1wn/'
Conforme saída acima, o diretório não existe no diskgroup.
Confirmando todos os diretórios existentes abaixo de +DATA/cdb1_gru1wn:
ASMCMD> cd +DATA/cdb1_gru1wn/ ASMCMD> ls 99EC724D1AB35C34E0530402340ADF28/ 99EC835D813D7AF2E0530402340A1BF5/ DATAFILE/ PARAMETERFILE/ TEMPFILE/
Tentativa de consultar os tempfiles pela view DBA_TEMP_FILES retorna erro:
SYS@CDB1> SELECT BYTES/1024/1024 FROM DBA_TEMP_FILES;
SELECT BYTES/1024/1024 FROM DBA_TEMP_FILES
*
ERROR at line 1:
ORA-01157: cannot identify/lock data file 1002 - see DBWR trace file
ORA-01110: data file 1002:
'+DATA/cdb1_gru1wn/96AC4BF27DB87EB5E0536606F40AA90F/datafile/pdbseed_temp012019-
12-17_07-36-37-pm.dbf'
Problema confirmado, a tablespace TEMP, que é a default no banco de dados, está sem tempfile válido.
Solução
Precisamos criar uma nova tablespace temporária e definir como a default para o PDB SEED.
1) Abrir o PDB SEED em READ-WRITE:
SQL> ALTER SESSION SET "_oracle_script"=TRUE;
Session altered.
SQL> ALTER PLUGGABLE DATABASE PDB$SEED CLOSE;
Pluggable database altered.
SQL> ALTER PLUGGABLE DATABASE PDB$SEED OPEN;
Warning: PDB altered with errors.
2) Criar nova tablespace temporária com nome diferente da atual:
SQL> ALTER SESSION SET CONTAINER=PDB$SEED;
Session altered.
SYS@CDB1> CREATE TEMPORARY TABLESPACE TEMP01 TEMPFILE '+DATA' SIZE 100M AUTOEXTEND ON NEXT 25M;
Tablespace created.
3) Definir como DEFAULT para este PDB:
SYS@CDB1> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP01;
Database altered.
4) Dropar a antiga:
SYS@CDB1> DROP TABLESPACE TEMP;
Tablespace dropped.
OPCIONAL: Recompilar objetos inválidos no PDB$SEED:
SQL> @?/rdbms/admin/utlrp.sql
5) Voltar o PDB$SEED para READ-ONLY:
SQL> ALTER PLUGGABLE DATABASE PDB$SEED CLOSE;
SQL> ALTER PLUGGABLE DATABASE PDB$SEED OPEN READ ONLY;
Agora podemos testar a execução do datapatch novamente.
Problema Relacionado:
Tentativa de aplicar patch via console OCI ou DBCLI falha com o seguinte erro quando o agente da cloud invoca o datapatch e o mesmo não conclui dentro de uma determinada janela de tempo (aparentemente o timeout é 2h):
Message: DCS-10806:Failed to execute db upgrade to version 20.4.2.1.0[dbexemplo] task within the expected time period due to: Timeout.
Quando o datapatch enfrentou erros devido a tablespace TEMP do PDB$SEED, o processo ficou rodando por mais de 6 horas, em uma espécie de loop infinito concedendo GRANTs e recriando VIEWS.