Wednesday, April 28, 2010

Public YUM Server

Oracle has a public yum server which offers a free and convenient way to install packages from the Enterprise Linux and Oracle VM installation media via a yum client.

Oracle Enterprise Linux 4, Update 6 or Newer

# cd /etc/yum.repos.d
# mv Oracle-Base.repo Oracle-Base.repo.disabled
# wget http://public-yum.oracle.com/public-yum-el4.repo

Oracle Enterprise Linux 5

# cd /etc/yum.repos.d
# wget http://public-yum.oracle.com/public-yum-el5.repo

Oracle Enterprise Linux 6

# cd /etc/yum.repos.d
# wget http://public-yum.oracle.com/public-yum-ol6.repo

Oracle VM 2

# cd /etc/yum.repos.d
# wget http://public-yum.oracle.com/public-yum-ovm2.repo

Enable the appropriate repository by editing the yum configuration file

* Open the yum configuration file in a text editor
* Locate the section in the file for the repository you plan to update from, e.g. [el4_u6_base]
* Change enabled=0 to enabled=1

Test with
yum list


Cloning/Duplicating controlfile between ASM diskgroups

Assume the following situation where there's only one controlfile for the whole DB.
SQL> show parameter control

NAME TYPE VALUE
------------- ------- ------------------------------
control_files string +FLASH/rac11g/controlfile/current.263.714060123

For obvious reasons it's not a good idea to run a DB with just one controlfile. To clone/duplicate the current controlfile to another diskgroup use the following steps.
Edit the spfile by specifying the new location of the controlfile
SQL> alter system set control_files='+FLASH/rac11g/controlfile/current.263.714060123',
'+DATA' scope=spfile sid='*';

Shutdown cleanly
SQL> shutdown immediate;
and
SQL> startup nomount
Use RMAN to restore the current controlfile to new location
RMAN> restore controlfile from '+FLASH/rac11g/controlfile/current.263.714060123';

Starting restore at 29-Apr-2010 00:32:33
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=141 instance=rac11g1 device type=DISK

channel ORA_DISK_1: copied control file copy
output file name=+FLASH/rac11g/controlfile/current.263.714060123
output file name=+DATA/rac11g/controlfile/current.393.717553955
Finished restore at 29-Apr-2010 00:32:38
Start the DB and view spfile has the full location of the new controlfile
SQL> alter database mount;
SQL> show parameter control
NAME TYPE VALUE
------------- ------ ------------------------------
control_files string +FLASH/rac11g/controlfile/current.263.714060123,
+DATA/rac11g/controlfile/current.393.717553955
SQL> alter database open;


Replacing spfile in ASM

In a database that is residing in a file system it is easy to replace the spfile with
create spfile from pfile
when the database is started with a pfile. But note the following when replacing a spfile that resides in ASM.

When the DB is created the spfile inside ASM is as below. spfile.260.714060565 is the exact spfile and spfilerac11g.ora is alias refering it

ASMCMD> ls -l
Type Redund Striped Time Sys Name
Y ARCHIVELOG/
Y CONTROLFILE/
Y DATAFILE/
Y ONLINELOG/
Y PARAMETERFILE/
Y TEMPFILE/
N spfilerac11g.ora => +DATA/RAC11G/PARAMETERFILE/spfile.260.714060565
Now create a pfile and start the DB using it
SQL> startup nomount pfile=pfile.ora
ORACLE instance started.

Total System Global Area 626327552 bytes
Fixed Size 2162280 bytes
Variable Size 385876376 bytes
Database Buffers 230686720 bytes
Redo Buffers 7602176 bytes

Create the spfile with the usual command try to start the DB

SQL> create spfile='+DATA' from pfile;

File created.

SQL> startup force;
The startup command will not return. Looking inside the ASM you will see the following

ASMCMD> ls -l
Type Redund Striped Time Sys Name
Y ARCHIVELOG/
Y CONTROLFILE/
Y DATAFILE/
Y ONLINELOG/
Y PARAMETERFILE/
Y TEMPFILE/
N spfilerac11g.ora => +DATA/RAC11G/PARAMETERFILE/spfile.260.714060565
ASMCMD> ls -l parameterfile
Type Redund Striped Time Sys Name
PARAMETERFILE UNPROT COARSE APR 28 23:00:00 Y spfile.260.714060565
PARAMETERFILE UNPROT COARSE APR 29 00:00:00 Y spfile.393.717552031

The alias is still refering the old spfile. Rectifiy this as follows

ASMCMD> rmalias spfilerac11g.ora
ASMCMD> mkalias +DATA/RAC11G/PARAMETERFILE/spfile.393.717552031 spfilerac11g.ora
ASMCMD> ls -l
Type Redund Striped Time Sys Name
Y ARCHIVELOG/
Y CONTROLFILE/
Y DATAFILE/
Y ONLINELOG/
Y PARAMETERFILE/
Y TEMPFILE/
N spfilerac11g.ora => +DATA/RAC11G/PARAMETERFILE/spfile.393.717552031

Try starting the DB and it would still hang. Problem lies how the spfile was created. When creating the spfile specify the full path of the creating pfile
create spfile='+DATA' from pfile='/home/oracle/asanga/pfile.ora';

Replace alias and start the DB

ASMCMD> ls parameterfile
spfile.260.714060565
spfile.393.717552031
spfile.394.717553085
ASMCMD> rmalias spfilerac11g.ora
ASMCMD> mkalias +DATA/RAC11G/PARAMETERFILE/spfile.394.717553085 spfilerac11g.ora
ASMCMD> ls -l
Type Redund Striped Time Sys Name
Y ARCHIVELOG/
Y CONTROLFILE/
Y DATAFILE/
Y ONLINELOG/
Y PARAMETERFILE/
Y TEMPFILE/
N spfilerac11g.ora => +DATA/RAC11G/PARAMETERFILE/spfile.394.717553085

SQL> startup force;
ORACLE instance started.

Total System Global Area 626327552 bytes
Fixed Size 2162280 bytes
Variable Size 385876376 bytes
Database Buffers 230686720 bytes
Redo Buffers 7602176 bytes
Database mounted.
Database opened.