AutoConfig is exiting with status 1

At the time of running the Autoconfig it is exiting with status 1.

The content of Autoconfig log file is as follows :


Perl lib version (v5.8.8) doesn't match executable version (v5.10.0) at /usr/lib64/perl5/5.8.8/x86_64-linux-thread-multi/Config.pm line 46.
Compilation failed in require at /DB/CRP1/db/tech_st/11.2.0/appsutil/clone/ouicli.pl line 35.
BEGIN failed--compilation aborted at /DB/CRP1/db/tech_st/11.2.0/appsutil/clone/ouicli.pl line 35.

autoconfig then AutoConfig could not successfully execute the following scripts: 
Directory: /DB/CRP1/db/tech_st/11.2.0/perl/bin/perl -I /DB/CRP1/db/tech_st/11.2.0/perl/lib/5.8.3 -I /DB/CRP1/db/tech_st/11.2.0/perl/lib/site_perl/5.8.3 -I /DB/CRP1/db/tech_st/11.2.0/appsutil/perl /DB/CRP1/db/tech_st/11.2.0/appsutil/clone
ouicli.pl INSTE8_APPLY 255


AutoConfig is exiting with status 1
RC-50013: Fatal: Instantiate driver did not complete successfully.


Solution :

• Check the version of perl

               perl -v

               It could return - This is perl, v5.8.8

• Now set env to use the db_home perl

             export PATH=$ORACLE_HOME/perl/bin:$PATH:/usr/ccs/bin 

            and check again. 

             It should return - This is perl, v5.10.0 ....

• Now re-run adcfgclone.

It should work fine now.

Deallocating Unused Space

When you deallocate unused space, the database frees the unused space at the unused (high water mark) end of the database segment and makes the space available for other segments in the tablespace.

Prior to deallocation, you can run the UNUSED_SPACE procedure of the DBMS_SPACE package, which returns information about the position of the high water mark and the amount of unused space in a segment. For segments in locally managed tablespaces with automatic segment space management, use theSPACE_USAGE procedure for more accurate information on unused space.

The following statements deallocate unused space in a segment (table, index or cluster):

ALTER TABLE table DEALLOCATE UNUSED KEEP integer;

ALTER INDEX index DEALLOCATE UNUSED KEEP integer;

ALTER CLUSTER cluster DEALLOCATE UNUSED KEEP integer;

The KEEP clause is optional and lets you specify the amount of space retained in the segment. You can verify that the deallocated space is freed by examining the DBA_FREE_SPACE view.

How to check free space in ASM

 1. Connect to asm instance:

select name, state, total_mb, free_mb from v$asm_diskgroup;

NAME       STATE     TOTAL_MB FREE_MB

—————————— ———– ———- ———-

ORADATA MOUNTED 65536      53439

SELECT name, type, ceil (total_mb/1024) TOTAL_GB , ceil (free_mb/1024) FREE_GB, required_mirror_free_mb,

ceil ((usable_file_mb)/1024) FROM V$ASM_DISKGROUP;

NAME TYPE TOTAL_GB FREE_GB REQUIRED_MIRROR_FREE_MB CEIL((USABLE_FILE_MB)/1024)

ORACLE_DATA1  EXTERN         466 236           0            236

ORACLE_FLASH1 EXTERN         131  124           0            124

ORACLE_FLASH2 EXTERN         10 9 0 9

2.Using ASMCMD tool

Make sure to setup ORACLE_SID and ORACLE_HOME environment variables to ASM instance.

-bash-3.2$ asmcmd -p

ASMCMD [+]>; du

Used_MB Mirror_used_MB

12011 12011

ASMCMD [+] >;

or run

lsdg command

State Type Rebal Unbal Sector Block AU Total_MB Free_MB Req_mir_free_MB Usable_file_MB Offline_disks Name

MOUNTED EXTERN N N 1024 4096 1048576 40960 37628 0 37628 0 ORADATA/

MOUNTED EXTERN N N 1024 4096 1048576 32768 32675 0 32675 0 ORADATA1/

3. Using Grid Control

Go to asm host and select asm instance. Go Administration tab and input asm administration password:

ORA-00980: synonym translation is no longer valid

This error occurs when you have a public synonym defined and the object referencing or owning the synonym has been dropped or deleted. You can check for the invalid synonyms using below query.

select * from dba_synonyms s

where table_owner not in(‘SYSTEM’,'SYS’)

and db_link is null

and not exists

(select 1

from dba_objects o

where s.table_owner=o.owner

and s.table_name=o.object_name);



Solution:

Replace the synonym with the name of the object it references or re- create the synonym so that it refers to a valid table, view, or synonym.
The Invalid synonyms must be dropped and recreated with the right owner. I normally do this using below query.(In this example the owner of the synonym was dropped and I am altering the synonym to reference the new owner)

DROP PUBLIC SYNONYM SYNONYM_NAME;
CREATE PUBLIC SYNONYM SYNONYM_NAME FOR NEW_OWNER.OBJECT_NAME;

Parallel Concurrent Processing(PCP)

PCP makes sense on a RAC environment.

POINT1: Check if you require PCP to load balance or to be used as failover.

If both the nodes then, Change the names of the concurrent managers say SM:Node A and Assign PRIMARY and SECONDARY Nodes to that manager.

Then duplicate the record and change the name to SM:Node B for the 2nd manager and change the PRIMARY and SECONDARY Nodes
Note: Ensure the hostname should be only the name of the host from uname -a command, not the virtual hostname.

Do it for all the managers in the instance including tx mgr, internal mgr EXCEPT Internal Monitor.

Following configuration details are from metalink document 362135.1 section 3.12.

Check PCP Prerequisites

It is assumed that you have more than one Concurrent processing tiers in your environment to setup PCP. If you do not have this refer to OracleMetaLink Note: 230672.1 for cloning of Applications tier.

Set Up PCP

1. Execute AutoConfig by using $COMMON_TOP/admin/scripts//adautocfg.sh on all concurrent nodes.
2. Source the application environment by using $APPL_TOP/APPSORA.env
3. Check the configuration files tnsnames.ora and listener.ora located under 8.0.6 ORACLE_HOME at $ORACLE_HOME

/network/admin/. Ensure that you have information of all the other concurrent nodes for FNDSM and FNDFS entries.
4. Restart the application listener processes on each application node.
5. Logon to Oracle E-Business Suite 11i Applications using SYSADMIN in login and System Administrator Responsibility.

Navigate to Install > Nodes screen and ensure that each node in the cluster is registered.
6. Verify whether the Internal Monitor for each node is defined properly with correct primary and secondary node

specification and work shift details. Also make sure the Internal Monitor manager is activated by going into Concurrent ->

Manager -> Administrator and activate the manager.
(e.g. Internal Monitor: Host2 must have primary node as host2 and secondary node as host3)
7. Set the $APPLCSF environment variable on all the CP nodes pointing to a log directory on a shared file system.
8. Set the $APPLPTMP environment variable on all the CP nodes to the value of the UTL_FILE_DIR entry in init.ora on the

database nodes. This value should be pointing to a directory on a shared file system.
9. Set profile option ' Concurrent: PCP Instance Check' to OFF if DB instance sensitive failover is not required. By setting

it to 'ON' Concurrent Managers will failover to a secondary middle-tier node when database instance it is connected goes down.


Set Up Transaction Managers

1. Shut down the application tiers on all the nodes.
2. Shut down all the database instances cleanly in RAC environment using
SQL>shutdown immediate;
3. Edit $ORACLE_HOME/dbs/_ifile.ora. Add the following parameters:
* _lm_global_posts=TRUE
* _immediate_commit_propagation=TRUE
4. Start the instances on the database nodes, one by one.
5. Start up the Application tier on all the nodes.
6. Log on to Oracle E-Business Suite 11i Applications using SYSADMIN in login and System Administrator Responsibility.
Navigate to Profile > System and change the profile option ‘Concurrent: TM Transport Type' to ‘QUEUE' and verify the

transaction manager works across the RAC instance.
7. Navigate to Concurrent > Manager > Define screen, and set up the primary and secondary node names for transaction

managers.
8. Restart the concurrent managers.

Load Balance the Concurrent Processing Tiers

1. Create a load balancing alias similar to _806_balance as shown sample in
Appendix C.
2. Edit the applications context file through Oracle Applications Manager interface and set the value of Concurrent Manager

TWO_TASK to load balancing alias created in previous step.
3. Execute AutoConfig by using $COMMON_TOP/admin/scripts//adautocfg.sh on all concurrent nodes.

Appendix C: Example for Concurrent Processing Tiers

Sample for Concurrent Processing Tiers

_806_BALANCE=
(DESCRIPTION_LIST=
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=))
(CONNECT_DATA=
(SERVICE_NAME=)
(INSTANCE_NAME=)
)
)
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=))
(CONNECT_DATA=
(SERVICE_NAME=)
(INSTANCE_NAME=)
)
)
)



Check test case below:

1. All tnsnames.ora files in the $TNS_ADMIN(806)_HOME's and IAS_ORACLE_HOME/network/admin/ should be identical.

2. The following is an example:

DEV =
(DESCRIPTION =
(ENABLE=BROKEN)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(PORT = 1521)(HOST = hostname1))
(ADDRESS = (PROTOCOL = TCP)(PORT = 1521)(HOST = hostname2))
)
(CONNECT_DATA = (SERVICE_NAME = devdb)(SERVER=DEDICATED)
)


DEV1= (DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST= hostname1)(PORT=1521))
(CONNECT_DATA=(INSTANCE_NAME=dev1)(SERVICE_NAME=devdb))
)

DEV2= (DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST= p201)(PORT=1521))
(CONNECT_DATA=(INSTANCE_NAME=dev2)(SERVICE_NAME=devdb))
)

3. DEV is your service_name alias defined for the RAC environment.
DEV1 and DEV2 are the actual database instances. The value of INSTANCE_NAME correlates to the value of INSTANCE_NAME defined in the database init.ora files.

4. For the alias DEV, the hostname definitions need to be ordered by local host.
In the above example, this entry would exist for the tnsnames.ora for hostname1.
For the tnsnames.ora on hostname2, the entry would look like:

(ADDRESS = (PROTOCOL = TCP)(PORT = 1521)(HOST = hostname2))
(ADDRESS = (PROTOCOL = TCP)(PORT = 1521)(HOST = hostname1))

5. Please ensure that parameter name INSTANCE_NAME is used instead of SID.
INSTANCE_NAME is what is recognized in the 11i RAC environment. SID value definition seems to cause problems with how the

database connection is being recognized when one of the database is shutdown.

6. After these settings start up all database instances. Make the following connections from all host machines in the 11i

Applications environment:

a. apps/@dev
b. apps/@dev1
c. apps/@dev2


NOTE: If you setup autoconfig then you do not have to manually edit 806 tnsnames. Follow metlaink doc 362135.1 section 3.11

Oracle table reorganization: Online or Offline

When using tables with large amounts of rows and especially after a lot of rows have been deleted from such a table, reorganizing the table may improve the performance of the table.


Oracle knows two types of table reorganizations.

1. Rebuilding the table by means of recreating it
2. Rebuilding the table by shrinking its free space ( Oracle 10g and up )

Below the two methods are explained.


Rebuilding the table by means of recreating it

There are two ways of rebuilding a table by recreating it. The first option is to export all the data into a file using the export utility.
After that truncate ( of recreate ) the table and reload the data back into it. The disadvantage here however is a long downtime.

Another method is moving the table either to another tablespace or within the same tablespace.


The advantage here is that the downtime will me much less, but it also has some disadvantages:

• The tablespace needs to be able to store this second copy of the table
• The tables indexes, any depending objects like packages, procedures can become INVALID and might need to be rebuild
• There will be a table-level exclusive lock on the table involved. Any application using that table will be affected by this lock.

By moving the table to another tablespace temporarily, the DBA is also able to reorganize the tablespace.


Rebuilding the table by shrinking its free space
Starting with Oracle 10g all the above is replaced by a very nice feature called 'SHRINK SPACE';
As of this release, tables can be reorganized online, without affecting the users functionality.
That is:

* The table itself must be in an ASMM tablespace
* The table must have row movement enabled
* At the end of the action a shortt table level lock takes place to adjust the HWM (Highwater Mark) of the table.


And it's easy !!
Just enable 'row movement' and start shrinking tables:

SQL> ALTER TABLE MYTABLE ENABLE ROW MOVEMENT; 
This enables row movement of the table. THis is necessary so that oracle is able to actually move the rows to other free extents in order to shrink the space needed.

SQL> ALTER TABLE MYTABLE SHRINK SPACE COMPACT; 
This shrinks the used space of the table, but does not update the HWM. This is usefull if you don't want to have a table-level lock during business hours.

SQL> ALTER TABLE MYTABLE SHRINK SPACE; 
This command shrinks the contents of the table and subsequently updates the HWM. This statement can also be used after a shrink with 'compact' option has been done, just to update the HWM.

SQL> ALTER TABLE MYTABLE SHRINK SPACE CASCADE; 
This command shrinks the contents of the table and all dependent objects like indexes.


High Water Mark: 

Now we have mentioned the High Water Mark (HWM) a couple of times. A short explanation:
The HWM is the pointer to that location of a table where on any point in history the table has grown to. If a lot of rows are deleted from a table, a lot of free space exists before the HWM. Doing a full table scan Oracle will not only read the actual rows, but also the empty space up to the HWM.
This is wasted time and is worsening the performance of getting data from that table.
So if from a large table a lot of rows are deleted, e.g. a count(*) will still take as long as before the delete.


Triggers and indexes: 

In contradiction to moving a table, or exporting/importing its data, triggers and indexes are NOT affected by a shrink space command. They will remain valid and functional during and after the operation.

Session6 RAC11g Database Creation using ASM