ora-16467: switchover target is not synchronized

Quick post about how I fixed ORA-16467 today.

After successfully running a switchover verify command, like below:

alter database switchover to dbname verify;

I end up getting an ORA-16467 error !

Checking the Data Guard config I realised the config LOG_ARCHIVE_CONFIG was missing 😦

I fixed this config and switchover went as expected !

This was an Oracle 12.1 db.

Restart from a failed Oracle upgrade

Interesting situation this morning.

I was performing a database upgrade from 11.2 to 12.2 when my VPN crashed.

I realized that I forgot to start the upgrade process using linux screen terminal which means that my upgrade process was lost.

Well, Oracle 12.2 has the ability to resume a failed upgrade process from the failed step automatically !

From the Oracle Docs:

Oracle Database 12c release 2 (12.2) includes a new Resume option for Parallel Upgrade Utility. This option is available for both CDBs and Non-CDBs. You are not required to identify failed or incomplete phases when you rerun or restart the upgrade. When you use the Parallel Upgrade Utility using the resume option (-R), the utility automatically detects phases from the previous upgrade that are not completed successfully. The Parallel Upgrade Utility then reruns or restarts just these phases that did not complete successfully, so that the upgrade is completed. Bypassing steps that already completed successfully reduces the amount of time it takes to rerun the upgrade. 

So I just ran $ORACLE_HOME/bin/dbupgrade -n 4 -R -l $ORACLE_HOME/diagnostics and the upgrade process was restarted.

Cool new feature, right ?

More info about it here.

Data transfer from AWS Oracle RDS to S3

It looks simple, right ? It is but there are a lot to do before you can actually perform the copy.

Prerequisits:

  • Create an IAM policy that gives RDS read/write/list permissions to the S3 bucket
  • Create an IAM role that gives RDS access to the S3 bucket
  • Associate the IAM role to the DB instance
  • Create a new Option Group or associate the S3_INTEGRATION option to an existing one

You can check the details on how to perform theses steps here.

OK, now you can perform the data transfer running, for example, the command below:

SELECT rdsadmin.rdsadmin_s3_tasks.upload_to_s3(
       p_bucket_name    =>  'mybucket', 
       p_prefix         =>  '', 
       p_s3_prefix      =>  '', 
       p_directory_name =>  'DATA_PUMP_DIR') 
    AS TASK_ID FROM DUAL; 

It will copy all files on DATA_PUMP_DIR directory to S3 bucket mybucket

This command will provide a task_id that will be useful to monitor the transfer status.

You can rely on AWS RDS Events at the console or the store procedure below to monitor the transfer job.

 SELECT text FROM table(rdsadmin.rds_file_util.read_text_file('BDUMP','dbtask-<task_id>.log')); 

Example:

Or at the AWS Console:

-ocmrf is no longer needed

I was patching an Oracle 12.1 Restart with latest bundle patch this week and realized that the -ocmrf option is no longer needed.

This enhancement started with Opatch 12.2.0.1.5 so now you have one more reason to update your Opatch 🙂

Don’t even bother looking for emocmrsp binary … it is no longer there !

So things just got easier ! All you have to do is run:

opatchauto apply <UNZIPPED_PATCH_LOCATION>/<BUG NO> 

Example for 12.1 BP:

export PATH=$PATH:/u01/app/12.1.0.2/grid/OPatch/
opatchauto apply /home/oracle/stage/29176139/29141038

Reference: MOS note 2161861.1, 1591616.1 and readme.html for patch 29176139.

CRS-2510 on GI Management Repository creation

I was following the steps from MOS note 12.2: How to Create GI Management Repository (Doc ID 2246123.1) and it failed with the error below:

[ 2019-04-02 10:15:05.945 BRT ] Registering database with Oracle Grid Infrastructure
[ 2019-04-02 10:15:06.320 BRT ] PRCR-1006 : Failed to add resource ora.mgmtdb for mgmtdb
PRCR-1071 : Failed to register or update resource ora.mgmtdb
CRS-2510: Resource ‘ora.MGMTLSNR’ used in dependency ‘hard’ does not exist or is not registered.
CRS-2514: Dependency attribute specification ‘hard’ is invalid in resource ‘ora.mgmtdb’
[ 2019-04-02 10:15:11.715 BRT ] DBCA_PROGRESS : DBCA Operation failed.

I could not found an exact match @MOS but to workaround this issue you can run srvctl add mgmtlsnr as grid user before running dbca and after that it will work properly.

ORA-28040: No matching authentication protocol

We deployed a new app last week and got the error ORA-28040: No matching authentication protocol while establishing a connection to the database.

It turns out that this app was using an embedded Oracle client 11.1 hence this error.

Oracle recommends that you upgrade your client software to match the current server software, which in my case is Oracle 12.2.

To fix this error you can upgrade the client or config the new parameter
SQLNET.ALLOWED_LOGON_VERSION_SERVER according to the documentation:

https://docs.oracle.com/en/database/oracle/oracle-database/12.2/upgrd/required-tasks-complete-upgrading-oracle-database.html#GUID-433E0DB9-026E-4322-A8FF-BA0E108AB28B

MOS 1957995.1 says that the default for the SQLNET.ALLOWED_LOGON_VERSION_SERVER setting has changed in 12.2 from 11 to 12. So if your client is not at least 11.2.0.3 or includes the CPUOCT2012 patch you will not be able to use the 12 setting.

It looks accurate as client 11.1 fails to connect to Oracle 12.2 and client 11.2 works:

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.