In this Document
Applies to: Oracle Database Cloud Schema Service - Version N/A and later Oracle Database Exadata Cloud Machine - Version N/A and later Oracle Cloud Infrastructure - Database Service - Version N/A and later Oracle Database Cloud Exadata Service - Version N/A and later Oracle Database Backup Service - Version N/A and later Information in this document applies to any platform. PurposeThis document is created for use as a guideline and checklist when manually upgrading from Oracle 11gR2 (11.2) or Oracle 12c Release 1 (12.1) or Oracle 12c Release 2 (12.2) to Oracle 18c Release ScopeDatabase Administrators, Support DetailsStep 1: Upgrade Path for 18.1 Oracle databaseMinimum version of the database that can be directly upgraded to Oracle 18.1Upgrade MatrixSourceTarget 11.2.0.3/11.2.0.418.1 12.1.0.1/12.1.0.218.1 12.2.0.118.1Intermediate upgrades needs to be carried for following releasesIndirect Upgrade MatrixSource Database Intermediate upgrade path Target database 11.2.0.1/11.2.0.2-->11.2.0.3/11.2.0.4-->18.1 11.1.0.6/11.1.0.7-->11.2.0.3/11.2.0.4-->18.1 10.2.0.2, 10.2.0.3, 10.2.0.4, 10.2.0.5-->11.2.0.3/11.2.0.4/12.1.0.1/12.1.0.2-->18.1 10.1.0.5-->11.2.0.3/11.2.0.4/12.1.0.1/12.1.0.2-->18.1 9.2.0.8 or earlier-->11.2.0.3/11.2.0.4-->18.1For any multi-step upgrade, if you must carry out two upgrades to upgrade to the current release, then you must run the preupgrade script twice: First, for the intermediate upgrade release, and second, for the target upgrade target release. For example, if the database from which you are upgrading is running Oracle Database 10g, then follow these steps:
This restriction does not apply if you use Oracle Data Pump export/import to migrate data to the new release. For example:
Empty the recycle bin Check materialized viewsCheck for INVALID objects in SYS and SYSTEM Check for duplicate objects in SYS and SYSTEM Check for INVALID, mandatory, obsolete components Check the status of all materialized views (MV), and refresh any materialized views that are not fresh. Check the size of your materialized view logs. If any materialized view logs have non-zero rows, then refresh the base table materialized views. Check the size of direct loader logs and PMOP logs (partition maintenance operation logs). If any direct loader logs or PMOP logs have non-zero rows, then refresh the MVs indicated by the logs Before upgrading Oracle Database, you must wait until all materialized views have completed refreshing. Run the following SQL query: SQL> SELECT o.name FROM sys.obj$ o, sys.user$ u, sys.sum$ s WHERE o.type# = 42 AND bitand(s.mflags, 8) =8; Copying Transparent Encryption Oracle WalletsIf Oracle wallet used with Transparent Data Encryption (TDE), then copy the sqlnet.ora and wallet file to the new Oracle home. You must copy the sqlnet.ora and the wallet file manually before starting the upgrade.
For example: SQL> STARTUP MOUNT; SQL> ALTER SYSTEM SET ENCRYPTION WALLET OPEN Understanding Password Case SensitivityStarting with Oracle Database 12c release 2 (12.2), the default password-based authentication protocol configuration excludes the use of the case-insensitive 10G password version. By default, the SQLNET.ORA parameter SQLNET.ALLOWED_LOGON_VERSION_SERVER is set to 12, which is an Exclusive Mode. For greater security, Oracle recommends that you leave case-sensitive password-based authentication enabled. This setting is the default. However, you can temporarily disable case-sensitive authentication during the upgrade to new Oracle Database releases. After the upgrade, you can then decide if you want to enable the case-sensitive password-based authentication feature as part of your implementation plan to manage your password versions. Before upgrading, Oracle recommends that you determine if this change to the default password-based authentication protocol configuration affects you. Perform the following checks:
Running Upgrades with Read-Only TablespacesUse the Parallel Upgrade Utility with the -T option to take schema-based tablespaces offline during upgrade. Oracle Database can read file headers created in earlier releases, so you are not required to do anything to them during the upgrade. The file headers of READ ONLY tablespaces are updated when they are changed to READ WRITE. If the upgrade suffers a catastrophic error, so that the upgrade is unable to bring the tablespaces back online, then review the upgrade log files. The log files contain the actual SQL statements required to make the tablespaces available. To bring the tablespaces back online, you must run the SQL statements in the log files for the database, or run the log files for each PDB. Viewing Tablespace Commands In Upgrade Log Files If a catastrophic upgrade failure occurs, then you can navigate to the log directory (Oracle_base/cfgtoologs/dbua), and run commands in the log files manually to bring up tablespaces. You can view tablespace commands in the following log files: Non-CDB Upgrades: catupgrd0.log PDB databases: catupgrdpdbname0.log, where pdbname is the name of the PDB that you are upgrading. At the beginning of each log file, you find SQL statements such as the following, which sets tables to READ ONLY: SQL> ALTER TABLESPACE <Tablespace Name> READ ONLY; Tablespace altered. Near the end of each log file, you find SQL statements to reset tables to READ WRITE: SQL> ALTER TABLESPACE <Tablespace Name> READ WRITE; Tablespace altered. Preparing the New Oracle Home for Upgrading
For security reasons, different Microsoft Windows user accounts configured as Oracle home users for different Oracle homes are not allowed to share the same Oracle Base.
For Oracle Database releases earlier than 12.1 using Oracle Label Security and Oracle Database Vault, you must run the OLS preprocess script before you upgrade. If you are upgrading from a database earlier than Oracle Database release 12.1 that uses Oracle Label Security (OLS) and Oracle Database Vault, then you must first run the OLS preprocess script, olspreupgrade.sql, to process the aud$ table contents. The OLS upgrade moves the aud$ table from the SYSTEM schema to the SYS schema. The olspreupgrade.sql script is a preprocessing script required for this move. Running the olspreupgrade.sql script before upgrading is mandatory for upgrading databases earlier than Oracle Database release 12.1 that use Oracle Label Security and Oracle Database Vault. Once you have upgraded to Oracle Database release 12.1, you do not have to perform the OLS preprocessing procedure going forward to patch or upgrade the database. Oracle Database Vault and Upgrades of Oracle Database Release 11.2 If Oracle Label Security is installed in the earlier release that you are upgrading, then grant the DV_PATCH_ADMIN role to SYS. To run the OLS preprocess script on a release 11.2 database before upgrading: 1. Copy the following scripts script from the newly installed Oracle home (18.1) to the Oracle home of the database that needs to be upgraded (11.2): ORACLE_HOME/rdbms/admin/olspreupgrade.sql ORACLE_HOME/rdbms/admin/emremove.sql ORACLE_HOME/rdbms/admin/catnoamd.sql 2. Start SQL*Plus and connect as DVOWNER to the database that you want to upgrade. 3. Run the following statement: SQL> GRANT DV_PATCH_ADMIN to SYS; 4. At the system prompt, connect SYS as SYSDBA: CONNECT SYS AS SYSDBA 5. Run the preprocess scripts for Data Vault ORACLE_HOME/rdbms/admin/olspreupgrade.sql ORACLE_HOME/rdbms/admin/emremove.sql ORACLE_HOME/rdbms/admin/catnoamd.sql You may continue to run your applications on the database while the preprocess scripts are running. 6. After the olspreupgrade.sql completes its run successfully, start SQL*Plus and connect to the database as DVOWNER. 7. Run the following SQL statement: SQL> REVOKE DV_PATCH_ADMIN from SYS; Granting the DV_PATCH_ADMIN Role to SYS for Oracle Database Vault If Oracle Database Vault is enabled, then to perform checks for Oracle Data Vault, the upgrade process requires running three SQL scripts - olspreupgrade.sql, emremove.sql, catnoamd.sql Start SQL*Plus and connect as DVOWNER to the database that you want to upgrade. Run the following statement: SQL> GRANT DV_PATCH_ADMIN to SYS; Backing Up Oracle Database for UpgradingSuggested to backup Oracle database after you run the Pre-Upgrade Information Tool. Take backup or create a guaranteed restore point or both. Test your backup. Ensure there is a proper fallback plan in case of any issues. rman "target / nocatalog" RUN { ALLOCATE CHANNEL chan_name TYPE DISK; BACKUP DATABASE FORMAT 'some_backup_directory%U' TAG before_upgrade; BACKUP CURRENT CONTROLFILE FORMAT 'controlfile location and name'; } Manually remove DB control with emremove.sql Stop/shutdown DB control emctl stop dbconsoleLogin as sysdba SQL>SET SERVEROUTPUT ON SQL>@emremove.sql >> Script located in new 12c ORACLE_HOME/rdbms/admin Manually remove ORACLE_HOME/HOSTNAME_SID/ and ORACLE_HOME/oc4j/j2ee/OC4J_DBConsole_HOSTNAME_SID directory from the system Ensure no files are in Back up mode before starting the upgradeOn windows delete DB Console service OracleDBConsoleSID Run the following statement: SQL> SELECT * FROM v$backup WHERE status != 'NOT ACTIVE'; Purge Recycle bin To empty the database recycle bin, run the following command: SQL> PURGE DBA_RECYCLEBINNote: The database recycle bin must be empty during the upgrade process to avoid possible ORA-00600 errors, and to minimize the upgrade time. PerformancePreserve performance statistics Check network performance Gather Optimizer statistics To decrease the amount of downtime, gather statistics. Oracle recommends that you use the DBMS_STATS.GATHER_DICTIONARY_STATS procedure to gather these statistics. For example, enter the following SQL statement: SQL> EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;Checking Time zone settingsTime zone should less than or equal to target database time zone version. If source is having higher time zone, then apply time zone patch on target ORACLE_HOME to match the source. About Upgrading Oracle OLAP Data Security PoliciesData security roles defined in a release 11g Oracle Database instance are not automatically converted to ORAS. Before upgrading Oracle Database Release 11g to a current Oracle Database release, delete any data security roles that are defined in the 11g database. After the upgrade, you can use the new release Oracle Database Analytic Workspace Manager to define the data security roles again. If DB us upgraded from an 11g database without deleting the 11g data security roles, then any data security policies that include a data security role are invalid in the later Oracle Database releases. Step 5: Preupgrade stepExecute Preupgrade script from source home $Earlier_release_Oracle_home/jdk/bin/java -jar $New_release_Oracle_home/rdbms/admin/preupgrade.jar FILE TEXT DIR output_dir FILE - Use this option to direct output to a file TEXT - Use this option to specify log should be in Text format (other option is to have XML output) DIR - Logs will be created under <output_dir> It is recommended to execute pre-upgrade fixup script, if any, which are AUTO FIXABLE Preupgrade fixup scriptExecute Preupgrade fixup scripts preupgrade_fixups.sql Dependencies on Network Utility PackagesExecute the following query SQL> SELECT * FROM DBA_DEPENDENCIES WHERE referenced_name IN ('UTL_TCP','UTL_SMTP','UTL_MAIL','UTL_HTTP','UTL_INADDR','DBMS_LDAP') AND owner NOT IN ('SYS','PUBLIC','ORDPLUGINS'); To ensure that the new access controls are part of your upgrade testing, prepare a post-upgrade script to make the scripts available in your database environment. After the upgrade, grant specific required privileges. Access is based on the usage in the original database. Check Time zone versionCheck if target database's time zone version is lower than the source database time zone version. If yes, before starting upgrade time zone should be upgrade without fail. RDBMS DST patches are available in Note 412160.1 Step 6: Upgrade Database to 18cShut down the database. SQL> SHUTDOWN IMMEDIATE Steps specific to Windows : If your operating system is Windows, then complete the following steps: a. Stop the OracleServiceSID Oracle service of the database you are upgrading,where SID is the instance name. For example, if your SID is ORCL, then enter the following at a command prompt: C:\> NET STOP OracleServiceORCL b. Delete the Oracle service at a command prompt using ORADIM. Refer to your platform guide for a complete list of the ORADIM syntax and commands. For example, if your SID is ORCL, then enter the following command. C:\> ORADIM -DELETE -SID ORCL c. Create the service for the new release Oracle Database at a command prompt using the ORADIM command of the new Oracle Database release. For example: C:\> ORADIM -NEW -SID SID -SYSPWD PASSWORD -MAXUSERS USERS -STARTMODE AUTO -PFILE ORACLE_HOME\DATABASE\INITSID.ORA For Unix/Linux Set the environment variables to point to target ORACLE_HOME export ORACLE_HOME=<path to Oracle 18c> export PATH=$ORACLE_HOME/bin:$PATH export ORACLE_BASE=<path to Oracle_Base set during installation> Copy the SPFILE.ORA or INIT.ORA file from the old Oracle home to the target Oracle home. Start DB in upgrade mode from target ORACLE_HOME CONNECT / AS SYSDBA SQL> startup upgrade; SQL> exit On Linux/Unix cd $ORACLE_HOME/bin ./dbupgrade On Windows cd %ORACLE_HOME%\bin dbupgrade Execute Post-Upgrade Status Tool, utlu122s.sql and review the upgrade spool log file. You run the Post-Upgrade Status Tool in the environment of the new release. $ sqlplus "/as sysdba" SQL> STARTUP SQL> @utlu122s.sql Verify the upgrade log whether catuppst.sql has been executed or not. If not, execute it manually from new ORACLE_HOME, located at $ORACLE_HOME/rdbms/admin directory SQL> @catuppst.sql Run utlrp.sql to recompile any remaining stored PL/SQL and Java code in another session. SQL> @utlrp.sql Check the state of the Oracle Data Dictionary for diagnosing upgrades and migrations. The dbupgdiag.sql script collects upgrade and migration diagnostic information about the current state of the data dictionary. You can run the script in SQL*Plus on the upgraded database as the SYS user. Refer Note 556610.1 Script to Collect DB Upgrade/Migrate Diagnostic Information (dbupgdiag.sql) If the dbupgdiag.sql script reports any invalid objects, run $ORACLE_HOME/rdbms/admin/utlrp.sql (multiple times) to validate the invalid objects in the database, until there is no change in the number of invalid objects. After validating the invalid objects, re-run dbupgdiag.sql in the upgraded database once again and make sure that everything is fine. If you are using Oracle Clusterware, then you must upgrade the Oracle Clusterware keys for the database. Run srvctl for Oracle Database 18c to upgrade the database. For example: ORACLE_HOME/bin/srvctl upgrade database -db name -o ORACLE_HOME Step 7: Post-upgrade Setting Environment variables on Linux and UnixConfirm that the following environment variables point to the directories of the new Oracle home: ORACLE_HOME PATH Update oratab entriesModify the corresponding entry in the /etc/oratab file to point to the new ORACLE_HOME location. Post-upgrade fixup scriptExecute post-upgrade fixup scripts generated by the pre-upgrade script. SQL> @postupgrade_fixups.sql Recovery Catalog UpgradeIf you use a version of the recovery catalog schema that is older than that required by the RMAN client, then you must upgrade it. You can upgrade the Recovery catalog by executing the UPGRADE CATALOG command Please refer to Oracle documentation under "Upgrading the Recovery Catalog" for complete information and steps Upgrade the Time Zone File Version After Upgrading Oracle DatabaseIf the Pre-Upgrade Information Tool instructed you to upgrade the time zone files after completing the database upgrade, then use the DBMS_DST PL/SQL package to update the RDBMS DST (timezone) version. Follow the procedure in Oracle documentation under "Steps to Upgrade Time Zone File and Timestamp with Time Zone Data" and Note 1509653.1 "Updating the RDBMS DST version in 12c Release 1 (12.1.0.1 and up) using DBMS_DST" Upgrading Statistics TablesIf you created statistics tables using the DBMS_STATS.CREATE_STAT_TABLE procedure, then upgrade these tables by running DBMS_STATS.UPGRADE_STAT_TABLE. In the following example, SYS is the owner of the statistics table and 'dictstattab' is the name of the statistics table. EXECUTE DBMS_STATS.UPGRADE_STAT_TABLE('SYS', 'dictstattab'); Perform this procedure for each statistics table. |
| 欢迎光临 重庆思庄Oracle、Redhat认证学习论坛 (http://bbs.cqsztech.com/) | Powered by Discuz! X3.2 |