How to solve ORA-28017: The password file is in the legacy format

SQL> alter user sys identified by iossS1Qwmk_kKfGHqs0UVu93xxswQ;
alter user sys identified by iossS1Qwmk_kKfGHqs0UVu93xxswQ
*
ERROR at line 1:
ORA-28017: The password file is in the legacy format.

Cause:

#oerr ora 28017
28017, 00000, "The password file is in the legacy format."
// *Cause:    There are multiple possibilities for the cause of the error:
//
//              * An attempt was made to grant SYSBACKUP, SYSDG or SYSKM.
//              * These administrative privileges could not be granted unless
//                the password file used a newer format ("12" or higher).
//              * An attempt was made to grant a privilege to a user who
//                has a large password hash which cannot be stored in
//                the password file unless the password file uses a newer
//                format ("12" or higher).
//              * An attempt was made to grant or revoke a common administrative
//                privilege in a CDB
// *Action:   Regenerate the password file in the newer format ("12" or higher).
//            Use the newer password file format ("12" or higher) if you need to
//            grant a user the SYSBACKUP, SYSDG or SYSKM administrative
//            privileges, or if you need to grant a privilege to a user
//            who has a large password hash value.

Cause: the password file is of the wrong format. You typically see this error after a migration from 11g to 12c.

Solution: regenerate the password file.

First, check the users that are affected by the change:

SQL> select * from v$pwfile_users;

USERNAME                       SYSDB SYSOP SYSAS SYSBA SYSDG SYSKM     CON_ID
------------------------------ ----- ----- ----- ----- ----- ----- ----------
SYS                            TRUE  TRUE  FALSE FALSE FALSE FALSE          0
DBAMON                         TRUE  FALSE FALSE FALSE FALSE FALSE          0

In my case, two users are affected: SYS and DBAMON.


Regenerate the password file:

# orapwd file=$ORACLE_HOME/dbs/orapwproddb01 entries=5 force=y

Enter password for SYS:

Check the password file users again. SYS was added as a result of the recreation of the password file:

SQL> select * from v$pwfile_users;

USERNAME                       SYSDB SYSOP SYSAS SYSBA SYSDG SYSKM     CON_ID
------------------------------ ----- ----- ----- ----- ----- ----- ----------
SYS                            TRUE  TRUE  FALSE FALSE FALSE FALSE          0

To add DBAMON as a sysdba user, grant the SYSDBA privilege to the account:

SQL> grant sysdba to DBAMON;

Grant succeeded.

Check again, and DBAMON is now registered as a privileged user in the password file:

SQL> select * from v$pwfile_users;

USERNAME                       SYSDB SYSOP SYSAS SYSBA SYSDG SYSKM     CON_ID
------------------------------ ----- ----- ----- ----- ----- ----- ----------
SYS                            TRUE  TRUE  FALSE FALSE FALSE FALSE          0
DBAMON                         TRUE  FALSE FALSE FALSE FALSE FALSE          0

Simple sed-command for exchanging a string in multiple files

sed -i "s/oldstring/newstring/g" 

For example

sed -i "s/11204/12102/g" /etc/oratab

MS word characters that have no representation in WE8ISO8859P1 and WE8ISO8859P15

During a globalization effort, I found the following interesting information about the difference between the WE8MSWIN1252 and the WE8ISO8859P15 character set

* 27 codepoints are NOT defined/used in WE8ISO8859P15 but are filled in / used in WE8MSWIN1252

(note that the WE8MSWIN1252 codepoints

* 91 = U+2018 : LEFT SINGLE QUOTATION MARK
* 92 = U+2019 : RIGHT SINGLE QUOTATION MARK
* 93 = U+201C : LEFT DOUBLE QUOTATION MARK
* 94 = U+201D : RIGHT DOUBLE QUOTATION MARK

are the default quotation marks of the Microsoft Word product, so if you have data that comes from Microsoft Office products you *need* a WE8MSWIN1252 database characterset.

Make sure you clients NLS_LANG is also correct: WE8ISO8859P15 is *not* correct as NLS_LANG for windows clients
Note 179133.1 The correct NLS_LANG in a Windows Environment


A more common problem is that in an environment using English and West European or Latin American ( French, Spanish, Portuguese, Dutch, Italian,...) windows clients. a lot of setups use a NLS_LANG set to WE8ISO8859P15 on the client side. For windows systems this is not correct and provokes in most cases that there are actually WE8MSWIN1252 codes stored in the WE8ISO8859P15 database. The most commonly seen characters are the € symbol and these qoutes: ‘’“” - these are the 1252 "smart qoutes" used in Microsoft Office. They look similar to the "normal" US7ASCII qoute " in most fonts, but are different characters often and result in confusion. The Courrier New font for example distinct them quite good visibly.


So watch out for cut-n-paste errors based on MS Word documents! They often result in characters that have no representation under the most commonly used non-Unicode character set.

What are the two options XDK and XML in DBA_REGISTRY anyway?

What are the two options XDK and XML in DBA_REGISTRY anyway?

You see them amongst other components in the DBA_REGISTRY view:

select comp_id,comp_name, version,status from dba_registry;

COMP_ID       COMP_NAME          VERSION        STATUS
-------------------- ---------------------------------------- ------------------------------ --------------------
XDB       Oracle XML Database        12.1.0.2.0       VALID
XML       Oracle XDK          12.1.0.2.0       VALID

So what are they, and how are they connected?

The XDK documentation states:

"Oracle XML Developer's Kit (XDK) is a versatile set of components that enables you to build and deploy C, C++, and Java software programs that process Extensible Markup Language (XML)."

In other words, XDK is a toolbox for developers of XML-driven applications.

The XML DB documentation states:
"Oracle XML DB is a set of Oracle Database technologies related to high-performance handling of XML data...Oracle XML DB and the XMLType abstract data type make Oracle Database XML-aware. Storing XML data as an XMLType column or table lets the database perform XML-specific operations on the content."

In other words, XML DB is the framework for allowing XML to be stored and retrieved in the database. It has been a part of the Oracle RDBMS since version 9.2.

The XDK

"supports Oracle XML DB, which is a set of technologies used for storing and processing XML in Oracle Database."

With XDK and XML DB you can

"build applications that run in Oracle Database. You can also use XDK independently of Oracle XML DB.
XDK is fully supported by Oracle and comes with a commercial redistribution license. The standard installation of Oracle Database includes XDK."

How to remove a non-used option marked as INVALID in dba_registry

If you have invalid components in your database table dba_registry, and those options are not used, you should mark them as removed to avoid misunderstandings.

set lines 200
col comp_name format a50
col version format a20
col status format a20
col parameter format a30
col value format a30
select comp_id, comp_name,version,status
from dba_registry;


COMP_ID                        COMP_NAME                                          VERSION              STATUS
------------------------------ -------------------------------------------------- -------------------- --------------------
RAC                            Oracle Real Application Clusters                   11.2.0.4.0           INVALID

16 rows selected.

Verify with v$option:

select value from v$option where parameter = 'Real Application Clusters';

VALUE
------
FALSE

To remove, execute

 exec dbms_registry.removed('RAC');

Registry is now updated:

COMP_ID                        COMP_NAME                                          VERSION              STATUS
------------------------------ -------------------------------------------------- -------------------- --------------------
RAC                            Oracle Real Application Clusters                   11.2.0.4.0           REMOVED

16 rows selected.

How to fix error ORA-04063: package body "OLAPSYS.CWM2_OLAP_METADATA_REFRESH" has errors when installing OLAP into a 11g database

You are installing OLAP.
You receive the following error at the end of the olap.sql script:

SQL> @?/olap/admin/olap.sql SYSAUX TEMP;

SQL> execute cwm2_olap_metadata_refresh.mr_refresh;
BEGIN cwm2_olap_metadata_refresh.mr_refresh; END;

*
ERROR at line 1:
ORA-04063: package body "OLAPSYS.CWM2_OLAP_METADATA_REFRESH" has errors
ORA-06508: PL/SQL: could not find program unit being called:
"OLAPSYS.CWM2_OLAP_METADATA_REFRESH"
ORA-06512: at line 1

Solution:
Drop the OLAPSYS user and rerun the script:

$ sqlplus '/ as sysdba'
SQL> drop user OLAPSYS cascade
SQL> @?/olap/admin/olap.sql SYSAUX TEMP;
SQL> @?/rdbms/admin/utlrp.sql

Sources: Doc ID 726253.1 "Errors: ORA-04063, ORA-06508, ORA-06512 When Adding OLAP Using the Script 'olap.sql SYSAUX TEMP;'"

How to add a line feed to your spool file

If you want to spool out a file from you sqlplus session, and need a line feed in your string, you can use the function

char(int)

to concatenate a line feed into your string. Integer represents the decimal value of the character you'd like to send to the database.

Example:

set trimspool on
set lines 200
set pages 0
set heading off
set verify off
set echo off
set feedback off
spool rowcount.sql
select 'spool rowcount.out' from dual;
select 'prompt rowcount for ' || owner || '.' || table_name || chr(10)|| 'select count(*) from ' || owner || '.'  || table_name || ';'
from dba_tables
where owner='SCOTT';
select 'exit' from dual;
exit

The chr(10) will return a line feed (may not be apparent in the code below but there is a line feed in there):

SYS@fsprod SQL> select chr(10) from dual;

C
-

The chr(10) corresponds to the hexadecimal value of A, which in turns corresponds to a line feed under the characterset that I happen to use in this database, which is WE8ISO8859P1.


The result is a file which looks like this:

spool rowcount.out
prompt rowcount for SCOTT.EMP
select count(*) from SCOTT.EMP;

prompt rowcount for SCOTT.DEPT
select count(*) from SCOTT.DEPT;

prompt rowcount for SCOTT.BONUS
select count(*) from SCOTT.BONUS;
.
.
.
exit

Sources: ISO code pages