1.) Find the SQL_ID from v$session.
select sid,serial#,sql_id from v$session where username='THE USER U ARE USING TO RUN THE SQL';
In our case it is d6w2vs1p33qrn.
2.) Now create the tuning task:
set serveroutput on
set long 10000 longchunksize 5000
DECLARE
my_task_name VARCHAR2(30);
BEGIN
my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
sql_id => 'd6w2vs1p33qrn',
scope => 'COMPREHENSIVE',
time_limit => 3000,
task_name => 'd6w2vs1p33qrn_tune',
description => 'Task to tune a query');
END;
/
Errors:DECLARE
*
ERROR at line 1:
ORA-13616: The current user SCOTT has not been granted the ADVISOR privilege.
ORA-06512: at "SYS.DBMS_SQLTUNE", line 791
ORA-06512: at line 4
3.) Grant advisor privilege to the user.
4.) Now execute the sql tune task:
exec dbms_sqltune.execute_tuning_task('d6w2vs1p33qrn_tune');
This is take sometime to run.
5.) Now check what the task has to say:
set long 20000
set longchunksize 20000
set linesize 400
select dbms_sqltune.report_tuning_task('d6w2vs1p33qrn_tune') from dual;
DBMS_SQLTUNE.REPORT_TUNING_TASK('D6W2VS1P33QRN_TUNE')
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name : d6w2vs1p33qrn_tune
Tuning Task Owner : SCOTT
Scope : COMPREHENSIVE
Time Limit(seconds) : 3000
Completion Status : COMPLETED
Started at : 09/07/2008 23:51:33
Completed at : 09/08/2008 00:08:44
Number of Statistic Findings : 7
Number of SQL Profile Findings : 1
-------------------------------------------------------------------------------
Schema Name: SCOTT
SQL ID : d6w2vs1p33qrn
SQL Text : select /*+ ordered INDEX(A,IDX_MV2_ACCT4) PARALLEL(a,2)
INDEX(C,IDX_TAB29) parallel(C,10) INDEX(U,IDX_TAB39)
PARALLEL(U,10) */
C.AD_GRP_ID,
C.ACCT_ID,
C.CRTV_ID,
C.SS_WEIGHT,
C.SS_RANK,
C.SS_ROTATION_FLG,
C.CM_WEIGHT,
C.CM_RANK,
C.CM_ROTATION_FLG,
C.TITLE,
C.DESCR,
C.SHORT_DESCR,
C.DISPLAY_URL,
C.CRTV_TMPLT_ID,
C.EXPLODED_FLG,
C.STATUS,
C.EDIT_STATUS,
C.LANG_ID C_LANG_ID,
C.CRTV_RATING,
pkg_distro_utils.cast_Unsigned(U.URL_ID) as URL_ID,
U.LANG_ID U_LANG_ID,
U.URL_RATING,
A.MARKET_ID,
pkg_distro_utils.get_unixtimestamp(C.LAST_UPD) as LAST_UPD
from am.TAB1 A
inner join TAB2 C on C.ACCT_ID = A.ACCT_ID
inner join TAB3 U
on U.ACCT_ID = C.ACCT_ID and U.URL_ID = C.URL_ID
and U.POD_ID = C.POD_ID --Ticket# 81628
where C.status <> 3
order by C.AD_GRP_ID
-------------------------------------------------------------------------------
FINDINGS SECTION (8 findings)
-------------------------------------------------------------------------------
1- Statistics Finding
---------------------
Optimizer statistics for index "SCOTT"."IDX_TAB39" are stale.
Recommendation
--------------
- Consider collecting optimizer statistics for this index.
execute dbms_stats.gather_index_stats(ownname => 'SCOTT', indname =>
'IDX_TAB39', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE);
Rationale
---------
The optimizer requires up-to-date statistics for the index in order to
select a good execution plan.
2- Statistics Finding
---------------------
Optimizer statistics for index "SCOTT"."IDX_TAB32" are stale.
Recommendation
--------------
- Consider collecting optimizer statistics for this index.
execute dbms_stats.gather_index_stats(ownname => 'SCOTT', indname =>
'IDX_TAB32', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE);
Rationale
---------
The optimizer requires up-to-date statistics for the index in order to
select a good execution plan.
3- Statistics Finding
---------------------
Optimizer statistics for index "SCOTT"."IDX_TAB35" are stale.
Recommendation
--------------
- Consider collecting optimizer statistics for this index.
execute dbms_stats.gather_index_stats(ownname => 'SCOTT', indname =>
'IDX_TAB35', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE);
Rationale
---------
The optimizer requires up-to-date statistics for the index in order to
select a good execution plan.
4- Statistics Finding
---------------------
Optimizer statistics for table "SCOTT"."TAB3" and its indices are stale.
Recommendation
--------------
- Consider collecting optimizer statistics for this table.
execute dbms_stats.gather_table_stats(ownname => 'SCOTT', tabname =>
'TAB3', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
method_opt => 'FOR ALL COLUMNS SIZE AUTO');
Rationale
---------
The optimizer requires up-to-date statistics for the table in order to
select a good execution plan.
5- Statistics Finding
---------------------
Optimizer statistics for index "SCOTT"."IDX_TAB27" are stale.
Recommendation
--------------
- Consider collecting optimizer statistics for this index.
execute dbms_stats.gather_index_stats(ownname => 'SCOTT', indname =>
'IDX_TAB27', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE);
Rationale
---------
The optimizer requires up-to-date statistics for the index in order to
select a good execution plan.
6- Statistics Finding
---------------------
Optimizer statistics for index "SCOTT"."IDX_TAB21" are stale.
Recommendation
--------------
- Consider collecting optimizer statistics for this index.
execute dbms_stats.gather_index_stats(ownname => 'SCOTT', indname =>
'IDX_TAB21', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE);
Rationale
---------
The optimizer requires up-to-date statistics for the index in order to
select a good execution plan.
7- Statistics Finding
---------------------
Optimizer statistics for table "SCOTT"."TAB2" and its indices are stale.
Recommendation
--------------
- Consider collecting optimizer statistics for this table.
execute dbms_stats.gather_table_stats(ownname => 'SCOTT', tabname =>
'TAB2', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
method_opt => 'FOR ALL COLUMNS SIZE AUTO');
Rationale
---------
The optimizer requires up-to-date statistics for the table in order to
select a good execution plan.
8- SQL Profile Finding (see explain plans section below)
--------------------------------------------------------
A potentially better execution plan was found for this statement.
Recommendation (estimated benefit: 60.25%)
------------------------------------------
- Consider accepting the recommended SQL profile.
execute dbms_sqltune.accept_sql_profile(task_name =>
'd6w2vs1p33qrn_tune', replace => TRUE);
-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------
1- Original With Adjusted Cost
------------------------------
Plan hash value: 3106191583
------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop | TQ |IN-OUT| PQ Distrib |
------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 92M| 26G| | 2812K (1)| 09:22:35 | | | | | |
| 1 | PX COORDINATOR FORCED SERIAL | | | | | | | | | | | |
| 2 | PX SEND QC (ORDER) | :TQ10004 | 92M| 26G| | 2812K (1)| 09:22:35 | | | Q1,04 | P->S | QC (ORDER) |
| 3 | SORT ORDER BY | | 92M| 26G| 56G| 2812K (1)| 09:22:35 | | | Q1,04 | PCWP | |
| 4 | PX RECEIVE | | 92M| 26G| | 2068K (1)| 06:53:37 | | | Q1,04 | PCWP | |
| 5 | PX SEND RANGE | :TQ10003 | 92M| 26G| | 2068K (1)| 06:53:37 | | | Q1,03 | P->P | RANGE |
|* 6 | HASH JOIN BUFFERED | | 92M| 26G| 3266M| 2068K (1)| 06:53:37 | | | Q1,03 | PCWP | |
| 7 | PX RECEIVE | | 751M| 20G| | 48486 (1)| 00:09:42 | | | Q1,03 | PCWP | |
| 8 | PX SEND HASH | :TQ10001 | 751M| 20G| | 48486 (1)| 00:09:42 | | | Q1,01 | P->P | HASH |
| 9 | PX PARTITION HASH ALL | | 751M| 20G| | 48486 (1)| 00:09:42 | 1 | 4 | Q1,01 | PCWC | |
| 10 | INDEX FULL SCAN | IDX_TAB39 | 751M| 20G| | 48486 (1)| 00:09:42 | 1 | 36 | Q1,01 | PCWP | |
| 11 | PX RECEIVE | | 92M| 23G| | 1717K (1)| 05:43:31 | | | Q1,03 | PCWP | |
| 12 | PX SEND HASH | :TQ10002 | 92M| 23G| | 1717K (1)| 05:43:31 | | | Q1,02 | P->P | HASH |
|* 13 | HASH JOIN | | 92M| 23G| | 1717K (1)| 05:43:31 | | | Q1,02 | PCWP | |
| 14 | BUFFER SORT | | | | | | | | | Q1,02 | PCWC | |
| 15 | PX RECEIVE | | 1915K| 20M| | 573 (1)| 00:00:07 | | | Q1,02 | PCWP | |
| 16 | PX SEND BROADCAST | :TQ10000 | 1915K| 20M| | 573 (1)| 00:00:07 | | | | S->P | BROADCAST |
| 17 | INDEX FULL SCAN | IDX_MV2_ACCT4 | 1915K| 20M| | 573 (1)| 00:00:07 | | | | | |
| 18 | PX PARTITION HASH ALL | | 92M| 22G| | 1716K (1)| 05:43:23 | 1 | 4 | Q1,02 | PCWC | |
|* 19 | TABLE ACCESS BY LOCAL INDEX ROWID| TAB2 | 92M| 22G| | 1716K (1)| 05:43:23 | 1 | 36 | Q1,02 | PCWP | |
| 20 | INDEX FULL SCAN | IDX_TAB29 | 139M| | | 15201 (1)| 00:03:03 | 1 | 36 | Q1,02 | PCWP | |
------------------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
6 - access("U"."ACCT_ID"="C"."ACCT_ID" AND "U"."URL_ID"="C"."URL_ID" AND "U"."POD_ID"="C"."POD_ID")
13 - access("C"."ACCT_ID"="A"."ACCT_ID")
19 - filter("C"."STATUS"<>3)
2- Original With Adjusted Cost
------------------------------
Plan hash value: 4016845619
------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop | TQ |IN-OUT| PQ Distrib |
------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 92M| 26G| | 2812K (1)| 09:22:35 | | | | | |
| 1 | PX COORDINATOR FORCED SERIAL | | | | | | | | | | | |
| 2 | PX SEND QC (ORDER) | :TQ10004 | 92M| 26G| | 2812K (1)| 09:22:35 | | | Q1,04 | P->S | QC (ORDER) |
| 3 | SORT ORDER BY | | 92M| 26G| 56G| 2812K (1)| 09:22:35 | | | Q1,04 | PCWP | |
| 4 | PX RECEIVE | | 92M| 26G| | 2068K (1)| 06:53:37 | | | Q1,04 | PCWP | |
| 5 | PX SEND RANGE | :TQ10003 | 92M| 26G| | 2068K (1)| 06:53:37 | | | Q1,03 | P->P | RANGE |
|* 6 | HASH JOIN BUFFERED | | 92M| 26G| 3266M| 2068K (1)| 06:53:37 | | | Q1,03 | PCWP | |
| 7 | PX RECEIVE | | 751M| 20G| | 48486 (1)| 00:09:42 | | | Q1,03 | PCWP | |
| 8 | PX SEND HASH | :TQ10001 | 751M| 20G| | 48486 (1)| 00:09:42 | | | Q1,01 | P->P | HASH |
| 9 | PX PARTITION HASH ALL | | 751M| 20G| | 48486 (1)| 00:09:42 | 1 | 4 | Q1,01 | PCWC | |
| 10 | INDEX FULL SCAN | IDX_TAB39 | 751M| 20G| | 48486 (1)| 00:09:42 | 1 | 36 | Q1,01 | PCWP | |
| 11 | PX RECEIVE | | 92M| 23G| | 1717K (1)| 05:43:31 | | | Q1,03 | PCWP | |
| 12 | PX SEND HASH | :TQ10002 | 92M| 23G| | 1717K (1)| 05:43:31 | | | Q1,02 | P->P | HASH |
|* 13 | HASH JOIN | | 92M| 23G| | 1717K (1)| 05:43:31 | | | Q1,02 | PCWP | |
| 14 | BUFFER SORT | | | | | | | | | Q1,02 | PCWC | |
| 15 | PX RECEIVE | | 1915K| 20M| | 573 (1)| 00:00:07 | | | Q1,02 | PCWP | |
| 16 | PX SEND BROADCAST | :TQ10000 | 1915K| 20M| | 573 (1)| 00:00:07 | | | | S->P | BROADCAST |
| 17 | INDEX FULL SCAN | IDX_MV2_ACCT4 | 1915K| 20M| | 573 (1)| 00:00:07 | | | | | |
| 18 | PX PARTITION HASH ALL | | 92M| 22G| | 1716K (1)| 05:43:23 | 1 | 4 | Q1,02 | PCWC | |
|* 19 | TABLE ACCESS BY LOCAL INDEX ROWID| TAB2 | 92M| 22G| | 1716K (1)| 05:43:23 | 1 | 36 | Q1,02 | PCWP | |
| 20 | INDEX FULL SCAN | IDX_TAB29 | 139M| | | 15201 (1)| 00:03:03 | 1 | 36 | Q1,02 | PCWP | |
------------------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
6 - access("U"."ACCT_ID"="C"."ACCT_ID" AND "U"."URL_ID"="C"."URL_ID" AND "U"."POD_ID"="C"."POD_ID")
13 - access("C"."ACCT_ID"="A"."ACCT_ID")
19 - filter("C"."STATUS"<>3)
3- Using SQL Profile
--------------------
Plan hash value: 1947416951
------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop | TQ |IN-OUT| PQ Distrib |
------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 92M| 26G| | 1117K (1)| 03:43:35 | | | | | |
| 1 | PX COORDINATOR FORCED SERIAL | | | | | | | | | | | |
| 2 | PX SEND QC (ORDER) | :TQ10004 | 92M| 26G| | 1117K (1)| 03:43:35 | | | Q1,04 | P->S | QC (ORDER) |
| 3 | SORT ORDER BY | | 92M| 26G| 54G| 1117K (1)| 03:43:35 | | | Q1,04 | PCWP | |
| 4 | PX RECEIVE | | 92M| 26G| | 447K (1)| 01:29:32 | | | Q1,04 | PCWP | |
| 5 | PX SEND RANGE | :TQ10003 | 92M| 26G| | 447K (1)| 01:29:32 | | | Q1,03 | P->P | RANGE |
|* 6 | HASH JOIN BUFFERED | | 92M| 26G| | 447K (1)| 01:29:32 | | | Q1,03 | PCWP | |
| 7 | PX RECEIVE | | 1915K| 20M| | 351 (2)| 00:00:05 | | | Q1,03 | PCWP | |
| 8 | PX SEND BROADCAST | :TQ10000 | 1915K| 20M| | 351 (2)| 00:00:05 | | | Q1,00 | P->P | BROADCAST |
| 9 | PX BLOCK ITERATOR | | 1915K| 20M| | 351 (2)| 00:00:05 | | | Q1,00 | PCWC | |
| 10 | INDEX FAST FULL SCAN | IDX_MV2_ACCT4 | 1915K| 20M| | 351 (2)| 00:00:05 | | | Q1,00 | PCWP | |
|* 11 | HASH JOIN | | 92M| 25G| 2432M| 447K (1)| 01:29:27 | | | Q1,03 | PCWP | |
| 12 | PX JOIN FILTER CREATE | :BF0000 | 92M| 22G| | 131K (1)| 00:26:18 | | | Q1,03 | PCWP | |
| 13 | PX RECEIVE | | 92M| 22G| | 131K (1)| 00:26:18 | | | Q1,03 | PCWP | |
| 14 | PX SEND HASH | :TQ10001 | 92M| 22G| | 131K (1)| 00:26:18 | | | Q1,01 | P->P | HASH |
| 15 | PX BLOCK ITERATOR | | 92M| 22G| | 131K (1)| 00:26:18 | 1 | 4 | Q1,01 | PCWC | |
|* 16 | TABLE ACCESS FULL | TAB2 | 92M| 22G| | 131K (1)| 00:26:18 | 1 | 36 | Q1,01 | PCWP | |
| 17 | PX RECEIVE | | 751M| 20G| | 48486 (1)| 00:09:42 | | | Q1,03 | PCWP | |
| 18 | PX SEND HASH | :TQ10002 | 751M| 20G| | 48486 (1)| 00:09:42 | | | Q1,02 | P->P | HASH |
| 19 | PX JOIN FILTER USE | :BF0000 | 751M| 20G| | 48486 (1)| 00:09:42 | | | Q1,02 | PCWP | |
| 20 | PX PARTITION HASH ALL| | 751M| 20G| | 48486 (1)| 00:09:42 | 1 | 4 | Q1,02 | PCWC | |
| 21 | INDEX FULL SCAN | IDX_TAB39 | 751M| 20G| | 48486 (1)| 00:09:42 | 1 | 36 | Q1,02 | PCWP | |
------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
6 - access("C"."ACCT_ID"="A"."ACCT_ID")
11 - access("U"."POD_ID"="C"."POD_ID" AND "U"."ACCT_ID"="C"."ACCT_ID" AND "U"."URL_ID"="C"."URL_ID")
16 - filter("C"."STATUS"<>3)
-------------------------------------------------------------------------------
6.) Drop the tuning task:
exec dbms_sqltune.DROP_TUNING_TASK('d6w2vs1p33qrn_tune');
Monday, September 8, 2008
dbms_sqltune usage example
Monday, August 11, 2008
PRKP-1029, CRS-0211 on srvctl modify/add service
What I was trying to do:
My requirement was to change the preferred node and available node for a service.
Issues:
srvctl modify service -d ORCL -s BULK -i node3,node4 -a node1,node2
PRKP-1029 : Failed to register the service ORCL_BULK_svc.
CRS-0211: Resource 'ora.ORCL.ORCL_BULK_svc.cs' has already been registered.
I tried the above command a couple of time, but gave the same error. I decided to stop the service and then try.
I decided to stop the service:
srvctl stop service -d ORCL -s BULK.
The service stopped without any issue.
But even after stopping the service, it gave the same error.
The I decided to remove the service and recreate it with the required configuration.
srvctl remove service -d ORCL -s BULK
Service got removed without any issue.
Now tried to add the service:
srvctl add service -d ORCL -s BULK -r node3,node4 -a node1,node2
PRKP-1029 : Failed to register the service ORCL_BULK_svc.
CRS-0211: Resource 'ora.ORCL.ORCL_BULK_svc.cs' has already been registered.
BAAAM, the error was not supposed to come, as I had already removed the service. Interestingly, I checked the DBA_SERVICES and still saw the entry of this service there. And also crs_stat showed this service.
Now I was in a soup as one of the most important application could not connect. Check with metalink/web the only thing I found was to "contact support". Turned to a couple of colleagues and got the suggestion to use crs_unregister. This is not a recommended thing to do, but I was in dire straits.
SO this is what we did:
crs_unregister ora.ORCL.ORCL_BULK_svc.cs
srvctl add service -d ORCL -s BULK -r node3,node4 -a node1,node2
PRKP-1029 : Failed to register the service ORCL_BULK_svc.
CRS-0211: Resource 'ora.ORCL.ORCL_BULK_svc.ORCL.srv' has already been registered.
crs_unregister ora.ORCL.ORCL_BULK_svc.ORCL.srv
srvctl add service -d ORCL -s BULK -r node3,node4 -a node1,node2
PRKP-1029 : Failed to register the service ORCL_BULK_svc.
CRS-0211: Resource 'ora.ORCL.ORCL_BULK_svc.cs' has already been registered.
We had already unregistered this one, but it gave the error for the same thing again. We ran the unregister once again:
crs_unregister ora.ORCL.ORCL_BULK_svc.cs
Looks like the sequence is to first remove the .srv and then the .cs:
crs_unregister ora.ORCL.ORCL_BULK_svc.ORCL.srv
CRS-0210: Could not find resource 'ora.ORCL.ORCL_BULK_svc.ORCL.srv'.
crs_unregister ora.ORCL.ORCL_BULK_svc.cs
CRS-0210: Could not find resource 'ora.ORCL.ORCL_BULK_svc.cs'.
Finally:
srvctl add service -d ORCL -s BULK -r node3,node4 -a node1,node2
and now it worked. And I started the service.
Details on crs_unregister from Oracle:
The crs_unregister command removes the registration information of Oracle Clusterware resources from the binary Oracle Clusterware registry database. The Oracle Clusterware will no longer acknowledge this resource. An application associated with a resource that is unregistered is no longer highly available. You must have full administrative privileges to use this command.
Upon successful completion of the crs_unregister command, the resource is removed from the online Oracle Clusterware environment. You cannot unregister a resource that is a required resource for another resource. You must stop the resource by using the crs_stop command before unregistering it.
Syntax and Options for crs_unregister
Use the crs_unregister command with the following syntax:
crs_unregister resource_name [...] [-q]
The only option available for this command is -q, that runs the crs_unregister command in quiet mode, which means no messages are displayed.
Example of crs_unregister
The following example unregisters a highly available application called postman:
crs_unregister postman
Errors for crs_unregister
Oracle displays a corresponding text message for the following error conditions:
* No root privilege
* CAA daemon is not running
* The application is running
* The application is not registered
Wednesday, June 11, 2008
How to use Exclude in EXPDP:
Recently i got a requirement to export schema minus certain tables:
expdp system/******** schemas=REPORT directory=REPORT1 dumpfile=report1.dmp logfile=report1.log EXCLUDE=TABLE:\"LIKE \'OP%\'\", TABLE:\"LIKE \'%XYZ%\'\"
I figured out the power of expdp here, which enables the use of LIKE and such other operations in the EXCLUDE section.
I had problems with just typing EXCLUDE=TABLE:"LIKE 'OP%'", TABLE:"LIKE '%XYZ%'":
ORA-39001: invalid argument value
ORA-39071: Value for EXCLUDE is badly formed.
ORA-00911: invalid character
So decided to include escape characters in the statement, so it became:
EXCLUDE=TABLE:\"LIKE \'OP%\'\", TABLE:\"LIKE \'%XYZ%\'\" , this means exclude tables starting with OP and any tables which have XYZ in their table name.
Other options in expdp exclude:
Using the NOT IN OPERATOR
EXCLUDE=TABLE:\"NOT IN \(\'TEMP\',\'TEMP1\'\)\"
Using the IN OPERATOR
EXCLUDE=TABLE:\"IN \(\'TEMP\',\'TEMP1\'\)\"
I think this is a great advancement in the way we use exp. I found this very helpful.
Thursday, May 29, 2008
ORA-14074: partition bound must collate higher than that of the last partition
Problem:
ORA-14074: partition bound must collate higher than that of the last partition
What was being done:
I had a table which had daily partitions. I wanted to drop daily partitions and create weekly partitions. I have the data available elsewhere, so I can drop the partitions. I had dropped partitions for a week:
alter table apun.log drop parition PARTITION_2007_10_01;
alter table apun.log drop parition PARTITION_2007_10_02;
alter table apun.log drop parition PARTITION_2007_10_03;
alter table apun.log drop parition PARTITION_2007_10_04;
alter table apun.log drop parition PARTITION_2007_10_05;
alter table apun.log drop parition PARTITION_2007_10_06;
alter table apun.log drop parition PARTITION_2007_10_07;
alter table apun.log drop parition PARTITION_2007_10_08;
and tried creating weekly partition:
SQL> ALTER TABLE apun.log ADD PARTITION week_01_10_2007 VALUES LESS THAN ((TO_DATE('2007-10-09 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')))
NOLOGGING
COMPRESS
TABLESPACE log_tablespace;
PARTITION week_01_10_2007 VALUES LESS THAN ((TO_DATE('2007-10-09 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')))
*
ERROR at line 3:
ORA-14074: partition bound must collate higher than that of the last partition
I still have other daily partitions, after the ones I dropped.
Resolution:
Searched the net and metalink and found that what needs to be done is to use SPLIT PARTITION:
ALTER TABLE apun.log
SPLIT PARTITION PARTITION_2007_10_09 AT
(((TO_DATE(' 2007-10-09 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))))
INTO (week_01_10_2007
TABLESPACE log_tablespace
PCTFREE 0
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 64K
NEXT 128M
MINEXTENTS 1
PCTINCREASE 0
BUFFER_POOL DEFAULT
),
PARTITION PARTITION_2007_10_09);
Monday, April 28, 2008
Step by Step 11gR1 RAC CRS Installation
Oracle Global Support strongly recommends that you use Red Hat Enterprise Linux ES/AS 4 (update 3 or higher). This is kernel 2.6.9-34 or greater.
Requried RPMS: (check details on Metalink Note:437123.1)
compat-libstdc++-33-3.2.3.x86_64.rpm
elfutils-libelf-devel-0.97-5.x86_64.rpm
glibc-devel-2.3.4-2.19.x86_64.rpm
glibc-devel-2.3.4-2.19.i386.rpm (32-Bit)
gcc-3.4.5-2.x86_64.rpm
libstdc++-devel-3.4.5-2.x86_64.rpm
gcc-c++-3.4.5-2.x86_64.rpm
libaio-0.3.105-2.x86_64.rpm
libaio-0.3.105-2.i386.rpm (32-Bit)
libaio-devel-0.3.105-2.x86_64.rpm
sysstat-5.0.5-7.rhel4.x86_64.rpm
glibc-headers-2.3.4-2.19.x86_64.rpm
glibc-kernheaders-2.4-9.1.98.EL.x86_64.rpm
To query rpm installed in your system:
rpm -qa --queryformat "%{NAME}-%{VERSION}-%{RELEASE} (%{ARCH})\n" | grep compat-libstdc++
Sample sysctl.conf
kernel.shmall = 2097152
kernel.shmmax = 2147483648
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
fs.file-max = 65536
net.ipv4.ip_local_port_range = 1024 65000
net.core.rmem_default=262144
net.core.rmem_max=262144
net.core.wmem_default=262144
net.core.wmem_max=262144
net.ipv4.tcp_wmem = 262144 262144 262144
net.ipv4.tcp_rmem = 4194304 4194304 4194304
Add the following settings to /etc/security/limits.conf
oracle soft nproc 2047
oracle hard nproc 16384
oracle soft nofile 1024
oracle hard nofile 65536
Add or edit the following line in the /etc/pam.d/login file, if it does not already exist:
session required pam_limits.so
Add the following lines to /etc/profile:
if [ $USER = "oracle" ]; then
if [ $SHELL = "/bin/ksh" ]; then
ulimit -p 16384
ulimit -n 65536
else
ulimit -u 16384 -n 65536
fi
fi
This is how the /etc/hosts file look like:
[oracle@oradbdev02 ~]$ more /etc/hosts
# Do not remove the following line, or various programs
# that require network functionality will fail.
#127.0.0.1 localhost.localdomain localhost
127.0.0.1 oradbdev02.xxxx.com oradbdev02
# Public
10.66.81.61 oradbdev01.xxxx.com oradbdev01
10.66.81.62 oradbdev02.xxxx.com oradbdev02
#Private
192.168.0.61 oradbdev01-priv.xxxx.com oradbdev01-priv
192.168.0.62 oradbdev02-priv.xxxx.com oradbdev02-priv
#Virtual
10.66.81.198 oradbdev01-vip.xxxx.com oradbdev01-vip
10.66.81.199 oradbdev02-vip.xxxx.com oradbdev02-vip
Imp metalink notes to look at before installation: 452780.1.
Setup user equivalence between the rac nodes.
Check that oracle is able to ssh between the rac servers without having to type the password.
Setup up location of OCR & Voting disk files. For test purposes I used the same mount point with different folders for each e.g.
/DB01/ocr
/DB01/voting_disk
Now run the cluster verify utility to do a pre-check before the cluster installation.
[oracle@oradbdev01 clusterware]$ ./runcluvfy.sh stage -pre crsinst -n oradbdev01,oradbdev02 -verbose
Performing pre-checks for cluster services setup
Checking node reachability...
Check: Node reachability from node "oradbdev01"
Destination Node Reachable?
------------------------------------ ------------------------
oradbdev01 yes
oradbdev02 yes
Result: Node reachability check passed from node "oradbdev01".
Checking user equivalence...
Check: User equivalence for user "oracle"
Node Name Comment
------------------------------------ ------------------------
oradbdev02 passed
oradbdev01 passed
Result: User equivalence check passed for user "oracle".
Checking administrative privileges...
Check: Existence of user "oracle"
Node Name User Exists Comment
------------ ------------------------ ------------------------
oradbdev02 yes passed
oradbdev01 yes passed
Result: User existence check passed for "oracle".
Check: Existence of group "oinstall"
Node Name Status Group ID
------------ ------------------------ ------------------------
oradbdev02 exists 503
oradbdev01 exists 503
Result: Group existence check passed for "oinstall".
Check: Membership of user "oracle" in group "oinstall" [as Primary]
Node Name User Exists Group Exists User in Group Primary Comment
---------------- ------------ ------------ ------------ ------------ ------------
oradbdev02 yes yes yes no failed
oradbdev01 yes yes yes no failed
Result: Membership check for user "oracle" in group "oinstall" [as Primary] failed.
Administrative privileges check failed.
Checking node connectivity...
Interface information for node "oradbdev02"
Interface Name IP Address Subnet Subnet Gateway Default Gateway Hardware Address
---------------- ------------ ------------ ------------ ------------ ------------
eth0 10.66.81.62 10.66.80.0 0.0.0.0 10.66.80.1 00:1B:78:DF:CC:9C
eth1 192.168.0.62 192.168.0.0 0.0.0.0 10.66.80.1 00:1B:78:DF:CC:84
Interface information for node "oradbdev01"
Interface Name IP Address Subnet Subnet Gateway Default Gateway Hardware Address
---------------- ------------ ------------ ------------ ------------ ------------
eth0 10.66.81.61 10.66.80.0 0.0.0.0 10.66.80.1 00:1B:78:DF:AC:0E
eth1 192.168.0.61 192.168.0.0 0.0.0.0 10.66.80.1 00:1B:78:DF:AC:08
Check: Node connectivity of subnet "10.66.80.0"
Source Destination Connected?
------------------------------ ------------------------------ ----------------
oradbdev02:eth0 oradbdev01:eth0 yes
Result: Node connectivity check passed for subnet "10.66.80.0" with node(s) oradbdev02,oradbdev01.
Check: Node connectivity of subnet "192.168.0.0"
Source Destination Connected?
------------------------------ ------------------------------ ----------------
oradbdev02:eth1 oradbdev01:eth1 yes
Result: Node connectivity check passed for subnet "192.168.0.0" with node(s) oradbdev02,oradbdev01.
Interfaces found on subnet "10.66.80.0" that are likely candidates for VIP:
oradbdev02 eth0:10.66.81.62
oradbdev01 eth0:10.66.81.61
Interfaces found on subnet "192.168.0.0" that are likely candidates for a private interconnect:
oradbdev02 eth1:192.168.0.62
oradbdev01 eth1:192.168.0.61
Result: Node connectivity check passed.
Checking system requirements for 'crs'...
Check: Total memory
Node Name Available Required Comment
------------ ------------------------ ------------------------ ----------
oradbdev02 7.79GB (8164980KB) 1GB (1048576KB) passed
oradbdev01 7.79GB (8164980KB) 1GB (1048576KB) passed
Result: Total memory check passed.
Check: Free disk space in "/tmp" dir
Node Name Available Required Comment
------------ ------------------------ ------------------------ ----------
oradbdev02 199.68GB (209378492KB) 400MB (409600KB) passed
oradbdev01 216.94GB (227478076KB) 400MB (409600KB) passed
Result: Free disk space check passed.
Check: Swap space
Node Name Available Required Comment
------------ ------------------------ ------------------------ ----------
oradbdev02 15.81GB (16579072KB) 1.5GB (1572864KB) passed
oradbdev01 15.8GB (16571036KB) 1.5GB (1572864KB) passed
Result: Swap space check passed.
Check: System architecture
Node Name Available Required Comment
------------ ------------------------ ------------------------ ----------
oradbdev02 x86_64 x86_64 passed
oradbdev01 x86_64 x86_64 passed
Result: System architecture check passed.
Check: Kernel version
Node Name Available Required Comment
------------ ------------------------ ------------------------ ----------
oradbdev02 2.6.9-55.ELsmp 2.6.9-11.EL passed
oradbdev01 2.6.9-55.ELsmp 2.6.9-11.EL passed
Result: Kernel version check passed.
Check: Package existence for "make-3.80"
Node Name Status Comment
------------------------------ ------------------------------ ----------------
oradbdev02 make-3.80-6.EL4 passed
oradbdev01 make-3.80-6.EL4 passed
Result: Package existence check passed for "make-3.80".
Check: Package existence for "binutils-2.15.92.0.2"
Node Name Status Comment
------------------------------ ------------------------------ ----------------
oradbdev02 binutils-2.15.92.0.2-22 passed
oradbdev01 binutils-2.15.92.0.2-22 passed
Result: Package existence check passed for "binutils-2.15.92.0.2".
Check: Package existence for "gcc-3.4.5"
Node Name Status Comment
------------------------------ ------------------------------ ----------------
oradbdev02 gcc-3.4.6-9 passed
oradbdev01 gcc-3.4.6-8 passed
Result: Package existence check passed for "gcc-3.4.5".
Check: Package existence for "libaio-0.3.105"
Node Name Status Comment
------------------------------ ------------------------------ ----------------
oradbdev02 libaio-0.3.105-2 passed
oradbdev01 libaio-0.3.105-2 passed
Result: Package existence check passed for "libaio-0.3.105".
Check: Package existence for "libaio-0.3.105"
Node Name Status Comment
------------------------------ ------------------------------ ----------------
oradbdev02 libaio-0.3.105-2 passed
oradbdev01 libaio-0.3.105-2 passed
Result: Package existence check passed for "libaio-0.3.105".
Check: Package existence for "libaio-devel-0.3.105"
Node Name Status Comment
------------------------------ ------------------------------ ----------------
oradbdev02 libaio-devel-0.3.105-2 passed
oradbdev01 libaio-devel-0.3.105-2 passed
Result: Package existence check passed for "libaio-devel-0.3.105".
Check: Package existence for "libstdc++-3.4.5"
Node Name Status Comment
------------------------------ ------------------------------ ----------------
oradbdev02 libstdc++-3.4.6-9 passed
oradbdev01 libstdc++-3.4.6-8 passed
Result: Package existence check passed for "libstdc++-3.4.5".
Check: Package existence for "libstdc++-3.4.5"
Node Name Status Comment
------------------------------ ------------------------------ ----------------
oradbdev02 libstdc++-3.4.6-9 passed
oradbdev01 libstdc++-3.4.6-8 passed
Result: Package existence check passed for "libstdc++-3.4.5".
Check: Package existence for "elfutils-libelf-devel-0.97"
Node Name Status Comment
------------------------------ ------------------------------ ----------------
oradbdev02 elfutils-libelf-devel-0.97.1-5 passed
oradbdev01 elfutils-libelf-devel-0.97.1-4 passed
Result: Package existence check passed for "elfutils-libelf-devel-0.97".
Check: Package existence for "sysstat-5.0.5"
Node Name Status Comment
------------------------------ ------------------------------ ----------------
oradbdev02 sysstat-5.0.5-16.rhel4 passed
oradbdev01 sysstat-5.0.5-14.rhel4 passed
Result: Package existence check passed for "sysstat-5.0.5".
Check: Package existence for "libgcc-3.4.5"
Node Name Status Comment
------------------------------ ------------------------------ ----------------
oradbdev02 libgcc-3.4.6-8 passed
oradbdev01 libgcc-3.4.6-8 passed
Result: Package existence check passed for "libgcc-3.4.5".
Check: Package existence for "libgcc-3.4.5"
Node Name Status Comment
------------------------------ ------------------------------ ----------------
oradbdev02 libgcc-3.4.6-9 passed
oradbdev01 libgcc-3.4.6-8 passed
Result: Package existence check passed for "libgcc-3.4.5".
Check: Package existence for "libstdc++-devel-3.4.5"
Node Name Status Comment
------------------------------ ------------------------------ ----------------
oradbdev02 libstdc++-devel-3.4.6-9 passed
oradbdev01 libstdc++-devel-3.4.6-8 passed
Result: Package existence check passed for "libstdc++-devel-3.4.5".
Check: Package existence for "elfutils-libelf-0.97"
Node Name Status Comment
------------------------------ ------------------------------ ----------------
oradbdev02 elfutils-libelf-0.97.1-4elfutils-libelf-0.97.1-5 passed
oradbdev01 elfutils-libelf-0.97.1-4 passed
Result: Package existence check passed for "elfutils-libelf-0.97".
Check: Package existence for "glibc-2.3.4-2.19"
Node Name Status Comment
------------------------------ ------------------------------ ----------------
oradbdev02 failed
oradbdev01 failed
Result: Package existence check failed for "glibc-2.3.4-2.19".
Check: Package existence for "glibc-2.3.4-2.19"
Node Name Status Comment
------------------------------ ------------------------------ ----------------
oradbdev02 glibc-2.3.4-2.36 passed
oradbdev01 glibc-2.3.4-2.36 passed
Result: Package existence check passed for "glibc-2.3.4-2.19".
Check: Package existence for "glibc-common-2.3.4"
Node Name Status Comment
------------------------------ ------------------------------ ----------------
oradbdev02 glibc-common-2.3.4-2.36 passed
oradbdev01 glibc-common-2.3.4-2.36 passed
Result: Package existence check passed for "glibc-common-2.3.4".
Check: Package existence for "glibc-devel-2.3.4"
Node Name Status Comment
------------------------------ ------------------------------ ----------------
oradbdev02 glibc-devel-2.3.4-2.36 passed
oradbdev01 glibc-devel-2.3.4-2.36 passed
Result: Package existence check passed for "glibc-devel-2.3.4".
Check: Package existence for "glibc-devel-2.3.4"
Node Name Status Comment
------------------------------ ------------------------------ ----------------
oradbdev02 glibc-devel-2.3.4-2.36 passed
oradbdev01 glibc-devel-2.3.4-2.36 passed
Result: Package existence check passed for "glibc-devel-2.3.4".
Check: Package existence for "gcc-c++-3.4.5"
Node Name Status Comment
------------------------------ ------------------------------ ----------------
oradbdev02 gcc-c++-3.4.6-9 passed
oradbdev01 gcc-c++-3.4.6-8 passed
Result: Package existence check passed for "gcc-c++-3.4.5".
Check: Package existence for "compat-libstdc++-33-3.2.3"
Node Name Status Comment
------------------------------ ------------------------------ ----------------
oradbdev02 compat-libstdc++-33-3.2.3-47.3 passed
oradbdev01 compat-libstdc++-33-3.2.3-47.3 passed
Result: Package existence check passed for "compat-libstdc++-33-3.2.3".
Check: Package existence for "compat-libstdc++-33-3.2.3"
Node Name Status Comment
------------------------------ ------------------------------ ----------------
oradbdev02 compat-libstdc++-33-3.2.3-47.3 passed
oradbdev01 compat-libstdc++-33-3.2.3-47.3 passed
Result: Package existence check passed for "compat-libstdc++-33-3.2.3".
Check: Group existence for "dba"
Node Name Status Comment
------------ ------------------------ ------------------------
oradbdev02 exists passed
oradbdev01 exists passed
Result: Group existence check passed for "dba".
Check: Group existence for "oinstall"
Node Name Status Comment
------------ ------------------------ ------------------------
oradbdev02 exists passed
oradbdev01 exists passed
Result: Group existence check passed for "oinstall".
Check: User existence for "nobody"
Node Name Status Comment
------------ ------------------------ ------------------------
oradbdev02 exists passed
oradbdev01 exists passed
Result: User existence check passed for "nobody".
System requirement failed for 'crs'
Pre-check for cluster services setup was unsuccessful on all the nodes.
The check has failed for me for checking if oracle users primary group is "oinstall". I am using dba as the groups. Hence I am omitting this error.
Now we are all set to start the OUI
[oracle@oradbdev01 clusterware]$ /home/oracle/software/11g/Cluster/clusterware/runInstaller
Now follow the screen shots:
The Welcome Screen - Press Next:
 |
| From For Blog |
Select the location for your CRS_HOME, where the crs will get installed and Press Next:
 |
| From For Blog |
Oracle does some pre-check, resolve any issues and Press Next:
 |
| From For Blog |
Add information about the cluster nodes and the Press Next:
 |
| From For Blog |
Example of how to add information:
 |
| From For Blog |
Select the Public and Private IP address subnets and Press Next:
 |
| From For Blog |
Specify OCR Location and Press Next:
 |
| From For Blog |
Specify Voting Disk Location and Press Next:
|
| From For Blog |
Installation summary window, check for the remote node and Press Install:
 |
| From For Blog |
Installation in Progress:
|
| From For Blog |
I received this error as I had not setup my environment properly before starting the installation.
Error from make.log
/usr/bin/make -f ins_rdbms.mk ipc_none ORACLE_HOME=/home/oracle/product/11.1.0/db//home/oracle/product/11.1.0/db/rdbms/lib/ins_rdbms.mk:2: /home/oracle/product/11.1.0/db/rdbms/lib/env_rdbms.mk: No such file or directory
make: *** No rule to make target `/home/oracle/product/11.1.0/db/rdbms/lib/env_rdbms.mk'. Stop.
I stopped the installation and setup ORACLE_HOME again and started the install again.
#
# An unexpected error has been detected by HotSpot Virtual Machine:
#
# SIGSEGV (0xb) at pc=0x0000002a9591bb92, pid=8270, tid=1094084960
#
# Java VM: Java HotSpot(TM) 64-Bit Server VM (1.5.0_11-b03 mixed mode)
# Problematic frame:
# V [libjvm.so+0x3a4b92]
#
# An error report file with more information is saved as hs_err_pid8270.log
#
# If you would like to submit a bug report, please visit:
# http://java.sun.com/webapps/bugreport/crash.jsp
Again at this point my $PATH showed wrong path. Hence remove unnecessary entries from $PATH and restarted installation and it worked this time.
 |
| From For Blog |
Run root.sh on both the nodes:
 |
| From For Blog |
[oracle@oradbdev01 crs]$ sudo ./root.sh
Password:
WARNING: directory '/home/oracle/product/11.1.0' is not owned by root
WARNING: directory '/home/oracle/product' is not owned by root
WARNING: directory '/home/oracle' is not owned by root
Checking to see if Oracle CRS stack is already configured
Setting the permissions on OCR backup directory
Setting up Network socket directories
PROT-1: Failed to initialize ocrconfig
Failed to upgrade Oracle Cluster Registry configuration
There were permission problems on the folders in node 2.
Resolved the issue and restarted the installation.
Node 1:
Running root.sh:
[oracle@oradbdev01 crs]$ sudo ./root.sh
WARNING: directory '/home/oracle/product/11.1.0' is not owned by root
WARNING: directory '/home/oracle/product' is not owned by root
WARNING: directory '/home/oracle' is not owned by root
Checking to see if Oracle CRS stack is already configured
/etc/oracle does not exist. Creating it now.
Setting the permissions on OCR backup directory
Setting up Network socket directories
Oracle Cluster Registry configuration upgraded successfully
The directory '/home/oracle/product/11.1.0' is not owned by root. Changing owner to root
The directory '/home/oracle/product' is not owned by root. Changing owner to root
The directory '/home/oracle' is not owned by root. Changing owner to root
Successfully accumulated necessary OCR keys.
Using ports: CSS=49895 CRS=49896 EVMC=49898 and EVMR=49897.
node
node 1: oradbdev01 oradbdev01-priv oradbdev01
node 2: oradbdev02 oradbdev02-priv oradbdev02
Creating OCR keys for user 'root', privgrp 'root'..
Operation successful.
Now formatting voting device: /DB01/11g_rac/voting_disk/vote1
Now formatting voting device: /DB01/11g_rac/voting_disk/vote2
Now formatting voting device: /DB01/11g_rac/voting_disk/vote3
Format of 3 voting devices complete.
Startup will be queued to init within 30 seconds.
Adding daemons to inittab
Expecting the CRS daemons to be up within 600 seconds.
Cluster Synchronization Services is active on these nodes.
oradbdev01
Cluster Synchronization Services is inactive on these nodes.
oradbdev02
Local node checking complete. Run root.sh on remaining nodes to start CRS daemons.
Node 2:
[oracle@oradbdev02 crs]$ sudo ./root.sh
Password:
WARNING: directory '/home/oracle/product/11.1.0' is not owned by root
WARNING: directory '/home/oracle/product' is not owned by root
WARNING: directory '/home/oracle' is not owned by root
Checking to see if Oracle CRS stack is already configured
/etc/oracle does not exist. Creating it now.
Setting the permissions on OCR backup directory
Setting up Network socket directories
Oracle Cluster Registry configuration upgraded successfully
The directory '/home/oracle/product/11.1.0' is not owned by root. Changing owner to root
The directory '/home/oracle/product' is not owned by root. Changing owner to root
The directory '/home/oracle' is not owned by root. Changing owner to root
clscfg: EXISTING configuration version 4 detected.
clscfg: version 4 is 11 Release 1.
Successfully accumulated necessary OCR keys.
Using ports: CSS=49895 CRS=49896 EVMC=49898 and EVMR=49897.
node
node 1: oradbdev01 oradbdev01-priv oradbdev01
node 2: oradbdev02 oradbdev02-priv oradbdev02
clscfg: Arguments check out successfully.
NO KEYS WERE WRITTEN. Supply -force parameter to override.
-force is destructive and will destroy any previous cluster
configuration.
Oracle Cluster Registry for cluster has already been initialized
Startup will be queued to init within 30 seconds.
Adding daemons to inittab
Expecting the CRS daemons to be up within 600 seconds.
Cluster Synchronization Services is active on these nodes.
oradbdev01
oradbdev02
Cluster Synchronization Services is active on all the nodes.
Waiting for the Oracle CRSD and EVMD to start
Oracle CRS stack installed and running under init(1M)
Running vipca(silent) for configuring nodeapps
Creating VIP application resource on (2) nodes...
Creating GSD application resource on (2) nodes...
Creating ONS application resource on (2) nodes...
Starting VIP application resource on (2) nodes...
Starting GSD application resource on (2) nodes...
Starting ONS application resource on (2) nodes...
Done.
Oracle Run vipca and other configurations: Press Next after it completes:
 |
| From For Blog |
Final Window - Installation Completed!!! - Press Exit:
 |
| From For Blog |
[oracle@oradbdev01 bin]$ ./crsctl check crs
Cluster Synchronization Services appears healthy
Cluster Ready Services appears healthy
Event Manager appears healthy
[oracle@oradbdev02 bin]$ ./crsctl check crs
Cluster Synchronization Services appears healthy
Cluster Ready Services appears healthy
Event Manager appears healthy
This shows that crs installation was successful on both the nodes!!
Monday, April 21, 2008
10.2.0.1 CRS Installation and upgrade to 10.2.0.3 for RAC
Download oracle crs software from http://www.oracle.com/technology/software/index.html location. For this document we will use oracle 10.2.0.1 crs software.
i.e. 10201_clusterware_linux_x86_64.cpio
##cpio –idmv <>
[oracle@ac-oracle-001]~/stage/clusterware/cluvfy% ./runcluvfy.sh stage -pre crsinst -n -oracle-001,ac-oracle-002
Performing pre-checks for cluster services setup
Checking node reachability...
Node reachability check passed from node "ac-oracle-001".
Checking user equivalence...
User equivalence check passed for user "oracle".
Checking administrative privileges...
User existence check passed for "oracle".
Group existence check failed for "oinstall".
Check failed on nodes:
ac-oracle-002,ac-oracle-001
Administrative privileges check failed.
Checking node connectivity...
Node connectivity check passed for subnet "76.13.224.64" with node(s) ac-oracle-002,ac-oracle-001.
Node connectivity check passed for subnet "10.128.159.0" with node(s) ac-oracle-002,ac-oracle-001.
Suitable interfaces for VIP on subnet "76.13.224.64":
ac-oracle-002 eth0:76.13.224.119 eth0:76.13.224.111
ac-oracle-001 eth0:76.13.224.120 eth0:76.13.224.112
Suitable interfaces for the private interconnect on subnet "10.128.159.0":
ac-oracle-002 ib1:10.128.159.53
ac-oracle-001 ib1:10.128.159.52
Node connectivity check passed.
Checking system requirements for 'crs'...
Total memory check passed.
Free disk space check passed.
Swap space check passed.
System architecture check passed.
Kernel version check passed.
Package existence check passed for "binutils-2.15.92.0.2-13".
Group existence check passed for "dba".
Group existence check failed for "oinstall".
Check failed on nodes:
ac-oracle-002,ac-oracle-001
User existence check passed for "nobody".
System requirement failed for 'crs'
Pre-check for cluster services setup was unsuccessful on all the nodes.
As I am not using oinstall group, I am going to ignore the error for the oinstall group not existing.
Now invoke the installer:
[oracle@ac-oracle-001]~/stage/clusterware%./runInstaller
This is give you the welcome screen, please press next.
Then:
 |
| From For Blog |
Select the Ora Inventory location and "dba" as the group for installation. And press Next:
 |
| From For Blog |
Select location of CRS_HOME and press Next:
 |
| From For Blog |
This screen shows that the installer is running a check from pre-requisites.
This should complete without error, check for warnings if any. I received a warning for Swap space, so I continued the installation, deciding to take care of the swap space latter. Press Next:
 |
| From For Blog |
Add node information in this window:
|
| From For Blog |
Specify the Pulic Name, Private Name and VIP Name for the various RAC nodes:
|
| From For Blog |
Press Next:
 |
| From For Blog |
I received the above error, this happened as the user equivalence was not setup properly. I rectified the problem and proceeded.
 |
| From For Blog |
This shows the details for the Public and Private IP, Please verify and see if the installer has got the right information about the IP addresses.
 |
| From For Blog |
Press Next:
 |
| From For Blog |
Specify the location of OCR files and Press Next:
 |
| From For Blog |
Specify the location of Voting Disk Files and Press Next:
Next you will see the install summary page, please press INSTALL on this page.
|
| From For Blog |
After the installation completes you will be asked to run the orainstRoot.sh and root.sh as root user.
 |
| From For Blog |
Before running the root.sh & orainstRoot.sh download and apply patch 4679769
[oracle@ac-oracle-001]~/stage/clusterware/patch/4679769% cp -p /oracle/product/crs/bin/clsfmt.bin /oracle/product/crs/bin/clsfmt.bin.bak
[oracle@ac-oracle-001]~/stage/clusterware/patch/4679769%
[oracle@ac-oracle-001]~/stage/clusterware/patch/4679769% ls -l /oracle/product/crs/bin/clsfmt*
-rwxr-xr-x 1 oracle dba 1676 Apr 18 08:36 /oracle/product/crs/bin/clsfmt
-rwxr-xr-x 1 oracle dba 687684 Oct 20 2005 /oracle/product/crs/bin/clsfmt.bin
-rwxr-xr-x 1 oracle dba 687684 Oct 20 2005 /oracle/product/crs/bin/clsfmt.bin.bak
[oracle@ac-oracle-001]~/stage/clusterware/patch/4679769%
[oracle@ac-oracle-001]~/stage/clusterware/patch/4679769% cp clsfmt.bin /oracle/product/crs/bin/clsfmt.bin
[oracle@ac-oracle-001]~/stage/clusterware/patch/4679769%
[oracle@ac-oracle-001]~/stage/clusterware/patch/4679769% chmod 755 /oracle/product/crs/bin/clsfmt.bin
[oracle@ac-oracle-001]~/stage/clusterware/patch/4679769%
[oracle@ac-oracle-001]~/stage/clusterware/patch/4679769% ls -lart /oracle/product/crs/bin/clsfmt.bi*
-rwxr-xr-x 1 oracle dba 687684 Oct 20 2005 /oracle/product/crs/bin/clsfmt.bin.bak
-rwxr-xr-x 1 oracle dba 687320 Apr 18 08:45 /oracle/product/crs/bin/clsfmt.bin
[oracle@ac-oracle-001]~/stage/clusterware/patch/4679769%
[oracle@ac-oracle-001]~/oraInventory% sudo ./orainstRoot.sh
Password:
Changing permissions of /oracle/oraInventory to 770.
Changing groupname of /oracle/oraInventory to dba.
The execution of the script is complete
[oracle@ac-oracle-001]~/oraInventory% cd
[oracle@ac-oracle-001]~% cd product/crs
[oracle@ac-oracle-001]~/product/crs% sudo ./root.sh
WARNING: directory '/oracle/product' is not owned by root
WARNING: directory '/' is not owned by root
Checking to see if Oracle CRS stack is already configured
/etc/oracle does not exist. Creating it now.
Setting the permissions on OCR backup directory
Setting up NS directories
Oracle Cluster Registry configuration upgraded successfully
WARNING: directory '/oracle/product' is not owned by root
WARNING: directory '/' is not owned by root
Successfully accumulated necessary OCR keys.
Using ports: CSS=49895 CRS=49896 EVMC=49898 and EVMR=49897.
node
node 1: ac-oracle-001 ac-oracle-001-i ac-oracle-001
node 2: ac-oracle-002 ac-oracle-002-i ac-oracle-002
Creating OCR keys for user 'root', privgrp 'root'..
Operation successful.
Now formatting voting device: /dev/raw/raw3
Now formatting voting device: /dev/raw/raw4
Now formatting voting device: /dev/raw/raw5
Format of 3 voting devices complete.
Startup will be queued to init within 90 seconds.
Adding daemons to inittab
Expecting the CRS daemons to be up within 600 seconds.
CSS is active on these nodes.
ac-oracle-001
CSS is inactive on these nodes.
ac-oracle-002
Local node checking complete.
Run root.sh on remaining nodes to start CRS daemons.
These 2 scripts need to be run on all RAC nodes.
These format and initializes the OCR & Voting Disks.
After the root.sh has completed successfully, the crs would come up on that node.
This completes the 10.2.0.1 CRS Installation.
To upgrade the same to 10.2.0.3 download the patch 5337014, use the runInstaller provided in this patch-set to upgrade the CRS home to 10.2.0.3
The most important step is to run the root102.sh after the upgrade installer completes, you have to run this on all the RAC nodes:
[oracle@ac-oracle-001]~% cd product/crs/install
[oracle@ac-oracle-001]~/product/crs/install% ls -lrt
total 168
-rwxr-xr-x 1 oracle dba 0 Feb 23 2005 install.incl
-rwxr-xr-x 1 oracle dba 38 Apr 19 2005 install.excl
-rw-rw---- 1 oracle dba 2800 Jun 7 2005 templocal
-r-xr-xr-x 1 oracle dba 4150 Aug 11 2005 rootaddnode.sbs
-rwxr-xr-x 1 oracle dba 1092 Oct 18 2005 cmdllroot.sh
-rw-rw---- 1 oracle dba 9625 Apr 18 08:36 rootlocaladd
-r-xr-xr-x 1 oracle dba 17029 Apr 18 08:36 rootupgrade
-r-xr-xr-x 1 oracle dba 3580 Apr 18 08:36 rootinstall
-r-xr-xr-x 1 oracle dba 12072 Apr 18 08:36 rootdelete.sh
-r-xr-xr-x 1 oracle dba 3918 Apr 18 08:36 rootdeletenode.sh
-r-xr-xr-x 1 oracle dba 8154 Apr 18 08:36 rootdeinstall.sh
-r-xr-xr-x 1 oracle dba 34063 Apr 18 08:36 rootconfig
-rwxr-xr-x 1 oracle dba 4570 Apr 18 08:36 preupdate.sh
-rw-rw---- 1 oracle dba 1760 Apr 18 08:36 paramfile.crs
-rw-rw---- 1 oracle dba 4065 Apr 18 08:36 make.log
-rw-rw---- 1 oracle dba 32 Apr 18 08:36 cluster.ini
-rw-rw---- 1 oracle dba 177 Apr 18 08:37 envVars.properties
-rwxr-xr-x 1 oracle dba 23126 Apr 18 11:36 root102.sh
drwxr-xr-x 32 oracle dba 4096 Apr 18 11:39 patch102
[oracle@ac-oracle-001]~/product/crs/install% sudo ./root102.sh
Password:
Creating pre-patch directory for saving pre-patch clusterware files
Completed patching clusterware files to /oracle/product/crs
Relinking some shared libraries.
Relinking of patched files is complete.
WARNING: directory '/oracle/product' is not owned by root
Preparing to recopy patched init and RC scripts.
Recopying init and RC scripts.
Startup will be queued to init within 30 seconds.
Starting up the CRS daemons.
Waiting for the patched CRS daemons to start.
This may take a while on some systems.
.
10203 patch successfully applied.
clscfg: EXISTING configuration version 3 detected.
clscfg: version 3 is 10G Release 2.
Successfully accumulated necessary OCR keys.
Using ports: CSS=49895 CRS=49896 EVMC=49898 and EVMR=49897.
node
node 8: ac-oracle-008 ac-oracle-008-i ac-oracle-008
Creating OCR keys for user 'root', privgrp 'root'..
Operation successful.
clscfg -upgrade completed successfully
[oracle@ac-oracle-008]~/product/crs/install% cd ../bin
[oracle@ac-oracle-008]~/product/crs/bin% ./crsctl query crs softwareversion
CRS software version on node [ac-oracle-001] is [10.2.0.3.0]
In function `lcdprm':: warning: the `gets' function is dangerous and should not be used. :failed
I was recently applying patch 5452672 patch on a 10.2.0.3 oracle 8 node RAC instance. It gave the below error for all the remote nodes:
WARNING for re-link on remote node 'ac-oracle-008':
OPatch completed the command 'cd /oracle/product/10.2.0/db/rdbms/lib; /usr/bin/make -f ins_rdbms.mk ioracle ORACLE_HOME=/oracle/product/10.2.0/db || echo REMOTE_MAKE_FAILED::>&2 ' with warnings.
This command is from the file '/oracle/product/10.2.0/db/.patch_storage/5452672_Feb_7_2007_05_56_01/rac/make_cmds.txt.instantiated', line number '1'
Probable cause: chmod 755 /oracle/product/10.2.0/db/bin - Linking Oracle rm -f /oracle/product/10.2.0/db/rdbms/lib/oraclegcc -o /oracle/product/10.2.0/db/rdbms/lib/oracle -L/oracle/product/10.2.0/db/rdbms/lib/ -L/oracle/product/10.2.0/db/lib/ -L/oracle/product/10.2.0/db/lib/stubs/ -Wl,-E `test -f /oracle/product/10.2.0/db/rdbms/lib/sskgpsmti.o && echo /oracle/product/10.2.0/db/rdbms/lib/sskgpsmti.o` /oracle/product/10.2.0/db/rdbms/lib/opimai.o /oracle/product/10.2.0/db/rdbms/lib/ssoraed.o /oracle/product/10.2.0/db/rdbms/lib/ttcsoi.o /oracle/product/10.2.0/db/rdbms/lib/defopt.o -Wl,--whole-archive -lperfsrv10 -Wl,--no-whole-archive /oracle/product/10.2.0/db/lib/nautab.o /oracle/product/10.2.0/db/lib/naeet.o /oracle/product/10.2.0/db/lib/naect.o /oracle/product/10.2.0/db/lib/naedhs.o /oracle/product/10.2.0/db/rdbms/lib/config.o -lserver10 -lodm10 -lnnet10 -lskgxp10 -lsnls10 -lnls10 -lcore10 -lsnls10 -lnls10 -lcore10 -lsnls10 -lnls10 -lxml10 -lcore10 -lunls10 -lsnls10 -lnls10 -lcore10 -lnls10 -lhasgen10 -lcore10 -lskgxn2 -locr10 -locrb10 -locrutl10 -lhasgen10 -lcore10 -lskgxn2 -lclient10 -lvsn10 -lcommon10 -lgeneric10 -lknlopt `if /usr/bin/ar tv /oracle/product/10.2.0/db/rdbms/lib/libknlopt.a | grep xsyeolap.o > /dev/null 2>&1 ; then echo "-loraolap10" ; fi` -lslax10 -lpls10 -lplp10 -lserver10 -lclient10 -lvsn10 -lcommon10 -lgeneric10 `if [ -f /oracle/product/10.2.0/db/lib/libavserver10.a ] ; then echo "-lavserver10" ; else echo "-lavstub10"; fi` `if [ -f /oracle/product/10.2.0/db/lib/libavclient10.a ] ; then echo "-lavclient10" ; fi` -lknlopt -lslax10 -lpls10 -lplp10 -ljox10 -lserver10 -lclsra10 -ldbcfg10 -locijdbcst10 -lwwg `cat /oracle/product/10.2.0/db/lib/ldflags` -lnsslb10 -lncrypt10 -lnsgr10 -lnzjs10 -ln10 -lnnz10 -lnl10 -lnro10 `cat /oracle/product/10.2.0/db/lib/ldflags` -lnsslb10 -lncrypt10 -lnsgr10 -lnzjs10 -ln10 -lnnz10 -lnl10 -lmm -lsnls10 -lnls10 -lcore10 -lsnls10 -lnls10 -lcore10 -lsnls10 -lnls10 -lxml10 -lcore10 -lunls10 -lsnls10 -lnls10 -lcore10 -lnls10 `cat /oracle/product/10.2.0/db/lib/ldflags` -lnsslb10 -lncrypt10 -lnsgr10 -lnzjs10 -ln10 -lnnz10 -lnl10 -lnro10 `cat /oracle/product/10.2.0/db/lib/ldflags` -lnsslb10 -lncrypt10 -lnsgr10 -lnzjs10 -ln10 -lnnz10 -lnl10 -lsnls10 -lnls10 -lcore10 -lsnls10 -lnls10 -lcore10 -lsnls10 -lnls10 -lxml10 -lcore10 -lunls10 -lsnls10 -lnls10 -lcore10 -lnls10 `if /usr/bin/ar tv /oracle/product/10.2.0/db/rdbms/lib/libknlopt.a | grep "kxmnsd.o" > /dev/null 2>&1 ; then echo " " ; else echo "-lordsdo10"; fi` -lctxc10 -lctx10 -lzx10 -lgx10 -lctx10 -lzx10 -lgx10 -lordimt10 -lsnls10 -lnls10 -lcore10 -lsnls10 -lnls10 -lcore10 -lsnls10 -lnls10 -lxml10 -lcore10 -lunls10 -lsnls10 -lnls10 -lcore10 -lnls10 -lsnls10 -lunls10 -lsnls10 -lnls10 -lcore10 -lsnls10 -lnls10 -lcore10 -lsnls10 -lnls10 -lxml10 -lcore10 -lunls10 -lsnls10 -lnls10 -lcore10 -lnls10 -laio `cat /oracle/product/10.2.0/db/lib/sysliblist` -Wl,-rpath,/oracle/product/10.2.0/db/lib -lm `cat /oracle/product/10.2.0/db/lib/sysliblist` -ldl -lm -L/oracle/product/10.2.0/db/libmv -f /oracle/product/10.2.0/db/bin/oracle /oracle/product/10.2.0/db/bin/oracleOmv /oracle/product/10.2.0/db/rdbms/lib/oracle /oracle/product/10.2.0/db/bin/oraclechmod 6751 /oracle/product/10.2.0/db/bin/oracle/oracle/product/10.2.0/db/lib//libcore10.a(lcd.o)(.text+0xb71): In function `lcdprm':: warning: the `gets' function is dangerous and should not be used. :failed
Solutions:
Check Metalink Note: 356363.1
No action is required, please ignore the message, it is a internal code bug message reference for developer to fix the code in future versions.
Fixed in 11g.Just thought of sharing this info. You may receive this error/warning with a lot of other patches, I faced this for other patches too.
Tuesday, April 15, 2008
Move/Replace/Change Location of OCR & Voting Disk
How To Change the location of OCR & Voting Disk in 10gR2 Oracle RAC
Voting Disk:
1.) Login as root
2.) Shutdown CRS on all nodes:
root@oracle-server:/home/crs/bin[bin]# ./crsctl stop crs
Stopping resources.
Successfully stopped CRS resources
Stopping CSSD.
Shutting down CSS daemon.
Shutdown request successfully issued.
root@oracle-server:/home/crs/bin[bin]# ./crsctl check crs
Failure 1 contacting CSS daemon
Cannot communicate with CRS
Cannot communicate with EVM
3.) Check the the location of the voting disk:
root@oracle-server:/home/crs/bin[bin]# ./crsctl query css votedisk
0. 0 /mnt/db_crs/vd1
1. 0 /mnt/db_crs/vd2
2. 0 /mnt/db_crs/vd3
4.) Copy the voting disk to the new location:
root@oracle-server:/home/crs/bin[bin]# cp /mnt/db_crs/vd2 /mnt/db_crs_mirror1/
root@oracle-server:/home/crs/bin[bin]# cp /mnt/db_crs/vd3 /mnt/db_crs_mirror2/
5.) Change the location in CRS:
root@oracle-server:/home/crs/bin[bin]# ./crsctl add css votedisk /mnt/db_crs_mirror1/vd2
Now formatting voting disk: /mnt/db_crs_mirror1/vd2
successful addition of votedisk /mnt/db_crs_mirror1/vd2.
root@oracle-server:/home/crs/bin[bin]# ./crsctl add css votedisk /mnt/db_crs_mirror2/vd3
Now formatting voting disk: /mnt/db_crs_mirror2/vd3
successful addition of votedisk /mnt/db_crs_mirror2/vd3.
6.) Delete the old Voting Disks:
root@oracle-server:/home/crs/bin[bin]# crsctl delete css votedisk /mnt/db_crs/vd2 -force
successful deletion of votedisk /mnt/db_crs/vd2.
root@oracle-server:/home/crs/bin[bin]# crsctl delete css votedisk /mnt/db_crs/vd3 -force
successful deletion of votedisk /mnt/db_crs/vd3.
7.) Query CRS to see if the changes where proper:
root@oracle-server:/home/crs/bin[bin]# ./crsctl query css votedisk
0. 0 /mnt/db_crs/vd1
1. 0 /mnt/db_crs_mirror2/vd3
2. 0 /mnt/db_crs_mirror1/vd2
PS: The permission to the voting disk should be root:dba, while copying and creating voting disk as we are using the root user, the permission will change to root:root, which will give error when starting crs.
OCR:
1.) Login as root.
2.) Start CRS on all nodes:
root@oracle-server:/home/crs/bin[bin]# ./crsctl start crs
Attempting to start CRS stack
The CRS stack will be started shortly
root@oracle-server:/home/crs/bin[bin]# ./crsctl check crs
Failure 1 contacting CSS daemon
Cannot communicate with CRS
Cannot communicate with EVM
root@oracle-server:/home/crs/bin[bin]# ./crsctl check crs
CSS appears healthy
CRS appears healthy
EVM appears healthy
3.) Check OCR configuration/backups:
root@oracle-server:/home/crs/bin[bin]# ocrcheck
Status of Oracle Cluster Registry is as follows :
Version : 2
Total space (kbytes) : 262144
Used space (kbytes) : 2812
Available space (kbytes) : 259332
ID : 429958626
Device/File Name : /mnt/db_crs/crs
Device/File integrity check succeeded
Device/File Name : /mnt/db_crs/crs_mirror
Device/File integrity check succeeded
Cluster registry integrity check succeeded
root@oracle-server:/home/crs/bin[bin]# ocrconfig -showbackup
oracle-server 2008/04/10 23:47:02 /oracle/product/10.2.0/crs/cdata/ac4-db-crs
oracle-server 2008/04/10 19:47:03 /oracle/product/10.2.0/crs/cdata/ac4-db-crs
oracle-server 2008/04/10 15:47:04 /oracle/product/10.2.0/crs/cdata/ac4-db-crs
oracle-server 2008/04/08 19:47:17 /oracle/product/10.2.0/crs/cdata/ac4-db-crs
oracle-server 2008/04/03 15:47:53 /oracle/product/10.2.0/crs/cdata/ac4-db-crs
4.) Copy the OCR mirror to the new location:
root@oracle-server:/mnt/prdaudit_crs[root@oracle-server db_crs]# ls -rlt
mtotal 38916
-rw-r--r-- 1 oracle dba 10240000 Apr 11 02:27 vd2.old
-rw-r--r-- 1 oracle dba 10240000 Apr 11 02:27 vd3.old
-rw-r----- 1 root dba 3121152 Apr 11 02:59 crs_mirror
-rw-r----- 1 root dba 3121152 Apr 11 03:22 crs
-rw-r--r-- 1 oracle dba 10240000 Apr 11 03:23 vd1
root@oracle-server:/mnt/db_crs[root@oracle-server db_crs]# cp crs_mirror /mnt/db_crs_mirror1
Check the permission, it should be root:dba
5.) Change the OCR location in the CRS:
root@oracle-server:/home/crs/bin[bin]# ./ocrconfig -replace ocrmirror /mnt/db_crs_mirror1/crs_mirror
Same way to can move the ocr:
oraconfig -replace ocr
root@oracle-server:/home/crs/bin[bin]# ocrcheck
Status of Oracle Cluster Registry is as follows :
Version : 2
Total space (kbytes) : 262144
Used space (kbytes) : 2812
Available space (kbytes) : 259332
ID : 429958626
Device/File Name : /mnt/db_crs/crs
Device/File integrity check succeeded
Device/File Name : /mnt/db_crs_mirror1/crs_mirror
Device/File integrity check succeeded
Cluster registry integrity check succeeded
root@oracle-server:/home/crs/bin[bin]# exit
OCR AND VOTING DISK MOVED!!!!
Thursday, April 10, 2008
Oracle 10g RAC Installation on NetApps Raw Devices with ASM: Part 3
Installing and Configuring Oracle ASMlib
Oracle ASMlib is software provided to ease the configuration of Disk Devices.
Please download the library files from http://www.oracle.com/technology/tech/linux/asmlib/index.html
The library are available with respect to your OS and OS version. The current version of ASMlib is 2.0. With respect to my operating system I downloaded:
oracleasm-support-2.0.3-1.x86_64.rpm
oracleasmlib-2.0.2-1.x86_64.rpm
oracleasm-2.6.9-55.ELsmp-2.0.3-1.x86_64.rpm
oracleasm-2.6.9-55.EL-2.0.3-1.x86_64.rpm
Installation of these binaries:
rpm –Uvh oracleasm-support-2.0.3-1.x86_64.rpm
rpm –Uvh oracleasm-2.6.9-55.EL-2.0.3-1.x86_64.rpm
rpm –Uvh oracleasmlib-2.0.2-1.x86_64.rpm
rpm –Uvh oracleasm-2.6.9-55.ELsmp-2.0.3-1.x86_64.rpm
Install these binaries on all RAC nodes.
Initialize oracleasm (All Nodes):
# /etc/init.d/oracleasm configure
Default user to own the driver interface []: oracle
Default group to own the driver interface []: dba
Start Oracle ASM library driver on boot (y/n) [n]: y
Fix permissions of Oracle ASM disks on boot (y/n) [y]
...
Note: Please make sure to enter default user and group to own the driver interface.
Creation of Disk Partitions and Oracle ASM Disk:
We are using 1 lun of 2GB to create partitions. We will create partitions for OCR, Voting Disk, and other database files.
You can also create separate luns for OCR, Voting Disk and DB files.
As we are using iSCSI devices we do not need to create disk partitions we can directly use /dev/sd* devices or as we are using multipath devices, we can directly use /dev/mpath/mpath*
For example sake I am creating disk partitions for this installation:
[root@oradbdev01 mpath]# fdisk /dev/dm-8 à LUN (multipath device)
Command (m for help): p
Disk /dev/dm-8: 2147 MB, 2147483648 bytes
67 heads, 62 sectors/track, 1009 cylinders
Units = cylinders of 4154 * 512 = 2126848 bytes
Device Boot Start End Blocks Id System
Command (m for help): n
Command action
e extended
p primary partition (1-4)
p
Partition number (1-4): 1
First cylinder (1-1009, default 1):
Using default value 1
Last cylinder or +size or +sizeM or +sizeK (1-1009, default 1009): +128M àOCR FILE
Command (m for help): p
Disk /dev/dm-8: 2147 MB, 2147483648 bytes
67 heads, 62 sectors/track, 1009 cylinders
Units = cylinders of 4154 * 512 = 2126848 bytes
Device Boot Start End Blocks Id System
/dev/dm-8p1 1 61 126666 83 Linux
Command (m for help): n
Command action
e extended
p primary partition (1-4)
p
Partition number (1-4): 2
First cylinder (62-1009, default 62):
Using default value 62
Last cylinder or +size or +sizeM or +sizeK (62-1009, default 1009): +128M àOCR FILE
Command (m for help): p
Disk /dev/dm-8: 2147 MB, 2147483648 bytes
67 heads, 62 sectors/track, 1009 cylinders
Units = cylinders of 4154 * 512 = 2126848 bytes
Device Boot Start End Blocks Id System
/dev/dm-8p1 1 61 126666 83 Linux
/dev/dm-8p2 62 122 126697 83 Linux
Command (m for help): n
Command action
e extended
p primary partition (1-4)
p
Partition number (1-4): 3
First cylinder (123-1009, default 123):
Using default value 123
Last cylinder or +size or +sizeM or +sizeK (123-1009, default 1009): +1000M àData File
Command (m for help): p
Disk /dev/dm-8: 2147 MB, 2147483648 bytes
67 heads, 62 sectors/track, 1009 cylinders
Units = cylinders of 4154 * 512 = 2126848 bytes
Device Boot Start End Blocks Id System
/dev/dm-8p1 1 61 126666 83 Linux
/dev/dm-8p2 62 122 126697 83 Linux
/dev/dm-8p3 123 593 978267 83 Linux
Command (m for help): n
Command action
e extended
p primary partition (1-4)
e
Selected partition 4
First cylinder (594-1009, default 594):
Using default value 594
Last cylinder or +size or +sizeM or +sizeK (594-1009, default 1009):
Using default value 1009 à Creating extended partition of the remaing space.
Command (m for help): p
Disk /dev/dm-8: 2147 MB, 2147483648 bytes
67 heads, 62 sectors/track, 1009 cylinders
Units = cylinders of 4154 * 512 = 2126848 bytes
Device Boot Start End Blocks Id System
/dev/dm-8p1 1 61 126666 83 Linux
/dev/dm-8p2 62 122 126697 83 Linux
/dev/dm-8p3 123 593 978267 83 Linux
/dev/dm-8p4 594 1009 864032 5 Extended
Command (m for help): n
First cylinder (594-1009, default 594):
Using default value 594
Last cylinder or +size or +sizeM or +sizeK (594-1009, default 1009): +20M àVoting Disk
Command (m for help): n
First cylinder (604-1009, default 604):
Using default value 604
Last cylinder or +size or +sizeM or +sizeK (604-1009, default 1009): +20M àVoting Disk
Command (m for help): n
First cylinder (614-1009, default 614): +20M
Value out of range.
First cylinder (614-1009, default 614):
Using default value 614
Last cylinder or +size or +sizeM or +sizeK (614-1009, default 1009): +20M àVoting Disk
Command (m for help): n
First cylinder (624-1009, default 624):
Using default value 624
Last cylinder or +size or +sizeM or +sizeK (624-1009, default 1009):
Using default value 1009 à Remaining space for Database files
Command (m for help): p
Disk /dev/dm-8: 2147 MB, 2147483648 bytes
67 heads, 62 sectors/track, 1009 cylinders
Units = cylinders of 4154 * 512 = 2126848 bytes
Device Boot Start End Blocks Id System
/dev/dm-8p1 1 61 126666 83 Linux àOCR FILE
/dev/dm-8p2 62 122 126697 83 Linux àOCR FILE
/dev/dm-8p3 123 593 978267 83 Linux àDatabase
/dev/dm-8p4 594 1009 864032 5 Extended
/dev/dm-8p5 594 603 20739 83 Linux àVoting Disk
/dev/dm-8p6 604 613 20739 83 Linux àVoting Disk
/dev/dm-8p7 614 623 20739 83 Linux àVoting Disk
/dev/dm-8p8 624 1009 801691 83 Linux àDatabase
Command (m for help): w
The partition table has been altered!
Calling ioctl() to re-read partition table.
WARNING: Re-reading the partition table failed with error 22: Invalid argument.
The kernel still uses the old table.
The new table will be used at the next reboot.
Syncing disks.
Reboot all nodes to read the partition table.
The devices will look like this:
[oracle@oradbdev01 ~]$ ls -lrt /dev/mpath/mpath8*
lrwxrwxrwx 1 root root 7 Apr 10 15:02 /dev/mpath/mpath8 -> ../dm-0
lrwxrwxrwx 1 root root 7 Apr 10 15:02 /dev/mpath/mpath8p2 -> ../dm-4
lrwxrwxrwx 1 root root 7 Apr 10 15:02 /dev/mpath/mpath8p6 -> ../dm-8
lrwxrwxrwx 1 root root 7 Apr 10 15:02 /dev/mpath/mpath8p1 -> ../dm-2
lrwxrwxrwx 1 root root 7 Apr 10 15:02 /dev/mpath/mpath8p5 -> ../dm-7
lrwxrwxrwx 1 root root 7 Apr 10 15:02 /dev/mpath/mpath8p7 -> ../dm-9
lrwxrwxrwx 1 root root 8 Apr 10 15:02 /dev/mpath/mpath8p8 -> ../dm-10
lrwxrwxrwx 1 root root 7 Apr 10 15:02 /dev/mpath/mpath8p3 -> ../dm-6
Now add database disk devices to oracle asm:
[root@oradbdev01 mpath]# /etc/init.d/oracleasm createdisk DISK3 /dev/mpath/mpath8p3;
Marking disk "/dev/mpath/mpath8p3" as an ASM disk: [ OK ]
[root@oradbdev01 mpath]# /etc/init.d/oracleasm createdisk DISK4 /dev/mpath/mpath8p8;
Marking disk "/dev/mpath/mpath8p8" as an ASM disk: [ OK ]
Run the command "/etc/init.d/oracleasm scandisks" on all other nodes so that ASM disks will show up on all other nodes.
[root@oradbdev02 ~]# /etc/init.d/oracleasm scandisks
Scanning system for ASM disks: [ OK ]
Run the command /etc/init.d/oracleasm listdisks and /etc/init.d/oracleasm querydisk on all nodes to verify the disks has been marked as ASM disks. For example:
[oracle@oradbdev01 ~]$ ssh oradbdev02 /etc/init.d/oracleasm listdisks
DISK1
DISK2
DISK3
DISK4
[oracle@oradbdev01 ~]$ /etc/init.d/oracleasm querydisk DISK3
Disk "DISK3" is a valid ASM disk on device [8, 19]
[oracle@oradbdev01 ~]$ ssh oradbdev02 /etc/init.d/oracleasm querydisk DISK3
Disk "DISK3" is a valid ASM disk on device [8, 19]
[oracle@oradbdev01 ~]$ /etc/init.d/oracleasm querydisk DISK4
Disk "DISK4" is a valid ASM disk on device [8, 24]
[oracle@oradbdev01 ~]$ ssh oradbdev02 /etc/init.d/oracleasm querydisk DISK4
Disk "DISK4" is a valid ASM disk on device [8, 24]
[root@oradbdev01 ~]# /etc/init.d/oracleasm querydisk /dev/mpath/mpath8p3
Disk "/dev/mpath/mpath8p3" is marked an ASM disk with the label "DISK3"
[root@oradbdev01 ~]# /etc/init.d/oracleasm querydisk /dev/mpath/mpath8p8
Disk "/dev/mpath/mpath8p8" is marked an ASM disk with the label "DISK4"
To find out the exact disk reflected in the output of oracleasm, use the entries in /proc/partitions file.
Configuring ASMlib on Multipath Disk:
[root@oradbdev01 ~]# vi /etc/sysconfig/oracleasm
#
# This is a configuration file for automatic loading of the Oracle
# Automatic Storage Management library kernel driver. It is generated
# By running /etc/init.d/oracleasm configure. Please use that method
# to modify this file
#
# ORACLEASM_ENABELED: 'true' means to load the driver on boot.
ORACLEASM_ENABLED=true
# ORACLEASM_UID: Default user owning the /dev/oracleasm mount point.
ORACLEASM_UID=oracle
# ORACLEASM_GID: Default group owning the /dev/oracleasm mount point.
ORACLEASM_GID=dba
# ORACLEASM_SCANBOOT: 'true' means fix disk perms on boot
ORACLEASM_SCANBOOT=true
# ORACLEASM_CLEARBOOT: 'true' means clean old disk perms on boot
ORACLEASM_CLEARBOOT=true
# ORACLEASM_SCANORDER: Matching patterns to order disk scanning
ORACLEASM_SCANORDER="dm" ##Updated it to “dm”
# ORACLEASM_SCANEXCLUDE: Matching patterns to exclude disks from scan
ORACLEASM_SCANEXCLUDE="sd" ##Updated it to “sd”
Make these changes in all the nodes.
Preparing & Configuring OCR and Voting Disk on Raw devices using Multipath or Udev:
On Linux platform, the LUNs presented from NAS or SCSI devices are identified with the device name (or path) /dev/sdx (/dev/sda, dev/sdb, etc). A common issue is that the device names (including multipath device name like /dev/mpath/mpath0 if multipath is enabled) can be changed when some disks are removed or added (as planned or due to I/O failures), SCSI or FC is reconfigured (like adding or removing ISCSI channels) or even host is rebooted. Even these devices are bound to raw with the consistent name (like /dev/raw/raw1) and assigned to OCR and voting disks, starting CRS can still fail because of the content of the device is not the OCR or the voting disk. To address this issue, you can use either multipath software or udev utility to setup devices with consistent name for OCR and voting disks.
multipath - multipath is a native Linux dynamic multipathing/failover software. An I/O path generally consists of an initiator port, target port, and LUN. Each permutation of this I/O is considered an independent path. Dynamic multipathing/failover tools (software or hardware) aggregate these independent paths into a single logical path. This path virtualization provides I/O load-balancing and non-disruptive failover across the host bus adapters (HBAs) or network interface card (NIC). Multipath software also can assign a consistent device name to LUNs as described in next section.
More info: http://en.wikipedia.org/wiki/Multipath_I/O
udev - udev is a new feature introduced in Linux 2.6. It provides a dynamic device directory containing only the files for actually present devices. It creates or removes device node files usually located in the /dev directory, or it renames network interfaces. As part of the hotplug subsystem, udev is executed if a kernel device is added or removed from the system. On device creation, udev reads the sysfs directory of the given device to collect device attributes like label, serial number or bus device number. These attributes may be used as keys to determine a unique name for the device.
More info: http://en.wikipedia.org/wiki/Udev
Configuring OCR & Voting Disk:
1. Choose the multipath device which will hold the ocr & voting disk files.
e.g /dev/mpath/mpath8
[root@oradbdev01 dev]# multipath -d -l mpath8
mpath8 (360a9800043346d77674a486332443238)
[size=2 GB][features="1 queue_if_no_path"][hwhandler="0"]
\_ round-robin 0 [active]
\_ 0:0:0:1 sdb 8:16 [active]
2. Rediscover all multipath devices on all nodes:
Note: Please shutdown all databases and ASM running on the affect nodes, as this will remove all multipath devices.
[root@oradbdev01 dev]# multipath –F
[root@oradbdev01 dev]# multipath –v2
As we have already created paritions, for probing these new partition devices on all the other nodes run:
[root@oradbdev02 dev]# partprobe
Instead you can also do:
/etc/init.d/iscsi reload
3. Make these changes to the /etc/sysconfig/rawdevices file, these are for OCR & Voting Disk.
[root@oradbdev01 dev]# more /etc/sysconfig/rawdevices
# This file and interface are deprecated.
# Applications needing raw device access should open regular
# block devices with O_DIRECT.
# raw device bindings
# format:
#
# example: /dev/raw/raw1 /dev/sda1
# /dev/raw/raw2 8 5
/dev/raw/raw1 /dev/mpath/mpath8p1
/dev/raw/raw2 /dev/mpath/mpath8p2
/dev/raw/raw3 /dev/mpath/mpath8p3
/dev/raw/raw8 /dev/mpath/mpath8p8
/dev/raw/raw4 /dev/mpath/mpath8p5
/dev/raw/raw5 /dev/mpath/mpath8p6
/dev/raw/raw6 /dev/mpath/mpath8p7
4. Bind the disk devices to the raw devices on all nodes as follow:
/sbin/service rawdevices restart
5. Run the following command as root on all nodes so that system automatically binds the devices listed in the rawdevices file when it reboots.
/sbin/chkconfig --level 345 rawdevices on
6. Setup the owner, group and permissions on the OCR and voting disks as root on all nodes as follow:
For OCR:
chown root:dba /dev/raw/raw1
chown root:dba /dev/raw/raw2
chmod 660 /dev/raw/raw1
chmod 660 /dev/raw/raw2
For voting disks:
chown oracle:dba /dev/raw/raw4
chown oracle:dba /dev/raw/raw5
chown oracle:dba /dev/raw/raw6
chmod 660 /dev/raw/raw4
chmod 660 /dev/raw/raw5
chmod 660 /dev/raw/raw6
7. For RHEL4, add the following lines in /etc/rc.d/rc.local as root on all nodes so that system will setup the owner, group and permissions properly after reboot.
# For OCR:
chown root:dba /dev/raw/raw1
chown root:dba /dev/raw/raw2
chmod 660 /dev/raw/raw1
chmod 660 /dev/raw/raw2
# For voting disks:
chown oracle:dba /dev/raw/raw4
chown oracle:dba /dev/raw/raw5
chown oracle:dba /dev/raw/raw6
chmod 660 /dev/raw/raw4
chmod 660 /dev/raw/raw5
chmod 660 /dev/raw/raw6
8. Initialize the OCR and voting disk as root on one node as follow.
dd if=/dev/zero of=/dev/mpath/mpath8p1 bs=125829120 count=1
dd if=/dev/zero of=/dev/mpath/mpath8p2 bs=125829120 count=1
dd if=/dev/zero of=/dev/mpath/mpath8p5 bs=20971520 count=1
dd if=/dev/zero of=/dev/mpath/mpath8p6 bs=20971520 count=1
dd if=/dev/zero of=/dev/mpath/mpath8p7 bs=20971520 count=1
10. Now most importantly setting up udev permission.
Incase you do not make this changes during CRS installation you will get error.
When you would specify the OCR device location in the CRS installation page it will give error that “The specified shared raw partition /dev/raw/raw1 may not have correct permission. Verify that the partition is owned by Oracle User.”
This is a misleading error, and will make us do changes to the actual permission of the devices. I had struggled with this error for a couple of day before I realized that I need to create a file /etc/udev/permissions.d/40-udev.permissions.
[oracle@oradbdev01 logs]$ more /etc/udev/permissions.d/40-udev.permissions
# dm devices
# ocr devices --inserted by apun
raw/raw1:root:dba:660
raw/raw2:root:dba:660
# voting disk --inserted by apun
raw/raw4:oracle:dba:660
raw/raw5:oracle:dba:660
raw/raw6:oracle:dba:660
Also comment out all entries for these devices in /etc/udev/permissions.d/50-udev.permissions file. The OS & oracle needs to refer to only /etc/udev/permissions.d/40-udev.permissions file.
Make these changes on all the RAC servers.
And reboot all the nodes.
This should complete all steps prior to starting any installation. Now you can download oracle crs and database software and start installation.
Important links to refer for more information;
http://www.oracle.com/technology/tech/linux/asmlib/index.html
