ORACLE8i- P3

Chia sẻ: Cong Thanh | Ngày: | Loại File: PDF | Số trang:40

0
39
lượt xem
4
download

ORACLE8i- P3

Mô tả tài liệu
  Download Vui lòng tải xuống để xem tài liệu đầy đủ

ORACLE8i- P3: We want information… information...” Possibly you recognize these words as the primary interest of a somewhat clandestine group, and as told by a character called Number 2 to Patrick McGoohan’s character Number 6 (in the old TV show The Prisoner). Indeed, in this day, information is king, and the speedy, accurate, and reliable retrieval of this information is paramount.

Chủ đề:
Lưu

Nội dung Text: ORACLE8i- P3

  1. REMOVING THE ORACLE SOFTWARE 69 FIGURE 2.10 PA R T The Control Panel ➢ I Services window showing Oracle services Oracle Essentials Most (if not all) Oracle services are prefixed with the word Oracle followed by the ORACLE_HOME name for that service. Thus, if the ORACLE_HOME name is ora817, then all your Oracle service names will end with ora817. It may be, of course, that you will have more than one ORACLE_HOME name and therefore will have duplicate instances of a service that start with different names (for example, OracleOra816TNSListener and OracleOra817TNSListener). Following are some common services that you will stop: • All Instance/Database Services (These services start with the word OracleService, followed by the name of the database. Thus for an instance called Robert, the name of the associated service would be OracleServiceRobert.) • The Oracle TNS Listener Other services that may be running include • The Oracle Agent • The client cache • Oracle Connection Manager services (CMADMIN and CMAN) • The Data Gatherer Step 2: Remove All Oracle Services WARN I NG Do not do anything to your Registry until you have backed it up! Failure to follow this advice will cause this book to self-destruct. You have been warned… tick…tick…tick… tick…. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. Copyright ©2002 SYBEX, Inc., Alameda, CA www.sybex.com
  2. 70 CHAPTER 2 • INSTALLING, UPGRADING, AND MIGRATING ORACLE Sometimes removing services manually is the only way to get it done right. To remove the services from the NT Registry: 1. Make sure you’ve stopped all Oracle services. 2. Now, before you do anything, back up your Registry. 3. Click Start ➢ Run and start REGEDIT. 4. Navigate to My Computer and then to the HKEY_LOCAL_MACHINE folder. Expand that folder and then expand SYSTEM, then ControlSet001, then SERVICES. 5. The SERVICES folder contains a subfolder for each service listed in the Control Panel Services window. Carefully remove each Oracle-related service from the Registry by highlighting its folder and pressing Delete. Step 3: Remove All Oracle Entries in the Registry The next step is to remove the software entries for Oracle from the Registry. Still in REGEDIT, find the HKEY_LOCAL_MACHINE folder and open the SOFTWARE sub- folder. Find the ORACLE folder and remove it. Then exit REGEDIT. Step 4: Remove All Oracle Software You are now almost home free (free of Oracle files, that is). Your final step is to remove all the Oracle software. Using Explorer, simply click on the uppermost Oracle software folder and delete away. Once this is complete, you have completely wiped Oracle off your NT system. Migrating/Updating to Oracle8i This section is for those of you very intelligent DBAs who have decided that you need to get your Oracle database running on Oracle8i. First we’ll make sure we know the difference between a migration and an upgrade. Then we’ll explore the various options available for migrating to Oracle 8i, including the MIG utility, the Data Migration Assistant (DMA), the EXP/IMP utilities, and the SQL*Plus COPY operation. Some of you are moving databases that are not supported by the Oracle8i direct migration process, and here you’ll find help with those sometimes more-complicated migrations. There’s also an extensive, comprehensive checklist for the migration process, step by step. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. Copyright ©2002 SYBEX, Inc., Alameda, CA www.sybex.com
  3. MIGRATING/UPDATING TO ORACLE8i 71 PA R T NOTE After doing literally hundreds of 7.x-to-8i migrations and several more hundred upgrades, we’ve included in this section some hints and suggestions from our experiences I with these processes. We hope our advice about migrations in particular will help save you from a failed migration effort. Oracle Essentials Migration vs. Upgrade DBAs tend to use the terms migration and upgrade interchangeably when referring to transition from one version of the Oracle database to another. In fact, however, these terms have two very different meanings. Version numbers of Oracle software start with a major version number (6, 7, or 8) followed by a dot, then the release number (giving you 7.2, 7.3, 8.0, 8.1), and then a patch number (giving you 7.3.3 or 8.1.6). This numbering scheme may extend even further into four and five digits for various “one-off” patch sets from Oracle. Upgrad- ing means advancing to later release numbers within the same major version number. Migrating means advancing to subsequent major version numbers. Migrations often require a great deal more work, because significant changes usually occur when major version numbers change. Oracle generally supplies a migration utility to facilitate migrations. The process of upgrading is typically much easier; generally, you’ll run a single upgrade script and then a couple of other standard Oracle scripts. Upgrades between the one-off patch sets are usually easier yet, requiring only the running of some standard Oracle scripts, depending on the options your database system uses. Migrating a Pre-7.1 Database to Oracle8i If you are running your current database on an Oracle version prior to 7.1 and you have decided to migrate to Oracle8i, you’ve made a wise decision. Unfortunately, your migration path will be somewhat more limited and potentially complex than for those who have already migrated to Oracle7 or Oracle8. With pre-7.1 databases, you have only a few migration options. You can use the Oracle Export and Import utilities to move the data to Oracle8i. You can also use the SQL*Plus COPY command to move the data. Or you can opt to first migrate your database to a version of Oracle7 that will support more robust migration options to Oracle8i. Let’s take a look at the various migration paths available to Oracle6 DBAs. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. Copyright ©2002 SYBEX, Inc., Alameda, CA www.sybex.com
  4. 72 CHAPTER 2 • INSTALLING, UPGRADING, AND MIGRATING ORACLE WARN I NG Migrations and upgrades are subject to many opportunities for massive failure. Don’t put a production database at risk by failing to plan and test your migration strategy. Then test it again to make sure you are comfortable with it. Finally, take the addi- tional precaution of having one of your DBA peers review it and test it again. Using the Export and Import Utilities The Import (IMP) and Export (EXP) programs are covered in much more detail in Chapter 11, but for now we’ll examine their use for doing migration. First you use EXP to create a logical backup of your database. This logical backup is dumped out to an operating system file. The dump file can then be used by IMP, which loads the data back into the same or another database. Here are the basic steps for this process: 1. Create an Oracle8i database. 2. Do a full export of the pre-7.1 database. 3. Do a full import of the dump file created in step 2 into the new Oracle8i database. 4. Check for any invalid objects, failures to import specific objects, and so on, as required. The Export/Import method isn’t a bad choice as long as the database is relatively small. Typically, if the amount of data to be exported is greater than 2GB, this solu- tion may become impractical. In this case, you’d consider the option to migrate first to a database under Oracle 7.0 or later, and then to Oracle8i (as explained shortly). TI P If your SYSTEM tablespace is badly fragmented, and your data dictionary tables are fragmented into many extents (SYS.SOURCE$), the Import/Export migration might be an advantage. That’s because it will allow you to rebuild your data dictionary and the SYSTEM tablespace, and performance may improve. Using the SQL*Plus COPY Command In our opinion, using the SQL*Plus COPY command is, at best, a kludgy answer to migration. You have to create the Oracle8i database first, and then use the SQL*Plus COPY command to create the tables and copy the data into those tables. You then have to create any indexes, primary keys, foreign key constraints, load triggers, Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. Copyright ©2002 SYBEX, Inc., Alameda, CA www.sybex.com
  5. MIGRATING/UPDATING TO ORACLE8i 73 functions, procedures, and so on, as required. This is a great deal more work than PA R T what’s needed for the migration utility, which we will discuss shortly. I Note that you can use a combination of these first two options, Import/Export and the SQL*Plus COPY command, to make the job easier. Here’s how: 1. Create the Oracle8i instance and the basic database shell (SYSTEM tablespace, Oracle Essentials rollback segments, temporary tablespace, and so on), including all tablespaces that will be needed. 2. Do a full export of the Oracle database you are migrating. Use the ROWS=N parameter in the export so that only the logical structure of the database is exported and not the data. (See Appendix C for syntax of the SQL*Plus COPY command.) 3. Into the new Oracle8i database, import the exported dump file created in step 2. This creates all structures, indexes, tables, stored code, and so on. 4. You may wish to disable all primary keys and wait to build indexes until after the load has completed. This will speed up the load process significantly because index updates will not occur during the load processes. Migrating to Oracle7 and Then to Oracle8i If you are migrating a pre-Oracle 7.1 database, you won’t be able to migrate directly to Oracle8i with the Oracle migration utility (MIG, discussed shortly) or the Database Migration Assistant (both of which we will discuss shortly). If your pre-Oracle 7.1 database is smaller than 2GB or so, it may be more expedient to migrate using just the Oracle Export and Import utilities. For larger databases, however, MIG is usually your best choice for upgrading. In any of these cases, you’ll need to determine how to migrate the pre-Oracle 7.1 database first to Oracle version 7.1 or later. This is because the Oracle8i migration process doesn’t support migrating from a version of the Oracle database that is earlier than Oracle 7.1. Refer to the Oracle 7.1 migration instructions for the version from which you are migrating. Once you have migrated to Oracle 7.1, you can move for- ward to Oracle8i. Migrating an Existing 7.1+ Database to Oracle8i You’ve waited for Oracle8 to mature and you figure it’s time to get on the Oracle8i bandwagon (not to mention that Oracle is pulling support for Oracle7). There are several options available for migrating your database to Oracle8i. These include the Oracle MIG utility, the Data Migration Assistant (DMA), the EXP/IMP utilities, and Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. Copyright ©2002 SYBEX, Inc., Alameda, CA www.sybex.com
  6. 74 CHAPTER 2 • INSTALLING, UPGRADING, AND MIGRATING ORACLE the SQL*Plus COPY operation. The last two options work just as described above for pre-7.1 databases. Here, let’s look at the MIG utility and then the DMA. The Oracle Migration Utility The Migration utility (MIG) facilitates migrations from supported Oracle7 databases (usually 7.1 and above) to Oracle8i. This utility modifies the Oracle7 data dictionary, making it compatible with the new Oracle8i data dictionary structures. In the process, it re-creates the entire Oracle7 data dictionary as an Oracle8i data dictionary, adding and removing columns, tables, and indexes as required. The MIG utility does not change the fundamental database structures at all, and the original data dictionary tables are left intact (although the views on those tables are removed). Because the Oracle7 data dictionary tables are left intact by MIG, you can recover the Oracle7 database easily should the MIG utility fail. You simply rerun the various database conversion scripts, such as catalog, catproc, and catrep, and you’re in business. Once you determine why MIG failed, you can rerun it without any problem. The MIG utility creates a schema in the database called MIGRATE. Before you start the migration process, make sure you don’t already have a MIGRATE schema. After MIG is done working, it will have created a conversion file called conv.dbf file in the Oracle7 ORACLE_HOME. This file must be moved to the Oracle8i ORACLE_ HOME, as shown in step 20 of the migration instructions later in this section. Note that the MIG utility runs with the ORACLE_HOME of the database pointing to the Oracle7 software, not the Oracle8i software. This is because MIG precedes the actual conversion process that takes place when you issue the ALTER DATABASE CONVERT command. It’s this fact that makes it easy to recover the database should MIG fail. Once the ALTER DATABASE CONVERT command has been issued, though, it’s a whole different story. Running MIGPREP If you have already done some 7.3-to-8 migrations in UNIX, you’ll be happy to know that the method of installing the MIG utility has changed. Previously, you had to load MIG into the 7.3 ORACLE_HOME from the Oracle8 CD-ROM. Now, MIG loads into the Oracle8i ORACLE_HOME directory when you install the 8i software, and you need only run a new utility called MIGPREP to move the migration software, includ- ing MIG, to the Oracle7 ORACLE_HOME. The syntax for the MIGPREP utility is Migprep Thus, if you are installing the MIG utility from 8.1.6 to 7.3.4, your MIGPREP com- mand would look something like this: Migprep /ora01/oracle/product/8.1.6 /ora01/oracle/product/7.3.4 Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. Copyright ©2002 SYBEX, Inc., Alameda, CA www.sybex.com
  7. MIGRATING/UPDATING TO ORACLE8i 75 Note that this does not apply to migrations on NT. The MIG utility can be run PA R T from the Oracle8i install on NT without a problem. I A Checklist for Using MIG The sidebar “Checklist for Migrating Oracle7 to Oracle8i” presents a step-by-step process for supported Oracle 7.x migrations to Oracle8i. This checklist has been Oracle Essentials through the test of time, updated and improved by Robert Freeman over the course of running many migrations and working through their problems. We recommend you print it out from the book’s CD-ROM and use it during your migrations to Oracle8i. This checklist is current as of Oracle version 8.1.7. And hey, if Robert has left any- thing out, feel free to write him at the e-mail address in the Introduction! Checklist for Migrating Oracle7 to Oracle8i 1. Verify successful backup of your database. 2. Install Oracle8i software on the server. 3. If you are running on NT, set the ORACLE_SID environment variable. If you are running on Unix, set up these environment variables for the Oracle7 environment: ORACLE_HOME, ORACLE_SID, PATH, LD_LIBRARY_PATH, ORA_NLS, ORACLE_BASE, and ORACLE_PATH (if set). 4. Unix only: If this is the first 7-to-8 migration for your Unix Oracle system, run the MIGPREP script to copy migration files to the Oracle7 environment. migprep /ora01/oracle/product/8.1.6.0 /ora01/oracle/product/7.3.4.4 5. In /tmp, create a directory and subdirectory called mig and mig/ (if they don’t already exist). 6. Remove all rows (via TRUNCATE) from the aud$ table. 7. Modify the following parameters in the initsid.ora (don’t forget to save a copy of the original file!): job_queue_processes=0 audit_trail=None Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. Copyright ©2002 SYBEX, Inc., Alameda, CA www.sybex.com
  8. 76 CHAPTER 2 • INSTALLING, UPGRADING, AND MIGRATING ORACLE 8. Confirm the version of the MIG utility. It should be the Oracle8i version to which you are migrating. To do this, print the MIG help screen, which will include the version of the utility. You’ll run MIG from the Oracle 7.3 ORACLE_HOME on Unix, and from the newly installed Oracle8i ORACLE_HOME on NT. Run MIG with this command: Mig help=y which produces this output: ORACLE7 to ORACLE8 Migration Utility Release 8.1.6.0.0 - Production 9. Shut down the database with SHUTDOWN IMMEDIATE. 10. Run the MIG utility with the CHECK_ONLY option to ensure that enough space is available in the database for the migration process. Unix example: From the Oracle 7.3 ORACLE_HOME, enter this command (note that the slashes before the quotes are required): mig CHECK_ONLY=TRUE SPOOL=\”/tmp/mig/_check_only.out\” NT example: From the newly installed Oracle8i ORACLE_HOME, enter mig CHECK_ONLY=TRUE SPOOL=”c:\mig\_check_only.out” pfile= 11. Check the data dictionary tables for fragmentation and/or excessive numbers of extents. Modify the migrate.bsq as required. The following script will look for fragmented data dictionary objects. If an excessive number of these exist, consider modifying these objects in the migrate.bsq so they will fit in fewer extents. SELECT a.owner, a.segment_name, a.segment_type, a.extents, b.initial_extent, b.next_extent FROM dba_segments a, dba_tables b WHERE a.owner=b.owner and a.segment_name=b.table_name and a.owner=’SYS’ and a.extents > 5 UNION SELECT a.owner, a.segment_name, a.segment_type, a.extents, b.initial_extent, b.next_extent Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. Copyright ©2002 SYBEX, Inc., Alameda, CA www.sybex.com
  9. MIGRATING/UPDATING TO ORACLE8i 77 PA R T I FROM dba_segments a, dba_indexes b WHERE a.owner=b.owner and a.segment_name=b.index_name and Oracle Essentials a.owner=’SYS’ and a.extents > 5; 12. Make sure the SYSTEM rollback segment does not have an optimal setting. Use the following query to check the OPTIMAL setting for the SYSTEM rollback segment. If the SYSTEM rollback segment is set to OPTIMAL, you will need to use the ALTER ROLLBACK SEGMENT command to reconfigure it so that OPTIMAL is NULL. SELECT a.usn, a.name, b.optsize FROM v$rollname a, v$rollstat b WHERE a.usn = b.usn AND name = ‘SYSTEM’; ALTER ROLLBACK SEGMENT SYSTEM STORAGE (OPTIMAL NULL); 13. Check the SYSTEM tablespace of the database to ensure that it has sufficient con- tiguous space. The SYSTEM tablespace should have a few chunks of space that are moderate to large. If it does not, consider adding to the size of the existing system tablespace database datafiles, or adding a new datafile as required. SELECT tablespace_name, bytes FROM dba_free_space WHERE tablespace_name = ‘SYSTEM’; 14. Check the DBA_2PC_PENDING table for any unresolved distributed transactions. If there are any unresolved transactions, see Chapter 22. SELECT * FROM dba_2pc_pending; 15. Shut down the Oracle database with a normal or immediate shutdown. Do not do a shutdown abort. 16. Run the Oracle8i Migration utility: Unix example (still set up for the ORACLE7 environment): mig SPOOL=\”/tmp/mig/_migration.out\” NT example: mig SPOOL=”c:\mig\_check_only.out” pfile= 17. Check the output file after running the Migration utility. If the utility ran correctly, you may wish to do another backup of the database at this point. (We rarely do this in practice, however, since we use a special backup/recovery method described later in this section, and it doesn’t take as long to rerun the MIG process as it does to recover the database.) Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. Copyright ©2002 SYBEX, Inc., Alameda, CA www.sybex.com
  10. 78 CHAPTER 2 • INSTALLING, UPGRADING, AND MIGRATING ORACLE 18. If you are running on Unix, set up the following environment variables for the Oracle8i environment: ORACLE_HOME, ORACLE_SID, PATH, LD_LIBRARY_PATH, ORA_NLS, ORACLE_BASE, and ORACLE_PATH (if set). If you are running on NT, take these steps: • Stop the Oracle service for your database: NET STOP OracleServiceORCL • Delete the Oracle7 service on NT using the ORADIM7x commands: ORADIM71 -delete -sid ORCL ORADIM72 -delete -sid ORCL ORADIM73 -delete -sid ORCL • Restart the system. • Create the Oracle8i service for the newly migrated Oracle8i database, using the ORADIM command. (See the NT-specific documentation for instructions on using ORADIM.) Here is an example: ORADIM -NEW -SID ORCL -INTPWD MYPASSWORD -MAXUSERS 200 -STARTMODE AUTO -PFILE ORACLE_HOME\DATABASE\INITSID.ORA 19. Remove or rename the database control files. Alternatively, you can change the CONTROL_FILES initialization parameter to specify new location and/or names for the control files. The CONTROL_FILES initialization parameter typically is set in the initsid.ora file. 20. Move or copy the convert file from the Oracle7 ORACLE_HOME to the Oracle8i ORACLE_HOME. On most Unix systems, in both the Oracle7 and the Oracle8i envi- ronments, the convert file (convsid.dbf, where sid is the Oracle8i database name), should reside in $ORACLE_HOME/dbs; in an NT system, it’s in ORACLE_HOME\database. Unix example: cp /ora01/oracle/product/7.3.4.4/dbs/convsid.dbf /ora01/oracle/product/8.1.6.0/dbs/convsid.dbf NT example: copy c:\oracle\product\7.3.4.4\database\convsid.dbf c:\ora01\oracle\product\8.1.6.0\database\convsid.dbf Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. Copyright ©2002 SYBEX, Inc., Alameda, CA www.sybex.com
  11. MIGRATING/UPDATING TO ORACLE8i 79 PA R T I 21. Copy the init.ora parameter file from the 7.3 ORACLE_HOME to the new 8i ORACLE_HOME. 22. Modify the copied init.ora parameter file to reflect any new Oracle8i parameters Oracle Essentials you wish to use. Be aware that, by default, the JAVA_POOL_SIZE will be allocated to 20MB! If you won’t be doing Java within the database, you’ll want to add the JAVA_POOL_SIZE parameter, setting the size of the pool to 0. Remove any obsolete parameters from the parameter file as well. Examples: JAVA_POOL_SIZE=0 COMPATIBLE=8.1.5 (or 8.1.6 or 8.1.7) INSTANCE_NAME (see Chapter 8) SERVICE_NAME (see Chapter 8) LOCAL_LISTENER (see Chapter 8) 23. Change to the new Oracle8i ORACLE_HOME/rdbms/admin directory. From Server Manager or SQL*Plus, issue the commands to convert and open the database under Oracle8i. CONNECT INTERNAL STARTUP NOMOUNT ALTER DATABASE CONVERT; ALTER DATABASE OPEN RESETLOGS; CAUTIONS: You must use the STARTUP NOMOUNT option when first opening the database. MOUNT may crash the database, forcing a restore. Once you have issued the ALTER DATABASE CONVERT, you will need to recover the database before you can run the MIG utility again! If an error occurs during the ALTER DATABASE CONVERT process, carefully deter- mine the nature of the error. It may or may not require you to recover the database. 24. Finish the conversion process by running the following conversion script from Server Manager or SQL*Plus. This script executes several other SQL scripts. You may need to include others not listed here if you are using optional Oracle prod- ucts. To find out, refer to the Migration Guide, the OS-specific documentation, and the product-specific documentation. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. Copyright ©2002 SYBEX, Inc., Alameda, CA www.sybex.com
  12. 80 CHAPTER 2 • INSTALLING, UPGRADING, AND MIGRATING ORACLE Note: If this script fails, correct the reason for the failure (lack of tablespace space, for example) and then rerun the script. After a script is completed, you don’t have to rerun it if a subsequent script fails, but you do have to rerun the failed script and the subsequent scripts. Also, check the log file for any invalid constraints that might arise out of the execution of utlconst.sql. SPOOL /tmp/mig/_catoutm.log SET ECHO ON @u0703040.sql @catrep.sql @r0703040.sql @utlrp.sql @utlconst.sql SHUTDOWN IMMEDIATE STARTUP 25. Review the log from the migration process. 26. Change user OUTLN’s password using the ALTER USER command as follows. Alter any additional new Oracle8i accounts that might be created: ALTER USER OUTLN IDENTIFIED BY new_password; 27. Modify the listener.ora file of the listener that was listening for the Oracle7 database. You will no longer need the SID description in the listener.ora for that database. 28. Verify client connectivity to the new Oracle8i database. 29. Perform a backup of the newly migrated Oracle8i database. Some Advice for Your Migrations The checklist in the sidebar is a good start when it comes to migrating your databases to Oracle8i. It does not, however, supplant your reading the Oracle Migration Guide, which is supplied with the Oracle8i documentation. Please—read the documentation, and then read it again. Then, if you feel comfortable with the migration process in general, test the process several times before trying it out on a real database. Follow- ing are our additional comments and suggestions for making your database migration experience a little easier. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. Copyright ©2002 SYBEX, Inc., Alameda, CA www.sybex.com
  13. MIGRATING/UPDATING TO ORACLE8i 81 Suggestions for Backup PA R T Always, without fail, back up your system before you do any migration. In fact, we I usually do two separate backups. The first one is a full backup of all tablespaces; hot backups are fine (see Chapters 10 through 13 for a full treatment on backups). Sec- ond, we back up only the SYSTEM tablespace and any tablespace with rollback seg- Oracle Essentials ments. After doing these two backups, we put all the other tablespaces except SYSTEM and rollback segment tablespaces into READ-ONLY mode before continuing the migration process. Making these tablespaces READ-ONLY is permissible because Oracle does not need to access them during the migration process. The benefit is that if you need to recover your database after a failed migration, you only have to recover the SYSTEM table- space and rollback segment tablespaces; none of the other tablespaces will have changed and will thus be consistent. You can make the tablespaces READ-WRITE again after the migration is successful and you have run the migration scripts. These scripts include @u0703040.sql, @catrep.sql, @r0703040.sql, @utlrp.sql, @utlconst .sql, and possibly others demanded by your database software installation. Finally, make sure you back up your new Oracle8i database after the migration is complete. Here again, a hot backup is sufficient. Other Important Migration Tasks Don’t neglect these important issues. Checking database parameters Take care of the database parameter files. The migration checklist suggests several changes to these files, so always make a copy of the parameter file first. If you are using the Oracle job scheduler replication and such, it is very important that you turn the scheduler off before starting the migration, as shown in step 7 of the checklist. Just set JOB_QUEUE_PROCESSES=0 in the database init.ora parameter file, and that will take care of it. Also, make sure you check the compatible parameter. Once the migration is com- plete, make sure you change it to the correct value (generally the version of the data- base you are migrating to). Also, keep an eye out for parameters that have become obsolete. Because these specific parameters are no longer supported, they will prevent your new database from coming up. There are also depreciated parameters. These are parameters that are no longer used by Oracle but their use will not keep your database from coming up, but will generate errors every time the database is started. It’s a good idea to remove these parameters from the database because future versions of Oracle will likely not support them at all. Finally, there are some parameters that have become hidden parameters. There are not to many of these, but your database will complain Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. Copyright ©2002 SYBEX, Inc., Alameda, CA www.sybex.com
  14. 82 CHAPTER 2 • INSTALLING, UPGRADING, AND MIGRATING ORACLE about their use and refuse to start until they are either removed or changed to the cor- rect hidden parameter. You can find lists of all obsolete and depreciated parameters in the Oracle8i documentation for the version of Oracle8i that you are running (they differ for each version). Set Auditing OFF If auditing is running, it can cause the migration to fail, so if you’re using database auditing, turn it OFF (see step 7 of the checklist). Also, note that the checklist suggests truncation of the audit_trail in step 6 (SYS.AUD$ is the audit trail table). This is because Oracle will try to make a copy of the SYS.AUD$ table during the migration process. If the audit table is particularly large, the migration will take substantially longer to complete. Check the System Items Before Migration Before you start the migration process, consider checking the data dictionary tables for fragmentation and/or excessive numbers of extents (see step 12 of the checklist). This is your chance to rebuild the data dictionary if some of the tables (such as SOURCE$) are in bad shape. With any segment that appears in the .sql script pro- vided with step 11, you should consider increasing the INITIAL and NEXT parameters for that object, in the file mig.bsq which is contained in the 8i $ORACLE_HOME directory. We strongly recommend assigning the same values to INITIAL and NEXT for that object, and setting PCTINCREASE to 0 for that object. Of course, you should always make a copy of the mig.bsq before changing it, and ensure that another user isn’t making changes or doing a conversion with that copy of the mig.bsq. Before migrating the database, ensure that the SYSTEM rollback segment does not have an OPTIMAL setting. Having OPTIMAL set will seldom cause a problem, but it may happen that you’ll get a “snapshot too old” error and the migration process will die. It’s better to reset OPTIMAL to NULL. Also, you must have the database shut down before you run the Migration utility. One of MIG’s actions is to start up the database, and the utility will stall if the database is already up. The other thing to note is that when the migration utility starts, it will take off line all rollback segments except the SYSTEM rollback segment. So, when you check the SYSTEM rollback seg- ment after running MIG with the CHECK_ONLY=TRUE flag, you may notice that the SYSTEM rollback segment is the only one. This is normal. Migration Utility Notes When you migrate a database with the MIG utility, a conversion file (conv.dbf) is created in the Oracle 7.3 ORACLE_HOME/dbs directory (ORACLE_HOME\database in NT). This file is critical to the conversion process. You will copy it to Oracle8i’s ORACLE_HOME\ dbs (or ORACLE_HOME\database) before you convert the database. The conversion file Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. Copyright ©2002 SYBEX, Inc., Alameda, CA www.sybex.com
  15. MIGRATING/UPDATING TO ORACLE8i 83 contains information that Oracle needs during the execution of the ALTER DATABASE PA R T CONVERT command, to check for changes in datafiles since the migration process I was last run. Watch out: If you’re running multiple versions of Oracle8 and/or Oracle8i on your system, it’s possible that the wrong version of MIG will be present in your 7.3 ORACLE_ HOME directory in Unix. In NT, you might be in the wrong ORACLE_HOME directory Oracle Essentials or the PATH could be set such that an incorrect version of MIG will be executed. This problem arises from the fact that there is a version of MIG for each edition of the Oracle8 and Oracle8i database software that is installed on a given system. When run- ning the migration utility, you need to make sure you are running the version of the utility that is associated with the Oracle software version to which you are migrating (see step 8). You will not immediately be made aware that you have used the wrong version of the migration utility. Oracle will happily migrate your database using the 8.0.5 version of the migration utility, even though you really mean to be using 8.1.7. In this case, you will not discover that you used the wrong migration utility until you try to actually convert the database, which may be sometime later in the migration process. During MIG execution, you may find that a SYS.* table can’t be extended due to an “out of extents” error. The MULTIPLIER parameter of the MIG command described earlier in this chapter may be the answer. This parameter causes certain system data dictionary tables to be created with a larger extent size than the Oracle 7.x data dictio- nary tables. MULTIPLIER’s default setting is 15. If you encounter such errors, we rec- ommended increasing this to 30; if errors persist, increase it again to 50. Note that this will increase the size of the objects in the data dictionary. ALTER DATABASE CONVERT Problems When you issue the ALTER DATABASE CONVERT command to convert the database, if you have used the wrong MIG utility, Oracle will produce an error message that the conv.dbf conversion file is incompatible with the database version you’re try- ing to convert. You’ve accidentally run the wrong version of the migration utility, and fixing this is pretty simple (although it may take some time). Shut Oracle down and run the correct version of the MIG utility. (In Unix, run MIGPREP to make sure that you have the right version in place. In NT, make sure you are in the correct ORACLE_HOME location.) Note, also, that in step 8 of the checklist you run the MIG process using the HELP=Y flag. This is so you can verify that the help banner displays the same Oracle version number as the database version to which you are moving. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. Copyright ©2002 SYBEX, Inc., Alameda, CA www.sybex.com
  16. 84 CHAPTER 2 • INSTALLING, UPGRADING, AND MIGRATING ORACLE WARN I NG If the ALTER DATABASE CONVERT command fails, be very careful about what you do next. Just because the command fails does not mean you’ll get automatic recovery. It might be that you forgot to remove the control files, or you didn’t move the conversion file into its proper place. It might be that the new control files are too big for their disks. (The Oracle control files are significantly larger in Oracle8i than Oracle7.) Just don’t panic if things don’t work right the first time. Again, as with all things, practice makes perfect. Please practice your migration efforts in a development environment before trying it in production. Migrating Using the Data Migration Assistant If the dark forces have hooked you into graphical interface addiction, the Data Migra- tion Assistant (DMA) is for you. There are various platform-specific requirements for using this interface to migrate an Oracle database, so read the Oracle8i Migration Guide for details. It tells you how to prepare the database before using the DMA, as well as procedures to follow after the migration. For the most part, the DMA automates the entire migration process. This is the pri- mary benefit of the DMA and makes it easier for a novice DBA to migrate a database. On the downside, you cannot use some of MIG’s command-line parameters (such as MULTIPLIER), which might be required. Another problem with the DMA is that if it fails, you are more likely to have to restore your database in order to try the migration again than if you had migrated the database manually. This is because you have more control over a manual migration and can more easily deal with failures. To start the DMA from Windows, click Start ➢ Migration Utilities ➢ Migration Assistant. From Unix, start it from the ORACLE8i ORACLE_HOME, with the ORACLE7 path for your database set up. The DMA will take it from there. Another benefit of the DMA is that it will handle upgrades of your database in addition to migrations. Should you need to upgrade from Oracle8 to Oracle8i, or even within versions of Oracle8i itself, the DMA can assist you in this process. We like to use the DMA for these types of upgrades, as opposed to migrations, because we don’t have to worry about making typing errors in the upgrade script names during the upgrade. And, since the upgrade process is fairly simple, we can generally just point, click, and forget. All that’s needed is to check that everything worked once the process is complete. Upgrading within Major Versions We have discussed migrations, which are movements to other major versions of the Oracle software. Now, let’s look at the topic of upgrades. We’ll discuss two types of Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. Copyright ©2002 SYBEX, Inc., Alameda, CA www.sybex.com
  17. MIGRATING/UPDATING TO ORACLE8i 85 upgrades: One is the upgrade to a different release level within the same major ver- PA R T sion (such as from 8.1.5 to 8.1.6). The second type of upgrade is the application of I patches to a minor version of the Oracle software. In this case the minor version number stays the same; thus you will remain at the 8.1.6 release level. Upgrading Oracle Essentials Minor-version upgrades to Oracle can occur fairly frequently. A new minor version of the software gets released as often as every 9 to 12 months or so. With each upgrade, new functions (and often new bugs, as well!) are introduced. Upgrades within major versions of the Oracle software (8.0 to 8.1, or 8.1.x to 8.1.x, for example) are generally fairly simple processes. Of course, you should always get specific instruction from the Oracle documentation for the version to which you’re upgrading. More often than not, you’ll find that the process consists of these steps: 1. Back up the database. 2. Shut down the database. 3. Restart the database using the new version of the Oracle software. 4. Run an upgrade script. You’ll do this from $ORACLE_HOME/rdbms/admin on Unix, or ORACLE_HOME\rdbms\admin on NT. This script usually takes on the naming convention Uversion#.sql, with version# generally a seven-digit number rep- resenting the database version you are upgrading. Each part of the version num- ber is proceeded with a zero (thus 8 is 08) except for the last digit, which normally is 0. Thus, if you are upgrading from an 8.1.5 to an 8.1.6 database, you’d run the upgrade script named u0805010.sql. NOTE Looking through upgrade scripts and the scripts they call can reveal many inter- esting things about the version to which you are upgrading—including new features and maybe even an occasional undocumented command! 5. Run catalog.sql, catproc.sql, catrep.sql and other conversion scripts required to re-create the data dictionary views and to reload other database options such as advanced replication. 6. Back up the upgraded database. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. Copyright ©2002 SYBEX, Inc., Alameda, CA www.sybex.com
  18. 86 CHAPTER 2 • INSTALLING, UPGRADING, AND MIGRATING ORACLE Patching The main difference between patch sets and upgrades is that patch sets are fixes and don’t include any new features. When you apply one-off patches to the RDBMS soft- ware, as described earlier in this chapter, additional work is often required on any database using that ORACLE_HOME software. Sometimes, however, a one-off patch might be released for a nondatabase product such as SQL*Plus. Always study the patch set’s Readme document to make sure you know what’s necessary for applying the upgrade to each of your databases. Typically the procedure involves running the catalog.sql and catproc.sql scripts (discussed in more detail in Chapter 4), and other scripts necessary to the optional Oracle products you have loaded. In Sum It’s true that migrating or upgrading your Oracle database can be an intimidating job. There is a good deal of preparation that needs to be done, and we hope you’re ready for that after reading this chapter. You now know about the different types of software upgrades that you may encounter, and you’ve been cautioned about some of the pit- falls of the process. The bottom line is, and this is true with all DBA work, you must protect the database. Understand what you are doing, test it, and test it again before you do it for real. Back up the database before making any changes at all to it. Do these things and you’re on your way to mastering Oracle installations. And when you need help, there are a few things you can do. Hopefully, you’re an Oracle customer, so you have access to support services. Oracle Metalink at metalink .oracle.com is Oracle’s web-based support site, which you can sign up for as an Oracle support customer. There are other sites you might want to look at as well. Here are a few: technet.oracle.com—You need not be an Oracle support customer to join up. In addition to help, this site has a good deal of Oracle software available for download for trial use. www.revealnet.com—Home of the RevealNet products you will find on the CD that comes with this book. This site also has the DBA Pipeline and the PL/SQL Pipeline, where Robert participates as a sysop. This site is a true community of Oracle DBAs and developers, so come join us! www.ixora.com.au—This site belongs to Steve Adams and his company, Ixora. It is dedicated to information about Oracle internals. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. Copyright ©2002 SYBEX, Inc., Alameda, CA www.sybex.com
  19. CHAPTER 3 Creating Oracle Databases F E AT U R I N G : Oracle’s Optimal Flexible Architecture (OFA) 89 Determining physical requirements 97 Determining the database block size 113 Naming conventions 115 Creating the parameter file (init.ora) 117 Creating the database 128 Summary of database creation steps 138 Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. Copyright ©2002 SYBEX, Inc., Alameda, CA www.sybex.com
  20. C reation of an Oracle database can seem an ominous task the first few times you try it. But like learning anything new, practice helps. This chapter gets you started on the right track, and you’ll gain experience every time you create a new or change an existing database. This chapter begins with a short discussion of the Oracle’s Optimal Flexible Archi- tecture (OFA), the standard on which Oracle databases are built. You’ll learn about the important preparatory work that goes into the process of database creation: planning the foundation of physical space and the memory you’ll need, configuring that foun- dation, and setting fundamental parameters. The important init.ora database parameter file contains most of the settings representing your decisions for these ele- ments. We will then look at the steps of actually creating the Oracle database—either manually or via the Database Configuration Agent—and some of the important tasks to be done immediately afterward. At the end of the chapter is a summarized list of steps to help you remember to do everything necessary to create your Oracle database from start to finish. NOTE This chapter is about creating the database proper. It is not about creating seg- ments, tables, indexes, and the other objects in an Oracle database. These topics are cov- ered in Chapter 6. Robert’s Rant: Read That Documentation!! Many Oracle DBAs are not familiar or even comfortable with the Oracle documentation. While the documentation Oracle provides is not always complete or absolutely correct, it is (along with this book of course) a good first resource to use. Particularly important is documentation for your specific hardware. Because Oracle is available for so many hardware platforms, there is no way a book like this one can provide you with every bit of information you need to know about installing Oracle on your system. So if you are one of these DBAs, we urge you to stop right now. Get out the docu- mentation CD, start it up, and spend at least a good hour with it. Learn how to use the documentation search functions, awkward though they may be. Know what documents Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. Copyright ©2002 SYBEX, Inc., Alameda, CA www.sybex.com
Đồng bộ tài khoản