In my current work environment, I often have to switch from one OEM console to another. In order to quickly identify which console I’m going to connect to, and to make sure I don’t make a mistake by confusing production and non-production, I discovered a trick that allows me to add a banner on the login page.Continue reading “Add a banner on Oracle Enterprise Manager login page.”
As a DBA you sometimes have to take time to look for tricks to accomplish tasks that seem simple at first glance. A few days ago, I was asked to identify tables that had sequences prefixed by the schema name as a default value in the columns. You may think: it’s an easy task, just filter on the “data_default” column of the “dba_tab_columns” view, but unfortunately it’s not that simple using SQLPlus. The “data_default” column is in “long” datatype, so you cannot include it directly in the “where” clause of a query.Continue reading “Simulate a where clause on a “long” type column from SQLPlus”
Today, I just want to share with you a little script that I use, which allows me to quickly check if my database services are running on the configured “Preferred instances” in RAC environments.Continue reading “Do my database services run on the preferred instance (s)?”
A few days ago, I was asked to find a way to automate the addition of newly created Linux virtual machines and databases in our EM13c console. Of course, no action should be performed via the EM web interface!
For the first part (installing the EM13c agent on newly created virtual machines), it’s quite simple, because Oracle gives you many methods (for example, “agent pull” method), in order to carry out this task. But the second part seemed more delicate according to the documentation and my constraints: everything must be done on the virtual machine. I am not allowed to connect to the EM server using SSH.
Fortunately, there is a simple solution that only requires a few extra lines in your shell script!
“Sometimes shit happens”. That’s what I thought when I realized that I had lost all the control files of one of my test databases. Of course, I had no backup because “it’s just a test database”…
Fortunately, I only lost my control files but no datafiles or redo log files. This is a very important point because if you are in this situation, all is not lost!
So today, I will show you how you can recover your Oracle database after the loss of all control files.
Today, I’m going to share with you a simple SQL query that will allow you to quickly extract useful information from your OEM repository about your databases:
- Database name
- Database version
- Server name
- Server OS
- OS version
The query can be very useful if you need to fill in a new CMDB for example.
If you are familiar with database links on Oracle database, you may have already encountered the error “ORA-12154: TNS:could not resolve the connect identifier specified“. That is a common error which can be caused by a lot of factors. Today, I’m going to focus on the importance of environment variables to avoid this type of error message (particularly the “TNS_ADMIN” environment variable).
Yesterday, I was working on a simple Golden Gate replication between two Oracle databases. Everything worked as expected, the source and target schemas were synchronized and the replication lag was about 0 seconds.
Unfortunately; after a couple of hours, I notice a 2 hours lag on the replicat…
After some research on GoldenGate and with some queries on v$session and v$sql, I realize that the lag is due to a 100 million rows table on which there is no primary key or unique key defined.
Important point: I was not allowed to add constraints on the source database!
Yes, I know: many scripts are available to get tablespaces usage from your Oracle database.
The one I’m going to give you is not magic, but it can handle some special situations and it can be easily integrate into a “custom metric” in Oracle Enterprise Manager.
The script runs on Oracle 10g and onward.Continue reading “Tablespace usage on Oracle Database”
As a DBA (or developer), you often need to make connection to your Oracle database from shell scripts.
That’s not a big deal if you connect locally to the database through the Linux/Unix account that owns the instance with “/ a sysdba”. But what if you need to connect to a remote database or a local database with a specific user?
Here are the solutions I have most often seen:
- Clear password in the shell script
- Clear password in a parameter file loaded from the shell script
- Environnement variable with clear password
- Function which decrypt a encrypted password, stored in a parameter file loaded from the shell script (more secure but easily breakable)
As you might have guessed, none of these solutions is secure!
Fortunately, Oracle provides us a free feature that allows you to get rid of clear passwords from your shell scripts:Secure external password store (also known as “SEPS” or “Oracle Wallets”).
The only prerequisite is to use Oracle 10gR2 or onward.