Shulou(Shulou.com)06/01 Report--

Oracle study-Oracle 11g OCM examination (2)

11g OCM examination outline: 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

According to the examination requirements, configure the table space and the diversification of control documents and redo log

I. configure tablespaces

[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) configure Undo tablespace

17:25:12 SYS@ test1 > show parameter undo

NAME TYPE VALUE

-

Undo_management string AUTO

Undo_retention integer 900

Undo_tablespace string UNDOTBS1

Set the undo_retention parameter, which can be set according to the longest query time of the transaction in the business:

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

Create a Undo tablespace:

23:49:48 SYS@ test1 > create undo tablespace undotbs2

23:49:55 2 datafile'/ u01 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

Toggle 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

View tablespace information:

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

Create a permanent tablespace:

17:30:14 SYS@ test1 > create tablespace test1

17:30:26 2 datafile'/ u01 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 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

Create temporary tablespaces and tablespace groups:

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 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 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

Set the temporary tablespace group to the database default temporary tablespace:

17:45:02 SYS@ test1 > alter database default temporary tablespace tmpgp1

Database altered.

Create a user test:

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 >

II) diversification of control documents and redo log file

1) controlling the diversification of documents

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

Create a control file storage directory: (it is recommended that you store the control file on a different storage medium)

[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/

Generate the spfile file:

23:17:03 SYS@ test1 > show parameter spfile

NAME TYPE VALUE

-

Spfile string

23:21:36 SYS@ test1 > create spfile from pfile

File created.

Restart the database, and Instance gives priority to 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 apprenticeship oradataUniverse Test1Oncontrol01.ctlandlemagoreandsk1Uniplicedoradatalandoradatacontrol 02.ctl'scope=spfile

System altered.

After closing the library, copy a copy of the control file:

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) add redo log group members (different members are stored to different media):

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

Add log group members:

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

-the OCM examination will be launched one after another.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.