Training, Training, Training

---

Training, Training, Training!

Before you start your installation process, get your key players trained. Get the technical people involved (those who are going to be responsible for understanding the installation and any customizations involved), the functional people (the ones who will be doing any given job or a trainer), and anyone else who will logically be doing anything with the system, particularly in the early days, trained.

Yes, your company has probably just allocated to this project a budget big enough to choke the proverbial horse and to expend more money on training people who will not even have the system to practice on when they get back sounds like a tremendous waste of resources. But stop and consider that trained people make fewer mistakes. They will have seen something before, so when they are faced with the "real" training on your own system, they will have some familiarity with the interface. They will have the background that they need to know where to start asking questions rather than floundering in a completely unfamiliar place without any point of reference.

While Oracle University provides considerable training on the functional aspects of Oracle E-Business Suite, it is a little light on technical training. Anyone involved in developing forms and reports has options in both beginner and advanced classes in each of those subjects as well as in PL/SQL Programming units, but for the Apps DBA, the offerings are somewhat slim. There is a class on migration from 10.7 to 11i through Oracle University as well as one on 11i E-Business Suite Essentials for Implementers that targets both functional and technical implementers. Attending an Oracle University course is not necessary if you can find any class on administering Oracle E-Business Suite 11i that will give you some idea of the different aspects that you will be dealing with, preferably one that has hands on training, will help you in the long run. If nothing else, it will put you into a room with other people who are facing similar situations to yours and who are likely at different skill levels in relation to the product. This will give you a better understanding of what questions you want to ask when the time comes for you to be in the hot seat.

While it is not essential to have an extensive background in accounting, finance, marketing, human resources, or any of the other product modules that your end users will be dealing with, knowing something about basic business and basic accounting principles is helpful. For instance, you should understand that debits and credits have the opposite effect on the company's finances than in balancing your checkbook, that there are cash inflows and cash outflows, and that accounts payable is money going out of the company and accounts receivable is money coming into the company. You should also have basic knowledge of what your HR, marketing, and other departments do (depending on the modules that you are using). It may not help you to administer the application any better, but having a frame of reference when dealing with the end users allows you to not have a deer-in-the-headlights look when they start talking in their language.

Helpful Hints

Do Not be Afraid to Admit You Do Not Know

Not knowing something is nothing to be ashamed of. Not knowing about some aspect of a software solution of this size, complexity, and power is to be expected, regardless of how long you have been an Apps administrator. Not knowing the answers to a lot of questions if you are the one who has been elected to make the leap from Oracle DBA to Oracle Apps DBA (it is a little more than the database) is pretty much a given.

Not knowing and being willing to admit to not knowing, though, are two different things:

"Can you handle the position?"

"Don't know, probably can. Heck, I never thought I could handle the one I'm in now."

"Why is the system down?"

"Not a clue. It was working when I left yesterday."

"When will you have it back up?"

"I have an open iTAR. I am working on the problem. Don't know when it will be back up, but as soon as I can get it there, you will be the first to know."

"How does this <insert service here> work?"

"Don't know. I can't find that in the manual anywhere, but I will keep looking."

The worst anyone can say if you admit you do not know is that you are honest and the best that can happen is that you get some slack and maybe some additional resources (e.g., a class, a book, or someone you can ask these questions of).

Do Not be Afraid to Say No

If you are working with a contracting company that is responsible for a significant part of your implementation and the rented project manager decides that you (the resident DBA assigned to the job) need to work the next 38 hours straight while he spends the weekend with his family lounging around a hotel pool, calling you every hour to make sure that you are really on the job, and does not tell anyone at the company (other than you) that those are the expectations, say no. There is no reason to hide from your company or your direct management what is being requested, especially if you know that the request is being made outside of common channels.

Take Notes

This one is not one of my favorites. Notes are too much like documentation and I really do not like writing documentation, but these notes will end up being your best friends in the end. The notes do not have to be elaborate. Mine were on multi-colored sticky notes that ended up stuck all over the front of notebooks, on manila file folders, on the monitor of the server I was working at, and on top of other sticky notes with other notes on them. My methods were the talk of the team and were often pointed at in ridicule, but there is nothing wrong with a method if it works. Go back when you have a few minutes and write out the situation, the question, and the answer that you got. If you asked a question of another sysadmin and you got an answer that made sense and that helped you once, write down both the question and an annotated version of the answer. You never know when you might have the same situation again and you will still have the answer. This is important if you are in the middle of patching a long series of patches, you have been at it 18 hours, and, by the looks of it, you have several more hours to go. It is the time that you will not be able to remember what was said, why it was said, or in reference to what, and it is probably when the best information will end up coming out.

If you ask a question on a listserv and you get an answer that either helps you with the situation or points you at something that you did not know before (even if it does not help with the situation at hand), print out the mail, make note of who responded, and keep that information.

If you have a patch that goes badly, make notes on what patch, what went bad, where you looked to find it, and what you did to fix it. Any contact information you can accumulate in your journey through this desert should be kept as well. You never know when you might have a similar question from someone who has been a big help before.

If you can get your notes into electronic format and store them all in the same place on a network, you or anyone working on a portion of the application could access these notes and search for a similar problem that they are having. It may mean that you get an extra hour's sleep if they can use your notes as well as Metalink's notes, instead of calling you in the middle of the night.

Upgrade or Migrate

You have an installation, now you are looking at upgrading. Upgrading refers to bringing a 10.7 or 11.0 implementation to 11i, migrating means going from, for example, 11.5.5 to 11.5.8. What is involved is not really very different and the same logical processes are involved. This means that the manner in which you have to proceed, the amount of time, and difficulty level you will face depends (to a great extent) on what version you are upgrading from and how much data you have to upgrade. While every installation and, therefore, every upgrade, is unique, there are many things that you will need to do regardless of what version you are upgrading from, what modules you have installed and licensed, what customizations you have done, or any other particulars unique to your implementation.

Here we will start looking at some of the common tasks that you will be faced with performing as well as some of the challenges and opportunities that you will be faced with.

Gather Information

If you are upgrading from Version 10.7 or 11 to 11i, you will find that a good bit has changed. If you are upgrading one version of 11i to another, this will be addressed in the migration section following this one. Some of the things that have changed between the earlier significant releases are enhanced and renamed versions of what you are used to, some are completely retooled, and much is entirely new. There are new modules and new functionality.

An upgrade from Version 10 to 11i is a lot like a new implementation. Because 11i is based on the Oracle 8i database and its features, many things that worked one way in Version 10 will not work the same way in 11i because of the differences in the rule-based and cost-based optimizers.

Oracle has tools and information that will help make the upgrade process more simple and elegant.

Will your current hardware configuration be sufficient to not only support the new configuration, but also both configurations for a time? Is the network servicing the current configuration going to be robust enough to withstand the Internet/intranet-based product taking a bite out of the available bandwidth? Is the configuration you are planning (database version, apps version, and OSs) certified by Oracle Support? Check out the certification section of Metalink before you start to make sure.

Have a Plan

In this regard, upgrading is not too different from installing a new implementation. It is critical to plan out what needs to happen and when it has to be done. If you have an absolute hard and fast completion date, you will have to start there and back calculate. Build in slack time in each environment for things to go wrong and time to resolve these problems.

You do not have to just plan at one level. Create a high-level task list with key milestones, so that everyone knows what needs to happen in a broad sense. A task-by-task, patch-by-patch, driver-by-driver low-level plan will help the technical types make sure that everything gets applied and nothing gets missed.

The high-level task list can start well in advance of the physical beginning of the upgrade process with researching and learning as much about what to expect in the upcoming upgrade and the changes that can be anticipated in functionality. A sample high-level task list could take the form of a formal project plan; however, at the earliest stages exact dependencies may not be known. A good beginning could be as simple as a text document in an outline format.

When formulating all levels of plans, take into account known and potential customizations as well as data feeds into and out of your current Apps installations and how the upgrade may affect the current system. You have to take into account whether the feeds are direct loads into data tables or (as is the better scenario) by means of the interface tables.

Make sure that you have enough of the right resources for the upgrade process and sufficient hardware for all of your environments on each server involved. You will want to revert one of the environments that you upgrade earliest to a training environment. Think of your first upgraded system in this way: it will have been your training server for your upgrade process and therefore can (once it is up and operational) work as the training area for end users and developers who will have to be using the product when the full upgrade cycle is completed.

Resources are not just hardware and network resources. People resources are every bit as important as, if not more important than, computing resources. One person cannot do it alone. Even on a maintenance release upgrade, it takes several technical people working in shifts to make sure that all patches get installed and all problems get resolved in a timely manner.

Include dealing with your current customizations in your plan. Some may no longer be necessary; others may no longer be functional. Make sure that you have allotted time for dealing with the customizations, regardless of the outcome.

Finally, create a plan for training. It is a whole new world in 11i. If you are upgrading from one maintenance pack to another, you should not have to retrain, only test and learn first hand. If you are upgrading from Version 10.7 or 11.0, you will likely see some significant changes. Even if your training is just spending time on one of your newly upgraded environments learning what has changed and the new ways to do things, adding time into the schedule for training will pay off in the end.

The following list contains all the elements that I recommend including in your plan.

Plans List:

1. Gather upgrade information

   • Oracle

   • Oracle Applications Users Group (OAUG)

   • Apps Net

   • Listservs

   • Consulting firms

   • Metalink

2. Analyze current environment

   • Database tier

   • Client tier

   • Data feeds

   • Network

   • Customizations

3. Analyze potential new environments

   • Database tier

   • Middle tier

   • End user tier

   • Data feeds

   • Network

   • Customizations

   • Changes in business needs

4. Test Plans

   • Environment

   • Functionality to test

   • Customization

   • Technical

   • Business

   • Develop test scripts and centralize

5. Training

   • New technical functionality

   • New functional functionality

6. Installation/upgrade/patch

7. Deal with customizations

8. Carry out test plans

9. Postupgrade meetings

Beware of Scope Creep Even Here

It is easy to get caught up in the idea of all of the new features, new functionality, and modules. However, now is not the time to introduce new modules or to license new products. Keep the upgrade process as simple as you can. Once you get through upgrading and prove that your current functionality is working as you anticipate, then you can start another project or a new phase of your upgrade process. Adding extra complexity in the midst of the upgrade will just muddy the waters and will make things extra complex. It will also impact your ability to complete the upgrade on time. Everything that you do in your test and development environments will carry through to your production environment and will mean additional downtime for your users. Smaller chunks of downtime would be better in the long run than one big outage.

Determine What Has Changed between What You Are Upgrading From and 11i

If you are upgrading from Version 10.7 or 11.0 to 11i, you can plan on needing more hardware. This can be anywhere from 30 to 40 percent more disk space for each of the tiers. On the Apps layer, this extra allows for space for the new modules and increased functionality of the application. On the database tier, this allows for the data files for the new modules if you were not implementing the new products (it will mean even more disk space if you are implementing the new products) and the additional space needed for the Oracle_Home.

Disk space is only part of the increase in hardware that will go along with an upgrade. You will need to increase the bandwidth available for Apps to make use of by between 10 and 15 percent and the memory available for its use by 15 to 20 percent.

Functionality will have changed considerably, as well. You will be moving to a truly thin client installation where all changes and upgrades are done at the server and the PC only requires a browser and JInitiator to function. This is the primary reason for the extra, required bandwidth.

Once you get to 11i, you will find that it takes advantage of invoker's rights on package, function, and procedure execution. This means that you can actually save space because you do not have to store the same procedure several times if you have multiple sets of books, which makes life as administrator easier, as well.

Additional support for new languages and enhanced support of country specific functionality are two more additions that will improve your ability to serve your end users, but will also make supporting the application more resource intensive.

Decide When to Upgrade

When you are implementing, it is probably a good idea to implement the latest version. In fact, if you are looking to go live 60 days or less following the release date of a maintenance pack, Oracle suggests that you do implement that new release. I believe the theory here is that it is freshest in the minds of the development team and if there were problems, getting them ironed out would be quick and fairly painless.

But do you want to upgrade from one major release (11 to 11i or 11i to 12) as soon as it is released? Probably not. What is the impetus for the upgrade? Are you going to have to upgrade both the application layer and the database to stay on certified configurations because you have to swap out leased equipment? Is the upgrade prompted because your current environment is next on the list to be desupported?

Waiting until there is a good reason to upgrade is a good decision. Waiting until the very last minute to try to hurry through an upgrade is not a good decision. Mistakes are made when you have an unmovable deadline and need to hurry too fast. Waiting too long can mean the additional cost of having to extend leases on machines that are being swapped out when the project plan does not go as planned and ends up taking longer than anticipated. Worse, if you wait until you get a desupport notice or you discover that your versions are not going to be supported past a certain date and then start the consideration process on your way to upgrading, you may be in an unsupported state before the end of the process. The information technology industry is the prime example of a place where if anything bad can happen, it will and at the worst possible time. It is a bad position to be in with a fully functional production system that breaks in the middle of your upgrade plan and you have no environment at the same release configuration as production (even worse if you have just outlived Oracle's final support date).

Bookmark Metalink

If you have a support contract, Metalink can be invaluable in your upgrade process. They will walk you through issues that arise in your installations and upgrades. They will help you determine what went wrong in a patching situation. They will steer you to documentation that you probably would not have found on your own otherwise. An upgrade can mean dozens of iTARs. Some are resolved in under an hour. Some may not be resolved completely until long after your upgrade is completed.

If you do not have a support contract, get one. It is the only way to stay current on patches and to get help with those unexpected opportunities for adventure that will likely come your way. Learn to navigate Metalink to find the resources that are buried within its site. This will become your most used site.

Have a Team

An 11i upgrade, regardless of whether you are upgrading from Version 10 or 11 to 11i or you are just upgrading within 11i from one major release to another, requires a team of individuals who have different bases of knowledge and skills. You will need technical people. You will need people who have knowledge of hardware and software configuration. You will need a technical expert on Apps architecture and of the utilities called into service during the upgrade process. You will also need people from the functional side. It requires someone who has knowledge of business processes and how they are accomplished with Oracle Applications. Further, you should have on your team someone who is responsible for the design, development implementation, and maintenance of the customizations for your installation. These people should have some knowledge of how things are done at whatever level you are upgrading from and will do some research on what the upgrade will mean to their area of expertise.

This team will need to work closely together to achieve the best end result.

Back Up to a Fallback Point

Back up often and verify the backup. Always make a backup when you have gotten to the end of a big chunk of processing that you might want to be able to fall back to. This is particularly important if you have upgraded part way and done some testing and everything appears functional. Having a backup at this point would mean that if you started the next round of processing and ran into a problem that you could not get through with Oracle Support's help in a reasonable amount of time, you can restore to a closer operational point than falling completely back to the beginning. Rerunning a patch or a procedure that has had problems once, even rerunning it with all of the same surrounding processes in place may be fully successful. The converse can also be true, rerunning a patch or process that has been successful once might fail completely under similar conditions. Limiting what has to be rerun to get another process to run successfully means limiting what more might go wrong.

Upgrade

Upgrade in Phases, Database First

This phase of the project includes installing an Oracle certified release of the RDBMS, the Tools, and Applications that are included in the set of RDBMS binaries. InterMedia, InterMedia Text, and Spatial are ones that you will find necessary along with SQL*Plus and the typical pieces of the database.

Both Release 10.7 and 11.0 are certified to run on an Oracle 8i database (8.1.7). If you have not already upgraded your database to this point, your first step in your upgrade plan should be to get your database to this point. This will allow you to resize objects in the newer version and start to migrate some of the obsolete functionality to the newer version. Bringing your database to Oracle 8i before you start the remainder of the upgrade will mean that you can attribute problems that arise because of the database to the database and not have to fight with dozens of changes at the same time when trying to hunt down the cause of problems. You will maximize your testing and limit downtime; although, you will have downtime broken into several smaller chunks, so users will be asked to stay out of the system several times during the duration of the process.

Interoperability

In this case, interoperability means allowing your current applications tier version to run against an Oracle 8i database. This allows you to upgrade in phases; it also allows you to make use of features in your current configuration to determine what changes you can expect later with database performance. This includes the base product as well as your customizations. It can also allow you to start to migrate to the cost based optimizer.

If you are using an Oracle 7 server that is any release lower than 7.3.4, you will have to upgrade the technology stack to this release and apply any associated interoperability patches before you start your upgrade to Oracle 8i Enterprise Edition.

Further, you will have to make sure that the minipack patch levels for payables, receivables, sales and marketing, global accounting engine, order entry, and purchasing are all at least those in Table 9.3.

Table 9.3: Installed Minipack Levels

Product	Patch Level
Payables	AP V
Receivables	AR W
Sales and Marketing	AS H
Global Accounting Engine	AX F
Order Entry	OE J
Purchasing	PO M

Once you have upgraded your database to Oracle 8i, you will have to make some changes in the init.ora file for your database. Several of the initialization parameters have changed names and others have become obsolete. Table 9.4 lists all of the renamed parameters and their new names and Table 9.5 lists some of the main obsolete parameters in 8.1.7 that you may have to remove. The database may or may not start with these parameters incorrect; if it does, it will place warnings in your alert logs at startup and you may not be able to take advantage of the new parameter features. Removing the obsolete parameters entirely would be the safest solution. You can either change or delete the old parameters or you can comment the old ones out and add the renamed ones below, deleting the old values later when you are comfortable with your upgrade.

Table 9.4: Init.ora Renamed Parameters

Old Initialization Parameter name	Renamed Value
ASYNC_READ	DISK_ASYNCH_IO
ASYNC_WRITE	DISK_ASYNCH_IO
CCF_IO_SIZE *	DB_FILE_DIRECT_IO_COUNT *
DB_FILE_STANDBY_NAME_CONVERT	DB_FILE_NAME_CONVERT
DB_WRITERS	DBWR_IO_SLAVES
LOG_FILE_STANDBY_NAME_CONVERT	LOG_FILE_NAME_CONVERT
SNAPSHOT_REFRESH_INTERVAL	JOB_QUEUE_INTERVAL

 ARCH_IO_SLAVES B_TREE_BITMAP_PLANS CLEANUP_ROLLBACK_ENTRIES COMPATIBLE_NO_RECOVERY CACHE_SIZE_THRESHOLD DB_BLOCK_CHECKPOINT_BATCH FREEZE_DB_FOR_FAST_INSTANCE_RECOVERY DB_BLOCK_LRU_STATISTICS DISCRETE_TRANSACTIONS_ENABLED FAST_FULL_SCAN_ENABLED DB_FILE_SIMULTANEOUS_WRITES JOB_QUEUE_KEEP_CONNECTIONS DB_BLOCK_LRU_EXTENDED_STATISTICS LGWR_IO_SLAVES COMPLEX_VIEW_MERGING CLOSE_CACHED_OPEN_CURSORS PARALLEL_DEFAULT_MAX_SCANS UNLIMITED_ROLLBACK_SEGMENTS PARALLEL_DEFAULT_SCAN_SIZE OPTIMIZER_PARALLEL_PASS SEQUENCE_CACHE_HASH_BUCKETS LM_NON_FAULT_TOLERANT IPQ_ADDRESS IPQ_NET IO_TIMEOUT INIT_SQL_FILES CHECKPOINT_PROCESS FAST_CACHE_FLUSH LARGE_POOL_MIN_ALLOC LOG_ARCHIVE_BUFFER_SIZE LOCK_SGA_AREAS LOG_ARCHIVE_BUFFERS LOG_FILES LOG_BLOCK_CHECKSUM LOG_SIMULTANEOUS_COPIES LOG_SMALL_ENTRY_MAX_SIZE PARALLEL_DEFAULT_MAX_INSTANCES PARALLEL_SERVER_IDLE_TIME PARALLEL_MIN_MESSAGE_POOL PUSH_JOIN_PREDICATE PARALLEL_TRANSACTION_RESOURCE_TIMEOUT ROW_CACHE_CURSORS SEQUENCE_CACHE_ENTRIES SEQUENCE_CACHE_HASH_BUCKETS SHARED_POOL_RESERVED_MIN_ALLOC SNAPSHOT_REFRESH_KEEP_CONNECTIONS SNAPSHOT_REFRESH_PROCESSES SORT_SPACEMAP_SIZE SORT_READ_FAC SORT_WRITE_BUFFERS 

Note, you cannot just change the name of CCF_IO_SIZE to DB_FILE_DIRECT_IO_COUNT as what they are calculated in has changed. CCF_IO_SIZE was in bytes, while DB_FILE_DIRECT_IO_COUNT is in database blocks.

Further, there are two new events that are required to be in your initialization parameters to maintain compatibility of the Oracle 7 and early Oracle 8 constructs in the Oracle 8i database. Add them to your init.ora file if they are not already there.

 event = "10929 trace name context forever" event = "10932 trace name context level 2" 

As with any installation of a different version of the database on the same machine, you will need to make sure that you reset your ORACLE_HOME, PATH, and ORA_NLS as well as LD_LIBRARY_PATH to point to your new environment and make sure that your oraInst.loc file has been renamed so you do not install your oraInventory into the same one as your old installation. This can cause inconsistencies and the inability to patch either database at a later time.

Follow the Oracle 8i installation guide for your platform to perform the installation, installing at least Oracle 8i server, Net8 Server, Net8 Client, and Oracle Utilities. Do not run the migration scripts at this time. Apply the latest certified Oracle 8i patch set to the new RDBMS binaries, but do not run any SQL scripts documented in the release notes yet.

Your new installation will take up considerably more room than the old version did. You will need to make sure that your SYSTEM tablespace is at least 500 MB (best to make sure that there is at least 150 MB free in this tablespace) and that you have at least 300 MB in your rollback segments.

Now, if you are on Oracle Version 7.3.4, you will need to migrate (not upgrade) your database to Oracle 8i Enterprise Edition. If you are on Version 8 of the database (8.0.#), you will need to upgrade your database to Oracle 8i. The Oracle 8i Migration Manual available either through Oracle Technology Net or http://tahiti.oracle.com can give you the steps necessary to accomplish this, regardless of which version you are currently on. Upgrade your database only. Configure your new Net8 Listener and start the listener.

You will need to set up a SQL*Net client on the machine where your Release 10.7 or 11.0 is installed as the technology stack manager (not the Forms Server administrator, if these are two different users). Make sure that all of the environment settings, at this point, are referring to your Oracle 7 server (7.3.4) technology stack environment (this has not changed yet). Double-check ORACLE_HOME, PATH, ORA_NLS, and LD_LIBRARY_PATH to make sure that nothing changed for any reason. Now configure the SQL*Net client so your 10.7 file system can connect to your newly upgraded Oracle 8i database. If you need help setting up the SQL*Net client, the manuals for Oracle 8i are still available on http://otn.oracle.com/documentation or on http://tahiti.oracle.com.

Apply interoperability patches that will allow your 10.7 or 11.0 functionality to continue under the new Oracle 8i database. Oracle continues to make updates and modifications to these patches, so get the most recent version from Metalink to make sure that you get the most stable and supported version of the patches.

Compile invalid objects. You just upgraded the database. You will, without a doubt, have dozens (possibly tens of thousands) of invalid objects. Nearly all of the database objects can be invalid at this point due to the migration/upgrade process. If you have never used utlrp.sql (found in $ORACLE_HOME/rdbms/admin on UNIX or %ORACLE_HOME\rdbms\admin if your database is on a Windows platform), it is the utility that recompiles invalid objects. It can run for hours, depending on how many invalid objects you have in your database (select count (*) from all_objects where status = 'INVALID';). There will be no feedback, but opening another session and rerunning the above select statement in another session will prove that it is still working when the number of invalid objects continues to go down. Run this script as sys, internal, or sysdba so you are able to recompile all invalid objects. Once utlrp has finished, you can use ADADMIN (AD administrator), choose maintain database objects, and compile APPS schema option. Again, this may take a while, depending on how many remaining APPS invalid objects are in the database. (Select count(*) from all_objects where status = 'INVALID' and owner = 'APPS';)

Create a Temporary Tablespace

Once you have gotten your database upgraded or migrated to Oracle 8i, you can start to implement the features of the newer release. One that will help you most in the rest of your migration to 11i will be the creation of a Temporary Tablespace. Create this tablespace as temporary and locally managed with uniform allocation. Be careful when you create this tablespace, however. When Oracle creates a temporary table, go out to the OS and look at the file specifications. Temporary tables are usually created as sparse, which means Oracle knows how big they are allowed to get, but can create them significantly smaller. The OS does not know this; it just knows it has a file of whatever size Oracle chooses to create. So when you create them, it may look like you have more than enough room in the file system for growth, but when you start using the Temporary Tablespace, it starts filling up; Oracle starts allocating more and more of the space it knows belongs to the Temporary Tablespace. When it runs up against the end of the file system and Oracle is still trying to make use of the space that has not yet been physically allocated to the tablespace, the database can hang and become unresponsive to any queries or processing. At this point, you will have to relocate one or more of the files making up your Temporary Tablespace to make sure that you will have sufficient space to use when you restart the upgrade/migration process. To make sure that Oracle physically allocates all of the space to your tablespace that it believes it will need, create the tablespace as permanent. Then drop the tablespace and create it again as temporary using the same data files. The first create statement causes the files to be fully allocated; the second makes use of them and causes them to be optimized for the Temporary Tablespace.

Once you have this Temporary Tablespace defined in this way, adding a data file to the tablespace is not as easy as it used to be. You can no longer just add a data file with alter tablespace add datafile command. You have to add a file with the alter database command. You will need to size your extents based on your configuration and environment. The 16K extents in the example are only used as an example; you do not have to use these sizes.

• Create Temporary Tablespace Appstemp tempfile'/u02/proddata/prod/appstemp01.dbf' size 2000 M reuse extent management local uniform size 16K.

• Add a data file.

   Alter database add tempfile  '/u02/ proddata/prod/appstemp02.dbf' size 1000 M; 

• Resize a data file.

   Alter database tempfile  '/u02/ proddata/prod/appstemp02.dbf' size 2000 M; 

• Drop a data file.

   Alter database tempfile  '/u02/ proddata/prod/appstemp02.dbf'  drop; 

Migrate Existing Nonsystem Tablespaces to Locally Managed

To take advantage of the new performance features that locally managed tablespaces bring in Oracle 8i, you need to migrate existing tablespaces to locally managed tablespaces. This is also the direction that Oracle is going as far as the supported way to create tablespaces. In Oracle 9i (depending on how you create your database), it will be the default manner that the system tablespace is created at database creation time.

There are two ways you can accomplish migrating your existing tablespaces to locally managed tablespaces. You can run the adtb-scnv.pls script that is supposed to be located under your APPL_TOP under admin/preupg directory. Log in to sqlplus as system to run the script, which will accept the system password as a parameter. Alternatively, you can use the DBMS_SPACE_ADMIN package and your existing v$tablespace view to use sql to create the sql to do this. This would be the more dynamic way to accomplish this and would allow you to make sure that you get all of the tablespaces in your database, even the custom or user tablespaces that are not directly related to Apps. It would also give you the flexibility to determine if there are any tablespaces, other than system, that you do not yet want to make locally managed and exclude them from the resulting list. Further, with DBMS_SPACE_ADMIN, you can migrate tables back and forth between locally and dictionary managed.

 Select 'execute dbms_space_admin.tablespace_migrate_to_local('||ta blespace_name||', <your desired minimum extent size>);'from v$tablepaces; 

---