Oracle Study之-Oracle 11g OCM考试（2)

Oracle Study之-Oracle 11g OCM考试（2)

11g OCM考试 纲:Server Configuration

1. 1  Create the database

2. 2  Determine and set sizing parameters for database structures

3. 3  Create and manage temporary, permanent, and undo tablespaces

4. 4  Stripe data files across multiple physical devices and locations

   
   

   按照考试要求，配置表空间及控制文件、redo log的多元化

   
   

   一、配置表空间

   
   

   [oracle@rh74 ~]$ export ORACLE_SID=test1

5. [oracle@rh74 ~]$ sqlplus '/as sysdba'
   SQL*Plus: Release 11.2.0.3.0 Production on Tue Apr 12 17:24:43 2016
   Copyright (c) 1982, 2011, Oracle.  All rights reserved.
   Connected to an idle instance.
   17:24:43 SYS@ test1>startup
   ORACLE instance started.
   Total System Global Area  313159680 bytes
   Fixed Size                  2227944 bytes
   Variable Size             218104088 bytes
   Database Buffers           88080384 bytes
   Redo Buffers                4747264 bytes
   Database mounted.
   Database opened.
   
   1）配置Undo表空间
   
   17:25:12 SYS@ test1>show parameter undo
   NAME                                 TYPE        VALUE
   ------------------------------------ ----------- ------------------------------
   undo_management                      string      AUTO
   undo_retention                       integer     900
   undo_tablespace                      string      UNDOTBS1

   设置undo_retention参数，可以按照业务中事务最长的查询时间来设置：

   17:25:47 SYS@ test1>alter system set undo_retention=3600;     
   System altered.
   
   17:26:14 SYS@ test1>show parameter undo;
   NAME                                 TYPE        VALUE
   ------------------------------------ ----------- ------------------------------
   undo_management                      string      AUTO
   undo_retention                       integer     3600
   undo_tablespace                      string      UNDOTBS1

   
   

   创建Undo tablespace：

   23:49:48 SYS@ test1>create undo tablespace undotbs2
   23:49:55   2  datafile '/u01/app/oracle/oradata/test1/undotbs02.dbf' size 100m
   23:50:01   3  autoextend on maxsize 2g
   23:50:06   4  extent management local;
   Tablespace created.
   
   23:50:37 SYS@ test1>show parameter undo
   
   NAME                                 TYPE        VALUE
   ------------------------------------ ----------- ------------------------------
   undo_management                      string      AUTO
   undo_retention                       integer     900
   undo_tablespace                      string      UNDOTBS1

   
   

   切换undo tablespace：
   23:50:43 SYS@ test1>alter system set undo_tablespace='undotbs2';
   System altered.
   
   23:50:57 SYS@ test1>show parameter undo
   NAME                                 TYPE        VALUE
   ------------------------------------ ----------- ------------------------------
   undo_management                      string      AUTO
   undo_retention                       integer     900
   undo_tablespace                      string      undotbs2
   
   查看表空间信息：
   17:28:03 SYS@ test1> select tablespace_name,file_id,file_name,bytes/1024/1024 "Total_Size(M)" from dba_data_files
   TABLESPACE_NAME                   FILE_ID FILE_NAME                                          Total_Size(M)
   ------------------------------ ---------- -------------------------------------------------- -------------
   SYSTEM                                  1 /u01/app/oracle/oradata/test1/system01.dbf                   325
   SYSAUX                                  2 /u01/app/oracle/oradata/test1/sysaux01.dbf                   325
   UNDOTBS1                                3 /u01/app/oracle/oradata/test1/undotbs01.dbf                  200

   UNDOTBS2                                3 /u01/app/oracle/oradata/test1/undotbs02.dbf                  100
   USERS                                   4 /u01/app/oracle/oradata/test1/users01.dbf                    100
   
   创建永久表空间：
   17:30:14 SYS@ test1>create tablespace test1   
   17:30:26   2  datafile '/u01/app/oracle/oradata/test1/test01.dbf' size 10m
   17:30:46   3  autoextend on maxsize 2g
   17:30:57   4  extent management local uniform size 128k;
   Tablespace created.
   
   17:31:23 SYS@ test1>select tablespace_name,file_id,file_name,bytes/1024/1024 "Total_Size(M)" from dba_data_files
   17:31:34   2     where tablespace_name='TEST1';
   TABLESPACE_NAME                   FILE_ID FILE_NAME                                          Total_Size(M)
   ------------------------------ ---------- -------------------------------------------------- -------------
   TEST1                                   5 /u01/app/oracle/oradata/test1/test01.dbf                      10
   Elapsed: 00:00:00.00
   
   17:33:55 SYS@ test1>create tablespace indx
   17:34:04   2  datafile '/u01/app/oracle/oradata/test1/indx01.dbf' size 10m
   17:34:22   3  autoextend on maxsize 2g
   17:34:37   4  extent management local autoallocate 
   17:35:14   5  segment space management manual;
   Tablespace created.
   
   17:36:15 SYS@ test1>select tablespace_name,file_id,file_name,bytes/1024/1024 "Total_Size(M)" from dba_data_files
   17:36:31   2  where tablespace_name='INDX';
   TABLESPACE_NAME                   FILE_ID FILE_NAME                                          Total_Size(M)
   ------------------------------ ---------- -------------------------------------------------- -------------
   INDX                                    6 /u01/app/oracle/oradata/test1/indx01.dbf                      10
   Elapsed: 00:00:00.01
   
   创建临时表空间及表空间组：
   17:37:27 SYS@ test1>select TABLESPACE_NAME ,file_name,bytes/1024/1024 from dba_temp_files;
   TABLESPACE_NAME                FILE_NAME                                          BYTES/1024/1024
   ------------------------------ -------------------------------------------------- ---------------
   TEMPTS1                        /u01/app/oracle/oradata/test1/temp01.dbf                        20
   
   17:38:05 SYS@ test1>create temporary tablespace temp01 
   17:38:23   2  tempfile '/u01/app/oracle/oradata/test1/temp_01.dbf' size 10m
   17:39:01   3  autoextend off tablespace group tmpgp1;
   Tablespace created.
   
   17:40:14 SYS@ test1>create temporary tablespace temp02
   17:40:20   2  tempfile '/u01/app/oracle/oradata/test1/temp_02.dbf' size 10m
   17:40:36   3  autoextend off;
   Tablespace created.
   
   17:41:08 SYS@ test1>alter tablespace temp02 tablespace group tmpgp1;
   Tablespace altered.
   
   17:41:25 SYS@ test1>alter tablespace tempts1 tablespace group tmpgp1;
   Tablespace altered.
   
   
   17:44:53 SYS@ test1>select * from dba_tablespace_groups;
   GROUP_NAME                     TABLESPACE_NAME
   ------------------------------ ------------------------------
   TMPGP1                         TEMPTS1
   TMPGP1                         TEMP01
   TMPGP1                         TEMP02
   
   将临时表空间组设为数据库默认临时表空间：
   17:45:02 SYS@ test1>alter database default temporary tablespace tmpgp1;
   Database altered.
   
   创建用户测试：
   17:46:59 SYS@ test1>create user tom
   17:47:03   2  identified by tom
   17:47:08   3  default tablespace test1       
   17:47:39   4  quota unlimited on test1
   17:48:03   5  temporary tablespace tmpgp1;
   User created.
   
   17:48:32 SYS@ test1>grant connect,resource to tom；
   Grant succeeded.
   
   17:48:42 SYS@ test1>conn tom/tom
   Connected.
   17:48:46 TOM@ test1>
   
   
   二）控制文件、redo log file多元化

   1）控制文件多元化
   23:16:35 SYS@ test1>show parameter control
   NAME                                 TYPE        VALUE
   ------------------------------------ ----------- ------------------------------
   control_file_record_keep_time        integer     7
   control_files                        string      /u01/app/oracle/oradata/test1/control01.ctl

   
   23:16:56 SYS@ test1>col name for a50
   23:17:03 SYS@ test1> select name from v$controlfile
   NAME
   --------------------------------------------------
   /u01/app/oracle/oradata/test1/control01.ctl
   
   创建控制文件存储目录：（建议将控制文件存储到不同的存储介质上）
   [root@rh74 dsk1]# mkdir -p /dsk1/test1/oradata
   [root@rh74 dsk1]# chown -R oracle:dba /dsk1
   [root@rh74 dsk1]# ls -ld /dsk1/test1/oradata/
   drwxr-xr-x 2 oracle dba 4096 Apr 12 23:20 /dsk1/test1/oradata/
   
   生成spfile文件：

   23:17:03 SYS@ test1>show parameter spfile
   NAME                                 TYPE        VALUE
   ------------------------------------ ----------- ------------------------------
   spfile                               string
   23:21:36 SYS@ test1>create spfile from pfile;
   File created.
   
   重新启动数据库，Instance优先使用spfile：
   23:21:43 SYS@ test1>startup force;
   ORACLE instance started.
   Total System Global Area  313159680 bytes
   Fixed Size                  2227944 bytes
   Variable Size             218104088 bytes
   Database Buffers           88080384 bytes
   Redo Buffers                4747264 bytes
   Database mounted.
   Database opened.
   23:22:49 SYS@ test1>show parameter spfile
   NAME                                 TYPE        VALUE
   ------------------------------------ ----------- ------------------------------
   spfile                               string      /u01/app/oracle/product/11.2.0/db_1/dbs/spfiletest1.ora
   
   
   23:23:17 SYS@ test1>alter system set control_files ='/u01/app/oracle/oradata/test1/control01.ctl','/dsk1/test1/oradata/control02.ctl' scope=spfile;
   System altered.
   
   关库后，拷贝控制文件的副本：
   23:24:00 SYS@ test1>shutdown immediate;
   Database closed.
   Database dismounted.
   ORACLE instance shut down.
   23:24:27 SYS@ test1>!cp /u01/app/oracle/oradata/test1/control01.ctl /dsk1/test1/oradata/control02.ctl
   23:24:46 SYS@ test1>startup mount;
   ORACLE instance started.
   Total System Global Area  313159680 bytes
   Fixed Size                  2227944 bytes
   Variable Size             226492696 bytes
   Database Buffers           79691776 bytes
   Redo Buffers                4747264 bytes
   Database mounted.
   23:25:42 SYS@ test1>select name from v$controlfile;
   NAME
   --------------------------------------------------
   /u01/app/oracle/oradata/test1/control01.ctl
   /dsk1/test1/oradata/control02.ctl
   
   2）增加redo日志组成员（不同成员存储到不同的介质）：
   23:25:52 SYS@ test1>col member for a50
   23:26:32 SYS@ test1>select group#,member from v$logfile;
       GROUP# MEMBER
   ---------- --------------------------------------------------
            1 /u01/app/oracle/oradata/test1/redo01a.log
            2 /u01/app/oracle/oradata/test1/redo02a.log
   
   23:26:39 SYS@ test1>select status from v$instance;
   STATUS
   ------------
   MOUNTED
   
   

   添加日志组成员：

   23:30:40 SYS@ test1>alter database add logfile  member

                    '/dsk1/test1/oradata/redo01b.log'   to group 1,

                   '/dsk1/test1/oradata/redo02b.log' to group 2;

   
   

   23:30:40 SYS@ test1>select group#,member from v$logfile order by 1;

       GROUP# MEMBER
   ---------- --------------------------------------------------
            1 /dsk1/test1/oradata/redo01b.log
            1 /u01/app/oracle/oradata/test1/redo01a.log
            2 /dsk1/test1/oradata/redo02b.log
            2 /u01/app/oracle/oradata/test1/redo02a.log
   
   -----------  OCM考试，后续陆续推出。。。