Column format/clear

sql>column column_name format A20

sql>column column_name clear

shell script/rman backup script

RUN
{
allocate channel c1 type disk format '/backup/pooo/rmanback/RMAN_%d_%u_%s_%p';
backup database include current controlfile;
release channel c1;
allocate channel c1 type disk
     format '/backup/pooo/rmanback/RMAN_%d_%u_%s_%p';
sql "alter system archive log current";
backup archivelog all delete input;
sql "alter database backup controlfile
     to ''/backup/pooo/rmanback/rman_control.rman'' reuse";
release channel c1;
}

above information save for   RMAN_backup.rcv format.. and create .sh file

export ORACLE_HOME=/ora1/pooo/db/tech_st/11.1.0
export ORACLE_SID=pooo
export PATH=$PATH:$ORACLE_HOME/bin

/ora1/pooo/db/tech_st/11.1.0/bin/./rman target /  msglog rman_backup_db_online.log CMDFILE RMAN_backup.rcv <<EOF
exit
EOF

the above information save for backupdb.sh format ... and run the script

#./backupdb.sh






 

Temporary/Undo tablespace referance

Temporary tablespace:

>create user mathan identified by mathan temporary tablespace temp1;

 Default temporary tablespace (no drop, no rename, no relocate) its only read only.

>create temporary tablespace temp2 tempfile  ‘/opt…../temp02.dbf’ size 100m;

>select property_name, property_value from database_properties where property_name=’DEFAULT_TEMPORARY_TABLESPACE’;

>alter database default temporary tablespace temp2;

Size check:

>select tablespace_name,(total_block *8)/1024,(used_blocks*8)/1024,current_users from v$sort_segments;

>select *from v$tempfile;

>select * from dba_temp_files;

Size Increases:

>alter tablespace temp add tempfile ‘/opt…./temp01.dbf’ size 100m;

Or

>alter database tempfile ‘/opt/…/tem01.dbf’ resize 200m;

Temporary tablespace group:

>create temporary tablespace temp1 temfile ‘/opt/…./temp1.dbf’ size 100m tablespace group tempgroup;

Add exiting temp to group:

>alter tablespace temp tablespace group tempgroup;

> alter tablespace temp1 tablespace group tempgroup;

>alter database default temporary tablespace tempgroup;

Drop member:

>alter tablespace temp tablespace group ‘’;

>drop tablespace temp;

Undo tablespace:

>create undo tablespace undotbs2 datafile ‘/opt/../undo2.dbf’ size 100m;

>show parameter undo_tablespace;
>alter system set undo_tablespace =undotbs2; 

/Advisor of undo size:

Undo tablespace size=transaction rate *block size *retention time/1024/1024

>slect max(undoblocks)/600 from v$undostat;

>show parameter unto_retention;

/

/10g:

>variable n number;

>begin

:n:=dbms_undo_adv.RBU_migration;

End;

/

>print n;   /

Data Pump

High Performance Oracle Database Export using Data Pump

Oracle offers high performance database not only in DDL and DML. Not only this that, but also in data backup and recovery. Oracle 10g offers new export/import utilities called data dump. The biggest advance of using data dump is it can push the system’s hardware capabilities to roof to achieve performance.

Oracle Data Pump is the replacement for the original Export and Import utilities. Available starting in Oracle Database 10g, Oracle Data Pump enables very high-speed movement of data and metadata from one database to another.

The Data Pump Export and Import utilities have a similar look and feel to the original utilities, but they are much more efficient and give you greater control and management of your import and export jobs.

Unlike original export/import utilities, data pump runs as jobs primarily on the server using server processes and stores the data in database server itself avoiding network latency(Except db link).

Data pump utilities uses direct file manipulation on the directory objects which helps us to achieve the speed. Since directory object can be managed from Oracle, security can be controlled by DBA.

Sample Practical example for export using data pump (expdp)

1. Creating directory 

Data pump works on direct file manipulation on database object directory. So before invoking data pump utilities, we need to create directory object and grant required permission. See the sample below.

create directory hr_backup_dir as '/u02/dbbackup';

grant read, write on directory hr_backup_dir to scott;

2. decide on parallelism

If you the whole system resource is dedicated to database backup, then PARALLEL can be set to number of processor available in that system. If you want to assign only limited processor for backup, then set PARALLEL count accordingly. Based on the PARALLEL count, data dump will span that many number of child Oracle processes.

Parallelism also depends on the number of DUMPFILE specified in expdp. Data pump thread needs exclusive access to DUMPFILE. So if you specify PARALLEL to 8 and give only 1 DUMPFILE, then only 1 data pump process will do export and all other processes will wait for first process to finish and release the dump file. So plan for both PARALLEL and DUMPFILE parameter.

3. run expdp

Here we go

expdp hr/hrworks SCHEMAS=HR DIRECTORY= hr_backup_dir DUMPFILE=hr.thiru.10-feb-2009%U.dmp LOGFILE=hr.thiru.10-feb-2009.log PARALLEL=8 JOB_NAME=HRDataPumpByThiru

If you specify %U in DUMPFILE, data pump will automatically create required number of files (most probably number of files created will be equal to PARALLEL).

4. check log files

Once export got completed, we can check the details in hr.thiru.10-feb-2009.log file.

Interactive Mode

While data pump export operation is going, user can press ctrl + c; it will stop displaying message in screen, but it will not stop the export operation. Instead it display Export prompt which is interactive. From here user can query to see the status of export operation and also alter data pump parameters such as parallel and so on.

Database Monitoring

DBA or any other user have permission can view the status of the export operation from the database views, too. The main view to monitor the jobs is DBA_DATAPUMP_JOBS, which tells how many worker processes (column DEGREE) are working on the job. The other view that is important is DBA_DATAPUMP_SESSIONS, which when joined with the previous view and V$SESSION gives the SID of the session of the main foreground process.

select sid, serial# from v$session s, dba_datapump_sessions d where s.saddr = d.saddr;

For more information refer Oracle documentation here

Table/Index/FK Constraints/Recently Analyzed Tables

Scripts related to Tables/Indexes Oracle

26 09 2010

Here are some scripts related to Tables/Indexes .

Tabs w/ Questionable Inds

TABLES WITH QUESTIONABLE INDEX(ES) NOTES:

Owner – Owner of the table

Table Name – Name of the table

Column – Name of the column in question

The above query shows all tables that have more than one index with the same leading column. These indexes can cause queries to use an inappropriate indexes; in other words, Oracle will use the index that was created most recently if two indexes are of equal ranking. This can cause different indexes to be used from one environment to the next (e.g., from DEV to TEST to PROD).

The information does not automatically indicate that an index is incorrect; however, you may need to justify the existence of each of the indexes above.

select  TABLE_OWNER,
 TABLE_NAME,
 COLUMN_NAME
from   dba_ind_columns
where   COLUMN_POSITION=1
and   TABLE_OWNER not in ('SYS','SYSTEM')
group   by TABLE_OWNER, TABLE_NAME, COLUMN_NAME
having  count(*) > 1

Tabs With More Than 5 Inds

TABLES WITH MORE THAN 5 INDEXES NOTES:

Owner – Owner of the table

Table Name – Name of the table

Index Count – Number of indexes

select  OWNER,
 TABLE_NAME,
 COUNT(*) index_count
from   dba_indexes
where   OWNER not in ('SYS','SYSTEM')
group   by OWNER, TABLE_NAME
having  COUNT(*) > 5
order  by COUNT(*) desc, OWNER, TABLE_NAME

Tables With No Indexes

TABLES WITHOUT INDEXES NOTES:

Owner – Owner of the table

Table Name – Name of the table

select  OWNER,
 TABLE_NAME
from
(
select  OWNER,
 TABLE_NAME
from  dba_tables
minus
select  TABLE_OWNER,
 TABLE_NAME
from  dba_indexes
)
orasnap_noindex
where OWNER not in ('SYS','SYSTEM')
order  by OWNER,TABLE_NAME

Tables With No PK

NO PRIMARY KEY NOTES:

Table Owner – Owner of the table

Table Name – Name of the table

select  OWNER,
 TABLE_NAME
from    dba_tables dt
where   not exists (
        select  'TRUE'
        from    dba_constraints dc
        where   dc.TABLE_NAME = dt.TABLE_NAME
        and     dc.CONSTRAINT_TYPE='P')
and  OWNER not in ('SYS','SYSTEM')
order by OWNER, TABLE_NAME

Disabled Constraints

DISABLED CONSTRAINT NOTES:

Owner – Owner of the table

Table Name – Name of the table

Constraint Name – Name of the constraint

Constraint Type – Type of constraint

Status – Current status of the constraint

select  OWNER,
        TABLE_NAME,
        CONSTRAINT_NAME,
        decode(CONSTRAINT_TYPE, 'C','Check',
                                'P','Primary Key',
                                'U','Unique',
                                'R','Foreign Key',
                                'V','With Check Option') type,
        STATUS
from  dba_constraints
where  STATUS = 'DISABLED'
order  by OWNER, TABLE_NAME, CONSTRAINT_NAME

FK Constraints

FOREIGN KEY CONSTRAINTS NOTES:

Table Owner – Owner of the table

Table Name – Name of the table

Constraint Name – Name of the constraint

Column Name – Name of the column

Referenced Table – Name of the referenced table

Reference Column – Name of the referenced column

Position – Position of the column

select  c.OWNER,
 c.TABLE_NAME,
 c.CONSTRAINT_NAME,
 cc.COLUMN_NAME,
 r.TABLE_NAME,
 rc.COLUMN_NAME,
 cc.POSITION
from  dba_constraints c,
 dba_constraints r,
 dba_cons_columns cc,
 dba_cons_columns rc
where  c.CONSTRAINT_TYPE = 'R'
and  c.OWNER not in ('SYS','SYSTEM')
and  c.R_OWNER = r.OWNER
and  c.R_CONSTRAINT_NAME = r.CONSTRAINT_NAME
and  c.CONSTRAINT_NAME = cc.CONSTRAINT_NAME
and  c.OWNER = cc.OWNER
and  r.CONSTRAINT_NAME = rc.CONSTRAINT_NAME
and  r.OWNER = rc.OWNER
and  cc.POSITION = rc.POSITION
order  by c.OWNER, c.TABLE_NAME, c.CONSTRAINT_NAME, cc.POSITION

FK Index Problems

FK CONSTRAINTS WITHOUT INDEX ON CHILD TABLE NOTES:

Owner – Owner of the table

Constraint Name – Name of the constraint

Column Name – Name of the column

Position – Position of the index

Problem – Nature of the problem

It is highly recommended that an index be created if the Foreign Key column is used in joining, or often used in a WHERE clause. Otherwise a table level lock will be placed on the parent table.

select  acc.OWNER,
 acc.CONSTRAINT_NAME,
 acc.COLUMN_NAME,
 acc.POSITION,
 'No Index' Problem
from    dba_cons_columns acc,
 dba_constraints ac
where   ac.CONSTRAINT_NAME = acc.CONSTRAINT_NAME
and    ac.CONSTRAINT_TYPE = 'R'
and     acc.OWNER not in ('SYS','SYSTEM')
and     not exists (
        select  'TRUE'
        from    dba_ind_columns b
        where   b.TABLE_OWNER = acc.OWNER
        and     b.TABLE_NAME = acc.TABLE_NAME
        and     b.COLUMN_NAME = acc.COLUMN_NAME
        and     b.COLUMN_POSITION = acc.POSITION)
order   by acc.OWNER, acc.CONSTRAINT_NAME, acc.COLUMN_NAME, acc.POSITION

Inconsistent Column Names

INCONSISTENT COLUMN DATATYPE NOTES:

Owner – Owner of the table

Column – Name of the column

Table Name – Name of the table

Datatype – Datatype of the column

select  OWNER,
 COLUMN_NAME,
 TABLE_NAME,
 decode(DATA_TYPE, 'NUMBER', DATA_PRECISION, DATA_LENGTH) datatype
from  dba_tab_columns
where   (COLUMN_NAME, OWNER) in
  (select COLUMN_NAME,
   OWNER
    from  dba_tab_columns
    group by COLUMN_NAME, OWNER
     having min(decode(DATA_TYPE, 'NUMBER', DATA_PRECISION, DATA_LENGTH)) <
    max(decode(DATA_TYPE, 'NUMBER', DATA_PRECISION, DATA_LENGTH)) )
and  OWNER not in ('SYS', 'SYSTEM')
order by COLUMN_NAME,DATA_TYPE

Object Extent Warning

TABLES THAT CANNOT EXTEND NOTES:

Owner – Owner of the object

Object Name – Name of the object

Object Type – Type of object

Tablespace – Name of the tablespace

Next Extent – Size of next extent (bytes)

select  OWNER,
 SEGMENT_NAME,
 SEGMENT_TYPE,
 TABLESPACE_NAME,
 NEXT_EXTENT
from (
 select  seg.OWNER,
  seg.SEGMENT_NAME,
   seg.SEGMENT_TYPE,
  seg.TABLESPACE_NAME,
   t.NEXT_EXTENT
 from  dba_segments seg,
   dba_tables t
 where  (seg.SEGMENT_TYPE = 'TABLE'
 and    seg.SEGMENT_NAME = t.TABLE_NAME
 and    seg.owner = t.OWNER
 and    NOT EXISTS (
   select  TABLESPACE_NAME
    from  dba_free_space free
    where  free.TABLESPACE_NAME = t.TABLESPACE_NAME
    and  BYTES >= t.NEXT_EXTENT))
 union
 select  seg.OWNER,
  seg.SEGMENT_NAME,
   seg.SEGMENT_TYPE,
  seg.TABLESPACE_NAME,
   c.NEXT_EXTENT
 from  dba_segments seg,
   dba_clusters c
 where   (seg.SEGMENT_TYPE = 'CLUSTER'
 and      seg.SEGMENT_NAME = c.CLUSTER_NAME
 and      seg.OWNER = c.OWNER
 and     NOT EXISTS (
   select  TABLESPACE_NAME
   from  dba_free_space free
   where  free.TABLESPACE_NAME = c.TABLESPACE_NAME
   and  BYTES >= c.NEXT_EXTENT))
 union
 select  seg.OWNER,
  seg.SEGMENT_NAME,
   seg.SEGMENT_TYPE,
  seg.TABLESPACE_NAME,
   i.NEXT_EXTENT
 from  dba_segments seg,
   dba_indexes  i
 where   (seg.SEGMENT_TYPE = 'INDEX'
 and      seg.SEGMENT_NAME = i.INDEX_NAME
 and      seg.OWNER        = i.OWNER
 and      NOT EXISTS (
   select  TABLESPACE_NAME
     from  dba_free_space free
     where  free.TABLESPACE_NAME = i.TABLESPACE_NAME
   and  BYTES >= i.NEXT_EXTENT))
 union
 select  seg.OWNER,
  seg.SEGMENT_NAME,
   seg.SEGMENT_TYPE,
  seg.TABLESPACE_NAME,
   r.NEXT_EXTENT
 from  dba_segments seg,
   dba_rollback_segs r
 where   (seg.SEGMENT_TYPE = 'ROLLBACK'
 and      seg.SEGMENT_NAME = r.SEGMENT_NAME
 and      seg.OWNER        = r.OWNER
 and      NOT EXISTS (
   select TABLESPACE_NAME
     from  dba_free_space free
     where  free.TABLESPACE_NAME = r.TABLESPACE_NAME
                and  BYTES >= r.NEXT_EXTENT))
)
orasnap_objext_warn
order  by OWNER,SEGMENT_NAME

Segment Fragmentation

OBJECTS WITH MORE THAN 50% OF MAXEXTENTS NOTES:

Owner – Owner of the object

Tablespace Name – Name of the tablespace

Segment Name – Name of the segment

Segment Type – Type of segment

Size – Size of the object (bytes)

Extents – Current number of extents

Max Extents – Maximum extents for the segment

Percentage – Percentage of extents in use

As of v7.3.4, you can set MAXEXTENTS=UNLIMITED to avoid ORA-01631: max # extents (%s) reached in table $s.%s.

To calculate the MAXEXTENTS value on versions < 7.3.4 use the following equation: DBBLOCKSIZE / 16 – 7

Here are the MAXEXTENTS for common blocksizes: 1K=57, 2K=121, 4K=249, 8K=505, and 16K=1017

Multiple extents in and of themselves aren’t bad. However, if you also have chained rows, this can hurt performance.

select  OWNER,
 TABLESPACE_NAME,
 SEGMENT_NAME,
 SEGMENT_TYPE,
 BYTES,
 EXTENTS,
 MAX_EXTENTS,
 (EXTENTS/MAX_EXTENTS)*100 percentage
from  dba_segments
where  SEGMENT_TYPE in ('TABLE','INDEX')
and  EXTENTS > MAX_EXTENTS/2
order  by (EXTENTS/MAX_EXTENTS) desc

Extents reaching maximum

TABLES AND EXTENTS WITHIN 3 EXTENTS OF MAXIMUM :

Owner – Owner of the segment

Segment Name – Name of the segment

select owner "Owner",
       segment_name "Segment Name",
       segment_type "Type",
       tablespace_name "Tablespace",
       extents "Ext",
       max_extents "Max"
from dba_segments
where ((max_extents - extents) <= 3)
and owner not in ('SYS','SYSTEM')
order by owner, segment_name

Analyzed Tables

ANALYZED TABLE NOTES:

Owner – Owner of the table

Analyzed – Number of analyzed tables

Not Analyzed – Number of tables that have not be analyzed

Total – Total number of tables owned by user

The ANALYZE statement allows you to validate and compute statistics for an index, table, or cluster. These statistics are used by the cost-based optimizer when it calculates the most efficient plan for retrieval. In addition to its role in statement optimization, ANALYZE also helps in validating object structures and in managing space in your system. You can choose the following operations: COMPUTER, ESTIMATE, and DELETE. Early version of Oracle7 produced unpredicatable results when the ESTIMATE operation was used. It is best to compute your statistics.

A COMPUTE will cause a table-level lock to be placed on the table during the operation.

select OWNER,
 sum(decode(nvl(NUM_ROWS,9999), 9999,0,1)) analyzed,
 sum(decode(nvl(NUM_ROWS,9999), 9999,1,0)) not_analyzed,
 count(TABLE_NAME) total
from  dba_tables
where  OWNER not in ('SYS', 'SYSTEM')
group  by OWNER

Recently Analyzed Tables

LAST ANALYZED TABLE NOTES:

Owner – Owner of the table

Table Name – Name of the table

Last Analyzed – Last analyzed date/time

select  OWNER,
 TABLE_NAME,
 to_char(LAST_ANALYZED,'MM/DD/YYYY HH24:MI:SS') last_analyzed
from  dba_tab_columns
where  OWNER not in ('SYS','SYSTEM')
and  LAST_ANALYZED is not null
and COLUMN_ID=1
and  (SYSDATE-LAST_ANALYZED) < 30
order by (SYSDATE-LAST_ANALYZED)

Cached Tables

CACHED TABLE NOTES:

Owner – Owner of the table

Table Name – Name of the table

Cache – Cached

select  OWNER,
 TABLE_NAME,
 CACHE
from dba_tables
where OWNER not in ('SYS','SYSTEM')
and CACHE like '%Y'
order by OWNER,TABLE_NAME

Comments : Leave a Comment »
Tags: Scripts, SQL
Categories : Database, SQL, SQL

---

How to Add Date and Time to a Filename with Windows Command Line.

22 07 2010

Many times it becomes necessary to append date stamp on your export dumps. In Unix systems, shell scripting is very liberal with variables you can define yourself eg:

expdate=`date ‘+%d%m%Y’`
dat=`date ‘+%m%d%y %H:%M:%S’`

And then go onto define in your script as

./expdp system/system directory=export_dir dumpfile=exp_swx_$expdate.dmp logfile=exp_swx_$expdate.log schemas=swx

But on Windows it can be done in a more easier way, using the Date and Time function. Just input the string below as part of the file name any time you want the current system date and/or time included as part of the file name.
Date:  %date:~4,2%-%date:~7,2%-%date:~12,2%
Time:  %time:~0,2%-%time:~3,2%-%time:~6,2%
Example: copy c:\test.txt c:\test-%date:~4,2%-%date:~7,2%-%date:~12,2%.txt will output c:\test-mm-dd-yy.txt

exp edate/edate file=edate%date:~4,2%-%date:~7,2%-%date:~12,2%.dmp log=edate%date:~4,2%-%date:~7,2%-%date:~12,2%.txt owner=edate statistics=none

Comments : Leave a Comment »
Tags: Data Pump, Import Export, Scripts, UNIX, WINDOWS
Categories : Database

---

Script to check Oracle instance status

27 04 2010

A small shell script to check instance status of oracle database.
$vi instanceup.sh
#!/bin/sh
SERVICE=’ora_pmon_orcl’
#replace ‘orcl’ with your sid
if ps -ef | grep -v grep | grep $SERVICE > /dev/null
then
echo “$SERVICE service running, everything is fine”
else
echo “$SERVICE is not running”
fi
$./instanceup.sh

Comments : Leave a Comment »
Tags: Scripts

Oracle 11g Invisible Indexes

CREATE INDEX I_emp_ename ON emp(ename)
TABLESPACE users
STORAGE (INITIAL 10K NEXT 10k PCTINCREASE 50)
INVISIBLE;

ALTER INDEX I_emp_ename INVISIBLE;

ALTER INDEX I_emp_ename VISIBLE;


SELECT INDEX_NAME, VISIBILITY FROM USER_INDEXES
WHERE INDEX_NAME = 'I_EMP_ENAME';

INDEX_NAME VISIBILITY
---------- ----------I_EMP_ENAME VISIBLE

AWR and STATSPACK

Differences or Advantage between AWR and STATSPACK report

1)The AWR is the next evolution of the STATSPACK utility.

2)The AWR repository holds all of the statistics available in STATSPACK as well as some additional statistics which are not.

3)STATSPACK does not store the Active Session History (ASH) statistics which are available in the AWR dba_hist_active_sess_history view.

4)An important difference between STATSPACK and the AWR is that STATSPACK does not store history for new metric statistics introduced in Oracle10g. The key AWR views, dba_hist_sysmetric_history and dba_hist_sysmetric_summary.

5)The AWR also contains views such as dba_hist_service_stat , dba_hist_service_wait_class and dba_hist_service_name , which store history for performance cumulative statistics tracked for specific services.

6)The latest version of STATSPACK included with Oracle10g contains a set of specific tables, which track history of statistics that reflect the performance of the Oracle Streams feature. These tables are stats$streams_capture , stats$streams_apply_sum , stats$buffered_subscribers , stats$rule_set , stats$propagation_sender , stats$propagation_receiver and stats$buffered_queues . The AWR does not contain the specific tables that reflect Oracle Streams activity; therefore, if a DBA relies heavily on the Oracle Streams feature, it would be useful to monitor its performance using STATSPACK utility.

7)Statspack snapshots must be run by an external scheduler (dbms_jobs, CRON, etc.). AWR snapshots are scheduled every 60 minutes by default. Administrators can manually adjust the snapshot interval if so desired.

8)ADDM captures a much greater depth and breadth of statistics than Statspack does. During snapshot processing, MMON transfers an in-memory version of the statistics to the permanent statistics tables.

9)Statspack snapshot purges must be scheduled manually. When the Statspack tablespace runs out of space, Statspack quits working. AWR snapshots are purged automatically by MMON every night. MMON, by default, tries to keep one week's worth of AWR snapshots available. If AWR detects that the SYSAUX tablespace is in danger of running out of space, it will free space in SYSAUX by automatically deleting the oldest set of snapshots. If this occurs, AWR will initiate a server-generated alert to notify administrators of the out-of-space error condition. Administrators can manually adjust the amount of information retained by invoking the MODIFY_SNAPSHOT_SETTINGS PL/SQL stored procedure and specifying the RETENTION parameter input variable.

10)AWR snapshots provide a persistent view of database statistics. They are stored in the system-defined schema, which resides in a new tablespace called SYSAUX. A snapshot is a collection of performance statistics that are captured at a specific point in time. The snapshot data points are used to compute the rate of change for the statistic being measured. A unique SNAP_ID snapshot identifier identifies each snapshot.

RMAN clone

RMAN Cloning

1.Create same(target) tree structure in clone server

2. windows:  c:\>set oracle_sid=<clone db name>

Or

Linux $ export ORACLE_SID=<clone db name>

3.create pwd file

Windows :c:\>orapwd file=%ORACLE_HOME%\database\PWD<clonename>.ora password=sys

Linux:$orapwd file=$ORACLE_HOME/dbs/orapw<db name> password=sys

4.copy the target pfile and past clone server and edit name and  add the db_file_name_convert, log_file_name_convert ..

5.Configure TNS and listener  and checking the status  use the command of ping

6.startup pfile nomount

7.open new terminal

8. $export ORACLE_SID=<target db name>

9.rman taget

10.RMAN>backup database;

11.RMAN>backup current controlfile;

12. RMAN>backup archivelog all;

RMAN>connect auxiliary username/pwd@<clone net service name>;

RMAN>duplicate target database to ‘<clone db name>’;

Take time for minimum 1 ½ hr

or

{

13. open new terminal

14. $export ORACLE_SID=<clone db>

15.rman target username/pwd@<net service name (target)> auxiliary /

16.RMAN>duplicate target database to ‘<clone name in caps>’;

Take time for minimum 1 ½ hr or  after restore controlfile and datafile give ctl+c

17. shu immediate  for target db and copy and past archivelog files

18.shu immediate for clone

19.startup mount clone

20. alter database open resetlogs; in clone

21.alter database open; in clone

}

RAC Installation

Oracle 10G RAC release 2 implementations on Linux

Network configuration on AII RAC nodes

#vi/etc/hosts

127.0.0.1 localhost.localdomain localhost

#Public Network – (eth0)

192.168.1.100  linux1

192.168.1.101  linux2

#Private Interconnect – (eth1)

192.168.2.100  linux1-priv

192.168.2.101  linux2-priv

#Public virtual IP (VIP) addresses – (eth0:1)

192.168.1.200  linux1-vip

192.168.1.201  linux2-vip

#Private Storage Network for SAN – (eth1)

192.168.1.195 SAN

Setting Kernel Parameters on All RAC Nodes

#vi / etc/sysctl.conf

Net.core.rmem_default = 262144

Net.core.wmem_default = 262144

Net.core.rmem_max = 262144

Net.core.wmem_max = 262144

Kernel.shmmax = 2147483648

Kernel.shmall = 2097152

Kernel.sem = 250 32000 100 128

fs.file-max = 65536

net.ipv4.ip_local_port_range = 1024 65000

Setting shell limits for the oracle User all RAC Nodes

# vi / etc / security / limits.conf

oracle soft nproc 2047

oracle hard nproc 16384

oracle soft nofile 1024

oracle hard nofile 65536

# vi / etc/pam.d/login

Session required /lib/security/pam_limits.so

Setting the correct date & time on all RAC nodes

Setting the date and time from linux1;

#date –s “9/2/2007 01:12:00”

Setting the date and time from linux2;

#date –s “9/2/200701:12:20”

Installing RPM Package on All RAC nodes

binutls-2.15.92.0.2-21

compat-db-4.1.25-9

compat-gcc-32-3.2.3-47.3

compat-gcc-32-c++-3.2.3-47.3

compat-libstdc++-33-3.2.3-47.3

compat-libgcc-296-2.96-132.7.2

control-center-2.8.0-12.rhel4.5

cpp-3.4.6-3

gcc-3.4.6-3

gcc-c++-3.4.6-3

glibc-2.3.4-2.25

glibc-common-2.3.4-2.25

glibc-devel-2.3.4-2.25

glibc-headers-2.3.4-2.25

glibc-kernheaders-2.4-9.1.98.EL

gnome-libs-1.4.1.2.90-44.1

libaio-0.3.105-2

libstdc++-devel-3.4.6-3

make-3.80-6.EL4

openmotif-2.2.3-10.RHEL4.5

openmotif21-2.1.30-11.RHEL4.6

pdksh-5.2.14-30.3

setarch-1.6-1

sysstat-5.0.5-11.rhel4

xscreensaver-4.18-5.rhel4.11

Configure SAN storage on All Nodes

# rpm – qa | grep iscsi

Iscsi-initiator-utils-4.0.3.0-5

# rpm –Uvh iscsi-initiator-utils-4.0.3.0-15.i386.rpm

# vi / etc/iscsi.conf

DiscoveryAddress = SAN

# service iscsi restart

# chkconfig --level 345 iscsi on

# fdisk / dev/sda (Create Partition only one node Linux1)

Command (m for help) : n

Command action

e extended

p primary partition (1-4)

p

Partition  number (1-4) : 1

First cylinder (1-15134, default 1 ) : 1

Last cylinder or +size or +sizeM or +sizeK (1-15134, default 15134) : + 20000M

Command (m for help) : n

Command action

e extended

p primary partition (1-4)

p

Partition  number (1-4) : 2

First cylinder (1-15134, default 1 ) :

Last cylinder or +size or +sizeM or +sizeK : + 50000M

Command (m for help) : n

Command action

e extended

p primary partition (1-4)

p

Partition  number (1-4) : 3

First cylinder (1-15134, default 1 ) :

Last cylinder or +size or +sizeM or +sizeK : + 50000M

Command (m for help) : n

Command action

e extended

p primary partition (1-4)

p

Partition  number (1-4) : 4

First cylinder (1-15134, default 1 ) :

Last cylinder or +size or +sizeM or +sizeK : + 100000M

Command (m for help) :W

The partition table has been altered!

Calling ioctl() to re-read partition table.

Syncing disks.

#fdisk -1

It will display four partition on all nodes

/dev/sda1

/dev/sda2

/dev/sda3

/dev/sda4

Create Group and User for Oracle All RAC Nodes

# groupadd –g 501 oinstall

#useradd –m –u 501 –g oinstall –G oracle

# id oracle

Uid=501(oracle) gig=501(oinstall) groups=501(oinstall)

Set the password for the oracle account all RAC Nodes

# passwd oracle

Changing password for user oracle.

New UNIX password : XXXXXXXXXXX

Retype new UNIX password : XXXXXXXXXXX

Passwd : all authentication tokens updated sucessfully

Create the oracle base Directory All Nodes

# mkdir –p /u01/app/oracle/product/10.2.0/d_1

#chown –R oracle:oinstall /u01/app/oracle

#chmod –R 775 /u01/app/oracle

Create  the oracle clustware Home Directory All Nodes

# mkdir –p /u01/app/crs

# chown –R oracle:oinstall /u01/app/crs

# chmod –R 775 /u01/app/crs

Create Mount Point for OCFS2 / clustware All Nodes

#mkdir –p /u02

# chown –R oracle:oinstall / u02

# chmod –R 775 /u02

Create Login Script for oracle User Account All nodes

# su – oracle

# vi.bash_profile

export ORACLE_SID=orcl1  /*second node change ORCL2*/

export ORACLE_BASE=/u01/app/oracle

export ORACLE_HOME=$ORACLE_BASE/product/10.2.0/db_1

export ORA_CRS_HOME=/Uo1/app/crs

export LD_LIBRARY_PATH=$ORACLE_HOME/lib

export CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/network/jilib:$ORACLE_HOME/rdbms/jlib

export PATH=$PATH:$ORACLE_HOME/bin:$ORA_CRS_HOME/bin

Configure the “hangcheck-timer” kernel module All RAC Nodes :

# vi / etc/modprobe.conf

Options hangcheck-timer hangcheck_tick=30 hangcheck_margin=180

Hangcheck-tick: This parameter defines the period of time between checks of system health. The default value is 60 seconds; oracle recommends setting it to 30 seconds. Hangcheck-margin: This parameter defines the maximum hang delay that should be tolerated before hangcheck-timer resets that RAC node. It defines the margin of error in seconds. The default value is 180 seconds; Oracle recommends setting it to 180 sconds.

Creating the RSA keys on All Oracle RAC Nodes

# rpm –qa |grep rsh*

# chkconfig rsh on

# chkconfig rlogin on

# service xinetd reload

# touch /etc/hosts.equiv

# chmod 600 /etc/hosts.equiv

# chown root:root /etc/hosts.equiv

#vi / etc/hosts.equiv

                +linux1 oracle

                +linux2 oracle

                +linux1-priv  oracle

                +linux2-priv  oracle

# mv / usr/Kerberos/bin/rsh/usr/Kerberos/bin/rsh.old

# mv / usr/Kerberos/bin/rlogin/usr/Kerberos/bin/rlogin.old

# mv / usr/Kerberos/bin/rcp/usr/Kerberos/bin/rcp.old

# which rsh

/usr/bin/rsh

Install and configure oracle cluster file system (OCFS2) All RAC Nodes

# rpm –Uvh ocfs2-2.6.9-55.EL-1.2.5-6.i686.rpm

# rpm –Uvh ocfs2console-1.2.4-1.i386.rpm

# rpm –Uvh ocfs2-tools-1.2.4-1.i386.rpm

Disable SELinux (RHEL4 U2 and higher ) All RAC Nodes:

# /usr/bin/system-config-securitylevel &

# init 6 (restart all servers)

Configure OCFS2 on All RAC Nodes

# ocfs2console &

1.       Select [ Cluster] -> [ Configure Nodes…].

2.       Click “Close” button on below window

3.       Displaying “Node configuration” window

4.       On the “node configuration” dialog , click the [ADD] button

5.       This will bring up the “Add Node” dialog

6.       In the “Add Node” dialog, enter the host name and IP address for the all the node one by one in the cluster. Leave the IP Port set to its default value of 7777.

Click [CLOSE] on the “node configuration” dialog.

After verifying all values are correct, exit the application using [ File] -> [Quit]. This needs to be performed on both Oracle RAC nodes in the cluster.

Check all RAC nodes OCFS configure

# more /etc/ocfs2/cluster.conf

node:

        ip_port = 7777

ip_address = 192.168.2.100

        number = 0

        name = linux1

        cluster = ocfs2

node:

        ip_port = 7777

ip_address = 192.168.2.101

        number = 1

        name = linux2

        cluster = ocfs2

cluster :

        node_count = 2

        name = ocfs2

# /etc/init.d/o2cb offline ocfs2

Stopping O2CB cluster ocfs2 : OK

The above command will offline the cluster we created, ocfs2.

# /etc/init.d/o2cb unload

Unmounting ocfs2_dlmfs filesystem: OK

Unloading module “ocfs2_dlmfs” : OK

Unmounting configfs filesystem : OK

Unloading module “configfs” : OK

The above command will  unload all OCFS2 modules.

# /etc/init.d/o2cb configure

Configuring the O2CB driver.

This will configure the on-boot properties of the O2CB driver.

The following questions will determine whether the driver is loaded on boot. The current values will be shown in brackets (‘[]’). Hitting  <enter> without typing an answer will keep that current value. Ctrl-C will abort.

Load O2CB driver on boot (y/n) [n] : y

Cluster  to start on boot (Enter “none” to clear) [ocfs2] : ocfs2

Specify heartbeat dead threshold (>=7) [7] : 61

Specify network idle timeout in ms (>=5000) [10000] : 10000

Specify network keepalive delay in ms (>=1000) [5000] : 5000

Specify network reconnect delay in ms (>=2000) [2000] : 2000

Writing O2CS configuration : OK

Loading module “configfs” : OK

Mounting configfs filesystem at /config :OK

Loading module “ocfs2_nodemanager” :OK

Loading module “ocfs2_dlm” : OK

Loading module “ocfs_dlmfs” : OK

Mounting ocfs2_dlmfs filesystem at / dlm : ok

Starting O2CB cluster ocfs2 : OK

Format the OCFS2 file system only one node Linux1

# mkfs.ocfs2 –b 4k –C 32K –N 4 –L oracrsfiles /dev/sda1

Mount the OCFS2 File system All RAC nodes

# mount  -t ocfs2 -0 datavolume,nointr –L “oracrsfiles” /u02

# vi/etc/fstab

LABEL = oracrsfiles        /u02    ocfs      _netdev,datavolume,nointr   0 0

# chkconfig –list o2cb

O2cb        0:off     1 : off    2: on  3:on   4:on    5:on   6:off

# chown oracle:oinstall / u02

# chmod 775 /u02

Create Directory for oracle clusterware Files any one node Linux1

 # mkdir –p /u02/oradata/orcl

# chown –R oracle:oinstall /u02/oradata

# chmod –R 775 /u02/oradata

# Is –I /u02/oradata

total 4

 drwxrwxr-x 2 oracle oinstall 4096 Sep 3 00:45 orcl

Reboot All RAC Nodes

# init 6

# mount

/dev/sde1 on / u02 type ocfs2 (rw, _netdev,datavolume,nointr,heartbeat=local)

# ca /proc/fs/ocfs2_nodemanager/hb_dead_threshold

61

Install and configure Automatic Storage Management (ASMLib 2.0) All RAC Nodes

# rpm  -Uvh oracleasm-2.6.9-55.EL-2.0.3-1.i686.rpm

# rpm –Uvh oracleasmlib-2.0.2-1.i386.rpm

# rpm –Uvh oracleasm-support-2.0.3-1.i386.rpm

# /etc/init.d/oracleasm configure

Configuring the Oracle ASM library driver

This will configure the on-boot properties of the oracle ASM library driver. The following questions will determine whether the driver is loaded on boot  and what permissions it will have. The current values will be shown in brackets (‘[]’). Hitting <ENTER>  without typing an answer will keep that current value. Ctrl-C will abort.

Default user to own the driver interface []: oracle

Default group to own the driver interface []: oinstall

Start Oracle ASM library driver on boot (y/n) [n] : y

Fix  permissions of Oracle ASM disks on boot (y/n) [y]: y

Writing Oracle ASM library driver configuration : [ OK ]

Creating /dev/oracleasm mount point : [ OK]

Loading module “oracleasm” : [ OK]

Mounting  ASMlib driver filesystem : [ OK]

Scanning system for ASM disks: [ OK]

Create ASM Disks for oracle on any one node Linux1

# /etc/init.d/oracleasm createdisk VOL1/dev/sda2

Marking disk “/dev/sda2” as an ASM disk [ OK]

# /etc/init.d/oracleasm createdisk VOL2/dev/sda3

Marking disk “/dev/sda31” as an ASM disk [ OK]

# /etc/init.d/oracleasm createdisk VOL3/dev/sda4

Marking disk “/dev/sda4” as an ASM disk [ OK]

# /etc/init.d/oracleasm listdisks  -All RAC nodes

VOL1

VOL2

VOL3

#init 6        on primary node Linux1

Login as oracle user on Linux1 and root user on Linux2

Install Oracle Clusterware 10g software from Linux1 as oracle user

$cd /software/clusterware

$ . / runInstallar

Click “Next”  Button

Enter  the appropriate name and path for the oracle Home and click the  “Next”  button.

Wait while the prerequisite checks are done. If you have any failures correct them and retry the tests before clicking the “Next” button

The “ Specify Cluster Configuration “ screen shows only the LINUX1 node in the cluster. Click the “ADD” button  to continue.

Enter the details for the LINUX2 node and click the “OK” button.

Click the “NEXT” button to continue.

The “Specific Network Interface Usage” screen defines how each network interface will be used. Highlight the “eth0” interface and click the  “Edit”  button.

Set the “eth0” interface type to “Public” and click the “OK” button.

Leave the “eht1”  interface as private and click the “Next” button.

Click the “External Redundancy” option, enter  “/u02/oradata/orcl/ocrfile” as the OCR location and click the “Next” button. To have greater redundancy we would need to define another shared disk for an alternate location.

Click the “External Redundancy” option, enter  “/u02/oradata/orcl/votingdisk” as the voting Disk location and click the “Next” button. To have greater redundancy we would need to define another shared disk for an alternate location.

On the “Summary” screen, click the “install” button to continue.

Wait while the installation takes place.

Once the install is complete , run the orainstRoot.sh and root.sh scripts on both nodes as directed on the following screen.

The output from the orainstRoot.sh file should look something like that listed below.

#  cd /u01/app/oracle/oraInventory

# /orainstRoot.sh

Changing permissions of /u01/app/oracle/oraInventory to 770

Changing groupname  of /u01/app/oracle/oraInventory to oinstall

The execution of the script is complete.

The output of the root.sh will vary a little depending on the node it is run on. The following text is the output from the Linux1 node.

# cd /u01/app/crs

# ./root.sh

WARNING:  directory ‘/u01/app/crs/product/10.2.0’ is not owned by root

WARNING:  directory ‘/u01/app/crs/product’ is not owned by root

WARNING:  directory ‘/u01/app/crs’ is not owned by root

WARNING:  directory ‘/u01/app’ is not owned by root

Checking to see if Oracle CRS stack is already configured.

/etc/oracle does not exist. Creating it now.

Setting the permissions on OCR backup directory

Setting up NS directories

Oracle Cluster Registry Configuration upgraded successfully

WARNING:  directory ‘/u01/app/crs’ is not owned by root

WARNING:  directory ‘/u01/app’ is not owned by root

assigning default hostname Linux1 for node1

assigning default hostname Linux2 for node2

Successfully accumulated necessary OCR keys

Using ports: CSS=49895 CRS=49896 EVMC=49898 and EVMR=49897

Node >nodenumber<: >nodename <>private interconnect name <> hostname<

node 1 : linux1 linux1-priv linux1

node 2 : linux2 linux2-priv linux2

Creating OCR keys for user ‘root’, privgrp ‘root’.

Operation successful.

Now formatting voting device: /u002/oradata/orcl/votingdisk

Format of 1 voting devices complete

Startup will be quested to init within 90 seconds

Adding daemons to initab

Expecting the CRS daemons to be up within 600 seconds

CSS is active on these nodes.

        Linux1

CSS is inactive on these nodes.

        Linux2

Local node checking complete.

Run  root.sh on remaining nodes to start CRS daemons.

#

Ignore the directory ownership warnings. We should really use a separate directory structure for the clusterware so it can be owned by the root user, but it ash little effect on the finished results.

The output from the Linux2 node is listed below.

# /u01/app/crs

# ./root.sh

WARNING:  directory ‘/u01/app/crs’ is not owned by root

WARNING:  directory ‘/u01/app’ is not owned by root

Checking to see if Oracle CRS stack is already configured.

/etc/oracle does not exist. Creaing it now.

Setting the permissions on OCR backup directory

Setting up NS directories

Oracle Cluster Registry Configuration upgraded successfully

WARNING:  directory ‘/u01/app/crs’ is not owned by root

WARNING:  directory ‘/u01/app’ is not owned by root

clsfg: EXISTING configuration version 3 deducted

clsfg : version 3 is 10G release 2. 

assigning default hostname Linux1 for node1

assigning default hostname Linux2 for node2

Successfully accumulated necessary OCR keys

Using ports: CSS=49895 CRS=49896 EVMC=49898 and EVMR=49897

Node >nodenumber<: >nodename <>private interconnect name <> hostname<

node 1 : linux1 linux1-priv linux1

node 2 : linux2 linux2-priv linux2

clsfg: Arguments check out successfully.

NO KEYS WERE WRITTEN. Supply –force parameter to override.

-force is destructive and will destroy any previous cluster

Configuration.

Oracle  Cluster Registry for cluster has been already been initialized.

Startup will be queued to init within 90 seconds.

Adding Daemons to inittab

Expecting the CRS daemons to be up within 600 seconds

CSS is active on these nodes.

        Linux1

        Linux2

CSS is active on these nodes.

Waiting for the oracle CRSD and EVMD to start

Waiting for the oracle CRSD and EVMD to start

Oracle CRS stack installed and running under init(1M)

Running vipca(silent) for configuring nodeapps

The given interface(s), “eth0” is not public. Public interfaces should be used  to configure virtual IPs.

#

Here you can see that some of the configuration steps are omitted as they were done by the first node. In addition, the final part of the script ran the Virtual IP Configuration Assistant (VIPCA) in silent mode, but it failed. This is because my public IP addresses are actually within the “10.1.255.255” range  which is a private IP range. If you were using “legal” IP addresses you would not see this and you could ignore the following VIPCA steps.

Run the VIPCA manually as the root user on the Linux2 node using the following command   

# cd/u01/app/crs/bin

# ./vipca

Click the “Next” button on the VIPCA welcome screen

Highlight the “eth0” interface and click the “Next” button

Enter the virtual IP alias and address for each node. Once you enter the first alias , the remaining values should default automatically. Click the “Next” button to continue.

Accept the summary information by clicking the “Finish” button.

Wait until the configuration is complete , then click the “OK” button

Accept the VIPCA results by clicking the “Exit” button

You should now return to the “Executive Configuration scripts” screen on Linux1 and click the “OK” button.

Wait for the configuration assistants to complete.

When the installation is complete, click the “Exit”  button to leave the installer.

Install Oracle Database Software from Linux1:

$ cd/software/Database

$ ./runInstaller

Select  the “Enterprise Edition” option and click the “Next” button.

Enter the name and path for the Oracle Home and click the “Next” button

Select the “Cluster Install” option and make sure both RAC nodes are selected, the click the “Next” button

Wait while the prerequisite checks are done. If you have any failures correct them and retry the tests before clicking the “Next” button

Select the “Install database software only” option, then click the “Next” button.

On the “Summary” screen, click the “Install” button to continue.

Wait while the database software installs.

Once the installation is complete, wait while the configuration assistants run.

Execute  the “root.sh” scripts on both nodes , as instructed on the “Execute Configuration scripts” screen, then click the “OK” button.

When the installation is complete, click the “Exit” button to leave the installer.

Listener Configuration (Necta) from Linux1 as oracle user

$ netca

Create a database using the DBCA from Linux1 as oracle user

$ dbca

On the “Welcome” screen, select  the “Oracle Real Application Clusters database” option and click the “Next” button

Select the “Create a Database” option and click the “Next” button.

Highlight both RAC nodes and click the “Next” button.

Select the “General purpose” option and click the “Next” button

Enter the values “ORCL” and “ORCL” for the Global Database Name and SID Prefix respectively, then click the “Next” button

Accept the management options by clicking the “Next” button. If you are attempting the installation on a server with limited memory, you may prefer not to configure Enterprise manager at this time.

Enter database passwords then click the “Next” button.

Select the “Automatic Storage Management “  option, then click the “Next” button.

Select the “IFILE parameter” option and enter sys user password for ASM instance then click the “Next” button.

Click “Ok” to create ASM instance.

Click “Create New” to  create ASM disk group from  Database area

Type group name “DATA” and select “Normal” redundancy then select “Vol1&vol2” then click “Ok”

“DATA” ASM Group Creation Progress

Click “Create New” to create another ASM disk group for Flash recovery area

Type “BACKUP”  in disk group name and select “External” redundancy and select  “VOL3” then click “OK”

Select “DATA & BACKUP” two ASM Disk group then click “Next”

Type Database area is “+DATA” ASM diskgroup then click “Next”

Type Flash Recovery area “+BACKUP” ASM diskgroup then click “Next”

Click “Next”

Accept the current database services configuration by clicking the “Next” button.

Select the “Custom” memory management option and accept the default settings by clicking the “Next” button.

Accept the database storage settings by clicking the “Next” button.

Accept the database creation options by clicking the “Finish” button.

Accept the summary information by clicking the “OK” button.

Wait while the database is created.

Once the database creation is complete you are presented with the following screen. Make a note of the information on the screen and click the “Exit” button.

The RAC database creation is now complete.

ADD RAC Nodes

Network configuration on all RAC Nodes

# vi/etc/hosts

127.0.0.1              localhost.localdomain localhost

#Public Network  - (eth0)

192.168.1.100 linux1

192.168.1.101 linux2

# Private Interconnect  - (eth1)

192.168.2.100 linux1-priv

192.168.2.100 linux2-priv

# Public Virtual IP (VIP) addresses – (eth0:1)

192.168.1.200 linux1-vip

192.168.1.201 linux2-vip

#Private Storage Network for SAN – (eth1)

192.168.1.195 SAN

Setting Kernel Parameters on All RAC Nodes:

# vi/etc/sysctl.conf

Net.core.rmem_default = 262144

Net.core.wmem_default = 262144

Net.core.rmem_max = 262144

Net.core.wmem_max = 262144

Kernel.shmmax = 2147483648

Kernel.shmall = 2097152

Kernel.sem = 250 32000 100 128

fs.file-max = 65536

net.ipv4.ip_local_port_range = 1024 65000

Setting shell limits for the oracle User all RAC Nodes

# vi / etc / security / limits.conf

oracle soft nproc 2047

oracle hard nproc 16384

oracle soft nofile 1024

oracle hard nofile 65536

# vi / etc/pam.d/login

Session required /lib/security/pam_limits.so

Setting the correct date & time on all RAC nodes

Setting the date and time from linux1;

#date –s “9/2/2007 01:12:00”

Setting the date and time from linux2;

#date –s “9/2/200701:12:20”

Installing RPM Package on All RAC nodes

binutls-2.15.92.0.2-21

compat-db-4.1.25-9

compat-gcc-32-3.2.3-47.3

compat-gcc-32-c++-3.2.3-47.3

compat-libstdc++-33-3.2.3-47.3

compat-libgcc-296-2.96-132.7.2

control-center-2.8.0-12.rhel4.5

cpp-3.4.6-3

gcc-3.4.6-3

gcc-c++-3.4.6-3

glibc-2.3.4-2.25

glibc-common-2.3.4-2.25

glibc-devel-2.3.4-2.25

glibc-headers-2.3.4-2.25

glibc-kernheaders-2.4-9.1.98.EL

gnome-libs-1.4.1.2.90-44.1

libaio-0.3.105-2

libstdc++-devel-3.4.6-3

make-3.80-6.EL4

openmotif-2.2.3-10.RHEL4.5

openmotif21-2.1.30-11.RHEL4.6

pdksh-5.2.14-30.3

setarch-1.6-1

sysstat-5.0.5-11.rhel4

xscreensaver-4.18-5.rhel4.11

Configure SAN storage on All Nodes

# rpm – qa | grep iscsi

Iscsi-initiator-utils-4.0.3.0-5

# rpm –Uvh iscsi-initiator-utils-4.0.3.0-15.i386.rpm

# vi / etc/iscsi.conf

DiscoveryAddress = SAN

# service iscsi restart

# chkconfig  --level 345 iscsi on

# fdisk  -I

/dev/sda1

/dev/sda2

/dev/sda3

/dev/sda4

Create Group and User for Oracle All RAC Nodes

# groupadd –g 501 oinstall

#useradd –m –u 501 –g oinstall –G oracle

# id oracle

Uid=501(oracle) gig=501(oinstall) groups=501(oinstall)

Set the password for the oracle account all RAC Nodes

# passwd oracle

Changing password for user oracle.

New UNIX password : XXXXXXXXXXX

Retype new UNIX password : XXXXXXXXXXX

Passwd : all authentication tokens updated sucessfully

Create the oracle base Directory All Nodes

# mkdir –p /u01/app/oracle/product/10.2.0/d_1

#chown –R oracle:oinstall /u01/app/oracle

#chmod –R 775 /u01/app/oracle

Create  the oracle clustware Home Directory All Nodes

# mkdir –p /u01/app/crs

# chown –R oracle:oinstall /u01/app/crs

# chmod –R 775 /u01/app/crs

Create Mount Point for OCFS2 / clustware All Nodes

#mkdir –p /u02

# chown –R oracle:oinstall / u02

# chmod –R 775 /u02

Create Login Script for oracle User Account All nodes

# su – oracle

# vi.bash_profile

export ORACLE_SID=orcl1  /*second node change ORCL2*/

export ORACLE_BASE=/u01/app/oracle

export ORACLE_HOME=$ORACLE_BASE/product/10.2.0/db_1

export ORA_CRS_HOME=/Uo1/app/crs

export LD_LIBRARY_PATH=$ORACLE_HOME/lib

export CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/network/jilib:$ORACLE_HOME/rdbms/jlib

export PATH=$PATH:$ORACLE_HOME/bin:$ORA_CRS_HOME/bin

Configure the “hangcheck-timer” kernel module All RAC Nodes :

# vi / etc/modprobe.conf

Options hangcheck-timer hangcheck_tick=30 hangcheck_margin=180

Hangcheck-tick: This parameter defines the period of time between checks of system health. The default value is 60 seconds; oracle recommends setting it to 30 seconds. Hangcheck-margin: This parameter defines the maximum hang delay that should be tolerated before hangcheck-timer resets that RAC node. It defines the margin of error in seconds. The default value is 180 seconds; Oracle recommends setting it to 180 sconds.

Creating the RSA keys on All Oracle RAC Nodes

# rpm –qa |grep rsh*

# chkconfig rsh on

# chkconfig rlogin on

# service xinetd reload

# touch /etc/hosts.equiv

# chmod 600 /etc/hosts.equiv

# chown root:root /etc/hosts.equiv

#vi / etc/hosts.equiv

                +linux1 oracle

                +linux2 oracle

                +linux1-priv  oracle

                +linux2-priv  oracle

# mv / usr/Kerberos/bin/rsh/usr/Kerberos/bin/rsh.old

# mv / usr/Kerberos/bin/rlogin/usr/Kerberos/bin/rlogin.old

# mv / usr/Kerberos/bin/rcp/usr/Kerberos/bin/rcp.old

# which rsh

/usr/bin/rsh

Install and configure oracle cluster file system (OCFS2) All RAC Nodes

# rpm –Uvh ocfs2-2.6.9-55.EL-1.2.5-6.i686.rpm

# rpm –Uvh ocfs2console-1.2.4-1.i386.rpm

# rpm –Uvh ocfs2-tools-1.2.4-1.i386.rpm

Disable SELinux (RHEL4 U2 and higher ) All RAC Nodes:

# /usr/bin/system-config-securitylevel &

# init 6 (restart all servers)

Configure OCFS2 on All RAC Nodes

# ocfs2console &

1.       Select [ Cluster] -> [ Configure Nodes…].

2.       Click “Close” button on below window

3.       Displaying “Node configuration” window

4.       On the “node configuration” dialog , click the [ADD] button

5.       This will bring up the “Add Node” dialog

6.       In the “Add Node” dialog, enter the host name and IP address for the all the node one by one in the cluster. Leave the IP Port set to its default value of 7777.

Click [CLOSE] on the “node configuration” dialog.

After verifying all values are correct, exit the application using [ File] -> [Quit]. This needs to be performed on both Oracle RAC nodes in the cluster.

Check all RAC nodes OCFS configure

# more /etc/ocfs2/cluster.conf

node:

        ip_port = 7777

ip_address = 192.168.2.100

        number = 0

        name = linux1

        cluster = ocfs2

node:

        ip_port = 7777

ip_address = 192.168.2.101

        number = 1

        name = linux2

        cluster = ocfs2

cluster :

        node_count = 2

        name = ocfs2

Add New Oracle RAC Node to the OCFS2 Cluster

As root , run the following from linux1 and then linux2;

[root@linux1 ~] #  o2cb_ctl –C     –i   –n  linux3 –t  node    –a  number=2  –a  ip_address=192.168.2.107   –a  ip_port=7777  –a  cluster=ocfs2

Node.linux3 created.

[root@linux2 ~] #  o2cb_ctl –C     –i   –n  linux3 –t  node    –a  number=2  –a  ip_address=192.168.2.107   –a  ip_port=7777  –a  cluster=ocfs2

Node.linux3 created.

O2cb_ctl parameters:

-c : Create an object in the OCFS2 Cluster Configuration

-i : Valid only with –C. When creating something (node or cluster), it will also install it in the live cluster (/config). If the parameter is not specified , then only update the /etc/ocfs2/cluster.conf.

-n : object name which is usually the node name or cluster name.

-t: Type can be cluster , node or heartbeat.

-a: Attribute in the format “parameter=value” which will be set in the file /etc/ocfs2/cluster.conf file. Since nodes are numbered starting with zero, the third node in the OCFS2 cluster will be “number=2”. Set the IP address which in this example will be the private interconnect “ip_address=192.168.2.107”. The port number used in the current two-node cluster is “ip_port=7777”. Finally , identifying which OCCFS2 cluster to use which in our case is named “cluster=ocfs2”.

#etc/init.d/o2cb offline ocfs2

Stopping O2CB cluster ocfs2:OK

The above command will offline the cluster we created, ocfs2.

# /etc/init.d/o2cb unload

Unmounting ocfs2_dlmfs filesystem: OK

Unloading module “ocfs2_dlmfs” : OK

Unmounting configfs filesystem : OK

Unloading module “configfs” : OK

The above command will  unload all OCFS2 modules.

# /etc/init.d/o2cb configure

Configuring the O2CB driver.

This will configure the on-boot properties of the O2CB driver.

The following questions will determine whether the driver is loaded on boot. The current values will be shown in brackets (‘[]’). Hitting  <enter> without typing an answer will keep that current value. Ctrl-C will abort.

Load O2CB driver on boot (y/n) [n] : y

Cluster  to start on boot (Enter “none” to clear) [ocfs2] : ocfs2

Specify heartbeat dead threshold (>=7) [7] : 61

Specify network idle timeout in ms (>=5000) [10000] : 10000

Specify network keepalive delay in ms (>=1000) [5000] : 5000

Specify network reconnect delay in ms (>=2000) [2000] : 2000

Writing O2CS configuration : OK

Loading module “configfs” : OK

Mounting configfs filesystem at /config :OK

Loading module “ocfs2_nodemanager” :OK

Loading module “ocfs2_dlm” : OK

Loading module “ocfs_dlmfs” : OK

Mounting ocfs2_dlmfs filesystem at / dlm : ok

Starting O2CB cluster ocfs2 : OK

 Mount the OCFS2 File system on New RAC nodes

# mount –t ocfs2 –o datavolume, nointr –L “oracrsfiles”  /u02

Configure OCFS2 to Mount Automatically at Startup on new RAC nodes:

# vi /etc/fstab

LABEL=oracrsfiles                             /u02                       ocfs2     _netdev,datavolume,nointr       0   0

# chkconfig  - -list o2cb

O2cb      0:off      1:off      2:on       3:on       4:on       5:on       6:off

Install ASMLib 2.0 Packages

 The installation process is simply a matter of running the following command on the new Oracle RAC node as the root user account:

#rpm –Uvh oracleasm-2.6.9-55.EL-2.0.3-1.i686.rpm \

Oracleasmlib-2.0.2-1.i386.rpm \

Oracleasm-support-2.0.3-1.i386.rpm

Preparing ……..                     ########################################[100%]

1 : oracleasm-support       #########################################[33%]

2 : oracleasm-2.6.9-55.EL ######################################### [67%]

3 : oracleasmlib                   #########################################[100%]

Configure and loading the ASMLib 2.0 Packages

Run the following as root on the new Oracle RAC node:

# /etc/init.d/oracleasm configure

Configuring the oracle ASM library driver

This will configure the on-boot properties of the oracle ASM library driver. The following questions will determine whether the driver is loaded on boot  and what permissions it will have. The current values will be shown in brackets (‘[]’). Hitting <ENTER>  without typing an answer will keep that current value. Ctrl-C will abort.

Default user to own the driver interface []: oracle

Default group to own the driver interface []: oinstall

Start Oracle ASM library driver on boot (y/n) [n] : y

Fix  permissions of Oracle ASM disks on boot (y/n) [y]: y

Writing Oracle ASM library driver configuration : [ OK ]

Creating /dev/oracleasm mount point : [ OK]

Loading module “oracleasm” : [ OK]

Mounting  ASMlib driver filesystem : [ OK]

Scanning system for ASM disks: [ OK]

Scan for ASM Disks

 # etc/init.d/oracleasm scandisks

Scanning system for ASM disks [ok]

# etc/init.d/oracleasm listdisks

Vol1

Vol2

Vol3

Configure Oracle Clusterware on the New Node on Linux1

$ cd /u01/app/crs/oui/bin

$ ./addNode.sh

Screen Name	Response
Welcome screen	Click Next
Specify cluster Nodes to add to Installation	Public Node Name Private Node Name Virtual Node Name linux3 linux3-priv linux3-vip Click Next to continue
Cluster Node Additional Summary	Verify the new oracle RAC node is listed under the “New Nodes” drilldown. Click Install  to start the installation!
Executive Configuration Scripts	From linux3 Navigate to the /u01/app/oracle/oraInventory directory on linux3 and run orainstRoot.sh as the “root” user account. Finally, navigate to the /u01/app/crs directory on linux3 and run root.sh as the “root” user account. The given interface(s), “eth0” is not public. Public interfaces should be used to configure virtual IPs #$ORA_CRS_HOME/bin/vipca Welcome: Click Next Network interfaces : Select only the public interface-eth0 Virtual IPs for cluster nodes: Node name : linux1 IP Alias Name : linux1-vip IP Address : 192.168.1.200 Subnet Mask : 255.255.255.0 Node name : linux2 IP Alias Name : linux2-vip IP Address : 192.168.1.201 Subnet Mask : 255.255.255.0 Node name : linux3 IP Alias Name : linux3-vip IP Address : 192.168.1.207 Subnet Mask : 255.255.255.0 Summary : Click Finish Configuration Assistant Progress Dialog : Click OK after configuration is complete Configuration Results : Click Exit Go back to the OUI and acknowledge the “Executive Configuration Scripts” dialog window
End of Installation	At the end of the installation, exit  from the OUI

Check Cluster Nodes:

$ $ORA_CRS_HOME/bin/olsnodes –n

linux1    1

linux2    2

linux3    3

Confirm Oracle Clusterware Function

$ $ORA_CRS_HOME/bin/crs_stat   -t  -v

Check CRS Status

CSS appears healthy

CRS appears healthy

EVM appears healthy

Check Oracle Clusterware Auto-Start Scripts on New Node (linux3)

$ 1s    -1  /etc/init.d/init.*

-rwxr-xr-x           1 root   root  2236  Feb  23   19:16  /etc/init.d/init.crs

-rwxr-xr-x           1 root   root  5252  Feb  23   19:16  /etc/init.d/init.crsd

-rwxr-xr-x           1 root   root  44589  Feb  23   19:16  /etc/init.d/init.cssd

-rwxr-xr-x           1 root   root  3669  Feb  23   19:16  /etc/init.d/init.evmd

Install Oracle Database Software on the New Node

$ORACLE_HOME/oui/bin directory from linux1;

$ hostname

linux1

$ cd/u01/app/oracle/product/10.2.0/db_1/oui/bin

$ ./addNode.sh

Screen Name	Response
Welcome Screen	Click Next
Specify Cluster Nodes to Add to Installation	By default linux3 is selected. Verify linux3 is selected (checked) and click  Next  to continue.
Cluster Node Additional Summary	Verify the new oracle RAC node is listed under the “New Nodes” drilldown. Click Install  to start the installation.
Execute Configuration Scripts	Navigate to the /u01/app/oracle/product/10.2.0/db 1 directory on linux3  and run root.sh  as the “root” user account. After running the root.sh script on the new Oracle RAC node, go back to the OUI and acknowledge the “Execute Configuration scripts” dialog window.
End of installation	At the end of the installation, exit  from the OUI

Run the Network Configuration Assitant:

To start the NETCA, run the following from linux1:

$ netca &

Screen Name	Response
Select the Type of Oracle Net Services Configuration	Select Cluster Configuration
Select the nodes to configure	Only  select the new oracle RAC node: linux3
Type of Configuration	Select Listener configuration
Listener Configuration Next 6 screens	What do you want to do : Add Listener name : Listener Selected protocols:TCP Port Number : 1521 Configure another listener :No Listener configuration complete : [Next] You will be returned to this welcome (Type of configuration)screen
Type of configuration	Select Naming Methods Configuration
Naming Methods Configuration	The following screens are :  Selected Naming Methods : Local Naming Naming Methods configuration complete ! [Next] You will be returned to this welcome (Type of configuration)screen
Type of configuration	Click Finish  to exit the NETCA

$ hostname

1inux1

$ ps  -ef | grep lsnr | grep  -v ‘grep’  | grep   -v  ‘ocfs’  | awk ‘ {print $9}’

LISTENER_LINUX1

$ hostname

1inux2

$ ps  -ef | grep lsnr | grep  -v ‘grep’  | grep   -v  ‘ocfs’  | awk ‘ {print $9}’

LISTENER_LINUX2

$ hostname

1inux3

$ ps  -ef | grep lsnr | grep  -v ‘grep’  | grep   -v  ‘ocfs’  | awk ‘ {print $9}’

LISTENER_LINUX3

Add Database Instance to New Node

To start the database instance creation process for the new oracle RAC node, run the following from linux1;

$ dbca &

Screen Name	Response
Welcome Screen	Select Oracle Real Application Clusters database
Operations	Select Instance Management
Instance Management	Select Add on instance
List of cluster databases	Orcl: Select this clustered database Username : sys Password : <sys_password>
List of cluster database instances	Verify this list is correct and Click Next to continue
Instance naming and node selection	The default values are correct (instance name “orcl3” to be added to node “linux3” ). After verifying these values, Click Next  to continue.
Database Services	The existing clustered database has one service defined named orcl taf. With the “oracl_taf” database service selected, change the details to Preferred  for the new instance (orcl3) and the “TAF Policy” set to Basic
Instance Storage	By default, the DBCA does a good job of determining the instance specific files such as an UNDO tablespace (UNDOTBS3), database files for this tablespace, and two redo log groups. Verify the storage options and Click Finish  to add the instance
Database Configuration Assistant : Summary	After verifying the instance creation options in the summary dialog, Click Ok  to begin the instance management process
Extend ASM	“ASM is present on the cluster but needs to be extended to the following nodes: [linux3]. Do you want ASM to be extended?” Click on Yes to add the ASM instance to the new node
Database Configuration assistant progress screen	“Do you want to perform another operation?” Click No  to end and exit the DBCA utility
Start New Database Services	The DBCA will automatically start the new instance (orcl3) on the node linux3. If any services were configured during the instance management process, however, they are left in an offline state. For the purpose of this example , I had to manually start the “oracl_taf” service for the database: $ srvctl start service –s orcl_taf –d orcl -i orcl3

Verify New instance

Login  to one of the instances and query the qv$instance view:

SQL> select inst_id, instance_name, status, to_char(startup_time, ‘DD-MON-YYYY HH24:MI:SS’) from  gv$instance order by inst_id;

Inst_ID                 Instance_Name                                Status                   TO_CHAR (STARTUP_TIME)

--------------         ---------------------                             ----------                               ----------------------------------------

1                              orcl1                                      OPEN                    23-FEB-2008  00:10:16

2                              orcl2                                      OPEN                    23-FEB-2008  00:10:47

3                              orcl1                                      OPEN                    23-FEB-2008  00:52:51

Remove Database Instance

To start the database instance removal process, run the following from linux1:

$ dbca &

Screen Name	Response
Welcome Screen	Select Oracle real Application clusters database
Operations	Select Instance Management
Instance Management	Select Delete an instance
List of cluster databases	Orcl: select this clustered database. At the bottom of this screen , Username :sys Password: <sys_password> Click Next to continue
List of cluster database instances	Select (linux3:orcl3) and Click Next  to continue
Database Services	The existing clustered database has one service defined named orcl taf. With the “orcl_taf”  database service selected  ,change the details to Not Used for the instance to be removed (orcl3)and leave the “TAF Policy” set to Basic. Click Finish to remove the instance
Database Configuration Assistant : Summary	After verifying the instance removal options in the summary dialog, click finish
Database Configuration assistant	Do you want to proceed?” Click ok to acknowledge this dialog
Database Configuration Assistant progress screen	“Do you want to perform another operation”? Click No to end and exit the DBCA utility

Verify Database Instance and Associated Objects were Removed

Check Cluster Services

$   $ORA-CRS_HOME/bin/crs_stat   -t

Verify Instance we removed:

Login to one of the instances and query the gv$instance view:

SQL>  select inst_id, instance_name,status, to_char(startup_time, ‘DD-MON-YYYY HH24:MI:SS’) from gv$instance order by inst_id;

Verify Database Files /Tablespaces have been Removed

Check that the UNDO tablespace that was defined for orcl3(UNDOTBS3) has been removed.

SQL> select tablespace_name from dba_tablespace order by 1;

Update TNSNAMES

Login to all machines that were accessing the instance removed and update the tnsnames.ora file (if necessary)

Remove the ASM Instance from the Oracle Cluster Registry File

Run the following from linux1 as the oracle user account:

$ srvct1 stop asm   -n    linux3

$ srvct1 remove asm   -n   linux3

Remove ASM Directories  from the Node to be deleted

Remove all ASM directories from the node to be deleted (linux3) as the oracle user account:

$ cd $ORACLE_BASE/admin

$ rm   -rf   +ASM

$ cd $ORACLE_HOME/dbs

$ rm –f   * ASM*

Remove ASMLib from the Node to be deleted

We will remove the ASMLib kernel driver, supporting  software and associated directories. Run the following from the node to be deleted (linux3) as the root user account.

# /etc/init.d/oracleasm stop

Unmounting ASMlib driver filesystem: [ok]

Unloading module “oracleasm” : [ok]

# rpm   -qa   |  grep oracleasm

oracleasm-support-2.0.3-1

oracleasm-2.6.9-55.EL-2.0.3-1

oracleasmlib-2.0.2-1

#rpm   -ev oracleasm-support-2.0.3-1 oracleasm-2.6.9-55.EL-2.0.3-1 oracleasmlib-2.0.2-1

Warning : /etc/sysconfig/oracleasm saved as /etc/sysconfig/oracleasm.rpmsave

#rm –f /etc/sysconfig/oracleasm.rpmsave

#rm –f /etc/rc.d/init.d/oracleasm

#rm –f /etc/rc0.d/init.d/*oracleasm*

#rm –f /etc/rc1.d/init.d/*oracleasm*

#rm –f /etc/rc2.d/init.d/*oracleasm*

#rm –f /etc/rc3.d/init.d/*oracleasm*

#rm –f /etc/rc4.d/init.d/*oracleasm*

#rm –f /etc/rc5.d/init.d/*oracleasm*

#rm –f /etc/rc6.d/init.d/*oracleasm*

Remove the Listener From the node to be deleted

To start the NETCA, run the following from linux1;

$ netca &

Screen Name	Response
Select the Type of oracle Net Services Configuration	Select Cluster configuration
Select the nodes to configure	Only select the oracle RAC node to remove the listener from :  linux3
Type of configuration	Select Listener configuration
Listener Configuration Next 4 screens	What do you want to do : Delete Select a listener you want to delete : Listener Acknowledge warning dialog: Yes Listener deleted [Next] Listener configuration complete ! [Next] You will be returned to this welcome (Type of configuration) screen
Type of configuration	Click Finish  to exit the NETCA

Verify TNS Listener Configuration

The Oracle node-specific TNS listener processes should now only be running on two nodes in the RAC cluster (linux1 and linux2)

$ hostname

linux1

$ ps  -ef  | grep lsnr | grep  -v ‘grep’ | grep –v ‘ocfs’  | awk ‘ {print $9}’

Listener_Linux1

$ hostname

linux2

$ ps  -ef  | grep lsnr | grep  -v ‘grep’ | grep –v ‘ocfs’  | awk ‘ {print $9}’

Listener_Linux2

$ hostname

Linux3

$ ps  -ef  | grep lsnr | grep  -v ‘grep’ | grep –v ‘ocfs’  | awk ‘ {print $9}’

Remove the Oracle Database Sofware From the Node to be deleted

Update Node List for oracle Database software – (Remove linux3)

From the node to be deleted (linux3) , run the OUI as the oracle user account to update the inventory node list for the oracle Database software:

$ cd $ORACLE_HOME/oui/bin

$ ./runinstaller –updateNodeList ORACLE_HOME=$ORACLE_HOME CLUSTER_NODES=”” – local

Starting Oracle Universal  Installer…..

No pre-requisite checks found in oraparam.ini, no system pre-requisite checks will be executed.

The inventory pointer is located at /etc/oraInst.loc

The inventory is located at /u01/app/oracle/oraInventory

‘UpdateNodeList’ was successful.

De-install Oracle Database Software

From linux3 as the oracle user account, run the following:

$ cd $ORACLE_HOME/oui/bin

$ ./runInstaller

Screen name	Response
Welcome Screen	Click the Intalled Products button.
Inventory: Contents Tab	Check the Oracle home to deleted (OraD10g homel) and click the Remove  button
Confirmation	Acknowledge the warning dialog by clicking Yes
Deinstallation Process	After confirming the Oracle Database software (Oracle home) was successfully removed. Click Close to exit this dialog.
Welcome Screen	Click Cancel to exit th OUI

Update Node List for remaining Nodes in the Cluster

Finally , from linux1 logged in as the oracle user account, update the Oracle Clusterware inventory node list for all nodes that will remain in the cluster.

$ cd $ORACLE_HOME/oui/bin

$ ./runInstaller –updateNodeList ORACLE_HOME=$ORACLE_HOME

“CLUSTER_NODES=(linux1,linux2)”

Starting Oracle is universal Installer……

 No pre-requisite checks found in oraparam.ini, no system pre-requiste checks will be executed.

The inventory pointer is located at /etc/oraInst.loc

The inventory is located at /u01/app/oracle/oraInventory

‘UpdateNodeList’ was successful .

Remove the Node to be deleted from Oracle Clusterware

Remove Node-Specific Interface Configuration

Run these commands from linux3 as the oracle user account;

$ $ORA_CRS_HOME/bin/racgons remove_config linux3:6200

Disable Oracle Clsterware Applications

From  the node you are deleting from the cluster(linux3) , run the script

$ORA_CRS_HOME/install/rootdelete.sh to disable the Oracle Clusterware applications that are on the node. This script should only be run once. Given the Clusterware software install is on local disk (non shared), make certain to use the nosharedhome  argument. The default for this script is sharedhome which prevents you from updating the permissions of local files such that they can be removed by the oracle user account.

Running  this script will stop the CRS stack and delete the ocr.loc file on the node to be removed. The nosharedvar option assumes the ocr.loc  file is not on a shared file system.

While  logged into linux3 as the root user account, run the following:

$ su

# cd $ORA_CRS_HOME/install

# ./rootdelete.sh local nosharedvar nosharedhome

CRS-0210: Could not find resource ‘ora.linux3.LISTENER_LINUX3.lsnr’.

Shutting down Oracle Cluster Ready Services (CRS):

Stopping resources. This could take several minutes.

Successfully stopped CRS resources.

Stopping CSSD.

Shutting down CSS daemon.

Shutdown request successfully issued.

Shutdown has begun. The daemons should exit soon.

Checking to see if oracle CRS stack is down…

Oracle CRS stack is not running.

Oracle CR stack is down now.

Removing script for oracle Cluster Ready services

Updating ocr file foe downgrade

Cleaning up SCR settings in ‘/etc/oracle/scls_scr’

Delete Node from Cluster and update OCR

Upon successful completion of the rootdelete.sh script, run the rootdeletenode.sh script to delete the node(linux3)  from the Oracle Cluster and to update the Oracle Cluster  and to update the Oracle Cluster Registry(OCR). This script should be run from a pree-existing /available node in the cluster(linux1) as the root  user account.

Before executing rootdeletenode.sh, we need to know the node number associated with the node name to be deleted from the cluster. To determine the node number,  run the following command as the oracle user account from linux1:

$ $ORA_CRS_HOME/bin/olsnodes –n

linux1  1

linux2  2

linux3  3

From the listing above, the node number for linux3 is 3.

While logged into linux1 as the root  user account,run the following using the name linux3 and the node number 3.

$ su

# cd $ORA_CRS_HOME/Install

# ./rootdeletenode.sh linux3,3

CRS-0210: Could not find resource ‘ora.linux3.LISTENER_LINUX3.lsnr’.

CRS-0210: Could not find resource ‘ora.linux3.ons’.

CRS-0210: Could not find resource ‘ora.linux3.vip’.

CRS-0210: Could not find resource ‘ora.linux3.gsd’.

CRS-0210: Could not find resource ‘ora.linux3.vip.

CRS  nodeapps are deleted successfully

Clscfg: EXISTING configuration version 3 detected.

Clscfg: Version 3 is LOG  Release 2.

Successfully deleted 14 values  from OCR.

Key SYSTEM.css.interfaces.nodelinux3 marked for deletion is not there. Ignoring ,

Successfully deleted 5 keys from OCR.

Node deletion operation successful.

‘linux3,3’ deleted successfully

To verify that the node was successfully removed , use the following as either the Oracle or root user:

$ $ORA_CRS_HOME/bin/olsnodes   -n

linux1  1

linux2  2

Update Node list for oracle Clusterware Software – (Remove linux3)

From the node to be deleted (linux3), run the OUI as the oracle  user account to update the inventory node list for the Oracle Clusterware Software:

$ cd $ORA_CRS_HOME/oui/bin

$ ./runInstaller –updateNodelist ORACLE_HOME=$ORA_CRS_HOME CLUSTER_NODES=”” –local CRS=true

Starting Oracle universal Installer……

No pre-requisite checks found in oraparam.ini, no system pre-requisite checks will be executed.

The inventory pointer is located at /etc/oraInst.loc

The inventory is located at /u01/app/oracle/oraInventory

‘UpdateNodeList’ was successful

De-install Oracle Clusterware Software

From linux3  as the oracle user account, run the following:

$ cd $ORA_CRS_HOME/oui/bin

$ ./runInstaller

Screen Name	Response
Welcome screen	Click the Installed Products button
Inventory: Contents Tab	Check the oracle home to deleted (OraCrs10g home)  and click the Remove  button
Confirmation	Acknowledge the warning dialog by clicking Yes to remove the Oracle Clusterware software and to remove the /u01/app/crs directory
Deinstallation Process	After confirming the Oracle Clusterware software (Clusterware home) was successfully removed. Click Close to exit this dialog
Welcome Screen	Click Cancel to exit the OUI

Update Node List for remaining Nodes in the Cluster

Finally , from linux1 logged in as the oracle user account (and user equivalence enabled), update the Oracle Clusterware inventory node list for all nodes that will remain in the cluster.

$ cd $ORACLE_HOME/oui/bin

$ ./runInstaller –updateNodeList ORACLE_HOME=$ORA_CRS_HOME

“CLUSTER_NODES=(linux1,linux2)” CRS=true

Starting Oracle is universal Installer……

 No pre-requisite checks found in oraparam.ini, no system pre-requiste checks will be executed.

The inventory pointer is located at /etc/oraInst.loc

The inventory is located at /u01/app/oracle/oraInventory

‘UpdateNodeList’ was successful .

Verify Node to be deleted is no longer a Member of the Cluster

$ $ORA_CRS_HOME/bin/olsnodes  -n

linux1  1

linux2  2

Remove /Rename any remaining Oracle Files from Node to be deleted

From the node to be deleted (linux3)  remove /rename any remaining Oracle files while logged in as the root  user account.

# mv  - f /etc/inittab.no_crs /etc/inittab

# rm  -f /etc/inittab.orig

# rm  -f /etc/inittab.crs

# rm  -rf /etc/oracle

# rm  -f /etc/oratab

# rm  -f /etc/oraInst.loc

# rm  -rf /etc/ORCLcluster

# rm  -rf /u01/app/oracle

# rm  -rf /u01/app/crs

# rm  -f /user/local/bin/coraenv

# rm  -f /user/local/bin/dbhome

# rm  -f /user/local/bin/oraenv

Finally, remove the Oracle user account and all associated UNIX groups from linux3.

# userdel –r oracle

# groupdel oinstall

# groupdel dba

Remove OCFS2 from the Node to be deleted

In this session we will remove the OCFS2 Kernel driver, supporting software, and associated directories. Run the following from the node to be deleted (linux3) as the root user account.

# umount /u02

# /etc/init.d/o2cb offline ocfs2

# /etc/init.d/o2cb unload

# rpm –qa  | grep ccfs2

Ocfs2-tools-1.2.4-1

Ocfs2-2.6.9-55.EL-1.2.5-6

Ocfs2console-1.2.4-1

# rpm  -ev ocfs2-tools -1.2.4-1 ocfs2-2.6.9-55.EL-1.2.5-6 ocfs2console-1.2.4-1

Warning : /etc/sysconfig/o2cb saved as /etc/sysconfig/o2cb.rpmsave

# rm –f /etc/sysconfig/o2cb.rpmsave

#rm –rf /etc/ocfs2

Remove Node to be deleted from the OCFS2 Cluster

The next step is to remove the Oracle RAC node being deleted (linux3) from the “live” OCFS2 cluster. This entails running the o2cb_ctl command-line utlity from the remaining two RAC nodes linux1 and linux2

As root, run the following from linux1 and then linux2:

[root@linux1 ~] # o2cb_ctl – D  – u  -n linux3

O2cb_ctl: Not yet supported

[root@linux12~] # o2cb_ctl – D  – u  -n linux3

O2cb_ctl: Not yet supported

O2cb_ctl parameters:

-D:          Delete an object from the existing OCFS2 cluster configuration.

-u:          Valid only with –D. When deleting  something (node or cluster), it will also remove it from the live cluster (/config). If the parameter is not specified , then only update the /etc/ocfs2/cluster.conf.

-n:          Object name which is usally the node name or cluster name.

Remove OCFS2 Mount options from /etc/fstab

Finally , remove the following entry from the /etc/fstab from linux3;

LABEL=oracrsfiles  /u02  ocfs2   -netdev,datavolume,nointr

Disable iSCSI Initiator Service on linux3

First , stop the iscsi initiator service on  linux3;

#  service iscsi stop

Searching for iscsi-based multipath maps

Found 0 maps

Stopping   iscsid: [OK]

Removing iscsi driver : [OK]

Next, edit the file /etc/iscsi.conf on linux3 and comment out(or remove) the Discovery Address entry which specifies the hostname of the Openfiler network storage server. In our example, use # at the beginning  of Discovery Address entry as shown below:

# DiscoveryAddress=openfiler1-priv

Finally , configure the ISCSI service to not be active across machine reboots on linux3.  The Linux command chkconfig  can be used to achieve that as follows:

#  chkconfig –level 345 iscsi off