NLS_CHARACTERSET, AL16UTF16 to AL32UTF8 oracle 11.2.0.3

alter database character set AL32UTF8;

alter system set NLS_NCHAR_CHARACTERSET=AL32UTF32

ALTER DATABASE CHARACTER SET AL32UTF8

 col VALUE format a10

SELECT parameter , value FROM NLS_DATABASE_PARAMETERS WHERE parameter like '%CHARACTERSET%'
/

SQL>   2
PARAMETER                      VALUE
------------------------------ ----------------------------------------
NLS_CHARACTERSET               WE8MSWIN1252
NLS_NCHAR_CHARACTERSET         AL16UTF16


SQL> show parameter chara

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
nls_numeric_characters               string


PARAMETER                      VALUE
------------------------------ ----------
NLS_CHARACTERSET               AL32UTF8
NLS_NCHAR_CHARACTERSET         AL16UTF16

(461) $ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Tue Jul 10 20:35:31 2012

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> conn csmig/csmig
ERROR:
ORA-28000: the account is locked


Warning: You are no longer connected to ORACLE.
SQL> conn / as sysdba
Connected.
SQL> alter user csmig account unlock;

User altered.



QL> conn csmig/csmig
ERROR:
ORA-28001: the password has expired


Changing password for csmig
New password:
Retype new password:
ERROR:
ORA-01045: user CSMIG lacks CREATE SESSION privilege; logon denied


Password changed
Warning: You are no longer connected to ORACLE.
SQL> connect /as sysdba
Connected.
SQL> grant create session to csmig;

Grant succeeded.

SQL> conn csmig/csmig
Connected.


SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

(485) $ csscan TABLE='(SYS.SQL_VERSION$)' FROMCHAR=AL16UTF16 TOCHAR=AL32UTF8 LOG=instchkc CAPTURE=N PROCESS=1 ARRAY=1024000


Character Set Scanner v2.2 : Release 11.2.0.3.0 - Production on Tue Jul 10 20:59:08 2012

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.


Username: / as sysdba

Password:

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Enumerating table to scan...

. process 1 scanning SYS.SQL_VERSION$[AAAACVAABAAAAGYAAA]

Creating Database Scan Summary Report...

Creating Individual Exception Report...

Scanner terminated successfully.

(486) $

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area 6415736832 bytes
Fixed Size                  2217192 bytes
Variable Size            1077938968 bytes
Database Buffers         5330960384 bytes
Redo Buffers                4620288 bytes
Database mounted.
SQL> ALTER SYSTEM ENABLE RESTRICTED SESSION;

System altered.

SQL> ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;

System altered.

SQL> ALTER SYSTEM SET AQ_TM_PROCESSES=0;

System altered.

SQL> ALTER DATABASE OPEN;

Database altered.

SQL> ALTER DATABASE CHARACTER SET INTERNAL_USE AL32UTF8;

Database altered.

SQL> SHUTDOWN IMMEDIATE;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> STARTUP;
ORACLE instance started.

Total System Global Area 6415736832 bytes
Fixed Size                  2217192 bytes
Variable Size            1077938968 bytes
Database Buffers         5330960384 bytes
Redo Buffers                4620288 bytes
Database mounted.
Database opened.
SQL> SELECT parameter , value FROM NLS_DATABASE_PARAMETERS WHERE parameter like '%CHARACTERSET%'
/  2

PARAMETER
------------------------------
VALUE
--------------------------------------------------------------------------------
NLS_CHARACTERSET
AL32UTF8

NLS_NCHAR_CHARACTERSET
AL16UTF16


SQL> col VALUE format a10
SQL> /

PARAMETER                      VALUE
------------------------------ ----------
NLS_CHARACTERSET               AL32UTF8
NLS_NCHAR_CHARACTERSET         AL16UTF16

SQL>

Oracle XDB Install/DeInstall

1. Deinstall XDB:

SQL> spool xdb_removal.log
SQL> connect / as sysdba
SQL> set echo on;
SQL> shutdown immediate;
SQL> startup
SQL> @?/rdbms/admin/catnoqm.sql
SQL> spool off;


2. Reinstall XDB -- Specify a default tablespace that is using ASSM such as SYSAUX:

select tablespace_name, extent_management, segment_space_management
from dba_tablespaces
where tablespace_name in ('SYSTEM', 'SYSAUX');

TABLESPACE_NAME      EXTENT_MANAGEMENT  SEGMENT_SPACE_MANAGEMENT
--------------------------------- ------------------------------------ -------------------------------------------------
SYSAUX                           LOCAL                                AUTO
SYSTEM                           LOCAL                                MANUAL

## IMPORTANT: You must shutdown and restart the database between removal and reinstall ##

SQL> spool xdb_install.log
SQL> connect / as sysdba
SQL> set echo on;
SQL> shutdown immediate;
SQL> startup;
SQL> @?/rdbms/admin/catqm.sql xdb sysaux temp yes
SQL> spool off


3. Run utlrp.sql to validate any remaining invalid objects and verify the XDB installation:

spool xdb_status.txt
connect / as sysdba
set echo on;
set pagesize 10000
col comp_name format a36
col version format a12
col status format a8
col owner format a12
col object_name format a35
col name format a25

SQL> @?/rdbms/admin/utlrp.sql

select comp_name, version, status
from dba_registry;

select owner, object_name, object_type, status
from dba_objects
where status = 'INVALID'
and owner in = 'XDB';

spool off;