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

2024-04-02 19:55

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

11g OCM考试纲:Server Configuration

1 Create the database
2 Determine and set sizing parameters for database structures
3 Create and manage temporary, permanent, and undo tablespaces
4 Stripe data files across multiple physical devices and locations

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

一、配置表空间

[oracle@rh74 ~]$ export ORACLE_SID=test1
[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考试，后续陆续推出。。。