溫馨提示×
您好，登錄后才能下訂單哦！
點(diǎn)擊 登錄注冊(cè) 即表示同意《億速云用戶服務(wù)條款》
您好，登錄后才能下訂單哦！
了解如何在 Oracle Enterprise Linux 5 上逐步將 Oracle RAC 10g 第 2 版升級(jí)到 Oracle RAC 11g。
作者：Vincent Chan
2007 年 10 月發(fā)布
Oracle 最近發(fā)布了大家期待已久的 Oracle 數(shù)據(jù)庫(kù) 11g（即，新一代網(wǎng)格計(jì)算）。該版本在網(wǎng)格計(jì)算方面取得了顯著進(jìn)步，提供了大量令人難以置信的新特性，從而進(jìn)一步改進(jìn)了數(shù)據(jù)庫(kù)可用性和可管理性、性能監(jiān)視、診斷以及數(shù)據(jù)庫(kù)升級(jí)過(guò)程。其中不乏令人印象深刻的創(chuàng)新功能，例如 Oracle 真正應(yīng)用測(cè)試、具有實(shí)時(shí)查詢功能的物理備用、新的分區(qū)方案以及結(jié)果緩存等關(guān)鍵特性。
轉(zhuǎn)自：http://www.oracle.com/technology/global/cn/pub/articles/chan-upgrade.html
基本上，將 Oracle RAC 10g 升級(jí)到 Oracle RAC 11g 的方法有三種：
推薦的方法是，使用 DBUA 升級(jí)您的數(shù)據(jù)庫(kù)和自動(dòng)存儲(chǔ)管理 (ASM)。DBUA 可以執(zhí)行許多人工任務(wù)，從而顯著簡(jiǎn)化升級(jí)過(guò)程。在本指南中，我們將描述使用 DBUA 進(jìn)行升級(jí)的過(guò)程。
本指南相關(guān)下載：
本指南分為以下步驟：
Oracle RAC 10g 環(huán)境概述：
| 主機(jī)名 | 實(shí)例名 | 數(shù)據(jù)庫(kù)名 | 集群件文件存儲(chǔ) | 數(shù)據(jù)庫(kù)文件存儲(chǔ) |
| merlin1 | devdb1 | devdb | Raw | ASM |
| merlin2 | devdb2 | devdb | Raw | ASM |
| 主機(jī)名 | 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 集群件文件 |
設(shè)備名 |
| Oracle Cluster Registry | /dev/sdb1 |
| Voting Disk | /dev/sdc1 |
Oracle RAC 11g 環(huán)境概述：
| 主機(jī)名 | 實(shí)例名 | 數(shù)據(jù)庫(kù)名 | 集群件文件存儲(chǔ) | 數(shù)據(jù)庫(kù)文件存儲(chǔ) |
| merlin1 | devdb1 | devdb | Raw | ASM |
| merlin2 | devdb2 | devdb | Raw | ASM |
| 主機(jī)名 | 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 集群件文件 | 設(shè)備名 |
| Oracle Cluster Registry | /dev/sdb1 |
| Voting Disk | /dev/sdc1 |
您將在每個(gè)節(jié)點(diǎn)的 Oracle 集群件主目錄、Oracle ASM 主目錄和 Oracle 主目錄中安裝軟件，以實(shí)現(xiàn)冗余和更高的可用性。
在升級(jí)到 Oracle RAC 11g 之前，備份您的 Oracle RAC 10g 環(huán)境。
以 root 用戶身份安裝以下程序包（如果它們尚未安裝到 RAC 節(jié)點(diǎn)上）。這些程序包可以從 Enterprise-R5-GA-Server-i386-disc2.iso 和 Enterprise-R5-GA-Server-i386-disc3.iso 中提取。
提取這些程序包之后，以 root 用戶身份執(zhí)行以下命令。
# ls -1 elfutils-libelf-devel-0.125-3.e15.i386.rpm libaio-devel-0.3.106-3.2.i386.rpm unixODBC-2.2.11-7.1.i386.rpm unixODBC-devel-2.2.11-7.1.i386.rpm # # rpm -Uvh *.rpm
最低內(nèi)核參數(shù)要求如下所示。如果必要，在兩個(gè)節(jié)點(diǎn)的 /etc/sysctl.conf 中配置相應(yīng)的參數(shù)。
kernel.shmall = 2097152 kernel.shmmax = 2147483648 kernel.shmmni = 4096 kernel.sem = 250 32000 100 128 fs.file-max = 65536 net.ipv4.ip_local_port_range = 1024 65000 net.core.rmem_default = 4194304 net.core.rmem_max = 4194304 net.core.wmem_default = 262144 net.core.wmem_max = 262144
如果您希望執(zhí)行滾動(dòng)升級(jí)，在升級(jí)到 Oracle RAC 11g 之前，Oracle 集群件的版本必須至少是 10.2.0.3 或帶有 CRS 2 號(hào)補(bǔ)丁包的 10.2.0.2（參見錯(cuò)誤 5256865）。10.2.0.3 補(bǔ)丁集 (5337014) 可以從 Oracle Metalink 下載。
參見 Oracle Metalink 說(shuō)明 419058.1 或針對(duì) Linux x86 的 Oracle 10.2.0.3 補(bǔ)丁集的相關(guān)信息。merlin1-> crsctl query crs activeversion CRS active version on the cluster is [10.2.0.3.0]
集群驗(yàn)證實(shí)用程序 (CVU) 可降低安裝 RAC 的復(fù)雜性并減少所花費(fèi)的時(shí)間。該工具將掃描集群環(huán)境中所有必需的組件，以確保滿足成功安裝的所有條件。
從 OTN 下載 Oracle 集群件 11.1.0.6 軟件并解壓縮到臨時(shí)目錄，然后執(zhí)行 runcluvfy.sh。
/stage/clusterware/runcluvfy.sh stage -pre crsinst -n all -verbose > /tmp/prechecks.log
驗(yàn)證是否滿足所有前提條件。您可以忽略針對(duì) openmotif-2.2.3-3.RHEL3 的“Package existence checked failed”消息。
merlin1-> srvctl stop database -d devdb merlin1-> srvctl stop asm -n merlin1 merlin1-> srvctl stop asm -n merlin2 merlin1-> srvctl stop nodeapps -n merlin1 merlin1-> srvctl stop nodeapps -n merlin2 merlin1-> crs_stat -t Name Type Target State Host ------------------------------------------------------------ ora.devdb.db application OFFLINE OFFLINE ora....b1.inst application OFFLINE OFFLINE ora....b2.inst application OFFLINE OFFLINE ora....SM1.asm application OFFLINE OFFLINE ora....N1.lsnr application OFFLINE OFFLINE ora....in1.gsd application OFFLINE OFFLINE ora....in1.ons application OFFLINE OFFLINE ora....in1.vip application OFFLINE OFFLINE ora....SM2.asm application OFFLINE OFFLINE ora....N2.lsnr application OFFLINE OFFLINE ora....in2.gsd application OFFLINE OFFLINE ora....in2.ons application OFFLINE OFFLINE ora....in2.vip application OFFLINE OFFLINE
在每個(gè)節(jié)點(diǎn)上執(zhí)行 preupdate.sh 腳本，以準(zhǔn)備要升級(jí)的集群件主目錄。該腳本可停止 Oracle 集群件組件并更改 Oracle 集群件主目錄中文件的權(quán)限。
以 root 用戶身份在每個(gè)節(jié)點(diǎn)上執(zhí)行，# cd /stage/clusterware/upgrade # ./preupdate.sh -crshome /u02/crs/oracle -crsuser oracle Shutting 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.
您只需在一個(gè)節(jié)點(diǎn)上運(yùn)行 Oracle Universal Installer (OUI)。OUI 將在每個(gè)節(jié)點(diǎn)上自動(dòng)安裝現(xiàn)有的二進(jìn)制軟件。
以 oracle 用戶身份在 merlin1 上執(zhí)行，
merlin1-> /stage/clusterware/runInstaller
在 merlin1 上，
# /u02/crs/oracle/install/rootupgrade Checking 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.config WARNING:directory '/u02/crs' is not owned by root WARNING:directory '/u02' is not owned by root Oracle Cluster Registry configuration upgraded successfully Adding daemons to inittab Attempting to start Oracle Clusterware stack Waiting for Cluster Synchronization Services daemon to start Cluster Synchronization Services daemon has started Waiting for Event Manager daemon to start Event Manager daemon has started Cluster Ready Services daemon has started Oracle 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 merlin1 Creating OCR keys for user 'root', privgrp 'root'.. Operation successful. clscfg -upgrade completed successfully CRS stack on this node, is successfully upgraded to 11.1.0.6.0 Checking the existence of nodeapps on this node Creating '/u02/crs/oracle/install/paramfile.crs' with data used for CRS configuration Setting CRS configuration values in /u02/crs/oracle/install/paramfile.crs在 merlin2 上，
# /u02/crs/oracle/install/rootupgrade Checking 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.config WARNING:directory '/u02/crs' is not owned by root WARNING:directory '/u02' is not owned by root Oracle Cluster Registry configuration upgraded successfully Adding daemons to inittab Attempting to start Oracle Clusterware stack Waiting for Cluster Synchronization Services daemon to start Cluster Synchronization Services daemon has started Waiting for Event Manager daemon to start Waiting for Event Manager daemon to start Event Manager daemon has started Cluster Ready Services daemon has started Oracle 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 merlin2 Creating OCR keys for user 'root', privgrp 'root'.. Operation successful. clscfg -upgrade completed successfully CRS stack on this node, is successfully upgraded to 11.1.0.6.0 Checking the existence of nodeapps on this node Creating '/u02/crs/oracle/install/paramfile.crs' with data used for CRS configuration Setting CRS configuration values in /u02/crs/oracle/install/paramfile.crs merlin1-> $ORA_CRS_HOME/bin/crsctl check crs Cluster Synchronization Services appears healthy Cluster Ready Services appears healthy Event Manager appears healthy merlin1-> $ORA_CRS_HOME/bin/crsctl query crs softwareversion Oracle Clusterware version on node [merlin1] is [11.1.0.6.0] merlin1-> $ORA_CRS_HOME/bin/crsctl query crs activeversion Oracle Clusterware active version on the cluster is [11.1.0.6.0] merlin2-> $ORA_CRS_HOME/bin/crsctl check crs Cluster Synchronization Services appears healthy Cluster Ready Services appears healthy Event Manager appears healthy merlin2-> $ORA_CRS_HOME/bin/crsctl query crs softwareversion Oracle Clusterware version on node [merlin2] is [11.1.0.6.0] merlin2-> $ORA_CRS_HOME/bin/crsctl query crs activeversion Oracle Clusterware active version on the cluster is [11.1.0.6.0]
merlin1-> /u02/crs/oracle/bin/cluvfy stage -post crsinst -n merlin1,merlin2 Performing post-checks for cluster services setup Checking 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 passed Checking 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 集群件已經(jīng)升級(jí)到 Oracle 集群件 11g，并且所有集群資源應(yīng)該處于運(yùn)行狀態(tài)。merlin1-> $ORA_CRS_HOME/bin/crs_stat -t Name Type Target State Host ------------------------------------------------------------ ora.devdb.db application ONLINE ONLINE merlin1 ora....b1.inst application ONLINE ONLINE merlin1 ora....b2.inst application ONLINE ONLINE merlin2 ora....SM1.asm application ONLINE ONLINE merlin1 ora....N1.lsnr application ONLINE ONLINE merlin1 ora....in1.gsd application ONLINE ONLINE merlin1 ora....in1.ons application ONLINE ONLINE merlin1 ora....in1.vip application ONLINE ONLINE merlin1 ora....SM2.asm application ONLINE ONLINE merlin2 ora....N2.lsnr application ONLINE ONLINE merlin2 ora....in2.gsd application ONLINE ONLINE merlin2 ora....in2.ons application ONLINE ONLINE merlin2 ora....in2.vip application ONLINE ONLINE merlin2
以 oracle 用戶身份在這兩個(gè)節(jié)點(diǎn)上創(chuàng)建新的 Oracle 主目錄。
mkdir -p /u01/app/oracle/product/11.1.0/db_1
從 OTN 上下載 Oracle 數(shù)據(jù)庫(kù)軟件。
以 oracle 用戶身份在 merlin1 上執(zhí)行，
merlin1-> /stage/database/runInstaller
在運(yùn)行數(shù)據(jù)庫(kù)升級(jí)助手 (DBUA) 之前，執(zhí)行數(shù)據(jù)庫(kù)升級(jí)前檢查（即 utlu111i.sql）以檢驗(yàn)是否滿足所有前提條件。作為升級(jí)過(guò)程的一部分，DBUA 會(huì)自動(dòng)將 cluster_database 參數(shù)由 true 更改為 false。進(jìn)行必要的修改后，重新執(zhí)行數(shù)據(jù)庫(kù)升級(jí)前檢查腳本。
以 sys 用戶身份連接，
SQL> spool /tmp/utlu111i.log SQL> @/u01/app/oracle/product/11.1.0/db_1/rdbms/admin/utlu111i Oracle 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 以便在兩個(gè)節(jié)點(diǎn)上反映新的 Oracle 數(shù)據(jù)庫(kù) 11g 目錄。
merlin1-> more .profile export PS1="`/bin/hostname -s`-> " export EDITOR=vi export ORACLE_SID=devdb1 export ORACLE_BASE=/u01/app/oracle export ORACLE_HOME=$ORACLE_BASE/product/11.1.0/db_1 export ORA_CRS_HOME=/u02/crs/oracle export LD_LIBRARY_PATH=$ORACLE_HOME/lib export PATH=$ORACLE_HOME/bin:/bin:/usr/bin:/usr/sbin:/usr/loca l/bin:/usr/X11R6/bin umask 022
以 oracle 用戶身份在 merlin1 上執(zhí)行 dbua。
merlin1-> ../.profile merlin1-> 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 Catalog 11.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 devdb merlin1 devdb1 /u01/app/oracle/product/11.1.0/db_1 merlin2 devdb2 /u01/app/oracle/product/11.1.0/db_1 merlin1-> $ORA_CRS_HOME/bin/crs_stat -t Name Type Target State Host ------------------------------------------------------------ ora.devdb.db application ONLINE ONLINE merlin1 ora....b1.inst application ONLINE ONLINE merlin1 ora....b2.inst application ONLINE ONLINE merlin2 ora....SM1.asm application ONLINE ONLINE merlin1 ora....N1.lsnr application ONLINE ONLINE merlin1 ora....in1.gsd application ONLINE ONLINE merlin1 ora....in1.ons application ONLINE ONLINE merlin1 ora....in1.vip application ONLINE ONLINE merlin1 ora....SM2.asm application ONLINE ONLINE merlin2 ora....N2.lsnr application ONLINE ONLINE merlin2 ora....in2.gsd application ONLINE ONLINE merlin2 ora....in2.ons application ONLINE ONLINE merlin2 ora....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/trace user_dump_dest /u01/app/oracle/diag/rdbms/devdb/devdb1/trace core_dump_dest /u01/app/oracle/diag/rdbms/devdb/devdb1/cdump diagnostic_dest /u01/app/oracle
單獨(dú)的 ASM 主目錄不是必需的；但是，具有一個(gè)單獨(dú)的 ASM 主目錄具有以下優(yōu)點(diǎn)：可讓您將補(bǔ)丁或補(bǔ)丁集獨(dú)立地從 ASM 主目錄應(yīng)用到 Oracle RDBMS 主目錄。在同一個(gè)節(jié)點(diǎn)上運(yùn)行一個(gè)以上的數(shù)據(jù)庫(kù)實(shí)例時(shí)，具有單獨(dú)的 ASM 主目錄和 RDBMS 主目錄尤其受益。在 Oracle RDBMS 主目錄需要修補(bǔ)時(shí)，ASM 實(shí)例可用性將不會(huì)受到影響。
此時(shí)，您的 ASM 主目錄仍然在 Oracle 數(shù)據(jù)庫(kù) 10g 主目錄下運(yùn)行。
merlin1-> srvctl config asm -n merlin1 +ASM1 /u01/app/oracle/product/10.2.0/db_1 merlin1-> srvctl config asm -n merlin2 +ASM2 /u01/app/oracle/product/10.2.0/db_1
在兩個(gè)節(jié)點(diǎn)上，以 oracle 用戶身份創(chuàng)建新的 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 上執(zhí)行，
merlin1-> ../.profile merlin1-> /stage/database/runInstaller
在 merlin1 上，以 oracle 用戶身份停止數(shù)據(jù)庫(kù)并啟動(dòng) DBUA。
merlin1-> srvctl stop database -d devdb merlin1-> /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/asm merlin1-> srvctl config asm -n merlin2 +ASM2 /u01/app/oracle/product/11.1.0/asm merlin1-> srvctl start database -d devdb merlin1-> crs_stat -t Name Type Target State Host ------------------------------------------------------------ ora.devdb.db application ONLINE ONLINE merlin1 ora....b1.inst application ONLINE ONLINE merlin1 ora....b2.inst application ONLINE ONLINE merlin2 ora....SM1.asm application ONLINE ONLINE merlin1 ora....N1.lsnr application ONLINE ONLINE merlin1 ora....in1.gsd application ONLINE ONLINE merlin1 ora....in1.ons application ONLINE ONLINE merlin1 ora....in1.vip application ONLINE ONLINE merlin1 ora....SM2.asm application ONLINE ONLINE merlin2 ora....N2.lsnr application ONLINE ONLINE merlin2 ora....in2.gsd application ONLINE ONLINE merlin2 ora....in2.ons application ONLINE ONLINE merlin2 ora....in2.vip application ONLINE ONLINE merlin2
圖 8
作為最后一步，要利用 Oracle 數(shù)據(jù)庫(kù) 11g 的新特性，必須將數(shù)據(jù)庫(kù)兼容性參數(shù)和磁盤組兼容性屬性更改為 11.1.0。
在 devdb1 實(shí)例上，
SQL> show parameter compatible NAME TYPE VALUE ------------------------ ----------- ------------------------- compatible string 10.2.0.1.0 SQL> alter system set compatible='11.1.0' scope=spfile; System altered.在 merlin1 上，重新啟動(dòng)數(shù)據(jù)庫(kù)，
merlin1-> srvctl stop database -d devdb merlin1-> srvctl start database -d devdb
在 ASM1 實(shí)例上，
SQL> select name,compatibility,database_compatibility from v$asm_diskgroup; NAME COMPATIBILITY DATABASE_COMPATIBILI --------------- ------------- -------------------- DG1 10.1.0.0.0 10.1.0.0.0 RECOVERYDEST 10.1.0.0.0 10.1.0.0.0 SQL> 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.0 RECOVERYDEST 11.1.0.0.0 11.1.0.0.0
此部分將簡(jiǎn)要描述 Oracle 數(shù)據(jù)庫(kù) 11g 的幾個(gè)新特性。新特性的詳細(xì)描述不在本指南的討論范圍之內(nèi)。要獲得更完整的列表，請(qǐng)參閱 Oracle 數(shù)據(jù)庫(kù)新特性指南 11g 第 1 版 (11.1)。
自動(dòng)內(nèi)存管理 — 在 Oracle 數(shù)據(jù)庫(kù) 11g 中，已經(jīng)使用動(dòng)態(tài)參數(shù) memory_target 進(jìn)一步自動(dòng)化了內(nèi)存管理。您只需指定總的實(shí)例內(nèi)存大小，數(shù)據(jù)庫(kù)將自動(dòng)管理 SGA 與 PGA 之間的內(nèi)存分配。視圖 v$memory_target_advice 將提供內(nèi)存調(diào)整建議。
間隔分區(qū)通過(guò)在插入行超出分區(qū)范圍時(shí)自動(dòng)創(chuàng)建新表分區(qū)，提高了分區(qū)表的可管理性。
按整數(shù)值分區(qū)
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_value
2 from user_tab_partitions
3 where table_name='PATIENTS';
PARTITION_NAME HIGH_VALUE
--------------- ---------------
P1 100
SQL> insert into patients values (100,'ROBERT','4 BORNE AVE');
1 row created.
SQL> select partition_name,high_value
2 from user_tab_partitions
3 where table_name='PATIENTS';
PARTITION_NAME HIGH_VALUE
--------------- ---------------
P1 100
SYS_P81 200
SQL> select count(*) from patients partition (SYS_P81);
COUNT(*)
----------
1
按日期分區(qū)
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_value
2 from user_tab_partitions
3 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=GREGORIA
SQL> insert into userlogs values (1,'11-AUG-07','XAV0004');
1 row created.
SQL> select partition_name,high_value
2 from user_tab_partitions
3 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=GREGORIA
SYS_P42 TO_DATE(' 2008-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
SQL> select count(*) from userlogs partition (sys_p42);
COUNT(*)
----------
1
引用分區(qū)根據(jù)父表的分區(qū)模式對(duì)子表進(jìn)行分區(qū)。
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 patients
2 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_fk
4 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 255
STORAGE(
BUFFER_POOL DEFAULT)
PARTITION BY REFERENCE ("INVOICES_FK")
(PARTITION "P1"
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS" NOCOMPRESS ,
PARTITION "P2"
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 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(*)
----------
1
SQL> select count(*) from invoices partition (p2);
COUNT(*)
----------
0
SQL> 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(*)
----------
1
SQL> select count(*) from invoices partition (p2);
COUNT(*)
----------
1
Oracle 數(shù)據(jù)庫(kù) 11g 中的表壓縮支持傳統(tǒng)的 DML 和刪除列操作。壓縮數(shù)據(jù)在讀取期間不需要解壓縮，這樣，因?yàn)樽x取的數(shù)據(jù)塊較少，查詢壓縮數(shù)據(jù)的速度將明顯加快。 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 on SQL> insert into mytable_nocompress 2 select 'ABCDEFGHIJKLMNOPQRSTUVWXYZ','ABCDEFGHIJKLMNOPQRSTUVWXYZ' 3 from (select 1 from dual connect by level <= 2000000); 2000000 rows created. Elapsed: 00:00:8.07 SQL> commit; Commit complete. Elapsed: 00:00:00.07 SQL> alter system flush buffer_Cache; System altered. SQL> alter system flush shared_pool; System altered. SQL> insert into mytable_compress 2 select 'ABCDEFGHIJKLMNOPQRSTUVWXYZ','ABCDEFGHIJKLMNOPQRSTUVWXYZ' 3 from (select 1 from dual connect by level <= 2000000); 2000000 rows created. Elapsed: 00:00:41.79 SQL> commit; Commit complete. Elapsed: 00:00:00.04 SQL> select segment_name,extents from user_segments where segment_name like 'MYTABLE%'; SEGMENT_NAME EXTENTS ------------------------------ ---------- MYTABLE_COMPRESS 53 MYTABLE_NOCOMPRESS 88 SQL> select tablespace_name,bytes/1024/1024 from dba_free_space where tablespace_name like 'TBS%'; TABLESPACE_NAME BYTES/1024/1024 ------------------------------ --------------- TBS1 461.9375 TBS2 363.9375 SQL> alter table mytable_compress drop column col2; Table altered. Elapsed: 00:00:21.04
免責(zé)聲明：本站發(fā)布的內(nèi)容（圖片、視頻和文字）以原創(chuàng)、轉(zhuǎn)載和分享為主，文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如果涉及侵權(quán)請(qǐng)聯(lián)系站長(zhǎng)郵箱：is@yisu.com進(jìn)行舉報(bào)，并提供相關(guān)證據(jù)，一經(jīng)查實(shí)，將立刻刪除涉嫌侵權(quán)內(nèi)容。
億速云公眾號(hào)
手機(jī)網(wǎng)站二維碼