Does a manual upgrade work from 8.0.5?
sPh, July 06, 2004 - 11:42 am UTC
If I interpret the Metalink document correctly, a "Direct Upgrade" from 8.0.5 to 9.2.0 is not supported.
However, the Export/Import process falls under the heading of "Manual Upgrade". The document is not clear on whether it is necessary to upgrade the 8.0.5 to 8.0.6 before doing the export, or whether the 8.0.5 dump file will work as a source for the 9.2.0 import. Sorry for being dense, but could you clarify that point for me? Thanks.
July 06, 2004 - 1:55 pm UTC
the 805 dmp file should work -- imp is backwards compatible.
public objects-
Dennis, July 07, 2004 - 1:09 pm UTC
You can always do a full export with rows=n to grab all the synonyms and such. Or is there a loophole I need to know about with that?
July 07, 2004 - 3:25 pm UTC
just messy is all, i tend to generate the public synonyms and grants out of the data dictionary using a tiny bit of sql to write sql.
Upgrading E-business suite DB from 9.2.0.4 to 9.2.0.7 RAC
Praful, February 03, 2006 - 10:32 pm UTC
Hi Tom
At my place the, we have Oracle Apss 11i on IBM AIX platform
Oracle Apps DB version is 9.2.0.4
Apps version 11.5.9
This is a multinode installation
Now we are migrating to DB version to 9.2.0.7 with RAC implementation with same apss version 11.5.9
I have few questions in my mind
1) Can we have a RAC DB on 9.2.0.7 ?
2) Can we migrate from 9.2.0.4 to 9.2.0.7 RAC DB
3) If yes what are all parameters we need to set in init.ora?
4) Does my application (11.5.9) support for RAC DB 9.2.0.7
5) Can u provide me some documents for this ?
February 06, 2006 - 12:09 am UTC
this would be something to discuss with support.
Confused with upgrade
ARU, March 26, 2006 - 6:51 pm UTC
Hi Tom,
We got a request from a customer who wanted database to be upgraded from 9.2.0.3 to 9.2.0.6, which was done. Now after 5 months the customer wants the database back to 9.2.0.3. What options do I have here?
a) WIll export / import work to a new instance I will create?
b) Can something be done to the same database as will to get it back to 9.2.0.3?
c) Can I use the datafiles of the 9.2.0.6 database (cold or hot backup) and open them up with 9.2.0.3 instance?
I have been trying to find out some docs. which can tell me what exactly happens during an upgrade, but all docs. I have found tell me what to do and not really what is happening when I do the steps and all. Please can you suggest.
Thanks for everything Tom, u're the best.
Regards,
ARU.
March 26, 2006 - 7:19 pm UTC
my first question of course is
why?
Upgrade
ARU, March 26, 2006 - 7:23 pm UTC
Hi Tom,
Something about the optimiser using wrong query plan for selects from partitioned tables in 9.2.0.6. This is abug in 9.2.0.6 and is resolved in 9.2.0.8 which is to be out around mid/end of April (or am I wrong?).
Regards,
ARU.
March 26, 2006 - 8:03 pm UTC
got bug #
nothing systemic that I'm aware of.
The correct bug discription
ARU, March 26, 2006 - 8:17 pm UTC
Hi Tom,
Sorry for not being precise earlier,
Bug 2423502: Wrong cost estimation with non-prefix bitmap index and nested loop.
This is the bug description and is to be resolved in 9.2.0.8.
Also please can you guide me to some documentation for what exactly is happening behind the scenes when we do all the upgrade steps to understand upgrades better.
Regards,
ARU.
Question - again
ARU, March 27, 2006 - 7:48 pm UTC
Hi Tom,
Am posting this again, you must have got busy elsewhere.
We got a request from a customer who wanted database to be upgraded from 9.2.0.3
to 9.2.0.6, which was done. Now after 5 months the customer wants the database
back to 9.2.0.3. What options do I have here?
a) WIll export / import work to a new instance I will create?
b) Can something be done to the same database as will to get it back to 9.2.0.3?
c) Can I use the datafiles of the 9.2.0.6 database (cold or hot backup) and open
them up with 9.2.0.3 instance?
I have been trying to find out some docs. which can tell me what exactly happens
during an upgrade, but all docs. I have found tell me what to do and not really
what is happening when I do the steps and all. Please can you suggest.
Thanks for everything Tom, u're the best.
Regards,
ARU.
March 27, 2006 - 8:25 pm UTC
I would not downgrade them - but if you do, work with support. They will outline your options. Including perhaps patching the bug they say they have been living with for 1/2 year but cannot wait to fix anymore?
scenerio
ARU, March 27, 2006 - 9:29 pm UTC
Hi Tom,
I have a scenerio:-
I have just upgraded to 9.2.0.7 database and I want to rollback to 9.2.0.1, can I setup another oracle_home on my server of 9.2.0.1 software and take cold backup of the 9.2.0.7 database and startup with the 9.2.0.1 binaries?
Will it work or am I climbing the wrong tree?
Regards,
ARU.
March 28, 2006 - 7:44 am UTC
Please utilize support for something like this - this is what they do best.
If you upgraded the dictionary - just doing a restore is insufficient.
I would ask "why" though, 9207 is pretty solid.
Upgrade from 8i to 9i
Charles Leung, April 04, 2007 - 4:33 am UTC
Dear Tom,
I need your comment on database upgrade.
The following program runs fine in old 8i database
SQL> l
1 declare
2 v2 varchar2(2):='1';
3 v1 varchar2(2);
4 begin
5 select v2 into v1 from (select v2 from dual);
6* end;
SQL> /
PL/SQL procedure successfully completed.
The database is upgrade to 9i (9.2.0.7), yet it failed.
SQL> l
1 declare
2 v2 varchar2(2):='1';
3 v1 varchar2(2);
4 begin
5 select v2 into v1 from (select v2 from dual);
6* end;
SQL> /
declare
*
ERROR at line 1:
ORA-00904: "V2": invalid identifier
ORA-06512: at line 5
The DBA advised to set the 9i DB to "Event 10933 level 512". I think this would turn the 9i into 8i behaviour, which mean I cannot use new features from 9i PL/SQL parser. Is there any better solution, other than viturally forcing 9i to 8i?
Thanks a lot.
April 04, 2007 - 10:17 am UTC
sigh, please look at the other place you posted this exact same stuff.
A reader, May 17, 2007 - 11:16 am UTC
Tom
We are upgrading from 9.2.0.5 to 9.2.0.8 this weekend, would you know of anywhere I can find info on any new developer(PL/SQL or SQL) features that are being added?
Thanks
Ravi
May 17, 2007 - 11:30 am UTC
that is just a patch set, it contains fixes - not new features.
just quick question about user level imports
A reader, May 18, 2007 - 10:50 am UTC
In re to user level importing: It seems easy to find which tables have been granted to public and to use dba_sys_privs to find out what system level privileges have been granted to public but how does one find out what roles have been granted to public? Thanks
May 18, 2007 - 4:15 pm UTC
ops$tkyte%ORA10GR2> create role foo;
Role created.
ops$tkyte%ORA10GR2> grant foo to public;
Grant succeeded.
ops$tkyte%ORA10GR2> select * from dba_role_privs where grantee = 'PUBLIC';
GRANTEE GRANTED_ROLE ADM DEF
------------------------------ ------------------------------ --- ---
PUBLIC FOO NO YES
Oracle upgrade 8i to oracle 9i
harsh, July 16, 2007 - 9:34 am UTC
Hi Tom,
I have seen the views which u suggested us ,they all are good, I have a specific requirment which is given below.
I have a running oracle database in 8i , now we are planning to migrate in oracle 9i.Just need to make sure that we following options to migrate or is there any else we can do.The env. in oracle 8i is in Sun Sol.
1. Take exp from oracle 8i and imp. in oracle 9i.
For this we have manually create oracle 9i and other thing(s) in Oracle box.
2. The Database Upgrade Assistant can be launched by the Oracle Universal Installer, depending upon the type of installation that you select, and provides a graphical user interface (GUI) that guides you through the upgrade of a database. During installation, you can choose to not use the Database Upgrade Assistant, instead choosing to launch it as a standalone tool at any time in the future to upgrade a database
3. Manually creation : Like we have first physically remove oracle 8i and fresh install oracle 9i.
Please suggest me to do so.
July 17, 2007 - 11:26 am UTC
"U" - who the heck is this person.
Migrating from the unsupported to ..... the unsupported. Hmmm...
Read the migration/upgrade guide:
http://docs.oracle.com/docs/cd/B10501_01/server.920/a96530/toc.htm it lays out all of your options in detail.
exp/imp would be dead last on my list.
an upgrade would be first.
List of invalid objects
A reader, April 27, 2009 - 5:24 am UTC
Hi Tom,
Many thanks for your time.
We have plan to upgrade oracle from 9.2.0.6 to 9.2.0.8
I have the following qustion:
Q) How to track the invalid objects after the upgrade.
there are list of different invalid objects before upgrade in each schema.
After upgarde , we would like to discard the objects which were invalid before upgrade and would like to know only the list of objects which are invalid after upgrade in each schema.
If you can write a SQL to do that, that would be great help.
Regards
April 27, 2009 - 2:21 pm UTC
create table hold_this_for_me
as
select * from all_objects where status <> 'VALID';
do your patch
use minus afterwards to find new additions.
Single User migration using DBUA
Jasika Jones, September 30, 2009 - 6:34 am UTC
Hi Tom,
I want to migrate one Orcle user having 100 gb data from Oracle 9(i) to Oracle 10(g).
1. What method I should use.( DBUA, exp/imp)
2. Can I use DBUA to migrate only one user?
3. How much time it will take in each method? How can I estimate it on my own?
Thanks,
Jasika Jones
October 07, 2009 - 8:22 am UTC
you upgrade databases
you do not migrate a single user with dbua, you upgrade a database.
You still don't say what 100gb of data means, sigh.
test it, that'll tell you how long. It'll depend on how much of that is data (table data) versus indexes (indexes do not take any time to export, indexes take a long time to import - since we import a CREATE INDEX). It'll depend on whether you have longs and long raws, blobs and clobs. It'll depend on the speed of your machine. It'll depend on your IO ability on that machine. It'll depend on many things.
so, benchmark it.