ORA-39346: data loss in character set conversion for object TABLE_STATISTICS

When doing export/import using data pump encountered below error. This happens when invalid or corrupt characters are stored in the database.

When using export/import instead of data pump, no errors reported.

Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
ORA-39346: data loss in character set conversion for object TABLE_STATISTICS:"ORCL"."TABLE1"
ORA-39346: data loss in character set conversion for object TABLE_STATISTICS:"ORCL"."TABLEMAT"
Job "SYS"."SYS_IMPORT_SCHEMA_01" completed with 2 error(s) at Sun Apr 11 12:05:25 2021 elapsed 0 00:20:13

After research found that this is a known issue due to Bug 13696958 .

The fix for Bug 13696958 

added warning messages in 12.1 datapump import for characters that couldn't be converted. Previously, a fix for Bug 16173664 was made available to report the affected object in the import log.

Workaround/fix:-

Needs to apply interim patch 21342624 
and execute post-install step:

$ datapatch

If the patch 21342624 installed then impdp log will report the objects that are experiencing data loss during characterset conversion. If the fix then character data is checked even when the client and database character sets are the same, to prevent input of invalid character data.

That being said the fix for Bug 21342624 does not stop the error messages or repair any bad data on the source database

In case of any further issues , patch questions or concerns, please contact Oracle Support.

Refer Oracle support notes: -

Unpublished Bug 21342624 - DATA LOSS DUE TO CHARACTER SET NOT SHOWING CORRECT SET OF OBJECTS DURING IMPORT.

Note 1317012.1 - How To Use MOS Patch Planner To Check And Request The Conflict Patches?

Thanks & Regards,
http://oracleracexpert.com, Oracle ACE

How to reset SYS, SYSTEM or User password or grant SYSDBA, SYSOPER privilege

This article helps to reset SYS, SYSTEM or any other user password and also grant admin privileages. There are different methods to rest the password in Oracle.

As SYSDBA or DBA privilege - As long as you have SYSDBA or DBA privilege you should be able to reset any user password.

$ sqlplus / as sysdba
SQL> alter user <username> identified by <new password>;
For ex:- alter user sys identified by xxxaaxxx;
For ex: - alter user EMP identified by xxssxxxx;

Orapwd Utility – This is very useful utility to rest the password. This utility helps in granting SYSOPER, SYSDBA privileges. Creating orapwd enables users to connect with admin privileges. From Oracle 11g the passwords are case sensitive.

orapwd file=orapwd<SID> password=password entries=max_users

In order Oracle to check for password file the REMOTE_LOGIN_PASSWORDFILE must be set to either SHARED or EXCLUSIVE

SHARED - More than one database can use a password file. But only SYS user recognized by the password file
EXCLUSIVE - The password file can be used by only one database and the password file can contain names other than SYS.

SQL> alter system set remote_login_passwordfile =’EXCLUSIVE’;
SQL> show parameter password
NAME TYPE VALUE
----------------------------- ----------- ----------
remote_login_passwordfile string EXCLUSIVE

You can also grant SYSDBA, SYSOPER privileges to user by logging as SYSDBA. Once the privilege is granted, the users will be added to password file

SQL> grant sysdba to AdminUser;
SQL> grant sysoper to DBAUser;

To see who are all the users has sysdba, sysoper privilege run below command. You can also see the users listed in password file

SQL> select * from v$pwfile_users;

Oracle has listed several articles how to recover LOST password and resolve issues connecting as YSSDBA.

 
How to Set up the Oracle Password File MOSC Note: 1029539.6
Checklist for Resolving Connect AS SYSDBA Issues MOSC Note: 69642.1
How To Recover From Lost SYS Password MOSC Note: 805084.1

Thanks & Regards,

http://oracleracexpert.com