Chapter 1: Oracle Database 11g Getting Started 11
or you can enter an alternative directory location for these files to be stored in. We will
discuss the disgnostic_dest parameter and other new parameter-related information
later in this chapter.
As with previous versions of the DBUA, you can choose to have Oracle back up
the database before the upgrade, or you can indicate that you have already backed
up the database. If you allow the DBUA to back up the database, then the backup
will be a cold backup, so be aware that you will be adding to the total time the
database will be down for the upgrade if you are going to have DBUA perform the
backup.
If you want to perform your own backup, I’d suggest you use Recovery Manager
(RMAN) to perform the backup. Oracle Press has a great book on RMAN called
Oracle Database 10g RMAN Backup and Recovery that can guide you on your
RMAN backup. Here is an example of an RMAN command that you can use to
back up your database:
RMAN> shutdown immediate
RMAN> startup restrict
RMAN> backup database plus archivelog
format 'backup_destination_here' tag before_upgrade;
RMAN> shutdown immediate
RMAN> startup
NOTE
Of course, backups are very important, particularly
when an upgrade fails for whatever reason. I’d prefer
to back up the database before the upgrade. If you
are using RMAN you can validate the backup and
make sure it’s complete. If you have the time and
resources, I’d do a test restore on the backup just to
cover yourself completely.
The DBUA also gives you the option of moving your database datafiles during
the upgrade process if you so desire. You can move them from file system to file
system, from a file system to ASM, or from ASM to a file system. Personally, I think
I’d do this independent of an upgrade, but that’s just me.
The DBUA also gives you the option of configuring the Flash Recovery Area (FRA).
This is particularly handy if you are moving from Oracle9i, which did not offer the
FRA, or if you did not use the FRA in previous Oracle Database 10g databases.
Another choice you will make from the DBUA is the option to recompile invalid
objects after the upgrade. This is similar to running the utlrp.sql package from the
SQL command line. The default option is to recompile packages, and I recommend
you take this option. Finally the DBUA provides the option to backup the database
(again, I prefer to do this before hand, manually). Having decided to backup or not
12 Oracle Database 11g New Features
backup, DBUA will provide a summary of your choices, and warnings related to the
database upgrade. You simply click on the finish button, and your upgrade will
begin! Note that once you start the upgrade, you will not be able to use the database
until the upgrade is complete. Figure 1-2 is an example of the DBUA window as it is
upgrading a database.
DBUA-Related Logging
Notice at the bottom of the DBUA output shown in Figure 1-2 that the logging
directory for the DBUA is listed (in our case $ORACLE_HOME/cfgtoollogs/dbua/db_
name). In this directory you will find the logs related to a DBUA upgrade. After each
upgrade you should review the logs in this directory for errors after the migration is
complete.
Note the location of the log files in the DBUA window. The logs can be very
handy in solving upgrade problems should that arise. The DBUA will create a
number of logs in the directory listed. Note that each separate upgrade of a database
will have its own log directory (thus, old logs are not removed). For example, in
Figure 1-2 the DBUA has put logs into the $ORACLE_HOME/db_1/cfgtools/dbua/
rob10dbua/upgrade2 directory. In this case we see that the rob10dbua database has
had one upgrade attempt previous to this upgrade attempt (the first upgrade attempt
would be in the upgrade1 directory).
FIGURE 1-2. The Database Upgrade Assistant: progress screen
Chapter 1: Oracle Database 11g Getting Started 13
Arup Says…
I find it extremely useful to let the log files scroll by as the entries are written to
them. In Unix-based systems, it is rather trivial. A simple command in another
terminal, tail -f , shows a continuous display of the tail end of the file.
As new material is added to it, I see it. This gives a little bit more educated
insight into the process than just looking at the upgrade screen with a slider bar
and a percentage indicator.
Within the DBUA log file directory there are a number of logs you might be
interested in. Table 1-2 provides a list of the log files of most interest to you.
How Do I Know If the Upgrade Is Successful?
Of course, if you watch the DBUA to completion, then you will know the upgrade
is successful (and that you can patiently outwait a very boring bit of screen output).
The DBUA will provide you with the screen seen in Figure 1-3 which indicates
success.
After you click on OK, the DBUA will display the Upgrade Results page. This
page provides summary information about the completed upgrade that includes
Log Name Purpose
UpgradeResults.html This is a summary of what the DBUA intends to upgrade.
This HTML file is displayed by the DBUA before the upgrade
begins.
Trace.log Provides detailed tracing information on the entire upgrade
process. Any errors reported by the DBUA will be recorded in
this log.
Oracle_Server.log This file (which can be quite large) provides details of the
execution of the entire migration project. If an error occurs
you can find more details in the text of this file. If something
in the upgrade fails, this is where you are likely to find
information pertinent to the failure.
Post_Upgrade.log Log file for details on post upgrade operations. You can look
in this file to determine if the upgrade was successful or not.
TABLE 1-2. Oracle Database 11g DBUA Logs of Interest
14 Oracle Database 11g New Features
FIGURE 1-3. The Database Upgrade Assistant: Successful Upgrade
information including the new ORACLE_HOME location, parameters that have been
added or updated, and parameters that have been removed. Also you can configure
database passwords from the DBUA Upgrade Results page.
If you feel that the upgrade was not successful in some way, the DBUA Upgrade
Results page also provides the ability to rollback the upgrade. If you had the DBUA
backup the database before it started the upgrade then DBUA will restore the database
and reset configuration parameters. If you did your own backup, then this option will
only reset various configuration settings and you will need to manually restore the
database. Figure 1-4 provides an example of the DBUA Upgrade Results page.
Once your upgrade is complete, you should backup your new database again.
Also, backing up other related database files that have changed (like the listener.ora,
or the tnsnames.ora) would be a good idea after a successful upgrade.
It may be that you will walk away from the process and then during your
absence some horrific thing like the system rebooting will occur (or as happened in
my case, your dog gets under your desk and kills the power). So, how do you know
if the upgrade was successful in this case? You will want to do the following:
1. Review the Oracle_Server.log (see the previous section for more on this log
file). Look for ORA- errors in the log. If there are any in the log that are not
expected, you will want to check with Oracle and determine what needs to
be done. One problem is that our friends at Oracle have filled this log with
Chapter 1: Oracle Database 11g Getting Started 15
comments that include ORA- in them, so a simple search and find will not
work very well. Many times I will just go to the bottom of the log and look
to see if there is an error there or near the bottom. Often if an error occurs it
will be toward the end of the log.
Another place to look is the Post_Upgrade.log file. This logs all operations
that occur after DBUA has actually upgraded the database. Look at the
bottom of the file for a successful call to dbms_registry_sys.validate_
components call. If it was completed successfully, then odds are that your
upgrade was successful.
2. Check the alert log of the database for errors during the migration. There
have been some changes to the way the alert log is managed. See Chapter 2
for more details on the new diagnostic_dest parameter and how it impacts
database logging.
3. You can check the DBA_REGISTRY view to make sure all of the components
have the correct version number assigned to them. If they do not, you will
need to determine why this is the case (it may be as simple as a bug where
one of the components is not getting updated correctly in the registry; that
has happened before).
FIGURE 1-4. The Database Upgrade Assistant: Upgrade Results
16 Oracle Database 11g New Features
Going Back
So, what if you see some errors during the upgrade process and the DBUA failed?
What if you find errors in the logs and you want to go back? If you had DBUA
backup your database, you can have it restore your database. In other cases, you are
going to have to restore the database that failed to migrate yourself.
In the DBUA interface, the last screen will give you an option to recover your
database if there was an error on the DBUA. However, as in our earlier example, if
the power went out and the DBUA session ended as a result, you no longer have
that option. This is one reason I just prefer to do a manual backup/restore. DBUA
does place all the files used to backup/restore your database into the logging
directory. Therefore another option is to go to the logging directory and use the files
contained there.
In the case of a power outage (or perhaps the Blue Screen of Death from
Windows), you need to review the logs carefully. If you can determine from the logs
that the upgrade process failed, you can manually restart the upgrade process from
that point. However, if you prefer to stick to using the DBUA to do your upgrades,
then the best course of action will be to recover the pre-upgrade database image
from the backup you took before the upgrade, and restart the upgrade.
DBUA does a simple copy of your data files when it does a backup rather than
use RMAN. You will find your backup files in the directory ORACLE_BASE/admin/
/backup. Here you will find a script file or batch file (the name
varies by platform) that you can run to restore your database to the pre-Oracle
Database 11g version. If you are running the script on Windows, it will also drop
and re-create the Oracle Windows service for you.
If you backed up your database via RMAN before the upgrade, then restoring
the database is as simple as issuing the following RMAN command (note we assume
you tagged your backup with the tag “before_upgrade”).
STARTUP NOMOUNT
RUN
{
RESTORE CONTROLFILE FROM 'save_controlfile_location';
ALTER DATABASE MOUNT;
RESTORE DATABASE FROM TAG before_upgrade
RECOVER DATABASE NOREDO;
ALTER DATABASE OPEN RESETLOGS;
}
After the restore is complete, you will need to reset your environment variables
to point to the old Oracle software locations. If you are using Windows you will
need to drop and re-create your Oracle service.
Chapter 1: Oracle Database 11g Getting Started 17
Manual Upgrades
The downside to manual upgrades is that they can be tedious and you have to
manage a number of steps. The upside is that you have a great deal more control
over the upgrade process. If something fails and you are doing a manual upgrade, it
is often much easier to recover from that failure than if you are using the DBUA.
If you are going to opt for a manual upgrade, the first thing I’d do is read the
upgrade manual carefully. I’d then create a checklist for you to follow. Each of the
upgrade steps can vary a little bit by platform (for example, Windows installs require
that you drop and re-create services). So it’s important to read the manual.
In the next sections we will divide the manual process up into pre-upgrade,
upgrade, and post-upgrade sections. In each section we will provide some direction
and insight into that part of the upgrade process. Finally, we will discuss rolling
back the upgrade if that becomes necessary.
Before You Upgrade to Oracle Database 11g
Before you just haul off and run the database upgrade scripts, a bit of pre-planning is
in order. While we present an ordered list in Table 1-3 of pre-upgrade steps to
follow, you must reference the Oracle Database 11g Upgrade Manual as well as the
Readme and other related files for the most current information on the steps to follow
when upgrading. We have found in the past that things tend to change between
different versions (and operating systems), and of course the specific version you are
upgrading to may be different than the version we used when we wrote this book
(we used the first production version of Oracle Database 11g for this book).
Another thing we must mention (again) is that you need to test, test, test (we
actually had 10 pages of the word “test” here, but our editor/publisher decided that
might be a bit much) before you do anything else. Table 1-3 provides a summary of
the pre-upgrade steps that you will want to make sure you take when performing an
Oracle Database 11g upgrade:
You will notice we mentioned the Oracle Pre-Upgrade Information Tool in step 8
in Table 1-3. It is critical to the smooth upgrade of your database that you run this
tool every time you do an upgrade. I have seen cases where the tool was run on
development and test databases without any problem findings being noted, only to
have an upgrade fail in production. This was because the DBA assumed that there
would be no problems with the production upgrade since there were not any
problems in the other upgrades. Don’t make this mistake.
NOTE
If you are upgrading a clustered database, you will shut
down all but one of the instances of the cluster, which
will be the node you upgrade. Check out the Oracle
upgrade documentation for specific actions that might
need to be completed on each node (such as installing
the new software on each node and so on).
18 Oracle Database 11g New Features
Step Action
1 Read this book! Read the Oracle Database Upgrade guide!
2 Upgrade your OS and any other vendor software as required to support Oracle Database 11g.
3 Install the Oracle Database 11g software. I always like to create a little test database after
installing the Oracle software just to make sure everything works right.
4 Test the upgrade on a non-production database first!
5 Back up the database. (Earlier in this chapter we provided you with an example RMAN script that
you can use.)
6 Prepare the new oracle_home location. Copy the old configuration files (SPFILE, IFILE, password
file, and so on) to the new Oracle Database 11g locations. Review these files and update them to
include any new or changed parameters.
7 Check the redo log file size and ensure that it is greater than 4MB in size. The Oracle Database
11g upgrade process will fail if the online redo logs are smaller than 4MB in size. You can run
this query to determine the size of the online redo logs:
Select name, bytes
FROM V$LOGFILE;
8 Run the Oracle Pre-Upgrade Information Tool (utlu111i.sql in our version) to determine what you
will need to change in your database to make the upgrade successful. You will find this tool in
ORACLE_HOME/rdbms/admin directory of your Oracle Database 11g software install. Changes
you may need to make include:
a. Remove obsolete database parameters.
b. Adjust parameter settings to reflect minimum values indicated by the output of the pre-
upgrade tool. For example the sga_target parameter might need to be increased.
c. Increase tablespace sizes.
d. You may wish to adjust the compatible parameter to 11.0 so you can use the new features
of Oracle Database 11g after the upgrade. Note that once you modify the compatible
parameter, you cannot change the compatible parameter to a lower setting without
recovering your database to a point in time that was before the change of the compatible
parameter.
Note that during the upgrade, the compatible parameter must be set to at least 10.0.0. You
can reset it to 9.2.0 after the upgrade if you wish to ensure that you can only use the 9.2.0
feature set.
e. Adjust all paths in the parameter file to reflect the new oracle_home structure as needed.
f. If you are going to upgrade a cluster, make sure cluster_database is set to false for the
upgrade.
9 Determine if there are any new Oracle parameters that you want to use. Determine if there are
any parameters that you want to change. You will make these changes after the database upgrade.
10 Determine if any users are currently using the CONNECT role. This role is depreciated in Oracle
Database 11g and has all privileges stripped from it except the create session privilege.
11 If you are using OEM, you will want to save your OEM Control Data should you need to
downgrade. Refer to the Oracle Database 11g Upgrade Manual for more information on this
process.
12 Create a listener for the Oracle Database 11g Database. This will need to be done before you can
upgrade to Oracle Database 11g.
TABLE 1-3. Oracle Database 11g Pre-Upgrade Steps
Chapter 1: Oracle Database 11g Getting Started 19
Upgrade to Oracle Database 11g
Once all the pre-upgrade work is done, it’s time for the fun part, upgrading the
database! Cowboys on the American plains might have said “yeeeehhhhaaawwww”
at this point. Again we provide a table with a general list of steps to follow when
upgrading your database. I can’t say it enough—please check out the Oracle
upgrade manual and make sure nothing has changed or that there are no OS-
specific things you need to do. Table 1-4 presents my list.
One of two things, lack of memory or lack of tablespace space, causes many
upgrade failures. If your failure is due to one of these, you can simply correct the
problem (for example, increase memory, extend the tablespace, or enable
autoextend) and then shutdown abort the database. Then restart the database with
the startup upgrade command and rerun the catupgrd.sql script again.
If you have started the upgrade with the catupgrd.sql script and you determine
that for whatever reason you cannot complete it, you will need to restore your
database with the backup you took of it. There is no “flashing back” an incomplete
upgrade.
Re-Run the Upgrade
In the case of an error during a manual upgrade, you can often re-run the upgrade.
Simply follow these steps:
1. Correct the problem.
2. Shutdown the database (shutdown immediate).
3. Restart the database with the startup upgrade command.
4. Re-start the upgrade process from step 9 in Table 1-4.
After You Upgrade to Oracle Database 11g
Once the upgrade script has completed the upgrade, you are almost done! Now we
need to perform some post-upgrade steps to check the upgrade status and complete
the process. One last time we will provide a table with a general list of steps to follow
when upgrading your database. Also one last time we remind you to first check out
the Oracle upgrade manual and make sure nothing has changed. Table 1-5 gives you
our list.
NOTE
We can’t say it enough: These are the general
steps you will need to take. You must reference the
upgrade guide, and your OS-specific documentation
for the complete enchilada! Don’t cry for me,
Argentina—you must prepare before you do!
20 Oracle Database 11g New Features
Step Action
1 Shut down the database. You should shut down the database in a consistent
manner using shutdown immediate. If you must use shutdown abort, restart
the database in restricted mode and then do a shutdown immediate.
2 If you are using Windows, you will need to stop the Oracle service for the
database you are upgrading. You will then use the oradim utility to remove
the service for the database you are migrating. Then use the oradim utility to
re-create the new Oracle Database 11g service.
3 If you are using UNIX, you will need to make sure your environment variables
are pointing to the new Oracle Database 11g directories. This would include
oracle_home, path, classpath, and ld_library_path as well as any OS-specific
environment variables you may need to set.
4 Open a command-line window/prompt and change to the ORACLE_HOME\
rdbms\admin directory.
5 Start SQL*Plus (make sure you are using the 11g version of SQL*Plus!) and
connect to the database as a user with SYSDBA privileges.
6 From the SQL*Plus prompt, start up the database in upgrade mode using the
following command:
startup upgrade
Confirm that the banner says the database was started with Oracle Database
version 11. There is no need to stop the upgrade process if errors appear
indicating that obsolete initialization parameters are in use. You can correct
those errors after the upgrade has completed.
7 If you are upgrading from Oracle 8.1.7 or Oracle9i, you will need to create a
sysaux tablespace. Follow the direction in the upgrade guide to complete this
step.
8 Use the spool command to start spooling the results of the upgrade to a log file.
SQL> spool upgrade.log
9 Using the catupgrd.sql script, start the upgrade process!
SQL> @catupgrd.sql
Once this script has completed, it will shutdown the database.
10 Restart the newly upgraded database with the startup command.
TABLE 1-4. Oracle Database 11g Upgrade Steps
Chapter 1: Oracle Database 11g Getting Started 21
Step Action
1 Run any post-install actions required by any ancillary Oracle features that you might
have installed in your database (for example, Oracle Text). These steps will be listed in
the Oracle Database 11g upgrade guide, or in the component-specific user guide.
2 Run the post-upgrade tool (in our version, utlu111s.sql) to display the status of the
database components. Ensure that all components show a valid status.
3 Run the catuppst.sql script from $ORACLE_HOME/rdbms/admin. This script contains
upgrade related steps that do not require the database to be started in upgrade mode.
4 After the catuppst.sql script has completed, run the utlrp.sql script contained in
$ORACLE_HOME/rdbms/admin. Note that catuppst.sql and utlrp.sql can be run at the
same time. After running utlrp.sql, you should make sure that no unexpected objects
are still invalid. SQL that can help you make this determination might include:
SELECT count(*) FROM dba_invalid_objects;
SELECT distinct object_name FROM dba_invalid_objects;
5 Shut down the database. As before, you should shut down the database in a consistent
manner using shutdown immediate.
6 Remove any obsolete parameters from the parameter file. Add or change any Oracle
Database 11g-specific parameters you identified during the pre-upgrade steps.
7 Start up the database with the startup command.
8 If you are using Oracle Label Security, you will need to run olstrig.sql to re-create the
data manipulation language (DML) triggers on the tables with Oracle Label Security
policies.
9 Recompile all stored PL/SQL and Java code with utlrp.sql. Check that all packages and
classes are valid.
10 Check the component Registry (DBA_REGISTRY) and make sure that each component
has been properly upgraded.
11 Back up your database.
12 Perform any final post-upgrade tasks as required. Such tasks might include:
a. Upgrading the RMAN Recovery catalog
b. Upgrading any statistics tables that you might have created.
c. Changing passwords for newly created Oracle-supplied accounts.
d. Enabling passwords to enforce case sensitivity.
e. Enabling any other new Oracle 11g features you might wish to use.
Check the upgrade guide for a complete list of possible post-upgrade tasks that you
might need to complete.
TABLE 1-5. Oracle Database 11g Post-Upgrade Steps
22 Oracle Database 11g New Features
Be extra aware of additional things you might need to do depending on what
options you are running. For example, if you are using packages such as utl_tcp,
utl_smtp, utl_mail, utl_http, or utl_inaddr then you are going to want to review
new features revolving around Access Control Lists (ACL) in Oracle Database 11g.
Until you have configured ACL’s in 11g, you will not be able to use these functions
anymore. We discuss ACL’s in chapter 6 of this book.
Rolling Back Your Upgrade to Oracle Database 10g
In America we say, “When all else fails, punt”…. When your newly upgraded
database just isn’t working and you need to go back, what do you do? The most
obvious answer is to restore the backup you took before the upgrade. This is the
simplest and most straightforward way of rolling back an upgrade.
Oracle Database 11g supports downgrading to the 10g major version of the release
that you upgraded from (note that downgrade to 9i is not supported). So, if you went
from 10.1 to 11.1, you can downgrade to 10.1 but not to 10.2. Of course, you will need
to make sure that you have not used any new Oracle Database 11g features before you
downgrade, and the compatible parameter can not have been changed to 11. There are
some version specific downgrade requirements, depending on which version you
upgraded from. Please reference the Oracle Database Upgrade Guide for more details
with regards to downgrading to the version of Oracle you are upgrading from.
NOTE
You can use export/import to downgrade to any
previous version, but that takes a lot of time. We
discuss this topic more later in this chapter.
As a part of the pre-upgrade process, I’d strongly recommend performing a
rollback test where you roll back a test database. This way you will be familiar with
the process. You might also want to talk to Oracle support and check Metalink to
make sure there are no gotchas waiting for you if you have to rollback. The Oracle
Upgrade Manual provides a concise set of instructions on downgrading your Oracle
Database to its previous version.
NOTE
Any time you have to downgrade, you should
consider that you are at risk. If things are bad enough
with the version of the software that you are on that
you have to downgrade, you need to consider that
the software can equally go wrong when you are
trying to use it to downgrade (for example, perhaps a
bug has introduced block corruption). When crafting
an upgrade plan, you must consider the possibility
that downgrading will not be an option.
Chapter 1: Oracle Database 11g Getting Started 23
Arup Says…
Before you upgrade, you should create a script to create the control file. You
can do it very easily by issuing alter database backup controlfile to trace. This
command will generate a trace file in the user_dump_dest directory. Locate that
file, open it, trim off all the fat from the top (the stuff like the Oracle version,
date, and so on) and save it in some location as a file named cr_cntfile.sql. This
file is a script to re-create the control file. If all else fails, you can at least create
a control file from this script to restore the database to the previous version. If
that is not reason enough, consider the contents of this script file: It contains the
database parameters like maxdatafiles; the names of all the redo log and data
files; temporary tablespace files; database characterset, and many other things.
Think of this as a quick documentation of the database. You should keep this
file and the pre-11g initialization file in some safe location.
Using Export/Import for Upgrades and Rollback
You can use the Export/Import utilities (or Data Pump if you are using Oracle
Database 10g Release 1 or later) to perform both upgrades and rollbacks if you
prefer. In this section we will discuss both of these options, starting with using
export to upgrade the database. We will then discuss rolling back using export/
import. While most of these sections will also apply to using Oracle Data Pump, our
final section will address the few issues that differ when Data Pump is in use.
Upgrade with Export/Import
I’ve talked to a number of DBAs who prefer to use the export/import method of
upgrading to a new database. Using export/import is a supported migration method,
as is using the Oracle Data Pump utilities introduced in Oracle Database 10g. For a
smaller database, or for cases where you would like to move your database to
another environment, export/import can be a good solution. Smaller is key here
though, as the export/import process can take a very long time on larger databases,
certainly much longer than using the DBUA or manually upgrading a database.
The export/import method requires that you have first created an Oracle
database. You can easily create a database with the Oracle Database Configuration
Assistant, or manually if you prefer. Once that is done then you can export the
database from the database to be upgraded, and import it into your new Oracle
Database 11g database.
Export/import comes in very handy if your database is at a version of Oracle that
does not support a direct upgrade path to Oracle Database 11g. This can reduce the
time to upgrade the database, since you don’t need to perform multiple upgrades. I’ve
also run into cases in the past where we could not find the CDs to the intermediate
24 Oracle Database 11g New Features
version of Oracle that we needed to upgrade to, so we just opted to export and
import. This was just a much easier solution.
Exports from lower versions are always upward compatible. So if you are
migrating from a 7.3 database to an 11g database via export, there should be no
problems.
Downgrade with Export/Import
Downgrading via an export, such as when you are trying to roll back from an
upgrade, is a different issue. In these cases you need to make sure you are using the
correct version of the Oracle export/import utility. The general rule is that when you
are exporting from a higher version of the Oracle database with intent to import the
file into a lower-level Oracle database, then you should use the lower version of the
export and import utilities. For example, to export from Oracle Database Version
11g to Oracle Database Version 10g, you would use the Oracle Database 10g
versions of export and import.
Along with making sure you use the correct version of the export/import utilities,
you will also need to make sure that you have the correct version of the export views
loaded in the database. This only applies in cases where you are exporting from a
newer database with the intent of importing the data into an older database. For
example, suppose you intend to export from Oracle Database Version 11g to Oracle
Database Version 10g. In this case, you would first load catexp.sql from the Oracle
Database 10g ORACLE_HOME into the Oracle Database 11g database. Once
the export is complete, run catexp.sql from the Oracle Database Version 11g
ORACLE_HOME in the database to update the views to the correct version of
Oracle.
One more issue with regard to rolling back with export/import is the issue of
object compatibility. If you have started to utilize some of the features of Oracle
Database 11g in your schemas and you decide you need to roll back the database
to an earlier version, you might be in for a nasty surprise. For example, if you
exported from Oracle 8.0.5 into an Oracle Database 11g database and then created
a table using list partitioning, you would have a problem if you tried to rollback to
Oracle 8.0.5. The bottom line is: be careful after you upgrade if you make any
schema changes using new Oracle Database 11g features.
What about Oracle Data Pump?
The biggest difference when using Oracle Data Pump has to do with the issue of
version differences. Data Pump makes it so much easier to move data between
different versions of the database. Oracle Data Pump comes with a version
parameter that allows you to define the version of the database that you are creating
the export for. So, for example, if you are exporting from an Oracle 11g database
and you wish to import that file into an Oracle 10.2.0 database, you would include
Chapter 1: Oracle Database 11g Getting Started 25
version=10.2.0 in the expdp command line. As with export/import, Oracle Data
Pump can read a dump file created by an older version of the database when
importing into a newer version.
Upgrade Using Data Copying
Oracle Database 11g also supports upgrades via the SQL*Plus copy command
through database links. This is a handy way to upgrade smaller databases, or if you
wish to only upgrade a small subset of a given schema (or perhaps subsets of rows
in a given schema).
If you choose this approach you will have to create the new Oracle Database 11g
database along with the tablespaces, the needed schemas/users, and the database
links before you could begin the migration process.
Oracle Parameter Changes
Each new Oracle version includes changes to the parameters within the database.
This section covers these changes so you can consider them in your upgrade plans.
In this section we will cover new parameters, deprecated parameters (ones that still
work but you need to consider replacing), and obsolete parameters (ones that no
longer work and you need to remove). We will only be covering the more
commonly used parameters in this section. We will not cover changes to hidden,
obscure, rarely used or OS-specific parameters.
New Parameters
A number of new parameters are available in Oracle Database 11g. A number of
these new parameters will be covered in various parts of this book. Parameters
discussed in this book are marked with the symbol (*). You can reference the index
for specific pages where these parameters are discussed. The new parameters in
Oracle Database 11g include the following:
■ asm_preferred_read_failure_groups (*)
■ client_result_cache_lag (*)
■ client_result_cache_size (*)
■ commit_logging (*)
■ commit_wait (*)
■ control_management_pack_access(*)
■ db_lost_write_protect
26 Oracle Database 11g New Features
■ db_securefile
■ db_ultra_safe
■ ddl_lock_timeout (*)
■ diagnostic_dest (*)
■ global_txn_processes
■ java_jit_enabled (*)
■ ldap_directory_sysauth
■ memory_max_target (*)
■ memory_target (*)
■ optimizer_capture_sql_plan_baselines (*)
■ optimizer_use_invisible_indexes (*)
■ optimizer_use_pending_statistics (*)
■ optimizer_use_sql_plan_baselines (*)
■ parallel_io_cap_enabled
■ plscope_settings
■ redo_transport_user
■ resource_manager_cpu_allocation
■ result_cache_max_result (*)
■ result_cache_max_size (*)
■ result_cache_mode(*)
■ result_cache_remote_expiration(*)
■ sec_case_sensitive_logon(*)
■ sec_max_failed_login_attempts (*)
■ sec_protocol_error_further_action
■ sec_protocol_error_trace_action
Chapter 1: Oracle Database 11g Getting Started 27
■ sec_return_server_release_banner
■ xml_db_events
Deprecated Parameters
Deprecated parameters are parameters that Oracle eventually plans on making
obsolete. They work normally, but warnings will appear as the database is starting
up on the console and in the database alert log. You can also determine if a
parameter is deprecated by using the column isdeprecated in the v$parameter
view. If the parameter is deprecated, this column will be set to TRUE.
Three parameters in Oracle Database 11g are deprecated (since Oracle
Database 10g Release 2) in favor of the diagnostic_dest parameter. These are
■ background_dump_dest
■ core_dump_dest
■ user_dump_dest
Remaining deprecated parameters (since Oracle Database 10g Release 2) include:
■ commit_write This parameter is replaced by the new commit_logging and
commit_wait parameters.
■ instance_groups See Chapter 10 for more information on Real Application
Cluster changes in Oracle Database 11g.
■ log_archive_local_first
■ plsql_debug Replaced by plsql_optimize_level
■ plsql_v2_compatibility
■ remote_os_authent
■ standby_archive_dest
■ transaction_lag attribute
NOTE
You can find a complete list of deprecated
parameters from various versions of Oracle in the
Oracle Database Upgrade Guide for 11g Release 1.
28 Oracle Database 11g New Features
Arup Says…
I strongly recommend setting the parameter diagnostic_dest when you upgrade
the database or create a new database under Oracle 11g. Oracle ignores the
parameter background_dump_dest, even if it is defined in the initialization
parameter file. Instead it assumes the parameter diagnostic_dest to be
$oracle_base. In that directory, it creates a subdirectory, diag; then another one
under that, rdbms; yet another subdirectory under that, ; and
so on, and stores the text alert log there. So don’t be surprised if you suddenly
find the alert log of the older database not being updated any more. It will be
under the diagnostic_dest directory.
Obsolete Parameters
A database with obsolete parameters will start, but warnings on the console and in
the alert log will appear. Parameter in Oracle Database 11g that have been made
obsolete include:
■ Ddl_wait_for_locks
■ Logmnr_max_persistent_sessions
■ Plsql_compiler_flags
Undo_Management Parameter Madness
One final parting thought on changes to parameters in Oracle Database 11g. That is
that the undo_management parameter default is now AUTO. Manual undo is still
available but you will have to enable it in order to use it.
Oracle Dictionary View Changes
Amazingly, no static data dictionary views were deprecated in Oracle Database 11g.
The v$datafile view had the column plugged_in removed. A large number of new
views have been added. Review the Oracle Database Reference manual for more
information on the different views that have been added.
Additionally, Oracle Database 11g does not deprecate any dynamic views
either. A large number of new views have been added. Review the Oracle Database
Reference manual for more information on the different views that have been
added.
Chapter 1: Oracle Database 11g Getting Started 29
End of Line
The word Summary or Chapter Summary seems so old fashioned. “End of Line”
therefore will be my summary at the end of these chapters. For those of you who
don’t know, “End of Line” was used in the movie Tron. The MCP would say “End of
Line” after finishing his communications.
Thus we are at End of Line for this chapter. We have discussed the rather
involved process of upgrading to Oracle Database 11g. We have discussed both
automated upgrades and manual upgrades, and hopefully I’ve given you a proper
feel for each, so you can decide which way you want to go. Successfully finishing
an upgrade is ultimately satisfying. Successfully finishing the upgrade of 200+
databases is wholly satisfying.
Now, get out there and upgrade those databases and read the rest of this book to
figure out what great things lie in wait for you with Oracle Database 11g!
End of line…
This page intentionally left blank