Rogerio Eguchi Blog

Variable substitution using new_val

This is not new but I wasn’t aware of this cool feature !

You can assign a value to a variable using new_val on SQLPlus.

From the doc: “Specifies a variable to hold a column value”

Example:

column PROPERTY_VALUE new_val temporary_tablespace

select PROPERTY_VALUE from database_properties where PROPERTY_NAME = 'DEFAULT_TEMP_TABLESPACE';

This way I can easily assign the output from the select command to the variable temporary_tablespace.

You can check the variable’s value using define command:

define temporary_tablespace

Easy, right ?

If you are about to launch an ExaCS using the new resource model be aware of BUG 32104352 – Provisioning fails with minimum value for vcpu count is 4.

You should be able to launch an ExaCS with only one ocpu in each node but it will fail to provision due to this bug.

Fix is very simple, you just need to define a value of 4 or above for the OCPU.

After provisioning is completed, you can scale up/down the OCPU’s online.

Happy provisioning 🙂

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/

Statspack batch install

If you don’t have Oracle Diagnostics pack or is running Oracle Standard Edition, just go for statspack !

In this blog post I will show you how to install it in batch mode.

It is actually very simple, you just need to define three variables:

connect / as sysdba

define default_tablespace='perfstat'
define temporary_tablespace='temp01'
define perfstat_password='YourComplexStatspackPassword'

then run

@?/rdbms/admin/spcreate.sql

Simple, isn’t ?

I would also recommend you to take a look at Franck’s way of improving statspack here.

OCI – iSCSI bug 30711156

If you run Oracle DB on OCI compute and leverage iSCSI as volume attachment, beware of bug 30711156 on iSCSI.

We hit this bug a while ago and as consequence we were not able to read/write to the block volume anymore.

Fix: kill all Oracle processes then remount the Filesystem.

If you see errors on /var/log/messages like the ones below, you mostly like hit the same issue:

Aug 10 00:29:30 host iscsid: iscsid: Kernel reported iSCSI connection 1:0 error (1020 - ISCSI_ERR_TCP_CONN_CLOSE: TCP connection closed) state (3) 
Aug 10 00:29:30 host iscsid: iscsid: re-opening session 1 (reopen_cnt 0) 
Aug 10 00:29:30 host iscsid: iscsid: disconnecting conn 0x563c2f155068, fd 7 
Aug 10 00:33:01 host kernel: session1: iscsi_eh_cmd_timed_out scsi cmd ffff9c3622aea948 timedout 
Aug 10 00:33:01 host kernel: session2: iscsi_eh_cmd_timed_out scsi cmd ffff9c3622ae8d48 timedout 
Aug 10 00:33:01 host kernel: session1: iscsi_eh_cmd_timed_out return timer reset 
Aug 10 00:33:01 host kernel: session2: iscsi_eh_cmd_timed_out return shutdown or nh 
Aug 10 00:33:01 host kernel: session1: iscsi_eh_cmd_timed_out scsi cmd ffff9c3622aec148 timedout 
Aug 10 00:33:01 host kernel: session1: iscsi_eh_cmd_timed_out return timer reset

Nice, right ?

A new iscsi-initiator-utils is available for download so go ahead and update your server.

Linux Errata available here.

Good patching !

AWS cli for rds reports

Another quick blog post on AWS stuff.

You can query your RDS metadata information using aws cli.

This is a very useful approach when you manage hundreds of servers and need to build a report.

Here is the command line I’ve got to retrieve the database name, license model, DB engine and DB version.

aws rds describe-db-instances --region us-east-1 --query "*[].{DBInstanceIdentifier:DBInstanceIdentifier,LicenseModel:LicenseModel,Engine:Engine,EngineVersion:EngineVersion}" --output table

You can find other rds cli options here.

AWS Nitro – volume id and device name

Hi all,

Quick blog post about EBS on AWS nitro instances.

When working with AWS Nitro instances your EBS volumes will be exposed as NVMe block devices, i.e nvme0n1/nvme1n1, etc, regardless what your input is when provisioning them.

But you can use the nvme tool to map the NVMe device name to the actual name you have provided:

[ec2-user ~]$ sudo nvme id-ctrl -v /dev/nvme1n1
NVME Identify Controller:
vid : 0x1d0f
ssvid : 0x1d0f
sn : vol01234567890abcdef
mn : Amazon Elastic Block Store
…
0000: 2f 64 65 76 2f 73 64 6a 20 20 20 20 20 20 20 20 "/dev/sdf…"

So in this case I’ve used sdf as block volume name and ended up with nvme1n1 on my instance.

I highly recommend you to read the docs here and watch a cool video.

OCI CLI authentication for federated users

In this short blog post I will explain how to authenticate using a federated user instead of a local one.

Install your oci cli then run:

oci session authenticate

Inform the region your tenant is subscribed to, login to the console and define profile name that better suits you.

A config file will be saved on your computer.

You can test your access running:

oci iam region list --config-file <config> --profile <name> --auth security_token

The default token TTL is set to 1 hour before it expires and can be refreshed within the validity period up to 24 hours. To refresh the token run:

oci session refresh --profile <name>

Hope it helps !

GUOB Tech Day 2020

Em função da pandemia, o evento será online e gratuito.

Uma coisa não muda: continuara sendo o melhor evento técnico para usuários de tecnologia Oracle no Brasil.

Reserve a agenda no dia 15/08/2020 !

Inscreva-se (aqui) o quanto antes já que as vagas são limitadas.

A agenda pode ser visualizada em: http://www.guob.com.br/index.php?page=programacao

Esse ano minha palestra será às 14h, sobre dicas e truques na Oracle Cloud Infrastructure.

Tudo (ou quase tudo) que vou apresentar você não vai encontrar na documentação oficial.

Sabe aqueles macetes que você só descobre quando vai implementar um recurso?

A ideia é essa 🙂

Vai ser gravado ? O video será liberado posteriormente ? Não sei 🙂

Melhor garantir e assistir online !

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 🙂