Oracle DBA Scripts

Understand Oracle Locking

2011-10-21T04:23:00.000-07:00

Overview

In multi-user systems, many users may update the same information at the same time. Locking allows only one user to update a particular data block; another person cannot modify the same data.
The basic idea of locking is that when a user modifies data through a transaction, that data is locked by that transaction until the transaction is committed or rolled back. The lock is held until the transaction is complete - this known as data concurrency.
The second purpose of locking is to ensure that all processes can always access (read) the original data as they were at the time the query began (uncommited modification), This is known as read consistency.
Although locks are vital to enforce database consistency, they can create performance problems. Every time one process issues a lock, another user may be shut out from processing the locked row or table. Oracle allows to lock whatever resources you need - a single row, many rows, an entire table, even many tables. But the larger the scope of the lock, the more processes you potentially shut out.

Oracle provides two different levels of locking: Row Level Lock and Table Level Lock.

Row-Level Locking

With a row-level locking strategy, each row within a table can be locked individually. Locked rows can be updated only by the locking process. All other rows in the table are still available for updating by other processes. Of course, other processes continue to be able to read any row in the table, including the one that is actually being updated. When other processes do read updated rows, they see only the old version of the row prior to update (via a rollback segment) until the changes are actually committed. This is known as a consistent read.
When a process places a row level lock on a record, what really happens?
First, a data manipulation language (DML) lock is placed over the row. This lock prevents other processes from updating (or locking) the row. This lock is released only when the locking process successfully commits the transaction to the database (i.e., makes the updates to that transaction permanent) or when the process is rolled back.

Next, a data dictionary language (DDL) lock is placed over the table to prevent structural alterations to the table. For example, this type of lock keeps the DBA from being able to remove a table by issuing a DROP statement against the table. This lock is released only when the locking process successfully commits the transaction to the database or when the process is rolled back.

Table-Level Locking

With table-level locking, the entire table is locked as an entity. Once a process has locked a table, only that process can update (or lock) any row in the table. None of the rows in the table are available for updating by any other process. Of course, other processes continue to be able to read any row in the table, including the one that is actually being updated.
How does table-level locking work?
The first DML operation that needs to update a row in a table obtains what's called a Row Share Exclusive lock over the entire table. All other query-only processes needing access to the table are informed that they must use the rollback information for the locking process. The lock is released only when the locking process successfully commits the transaction to the database or when the process is rolled back.

Releasing Locks

Many users believe that they are the only users on the system - at least the only ones who count. Unfortunately, this type of attitude is what causes locking problems. We've often observed applications that were completely stalled because one user decided to go to lunch without having committed his or her changes. Remember that all locking (row or table) will prevent other users from updating information. Every application has a handful of central, core tables. Inadvertently locking such tables can affect many other people in a system.
Many users, and some programmers, don't understand that terminating a process does not always release locks. Switching off your workstation before you go home does not always release locks. Locks are released only when changes are committed or rolled back. A user's action is the only thing that distinguishes between committing, aborting, and rolling back changes. Make it a priority to train your users to commit or roll back all outstanding changes before leaving their current screens.

Modes of Locking

Oracle uses two modes of locking in a multi-user database:
Exclusive lock mode (X) prevents the associates resource from being shared. This lock mode is obtained to modify data. The first transaction to lock a resource exclusively is the only transaction that can alter the resource until the exclusive lock is released.

Share lock mode (S) allows the associated resource to be shared, depending on the operations involved. Multiple users reading data can share the data, holding share locks to prevent concurrent access by a writer (who needs an exclusive lock). Several transactions can acquire share locks on the same resource.

Exclusive Locks

SQL Statement

Mode of Lock

SELECT ... FROM table...
No Lock

INSERT INTO table ...
RX

UPDATE table ...
RX

DELETE FROM table ...
RX

LOCK TABLE table IN ROW EXCLUSIVE MODE
RX

LOCK TABLE table IN SHARE ROW EXCLUSIVE MODE
SRX

LOCK TABLE table IN EXCLUSIVE MODE
X

Share Locks

SQL Statement

Mode of Lock

SELECT ... FROM table FOR UPDATE OF ... RS
LOCK TABLE table IN ROW SHARE MODE RS
LOCK TABLE table IN SHARE MODE S
LOCK TABLE table IN SHARE ROW EXCLUSIVE MODE SRX
RS:   Row Share
RX:   Row Exclusive
S:    Share
SRX: Share Row Exclusive
X:    Exclusive

Description of each Lock Mode

The following sections explain each mode of lock, from least restrictive to most restrictive.

Row Share Table Locks (RS)

A row share table lock (also sometimes called a subshare table lock, SS) indicates that the transaction holding the lock on the table has locked rows in the table and intends to update them. A row share table lock is automatically acquired for a table when one of the following SQL statements is executed:
SELECT ... FROM table ... FOR UPDATE OF ... ;
LOCK TABLE table IN ROW SHARE MODE;
A row share table lock is the least restrictive mode of table lock, offering the highest degree of concurrency for a table.

Permitted Operations:

A row share table lock held by a transaction allows other transactions to:
SELECT (query the table)
INSERT, UPDATE, DELETE
or lock rows concurrently in the same table.
Therefore, other transactions can obtain simultaneous row share, row exclusive, share, and share row exclusive table locks for the same table.

Prohibited Operations:

A row share table lock held by a transaction prevents other transactions from exclusive write access to the same table.

When to Lock with ROW SHARE Mode:

Your transaction needs to prevent another transaction from acquiring an intervening share, share row, or exclusive table lock for a table before the table can be updated in your transaction. If another transaction acquires an intervening share, share row, or exclusive table lock, no other transactions can update the table until the locking transaction commits or rolls back.
Your transaction needs to prevent a table from being altered or dropped before the table can be modified later in your transaction.

We use the EMP table for the next examples.
     EMPNO ENAME      JOB
---------- ---------- ---------
      7369 Smith      CLERK
      7499 Allen      SALESMAN
      7521 Ward       SALESMAN
      7566 Jones      MANAGER
      7654 Martin     SALESMAN
      7698 Blake      MANAGER
      7782 Clark      MANAGER
      7788 Scott      ANALYST
      7839 King       PRESIDENT
      7844 Turner     SALESMAN
      7876 Adams      CLERK
      7900 James      TEST
      7902 Ford       ANALYST
      7934 Miller     CLERK

Session 1

Session 2

select job from emp
where job = 'CLERK'
   for update of empno;
OK

select job from emp
where job = 'CLERK'
   for update of empno;
Waiting ....

select job from emp
where job = 'MANAGER'
   for update of empno;
OK

lock table emp in share mode;
OK

lock table emp in exclusive mode;
Waiting ....

insert into emp (empno,ename)
values (9999,'Test');
OK

delete from emp where empno = 9999;
OK

delete from emp where empno = 7876;
Waiting .... (Blocked by Session 1)

update emp set job = 'CLIMBER'
where empno = 7876;
Waiting .... (Blocked by Session 1)

A first look about the locking situation can be found in DBA_LOCKS
SELECT SUBSTR(TO_CHAR(session_id),1,5) "SID",
       SUBSTR(lock_type,1,15) "Lock Type",
       SUBSTR(mode_held,1,15) "Mode Held",
       SUBSTR(blocking_others,1,15) "Blocking?"
FROM dba_locks
/

SID   Lock Type       Mode Held       Blocking?
----- --------------- --------------- ---------------
95    Transaction     Exclusive       Blocking      <- This is Session 1
95    DML             Row-S (SS)      Not Blocking
98    DML             Row-X (SX)      Not Blocking <- This is Session 2
98    Transaction     None            Not Blocking
110   Temp Segment    Row-X (SX)      Not Blocking
111   RS              Row-S (SS)      Not Blocking
111   Control File    Row-S (SS)      Not Blocking
111   XR              Null            Not Blocking
112   Redo Thread     Exclusive       Not Blocking
113   Media Recovery Share           Not Blocking
113   Media Recovery Share           Not Blocking
113   Media Recovery Share           Not Blocking
113   Media Recovery Share           Not Blocking
113   Media Recovery Share           Not Blocking
113   Media Recovery Share           Not Blocking
113   Media Recovery Share           Not Blocking
113   PW              Row-X (SX)      Not Blocking

Row Exclusive Table Locks (RX)

A row exclusive table lock (also called a subexclusive table lock, SX) generally indicates that the transaction holding the lock has made one or more updates to rows in the table. A row exclusive table lock is acquired automatically for a table modified by the following types of statements:
INSERT INTO table ... ;
UPDATE table ... ;
DELETE FROM table ... ;
LOCK TABLE table IN ROW EXCLUSIVE MODE;
A row exclusive table lock is slightly more restrictive than a row share table lock.

Permitted Operations:

A row exclusive table lock held by a transaction allows other transactions to
SELECT (query the table)
INSERT, UPDATE, DELETE
or lock rows concurrently in the same table.
Therefore, row exclusive table locks allow multiple transactions to obtain simultaneous row exclusive and row share table locks for the same table.

Prohibited Operations:

A row exclusive table lock held by a transaction prevents other transactions from manually locking the table for exclusive reading or writing. Therefore, other transactions cannot concurrently lock the table using the following statements:
LOCK TABLE table IN SHARE MODE;
LOCK TABLE table IN EXCLUSIVE MODE;

When to Lock with ROW EXCLUSIVE Mode:

This is the Default Locking Behaviour of Oracle.

Session 1

Session 2

update emp
set ename = 'Zahn';
OK

lock table emp in exclusive mode;
Waiting ....

Share Table Locks (S)

A share table lock is acquired automatically for the table specified in the following statement:
LOCK TABLE table IN SHARE MODE;

Permitted Operations:

A share table lock held by a transaction allows other transactions only to
to SELECT (query the table)
to lock specific rows with SELECT ... FOR UPDATE
or to execute LOCK TABLE ... IN SHARE MODE
statements successfully. No updates are allowed by other transactions. Multiple transactions can hold share table locks for the same table concurrently. In this case, no transaction can update the table (even if a transaction holds row locks as the result of a SELECT statement with the FOR UPDATE clause). Therefore, a transaction that has a share table lock can update the table only if no other transactions also have a share table lock on the same table.

Prohibited Operations:

A share table lock held by a transaction prevents other transactions from modifying the same table and from executing the following statements:
LOCK TABLE table IN SHARE ROW EXCLUSIVE MODE;
LOCK TABLE table IN EXCLUSIVE MODE;
LOCK TABLE table IN ROW EXCLUSIVE MODE;

When to Lock with SHARE Mode

Your transaction only queries the table, and requires a consistent set of the table's data for the duration of the transaction.
You can hold up other transactions that try to update the locked table, until all transactions that hold SHARE locks on the table either commit or roll back.
Other transactions may acquire concurrent SHARE table locks on the same table, also allowing them the option of transaction-level read consistency.
Caution:
Your transaction may or may not update the table later in the same transaction. However, if multiple transactions concurrently hold share table locks for the same table, no transaction can update the table (even if row locks are held as the result of a SELECT... FOR UPDATE statement). Therefore, if concurrent share table locks on the same table are common, updates cannot proceed and deadlocks are common. In this case, use share row exclusive or exclusive table locks instead.

Session 1

Session 2

lock table emp
in share mode;
OK
update emp
set ename = 'Zahn'
where empno = 7900;
commit;
OK
lock table emp
in share mode;
OK
This and other Transactions have to wait until Session 2 commits the Transaction.
update emp
set ename = 'MÃ¼ller'
where empno = 7900;
Waiting ....

select * from emp;
OK

This and other Transactions have to wait until Session 1 commits the Transaction.

This and other Transactions can
get a Share Lock (Lock Switch).

lock table emp
in share mode;
OK

For example, assume that two tables, emp and budget, require a consistent set of data in a third table, dept. For a given department number, you want to update the information in both of these tables, and ensure that no new members are added to the department between these two transactions.
Although this scenario is quite rare, it can be accommodated by locking the dept table in SHARE MODE, as shown in the following example. Because the dept table is rarely updated, locking it probably does not cause many other transactions to wait long.
LOCK TABLE dept IN SHARE MODE; /* Other Transactions have to wait */
UPDATE emp
SET sal = sal * 1.1
WHERE deptno IN
(SELECT deptno FROM dept WHERE loc = 'DALLAS');

UPDATE budget
SET Totsal = Totsal * 1.1
WHERE deptno IN
(SELECT deptno FROM dept WHERE Loc = 'DALLAS');

COMMIT; /* This releases the lock */

Exclusive Table Locks (X)

An exclusive table lock is the most restrictive mode of table lock, allowing the transaction that holds the lock exclusive write access to the table. An exclusive table lock is acquired for a table as follows:
LOCK TABLE table IN EXCLUSIVE MODE;

Permitted Operations:

Only one transaction can obtain an exclusive table lock for a table. An exclusive table lock permits other transactions only to query the table.

Prohibited Operations:

An exclusive table lock held by a transaction prohibits other transactions from performing any type of DML statement or placing any type of lock on the table.

Be careful to use an EXCLUSIVE lock!

Your transaction requires immediate update access to the locked table. When your transaction holds an exclusive table lock, other transactions cannot lock specific rows in the locked table.
Your transaction also ensures transaction-level read consistency for the locked table until the transaction is committed or rolled back.
You are not concerned about low levels of data concurrency, making transactions that request exclusive table locks wait in line to update the table sequentially.

Session 1

Session 2

lock table emp
in exclusive mode;
OK
update emp
set ename = 'Zahn'
where empno = 7900;
commit;
OK
lock table emp
in exclusive mode;
OK

select * from emp;
OK

This and other Transactions have to wait until Session 1 commits the Transaction.

This and other Transactions cannot
get any other Lock (No Lock Switch).
lock table emp
in share mode;
Waiting ....
lock table emp
in exclusive mode; Waiting ....
update emp
set ename = 'Zahn'
where empno = 7900; Waiting ....

Data Lock Conversion Versus Lock Escalation

A transaction holds exclusive row locks for all rows inserted, updated, or deleted within the transaction. Because row locks are acquired at the highest degree of restrictiveness, no lock conversion is required or performed.
Oracle automatically converts a table lock of lower restrictiveness to one of higher restrictiveness as appropriate. For example, assume that a transaction uses a SELECT statement with the FOR UPDATE clause to lock rows of a table. As a result, it acquires the exclusive row locks and a row share table lock for the table. If the transaction later updates one or more of the locked rows, the row share table lock is automatically converted to a row exclusive table lock.
Lock escalation occurs when numerous locks are held at one level of granularity (for example, rows) and a database raises the locks to a higher level of granularity (for example, table). For example, if a single user locks many rows in a table, some databases automatically escalate the user's row locks to a single table. The number of locks is reduced, but the restrictiveness of what is being locked is increased.
Oracle never escalates locks. Lock escalation greatly increases the likelihood of deadlocks.

Deadlocks

A deadlock can occur when two or more users are waiting for data locked by each other. Deadlocks prevent some transactions from continuing to work. The next example illustrates two transactions in a deadlock.

Example

Session 1

Session 2
Time

update emp set
sal = sal * 1.1
where empno = 7369;

1 row updated.
update emp set
sal = sal * 1.1
where empno = 7934;

ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource

update emp set
mgr = 1342
where empno = 7934;

1 row updated.
update emp set
mgr = 1342
where empno = 7369;

Waiting ...

A

B

C

In the example, no problem exists at time point A, as each transaction has a row lock on the row it attempts to update. Each transaction proceeds without being terminated. However, each tries next to update the row currently held by the other transaction. Therefore, a deadlock results at time point B, because neither transaction can obtain the resource it needs to proceed or terminate. It is a deadlock because no matter how long each transaction waits, the conflicting locks are held.

Automatic Deadlock Detection

Oracle performs automatic deadlock detection for enqueue locking deadlocks. Deadlock detection is initiated whenever an enqueue wait times out, if the resource type required is regarded as deadlock sensitive, and if the lock state for the resource has not changed. If any session that is holding a lock on the required resource in an incompatible mode is waiting directly or indirectly for a resource that is held by the current session in an incompatible mode, then a deadlock exists.
If a deadlock is detected, the session that was unlucky enough to find it aborts its lock request and rolls back its current statement in order to break the deadlock. Note that this is a rollback of the current statement only, not necessarily the entire transaction. Oracle places an implicit savepoint at the beginning of each statement, called the default savepoint, and it is to this savepoint that the transaction is rolled back in the first case. This is enough to resolve the technical deadlock. However, the interacting sessions may well remain blocked.

ORA-60 error in ALERT.LOG

An ORA-60 error is returned to the session that found the deadlock, and if this exception is not handled, then depending on the rules of the application development tool, the entire transaction is normally rolled back, and a deadlock state dump written to the user dump destination directory. This, of course, resolves the deadlock entirely. The enqueue deadlocks statistic in V$SYSSTAT records the number of times that an enqueue deadlock has been detected.
select name, value
from v$sysstat
where name = 'enqueue deadlocks';

NAME VALUE
------------------------------------------------------------ ----------
enqueue deadlocks 1

How to avoid Deadlocks

Application developers can eliminate all risk of enqueue deadlocks by ensuring that transactions requiring multiple resources always lock them in the same order. However, in complex applications, this is easier said than done, particularly if an ad hoc query tool is used. To be safe, you should adopt a strict locking order, but you must also handle the ORA-60 exception appropriately. In some cases it may be sufficient to pause for three seconds, and then retry the statement. However, in general, it is safest to roll back the transaction entirely, before pausing and retrying.

Referential Integrity Locks (RI Locks)

With the introduction of automated referential integrity (RI) came a whole new suite of locking problems. What seems at first to be a DBA's blessing can turn out to be an absolute nightmare when the DBA doesn't fully understand the implications of this feature. Why is this so?
RI constraints are validated by the database via a simple SELECT from the dependent (parent) table in question-very simple, very straightforward. If a row is deleted or a primary key is modified within the parent table, all associated child tables need to be scanned to make sure no orphaned records will result. If a row is inserted or the foreign key is modified, the parent table is scanned to ensure that the new foreign key value(s) is valid. If a DELETE CASCADE clause is included, all associated child table records are deleted. Problems begin to arise when we look at how the referential integrity is enforced.
Oracle assumes the existence of an index over every foreign key within a table. This assumption is valid for a primary key constraint or even a unique key constraint but a little presumptuous for every foreign key.

Index or no Index on Foreign Key's ?

If an index exists on the foreign key column of the child table, no DML locks, other than a lock over the rows being modified, are required.
If the index is not created, a share lock is taken out on the child table for the duration of the transaction.
The referential integrity validation could take several minutes or even hours to resolve. The share lock over the child table will allow other users to simultaneously read from the table, while restricting certain types of modification. The share lock over the table can actually block other normal, everyday modification of other rows in that table.
You can use the script: show_missing_fk_index.sql to check unindexed foreign keys:
SQL> start show_missing_fk_index.sql

Please enter Owner Name and Table Name. Wildcards allowed (DEFAULT: %)

eg.: SCOTT, S% OR %
eg.: EMP, E% OR %

Owner <%>: SCOTT
Tables <%>:

Unindexed Foreign Keys owned by Owner: SCOTT

Table Name 1. Column Constraint Name
------------------------ ------------------------ ---------------
EMP DEPTNO FK_EMP_DEPT

What is so dangerous about a Cascading Delete ?

Oracle allows to enhance a referential integrity definition to included cascading deletion. If a row is deleted from a parent table, all of the associated children will be automatically purged. This behavior obviously will affect an application's locking strategy, again circumnavigating normal object locking, removing control from the programmer.
What is so dangerous about a cascading delete? A deleted child table might, in turn, have its own child tables. Even worse, the child tables could have table-level triggers that begin to fire. What starts out as a simple, single-record delete from a harmless table could turn into an uncontrollable torrent of cascading deletes and stored database triggers.
DELETE CASCADE constraints can be found with the following script:
SQL> SELECT OWNER,
       CONSTRAINT_NAME,
       CONSTRAINT_TYPE,
       TABLE_NAME,
       DELETE_RULE
FROM USER_CONSTRAINTS
WHERE DELETE_RULE IS NOT NULL;
CONSTRAINT_NAME                C TABLE_NAME        DELETE_RU
------------------------------ - ----------------- ---------
FK_EMP_DEPT                    R EMP               CASCADE

Blocking Locks

Oracle resolves true enqueue deadlocks so quickly that overall system activity is scarcely affected. However, blocking locks can bring application processing to a standstill. For example, if a long-running transaction takes a shared mode lock on a key application table, then all updates to that table must wait.
There are numerous ways of attempting to diagnose blocking lock situations, normally with the intention of killing the offending session.
Blocking locks are almost always TX (transaction) locks or TM (table) locks . When a session waits on a TX lock, it is waiting for that transaction to either commit or roll back. The reason for waiting is that the transaction has modified a data block, and the waiting session needs to modify the same part of that block. In such cases, the row wait columns of V$SESSION can be useful in identifying the database object, file, and block numbers concerned, and even the row number in the case of row locks. V$LOCKED_OBJECT can then be used to obtain session information for the sessions holding DML locks on the crucial database object. This is based on the fact that sessions with blocking TX enqueue locks always hold a DML lock as well, unless DML locks have been disabled.
It may not be adequate, however, to identify a single blocking session, because it may, in turn, be blocked by another session. To address this requirement, Oracle's UTLLOCKT.SQL script gives a tree-structured report showing the relationship between blocking and waiting sessions. Some DBAs are loath to use this script because it creates a temporary table, which will block if another space management transaction is caught behind the blocking lock. Although this is extremely unlikely, the same information can be obtained from the DBA_WAITERS view if necessary. The DBA_WAITERS view is created by Oracle's catblock.sql script.
Some application developers attempt to evade blocking locks by preceding all updates with a SELECT FOR UPDATE NOWAIT or SELECT FOR UPDATE SKIP LOCKED statement. However, if they allow user interaction between taking a sub-exclusive lock in this way and releasing it, then a more subtle blocking lock situation can still occur. If a user goes out to lunch while holding a sub-exclusive lock on a table, then any shared lock request on the whole table will block at the head of the request queue, and all other lock requests will queue behind it.
Diagnosing such situations and working out which session to kill is not easy, because the diagnosis depends on the order of the waiters. Most blocking lock detection utilities do not show the request order, and do not consider that a waiter can block other sessions even when it is not actually holding any locks.

Lock Detection Scripts

The following scripts can be used to track and identify blocking locks. The scripts shows the following lock situation.
Session 1 Session 2
select empno
from emp for update of empno;
update emp set ename = 'MÃ¼ller'
where empno = 7369;

This script shows actual DML-Locks (incl. Table-Name), WAIT = YES means
that users are waiting for a lock.
WAI OSUSER PROCESS LOCKER T_OWNER OBJECT_NAME   PROGRAM
--- ------- -------- ------- -------- ------------- --------------
NO zahn    8935     SCOTT   -        Record(s)     sqlplus@akira
YES zahn    8944     SCOTT   -        Record(s)     sqlplus@akira
NO zahn    8935     SCOTT   SCOTT    EMP           sqlplus@akira
NO zahn    8944     SCOTT   SCOTT    EMP           sqlplus@akira

This script show users waiting for a lock, the locker and the SQL-Command they are waiting for a lock, the osuser, schema and PIDs are shown as well.
Current Lock-Waits

OS_LOCKER LOCKER_SCHEMA LOCKER_PID OS_WAITER WAITER_SCHEMA WAITER_PID
---------- -------------- ---------- ----------- --------------- ----------
zahn SCOTT 8935 zahn SCOTT 8944

SQL_TEXT_WAITER
--------------------------------------------------------------------------
TX: update emp set ename = 'MÃ¼ller' where empno = 7369

This is the original Oracle script to print out the lock wait-for graph in a tree structured fashion. This script prints the sessions in the system that are waiting for locks, and the locks that they are waiting for. The printout is tree structured. If a sessionid is printed immediately below and to the right of another session, then it is waiting for that session. The session ids printed at the left hand side of the page are the ones that everyone is waiting for (Session 96 is waiting for session 88 to complete):
WAITING_SESSION   LOCK_TYPE    MODE_REQUESTED MODE_HELD  LOCK_ID1  LOCK_ID2
----------------- ------------ -------------- ---------- --------- --------
88                None
   96             Transaction  Exclusive      Exclusive  262144    3206
The lock information to the right of the session id describes the lock that the session is waiting for (not the lock it is holding). Note that this is a script and not a set of view definitions because connect-by is used in the implementation and therefore a temporary table is created and dropped since you cannot do a join in a connect-by.
This script has two small disadvantages. One, a table is created when this script is run. To create a table a number of locks must be acquired. This might cause the session running the script to get caught in the lock problem it is trying to diagnose. Two, if a session waits on a lock held by more than one session (share lock) then the wait-for graph is no longer a tree and the conenct-by will show the session (and any sessions waiting on it) several times.

Distributed Transactions

For distributed transactions, Oracle is unable to distinguish blocking locks and deadlocks, because not all of the lock information is available locally. To prevent distributed transaction deadlocks, Oracle times out any call in a distributed transaction if it has not received any response within the number of seconds specified by the _DISTRIBUTED_LOCK_TIMEOUT parameter. This timeout defaults to 60 seconds. If a distributed transaction times out, an ORA-2049 error is returned to the controlling session. Robust applications should handle this exception in the same way as local enqueue deadlocks.
select name,value
from v$parameter
where name = 'distributed_lock_timeout';

NAME VALUE
----------------------------- ------
distributed_lock_timeout 60

ITL Entry Shortages

There is an interested transaction list (ITL) in the variable header of each Oracle data block. When a new block is formatted for a segment, the initial number of entries in the ITL is set by the INITRANS parameter for the segment. Free space permitting, the ITL can grow dynamically if required, up to the limit imposed by the database block size, or the MAXTRANS parameter for the segment, whichever is less.
Every transaction that modifies a data block must record its transaction identifier and the rollback segment address for its changes to that block in an ITL entry. (However, for discrete transactions, there is no rollback segment address for the changes.) Oracle searches the ITL for a reusable or free entry. If all the entries in the ITL are occupied by uncommitted transactions, then a new entry will be dynamically created, if possible.
If the block does not have enough internal free space (24 bytes) to dynamically create an additional ITL entry, then the transaction must wait for a transaction using one of the existing ITL entries to either commit or roll back. The blocked transaction waits in shared mode on the TX enqueue for one of the existing transactions, chosen pseudo-randomly. The row wait columns in V$SESSION show the object, file, and block numbers of the target block. However, the ROW_WAIT_ROW# column remains unset, indicating that the transaction is not waiting on a row-level lock, but is probably waiting for a free ITL entry.
The most common cause of ITL entry shortages is a zero PCTFREE setting. Think twice before setting PCTFREE to zero on a segment that might be subject to multiple concurrent updates to a single block, even though those updates may not increase the total row length. The degree of concurrency that a block can support is dependent on the size of its ITL, and failing that, the amount of internal free space. Do not, however, let this warning scare you into using unnecessarily large INITRANS or PCTFREE settings. Large PCTFREE settings compromise data density and degrade table scan performance, and non-default INITRANS settings are seldom warranted.
One case in which a non-default INITRANS setting is warranted is for segments subject to parallel DML. If a child transaction of a PDML transaction encounters an ITL entry shortage, it will check whether the other ITL entries in the block are all occupied by its sibling transactions and, if so, the transaction will roll back with an ORA-12829 error, in order to avoid self-deadlock. The solution in this case is to be content with a lower degree of parallelism, or to rebuild the segment with a higher INITRANS setting. A higher INITRANS value is also needed if multiple serializable transactions may have concurrent interest in any one block.

Check ITL Waits

The following SQL-Statement shows the number of ITL-Waits per table (Interested Transaction List). INITRANS and/or PCTFREE for those tables is to small (could also be that MAXTRANS is too small). Note that STATISTICS_LEVEL must be set to TYPICAL or ALL, MAXTRANS has been desupported in Oracle 10g and now is always 255 (maximum).
select name,value
from v$parameter
where name = 'statistics_level';
NAME                                 VALUE
------------------------------------ -----------
statistics_level                     TYPICAL
TTITLE "ITL-Waits per table (INITRANS to small)"
set pages 1000
col owner format a15 trunc
col object_name format a30 word_wrap
col value format 999,999,999 heading "NBR. ITL WAITS"
--
select owner,
       object_name||' '||subobject_name object_name,
       value
from v$segment_statistics
where statistic_name = 'ITL waits'
and value > 0
order by 3,1,2;
--
col owner clear
col object_name clear
col value clear
ttitle off
/

Conclusion

Exclusive Locks lock a resource exclusively, Share Locks can be acquired by more than one Session as long as the other Session holding a Share Lock have no open Transaction. A Share Lock can be "switched" from one Session to another.
Application developers can eliminate the risk of deadlocks by ensuring that transactions requiring multiple resources always lock them in the same order.
A DELETE CASCADE can start out as a simple, single-record delete from a harmless table could turn into an uncontrollable torrent of cascading deletes and stored database triggers.
Blocking locks are almost always TX (transaction) locks or TM (table) locks. Oracle always performs locking automatically to ensure data concurrency, data integrity, and statement-level read consistency. Usually, the default locking mechanisms should not be overriden.
For distributed transactions, Oracle is unable to distinguish blocking locks and deadlocks.
The most common cause of ITL entry shortages is a zero PCTFREE setting.

HAPPY LEARNING!
AJITHPATHIYIL

Rollback Of Single Transaction

2011-10-21T03:25:00.000-07:00

SQL> select local_tran_id from dba_2pc_pending; 

LOCAL_TRAN_ID
----------------------
1.1306666
29.1398766
21.996344
27.1267023
30.1222781
4.1280743
13.928667
1.469932
33.554319
2.502494
8.263605

LOCAL_TRAN_ID
----------------------
56.19.234600
58.12.278189
66.9.233351
7.32.277783

15 rows selected.

SQL> Rollback Force '26.30.1222781';

Rollback complete.

SQL> select local_tran_id from dba_2pc_pending where  local_tran_id='26.30.1222781';

LOCAL_TRAN_ID
----------------------
26.30.1222781

SQL> execute dbms_transaction.purge_lost_db_entry('26.30.1222781');

PL/SQL procedure successfully completed.

SQL> select local_tran_id from dba_2pc_pending where  local_tran_id='26.30.1222781';

no rows selected

SQL> select name from v$database;

NAME
---------
AJITHDB

ORACLE PROCESSES, SESSIONS AND CONNECTIONS

2011-10-13T05:32:00.000-07:00

In linux/unix systems a process is a physical process or thread which can be seen with "ps" command for example, There are many types of processes in Oracle, Background processes like

ajithpathiyil:/export/home/oracle>>ps -ef|grep ora_
oracle 103 1 0 Oct 02 ? 45:01 ora_lmon_AJITHDB
oracle 89 1 0 Oct 02 ? 27:30 ora_pmon_AJITHDB
oracle 105 1 0 Oct 02 ? 41:55 ora_lmd0_AJITHDB
oracle 159 1 0 Oct 02 ? 29:27 ora_lgwr_AJITHDB
oracle 161 1 0 Oct 02 ? 9:59 ora_ckpt_AJITHDB
oracle 163 1 0 Oct 02 ? 1:56 ora_smon_AJITHDB
oracle 165 1 0 Oct 02 ? 0:02 ora_reco_AJITHDB
oracle 791 1 0 Oct 02 ? 7:00 ora_arc0_AJITHDB
..
..
..
ajithpathiyil:/export/home/oracle>>

etc..... Meanwhil user processes are like dedicated servers or shared server (multi-threaded server -- aka MTS -- configuration)

A connection is a "physical circuit", a pathway to a database. You can be connected to a database yet have 0 or 1 or MORE sessions going on that connection. We can see that with sqlplus, consider (single user system here, its all about me)

ajithpathiyil:/export/home/oracle>>$ ps -auxww |grep oracleora920

ajithpathiyil:/export/home/oracle>>$ sqlplus /nolog

SQL*Plus: Release 9.2.0.1.0 - Production on Sat Sep 28 10:36:03 2002

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

SQL> !ps -auxww |grep oracleora920
ajithpathiyil 19971 0.0 0.1 2196 916 pts/1 S 10:36 0:00 /bin/bash -c ps -auxww
grep oracleora920
ajithpathiyil 19973 0.0 0.1 1736 564 pts/1 S 10:36 0:00 grep oracleora920
SQL>

no process, no nothing

SQL> connect /
Connected.

SQL> !ps -auxww
grep oracleora920
ora920 19974 1.5 2.2 230976 11752 ? S 10:36 0:00 oracleora920 (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
ajithpathiyil 19975 0.0 0.1 2196 916 pts/1 S 10:36 0:00 /bin/bash -c ps -auxww
grep oracleora920
ajithpathiyil 19977 0.0 0.1 1736 564 pts/1 S 10:36 0:00 grep oracleora920

got my process now...

SQL> disconnect
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production

With the Partitioning, OLAP and Oracle Data Mining options

JServer Release 9.2.0.1.0 - Production

SQL> !ps -auxww |grep oracleora920

ora920 19974 0.6 2.3 230976 11876 ? S 10:36 0:00 oracleora920 (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
ajithpathiyil 19978 0.0 0.1 2196 916 pts/1 S 10:36 0:00 /bin/bash -c ps -auxww
grep oracleora920
ajithpathiyil 19980 0.0 0.1 1736 564 pts/1 S 10:36 0:00 grep oracleora920

SQL> select * from dual;

SP2-0640: Not connected

still have my process, but no session, the message is a little "misleading". Technically -- I have a connection, I don't have a session further, autotrace in sqlplus can be used to show that you can have

a) a connection
b) that uses a single process
c) to service two sessions:

SQL> select username from v$session where username is not null;

USERNAME
------------------------------
AJITHPATHIYIL

SQL>

one session, ME

SQL> select username, program from v$process;

USERNAME PROGRAM
--------------- ------------------------------------------------
PSEUDO
ora920 oracle@ajithpathiyil.home.com (PMON)
ora920 oracle@ajithpathiyil.home.com (DBW0)
ora920 oracle@ajithpathiyil.home.com (LGWR)
ora920 oracle@ajithpathiyil.home.com (CKPT)
ora920 oracle@ajithpathiyil.home.com (SMON)
ora920 oracle@ajithpathiyil.home.com (RECO)
ora920 oracle@ajithpathiyil.home.com (CJQ0)
ora920 oracle@ajithpathiyil.home.com (QMN0)
ora920 oracle@ajithpathiyil.home.com (S000)
ora920 oracle@ajithpathiyil.home.com (D000)
ora920 oracle@ajithpathiyil.home.com (ARC0)
ora920 oracle@ajithpathiyil.home.com (ARC1)
ajithpathiyil oracle@ajithpathiyil.home.com (TNS V1-V3)

14 rows selected.

SQL>

you can see all of the backgrounds and my dedicated server...

SQL> set autotrace on statistics;

Autotrace for statistics uses ANOTHER session so it can query up the stats for your CURRENT session without impacting the STATS for that session!

ajithpathiyil:/export/home/oracle> select username from v$session where username is not null;

USERNAME
------------------------------
AJITHPATHIYIL
AJITHPATHIYIL

see, two sessions but....

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
418 bytes sent via SQL*Net to client
499 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2 rows processed

SQL> select username, program from v$process;

USERNAME PROGRAM
--------------- ------------------------------------------------
PSEUDO
ora920 oracle@ajithpathiyil.home.com (PMON)
ora920 oracle@ajithpathiyil.home.com (DBW0)
ora920 oracle@ajithpathiyil.home.com (LGWR)
ora920 oracle@ajithpathiyil.home.com (CKPT)
ora920 oracle@ajithpathiyil.home.com (SMON)
ora920 oracle@ajithpathiyil.home.com (RECO)
ora920 oracle@ajithpathiyil.home.com (CJQ0)
ora920 oracle@ajithpathiyil.home.com (QMN0)
ora920 oracle@ajithpathiyil.home.com (S000)
ora920 oracle@ajithpathiyil.home.com (D000)
ora920 oracle@ajithpathiyil.home.com (ARC0)
ora920 oracle@ajithpathiyil.home.com (ARC1)
ajithpathiyil oracle@ajithpathiyil.home.com (TNS V1-V3)

14 rows selected.

SQL>
same 14 processes...

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
1095 bytes sent via SQL*Net to client
499 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
14 rows processed

SQL>

A connection is a physical circuit between you and the database.

A connection might be one of many types -- most popular begin DEDICATED server and SHARED server.

Zero, one or more sessions may be established over a given connection to the database as show above with sqlplus.

A process will be used by a session to execute statements.
Sometimes there is a one to one relationship between CONNECTION->SESSION->PROCESS (eg: a normal dedicated server connection).
Sometimes there is a one to many from connection to sessions (eg: like autotrace, one connection, two sessions, one process).

A process does not have to be dedicated to a specific connection or session however, for example when

Using shared server (MTS), your SESSION will grab a process from a pool of processes in order to execute a statement.

When the call is over, that process is released back to the pool of processes.

Happy Learning!

Ajithpathiyil

RAC Cheatsheet-2

2011-09-30T22:07:00.000-07:00

Opatch in RAC

===============

. All-Node Patch

. Shutdown all Oracle instances on all nodes

. Apply the patch to all nodes

. Bring all nodes up

. Minimum downtime

. Shutdown the Oracle instance on node 1

. Apply the patch to the Oracle instance on node 1

. Shutdown the Oracle instance on node 2

. Apply the patch to the Oracle instance on node 2

. Shutdown the Oracle instance on node 3

. At this point, instances on nodes 1 and 2 can be brought up

. Apply the patch to the Oracle instance on node 3

. Startup the Oracle instance on node 3

. Rolling patch (no downtime)

. Shutdown the Oracle instance on node 1

. Apply the patch to the Oracle instance on node 1

. Start the Oracle instance on node 1

. Shutdown the Oracle instance on node 2

. Apply the patch to the Oracle instance on node 2

. Start the Oracle instance on node 2

. Shutdown the Oracle instance on node 3

. Apply the patch to the Oracle instance on node 3

. Start the Oracle instance on node 3

5 - How to determine if a patch is a "rolling patch" or not?

- 9i or 10gR1: (run)

$ opatch query -is_rolling

Opatch will ask the patch location and then will inform if the patch is or

not a "rolling patch"

- 10gR2: (run)

$ opatch query -all | grep rolling

Patching one node at time

The Opatch strategies discussed above (All-Node, Min. Down-Time, and Rolling)

presumes that all nodes will be patched at the same time. Additionally,

each node can be patched individually, at different times, using the "-local"

key word, which will patch only the local node.

opatch apply -local

What is OCR and Voting disk

===========================

The voting disk is a file that contains and manages information of all the node memberships

OCR is a file that manages the cluster and RAC configuration.

Version of CRS

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

crsctl query crs activeversion

command gives the details about voting disk

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

crsctl query css votedisk

to backup voting disk

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

dd if=voting_disk_name of=backup_file_name

alpgadbq02sec:/export/users/crsqa $ dd if=/ocrvoteqa/crsqa/oracle/admin/vote_file of=/export/users/crsqa/vote_file2

20000+0 records in

20000+0 records out

alpgadbq02sec:/export/users/crsqa $ ls -lrt /export/users/crsqa/vote_file2

-rw-r--r-- 1 crsqa dbaqa 10240000 Mar 25 09:38 /export/users/crsqa/vote_file2

alpgadbq02sec:/export/users/crsqa $ ls -lrt /ocrvoteqa/crsqa/oracle/admin/vote_file

-rw-r--r-- 1 crsqa dbaqa 10240000 Mar 25 09:39 /ocrvoteqa/crsqa/oracle/admin/vote_file

alpgadbq02sec:/export/users/crsqa $ file /export/users/crsqa/vote_file2

Recover voting disk from file

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

Take a backup of all voting disk:

dd if=voting_disk_name of=backup_file_name

The following can be used to restore the voting disk from the backup file created.

dd if=backup_file_name of=voting_disk_name

As CRS User

cinohapdbp01sec:/crslab1/oracle/product/10.2.0/crslab1/cdata/crslab1 $ dd if=/ocrvote/crslab1/oracle/admin/Vote_File of=/crslab1/oracle/product/10.2.0/crslab1/log/vote

crsctl command to add and delete the voting disks

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

In 11g online voting disk removal

In 10g it's offline

cinohapdbp01sec # /crslab1/oracle/product/10.2.0/crslab1/bin/crsctl delete css votedisk /ocrvote/crslab1/oracle/admin/Vote_File2 -force

successful deletion of votedisk /ocrvote/crslab1/oracle/admin/Vote_File2.

cinohapdbp01sec #

crsctl delete css votedisk path

crsctl add css votedisk path

use -force option when cluster is down

crsctl add css votedisk path -force

Location of OCR

==================

/cdata/

ocrconfig -showbackup

atlease 2 backup in 4 hr gap

one daily back

one weekly back

ocrconfig -help

Manual backup of CRS

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

As root

Backup=> ocrconfig -export

Bringdown CRS

Restore=> ocrconfig -import

Bring up CRS

cinohapdbp01sec # /crslab1/oracle/product/10.2.0/crslab1/bin/ocrconfig -export /crslab1/oracle/product/10.2.0/crslab1/log/ocr_bak

cinohapdbp01sec # /crslab1/oracle/product/10.2.0/crslab1/bin/ocrconfig -Import /crslab1/oracle/product/10.2.0/crslab1/log/ocr_bak

To change loc

==============

ocrconfig -backuploc

Voting disk details

====================

crsctl query css votedisk

ocrcheck

To check No of instances

==========================

SELECT * FROM V$ACTIVE_INSTANCES;

pfile parameters

=================

ebizdb1.__db_cache_size=377487360

ebizdb2.__db_cache_size=394264576..etc

*.cluster_database_instances=2

*.cluster_database=true

ebizdb2.instance_number=2

ebizdb1.instance_number=1

ebizdb1.undo_tablespace='UNDOTBS1'

ebizdb2.undo_tablespace='UNDOTBS2'

To stop the instance and Db

============================

export ORACLE_SID=ebizdb2

emctl stop dbconsole

srvctl stop instance -d ebizdb -i ebizdb1

srvctl stop asm -n ebizdb1

srvctl stop nodeapps -n ebizdb1

srvctl status database -d ebizdb

srvctl status instance -d ebizdb -i ebizdb1

no of node

===========

$ORA_CRS_HOME/bin/olsnodes -n

cihsnsddb001.sensing.ge.com

To stop clusterware

====================

crsctl check crs

crsctl stop crs

/etc/init.d/init.crs stop

crs_stat -t

pa55word

crs_start -all

crsctl query css votedisk

can i try to delete a node from RAC and add it again

fdisk -l

iscsi-ls

/etc/init.d/o2cb status

OCFS

======

create

mkfs.ocfs2 -b 4k -C 32K -N 4 -L ebizdata_2 /dev/sdd1

mkfs.ocfs2 -b 4K -C 32K -N 4 -L ebizdata_3 /dev/sdc1

mkfs.ocfs2 -b 4K -C 32K -N 4 -L R12data_1 /dev/hda1

mount (cat /etc/fstab)

mount -t ocfs2 -o datavolume,nointr -L "crs" /d01/crsdata/ebizdb

mount -t ocfs2 -o datavolume,nointr -L "ebizdata_3" /d02/oradata/ebizdb

ASM

create

/etc/init.d/oracleasm createdisk VOL2 /dev/sde1

check

openfiler

============

service iscsi-target restart

rac node

========

service iscsi restart

/etc/init.d/oracleasm listdisks

after mount add value in

/etc/fstab

dmesg | sort | grep '^Attached scsi disk'

add "DiscoveryAddress=openfiler-priv:3260" in /etc/iscsi.conf

DiscoveryAddress=openfiler-priv:3260

rpm -Uvh ocfs2-2.6.9-42.ELsmp-1.2.9-1.el4.i686.rpm ocfs2console-1.2.7-1.el4.i386.rpm ocfs2-tools-1.2.7-1.el4.i386.rpm ocfs2-tools-1.2.7-1.el4.i386.rpm

ebizdb1:/root $ ./iscsi-ls-map.sh

Host / SCSI ID SCSI Device Name iSCSI Target Name

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

2 /dev/sdc ebizdata_3

3 /dev/sdb ebizdata_2

4 /dev/sdd ebizdata_1

5 /dev/sde asm3

6 /dev/sdf asm2

7 /dev/sdh asm1

8 /dev/sdg crs

ebizdb1:/root $

ebizdb1:/root $ fdisk /dev/sdc

Command (m for help): n

Command action

e extended

p primary partition (1-4)

Partition number (1-4): 1

First cylinder (1-1019, default 1):

Using default value 1

Last cylinder or +size or +sizeM or +sizeK (1-1019, default 1019):

Using default value 1019

Command (m for help): w

The partition table has been altered!

Calling ioctl() to re-read partition table.

Syncing disks.

ebizdb1:/root $ ./iscsi-ls-map.sh

Host / SCSI ID SCSI Device Name iSCSI Target Name

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

2 /dev/sdc ebizdata_3

3 /dev/sdb ebizdata_2

4 /dev/sdd ebizdata_1

5 /dev/sde asm3

6 /dev/sdf asm2

7 /dev/sdh asm1

8 /dev/sdg crs

ebizdb1:/root $ mkfs.ocfs2 -b 4K -C 32K -N 4 -L ebizdata_3 /dev/sdc1

COLUMN instance_name FORMAT a13

COLUMN host_name FORMAT a9

COLUMN failover_method FORMAT a15

COLUMN failed_over FORMAT a11

SELECT instance_name , host_name , NULL AS failover_type , NULL AS failover_method , NULL AS failed_over FROM v$instance

UNION

SELECT NULL , NULL , failover_type , failover_method , failed_over

FROM v$session WHERE username = 'SYSTEM';

388577.1-convert to RAC

[root@ebizdb1 ~]# service iscsi restart

Searching for iscsi-based multipath maps

Found 0 maps

Stopping iscsid: [ OK ]

Removing iscsi driver: ERROR: Module iscsi_sfnet is in use

[FAILED]

[root@ebizdb1 ~]#

[root@ebizdb1 ~]# service iscsi restart

Searching for iscsi-based multipath maps

Found 0 maps

Stopping iscsid: iscsid not running

Removing iscsi driver: ERROR: Module iscsi_sfnet is in use

[FAILED]

to install

===========

rpm -Uvh compat*

check

=====

rpm -q compa*

15920685

/dev/hdc6

[root@ebizapp apps_12]# rpm -Uvh compat*

warning: compat-gcc-c++-7.3-2.96.128.i386.rpm: V3 DSA signature: NOKEY, key ID 73307de6

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

file /usr/lib/libstdc++-2-libc6.1-1-2.9.0.so from install of compat-libstdc++-7.3-2.96.128 conflicts with file from package compat-libstdc++-296-2.96-132.7.2

file /usr/lib/libstdc++-3-libc6.2-2-2.10.0.so from install of compat-libstdc++-7.3-2.96.128 conflicts with file from package compat-libstdc++-296-2.96-132.7.2

[root@ebizapp apps_12]#

runcluvfy.sh stage -post crs -n ebizdb1,ebizdb2 -verbose

./runcluvfy.sh stage -pre dbinst -n ebizdb1,ebizdb2 -r 10gR2 -verbose

Manually deleting the services and clean-up

Removing a Node from a 10g RAC Cluster

======================================

Doc ID: Note:269320.1

=======================

/etc/init.d/init.evmd stop

/etc/init.d/init.evmd disable

/etc/init.d/init.cssd stop

/etc/init.d/init.cssd disable

/etc/init.d/init.crsd stop

/etc/init.d/init.crsd disable

/etc/init.d/init.crs stop

/etc/init.d/init.crs disable

rm -rf /etc/oracle /etc/oraInst.loc /etc/oratab

rm -rf /etc/init.d/init.crsd /etc/init.d/init.crs /etc/init.d/init.cssd /etc/init.d/init.evmd

rm -rf /etc/rc2.d/K96init.crs /etc/rc2.d/S96init.crs etc/rc3.d/K96init.crs \

rm -rf /etc/rc3.d/S96init.crs /etc/rc4.d/K96init.crs /etc/rc4.d/S96init.crs \

rm -rf /etc/rc5.d/K96init.crs /etc/rc5.d/S96init.crs /etc/rc.d/rc0.d/K96init.crs \

rm -rf /etc/rc.d/rc1.d/K96init.crs /etc/rc.d/rc6.d/K96init.crs /etc/rc.d/rc4.d/K96init.crs

cp /etc/inittab.orig /etc/inittab

rm -rf /etc/inittab.crs /etc/inittab.no_crs

rm -rf /tmp/*

rm -rf /tmp/.oracle

rm -rf /usr/local/bin/dbhome /usr/local/bin/oraenv /usr/local/bin/coraenv

rm -rf /var/tmp/.oracle

rm -rf /opt/oracle/*

rm -rf /u03/oracrs/

./runInstaller -updateNodeList ORACLE_HOME=/u01/app/oracle/product/10.2.0/db_1 CLUSTER_NODES=ebizdb1,ebizdb2

runInstaller -updateNodeList -noClusterEnabled ORACLE_HOME=/u01/app/oracle/product/10.2.0/db_1 CLUSTER_NODES=ebizdb1,ebizdb2 CRS=false "INVENTORY_LOCATION=/u01/app/oraInventory" LOCAL_NODE=.

[root@ebizdb3 install]# ./rootdelete.sh local nosharedvar nosharedhome

[root@ebizdb1 install]# ./rootdeletenode.sh ebizdb3,3

runInstaller -updateNodeList ORACLE_HOME=/u01/app/crs CLUSTER_NODES=ebizdb1,ebizdb2 CRS=TRUE

Node addition

===============

prepare the hardware similar to existing nodes

Verify Remote Access / User Equivalence

run $ORA_CRS_HOME/oui/bin/addNode.sh

it will ask to run as root for below script

/ORA_CRS_HOME/install/rootaddnode.sh from running node

/ORA_CRS_HOME/root.sh from new node

oraInstRoot.sh creates inventory location if it's not available.

Although Oracle CRS replaces the Oracle Cluster Manager (ORACM) in Oracle9i RAC, it does continue support for the Global Services Daemon (GSD), which in Oracle9i is responsible for communicating with the Oracle RAC database. In Oracle 10g, GSD's sole purpose is to serve Oracle9i clients (such as SRVCTL, Database Configuration Assistant, and Oracle Enterprise Manager). Financially, this is a very positive benefit since one is not bound to buy new client licenses and hardware to support an Oracle 10g database.

To check interfaces between the RAC nodes

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

$ORA_CRS_HOME/bin/oifcfg iflist

cinohapdbp01sec:/export/users/crslab1 $ $ORA_CRS_HOME/bin/oifcfg iflist

ce0 3.112.44.0

ce2 3.112.44.0

ce3 192.168.22.0

ce3 192.168.21.0

ce5 172.17.0.0

ce7 192.168.24.0

ce7 192.168.23.0

ce7 192.168.25.0

ce9 3.24.138.0

cinohapdbp01sec:/export/users/crslab1 $

3.112.45.199 cinohapdbp01sec cinohapdbp01sec.security.ge.com loghost

3.112.45.205 cinohapdbp01sec-vip cinohapdbp01sec-vip.security.ge.com

192.168.21.1 cinohapdbp01sec-priv

3.112.45.202 cinohapdbp02sec cinohapdbp02sec.security.ge.com

3.112.45.206 cinohapdbp02sec-vip cinohapdbp02sec-vip.security.ge.com

192.168.21.2 cinohapdbp02sec-priv

/usr/bin/netstat -f inet

Logfile Location

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

cd $ORA_CRS_HOME/log/node_name

alert.log

evmd/evmd.log

cssd/cssd.log

crsd/crsd.log

cd racg

ora..vip.log

ora..ons.log

ora..gsd.log

To relocate the crs services

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

crs_relocate

crs_relocate ora.cinohapdbp02sec.vip

Enable debug for particulare RAC services

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

crsctl debug log res ':5'

to diable

----------

crsctl debug log res 'ora.cinohapdbp02sec.vip'

cinohapdbp02sec:/export/users/crslab1 $ crsctl help

Usage: crsctl check crs - checks the viability of the Oracle Clusterware

crsctl check cssd - checks the viability of Cluster Synchronization Services

crsctl check crsd - checks the viability of Cluster Ready Services

crsctl check evmd - checks the viability of Event Manager

crsctl check cluster [-node ] - checks the viability of CSS across nodes

crsctl set css - sets a parameter override

crsctl get css - gets the value of a Cluster Synchronization Services parameter

crsctl unset css - sets the Cluster Synchronization Services parameter to its default

crsctl query css votedisk - lists the voting disks used by Cluster Synchronization Services

crsctl add css votedisk - adds a new voting disk

crsctl delete css votedisk - removes a voting disk

crsctl enable crs - enables startup for all Oracle Clusterware daemons

crsctl disable crs - disables startup for all Oracle Clusterware daemons

crsctl start crs [-wait] - starts all Oracle Clusterware daemons

crsctl stop crs [-wait] - stops all Oracle Clusterware daemons. Stops Oracle Clusterware managed resources in case of cluster.

crsctl start resources - starts Oracle Clusterware managed resources

crsctl stop resources - stops Oracle Clusterware managed resources

crsctl debug statedump css - dumps state info for Cluster Synchronization Services objects

crsctl debug statedump crs - dumps state info for Cluster Ready Services objects

crsctl debug statedump evm - dumps state info for Event Manager objects

crsctl debug log css [module:level] {,module:level} ... - turns on debugging for Cluster Synchronization Services

crsctl debug log crs [module:level] {,module:level} ... - turns on debugging for Cluster Ready Services

crsctl debug log evm [module:level] {,module:level} ... - turns on debugging for Event Manager

crsctl debug log res [resname:level] ... - turns on debugging for Event Manager

crsctl debug trace css [module:level] {,module:level} ... - turns on debugging for Cluster Synchronization Services

crsctl debug trace crs [module:level] {,module:level} ... - turns on debugging for Cluster Ready Services

crsctl debug trace evm [module:level] {,module:level} ... - turns on debugging for Event Manager

crsctl query crs softwareversion [] - lists the version of Oracle Clusterware software installed

crsctl query crs activeversion - lists the Oracle Clusterware operating version

crsctl lsmodules css - lists the Cluster Synchronization Services modules that can be used for debugging

crsctl lsmodules crs - lists the Cluster Ready Services modules that can be used for debugging

crsctl lsmodules evm - lists the Event Manager modules that can be used for debugging

If necessary any of these commands can be run with additional tracing by adding a 'trace'

argument at the very front. Example: crsctl trace check css

cinohapdbp02sec:/export/users/crslab1

export _USR_ORA_VIP=3.112.45.205

export _USR_ORA_NETMASK=255.255.252.0

export _USR_ORA_IF=ce2

export _CAA_NAME=cinohapdbp01sec

export _USR_ORA_VIP=3.112.45.206

export _USR_ORA_NETMASK=255.255.252.0

export _USR_ORA_IF=ce2

export _CAA_NAME=cinohapdbp02sec

cinohapdbp02sec # /crslab1/oracle/product/10.2.0/crslab1/bin/racgvip check

cinohapdbp02sec # /crslab1/oracle/product/10.2.0/crslab1/bin/racgvip start

checking the cluster configuration

===================================

cinohapdbp02sec:/crslab1/oracle/product/10.2.0/crslab1/log/cinohapdbp02sec/evmd $ srvctl config nodeapps -n cinohapdbp02sec

VIP exists.: /cinohapdbp02sec-vip/3.112.45.206/255.255.252.0/ce2

GSD exists.

ONS daemon exists.

Listener does not exist.

cinohapdbp02sec:/crslab1/oracle/product/10.2.0/crslab1/log/cinohapdbp02sec/evmd $

Doc ID: 283107.1

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

bash-3.00$ ifconfig -a

lo0: flags=2001000849 mtu 8232 index 1

inet 127.0.0.1 netmask ffffff00

ce0: flags=19040843 mtu 1500 index 2

inet 3.112.45.207 netmask fffffc00 broadcast 3.112.47.255

groupname IPMP0

ce2: flags=29040843 mtu 1500 index 3

inet 3.112.45.208 netmask fffffc00 broadcast 3.112.47.255

groupname IPMP0

ce2:1: flags=21000843 mtu 1500 index 3

inet 3.112.45.199 netmask fffffc00 broadcast 3.112.47.255

ce2:2: flags=21000843 mtu 1500 index 3

inet 3.112.45.201 netmask fffffc00 broadcast 3.112.47.255

ce2:3: flags=21040843 mtu 1500 index 3

inet 3.112.45.205 netmask fffffc00 broadcast 3.112.47.255

ce3: flags=1000843 mtu 1500 index 7

inet 192.168.22.1 netmask ffffff00 broadcast 192.168.22.255

ce3:1: flags=1000843 mtu 1500 index 7

inet 192.168.21.1 netmask ffffff00 broadcast 192.168.21.255

ce5: flags=1000843 mtu 1500 index 4

inet 172.17.7.185 netmask ffffc000 broadcast 172.17.63.255

ce7: flags=1000843 mtu 1500 index 8

inet 192.168.24.1 netmask ffffff00 broadcast 192.168.24.255

ce7:1: flags=1000843 mtu 1500 index 8

inet 192.168.23.1 netmask ffffff00 broadcast 192.168.23.255

ce7:2: flags=1000843 mtu 1500 index 8

inet 192.168.25.1 netmask ffffff00 broadcast 192.168.25.255

ce9: flags=1000843 mtu 1500 index 5

inet 3.24.138.216 netmask fffffe00 broadcast 3.24.139.255

bash-3.00$ hostname

cinohapdbp01sec

cinohapdbp02sec # ifconfig -a

lo0: flags=2001000849 mtu 8232 index 1

inet 127.0.0.1 netmask ff000000

ce0: flags=9040843 mtu 1500 index 2

inet 3.112.45.210 netmask fffffc00 broadcast 3.112.47.255

groupname IPMP0

ether 0:14:4f:74:53:64

ce0:1: flags=1000843 mtu 1500 index 2

inet 3.112.45.202 netmask fffffc00 broadcast 3.112.47.255

ce0:2: flags=1040843 mtu 1500 index 2

inet 3.112.45.206 netmask fffffc00 broadcast 3.112.47.255

ce2: flags=69040843 mtu 1500 index 3

inet 3.112.45.211 netmask fffffc00 broadcast 3.112.47.255

groupname IPMP0

ether 0:14:4f:1f:3d:f8

ce3: flags=1000843 mtu 1500 index 6

inet 192.168.22.2 netmask ffffff00 broadcast 192.168.22.255

ether 0:14:4f:1f:3d:f9

ce3:1: flags=1000843 mtu 1500 index 6

inet 192.168.21.2 netmask ffffff00 broadcast 192.168.21.255

ce5: flags=1000843 mtu 1500 index 4

inet 172.17.7.186 netmask ffffc000 broadcast 172.17.63.255

ether 0:14:4f:1f:3d:fb

ce7: flags=1000843 mtu 1500 index 8

inet 192.168.23.2 netmask ffffff00 broadcast 192.168.23.255

ether 0:14:4f:1f:1c:9d

ce7:1: flags=1000843 mtu 1500 index 8

inet 192.168.25.2 netmask ffffff00 broadcast 192.168.25.255

ce7:2: flags=1000843 mtu 1500 index 8

inet 192.168.24.2 netmask ffffff00 broadcast 192.168.24.255

ce9: flags=1000843 mtu 1500 index 5

inet 3.24.138.214 netmask fffffe00 broadcast 3.24.139.255

ether 0:14:4f:1f:1c:9f

cinohapdbp02sec #

ce2 is standby IP Multipathing (IPMP)

What is the difference between VIP and IPMP ?

================================================

IPMP can failover an address to another interface, but not failover to the other node.

Oracle VIP can failover to another interface on the same node or to another host in the cluster.

cinohapdbp02sec # srvctl modify nodeapps -n cinohapdbp02sec -A cinohapdbp02sec-vip/255.255.252.0/ce2\|ce0

cinohapdbp02sec # srvctl config nodeapps -n cinohapdbp02sec

VIP exists.: /cinohapdbp02sec-vip/3.112.45.206/255.255.252.0/ce2:ce0

GSD exists.

ONS daemon exists.

Listener does not exist.

cinohapdbp02sec # srvctl modify nodeapps -n cinohapdbp01sec -A cinohapdbp01sec-vip/255.255.252.0/ce2\|ce0

cinohapdbp02sec # srvctl start nodeapps -n cinohapdbp02sec

cinohapdbp02sec # srvctl status nodeapps -n cinohapdbp02sec

VIP is running on node: cinohapdbp02sec

GSD is running on node: cinohapdbp02sec

PRKO-2016 : Error in checking condition of listener on node: cinohapdbp02sec

ONS daemon is running on node: cinohapdbp02sec

cinohapdbp02sec #

Srvctl tools to add new db instance

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

srvctl add database -d lab1 -o /lab1/oracle/product/10.2.0/lab1

srvctl add instance -d lab1 -i lab11 -n cinohapdbp01sec

srvctl add instance -d lab1 -i lab12 -n cinohapdbp02sec

srvctl setenv instance -d lab1 -i lab11 -t TNS_ADMIN=/lab1/oracle/product/10.2.0/lab1/network/admin/lab11_cinohapdbp01sec

srvctl setenv instance -d lab1 -i lab12 -t TNS_ADMIN=/lab1/oracle/product/10.2.0/lab1/network/admin/lab12_cinohapdbp02sec

3.112.45.199 cinohapdbp01sec cinohapdbp01sec.security.ge.com loghost

3.112.45.205 cinohapdbp01sec-vip cinohapdbp01sec-vip.security.ge.com

192.168.21.1 cinohapdbp01sec-priv

3.112.45.202 cinohapdbp02sec cinohapdbp02sec.security.ge.com loghost

3.112.45.206 cinohapdbp02sec-vip cinohapdbp02sec-vip.security.ge.com

192.168.21.2 cinohapdbp02sec-priv

$ORA_CRS_HOME/bin/oifcfg getif

cinohapdbp01sec $ ./oifcfg getif

ce2 3.112.44.0 global public

ce3 192.168.21.0 global cluster_interconnect

cinohapdbp02sec $ ./oifcfg getif

ce2 3.112.44.0 global public

ce3 192.168.21.0 global cluster_interconnect

from 10201

before cluster instll

cd /lab1/oradata/recovery/arch01/lab11/software/10201_cluster/cluvfy

./runcluvfy.sh comp nodecon -n cinohapdbp01sec,cinohapdbp02sec -verbose

./runcluvfy.sh stage -pre crsinst -n all -verbose

After Cluster install

$ORA_CRS_HOME/bin/cluvfy stage -post crsinst -n cinohapdbp01sec,cinohapdbp02sec

from 11.1.0.6

cd /lab1/oradata/recovery/arch01/lab11/software/11106_software/clusterware

./runcluvfy.sh stage -pre crsinst -n cinohapdbp01sec,cinohapdbp02sec -verbose

before db binray install

./runcluvfy.sh stage -pre dbinst -n cinohapdbp02sec,cinohapdbp01sec -verbose

Before dbca

./runcluvfy.sh stage -pre dbcfg -n cinohapdbp02sec,cinohapdbp01sec -d /lab1/oracle/product/11.1.0/lab1 -verbose

From CRS_HOME

220863/Target!18

TAF

---

Add the below entry in client and db tnsnames.ora

LAB1_TAF =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = cinohapdbp01sec-vip.security.ge.com)(PORT = 1529))

(ADDRESS = (PROTOCOL = TCP)(HOST = cinohapdbp02sec-vip.security.ge.com)(PORT = 1529))

(LOAD_BALANCE = yes)

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = LAB1_TAF)

(FAILOVER_MODE =

(TYPE = SELECT)

(METHOD = BASIC)

(RETRIES = 180)

(DELAY = 5)

)

Check the failover Information

===============================

COLUMN instance_name FORMAT a13

COLUMN host_name FORMAT a9

COLUMN failover_method FORMAT a15

COLUMN failed_over FORMAT a11

SELECT

instance_name

, host_name

, NULL AS failover_type

, NULL AS failover_method

, NULL AS failed_over

FROM v$instance

UNION

SELECT

NULL

, NULL

, failover_type

, failover_method

, failed_over

FROM v$session

WHERE username = 'SYSTEM';

Check Veritas cluster file system installation

modinfo | grep vxfs

cinohapdbp01sec # hastatus -summary

-- SYSTEM STATE

-- System State Frozen

A cinohapdbp01sec RUNNING 0

A cinohapdbp02sec RUNNING 0

-- GROUP STATE

-- Group System Probed AutoDisabled State

B clustmon cinohapdbp01sec Y N ONLINE

B clustmon cinohapdbp02sec Y N OFFLINE

B cvm cinohapdbp01sec Y N ONLINE

B cvm cinohapdbp02sec Y N ONLINE

Check Veritas mount in the system

bash-3.00$ df -F vxfs

/crslab1/oracle (/dev/vx/dsk/cinohapdbp01secdg/crsprod_oracle): 9530832 blocks 148914 files

/lab1/oradata/system01(/dev/vx/dsk/lab1system_dg/system01):82405744 blocks 1287565 files

/lab1/oradata/recovery/redo04/lab12(/dev/vx/dsk/lab1redo_dg/redo04_2):26761600 blocks 418136 files

/lab1/oradata/recovery/redo04/lab11(/dev/vx/dsk/lab1redo_dg/redo04):26462288 blocks 413470 files

/backup/data (/dev/vx/dsk/bkpdata_dg/bkpdata):459034640 blocks 7172416 files

/lab1/oradata/custom01(/dev/vx/dsk/lab1custom_dg/custom01):47658544 blocks 744638 files

/lab1/oradata/data01(/dev/vx/dsk/lab1data_dg/data01):59864944 blocks 935387 files

/lab1/oradata/recovery/redo01/lab11(/dev/vx/dsk/lab1redo_dg/redo01):26446368 blocks 413216 files

/lab1/oradata/recovery/redo01/lab12(/dev/vx/dsk/lab1redo_dg/redo01_2):26705264 blocks 417243 files

/lab1/oradata/data05(/dev/vx/dsk/lab1data_dg/data05):102242272 blocks 1597506 files

/lab1/oradata/data04(/dev/vx/dsk/lab1data_dg/data04):96421840 blocks 1506561 files

/lab1/oradata/recovery/redo02/lab12(/dev/vx/dsk/lab1redo_dg/redo02_2):26705584 blocks 417271 files

/lab1/oradata/recovery/redo02/lab11(/dev/vx/dsk/lab1redo_dg/redo02):24309104 blocks 379828 files

/lab1/oradata/data02(/dev/vx/dsk/lab1data_dg/data02):81311632 blocks 1270493 files

/lab1/oradata/data03(/dev/vx/dsk/lab1data_dg/data03):90363840 blocks 1411904 files

/lab1/oradata/recovery/redo03/lab11(/dev/vx/dsk/lab1redo_dg/redo03):24426528 blocks 381658 files

/lab1/oradata/recovery/redo03/lab12(/dev/vx/dsk/lab1redo_dg/redo03_2):26761600 blocks 418136 files

/ocrvote (/dev/vx/dsk/ocrvotedg1/ocrvotevol): 1956522 blocks 244564 files

/lab1/oracle (/dev/vx/dsk/lab1bin_dg/oracle):14202576 blocks 221888 files

/etc/VRTSvcs/conf/config/main.cf

Online redo log file

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

select l.group#, thread#, member

from v$log l, v$logfile lf

where l.group#=lf.group#;

select THREAD#,GROUPS,INSTANCE,SEQUENCE# from V$thread;

HAPPY LEARNING!

Ajith Pathiyil

RAC - Issues & Troubleshooting

2011-09-08T01:34:00.000-07:00

Whenever a node is having issues joining the cluster back post reboot, here is a quick check list I would suggest:

/var/log/messages

ifconfig

ip route

/etc/hosts

/etc/sysconfig/network-scripts/ifcfg-eth*

ethtool

mii-tool

cluvfy

$ORA_CRS_HOME/log

Let us now take a closer look at specifc issues with examples and steps taken for their resolution.

These are all tested on Oracle 10.2.0.4 database on RHEL4 U8 x-64

1. srvctl not able to start Oracle Instance but sqlplus able to start

a. Check racg log for actual error message.

% more $ORACLE_HOME/log/`hostname -s`/racg/ora.{DBNAME}.{INSTANCENAME}.inst.log

b. Check if srvctl is configured to use correct parameter file(pfile/spfile)

% srvctl config database -d {DBNAME} -a

You can also validate parameter file by using sqlplus to see the exact error message.

c. Check ownership for $ORACLE_HOME/log

If this is owned by root, srvctl won't be able to start instance as oracle user.

# chown -R oracle:dba $ORACLE_HOME/log

2. VIP has failed over to another node but is not coming back to the original node

Fix: The node where the VIP has failed over, bring it down manually as root

Example: ifconfig eth0:2 down

PS: Be careful to bring down only VIP. A small typo may bring down your public interface:)

3. Moving OCR to a different location

PS: This can be done while CRS is up as root.

While trying to change ocr mirror or the ocr to a new location, ocrconfig complaints.

The fix is to touch the new file.

Example:

# ocrconfig -replace ocrmirror /crs_new/cludata/ocrfile

PROT-21: Invalid parameter

# touch /crs_new/cludata/ocrfile

# chown root:dba /crs_new/cludata/ocrfile

# ocrconfig -replace ocrmirror /crs_new/cludata/ocrfile

Verify:

a. Validate using "ocrcheck". Device/File Name should point to the new one with integrity check succeeded.

b. Ensure OCR inventory is updated correctly

# cat /etc/oracle/ocr.loc

ocrconfig_loc and ocrmirrorconfig_loc should point to correct locations.

4. Moving Voting Disk to a different location

PS: CRS must be down while moving the voting disk.

The idea is to add new voting disks and delete the older ones.

Find below sample errors and their fix.

# crsctl add css votedisk /crs_new/cludata/cssfile_new

Cluster is not in a ready state for online disk addition

We need to use force option. However, before using force option, ensure CRS is down.

If CRS is up, DO NOT use force option else it may corrupt your OCR.

# crsctl add css votedisk /crs_new/cludata/cssfile_new -force

Now formatting voting disk: /crs_new/cludata/cssfile_new

successful addition of votedisk /crs_new/cludata/cssfile_new.

Verify using "crsctl query css votedisk" and then delete the old votedisks.

While deleting too, you'll need to use force option.

Also verify the permissions of the voting disk files. It should be oracle:dba

If voting disks were added using root, the permission should be changed to oracle:dba

5. Manually registering listener resource to OCR

Listener was registered manually with OCR but srvctl was unable to bring up the listener

Let us first see example of how to manually do this.

From an existing available node, print the listener resource

% crs_stat -p ora.test-server2.LISTENER_TEST-SERVER2.lsnr > /tmp/res

% cat /tmp/res

NAME=ora.test-server2.LISTENER_TEST-SERVER2.lsnr

TYPE=application

ACTION_SCRIPT=/orahome/ora10g/product/10.2.0/db_1/bin/racgwrap

ACTIVE_PLACEMENT=0

AUTO_START=1

CHECK_INTERVAL=600

DESCRIPTION=CRS application for listener on node

FAILOVER_DELAY=0

FAILURE_INTERVAL=0

FAILURE_THRESHOLD=0

HOSTING_MEMBERS=test-server2

OPTIONAL_RESOURCES=

PLACEMENT=restricted

REQUIRED_RESOURCES=ora.test-server2.vip

RESTART_ATTEMPTS=5

SCRIPT_TIMEOUT=600

START_TIMEOUT=0

STOP_TIMEOUT=0

UPTIME_THRESHOLD=7d

USR_ORA_ALERT_NAME=

USR_ORA_CHECK_TIMEOUT=0

USR_ORA_CONNECT_STR=/ as sysdba

USR_ORA_DEBUG=0

USR_ORA_DISCONNECT=false

USR_ORA_FLAGS=

USR_ORA_IF=

USR_ORA_INST_NOT_SHUTDOWN=

USR_ORA_LANG=

USR_ORA_NETMASK=

USR_ORA_OPEN_MODE=

USR_ORA_OPI=false

USR_ORA_PFILE=

USR_ORA_PRECONNECT=none

USR_ORA_SRV=

USR_ORA_START_TIMEOUT=0

USR_ORA_STOP_MODE=immediate

USR_ORA_STOP_TIMEOUT=0

USR_ORA_VIP=

Modify relevant parameters in the resource file to point to correct instance.

Rename as resourcename.cap

% mv /tmp/res /tmp/ora.test-server1.LISTENER_TEST-SERVER1.lsnr.cap

Register with OCR

% crs_register ora.test-server1.LISTENER_TEST-SERVER1.lsnr -dir /tmp/

Start listener

% srvctl start listener -d testdb -n test-server1

While trying to start listener, srvctl is throwing errors like "Unable to read from listener log file"

The listener log file exists.

If resource is registered using root, then srvctl won't be able to start using oracle user.

So all the aforementioned operations while registering the listener manually should be done using oracle user.

6. Services

While checking status of a service, it says "not running"

If we try to start it using srvctl, the error message is "No such service exists" or "already running"

If we try to add service with same name, it says "already exists"

This happens because the service is in an "Unknown" state in the OCR

Using crs_stat, check if any related resource for service(resource names ending with .srv and .cs) is still lying around.

srvctl remove service -f has been tried and the issue persists.

Here is the fix:

# crs_stop -f {resourcename}

# crs_unregister {resourcename}

Now service can be added and started correctly.

7. Post host reboot, CRS is not starting

After host reboot, CRS was not coming up. No CRS logs in $ORA_CRS_HOME

Check /var/log/messages

"Cluster Ready Services waiting on dependencies. Diagnostics in /tmp/crsctl.9559"

No logs seen in /tmp/crsctl.*

Run cluvfy to identify the issue

$ORA_CRS_HOME/bin/cluvfy stage -post crsinst -n {nodename}

/tmp was not writable

/etc/fstab was incorrect and was fixed for making /tmp available

If you see messages like "Shutdown CacheLocal. my hash ids don't match" in the CRS log, then

check if /etc/oracle/ocr.loc is same across all nodes of the cluster.

8. CRS binary restored by copying from existing node in the cluster

CRS not starting with following messages in /var/log/messages;

"Id "h1" respawning too fast: disabled for 5 minutes"

CRSD log showing "no listener"

If CRS binary is restored by copying from existing node in the cluster, then you need to ensure:

a. Hostnames are modified correctly in $ORA_CRS_HOME/log

b. You may need to cleanup socket files from /var/tmp/.oracle

PS:Exercise caution while working with the socket files. If CRS is up, you should never touch those files otherwise reboot may be inevitable.

9. CRS rebooting frequently by oprocd

Check /etc/oracle/oprocd/ and grep for "Rebooting".

Check /var/log/messages and grep for "restart"

If the timestamps are matching, this confirms reboots are being initated by oprocd process.

%ps -ef
grep oprocd

root 10409 9937 0 Feb27 ? 00:00:00 /oracle/product/crs/bin/oprocd.bin run -t 1000 -m 500 -f

-t 1000 means oprocd would wake up every 1000ms

-m 500 means allow upto 500ms margin of error

Basically with these options if oprocd wakes up after > 1.5 secs it’s going to force a reboot.

This is conceptually analogous to what hangcheck timer used to do pre 10.2.0.4 Oracle releases on Linux.

Fix is to set CSS diagwait to 13

#crsctl set css diagwait 13 -force

# /oracle/product/crs/bin/crsctl get css diagwait

13

This actually changes what parameters oprocd runs with

%ps -ef
grep oprocd

root 10409 9937 0 Feb27 ? 00:00:00 /oracle/product/crs/bin/oprocd.bin run -t 1000 -m 10000 -hsi 5:10:50:75:90 -f

Note that the margin has now changed to 10000ms i.e 10 seconds in place of the default 0.5 seconds.

PS: Setting diagwait requires a full shutdown of Oracle Clusterware on ALL nodes.

10. Cluster hung. All SQL queries on GV$ views are hanging.

Alert log from all instance have message like below:

INST1: IPC Send timeout detected. Receiver ospid 1650

INST2:IPC Send timeout detected.Sender: ospid 24692

Receiver: inst 1 binc 150 ospid 1650

INST3: IPC Send timeout detected.Sender: ospid 12955

Receiver: inst 1 binc 150 ospid 1650

The ospid on all instances belong to LCK0 - Lock Process

In case of inter-instance lock issues, it's important to identify the instance from where it's initiating.

As seen from above, INST1 is the one that needs to be fixed.

Just identify the process that is causing row cache lock and kill it otherwise reboot node 1.

11. Inconsistent OCR with invalid permissions

% srvctl add db -d testdb -o /oracle/product/10.2

PRKR-1005 : adding of cluster database testdb configuration failed, PROC-5: User does not have permission to perform a cluster registry operation on this key. Authentication error [User does not have permission to perform this operation] [0]

crs_stat doesn't have any trace of it so utilities like crs_setperm/crs_unregister/crs_stop won't work in this case.

ocrdump shows:

[DATABASE.LOG.testdb]

UNDEF :

SECURITY : {USER_PERMISSION : PROCR_ALL_ACCESS, GROUP_PERMISSION : PROCR_ALL_ACCESS, OTHER_PERMISSION : PROCR_READ, USER_NAME : root, GROUP_NAME : root}

[DATABASE.LOG.testdb.INSTANCE]

UNDEF :

SECURITY : {USER_PERMISSION : PROCR_ALL_ACCESS, GROUP_PERMISSION : PROCR_ALL_ACCESS, OTHER_PERMISSION : PROCR_READ, USER_NAME : root, GROUP_NAME : root}

These logs are owned by root and that's the problem.

This means that the resource was perhaps added into OCR using root.

Though it has been removed by root but now it cannot be added by oracle user unless we get rid of the aforementioned.

Shutdown the entire cluster and either restore from previous good backup of OCR using:

ocrconfig -restore backupfilename

You can get list of backups using:

ocrconfig -showbackup

If you are not sure of last good backup, there you can also do the following:

Take export backup of OCR using:

ocrconfig -export /tmp/export -s online

Edit /tmp/export and remove those 2 lines pointing to DATABASE.LOG.testdb and DATABASE.LOG.testdb.INSTANCE owned by root

Import it back now

ocrconfig -import /tmp/export

After starting the cluster, verify using ocrdump.

The OCRDUMPFILE should not have any trace of those leftover log entries owned by root.

Self Service or DMZ Setup (from 11.5.10)

2011-01-10T10:51:00.000-08:00

In Self Service or DMZ Setup (from 11.5.10) you might have noticed new configuration file url_fw.conf under $IAS_ORACLE_HOME/Apache/Apache/conf . In this post We’ll understand requirement of this file , building blocks of this file, mod_rewrite module of apache and regular expressions.

Overview of url_fw.conf file is required ?

This file is delievered by patch 3942483 (included in 11.5.10) and called by Apache/Webserver configuration file httpd.conf .

This file uses mod_rewrite module of Apache to allow/disallow URL’s matched by regular expression.

What this does? - > This file provide extra security for DMZ or Self Service implementation accessible over internet. Only few URL’s opened/allowed by this file are accessible thus protecting secured URL which should not be accessible via internet.

Why its decided to include url_fw.conf -> If node trust level is marked as external (Three type of Node Trust level for a node , External, Internal, Administration) then Autoconfig includes url_fw.conf file in httpd.conf

What is mod_rewrite and where to get more information -> mod_rewrite is URL Rewrite Engine in Apache (on which Oracle-Apache or Oracle HTTP Server or Web Server in Apps). mod_rewrite is powerful tool for URL manipulation like to

- Restrict Access to directories and files

- Conditional redirection of access

- Relocating Servers, File System or Directories

- Regeneration of static pages based on HTTP Header Variable

For more information on mod_rewrite module of apache visit http://httpd.apache.org/docs/1.3/mod/mod_rewrite.html

How to debug mod_rewite issues ?

If you think some of URL’s (complete url or partial - gif, jpg, html or jsp file) are blocked by above URL Firewall and you wish to know which file is blocked , you can enable logging by adding following directive in url_fw.conf

RewriteLog “/your_log_directory/rewrite.log”

RewriteLogLevel 7

By default logging is disabled , logLevel value is from 0-10 (0 means no logging and 10 is log everything which records all steps mod_rewrite is doing in background) a sensible value is 6 or 7 and you will see in log what URL is blocked and by what rule; so that if you think user should have access to that URL you can grant access on that resource by adding new rule in url_fw.conf

Sample url_fw.conf value and its meaning -

RewriteRule ^/$ /OA_HTML/AppsLocalLogin.jsp [R,L]

or

RewriteRule ^/OA_HTML/jsp/fnd/fndhelp.jsp$ - [L]

Here first rule is saying that when user type / i.e. after hostname , domainname and port number and then /; redirect user to /OA_HTML/AppsLocalLogin.jsp and stop applying any rewrite rule after that.

In second rule; - which means don’t do any thing and present User same url as mentioned in left side i.e. /OA_HTML/jsp/fnd/fndhelp.jsp

here [R,L] in end

R- Means Rewrite

L - Last rewrite rule (No more rule to apply after this)

In order to understand above rules , you should know regular expression and here few tips/meta characters on regular expressions

1) . (dot) means matches any characters

2) [] specifies a class

i.e.

—> [a-z] matches any lower case characters from a to z

—>[a-zA-Z0-9] matches any character upper or lower case from a to z and numeric 0 to 9

—> [abc$] matches a or b or c or $

—> [^0-9] matches anything except digit 0 to 9 . Here ^ is negation

Meta Characters in Regular Expressions

^ -> Matches Start of a line

$ -> Matches End of line

like

^appsdba -> Matches any line starting with appsdba

appsdba$ -> Matches any line ending with appsdba

^appsdba$ -> Matches any line which consist of just one word appsdba

Quantifiers for Characters

–> ? matches zero or one instance of character

–> + matches one or more instance of character

–> * matches zero or more instance of character

For Example

appsdba? matches appsdb or appsdba

appasdba+ matches appsdba, appsdbaa, appsdbaaa and so on

appsdba* matches appsdb, appsdba, appsdbaa, appsdbaaa and so on

Few error messages related to URL Firewall are

– Access to requested URL has been blocked by the url firewall

– Gone URL you are looking for is blocked by url Firewall

– Error in opening up attachments or date picker in iStore, iRec, iProc

– FW-1 at Firewall-2: Access denied

For more information on DMZ and E-Business Suite visit Steven Chan’s post at

http://blogs.oracle.com/schan/2006/05/17

HAPPY LEARNING!

2011-01-10T10:31:00.000-08:00

Oracle E –Business suite 11.5.10.2 Database upgradation from 10.2.01. to 10.2.0.2

.

1. Introduction

Upgrading from Oracle database from 10.2.0.1 to 10.2.0.2 on Sun OS using DBUA is as we upgrade on other platforms using DBUA on other platforms. Following are the differences in various platforms

• Setting operating system parameters like kernel configuration,

• Checking memory,

• Disks and other resources.

.

2. Assumption

• The Sun Solaris (UNIX) Operating System is installed on the Sun Solaris Hardware with all the required resources.

• All the required file systems are created with sufficient disk space as required for Oracle Applications Software and Database.

• The Staging area for Oracle Database is created

• Application DBA performing installation has a graphical interface with the Sun OS system. We are going to use xmanger 2.0 software for accessing the Sun OS from remote client.

• The openwin - xterm process is running on the server.

.

3 Pre – Requisite

Before Starting Upgrading process we have to apply two important patches in the Source

1. 10g Release 2 interoperability patch for 11.5.10 (4653225)

2. 5478710 (TXK (FND & ADX) AUTOCONFIG ROLLUP PATCH O

Refer Metalink Note 362203.1

4. Creating Staging Area on the Server

Staging Area is an area from where you will stage the Oracle 10GR2/Companion Software and run the installation program. Staging area for Oracle 10GR2 requires around 3-4 GB of disk space.

Mount the DVD’s of Oracle 10GR2/Download from oracle websites otn.oracle.com on the server and copy the DVD’s in the staging location.

Create directories named as the stage (this you can get from the DVD’s component.label file).

Copy all the DVD’s of similar components in same stage area.

Stage No. of DVD’s DVD Contents

Oracle11 Software 1 Disk1 /d33/stage

Oracle Companion 1 Disk2 d33/stage /stage1

.

5. Checking the Sun Operating System

System and hardware Configuration:

Hardware Status: sun4 (UNIX command; ‘arch’)

Operating System: SunOS 5.11 Generic sun4u sparc SUNW, Sun-Fire-V240 (UNIX command; ‘uname –a’).

CPU: 2 CPU’s. Each having the sparcv9 processor operates at 1503 MHz and has a sparcv9 floating point processor (UNIX command; ‘psrinfo –v’).

Memory size: 5120 Megabytes (UNIX command; ‘prtconf
grep size’).

Swap Total: (45440k bytes allocated + 4032k reserved = 49472k used) 14468936k available, i.e., 14GB (UNIX command; ‘swap –s’).

Check file System mounted and its respective size (UNIX command; ‘df -h’).

6. Create base directories for Oracle 10GR2 installation

From the mounted file system decide which file system will hold the database tier

For Ex:

/d35 – 32 GB – Oracle 10G Database Software (ORACLE_10GHOME RDBMS,)Create directory “/d35/ora10G/10.2.0”

7. Create OS group and Users and granting required privileges and file permissions

Create operating system group “DBA” and OS users “ora10G” for database tier . Note that we are planning to upgrade database tier owned by “oracle” user but both on the same host / system. But new Oracle_10G Home owner and source Oracle_9i Home owner should fall under same group.

Check the group created in “/etc/group”

# cat /etc/group
grep dba

dba::110:

Create Unix Users “oracle ”

Add these lines in “/etc/passwd” file:

ora10G:x:113:110::/orahome/ora10g:/bin/sh

Add these lines in “/etc/shadow” file:

Ora10g:30oQlK30ch6xY:13187::::::

Note that the user id is always unique and also the home directory given is the base directory as created above.

Give ownership of “/d35/ora10G” to “ora10G” .

# chown -R ora10G:dba /d35/ora10G

# ls -lrt /d35

/u01:

total 18

drwx—— 2 root root 8192 Jan 13 13:16 lost+found

drwxr-xr-x 2 oracle dba 512 Jan 23 12:34 ora10G

Give full file permissions on the staging area of Oracle Applications. Give “777” privileges on the staging area. Some unzip files are not able to get unzipped and then the installation gives error.

# pwd

/u06/stage

# ls

install runInstaller stage1

doc response stage welcome.html

#

# chmod -R 777 *

# pwd

/u06/stage

#

.

8. Universal Installer

9. Starting the X Windows session

Login to Server from a graphical interface console.

We are using Xmanager2.0 software for Oracle Universal Install and DBUA.

Go to option and select ->Session->Java Desktop System 3

Environment settings

Logged in as “ora10G” and set the profile Example ORACLE_BASE=/d35/test/v10G;export ORACLE_BASEORACLE_HOME=$ORACLE_BASE/10.2.0; export ORACLE_HOMEORACLE_SID=PATCH ; export ORACLE_SIDLD_LIBRARY_PATH=$ORACLE_HOME/lib ; export LD_LIBRARY_PATHPATH=$PATH:/usr/local/bin:/usr/ccs/bin:/usr/sfw/bin:$ORACLE_HOME/bin ; export PATHVerify the profile using $ env
more Check the inventory_loc in Sun Solaris it is located in /var/opt/oracle/oraInst.loc change the inventory location Check the oratab file in Sun Solaris it is located in /var/opt/oracle/oratab and comment the source instance because we are following the same ORACLE_SID for new ORACLE_10G .Example is below :- #v1:/d35/oracle/v1db/9.2.0:N

.

9.1 Starting Oracle 10G Software Installation.

Go to”/d33/stage” and execute “./runInstaller” command. The “Oracle 10g Database Installation Screen will Appear”.

In this screen change Global Database Name and enter password click Next.

9.2 Product Specific Prerequisite Checks

In “Product Specific Prerequisite Checks” Click Next . If any errors or warning appears rectify an proceed

9.3 Configuration Options

Select “Install Database Software Only ” and click “Next”.

.

9.4 Oracle Universal Installer Summary

In the “Oracle Universal Installer Summary” screen accept the default and click “Install ”.

9.5 Install

In “Install” You have to monitor the log file. The log file location has been highlighted in the screen.

9.6 Execute Configuration Scripts

In “Configuration Scripts” screen select “ok” and follow the instruction the screen.

9.7 End of Installation

In the “End of Installation” Screen click Exit

9.8 Oracle 10G Companion Software Installation

Go to”/d33/stage/stage1” and execute “./runInstaller” command. In the welcome screen click next

9.9 Select Product to Install

9.10 Specify Home Details

In the “Specify home Details” screen update the details as required and click “Next”.

9.11 Product Specific Prerequisite Checks

In “Product Specific Prerequisite Checks” Click Next. If any errors or warning appears rectify and proceed.

9.12 Oracle Universal Installer Summary

In the “Oracle Universal Installer Summary” screen accept the default and click “Install”.

9.13 End of Installation

In the “End of Installation” Screen click Exit.

9.14 Post Install Checks Check the log file for any errors .Login as Oracle 10G_home owner and check whether you are able to connect as sys $ sqlplus “/as sysdba”

you will get the follwing output $ sqlplus “/as sysdba” SQL*Plus: Release 10.2.0.1.0 - Production on Wed May 23 13:19:31 2007 Copyright (c) 1982, 2005, Oracle. All rights reserved. Connected to an idle instance:Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit ProductionWith the Partitioning, OLAP and Data Mining options SQL>

9.15 Before you Begin Up gradation in Source Database

a. Log in to the system as the owner of the new 10gR2 ORACLE_HOME and copy the following files from the 10gR2 ORACLE_HOME/rdbms/admin directory to a directory outside of the Oracle home, such as the /tmp directory on your system: ORACLE_HOME/rdbms/admin/utlu102i.sql ORACLE_HOME/rdbms/admin/utltzuv2.sql Make a note of the new location of these files.

.

b. Login as ORACLE_SOURCE home owner. Go to /var/opt/oracle/oratab remove the comment and startup the database. Now you connect as sys owner from location where you have copied utlu102i.sql and utltzuv2.sql in sqlplus $ sqlplus “/as sysdba” SQL>@ utlu102i.sqlSQL> spool off

c. The above sql generate output file called updatelog.lst review the output made the changes accordingly in target init.ora .SQL>@ utltzuv2.sqlSQL> spool off The above sql generate output file called timezone.log review the output and change accordingly .

.

d. Check for the deprecated CONNECT Role After upgrading to 10gR2, the CONNECT role will only have the CREATE SESSIONprivilege; the other privileges granted to the CONNECT role in earlier releases will be revoked during the upgrade.

To identify which users and roles in your database are granted the CONNECT role, use the following query:

SELECT grantee FROM dba_role_privs

WHERE granted_role = ‘CONNECT’ and grantee NOT IN ( ‘SYS’, ‘OUTLN’, ‘SYSTEM’, ‘CTXSYS’, ‘DBSNMP’, ‘LOGSTDBY_ADMINISTRATOR’, ‘ORDSYS’, ‘ORDPLUGINS’, ‘OEM_MONITOR’, ‘WKSYS’, ‘WKPROXY’, ‘WK_TEST’, ‘WKUSER’, ‘MDSYS’, ‘LBACSYS’, ‘DMSYS’, ‘WMSYS’, ‘OLAPDBA’, ‘OLAPSVR’, ‘OLAP_USER’, ’OLAPSYS’, ‘EXFSYS’, ‘SYSMAN’, ‘MDDATA’, ’SI_INFORMTN_SCHEMA’, ‘XDB’, ‘ODM’);

If users or roles require privileges other than CREATE SESSION, then grant the specific required privileges prior to upgrading. The upgrade scripts adjust the privilegesfor the Oracle-supplied users.

.

e. In Oracle 9.2.x and 10.1.x CONNECT role includes the following privileges:

SELECT GRANTEE,PRIVILEGE FROM DBA_SYS_PRIVSWHERE GRANTEE=’CONNECT’ ;

GRANTEE PRIVILEGE—————————— —————————CONNECT CREATE VIEWCONNECT CREATE TABLECONNECT ALTER SESSIONCONNECT CREATE CLUSTERCONNECT CREATE SESSIONCONNECT CREATE SYNONYMCONNECT CREATE SEQUENCECONNECT

CREATE DATABASE LINK In Oracle 10.2 the CONNECT role only includes CREATE SESSION privilege.

.

f. Create the script for dblink incase of downgrade of the database. During the upgrade to 10gR2, any passwords in database links will be encrypted. To downgrade back to the original release, all of the database links with encrypted passwords must be dropped prior to the downgrade. Consequently, the database links will not exist in the downgraded database. If you anticipate a requirement to be able to downgrade back to your original release, then save the information about affected database links from the SYS.LINK$ table, so that you can recreate the database links after the downgrade.

.

g. Following script can be used to construct the dblink.

SELECT‘create ‘

DECODE(U.NAME,’PUBLIC’,'public ‘)

’database link ‘

CHR(10)

DECODE(U.NAME,’PUBLIC’,Null, U.NAME

’.')

L.NAME

chr(10)

’connect to ‘

L.USERID

‘ identified by ”’

L.PASSWORD

”’ using ”’

L.host

””

chr(10)

’;’ TEXTFROM sys.link$ L, sys.user$ UWHERE L.OWNER# = U.USER# ;

.

h. Check the invalid objects in source instance using the following qyery :-

spool invalid_pre.lstselect substr(owner,1,12) owner, substr(object_name,1,30) object, substr(object_type,1,30) type, status from dba_objects where status <>’VALID’;

spool off

Compile all the invalid objects using Run the following script and then requery invalid objects: This script must be run as a user with SYSDBA privs using SQL*Plus:

$ cd $ORACLE_HOME/rdbms/admin

$ sqlplus ‘/as sysdba’

SQL> @utlrp.sql

Even compile all the objects using Adamin utility of Oracle E –Business Suit .

.

i. Take Complete Backup of source Application and Database

9.16 Copy the Important Parameter files

a. Login to Source ORACLE_HOME $ cd $ORACLE_HOME/dbsCopy init.ora to target ORACLE_10G home $ORACLE_HOME/network

Copy tnsnames.ora and listener.ora to target TNS_ADMIN directory

b. Made changes in init.ora and the changes which was suggested by the utlu102i.sql output.c. Made changes in Tnsanames.ora and Listener.ora as per situation.

.

10. Database Upgrade Assistant

Login to Xsession again as target Oracle_10g home owner

$cd $ORACLE_HOME/bin $./dbua

10.1 Database Upgrade Assistant Welcome Screen

In the “Database Upgrade Assistant Welcome Screen” Click Next

10.2 Database Upgrade Assistant Steps 1

In “Database Upgrade Assistant” choose the proper source database click Next

10.3 Database Upgrade Assistant Steps 2

In “Database Upgrade Assistant Steps 2 “ It will take 20 to 30 minutes to get the database information and then click Next.

10.4 Database Upgrade Assistant Steps 3

In the “Database Upgrade Assistant Steps 3” Click Yes

10.5 Database Upgrade Assistant Steps 4

In the “Database Upgrade Assistant Steps 4” Change the datafile location according to requirement Click Next

10.6 Database Upgrade Assistant Steps 5

Compile invalid Objects

In the “Database Upgrade Assistant Steps 5 Compile Invalid Objects” Select Degree of Parallelism. Degree of Parallelism depend on Number of CPU.

10.7 Database Upgrade Assistant Steps 6

In “Database Upgrade Assistant Steps 6” Select “I have already backed up my database”

And click Next

10.8 Database Upgrade Assistant Steps 7 Database Credentials

In “Database Upgrade Assistant Steps 7 Database Credentials” Enter password and click Next

10.9 Database Upgrade Assistant Steps 7 of 7 Summary

In “Database Upgrade Assistant Steps 7 of 7 Summary” Click Finish

10.10 Database Upgrade Assistant Progress

Now its time to monitor log file

End of Database Upgrade Assistant

.

11a Errors Encountered and the Solution

a. While running runInstaller Script encountered the following errors :- ./runInstaller: /backup/database/install/.oui: cannot execute This error was due to bad media, So I downloaded following files from otn.oracle.com

1. 10Gr2_db_sol.cpio (Oracle 10GR2 Software )

2. 10Gr2_companion_sol.cpio (Oracle Companion Software)

Create Stage directory in any mount point give e.g. /d33/stage 777 permission to that directory # cat 10Gr2_db_sol.cpio
cpio –icd

It will extract entire software under stage directory.

Create Stage1 directory under stage directory e.g. d33/stage /stage1 give 777 permission to that directory

# cat 10Gr2_companion_sol.cpio
cpio –icd

It will extract entire software under stage directory.

b. While Running DBUA I have found the following errors :-

This is due to target and the source database owner is not in same group

Login as root; Example source oracle database owner group is dba , so change it accordingly .

# usermod -g dba ora10g

Login as ora10g owner and execute the following command

$cd $ORACLE_HOME

$ ls -l `find . ! -user ora10g`

In my system it gave the following output

-rwsr-x— 1 root dba 69028 2006-07-17 21:20 ./bin/extjob*

-rwsr-s— 1 root dba 18845 2006-06-21 09:13 ./bin/nmb*

-rwsr-s— 1 root dba 19999 2006-06-21 09:13 ./bin/nmo*

-r-sr-s— 1 root dba 14456 2006-02-04 21:57 ./bin/oradism*

-rw-r—– 1 root dba 1534 2005-12-22 13:39 ./rdbms/admin/externaljob.ora

Change ownership to the Oracle_10g Home using following command

#chown –R ora10g:dba / (Loging as Oracle_10g home owner) example ora10g

$ cd $ORACLE_HOME/rdbms/lib

$mv config.o config.o.bkpEdit config.s file

and change the lines /* 0×0008 15 */ .ascii “v2dba”/* 0×0014 20 */ .align 8.L13:/* 0×0014 22 */ .ascii “v2dba”to/* 0×0008 15 */ .ascii “dba”/* 0×0014 20 */ .align 8 .L13:/* 0×0014 22 */ .ascii “dba”$Edit env_rdbms.mk From : KPIC_OPTION=-K PICTo : KPIC_OPTION=-xarch=v9 -K PIC$ make -f ins_rdbms.mk config.o ioracleIt has solved the problem

.

14. Summary

In this document we have covered:

1. Preparing the Sun Solaris system for Oracle 10GR2 Up gradation .

2. Creating Staging Area of Oracle 10GR2 Software.

3. Installing Oracle 10GR2 along with Companion Products on Sun Solaris Server.

4. Upgrading Oracle Database from 9.2.0.6 to 10GR2

15. References

Oracle Metalink: https://metalink.oracle.com

HAPPY LEARNING!

SGA & Hugepages Myth

2010-10-09T00:22:00.000-07:00

Hi All,

I'd like to shed some light on how Hugepages works and its relation to SGA in order to eliminate some confusion you might have.

1- These 2 are not related, no need for huegapgaes to be set, but it's advisable to set it for better memory utilization.

2- If hugepages is set, then the value is affected by the SGA size

3- If SGA is changing(increase/decrease) , hugepages must be recalculated accordingly.

Let me illustrate it:

_*Case 1 -- Hugepages not set*_
========================

* cat /proc/meminfo and if HugePages_Total = 0 , this indicates, that the hugepages not set

* Suppose we want to set the hugepages for 5GB SGA, do the following:

o 1st we must convert the 5GB to MB, so 5*1024 which equals to 5120MB

o Divide the above value by 2, so 5120/2 = 2560

o Add 3% (for over head)to the above value, so 2560+85(3%)= 2645 which is value of hugepages

o Set vm.nr_hugepages = 2645 in /etc/sysctl.conf

_*Case 2 - SGA size changed*_
======================

* If the SGA is changed(buffer cache or shared_pool), then we need to adjust the hugepages accordingly as follows:

* Suppose we're increasing the SGA by 2GB (from 5GB to 7GB), so we must increase the hugepages by 2 GB or set it to 7GB if it's not set; let's assume that the current value is set correctly and we just need to increase it by 2 GB; then we do:

o Convert the 2GB to MB, so 2*1024 which equals to 2048MB

o Divide the above value by 2, so 2048/2 = 1024 and add 3%

o Add the above value to he current hugepages value, so 2645+1054= 3799 which is the new value of hugepages

o Set vm.nr_hugepages = 3749 in /etc/sysctl.conf

And remember that you must always check the value of kernel.shmmax & kernel.shmall in /etc/sysctl.conf

Pls refer to the attached table below on how to set the value for these parameter:

HAPPY LEARNING!

How To Prevent Inactive JDBC Connections In Oracle Applications

2010-10-09T00:05:00.000-07:00

Applies to:
=======
Oracle Application Object Library - Version: 11.5.9 to 11.5.10.2
This problem can occur on any platform.

Symptoms
=======
Many Inactive JDBC connections causing performance issues in the database and in framework pages

Cause
=====
There are many reasons for the inactive JDBC sessions to happen. Following are the brief details how the JDBC connections are established and are maintained in pool.

In E-Business suite environment, the JDBC connections are established with the server when there is a database connection request comes from the client.

In Oracle applications we use the JDBC thin driver out of various database connection drivers.

The dbc file present under $FND_TOP/secure directory contains various parameters which are responsible for the connection to the database upon receiving a request from Apache Jserv.The following are the important parameters in the dbc file :

FND_MAX_JDBC_CONNECTIONS=100
FND_JDBC_BUFFER_MIN=5
FND_JDBC_BUFFER_MAX=5
FND_JDBC_BUFFER_DECAY_INTERVAL=60
FND_JDBC_BUFFER_DECAY_SIZE=1
FND_JDBC_USABLE_CHECK=true
FND_JDBC_CONTEXT_CHECK=true
FND_JDBC_PLSQL_RESET=false

The AOLJ Database connection pool is intended to have a farm of open JDBC connections to the database which can be borrowed by the java code running in the OACoreGroup for a short time. Performance wise this is more efficient since it saves opening and closing of a JDBC connection each time. This however means that a connection can be idle for quite a long time when there is little activity in the system.

Note that each JVM has it's own connection pool. So, if there are 2 JVMs running for OACore, then there are also 2 connection pools.

This is important since it also means that the max number of JDBC connections in this case is 2 x FND_MAX_JDBC_CONNECTIONS.

Specially in large environments with multiple MT servers and multiple JVM's the total number of connection could become too large.

Unfortunately there is no mechanism implemented in the connection pool which performs some kind of 'heartbeat' (like we have in Forms) for idle connections.

Also there is no mechanism in the Connection pool to determine whether the JDBC connection to the database has been dropped.

So the JDBC connection in the pool still seems to be valid until some code borrows it and then finds out that the connection has been dropped.

We can drop all the INACTIVE connections at once. Later, when high number of new requests for JDBC connections are received then a lot of new connections have to be created which does not benefit system performance. The JDBC connection pool, neither knows nor cares whether a given user is still logged in. It only cares how many different user sessions need access to the database right now.

Solution
======
Following proactive checks can be done to prevent high number of inactive JDBC connections :

1. Check the for the JDK version being used it the instance. From JDK 1.4.2 onwards as a thumb rule it is suggested to use 1 JVM per CPU for 100 active connected users to OACoreGroup.

Use the script to determine "active users" for OACoreGroup :

REM
REM SQL to count number of Apps 11i users
REM Run as APPS user
REM
select 'Number of user sessions : '
count( distinct session_id) How_many_user_sessions from icx_sessions icx where disabled_flag != 'Y'
and PSEUDO_FLAG = 'N'
and (last_connect + decode(FND_PROFILE.VALUE('ICX_SESSION_TIMEOUT'), NULL,limit_time, 0,limit_time,FND_PROFILE.VALUE('ICX_SESSION_TIMEOUT')/60)/24) > sysdate and counter < limit_connects;
REM
REM END OF SQL
REM

Note 362851.1 : Guidelines to setup the JVM in Apps Ebusiness Suite 11i and R12

2. For application version 11.5.10 onwards ensure ATG_PF.H is applied onto the instance. Also ensure that you are using the latest version of the JDBC driver. You may run the following sql to get the current JDBC driver version in the system :

select bug_number, decode(bug_number,
'3043762','JDBC drivers 8.1.7.3',
'2969248','JDBC drivers 9.2.0.2',
'3080729','JDBC drivers 9.2.0.4 (OCT-2003)',
'3423613','JDBC drivers 9.2.0.4 (MAR-2004)',
'3585217','JDBC drivers 9.2.0.4 (MAY-2004)',
'3882116','JDBC drivers 9.2.0.5 (OCT-2004)',
'3966003','JDBC drivers 9.2.0.5 (OCT-2004)',
'3981178','JDBC drivers 9.2.0.5 (NOV-2004)',
'4090504','JDBC drivers 9.2.0.5 (JAN-2005)',
'4201222','JDBC drivers 9.2.0.6 (MAY-2005)') Patch_description
from ad_bugs
where bug_number in
('3043762',
'2969248',
'3080729',
'3423613',
'3585217',
'3882116',
'3966003',
'3981178',
'4090504',
'4201222'
) order by 2;

3. Ensure that you have all the database initialization parameters set and all the recommended database performance patches applied on the instance as per the following notes :

Note 216205.1 : Database Initialization Parameters for Oracle Applications 11i
Note 396009.1 : Database Initialization Parameters for Oracle Applications Release 12
Note 244040.1 : Oracle E-Business Suite Recommended Performance Patches

4. Implement a strategy to minimize the JDBC connections

If JDBC connections are being retained in the pool then this will ensure connections are dropped as soon as the application has finished with it.

Apart from ensuring minimum DB connections it will also help to identify if there is a JDBC connection leak

a) De-tune JDBC connection pool

Do through Autoconfig (or manually update DBC file) on all Middle Tier servers

FND_JDBC_BUFFER_DECAY_INTERVAL=120
FND_JDBC_BUFFER_MIN=0
FND_JDBC_BUFFER_MAX=0
FND_MAX_JDBC_CONNECTIONS=256
FND_JDBC_USABLE_CHECK=true
FND_JDBC_BUFFER_DECAY_SIZE=5

Note : "FND_JDBC_USABLE_CHECK=true" is preferred for RAC, as discussed in note
Note 278868.1 : AOL/J JDBC Connection Pool White Paper
Note 294652.1 : E-Business Suite 11i on RAC : Configuring Database Load balancing & Failover

5. Ensure that the current TCP settings are set as recommended by Oracles tcpset.sh script and also from OS vendor web site

Parameter Recommended value

tcp_ip_abort_interval 60,000
tcp_keepalive_interval 900,000
tcp_rexmit_interval_initial 1500

6. Check the jserv.properties "security" settings

Ensure that the current setting does not allow more connections to the JVM than JDBC connections, which is not best practice.

Changing security.backlog, in particular, can lead to user connections hanging.
Maintain default settings
security.maxConnections=256
# security.backlog=5

7. Check ApJServRetryAttempts parameter in Jserv.conf

This setting will delay any recovery of a dead JVM by mod_oprocmgr. If a JVM is not responding in 45 minutes (default setting) then tuning should be implemented to resolve this, rather than allowing 45 minutes of no response.

Maintain default settings

ApJServRetryAttempts 3

8. Disable JVM Distributed Caching

If some JVMs out of many are not servicing requests and generating "java.lang.NoClassDefFoundError" errors. Disabling Distributed JVM caching would eliminate this cause of the problem.

Disabling Distributed JVM Cache is achieved by changing "LONG_RUNNING_JVM=" from "true" to "false" in the jserv.properties. This is controlled by AutoConfig parameter "s_long_running_jvm"

9. Prevent wastage of database connections on the system by setting the profile option

'FND: Application Module Pool Minimum Available Size' value to 0 (which is the default).

After doing the above monitor the progress in terms of connection utilization. Also keep collecting the following information from system periodically:

column module heading "Module Name" format a48;
column machine heading "Machine Name" format a15;
column process heading "Process ID" format a10;
column inst_id heading "Instance ID" format 99;
prompt
prompt Connection Usage Per Module and process
select to_char(sysdate, 'dd-mon-yyyy hh24:mi') Time from dual
/
prompt ~~~~
select count(*), machine, process, module from v$session
where program like 'JDBC%' group by machine, process, module order by 1 asc
/

10. The following sqls can be useful

+ To find total number of open database connections for a given JVM PID

SELECT s.process, Count(*) all_count FROM v$session s WHERE s.process IN () GROUP BY s.process

+ To find number of database connections per JVM that were inactive for longer then 30 minutes

SELECT s.process, Count(*) olderConnection_count FROM v$session s WHERE s.process IN ()
and s.last_call_et>=(30*60) and s.status='INACTIVE' GROUP BY s.process

+ To find the modules responsible to JDBC connections for a process id

SELECT Count(*), process,machine, program, MODULE FROM v$session s
WHERE s.process IN ('&id')GROUP BY process,machine, program, MODULE ORDER BY process,machine, program, MODULE;

11. For processes that show the highest counts in query result above, Log in to the JVM machine and run

"kill -3 jvmid".

where jvmid is the process seen in query output above.

The output goes to OACoreGroup.*.stdout file. Post those files to Oracle Support for reveiw.

12. If there seems to be a lot of blocked sessions in the database. Check 2 or 3 locked/blocked sessions to ascertain the user, sql and row locked.

You can use utllockt.sql ( Note 166534.1 : Resolving locking issues using utllockt.sql script ) as the starting point. Post the output from utllockt.sql in addition to the SQL / row lock information for 2 or 3 sessions to Oracle Support.

References
========
Note 164317.1 - Upgrading JDBC drivers with Oracle Applications 11i
Note 166534.1 - Resolving locking issues using utllockt.sql script
Note 216205.1 - Database Initialization Parameters for Oracle Applications Release 11i
Note 244040.1 - Oracle E-Business Suite Recommended Performance Patches
Note 278868.1 - AOL/J JDBC Connection Pool White Paper
Note 294652.1 - E-Business Suite 11i on RAC : Configuring Database Load balancing & Failover

HAPPY LEARNING!

AWR- Wait Event Classes

2010-10-08T09:32:00.000-07:00

Hi,

I have tried to classify the AWR wait events and the actions to be taken for each wait events. This chart should help in diagnosing the wait events shown in AWR reports.

To view the chart clearly, click on the chart it will be opened in a new page.

HAPPY LEARNING!

Oracle Application Server FAQ

2010-10-02T21:52:00.000-07:00

1. Which are the main 3 Oracle e-business platform components ?
2. What is the Oracle Application Server ?
3. Which are the key components of Oracle Application Server (OAS) ?
4. Which are the key components of the OAS Identity Management ?
5. Which are the 2 main components of Oracle Application Server ?
6. Which are the Middle-Tier components ?
7. Which are the main components of OracleAS Infrastructure ?
8. Which are the 3 categories of Metadata Repository ?

1. Which are the main 3 Oracle e-business platform components ?
• Oracle Application Server: to help deploy Internet applications
• Oracle Database Server: to store enterprise business data
• Oracle Developer Suite: to develop Internet applications

2. What is the Oracle Application Server ?
The Oracle Application Server is an Integrated Software Infrastructure for Enterprise Applications used to deploy Internet applications but also to create and manage enterprise portals and mobile devices, to automate business process and to provide real-time business intelligence.

3. Which are the key components of Oracle Application Server (OAS) ?
• OC4J ( Oracle Application Server Containers for J2EE)
• Oracle HTTP Server
• Oracle Internet Directory
• Oracle AS Web Cache
• Oracle AS Portal
• Oracle AS Wireless
• Oracle Identity Management (in addition to OID, it includes the Oracle SSO and Oracle CA)
• Management, Integration and Security Components (Oracle Application Server Control, Oracle Internet Directory )
• Oracle Business Intelligence (Oracle AS Discoverer)

4. Which are the key components of the OAS Identity Management ?
• Oracle Internet Directory (OID) = single store for all type of user information
• Oracle Delegated Administrative Services (DAS) = manage the OID
• Oracle Directory Integration and Provisioning. Directory Integration refers to the synchronization of the OID with other irectories and user repositories. Directory Provisioning refers to the Oracle AS 10g feature that enable the creation and management of users' accounts and privileges for various Oracle components and applications
• OracleAS Single Sign-On (SSO) = provide the transparent logon to all SSO-enabled applications on all application servers in an Oracle AS farm
• OracleAS Certificate Authority (CA) = manage the public-key certificates.

5. Which are the 2 main components of Oracle Application Server ?
• Oracle AS Infrastructure = supports the functioning of the Oracle AS middle-tier components
• Oracle AS Middle-Tier = all the applications are deployed and run from middle-tier.

6. Which are the Middle-Tier components ?
• Oracle HTTP Server
• Oracle Containers for J2EE (OC4J)
• Oracle AS Portal
• Oracle AS Web Cache

7. Which are the main component of OracleAS Infrastructure ?
• OracleAS Metadata Repository = a part of an Oracle Database which store information for various Oracle AS components;
• Oracle Identity Managemant

8. Which are the 3 categories of Metadata Repository ?
• Product metadata = used by products like Oracle AS Portal or Oracle AS Wireless
• Identity Management metadata = used by OID, SSO, CA
• Configuration Management metadata

Oracle AS 10g let you install the Oracle AS Metadata Repository into an existing Oracle database; otherwise, it creates a new Oracle Database 10g for this purpose.

HAPPY LEARNING !

Configuring Middle-Tier JVMs for Applications 11i

2010-10-01T20:14:00.000-07:00

When you call Oracle Support with a problem like apj12 errors in your mod_jserv.log, middle-tier Java Virtual Machines (JVMs) crashing, or poor middle-tier performance, then it will often be suggested to increase the number of JVM processes. So, the key question that likely occurs to you is, "How many JVMs are required for my system?"

Processing Java Traffic in Groups

First, some quick background: web requests received by Oracle HTTP Server (Apache) to process Java code is sent to one of four different types of JVM groups to be processed. You can see this in the jserv.conf file:

ApJServGroup OACoreGroup 2 1 /usr/.../jserv.properties
ApJServGroup DiscoGroup 1 1 /usr/.../viewer4i.properties
ApJServGroup FormsGroup 1 1 /usr/.../forms.properties
ApJServGroup XmlSvcsGrp 1 1 /usr/.../xmlsvcs.properties

The number of JVMs for each group is signified by the first number on each line.

OACoreGroup is the default group. This is where most Java requests will be serviced

DiscoGroup is only used for Discoverer 4i requests

FormsGroup is only used for Forms Servlet requests

XmlSvcsGrp is for XML Gateway, Web Services, and SOAP requests

In the example above, I have two JVMs configured for OACoreGroup and one JVM configured for each of the other groups.

Factors Affecting Number of JVMs Required

Determining how many JVMs to configure is a complex approximation, as many factors need to be taken into account. These include:

Hardware specification and current utilization levels

Operating system patches and kernel settings

JDK version and tuning

Applications code versions, especially JDBC and oJSP

JServ configuration file tuning (jserv.properties and zone.properties)

Applications modules being used

How many active users

User behaviour

Rough Guidelines for JVMs

Luckily, Oracle Development have undertaken various performance tests to establish some rules of thumb that can be used to configure the initial number of JVMs for your system.

OACoreGroup

1 JVM per 100 active users

DiscoGroup

Use the capacity planning guide from Note 236124.1 "Oracle 9iAS 1.0.2.2 Discoverer 4i: A Capacity Planning Guide"

FormsGroup

1 JVM per 50 active forms users

XmlSvcsGrp

1 JVM is generally sufficient

In addition to this, Oracle generally recommends no more than 2 JVMs per CPU. You also need to confirm there are enough operating system resources (e.g. physical memory) to cope with any additional JVMs.

Your Mileage Will Vary

The general guidelines above are just that -- they're very broad estimates, and your mileage will vary. As I write this, the Applications Technology Group is working on a JVM Sizing whitepaper that will provide guidelines based on whether your E-Business Suite deployment is small, medium, or large. I'll profile this whitepaper here as soon as it's released publicly.

Until then, it's critical that you test your environment under load, using transactional tests that closely mirror what your users will be doing. It's useful to use automated testing tools for this, as you create your benchmarks.

Here are a couple of quick-and-dirty tools that might be useful in sizing your JVMs.

Script to determine "active users" for OACoreGroup

REM
REM SQL to count number of Apps 11i users
REM Run as APPS user
REM

select 'Number of user sessions : '
count( distinct session_id) How_many_user_sessions
from icx_sessions icx
where disabled_flag != 'Y'
and PSEUDO_FLAG = 'N'
and (last_connect + decode(FND_PROFILE.VALUE('ICX_SESSION_TIMEOUT'), NULL,limit_time, 0,limit_time,FND_PROFILE.VALUE('ICX_SESSION_TIMEOUT')/60)/24) > sysdate
and counter < limit_connects;

REM
REM END OF SQL
REM

How to determine "active forms users" for FormsGroup

Check the number of f60webmx processes on the Middle Tier server. For example:

ps -ef
grep f60webx
wc -l

Conclusion
The number of required JVMs is extremely site-specific and can be complex to predict
Use the rules of thumb as a starting point, but benchmark your environment carefully to see if they're adequate
Proactively monitor your environment to determine the efficiency of the existing settings and reevaluate if required

More on Java Memory Tuning Later

Once you've established the right number of JVMs to use, it's then time to optimize them. I'm intending to discuss Java memory tuning and OutOfMemory issues in a future article. Stay tuned

HAPPY LEARNING!

How to determine if I/O is the real issue from AWR report?

2010-10-01T19:39:00.000-07:00

DB FILE SEQUENTIAL READ

=======================

'Wait Time` = 493 x 100% / 83.2% = 592.54 (s)

'Service Time' = 11023 (s)

'Response Time' = 11023 + 592.54 = 11615.54 (s)

If we now calculate percentages for all the 'Response Time' components:

CPU time = 94.8 %

db file sequential read = 4.24 %

control file sequential read = 0.16 %

control file parallel write = 0.1 %

db file parallel write = 0.07 %

Summary:

It is now obvious that the I/O-related Wait Events are not really a significant component of the overall Response Time and that subsequent tuning should be directed to the Service Time component i.e. CPU consumption.

HAPPY LEARNING!

RAC- DBA Cheatsheet

2010-10-01T19:29:00.000-07:00

HAPPY LEARNING!

R12- New File System Architecture

2010-10-01T07:30:00.000-07:00

Base Directory: The directory where the Oracle Applications is installed. This is a generic directory.

I will suppose that the Base Directory is /APPS

$ORACLE_HOME (for the database tier): /APPS/db/tech_st/10.2.0

$APPL_TOP: /APPS/apps/apps_st/appl

$COMMON_TOP: /APPS/apps/apps_st/comn

$ORACLE_HOME (for the apps tier): /APPS/apps/tect_st/10.1.2

$IAS_ORACLE_HOME (for the apps tier): /APPS/apps/tect_st/10.1.3

$INST_TOP: /APPS/inst/apps/ contains certifications, configurations, logs for the servers.

HAPPY LEARNING!

Installing Oracle Apps 11i

2010-10-01T06:52:00.000-07:00

Introduction

This post is about installing Oracle Apps 11i using rapid install. This is a very brief discription of the install document provided by Oracle. For details, you can always refer to the official docs of Oracle. I am providing the installation sequence for one of my test instance. I hope this will serve as a fast and simple docs for you to quickly understand the installation and the steps.

Basic installation of Oracle Applications 11i is divided into 3 parts.

1. Pre-installation

2. Installation

3. Post-installation.

Pre-Installation

i) Checking system requirement

In pre-installation we check about

1) Software Requirement

2) CPU Requirement

3) Memory Requirement

4) Disk Space Requirement

required for installing Oracle Applications.

ii) Creating staging area

We then create a staging area where we download and extract all the required files. The staging area after extracting the software will look as shown below.

[root@ocvmrh2122 11i10_CU2_115102]# ls -rlt

total 24

drwxr-xr-x 5 root root 4096 Oct 13 2005 oraDB

drwxr-xr-x 26 root root 4096 Oct 13 2005 oraAppDB

drwxr-xr-x 6 root root 4096 Oct 13 2005 oraiAS

drwxr-xr-x 10 root root 4096 Oct 13 2005 oraApps

drwxr-xr-x 9 root root 4096 May 7 2007 startCD

You need around 24G for staging area after extraction.

iii) Creating User Accounts

Before we start installation we need to create 2 users. One user (APPLMGR) will be the owner of middle tier and other user (ORACLE) will be the owner of database. Assign the primary owner as “oinstall” and secondary owner as “dba” for both the users.

Check the display setting before starting the installation. You can set the DISPLAY to hostname:0.0

Installation

We begin installation by running rapidwiz present in startCD/Disk1/rapidwiz/ directory. Below are the screen shots that you will see. I have given the screen shots with my input for your easy referrence so that you can go through the same fast.

Welcome screen lists the database version and the technology stack components that are installed with the E-Business Suite. Click on next.

You can either make a new installation of upgrade an existing installation. In our case we are going to do a new installation. In case of Express Configuration you supply a few basic parameters, such as database type and name, top-level install directory, and increments for port settings. The remaining directories and mount points are supplied by Rapid Install using default values.

If you have previous installation saved configuration file you can give that as input. If you answer No, Rapid Install saves the configuration parameters you enter on the wizard screens in a new configuration file (config.txt) that it will use to configure your system for the new installation.

You can either select a single node installation of a multi-node installation. In our case we are going for a single node installation.

Select database type. We can either have a Vision demo database or a production database. Production database won’t have any data. Vision demo database will have test data present for our testing. If you are using Vision demo database then your database will need around 130G-140G of space. Else in case of production database space required would be 45G.

Set up the Oracle user and base install directory. Once you set the base install directory, all other directories will be set automatically. You can also edit the individual directories like ORACLE_HOME or db file location as per your requirement.

Select the type of licensing you got from Oracle. Completing a licensing screen does not constitute a license agreement. It simply registers your products as active.

If you select E-Business Suite price bundle then you will see this screen with some of the checkbox grayed. The products that are checked and grayed are licensed automatically as a part of the suite. The ones that are not must be registered separately as additional products — they are not part of the E-Business Suite price bundle. Place a check mark next to any additional products you have licensed and want to register.

Some systems require the country-specific functionality of a localized Applications product. For example, if your company operates in Canada, products such as Human Resources require additional features to accommodate the Canadian labor laws and codes that differ from those in the United States. In such situation, select the proper country. In my case there is no country specific functionality.

Select additional language. By default US will be selected. If you want to install any more language, then you can always select from the available list.

The Select Internationalization Settings screen derives information from the languages you entered on the Select Additional Languages screen. You use it to further define NLS configuration parameters.

Select the user and base directory for the application side installation. Once you set the base install directory, all other directories will be set automatically. You can also edit the individual directories like APPL_TOP, COMMON_TOP etc. as per your requrement.

Provide the domain name and the port ranges. Give the ports that are not used before as per your knowledge. Anyway the installer will check for port conflicts before it installs the application. You can even change the individual port setting as well.

You have now completed all the information Rapid Install needs to set up and install a single-node system. The Save Instance-specific Configuration screen asks you to save the values you have entered in the wizard in a configuration file.

Review pre-install checks. This will check whether all the requirements are met or not.

Once all the requirements are met, please proceed further to install the application.

Before installation it will give the summary of the techstack its going to install. Click on next.

You can see the progress of installation.

Once all the installation is done, it will show the components installed and its status.

Post-Installation

You can check for the post install steps from the metalink note ID 316365.1 as applicable.

Reference:

Oracle Apps 11i – Install Docs

Metalink Note ID: 316365.1

HAPPY LEARNING!

Steps in Applying a Patch - Oracle Apps 11i

2010-10-01T04:08:00.000-07:00

a) login as applmgr and set the environment. For the Windows environment also, you have to test that CLASSPATH contains %JAVA_TOP%, %JAVA_TOP%\loadjava.zip

b) create a PATCH_TOP directory in the Base Directory (at the same level as APPL_TOP, COMMON_TOP, etc: this is just a recommandation) for the patches which will be downloaded. If this directory exists, this step can be skipped. An OS environment variable could be created for this directory. This will be done only one time, when the first patch will be applied.

c) download the patch you want to apply in PATCH_TOP directory and unzip the patch.

d) understand the README.txt file and complete the prerequisite or manual steps. Here, if there are any patched to apply as pre-requisite, in general, is created a document with all the steps involving in the patching process and the pre-requisite patches will be applied before the initial patch.

e) assure that the PLATFORM variable environment (under UNIX, Linux, Solaris) is set

f) Shut down APPS services. The database services and the listener must be up and running.

g) Enable Maintenance Mode.

h) Start AutoPatch in interactive mode. this task must be done from the directory where the patch driver is/was unzipped. Also, respond to the adpatch prompts. If there are more drivers to apply (there is no unified drive: there could be a database (d), copy (c) or generate (g) drive) restart the adpatch and apply the other patches.

i) Review the log files. By default, the location is $APPL_TOP/admin//log and the file is adpatch.log.

j) Review the customizations (if any). If a customization was modified by this patch, the customization must be applied again.

For the customizations please look into the $APPL_TOP/admin/applcust.txt file.

k) Disable Maintenance Mode

l) Restart APPS services

m) Archive or delete the AutoPatch backup files.

2. How could I test the impact of the patch on the APPS environment ?

AutoPatch must be run in test mode (apply=no). The APPS services must be stopped and the Maintenance Mode must be enabled as well. To see which is the impact on the system, you can use Patch Impact Analysis in the Patch Wizard.

3. May I apply a patch if the APPS services are running and the Maintenance Mode is not enabled ?

If this is possible the README.txt will let you know. If the patch README.txt file will not state this explicitly, that means you have to stop the APPS processes and to enable the Maintenance Mode. The help files can always be applied without stopping the APPS services.

If a patch can be applied without stopping the APPS services we have to use the option hotpatch.

4. What is a non-standard patch ?

A non-standard patch is a regular patch (with a similar structure as a standard patch), but the naming is not standard (the naming of the driver file).

A standard patch is named u.drv, c.drv, d.drv or g.drv. The has 6-8 digits.

5. Which are the Oracle Applications Patch types ?

HAPPY LEARNING!

Oracle Advanced Replication - Setup

2010-10-01T03:38:00.001-07:00

Steps for Setting Up Advanced Replication
Step 1 - Create 2 users: REPADMIN and REPSYS.
REPADMIN will perform all administration tasks related with advanced replication. REPSYS performs operations on SYS's behalf, as required by the advanced replication packages. SYSTEM will hold the replication tables. Set up the db links and grant the users the appropriate privileges.

At the master definition site:

REM Run this first on the main database as SYS.
REM Function: sets up the advanced replication users.
REM
SET echo on
SPOOL cr_rep1.out
CONNECT sys/&sys_passwd
ALTER database rename global_name to NEMP.WORLD;

CREATE USER repsys identified by &repsys_passwd
default tablespace tools
temporary tablespace temp
quota unlimited on tools;

GRANT connect,resource to repsys;
DROP public database link PRODREP.world;

CREATE PUBLIC database link PRODREP.WORLD using 'PRODREP.WORLD';
DROP database link PRODREP.WORLD;

CREATE DATABASE link PRODREP.WORLD connect to repsys identified by &repsys_passwd;

CREATE USER repadmin identified by &repadmin_passwd
default tablespace tools
temporary tablespace temp
quota unlimited on tools;

GRANT dba to repadmin;
GRANT execute on dbms_defer to repadmin with grant option;
GRANT execute on dbms_defer_query to repadmin;
EXECUTE dbms_repcat_admin.grant_admin_any_repgroup('repadmin');
EXECUTE dbms_repcat_auth.grant_surrogate_repcat('repsys');
CONNECT repadmin/&repadmin_passwd
DROP database link PRODREP.world;

CREATE DATABASE link PRODREP.WORLD connect to repadmin
identified by &repadmin_passwd;
GRANT execute on sys.dbms_defer to ACME;

CONNECT ACME/&ACME_passwd
DROP database link PRODREP.world;

CREATE DATABASE link PRODREP.WORLD connect to ACME
identified by &ACME_passwd;
SPOOL off;
Step 2 - Create the replication administration users at the master site, with the appropriate database links and database privileges.
REM To be run on the master site as SYS
REM Sites up the replication admin users and objects
REM
SET echo on
SPOOL cr_rep1.out
CONNECT sys/&sys_passwd
ALTER database rename global_name to PRODREP.WORLD;

CREATE USER repsys identified by &repsys_passwd
default tablespace tools
temporary tablespace temp
quota unlimited on tools;

GRANT connect,resource to repsys;
DROP public database link NEMP.WORLD;

CREATE PUBLIC database link NEMP.WORLD using 'NEMP';
DROP database link NEMP.WORLD;

CREATE DATABASE link NEMP.WORLD connect to repsys
identified by &repsys_passwd;

CREATE USER repadmin identified by &repadmin_passwd
default tablespace tools
temporary tablespace temp
quota unlimited on tools
/

GRANT dba to repadmin;
GRANT execute on dbms_defer to repadmin with grant option;
GRANT execute on dbms_defer_query to repadmin;
EXECUTE dbms_repcat_admin.grant_admin_any_repgroup('repadmin');
EXECUTE dbms_repcat_auth.grant_surrogate_repcat('repsys');
CONNECT repadmin/&repadmin_passwd
DROP database link NEMP.WORLD;

CREATE DATABASE link NEMP.WORLD connect to repadmin
identified by &repadmin_passwd;

GRANT execute on sys.dbms_defer to ACME;
CONNECT ACME/&ACME_passwd
DROP database link NEMP.WORLD;

CREATE DATABASE link NEMP.WORLD connect to ACME
identified by &ACME_passwd;
SPOOL off;
SET echo off
From here on, you must perform all tasks as the REPADMIN user!

Step 3 - At the master site, pre-create the objects to be replicated.
If the table is constantly changing at the master definition site, it is better to create just the structures and have the replication manager copy the rows while creating the replication objects.

Step 4 - At the master definition site, create a replication schema.
exec dbms_repcat.create_master_schema('ACME');
Step 5 - At the master definition site, create the required replication group.
exec dbms_repcat.create_master_repgroup('BILLING_RUN');
Step 6 - Add the objects to the group. For example:
exec dbms_repcat.create_master_repobject(-
SNAME=>'ACME',ONAME=>'BILLINGSMELTERREDUCTION',TYPE=>'TABLE',-
USE_EXISTING_OBJECT=>true,COPY_ROWS=>true,GNAME=>'BILLING_RUN');
Step 7 - Generate replication support for the object.
This will create the packages and triggers for advanced replication.

exec dbms_repcat.generate_replication_support('BILLING_RUN', 'BILLINGSMELTERREDUCTION','TABLE');
Step 8 - Add one or more master site to the group.
exec dbms_repcat.create_master_database(-
SNAME => 'ACME',oname => 'BILLINGSMELTERREDUCTION',type=>'TABLE',-
USE_EXISTING_OBJECT=>true,copy_rows => true,gname => 'BILLING_RUN');
At this point, there should be a jobs submitted automatically to the master site and master definition site:

JOB WHAT
---- ------------------------------------------------------------
INTERVAL LAST_DATE LAST_SEC BR NEXT_DATE NEXT_SEC
-------------------- --------- -------- -- --------- --------
41 dbms_repcat.do_deferred_repcat_admin('"ACME"', FALSE);
SYSDATE + (5/1440) 08-JUL-98 18:03:19 N 08-JUL-98 18:13:19
This job will execute the tasks listed in the view DBA_REPCATLOG. For example, add replication object, add master site, etc.

At the master definition site, the job to replicate all SQL command should also be automatically submitted. For example:

JOB WHAT
---- ------------------------------------------------------------
INTERVAL LAST_DATE LAST_SEC BR NEXT_DATE NEXT_SEC
-------------------- --------- -------- -- --------- --------
27 sys.dbms_defer_sys.execute(destination=>'PRODREP.WORLD', execute_as_user=>TRUE,
batch_size=>140);
sysdate+5/1440 08-JUL-98 18:01:12 N 08-JUL-98 18:06:12
Step 9 - Resume master activity.
Once DBA_REPCATLOG is cleared, ie, job 41 is completed successfully, you can resume master activity. Errors in executing job 41 will be recorded in DBA_REPCATLOG. To resume master activity, issue the command listed below.

Exec dbms_repcat.resume_master_activity('BILLING_RUN');
From this point the replication group should change from QUIESCED to NORMAL mode. While the database is in QUIESCED mode, only SELECT statements are allowed against the database. In NORMAL mode, all DMLs are allowed.

HAPPY LEARNING!

GUIDE ON CREATING PHYSICAL STANDBY USING RMAN DUPLICATE

2010-10-01T03:29:00.001-07:00

METALINK DOCID: 1075908.1 STEP BY STEP GUIDE ON CREATING PHYSICAL STANDBY USING RMAN DUPLICATE...FROM ACTIVE DATABASE
========================= WITHOUT SHUTTING DOWN THE PRIMARY AND USING PRIMARY ACTIVE DATABASE FILES [ID 1075908.1]

STEP-1 (KEEP THE FOLLOWING DETAILS IN HANDY)
============================================
PRIMARY Database Details
========================
SQL> sho parameter spfile

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string +DATADG1/spfilepmcpbs21.ora

SQL> sho parameter control_files

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_files string +DATADG1/bcrpbs20/controlfile/control01.ctl

SQL> sho parameter db_recovery_file_dest

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string +datadg1 <-- This parameter should be definedSQL> select FILE#,NAME from v$datafile;

FILE# NAME
---------- ----------------------------------------------------------------------------------------------------
1 +DATADG1/bcrpbs20/datafile/system.279.699561171
2 +DATADG1/bcrpbs20/datafile/sysaux.280.699561057
3 +DATADG1/bcrpbs20/datafile/undotbs1.276.699561241
..
..
..
73 +DATADG1/bcrpbs20/datafile/pbsd_racs_data.469.721768271
74 +DATADG1/bcrpbs20/datafile/pbsd_price_idx.464.721768335
76 +DATADG1/bcrpbs20/datafile/pbsd_stg_raw_data.450.721859367
77 +DATADG1/bcrpbs20/datafile/pbsd_racs_data.443.721859377
78 +DATADG1/bcrpbs20/datafile/pbsd_price_idx.470.721859383

60 rows selected.

SQL>

SQL> select MEMBER from v$logfile;

MEMBER
--------------------------------------------------------------------------------
+DATADG1/bcrpbs20/onlinelog/group_1.378.713448383
+DATADG1/bcrpbs20/onlinelog/group_1.369.713448389
+DATADG1/bcrpbs20/onlinelog/group_5.265.713446555
..
..
..
+DATADG1/bcrpbs20/onlinelog/group_33.421.713454787
+DATADG1/bcrpbs20/onlinelog/group_33.422.713454793

STANDBY Database Details
========================

SQL> sho parameter spfile

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string +DATADG1/pmcpbs20/spfilepmcpbs2.ora
SQL> sho parameter control_files

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_files string +DATADG1/pmcpbs20/controlfile/control01.ctl

SQL>

SQL> select FILE#,NAME from v$datafile;

FILE# NAME
---------- ----------------------------------------------------------------------------------------------------
1 +DATADG1/pmcpbs20/datafile/system.661.727505763
2 +DATADG1/pmcpbs20/datafile/sysaux.660.727505841
3 +DATADG1/pmcpbs20/datafile/undotbs1.662.727505217
..
..
..
77 +DATADG1/pmcpbs20/datafile/pbsd_racs_data.656.727505911
78 +DATADG1/pmcpbs20/datafile/pbsd_price_index.653.727506103

57 rows selected.

SQL>

STEP-2 TAKE BACKUP OF THE SPFILE OR PFILE OF STANDBY DATABASE
======================================================

(i) If standby is using spfile, create pfile using spfile in temporary location, If standby was using pfile
make a copy of the pfile to the temporary location
(ii) In the pfile created in (i), Make parameter cluster_database=false , If standby is in RAC.
(ii) Shutdown the database(RAC-using srvctl) and start the DB as standalone with the PFILE taken as backup

bash-3.00$ ls -ltr
total 179752
-rw-r--r-- 1 oracle oinstall 3553 Aug 26 00:59 pfile_bkp.ora
bash-3.00$ pwd
/home/oracle/PFILE_CONTROL
bash-3.00$

STEP-3 CREATE AN RMAN .RCV FILE AS BELOW
=========================================
run {
allocate channel prmy1 type disk;
allocate channel prmy2 type disk;
allocate channel prmy3 type disk;
allocate channel prmy4 type disk;
allocate channel prmy5 type disk;
allocate channel prmy6 type disk;
allocate auxiliary channel stby1 type disk;
allocate auxiliary channel stby2 type disk;
allocate auxiliary channel stby3 type disk;
allocate auxiliary channel stby4 type disk;
duplicate target database for standby from active database
spfile
set db_unique_name='PMCPBS20'
set control_files='+DATADG1/pmcpbs20/controlfile/control01.ctl'
set cluster_database='false'
set service_names='PMCPBS20'
set fal_client='pmcpbs20'
set fal_server='bcrpbs20'
set standby_file_management='AUTO'
set log_archive_config='dg_config=(PMCPBS20,BCRPBS20)'
set log_archive_dest_1='location="+ARCHDG1", valid_for=(ALL_LOGFILES,ALL_ROLES)'
nofilenamecheck;
}

Where,

.. set db_unique_name='PMCPBS20' <<-------- This should be the unique name of standby database.. set control_files='+DATADG1/pmcpbs20/controlfile/control01.ctl' <<-------- This was the control file location found in STEP-1.. set cluster_database='false' <<-------- For RAC this parameter is set FALSE in STEP-2.. set service_names='PMCPBS20' <<-------- This SERVICE NAME should connect to standby databaseSTEP-4 RUN THE BELOW .RCV IN STANDBY SITE AS SHOWN BELOW========================================================nohup rman target USER/PWD@BCRPBS20_forrebuild auxiliary USER/PWD@PMCPBS20 cmdfile /home/oracle/PFILE_CONTROL/Rebuilt_BCRPBS20_26thAug.rcv log /home/oracle/PFILE_CONTROL/Rebuilt_BCRPBS20_26thAug.log &Where,USER/PWD@BCRPBS20_forrebuild <<---- BCRPBS20_forrebuild(This tns entry should be added to TNSNAMES.ORA for connecting to PRIMARY DB to only 1 node)USER/PWD@PMCPBS20 <<---- PMCPBS20 (This tns entry should point to standby database itself)LOG OF THE ABOVE STANDBY CREATION ACTIVITY SHOULD BE AS BELOW ==============================================================Recovery Manager: Release 11.1.0.7.0 - Production on Thu Aug 26 04:22:32 2010Copyright (c) 1982, 2007, Oracle. All rights reserved.connected to target database: PMCPBS20 (DBID=1148223702)connected to auxiliary database: PMCPBS20 (not mounted)RMAN> run {
2> allocate channel prmy1 type disk;
3> allocate channel prmy2 type disk;
4> allocate channel prmy3 type disk;
5> allocate channel prmy4 type disk;
6> allocate channel prmy5 type disk;
7> allocate channel prmy6 type disk;
8> allocate auxiliary channel stby1 type disk;
9> allocate auxiliary channel stby2 type disk;
10> allocate auxiliary channel stby3 type disk;
11> allocate auxiliary channel stby4 type disk;
12> duplicate target database for standby from active database
13> spfile
14> set db_unique_name='PMCPBS20'
15> set control_files='+DATADG1/pmcpbs20/controlfile/control01.ctl'
16> set cluster_database='false'
17> set service_names='PMCPBS20'
18> set fal_client='pmcpbs20'
19> set fal_server='bcrpbs20'
20> set standby_file_management='AUTO'
21> set log_archive_config='dg_config=(PMCPBS20,BCRPBS20)'
22> set log_archive_dest_1='location="+ARCHDG1", valid_for=(ALL_LOGFILES,ALL_ROLES)'
23> nofilenamecheck;
24> }
25>
26>
using target database control file instead of recovery catalog
allocated channel: prmy1
channel prmy1: SID=1354 instance=PMCPBS21 device type=DISK

allocated channel: prmy2
channel prmy2: SID=1353 instance=PMCPBS21 device type=DISK

allocated channel: prmy3
channel prmy3: SID=1341 instance=PMCPBS21 device type=DISK

allocated channel: prmy4
channel prmy4: SID=1349 instance=PMCPBS21 device type=DISK

allocated channel: prmy5
channel prmy5: SID=1325 instance=PMCPBS21 device type=DISK

allocated channel: prmy6
channel prmy6: SID=1357 instance=PMCPBS21 device type=DISK

allocated channel: stby1
channel stby1: SID=2066 device type=DISK

allocated channel: stby2
channel stby2: SID=2065 device type=DISK

allocated channel: stby3
channel stby3: SID=2064 device type=DISK

allocated channel: stby4
channel stby4: SID=2753 device type=DISK

Starting Duplicate Db at 26-AUG-10

contents of Memory Script:
{
backup as copy reuse
file '/home/oracle/PMCPBS20/product/dbms/11G/dbs/orapwPMCPBS21' auxiliary format
'/home/oracle/PMCPBS20/product/dbms/11G/dbs/orapwPMCPBS21' file
'+DATADG1/spfilepmcpbs21.ora' auxiliary format
'+DATADG1/pmcpbs20/spfilepmcpbs2.ora' ;
sql clone "alter system set spfile= ''+DATADG1/pmcpbs20/spfilepmcpbs2.ora''";
}
executing Memory Script

Starting backup at 26-AUG-10
Finished backup at 26-AUG-10

sql statement: alter system set spfile= ''+DATADG1/pmcpbs20/spfilepmcpbs2.ora''

contents of Memory Script:
{
sql clone "alter system set db_unique_name =
''PMCPBS20'' comment=
'''' scope=spfile";
sql clone "alter system set control_files =
''+DATADG1/pmcpbs20/controlfile/control01.ctl'' comment=
'''' scope=spfile";
sql clone "alter system set cluster_database =
false comment=
'''' scope=spfile";
sql clone "alter system set service_names =
''PMCPBS20'' comment=
'''' scope=spfile";
sql clone "alter system set fal_client =
''pmcpbs20'' comment=
'''' scope=spfile";
sql clone "alter system set fal_server =
''bcrpbs20'' comment=
'''' scope=spfile";
sql clone "alter system set standby_file_management =
''AUTO'' comment=
'''' scope=spfile";
sql clone "alter system set log_archive_config =
''dg_config=(PMCPBS20,BCRPBS20)'' comment=
'''' scope=spfile";
sql clone "alter system set log_archive_dest_1 =
''location=^"+ARCHDG1^", valid_for=(ALL_LOGFILES,ALL_ROLES)'' comment=
'''' scope=spfile";
shutdown clone immediate;
startup clone nomount ;
}
executing Memory Script

sql statement: alter system set db_unique_name = ''PMCPBS20'' comment= '''' scope=spfile

sql statement: alter system set control_files = ''+DATADG1/pmcpbs20/controlfile/control01.ctl'' comment= '''' scope=spfile

sql statement: alter system set cluster_database = false comment= '''' scope=spfile

sql statement: alter system set service_names = ''PMCPBS20'' comment= '''' scope=spfile

sql statement: alter system set fal_client = ''pmcpbs20'' comment= '''' scope=spfile

sql statement: alter system set fal_server = ''bcrpbs20'' comment= '''' scope=spfile

sql statement: alter system set standby_file_management = ''AUTO'' comment= '''' scope=spfile

sql statement: alter system set log_archive_config = ''dg_config=(PMCPBS20,BCRPBS20)'' comment= '''' scope=spfile

sql statement: alter system set log_archive_dest_1 = ''location="+ARCHDG1", valid_for=(ALL_LOGFILES,ALL_ROLES)'' comment= '''' scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area 42757922816 bytes

Fixed Size 2181392 bytes
Variable Size 16176203504 bytes
Database Buffers 26575110144 bytes
Redo Buffers 4427776 bytes
allocated channel: stby1
channel stby1: SID=1378 device type=DISK
allocated channel: stby2
channel stby2: SID=2065 device type=DISK
allocated channel: stby3
channel stby3: SID=2064 device type=DISK
allocated channel: stby4
channel stby4: SID=2063 device type=DISK

contents of Memory Script:
{
backup as copy current controlfile for standby auxiliary format '+DATADG1/pmcpbs20/controlfile/control01.ctl';
sql clone 'alter database mount standby database';
}
executing Memory Script

Starting backup at 26-AUG-10
channel prmy1: starting datafile copy
copying standby control file
output file name=/home/oracle/PMCPBS20/product/dbms/11G/dbs/snapcf_PMCPBS21.f tag=TAG20100826T042344 RECID=38 STAMP=728022226
channel prmy1: datafile copy complete, elapsed time: 00:00:15
Finished backup at 26-AUG-10

sql statement: alter database mount standby database
RMAN-05529: WARNING: DB_FILE_NAME_CONVERT resulted in invalid ASM names; names changed to disk group only.

contents of Memory Script:
{
set newname for tempfile 1 to
"+datadg1";
switch clone tempfile all;
set newname for datafile 1 to
"+datadg1";
set newname for datafile 2 to
"+datadg1";
set newname for datafile 3 to
"+datadg1";
set newname for datafile 4 to
"+datadg1";
set newname for datafile 5 to
"+datadg1";
set newname for datafile 6 to
"+datadg1";
set newname for datafile 7 to
"+datadg1";
set newname for datafile 8 to
"+datadg1";
set newname for datafile 9 to
"+datadg1";
set newname for datafile 10 to
"+datadg1";
set newname for datafile 11 to
"+datadg1";
set newname for datafile 12 to
"+datadg1";
set newname for datafile 13 to
"+datadg1";
set newname for datafile 14 to
"+datadg1";
set newname for datafile 15 to
"+datadg1";
set newname for datafile 16 to
"+datadg1";
set newname for datafile 17 to
"+datadg1";
set newname for datafile 18 to
"+datadg1";
set newname for datafile 19 to
"+datadg1";
set newname for datafile 21 to
"+datadg1";
set newname for datafile 23 to
"+datadg1";
set newname for datafile 26 to
"+datadg1";
set newname for datafile 28 to
"+datadg1";
set newname for datafile 29 to
"+datadg1";
set newname for datafile 30 to
"+datadg1";
set newname for datafile 31 to
"+datadg1";
set newname for datafile 32 to
"+datadg1";
set newname for datafile 33 to
"+datadg1";
set newname for datafile 34 to
"+datadg1";
set newname for datafile 42 to
"+datadg1";
set newname for datafile 43 to
"+datadg1";
set newname for datafile 44 to
"+datadg1";
set newname for datafile 45 to
"+datadg1";
set newname for datafile 46 to
"+datadg1";
set newname for datafile 47 to
"+datadg1";
set newname for datafile 48 to
"+datadg1";
set newname for datafile 50 to
"+datadg1";
set newname for datafile 51 to
"+datadg1";
set newname for datafile 52 to
"+datadg1";
set newname for datafile 53 to
"+datadg1";
set newname for datafile 56 to
"+datadg1";
set newname for datafile 57 to
"+datadg1";
set newname for datafile 58 to
"+datadg1";
set newname for datafile 59 to
"+datadg1";
set newname for datafile 60 to
"+datadg1";
set newname for datafile 61 to
"+datadg1";
set newname for datafile 62 to
"+datadg1";
set newname for datafile 63 to
"+datadg1";
set newname for datafile 64 to
"+datadg1";
set newname for datafile 66 to
"+datadg1";
set newname for datafile 67 to
"+datadg1";
set newname for datafile 68 to
"+datadg1";
set newname for datafile 69 to
"+datadg1";
set newname for datafile 70 to
"+datadg1";
set newname for datafile 71 to
"+datadg1";
set newname for datafile 72 to
"+datadg1";
set newname for datafile 73 to
"+datadg1";
set newname for datafile 74 to
"+datadg1";
set newname for datafile 76 to
"+datadg1";
set newname for datafile 77 to
"+datadg1";
set newname for datafile 78 to
"+datadg1";
backup as copy reuse
datafile 1 auxiliary format
"+datadg1" datafile
2 auxiliary format
"+datadg1" datafile
3 auxiliary format
"+datadg1" datafile
4 auxiliary format
"+datadg1" datafile
5 auxiliary format
"+datadg1" datafile
6 auxiliary format
"+datadg1" datafile
7 auxiliary format
"+datadg1" datafile
8 auxiliary format
"+datadg1" datafile
9 auxiliary format
"+datadg1" datafile
10 auxiliary format
"+datadg1" datafile
11 auxiliary format
"+datadg1" datafile
12 auxiliary format
"+datadg1" datafile
13 auxiliary format
"+datadg1" datafile
14 auxiliary format
"+datadg1" datafile
15 auxiliary format
"+datadg1" datafile
16 auxiliary format
"+datadg1" datafile
17 auxiliary format
"+datadg1" datafile
18 auxiliary format
"+datadg1" datafile
19 auxiliary format
"+datadg1" datafile
21 auxiliary format
"+datadg1" datafile
23 auxiliary format
"+datadg1" datafile
26 auxiliary format
"+datadg1" datafile
28 auxiliary format
"+datadg1" datafile
29 auxiliary format
"+datadg1" datafile
30 auxiliary format
"+datadg1" datafile
31 auxiliary format
"+datadg1" datafile
32 auxiliary format
"+datadg1" datafile
33 auxiliary format
"+datadg1" datafile
34 auxiliary format
"+datadg1" datafile
42 auxiliary format
"+datadg1" datafile
43 auxiliary format
"+datadg1" datafile
44 auxiliary format
"+datadg1" datafile
45 auxiliary format
"+datadg1" datafile
46 auxiliary format
"+datadg1" datafile
47 auxiliary format
"+datadg1" datafile
48 auxiliary format
"+datadg1" datafile
50 auxiliary format
"+datadg1" datafile
51 auxiliary format
"+datadg1" datafile
52 auxiliary format
"+datadg1" datafile
53 auxiliary format
"+datadg1" datafile
56 auxiliary format
"+datadg1" datafile
57 auxiliary format
"+datadg1" datafile
58 auxiliary format
"+datadg1" datafile
59 auxiliary format
"+datadg1" datafile
60 auxiliary format
"+datadg1" datafile
61 auxiliary format
"+datadg1" datafile
62 auxiliary format
"+datadg1" datafile
63 auxiliary format
"+datadg1" datafile
64 auxiliary format
"+datadg1" datafile
66 auxiliary format
"+datadg1" datafile
67 auxiliary format
"+datadg1" datafile
68 auxiliary format
"+datadg1" datafile
69 auxiliary format
"+datadg1" datafile
70 auxiliary format
"+datadg1" datafile
71 auxiliary format
"+datadg1" datafile
72 auxiliary format
"+datadg1" datafile
73 auxiliary format
"+datadg1" datafile
74 auxiliary format
"+datadg1" datafile
76 auxiliary format
"+datadg1" datafile
77 auxiliary format
"+datadg1" datafile
78 auxiliary format
"+datadg1" ;
sql 'alter system archive log current';
}
executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to +datadg1 in control file

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting backup at 26-AUG-10
channel prmy1: starting datafile copy
input datafile file number=00013 name=+DATADG1/bcrpbs20/datafile/pbsd_stg_raw_idx.291.701529109
channel prmy2: starting datafile copy
input datafile file number=00014 name=+DATADG1/bcrpbs20/datafile/pbsd_racs_data.292.701529121
channel prmy3: starting datafile copy
input datafile file number=00019 name=+DATADG1/bcrpbs20/datafile/pbsd_price_data.297.701529397
channel prmy4: starting datafile copy
input datafile file number=00023 name=+DATADG1/bcrpbs20/datafile/pbsd_stg_raw_data.301.701529895
channel prmy5: starting datafile copy
input datafile file number=00026 name=+DATADG1/bcrpbs20/datafile/pbsd_stg_raw_data.258.709602863
channel prmy6: starting datafile copy
input datafile file number=00046 name=+DATADG1/bcrpbs20/datafile/pbsd_stg_raw_data.362.711412309
output file name=+DATADG1/pmcpbs20/datafile/pbsd_data.648.728022265 tag=TAG20100826T042420 RECID=0 STAMP=0
channel prmy5: datafile copy complete, elapsed time: 00:43:06
channel prmy5: starting datafile copy
input datafile file number=00047 name=+DATADG1/bcrpbs20/datafile/pbsd_stg_raw_data.363.711679821
output file name=+DATADG1/pmcpbs20/datafile/pbsd_index.645.728022263 tag=TAG20100826T042420 RECID=0 STAMP=0
channel prmy1: datafile copy complete, elapsed time: 00:43:42
channel prmy1: starting datafile copy
input datafile file number=00048 name=+DATADG1/bcrpbs20/datafile/pbsd_stg_raw_data.364.712512321
output file name=+DATADG1/pmcpbs20/datafile/pbsd_price_data.646.728022263 tag=TAG20100826T042420 RECID=0 STAMP=0
channel prmy3: datafile copy complete, elapsed time: 00:43:42
channel prmy3: starting datafile copy
input datafile file number=00051 name=+DATADG1/bcrpbs20/datafile/pbsd_stg_raw_idx.367.712524829
output file name=+DATADG1/pmcpbs20/datafile/pbsd_racs_data.651.728022265 tag=TAG20100826T042420 RECID=0 STAMP=0
channel prmy2: datafile copy complete, elapsed time: 00:43:49
channel prmy2: starting datafile copy
input datafile file number=00052 name=+DATADG1/bcrpbs20/datafile/pbsd_price_data.368.712524839
output file name=+DATADG1/pmcpbs20/datafile/pbsd_data.647.728022265 tag=TAG20100826T042420 RECID=0 STAMP=0
channel prmy4: datafile copy complete, elapsed time: 00:43:57
channel prmy4: starting datafile copy
input datafile file number=00067 name=+DATADG1/bcrpbs20/datafile/pbsd_racs_data.490.719525879
output file name=+DATADG1/pmcpbs20/datafile/pbsd_data.650.728022265 tag=TAG20100826T042420 RECID=0 STAMP=0
channel prmy6: datafile copy complete, elapsed time: 00:44:04
channel prmy6: starting datafile copy
input datafile file number=00069 name=+DATADG1/bcrpbs20/datafile/pbsd_price_data.446.719527593
output file name=+DATADG1/pmcpbs20/datafile/pbsd_data.654.728024885 tag=TAG20100826T042420 RECID=0 STAMP=0
channel prmy1: datafile copy complete, elapsed time: 00:42:38
channel prmy1: starting datafile copy
input datafile file number=00045 name=+DATADG1/bcrpbs20/datafile/pbsd_stg_raw_data.360.711327831
output file name=+DATADG1/pmcpbs20/datafile/pbsd_data.652.728024851 tag=TAG20100826T042420 RECID=0 STAMP=0
channel prmy5: datafile copy complete, elapsed time: 00:43:14
channel prmy5: starting datafile copy
input datafile file number=00042 name=+DATADG1/bcrpbs20/datafile/pbsd_price_data.358.710788823
output file name=+DATADG1/pmcpbs20/datafile/pbsd_index.653.728024885 tag=TAG20100826T042420 RECID=0 STAMP=0
channel prmy3: datafile copy complete, elapsed time: 00:43:03
channel prmy3: starting datafile copy
input datafile file number=00056 name=+DATADG1/bcrpbs20/datafile/pbsd_price_data.427.715262723
output file name=+DATADG1/pmcpbs20/datafile/pbsd_price_data.655.728024893 tag=TAG20100826T042420 RECID=0 STAMP=0
channel prmy2: datafile copy complete, elapsed time: 00:43:12
channel prmy2: starting datafile copy
input datafile file number=00057 name=+DATADG1/bcrpbs20/datafile/pbsd_price_data.428.715262725
output file name=+DATADG1/pmcpbs20/datafile/pbsd_price_data.657.728024907 tag=TAG20100826T042420 RECID=0 STAMP=0
channel prmy6: datafile copy complete, elapsed time: 00:43:42
channel prmy6: starting datafile copy
input datafile file number=00059 name=+DATADG1/bcrpbs20/datafile/pbsd_racs_data.430.715262733
output file name=+DATADG1/pmcpbs20/datafile/pbsd_racs_data.656.728024901 tag=TAG20100826T042420 RECID=0 STAMP=0
channel prmy4: datafile copy complete, elapsed time: 00:43:52
channel prmy4: starting datafile copy
input datafile file number=00060 name=+DATADG1/bcrpbs20/datafile/pbsd_stg_raw_data.431.715262739
output file name=+DATADG1/pmcpbs20/datafile/pbsd_price_data.659.728027443 tag=TAG20100826T042420 RECID=0 STAMP=0
channel prmy5: datafile copy complete, elapsed time: 00:41:45
channel prmy5: starting datafile copy
input datafile file number=00061 name=+DATADG1/bcrpbs20/datafile/pbsd_stg_raw_data.432.715262741
output file name=+DATADG1/pmcpbs20/datafile/pbsd_price_data.660.728027469 tag=TAG20100826T042420 RECID=0 STAMP=0
channel prmy3: datafile copy complete, elapsed time: 00:42:25
channel prmy3: starting datafile copy
input datafile file number=00062 name=+DATADG1/bcrpbs20/datafile/pbsd_stg_raw_data.433.715262749
output file name=+DATADG1/pmcpbs20/datafile/pbsd_price_data.661.728027485 tag=TAG20100826T042420 RECID=0 STAMP=0
channel prmy2: datafile copy complete, elapsed time: 00:42:17
channel prmy2: starting datafile copy
input datafile file number=00063 name=+DATADG1/bcrpbs20/datafile/pbsd_stg_raw_data.434.715262751
output file name=+DATADG1/pmcpbs20/datafile/pbsd_data.658.728027443 tag=TAG20100826T042420 RECID=0 STAMP=0
channel prmy1: datafile copy complete, elapsed time: 00:43:13
channel prmy1: starting datafile copy
input datafile file number=00064 name=+DATADG1/bcrpbs20/datafile/pbsd_stg_raw_idx.435.715262753
output file name=+DATADG1/pmcpbs20/datafile/pbsd_racs_data.662.728027531 tag=TAG20100826T042420 RECID=0 STAMP=0
channel prmy6: datafile copy complete, elapsed time: 00:42:22
channel prmy6: starting datafile copy
input datafile file number=00070 name=+DATADG1/bcrpbs20/datafile/pbsd_stg_raw_idx.479.720229095
output file name=+DATADG1/pmcpbs20/datafile/pbsd_data.663.728027533 tag=TAG20100826T042420 RECID=0 STAMP=0
channel prmy4: datafile copy complete, elapsed time: 00:42:54
channel prmy4: starting datafile copy
input datafile file number=00010 name=+DATADG1/bcrpbs20/datafile/pbsd_index.296.722468841
output file name=+DATADG1/pmcpbs20/datafile/pbsd_data.664.728029949 tag=TAG20100826T042420 RECID=0 STAMP=0
channel prmy5: datafile copy complete, elapsed time: 00:44:44
channel prmy5: starting datafile copy
input datafile file number=00011 name=+DATADG1/bcrpbs20/datafile/pbsd_index.465.722469719
output file name=+DATADG1/pmcpbs20/datafile/pbsd_index.669.728030107 tag=TAG20100826T042420 RECID=0 STAMP=0
channel prmy4: datafile copy complete, elapsed time: 00:42:21
channel prmy4: starting datafile copy
input datafile file number=00043 name=+DATADG1/bcrpbs20/datafile/pbsd_stg_raw_data.361.711103905
output file name=+DATADG1/pmcpbs20/datafile/pbsd_index.668.728030073 tag=TAG20100826T042420 RECID=0 STAMP=0
channel prmy6: datafile copy complete, elapsed time: 00:43:31
channel prmy6: starting datafile copy
input datafile file number=00044 name=+DATADG1/bcrpbs20/datafile/pbsd_stg_raw_data.359.711053251
output file name=+DATADG1/pmcpbs20/datafile/pbsd_data.666.728030021 tag=TAG20100826T042420 RECID=0 STAMP=0
channel prmy2: datafile copy complete, elapsed time: 00:44:38
channel prmy2: starting datafile copy
input datafile file number=00068 name=+DATADG1/bcrpbs20/datafile/pbsd_stg_raw_data.485.719526975
output file name=+DATADG1/pmcpbs20/datafile/pbsd_data.665.728030015 tag=TAG20100826T042420 RECID=0 STAMP=0
channel prmy3: datafile copy complete, elapsed time: 00:44:47
channel prmy3: starting datafile copy
input datafile file number=00066 name=+DATADG1/bcrpbs20/datafile/pbsd_racs_data.314.719275135
output file name=+DATADG1/pmcpbs20/datafile/pbsd_index.667.728030037 tag=TAG20100826T042420 RECID=0 STAMP=0
channel prmy1: datafile copy complete, elapsed time: 00:44:39
channel prmy1: starting datafile copy
input datafile file number=00021 name=+DATADG1/bcrpbs20/datafile/pbsd_price_idx.299.701529451
output file name=+DATADG1/pmcpbs20/datafile/pbsd_price_index.675.728032717 tag=TAG20100826T042420 RECID=0 STAMP=0
channel prmy1: datafile copy complete, elapsed time: 00:39:34
channel prmy1: starting datafile copy
input datafile file number=00008 name=+DATADG1/bcrpbs20/datafile/pbsd_index.426.722364855
output file name=+DATADG1/pmcpbs20/datafile/pbsd_racs_data.674.728032701 tag=TAG20100826T042420 RECID=0 STAMP=0
channel prmy3: datafile copy complete, elapsed time: 00:40:56
channel prmy3: starting datafile copy
input datafile file number=00058 name=+DATADG1/bcrpbs20/datafile/pbsd_price_idx.429.715262731
output file name=+DATADG1/pmcpbs20/datafile/pbsd_data.671.728032649 tag=TAG20100826T042420 RECID=0 STAMP=0
channel prmy4: datafile copy complete, elapsed time: 00:44:04
channel prmy4: starting datafile copy
input datafile file number=00004 name=+DATADG1/bcrpbs20/datafile/users.275.699561249
output file name=+DATADG1/pmcpbs20/datafile/pbsd_data.672.728032685 tag=TAG20100826T042420 RECID=0 STAMP=0
channel prmy6: datafile copy complete, elapsed time: 00:43:44
channel prmy6: starting datafile copy
input datafile file number=00006 name=+DATADG1/bcrpbs20/datafile/undotbs3.277.699561235
output file name=+DATADG1/pmcpbs20/datafile/pbsd_index.670.728032633 tag=TAG20100826T042420 RECID=0 STAMP=0
channel prmy5: datafile copy complete, elapsed time: 00:45:20
channel prmy5: starting datafile copy
input datafile file number=00072 name=+DATADG1/bcrpbs20/datafile/pbsd_stg_raw_data.474.721768159
output file name=+DATADG1/pmcpbs20/datafile/pbsd_data.673.728032699 tag=TAG20100826T042420 RECID=0 STAMP=0
channel prmy2: datafile copy complete, elapsed time: 00:44:39
channel prmy2: starting datafile copy
input datafile file number=00076 name=+DATADG1/bcrpbs20/datafile/pbsd_stg_raw_data.450.721859367
output file name=+DATADG1/pmcpbs20/datafile/pbsd_data.680.728035353 tag=TAG20100826T042420 RECID=0 STAMP=0
channel prmy5: datafile copy complete, elapsed time: 00:23:06
channel prmy5: starting datafile copy
input datafile file number=00050 name=+DATADG1/bcrpbs20/datafile/pbsd_price_idx.366.712524817
output file name=+DATADG1/pmcpbs20/datafile/pbsd_data.681.728035379 tag=TAG20100826T042420 RECID=0 STAMP=0
channel prmy2: datafile copy complete, elapsed time: 00:22:48
channel prmy2: starting datafile copy
input datafile file number=00015 name=+DATADG1/bcrpbs20/datafile/pbsd_data.732.727976203
output file name=+DATADG1/pmcpbs20/datafile/undotbs3.679.728035311 tag=TAG20100826T042420 RECID=0 STAMP=0
channel prmy6: datafile copy complete, elapsed time: 00:25:05
channel prmy6: starting datafile copy
input datafile file number=00016 name=+DATADG1/bcrpbs20/datafile/pbsd_data.547.727976341
output file name=+DATADG1/pmcpbs20/datafile/users.678.728035293 tag=TAG20100826T042420 RECID=0 STAMP=0
channel prmy4: datafile copy complete, elapsed time: 00:31:47
channel prmy4: starting datafile copy
input datafile file number=00071 name=+DATADG1/bcrpbs20/datafile/pbsd_price_data.311.721407945
output file name=+DATADG1/pmcpbs20/datafile/pbsd_price_index.677.728035157 tag=TAG20100826T042420 RECID=0 STAMP=0
channel prmy3: datafile copy complete, elapsed time: 00:34:10
channel prmy3: starting datafile copy
input datafile file number=00005 name=+DATADG1/bcrpbs20/datafile/undotbs2.278.699561227
output file name=+DATADG1/pmcpbs20/datafile/pbsd_index.676.728035091 tag=TAG20100826T042420 RECID=0 STAMP=0
channel prmy1: datafile copy complete, elapsed time: 00:36:42
channel prmy1: starting datafile copy
input datafile file number=00003 name=+DATADG1/bcrpbs20/datafile/undotbs1.276.699561241
output file name=+DATADG1/pmcpbs20/datafile/pbsd_data.683.728036747 tag=TAG20100826T042420 RECID=0 STAMP=0
channel prmy2: datafile copy complete, elapsed time: 00:20:14
channel prmy2: starting datafile copy
input datafile file number=00017 name=+DATADG1/bcrpbs20/datafile/pbsd_price_data.859.727981313
output file name=+DATADG1/pmcpbs20/datafile/pbsd_data.684.728036813 tag=TAG20100826T042420 RECID=0 STAMP=0
channel prmy6: datafile copy complete, elapsed time: 00:19:53
channel prmy6: starting datafile copy
input datafile file number=00012 name=+DATADG1/bcrpbs20/datafile/pbsd_racs_data.836.726285865
output file name=+DATADG1/pmcpbs20/datafile/pbsd_price_index.682.728036739 tag=TAG20100826T042420 RECID=0 STAMP=0
channel prmy5: datafile copy complete, elapsed time: 00:21:32
channel prmy5: starting datafile copy
input datafile file number=00001 name=+DATADG1/bcrpbs20/datafile/system.279.699561171
output file name=+DATADG1/pmcpbs20/datafile/undotbs1.687.728037293 tag=TAG20100826T042420 RECID=0 STAMP=0
channel prmy1: datafile copy complete, elapsed time: 00:14:55
channel prmy1: starting datafile copy
input datafile file number=00002 name=+DATADG1/bcrpbs20/datafile/sysaux.280.699561057
output file name=+DATADG1/pmcpbs20/datafile/undotbs2.686.728037207 tag=TAG20100826T042420 RECID=0 STAMP=0
channel prmy3: datafile copy complete, elapsed time: 00:16:46
channel prmy3: starting datafile copy
input datafile file number=00009 name=+DATADG1/bcrpbs20/datafile/pbsd_core_data.287.701528999
output file name=+DATADG1/pmcpbs20/datafile/pbsd_price_data.685.728037199 tag=TAG20100826T042420 RECID=0 STAMP=0
channel prmy4: datafile copy complete, elapsed time: 00:18:49
channel prmy4: starting datafile copy
input datafile file number=00073 name=+DATADG1/bcrpbs20/datafile/pbsd_racs_data.469.721768271
output file name=+DATADG1/pmcpbs20/datafile/pbsd_core_data.691.728038213 tag=TAG20100826T042420 RECID=0 STAMP=0
channel prmy3: datafile copy complete, elapsed time: 00:03:51
channel prmy3: starting datafile copy
input datafile file number=00077 name=+DATADG1/bcrpbs20/datafile/pbsd_racs_data.443.721859377
output file name=+DATADG1/pmcpbs20/datafile/system.690.728038033 tag=TAG20100826T042420 RECID=0 STAMP=0
channel prmy5: datafile copy complete, elapsed time: 00:07:08
channel prmy5: starting datafile copy
input datafile file number=00074 name=+DATADG1/bcrpbs20/datafile/pbsd_price_idx.464.721768335
output file name=+DATADG1/pmcpbs20/datafile/pbsd_racs_data.508.728038329 tag=TAG20100826T042420 RECID=0 STAMP=0
channel prmy4: datafile copy complete, elapsed time: 00:03:06
channel prmy4: starting datafile copy
input datafile file number=00078 name=+DATADG1/bcrpbs20/datafile/pbsd_price_idx.470.721859383
output file name=+DATADG1/pmcpbs20/datafile/pbsd_price_index.496.728038459 tag=TAG20100826T042420 RECID=0 STAMP=0
channel prmy5: datafile copy complete, elapsed time: 00:01:31
channel prmy5: starting datafile copy
input datafile file number=00018 name=+DATADG1/bcrpbs20/datafile/pbsd_data.739.728014585
output file name=+DATADG1/pmcpbs20/datafile/pbsd_data.693.728038551 tag=TAG20100826T042420 RECID=0 STAMP=0
channel prmy5: datafile copy complete, elapsed time: 00:00:45
channel prmy5: starting datafile copy
input datafile file number=00028 name=+DATADG1/bcrpbs20/datafile/pbsd_wli.261.710086635
output file name=+DATADG1/pmcpbs20/datafile/pbsd_price_index.649.728038515 tag=TAG20100826T042420 RECID=0 STAMP=0
channel prmy4: datafile copy complete, elapsed time: 00:01:36
channel prmy4: starting datafile copy
input datafile file number=00033 name=+DATADG1/bcrpbs20/datafile/pbsd_geneva.349.710086799
output file name=+DATADG1/pmcpbs20/datafile/pbsd_wli.491.728038597 tag=TAG20100826T042420 RECID=0 STAMP=0
channel prmy5: datafile copy complete, elapsed time: 00:00:16
channel prmy5: starting datafile copy
input datafile file number=00053 name=+DATADG1/bcrpbs20/datafile/users.424.714673535
output file name=+DATADG1/pmcpbs20/datafile/sysaux.507.728038191 tag=TAG20100826T042420 RECID=0 STAMP=0
channel prmy1: datafile copy complete, elapsed time: 00:07:11
channel prmy1: starting datafile copy
input datafile file number=00007 name=+DATADG1/bcrpbs20/datafile/dbus.352.724592017
output file name=+DATADG1/pmcpbs20/datafile/dbus.695.728038619 tag=TAG20100826T042420 RECID=0 STAMP=0
channel prmy1: datafile copy complete, elapsed time: 00:00:02
channel prmy1: starting datafile copy
input datafile file number=00029 name=+DATADG1/bcrpbs20/datafile/pbsd_xml.262.710086651
output file name=+DATADG1/pmcpbs20/datafile/pbsd_geneva.500.728038611 tag=TAG20100826T042420 RECID=0 STAMP=0
channel prmy4: datafile copy complete, elapsed time: 00:00:10
channel prmy4: starting datafile copy
input datafile file number=00030 name=+DATADG1/bcrpbs20/datafile/pbsd_blob.259.710086705
output file name=+DATADG1/pmcpbs20/datafile/pbsd_xml.696.728038621 tag=TAG20100826T042420 RECID=0 STAMP=0
channel prmy1: datafile copy complete, elapsed time: 00:00:01
channel prmy1: starting datafile copy
input datafile file number=00031 name=+DATADG1/bcrpbs20/datafile/pbsd_olap.260.710086743
output file name=+DATADG1/pmcpbs20/datafile/users.694.728038611 tag=TAG20100826T042420 RECID=0 STAMP=0
channel prmy5: datafile copy complete, elapsed time: 00:00:10
channel prmy5: starting datafile copy
input datafile file number=00032 name=+DATADG1/bcrpbs20/datafile/pbsd_racs.348.710086771
output file name=+DATADG1/pmcpbs20/datafile/pbsd_olap.698.728038623 tag=TAG20100826T042420 RECID=0 STAMP=0
channel prmy1: datafile copy complete, elapsed time: 00:00:02
channel prmy1: starting datafile copy
input datafile file number=00034 name=+DATADG1/bcrpbs20/datafile/pbsd_archive.350.710087395
output file name=+DATADG1/pmcpbs20/datafile/pbsd_blob.697.728038621 tag=TAG20100826T042420 RECID=0 STAMP=0
channel prmy4: datafile copy complete, elapsed time: 00:00:02
output file name=+DATADG1/pmcpbs20/datafile/pbsd_racs_data.692.728038445 tag=TAG20100826T042420 RECID=0 STAMP=0
channel prmy3: datafile copy complete, elapsed time: 00:02:59
output file name=+DATADG1/pmcpbs20/datafile/pbsd_racs.699.728038623 tag=TAG20100826T042420 RECID=0 STAMP=0
channel prmy5: datafile copy complete, elapsed time: 00:00:01
output file name=+DATADG1/pmcpbs20/datafile/pbsd_archive.701.728038623 tag=TAG20100826T042420 RECID=0 STAMP=0
channel prmy1: datafile copy complete, elapsed time: 00:00:01
output file name=+DATADG1/pmcpbs20/datafile/pbsd_racs_data.689.728038007 tag=TAG20100826T042420 RECID=0 STAMP=0
channel prmy6: datafile copy complete, elapsed time: 00:12:03
output file name=+DATADG1/pmcpbs20/datafile/pbsd_price_data.688.728037961 tag=TAG20100826T042420 RECID=0 STAMP=0
channel prmy2: datafile copy complete, elapsed time: 00:13:59
Finished backup at 26-AUG-10

sql statement: alter system archive log current

contents of Memory Script:
{
switch clone datafile all;
}
executing Memory Script

datafile 1 switched to datafile copy
input datafile copy RECID=38 STAMP=728038813 file name=+DATADG1/pmcpbs20/datafile/system.690.728038033
datafile 2 switched to datafile copy
input datafile copy RECID=39 STAMP=728038813 file name=+DATADG1/pmcpbs20/datafile/sysaux.507.728038191
datafile 3 switched to datafile copy
input datafile copy RECID=40 STAMP=728038813 file name=+DATADG1/pmcpbs20/datafile/undotbs1.687.728037293
datafile 4 switched to datafile copy
input datafile copy RECID=41 STAMP=728038813 file name=+DATADG1/pmcpbs20/datafile/users.678.728035293
datafile 5 switched to datafile copy
input datafile copy RECID=42 STAMP=728038814 file name=+DATADG1/pmcpbs20/datafile/undotbs2.686.728037207
datafile 6 switched to datafile copy
input datafile copy RECID=43 STAMP=728038814 file name=+DATADG1/pmcpbs20/datafile/undotbs3.679.728035311
datafile 7 switched to datafile copy
input datafile copy RECID=44 STAMP=728038814 file name=+DATADG1/pmcpbs20/datafile/dbus.695.728038619
datafile 8 switched to datafile copy
input datafile copy RECID=45 STAMP=728038814 file name=+DATADG1/pmcpbs20/datafile/pbsd_index.676.728035091
datafile 9 switched to datafile copy
input datafile copy RECID=46 STAMP=728038814 file name=+DATADG1/pmcpbs20/datafile/pbsd_core_data.691.728038213
datafile 10 switched to datafile copy
input datafile copy RECID=47 STAMP=728038814 file name=+DATADG1/pmcpbs20/datafile/pbsd_index.669.728030107
datafile 11 switched to datafile copy
input datafile copy RECID=48 STAMP=728038815 file name=+DATADG1/pmcpbs20/datafile/pbsd_index.670.728032633
datafile 12 switched to datafile copy
input datafile copy RECID=49 STAMP=728038815 file name=+DATADG1/pmcpbs20/datafile/pbsd_racs_data.689.728038007
datafile 13 switched to datafile copy
input datafile copy RECID=50 STAMP=728038815 file name=+DATADG1/pmcpbs20/datafile/pbsd_index.645.728022263
datafile 14 switched to datafile copy
input datafile copy RECID=51 STAMP=728038815 file name=+DATADG1/pmcpbs20/datafile/pbsd_racs_data.651.728022265
datafile 15 switched to datafile copy
input datafile copy RECID=52 STAMP=728038815 file name=+DATADG1/pmcpbs20/datafile/pbsd_data.683.728036747
datafile 16 switched to datafile copy
input datafile copy RECID=53 STAMP=728038815 file name=+DATADG1/pmcpbs20/datafile/pbsd_data.684.728036813
datafile 17 switched to datafile copy
input datafile copy RECID=54 STAMP=728038816 file name=+DATADG1/pmcpbs20/datafile/pbsd_price_data.688.728037961
datafile 18 switched to datafile copy
input datafile copy RECID=55 STAMP=728038816 file name=+DATADG1/pmcpbs20/datafile/pbsd_data.693.728038551
datafile 19 switched to datafile copy
input datafile copy RECID=56 STAMP=728038816 file name=+DATADG1/pmcpbs20/datafile/pbsd_price_data.646.728022263
datafile 21 switched to datafile copy
input datafile copy RECID=57 STAMP=728038816 file name=+DATADG1/pmcpbs20/datafile/pbsd_price_index.675.728032717
datafile 23 switched to datafile copy
input datafile copy RECID=58 STAMP=728038816 file name=+DATADG1/pmcpbs20/datafile/pbsd_data.647.728022265
datafile 26 switched to datafile copy
input datafile copy RECID=59 STAMP=728038816 file name=+DATADG1/pmcpbs20/datafile/pbsd_data.648.728022265
datafile 28 switched to datafile copy
input datafile copy RECID=60 STAMP=728038817 file name=+DATADG1/pmcpbs20/datafile/pbsd_wli.491.728038597
datafile 29 switched to datafile copy
input datafile copy RECID=61 STAMP=728038817 file name=+DATADG1/pmcpbs20/datafile/pbsd_xml.696.728038621
datafile 30 switched to datafile copy
input datafile copy RECID=62 STAMP=728038817 file name=+DATADG1/pmcpbs20/datafile/pbsd_blob.697.728038621
datafile 31 switched to datafile copy
input datafile copy RECID=63 STAMP=728038817 file name=+DATADG1/pmcpbs20/datafile/pbsd_olap.698.728038623
datafile 32 switched to datafile copy
input datafile copy RECID=64 STAMP=728038817 file name=+DATADG1/pmcpbs20/datafile/pbsd_racs.699.728038623
datafile 33 switched to datafile copy
input datafile copy RECID=65 STAMP=728038817 file name=+DATADG1/pmcpbs20/datafile/pbsd_geneva.500.728038611
datafile 34 switched to datafile copy
input datafile copy RECID=66 STAMP=728038818 file name=+DATADG1/pmcpbs20/datafile/pbsd_archive.701.728038623
datafile 42 switched to datafile copy
input datafile copy RECID=67 STAMP=728038818 file name=+DATADG1/pmcpbs20/datafile/pbsd_price_data.659.728027443
datafile 43 switched to datafile copy
input datafile copy RECID=68 STAMP=728038818 file name=+DATADG1/pmcpbs20/datafile/pbsd_data.671.728032649
datafile 44 switched to datafile copy
input datafile copy RECID=69 STAMP=728038818 file name=+DATADG1/pmcpbs20/datafile/pbsd_data.672.728032685
datafile 45 switched to datafile copy
input datafile copy RECID=70 STAMP=728038818 file name=+DATADG1/pmcpbs20/datafile/pbsd_data.658.728027443
datafile 46 switched to datafile copy
input datafile copy RECID=71 STAMP=728038818 file name=+DATADG1/pmcpbs20/datafile/pbsd_data.650.728022265
datafile 47 switched to datafile copy
input datafile copy RECID=72 STAMP=728038819 file name=+DATADG1/pmcpbs20/datafile/pbsd_data.652.728024851
datafile 48 switched to datafile copy
input datafile copy RECID=73 STAMP=728038819 file name=+DATADG1/pmcpbs20/datafile/pbsd_data.654.728024885
datafile 50 switched to datafile copy
input datafile copy RECID=74 STAMP=728038819 file name=+DATADG1/pmcpbs20/datafile/pbsd_price_index.682.728036739
datafile 51 switched to datafile copy
input datafile copy RECID=75 STAMP=728038819 file name=+DATADG1/pmcpbs20/datafile/pbsd_index.653.728024885
datafile 52 switched to datafile copy
input datafile copy RECID=76 STAMP=728038819 file name=+DATADG1/pmcpbs20/datafile/pbsd_price_data.655.728024893
datafile 53 switched to datafile copy
input datafile copy RECID=77 STAMP=728038819 file name=+DATADG1/pmcpbs20/datafile/users.694.728038611
datafile 56 switched to datafile copy
input datafile copy RECID=78 STAMP=728038820 file name=+DATADG1/pmcpbs20/datafile/pbsd_price_data.660.728027469
datafile 57 switched to datafile copy
input datafile copy RECID=79 STAMP=728038820 file name=+DATADG1/pmcpbs20/datafile/pbsd_price_data.661.728027485
datafile 58 switched to datafile copy
input datafile copy RECID=80 STAMP=728038820 file name=+DATADG1/pmcpbs20/datafile/pbsd_price_index.677.728035157
datafile 59 switched to datafile copy
input datafile copy RECID=81 STAMP=728038820 file name=+DATADG1/pmcpbs20/datafile/pbsd_racs_data.662.728027531
datafile 60 switched to datafile copy
input datafile copy RECID=82 STAMP=728038820 file name=+DATADG1/pmcpbs20/datafile/pbsd_data.663.728027533
datafile 61 switched to datafile copy
input datafile copy RECID=83 STAMP=728038820 file name=+DATADG1/pmcpbs20/datafile/pbsd_data.664.728029949
datafile 62 switched to datafile copy
input datafile copy RECID=84 STAMP=728038821 file name=+DATADG1/pmcpbs20/datafile/pbsd_data.665.728030015
datafile 63 switched to datafile copy
input datafile copy RECID=85 STAMP=728038821 file name=+DATADG1/pmcpbs20/datafile/pbsd_data.666.728030021
datafile 64 switched to datafile copy
input datafile copy RECID=86 STAMP=728038821 file name=+DATADG1/pmcpbs20/datafile/pbsd_index.667.728030037
datafile 66 switched to datafile copy
input datafile copy RECID=87 STAMP=728038821 file name=+DATADG1/pmcpbs20/datafile/pbsd_racs_data.674.728032701
datafile 67 switched to datafile copy
input datafile copy RECID=88 STAMP=728038821 file name=+DATADG1/pmcpbs20/datafile/pbsd_racs_data.656.728024901
datafile 68 switched to datafile copy
input datafile copy RECID=89 STAMP=728038821 file name=+DATADG1/pmcpbs20/datafile/pbsd_data.673.728032699
datafile 69 switched to datafile copy
input datafile copy RECID=90 STAMP=728038822 file name=+DATADG1/pmcpbs20/datafile/pbsd_price_data.657.728024907
datafile 70 switched to datafile copy
input datafile copy RECID=91 STAMP=728038822 file name=+DATADG1/pmcpbs20/datafile/pbsd_index.668.728030073
datafile 71 switched to datafile copy
input datafile copy RECID=92 STAMP=728038822 file name=+DATADG1/pmcpbs20/datafile/pbsd_price_data.685.728037199
datafile 72 switched to datafile copy
input datafile copy RECID=93 STAMP=728038822 file name=+DATADG1/pmcpbs20/datafile/pbsd_data.680.728035353
datafile 73 switched to datafile copy
input datafile copy RECID=94 STAMP=728038822 file name=+DATADG1/pmcpbs20/datafile/pbsd_racs_data.508.728038329
datafile 74 switched to datafile copy
input datafile copy RECID=95 STAMP=728038822 file name=+DATADG1/pmcpbs20/datafile/pbsd_price_index.496.728038459
datafile 76 switched to datafile copy
input datafile copy RECID=96 STAMP=728038823 file name=+DATADG1/pmcpbs20/datafile/pbsd_data.681.728035379
datafile 77 switched to datafile copy
input datafile copy RECID=97 STAMP=728038823 file name=+DATADG1/pmcpbs20/datafile/pbsd_racs_data.692.728038445
datafile 78 switched to datafile copy
input datafile copy RECID=98 STAMP=728038823 file name=+DATADG1/pmcpbs20/datafile/pbsd_price_index.649.728038515
Finished Duplicate Db at 26-AUG-10
released channel: prmy1
released channel: prmy2
released channel: prmy3
released channel: prmy4
released channel: prmy5
released channel: prmy6
released channel: stby1
released channel: stby2
released channel: stby3
released channel: stby4

Recovery Manager complete.

HAPPY LEARNING!

SOLARIS USEFUL COMMANDS FOR DBA'S

2010-10-01T03:24:00.000-07:00

Not just for DBAs: this is to remind anyone of syntax and options.

1. Handy Solaris commands HW config, swap, patches etc.
2. Real-time performance monitoring vmstat, iostat, mpstat etc.
3. Unix Commands find, awk, sed, grep etc.
4. vi editor commands the 22 most useful vi commands
5. Unix file permissions explained sounds basic, but you may learn something new here!
6. The Solaris Name Service explained how hostname and username lookups work

1. Handy Solaris commands

show hardware config (memory, CPUs etc) :
/usr/platform/sun4u/sbin/prtdiag -v |more
/usr/sbin/psrinfo
show kernel settings:
/usr/sbin/sysdef |more
more /etc/system

show list of Solaris patches:
showrev -p |more

show software installed:
pkginfo |more

check swap space:
/usr/sbin/swap -s

check system load average: an uptime more than double the number of CPUs is getting a bit busy
uptime

top CPU users:
/usr/ucb/ps uax |head

top memory users:
/usr/ucb/ps vax |head

check disk space:
df -k (shows all mounted filesystems -including NFS etc)
df -lk (shows only local filesystems)
df -k -Fufs (shows only local Sun UFS filesystems (normal hard disks)
df -k . (show just the filesystem you are currently in)

2. Real-time performance monitoring:

memory usage :
vmstat 5 -look at memory/free field and page/sr field. Ignore the first line output as it’s historical
procs memory page disk faults cpu
r b w swap free re mf pi po fr de sr s0 s1 s6 s3 in sy cs us sy id
0 0 83 4456 456 1 431 266 70 167 0 35 6 6 0 2 523 567 31 14 9 76
0 0 62 3588464 46824 0 196 64 0 0 0 0 5 4 0 0 606 9743 882 86 7 7
0 0 62 3587960 42672 1 552 41 1 1 0 0 2 2 0 0 789 5488 1040 84 7 9
0 1 62 3584704 38848 0 471 3 38 38 0 0 5 5 0 1 1426 5270 968 64 9 27
0 0 62 3586464 38456 0 451 0 0 0 0 0 2 2 0 0 929 6039 1265 70 6 24
Also make sure that cpu/us is at least double cpu/sy

disk busy-ness
mpstat 5 (or iostat -c 5 )
CPU minf mjf xcal intr ithr csw icsw migr smtx srw syscl usr sys wt idl
0 221 3 544 227 75 582 61 31 28 7 267 18 12 2 68
2 209 2 446 395 178 328 37 31 32 6 299 11 7 2 80
-look at the wt field, this is wait-for-I/O which can be network or disk I/O, and should not be more than 30-40 (percent)

To see individual disk performance and find slow ('hot') disks :
iostat -d 5
sd0 sd1 sd6 sd37
kps tps serv kps tps serv kps tps serv kps tps serv
123 6 44 123 6 42 0 0 42 66 2 8
33 1 3 37 1 1 0 0 0 3 0 5
-check the serv column for each disk: this is the disk service time in milliseconds.
However iostat by default shows only the first four disks: if you have more use -x and/or -l options :
iostat -xdnl 7 5 (e.g. if you have seven hard disks)
extended device statistics
r/s w/s kr/s kw/s wait actv wsvc_t asvc_t %w %b device
0.4 1.6 3.2 70.4 0.0 0.0 0.0 2.7 0 1 c0t0d0
0.2 1.6 1.6 70.4 0.0 0.0 0.0 3.0 0 1 c0t1d0
0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0 0 c0t6d0
0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0 0 c1t8d0
88.8 19.8 1276.8 158.4 0.0 0.6 0.0 5.7 0 51 c1t9d0
0.0 0.4 0.0 100.8 0.0 0.0 0.0 17.4 0 1 c1t10d0
0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0 0 c1t11d0

3. Unix Commands

at - run a command or script once in the future
bash$ at 0815 tomorrow
at> rm $HOME/zxc
at> (type Control-D)
commands will be executed using /usr/bin/bash
job 984032100.a at Thu Mar 8 06:15:00 2001

awk

cron
crontab -l to see your crontab
crontab -e to edit it (NB but first do EDITOR=vi (or EDITOR=emacs) ; export EDITOR )
Remember fields are : minute hour day month day_of_week (0=Sun)
0-59 0-23 1-31 1-12 0-6
e.g. run a script only on Tuesdays to Fridays, at 10:12 am, 3:12pm, 4:12pm and 5:12pm
12 10,15-17 * * 2-5/home/oracle/bin/script

du -show disk space usage
du -k -show disk usage in kB in all subdirectories below the current one
du -sk -show just the total of everything below the current dir
du -sk * -show individual totals for each file or directory in the current dir

find
find things you can’t remember the exact name of :
find /usr/lib -name "*socket*"
find big files (more than 20000 blocks, ie about 10MB )
find . -size +20000
find files or directories modified in last day
find . -mtime -1
find files not accessed (read) in 45 days
find /tmp -atime +45
Add -ls to the end of any of the above commands to see a full ls -l listing of the files found.

You can combine multiple options , e.g look for which big files have filled up a filesystem recently
find . -size +20000 -mtime -1 -ls
Or combine options using an "OR" syntax, e.g. find files which were modified either less than 1 week ago or more than 2 weeks ago
find . $ -mtime +14 -o -mtime -7 $ -ls
You can send the ouput of find to another command : the xargs command is ideal for this: e.g. interactively delete all core files under your $HOME
find ~ -type f -name core |xargs rm -i
or look for a particular word in all your .c files:
find . -name "*.c" |xargs grep -l libsocket

grep and its more flexible and faster cousins egrep and fgrep
Search for either of two strings in all log files:
egrep "error|PROBLEM" *log
case-insensitive search
fgrep -i error *log (will find Error, ERROR, etc.)
just see the filenames where the search text was found
grep -l error *log

sort
-n sorts by number instead of alphabetically
e.g. cd /export/home; du -sk * | sort -nr lists who is using most space in their home directory

tar
e.g. to copy a directory tree, preserving symlinks:
cd fromdir; tar cf - . | (cd todir; tar xpf -)

tr translate characters
convert lower-case to upper:
cat myfile | tr [a-z] [A-Z]
change colons to newlines:
tr : "\n" < myfile > newfile

who show who is currently logged on and where from

w show what people are doing

other useful Unix commands for you to check out:

cut, paste , split

diff, sdiff, cmp, dircmp

head, tail, tail -f

id, groups

4. vi commands
The basic vi commands:
i insert before current character a append after current char
I insert at beginning of line A append after end of line
x delete current character X backspace-delete (deletes char to left)
dd delete current line
22dd delete 22 lines u undo the last thing you did
p paste those 22 lines below the current line
12yy copy 12 lines into the ‘clipboard’ for later pasting
to get out of editing (insert/append) mode
:wq to save ('write') and quit (:q! to quit without writing)

vi moving-around Timesavers:

using h,j,k,l to move left,down,up and right is quicker than using the arrow keys (once you get used to it!). w move one word forward b move back a word e go to end of word
Ctl-F move Forward a whole page Ctl-B move Back a page
0 (zero) go to beginning of line $ go to end of line
:242 go to line 242 Ctl-G see what line you are on

vi modifying things:
cw change word 3cw change 3 words C change to end-of-line
dw delete word D delete to end-of-line

cc replace current line with what you type next
r replace one character R endless replace (like over-typing)
o open/add a new line below the current one O open new line above current one
xp swap two characters (e.g. when you make a typo)
/bob search forward for 'bob' n repeat previous search ? search backwards

Probably the handiest vi command:
. (dot) -repeat your last command

useful extra vi commands:
~ swap case of current character (capitalize or lower-case)
Ctl-L re-draw the screen (e.g when something from a background process writes to your screen and messes up your vi window)
:set nu show line numbers (:set nonu to remove line numbers)
:set list show hidden characters, line endings etc. (: set nolist)

Global replace:
:%s/old/new/g (% means all lines)
or :g/old/s//new/g
or :%s/old/new/gc (c is to confirm each replacement, type ‘y’ to accept)

5. Unix file permissions
You probably already know the basics:
Each user in Unix belongs to at least one group, each file or directory on the system belongs to one user and one group. When you do an ls -l on a file you see what permissions the file owner, group owner and everyone else (‘world’ or ‘others’) have on it
-rwxr-xr-- 1 robins devteam 180 Mar 8 13:50 instbb
so on the file instbb there is full access (rwx- read,write,execute) for the owner (robins), read and execute for the group devteam and read-only for everyone else.

What you may not know is that whether someone can delete that file is not determined by the file permissions, but by the permissions of the directory the file is in :
bash$ ls -al
total 598
drwxrwxr-x 3 robins devteam 512 Mar 8 12:02 .
drwxr-xr-x 24 root wheel 1024 Mar 8 12:02 ..
-rwxr-xr-- 1 robins devteam 180 Mar 8 13:50 instbb
In this case the directory (.) is group writeable, which means anyone in the group devteam can delete the file instbb. Although they can’t modify it, they could copy it to a new file in that directory, modify it, then delete the original and rename the new file as instbb. So the file isn’t as secure as it may appear..

6. Solaris Name services
Solaris provides a mechanism for getting hostnames and usernames etc from several sources (e.g DNS, NIS, or the traditional /etc/hosts file) : the file /etc/nsswitch.conf , which may contain
hosts: files dns
this means that when you try to access a remote host by name (e.g. ping neptune) it will look for neptune first in /etc/hosts, then do an lookup in DNS (nslookup using the server specified in /etc/resolv.conf)

Similarly for usernames, you may have
passwd: files nis
So when you run a command which refers to a username (e.g. cd ~oracle ) , it first looks in /etc/passwd then does a lookup in NIS (ypmatch oracle passwd).

However you can look up hosts and users without worrying about where they are stored, using the getent command :
bash$ getent passwd oracle
oracle:##oracle:3008:5001:Oracle DBA:/export/home/oracle:/usr/local/bin/bash
bash$ getent hosts www.inkq.com
192.168.245.12 www.inkq.com
bash$ getent hosts 10.0.0.91
10.0.0.91 plop.inkq.com plop mailhost

HAPPY LEARNING!

SRVCTL FAILS TO START RAC DATABASE

2010-10-01T03:20:00.000-07:00

SRVCTL has problems while starting the database (SRVCTL STOP DATABASE works ok).
Also, the database can be started and stopped fine using SQL*Plus. No problems there.

$ srvctl start database -d HLPR
PRKP-1001 : Error starting instance HLPR1 on node db01-lisqa
CRS-0215: Could not start resource 'ora.HLPR.HLPR1.inst'.
PRKP-1001 : Error starting instance HLPR2 on node db02-lisqa
CRS-0215: Could not start resource 'ora.HLPR.HLPR2.inst'.
PRKP-1001 : Error starting instance HLPR3 on node db03-lisqa
CRS-0215: Could not start resource 'ora.HLPR.HLPR3.inst'.
PRKP-1001 : Error starting instance HLPR4 on node db04-lisqa
CRS-0215: Could not start resource 'ora.HLPR.HLPR4.inst'.
$
As seen above, it is a 4-node RAC database.

2 Issue is here
1) REQUIRED_RESOURCE missing for one of the nodes
===========================================
Doing a CRS_STAT on the above resources, shows that the REQUIRED_RESOURCES for node 4 instance (HLPR4) is missing. For the other 3 nodes the ASM dependency is defined. It is already verified that ASM is up and running on the 4th node (also there are other RAC databases on the same cluster that do not have any issues).
$ crs_stat -p ora.HLPR.HLPR1.inst|grep REQUIRED
REQUIRED_RESOURCES=ora.db01-lisqa.ASM1.asm
$ crs_stat -p ora.HLPR.HLPR2.inst|grep REQUIRED
REQUIRED_RESOURCES=ora.db02-lisqa.ASM2.asm
$ crs_stat -p ora.HLPR.HLPR3.inst|grep REQUIRED
REQUIRED_RESOURCES=ora.db03-lisqa.ASM3.asm
$ crs_stat -p ora.HLPR.HLPR4.inst|grep REQUIRED
REQUIRED_RESOURCES=
$

Fix
=====
Add the REQUIRED_RESOURCE using "srvctl modify". First lets look at the syntax (-h for help) -
$ srvctl modify instance -h
Usage: srvctl modify database -d [-n ] [-m ] [-p ] [-r {PRIMARY | PHYSICAL_STANDBY | LOGICAL_STANDBY}] [-s ] [-y {AUTOMATIC | MANUAL}]
-d Unique name for the database
-n Database name (DB_NAME), if different from the unique name given by the -d option
-o ORACLE_HOME path
-m Domain for cluster database
-p Server parameter file path
-r Role of the database (primary, physical_standby, logical_standby)
-s Startup options for the database
-y Management policy for the database (automatic, manual)
-h Print usage
$
$ srvctl modify instance -d HLPR -i HLPR4 -s +ASM4
$
$ crs_stat -p ora.HLPR.HLPR4.inst|grep REQUIRE
REQUIRED_RESOURCES=ora.db04-lisqa.ASM4.asm
$

2) SPFILE was incorrectly defined in the OCR
====================================
The imon log for the database under /log//racg shows this --
$ cd $ORACLE_HOME/log//racg
$ vi imon_HLPR.log
...
...
2009-03-19 20:36:02.729: [ RACG][1] [8400][1][ora.HLPR.HLPR1.inst]: racgimon exiting

2009-03-19 20:43:34.680: [ RACG][1] [12432][1][ora.HLPR.HLPR1.inst]: racgimon started

2009-03-19 20:43:37.137: [ RACG][6] [12432][6][ora.HLPR.HLPR1.inst]:
SQL*Plus: Release 10.2.0.4.0 - Production on Thu Mar 19 20:43:36 2009

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Enter user-name: Connected to an idle instance.

SQL> ORA-01565: error in identifying file '+RAC_REDO02/HLPR/spfileHLPR.ora'

2009-03-19 20:43:37.137: [ RACG][6] [12432][6][ora.HLPR.HLPR1.inst]: ORA-17503: ksfdopn:2 Failed to open file
+RAC_REDO02/HLPR/spfileHLPR.ora
ORA-15056: additional error message
ORA-17503: ksfdopn:DGOpenFile05 Failed to open file +RAC_REDO02/hlpr/spfile
ORA-01078: failure in processing system parameters
SQL> Disconnected
This shows that the SPFILE was incorrectly defined in the OCR

Fix
====
Modified OCR for the correct location of the SPFILE -
$ cat $ORACLE_HOME/dbs/initHLPR1.ora
spfile='+HLPR_DATA/HLPR/PARAMETERFILE/spfile.286.640265465'
$
$ srvctl modify database -d HLPR -p +HLPR_DATA/HLPR/PARAMETERFILE/spfile.286.640265465
$

This fixed the OCR and database could then be started using SRVCTL.

There is also one more issue that I noticed today in the listener configuration w/ CRS. On node4, the listener was up under db_home (typically it is seen under ASM home). Doing a "ps -ef|grep tns" will tell what OH is being used for the listener home. Also, the crs_stat -p output shows that.
[oracle@ajithpathiyil /home/oracle] $ ps -ef|grep tns
oracle 1982 1 0 May 13 ? 2:06 /d01/app/oracle/product/10.2/oracledb/bin/tnslsnr LISTENER_DB04-LISQA -inherit
oracle 1965 1 0 May 13 ? 5:49 /d01/app/oracle/product/10.2/oracledb/bin/tnslsnr LISTENER_CGQA_DB04-LISQA -inherit
oracle 2007 1 0 May 13 ? 4:33 /d01/app/oracle/product/10.2/oracledb/bin/tnslsnr LISTENER_HLPR_DB04-LISQA -inherit
oracle 2041 1 0 May 13 ? 5:43 /d01/app/oracle/product/10.2/oracledb/bin/tnslsnr LISTENER_ORACLE_DB04-LISQA -inherit
oracle 2024 1 0 May 13 ? 6:33 /d01/app/oracle/product/10.2/oracledb/bin/tnslsnr LISTENER_PLQA_DB04-LISQA -inherit
oracle 22092 23479 0 11:18:42 pts/1 0:00 grep tns

As we can see from above, listener "LISTENER_DB04-LISQA" is running under the database home (/d01/app/oracle/product/10.2/oracledb). The crs_stat -p output for this resource shows this --
[oracle@ajithpathiyil /home/oracle] $ crs_stat -p ora.db04-lisqa.LISTENER_DB04-LISQA.lsnr
NAME=ora.db04-lisqa.LISTENER_DB04-LISQA.lsnr
TYPE=application
ACTION_SCRIPT=/d01/app/oracle/product/10.2/oracledb/bin/racgwrap
ACTIVE_PLACEMENT=0
AUTO_START=1
CHECK_INTERVAL=600
DESCRIPTION=CRS application for listener on node
FAILOVER_DELAY=0
FAILURE_INTERVAL=0
FAILURE_THRESHOLD=0
HOSTING_MEMBERS=db04-lisqa
OPTIONAL_RESOURCES=
PLACEMENT=restricted
REQUIRED_RESOURCES=ora.db04-lisqa.vip
RESTART_ATTEMPTS=5
SCRIPT_TIMEOUT=600
START_TIMEOUT=0
STOP_TIMEOUT=0
UPTIME_THRESHOLD=7d
USR_ORA_ALERT_NAME=
USR_ORA_CHECK_TIMEOUT=0
USR_ORA_CONNECT_STR=/ as sysdba
USR_ORA_DEBUG=0
USR_ORA_DISCONNECT=false
USR_ORA_FLAGS=
USR_ORA_IF=
USR_ORA_INST_NOT_SHUTDOWN=
USR_ORA_LANG=
USR_ORA_NETMASK=
USR_ORA_OPEN_MODE=
USR_ORA_OPI=false
USR_ORA_PFILE=
USR_ORA_PRECONNECT=none
USR_ORA_SRV=
USR_ORA_START_TIMEOUT=0
USR_ORA_STOP_MODE=immediate
USR_ORA_STOP_TIMEOUT=0
USR_ORA_VIP=

[oracle@ajithpathiyil /home/oracle] $

The action script clearly points to the database home.
To change this, we again use the "srvctl modify" command.

[oracle@ajithpathiyil /home/oracle] $ srvctl modify listener -h
Usage: srvctl modify listener -n [-l ] -o
-n Node name
-l "" Comma separated listener names
-o ORACLE_HOME path
-h Print usage
[oracle@ajithpathiyil /home/oracle] $

[oracle@ajithpathiyil /home/oracle] $ srvctl modify listener -n db04-lisqa -l LISTENER_DB04-LISQA -o /d01/app/oracle/product/10.2/ASM

[oracle@ajithpathiyil /home/oracle] $ crs_stat -p ora.db04-lisqa.LISTENER_DB04-LISQA.lsnr
NAME=ora.db04-lisqa.LISTENER_DB04-LISQA.lsnr
TYPE=application
ACTION_SCRIPT=/d01/app/oracle/product/10.2/ASM/bin/racgwrap
ACTIVE_PLACEMENT=0
AUTO_START=1
CHECK_INTERVAL=600
DESCRIPTION=CRS application for listener on node
FAILOVER_DELAY=0
FAILURE_INTERVAL=0
FAILURE_THRESHOLD=0
HOSTING_MEMBERS=db04-lisqa
OPTIONAL_RESOURCES=
PLACEMENT=restricted
REQUIRED_RESOURCES=ora.db04-lisqa.vip
RESTART_ATTEMPTS=5
SCRIPT_TIMEOUT=600
START_TIMEOUT=0
STOP_TIMEOUT=0
UPTIME_THRESHOLD=7d
USR_ORA_ALERT_NAME=
USR_ORA_CHECK_TIMEOUT=0
USR_ORA_CONNECT_STR=/ as sysdba
USR_ORA_DEBUG=0
USR_ORA_DISCONNECT=false
USR_ORA_FLAGS=
USR_ORA_IF=
USR_ORA_INST_NOT_SHUTDOWN=
USR_ORA_LANG=
USR_ORA_NETMASK=
USR_ORA_OPEN_MODE=
USR_ORA_OPI=false
USR_ORA_PFILE=
USR_ORA_PRECONNECT=none
USR_ORA_SRV=
USR_ORA_START_TIMEOUT=0
USR_ORA_STOP_MODE=immediate
USR_ORA_STOP_TIMEOUT=0
USR_ORA_VIP=

[oracle@ajithpathiyil /home/oracle] $

The CRS registration has been changed.
Now we only need to stop the listener and restart it under the ASM home.

HAPPY LEARNING!