Get distinct datafile paths.

Code:

select distinct substr(file_name,1,instr(file_name,'/',-1)) from dba_data_files;

List tables which have specific column with number of rows for a given requirement.

select q'[select ']'||table_name||q'[' as table_name, count(*) from ]'||table_name||q'[ where COL1='VAL1';]' from dba_tab_columns where column_name='COL1' and upper(owner) ='USER';

Exclude specific tables from expdp

Here is the command

expdp user/password@sid schemas=schema_name include=TABLE:"IN ('TABLE1', 'TABLE2')" 

directory=DATAPUMP_DIR dumpfile=user_date.dmp logfile=user_date.log

Some operating systems need special characters to be escaped

include=TABLE:\"IN (\'TABLE1\', \'TABLE2\')"

Or

include=TABLE:\"IN \(\'TABLE1\', \'TABLE2\'\)"
  

Update a table column using a sequence

create table duplicate as select * from original
alter table duplicate add ID number;
create sequence seq1

set pagesize 0

set linesize 200

spool update.sql
select 'update duplicate set ID='||seq1.nextval||q'[ where rowid=']'||rowid||q'[';]' from duplicate;

spool off

>@update.sql

/OVS is empty after installing Oracle VM Server

Had to pull your hair to figure out why /OVS is empty after a fresh installation of Oracle VM Server? Well It's the default behavior in OVM Server 3.0.3, unlike 2.2.0.
It can be and should be done via Oracle VM Manager.
Click here to see how I found it out.

OVM-1004 XML-RPC Client Call Oracle VM Agent API 'register_server' error: exceptions.Exception:method "register_server" is not supported, please try again later.

If you get this error when you try to create a new server pool, then you are probably using two different versions of VM Server and  VM Manager which are not compatible. Look! error is misleading. This won't get resolved even if you try again a bit later, until you  have right packages installed.

Click here to see how I found this out. 

VM Manager 3.0.3 on SuSE 11

Today I tried installing VM Manager 3.0.3 on SuSE 11 and got the following.

Oracle VM Manager can only be installed on a 64 bit Oracle Linux version 5.5 and higher.
Current version is 'SuSE' version '11'
**** WARNING: Recommended memory for the Oracle VM Manager server installation is 3950 MB RAM*
Configuration verification failed

 Solution:
Error says it all. Have OL 5.5 or higher!

Can't connect to Oracle VM Manager home page

This is a firewall issue!

Open http access

system-config-securitylevel-tui -q -p 8888

Open https access

system-config-securitylevel-tui -q -p 4443

Disable iptables altogether!

service iptables stop

Installing Oracle Linux over network - No DVD-ROM

I was able to install Oracle VM Server and wanted to install Oracle VM Manager in a separate machine in order to manage VM's in VM Server. Oracle does not recommend installing Oracle VM Manager in a VM which will eventually be managed by Oracle VM Manager itself. So, my plan was to install Oracle Linux and then install Oracle VM Manager on top of it. The machine in question, did not have a DVD-ROM, but had a CD-ROM. Here is how I got through.

Packages in hand
Oracle Linux Release 6 boot iso image
Oracle Linux Release 6

Configuring NFS
I used a separate Linux server for this. Yes it did have a DVD-ROM and I did not want to install VM Manager on it.
Create two directories.

mkdir /x01/package  /x01/inst

Place OEL6 iso at /x01/package directory.
 Unzip it


 Now mount it.

mount -ro loop /x01/package/OEL6.iso /media

Copy required files to /x01/inst directory.

cp -ar /media/. /x01/inst/

To setup NFS share, Add following line to /etc/export.

/x01/inst         *(ro,sync)

export the directory and restart NFS service.

exportfs -a

service nfs stop

service nfs start

To see the status of the share

showmount -e

NFS share is created. Rest is easy. Boot the machine with OEL boot cd and select the installation method as NFS. Provide NFS IP address and the path (/x01/ins). Installation would be started in no time. After the installation is completed, Oracle VM Manager package can be installed right away. I used FTP to transfer the package.

Installing Oracle VM Server in VMware Workstation

These days I'm particularly interested in Oracle Virtualization, though I do not have enough resources to play with. Today I decided to install Oracle VM Server in VMware workstation which is actually useless for real world scenarios.
I tried to install Oracle VM in VMware as I used to do with any other operating systems, but it didn't work out. But later on I found what was missing.
These are the additional steps.

1. Download Oracle VM Server ISO and extract it.
2. Open out VMware workstation
3. Select VMware Workstation 5 from the drop down menu when a new virtual machine is created and additionally tick on "ESX Server Compatible"
4. Operating system : Linux -> Other Linux 2.6.x kernel64-bit

Rest of the steps are quite the usual.

When booting the new virtual machine, I got the following error

ERR: Not a 64-Bit CPU!erver-ovs (xen-64-3 .1.3 2.6.18-8.1.15.0.16.el5ovs)

This can be resolved by pressing [ENTER] while booting and selecting 32 bit installation from the menu.

ORA-15505: cannot start workload capture because instance 1 encountered errors

I was trying to start a database workload capture so that I can replay it. Here is the sequence.

Directory was created.

SQL> create directory mydir as '/x01/oracle/softwares/datapump';

Capture was started

SQL> begin
  2   dbms_workload_capture.start_capture('Migration Test','MYDIR');
  3  end;
  4  /

PL/SQL procedure successfully completed.

After some time I decided to finish the capture.

SQL> begin
  2  dbms_workload_capture.finish_capture;
  3  end;
  4  /

PL/SQL procedure successfully completed.

Suddenly I thought the workload captured is not just enough for my simulations. So I started capturing again. Destination is the same directory. Here is what I got.

SQL> begin
  2  dbms_workload_capture.start_capture('Migration Test 2','MYDIR');
  3  end;
  4  /
begin
*
ERROR at line 1:
ORA-15505: cannot start workload capture because instance 1 encountered errors
while accessing directory "/x01/oracle/softwares/datapump"
ORA-06512: at "SYS.DBMS_WORKLOAD_CAPTURE", line 811
ORA-06512: at line 2

Solution.

It was turned out to be impossible to direct my capture destination to a directory which is not empty. Removing old files in MYDIR solved the issue.

ORA-01187: cannot read from file because it failed verification tests

After doing some intensive database simulations I decided to do some SQL tuning. So I went to EM and selected SQL tuning advisor. Since I did not have pre-configured SQL tuning sets, I selected a suitable time window in SQL top activity pane. Everything  looked fine until I clicked on submit button.
Bad luck.

There was a problem creating a SQL tuning task. ORA-01187: cannot read from file because it failed verification tests ORA-01110: data file 201: '/u01/app/oracle/oradata/testdb/temp01.dbf' ORA-06512: at "SYS.DBMS_SQLTUNE_INTERNAL", line 7718 ORA-06512: at "SYS.PRVT_SQLADV_INFRA", line 275 ORA-06512: at "SYS.DBMS_SQLTUNE", line 674 ORA-06512: at line 1

As you can see, error was ridiculously  long. Look, something is wrong with the temp file. So first thing I did was to check if the temp file really exists!

SQL> !ls /u01/app/oracle/oradata/testdb/temp01.dbf
/u01/app/oracle/oradata/testdb/temp01.dbf

It does exist. Now what? I checked dba_temp_files to check if it is listed there.

SQL> show user
USER is "SAJEEVA"
SQL> conn / as sysdba
Connected.
SQL> select file_name from dba_temp_files;
select file_name from dba_temp_files
                      *
ERROR at line 1:
ORA-01187: cannot read from file  because it failed verification tests
ORA-01110: data file 201: '/u01/app/oracle/oradata/testdb/temp01.dbf'

Something terribly wrong with the temp file. So here is how I solved it.

First I added another temp file to temp tablespace.

SQL> alter tablespace temp add tempfile;

Tablespace altered.

OMF was enabled, So no need to give the absolute path. 
Then I deleted the currupted (lets say currupted)

SQL> alter tablespace temp drop tempfile '/u01/app/oracle/oradata/testdb/temp01.dbf';

Tablespace altered.

Problem solved.

SQL> select file_name from dba_temp_files;

FILE_NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/TESTDB2/datafile/o1_mf_temp_7kfxh4jq_.tmp

Understanding Block Curruption

When oracle detects a block curruption, It writes an error in alert log as follows.

ORA-01578: ORACLE data block corrupted (file # X, block # Y)
ORA-01110: data file X: '/x01/app/oracle/oradata/ora11g/users01.dbf

Error itself shows which data file has the corrupted block. Usually curruption is caused by hardware failure or operating system caching problems.

To get the tablespace name, we can use following query.

select tablespace_name from dba_data_files where file_id=X;

Let's say tablespace is USERS for the moment.
Then we can use following query to find more details.

select tablespace_name,segment_name,extent_id,bytes,file_id,blocks,block_id from dba_extents where tablespace_name='USERS' order by block_id;

Output will give each extents and the starting block id. So we can easily find the specific segment name which has the corrupted block.


Block media recovery.
Following prerequisites should be fullfilled.
Database must be in ARCHIVELOG mode.
Full backup or Level 0 incremental backup should be available.

RMAN>recover datafile X block Y;

ORA-00845: MEMORY_TARGET not supported on this system

I have seen this error under two different circumstances and thought it would be helpful to note it down how I got it solved.

Case 1:
I already had the database installed. Oracle 11.2.0.2.0, RHEL 5, single instance. Then I installed Grid Infrastructure and created several ASM disk groups. Everything was just fine until server got rebooted. When the server was up, ASM instance was automatically up and running. When I tried to start the database instance, I got the following.

ORA-00845: MEMORY_TARGET not supported on this system

Solution to this particular problem is painfully explained in the web. But for non-ASM instance scenarios!
The reason here is that, now we have database instance plus an ASM instance, means oracle needs more memory!

I already had a shared file system, but to increase it's size, I did unmount it and mounted again with a larger size.

# umount tmpfs
# mount -t tmpfs shmfs -o size=<size>m /dev/shm

Then I tried to startup the database, but eventually found out that ASM instance had been crashed mysteriously ( I guess umount might have released the memory which was allocated to ASM, hence crashed )

So, then I tried to startup the ASM instance, but effort was in vain, until I execute this command,

crsctl start resources -all

Then everything seems to be fine and I could start the database instance.


Case 2:
My oracle database was in ASMM (Automatic Shared Memory Management) mode and I wanted to enable AMM (Automatic Memory Management).

alter system set memory_max_target=1G scope=spfile;
shutdown immediate;
startup;

Then I got our beloved error again.  (Yes, this system is not so generous to give 1G to oracle, It was a VMware 1G guest in 2G windows host)


Unfortunately I realised that I hadn't created a spfile so that there is no way that I can revert it back just in case. But fortunately I had enabled autobackup and had taken several database backups. So spfile is luckily available in FRA.


So I deleted renamed current spfile and logged in to RMAN to start the database with default parameters. 

rman target /
startup;

Chaos! system did not mount as control files locations are not known. But it's ok for the moment. Then I tried to restore the spfile

RMAN>restore spfile from autobackup;
RMAN-06495: must explicitly specify DBID with SET DBID command

Now how to find dbid? I could have found it by querying v$database if database was mounted. But it was not. chicken egg problem!
Luckily there are other ways too. This can be obtained using existing backups. here is how,

strings file_name |grep MAXVALUE, (In case of SYSTEM datafile)
strings file_name |grep MAXVALUE (In case of UNDO datafile)

So, Then suddenly everything seemed to be easy.

RMAN>set dbid=xxxxxxx
RMAN>restore spfile from autobackup;


SQL>shutdown immediate;
SQL>startup;

ORA-01830: date format picture ends before converting entire input string

I just needed to do a point in time recovery of the tablespace TESTTB, so that I can recover objects which were dropped, or undo recent logical corruptions. But RMAN didn't seem to be happy with the date format I entered.

RMAN> recover tablespace testtb until time '09-JAN-2012 12:21:15' auxiliary destination '/x01/oracle/auxiliary';

Starting recover at 09-JAN-2012 14:14:19
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=31 device type=DISK
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 01/09/2012 14:14:20
ORA-01830: date format picture ends before converting entire input string

I exported NLS_DATE_FORMAT and tried again, but it didn't work.

[oracle@rhel auxiliary]$ export NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS';
RMAN> recover tablespace testtb until time '09-JAN-2012 12:21:15' auxiliary destination '/x01/oracle/auxiliary';

Starting recover at 09-JAN-2012 14:03:29
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1 device type=DISK
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 01/09/2012 14:03:30
ORA-01830: date format picture ends before converting entire input string

Solution

Use explicit date conversion.

RMAN> recover tablespace testtb until time "to_date('09-jan-2012 12:21:15','dd-mon-rrrr hh24:mi:ss')" auxiliary destination '/x01/oracle/auxiliary';

Note: You can't do TSPITR to a tablespace which contains objects owned by SYS. Objects owned by SYS are not considered as self contained. If you do so, you would get the following error.

starting up automatic instance ORA11G

Oracle instance started

Total System Global Area     292933632 bytes

Fixed Size                     1336092 bytes
Variable Size                100666596 bytes
Database Buffers             184549376 bytes
Redo Buffers                   6381568 bytes
Automatic instance created
Running TRANSPORT_SET_CHECK on recovery set tablespaces
The following errors need to be fixed before peforming this command
Violation: ORA-39917: SYS owned object TESTTB in tablespace TESTTB not allowed in pluggable set

RMAN-05541: No archived logs found in target database

After setting up the auxiliary instance ora11g2, I tried to duplicate the database ora11g.

#rman target sys/pwd@ora11g auxiliary sys/pwd@ora11g2;
RMAN>duplicate target database to ora11g2 from active database

I got the following error.

using channel ORA_AUX_DISK_1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command
RMAN-05541: no archived logs found in target database

Solution
Archive current redo log file and proceed with the duplication.

SQL> alter system archive log current;

RMAN> duplicate target database to ora11g2 from active database;
Starting Duplicate
using channel ORA_AUX_DISK_1
contents of Memory Script: { sql clone "create spfile from memory"; }
executing Memory Script
sql statement: create spfile from memory
contents of Memory Script: { shutdown clone immediate; startup clone nomount; }
executing Memory Script

ORA-01150: cannot prevent writes - file has online backup set

You will see the following error when you try to drop a tablespace which is having one or more datafiles in backup mode.

ORA-01150: cannot prevent writes - file 6 has online backup set
ORA-01110: data file 6: '+DATA2/ora11g/datafile/newtb.266.772045007'

Solution

Change the backup mode

ALTER TABLESPACE NEWTB END BACKUP;

 

RMAN-06172: no AUTOBACKUP found or specified handle is not a valid copy or piece

You might see this error when  you lost your SPFILE and try to restore it from autobackup.

Solution.

RMAN> restore spfile from autobackup recovery area='+RECOV' db_name=ora11g;

+RECOV is my flash recovery area resides on ASM. If you haven't configured ASM, you should use absolute path of FRA.

If you are sure that there is an autobackup, Find it's path.
If you have configured ASM, This would usually be at +RECOV/ora11g/autobackup/<latest_date>

For Ex:  +RECOV/ora11g/autobackup/2012_01_07/s_771966441.386.771966443

Then you can restore your SPFILE using

RMAN> restore spfile from '+RECOV/ora11g/autobackup/2012_01_07/s_771966441.386.771966443';