文章详情

短信预约-IT技能 免费直播动态提醒

请输入下面的图形验证码

提交验证

短信预约提醒成功

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

2024-04-02 19:55

关注

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考试,后续陆续推出。。。


阅读原文内容投诉

免责声明:

① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的,并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据,供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。

② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341

软考中级精品资料免费领

  • 历年真题答案解析
  • 备考技巧名师总结
  • 高频考点精准押题
  • 2024年上半年信息系统项目管理师第二批次真题及答案解析(完整版)

    难度     813人已做
    查看
  • 【考后总结】2024年5月26日信息系统项目管理师第2批次考情分析

    难度     354人已做
    查看
  • 【考后总结】2024年5月25日信息系统项目管理师第1批次考情分析

    难度     318人已做
    查看
  • 2024年上半年软考高项第一、二批次真题考点汇总(完整版)

    难度     435人已做
    查看
  • 2024年上半年系统架构设计师考试综合知识真题

    难度     224人已做
    查看

相关文章

发现更多好内容

猜你喜欢

AI推送时光机
位置:首页-资讯-数据库
咦!没有更多了?去看看其它编程学习网 内容吧
首页课程
资料下载
问答资讯