Resolving ORA-02374: conversion error loading table during impdp

Iam trying to do an import of a table into 11.2.0.2 database and facing the below mentioned error:

ORA-02374: conversion error loading table "MINE_USR"."POPUPS_PROJECT_BIN"

ORA-12899: value too large for column MINE_TEXT (actual: 66, maximum: 64)

ORA-02372: data for row: MINE_TEXT : 0X'45737061C3B16F6C2020202020202020202020202020202020'

Details:

1.        Used expdp and created a dump for the table: MINE_USR. POPUPS_PROJECT_BIN

2.        While doing a impdp on the target database :

ORA-02374: conversion error loading table "MINE_USR"."POPUPS_PROJECT_BIN"

ORA-12899: value too large for column MINE_TEXT (actual: 66, maximum: 64)

ORA-02372: data for row: MINE_TEXT : 0X'45737061C3B16F6C2020202020202020202020202020202020'

Solution:

When you describe the table, you will see that the column MINE_TEXT is char(64):

SQL> desc MINE_USR.POPUPS_PROJECT_BIN

 Name                                      Null?    Type

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

 MINE_IDX                                NOT NULL NUMBER(5)

 MINE_IDX                                 NOT NULL NUMBER(5)

 MINE_COLUMN                                     NUMBER(5)

 MINE_VALUE                                      NUMBER(5)

 MINE_TEXT                                         CHAR(64)

Execute the below to modify the column of the table:

SQL> alter table MINE_USR.POPUPS_PROJECT_BIN modify MINE_TEXT CHAR(66);

Table altered.

Now perform the import using the below command:

impdp directory=DATA_PUMP_DIR dumpfile=MINE_USR.dmp logfile=MINE_USR.log tables=MINE_USR.POPUPS_PROJECT_BIN table_exists_action=truncate

Change password using AFPASSWD utility in Oracle R12.1

AFPASSWD utlity in R12.1 is enhanced version of FNDCPASS having the below mentioned good and new  features:

1. Run it from any node : may it be database node or Mid-tier.

2. It will prompt you twice to enter the new password ..just to confirm .

We can still continue using FNDCPASS and mandatorily use FNDCPASS for migrating all oracle application user passwords to a non-reversible hash password scheme ( Note id : 457166.1).

How to use AFPASSWD:

AFPASSWD [-c <APPSUSER>[@<TWO_TASK>]] [-f <FNDUSER>]

AFPASSWD [-c <APPSUSER>[@<TWO_TASK>]] [-o <DBUSER>]

AFPASSWD [-c <APPSUSER>[@<TWO_TASK>]] [-a]

AFPASSWD [-c <APPSUSER>[@<TWO_TASK>]] [-l <ORACLEUSER> [<TRUE>] |

[<FALSE>]]

AFPASSWD [-c <APPSUSER>[@<TWO_TASK>]] [-L [<TRUE>] | [<FALSE>]]

AFPASSWD [-c <APPSUSER>[@<TWO_TASK>]] [-s] <APPLSYS>

• -c {APPSUSER}[@{TWO_TASK}] - Specifies the connection string to use, the Applications user, and/or the value of TWO_TASK. This option can be use in combination with others. If it is not specified, default values from the environment will be used.

Note: The password will be prompted for, and is not to be provided in the connection string.

• -f {FNDUSER} - Changes the password for an Applications user. A username that contains spaces must be enclosed in double quotation marks; for example, "SANDESH ACHAR".

• -o {DBUSER} - Changes the password for an Oracle E-Business Suite database user.

Note: This only applies to users listed in the FND_ORACLE_USERID table, not database users in general.

• -a - Changes all Oracle (ALLORACLE) passwords (except the passwords of APPS, APPLSYS, APPLSYSPUB) to the same password, in the same way as the ALLORACLE mode does in FNDCPASS.

• -l - Locks individual {ORACLE_USER} users (except required schemas). {TRUE} = LOCK, {FALSE} = UNLOCK.

• -L - Locks all Oracle (ALLORACLE) users (except required schemas). {TRUE} = LOCK, {FALSE} = UNLOCK.

• -s {APPLSYS} - Changes the password for the APPLSYS user and the APPS user.

This requires the execution of AutoConfig on all tiers.

• -h - Displays help

Script to Monitor Concurrent requests average run time

Do you need a daily  report which would give an average run time of all concurrent requests in your environment?

So you would know which concurrent request is taking longer time and on which day:
The below script gives an average run time for all concurrent requests for the current day, previous day, day before yesterday and for the whole week:



set pagesize 500
set echo off
set feedback off
set linesize 200
col USER_CONCURRENT_PROGRAM_NAME for a75
col wkly_Time Heading 'Weekly Run | Time | Avg |(In Minutes) '
col dbydy_Tim Heading 'Day Before|Yesterday | Run Time | Avg |(In Minutes) '
col ydy_Tim Heading 'Yesterday | Run Time | Avg |(In Minutes) '
col tdy_Tim Heading 'Today | Run Time | Avg |(In Minutes) '
col tdy_Tim Heading &_DATE
select  wkly.user_concurrent_program_name, wkly.wkly_time , to_char(nvl(dbydy.dbydy_time,'     Not Run'))  Dbydy_tim,to_char(nvl(ydy.ydy_time,'     Not Run'))  ydy_tim,to_char(nvl(tdy.tdy_time,'     Not Run'))  tdy_tim
FROM
(SELECT user_concurrent_program_name,
to_char(avg(round((actual_completion_date- actual_start_date)*24*60)),'99999999.99') wkly_time
FROM apps.fnd_conc_req_summary_v
WHERE nvl(actual_start_date,sysdate) >= (sysdate-7)
AND phase_code='C' and status_code  in ('C','G')
group by user_concurrent_program_name) wkly,
(SELECT user_concurrent_program_name,
to_char(avg(round((actual_completion_date- actual_start_date)*24*60)),'99999999.99') dbydy_time
FROM apps.fnd_conc_req_summary_v
WHERE trunc(nvl(actual_start_date,sysdate)) = trunc((sysdate-2))
AND phase_code='C' and status_code  in ('C','G')
group by user_concurrent_program_name ) dbydy,
(SELECT user_concurrent_program_name,
to_char(avg(round((actual_completion_date- actual_start_date)*24*60)),'99999999.99') ydy_time
FROM apps.fnd_conc_req_summary_v
WHERE trunc(nvl(actual_start_date,sysdate)) = trunc((sysdate-1))
AND phase_code='C' and status_code  in ('C','G')
group by user_concurrent_program_name ) ydy,
(SELECT user_concurrent_program_name,
to_char(avg(round((actual_completion_date- actual_start_date)*24*60)),'99999999.99') tdy_time
FROM apps.fnd_conc_req_summary_v
WHERE trunc(nvl(actual_start_date,sysdate)) = trunc((sysdate))
AND phase_code='C' and status_code  in ('C','G')
group by user_concurrent_program_name ) tdy
WHERE wkly.user_concurrent_program_name =dbydy.user_concurrent_program_name (+)
AND   dbydy.user_concurrent_program_name = ydy.user_concurrent_program_name (+)
AND   ydy.user_concurrent_program_name  = tdy.user_concurrent_program_name (+)
order by wkly_time desc

Log and Sql trace in MWA

The mwa.LogLevel can have the following values:

-          Fatal, Error, Warning, Debug, and Trace.

-          Trace is the least restrictive: it will log all messages. 

-          Fatal is the most restrictive: it will display only messages from fatal errors.

-          Trace is the setting normally suggested when debugging a problem on mobile.

How to set up mwa trace:

To enable logging you set "mwa.LogLevel = TRACE" in the mwa.cfg file then restart the mobile server.

Steps:

1.       Cd $MWA_TOP/secure

2.       Change the below mentioned entries in mwa.cfg:

Change mwa.LogLevel=TRACE

3.       Restart the MWA server.

4.       Perform the transaction.

5.       [port].INV.log
[port].system.log  are the logs which will capture the transaction logs in the below mentioned location:

Reference: Metalink: Doc ID 338291.1

How to setup sql trace file for MWA:

How It Works
If the user sets the parameter mwa.LogLevel = performance in configuration file (mwa.cfg), the mobile application will write a SQL*Trace file for all users connecting to the mobile server. All user connections will be written to the same SQL*Trace file usually found in the server side UDUMP directory in the same location as the other trace files.

Steps:

1.       Cd $MWA_TOP/secure

2.       Change the below mentioned entries in mwa.cfg:

Change mwa.LogLevel=performance

3.       Restart the MWA server.

4.       Perform the related navigation steps to cause the issue.

5.       You can search in the trace directories for files created. Or you can try to locate the particular file using SQL like the following to help. Query to identify the trace file name with the corresponding number from the username.

select prs.spid
from v$session ses , v$process prs
where ses.program = 'JDBC Thin Client'
and ses.client_info is not null
and ses.osuser = '&OS_USER_NAME'
and ses.paddr = prs.addr
order by ses.logon_time desc ;

6.       Use the column "prs.spid" which maps to the filename in udump location. For example, if the column has the value 18027, then the file name could be  "ora_18027.trc".

7.        Create a TKPROF file and upload both the raw trace (.trc) and TKPROF.

8.        After generating the trace file, ensure that you disable SQL*Trace by stopping the mobile server, resetting the parameter in mwa.cfg, and restarting the mobile server.

Reference Metalink Doc ID 277655.1

Autoconfig Error after clone in 12.1.3 Instance

While running autoconfig on a cloned instance , the below mentioned error occurred:


PL/SQL procedure successfully completed.

declare
*
ERROR at line 1:
ORA-00001: unique constraint (APPLSYS.AD_APPL_TOPS_U2) violated
ORA-06512: at line 32


Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
adadmprf.sh exiting with status 1
ERRORCODE = 1 ERRORCODE_END
.end std out.

.end err out.

AutoConfig could not successfully execute the following scripts:
    Directory: /u01/apps/mine/inst/apps/test_mine/admin/install
      adadmprf.sh             INSTE8_PRF         1 

Solution:

1. select applications_system_name from applsys.fnd_product_groups; 

This will return the source instance sid.

2. Update the entry with target instance name:

 update applsys.fnd_product_groups set applications_system_name = '<target instance>'; 

3. Run autoconfig again. 

FRM-92101 There was a failure in forms server during startup: Error after cloning in 12.1.3 Instance

After Performing Clone from PRODUCTION to TEST : Database 11.2.0.3 and Apps 12.1.3 , while opening forms the above error occurred.


Solution:

1. $ cd $ORACLE_HOME/lib32   >>>>>>This is 10.1.2 OH

$ rm ldflags
$ ln -s $ORACLE_HOME/lib/ldflags ldflags

Then, stop the web tier services and relink the forms executable(s):

$ cd $ORACLE_HOME/forms/lib32/
$ make -f ins_forms.mk install

2. Start all Services and retest.

HCC in Exadata Database 11g

Oracle’s Hybrid Columnar Compression technology is a new method for organizing data within a database block. Storing  data in a ‘columnar’ format, where data is organized and stored by column. Otherwise row format is followed to store where all column data for a particular row is stored sequentially within a single database block.

Hybrid Columnar Compression: HCC  is a compression option available only for tables residing on Exadata Storage. 

Apart from Exadata storage, Axiom, the SPARC Super Cluster and the Sun ZFS Storage Appliance have the same technology. 

This won’t work on any other system as seen below:

SQL> alter table mine move compress for query high;

alter table mine move compress for query high

            *

ERROR at line 1:

ORA-64307: hybrid columnar compression is not supported for table spaces on this storage type

HIGH and LOW are the Options available in the above querry.

The above query was run on a non-exadata storage system.

HCC re-organizes data into Compression Units (CU). It comes in two forms; 

Warehouse Compression (add COMPRESS FOR QUERY to your table definition script)

 and Archive Compression (add COMPRESS FOR ARCHIVE to your table script).

Physical IO’s are reduced hence enhancing performance. 

adoafmctl.sh fails after 12.1.3 Upgrade

Recently performed an Upgrade of :

1.Database from 11.2.0.2 to 11.2.0.3 and
2. Oracle R12 from 12.1.2 to 12.1.3.


While trying to start all services, adoafmctl.sh fails with below error :


Starting OPMN managed OAFM OC4J instance ...

adoafmctl.sh: exiting with status 152


And the log file says:

Error
--> Process (index=1,uid=457048498,pid=14483554)
time out while waiting for a managed process to start



Solution:

1. rm $ORA_CONFIG_HOME/10.1.3/opmn/logs/states/* 

Clear the TLD cache:

- stop all middle tier services

-Make sure there are not background unix processes 'ps -ef|grep applmgr'

- Delete/backup the file:

$COMMON_TOP/_TldCache

2. delete all .lock files from the below directory subtrees:

$INST_TOP/ora/10.1.3/j2ee/forms/persistence/forms_default_group_1/

$INST_TOP/ora/10.1.3/j2ee/oacore/persistence/oacore_default_group_1/

$INST_TOP/ora/10.1.3/j2ee/oafm/persistence/oafm_default_group_1/

Then Start all services.

Verify and change oc4jadmin password in Oracle R12

oc4jadmin password could become an issue when installing 10.1.3.5 Techstack software in Oracle 12.1.3 EBS. Its important to know that you are feeding the right oc4jadmin password.


How to verify:

Run the below command as applmgr user :


appmine> java -jar $IAS_ORACLE_HOME/j2ee/home/jazn.jar -checkpasswd jazn.com oc4jadmin -pw *****
Successful verification of user/password pair.

***** = oc4jadmin password


The message should be successful. If not the password can be changed in system-jazn-data.xml file:


1. Shutdown all opmn services :

cd $ADMIN_SCRIPTS_HOME
adstpall.sh / adopmnctl.sh stopall

2. cd $ORACLE_HOME/j2ee/home/config

Here ORACLE_HOME= Oracle Application server home, ie, 10.1.3 HOME
Take a backup of system-jazn-data.xml and edit the below content:



<user>
<name>oc4jadmin</name>
<display-name>OC4J Administrator</display-name>
<guid>93E5A2505D1511DEBF8E89BC12E10097</guid>
<description>OC4J Administrator</description>
<credentials>!newpassword</credentials>
</user>



Marked in bold is the details to be changed and that is the password. Note an ! mark has to prefixed with the password.


3. Start all opmn services.


Whenever autoconfig is run after this practice, the password changes to encrypted format.

How to Enable Write-Back Flash Cache in 11.2.3.2.0

Write-back Flash Cache= WBFC

By default the flash cache mode in 11.2.3.2.0 is write-through.

 The following mentioned steps would change the mode from write through to write-back.

All the changes would be executed using cellcli.

How to enable write-back flash cache (WBFC):

-      We will have to drop the existing Write-Through flash cache mode before doing any changes.

For example:

CellCLI> drop flashcache

-       Please Shut down the CellSrv by using the below command:

CellCLI> alter cell shutdown services cellsrv

-      Now we are ready to change the flash cache mode to Write-Back using the below command:

CellCLI> alter cell flashcacheMode = writeback

-       Please Start up CellSrv :

CellCLI> alter cell startup services cellsrv

-       The Flash cache needs a recreation for effect to take place. The below command would do the change:

CellCLI> create flashcache all 

How to rollback a patch using patchmgr in EXADATA

  

Can we rollback a patch applied using the utility patchmgr?

Yes we can, however only those patches can be rolled back which has successfully patched the EXADATA cells. 

Cells with incomplete/Failed patching cant be rolled back.

How to do:

1. Check the prerequisites using the below command:

./patchmgr -cells cell_group -rollback_check_prereq [-rolling]

2. Perform the rollback using the following command:

./patchmgr -cells cell_group -rollback [-rolling]

Change lock time in Exadata

In image 11.2.3.1.1, when entered a wrong password, we will have to wait 10 minutes before retry. How to change/remove this configuration?

Solution: 

1. Change the lock time in /etc/pam.d/sshd.

Log in as root and change the lock_time parameter in sshd file:

For example:

[root@mine-admin ~]# cat /etc/pam.d/sshd
#%PAM-1.0
auth       include      system-auth
auth       required     pam_tally2.so deny=50 onerr=fail lock_time=1 <Seconds>
.
.

2.  Please restart ssh for the change to take effect.

For example:

service sshd restart

Note: There might be a need to  change /etc/pam.d/login for KVM logins.

One system and Two system upgrade in EM 12c

One System Upgrade:

•           This approach is simpler.
•           This approach assumes the same host as the prior version of operation.
•           The repository components are upgraded in the same repository database.
•           This approach is suitable, when the number of agents is less.
•           Requires downtime.
•           OMA , OMS and Repository database are upgraded at once.

Two System Upgrade:

•  This approach is useful in more complex monitoring environments, where:
•   A different host from the prior OMS version of operation will be used.
•  The repository components will be upgraded in a different repository database copy.
•  Best suited for large agent counts and anywhere the unmonitored time must be minimized.
•   Minimal Downtime
•   The existing reports, metrics and jobs are migrated as well.