ODA X5-2 simplifies and speeds up the creation of a 12c database quite considerably with oakcli. You can take advantage of this command by also using it in the creation of physical standby databases as I discovered when I had to setup Dataguard on as many as 5 production and […]
Harry Dragstra
ETL using Oracle DBMS_HS_PASSTHROUGH and SQL Server
While I prefer a “loosely coupled architecture” for replication between Oracle and SQL Server, sometimes a direct (database) link cannot be avoided. By using DBMS_HS_PASSTHROUGH for data extraction the 2 other ETL processes (transformation and load) can be configured and administered with more flexibility, providing an almost acceptable level of […]
Golden Gate 12c and DIY Sequence Replication with PL/SQL
Recently, while migrating AIX 11gR2 Databases to Oracle Linux 12cR1 on an ODA X5-2, our setup of Sequence Replication by Oracle Golden Gate appeared to be faulty. The target side sequences were not automatically incremented. The problem came to light during the migration of acceptance databases, and under some time […]
Better track the Usage of Database Options and Management Packs, or it will cost you
So here it is Oracle announces a license audit, some urgency kicks in and this familiar but also really serious question comes down from management: “Are we using any unlicensed database features“. The seriousness is quite understandable, because if so, the company can look forward to some negotiations with Oracle […]
Dump Oracle data into a delimited ascii file with PL/SQL
This is how I dump data from an Oracle Database (tested on 8i,9i,10g,11g,12c) to a delimited ascii file: Next to the query and the generated filename the Dump_Delimited function takes another 6 parameters, each one with a default value. Check out the PL/SQL, and BTW… the basics for this code […]
DIY Parallelization with Oracle DBMS_DATAPUMP
Oracle dbms_datapump provides a parallel option for exports and imports, but some objects cannot be processed in this mode. In a migration project from AIX 11gR2 to ODA X5-2 ( OL 5.9 ) 12c that included an initial load for Golden Gate, I had to deal with one of those […]
How About Oracle Database 12c Threaded_Execution
THREADED_EXECUTION Threaded_Execution is an Oracle Database 12c feature aiming to reduce the number of Oracle processes on LINUX. After setting parameter THREADED_EXECUTION on TRUE and a database bounce, most of the background processes are threads within just 6 Oracle processes, where more than 60 processes existed before the bounce. And […]
Oracle 12c STIG Password Generator in PL/SQL
Creating or modifying an Oracle Database user password can be done by using any standard password generator you can find on the WEB. But I wanted a password to comply to “ora12c_strong_verify_function”, and this isn’t as easy to generate as you might expect. Though most generators provide options to include […]
How-to bulk delete ( or archive ) as fast as possible, using minimal undo, redo and temp
Deleting some rows or tens of millions of rows from an Oracle database should be treated in a completely different fashion. Though the delete itself is technically the same, maintaining indexes and validating constraints may have such a time and resource consuming influence that a vast amount of undo and […]
How-to set the current database schema of an application using a global context
Question: We have customers who want to work with different database schemas and we want to determine dynamically – depending on a choice during or after login – which database schema the application in a given session should use. Is it possible to set this up in the database? Answer: […]
SQL> Select * From Alert_XML_Errors;
Once you are able to show the xml version of the alert log as data in database table Alert_XML, it would be nice to checkout the errors with accompanying timestamps from within view Alert_XML_Errors. Like this, with the help of 2 types and a pipelined function. And checkout the errors […]
SQL> Select * From Alert_XML;
By mapping an external table to some text file, you can view the file contents as if it were data in a database table. External tables are available since Oracle 9i Database, and from Oracle 11gR2 Database on, it is even possible to do some inline preprocessing on the file. […]
Fast delete of many files in LINUX and WINDOWS
One of my customers ( Oracle Database 11gR2 on LINUX ) never noticed the Oracle software LUN filling up until a “df -h” reported 97% used, wondered why, and of course wanted it to be solved asap. Well, after some investigation I found the adump directory to be the problem. […]
Easy setup of the query_partition_clause of row_number() for dedup(lication)
Finding and getting rid of duplicate records in a table is easy…. The row_number() over ( <query_partition_clause> <order_by_clause> ) analytic function is helpful in determining not only the number of duplicate records, but also in identifying them. Setting up the partition clause though with each and every attribute that is […]
How-to backup Oracle RAC 11gR2 Database with RMAN
RAC and single instance RMAN backup is similar, but… Backup of a 11gR2 RAC database is similar to that of a single instance 11gR2 database. There is an important difference though… assuming that you – like me – schedule a non-RAC backup by cron on the database node, using local […]
Re-ordering without procedural code or using a sequence
Question from one of my customers: “Please help me find an update statement that’s able to re-order a primary key after a delete.. and by the way.. you are not allowed to use PL/SQL, triggers or a sequence, because we cannot change anything in the app schema”. Updating a PK […]
Compare tables with the minus operator, even if they contain clobs and/or blobs.
I wanted to compare the content of two tables with identical layout quickly with the minus set operator, but ran into a couple of errors, caused by clob and blob colums. As I found out, set operators ( union, minus, intersect ) in combination with clobs or blobs, are not […]
Rman 11gR2 changes tag labeling of the inc backup in incrementally updated backups
In (rman) incrementally updated backups, only incremental backups are done after the first full backup to the Fast Recovery Area. From 11gR2 on the incremental backup pieces will get the same tag as the datafile copies, and that’s actually different behaviour from pre- 11gR2 versions of the database. I stumbled […]
How to switch datafiles to FRA and back again
If you, like me, like to use (rman) incrementally updated backups, a copy of all datafiles will be present in the Fast Recovery Area. That becomes quite handy if you are in sudden need of extra disk space for your database, and the FRA still has ample space left. By […]
DBPITR with a rman archival backup
One of our customers had this request: Can you make a backup of my development database that I can use to restore ( the same database ) to a couple of months back in time? This customer uses Standard Edition [SE] 11gR1 database on Windows, and has a backup regime where […]
I don't like a reclaimable database disk copy
As of Oracle 10g it’s possible to recover (or wind forward) a RMAN disk copy of the database to a specific time in the past. This type of backup is quite suitable for big databases (> 100GB), because you have to perform a time consuming full backup only once. All […]
Oracle database directory listing with ls function
Say, you are in need of a directory listing from within the Oracle database similar to the one generated with Unix command ls. Your database doesn’t contain a JVM, so the use of some JAVA is out of the question. Well, starting from DB Release 10g there is the possibility […]