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

叁歲伎倆 2022-07-28 09:25 140阅读 0赞

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@rh64 ~\]$ export ORACLE\_SID=test1  
    \[oracle@rh64 ~\]$ 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@rh64 dsk1\]\# mkdir -p /dsk1/test1/oradata  
    \[root@rh64 dsk1\]\# chown -R oracle:dba /dsk1  
    \[root@rh64 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考试，后续陆续推出。。。**