Home » RDBMS Server » Server Utilities » Downgrade from Oracle11g to Oracle9i via exp/imp (Oracle 9i/Oracle11g, 9.2.0.1.0/11.2.0.1.0,RHEL 2.1/RHEL 5.0)
Downgrade from Oracle11g to Oracle9i via exp/imp [message #487515] Tue, 28 December 2010 06:19 Go to next message
jimit_shaili
Messages: 224
Registered: June 2006
Location: India, Ahmedabad
Senior Member
Dear Friends

I want to downgrade my database from Oracle 11g (11.2.0.1.0) to Oracle 9i (9.2.0.1.0).
I've done trial of taking export of higher version(Oracle11g) through lower version(Oracle9i) exp utility and
trying to import same exp.dmp files into lower version(Oracle9i), but not succeded and getting some parameter
related error. Please guide the way i'm trying, is it the right or suggest me the right one. Here i'm giving
detail of my try.

On Oracle9i database client- Here i'm connecting to Oracle11g via use of 'wbdata.wbh-db1' service

[oracle1@WBDevelop smbshare]$ exp lab/lab@wbdata.wbh-db1 

Export: Release 9.2.0.1.0 - Production on Tue Dec 28 15:45:32 2010

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.


Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Produc
tion
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Enter array fetch buffer size: 4096 >

Export file: expdat.dmp > lab

(2)U(sers), or (3)T(ables): (2)U >

Export grants (yes/no): yes >

Export table data (yes/no): yes >

Compress extents (yes/no): yes >

Export done in US7ASCII character set and AL16UTF16 NCHAR character set
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user LAB
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user LAB
About to export LAB's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export LAB's tables via Conventional Path ...
. . exporting table                  BLENDSAMPLEDC        620 rows exported
. . exporting table                         JCTEMP        620 rows exported
. . exporting table                       SAMPLEDC        133 rows exported
. . exporting table                 SAMPLERECEIVEA        131 rows exported
. . exporting table                 SAMPLERECEIVEB        815 rows exported
. . exporting table                 SAMPLETESTINGA        815 rows exported
. . exporting table                 SAMPLETESTINGB      15485 rows exported
. . exporting table                  SAVESTATS_LAB       1601 rows exported
. . exporting table              TESTING_PARAMETER         19 rows exported
. . exporting table                 TRADESAMPLEDCA        213 rows exported
. exporting synonyms
. exporting views
. exporting stored procedures
. exporting operators
. exporting referential integrity constraints
. exporting triggers
. exporting indextypes
. exporting bitmap, functional and extensible indexes
. exporting posttables actions
. exporting materialized views
. exporting snapshot logs
. exporting job queues
. exporting refresh groups and children
. exporting dimensions
. exporting post-schema procedural objects and actions
. exporting statistics
Export terminated successfully without warnings.



On Oracle9i database client - Here i'm importing the lab.dmp
(Export taken from Oracle11g via use of 'wbdata.wbh-db1' service)

[oracle1@WBDevelop smbshare]$ imp lab/lab

Import: Release 9.2.0.1.0 - Production on Tue Dec 28 15:49:51 2010

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.


Connected to: Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production

Import file: expdat.dmp > lab

Enter insert buffer size (minimum is 8192) 30720>

Export file created by EXPORT:V09.02.00 via conventional path
import done in US7ASCII character set and AL16UTF16 NCHAR character set
IMP-00003: ORACLE error 96 encountered
ORA-00096: invalid value  for parameter plsql_compiler_flags, must be from among
 NON_DEBUG, DEBUG, INTERPRETED, NATIVE
IMP-00000: Import terminated unsuccessfully



Regards

Jimit
Re: Downgrade from Oracle11g to Oracle9i via exp/imp [message #487516 is a reply to message #487515] Tue, 28 December 2010 06:34 Go to previous messageGo to next message
jimit_shaili
Messages: 224
Registered: June 2006
Location: India, Ahmedabad
Senior Member
Dear Friends

Additional information i want to provide related to above parameter error while imp utility.

Setting of parameter on my lower version database(Oracle 9i)
SQL> show parameter plsql_compiler_flags 

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
plsql_compiler_flags                 string      INTERPRETED

SQL> select 'session' who, value
  2  from v$nls_parameters
  3  where parameter = 'NLS_CHARACTERSET'
  4  union all
  5  select 'database', value
  6  from nls_database_parameters
  7  where parameter = 'NLS_CHARACTERSET'
  8  /

WHO      VALUE
-------- ----------------------------------------------------------------
session  US7ASCII
database US7ASCII

SQL> select * from v$nls_parameters where PARAMETER = 'NLS_NCHAR_CHARACTERSET';

PARAMETER                                                        VALUE
---------------------------------------------------------------- --------------------
NLS_NCHAR_CHARACTERSET                                           AL16UTF16



Regards

Jimit

[Updated on: Tue, 28 December 2010 06:39]

Report message to a moderator

Re: Downgrade from Oracle11g to Oracle9i via exp/imp [message #487518 is a reply to message #487516] Tue, 28 December 2010 06:53 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10621
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
The settings are read from the dump file.
So check the setting in 11g database. Probably it is deprecated/obsolete/gone.
Check metalink. There must several notes ( can't recollect any now).

Meanwhile, technically this is not a downgrade.
You are just logically copying (exporting) the data from a higher version
and
Importing into a lower version.
The data is NOT upgraded or downgraded.
In most cases, if the data is simple and there is no compatibility issues this will work.

[Updated on: Tue, 28 December 2010 07:04]

Report message to a moderator

Re: Downgrade from Oracle11g to Oracle9i via exp/imp [message #487519 is a reply to message #487518] Tue, 28 December 2010 07:03 Go to previous messageGo to next message
jimit_shaili
Messages: 224
Registered: June 2006
Location: India, Ahmedabad
Senior Member
Dear Mahesh,

As you u describe i'm wrong to say that i'm downgrading. It is actually copying the data between two database. As per your suggestion i check my Oracle11g database for character set related setting, it is same as Oracle9i database. I also check that parameter 'plsql_compiler_flags' is no longer available in Oracle11g. I don't have access to metalink so please suggest some docs/link on our forum or else. Please guide me on above to resolve the issue.

Parameter values of Oracle11g database

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

SQL> select 'session' who, value
  2  from v$nls_parameters
  3  where parameter = 'NLS_CHARACTERSET'
  4  union all
  5  select 'database', value
  6  from nls_database_parameters
  7  where parameter = 'NLS_CHARACTERSET'
  8  /

WHO      VALUE
-------- ----------------------------------------------------------------
session  US7ASCII
database US7ASCII

SQL> select * from v$nls_parameters where PARAMETER = 'NLS_NCHAR_CHARACTERSET';

PARAMETER                                                        VALUE
---------------------------------------------------------------- ------------------
NLS_NCHAR_CHARACTERSET                                           AL16UTF16

SQL> show parameter plsql_compiler_flags;
SQL> 
Re: Downgrade from Oracle11g to Oracle9i via exp/imp [message #487521 is a reply to message #487519] Tue, 28 December 2010 07:15 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10621
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Metalink doc 550740.1 talks about it.
It involves recreating a dictionary view.
First hit on google with the DOC ID got me here
http://dbarajabaskar.blogspot.com/2009_09_01_archive.html
which seems to be an exact copy of the Metalink Note.

But, if this is production database or any database of worth,
I would first consult Oralce support before blindly following a document that advises to fix a dictionary view.

Re: Downgrade from Oracle11g to Oracle9i via exp/imp [message #487523 is a reply to message #487521] Tue, 28 December 2010 07:20 Go to previous messageGo to next message
jimit_shaili
Messages: 224
Registered: June 2006
Location: India, Ahmedabad
Senior Member
Dear Mahesh,

Thanks for reply, as you suggest i will check the link and follow it on my development db and let you know what happened. And i'm preparing myself for worst case of failing migrating on Oracle11g. It can be either from application level or database level. I will check and revert.

Regards

Jimit
Re: Downgrade from Oracle11g to Oracle9i via exp/imp [message #487528 is a reply to message #487523] Tue, 28 December 2010 07:57 Go to previous messageGo to next message
jimit_shaili
Messages: 224
Registered: June 2006
Location: India, Ahmedabad
Senior Member
Dear Mahesh,

As i told earlier i have tried as per your suggested blog, it is working ok. Actually it is not taking the export of those tables which has no rows. Means not exporting the table definition also.
But it seems to working ok for tables having rows>0;Update me if this can be handle by some other way.

Regards

Jimit
Re: Downgrade from Oracle11g to Oracle9i via exp/imp [message #487529 is a reply to message #487528] Tue, 28 December 2010 08:10 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10621
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Default intended behavior in 11g

http://www.orafaq.com/forum/?t=msg&goto=468010&42800/&srch=deferred#msg_467586

http://download.oracle.com/docs/cd/E11882_01/server.112/e17110/initparams073.htm
Re: Downgrade from Oracle11g to Oracle9i via exp/imp [message #537067 is a reply to message #487528] Mon, 26 December 2011 07:04 Go to previous messageGo to next message
csam
Messages: 3
Registered: December 2011
Location: pune
Junior Member
Hello Mahesh

I Also want to do the same to move my schema from Oracle 11G to Oracle 9i using exp/imp utilities(From 9i Client as mentioned by oracle ) . but while doing so i m getting error mentioned below:-

EXP-00008: ORACLE error 1455 encountered
ORA-01455: converting column overflows integer datatype
EXP-00000: Export terminated unsuccessfully


Please can you help in this ???
any help will be appreciated .


Regards

Manender Singh
Re: Downgrade from Oracle11g to Oracle9i via exp/imp [message #537087 is a reply to message #537067] Mon, 26 December 2011 08:19 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10621
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Please see if the methods discussed here are applicable for you.


https://forums.oracle.com/forums/thread.jspa?threadID=377314

Re: Downgrade from Oracle11g to Oracle9i via exp/imp [message #537131 is a reply to message #537087] Mon, 26 December 2011 23:57 Go to previous messageGo to next message
csam
Messages: 3
Registered: December 2011
Location: pune
Junior Member
Hi Mahesh ,

I Already tried all those thing mentioned in the doc ,but no Luck ... Sad ... after googling i got to know that it a bug from oracle side .. n there is no patch availiable for this...what is suggested to overcome this problem is to use 10g client in beetween this. but form me it is not possible .. so can you please tell me some other way for moving data from Oracle 11g to Oracle 9i.


Regards
Manender Singh
Re: Downgrade from Oracle11g to Oracle9i via exp/imp [message #537134 is a reply to message #537131] Tue, 27 December 2011 00:11 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10621
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Nothing I know on top of my head. 9i is gone a long while ago.
There are many related bugs. Check metalink.
Some will use 10g client to export and import. You can just install the 10g client else where and do the job.
Re: Downgrade from Oracle11g to Oracle9i via exp/imp [message #537157 is a reply to message #537134] Tue, 27 December 2011 00:59 Go to previous messageGo to next message
csam
Messages: 3
Registered: December 2011
Location: pune
Junior Member
Its Our Live server and its not possible to install 10g client on it(Live DBA is not ready to install 10g Client) ... so can't use 10g client option Sad ... can u please tell me some other option for this.


Regards

Manender Singh
Re: Downgrade from Oracle11g to Oracle9i via exp/imp [message #537219 is a reply to message #537157] Tue, 27 December 2011 05:30 Go to previous message
Mahesh Rajendran
Messages: 10621
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
You do not have to install on the server. You can install it anywhere.
Previous Topic: how to create control file to insert data in our database
Next Topic: Primary keys are not exporting when export using EXP command
Goto Forum:
  


Current Time: Mon Sep 01 18:05:10 CDT 2014

Total time taken to generate the page: 0.09504 seconds