Search

Top 60 Oracle Blogs

Recent comments

TOI

How to safeguard against malicious developers in #Oracle 12c

AUTHID_CURRENT_USER can be misused by developers. The new object privilege INHERIT PRIVILEGES ON was introduced in 12c to prevent that. I start with a demo to explain the problem that the new features solves. The playground:

How to do PDB PITR in #Oracle 12c

A logical error happened in one Pluggable Database. A PDB Point-In-Time-Recovery rewinds it while the others remain available and stay as they are.

Logical error in PDB2

Logical error in PDB2

How to reinstate the old Primary as a Standby after Failover in #Oracle

You have done a failover to your Standby database so it becomes the new Primary. It may be possible to convert the old Primary into a Standby database now instead of having to do a time consuming duplicate again. The old Primary must have been running in flashback mode before the failover. The playground:

How to use DURATION with RMAN backups in #Oracle

The DURATION clause enables you to reduce the performance impact of RMAN backups respectively it sets a certain time limit for the backup.

Let’s suppose your RMAN backup takes one hour now and you take it online while end users work with the database. This reduces the performance impact of the online backup by half approximately:

RMAN> backup duration 02:00 minimize load database;

The first two digits are hours, the second two digits are minutes. Above command tells RMAN to spend 02 hours and 00 minutes with the backup that takes normally one hour. That way, RMAN gets throttled down, causing roughly half the load on the system than otherwise.

How to resolve media failures with the Recovery Advisor in #Oracle

Error messages are showing up because files have been damaged? Database Recovery Advisor to the rescue!

How to fix a problem with the spfile in #Oracle

An invalid entry in the spfile may prevent the instance from starting up:

SQL> alter system set sga_target=500m scope=spfile;

System altered.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORA-00821: Specified value of sga_target 512M is too small, needs to be at least 1392M

The instance doesn’t come up! This is easy to fix without having to restore the spfile from backup:

SQL> create pfile='/home/oracle/init.ora' from spfile;

File created.

SQL> host vi /home/oracle/init.ora

Now correct the value in the text file. I just removed the sga_target parameter from it here. Then

Index Competition in #Oracle 12c

Suppose you want to find out which type of index is best for performance with your workload. Why not set up a competition and let the optimizer decide? The playground:

Data Redaction and Data Pump in #Oracle 12c

What happens upon Data Pump Export if tables are being exported that have a Data Redaction Policy? I got that question several times in class, which is why I put the answer here , so I can refer to it subsequently.  Might also be of interest to the Oracle Community:-)

Create a SQL Profile to let the Optimizer ignore hints in #Oracle

Something I presented recently during an Oracle Database 12c Performance Management and Tuning class. Hints are a double-edged sword; they may do more harm than good. What if  hinted SQL comes from an application that you as the DBA in charge can’t modify? You can tell the Optimizer to ignore that nasty hint.

One method is to use alter session set “_optimizer_ignore_hints”=true; This will make the optimizer ignore all hints during that session  – also the useful ones, so maybe that is not desirable. The method I show here works on the statement level. The playground:

Another reason why you should use the Data Guard Broker for your #Oracle Standby

The Data Guard Broker is recommended for various reasons, this one is less obvious: It prevents a Split-Brain problem that may otherwise occur in certain situations. Let me show you: