O cliente reportou que todas as tentativas de usar a console OCI para configurar um Data Guard no DB System com Container Database conclui a criação do DB System Standby mas deixa o Data Guard com status de falha:
Conectando no DB System primário, os JOBs do DBCLI completaram com sucesso:
# dbcli list-jobs -k DG
ID Description Created Status
---------------------------------------- --------------------------------------------------------------------------- ----------------------------------- ----------
81e57e42-7ad7-4a6a-9ed1-6d0f4dd897ed Validate DG Config Creation Monday, August 02, 2021, 16:27:49 BRT Success
9699520f-2f1c-4edd-b8dc-ba5ca75fb641 DG Config service creation Monday, August 02, 2021, 17:16:12 BRT Success
bd2633f0-81c6-4b14-9ba8-4348d51c15f4 Validate DG Config Creation Tuesday, August 03, 2021, 09:34:09 BRT Success
63b7dc4b-3e06-474e-ba13-6a8c34ade4d1 DG Config service creation Tuesday, August 03, 2021, 10:14:17 BRT Success
595248de-5b61-4195-8cc5-b4cc1ffa09dd Validate DG Config Creation Wednesday, August 04, 2021, 16:12:40 BRT Success
9676a673-a715-4f88-96d5-2ccc990a530a DG Config service creation Wednesday, August 04, 2021, 16:52:58 BRT Success
Tentei checar a configuração do Data Guard Broker, mas ainda não havia sido criada:
$ dgmgrl / DGMGRL for Linux: Version 12.1.0.2.0 - 64bit Production Copyright (c) 2000, 2013, Oracle. All rights reserved. Welcome to DGMGRL, type "help" for information. Connected as SYSDBA. DGMGRL> show configuration; ORA-16532: Oracle Data Guard broker configuration does not exist Configuration details cannot be determined by DGMGRL
O erro não parece ter ocorrido no DB System Primary, então comecei investigar no DB System Standby.
Listando os JOBs do DBCLI, havia um único JOB relacionado a DG e o mesmo havia falhado:
# dbcli list-jobs -k DG
ID Description Created Status
---------------------------------------- --------------------------------------------------------------------------- ----------------------------------- ----------
9e858321-4c02-40c1-a34a-6266a93cb163 DG Config service creation Wednesday, August 04, 2021, 16:56:52 BRT Failure
Obtendo detalhes do JOB com falha:
# dbcli describe-job -i 9e858321-4c02-40c1-a34a-6266a93cb163
Job details
----------------------------------------------------------------
ID: 9e858321-4c02-40c1-a34a-6266a93cb163
Description: DG Config service creation
Status: Failure
Created: August 4, 2021 4:56:52 PM BRT
Progress: 53%
Message: DCS-12518:Failed to populate standby database metadata.
Cause: Unable to populate standby database metadata.
Action: Check the logs for more information. Contact Oracle Support Services for assistance.
Task Name Start Time End Time Status
------------------------------------------------------------------------ ----------------------------------- ----------------------------------- ----------
task:TaskZLockWrapper_891 August 4, 2021 4:56:53 PM BRT August 4, 2021 8:11:26 PM BRT Failure
DG config standby task August 4, 2021 4:56:53 PM BRT August 4, 2021 8:11:26 PM BRT Failure
Populate standby database metadata August 4, 2021 8:11:09 PM BRT August 4, 2021 8:11:26 PM BRT Failure
A mensagem de erro não identificou a real causa do problema, mas serviu como ponto de partida para investigar os logs do DCS Agent.
Procurei pela primeira ocorrência dessa mensagem no arquivo “/opt/oracle/dcs/log/dcs-agent.0.0.log” e encontrei o seguinte trecho:
[2021-08-04 20:11:26,922] FINE [Populate standby database metadata : JobId=9e858321-4c02-40c1-a34a-6266a93cb163] com.oracle.dcs.agent.utils.db.SqlPlusHelper: Finished running Sql Script /tmp/dcsserver/sqlplus/sqlplus2021-08-04_20-11-24-8888909173242362806.sql
[2021-08-04 20:11:26,922] SEVERE [Populate standby database metadata : JobId=9e858321-4c02-40c1-a34a-6266a93cb163] com.oracle.dcs.agent.rpc.service.dataguard.DataguardOperations: No matching record found for pdb user in CDB_ROLE_PRIVS
[2021-08-04 20:11:26,922] SEVERE [Populate standby database metadata : JobId=9e858321-4c02-40c1-a34a-6266a93cb163] com.oracle.dcs.agent.rpc.service.dataguard.DataguardActions: Unable to populate standby database metadata
[2021-08-04 20:11:26,923] SEVERE [Populate standby database metadata : JobId=9e858321-4c02-40c1-a34a-6266a93cb163] com.oracle.dcs.commons.jrpc.JsonRequestProcessor: RPC request invocation failed on request: {"classz":"******** "
DcsException{errorHttpCode=InternalError, msg=Failed to populate standby database metadata. , msgId=12518, causedBy=null}
at com.oracle.dcs.commons.exception.DcsException$Builder.build(DcsException.java:69)
Aqui a causa do problema ficou mais clara com essa mensagem:
No matching record found for pdb user in CDB_ROLE_PRIVS
O DCS Agent não encontrou nenhum registro na view CDB_ROLE_PRIVS referente usuário que é PDB Admin. O nome esperado para o PDB Admin é “pdbuser”, conforme consultado via DBCLI
# dbcli list-databases
ID DB Name DB Type DB Version CDB Class Shape Storage Status DbHomeID
---------------------------------------- ---------- -------- -------------------- ---------- -------- -------- ---------- ------------ ----------------------------------------
0bfb4270-35d6-4442-8c8d-63b488eed7d9 CDB1 Si 12.1.0.2.210119 true Oltp ASM Configured c0b2de75-b51e-4352-b8c0-66445d21c406
# dbcli describe-database -i 0bfb4270-35d6-4442-8c8d-63b488eed7d9
Database details
----------------------------------------------------------------
ID: 0bfb4270-35d6-4442-8c8d-63b488eed7d9
Description: CDB1
DB Name: CDB1
DB Version: 12.1.0.2.210119
DB Type: Si
DB Edition: EE
DBID: 1047367877
Instance Only Database: false
CDB: true
PDB Name: PRD00
PDB Admin User Name: pdbuser
Class: Oltp
Shape:
Storage: ASM
DB Redundancy:
CharacterSet: WE8MSWIN1252
National CharacterSet: AL16UTF16
Language: AMERICAN
Territory: AMERICA
Home ID: c0b2de75-b51e-4352-b8c0-66445d21c406
Console Enabled: false
Level 0 Backup Day: Sunday
AutoBackup Enabled: false
BackupConfigId: 9cd51542-2976-4213-9773-910c3fe5b88f
Created: May 27, 2021 11:42:51 AM BRT
No banco de dados primário, realmente não havia nenhuma role atribuída ao usuário PDBUSER:
SQL> SELECT * FROM CDB_ROLE_PRIVS WHERE GRANTEE='PDBUSER';
no rows selected
Também não existia o usuário criado:
SQL> SELECT USERNAME, ACCOUNT_STATUS FROM CDB_USERS WHERE USERNAME ='PDBUSER';
no rows selected
Solução
1) Criar o usuário PDBUSER dentro do PDB e conceder a role PDB_DBA:
SQL> alter session set container = PRD00;
Session altered.
SQL> create user pdbuser identified by "<senha de admin do DB System>";
User created.
SQL> grant pdb_dba to pdbuser;
Grant succeeded.
SQL> SELECT GRANTED_ROLE FROM CDB_ROLE_PRIVS WHERE GRANTEE='PDBUSER';
GRANTED_ROLE
--------------------------------------------------------------------------------
PDB_DBA
2 Recriar o Data Guard na Console OCI.
Validando a configuração do Data Guard no Broker:
$ dgmgrl /
DGMGRL for Linux: Version 12.1.0.2.0 - 64bit Production
Copyright (c) 2000, 2013, Oracle. All rights reserved.
Welcome to DGMGRL, type "help" for information.
Connected as SYSDBA.
DGMGRL> show configuration;
Configuration - CDB1_iad175_CDB1_gru1wq
Protection Mode: MaxPerformance
Members:
CDB1_iad175 - Primary database
CDB1_gru1wq - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS (status updated 19 seconds ago)
DGMGRL> show database "CDB1_gru1wq"
Database - CDB1_gru1wq
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds (computed 0 seconds ago)
Apply Lag: 0 seconds (computed 0 seconds ago)
Average Apply Rate: 420.00 KByte/s
Real Time Query: OFF
Instance(s):
CDB1
Database Status:
SUCCESS
Causa raiz
Por padrão, todo Container Database (CDB) provisionado na console OCI terá o usuário PDBUSER criado no Pluggable Database (PDB). Neste caso, o PDB original criado pela console havia sido dropado e criado um novo a partir de um XML para migrar um banco de dados de non-CDB para PDB.
O problema deve ocorrer sempre que for criado um PDB no CDB do DB System e não criarmos o usuário PDBUSER no novo PDB.