CHARACTER SET 1

[Palmero]

Hi all,
In my laptop, I have installed an oracle 8.1.7 database with character set WE8ISO8859P1.
I want to install a product and the installation will work if and only if I have an oracle database with character set UTF8.
I can't alter the database with this character set (see below) :
SQL> ALTER DATABASE CHARACTER SET UTF8;
ALTER DATABASE CHARACTER SET UTF8
*
ERREUR à la ligne 1 :
ORA-12712: le nouveau jeu de caractères doit être un surensemble de l'ancien

In the same laptop, how can I do to create a new database with the character set UTF8 and be able to use the old and the new database?

Thanks in advance for your answer.

Palmero.

 

[SantaMufasa]

Palmero,

You can create a second database with UTF8 character set by doing the following:

1) create a new directory structure for the new database that parallels the data directory structure for your original database. (For example, <drive>:\Oracle\admin\<SID>\adhoc, bdump, cdump, create, exp, pfile, udump).

2) create a parameter file for the new instance in your pfile directory created above.

3) Run the oradim program to create a new Windows service for the new instance:
oradim80 -new -sid <sid> -intpwd <password> -startmode auto -pfile <drive:\path\pfile<SID>.ora>

4) Run &quot;svrmgrl&quot; or &quot;sqlplus /nolog&quot;
5) &quot;connect / as sysdba&quot;
6) Then run a modified copy of the following script, which creates a UTF8 database:

startup nomount pfile=C:\Oracle\admin\DEMO817\pfile\initDEMO817.ora

create database <dbname>
	controlfile reuse
	logfile	group 1 ('<drive>:\<path>\<logfilename1a>'
			,'<drive>:\<path>\<logfilename1b>' size 30M reuse,
		group 2 ('<drive>:\<path>\<logfilename2a>'
			,'<drive>:\<path>\<logfilename2b>' size 30M reuse,
		group 3 (('<drive>:\<path>\<logfilename1a>'
			,'<drive>:\<path>\<logfilename1b>' size 30M reuse

	datafile '<drive>:\<path>\<name for system file>' size <size in M> reuse
	character set [b]UTF8[/b];

7) Create your rollback-segment tablespace and your rollback segments, bringing them on-line.
8) Create your temporary tablespace.
9) Create your application tablespace(s).
10) Create your users. Change passwords for SYS and SYSTEM.
11) Run Oracle-install cleanup scripts:
@<drive>:\Oracle\Ora81\rdbms\admin\catalog
@<drive>:\Oracle\Ora81\rdbms\admin\catproc
@<drive>:\Oracle\Ora81\rdbms\admin\catexp
@<drive>:\Oracle\Ora81\rdbms\admin\catblock
@<drive>:\Oracle\Ora81\rdbms\admin\utlxplan
@<drive>:\Oracle\Ora81\rdbms\admin\utltkprf
@<drive>:\oracle\Ora81\sqlplus\admin\pluctrce
connect system\<password>
@<drive>:\Oracle\Ora81\sqlplus\admin\pupbld

Let me know if this works for you.

Mufasa
(aka Dave of Sandy, Utah, USA @ 18:36 (12Feb04) UTC (aka &quot;GMT&quot; and &quot;Zulu&quot, 11:36 (12Feb04) Mountain Time)

 

[Palmero]

Hi Mufasa,

I am performing your solution, but I have problems to connect / as sysdba. What could be the issue according to you. The &quot;OracleOraHome81TNSListener&quot; is started.

Thanks very much for your help.

SVRMGR> connect / as sysdba
ORA-12560: TNS : erreur d'adaptateur de protocole
SVRMGR>

Palmero.

 

[SantaMufasa]

Palmero,

&quot;ORA-12560: TNS : erreur d'adaptateur de protocole&quot; on Windows usually results from either

1) your new Oracle service (that you created with the &quot;oradim&quot; command) is not &quot;Started&quot; (Do you know how to confirm on Windows if a service is &quot;Started&quot;?), and/or
2) Svrmgrl cannot determine your ORACLE_HOME and/or ORACLE_SID. You can manually fix that with the following commands from your OS prompt:
set ORACLE_HOME=<drive>:\<OracleHome Path>
set ORACLE_SID=<new SID name>
...then log into svrmgrl again and re-try your connection and commands.

Let us know what you discover.

Mufasa
(aka Dave of Sandy, Utah, USA @ 20:53 (12Feb04) UTC (aka &quot;GMT&quot; and &quot;Zulu&quot, 13:53 (12Feb04) Mountain Time)

 

[Palmero]

Hi Mufasa,

My ORACLE_HOME is defined in the Windows Registry HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE (Oracle_home=c:\oracle\ora81).
My ORACLE_SID is defined in the Windows Registry HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\HOME0 for my first database (Oracle_sid=MYBASE) which works.
The Oradim I performed for COGNOS database created the OracleServiceCOGNOS and this service is started when I look at the Control Panel --> administration tools--> Services.

In the DOS Windows, I have :
C:\>set
COMSPEC=C:\WINNT\SYSTEM32\COMMAND.COM
ALLUSERSPROFILE=C:\DOCUME~1\ALLUSE~1
APPDATA=C:\DOCUME~1\ADMINI~1\APPLIC~1
COMMONPROGRAMFILES=C:\PROGRA~1\FICHIE~1
COMPUTERNAME=UP020000
HOMEDRIVE=C:
HOMEPATH=INCLUDE=C:\ODI\OStore\include
LIB=C:\ODI\OStore\lib
LINK_DIR=C:\Program Files\Business Objects\Data Integrator 6.0
LOGONSERVER=\\UP020000
NUMBER_OF_PROCESSORS=1
OS=Windows_NT
OS2LIBPATH=C:\WINNT\system32\os2\dll;
OS_ROOTDIR=C:\ODI\OStore
OS_TMPDIR=C:\WINNT\TEMPPATH=C:\PROGRA~1\BUSINE~1\BUSINE~1\bin\orb\asp\5.1\bin
PATHEXT=.COM;.EXE;.BAT;.CMD;.VBS;.VBE;.JS;.JSE;.WSF;.WSH
PROCESSOR_ARCHITECTURE=x86
PROCESSOR_IDENTIFIER=x86 Family 6 Model 11 Stepping 1, GenuineIntel
PROCESSOR_LEVEL=6
PROCESSOR_REVISION=0b01
PROGRAMFILES=C:\PROGRA~1
PROMPT=$P$G
SYSTEMDRIVE=C:
SYSTEMROOT=C:\WINNT
TEMP=C:\WINNT\TEMP
TMP=C:\WINNT\TEMP
USERDOMAIN=UP020000
USERNAME=Administrateur
USERPROFILE=C:\DOCUME~1\ADMINI~1
WV_GATEWAY_CFG=C:\oracle\ora81\Apache\modplsql\cfg\wdbsvr.app

When I define the environment variables, I still have the same problem :
This is what I did and I still have the same problem :
C:\>set ORACLE_HOME=c:\oracle\ora81

C:\>set ORACLE_SID=COGNOS

C:\>


Thanks for your implication.

Palmero.

 

[Palmero]

C:\>set ORACLE_HOME=c:\oracle\ora81

C:\>set ORACLE_SID=COGNOS
Another test :

C:\>svrmgrl

Oracle Server Manager Release 3.1.7.0.0 - Production

Copyright (c) 2000, Oracle Corporation. Tous droits rÚservÚs.

Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production
With the Partitioning option
JServer Release 8.1.7.0.0 - Production

SVRMGR> connect / as sysdba
ORA-01031: insufficient privileges
SVRMGR>

 

[SantaMufasa]

Palmero,

The &quot;ORA-01031: insufficient privileges&quot; message on Windows most often occurs when the username which you have used to connect on your Windows machine is not a member of the &quot;ORA_DBA&quot; group. Start..Programs..Administrative Tools..User Manager for Domains, then go to the &quot;Groups&quot; section at the bottom of the window, and double-click &quot;Administrators&quot;. If successful, then you can see the usernames of members of the &quot;Administrators&quot; group that can &quot;connect / as sysdba&quot;; if unsuccessful, then you are not logged in as a member of the &quot;Administrators&quot; group and you do not have enough permission to connect in svrmgrl &quot;as sysdba&quot;. If you ARE listed as an Administrator, then we must do more detective work. Please post and let us know your findings.

Mufasa
(aka Dave of Sandy, Utah, USA @ 21:39 (12Feb04) UTC (aka &quot;GMT&quot; and &quot;Zulu&quot, 14:39 (12Feb04) Mountain Time)

 

[Palmero]

Hi Mufasa,
YOU'RE RIGHT !
SVRMGR> connect / as sysdba
ConnectÚ
SVRMGR>

But I have another problem :
SVRMGR> startup nomount pfile=C:\Oracle\admin\COGNOS\pfile\cognos.ora

ORA-24323: valeur interdite
ORA-12571: TNS : échec à l'écriture du paquet

When I performed the &quot;oradim -new -sid COGNOS -intpwd cognos -startmode auto -pfile c:\oracle\admin\cognos\pfile\cognos.ora&quot;, the following line was in the cognos.ora :
control_files = (&quot;C:\oracle\oradata\COGNOS\control01.ctl&quot;, &quot;C:\oracle\oradata\COGNOS\control02.ctl&quot;, &quot;C:\oracle\oradata\COGNOS\control03.ctl&quot

THE problem is that control01.ctl,control02.ctl and control03.ctl did not exist.

Thanks!

 

[SantaMufasa]

Palmero,

&quot;ORA-24323: valeur interdite&quot; can occur from an incorrect value in your parameter file. Could you please post the contents of &quot;COGNOS.ora&quot; (your parameter file)?

Mufasa
(aka Dave of Sandy, Utah, USA @ 01:31 (13Feb04) UTC (aka &quot;GMT&quot; and &quot;Zulu&quot, 18:31 (12Feb04) Mountain Time)

 

[Palmero]

Hi Mufasa,

Please find below the contents of my parameter file :
#
# Copyright (c) 1991, 2000 by Oracle Corporation
#
##############################################################################
# Example INIT.ORA file
#
# This file is provided by Oracle Corporation to help you customize
# your RDBMS installation for your site. Important system parameters
# are discussed, and example settings given.
#
# Some parameter settings are generic to any size installation.
# For parameters that require different values in different size
# installations, three scenarios have been provided: SMALL, MEDIUM
# and LARGE. Any parameter that needs to be tuned according to
# installation size will have three settings, each one commented
# according to installation size.
#
# Use the following table to approximate the SGA size needed for the
# three scenarious provided in this file:
#
# -------Installation/Database Size------
# SMALL MEDIUM LARGE
# Block 2K 4500K 6800K 17000K
# Size 4K 5500K 8800K 21000K
#
# To set up a database that multiple instances will be using, place
# all instance-specific parameters in one file, and then have all
# of these files point to a master file using the IFILE command.
# This way, when you change a public
# parameter, it will automatically change on all instances. This is
# necessary, since all instances must run with the same value for many
# parameters. For example, if you choose to use private rollback segments,
# these must be specified in different files, but since all gc_*
# parameters must be the same on all instances, they should be in one file.
#
# INSTRUCTIONS: Edit this file and the other INIT files it calls for
# your site, either by using the values provided here or by providing
# your own. Then place an IFILE= line into each instance-specific
# INIT file that points at this file.
#
# NOTE: Parameter values suggested in this file are based on conservative
# estimates for computer memory availability. You should adjust values upward
# for modern machines.
#
###############################################################################

db_name = &quot;COGNOS&quot;

instance_name = COGNOS

service_names = COGNOS

db_files = 1024 # INITIAL
# db_files = 80 # SMALL
# db_files = 400 # MEDIUM
# db_files = 1500 # LARGE

control_files = (&quot;C:\oracle\oradata\COGNOS\control01.ctl&quot;, &quot;C:\oracle\oradata\COGNOS\control02.ctl&quot;, &quot;C:\oracle\oradata\COGNOS\control03.ctl&quot

open_cursors = 300
max_enabled_roles = 30
db_file_multiblock_read_count = 8 # INITIAL
# db_file_multiblock_read_count = 8 # SMALL
# db_file_multiblock_read_count = 16 # MEDIUM
# db_file_multiblock_read_count = 32 # LARGE

db_block_buffers = 2048 # INITIAL
# db_block_buffers = 100 # SMALL
# db_block_buffers = 550 # MEDIUM
# db_block_buffers = 3200 # LARGE

shared_pool_size = 31457280 # INITIAL
# shared_pool_size = 3500000 # SMALL
# shared_pool_size = 5000000 # MEDIUM
# shared_pool_size = 9000000 # LARGE

large_pool_size = 614400
java_pool_size = 20971520

log_checkpoint_interval = 10000
log_checkpoint_timeout = 1800

processes = 150 # INITIAL
# processes = 50 # SMALL
# processes = 100 # MEDIUM
# processes = 200 # LARGE

parallel_max_servers = 5 # SMALL
# parallel_max_servers = 4 x (number of CPUs) # MEDIUM
# parallel_max_servers = 4 x (number of CPUs) # LARGE

log_buffer = 32768 # INITIAL
# log_buffer = 32768 # SMALL
# log_buffer = 32768 # MEDIUM
# log_buffer = 163840 # LARGE

#audit_trail = true # if you want auditing
#timed_statistics = true # if you want timed statistics
max_dump_file_size = 10240 # limit trace file size to 5M each

# Uncommenting the line below will cause automatic archiving if archiving has
# been enabled using ALTER DATABASE ARCHIVELOG.
# log_archive_start = true
# log_archive_dest_1 = &quot;location=C:\oracle\oradata\BARTH\archive&quot;
# log_archive_format = %%ORACLE_SID%%T%TS%S.ARC

# If using private rollback segments, place lines of the following
# form in each of your instance-specific init.ora files:
#rollback_segments = ( RBS0, RBS1, RBS2, RBS3, RBS4, RBS5, RBS6 )

# If using public rollback segments, define how many
# rollback segments each instance will pick up, using the formula
# # of rollback segments = transactions / transactions_per_rollback_segment
# In this example each instance will grab 40/5 = 8
# transactions = 40
# transactions_per_rollback_segment = 5

# Global Naming -- enforce that a dblink has same name as the db it connects to
global_names = true

# Edit and uncomment the following line to provide the suffix that will be
# appended to the db_name parameter (separated with a dot) and stored as the
# global database name when a database is created. If your site uses
# Internet Domain names for e-mail, then the part of your e-mail address after
# the '@' is a good candidate for this parameter value.
# db_domain = us.acme.com # global database name is db_name.db_domain

# Uncomment the following line if you wish to enable the Oracle Trace product
# to trace server activity. This enables scheduling of server collections
# from the Oracle Enterprise Manager Console.
# Also, if the oracle_trace_collection_name parameter is non-null,
# every session will write to the named collection, as well as enabling you
# to schedule future collections from the console.
# oracle_trace_enable = true

oracle_trace_collection_name = &quot;&quot;
# define directories to store trace and alert files
background_dump_dest = C:\oracle\admin\COGNOS\bdump
#Uncomment this parameter to enable resource management for your database.
#The SYSTEM_PLAN is provided by default with the database.
#Change the plan name if you have created your own resource plan.# resource_manager_plan = system_plan
user_dump_dest = C:\oracle\admin\COGNOS\udump

db_block_size = 8192

remote_login_passwordfile = exclusive

os_authent_prefix = &quot;&quot;

# The following parameters are needed for the Advanced Replication Option
job_queue_processes = 4
job_queue_interval = 10
open_links = 4

distributed_transactions = 500
mts_dispatchers = &quot;(PROTOCOL=TCP)(PRE=oracle.aurora.server.SGiopServer)&quot;
# Uncomment the following line when your listener is configured for SSL
# (listener.ora and sqlnet.ora)
# mts_dispatchers = &quot;(PROTOCOL=TCPS)(PRE=oracle.aurora.server.SGiopServer)&quot;

compatible = 8.1.0
sort_area_size = 65536
sort_area_retained_size = 65536
UTL_FILE_DIR = *

Palmero

 

[Palmero]

Hi Mufasa,
DATABASE CREATED! Let me know you what happens after.

SVRMGR> connect / as sysdba
ConnectÚ
SVRMGR> startup nomount pfile=C:\Oracle\admin\COGNOS\pfile\cognos.ora
Instance ORACLE dÚmarrÚe
Zone globale systÞme (SGA) totale de 73701404 octets
Fixed Size 75804 octets
Variable Size 56770560 octets
Database Buffers 16777216 octets
Redo Buffers 77824 octets
SVRMGR> create database COGNOS
2> controlfile reuse
3> LOGFILE group 1 ('c:\oracle\oradata\cognos\redolog1a.dbf','c:\oracle\ora
data\cognos\redolog1b.dbf' ) SIZE 30M reuse,
4> group 2 ('c:\oracle\oradata\cognos\redolog2a.dbf','c:\oracle\ora
data\cognos\redolog2b.dbf' ) SIZE 30M reuse,
5> group 3 ('c:\oracle\oradata\cognos\redolog3a.dbf','c:\oracle\ora
data\cognos\redolog3b.dbf' ) SIZE 30M reuse
6> datafile 'c:\oracle\oradata\cognos\system.dbf' size 200M reuse
7> character set UTF8;
Instruction traitÚe
SVRMGR>

Palmero

 

[SantaMufasa]

So, Palmero, are you (as we say in the US) a &quot;happy camper&quot;? Is there anything else we must do on this thread?

Mufasa
(aka Dave of Sandy, Utah, USA @ 16:32 (13Feb04) UTC (aka &quot;GMT&quot; and &quot;Zulu&quot, 09:32 (13Feb04) Mountain Time)