Posted on 2009-04-09
Medium Priority
Last Modified: 2013-12-01
We have a 9i database runiing on machine A. We have 9IAS running on Machine B. We only use the oracle http server and mod_plsql in that server.

 We want to upgrade to 11g. I would like to get some ideas/steps for testing the application.

1.  Do you usually run upgrade script on the existing 9i db server or install a new 11g server and export schemas out from 9i and import into 11g. Which is better method?

2.  Do we need to set up a file system also that is similar to the one used by production on the new test machine? I assume yes.

3.  How do you roughly estimate the time for  testing the application?
Question by:sam15
  • 5
  • 4
  • 3
LVL 40

Expert Comment

ID: 24112596
The "cleanest" path is via export. That way you start with a brand new database, however if your data is really large, or larger than you have space for exporting, then you can do the upgrade in place.

If you do schema exports only, then there is no requirement to match filesystem on old and new servers. Import will put the objects into the default tablespace of the schema owner, in that case.

If you do full exports, then when you import, it will try to create any tablespaces that don't already exist, and it will try with the original file path; however if you pre-create those tablespaces, you can put them anywhere you want and the import will use them where they are.

The key question is how large is your data? That is a often a deciding factor.


Author Comment

ID: 24112899
not too large. about 5 giga bytes DB.

what do you mean the cleanest is "export". You mean installing a new 11g server instead of upgrading existing server?

Database has couple of schemas only. I did not quite understnad what you mean by match filesystem. I meant we have some custom directories on the unix system where oracle application uses for reading.writing files etc. This will not be created by an export or import. It is a unix thing i think.
LVL 40

Accepted Solution

mrjoltcola earned 1000 total points
ID: 24112928
You asked:

>>Do you usually run upgrade script on the existing 9i db server or install a new 11g server and export schemas out from 9i and import into 11g. Which is better method?

With 5GB database, and assuming you want to reuse the same OS server:
I recommend:
1) Do full system export of 9i DB (exp system/admin full=y file=oldsid.dmp
2) Take a snapshot of the tablespace  datafile layout. Fastest method is:

    alter database backup controlfile to trace as 'C:\oldsid.sql';
That will give  you an idea of what options to use for the new DB and which tablespaces to create.

2) shutdown old instance (but don't delete, keep it safe in case you rollback to it)
3) Change ORACLE_HOME in shell profile, install Oracle 11g from scratch in new ORACLE_HOME (make sure to set it before running dbca), create a starter database, setup new environment variable for ORACLE_SID
4) Create all tablespaces that you need based on old ones, they can be different paths but should use the same names
5) Do a full import or an import of each schema
6) Setup RMAN, do a full backup of the new 11g database

That way your existing non-oracle filesystems don't have to change.

Be advised, prior to installing 11g, you will need a separate area with plenty of space for your flash recovery area for RMAN to work from. Usually it should be at least 7-10 times the size of your core database, if not more, so as to be able to keep multiple full backups online and plenty of archivelog. So plan your disk ahead of time. 11g is a bit different than 9i.
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

LVL 48

Assisted Solution

schwertner earned 1000 total points
ID: 24114856
The best way is to prepare a spare machine with 11g and to set NLS_LANG parametre on the BOTH (very important!!!)  machines.
Using full or schemas level export make a dump file.
Precreate the nonsystem users and tablespaces on the 11g machine.
Make Import (in 11g there is available only Import, everything else is done by the Data Pump).
Use the new installation tempoarily to support the normal activity.

Now on the old server:
1. Deinstall 9i and say 'Good Bye' to the old times (sandwiches and wine!)
2. Make a new installation of 11g (Character set, DB block size?)
3. Upgrade to the newest version (if you have metalink!)
4. Using Data Pump make a full or schema level export
5. Precrate the tablespaces (nonsystem). User will be created automaically
6. Create Oracle directory
7. Import the dump


Because upgrading the existing server from 9i to 11g possibly you will run in errors,
problems will arise and time will be loosen. The danger to not have running DB
is very high and this can reflect on your reputation and even career!

Author Comment

ID: 24114879
I assume the above steps apply when you are migrating the 9i production DB to 11g production DB on same machine.

But we have to test the application first on different machine under 11g to make sure everything works properly.
I assume we would install the 11g on second machine and then export full copy of the production DB from 9i on machine 1 and then import it into 11g on machine 2. The directories on the second machine will have to be established too similar to machine 1. is this correct procedure.

It sounds there is no upgrade script that takes you from 9i to 11g and you recommend installing a new instance of teh 11g. That probbaly makes sense since 11g has many new products in it.

LVL 40

Expert Comment

ID: 24114979
Take it from experience, if you are concerned about reducing risk, upgrading in place from 9 to 11 is more likely to go wrong than installing 11g on a new server and moving over an export. That is why experienced DBAs always prefer a logical "export" up an upgrade, if possible. Experience. We have seen enough Oracle "upgrades" fail in the middle due to some odd SQL error or ORA-error.

Though I don't agree that an experience DBA should uninstall 9i (as schwertner suggests) before 11g is up and running, schwertner is right, a novice can very easily have problems due to mixing Oracle installs in the same environment.

So, best path, if you have a 2nd machine, definitely install 11g there, and move via export dump file there, and leave machine 1 pristine.

Testing has to happen, yes. That doesn't change this. 11g works. Don't be afraid to move to it. Testing it is just a part of it.

Author Comment

ID: 24115121
OK, iam convinced of the new install and export/import of database.

My question is how to replicate the file system. OUr current database read/writes to the some directoriers on the server and some directories are even mounted from a different machine.

Would i be able to re-create the same filesystem structure on the new machine without changing any of the application source code.
LVL 48

Expert Comment

ID: 24115217
I am glad you understand the advantages of having a spare server.
You do not need to replicate the file system at all if you use Export/Import.
Precreating the non-system tablespaces you say Oracle where they reside.
They can reside where you want.
The only prere`uisite is that the Oracle user has to have access on the directories.

Author Comment

ID: 24120771
i think you misunderstood me on the filesystem
currently we have a main directory /myappfiles/ and it has som subdirectories where files get written and read by oracle. i have to copy this over to the other machine. I am not refering to oracle home directories or internal oracle file system.

When you install 11 g server you will only have one HOME and then can run as many instances/databases as you can using that? It is not that each has a home and server running it right.
LVL 40

Expert Comment

ID: 24121791
>>currently we have a main directory /myappfiles/ and it has som subdirectories where files get written and read by oracle. i have to
>> copy this over to the other machine. I am not refering to oracle home directories or internal oracle file system.

User permissions and path must match. So you already answered this part of your question. Depending on how these files are "written and read" by Oracle it may mean Oracle knows about the directory due to configuration. To check this, login to your old database with sqlplus and type:

   show parameter util_file_dir

If that is set to the directory in question, you need to set it in your new database as well as an init param.

>>When you install 11 g server you will only have one HOME and then can run as many instances/databases as you can using that? It is

The normal way is to run multiple instances, out of the same ORACLE_HOME, each with its own ORACLE_SID. All instances may share the same physical executable installation, though it is possible not to, but not recommended.

>>not that each has a home and server running it right.

Forgive me if I misunderstand because of your use of terms. "Server" is such a generic term, but I just assume you are not clear on Oracle architecture. One set of "server" processes _per_ database. The "server" processes are called the INSTANCE. In Oracle there is a one-to-one relationship between INSTANCE and DATABASE in a non-clustered setup. If you have 3 databases, there will be 3 instances, so 3 full SETS of server processes, although they can all share the same listener process & port. An instance is made up of several processes, each with a different function, except on Windows where instead there is one process with different threads.


Author Comment

ID: 24122678
i think the "instance" are the set of memory processes required for db to run
"Database" are the physical db files that are on disk.

I think one instance can mount MANY databases but one database can be mounted by only ONE instance.

I assume all the instances are running using  ONE oracle ENGINE running in oracle home directory
LVL 48

Expert Comment

ID: 24122779
If you doesn't use Virtual Machines you can use Only one Home for Oracle.
It deliveres the binaries that are 'inflating' the instances and the Listener.

After that you can create the needed number of instances.
ORACLE HOME - set of oracle software binaries and parameter files
INSTANCE - set of RAM caches and background processes that work with datebaes
DATABASE - set of datafiles that stores permanently Oracle objects.

Every Instance works with only one database.
One Database can be served by one instance (common case) OR
by many instances (Real Application Server)

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

600 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question