[plantopiadb:oraperf] /home/users/oraperf> imp transport_tablespace=y file=/perf/dump/perfdump.dmp log=/perf/dump/perfdump>
Import: Release 9.2.0.7.0 - Production on Fri Sep 11 15:45:06 2009
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Username: / as sysdba
Connected to: Oracle9i Enterprise Edition Release 9.2.0.7.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.7.0 - Production
Export file created by EXPORT:V09.02.00 via conventional path
About to import transportable tablespace(s) metadata...
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses UTF8 character set (possible charset conversion)
. importing SYS's objects into SYS
IMP-00017: following statement failed with ORACLE error 29342:
"BEGIN sys.dbms_plugts.checkUser('APPS'); END;"
IMP-00003: ORACLE error 29342 encountered
ORA-29342: user APPS does not exist in the database
ORA-06512: at "SYS.DBMS_PLUGTS", line 1594
ORA-06512: at line 1
IMP-00000: Import terminated unsuccessfully
Soltuion :
APPS user is having object in respective tablespace (PERFSTAT in our case) at the source database. But no APPS user at the destination database. Use the query mentioned below to fine all the users having the object in PERFSTAT tablespace at source database and need to be created at the destination database , in case if not present .
SQL> select distinct owner from dba_segments where tablespace_name='PERFSTAT' ;
OWNER
------------------------------
APPS
BIZMAX
DBSNMP
KIC
PERFSTAT
TRCANLZR
create all the users in your destiantion database . Where you're plugin the tablespace ..
Wednesday, September 16, 2009
ORA-29335: tablespace 'PERFSTAT' is not read only ( during transport tablespace option )
[veritas:oraprod] /home/oraprod > TAT file=/u43/oradata/stats_dump/perfdump.dmp log=/u43/oradata/stats_dump/perfdump.log <
Export: Release 9.2.0.5.0 - Production on Fri Sep 11
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Username: / as sysdba
Connected to: Oracle9i Enterprise Edition Release 9.2.0.5.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.5.0 - Production
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses UTF8 character set (possible charset conversion)
Note: table data (rows) will not be exported
About to export transportable tablespace metadata...
EXP-00008: ORACLE error 29335 encountered
ORA-29335: tablespace 'PERFSTAT' is not read only
ORA-06512: at "SYS.DBMS_PLUGTS", line 436
ORA-06512: at line 1
EXP-00000: Export terminated unsuccessfully
Soltuion :
This is because of the tablespace in READ WRITE mode . Before taking the export of metadata, tablespace
needs to be taken in READ ONLY mode
How to change the character set of a database
Step 1 : Find the present character set of your database ..
SQL> select * from NLS_DATABASE_PARAMETERS where parameter in ('NLS_CHARACTERSET','NLS_NCHAR_CHARACTERSET');
PARAMETER VALUE
------------------------------ ----------------------------------------
NLS_CHARACTERSET US7ASCII
NLS_NCHAR_CHARACTERSET AL16UTF16
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER SYSTEM ENABLE RESTRICTED SESSION;
ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;
ALTER SYSTEM SET AQ_TM_PROCESSES=0;
ALTER DATABASE OPEN;
select value from nls_database_parameters where parameter='NLS_CHARACTERSET';
---- TRUNCATE TABLE SYS.METASTYLESHEET; ---to get rid of ORA-12716 per Metalink Note - 213015.1
ALTER DATABASE CHARACTER SET UTF8;
SHUTDOWN IMMEDIATE;
STARTUP;
@$ORACLE_HOME/rdbms/admin/catmet.sql
SHUTDOWN IMMEDIATE;
STARTUP;
Step 3 : Now confirm the new character set of your database ..
SQL> select * from NLS_DATABASE_PARAMETERS where parameter in ('NLS_CHARACTERSET','NLS_NCHAR_CHARACTERSET');
PARAMETER VALUE
------------------------------ ----------------------------------------
NLS_CHARACTERSET UTF8
NLS_NCHAR_CHARACTERSET AL16UTF16
Subscribe to:
Posts (Atom)