在 oracle Enterprise linux 上升级到 Oracle RAC 11g了解如何在 Oracle Enterprise Linux 5 上逐步将 Oracle RAC 10g 第 2 版升级到 Oracle RAC 11g
了解如何在 Oracle Enterprise Linux 5 上逐步将 Oracle RAC 10g 第 2 版升级到 Oracle RAC 11g。
作者：Vincent Chan
2007 年 10 月发布
Oracle 最近发布了大家期待已久的 Oracle 数据库 11g（即，新一代网格计算）。该版本在网格计算方面取得了显著进步，提供了大量令人难以置信的新特性，从而进一步改进了数据库可用性和可管理性、性能监视、诊断以及数据库升级过程。其中不乏令人印象深刻的创新功能，例如 Oracle 真正应用测试、具有实时查询功能的物理备用、新的分区方案以及结果缓存等关键特性。
转自：Http://www.oracle.com/technology/global/cn/pub/articles/chan-upgrade.html
基本上，将 Oracle RAC 10g 升级到 Oracle RAC 11g 的方法有三种：
推荐的方法是，使用 DBUA 升级您的数据库和自动存储管理 (ASM)。DBUA 可以执行许多人工任务，从而显著简化升级过程。在本指南中，我们将描述使用 DBUA 进行升级的过程。
本指南相关下载：
本指南分为以下步骤：
Oracle RAC 10g 环境概述：
| 主机名 | 实例名 | 数据库名 | 集群件文件存储 | 数据库文件存储 |
| merlin1 | devdb1 | devdb | Raw | ASM |
| merlin2 | devdb2 | devdb | Raw | ASM |
| 主机名 | Oracle 集群件主目录 | Oracle ASM 主目录 | Oracle 主目录 |
| merlin1 | /u02/crs/oracle | /u01/app/oracle/product/10.2.0/db_1 | /u01/app/oracle/product/10.2.0/db_1 |
| merlin2 | /u02/crs/oracle | /u01/app/oracle/product/10.2.0/db_1 | /u01/app/oracle/product/10.2.0/db_1 |
Oracle 集群件文件 | 设备名 |
| Oracle Cluster ReGIStry | /dev/sdb1 |
| Voting Disk | /dev/sdc1 |
Oracle RAC 11g 环境概述：
| 主机名 | 实例名 | 数据库名 | 集群件文件存储 | 数据库文件存储 |
| merlin1 | devdb1 | devdb | Raw | ASM |
| merlin2 | devdb2 | devdb | Raw | ASM |
| 主机名 | Oracle 集群件主目录 | Oracle ASM 主目录 | Oracle 主目录 |
| merlin1 | /u02/crs/oracle | /u01/app/oracle/product/11.1.0/asm | /u01/app/oracle/product/11.1.0/db_1 |
| merlin2 | /u02/crs/oracle | /u01/app/oracle/product/11.1.0/asm | /u01/app/oracle/product/11.1.0/db_1 |
| Oracle 集群件文件 | 设备名 |
| Oracle Cluster Registry | /dev/sdb1 |
| Voting Disk | /dev/sdc1 |
您将在每个节点的 Oracle 集群件主目录、Oracle ASM 主目录和 Oracle 主目录中安装软件，以实现冗余和更高的可用性。
在升级到 Oracle RAC 11g 之前，备份您的 Oracle RAC 10g 环境。
以 root 用户身份安装以下程序包（如果它们尚未安装到 RAC 节点上）。这些程序包可以从 Enterprise-R5-GA-Server-i386-disc2.iso 和 Enterprise-R5-GA-Server-i386-disc3.iso 中提取。
提取这些程序包之后，以 root 用户身份执行以下命令。
# ls -1elfutils-libelf-devel-0.125-3.e15.i386.rpmlibaio-devel-0.3.106-3.2.i386.rpmunixODBC-2.2.11-7.1.i386.rpmunixODBC-devel-2.2.11-7.1.i386.rpm ## rpm -Uvh *.rpm
最低内核参数要求如下所示。如果必要，在两个节点的 /etc/sysctl.conf 中配置相应的参数。
kernel.shmall = 2097152kernel.shmmax = 2147483648kernel.shmmni = 4096kernel.sem = 250 32000 100 128fs.file-max = 65536net.ipv4.ip_local_port_range = 1024 65000net.core.rmem_default = 4194304net.core.rmem_max = 4194304net.core.wmem_default = 262144net.core.wmem_max = 262144
如果您希望执行滚动升级，在升级到 Oracle RAC 11g 之前，Oracle 集群件的版本必须至少是 10.2.0.3 或带有 CRS 2 号补丁包的 10.2.0.2（参见错误 5256865）。10.2.0.3 补丁集 (5337014) 可以从 Oracle Metalink 下载。
参见 Oracle Metalink 说明 419058.1 或针对 Linux x86 的 Oracle 10.2.0.3 补丁集的相关信息。merlin1-> crsctl query crs activeversionCRS active version on the cluster is [10.2.0.3.0]
集群验证实用程序 (CVU) 可降低安装 RAC 的复杂性并减少所花费的时间。该工具将扫描集群环境中所有必需的组件，以确保满足成功安装的所有条件。
从 OTN 下载 Oracle 集群件 11.1.0.6 软件并解压缩到临时目录，然后执行 runcluvfy.sh。
/stage/clusterware/runcluvfy.sh stage -pre crsinst -n all -verbose > /tmp/prechecks.log
验证是否满足所有前提条件。您可以忽略针对 openmotif-2.2.3-3.RHEL3 的“Package existence checked failed”消息。
merlin1-> srvctl stop database -d devdbmerlin1-> srvctl stop asm -n merlin1merlin1-> srvctl stop asm -n merlin2merlin1-> srvctl stop nodeapps -n merlin1merlin1-> srvctl stop nodeapps -n merlin2merlin1-> crs_stat -tName Type Target State Host------------------------------------------------------------ora.devdb.db application OFFLINE OFFLINEora....b1.inst application OFFLINE OFFLINEora....b2.inst application OFFLINE OFFLINEora....SM1.asm application OFFLINE OFFLINEora....N1.lsnr application OFFLINE OFFLINEora....in1.gsd application OFFLINE OFFLINEora....in1.ons application OFFLINE OFFLINEora....in1.vip application OFFLINE OFFLINEora....SM2.asm application OFFLINE OFFLINEora....N2.lsnr application OFFLINE OFFLINEora....in2.gsd application OFFLINE OFFLINEora....in2.ons application OFFLINE OFFLINEora....in2.vip application OFFLINE OFFLINE
在每个节点上执行 preupdate.sh 脚本，以准备要升级的集群件主目录。该脚本可停止 Oracle 集群件组件并更改 Oracle 集群件主目录中文件的权限。
以 root 用户身份在每个节点上执行，# cd /stage/clusterware/upgrade# ./preupdate.sh -crshome /u02/crs/oracle -crsuser oracleShutting down Oracle Cluster Ready Services (CRS):Stopping resources.This could take several minutes.Successfully stopped CRS resources.Stopping CSSD.Shutting down CSS daemon.Shutdown request successfully issued.Shutdown has begun.The daemons should exit soon.Checking to see if Oracle CRS stack is down...Oracle CRS stack is down now.
您只需在一个节点上运行 Oracle Universal Installer (OUI)。OUI 将在每个节点上自动安装现有的二进制软件。
以 oracle 用户身份在 merlin1 上执行，
merlin1-> /stage/clusterware/runInstaller
在 merlin1 上，
# /u02/crs/oracle/install/rootupgradeChecking to see if Oracle CRS stack is already up...copying ONS config file to 11.1 CRS home/bin/cp:`/u02/crs/oracle/opmn/conf/ons.config' and `/u02/crs/oracle/opmn/conf/ons.config' are the same file/u02/crs/oracle/opmn/conf/ons.config was copied successfully to /u02/crs/oracle/opmn/conf/ons.configWARNING:directory '/u02/crs' is not owned by rootWARNING:directory '/u02' is not owned by rootOracle Cluster Registry configuration upgraded successfullyAdding daemons to inittabAttempting to start Oracle Clusterware stackWaiting for Cluster Synchronization Services daemon to startCluster Synchronization Services daemon has startedWaiting for Event Manager daemon to startEvent Manager daemon has startedCluster Ready Services daemon has startedOracle CRS stack is running under init(1M)clscfg:EXISTING configuration version 3 detected.clscfg:version 3 is 10g Release 2.Successfully accumulated necessary OCR keys.Using ports:CSS=49895 CRS=49896 EVMC=49898 and EVMR=49897.node <nodenumber>:<nodename> <private interconnect name> <hostname>node 1:merlin1 merlin1-priv merlin1Creating OCR keys for user 'root', privgrp 'root'..Operation successful.clscfg -upgrade completed successfullyCRS stack on this node, is successfully upgraded to 11.1.0.6.0Checking the existence of nodeapps on this nodeCreating '/u02/crs/oracle/install/paramfile.crs' with data used for CRS configurationSetting CRS configuration values in /u02/crs/oracle/install/paramfile.crs在 merlin2 上，
# /u02/crs/oracle/install/rootupgradeChecking to see if Oracle CRS stack is already up...copying ONS config file to 11.1 CRS home/bin/cp:`/u02/crs/oracle/opmn/conf/ons.config' and `/u02/crs/oracle/opmn/conf/ons.config' are the same file/u02/crs/oracle/opmn/conf/ons.config was copied successfully to /u02/crs/oracle/opmn/conf/ons.configWARNING:directory '/u02/crs' is not owned by rootWARNING:directory '/u02' is not owned by rootOracle Cluster Registry configuration upgraded successfullyAdding daemons to inittabAttempting to start Oracle Clusterware stack Waiting for Cluster Synchronization Services daemon to startCluster Synchronization Services daemon has startedWaiting for Event Manager daemon to startWaiting for Event Manager daemon to startEvent Manager daemon has startedCluster Ready Services daemon has startedOracle CRS stack is running under init(1M)clscfg:EXISTING configuration version 4 detected.clscfg:version 4 is 11 Release 1.Successfully accumulated necessary OCR keys.Using ports:CSS=49895 CRS=49896 EVMC=49898 and EVMR=49897.node <nodenumber>:<nodename> <private interconnect name> <hostname>node 2:merlin2 merlin2-priv merlin2Creating OCR keys for user 'root', privgrp 'root'..Operation successful.clscfg -upgrade completed successfullyCRS stack on this node, is successfully upgraded to 11.1.0.6.0Checking the existence of nodeapps on this nodeCreating '/u02/crs/oracle/install/paramfile.crs' with data used for CRS configurationSetting CRS configuration values in /u02/crs/oracle/install/paramfile.crsmerlin1-> $ORA_CRS_HOME/bin/crsctl check crsCluster Synchronization Services appears healthyCluster Ready Services appears healthyEvent Manager appears healthymerlin1-> $ORA_CRS_HOME/bin/crsctl query crs softwareversionOracle Clusterware version on node [merlin1] is [11.1.0.6.0] merlin1-> $ORA_CRS_HOME/bin/crsctl query crs activeversionOracle Clusterware active version on the cluster is [11.1.0.6.0]merlin2-> $ORA_CRS_HOME/bin/crsctl check crs Cluster Synchronization Services appears healthyCluster Ready Services appears healthyEvent Manager appears healthymerlin2-> $ORA_CRS_HOME/bin/crsctl query crs softwareversionOracle Clusterware version on node [merlin2] is [11.1.0.6.0] merlin2-> $ORA_CRS_HOME/bin/crsctl query crs activeversionOracle Clusterware active version on the cluster is [11.1.0.6.0]
merlin1-> /u02/crs/oracle/bin/cluvfy stage -post crsinst -n merlin1,merlin2PerfORMing post-checks for cluster services setupChecking node reachability...Node reachability check passed from node "merlin1".Checking user equivalence...User equivalence check passed for user "oracle".Checking Cluster manager integrity...Checking CSS daemon...Daemon status check passed for "CSS daemon".Cluster manager integrity check passed.Checking cluster integrity...Cluster integrity check passedChecking OCR integrity...Checking the absence of a non-clustered configuration...All nodes free of non-clustered, local-only configurations.Uniqueness check for OCR device passed.Checking the version of OCR...OCR of correct Version "2" exists.Checking data integrity of OCR...Data integrity check for OCR passed.OCR integrity check passed.Checking CRS integrity...Checking daemon liveness...Liveness check passed for "CRS daemon".Checking daemon liveness...Liveness check passed for "CSS daemon".Checking daemon liveness...Liveness check passed for "EVM daemon".Checking CRS health...CRS health check passed.CRS integrity check passed.Checking node application existence...Checking existence of VIP node application (required)Check passed.Checking existence of ONS node application (optional)Check passed.Checking existence of GSD node application (optional)Check passed.Post-check for cluster services setup was successful.
在此阶段，Oracle 集群件已经升级到 Oracle 集群件 11g，并且所有集群资源应该处于运行状态。merlin1-> $ORA_CRS_HOME/bin/crs_stat -tName Type Target State Host------------------------------------------------------------ora.devdb.db application ONLINE ONLINE merlin1ora....b1.inst application ONLINE ONLINE merlin1ora....b2.inst application ONLINE ONLINE merlin2ora....SM1.asm application ONLINE ONLINE merlin1ora....N1.lsnr application ONLINE ONLINE merlin1 ora....in1.gsd application ONLINE ONLINE merlin1ora....in1.ons application ONLINE ONLINE merlin1ora....in1.vip application ONLINE ONLINE merlin1ora....SM2.asm application ONLINE ONLINE merlin2ora....N2.lsnr application ONLINE ONLINE merlin2ora....in2.gsd application ONLINE ONLINE merlin2ora....in2.ons application ONLINE ONLINE merlin2ora....in2.vip application ONLINE ONLINE merlin2
以 oracle 用户身份在这两个节点上创建新的 Oracle 主目录。
mkdir -p /u01/app/oracle/product/11.1.0/db_1
从 OTN 上下载 Oracle 数据库软件。
以 oracle 用户身份在 merlin1 上执行，
merlin1-> /stage/database/runInstaller
在运行数据库升级助手 (DBUA) 之前，执行数据库升级前检查（即 utlu111i.sql）以检验是否满足所有前提条件。作为升级过程的一部分，DBUA 会自动将 cluster_database 参数由 true 更改为 false。进行必要的修改后，重新执行数据库升级前检查脚本。
以 sys 用户身份连接，
SQL> spool /tmp/utlu111i.logSQL> @/u01/app/oracle/product/11.1.0/db_1/rdbms/admin/utlu111iOracle Database 11.1 Pre-Upgrade Information Tool 08-13-2007 18:03:45 . ********************************************************************** Database: ********************************************************************** --> name:DEVDB--> version: 10.2.0.3.0 --> compatible: 10.2.0.1.0 --> blocksize: 8192 --> platform.:Linux IA (32-bit) --> timezone file:V4 . ********************************************************************** Tablespaces:[make adjustments in the current environment] ********************************************************************** --> SYSTEM tablespace is adequate for the upgrade. .... minimum required size:743 MB --> UNDOTBS1 tablespace is adequate for the upgrade..... minimum required size:315 MB --> SYSAUX tablespace is adequate for the upgrade. .... minimum required size:458 MB --> TEMP tablespace is adequate for the upgrade. .... minimum required size:61 MB --> EXAMPLE tablespace is adequate for the upgrade. .... minimum required size:66 MB . ********************************************************************** Update Parameters:[Update Oracle Database 11.1 init.ora or spfile] ********************************************************************** -- No update parameter changes are required. . ********************************************************************** Renamed Parameters:[Update Oracle Database 11.1 init.ora or spfile] ********************************************************************** -- No renamed parameters found.No changes are required. . ********************************************************************** Obsolete/Deprecated Parameters:[Update Oracle Database 11.1 init.ora or spfile]********************************************************************** --> "background_dump_dest" replaced by "diagnostic_dest" --> "user_dump_dest" replaced by "diagnostic_dest" --> "core_dump_dest" replaced by "diagnostic_dest" . ********************************************************************** Components:[The following database components will be upgraded or installed] ********************************************************************** --> Oracle Catalog Views [upgrade] VALID --> Oracle Packages and Types [upgrade] VALID --> jserver JAVA Virtual Machine [upgrade] VALID --> Oracle XDK for Java [upgrade] VALID --> Real Application Clusters [upgrade] VALID --> Oracle Workspace Manager [upgrade] VALID --> OLAP Analytic Workspace [upgrade] VALID --> OLAP Catalog [upgrade] VALID --> EM Repository [upgrade] VALID --> Oracle Text [upgrade] VALID --> Oracle XML Database [upgrade] VALID --> Oracle Java Packages [upgrade] VALID --> Oracle interMedia [upgrade] VALID --> Spatial [upgrade] VALID --> Data Mining [upgrade] VALID --> Expression Filter [upgrade] VALID --> Rule Manager [upgrade] VALID --> Oracle OLAP api [upgrade] VALID . ********************************************************************** Miscellaneous Warnings ********************************************************************** WARNING:--> The "cluster_database" parameter is currently "TRUE" and must be set to "FALSE" prior to running the upgrade. WARNING:--> Database contains stale optimizer statistics. .... Refer to the 11g Upgrade Guide for instructions to update .... statistics prior to upgrading the database. .... Component Schemas with stale statistics: .... SYS WARNING:--> Database contains schemas with objects dependent on network packages. .... Refer to the 11g Upgrade Guide for instructions to configure Network ACLs. WARNING:--> EM Database Control Repository exists in the database. .... Direct downgrade of EM Database Control is not supported.Refer to the .... 11g Upgrade Guide for instructions to save the EM data prior to upgrade. . PL/SQL procedure successfully completed.SQL> spool off
修改 ORACLE_HOME 以便在两个节点上反映新的 Oracle 数据库 11g 目录。
merlin1-> more .profileexport PS1="`/bin/hostname -s`-> "export EDITOR=viexport ORACLE_SID=devdb1export ORACLE_BASE=/u01/app/oracleexport ORACLE_HOME=$ORACLE_BASE/product/11.1.0/db_1export ORA_CRS_HOME=/u02/crs/oracleexport LD_LIBRARY_PATH=$ORACLE_HOME/libexport PATH=$ORACLE_HOME/bin:/bin:/usr/bin:/usr/sbin:/usr/local/bin:/usr/X11R6/binumask 022
以 oracle 用户身份在 merlin1 上执行 dbua。
merlin1-> ../.profilemerlin1-> which dbua/u01/app/oracle/product/11.1.0/db_1/bin/dbua merlin1-> dbua
图 1
图 2
图 3
图 4
图 5
SQL> select comp_name,version,status from dba_registry;COMP_NAME VERSION STATUS --------------------------------------- ---------- ------ Oracle Enterprise Manager 11.1.0.6.0 VALID OLAP Catalog11.1.0.6.0 VALID Spatial 11.1.0.6.0 VALID Oracle Multimedia 11.1.0.6.0 VALID Oracle XML Database 11.1.0.6.0 VALID Oracle Text 11.1.0.6.0 VALID Oracle Data Mining 11.1.0.6.0 VALID Oracle Expression Filter 11.1.0.6.0 VALID Oracle Rule Manager 11.1.0.6.0 VALID Oracle Workspace Manager 11.1.0.6.0 VALID Oracle Database Catalog Views 11.1.0.6.0 VALID Oracle Database Packages and Types 11.1.0.6.0 VALID JServer JAVA Virtual Machine 11.1.0.6.0 VALID Oracle XDK 11.1.0.6.0 VALID Oracle Database Java Packages 11.1.0.6.0 VALID OLAP Analytic Workspace 11.1.0.6.0 VALID Oracle OLAP API 11.1.0.6.0 VALID Oracle Real Application Clusters 11.1.0.6.0 VALID 18 rows selected.merlin1-> srvctl config database -d devdbmerlin1 devdb1 /u01/app/oracle/product/11.1.0/db_1merlin2 devdb2 /u01/app/oracle/product/11.1.0/db_1merlin1-> $ORA_CRS_HOME/bin/crs_stat -tName Type Target State Host------------------------------------------------------------ora.devdb.db application ONLINE ONLINE merlin1ora....b1.inst application ONLINE ONLINE merlin1ora....b2.inst application ONLINE ONLINE merlin2ora....SM1.asm application ONLINE ONLINE merlin1ora....N1.lsnr application ONLINE ONLINE merlin1ora....in1.gsd application ONLINE ONLINE merlin1ora....in1.ons application ONLINE ONLINE merlin1ora....in1.vip application ONLINE ONLINE merlin1ora....SM2.asm application ONLINE ONLINE merlin2ora....N2.lsnr application ONLINE ONLINE merlin2ora....in2.gsd application ONLINE ONLINE merlin2ora....in2.ons application ONLINE ONLINE merlin2ora....in2.vip application ONLINE ONLINE merlin2新的诊断位置
SQL> select name, value from v$parameter where name like '%dump_dest' or name like 'diag%';NAME VALUE-------------------- --------------------------------------------------background_dump_dest /u01/app/oracle/diag/rdbms/devdb/devdb1/traceuser_dump_dest /u01/app/oracle/diag/rdbms/devdb/devdb1/tracecore_dump_dest /u01/app/oracle/diag/rdbms/devdb/devdb1/cdumpdiagnostic_dest /u01/app/oracle
单独的 ASM 主目录不是必需的；但是，具有一个单独的 ASM 主目录具有以下优点：可让您将补丁或补丁集独立地从 ASM 主目录应用到 Oracle RDBMS 主目录。在同一个节点上运行一个以上的数据库实例时，具有单独的 ASM 主目录和 RDBMS 主目录尤其受益。在 Oracle RDBMS 主目录需要修补时，ASM 实例可用性将不会受到影响。
此时，您的 ASM 主目录仍然在 Oracle 数据库 10g 主目录下运行。
merlin1-> srvctl config asm -n merlin1+ASM1 /u01/app/oracle/product/10.2.0/db_1merlin1-> srvctl config asm -n merlin2+ASM2 /u01/app/oracle/product/10.2.0/db_1
在两个节点上，以 oracle 用户身份创建新的 ASM 主目录
mkdir /u01/app/oracle/product/11.1.0/asm并修改 shell 配置文件中的 ORACLE_HOME 变量，以反映新的 ASM 主目录。
ORACLE_HOME=/u01/app/oracle/product/11.1.0/asm
以 oracle 用户身份在 merlin1 上执行，
merlin1-> ../.profile merlin1-> /stage/database/runInstaller
在 merlin1 上，以 oracle 用户身份停止数据库并启动 DBUA。
merlin1-> srvctl stop database -d devdbmerlin1-> /u01/app/oracle/product/11.1.0/asm/bin/dbua
图 6
图 7
merlin1-> srvctl config asm -n merlin1+ASM1 /u01/app/oracle/product/11.1.0/asmmerlin1-> srvctl config asm -n merlin2+ASM2 /u01/app/oracle/product/11.1.0/asmmerlin1-> srvctl start database -d devdbmerlin1-> crs_stat -t Name Type Target State Host------------------------------------------------------------ora.devdb.db application ONLINE ONLINE merlin1ora....b1.inst application ONLINE ONLINE merlin1ora....b2.inst application ONLINE ONLINE merlin2ora....SM1.asm application ONLINE ONLINE merlin1ora....N1.lsnr application ONLINE ONLINE merlin1ora....in1.gsd application ONLINE ONLINE merlin1ora....in1.ons application ONLINE ONLINE merlin1ora....in1.vip application ONLINE ONLINE merlin1ora....SM2.asm application ONLINE ONLINE merlin2ora....N2.lsnr application ONLINE ONLINE merlin2ora....in2.gsd application ONLINE ONLINE merlin2ora....in2.ons application ONLINE ONLINE merlin2ora....in2.vip application ONLINE ONLINE merlin2
图 8
作为最后一步，要利用 Oracle 数据库 11g 的新特性，必须将数据库兼容性参数和磁盘组兼容性属性更改为 11.1.0。
在 devdb1 实例上，
SQL> show parameter compatibleNAME TYPE VALUE------------------------ ----------- -------------------------compatible string 10.2.0.1.0SQL> alter system set compatible='11.1.0' scope=spfile;System altered.在 merlin1 上，重新启动数据库，
merlin1-> srvctl stop database -d devdbmerlin1-> srvctl start database -d devdb
在 ASM1 实例上，
SQL> select name,compatibility,database_compatibility from v$asm_diskgroup;NAME COMPATIBILITY DATABASE_COMPATIBILI--------------- ------------- --------------------DG1 10.1.0.0.0 10.1.0.0.0RECOVERYDEST 10.1.0.0.0 10.1.0.0.0SQL> alter diskgroup dg1 set attribute 'compatible.asm'='11.1.0';Diskgroup altered.SQL> alter diskgroup dg1 set attribute 'compatible.rdbms'='11.1.0';Diskgroup altered.SQL> alter diskgroup recoverydest set attribute 'compatible.asm'='11.1.0';Diskgroup altered.SQL> alter diskgroup recoverydest set attribute 'compatible.rdbms'='11.1.0';Diskgroup altered.SQL> select name,compatibility,database_compatibility from v$asm_diskgroup;NAME COMPATIBILITY DATABASE_COMPATIBILI--------------- ------------- --------------------DG1 11.1.0.0.0 11.1.0.0.0RECOVERYDEST 11.1.0.0.0 11.1.0.0.0
此部分将简要描述 Oracle 数据库 11g 的几个新特性。新特性的详细描述不在本指南的讨论范围之内。要获得更完整的列表，请参阅 Oracle 数据库新特性指南 11g 第 1 版 (11.1)。
自动内存管理 — 在 Oracle 数据库 11g 中，已经使用动态参数 memory_target 进一步自动化了内存管理。您只需指定总的实例内存大小，数据库将自动管理 SGA 与 PGA 之间的内存分配。视图 v$memory_target_advice 将提供内存调整建议。
间隔分区通过在插入行超出分区范围时自动创建新表分区，提高了分区表的可管理性。
按整数值分区
SQL> create table patients (2 patientid number not null,name varchar2(10),address varchar2(15) 3 )4 partition by range (patientid)5 interval (100)6 (partition p1 values less than (100)) 7 /Table created.SQL> select partition_name,high_value2 from user_tab_partitions3 where table_name='PATIENTS';PARTITION_NAME HIGH_VALUE--------------- ---------------P1 100SQL> insert into patients values (100,'ROBERT','4 BORNE AVE');1 row created.SQL> select partition_name,high_value2 from user_tab_partitions3 where table_name='PATIENTS';PARTITION_NAME HIGH_VALUE--------------- ---------------P1 100SYS_P81 200SQL> select count(*) from patients partition (SYS_P81);COUNT(*)---------- 1
按日期分区
SQL> create table userlogs (2 transid number,3 transdt date,4 terminal varchar2(10) 5 )6 partition by range (transdt)7 interval (numtoyminterval(1,'YEAR')) 8 (9 partition p1 values less than (to_date('01-01-2007','mm-dd-yyyy')) 10 );Table created.SQL> select partition_name,high_value2 from user_tab_partitions3 where table_name='USERLOGS';PARTITION_NAME HIGH_VALUE-------------- --------------------------------------------------------------------------------P1 TO_DATE(' 2007-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGoRIASQL> insert into userlogs values (1,'11-AUG-07','XAV0004');1 row created.SQL> select partition_name,high_value2 from user_tab_partitions3 where table_name='USERLOGS';PARTITION_NAME HIGH_VALUE-------------- --------------------------------------------------------------------------------P1 TO_DATE(' 2007-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIASYS_P42 TO_DATE(' 2008-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIASQL> select count(*) from userlogs partition (sys_p42);COUNT(*)---------- 1引用分区根据父表的分区模式对子表进行分区。 SQL> create table patients (2 patientid number not null,name varchar2(10), address varchar2(15) 3 )4 partition by range (patientid)5 (partition p1 values less than (100),6 partition p2 values less than (200)) 7 /Table created.SQL> alter table patients2 add constraint patients_pk primary key (patientid);Table altered.SQL> create table invoices (2 invoiceno number,amount number, patientid number not null,3 constraint invoices_fk4 foreign key (patientid) references patients 5 )6 partition by reference (invoices_fk);Table created.SQL> select dbms_metadata.get_ddl('TABLE','INVOICES','VCHAN') from dual;DBMS_METADATA.GET_DDL('TABLE','INVOICES','VCHAN')-----------------------------------------------------------------------CREATE TABLE "VCHAN"."INVOICES"( "INVOICENO" NUMBER,"AMOUNT" NUMBER,"PATIENTID" NUMBER NOT NULL ENABLE,CONSTRAINT "INVOICES_FK" FOREIGN KEY ("PATIENTID")REFERENCES "VCHAN"."PATIENTS" ("PATIENTID") ENABLE) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255STORAGE(BUFFER_POOL DEFAULT)PARTITION BY REFERENCE ("INVOICES_FK")(PARTITION "P1"PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)TABLESPACE "USERS" NOCOMPRESS ,PARTITION "P2"PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)TABLESPACE "USERS" NOCOMPRESS )SQL> insert into patients values (1,'TOBY','88 Palace Ave');1 row created.SQL> insert into invoices values (150,262.12,1);1 row created.SQL> select count(*) from invoices partition (p1);COUNT(*)---------- 1SQL> select count(*) from invoices partition (p2);COUNT(*)---------- 0SQL> insert into patients values (110,'GILY','512 HILE STREET');1 row created.SQL> insert into invoices values (151,500.01,110);1 row created.SQL> select count(*) from invoices partition (p1);COUNT(*)---------- 1SQL> select count(*) from invoices partition (p2);COUNT(*)---------- 1Oracle 数据库 11g 中的表压缩支持传统的 DML 和删除列操作。压缩数据在读取期间不需要解压缩，这样，因为读取的数据块较少，查询压缩数据的速度将明显加快。 SQL> create tablespace tbs1 datafile '/u01/app/oracle/oradata/db11g/tbs1_01.dbf' size 500M;Tablespace created.SQL> create tablespace tbs2 datafile '/u01/app/oracle/oradata/db11g/tbs2_01.dbf' size 500M;Tablespace created.SQL> create table mytable_compress (col1 varchar2(26),col2 varchar2(26)) tablespace tbs1 compress for all operations;Table created.SQL> create table mytable_nocompress (col1 varchar2(26),col2 varchar2(26)) tablespace tbs2;Table created.SQL> alter system flush buffer_Cache;System altered.SQL> alter system flush shared_pool;System altered.SQL> set timing onSQL> insert into mytable_nocompress2 select 'ABCDEFGHIJKLMNOPQRSTUVWXYZ','ABCDEFGHIJKLMNOPQRSTUVWXYZ'3 from (select 1 from dual connect by level <= 2000000);2000000 rows created.Elapsed: 00:00:8.07SQL> commit;Commit complete.Elapsed: 00:00:00.07SQL> alter system flush buffer_Cache;System altered.SQL> alter system flush shared_pool;System altered.SQL> insert into mytable_compress2 select 'ABCDEFGHIJKLMNOPQRSTUVWXYZ','ABCDEFGHIJKLMNOPQRSTUVWXYZ'3 from (select 1 from dual connect by level <= 2000000);2000000 rows created.Elapsed: 00:00:41.79SQL> commit;Commit complete.Elapsed: 00:00:00.04SQL> select segment_name,extents from user_segments where segment_name like 'MYTABLE%';SEGMENT_NAME EXTENTS------------------------------ ----------MYTABLE_COMPRESS 53MYTABLE_NOCOMPRESS 88SQL> select tablespace_name,bytes/1024/1024 from dba_free_space where tablespace_name like 'TBS%';TABLESPACE_NAME BYTES/1024/1024------------------------------ ---------------TBS1 461.9375TBS2 363.9375SQL> alter table mytable_compress drop column col2;Table altered.Elapsed: 00:00:21.04
--结束END--
本文标题: 10G RAC升级到11G的方法（官方）
本文链接: https://www.lsjlt.com/news/245731.html(转载时请注明来源链接)
有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341
下载Word文档到电脑，方便收藏和打印～
2024-05-09
2024-05-09
2024-05-09
2024-05-09
2024-05-09
2024-05-09
2024-05-09
2024-05-09
2024-05-09
2024-05-09
回答
回答
回答
回答
回答
回答
回答
回答
回答
回答
官方手机版
微信公众号
商务合作
0