ORA-1031 on Logical Standby

Comecei a estudar para a prova Oracle 12c Data Guard Administrator faz alguns dias e durante meus testes encontrei o erro ORA-1031 no logical standby.

I’ve started my studies for Oracle 12c Data Guard Administrator test a couple of days ago and during the tests on my LAB I got the error ORA-1031 on logical standby.

Na verdade eu nunca havia criado um Logical Standby então tudo aqui é novidade pra mim !

The true is everything related to logical standby is new to me since I have never had the opportunity to work with this technology.

Antes de descobrir o erro ORA-1031, o dg broker mostra o erro genérico ORA-16810:

Before error ORA-1031 I got the generic error ORA-16810:

logical_error

Você chega no erro de verdade de duas maneiras:

You will narrow this issue down using either of those two approaches:

  • Checking alert.log  from logical standby

alert_stdb_err

trace file from Apply process (chicago_as01_18532.trc) also shows error ORA-1031:

[oracle@oralab2 ~]$ tail -5 /u01/app/oracle/diag/rdbms/chicago/chicago/trace/chicago_as01_18532.trc
*** 2018-12-10 06:40:38.414
ORA-01031: insufficient privileges
OPIRIP: Uncaught error 447. Error stack:
ORA-00447: fatal error in background process
ORA-01031: insufficient privileges
  • Running dg broker command show database <db> ‘StatusReport’logical_ora1031

O erro começou quando eu executei o comando “grant sysdg to sysdg” uma vez que o processo de apply não é executado com privilégio de sysdba.

This issue was due to the command “grant sysdg to user” because the Logical Apply Processes does not run with sysdba privileges.

Para corrigir precisaremos criar uma procedure para “pular” erros de grant no schema desejado.

To fix it we need to build a procedure to skip grants errors on specific schemas.

Exmple:

CREATE OR REPLACE PROCEDURE sys.handle_error_ddl ( 
old_stmt IN VARCHAR2, 
stmt_type IN VARCHAR2, 
schema IN VARCHAR2, 
name IN VARCHAR2, 
xidusn IN NUMBER, 
xidslt IN NUMBER, 
xidsqn IN NUMBER, 
error IN VARCHAR2, 
new_stmt OUT VARCHAR2 
) AS 

BEGIN 
-- Default to what we already have 
new_stmt := old_stmt; 

-- Ignore any GRANT errors on SYS or HR schemas 
IF INSTR(UPPER(old_stmt),'GRANT') > 0 
THEN 
IF schema IS NULL 
OR (schema IS NOT NULL AND 
(UPPER(schema) = 'SYS' OR UPPER(schema) = 'SYSDG' ))
THEN 
new_stmt := NULL; 
-- record the fact that we just skipped an error on 'SYS' or 'HR' schemas 
-- code not shown here 
END IF; 
END IF; 

END handle_error_ddl; 
/
EXECUTE DBMS_LOGSTDBY.SKIP_ERROR (stmt => 'NON_SCHEMA_DDL', schema_name => NULL, object_name => NULL, proc_name => 'SYS.HANDLE_ERROR_DDL');

Depois basta iniciar o processo de apply novamente.

After that you have to restart the Logical Apply Process again.

Update 1:

Outra situação em que o erro ORA-1031 pode acontecer é quando o arquivo de senhas (orapw<sid>) não está sincronizado entre os bancos primary e standby.

Another situation when error ORA-1031 happens is when the password file gets out-of-sync.

 

 

Author: reguchi

Oracle ACE, OCP 12c, OCE 12c MAA, OCE Exadata, RHCE, AWS CSAA, OCI CSAA. Technology geek, homebrewer, dad.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s