正文
OCM 学习练习题目
提示：扫一扫查出行【扫一扫了解最新限行尾号】
复制提示
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" 命令执行后，调用出图形界面：执行下一步操作
4) Select "Create database" -> Click "Next" 选择创建数据库，然后在执行下一步按钮
5) Select "Custom Database" -> Click "Next" 此处按照要求选择相对应选项。此处我选择的是 第一个选项
其他选项：
General Purpose or Transaction Processing
Data Warehouse
6) Input the "Global Database Name" and SID 输入数据库名和数据库实例名
Global Database Name = "PROD1.oracle.com"
SID = "PROD1"
Click "Next"
7) Deselect "Configure Enterprise Manager" -> Click "Next" ；去掉“配置企业管理器”的选择
注意：如果选择此项，需要先启动监听(Listener)
8) Select "Use the Same Administrative Password for All Account"
Password = "xxxxxx" (例如：oracle)
Confirm Password = "xxxxxx" (例如：oracle)
Click "Next"
把所有用户的密码都设置一样的选择。 考试的时候最后都设置一样的 除掉默认的oracle名称其他的密码；
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"
10) Keep the default settings of Flash/Fast Recovery Area -> Deselect "Enable Archiving" -> Click "Next"
保留Flash（闪存） /快速恢复区域的默认设置 - >取消选择“启用归档” - >单击“下一步”
11) Select "Sample Schema" and keep the default settings of other components -> Click "Next"
选择安装默认组件：完成时间？
取消安装全部组件：完成时间？
12) Select the character set, keep other options by default
Click tab "Character Sets"
Click "Use Unicode (AL32UTF8)
Click "Next"
13) In the window "Database Storage" -> Click "Next"
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)
创建数据库实例并生成数据库生成脚本
15) Confirm the Summary -> Click "OK"
16) Confirm to create the script of DB - > Click "OK"
17) Finish the installation -> Click "Exit"
测试下安装结果：
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 的解决》
对于手工删库请参考此文内容：
- 四：删除数据库实例。即：删除数据库
- 1: 手工删除数据库
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
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（管理撤销）
图解位置：
2. TABLESPACE 分类
locally managed tablespace （本地管理表空间）
- automatic segment space mangement（自动段空间管理）
- manual segment spance mamagement（手动段空间管理）
dictionary managed tablespace (字典管理表空间)
LOCALLY MANAGED TABLESPACE （本地管理的表空间） DICTIONARY MANAGED TABLESPACE（字典管理表空间） AUTOMATIC SEGMENT SPACE MANAGEMENT(ASSM)
（自动段空间管理） MANUAL SEGMENT
SPACE MANAGEMENT
（手动段空间管理） The EXTENTS are managed through Data Dictionary （区段是通过数据字典管理的） Use Bitmaps（使用位图） Use Freelists（使用免费的列表） Use Dict.（使用字典类型） => SYS.EUT$ , SYS.FET$
1 #1)自动段空间管理(ASSM)
2 1) Automatic Segment Space Management (ASSM)
3 #ASSM更有效，是永久的、本地管理的表空间的缺省值。ASSM方法使用位图来管理空间。位图提供如下优势:
4 ASSM is more efficient and is the default for permanent, locally managed tablespaces.The ASSM method uses bitmaps to manage space. Bitmaps provide the following advantages:
5 #l简化管理:ASSM避免了需要手动确定正确设置多少存储参数。只有一个关键的SQL参数控制空间分配:PCTFREE(默认值为10%)。该参数指定在一个块中预留的空间百分比用于将来的更新(“数据块中的空闲空间百分比”)。
6 l 简化管理: ASSM avoids the need to manually determine correct settings for many storage parameters. Only one crucial SQL parameter controls space allocation: PCTFREE (default=10%).This parameter specifies the percentage of space to be reserved in a block for future updates("Percentage of Free Space in Data Blocks").
7 #l提升并发性能:多个事务,可以搜索单独列表的免费数据块,从而减少争用和等待。对于许多标准的工作负载，使用ASSM的应用程序性能要优于使用MSSM的调优应用程序的性能。例如:如果“缓冲区繁忙等待”事件由段头的争用引起，那么它很可能是自由列表争用。如果可能的话，从手动空间管理切换到自动分段空间管理(ASSM)。
8 l 提升并发性能: Multiple transactions can search separate lists of free data blocks, thereby reducing contention and waits. For many standard workloads, application performance with ASSM is better than the performance of a well-tuned application that uses MSSM. For example: If the "buffer busy waits" event caused by contention on the segment header, then it is most likely free list contention. If possible, switch from manual space management to automatic segment-space management (ASSM).
9 #l动态关联:动态空间的亲和力在Oracle实例真正的应用程序集群(Oracle RAC)环境下面的图形的概念表示bitmap-managed存储。header中的1指的是使用的空间，而0指的是空闲空间。
10 l 动态关联：Dynamic affinity of space to instances in an Oracle Real Application Clusters(Oracle RAC) environment The following graphic is a conceptual representation of bitmap-managed storage. A 1 in the header refers to used space, whereas a 0 refers to free space.
1
2 ASSM 的使用限制：
3 l Only for a permanent, locally managed tablespace. #只用于永久的本地管理的表空间。
4 l Cannot specify for the SYSTEM tablespace. #不能为系统表空间指定。
5
1 #2)手动段空间管理(MSSM)
2 #2) Manual Segment Space Management (MSSM)
3 #除了PCTFREE之外，MSSM还需要使用SQL参数来控制空间分配，如pct二手车、frelist和FREELIST组。pct二手(默认=40%)设置当前使用的数据库中必须存在的空间百分比，以便将其放在空闲列表中。例如，如果您在CREATE TABLE语句中设置pct40，那么您就不能将行插入到该段中的一个块中，直到使用了不到40%的块空间。手动管理段空间可能是复杂的。由于难以对空间分配参数进行微调，Oracle强烈建议使用ASSM。在ASSM中，PCTFREE决定是否将一个新行插入到一个块中，但是它不使用空闲列表，并且忽略了pctuse
4 #In addition to PCTFREE, MSSM requires you to control space allocation with SQL parameters such as PCTUSED, FREELISTS, and FREELIST GROUPS. PCTUSED (default=40%) sets the percentage of space that must exist in a currently used block for the database to put it on the free list. For example, if you set PCTUSED to 40 in a CREATE TABLE statement, then you cannot insert rows into a block in the segment until less than 40% of the block space is used. Managing segment space manually can be complex. Because of the difficulty of fine-tuning space allocation parameters, Oracle strongly recommends ASSM. In ASSM, PCTFREE determines whether a new row can be inserted into a block, but it does not use free lists and ignores PCTUSED.
5 #3)Dictionary-Managed表空间
6 #3) Dictionary-Managed Tablespaces
7 #一个字典管理的表空间使用数据字典来管理它的区段。Oracle数据库在数据字典中更新数据表，只要在一定程度上分配或释放数据，就可以重用。数据库在后台执行的SQL为数据库对象获取空间的SQL是递归SQL。频繁使用递归SQL可能会对性能产生负面影响，因为对数据字典的更新必须被序列化。本地管理的表空间是默认的，避免了这种性能问题。
8 #A dictionary-managed tablespace uses the data dictionary to manage its extents. Oracle Database updates tables in the data dictionary whenever an extent is allocated or freed for reuse. The SQL that the database executes in the background to obtain space for database objects is recursive SQL. Frequent use of recursive SQL can have a negative impact on performance because updates to the data dictionary must be serialized. Locally managed tablespaces, which are the default, avoid this performance problem.
1 # #3.1创建一个包含两个临时表空间的临时表空间组，以支持批处理 创建大型索引和分析表格，使用以下规格：
2 # 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:
3 # #3.1.1临时表空间组名为TEMP_GRP，包含临时表空间TEMP和TEMP2。
4 # 3.1.1 Temporary tablespace group named TEMP_GRP containing temporary tablespaces TEMP and TEMP2.
5 # #3.1.2使TEMP_GRP成为所有新用户的默认临时表空间。
6 # 3.1.2 Make TEMP_GRP the default temporary tablespace for all new users.
7 # #3.1.3将默认临时表空间更改为TEMP。
8 # 3.1.3 Change back the default temporary tablespace to TEMP.
9 # #3.1.4将临时表空间TEMP扩展到200MB，然后将其大小减小到100MB。
10 # 3.1.4 Extend the temporary tablespace TEMP to 200MB, then cut its size to 100MB.
11 # #3.1.5使用语句检查临时用法：select a.object_id from dba_objects a,dba_objects b order by object_id;
12 # 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;
13 # #3.1.6将临时表空间剪切到最小大小。
14 # 3.1.6 Cut the temporary tablespace to the minimum size.
------建立permanent TABLESPACE
1 #3. 建立permanent TABLESPACE
2 # 1) 建立普通表空间
3 #-- 默认设定 segment space management auto（段空间管理自动） + extent management local autoallocate（范围管理局部自动分配）
4 #自动分配:由最小区段大小为64K的系统管理
5 #-- AUTOALLOCATE: managed by system with a minimum extent size of 64K
6 -------------------------------------------------------------------------------------------------------------------
7 -- #3.2创建以下永久表空间来存储样本测试数据。使用以下规格：
8 -- 3.2 Create the following permanent tablespace to store sample test data. Use the following specifications:
9 ------------------------------------------------------------------------------------------------------------------
10 -- #3.2.1表空间TEST01，初始数据文件大小为100MB
11 -- 3.2.1 Tablespace TEST01, initial datafile size 100MB
12 SQL> create tablespace TEST01 datafile '/u01/app/oracle/oradata/PROD1/test01.dbf' size 100M;
13
14 Tablespace created.
15
16 SQL> select file_name,tablespace_name from dba_data_files;
17
18 FILE_NAME TABLESPACE_NAME
19 ----------------------------------------------------------------------------
20 /u01/app/oracle/oradata/PROD1/users01.dbf USERS
21 /u01/app/oracle/oradata/PROD1/undotbs01.dbf UNDOTBS1
22 /u01/app/oracle/oradata/PROD1/sysaux01.dbf SYSAUX
23 /u01/app/oracle/oradata/PROD1/system01.dbf SYSTEM
24 /u01/app/oracle/oradata/PROD1/example01.dbf EXAMPLE
25 /u01/app/oracle/oradata/PROD1/test01.dbf TEST01
26
27 6 rows selected.
28
29 -- #3.2.2表空间TEST02，初始数据文件大小为100MB，初始扩展大小为1MB，下一个扩展区大小为1MB）
30 --- 3.2.2 Tablespace TEST02, initial datafile size 100MB, Initial extent size of 1MB, next extent size of 1MB)
31
32
33 SQL> create tablespace TEST02 datafile '/u01/app/oracle/oradata/PROD1/test02.dbf' size 100m segment space management auto extent management local uniform size 1m;
34
35 Tablespace created.
36
37 SQL> select file_name ,tablespace_name from dba_data_files;
38
39 FILE_NAME TABLESPACE_NAME
40 --------------------------------------------------------------------------
41 /u01/app/oracle/oradata/PROD1/users01.dbf USERS
42 /u01/app/oracle/oradata/PROD1/undotbs01.dbf UNDOTBS1
43 /u01/app/oracle/oradata/PROD1/sysaux01.dbf SYSAUX
44 /u01/app/oracle/oradata/PROD1/system01.dbf SYSTEM
45 /u01/app/oracle/oradata/PROD1/example01.dbf EXAMPLE
46 /u01/app/oracle/oradata/PROD1/test01.dbf TEST01
47 /u01/app/oracle/oradata/PROD1/test02.dbf TEST02
48
49 7 rows selected.
50
51 SQL>
52
53 ---- #3.2.3表空间TEST03，初始数据文件大小为100MB，带freelist的段管理
54 --- 3.2.3 Tablespace TEST03, initial datafile size 100MB, segment management with freelist
55
56 SQL> create tablespace TEST03 datafile '/u01/app/oracle/oradata/PROD1/test03.dbf' size 100m segment space management manual extent management local autoallocate;
57
58 Tablespace created.
59
60 SQL> select tablespace_name,file_name from dba_data_files;
61
62 TABLESPACE_NAME FILE_NAME
63 ------------------------------ -------------------------------------------------------------
64 USERS /u01/app/oracle/oradata/PROD1/users01.dbf
65 UNDOTBS1 /u01/app/oracle/oradata/PROD1/undotbs01.dbf
66 SYSAUX /u01/app/oracle/oradata/PROD1/sysaux01.dbf
67 SYSTEM /u01/app/oracle/oradata/PROD1/system01.dbf
68 EXAMPLE /u01/app/oracle/oradata/PROD1/example01.dbf
69 TEST01 /u01/app/oracle/oradata/PROD1/test01.dbf
70 TEST02 /u01/app/oracle/oradata/PROD1/test02.dbf
71 TEST03 /u01/app/oracle/oradata/PROD1/test03.dbf
72
73 8 rows selected.
74
75 SQL>
76
77 ---- #3.2.4表空间TEST04，初始数据文件大小为100MB，下一个扩展大小为100MB，最大大小为2G
78 ---- 3.2.4 Tablespace TEST04, initial datafile size 100MB, next extent size of 100MB with maximum size of 2G
79
80
81 SQL> create tablespace TEST04 datafile '/u01/app/oracle/oradata/PROD1/test04.dbf' size 100m autoextend on next 100m maxsize 2G;
82
83 Tablespace created.
84
85 SQL> select tablespace_name ,file_name from dba_data_files;
86
87 TABLESPACE_NAME FILE_NAME
88 ------------------------------------------------------------------------------------------------
89 USERS /u01/app/oracle/oradata/PROD1/users01.dbf
90 UNDOTBS1 /u01/app/oracle/oradata/PROD1/undotbs01.dbf
91 SYSAUX /u01/app/oracle/oradata/PROD1/sysaux01.dbf
92 SYSTEM /u01/app/oracle/oradata/PROD1/system01.dbf
93 EXAMPLE /u01/app/oracle/oradata/PROD1/example01.dbf
94 TEST01 /u01/app/oracle/oradata/PROD1/test01.dbf
95 TEST02 /u01/app/oracle/oradata/PROD1/test02.dbf
96 TEST03 /u01/app/oracle/oradata/PROD1/test03.dbf
97 TEST04 /u01/app/oracle/oradata/PROD1/test04.dbf
98
99 9 rows selected.
100
101 SQL>
102 --检查TBS属性
103 SQL> select tablespace_name ,block_size ,extent_management,segment_space_management ,allocation_type,bigfile from dba_tablespaces;
104
105 TABLESPACE_NAME BLOCK_SIZE EXTENT_MAN FR ALLOCATIO BIG
106 ------------------------------ ---------- ---------- ------ --------- ---
107 SYSTEM 8192 LOCAL MANUAL SYSTEM NO
108 SYSAUX 8192 LOCAL AUTO SYSTEM NO
109 UNDOTBS1 8192 LOCAL MANUAL SYSTEM NO
110 TEMP 8192 LOCAL MANUAL UNIFORM NO
111 USERS 8192 LOCAL AUTO SYSTEM NO
112 EXAMPLE 8192 LOCAL AUTO SYSTEM NO
113 TEST01 8192 LOCAL AUTO SYSTEM NO
114 TEST02 8192 LOCAL AUTO UNIFORM NO
115 TEST03 8192 LOCAL MANUAL SYSTEM NO
116 TEST04 8192 LOCAL AUTO SYSTEM NO
117
118 10 rows selected.
119
1--3.2.9在表空间TEST01中创建2个表：TEST_PCTFREE_0和TEST_PCTFREE_50。将dba_objects记录插入到它们中;检查2个表：记录数，块，平均空间和空格。
--- 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. 2 --检查两个表，其中有不同的PCTFREE值。
3 --- Check two tables with different values for PCTFREE.
4 create table test_pctfree_0 tablespace test01 pctfree 0 as select * from dba_objects;
5 create table test_pctfree_50 tablespace test01 pctfree 50 as select * from dba_objects;
6 -- Statistics tables 统计数据表 analyze 分析 compute statistics 计算统计数据;
7 analyze table test_pctfree_0 compute statistics;
8 analyze table test_pctfree_50 compute statistics;
9 -- Check as different values for PCTFREE can cause different occupations for a table
10
11 SQL> select table_name, num_rows, blocks, avg_space, pct_free from dba_tables where table_name in ('TEST_PCTFREE_0','TEST_PCTFREE_50');
12
13 TABLE_NAME NUM_ROWS BLOCKS AVG_SPACE PCT_FREE
14 ------------------------------ ---------- ---------- ---------- ----------
15 TEST_PCTFREE_0 75185 987 65 0
16 TEST_PCTFREE_50 50
17
18 SQL>
19
20
21
1
2 ---# #3.2.5具有OMF位置的表空间TEST05
3 ---# 3.2.5 Tablespace TEST05 with the OMF location
4 --1： Check the OMF setting （检查OMF设置）
5 SQL> show parameter db_create_file_dest;
6
7 NAME TYPE VALUE
8 ------------------------------------ ----------- ------------------------------
9 db_create_file_dest string
10 SQL>
11 -- The datafiles will be created under the directory as "db_create_file_dest"/PROD1/datafile/*.dbf
12
13 SQL> alter system set db_create_file_dest='/u01/app/oracle/oradata' scope=both;
14
15 System altered.
16
17 SQL> show parameter db_create_file_dest;
18
19 NAME TYPE VALUE
20 ------------------------------------ ----------- ------------------------------
21 db_create_file_dest string /u01/app/oracle/oradata
22 SQL>
23
24 --Create a TBS with OMF, an auto-extending datafile init 100M and 32GB maximum size （使用OMF创建一个TBS，自动扩展数据文件初始化1m和32GB的最大大小）
25 SQL> create tablespace test05;
26
27 Tablespace created.
28
29 SQL>
30
31 -- Check the created DATAFILE features (检查创建的DATAFILE特性)
32
33 SQL> col file_name format a600;
34 SQL> select file_name ,bytes/1024/1024 ||'MB' ,autoextensible,maxbytes/1024/1024 ||'MB' from dba_data_files where tablespace_name='TEST05';
35
36 FILE_NAME BYTES/1024/1024||'MB' autoextensible MAXBYTES/1024/1024||'MB'
37 -------------------------------------------------------------------------------------------------------------------------------------------------------------
38 /u01/app/oracle/oradata/PROD1/datafile/o1_mf_test05_dzx2hwco_.dbf 100MB YES 32767.984375MB
39
1 --建立非默认blocksize 表空间
2 ---# #3.2.6具有OMF(Oracle Managed Files:Oracle管理文件)位置的表空间TEST06，16k块大小。
3 --# 3.2.6 Tablespace TEST06 with the OMF location, 16k blocksize.
4 SQL> show parameter cache_size;
5
6 NAME TYPE VALUE
7 ------------------------------------ ----------- ------------------------------
8 client_result_cache_size big integer 0
9 db_16k_cache_size big integer 0
10 db_2k_cache_size big integer 0
11 db_32k_cache_size big integer 0
12 db_4k_cache_size big integer 0
13 db_8k_cache_size big integer 0
14 db_cache_size big integer 0
15 db_flash_cache_size big integer 0
16 db_keep_cache_size big integer 0
17 db_recycle_cache_size big integer 0
18 SQL> alter system set db_16k_cache_size=50m scope=both;
19
20 System altered.
21
22 SQL> create tablespace test06 blocksize 16k;
23
24 Tablespace created.
25
26 SQL> select file_name,bytes/1024/1024 mb,autoextensible,maxbytes/1024/1024 maxmb from dba_data_files where tablespace_name like 'TEST%';
27
28 FILE_NAME MB AUT MAXMB
29 ----------------------------------------------------------------------------------------------
30 /u01/app/oracle/oradata/PROD1/test01.dbf 100 NO 0
31 /u01/app/oracle/oradata/PROD1/test02.dbf 100 NO 0
32 /u01/app/oracle/oradata/PROD1/test03.dbf 100 NO 0
33 /u01/app/oracle/oradata/PROD1/test04.dbf 100 YES 2048
34
35 /u01/app/oracle/oradata/PROD1/datafile/o1_mf_test05_dzx2hwco_.dbf 100 YES 32767.9844
36
37 /u01/app/oracle/oradata/PROD1/datafile/o1_mf_test06_dzx38p3k_.dbf 100 YES 65535.9688
38
39
40 6 rows selected.
41
42 SQL>
43
1 --建立BIGFILE 表空间.
2 --Bigfile 表空间的优势是可以使用一个超大数据文件(datafile)，数据文件使用8k blocksize 可达32TB.
3
4 -- #3.2.7表空间TEST07 for bigfile，初始数据文件大小为100MB，文件预期增长到4TB
5 -- 3.2.7 Tablespace TEST07 for bigfile, initial datafile size 100MB with the file expected to grow to 4TB
6
7
8 SQL> create bigfile tablespace test07 datafile '/u01/app/oracle/oradata/PROD1/test07.dbf' size 100m ;
9
10 Tablespace created--- 3.2.8检查上述表空间：名称，数据文件名，块大小，extent_management，段空间管理，分配类型，bigfile
--- 3.2.8 Check the above tablespaces: name, datafile name, block size, extent_management, segment space management, allocation type, bigfile
11 SQL> select tablespace_name, block_size, extent_management, segment_space_management,allocation_type, bigfile from dba_tablespaces;
12
13 TABLESPACE_NAME BLOCK_SIZE EXTENT_MAN SEGMEN ALLOCATIO BIG
14 ------------------------------ ---------- ---------- ------ --------- ---
15 SYSTEM 8192 LOCAL MANUAL SYSTEM NO
16 SYSAUX 8192 LOCAL AUTO SYSTEM NO
17 UNDOTBS1 8192 LOCAL MANUAL SYSTEM NO
18 TEMP 8192 LOCAL MANUAL UNIFORM NO
19 USERS 8192 LOCAL AUTO SYSTEM NO
20 EXAMPLE 8192 LOCAL AUTO SYSTEM NO
21 TEST01 8192 LOCAL AUTO SYSTEM NO
22 TEST02 8192 LOCAL AUTO UNIFORM NO
23 TEST03 8192 LOCAL MANUAL SYSTEM NO
24 TEST04 8192 LOCAL AUTO SYSTEM NO
25 TEST05 8192 LOCAL AUTO SYSTEM NO
26 TEST06 16384 LOCAL AUTO SYSTEM NO
27 TEST07 8192 LOCAL AUTO SYSTEM YES
28
29 13 rows selected.
30
31 SQL>
----还原环境：
---3.2.10删除上述表空间并清除环境设置（缓存大小，OMF路径）
----3.2.10 Drop the above tablespaces and clear the environment settings (cache size, OMF path)
1 ---5) 恢复环境.
2 SQL> drop tablespace test01 including contents and datafiles;
3
4 Tablespace dropped.
5
6 SQL> drop tablespace test02 including contents and datafiles;
7
8 Tablespace dropped.
9
10 SQL> drop tablespace test03 including contents and datafiles;
11
12 Tablespace dropped.
13
14 SQL> drop tablespace test04 including contents and datafiles;
15
16 Tablespace dropped.
17
18 SQL> drop tablespace test05 including contents and datafiles;
19
20 Tablespace dropped.
21
22 SQL> drop tablespace test06 including contents and datafiles;
23
24 Tablespace dropped.
25
26 SQL> drop tablespace test07 including contents and datafiles;
27
28 Tablespace dropped.
29
30 SQL> alter system reset db_16k_cache_size scope=spfile;
31
32 System altered.
33
34 SQL> alter system set db_16k_cache_size=0 scope=memory;
35
36 System altered.
37
38 SQL> alter system reset db_create_file_dest scope=spfile;
39
40 System altered.
41
42 SQL> alter system set db_create_file_dest='' scope=memory;
43
44 System altered.
45
46 SQL>
------ 临时表空间(TEMPORARY TABLESPACE).
1 --4. 临时表空间(TEMPORARY TABLESPACE).
2 -- Check the default temporary TBS (检查默认的临时TBS)
3
4 SQL> col property_name format a30;
5 SQL> col propetty_value format a20;
6 SQL> select property_name,property_value from database_properties where property_name like 'DEFAULT_TEMP_TABLESPACE';
7
8 PROPERTY_NAME PROPERTY_VALUE
9 --------------------------------------------------------------------------------------------------------------
10 DEFAULT_TEMP_TABLESPACE TEMP
11
12 SQL>
13
14 --1）建立TEMP TBS group
15 --将当前临时TBS(TEMP)添加到一个新组(TEMPGROUP)
16 -- Add the current temporary TBS (TEMP) to a new group (TEMPGROUP)
17 SQL> alter tablespace temp tablespace group tempgroup;
18
19 Tablespace altered.
20 --- 检查结果：
21 SQL> select * from dba_tablespace_groups;
22
23 GROUP_NAME TABLESPACE_NAME
24 ------------------------------ ------------------------------
25 TEMPGROUP TEMP
26
27 SQL>
28
29 --创建第二个临时TBS，并将其添加到相同的组中（Create a 2nd temporary TBS, and add it to the same group）
30
31 SQL> create temporary tablespace temp2 tempfile '/u01/app/oracle/oradata/PROD1/temp201.dbf' size 100m tablespace group tempgroup;
32
33 Tablespace created.
34
35 SQL>
36 --检查下执行结果
37 SQL> select * from dba_tablespace_groups;
38
39 GROUP_NAME TABLESPACE_NAME
40 ------------------------------ ------------------------------
41 TEMPGROUP TEMP
42 TEMPGROUP TEMP2
43
44 SQL>
45
46 --将临时TBS组更改为缺省临时TBS（Change the group of temporary TBS as the default temporary TBS）
47 SQL> alter database default temporary tablespace tempgroup;
48
49 Database altered.
50 SQL> select property_name,property_value from database_properties where property_name like 'DEFAULT_TEMP_TABLESPACE';
51
52 PROPERTY_NAME PROPERTY_VALUE
53 --------------------------------------------------------------------------------------------
54
55 DEFAULT_TEMP_TABLESPACE TEMPGROUP
56
57
58 SQL>
59
60 -- 恢复环境
61 SQL> alter tablespace temp tablespace group '';
62
63 Tablespace altered.
64 SQL> alter database default temporary tablespace temp;
65
66 Database altered.
67
68 SQL> drop tablespace temp2 including contents and datafiles;
69
70 Tablespace dropped.
71
72 SQL>
73
74
75
76 ---2）收缩TEMP TBS
77 -- 增大TEMP TBS
78
79 SQL> alter database tempfile '/u01/app/oracle/oradata/PROD1/temp01.dbf' resize 200m;
80
81 Database altered.
82
83 SQL> select bytes/1024/1024 from dba_temp_files;
84
85 BYTES/1024/1024
86 ---------------
87 200
88
89
90 -- 收缩TEMP TBS 至100MB
91 SQL> alter tablespace temp shrink space keep 100m;
92
93 Tablespace altered.
94
95 SQL> select bytes/1024/1024 from dba_temp_files;
96
97 BYTES/1024/1024
98 ---------------
99 101
100 -- 收缩至最小值
101 SQL> alter tablespace temp shrink space;
102
103 Tablespace altered.
104
105 SQL> select bytes/1024/1024 from dba_temp_files;
106
107 BYTES/1024/1024
108 ---------------
109 25.9921875
110
---UNDO TABLESPACE undo 表空间
1 ---5. UNDO TABLESPACE
2
3
4 ---1）Check the UNDO parameters 1)检查撤销参数
5 -- 它是默认值。Oracle管理手动模式回滚段中的撤消eg分段，不推荐使用
6 -- UNDO_MANAGEMENT = AUTO => It is the default value. Oracle manages undo egments
7 In MANUAL mode ROLLBACK segments are used -> not recommended
8
9 -- UNDO_RETENTION = 900 => The time Oracle TRYING to retain info in UNDO (秒)
10 -- UNDO_TABLESPACE = UNDOTBS1 => If not specified, Oracle uses the first one it finds
11
12
13 SQL> show parameter undo
14
15 NAME TYPE VALUE
16 ------------------------------------ ----------- ------------------------------
17 undo_management string AUTO
18 undo_retention integer 900
19 undo_tablespace string UNDOTBS1
20 SQL>
21
22 ---2）Set the size of UNDO TBS. （设置 undo TBS的大小。）
23
24
25 SQL> select file_name from dba_data_files;
26
27 FILE_NAME
28 --------------------------------------------------------------------------------
29 /u01/app/oracle/oradata/PROD1/users01.dbf
30 /u01/app/oracle/oradata/PROD1/undotbs01.dbf
31 /u01/app/oracle/oradata/PROD1/sysaux01.dbf
32 /u01/app/oracle/oradata/PROD1/system01.dbf
33 /u01/app/oracle/oradata/PROD1/example01.dbf
34
35 SQL> alter database datafile '/u01/app/oracle/oradata/PROD1/undotbs01.dbf' autoextend on;
36
37 Database altered.
38 -- Set the retention(保留) to 5 hours (5h * 60min * 60seg = 18000)
39 -- If UNDO TBS DATAFILES are not in AUTOEXTEND mode, the UNDO_RETENTION parameter is ignored
40 --如果 undo表空间数据文件不存在自动扩展，则会自动忽略undo滞留参数
41 SQL> alter system set undo_retention=18000 scope=both;
42
43 System altered.
44
----3）避免ORA-1555 and ORA-30036 errors
1 -- When get an error, use the "OERR" utility to get more information the error message
2 --!oerr ora 1555 # Suggest increasing the parameter UNDO_RETENTION or extend the UNDO
3 --!oerr ora 30036 # Suggest increasing the UNDO TBS
4 /*
5 ——当出现错误时，使用“OERR”工具获取更多信息，错误消息
6 ！oerr ora 1555建议增加参数不保留或扩展撤销
7 ！oerr ora 30036建议增加撤消TBS
8
9 */
10 SQL> alter tablespace undotbs1 retention guarantee;
11
12 Tablespace altered.
13
14 SQL> alter tablespace undotbs1 retention noguarantee;
15
16 Tablespace altered.
17
18 SQL>
--变更UNDO 表空间
1 -- #3.3在数据库中设置自动撤销管理，支持以下要求：-- 3.3 Set up automatic undo management in the database to support the following requirements:-- #3.3.1避免ORA-01555对于平均5个小时的查询，快照太旧错误。-- 3.3.1 Avoid ORA-01555 Snapshot too old errors for queries running up to 5 hours on average.-- #3.3.2检查不同状态的UNDO的使用情况。-- 3.3.2 Check the usage of UNDO with different status.-- #3.3.3将UNDO更改为tablespace undotbs2，然后更改为tablespace undotbs1。-- 3.3.3 Change the UNDO to tablespace undotbs2, then change back to tablespace undotbs1.-- #3.3.4向表空间undotbs1添加一个新的datafile undotbs02.dbf（size = 100MB，扩展大小为10MB），然后删除新的撤消数据文件。-- 3.3.4 Add a new datafile undotbs02.dbf (size=100MB, extent size of 10MB) to tablespace undotbs1, then drop the new undo datafile.-- #3.4检查所有表空间（永久，临时）及其数据文件：文件＃，文件名，表空间名称-- 3.4 Check all the tablespaces (permanent, temporary) and their datafiles: file#,file name,tablespace name ---4) 变更UNDO TBS
2 -- 创建一个新的 UNDO 表空间 (Create a new UNDO TBS)
3
4 SQL> select file_name from dba_data_files;
5
6 FILE_NAME
7 --------------------------------------------------------------------------------
8 /u01/app/oracle/oradata/PROD1/users01.dbf
9 /u01/app/oracle/oradata/PROD1/undotbs01.dbf
10 /u01/app/oracle/oradata/PROD1/sysaux01.dbf
11 /u01/app/oracle/oradata/PROD1/system01.dbf
12 /u01/app/oracle/oradata/PROD1/example01.dbf
13 ----创建undo表空间2 初始化大小为100M 每次增长1oM 无最大限制。
14 SQL> create undo tablespace undotbs2 datafile '/u01/app/oracle/oradata/PROD1/undotbs02.dbf' size 100m autoextend on next 10m maxsize unlimited;
15
16 Tablespace created.
17
18 SQL> show parameter undo_t
19
20 NAME TYPE VALUE
21 ------------------------------------ ----------- ------------------------------
22 undo_tablespace string UNDOTBS1
23
24 SQL> alter system set undo_tablespace=undotbs2 scope=both;
25
26 System altered.
27
28 SQL> show parameter undo_tablespace
29
30 NAME TYPE VALUE
31 ------------------------------------ ----------- ------------------------------
32 undo_tablespace string UNDOTBS2
33 SQL>
34
35
36 -- Delete the old TBS 删除旧的 表空间
37 SQL> drop tablespace undotbs1 including contents and datafiles;
38
39 Tablespace dropped.
40
41 SQL> select file_name from dba_data_files;
42
43 FILE_NAME
44 --------------------------------------------------------------------------------
45 /u01/app/oracle/oradata/PROD1/users01.dbf
46 /u01/app/oracle/oradata/PROD1/sysaux01.dbf
47 /u01/app/oracle/oradata/PROD1/system01.dbf
48 /u01/app/oracle/oradata/PROD1/example01.dbf
49 /u01/app/oracle/oradata/PROD1/undotbs02.dbf
50
51 -- Reset the old UNDO TBS DATAFILE 重置旧的 undo TBS数据文件
52
53 SQL> select file_name from dba_data_files;
54
55 FILE_NAME
56 --------------------------------------------------------------------------------
57 /u01/app/oracle/oradata/PROD1/users01.dbf
58 /u01/app/oracle/oradata/PROD1/sysaux01.dbf
59 /u01/app/oracle/oradata/PROD1/system01.dbf
60 /u01/app/oracle/oradata/PROD1/example01.dbf
61 /u01/app/oracle/oradata/PROD1/undotbs02.dbf
62
63 SQL> create undo tablespace undotbs1 datafile '/u01/app/oracle/oradata/PROD1/undotbs01.dbf' size 100m autoextend on next 10m maxsize unlimited;
64
65 Tablespace created.
66
67 SQL> select file_name from dba_data_files;
68
69 FILE_NAME
70 --------------------------------------------------------------------------------
71 /u01/app/oracle/oradata/PROD1/users01.dbf
72 /u01/app/oracle/oradata/PROD1/undotbs01.dbf
73 /u01/app/oracle/oradata/PROD1/sysaux01.dbf
74 /u01/app/oracle/oradata/PROD1/system01.dbf
75 /u01/app/oracle/oradata/PROD1/example01.dbf
76 /u01/app/oracle/oradata/PROD1/undotbs02.dbf
77
78 6 rows selected.
79
80 SQL> show parameter undo_tablespace;
81
82 NAME TYPE VALUE
83 ------------------------------------ ----------- ------------------------------
84 undo_tablespace string UNDOTBS2
85 SQL> alter system set undo_tablespace=undotbs1 scope=both;
86
87 System altered.
88
89 SQL> show parameter undo_tablespace
90
91 NAME TYPE VALUE
92 ------------------------------------ ----------- ------------------------------
93 undo_tablespace string UNDOTBS1
94
95 SQL> drop tablespace undotbs2 including contents and datafiles;
96
97 Tablespace dropped.
98
99 SQL> select file_name from dba_data_files;
100
101 FILE_NAME
102 --------------------------------------------------------------------------------
103 /u01/app/oracle/oradata/PROD1/users01.dbf
104 /u01/app/oracle/oradata/PROD1/undotbs01.dbf
105 /u01/app/oracle/oradata/PROD1/sysaux01.dbf
106 /u01/app/oracle/oradata/PROD1/system01.dbf
107 /u01/app/oracle/oradata/PROD1/example01.dbf
108
109
110 -- Add/Delete an UNDO datafile (新增/删除 undo 表空间数据文件)
111
112
113 SQL> select file_name from dba_data_files;
114
115 FILE_NAME
116 --------------------------------------------------------------------------------
117 /u01/app/oracle/oradata/PROD1/users01.dbf
118 /u01/app/oracle/oradata/PROD1/undotbs01.dbf
119 /u01/app/oracle/oradata/PROD1/sysaux01.dbf
120 /u01/app/oracle/oradata/PROD1/system01.dbf
121 /u01/app/oracle/oradata/PROD1/example01.dbf
122
123 SQL> alter tablespace undotbs1 add datafile '/u01/app/oracle/oradata/PROD1/undotbs01_1.dbf' size 100m autoextend on next 10m maxsize unlimited;
124
125 Tablespace altered.
126
127 SQL> select file_name from dba_data_files;
128
129 FILE_NAME
130 --------------------------------------------------------------------------------
131 /u01/app/oracle/oradata/PROD1/users01.dbf
132 /u01/app/oracle/oradata/PROD1/undotbs01.dbf
133 /u01/app/oracle/oradata/PROD1/sysaux01.dbf
134 /u01/app/oracle/oradata/PROD1/system01.dbf
135 /u01/app/oracle/oradata/PROD1/example01.dbf
136 /u01/app/oracle/oradata/PROD1/undotbs01_1.dbf
137
138 6 rows selected.
139
140 SQL> alter tablespace undotbs1 drop datafile '/u01/app/oracle/oradata/PROD1/undotbs01_1.dbf' ;
141
142 Tablespace altered.
143
144 SQL> select file_name from dba_data_files;
145
146 FILE_NAME
147 --------------------------------------------------------------------------------
148 /u01/app/oracle/oradata/PROD1/users01.dbf
149 /u01/app/oracle/oradata/PROD1/undotbs01.dbf
150 /u01/app/oracle/oradata/PROD1/sysaux01.dbf
151 /u01/app/oracle/oradata/PROD1/system01.dbf
152 /u01/app/oracle/oradata/PROD1/example01.dbf
153
154 --6. Check the TBS and their datafiles
155 SQL> set linesize 5000;
156 SQL> col file_name format a50;
157 SQL> select file_name,file_id,tablespace_name from dba_data_files;
158
159 FILE_NAME FILE_ID TABLESPACE_NAME
160 -------------------------------------------------- ---------- ------------------------------
161 /u01/app/oracle/oradata/PROD1/users01.dbf 4 USERS
162 /u01/app/oracle/oradata/PROD1/undotbs01.dbf 3 UNDOTBS1
163 /u01/app/oracle/oradata/PROD1/sysaux01.dbf 2 SYSAUX
164 /u01/app/oracle/oradata/PROD1/system01.dbf 1 SYSTEM
165 /u01/app/oracle/oradata/PROD1/example01.dbf 5 EXAMPLE
166
104 - Stripe Data Files Across Multiple Physical Devices and Locations（跨多个物理设备和位置的条带数据文件）
1 #--104 - Stripe Data Files Across Multiple Physical Devices and Locations （跨多个物理设备和位置的条带数据文件）
2 /*
3 1:在文档位置：
4 Performance Tuning Guide -> 8 I/O Configuration and Design
5
6
7 #4.跨多个物理设备和位置条带数据文件 4. Stripe data files across multiple physical devices and locations #4.1确认路径的现有路径，并将权限设置为user oracle。 #Dir1：/ u01 / app / oracle / oradata / PROD1 #Dir2：/ u02 / app / oracle / oradata / PROD2 4.1 Confirm the existing of the path and set the privileges to user oracle. Dir1: /u01/app/oracle/oradata/PROD1 Dir2: /u02/app/oracle/oradata/PROD2*/
8 2. 建立数据文件目录，模拟不同的磁盘
9 -- Create the directory as root and give permissions
10 [root@oracle ~]# mkdir -p /u02/app/oracle/oradata/PROD1
11 [root@oracle ~]# chown -R oracle:oinstall /u02/app/oracle
12 [root@oracle ~]# chmod -R 775 /u02/app/oracle
----重建在线日志文件
1 /*
2 4.1 Confirm the existing of the path and set the privileges to user oracle.
3 Dir1: /u01/app/oracle/oradata/PROD1
4 Dir2: /u02/app/oracle/oradata/PROD2
5 #4.2将重做日志文件重新映射到上述两个路径中的每组3组和2个成员
6 4.2 Recreate the redo log files to 3 groups and 2 members per group located to the above two paths
7 */
8 [root@oracle ~]# su - oracle
9 [oracle@oracle ~]$ export ORACLE_SID=PROD1
10 [oracle@oracle ~]$ sqlplus / as sysdba;
11
12 SQL*Plus: Release 11.2.0.3.0 Production on Sat Nov 11 12:08:58 2017
13
14 Copyright (c) 1982, 2011, Oracle. All rights reserved.
15
16
17 Connected to:
18 Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
19 With the Partitioning, OLAP, Data Mining and Real Application Testing options
20
21 SQL> set linesize 30000;
22
23 SQL> select * from v$log;
24
25 GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
26 ---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ ---------
27 1 1 10 52428800 512 1 YES INACTIVE 922570 05-NOV-17 944610 11-NOV-17
28 2 1 8 52428800 512 1 YES INACTIVE 901908 04-NOV-17 914559 05-NOV-17
29 3 1 9 52428800 512 1 YES INACTIVE 914559 05-NOV-17 922570 05-NOV-17
30 4 1 11 104857600 512 2 NO CURRENT 944610 11-NOV-17 2.8147E+14
31
32 SQL> col member format a100;
33 SQL> select * from v$logfile;
34
35 GROUP# STATUS TYPE MEMBER IS_
36 ---------- ------- ------- ---------------------------------------------------------------------------------------------------- ---
37 3 ONLINE /u01/app/oracle/oradata/PROD1/redo03.log NO
38 2 ONLINE /u01/app/oracle/oradata/PROD1/redo02.log NO
39 1 ONLINE /u01/app/oracle/oradata/PROD1/redo01.log NO
40 4 ONLINE /u01/app/oracle/oradata/PROD1/redo401.log NO
41 4 ONLINE /u01/app/oracle/oradata/PROD1/redo402.log NO
42 ---查询系统使用的是哪一组日志文件：
43 /*
44 可以通过v$log视图来查看日志文件的状态。
45 状态有以下几种：current/active/inactive/unused
46 current：当前的日志文件，该日志文件是活动的，当前正在被使用的，在进行崩溃恢复时，current日志文件是必须的
47 active：活动的非当前日志，该日志可能已经完成归档，也可能没有归档，活动的日志文件在crash恢复时会被用到
48 inactive：非活动日志，该日志在实例恢复时不再需要，但是在介质恢复时可能会用到。inactive状态的日志也可能没有被归档。如果数据库在归档模式，在未完成归档之前，日志文件也不允许被覆盖，这时候活动进程会处于Log File Switch（Archiving Needed）等待之中。
49 unused：日志从未被写入，这类日志可能是刚被添加到数据库或者在Reset Logs之后被重置。被使用之后，该状态会被改变。
50 */
51
52 SQL> select * from v$log;
53
54 GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
55 ---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ ---------
56 1 1 10 52428800 512 1 YES INACTIVE 922570 05-NOV-17 944610 11-NOV-17
57 2 1 8 52428800 512 1 YES INACTIVE 901908 04-NOV-17 914559 05-NOV-17
58 3 1 9 52428800 512 1 YES INACTIVE 914559 05-NOV-17 922570 05-NOV-17
59 4 1 11 104857600 512 2 NO CURRENT 944610 11-NOV-17 2.8147E+14
60
61 SQL> col member format a100;
62 --- 查询正在使用的组所对应的日志文件：
63 SQL> select * from v$logfile;
64
65 GROUP# STATUS TYPE MEMBER IS_
66 ---------- ------- ------- ---------------------------------------------------------------------------------------------------- ---
67 3 ONLINE /u01/app/oracle/oradata/PROD1/redo03.log NO
68 2 ONLINE /u01/app/oracle/oradata/PROD1/redo02.log NO
69 1 ONLINE /u01/app/oracle/oradata/PROD1/redo01.log NO
70 4 ONLINE /u01/app/oracle/oradata/PROD1/redo401.log NO
71 4 ONLINE /u01/app/oracle/oradata/PROD1/redo402.log NO
72 ----新增日志文件组 5
73 SQL> alter database add logfile group 5 ('/u01/app/oracle/oradata/PROD1/redo501.log','/u02/app/oracle/oradata/PROD1/redo502.log') size 100m;
74
75
76 Database altered.
77
78 ----新增日志文件组 6
79 SQL> alter database add logfile group 6 ('/u01/app/oracle/oradata/PROD1/redo601.log','/u02/app/oracle/oradata/PROD1/redo602.log') size 100m;
80
81
82 Database altered.
83 ----查询正在使用的组所对应的日志文件：
84 SQL> select * from v$logfile;
85
86 GROUP# STATUS TYPE MEMBER IS_
87 ---------- ------- ------- ---------------------------------------------------------------------------------------------------- ---
88 3 ONLINE /u01/app/oracle/oradata/PROD1/redo03.log NO
89 2 ONLINE /u01/app/oracle/oradata/PROD1/redo02.log NO
90 1 ONLINE /u01/app/oracle/oradata/PROD1/redo01.log NO
91 4 ONLINE /u01/app/oracle/oradata/PROD1/redo401.log NO
92 4 ONLINE /u01/app/oracle/oradata/PROD1/redo402.log NO
93 6 ONLINE /u01/app/oracle/oradata/PROD1/redo601.log NO
94 6 ONLINE /u02/app/oracle/oradata/PROD1/redo602.log NO
95 5 ONLINE /u01/app/oracle/oradata/PROD1/redo501.log NO
96 5 ONLINE /u02/app/oracle/oradata/PROD1/redo502.log NO
97
98 9 rows selected.
99
100
101
102 ---强制日志切换：
103
104 SQL> alter system switch logfile;
105
106 System altered.
107
108 ---查询系统使用的是哪一组日志文件：
109 SQL> select * from v$log;
110
111 GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
112 ---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ ---------
113 1 1 10 52428800 512 1 YES INACTIVE 922570 05-NOV-17 944610 11-NOV-17
114 2 1 12 52428800 512 1 YES ACTIVE 947873 11-NOV-17 964189 11-NOV-17
115 3 1 9 52428800 512 1 YES INACTIVE 914559 05-NOV-17 922570 05-NOV-17
116 4 1 11 104857600 512 2 YES INACTIVE 944610 11-NOV-17 947873 11-NOV-17
117 5 1 13 104857600 512 2 NO CURRENT 964189 11-NOV-17 2.8147E+14
118 6 1 0 104857600 512 2 YES UNUSED 0 0
119
120 6 rows selected.
121
122
123 /*
124 checkpoint由ckpt进程触发oracle进行checkpoint动作,将data buffer中的脏块(已经写在
125 redo里记录但是没有写到datafile里的)的内容写入到data file里并释放站用的空间,由
126 dbw后台进程完成,并修改controlfile和datafile的scn.
127
128 一般手工执行是由于要删除某个日志但是该日志里还有没有同步到data file里的内容,就
129 需要手工check point来同步数据,然后就可以drop logfile group n.
130 */
131
132 SQL> alter system checkpoint;
133
134 System altered.
135
136 SQL> select * from v$log;
137
138 GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
139 ---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ ---------
140 1 1 10 52428800 512 1 YES INACTIVE 922570 05-NOV-17 944610 11-NOV-17
141 2 1 12 52428800 512 1 YES INACTIVE 947873 11-NOV-17 964189 11-NOV-17
142 3 1 9 52428800 512 1 YES INACTIVE 914559 05-NOV-17 922570 05-NOV-17
143 4 1 11 104857600 512 2 YES INACTIVE 944610 11-NOV-17 947873 11-NOV-17
144 5 1 13 104857600 512 2 NO CURRENT 964189 11-NOV-17 2.8147E+14
145 6 1 0 104857600 512 2 YES UNUSED 0 0
146
147 6 rows selected.
148
149 -----删除日志组文件
150 SQL> select * from v$logfile order by 1;
151
152 GROUP# STATUS TYPE MEMBER IS_
153 ---------- ------- ------- ---------------------------------------------------------------------------------------------------- ---
154 1 ONLINE /u01/app/oracle/oradata/PROD1/redo01.log NO
155 2 ONLINE /u01/app/oracle/oradata/PROD1/redo02.log NO
156 3 ONLINE /u01/app/oracle/oradata/PROD1/redo03.log NO
157 4 ONLINE /u01/app/oracle/oradata/PROD1/redo401.log NO
158 4 ONLINE /u01/app/oracle/oradata/PROD1/redo402.log NO
159 5 ONLINE /u01/app/oracle/oradata/PROD1/redo501.log NO
160 5 ONLINE /u02/app/oracle/oradata/PROD1/redo502.log NO
161 6 ONLINE /u01/app/oracle/oradata/PROD1/redo601.log NO
162 6 ONLINE /u02/app/oracle/oradata/PROD1/redo602.log NO
163
164 9 rows selected.
165
166 SQL> alter database drop logfile group 1;
167
168 Database altered.
169
170 SQL> select * from v$logfile order by 1;
171
172 GROUP# STATUS TYPE MEMBER IS_
173 ---------- ------- ------- ---------------------------------------------------------------------------------------------------- ---
174 2 ONLINE /u01/app/oracle/oradata/PROD1/redo02.log NO
175 3 ONLINE /u01/app/oracle/oradata/PROD1/redo03.log NO
176 4 ONLINE /u01/app/oracle/oradata/PROD1/redo401.log NO
177 4 ONLINE /u01/app/oracle/oradata/PROD1/redo402.log NO
178 5 ONLINE /u01/app/oracle/oradata/PROD1/redo501.log NO
179 5 ONLINE /u02/app/oracle/oradata/PROD1/redo502.log NO
180 6 ONLINE /u01/app/oracle/oradata/PROD1/redo601.log NO
181 6 ONLINE /u02/app/oracle/oradata/PROD1/redo602.log NO
182
183 8 rows selected.
184
185
186 SQL> alter database add logfile group 1 ('/u01/app/oracle/oradata/PROD1/redo101.log','/u02/app/oracle/oradata/PROD1/redo102.log') size 100m reuse ;
187
188 SQL> select * from v$logfile order by 1;
189
190 GROUP# STATUS TYPE MEMBER IS_
191 ---------- ------- ------- ---------------------------------------------------------------------------------------------------- ---
192 1 ONLINE /u02/app/oracle/oradata/PROD1/redo102.log NO
193 1 ONLINE /u01/app/oracle/oradata/PROD1/redo101.log NO
194 2 ONLINE /u01/app/oracle/oradata/PROD1/redo02.log NO
195 3 ONLINE /u01/app/oracle/oradata/PROD1/redo03.log NO
196 4 ONLINE /u01/app/oracle/oradata/PROD1/redo401.log NO
197 4 ONLINE /u01/app/oracle/oradata/PROD1/redo402.log NO
198 5 ONLINE /u01/app/oracle/oradata/PROD1/redo501.log NO
199 5 ONLINE /u02/app/oracle/oradata/PROD1/redo502.log NO
200 6 ONLINE /u02/app/oracle/oradata/PROD1/redo602.log NO
201 6 ONLINE /u01/app/oracle/oradata/PROD1/redo601.log NO
202
203 10 rows selected.
204
205 SQL> alter database drop logfile group 2;
206
207 Database altered.
208
209 SQL> select * from v$logfile;
210
211 GROUP# STATUS TYPE MEMBER IS_
212 ---------- ------- ------- ---------------------------------------------------------------------------------------------------- ---
213 3 ONLINE /u01/app/oracle/oradata/PROD1/redo03.log NO
214 1 ONLINE /u01/app/oracle/oradata/PROD1/redo101.log NO
215 4 ONLINE /u01/app/oracle/oradata/PROD1/redo401.log NO
216 4 ONLINE /u01/app/oracle/oradata/PROD1/redo402.log NO
217 6 ONLINE /u01/app/oracle/oradata/PROD1/redo601.log NO
218 6 ONLINE /u02/app/oracle/oradata/PROD1/redo602.log NO
219 5 ONLINE /u01/app/oracle/oradata/PROD1/redo501.log NO
220 5 ONLINE /u02/app/oracle/oradata/PROD1/redo502.log NO
221 1 ONLINE /u02/app/oracle/oradata/PROD1/redo102.log NO
222
223 9 rows selected.
224
225 SQL> alter database add logfile group 2 ('/u01/app/oracle/oradata/PROD1/redo201.log','/u02/app/oracle/oradata/PROD1/redo202.log') size 100m reuse;
226
227 Database altered.
228
229 SQL> select * from v$logfile order by 1;
230
231 GROUP# STATUS TYPE MEMBER IS_
232 ---------- ------- ------- ---------------------------------------------------------------------------------------------------- ---
233 1 ONLINE /u01/app/oracle/oradata/PROD1/redo101.log NO
234 1 ONLINE /u02/app/oracle/oradata/PROD1/redo102.log NO
235 2 ONLINE /u02/app/oracle/oradata/PROD1/redo202.log NO
236 2 ONLINE /u01/app/oracle/oradata/PROD1/redo201.log NO
237 3 ONLINE /u01/app/oracle/oradata/PROD1/redo03.log NO
238 4 ONLINE /u01/app/oracle/oradata/PROD1/redo401.log NO
239 4 ONLINE /u01/app/oracle/oradata/PROD1/redo402.log NO
240 5 ONLINE /u01/app/oracle/oradata/PROD1/redo501.log NO
241 5 ONLINE /u02/app/oracle/oradata/PROD1/redo502.log NO
242 6 ONLINE /u02/app/oracle/oradata/PROD1/redo602.log NO
243 6 ONLINE /u01/app/oracle/oradata/PROD1/redo601.log NO
244
245 11 rows selected.
246
247 SQL> alter database drop logfile group 3;
248
249 Database altered.
250
251 SQL> alter database add logfile group 3 ('/u01/app/oracle/oradata/PROD1/redo301.log','/u02/app/oracle/oradata/PROD1/redo302.log') size 100m reuse;
252
253 Database altered.
254
255 SQL> select * from v$logfile;
256
257 GROUP# STATUS TYPE MEMBER IS_
258 ---------- ------- ------- ---------------------------------------------------------------------------------------------------- ---
259 3 ONLINE /u01/app/oracle/oradata/PROD1/redo301.log NO
260 2 ONLINE /u01/app/oracle/oradata/PROD1/redo201.log NO
261 1 ONLINE /u01/app/oracle/oradata/PROD1/redo101.log NO
262 4 ONLINE /u01/app/oracle/oradata/PROD1/redo401.log NO
263 4 ONLINE /u01/app/oracle/oradata/PROD1/redo402.log NO
264 6 ONLINE /u01/app/oracle/oradata/PROD1/redo601.log NO
265 6 ONLINE /u02/app/oracle/oradata/PROD1/redo602.log NO
266 5 ONLINE /u01/app/oracle/oradata/PROD1/redo501.log NO
267 5 ONLINE /u02/app/oracle/oradata/PROD1/redo502.log NO
268 1 ONLINE /u02/app/oracle/oradata/PROD1/redo102.log NO
269 2 ONLINE /u02/app/oracle/oradata/PROD1/redo202.log NO
270
271 GROUP# STATUS TYPE MEMBER IS_
272 ---------- ------- ------- ---------------------------------------------------------------------------------------------------- ---
273 3 ONLINE /u02/app/oracle/oradata/PROD1/redo302.log NO
274
275 12 rows selected.
276
277
278
279 ---删除非当前任务状态；而且还未被使用状态的 日志组 6 的文件信息
280 SQL> alter database drop logfile group 6;
281
282 Database altered.
283
284 SQL> select * from v$log;
285
286 GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
287 ---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ ---------
288 1 1 0 104857600 512 2 YES UNUSED 0 0
289 2 1 0 104857600 512 2 YES UNUSED 0 0
290 3 1 0 104857600 512 2 YES UNUSED 0 0
291 5 1 13 104857600 512 2 NO CURRENT 964189 11-NOV-17 2.8147E+14
292
293 SQL> select * from v$logfile;
294
295 GROUP# STATUS TYPE MEMBER IS_
296 ---------- ------- ------- ---------------------------------------------------------------------------------------------------- ---
297 3 ONLINE /u01/app/oracle/oradata/PROD1/redo301.log NO
298 2 ONLINE /u01/app/oracle/oradata/PROD1/redo201.log NO
299 1 ONLINE /u01/app/oracle/oradata/PROD1/redo101.log NO
300 5 ONLINE /u01/app/oracle/oradata/PROD1/redo501.log NO
301 5 ONLINE /u02/app/oracle/oradata/PROD1/redo502.log NO
302 1 ONLINE /u02/app/oracle/oradata/PROD1/redo102.log NO
303 2 ONLINE /u02/app/oracle/oradata/PROD1/redo202.log NO
304 3 ONLINE /u02/app/oracle/oradata/PROD1/redo302.log NO
305
306 8 rows selected.
307
308
309 -----切换日志：把当前状态的日志切换非当前任务状态
310 SQL> alter system switch logfile;
311
312 System altered.
313 SQL> select * from v$log order by 1;
314
315 GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
316 ---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ ---------
317 1 1 14 104857600 512 2 NO CURRENT 976765 11-NOV-17 2.8147E+14
318 2 1 0 104857600 512 2 YES UNUSED 0 0
319 3 1 0 104857600 512 2 YES UNUSED 0 0
320 5 1 13 104857600 512 2 YES ACTIVE 964189 11-NOV-17 976765 11-NOV-17
321
322 ---oracle中如何将active状态的重做日志文件切换成inactive
323
324 SQL> alter system checkpoint;
325
326 System altered.
327
328 SQL> select * from v$log order by 1;
329
330 GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
331 ---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ ---------
332 1 1 14 104857600 512 2 YES INACTIVE 976765 11-NOV-17 976793 11-NOV-17
333 2 1 15 104857600 512 2 YES INACTIVE 976793 11-NOV-17 976813 11-NOV-17
334 3 1 16 104857600 512 2 NO CURRENT 976813 11-NOV-17 2.8147E+14
335 5 1 13 104857600 512 2 YES INACTIVE 964189 11-NOV-17 976765 11-NOV-17
336
337 SQL> alter database drop logfile group 5;
338
339 Database altered.
340
341 SQL> select * from v$log order by 1;
342
343 GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
344 ---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ ---------
345 1 1 14 104857600 512 2 YES INACTIVE 976765 11-NOV-17 976793 11-NOV-17
346 2 1 15 104857600 512 2 YES INACTIVE 976793 11-NOV-17 976813 11-NOV-17
347 3 1 16 104857600 512 2 NO CURRENT 976813 11-NOV-17 2.8147E+14
348
349 SQL>
---------- 4: 新增表空间
1
2 ---#4.3将位于Dir2中的新数据文件system02.dbf添加到系统表空间。
3 --- 4.3 Add a new datafile system02.dbf located in the Dir2 to the system tablespace.
4
5 ---可以通过PL/SQL查看。
6 ---1. 查看所有表空间大小
7 select tablespace_name,sum(bytes)/1024/1024 from dba_data_files group by tablespace_name;
8 ---2. 未使用的表空间大小
9 select tablespace_name,sum(bytes)/1024/1024 from dba_free_space group by tablespace_name;
10
11
12 SQL> col file_name format a100;
13 SQL> select file_name from dba_data_files;
14
15 FILE_NAME
16 ----------------------------------------------------------------------------------------------------
17 /u01/app/oracle/oradata/PROD2/datafile/o1_mf_system_dz3z3pyj_.dbf
18 /u01/app/oracle/oradata/PROD2/datafile/o1_mf_sysaux_dz3z3tz1_.dbf
19 /u01/app/oracle/oradata/PROD2/datafile/o1_mf_sys_undo_dz3z3z78_.dbf
20 ----新增 表空间数据文件
21 SQL> alter tablespace system add datafile '/u02/app/oracle/oradata/PROD1/system02.dbf' size 400M ;
22
23 Tablespace altered.
24
25 SQL> select file_name from dba_data_files;
26
27 FILE_NAME
28 ----------------------------------------------------------------------------------------------------
29 /u01/app/oracle/oradata/PROD2/datafile/o1_mf_system_dz3z3pyj_.dbf
30 /u02/app/oracle/oradata/PROD1/system02.dbf
31 /u01/app/oracle/oradata/PROD2/datafile/o1_mf_sysaux_dz3z3tz1_.dbf
32 /u01/app/oracle/oradata/PROD2/datafile/o1_mf_sys_undo_dz3z3z78_.dbf
33
34 ---在系统数据盘之外的磁盘建立FRA，存放归档日志及备份等
35 SQL> show parameter db_recovery
36
37 NAME TYPE VALUE
38 ------------------------------------ ----------- ------------------------------
39 db_recovery_file_dest string
40 db_recovery_file_dest_size big integer 0
41
42
43 SQL> select name from v$archived_log order by completion_time;
44
45 no rows selected
46
----------5:配置数据库环境以支持最佳数据访问性能
1 --- 5.配置数据库环境以支持最佳数据访问性能
2 --- 5. Configure the database environment to support optimal data access performance
3
4 /*
5 1. 文档
6 Performance Tuning Guide -> 8 I/O Configuration and Design
7 Reference -> 1 Initialization Parameters
8 2. DB_BLOCK_SIZE 参数
9 DB_BLOCK_SIZE 的参考建议（提升I/O 效率）:
10 Small rows + random access => small block size (DB_BLOCK_SIZE = 8192)
11 Small rows + sequential access => big block size (DB_BLOCK_SIZE = 16384 or 32768)
12 Small rows + random and sequential access => big block size (DB_BLOCK_SIZE = 16384 or 32768)
13 Big rows (例如：LOB objects) => big block size (DB_BLOCK_SIZE = 16384 or 32768)
14 For example, a data warehouse (DW) DB:
15 Default block size for DB DW => DB_BLOCK_SIZE = 16K
16 The TABLESPACES for FACT TABLES（事实表） => DB_BLOCK_SIZE = 32K
17 The TABLESPACES for DIMENSION TABLES（维度表） => DB_BLOCK_SIZE = 8K
18
19 3. FAST_START_MTTR_TARGET 参数（mean time to recover - MTTR）
20 The FAST_START_MTTR_TARGET initialization parameter causes the database to calculate internal
21 system trigger values, in order to limit the length of the redo log and the number of dirty data
22 buffers in the data cache.
23 FAST_START_MTTR_TARGET enables you to specify the number of seconds the database takes to perform
24 crash recovery of a single instance. When we set this parameter, the database starts to run
25 checkpoint incremental periodically so that recovery before a drop of DB does not exceed the
26 time established in FAST_START_MTTR_TARGET.
27
28
29
30
31 */
32
33
34
35 ---fast_start_mttr_target 值在0到3600秒之间，默认值为0。
36
37 SQL> show parameter fast_start_mttr_target
38
39 NAME TYPE VALUE
40 ------------------------------------ ----------- ------------------------------
41 fast_start_mttr_target integer 0
42
43 ---Determining Lower Bound for FAST_START_MTTR_TARGET （FAST_START_MTTR_TARGET 确定下限）
44 SQL> alter system set fast_start_mttr_target=1 scope=both;
45
46 System altered.
47
48 SQL> shutdown immediate;
49 Database closed.
50 Database dismounted.
51 ORACLE instance shut down.
52 SQL> startup
53 ORACLE instance started.
54
55 Total System Global Area 1071333376 bytes
56 Fixed Size 1349732 bytes
57 Variable Size 658507676 bytes
58 Database Buffers 406847488 bytes
59 Redo Buffers 4628480 bytes
60 Database mounted.
61 Database opened.
62 SQL> select target_mttr,estimated_mttr from v$instance_recovery;
63
64 TARGET_MTTR ESTIMATED_MTTR
65 ----------- --------------
66 11 9
67 /**
68 TARGET_MTTR :值是系统可以实现的最小MTTR目标
69 ESTIMATED_MTTR : 估计的mttr字段包含根据当前情况进行恢复的平均时间 运行数据库的状态。最近的数据库活动可以在短期内影响评估mttr
70
71
72 */
73
74 -- Set FAST_START_MTTR_TARGET = TARGET_MTTR
75 SQL> alter system set fast_start_mttr_target=18 scope=both;
76
77 System altered.
78
79 -- Get the recommended size (in MB) of the redo files
80 -- The size is considered optimal based on the current setting of FAST_START_MTTR_TARGET.
81
82 SQL> select optimal_logfile_size from v$instance_recovery;
83
84 OPTIMAL_LOGFILE_SIZE
85 --------------------
86 245
87 SQL> select target_mttr,estimated_mttr from v$instance_recovery;
88
89 TARGET_MTTR ESTIMATED_MTTR
90 ----------- --------------
91 18 9
92 --#5.2配置数据库以在alert.log文件中记录检查点。
93 --- 5.2 Configure your database to record checkpoints in the alert.log file.
94 /*
95 LOG_CHECKPOINT_INTERVAL:指定检查点的频率，在增量检查点和写到重做日志的最后一个块之间存在的重做日志文件块的数量。这个数字是指物理操作系统块，而不是数据库块。默认值为0，该参数的非0值被认为是有意义的。
96 LOG_CHECKPOINT_TIMEOUT:指定(以秒为单位)从上一次写到重做日志(有时称为日志尾)的位置上的增量检查点的时间所发生的时间。这个参数也表示，在超过整数秒的时间内，任何缓冲区都不会是脏的(在缓存中)。默认是1800。指定一个值为0的值禁用基于时间的检查点。因此，除非设置了faststartmttrtarget，否则不建议将值设置为0。
97
98 LOG_CHECKPOINTS_TO_ALERT:让您将检查点记录到警报文件。这样做对于确定检查点是否出现在期望的频率是很有用的。默认值是错误的。
99 */
100
101
102 SQL> show parameter log_checkpoint;
103
104 NAME TYPE VALUE
105 ------------------------------------ ----------- ------------------------------
106 log_checkpoint_interval integer 0
107 log_checkpoint_timeout integer 1800
108 log_checkpoints_to_alert boolean FALSE
109 SQL> alter system set log_checkpoints_to_alert=true;
110
111 System altered.
112
113 /*
114 ARCHIVE_LAG_TARGET 强制执行一个重做日志开关的时间间隔。值的范围:60-7200秒，默认值为0。0值禁用基于时间的线程提前特性;典型的或推荐值是1800(30分钟)。
115
116
117 */
----6.创建和管理数据库配置文件 Create and manage database configuration files
1. 文档：
Administrator's Guide -> 2 Creating and Configuring an Oracle Database -> Managing
Initialization Parameters Using a Server Parameter File
Administrator's Guide -> 3 Starting Up and Shutting Down
2. PFILE, SPFILE
In the platform-specific default location, Oracle Database locates your initialization parameter
file by examining file names in the following order:
1) spfile<ORACLE_SID>.ora
2) spfile.ora
3) Init<ORACLE_SID>.ora
6.1从MEMORY，SPFILE或PFILE创建临时PFILE / SPFILE。（Create a temporary PFILE / SPFILE from MEMORY, SPFILE or PFILE.）
1 --6.1从MEMORY，SPFILE或PFILE创建临时PFILE / SPFILE。（Create a temporary PFILE / SPFILE from MEMORY, SPFILE or PFILE.）
2
3 ---检查实例是否从PFILE或SPFILE开始 （Check if the instance started with PFILE or SPFILE）
4
5
6 SQL> show parameter spfile;
7
8 NAME TYPE VALUE
9 ------------------------------------ ----------- ------------------------------
10 spfile string /u01/app/oracle/product/11.2.0
11 /db_1/dbs/spfilePROD1.ora
12
13 ---- Create a PFILE / SPFILE from MEMORY, SPFILE or PFILE
14 -- If not specify the location, the default path is $ORACLE_HOME/dbs
15 ----从内存中创建pfile:
16 SQL> create pfile from memory;
17
18 File created.
19 ---从spfile 中创建pfile;
20 SQL> create pfile from spfile;
21
22 File created.
23
24 /*
25 -- 仅当使用PFILE 启动实例时，生成SPFILE 文件
26 -- 否则出错ORA-32002: cannot create SPFILE already being used by the instance
27 -- create spfile from memory;
28 -- create spfile from pfile;
29 */
30
31 SQL> shutdown immediate
32 Database closed.
33 Database dismounted.
34 ORACLE instance shut down.
35 SQL> startup from pfile;
36 SP2-0714: invalid combination of STARTUP options
37 SQL>
38 SQL> startup pfile='/u01/app/oracle/product/11.2.0/db_1/dbs/initPROD1.ora';
39 ORACLE instance started.
40
41 Total System Global Area 1707446272 bytes
42 Fixed Size 1345408 bytes
43 Variable Size 1040189568 bytes
44 Database Buffers 654311424 bytes
45 Redo Buffers 11599872 bytes
46 Database mounted.
47 Database opened.
48
49 ---从pfile中生成spfile:
50
51 SQL> create spfile from pfile;
52
53 File created.
54
55 ----直接从spfile 启动数据库
56
57 SQL> shutdown immediate;
58
59 SQL> shutdown immediate;
60 Database closed.
61 Database dismounted.
62 ORACLE instance shut down.
63 SQL> startup
64 ORACLE instance started.
65
66 Total System Global Area 1707446272 bytes
67 Fixed Size 1345408 bytes
68 Variable Size 1040189568 bytes
69 Database Buffers 654311424 bytes
70 Redo Buffers 11599872 bytes
71 Database mounted.
72 Database opened.
73
---Specify the path to avoid overwriting the configuration files（指定避免覆盖配置文件的路径）
1 SQL> show parameter pfile;
2
3 NAME TYPE VALUE
4 ------------------------------------ ----------- ------------------------------
5 spfile string /u01/app/oracle/product/11.2.0
6 /db_1/dbs/spfilePROD1.ora
7 SQL> create pfile='/tmp/initPROD1.ora' from memory;
8
9 File created.
10
11 SQL> create pfile='/tmp/initPROD1.ora' from spfile;
12
13 File created.
14
15 SQL> create spfile='/tmp/spfilePROD1.ora' from memory;
16
17 File created.
18
19 SQL> create spfile='/tmp/spfilePROD1.ora' from pfile;
20
21 File created.
22
23
24 SQL> create pfile='/tmp/initPROD1.ora' from spfile='/tmp/spfilePROD1.ora' ;
25
26 File created.
27
28 SQL> create spfile='/tmp/spfilePROD1.ora' from pfile='/tmp/initPROD1.ora' ;
29
30 File created.
31
32 SQL>
1 SQL> shutdown immediate;
2 Database closed.
3 Database dismounted.
4 ORACLE instance shut down.
5 SQL> startup pfile='/tmp/initPROD1.ora' ;
6 ORACLE instance started.
7
8 Total System Global Area 1707446272 bytes
9 Fixed Size 1345408 bytes
10 Variable Size 1040189568 bytes
11 Database Buffers 654311424 bytes
12 Redo Buffers 11599872 bytes
13 Database mounted.
14 Database opened.
15 SQL> show parameter spfile;
16
17 NAME TYPE VALUE
18 ------------------------------------ ----------- ------------------------------
19 spfile string
20 SQL> shutdown immediate;
21 Database closed.
22 Database dismounted.
23 ORACLE instance shut down.
24 SQL> startup
25 ORACLE instance started.
26
27 Total System Global Area 1707446272 bytes
28 Fixed Size 1345408 bytes
29 Variable Size 1040189568 bytes
30 Database Buffers 654311424 bytes
31 Redo Buffers 11599872 bytes
32 Database mounted.
33 Database opened.
34 SQL> show parameter spfile;
35
36 NAME TYPE VALUE
37 ------------------------------------ ----------- ------------------------------
38 spfile string /u01/app/oracle/product/11.2.0
39 /db_1/dbs/spfilePROD1.ora
----修改spfile 参数
1 ----6.2将MEMORY_TARGET参数更改为450M。
2 ---- 6.2 Change the MEMORY_TARGET parameter to 900M.
3
4 SQL> show parameter memory_target
5
6 NAME TYPE VALUE
7 ------------------------------------ ----------- ------------------------------
8 memory_target big integer 1632M
9
10
11 SQL> alter system set memory_target=900m scope=both;
12
13 System altered.
14
15 SQL> alter system set memory_target=900m scope=spfile;
16
17 System altered.
18
19 SQL> show parameter memory_target
20
21 NAME TYPE VALUE
22 ------------------------------------ ----------- ------------------------------
23 memory_target big integer 912M
24 SQL> alter system set memory_target=1632m ;
25
26 System altered.
27
28 -- default is SCOPE=BOTH 默认设置方法
29
30 SQL> show parameter memory_target;
31
32 NAME TYPE VALUE
33 ------------------------------------ ----------- ------------------------------
34 memory_target big integer 1632M
35 SQL>
-----7.创建和管理bigfile表空间 （Create and manage bigfile tablespaces）
1. 文档：
Administrator's Guide -> 14 Managing Tablespaces -> Creating Tablespaces -> Bigfile
Tablespaces
2. BIGFILE TBS
With Only a DATAFILE that may be very large.(只有一个可能非常大的数据文件。)
The maximum size of a BIGFILE TBS datafile is 4G blocks. This means that with a DB of 8K,
the maximum size is 4G blocks * 8K = 32T. The normal TBS datafile is 4M blocks. With a DB of 8K, the maximum size is 32G.
（BIGFILE TBS数据文件的最大大小是4G块。这意味着，在8K的DB中，最大的大小是4G块，8K=32T。正常的TBS数据文件是400万块。使用DB 8K，最大大小为32G。）
Oracle 11gR2, the BIGFILE TBS can only be LOCALLY MANAGED with ASSM. （Oracle 11gR2，BIGFILE TBS只能用ASSM本地管理。）
1 ----7.1创建一个大小为100MB的bigfile tablespace BIGTBS1
2 ---- 7.1 Create a bigfile tablespace BIGTBS1 with size 100MB
3 ----7.2创建一个bigfile表空间BIGTBS2初始大小为100MB，下一个扩展大小为100MB，最大大小为100G
4 ---7.2 Create a bigfile tablespace BIGTBS2 initial size 100MB, next extent size of 100MB with maximum size 100G
5
6
7 SQL> select tablespace_name ,file_name from dba_data_files;
8
9 TABLESPACE_NAME FILE_NAME
10 ---------------------------------------------------------------------------------------
11 USERS /u01/app/oracle/oradata/PROD1/users01.dbf
12 UNDOTBS1 /u01/app/oracle/oradata/PROD1/undotbs01.dbf
13 SYSAUX /u01/app/oracle/oradata/PROD1/sysaux01.dbf
14 SYSTEM /u01/app/oracle/oradata/PROD1/system01.dbf
15 EXAMPLE /u01/app/oracle/oradata/PROD1/example01.dbf
16
17
18 SQL> create bigfile tablespace bigtbs1 datafile '/u01/app/oracle/oradata/PROD1/bigtbs01.dbf' size 100m ;
19
20 Tablespace created.
21
22
23 SQL> create bigfile tablespace bigtbs2 datafile '/u01/app/oracle/oradata/PROD1/bigtbs02.dbf' size 100m autoextend on next 100m maxsize 100g ;
24
25 Tablespace created.
26
27 SQL> select tablespace_name , file_name from dba_data_files;
28
29 TABLESPACE_NAME FILE_NAME
30 ------------------------------ ------------------------------ -------------------
31 USERS /u01/app/oracle/oradata/PROD1/users01.dbf
32 UNDOTBS1 /u01/app/oracle/oradata/PROD1/undotbs01.dbf
33 SYSAUX /u01/app/oracle/oradata/PROD1/sysaux01.dbf
34 SYSTEM /u01/app/oracle/oradata/PROD1/system01.dbf
35 EXAMPLE /u01/app/oracle/oradata/PROD1/example01.dbf
36 BIGTBS1 /u01/app/oracle/oradata/PROD1/bigtbs01.dbf
37 BIGTBS2 /u01/app/oracle/oradata/PROD1/bigtbs02.dbf
38
39 7 rows selected.
---------------更改BIGTBS1 Change the BIGTBS1 size to 200MB
1 ----7.4删除2个表空间。 -----7.4 Drop the 2 tablespaces.
3 SQL> select tablespace_name ,file_name from dba_data_files;
4
5 TABLESPACE_NAME FILE_NAME
6 ------------------------------------------------------------------------------------------
7 USERS /u01/app/oracle/oradata/PROD1/users01.dbf
8 UNDOTBS1 /u01/app/oracle/oradata/PROD1/undotbs01.dbf
9 SYSAUX /u01/app/oracle/oradata/PROD1/sysaux01.dbf
10 SYSTEM /u01/app/oracle/oradata/PROD1/system01.dbf
11 EXAMPLE /u01/app/oracle/oradata/PROD1/example01.dbf
12 BIGTBS1 /u01/app/oracle/oradata/PROD1/bigtbs01.dbf
13 BIGTBS2 /u01/app/oracle/oradata/PROD1/bigtbs02.dbf
14
15 7 rows selected.
16
17 SQL> drop tablespace bigtbs1 including contents and datafiles;
18
19 Tablespace dropped.
20
21 SQL> drop tablespace bigtbs2 including contents and datafiles;
22
23 Tablespace dropped.
24
25 SQL>
1 ---7.3更改BIGTBS1
2 ---7.3 Change the BIGTBS1 size to 200MB
3
4 SQL> select tablespace_name ,file_name from dba_data_files;
5
6 TABLESPACE_NAME FILE_NAME
7 ------------------------------ ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
8 USERS /u01/app/oracle/oradata/PROD1/users01.dbf
9 UNDOTBS1 /u01/app/oracle/oradata/PROD1/undotbs01.dbf
10 SYSAUX /u01/app/oracle/oradata/PROD1/sysaux01.dbf
11 SYSTEM /u01/app/oracle/oradata/PROD1/system01.dbf
12 EXAMPLE /u01/app/oracle/oradata/PROD1/example01.dbf
13
14 SQL> create bigfile tablespace bigtsb1 datafile '/u01/app/oracle/oradata/PROD1/biftsb01.dbf' size 100m autoextend on next 100m maxsize 100g ;
15
16 Tablespace created.
17
18 SQL> SELECT TABLESPACE_NAME,FILE_NAME FROM DBA_DATA_FILES;
19
20 TABLESPACE_NAME FILE_NAME
21 ------------------------------
22 USERS /u01/app/oracle/oradata/PROD1/users01.dbf
23 UNDOTBS1 /u01/app/oracle/oradata/PROD1/undotbs01.dbf
24 SYSAUX /u01/app/oracle/oradata/PROD1/sysaux01.dbf
25 SYSTEM /u01/app/oracle/oradata/PROD1/system01.dbf
26 EXAMPLE /u01/app/oracle/oradata/PROD1/example01.dbf
27 BIGTSB1 /u01/app/oracle/oradata/PROD1/biftsb01.dbf
28
29 6 rows selected.
30
31 SQL> ALTER DATABASE DATAFILE '/u01/app/oracle/oradata/PROD1/biftsb01.dbf' RESIZE 200M ;
32
33 Database altered.
34
--- -创建和管理使用NFS挂载文件系统文件的表空间 Create and Manage a Tablespace that uses NFS Mounted File System File
1. 文档：
Database Installation Guide for Linux -> 5 Oracle Database Postinstallation Tasks -> 5.3.9
Configuring and Using Direct NFS Client
Grid Infrastructure Installation Guide for Linux -> 3 Configuring Storage for Oracle Grid
Infrastructure for a Cluster and Oracle RAC -> 3.2.3 Deciding to Use Direct NFS Client for
Data Files
Enable Direct NFS as follow (启用直接 网络文件系统（NFS），如下:)
1)Enable system normal NFS setting (both server & client) (启用系统正常NFS设置(服务器和客户端： 奇数ip 为 客户端 偶数ip 为 服务端 ))
2)Shutdown the database instances.（关闭数据实例）
3)The turn on the DNFS option from the Oracle kernel (as oracle OS user):（从Oracle内核(作为Oracle OS用户)转向DNFS选项:）
$> cd $ORACLE_HOME/rdbms/lib
$> make -f ins_rdbms.mk dnfs_on
4)Startup the database instances and Create datafiles on the NFS storage. (启动数据库实例，并在NFS存储上创建数据文件)
Disable Direct NFS as follow: (禁用直接NFS，如下:)
1)Drop the datafiles on the NFS storage and Shutdown the database instances.(在NFS存储上删除数据文件，并关闭数据库实例)
2)The turn off the DNFS option from the Oracle kernel (as oracle OS user):(从Oracle内核(作为Oracle OS用户)关闭DNFS选项:)
$> cd $ORACLE_HOME/rdbms/lib
$> make -f ins_rdbms.mk dnfs_off
3)Startup the database instances. (启动数据库实例。)
4)Disable the system normal NFS setting.(禁用系统正常的NFS设置。)
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
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" 命令执行后，调用出图形界面：执行下一步操作
4) Select "Create database" -> Click "Next" 选择创建数据库，然后在执行下一步按钮
5) Select "Custom Database" -> Click "Next" 此处按照要求选择相对应选项。此处我选择的是 第一个选项
其他选项：
General Purpose or Transaction Processing
Data Warehouse
6) Input the "Global Database Name" and SID 输入数据库名和数据库实例名
Global Database Name = "PROD1.oracle.com"
SID = "PROD1"
Click "Next"
7) Deselect "Configure Enterprise Manager" -> Click "Next" ；去掉“配置企业管理器”的选择
注意：如果选择此项，需要先启动监听(Listener)
8) Select "Use the Same Administrative Password for All Account"
Password = "xxxxxx" (例如：oracle)
Confirm Password = "xxxxxx" (例如：oracle)
Click "Next"
把所有用户的密码都设置一样的选择。 考试的时候最后都设置一样的 除掉默认的oracle名称其他的密码；
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"
10) Keep the default settings of Flash/Fast Recovery Area -> Deselect "Enable Archiving" -> Click "Next"
保留Flash（闪存） /快速恢复区域的默认设置 - >取消选择“启用归档” - >单击“下一步”
11) Select "Sample Schema" and keep the default settings of other components -> Click "Next"
选择安装默认组件：完成时间？
取消安装全部组件：完成时间？
12) Select the character set, keep other options by default
Click tab "Character Sets"
Click "Use Unicode (AL32UTF8)
Click "Next"
13) In the window "Database Storage" -> Click "Next"
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)
创建数据库实例并生成数据库生成脚本
15) Confirm the Summary -> Click "OK"
16) Confirm to create the script of DB - > Click "OK"
17) Finish the installation -> Click "Exit"
测试下安装结果：
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
【补充】
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
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
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 的解决》
- 四：删除数据库实例。即：删除数据库
- 1: 手工删除数据库
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
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]--
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（管理撤销）
图解位置：
2. TABLESPACE 分类
locally managed tablespace （本地管理表空间）
- automatic segment space mangement（自动段空间管理）
- manual segment spance mamagement（手动段空间管理）
dictionary managed tablespace (字典管理表空间)
| LOCALLY MANAGED TABLESPACE （本地管理的表空间） | DICTIONARY MANAGED TABLESPACE（字典管理表空间） | |
| AUTOMATIC SEGMENT SPACE MANAGEMENT(ASSM) （自动段空间管理） | MANUAL SEGMENT SPACE MANAGEMENT （手动段空间管理） | The EXTENTS are managed through Data Dictionary （区段是通过数据字典管理的） |
| Use Bitmaps（使用位图） | Use Freelists（使用免费的列表） | Use Dict.（使用字典类型） => SYS.EUT$ , SYS.FET$ |
1 #1)自动段空间管理(ASSM)
2 1) Automatic Segment Space Management (ASSM)
3 #ASSM更有效，是永久的、本地管理的表空间的缺省值。ASSM方法使用位图来管理空间。位图提供如下优势:
4 ASSM is more efficient and is the default for permanent, locally managed tablespaces.The ASSM method uses bitmaps to manage space. Bitmaps provide the following advantages:
5 #l简化管理:ASSM避免了需要手动确定正确设置多少存储参数。只有一个关键的SQL参数控制空间分配:PCTFREE(默认值为10%)。该参数指定在一个块中预留的空间百分比用于将来的更新(“数据块中的空闲空间百分比”)。
6 l 简化管理: ASSM avoids the need to manually determine correct settings for many storage parameters. Only one crucial SQL parameter controls space allocation: PCTFREE (default=10%).This parameter specifies the percentage of space to be reserved in a block for future updates("Percentage of Free Space in Data Blocks").
7 #l提升并发性能:多个事务,可以搜索单独列表的免费数据块,从而减少争用和等待。对于许多标准的工作负载，使用ASSM的应用程序性能要优于使用MSSM的调优应用程序的性能。例如:如果“缓冲区繁忙等待”事件由段头的争用引起，那么它很可能是自由列表争用。如果可能的话，从手动空间管理切换到自动分段空间管理(ASSM)。
8 l 提升并发性能: Multiple transactions can search separate lists of free data blocks, thereby reducing contention and waits. For many standard workloads, application performance with ASSM is better than the performance of a well-tuned application that uses MSSM. For example: If the "buffer busy waits" event caused by contention on the segment header, then it is most likely free list contention. If possible, switch from manual space management to automatic segment-space management (ASSM).
9 #l动态关联:动态空间的亲和力在Oracle实例真正的应用程序集群(Oracle RAC)环境下面的图形的概念表示bitmap-managed存储。header中的1指的是使用的空间，而0指的是空闲空间。
10 l 动态关联：Dynamic affinity of space to instances in an Oracle Real Application Clusters(Oracle RAC) environment The following graphic is a conceptual representation of bitmap-managed storage. A 1 in the header refers to used space, whereas a 0 refers to free space.
1
2 ASSM 的使用限制：
3 l Only for a permanent, locally managed tablespace. #只用于永久的本地管理的表空间。
4 l Cannot specify for the SYSTEM tablespace. #不能为系统表空间指定。
5
1 #2)手动段空间管理(MSSM)
2 #2) Manual Segment Space Management (MSSM)
3 #除了PCTFREE之外，MSSM还需要使用SQL参数来控制空间分配，如pct二手车、frelist和FREELIST组。pct二手(默认=40%)设置当前使用的数据库中必须存在的空间百分比，以便将其放在空闲列表中。例如，如果您在CREATE TABLE语句中设置pct40，那么您就不能将行插入到该段中的一个块中，直到使用了不到40%的块空间。手动管理段空间可能是复杂的。由于难以对空间分配参数进行微调，Oracle强烈建议使用ASSM。在ASSM中，PCTFREE决定是否将一个新行插入到一个块中，但是它不使用空闲列表，并且忽略了pctuse
4 #In addition to PCTFREE, MSSM requires you to control space allocation with SQL parameters such as PCTUSED, FREELISTS, and FREELIST GROUPS. PCTUSED (default=40%) sets the percentage of space that must exist in a currently used block for the database to put it on the free list. For example, if you set PCTUSED to 40 in a CREATE TABLE statement, then you cannot insert rows into a block in the segment until less than 40% of the block space is used. Managing segment space manually can be complex. Because of the difficulty of fine-tuning space allocation parameters, Oracle strongly recommends ASSM. In ASSM, PCTFREE determines whether a new row can be inserted into a block, but it does not use free lists and ignores PCTUSED.
5 #3)Dictionary-Managed表空间
6 #3) Dictionary-Managed Tablespaces
7 #一个字典管理的表空间使用数据字典来管理它的区段。Oracle数据库在数据字典中更新数据表，只要在一定程度上分配或释放数据，就可以重用。数据库在后台执行的SQL为数据库对象获取空间的SQL是递归SQL。频繁使用递归SQL可能会对性能产生负面影响，因为对数据字典的更新必须被序列化。本地管理的表空间是默认的，避免了这种性能问题。
8 #A dictionary-managed tablespace uses the data dictionary to manage its extents. Oracle Database updates tables in the data dictionary whenever an extent is allocated or freed for reuse. The SQL that the database executes in the background to obtain space for database objects is recursive SQL. Frequent use of recursive SQL can have a negative impact on performance because updates to the data dictionary must be serialized. Locally managed tablespaces, which are the default, avoid this performance problem.
1 # #3.1创建一个包含两个临时表空间的临时表空间组，以支持批处理 创建大型索引和分析表格，使用以下规格：
2 # 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:
3 # #3.1.1临时表空间组名为TEMP_GRP，包含临时表空间TEMP和TEMP2。
4 # 3.1.1 Temporary tablespace group named TEMP_GRP containing temporary tablespaces TEMP and TEMP2.
5 # #3.1.2使TEMP_GRP成为所有新用户的默认临时表空间。
6 # 3.1.2 Make TEMP_GRP the default temporary tablespace for all new users.
7 # #3.1.3将默认临时表空间更改为TEMP。
8 # 3.1.3 Change back the default temporary tablespace to TEMP.
9 # #3.1.4将临时表空间TEMP扩展到200MB，然后将其大小减小到100MB。
10 # 3.1.4 Extend the temporary tablespace TEMP to 200MB, then cut its size to 100MB.
11 # #3.1.5使用语句检查临时用法：select a.object_id from dba_objects a,dba_objects b order by object_id;
12 # 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;
13 # #3.1.6将临时表空间剪切到最小大小。
14 # 3.1.6 Cut the temporary tablespace to the minimum size.
1 #3. 建立permanent TABLESPACE
2 # 1) 建立普通表空间
3 #-- 默认设定 segment space management auto（段空间管理自动） + extent management local autoallocate（范围管理局部自动分配）
4 #自动分配:由最小区段大小为64K的系统管理
5 #-- AUTOALLOCATE: managed by system with a minimum extent size of 64K
6 -------------------------------------------------------------------------------------------------------------------
7 -- #3.2创建以下永久表空间来存储样本测试数据。使用以下规格：
8 -- 3.2 Create the following permanent tablespace to store sample test data. Use the following specifications:
9 ------------------------------------------------------------------------------------------------------------------
10 -- #3.2.1表空间TEST01，初始数据文件大小为100MB
11 -- 3.2.1 Tablespace TEST01, initial datafile size 100MB
12 SQL> create tablespace TEST01 datafile '/u01/app/oracle/oradata/PROD1/test01.dbf' size 100M;
13
14 Tablespace created.
15
16 SQL> select file_name,tablespace_name from dba_data_files;
17
18 FILE_NAME TABLESPACE_NAME
19 ----------------------------------------------------------------------------
20 /u01/app/oracle/oradata/PROD1/users01.dbf USERS
21 /u01/app/oracle/oradata/PROD1/undotbs01.dbf UNDOTBS1
22 /u01/app/oracle/oradata/PROD1/sysaux01.dbf SYSAUX
23 /u01/app/oracle/oradata/PROD1/system01.dbf SYSTEM
24 /u01/app/oracle/oradata/PROD1/example01.dbf EXAMPLE
25 /u01/app/oracle/oradata/PROD1/test01.dbf TEST01
26
27 6 rows selected.
28
29 -- #3.2.2表空间TEST02，初始数据文件大小为100MB，初始扩展大小为1MB，下一个扩展区大小为1MB）
30 --- 3.2.2 Tablespace TEST02, initial datafile size 100MB, Initial extent size of 1MB, next extent size of 1MB)
31
32
33 SQL> create tablespace TEST02 datafile '/u01/app/oracle/oradata/PROD1/test02.dbf' size 100m segment space management auto extent management local uniform size 1m;
34
35 Tablespace created.
36
37 SQL> select file_name ,tablespace_name from dba_data_files;
38
39 FILE_NAME TABLESPACE_NAME
40 --------------------------------------------------------------------------
41 /u01/app/oracle/oradata/PROD1/users01.dbf USERS
42 /u01/app/oracle/oradata/PROD1/undotbs01.dbf UNDOTBS1
43 /u01/app/oracle/oradata/PROD1/sysaux01.dbf SYSAUX
44 /u01/app/oracle/oradata/PROD1/system01.dbf SYSTEM
45 /u01/app/oracle/oradata/PROD1/example01.dbf EXAMPLE
46 /u01/app/oracle/oradata/PROD1/test01.dbf TEST01
47 /u01/app/oracle/oradata/PROD1/test02.dbf TEST02
48
49 7 rows selected.
50
51 SQL>
52
53 ---- #3.2.3表空间TEST03，初始数据文件大小为100MB，带freelist的段管理
54 --- 3.2.3 Tablespace TEST03, initial datafile size 100MB, segment management with freelist
55
56 SQL> create tablespace TEST03 datafile '/u01/app/oracle/oradata/PROD1/test03.dbf' size 100m segment space management manual extent management local autoallocate;
57
58 Tablespace created.
59
60 SQL> select tablespace_name,file_name from dba_data_files;
61
62 TABLESPACE_NAME FILE_NAME
63 ------------------------------ -------------------------------------------------------------
64 USERS /u01/app/oracle/oradata/PROD1/users01.dbf
65 UNDOTBS1 /u01/app/oracle/oradata/PROD1/undotbs01.dbf
66 SYSAUX /u01/app/oracle/oradata/PROD1/sysaux01.dbf
67 SYSTEM /u01/app/oracle/oradata/PROD1/system01.dbf
68 EXAMPLE /u01/app/oracle/oradata/PROD1/example01.dbf
69 TEST01 /u01/app/oracle/oradata/PROD1/test01.dbf
70 TEST02 /u01/app/oracle/oradata/PROD1/test02.dbf
71 TEST03 /u01/app/oracle/oradata/PROD1/test03.dbf
72
73 8 rows selected.
74
75 SQL>
76
77 ---- #3.2.4表空间TEST04，初始数据文件大小为100MB，下一个扩展大小为100MB，最大大小为2G
78 ---- 3.2.4 Tablespace TEST04, initial datafile size 100MB, next extent size of 100MB with maximum size of 2G
79
80
81 SQL> create tablespace TEST04 datafile '/u01/app/oracle/oradata/PROD1/test04.dbf' size 100m autoextend on next 100m maxsize 2G;
82
83 Tablespace created.
84
85 SQL> select tablespace_name ,file_name from dba_data_files;
86
87 TABLESPACE_NAME FILE_NAME
88 ------------------------------------------------------------------------------------------------
89 USERS /u01/app/oracle/oradata/PROD1/users01.dbf
90 UNDOTBS1 /u01/app/oracle/oradata/PROD1/undotbs01.dbf
91 SYSAUX /u01/app/oracle/oradata/PROD1/sysaux01.dbf
92 SYSTEM /u01/app/oracle/oradata/PROD1/system01.dbf
93 EXAMPLE /u01/app/oracle/oradata/PROD1/example01.dbf
94 TEST01 /u01/app/oracle/oradata/PROD1/test01.dbf
95 TEST02 /u01/app/oracle/oradata/PROD1/test02.dbf
96 TEST03 /u01/app/oracle/oradata/PROD1/test03.dbf
97 TEST04 /u01/app/oracle/oradata/PROD1/test04.dbf
98
99 9 rows selected.
100
101 SQL>
102 --检查TBS属性
103 SQL> select tablespace_name ,block_size ,extent_management,segment_space_management ,allocation_type,bigfile from dba_tablespaces;
104
105 TABLESPACE_NAME BLOCK_SIZE EXTENT_MAN FR ALLOCATIO BIG
106 ------------------------------ ---------- ---------- ------ --------- ---
107 SYSTEM 8192 LOCAL MANUAL SYSTEM NO
108 SYSAUX 8192 LOCAL AUTO SYSTEM NO
109 UNDOTBS1 8192 LOCAL MANUAL SYSTEM NO
110 TEMP 8192 LOCAL MANUAL UNIFORM NO
111 USERS 8192 LOCAL AUTO SYSTEM NO
112 EXAMPLE 8192 LOCAL AUTO SYSTEM NO
113 TEST01 8192 LOCAL AUTO SYSTEM NO
114 TEST02 8192 LOCAL AUTO UNIFORM NO
115 TEST03 8192 LOCAL MANUAL SYSTEM NO
116 TEST04 8192 LOCAL AUTO SYSTEM NO
117
118 10 rows selected.
119
1--3.2.9在表空间TEST01中创建2个表：TEST_PCTFREE_0和TEST_PCTFREE_50。将dba_objects记录插入到它们中;检查2个表：记录数，块，平均空间和空格。
--- 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. 2 --检查两个表，其中有不同的PCTFREE值。
3 --- Check two tables with different values for PCTFREE.
4 create table test_pctfree_0 tablespace test01 pctfree 0 as select * from dba_objects;
5 create table test_pctfree_50 tablespace test01 pctfree 50 as select * from dba_objects;
6 -- Statistics tables 统计数据表 analyze 分析 compute statistics 计算统计数据;
7 analyze table test_pctfree_0 compute statistics;
8 analyze table test_pctfree_50 compute statistics;
9 -- Check as different values for PCTFREE can cause different occupations for a table
10
11 SQL> select table_name, num_rows, blocks, avg_space, pct_free from dba_tables where table_name in ('TEST_PCTFREE_0','TEST_PCTFREE_50');
12
13 TABLE_NAME NUM_ROWS BLOCKS AVG_SPACE PCT_FREE
14 ------------------------------ ---------- ---------- ---------- ----------
15 TEST_PCTFREE_0 75185 987 65 0
16 TEST_PCTFREE_50 50
17
18 SQL>
19
20
21
1
2 ---# #3.2.5具有OMF位置的表空间TEST05
3 ---# 3.2.5 Tablespace TEST05 with the OMF location
4 --1： Check the OMF setting （检查OMF设置）
5 SQL> show parameter db_create_file_dest;
6
7 NAME TYPE VALUE
8 ------------------------------------ ----------- ------------------------------
9 db_create_file_dest string
10 SQL>
11 -- The datafiles will be created under the directory as "db_create_file_dest"/PROD1/datafile/*.dbf
12
13 SQL> alter system set db_create_file_dest='/u01/app/oracle/oradata' scope=both;
14
15 System altered.
16
17 SQL> show parameter db_create_file_dest;
18
19 NAME TYPE VALUE
20 ------------------------------------ ----------- ------------------------------
21 db_create_file_dest string /u01/app/oracle/oradata
22 SQL>
23
24 --Create a TBS with OMF, an auto-extending datafile init 100M and 32GB maximum size （使用OMF创建一个TBS，自动扩展数据文件初始化1m和32GB的最大大小）
25 SQL> create tablespace test05;
26
27 Tablespace created.
28
29 SQL>
30
31 -- Check the created DATAFILE features (检查创建的DATAFILE特性)
32
33 SQL> col file_name format a600;
34 SQL> select file_name ,bytes/1024/1024 ||'MB' ,autoextensible,maxbytes/1024/1024 ||'MB' from dba_data_files where tablespace_name='TEST05';
35
36 FILE_NAME BYTES/1024/1024||'MB' autoextensible MAXBYTES/1024/1024||'MB'
37 -------------------------------------------------------------------------------------------------------------------------------------------------------------
38 /u01/app/oracle/oradata/PROD1/datafile/o1_mf_test05_dzx2hwco_.dbf 100MB YES 32767.984375MB
39
1 --建立非默认blocksize 表空间
2 ---# #3.2.6具有OMF(Oracle Managed Files:Oracle管理文件)位置的表空间TEST06，16k块大小。
3 --# 3.2.6 Tablespace TEST06 with the OMF location, 16k blocksize.
4 SQL> show parameter cache_size;
5
6 NAME TYPE VALUE
7 ------------------------------------ ----------- ------------------------------
8 client_result_cache_size big integer 0
9 db_16k_cache_size big integer 0
10 db_2k_cache_size big integer 0
11 db_32k_cache_size big integer 0
12 db_4k_cache_size big integer 0
13 db_8k_cache_size big integer 0
14 db_cache_size big integer 0
15 db_flash_cache_size big integer 0
16 db_keep_cache_size big integer 0
17 db_recycle_cache_size big integer 0
18 SQL> alter system set db_16k_cache_size=50m scope=both;
19
20 System altered.
21
22 SQL> create tablespace test06 blocksize 16k;
23
24 Tablespace created.
25
26 SQL> select file_name,bytes/1024/1024 mb,autoextensible,maxbytes/1024/1024 maxmb from dba_data_files where tablespace_name like 'TEST%';
27
28 FILE_NAME MB AUT MAXMB
29 ----------------------------------------------------------------------------------------------
30 /u01/app/oracle/oradata/PROD1/test01.dbf 100 NO 0
31 /u01/app/oracle/oradata/PROD1/test02.dbf 100 NO 0
32 /u01/app/oracle/oradata/PROD1/test03.dbf 100 NO 0
33 /u01/app/oracle/oradata/PROD1/test04.dbf 100 YES 2048
34
35 /u01/app/oracle/oradata/PROD1/datafile/o1_mf_test05_dzx2hwco_.dbf 100 YES 32767.9844
36
37 /u01/app/oracle/oradata/PROD1/datafile/o1_mf_test06_dzx38p3k_.dbf 100 YES 65535.9688
38
39
40 6 rows selected.
41
42 SQL>
43
1 --建立BIGFILE 表空间.
2 --Bigfile 表空间的优势是可以使用一个超大数据文件(datafile)，数据文件使用8k blocksize 可达32TB.
3
4 -- #3.2.7表空间TEST07 for bigfile，初始数据文件大小为100MB，文件预期增长到4TB
5 -- 3.2.7 Tablespace TEST07 for bigfile, initial datafile size 100MB with the file expected to grow to 4TB
6
7
8 SQL> create bigfile tablespace test07 datafile '/u01/app/oracle/oradata/PROD1/test07.dbf' size 100m ;
9
10 Tablespace created--- 3.2.8检查上述表空间：名称，数据文件名，块大小，extent_management，段空间管理，分配类型，bigfile
--- 3.2.8 Check the above tablespaces: name, datafile name, block size, extent_management, segment space management, allocation type, bigfile
11 SQL> select tablespace_name, block_size, extent_management, segment_space_management,allocation_type, bigfile from dba_tablespaces;
12
13 TABLESPACE_NAME BLOCK_SIZE EXTENT_MAN SEGMEN ALLOCATIO BIG
14 ------------------------------ ---------- ---------- ------ --------- ---
15 SYSTEM 8192 LOCAL MANUAL SYSTEM NO
16 SYSAUX 8192 LOCAL AUTO SYSTEM NO
17 UNDOTBS1 8192 LOCAL MANUAL SYSTEM NO
18 TEMP 8192 LOCAL MANUAL UNIFORM NO
19 USERS 8192 LOCAL AUTO SYSTEM NO
20 EXAMPLE 8192 LOCAL AUTO SYSTEM NO
21 TEST01 8192 LOCAL AUTO SYSTEM NO
22 TEST02 8192 LOCAL AUTO UNIFORM NO
23 TEST03 8192 LOCAL MANUAL SYSTEM NO
24 TEST04 8192 LOCAL AUTO SYSTEM NO
25 TEST05 8192 LOCAL AUTO SYSTEM NO
26 TEST06 16384 LOCAL AUTO SYSTEM NO
27 TEST07 8192 LOCAL AUTO SYSTEM YES
28
29 13 rows selected.
30
31 SQL>
----还原环境：
----3.2.10 Drop the above tablespaces and clear the environment settings (cache size, OMF path)
1 ---5) 恢复环境.
2 SQL> drop tablespace test01 including contents and datafiles;
3
4 Tablespace dropped.
5
6 SQL> drop tablespace test02 including contents and datafiles;
7
8 Tablespace dropped.
9
10 SQL> drop tablespace test03 including contents and datafiles;
11
12 Tablespace dropped.
13
14 SQL> drop tablespace test04 including contents and datafiles;
15
16 Tablespace dropped.
17
18 SQL> drop tablespace test05 including contents and datafiles;
19
20 Tablespace dropped.
21
22 SQL> drop tablespace test06 including contents and datafiles;
23
24 Tablespace dropped.
25
26 SQL> drop tablespace test07 including contents and datafiles;
27
28 Tablespace dropped.
29
30 SQL> alter system reset db_16k_cache_size scope=spfile;
31
32 System altered.
33
34 SQL> alter system set db_16k_cache_size=0 scope=memory;
35
36 System altered.
37
38 SQL> alter system reset db_create_file_dest scope=spfile;
39
40 System altered.
41
42 SQL> alter system set db_create_file_dest='' scope=memory;
43
44 System altered.
45
46 SQL>
1 --4. 临时表空间(TEMPORARY TABLESPACE).
2 -- Check the default temporary TBS (检查默认的临时TBS)
3
4 SQL> col property_name format a30;
5 SQL> col propetty_value format a20;
6 SQL> select property_name,property_value from database_properties where property_name like 'DEFAULT_TEMP_TABLESPACE';
7
8 PROPERTY_NAME PROPERTY_VALUE
9 --------------------------------------------------------------------------------------------------------------
10 DEFAULT_TEMP_TABLESPACE TEMP
11
12 SQL>
13
14 --1）建立TEMP TBS group
15 --将当前临时TBS(TEMP)添加到一个新组(TEMPGROUP)
16 -- Add the current temporary TBS (TEMP) to a new group (TEMPGROUP)
17 SQL> alter tablespace temp tablespace group tempgroup;
18
19 Tablespace altered.
20 --- 检查结果：
21 SQL> select * from dba_tablespace_groups;
22
23 GROUP_NAME TABLESPACE_NAME
24 ------------------------------ ------------------------------
25 TEMPGROUP TEMP
26
27 SQL>
28
29 --创建第二个临时TBS，并将其添加到相同的组中（Create a 2nd temporary TBS, and add it to the same group）
30
31 SQL> create temporary tablespace temp2 tempfile '/u01/app/oracle/oradata/PROD1/temp201.dbf' size 100m tablespace group tempgroup;
32
33 Tablespace created.
34
35 SQL>
36 --检查下执行结果
37 SQL> select * from dba_tablespace_groups;
38
39 GROUP_NAME TABLESPACE_NAME
40 ------------------------------ ------------------------------
41 TEMPGROUP TEMP
42 TEMPGROUP TEMP2
43
44 SQL>
45
46 --将临时TBS组更改为缺省临时TBS（Change the group of temporary TBS as the default temporary TBS）
47 SQL> alter database default temporary tablespace tempgroup;
48
49 Database altered.
50 SQL> select property_name,property_value from database_properties where property_name like 'DEFAULT_TEMP_TABLESPACE';
51
52 PROPERTY_NAME PROPERTY_VALUE
53 --------------------------------------------------------------------------------------------
54
55 DEFAULT_TEMP_TABLESPACE TEMPGROUP
56
57
58 SQL>
59
60 -- 恢复环境
61 SQL> alter tablespace temp tablespace group '';
62
63 Tablespace altered.
64 SQL> alter database default temporary tablespace temp;
65
66 Database altered.
67
68 SQL> drop tablespace temp2 including contents and datafiles;
69
70 Tablespace dropped.
71
72 SQL>
73
74
75
76 ---2）收缩TEMP TBS
77 -- 增大TEMP TBS
78
79 SQL> alter database tempfile '/u01/app/oracle/oradata/PROD1/temp01.dbf' resize 200m;
80
81 Database altered.
82
83 SQL> select bytes/1024/1024 from dba_temp_files;
84
85 BYTES/1024/1024
86 ---------------
87 200
88
89
90 -- 收缩TEMP TBS 至100MB
91 SQL> alter tablespace temp shrink space keep 100m;
92
93 Tablespace altered.
94
95 SQL> select bytes/1024/1024 from dba_temp_files;
96
97 BYTES/1024/1024
98 ---------------
99 101
100 -- 收缩至最小值
101 SQL> alter tablespace temp shrink space;
102
103 Tablespace altered.
104
105 SQL> select bytes/1024/1024 from dba_temp_files;
106
107 BYTES/1024/1024
108 ---------------
109 25.9921875
110
1 ---5. UNDO TABLESPACE
2
3
4 ---1）Check the UNDO parameters 1)检查撤销参数
5 -- 它是默认值。Oracle管理手动模式回滚段中的撤消eg分段，不推荐使用
6 -- UNDO_MANAGEMENT = AUTO => It is the default value. Oracle manages undo egments
7 In MANUAL mode ROLLBACK segments are used -> not recommended
8
9 -- UNDO_RETENTION = 900 => The time Oracle TRYING to retain info in UNDO (秒)
10 -- UNDO_TABLESPACE = UNDOTBS1 => If not specified, Oracle uses the first one it finds
11
12
13 SQL> show parameter undo
14
15 NAME TYPE VALUE
16 ------------------------------------ ----------- ------------------------------
17 undo_management string AUTO
18 undo_retention integer 900
19 undo_tablespace string UNDOTBS1
20 SQL>
21
22 ---2）Set the size of UNDO TBS. （设置 undo TBS的大小。）
23
24
25 SQL> select file_name from dba_data_files;
26
27 FILE_NAME
28 --------------------------------------------------------------------------------
29 /u01/app/oracle/oradata/PROD1/users01.dbf
30 /u01/app/oracle/oradata/PROD1/undotbs01.dbf
31 /u01/app/oracle/oradata/PROD1/sysaux01.dbf
32 /u01/app/oracle/oradata/PROD1/system01.dbf
33 /u01/app/oracle/oradata/PROD1/example01.dbf
34
35 SQL> alter database datafile '/u01/app/oracle/oradata/PROD1/undotbs01.dbf' autoextend on;
36
37 Database altered.
38 -- Set the retention(保留) to 5 hours (5h * 60min * 60seg = 18000)
39 -- If UNDO TBS DATAFILES are not in AUTOEXTEND mode, the UNDO_RETENTION parameter is ignored
40 --如果 undo表空间数据文件不存在自动扩展，则会自动忽略undo滞留参数
41 SQL> alter system set undo_retention=18000 scope=both;
42
43 System altered.
44
1 -- When get an error, use the "OERR" utility to get more information the error message
2 --!oerr ora 1555 # Suggest increasing the parameter UNDO_RETENTION or extend the UNDO
3 --!oerr ora 30036 # Suggest increasing the UNDO TBS
4 /*
5 ——当出现错误时，使用“OERR”工具获取更多信息，错误消息
6 ！oerr ora 1555建议增加参数不保留或扩展撤销
7 ！oerr ora 30036建议增加撤消TBS
8
9 */
10 SQL> alter tablespace undotbs1 retention guarantee;
11
12 Tablespace altered.
13
14 SQL> alter tablespace undotbs1 retention noguarantee;
15
16 Tablespace altered.
17
18 SQL>
1 -- #3.3在数据库中设置自动撤销管理，支持以下要求：-- 3.3 Set up automatic undo management in the database to support the following requirements:-- #3.3.1避免ORA-01555对于平均5个小时的查询，快照太旧错误。-- 3.3.1 Avoid ORA-01555 Snapshot too old errors for queries running up to 5 hours on average.-- #3.3.2检查不同状态的UNDO的使用情况。-- 3.3.2 Check the usage of UNDO with different status.-- #3.3.3将UNDO更改为tablespace undotbs2，然后更改为tablespace undotbs1。-- 3.3.3 Change the UNDO to tablespace undotbs2, then change back to tablespace undotbs1.-- #3.3.4向表空间undotbs1添加一个新的datafile undotbs02.dbf（size = 100MB，扩展大小为10MB），然后删除新的撤消数据文件。-- 3.3.4 Add a new datafile undotbs02.dbf (size=100MB, extent size of 10MB) to tablespace undotbs1, then drop the new undo datafile.-- #3.4检查所有表空间（永久，临时）及其数据文件：文件＃，文件名，表空间名称-- 3.4 Check all the tablespaces (permanent, temporary) and their datafiles: file#,file name,tablespace name ---4) 变更UNDO TBS
2 -- 创建一个新的 UNDO 表空间 (Create a new UNDO TBS)
3
4 SQL> select file_name from dba_data_files;
5
6 FILE_NAME
7 --------------------------------------------------------------------------------
8 /u01/app/oracle/oradata/PROD1/users01.dbf
9 /u01/app/oracle/oradata/PROD1/undotbs01.dbf
10 /u01/app/oracle/oradata/PROD1/sysaux01.dbf
11 /u01/app/oracle/oradata/PROD1/system01.dbf
12 /u01/app/oracle/oradata/PROD1/example01.dbf
13 ----创建undo表空间2 初始化大小为100M 每次增长1oM 无最大限制。
14 SQL> create undo tablespace undotbs2 datafile '/u01/app/oracle/oradata/PROD1/undotbs02.dbf' size 100m autoextend on next 10m maxsize unlimited;
15
16 Tablespace created.
17
18 SQL> show parameter undo_t
19
20 NAME TYPE VALUE
21 ------------------------------------ ----------- ------------------------------
22 undo_tablespace string UNDOTBS1
23
24 SQL> alter system set undo_tablespace=undotbs2 scope=both;
25
26 System altered.
27
28 SQL> show parameter undo_tablespace
29
30 NAME TYPE VALUE
31 ------------------------------------ ----------- ------------------------------
32 undo_tablespace string UNDOTBS2
33 SQL>
34
35
36 -- Delete the old TBS 删除旧的 表空间
37 SQL> drop tablespace undotbs1 including contents and datafiles;
38
39 Tablespace dropped.
40
41 SQL> select file_name from dba_data_files;
42
43 FILE_NAME
44 --------------------------------------------------------------------------------
45 /u01/app/oracle/oradata/PROD1/users01.dbf
46 /u01/app/oracle/oradata/PROD1/sysaux01.dbf
47 /u01/app/oracle/oradata/PROD1/system01.dbf
48 /u01/app/oracle/oradata/PROD1/example01.dbf
49 /u01/app/oracle/oradata/PROD1/undotbs02.dbf
50
51 -- Reset the old UNDO TBS DATAFILE 重置旧的 undo TBS数据文件
52
53 SQL> select file_name from dba_data_files;
54
55 FILE_NAME
56 --------------------------------------------------------------------------------
57 /u01/app/oracle/oradata/PROD1/users01.dbf
58 /u01/app/oracle/oradata/PROD1/sysaux01.dbf
59 /u01/app/oracle/oradata/PROD1/system01.dbf
60 /u01/app/oracle/oradata/PROD1/example01.dbf
61 /u01/app/oracle/oradata/PROD1/undotbs02.dbf
62
63 SQL> create undo tablespace undotbs1 datafile '/u01/app/oracle/oradata/PROD1/undotbs01.dbf' size 100m autoextend on next 10m maxsize unlimited;
64
65 Tablespace created.
66
67 SQL> select file_name from dba_data_files;
68
69 FILE_NAME
70 --------------------------------------------------------------------------------
71 /u01/app/oracle/oradata/PROD1/users01.dbf
72 /u01/app/oracle/oradata/PROD1/undotbs01.dbf
73 /u01/app/oracle/oradata/PROD1/sysaux01.dbf
74 /u01/app/oracle/oradata/PROD1/system01.dbf
75 /u01/app/oracle/oradata/PROD1/example01.dbf
76 /u01/app/oracle/oradata/PROD1/undotbs02.dbf
77
78 6 rows selected.
79
80 SQL> show parameter undo_tablespace;
81
82 NAME TYPE VALUE
83 ------------------------------------ ----------- ------------------------------
84 undo_tablespace string UNDOTBS2
85 SQL> alter system set undo_tablespace=undotbs1 scope=both;
86
87 System altered.
88
89 SQL> show parameter undo_tablespace
90
91 NAME TYPE VALUE
92 ------------------------------------ ----------- ------------------------------
93 undo_tablespace string UNDOTBS1
94
95 SQL> drop tablespace undotbs2 including contents and datafiles;
96
97 Tablespace dropped.
98
99 SQL> select file_name from dba_data_files;
100
101 FILE_NAME
102 --------------------------------------------------------------------------------
103 /u01/app/oracle/oradata/PROD1/users01.dbf
104 /u01/app/oracle/oradata/PROD1/undotbs01.dbf
105 /u01/app/oracle/oradata/PROD1/sysaux01.dbf
106 /u01/app/oracle/oradata/PROD1/system01.dbf
107 /u01/app/oracle/oradata/PROD1/example01.dbf
108
109
110 -- Add/Delete an UNDO datafile (新增/删除 undo 表空间数据文件)
111
112
113 SQL> select file_name from dba_data_files;
114
115 FILE_NAME
116 --------------------------------------------------------------------------------
117 /u01/app/oracle/oradata/PROD1/users01.dbf
118 /u01/app/oracle/oradata/PROD1/undotbs01.dbf
119 /u01/app/oracle/oradata/PROD1/sysaux01.dbf
120 /u01/app/oracle/oradata/PROD1/system01.dbf
121 /u01/app/oracle/oradata/PROD1/example01.dbf
122
123 SQL> alter tablespace undotbs1 add datafile '/u01/app/oracle/oradata/PROD1/undotbs01_1.dbf' size 100m autoextend on next 10m maxsize unlimited;
124
125 Tablespace altered.
126
127 SQL> select file_name from dba_data_files;
128
129 FILE_NAME
130 --------------------------------------------------------------------------------
131 /u01/app/oracle/oradata/PROD1/users01.dbf
132 /u01/app/oracle/oradata/PROD1/undotbs01.dbf
133 /u01/app/oracle/oradata/PROD1/sysaux01.dbf
134 /u01/app/oracle/oradata/PROD1/system01.dbf
135 /u01/app/oracle/oradata/PROD1/example01.dbf
136 /u01/app/oracle/oradata/PROD1/undotbs01_1.dbf
137
138 6 rows selected.
139
140 SQL> alter tablespace undotbs1 drop datafile '/u01/app/oracle/oradata/PROD1/undotbs01_1.dbf' ;
141
142 Tablespace altered.
143
144 SQL> select file_name from dba_data_files;
145
146 FILE_NAME
147 --------------------------------------------------------------------------------
148 /u01/app/oracle/oradata/PROD1/users01.dbf
149 /u01/app/oracle/oradata/PROD1/undotbs01.dbf
150 /u01/app/oracle/oradata/PROD1/sysaux01.dbf
151 /u01/app/oracle/oradata/PROD1/system01.dbf
152 /u01/app/oracle/oradata/PROD1/example01.dbf
153
154 --6. Check the TBS and their datafiles
155 SQL> set linesize 5000;
156 SQL> col file_name format a50;
157 SQL> select file_name,file_id,tablespace_name from dba_data_files;
158
159 FILE_NAME FILE_ID TABLESPACE_NAME
160 -------------------------------------------------- ---------- ------------------------------
161 /u01/app/oracle/oradata/PROD1/users01.dbf 4 USERS
162 /u01/app/oracle/oradata/PROD1/undotbs01.dbf 3 UNDOTBS1
163 /u01/app/oracle/oradata/PROD1/sysaux01.dbf 2 SYSAUX
164 /u01/app/oracle/oradata/PROD1/system01.dbf 1 SYSTEM
165 /u01/app/oracle/oradata/PROD1/example01.dbf 5 EXAMPLE
166
1 #--104 - Stripe Data Files Across Multiple Physical Devices and Locations （跨多个物理设备和位置的条带数据文件）
2 /*
3 1:在文档位置：
4 Performance Tuning Guide -> 8 I/O Configuration and Design
5
6
7 #4.跨多个物理设备和位置条带数据文件 4. Stripe data files across multiple physical devices and locations #4.1确认路径的现有路径，并将权限设置为user oracle。 #Dir1：/ u01 / app / oracle / oradata / PROD1 #Dir2：/ u02 / app / oracle / oradata / PROD2 4.1 Confirm the existing of the path and set the privileges to user oracle. Dir1: /u01/app/oracle/oradata/PROD1 Dir2: /u02/app/oracle/oradata/PROD2*/
8 2. 建立数据文件目录，模拟不同的磁盘
9 -- Create the directory as root and give permissions
10 [root@oracle ~]# mkdir -p /u02/app/oracle/oradata/PROD1
11 [root@oracle ~]# chown -R oracle:oinstall /u02/app/oracle
12 [root@oracle ~]# chmod -R 775 /u02/app/oracle
1 /*
2 4.1 Confirm the existing of the path and set the privileges to user oracle.
3 Dir1: /u01/app/oracle/oradata/PROD1
4 Dir2: /u02/app/oracle/oradata/PROD2
5 #4.2将重做日志文件重新映射到上述两个路径中的每组3组和2个成员
6 4.2 Recreate the redo log files to 3 groups and 2 members per group located to the above two paths
7 */
8 [root@oracle ~]# su - oracle
9 [oracle@oracle ~]$ export ORACLE_SID=PROD1
10 [oracle@oracle ~]$ sqlplus / as sysdba;
11
12 SQL*Plus: Release 11.2.0.3.0 Production on Sat Nov 11 12:08:58 2017
13
14 Copyright (c) 1982, 2011, Oracle. All rights reserved.
15
16
17 Connected to:
18 Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
19 With the Partitioning, OLAP, Data Mining and Real Application Testing options
20
21 SQL> set linesize 30000;
22
23 SQL> select * from v$log;
24
25 GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
26 ---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ ---------
27 1 1 10 52428800 512 1 YES INACTIVE 922570 05-NOV-17 944610 11-NOV-17
28 2 1 8 52428800 512 1 YES INACTIVE 901908 04-NOV-17 914559 05-NOV-17
29 3 1 9 52428800 512 1 YES INACTIVE 914559 05-NOV-17 922570 05-NOV-17
30 4 1 11 104857600 512 2 NO CURRENT 944610 11-NOV-17 2.8147E+14
31
32 SQL> col member format a100;
33 SQL> select * from v$logfile;
34
35 GROUP# STATUS TYPE MEMBER IS_
36 ---------- ------- ------- ---------------------------------------------------------------------------------------------------- ---
37 3 ONLINE /u01/app/oracle/oradata/PROD1/redo03.log NO
38 2 ONLINE /u01/app/oracle/oradata/PROD1/redo02.log NO
39 1 ONLINE /u01/app/oracle/oradata/PROD1/redo01.log NO
40 4 ONLINE /u01/app/oracle/oradata/PROD1/redo401.log NO
41 4 ONLINE /u01/app/oracle/oradata/PROD1/redo402.log NO
42 ---查询系统使用的是哪一组日志文件：
43 /*
44 可以通过v$log视图来查看日志文件的状态。
45 状态有以下几种：current/active/inactive/unused
46 current：当前的日志文件，该日志文件是活动的，当前正在被使用的，在进行崩溃恢复时，current日志文件是必须的
47 active：活动的非当前日志，该日志可能已经完成归档，也可能没有归档，活动的日志文件在crash恢复时会被用到
48 inactive：非活动日志，该日志在实例恢复时不再需要，但是在介质恢复时可能会用到。inactive状态的日志也可能没有被归档。如果数据库在归档模式，在未完成归档之前，日志文件也不允许被覆盖，这时候活动进程会处于Log File Switch（Archiving Needed）等待之中。
49 unused：日志从未被写入，这类日志可能是刚被添加到数据库或者在Reset Logs之后被重置。被使用之后，该状态会被改变。
50 */
51
52 SQL> select * from v$log;
53
54 GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
55 ---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ ---------
56 1 1 10 52428800 512 1 YES INACTIVE 922570 05-NOV-17 944610 11-NOV-17
57 2 1 8 52428800 512 1 YES INACTIVE 901908 04-NOV-17 914559 05-NOV-17
58 3 1 9 52428800 512 1 YES INACTIVE 914559 05-NOV-17 922570 05-NOV-17
59 4 1 11 104857600 512 2 NO CURRENT 944610 11-NOV-17 2.8147E+14
60
61 SQL> col member format a100;
62 --- 查询正在使用的组所对应的日志文件：
63 SQL> select * from v$logfile;
64
65 GROUP# STATUS TYPE MEMBER IS_
66 ---------- ------- ------- ---------------------------------------------------------------------------------------------------- ---
67 3 ONLINE /u01/app/oracle/oradata/PROD1/redo03.log NO
68 2 ONLINE /u01/app/oracle/oradata/PROD1/redo02.log NO
69 1 ONLINE /u01/app/oracle/oradata/PROD1/redo01.log NO
70 4 ONLINE /u01/app/oracle/oradata/PROD1/redo401.log NO
71 4 ONLINE /u01/app/oracle/oradata/PROD1/redo402.log NO
72 ----新增日志文件组 5
73 SQL> alter database add logfile group 5 ('/u01/app/oracle/oradata/PROD1/redo501.log','/u02/app/oracle/oradata/PROD1/redo502.log') size 100m;
74
75
76 Database altered.
77
78 ----新增日志文件组 6
79 SQL> alter database add logfile group 6 ('/u01/app/oracle/oradata/PROD1/redo601.log','/u02/app/oracle/oradata/PROD1/redo602.log') size 100m;
80
81
82 Database altered.
83 ----查询正在使用的组所对应的日志文件：
84 SQL> select * from v$logfile;
85
86 GROUP# STATUS TYPE MEMBER IS_
87 ---------- ------- ------- ---------------------------------------------------------------------------------------------------- ---
88 3 ONLINE /u01/app/oracle/oradata/PROD1/redo03.log NO
89 2 ONLINE /u01/app/oracle/oradata/PROD1/redo02.log NO
90 1 ONLINE /u01/app/oracle/oradata/PROD1/redo01.log NO
91 4 ONLINE /u01/app/oracle/oradata/PROD1/redo401.log NO
92 4 ONLINE /u01/app/oracle/oradata/PROD1/redo402.log NO
93 6 ONLINE /u01/app/oracle/oradata/PROD1/redo601.log NO
94 6 ONLINE /u02/app/oracle/oradata/PROD1/redo602.log NO
95 5 ONLINE /u01/app/oracle/oradata/PROD1/redo501.log NO
96 5 ONLINE /u02/app/oracle/oradata/PROD1/redo502.log NO
97
98 9 rows selected.
99
100
101
102 ---强制日志切换：
103
104 SQL> alter system switch logfile;
105
106 System altered.
107
108 ---查询系统使用的是哪一组日志文件：
109 SQL> select * from v$log;
110
111 GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
112 ---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ ---------
113 1 1 10 52428800 512 1 YES INACTIVE 922570 05-NOV-17 944610 11-NOV-17
114 2 1 12 52428800 512 1 YES ACTIVE 947873 11-NOV-17 964189 11-NOV-17
115 3 1 9 52428800 512 1 YES INACTIVE 914559 05-NOV-17 922570 05-NOV-17
116 4 1 11 104857600 512 2 YES INACTIVE 944610 11-NOV-17 947873 11-NOV-17
117 5 1 13 104857600 512 2 NO CURRENT 964189 11-NOV-17 2.8147E+14
118 6 1 0 104857600 512 2 YES UNUSED 0 0
119
120 6 rows selected.
121
122
123 /*
124 checkpoint由ckpt进程触发oracle进行checkpoint动作,将data buffer中的脏块(已经写在
125 redo里记录但是没有写到datafile里的)的内容写入到data file里并释放站用的空间,由
126 dbw后台进程完成,并修改controlfile和datafile的scn.
127
128 一般手工执行是由于要删除某个日志但是该日志里还有没有同步到data file里的内容,就
129 需要手工check point来同步数据,然后就可以drop logfile group n.
130 */
131
132 SQL> alter system checkpoint;
133
134 System altered.
135
136 SQL> select * from v$log;
137
138 GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
139 ---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ ---------
140 1 1 10 52428800 512 1 YES INACTIVE 922570 05-NOV-17 944610 11-NOV-17
141 2 1 12 52428800 512 1 YES INACTIVE 947873 11-NOV-17 964189 11-NOV-17
142 3 1 9 52428800 512 1 YES INACTIVE 914559 05-NOV-17 922570 05-NOV-17
143 4 1 11 104857600 512 2 YES INACTIVE 944610 11-NOV-17 947873 11-NOV-17
144 5 1 13 104857600 512 2 NO CURRENT 964189 11-NOV-17 2.8147E+14
145 6 1 0 104857600 512 2 YES UNUSED 0 0
146
147 6 rows selected.
148
149 -----删除日志组文件
150 SQL> select * from v$logfile order by 1;
151
152 GROUP# STATUS TYPE MEMBER IS_
153 ---------- ------- ------- ---------------------------------------------------------------------------------------------------- ---
154 1 ONLINE /u01/app/oracle/oradata/PROD1/redo01.log NO
155 2 ONLINE /u01/app/oracle/oradata/PROD1/redo02.log NO
156 3 ONLINE /u01/app/oracle/oradata/PROD1/redo03.log NO
157 4 ONLINE /u01/app/oracle/oradata/PROD1/redo401.log NO
158 4 ONLINE /u01/app/oracle/oradata/PROD1/redo402.log NO
159 5 ONLINE /u01/app/oracle/oradata/PROD1/redo501.log NO
160 5 ONLINE /u02/app/oracle/oradata/PROD1/redo502.log NO
161 6 ONLINE /u01/app/oracle/oradata/PROD1/redo601.log NO
162 6 ONLINE /u02/app/oracle/oradata/PROD1/redo602.log NO
163
164 9 rows selected.
165
166 SQL> alter database drop logfile group 1;
167
168 Database altered.
169
170 SQL> select * from v$logfile order by 1;
171
172 GROUP# STATUS TYPE MEMBER IS_
173 ---------- ------- ------- ---------------------------------------------------------------------------------------------------- ---
174 2 ONLINE /u01/app/oracle/oradata/PROD1/redo02.log NO
175 3 ONLINE /u01/app/oracle/oradata/PROD1/redo03.log NO
176 4 ONLINE /u01/app/oracle/oradata/PROD1/redo401.log NO
177 4 ONLINE /u01/app/oracle/oradata/PROD1/redo402.log NO
178 5 ONLINE /u01/app/oracle/oradata/PROD1/redo501.log NO
179 5 ONLINE /u02/app/oracle/oradata/PROD1/redo502.log NO
180 6 ONLINE /u01/app/oracle/oradata/PROD1/redo601.log NO
181 6 ONLINE /u02/app/oracle/oradata/PROD1/redo602.log NO
182
183 8 rows selected.
184
185
186 SQL> alter database add logfile group 1 ('/u01/app/oracle/oradata/PROD1/redo101.log','/u02/app/oracle/oradata/PROD1/redo102.log') size 100m reuse ;
187
188 SQL> select * from v$logfile order by 1;
189
190 GROUP# STATUS TYPE MEMBER IS_
191 ---------- ------- ------- ---------------------------------------------------------------------------------------------------- ---
192 1 ONLINE /u02/app/oracle/oradata/PROD1/redo102.log NO
193 1 ONLINE /u01/app/oracle/oradata/PROD1/redo101.log NO
194 2 ONLINE /u01/app/oracle/oradata/PROD1/redo02.log NO
195 3 ONLINE /u01/app/oracle/oradata/PROD1/redo03.log NO
196 4 ONLINE /u01/app/oracle/oradata/PROD1/redo401.log NO
197 4 ONLINE /u01/app/oracle/oradata/PROD1/redo402.log NO
198 5 ONLINE /u01/app/oracle/oradata/PROD1/redo501.log NO
199 5 ONLINE /u02/app/oracle/oradata/PROD1/redo502.log NO
200 6 ONLINE /u02/app/oracle/oradata/PROD1/redo602.log NO
201 6 ONLINE /u01/app/oracle/oradata/PROD1/redo601.log NO
202
203 10 rows selected.
204
205 SQL> alter database drop logfile group 2;
206
207 Database altered.
208
209 SQL> select * from v$logfile;
210
211 GROUP# STATUS TYPE MEMBER IS_
212 ---------- ------- ------- ---------------------------------------------------------------------------------------------------- ---
213 3 ONLINE /u01/app/oracle/oradata/PROD1/redo03.log NO
214 1 ONLINE /u01/app/oracle/oradata/PROD1/redo101.log NO
215 4 ONLINE /u01/app/oracle/oradata/PROD1/redo401.log NO
216 4 ONLINE /u01/app/oracle/oradata/PROD1/redo402.log NO
217 6 ONLINE /u01/app/oracle/oradata/PROD1/redo601.log NO
218 6 ONLINE /u02/app/oracle/oradata/PROD1/redo602.log NO
219 5 ONLINE /u01/app/oracle/oradata/PROD1/redo501.log NO
220 5 ONLINE /u02/app/oracle/oradata/PROD1/redo502.log NO
221 1 ONLINE /u02/app/oracle/oradata/PROD1/redo102.log NO
222
223 9 rows selected.
224
225 SQL> alter database add logfile group 2 ('/u01/app/oracle/oradata/PROD1/redo201.log','/u02/app/oracle/oradata/PROD1/redo202.log') size 100m reuse;
226
227 Database altered.
228
229 SQL> select * from v$logfile order by 1;
230
231 GROUP# STATUS TYPE MEMBER IS_
232 ---------- ------- ------- ---------------------------------------------------------------------------------------------------- ---
233 1 ONLINE /u01/app/oracle/oradata/PROD1/redo101.log NO
234 1 ONLINE /u02/app/oracle/oradata/PROD1/redo102.log NO
235 2 ONLINE /u02/app/oracle/oradata/PROD1/redo202.log NO
236 2 ONLINE /u01/app/oracle/oradata/PROD1/redo201.log NO
237 3 ONLINE /u01/app/oracle/oradata/PROD1/redo03.log NO
238 4 ONLINE /u01/app/oracle/oradata/PROD1/redo401.log NO
239 4 ONLINE /u01/app/oracle/oradata/PROD1/redo402.log NO
240 5 ONLINE /u01/app/oracle/oradata/PROD1/redo501.log NO
241 5 ONLINE /u02/app/oracle/oradata/PROD1/redo502.log NO
242 6 ONLINE /u02/app/oracle/oradata/PROD1/redo602.log NO
243 6 ONLINE /u01/app/oracle/oradata/PROD1/redo601.log NO
244
245 11 rows selected.
246
247 SQL> alter database drop logfile group 3;
248
249 Database altered.
250
251 SQL> alter database add logfile group 3 ('/u01/app/oracle/oradata/PROD1/redo301.log','/u02/app/oracle/oradata/PROD1/redo302.log') size 100m reuse;
252
253 Database altered.
254
255 SQL> select * from v$logfile;
256
257 GROUP# STATUS TYPE MEMBER IS_
258 ---------- ------- ------- ---------------------------------------------------------------------------------------------------- ---
259 3 ONLINE /u01/app/oracle/oradata/PROD1/redo301.log NO
260 2 ONLINE /u01/app/oracle/oradata/PROD1/redo201.log NO
261 1 ONLINE /u01/app/oracle/oradata/PROD1/redo101.log NO
262 4 ONLINE /u01/app/oracle/oradata/PROD1/redo401.log NO
263 4 ONLINE /u01/app/oracle/oradata/PROD1/redo402.log NO
264 6 ONLINE /u01/app/oracle/oradata/PROD1/redo601.log NO
265 6 ONLINE /u02/app/oracle/oradata/PROD1/redo602.log NO
266 5 ONLINE /u01/app/oracle/oradata/PROD1/redo501.log NO
267 5 ONLINE /u02/app/oracle/oradata/PROD1/redo502.log NO
268 1 ONLINE /u02/app/oracle/oradata/PROD1/redo102.log NO
269 2 ONLINE /u02/app/oracle/oradata/PROD1/redo202.log NO
270
271 GROUP# STATUS TYPE MEMBER IS_
272 ---------- ------- ------- ---------------------------------------------------------------------------------------------------- ---
273 3 ONLINE /u02/app/oracle/oradata/PROD1/redo302.log NO
274
275 12 rows selected.
276
277
278
279 ---删除非当前任务状态；而且还未被使用状态的 日志组 6 的文件信息
280 SQL> alter database drop logfile group 6;
281
282 Database altered.
283
284 SQL> select * from v$log;
285
286 GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
287 ---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ ---------
288 1 1 0 104857600 512 2 YES UNUSED 0 0
289 2 1 0 104857600 512 2 YES UNUSED 0 0
290 3 1 0 104857600 512 2 YES UNUSED 0 0
291 5 1 13 104857600 512 2 NO CURRENT 964189 11-NOV-17 2.8147E+14
292
293 SQL> select * from v$logfile;
294
295 GROUP# STATUS TYPE MEMBER IS_
296 ---------- ------- ------- ---------------------------------------------------------------------------------------------------- ---
297 3 ONLINE /u01/app/oracle/oradata/PROD1/redo301.log NO
298 2 ONLINE /u01/app/oracle/oradata/PROD1/redo201.log NO
299 1 ONLINE /u01/app/oracle/oradata/PROD1/redo101.log NO
300 5 ONLINE /u01/app/oracle/oradata/PROD1/redo501.log NO
301 5 ONLINE /u02/app/oracle/oradata/PROD1/redo502.log NO
302 1 ONLINE /u02/app/oracle/oradata/PROD1/redo102.log NO
303 2 ONLINE /u02/app/oracle/oradata/PROD1/redo202.log NO
304 3 ONLINE /u02/app/oracle/oradata/PROD1/redo302.log NO
305
306 8 rows selected.
307
308
309 -----切换日志：把当前状态的日志切换非当前任务状态
310 SQL> alter system switch logfile;
311
312 System altered.
313 SQL> select * from v$log order by 1;
314
315 GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
316 ---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ ---------
317 1 1 14 104857600 512 2 NO CURRENT 976765 11-NOV-17 2.8147E+14
318 2 1 0 104857600 512 2 YES UNUSED 0 0
319 3 1 0 104857600 512 2 YES UNUSED 0 0
320 5 1 13 104857600 512 2 YES ACTIVE 964189 11-NOV-17 976765 11-NOV-17
321
322 ---oracle中如何将active状态的重做日志文件切换成inactive
323
324 SQL> alter system checkpoint;
325
326 System altered.
327
328 SQL> select * from v$log order by 1;
329
330 GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
331 ---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ ---------
332 1 1 14 104857600 512 2 YES INACTIVE 976765 11-NOV-17 976793 11-NOV-17
333 2 1 15 104857600 512 2 YES INACTIVE 976793 11-NOV-17 976813 11-NOV-17
334 3 1 16 104857600 512 2 NO CURRENT 976813 11-NOV-17 2.8147E+14
335 5 1 13 104857600 512 2 YES INACTIVE 964189 11-NOV-17 976765 11-NOV-17
336
337 SQL> alter database drop logfile group 5;
338
339 Database altered.
340
341 SQL> select * from v$log order by 1;
342
343 GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
344 ---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ ---------
345 1 1 14 104857600 512 2 YES INACTIVE 976765 11-NOV-17 976793 11-NOV-17
346 2 1 15 104857600 512 2 YES INACTIVE 976793 11-NOV-17 976813 11-NOV-17
347 3 1 16 104857600 512 2 NO CURRENT 976813 11-NOV-17 2.8147E+14
348
349 SQL>
1
2 ---#4.3将位于Dir2中的新数据文件system02.dbf添加到系统表空间。
3 --- 4.3 Add a new datafile system02.dbf located in the Dir2 to the system tablespace.
4
5 ---可以通过PL/SQL查看。
6 ---1. 查看所有表空间大小
7 select tablespace_name,sum(bytes)/1024/1024 from dba_data_files group by tablespace_name;
8 ---2. 未使用的表空间大小
9 select tablespace_name,sum(bytes)/1024/1024 from dba_free_space group by tablespace_name;
10
11
12 SQL> col file_name format a100;
13 SQL> select file_name from dba_data_files;
14
15 FILE_NAME
16 ----------------------------------------------------------------------------------------------------
17 /u01/app/oracle/oradata/PROD2/datafile/o1_mf_system_dz3z3pyj_.dbf
18 /u01/app/oracle/oradata/PROD2/datafile/o1_mf_sysaux_dz3z3tz1_.dbf
19 /u01/app/oracle/oradata/PROD2/datafile/o1_mf_sys_undo_dz3z3z78_.dbf
20 ----新增 表空间数据文件
21 SQL> alter tablespace system add datafile '/u02/app/oracle/oradata/PROD1/system02.dbf' size 400M ;
22
23 Tablespace altered.
24
25 SQL> select file_name from dba_data_files;
26
27 FILE_NAME
28 ----------------------------------------------------------------------------------------------------
29 /u01/app/oracle/oradata/PROD2/datafile/o1_mf_system_dz3z3pyj_.dbf
30 /u02/app/oracle/oradata/PROD1/system02.dbf
31 /u01/app/oracle/oradata/PROD2/datafile/o1_mf_sysaux_dz3z3tz1_.dbf
32 /u01/app/oracle/oradata/PROD2/datafile/o1_mf_sys_undo_dz3z3z78_.dbf
33
34 ---在系统数据盘之外的磁盘建立FRA，存放归档日志及备份等
35 SQL> show parameter db_recovery
36
37 NAME TYPE VALUE
38 ------------------------------------ ----------- ------------------------------
39 db_recovery_file_dest string
40 db_recovery_file_dest_size big integer 0
41
42
43 SQL> select name from v$archived_log order by completion_time;
44
45 no rows selected
46
1 --- 5.配置数据库环境以支持最佳数据访问性能
2 --- 5. Configure the database environment to support optimal data access performance
3
4 /*
5 1. 文档
6 Performance Tuning Guide -> 8 I/O Configuration and Design
7 Reference -> 1 Initialization Parameters
8 2. DB_BLOCK_SIZE 参数
9 DB_BLOCK_SIZE 的参考建议（提升I/O 效率）:
10 Small rows + random access => small block size (DB_BLOCK_SIZE = 8192)
11 Small rows + sequential access => big block size (DB_BLOCK_SIZE = 16384 or 32768)
12 Small rows + random and sequential access => big block size (DB_BLOCK_SIZE = 16384 or 32768)
13 Big rows (例如：LOB objects) => big block size (DB_BLOCK_SIZE = 16384 or 32768)
14 For example, a data warehouse (DW) DB:
15 Default block size for DB DW => DB_BLOCK_SIZE = 16K
16 The TABLESPACES for FACT TABLES（事实表） => DB_BLOCK_SIZE = 32K
17 The TABLESPACES for DIMENSION TABLES（维度表） => DB_BLOCK_SIZE = 8K
18
19 3. FAST_START_MTTR_TARGET 参数（mean time to recover - MTTR）
20 The FAST_START_MTTR_TARGET initialization parameter causes the database to calculate internal
21 system trigger values, in order to limit the length of the redo log and the number of dirty data
22 buffers in the data cache.
23 FAST_START_MTTR_TARGET enables you to specify the number of seconds the database takes to perform
24 crash recovery of a single instance. When we set this parameter, the database starts to run
25 checkpoint incremental periodically so that recovery before a drop of DB does not exceed the
26 time established in FAST_START_MTTR_TARGET.
27
28
29
30
31 */
32
33
34
35 ---fast_start_mttr_target 值在0到3600秒之间，默认值为0。
36
37 SQL> show parameter fast_start_mttr_target
38
39 NAME TYPE VALUE
40 ------------------------------------ ----------- ------------------------------
41 fast_start_mttr_target integer 0
42
43 ---Determining Lower Bound for FAST_START_MTTR_TARGET （FAST_START_MTTR_TARGET 确定下限）
44 SQL> alter system set fast_start_mttr_target=1 scope=both;
45
46 System altered.
47
48 SQL> shutdown immediate;
49 Database closed.
50 Database dismounted.
51 ORACLE instance shut down.
52 SQL> startup
53 ORACLE instance started.
54
55 Total System Global Area 1071333376 bytes
56 Fixed Size 1349732 bytes
57 Variable Size 658507676 bytes
58 Database Buffers 406847488 bytes
59 Redo Buffers 4628480 bytes
60 Database mounted.
61 Database opened.
62 SQL> select target_mttr,estimated_mttr from v$instance_recovery;
63
64 TARGET_MTTR ESTIMATED_MTTR
65 ----------- --------------
66 11 9
67 /**
68 TARGET_MTTR :值是系统可以实现的最小MTTR目标
69 ESTIMATED_MTTR : 估计的mttr字段包含根据当前情况进行恢复的平均时间 运行数据库的状态。最近的数据库活动可以在短期内影响评估mttr
70
71
72 */
73
74 -- Set FAST_START_MTTR_TARGET = TARGET_MTTR
75 SQL> alter system set fast_start_mttr_target=18 scope=both;
76
77 System altered.
78
79 -- Get the recommended size (in MB) of the redo files
80 -- The size is considered optimal based on the current setting of FAST_START_MTTR_TARGET.
81
82 SQL> select optimal_logfile_size from v$instance_recovery;
83
84 OPTIMAL_LOGFILE_SIZE
85 --------------------
86 245
87 SQL> select target_mttr,estimated_mttr from v$instance_recovery;
88
89 TARGET_MTTR ESTIMATED_MTTR
90 ----------- --------------
91 18 9
92 --#5.2配置数据库以在alert.log文件中记录检查点。
93 --- 5.2 Configure your database to record checkpoints in the alert.log file.
94 /*
95 LOG_CHECKPOINT_INTERVAL:指定检查点的频率，在增量检查点和写到重做日志的最后一个块之间存在的重做日志文件块的数量。这个数字是指物理操作系统块，而不是数据库块。默认值为0，该参数的非0值被认为是有意义的。
96 LOG_CHECKPOINT_TIMEOUT:指定(以秒为单位)从上一次写到重做日志(有时称为日志尾)的位置上的增量检查点的时间所发生的时间。这个参数也表示，在超过整数秒的时间内，任何缓冲区都不会是脏的(在缓存中)。默认是1800。指定一个值为0的值禁用基于时间的检查点。因此，除非设置了faststartmttrtarget，否则不建议将值设置为0。
97
98 LOG_CHECKPOINTS_TO_ALERT:让您将检查点记录到警报文件。这样做对于确定检查点是否出现在期望的频率是很有用的。默认值是错误的。
99 */
100
101
102 SQL> show parameter log_checkpoint;
103
104 NAME TYPE VALUE
105 ------------------------------------ ----------- ------------------------------
106 log_checkpoint_interval integer 0
107 log_checkpoint_timeout integer 1800
108 log_checkpoints_to_alert boolean FALSE
109 SQL> alter system set log_checkpoints_to_alert=true;
110
111 System altered.
112
113 /*
114 ARCHIVE_LAG_TARGET 强制执行一个重做日志开关的时间间隔。值的范围:60-7200秒，默认值为0。0值禁用基于时间的线程提前特性;典型的或推荐值是1800(30分钟)。
115
116
117 */
1. 文档：
Administrator's Guide -> 2 Creating and Configuring an Oracle Database -> Managing
Initialization Parameters Using a Server Parameter File
Administrator's Guide -> 3 Starting Up and Shutting Down
2. PFILE, SPFILE
In the platform-specific default location, Oracle Database locates your initialization parameter
file by examining file names in the following order:
1) spfile<ORACLE_SID>.ora
2) spfile.ora
3) Init<ORACLE_SID>.ora
6.1从MEMORY，SPFILE或PFILE创建临时PFILE / SPFILE。（Create a temporary PFILE / SPFILE from MEMORY, SPFILE or PFILE.）
1 --6.1从MEMORY，SPFILE或PFILE创建临时PFILE / SPFILE。（Create a temporary PFILE / SPFILE from MEMORY, SPFILE or PFILE.）
2
3 ---检查实例是否从PFILE或SPFILE开始 （Check if the instance started with PFILE or SPFILE）
4
5
6 SQL> show parameter spfile;
7
8 NAME TYPE VALUE
9 ------------------------------------ ----------- ------------------------------
10 spfile string /u01/app/oracle/product/11.2.0
11 /db_1/dbs/spfilePROD1.ora
12
13 ---- Create a PFILE / SPFILE from MEMORY, SPFILE or PFILE
14 -- If not specify the location, the default path is $ORACLE_HOME/dbs
15 ----从内存中创建pfile:
16 SQL> create pfile from memory;
17
18 File created.
19 ---从spfile 中创建pfile;
20 SQL> create pfile from spfile;
21
22 File created.
23
24 /*
25 -- 仅当使用PFILE 启动实例时，生成SPFILE 文件
26 -- 否则出错ORA-32002: cannot create SPFILE already being used by the instance
27 -- create spfile from memory;
28 -- create spfile from pfile;
29 */
30
31 SQL> shutdown immediate
32 Database closed.
33 Database dismounted.
34 ORACLE instance shut down.
35 SQL> startup from pfile;
36 SP2-0714: invalid combination of STARTUP options
37 SQL>
38 SQL> startup pfile='/u01/app/oracle/product/11.2.0/db_1/dbs/initPROD1.ora';
39 ORACLE instance started.
40
41 Total System Global Area 1707446272 bytes
42 Fixed Size 1345408 bytes
43 Variable Size 1040189568 bytes
44 Database Buffers 654311424 bytes
45 Redo Buffers 11599872 bytes
46 Database mounted.
47 Database opened.
48
49 ---从pfile中生成spfile:
50
51 SQL> create spfile from pfile;
52
53 File created.
54
55 ----直接从spfile 启动数据库
56
57 SQL> shutdown immediate;
58
59 SQL> shutdown immediate;
60 Database closed.
61 Database dismounted.
62 ORACLE instance shut down.
63 SQL> startup
64 ORACLE instance started.
65
66 Total System Global Area 1707446272 bytes
67 Fixed Size 1345408 bytes
68 Variable Size 1040189568 bytes
69 Database Buffers 654311424 bytes
70 Redo Buffers 11599872 bytes
71 Database mounted.
72 Database opened.
73
1 SQL> show parameter pfile;
2
3 NAME TYPE VALUE
4 ------------------------------------ ----------- ------------------------------
5 spfile string /u01/app/oracle/product/11.2.0
6 /db_1/dbs/spfilePROD1.ora
7 SQL> create pfile='/tmp/initPROD1.ora' from memory;
8
9 File created.
10
11 SQL> create pfile='/tmp/initPROD1.ora' from spfile;
12
13 File created.
14
15 SQL> create spfile='/tmp/spfilePROD1.ora' from memory;
16
17 File created.
18
19 SQL> create spfile='/tmp/spfilePROD1.ora' from pfile;
20
21 File created.
22
23
24 SQL> create pfile='/tmp/initPROD1.ora' from spfile='/tmp/spfilePROD1.ora' ;
25
26 File created.
27
28 SQL> create spfile='/tmp/spfilePROD1.ora' from pfile='/tmp/initPROD1.ora' ;
29
30 File created.
31
32 SQL>
1 SQL> shutdown immediate;
2 Database closed.
3 Database dismounted.
4 ORACLE instance shut down.
5 SQL> startup pfile='/tmp/initPROD1.ora' ;
6 ORACLE instance started.
7
8 Total System Global Area 1707446272 bytes
9 Fixed Size 1345408 bytes
10 Variable Size 1040189568 bytes
11 Database Buffers 654311424 bytes
12 Redo Buffers 11599872 bytes
13 Database mounted.
14 Database opened.
15 SQL> show parameter spfile;
16
17 NAME TYPE VALUE
18 ------------------------------------ ----------- ------------------------------
19 spfile string
20 SQL> shutdown immediate;
21 Database closed.
22 Database dismounted.
23 ORACLE instance shut down.
24 SQL> startup
25 ORACLE instance started.
26
27 Total System Global Area 1707446272 bytes
28 Fixed Size 1345408 bytes
29 Variable Size 1040189568 bytes
30 Database Buffers 654311424 bytes
31 Redo Buffers 11599872 bytes
32 Database mounted.
33 Database opened.
34 SQL> show parameter spfile;
35
36 NAME TYPE VALUE
37 ------------------------------------ ----------- ------------------------------
38 spfile string /u01/app/oracle/product/11.2.0
39 /db_1/dbs/spfilePROD1.ora
1 ----6.2将MEMORY_TARGET参数更改为450M。
2 ---- 6.2 Change the MEMORY_TARGET parameter to 900M.
3
4 SQL> show parameter memory_target
5
6 NAME TYPE VALUE
7 ------------------------------------ ----------- ------------------------------
8 memory_target big integer 1632M
9
10
11 SQL> alter system set memory_target=900m scope=both;
12
13 System altered.
14
15 SQL> alter system set memory_target=900m scope=spfile;
16
17 System altered.
18
19 SQL> show parameter memory_target
20
21 NAME TYPE VALUE
22 ------------------------------------ ----------- ------------------------------
23 memory_target big integer 912M
24 SQL> alter system set memory_target=1632m ;
25
26 System altered.
27
28 -- default is SCOPE=BOTH 默认设置方法
29
30 SQL> show parameter memory_target;
31
32 NAME TYPE VALUE
33 ------------------------------------ ----------- ------------------------------
34 memory_target big integer 1632M
35 SQL>
1. 文档：
Administrator's Guide -> 14 Managing Tablespaces -> Creating Tablespaces -> Bigfile
Tablespaces
2. BIGFILE TBS
With Only a DATAFILE that may be very large.(只有一个可能非常大的数据文件。)
The maximum size of a BIGFILE TBS datafile is 4G blocks. This means that with a DB of 8K,
the maximum size is 4G blocks * 8K = 32T. The normal TBS datafile is 4M blocks. With a DB of 8K, the maximum size is 32G.
（BIGFILE TBS数据文件的最大大小是4G块。这意味着，在8K的DB中，最大的大小是4G块，8K=32T。正常的TBS数据文件是400万块。使用DB 8K，最大大小为32G。）
Oracle 11gR2, the BIGFILE TBS can only be LOCALLY MANAGED with ASSM. （Oracle 11gR2，BIGFILE TBS只能用ASSM本地管理。）
1 ----7.1创建一个大小为100MB的bigfile tablespace BIGTBS1
2 ---- 7.1 Create a bigfile tablespace BIGTBS1 with size 100MB
3 ----7.2创建一个bigfile表空间BIGTBS2初始大小为100MB，下一个扩展大小为100MB，最大大小为100G
4 ---7.2 Create a bigfile tablespace BIGTBS2 initial size 100MB, next extent size of 100MB with maximum size 100G
5
6
7 SQL> select tablespace_name ,file_name from dba_data_files;
8
9 TABLESPACE_NAME FILE_NAME
10 ---------------------------------------------------------------------------------------
11 USERS /u01/app/oracle/oradata/PROD1/users01.dbf
12 UNDOTBS1 /u01/app/oracle/oradata/PROD1/undotbs01.dbf
13 SYSAUX /u01/app/oracle/oradata/PROD1/sysaux01.dbf
14 SYSTEM /u01/app/oracle/oradata/PROD1/system01.dbf
15 EXAMPLE /u01/app/oracle/oradata/PROD1/example01.dbf
16
17
18 SQL> create bigfile tablespace bigtbs1 datafile '/u01/app/oracle/oradata/PROD1/bigtbs01.dbf' size 100m ;
19
20 Tablespace created.
21
22
23 SQL> create bigfile tablespace bigtbs2 datafile '/u01/app/oracle/oradata/PROD1/bigtbs02.dbf' size 100m autoextend on next 100m maxsize 100g ;
24
25 Tablespace created.
26
27 SQL> select tablespace_name , file_name from dba_data_files;
28
29 TABLESPACE_NAME FILE_NAME
30 ------------------------------ ------------------------------ -------------------
31 USERS /u01/app/oracle/oradata/PROD1/users01.dbf
32 UNDOTBS1 /u01/app/oracle/oradata/PROD1/undotbs01.dbf
33 SYSAUX /u01/app/oracle/oradata/PROD1/sysaux01.dbf
34 SYSTEM /u01/app/oracle/oradata/PROD1/system01.dbf
35 EXAMPLE /u01/app/oracle/oradata/PROD1/example01.dbf
36 BIGTBS1 /u01/app/oracle/oradata/PROD1/bigtbs01.dbf
37 BIGTBS2 /u01/app/oracle/oradata/PROD1/bigtbs02.dbf
38
39 7 rows selected.
---------------更改BIGTBS1 Change the BIGTBS1 size to 200MB
1 ----7.4删除2个表空间。 -----7.4 Drop the 2 tablespaces.
3 SQL> select tablespace_name ,file_name from dba_data_files;
4
5 TABLESPACE_NAME FILE_NAME
6 ------------------------------------------------------------------------------------------
7 USERS /u01/app/oracle/oradata/PROD1/users01.dbf
8 UNDOTBS1 /u01/app/oracle/oradata/PROD1/undotbs01.dbf
9 SYSAUX /u01/app/oracle/oradata/PROD1/sysaux01.dbf
10 SYSTEM /u01/app/oracle/oradata/PROD1/system01.dbf
11 EXAMPLE /u01/app/oracle/oradata/PROD1/example01.dbf
12 BIGTBS1 /u01/app/oracle/oradata/PROD1/bigtbs01.dbf
13 BIGTBS2 /u01/app/oracle/oradata/PROD1/bigtbs02.dbf
14
15 7 rows selected.
16
17 SQL> drop tablespace bigtbs1 including contents and datafiles;
18
19 Tablespace dropped.
20
21 SQL> drop tablespace bigtbs2 including contents and datafiles;
22
23 Tablespace dropped.
24
25 SQL>
1 ---7.3更改BIGTBS1
2 ---7.3 Change the BIGTBS1 size to 200MB
3
4 SQL> select tablespace_name ,file_name from dba_data_files;
5
6 TABLESPACE_NAME FILE_NAME
7 ------------------------------ ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
8 USERS /u01/app/oracle/oradata/PROD1/users01.dbf
9 UNDOTBS1 /u01/app/oracle/oradata/PROD1/undotbs01.dbf
10 SYSAUX /u01/app/oracle/oradata/PROD1/sysaux01.dbf
11 SYSTEM /u01/app/oracle/oradata/PROD1/system01.dbf
12 EXAMPLE /u01/app/oracle/oradata/PROD1/example01.dbf
13
14 SQL> create bigfile tablespace bigtsb1 datafile '/u01/app/oracle/oradata/PROD1/biftsb01.dbf' size 100m autoextend on next 100m maxsize 100g ;
15
16 Tablespace created.
17
18 SQL> SELECT TABLESPACE_NAME,FILE_NAME FROM DBA_DATA_FILES;
19
20 TABLESPACE_NAME FILE_NAME
21 ------------------------------
22 USERS /u01/app/oracle/oradata/PROD1/users01.dbf
23 UNDOTBS1 /u01/app/oracle/oradata/PROD1/undotbs01.dbf
24 SYSAUX /u01/app/oracle/oradata/PROD1/sysaux01.dbf
25 SYSTEM /u01/app/oracle/oradata/PROD1/system01.dbf
26 EXAMPLE /u01/app/oracle/oradata/PROD1/example01.dbf
27 BIGTSB1 /u01/app/oracle/oradata/PROD1/biftsb01.dbf
28
29 6 rows selected.
30
31 SQL> ALTER DATABASE DATAFILE '/u01/app/oracle/oradata/PROD1/biftsb01.dbf' RESIZE 200M ;
32
33 Database altered.
34
--- -创建和管理使用NFS挂载文件系统文件的表空间 Create and Manage a Tablespace that uses NFS Mounted File System File
1. 文档：
Database Installation Guide for Linux -> 5 Oracle Database Postinstallation Tasks -> 5.3.9
Configuring and Using Direct NFS Client
Grid Infrastructure Installation Guide for Linux -> 3 Configuring Storage for Oracle Grid
Infrastructure for a Cluster and Oracle RAC -> 3.2.3 Deciding to Use Direct NFS Client for
Data Files
Enable Direct NFS as follow (启用直接 网络文件系统（NFS），如下:)
1)Enable system normal NFS setting (both server & client) (启用系统正常NFS设置(服务器和客户端： 奇数ip 为 客户端 偶数ip 为 服务端 ))
2)Shutdown the database instances.（关闭数据实例）
3)The turn on the DNFS option from the Oracle kernel (as oracle OS user):（从Oracle内核(作为Oracle OS用户)转向DNFS选项:）
$> cd $ORACLE_HOME/rdbms/lib
$> make -f ins_rdbms.mk dnfs_on
4)Startup the database instances and Create datafiles on the NFS storage. (启动数据库实例，并在NFS存储上创建数据文件)
Disable Direct NFS as follow: (禁用直接NFS，如下:)
1)Drop the datafiles on the NFS storage and Shutdown the database instances.(在NFS存储上删除数据文件，并关闭数据库实例)
2)The turn off the DNFS option from the Oracle kernel (as oracle OS user):(从Oracle内核(作为Oracle OS用户)关闭DNFS选项:)
$> cd $ORACLE_HOME/rdbms/lib
$> make -f ins_rdbms.mk dnfs_off
3)Startup the database instances. (启动数据库实例。)
4)Disable the system normal NFS setting.(禁用系统正常的NFS设置。)
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
