Faster ASM Rebalance

Yes, it is possible to accomplish it but only if you have a flash storage.

Why ?

Because in this case you can skip the Compact Phase of ASM rebalance operation.

For 12c onwards, you can use the command below to skip the compact phase:

ALTER DISKGROUP <DG_NAME> SET ATTRIBUTE '_rebalance_compact'='FALSE';

But before running this command, please read the following articles so you can better understand what you are doing.

Rebalancing act – http://asmsupportguy.blogspot.com/2011/11/rebalancing-act.html

MOS Note 1902001.1 – What is ASM rebalance compact Phase and how it can be disabled

ASM Rebalance Too Slow? 3 Tips To Improve Rebalance Times – https://flashdba.com/2015/04/17/asm-rebalance-too-slow-3-tips-to-improve-rebalance-times/

OCE – Maximum Availability Certified Expert

Oracle Certified Expert exams were the most difficult ones I’ve ever done in my life.

There are two reasons for that:

1. A lot of content to cover

2. Exams requires deep hands-on experience

To achieve the Oracle Database 12c Maximum Availability Certified Expert Certification, you must complete ALL three certifications below:

1. Oracle Database 12c Administrator Certified Professional

2. Oracle Certified Expert, Oracle Database 12c: RAC and Grid Infrastructure Administration

3. Oracle Certified Expert, Oracle Database 12c: Data Guard Administration

and yesterday I finally passed the Oracle 12c Data Guard exam.

What did I do to prepare for the exams ?

I read the training material twice.

I read a lot of information on the official documentation.

I’ve created a LAB to test all features and scenarios.

And of course, I’ve been working with both RAC and Data Guard for a long time.

I strongly agree with my Brazilian friend Alex Zaballa: stay away from brain-dumps !

You will fail the exam during this process (as I did) but you will get there !

Let’s celebrate 🙂

Change TZ on Oracle Restart and Oracle RAC

If you need to change the timezone on a server running an Oracle DB be careful to change it properly for Oracle Restart and Oracle RAC, otherwise you will end up getting incorrect values when connecting via Listener.

To change the TZ on OEL 7 to MST TZ:

timedatectl set-timezone America/Phoenix

Date command on OS will return the correct date:

[oracle@dbserver]$ date
Thu Jun 25 18:56:48 MST 2020

To configure TZ for Oracle Restart/RAC:

srvctl setenv database -d rspaws -t 'TZ=America/Phoenix'
srvctl setenv listener -l LISTENER -t 'TZ=America/Phoenix'

Check new values:

srvctl getenv database -d rspaws
srvctl getenv listener

Then restart the services:

srvctl stop listener -l LISTENER
srvctl start listener -l LISTENER

srvctl stop database -d rspaws
srvctl start database -d rspaws

Connect to the db using using the listener:

sqlplus system@rspaws

alter session set nls_date_format = 'dd/mm/yyyy hh24:mi:ss';

!date

select sysdate from dual;

Date output should match between OS and Oracle.

If you don’t use Oracle Restart/RAC then changing the TZ at OS level is enough to make Oracle aware of the new date.

I’ve tested this procedure on Oracle 12.1, Oracle 12.2 and Oracle 18c with GI 18c.

Enjoy!

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.