How to move a datafile from a ASM disk group to another

I'll explain two ways of moving a datafile from a ASM disk group to another disk group. As you might guess, during migration of the data file you need to take whether the whole tablespace or just the the datafile offline, which makes the difference between two approaches I'm about to explain.

Here is the first way:

1. Find the name of the datafile that you're going to migrate:

SQL> SELECT FILE_NAME FROM DBA_DATA_FILES WHERE tablespace_name = 'EXAMPLE';

2. Take the tablespace offline:

SQL> alter tablespace example offline;

3. Copy the file using RMAN:

RMAN> COPY datafile '+RECOVERY/orcl/datafile/example.123.123456789' to '+DATA';

4. Change datafile's name to the name displayed as output of the command above:    

SQL> ALTER DATABASE RENAME FILE '<old_name>' TO '<output file name written at rman output>';

5. Bring back the tablespace online:


SQL> alter tablespace example online;

And here is the second way:

1. Find the name of the datafile that you're going to migrate just like firt method:

SQL> SELECT FILE_NAME FROM DBA_DATA_FILES WHERE tablespace_name = 'EXAMPLE';

2. Take the datafile offline instead this time:

SQL> ALTER DATABASE DATAFILE '+RECOVERY/orcl/datafile/example.123.123456789' OFFLINE;

3. Copy the file using RMAN:

RMAN> COPY datafile '+RECOVERY/orcl/datafile/example.123.123456789' to '+DATA';

4. Change datafile's name to the new name displayed as output of the command above to update data dictionary: 

SQL> ALTER DATABASE RENAME FILE '<old_name> TO '<new_name>';

5. Rename the datafile using RMAN to update ASM. This command is equivalent of "alter database rename file":   

RMAN> SWITCH DATAFILE '<new_name>' TO COPY;

6. Recover  the new datafile:

RMAN> RECOVER DATAFILE '<new_name>';

7. Bring it online

SQL> ALTER DATABASE DATAFILE '<new_name>' ONLINE;

Both ways do the job. To choose between them is deciding whether tablespace can be taken offline for while or not.

How to Install/Update Java (JDK) on Oracle Linux

1. Download JDK RPM from Oracle

2. As root:

$ chmod +x jdk-6u30-linux-i586-rpm.bin
$ ./jdk-6u30-linux-i586-rpm.bin. 

3. Re-link to new JDK:

$ /usr/sbin/alternatives --install /usr/bin/java java /usr/java/jdk1.6.0_30/bin/java 16030

or alternatively:

$ /usr/sbin/alternatives --config java

Deinstalling Old Oracle Homes After Upgrade

When Oracle Database and Grid Infrastructure (GI) is updated as Oracle recommended which is out-of-place upgrade, a new Oracle home is created. And a new home means extra disk space. Since no disk has unlimited capacity after a few or many upgrades you need to clean up.

So, assuming we are performing clean up on a single instance with ASM configured and  homes to be uninstalled is under /u01/app/oracle/product/11.2.0 let's start with db home:

$ cd /u01/app/oracle/product/11.2.0/dbhome_1/deinstall
$ ./deinstall

Deinstall script discovers the configuration. Notice that no database names that are configured in this Oracle home is found. Accept default answers and say yes to continue:

Oracle Home selected for de-install is: /u01/app/oracle/product/11.2.0/dbhome_1
Inventory Location where the Oracle home registered is: /u01/app/oraInventory
No Enterprise Manager configuration to be updated for any database(s)
No Enterprise Manager ASM targets to update
No Enterprise Manager listener targets to migrate
Checking the config status for CCR
Oracle Home exists with CCR directory, but CCR is not configured
CCR check is finished
Do you want to continue (y - yes, n - no)? [n]: y

Deinstall tool does the cleanup smoothly.

Next step is uninstalling GI home which is a bit more tricky. According to Oracle documentation you need to change the permissions of the directory first as root:

$ cd /u01/app/oracle/product/11.2.0
$ chown -R oracle:oinstall grid
$ chmod -R 775 grid

GI also has deinstall tool in its home. However if you run it as you do for database, you see the following error though no error logged in the log file.

ERROR: The deconfiguration and deinstallation tool has detected runtime errors when checking the existing configuration due to which the tool cannot continue with clean up operation.  Please check the log files for more information.  Rerun the tool after fixing the errors to proceed with the ORACLE_HOME clean up.

Don't give up deinstall tool yet. Unzip the 7th of the installation zip files you've downloaded which is the deinstall tool and run it from the unzip directory by giving the home parameter:

$ ./deinstall -home /u01/app/oracle/product/11.2.0/grid

Oracle Grid Infrastructure Home is: /u01/app/oracle/product/11.2.0.2/grid
The cluster node(s) on which the Oracle home de-installation will be performed are:null
Oracle Home selected for de-install is: /u01/app/oracle/product/11.2.0/grid
Inventory Location where the Oracle home registered is: /u01/app/oraInventory
Skipping Windows and .NET products configuration check
The home being deconfigured is NOT a configured Grid Infrastructure home (/u01/app/oracle/product/11.2.0.2/grid)
ASM was not detected in the Oracle Home
Do you want to continue (y - yes, n - no)? [n]: y

Again say yes to cleanup. When it finishes, you gain ~10GB of your disk space back.

Upgrading Single Instance Oracle Database 11.2.0.1 to 11.2.0.2

Today I'll upgrade my single instance database from 11.2.0.1 to 11.2.0.2. As you know patch sets are full installations so procedure will  be like installing for the very first time except choosing upgrade options.

My database takes advantage of ASM. Therefore we'll start with upgrading Grid Infrastructure. Unzip the 3. of the installation zip files to an appropriate directory such as /tmp. Since 11gR2, upgrades must be done as "out-of-place upgrades". So create the directories for grid and database upgrades.

$ mkdir -p /u01/app/oracle/product/11.2.0.2/grid
$ mkdir -p /u01/app/oracle/product/11.2.0.2/dbhome_1

If you don't show an empty home to installer you get the following error:

I will use graphical interfaces. To use UI, you can take a look at my blog about displaying x client on Oracle Linux. Run the installer:

$ ./runInstaller
Skip software updates.
 Select upgrade option.

Select language(s)
Leave groups as default

Select the new directory as software location. After prerequisite check (nothing came up hopefully)  installation will begin and you will be prompted to run root script:

Output of the root script should be like the following:


Grid Infrastructure upgrade is completed when the script is finished. You can check the new version by:

$ cd /u01/app/oracle/product/11.2.0.2/grid/bin/
$ ./crsctl query has softwareversion
Oracle High Availability Services version on the local node is [11.2.0.2.0]

Next step is upgrading the database. Unzip the first and the second of the installation zip files and run the installer.


Skip software updates.
Select upgrade option.
Select language(s)

Select the edition you  want to install.
Select the new directory as software location.
Leave groups as default
 After prerequisite check (nothing came up hopefully)  installation will begin and you will be prompted to run root script:
Output of the root script should be like the following:

When you click "OK" when the script succeeds, Database Upgrade Assistant (DBUA) automatically starts.

 Select the database to upgrade.
 
If Oracle finds out any warnings, you are prompted to correct them. You can run the recommended commands as fix and keep going.
Select an appropriate number of parallelism and turn off archiving otherwise some amount of redo log will be generated.
You don't want to move database files so leave as default
Select ASM disk group for FRA if not discovered
A summary report is generated.
Upgrade process when you click on finish.
Upgrade may take quite some while depend on your configuration.

At the end a result report  is generated.

The whole procedure is pretty much straight forward and goes smoothly.

How to change password of users sysman and dbsnmp for DB Console

For security reasons or you just forget about password expiration and users got locked, you may need to change passwords of users: sysman and dbsnmp. It is not as easy as changing as user's password, but it's so hard either. Here are the steps:

1. Logon to your server as software owner, probably oracle

2. If ORACLE_UNQNAME is not set properly:


$ export ORACLE_UNQNAME = <db_name>

3. Stop DB Console

$ emctl stop dbconsole

4. Open SQLPlus as sysdba 

SQL> conn / as sysdba
SQL> alter user sysman identified by new_password;
SQL> alter user dbsnmp identified by new_password;

If user(s) got locked:

SQL> alter user sysman account unlock;
SQL> alter user dbsnmp account unlock;

5. To change sysman's password for DB Console edit config file:

If it is a RAC environment
$ cd /u01/app/oracle/product/11.2.0/dbhome_1/<node_name>_<db_name>/sysman/config/

If it is a single instance:
$ cd /u01/app/oracle/product/11.2.0/dbhome_1/sysman/config

Open emoms.properties file with your favorite text editor and change the lines:
orcle.sysman.eml.mntr.emdRepPwd=newplaintextpassword
orcle.sysman.eml.mntr.emdRepPwdEncrypted=FALSE (Change it from TRUE to FALSE)

Don't worry because of writing password in plain text to a file, all the password you've written down will be encrypted and the property emdRepPwdEncrypted will turn into TRUE again when you start DB Console.

6. To change DBSNMP's password:

If it is a RAC environment
$ cd /u01/app/oracle/product/11.2.0/dbhome_1/<node_name>_<db_name>/sysman/emd/

If it is a single instance:
$ cd /u01/app/oracle/product/11.2.0/dbhome_1/sysman/emd

Edit targets.xml file for every appearance of statement below, except the one for ASM.
Change encrypted from TRUE to FALSE
    <property name="password" value="new_password" encrypted="FALSE"> 

It's the same case for plain text and encryption  just like previous step.

7. Start DB Console:

$ emctl start dbconsole

8. You can check the files you edited to see passwords got encrypted.

Backing up Exadata Storage Cell

As a DMA, you'd like to backup Stroge Cells as well as a part of your backup process. Though, when it comes to Cell backup necessary things to be backed up are pretty much limited. Because Exadata does most of the work automatically for you using an embedded USB called CELLBOOT USB flash drive. Latest successfully working system image is stored in CELLBOOT USB. You can find versions of both active system and CELLBOOT USB by running the command as seen below:

$ imageinfo

Kernel version: 2.6.18-194.3.1.0.4.el5 #1 SMP Sat Feb 19 03:38:37 EST 2011 x86_64
Cell version: OSS_11.2.0.3.0_LINUX.X64_110520
Cell rpm version: cell-11.2.2.3.2_LINUX.X64_110520-1

Active image version: 11.2.2.3.2.110520
Active image activated: 2011-06-24 00:04:28 -0700
Active image status: success
Active system partition on device: /dev/md5
Active software partition on device: /dev/md7

In partition rollback: Impossible

Cell boot usb partition: /dev/sdm1
Cell boot usb version: 11.2.2.3.2.110520

Inactive image version: undefined
Rollback to the inactive partitions: Impossible

What is left for you to back up is just a couple files listed below:

1. /etc/hosts
2. /etc/modprobe.conf
3. /etc/sysconfig/network
4. /etc/sysconfig/network-scripts/*

Exadata also provides a way for you to create your own CELLBOOT USB. All you have to is plug-in a USB disk which should be at least 1 GB and run the tool:

$ /opt/oracle.SupportTools/make_cellboot_usb

Exadata turns your USB disk into a bootable disk of active image of your system.

How to Display X Client on Oracle Linux to Run DBCA

As a best practice, Oracle recommends creating new databases by using atabase Configuration Assistant (DBCA) tool. Since DBCA is a graphical interface and if your Oracle runs on an Oracle Linux installation without a desktop environment what you need to do is explained right below:

1. Install Xming on your Windows desktop. During the installation select the "Don't install an SSH Client" radio button.
2. Run Xming
3. Assuming that you're using Putty, click "Enable X11 forwarding" and set "X Display Location" to "localhost:0" as displayed below:



After you logged on to your server you can run DBCA and a new window will be opened on your desktop.

RMAN-03009 and ORA-00245 on 11g R2 RAC

Time  to time you may see your backup job failed with RMAN-03009 and ORA-00245 errors just like below in your backup reports:

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of backup command on x channel at <sometime>

exit;
ORA-00245: control file backup operation failed

As described in MOS Doc [ID 1268725.1] starting from 11.2.0.2, RMAN fails on RAC when doing controlfile autobackup if RMAN configuration for snapshot controlfile is set to default value which is ORACLE_HOME/dbs/snapcf_<dbName><nodeId>.f. This is because of the change in 11.2.0.2 that any of the nodes in the cluster can write to the snapshot controlfile. Therefore, all nodes need access to snapshot file which comes to solution that in a RAC environment snapshot controlfile must be kept in ASM (I assume you use ASM, otherwise any shared NFS or such).

So, what you need to do is:

1. First of all, check if it is really the case by :

RMAN> SHOW ALL ;

2. If it is, configure RMAN to use a shared resource by:

RMAN> CONFIGURE SNAPSHOT CONTROLFILE NAME TO '+RECOVERY/DB1/snapcf_db1.f';

That's it.