Saturday, 18 February 2012

MOVING SPFILE FOR RAC DB IN ASM

$ sqlplus / as sysdba
SQL> create pfile='/home/oracle/pfile_Raju1.ora' from spfile;
File created.

SQL> create spfile='+RAJU1' from pfile='/home/oracle/pfile_zp1.ora';
File created.

you have to find the file name details in the asmcmd
After finding the name, you have to change to the database

$ srvctl modify database -d RAJU -p +RAJU1/RAJU/PARAMETERFILE/spfile.272.762954069
STOP AND START THE RAC DATASBASE
CHECK THE SPFILE LOCATION IN ALL NODES

SQL> show parameter spfile

NAME        TYPE        VALUE
--------- ----------- ------------------------------
spfile      string      +RAJU1/RAJU/parameterfile/spfile.272.762954069
Note: Remove your old spfile using rm command or ALTER DISKGROUP DISK_GROUP_NAME DROP FILE ‘OLD_LOCATION’;

Thursday, 16 February 2012

How to ADD/REMOVE/REPLACE/MOVE Oracle Cluster Registry (OCR) and Voting Disk

ADD/REMOVE/REPLACE OCR Device
Note: You must be logged in as the root user, because root own the OCR files.
Make sure there is a recent copy of the OCR file before making any changes:
ocrconfig –showbackup
If there is not a recent backup copy of the OCR file, an export can be taken
for the current OCR file. Use the following command to generate a an export of
the online OCR file:
ocrconfig –export -s online
If you need to recover using this file, the following command can be used:
ocrconfig import
1. To add an OCR device:
To add an OCR device, provide the full path including file name.
ocrconfig -replace ocr
To add an OCR mirror device, provide the full path including file name.
ocrconfig -replace ocrmirror
2. To remove an OCR device:
To remove an OCR device:
ocrconfig -replace ocr
To remove an OCR mirror device
ocrconfig -replace ocrmirror
3.Replace or move the location of an OCR 
To replace the OCR device with , provide the full path including file
name.
ocrconfig -replace ocr
To replace the OCR mirror device with , provide the full path
including file name.
ocrconfig -replace ocrmirror 

Moving OCR file from OCFS to raw devices
The OCR disk must be owned by root, must be in the oinstall group, and must have permissions set
to 640. Provide at least 100 MB disk space for the OCR.
In this example the OCR file are located in the ocfs2 file system:
/ocfs2/ocr1
/ocfs2/ocr2
Create raw device files of at least 100 MB. In this example the new OCR file
will be on the following devices:
/dev/raw/raw1
/dev/raw/raw2
Once the raw devices are created, use the dd command to zero out the device and
make sure no data is written to the raw devices:
dd if=/dev/zero of=/dev/raw/raw1
dd if=/dev/zero of=/dev/raw/raw2

Now we are ready to move/replace the OCR file to the new storage location.
Move/Replace the OCR device
ocrconfig -replace ocr /dev/raw/raw1
Add /dev/raw/raw2 as OCR mirror device
ocrconfig -replace ocr /dev/raw/raw2

Example of adding an OCR device file
If you have upgraded your environment from a previous version, where you only
had 1 OCR device file, you can use the following step to add an additional OCR
file.
In this example a second OCR device file is added:
Add /dev/raw/raw2 as OCR mirror device
ocrconfig -replace ocr /dev/raw/raw2

ADD/DELETE/MOVE Voting Disk
Note: Shutdown the Oracle Clusterware (crsctl stop crs as root) on all nodes
before making any modification to the voting disk. Determine the current
voting disk location using:
crsctl query css votedisk
Take a backup of all voting disk:
dd if=voting_disk_name of=backup_file_name
Note: Use UNIX man pages for additional information on the dd command.
The following can be used to restore the voting disk from the backup file
created.
dd if=backup_file_name of=voting_disk_name
1. To add a Voting Disk, provide the full path including file name.:
crsctl add votedisk css -force
2. To delete a Voting Disk, provide the full path including file name.:
crsctl delete votedisk css -force
3. To move a Voting Disk, provide the full path including file name.:
crsctl delete votedisk css –force
crsctl add votedisk css –force

After modifying the voting disk, start the Oracle Clusterware stack on all
nodes
crsctl start crs
Verify the voting disk location using
crsctl query css votedisk
Example moving Voting Disk from OCFS to raw devices
The voting disk is a partition that Oracle Clusterware uses to verify cluster
node membership and status.
The voting disk must be owned by the oracle user, must be in the dba
group, and must have permissions set to 644. Provide at least 20 MB disk
space for the voting disk.
In this example the Voting Disks are located in the ocfs2 file system:
/ocfs2/voting1
/ocfs2/voting2
/ocfs2/voting3
Create raw device files of at least 20 MB. In this example the new voting
disks will be on the following devices:
/dev/raw/raw3
/dev/raw/raw4
/dev/raw/raw5
Once the raw devices are created, use the dd command to zero out the device and
make sure no data is written to the raw devices:
dd if=/dev/zero of=/dev/raw/raw3
dd if=/dev/zero of=/dev/raw/raw4
dd if=/dev/zero of=/dev/raw/raw5
Note: Use UNIX man pages for additional information on the dd command.
Now you are ready to move/replace the voting disks to the new storage location.
To move a Voting Disk to new storage location:
crsctl delete votedisk css /ocfs2/voting1 –force
crsctl add votedisk css /dev/raw/raw3 –force
crsctl delete votedisk css /ocfs2/voting2 –force
crsctl add votedisk css /dev/raw/raw4 –force
crsctl delete votedisk css /ocfs2/voting3 –force
crsctl add votedisk css /dev/raw/raw5 –force

Adding Voting Disks
If you have upgraded your environment from a previous version, where you only
had 1 voting disk, you can use the following steps to add additional voting
disk.
In this example 2 additional Voting Disks are added:
crsctl add votedisk css /dev/raw/raw4 –force
crsctl add votedisk css /dev/raw/raw5 –force

After modifying the voting disk, start the Oracle Clusterware stack on all nodes
crsctl start crs
Verify the voting disk location using
crsctl query css votedisk

Migrate db files from nfs to ASM filesystem

Configure flash recovery area
SQL> connect sys/sys@prod1 as sysdba
Connected.
SQL> alter database disable block change tracking;
Database altered.
SQL> alter system set db_recovery_file_dest_size=500m;
System altered.
SQL> alter system set db_recovery_file_dest=’+RECOVERYDEST’;
System altered


Migrate control file to ASM
Use RMAN to migrate the data files to ASM disk groups.
All datafiles will be migrated to newly created disk group DATA

SQL> alter system set db_create_file_dest='+DATA01';
System altered.
SQL> alter system set control_files='+DATA01/control01.dbf'
scope=spfile;
System altered.
SQL> shu immediate
[oracle@rac1 bin]$ ./rman target /
RMAN> startup nomount
Oracle instance started
RMAN> restore controlfile from
'/u01/new/oracle/oradata/rajudb/control01.ctl';
Starting restore at 18-JAN-10
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=146 device type=DISK
channel ORA_DISK_1: copied control file copy
output file name=+DATA01/control01.dbf
Finished restore at 18-JAN-10
RMAN> alter database mount;
database mounted
released channel: ORA_DISK_1

Migrate data files to ASM
RMAN> backup as copy database format '+DATA01';
Starting backup at 18-JAN-10
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=146 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001
name=/u01/new/oracle/oradata/rajudb/system01.dbf
output file name=+DATA01/rajudb/datafile/system.257.705063763
tag=TAG20091208T110241 RECID=1 STAMP=705064274
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:08:39
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002
name=/u01/new/oracle/oradata/rajudb/sysaux01.dbf
output file name=+DATA01/rajudb/datafile/sysaux.258.705064283
tag=TAG20091208T110241 RECID=2 STAMP=705064812

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:08:56
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003
name=/u01/new/oracle/oradata/rajudb/undotbs01.dbf

output file name=+DATA01/rajudb/datafile/undotbs1.259.705064821
tag=TAG20091208T110241 RECID=3 STAMP=705064897

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:25
channel ORA_DISK_1: starting datafile copy

copying current control file
output file name=+DATA01/rajudb/controlfile/backup.260.705064907
tag=TAG20091208T110241 RECID=4 STAMP=705064912

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile copy

input datafile file number=00004
name=/u01/new/oracle/oradata/rajudb/users01.dbf

output file name=+DATA01/rajudb/datafile/users.261.705064915
tag=TAG20091208T110241 RECID=5 STAMP=705064915

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set

including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 18-JAN-10
channel ORA_DISK_1: finished piece 1 at 18-JAN-10

piece handle=+DATA01/rajudb/backupset/2010_01_18/nnsnf0_tag29.262.7050
tag=TAG20091208T110241 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 18-JAN-10

RMAN> switch database to copy;
datafile 1 switched to datafile copy
"+DATA01/rajudb/datafile/system.257.705063763"
datafile 2 switched to datafile copy
"+DATA01/rajudb/datafile/sysaux.258.705064283"
datafile 3 switched to datafile copy
"+DATA01/rajudb/datafile/undotbs1.259.705064821"
datafile 4 switched to datafile copy
"+DATA01/rajudb/datafile/users.261.705064915"

RMAN> alter database open;
database opened
RMAN> exit
Recovery Manager complete.

SQL> conn sys/oracle as sysdba
Connected.
SQL> select tablespace_name,file_name from dba_data_files;
TABLESPACE_NAME      FILE_NAME
------------------ ---------------------------------------------
USERS                +DATA01/rajudb/datafile/users.261.705064915
UNDOTBS1             +DATA01/rajudb/datafile/undotbs1.259.705064821
SYSAUX               +DATA01/rajudb/datafile/sysaux.258.705064283
SYSTEM               +DATA01/rajudb/datafile/system.257.705063763
SQL> select name from v$controlfile;
NAME
----
+DATA01/control01.dbf

Migrate redo logs to ASM.
SQL> select member,group# from v$logfile;
MEMBER                                                 GROUP#
-------------------------------------------------- ----------
/u01/new/oracle/oradata/rajudb/redo03.log                     3
/u01/new/oracle/oradata/rajudb/redo02.log                     2
/u01/new/oracle/oradata/rajudb/redo01.log                     1

SQL> alter database add logfile group 4 size 5m;
Database altered.
SQL> alter database add logfile group 5 size 5m;
Database altered.
SQL> alter database add logfile group 6 size 5m;
Database altered.

SQL> select member,group# from v$logfile;
MEMBER                                         GROUP#
---------------------------------------------- -------
/u01/new/oracle/oradata/rajudb/redo03.log        3
/u01/new/oracle/oradata/rajudb/redo02.log        2
/u01/new/oracle/oradata/rajudb/redo01.log        1
+DATA01/rajudb/onlinelog/group_4.264.705065691   4
+DATA01/rajudb/onlinelog/group_5.265.705065703   5
+DATA01/rajudb/onlinelog/group_6.266.705065719   6

SQL> alter system switch logfile;
System altered.
SQL> alter database drop logfile group 2;
Database altered.
SQL> alter database drop logfile group 3;
Database altered.
SQL> alter database drop logfile group 4;
Database altered.
SQL> alter database drop logfile group 1;
Database altered.
Add additional control file
If an additional control file is required for redundancy,
you can create it in ASM as you would on any other filesystem.

SQL> connect sys/sys@prod1 as sysdba
Connected to an idle instance.
SQL> startup mount
ORACLE instance started.

SQL> alter database backup controlfile to '+DATA01/control02.dbf';
Database altered.
SQL> alter system set control_files='+DATA01/control01.dbf '
,'+DATA01/control02.dbf' scope=spfile;
System altered.

SQL> shutdown immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.

SQL> startup
ORACLE instance started.
SQL> select name from v$controlfile;
NAME
---------------------------------------
+DATA01/control01.dbf
Migrate temp tablespace to ASM
SQL> alter tablespace temp add tempfile size 100m;
Tablespace altered.
SQL> select file_name from dba_temp_files;
FILE_NAME
---------------------------------------------
+DATA01/rajudb/tempfile/temp.263.705065455

Else Create new temporary tablespace in ASM disk group.
SQL> CREATE TABLESPACE temp1 TEMPFILE ‘+DATA01’;
SQL> alter database default temporary tablespace temp1;
Database altered.