INDEX REBUILD

ORACLE

When should you rebuild index and how?

Oracle's index rebuilding guidelines appear in recommends that indexes be periodically examined to see if they are candidates for an index rebuild:

“When an index is skewed, parts of an index are accessed more frequently than others. As a result, disk contention may occur, creating a bottleneck in performance.  It is important to periodically examine your indexes to determine if they have become skewed and might need to be rebuilt.”

Oracle index nodes are not physically deleted when table rows are deleted, nor are the entries removed from the index. Rather, Oracle "logically" deletes the index entry and leaves "dead" nodes in the index tree where that may be re-used if another adjacent entry is required.  However, when large numbers of adjacent rows are deleted, it is highly unlikely that Oracle will have an opportunity to re-use the deleted leaf rows, and these represent wasted space in the index. In addition to wasting space, large volumes of deleted leaf nodes will make index fast-full scans run for longer periods.  These deleted leaf nodes can be easily identified by running the IDL.SQL script.

The number of deleted leaf rows

The term "deleted leaf node" refers to the number of index inodes that have been logically deleted as a result of row deletes. Remember that Oracle leaves "dead" index nodes in the index when rows are deleted. This is done to speed up SQL deletes, since Oracle does not have to allocate resources to rebalance the index tree when rows are deleted.

Index height

The height of the index refers to the number of levels that are spawned by the index as a result in row inserts. When a large amount of rows are added to a table, Oracle may spawn additional levels of an index to accommodate the new rows.

Oracle indexes can support many millions of entries in three levels.  Any Oracle index that has spawned to a 4th level followed by a large delete job might benefit from rebuilding to restore the index to its pristine state.

Gets per index access

The number of "gets" per access refers to the amount of logical I/O that is required to fetch a row with the index. As you may know, a logical "get" is not necessarily a physical I/O since much of the index may reside in the Oracle buffer cache.

Unfortunately, Oracle does not make it easy to capture this information. In Oracle we must issue these commands to populate the statistics in dba_indexes and related dictionary tables:

ANALYZE INDEX index_name COMPUTE STATISTICS

ANALYZE INDEX index_name VALIDATE STRUCTURE

We might want to rebuild an index if the “block gets” per access is excessive.  This happens when an index becomes "sparse" after high delete activity, making full-index scans requires unnecessary I/O. Another rebuild condition would be cases where deleted leaf nodes comprise more than 20% of the index nodes.

How to rebuild index?

As you may know, you can easily rebuild an Oracle index with the command:

ALTER INDEX index_name REBUILD tablespace FLOP;

Done properly during scheduled downtime, rebuilding an index is 100% safe.  Note the use of the tablespace option.  When rebuilding multi-gigabyte indexes, many DBA's will rebuild partitioned indexes into a fresh, empty tablespace for greater manageability.

The ALTER INDEX index_name REBUILD command is very safe way to rebuild indexes. Here is the syntax of the command:

alter index index_name

rebuild

tablespace tablespace_name

storage (initial new_initial next new_next freelists new_freelist_number )

Unlike the traditional method where we drop the index and recreate it, the REBUILD command does not require a full table scan of the table, and the subsequent sorting of the keys and rowids. Rather, the REBUILD command will perform the following steps:

1.         Walk the existing index to get the index keys.

2.         Populate temporary segments with the new tree structure.

3.         Once the operation has completed successfully, drop the old tree, and rename the temporary segments to the new index.

As you can see from the steps, you can rebuild indexes without worrying that you will accidentally lose the index. If the index cannot be rebuilt for any reason, Oracle will abort the operation and leave the existing index intact. Only after the entire index has been rebuilt does Oracle transfer the index to the new b-tree.

Note that the TABLESPACE clause should always be used with the ALTER INDEX REBUILD command to ensure that the index is not rebuilt within the default tablespace (usually SYS). It's always a good idea to move an index into another tablespace and you must have enough room in that tablespace to hold all of the temporary segments required for the index rebuild, so most Oracle administrators will double-size index tablespaces with enough space for two full index trees.

LOCAL LISENER vs REMOTE LISTENER

you're right, LOCAL_LISTENER on each node should point to the listener on that node.
REMOTE_LISTENER should point to all listeners on all nodes if you want server side load balancing,
otherwise don't set REMOTE_LISTENER.
Use listener_<instance_name> as the alias name for the local listener, and listeners_<servicename> for the remote listener alias.

     Quick explanation :
     The SERVICE_NAME=VIS is standing for the 4 RAC instances : VIS1, VIS2, VIS3 and VIS4.
     The Apps user will connect to the 11i/RAC via the SERVICE_NAME=VIS, and the Rac engine will
      randomly switch the users to "one of" the RAC instance (VIS1, VIS2,VIS3 or VIS4)  where has the
      lower load compare to the 3 other instances, this mechanism is calling the RAC Load balancing.

the remote_listener parameter points to your SCAN listener(s) which will do the load balancing, and local_listener points to the node listener which will do the spawn-and-bequeath.
When a SCAN Listener receives a connection request, the SCAN Listener will check for the least loaded instance
providing the requested service. It will then re-direct the connection request to the local listener on the node where the
least loaded instance is running. Subsequently, the client will be given the address of the local listener. The local listener
will finally create the connection to the database instance.
  
http://www.oracle.com/technetwork/database/clustering/overview/scan-129069.pdf
How To Find Out The Example of The LOCAL_LISTENER and REMOTE_LISTENER Defined In The init.ora When configuring the 11i or R12 on RAC ? [ID 744508.1]
http://www.comp.dit.ie/btierney/oracle11gdoc/install.111/b28264/undrstnd.htm

Solving Concurrent Program “Pending Standby” status

1. Concurrent request status is PENDING STANDBY status?
Pending Standby  - Phase Pending and Status Standby means Program to run request is incompatible with other program(s) currently running.
 I have submitted one concurrent request at 19:00 Hrs (Day1) and this request is to be run by a concurrent manager which has work shift of 00:00 Hrs to 08:00 Hrs (Day 2). The moment when I submit this concurrent request it’s status will be “Inactive No Manager” until this manager goes active. Suppose I have many requests pending with this concurrent and this request couldn’t start.
Now what will be status of this request after 08:00 Hrs (Day 2) when it’s manager goes down? Here in my case it is still “Inactive No Manager” though I think it should be “Pending Standby”
This could be for different reasons (other pending requests, no place for new requests, ...etc).
Solving Concurrent Program “Pending Standby” status (Oracle EBS 11i)
There are instances when we run the Concurrent Program, it goes to “Pending Standby” status. Sometimes we can see this status get changed after few minutes but,
there are instances it will be in the same status forever. This means the request is not progressing and will not be completed ever.
To make that request to progress again, the following steps need to be carried out; Go to System Administrator Responsibility;
——>>System Administrator
————>Concurrent manager
——————–>Conflict Resolution Manager
See whether the Conflict Resolution manager is running, if is it not, activate it and it will solve the standby status issue.
If the Conflict Resolution manager is running
——->Click “Verify” button
Once completed with the verification, resubmit the previous hanging request, and it should run without any problem.

DR REBUILD procedure

dr REBUILD PROCEDURE:
====================
1. Shutdown the standby database if it is running.
a) First cancel the recovery
sqlplus / as sysdba
sql> recover managed standby database cancel;
sql>shutdown immediate
b) stop the listener.
2. Put the all tablespaces in hot backup mode in prod.
3. Have SAN Administrator to Split the Disks and mount the disks on the standby server.
4. Put end hot backup on all tablespaces
5. create the standby control file
ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/tmp/boston.ctl';
6. rsync the standby controlfile to the standby server
7. Bringup the standby db
a) copy the control file to normal control files loc.
b)create standby spfile from pfile
c)start standby listener
d)mount standby db and
start recovery =  alter database recover managed standby database disconnect from session;

8. Verify the standby recovery progress
a) Start remote archiving by forcing a log switch on the primary database node
$ sqlplus <user>/<pass>
SQL> alter system archive log current;
b) Tail the Standby Database alert log to verify there are no errors and make sure archive log switched
in previous step received by standby and Media Recovery delayed by 60 minutes for the log
9. Schedule archive job for standby db.

All about Dataguard

How do you apply a Patchset,PSU or CPU in a Data Guard Physical Standby configuration [ID 278641.1]
1. In Primary disable log shipping to the standby & Stop MRP on standby
2. Shutdown Standby Database & Listener
3. Install CPU patch on Standby
4. Shutdown Primary database & Listener.
5. Apply Patch on Primary & Run Post installation Scripts(catbundle.sql)
6. Re-enable Log shipping on Primary
7. Monitor the redo apply from Primary to Standby
http://www.oracle-ckpt.com/applying-cpu-patch-on-dataguard-physical-standby-configuration-11gr2/
Patching oracle apps with data guard
Lately we have applied a ptach on our apps instance with physical standby database.

Following are the steps used.

1. Stopped the recovery delay in standby db
2. Stopeed application tier services on production
3. Switch the log in prod db and get the last sequence#
4. verify on standby that last log applied
5. Stop recovery on standby
6. Take the apps prod instance to maintenance mode using adadmin
7. Perfoem patching
8. Re-start redo shipping and apply on standby
9. Run middle tier pre-clone on prod
10. Run DB tier pre-clone (Only if patching updates ORACLE_HOME)
11. Synchronize the application tier file system with standby (use rsync on linux)
12. When sync completes create new context file using adclonectx.pl
13. After new context file created configure file system using adconfig.pl
14. Optinally configure standby DB file system if you did step 10.
15. End.

Now the production and stnadby oracle applications instances are in sync.
Master Note for Data Guard [ID 1101938.1]

Latest interview Q's - 2

1. HOw do u clone from non-rac db to rac db and viceversa?
2. What will happen when CRS goes down?
3. What is css ?
4. how u decommission the database and apps?
5. When you decise a particular process as runaway process and how u will resolve it?
6. Concurrent prograam is running very slow how to debug it?
7. What adcfgclone will do internally and what scripts will run inside?
8. HOw to confirm whether DR is running or not?
9. HOw to rebuild DR explain in detail steps?
10. fndcpass syntax explain in details the arguments?
11. 11i and R12 differences.
12. cp is in pending standby status why?
13. In case of ICM is down what will be the impact?
14. backups and recovery scenarios in ur exp?
15. Explain Upgradation 11i to r12.
16. Any Patching issues?
17. what r the types the patches u have applied?
18. Diff between cache clearence in 11i and R12.
19. HOw u will add datafile to TS in raw filesystem?
20. What r the concurrent manager issues you have faced?
21. what is the crosscheck command in rman?
22. Any recovery catalog issues?
23. What is the naming convention for concurrent request log files and CM log files and FNDSM log files and what is the location these log files?

Latest Interview Questions

1. Explain the difference between adcfgclone.pl dbconfig and autoconfig?
2. what is local inventory and global inventory?in case of global inventoy corrupted how to apply opatch without creating the global inventory?
optach -invPtrLoc=oraInst.loc
3. what is the use of .patch_storage directory explan in details?
4. Explain the -invptrloc option for optach?
5. Explain some 10g RAC specific parameters?
6. can you talk about local listener and remote listener?local_lister,remote_listener parameters can be placed in the same pfile?
How To Find Out The Example of The LOCAL_LISTENER and REMOTE_LISTENER Defined In The init.ora When configuring the 11i or R12 on RAC ? [ID 744508.1]
http://www.comp.dit.ie/btierney/oracle11gdoc/install.111/b28264/undrstnd.htm

7. What is thread paramer in specific to RAC?
8. Explain database upgradation , While upgrading why we run the gather stats for dictionay?
9. While apps upgrading why we will create appsutil and copy to db home?what is the purpose?
Update database tier nodes with the Release 12.1.2 code level.
After you have upgraded to Release 12.1.2, perform the following post-update steps to update all
database tier nodes with the code level, provided by Release 12.1.2:
10. Explain about online redo log files in RAC environment? where they store,how they work,etc?
11. My ICM is down and i have submitted the concurrent request what will happen to my reuqest?
12. With PCP option enabled in the environemnt if one of my concurent manger server node crashes what will happen to running requests?
13. What is the job of internal monitor?
14. What is the unified appltop in R12?
ANS: http://onlineappsdba.com/index.php/2007/05/18/unified-appl_top-changed-feature-in-oracle-apps-r12/
15. What is the default forms mode in R12 and 11i?what is the difference between servlet and socket modes of forms mode?In 11.5.10 env, if i am business user with forms mode
    in servlet mode whether i need jintiator in my PC or not?
ANS:
====
Default Forms connection mode in Oracle Applications R12 is “SERVLET” where as in Oracle Apps 11i default form connect mode is “SOCKET”
When forms run SOCKET Mode these are dedicated connection between Client Machine & Form Server (Started by adfrmctl.sh).
When Forms run in servlet mode the forms requests are fulfilled by Jserv in Apache .
There will be additional JVM for Forms Request in that case and you won't start form via adfrmctl.sh.
Oracle Form Servlet Overview in apps R12
——————————————
i) In this mode, Java servlet handles communication between forms client(java based) and Oracle Forms Service (10g).
ii) All connection is via HTTP Server so there is no need to start form server and no need to open form server port on firewall between client machine and application tier.
iii) More secure as compared to Forms Socket Mode.
iv) Network traffic is more as HTTP protocol is more chatty so little bit network bandwidth hungry when compared with SOCKET mode
v) No additional certificate requirement during SSL implementation for application tier, single certificate will handle both forms & web connection

16. What is the difference between 11i and R12?

17a) Explain the apps upgradation?
http://onlineappsdba.com/index.php/2008/07/09/upgrade-oracle-apps-11i-to-r1212i-1204-key-points/
18. What CPU patch and PSu patch and explain the difference? what r the addition steps for applying the PSU patch?
19. While upgrading your db what about your dataguard how you operated the dataguard?
20. what is the MRP and RFS process?
21. While applying optach your database what about your datagurad? whether it will be up?
22. what optach apply will do stage by stage?
23. Explain some SSO profiles?
24. What is the diff in starting the db in normal mode and upgrade mode?

1) how to integrate sso with ebiz?
2) after sso login got the page for login credentials again? why?how to resolve it?
3) what is sso cookie?
4) explain about cloning brefily?
5) after cloning my concurrent managers are not coming up? how to resolve it?
6) My concurrent program is running very slow how to resolve it?
7) one user is complaining about one form is running very slow? what are the checks u will do it ?