tag:blogger.com,1999:blog-90817893657744418392024-02-21T20:13:32.555+05:30Yet another technology blog.Ajay Singh Chandihttp://www.blogger.com/profile/05807334441165607306noreply@blogger.comBlogger136125tag:blogger.com,1999:blog-9081789365774441839.post-76747093420812679842014-01-22T19:22:00.000+05:302014-01-22T19:22:10.741+05:30Flashback Data Archive (Oracle Total Recall) 11g.<pre>
Starting from 11g Oracle has added another tool to its bundle of flashback technologies called Flashback
Data Archive(Oracle Total Recall).
With this feature previous state of a table can be viewed. But Unlike Flashback query
and or Flashback transaction query this feature does not depend on the undo_retention
of the database.
Flashback Data Archive requires one or more tablespaces where it can store historical data
for one or several tables.
whenever the data in the table is modified its original unmodified value is written to the undo
tablespace. From there the Flashback Data Archiver Process(FBDA) will collect it and write
the data to flashback data archive.
#Create a Tablespace for the Flashback Data Archive
SQL> create tablespace fda datafile '/u01/fda01.dbf' size 500M
autoextend on next 100M
extent management local
segment space management auto;
SQL> select * from dba_sys_privs where privilege like '%FLASH%';
<code>GRANTEE PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
SCOTT FLASHBACK ARCHIVE ADMINISTER NO
SYS FLASHBACK ANY TABLE NO
DBA FLASHBACK ANY TABLE YES
SYS FLASHBACK ARCHIVE ADMINISTER NO
DBA FLASHBACK ARCHIVE ADMINISTER YES
MDSYS FLASHBACK ANY TABLE NO
6 rows selected.</code>
SQL> grant flashback archive administer to scott;
<code>Grant succeeded.</code>
SQL> create flashback archive flash_data
tablespace fda
quota 4g
retention 5 year;
<code>Flashback archive created.</code>
#Quota is optional.It means that FDA can only use upto 4Gb space in the tablespace.
SQL> drop flashback archive flash_data;
<code>Flashback archive dropped.</code>
<b>Modify commands</b>
#Make it the default archive
SQL> alter flashback archive flash_data set default;
<code>Flashback archive altered.</code>
#Add space to it.
SQL> alter flashback archive flash_data add tablespace abc;
<code>Flashback archive altered.</code>
#Change its quota
SQL> alter flashback archive flash_data modify tablespace fda quota 1G;
<code>Flashback archive altered.</code>
#Change retention
SQL> alter flashback archive flash_data modify retention 1 year;
<code>Flashback archive altered.</code>
#Remove tablespace from flashback data archive
SQL> alter flashback archive flash_data
2 remove tablespace abc;
<code>Flashback archive altered.</code>
#Purge data from flashback data archive
SQL> alter flashback archive flash_data purge all;
<code>Flashback archive altered.</code>
#Purge using interval clause
SQL> alter flashback archive flash_data purge before
2 timestamp(systimestamp - interval '5' day);
<code>Flashback archive altered.</code>
#Purge using SCN Clause
SQL> alter flashback archive flash_data purge before scn 799684;
<code>Flashback archive altered.</code>
<b>Playing with it.</b>
SQL> create table t1
2 (id int,name varchar2(30))
3 flashback archive flash_data;
<code>Table created.</code>
SQL> alter table emp flashback archive;
<code>Table altered.</code>
#Below in the column ARCHIVE_TABLE_NAME tables which have been created in the FBA tablespace can be seen.
SQL> select * from dba_flashback_archive_tables;
<code>TABLE_NAME OWNER_NAME FLASHBACK_ARCHIVE_NAME ARCHIVE_TABLE_NAME STATUS
------------------------------ ------------------------------ ------------------------------ ------------------------------
--------
T1 SCOTT FLASH_DATA SYS_FBA_HIST_74560
ENABLED
EMP SCOTT FLASH_DATA SYS_FBA_HIST_73181
ENABLED</code>
SQL> select flashback_archive_name, status from dba_flashback_archive;
<code>FLASHBACK_ARCHIVE_NAME STATUS
------------------------------ -------
FLASH_DATA DEFAULT</code>
SQL> alter table emp no flashback archive;
<code>Table altered.</code>
SQL> alter table emp flashback archive flash_data;
<code>Table altered.</code>
SQL> select * from dba_flashback_archive;
<code>OWNER_NAME FLASHBACK_ARCHIVE_NAME FLASHBACK_ARCHIVE# RETENTION_IN_DAYS CREATE_TIME LAST_PURGE_TIME
STATUS
---------- ---------------------- ------------------ ----------------- -----------------------------------
---------------------------------- -------
SYS FLASH_DATA 1 365 21-JAN-14 04.09.51.000000000 PM 21-JAN-14
04.23.59.000000000 PM DEFAULT</code>
<b>Values usable with systimestamp</b>
systimestamp - interval '60' second/minute/day/month
SQL> select * from dba_flashback_archive_ts;
<code>FLASHBACK_ARCHIVE_NAME FLASHBACK_ARCHIVE# TABLESPACE_NAME QUOTA_IN_MB
---------------------- ------------------ ------------------------------ ----------------------------------------
FLASH_DATA 1 FDA 1024</code>
SQL> select sal from emp where empno=7934;
<code> SAL
----------
1300</code>
SQL> update emp set sal=2000 where empno=7934;
<code>1 row updated.</code>
SQL> commit;
<code>Commit complete.</code>
SQL> select sal from emp where empno=7934;
<code> SAL
----------
2000</code>
#After modifying the we can see the previous state with the as of clause.
SQL> select * from emp as of timestamp(systimestamp - interval '10' minute) where empno=7934;
<code> EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7934 MILLER CLERK 7782 23-JAN-82 1300 10</code>
</pre>Ajay Singh Chandihttp://www.blogger.com/profile/05807334441165607306noreply@blogger.com0tag:blogger.com,1999:blog-9081789365774441839.post-81802124520580460952014-01-18T07:32:00.002+05:302014-03-03T20:05:38.262+05:30ADRCI Walkthrough.<pre>
Invoke adrci as the oracle user.
> adrci
<code>ADRCI: Release 11.2.0.3.0 - Production on Fri Jan 17 22:38:57 2014
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
ADR base = "/opt/oracle"</code>
adrci> show home
<code>ADR Homes:
diag/diagtool/user_oracle/host_365570786_11
diag/rdbms/orcl/ORCL
diag/asm/+asm/+ASM4
diag/tnslsnr/orcl4/listener_cad</code>
Set the home
adrci> set home diag/rdbms/orcl/ORCL
To check the alert log use the following option.
adrci> show alert -tail -f
<code>2014-01-17 22:28:35.440000 +00:00
ALTER SYSTEM ARCHIVE LOG
2014-01-17 22:28:38.414000 +00:00
Thread 4 advanced to log sequence 1392 (LGWR switch)
Current log# 11 seq# 1392 mem# 0: +REDO/orcl/redot4g11f1.rdo
Current log# 11 seq# 1392 mem# 1: +REDO/orcl/redot4g11f2.rdo
Archived Log entry 10628 added for thread 4 sequence 1391 ID 0xc5495541 dest 1:</code>
or
adrci> show alert -tail
or
adrci> show alert
To check for severe problems in database.
adrci> show problem
ADR Home = /opt/oracle/diag/rdbms/orcl/ORCL:
<code>*************************************************************************
PROBLEM_ID PROBLEM_KEY LAST_INCIDENT LASTINC_TIME
-------------------- ----------------------------------------------------------- -------------------- ----------------------------------------
2 ORA 600 [723] 3807 2007-06-18 21:35:47.862114 -07:00
1 ORA 29770 24129 2011-10-21 19:08:24.097000 +00:00
2 rows fetched</code>
To check for incedents related to problems.
adrci> show incident
<code>ADR Home = /opt/oracle/diag/rdbms/orcl/ORCL:
*****************************************************************************
INCIDENT_ID PROBLEM_KEY CREATE_TIME
----------------- ------------------------- ---------------------------------
3808 ORA 603 2007-06-18 21:35:49.322161 -07:00
3807 ORA 600 [723] 2007-06-18 21:35:47.862114 -07:00
3805 ORA 600 [723] 2007-06-18 21:35:25.012579 -07:00
3804 ORA 1578 2007-06-18 21:35:08.483156 -07:00
4 rows fetched</code>
To get more details on the incident execute the following
adrci> show incident -mode detail -p "incident_id=3807"
adrci> show trace <trace_file_location>
This statement lists the names of all trace files related to incident number 1681:
adrci> SHOW TRACEFILE -I 1681
adrci> show control
ADR Home = /opt/oracle/diag/rdbms/orcl/ORCL:
<code>*************************************************************************
ADRID SHORTP_POLICY LONGP_POLICY LAST_MOD_TIME LAST_AUTOPRG_TIME LAST_MANUPRG_TIME ADRDIR_VERSION ADRSCHM_VERSION ADRSCHMV_SUMMARY ADRALERT_VERSION CREATE_TIME
-------------------- -------------------- -------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- -------------------- -------------------- -------------------- -------------------- ----------------------------------------
3783208084 720 8760 2013-12-16 19:28:24.827055 +00:00 2014-01-17 21:09:07.527157 +00:00 2014-01-17 10:15:21.303696 +00:00 1 2 80 1 2013-12-16 19:28:24.827055 +00:00
1 rows fetched</code>
All the tracefiles will stay for 30 days (720 hours),
but all the incident files would be kept for one year (8760 hours) by default.
This can be changed by the following command
adrci> set control (SHORTP_POLICY = 48)
adrci> set control (LONGP_POLICY = 48)
Purging manually
Files can be purged manually by the following commands. The value "2880" is in minutes.
adrci> purge -age 2880 -type incident
adrci> purge -age 2880 -type ALERT
adrci> purge -age 2880 -type TRACE
adrci> purge -age 2880 -type CDUMP
adrci> purge -age 2880 -type HM
<b>Creation of Packages & ZIP files to send to Oracle Support</b>
adrci> set home diag/asm/+asm/+ASM1
adrci> show problem
<code>ADR Home = /opt/oracle/diag/asm/+asm/+ASM1:
*************************************************************************
PROBLEM_ID PROBLEM_KEY
-------------------- -----------------------------------------------------------
1 ORA 4030
1 rows fetched</code>
<code>ADR Home = /opt/oracle/diag/asm/+asm/+ASM1:
*************************************************************************
PROBLEM_ID PROBLEM_KEY
-------------------- -----------------------------------------------------------
1 ORA 4030
1 rows fetched</code>
adrci> ips create package problem 1 correlate all
<code>Created package 1 based on problem id 1, correlation level all</code>
#Create logical package with correlation level all.
adrci> ips generate package 1 in "/opt/oracle"
<code>Generated package 1 in file /opt/oracle/ORA4030_20140219202848_COM_1.zip, mode complete</code>
#Now create a zip file from the logical package
</pre>Ajay Singh Chandihttp://www.blogger.com/profile/05807334441165607306noreply@blogger.com0tag:blogger.com,1999:blog-9081789365774441839.post-77556119542178858342013-11-30T19:01:00.000+05:302013-11-30T19:01:21.653+05:30Oracle Undo Tablespace Advisor(DBMS_UNDO_ADV).<pre>
As per Doc ID 1580225.1
The package DBMS_UNDO_ADV is undocumented, and it is used internally by the Undo Advisor .
dbms_undo_adv package gives advise based on historical information present in memory or Automatic Workload
Repository. The default retention of AWR is 7 days.
<b>Function longest_query : </b>Returns the length of the longest query for a given period .
<b>Method 1</b>
SELECT dbms_undo_adv.longest_query LONGEST_QUERY FROM dual;
<b>Method 2 (using Start/End time)</b>
SELECT dbms_undo_adv.longest_query(SYSDATE-1/24, SYSDATE) LONGEST_QUERY FROM dual;
<b>Method 3 (using Begin/End AWR snapshot id)</b>
SELECT dbms_undo_adv.longest_query(345, 768) LONGEST_QUERY FROM dual;
<b>Function required_retention: </b>returns the undo_retention value required for running the longest query.
<b>Method 1</b>
select dbms_undo_adv.required_retention from dual;
<b>Method 2 (using Start/End time)</b>
SELECT Ddbms_undo_adv.required_retention(SYSDATE-1/24, SYSDATE) required_retention FROM dual;
<b>Method 3 (using Begin/End AWR snapshot id)</b>
SELECT dbms_undo_adv.longest_query(345, 768) LONGEST_QUERY FROM dual;
<b>Function best_possible_retention:</b> Returns the best possible undo retention which the current
undo tablespace can support.
<b>Note:</b>If undo tablespace autoextensible then "maxsize" setting will be taken into account to
calculate best_possible_retention.
<b>Method 1</b>
SELECT dbms_undo_adv.best_possible_retention best_retention FROM dual;
<b>Method 2 (using Start/End time)</b>
SELECT dbms_undo_adv.best_possible_retention(SYSDATE-1/24, SYSDATE) best_retention FROM dual;
<b>Method 3 (using Begin/End AWR snapshot id)</b>
SELECT dbms_undo_adv.best_possible_retention(345, 768) best_retention FROM dual;
<b>Function required_undo_size:</b> Returne the appropriate undo tablespace size to satisfy undo retention value.
<b>Method 1</b>
SELECT dbms_undo_adv.required_undo_size(900) required_undo_size FROM dual;
<b>Method 2 (using Start/End time)</b>
SELECT dbms_undo_adv.required_undo_size(900,SYSDATE-1/24, SYSDATE) required_undo_size FROM dual;
<b>Method 3 (using Begin/End AWR snapshot id)</b>
SELECT dbms_undo_adv.required_undo_size(900, 345, 768) required_undo_size FROM dual;
</pre>Ajay Singh Chandihttp://www.blogger.com/profile/05807334441165607306noreply@blogger.com0tag:blogger.com,1999:blog-9081789365774441839.post-38785374710189926072013-11-25T08:59:00.000+05:302013-11-25T08:59:10.800+05:30Starting And Stopping OEM/OMS 12c Agent and Server.<pre>
[oracle@afr bin]$ pwd
<code>/u01/app/oracle/Middleware/agent/agent_inst/bin</code>
[oracle@afr bin]$ ./emctl stop agent
<code>Oracle Enterprise Manager 12c Cloud Control 12.1.0.1.0
Copyright (c) 1996, 2012 Oracle Corporation. All rights reserved.
Stopping agent ..... stopped.</code>
[oracle@afr bin]$ ./emctl start agent
<code>Oracle Enterprise Manager 12c Cloud Control 12.1.0.1.0
Copyright (c) 1996, 2012 Oracle Corporation. All rights reserved.
Starting agent ........................................... started.</code>
[oracle@afr bin]$ pwd
<code>/u01/app/oracle/Middleware/oms/bin</code>
[oracle@afr bin]$ ./emctl stop oms -all
<code>Oracle Enterprise Manager Cloud Control 12c Release 12.1.0.1.0
Copyright (c) 1996, 2012 Oracle Corporation. All rights reserved.
Stopping WebTier...
WebTier Successfully Stopped
Stopping Oracle Management Server...
Oracle Management Server Successfully Stopped
AdminServer Successfully Stopped
Oracle Management Server is Down</code>
</pre>Ajay Singh Chandihttp://www.blogger.com/profile/05807334441165607306noreply@blogger.com0tag:blogger.com,1999:blog-9081789365774441839.post-15031130989251304912013-11-09T18:19:00.001+05:302013-11-09T18:19:31.072+05:30How To Find Views And What Information They Provide In Oracle Database ?<pre>
SQL> desc dba_views;
<code>Name Null? Type
----------------------------------------- -------- ----------------------------
OWNER NOT NULL VARCHAR2(30)
VIEW_NAME NOT NULL VARCHAR2(30)
TEXT_LENGTH NUMBER
TEXT LONG
TYPE_TEXT_LENGTH NUMBER
TYPE_TEXT VARCHAR2(4000)
OID_TEXT_LENGTH NUMBER
OID_TEXT VARCHAR2(4000)
VIEW_TYPE_OWNER VARCHAR2(30)
VIEW_TYPE VARCHAR2(30)
SUPERVIEW_NAME VARCHAR2(30)
EDITIONING_VIEW VARCHAR2(1)
READ_ONLY VARCHAR2(1)</code>
SQL> select view_name from dba_views where view_name like'%ROLE%';
<code>VIEW_NAME
------------------------------
V$XS_SESSION_ROLE
DBA_APPLICATION_ROLES
USER_APPLICATION_ROLES
EXU9NTAROLE
EXU81APPROLE
DBA_ROLES
DBA_ROLE_PRIVS
USER_ROLE_PRIVS
ROLE_ROLE_PRIVS
ROLE_SYS_PRIVS
ROLE_TAB_PRIVS
VIEW_NAME
------------------------------
EXU10NTAROLE
SESSION_ROLES
XS_SESSION_ROLES
DBA_SCHEDULER_JOB_ROLES
KU$_DEFROLE_LIST_VIEW
KU$_DEFROLE_VIEW
KU$_DUMMY_ROLE_VIEW
KU$_PROXY_ROLE_LIST_VIEW
KU$_ROLE_VIEW
DBA_CONNECT_ROLE_GRANTEES
PROXY_ROLES
VIEW_NAME
------------------------------
PROXY_USERS_AND_ROLES
ROLE_WM_PRIVS
MGMT$ESA_DBA_ROLE_REPORT
25 rows selected.</code>
SQL> desc dict;
<code> Name Null? Type
----------------------------------------- -------- ----------------------------
TABLE_NAME VARCHAR2(30)
COMMENTS VARCHAR2(4000)</code>
SQL> select * from dict where table_name like '%ROLE%';
<code>TABLE_NAME
------------------------------
COMMENTS
--------------------------------------------------------------------------------
DBA_ROLES
All Roles which exist in the database
DBA_ROLE_PRIVS
Roles granted to users and roles
USER_ROLE_PRIVS
Roles granted to current user
TABLE_NAME
------------------------------
COMMENTS
--------------------------------------------------------------------------------
DBA_SCHEDULER_JOB_ROLES
All scheduler jobs in the database by database role
DBA_CONNECT_ROLE_GRANTEES
Information regarding which users are granted CONNECT
ROLE_ROLE_PRIVS
Roles which are granted to roles
TABLE_NAME
------------------------------
COMMENTS
--------------------------------------------------------------------------------
ROLE_SYS_PRIVS
System privileges granted to roles
ROLE_TAB_PRIVS
Table privileges granted to roles
SESSION_ROLES
Roles which the user currently has enabled.
9 rows selected.</code>
</pre>Ajay Singh Chandihttp://www.blogger.com/profile/05807334441165607306noreply@blogger.com0tag:blogger.com,1999:blog-9081789365774441839.post-49402521027716261952013-10-22T06:26:00.000+05:302013-10-22T06:26:56.404+05:30Create a System Trigger to Enable SQL TRACE for a session..<pre>
The following script will enable session trace for the user scott.
[oracle@canada u01]$ cat trace_trigger.sql
<code>set echo on
spool trace_trigger.log
DROP TRIGGER SYS.trace_trigger;
CREATE OR REPLACE TRIGGER sys.trace_trigger
After logon on database
Begin
if ( user='SCOTT') then
execute immediate 'alter session set sql_trace=true';
execute immediate 'alter session set timed_statistics=true';
execute immediate 'alter session set tracefile_identifier="SCOTT"';
execute immediate 'alter session set max_dump_file_size=unlimited';
execute immediate 'alter session set events ''10046 trace name context forever, level 12''';
End if;
End;
/
spool off;</code>
[oracle@canada u01]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Tue Oct 22 06:04:31 2013
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> @trace_trigger.sql
SQL>
SQL> spool trace_trigger.log
SQL>
SQL> DROP TRIGGER SYS.trace_trigger;
DROP TRIGGER SYS.trace_trigger
*
ERROR at line 1:
ORA-04080: trigger 'trace_trigger' does not exist
SQL>
SQL> CREATE OR REPLACE TRIGGER sys.trace_trigger
2 After logon on database
3 Begin
4 if ( user='SCOTT') then
5 execute immediate 'alter session set sql_trace=true';
6 execute immediate 'alter session set timed_statistics=true';
7 execute immediate 'alter session set tracefile_identifier="SCOTT"';
8 execute immediate 'alter session set max_dump_file_size=unlimited';
9 execute immediate 'alter session set events ''10046 trace name context forever, level 12''';
10 End if;
11 End;
12 /
Trigger created.
SQL>
SQL> spool off;
SQL>
SQL> select status from dba_triggers where trigger_name like'%TRACE_TRIGGER%';
<code>STATUS
--------
ENABLED</code>
To disable the trigger.
SQL> alter trigger trace_trigger disable;
<code>Trigger altered.</code>
SQL> select status from dba_triggers where trigger_name like'%TRACE_TRIGGER%';
<code>STATUS
--------
DISABLED</code>
To enable the trigger.
SQL> alter trigger trace_trigger enable;
<code>Trigger altered.</code>
SQL> select status from dba_triggers where trigger_name like'%TRACE_TRIGGER%';
<code>STATUS
--------
ENABLED</code>
</pre>Ajay Singh Chandihttp://www.blogger.com/profile/05807334441165607306noreply@blogger.com0tag:blogger.com,1999:blog-9081789365774441839.post-46668703638502004042013-10-06T22:07:00.000+05:302013-10-06T22:07:43.414+05:30Rman Disaster Recovery Walkthrough.<pre>
On Source
Make a backup location.
<code>mkdir -p /u01/backup</code>
Maker sure database is in archive log mode.
Configure control file backup parameters in Rman.
RMAN> configure controlfile autobackup on;
<code>new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters are successfully stored</code>
RMAN> configure controlfile autobackup format for device type disk to '/u01/backup/cf%F';
<code>new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/u01/backup/cf%F';
new RMAN configuration parameters are successfully stored</code>
Take backup of database plus archivelog.
[oracle@canada ~]$ rman target /
<code>Recovery Manager: Release 11.2.0.1.0 - Production on Sat Oct 5 19:13:05 2013
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: DELL (DBID=3764293269)</code>
RMAN> run
2> {
3> allocate channel c1 device type disk format '/u01/backup/%U';
4> allocate channel c2 device type disk format '/u01/backup/%U';
5> allocate channel c3 device type disk format '/u01/backup/%U';
6> allocate channel c4 device type disk format '/u01/backup/%U';
7> backup database plus archivelog;
8> }
<code>using target database control file instead of recovery catalog
allocated channel: c1
channel c1: SID=51 device type=DISK
allocated channel: c2
channel c2: SID=28 device type=DISK
allocated channel: c3
channel c3: SID=49 device type=DISK
allocated channel: c4
channel c4: SID=34 device type=DISK
Starting backup at 05-OCT-13
current log archived
channel c1: starting archived log backup set
channel c1: specifying archived log(s) in backup set
input archived log thread=1 sequence=3 RECID=1 STAMP=828040174
channel c1: starting piece 1 at 05-OCT-13
channel c2: starting archived log backup set
channel c2: specifying archived log(s) in backup set
input archived log thread=1 sequence=4 RECID=2 STAMP=828040176
input archived log thread=1 sequence=5 RECID=3 STAMP=828040184
channel c2: starting piece 1 at 05-OCT-13
channel c3: starting archived log backup set
channel c3: specifying archived log(s) in backup set
input archived log thread=1 sequence=6 RECID=4 STAMP=828040185
input archived log thread=1 sequence=7 RECID=5 STAMP=828040187
channel c3: starting piece 1 at 05-OCT-13
channel c4: starting archived log backup set
channel c4: specifying archived log(s) in backup set
input archived log thread=1 sequence=8 RECID=6 STAMP=828040188
input archived log thread=1 sequence=9 RECID=7 STAMP=828040446
channel c4: starting piece 1 at 05-OCT-13
channel c1: finished piece 1 at 05-OCT-13
piece handle=/u01/backup/01ollp7v_1_1 tag=TAG20131005T191406 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:00
channel c2: finished piece 1 at 05-OCT-13
piece handle=/u01/backup/02ollp7v_1_1 tag=TAG20131005T191406 comment=NONE
channel c2: backup set complete, elapsed time: 00:00:00
channel c3: finished piece 1 at 05-OCT-13
piece handle=/u01/backup/03ollp7v_1_1 tag=TAG20131005T191406 comment=NONE
channel c3: backup set complete, elapsed time: 00:00:00
channel c4: finished piece 1 at 05-OCT-13
piece handle=/u01/backup/04ollp7v_1_1 tag=TAG20131005T191406 comment=NONE
channel c4: backup set complete, elapsed time: 00:00:01
Finished backup at 05-OCT-13
Starting backup at 05-OCT-13
channel c1: starting full datafile backup set
channel c1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/oradata/dell/system01.dbf
channel c1: starting piece 1 at 05-OCT-13
channel c2: starting full datafile backup set
channel c2: specifying datafile(s) in backup set
input datafile file number=00002 name=/u01/app/oracle/oradata/dell/sysaux01.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/dell/users01.dbf
channel c2: starting piece 1 at 05-OCT-13
channel c3: starting full datafile backup set
channel c3: specifying datafile(s) in backup set
input datafile file number=00005 name=/u01/app/oracle/oradata/dell/example01.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/dell/undotbs01.dbf
channel c3: starting piece 1 at 05-OCT-13
channel c3: finished piece 1 at 05-OCT-13
piece handle=/u01/backup/07ollp81_1_1 tag=TAG20131005T191408 comment=NONE
channel c3: backup set complete, elapsed time: 00:00:37
channel c2: finished piece 1 at 05-OCT-13
piece handle=/u01/backup/06ollp81_1_1 tag=TAG20131005T191408 comment=NONE
channel c2: backup set complete, elapsed time: 00:01:18
channel c1: finished piece 1 at 05-OCT-13
piece handle=/u01/backup/05ollp81_1_1 tag=TAG20131005T191408 comment=NONE
channel c1: backup set complete, elapsed time: 00:01:50
Finished backup at 05-OCT-13
Starting backup at 05-OCT-13
current log archived
channel c1: starting archived log backup set
channel c1: specifying archived log(s) in backup set
input archived log thread=1 sequence=10 RECID=8 STAMP=828040560
channel c1: starting piece 1 at 05-OCT-13
channel c1: finished piece 1 at 05-OCT-13
piece handle=/u01/backup/08ollpbg_1_1 tag=TAG20131005T191600 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:01
Finished backup at 05-OCT-13
Starting Control File and SPFILE Autobackup at 05-OCT-13
piece handle=/u01/backup/cfc-3764293269-20131005-00 comment=NONE
Finished Control File and SPFILE Autobackup at 05-OCT-13
released channel: c1
released channel: c2
released channel: c3
released channel: c4</code>
Take backup of spfile.
RMAN> backup spfile to destination '/u01/backup/';
<code>Starting backup at 05-OCT-13
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=49 device type=DISK
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 05-OCT-13
channel ORA_DISK_1: finished piece 1 at 05-OCT-13
piece handle=/u01/backup/DELL/backupset/2013_10_05/o1_mf_nnsnf_TAG20131005T192028_950694xh_.bkp tag=TAG20131005T192028 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 05-OCT-13
Starting Control File and SPFILE Autobackup at 05-OCT-13
piece handle=/u01/backup/cfc-3764293269-20131005-01 comment=NONE
Finished Control File and SPFILE Autobackup at 05-OCT-13</code>
DBID of database to be restored DBID=3764293269
On new host where database is to be restored
make required directories
[oracle@newyork ~]$ mkdir -p /u01/app/oracle/admin/dell/adump
[oracle@newyork ~]$ mkdir -p /u01/app/oracle/oradata/dell/
[oracle@newyork ~]$ mkdir -p /u01/app/oracle/flash_recovery_area/dell/
Copy backup files to the destination server.
On Destination Server
1) Restore the spfile
[oracle@newyork ~]$ export ORACLE_SID=dell
[oracle@newyork ~]$ rman target /
Recovery Manager: Release 11.2.0.1.0 - Production on Sat Oct 5 19:34:27 2013
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database (not started)
RMAN> set dbid=3764293269
<code>executing command: SET DBID</code>
RMAN> startup force nomount;
<code>startup failed: ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/u01/app/oracle/product/11.2.0/db_1/dbs/initdell.ora'
starting Oracle instance without parameter file for retrieval of spfile
Oracle instance started
Total System Global Area 159019008 bytes
Fixed Size 1335192 bytes
Variable Size 75497576 bytes
Database Buffers 79691776 bytes
Redo Buffers 2494464 bytes</code>
RMAN> restore spfile from '/u01/backup/cfc-3764293269-20131005-00';
<code>Starting restore at 05-OCT-13
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=19 device type=DISK
channel ORA_DISK_1: restoring spfile from AUTOBACKUP /u01/backup/cfc-3764293269-20131005-00
channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete
Finished restore at 05-OCT-13</code>
RMAN> shutdown immediate
<code>Oracle instance shut down</code>
2) Restore controlfile
RMAN> startup nomount
<code>connected to target database (not started)
Oracle instance started
Total System Global Area 272011264 bytes
Fixed Size 1335952 bytes
Variable Size 201330032 bytes
Database Buffers 67108864 bytes
Redo Buffers 2236416 bytes</code>
RMAN> run
2> {
3> set controlfile autobackup format for device type disk to '/u01/backup/cf%F';
4> restore controlfile from autobackup;
5> }
<code>executing command: SET CONTROLFILE AUTOBACKUP FORMAT
Starting restore at 05-OCT-13
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=19 device type=DISK
recovery area destination: /u01/app/oracle/flash_recovery_area
database name (or database unique name) used for search: DELL
channel ORA_DISK_1: no AUTOBACKUPS found in the recovery area
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20131005
channel ORA_DISK_1: AUTOBACKUP found: /u01/backup/cfc-3764293269-20131005-01
channel ORA_DISK_1: restoring control file from AUTOBACKUP /u01/backup/cfc-3764293269-20131005-01
channel ORA_DISK_1: control file restore from AUTOBACKUP complete
output file name=/u01/app/oracle/oradata/dell/control01.ctl
output file name=/u01/app/oracle/flash_recovery_area/dell/control02.ctl
Finished restore at 05-OCT-13</code>
3) Restore the database.
RMAN> alter database mount;
<code>database mounted
released channel: ORA_DISK_1</code>
RMAN> catalog start with ' '; (if backup destination is different from source)
RMAN> restore database;
<code>Starting restore at 05-OCT-13
Starting implicit crosscheck backup at 05-OCT-13
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=19 device type=DISK
Crosschecked 10 objects
Finished implicit crosscheck backup at 05-OCT-13
Starting implicit crosscheck copy at 05-OCT-13
using channel ORA_DISK_1
Finished implicit crosscheck copy at 05-OCT-13
searching for all files in the recovery area
cataloging files...
no files cataloged
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/dell/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00005 to /u01/app/oracle/oradata/dell/example01.dbf
channel ORA_DISK_1: reading from backup piece /u01/backup/07ollp81_1_1
channel ORA_DISK_1: piece handle=/u01/backup/07ollp81_1_1 tag=TAG20131005T191408
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:23
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00002 to /u01/app/oracle/oradata/dell/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/dell/users01.dbf
channel ORA_DISK_1: reading from backup piece /u01/backup/06ollp81_1_1
channel ORA_DISK_1: piece handle=/u01/backup/06ollp81_1_1 tag=TAG20131005T191408
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:37
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /u01/app/oracle/oradata/dell/system01.dbf
channel ORA_DISK_1: reading from backup piece /u01/backup/05ollp81_1_1
channel ORA_DISK_1: piece handle=/u01/backup/05ollp81_1_1 tag=TAG20131005T191408
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:58
Finished restore at 05-OCT-13</code>
4) Recover the database
RMAN> recover database;
<code>Starting recover at 05-OCT-13
using channel ORA_DISK_1
starting media recovery
channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=10
channel ORA_DISK_1: reading from backup piece /u01/backup/08ollpbg_1_1
channel ORA_DISK_1: piece handle=/u01/backup/08ollpbg_1_1 tag=TAG20131005T191600
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
archived log file
name=/u01/app/oracle/flash_recovery_area/DELL/archivelog/2013_10_05/o1_mf_1_10_9507y3bg_.arc thread=1
sequence=10
channel default: deleting archived log(s)
archived log file
name=/u01/app/oracle/flash_recovery_area/DELL/archivelog/2013_10_05/o1_mf_1_10_9507y3bg_.arc RECID=9
STAMP=828042523
unable to find archived log
archived log thread=1 sequence=11
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 10/05/2013 19:48:45
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 11 and starting SCN
of 797450</code>
Note: Ignore the error and relogin to thr rman prompt.
5) open with resetlogs
[oracle@newyork ~]$ rman target /
Recovery Manager: Release 11.2.0.1.0 - Production on Sat Oct 5 19:52:07 2013
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: DELL (DBID=3764293269, not open)
RMAN> sql 'alter database open resetlogs';
<code>using target database control file instead of recovery catalog
sql statement: alter database open resetlogs</code>
RMAN> exit
Recovery Manager complete.
6) verify
[oracle@newyork ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Sat Oct 5 19:54:11 2013
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> archive log list;
<code>Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 1
Next log sequence to archive 1
Current log sequence 1</code>
SQL> select name, dbid from v$database;
<code>NAME DBID
--------- ----------
DELL 3764293269</code>
</pre>Ajay Singh Chandihttp://www.blogger.com/profile/05807334441165607306noreply@blogger.com0tag:blogger.com,1999:blog-9081789365774441839.post-18138154904405902972013-08-18T15:32:00.000+05:302013-08-18T15:32:05.484+05:30Online Redo Log File Management In 11gR2 RAC/ASM.<pre>
SQL> select GROUP#,THREAD# from v$log;
<code> GROUP# THREAD#
---------- ----------
1 1
2 1
3 2
4 2</code>
<b>Here Thread 1 refers to Node 1 and Thread 2 refers to Node 2.</b>
SQL> alter database add logfile thread 1 group 5 ('+DATA') size 50M;
Database altered.
SQL> alter database add logfile thread 1 group 6 ('+DATA') size 50M;
Database altered.
SQL> alter database add logfile thread 2 group 7 ('+DATA') size 50M;
Database altered.
SQL> alter database add logfile thread 2 group 8 ('+DATA') size 50M;
Database altered.
SQL> select THREAD#,GROUP# from v$log order by THREAD#;
<code> THREAD# GROUP#
---------- ----------
1 1
1 6
1 5
1 2
2 7
2 8
2 3
2 4
8 rows selected.</code>
<b>To Add logfile member:</b>
SQL> alter database add logfile member '+DATA' to group 1;
Database altered.
SQL> select group#,member from v$logfile order by group#;
<code> GROUP#
----------
MEMBER
---------------------------------------------------------------------------------------------------
1
+DATA/orcl/onlinelog/group_1.261.823159585
1
+FRA/orcl/onlinelog/group_1.257.823159591
1
+DATA/orcl/onlinelog/group_1.277.823782961
2
+DATA/orcl/onlinelog/group_2.262.823159597
2
+FRA/orcl/onlinelog/group_2.258.823159599
3
+DATA/orcl/onlinelog/group_3.266.823160637
3
+FRA/orcl/onlinelog/group_3.259.823160641
4
+FRA/orcl/onlinelog/group_4.260.823160649
4
+DATA/orcl/onlinelog/group_4.267.823160645
5
+DATA/orcl/onlinelog/group_5.273.823782521
6
+DATA/orcl/onlinelog/group_6.274.823782539
7
+DATA/orcl/onlinelog/group_7.275.823782599
8
+DATA/orcl/onlinelog/group_8.276.823782615
13 rows selected.</code>
</pre>Ajay Singh Chandihttp://www.blogger.com/profile/05807334441165607306noreply@blogger.com0tag:blogger.com,1999:blog-9081789365774441839.post-16877407061574220692013-08-18T15:26:00.000+05:302013-08-18T15:26:34.986+05:30How to create tablespaces in 11gR2 RAC/ASM ?<pre>SQL> create tablespace test datafile
2 '+DATA'
3 size 50m autoextend on next 100m
4 extent management local
5 segment space management auto;
Tablespace created.
SQL> select tablespace_name from dba_data_files;
<code>TABLESPACE_NAME
------------------------------
USERS
UNDOTBS1
SYSAUX
SYSTEM
EXAMPLE
UNDOTBS2
TEST</code>
SQL> alter tablespace test add datafile
2 '+DATA'
3 size 50M autoextend on next 100M;
Tablespace altered.
SQL> select file_name from dba_data_files where tablespace_name='TEST';
<code>FILE_NAME
--------------------------------------------------------------------
+DATA/orcl/datafile/test.271.823780803
+DATA/orcl/datafile/test.272.823781017</code>
</pre>Ajay Singh Chandihttp://www.blogger.com/profile/05807334441165607306noreply@blogger.com0tag:blogger.com,1999:blog-9081789365774441839.post-8664558237358556342013-08-18T15:18:00.000+05:302013-08-18T15:26:47.830+05:30Multiplex Control Files In 11gR2 RAC/ASM.<pre>
<b>1) Add location to the control_files parameter.</b>
[oracle@rac1 ~]$ export ORACLE_SID=orcl1
[oracle@rac1 ~]$ sqlplus / as sysdba
<pre>SQL*Plus: Release 11.2.0.1.0 Production on Sun Aug 18 11:22:46 2013
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options</pre>
SQL> show parameter control_files;
<code>NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_files string +DATA/orcl/controlfile/current
.260.823159575, +FRA/orcl/cont
rolfile/current.256.823159577</code>
SQL> alter system set control_files='+DATA/orcl/controlfile/current.260.823159575',
'+FRA/orcl/controlfile/current.256.823159577', <b>'+DATA', '+FRA'</b> scope=spfile;
<b>2) Stop the database and start it in nomount mode.</b>
[oracle@rac1 ~]$ srvctl stop database -d orcl
[oracle@rac1 ~]$ srvctl start database -d orcl -o nomount
<b>3) Create the control file using RMAN by pointing to an actual existing controlfile.</b>
[oracle@rac1 ~]$ rman target /
<code>Recovery Manager: Release 11.2.0.1.0 - Production on Sun Aug 18 11:38:56 2013
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL (not mounted)</code>
RMAN> restore controlfile from '+DATA/orcl/controlfile/current.260.823159575';
<code>Starting restore at 18-AUG-2013 11:39:31
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1 instance=orcl1 device type=DISK
channel ORA_DISK_1: copied control file copy
output file name=+DATA/orcl/controlfile/current.260.823159575
output file name=+FRA/orcl/controlfile/current.256.823159577
output file name=+DATA/orcl/controlfile/current.270.823779575
Finished restore at 18-AUG-2013 11:39:39</code>
<b>4) Stop the database and start it in normal mode.</b>
[oracle@rac1 ~]$ srvctl stop database -d orcl
[oracle@rac1 ~]$ srvctl start database -d orcl
SQL> select open_mode,name from v$database;
OPEN_MODE NAME
-------------------- ---------
READ WRITE ORCL
<code>+DATA/orcl/controlfile/current.260.823159575
+FRA/orcl/controlfile/current.256.823159577
+DATA/orcl/controlfile/current.269.823780197
+FRA/orcl/controlfile/current.271.823780199</code>
</pre>Ajay Singh Chandihttp://www.blogger.com/profile/05807334441165607306noreply@blogger.com0tag:blogger.com,1999:blog-9081789365774441839.post-27280082911450049072013-05-28T02:15:00.002+05:302013-05-28T02:15:44.443+05:3011gR2 RAC (ASM) to Single Instance (NON-ASM) Cloning.<pre>
<b>Source</b> = 2 node rac cluster with asm SID=dell
<b>Target</b> = single instance non asm SID=dup(to be made)
<b>Notes:</b>
1) For cloning Rman active database duplication will be used,
that means no backups are required(11gR2 feature).
Other Methods which can be used for duplication using RMAN.
1.1) Duplication without connection to target(11gR2 Feature).
1.2) Backup and recreate database manually.
1.3) Rman duplication using backups.
2) Since source database is OMF managed target will be OMF managed as well.
following parameters will be used to achieve that.
2.1) db_create_file_dest---------------->> This directory will contain data,redo & control files.
2.2) db_recovery_file_dest-------------->> This will contain Flash recover Area.
3) In case where source is NON-OMF managed use following parameters : -
3.1) control_files
3.2) db_file_name_convert
3.3) log_file_name_convert
<b>Source</b>
1) Make sure rac database is in archive log mode.
2) Copy password file to target.
[oracle@rac1 dbs]$ scp orapwdell1 192.168.1.69:/u01/app/oracle/product/11.2.0/db_1/dbs/orapwdup
<code>oracle@192.168.1.69's password:
orapwdell1 100% 1536 1.5KB/s 00:00</code>
3) make tnsnames.ora entry using "NETCA" as "Oracle" user
so that a connection could be made to target.
Sample on both nodes:
[oracle@rac1 admin]$ cat tnsnames.ora
<code># tnsnames.ora.rac1 Network Configuration File: /u01/app/oracle/product/11.2.0/
dbhome_1/network/admin/tnsnames.ora.rac1
# Generated by Oracle configuration tools.
DUP =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.69)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = dup)
)
)
DELL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac-cluster-scan)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = dell.example.com)
)
)</code>
Copy the modified tnsnames.ora file to target.
[oracle@rac1 admin]$ scp tnsnames.ora 192.168.1.69:/u01/app/oracle/product/11.2.0/db_1/network/admin/
<code>oracle@192.168.1.69's password:
tnsnames.ora 100% 533 0.5KB/s 00:00</code>
4) Make pfile out of spfile and modify it for single instance
cloning then copy it to target.
<b>Original:</b>
[oracle@rac1 oracle]$ cat initdup.ora
<code>dell1.__db_cache_size=29360128
dell2.__db_cache_size=29360128
dell1.__java_pool_size=4194304
dell2.__java_pool_size=4194304
dell1.__large_pool_size=4194304
dell2.__large_pool_size=4194304
dell1.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
dell1.__pga_aggregate_target=109051904
dell2.__pga_aggregate_target=109051904
dell1.__sga_target=155189248
dell2.__sga_target=155189248
dell1.__shared_io_pool_size=0
dell2.__shared_io_pool_size=0
dell1.__shared_pool_size=113246208
dell2.__shared_pool_size=113246208
dell1.__streams_pool_size=0
dell2.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/dell/adump'
*.audit_trail='DB'
<b>*.cluster_database=true
dell1.cluster_database=TRUE</b>
*.compatible='11.2.0.0.0'
<b>*.control_files='+DATA/dell/controlfile/current.261.800523723','+FRA/dell/controlfile/current.268.800523725'</b>
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_domain='example.com'
*.db_name='dell'
*.db_recovery_file_dest='+FRA'
*.db_recovery_file_dest_size=4039114752
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=dellXDB)'
<b>dell2.instance_number=2
dell1.instance_number=1</b>
*.memory_target=262144000
*.open_cursors=300
*.processes=150
<b>*.remote_listener='rac-cluster-scan:1521'</b>
*.remote_login_passwordfile='exclusive'
<b>dell2.thread=2
dell1.thread=1
dell1.undo_tablespace='UNDOTBS1'
dell2.undo_tablespace='UNDOTBS2'</b></code>
<b>Modified:</b>
<code>dup.__db_cache_size=29360128
dup.__java_pool_size=4194304
dup.__large_pool_size=4194304
dup.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
dup.__pga_aggregate_target=109051904
dup.__sga_target=155189248
dup.__shared_io_pool_size=0
dup.__shared_pool_size=113246208
dup.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/dup/adump'
*.audit_trail='DB'
*.compatible='11.2.0.0.0'
*.db_block_size=8192
<b>*.db_create_file_dest='/u01/app/oracle/oradata'</b>
*.db_domain='example.com'
*.db_name='dup'
<b>*.db_recovery_file_dest='/u01/app/oracle/flash_recovery_area'</b>
*.db_recovery_file_dest_size=4039114752
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=dupXDB)'
*.memory_target=262144000
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='exclusive'
<b>*.undo_tablespace='UNDOTBS1'</b></code>
<b>Target</b>
1) Make ORACLE_SID entry in the bash profile before doing anything else.
----------------------------------snip------------------------------------
ORACLE_UNQNAME=orcl; export ORACLE_UNQNAME
ORACLE_BASE=/u01/app/oracle; export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1; export ORACLE_HOME
<b>ORACLE_SID=dup; export ORACLE_SID</b>
PATH=/usr/sbin:$PATH; export PATH
PATH=$ORACLE_HOME/bin:$PATH; export PATH
------------------------------------snip------------------------------------
2) Make a listener and register the "dup" database and start it using lsnrctl command.
Sample listener.
[oracle@newyork admin]$ cat listener.ora
<code># listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0
/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = dup)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
(SID_NAME = dup)
)
)
LISTENER =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.69)(PORT = 1521))
)
ADR_BASE_LISTENER = /u01/app/oracle</code>
3) Make appropriate directory structures.
[oracle@newyork ~]$ mkdir -p /u01/app/oracle/admin/dup/adump
[oracle@newyork ~]$ mkdir -p /u01/app/oracle/oradata
[oracle@newyork ~]$ mkdir -p /u01/app/oracle/flash_recovery_area
4) Make appropriate entries in "/etc/resolv.conf" so a connection can
be made through SCAN.
<code>search example.com
nameserver 192.168.1.100</code>
5) Do tnsping from both nodes to make sure both systems are reachable.
tnsping dell
tnsping dup
6) create spfile from pfile and start database in nomount stage.
[oracle@newyork ~]$ echo $ORACLE_SID
<code>dup</code>
[oracle@newyork ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Tue May 28 01:24:39 2013
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to an idle instance.
SQL> create spfile from pfile='/u01/initdup.ora';
File created.
SQL> startup nomount
<code>ORACLE instance started.
Total System Global Area 263639040 bytes
Fixed Size 1335892 bytes
Variable Size 230690220 bytes
Database Buffers 29360128 bytes
Redo Buffers 2252800 bytes</code>
7) Through RMAN do active database cloning.
[oracle@newyork ~]$ echo $ORACLE_SID
<code>dup</code>
<b>[oracle@newyork ~]$ rman target sys/sys@dell nocatalog auxiliary sys/sys@dup</b>
Recovery Manager: Release 11.2.0.1.0 - Production on Tue May 28 01:28:28 2013
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: DELL (DBID=3771264459)
using target database control file instead of recovery catalog
connected to auxiliary database: DUP (not mounted)
RMAN> duplicate database to dup
2> from active database
3> nofilenamecheck;
<code>Starting Duplicate Db at 28-MAY-13
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=19 device type=DISK
contents of Memory Script:
{
sql clone "alter system set control_files =
''/u01/app/oracle/oradata/DUP/controlfile/o1_mf_8t7gsyl1_.ctl'', ''/u01/app/oracle/
flash_recovery_area/DUP/controlfile/o1_mf_8t7gsyn9_.ctl'' comment=
''Set by RMAN'' scope=spfile";
sql clone "alter system set db_name =
''DELL'' comment=
''Modified by RMAN duplicate'' scope=spfile";
sql clone "alter system set db_unique_name =
''DUP'' comment=
''Modified by RMAN duplicate'' scope=spfile";
shutdown clone immediate;
startup clone force nomount
backup as copy current controlfile auxiliary format '/u01/app/oracle/oradata/DUP/controlfile/
o1_mf_8t7gsyns_.ctl';
restore clone controlfile to '/u01/app/oracle/flash_recovery_area/DUP/controlfile/o1_mf_8t7gsyo3_.ctl' from
'/u01/app/oracle/oradata/DUP/controlfile/o1_mf_8t7gsyns_.ctl';
sql clone "alter system set control_files =
''/u01/app/oracle/oradata/DUP/controlfile/o1_mf_8t7gsyns_.ctl'',
''/u01/app/oracle/flash_recovery_area/DUP/controlfile/o1_mf_8t7gsyo3_.ctl'' comment=
''Set by RMAN'' scope=spfile";
shutdown clone immediate;
startup clone nomount;
alter clone database mount;
}
executing Memory Script
sql statement: alter system set control_files =
''/u01/app/oracle/oradata/DUP/controlfile/o1_mf_8t7gsyl1_.ctl'',
''/u01/app/oracle/flash_recovery_area/DUP/controlfile/o1_mf_8t7gsyn9_.ctl''
comment= ''Set by RMAN'' scope=spfile
sql statement: alter system set db_name = ''DELL'' comment= ''Modified by RMAN duplicate'' scope=spfile
sql statement: alter system set db_unique_name = ''DUP'' comment= ''Modified by RMAN duplicate'' scope=spfile
Oracle instance shut down
Oracle instance started
Total System Global Area 263639040 bytes
Fixed Size 1335892 bytes
Variable Size 230690220 bytes
Database Buffers 29360128 bytes
Redo Buffers 2252800 bytes
Starting backup at 28-MAY-13
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=51 instance=dell2 device type=DISK
channel ORA_DISK_1: starting datafile copy
copying current control file
output file name=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/snapcf_dell2.f tag=TAG20130528T013011 RECID=2 STAMP=816571814
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
Finished backup at 28-MAY-13
Starting restore at 28-MAY-13
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=18 device type=DISK
channel ORA_AUX_DISK_1: copied control file copy
Finished restore at 28-MAY-13
sql statement: alter system set control_files =
''/u01/app/oracle/oradata/DUP/controlfile/o1_mf_8t7gsyns_.ctl'',
''/u01/app/oracle/flash_recovery_area/DUP/controlfile/o1_mf_8t7gsyo3_.ctl''
comment= ''Set by RMAN'' scope=spfile
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 263639040 bytes
Fixed Size 1335892 bytes
Variable Size 230690220 bytes
Database Buffers 29360128 bytes
Redo Buffers 2252800 bytes
database mounted
contents of Memory Script:
{
set newname for clone datafile 1 to new;
set newname for clone datafile 2 to new;
set newname for clone datafile 3 to new;
set newname for clone datafile 4 to new;
set newname for clone datafile 5 to new;
set newname for clone datafile 6 to new;
backup as copy reuse
datafile 1 auxiliary format new
datafile 2 auxiliary format new
datafile 3 auxiliary format new
datafile 4 auxiliary format new
datafile 5 auxiliary format new
datafile 6 auxiliary format new
;
sql 'alter system archive log current';
}
executing Memory Script
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting backup at 28-MAY-13
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=+DATA/dell/datafile/system.267.800523427
output file name=/u01/app/oracle/oradata/DUP/datafile/o1_mf_system_0coanpe6_.dbf tag=TAG20130528T013045
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:39
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=+DATA/dell/datafile/sysaux.263.800523439
output file name=/u01/app/oracle/oradata/DUP/datafile/o1_mf_sysaux_0doanphc_.dbf tag=TAG20130528T013045
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:07
channel ORA_DISK_1: starting datafile copy
input datafile file number=00005 name=+DATA/dell/datafile/example.266.800523445
output file name=/u01/app/oracle/oradata/DUP/datafile/o1_mf_example_0eoanpjf_.dbf tag=TAG20130528T013045
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:21
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=+DATA/dell/datafile/undotbs1.265.800523447
output file name=/u01/app/oracle/oradata/DUP/datafile/o1_mf_undotbs1_0foanpk4_.dbf tag=TAG20130528T013045
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:08
channel ORA_DISK_1: starting datafile copy
input datafile file number=00006 name=+DATA/dell/datafile/undotbs2.258.800523875
output file name=/u01/app/oracle/oradata/DUP/datafile/o1_mf_undotbs2_0goanpkc_.dbf tag=TAG20130528T013045
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:10
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=+DATA/dell/datafile/users.262.800523447
output file name=/u01/app/oracle/oradata/DUP/datafile/o1_mf_users_0hoanpkm_.dbf tag=TAG20130528T013045
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:04
Finished backup at 28-MAY-13
sql statement: alter system archive log current
contents of Memory Script:
{
backup as copy reuse
archivelog like "+FRA/dell/archivelog/2013_05_28/thread_2_seq_3.270.816570261" auxiliary format
"/u01/app/oracle/flash_recovery_area/DUP/archivelog/2013_05_28/o1_mf_2_3_%u_.arc" archivelog like
"+FRA/dell/archivelog/2013_05_28/thread_1_seq_5.269.816572061" auxiliary format
"/u01/app/oracle/flash_recovery_area/DUP/archivelog/2013_05_28/o1_mf_1_5_%u_.arc" archivelog like
"+FRA/dell/archivelog/2013_05_28/thread_2_seq_4.267.816572061" auxiliary format
"/u01/app/oracle/flash_recovery_area/DUP/archivelog/2013_05_28/o1_mf_2_4_%u_.arc" ;
catalog clone recovery area;
switch clone datafile all;
}
executing Memory Script
Starting backup at 28-MAY-13
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log copy
input archived log thread=2 sequence=3 RECID=6 STAMP=816570261
output file name=/u01/app/oracle/flash_recovery_area/DUP/archivelog/2013_05_28/o1_mf_2_3_0ioanpl7_.arc RECID=0 STAMP=0
channel ORA_DISK_1: archived log copy complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting archived log copy
input archived log thread=1 sequence=5 RECID=7 STAMP=816572061
output file name=/u01/app/oracle/flash_recovery_area/DUP/archivelog/2013_05_28/o1_mf_1_5_0joanpla_.arc RECID=0 STAMP=0
channel ORA_DISK_1: archived log copy complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting archived log copy
input archived log thread=2 sequence=4 RECID=8 STAMP=816572062
output file name=/u01/app/oracle/flash_recovery_area/DUP/archivelog/2013_05_28/o1_mf_2_4_0koanplb_.arc RECID=0 STAMP=0
channel ORA_DISK_1: archived log copy complete, elapsed time: 00:00:02
Finished backup at 28-MAY-13
searching for all files in the recovery area
List of Files Unknown to the Database
=====================================
File Name: /u01/app/oracle/flash_recovery_area/DUP/controlfile/o1_mf_8t7gsyn9_.ctl
File Name: /u01/app/oracle/flash_recovery_area/DUP/archivelog/2013_05_28/o1_mf_2_3_0ioanpl7_.arc
File Name: /u01/app/oracle/flash_recovery_area/DUP/archivelog/2013_05_28/o1_mf_2_4_0koanplb_.arc
File Name: /u01/app/oracle/flash_recovery_area/DUP/archivelog/2013_05_28/o1_mf_1_5_0joanpla_.arc
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: /u01/app/oracle/flash_recovery_area/DUP/archivelog/2013_05_28/o1_mf_2_3_0ioanpl7_.arc
File Name: /u01/app/oracle/flash_recovery_area/DUP/archivelog/2013_05_28/o1_mf_2_4_0koanplb_.arc
File Name: /u01/app/oracle/flash_recovery_area/DUP/archivelog/2013_05_28/o1_mf_1_5_0joanpla_.arc
List of Files Which Where Not Cataloged
=======================================
File Name: /u01/app/oracle/flash_recovery_area/DUP/controlfile/o1_mf_8t7gsyn9_.ctl
RMAN-07517: Reason: The file header is corrupted
datafile 1 switched to datafile copy
input datafile copy RECID=2 STAMP=816572080 file name=/u01/app/oracle/oradata/DUP/datafile/o1_mf_system_0coanpe6_.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=3 STAMP=816572080 file name=/u01/app/oracle/oradata/DUP/datafile/o1_mf_sysaux_0doanphc_.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=4 STAMP=816572080 file name=/u01/app/oracle/oradata/DUP/datafile/o1_mf_undotbs1_0foanpk4_.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=5 STAMP=816572080 file name=/u01/app/oracle/oradata/DUP/datafile/o1_mf_users_0hoanpkm_.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=6 STAMP=816572080 file name=/u01/app/oracle/oradata/DUP/datafile/o1_mf_example_0eoanpjf_.dbf
datafile 6 switched to datafile copy
input datafile copy RECID=7 STAMP=816572080 file name=/u01/app/oracle/oradata/DUP/datafile/o1_mf_undotbs2_0goanpkc_.dbf
contents of Memory Script:
{
set until scn 863230;
recover
clone database
delete archivelog
;
}
executing Memory Script
executing command: SET until clause
Starting recover at 28-MAY-13
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=18 device type=DISK
starting media recovery
archived log for thread 1 with sequence 5 is already on disk as file /u01/app/oracle/flash_recovery_area/DUP/
archivelog/2013_05_28/o1_mf_1_5_0joanpla_.arc
archived log for thread 2 with sequence 4 is already on disk as file /u01/app/oracle/flash_recovery_area/DUP/
archivelog/2013_05_28/o1_mf_2_4_0koanplb_.arc
archived log file name=/u01/app/oracle/flash_recovery_area/DUP/archivelog/2013_05_28/o1_mf_1_5_0joanpla_.arc
thread=1 sequence=5
archived log file name=/u01/app/oracle/flash_recovery_area/DUP/archivelog/2013_05_28/o1_mf_2_4_0koanplb_.arc
thread=2 sequence=4
media recovery complete, elapsed time: 00:00:02
Finished recover at 28-MAY-13
contents of Memory Script:
{
shutdown clone immediate;
startup clone nomount;
sql clone "alter system set db_name =
''DUP'' comment=
''Reset to original value by RMAN'' scope=spfile";
sql clone "alter system reset db_unique_name scope=spfile";
shutdown clone immediate;
startup clone nomount;
}
executing Memory Script
database dismounted
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 263639040 bytes
Fixed Size 1335892 bytes
Variable Size 230690220 bytes
Database Buffers 29360128 bytes
Redo Buffers 2252800 bytes
sql statement: alter system set db_name = ''DUP'' comment= ''Reset to original value by RMAN'' scope=spfile
sql statement: alter system reset db_unique_name scope=spfile
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 263639040 bytes
Fixed Size 1335892 bytes
Variable Size 230690220 bytes
Database Buffers 29360128 bytes
Redo Buffers 2252800 bytes
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "DUP" RESETLOGS ARCHIVELOG
MAXLOGFILES 192
MAXLOGMEMBERS 3
MAXDATAFILES 1024
MAXINSTANCES 32
MAXLOGHISTORY 292
LOGFILE
GROUP 1 SIZE 50 M ,
GROUP 2 SIZE 50 M
DATAFILE
'/u01/app/oracle/oradata/DUP/datafile/o1_mf_system_0coanpe6_.dbf'
CHARACTER SET WE8MSWIN1252
sql statement: ALTER DATABASE ADD LOGFILE
INSTANCE 'i2'
GROUP 3 SIZE 50 M ,
GROUP 4 SIZE 50 M
contents of Memory Script:
{
set newname for clone tempfile 1 to new;
switch clone tempfile all;
catalog clone datafilecopy "/u01/app/oracle/oradata/DUP/datafile/o1_mf_sysaux_0doanphc_.dbf",
"/u01/app/oracle/oradata/DUP/datafile/o1_mf_undotbs1_0foanpk4_.dbf",
"/u01/app/oracle/oradata/DUP/datafile/o1_mf_users_0hoanpkm_.dbf",
"/u01/app/oracle/oradata/DUP/datafile/o1_mf_example_0eoanpjf_.dbf",
"/u01/app/oracle/oradata/DUP/datafile/o1_mf_undotbs2_0goanpkc_.dbf";
switch clone datafile all;
}
executing Memory Script
executing command: SET NEWNAME
renamed tempfile 1 to /u01/app/oracle/oradata/DUP/datafile/o1_mf_temp_%u_.tmp in control file
cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata/DUP/datafile/o1_mf_sysaux_0doanphc_.dbf RECID=1 STAMP=816572129
cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata/DUP/datafile/o1_mf_undotbs1_0foanpk4_.dbf RECID=2 STAMP=816572129
cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata/DUP/datafile/o1_mf_users_0hoanpkm_.dbf RECID=3 STAMP=816572129
cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata/DUP/datafile/o1_mf_example_0eoanpjf_.dbf RECID=4 STAMP=816572129
cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata/DUP/datafile/o1_mf_undotbs2_0goanpkc_.dbf RECID=5 STAMP=816572129
datafile 2 switched to datafile copy
input datafile copy RECID=1 STAMP=816572129 file name=/u01/app/oracle/oradata/DUP/datafile/o1_mf_sysaux_0doanphc_.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=816572129 file name=/u01/app/oracle/oradata/DUP/datafile/o1_mf_undotbs1_0foanpk4_.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=3 STAMP=816572129 file name=/u01/app/oracle/oradata/DUP/datafile/o1_mf_users_0hoanpkm_.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=4 STAMP=816572129 file name=/u01/app/oracle/oradata/DUP/datafile/o1_mf_example_0eoanpjf_.dbf
datafile 6 switched to datafile copy
input datafile copy RECID=5 STAMP=816572129 file name=/u01/app/oracle/oradata/DUP/datafile/o1_mf_undotbs2_0goanpkc_.dbf
contents of Memory Script:
{
Alter clone database open resetlogs;
}
executing Memory Script
database opened
Finished Duplicate Db at 28-MAY-13</code>
8) after cloning disable thread 2 and remove respective redo groups.
SQL> select thread#,status,enabled from v$thread;
<code> THREAD# STATUS ENABLED
---------- ------ --------
1 OPEN PUBLIC
2 CLOSED PUBLIC</code>
SQL> select group# from v$log where thread#=2;
<code> GROUP#
----------
3
4</code>
SQL> alter database disable thread 2;
Database altered.
SQL> alter database drop logfile group 3;
Database altered.
SQL> alter database drop logfile group 4;
Database altered.
SQL> select thread#, status, enabled from v$thread;
<code> THREAD# STATUS ENABLED
---------- ------ --------
1 OPEN PUBLIC</code>
9) drop additional undo tablespaces
SQL> show parameter undo
<code>NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS1</code>
SQL> select tablespace_name from dba_tablespaces;
<code>TABLESPACE_NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
TEMP
USERS
UNDOTBS2
EXAMPLE
7 rows selected.</code>
SQL> drop tablespace UNDOTBS2 including contents and datafiles;
Tablespace dropped.
10) check status of tablespaces.
SQL> select tablespace_name,file_name,status from dba_temp_files;
<code>TABLESPACE_NAME
------------------------------
FILE_NAME
--------------------------------------------------------------------------------
STATUS
-------
TEMP
/u01/app/oracle/oradata/DUP/datafile/o1_mf_temp_8t7h4w47_.tmp
ONLINE</code>
SQL> select tablespace_name,file_name,status from dba_data_files;
<code>TABLESPACE_NAME
------------------------------
FILE_NAME
---------------------------------------------------------------------------------------------------
STATUS
---------
SYSTEM
/u01/app/oracle/oradata/DUP/datafile/o1_mf_system_0coanpe6_.dbf
AVAILABLE
SYSAUX
/u01/app/oracle/oradata/DUP/datafile/o1_mf_sysaux_0doanphc_.dbf
AVAILABLE
TABLESPACE_NAME
------------------------------
FILE_NAME
---------------------------------------------------------------------------------------------------
STATUS
---------
UNDOTBS1
/u01/app/oracle/oradata/DUP/datafile/o1_mf_undotbs1_0foanpk4_.dbf
AVAILABLE
USERS
/u01/app/oracle/oradata/DUP/datafile/o1_mf_users_0hoanpkm_.dbf
TABLESPACE_NAME
------------------------------
FILE_NAME
---------------------------------------------------------------------------------------------------
STATUS
---------
AVAILABLE
EXAMPLE
/u01/app/oracle/oradata/DUP/datafile/o1_mf_example_0eoanpjf_.dbf
AVAILABLE</code>
Cloning Finished.
</pre>Ajay Singh Chandihttp://www.blogger.com/profile/05807334441165607306noreply@blogger.com1tag:blogger.com,1999:blog-9081789365774441839.post-62444707797467705232013-03-27T00:41:00.003+05:302013-03-27T00:41:19.875+05:30General Performance Tuning Guidelines for Oracle RAC.<div dir="ltr" style="text-align: left;" trbidi="on">
<pre><b>1) Rule of thumb</b> - It is safe to assume that an application which
scales well in a single instance environment would scale well in
a RAC environment. Opposite of that is also true, If an application
has problems with scalability in a single instance environment,
Then it may not scale well in a RAC environment.
<b>2) Speed up instance recovery. </b>
<b>i)</b> To speed up the instance recovery time use FAST_START_MTTR_TARGET.
In a single instance environment instance start-up + crash recovery time
is controlled by this parameter.<a name='more'></a>
Setting a value for this parameter would result in a more aggressive
incremental checkpointing, which in turn would incur higher I/O.
The estimates for the instance recovery in RAC can found in
the V$INSTANCE_RECOVERY view through the ESTD_CLUSTER_AVAILABLE_TIME column.
<b>ii)</b> Use RECOVERY_PARALLISM to parallelize instance recovery.
<b>iii)</b> Set PARALLEL_MIN_SERVERS CPU_COUNT-1. This parameter specifies
the minimum number of parallel execution processes running for the instance.
<b>iv)</b> Speed up transaction recovery after system failure by setting FAST_START_PARALLEL_ROLLBACK.
<b>v)</b> Use asynchronous I/O instead of synchronous I/O.
<b>vi)</b> Instance recovery uses 50 % of the buffer cache for recovery buffers. Make
sure buffer cache is sufficiently sized.
<b>3) Object level tuning.</b>
<b>i)</b> Avoid full table scans - Full table scans results in higher Global Cache Requests.
That is because if the requested blocks are not found in the local cache, then
an attempt is made to find them in another cache.
<b>ii)</b> Use Automatic Segment Space Management - ASSM helps applications with
high insert intensive nature to avoid issues previously encountered and related
to free lists. It also provides instance level affinity to table blocks.
<b>iii)</b> Use sequences and increase their cache size. Increase in sequence cache size
improves instance affinity to index keys deriving their values from sequences.
If application logic permits use "cache noorder".
<b>iv)</b> Use Range or list partitioning, which results in the workload being directed
to modify a particular range of values from a particular instance.
<b>v)</b> Use hash partitioning to reduce buffer busy contention.
<b>vi)</b> Reduce excessive parsing in library cache and row cache operations to
avoid additional interconnect traffic.
<b>vii)</b> Use of tables instead of sequences to generate unique numbers is not
recommended because it may cause severe contention.
<b>viii)</b> Make sure that index selectivity is good. Unselective indexes degrade
query performance and also cause inter instance contention.
<b>4) Network Tuning</b> - Make sure that a private network is used for the interconnect. The private network
link should be in full duplex mode. Also, configure the MTU (Maximum transmission Unit)
to 9000 or 9k.
<b>5) Reduce Index Block Contention</b> -
<b>i)</b> Use Global index hash partitioning
<b>ii)</b> If sequences are used, Then increase sequence cache size.
<b>iii)</b> Use Natural keys as opposed to surrogate keys -
Surrogate keys are keys that have no "business" meaning and are used only
for identifying table records.
Example: Keys generated using sequences or via a table in the schema.
Natural keys - Keys which are used by the application or people are natural keys.
Example - Invoice-Numbers, Tax-Ids etc.
<b>iv)</b> Use reverse key indexes.
<b>6) Other Considerations</b> -
<b>i)</b> You may see High Water Mark contention in applications where data insertion
is the prevalent feature.
That is because if data insertion is at a higher rate, new blocks may have to
be maid available after unfruitful searches for free space. All of this happens
while holding high water mark enqueue.
So, common wait events for this scenario are as following: -
a) High percentage of enq: HW - contention
b) High percentage of gc current grant events.
Common solution to these problems is to define uniform and large
extent sizes.
<b>ii)</b> Truncating different tables from different instances in RAC does not scale
well as it requires excessive cross instance calls.
</pre>
</div>
Ajay Singh Chandihttp://www.blogger.com/profile/05807334441165607306noreply@blogger.com1tag:blogger.com,1999:blog-9081789365774441839.post-57090908476259597012013-02-06T14:30:00.000+05:302013-02-06T14:30:24.507+05:30Collect Diagnostic Data in 11gR2 RAC Using DiagCollection.pl Script.<pre>
All the log files related to clusterware processes can be found in the "$GRID_HOME/logs"
directory. So, if there is some problem with the clusterware you could check
all the log files in the directory.
But mining data from all the log files is difficult. So, in order
to ease of this difficulty in 11gR2 you can use the "diagcollection.pl"
script to collect diagnostic data.
Run the diagcollection.pl script as the root user to collect diagnostic information
from an Oracle Clusterware installation. The diagnostics provide additional information
so that Oracle Support Services can resolve problems. Run this script from the
operating system prompt as follows, where CRS_home is the home directory of your Oracle
Clusterware installation:
# Invoke this utility as the root user.
# CRS_home/bin/diagcollection.pl --collect
[root@rac1 ~]# /u01/app/11.2.0/grid/bin/diagcollection.pl --collect
<code>Production Copyright 2004, 2008, Oracle. All rights reserved
Cluster Ready Services (CRS) diagnostic collection tool
The following CRS diagnostic archives will be created in the local directory.
crsData_rac1_20130206_0017.tar.gz -> logs,traces and cores from CRS home.
Note: core files will be packaged only with the --core option.
ocrData_rac1_20130206_0017.tar.gz -> ocrdump, ocrcheck etc
coreData_rac1_20130206_0017.tar.gz -> contents of CRS core files in text format
osData_rac1_20130206_0017.tar.gz -> logs from Operating System
Collecting crs data
/bin/tar: log/rac1/cssd/ocssd.log: file changed as we read it
/bin/tar: log/rac1/ctssd/octssd.log: file changed as we read it
Collecting OCR data
Collecting information from core files
No corefiles found
Collecting OS logs</code>
[root@rac1 ~]# ll
<code>total 14720
-rw------- 1 root root 2946 Aug 23 21:21 anaconda-ks.cfg
-rw-r--r-- 1 root root 14785218 Feb 6 00:17 <b>crsData_rac1_20130206_0017.tar.gz</b>
drwxr-xr-x 4 root root 4096 Feb 3 16:21 Desktop
-rw-r--r-- 1 root root 42129 Aug 23 21:20 install.log
-rw-r--r-- 1 root root 5259 Aug 23 21:20 install.log.syslog
-rw-r--r-- 1 root root 10960 Feb 6 00:18 <b>ocrData_rac1_20130206_0017.tar.gz</b>
-rw-r--r-- 1 root root 171884 Feb 6 00:18 <b>osData_rac1_20130206_0017.tar.gz</b>
-rwxr-x--- 1 root root 696 Jan 26 14:07 root.sh.racnode1.AFTER_INSTALL</code>
To clean the collected data use the "--clean" option.
[root@rac1 ~]# /u01/app/11.2.0/grid/bin/diagcollection.pl --clean
<code>Production Copyright 2004, 2008, Oracle. All rights reserved
Cluster Ready Services (CRS) diagnostic collection tool
Cleaning up tar and gzip files
Done</code>
[root@rac1 ~]# ll
total 88
-rw------- 1 root root 2946 Aug 23 21:21 anaconda-ks.cfg
drwxr-xr-x 5 root root 4096 Feb 6 00:19 cfgtoollogs
drwxr-xr-x 4 root root 4096 Feb 3 16:21 Desktop
drwxr-xr-x 2 root root 4096 Feb 6 00:19 install
-rw-r--r-- 1 root root 42129 Aug 23 21:20 install.log
-rw-r--r-- 1 root root 5259 Aug 23 21:20 install.log.syslog
drwxr-xr-x 3 root root 4096 Feb 6 00:19 log
-rwxr-x--- 1 root root 696 Jan 26 14:07 root.sh.racnode1.AFTER_INSTALL
</pre>Ajay Singh Chandihttp://www.blogger.com/profile/05807334441165607306noreply@blogger.com0tag:blogger.com,1999:blog-9081789365774441839.post-80697549842338326112013-02-06T00:01:00.001+05:302013-02-06T00:02:11.745+05:30Oracle RAC 11gR2 Policy Managed Database Creation using DBCA.<div dir="ltr" style="text-align: left;" trbidi="on">
<pre>Policy managed databases depend upon server pools in order to work.
Starting from 11gR2, Oracle RAC has three kinds of server pools.
1) <b>Free Server Pool</b> - This pool contains servers which are not assigned to
any server pools.
2) <b>Generic Server Pool</b> - This pool contains Pre 11gR2 databases as well as administrator-managed
databases.
3) <b>User Created pool</b> - This server pool is created by the user and is used for running
policy managed databases.
I have a test three node cluster, Which will be used for creating
policy managed database.
# Make sure that clusterware services are running on all the nodes
# before starting starting with the installation.
[root@rac1 ~]# su - grid
[grid@rac1 ~]$ crsctl check cluster -all
<code>**************************************************************
rac1:
CRS-4537: Cluster Ready Services is online
CRS-4529: Cluster Synchronization Services is online
CRS-4533: Event Manager is online
**************************************************************
rac2:
CRS-4537: Cluster Ready Services is online
CRS-4529: Cluster Synchronization Services is online
CRS-4533: Event Manager is online
**************************************************************
rac3:
CRS-4537: Cluster Ready Services is online
CRS-4529: Cluster Synchronization Services is online
CRS-4533: Event Manager is online
**************************************************************</code>
Also, make sure that you have appropriate servers in the free pool.
[grid@rac1 ~]$ crsctl status serverpool
<code>NAME=Free
ACTIVE_SERVERS=rac1 rac2 rac3
NAME=Generic
ACTIVE_SERVERS=</code>
In my test cluster i did not have servers in the free pool as they were being
used in the generic pool because of the administrator managed database that i had
created earlier.
So, i had to drop that database in order get servers in the free pool.
Start with the database creation.
[root@rac1 ~]# xhost +
access control disabled, clients can connect from any host
[root@rac1 ~]# su - oracle
[oracle@rac1 ~]$ dbca
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjhFBWL-6WVu_VJCR8ETVmesZ6wEiWNDuOdQScKLvBaGYMKplJIDL5dIjdpe5kyRzdnkfWKsz5yrhXnQCqbZwABZUHaAEye3nuZruKLdBqAT-XdOJDRkqnH9VYabfvOPi3GxcAkxcgUl205/s1600/policymanaged1.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="225" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjhFBWL-6WVu_VJCR8ETVmesZ6wEiWNDuOdQScKLvBaGYMKplJIDL5dIjdpe5kyRzdnkfWKsz5yrhXnQCqbZwABZUHaAEye3nuZruKLdBqAT-XdOJDRkqnH9VYabfvOPi3GxcAkxcgUl205/s320/policymanaged1.png" width="320" /></a></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiWqOjNinj1m4fRN0xgw9rL7kypcZU1nE3ZvkrhPuz1HX9W2d3QUy809lcWE5jX9YFdKS9zqZHbDQFDlFwjY6fDLwPSe0DOk1z7NrAewKaYEzn_UUU31b9b86f5tXY-HcsYRuxvgThmaZUX/s1600/policymanaged2.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="226" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiWqOjNinj1m4fRN0xgw9rL7kypcZU1nE3ZvkrhPuz1HX9W2d3QUy809lcWE5jX9YFdKS9zqZHbDQFDlFwjY6fDLwPSe0DOk1z7NrAewKaYEzn_UUU31b9b86f5tXY-HcsYRuxvgThmaZUX/s320/policymanaged2.png" width="320" /></a></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgr9kPRWjeNohxmQsobVMDZED9XpjIYL0aNymvAFLZ20PidCoKuKHY4_hmYqyaZwVyB15Yw_C69pV903o17jrfHD1jmJyiphOzUB7VirR-VFoKkjyXKEChd7SGfJQd3gNOPOiRS0FKJpULW/s1600/policymanaged3.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="227" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgr9kPRWjeNohxmQsobVMDZED9XpjIYL0aNymvAFLZ20PidCoKuKHY4_hmYqyaZwVyB15Yw_C69pV903o17jrfHD1jmJyiphOzUB7VirR-VFoKkjyXKEChd7SGfJQd3gNOPOiRS0FKJpULW/s320/policymanaged3.png" width="320" /></a></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhKW4QPUrDA5WUownFD3acNh2O7VhgPJvGI5LNGDWEr3NpaPzxcx5vJp8wySIuVoABPJsme0aDc_Saohvd3EBy6OaTAeB6EEHzntUf9bZuAigPgTz_WpIpaSTg48xSzXusiYXnreAQ2oKzX/s1600/policymanaged4.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="226" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhKW4QPUrDA5WUownFD3acNh2O7VhgPJvGI5LNGDWEr3NpaPzxcx5vJp8wySIuVoABPJsme0aDc_Saohvd3EBy6OaTAeB6EEHzntUf9bZuAigPgTz_WpIpaSTg48xSzXusiYXnreAQ2oKzX/s320/policymanaged4.png" width="320" /></a></div>
In the server pool creation step i have set the cardinality to "2"</pre>
<pre>this means that out of three nodes my database will run only on 2 nodes</pre>
<pre>at a time.
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgMGnud-uQgEn1dd8pPoPHMRAJSqPLPfnz5bVeFVzUL9HxiA6PBOATA_9lIpgE0ot4V5cBy3N6WM3zXIUSV7wFcmuSAJX864DLErkaGAOgJF1R6a_IHlzuVUR10ILjSbW3XFDYtp1Q9sczZ/s1600/policymanaged5.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="226" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgMGnud-uQgEn1dd8pPoPHMRAJSqPLPfnz5bVeFVzUL9HxiA6PBOATA_9lIpgE0ot4V5cBy3N6WM3zXIUSV7wFcmuSAJX864DLErkaGAOgJF1R6a_IHlzuVUR10ILjSbW3XFDYtp1Q9sczZ/s320/policymanaged5.png" width="320" /></a></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgAQgkTvKd76KKe_A8jwD-QIrQaJ2re4VMBsUHMcjOCRI1dL-N7t_DNqtusFhvh1UfReYkAMXjGwMedFmNjHJDyHBHPw2NrJypkMCcxb5k_FCyyyLw6ag91Vjfo9QqW6z-OQ9-ZxY12ePSk/s1600/policymanaged6.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="225" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgAQgkTvKd76KKe_A8jwD-QIrQaJ2re4VMBsUHMcjOCRI1dL-N7t_DNqtusFhvh1UfReYkAMXjGwMedFmNjHJDyHBHPw2NrJypkMCcxb5k_FCyyyLw6ag91Vjfo9QqW6z-OQ9-ZxY12ePSk/s320/policymanaged6.png" width="320" /></a></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhqe0CDg-6MQN3QDIWuEjV_QpR-VxTCw6OLsjw9sNqee6q7x3UIsKNjdG6Muz1-7io8AnucebLBzNZJTlv4RIx_luUhYh2r2Rb42sfTI2_7DwymKhrMq5tvcgDs2dupfDU0hCrYV-roXxEv/s1600/policymanaged7.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="227" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhqe0CDg-6MQN3QDIWuEjV_QpR-VxTCw6OLsjw9sNqee6q7x3UIsKNjdG6Muz1-7io8AnucebLBzNZJTlv4RIx_luUhYh2r2Rb42sfTI2_7DwymKhrMq5tvcgDs2dupfDU0hCrYV-roXxEv/s320/policymanaged7.png" width="320" /></a></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjBxrO02lEE5ljqcYiKfz7JvkG8snR9yUsVP3lWRB_48i2lfiQWo53dTpg_YudOkFqoJ4gpRa4_EK18HFuojdWSo67_WZcMoFxenFboX3nGzi1a4rvjcmph1zvxVsamGullFIwAuJJg0p4n/s1600/policymanaged8.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="226" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjBxrO02lEE5ljqcYiKfz7JvkG8snR9yUsVP3lWRB_48i2lfiQWo53dTpg_YudOkFqoJ4gpRa4_EK18HFuojdWSo67_WZcMoFxenFboX3nGzi1a4rvjcmph1zvxVsamGullFIwAuJJg0p4n/s320/policymanaged8.png" width="320" /></a></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgn1Pedc3BlI4dcvXafNzqjEe384J5qQ0r_OEK18-EZUYtpbc5br8iLQ4n1r53TsT7rwz14R9rjPY4tYZ3y7alAYnQ18NmuzeULPjZZIe4Aw-ZdJE1t9YONi_ho_c_ms9C13Li5CT5RZ7qR/s1600/policymanaged9.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="226" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgn1Pedc3BlI4dcvXafNzqjEe384J5qQ0r_OEK18-EZUYtpbc5br8iLQ4n1r53TsT7rwz14R9rjPY4tYZ3y7alAYnQ18NmuzeULPjZZIe4Aw-ZdJE1t9YONi_ho_c_ms9C13Li5CT5RZ7qR/s320/policymanaged9.png" width="320" /></a></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh0qSqf-pRtWKSrCl86jeEJZTN_SH2NlTMqXQiE5EExZk7DGcHJt5JXdYW92gdgZxmwbKR5HR95mQQxwF__Ay_KksV4ehBpeNAg0r37DFbxzW9LRRGPJ0TUdwrN7pMrg4VxN1EaiWhLdcHm/s1600/policymanaged10.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="228" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh0qSqf-pRtWKSrCl86jeEJZTN_SH2NlTMqXQiE5EExZk7DGcHJt5JXdYW92gdgZxmwbKR5HR95mQQxwF__Ay_KksV4ehBpeNAg0r37DFbxzW9LRRGPJ0TUdwrN7pMrg4VxN1EaiWhLdcHm/s320/policymanaged10.png" width="320" /></a></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi0gkjFWbS5-ikOeokNq08pB5m9D-SjXG7ZfWQfb05FNN5DZvYHBsw4SsOhF4cAg6yWZEFMHY5QMXFOb1ENIx94ZhSXgZKhJDrp5Z1VFwBjTqwyhDVq3oIlXiX4tneYrTJAuBaxZqeY9g0u/s1600/policymanaged11.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="228" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi0gkjFWbS5-ikOeokNq08pB5m9D-SjXG7ZfWQfb05FNN5DZvYHBsw4SsOhF4cAg6yWZEFMHY5QMXFOb1ENIx94ZhSXgZKhJDrp5Z1VFwBjTqwyhDVq3oIlXiX4tneYrTJAuBaxZqeY9g0u/s320/policymanaged11.png" width="320" /></a></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhzG8TAJ_d3Ea209epSm0KJUEWj9iSnhmV1h4WAu5ETjKgKH1RTaSlemYmgW75a_u70eG-dlYuPDQpHFlSeLdBIi8T3zPObsLmrIr1ylC8ifd6A58IZzhWPuIDijRe6l540FeOLspzv52HG/s1600/policymanaged12.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="226" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhzG8TAJ_d3Ea209epSm0KJUEWj9iSnhmV1h4WAu5ETjKgKH1RTaSlemYmgW75a_u70eG-dlYuPDQpHFlSeLdBIi8T3zPObsLmrIr1ylC8ifd6A58IZzhWPuIDijRe6l540FeOLspzv52HG/s320/policymanaged12.png" width="320" /></a></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiNHhxLnHqkbTJ7kHkxZfcG_fFcRsKhyphenhyphenqH5SrJ1bdPUH6bC5gM9_MCuX2mSJjMha0H96Oe5NLRkulQnAQMd8egRxshnX0HVVAtHylHD6yuCz_fR0ahgrXzCIysu4HoIvmCX1v8rVVbUhjHo/s1600/policymanaged13.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="320" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiNHhxLnHqkbTJ7kHkxZfcG_fFcRsKhyphenhyphenqH5SrJ1bdPUH6bC5gM9_MCuX2mSJjMha0H96Oe5NLRkulQnAQMd8egRxshnX0HVVAtHylHD6yuCz_fR0ahgrXzCIysu4HoIvmCX1v8rVVbUhjHo/s320/policymanaged13.png" width="319" /></a></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEicjs4Lgz8VCUlptqTshKgrFoSYOa2A0HzhwxmHq57ic-Y9DGpxBzp9G42z-5pacF7wo_LXhXo8Uxe71aWzlOa6vYcfpain5q4eM_Kk14ndLjUUUIpqCl0sgwqv8C9jPY_57ijbVC9fvHfN/s1600/policymanaged14.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="226" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEicjs4Lgz8VCUlptqTshKgrFoSYOa2A0HzhwxmHq57ic-Y9DGpxBzp9G42z-5pacF7wo_LXhXo8Uxe71aWzlOa6vYcfpain5q4eM_Kk14ndLjUUUIpqCl0sgwqv8C9jPY_57ijbVC9fvHfN/s320/policymanaged14.png" width="320" /></a></div>
[grid@rac1 ~]$ crsctl status serverpool
NAME=Free
ACTIVE_SERVERS=rac1
NAME=Generic
ACTIVE_SERVERS=
NAME=ora.myserverpool
ACTIVE_SERVERS=<b>rac2 rac3</b>
[oracle@rac1 ~]$ srvctl status database -d dell
Instance dell_1 is running on node rac2
Instance dell_2 is running on node rac3
</pre>
</div>
Ajay Singh Chandihttp://www.blogger.com/profile/05807334441165607306noreply@blogger.com0tag:blogger.com,1999:blog-9081789365774441839.post-25222924543637448432013-01-21T00:03:00.000+05:302013-01-21T00:04:10.101+05:30How to Password Protect Listener in 11g ?<pre>
As per Oracle Documentation from Oracle 11g Release 2 (11.2), the password feature is being deprecated.
This does not cause a loss of security because authentication is enforced through local operating
system authentication.
If remote administration of listener is required, then use one of the following methods to
connect to and administer the listener.
1) Connect to the host where listener is running using SSH or other secure method.
2) Use Oracle Enterprise Manager to adminster the listener.
Listener Name: <b>prod</b>
<b>
Log in to the listener control utility.</b>
[oracle@canada ~]$ lsnrctl
#Select listener name.
LSNRCTL> set current_listener prod
<code>Current Listener is prod</code>
#Change password(press enter for old password).
LSNRCTL> change_password
<code>Old password:
New password:
Reenter new password:
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=canada.example.com)(PORT=1521)))
Password changed for prod
The command completed successfully</code>
#Save the configuration.
LSNRCTL> save_config
<code>Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=canada.example.com)(PORT=1521)))
Saved prod configuration parameters.
Listener Parameter File /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
Old Parameter File /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.bak
The command completed successfully</code>
<b>In listener.ora file insert the following line.</b>
LOCAL_OS_AUTHENTICATION_PROD=OFF
#Restart the listener.
[oracle@canada ~]$ lsnrctl
LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 04-JAN-2013 11:43:43
Copyright (c) 1991, 2009, Oracle. All rights reserved.
Welcome to LSNRCTL, type "help" for information.
LSNRCTL> set current_listener prod
<code>Current Listener is prod</code>
LSNRCTL> stop
<code>Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=canada.example.com)(PORT=1521)))
The command completed successfully</code>
LSNRCTL> start
<code>Starting /u01/app/oracle/product/11.2.0/db_1/bin/tnslsnr: please wait...
TNSLSNR for Linux: Version 11.2.0.1.0 - Production
System parameter file is /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/canada/prod/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=canada.example.com)(PORT=1521)))
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=canada.example.com)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias prod
Version TNSLSNR for Linux: Version 11.2.0.1.0 - Production
Start Date 04-JAN-2013 11:43:59
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
<b>Security ON: Password</b>
SNMP OFF
Listener Parameter File /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/canada/prod/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=canada.example.com)(PORT=1521)))
Services Summary...
Service "dell" has 1 instance(s).
Instance "dell", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully</code>
#At this stage the password has been successfully implemented.
#Stopping a listener at this stage will result in an TNS error.
LSNRCTL> stop
<code>Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=canada.example.com)(PORT=1521)))
<b>TNS-01169: The listener has not recognized the password</b></code>
#To stop it set the password.
LSNRCTL> set password
<code>Password:
The command completed successfully</code>
LSNRCTL> stop
<code>Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=canada.example.com)(PORT=1521)))
The command completed successfully</code>
LSNRCTL> start
<code>Starting /u01/app/oracle/product/11.2.0/db_1/bin/tnslsnr: please wait...
TNSLSNR for Linux: Version 11.2.0.1.0 - Production
System parameter file is /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/canada/prod/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=canada.example.com)(PORT=1521)))
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=canada.example.com)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias prod
Version TNSLSNR for Linux: Version 11.2.0.1.0 - Production
Start Date 04-JAN-2013 11:44:51
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
<b>Security ON: Password</b>
SNMP OFF
Listener Parameter File /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/canada/prod/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=canada.example.com)(PORT=1521)))
Services Summary...
Service "dell" has 1 instance(s).
Instance "dell", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully</code>
</pre>Ajay Singh Chandihttp://www.blogger.com/profile/05807334441165607306noreply@blogger.com0tag:blogger.com,1999:blog-9081789365774441839.post-47075794805162551232013-01-16T22:56:00.001+05:302013-01-17T22:50:27.977+05:30How to Configure DNS Server in RHEL 6?<pre>
<b>Server IP Address:</b> 192.168.1.121
<b>Hostname:</b> newyork.example.com
[root@newyork named]# vi /etc/sysconfig/network-scripts/ifcfg-eth0
<a name='more'></a>
<code>DEVICE=eth0
HWADDR=00:0c:29:a8:9e:61
<b>NM_CONTROLLED=no
ONBOOT=yes
IPADDR=192.168.1.121</b>
BOOTPROTO=none
NETMASK=255.255.255.0
TYPE=Ethernet
GATEWAY=192.168.1.1
IPV6INIT=no
USERCTL=no</code>
[root@newyork named]# vi /etc/sysconfig/network
<code>NETWORKING=yes
<b>HOSTNAME=newyork.example.com</b></code>
[root@newyork named]# service network restart
<code>Shutting down interface eth0: [ OK ]
Shutting down loopback interface: [ OK ]
Bringing up loopback interface: [ OK ]
Bringing up interface eth0: [ OK ]</code>
[root@newyork ~]# yum install -y *bind* caching-nameserver
[root@newyork ~]# ifconfig eth0
<code>eth0 Link encap:Ethernet HWaddr 00:0C:29:A8:9E:61
inet addr:<b>192.168.1.121</b> Bcast:192.168.1.255 Mask:255.255.255.0
inet6 addr: fe80::20c:29ff:fea8:9e61/64 Scope:Link
UP BROADCAST RUNNING MULTICAST MTU:1500 Metric:1
RX packets:138 errors:0 dropped:0 overruns:0 frame:0
TX packets:184 errors:0 dropped:0 overruns:0 carrier:0
collisions:0 txqueuelen:1000
RX bytes:13080 (12.7 KiB) TX bytes:29969 (29.2 KiB)
Interrupt:19 Base address:0x2024</code>
[root@newyork ~]# cd /etc/
[root@newyork etc]# ll named*
-rw-r----- 1 root named 930 Feb 15 2010 named.conf
-rw-r--r-- 1 root named 601 May 26 2010 named.iscdlv.key
-rw-r----- 1 root named 931 Jun 21 2007 named.rfc1912.zones
named:
total 0
[root@newyork etc]# cp named.conf named.conf.orig
[root@newyork etc]# vi named.conf
<code>//
// named.conf
//
// Provided by Red Hat bind package to configure the ISC BIND named(8) DNS
// server as a caching only nameserver (as a localhost DNS resolver only).
//
// See /usr/share/doc/bind*/sample/ for example named configuration files.
//
options {
<b>listen-on port 53 { 192.168.1.121; };
# listen-on-v6 port 53 { ::1; };</b>
directory "/var/named";
dump-file "/var/named/data/cache_dump.db";
statistics-file "/var/named/data/named_stats.txt";
memstatistics-file "/var/named/data/named_mem_stats.txt";
<b> allow-query { any; };</b>
recursion yes;
dnssec-enable yes;
dnssec-validation yes;
dnssec-lookaside auto;
/* Path to ISC DLV key */
bindkeys-file "/etc/named.iscdlv.key";
};
logging {
channel default_debug {
file "data/named.run";
severity dynamic;
};
};
zone "." IN {
type hint;
file "named.ca";
};
include "/etc/named.rfc1912.zones";</code>
[root@newyork etc]# cp named.rfc1912.zones named.rfc1912.zones.orig
[root@newyork etc]# vi named.rfc1912.zones
<code>// named.rfc1912.zones:
//
// Provided by Red Hat caching-nameserver package
//
// ISC BIND named zone configuration for zones recommended by
// RFC 1912 section 4.1 : localhost TLDs and address zones
// and http://www.ietf.org/internet-drafts/draft-ietf-dnsop-default-local-zones-02.txt
// (c)2007 R W Franks
//
// See /usr/share/doc/bind*/sample/ for example named configuration files.
//
<b>zone "example.com" IN {</b>
type master;
<b> file "forward.zone";</b>
allow-update { none; };
};
zone "localhost" IN {
type master;
file "named.localhost";
allow-update { none; };
};
zone "1.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.ip6.arpa" IN {
type master;
file "named.loopback";
allow-update { none; };
};
<b>zone "1.168.192.in-addr.arpa" IN {</b>
type master;
<b> file "reverse.zone";</b>
allow-update { none; };
};
zone "0.in-addr.arpa" IN {
type master;
file "named.empty";
allow-update { none; };
};</code>
[root@newyork etc]# chgrp named named.conf
[root@newyork etc]# cd /var/named/
[root@newyork named]# cp named.localhost forward.zone
[root@newyork named]# cp named.loopback reverse.zone
[root@newyork named]# vi forward.zone
<code>$TTL 1D
@ IN SOA <b>newyork.example.com. root.newyork.example.com.</b> (
0 ; serial
1D ; refresh
1H ; retry
1W ; expire
3H ) ; minimum
<b> IN NS newyork.example.com.
newyork IN A 192.168.1.121</b></code>
[root@newyork named]# vi reverse.zone
<code>$TTL 1D
@ IN SOA <b>newyork.example.com. root.newyork.example.com.</b> (
0 ; serial
1D ; refresh
1H ; retry
1W ; expire
3H ) ; minimum
<b> IN NS newyork.example.com.
121 IN PTR newyork.example.com.</b></code>
[root@newyork named]# chgrp named forward.zone
[root@newyork named]# chgrp named reverse.zone
[root@newyork named]# vi /etc/hosts
<code>127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4
<b>192.168.1.121 newyork.example.com newyork</b></code>
[root@newyork named]# vi /etc/resolv.conf
<code># Generated by NetworkManager
<b>search example.com
nameserver 192.168.1.121</b>
# No nameservers found; try putting DNS servers into your
# ifcfg files in /etc/sysconfig/network-scripts like so:
#
# DNS1=xxx.xxx.xxx.xxx
# DNS2=xxx.xxx.xxx.xxx
# DOMAIN=lab.foo.com bar.foo.com</code>
[root@newyork named]# service named restart
<code>Stopping named: [ OK ]
Starting named: [ OK ]</code>
[root@newyork named]# dig newyork.example.com
<code>; <<>> DiG 9.7.0-P2-RedHat-9.7.0-5.P2.el6 <<>> newyork.example.com
;; global options: +cmd
;; Got answer:
;; ->>HEADER<<- opcode: QUERY, status: NOERROR, id: 1113
;; flags: qr aa rd ra; <b>QUERY: 1, ANSWER: 1, AUTHORITY: 1</b>, ADDITIONAL: 0
;; QUESTION SECTION:
;newyork.example.com. IN A
;; ANSWER SECTION:
newyork.example.com. 86400 IN A 192.168.1.121
;; AUTHORITY SECTION:
example.com. 86400 IN NS newyork.example.com.
;; Query time: 1 msec
;; SERVER: 192.168.1.121#53(192.168.1.121)
;; WHEN: Wed Jan 16 11:26:32 2013
;; MSG SIZE rcvd: 67</code>
[root@newyork named]# dig -x 192.168.1.121
<code>; <<>> DiG 9.7.0-P2-RedHat-9.7.0-5.P2.el6 <<>> -x 192.168.1.121
;; global options: +cmd
;; Got answer:
;; ->>HEADER<<- opcode: QUERY, status: NOERROR, id: 57162
;; flags: qr aa rd ra; <b>QUERY: 1, ANSWER: 1, AUTHORITY: 1, ADDITIONAL: 1</b>
;; QUESTION SECTION:
;121.1.168.192.in-addr.arpa. IN PTR
;; ANSWER SECTION:
121.1.168.192.in-addr.arpa. 86400 IN PTR newyork.example.com.
;; AUTHORITY SECTION:
1.168.192.in-addr.arpa. 86400 IN NS newyork.example.com.
;; ADDITIONAL SECTION:
newyork.example.com. 86400 IN A 192.168.1.121
;; Query time: 1 msec
;; SERVER: 192.168.1.121#53(192.168.1.121)
;; WHEN: Wed Jan 16 11:26:59 2013
;; MSG SIZE rcvd: 107</code>
[root@newyork named]# nslookup newyork.example.com
<code>Server: 192.168.1.121
Address: 192.168.1.121#53
Name: newyork.example.com
Address: 192.168.1.121</code>
[root@newyork named]# nslookup 192.168.1.121
<code>Server: 192.168.1.121
Address: 192.168.1.121#53
121.1.168.192.in-addr.arpa name = newyork.example.com.</code>
[root@newyork named]# chkconfig named --list
<code>named 0:off 1:off 2:off 3:off 4:off 5:off 6:off</code>
[root@newyork named]# chkconfig named on
[root@newyork named]# chkconfig named --list
<code>named 0:off 1:off 2:on 3:on 4:on 5:on 6:off</code>
</pre>
Ajay Singh Chandihttp://www.blogger.com/profile/05807334441165607306noreply@blogger.com2tag:blogger.com,1999:blog-9081789365774441839.post-86100151321830863612012-12-27T19:34:00.000+05:302013-01-17T23:20:12.707+05:30Different ways to get the DBID of Database.<div dir="ltr" style="text-align: left;" trbidi="on">
<pre><b>Method 1:Through SQL command prompt.</b>
SQL> select dbid from v$database;
DBID
----------
3764293269
<a name='more'></a>
<b>Method 2:Through RMAN</b>
[oracle@canada ~]$ rman target =/
Recovery Manager: Release 11.2.0.1.0 - Production on Fri Dec 14 16:41:00 2012
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: DELL <b>(DBID=3764293269)
</b>
RMAN>
<b>Method 3: By Generating Trace Files</b>
SQL> startup nomount
SQL> alter session set tracefile_identifier = myoradump;
SQL> alter system dump datafile '/u01/app/oracle/oradata/dell/system01.dbf' block min 1 block max 10;
In user_dump_test find trace file <b>"myoradump"</b> in that search <b>"Db ID"</b>
Start dump data block from file /u01/app/oracle/oradata/dell/system01.dbf minblk 1 maxblk 10
V10 STYLE FILE HEADER:
Compatibility Vsn = 186646528=0xb200000
<b>Db ID=3764293269</b>=0xe05e8e95, Db Name='DELL'
Activation ID=0=0x0
Control Seq=659=0x293, File size=85760=0x14f00
File Number=1, Blksiz=8192, File Type=3 DATA
Same command can be used for log and archived log files
SQL> alter system dump logfile" "
</pre>
</div>
Ajay Singh Chandihttp://www.blogger.com/profile/05807334441165607306noreply@blogger.com0tag:blogger.com,1999:blog-9081789365774441839.post-41221878059417283632012-12-27T19:25:00.000+05:302013-01-17T23:20:04.625+05:30Simulating Oracle Tape Backups.<div dir="ltr" style="text-align: left;" trbidi="on">
<pre><b>As per Oracle Note:312737.1</b>
The Oracle DiskSbt API is designed for TESTING purposes ONLY
and can NOT be used for any database backup.
The Oracle.Disksbt can be used to simulate tape backups on disk using sbt_tape
channel.
<a name='more'></a>
[oracle@canada ~]$ rman target = / nocatalog
Recovery Manager: Release 11.2.0.1.0 - Production on Thu Dec 27 18:53:45 2012
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: DELL (DBID=3764293269)
using target database control file instead of recovery catalog
<b>RMAN> run {
2> allocate channel 't1' type 'sbt_tape'
3> parms 'SBT_LIBRARY=oracle.disksbt, ENV=(BACKUP_DIR=/u01/backup)';
4> backup database plus archivelog format='%U';
5> }</b>
<code>allocated channel: t1
channel t1: SID=46 device type=SBT_TAPE
channel t1: WARNING: Oracle Test Disk API
Starting backup at 27-DEC-12
current log archived
channel t1: starting archived log backup set
channel t1: specifying archived log(s) in backup set
input archived log thread=1 sequence=3 RECID=1 STAMP=803155707
input archived log thread=1 sequence=4 RECID=2 STAMP=803155714
input archived log thread=1 sequence=5 RECID=3 STAMP=803155911
input archived log thread=1 sequence=6 RECID=4 STAMP=803156172
channel t1: starting piece 1 at 27-DEC-12
channel t1: finished piece 1 at 27-DEC-12
piece handle=02ntuc6c_1_1 tag=TAG20121227T185612 comment=API Version 2.0,MMS Version 8.1.3.0
channel t1: backup set complete, elapsed time: 00:00:01
Finished backup at 27-DEC-12
Starting backup at 27-DEC-12
channel t1: starting full datafile backup set
channel t1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/oradata/dell/system01.dbf
input datafile file number=00002 name=/u01/app/oracle/oradata/dell/sysaux01.dbf
input datafile file number=00005 name=/u01/app/oracle/oradata/dell/example01.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/dell/undotbs01.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/dell/users01.dbf
channel t1: starting piece 1 at 27-DEC-12
channel t1: finished piece 1 at 27-DEC-12
piece handle=03ntuc6e_1_1 tag=TAG20121227T185613 comment=API Version 2.0,MMS Version 8.1.3.0
channel t1: backup set complete, elapsed time: 00:02:03
channel t1: starting full datafile backup set
channel t1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel t1: starting piece 1 at 27-DEC-12
channel t1: finished piece 1 at 27-DEC-12
piece handle=04ntuca9_1_1 tag=TAG20121227T185613 comment=API Version 2.0,MMS Version 8.1.3.0
channel t1: backup set complete, elapsed time: 00:00:01
Finished backup at 27-DEC-12
Starting backup at 27-DEC-12
current log archived
channel t1: starting archived log backup set
channel t1: specifying archived log(s) in backup set
input archived log thread=1 sequence=7 RECID=5 STAMP=803156303
channel t1: starting piece 1 at 27-DEC-12
channel t1: finished piece 1 at 27-DEC-12
piece handle=05ntucah_1_1 tag=TAG20121227T185824 comment=API Version 2.0,MMS Version 8.1.3.0
channel t1: backup set complete, elapsed time: 00:00:01
Finished backup at 27-DEC-12
released channel: t1</code>
</pre>
</div>
Ajay Singh Chandihttp://www.blogger.com/profile/05807334441165607306noreply@blogger.com0tag:blogger.com,1999:blog-9081789365774441839.post-87170655034398142832012-12-27T19:17:00.000+05:302013-01-17T23:19:55.394+05:30Oracle Database links.<div dir="ltr" style="text-align: left;" trbidi="on">
<br /><pre><b>Primary database name</b> = dell
<b>Secondary Database Name</b> = dup
<b>On Secondary</b>
<b>
<i>Create TNS Entry for primary database.</i></b>
[oracle@newyork admin]$ cat tnsnames.ora
<code># tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
<a name='more'></a>
REMOTEDB =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.39)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = dell)
)
)</code>
<i><b>Login as sys</b></i>
[oracle@newyork ~]$ sqlplus
SQL*Plus: Release 11.2.0.1.0 Production on Wed Dec 26 19:21:11 2012
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Enter user-name: / as sysdba
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> grant create database link to scott;
SQL> grant create database link to hr;
Grant succeeded.
<b>Private connected user database link</b>
SQL> conn scott/tiger;
Connected.
SQL> create database link mylink1 using 'remotedb';
Database link created.
SQL> select * from emp@mylink1 where empno=7900;
<code> EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- --------- ---------- ----------
DEPTNO
----------
7900 JAMES CLERK 7698 03-DEC-81 9500
30</code>
SQL> select * from user_db_links;
<code>DB_LINK
---------------------------------------------------------------------------------------------------
USERNAME PASSWORD
------------------------------ ------------------------------
HOST
---------------------------------------------------------------------------------------------------
CREATED
---------
MYLINK1.EXAMPLE.COM
remotedb
26-DEC-12</code>
<b>Private fixed user database link</b>
Connect as HR user.
SQL> conn hr/hr
Connected.
SQL> create database link mylink2
2 connect to scott identified by tiger using 'remotedb';
Database link created.
SQL> select * from scott.emp@mylink2 where empno=7900;
<code> EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- --------- ---------- ----------
DEPTNO
----------
7900 JAMES CLERK 7698 03-DEC-81 9500
30</code>
SQL> select * from user_db_links;
<code>DB_LINK
---------------------------------------------------------------------------------------------------
USERNAME PASSWORD
------------------------------ ------------------------------
HOST
---------------------------------------------------------------------------------------------------
CREATED
---------
MYLINK2.EXAMPLE.COM
SCOTT
remotedb
26-DEC-12</code>
<b>
Public fixed user database link.</b>
SQL> conn / as sysdba
Connected.
SQL> create public database link mylink3
2 connect to scott identified by tiger using 'remotedb';
Database link created.
SQL> conn tom/jerry
Connected.
SQL> select * from scott.emp@mylink3 where empno=7900;
<code> EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- --------- ---------- ----------
DEPTNO
----------
7900 JAMES CLERK 7698 03-DEC-81 9500
30</code>
SQL> select * from dba_db_links;
<code>OWNER
------------------------------
DB_LINK
---------------------------------------------------------------------------------------------------
USERNAME
------------------------------
HOST
---------------------------------------------------------------------------------------------------
CREATED
---------
PUBLIC
MYLINK3.EXAMPLE.COM
SCOTT
remotedb
26-DEC-12
SCOTT
MYLINK1.EXAMPLE.COM
remotedb
26-DEC-12
HR
MYLINK2.EXAMPLE.COM
SCOTT
remotedb
26-DEC-12</code>
<b>
Dropping database links.</b>
DROP [PUBLIC] DATABASE LINK link_name;
<b>Note:</b> You cannot drop a database link in another user's schema and you have to
specify PUBLIC option in order to drop a public database link.
</pre>
</div>
Ajay Singh Chandihttp://www.blogger.com/profile/05807334441165607306noreply@blogger.com0tag:blogger.com,1999:blog-9081789365774441839.post-51962092017797000802012-12-11T20:49:00.000+05:302013-01-17T23:19:45.391+05:30Oracle 11gR2 RAC Node Deletion.<div dir="ltr" style="text-align: left;" trbidi="on">
<pre><b>NodeName which has to be removed from the cluster - rac3</b>
<b>Backup OCR from rac1</b>
[root@rac1 ~]# /u01/app/11.2.0/grid/bin/ocrconfig -manualbackup
<a name='more'></a>
<b>Remove instance by running DBCA from rac1</b>
[root@rac1 ~]# xhost +
access control disabled, clients can connect from any host
[root@rac1 ~]# su - oracle
[oracle@rac1 ~]$ dbca
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhbHooueBIffevb994Jz1hkRGaUKEgNUyKmM6zNRvy0cVFccli6aF_8ifSJUu44Bx-eEMZ3I-YJDTlnLHJ35UuDQDV1c0Cw7ZZE-svRMdPsWFuoDEdK1bjywAd9R7uUZSzP1Wn1N_mygOfX/s1600/nodedell1.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="227" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhbHooueBIffevb994Jz1hkRGaUKEgNUyKmM6zNRvy0cVFccli6aF_8ifSJUu44Bx-eEMZ3I-YJDTlnLHJ35UuDQDV1c0Cw7ZZE-svRMdPsWFuoDEdK1bjywAd9R7uUZSzP1Wn1N_mygOfX/s320/nodedell1.png" width="320" /></a></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjaXGYbNlr6-aFXVRXpt5QNJCQckyJyDCPtkgMOphA2zpIwTGnlzgXQODVz8krVyzbKT1i6qQS6JFw0t4_erAbQvqQNvVE3GOXTRj1fSShd80jNvLGES8ThZlZNKJq4ArvyXVXpLk2nYddC/s1600/nodedell2.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="227" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjaXGYbNlr6-aFXVRXpt5QNJCQckyJyDCPtkgMOphA2zpIwTGnlzgXQODVz8krVyzbKT1i6qQS6JFw0t4_erAbQvqQNvVE3GOXTRj1fSShd80jNvLGES8ThZlZNKJq4ArvyXVXpLk2nYddC/s320/nodedell2.png" width="320" /></a></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjmbKNnqbUqcUuLta5AloZsJMr2fgYro-oYhyY_a04upZUpnMvZMmQlH1754ml9JFdXSqJJ45g-WvfSz9_ig9ieVmal6jcWmG4KjqoyWQflkbryXj0diPys1-CWJH8NQ3ARhjwixvUahJTJ/s1600/nodedell3.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="226" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjmbKNnqbUqcUuLta5AloZsJMr2fgYro-oYhyY_a04upZUpnMvZMmQlH1754ml9JFdXSqJJ45g-WvfSz9_ig9ieVmal6jcWmG4KjqoyWQflkbryXj0diPys1-CWJH8NQ3ARhjwixvUahJTJ/s320/nodedell3.png" width="320" /></a></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjOMYfxCzrXzrdm3kw_t6ZzX8kagV9nOAQExmPvCzIAvf2DJosJRd6V9P8CQnKvsqkRW93DaYV43Lb1FhuJAlDctmAnpmNOOWEV4tvaIScecIP0dxZrdFxGuciuvav75tq4BAhQbXmrDWnb/s1600/nodedell4.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="227" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjOMYfxCzrXzrdm3kw_t6ZzX8kagV9nOAQExmPvCzIAvf2DJosJRd6V9P8CQnKvsqkRW93DaYV43Lb1FhuJAlDctmAnpmNOOWEV4tvaIScecIP0dxZrdFxGuciuvav75tq4BAhQbXmrDWnb/s320/nodedell4.png" width="320" /></a></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh1vcybRXCl3BT6XiIewLsh0EQdSu5P9bLQa700B1mqt93TnI0vRiMtLgBzJp3vEhvbRHI8L83OHlkUub3csXAinn3W8OfbFEV5y_M0x8tU146eeGe1aKYjTJmnfpxX7WS7oTTE-al_6utd/s1600/nodedell5.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="226" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh1vcybRXCl3BT6XiIewLsh0EQdSu5P9bLQa700B1mqt93TnI0vRiMtLgBzJp3vEhvbRHI8L83OHlkUub3csXAinn3W8OfbFEV5y_M0x8tU146eeGe1aKYjTJmnfpxX7WS7oTTE-al_6utd/s320/nodedell5.png" width="320" /></a></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgA7-ww6xKCg7PxeJIkshaLj5oJ7CS9VugcPzakso2Dlu1jvS7gGpasEUfZd9MyP5i7ll14ffPjWCyD2U39mc4Pn0V1Oi-8jvRLyoQ9fQlD8JqZgVM3QoEWd_AayoTkOtsJ_KXtm8UCKUCD/s1600/nodedell6.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="228" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgA7-ww6xKCg7PxeJIkshaLj5oJ7CS9VugcPzakso2Dlu1jvS7gGpasEUfZd9MyP5i7ll14ffPjWCyD2U39mc4Pn0V1Oi-8jvRLyoQ9fQlD8JqZgVM3QoEWd_AayoTkOtsJ_KXtm8UCKUCD/s320/nodedell6.png" width="320" /></a></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhDIt9Lp3ZgZc1zVt5T_2pBgsANcvB9J-fuHh1V53EgSYJezVpr1D05MGYY_RM9_zu5-_EdXVc9qz5U_keOw1-cshAEZyQ7MQk4Re8MWRONGTcCj8DHk16a7SEMI4tqP45jpbXhD2pWLMCc/s1600/nodedell7.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="226" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhDIt9Lp3ZgZc1zVt5T_2pBgsANcvB9J-fuHh1V53EgSYJezVpr1D05MGYY_RM9_zu5-_EdXVc9qz5U_keOw1-cshAEZyQ7MQk4Re8MWRONGTcCj8DHk16a7SEMI4tqP45jpbXhD2pWLMCc/s320/nodedell7.png" width="320" /></a></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgCadKidGJfLQ9ZhwQ_nUP0-gXMbj2HbmV32Rje05lwILK2q9L_b1ZgfRNcJWUzWYkkxiWLi9RJH3AidWFb5HJ5KnT-pZnoZdVQBPsi3EBgwv2TrAmXk-3G4vk-eo_HKmKGUUmqGTq4T7i8/s1600/nodedell8.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="227" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgCadKidGJfLQ9ZhwQ_nUP0-gXMbj2HbmV32Rje05lwILK2q9L_b1ZgfRNcJWUzWYkkxiWLi9RJH3AidWFb5HJ5KnT-pZnoZdVQBPsi3EBgwv2TrAmXk-3G4vk-eo_HKmKGUUmqGTq4T7i8/s320/nodedell8.png" width="320" /></a></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg41UlMG_yrltJEooD5YQmrggO0tLQH7605B2EkXjWqzr1inYzORlWu-d-IaxShswKxXC1mMJeUHrHHAne9WaD5JE3eF0zrCLApleUjXggFV0s5aEwZRdbm7t7ugE-ql-19KzJuM-dMPI9K/s1600/nodedell9.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="229" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg41UlMG_yrltJEooD5YQmrggO0tLQH7605B2EkXjWqzr1inYzORlWu-d-IaxShswKxXC1mMJeUHrHHAne9WaD5JE3eF0zrCLApleUjXggFV0s5aEwZRdbm7t7ugE-ql-19KzJuM-dMPI9K/s320/nodedell9.png" width="320" /></a></div>
<b>Verify</b>
[oracle@rac1 ~]$ srvctl status database -d dell
Instance dell1 is running on node rac1
Instance dell2 is running on node rac2
<b>
Remove oracle database software.</b>
From RAC3 run the ./runInstaller to update oracle inventory.
[oracle@rac3 ~]$ cd $ORACLE_HOME/oui/bin
[oracle@rac3 bin]$ ./runInstaller -updateNodeList ORACLE_HOME=$ORACLE_HOME "CLUSTER_NODES={rac3}" -local
<code>Starting Oracle Universal Installer...
Checking swap space: must be greater than 500 MB. Actual 3950 MB Passed
The inventory pointer is located at /etc/oraInst.loc
The inventory is located at /u01/app/oraInventory
'UpdateNodeList' was successful.</code>
# In the above command the "-local" hint is used, So that when we
# deinstall the oracle software, then only the local installation
# in removed otherwise. If this hint is not supplied then
# Oracle binaries from all the instances would be removed.
<b>Your oratab should not contain any database entries except for ASM entries.</b>
Sample oratab file
[root@rac3 ~]# cat /etc/oratab
<code>#Backup file is /u01/app/oracle/product/11.2.0/dbhome_1/srvm/admin/oratab.bak.rac3 line added by Agent
#
# This file is used by ORACLE utilities. It is created by root.sh
# and updated by the Database Configuration Assistant when creating
# a database.
# A colon, ':', is used as the field terminator. A new line terminates
# the entry. Lines beginning with a pound sign, '#', are comments.
#
# Entries are of the form:
# $ORACLE_SID:$ORACLE_HOME:<n>:
#
# The first and second fields are the system identifier and home
# directory of the database respectively. The third filed indicates
# to the dbstart utility that the database should , "Y", or should not,
# "N", be brought up at system boot time.
#
# Multiple entries with the same $ORACLE_SID are not allowed.
#
#
<b>+ASM3:/u01/app/11.2.0/grid:N # line added by Agent</b></n></code>
<b>Deinstall Oracle Software.</b>
[root@rac3 ~]# su - oracle
[oracle@rac3 ~]$
[oracle@rac3 ~]$ cd $ORACLE_HOME/deinstall
[oracle@rac3 deinstall]$ ./deinstall -local
<code>Checking for required files and bootstrapping ...
Please wait ...
Location of logs /u01/app/oraInventory/logs/
############ ORACLE DEINSTALL & DECONFIG TOOL START ############
######################## CHECK OPERATION START ########################
Install check configuration START
Checking for existence of the Oracle home location /u01/app/oracle/product/11.2.0/dbhome_1
Oracle Home type selected for de-install is: RACDB
Oracle Base selected for de-install is: /u01/app/oracle
Checking for existence of central inventory location /u01/app/oraInventory
Checking for existence of the Oracle Grid Infrastructure home /u01/app/11.2.0/grid
The following nodes are part of this cluster: rac3
Install check configuration END
Network Configuration check config START
Network de-configuration trace file location: /u01/app/oraInventory/logs/netdc_check4706613160153670172.log
Network Configuration check config END
Database Check Configuration START
Database de-configuration trace file location: /u01/app/oraInventory/logs/databasedc_check6208371534310470930.log
Database Check Configuration END
Enterprise Manager Configuration Assistant START
EMCA de-configuration trace file location: /u01/app/oraInventory/logs/emcadc_check.log
Enterprise Manager Configuration Assistant END
Oracle Configuration Manager check START
OCM check log file location : /u01/app/oraInventory/logs//ocm_check2937.log
Oracle Configuration Manager check END
######################### CHECK OPERATION END #########################
####################### CHECK OPERATION SUMMARY #######################
Oracle Grid Infrastructure Home is: /u01/app/11.2.0/grid
The cluster node(s) on which the Oracle home exists are:
(Please input nodes seperated by ",", eg: node1,node2,...)<b>rac3</b>
Since -local option has been specified, the Oracle home will be
de-installed only on the local node, 'rac3', and the global configuration will be removed.
Oracle Home selected for de-install is: /u01/app/oracle/product/11.2.0/dbhome_1
Inventory Location where the Oracle home registered is:
/u01/app/oraInventory
The option -local will not modify any database configuration for this Oracle home.
No Enterprise Manager configuration to be updated for any database(s)
No Enterprise Manager ASM targets to update
No Enterprise Manager listener targets to migrate
Checking the config status for CCR
Oracle Home exists with CCR directory, but CCR is not configured
CCR check is finished
Do you want to continue (y - yes, n - no)? [n]: <b>y</b>
A log of this session will be written to:
'/u01/app/oraInventory/logs/deinstall_deconfig2012-12-02_05-13-43-PM.out'
Any error messages from this session will be written to:
'/u01/app/oraInventory/logs/deinstall_deconfig2012-12-02_05-13-43-PM.err'
######################## CLEAN OPERATION START ########################
Enterprise Manager Configuration Assistant START
EMCA de-configuration trace file location: /u01/app/oraInventory/logs/emcadc_clean.log
Updating Enterprise Manager ASM targets (if any)
Updating Enterprise Manager listener targets (if any)
Enterprise Manager Configuration Assistant END
Database de-configuration trace file location: /u01/app/oraInventory/logs/databasedc_clean3983205340911706037.log
Network Configuration clean config START
Network de-configuration trace file location: /u01/app/oraInventory/logs/netdc_clean4816433689117593893.log
De-configuring Local Net Service Names configuration file...
Local Net Service Names configuration file de-configured successfully.
De-configuring backup files...
Backup files de-configured successfully.
The network configuration has been cleaned up successfully.
Network Configuration clean config END
Oracle Configuration Manager clean START
OCM clean log file location : /u01/app/oraInventory/logs//ocm_clean2937.log
Oracle Configuration Manager clean END
Oracle Universal Installer clean START
Detach Oracle home '/u01/app/oracle/product/11.2.0/dbhome_1' from the central inventory on the local node : Done
Delete directory '/u01/app/oracle/product/11.2.0/dbhome_1' on the local node : Done
Failed to delete the directory '/u01/app/oracle'. The directory is in use.
Delete directory '/u01/app/oracle' on the local node : Failed <<<<
Oracle Universal Installer cleanup completed with errors.
Oracle Universal Installer clean END
Oracle install clean START
Clean install operation removing temporary directory '/tmp/install' on node 'rac3'
Oracle install clean END
######################### CLEAN OPERATION END #########################
####################### CLEAN OPERATION SUMMARY #######################
Cleaning the config for CCR
As CCR is not configured, so skipping the cleaning of CCR configuration
CCR clean is finished
Successfully detached Oracle home '/u01/app/oracle/product/11.2.0/dbhome_1'
from the central inventory on the local node.
Successfully deleted directory '/u01/app/oracle/product/11.2.0/dbhome_1' on the local node.
Failed to delete directory '/u01/app/oracle' on the local node.
Oracle Universal Installer cleanup completed with errors.
Oracle install successfully cleaned up the temporary directories.
#######################################################################
############# ORACLE DEINSTALL & DECONFIG TOOL END #############</code>
<b>On the remaining nodes (rac1,rac2) update the oraInventory.</b>
[root@rac1 ~]# su - oracle
[oracle@rac1 ~]$ cd $ORACLE_HOME/oui/bin
[oracle@rac1 bin]$ ./runInstaller -updateNodeList ORACLE_HOME=$ORACLE_HOME "CLUSTER_NODES={rac1,rac2}"
<code>Starting Oracle Universal Installer...
Checking swap space: must be greater than 500 MB. Actual 3907 MB Passed
The inventory pointer is located at /etc/oraInst.loc
The inventory is located at /u01/app/oraInventory
'UpdateNodeList' was successful.</code>
<b>Verify that the Inventory.xml file on both nodes should only contain
dbhome entries for rac1 and rac2.</b>
[oracle@rac1 ContentsXML]$ cat /u01/app/oraInventory/ContentsXML/inventory.xml
<code><?xml version="1.0" standalone="yes" ?>
<!-- Copyright (c) 1999, 2009, Oracle. All rights reserved. -->
<!-- Do not modify the contents of this file by hand. -->
<INVENTORY>
<VERSION_INFO>
<SAVED_WITH>11.2.0.1.0</SAVED_WITH>
<MINIMUM_VER>2.1.0.6.0</MINIMUM_VER>
</VERSION_INFO>
<HOME_LIST>
<HOME NAME="Ora11g_gridinfrahome1" LOC="/u01/app/11.2.0/grid" TYPE="O" IDX="1" CRS="true">
<NODE_LIST>
<NODE NAME="rac1"/>
<NODE NAME="rac2"/>
<NODE NAME="rac3"/>
</NODE_LIST>
</HOME>
<HOME NAME="OraDb11g_home1" LOC="/u01/app/oracle/product/11.2.0/dbhome_1" TYPE="O" IDX="2">
<NODE_LIST>
<b><NODE NAME="rac1"/>
<NODE NAME="rac2"/></b>
</NODE_LIST>
</HOME>
</HOME_LIST>
</INVENTORY></code>
<b>Remove Clusterware from the rac3.</b>
[root@rac1 ~]# cd /u01/app/11.2.0/grid/bin/
[root@rac1 bin]# ./olsnodes -s -t
<code>rac1 Active Unpinned
rac2 Active Unpinned
rac3 Active Unpinned</code>
The third node should be unpinned if it's not then do the following.
[root@rac1 bin]#./crsctl unpin css -n rac3
<b>
Disable Oracle clusterware by executing the following from rac3.</b>
[root@rac3 ~]# cd /u01/app/11.2.0/grid/crs/install/
[root@rac3 install]# ./rootcrs.pl -deconfig -force
<code>2012-12-02 17:35:41: Parsing the host name
2012-12-02 17:35:41: Checking for super user privileges
2012-12-02 17:35:41: User has super user privileges
Using configuration parameter file: ./crsconfig_params
VIP exists.:rac1
VIP exists.: /rac1-vip/192.168.1.251/255.255.255.0/eth0
VIP exists.:rac2
VIP exists.: /rac2-vip/192.168.1.252/255.255.255.0/eth0
VIP exists.:rac3
VIP exists.: /rac3-vip/192.168.1.250/255.255.255.0/eth0
GSD exists.
ONS daemon exists. Local port 6100, remote port 6200
eONS daemon exists. Multicast port 17994, multicast IP address 234.58.121.78, listening port 2016
ACFS-9200: Supported
CRS-2673: Attempting to stop 'ora.registry.acfs' on 'rac3'
CRS-2677: Stop of 'ora.registry.acfs' on 'rac3' succeeded
CRS-2791: Starting shutdown of Oracle High Availability Services-managed resources on 'rac3'
CRS-2673: Attempting to stop 'ora.crsd' on 'rac3'
CRS-2790: Starting shutdown of Cluster Ready Services-managed resources on 'rac3'
CRS-2673: Attempting to stop 'ora.CRS.dg' on 'rac3'
CRS-2673: Attempting to stop 'ora.DATA.dg' on 'rac3'
CRS-2673: Attempting to stop 'ora.FRA.dg' on 'rac3'
CRS-2677: Stop of 'ora.CRS.dg' on 'rac3' succeeded
CRS-2677: Stop of 'ora.DATA.dg' on 'rac3' succeeded
CRS-2677: Stop of 'ora.FRA.dg' on 'rac3' succeeded
CRS-2673: Attempting to stop 'ora.asm' on 'rac3'
CRS-2677: Stop of 'ora.asm' on 'rac3' succeeded
CRS-2792: Shutdown of Cluster Ready Services-managed resources on 'rac3' has completed
CRS-2677: Stop of 'ora.crsd' on 'rac3' succeeded
CRS-2673: Attempting to stop 'ora.mdnsd' on 'rac3'
CRS-2673: Attempting to stop 'ora.drivers.acfs' on 'rac3'
CRS-2673: Attempting to stop 'ora.cssdmonitor' on 'rac3'
CRS-2673: Attempting to stop 'ora.ctssd' on 'rac3'
CRS-2673: Attempting to stop 'ora.evmd' on 'rac3'
CRS-2673: Attempting to stop 'ora.asm' on 'rac3'
CRS-2677: Stop of 'ora.cssdmonitor' on 'rac3' succeeded
CRS-2677: Stop of 'ora.evmd' on 'rac3' succeeded
CRS-2677: Stop of 'ora.mdnsd' on 'rac3' succeeded
CRS-2677: Stop of 'ora.ctssd' on 'rac3' succeeded
CRS-2677: Stop of 'ora.drivers.acfs' on 'rac3' succeeded
CRS-2677: Stop of 'ora.asm' on 'rac3' succeeded
CRS-2673: Attempting to stop 'ora.cssd' on 'rac3'
CRS-2677: Stop of 'ora.cssd' on 'rac3' succeeded
CRS-2673: Attempting to stop 'ora.gpnpd' on 'rac3'
CRS-2673: Attempting to stop 'ora.diskmon' on 'rac3'
CRS-2677: Stop of 'ora.gpnpd' on 'rac3' succeeded
CRS-2673: Attempting to stop 'ora.gipcd' on 'rac3'
CRS-2677: Stop of 'ora.gipcd' on 'rac3' succeeded
CRS-2677: Stop of 'ora.diskmon' on 'rac3' succeeded
CRS-2793: Shutdown of Oracle High Availability Services-managed resources on 'rac3' has completed
CRS-4133: Oracle High Availability Services has been stopped.
Successfully deconfigured Oracle clusterware stack on this node</code>
<b>Run the following from rac1 to verify.</b>
[root@rac1 ~]# cd /u01/app/11.2.0/grid/bin/
[root@rac1 bin]# ./crsctl delete node -n rac3
<code>CRS-4661: Node rac3 successfully deleted.</code>
[root@rac1 bin]# ./olsnodes -t -s
<code>rac1 Active Unpinned
rac2 Active Unpinned</code>
<b>
Update Oracle inventory from rac3</b>
[grid@rac3 ~]$ cd $GRID_HOME/oui/bin
[grid@rac3 bin]$ ./runInstaller -updateNodeList ORACLE_HOME=$GRID_HOME "CLUSTER_NODES={rac3}" CRS=TRUE -local
<code>Starting Oracle Universal Installer...
Checking swap space: must be greater than 500 MB. Actual 4094 MB Passed
The inventory pointer is located at /etc/oraInst.loc
The inventory is located at /u01/app/oraInventory
'UpdateNodeList' was successful.</code>
<b>
Deinstall Grid Infrastructure Software from rac3.</b>
[grid@rac3 ~]$ cd $GRID_HOME/deinstall
[grid@rac3 deinstall]$ ./deinstall -local
<code>Checking for required files and bootstrapping ...
Please wait ...
Location of logs /tmp/deinstall2012-12-02_05-51-29-PM/logs/
############ ORACLE DEINSTALL & DECONFIG TOOL START ############
######################## CHECK OPERATION START ########################
Install check configuration START
Checking for existence of the Oracle home location /u01/app/11.2.0/grid
Oracle Home type selected for de-install is: CRS
Oracle Base selected for de-install is: /u01/app/grid
Checking for existence of central inventory location /u01/app/oraInventory
Checking for existence of the Oracle Grid Infrastructure home
The following nodes are part of this cluster: rac3
Install check configuration END
Traces log file: /tmp/deinstall2012-12-02_05-51-29-PM/logs//crsdc.log
Enter an address or the name of the virtual IP used on node "rac3"[rac3-vip]
> PRESS ENTER
The following information can be collected by running ifconfig -a on node "rac3"
Enter the IP netmask of Virtual IP "192.168.1.250" on node "rac3"[255.255.255.0]
> PRESS ENTER
Enter the network interface name on which the virtual IP address "192.168.1.250" is active
> PRESS ENTER
Enter an address or the name of the virtual IP[]
> PRESS ENTER
Network Configuration check config START
Network de-configuration trace file location:
/tmp/deinstall2012-12-02_05-51-29-PM/logs/netdc_check6970228565355526821.log
Specify all RAC listeners that are to be de-configured [LISTENER,LISTENER_SCAN2]:LISTENER
At least one listener from the discovered listener list [LISTENER,LISTENER_SCAN2] is missing
in the specified listener list [LISTENER]. The Oracle home will be cleaned up, so all the
listeners will not be available after deinstall. If you want to remove a specific listener, please use
Oracle Net Configuration Assistant instead. Do you want to continue? (y|n) [n]: <b>y</b>
Network Configuration check config END
Asm Check Configuration START
ASM de-configuration trace file location:
/tmp/deinstall2012-12-02_05-51-29-PM/logs/asmcadc_check1139713786370590829.log
######################### CHECK OPERATION END #########################
####################### CHECK OPERATION SUMMARY #######################
Oracle Grid Infrastructure Home is:
The cluster node(s) on which the Oracle home exists are: (Please input nodes seperated
by ",", eg: node1,node2,...)<b>rac3</b>
Since -local option has been specified, the Oracle home will be de-installed only on the local node,
'rac3', and the global configuration will be removed.
Oracle Home selected for de-install is: /u01/app/11.2.0/grid
Inventory Location where the Oracle home registered is: /u01/app/oraInventory
Following RAC listener(s) will be de-configured: LISTENER
Option -local will not modify any ASM configuration.
Do you want to continue (y - yes, n - no)? [n]: y
A log of this session will be written to:
'/tmp/deinstall2012-12-02_05-51-29-PM/logs/deinstall_deconfig2012-12-02_05-53-05-PM.out'
Any error messages from this session will be written to:
'/tmp/deinstall2012-12-02_05-51-29-PM/logs/deinstall_deconfig2012-12-02_05-53-05-PM.err'
######################## CLEAN OPERATION START ########################
ASM de-configuration trace file location:
/tmp/deinstall2012-12-02_05-51-29-PM/logs/asmcadc_clean5733666251865826553.log
ASM Clean Configuration END
Network Configuration clean config START
Network de-configuration trace file location:
/tmp/deinstall2012-12-02_05-51-29-PM/logs/netdc_clean2216372759318186245.log
De-configuring RAC listener(s): LISTENER
De-configuring listener: LISTENER
Stopping listener on node "rac3": LISTENER
Warning: Failed to stop listener. Listener may not be running.
Listener de-configured successfully.
De-configuring Naming Methods configuration file...
Naming Methods configuration file de-configured successfully.
De-configuring backup files...
Backup files de-configured successfully.
The network configuration has been cleaned up successfully.
Network Configuration clean config END
---------------------------------------->
Remove the directory: /tmp/deinstall2012-12-02_05-51-29-PM on node:
Oracle Universal Installer clean START
Detach Oracle home '/u01/app/11.2.0/grid' from the central inventory on the local node : Done
Delete directory '/u01/app/11.2.0/grid' on the local node : Done
Delete directory '/u01/app/oraInventory' on the local node : Done
Delete directory '/u01/app/grid' on the local node : Done
Oracle Universal Installer cleanup was successful.
Oracle Universal Installer clean END
Oracle install clean START
Clean install operation removing temporary directory '/tmp/install' on node 'rac3'
Oracle install clean END
######################### CLEAN OPERATION END #########################
####################### CLEAN OPERATION SUMMARY #######################
Following RAC listener(s) were de-configured successfully: LISTENER
Oracle Clusterware was already stopped and de-configured on node "rac3"
Oracle Clusterware is stopped and de-configured successfully.
Successfully detached Oracle home '/u01/app/11.2.0/grid' from the central inventory on the local node.
Successfully deleted directory '/u01/app/11.2.0/grid' on the local node.
Successfully deleted directory '/u01/app/oraInventory' on the local node.
Successfully deleted directory '/u01/app/grid' on the local node.
Oracle Universal Installer cleanup was successful.
Run 'rm -rf /etc/oraInst.loc' as root on node(s) 'rac3' at the end of the session.
Oracle install successfully cleaned up the temporary directories.
#######################################################################
############# ORACLE DEINSTALL & DECONFIG TOOL END #############</code>
<b>
Run the specified commands as root from rac3.</b>
[root@rac3 ~]# rm -rf /etc/oraInst.loc
[root@rac3 ~]# rm -rf /opt/ORCLfmap
[root@rac3 ~]# rm -rf /u01/app/11.2.0/
[root@rac3 ~]# rm -rf /u01/app/oracle/
<b>
After the de-install make sure that oracle clusterware does not start
by checking the following.</b>
[root@rac3 ~]# diff /etc/inittab /etc/inittab.no_crs
<b>Update Oracle Inventory on all nodes by running the following command from rac1.</b>
[root@rac1 ~]# su - grid
[grid@rac1 ~]$ cd $GRID_HOME/oui/bin
[grid@rac1 bin]$ ./runInstaller -updateNodeList ORACLE_HOME=$GRID_HOME "CLUSTER_NODES={rac1,rac2}" CRS=TRUE
<code>Starting Oracle Universal Installer...
Checking swap space: must be greater than 500 MB. Actual 3908 MB Passed
The inventory pointer is located at /etc/oraInst.loc
The inventory is located at /u01/app/oraInventory
'UpdateNodeList' was successful.</code>
<b>
Do post node removal checks from rac1.</b>
[grid@rac1 bin]$ cluvfy stage -post nodedel -n rac3 -verbose
<code>Performing post-checks for node removal
Checking CRS integrity...
The Oracle clusterware is healthy on node "rac2"
The Oracle clusterware is healthy on node "rac1"
CRS integrity check passed
Result:
Node removal check passed
Post-check for node removal was successful. </code>
<b>Remove ORACLE ASM from rac3.</b>
[root@rac3 ~]# /usr/sbin/oracleasm exit
<code>Unmounting ASMlib driver filesystem: /dev/oracleasm
Unloading module "oracleasm": oracleasm</code>
[root@rac3 ~]# rpm -qa | grep asm
<code>oracleasm-support-2.1.7-1.el5
ibmasm-3.0-9
nasm-0.98.39-3.2.2
ibmasm-xinput-2.1-1.el5
oracleasmlib-2.0.4-1.el5
oracleasm-2.6.18-164.el5-2.0.5-1.el5</code>
[root@rac3 ~]# rpm -ev oracleasmlib-2.0.4-1.el5 oracleasm-2.6.18-164.el5-2.0.5-1.el5 oracleasm-support-2.1.7-1.el5
<code>warning: /etc/sysconfig/oracleasm saved as /etc/sysconfig/oracleasm.rpmsave</code>
[root@rac3 ~]# rm -f /etc/sysconfig/oracleasm.rpmsave
[root@rac3 ~]# rm -f /etc/sysconfig/oracleasm-_dev_oracleasm
[root@rac3 ~]# rm -f /etc/rc.d/rc2.d/S29oracleasm
[root@rac3 ~]# rm -f /etc/rc.d/rc0.d/K20oracleasm
[root@rac3 ~]# rm -f /etc/rc.d/rc5.d/S29oracleasm
[root@rac3 ~]# rm -f /etc/rc.d/rc4.d/S29oracleasm
[root@rac3 ~]# rm -f /etc/rc.d/rc1.d/K20oracleasm
[root@rac3 ~]# rm -f /etc/rc.d/rc3.d/S29oracleasm
[root@rac3 ~]# rm -f /etc/rc.d/rc6.d/K20oracleasm
<b>Remove Oracle and Grid Users.</b>
[root@rac3 ~]# userdel -r grid
[root@rac3 ~]# userdel -r oracle
[root@rac3 ~]# groupdel oinstall
[root@rac3 ~]# groupdel asmadmin
[root@rac3 ~]# groupdel asmdba
[root@rac3 ~]# groupdel asmoper
[root@rac3 ~]# groupdel dba
[root@rac3 ~]# groupdel oper
</pre>
</div>
Ajay Singh Chandihttp://www.blogger.com/profile/05807334441165607306noreply@blogger.com0tag:blogger.com,1999:blog-9081789365774441839.post-19390512573476736432012-12-10T16:54:00.000+05:302013-01-17T23:19:32.699+05:30RAC common wait events.<div dir="ltr" style="text-align: left;" trbidi="on">
<pre><b>1) Global cache blocks lost-</b> This statistic shows block losses during network
transfers. High values of this statistic indicate network problems.
The use of an unreliable IPC protocol, such as UDP, may result in the value
for lost global cache blocks being non-zero.
High values for this statistics indicates that there is some problem with the IPC,
network or hardware.
<b>2) Global cache blocks corrupt - </b>This statistic shows if there is any block corruption
during interconnect transfers. High value for this statistic indicates
that there is a IPC,network or hardware problem.
<a name='more'></a>
<b>3) global cache open s and global cache open x -</b> These events are generated when
an instance initialy fetches data blocks. The duration of the wait event should be
short.
This wait event is generated when the blocks being requested are not in the
cache of any instance in the cluster, which in turn results into reads from disk.
Another reason for this wait event can be that the blocks cannot be fetched
from other instances cache and becuase of that there is a disk read.
One way to reduce this wait event is to preload the buffer cache with frequently
accessed tables.
<b>
4) global cache null to s and global cache null to x - </b>These wait events are
generated whenever there is a inter-instance block ping across the network.
Inter-instance block ping occurs when two instances exchange the
same block between each other repeatedly.
Processes waiting for global cache null to s events are waiting for
a block to be transferred from the instance that last changed it.
This event consumes a large proportion of wait time, When one instance
frequently requests cached data blocks from the other RAC instances.
Recommended method to reduce this wait event is to reduce number of rows
per block to reduce block swapping among nodes.
<b>5) Global cache cr request - </b>This event is generated when an instance requested a
consistent read data block and the block to be transferred has not arrived to
the instance who had requested it.
<b>6) gc cr block lost - </b>Possible reasons for this event are as following:
i) Network problems(discarded packets and fragments, packet reassembly or timeouts,
buffer overflows etc).
ii) Checksum errors or corrupted headers.
Possible solution is to check our entire network layer for problems ex NIC,switch etc.
<b>7) gc buffer busy -</b> GC buffer busy means that the buffer in the buffer cache, that the
session is trying to access is already involved in another ongoing global cache operation.
Until that global cache operation completes, session must wait.
Possible reasons for this event may be due to CPU starvation issues, Swapping issues, interconnect
issues etc.
<b>8) congested - </b>The events that contain "congested" suggest CPU saturation
(runaway or spinning processes), long running queues, and network
configuration issues. Excessive swapping in harddisk because of low memory can
also cause this event.
<b>9) busy - </b>The events that contain "busy" indicate contention. Investigation for
this event should be done by examining SQL with high cluster wait time or segments
with high interconnect block transfers.
<b>10) Gc[current/cr][failure/retry] - </b>This event means that due to network problems,
the sending instance needs to retry sending the block image.
This event happen when there is network or hardware problems.
<b>11) Gc [current/cr] [2/3]-way - </b>If we have a two-node cluster, then only a 2-way
cluster is possible, as we can have at most two hops. If we have three or more
RAC instances, then 2-way or 3-way cluster is possible. Events are received
immediately after two or three network hops.
<b>12) Gc[current/cr] grant 2-way -</b> A grant means, that an instance has requested
a block image and no other instance in the cluster has it in its buffer cache.
So, the requesting instance performs a disk i/o and gets the data from the
datafile.
A grant can be of two types "CR" or "Current".
A current grant reads the block from the datafiles.
A cr grant reads the block from disk and builds a read consistent block,
as of a certain time in past.
<b>
13) Gc[current/cr][block/grant] congested -</b> A current or cr block is requested and a block or
grant message is received. The congested hint implies that the request spent more than 1 ms in internal
queues. Reasons for this event may include intensive CPU consumption, lack of memory,
excessive paging and swapping.
<b>
14) Gc[current/cr] block busy -</b> A current or cr block is requested and received,
but is not sent immediately by LMS which caused the delay in sending.
<b>
15) Gc current grant busy -</b> A current block is requested and a grant message received.
The busy hint means that the request is blocked for some reason or it
cannot be handled immediately.
<b>16) Gc[current/cr][failure/retry] - </b>A block is requested and a failure status
received or some other exceptional event has occured.
<b>17) Gc buffer busy -</b> If the time between buffer accesses becomes less than the time the
buffer is pinned in memory, the buffer containing a block is said to become busy and as a result
interested users may have to wait for it to be unpinned.
</pre>
</div>
Ajay Singh Chandihttp://www.blogger.com/profile/05807334441165607306noreply@blogger.com0tag:blogger.com,1999:blog-9081789365774441839.post-82781362152384698682012-12-04T17:27:00.002+05:302013-01-17T23:19:21.663+05:30Oracle 11gR2 RAC Node Addition.<div dir="ltr" style="text-align: left;" trbidi="on">
<div dir="ltr" style="text-align: left;" trbidi="on">
<pre>Following are the IP Addresses which i am using
for the node.
<b>Public IP Address:</b> 192.168.1.170
<b>Private IP Address:</b> 192.168.2.170
<b>Virtual IP Address:</b> 192.168.1.250
<b>OS installed</b> - RHEL 5.4
<a name='more'></a>
<b>Packages to install</b>
<b>Desktop Environment</b>
Gnome
<b>Applications</b>
Editors
Graphical internet
Text-based Internet
<b>Development</b>
Develoment libraries
Development Tools
Legacy Software development
<b>Servers</b>
Server Configuration Tools
<b>Base system</b>
administration Tools
base
java
legacy software support
system tools
X window system
<b>Rpms to be installed</b>
binutils-2.17.50.0.6
compat-libstdc++33*
elfutils-libelf*
elfutils-libelf-devel*
elfutils-libelf-devel-static*
gcc-4*
gcc-c++*
glibc-2*
glibc-common*
glibc-devel*
glibc-headers*
kernel-headers-2*
ksh*
libaio*
libaio-devel*
libgcc*
libgomp*
libstdc++*
libstdc++-devel*
make*
pdksh*
sysstat*
unixODBC*
unixODBC-devel
<b>Make appropiate changes in the DNS Server.</b>
<b>DNS Forward lookup entries</b>
[root@rac1 ~]# vi /var/named/chroot/var/named/forward.zone
<code>$TTL 86400
@ IN SOA rac1.example.com. root.example.com. (
42 ; serial (d. adams)
3H ; refresh
15M ; retry
1W ; expiry
1D ) ; minimum
IN NS rac1.example.com.
rac1 IN A 192.168.1.100
; Oracle RAC Nodes
rac1 IN A 192.168.1.100
rac2 IN A 192.168.1.200
<b>rac3 IN A 192.168.1.170</b>
rac1-priv IN A 192.168.2.100
rac2-priv IN A 192.168.2.200
<b>rac3-priv IN A 192.168.2.170</b>
rac1-vip IN A 192.168.1.251
rac2-vip IN A 192.168.1.252
<b>rac3-vip IN A 192.168.1.250</b>
; Single Client Access Name (SCAN) virtual IP
rac-cluster-scan IN A 192.168.1.150
rac-cluster-scan IN A 192.168.1.151
rac-cluster-scan IN A 192.168.1.152</code>
<b>DNS Reverse lookup entries</b>
[root@rac1 ~]# vi /var/named/chroot/var/named/reverse.zone
<code>$TTL 86400
@ IN SOA rac1.example.com. root.rac1.example.com. (
1997022700 ; Serial
28800 ; Refresh
14400 ; Retry
3600000 ; Expire
86400 ) ; Minimum
IN NS rac1.example.com.
100 IN PTR rac1.example.com.
; Oracle RAC Nodes
100 IN PTR rac1.example.com.
200 IN PTR rac2.example.com.
<b>170 IN PTR rac3.example.com.</b>
251 IN PTR rac1-vip.example.com.
252 IN PTR rac2-vip.example.com.
<b>250 IN PTR rac3-vip.example.com.</b>
; Single Client Access Name (SCAN) virtual IP
150 IN PTR rac-cluster-scan.example.com.
151 IN PTR rac-cluster-scan.example.com.
152 IN PTR rac-cluster-scan.example.com.</code>
<b>After making forward and reverse lookup entries
restart the DNS server.
</b>
[root@rac1 ~]# service named restart
<code>Stopping named: . [ OK ]
Starting named: [ OK ]</code>
<b>Insert into rac3 for dns lookup.</b>
[root@rac3 ~]# vi /etc/resolv.conf
search example.com
nameserver 192.168.1.100
<b>New /etc/hosts file on all nodes</b>
<code># Public Network - (eth0)
192.168.1.100 rac1.example.com rac1
192.168.1.200 rac2.example.com rac2
<b>192.168.1.170 rac3.example.com rac3</b>
# Private Interconnect - (eth1)
192.168.2.100 rac1-priv.example.com rac1-priv
192.168.2.200 rac2-priv.example.com rac2-priv
<b>192.168.2.170 rac3-priv.example.com rac3-priv</b>
# Public Virtual IP (VIP) addresses - (eth0:1)
192.168.1.251 rac1-vip.example.com rac1-vip
192.168.1.252 rac2-vip.example.com rac2-vip
<b>192.168.1.250 rac3-vip.example.com rac3-vip</b></code>
<b>Deconfigure NTP services on RAC3.</b>
<code>/sbin/service ntpd stop
chkconfig ntpd off
mv /etc/ntp.conf /etc/ntp.conf.original
rm /var/run/ntpd.pid</code>
<b>Create necessary groups and users for Grid on RAC3.</b>
[root@rac3 ~]#groupadd -g 1000 oinstall
[root@rac3 ~]#groupadd -g 1200 asmadmin
[root@rac3 ~]#groupadd -g 1201 asmdba
[root@rac3 ~]#groupadd -g 1202 asmoper
[root@rac3 ~]#useradd -m -u 1100 -g oinstall -G asmadmin,asmdba,
asmoper -d /home/grid -s /bin/bash -c "Grid Infrastructure Owner" grid
[root@rac3 ~]# id grid
uid=1100(grid) gid=1000(oinstall) groups=1000(oinstall),1200(asmadmin),1201(asmdba),1202(asmoper)
Assign password to grid user on RAC3.
[root@rac3 ~]#passwd grid
<b>Set Bash Profile for grid user on RAC3.</b>
[root@rac3 ~]#su - grid
Replace the content in .bash_profile with the following of grid user on both nodes.
<code># ---------------------------------------------------
# .bash_profile
# ---------------------------------------------------
# OS User: grid
# Application: Oracle Grid Infrastructure
# Version: Oracle 11g Release 2
# ---------------------------------------------------
# Get the aliases and functions
if [ -f ~/.bashrc ]; then
. ~/.bashrc
fi
alias ls="ls -FA"
# ---------------------------------------------------
# ORACLE_SID
# ---------------------------------------------------
# Specifies the Oracle system identifier (SID)
# for the Automatic Storage Management (ASM)instance
# running on this node.
# Each RAC node must have a unique ORACLE_SID.
# (i.e. +ASM1, +ASM2,...)
# ---------------------------------------------------
ORACLE_SID=+ASM3; export ORACLE_SID
# ---------------------------------------------------
# JAVA_HOME
# ---------------------------------------------------
# Specifies the directory of the Java SDK and Runtime
# Environment.
# ---------------------------------------------------
JAVA_HOME=/usr/local/java; export JAVA_HOME
# ---------------------------------------------------
# GRID_BASE
# ---------------------------------------------------
# Specifies the base of the Oracle directory structure
# for Optimal Flexible Architecture (OFA) compliant
# installations. The Oracle base directory for the
# grid installation owner is the location where
# diagnostic and administrative logs, and other logs
# associated with Oracle ASM and Oracle Clusterware
# are stored.
# ---------------------------------------------------
GRID_BASE=/u01/app/grid; export GRID_BASE
ORACLE_BASE=$GRID_BASE; export ORACLE_BASE
# ---------------------------------------------------
# GRID_HOME
# ---------------------------------------------------
# Specifies the directory containing the Oracle
# Grid Infrastructure software. For grid
# infrastructure for a cluster installations, the Grid
# home must not be placed under one of the Oracle base
# directories, or under Oracle home directories of
# Oracle Database installation owners, or in the home
# directory of an installation owner. During
# installation, ownership of the path to the Grid
# home is changed to root. This change causes
# permission errors for other installations.
# ---------------------------------------------------
GRID_HOME=/u01/app/11.2.0/grid; export GRID_HOME
ORACLE_HOME=$GRID_HOME; export ORACLE_HOME
# ---------------------------------------------------
# ORACLE_PATH
# ---------------------------------------------------
# Specifies the search path for files used by Oracle
# applications such as SQL*Plus. If the full path to
# the file is not specified, or if the file is not
# in the current directory, the Oracle application
# uses ORACLE_PATH to locate the file.
# This variable is used by SQL*Plus, Forms and Menu.
# ---------------------------------------------------
ORACLE_PATH=/u01/app/oracle/dba_scripts/sql; export ORACLE_PATH
# ---------------------------------------------------
# SQLPATH
# ---------------------------------------------------
# Specifies the directory or list of directories that
# SQL*Plus searches for a login.sql file.
# ---------------------------------------------------
# SQLPATH=/u01/app/oracle/dba_scripts/sql; export SQLPATH
# ---------------------------------------------------
# ORACLE_TERM
# ---------------------------------------------------
# Defines a terminal definition. If not set, it
# defaults to the value of your TERM environment
# variable. Used by all character mode products.
# ---------------------------------------------------
ORACLE_TERM=xterm; export ORACLE_TERM
# ---------------------------------------------------
# NLS_DATE_FORMAT
# ---------------------------------------------------
# Specifies the default date format to use with the
# TO_CHAR and TO_DATE functions. The default value of
# this parameter is determined by NLS_TERRITORY. The
# value of this parameter can be any valid date
# format mask, and the value must be surrounded by
# double quotation marks. For example:
#
# NLS_DATE_FORMAT = "MM/DD/YYYY"
#
# ---------------------------------------------------
NLS_DATE_FORMAT="DD-MON-YYYY HH24:MI:SS"; export NLS_DATE_FORMAT
# ---------------------------------------------------
# TNS_ADMIN
# ---------------------------------------------------
# Specifies the directory containing the Oracle Net
# Services configuration files like listener.ora,
# tnsnames.ora, and sqlnet.ora.
# ---------------------------------------------------
TNS_ADMIN=$GRID_HOME/network/admin; export TNS_ADMIN
# ---------------------------------------------------
# ORA_NLS11
# ---------------------------------------------------
# Specifies the directory where the language,
# territory, character set, and linguistic definition
# files are stored.
# ---------------------------------------------------
ORA_NLS11=$GRID_HOME/nls/data; export ORA_NLS11
# ---------------------------------------------------
# PATH
# ---------------------------------------------------
# Used by the shell to locate executable programs;
# must include the $GRID_HOME/bin directory.
# ---------------------------------------------------
PATH=.:${JAVA_HOME}/bin:$JAVA_HOME/db/bin:${PATH}:$HOME/bin:$ORACLE_HOME/bin:$ORACLE_HOME/OPatch
PATH=${PATH}:/usr/bin:/bin:/usr/bin/X11:/usr/local/bin
PATH=${PATH}:/u01/app/oracle/dba_scripts/bin
export PATH
# ---------------------------------------------------
# LD_LIBRARY_PATH
# ---------------------------------------------------
# Specifies the list of directories that the shared
# library loader searches to locate shared object
# libraries at runtime.
# ---------------------------------------------------
LD_LIBRARY_PATH=$GRID_HOME/lib
LD_LIBRARY_PATH=${LD_LIBRARY_PATH}:$GRID_HOME/oracm/lib
LD_LIBRARY_PATH=${LD_LIBRARY_PATH}:/lib:/usr/lib:/usr/local/lib
export LD_LIBRARY_PATH
# ---------------------------------------------------
# CLASSPATH
# ---------------------------------------------------
# Specifies the directory or list of directories that
# contain compiled Java classes.
# ---------------------------------------------------
CLASSPATH=$GRID_HOME/JRE
CLASSPATH=${CLASSPATH}:$GRID_HOME/jdbc/lib/ojdbc6.jar
CLASSPATH=${CLASSPATH}:$GRID_HOME/jlib
CLASSPATH=${CLASSPATH}:$GRID_HOME/rdbms/jlib
CLASSPATH=${CLASSPATH}:$ORACLE_HOME/oc4j/ant/lib/ant.jar
CLASSPATH=${CLASSPATH}:$ORACLE_HOME/oc4j/ant/lib/ant-launcher.jar
CLASSPATH=${CLASSPATH}:$JAVA_HOME/db/lib/derby.jar
CLASSPATH=${CLASSPATH}:$GRID_HOME/network/jlib
export CLASSPATH
# ---------------------------------------------------
# THREADS_FLAG
# ---------------------------------------------------
# All the tools in the JDK use green threads as a
# default. To specify that native threads should be
# used, set the THREADS_FLAG environment variable to
# "native". You can revert to the use of green
# threads by setting THREADS_FLAG to the value
# "green".
# ---------------------------------------------------
THREADS_FLAG=native; export THREADS_FLAG
# ---------------------------------------------------
# TEMP, TMP, and TMPDIR
# ---------------------------------------------------
# Specify the default directories for temporary
# files; if set, tools that create temporary files
# create them in one of these directories.
# ---------------------------------------------------
export TEMP=/tmp
export TMPDIR=/tmp
# ---------------------------------------------------
# UMASK
# ---------------------------------------------------
# Set the default file mode creation mask
# (umask) to 022 to ensure that the user performing
# the Oracle software installation creates files
# with 644 permissions.
# ---------------------------------------------------
umask 022</code>
<b>Make sure of setting for RAC3 ORACLE_SID=+ASM3; export ORACLE_SID</b>
<b>Activate the bash_profile on RAC3 using.</b>
[grid@rac3 ~]$ . ./.bash_profile
<b>Create necessary users and groups for Oracle software</b>
[root@rac3 ~]#groupadd -g 1300 dba
[root@rac3 ~]#groupadd -g 1301 oper
[root@rac3 ~]#useradd -m -u 1101 -g oinstall -G dba,oper,asmdba -d
/home/oracle -s /bin/bash -c "Oracle Software Owner" oracle
[root@rac3 ~]# id oracle
uid=1101(oracle) gid=1000(oinstall) groups=1000(oinstall),1201(asmdba),1300(dba),1301(oper)
<b>Create password for oracle user</b>
[root@rac3 ~]#passwd oracle
Set Bash Profile for oracle user on RAC3.
[root@rac3 ~]#su - oracle
Replace the content in .bash_profile with the following
<code># ---------------------------------------------------
# .bash_profile
# ---------------------------------------------------
# OS User: oracle
# Application: Oracle Database Software Owner
# Version: Oracle 11g Release 2
# ---------------------------------------------------
# Get the aliases and functions
if [ -f ~/.bashrc ]; then
. ~/.bashrc
fi
alias ls="ls -FA"
# ---------------------------------------------------
# ORACLE_SID
# ---------------------------------------------------
# Specifies the Oracle system identifier (SID) for
# the Oracle instance running on this node.
# Each RAC node must have a unique ORACLE_SID.
# (i.e. orcl1, orcl2,...)
# ---------------------------------------------------
ORACLE_SID=orcl3; export ORACLE_SID
# ---------------------------------------------------
# ORACLE_UNQNAME
# ---------------------------------------------------
# In previous releases of Oracle Database, you were
# required to set environment variables for
# ORACLE_HOME and ORACLE_SID to start, stop, and
# check the status of Enterprise Manager. With
# Oracle Database 11g Release 2 (11.2) and later, you
# need to set the environment variables ORACLE_HOME
# and ORACLE_UNQNAME to use Enterprise Manager.
# Set ORACLE_UNQNAME equal to the database unique
# name.
# ---------------------------------------------------
ORACLE_UNQNAME=orcl; export ORACLE_UNQNAME
# ---------------------------------------------------
# JAVA_HOME
# ---------------------------------------------------
# Specifies the directory of the Java SDK and Runtime
# Environment.
# ---------------------------------------------------
JAVA_HOME=/usr/local/java; export JAVA_HOME
# ---------------------------------------------------
# ORACLE_BASE
# ---------------------------------------------------
# Specifies the base of the Oracle directory structure
# for Optimal Flexible Architecture (OFA) compliant
# database software installations.
# ---------------------------------------------------
ORACLE_BASE=/u01/app/oracle; export ORACLE_BASE
# ---------------------------------------------------
# ORACLE_HOME
# ---------------------------------------------------
# Specifies the directory containing the Oracle
# Database software.
# ---------------------------------------------------
ORACLE_HOME=$ORACLE_BASE/product/11.2.0/dbhome_1; export ORACLE_HOME
# ---------------------------------------------------
# ORACLE_PATH
# ---------------------------------------------------
# Specifies the search path for files used by Oracle
# applications such as SQL*Plus. If the full path to
# the file is not specified, or if the file is not
# in the current directory, the Oracle application
# uses ORACLE_PATH to locate the file.
# This variable is used by SQL*Plus, Forms and Menu.
# ---------------------------------------------------
ORACLE_PATH=/u01/app/oracle/dba_scripts/sql:$ORACLE_HOME/rdbms/admin; export ORACLE_PATH
# ---------------------------------------------------
# SQLPATH
# ---------------------------------------------------
# Specifies the directory or list of directories that
# SQL*Plus searches for a login.sql file.
# ---------------------------------------------------
# SQLPATH=/u01/app/oracle/dba_scripts/sql; export SQLPATH
# ---------------------------------------------------
# ORACLE_TERM
# ---------------------------------------------------
# Defines a terminal definition. If not set, it
# defaults to the value of your TERM environment
# variable. Used by all character mode products.
# ---------------------------------------------------
ORACLE_TERM=xterm; export ORACLE_TERM
# ---------------------------------------------------
# NLS_DATE_FORMAT
# ---------------------------------------------------
# Specifies the default date format to use with the
# TO_CHAR and TO_DATE functions. The default value of
# this parameter is determined by NLS_TERRITORY. The
# value of this parameter can be any valid date
# format mask, and the value must be surrounded by
# double quotation marks. For example:
#
# NLS_DATE_FORMAT = "MM/DD/YYYY"
#
# ---------------------------------------------------
NLS_DATE_FORMAT="DD-MON-YYYY HH24:MI:SS"; export NLS_DATE_FORMAT
# ---------------------------------------------------
# TNS_ADMIN
# ---------------------------------------------------
# Specifies the directory containing the Oracle Net
# Services configuration files like listener.ora,
# tnsnames.ora, and sqlnet.ora.
# ---------------------------------------------------
TNS_ADMIN=$ORACLE_HOME/network/admin; export TNS_ADMIN
# ---------------------------------------------------
# ORA_NLS11
# ---------------------------------------------------
# Specifies the directory where the language,
# territory, character set, and linguistic definition
# files are stored.
# ---------------------------------------------------
ORA_NLS11=$ORACLE_HOME/nls/data; export ORA_NLS11
# ---------------------------------------------------
# PATH
# ---------------------------------------------------
# Used by the shell to locate executable programs;
# must include the $ORACLE_HOME/bin directory.
# ---------------------------------------------------
PATH=.:${JAVA_HOME}/bin:$JAVA_HOME/db/bin:${PATH}:$HOME/bin:$ORACLE_HOME/bin:$ORACLE_HOME/OPatch
PATH=${PATH}:/usr/bin:/bin:/usr/bin/X11:/usr/local/bin
PATH=${PATH}:/u01/app/oracle/dba_scripts/bin
export PATH
# ---------------------------------------------------
# LD_LIBRARY_PATH
# ---------------------------------------------------
# Specifies the list of directories that the shared
# library loader searches to locate shared object
# libraries at runtime.
# ---------------------------------------------------
LD_LIBRARY_PATH=$ORACLE_HOME/lib
LD_LIBRARY_PATH=${LD_LIBRARY_PATH}:$ORACLE_HOME/oracm/lib
LD_LIBRARY_PATH=${LD_LIBRARY_PATH}:/lib:/usr/lib:/usr/local/lib
export LD_LIBRARY_PATH
# ---------------------------------------------------
# CLASSPATH
# ---------------------------------------------------
# Specifies the directory or list of directories that
# contain compiled Java classes.
# ---------------------------------------------------
CLASSPATH=$ORACLE_HOME/jdbc/lib/ojdbc6.jar
CLASSPATH=${CLASSPATH}:$ORACLE_HOME/jlib
CLASSPATH=${CLASSPATH}:$ORACLE_HOME/rdbms/jlib
CLASSPATH=${CLASSPATH}:$ORACLE_HOME/oc4j/ant/lib/ant.jar
CLASSPATH=${CLASSPATH}:$ORACLE_HOME/oc4j/ant/lib/ant-launcher.jar
CLASSPATH=${CLASSPATH}:$JAVA_HOME/db/lib/derby.jar
CLASSPATH=${CLASSPATH}:$ORACLE_HOME/network/jlib
export CLASSPATH
# ---------------------------------------------------
# THREADS_FLAG
# ---------------------------------------------------
# All the tools in the JDK use green threads as a
# default. To specify that native threads should be
# used, set the THREADS_FLAG environment variable to
# "native". You can revert to the use of green
# threads by setting THREADS_FLAG to the value
# "green".
# ---------------------------------------------------
THREADS_FLAG=native; export THREADS_FLAG
# ---------------------------------------------------
# TEMP, TMP, and TMPDIR
# ---------------------------------------------------
# Specify the default directories for temporary
# files; if set, tools that create temporary files
# create them in one of these directories.
# ---------------------------------------------------
export TEMP=/tmp
export TMPDIR=/tmp
# ---------------------------------------------------
# UMASK
# ---------------------------------------------------
# Set the default file mode creation mask
# (umask) to 022 to ensure that the user performing
# the Oracle software installation creates files
# with 644 permissions.
# ---------------------------------------------------
umask 022</code>
<b>Make sure setting for rac3 should be ORACLE_SID=orcl3; export ORACLE_SID</b>
<b>Activate bash profile on RAC3</b>
[oracle@rac3 ~]$ . ./.bash_profile
<b>verify that user nobody exists on RAC3.</b>
[root@rac3 ~]# id nobody
uid=99(nobody) gid=99(nobody) groups=99(nobody)
<b>Create directory structures on RAC3.</b>
[root@rac3 ~]# mkdir -p /u01/app/grid
[root@rac3 ~]# mkdir -p /u01/app/11.2.0/grid
[root@rac3 ~]# chown -R grid:oinstall /u01
[root@rac3 ~]# mkdir -p /u01/app/oracle
[root@rac3 ~]# chown oracle:oinstall /u01/app/oracle
[root@rac3 ~]# chmod -R 775 /u01
<b>Insert the following in /etc/security/limits.conf on RAC3.</b>
grid soft nproc 2047
grid hard nproc 16384
grid soft nofile 1024
grid hard nofile 65536
oracle soft nproc 2047
oracle hard nproc 16384
oracle soft nofile 1024
oracle hard nofile 65536
<b>Insert the following in /etc/pam.d/login on RAC3.</b>
session required pam_limits.so
<b>Append the following in root user /etc/profile on RAC3.</b>
<code>if [ \$USER = "oracle" ] || [ \$USER = "grid" ]; then
if [ \$SHELL = "/bin/ksh" ]; then
ulimit -p 16384
ulimit -n 65536
else
ulimit -u 16384 -n 65536
fi
umask 022
fi</code>
<b>Edit kernel parameters on RAC3 /etc/sysctl.conf.</b>
leave the fields for
kernel.shmall
kernel.shmmax
as it is and append the following values in /etc/sysctl.conf.
<code># Controls the maximum number of shared memory segments system wide
kernel.shmmni = 4096
# Sets the following semaphore values:
# SEMMSL_value SEMMNS_value SEMOPM_value SEMMNI_value
kernel.sem = 250 32000 100 128
# Sets the maximum number of file-handles that the Linux kernel will allocate
fs.file-max = 6815744
# Defines the local port range that is used by TCP and UDP
# traffic to choose the local port
net.ipv4.ip_local_port_range = 9000 65500
# Default setting in bytes of the socket "receive" buffer which
# may be set by using the SO_RCVBUF socket option
net.core.rmem_default=262144
# Maximum setting in bytes of the socket "receive" buffer which
# may be set by using the SO_RCVBUF socket option
net.core.rmem_max=4194304
# Default setting in bytes of the socket "send" buffer which
# may be set by using the SO_SNDBUF socket option
net.core.wmem_default=262144
# Maximum setting in bytes of the socket "send" buffer which
# may be set by using the SO_SNDBUF socket option
net.core.wmem_max=1048576
# Maximum number of allowable concurrent asynchronous I/O requests
fs.aio-max-nr=1048576</code>
<b>Activate kernel settings on RAC3.</b>
[root@rac3 ~]# sysctl -p
<b>Install asm on RAC3.</b>
[root@rac3 asm11g]# rpm -Uvh oracleasm-2.6.18-164.el5-2.0.5-1.el5.i686.rpm \
> oracleasmlib-2.0.4-1.el5.i386.rpm \
> oracleasm-support-2.1.7-1.el5.i386.rpm
Output
<code>warning: oracleasm-2.6.18-164.el5-2.0.5-1.el5.i686.rpm: Header V3 DSA signature: NOKEY, key ID 1e5e0159
Preparing... ########################################### [100%]
1:oracleasm-support ########################################### [ 33%]
2:oracleasm-2.6.18-164.el########################################### [ 67%]
3:oracleasmlib ########################################### [100%]</code>
<b>Check on RAC3</b>
[root@rac3 ~]# rpm -qa | grep asm
oracleasm-2.6.18-164.el5-2.0.5-1.el5
ibmasm-3.0-9
oracleasm-support-2.1.7-1.el5
nasm-0.98.39-3.2.2
ibmasm-xinput-2.1-1.el5
oracleasmlib-2.0.4-1.el5
<b>Configure asm on RAC3.</b>
[root@rac3 ~]# /usr/sbin/oracleasm configure -i
<code>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 without typing an
answer will keep that current value. Ctrl-C will abort.
Default user to own the driver interface []: grid
Default group to own the driver interface []: asmadmin
Start Oracle ASM library driver on boot (y/n) [n]: y
Scan for Oracle ASM disks on boot (y/n) [y]: y
Writing Oracle ASM library driver configuration: done</code>
<b>Execute the following on RAC3.</b>
[root@rac3 ~]# /usr/sbin/oracleasm init
<code>Creating /dev/oracleasm mount point: /dev/oracleasm
Loading module "oracleasm": oracleasm
Mounting ASMlib driver filesystem: /dev/oracleasm</code>
<b>Execute the following from RAC3.</b>
[root@rac3 ~]# /usr/sbin/oracleasm scandisks
<code>Reloading disk partitions: done
Cleaning any stale ASM disks...
Scanning system for ASM disks...
Instantiating disk "VOL1"
Instantiating disk "VOL2"
Instantiating disk "VOL3"
Instantiating disk "VOL4"
Instantiating disk "VOL5"
Instantiating disk "VOL6"
Instantiating disk "VOL7"</code>
<b>Execute the following from RAC3.</b>
[root@rac3 ~]# /usr/sbin/oracleasm listdisks
<code>VOL1
VOL2
VOL3
VOL4
VOL5
VOL6
VOL7</code>
<b>Enable user equivalence for RAC3 as Grid user from RAC1.</b>
[root@rac1 ~]# su - grid
[grid@rac1 ~]$ /u01/app/11.2.0/grid/oui/bin/runSSHSetup.sh -user grid -hosts "rac2 rac3" -advanced -exverify
<code>This script will setup SSH Equivalence from the host 'rac1.example.com' to specified remote hosts.
ORACLE_HOME = /u01/app/11.2.0/grid
JAR_LOC = /u01/app/11.2.0/grid/oui/jlib
SSH_LOC = /u01/app/11.2.0/grid/oui/jlib
OUI_LOC = /u01/app/11.2.0/grid/oui
PROP_LOC = /u01/app/11.2.0/grid/sysman/prov/resources
JAVA_HOME = /u01/app/11.2.0/grid/jdk
Checking if the remote hosts are reachable.
formulateLocalCmd rac2/bin/ping-c 1 -w 5
formulateLocalCmd rac3/bin/ping-c 1 -w 5
/bin/ping#rac2#-c#1#-w#5
/bin/ping#rac3#-c#1#-w#5
Remote host reachability check succeeded.
All hosts are reachable. Proceeding further...
NOTE :
As part of the setup procedure, this script will use ssh and scp to copy
files between the local host and the remote hosts. You may be prompted for
the password during the execution of the script.
AS PER SSH REQUIREMENTS, THIS SCRIPT WILL SECURE THE USER HOME DIRECTORY
AND THE .ssh DIRECTORY BY REVOKING GROUP AND WORLD WRITE PRIVILEDGES TO THESE
directories.
Do you want to continue and let the script make the above mentioned changes (yes/no)?
<b>yes</b>
If The files containing the client public and private keys already exist on the local host.
The current private key may or may not have a passphrase associated with it.
In case you remember the passphrase and do not want to re-run ssh-keygen, type 'no'. If you type 'yes',
the script will remove the old private/public key files and, any previous SSH user setups would be reset.
Enter 'yes', 'no'
<b>no</b>
Enter the password:<b>****</b>
Logfile Location : /tmp/SSHSetup2012-12-02_02-09-40-PM
Doing SSHSetup...
Please be patient, this operation might take sometime...Dont press Ctrl+C...
Local Platform:- Linux
------------------------------------------------------------------------
Verifying SSH setup
===================
The script will now run the date command on the remote nodes using ssh
to verify if ssh is setup correctly. IF THE SETUP IS CORRECTLY SETUP,
THERE SHOULD BE NO OUTPUT OTHER THAN THE DATE AND SSH SHOULD NOT ASK FOR
PASSWORDS. If you see any output other than date or are prompted for the
password, ssh is not setup correctly and you will need to resolve the
issue and set up ssh again.
The possible causes for failure could be:
1. The server settings in /etc/ssh/sshd_config file do not allow ssh
for user grid.
2. The server may have disabled public key based authentication.
3. The client public key on the server may be outdated.
4. ~grid or ~grid/.ssh on the remote host may not be owned by grid.
5. User may not have passed -shared option for shared remote users or
may be passing the -shared option for non-shared remote users.
6. If there is output in addition to the date, but no password is asked,
it may be a security alert shown as part of company policy. Append the
additional text to the <oms home="home">/sysman/prov/resources/ignoreMessages.txt file.
------------------------------------------------------------------------
--rac2:--
Running /usr/bin/ssh -x -l grid rac2 date to verify SSH connectivity has been setup from local host to rac2.
IF YOU SEE ANY OTHER OUTPUT BESIDES THE OUTPUT OF THE DATE COMMAND OR IF YOU ARE PROMPTED FOR A PASSWORD HERE,
IT MEANS SSH SETUP HAS NOT BEEN SUCCESSFUL. Please note that being prompted for a passphrase may be OK but being
prompted for a password is ERROR.
Sun Dec 2 14:11:05 IST 2012
------------------------------------------------------------------------
--rac3:--
Running /usr/bin/ssh -x -l grid rac3 date to verify SSH connectivity has been setup from local host to rac3.
IF YOU SEE ANY OTHER OUTPUT BESIDES THE OUTPUT OF THE DATE COMMAND OR IF YOU ARE PROMPTED FOR A PASSWORD HERE,
IT MEANS SSH SETUP HAS NOT BEEN SUCCESSFUL. Please note that being prompted for a passphrase may be OK but being
prompted for a password is ERROR.
Sun Dec 2 14:11:06 IST 2012
------------------------------------------------------------------------
------------------------------------------------------------------------
Verifying SSH connectivity has been setup from rac2 to rac2
------------------------------------------------------------------------
IF YOU SEE ANY OTHER OUTPUT BESIDES THE OUTPUT OF THE DATE COMMAND OR IF YOU ARE PROMPTED FOR A PASSWORD HERE,
IT MEANS SSH SETUP HAS NOT BEEN SUCCESSFUL.
Sun Dec 2 14:11:07 IST 2012
------------------------------------------------------------------------
------------------------------------------------------------------------
Verifying SSH connectivity has been setup from rac2 to rac3
------------------------------------------------------------------------
IF YOU SEE ANY OTHER OUTPUT BESIDES THE OUTPUT OF THE DATE COMMAND OR IF YOU ARE PROMPTED FOR A PASSWORD HERE,
IT MEANS SSH SETUP HAS NOT BEEN SUCCESSFUL.
Sun Dec 2 14:11:08 IST 2012
------------------------------------------------------------------------
-Verification from rac2 complete-
------------------------------------------------------------------------
Verifying SSH connectivity has been setup from rac3 to rac2
------------------------------------------------------------------------
IF YOU SEE ANY OTHER OUTPUT BESIDES THE OUTPUT OF THE DATE COMMAND OR IF YOU ARE PROMPTED FOR A PASSWORD HERE,
IT MEANS SSH SETUP HAS NOT BEEN SUCCESSFUL.
Sun Dec 2 14:11:08 IST 2012
------------------------------------------------------------------------
------------------------------------------------------------------------
Verifying SSH connectivity has been setup from rac3 to rac3
------------------------------------------------------------------------
IF YOU SEE ANY OTHER OUTPUT BESIDES THE OUTPUT OF THE DATE COMMAND OR IF YOU ARE PROMPTED FOR A PASSWORD HERE,
IT MEANS SSH SETUP HAS NOT BEEN SUCCESSFUL.
Sun Dec 2 14:11:10 IST 2012
------------------------------------------------------------------------
-Verification from rac3 complete-
SSH verification complete.</oms></code>
<b>Enable user equivalence for RAC3 as Oracle user from RAC1.</b>
[root@rac1 ~]# su - oracle
[oracle@rac1 ~]$ /u01/app/oracle/product/11.2.0/dbhome_1/oui/bin/runSSHSetup.sh -user oracle -hosts "rac2 rac3" -advanced -exverify
<code>This script will setup SSH Equivalence from the host 'rac1.example.com' to specified remote hosts.
ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1
JAR_LOC = /u01/app/oracle/product/11.2.0/dbhome_1/oui/jlib
SSH_LOC = /u01/app/oracle/product/11.2.0/dbhome_1/oui/jlib
OUI_LOC = /u01/app/oracle/product/11.2.0/dbhome_1/oui
PROP_LOC = /u01/app/oracle/product/11.2.0/dbhome_1/sysman/prov/resources
JAVA_HOME = /u01/app/oracle/product/11.2.0/dbhome_1/jdk
Checking if the remote hosts are reachable.
formulateLocalCmd rac2/bin/ping-c 1 -w 5
/bin/ping#rac2#-c#1#-w#5
formulateLocalCmd rac3/bin/ping-c 1 -w 5
/bin/ping#rac3#-c#1#-w#5
Remote host reachability check succeeded.
All hosts are reachable. Proceeding further...
NOTE :
As part of the setup procedure, this script will use ssh and scp to copy
files between the local host and the remote hosts. You may be prompted for
the password during the execution of the script.
AS PER SSH REQUIREMENTS, THIS SCRIPT WILL SECURE THE USER HOME DIRECTORY
AND THE .ssh DIRECTORY BY REVOKING GROUP AND WORLD WRITE PRIVILEDGES TO THESE
directories.
Do you want to continue and let the script make the above mentioned changes (yes/no)?
<b>yes</b>
If The files containing the client public and private keys already exist on the local host.
The current private key may or may not have a passphrase associated with it.
In case you remember the passphrase and do not want to re-run ssh-keygen, type 'no'. If you type 'yes',
the script will remove the old private/public key files and, any previous SSH user setups would be reset.
Enter 'yes', 'no'
<b>no</b>
Enter the password:<b>****</b>
Logfile Location : /tmp/SSHSetup2012-12-02_02-16-53-PM
Doing SSHSetup...
Please be patient, this operation might take sometime...Dont press Ctrl+C...
Local Platform:- Linux
------------------------------------------------------------------------
Verifying SSH setup
===================
The script will now run the date command on the remote nodes using ssh
to verify if ssh is setup correctly. IF THE SETUP IS CORRECTLY SETUP,
THERE SHOULD BE NO OUTPUT OTHER THAN THE DATE AND SSH SHOULD NOT ASK FOR
PASSWORDS. If you see any output other than date or are prompted for the
password, ssh is not setup correctly and you will need to resolve the
issue and set up ssh again.
The possible causes for failure could be:
1. The server settings in /etc/ssh/sshd_config file do not allow ssh
for user oracle.
2. The server may have disabled public key based authentication.
3. The client public key on the server may be outdated.
4. ~oracle or ~oracle/.ssh on the remote host may not be owned by oracle.
5. User may not have passed -shared option for shared remote users or
may be passing the -shared option for non-shared remote users.
6. If there is output in addition to the date, but no password is asked,
it may be a security alert shown as part of company policy. Append the
additional text to the <oms home="home">/sysman/prov/resources/ignoreMessages.txt file.
------------------------------------------------------------------------
--rac2:--
Running /usr/bin/ssh -x -l oracle rac2 date to verify SSH connectivity has been setup from local host to rac2.
IF YOU SEE ANY OTHER OUTPUT BESIDES THE OUTPUT OF THE DATE COMMAND OR IF YOU ARE PROMPTED FOR A PASSWORD HERE,
IT MEANS SSH SETUP HAS NOT BEEN SUCCESSFUL. Please note that being prompted for a passphrase may be OK but
being prompted for a password is ERROR.
Sun Dec 2 14:18:29 IST 2012
------------------------------------------------------------------------
--rac3:--
Running /usr/bin/ssh -x -l oracle rac3 date to verify SSH connectivity has been setup from local host to rac3.
IF YOU SEE ANY OTHER OUTPUT BESIDES THE OUTPUT OF THE DATE COMMAND OR IF YOU ARE PROMPTED FOR A PASSWORD HERE,
IT MEANS SSH SETUP HAS NOT BEEN SUCCESSFUL. Please note that being prompted for a passphrase may be OK but being
prompted for a password is ERROR.
Sun Dec 2 14:18:31 IST 2012
------------------------------------------------------------------------
------------------------------------------------------------------------
Verifying SSH connectivity has been setup from rac2 to rac2
------------------------------------------------------------------------
IF YOU SEE ANY OTHER OUTPUT BESIDES THE OUTPUT OF THE DATE COMMAND OR IF YOU ARE PROMPTED FOR A PASSWORD HERE,
IT MEANS SSH SETUP HAS NOT BEEN SUCCESSFUL.
Sun Dec 2 14:18:32 IST 2012
------------------------------------------------------------------------
------------------------------------------------------------------------
Verifying SSH connectivity has been setup from rac2 to rac3
------------------------------------------------------------------------
IF YOU SEE ANY OTHER OUTPUT BESIDES THE OUTPUT OF THE DATE COMMAND OR IF YOU ARE PROMPTED FOR A PASSWORD HERE,
IT MEANS SSH SETUP HAS NOT BEEN SUCCESSFUL.
Sun Dec 2 14:18:34 IST 2012
------------------------------------------------------------------------
-Verification from rac2 complete-
------------------------------------------------------------------------
Verifying SSH connectivity has been setup from rac3 to rac2
------------------------------------------------------------------------
IF YOU SEE ANY OTHER OUTPUT BESIDES THE OUTPUT OF THE DATE COMMAND OR IF YOU ARE PROMPTED FOR A PASSWORD HERE,
IT MEANS SSH SETUP HAS NOT BEEN SUCCESSFUL.
Sun Dec 2 14:18:35 IST 2012
------------------------------------------------------------------------
------------------------------------------------------------------------
Verifying SSH connectivity has been setup from rac3 to rac3
------------------------------------------------------------------------
IF YOU SEE ANY OTHER OUTPUT BESIDES THE OUTPUT OF THE DATE COMMAND OR IF YOU ARE PROMPTED FOR A PASSWORD HERE,
IT MEANS SSH SETUP HAS NOT BEEN SUCCESSFUL.
Sun Dec 2 14:18:37 IST 2012
------------------------------------------------------------------------
-Verification from rac3 complete-
SSH verification complete.</oms></code>
<b>Install cvuqdisk rpm on rac3.</b>
[root@rac1 ~]# scp /home/grid/software/oracle/grid/rpm/cvuqdisk-1.0.7-1.rpm rac3:/tmp
root@rac3's password:
cvuqdisk-1.0.7-1.rpm 100% 7831 7.7KB/s 00:00
[root@rac3 tmp]# CVUQDISK_GRP=oinstall; export CVUQDISK_GRP
[root@rac3 tmp]# rpm -iv cvuqdisk-1.0.7-1.rpm
Preparing packages for installation...
cvuqdisk-1.0.7-1
[root@rac3 tmp]# ls -l /usr/sbin/cvuqdisk
-rwsr-xr-x 1 root oinstall 8272 May 28 2009 /usr/sbin/cvuqdisk
<b>Verify readiness of the new node from hardware and operating system point of view.</b>
[root@rac1 ~]# su - grid
[grid@rac1 ~]$ /u01/app/11.2.0/grid/bin/cluvfy stage -post hwos -n rac3
<code>Performing post-checks for hardware and operating system setup
Checking node reachability...
Node reachability check passed from node "rac1"
Checking user equivalence...
User equivalence check passed for user "grid"
Checking node connectivity...
Checking hosts config file...
Verification of the hosts config file successful
Node connectivity passed for subnet "192.168.1.0" with node(s) rac3
TCP connectivity check passed for subnet "192.168.1.0"
Node connectivity passed for subnet "192.168.2.0" with node(s) rac3
TCP connectivity check passed for subnet "192.168.2.0"
Interfaces found on subnet "192.168.1.0" that are likely candidates for VIP are:
rac3 eth0:192.168.1.170
Interfaces found on subnet "192.168.2.0" that are likely candidates for a private interconnect are:
rac3 eth1:192.168.2.170
Node connectivity check passed
Check for multiple users with UID value 0 passed
Post-check for hardware and operating system setup was successful.</code>
<b>Do prechecks as grid user for node addition.</b>
[root@rac1 ~]# su - grid
[grid@rac1 ~]$ /u01/app/11.2.0/grid/bin/cluvfy stage -pre nodeadd -n rac3 -fixup -verbose
<code>Performing pre-checks for node addition
Checking node reachability...
Check: Node reachability from node "rac1"
Destination Node Reachable?
------------------------------------ ------------------------
rac3 yes
Result: Node reachability check passed from node "rac1"
Checking user equivalence...
Check: User equivalence for user "grid"
Node Name Comment
------------------------------------ ------------------------
rac3 passed
Result: User equivalence check passed for user "grid"
Checking node connectivity...
Checking hosts config file...
Node Name Status Comment
------------ ------------------------ ------------------------
rac2 passed
rac1 passed
rac3 passed
Verification of the hosts config file successful
Interface information for node "rac1"
Name IP Address Subnet Gateway Def. Gateway HW Address MTU
------ --------------- --------------- --------------- --------------- ----------------- ------
eth0 192.168.1.100 192.168.1.0 0.0.0.0 192.168.1.1 00:0C:29:C7:15:90 1500
eth0 192.168.1.150 192.168.1.0 0.0.0.0 192.168.1.1 00:0C:29:C7:15:90 1500
eth0 192.168.1.251 192.168.1.0 0.0.0.0 192.168.1.1 00:0C:29:C7:15:90 1500
eth0 192.168.1.152 192.168.1.0 0.0.0.0 192.168.1.1 00:0C:29:C7:15:90 1500
eth1 192.168.2.100 192.168.2.0 0.0.0.0 192.168.1.1 00:0C:29:C7:15:9A 1500
Interface information for node "rac2"
Name IP Address Subnet Gateway Def. Gateway HW Address MTU
------ --------------- --------------- --------------- --------------- ----------------- ------
eth0 192.168.1.200 192.168.1.0 0.0.0.0 192.168.1.1 00:0C:29:08:B8:BB 1500
eth0 192.168.1.151 192.168.1.0 0.0.0.0 192.168.1.1 00:0C:29:08:B8:BB 1500
eth0 192.168.1.252 192.168.1.0 0.0.0.0 192.168.1.1 00:0C:29:08:B8:BB 1500
eth1 192.168.2.200 192.168.2.0 0.0.0.0 192.168.1.1 00:0C:29:08:B8:C5 1500
Interface information for node "rac3"
Name IP Address Subnet Gateway Def. Gateway HW Address MTU
------ --------------- --------------- --------------- --------------- ----------------- ------
eth0 192.168.1.170 192.168.1.0 0.0.0.0 192.168.1.1 00:0C:29:27:02:5C 1500
eth1 192.168.2.170 192.168.2.0 0.0.0.0 192.168.1.1 00:0C:29:27:02:66 1500
Check: Node connectivity for interface "eth0"
Source Destination Connected?
------------------------------ ------------------------------ ----------------
rac1:eth0 rac1:eth0 yes
rac1:eth0 rac1:eth0 yes
rac1:eth0 rac1:eth0 yes
rac1:eth0 rac2:eth0 yes
rac1:eth0 rac2:eth0 yes
rac1:eth0 rac2:eth0 yes
rac1:eth0 rac3:eth0 yes
rac1:eth0 rac1:eth0 yes
rac1:eth0 rac1:eth0 yes
rac1:eth0 rac2:eth0 yes
rac1:eth0 rac2:eth0 yes
rac1:eth0 rac2:eth0 yes
rac1:eth0 rac3:eth0 yes
rac1:eth0 rac1:eth0 yes
rac1:eth0 rac2:eth0 yes
rac1:eth0 rac2:eth0 yes
rac1:eth0 rac2:eth0 yes
rac1:eth0 rac3:eth0 yes
rac1:eth0 rac2:eth0 yes
rac1:eth0 rac2:eth0 yes
rac1:eth0 rac2:eth0 yes
rac1:eth0 rac3:eth0 yes
rac2:eth0 rac2:eth0 yes
rac2:eth0 rac2:eth0 yes
rac2:eth0 rac3:eth0 yes
rac2:eth0 rac2:eth0 yes
rac2:eth0 rac3:eth0 yes
rac2:eth0 rac3:eth0 yes
Result: Node connectivity passed for interface "eth0"
Result: Node connectivity check passed
Checking CRS integrity...
The Oracle clusterware is healthy on node "rac1"
The Oracle clusterware is healthy on node "rac2"
CRS integrity check passed
Checking shared resources...
Checking CRS home location...
The location "/u01/app/11.2.0/grid" is not shared but is present/creatable on all nodes
Result: Shared resources check for node addition passed
Checking node connectivity...
Checking hosts config file...
Node Name Status Comment
------------ ------------------------ ------------------------
rac2 passed
rac1 passed
rac3 passed
Verification of the hosts config file successful
Interface information for node "rac1"
Name IP Address Subnet Gateway Def. Gateway HW Address MTU
------ --------------- --------------- --------------- --------------- ----------------- ------
eth0 192.168.1.100 192.168.1.0 0.0.0.0 192.168.1.1 00:0C:29:C7:15:90 1500
eth0 192.168.1.150 192.168.1.0 0.0.0.0 192.168.1.1 00:0C:29:C7:15:90 1500
eth0 192.168.1.251 192.168.1.0 0.0.0.0 192.168.1.1 00:0C:29:C7:15:90 1500
eth0 192.168.1.152 192.168.1.0 0.0.0.0 192.168.1.1 00:0C:29:C7:15:90 1500
eth1 192.168.2.100 192.168.2.0 0.0.0.0 192.168.1.1 00:0C:29:C7:15:9A 1500
Interface information for node "rac2"
Name IP Address Subnet Gateway Def. Gateway HW Address MTU
------ --------------- --------------- --------------- --------------- ----------------- ------
eth0 192.168.1.200 192.168.1.0 0.0.0.0 192.168.1.1 00:0C:29:08:B8:BB 1500
eth0 192.168.1.151 192.168.1.0 0.0.0.0 192.168.1.1 00:0C:29:08:B8:BB 1500
eth0 192.168.1.252 192.168.1.0 0.0.0.0 192.168.1.1 00:0C:29:08:B8:BB 1500
eth1 192.168.2.200 192.168.2.0 0.0.0.0 192.168.1.1 00:0C:29:08:B8:C5 1500
Interface information for node "rac3"
Name IP Address Subnet Gateway Def. Gateway HW Address MTU
------ --------------- --------------- --------------- --------------- ----------------- ------
eth0 192.168.1.170 192.168.1.0 0.0.0.0 192.168.1.1 00:0C:29:27:02:5C 1500
eth1 192.168.2.170 192.168.2.0 0.0.0.0 192.168.1.1 00:0C:29:27:02:66 1500
Check: Node connectivity of subnet "192.168.1.0"
Source Destination Connected?
------------------------------ ------------------------------ ----------------
rac1:eth0 rac1:eth0 yes
rac1:eth0 rac1:eth0 yes
rac1:eth0 rac1:eth0 yes
rac1:eth0 rac2:eth0 yes
rac1:eth0 rac2:eth0 yes
rac1:eth0 rac2:eth0 yes
rac1:eth0 rac3:eth0 yes
rac1:eth0 rac1:eth0 yes
rac1:eth0 rac1:eth0 yes
rac1:eth0 rac2:eth0 yes
rac1:eth0 rac2:eth0 yes
rac1:eth0 rac2:eth0 yes
rac1:eth0 rac3:eth0 yes
rac1:eth0 rac1:eth0 yes
rac1:eth0 rac2:eth0 yes
rac1:eth0 rac2:eth0 yes
rac1:eth0 rac2:eth0 yes
rac1:eth0 rac3:eth0 yes
rac1:eth0 rac2:eth0 yes
rac1:eth0 rac2:eth0 yes
rac1:eth0 rac2:eth0 yes
rac1:eth0 rac3:eth0 yes
rac2:eth0 rac2:eth0 yes
rac2:eth0 rac2:eth0 yes
rac2:eth0 rac3:eth0 yes
rac2:eth0 rac2:eth0 yes
rac2:eth0 rac3:eth0 yes
rac2:eth0 rac3:eth0 yes
Result: Node connectivity passed for subnet "192.168.1.0" with node(s) rac1,rac2,rac3
Check: TCP connectivity of subnet "192.168.1.0"
Source Destination Connected?
------------------------------ ------------------------------ ----------------
rac1:192.168.1.100 rac1:192.168.1.150 passed
rac1:192.168.1.100 rac1:192.168.1.251 passed
rac1:192.168.1.100 rac1:192.168.1.152 passed
rac1:192.168.1.100 rac2:192.168.1.200 passed
rac1:192.168.1.100 rac2:192.168.1.151 passed
rac1:192.168.1.100 rac2:192.168.1.252 passed
rac1:192.168.1.100 rac3:192.168.1.170 passed
Result: TCP connectivity check passed for subnet "192.168.1.0"
Check: Node connectivity of subnet "192.168.2.0"
Source Destination Connected?
------------------------------ ------------------------------ ----------------
rac1:eth1 rac2:eth1 yes
rac1:eth1 rac3:eth1 yes
rac2:eth1 rac3:eth1 yes
Result: Node connectivity passed for subnet "192.168.2.0" with node(s) rac1,rac2,rac3
Check: TCP connectivity of subnet "192.168.2.0"
Source Destination Connected?
------------------------------ ------------------------------ ----------------
rac1:192.168.2.100 rac2:192.168.2.200 passed
rac1:192.168.2.100 rac3:192.168.2.170 passed
Result: TCP connectivity check passed for subnet "192.168.2.0"
Interfaces found on subnet "192.168.1.0" that are likely candidates for VIP are:
rac1 eth0:192.168.1.100 eth0:192.168.1.150 eth0:192.168.1.251 eth0:192.168.1.152
rac2 eth0:192.168.1.200 eth0:192.168.1.151 eth0:192.168.1.252
rac3 eth0:192.168.1.170
Interfaces found on subnet "192.168.2.0" that are likely candidates for a private interconnect are:
rac1 eth1:192.168.2.100
rac2 eth1:192.168.2.200
rac3 eth1:192.168.2.170
Result: Node connectivity check passed
Check: Total memory
Node Name Available Required Comment
------------ ------------------------ ------------------------ ----------
rac3 1.98GB (2075500.0KB) 1.5GB (1572864.0KB) passed
Result: Total memory check passed
Check: Available memory
Node Name Available Required Comment
------------ ------------------------ ------------------------ ----------
rac3 1.92GB (2008364.0KB) 50MB (51200.0KB) passed
Result: Available memory check passed
Check: Swap space
Node Name Available Required Comment
------------ ------------------------ ------------------------ ----------
rac3 4GB (4192956.0KB) 2.97GB (3113250.0KB) passed
Result: Swap space check passed
Check: Free disk space for "rac3:/tmp"
Path Node Name Mount point Available Required Comment
---------------- ------------ ------------ ------------ ------------ ------------
/tmp rac3 / 17.45GB 1GB passed
Result: Free disk space check passed for "rac3:/tmp"
Check: User existence for "grid"
Node Name Status Comment
------------ ------------------------ ------------------------
rac3 exists passed
Result: User existence check passed for "grid"
Check: Run level
Node Name run level Required Comment
------------ ------------------------ ------------------------ ----------
rac3 5 3,5 passed
Result: Run level check passed
Check: Hard limits for "maximum open file descriptors"
Node Name Type Available Required Comment
---------------- ------------ ------------ ------------ ----------------
rac3 hard 65536 65536 passed
Result: Hard limits check passed for "maximum open file descriptors"
Check: Soft limits for "maximum open file descriptors"
Node Name Type Available Required Comment
---------------- ------------ ------------ ------------ ----------------
rac3 soft 1024 1024 passed
Result: Soft limits check passed for "maximum open file descriptors"
Check: Hard limits for "maximum user processes"
Node Name Type Available Required Comment
---------------- ------------ ------------ ------------ ----------------
rac3 hard 16384 16384 passed
Result: Hard limits check passed for "maximum user processes"
Check: Soft limits for "maximum user processes"
Node Name Type Available Required Comment
---------------- ------------ ------------ ------------ ----------------
rac3 soft 2047 2047 passed
Result: Soft limits check passed for "maximum user processes"
Check: System architecture
Node Name Available Required Comment
------------ ------------------------ ------------------------ ----------
rac3 i686 x86 passed
Result: System architecture check passed
Check: Kernel version
Node Name Available Required Comment
------------ ------------------------ ------------------------ ----------
rac3 2.6.18-164.el5 2.6.18 passed
Result: Kernel version check passed
Check: Kernel parameter for "semmsl"
Node Name Configured Required Comment
------------ ------------------------ ------------------------ ----------
rac3 250 250 passed
Result: Kernel parameter check passed for "semmsl"
Check: Kernel parameter for "semmns"
Node Name Configured Required Comment
------------ ------------------------ ------------------------ ----------
rac3 32000 32000 passed
Result: Kernel parameter check passed for "semmns"
Check: Kernel parameter for "semopm"
Node Name Configured Required Comment
------------ ------------------------ ------------------------ ----------
rac3 100 100 passed
Result: Kernel parameter check passed for "semopm"
Check: Kernel parameter for "semmni"
Node Name Configured Required Comment
------------ ------------------------ ------------------------ ----------
rac3 128 128 passed
Result: Kernel parameter check passed for "semmni"
Check: Kernel parameter for "shmmax"
Node Name Configured Required Comment
------------ ------------------------ ------------------------ ----------
rac3 4294967295 536870912 passed
Result: Kernel parameter check passed for "shmmax"
Check: Kernel parameter for "shmmni"
Node Name Configured Required Comment
------------ ------------------------ ------------------------ ----------
rac3 4096 4096 passed
Result: Kernel parameter check passed for "shmmni"
Check: Kernel parameter for "shmall"
Node Name Configured Required Comment
------------ ------------------------ ------------------------ ----------
rac3 268435456 2097152 passed
Result: Kernel parameter check passed for "shmall"
Check: Kernel parameter for "file-max"
Node Name Configured Required Comment
------------ ------------------------ ------------------------ ----------
rac3 6815744 6815744 passed
Result: Kernel parameter check passed for "file-max"
Check: Kernel parameter for "ip_local_port_range"
Node Name Configured Required Comment
------------ ------------------------ ------------------------ ----------
rac3 between 9000 & 65500 between 9000 & 65500 passed
Result: Kernel parameter check passed for "ip_local_port_range"
Check: Kernel parameter for "rmem_default"
Node Name Configured Required Comment
------------ ------------------------ ------------------------ ----------
rac3 262144 262144 passed
Result: Kernel parameter check passed for "rmem_default"
Check: Kernel parameter for "rmem_max"
Node Name Configured Required Comment
------------ ------------------------ ------------------------ ----------
rac3 4194304 4194304 passed
Result: Kernel parameter check passed for "rmem_max"
Check: Kernel parameter for "wmem_default"
Node Name Configured Required Comment
------------ ------------------------ ------------------------ ----------
rac3 262144 262144 passed
Result: Kernel parameter check passed for "wmem_default"
Check: Kernel parameter for "wmem_max"
Node Name Configured Required Comment
------------ ------------------------ ------------------------ ----------
rac3 1048576 1048576 passed
Result: Kernel parameter check passed for "wmem_max"
Check: Kernel parameter for "aio-max-nr"
Node Name Configured Required Comment
------------ ------------------------ ------------------------ ----------
rac3 1048576 1048576 passed
Result: Kernel parameter check passed for "aio-max-nr"
Check: Package existence for "make-3.81"
Node Name Available Required Comment
------------ ------------------------ ------------------------ ----------
rac3 make-3.81-3.el5 make-3.81 passed
Result: Package existence check passed for "make-3.81"
Check: Package existence for "binutils-2.17.50.0.6"
Node Name Available Required Comment
------------ ------------------------ ------------------------ ----------
rac3 binutils-2.17.50.0.6-12.el5 binutils-2.17.50.0.6 passed
Result: Package existence check passed for "binutils-2.17.50.0.6"
Check: Package existence for "gcc-4.1.2"
Node Name Available Required Comment
------------ ------------------------ ------------------------ ----------
rac3 gcc-4.1.2-46.el5 gcc-4.1.2 passed
Result: Package existence check passed for "gcc-4.1.2"
Check: Package existence for "gcc-c++-4.1.2"
Node Name Available Required Comment
------------ ------------------------ ------------------------ ----------
rac3 gcc-c++-4.1.2-46.el5 gcc-c++-4.1.2 passed
Result: Package existence check passed for "gcc-c++-4.1.2"
Check: Package existence for "libgomp-4.1.2"
Node Name Available Required Comment
------------ ------------------------ ------------------------ ----------
rac3 libgomp-4.4.0-6.el5 libgomp-4.1.2 passed
Result: Package existence check passed for "libgomp-4.1.2"
Check: Package existence for "libaio-0.3.106"
Node Name Available Required Comment
------------ ------------------------ ------------------------ ----------
rac3 libaio-0.3.106-3.2 libaio-0.3.106 passed
Result: Package existence check passed for "libaio-0.3.106"
Check: Package existence for "glibc-2.5-24"
Node Name Available Required Comment
------------ ------------------------ ------------------------ ----------
rac3 glibc-2.5-42 glibc-2.5-24 passed
Result: Package existence check passed for "glibc-2.5-24"
Check: Package existence for "compat-libstdc++-33-3.2.3"
Node Name Available Required Comment
------------ ------------------------ ------------------------ ----------
rac3 compat-libstdc++-33-3.2.3-61 compat-libstdc++-33-3.2.3 passed
Result: Package existence check passed for "compat-libstdc++-33-3.2.3"
Check: Package existence for "elfutils-libelf-0.125"
Node Name Available Required Comment
------------ ------------------------ ------------------------ ----------
rac3 elfutils-libelf-0.137-3.el5 elfutils-libelf-0.125 passed
Result: Package existence check passed for "elfutils-libelf-0.125"
Check: Package existence for "elfutils-libelf-devel-0.125"
Node Name Available Required Comment
------------ ------------------------ ------------------------ ----------
rac3 elfutils-libelf-devel-0.137-3.el5 elfutils-libelf-devel-0.125 passed
Result: Package existence check passed for "elfutils-libelf-devel-0.125"
Check: Package existence for "glibc-common-2.5"
Node Name Available Required Comment
------------ ------------------------ ------------------------ ----------
rac3 glibc-common-2.5-42 glibc-common-2.5 passed
Result: Package existence check passed for "glibc-common-2.5"
Check: Package existence for "glibc-devel-2.5"
Node Name Available Required Comment
------------ ------------------------ ------------------------ ----------
rac3 glibc-devel-2.5-42 glibc-devel-2.5 passed
Result: Package existence check passed for "glibc-devel-2.5"
Check: Package existence for "glibc-headers-2.5"
Node Name Available Required Comment
------------ ------------------------ ------------------------ ----------
rac3 glibc-headers-2.5-42 glibc-headers-2.5 passed
Result: Package existence check passed for "glibc-headers-2.5"
Check: Package existence for "libaio-devel-0.3.106"
Node Name Available Required Comment
------------ ------------------------ ------------------------ ----------
rac3 libaio-devel-0.3.106-3.2 libaio-devel-0.3.106 passed
Result: Package existence check passed for "libaio-devel-0.3.106"
Check: Package existence for "libgcc-4.1.2"
Node Name Available Required Comment
------------ ------------------------ ------------------------ ----------
rac3 libgcc-4.1.2-46.el5 libgcc-4.1.2 passed
Result: Package existence check passed for "libgcc-4.1.2"
Check: Package existence for "libstdc++-4.1.2"
Node Name Available Required Comment
------------ ------------------------ ------------------------ ----------
rac3 libstdc++-4.1.2-46.el5 libstdc++-4.1.2 passed
Result: Package existence check passed for "libstdc++-4.1.2"
Check: Package existence for "libstdc++-devel-4.1.2"
Node Name Available Required Comment
------------ ------------------------ ------------------------ ----------
rac3 libstdc++-devel-4.1.2-46.el5 libstdc++-devel-4.1.2 passed
Result: Package existence check passed for "libstdc++-devel-4.1.2"
Check: Package existence for "sysstat-7.0.2"
Node Name Available Required Comment
------------ ------------------------ ------------------------ ----------
rac3 sysstat-7.0.2-3.el5 sysstat-7.0.2 passed
Result: Package existence check passed for "sysstat-7.0.2"
Check: Package existence for "unixODBC-2.2.11"
Node Name Available Required Comment
------------ ------------------------ ------------------------ ----------
rac3 unixODBC-2.2.11-7.1 unixODBC-2.2.11 passed
Result: Package existence check passed for "unixODBC-2.2.11"
Check: Package existence for "unixODBC-devel-2.2.11"
Node Name Available Required Comment
------------ ------------------------ ------------------------ ----------
rac3 unixODBC-devel-2.2.11-7.1 unixODBC-devel-2.2.11 passed
Result: Package existence check passed for "unixODBC-devel-2.2.11"
Check: Package existence for "ksh-20060214"
Node Name Available Required Comment
------------ ------------------------ ------------------------ ----------
rac3 ksh-20080202-14.el5 ksh-20060214 passed
Result: Package existence check passed for "ksh-20060214"
Checking for multiple users with UID value 0
Result: Check for multiple users with UID value 0 passed
Checking to make sure user "grid" is not in "root" group
Node Name Status Comment
------------ ------------------------ ------------------------
rac3 does not exist passed
Result: User "grid" is not part of "root" group. Check passed
Starting Clock synchronization checks using Network Time Protocol(NTP)...
NTP Configuration file check started...
Network Time Protocol(NTP) configuration file not found on any of the nodes. Oracle Cluster Time
Synchronization Service(CTSS) can be used instead of NTP for time synchronization on the cluster nodes
Result: Clock synchronization check using Network Time Protocol(NTP) passed
Pre-check for node addition was successful. </code>
<b>Extend grid infrastructure to the new node from RAC1.</b>
[root@rac1 ~]# xhost +
access control disabled, clients can connect from any host
[root@rac1 ~]# su - grid
[grid@rac1 ~]$ cd /u01/app/11.2.0/grid/oui/bin
[grid@rac1 bin]$ ./addNode.sh -silent "CLUSTER_NEW_NODES={rac3}" "CLUSTER_NEW_VIRTUAL_HOSTNAMES={rac3-vip}"
<code>Starting Oracle Universal Installer...
Checking swap space: must be greater than 500 MB. Actual 3933 MB Passed
Oracle Universal Installer, Version 11.2.0.1.0 Production
Copyright (C) 1999, 2009, Oracle. All rights reserved.
Performing tests to see whether nodes rac2,rac3 are available
............................................................... 100% Done.
.
-----------------------------------------------------------------------------
Cluster Node Addition Summary
Global Settings
Source: /u01/app/11.2.0/grid
New Nodes
Space Requirements
New Nodes
rac3
/: Required 3.45GB : Available 16.25GB
Installed Products
Product Names
Oracle Grid Infrastructure 11.2.0.1.0
Sun JDK 1.5.0.17.0
Installer SDK Component 11.2.0.1.0
Oracle One-Off Patch Installer 11.2.0.0.2
Oracle Universal Installer 11.2.0.1.0
Oracle Configuration Manager Deconfiguration 10.3.1.0.0
Enterprise Manager Common Core Files 10.2.0.4.2
Oracle DBCA Deconfiguration 11.2.0.1.0
Oracle RAC Deconfiguration 11.2.0.1.0
Oracle Quality of Service Management (Server) 11.2.0.1.0
Installation Plugin Files 11.2.0.1.0
Universal Storage Manager Files 11.2.0.1.0
Oracle Text Required Support Files 11.2.0.1.0
Automatic Storage Management Assistant 11.2.0.1.0
Oracle Database 11g Multimedia Files 11.2.0.1.0
Oracle Multimedia Java Advanced Imaging 11.2.0.1.0
Oracle Globalization Support 11.2.0.1.0
Oracle Multimedia Locator RDBMS Files 11.2.0.1.0
Oracle Core Required Support Files 11.2.0.1.0
Bali Share 1.1.18.0.0
Oracle Database Deconfiguration 11.2.0.1.0
Oracle Quality of Service Management (Client) 11.2.0.1.0
Expat libraries 2.0.1.0.1
Oracle Containers for Java 11.2.0.1.0
Perl Modules 5.10.0.0.1
Secure Socket Layer 11.2.0.1.0
Oracle JDBC/OCI Instant Client 11.2.0.1.0
Oracle Multimedia Client Option 11.2.0.1.0
LDAP Required Support Files 11.2.0.1.0
Character Set Migration Utility 11.2.0.1.0
Perl Interpreter 5.10.0.0.1
PL/SQL Embedded Gateway 11.2.0.1.0
OLAP SQL Scripts 11.2.0.1.0
Database SQL Scripts 11.2.0.1.0
Oracle Extended Windowing Toolkit 3.4.47.0.0
SSL Required Support Files for InstantClient 11.2.0.1.0
SQL*Plus Files for Instant Client 11.2.0.1.0
Oracle Net Required Support Files 11.2.0.1.0
Oracle Database User Interface 2.2.13.0.0
RDBMS Required Support Files for Instant Client 11.2.0.1.0
Enterprise Manager Minimal Integration 11.2.0.1.0
XML Parser for Java 11.2.0.1.0
Oracle Security Developer Tools 11.2.0.1.0
Oracle Wallet Manager 11.2.0.1.0
Enterprise Manager plugin Common Files 11.2.0.1.0
Platform Required Support Files 11.2.0.1.0
Oracle JFC Extended Windowing Toolkit 4.2.36.0.0
RDBMS Required Support Files 11.2.0.1.0
Oracle Ice Browser 5.2.3.6.0
Oracle Help For Java 4.2.9.0.0
Enterprise Manager Common Files 10.2.0.4.2
Deinstallation Tool 11.2.0.1.0
Oracle Java Client 11.2.0.1.0
Cluster Verification Utility Files 11.2.0.1.0
Oracle Notification Service (eONS) 11.2.0.1.0
Oracle LDAP administration 11.2.0.1.0
Cluster Verification Utility Common Files 11.2.0.1.0
Oracle Clusterware RDBMS Files 11.2.0.1.0
Oracle Locale Builder 11.2.0.1.0
Oracle Globalization Support 11.2.0.1.0
Buildtools Common Files 11.2.0.1.0
Oracle RAC Required Support Files-HAS 11.2.0.1.0
SQL*Plus Required Support Files 11.2.0.1.0
XDK Required Support Files 11.2.0.1.0
Agent Required Support Files 10.2.0.4.2
Parser Generator Required Support Files 11.2.0.1.0
Precompiler Required Support Files 11.2.0.1.0
Installation Common Files 11.2.0.1.0
Required Support Files 11.2.0.1.0
Oracle JDBC/THIN Interfaces 11.2.0.1.0
Oracle Multimedia Locator 11.2.0.1.0
Oracle Multimedia 11.2.0.1.0
HAS Common Files 11.2.0.1.0
Assistant Common Files 11.2.0.1.0
PL/SQL 11.2.0.1.0
HAS Files for DB 11.2.0.1.0
Oracle Recovery Manager 11.2.0.1.0
Oracle Database Utilities 11.2.0.1.0
Oracle Notification Service 11.2.0.0.0
SQL*Plus 11.2.0.1.0
Oracle Netca Client 11.2.0.1.0
Oracle Net 11.2.0.1.0
Oracle JVM 11.2.0.1.0
Oracle Internet Directory Client 11.2.0.1.0
Oracle Net Listener 11.2.0.1.0
Cluster Ready Services Files 11.2.0.1.0
Oracle Database 11g 11.2.0.1.0
-----------------------------------------------------------------------------
Instantiating scripts for add node (Sunday, December 2, 2012 2:49:04 PM IST)
. 1% Done.
Instantiation of add node scripts complete
Copying to remote nodes (Sunday, December 2, 2012 2:49:15 PM IST)
...............................................................................................96% Done.
Home copied to new nodes
Saving inventory on nodes (Sunday, December 2, 2012 3:12:50 PM IST)
. 100% Done.
Save inventory complete
WARNING:A new inventory has been created on one or more nodes in this session. However, it has not yet been
registered as the central inventory of this system.
To register the new inventory please run the script at '/u01/app/oraInventory/orainstRoot.sh' with root
privileges on nodes 'rac3'.
If you do not register the inventory, you may not be able to update or patch the products you installed.
The following configuration scripts need to be executed as the "root" user in each cluster node.
/u01/app/oraInventory/orainstRoot.sh #On nodes rac3
/u01/app/11.2.0/grid/root.sh #On nodes rac3
To execute the configuration scripts:
1. Open a terminal window
2. Log in as "root"
3. Run the scripts in each cluster node
The Cluster Node Addition of /u01/app/11.2.0/grid was successful.
Please check '/tmp/silentInstall.log' for more details.</code>
<b>Run scripts as the root user on the new node.</b>
[root@rac3 ~]# /u01/app/oraInventory/orainstRoot.sh
<code>Creating the Oracle inventory pointer file (/etc/oraInst.loc)
Changing permissions of /u01/app/oraInventory.
Adding read,write permissions for group.
Removing read,write,execute permissions for world.
Changing groupname of /u01/app/oraInventory to oinstall.
The execution of the script is complete.</code>
[root@rac3 ~]# /u01/app/11.2.0/grid/root.sh
<code>Running Oracle 11g root.sh script...
The following environment variables are set as:
ORACLE_OWNER= grid
ORACLE_HOME= /u01/app/11.2.0/grid
Enter the full pathname of the local bin directory: [/usr/local/bin]:
Copying dbhome to /usr/local/bin ...
Copying oraenv to /usr/local/bin ...
Copying coraenv to /usr/local/bin ...
Creating /etc/oratab file...
Entries will be added to the /etc/oratab file as needed by
Database Configuration Assistant when a database is created
Finished running generic part of root.sh script.
Now product-specific root actions will be performed.
2012-12-02 15:17:37: Parsing the host name
2012-12-02 15:17:37: Checking for super user privileges
2012-12-02 15:17:37: User has super user privileges
Using configuration parameter file: /u01/app/11.2.0/grid/crs/install/crsconfig_params
Creating trace directory
LOCAL ADD MODE
Creating OCR keys for user 'root', privgrp 'root'..
Operation successful.
Adding daemon to inittab
CRS-4123: Oracle High Availability Services has been started.
ohasd is starting
CRS-4402: The CSS daemon was started in exclusive mode but found an active CSS daemon on node rac1,
number 1, and is terminating
An active cluster was found during exclusive startup, restarting to join the cluster
CRS-2672: Attempting to start 'ora.mdnsd' on 'rac3'
CRS-2676: Start of 'ora.mdnsd' on 'rac3' succeeded
CRS-2672: Attempting to start 'ora.gipcd' on 'rac3'
CRS-2676: Start of 'ora.gipcd' on 'rac3' succeeded
CRS-2672: Attempting to start 'ora.gpnpd' on 'rac3'
CRS-2676: Start of 'ora.gpnpd' on 'rac3' succeeded
CRS-2672: Attempting to start 'ora.cssdmonitor' on 'rac3'
CRS-2676: Start of 'ora.cssdmonitor' on 'rac3' succeeded
CRS-2672: Attempting to start 'ora.cssd' on 'rac3'
CRS-2672: Attempting to start 'ora.diskmon' on 'rac3'
CRS-2676: Start of 'ora.diskmon' on 'rac3' succeeded
CRS-2676: Start of 'ora.cssd' on 'rac3' succeeded
CRS-2672: Attempting to start 'ora.ctssd' on 'rac3'
CRS-2676: Start of 'ora.ctssd' on 'rac3' succeeded
CRS-2672: Attempting to start 'ora.drivers.acfs' on 'rac3'
CRS-2676: Start of 'ora.drivers.acfs' on 'rac3' succeeded
CRS-2672: Attempting to start 'ora.asm' on 'rac3'
CRS-2676: Start of 'ora.asm' on 'rac3' succeeded
CRS-2672: Attempting to start 'ora.crsd' on 'rac3'
CRS-2676: Start of 'ora.crsd' on 'rac3' succeeded
CRS-2672: Attempting to start 'ora.evmd' on 'rac3'
CRS-2676: Start of 'ora.evmd' on 'rac3' succeeded
clscfg: EXISTING configuration version 5 detected.
clscfg: version 5 is 11g Release 2.
Successfully accumulated necessary OCR keys.
Creating OCR keys for user 'root', privgrp 'root'..
Operation successful.
rac3 2012/12/02 15:25:08 /u01/app/11.2.0/grid/cdata/rac3/backup_20121202_152508.olr
Configure Oracle Grid Infrastructure for a Cluster ... succeeded
Updating inventory properties for clusterware
Starting Oracle Universal Installer...
Checking swap space: must be greater than 500 MB. Actual 4094 MB Passed
The inventory pointer is located at /etc/oraInst.loc
The inventory is located at /u01/app/oraInventory
'UpdateNodeList' was successful.</code>
<b>Do post node addition checks from rac1.</b>
[root@rac1 ~]# su - grid
[grid@rac1 ~]$ /u01/app/11.2.0/grid/bin/cluvfy stage -post nodeadd -n rac3 -verbose
<code>Performing post-checks for node addition
Checking node reachability...
Check: Node reachability from node "rac1"
Destination Node Reachable?
------------------------------------ ------------------------
rac3 yes
Result: Node reachability check passed from node "rac1"
Checking user equivalence...
Check: User equivalence for user "grid"
Node Name Comment
------------------------------------ ------------------------
rac3 passed
Result: User equivalence check passed for user "grid"
Checking node connectivity...
Checking hosts config file...
Node Name Status Comment
------------ ------------------------ ------------------------
rac2 passed
rac1 passed
rac3 passed
Verification of the hosts config file successful
Interface information for node "rac2"
Name IP Address Subnet Gateway Def. Gateway HW Address MTU
------ --------------- --------------- --------------- --------------- ----------------- ------
eth0 192.168.1.200 192.168.1.0 0.0.0.0 192.168.1.1 00:0C:29:08:B8:BB 1500
eth0 192.168.1.151 192.168.1.0 0.0.0.0 192.168.1.1 00:0C:29:08:B8:BB 1500
eth0 192.168.1.252 192.168.1.0 0.0.0.0 192.168.1.1 00:0C:29:08:B8:BB 1500
eth1 192.168.2.200 192.168.2.0 0.0.0.0 192.168.1.1 00:0C:29:08:B8:C5 1500
Interface information for node "rac1"
Name IP Address Subnet Gateway Def. Gateway HW Address MTU
------ --------------- --------------- --------------- --------------- ----------------- ------
eth0 192.168.1.100 192.168.1.0 0.0.0.0 192.168.1.1 00:0C:29:C7:15:90 1500
eth0 192.168.1.150 192.168.1.0 0.0.0.0 192.168.1.1 00:0C:29:C7:15:90 1500
eth0 192.168.1.251 192.168.1.0 0.0.0.0 192.168.1.1 00:0C:29:C7:15:90 1500
eth1 192.168.2.100 192.168.2.0 0.0.0.0 192.168.1.1 00:0C:29:C7:15:9A 1500
Interface information for node "rac3"
Name IP Address Subnet Gateway Def. Gateway HW Address MTU
------ --------------- --------------- --------------- --------------- ----------------- ------
eth0 192.168.1.170 192.168.1.0 0.0.0.0 192.168.1.1 00:0C:29:27:02:5C 1500
eth0 192.168.1.152 192.168.1.0 0.0.0.0 192.168.1.1 00:0C:29:27:02:5C 1500
eth0 192.168.1.250 192.168.1.0 0.0.0.0 192.168.1.1 00:0C:29:27:02:5C 1500
eth1 192.168.2.170 192.168.2.0 0.0.0.0 192.168.1.1 00:0C:29:27:02:66 1500
Check: Node connectivity for interface "eth0"
Source Destination Connected?
------------------------------ ------------------------------ ----------------
rac2:eth0 rac2:eth0 yes
rac2:eth0 rac2:eth0 yes
rac2:eth0 rac1:eth0 yes
rac2:eth0 rac1:eth0 yes
rac2:eth0 rac1:eth0 yes
rac2:eth0 rac3:eth0 yes
rac2:eth0 rac3:eth0 yes
rac2:eth0 rac3:eth0 yes
rac2:eth0 rac2:eth0 yes
rac2:eth0 rac1:eth0 yes
rac2:eth0 rac1:eth0 yes
rac2:eth0 rac1:eth0 yes
rac2:eth0 rac3:eth0 yes
rac2:eth0 rac3:eth0 yes
rac2:eth0 rac3:eth0 yes
rac2:eth0 rac1:eth0 yes
rac2:eth0 rac1:eth0 yes
rac2:eth0 rac1:eth0 yes
rac2:eth0 rac3:eth0 yes
rac2:eth0 rac3:eth0 yes
rac2:eth0 rac3:eth0 yes
rac1:eth0 rac1:eth0 yes
rac1:eth0 rac1:eth0 yes
rac1:eth0 rac3:eth0 yes
rac1:eth0 rac3:eth0 yes
rac1:eth0 rac3:eth0 yes
rac1:eth0 rac1:eth0 yes
rac1:eth0 rac3:eth0 yes
rac1:eth0 rac3:eth0 yes
rac1:eth0 rac3:eth0 yes
rac1:eth0 rac3:eth0 yes
rac1:eth0 rac3:eth0 yes
rac1:eth0 rac3:eth0 yes
rac3:eth0 rac3:eth0 yes
rac3:eth0 rac3:eth0 yes
rac3:eth0 rac3:eth0 yes
Result: Node connectivity passed for interface "eth0"
Result: Node connectivity check passed
Checking cluster integrity...
Node Name
------------------------------------
rac1
rac2
rac3
Cluster integrity check passed
Checking CRS integrity...
The Oracle clusterware is healthy on node "rac2"
The Oracle clusterware is healthy on node "rac1"
The Oracle clusterware is healthy on node "rac3"
CRS integrity check passed
Checking shared resources...
Checking CRS home location...
The location "/u01/app/11.2.0/grid" is not shared but is present/creatable on all nodes
Result: Shared resources check for node addition passed
Checking node connectivity...
Checking hosts config file...
Node Name Status Comment
------------ ------------------------ ------------------------
rac2 passed
rac1 passed
rac3 passed
Verification of the hosts config file successful
Interface information for node "rac2"
Name IP Address Subnet Gateway Def. Gateway HW Address MTU
------ --------------- --------------- --------------- --------------- ----------------- ------
eth0 192.168.1.200 192.168.1.0 0.0.0.0 192.168.1.1 00:0C:29:08:B8:BB 1500
eth0 192.168.1.151 192.168.1.0 0.0.0.0 192.168.1.1 00:0C:29:08:B8:BB 1500
eth0 192.168.1.252 192.168.1.0 0.0.0.0 192.168.1.1 00:0C:29:08:B8:BB 1500
eth1 192.168.2.200 192.168.2.0 0.0.0.0 192.168.1.1 00:0C:29:08:B8:C5 1500
Interface information for node "rac1"
Name IP Address Subnet Gateway Def. Gateway HW Address MTU
------ --------------- --------------- --------------- --------------- ----------------- ------
eth0 192.168.1.100 192.168.1.0 0.0.0.0 192.168.1.1 00:0C:29:C7:15:90 1500
eth0 192.168.1.150 192.168.1.0 0.0.0.0 192.168.1.1 00:0C:29:C7:15:90 1500
eth0 192.168.1.251 192.168.1.0 0.0.0.0 192.168.1.1 00:0C:29:C7:15:90 1500
eth1 192.168.2.100 192.168.2.0 0.0.0.0 192.168.1.1 00:0C:29:C7:15:9A 1500
Interface information for node "rac3"
Name IP Address Subnet Gateway Def. Gateway HW Address MTU
------ --------------- --------------- --------------- --------------- ----------------- ------
eth0 192.168.1.170 192.168.1.0 0.0.0.0 192.168.1.1 00:0C:29:27:02:5C 1500
eth0 192.168.1.152 192.168.1.0 0.0.0.0 192.168.1.1 00:0C:29:27:02:5C 1500
eth0 192.168.1.250 192.168.1.0 0.0.0.0 192.168.1.1 00:0C:29:27:02:5C 1500
eth1 192.168.2.170 192.168.2.0 0.0.0.0 192.168.1.1 00:0C:29:27:02:66 1500
Check: Node connectivity of subnet "192.168.1.0"
Source Destination Connected?
------------------------------ ------------------------------ ----------------
rac2:eth0 rac2:eth0 yes
rac2:eth0 rac2:eth0 yes
rac2:eth0 rac1:eth0 yes
rac2:eth0 rac1:eth0 yes
rac2:eth0 rac1:eth0 yes
rac2:eth0 rac3:eth0 yes
rac2:eth0 rac3:eth0 yes
rac2:eth0 rac3:eth0 yes
rac2:eth0 rac2:eth0 yes
rac2:eth0 rac1:eth0 yes
rac2:eth0 rac1:eth0 yes
rac2:eth0 rac1:eth0 yes
rac2:eth0 rac3:eth0 yes
rac2:eth0 rac3:eth0 yes
rac2:eth0 rac3:eth0 yes
rac2:eth0 rac1:eth0 yes
rac2:eth0 rac1:eth0 yes
rac2:eth0 rac1:eth0 yes
rac2:eth0 rac3:eth0 yes
rac2:eth0 rac3:eth0 yes
rac2:eth0 rac3:eth0 yes
rac1:eth0 rac1:eth0 yes
rac1:eth0 rac1:eth0 yes
rac1:eth0 rac3:eth0 yes
rac1:eth0 rac3:eth0 yes
rac1:eth0 rac3:eth0 yes
rac1:eth0 rac1:eth0 yes
rac1:eth0 rac3:eth0 yes
rac1:eth0 rac3:eth0 yes
rac1:eth0 rac3:eth0 yes
rac1:eth0 rac3:eth0 yes
rac1:eth0 rac3:eth0 yes
rac1:eth0 rac3:eth0 yes
rac3:eth0 rac3:eth0 yes
rac3:eth0 rac3:eth0 yes
rac3:eth0 rac3:eth0 yes
Result: Node connectivity passed for subnet "192.168.1.0" with node(s) rac2,rac1,rac3
Check: TCP connectivity of subnet "192.168.1.0"
Source Destination Connected?
------------------------------ ------------------------------ ----------------
rac1:192.168.1.100 rac2:192.168.1.200 failed
rac1:192.168.1.100 rac2:192.168.1.151 passed
rac1:192.168.1.100 rac2:192.168.1.252 passed
rac1:192.168.1.100 rac1:192.168.1.150 passed
rac1:192.168.1.100 rac1:192.168.1.251 passed
rac1:192.168.1.100 rac3:192.168.1.170 passed
rac1:192.168.1.100 rac3:192.168.1.152 passed
rac1:192.168.1.100 rac3:192.168.1.250 passed
Result: TCP connectivity check failed for subnet "192.168.1.0"
Check: Node connectivity of subnet "192.168.2.0"
Source Destination Connected?
------------------------------ ------------------------------ ----------------
rac2:eth1 rac1:eth1 yes
rac2:eth1 rac3:eth1 yes
rac1:eth1 rac3:eth1 yes
Result: Node connectivity passed for subnet "192.168.2.0" with node(s) rac2,rac1,rac3
Check: TCP connectivity of subnet "192.168.2.0"
Source Destination Connected?
------------------------------ ------------------------------ ----------------
rac1:192.168.2.100 rac2:192.168.2.200 passed
rac1:192.168.2.100 rac3:192.168.2.170 passed
Result: TCP connectivity check passed for subnet "192.168.2.0"
Interfaces found on subnet "192.168.1.0" that are likely candidates for VIP are:
rac2 eth0:192.168.1.200 eth0:192.168.1.151 eth0:192.168.1.252
rac1 eth0:192.168.1.100 eth0:192.168.1.150 eth0:192.168.1.251
rac3 eth0:192.168.1.170 eth0:192.168.1.152 eth0:192.168.1.250
Interfaces found on subnet "192.168.2.0" that are likely candidates for a private interconnect are:
rac2 eth1:192.168.2.200
rac1 eth1:192.168.2.100
rac3 eth1:192.168.2.170
Result: Node connectivity check passed
Checking node application existence...
Checking existence of VIP node application
Node Name Required Status Comment
------------ ------------------------ ------------------------ ----------
rac2 yes online passed
rac1 yes online passed
rac3 yes online passed
Result: Check passed.
Checking existence of ONS node application
Node Name Required Status Comment
------------ ------------------------ ------------------------ ----------
rac2 no online passed
rac1 no online passed
rac3 no online passed
Result: Check passed.
Checking existence of GSD node application
Node Name Required Status Comment
------------ ------------------------ ------------------------ ----------
rac2 no does not exist ignored
rac1 no does not exist ignored
rac3 no does not exist ignored
Result: Check ignored.
Checking existence of EONS node application
Node Name Required Status Comment
------------ ------------------------ ------------------------ ----------
rac2 no online passed
rac1 no online passed
rac3 no online passed
Result: Check passed.
Checking existence of NETWORK node application
Node Name Required Status Comment
------------ ------------------------ ------------------------ ----------
rac2 no online passed
rac1 no online passed
rac3 no online passed
Result: Check passed.
Checking Single Client Access Name (SCAN)...
SCAN VIP name Node Running? ListenerName Port Running?
---------------- ------------ ------------ ------------ ------------ ------------
rac-cluster-scan rac2 true LISTENER 1521 true
Checking name resolution setup for "rac-cluster-scan"...
SCAN Name IP Address Status Comment
------------ ------------------------ ------------------------ ----------
rac-cluster-scan 192.168.1.151 passed
rac-cluster-scan 192.168.1.152 passed
rac-cluster-scan 192.168.1.150 passed
Verification of SCAN VIP and Listener setup passed
Checking to make sure user "grid" is not in "root" group
Node Name Status Comment
------------ ------------------------ ------------------------
rac3 does not exist passed
Result: User "grid" is not part of "root" group. Check passed
Checking if Clusterware is installed on all nodes...
Check of Clusterware install passed
Checking if CTSS Resource is running on all nodes...
Check: CTSS Resource running on all nodes
Node Name Status
------------------------------------ ------------------------
rac3 passed
Result: CTSS resource check passed
Querying CTSS for time offset on all nodes...
Result: Query of CTSS for time offset passed
Check CTSS state started...
Check: CTSS state
Node Name State
------------------------------------ ------------------------
rac3 Active
CTSS is in Active state. Proceeding with check of clock time offsets on all nodes...
Reference Time Offset Limit: 1000.0 msecs
Check: Reference Time Offset
Node Name Time Offset Status
------------ ------------------------ ------------------------
rac3 0.0 passed
Time offset is within the specified limits on the following set of nodes:
"[rac3]"
Result: Check of clock time offsets passed
Oracle Cluster Time Synchronization Services check passed
Post-check for node addition was successful.</code>
<b>Do following checks on the new node.</b>
[grid@rac3 ~]$ crsctl check crs
<code>CRS-4638: Oracle High Availability Services is online
CRS-4537: Cluster Ready Services is online
CRS-4529: Cluster Synchronization Services is online
CRS-4533: Event Manager is online</code>
[grid@rac3 ~]$ crs_stat -t -v
<code>Name Type R/RA F/FT Target State Host
----------------------------------------------------------------------
ora.CRS.dg ora....up.type 0/5 0/ ONLINE ONLINE rac1
ora.DATA.dg ora....up.type 0/5 0/ ONLINE ONLINE rac1
ora.FRA.dg ora....up.type 0/5 0/ ONLINE ONLINE rac1
ora....ER.lsnr ora....er.type 0/5 0/ ONLINE ONLINE rac1
ora....N1.lsnr ora....er.type 0/5 0/0 ONLINE ONLINE rac2
ora....N2.lsnr ora....er.type 0/5 0/0 ONLINE ONLINE rac3
ora....N3.lsnr ora....er.type 0/5 0/0 ONLINE ONLINE rac1
ora.asm ora.asm.type 0/5 0/ ONLINE ONLINE rac1
ora.dell.db ora....se.type 0/2 0/1 ONLINE ONLINE rac1
ora.eons ora.eons.type 0/3 0/ ONLINE ONLINE rac1
ora.gsd ora.gsd.type 0/5 0/ OFFLINE OFFLINE
ora....network ora....rk.type 0/5 0/ ONLINE ONLINE rac1
ora.oc4j ora.oc4j.type 0/5 0/0 OFFLINE OFFLINE
ora.ons ora.ons.type 0/3 0/ ONLINE ONLINE rac1
ora....SM1.asm application 0/5 0/0 ONLINE ONLINE rac1
ora....C1.lsnr application 0/5 0/0 ONLINE ONLINE rac1
ora.rac1.gsd application 0/5 0/0 OFFLINE OFFLINE
ora.rac1.ons application 0/3 0/0 ONLINE ONLINE rac1
ora.rac1.vip ora....t1.type 0/0 0/0 ONLINE ONLINE rac1
ora....SM2.asm application 0/5 0/0 ONLINE ONLINE rac2
ora....C2.lsnr application 0/5 0/0 ONLINE ONLINE rac2
ora.rac2.gsd application 0/5 0/0 OFFLINE OFFLINE
ora.rac2.ons application 0/3 0/0 ONLINE ONLINE rac2
ora.rac2.vip ora....t1.type 0/0 0/0 ONLINE ONLINE rac2
ora....SM3.asm application 0/5 0/0 ONLINE ONLINE rac3
ora....C3.lsnr application 0/5 0/0 ONLINE ONLINE rac3
ora.rac3.gsd application 0/5 0/0 OFFLINE OFFLINE
ora.rac3.ons application 0/3 0/0 ONLINE ONLINE rac3
ora.rac3.vip ora....t1.type 0/0 0/0 ONLINE ONLINE rac3
ora....ry.acfs ora....fs.type 0/5 0/ ONLINE ONLINE rac1
ora.scan1.vip ora....ip.type 0/0 0/0 ONLINE ONLINE rac2
ora.scan2.vip ora....ip.type 0/0 0/0 ONLINE ONLINE rac3
ora.scan3.vip ora....ip.type 0/0 0/0 ONLINE ONLINE rac1</code>
[grid@rac3 ~]$ olsnodes -n
<code>rac1 1
rac2 2
rac3 3</code>
[grid@rac3 ~]$ ps -ef | grep lsnr | grep -v 'grep' | grep -v 'ocfs' | awk '{print $9}'
<code>LISTENER_SCAN2
LISTENER</code>
[grid@rac3 ~]$ srvctl status asm -a
<code>ASM is running on rac1,rac2,rac3
ASM is enabled.</code>
[grid@rac3 ~]$ ocrcheck
<code>Status of Oracle Cluster Registry is as follows :
Version : 3
Total space (kbytes) : 262120
Used space (kbytes) : 2948
Available space (kbytes) : 259172
ID : 576957443
Device/File Name : +CRS
Device/File integrity check succeeded
Device/File not configured
Device/File not configured
Device/File not configured
Device/File not configured
Cluster registry integrity check succeeded
Logical corruption check bypassed due to non-privileged user</code>
[grid@rac3 ~]$ crsctl query css votedisk
<code>## STATE File Universal Id File Name Disk group
-- ----- ----------------- --------- ---------
1. ONLINE 57e4ff96d9b64fdabfc724b7b1e7d4f9 (ORCL:VOL1) [CRS]
2. ONLINE 7869d5fd844c4f0bbf1a022259193dfc (ORCL:VOL2) [CRS]
3. ONLINE 73f4a7ede9bf4f4dbfb86e1b24167a3a (ORCL:VOL7) [CRS]
Located 3 voting disk(s).</code>
<b>Extend oracle software to the new node from RAC1.</b>
[root@rac1 ~]# su - oracle
[oracle@rac1 ~]$ cd /u01/app/oracle/product/11.2.0/dbhome_1/oui/bin/
[oracle@rac1 bin]$ ./addNode.sh -silent "CLUSTER_NEW_NODES={rac3}"
<code>Starting Oracle Universal Installer...
Checking swap space: must be greater than 500 MB. Actual 3903 MB Passed
Oracle Universal Installer, Version 11.2.0.1.0 Production
Copyright (C) 1999, 2009, Oracle. All rights reserved.
Performing tests to see whether nodes rac2,rac3 are available
............................................................... 100% Done.
..
-----------------------------------------------------------------------------
Cluster Node Addition Summary
Global Settings
Source: /u01/app/oracle/product/11.2.0/dbhome_1
New Nodes
Space Requirements
New Nodes
rac3
/: Required 3.89GB : Available 13.09GB
Installed Products
Product Names
Oracle Database 11g 11.2.0.1.0
Sun JDK 1.5.0.17.0
Installer SDK Component 11.2.0.1.0
Oracle One-Off Patch Installer 11.2.0.0.2
Oracle Universal Installer 11.2.0.1.0
Oracle Configuration Manager Deconfiguration 10.3.1.0.0
Oracle DBCA Deconfiguration 11.2.0.1.0
Oracle RAC Deconfiguration 11.2.0.1.0
Oracle Database Deconfiguration 11.2.0.1.0
Oracle Configuration Manager 10.3.1.1.0
Oracle ODBC Driverfor Instant Client 11.2.0.1.0
LDAP Required Support Files 11.2.0.1.0
SSL Required Support Files for InstantClient 11.2.0.1.0
Bali Share 1.1.18.0.0
Oracle Extended Windowing Toolkit 3.4.47.0.0
Oracle JFC Extended Windowing Toolkit 4.2.36.0.0
Oracle Real Application Testing 11.2.0.1.0
Oracle Database Vault J2EE Application 11.2.0.1.0
Oracle Label Security 11.2.0.1.0
Oracle Data Mining RDBMS Files 11.2.0.1.0
Oracle OLAP RDBMS Files 11.2.0.1.0
Oracle OLAP API 11.2.0.1.0
Platform Required Support Files 11.2.0.1.0
Oracle Database Vault option 11.2.0.1.0
Oracle RAC Required Support Files-HAS 11.2.0.1.0
SQL*Plus Required Support Files 11.2.0.1.0
Oracle Display Fonts 9.0.2.0.0
Oracle Ice Browser 5.2.3.6.0
Oracle JDBC Server Support Package 11.2.0.1.0
Oracle SQL Developer 11.2.0.1.0
Oracle Application Express 11.2.0.1.0
XDK Required Support Files 11.2.0.1.0
RDBMS Required Support Files for Instant Client 11.2.0.1.0
SQLJ Runtime 11.2.0.1.0
Database Workspace Manager 11.2.0.1.0
RDBMS Required Support Files 11.2.0.1.0
Oracle Globalization Support 11.2.0.1.0
Exadata Storage Server 11.2.0.1.0
Provisioning Advisor Framework 10.2.0.4.2
Enterprise Manager Database Plugin -- Repository Support 11.2.0.1.0
Enterprise Manager Repository Core Files 10.2.0.4.2
Enterprise Manager Database Plugin -- Agent Support 11.2.0.1.0
Enterprise Manager Grid Control Core Files 10.2.0.4.2
Enterprise Manager Common Core Files 10.2.0.4.2
Enterprise Manager Agent Core Files 10.2.0.4.2
Agent Required Support Files 10.2.0.4.2
regexp 2.1.9.0.0
Parser Generator Required Support Files 11.2.0.1.0
Oracle 11g Warehouse Builder Required Files 11.2.0.1.0
Oracle Notification Service (eONS) 11.2.0.1.0
Oracle Text Required Support Files 11.2.0.1.0
Precompiler Required Support Files 11.2.0.1.0
Oracle Database 11g Multimedia Files 11.2.0.1.0
Oracle Multimedia Java Advanced Imaging 11.2.0.1.0
Oracle Multimedia Annotator 11.2.0.1.0
Oracle JDBC/OCI Instant Client 11.2.0.1.0
Oracle Multimedia Locator RDBMS Files 11.2.0.1.0
Oracle Core Required Support Files 11.2.0.1.0
Oracle Help For Java 4.2.9.0.0
Sample Schema Data 11.2.0.1.0
Oracle Starter Database 11.2.0.1.0
Oracle Message Gateway Common Files 11.2.0.1.0
Oracle XML Query 11.2.0.1.0
XML Parser for Oracle JVM 11.2.0.1.0
Expat libraries 2.0.1.0.1
Installation Plugin Files 11.2.0.1.0
Enterprise Manager Common Files 10.2.0.4.2
Perl Modules 5.10.0.0.1
Deinstallation Tool 11.2.0.1.0
Oracle Quality of Service Management (Client) 11.2.0.1.0
Perl Interpreter 5.10.0.0.1
JAccelerator (COMPANION) 11.2.0.1.0
Oracle Containers for Java 11.2.0.1.0
Oracle Code Editor 1.2.1.0.0I
Oracle Net Required Support Files 11.2.0.1.0
Secure Socket Layer 11.2.0.1.0
Oracle Universal Connection Pool 11.2.0.1.0
Oracle JDBC/THIN Interfaces 11.2.0.1.0
Oracle Multimedia Client Option 11.2.0.1.0
Oracle Java Client 11.2.0.1.0
Character Set Migration Utility 11.2.0.1.0
Oracle Locale Builder 11.2.0.1.0
PL/SQL Embedded Gateway 11.2.0.1.0
OLAP SQL Scripts 11.2.0.1.0
Database SQL Scripts 11.2.0.1.0
Oracle Globalization Support 11.2.0.1.0
Required Support Files 11.2.0.1.0
SQL*Plus Files for Instant Client 11.2.0.1.0
Oracle ODBC Driver 11.2.0.1.0
Oracle Database User Interface 2.2.13.0.0
Oracle Notification Service 11.2.0.0.0
Enterprise Manager Minimal Integration 11.2.0.1.0
XML Parser for Java 11.2.0.1.0
Oracle Security Developer Tools 11.2.0.1.0
Oracle Wallet Manager 11.2.0.1.0
Cluster Verification Utility Common Files 11.2.0.1.0
Oracle Clusterware RDBMS Files 11.2.0.1.0
Oracle UIX 2.2.24.5.0
Enterprise Manager plugin Common Files 11.2.0.1.0
HAS Common Files 11.2.0.1.0
Precompiler Common Files 11.2.0.1.0
Installation Common Files 11.2.0.1.0
Oracle Help for the Web 2.0.14.0.0
Oracle LDAP administration 11.2.0.1.0
Buildtools Common Files 11.2.0.1.0
Assistant Common Files 11.2.0.1.0
Oracle Recovery Manager 11.2.0.1.0
PL/SQL 11.2.0.1.0
Generic Connectivity Common Files 11.2.0.1.0
Oracle Database Gateway for ODBC 11.2.0.1.0
Oracle Programmer 11.2.0.1.0
Oracle Database Utilities 11.2.0.1.0
Enterprise Manager Agent 10.2.0.4.2
Oracle Netca Client 11.2.0.1.0
SQL*Plus 11.2.0.1.0
Oracle Call Interface (OCI) 11.2.0.1.0
Oracle Multimedia Locator 11.2.0.1.0
Oracle Multimedia 11.2.0.1.0
Oracle Net 11.2.0.1.0
Database Configuration and Upgrade Assistants 11.2.0.1.0
Oracle XML Development Kit 11.2.0.1.0
Oracle JVM 11.2.0.1.0
Oracle Advanced Security 11.2.0.1.0
Oracle Internet Directory Client 11.2.0.1.0
HAS Files for DB 11.2.0.1.0
Oracle Enterprise Manager Console DB 11.2.0.1.0
Oracle Net Listener 11.2.0.1.0
Oracle Text 11.2.0.1.0
Oracle Net Services 11.2.0.1.0
Oracle Database 11g 11.2.0.1.0
Oracle OLAP 11.2.0.1.0
Oracle Spatial 11.2.0.1.0
Oracle Partitioning 11.2.0.1.0
Enterprise Edition Options 11.2.0.1.0
-----------------------------------------------------------------------------
Instantiating scripts for add node (Sunday, December 2, 2012 3:41:44 PM IST)
. 1% Done.
Instantiation of add node scripts complete
Copying to remote nodes (Sunday, December 2, 2012 3:42:00 PM IST)
...............................................................................................96% Done.
Home copied to new nodes
Saving inventory on nodes (Sunday, December 2, 2012 3:59:32 PM IST)
. 100% Done.
Save inventory complete
WARNING:
The following configuration scripts need to be executed as the "root" user in each cluster node.
/u01/app/oracle/product/11.2.0/dbhome_1/root.sh #On nodes rac3
To execute the configuration scripts:
1. Open a terminal window
2. Log in as "root"
3. Run the scripts in each cluster node
The Cluster Node Addition of /u01/app/oracle/product/11.2.0/dbhome_1 was successful.
Please check '/tmp/silentInstall.log' for more details.</code>
<b>Run the script as root on new node.</b>
[root@rac3 ~]# /u01/app/oracle/product/11.2.0/dbhome_1/root.sh
<code>Running Oracle 11g root.sh script...
The following environment variables are set as:
ORACLE_OWNER= oracle
ORACLE_HOME= /u01/app/oracle/product/11.2.0/dbhome_1
Enter the full pathname of the local bin directory: [/usr/local/bin]:
The file "dbhome" already exists in /usr/local/bin. Overwrite it? (y/n)
[n]: n
The file "oraenv" already exists in /usr/local/bin. Overwrite it? (y/n)
[n]: n
The file "coraenv" already exists in /usr/local/bin. Overwrite it? (y/n)
[n]: n
Entries will be added to the /etc/oratab file as needed by
Database Configuration Assistant when a database is created
Finished running generic part of root.sh script.
Now product-specific root actions will be performed.
Finished product-specific root actions.</code>
Since we are using job role separation (grid,oracle), The $ORACLE_HOME/bin/oracle binary
may not have proper group priviledges on the new node.
This may prevent the oracle software owner from accessing the ASM disks.
To resolve this problem do the following on the new node.
[root@rac3 ~]# su - grid
[grid@rac3 ~]$ cd $GRID_HOME/bin
[grid@rac3 bin]$ ls -l /u01/app/oracle/product/11.2.0/dbhome_1/bin/oracle
<code>-r-sr-s--x 1 oracle oinstall 173515905 Dec 2 15:55 /u01/app/oracle/product/11.2.0/dbhome_1/bin/oracle*</code>
[grid@rac3 bin]$ ./setasmgidwrap o=/u01/app/oracle/product/11.2.0/dbhome_1/bin/oracle
[grid@rac3 bin]$ ls -l /u01/app/oracle/product/11.2.0/dbhome_1/bin/oracle
<code>-r-sr-s--x 1 oracle asmadmin 173515905 Dec 2 15:55 /u01/app/oracle/product/11.2.0/dbhome_1/bin/oracle*</code>
<b>Add new instance to the cluster using DBCA from rac1.</b>
[root@rac1 ~]# xhost +
access control disabled, clients can connect from any host
[root@rac1 ~]# su - oracle
[oracle@rac1 ~]$ dbca
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEies0lc2nxY4aCRLM84ioMM5qVcIoQ3hc_vMjqGCAqCsjPXkWsoFrfuzDoTl_Z3aTIDuXAIY4N76lKN6pfSEIdyZDaH8S_x6sWBOoCopezN07WjL-i3gaJbHozgLqVacoqvJh0Nv3_-FZuh/s1600/nodeadd1.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="226" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEies0lc2nxY4aCRLM84ioMM5qVcIoQ3hc_vMjqGCAqCsjPXkWsoFrfuzDoTl_Z3aTIDuXAIY4N76lKN6pfSEIdyZDaH8S_x6sWBOoCopezN07WjL-i3gaJbHozgLqVacoqvJh0Nv3_-FZuh/s320/nodeadd1.png" width="320" /></a></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgOK5GqabD2D9h2OCpThr6ll8YwzcQ4yD9TfDVXPHss8v730L7Oa18JMCfw7uv4DawdeSwDcRCo2puqMjOEDbE2UNTIJYj5L1jCUfT09HjiY7RfFYiWv_Zh6l2G9CgtatKttlVsabAN5dr5/s1600/nodeadd2.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="225" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgOK5GqabD2D9h2OCpThr6ll8YwzcQ4yD9TfDVXPHss8v730L7Oa18JMCfw7uv4DawdeSwDcRCo2puqMjOEDbE2UNTIJYj5L1jCUfT09HjiY7RfFYiWv_Zh6l2G9CgtatKttlVsabAN5dr5/s320/nodeadd2.png" width="320" /></a></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjFG4Ic-FRDl7gSdKnMu1YdsM4elJLhmQ1JE9CsGkQeZr-PYmpZ0W5hcCgZ7ydUH_y9f9UQUp2yuOniLCT_0Dw5N6v9SwE3EePbed1ys5nzENHiv8AgxUYBBJmdicBWx6wJReej7JOj0m3x/s1600/nodeadd3.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="226" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjFG4Ic-FRDl7gSdKnMu1YdsM4elJLhmQ1JE9CsGkQeZr-PYmpZ0W5hcCgZ7ydUH_y9f9UQUp2yuOniLCT_0Dw5N6v9SwE3EePbed1ys5nzENHiv8AgxUYBBJmdicBWx6wJReej7JOj0m3x/s320/nodeadd3.png" width="320" /></a></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhsdW7xh-Yv5IFogR1X1GNcQRystOLZ3c0HhL5iKyVXK5ssPI4tviCBL8kQaPoNLp0abVmcQqab3dy4wyASts6mieRVVHPf_MtOJNWW1Eb505W9lw8T0JmQPytU3tiXWfYl1OclB96dsf3B/s1600/nodeadd4.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="226" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhsdW7xh-Yv5IFogR1X1GNcQRystOLZ3c0HhL5iKyVXK5ssPI4tviCBL8kQaPoNLp0abVmcQqab3dy4wyASts6mieRVVHPf_MtOJNWW1Eb505W9lw8T0JmQPytU3tiXWfYl1OclB96dsf3B/s320/nodeadd4.png" width="320" /></a></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiODlSYk11a5JmaGRlpRUVCLiAcYAj-3nQhj6VhMYNWEw_IHmzAYlvLsJeGCd6uaGcb5XS6Fsq4Shknsw2TbyVrWDGusSSDDycCgOi070VU9B6zS0nBVSRY0dybqLIO6uep1D2ZN6dJrJM0/s1600/nodeadd5.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="226" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiODlSYk11a5JmaGRlpRUVCLiAcYAj-3nQhj6VhMYNWEw_IHmzAYlvLsJeGCd6uaGcb5XS6Fsq4Shknsw2TbyVrWDGusSSDDycCgOi070VU9B6zS0nBVSRY0dybqLIO6uep1D2ZN6dJrJM0/s320/nodeadd5.png" width="320" /></a></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiSp-0Bx3qvix2x4GeYBSzURghyphenhyphen1hMFz5WO8ets3JTPQoJnOVZD8npyJDEBb_JH3MN1ZWFMeolRpnH4qeRWPkUW7OOfqAVIj857ouJ5YJ-xcmiW0K80t1L45UPHMIXCuwN0uJZrPVPcTfVS/s1600/nodeadd6.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="226" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiSp-0Bx3qvix2x4GeYBSzURghyphenhyphen1hMFz5WO8ets3JTPQoJnOVZD8npyJDEBb_JH3MN1ZWFMeolRpnH4qeRWPkUW7OOfqAVIj857ouJ5YJ-xcmiW0K80t1L45UPHMIXCuwN0uJZrPVPcTfVS/s320/nodeadd6.png" width="320" /></a></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEidPUjGpVhzlOKDXxL-gUUaZGx23q5njhLhpnk-rILqLkeaSNnVNuCOerg_WFopLsOGhlbB30icAjPFsmboSHmfWfGYUIhkzAGB_5Uc2PtgRMW_m-om6zQD6uMrOUKRzvSYPgx4NXwxDCon/s1600/nodeadd7.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="227" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEidPUjGpVhzlOKDXxL-gUUaZGx23q5njhLhpnk-rILqLkeaSNnVNuCOerg_WFopLsOGhlbB30icAjPFsmboSHmfWfGYUIhkzAGB_5Uc2PtgRMW_m-om6zQD6uMrOUKRzvSYPgx4NXwxDCon/s320/nodeadd7.png" width="320" /></a></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhKuu7IDPFWC1edw2u5vcIvQehgwwIZEA2l4WV5XEavvxINqq6SRh4p6RfvqiT4usrBPR8FDMTk3wA4wZycsOJeRF0bw972J6Qk41tv509CxKHwIaBdGV546LKW8JQhXYKHMyU_1obSUfey/s1600/nodeadd8.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="320" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhKuu7IDPFWC1edw2u5vcIvQehgwwIZEA2l4WV5XEavvxINqq6SRh4p6RfvqiT4usrBPR8FDMTk3wA4wZycsOJeRF0bw972J6Qk41tv509CxKHwIaBdGV546LKW8JQhXYKHMyU_1obSUfey/s320/nodeadd8.png" width="319" /></a></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhgIq_0u7v3weq0pKlO19W0efP1GJymiCqmxLM9QAn6QC5Gi3gwTNScs3BfWxAtiEfOtfbwsD899Q6cdL-Zz1OjDPQ8LqSzE3Z1ha-hPfeDWdaIJ4BenVhZK6FyIasmKl320hEAJO_PWmjn/s1600/nodeadd9.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="222" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhgIq_0u7v3weq0pKlO19W0efP1GJymiCqmxLM9QAn6QC5Gi3gwTNScs3BfWxAtiEfOtfbwsD899Q6cdL-Zz1OjDPQ8LqSzE3Z1ha-hPfeDWdaIJ4BenVhZK6FyIasmKl320hEAJO_PWmjn/s320/nodeadd9.png" width="320" /></a></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEicMFMLU2TiowS43Q6bnoFUmyT593FqvmNwewKF_wt_yx2695y94eZ6e1vowkyk5St_uG0FjbG0zYDRX1uB6K0h5iZRK-F3Jx4NNy3JV-9q7qXFNrBv5wgVVV6MLJMQM39v44ug9NZoh1uO/s1600/nodeadd10.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="137" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEicMFMLU2TiowS43Q6bnoFUmyT593FqvmNwewKF_wt_yx2695y94eZ6e1vowkyk5St_uG0FjbG0zYDRX1uB6K0h5iZRK-F3Jx4NNy3JV-9q7qXFNrBv5wgVVV6MLJMQM39v44ug9NZoh1uO/s320/nodeadd10.png" width="320" /></a></div>
<b>Verify</b>
[oracle@rac1 ~]$ srvctl status database -d dell
<code>Instance dell1 is running on node rac1
Instance dell2 is running on node rac2
Instance dell3 is running on node rac3</code>
<b>copy tnsnames.ora file from rac1 to the new node.</b>
[root@rac1 ~]# su - oracle
[oracle@rac1 ~]$ cd $ORACLE_HOME/network/admin
[oracle@rac1 admin]$ scp tnsnames.ora rac3:$ORACLE_HOME/network/admin
tnsnames.ora 100% 347 0.3KB/s 00:00
References:<a href="http://www.idevelopment.info/data/Oracle/DBA_tips/Oracle11gRAC/CLUSTER_22.shtml">http://www.idevelopment.info/data/Oracle/DBA_tips/Oracle11gRAC/CLUSTER_22.shtml</a>
</pre>
</div>
<pre><oms home="home"><oms home="home"> </oms></oms></pre>
</div>
Ajay Singh Chandihttp://www.blogger.com/profile/05807334441165607306noreply@blogger.com0tag:blogger.com,1999:blog-9081789365774441839.post-19311546636353849332012-11-14T16:26:00.000+05:302013-01-17T23:19:04.095+05:30Oracle Goldengate 11g Initial Load Using Direct Load Method.<div dir="ltr" style="text-align: left;" trbidi="on">
<pre>This article focuses on initial synchronization of source and
target databases by using Golden Gate initial direct load method for the
purpose of zero downtime DML replication.
<b>Prerequisites:</b>
1) Target Tables should be empty. Otherwise you can get duplicate row errors.
2) Disable Foreign Key and Check Constraints. They can be enabled later on
when the initial load is complete.
3) (Optional) Remove indexes from target tables as they may slow down the
initial loading process.
4) To use handlecollisions function, each target table must have a
primary or unique key. If that's not possible use the KEYCOLS
option of the TABLE and MAP parameters. Even if that is not possible
then quiesce your database for the duration of intial load.
<a name='more'></a>
<b>Schema to be replicated "SCOTT"</b>
<b>1) Install GoldenGate on both the machines.
</b>
<b>2) Disable Foreign key and check constraints and triggers on target tables.</b>
SQL> select CONSTRAINT_NAME,CONSTRAINT_TYPE,TABLE_NAME,status from dba_constraints where owner='SCOTT';
<code>CONSTRAINT_NAME C TABLE_NAME STATUS
------------------------------ - ------------------------------ --------
FK_DEPTNO R EMP ENABLED
PK_DEPT P DEPT ENABLED
PK_EMP P EMP ENABLED</code>
SQL> alter table emp disable constraint fk_deptno;
Table altered.
<b>3) Configure and start extract process on source.</b>
<b>4) configure and start Data Pump process on source.</b>
<b>5) Configure Initial load extract and replicat processes.</b>
<b><i>On Source</i></b>
GGSCI (canada.example.com) 15> edit param iniload1
extract iniload1
userid ggs_admin, password ggs_admin
rmthost newyork, mgrport 7980
rmttask replicat, group iniload2
table SCOTT.*;
#RMTTASK - Instructs the manager process on the target system to
#start the initial-load replicat process without manual intervention
#as a one time task.
GGSCI (canada.example.com) 18> add extract iniload1, sourceistable
EXTRACT added.
#<b>SOURCEISTABLE</b> - Tells the extract process to extract records directly
from the source tables rather than from the redo logs.
<b><i>On Target</i></b>
GGSCI (newyork.example.com) 5> edit param iniload2
replicat iniload2
userid ggs_admin, password ggs_admin
assumetargetdefs
map SCOTT.*, target SCOTT.*;
GGSCI (newyork.example.com) 9> add replicat iniload2, specialrun
REPLICAT added.
#<b>SpecialRun</b> - Runs as a onetime process with distinct beginning and end,
#which is different from continuous processing as it does not have a
#specific end point.
<b><i>On Source</i></b>
Start initial load extract process on source. The replicat process
on target will start automatically.
GGSCI (canada.example.com) 19> start extract iniload1
<code>Sending START request to MANAGER ...
EXTRACT INILOAD1 starting</code>
GGSCI (canada.example.com) 30> info extract iniload1
<code>EXTRACT INILOAD1 Last Started 2012-11-13 21:48 Status RUNNING
Checkpoint Lag Not Available
Log Read Checkpoint Table SCOTT.DEPT
2012-11-13 21:48:32 Record 1
Task SOURCEISTABLE</code>
GGSCI (canada.example.com) 31> view report iniload1
<code>2012-11-13 21:48:27 INFO OGG-01017 Wildcard resolution set to IMMEDIATE because SOURCEISTABLE is used.
***********************************************************************
Oracle GoldenGate Capture for Oracle
Version 11.1.1.1.2 OGGCORE_11.1.1.1.2_PLATFORMS_111004.2100
Linux, x86, 32bit (optimized), Oracle 11g on Oct 7 2011 15:32:42
Copyright (C) 1995, 2011, Oracle and/or its affiliates. All rights reserved.
Starting at 2012-11-13 21:48:27
***********************************************************************
Operating System Version:
Linux
Version #1 SMP Tue Aug 18 15:51:54 EDT 2009, Release 2.6.18-164.el5
Node: canada.example.com
Machine: i686
soft limit hard limit
Address Space Size : unlimited unlimited
Heap Size : unlimited unlimited
File Size : unlimited unlimited
CPU Time : unlimited unlimited
Process id: 7671
Description:
***********************************************************************
** Running with the following parameters **
***********************************************************************
extract iniload1
userid ggs_admin, password *********
rmthost newyork, mgrport 7980
rmttask replicat, group iniload2
table SCOTT.*;
TABLEWildcard resolved (entry SCOTT.*):
table SCOTT.BONUS;
2012-11-13 21:48:27 WARNING OGG-00869 No unique key is defined for table BONUS. All viable columns will b
e used to represent the key, but may not guarantee uniqueness. KEYCOLS may be used to define the key.
Using the following key columns for source table SCOTT.BONUS: ENAME, JOB, SAL, COMM.
TABLEWildcard resolved (entry SCOTT.*):
table SCOTT.DEPT;
Using the following key columns for source table SCOTT.DEPT: DEPTNO.
TABLEWildcard resolved (entry SCOTT.*):
table SCOTT.EMP;
Using the following key columns for source table SCOTT.EMP: EMPNO.
TABLEWildcard resolved (entry SCOTT.*):
table SCOTT.SALGRADE;
2012-11-13 21:48:27 WARNING OGG-00869 No unique key is defined for table SALGRADE. All viable columns wil
l be used to represent the key, but may not guarantee uniqueness. KEYCOLS may be used to define the key.
Using the following key columns for source table SCOTT.SALGRADE: GRADE, LOSAL, HISAL.
CACHEMGR virtual memory values (may have been adjusted)
CACHEBUFFERSIZE: 64K
CACHESIZE: 2G
CACHEBUFFERSIZE (soft max): 4M
CACHEPAGEOUTSIZE (normal): 4M
PROCESS VM AVAIL FROM OS (min): 2.87G
CACHESIZEMAX (strict force to disk): 2.64G
Database Version:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
Database Language and Character Set:
NLS_LANG environment variable specified has invalid format, default value will be used.
NLS_LANG environment variable not set, using default value AMERICAN_AMERICA.US7ASCII.
NLS_LANGUAGE = "AMERICAN"
NLS_TERRITORY = "AMERICA"
NLS_CHARACTERSET = "WE8MSWIN1252"
Warning: your NLS_LANG setting does not match database server language setting.
Please refer to user manual for more information.
Processing table SCOTT.BONUS
Processing table SCOTT.DEPT
Processing table SCOTT.EMP
Processing table SCOTT.SALGRADE
***********************************************************************
* ** Run Time Statistics ** *
***********************************************************************
Report at 2012-11-13 21:48:50 (activity since 2012-11-13 21:48:27)
Output to iniload2:
From Table SCOTT.DEPT:
# inserts: 4
# updates: 0
# deletes: 0
# discards: 0
From Table SCOTT.EMP:
# inserts: 14
# updates: 0
# deletes: 0
# discards: 0
From Table SCOTT.SALGRADE:
# inserts: 5
# updates: 0
# deletes: 0
# discards: 0
REDO Log Statistics
Bytes parsed 0
Bytes output 3473</code>
<b>
6) Enable all the constraints and triggers on the target.</b>
SQL> alter table emp enable constraint fk_deptno;
Table altered.
<b>7) Configure and start Replicat process on target.</b>
</pre>
</div>
Ajay Singh Chandihttp://www.blogger.com/profile/05807334441165607306noreply@blogger.com0tag:blogger.com,1999:blog-9081789365774441839.post-22574958177517270982012-11-11T18:08:00.000+05:302013-01-17T23:18:54.869+05:30Delete or Remove Extract & Replicat Processes (Golden Gate 11g).<div dir="ltr" style="text-align: left;" trbidi="on">
<pre>1)Stop extract/replicat.
kill extract group_name
kill replicat group_name
2)Delete extracat / replicat.
delete extract group_name
delete replicat group_name
3) Cleanup.
cleanup extract group_name
cleanup replicat group_name
</pre>
</div>
Ajay Singh Chandihttp://www.blogger.com/profile/05807334441165607306noreply@blogger.com0tag:blogger.com,1999:blog-9081789365774441839.post-20810246671569441372012-10-30T22:41:00.001+05:302013-01-17T23:18:46.254+05:30How to Tune Shared Pool Cache ?<div dir="ltr" style="text-align: left;" trbidi="on">
<pre>The one basic rule for tuning the shared pool is to
avoid or minimize parses of any kind, whether they be
soft parses or hard parses.
Parses can only be avoided by your developers by efficiently coding
the application.
As a DBA you can only minimize parses.
<a name='more'></a>
So, if you have lot have hard parses, Then you have to convert
them to soft one's.
And, if you have lot of soft parses then you have to minimize them.
This article focuses on tuning your shared pool using AWR or statspack reports
along with some init parameters.
<b>Load profile</b>
Load Profile
~~~~~~~~~~~~ Per Second Per Transaction
--------------- ---------------
DB Time(s): 0.8 2.5
DB CPU(s): 0.8 2.5
Redo size: 26,727.5 85,874.4
Logical reads: 14,003.8 44,993.8
Block changes: 53.2 171.0
Physical reads: 111.3 357.7
Physical writes: 11.2 36.0
User calls: 7.1 22.7
<b>Parses: 820.8 2,637.1
Hard parses: 743.5 2,388.9</b>
W/A MB processed: 828,525.2 2,662,020.7
Logons: 0.1 0.3
Executes: 1,025.7 3,295.4
Rollbacks: 0.0 0.0
Transactions: 0.3
The important values to check here are<b> "Parses & Hard Parses"</b>.
So, out of total 90% of the parses are hard parses (90.582% to be precise).
Possible reason for that is maybe cursors are not being shared.
The general rule is that
In an OLTP system number of hard parses should be few.
and
In and DWH and DSS environments the percentage of hard parses is normally higher.
<b>
Instance Efficiency</b>
Instance Efficiency Percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Nowait %: 100.00 Redo NoWait %: 100.00
Buffer Hit %: 99.21 In-memory Sort %: 100.00
<b>Library Hit %: 65.63</b> <b>Soft Parse %: 9.41</b>
<b>Execute to Parse %: 19.98 </b> Latch Hit %: 99.98
<b>Parse CPU to Parse Elapsd %: 0.01</b> % Non-Parse CPU: 22.67
<b>Indicators which mean that there is problem with shared pool.</b>
<b>Library Hit%</b> - Shows you in percentage the number of times a requested object
was found in the shared pool(ideal range (95 to 100)%).
<b>Soft Parse %</b> - Shows you in percentage the number of times a cursor was found
and reused (ideal range (95 to 100)%).
A low percentage means that cursors are not being reused.
<b>Execute to Parse%</b> - The execute to parse ratio should be very high in a ideal database (ideal range (95 to 100)%).
The execute to parse ratio is basically a measure between the number
Of times a sql is executed versus the number of times it is parsed.
The ratio will move higher as the number of executes go up, while
The number of parses either go down or remain the same.
The ratio will be close to zero if the number of executes and parses
Are almost equal.
The ratio will be negative executes are lower but the parses are higher.
<b>Parse CPU to Parse Elasped%</b> - Parse CPU means amount of CPU time used for parsing.
Parse Elapsed means amount of clock time used for parsing.
So, if for example if this percentage is high
say 87.88% then (1/.8788 = 1.13791534) that means for every 1 cpu second 1.13 seconds
of wall clock time has elapsed in order to do parses.
Low percentage in this ratio may be an indicator of latch problems.
Ideal Range is between 95% and 100%.
<b>Top 5 Timed Events</b>
Top 5 Timed Events Avg %Total
~~~~~~~~~~~~~~~~~~ wait Call
Event Waits Time (s) (ms) Time Wait Class
------------------------------ ------------ ----------- ------ ------ ----------
DB CPU 67 98.21
db file sequential read 8,371 0 0 0.52 User I/O
<b>latch: row cache objects 16 0 8 0.19 Concurrency
latch: shared pool 956 0 0 0.15 Concurrency</b>
log file sync 25 0 2 0.06 Commit
-------------------------------------------------------------
Both latch: row cach objects & latch: shared pool indicate that there is
some issue with shared pool (latch contention).
So, according to the given slide (0.19 + 0.15) 0.34% of the CPU time
is being eaten by latch contention.
"Row cache objects" is a latch which is used to protect the access to
data dictionary cache in the SGA.
A high value for this may indicate the following things.
1) There is excessive use of data dictionary information.
2) There is lot of hard parsing.
General solution to this problem is to increase the size of shared pool.
Latch: shared pool
Shared pool latch is used to protect memory allocation.
shared pool latch contention may indicate the following:
1) There is excessive hard parsing because the application is using literals
instead of bind variables.
2) Cursors are not being shared.
<b>Time Model Statistics</b>
Time related statistics presents the various operations which are consuming most of the database time.
Statistic Name Time (s) % of DB Time
------------------------------------------ ------------------ ------------
DB CPU 67.39 98.21
sql execute elasped time 61.13 89.09
<b>parse time elasped 56.36 82.14
hard parse elasped time 49.99 72.86
</b>PL/SQL execution elasped time 4.30 6.26
PL/SQL compilation elasped time 0.76 1.10
connection management call elasped time 0.26 0.38
hard parse (sharing criteria) elasped time 0.15 0.22
hard parse (bind mismatch) elasped time 0.09 0.13
repeated bind elasped time 0.06 0.08
sequence load elasped time 0.03 0.04
DB time 68.62
background elasped time 1.50
background cpu time 0.12
-------------------------------------------------------------
According to oracle docs
<b>Parse time elapsed</b> - Amount of elapsed time spent parsing SQL statements. It includes both soft and
hard parse time.
<b>
Hard parse elapsed time</b> - Amount of elapsed time spent hard parsing SQL statements.
So, according to the slide ((72.86*100)/82.14) 88.70% of DB time is spent on hard parses
which is not good.
<b>Library Cache Activity.</b>
Namespace Get Requests Pct Miss Pin Requests Pct Miss Reloads Invalidations
---------- ------------ -------- ------------ -------- ------- -------------
BODY 26 30.77 354 6.50 15 0
CLUSTER 576 1.04 322 1.86 0 0
INDEX 35,320 0.07 53,320 0.07 0 0
<b>SQL AREA 130,580 97.43 225,838 85.78 705 479 </b>
TABLE/PROCEDURE 405,501 0.35 313,834 1.31 908 0
TRIGGER 90 15.56 100 14.00 0 0
All the compiled cursors are stored in SQL Area.
High Pct Miss can indicate the following things.
1) Cursors ar not being used.
2) Cursors are being aged out or reloaded frequently because of low memory.
The number of reloads should not be more than 1% of the number of pins.
In our case it is ((705*100)/225838) 0.31%.
The reloads to pin ration can be in excess of 1% in the following scenarios.
1) Shared parsed areas have been aged-out because of lack of space. Main solution
to this problem is to increase the shared pool size.
2) Shared parsed areas are invalidated. Possible solution for this is to do
house keeping service like index creation & gathering statistics when there is
low database activity.
So, in order to tune shared pool we have to avoid hard parses.
There are three main methods to do it.
1) Make sure your shared pool is sufficiently sized.
2) Tell your developers to use bind variables instead of literals.
3) The third method is to use cursor_sharing only if the application
has already been developed and does not use bind variables.
This method should be used as a last resort.
<b>Sharing your Cursors.</b>
Cursors can be shared by using the init parameter "cursor_sharing".
It can be set to three values.
<b>1) EXACT(default)</b> - cursors can be shared only when the sql statements text are exactly similar.
<b>2) Similar(Deprecated in 11g)</b> - cursors are shared when sql statements are identical.
The execution plan can be different depending on the literal value.
<b>3) FORCE</b> - SQL statements that are similar will share cursors and their is going
to be only one execution plan for all the sql statements.
<b>Recommendations</b>
1) cursor_sharing should be set to "EXACT" for DSS environments if complex
queries are being used.
2) cursor_sharing should be set to "FORCE" for OLTP environments if bind
variables are not being used.
<b>Adaptive Cursor sharing (11g)</b>
It is always recommended to use bind variables while developing applications
in order to gain performance benefits.
But sometimes instead of improving, The performance is degraded because of a combination of
bind peeking and skewness of data in certain columns.
In order to solve this problem oracle 11g has come up with a new feature
called "adaptive cursor sharing".
For this to work properly histograms should be created on columns where the
skewness of data is high. They are collected automatically in 11g.
Adaptive cursor sharing works by observing the execution plans of statements across
various executions. If it finds any suboptimal plans, then it allows the bind variables
to use different execution plans for the same statement in order to improve performance.
Minimizing overhead of soft parses.
<b>SESSION_CACHED_CURSORS</b>
The overhead on soft parses and aging out of cursors can be avoided by using the parameter "session_cached_cursors".
This works by keeping a cache of the closed cursors in the session memory.
So when a query is executed, The session searches the session cache first and if
the cursor is found, the soft parse is avoided.
<b>NOTE:</b> Even if the cursor is found in the session cache, It will still require
validation. It does that by validating that the opened cursor points to the right sql statement
in the shared pool. So, in essence its more like a "softer" soft parse.
<b>SHARED_POOL_RESERVED_SIZE</b>
The SHARED_POOL_RESERVED_SIZE parameter specifies the shared pool space that is reserved for large contiguous
requests for shared pool memory. This static parameter should be set high enough to avoid performance
degradation in the shared pool from situations where pool fragmentation forces Oracle to search for
free chunks of unused pool to satisfy the current request.
Ideally, this parameter should be large enough to satisfy any request scanning for memory on the reserved list
without flushing objects from the shared pool.
The default value is 5% of the shared pool size, while the maximum value is 50% of the shared pool size.
For interMedia applications, a value at or close to the maximum can provide performance benefits.
Recommended for databases with large chunks of data or LOB objects(audio, video, images).
<b>SQL query result cache (11g).</b>
SQL query result cache enables the database to store the query result sets in the shared pool.
In brief it stores the result of a query for future reuse.
This cache can be used by multiple statements from the same session as well as
by multiple sessions.
For example if the first session executes, after fething the data from the database it caches the result
in the SQL query result.
So, if any other instance executes the exact query, the query result will be fetched
from the SQL query result caches instead of the datafiles.
<b>SQL query result cache in RAC.</b>
Each RAC node has it's own private SQL query result cache.
One node cannot share the SQL query result cache of another node.
<b>Initialization parameters.</b>
<b>RESULT_CACHE_MODE</b>
<b>MANUAL:</b> You have to specify the RESULT_CACHE hint in the SQL statement in
order to store result in cache i.e <b>"select /*+ RESULT_CACHE */ dept_id, emp_id, sal from employees;"</b>.
<b>FORCE:</b> All the result sets are stored in the cache by default.
if with setting you do not want a particular query to use this feature then
mention the NO_RESULT_CACHE hint i.e <b>"select /*+ NO_RESULT_CACHE */ dept_id, emp_id, sal from employees".</b>
<b>RESULT_CACHE_MAX_SIZE</b>
This parameter defines the memory allocation of the result cache.
Value of "0" means it is disabled.
Maxlimit is 75% of shared pool.
Default value is derived as following.
0.25% of memory_target or
0.5% of sga_target or
1% of shared_pool_size.
<b>RESULT_CACHE_MAX_RESULT</b>
Defines the max memory allocation for a single result.
Default is 5% of RESULT_CACHE_MAX_SIZE
<b>DBMS_RESULT_CACHE package.</b>
To check the status of the cache.
SQL> select dbms_result_cache.status from dual;
<code>STATUS
--------------------------------------------------------------------------------
ENABLED</code>
To check the cache memory usage.
SQL> set serveroutput on
SQL> exec dbms_result_cache.memory_report(detailed => true);
<code>R e s u l t C a c h e M e m o r y R e p o r t
[Parameters]
Block Size = 1K bytes
Maximum Cache Size = 672K bytes (672 blocks)
Maximum Result Size = 33K bytes (33 blocks)
[Memory]
Total Memory = 107836 bytes [0.135% of the Shared Pool]
... Fixed Memory = 9440 bytes [0.012% of the Shared Pool]
....... Memory Mgr = 124 bytes
....... Bloom Fltr = 2K bytes
....... Cache Mgr = 4416 bytes
....... State Objs = 2852 bytes
... Dynamic Memory = 98396 bytes [0.123% of the Shared Pool]
....... Overhead = 65628 bytes
........... Hash Table = 32K bytes (4K buckets)
........... Chunk Ptrs = 12K bytes (3K slots)
........... Chunk Maps = 12K bytes
........... Miscellaneous = 8284 bytes
....... Cache Memory = 32K bytes (32 blocks)
........... Unused Memory = 23 blocks
........... Used Memory = 9 blocks
............... Dependencies = 3 blocks (3 count)
............... Results = 6 blocks
................... SQL = 6 blocks (6 count)
PL/SQL procedure successfully completed.</code>
To remove the contents from result cache.
SQL> execute dbms_result_cache.flush;
PL/SQL procedure successfully completed.
<b>NOTE:</b> The contents from the query result cache will only be removed
if the cache is not in use. To purge successfully set "RESULT_CACHE_MAX_SIZE=0".
In order to reactivate the previous parameter you will have to restart
the instance after giving it a non zero value.
<b>Things to consider for Query Result Cache.</b>
<b>Result cache is disabled for queries containing:</b>
1) Temporary or dictionary tables
2) Nondeterministic PL/SQL functions.
3) CURRVAL and NEXTVAL(sequences).
4) SQL functions i.e current_date,sysdate etc.
Flashback queries can also be cached.
Result cache grows until it reaches its maximum size.
Objects or results in result cache are aged out based on the LRU algorithm.
<b>References:</b>
Oracle documentation.</pre>
<pre><span style="color: white;">Tuning shared pool cache</span>
</pre>
</div>
Ajay Singh Chandihttp://www.blogger.com/profile/05807334441165607306noreply@blogger.com0