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考試，后續陸續推出。。。

網頁題目：OracleStudy之-Oracle11gOCM考試（2)
瀏覽路徑：http://www.yijiale78.com/article48/pehjep.html

成都網站建設公司_創新互聯，為您提供面包屑導航、網站改版、ChatGPT、定制開發、網站策劃、外貿建站

廣告

聲明：本網站發布的內容（圖片、視頻和文字）以用戶投稿、用戶轉載內容為主，如果涉及侵權請盡快告知，我們將會在第一時間刪除。文章觀點不代表本網站立場，如需處理請聯系客服。電話：028-86922220；郵箱：631063699@qq.com。內容未經允許不得轉載，或轉載時需注明來源： 創新互聯