/**PageBeginHtml Block Begin **/ /***自定义返回顶部小火箭***/ /*生成博客目录的JS 开始*/ /*生成博客目录的JS 结束*/

OCM_第二天课程:Section1 —》配置 Oracle 网络环境


注:本文为原著(其内容来自 腾科教育培训课堂)。阅读本文注意事项如下:

1:所有文章的转载请标注本文出处。

2:本文非本人不得用于商业用途。违者将承当相应法律责任。

3:该系列文章目录列表:

  一:《OCM 基本班课程表

       二:《OCM_第一天课程:OCM课程环境搭建

       三:《OCM_第二天课程:Section1 —》配置 Oracle 网络环境 

       四:《OCM_第三天课程:Section1 —》表空间的操作和管理、服务配置 

       五:《OCM_第四天课程:Section2 —》GC 的安装和配置

       六: 《OCM_第五天课程:Section2 —》AGENT 的安装 、GC 的使用

      七: 《OCM_第六天课程:Section3 —》数据库可用性

      八:《OCM_第七天课程:Section3 —》数据库可用性

      九:《OCM_第八天课程:Section4 —》数据管理  》

      十: 《OCM_第九天课程:Section4—》OCM课程环境搭建

      十一: 《OCM_第十天课程:Section5—》数据仓库

      十二: 《OCM_第十一天课程:Section5 —》数据仓库

      十三:  《OCM_第十二天课程:Section6 —》数据库性能调优_ 资源管理器/执行计划

      十四:  《OCM_第十三天课程:Section6 —》数据库性能调优 _结果缓存 /多列数据信息采集统计/采集数据信息保持游标有效

       十五:  《OCM_第十四天课程:Section6 —》数据库性能调优_各类索引 /调优工具使用/SQL 优化建议

      十六:   《OCM_第十五天课程:Section6 —》数据库性能调优 _SQL 访问建议 /SQL 性能分析器/配置基线模板/SQL  执行计划管理/实例限制

      十七:   《OCM_第十六天课程:Section7 —》GI 及 ASM 安装配置 _安装 GRID 软件/创建和管理 ASM  磁盘组/创建和管理 ASM 实例

      十八:  《OCM_第十七天课程:Section7 —》GI 及 ASM 安装配置 _管理和配置 GRID /实施 ASM 故障组 /创建 ACFS  文件系统

      十九:   《OCM_第十八天课程:Section8 —》RAC 数据库 _ RAC DB 搭建/RAC DB 配置使用

     二十:   《OCM_第十九天课程:Section9 —》Data Guard _ DATA GUARD 原理/DATA GUARD  应用/DATA GUARD 搭建

     二十一:   《OCM_第二十天课程:Section9 —》Data Guard _ DATA GUARD 搭建/DATA GUARD 管理

     二十二:  《OCM_第二十一天课程:考前辅导 》

      二十三:  《OCM_第  二十二天课程:考前辅导 》

     二十四:   《OCM_第二十三天课程:模拟考试》

     二十五:   《OCM_第二十四天课程:模拟考试》



一:配置 Oracle 网络环境

题目:

  1 After completing this lesson, you should be able to do the following:
  2 Describe the Oracle Network configuration
  3 Use the Enterprise Manager Net Services Administration page for Oracle Network configuration
  4 Use the Listener Control utility
  5 Configure a client to access an Oracle database
  6 
  7 在完成这一课之后,你应该能够做到以下几点:
  8 描述Oracle网络配置
  9 使用企业管理器Net服务管理页面来进行Oracle网络配置
 10 使用侦听器控制工具
 11 配置客户端以访问Oracle数据库

1:Oracle 网络配置 

•Oracle Network configuration  (oracle 数据库 网络配置)  

image



• Tools for Configuring and Managing the Oracle Network   (用于配置和管理Oracle网络的工具)

  • Oracle Enterprise Manager Net Services Administration page  (Oracle企业管理服务管理页面)
  • Oracle Net Manager (Oracle网络管理器)
  • Oracle Net Configuration Assistant (launched by Oracle Universal Installer)  (Oracle Net配置助理(由Oracle Universal安装程序启动))


Using the Enterprise Manager Net Services Administration page for Oracle Network configuration(使用企业管理器Net服务管理页面来进行Oracle网络配置)

Enterprise Manager Net Services Administration Page(企业经理Net服务管理页面)

  1. You can use the Enterprise Manager Net Services Administration page to perform the following tasks:(您可以使用企业管理器Net服务管理页面执行以下任务:)
  •                   Configuring local naming and directory naming(配置本地命名和目录命名)
  •                   Searching directory naming entries  (搜索目录命名条目)
  •                   Configuring and administering the listener  (配置和管理侦听器)
  •                   Sorting listener names, local naming entries, and directory naming entries (分类侦听器名称、本地命名条目和目录命名条目)
  •                   Exporting directory naming entries to the tnsnames.ora file (将目录命名项导出到tnsname。ora文件)


  1. Accessing the Net Services Administration Page  (访问Net服务管理页面)

                      image

  1. Oracle Net Listener: Configuration and Management(Oracle Net侦听器:配置和管理)

                   image


  1. Managing the Oracle Net Listener with Enterprise Manager(使用企业管理器管理Oracle Net侦听器)

                   image

•Using the Listener Control utility(使用侦听器控制工具)

  1 -- Use the following commands to control the listener(使用以下命令来控制侦听器):
  2 --Starting the listener(启动监听器)
  3 [oracle@oracle ~]$ lsnrctl START listener_name
  4 --Stopping the listener(停止监听器)
  5 [oracle@oracle ~]$  $lsnrctl STOP listener_name

----示例;

  1  #进去监听命令中
  2   [oracle@oracle ~]$ lsnrctl
  3 
  4   LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 17-OCT-2017 23:41:44
  5 
  6   Copyright (c) 1991, 2011, Oracle.  All rights reserved.
  7 
  8   Welcome to LSNRCTL, type "help" for information.
  9   #查看监听的辅助命令
 10   LSNRCTL> help
 11   The following operations are available
 12   An asterisk (*) denotes a modifier or extended command:
 13   #启动              停止                状态
 14   start               stop                status
 15   #服务               版本                 重启
 16   services            version             reload
 17   #保存配置           追踪                生成
 18   save_config         trace               spawn
 19   # 修改密码          退出                退出
 20   change_password     quit                exit
 21 
 22   set*                show*
 23 
 24   #查看状态
 25   LSNRCTL> status
 26   Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oracle.example.com)(PORT=1521)))
 27   STATUS of the LISTENER
 28   ------------------------
 29   #监听名称
 30   Alias                     LISTENER
 31   #数据库版本
 32   Version                   TNSLSNR for Linux: Version 11.2.0.3.0 - Production
 33   #启动监听时间
 34   Start Date                17-OCT-2017 23:36:47
 35   #
 36   Uptime                    0 days 0 hr. 5 min. 5 sec
 37   #追踪级别
 38   Trace Level               off
 39   Security                  ON: Local OS Authentication
 40   SNMP                      OFF
 41   #配置文件位置信息
 42   Listener Parameter File   /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
 43   #监听日志信息
 44   Listener Log File         /u01/app/oracle/diag/tnslsnr/oracle/listener/alert/log.xml
 45   #基于 tcp协议,ip 地址为:oracle.example.com
 46   Listening Endpoints Summary...
 47     (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oracle.example.com)(PORT=1521)))
 48     (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
 49   Services Summary...
 50   Service "prod2" has 1 instance(s).
 51     Instance "prod2", status READY, has 1 handler(s) for this service...
 52   The command completed successfully
 53   # 停止监听
 54   LSNRCTL> stop
 55   Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oracle.example.com)(PORT=1521)))
 56   The command completed successfully
 57   #退出 监听命令编辑器
 58   LSNRCTL> quit
 59   #启动监听
 60   [oracle@oracle ~]$ lsnrctl start
 61 
 62   LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 17-OCT-2017 23:42:16
 63 
 64   Copyright (c) 1991, 2011, Oracle.  All rights reserved.
 65 
 66   Starting /u01/app/oracle/product/11.2.0/db_1/bin/tnslsnr: please wait...
 67 
 68   TNSLSNR for Linux: Version 11.2.0.3.0 - Production
 69   System parameter file is /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
 70   Log messages written to /u01/app/oracle/diag/tnslsnr/oracle/listener/alert/log.xml
 71   Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oracle.example.com)(PORT=1521)))
 72   Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
 73 
 74   Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oracle.example.com)(PORT=1521)))
 75   STATUS of the LISTENER
 76   ------------------------
 77   Alias                     LISTENER
 78   Version                   TNSLSNR for Linux: Version 11.2.0.3.0 - Production
 79   Start Date                17-OCT-2017 23:42:16
 80   Uptime                    0 days 0 hr. 0 min. 20 sec
 81   Trace Level               off
 82   Security                  ON: Local OS Authentication
 83   SNMP                      OFF
 84   Listener Parameter File   /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
 85   Listener Log File         /u01/app/oracle/diag/tnslsnr/oracle/listener/alert/log.xml
 86   Listening Endpoints Summary...
 87     (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oracle.example.com)(PORT=1521)))
 88     (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
 89   The listener supports no services
 90   The command completed successfully
 91   #停止监听
 92   [oracle@oracle ~]$ lsnrctl stop
 93 
 94   LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 17-OCT-2017 23:42:54
 95 
 96   Copyright (c) 1991, 2011, Oracle.  All rights reserved.
 97 
 98   Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oracle.example.com)(PORT=1521)))
 99   The command completed successfully
100   #查看状态
101   [oracle@oracle ~]$ lsnrctl status
102 
103   LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 17-OCT-2017 23:43:03
104 
105   Copyright (c) 1991, 2011, Oracle.  All rights reserved.
106 
107   Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oracle.example.com)(PORT=1521)))
108   TNS-12541: TNS:no listener
109    TNS-12560: TNS:protocol adapter error
110     TNS-00511: No listener
111      Linux Error: 111: Connection refused
112   Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
113   TNS-12541: TNS:no listener
114    TNS-12560: TNS:protocol adapter error
115     TNS-00511: No listener
116      Linux Error: 2: No such file or directory
117   [oracle@oracle ~]$ lsnrctl start
118 
119   LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 17-OCT-2017 23:43:11
120 
121   Copyright (c) 1991, 2011, Oracle.  All rights reserved.
122 
123   Starting /u01/app/oracle/product/11.2.0/db_1/bin/tnslsnr: please wait...
124 
125   TNSLSNR for Linux: Version 11.2.0.3.0 - Production
126   System parameter file is /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
127   Log messages written to /u01/app/oracle/diag/tnslsnr/oracle/listener/alert/log.xml
128   Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oracle.example.com)(PORT=1521)))
129   Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
130 
131   Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oracle.example.com)(PORT=1521)))
132   STATUS of the LISTENER
133   ------------------------
134   Alias                     LISTENER
135   Version                   TNSLSNR for Linux: Version 11.2.0.3.0 - Production
136   Start Date                17-OCT-2017 23:43:11
137   Uptime                    0 days 0 hr. 0 min. 20 sec
138   Trace Level               off
139   Security                  ON: Local OS Authentication
140   SNMP                      OFF
141   Listener Parameter File   /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
142   Listener Log File         /u01/app/oracle/diag/tnslsnr/oracle/listener/alert/log.xml
143   Listening Endpoints Summary...
144     (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oracle.example.com)(PORT=1521)))
145     (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
146   The listener supports no services
147   The command completed successfully
148   [oracle@oracle ~]$ lsnctl status
149   -bash: lsnctl: command not found
150   [oracle@oracle ~]$ lsnrctl status
151 
152   LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 17-OCT-2017 23:43:47
153 
154   Copyright (c) 1991, 2011, Oracle.  All rights reserved.
155 
156   Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oracle.example.com)(PORT=1521)))
157   STATUS of the LISTENER
158   ------------------------
159   Alias                     LISTENER
160   Version                   TNSLSNR for Linux: Version 11.2.0.3.0 - Production
161   Start Date                17-OCT-2017 23:43:11
162   Uptime                    0 days 0 hr. 0 min. 36 sec
163   Trace Level               off
164   Security                  ON: Local OS Authentication
165   SNMP                      OFF
166   Listener Parameter File   /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
167   Listener Log File         /u01/app/oracle/diag/tnslsnr/oracle/listener/alert/log.xml
168   Listening Endpoints Summary...
169     (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oracle.example.com)(PORT=1521)))
170     (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
171   The listener supports no services
172   The command completed successfully
173   [oracle@oracle ~]$


•Configuring a client to access an Oracle database

                      image






Oracle Net provides support for the following methods: (Oracle Net提供了以下方法的支持:)

  • Local naming: Uses a local configuration file (本地命名:使用本地配置文件)
  • Directory naming: Uses a centralized LDAP-compliant directory server(目录命名:使用一个集中式的与ldap兼容的目录服务器)
  • Easy connect naming: Uses a TCP/IP connect string (简单连接命名:使用一个/ip连接字符串)
  • External naming: Uses a supported non-Oracle naming service (外部命名:使用受支持的非oracle命名服务)









2:创建和管理监听器 

The server segment USES "netca" to configure orcle network monitoring(服务器段使用 “netca” 配置 orcle 网络监听)教程

1:在oracle 服务器端机器中;打开open terminal 后切换到oracle 用户 ;然后收入 “netcat”命令;如图

   image.

    image

点击next 后选择新增

      image

       image

在上图输入 监听文字的信息后,点击next ,选择tcp 协议。然后再点击next

       image

     填入监听端口;默认为1521;填写完毕后,点击next

       image

就这样一路默认下来基本就可以创建完成了

查看配置好的listen文件信息

  1 [oracle@oracle admin]$ pwd
  2 /u01/app/oracle/product/11.2.0/db_1/network/admin
  3 [oracle@oracle admin]$ ls
  4 listener17101810PM4639.bak  listener.ora  samples  shrept.lst
  5 [oracle@oracle admin]$ cat listener.ora
  6 # listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
  7 # Generated by Oracle configuration tools.
  8 
  9 LISTENER1 =
 10   (DESCRIPTION_LIST =
 11     (DESCRIPTION =
 12       (ADDRESS = (PROTOCOL = TCP)(HOST = oracle.example.com)(PORT = 1522))
 13     )
 14   )
 15 
 16 ADR_BASE_LISTENER1 = /u01/app/oracle
 17 
 18 LISTENER =
 19   (DESCRIPTION_LIST =
 20     (DESCRIPTION =
 21       (ADDRESS = (PROTOCOL = TCP)(HOST = oracle.example.com)(PORT = 1521))
 22       (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
 23     )
 24   )
 25 
 26 ADR_BASE_LISTENER = /u01/app/oracle
 27 
 28 [oracle@oracle admin]$
 29 



The server segment USES "netmgr" to configure orcle network monitoring(服务器段使用 “netmgr” 配置 orcle 网络监听)教程

1: 使用 netmgr 启动 管理器

           image

2:查看已经设置好的监听信息

        image

         image

         给该监听增加新的监听方式信息:

           image

增加新的监听内容:

           image

           image

   接下来就是增加一些监听具体的监听方式:

         image


最后关闭时,保存好设置好的设置信息:

以下是查看设置好的监听配置信息:

  1 [oracle@oracle admin]$ pwd
  2 /u01/app/oracle/product/11.2.0/db_1/network/admin
  3 [oracle@oracle admin]$ ls
  4 listener.ora  samples  shrept.lst
  5 [oracle@oracle admin]$ cat listener.ora
  6 # listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
  7 # Generated by Oracle configuration tools.
  8 
  9 LISTENER1 =
 10   (DESCRIPTION =
 11     (ADDRESS = (PROTOCOL = TCP)(HOST = oracle.example.com)(PORT = 1522))
 12   )
 13 
 14 ADR_BASE_LISTENER1 = /u01/app/oracle
 15 
 16 LISTENER =
 17   (DESCRIPTION_LIST =
 18     (DESCRIPTION =
 19       (ADDRESS = (PROTOCOL = TCP)(HOST = oracle.example.com)(PORT = 1521))
 20     )
 21     (DESCRIPTION =
 22       (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
 23     )
 24   )
 25 
 26 ADR_BASE_LISTENER = /u01/app/oracle
 27 
 28 [oracle@oracle admin]$
 29 

使用 “netmgr” 创建 服务名

   1:填写服务名:

imageimage

选择tcp/ip 协议

imageimage

填写主机名和端口号:

imageimage

 imageimage

imageimage

imageimage


imageimage

imageimage

此测试告诉我的意思是:网络是通的,只是登录密码不对。可以关闭测试界面了

imageimage

imageimage

查看服务的配置信息

  1 [oracle@oracle ~]$ cd  /u01/app/oracle/product/11.2.0/db_1/network/admin/
  2 [oracle@oracle admin]$ ls
  3 listener.ora  samples  shrept.lst
  4 [oracle@oracle admin]$ ls
  5 listener.ora  samples  shrept.lst  tnsnames.ora
  6 [oracle@oracle admin]$ cat tnsnames.ora
  7 # tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora
  8 # Generated by Oracle configuration tools.
  9 
 10 ORCL1 =
 11   (DESCRIPTION =
 12     (ADDRESS_LIST =
 13       (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.242.10)(PORT = 1521))
 14     )
 15     (CONNECT_DATA =
 16       (SERVICE_NAME = prod2)
 17     )
 18   )
 19 
 20 [oracle@oracle admin]$




image





3: 动态注册与静态注册


查看监听和数据库连接文件的信息

  1 Last login: Sat Oct 21 12:08:00 2017 from 192.168.242.1
  2 #登录到oracle用户
  3 [root@oracle ~]# su - oracle
  4 # 查看 监听信息 ;主要目的是获取监听文件的位置信息
  5 [oracle@oracle ~]$ lsnrctl status
  6 
  7 LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 21-OCT-2017 12:12:12
  8 
  9 Copyright (c) 1991, 2011, Oracle.  All rights reserved.
 10 
 11 Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oracle.example.com)(PORT=1521)))
 12 STATUS of the LISTENER
 13 ------------------------
 14 Alias                     LISTENER
 15 Version                   TNSLSNR for Linux: Version 11.2.0.3.0 - Production
 16 Start Date                21-OCT-2017 10:59:54
 17 Uptime                    0 days 1 hr. 12 min. 18 sec
 18 Trace Level               off
 19 Security                  ON: Local OS Authentication
 20 SNMP                      OFF
 21 Listener Parameter File   /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
 22 Listener Log File         /u01/app/oracle/diag/tnslsnr/oracle/listener/alert/log.xml
 23 Listening Endpoints Summary...
 24   (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oracle.example.com)(PORT=1521)))
 25   (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
 26 Services Summary...
 27 Service "orcl" has 1 instance(s).
 28   Instance "orcl", status READY, has 1 handler(s) for this service...
 29 Service "orclXDB" has 1 instance(s).
 30   Instance "orcl", status READY, has 1 handler(s) for this service...
 31 Service "prod" has 1 instance(s).
 32   Instance "prod", status READY, has 1 handler(s) for this service...
 33 Service "prod2" has 1 instance(s).
 34   Instance "prod2", status READY, has 1 handler(s) for this service...
 35 The command completed successfully
 36 #进入  监听文件信息文件位置
 37 [oracle@oracle ~]$ cd  /u01/app/oracle/product/11.2.0/db_1/network/admin/
 38 # 查看该文件路径下有文件
 39 [oracle@oracle admin]$ ls
 40 listener.ora  samples  shrept.lst  tnsnames.ora
 41 #查看监听配置信息
 42 [oracle@oracle admin]$ cat listener.ora
 43 # listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
 44 # Generated by Oracle configuration tools.
 45 
 46 LISTENER2 =
 47   (DESCRIPTION =
 48     (ADDRESS = (PROTOCOL = TCP)(HOST = oracle.example.com)(PORT = 1523))
 49   )
 50 #静态注册监听信息:
 51 LISTENER1 =
 52   (DESCRIPTION =
 53     (ADDRESS = (PROTOCOL = TCP)(HOST = oracle.example.com)(PORT = 1522))
 54   )
 55 #静态注册监听数据信息
 56 SID_LIST_LISTENER1 =
 57   (SID_LIST =
 58     (SID_DESC =
 59       (GLOBAL_DBNAME = prod2) #数据库服务名 
 60       (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1) #数据库默认安装路径
 61       (SID_NAME = prod2)  #数据库实例名
 62     )
 63   )
 64 
 65 ADR_BASE_LISTENER2 = /u01/app/oracle
 66 
 67 ADR_BASE_LISTENER1 = /u01/app/oracle
 68 
 69 #动态配置的监听信息;oracle数据库默认配置的数据信息
 70 LISTENER =
 71   (DESCRIPTION_LIST =
 72     (DESCRIPTION =
 73       (ADDRESS = (PROTOCOL = TCP)(HOST = oracle.example.com)(PORT = 1521))
 74     )
 75     (DESCRIPTION =
 76       (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
 77     )
 78   )
 79 
 80 ADR_BASE_LISTENER = /u01/app/oracle
 81 
 82 #查看数据库远程连接文件信息
 83 [oracle@oracle admin]$ cat tnsnames.ora
 84 # tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora
 85 # Generated by Oracle configuration tools.
 86 
 87 # 手工建库实例  数据库实例prod2的连接配置方式  默认监听 1521
 88 PROD2 =
 89   (DESCRIPTION =
 90     (ADDRESS_LIST =
 91       (ADDRESS = (PROTOCOL = TCP)(HOST = oracle.example.com)(PORT = 1521))
 92     )
 93     (CONNECT_DATA =
 94       (SERVICE_NAME = prod2)
 95     )
 96   )
 97 # 手工建库实例   数据库实例prod的连接配置方式   默认监听 1521
 98 PROD1 =
 99   (DESCRIPTION =
100     (ADDRESS_LIST =
101       (ADDRESS = (PROTOCOL = TCP)(HOST = oracle.example.com)(PORT = 1521))
102     )
103     (CONNECT_DATA =
104       (SERVICE_NAME = prod)
105     )
106   )
107 # dbca模式建库 数据库实例orcl的连接配置方式   默认监听 1521
108 ORCL1 =
109   (DESCRIPTION =
110     (ADDRESS_LIST =
111       (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.242.10)(PORT = 1521))
112     )
113     (CONNECT_DATA =
114       (SERVICE_NAME = orcl)
115     )
116   )
117 
118 # 手工建库实例  数据库实例prod2的连接配置方式  默认监听 1522
119 PORD2_1522 =
120   (DESCRIPTION =
121     (ADDRESS_LIST =
122       (ADDRESS = (PROTOCOL = TCP)(HOST = oracle.example.com)(PORT = 1522))
123     )
124     (CONNECT_DATA =
125       (SERVICE_NAME = pord2)
126     )
127   )
128 
129 [oracle@oracle admin]$

使用 netmgr 命令进行 监听注册监听信息


1:基本的创建请参考“上述:《使用 “netmgr” 创建 服务名》的操作步骤”


2:添加 静态注册监听信息

  2-1:先创建1个 监听 LISTENER2

    image

  2-2:选择“数据库设置”

                image

               image

              说明:Global Database Name 一般都是在使用DBCA 静默方式安装数据实例或者是 使用手动建库方式创建数据库实例时都需要配置的数据库域名; 

         即:这个数据库系统参数的值: db_domain='prod2’  ;一般情况该值的输入都是一个域名的模式db_domain='PROD2.orcle.com' ;
          此处因为我在使用手工建库的时候是没有写该系统参数,于是创建数据库实例的时候是取的默认缺省值。
        在dbca图鉴命令创建的时候填写的位置为:
       image

     Oracle Home Directory : oracle数据库默认安装路径:/u01/app/oracle/product/11.2.0/db_1

     SID: 数据库实例SID: prod2






二:配置 Oracle 网络环境


1:网络故障 failover




2:设置网络追踪 






3:配置数据库实例支持共享服务



image



image




image

共享Server:也就是多个人共享1个空闲Server process;

image

.分发器(dispatcher):处理连接数太多的情况进行分发连接。

目的就是为了节约服务器内存。

image

具体的Oracle数据库结构内容请 参考《Oracle 服务器结构》中的第二点内容:

二、Oracle 内存结构

  1 ---------程序全局区 (PGA)
  2 程序全局区 (PGA) 是包含某服务器进程的数据及控制信息的内存区。
  3 这是 Oracle 服务器在服务器进程启动时创建的非共享内存,只有该服务器进程才能访问。
  4 Oracle 实例的所有服务器进程分配的 PGA 总内存,也称为该实例分配的聚集 PGA 内存。
  5 使用共享服务器时,部分 PGA 可位于 SGA 中。
  6 
  7 PGA 内存通常包含以下各项:
  8 专用 SQL 区  、游标和 SQL 区、工作区、会话内存
  9 
 10 专用 SQL 区
 11 专用 SQL 区包含绑定信息和运行时内存结构等数据。这些信息是每个会话的 SQL 语句调用所特有的;在其它方面,绑定变量有不同的值,游标的状态也不同。发出 SQL 语句的每个会话都有一个专用 SQL 区。提交同一 SQL 语句的每个用户也都有其自己的专用 SQL区,该专用 SQL 区使用一个共享 SQL 区。这样,许多专用 SQL 区可与同一个共享 SQL区关联。专用 SQL 区的位置取决于为会话建立的连接类型。
 12 
 13 如果会话是通过专用服务器连接的,则专用 SQL 区位于该服务器进程的 PGA 中。
 14 不过,如果会话是通过共享服务器连接的,则部分专用 SQL 区将保留在 SGA 中。
 15 
 16 
 17 游标和 SQL 区
 18 Oracle 预编译器程序或 OCI 程序的应用程序开发人员可以显式打开特定专用 SQL 区的游标或句柄,并在该程序的整个执行过程中将它们用作命名资源。数据库为某些 SQL 语句隐式发出的递归游标也使用共享 SQL 区。
 19 
 20 
 21 工作作区
 22 对于复杂查询(例如,决策支持查询),会将大部分 PGA 供内存密集型运算符分配的
 23 工作区专用,例如:
 24 基于排序的运算符(如 ORDER BY、GROUP BY、ROLLUP)和窗口函数
 25 散列联接
 26 位图合并
 27 位图创建
 28 批量装载操作使用的写缓冲区
 29 排序运算符使用工作区(排序区),对一组行执行内存中排序。与此类似,散列联接运算
 30 符使用工作区(散列区),根据其左侧输入内容生成散列表。
 31 
 32 工作区的大小是可以控制和优化的。通常,较大的工作区可以显著改进特定运算符的性能,不过代价是消耗较多的内存。
 33 
 34 
 35 会话内存
 36 会话内存是用于存放会话的变量(登录信息)以及与会话相关的其它信息的内存。对于共享服务器,会话内存是共享的,而不是专用的。
 37 
 38 
 39 ----------自动 PGA 内存管理
 40 根据 PGA_AGGREGATE_TARGET 参数,动态调整
 41 专用于工作区的 PGA 内存量
 42 有助于最大限度地提高所有内存密集型 SQL 操作的性能
 43 默认情况下是启用的
 44 
 45 
 46 理想情况下,工作区的大小只要能容纳其相关 SQL 运算符分配的输入数据和辅助内存结构就已足够。这一大小称为工作区的最佳大小。工作区大小小于最佳大小时,响应时间就会增加,因为要对部分输入数据执行额外的传输操作。
 47 
 48 
 49 自动 PGA 内存管理功能简化并改进了 PGA 内存的分配方式。默认情况下,PGA 内存管理功能是启用的。在此模式下,Oracle 数据库以 SGA 内存大小的 20% 为基准,动态调整
 50 专用于工作区的那部分 PGA 内存的大小。最小值为 10 MB。
 51 
 52 
 53 在自动 PGA 内存管理模式下运行时,所有会话的工作区大小的调整都是自动的,
 54 
 55 在任何给定时间,可用于实例中各活动工作区的 PGA 内存总量自动从
 56 PGA_AGGREGATE_TARGET 初始化参数派生。此内存量设置为
 57 PGA_AGGREGATE_TARGET 值减去系统其它组件分配的 PGA 内存量(例如,会话分配的
 58 PGA 内存)。由此得到的 PGA 内存随后按照各活动工作区的特定内存需求分配给相应的工作区。
 59 
 60 
 61 --PGA 管理资源
 62 配置新实例时,难以确切知道 PGA_AGGREGATE_TARGET 的适当设置。该设置可以通过
 63 以下三个阶段确定:
 64 1.根据惯例,初步估算 PGA_AGGREGATE_TARGET。默认情况下,Oracle DB 将其设
 65 置为 SGA 大小的 20%。不过,对于大型 DSS 系统而言,此初始设置可能太低。
 66 2.在实例上运行典型工作量,使用 Oracle 收集到的 PGA 统计信息监视性能,查看最大 PGA 大小的配置是过低还是过高。
 67 3.使用 Oracle PGA 建议统计信息来优化 PGA_AGGREGATE_TARGET。
 68 
 69 
 70 为了能够向后兼容,可以通过将 PGA_AGGREGATE_TARGET 初始化参数值设置为 0,
 71 来禁用自动 PGA 内存管理。禁用了自动 PGA 内存管理功能后,可以使用关联的
 72 *_AREA_SIZE 参数调整工作区的最大大小。以下是这些参数的示例:
 73 
 74 SORT_AREA_SIZE
 75 HASH_AREA_SIZE
 76 BITMAP_MERGE_AREA_SIZE
 77  CREATE_BITMAP_AREA_SIZE
 78 
 79 
 80 ---------------使用内存指导调整 SGA 的大小
 81 使用内存指导可以优化内存结构的大小。如果启用了自动管理共享内存功能,则可以使
 82 用该功能优化 SGA 的总大小。如果禁用了 ASMM,则可以使用此指导来优化 SGA 的不同组件。
 83 
 84 内存指导包括三个指导:
 85 共享池
 86 缓冲区高速缓存
 87 主页---相关链接---指导中心----内存指导----共享池或缓冲区高速缓存--建议
 88 
 89 
 90 
 91 使用内存指导调整 PGA 的大小
 92 可使用内存指导获取有关 PGA 大小的建议。单击“PGA”选项卡,然后单击
 93 “Advice(建议)”。
 94 

如果分发器不够用的时候,则会需要分发器进行轮询使用。

image

当共享链接的时候:此时 私有的PGA 则会放在SGA里面。

image


共享服务的设置: 具体的系统参数设置请查看:《【Shared Server Mode】测试调整shared_servers参数对数据库的影响

                      《ORACLE参数max_shared_servers空值与零的区别



image


image

image

image

回环

image

image


image


image


数据库视图

  1 select * from  V$CIRCUIT                     ;
  2 ---共享服务启动了多少个
  3 select * from  V$SHARED_SERVER               ;
  4 ---共享分发器启动了多少个
  5 select * from  V$DISPATCHER                  ;
  6 select * from  V$SHARED_SERVER_MONITOR       ;
  7 ---排序:有多少个固列排列 ,排列越长表示分发器 server 进程 就够用
  8 select * from  V$QUEUE                       ;
  9 ---在线session 用户信息
 10 select * from  V$SESSION                     ;
 11 

image


image

image

查看专用连接与共享连接的区别:

 1:查看专用链接:(默认和专用的配置信息是一样的)

   image

   image

  查看配置文件信息

  1 
  2 [oracle@oracle admin]$ cat tnsnames.ora
  3 # tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora
  4 # Generated by Oracle configuration tools.
  5 
  6 PROD21 =
  7   (DESCRIPTION =
  8     (ADDRESS_LIST =
  9       (ADDRESS = (PROTOCOL = TCP)(HOST = oracle.example.com)(PORT = 1522))
 10     )
 11     (CONNECT_DATA = (SERVER = DEDICATED) #专用模式
 13       (SERVICE_NAME = prod2)
 14     )
 15   )
 16 
 17 PROD2 =
 18   (DESCRIPTION =
 19     (ADDRESS_LIST =
 20       (ADDRESS = (PROTOCOL = TCP)(HOST = oracle.example.com)(PORT = 1521))
 21     )
 22     (CONNECT_DATA =
 23       (SERVICE_NAME = prod2)
 24     )
 25   )
 26 
 27 PROD1 =
 28   (DESCRIPTION =
 29     (ADDRESS_LIST =
 30       (ADDRESS = (PROTOCOL = TCP)(HOST = oracle.example.com)(PORT = 1521))
 31     )
 32     (CONNECT_DATA =
 33       (SERVICE_NAME = prod)
 34     )
 35   )
 36 
 37 ORCL1 =
 38   (DESCRIPTION =
 39     (ADDRESS_LIST =
 40       (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.242.10)(PORT = 1521))
 41     )
 42     (CONNECT_DATA =
 43       (SERVICE_NAME = orcl)
 44     )
 45   )
 46 
 47 [oracle@oracle admin]$


如果设置为 共享模式:

image

  1 [oracle@oracle admin]$ cat tnsnames.ora
  2 # tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora
  3 # Generated by Oracle configuration tools.
  4 
  5 PROD21 =
  6   (DESCRIPTION =
  7     (ADDRESS_LIST =
  8       (ADDRESS = (PROTOCOL = TCP)(HOST = oracle.example.com)(PORT = 1522))
  9     )
 10     (CONNECT_DATA = (SERVER = SHARED) #共享模式
 12       (SERVICE_NAME = prod2)
 13     )
 14   )
 15 
 16 PROD2 =
 17   (DESCRIPTION =
 18     (ADDRESS_LIST =
 19       (ADDRESS = (PROTOCOL = TCP)(HOST = oracle.example.com)(PORT = 1521))
 20     )
 21     (CONNECT_DATA =
 22       (SERVICE_NAME = prod2)
 23     )
 24   )
 25 
 26 PROD1 =
 27   (DESCRIPTION =
 28     (ADDRESS_LIST =
 29       (ADDRESS = (PROTOCOL = TCP)(HOST = oracle.example.com)(PORT = 1521))
 30     )
 31     (CONNECT_DATA =
 32       (SERVICE_NAME = prod)
 33     )
 34   )
 35 
 36 ORCL1 =
 37   (DESCRIPTION =
 38     (ADDRESS_LIST =
 39       (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.242.10)(PORT = 1521))
 40     )
 41     (CONNECT_DATA =
 42       (SERVICE_NAME = orcl)
 43     )
 44   )
 45 
 46 [oracle@oracle admin]$





image

image



3:   高可用连接  High Availability of Connections


Objectives (目标)

After completing this lesson, you should be able to:(在完成这一课之后,你应该能够做到:)

  • Configure client-side, connect-time load balancing(配置客户端、连接-时间负载平衡)
  • Configure client-side, connect-time failover(配置客户端、连接时间故障转移)
  • Configure server-side, connect-time load balancing(配置客户端、连接时间故障转移)
  • Use the Load Balancing Advisory (LBA)(使用负载平衡咨询(LBA))
  • Describe the benefits of Fast Application Notification (FAN)(描述快速应用程序通知(扇)的好处)
  • Configure server-side callouts(•配置服务器端插图的编号)
  • Configure Transparent Application Failover (TAF)(配置透明的应用程序故障转移(TAF))



Types of Workload Distribution( 类型的工作负载分布)

• Connection balancing is rendered possible by configuring multiple listeners on multiple nodes(通过在多个节点上配置多个侦听器,可以实现连接平衡:)

  1. – Client-side, connect-time load balancing(客户端,连接-时间负载平衡)
  2. – Client-side, connect-time failover  (客户端,连接时间故障转移)
  3. – Server-side, connect-time load balancing(服务器端,连接-时间负载平衡)

• Run-time connection load balancing is rendered possible by using connection pools(通过使用连接池,可以实现运行时连接负载平衡。)

  1. – Work requests automatically balanced across the pool of connections (-工作请求在连接池中自动平衡)
  2. – Native feature of the JDBC implicit connection cache and ODP.NET connection pool (JDBC隐式连接缓存和ODP的本地特性。网络连接池)

image

local_listener :当前连接的监听信息

remote_listener: 把监听连接推送到比较空闲的监听去。


客户端,连接时间故障转移   Client-Side, Connect-Time Failover

一个数据库4个数据库实例4个数据库监听。

image

image

客户端,连接时间故障转移

image

nodevip :活动的节点ip地址;



Server-Side Load Balancing (服务器端负载平衡)

• Server-side load balancing:  (服务器端负载均衡:)

  1. – Causes the listener to direct connection requests to the best instance currently providing the service(-让侦听器将连接请求直接指向当前提供服务的最佳实例)
  2. – Uses connection information from the LBA (-使用来自LBA的连接信息)

• When DBCA is used to create a RAC database:(当DBCA用于创建RAC数据库时:)

  1. – Server-side load balancing is configured and enabled.(服务器端负载平衡被配置和启用。)
  2. – The REMOTE_LISTENER parameter is set to the SCAN listener.(-remotelistener参数设置为扫描侦听器。)

• If DBCA is not used or listener ports other than 1521 are used, LOCAL_LISTENER and REMOTE_LISTENER parameters should point to scan_name:scan_port (如果DBCA没有使用,或者使用了超过1521的侦听器端口,locallistener和remotelistener参数应该指向scanname:scanport)

• The LBA depends on an accurate configuration that  includes setting the CLB_GOAL for the service (LBA依赖于精确的配置,包括为服务设置clb球门)


image


image



Fast Application Notification: Benefits  (快速应用程序通知:福利)

  • • No need for connections to rely on connection timeouts (不需要连接依赖于连接超时)
  • • Used by Load Balancing Advisory to propagate load information(负载平衡咨询用于传播负载信息)
  • • Designed for enterprise application and management console integration (为企业应用程序和管理控制台设计)
  • • Reliable distributed system that:(集成)
  1. – Detects high-availability event occurrences in a timely manner(可靠的分布式系统:)
  2. – Pushes notification directly to your applications(直接向应用程序推送通知)

• Tightly integrated with:(紧密集成:)

  1. – Oracle JDBC applications using connection pools(使用连接池的Oracle JDBC应用程序)
  2. – Enterprise Manager(企业管理器)
  3. – Data Guard Broker(数据保护代理)

image

image


image


image

image



Implementation of Server-Side Callouts 实现服务器端插图的编号


• The callout directory:(callout目录:)

  1. – <GRID_Home>/racg/usrco
  2. – Can store more than one callout(可以存储多个调用)
  3. – Grants execution on callouts and the callout directory to the Oracle Clusterware user (-授权在callout和callout目录上执行)


• The order in which callouts are executed is nondeterministic.(Oracle和修改用户)
• Writing callouts involves:(执行调用的顺序是)

  1. Parsing callout arguments: The event payload(1。解析callout参数:事件有效负载)
  2. Filtering incoming FAN events  (2。事件过滤传入的FAN )
  3. Executing event-handling programs (3。执行事件处理程序)




  1 C - 19 Copyright © 2010, Oracle and/or its affiliates. All rights reserved.
  2 Server-Side Callout Parse: Example
  3 #!/bin/sh
  4 NOTIFY_EVENTTYPE=$1
  5 for ARGS in $*; do
  6 PROPERTY=`echo $ARGS | $AWK -F"=" '{print $1}'`
  7 VALUE=`echo $ARGS | $AWK -F"=" '{print $2}'`
  8 case $PROPERTY in
  9 VERSION|version) NOTIFY_VERSION=$VALUE ;;
 10 SERVICE|service) NOTIFY_SERVICE=$VALUE ;;
 11 DATABASE|database) NOTIFY_DATABASE=$VALUE ;;
 12 INSTANCE|instance) NOTIFY_INSTANCE=$VALUE ;;
 13 HOST|host) NOTIFY_HOST=$VALUE ;;
 14 STATUS|status) NOTIFY_STATUS=$VALUE ;;
 15 REASON|reason) NOTIFY_REASON=$VALUE ;;
 16 CARD|card) NOTIFY_CARDINALITY=$VALUE ;;
 17 TIMESTAMP|timestamp) NOTIFY_LOGDATE=$VALUE ;;
 18 ??:??:??) NOTIFY_LOGTIME=$PROPERTY ;;
 19 esac
 20 done
 21 
 22 


  1 
  
  3 Server-Side Callout Filter: Example
  4 if ((( [ $NOTIFY_EVENTTYPE = "SERVICE" ] ||
  5 [ $NOTIFY_EVENTTYPE = "DATABASE" ] || \
  6 [ $NOTIFY_EVENTTYPE = "NODE" ] \
  7 ) && \
  8 ( [ $NOTIFY_STATUS = "not_restarting" ] \
  9 )) && \
 10 ( [ $NOTIFY_DATABASE = "PROD" ] || \
 11 [ $NOTIFY_SERVICE = "ERP" ] \
 12 ))
 13 then
 14 /usr/local/bin/logTicket $NOTIFY_LOGDATE \
 15 $NOTIFY_LOGTIME \
 16 $NOTIFY_SERVICE \
 17 $NOTIFY_DBNAME \
 18 $NOTIFY_HOST
 19 fi


image


image


image

image



image

image


image


image


image





数据库考试题目练习


1:数据安装操作练习:考试题目


1: Creating a database & Server Configuration

  1  --[101]--
  2 #创建数据库
  3 1. Create the database
  4 #使用dbca创建一个PROD1数据库,包括Sample Schema,启用archivelog,没有EM。
  5 1.1 Create a PROD1 database with dbca, including Sample Schema, enable archivelog, without EM.
  6 #全局数据库名称:PROD1.oracle.com ; SID:PROD1
  7 Global db name: PROD1.oracle.com SID: PROD1
  8 #运行最小所需脚本以创建具有create database语句的PROD2数据库。 全局数据库名称:PROD2.oracle.com ;SID:PROD2
  9 1.2 Run the minimum required scripts to create a PROD2 database with create database statement.
 10  Global db name: PROD2.oracle.com  SID: PROD2
 11 --[102]--
 12 #确定并设置数据库结构的大小参数
 13 2. Determine and set sizing parameters for database structures
 14 #查看DB的以下设置:spfile,db名称,db域,全局数据库名称,快速恢复区域,控制文件,数据文件;
 15 2.1 View the following setting of the DB: spfile, db name, db domain, global db name, fast recovery area, control files, data files;
 16 #将快速恢复区域大小更改为5G。
 17 2.2 Change fast recovery area size to 5G.
 18 #将控制文件更改为3个成员,然后更改为2个成员;
 19 2.3 Change control files to 3 members, then change back to 2 members;
 20 #设置db块大小
 21 2.4 Setting the db block size
 22 #创建具有16k块大小的100MB表空间TESTDW
 23 2.4.1 Create a 100MB tablespace TESTDW with the 16k block size
 24 #删除表空间TESTDW,清除内存缓存大小。
 25 2.4.2 Drop the tablespace TESTDW, clear the memory cache size.
 26 #2.4.3检查记忆操作动作。
 27 2.4.3 Check the memory operation movements.
 28 #2.5设置以下要求:
 29 2.5 Set up for the following requirements:
 30 #2.5.1正常工作时间内,OLTP用户数量或并发OLTP用户数约为120个。
 31 2.5.1 The number or concurrent OLTP users will be approximately 120 during normal business hours.
 32 #2.5.2将在晚上和周末运行的数量或并发批处理程序约为12到 15。
 33 2.5.2 The number or concurrent batch processes that will run in the evenings and weekends will approximately 12 to 15..
 34 #2.5.3修改或添加以下列出的参数: UTL_FILE_DIR =('/home/oracle','/home/oracle/temp','/home/oracle/scripts')
 35 2.5.3 modify or add the following listed parameters:UTL_FILE_DIR=('/home/oracle','/home/oracle/temp','/home/oracle/scripts')
 36 #2.6允许10秒的DDL语句等待锁定。检查并重置等待时间。
 37 2.6 Allow to 10 sec for a DDL statement to wait for a lock. Check it and reset the wait time.
 38 #2.7将参数memory_target更改为200g,重新启动db实例,并从错误方案中恢复。
 39 2.7 Change the parameter memory_target to 200g, restart the db instance, and recovery from the error scenario.
 40 --[103]--
 41 #3.创建和管理临时,永久和撤销表空间
 42 3. Create and manage temporary, permanent, and undo tablespaces
 43 #3.1创建一个包含两个临时表空间的临时表空间组,以支持批处理 创建大型索引和分析表格,使用以下规格:
 44 3.1 Create a temporary tablespace group that contains two temporary tablespaces to support batch processing, the creation of large indexes,and analyzing tables, Use the following specifications:
 45 #3.1.1临时表空间组名为TEMP_GRP,包含临时表空间TEMP和TEMP2。
 46 3.1.1 Temporary tablespace group named TEMP_GRP containing temporary tablespaces TEMP and TEMP2.
 47 #3.1.2使TEMP_GRP成为所有新用户的默认临时表空间。
 48 3.1.2 Make TEMP_GRP the default temporary tablespace for all new users.
 49 #3.1.3将默认临时表空间更改为TEMP。
 50 3.1.3 Change back the default temporary tablespace to TEMP.
 51 #3.1.4将临时表空间TEMP扩展到200MB,然后将其大小减小到100MB。
 52 3.1.4 Extend the temporary tablespace TEMP to 200MB, then cut its size to 100MB.
 53 #3.1.5使用语句检查临时用法:select a.object_id from dba_objects a,dba_objects b order by object_id; 
 54 3.1.5 Check the temporary usage with the statement: select a.object_id from dba_objects a,dba_objects b order by object_id;
 55 #3.1.6将临时表空间剪切到最小大小。
 56 3.1.6 Cut the temporary tablespace to the minimum size.
 57 #3.2创建以下永久表空间来存储样本测试数据。使用以下规格:
 58 3.2 Create the following permanent tablespace to store sample test data. Use the following specifications:
 59 #3.2.1表空间TEST01,初始数据文件大小为100MB
 60 3.2.1 Tablespace TEST01, initial datafile size 100MB
 61 #3.2.2表空间TEST02,初始数据文件大小为100MB,初始扩展大小为1MB,下一个扩展区大小为1MB)
 62 3.2.2 Tablespace TEST02, initial datafile size 100MB, Initial extent size of 1MB, next extent size of 1MB)
 63 #3.2.3表空间TEST03,初始数据文件大小为100MB,带freelist的段管理 
 64 3.2.3 Tablespace TEST03, initial datafile size 100MB, segment management with freelist
 65 #3.2.4表空间TEST04,初始数据文件大小为100MB,下一个扩展大小为100MB,最大大小为2G
 66 3.2.4 Tablespace TEST04, initial datafile size 100MB, next extent size of 100MB with maximum size of 2G
 67 #3.2.5具有OMF位置的表空间TEST05
 68 3.2.5 Tablespace TEST05 with the OMF location
 69 #3.2.6具有OMF位置的表空间TEST06,16k块大小。
 70 3.2.6 Tablespace TEST06 with the OMF location, 16k blocksize.
 71 #3.2.7表空间TEST07 for bigfile,初始数据文件大小为100MB,文件预期增长到4TB
 72 3.2.7 Tablespace TEST07 for bigfile, initial datafile size 100MB with the file expected to grow to 4TB
 73 #3.2.8检查上述表空间:名称,数据文件名,块大小,extent_management,段空间管理,分配类型,bigfile
 74 3.2.8 Check the above tablespaces: name, datafile name, block size, extent_management, segment space management, allocation type, bigfile
 75 #3.2.9在表空间TEST01中创建2个表:TEST_PCTFREE_0和TEST_PCTFREE_50。将dba_objects记录插入到它们中;检查2个表:记录数,块,平均空间和空格。
 76 3.2.9 Create 2 tables in tablespace TEST01: TEST_PCTFREE_0 and TEST_PCTFREE_50.Insert the dba_objects records to both of them; Check the 2 tables: number of records, blocks, avg space and pct free.
 77 #3.2.10删除上述表空间并清除环境设置(缓存大小,OMF路径)
 78 3.2.10 Drop the above tablespaces and clear the environment settings (cache size, OMF path)
 79 #3.3在数据库中设置自动撤销管理,支持以下要求:
 80 3.3 Set up automatic undo management in the database to support the following requirements:
 81 #3.3.1避免ORA-01555对于平均5个小时的查询,快照太旧错误。
 82 3.3.1 Avoid ORA-01555 Snapshot too old errors for queries running up to 5 hours on average.
 83 #3.3.2检查不同状态的UNDO的使用情况。
 84 3.3.2 Check the usage of UNDO with different status.
 85 #3.3.3将UNDO更改为tablespace undotbs2,然后更改为tablespace undotbs1。
 86 3.3.3 Change the UNDO to tablespace undotbs2, then change back to tablespace undotbs1.
 87 #3.3.4向表空间undotbs1添加一个新的datafile undotbs02.dbf(size = 100MB,扩展大小为10MB),然后删除新的撤消数据文件。
 88 3.3.4 Add a new datafile undotbs02.dbf (size=100MB, extent size of 10MB) to tablespace undotbs1, then drop the new undo datafile.
 89 #3.4检查所有表空间(永久,临时)及其数据文件:文件#,文件名,表空间名称
 90 3.4 Check all the tablespaces (permanent, temporary) and their datafiles: file#,file name,tablespace name
 91 --[104]--
 92 #4.跨多个物理设备和位置条带数据文件
 93 4. Stripe data files across multiple physical devices and locations
 94 #4.1确认路径的现有路径,并将权限设置为user oracle。
 95 #Dir1:/ u01 / app / oracle / oradata / PROD1
 96 #Dir2:/ u02 / app / oracle / oradata / PROD2
 97 4.1 Confirm the existing of the path and set the privileges to user oracle.
 98 Dir1: /u01/app/oracle/oradata/PROD1
 99 Dir2: /u02/app/oracle/oradata/PROD2
100 #4.2将重做日志文件重新映射到上述两个路径中的每组3组和2个成员
101 4.2 Recreate the redo log files to 3 groups and 2 members per group located to the above two paths
102 #4.3将位于Dir2中的新数据文件system02.dbf添加到系统表空间。
103 4.3 Add a new datafile system02.dbf located in the Dir2 to the system tablespace.
104 #4.4检查FAST RECOVERY AREA和archivelog文件的位置路径
105 4.4 Check the location path of FAST RECOVERY AREA and the archivelog files
106 --[105]--
107 #5.配置数据库环境以支持最佳数据访问性能
108 5. Configure the database environment to support optimal data access performance
109 #5.1校准参数FAST_START_MTTR_TARGET,请查看重做日志文件的建议大小。
110 5.1 Calibrate the parameter FAST_START_MTTR_TARGET, check the recommended size for redo log files.
111 #5.2配置数据库以在alert.log文件中记录检查点。
112 5.2 Configure your database to record checkpoints in the alert.log file.
113 --[106]--
114 #6.创建和管理数据库配置文件
115 6. Create and manage database configuration files
116 #6.1从MEMORY,SPFILE或PFILE创建临时PFILE / SPFILE。
117 6.1 Create a temporary PFILE / SPFILE from MEMORY, SPFILE or PFILE.
118 #6.2将MEMORY_TARGET参数更改为450M。
119 6.2 Change the MEMORY_TARGET parameter to 450M.
120 --[107]--
121 #7.创建和管理bigfile表空间
122 7. Create and manage bigfile tablespaces
123 #7.1创建一个大小为100MB的bigfile tablespace BIGTBS1
124 7.1 Create a bigfile tablespace BIGTBS1 with size 100MB
125 #7.2创建一个bigfile表空间BIGTBS2初始大小为100MB,下一个扩展大小为100MB,最大大小为100G
126 7.2 Create a bigfile tablespace BIGTBS2 initial size 100MB, next extent size of 100MB with maximum size 100G
127 #7.3更改BIGTBS1
128 7.3 Change the BIGTBS1 size to 200MB
129 #7.4删除2个表空间。
130 7.4 Drop the 2 tablespaces.
131 
132 --[108]--
133 #8.创建和管理使用NFS挂载文件系统文件的表空间
134 8. Create and Manage a tablespace that uses NFS mounted file system file
135 #8.1安装NFS服务器,将NFS客户端安装到NFS服务器存储。
136 8.1 Setup a NFS server, mount the NFS client to the NFS server storage.
137 #8.2启用直接NFS并在NFS存储上创建一个表空间TESTNFS大小为100MB。
138 8.2 Enable the Direct NFS and create a tablespace TESTNFS size 100MB on the NFS storage.
139 #8.3删除表空间并禁用直接NFS,清除NFS设置。
140 8.3 Drop the tablespace and Disable the Direct NFS, clear the NFS setting.
141 
142 --[109]--
143 #9.创建和管理多个网络配置文件
144 9. Create and manage multiple network configuration files
145 #9.1为手动创建PROD1,PROD2的客户端网络配置文件,包括listener.ora,tnsnames.ora,SQLNET.ORA。
146 9.1 Create the client-side network configuration files for PROD1,PROD2 manually, including listener.ora, tnsnames.ora,sqlnet.ora.
147 #9.2使用netca创建PROD1,PROD2的客户端网络配置文件,包括listener.ora,tnsnames.ora,sqlnet.ora。
148 9.2 Create the client-side network configuration files for PROD1,PROD2 with netca, including listener.ora,tnsnames.ora, sqlnet.ora.
149 #9.3创建客户端网络配置RACDB。它应该连接到RACDB.oracle.com服务与a 专用服务器连接。
150 9.3 Create a Client-side Network configuration RACDB. It should connect to the RACDB.oracle.com service with a dedicated server connection.
151 #9.3.1 RACDB.oracle.com服务将在两个RAC节点上运行:host1,host2
152 9.3.1 The RACDB.oracle.com service will be running on two RAC nodes: host1, host2
153 #9.3.2客户端配置应具有连接时故障转移和客户端负载平衡
154 9.3.2 The client-side configuration should with connect-time Failover and client load balancing
155 #9.3.3如果实例发生故障,应用程序将故障切换到另一个节点的监听器,保留任何SELECT声明正在进行中。
156 9.3.3 If the instance fails, the application should fail over to the other node's listener, reserving any SELECT statements in progress.
157 #9.4创建客户端网络配置PROD2。它应该连接到PROD2.oracle.com服务与a 专用服务器连接。
158 9.4 Create a Client-side Network configuration PROD2. It should connect to the PROD2.oracle.com service with a dedicated server connection.
159 #9.4.1 PROD2.oracle.com服务在主机ODD.oracle.com上运行
160 9.4.1 The PROD2.oracle.com service is running on host ODD.oracle.com
161 #9.4.2如果连接失败,则应等待15秒钟再尝试重新连接,并尝试重新连接最多20次。
162 9.4.2 If the connection fails, it should waits 15 seconds before trying to reconnect again, and attempts to reconnect up to 20 times.
163 #9.5添加一个名为LISTENER_TEST的侦听器,它将侦听端口50000.配置此侦听器以仅支持 自动实例注册。
164 9.5 Add a listener, named LISTENER_TEST,which will listen on port 50000.Configure this listener to support only automatic instance registrations.
165 #9.5.1设置PROD1实例以自动注册LISTENER_TEST。
166 9.5.1 Set up the PROD1 instance to automatically register with the LISTENER_TEST.
167 #9.5.2检查新的侦听器状态,然后将其删除。
168 9.5.2 Check the new listener status, and then remove it.
169 --[110]--
170 #10.创建和配置一个监听器
171 10. Create and configure a listener
172 #10.1使用图形工具设置监听器:netca,netmgr
173 10.1 Use the graphic tools to set up the listener: netca, netmgr
174 --[111]--
175 #11.配置数据库实例以支持共享服务器连接
176 11. Configure the database instance to support shared server connections
177 #11.1配置PROD1数据库最多支持300个会话,为专用连接保留100个。
178 11.1 Configure the PROD1 database to support up to 300 sessions, reserving 100 for dedicated connections.
179 #11.2配置PROD1数据库进行支持。
180 11.2 Configure the PROD1 database to support.
181 #11.2.1默认3个TCP调度程序,2个TCPS调度程序。
182 11.2.1 Default of 3 TCP dispatchers, 2 TCPS dispatchers。
183 #11.2.2最多10个调度员
184 11.2.2 Maximum of 10 dispatchers
185 #11.3配置PROD1数据库以支持:
186 11.3 Configure the PROD1 database to support:
187 #11.3.1最少10个共享服务器进程
188 11.3.1 Minimum of 10 shared server processes
189 #11.3.2最多30个共享服务器进程
190 11.3.2 Maximum of 30 shared server processes
191 #11.4将db与共享进程连接,然后删除此共享连接。
192 11.4 Connect the db with shared process, and then drop this shared connection.
193 #11.5删除上述共享服务器设置。
194 11.5 Remove the above shared server settings.
195 --[112]--
196  #12.设置网络跟踪
197 12. Set up network tracing
198 #12.1在PROD1上启用跟踪监听器,将跟踪文件路径设置为"/u01/app/oracle/trace/listener",并设置跟踪文件 到“listener.trc”,跟踪级别= 4
199 12.1 Enable tracing listener on PROD1, set the trace file path to "/u01/app/oracle/trace/listener" and set the traced file to "listener.trc", trace level=4
200 #12.2启用跟踪sqlnet.ora
201 12.2 Enable tracing the sqlnet.ora
202 #12.2.1设置客户端和服务器的跟踪级别以支持
203 12.2.1 set the trace level of client and server to support
204 #12.2.2将客户端的跟踪目录设置为‘/u01/app/oracle/trace/client’,服务器的跟踪目录‘/u01/app/oracle/trace/server’
205 12.2.2 set the trace directory for client to ‘/u01/app/oracle/trace/client’, the trace directory for server to ‘/u01/app/oracle/trace/server’
206 #12.3禁用所有跟踪
207 12.3 Disable all the tracing
208 --[113]--
209 #13.管理Oracle网络进程
210 13. Manage Oracle network processes
211 #13.1使用lsnrctl实用程序管理侦听器:start/status/services/trace/stop/reload。
212 13.1 Manage the listener with lsnrctl utility: start/status/services/trace/stop/reload.
213 --[114]--
214 #14.配置网络环境以允许连接到多个数据库
215 14. Configure the network environment to allow connections to multiple databases
216 #14.1修改listener.ora和tnsnames.ora以连接PROD1,PROD2数据库
217 14.1 Modify listener.ora and tnsnames.ora to connect the PROD1,PROD2 databases
218 #14.2设置listener.ora以允许默认服务连接到PROD1。
219 14.2 Set the listener.ora to allow default service to connect to the PROD1.
220 --[115]--
221 #15.使用无配置连接
222 15. Use configurationless connections
223 #14.1设置sqlnet.ora以允许无组件连接
224 14.1 Setting the sqlnet.ora to allow configurationless connection
225 --[116]--
226 #16.使用OPatch安装补丁
227 16. Use OPatch to install a patch
228 #16.1安装在线补丁 - p13429702_112030_LINUX
229 16.1 Install online patch - p13429702_112030_LINUX
230 --[117]--
231 #17.使用Grid Infrastructure来管理oracle数据库和其他资源
232 17. Use Grid Infrastructure to manage oracle databases and other resources
233 #17.1安装Grid Infrastructure软件
234 17.1 Install Grid Infrastructure software
235 #17.2启动高可用性服务并启用自动启动
236 17.2 Start the High Availability Services and enable automatic startup
237 #17.3将数据库PROD1,PROD2添加到Oracle重新启动配置
238 17.3 Add the database PROD1,PROD2 to the Oracle Restart configuration
239 --[118]--
240 #17.使用企业管理器配置助理(EMCA)实用程序
241 17. Use Enterprise Manager Configuration Assistant(EMCA) utility
242 #17.1为PROD1手动创建数据库控制EM
243 17.1 Create the database control EM manually for PROD1
244 #17.2手动启动和停止PROD1
245 17.2 Start and stop the EM manually for PROD1

2:数据库安装实际操作步骤

第一题:创建数据库:

  1 --[101]--
  2 #创建数据库
  3 1. Create the database
  4 #使用dbca创建一个PROD1数据库,包括Sample Schema,启用archivelog,没有EM。
  5 1.1 Create a PROD1 database with dbca, including Sample Schema, enable archivelog, without EM.
  6 #全局数据库名称:PROD1.oracle.com ; SID:PROD1
  7 Global db name: PROD1.oracle.com SID: PROD1
  8 #运行最小所需脚本以创建具有create database语句的PROD2数据库。 全局数据库名称:PROD2.oracle.com ;SID:PROD2
  9 1.2 Run the minimum required scripts to create a PROD2 database with create database statement.
 10  Global db name: PROD2.oracle.com  SID: PROD2

文档参考位置

  •  2 Day DBA -> 2 Installing Oracle Database and Creating a Database -> Creating and Managing a Database with DBCA
  •  Administrator's Guide -> 2 Creating and Configuring an Oracle Database -> Creating a Database with the CREATE DATABASE Statement

       #使用dbca创建一个PROD1数据库,包括Sample Schema,启用archivelog,没有EM。

 1.1 Create a PROD1 database with dbca, including Sample Schema, enable archivelog, without EM.  

      #全局数据库名称:PROD1.oracle.com ; SID:PROD1  

      Global db name: PROD1.oracle.com SID: PROD1


   1) Login as Linux user "oracle"  ;即 从 root 用户登录到oracle  用户,并核查oracle 用户下关于oracle数据库的系统环境参数信息

  1 #切换oracle用户
  2 [root@oracle ~]# su -  oracle
  3 #查看本用户根目录下信息
  4 [oracle@oracle ~]$ ls -a
  5 .  ..  .bash_history  .bash_logout  .bash_profile  .bashrc  .emacs  .kde  .mozilla  .viminfo  .zshrc
  6 #查看   .bash_profile 信息:在不同机器上面,该值名称不一定一样
  7 [oracle@oracle ~]$ cat .bash_profile
  8 # .bash_profile
  9 
 10 # Get the aliases and functions
 11 if [ -f ~/.bashrc ]; then
 12         . ~/.bashrc
 13 fi
 14 
 15 # User specific environment and startup programs
 16 
 17 PATH=$PATH:$HOME/bin
 18 
 19 export PATH
 20 export ORACLE_SID=PROD1
 21 export ORACLE_BASE=/u01/app/oracle
 22 export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/dbhome_1
 23 export PATH=$PATH:$ORACLE_HOME/bin
 24 
 25 export ORACLE_TERM=xterm
 26 export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ORACLE_HOME/lib
 27 
 28 export CLASSPATH=$ORACLE_HOME/JRE:ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib
 29 
 30 [oracle@oracle ~]$
 31 

2) Run the cmd of Database Configuration Assistant  执行DBCA 命令:

   命令执行之前,需要用root用户执行 xhost + 命令

  1 [root@oracle ~]# xhost +
  2 access control disabled, clients can connect from any host
  3 [root@oracle ~]# 
  4 

  执行DBCA命令

  1 [oracle@oracle ~]$ dbca
  2 

3) Welcome screen -> Click "Next"   命令执行后,调用出图形界面:执行下一步操作

image

4) Select "Create database" -> Click "Next"  选择创建数据库,然后在执行下一步按钮

image


5) Select "Custom Database" -> Click "Next"    此处按照要求选择相对应选项。此处我选择的是 第一个选项
其他选项:
 General Purpose or Transaction Processing
 Data Warehouse

image

6) Input the "Global Database Name" and SID    输入数据库名和数据库实例名
Global Database Name = "PROD1.oracle.com"
SID = "PROD1"
Click "Next" 

image

7) Deselect "Configure Enterprise Manager" -> Click "Next"  ;去掉“配置企业管理器”的选择
注意:如果选择此项,需要先启动监听(Listener)

image

8) Select "Use the Same Administrative Password for All Account"
Password = "xxxxxx" (例如:oracle)
Confirm Password = "xxxxxx" (例如:oracle)
Click "Next"

把所有用户的密码都设置一样的选择。 考试的时候最后都设置一样的 除掉默认的oracle 名称其他的密码;

image

image

password entered does not satisty oracle recommended password complexity policy.
a password should hava minimum of 8 characters in length . in addition,the password must contain at least one upper case character ,one lower case character and one digit;

密码输入不符合oracle推荐的密码复杂性策略。  一个密码最多不得超过8个字符。 此外,密码必须包含至少一个大写字母,一个小写字母和一位数字;


 

9) Set the storage type:  设置存储类型:
Storage Type = "File System"  存储类型=“文件系统”
Select "Use Common Location for All Database Files"     选择“对所有数据库文件使用通用位置”
Database Files Location = "{ORACLE_BASE}/oradata"  数据库文件位置=“{ORACLE_BASE} / oradata”
Click "Next"

image


10) Keep the default settings of Flash/Fast Recovery Area -> Deselect "Enable Archiving" -> Click "Next"

 保留Flash(闪存) /快速恢复区域的默认设置 - >取消选择“启用归档” - >单击“下一步”

image

11) Select "Sample Schema" and keep the default settings of other components -> Click "Next"
 选择安装默认组件:完成时间?
 取消安装全部组件:完成时间?

image


12) Select the character set, keep other options by default
Click tab "Character Sets"
Click "Use Unicode (AL32UTF8)
Click "Next"

image

image

 imageimage

13) In the window "Database Storage" -> Click "Next"

image


14) Activate the boxes "Create Database" and "Generate Database Creation Scripts" -> Click "Finish" (The default path for the script = /u01/app/oracle/admin/PROD1/scripts)

创建数据库实例并生成数据库生成脚本

image

15) Confirm the Summary -> Click "OK"

image

16) Confirm to create the script of DB - > Click "OK"

image

17) Finish the installation -> Click "Exit"

image

image

测试下安装结果:

  1 [oracle@oracle ~]$ export ORACLE_SID=PROD1
  2 [oracle@oracle ~]$ sqlplus / as sysdba;
  3 
  4 SQL*Plus: Release 11.2.0.3.0 Production on Sun Oct 22 16:12:23 2017
  5 
  6 Copyright (c) 1982, 2011, Oracle.  All rights reserved.
  7 
  8 
  9 Connected to:
 10 Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
 11 With the Partitioning, OLAP, Data Mining and Real Application Testing options
 12 
 13 SQL> startup force
 14 ORACLE instance started.
 15 
 16 Total System Global Area 1707446272 bytes
 17 Fixed Size                  1345408 bytes
 18 Variable Size             989857920 bytes
 19 Database Buffers          704643072 bytes
 20 Redo Buffers               11599872 bytes
 21 Database mounted.
 22 Database opened.
 23 SQL> exit
 24 Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
 25 With the Partitioning, OLAP, Data Mining and Real Application Testing options
 26 [oracle@oracle ~]$ sqlplus /nolog
 27 
 28 SQL*Plus: Release 11.2.0.3.0 Production on Sun Oct 22 16:12:58 2017
 29 
 30 Copyright (c) 1982, 2011, Oracle.  All rights reserved.
 31 
 32 SQL> connect sys/oracle as sysdba;
 33 Connected.
 34 SQL>
 35 
 36 

【补充】
18) Set the DB in ARCHIVELOG mode, and activate FLASHBACK mode.

  1 [oracle@oracle ~]$ export ORACLE_SID=PROD1
  2 [oracle@oracle ~]$ sqlplus / as sysdba;
  3 
  4 SQL*Plus: Release 11.2.0.3.0 Production on Sun Oct 22 16:45:38 2017
  5 
  6 Copyright (c) 1982, 2011, Oracle.  All rights reserved.
  7 
  8 
  9 Connected to:
 10 Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
 11 With the Partitioning, OLAP, Data Mining and Real Application Testing options
 12 
 13 SQL> shutdown immediate
 14 Database closed.
 15 Database dismounted.
 16 ORACLE instance shut down.
 17 SQL> startup mount;
 18 ORACLE instance started.
 19 
 20 Total System Global Area 1707446272 bytes
 21 Fixed Size                  1345408 bytes
 22 Variable Size             989857920 bytes
 23 Database Buffers          704643072 bytes
 24 Redo Buffers               11599872 bytes
 25 Database mounted.
 26 SQL> alter database archivelog;
 27 
 28 Database altered.
 29 
 30 SQL> alter database flashback on;
 31 
 32 Database altered.
 33 
 34 SQL> alter database open
 35   2  ;
 36 
 37 Database altered.
 38 
 39 SQL> select name ,log_mode,flashback_on from v$database ;
 40 
 41 NAME      LOG_MODE     FLASHBACK_ON
 42 --------- ------------ ------------------
 43 PROD1     ARCHIVELOG   YES
 44 
 45 SQL>
 46 



#运行最小所需脚本以创建具有create database语句的PROD2数据库。 全局数据库名称:PROD2.oracle.com ;SID:PROD2  

1.2 Run the minimum required scripts to create a PROD2 database with create database statement. 

     Global db name: PROD2.oracle.com    SID: PROD2

Step 1: Specify an Instance Identifier (SID)
DB 设计:ORACLE_SID=PROD2,db_name=PROD2, db_domain=oracle.com

  1 [root@oracle ~]# su - oracle
  2 [oracle@oracle ~]$ ls -a
  3 .  ..  .bash_history  .bash_logout  .bash_profile  .bashrc  .emacs  .kde  .mozilla  .viminfo  .zshrc
  4 [oracle@oracle ~]$ cat .bash_profile
  5 # .bash_profile
  6 
  7 # Get the aliases and functions
  8 if [ -f ~/.bashrc ]; then
  9         . ~/.bashrc
 10 fi
 11 
 12 # User specific environment and startup programs
 13 
 14 PATH=$PATH:$HOME/bin
 15 
 16 export PATH
 17 export ORACLE_SID=PROD1
 18 export ORACLE_BASE=/u01/app/oracle
 19 export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/dbhome_1
 20 export PATH=$PATH:$ORACLE_HOME/bin
 21 
 22 export ORACLE_TERM=xterm
 23 export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ORACLE_HOME/lib
 24 
 25 export CLASSPATH=$ORACLE_HOME/JRE:ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib
 26 
 27 [oracle@oracle ~]$ vi .bash_profile
 28 [oracle@oracle ~]$ cat .bash_profile
 29 # .bash_profile
 30 
 31 # Get the aliases and functions
 32 if [ -f ~/.bashrc ]; then
 33         . ~/.bashrc
 34 fi
 35 
 36 # User specific environment and startup programs
 37 
 38 PATH=$PATH:$HOME/bin
 39 
 40 export PATH
 41 export ORACLE_SID=PROD2
 42 export ORACLE_BASE=/u01/app/oracle
 43 export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/dbhome_1
 44 export PATH=$PATH:$ORACLE_HOME/bin
 45 
 46 export ORACLE_TERM=xterm
 47 export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ORACLE_HOME/lib
 48 
 49 export CLASSPATH=$ORACLE_HOME/JRE:ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib
 50 
 51 [oracle@oracle ~]$
 52 
 53 

Step 2: Ensure That the Required Environment Variables Are Set

   


  1 
  2     # 切换到 oracle 用户
  3     [root@oracle ~]# su - oracle
  4     # 进入 oracle_home 安装的根目录
  5     [oracle@oracle ~]$ cd $ORACLE_HOME
  6     #查看目录路劲
  7     [oracle@oracle db_1]$ pwd
  8     /u01/app/oracle/product/11.2.0/db_1
  9     #查看该路劲下的文件信息
 10     [oracle@oracle db_1]$ ls
 11     apex         css          EMStage        jdev     nls                      oui       scheduler        usm
 12     assistants   ctx          has            jdk      oc4j                     owb       slax             utl
 13     bin          cv           hs             jlib     odbc                     owm       sqldeveloper     wwg
 14     ccr          dbs          ide            ldap     olap                     perl      sqlj             xdk
 15     cdata        dc_ocm       install        lib      OPatch                   plsql     sqlplus
 16     cfgtoollogs  deinstall    instantclient  log      opmn                     precomp   srvm
 17     clone        demo         inventory      md       oracle.example.com_orcl  racg      sysman
 18     config       diagnostics  j2ee           mesg     oracore                  rdbms     timingframework
 19     crs          dv           javavm         mgw      oraInst.loc              relnotes  ucp
 20     csmig        emcli        jdbc           network  ord                      root.sh   uix
 21     # 进入dbs 文件夹
 22     [oracle@oracle db_1]$ cd dbs
 23     #查看dbs文件下的文件信息
 24     [oracle@oracle dbs]$ ls
 25     hc_orcl.dat  hc_prod.dat  init.ora  initprod.ora  lkORCL  lkPROD  orapworcl  spfileorcl.ora  spfileprod.ora
 26     #查看 init.ora 文件信息
 27     [oracle@oracle dbs]$ cat init.ora
 28     #
 29     # $Header: rdbms/admin/init.ora /main/23 2009/05/15 13:35:38 ysarig Exp $
 30     #
 31     # Copyright (c) 1991, 1997, 1998 by Oracle Corporation
 32     # NAME
 33     #   init.ora
 34     # FUNCTION
 35     # NOTES
 36     # MODIFIED
 37     #     ysarig     05/14/09  - Updating compatible to 11.2
 38     #     ysarig     08/13/07  - Fixing the sample for 11g
 39     #     atsukerm   08/06/98 -  fix for 8.1.
 40     #     hpiao      06/05/97 -  fix for 803
 41     #     glavash    05/12/97 -  add oracle_trace_enable comment
 42     #     hpiao      04/22/97 -  remove ifile=, events=, etc.
 43     #     alingelb   09/19/94 -  remove vms-specific stuff
 44     #     dpawson    07/07/93 -  add more comments regarded archive start
 45     #     maporter   10/29/92 -  Add vms_sga_use_gblpagfile=TRUE
 46     #     jloaiza    03/07/92 -  change ALPHA to BETA
 47     #     danderso   02/26/92 -  change db_block_cache_protect to _db_block_cache_p
 48     #     ghallmar   02/03/92 -  db_directory -> db_domain
 49     #     maporter   01/12/92 -  merge changes from branch 1.8.308.1
 50     #     maporter   12/21/91 -  bug 76493: Add control_files parameter
 51     #     wbridge    12/03/91 -  use of %c in archive format is discouraged
 52     #     ghallmar   12/02/91 -  add global_names=true, db_directory=us.acme.com
 53     #     thayes     11/27/91 -  Change default for cache_clone
 54     #     jloaiza    08/13/91 -         merge changes from branch 1.7.100.1
 55     #     jloaiza    07/31/91 -         add debug stuff
 56     #     rlim       04/29/91 -         removal of char_is_varchar2
 57     #   Bridge     03/12/91 - log_allocation no longer exists
 58     #   Wijaya     02/05/91 - remove obsolete parameters
 59     #
 60     ##############################################################################
 61     # Example INIT.ORA file
 62     #
 63     # This file is provided by Oracle Corporation to help you start by providing
 64     # a starting point to customize your RDBMS installation for your site.
 65     #
 66     # NOTE: The values that are used in this file are only intended to be used
 67     # as a starting point. You may want to adjust/tune those values to your
 68     # specific hardware and needs. You may also consider using Database
 69     # Configuration Assistant tool (DBCA) to create INIT file and to size your
 70     # initial set of tablespaces based on the user input.
 71     ###############################################################################
 72 
 73     # Change '<ORACLE_BASE>' to point to the oracle base (the one you specify at
 74     # install time)
 75 
 76     db_name='ORCL'
 77     memory_target=1G
 78     processes = 150
 79     audit_file_dest='<ORACLE_BASE>/admin/orcl/adump'
 80     audit_trail ='db'
 81     db_block_size=8192
 82     db_domain=''
 83     db_recovery_file_dest='<ORACLE_BASE>/flash_recovery_area'
 84     db_recovery_file_dest_size=2G
 85     diagnostic_dest='<ORACLE_BASE>'
 86     dispatchers='(PROTOCOL=TCP) (SERVICE=ORCLXDB)'
 87     open_cursors=300
 88     remote_login_passwordfile='EXCLUSIVE'
 89     undo_tablespace='UNDOTBS1'
 90     # You may want to ensure that control files are created on separate physical
 91     # devices
 92     control_files = (ora_control1, ora_control2)
 93     compatible ='11.2.0'
 94 
 95 
 96     #创建新的 数据库初始化文件: initprod2.ora
 97     [oracle@oracle dbs]$ cat init.ora  | grep -v ^# | grep -v ^$ > initprod2.ora
 98 
 99     #查看创建好的 initprod2.ora 数据库创建初始化文件信息
100     [oracle@oracle dbs]$ view initprod2.ora
101 
102     db_name='ORCL'
103     memory_target=1G
104     processes = 150
105     audit_file_dest='<ORACLE_BASE>/admin/orcl/adump'
106     audit_trail ='db'
107     db_block_size=8192
108     db_domain=''
109     db_recovery_file_dest='<ORACLE_BASE>/flash_recovery_area'
110     db_recovery_file_dest_size=2G
111     diagnostic_dest='<ORACLE_BASE>'
112     dispatchers='(PROTOCOL=TCP) (SERVICE=ORCLXDB)'
113     open_cursors=300
114     remote_login_passwordfile='EXCLUSIVE'
115     undo_tablespace='UNDOTBS1'
116     control_files = (ora_control1, ora_control2)
117     compatible ='11.2.0'
118 
119 
120     #修改该系统参数文件:删除没有必要的数据库参数,保留最终结果
121 
122 
123     [oracle@oracle dbs]$ vi initprod2.ora
124     #配置的结果如下:
125     db_name='PROD2'
126     db_create_file_dest='/u01/app/oracle/oradata'
127     memory_target=1G
128     compatible ='11.2.0'
129     db_domain='PROD2.orcle.com'
130     #保存退出
131 
132 
133     #切换oracle的SID 标识
134 
135     [oracle@oracle ~]$ export ORACLE_SID=prod2
136 
137     #创建密码文件 eg:orapwd file=/u01/app/oracle/product/11.2.0/dbs/orapwmypl password=oracle;
138      [oracle@oracle dbs]$ pwd
139        /u01/app/oracle/product/11.2.0/db_1/dbs
140 
141       [oracle@oracle dbs]$ orapwd file=dbsorapwprod2 entries=30
142      Enter password for SYS:
143 
144 
145 
146 
147     #切换到 sqlplus 登录到oracle 数据库
148     [oracle@oracle ~]$ sqlplus / as sysdba
149 
150     SQL*Plus: Release 11.2.0.3.0 Production on Sat Oct 14 21:23:12 2017
151 
152     Copyright (c) 1982, 2011, Oracle.  All rights reserved.
153 
154     Connected to an idle instance.
155 
156 
157     #创建 spfile 系统参数文件
158 
159     SQL> create spfile from pfile;
160 
161     File created.
162 
163     # 创建好prod2的spfile系统参数文件后,启动数据库到nomount状态
164     SQL> startup nomount;
165     ORACLE instance started.
166 
167     Total System Global Area 1071333376 bytes
168     Fixed Size                  1349732 bytes
169     Variable Size             620758940 bytes
170     Database Buffers          444596224 bytes
171     Redo Buffers                4628480 bytes
172     SQL>
173 
174     #创建数据库
175     SQL> create database prod2;
176     #等待许些时间,会显示创建结果
177 
178     Database created.
179 
180     #查看日志,看数据库是否创建成功
181     [root@oracle ~]# su - oracle
182     [oracle@oracle ~]$cd  /u01/app/oracle/diag/rdbms/prod2/prod2/trace
183     [oracle@oracle trace]$ ls
184     alert_prod2.log      prod2_mmon_5719.trc  prod2_ora_5631.trm  prod2_vktm_5695.trc
185     prod2_mman_5707.trc  prod2_mmon_5719.trm  prod2_ora_5723.trc  prod2_vktm_5695.trm
186     prod2_mman_5707.trm  prod2_ora_5631.trc   prod2_ora_5723.trm
187     [oracle@oracle trace]$
188     [oracle@oracle trace]$ view alert_prod2.log  #查看日志信息
189 
190 
191 
192 #三:用sys用户 跑 命令脚本
193 
194 
195 
196    [oracle@oracle ~]$ sqlplus / as sysdba
197 
198    SQL*Plus: Release 11.2.0.3.0 Production on Sat Oct 14 21:43:26 2017
199 
200    Copyright (c) 1982, 2011, Oracle.  All rights reserved.
201 
202 
203    Connected to:
204    Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
205    With the Partitioning, OLAP, Data Mining and Real Application Testing options
206 
207    # 分别执行下列3条脚本执行命令
208    SQL>   @?/rdbms/admin/catalog.sql
209 
210    SQL>   @?/rdbms/admin/catproc.sql
211 
212    SQL>   @?/rdbms/admin/utlrp.sql
213 
214    #这3个脚本执行时间有点长 ;执行过程的错误可以忽略
215 
216 #四: 用system 用户执行命令脚本
217 
218 #1:如果不知道system 用户的登录密码;则需要用sys用户来修改system登录密码:
219 
220 
221 
222   [oracle@oracle oracle]$ sqlplus / as sysdba
223 
224   SQL*Plus: Release 11.2.0.3.0 Production on Sat Oct 14 21:57:58 2017
225 
226   Copyright (c) 1982, 2011, Oracle.  All rights reserved.
227 
228   Connected to an idle instance.
229   #修改 system 用户密码
230   SQL> alter user system identified by 123456;
231 
232   User altered.
233   #system 用户登录
234   SQL> conn system /123456
235   Connected.
236   SQL>
237 
238 #2:执行数据库创建操作脚本;
239 
240     SQL> conn system /123456
241     Connected.
242 
243     SQL>
244     SQL> @?/sqlplus/admin/pupbld.sql
245 
246 
247 #五:切换到sys 用户 重新启动数据库:
248 
249 
250   SQL> conn / as sysdba;
251   Connected.
252   #启动
253   SQL> startup
254   ORA-01081: cannot start already-running ORACLE - shut it down first
255   #上述错误表示数据库已经处在启动中,重新启动下
256   SQL> startup force
257   ORACLE instance started.
258 
259   Total System Global Area 1071333376 bytes
260   Fixed Size                  1349732 bytes
261   Variable Size             624953244 bytes
262   Database Buffers          440401920 bytes
263   Redo Buffers                4628480 bytes
264   Database mounted.
265   Database opened.
266   SQL>
267 
268 
269 #六:注册数据库实例PROD2到数据库实例列表中
270 
271 
272   [root@oracle admin]# cat /etc/oratab 
273   #
274 
275 
276 
277   # This file is used by ORACLE utilities.  It is created by root.sh
278   # and updated by either Database Configuration Assistant while creating
279   # a database or ASM Configuration Assistant while creating ASM instance.
280 
281   # A colon, ':', is used as the field terminator.  A new line terminates
282   # the entry.  Lines beginning with a pound sign, '#', are comments.
283   #
284   # Entries are of the form:
285   #   $ORACLE_SID:$ORACLE_HOME:<N|Y>:
286   #
287   # The first and second fields are the system identifier and home
288   # directory of the database respectively.  The third filed indicates
289   # to the dbstart utility that the database should , "Y", or should not,
290   # "N", be brought up at system boot time.
291   #
292   # Multiple entries with the same $ORACLE_SID are not allowed.
293   #
294   #
295   orcl:/u01/app/oracle/product/11.2.0/db_1:N
296   [root@oracle admin]# vi /etc/oratab 
297 
298   #
299 
300 
301 
302   # This file is used by ORACLE utilities.  It is created by root.sh
303   # and updated by either Database Configuration Assistant while creating
304   # a database or ASM Configuration Assistant while creating ASM instance.
305 
306   # A colon, ':', is used as the field terminator.  A new line terminates
307   # the entry.  Lines beginning with a pound sign, '#', are comments.
308   #
309   # Entries are of the form:
310   #   $ORACLE_SID:$ORACLE_HOME:<N|Y>:
311   #
312   # The first and second fields are the system identifier and home
313   # directory of the database respectively.  The third filed indicates
314   # to the dbstart utility that the database should , "Y", or should not,
315   # "N", be brought up at system boot time.
316   #
317   # Multiple entries with the same $ORACLE_SID are not allowed.
318   #
319   #
320   orcl:/u01/app/oracle/product/11.2.0/db_1:N
321   PROD2:/u01/app/oracle/product/11.2.0/db_1:N
322 
323   ~
324   ~
325   ~
326   ~
327   ~
328   ~
329   Entering Ex mode.  Type "visual" to go to Normal mode.
330   :wq
331   "/etc/oratab" 25L, 871C written
332   [root@oracle admin]# cat /etc/oratab 
333   #
334 
335 
336 
337   # This file is used by ORACLE utilities.  It is created by root.sh
338   # and updated by either Database Configuration Assistant while creating
339   # a database or ASM Configuration Assistant while creating ASM instance.
340 
341   # A colon, ':', is used as the field terminator.  A new line terminates
342   # the entry.  Lines beginning with a pound sign, '#', are comments.
343   #
344   # Entries are of the form:
345   #   $ORACLE_SID:$ORACLE_HOME:<N|Y>:
346   #
347   # The first and second fields are the system identifier and home
348   # directory of the database respectively.  The third filed indicates
349   # to the dbstart utility that the database should , "Y", or should not,
350   # "N", be brought up at system boot time.
351   #
352   # Multiple entries with the same $ORACLE_SID are not allowed.
353   #
354   #
355   orcl:/u01/app/oracle/product/11.2.0/db_1:N
356   PROD2:/u01/app/oracle/product/11.2.0/db_1:N
357 
358   [root@oracle admin]# 
359 
360 

如果在启动过程中出现下图问题:请参考此文:《ORA-00845 MEMORY_TARGET not supported on this system 的解决

L5WHEL06IOV(NNSN]J8~O_E

对于手工删库请参考此文内容:

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------

  1 #确定并设置数据库结构的大小参数
  2  13 2. Determine and set sizing parameters for database structures
  3  14 #查看DB的以下设置:spfile,db名称,db域,全局数据库名称,快速恢复区域,控制文件,数据文件;
  4  15 2.1 View the following setting of the DB: spfile, db name, db domain, global db name, fast recovery area, control files, data files;
  5  16 #将快速恢复区域大小更改为5G。
  6  17 2.2 Change fast recovery area size to 5G.
  7  18 #将控制文件更改为3个成员,然后更改为2个成员;
  8  19 2.3 Change control files to 3 members, then change back to 2 members;
  9  20 #设置db块大小
 10  21 2.4 Setting the db block size
 11  22 #创建具有16k块大小的100MB表空间TESTDW
 12  23 2.4.1 Create a 100MB tablespace TESTDW with the 16k block size
 13  24 #删除表空间TESTDW,清除内存缓存大小。
 14  25 2.4.2 Drop the tablespace TESTDW, clear the memory cache size.
 15  26 #2.4.3检查记忆操作动作。
 16  27 2.4.3 Check the memory operation movements.
 17  28 #2.5设置以下要求:
 18  29 2.5 Set up for the following requirements:
 19  30 #2.5.1正常工作时间内,OLTP用户数量或并发OLTP用户数约为120个。
 20  31 2.5.1 The number or concurrent OLTP users will be approximately 120 during normal business hours.
 21  32 #2.5.2将在晚上和周末运行的数量或并发批处理程序约为12到 15。
 22  33 2.5.2 The number or concurrent batch processes that will run in the evenings and weekends will approximately 12 to 15..
 23  34 #2.5.3修改或添加以下列出的参数: UTL_FILE_DIR =('/home/oracle','/home/oracle/temp','/home/oracle/scripts')
 24  35 2.5.3 modify or add the following listed parameters:UTL_FILE_DIR=('/home/oracle','/home/oracle/temp','/home/oracle/scripts')
 25  36 #2.6允许10秒的DDL语句等待锁定。检查并重置等待时间。
 26  37 2.6 Allow to 10 sec for a DDL statement to wait for a lock. Check it and reset the wait time.
 27  38 #2.7将参数memory_target更改为200g,重新启动db实例,并从错误方案中恢复。
 28  39 2.7 Change the parameter memory_target to 200g, restart the db instance, and recovery from the error scenario.
 29  40 --[103]--
 30 

-----

  1 --#确定并设置数据库结构的大小参数
  2    2. Determine and set sizing parameters for database structures
  3 --#-------------------------------------------------------------------------------
  4 --1. 文档     l Administrator's Guide -> 2 Creating and Configuring an Oracle Database -> Specifying Initialization Parameters
  5 
  6 
  7 
  8 ----------------------------------------------------------------------------------
  9   ---#查看DB的以下设置:spfile,db名称,db域,全局数据库名称,快速恢复区域,控制文件,数据文件;
 10   2.1 View the following setting of the DB: spfile, db name, db domain, global db name, fast recovery area, control files, data files;
 11 -------------------------------------------------------------------------------
 12 
 13 SQL> show parameter spfile
 14 
 15 NAME                                 TYPE        VALUE
 16 ------------------------------------ ----------- ------------------------------
 17 spfile                               string      /u01/app/oracle/product/11.2.0
 18                                                  /db_1/dbs/spfilePROD1.ora
 19 SQL> show parameter db_name
 20 
 21 NAME                                 TYPE        VALUE
 22 ------------------------------------ ----------- ------------------------------
 23 db_name                              string      PROD1
 24 SQL> show parameter db_domain
 25 
 26 NAME                                 TYPE        VALUE
 27 ------------------------------------ ----------- ------------------------------
 28 db_domain                            string      oracle.com
 29 
 30 
 31 
 32 SQL> select property_name, property_value from database_properties         where property_name ='GLOBAL_DB_NAME';
 33 
 34 PROPERTY_NAME                 PROPERTY_VALUE
 35 ---------------------------------------------------------------------------
 36 GLOBAL_DB_NAME                 PROD1.ORACLE.COM
 37 
 38 SQL> show parameter control_files;
 39 
 40 NAME                                 TYPE        VALUE
 41 ------------------------------------ ----------- ------------------------------
 42 control_files                        string     /u01/app/oracle/oradata/PROD1/control01.ctl, /u01/app/oracle/fast_recovery_area/PROD1/control02.ctl
 43 SQL>
 44 
 45 -------------------------------------------------------------------------------
 46 
 47 --  #将快速恢复区域大小更改为5G。
 48  -- 2.2 Change fast recovery area size to 5G.
 49 ----------------------------------------------------------------------------
 50 SQL> show parameter recovery
 51 
 52 NAME                                 TYPE        VALUE
 53 ------------------------------------ ----------- ------------------------------
 54 db_recovery_file_dest                string      /u01/app/oracle/fast_recovery_area
 55 db_recovery_file_dest_size           big integer 4122M
 56 recovery_parallelism                 integer     0
 57 SQL> alter system set db_recovery_file_dest_size=5G scope=both;
 58 
 59 System altered.
 60 
 61 SQL> select  * from v$recovery_area_usage;
 62 
 63 
 64 
 65 
 66 ------------------------------------------------------------------------------
 67  -- #将控制文件更改为3个成员,然后更改为2个成员;
 68  -- 2.3 Change control files to 3 members, then change back to 2 members;
 69 ----------------------------------------------------------------------------------
 70 ---切换数据库实例:
 71 [oracle@oracle controlfile]$ export ORACLE_SID=PROD2
 72 [oracle@oracle controlfile]$ sqlplus / as sysdba;
 73 
 74 SQL*Plus: Release 11.2.0.3.0 Production on Sat Oct 28 15:33:05 2017
 75 
 76 Copyright (c) 1982, 2011, Oracle.  All rights reserved.
 77 
 78 Connected to an idle instance.
 79  SQL> startup
 80 ORACLE instance started.
 81 
 82 Total System Global Area 1071333376 bytes
 83 Fixed Size                  1349732 bytes
 84 Variable Size             624953244 bytes
 85 Database Buffers          440401920 bytes
 86 Redo Buffers                4628480 bytes
 87 Database mounted.
 88 Database opened.
 89 SQL> ho clear
 90 
 91 
 92 SQL> select name from v$database;
 93 
 94 NAME
 95 ---------
 96 PROD2
 97 
 98 SQL> select status from v$instance;
 99 
100 STATUS
101 ------------
102 OPEN
103 
104 SQL> show parameter control_files
105 
106 NAME                                 TYPE        VALUE
107 ------------------------------------ ----------- ------------------------------
108 control_files                        string      /u01/app/oracle/oradata/PROD2/
109                                                  controlfile/o1_mf_dz7vfyxr_.ct
110                                                  l
111 SQL> shutdown immediate;
112 Database closed.
113 Database dismounted.
114 ORACLE instance shut down.
115 SQL> startup nomount;
116 ORACLE instance started.
117 
118 Total System Global Area 1071333376 bytes
119 Fixed Size                  1349732 bytes
120 Variable Size             624953244 bytes
121 Database Buffers          440401920 bytes
122 Redo Buffers                4628480 bytes
123 SQL> alter system set control_files='/u01/app/oracle/oradata/PROD2/controlfile/o1_mf_dz7vfyxr_.ctl','/u01/app/oracle/oradata/PROD2/controlfile/control02.ctl','/u01/app/oracle/oradata/PROD2/controlfile/control03.ctl' scope=spfile;
124 
125 System altered.
126 
127 SQL> shutdown immediate;
128 ORA-01507: database not mounted
129 
130 
131 ORACLE instance shut down.
132 SQL> !cp -p /u01/app/oracle/oradata/PROD2/controlfile/o1_mf_dz7vfyxr_.ctl  /u01/app/oracle/oradata/PROD2/controlfile/control02.ctl
133 
134 SQL> !cp -p /u01/app/oracle/oradata/PROD2/controlfile/o1_mf_dz7vfyxr_.ctl  /u01/app/oracle/oradata/PROD2/controlfile/control03.ctl
135 
136 SQL> startup
137 ORACLE instance started.
138 
139 Total System Global Area 1071333376 bytes
140 Fixed Size                  1349732 bytes
141 Variable Size             624953244 bytes
142 Database Buffers          440401920 bytes
143 Redo Buffers                4628480 bytes
144 Database mounted.
145 Database opened.
146 SQL> show parameter control_files
147 
148 NAME                                 TYPE        VALUE
149 ------------------------------------ ----------- ------------------------------
150 control_files                        string      /u01/app/oracle/oradata/PROD2/
151                                                  controlfile/o1_mf_dz7vfyxr_.ct
152                                                  l, /u01/app/oracle/oradata/PRO
153                                                  D2/controlfile/control02.ctl,
154                                                  /u01/app/oracle/oradata/PROD2/
155                                                  controlfile/control03.ctl
156 SQL>
157 
158 ----减少 控制文件 为2个
159 
160 SQL> shutdown immediate
161 Database closed.
162 Database dismounted.
163 ORACLE instance shut down.
164 SQL> startup nomount
165 ORACLE instance started.
166 
167 Total System Global Area 1071333376 bytes
168 Fixed Size                  1349732 bytes
169 Variable Size             624953244 bytes
170 Database Buffers          440401920 bytes
171 Redo Buffers                4628480 bytes
172 SQL> alter system set control_files='/u01/app/oracle/oradata/PROD2/controlfile/o1_mf_dz7vfyxr_.ctl','/u01/app/oracle/oradata/PROD2/controlfile/control02.ctl' scope=spfile;
173 
174 System altered.
175 
176 SQL> shutdown immediate;
177 ORA-01507: database not mounted
178 
179 
180 ORACLE instance shut down.
181 
182 SQL> !rm  /u01/app/oracle/oradata/PROD2/controlfile/control03.ctl
183 
184 SQL> startup
185 ORACLE instance started.
186 
187 Total System Global Area 1071333376 bytes
188 Fixed Size                  1349732 bytes
189 Variable Size             624953244 bytes
190 Database Buffers          440401920 bytes
191 Redo Buffers                4628480 bytes
192 Database mounted.
193 Database opened.
194 SQL> show parameter control_files
195 
196 NAME                                 TYPE        VALUE
197 ------------------------------------ ----------- ------------------------------
198 control_files                        string      /u01/app/oracle/oradata/PROD2/
199                                                  controlfile/o1_mf_dz7vfyxr_.ct
200                                                  l, /u01/app/oracle/oradata/PRO
201                                                  D2/controlfile/control02.ctl
202 SQL>
203 
204 
205 
206 
207 
208 ------------------------------------------------------------------------------------
209 --#设置db块大小
210 --2.4 Setting the db block size
211 -------------------------------------------
212 SQL> show parameter db_block_size
213 
214 NAME                                 TYPE        VALUE
215 ------------------------------------ ----------- ------------------------------
216 db_block_size                        integer     8192
217 SQL> show parameter cache_size
218 
219 NAME                                 TYPE        VALUE
220 ------------------------------------ ----------- ------------------------------
221 client_result_cache_size             big integer 0
222 db_16k_cache_size                    big integer 0
223 db_2k_cache_size                     big integer 0
224 db_32k_cache_size                    big integer 0
225 db_4k_cache_size                     big integer 0
226 db_8k_cache_size                     big integer 0
227 db_cache_size                        big integer 0
228 db_flash_cache_size                  big integer 0
229 db_keep_cache_size                   big integer 0
230 db_recycle_cache_size                big integer 0
231 SQL>
232 ________________________________________________
233 -- #创建具有16k块大小的100MB表空间TESTDW
234 -- 2.4.1 Create a 100MB tablespace TESTDW with the 16k block size
235 ----------------------------------------------------------------------------------
236 -- 默认值(DB_BLOCK_SIZE = 8192)
237 SQL> show parameter db_block_size
238 
239 NAME                                 TYPE        VALUE
240 ------------------------------------ ----------- ------------------------------
241 db_block_size                        integer     8192
242 
243 
244 SQL> show parameter cache_size
245 
246 NAME                                 TYPE        VALUE
247 ------------------------------------ ----------- ------------------------------
248 client_result_cache_size             big integer 0
249 db_16k_cache_size                    big integer 0
250 db_2k_cache_size                     big integer 0
251 db_32k_cache_size                    big integer 0
252 db_4k_cache_size                     big integer 0
253 db_8k_cache_size                     big integer 0
254 db_cache_size                        big integer 0
255 db_flash_cache_size                  big integer 0
256 db_keep_cache_size                   big integer 0
257 db_recycle_cache_size                big integer 0
258 SQL> alter system set db_16k_cache_size=100m scope=both;
259 
260 System altered.
261 
262 SQL> show parameter cache_size
263 
264 NAME                                 TYPE        VALUE
265 ------------------------------------ ----------- ------------------------------
266 client_result_cache_size             big integer 0
267 db_16k_cache_size                    big integer 100M
268 db_2k_cache_size                     big integer 0
269 db_32k_cache_size                    big integer 0
270 db_4k_cache_size                     big integer 0
271 db_8k_cache_size                     big integer 0
272 db_cache_size                        big integer 0
273 db_flash_cache_size                  big integer 0
274 db_keep_cache_size                   big integer 0
275 db_recycle_cache_size                big integer 0
276 SQL> create tablespace testdw datafile '/u01/app/oracle/oradata/PROD2/datafile/testdw01.dbf' size 100m blocksize 16k ;
277 
278 Tablespace created.
279 
280 SQL> select tablespace_name ,block_size from dba_tablespaces;
281 
282 TABLESPACE_NAME                BLOCK_SIZE
283 ------------------------------ ----------
284 SYSTEM                               8192
285 SYSAUX                               8192
286 SYS_UNDOTS                           8192
287 TESTDW                              16384
288 
289 
290 
291 
292 ----------------------------------------------------------------------------------
293 --  #删除表空间TESTDW,清除内存缓存大小。 
294 --2.4.2 Drop the tablespace TESTDW, clear the memory cache size.
295 ---------------------------------------------------------------------------------
296 -- 取消非默认blocksize
297 SQL> drop tablespace testdw including contents and datafiles;
298 
299 Tablespace dropped.
300 
301 SQL> alter system set db_16k_cache_size=0 scope=memory;
302 
303 System altered.
304 
305 SQL> alter system reset db_16k_cache_size scope=spfile;
306 
307 System altered.
308 
309 
310 
311 
312 




  1  -------------------------------------------------------------------------------
  2    -- #2.4.3检查记忆操作动作。
  3    -- 2.4.3 Check the memory operation movements.
  4    -------------------------------------------------------------------------------
  5 
  6 
  7 
  8 
  9 
 10  ------------------------------------------------------------------------------
 11   --#2.5设置以下要求:
 12   --2.5 Set up for the following requirements:
 13   --#2.5.1正常工作时间内,OLTP用户数量或并发OLTP用户数约为120个。
 14   --2.5.1 The number or concurrent OLTP users will be approximately 120 during normal business hours.
 15   -----------------------------------------------------------------------------------------
 16   --PROCESSES(连接数):限定可同时连接到oracle 的操作系统用户进程的最大数量。Oracle processes    包括Server processes, Background processes 及其衍生的Slave processes。
 17 
 18   SQL> show parameter processes
 19 
 20   NAME                                 TYPE        VALUE
 21   ------------------------------------ ----------- ------------------------------
 22   aq_tm_processes                      integer     1
 23   db_writer_processes                  integer     1
 24   gcs_server_processes                 integer     0
 25   global_txn_processes                 integer     1
 26   job_queue_processes                  integer     1000
 27   log_archive_max_processes            integer     4
 28   processes                            integer     360
 29   SQL> alter system set processes=250 scope=spfile;
 30 
 31   System altered.
 32 
 33   SQL> startup force;
 34   ORACLE instance started.
 35 
 36   Total System Global Area 1071333376 bytes
 37   Fixed Size                  1349732 bytes
 38   Variable Size             624953244 bytes
 39   Database Buffers          440401920 bytes
 40   Redo Buffers                4628480 bytes
 41   Database mounted.
 42   Database opened.
 43   SQL> show parameter processes
 44 
 45   NAME                                 TYPE        VALUE
 46   ------------------------------------ ----------- ------------------------------
 47   aq_tm_processes                      integer     1
 48   db_writer_processes                  integer     1
 49   gcs_server_processes                 integer     0
 50   global_txn_processes                 integer     1
 51   job_queue_processes                  integer     1000
 52   log_archive_max_processes            integer     4
 53   processes                            integer     250
 54   SQL>
 55 
 56   ---JOB_QUEUE_PROCESSES:限定DB Instance 的作业队列进程数量。Job Queue Processes 属于Background processes,由Oracle Scheduler 启动和停止。Job Queue Processes 包括 job coordinator process (CJQ0) 和job queue slave processes (Jnnn)。
 57 
 58   SQL> show parameter job_queue_processes
 59 
 60   NAME                                 TYPE        VALUE
 61   ------------------------------------ ----------- ------------------------------
 62   job_queue_processes                  integer     1000
 63 
 64   SQL> alter system set job_queue_processes=200 scope=both;
 65 
 66   System altered.
 67   SQL> show parameter job_queue_processes
 68 
 69   NAME                                 TYPE        VALUE
 70   ------------------------------------ ----------- ------------------------------
 71   job_queue_processes                  integer     200
 72   SQL>
 73 
 74   --SESSIONS:指定Instance 中能够同时存在的sessions 数量,即能同时登陆到数据库的并发用户数。
 75 
 76 -- 默认值: (1.5 * PROCESSES) + 22  
 77 SQL> show parameter session;
 78 
 79 NAME                                 TYPE        VALUE
 80 ------------------------------------ ----------- ------------------------------
 81 java_max_sessionspace_size           integer     0
 82 java_soft_sessionspace_limit         integer     0
 83 license_max_sessions                 integer     0
 84 license_sessions_warning             integer     0
 85 session_cached_cursors               integer     50
 86 session_max_open_files               integer     10
 87 sessions                             integer     400
 88 shared_server_sessions               integer
 89 SQL> alter system set sessions=500 scope=spfile;
 90 
 91 System altered.
 92 
 93 SQL> shutdown immediate;
 94 Database closed.
 95 Database dismounted.
 96 ORACLE instance shut down.
 97 SQL> startup
 98 ORACLE instance started.
 99 
100 Total System Global Area 1071333376 bytes
101 Fixed Size                  1349732 bytes
102 Variable Size             641730460 bytes
103 Database Buffers          423624704 bytes
104 Redo Buffers                4628480 bytes
105 Database mounted.
106 Database opened.
107 SQL> show parameter session;
108 
109 NAME                                 TYPE        VALUE
110 ------------------------------------ ----------- ------------------------------
111 java_max_sessionspace_size           integer     0
112 java_soft_sessionspace_limit         integer     0
113 license_max_sessions                 integer     0
114 license_sessions_warning             integer     0
115 session_cached_cursors               integer     50
116 session_max_open_files               integer     10
117 sessions                             integer     500
118 shared_server_sessions               integer
119 SQL>
120     --------------------------------------------------------------------------------------------
121  -- #2.5.2将在晚上和周末运行的数量或并发批处理程序约为12到 15。
122 --  2.5.2 The number or concurrent batch processes that will run in the evenings and weekends will approximately 12 to 15..
123 -------------------------------------------------------------
124 
125 ---------------------------------------------------------------
126   --#2.5.3修改或添加以下列出的参数: UTL_FILE_DIR =(   '/home/oracle','/home/oracle/temp','/home/oracle/scripts'  )
127  -- 2.5.3 modify or add the following listed parameters:UTL_FILE_DIR=('/home/oracle','/home/oracle/temp','/home/oracle/scripts')
128 -----------------------------------------------------------------------
129 SQL> show parameter utl_file_dir
130 
131 NAME                                 TYPE        VALUE
132 ------------------------------------ ----------- ------------------------------
133 utl_file_dir                         string
134 SQL> alter system set utl_file_dir='/home/oracle','/home/oracle/temp','/home/oracle/scripts' scope=spfile;
135 
136 System altered.
137 
138 SQL> shutdown immediate;
139 Database closed.
140 Database dismounted.
141 ORACLE instance shut down.
142 SQL> startup
143 ORACLE instance started.
144 
145 Total System Global Area 1071333376 bytes
146 Fixed Size                  1349732 bytes
147 Variable Size             641730460 bytes
148 Database Buffers          423624704 bytes
149 Redo Buffers                4628480 bytes
150 Database mounted.
151 Database opened.
152 SQL> show parameter utl_file_dir
153 
154 NAME                                 TYPE        VALUE
155 ------------------------------------ ----------- ------------------------------
156 utl_file_dir                         string      /home/oracle, /home/oracle/tem
157                                                  p, /home/oracle/scripts
158 SQL>
159 -----------------------------------------------------------------------------
160 --  #2.6允许10秒的DDL语句等待锁定。检查并重置等待时间。
161 ----.6 Allow to 10 sec for a DDL statement to wait for a lock. Check it and reset the wait time.
162 ------------------------------------------------------------------------------
163 
164 SQL> show parameter ddl_lock_timeout
165 
166 NAME                                 TYPE        VALUE
167 ------------------------------------ ----------- ------------------------------
168 ddl_lock_timeout                     integer     0
169 SQL> alter session set ddl_lock_timeout=10;
170 
171 Session altered.
172 
173 
174 
175 
176 SQL> create table test(coll varchar2(10));
177 
178 Table created.
179 
180 SQL> insert into test values('x');
181 
182 1 row created.
183 
184 SQL> insert into test values('y');
185 
186 1 row created.
187 
188 SQL> set timing on;
189 SQL> drop table test;
190 
191 Table dropped.
192 
193 Elapsed: 00:00:00.13
194 SQL> rollback;
195 
196 Rollback complete.
197 
198 Elapsed: 00:00:00.02
199 SQL> drop table test;
200 drop table test
201            *
202 ERROR at line 1:
203 ORA-00942: table or view does not exist
204 
205 
206 Elapsed: 00:00:00.00
207 SQL> alter session set ddl_lock_timeout=0;
208 
209 Session altered.
210 
211 Elapsed: 00:00:00.00
212 SQL> set timing off
213 SQL> show parameter ddl_lock_timeout
214 
215 NAME                                 TYPE        VALUE
216 ------------------------------------ ----------- ------------------------------
217 ddl_lock_timeout
218 
219 
220 
221 -----------------------------------------------------------------------------
222 --  #2.7将参数memory_target更改为200g,重新启动db实例,并从错误方案中恢复。
223 --- 2.7 Change the parameter memory_target to 200g, restart the db instance, and recovery from the error scenario.
224 
225 -----------------------------------------------------------------------------
226 SQL> show parameter memory_target
227 
228 NAME                                 TYPE        VALUE
229 ------------------------------------ ----------- ------------------------------
230 memory_target                        big integer 1G
231 SQL> alter system set memory_target =200g scope=spfile;
232 
233 System altered.
234 
235 SQL> shutdown immediate;
236 Database closed.
237 Database dismounted.
238 ORACLE instance shut down.
239 SQL> startup
240 ORA-00845: MEMORY_TARGET not supported on this system
241 SQL> create pfile='/tmp/initPROD2.ora' from spfile;
242 
243 File created.
244 
245 SQL> quit
246 Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
247 With the Partitioning, OLAP, Data Mining and Real Application Testing options
248 [oracle@oracle ~]$ vi /tmp/initPROD2.ora
249 
250 PROD2.__db_cache_size=423624704
251 PROD2.__java_pool_size=4194304
252 PROD2.__large_pool_size=4194304
253 PROD2.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
254 PROD2.__pga_aggregate_target=432013312
255 PROD2.__sga_target=641728512
256 PROD2.__shared_io_pool_size=0
257 PROD2.__shared_pool_size=201326592
258 PROD2.__streams_pool_size=0
259 *.compatible='11.2.0'
260 *.control_files='/u01/app/oracle/oradata/PROD2/controlfile/o1_mf_dz7vfyxr_.ctl','/u01/app/oracle/oradata/PROD2/controlfile/control02.ctl'
261 *.db_create_file_dest='/u01/app/oracle/oradata'
262 *.db_domain='PROD2.oracle.com'
263 *.db_name='PROD2'
264 *.job_queue_processes=200
265 *.memory_target=500M
266 *.processes=250
267 *.sessions=500
268 *.utl_file_dir='/home/oracle','/home/oracle/temp','/home/oracle/scripts'
269 ~
270 ~
271 
272 "/tmp/initPROD2.ora" 19L, 735C written
273 [oracle@oracle ~]$ system /  as system
274 -bash: system: command not found
275 [oracle@oracle ~]$ sqlplus / as sysdba;
276 
277 SQL*Plus: Release 11.2.0.3.0 Production on Mon Oct 30 22:24:57 2017
278 
279 Copyright (c) 1982, 2011, Oracle.  All rights reserved.
280 
281 Connected to an idle instance.
282 
283 SQL> startup mount pfile='/tmp/initPROD2.ora'
284 ;
285 ORACLE instance started.
286 
287 Total System Global Area  523108352 bytes
288 Fixed Size                  1346052 bytes
289 Variable Size             322962940 bytes
290 Database Buffers          192937984 bytes
291 Redo Buffers                5861376 bytes
292 Database mounted.
293 SQL> SP2-0223: No lines in SQL buffer.
294 SQL> create spfile from pfile='/tmp/initPROD2.ora';
295 
296 File created.
297 
298 SQL> shutdown immediate;
299 ORA-01109: database not open
300 
301 
302 Database dismounted.
303 ORACLE instance shut down.
304 SQL> startup
305 ORACLE instance started.
306 
307 Total System Global Area  523108352 bytes
308 Fixed Size                  1346052 bytes
309 Variable Size             322962940 bytes
310 Database Buffers          192937984 bytes
311 Redo Buffers                5861376 bytes
312 Database mounted.
313 Database opened.
314 SQL> show parameter memory_target
315 
316 NAME                                 TYPE        VALUE
317 ------------------------------------ ----------- ------------------------------
318 memory_target                        big integer 500M
319 SQL>
320 SQL>
321 
322 
323 
324 
325 
326 -----------------------------------------------------------------------------
327  --[103]--



  1 #3.创建和管理临时,永久和撤销表空间
  2 # 3. Create and manage temporary, permanent, and undo tablespaces
  3 # #3.1创建一个包含两个临时表空间的临时表空间组,以支持批处理 创建大型索引和分析表格,使用以下规格:
  4 # 3.1 Create a temporary tablespace group that contains two temporary tablespaces to support batch processing, the creation of large indexes,and analyzing tables, Use the following specifications:
  5 # #3.1.1临时表空间组名为TEMP_GRP,包含临时表空间TEMP和TEMP2。
  6 # 3.1.1 Temporary tablespace group named TEMP_GRP containing temporary tablespaces TEMP and TEMP2.
  7 # #3.1.2使TEMP_GRP成为所有新用户的默认临时表空间。
  8 # 3.1.2 Make TEMP_GRP the default temporary tablespace for all new users.
  9 # #3.1.3将默认临时表空间更改为TEMP。
 10 # 3.1.3 Change back the default temporary tablespace to TEMP.
 11 # #3.1.4将临时表空间TEMP扩展到200MB,然后将其大小减小到100MB。
 12 # 3.1.4 Extend the temporary tablespace TEMP to 200MB, then cut its size to 100MB.
 13 # #3.1.5使用语句检查临时用法:select a.object_id from dba_objects a,dba_objects b order by object_id; 
 14 # 3.1.5 Check the temporary usage with the statement: select a.object_id from dba_objects a,dba_objects b order by object_id;
 15 # #3.1.6将临时表空间剪切到最小大小。
 16 # 3.1.6 Cut the temporary tablespace to the minimum size.
 17 # #3.2创建以下永久表空间来存储样本测试数据。使用以下规格:
 18 # 3.2 Create the following permanent tablespace to store sample test data. Use the following specifications:
 19 # #3.2.1表空间TEST01,初始数据文件大小为100MB
 20 # 3.2.1 Tablespace TEST01, initial datafile size 100MB
 21 # #3.2.2表空间TEST02,初始数据文件大小为100MB,初始扩展大小为1MB,下一个扩展区大小为1MB)
 22 # 3.2.2 Tablespace TEST02, initial datafile size 100MB, Initial extent size of 1MB, next extent size of 1MB)
 23 # #3.2.3表空间TEST03,初始数据文件大小为100MB,带freelist的段管理 
 24 # 3.2.3 Tablespace TEST03, initial datafile size 100MB, segment management with freelist
 25 # #3.2.4表空间TEST04,初始数据文件大小为100MB,下一个扩展大小为100MB,最大大小为2G
 26 # 3.2.4 Tablespace TEST04, initial datafile size 100MB, next extent size of 100MB with maximum size of 2G
 27 # #3.2.5具有OMF位置的表空间TEST05
 28 # 3.2.5 Tablespace TEST05 with the OMF location
 29 # #3.2.6具有OMF位置的表空间TEST06,16k块大小。
 30 # 3.2.6 Tablespace TEST06 with the OMF location, 16k blocksize.
 31 # #3.2.7表空间TEST07 for bigfile,初始数据文件大小为100MB,文件预期增长到4TB
 32 # 3.2.7 Tablespace TEST07 for bigfile, initial datafile size 100MB with the file expected to grow to 4TB
 33 # #3.2.8检查上述表空间:名称,数据文件名,块大小,extent_management,段空间管理,分配类型,bigfile
 34 # 3.2.8 Check the above tablespaces: name, datafile name, block size, extent_management, segment space management, allocation type, bigfile
 35 # #3.2.9在表空间TEST01中创建2个表:TEST_PCTFREE_0和TEST_PCTFREE_50。将dba_objects记录插入到它们中;检查2个表:记录数,块,平均空间和空格。
 36 # 3.2.9 Create 2 tables in tablespace TEST01: TEST_PCTFREE_0 and TEST_PCTFREE_50.Insert the dba_objects records to both of them; Check the 2 tables: number of records, blocks, avg space and pct free.
 37 # #3.2.10删除上述表空间并清除环境设置(缓存大小,OMF路径)
 38 # 3.2.10 Drop the above tablespaces and clear the environment settings (cache size, OMF path)
 39 # #3.3在数据库中设置自动撤销管理,支持以下要求:
 40 # 3.3 Set up automatic undo management in the database to support the following requirements:
 41 # #3.3.1避免ORA-01555对于平均5个小时的查询,快照太旧错误。
 42 # 3.3.1 Avoid ORA-01555 Snapshot too old errors for queries running up to 5 hours on average.
 43 # #3.3.2检查不同状态的UNDO的使用情况。
 44 # 3.3.2 Check the usage of UNDO with different status.
 45 # #3.3.3将UNDO更改为tablespace undotbs2,然后更改为tablespace undotbs1。
 46 # 3.3.3 Change the UNDO to tablespace undotbs2, then change back to tablespace undotbs1.
 47 # #3.3.4向表空间undotbs1添加一个新的datafile undotbs02.dbf(size = 100MB,扩展大小为10MB),然后删除新的撤消数据文件。
 48 # 3.3.4 Add a new datafile undotbs02.dbf (size=100MB, extent size of 10MB) to tablespace undotbs1, then drop the new undo datafile.
 49 # #3.4检查所有表空间(永久,临时)及其数据文件:文件#,文件名,表空间名称
 50 # 3.4 Check all the tablespaces (permanent, temporary) and their datafiles: file#,file name,tablespace name
 51 # --[104]--

------103 表空间建立:

  1 #3.创建和管理临时,永久和撤销表空间
  2 # 3. Create and manage temporary, permanent, and undo tablespaces

  1: 在文档位置:

      Concepts -> 12 Logical Storage Structures
   Administrator's Guide -> 14 Managing Tablespaces
    Administrator's Guide -> 16 Managing Undo

图解位置:

imageimage

imageimage


























--------------------------------------------------------------------------


posted @ 2017-09-23 00:27  一品堂.技术学习笔记  阅读(1300)  评论(0编辑  收藏  举报