Solved: Oracle Homes are already registered in the global inventory:

CAUSE: The script was trying to register the oracle home in inventory but its failing to update it.
SOLUTION: Please delete existing files under oraInventory folder and retry the adcfgclone.

ERROR :

Below Oracle Homes are already registered in the global inventory:
/opt/silprod/fs2/FMW_Home/oracle_common
/opt/silprod/fs2/FMW_Home/webtier
/opt/silprod/fs2/FMW_Home/Oracle_EBS-app1

1.. First check central and or local oraInventory location

[oracle@silperp ~]$ cat /etc/oraInst.loc
inventory_loc=inventory_loc=/opt/silprod/db/tech_st/11.2.0//admin/oui/SILP25_silperp/oraInventory
inst_group=oracle[oracle@silperp ~]$

Solution: 

2. call run environment
   
   . [oracle@silperp ~]$ . /opt/silprod/EBSapps.env

  E-Business Suite Environment Information
  ----------------------------------------
  RUN File System           : /opt/silprod/fs1/EBSapps/appl
  PATCH File System         : /opt/silprod/fs2/EBSapps/appl
  Non-Editioned File System : /opt/silprod/fs_ne


  DB Host: silperp.silhon.com  Service/SID: SILP25


  E-Business Suite Environment Setting
  ------------------------------------
  - Enter [R/r] for sourcing Run File System Environment file, or
  - Enter [P/p] for sourcing Patch File System Environment file, or
  - Enter anything else to exit

  Please choose the environment file you wish to source [R/P]:R

  Sourcing the RUN File System ...

[oracle@silperp ~]$

2.  Before deinstalling  don't remove any direcotry i.e. FMW_Home and inst

   

     Deinstall registered Oracle Homes in oraInventory

     cd  /opt/silprod/fs1/EBSapps/10.1.2/oui/bin

    ./runInstaller -silent -deinstall REMOVE_HOMES={"/opt/silprod/fs2/FMW_Home/Oracle_EBS-app1"}

    ./runInstaller -silent -deinstall REMOVE_HOMES={"/opt/silprod/fs2/FMW_Home/webtier"}

    ./runInstaller -silent -deinstall REMOVE_HOMES={"/opt/silprod/fs2/FMW_Home/oracle_common"}

Note: if above commands not successes then:

     cd  /opt/silprod/fs1/EBSapps

    $> find -name deinstall and execute

3. Backup or remove exiting oraInventory directory

cd /opt/silprod/db/tech_st/11.2.0//admin/oui/SILP25_silperp/

Backup: $> mv oraInventory oraInventory_backup

Remove: $> rm -rf oraInventory

$> mkdir oraInventory

4. After completing deinstalling and re-creating directy try adcfgclone.pl script.

Solved: ORA-38760: This database instance failed to turn on flashback database

After up-gradation Oracle Database from 11gR2 to 12.2.0.1 in Oracle RedHat Linux 6.6 x86_64, utlrp.sql not responded, and when trying to restart database by logging new session,  I faced this issue, and solved after reviewing alert log and googling.

 ORA-38760: This database instance failed to turn on flashback database

SQL>  shutdown abort;
ORACLE instance shut down.
SQL>  startup
ORACLE instance started.

Total System Global Area 1.0737E+10 bytes
Fixed Size    12170960 bytes
Variable Size   939526448 bytes
Database Buffers 9764339712 bytes
Redo Buffers    21381120 bytes
Database mounted.
ORA-38760: This database instance failed to turn on flashback database

SQL>

1. Check flashback is in on target database

SQL>  select name,flashback_on from v$database;

NAME      FLASHBACK_ON

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

SILP21    YES

2. Check target database is in mount state

SQL>   select name,open_mode from v$database;

NAME      OPEN_MODE

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

SILP21    MOUNTED

SQL> 

3. Disable flashback and try to open target database

SQL>  alter database flashback off;

Database altered.

SQL>  alter database open;

alter database open

*

ERROR at line 1:

ORA-38760: This database instance failed to turn on flashback database

SQL> 

Note: still unable to open database, enable database flashback on and clear / delete restore points

A) Enable Database Flashback

SQL>  alter database flashback on;

alter database flashback on

*

ERROR at line 1:

ORA-38706: Cannot turn on FLASHBACK DATABASE logging.

ORA-38714: Instance recovery required.

B) Check old restore flashback points

SQL> select NAME,SCN from v$restore_point;

NAME                     SCN

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

BEF_UPGRADE     5.9837E+12

BEF_UPGRADE1   5.9837E+12

SQL> 

C) Delete database flashback restore points

SQL>  drop restore point BEF_UPGRADE;

Restore point dropped.

SQL>  drop restore point BEF_UPGRADE1;

Restore point dropped.

SQL> 

D) Finally, now open database.

SQL>  alter database open;

Database altered.

SQL> 

Oracle E-Business Suite R12 Cloning - Part I

In the following document we will clone our PROD single node instance installed on tmfbtest2.tameerbank.com server at location /d01/oracle/TEST to the other server tmfbtest1.company.com at location  /d01/oracle/TEST

                                                  

Pre-Clone

/opt/oraprod/db/tech_st/11.1.0/appsutil/scripts/PROD_r12prod

perl adpreclone.pl dbTier

/opt/oraprod/inst/apps/PROD_r12prod/admin/scripts

                                                      Source system                                Target system

    SID                                              PROD                                             PROD

    Port pool                                     80                                                    53

    Database user                             oraprod                                          oraprod

    Application user                        applprod                                         applprod

1. Start source system database tier

              Login as oracle database user

Start database listener

 

                 Start database

 

2. Start source system application tier

  

         Login as oracle application user

 

       Start application

3.  Prepare source system database tier

  

        Connect to database user and run given script

       Provide apps user password

      It will take some time to complete the stage

       Log file should be checked 

4. Prepare source system – application tier

      

          Run given script

        It will take some time to complete the stage

         After completing this stage generated log must be checked

        

         At the end of log file status must be “completed successfully”

5. Stop source system – application tier

6. Stop source system - database tier

           Stop  database listener

          Stop  the database

7. Copy the source system to target system

          Login as root user 

         Copy db folder from source to destination machine at appropriate path

        Copy apps folder from source to target machine at appropriate path

      

      Copy inst folder from source to target machine at appropriate path

 8.    After completing the copy of these folders go to target machine

     

         Add database and application user having group dba

9. Give permission on Target System

     

         Change ownership of db folder from root to oraprod

         Change ownership of apps folder from root to applprod

       

         Change ownership of inst folder from root to applprod

10. Configure the Target System – database tier

            Login as oracle database user and run adcfgclone.pl from given path

          

            Provide the apps user password

        Give target system host name

        

        Provide no for RAC

      

        Provide SID

    

        Provide path of base directory

       

   

    

        Provide path of data folder, default in this case

       

        Provide path of RDMS ORACLE_HOME directory default in this case

         

        Do not preserve the display

        

        Give target system display

       

        For port value provide no if you want to use different port value from source                

        system  

Provide port pool value i.e 53

It will take some time to complete

10. Configure the Target System – application tier

          

            Login as oracle application user and run adcfgclone.pl from given path

           

            provide apps user password

        Provide host name i.e tmfbtest1.tameerbank.com in this case

      

        Provide database SID i.e. PROD

       

        Provide name of database server node i.e. tmfbtest1.tameerbank.com

      

        Provide path of database directory

      

        Provide path of ORACLE_HOME directory, default in this case

         Provide path of APPL_TOP, default in this case

      

         Provide path of COMMON_TOP directory, default in this case

      

         Provide path of instance home directory

        

         Press   Enter for default values

       Do not preserve the display, for this case

    

       Provide system display

   

      Provide no to select different port pool value from source system

      Give port pool value i.e. 53

      Select 1 for UTL_FILE _DIR

       

If status of all services is 0 it means cloning process has successfully done

       

 Set environment file for database and application users in following way

         Provide high lighted information and save the changes

          Similarly create environment file for application user

        Provide the high lighted information and save the changes

    

              Login as oracle database user 

              Use ls command to see all scripts available for this user

         Login as oracle application user 

         Use ls command to see all scripts available for this user

            Now database and application is running

            Go to internet browser and give following address to access application

            Here default port for application is 8000 and 53 is added to this value

             http://tmfbtest1.tameerbank.com:8053

Troubleshooting

Log Directories:

/opt/oraprod/db/tech_st/11.1.0/admin/PROD_r12prod/diag/rdbms/prod/PROD/trace

/opt/oraprod/inst/apps/PROD_r12prod/logs/appl/conc/log

/opt/oraprod/inst/apps/PROD_r12prod/logs/appl/conc/out

/opt/oraprod/inst/apps/PROD_r12prod/logs/ora/10.1.2/reports/cache

/opt/oraprod/inst/apps/PROD_r12prod/logs/ora/10.1.3/Apache/

/opt/oraprod/inst/apps/PROD_r12prod/appltmp

Delete core file from /opt/oraprod/inst/apps/PROD_r12prod/ora/10.1.2/forms

/oradisk/oraprod/db/tech_st/11.1.0/admin/PRODTEST_stylodb/diag/rdbms/prodtest/PRODTEST/trace/oradisk/oraprod /oradisk/oraprod/inst/apps/PRODTEST_stylodb/logs/appl/conc/log

/oradisk/oraprod/inst/apps/PRODTEST_stylodb/logs/appl/conc/out

/oradisk/oraprod/inst/apps/PRODTEST_stylodb/logs/ora/10.1.2/reports/cache

/oradisk/oraprod/inst/apps/PRODTEST_stylodb/logs/ora/10.1.3/Apache/

/oradisk/oraprod/inst/apps/PRODTEST_stylodb/appltmp

Deleting Large number of  *.log Files

find . -name "*.log" -print | xargs rm

find /Backups/ -type f -mtime +20 -exec rm {} \;

find . -name "*.req" -print | xargs rm

                                                                                                       

Removing old Logs (keep only three days old)

/opt/oraprod/db/tech_st/11.1.0/admin/PROD_r12prod/diag/rdbms/prod/PROD/trace

find  /opt/oraprod/inst/apps/PROD_r12prod/logs/appl/conc/log/ -type f -mtime +0 -exec rm {} \;

find  /opt/oraprod/inst/apps/PROD_r12prod/logs/appl/conc/out/ -type f -mtime +0 -exec rm {} \;

find  /opt/oraprod/inst/apps/PROD_r12prod/logs/ora/10.1.3/opmn/ -type f -mtime +0 -exec rm {} \;

find  /opt/oraprod/inst/apps/PROD_r12prod/logs/ora/10.1.2/reports/cache/ -type f -mtime +0 -exec rm {} \;

find /opt/oraprod/inst/apps/PROD_r12prod/logs/ora/10.1.2/forms -type f -mtime +0 -exec rm {} \;

find  /opt/oraprod/inst/apps/PROD_r12prod/logs/ora/10.1.3/Apache/ -type f -mtime +0 -exec rm {} \;

find  /opt/oraprod/inst/apps/PROD_r12prod/logs/ora/10.1.3/opmn -type f -mtime +0 -exec rm {} \;

find  /opt/oraprod/db/tech_st/11.1.0/admin/PROD_r12prod/diag/rdbms/prod/PROD/trace -type f -mtime +0 -exec rm {} \;

find  /opt/oraprod/inst/apps/TEST_stylodb/logs/appl/conc/log/ -type f -mtime +0 -exec rm {} \;

find  /opt/oraprod/inst/apps/TEST_stylodb/logs/appl/conc/out/ -type f -mtime +0 -exec rm {} \;

find  /opt/oraprod/inst/apps/TEST_stylodb/logs/ora/10.1.2/reports/cache/ -type f -mtime +0 -exec rm {} \;

find  find  /opt/oraprod/inst/apps/TEST_stylodb/logs/ora/10.1.3/Apache/ -type f -mtime +0 -exec rm {} \;

http://saadshujaatoramentary.wordpress.com/category/oracle-ebs-r12/

Cleaning up abnormal stopped (orphaned) datapumb jobs

Reason : Some time data pumb jobs marked as orphaned (stopped abnormally) and job information still remain in database. On starting datapump job start, job name table automatically created in database for keeping job related information. All data export and import operation will aborted.

Solution : Find orphaned datapumb jobs, delete metadata, start job again. Step by step cleanup orphaned datapump jobs is discussed below.

1. First check the orphaned datapump jobs status .

SELECT * FROM dba_datapump_jobs;

2. Check the state field. For orphaned jobs the state will be NOT RUNNING
3. Drop the master table (holding metadata).

DROP TABLE SYSTEM.FULLEXP1;
DROP TABLE SYSTEM.FULLEXP2;

Check job status now !

SELECT * FROM dba_datapump_jobs;

4. In this stage you did not get any orphaned jobs if the jobs have a master table. If there are still jobs listed in dba_datapump_jobs do cleanup process like below.

DECLARE
job1 NUMBER;
BEGIN
job1 := DBMS_DATAPUMP.ATTACH('EXPFULL','SYSTEM');
DBMS_DATAPUMP.STOP_JOB (job1);
END;
/
DECLARE
job2 NUMBER;
BEGIN
job2 := DBMS_DATAPUMP.ATTACH('EXPFULL','SYSTEM');
DBMS_DATAPUMP.STOP_JOB (job2);
END;
/

In my case, trace file contents as:

Trace file /oradisk/oracle/diag/rdbms/orcl/ORCL/trace/ORCL_dm00_11242.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE_HOME = /oradisk/oracle/product/11.2.0/db
System name: Linux
Node name: primarydb
Release: 2.6.18-164.el5
Version: #1 SMP Thu Sep 3 04:15:13 EDT 2009
Machine: x86_64
Instance name: ORCL
Redo thread mounted by this instance: 1
Oracle process number: 39
Unix process pid: 11242, image: oracle@primarydb (DM00)


*** 2010-08-17 10:12:02.399
*** SESSION ID:(1188.9) 2010-08-17 10:12:02.400
*** CLIENT ID:() 2010-08-17 10:12:02.400
*** SERVICE NAME:(SYS$USERS) 2010-08-17 10:12:02.400
*** MODULE NAME:(Data Pump Master) 2010-08-17 10:12:02.400
*** ACTION NAME:(EXPFULL) 2010-08-17 10:12:02.400

KUPC: Setting remote flag for this process to FALSE
prvtaqis - Enter
prvtaqis subtab_name upd
prvtaqis sys table upd
KUPP: Initialization complete for master process DM00

*** 2010-08-17 10:12:04.018
kwqberlst !retval block
kwqberlst rqan->lagno_kwqiia 6
kwqberlst rqan->lascn_kwqiia > 0 block
kwqberlst rqan->lascn_kwqiia 6
kwqberlst ascn 392980029 lascn 22

Configuraing Oracle DbConsole and SYSMAN Schema

Follow following steps for re-configuration Oracle DBConsole:

Removing OracleDBConsole[SID] Service

Go to Windows Services panel and verify that OracleDBConsole[SID] is there.
If you fnd it in the list and it can NOT be deleted by emca -x [SID] command,
you need to remove it manually.

Delete OracleDBConsole[SID] from the registry:

1. Go to Start -> Run -> type regedit.
2. Navigate to HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\OracleDBConsole[SID].
3. Delete OracleDBConsole[SID] key (hit delete).

Removing Sysman Directories

In order to recreate Sysman Schema, you will need to remove old directories:

1. Navigate to %ORACLE_HOME%\DomainName_SID directory and remove it.
2. Navigate to %ORACLE_HOME%\oc4j\j2ee\OC4J_DBConsole_DomainName_SID directory and remove it.
3. Reboot the server.
4. Verify that OracleDBConsole[SID] is not listed in Windows Services.

Creating new Sysman Schema and OracleDBConsole[SID] Service.

This presumes that Sysman Schema has been previosly dropped. If not, drop it first:

Logon SQLPLUS as user SYS or SYSTEM, and drop the sysman account and mangement objects:
a. drop user sysman cascade;
b. drop role MGMT_USER;
c. drop user MGMT_VIEW cascade;
d. drop public synonym MGMT_TARGET_BLACKOUTS;
e. drop public synonym SETEMVIEWUSERCONTEXT;

In fact, OracleDBConsole[SID] Service is created automatically as part of
creating Sysman Schema procedure:

1. Start Database Configuration Assistant.
2. Click Next on the Welcome screen.
3. Select Configure Database Options and click Next.
4. Next screen will display database name, click Next.
5. Select Eneterprise Manager Repository and click Next.
6. Select your Server Mode (default is Dedicated) and click Finish.

This process may take several minutes and it will create dropped directories and OracleDBConsole[SID] Service.
.

ORA-01502: index is in unusable state

When try to execute query on any table with select SQL statement, Oracle returns the following error:

ORA-01502: index 'indexname' or partition of such index is in unusable state

The reason is that some indexes has been marked unusable by a direct load or by a DDL operation, or shrinking space etc. Here is simple solution.

1. Drop the specified index and/or recreate the index
2. Rebuild the specified index
3. Rebuild the unusable index partition

Here we rebuilding UNUSABLE indexes onling, quering to USER_INDEXES view.

$sqlplus /nolog

Sql> connect system/oracle
Sql> SELECT count(*),status FROM all_indexes GROUP BY status;
Sql> SELECT 'alter index 'table_owner'.'index_name' rebuild online ;' FROM all_indexes where status='UNUSABLE' ORDER BY table_owner

Just execute sql statement output, it will rebuild all unused indexes.

Oracle Data Pump in Oracle Database

Oracle Database have new Data Pump utility, which is faster and more flexible as compaire to the "exp" and "imp" utilities used in old Oracle database releases. In addition to basic import and export functionality data pump provides a PL/SQL API and support for external tables. Here some simple data pump examples using expdp and impdp utility.

1. Create a DIRECTORY, Grant on DIRECTORY (useing directory name as "datadir","datadump")

Create directory at the OS Level

$ cd /oradisk
$ mkdir datadump

$ sqlplus /nolog
SQL> connect /as sysdba

SQL> DROP DIRECTORY datadump;
SQL> CREATE DIRECTORY datadump AS '/oradisk/datadump';
SQL> GRANT READ,WRITE ON DIRECTORY datadump TO HR;

Export the EMPPLOYEE table from the HR user

$ expdp hr/hr directory=datadump tables=hr.employee
SQL> Drop table employee purge;

Import the .DMP file back into the HR user.

$ impdp hr/hr directory=datadump tables=employee

Check employee table after completing import.

$ sqlplus hr/hr
Sql> select * from employee;

1. Examples: Performing a table mode export

expdp system/ TABLES=sh.costs,sh.sales DUMPFILE=datadir:table.dmp NOLOGFILE=Y

2. Examples: Performing a table mode export
expdp system/oracle SCHEMAS=sh,hr DUMPFILE=datadir:schema%U.dmp LOGFILE=datadir:expschema.log

3. Example - Performing a Parallel Full Database Exportexpdp system/oracle
FULL=Y DUMPFILE=datadir:fullexp%U.dmp, FILESIZE=2g PARALLEL=4 LOGFILE=datadir:expfull.log JOB_NAME=expfull

The FULL parameter indicates that the export is a full database mode export. All data andmetadata in the database are exported.

The PARALLEL parameter specifies the maximum number of threads of activeexecution operating on behalf of the export job. This parameter enables you to maketrade-offs between resource consumption and elapsed time. For best performance, thevalue specified for PARALLEL should be at least as large as the number of output filesspecified with the DUMPFILE parameter. Each Data Pump execution thread writesexclusively to one file at a time.

The PARALLEL parameter is valid only in the Enterprise Edition of the Oracle database.To increase or decrease the value of PARALLEL during job execution, use interactivecommandmode that is described in the example below.The FILESIZE parameter will limit the maximum size of each dump file to 2 gigabytes.

You can query DBA_DATAPUMP_JOBS to monitor the current jobs.

SQL> select * from dba_datapump_jobs;

The INCLUDE and EXCLUDE parameters can be used to limit the export/import to specific objects. When the INCLUDE parameter is used, only those objects specified by it will be included in the export. When the EXCLUDE parameter is used all objects except those specified by it will be included in the export.

expdp scott/tiger schemas=SCOTT include=TABLE:"IN ('EMP', 'DEPT')" DUMPFILE:SCOTT.dmp LOGFILE=datadump:expdpSCOTT.log
expdp scott/tiger schemas=SCOTT exclude=TABLE:"= 'BONUS'" DUMPFILE:SCOTT.dmp LOGFILE=datadump:expdpSCOTT.log