ORA-01722 when running ausy1120.sql during preparation of new target database for EBS

If you are following note Doc ID 741818.1 "Export/import process for 12.0 or 12.1 using 11gR1 or 11gR2" and run into the follwing problem when running the ausy1120.sql script:

sqlplus system/*** @ausy1120.sql

SQL*Plus: Release 11.2.0.4.0 Production on Mon Nov 2 14:19:31 2015

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning option


PL/SQL procedure successfully completed.

declare
*
ERROR at line 1:
ORA-01722: invalid number
ORA-06512: at line 5

the solution is to modify the script slightly and correct a typo.
Open the ausy1120.sql file in an editor and change

select
  to_number(substr(version,1,instr(version,'.')))
  into :dbver
  from v$instance
  where rownum=1;

to

select
  to_number(substr(version,1,instr(version,'.')-1))
  into :dbver
  from v$instance
  where rownum=1;

Then rerun the script. It should finish almost immediately and the output should be similar to:

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

--------------------------------------------------------------------------------
--- ausy1120 started at 02-NOV-2015 14:40:04 ---


'---AUSY1120COMPLETEDAT'||TO_CHAR(SYSDATE,'DD-MON-YYYYHH24:MI:SS')||'----'
--------------------------------------------------------------------------------
--- ausy1120 completed at 02-NOV-2015 14:40:04 ----

Commit complete.

The same error seems to occur when running the aujv1120.sql and the aumsc1120.sql as well. The solution is the same for all three.

Syntax for setting hidden parameter in Oracle

Syntax example:

alter system set "_system_trig_enabled"=TRUE scope=both;

Syntax for setting the default undo tablespace for the instance

ALTER SYSTEM SET undo_tablespace='APPS_UNDOTS1' SCOPE=BOTH;

How to check your Operating System's code page for a particular character

I have been involved in several migration projects where we had to deal with Western European characters, which have invalid representation in the database, and needs to be converted.

Like I showed in my post about usage of the chr function, you sometimes need to find the decimal and/or hexadecimal value for a particular character.

But how can you verify that your operating system actually agrees; how would your Operating System translate a character passed from a terminal, e.g. putty?

First, make sure your putty terminal has its translation-settings set to the character set of the server you are logging into: Right-click your putty terminal upper frame, and pick "Change Settings" from the menu. Go to the "translation" settings and then select the correct character set from the drop-down menu "Remote character set".

On Linux platforms, a simple way to check how a character would be translated would be to use the hexdump utility. Thanks to Gray Watson for demonstrating this!

man hexdump
The hexdump utility is a filter which displays the specified files, or the standard input, if no files are specified, in a user specified format.

Let's try to hexdump the character ø, and see what the internal hexadecimal representation is:

echo "ø" | hexdump -v -e '"x" 1/1 "%02X" " "'
xC3 xB8 x0A 

The prefix x in front of the values represents hexadecimal values, so the important part here is "C3 and B8" - in a multibyte character set this represent the Scandinavian character ø ( I must admit, I never figured out what the 0A represents. Anyone?)

Another way is to use the "od" utility:

man od

od - dump files in octal and other formats
       -x     same as -t x2, select hexadecimal 2-byte units

Using the -x flag straight off will give the hexadecimal value in 2-byte units:

echo "ø" | od -x
0000000 b8c3 000a
0000003

This time, the values are cast around, and should be read backwards for meaning. I have not found an explanation to why od does this. Anyone?

However, if you use the -t x notation instead,:

echo "ø" | od -t x1
0000000 c3 b8 0a
0000003

The values come out as a DBA expects; c3b8 corresponds to decimal value 50104 which in turn represent the Scandinavian letter ø.

( And again, I never figured out what the 0a represents. Anyone?)

How to work around ORA-29275: partial multibyte character when using chr function

If you need to check what is actually stored in a database table, meaning the internal representation, you can use the DUMP function.

It will return a varcar2 value containing the data type code, length in bytes, and internal representation

SQL> select dump ('ø',1010) from dual;

DUMP('Ø',1010)
--------------------------------------------------------------------------------
Typ=96 Len=2 CharacterSet=AL32UTF8: 195,184

So the type above is 96, representing a VARCHAR2 or NVARCHAR datatype, the length is 2 bytes, the characterset of the database is AL32UTF8 and the decimal values of each byte is 195 and 184.

For single-byte character set, it would be easy to check which character the decimal represented under the database's characterset; simply use the chr function and pass the decimal value as an argument.

However with a multibyte character set, which one of the returned decimal functions should you choose?

SQL> select chr(195) from dual;

ERROR:
ORA-29275: partial multibyte character

A workaround is to dump the character to hexadecimal format instead of decimal, by using the argument 1016 as the second argument to the function:

SQL>  select dump ('ø',1016) from dual;

DUMP('Ø',1016)
--------------------------------------------------------------------------------
Typ=96 Len=2 CharacterSet=AL32UTF8: c3,b8

The c3b8 hexadecimal value corresponds to the decimal value of 50104. Pass this value to the chr function and you get the result you're looking for:

SQL> select chr(50104) from dual;

CHR(50
------
ø

A brilliant converter between decimal and hexadecimal numbers can be found here

The documentation for the dump function can be found at http://docs.oracle.com/cd/E11882_01/server.112/e41084/functions055.htm#SQLRF00635

The documentation for the chr function can be found at http://docs.oracle.com/cd/E11882_01/server.112/e41084/functions026.htm#SQLRF00616

The datatype table is listed here: https://docs.oracle.com/cd/B19306_01/appdev.102/b14250/oci03typ.htm#i422575

Is it possible to change the character set of the database by recreating the control file?

Question: Is it possible to change the character set of the database by recreating the controlfil?
Answer: No, this is not supported.

Doc ID 225912.1 states:

"Note also that it's NOT possible to change / adapt the NLS_CHARACTERSET by re-creating the control file.
The characterset specified in CREATE CONTROLFILE DATABASE ... RESETLOGS ....CHARACTER SET ; ( the result of an ALTER DATABASE BACKUP CONTROLFILE TO TRACE..) need to be the actual current NLS_CHARACTERSET of the database."

Question: So why does CREATE CONTROLFILE generate a CHARACTERSET clause in the first place then?
Answer: In case media recovery is required after the controlfile has been recreated.

From the documentation:
"If you specify a character set, then Oracle Database reconstructs character set information in the control file. If media recovery of the database is subsequently required, then this information will be available before the database is open, so that tablespace names can be correctly interpreted during recovery."

The documentation goes on to say

"This clause is required only if you are using a character set other than the default, which depends on your operating system."

Without having found any sources on the internet to confirm my interpretation, I will claim that this means:

* If you recreate your controlfile for a database with, say, WE8ISO8859P1 as character set, you will indeed need to specify the character in the CREATE CONTROLFILE clause.

* If you recreate the controlfile of a database using UTF8, for example AL32UT8, you can skip this clause altogether if you wish

How to pause an Oracle database

Sometimes it is useful to pause a database, and let it finish the running jobs at a later time.
Particularly in environments with shared resources contention may occur. Important databases need to be prioritized while less important ones should be suspended.

For this purpose, you can use the "suspend" command:

alter system suspend;

Alert log:

Mon Oct 05 10:14:49 2015
Suspending database after alter system suspend command

The database_status field in v$instance will reflect the new state of the database:

SQL> select database_status from v$instance;

 DATABASE_STATUS
-----------------

SUSPENDED

When you are ready to resume database activity, issue the following command:

alter system resume;

More info: Oracle Documentation