Month: May 2015

Leap Second and Impact for Oracle Database

matheusdba Leave a comment

Don’t know what is this? Oh boy, I suggest you take a look…

It can sound a little crazy, but it’s about an universal time adjustment of atomic time. Something like that. To understand, take a look on:
http://www.meinberg.de/english/info/leap-second.htm
http://en.wikipedia.org/wiki/Coordinated_Universal_Time
http://en.wikipedia.org/wiki/International_Atomic_Time
http://www.britannica.com/EBchecked/topic/136395/Coordinated-Universal-Time
http://www.britannica.com/EBchecked/topic/290686/International-Atomic-Time

20499seconds
Okey doke!
But what about Oracle Database adjustment? Good news: Nothing to do! 😀

In Oracle words: “The Oracle RDBMS needs no patches and has no problem with the leap second changes on OS level.”

But, attention!
If your application uses timestamp or sysdate, verify the adjust of the OS Level. If it consists on a “60” second, it can result on “ORA-01852 seen 60 seconds is a illegal value for the date or timestamp dataype.”
(Insert leap seconds into a timestamp column fails with ORA-01852 (Doc ID 1553906.1))

Another possibilities is documented on these notes:
NTP leap second event causing Oracle Clusterware node reboot (Doc ID 759143.1)
(Oracle VM and RHEL 4.4 to 6.2): Leap Second Hang – CPU Can Be Seen at 100% (Doc ID 1472421.1)
(OEM on Linux): Enterprise Manager Management Agent or OMS CPU Use Is Excessive near Leap Second Additions on Linux (Doc ID 1472651.1)

So, pay attention! 🙂

Here other Oracle notes that I recommend to take a look:
Leap seconds (extra second in a year) and impact on the Oracle database. (Doc ID 730795.1)
Leap Second Time Adjustment (e.g. on June 30, 2015 at 23:59:59 UTC) and Its Impact on Exadata Database Machine (Doc ID 1986986.1)
How Leap Second Affects The OS Clock on Linux and Oracle VM (Doc ID 1453523.1)
NOTE:1461363.1 – What Leap Second Affects Occur In Tuxedo?
NOTE:1553906.1 – Insert leap seconds into a timestamp column fails with ORA-01852
NOTE:412160.1 – Updated DST Transitions and New Time Zones in Oracle RDBMS and OJVM Time Zone File Patches
NOTE:1453523.1 – How Leap Second Affects The OS Clock on Linux and Oracle VM
NOTE:1019692.1 – Leap Second Handling in Solaris – NTPv3 and NTPv4
NOTE:1444354.1 – Strftime(3c) Does Not Show The Leap Second As 23:59:60
NOTE:1461606.1 – Any Effect of Leap Seconds to MessageQ?

Matheus.

GB vs GiB | MB vs MiB | KB vs KiB

matheusdba Leave a comment

Oh man!
It’s just me or you doesn’t know about too?

Okey. Here the difference is well explained. I saw it for the first time in EMC DataDomain interface and it sounded a little “strange”, but ok. Last week a heard a friend talking about and decided to search… What a surprise! haha

gibibyte-vs-gigabyte-small

In a nutshell, the units as we know them (1Gigabyte = 1000 Megabytes) was proposed by  Système International D’Unités (SI) and the other way (1Gibibyte = 1024 Mebibytes, with much more “precision”) was proposed by International Electrotechnical Commission’s (IEC), in 1999.
The main difference is that the first uses 10^x measurement, rather than 2^x (1024 base), like IEC. For example:

For a DVD:
4.7 GB ==> 4.337 GiB
8.5 GB ==> 7.91 GiB

Interesting, isn’t it?
So, again, I suggest you spend some time reading this…

Matheus.

Unplug/Plug PDB between different Clusters

matheusdba Leave a comment

Everyone test, write and show how to move pluggable databases between containers (CBDs) in the same Cluster, but a little more than a few write/show about move pluggable databases between different clusters, with isolated storage. So, let’s do that:

OBS: Just to stay easy to understand, this post is about migration of a Pluggable Database (BACENDB) from a cluster named ORAGRID12C and a Container Database named INFRACDB to the Cluster CLBBGER12, into Container CDBBGER.
(Click on images to get it bigger)

1. Access the container INFRACDB (Cluster GRID12C) and List the PDBs: 1

2. Shutdown BACENDB:
2
(of course it does’n worked with a normal shutdown. I don’t know what I was thinking… haha) 3

3. Unplug BACENDB (PDB) to XML (must be done from Pluggable, as you see…) 4
4. Created an ACFS (180G) to use as “migration area” mounted on “/migration/” in ORAGRID12C cluster:
5

5. Copy Datafiles and Tempfiles for the “/migration” through ASMCMD cp 6

6. ACFS exported and mounted as NFS on destination (CLBBGER12): 7
8

7. Pluggable created (Plugged) on new Cluster (CDBBGER), using “MOVE” FILE_NAME_CONVERT, to send the files to diskgroup +DGCDBBGER:

9

7.1 How it looks like on alert.log?

10

7.2 How about the Datafiles?

11

7.3 Checking database by remote sqlplus:

13

8. Creating the services as needed:

12

9. Dropping Pluggable from INFRACDB:

14

That’s Okey? Of course there is a few other ways to copy the files from an infra to another, like scp rather than mount.nfs, RMAN Copy, or other possibilities…

By the way, one of the restrictions of pluggable migration is to use the same endian format. Buut it’s possible to use RMAN Convert Plataform and convert datafiles to a filesystem, isn’t?
So, I guess it’s not a necessary limitation. Must to test an write another post… haha

About the post, this link helped, but, again, don’t mention about “another” cluster/infra/storage.

Matheus.

Whats is the main characteristics/skills of a DBA? [part 1]

matheusdba 2 Comments

More than a half decade acting as DBA, I still frequently asking myself about what means being a DBA? What is needed for? Or, more precisely: How to achieve the excellence in my job?

Easy-Server-Manageability-In-Oracle-Data-Base-Administrator
Well, I spent the last nights thinking and searching for this subject in the web. You might suppose I found a few answers, right? Yes, of course I found. So, let’s understand and analyze it.

In the first place, let me share with you some pre-steps/conditions: Looking for don’t be 100% reactive and use, at least a little bit of, my gray matter, I decided the structure my own “list” before the search. Then, just in the moment to write, I realized that it’s not so easy to merge Personal Characteristics and Technical Skills. This way, I decided to slipt this analysis in these two topics. As a consequence, in two posts. 🙂

By the way, after this “research” and the results below, but before this post, I quickly read the ebook “How to Become an Exceptional DBA” (2nd edition), by Brad McGehee. I really recommend it. I liked and I suppose you will too.

Another thing to say is that the lists could be infinite. So, I decided to keep my lists just in the “Top 8”. Here it goes what a listed before the search:

DBA’s most important characteristics:
(by Matheus)

1) Passionate
The DBA must to love his job. Have “light in their eyes”, something like excitement about the future. This is the best way to keep motivated, enthusiastic himself.

2) Committed
To have confidence in DBA promises and deadlines is a key factor. The DBA must understand the importance of his job for the health of the IT systems and the business, assume his responsibilities be reliable about everything and everyone. Trustworthy question.

3) Willfull
Something like “be always available to get up at 3:00 AM to help someone with a problem that is not directly your business”. Also counts keep available to help anyone with “just a little SQL doubt”, “just a little query”, “just a little grant” or “just a little project”… You know, embrace any kind of problem and goes right to the solution without any credits/glory behind it. Anyway, it’s just your job. Attitude and proactivity are a keywords too.

4) Innovator
Embraces the change and the challenges. The main difference between the creativity and innovation is that the second really make it happen. The DBA has to read, study, create but, the most important, implement new ideas, processes, tools. If it doesn’t work, it just try again and again. This is the evolution path. And the DBA has to pavement it.

5) Bookish / Like to Study
The DBA will be always a reference in the team. So, is his job to keep “fresh”, reading and having assertiveness answers for the developers, the business stakeholders, the or just the curiousness of anyone. It’s part of the “get confidence” process.

6) Ethics
Is not needed to say that DBA is always working with high critical data and buniness sensitive information. So, is not needed to spend more words to explain how is important this “characteristic”.

7) Communicative
DBA usually spend more time explaining, planning and justifying his actions that more properly doing the actions. Is a huge difference between a DBA and a Developer, in my point of view: The DBA spends hours thinking about a problem and understanding what is really happen, for correct with one or two lines of commands, on the other hand the Developer usually spends seconds or minutes to understand the problems and hours coding and testing the solution. Is not rare that the “most senior” DBA keeps in touch with “high hierarchies” to pass status report of crisis and “upswinged” problems. Make reports is another usual task. So, express verbally and write well is a potential difference between excellent and just “OK” DBAs, in my point of view.

8) Talented
No way out, the DBA must be talented to play well. Be a natural problem solver, detail oriented, realistic, patient, organized, assertive or good decision maker, keep calm under pressure, business minded, etc. Usually DBA is a experienced professional, so to have good mentoring skills and usually be ‘natural leaders’ are some decisive factors to. In summary, the DBA was ‘born to be wild’. Don’t you think like that?

OOOOOOOOK,
Let’s finally see what a found. Below all the authors, you will have the link where I read the topics.
I choose 5 international recognized personas and 2 brazilian bloggers, because I believe the key of success is to join experienced and recognized but have to look intern and valorize our goods. Also because I believe that, being Brazilian, they probably talk something specific or cultureless useful to me and other Brazilian readers… 🙂

# Bourleson
– Excellent comunication skills
– Formal education
– Real-world experience
– Knowledge of database theory
(http://www.dba-oracle.com/oracle_tips_dba_job_skills.htm)

# Brad McGehee
– Enjoys Technology
– Enjoys Challenges
– Good with Details
– Embraces Change
– Enjoys Learning
– Accepts Responsibility
– Maintains Professionalism
– Trustworthy
– Dependable
– Can Work as Part of a Team
– Can Communicate Effectively
– Listens Well
– Realistic
– Patient
– Enthusiastic
(http://bradmcgehee.com/2009/06/23/how-to-identify-important-characteristics-for-a-dba-job-candidate/)

# Greg Larsen
– Problem Solving Skills
– Mentoring Skills
– Automating Everything
– Looking for Process Improvement Efforts
– Following a Standard Methodology
– Attitude
– Community Oriented
– Ethical
– Prioritizing Work Correctly
– Leadership skills
– Communication Skills
– Customer Service
(http://www.databasejournal.com/features/mssql/traits-of-a-dba-part-two-the-personal-side-of-a-dba.html)

# John Sansom
– Excellent Attention to Detail
– A Natural Problem Solver
– Assertive
– Tactful
– Manage Relationships
– A Good Decision Maker
– Humble
– Calm Under Pressure
– Business Minded
– Approachable
(http://www.johnsansom.com/10-character-traits-of-outstanding-dbas/)

# Alan Hughes
– Organization Skills
– Technical Skills
– Interpersonal Skills
– Reading/Writing Skills
(http://work.chron.com/skills-needed-database-administrator-10356.html)

# Fernando Gazioli (BR)
– Confiança (Confidence)
– Pró-atividade (Proactivity)
– Bom Relacionamento (Good Relationship)
– Organização (Organizing skills)
– Curiosidade (Curiousity)
(http://www.tiespecialistas.com.br/2013/04/as-5-caracteristicas-de-comportamento-de-um-bom-dba/)

# Tiago Gouvêa (BR)
– Raciocínio Lógico (Logic)
– Autodidata (self-taught)
– Solucionador de Problemas (Problem Solver)
– Inglês (English)
– Gosta de Aprender (Enjoys Learning)
(http://www.profissionaisti.com.br/2014/01/os-5-principais-requisitos-para-ser-um-bom-programador/)

The final rating (top 8), grouping by similarity is something like:

Confident/Ethics/Professional/Responsible: 5 votes
Communicative/Good Relationship: 5 votes
Logic/Natural Leader/Mentoring/Assertiveness/Decision Maker/ Calm (in summary: Talented): 5 votes
“Problem Solver”: 4 votes
Curious/ Enjoys Learning/Self-taught: 4 votes
Embraces changes/challenges/process improvements: 4 votes
Enthusiastic/Proactive/Attitude: 4 votes
Detail-oriented: 3 votes

Do you liked this post?
I suppose you can like the Eduardo Morelli speech (05/21/2015!) too: http://webinar.infnet.edu.br/8-competencias-dba-oracle/

Take a look.

Oracle Convert Number into Days, Hours, Minutes

matheusdba 1 Comment

There’s a little trick…
Today I had to convert a “number” of minutes into hours:minutes format. Something like convert 570 minutes in format hour:minutes. As you know, 570/60 is “9,5” and should be “9:30”.

Lets use 86399 seconds (23:59:59) as example:

I began testing “to_char(to_date)” functions:
boesing@db>select to_char(to_date(86399,'sssss'),'hh24:mi:ss') formated from dual;

FORMATED
——–
23:59:59

Ok, it works. But using “seconds past midnight” (sssss). By the way, it works between 0 and 86399 only:

boesing@db> select to_char(to_date(86400,'sssss'),'hh24:mi:ss') from dual;
select to_char(to_date(86400,'sssss'),'hh24:mi:ss') from dual
*
ERROR at line 1:
ORA-01853: seconds in day must be between 0 and 86399

The problem remains. How to use minutes in 3 digits (570 minutes -> 9:30), for example?
The best way I solve was:

--- Seconds in hours:minutes:seconds
--- If you comment the first "TO_CHAR" line, can be minutes in hours:minutes too..
select
TO_CHAR(TRUNC(vlr/3600),'FM9900') || ':' || -- hours
TO_CHAR(TRUNC(MOD(vlr,3600)/60),'FM00') || ':' || -- minutes
TO_CHAR(MOD(vlr,60),'FM00') -- second
from dual;

It always works. 🙂

boesing@db>select
2 TO_CHAR(TRUNC(86399/3600),'FM9900') || ':' || -- hours
3 TO_CHAR(TRUNC(MOD(86399,3600)/60),'FM00') || ':' || -- minutes
4 TO_CHAR(MOD(86399,60),'FM00') -- second
5 from dual;

TO_CHAR(TRUNC
————-
23:59:59

boesing@db>select
2 TO_CHAR(TRUNC(570/3600),’FM9900′) || ‘:’ || — hours
3 TO_CHAR(TRUNC(MOD(570,3600)/60),’FM00′) || ‘:’ || — minutes
4 TO_CHAR(MOD(570,60),’FM00′) — second
5 from dual;

TO_CHAR(TRUNC
————-
00:09:30

boesing@db>select
2 TO_CHAR(TRUNC(MOD(570,3600)/60),’FM00′) || ‘:’ || — hours
3 TO_CHAR(MOD(570,60),’FM00′) — minutes
4 from dual;

TO_CHAR
——-
09:30

Any better way? Leave a comment. Thanks!

Matheus.

How to list all my Oracle Products from Application park?

matheusdba 1 Comment

YES!
I knew you would like the last post!

So, remains a doubt. What about my Oracle Application park?
Be soft. I’m glad to help. At real, Dieison Santos and me. As I said in the last post, it was his problem theese days… 😛

Here is a query to list your Oracle Application Products (including Oracle SOA Suite, of course) from OEM.

Use wisely:

select distinct * from (
select (LBL_HOSTNAME) "Host",
(CASE
when LBL_PRODUCTNAME like 'WebLogic Server' then 'WebLogic Suite'
when LBL_PRODUCTNAME like '%WebTier and Utilities%' then 'WebLogic Suite'
when LBL_PRODUCTNAME like '%EM Platform (OMS)%' then 'WebLogic Suite'
when LBL_PRODUCTNAME like '%Web Services Manager%' then 'Diagnostics Pack for Internet Application Server'
when LBL_PRODUCTNAME like '%Application Server 10g%' then 'Internet Application Server Enterprise Edition'
when LBL_PRODUCTNAME like '%Application Server Infrastructure 10g%' then 'Oracle Enterprise Single Sign-On Suite'
when LBL_PRODUCTNAME like '%Business Intelligence%' then 'Oracle Business Intelligence Suite Enterprise Edition Plus'
when LBL_PRODUCTNAME like '%Oracle SOA Suite%' then 'SOA Suite for Oracle Middleware'
when LBL_PRODUCTNAME like '%Oracle BAM%' then 'SOA Suite for Oracle Middleware'
when LBL_PRODUCTNAME like '%WebCenter Portal Suite 11g%' then 'Oracle WebCenter Portal'
when LBL_PRODUCTNAME like '%Oracle Business Process Management%' then 'Unified Business Process Management Suite'
when LBL_PRODUCTNAME like '%Oracle Remote Intradoc Client%' then 'Oracle WebCenter Content'
when LBL_PRODUCTNAME like '%Oracle Application Server Guard%' then 'Internet Application Server Enterprise Edition'
when LBL_PRODUCTNAME like '%Application Server Configuration%' then 'Configuration Management Pack for Internet Application Server'
else
LBL_PRODUCTNAME
end)
"Produto",
LBL_BASEVERSION "Versao",
LBL_PROCESSOR "Processador",
lbl_virtual "VIrtual",
DECODE(LBL_CPUS,null,1,LBL_CPUS) "CPUS"
from (SELECT M.EXTERNAL_NAME LBL_PRODUCTNAME,
M.NAME LBL_COMPONENTNAME,
M.BASE_VERSION LBL_BASEVERSION,
M.HOST_NAME LBL_HOSTNAME,
p.virtual lbl_Virtual,
p.system_config || nvl2(p.freq, p.freq || ' MHz FSB ', '') LBL_PROCESSOR,
p.cpu_count LBL_CPUS
FROM (MGMT$SOFTWARE_COMPONENTS M INNER JOIN mgmt$os_hw_summary p ON
M.HOST_NAME = P.HOST_NAME))
where   (
LBL_PRODUCTNAME like 'WebLogic Server'
or LBL_PRODUCTNAME like '%WebTier and Utilities%'
or LBL_PRODUCTNAME like '%EM Platform (OMS)%'
or LBL_PRODUCTNAME like '%Oracle Remote Intradoc Client%'
or LBL_PRODUCTNAME like '%Application Server 10g%'
or LBL_PRODUCTNAME like '%Application Server Infrastructure 10g%'
or LBL_PRODUCTNAME like '%Business Intelligence%'
or LBL_PRODUCTNAME like '%Oracle SOA Suite%'
or LBL_PRODUCTNAME like '%Oracle BAM%'
or LBL_PRODUCTNAME like '%WebCenter Portal Suite 11g'
or LBL_PRODUCTNAME like '%Oracle Business Process Management%'
or LBL_PRODUCTNAME like '%Application Server Configuration%'
or LBL_PRODUCTNAME like '%Oracle Application Server Guard%'
or LBL_PRODUCTNAME like '%Oracle Remote Intradoc Client%'
) order by "Produto");

Matheus.

How to list all my Oracle Products from Database park?

matheusdba 2 Comments

This is part of DBA role: know and prospect the use of Oracle Products for Oracle contract periodical review, isn’t?
It usually represent a huge problem, or, at least, demands a long time to refresh your spread sheet…

Well, If you use OEM, we offer you a better option! 😀
(I said ‘we’, because Dieison Santos came to me with this problem theese days…. So we talked about, I gave some directives and he mainly solved the problem. This way, great part of ‘we’ should be ‘he’… haha)

Without further, here’s a query that can map your environment (at least your Oracle database products):
You can use it to automate a report and set thresholds. Be creative… 😉

PS: From now, I’ll post all in english. Just for fun.

select
distinct(ddi.host_name) "Host",
(case
when opt.name like '%Active Data Guard%' then 'Oracle Active Data Guard'
when opt.name like '%Advanced Compression%' then 'Oracle Advanced Compression'
when opt.name like '%Audit Vault%' then 'Oracle Audit Vault'
when opt.name like '%Database Vault%' then 'Oracle Database Vault'
when opt.name like '%Partitioning (User)%' then 'Oracle Partitioning'
when opt.name like '%Real Application Clusters%' then 'Oracle Real Application Clusters'
when opt.name like '%Real Application Testing%' then 'Oracle Real Application Testing'
when (opt.name like '%ADDM%' or
opt.name like '%Automatic Database Diagnostic Monitor%' or
opt.name like '%Automatic Workload Repository%' or
opt.name like '%AWR%' or
opt.name like '%Baseline%' or
opt.name like '%Diagnostic Pack%' ) then 'Oracle Diagnostic Pack'
when (opt.name like '%SQL Monitoring%' or
opt.name like '%SQL Performance%' or
opt.name like '%SQL Performance%' or
opt.name like '%SQL Profile%' or
opt.name like '%SQL Tuning%' or
opt.name like '%SQL Access Advisor%' or
opt.name like '%Tuning Pack%') then 'Oracle Tuning Pack'
when opt.name like '%Change Management Pack%' then 'Oracle Change Management Pack'
when ddi.edition like 'Enterprise Edition' then 'Oracle Database Enterprise Edition'
else opt.name
end) "Produto Oracle",
hcd.num_cores "Cores",
ohs.virtual "Virtual",
hcd.impl "Processador",
ddi.dbversion "Versao"
from
mgmt$hw_cpu_details hcd,
mgmt$os_hw_summary ohs,
mgmt$db_dbninstanceinfo ddi,
(select
h.host_name as host,
h.target_name as database_name,
i.instance_name as instance_name,
h.target_type   as target_type,
h.target_guid as target_guid,
f.DBID,
f.NAME,
f.CURRENTLY_USED,
f.DETECTED_USAGES,
f.FIRST_USAGE_DATE,
f.LAST_USAGE_DATE,
f.VERSION,
f.LAST_SAMPLE_DATE,
f.LAST_SAMPLE_PERIOD,
f.TOTAL_SAMPLES,
f.AUX_COUNT,
f.DESCRIPTION
from
mgmt_db_featureusage f,
mgmt_targets h,
mgmt_db_dbninstanceinfo_ecm i,
gc$ecm_gen_snapshot s
where
s.is_current = 'Y' and
s.snapshot_guid = i.ecm_snapshot_id and
s.target_guid = f.target_guid and
h.target_type in ('oracle_database','rac_database') and
s.target_type = h.target_type and
s.snapshot_type in ('oracle_dbconfig','oracle_racconfig') and
f.DETECTED_USAGES>0
) opt
where
hcd.target_guid=ohs.target_guid
and ohs.host_name=ddi.host_name
and ddi.target_guid=opt.target_guid
and (    opt.name like '%Active Data Guard%' -- Active Data Guard
or opt.name like '%Advanced Compression%' -- Advanced Compression
or opt.name like '%Audit Vault%' -- Audit Vault
or opt.name like '%Database Vault%' -- DB Vault
or opt.name like '%Partitioning (user)%' -- Partitioning
or opt.name like '%Real Application Clusters%' --RAC
or opt.name like '%Real Application Testing%' -- RAT
or opt.name like '%ADDM%' -- Diagnostic Pack
or opt.name like '%Automatic Database Diagnostic Monitor%' -- Diagnostic Pack
or opt.name like '%Automatic Workload Repository%' -- Diagnostic Pack
or opt.name like '%AWR%' -- Diagnostic Pack
or opt.name like '%Baseline%' --  Diagnostic Pack
or opt.name like '%Diagnostic Pack%' -- Diagnostic Pack
or opt.name like '%SQL Monitoring%' -- Tuning Pack
or opt.name like '%SQL Performance%' -- Tuning Pack
or opt.name like '%SQL Profile%' -- Tuning Pack
or opt.name like '%SQL Tuning%' -- Tuning Pack
or opt.name like '%SQL Access%' -- Tuning Pack
or opt.name like '%Tuning Pack%' -- Tuning Pack
or opt.name like '%Change Management Pack%' -- Change Management Pack
or ddi.edition like 'Enterprise Edition')
order by ddi.host_name;

Matheus.