Moving or renaming Oracle datafiles.

If you need to move or rename Oracle datafiles you can do it two different ways.

You can use alter database or alter tablespace clause to move or rename datafiles.

With alter database you need to shutdown the database instance to make needed changes.

And with alter tablespace clause without need to shutdown database instance. But you can't use alter tablespace for datafiles moving/renaming with SYSTEM, UNDO or TEMP tablespaces. And if you are using alter tablespace for datafiles moving/renaming it will take those datafiles offline. And this means that if your applications are trying to use data from those datafiles during offline they'll get errors. So usually you don't want to use this clause with production databases. Changes like these in production enviroment need to be tested first in test enviroment and then done in maintenance window with alter database clause and clean shutdown.

You can also use RMAN for renaming/moving the datafiles but that also needs to use offline.
Althought with RMAN the offline time is smaller because you can copy datafile into new path before you take it offline. But I prefer the planned maintenance window with alter database clause. 

Datafiles moving with alter tablespace clause:
----
1. Change the tablespace offline.

sqlplus / as sysdba
SQL> alter tablespace test_data offline;

2. Renaming/moving the datafile using operating system commands.

For example in linux:

cp /data/oracle/datafiles/test_data_01.dbf /data_new/oracle/datafiles/test_data_01.dbf

or renaming it at the same time:
cp /data/oracle/datafiles/test_data_01.dbf /data_new/oracle/datafiles/test_data_05.dbf

3. Use the alter tablespace clause to rename the file in the database.

SQL> alter tablespace test_data rename datafile '/data/oracle/datafiles/test_data_01.dbf' TO '/data_new/oracle/datafiles/test_data_01.dbf';

or renaming it at the same time:
SQL> alter tablespace test_data rename datafile '/data/oracle/datafiles/test_data_01.dbf' TO '/data_new/oracle/datafiles/test_data_05.dbf';

4. Change the tablespace back online. After this you can again use this tablespace all datafiles normally.

SQL> alter tablespace test_data online;

----

Datafiles moving with alter database clause:

----

1. Shutdown the database instance.

sqlplus / as sysdba
SQL> shutdown immediate;

2. Renaming/moving the datafile using operating system commands.

For example in linux:

cp /data/oracle/datafiles/test_data_01.dbf /data_new/oracle/datafiles/test_data_01.dbf

or renaming it at the same time:
cp /data/oracle/datafiles/test_data_01.dbf /data_new/oracle/datafiles/test_data_05.dbf

3.Start database into mount and rename/move datafiles in the database with alter database clause. You need to use full path and filenames.

SQL> startup mount;

SQL> alter database rename file '/data/oracle/datafiles/test_data_01.dbf' TO '/data_new/oracle/datafiles/test_data_01.dbf';

or renaming it at the same time:
SQL> alter database rename file '/data/oracle/datafiles/test_data_01.dbf' TO '/data_new/oracle/datafiles/test_data_05.dbf';

4. Open the database.

SQL> alter database open;

----

Undo tablespace shrink

If your Undo tablespace is overgrown and you need to shrink it. This can be done by creating a new Undo tablespace, start using it and remove the old one after that. Here is example:

----

Create new Undo tablespace with suitable size
SQL> create undo tablespace undotbsnew datafile '<path_to_the_datafiles_usually same_where current_undo_datafile_is>\UNDOTBSNEW.DBF'
size 1024m autoextend on next 5120K maxsize 32767M;

Set new tablespace as current Undo tablespace
SQL> alter system set undo_tablespace= UNDOTBSNEW scope=both;

Drop the old tablespace
SQL> drop tablespace UNDOTBS1 including contents;

-Don't use "and datafiles" clause in drop tablespace instead of that clear old Undo datafiles from OS side (Read my earlier "Cleaning Oracle logs / trace files / datafiles on the fly. " post for more information about this).

In Windows make empty text file test.txt
copy test.txt UNDOTBS1.DBF
In Linux:ssa do following:
cat /dev/null > UNDOTBS1.DBF

After you can restart database you can remove these old Undo datafiles from OS. Before database restart OS does not let you do it.


And after you have removed old Undo datafiles you can then recreate Undo tablespace with old name. But usually rhere is no need for that.

And remember that if you use auto extended Undo tablespace don't do to big DML operations without frequent commits. If you do you can get really big Undo tablespaces and other problems.

----

Cleaning Oracle logs / trace files / datafiles on the fly.

Regardless which OS you are using straight delete for Oracle logs / trace files / datafiles is not good option. Of course there is differences between files but usually these files used space is freed up only after database instance restart. So best way to clean these files is to make files size to zero. This way OS is freeing up the used space on the fly.

In *unix you can do it this way:
cat /dev/null > listener.log
and in the Windows you can copy zero size file over the orginal big size file.
When you are removing tablespaces do not use "and datafiles" clause in drop tablespace clause. Instead of that drop tablespace without "and datafiles" then change dropped tablespace datafiles sizes to zero (like above) from OS side.  And last you can delete datafiles from OS side after you have restarted database.

Recreate TEMP tablespace in Oracle

Sometimes you need  to recreate TEMP tablespace in Oracle. For example if it is overgrown and you can't shrink it.  With following sql clauses you can create new TEMP tablespace, start to use it and remove old one:

New TEMP tablespace name in this example is TEMP2 and current TEMP tablespace name is TEMP.

Create new TEMP tablespace:

CREATE SMALLFILE TEMPORARY TABLESPACE "TEMP2" TEMPFILE '<path_to_datafiles>temp02.dbf' SIZE 1024M REUSE AUTOEXTEND ON NEXT 640K MAXSIZE 6144M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1024K;

Start to use new TEMP tablespace:

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE "TEMP2";

Remove old TEMP tablespace:
(This removing can be done only if nobody isn't using old TEMP tablespace.
You can check if there is still users in old TEMP tablespace with following sql (If you are using RAC you need to run this in all nodes):
select a.username, a.sid, a.serial#, a.osuser, b.tablespace, b.blocks from v$session a, v$tempseg_usage b where a.saddr = b.session_addr order by b.tablespace, b.blocks;

If there is users in old TEMP tablespace you need to kill those sessions or wait until they are ending normal way.

After there is no open sessions using old TEMP tablespace you can remove it with following sql:
DROP TABLESPACE TEMP INCLUDING CONTENTS AND DATAFILES;

Oracle 11.2 RHEL 6 VMware and ASMlib

If you need to install Oracle 11.2 into RHEL 6 VMware servers you face problems with Oracle ASMlib.
ASMlib is not supported anymore with RHEL 6. Oracle recommends that you use Oracle Unbreakable Linux because it's support ASMlib. And that is not bad choice at all.

But if you need to stick with RHEL 6 then you have to do it without the ASMlib. And you can do it with udev rules. If you are not using VMware you can use also multipath choice but with VMware you need to use udev.

I assume here that all disk are already added into the needed virtual machines.

First you have to enable scsi id's from VMware. If you don't do this you does not see disks id's in RHEL.
Shutdown virtual machine and do the following  (this needs to be done all servers if you are installing RAC)go to the Options tab --> select the Advanced -> General on the left and click the Configuration Parameters button. --> Click the Add Row button and add disk.EnableUUID with value true --> Ok --> reboot Virtual machines

More info about SCSI UUID's from here:
http://diznix.com/2011/05/21/the-case-of-vmware-and-the-missing-scsi-id/

Then create udev rules to find the disks and to create symlinks to them (this needs to be done all servers if you are installing RAC):
Create file /etc/udev/rules.d/99-oracle-asmdevices.rules

File can look like this (in PROGRAM you fetch the SCSI_id which should match the correct disk RESULT value. And SYMLINK creates accepted symlink for the disk.) :

# crs
KERNEL=="sd*1", BUS=="scsi", PROGRAM=="/sbin/scsi_id -g -u --device=/dev/$parent /dev/$name", RESULT=="36000c29de840f074b4c0ecd97719d3c0", OWNER="oracle", GROUP="dba", MODE="0660", SYMLINK+="sdASMcrs"
# data
KERNEL=="sd*1", BUS=="scsi", PROGRAM=="/sbin/scsi_id -g -u --device=/dev/$parent /dev/$name", RESULT=="36000c29d5b7c9a2cf306acf4dbc2d6de", OWNER="oracle", GROUP="dba", MODE="0660", SYMLINK+="sdASMdata"
# arch
KERNEL=="sd*1", BUS=="scsi", PROGRAM=="/sbin/scsi_id -g -u --device=/dev/$parent /dev/$name", RESULT=="36000c29051374180400e5599ae556288", OWNER="oracle", GROUP="dba", MODE="0660", SYMLINK+="sdASMarch"
.
.
.

Last reboot the servers and check that disks are visible correctly in all of them and start the installation.

Grid Control 11g (OMS) repository database moving into new database and passwords

If you need to move your Grid Control (OMS) repository database into new database. You probably do it with following guide (this can be find from MOS (My Oracle Support)):
11g Grid Control: Steps for Migrating the 11g Grid Control Repository from One Database to Another [ID 1302281.1]

This guide will work if you remember to use EE database and always run scripts with correct user. Best way to run individual scripts is sign out and in into sqlplus always between different scripts. This way you always check that the user is correct.

Problem with this guide is the Grid Control users passwords. The scripts that this guide use will move also Grid Control users but it recreates those users passwords. After move is done Grid Control console users passwords are same as the usernames except all passwords are writen with caps.

If you want to change passwords back like they where in old database you can do it this way:

Check username password hash from old Oracle 11 repository database with following sql (older Oracle version did show password hash in the dba_users password column but it is not anymore there in Oracle 11 (this is because security reasons)):
--
select REGEXP_SUBSTR (DBMS_METADATA.get_ddl ('USER','USERNAME'), '''[^'']+''') PASSWD from dual;
-- 

and after that change user password into new repository database with following sql (and use the hash you get from the first sql):
--
alter user USERNAME identified by values 'S:91F217F4280C854E10D66C2C79E729C55B45E26DAD30297D8705542EF5B5;45BE3895069161E2';
--

You must run above scripts for all Grid Control console users to change their passwords.

And of course you can use same sql's for another Oracle 11 password changes where you need for old password. For example if you need to use older exp/imp tools and you need to create users into new database before import.

Oracle Grid Control database licences

Oracle Grid Control needs Oracle EE version database.
Oracle Grid Control 10g and earlier versions did contain embedded EE version database.
But after that there is no more embedded database included in installation packages. But Oracle let you install single instance Oracle EE database freely for Grid Control use only. So the good news is that you still don't need to buy EE license to run newer Grid Control versions to monitoring your Oracle databases.