RE: Changing Character Set
- From: "Ken Payton" <Ken.Payton@xxxxxxxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 30 Jul 2004 17:50:14 -0400
Here is Oracle's response. It seems that if you are confident that you =
have no violating data then you can turn off the check.
ALTER SYSTEM SET "_tts_allow_nchar_mismatch"=3Dtrue;
If I find time I will attempt to build with UTF8, but won't I still have =
the problem with US7ASCII or does Oracle consider UTF8 a superset of =
US7ASCII?
Thanks for everyone's help in resolving this matter
--------
Steps to be performed are:
1. Check if the tablespace to be plugged in contains any national
character set data. Run the following query in the source
database of the tablespace:
SELECT owner||'.'||table_name||'.'||column_name||
' ('||data_type||')' "Column"
FROM dba_tab_columns
WHERE data_type IN ( 'NCHAR', 'NVARCHAR2', 'NCLOB' )
AND ( owner, table_name ) IN
( SELECT owner, table_name FROM dba_tables
WHERE tablespace_name =3D '<tablespace_name>'
UNION ALL
SELECT owner, cluster_name FROM dba_clusters
WHERE tablespace_name =3D '<tablespace_name>'
UNION ALL
SELECT table_owner, table_name FROM dba_tab_partitions
WHERE tablespace_name =3D '<tablespace_name>'
)
Substitute the three <tablespace_name> placeholders with the name
of the tablespace to be verified. Put the name in uppercase.
If the query returns any rows, the tablespace cannot be plugged
into a database with a different national character set.
WARNING:
=3D=3D=3D=3D=3D=3D=3D=3D
Do *NOT* set _tts_allow_nchar_mismatch=3Dtrue in such case!!!
If the query returns no rows, the tablespace can be safely
imported.
2. If the tablespace has been positively verified to not contain
any national character set data, as instructed above, log in
with administrative privileges to the target database
(e.g. in SQL*Plus) and issue the following statement:
ALTER SYSTEM SET "_tts_allow_nchar_mismatch"=3Dtrue;
3. Run the Import utility to plug in the tablespace as usual.
The ORA-19736 error will not be reported.
4. To avoid importing tablespaces that have not been verified
issue:
ALTER SYSTEM SET "_tts_allow_nchar_mismatch"=3Dfalse;
to restore the standard database behavior.
Note
~~~~
A restriction of the transportable tablespace feature is also that the =
source and target databases must use the same database
character set. This restriction cannot be worked around.
If the database character sets differ, the Import utility will report:
IMP-00017: following statement failed with ORACLE error 29345: [...]
IMP-00003: ORACLE error 29345 encountered
ORA-29345: can not plug a tablespace into a database using a different =
character set
If I understood this correctly, you are creating the 10g DB with =
US7ASCII for backwards compatibilit
y, so you should not have a problem.
Regards,
-Manuel Tous
Oracle Support Services
Status:
=3D=3D=3D=3D=3D=3D=3D=3D
@CUS
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Justin Cave
Sent: Friday, July 30, 2004 2:52 PM
To: oracle-l@xxxxxxxxxxxxx
Subject: RE: Changing Character Set
Do the other databases have data stored in NCHAR/ NVARCHAR2/ NCLOB =
columns?
If so, is that data really ASCII (i.e. only English, no characters above
127)? =20
What about the CHAR/ VARCHAR2/ CLOB data? Is that really ASCII?
If everything really is ASCII, can you change the national character set =
to
UTF8 on the existing databases, since that is a strict binary superset =
of
US7ASCII?
Justin Cave
Distributed Database Consulting, Inc.
http://www.ddbcinc.com/askDDBC
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx =
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Ken Payton
Sent: Friday, July 30, 2004 11:31 AM
To: oracle-l@xxxxxxxxxxxxx
Subject: RE: Changing Character Set
It is actually looking even more grim. What I am trying to build is a =
=3D new
10g environment that will allow the transporting of tablespaces from =3D =
our
various existing 8i environments which have a US7ASCII character set =3D =
and
national character set. 9i/10g only allows AL16UTF16 or UTF8 for =3D =
national
character set. I have opened a TAR with Oracle to see what =3D their
recommendations are. Unfortunately the data can range from =3D several
hundred GB to upwards of 20TB, conversion is definitely not =3D =
feasible. I
will update the post with Oracle's recommendations.
Kenny
=3D20
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Justin Cave
Sent: Friday, July 30, 2004 10:52 AM
To: oracle-l@xxxxxxxxxxxxx
Subject: RE: Changing Character Set
For any database, the superset check is pretty quick. It just grabs the
current database character set and the new database character set and =
=3D
checks whether the new character set is a strict binary superset (the =
=3D
globalization guide has an appendix that lists the valid supersets). =
Oracle
never has =3D to look at your data.
There is an undocumented clause INTERNAL_USE that you can use to force =
=3D the
character set change, but I would classify that as an incredibly high =
=3D risk
operation (and the fact that the clause itself tells you that it is for
internal use should amplify that warning). Recreating a database with =
=3D no
data is trivial in comparison to the pain and testing that should go in =
=3D to
using this clause.
Justin Cave
Distributed Database Consulting, Inc.
http://www.ddbcinc.com/askDDBC
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx =3D
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Mark W. Farnham
Sent: Friday, July 30, 2004 8:46 AM
To: oracle-l@xxxxxxxxxxxxx
Subject: RE: Changing Character Set
OP did write "new" database, so I think that means no user data is at =
=3D
risk.
But I'm not sure whether you can even skip the binary superset check for =
=3D
the Oracle data dictionary and things they load in support of the =
products.
=3D I wonder whether less time and effort and more certainty would be to =
=3D
simply recreate the database. Has anyone already done this? Of course =
one
"it worked okay, no problems" only covers that specific case. Without an
engineering certainty of what might go wrong, there would have to be a
pretty big overhead avoided to justify the testing overhead. For a new
database the superset check should be pretty short, right?
mwf
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Justin Cave
Sent: Friday, July 30, 2004 9:57 AM
To: oracle-l@xxxxxxxxxxxxx
Subject: RE: Changing Character Set
I'm not sure whether you intended to send a different Metalink note, but
234381.1 covers how to move an Oracle 7 database with the AL24UTFFSS to =
=3D 9i
with a character set AL32UTF8 by going through 8i (since AL24UTFFSS =3D =
isn't
a valid character set in 9i).
In almost every case, you would not want to skip the superset check. =
=3D When
you change the database character set, Oracle is only updating the =3D
internal data structures, it is not updating any of the actual data in =
the =3D
database.
Without the binary superset check, you can very, very easily corrupt =3D =
some
or all of your data.
Justin Cave
Distributed Database Consulting, Inc.
http://www.ddbcinc.com/askDDBC
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx =3D
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Fuad Arshad
Sent: Friday, July 30, 2004 7:16 AM
To: oracle-l@xxxxxxxxxxxxx
Subject: Re: Changing Character Set
metalink Note:234381.1 can help you do this Ken Payton
<Ken.Payton@xxxxxxxxxxxxxxxxxx> wrote:Does anyone know how to switch off =
=3D
the superset check when changing the =3D3D national character set. I =
would =3D
like to change the character set of a =3D3D new database without =
rebuilding it
and =3D I am receiving ORA-12714: invalid =3D3D national character set
specified. =3D3D20
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Tim Gorman
Sent: Friday, July 30, 2004 5:38 AM
To: oracle-l@xxxxxxxxxxxxx
Subject: Re: Lost PERFSTAT bridge scripts Found it
Larry,
Thanks for the post of Mr Hurley's material. Especially useful is the =
=3D =3D3D
use of AUTONOMOUS TRANSACTION pragma, although I've not found it to be
necessary...
I can understand performing a STATSPACK.SNAP before database shutdown, =
=3D =3D3D
to "flush" any values to disk before they are lost, but I am at a loss =
to
understand the reason to perform a STATSPACK.SNAP in an AFTER STARTUP
database-event trigger?
-Tim
on 7/29/04 1:49 PM, Wolfson Larry - lwolfs at =3D3D =3D
lawrence.wolfson@xxxxxxxxxx
wrote:
> Guess I didn't get specific enough on Google the first time.
>=3D3D20
> Sorry
>=3D3D20
> http://www.quest-pipelines.com/pipelines/dba/tips03.htm#january
>=3D3D20
> January's Tip of the Month
>=3D3D20
> Automatic Statspack Snapshots at Shutdown and Startup Compliments=20
>of=3D20 Darryl Hurley, Pipeline SYSOP (dhurley@xxxxxxxxxx) =3D3D20 =20
>Oracle?s=3D20 Statspack utility provides a straightforward method of =
=3D3D
monitoring
> database performance statistics. The process is simple; take=20
> interval=3D20 snapshots of performance indicators and then run reports =
> to see how =3D
=3D3D
much the
> indicators have changed during the interval(s).
>=3D3D20
> Problems arise when intervals span an Oracle shutdown because =3D3D
comparing
> interval values across them is illogical. Here?s an example:
>=3D3D20
> 10:00 PM Statspack Snapshot #33 shows Physical Reads =3D3D3D 100000
> 10:15 PM Database Shutdown
> 10:20 PM Database Restarted
> 11:00 PM Statspack Snapshot #34 shows Physical Reads =3D3D3D 100 =
=3D3D20 =20
>=3D
At=3D20
>this point a StatsPack Report comparing snapshot #33 to snapshot =3D3D
#34
> would claim that ?99900 physical reads had occurred. Actually the =
=3D3D
report
> would begin with this self-explanatory text:
>=3D3D20
> ERROR: Snapshots chosen span an instance shutdown: RESULTS ARE INVALID =
>=3D
>=3D3D20 It?s impossible to report across a shutdown, but it is =
possible=20
>=3D
to=3D20
>=3D3D
reduce
> the lost periods of time (10:00 to 10:15 and 10:20 to 11:00 in our =
=3D3D
example)
> by automatically performing snapshots before shutdown and after =3D3D
startup.
> It?s easily done with BEFORE-SHUTDOWN and AFTER-STARTUP triggers.
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx put
'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx put
'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx put
'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx put
'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx put
'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx put
'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx put
'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts: