Changing National Character Set AL16UTF16 to UTF8

Changing National Character Set AL16UTF16 to UTF8 on 12c PDB

The national character set is used for data that is stored in table columns of the types NCHAR, NVARCHAR2, and NCLOB.
In contrast, the database character set is used for data stored in table columns of the types CHAR, VARCHAR2 and CLOB.
Like the database character set, the national character set is defined when the database is initially
created and can usually no longer be changed, at least not easily or without involving quite a lot of work  (export, recreate database, import).
Except when creating the database, where the national character set is defined explicitly,


You require SYSDBA authorization to change the national character set.
Changing the national character set means changing an Oracle Dictionary entry, but no data is changed.

Connect PDB as sysdba

[oracle@CDB ~]$ sqlplus sys/password@SID as sysdba

SQL> Select property_value from database_properties
     where upper(property_name) = 'NLS_NCHAR_CHARACTERSET';

SQL> Select owner, table_name, column_name
     from dba_tab_columns
     where (data_type = 'NCHAR' or data_type = 'NVARCHAR2' or data_type = 'NCLOB') and
     owner != 'SYS' and owner != 'SYSTEM';

SQL> select * from v$nls_parameters where parameter like '%CHARACTERSET%';

PARAMETER
----------------------------------------------------------------
VALUE                                                                CON_ID
---------------------------------------------------------------- ----------
NLS_CHARACTERSET
AL32UTF8                                                                  0
NLS_NCHAR_CHARACTERSET
AL16UTF16                                                                 0

SQL> select * from nls_database_parameters where parameter='NLS_NCHAR_CHARACTERSET';

PARAMETER                                                VALUE
--------------------------------------------------------------------------------
NLS_NCHAR_CHARACTERSET                   AL16UTF16



Note: If there are no table columns of the types NCHAR, NVARCHAR2 or NCLOB on the database, you can change the national character set without encountering any problems. However, if the database contains tables with NCHAR data type columns, you should perform a check to see whether these columns also contain data.

SQL> SHUTDOWN IMMEDIATE

SQL> STARTUP MOUNT

SQL> STARTUP RESTRICT;
Pluggable Database opened.

SQL> ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;

System altered.

SQL> update props$
set value$='UTF8'
where name in ('NLS_CHARACTERSET', 'NLS_NCHAR_CHARACTERSET'); 

2 rows updated.

SQL> commit;

Commit complete.

SQL> SHUTDOWN IMMEDIATE;
Pluggable Database closed.

SQL> ALTER DATABASE OPEN ;
Database altered.

SQL> select * from nls_database_parameters where parameter='NLS_NCHAR_CHARACTERSET';

PARAMETER                                VALUE
--------------------------------------------------------------------------------
NLS_NCHAR_CHARACTERSET                    UTF8

ORA-28374


When I am trying to create tablespace on pluggable databases (PDB) facing below issue.

SQL Error: ORA-28374: typed master key not found in wallet
28374. 0000 -  "typed master key not found in wallet"

*Cause:    You attempted to access encrypted tablespace or redo logs with
           a typed master key not existing in the wallet.

*Action:   Copy the correct Oracle Wallet from the instance where the tablespace
           was created.


Solution

Login with CDB and follow below steps.

[oracle@JCDB ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Mon Jun 5 15:53:48 2017
Copyright (c) 1982, 2014, Oracle.  All rights reserved.
Connected to:
Oracle Database 12c EE High Perf Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Advanced Analytics
and Real Application Testing options


SQL> show con_name;

CON_NAME
------------------------------
CDB$ROOT

SQL> administer key management set keystore close;

keystore altered.

SQL> select status, wallet_type, con_id from v$encryption_wallet;

STATUS                         WALLET_TYPE              CON_ID
------------------------------ -------------------- ----------
OPEN                           AUTOLOGIN                     0

SQL> select '!rm ' || wrl_parameter || 'cwallet.sso' from v$encryption_wallet;

'!RM'||WRL_PARAMETER||'CWALLET.SSO'
--------------------------------------------------------------------------------
!rm /u01/app/oracle/admin/CDB/tde_wallet/cwallet.sso

SQL> !rm /u01/app/oracle/admin/CDB/tde_wallet/cwallet.sso

SQL> administer key management set keystore close;

keystore altered.

SQL> select status, wallet_type, con_id from v$encryption_wallet;

STATUS                         WALLET_TYPE              CON_ID
------------------------------ -------------------- ----------
CLOSED                         UNKNOWN                       0


SQL> administer key management set keystore open identified by sys_password container=all;

keystore altered.

SQL> administer key management set key identified by sys_password with backup;

keystore altered.

SQL> administer key management set key identified by sys_password with backup container=all;

keystore altered.


Retest issue.


SQL> alter session set container=PDB;

Session altered.

SQL> show con_name

CON_NAME
------------------------------
PDB

SQL> CREATE TABLESPACE tablespace-name DATAFILE '/u02/app/oracle/oradata/CDB/data01.dbf' size 1024M;

Tablespace created.


issue resolved.

ORA-29874: warning in the execution of ODCIINDEXALTER routine

R12 E-Business Suite Autoconfig Fails On Script jtfictx.sh With Errors ORA-29874: warning in the execution of ODCIINDEXALTER routine And DRG-100595 and DRG-11000 After An Upgrade To 12.1.3 And RDBMS 12.1.0.1 (Doc ID 2223844.1) To BottomTo Bottom


APPLIES TO:

Oracle Applications DBA - Version 12.1.3 and later
Information in this document applies to any platform.
SYMPTOMS

E-Business Suite R12 Applications DBA, Upgrade Related issues

 After an upgrade from R12.0.6 to R12.1.3 including a database migration from 11.2.0.3 to 12.1.0.1,  AutoConfig fails with the following error:

ERROR
-----------------------
jtfictx.sh started at Mon Jan 16 09:00:00 2017


SQL*Plus: Release 10.1.0.5.0 - Production on Mon Jan 16 09:00:00 2017

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Enter value for 1: Enter value for 2: Enter value for 3: Connected.
DECLARE
*
ERROR at line 1:
ORA-29874: warning in the execution of ODCIINDEXALTER routine
ORA-29960: line 1,
DRG-10595: ALTER INDEX JTF_AMV_ITEMS_URL_CTX failed
DRG-11000: invalid keyword SYNC
ORA-06512: at "SYS.DBMS_SQL", line 1120
ORA-06512: at line 96


Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
jtfictx.sh exiting with status 1
ERRORCODE = 1 ERRORCODE_END
.end std out.
...
...
[AutoConfig Error Report]
The following report lists errors AutoConfig encountered during each
phase of its execution. Errors are grouped by directory and phase.
The report format is:
 

  [PROFILE PHASE]
  AutoConfig could not successfully execute the following scripts:
  Directory: /prodapps/PROD/inst/apps/UPDB8_oupapp/admin/install
  jtfictx.sh INSTE8_PRF 1


AutoConfig is exiting with status 1

STEPS
-----------------------
The issue can be reproduced at will with the following steps:
1. Upgrade R12.0.6 to R12.1.3
2. Upgrade database from 11.2.0.3 to 12.1.0.1.
3. Run AutoConfig on DB Tier and then run AutoConfig on AP Tier.



CHANGES

 Upgraded EBS from R12.0.6 to R12.1.3 and Database from 11.2.0.3 to 12.1.0.1

CAUSE

One of the mandatory patches for the 12.1.3 upgrade was not applied:

Patch 16289505 - INTEROPERABILITY PATCH FOR EBS 12.1.3 ON RDBMS 12C (12.1.0.1)

The case is discussed in the following bug:

BUG 22235648 AFTER 12C DATABASE UPGRADE ISSUE WITH AUTOCONFIG - JTFICTX.SH EXITING WITH STATUS,

Per the above bug the following SQL statement should have been replaced with call to ad_ctx_ddl.sync_index in jtfirip_ux.sql 120.4.12010000.4:

  ALTER INDEX REBUILD ON LINE PARAMETERS('sync')

This file should have been provided with patch 16289505 when upgrading DB to 12.1.0.1 as stated in the following document:
Note 1524398.1 - Interoperability Notes EBS 12.0 or 12.1 with RDBMS 12cR1


SOLUTION

To resolve the issue test the following steps in a development instance and then migrate accordingly:

1. Download Patch 16289505 along with any required prerequisite patches.

2. Apply Patch 16289505 along with any required prerequisite patches per the readme instructions.

3. Restart the instance to ensure the changes are successful.

4. Retest AutoConfig and confirm the error is resolved.

ORA-06598: insufficient INHERIT PRIVILEGES privilege

12c prevents this with this new feature:
INHERIT PRIVILEGES and INHERIT ANY PRIVILEGES Privileges.

12c introduced this new piece of functionality that initially looks a little odd but actually does make sense when you follow it through, i would imagine there are limited cases though where this specific functionality is required.

What it essentially allows only is relevant  for invokers rights procedures where you might want a certain piece of the code block to have rights that the invoker might not have and you don't want to give them that right directly - you only want it to be valid when they run that code block. This new feature allows you to do that - with me so far?

Ok lets do a quick demo, we'll create a c##test user which will own the plsql procedure and a c##rich user who we will let invoke it but we don't actually want that user to have any additional rights - just be able to run the few lines of plsql with elevated rights. Now you could argue here - why bother with invokers rights then just run with definers rights - and in the simple case below that is true - but I'm sure there are more complex other cases where this new feature is useful.

Anyway lets set a few things up - starting with the user

Re-create grants and synonyms for Apps Schema using adadmin utility

Facing below error
sqlplus -s APPS/***** @/apps/ebs/apps/apps_st/appl/ad/12.0.0/patch/115/sql/adgrnctx.sql &systempwd &un_fnd CTXSYS &un_jtf JTF

Connected.
Connected.
User altered.
declare
*
ERROR at line 1:
ORA-06598: insufficient INHERIT PRIVILEGES privilege
ORA-06512: at "SYSTEM.AD_DDL", line 165
ORA-06512: at line 160

Solution: -

12c prevents this with this new feature:
INHERIT PRIVILEGES and INHERIT ANY PRIVILEGES Privileges
But if SYS trusts x, the following can be done:

SQL> grant inherit privileges on user sys to system;
Grant succeeded.

This will allow x to inherit the privileges of SYS when running code like the above.

SQL> grant INHERIT PRIVILEGES ON USER sys to public;
Grant succeeded.

SQL> grant INHERIT PRIVILEGES ON USER system to apps;
Grant succeeded.

SQL> grant dba to system;
Grant succeeded.

SQL> grant dba to apps;

Grant succeeded.

ORA-01804

Autoconfig Failed On 12c Database With "ORA-01804: failure to initialize timezone information"
(Doc ID 1908836.1)

Oracle Applications DBA - Version 12.1.0.2 to 12.1.3 Information in this document applies to any platform.

SYMPTOMS

On Oracle Applications 11.5.10.CU2, OR 12.0.6 OR 12.1.3 version, when attempting to execute autoconfig on the database tier,

the following error occurs:

ORA-01804: failure to initialize timezone information

SP2-0152: ORACLE may not be functioning properly


The issue can be reproduced at will with the following steps:
1. Autoconfig run on the database tier.
2. The error occurs.

CHANGES

Newly upgraded 12c Database.

CAUSE

The issue is caused by 12c Timezone Specification file code was not added in following files:

ad8idbux.env
ad8idbnt.env
adlnkoh.sh

---------------------In ad8idbux.env file---------------------

if test "%s_database%" = "db112" -o "%s_database%" = "db121" ; then
     ORA_TZFILE=""
else
     ORA_TZFILE="$ORACLE_HOME/oracore/zoneinfo/timezlrg.dat"
fi
export ORA_TZFILE
---------------------In ad8idbux.env file---------------------



---------------------In ad8idbnt.env file---------------------

IF "%s_database%" == "db121" (
     set ORA_TZFILE=
)
---------------------In ad8idbnt.env file---------------------

This issue has been fixed in ad8idbux.env 120.8.12010000.3 or Higher, and in ad8idbnt.env 120.4.12010000.5 or
Higher.

SOLUTION

To implement the solution, please execute the following steps:

1. Download and review the read me and pre-requisites for Patch 16289505.

2. Ensure that you have taken a backup of your system before applying the recommended patch.

3. Apply the patch in a test environment.

4. Confirm the following file versions(database node):
       $ORACLE_HOME/appsutil/template/ad8idbux.env     120.8.12010000.3
       $ORACLE_HOME/appsutil/template/ad8idbnt.env     120.4.12010000.5
       $ORACLE_HOME/appsutil/template/adlnkoh.sh         120.16.12010000.11
You can use the commands like the following:
       strings -a $ORACLE_HOME/appsutil/template/ad8idbux.env | grep Header
       strings -a $ORACLE_HOME/appsutil/template/ad8idbnt.env | grep Header
       strings -a $ORACLE_HOME/appsutil/template/adlnkoh.sh | grep Header

5. Retest the issue.

6. Migrate the solution as appropriate to other environments.

OR try below steps also issue resolved.

Database:- 12.1.0.2 or Apps:- R12.1.3

SQL> conn / as sysdba
Connected.
SQL> SELECT version FROM v$timezone_file;

VERSION
----------
18



Symptoms

 Running AutoConfig on a database tier of E-Business Suite 12.1.3 results in the following error:
ORA-01804: failure to initialize timezone information

SP2-0152: ORACLE may not be functioning properly

Cause

 This issue is caused by the current AutoConfig template file that points to the wrong file.

Solution


1. This is resolved by editing the template ad8idbux.env in location .../oracle/db/tech_st/11.2.0/appsutil/template changing:

From:
set ORA_TZFILE=%s_db_oh%\oracore\zoneinfo\timezlrg.dat

To:
set ORA_TZFILE=%s_db_oh%\oracore\zoneinfo\timezlrg_18.dat

2. Running AutoConfig after this change means the environment file is created with the ORA_TZFILE value as timezlrg_14.dat and AutoConfig completes successfully.


Issue resolved.

UnsatisfiedLinkError exception loading native library: njni11

When running Autoconfig for Oracle database 12.1.0.2.0 with R12.1.3 i get the 

error:
UnsatisfiedLinkError exception loading native library: njni11
java.lang.UnsatisfiedLinkError: /lib/libnjni11.so: ld.so.1: java: fatal: /lib/libnjni11.so: wrong ELF class: ELFCLASS64 (Possible cause: architecture word width mismatch)
/lib/libnjni11.so: wrong ELF class: ELFCLASS64 (Possible cause: architecture word width mismatch)


Solution 

1. Apply patch latest TXK patch. For R12.1.3 Patch 7651166 

2. Migrate the code from the APPS-Tier to the DB-Tier via the appsutil.zip 

3. Check that appsutil has all the required subdirectories (for example scripts, if not copy them from the old appsutil!) 

4. Create the Database Context File again
perl $ORACLE_HOME/appsutil/bin/adbldxml.pl
template=$ORACLE_HOME/appsutil/template/adxdbctx.tmp
out=/u01/clone/db/tech_st/11.2.0/appsutil/CLONE_ebs6.xml

5. Run Database Autoconfig

Oracle Support says that ORACLE_HOME 11.2 does not contain 32-bit libraries, but Autoconfig still uses a 32-bit Java version. You must use 64-bit Java. 

ORA-08004: sequence IEX_DEL_BUFFERS_S.NEXTVAL exceeds MAXVALUE

 Error:- IEX: Scoring Engine Harness Error - ORA-08004: sequence IEX_DEL_BUFFERS_S.NEXTVAL exceeds MAXVALUE (Doc ID 2056754.1) SYMPTOMS:- Yo...