ORA-600 / BLOG / 0x600
Redo, memory, the optimizer, failures and discoveries. Technical, specific, sometimes with a little swearing.
A full index of the existing blog. Articles open on blog.ora-600.pl, preserving their URLs, comments and original formatting.
116 posts
2026-07-13 / BLOG.ORA-600.PL
One of my customers experienced a weird behavior – thousands of child cursors with LANGUAGE_MISMATCH When I looked closer, into V$SQL_SHARED_CURSOR.REASON I saw something puzzling: While this part is normal: Because it means that data type of bind variable changes, the following one makes no sense: It looks like something has changed ‘zl’ into…. ‘zl’. […]
Read article ↗2025-10-13 / BLOG.ORA-600.PL
A year ago I described the basics of checkpoint queue latches: After writing that article, I attended an excellent lecture by Mike Dietrich and Daniel Overby Hansen about noisy neighbors in a multitenant environment and how to make PDBs quieter and easier to live with in a larger society. During that lecture I thought about […]
Read article ↗2025-07-28 / BLOG.ORA-600.PL
I know, I know – AI is stupid… but we shouldn’t be and LLM is a tool that we should get familiar with. That’s why JAS-MIN gets integration with AI in two modes – one-time batch processing and backend assistant. In this article we will focus on the first type of AI usage. Right now […]
Read article ↗2025-04-12 / BLOG.ORA-600.PL
We have no idea when Oracle 23ai will be released on premises. And it is a shame, since this database is offering a lot of great features that you probably would like to learn! One of the solutions is to use Oracle Cloud to learn, another is to use the Oracle Free version… but if […]
Read article ↗2025-02-20 / BLOG.ORA-600.PL
I know I know, it’s getting boring when I constantly talk and write about an evil KVM admin, but tomorrow starts our first meetup with IBM and they are going to explain why my hacks won’t work in Power environment – I can’t wait 😀 In the meantime I realized that I never published the […]
Read article ↗2024-12-13 / BLOG.ORA-600.PL
Not having Diagnostic Pack sucks. But event if you have it, it is not that obvious that you will be able to visualize properly the data and perform a meaningful analyze. That’s why I have created JAS-MIN! A little background: In my work I often have to analyze customer’s databases in order to understand overall […]
Read article ↗2024-08-02 / BLOG.ORA-600.PL
More and more customers are using fancy security tools like CyberArk or other solutions based on Windows RDP with turned off or limited possibility of copy/paste functionalities. And I get it. Security is really important topic and we should take it seriously. But from time to time you have to solve quickly some extremely important […]
Read article ↗2024-06-11 / BLOG.ORA-600.PL
This article is true for Oracle 19.19 and 19.23 on ARM. On x86_64 there’s something funny going on with location of the buffer address in the private memory (finding appropriate register at kcbklbc call)- I need a bit more time to compare those two compilations, but the idea is the same 🙂 During my journey […]
Read article ↗2024-02-10 / BLOG.ORA-600.PL
This is a pain in the ass. You try to import something from a newer database and you hit the error that your timestamp is not good enough! Even tho you have no timestamp datatypes in the dump… The whole case was covered by Mike Dietrich: Let me show you my – a little bit […]
Read article ↗2023-09-18 / BLOG.ORA-600.PL
We are continuing our journey as an evil KVM administrator who wants to mess with a tenants virtual machines. This time we will modify a binary execution code path by changing machine code opcodes in a running process! Let’s image a simple C program, that looks like this: This short code will show "You are […]
Read article ↗2023-09-04 / BLOG.ORA-600.PL
Shit happens. We had recently another customer who can relate to that absolute truth – after a serious storage problem, they managed to restore only data files from one mount point. In theory they were lucky because it was mount point with actual data tablespaces… but they didn’t have any SYSTEM datafiles. It’s like having […]
Read article ↗2023-05-24 / BLOG.ORA-600.PL
So we continue our journey to understand the nature of buffer cache and forcing DBWR process to write our changes to disk. If you want to follow this thread – you should get familiar with at least those blog posts: Until now we have established, that DBWR will write to disk a database block changed […]
Read article ↗2023-03-03 / BLOG.ORA-600.PL
Check out those charts: During my last performance tuning audit I found out that database is consuming big amounts of CPU on parsing. One of the queries that took significant amount of time was rather a small DELETE looking something like this: Funny story tho – the table is usually empty but during the hot […]
Read article ↗2022-12-23 / BLOG.ORA-600.PL
Halloween started really scary this year – some company called us and said, that they have some problems with their Oracle Database Standard Edition. Apparently they had a storage crash six months before, which caused some blocks to get corrupted and from that time no one has noticed that RMAN is not producing any backups. […]
Read article ↗2022-10-02 / BLOG.ORA-600.PL
You may already read my articles about virtualization/cloud security: In the above articles I was impersonating an evil cloud administrator and showing you how this kind of person can access and modify even encrypted data. We will continue this fun, but this time you may also use this trick when you forget root password in […]
Read article ↗2022-05-19 / BLOG.ORA-600.PL
Recently a customer called us that there is something wrong after upgrading Oracle Database to 19.15 on RedHat. Apparently The amount of RAM consumed by DBWR and process servers became really impressive – as a proof I got screenshots looking like this: They said that before the migration it never looked like this and asked […]
Read article ↗2022-05-13 / BLOG.ORA-600.PL
Six years ago I wrote a short instruction on how to restore OCR and Votedisk in RAC 12c after a diskgroup failure. Yesterday I had a possibility of validating my procedure in 21c RAC environment and it has occurred that it is a bit more complicated. Who would have thought? 21 is just 12 backwards! […]
Read article ↗2022-02-14 / BLOG.ORA-600.PL
So you have migrated your databases to cloud and you want to feel a bit more secure – what do you do? Of course you follow the golden rule and you encrypt you tablespaces. That’s reasonable and that’s what vendor recommends. All databases created in Oracle Cloud Infrastructure are encrypted using transparent data encryption (TDE). […]
Read article ↗2021-05-07 / BLOG.ORA-600.PL
Inspired by a little chat with Frits Hoogland about the future of programming languages I challenged myself to learn a bit of Rust and show how to create a shared library to send emails and attach it to Oracle as an external procedure (just as I did with GoLang here: https://blog.ora-600.pl/2021/05/04/golang-as-oracle-external-procedure/) The steps are actually […]
Read article ↗2021-05-04 / BLOG.ORA-600.PL
This short article is a result of the following Twitter activity: So let’s do it! First of all, you have to install GoLang: https://golang.org/doc/install Once this is done, we can create a Go program to send emails. We will use "gopkg.in/mail.v2″ to make it simple. Below you can find a simple GoLang code to send […]
Read article ↗2021-04-15 / BLOG.ORA-600.PL
Remember my post, regarding dumping the SGA to read encrypted blocks? What if I tell you, that you can do the same, while being a KVM host administrator with no credentials to a VM itself? Let’s prepare our secure database in a way I did in article AMM vs security. After enabling Oracle Wallet and […]
Read article ↗2021-04-10 / BLOG.ORA-600.PL
In my previous article I described a few technics of accessing a guest virtual machine from the virtualization host (KVM) without any credentials. It assumed that our data can get compromised by a corrupted vendor employee. After publishing it I had a few talks with my colleagues and have to explain one thing – the […]
Read article ↗2021-03-30 / BLOG.ORA-600.PL
So apparently this cloud-thing is here for good. You may even say that it became endemic 😉 With cloud there is one potential problem – the bigger and heavier it is, the more possible is the leak. Cloud after all is just a virtualization in a big scale and if you go to public cloud […]
Read article ↗2021-01-08 / BLOG.ORA-600.PL
Parse it 🙂 Simple, right? But for sure we are not going to use tkprof for 14 000 trace files, are we? We don’t have Diagnostic Pack and we just started a complete database tracing for an hour or two, because the customer said that they encounter performance problems during this period. Now you have […]
Read article ↗2020-07-13 / BLOG.ORA-600.PL
There’s a lot of articles about installing Oracle XE 18c on Docker. But Docker is not the only containerization technology on the market. In this article, we are going to create Oracle XE 18c installation in Linux Containers (LXD) A bit of history – LXC vs LXD When we start our adventure with Linux Containers […]
Read article ↗2020-01-22 / BLOG.ORA-600.PL
In my previous post I described a case of "enq: TX – row lock contention" that was actually a network latency problem. More and more those kinds of problems (it seems like history happens all over again) caused my friend (Radosław Kut) and me to write a simple tool to analyze SQL performance from a […]
Read article ↗2020-01-09 / BLOG.ORA-600.PL
So we had a doubtful pleasure of migrating a few databases from Oracle Exadata X3-2 to Oracle Exadata X7-2 Cloud at Customer. Why doubtful? Well, this a material for a whole different story with a lot of beer – let me just say, that CC gen 1 was a bit rough around the edges 😉 […]
Read article ↗2019-09-27 / BLOG.ORA-600.PL
You probably heard by now about Oracle Cloud Free Tier While it sounds good, the only databases that you can use are in fact autonomous databases (which are not interesting for a tech, geeky guy like me). Of course, you have also "2 virtual machines with 1/8 OCPU and 1 GB memory each." I thought: […]
Read article ↗2019-03-20 / BLOG.ORA-600.PL
Python is awesome. I like Python very much, but there are some cases where Python’s performance is just not enough. What other options do we have? Well, there’s for example GoLang Problem with GoLang is that there is not a lot on the Internet about using it with Oracle Databases. Let me show you some […]
Read article ↗2018-10-04 / BLOG.ORA-600.PL
At Trivadis Performance Days 2018 (awesome event by the way) I promised to deliver ODBV3 with support for ASM – and here it is! 🙂 https://github.com/ora600pl/odbv To use it, you have to have access to a user at ASM level with at least SYSDBA privilege. In the connect string you don’t have to add AS […]
Read article ↗2018-09-17 / BLOG.ORA-600.PL
It has been crazy few months – organizing POUG2018 took a lot of energy but it was satisfying as hell! 😀 This weekend I had some time to prepare a new version of ODBV for Trivadis Performance Days 2018 where I’ll be talking about the internals of database block storage. The series of articles about […]
Read article ↗2018-07-10 / BLOG.ORA-600.PL
After my last article AMM vs security, Martin Berger wrote to me: well, even without AMM you can do it: write your own process which attaches to the same shm segments – and use its memory mapping (?) My response was that it is also possible with ASMM but AMM makes it extremely easy. And […]
Read article ↗2018-05-18 / BLOG.ORA-600.PL
Most of us already know that AMM sucks. But usually, we think about disadvantages of AMM in terms of performance. Let’s see why it sucks in the terms of security 😉 Let’s create an encrypted tablespace for HR.EMPLOYEES and protect HR schema with Database Vault. Contents of sqlnet.ora Now we will use dbca to configure […]
Read article ↗2018-02-14 / BLOG.ORA-600.PL
In my last blog post I explained a XOR alghorithm that is used to count Oracle database block checksum. I also wrote, that sometimes you are facing problems, that are unresolvable without a low-level knowledge. This is the story of this kind of situation. The story of misread documentation. The story of haste and hex. […]
Read article ↗2018-01-28 / BLOG.ORA-600.PL
Recently I’ve started to write my own clone of BBED to have something handy and useful in extreme cases when you have to go deep and fix stuff on low level (I have only like 2 such cases a year but each time it is really fun and a nice money 😉 ) When I’ll […]
Read article ↗2017-12-21 / BLOG.ORA-600.PL
Some time ago I wrote a simple tool to learn about Oracle data block internals – ODBV. The series of articles can be found here: https://blog.ora-600.pl/?s=odbv&submit= and the github repo is here: https://github.com/ora600pl/odbv This is not a production tool but during the last session in Birmingham at UKOUG_TECH17 – where I was doing a presentation […]
Read article ↗2017-10-28 / BLOG.ORA-600.PL
If you work as a consultant, getting remote access to your customer is sometimes a nightmare! Even when you’ll get one, there a times when you just want to get your job done and finish this nightmare. For example, there are times when it is easier to dump a statspack repo and do offline analyzes. […]
Read article ↗2017-10-02 / BLOG.ORA-600.PL
(Please tell me that I’m not the only one who thinks "Index Organized Table" instead of "Internet Of Things" when hearing IOT…) This post is inspired by Connor McDonald and his blog post from a year ago about direct mode operations and IOTs. You can read it here: https://connor-mcdonald.com/2016/07/04/direct-mode-operations-on-iots/amp/ While writing a redo parser for […]
Read article ↗2017-09-20 / BLOG.ORA-600.PL
While researching redo log internals for V00D00 we had to face the fact, that we know shit about real transactional behavior. When I say "real", I mean – under the hood. Even with a very simple stuff like COMMIT and ROLLBACK we were constantly amazed by the internal mechanisms. Today let’s take ROLLBACK under the […]
Read article ↗2017-08-21 / BLOG.ORA-600.PL
Before you proceed, please check out this short article written by Tanel Poder: https://blog.tanelpoder.com/2010/09/02/which-number-takes-more-space-in-an-oracle-row/ In the documentation, you can find the following explanation about the internal numeric format: Oracle stores numeric data in variable-length format. Each value is stored in scientific notation, with 1 byte used to store the exponent and up to 20 bytes […]
Read article ↗2017-07-24 / BLOG.ORA-600.PL
During a research for VOODOO, we came across a lot of interesting stuff inside REDO. One of my favourites is an UPDATE, becoming an INSERT 🙂 So let’s see what has happened – I performed the following update on a sample table: The regular dump of redo log with an UPDATE looks like this: In […]
Read article ↗2017-07-05 / BLOG.ORA-600.PL
Contest time! I had a cool function in my database: When executed, it returned a -15% voucher for POUG conference. And POUG is a REALLY COOL confernece 🙂 Check it out: poug.org The problem is that I was cleaning my training database and dropped the function by accident. Fortunately, I have archivelogs and I wrote […]
Read article ↗2017-05-28 / BLOG.ORA-600.PL
Sorry for long time without post, but we are writing with Marcin Rydz a new heterogenous replication product based on archivelogs and it’s consuming a looooooot of our time 🙂 Yes I know it’s nothing new on the market. There are other cool products and I know they’ll be probably be better. But we want […]
Read article ↗2017-03-12 / BLOG.ORA-600.PL
Thanks to suggestions made by Frits Hoogland, I made some improvements to the ODBV. The new version can be found here: http://ora-600.pl/oinstall/odbv.x86_64 The changes are: Recognition of first, second and third level bitmap block Recognition of pagetable segment header Block number ranges on the left side The blocks will be coloured properly to belonging segment. […]
Read article ↗2017-02-01 / BLOG.ORA-600.PL
It’s time for the next article with ODBV visualisation 🙂 This time let’s examine the difference between move and shrink – this is very common question on a lot of trainings. You can find a lot of great articles in the Internet regarding this subject but I think that visualisation really helps to understand what […]
Read article ↗2016-12-30 / BLOG.ORA-600.PL
From time to time I get questions on my trainings, what is the difference between TRUNCATE TABLE and TRUNCATE TABLE DROP STORAGE… well, there is no difference because DROP STORAGE is default 😉 DROP STORAGE Specify DROP STORAGE to deallocate all space from the deleted rows from the table except the space allocated by the […]
Read article ↗2016-11-03 / BLOG.ORA-600.PL
Recently I wanted to demonstrate to some people on my training, how Oracle database maintains blocks in a datafile – what happens after truncate, truncate with drop storage clause, delete, regular insert, direct path insert and so on… I didn’t find any tool for that so I’ve written my own. It’s core code is based […]
Read article ↗2016-10-11 / BLOG.ORA-600.PL
Joining the OTN Appreciation Day 🙂 I really like the new pragma UDF feature for PL/SQL – it makes me believe that context switches will be less painful in the future 🙂 I wrote a little bit more about it here: https://blog.ora-600.pl/2015/10/29/oracle-12c-pragma-udf-the-truth/ And Martin Widlake wrote a lot about it here: https://mwidlake.wordpress.com/2015/11/11/pragma-udf-some-current-limitations/
Read article ↗2016-09-19 / BLOG.ORA-600.PL
I know – everyone knows, that PL/SQL packages are faster than stored procedures. If you’ll ask anyone at the training or in your dev team "what is better" – you’ll (almost) always hear: PL/SQL packages. But why exactly? The documentation says: The first time you invoke a package subprogram, Oracle Database loads the whole package […]
Read article ↗2016-09-14 / BLOG.ORA-600.PL
Years pass by and I think that the more I’m trying to understand the Oracle RDBMS – the less I know. Recently I started to examine the behavior of session cursor cache and I noticed an interesting thing. But let’s start from the beginning like we should 🙂 The documentation says: About the Session Cursor […]
Read article ↗2016-09-01 / BLOG.ORA-600.PL
Since the promotion tickets for the first international Oracle conference in Poland (poug.org/en) are sold out, I’ve decided to create a little contest allowing you to win a voucher code for -20% of the current ticket price. The task is easy, but you have to use DBA and DEV skills to resolve it. So the […]
Read article ↗2016-07-31 / BLOG.ORA-600.PL
Sometimes you drop something by accident – you know, the syndrome called "An Enter Too Far". If you drop a table, the case is easy – you can use recyclebin to restore it. But when you drop a package or procedure… well, you have a problem 🙂 Of course, you could use a flashback query […]
Read article ↗2016-07-27 / BLOG.ORA-600.PL
This will be just short article with very simple trick, showing how to match the ASMLib devices with actual multipath devices. I’m sure there is a simpler way… but I just love using AWK 🙂 If you know a simpler or more elegant solution – please post it in the comments section. My solution looks […]
Read article ↗2016-06-14 / BLOG.ORA-600.PL
Let’s fuckup the cluster! Of course after this operation, the final state of the processes can look like this: The cssd service will not be able to start, because there are no voting disks: OK, so let’s try to stop the cluster services: Now we will have to start CRS in exclusive mode and start […]
Read article ↗2016-05-27 / BLOG.ORA-600.PL
Well it has been a month since my last blog post, so I think it’s time to write something 🙂 Those context switches can be a real pain in the ass – there is a great article by Frits Hoogland about context switching from SQL to PL/SQL – you can read it here: https://fritshoogland.wordpress.com/2016/01/23/plsql-context-switch/ You […]
Read article ↗2016-04-26 / BLOG.ORA-600.PL
Virtual hosts on Exadata with OVM are HVM and not PV. This is one of the limitations of Infiniband SR-IOV – can’t be used with PV. So there is a qemu used to emulate the hardware While accessing a physical device from within a DOMU we can see that actual work is being done on […]
Read article ↗2016-04-03 / BLOG.ORA-600.PL
This week I had a "pleasure" of reimaging Exadata server to use OVM. During this process we hit interesting problem – after the reimaging process there was no bridge at DOM0, corresponding to the client network – because of this, the OEDA sofware returned an error like this: The error was produced by this script: […]
Read article ↗2016-03-16 / BLOG.ORA-600.PL
I’ve created recently a script in AWK to create wait event histogram from 10046 trace file. The script can be found here. I thought that a good idea would be creating a little script to analyze the contents of 10053 and 10046 events together. So I wrote one 🙂 You can download it here: http://ora-600.pl/oinstall/format_10046_10053.awk […]
Read article ↗2016-03-08 / BLOG.ORA-600.PL
I wrote a very simple script in AWK to create wait event histogram (for db file scattered read, db file sequential read, direct path read and direct path read temp) based on 10046 trace file with wait events. Maybe someone will find it useful 🙂 The sample output looks like this: Enjoy 😉
Read article ↗2016-03-07 / BLOG.ORA-600.PL
I was second time at the conference organized by UKOUG but this time I had a pleasure to be among the speakers 🙂 To reach the level set by other speakers was impossible – but trying it gives a lot of fun. Since I did a live demo and I didn’t have any slides – […]
Read article ↗2016-02-11 / BLOG.ORA-600.PL
Some time ago one of my students asked me if temp segments are being written to flash disks on Exadata… Well I wasn’t sure 🙂 But recently I had some time to check it. Let’s create some query that will generate temp segment: Great. Now we have to do some tracing at the cell servers […]
Read article ↗2016-02-10 / BLOG.ORA-600.PL
I’ve wrote about privilege escalation with external tables in this post: https://blog.ora-600.pl/2014/12/23/simple-technics-of-privilege-escalation-part2-dbasysdba/ This time we will try to list all files within directory object, to which we have no EXECUTE privs. This is possible due to great new feature of database 12c that allows to use metacharacters to match multiple files in external table. I […]
Read article ↗2016-02-08 / BLOG.ORA-600.PL
I had a pleasure to work with my colleagues from Oracle – Radosław Kut and Krzysztof Marciniak – at comparing the performance of In-Memory queries between Sparc M7, Intel® Xeon® X5670 and Intel® Xeon® E5-2699. You can find results of our findings in this presentation: https://www.oracle.com/webfolder/s/delivery_production/docs/FY16h1/doc17/DB12c-on-SPARC-M7.pdf Following their methodology I’d like to present appropriate results […]
Read article ↗2016-01-27 / BLOG.ORA-600.PL
Thanks to Oracle I had a possibility to test the new Sparc M7 with DAX coprocessors to boost In-Memory performance. You can read about it here and here My first thought was – how to check if and when the DAX coprocessors are being used? When you have a POC for Exadata, you want to […]
Read article ↗2016-01-13 / BLOG.ORA-600.PL
Let’s imagine the following situation – you have a table called EMPLOYEES_TMP which is a global temporary table with a variable number of rows – totally nondeterministic. And there is a query which runs very often to compare primary key values between this table and a remote one (which is basically the same but not […]
Read article ↗2016-01-10 / BLOG.ORA-600.PL
ORA-600 i IT School wspierają XXIV finał Wielkiej Orkiestry Świątecznej Pomocy. Nasze połączone siły dewelopersko-administratorskie przygotowały aplikację, za pomocą której będziecie mogli licytować złote serduszka oraz karty używając do tego celu komputerów lub urządzeń mobilnych. Wejdźcie i zalicytujcie na: https://licytacja.wosp.org.pl/. Cieszymy się, że możemy mierzyć wysoko razem z WOŚP. W tym roku pieniądze zostaną przeznaczone […]
Read article ↗2015-12-17 / BLOG.ORA-600.PL
At UKOUG Tech’15 (Super Sunday) there was a session called "12c: A Closer Look at the Multi-Process Multi-Threaded Model for Oracle on Linux/Unix" by Markus Flechtner. Since then I can’t stop thinking about the performance of this feature. Why Oracle decided to provide it? I did some tests regarding sort operations and threaded execution was […]
Read article ↗2015-10-29 / BLOG.ORA-600.PL
Let’s check, why PRAGMA UDF makes execution faster, then regular function. C function responsible for calling a PL/SQL code from SQL is called "plsql_run": Now let’s try to create a new HR session and create a simple function which will multiple values by 2: From other terminal I will connect to the HR session with […]
Read article ↗2015-10-11 / BLOG.ORA-600.PL
I’ve already showed you, how to escalate privileges in Oracle Databases. https://blog.ora-600.pl/2013/04/02/privilege-escalation-in-oracle-11gr2-part1/ https://blog.ora-600.pl/2014/12/23/simple-technics-of-privilege-escalation-part2-dbasysdba/ The question is – how to secure your database, if you don’t have EE or possibility to buy Oracle Database Vault or Oracle Advanced Security. Well – there’s always a DBA creativity 😉 For example – if you want to secure the […]
Read article ↗2015-09-04 / BLOG.ORA-600.PL
I było tak, że 31 dnia miesiąca sierpnia roku 2015, spotkali się entuzjaści Wyroczni w posiadłości AVIVA. I było tak, że dzierżąc kufle – po brzegi browarem wypełnione – zasiadło wielu aby posłuchać kilku, którzy chcieli się przemyśleniami swoimi podzielić. I stało się tak, że admin przepijał do dewelopera a deweloper do admina i pospołu dyskusja toczyła się wartka a […]
Read article ↗2015-09-03 / BLOG.ORA-600.PL
I’ve started the project called RICO – the tool, written in C++ for last resort data recovery. It can be used to extract data from corrupted datafiles and dump them in the form of text files. It is in the early development stage right now and the functionalities are narrowed to: Dump regular and partitioned […]
Read article ↗2015-09-03 / BLOG.ORA-600.PL
One of the new CBO features in database 12c is "sql plan directives". The main purpose of this functionality is to deliver better adaptive plans if object statistics does not reflect the reality. Quoting after documentation During SQL execution, if a cardinality misestimate occurs, then the database creates SQL plan directives. During SQL compilation, the […]
Read article ↗2015-07-17 / BLOG.ORA-600.PL
Recently one of my students asked me if there is any correlation between the AU size in ASM diskgroup and parameter db_file_multiblock_read_count. I made some tests but results are very strange. I have 4 same tables, located in 4 different diskgroups: All tables are smaller then a hidden parameter "_small_table_threshold" so table access full on […]
Read article ↗2015-05-25 / BLOG.ORA-600.PL
At the beginning we have to prepare parameter file for the new instance Start the instance in nomount Since I want to duplicate the database in active mode I will register it in LISTENER. To achieve this I have to add the following lines to the listener.ora Now we can perform active database duplication with […]
Read article ↗2015-05-14 / BLOG.ORA-600.PL
Let’s try to migrate database to ASM diskgroup without big maintenance window. I have database and ASM (11.2.0.4) – the database is prepared to migration: We will use recoverable copy of database to migrate to ASM. The first run of this script will create copy of datafile on ASM: Subsequent executions of this script will […]
Read article ↗2015-05-14 / BLOG.ORA-600.PL
I have configured two devices for ASM – one with ASMLib and one with UDEV: I have downloaded 11.2.0.4 grid infrastructure binaries and prepared response file: Of course in production environment you have set different groups for security reasons. If you think that you can ignore security, read my articles regarding privilege escalations: https://blog.ora-600.pl/category/oracle/security/ With […]
Read article ↗2015-05-03 / BLOG.ORA-600.PL
There is a new feature in Oracle Spatial 12c, called "Spatial Vector Acceleration". According to Oracle: One of the most notable improvements with Oracle Spatial and Graph 12c can be seen in performance increases in vector data operations. Enabling the Spatial Vector Acceleration feature (available with the Spatial option) dramatically improves the performance of commonly […]
Read article ↗2015-04-17 / BLOG.ORA-600.PL
In this article I will show how to change IP address of cluster interconnect. First we have to define new address of the cluster interconnect before we change network settings. And now stop all cluster resources on both nodes Now we can change cluster interconnect ips Now we can start init cluster resources on both […]
Read article ↗2015-04-17 / BLOG.ORA-600.PL
In this article I will show how to reconfigure IP addresses in public network for RAC. ` First we have to close all cluster resources This command can hang on stopping ASM. It’s normal, because the resource CRSD is using OCR, which is located on ASM diskgroup. In ASM alertlog you will see errors like: […]
Read article ↗2015-04-16 / BLOG.ORA-600.PL
Procedure to convert a standalone database to RAC 11gR2 I have a single-instance database and I want to convert it to RAC with minimum downtime. The first step is to enable RAC option by recompiling the binaries (YOU HAVE TO STOP THE DATABASE FIRST!!!) After this operation I can startup instance and proceed to migration. […]
Read article ↗2015-04-15 / BLOG.ORA-600.PL
Add a new node to RAC Check the network configuration for both nodes On the with active cluster, check the network configuration Configure appropriate network devices on the node, which I want to add Create appropriate user on the new node: Configure shared storage – first install ASMLib drivers (if you are using ASMLib :)) […]
Read article ↗2015-04-15 / BLOG.ORA-600.PL
Change VOTE and OCR location First we have to create new ASMLib binding Then we have to create new ASM diskgroup and set the appropriate attributes (at least ‘compatible.asm’) Then we can add a new location for OCR. And remove the old location Now we can replace the voting disk location
Read article ↗2015-04-09 / BLOG.ORA-600.PL
There is a great new feature related to the 12c database. The feature that will resolve a lot of problems with migrating databases between endian formats. And thanks to our new partner – Omnitec, who agreed to share with their SPARC resources – I will be able to demonstrate you this powerful solution. Oracle has […]
Read article ↗2015-03-12 / BLOG.ORA-600.PL
Since the documentation to install Oracle Database on the Solaris SPARC 64bit is so lame, I wrote the short step-by-step instruction on how to install the database, using response file. I assume, that the network is configured and all appropriate packages are installed, so let’s start with configuring project and kernel parameter for user oracle: […]
Read article ↗2015-02-24 / BLOG.ORA-600.PL
There is some kind of wrong understanding, regarding what Oracle ASM really is. A lot of people thinks, that this is some kind of file system, provided by Oracle and that Oracle Database reads data "from ASM", while the truth is, that the database reads data directly from the physical raw device with assistance of […]
Read article ↗2015-02-09 / BLOG.ORA-600.PL
There are a lot of new powerful features in the 12c database but don’t forget about the little features that can make a DBA happier on the day to day basis 🙂 One of my favourites is a new approach to use a DBCA command line interface to create a database in silent mode: Nice, […]
Read article ↗2015-01-28 / BLOG.ORA-600.PL
Recently there was a new vulnerability introduced in glibc library: http://www.openwall.com/lists/oss-security/2015/01/27/9 Oracle is using the gethostbyname function in "*skgxp*" libraries: So this can be an issue – a specially in RAC environments. RedHat recommends to upgrade the glibc libraries: https://access.redhat.com/articles/1332213 And I do agree 😉
Read article ↗2015-01-27 / BLOG.ORA-600.PL
One of my customers had a problem with a very slow TRUNCATE TABLE operation on a partitioned table having 22 000 partitions. The problem has raised after a test migration to database 12c. I’ve prepared a little demo to address this issue and show the walkaround for it. Oracle Database 12c (12.1.0.2): Oracle Database 11g […]
Read article ↗2014-12-23 / BLOG.ORA-600.PL
A lot companies consolidates databases into one appliance – like for example Oracle Exadata. So you can have a lot of different databases in one physical cluster. And what if I tell you that you can execute any OS command as an oracle user, having just access to a database user with appropriate privileges? What […]
Read article ↗2014-12-06 / BLOG.ORA-600.PL
Ludzie z PLUG (http://www.linux.org.pl/) zorganizowali w tym roku pewną wyjątkową konferencję – dBConf (https://www.dbconf.pl/2014/pl/) pod hasłem "Bazodanowcy wszystkich środowisk łączcie się!". Idea zacna i mam nadzieję, że zyska popularność na polskiej scenie konferencyjnej, bo jak często się zdarza, żeby Oracle’owiec pogawędził przy piwku ze specem od DB2 lub SQL Server’a? Wymienił się uwagami z fanem […]
Read article ↗2014-10-10 / BLOG.ORA-600.PL
Hash and sort operations are perfomed in PGA. Quoting after the documentation: A sort operator uses a work area (the sort area) to perform the in-memory sorting of a set of rows. Similarly, a hash-join operator uses a work area (the hash area) to build a hash table from its left input. If the work […]
Read article ↗2014-09-27 / BLOG.ORA-600.PL
In Oracle 12c (12.1.0.2 to be exact) we can find a cool new feature: Oracle ASM Filter Driver. Quoting after the documentiation: Oracle ASM Filter Driver (Oracle ASMFD) is a kernel module that resides in the I/O path of the Oracle ASM disks. Oracle ASM uses the filter driver to validate write I/O requests to […]
Read article ↗2014-09-15 / BLOG.ORA-600.PL
A few months ago I showed how to restore a controlfile, when you have no idea in which backupset is the newest file https://blog.ora-600.pl/2014/04/26/oracle-myth-you-need-dbid-to-restore-the-controlfile/ On last training one of my students asked me, how to locate an appropriate spfile backup after using this trick (the restored database has a different DBID). Well, you can just […]
Read article ↗2014-09-10 / BLOG.ORA-600.PL
Sometimes you have to export all metadata for a schema but you have no access to data pump or other tools. SQL Developer can cause a lot of problems with referential constraints. But there is always a good old SQL*Plus 🙂 http://ora-600.pl/oinstall/metadata.sql
Read article ↗2014-08-26 / BLOG.ORA-600.PL
http://www.itcertificationmaster.com/become-oracle-certified-master-fun-profit-kamil-stawiarski/
Read article ↗2014-06-17 / BLOG.ORA-600.PL
In Oracle 12c there is a possibility to define a PL/SQL function inside the "WITH" clause, which can reduce the context switching overhead. Short example:
Read article ↗2014-06-16 / BLOG.ORA-600.PL
Install Oracle RAC 11gR2 on VirtualBox: http://www.youtube.com/playlist?list=PLqQNL6H71xCygSF_VqwM8j-Hhft4XoEEx VM templates can be found here: https://mega.co.nz/#!esp1kQQA!CUr8SlErdPppk-GkxedTUAdMpVQPrrrSI3c1ouquAhk Prezi: Route to install simple RAC
Read article ↗2014-06-16 / BLOG.ORA-600.PL
Instead of bonding you can use Oracle cluster interconnect HAIP for using multiple ethernets. And here is how its done 🙂 At the begining I have one interconnect – eth1: To add another one (eth3), I will use an oifcfg tool: Now the restart of the CRS on all nodes is required for changes to […]
Read article ↗2014-06-11 / BLOG.ORA-600.PL
Moja prezentacji z konferencji COIG http://prezi.com/ov5xbjytuzgw/?utm_campaign=share&utm_medium=copy&rc=ex0share
Read article ↗2014-05-29 / BLOG.ORA-600.PL
2014-05-07 / BLOG.ORA-600.PL
We had to change the hostnames on Exadata with one of the customers. It is a well known fact that RAC doesn’t like such changes – we had to reconfigure the cluster and after the whole process I found the funny thing: So let’s try to turn RDS on 🙂 After shutting down all cluster […]
Read article ↗2014-04-26 / BLOG.ORA-600.PL
Oracle 12c offers a lot of fascinating new features – among them (at last) "FETCH FIRST n ROWS ONLY" syntax. http://www.oracle-base.com/articles/12c/row-limiting-clause-for-top-n-queries-12cr1.php Out of curiosity I’ve checked with 10053 event, the following query: And this was the result 🙂
Read article ↗2014-04-26 / BLOG.ORA-600.PL
My experience shows that you don’t have to know the DBID now for restoring contorlfile from backup. If I know the name of backup piece, that contains controlfile I can do the following scenario: If I don’t know the name of the backup piece and there is a lot of strangely named files in a […]
Read article ↗2013-09-26 / BLOG.ORA-600.PL
Oracle Cost Based Optimizer consists of three main components: Query Transformer, Esitmator, Plan Generator. In this article I will try to show you some interesting features of the first component – Query Transformer. This component has some very powerful features and most of them remain in shadows, unrevealed while other new features gains all the […]
Read article ↗2013-06-12 / BLOG.ORA-600.PL
Tak dobrych referencji jeszcze chyba nigdy nie dostałem 🙂 I gdybym cechował się jakąkolwiek skromnością, to bym tego nie publikował. http://rafalosinski.com/lifetech/najlepsza-znana-mi-prelekcja-czyli-warsztatu-z-hackowania-oracle-na-infoshare/
Read article ↗2013-04-30 / BLOG.ORA-600.PL
Do you realy know SQL? I mean Oracle SQL…. Maybe you should take a closer look: http://ora-600.pl/art/sql-for-modeling.pdf
Read article ↗2013-04-02 / BLOG.ORA-600.PL
A few simple techniques of escalating privileges in database Oracle 11.2.0.3. http://ora-600.pl/art/oracle_privilege_escalation.pdf
Read article ↗2013-03-15 / BLOG.ORA-600.PL
Oracle stworzył Database Vault’a, żeby chronić wrażliwe dane przed administratorem DB. Po instalacji produktu i stworzeniu tzw. REALM’ów, SYSDBA traci uprawnienia do określonych czynności (m.in. zakładanie kont i wiele poleceń ALTER) ale głównie traci możliwość wybierania danych z tabel chronionych takim REALM’em. Sam Vault jednak nie wystarczy, bo admin mający dostęp do plików danych może […]
Read article ↗2013-02-07 / BLOG.ORA-600.PL
Dzięki mojej uroczej i trochę zwariowanej asystentce – Klaudynie – udało mi się na ostatniej konferencji CareerCon we Wrocławiu, przedstawić w nieco niestandardowy (niektórzy twierdzą, że szalony) sposób ideę wdrożeń aplikacji w klastrze/chmurze – na podstawie Oracle RAC 11gR2. Jeśli chcecie wiedzieć jakie podwaliny pod dzisiejszą "chmurę" podłożył Juliusz Cezar i jakie są najnowsze trendy […]
Read article ↗2013-02-07 / BLOG.ORA-600.PL
Ostatnio przyszło mi instalować RAC’a 11.2.0.3 na Solarisie 11. Zadanie okazało się bardziej zabawne niż mi się wydawało. Całość opisałem na GoldenLine: http://www.goldenline.pl/forum/3174524/solaris-11-i-oracle-11
Read article ↗2012-06-13 / BLOG.ORA-600.PL
Całkiem niedawno przyszło mi optymalizować jedno niesforne zapytanie, korzystające z indeksu sptialowego – właściwie to niesforność polegała właśnie na tym, że ten index za nic w świecie nie chciał zostać użyty w procesie budowania planu. Sytuację opisałem na forum GoldenLine: http://www.goldenline.pl/forum/2937348/indexy-spatialowe/s/1#50027638 – zapraszam do dyskusji. Dla tych, którzy nie są członkami grupy "Optymalizacja Oracle", załączam jeszcze […]
Read article ↗2012-06-10 / BLOG.ORA-600.PL
Prowdząc niedawno optymalizację wydajności jednego RACa, natknąłem się na ciekawe zachowanie w trakcie parsowania zapytań odwołujących się w predykacie do typu TIMESTAMP – niecodzienne zachowanie dotyczy ustawienia CURSOR_SHARING=FORCE i bazy 11.1.0.7 oraz 11.2.0.2. Problem opisłem na forum GoldenLine – jeśli spotkaliście się z podobnym problemem lub znaleźście jakiegoś patcha – zachęcam do podzielenia się tą […]
Read article ↗2012-06-10 / BLOG.ORA-600.PL
Baza Oracle 10g XE dostarcza zainstalowanego APEX’a – jest to jednak stara wersja tego narzędzia, nie pozwalająca na tworzenie własnych apliacji. http://www.itschool.pl/blog/instalacja_oracle_apex_na_bazie_danych_oracle_10g_xe
Read article ↗2012-06-10 / BLOG.ORA-600.PL
Stworzenie bazy Oracle z kreatora, takiego jak DBCA jest niezwykle proste, jednak nie pozwala nam na zgłębienie zależności między poszczególnymi plikami. Warto przynajmniej raz przećwiczyć sobie postawienie bazy "z palca", dla lepszego zrozumienia mechanizmów. http://www.itschool.pl/blog/instalacja_i_konfiguracja_oracle_11g_r1_kreowanie_bazy_danych
Read article ↗2012-06-10 / BLOG.ORA-600.PL
Maksymalnie trzy wirtualne maszyny starczą na postawienie działającego RAC’a w dowolnym środowisku. Obecnie można nawet obyć się bez maszyny z iscsi-target, bo nowy VirtualBox pozwala na współdzielenie dysków między wirtualnymi systemami. Oczywiście znów nie chce mi się pisać od nowa czegoś, co już raz zostało przeze mnie zrobione – zamiast tego załączam linki: http://www.itschool.pl/blog/instalacja_klastra_oracle_rac_11g_r2_cz_1 http://www.itschool.pl/blog/instalacja_klastra_oracle_rac_11g_r2_cz_2 […]
Read article ↗2012-06-10 / BLOG.ORA-600.PL
Ponieważ leniwe ze mnie bydle, zacznę od umieszczenia odnośników do rzeczy, które już poumieszczałem na różnych innych serwisach 🙂 Zdarzyło mi się kiedyś wystąpić na PLOUG’u i poprowadzić wykład z nowych cech Oracle 11g – oto co powstało w bolesnym wysiłku twórczym: www.ploug.org.pl/konf_08/materialy/pdf/19_Stawiarski.pdf
Read article ↗NEXT COMMAND / LET’S TALK