matheusdba

ASM: Adding disk “_DROPPED%” FORCE

By matheusdba 6 de June de 2015 21 de April de 2020

Ok doke,
First let I make it clear: Adding a disk with force should be avoided, mainly by all the rebalance involved. The best choice, if you has “time”, is to just put disks online, like:

1) ALTER DISKGROUP ONLINE DISK ; or 2) ALTER DISKGROUP ONLINE DISKS IN FAILGROUP ; or 3) ALTER DISKGROUP ONLINE ALL;

But, the post is about adding back to DG the dropped disks.
Let’s imagine, to undestand my situation, you lost the contact with one of your two site storage… In this example, represented by failgroup FGAUX. You would see the disks like this:

SQL> select name,failgroup,state from v$asm_disk a where state <> 'NORMAL';

NAME FAILGROUP STATE ------------------------------ ------------------------------ -------- _DROPPED_0000_DGDATA FGAUX FORCING _DROPPED_0001_DGDATA FGAUX FORCING _DROPPED_0002_DGDATA FGAUX FORCING

So, you know your disks by the name pattern (0 are FGMAIN and 1 are FGAUX, the problematic). You can do something like:

[root@database-host ~]# /etc/init.d/oracleasm listdisks |grep DGDATA DGDATA001 DGDATA002 DGDATA003 DGDATA101 DGDATA102 DGDATA103

Now, make the simple… 🙂

SQL> ALTER DISKGROUP DGDATA ADD FAILGROUP FGAUX DISK 'ORCL:DGDATA101' name DGDATA101 FORCE, 'ORCL:DGDATA102' name DGDATA102 FORCE, 'ORCL:DGDATA103' name DGDATA103 FORCE;

Diskgroup altered.

SQL> ALTER DISKGROUP DGDATA rebalance power 8;

Diskgroup altered.

While waiting the reball, let’s see the disks in DG:

SQL> select * from v$asm_operation where group_number=(select group_number from v$asm_diskgroup where name='DGDATA');

GROUP_NUMBER OPERA STAT POWER ACTUAL SOFAR EST_WORK EST_RATE EST_MINUTES ERROR_CODE ------------ ----- ---- ---------- ---------- ---------- ---------- ---------- ----------- -------------------------------------------- 3 REBAL WAIT 8 SQL> select name,failgroup,state from v$asm_disk a where group_number=(select group_number from v$asm_diskgroup where name='DGDATA');

NAME FAILGROUP STATE ------------------------------ ------------------------------ -------- _DROPPED_0000_DGDATA FGAUX FORCING _DROPPED_0001_DGDATA FGAUX FORCING _DROPPED_0002_DGDATA FGAUX FORCING DGDATA101 FGAUX NORMAL DGDATA102 FGAUX NORMAL DGDATA103 FGAUX NORMAL DGDATA001 FGMAIN NORMAL DGDATA002 FGMAIN NORMAL DGDATA003 FGMAIN NORMAL

And, when the rebalance end, the situation will be OK:

SQL> select * from v$asm_operation where group_number=(select group_number from v$asm_diskgroup where name='DGDATA');

no rows selected

SQL> select name,failgroup,state from v$asm_disk a where group_number=(select group_number from v$asm_diskgroup where name='DGDATA');
NAME FAILGROUP STATE ------------------------------ ------------------------------ -------- DGDATA101 FGAUX NORMAL DGDATA102 FGAUX NORMAL DGDATA103 FGAUX NORMAL DGDATA001 FGMAIN NORMAL DGDATA002 FGMAIN NORMAL DGDATA003 FGMAIN NORMAL

OK? Easy! 😀

Matheus.

Career

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

By matheusdba 5 de June de 2015 21 de April de 2020

This is a second round about this post.

In the first part, I listed the top 8 “Personal Characteristics” of a DBA, to me and 7 other authors. Now, let’s do the same about the “Technical Characteristics”, or just “Skills”…

As I said in the first part of, it’s not an usual subject, so, identify and select these skills was not an easy task. Also as in the first part, I made my own list before the search. And I believe in something like that…

# Top 8 DBA Skills:
(By Matheus)

1) Database Architecture

Components, pools, events, basic flow and structure, reports, logs, traces, views, SQL concepts… In a nutshell, how to use a Database. 

2) Logic and some Programming Skills

Basic programming skills/logic is needed. Programming best practices, techniques and logics base in, at least, 2 different paradigms.

3) Infrastructure Knowledge

Components that surround or are basic services to DB : Networks, Computer Architecture / processors , Storage , Application Server operation , etc.

4) Data Structs and Database Design/Modeling

After all, it’s what database means: understand about structure of objects, partitioning, indexes, statistics, transactions, data modeling, manipulation tools and data migration.

5) Solid SQL and PL/SQL

As a base of data, you must know where data is, how to get it, how to manipulate it efficiently and aligned with good development practices. Isn’t it?

6) Database and SQL Performance Tuning

Wide and the hardest to archive in my opinion. For archive excellence on it, you’ll need all items above. Database operating knowledge, their pools, optimizers, available resources, development/programming and SQL to find gaps in the code, and infrastructure knowledge to understand possible external interference in the functioning of the DB.

7) Security and Oracle Support Interacting

Knowing good patching management practices, how to apply them, techniques and strategies for patch, upgrade and migration, openness and interaction on SRs, navigation Metalink, seraching and understanding of bugs, backup/recovery (DRs) management, access and possible security gaps in infrastructure, systems, or credentials.

8) Knows the Environment / Applications

To know the main applications of the company where you are, it implementation language, some business rules, the environmental behavior, most normal events, peak hours, the operation of legacy applications, people, company practices and their major gaps is essential.

What do you think about my list?
Here is the lists of 5 important references I found, just to have in mind:

# Burleson
– System analisys and design skills
– Database design skills
– Physical disk storage skills
– Databa Secutiry Skills
– Backup and Recovery Skills
– Change Control Management Skills
(http://www.dba-oracle.com/oracle_tips_dba_job_skills.htm)

# Craig Mullins
– Data modeling and database design
– Metadata managements and respository usage
– Database schema creationg and management
– Backup and recovery
– Ensuring data integrity
– Performance management and tunning
– Ensuring availability
– SQL code reviews and walk-thru
– Procedural skills
– Data security
– Capacity planning
– General database management
– General system managements and networking skills
– ERP and business knowledge
– Extensible datatype administration
– Web-specific technology expertise
– Storage management technics
(http://www.craigsmullins.com/dbta_085.htm)

# Mark Spenik and Orryn Sledge
– Knowledge of Structured Query Language (SQL)
– Sound database design
– General understanding of network architectures (for example, Client/Server, Internet/Intranet, Enterprise)
– Knowledge about the database itself
(http://www.developer.com/db/article.php/718491/What-Is-a-Database-Administrator.htm)

# Oracle
– Installing and upgrading the Oracle server and application tools
– Allocating system storage and planning future storage requirements for the database system
– Creating primary database storage structures (tablespaces) after application developers have designed an application
– Creating primary objects (tables, views, indexes) once application developers have designed an application
– Modifying the database structure, as necessary, from information given by application developers
– Enrolling users and maintaining system security
– Ensuring compliance with your Oracle license agreement
– Controlling and monitoring user access to the database
– Monitoring and optimizing the performance of the database
– Planning for backup and recovery of database information
– Maintaining archived data on tape
– Backing up and restoring the database
– Contacting Oracle Corporation for technical support
(http://www.developer.com/db/article.php/718491/What-Is-a-Database-Administrator.htm)

# Wikipedia
– Communication
– Database theory
– Database design
– Technology knowledgements
– SQL & PL/SQL
– Computing architectures
– Operating Systems
(http://en.wikipedia.org/wiki/Database_administrator)

What do YOU think about it?

Liked this post?
Leave a comment and feel free to share it!

Database, Errors and Bugs, RAC | Data Guard

ORA-10456: cannot open standby database; media recovery session may be in progress

By matheusdba 4 de June de 2015 21 de April de 2020

Easy, easy… Take a look:

# Error

db2database2p:>srvctl status database -d database Instance database1 is running on node db1database1p Instance database2 is not running on node db2database2p db2database2p:>srvctl start instance -d database -i database2 PRCR-1013 : Failed to start resource ora.database.db PRCR-1064 : Failed to start resource ora.database.db on node db2database2p CRS-5017: The resource action "ora.database.db start" encountered the following error: ORA-10456: cannot open standby database; media recovery session may be in progress . For details refer to "(:CLSN00107:)" in "/grid/product/11.2.0/log/db2database2p/agent/crsd/oraagent_oracle/oraagent_oracle.log". CRS-2674: Start of 'ora.database.db' on 'db2database2p' failed.

# Solution

Connected to an idle instance.

SQL> startup ORACLE instance started. Total System Global Area 1.1224E+11 bytes Fixed Size 2234920 bytes Variable Size 6.1472E+10 bytes Database Buffers 5.0466E+10 bytes Redo Buffers 299741184 bytes Database mounted. ORA-10456: cannot open standby database; media recovery session may be in progress

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL; Database altered. SQL> ALTER DATABASE OPEN READ ONLY; Database altered. SQL> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options

db2database2p:>srvctl status database -d database Instance database1 is running on node db1database1p Instance database2 is running on node db2database2p

Matheus.

Database, Linux, Shell Scripts, Tools

Grepping Entries from Alert.log

By matheusdba 2 de June de 2015 21 de April de 2020

Hey hey,
One more McGayver by me! Haha
Again to find some information in alert. This time, I’m looking to count and list all occurrences of an action in alert. To archive this, I made the script below.

The functionality is just a little bit more complex than the script of the last post, but stills quite simple. Take a look:

Parameters:
PAR1: name of alert (the main alert.log)
PAR2: Searched token
PAR3: Start day you want to, in the format “Mon dd” or just “Mon”. Below an example.
PAR4: Start Year (4 digits)
PAR5: [optional]End day you want to, in the format “Mon dd” or just “Mon”. The default value is “until now”.
PAR6: [optional]End Year (4 digits). The default value is “until now”. If you use the PAR5, you have to use PAR6.
PAR7: [optional] List All entries and when?. If you want to use this PAR, you must to use PAR5 and PAR6.

Examples (Looking for service reconfigurations):
Ex1: sh grep_entries_alert.sh alert_xxdb_1.log “services=” “Apr 12” 2015
(Seach between April 12 and now and count entries).
Ex2: sh grep_entries_alert.sh alert_xxdb_1.log “services=” “Apr 01” 2015 “May 30” 2015
(Seach between April 01 and May 30 and count the entries).
Ex3: sh grep_entries_alert.sh alert_xxdb_1.log “services=” “Apr 01” 2015 “May 30” 2015 LIST
(Seach between April 01 and May 30 and count the entries and list them all…)

It’s not beautiful. But it works! 🙂

After that, there is the new blog sponsor:

(Hahahaha)

Matheus.

Database, Linux, Shell Scripts, Tools

Grepping Alert by Day

By matheusdba 1 de June de 2015 21 de April de 2020

Hi all,
For that moment when your alert is very big and some OS doesn’t “work very well with it” (in my case was using AIX), I jerry-ringged the shellscript bellow. It puts in a new log just the log entries of a selected day.

The call can be made with two or three parameters, this way:

Parameters:
PAR1: name of alert (the main alert.log)
PAR2: Day you want to, in the format “Mon dd”. Below an example.
PAR3: [optional] desired year. The default is the current year. But is useful specially on the “new year” period…

Examples:
Ex1: sh grep_day.sh alert_xxdb_1.log “Apr 12”
Ex2: sh grep_day.sh alert_xxdb_1.log “Apr 12” 2014

Generated files:
dalert_2015Apr12.log
dalert_2014Apr12.log

Belive me! It can be useful…. haha

See ya!

Matheus.

Database, Errors and Bugs, Migration | Upgrades | Patching

Leap Second and Impact for Oracle Database

By matheusdba 31 de May de 2015 21 de April de 2020

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

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.

Cloud Services | Exadata | Linux, Linux

GB vs GiB | MB vs MiB | KB vs KiB

By matheusdba 30 de May de 2015 21 de April de 2020

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

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.

Database, Migration | Upgrades | Patching

Unplug/Plug PDB between different Clusters

By matheusdba 30 de May de 2015 21 de April de 2020

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:

2. Shutdown BACENDB:

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

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

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

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

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

7.1 How it looks like on alert.log?

7.2 How about the Datafiles?

7.3 Checking database by remote sqlplus:

8. Creating the services as needed:

9. Dropping Pluggable from INFRACDB:

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.

Career

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

By matheusdba 20 de May de 2015 21 de April de 2020

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?

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.

Database, SQL | PLSQL

Oracle Convert Number into Days, Hours, Minutes

By matheusdba 19 de May de 2015 31 de January de 2017

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.