Friday, September 11, 2015

Concurrent Managers NOT coming up after cloning in 11i/R12

It is a very common issue that many times concurrent managers (CM) does not come up after apps cloning. Here are the steps to perform to resolve it

1 Query the apps.fnd_nodes tables. Many times,  the CM node entry is not there, or prod node entries are still there in fnd_nodes table. Stop the concurrent manager service and clear the fnd_nodes table by executing the folowing as apps user->
EXEC FND_CONC_CLONE.SETUP_CLEAN;
COMMIT;

2 Now Run AutoConfig on all tiers on the target system. Firstly on the DB tier and then on all the apps tier individually. This step will populate the apps.fnd_nodes table. If you query this table, then it will show you the target node entries now.

3 Start the CM services and then check the CM. If CM is up then fine else perform the following

4  Stop the concurrent manager services. Connect to apps user and run cmclean.sql script. This script can be downloaded from metalink. Remember to commit the script after execution. Start the CM services and then check the CM. If CM is up then fine else perform the following

5  Stop the concurrent manager service....and run the following statement on concurrent manager node
Go to cd $FND_TOP/bin
adrelink.sh force=y "fnd FNDLIBR"
adrelink.sh force=y "fnd FNDSM"
adrelink.sh force=y "fnd FNDFS"
adrelink.sh force=y "fnd FNDCRM"

6 Start the CM services and then check the CM.  Concurrent managers  should be up and running fine now.

Thursday, September 10, 2015

Interval partitioning in oracle 11g


Partitioning is simple terms means to divide a big table/index into smaller parts .

Why do we need partitioning?
Ofcourse the question comes to mind that why do we need partitioning or what is the use of partitioning.
Well partitioning has 3 main advantages->
1 Performance improvement by using partitioning  pruning and partition wise joins
2 Mangeability- operations van be performed on per partition basis rather than on whole table
3 Availability- A single partition can be dropped/truncated, different partitions can be stored in different tablespaces, etc

Type of partitioning
1 Range partitions
2 List partitions
3 Hash partitions
4 Composite partitions

What is Interval partitioning ?
Interval partitioning  basically extends range partitions. Traditionally in range partitions, the partitions for future had to be added manually using
alter table add partition command 

otherwise oracle would give following error in insert operation
ORA-14400: inserted partition key does not map to any partition

In Interval partitioning, all the future partitions would be automatically added by oracle on demand  which means that when we fire an insert statement for lets say a  date Aug2010 and when oracle finds that this partition is not available in the table, then it would automatically add this particular partition in the table and the  insert statement would execute succesfully!'

create table  tab_int_part (   start_date  DATE,   store_id  NUMBER )
PARTITION BY RANGE (start_date)
INTERVAL(NUMTOYMINTERVAL(1, 'MONTH'))
( PARTITION tab_int_part_p1 VALUES LESS THAN (TO_DATE('1-7-2010', 'DD-MM-YYYY')) );

However, there are some restrictions also of interval partitioning-
1 It can only have single partitioning key which can be either number or date datatype
2 It does not support IOT
3 Domain index are not supported these tables.
4 Also, we can create only single level interval partitioned tables and  composite partitioned tables can be only of -interval range, interval list and interval hash partitions.

How to convert range partitioned table to Interval partitioning
ALTER TABLE SET INTERVAL (interval value);
for monthly partitions-
ALTER TABLE table_month_part SET INTERVAL(NUMTOYMINTERVAL(1,'MONTH'));

for daily partitions-
ALTER TABLE table_day_part SET INTERVAL (numtodsinterval(1,'day'));


How to convert Interval partition to Range partition

Use "SET INTERVAL ()" to  convert interval partitioning back to range partitioning.
ALTER TABLE table_day_part SET INTERVAL ();


Also, remember that in oracle 11g, there can be a maximum of 1024k-1=1048575 partitions or sub partitions in a table or an index. Also, when using interval partitioning, oracle counts not only the physical partitions, but it actually counts the partitions from the lowest value created to the highest value created because  it assumes  that in between partitions might be created anytime!!