|
|
|
A database unload/reload ensures a clean start with a fresh database.
To perform an upgrade using unload/reload, use the following process.
Note: In this process, the notation [Each DB] means: "For each database, not including the iidbdb (master database), become the DBA for that database, cd to the unload directory for the database created in Step 1, and perform this step." If using Ingres Star, include the coordinator database in the list of databases. Steps that apply to a particular upgrade type only (that is, in-place upgrade or clean install upgrade) are marked accordingly.
For details on these steps, see the following sections.
|
|
|
You should create a directory to hold scripts and data from the unloaded database.
Note: This directory requires a large amount of disk space. As an estimate, the unloaded data is about the same size as the Ingres database; however, compressed data can expand to take up much more space than the Ingres database.
To create a directory, issue the following commands for each database:
UNIX:
mkdir /someplace/dbname
chmod 777 /someplace/dbname 
Windows:
mkdir d:\someplace\dbname
|
|
|
Run unloaddb against each database. The unloaddb command does not unload the database; it simply creates scripts.
For Ingres Star databases, unload the CDB in the same way as for a local database. For a DDB, use unloaddb/star.
For a regular DB or CDB, issue this command:
unloaddb dbname
For an Ingres Star DDB, issue this command:
unloaddb ddbname/star
If doing a clean-install upgrade to a different machine that has a newer architecture, binary data may not be compatible between the two machines. If this is the case, use the unloaddb –c option, which causes an ASCII instead of binary unload.
|
|
|
Old databases may have objects created by users who no longer exist. Check for obsolete users for each database.
To check for obsolete users:
Each script contains set session authorization SQL statements for each user who owns a database object.
|
|
|
Note: This step is optional. You can omit this step if you can rely on the system backup to be taken in the later step Shut Down Ingres and Back Up System.
Follow these steps:
|
|
|
During the upgrade, the production system is not available for use. Make sure that users are not able to access the databases until the upgrade is complete.
|
|
|
The Remote Command Server component of Visual DBA must be disabled for the duration of the upgrade. The Remote Command Server uses the iidbdb database as a communications mechanism in versions of Ingres prior to 2.6, so it will interfere with upgrading.
Note: If you are upgrading from early versions of OpenIngres 1.x, and you do not see an entry in CBF for the Remote Command Server, skip this step.
To disable the Remote Command Server
|
|
|
You should perform a clean shutdown of Ingres, clearing all transactions from the transaction log, and then back up your system.
To perform a clean shutdown of Ingres
To back up your system
Note: Watch for symbolic links and cross-mounts; make sure real data is saved and not a symbolic link.
|
|
|
For each database, run the unload.ing script created by the unloaddb command. The database is unloaded into your unload directory.
|
|
|
Note: This step applies only to a clean-install upgrade.
Print optimizer statistics for each database. If your upgrade plan allows enough downtime to run a full optimizedb against your databases, you can omit this step. If your plan does not allow enough downtime, perform this step as a shortcut.
Note: Using this shortcut may result in some of the new Ingres metrics not being available; query performance may suffer until a full optimizedb can be completed.
If you are upgrading from OpenIngres 1.x, you should regenerate new statistics instead of saving the old ones, if possible.
To print the existing optimizer statistics, run statdump with the -o flag to a file for each database, as follows:
statdump -o dbname.stats dbname
|
|
|
For each database, you will need to know information such as whether the database was journaled, where the database resides, and in what order the data locations were configured.
To record database information
infodb dbname >infodb.out
Save the output for later.
To find out, use the catalogdb command. Select Databases, and then enter the database name. The screen that appears has an Access field that indicates whether the database is public or private.
|
|
|
To record database privileges
sql iidbdb
\script dbprivs.out
select *
from iidbprivileges
where database_name <> ''
order by database_name,grantee_name
\go
\script
\quit
The file dbprivs.out is created for future reference.
|
|
|
Note: This step is required only for a clean-install upgrade.
To save users, groups,and roles
sql iidbdb
copy iiusergroup (
groupid=c0comma,groupmem=c0nl
) into 'groups.out'
\go
copy iirole(
roleid=c0nl
) into 'roles.out'
\go
create table role_tmp as
select role_name,grantee_name
from iirolegrant
where admin_option <> 'Y'
\go
copy role_tmp(
role_name = c0comma,
grantee_name = c0nl
) into 'rolegrants.out';
drop role_tmp;
\go
\quit
A file called users.sql is written that will recreate all users, as they are currently defined.
|
|
|
Note: This step is required only for an in-place upgrade.
Destroy each database using the destroydb command.
|
|
|
Note: This step is required only for an in-place upgrade.
To clean the iidbdb database
As the installation owner, run the following steps against the master database iidbdb:
Note: It is assumed that there are no objects created by users in the iidbdb.
sysmod iidbdb
verifydb -mrun -sdbname iidbdb -opurge
verifydb -mrun -sdbname iidbdb –odbms
ckpdb –j iidbdb
The verifydb command may issue the following messages, which you can ignore:
S_DU1611_NO_PROTECTS iirelation indicates that there are protections for table (owner), but none are defined.
S_DU0305_CLEAR_PRTUPS Recommended action is to clear protection information from iirelation, and S_DU1619_NO_VIEW iirelation indicates that there is a view defined for table (owner), but none exists.
S_DU030C_CLEAR_VBASE Recommended action is to clear view base specification from iirelation.
You can also ignore the "patch warning" message that warns of the loss of user tables in "runinteractive" mode. This mode will not be used.
|
|
|
If Ingres starts automatically when the machine boots up, turn auto-starting off until the upgrade is complete.
To disable Ingres startup and put operating system changes into effect
UNIX: On most UNIX platforms, a file in a system startup directory performs Ingres startup and shutdown; place an "exit 0" at the top of this file. The system administrator may need to perform this step if it requires root privilege. (The system startup directory depends on your platform—/etc/init.d, or /sbin/init.d, or a similar name).
Windows: If Ingres is run as a system service, set the service to start manually instead of automatically.
Note: This step is recommended even if you are doing a clean installation upgrade. By leaving the old installation shut down, you eliminate the chance that someone will connect to it by mistake later.
|
|
|
Files distributed as part of Ingres that you have customized will be lost during the upgrade. Any custom files you have added to the $II_SYSTEM directory tree will remain.
You must copy your customized files to a safe place. Do not copy them to /tmp or anywhere in $II_SYSTEM/ingres directory.
If local collation sequence files have been customized, save the original collation definition files and the compiled files that reside in $II_SYSTEM/ingres/files/collation.
|
|
|
When upgrading, Visual DBA configuration files (.vdbacfg) are not upwardly compatible and must be recreated.
Note: Instead of using configuration files, you can use the vdba command with command line flags to start Visual DBA with, for example, certain windows open on given nodes. For details on the vdba command, see the Command Reference Guide.
|
|
|
Note: This step is optional but recommended.
The Ingres installation procedure on UNIX starts by extracting the install subdirectory from the Ingres distribution.
You should delete the old contents of that directory first, as follows:
cd $II_SYSTEM/ingres
rm –rf install
Note: This step is required only for an in-place upgrade on UNIX.
|
|
|
To install Ingres, see the Ingres installation instructions for your platform.
In-place upgrades only: During the installation process, the DBMS Server setup asks whether all databases are to be upgraded; answer No. The install procedure automatically upgrades the iidbdb. If the upgrade of iidbdb fails, see the appendix "Troubleshooting Upgradedb."
After the iidbdb is upgraded, the DBMS Server setup attempts to upgrade imadb and install Remote Command Server objects into imadb. Some versions of upgradedb neglect to create imadb first, and you will get "Database does not exist: imadb" errors. These will be corrected in the next step.
|
|
|
Newer versions of Ingres distribute service packs. You can install service packs without having to install a base release of Ingres first.
UNIX: If you are upgrading to an older Ingres version that requires an overlay patch instead of a service pack, follow this procedure:
Setup now uses the fixed version.
|
|
|
Note: Perform this step only if you received "Database does not exist: imadb" messages during the DBMS setup phase of your Ingres install. This should only occur if you are upgrading from OpenIngres 1.x to Ingres 2.6 or older.
To create the imadb database, as the installation owner, execute these commands:
UNIX:
ingstart
cd $II_SYSTEM/ingres/vdba
createdb '-u$ingres' imadb –f nofeclients
sql '-u$ingres' imadb <makimau.sql
rmcmdgen
ingstop
Windows:
ingstart
cd %II_SYSTEM%\ingres\vdba
createdb -u$ingres imadb –f nofeclients
sql -u$ingres imadb <makiman.sql
rmcmdgen
ingstop
As the makimau or makiman SQL scripts run, you see a series of messages such as "E_US0AC1 'some-name' does not exist or is not owned by you." These are normal and can be ignored.
|
|
|
Restore any site-specific files that you copied in the step Preserve Site Modifications.
If the checkpoint template file cktmpl.def has been modified, the modifications may need to be carried forward into Ingres. The cktmpl.def from Ingres 6.4 cannot be used with Ingres, as the file format has changed. This means that you must recreate the changes using the Ingres 6.4 cktmpl.def as a guide. See the Ingres 6.4 Database Administrator's Guide.
If the archiver exit script acpexit was changed in Ingres 6.4, you must make the changes to the Ingres template (acpexit.def), and then move that file to $II_SYSTEM/ingres/files/acpexit.
|
|
|
If you are doing a clean install, you need to change the default Ingres configuration to match your site requirements.
If you are doing an in-place upgrade, the upgrade process preserves your original Ingres installation parameters. You should review the configuration because some parameters may change from version to version. For information on parameters that changed, check the Readme for your new version of Ingres.
Review your parameter settings by running Configuration-By-Forms or Configuration Manager. Especially pay attention to major items such as startup counts and DBMS cache settings. If you are doing a clean install, you can use your original Ingres installation configuration as a guide.
Note: If you disabled the Remote Command Server in the step Disable Remote Command Server, use EditCount to restore its startup count to the original value.
|
|
|
Create the vnode definitions for the remote installations by using netutil. If using shadow passwords on UNIX, you must run mkvalidpw. For details, see the Connectivity Guide.
If there are NFS client-only installations that have not been set up, run ingmknfs to set them up.
|
|
|
Run ingstart to start Ingres.
|
|
|
Note: This step is required only for a clean-installation upgrade.
To recreate users, groups, and roles:
sql '-u$ingres' iidbdb
copy iiusergroup(groupid=c0comma,groupmem=c0nl)
from 'groups.out'
\go
commit
\go
\read users.sql
commit
\go
\quit
Windows: Omit the quotes from the sql command line.
The file users.sql may try to recreate some users that already exist in the installation, such as the installation owner and root user. This will cause "E_US18B6 The user 'name' already exists" errors. You can ignore these errors.
Roles cannot be reliably bulk-loaded from the original installation, so you must recreate them by hand. After you recreate each role, issue the following SQL statement:
grant rolename to user; commit
The most common user here is public. You can use the file rolegrants.out to determine what role grants are needed.
|
|
|
Note: This step is required only for a clean-install upgrade.
To recreate locations:
|
|
|
Before creating each database, refer to the infodb output saved in the step Record Database Information of this upgrade procedure. Look at the location names for ROOT, JOURNAL, CHECKPOINT, and DUMP. If these are not ii_database, ii_journal, ii_checkpoint, or ii_dump, you must specify the location to createdb with the –d, -j, -c, or –b flags, respectively.
Also, refer to the database access information recorded in that step. If the database access was "private," you must use the –p flag for createdb.
If all the database locations are the default, and the database is public, you can omit the flags on the createdb command line.
Recreate each user database, omitting the front-end catalogs. (The front-end catalogs will be created as part of the reload.) Use the following command:
createdb dbname flags -f nofeclients
Note: For an Ingres Star database, run createdb/star for the DDB. Do not run createdb for the CDB.
|
|
|
To extend each database:
If you prefer a non-interactive command line utility, you can use the extenddb utility instead of accessdb.
|
|
|
To recreate database privileges:
Each row in the dbprivs.out file describes one or more database privileges given to the user grantee-name. A Y or N in a privilege column indicates the specific privilege. (A U in a column means "Unchanged.")
sql iidbdb
grant privilege on database database-name to grantee-name;commit
If the privilege column is N, grant noprivilege instead of privilege.
The structure of the iidbpriv catalog did not change between OpenIngres 1.x and Ingres 2.6, so it is possible to copy the original contents of the catalog directly. However, we do not recommend this because the catalog may change in future releases.
If you have defined many privileges, or recreated many users, groups, or roles, you should run sysmod on the iidbdb, which will accelerate query processing. Issue the sysmod command, as follows:
sysmod iidbdb
|
|
|
Because the new database was not created with front-end catalogs, it is not necessary to drop them.
To fix the front-end reload script, for each database:
\include/ing12/ingres/files/iiud.scr
\include/ing12/ingres/files/iiud65.scr
Note: The directory path may differ.
create table ii_atttype (
.
.
...about 23 lines...
.
.
system_maintained char(1) not null
Not all databases contain the ii_atttype catalog, so it is okay if you do not find the definition.
|
|
|
To reload the database:
UNIX: Redirect the reload to a log file so that it can be checked for errors. Using the C shell:
reload.ing |& tee reload.log
Note: If using Ingres Star, reload the CDB and all "real" local databases before reloading the DDBs.
Type isql <database>, and select * from ii_id.
|
|
|
To upgrade the front-end catalogs to the new Ingres level, run upgradefe on each database.
Issue the following command:
upgradefe dbname INGRES
Type the word INGRES in uppercase.
|
|
|
Reapply optimization statistics for each database. You can do this by either:
If there is sufficient time, we recommend that you regenerate the optimizer statistics using the procedures of your application system.
If time is short, and if you printed the original statistics, you can read them back in with the –i option to optimizedb:
optimizedb dbname -i dbname.stats
|
|
|
Checkpoints and journals from your original Ingres version will not work with the newer version, so do not omit or delay this step.
Checkpoint each database, including the iidbdb. If the database was journaled previously, use the +j flag to turn on journaling.
To know which databases were journaled, see the infodb output from the step Record Database Information.
The iidbdb should always be journaled, regardless of whether it was journaled in the original installation.
|
|
|
To perform the last step of the upgrade procedure: