EBSQ Art of the Day

There are three restrictions on indexing and partitioning: a unique index cannot be local non-prefixed; a global non-prefixed index is not possible; a bitmap index cannot be global. Why these limitations? I suspect that they are there to prevent us from doing something idiotic.

This is the table used for all examples that follow:

CREATE TABLE EMP
      (EMPNO NUMBER(4) CONSTRAINT PK_EMP PRIMARY KEY,
       ENAME VARCHAR2(10),
       JOB VARCHAR2(9),
       MGR NUMBER(4),
       HIREDATE DATE,
       SAL NUMBER(7,2),
       COMM NUMBER(7,2),
       DEPTNO NUMBER(2) )
PARTITION BY HASH (EMPNO) PARTITIONS 4;

the usual EMP table, with a partitioning clause appended. It is of course a contrived example. Perhaps I am recruiting so many employees concurrently that a non-partitioned table has problems with buffer contention that can be solved only with hash partitioning.

Why can't I have a local non-prefixed unique index?
A local non-unique index is no problem, but unique is not possible:

orclz> create index enamei on emp(ename) local;

Index created.

orclz> drop index enamei;

Index dropped.

orclz> create unique index enamei on emp(ename) local;
create unique index enamei on emp(ename) local
                              *
ERROR at line 1:
ORA-14039: partitioning columns must form a subset of key columns of a UNIQUE index

You cannot get a around the problem by separating the index from the constraint (which is always good practice):

orclz> create index enamei on emp(ename) local;

Index created.

orclz> alter table emp add constraint euk unique (ename);
alter table emp add constraint euk unique (ename)
*
ERROR at line 1:
ORA-01408: such column list already indexed


orclz>

So what is the issue? Clearly it is not a technical limitation. But if it were possible, consder the implications for performance. When inserting a row, a unique index (or a non-unique index enforcing a unique constraint) must be searched to see if the key value already exists. For my little four partition table, that would mean four index searches: one of each local index partition. Well, OK. But what if the table were range partitioned into a thousand partitions? Then every insert would have to make a thousand index lookups. This would be unbelievably slow. By restricting unique indexes to global or local prefixed, Uncle Oracle is ensuring that we cannot create such an awful situation.

Why can't I have a global non-prefixed index?
Well, why would you want one? In my example, perhaps you want a global index on deptno, partitioned by mgr. But you can't do it:

orclz> create index deptnoi on emp(deptno) global partition by hash(mgr) partitions 4;
create index deptnoi on emp(deptno) global partition by hash(mgr) partitions 4
                                                                *
ERROR at line 1:
ORA-14038: GLOBAL partitioned index must be prefixed


orclz>

This index, if it were possible, might assist a query with an equality predicate on mgr and a range predicate on deptno: prune off all the non-relevant mgr partitions, then a range scan. But exactly the same effect would be achieved by using global nonpartitioned concatenated index on mgr and deptno. If the query had only deptno in the predicate, it woud have to search each partition of the putative global partitioned index, a process which would be just about identical to a skip scan of the nonpartitioned index. And of course the concatenated index could be globally partitioned - on mgr. So there you have it: a global non-prefixed index would give you nothing that is not available in other ways.

Why can't I have a global partitioned bitmap index?
This came up on the Oracle forums recently, https://forums.oracle.com/thread/2575623
Global indexes must be prefixed. Bearing that in mind, the question needs to be re-phrased: why would anyone ever want a prefixed partitioned bitmap index? Something like this:

orclz>
orclz> create bitmap index bmi on emp(deptno) global partition by hash(deptno) partitions 4;
create bitmap index bmi on emp(deptno) global partition by hash(deptno) partitions 4
                                       *
ERROR at line 1:
ORA-25113: GLOBAL may not be used with a bitmap index

orclz>

If this were possible, what would it give you? Nothing. You would not get the usual benefit of reducing contention for concurrent inserts, because of the need to lock entire blocks of a bitmap index (and therefore ranges of rows) when doing DML. Range partitioning a bitmap index would be ludicrous, because of the need to use equality predicates to get real value from bitmaps. Even with hash partitions, you would not get any benefit from partition pruning, because using equality predicates on a bitmap index in effect prunes the index already: that is what a bitmap index is for. So it seems to me that a globally partitioned bitmap index would deliver no benefit, while adding complexity and problems of index maintenance. So I suspect that, once again, Uncle Oracle is protecting us from ourselves.

Is there a technology limitation?
I am of course open to correction, but I cannot see a technology limitation that enforces any of these three impossibilities. I'm sure they are all technically possible. But Oracle has decided that, for our own good, they will never be implemented.
--
John Watson
Oracle Certified Master DBA
http://skillbuilders.com

The inverted table format can deliver fast and flexible query capabilities, but is not widely used. ADABAS is probably the most successful implementation, but how often do you see that nowadays? Following is a description of how to implement inverted structures within a relational database. All code run on Oracle Database 12c, release 12.1.0.1.

Consider this table and a few rows, that describe the contents of my larder:

create table food(id number,capacity varchar2(10),container varchar2(10),item varchar2(10));
insert into food values(1,'large','bag','potatoes');
insert into food values(2,'small','box','carrots');
insert into food values(3,'medium','tin','peas');
insert into food values(4,'large','box','potatoes');
insert into food values(5,'small','tin','carrots');
insert into food values(6,'medium','bag','peas');
insert into food values(7,'large','tin','potatoes');
insert into food values(8,'small','bag','carrots');
insert into food values(9,'medium','box','peas');

The queries I run against the table might be "how many large boxes have I?" or "give me all the potatoes, I don't care about how they are packed". The idea is that I do not know in advance what columns I will be using in my predicate: it could be any combination. This is a common issue in a data warehouse.
So how do I index the table to satisfy any possible query? Two obvious possibilities:
First, build an index on each column, and the optimizer can perform an index_combine operation on whatever columns happen to be listed in the predicate. But that means indexing every column - and the table might have hundreds of columns. No way can I do that.
Second, build a concatenated index across all the columns: in effect, use an IOT. That will give me range scan access if any of the predicated columns are in the leading edge of the index key followed by filtering on the rest of the predicate. Or if the predicate does not include the leading column(s), I can get skip scan access and filter. But this is pretty useless, too: there will be wildly divergent performance depending on the predicate.
The answer is to invert the table:

create table inverted(colname varchar2(10),colvalue varchar2(10),id number);
insert into inverted select 'capacity',capacity,id from food;
insert into inverted select 'container',container,id from food;
insert into inverted select 'item',item,id from food;

Now just one index on each table can satisfy all queries:

create index food_i on food(id);
create index inverted_i on inverted(colname,colvalue);

To retrieve all the large boxes:

orclz> set autotrace on explain
orclz> select * from food where id in
  2  (select id from inverted where colname='capacity' and colvalue='large'
  3  intersect
  4  select id from inverted where colname='container' and colvalue='box');

        ID CAPACITY   CONTAINER  ITEM
---------- ---------- ---------- ----------
         4 large      box        potatoes


Execution Plan
----------------------------------------------------------
Plan hash value: 1945359172

---------------------------------------------------------------------------------
| Id  | Operation                                | Name       | Rows  | Bytes | C
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                         |            |     3 |   141 |
|   1 |  MERGE JOIN                              |            |     3 |   141 |
|   2 |   TABLE ACCESS BY INDEX ROWID            | FOOD       |     9 |   306 |
|   3 |    INDEX FULL SCAN                       | FOOD_I     |     9 |       |
|*  4 |   SORT JOIN                              |            |     3 |    39 |
|   5 |    VIEW                                  | VW_NSO_1   |     3 |    39 |
|   6 |     INTERSECTION                         |            |       |       |
|   7 |      SORT UNIQUE                         |            |     3 |    81 |
|   8 |       TABLE ACCESS BY INDEX ROWID BATCHED| INVERTED   |     3 |    81 |
|*  9 |        INDEX RANGE SCAN                  | INVERTED_I |     3 |       |
|  10 |      SORT UNIQUE                         |            |     3 |    81 |
|  11 |       TABLE ACCESS BY INDEX ROWID BATCHED| INVERTED   |     3 |    81 |
|* 12 |        INDEX RANGE SCAN                  | INVERTED_I |     3 |       |
---------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("ID"="ID")
       filter("ID"="ID")
   9 - access("COLNAME"='capacity' AND "COLVALUE"='large')
  12 - access("COLNAME"='container' AND "COLVALUE"='box')

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)

orclz>

Or all the potatoes:

orclz> select * from food where id in
  2  (select id from inverted where colname='item' and colvalue='potatoes');

        ID CAPACITY   CONTAINER  ITEM
---------- ---------- ---------- ----------
         1 large      bag        potatoes
         4 large      box        potatoes
         7 large      tin        potatoes


Execution Plan
----------------------------------------------------------
Plan hash value: 762525239

---------------------------------------------------------------------------------
| Id  | Operation                              | Name       | Rows  | Bytes | Cos
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |            |     3 |   183 |
|   1 |  NESTED LOOPS                          |            |       |       |
|   2 |   NESTED LOOPS                         |            |     3 |   183 |
|   3 |    SORT UNIQUE                         |            |     3 |    81 |
|   4 |     TABLE ACCESS BY INDEX ROWID BATCHED| INVERTED   |     3 |    81 |
|*  5 |      INDEX RANGE SCAN                  | INVERTED_I |     3 |       |
|*  6 |    INDEX RANGE SCAN                    | FOOD_I     |     1 |       |
|   7 |   TABLE ACCESS BY INDEX ROWID          | FOOD       |     1 |    34 |
---------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   5 - access("COLNAME"='item' AND "COLVALUE"='potatoes')
   6 - access("ID"="ID")

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)
   - this is an adaptive plan

orclz>

Of course, consideration needs to be given to handling more complex boolean expressions; maintaining the inversion is going to take resources; and a query generator has to construct the inversion code and re-write the queries. But In principle, this structure can deliver indexed access for unpredictable predicates of any number of any columns, with no separate filtering operation. Can you do that with a normalized star schema? I don't think so.
I hope this little thought experiment has stimulated the little grey cells, and made the point that relational structures are not always optimal for all problems.
--
John Watson
Oracle Certified Master DBA
http://skillbuilders.com

Finding gaps is classic problem in PL/SQL. The basic concept is that you have some sort of numbers (like these: 1, 2, 3, 5, 6, 8, 9, 10, 15, 20, 21, 22, 23, 25, 26), where there’s supposed to be a fixed interval between the entries, but some entries could be missing. The gaps problem involves identifying the ranges of missing values in the sequence. For these numbers, the solution will be as follows:
START_GAP END_GAP
4 4
7 7
11 14
16 19
24 24

First, run the following code, to create tab1 table:


CREATE TABLE tab1
(
col1 INTEGER
);


Then, insert a few rows:


INSERT INTO tab1 VALUES (1);
INSERT INTO tab1 VALUES (2);
INSERT INTO tab1 VALUES (3);
INSERT INTO tab1 VALUES (5);
INSERT INTO tab1 VALUES (6);
INSERT INTO tab1 VALUES (8);
INSERT INTO tab1 VALUES (9);
INSERT INTO tab1 VALUES (10);
INSERT INTO tab1 VALUES (15);
INSERT INTO tab1 VALUES (20);
INSERT INTO tab1 VALUES (21);
INSERT INTO tab1 VALUES (22);
INSERT INTO tab1 VALUES (23);
INSERT INTO tab1 VALUES (25);
INSERT INTO tab1 VALUES (26);

COMMIT;

With data, you can take care of solving the gaps problem…

One of the most efficient solutions to the gaps problem involves using analytic functions (also known as window functions)


WITH aa AS
(SELECT col1 AS cur_value, LEAD (col1) OVER (ORDER BY col1) AS next_value
FROM tab1)
SELECT cur_value + 1 AS start_gap, next_value - 1 AS end_gap
FROM aa
WHERE next_value - cur_value > 1
ORDER BY start_gap


Using the LEAD function, you can return for each current col1 value (call it cur_value) the next value in the sequence (call it next_value). Then you can filter only pairs where the difference between the two is greater than the one.

I recently had the opportunity to talk with Tom Kyte (!), and in the course of our conversation, he really made me face up to the fact that the SQL syntax I use every day is frozen in time: I’m not making much use of the analytic functions and other syntax that Oracle has introduced since 8i.

Here’s a brief history of these additions to Oracle SQL, from Keith Laker, Oracle’s Product Manager for Analytical SQL:

8i	Window functions
9i	Rollup, grouping sets, cube, enhanced window functions
10g	SQL Model clause, statistical functions, partition outer join
11g	SQL Pivot clause, Recursive WITH, Listagg, Nth value
12c	Pattern matching, Top N

Not only do these make complex queries much, much simpler and easier, they are also much faster for the same result than non-analytic SQL, as Tom Kyte has shown repeatedly on his blog and in his books.

So, I was sold and I wanted to jump in with Recursive WITH. The WITH clause lets you define inline views to use across an entire query, and the coolest thing about this is that you can define the subquery recursively – so that the inline view calls itself.

Recursive WITH basic syntax:

WITH Tablename (col1, col2, ...) AS
(SELECT A, B, C... FROM dual                   --anchor member
UNION ALL
SELECT A', B', C'... from Tablename where...   --recursive member
)
select ... from Tablename where ...

Refactoring the Factorial

One fun thing about recursive WITH, aka recursive subquery refactoring, is the ease with which we can implement a recursive algorithm in SQL. Let’s warm up with a classic example of recursion: finding the factorial of a number. Factorial(n) = n! = 1*2*3*…*n . It’s a classic example because Factorial(n) can be defined recursively as:

Factorial(0) = 1
Factorial(n) = Factorial(n-1) * n

Here’s a first pass at implementing that directly in SQL to find the factorial of 5, using a recursive WITH subquery:

WITH Factorial (operand,total_so_far) AS
(SELECT 5 operand, 5 total_so_far FROM dual    -- Using anchor member to pass in "5"
UNION ALL
SELECT operand-1, total_so_far * (operand-1) FROM Factorial
WHERE operand > 1)
SELECT * FROM Factorial;

   OPERAND TOTAL_SO_F
---------- ----------
         5          5
         4         20
         3         60
         2        120
         1        120

and to display just the result, we select it from Factorial:

WITH Factorial (operand,total_so_far) AS
(SELECT 5 operand, 5 total_so_far FROM dual    -- Find the factorial of 5
UNION ALL
SELECT operand-1, total_so_far * (operand-1) FROM Factorial
WHERE operand > 1)
SELECT MAX(operand) || '! = ' || MAX(total_so_far) AS RESULT FROM Factorial;

RESULT
-----------------
5! = 120

Ahem! I have cheated a little for simplicity here. The query doesn’t take into account that Factorial(0) = 1:

WITH Factorial (operand,total_so_far) AS
(SELECT 0 operand, 0 total_so_far FROM dual    -- Find the factorial of 0
UNION ALL
SELECT operand-1, total_so_far * (operand-1) FROM Factorial
WHERE operand > 1)                             -- This is going to get me nowhere fast...
SELECT * FROM Factorial;

  OPERAND TOTAL_SO_F
---------- ----------
         0          0

To do it properly, we need to include Factorial(0) = 1 in the recursive subquery:

WITH Factorial (operand,total_so_far) AS
(SELECT 0 operand, 0 total_so_far FROM dual    -- Find the factorial of 0
UNION ALL
SELECT operand-1, 
CASE                                           -- Factorial (0) = 1
  WHEN operand=0 THEN 1
  ELSE (total_so_far * (operand-1))
  END
FROM Factorial
WHERE operand >= 0)
SELECT MAX(operand) || '! = ' || MAX(total_so_far) AS RESULT FROM Factorial;

RESULT
------------------------------------------------------------------------------------
0! = 1

We can also reverse direction and recursively build a table of factorials, multiplying as we go.
That’s the approach Lucas Jellema takes in his excellent blog post on factorials in SQL.

WITH Factorial (operand,output) AS
(SELECT 0 operand, 1 output FROM dual
UNION ALL
SELECT operand+1, output * (operand+1) FROM Factorial
WHERE operand < 5)
SELECT * FROM Factorial;

   OPERAND     OUTPUT
---------- ----------
         0          1
         1          1
         2          2
         3          6
         4         24
         5        120

There are two nice things about this approach: first, every row of the subquery result contains n and n! , and second, the rule that 0! = 1 is elegantly captured in the anchor member.

denrael ev’ew tahw gniylppA

Now let’s do something more interesting – reversing a string. Here’s some sample code in C from the CS 211 course at Cornell:

 public String reverseString(String word) {
     if(word == null || word.equals(""))
        return word;
     else
        return reverseString(word.substring(1, word.length())) + 
            word.substring(0,1);
  }

Let’s run through an example word to see how it works. For simplicity I’ll write reverseString(“word”) as r(word). Using “cat” as the word, stepping through the algorithm gives:

r(cat) = r(r(at))+c = r(r(r(t))+a+c = r(r(r(r())+t+a+c = ''+t+a+c = tac

Now to rewrite the same function in SQL. Using the same example string, “cat,” I want my recursively defined table to look like this:

in   out
--------
cat 
at   c
t    ac
     tac

In C, the initial letter in the word is the 0th letter, and in SQL, it’s the 1st letter. So the C expression word.substring(1,N) corresponds to SQL expression substr(word,2,N-1) . With that in mind, it’s easy to rewrite the C algorithm in SQL:

WITH WordReverse (INPUT, output) AS
  (SELECT 'CAT' INPUT, NULL output FROM dual
   UNION ALL
   SELECT substr(INPUT,2,LENGTH(INPUT)-1), substr(INPUT,1,1) || output
   FROM wordReverse
   WHERE LENGTH(INPUT) > 0
  )
SELECT * FROM wordReverse;

INPUT    OUTP
-------- ----
CAT
AT       C
T        AC
         TAC

NOTE: if using 11.2.0.3 or earlier, you might get “ORA-01489: result of string concatenation is too long” when reversing anything longer than a few letters. This is due to Bug 13876895: False ora-01489 on recursive WITH clause when concatenating columns. The bug is fixed in 11.2.0.4 and 12.1.0.1, and there’s an easy workaround: Cast one of the inputs to the concatenation as a varchar2(4000).

We could make this query user-friendlier by using a sql*plus variable to hold the input string. Another approach is to add an additional subquery to the with block to “pass in” parameters. I picked this up from Lucas Jellema’s post mentioned above, and wanted to give it a try, so I’ll add it in to my WordReverse query here.

Let’s use this to reverse a word that’s really quite atrocious:

WITH
params AS
  (SELECT 'supercalifragilisticexpialidocious' phrase FROM dual),
WordReverse (inpt, outpt) AS
  (SELECT phrase inpt, CAST(NULL AS varchar2(4000)) outpt FROM params
   UNION ALL
   SELECT substr(inpt,2,LENGTH(inpt)-1), substr(inpt,1,1) || outpt
   FROM wordReverse
   WHERE LENGTH(inpt) > 0
  )
SELECT phrase,outpt AS reversed FROM wordReverse, params
WHERE LENGTH(outpt) = LENGTH(phrase) ;

PHRASE                             REVERSED
---------------------------------- ----------------------------------------
supercalifragilisticexpialidocious suoicodilaipxecitsiligarfilacrepus

Now you might not have needed to know how to spell “supercalifragilisticexpialidocious” backwards, but one recursive requirement that does come up often is querying hierarchical data. I wrote a series of posts on hierarchical data recently, using Oracle’s CONNECT BY syntax. But recursive WITH can also be used to query hierarchical data. That’ll be the subject of my next post.

---

Republished with permission. Original URL: http://rdbms-insight.com/wp/?p=94

---

AIX is like an Unix environment awesome original, same to HP-Unix, and, if you have a plan to install Oracle RAC, you need to pay attention. I note some tips in this article to help.

1. Checking Operating System Packages

# lslpp -l bos.adt.base bos.adt.lib bos.adt.libm bos.perf.libperfstat 
bos.perf.perfstat bos.perf.proctools rsct.basic.rte rsct.compat.clients.rte 
xlC.aix61.rte

If not, install on AIX source by smity. It's easy, but remember, some packaged requires your IBM's account to download.

2. Verify UDP and TCP Kernel Parameters

# /usr/sbin/no -a | fgrep ephemeral

If you expect your workload to require a high number of ephemeral ports, then update
the UDP and TCP ephemeral port range to a broader range. For example:

# /usr/sbin/no -p -o tcp_ephemeral_low=9000 -o tcp_ephemeral_high=65500
# /usr/sbin/no -p -o udp_ephemeral_low=9000 -o udp_ephemeral_high=65500

3. Checking Resource Limits:
To ensure that these resource limits are honored, confirm that the line login
session required /usr/lib/security/pam_aix is set in /etc/pam.conf.For example:

dtsession auth required /usr/lib/security/pam_aix
dtlogin session required /usr/lib/security/pam_aix
ftp session required /usr/lib/security/pam_aix
imap session required /usr/lib/security/pam_aix
login session required /usr/lib/security/pam_aix
rexec session required /usr/lib/security/pam_aix
rlogin session required /usr/lib/security/pam_aix
rsh session required /usr/lib/security/pam_aix
snapp session required /usr/lib/security/pam_aix
su session required /usr/lib/security/pam_aix
swrole session required /usr/lib/security/pam_aix
telnet session required /usr/lib/security/pam_aix
xdm session required /usr/lib/security/pam_aix
OTHER session required /usr/lib/security/pam_prohibit
websm_rlogin session required /usr/lib/security/pam_aix
websm_su session required /usr/lib/security/pam_aix
wbem session required /usr/lib/security/pam_aix

4. Tuning AIX System Environment
Confirm the aio_maxreqs value using the procedure for your release:
AIX 6.1 and 7.1:

# ioo -o aio_maxreqs
aio_maxreqs = 65536

The aio is Asynchronous Input Output is an exciting parameter, I tried to control and modified it many times, but it's strongly to do from Oracle advices,

Oracle document refer: https://docs.oracle.com/database/121/AXDBI/app_manual.htm#AXDBI7880

5. Tuning Virtual Memory Manager

vmo -p -o minperm%=3
vmo -p -o maxperm%=90
vmo -p -o maxclient%=90
vmo -p -o lru_file_repage=0
vmo -p -o strict_maxclient=1
vmo -p -o strict_maxperm=0

Note: You must restart the system for these changes to take effect

6. Increase System block size allocation

# /usr/sbin/chdev -l sys0 -a ncargs='128'

7. Configure SSH LoginGraceTime Parameter
On AIX systems, the OpenSSH parameter LoginGraceTime by default is commented
out, and the default behavior of OpenSSH on AIX can sometimes result in timeout
errors. To avoid these errors, complete the following procedure:
7.1. Log in as root.
7.2. Using a text editor, open the OpenSSH configuration file /etc/ssh/sshd_config.
7.3. Locate the comment line #LoginGraceTime 2m.
7.4. Uncomment the line, and change the value to 0 (unlimited). For example:
LoginGraceTime 0
7.5. Save /etc/ssh/sshd_config.
7.6. Restart SSH.

8. Setting priviledge to Oracle ASM Luns
Same to Solaris, HP-Unix. Remember, when you've got failure of ASM configuration, you need to flush out the disk's slice/partition by OS command "dd". And the slice/partition/LUN allocated from storage to IBM, has got different first alphabet to other platform. The alphabet begins by "r", example:

7.1 ORC and Voting disk
# chown grid:asmadmin /dev/rhdisk5 -> OCR
# chmod 660 /dev/rhdisk5 
# chown grid:asmadmin /dev/rhdisk6 -> Voting Disk
# chmod 660 /dev/rhdisk6
7.2 Datafile, Archivelog and Backup 
# chown grid:asmadmin /dev/rhdisk2
# chmod 660 /dev/rhdisk2
# chown grid:asmadmin /dev/rhdisk3
# chmod 660 /dev/rhdisk3
# chown grid:asmadmin /dev/rhdisk4
# chmod 660 /dev/rhdisk4
# chown grid:asmadmin /dev/rhdisk9
# chmod 660 /dev/rhdisk9
# chown grid:asmadmin /dev/rhdisk10
# chmod 660 /dev/rhdisk10

9. Enable simultaneous access to a disk device from multiple nodes
To enable simultaneous access to a disk device from multiple nodes, you must set the appropriate Object Data Manager (ODM) attribute, depending on the type of reserve attribute used by your disks. The following section describes how to perform this task using hdisk logical names

8.1. determine the reserve setting your disks use, enter the following command,where n is the hdisk device number

# lsattr -E -l hdiskn | grep reserve_

The response is either a reserve_lock setting, or a reserve_policy setting. If the attribute is reserve_lock, then ensure that the setting is reserve_lock = no. If the attribute is reserve_policy, then ensure that the setting is reserve_policy = no_reserve.

8.2. If necessary, change the setting with the chdev command using the following syntax, where n is the hdisk device number:

chdev -l hdiskn -a [ reserve_lock=no | reserve_policy=no_reserve ]

For example:

# chdev -l hdisk5 -a reserve_lock=no
# chdev -l hdisk5 -a reserve_policy=no_reserve

8.3. Enter commands similar to the following on any node to clear the PVID from each disk device that you want to use:

# /usr/sbin/chdev -l hdiskn -a pv=clear

When you are installing Oracle Clusterware, you must enter the paths to the appropriate device files when prompted for the path of the OCR and Oracle Clusterware voting disk. For example: /dev/rhdisk10

9.Configure Shell Limits
9.1. Add the following lines to the /etc/security/limits file:

default:
fsize = -1
core = 2097151
cpu = -1
data = -1
rss = -1
stack = -1
nofiles = -1

9.2.Enter the following command to list the current setting for the maximum number of process allowed by the Oracle software user:

/usr/sbin/lsattr -E -l sys0 -a maxuproc

If necessary, change the maxuproc setting using the following command:

/usr/sbin/chdev -l sys0 -a maxuproc=16384

10. Configure User Process Parameters (Verify that the maximum number of processes allowed for each user is set to 2048 or greater)
Enter the following command:

# smit chgsys

Verify that the value shown for Maximum number of PROCESSES allowed for each user is greater than or equal to 2048. If necessary, edit the existing value.
When you have finished making changes, press Enter, then Esc+0 (Exit) to exit.

11. Configure Network Tuning Parameters:
To check the current values of the network tuning parameters:

# no -a | more

If the system is running in compatibility mode, then follow these steps to change the parameter values:

Enter commands similar to the following to change the value of each parameter:

# no -o parameter_name=value

For example:

# no -o udp_recvspace=655360

Add entries similar to the following to the /etc/rc.net file for each parameter that you changed in the previous step:

if [ -f /usr/sbin/no ] ; then
/usr/sbin/no -o udp_sendspace=65536
/usr/sbin/no -o udp_recvspace=655360
/usr/sbin/no -o tcp_sendspace=65536
/usr/sbin/no -o tcp_recvspace=65536
/usr/sbin/no -o rfc1323=1
/usr/sbin/no -o sb_max=4194304
/usr/sbin/no -o ipqmaxlen=512
fi

For the ISNO parameter tcp_sendspace, use the following command to set it:

# ifconfig en0 tcp_sendspace 65536

By adding these lines to the /etc/rc.net file, the values persist when the system restarts.

12. Automatic SSH configuration
By default, OUI searches for SSH public keys in the directory /usr/local/etc/, and ssh-keygen binaries in /usr/local/bin. However, on AIX, SSH public keys typically are located in the path /etc/ssh, and ssh-keygen binaries are located in the path /usr/bin. To ensure that OUI can set up SSH, use the following command to create soft links:

# ln -s /etc/ssh /usr/local/etc
# ln -s /usr/bin /usr/local/bin

In rare cases, Oracle Clusterware installation may fail during the "AttachHome" operation when the remote node closes the SSH connection. To avoid this problem, set the following parameter in the SSH daemon configuration file /etc/ssh/sshd_config on all cluster nodes to set the timeout wait to unlimited:

LoginGraceTime 0

13. Shell Limit
Adding these line in /etc/security/limits

default:
fsize = -1
core = 2097151
cpu = -1
data = -1
rss = -1
stack = -1
nofiles = -1

14. Create groups and users

# mkgroup -'A' id='1000' adms='root' oinstall
# mkgroup -'A' id='1031' adms='root' dba
# mkgroup -'A' id='1032' adms='root' oper
# mkgroup -'A' id='1020' adms='root' asmadmin
# mkgroup -'A' id='1022' adms='root' asmoper
# mkgroup -'A' id='1021' adms='root' asmdba
# mkuser id='1100' pgrp='oinstall' groups='dba,asmadmin,asmoper,asmdba' home='/portalgrid/grid' grid
# mkuser id='1101' pgrp='oinstall' groups='dba,oper,asmdba' home='/portaloracle/oracle' oracle

# mkdir -p /portalapp/app/11.2.0/grid
# mkdir -p /portalapp/app/grid
# mkdir -p /portalapp/app/oracle

# chown grid:oinstall /portalapp/app/11.2.0/grid <- GRID_HOME
# chown grid:oinstall /portalapp/app/grid <- GRID_BASE (ORACLE_BASE for Grid user)
# chown -R grid:oinstall /portalapp
# chown oracle:oinstall /portalapp/app/oracle
# chmod -R 775 /portalapp/

15. Setting the profile
15.1. Grid Profile

export TEMP=/tmp
export TMP=/tmp
export TMPDIR=/tmp
umask 022

export ORACLE_HOSTNAME=portal1
export ORACLE_BASE=/portalapp/app/grid
export ORACLE_HOME=/portalapp/app/11.2.0/grid
export GRID_HOME=/portalapp/app/11.2.0/grid
export CRS_HOME=/portalapp/app/11.2.0/grid
export ORACLE_SID=+ASM1

export PATH=$ORACLE_HOME/bin:$PATH
export LD_LIBRARY_PATH=$ORACLE_HOME/lib

16. Prevent Xserver does not display correct term

# startsrc -x

17. Create the following softlink needed for some Oracle utilites

# ln -s /usr/sbin/lsattr /etc/lsattr

To check existing capabilities, enter the following command as root; in this example,
the Grid installation user account is grid:

# /usr/bin/lsuser -a capabilities grid

To add capabilities, enter a command similar to the following:

# /usr/bin/chuser capabilities=CAP_NUMA_ATTACH,CAP_BYPASS_RAC_VMM,CAP_PROPAGATE grid

18. Remember to run the Installation fixup scripts

$ ./runcluvfy.sh stage -pre crsinst -n node -fixup -verbose

With Oracle Clusterware 11g release 2, Oracle Universal Installer (OUI) detects when the minimum requirements for an installation are not met, and creates shell scripts,
called fixup scripts, to finish incomplete system configuration steps. If OUI detects an incomplete task, then it generates fixup scripts (runfixup.sh). You can run the fixup script after you click the Fix and Check Again Button.

19. In the installation progressing, when root.sh at node 2, can the error such as "CRS appear in node 1, did not attemp to stop cluster, re-join cluster, by pass and continue installation.
- If Xterm did not occur, then do: $ export ORACLE_TERM=dtterm
- Manually export ORACLE_BASE, ORACLE_HOME when make an installation before running runInstaller.sh
- If /tmp is too small <500MB, then make a private directory point to other directory, change owner to grid, oracle user, example:

A. Grid

# cd /portallog
# mkdir /portallog/tmp
# chown -R grid:oinstall /portallog/tmp

B. Oracle

# cd /portal
# mkdir tmp
# chown -R oracle:dba /portal/tmp 

C. Export

# export TMP=/portallog/tmp
# export TEMPDIR=/portallog/tmp
# export TMPDIR=/portallog/tmp

Hope this help.
End.
TAT