Calling-DBA

Copying/Moving a datafile from one diskgroup to another diskgroup

2010-03-15T12:16:00.001+05:30

Steps involved in moving/copying a datafile from one diskgroup to another diskgroup

1) Use rman to copy the datafile to another diskgroup.
2) Offline the datafile
3) Switch the datafile to the copy
4) Recover the datafile
5) Online the datafile

RMAN> report schema;

Report of database schema
List of Permanent Datafiles
===========================

File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------

1 480 SYSTEM *** +DG1/racdb/datafile/system.261.713375891

2 200 UNDOTBS1 *** +DG1/racdb/datafile/undotbs1.516.713375845

3 620 SYSAUX *** +DG1/racdb/datafile/sysaux.517.713375779

4 200 UNDOTBS2 *** +DG1/racdb/datafile/undotbs2.515.713375845

5 5 USERS *** +DG1/racdb/datafile/users.262.713375891

6 3 TS1 *** +DG1/racdb/datafile/ts1.450.713706851-----moving this file to other diskgroup 'FRA'

List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------

1 29 TEMP 32767 +DG1/racdb/tempfile/temp.264.708103309

1) Connect to rman and copy the datafile .

Here i am moving the datafile 6 from diskgroup DG1 to diskgroup FRA

RMAN> copy datafile 6 to '+FRA';

Starting backup at 15-MAR-10

allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=121 instance=RACDB1 devtype=DISK
channel ORA_DISK_1: starting datafile copy
input datafile fno=00006 name=+DG1/racdb/datafile/ts1.450.713706851
output filename=+FRA/racdb/datafile/ts1.257.713707167 tag=TAG20100315T115925 recid=13 stamp=713707167
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 15-MAR-10
Starting Control File and SPFILE Autobackup at 15-MAR-10
piece handle=/u01/app/oracle/rman/cont_auto_c-705719985-20100315-04 comment=NONE
Finished Control File and SPFILE Autobackup at 15-MAR-10

2) Take the datafile which you want to move to offline state

RMAN> sql "alter database datafile 6 offline";

3) Switch the datafile to copy; It is same as issuing "alter database rename file statement"

RMAN> switch datafile 6 to copy;

datafile 6 switched to datafile copy "+FRA/racdb/datafile/ts1.257.713707167"

4) Recover the datafile 6

RMAN> recover datafile 6;

Starting recover at 15-MAR-10
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:00:04
Finished recover at 15-MAR-10

5) Online the datafile 6

RMAN> sql "alter database datafile 6 online";

RMAN> report schema;

Report of database schema
List of Permanent Datafiles

===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------

1 480 SYSTEM *** +DG1/racdb/datafile/system.261.713375891

2 200 UNDOTBS1 *** +DG1/racdb/datafile/undotbs1.516.713375845

3 620 SYSAUX *** +DG1/racdb/datafile/sysaux.517.713375779

4 200 UNDOTBS2 *** +DG1/racdb/datafile/undotbs2.515.713375845

5 5 USERS *** +DG1/racdb/datafile/users.262.713375891

6 3 TS1 *** +FRA/racdb/datafile/ts1.257.713707167 ----moved from '+DG1' to '+FRA' diskgroup.

List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------

1 29 TEMP 32767 +DG1/racdb/tempfile/temp.264.708103309

Installing Oracle 11g R2 Grid Infrastructure

2010-03-11T17:58:00.003+05:30

Steps for installing 11g R2 grid infrastructure

I have installed 11g R2 grid infrastructure for cluster on OEL 5 using vmware server 2.0.

1) Here i am going with grid infrastructure for cluster

2) Going with advanced installation

3) Select the language

4) SCAN - Single Client Access Name
Oracle 11g R2 introduced new concept called Single Client Access Name (SCAN).

It eliminates the need to change TNS Entry when nodes are added to or removed from the Cluster and also creates the location independence for the clients.

Here i am adding a scan vip entry in the /etc/hosts file. This vip should not be accessible

SCAN IP, VIP and PUBLIC IP should be on the same subnet.

5) Here since we are not going with GNS for the vip, so manually feeding the vip info for both the nodes.

6) Here ssh is checking for the connectivity.

7) Providing the interfaces for public and private ip

8) From 11g R2 we don't have the option of going with raw devices or OCFS for OCR and VD. We have to use either ASM diskgroup or some clustered file system

9) Creating diskgroup for keeping OCR and Voting Disk

Selecting all the 3 disks for the diskgroup

10) Setting the password

11)

12)Created 3 OS groups and assinging groups for os authentication to ASM

13)Providing the orainventory location and location for grid installation

14) Checking for pre-reqs

Due to insufficient memory it is throwing messages, but ignoring those messages and proceeding. The minimum memory required for 11g R2 is 1.5G

15) After the binaries are installed on to both the nodes it will propmt you to execute 2 scripts

a)oranstRoot.sh
b)root.sh

Execute both scripts on each node one after the other.

After executing the remaining configuration installations will be done.

After this the installation of grid infrastructure is completed.

How to Change Public IP,Private IP and VIP in Oracle 10g RAC on Linux

2010-01-11T18:15:00.023+05:30

Changing the IP at OS level without updating the oracle cluster will crash the cluster.

Hence below are steps which provides the process of changing the IP on Cluster nodes.

Here i am using 2 node cluster on RHEL AS-4.

1) Shut down all the services like database,ASM,nodeapps on the both the nodes.

Here in my case only cluster services are running

2)Change the Public IP and Private IPin clusterware. Fire the statement from the node from which you have installed clusterware.

Here my previous IP was 172.26.0.0 series and i am changing it to 172.25.0.0 series

3) Change the Virtual IP

Change the VIP as it needs to be in the same subnet as of Public IP

My VIP was 172.26.16.61 on node2 and i am changing it to 172.25.16.61. Similarly for node1

4) Shut down the cluster on all nodes

node1 #]crsctl stop crs
node2 #]crsctl stop crs

5) Now change the IP at OS level and change the /etc/hosts file on both nodes

Changing from 172.26.0.0 series to 172.25.0.0 series.

6) After performing all the above steps reboot both the nodes.

Find the patches applied to your database

2010-01-05T15:44:00.005+05:30

SQL> select ACTION_TIME,ACTION,VERSION,ID,COMMENTS from registry$history;

ACTION_TIME ACTION VERSION ID COMMENTS
-------------------- -------------- ---------- ---------- --------------------
13-NOV-09 05.54.51.9 UPGRADE 10.2.0.4.0 Upgraded from 10.2.0
01116 PM .1.0

The above query will display the series of upgrades/downgrades done on your database.

(OR)

$ORACLE_HOME/OPatch/opatch lsinventory

Modifying the default gateway address used by the Oracle 10g VIP

2010-01-05T14:37:00.000+05:30

By default, the server's default gateway is used as a ping target during the Oracle RAC 10g VIP status check action. Upon a ping failure, Oracle will decide that the current interface where the VIP is running has failed, and will initiate an interface / internode VIP failover.

Though the VIP check action will work as designed in most situations, it will not function
correctly as designed under special circumstances where the server's default gateway resides on
a different network from the client LAN network (the network where the VIP is configured on).
In order for the VIP check action to function as designed, the ping target address needs to be
modified after installing RAC 10g.

o Example of a network configuration where use of the default gateway is sufficient
Since the VIP, clients and default gateway are all on the same network segment, the VIP
check action will function correctly in the above case.

o Example of a network configuration where the ping target needs to be modified

Since the default gateway is configured on a different network segment from the VIP/Clients,the VIP check action will not work correctly as designed in the above configuration.

Since the VIP check action will use the default gateway (192.168.1.1) as the ping target,and Oracle will not be able to detect failures in the client network (146.56.20.X) as expected.

In this case, the ping target used by the VIP needs to be modified to an IP address in the 146.56.20.X network segment in order for the check action to function correctly as designed.

The target address needs to remain static and highly available, as VIP availability directly leads to service / instance availability. In order to achieve high availability for the ping target, make sure to use redundant links / hardware for the host / router to be used as the target.

Modifying the ping target for the Oracle 10g VIP
----------------------------------------------------------
The following steps need to be performed on every node within the cluster.

1. Stop all node applications.

% srvctl stop instance -d -i
% srvctl stop asm -n
% srvctl stop nodeapps -n

2. As root, modify the following script and change the value of DEFAULTGW variable.
# vi $ORA_CRS_HOME/bin/racgvip

* Examples of modifying the ping target

BEFORE)
DEFAULTGW=

AFTER)
DEFAULTGW=146.56.20.1

3. Start the node applications and other necessary resources.
% srvctl start nodeapps -n
% srvctl start asm -n
% srvctl start instance -d -i

Make sure to repeat these steps for all CRS homes within the cluster.

References: Metalink