Best Practices for Statistics Gathering on Oracle 11g

Some people look at Statistics Gathering as taking vitamins: nice to have. And it’s wrong.

You shouldn’t base your tuning strategy on dynamic sampling alone, or luck. Some people freak out because once they generated stats for some system and it all went south. That’s the same as saying that because cars crash and people get killed we shouldn’t drive at all.

You have to use stats. Period.

So my small contribution to make your life easier is to give 5 tips if you are using Oracle 11g:

1) Run all queries against empty tables to populate column usage (histograms will be gathered based on this column usage).

2) Run stats after data has been loaded but before indexes are created (the creation of the index gathers stats on them automatically).

3) Find (business) correlations between columns and inform the optimizer about these correlations by the means of creating Column Groups. Also use this technique instead of function-based indexes.

4) Always gather stats with AUTO_SAMPLE_SIZE so the new stats gathering algorithm of Oracle 11g can kick in.

5) If you use partition tables you *must* use synopsis so the global stats can be derived much much much faster in an incremental fashion.

I guess 1, 2 and 4 don’t offer any doubts.

Regarding 3 and 5 here’s the usual techie yada yada:

3) Column Groups are created by the means of Extended Optimizer Statistics a new procedure of the DBMS_STATS package: CREATE_EXTENDED_STATS.

See an example in this very good article.

5) Synopsis are created when you enable incremental stats at the table or system level:

table level: SQL> exec DBMS_STATS.SET_TABLE_PREFS(‘SCOTT’,'TABLE_XYZ’,'INCREMENTAL”TRUE’);

system level: SQL> exec DBMS_STATS.SET_GLOBAL_PREFS(‘INCREMENTAL”TRUE’);

This will avoid rescaning partitions that haven’t changed since the last stats generation, making it faster.

So remember: ASICS it’s a matter of survival, not an option.

LMC.

Extended Optimizer Statistics

Exploring the revolutionary DBFS

Finally a blog post worth sharing with you all about DBFS.

This is from Ronny Egners and it’s very good and comprehensive article about his experiences with DBFS. It even has an URL for the Oracle official documentation.

Enjoy:

Ronny Egners’s Blog post about DBFS

Reversing the effect of a TRUNCATE TABLE in Oracle 11gR2

Today I’ll share with you a way to reverse the effect of a TRUNCATE TABLE without any complex recovery procedures. I’m assuming the schema used has DBA privileges, just to avoid switching from user to user during the example.
During the example I’ll also show you a new 11gR2 feature that enables you to do DDL on tables that have UNDO data going to a Flashback Data Archive (FDA).

Here’s the outline:

1) Create a dedicated FDA
2) Create a trial table having it’s undo data going to the new FDA
3) Insert data
4) Take note of both SCN and SYSDATE
5) Truncate the trial table (and see that it takes too long! It’s due to the new 11gR2 algorithm that sucks)
6) Try to FLASHBACK the table without success
7) Using FLASHBACK QUERY with success and reverse the effect of the TRUNCATE: no sweat!

Here’s the code:

SQL> show user
USER is "LMC"

SQL> select * from session_roles;

ROLE
------------------------------
CONNECT
RESOURCE
DBA
(...)

SQL> CREATE FLASHBACK ARCHIVE ando TABLESPACE users QUOTA 500m RETENTION 1 year;

Flashback archive created.

SQL> create table mytab (n number, x varchar2(90), d date);

Table created.

SQL> alter table mytab flashback archive ando;

Table altered.

SQL> alter table mytab flashback archive enable row movement

Table altered.

SQL> insert into mytab values (1,'Monsters of Folk',sysdate);

1 row created.

SQL> insert into mytab values (2,'The Frames',sysdate-1/24);

1 row created.

SQL> commit;

Commit complete.

SQL> select CURRENT_SCN from v$database;

CURRENT_SCN
-----------
    1614140

SQL> select * from mytab;

         N X                                   D
---------- ----------------------------------- ---------
         1 Monsters of Folk                    19-OCT-09
         2 The Frames                          19-OCT-09
         3 Boys Like Girls                     18-OCT-09

SQL> select to_char(sysdate,'yyyymmdd hh24:mi:ss') from dual;

TO_CHAR(SYSDATE,'
-----------------
20091019 19:11:47

SQL> set timing on
SQL> truncate table mytab;

Table truncated.

Elapsed: 00:00:17.03

Seventeen Seconds to truncate a kb table? Looks like that new 11gR2 algorithm that enables DDL support in Flashback Data Archives is a little bit crapy…

SQL> flashback table mytab to scn 1615151;
flashback table mytab to scn 1615151
                *
ERROR at line 1:
ORA-01466: unable to read data - table definition has changed

I was expecting that, no problem. You can’t flashback table through DDL
Now let’s try going to the past just to fetch the info…

SQL> select * from mytab as of timestamp TO_TIMESTAMP('20091019 19:11:47','yyyymmdd hh24:mi:ss');

         N X                                   D
---------- ----------------------------------- ---------
         1 Monsters of Folk                    19-OCT-09
         2 The Frames                          19-OCT-09
         3 Boys Like Girls                     18-OCT-09

SQL> insert into mytab
  2  select * from mytab as of timestamp TO_TIMESTAMP('20091019 19:11:47','yyyymmdd hh24:mi:ss');

3 rows created.

SQL> commit;

Commit complete.

Elapsed: 00:00:00.00
SQL> select * from mytab;

         N X                                   D
---------- ----------------------------------- ---------
         1 Monsters of Folk                    19-OCT-09
         2 The Frames                          19-OCT-09
         3 Boys Like Girls                     18-OCT-09

SQL> — My data is back!!!

Advanced Corruption Resolution with RMAN

When Oracle Database 11g came with automatic recovering procedures, I was somewhat suspicious, but since all the exercises in the Oracle University labs went well in my classes, my perception of  these procedures was somewhat neutral.

The case I present to you today will show that these procedures are not so automatic, and above all, not so flexible that a DBA can use them in any panic situation.

We will start by asking RMAN if we have any problems:

RMAN> advise failure all;

no failures found that match specification

The database we will use for this is the standard one, with the sample schemas tablespace called EXAMPLE, with one datafile:

$ ll
(...)
-rw-r-----  1 oracle oinstall 104865792 example01.dbf
(...)

The datafile has aprox 100 MBytes, but we will mess with the “example01.dbf” datafile where the HR schema has its objects:

$ dd if=/dev/zero of=example01.dbf bs=8192 count=1024
1024+0 records in
1024+0 records out

So now the file has 8 MBytes instead of 100 Mbytes…

$ ll example01.dbf
-rw-r-----  1 oracle oinstall 8388608 example01.dbf

Will the HR schema be able to fetch its data? Take a look:

SQL> select count(*) from hr.employees;
select count(*) from hr.employees
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 5, block # 211)
ORA-01110: data file 5: '/u01/app/oracle/oradata/orcl/example01.dbf'

This is so awsome! Having corruption errors hit your face and not sh*ting you pants is such a relief.

Going back to the RMAN console, let’s try to get advice again:

RMAN> advise failure all;

using target database control file instead of recovery catalog
List of Database Failures
=========================

Failure ID Priority Status    Time Detected Summary
---------- -------- --------- ------------- -------
82         HIGH     OPEN      08-OCT-09     One or more non-system datafiles are corrupt

analyzing automatic repair options; this may take some time
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=134 device type=DISK
analyzing automatic repair options complete

Mandatory Manual Actions
========================
no manual actions available

Optional Manual Actions
=======================
no manual actions available

Automated Repair Options
========================
Option Repair Description
------ ------------------
1      Restore and recover datafile 5
Strategy: The repair includes complete media recovery with no data loss
Repair script: /u01/app/oracle/diag/rdbms/orcl/orcl/hm/reco_105909009.hm

This is the whole output of the “ADVISE” RMAN command, which tells us that there is a corrupt datafile (doesn’t tell you which).

There are no Manual Actions you need to take, only automated. But the only automated task can be manually triggered by executing a script. So let’s do it:

RMAN> @/u01/app/oracle/diag/rdbms/orcl/orcl/hm/reco_105909009.hm

RMAN>    # restore and recover datafile
2>    sql 'alter database datafile 5 offline';
sql statement: alter database datafile 5 offline

RMAN>    restore datafile 5;
Starting restore at 08-OCT-09
using channel ORA_DISK_1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 10/08/2009 11:58:32
ORA-01135: file 5 accessed for DML/query is offline
ORA-01110: data file 5: '/u01/app/oracle/oradata/orcl/example01.dbf'
RMAN-06010: error while looking up datafile: 5

RMAN>    recover datafile 5;
Starting recover at 08-OCT-09
using channel ORA_DISK_1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 10/08/2009 11:58:32
ORA-01135: file 5 accessed for DML/query is offline
ORA-01110: data file 5: '/u01/app/oracle/oradata/orcl/example01.dbf'
RMAN-06010: error while looking up datafile: 5

RMAN>    sql 'alter database datafile 5 online';
sql statement: alter database datafile 5 online
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of sql command on default channel at 10/08/2009 11:58:32
RMAN-11003: failure during parse/execution of SQL statement: alter database datafile 5 online
ORA-01122: database file 5 failed verification check
ORA-01110: data file 5: '/u01/app/oracle/oradata/orcl/example01.dbf'
ORA-01210: data file header is media corrupt

RMAN> **end-of-file**

Looks like the recovery script didn’t do no good… is there any other “automatic” way of doing it? Yes, if you use the REPAIR command:

RMAN> repair failure;

Strategy: The repair includes complete media recovery with no data loss
Repair script: /u01/app/oracle/diag/rdbms/orcl/orcl/hm/reco_105909009.hm

contents of repair script:
# restore and recover datafile
sql 'alter database datafile 5 offline';
restore datafile 5;
recover datafile 5;
sql 'alter database datafile 5 online';

Do you really want to execute the above repair (enter YES or NO)? yes
executing repair script

sql statement: alter database datafile 5 offline

Starting restore at 08-OCT-09
using channel ORA_DISK_1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of repair command at 10/08/2009 13:27:41
RMAN-03015: error occurred in stored script Repair Script
ORA-01135: file 5 accessed for DML/query is offline
ORA-01110: data file 5: '/u01/app/oracle/oradata/orcl/example01.dbf'
RMAN-06010: error while looking up datafile: 5

The REPAIR FAILURE command does exactly the same. Let’s try to do it manually:

RMAN> sql 'alter database datafile 5 offline';

sql statement: alter database datafile 5 offline

The first command went ok. Now the restore:

RMAN> restore datafile 5;

Starting restore at 08-OCT-09
using channel ORA_DISK_1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 10/08/2009 13:30:01
ORA-01135: file 5 accessed for DML/query is offline
ORA-01110: data file 5: '/u01/app/oracle/oradata/orcl/example01.dbf'
RMAN-06010: error while looking up datafile: 5

So the error is in the restore…

Just a wild guess: what if I rename the existing, yet corrupted datafile?

$ mv example01.dbf example01.dbf.old

And try again the restore…

RMAN> restore datafile 5;

Starting restore at 08-OCT-09
using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00005 to /u01/app/oracle/oradata/orcl/example01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/fra/ORCL/backupset/2009_10_08/o1_mf_nnndf_TAG20091008T102933_5dvd7xrb_.bkp
channel ORA_DISK_1: piece handle=/u01/app/oracle/fra/ORCL/backupset/2009_10_08/o1_mf_nnndf_TAG20091008T102933_5dvd7xrb_.bkp

tag=TAG20091008T102933
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07
Finished restore at 08-OCT-09

And now do the rest of the process manually:

RMAN> recover datafile 5;

Starting recover at 08-OCT-09
using channel ORA_DISK_1

starting media recovery
media recovery complete, elapsed time: 00:00:00

Finished recover at 08-OCT-09

RMAN> sql 'alter database datafile 5 online';

sql statement: alter database datafile 5 online

And now we can check if the HR schema can select from its own tables:

SQL> select count(*) from hr.employees;

COUNT(*)
----------
107

Everything now is tip-top! Problem solved.

So why did the automatic process failed?

Because what we’ve had wasn’t either a missing file nor corruption. We had a “fake” file, which led us to a case of “fake” corruption, though the message pointed to that direction, it wasn’t corruption. The automatic process didn’t even built a script to handle corruption, otherwise it would use RMAN commands like “RECOVER CORRUPTION LIST;” or “RECOVER DATAFILE 5 BLOCK 211;”.

If we had selected from the Data Dictionary we would see no corruption at all:

SQL> select * from V$DATABASE_BLOCK_CORRUPTION;

no rows selected

So in these cases, the file you have is no good and you should rename or even delete it, and THEN try the automatic process of restoring and recovering it.

Bottom-line: The guys at Redwood Shores should take this in consideration when making “almost” automatic recovering processes, because when the DBA has the knive on the neck, he should be able to trust these “almost” automatic processes, and until now he can’t.

LMC.

211

Duplicating a database has never been so easy! (11gR2 new feature)

When the DUPLICATE RMAN command was introduced back in 10g, one of the things that was freaking people out was that although you were able to make it from a backup, you had to have connection established to the main database (I won’t call it Primary so you don’t get confused with Data Guard concepts).

On Oracle 11g Release 1, the new feature went a step backwards in my opinion, launching a feature that would enable you to duplicate a runing database without the need of a backup. What was the matter with these people!? All we wanted was the exact opposite!!

So now in Oracle 11g Release 2 you can duplicate a database from a backup alone (without the need to connect to the source database), and even better: you can choose which backup you would want to duplicate from!

To explain you guys how this is done, I’ve decided to duplicate my RAC Database, from the most recent backupset, onto the same machine as a “Standalone” database. Pretty cool. I’ve managed to un-RAC it in minutes!

Take a look:

We are going to use Enterprise Manager, but this whole process can be done through RMAN and a coupple of other manual tweaks. You choose “CLONE DATABASE” from the “Data Movement” tab and the process starts.

In this screen we selected “Existing Database Backup”, from a backup taken previously. Fill in the host credentials so the process can invoke the RMAN shell:

This is awsome! You might want to duplicate or clone from the latest backup taken or from a point somewhere in the past through a time reference or a SCN (System Change Number) reference.

You can choose the name of your duplicated database, whether that would go to a FS or ASM, and what will be the degree of parallelism, that will mean in practical terms, that RMAN will allocate more channels for the operation. If your fetching the backup from tape, you might want to raise the level to a comfortable level between getting performancewise stuck and taking too long.

We had problems using AMM’s new 11g feature, and so we decided to stick with the ASMM way:

Attention! You have to choose a different TNS Listener port if you are going to duplicate in the same host, or into a different one that has already a TNS Listener defined. In other words, there can’t be no colisions in the TNS Port you choose, otherwise you will be flagged:

If on the same server you can choose to put the files on the same place or choose another location:

Now it’s always better than “Later”

A brief review of you are about to launch. In our case it’s a simple database clone in the same server. The proceding for a different would be the same, except that in the case of tape backup the destination host would have to see the tape robot or drives. In case of disk backup, the destination host would have to have the backup copied over there (don’t tie yourself up with Unix NFS or Windows Network Drives if you can avoid it).

Well at least the sumbition went well… let’s click on the job’s name and make a regular refresh to the page (or just check V$RMAN_OUTPUT):

Step 1 succeeded! Yes!! Let’s wait calmly because this is too much enthusiasm…

After 857 seconds the process ended just fine. Now let’s check out new database:

1) You have your “/etc/oratab” updated

2) You have the user HR unlocked and with the password HR, which was the change we’ve made on the source database so we could test it out here

Cloning and duplicating databases was never so easy.

LMC.

Putting ACFS to work: how to create a mount point in the new Oracle File System

This post is a translation of another one that I’ve originally wrote in portuguese. I’ve decided to share with the international Oracle community through my weblog.

ACFS stands for ASM Cluster File System. ACFS is a new file system created by Oracle that is created on top of an ASM Disk Group. The architecture is very simple and these mount points can contain both general purpose files (except  db files) or Oracle Home binaries, which is very good for sharing binaries.

There are three ways to setup ACFS:

- Web (with Enterprise Manager)

- With  ASMCA (ASM Configuration Assistant) a graphic tool

- or through command line statements

We will show you examples using ASMCA because it’s nicer

Here’s the step-by-step:

1 – We had two 1GB LUNs/Disks for this example with which two raw devices were created:

[oracle@node1 ~]$ cat /etc/rc.local

#!/bin/sh

#

# This script will be executed *after* all the other init scripts.

# You can put your own initialization stuff in here if you don’t

# want to do the full Sys V style init stuff.

touch /var/lock/subsys/local

raw /dev/raw/raw1 /dev/sdb1

raw /dev/raw/raw2 /dev/sdc1

chmod 666 /dev/raw/raw1

chmod 666 /dev/raw/raw2

chown oracle:oinstall /dev/raw/raw1

chown oracle:oinstall /dev/raw/raw2

2 – Now it’s time to call ASMCA:

[oracle@node1 ~]$ . oraenv

ORACLE_SID = [+ASM] ?

ORACLE_HOME = [/home/oracle] ? /u01/11.2.0/grid

The Oracle base for ORACLE_HOME=/u01/11.2.0/grid is /u01/oracle

[oracle@node1 ~]$ asmca &

3 – Create one disk group (DG) with those two raw devices:

4 – We then create a ASM volume on top of this DG, naming it “volumezinho” (we show here several ways of doing so):

There are a few advanced options in ASMCA when you create a volume:

5 – Create a directoy on the ext3 Linux file system so when can mount there the device created by “volumezinho”

[root@node1 ~]# mkdir /u02/oradata/acfs
[root@node1 ~]# chown oracle:oinstall /u02/oradata/acfs

6 – Now let’s create the ACFS mount point. We can choose between one of these two options:

But still you have to mount it manually:

Et voilá!

Now we can go to the directory mounted on the ASM volume formated as ACFS, and create some bogus text file:



[oracle@node1 ~]$ df -m
Filesystem           1M-blocks      Used Available Use% Mounted on
/dev/mapper/VolGroup00-LogVol00 47551     16389     28708  37% /
/dev/sda1                   99        11        83  12% /boot
tmpfs                     1014       662       352  66% /dev/shm
/dev/sdd1                  512       351       162  69% /u02/oradata/cluster-lmc
/dev/asm/volumezinho-486   512       101       412  20% /u02/oradata/acfs/data_volumezinho

[oracle@node1 ~]$ cd /u02/oradata/acfs/data_volumezinho
[oracle@node1 data_volumezinho]$ ll
total 68
-rw-r--r-- 1 oracle oinstall    89 Sep 24 14:33 ficheiro.txt
drwx------ 2 root   root     65536 Sep 24 14:20 lost+found

[oracle@node1 data_volumezinho]$ cat ficheiro.txt


Primeiro ficheiro de texto criado num volume ASM no qual foi montado um ACFS!!!


Yupii!!


[oracle@node1 data_volumezinho]$



Technology is an amazing thing. So many twists and turns to end up in the same place ...

SSH User Equivalence in 11gR2 Real Application Clusters Clusterware installation

There has been a while since a new Oracle Database release hasn’t hit me with so many key new features.
And for those who had suffer long hours of horror movie-like feelings configuring ssh user equivalence, knowing that Oracle Database 11g Release 2 does this by itself based on the /etc/hosts alone, it’s like a blender that fetches the fruit from the fridge by itself and puts back the juice in the fridge so you can open the door and selfserve from that delicious natural fruit juice. It’s magic, it’s the impossible made possible, it’s … let’s see some images because I’m running out of metaphores:

When I saw the “SSH Connectivity” button … my heart froze

I took a deep breath and clicked. All of the sudden a new part in the installation window showed me a new and wonderfull world of self-configurable SSH user equivalence. I’ve pushed the “TEST” button so I could see the more than expected error because I’ve done nothing so far to configure it, and so I got the following message:

This is more than expected, not because I was installing a one-node clusterware, but because I haven’t done nothing yet in this particular issue of SSH Connectivity. And to tell you the truth I wasn’t in the mood to do it, so I decided to take another deep (deeper) breath and click on the “SETUP” button, whishing the impossible and knowing that another complex form might pop-up for me to fill. But my 14-year Oracle experience didn’t prepared me for what was about to hit me: total and complete self configuring SSH Connectivity. Take a look:

I’ve almost “jizzed in my pants”

Disclaimer from the docs:

“To enable the script to run, you must remove stty commands from the profiles of any Oracle software installation owners, and remove other security measures that are triggered during a login, and that generate messages to the terminal. These messages, mail checks, and other displays prevent Oracle software installation owners from using the SSH configuration script that is built into the Oracle Universal Installer. If they are not disabled, then SSH must be configured manually before an installation can be run.”

Recovering a Dropped Tablespace with TSPITR in 11gR2

This is heavy dutty stuff. The scenario here is when you drop a tablespace and want it back! We’re testing out the 11gR2 new feature that allows us to recover a dropped tablespace from a previous backup.

In order not to damage any of the other existing tablespaces, we’ll create a guinea pig tablespace called LMC:

SQL> create tablespace lmc datafile '/u02/oradata/orcl/lmc01.dbf' size 50m;

Tablespace created.

We can’t use the SYS schema to arbor an example table because objects belonging to SYS are not considered self-contained, and in the end RMAN would throw these errors:

RMAN-06960:    EXPDP> ORA-39123: Data Pump transportable tablespace job aborted
ORA-39187: The transportable set is not self-contained, violation list is
ORA-39917: SYS owned object TABELA_GRANDE in tablespace LMC not allowed in pluggable set

So, let’s not make the common mistake of using the SYS schema for testing.
Let us create this trial table on a new fresh schema:

SQL> grant connect, resource to fresh identified by cool;

Grant succeeded.

Now we create a 30MB empty table inside that tablespace, belonging to the “fresh” schema …

SQL>
create table fresh.tabela_grande (coluna_grande varchar2(4000), n number)
tablespace lmc
storage (initial 30m);

Table created.

… and we load random data inside:

SQL>
begin
  for i in 1..4000 loop
    insert into fresh.tabela_grande values (dbms_random.string('a',4000),i);
  end loop;
end;
/

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.

In order to test TSPITR we need to have a full database backup, along with our LMC tablespace. I’m assuming that the full database backup had been done somewhere in a near past moment (we did it before creating the LMC tablespace).
But before that, controlfile autobackup needs to be enabled:

$ rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Thu Sep 17 16:03:48 2009
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORCL (DBID=1225517806)

RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;

new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters are successfully stored

RMAN> backup tablespace lmc;

Starting backup at 17-SEP-09
(...)
Finished backup at 17-SEP-09

Now we drop the tablespace, but before that we register the time so we can do a real “point-in-time” recovery of the dropped tablespace:

SQL> !date
Thu Sep 17 18:52:24 WEST 2009

SQL> drop tablespace lmc including contents;

Tablespace dropped.

And now let’s hope that 11gR2′s TSPITR can bring us our LMC tablespace from the “dead”!!

RMAN> RECOVER TABLESPACE 'LMC'
   UNTIL TIME "to_date('September 17 2009, 18:52:24','Month dd yyyy, HH24:MI:SS')"
   AUXILIARY DESTINATION '/u01/aux';

This last command will last a bit long and throw sooooo many output at you that you shouldn’t worry about.

If your process ends with these errors:

RMAN-06961:    IMPDP> ORA-39123: Data Pump transportable tablespace job aborted
ORA-01565: error in identifying file '+DATA/orcl/datafile/lmc.256.697830651'
ORA-17503: ksfdopn:2 Failed to open file +DATA/orcl/datafile/lmc.256.697830651
ORA-15012: ASM file '+DATA/orcl/datafile/lmc.256.697830651' does not exist

It’s because you used the clause “AND DATAFILES” in the DROP TABLESPACE statement. If you did you should had restored the datafile first, or not had used the clause at all, like we did.

In the end if all is fine let’s see it from SQL point of view:

SQL> select count(*) from fresh.tabela_grande;
select count(*) from fresh.tabela_grande
*
ERROR at line 1:
ORA-00376: file 6 cannot be read at this time
ORA-01110: data file 6: '/u02/oradata/orcl/lmc01.dbf'

SQL> alter tablespace lmc online;

Tablespace altered.

SQL> select count(*) from fresh.tabela_grande;

COUNT(*)
----------
4000

Bingo! The table is back!

Bottom line: In 11gR2 if you drop a tablespace and want it back you should have the datafiles somehow, either by a operating system backup or by restoring them via an image copy. To avoid this hassle, don’t use the “AND DATAFILES” clause of DROP TABLESPACE statement, backed them up before dropping (unless they’re empty), and store the datafiles for a reasonable period. If in ASM, make an image copy via RMAN to a file system.

Why does ASM Configuration Assistant can’t start my ASM instance?

[oracle@centos5 ~]$ asmca -silent -startup -configureParameter -param coiso=valor -sysAsmPassword Oracle.123

ASM failed to start. Check /oracle/cfgtoollogs/asmca/asmca-0909103PM0424.log for details.

[oracle@centos5 ~]$ asmca -silent -startup -configureParameter -param coiso=valor -sysAsmPassword Oracle.123

ASM failed to start. Check /oracle/cfgtoollogs/asmca/asmca-0909103PM0424.log for details.

And the log says:

(..) [UsmcaLogger.logException:171]  SEVERE:method oracle.sysman.assistants.usmca.backend.USMInstance:checkAndStartupInstance
(..) [UsmcaLogger.logException:172]  ORA-01034: ORACLE not available
(..) [UsmcaLogger.logException:173]  oracle.sysman.assistants.util.sqlEngine.SQLFatalErrorException: ORA-01034: ORACLE not available

Why??? And it goes on:

oracle.sysman.assistants.util.sqlEngine.SQLEngine.executeImpl(SQLEngine.java:1650)
oracle.sysman.assistants.util.sqlEngine.SQLEngine.executeQuery(SQLEngine.java:823)
oracle.sysman.assistants.usmca.backend.USMInstance.checkAndStartupInstance(USMInstance.java:602)
oracle.sysman.assistants.usmca.backend.USMInstance.doPreliminaryChecks(USMInstance.java:2597)
oracle.sysman.assistants.usmca.backend.USMInstance.configureASMParameters(USMInstance.java:1689)
oracle.sysman.assistants.usmca.service.UsmcaService.configureASMParameters(UsmcaService.java:252)
oracle.sysman.assistants.usmca.model.UsmcaModel.performConfigureASMParameters(UsmcaModel.java:746)
oracle.sysman.assistants.usmca.model.UsmcaModel.performOperation(UsmcaModel.java:512)
oracle.sysman.assistants.usmca.Usmca.execute(Usmca.java:156)
oracle.sysman.assistants.usmca.Usmca.main(Usmca.java:333)
(..) [UsmcaLogger.logExit:122]  Exiting oracle.sysman.assistants.usmca.backend.USMInstance Method : checkAndStartupInstance
(..) [UsmcaLogger.logInfo:141]  ASM failed to start. Check /oracle/cfgtoollogs/asmca/asmca-0909103PM0424.log for details.
(..) [UsmcaLogger.logExit:122]  Exiting oracle.sysman.assistants.usmca.model.UsmcaModel Method : performOperation

Why does the script tells me to read … the script itself?

Why do I feel like a guinea-pig?

I’ve logged and SR on Metalink.

Oracle 11gR2 feature of the Day!

I did this on a CentOS 5 box with Oracle 11.2.0.1 and it was to show the latest new hint that you can use when transporting lines from one table to another, and there are colisions due to unique/primary keys.

The example is hilarious!

SQL> create table exemplo (
 2  col1 number primary key,
 3  col2 varchar2(100));

Table created.

SQL> insert into exemplo values (1,'Primeira Linha');
1 row created.

SQL> insert into exemplo values (2,'Segunda Linha');
1 row created.

SQL> commit;
Commit complete.

SQL> — NOW HERE IT COMES THE 11gR2 TWEAK
SQL> — Create a new table based on EXEMPLO

SQL> create table exemplo2 as select * from exemplo;
Table created.

SQL> — Insert an extra line in this new table

SQL> insert into exemplo2 values (3,'Terceira Linha');
1 row created.

SQL> commit;
Commit complete.

SQL> — And NOW if we insert all the lines from the 2nd table into the first

SQL> — The line 1 and 2 will colide

SQL> insert into exemplo select * from exemplo2;
insert into exemplo select * from exemplo2
*
ERROR at line 1:
ORA-00001: unique constraint (LMC.SYS_C0014102) violated

SQL> — Buahhhhh But I wanted Oracle to be smart without me having the need to learn PL/SQL…
SQL> — What? It is possible now?
SQL> — How?
SQL> — Like this:

SQL> insert --+IGNORE_ROW_ON_DUPKEY_INDEX
 2  into exemplo select * from exemplo2;
insert --+IGNORE_ROW_ON_DUPKEY_INDEX
*
ERROR at line 1:
ORA-00001: unique constraint (LMC.SYS_C0014102) violated

SQL> — What did I did wrong?
SQL> — See …

SQL> insert --+IGNORE_ROW_ON_DUPKEY_INDEX(exemplo(col1))
 2  into exemplo select * from exemplo2;
1 row created.

SQL> commit;
Commit complete.

SQL> select * from exemplo;

 COL1 COL2
---------- ------------------------------
 1 Primeira Linha
 2 Segunda Linha
 3 Terceira Linha

SQL> — The 3rd line just went in and the colisions ignored!!
SQL> — Damn I’m good!

Turn off CASE Sensitiveness in passwords in Oracle11g

In case you’re wandering looking for a way to turn off that sexy feature of Oracle 11g that puts your passwords sensitive to case, it’s very simple, because it’s a spfile parameter:

SQL> show parameter case

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
sec_case_sensitive_logon             boolean     TRUE

So if you want to turn off the case sensitiveness just type:

SQL> alter system set sec_case_sensitive_logon = false scope=both;

Attention: If you create a mixed case password during the “off” period, you must remember that when you decide to turn it back on.

I/O Calibration in Oracle 11g

Very cool feature. For those who knew or used ORION probably it’s no big surprise, but still it’s very usefull.

You need to

1) alter one parameter: SQL>alter system set filesystemio_options=ASYNCH scope=spfile;  (needs a DB bounce)
2) Check another parameter: SQL> show parameter disk_asynch_io (if it’s TRUE, it’s OK)
3) Decide on the latency (in miliseconds) you will tolerate has the maximum for your storage system
4) Know how many physical disks you have
5) If using Linux and if  “cat /proc/sys/fs/aio-nr” is too close to cat /proc/sys/fs/aio-max-nr” edit (as root) the “/etc/sysctl.conf” file and increase the max parameter:

# vi/etc/sysctl.conf

add the following line: fs.aio-max-nr = 131072

This should be suficient for most systems, but if the value given by cat /proc/sys/fs/aio-nr” is too close to 131072, then you can increase the value of “fs.aio-max-nr” in “/etc/sysctl.conf”  file.

After this you can calibrate your I/O. You will need to do this in order to have realistic metric thresholds for you I/O.

To calibrate you have two ways:

* Calibrate with Enterprise Manager (EM)  interface
* Calibrate using the PL/SQL API

Calibrating your I/O with EM

This is supposed to be easy, but after you submit your calibration process you might wonder where it is. It’s the EM way! To get lost, or loose your context! Here are the steps to submit the process:

1) Go to Performance tab, and underneath the “Average Active Sessions” graphic click the “I/O” tab
2) After this you will need to page down again, and find that the I/O tab has a “I/O Calibration” button. Press it.
3) Now you just have to fill two text boxes: number of disks, and expected latency. Click Submit. You’re done.
4) Now go back and click the Server tab, and under “Oracle Scheduler” click “Jobs”, and then click the “Running” tab. You’ll find your Calibration Job here.

Calibrating your I/O with the PL/SQL API

Inside the DBMS_RESOURCE_MANAGER package there’s a procedure called CALIBRATE_IO that you must invoke with two variables going in:

SQL> desc DBMS_RESOURCE_MANAGER
PROCEDURE CALIBRATE_IO
Argument Name                  Type                    In/Out Default?
------------------------------ ----------------------- ------ --------
NUM_PHYSICAL_DISKS             BINARY_INTEGER          IN     DEFAULT
MAX_LATENCY                    BINARY_INTEGER          IN     DEFAULT
MAX_IOPS                       BINARY_INTEGER          OUT
MAX_MBPS                       BINARY_INTEGER          OUT
ACTUAL_LATENCY                 BINARY_INTEGER          OUT

Checking the Status and AfterMath of Calibration

After you submit your Calibration process either by EM or PL/SQL you can keep up to date of its status by querying the following view:

SQL> select * from v$io_calibration_status;

STATUS
-------------
CALIBRATION_TIME
---------------------------------------------------------------------------
IN PROGRESS

After a while the same query will give back a finishing status:

SQL> select * from v$io_calibration_status;

STATUS
-------------
CALIBRATION_TIME
---------------------------------------------------------------------------
READY
23-MAY-09 05.57.33.317 PM

And now it’s the time to check on the results, either by clicking again on the “I/O Calibration” button on EM and checking the “Existing Calibration Results” or by issuing the following query:

SQL> select * from dba_rsrc_io_calibrate

START_TIME
---------------------------------------------------------------------------
END_TIME
---------------------------------------------------------------------------
 MAX_IOPS   MAX_MBPS  MAX_PMBPS    LATENCY NUM_PHYSICAL_DISKS
---------- ---------- ---------- ---------- ------------------
23-MAY-09 05.37.25.117503 PM
23-MAY-09 05.57.33.316982 PM
 85         14         15         89                  1

Where’s Alert.log in Oracle11g?

In Oracle11g with the introduction of the dreadfull ADRCI people lost track of where the heck is ALERT.LOG? Is it true that ALERT.LOG is no longer in text mode? Is it true that I’ll have to buy an XML book to read my DB messages?  STOP THE PANIC!

You have two logs now: one for humans like you that have to see it in text, and other for the EM to bring up in a beatifull tabled format (log.xml).

[oracle@linux trace]$ pwd
/u01/app/oracle/diag/rdbms/orcl/orcl/trace

[oracle@linux trace]$ ll alert_orcl.log
-rw-r—–  1 oracle oinstall 24219 Abr 29 11:00 alert_orcl.log

[oracle@linux trace]$ ll ../alert/log.xml
-rw-r—–  1 oracle oinstall 378669 Abr 29 11:00 ../alert/log.xml

Data Pump Data Compression (new in 11g)

This is a new feature from 11g that I think was the missing link in Oracle Data Pump: compression.It allows you to compress both metadata and data, either or neither. If network mode it’s not possible for you and you are bounded by disk storage: choose compression.

To explain it what’s better than the thing less read in the world of technology: the instructions manual (which has an example that sucks because it presents the default value … but since it’s oracle product documentation it’s not suppose to be good, but only satisfactory)

COMPRESSION

Default: METADATA_ONLY

Purpose

Specifies which data to compress before writing to the dump file set.

Syntax and Description

COMPRESSION={ALL | DATA_ONLY | METADATA_ONLY | NONE}

• ALL enables compression for the entire export operation.
• DATA_ONLY results in all data being written to the dump file in compressed format.
• METADATA_ONLY results in all metadata being written to the dump file in compressed format. This is the default.
• NONE disables compression for the entire export operation.

Restrictions

• To make full use of all these compression options, the COMPATIBLE initialization parameter must be set to at least 11.0.0.
• The METADATA_ONLY option can be used even if the COMPATIBLE initialization parameter is set to 10.2.

Example

The following is an example of using the COMPRESSION parameter:

> expdp hr DIRECTORY=dpump_dir1 DUMPFILE=hr_comp.dmp
COMPRESSION=METADATA_ONLY

This command will execute a schema-mode export that will compress all metadata before writing it out to the dump file, hr_comp.dmp. It defaults to a schema-mode export because no export mode is specified.

Duplicate Database Yet More Simplified in 11g

Duplicating a Database : <<this is an excert from Oracle University doc>>
Prior to Oracle Database 11g, you could create a duplicate database with RMAN for testing or for standby. It required the source database, a copy of a backup on the destination system, and the destination database itself.
Oracle Database 11g greatly simplifies this process. You can instruct the source database to perform online image copies and archived log copies directly to the auxiliary instance, by using Enterprise Manager or the FROM ACTIVE DATABASE clause of the RMAN DUPLICATE command. Preexisting backups are no longer needed.
The database files are copied from the source to a destination or AUXILIARY instance via an inter-instance network connection. RMAN then uses a “memory script” (one that is contained only in memory) to complete recovery and open the database.

Active Database Duplication
Usage Notes for Active Database Duplication
Oracle Net must be aware of the source and destination databases. The FROM ACTIVE DATABASE clause implies network action.
If the source database is open, it must have archive logging enabled.
If the source database is in mounted state (and not a standby), the source database must have been shut down cleanly.
Availability of the source database is not affected by active database duplication. But the source database instance provides CPU cycles and network bandwidth.
Enterprise Manager Interface
In Enterprise Manager, select Data Movement > Clone Database.

Password files are copied to the destination. The destination must have the same SYS user password as the source. In other words, at the beginning of the active database duplication process, both databases (source and destination) must have password files.
When creating a standby database, the password file from the primary database overwrites the current (temporary) password file on the standby database. When you use the command line and do not duplicate for a standby database, then you need to use the PASSWORD clause (with the FROM ACTIVE DATABASE clause of the RMAN DUPLICATE command).

Prior to Oracle Database 11g, the SPFILE was not copied, because it requires alterations appropriate for the destination environment. You had to copy the SPFILE into the new location, edit it, and specify it when starting the instance in NOMOUNT mode or on the RMAN command line to be used before opening the newly copied database.
With Oracle Database 11g, you provide your list of parameters and desired values and the system sets them. The most obvious parameters are those whose value contains a directory specification. All parameter values that match your choice (with the exception of the DB_FILE_NAME_CONVERT and LOG_FILE_NAME_CONVERT parameters) are placed.
Note the case-sensitivity of parameters: The case must match for PARAMETER_VALUE_CONVERT. With the FILE_NAME_CONVERT parameters, pattern matching is operating system specific.
This functionality is equivalent to pausing the database duplication after restoring the SPFILE and issuing ALTER SYSTEM SET commands to modify the parameter file (before the instance is mounted).
The example in the slide shows how to clone a database on the same host and in the same Oracle Home, with the use of different top-level disk locations. The source directories are under u01, the destination under u02: You need to confirm your choice.

Example:

 DUPLICATE TARGET DATABASE
  TO dbtest
  FROM ACTIVE DATABASE  
SPFILE PARAMETER_VALUE_CONVERT '/u01', '/u02'
    SET SGA_MAX_SIZE = '200M'
    SET SGA_TARGET = '125M'
    SET LOG_FILE_NAME_CONVERT = '/u01','/u02'
DB_FILE_NAME_CONVERT = '/u01','/u02';

A new paradigm: Oracle 11g’s feature for context objects invalidation (COI)

Attention: revolution has come to Oracle RDBMS!

How many times you’ve had modified a table and just because of that simple change, all objects (views, subprograms, etc.) dependent on that table get invalidated?
It’s probably the best known Oracle “feature” for developers!

Well guys, this all is about to change taking a 180 degrees spin! Only the changes that afect dependent objects will invalidate them. This will decrease 90% of total invalidations.

Let’s use a simple, very simple scenario:

1 – Table with one column
2- View based on this table but created with “SELECT *”
3- We add a new column to the table
4- The view not only doesn’t invalidates, but discards the new column from the “SELECT *”

Wow!!! This is freaky! It is a new paradigm for developers.

Here’s the code for who need to see it to understand it:

SQL*Plus: Release 11.1.0.7.0 - Production on Thu Mar 5 00:37:25 2009

Copyright (c) 1982, 2008, Oracle.  All rights reserved.
SQL> conn hr/hr
Connected.
SQL> create table inva (n number);

Table created.

SQL> create or replace view invaview as select * from inva;

View created.

SQL> select count(*) from user_objects where status='INVALID';

  COUNT(*)
----------
         0
SQL> alter table inva add (n2 number);

Table altered.

SQL> select count(*) from user_objects where status='INVALID';

  COUNT(*)
----------
         0

SQL> insert into inva values (1,1);

1 row created.

SQL> select * from  invaview;

         N
----------
         1

Oracle 11g new System Partitioning feature

Oracle 11g is an exciting new release in terms of new features. There are tons of new features when it comes to partitioning. From virtual column partitioning, to new combinations of subpartitioning.

One of those new partition features is system partitioning that enables you to create a table that in practice it’s dynamically partitioned. You just have to select the names and number of partitions and then at the DML stage you decide.

Let’s take it slowly. First create the table with two partitions, each in a different tablespace. Here we take two default tablespaces, but you should use “real” permanent tablespaces:

SQL> create table SMALL_PARTS_ISOLATED
  2  (ncol number, xar varchar2(80))
  3  PARTITION BY SYSTEM
  4  (partition P1 tablespace users,
  5   partition P2 tablespace example);

Table created.

The key is that DML is done in a different fashion, because you have to decide in which partition you’ll put your rows into. Let’s imagine you don’t know this is a system partitioned table and you’ll do a “normal” insert. In this case you get the following error:

SQL> insert into SMALL_PARTS_ISOLATED values (1,'Nomeansno');
insert into SMALL_PARTS_ISOLATED values (1,'Nomeansno')
            *
ERROR at line 1:
ORA-14701: partition-extended name or bind variable must be used for DMLs on
tables partitioned by the System method

Wow! This error message wasn’t produced at the regular shop where Oracle uses to make them! Why? Because it’s crystal clear.
And now the right way to do DML in a system partitioned table by using the PARTITION keyword:

SQL> insert into SMALL_PARTS_ISOLATED PARTITION (p2) values (1,'YesMan');

1 row created.

SQL> insert into SMALL_PARTS_ISOLATED PARTITION (p1) values (2,'TwiceIsCheaper');

1 row created.

When you select from it this is a regular table like any other:

SQL> select * from small_parts_isolated;

      NCOL XAR
---------- --------------------------------------------------------------------------------
         2 TwiceIsCheaper
         1 YesMan

But how the heck to we know the name of the partition in order to insert it into the right place? This last table had partition P1 and P2, so it should be right to assume that it might have a partition called “P3″:

SQL> insert into SMALL_PARTS_ISOLATED PARTITION (p3) values (30,'NonExistentPartition');
insert into SMALL_PARTS_ISOLATED PARTITION (p3) values (30,'NonExistentPartition')
                                            *
ERROR at line 1:
ORA-02149: Specified partition does not exist

Oopsy dasy… Looks like our assumption was wrong…

The way I see this, Oracle Corp. should have had the idea to incorporate partition’s names at the DESCRIBE command, but this is asking too much from those folks. So describing the table won’t help a thing:

SQL> desc small_parts_isolated
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 NCOL                                               NUMBER
 XAR                                                VARCHAR2(80)

Looks like we’re going to have a trip to the dreadfull Oracle data dictionary. For this we used a query from 24th June 1997:

SQL> select partition_name
  2  from user_tab_partitions
  3  where table_name = 'SMALL_PARTS_ISOLATED';

PARTITION_NAME
------------------------------
P1
P2

Does MEMORY_TARGET parameter includes the Redo Log Buffer?

Does MEMORY_TARGET parameter includes the Redo Log Buffer?

Yep. Just try to reset to zero the LOG_BUFFER parameter, and voilá! the new AMM (Automatic Memory Management) of Oracle 11g gives the small buffer a default value of 823KB. Here’s how its done:

SQL> show parameter memory

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
hi_shared_memory_address             integer     0
memory_max_target                    big integer 500M
memory_target                        big integer 500M
shared_memory_address                integer     0
SQL> show parameter log_buf

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_buffer                           integer     5702144
SQL> show parameter keep

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
buffer_pool_keep                     string
control_file_record_keep_time        integer     7
db_keep_cache_size                   big integer 0
SQL> alter system set log_buffer = 0 scope=spfile;

System altered.

SQL> startup force
ORACLE instance started.

Total System Global Area  523108352 bytes
Fixed Size                  1314492 bytes
Variable Size             440402244 bytes
Database Buffers           79691776 bytes
Redo Buffers                1699840 bytes
Database mounted.
Database opened.
SQL> show parameter log_bu

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_buffer                           integer     851968

SYSASM Role

SYSASM role exists to manage ASM instances and avoids overlaping between DBAs and storage administrators

SQL> CONNECT / AS SYSASM

For ASM instances, SYSDBA will be deprecated in the future:

• Oracle Database 11g Release 1 behaves as in 10g
• In future releases SYSDBA privileges restricted in ASM instances

Save EM Repository information when upgrading to 11g

Saving your EM information

This is something taken from the Oracle Documentation and something that can assault you in case you wonder: “If I loose my Enterprise Manager intell I’m doomed!”. Well for those who can’t aford to loose it here’s what Oracle tells you to do:

The emdwgrd utility resides in the ORACLE_HOME/bin directory in the Oracle Database 11g Release 1 (11.1) home. Before running the utility you must install Oracle Database 11g Release 1 (11.1) and invoke the script from the Oracle Database 11g Release 1 (11.1) home. The emdwgrd utility, however, requires that you set ORACLE_HOME to the old Oracle home.

The following procedure is for Linux and UNIX. To run it on Windows, simply substitute emdwgrd.bat for emdwgrd.
Follow these steps to save your Database Control files and data:

1.Install Oracle Database 11g Release 1 (11.1).
2.Set ORACLE_HOME to your old Oracle home.
3.Set ORACLE_SID to the SID of the database being upgraded.
4.Set PATH, LD_LIBRARY_PATH and SHLIB_PATH to point to the Oracle home from which the database is being upgraded.
5.Go to the Oracle Database 11g Release 1 (11.1) home.
6.Execute one of the following:

For a single-instance database, run the following command, where old_SID is the SID of the database being upgraded and save_directory is the path to the storage place you have chosen for your Database Control files and data:

emdwgrd -save -sid old_SID -path save_directory

ASM and Database version compatibility between 10g and 11g

Scenario:
You have two 10g databases in two different Oracle Homes and only one ASM 10g instance (in its own Oracle Home).

You need to:
Upgrade one of the databases to 11g keeping the same ASM

Follow the instructions on this simple document to make sure you don’t blow your storage to pieces:

ASM and Oracle11g version support

Main tags: DBUA, ASM, Oracle 11g, Upgrade