퐁숑퐁숑

OCP라는 자격증을 취득 후 그다음 단계는 OCM이라는 자격증을 취득할 수 있다. 2010년도 그당시만 해도 OCM 자격증 취득자가 별로 없었다. 덤프를 외워서 취득할 수 있는 OCP와 달리 OCM은 실습도 있고 상당히 까다롭기 때문에 그만큼 자격증 취득 여부에 따라 회사에 들어갈때 그만큼 가산점을 얻을 수 있었다. OCM을  취득하기 위해 가장 첫번째 실습 시나리오가 바로 DB 수동 설치이다. 오늘은 DB 수동 설치 후 EM을 추가하는 것 까지 정리해 보고자 한다.

[CONTENTS]

1. 수동 DB 생성 (OCM 0 scenario)
2. DBMS_SPACE_ADMIN 을 이용한 Dictionary → Local migration
3. Enterprise Manager 추가

--수동 DB 설치 (SID-testdb)

1. Nomount 수행을 위한 구성

Parameter file (수동으로 생성해야 하므로 pfile 생성.)

$] cd $ORACLE_HOME/dbs

$] vi inittestdb.ora

-다음과 같이 파라메터 값을 입력한다.

#testdb.__db_cache_size=83886080
#testdb.__java_pool_size=4194304
#testdb.__large_pool_size=4194304
#testdb.__shared_pool_size=67108864
#testdb.__streams_pool_size=0
#*.background_dump_dest='/u01/app/oracle/admin/testdb/bdump'
#*.compatible='10.2.0.1.0'
#*.core_dump_dest='/u01/app/oracle/admin/testdb/cdump'
#*.db_domain=''
#*.db_recovery_file_dest='/u01/oradata/flash_recovery_area'
#*.db_recovery_file_dest_size=2147483648
#*.job_queue_processes=10
#*.nls_language='KOREAN'
#*.nls_territory='KOREA'
#*.open_cursors=300
#*.processes=150
#*.remote_login_passwordfile='EXCLUSIVE'
#*.user_dump_dest='/u01/app/oracle/admin/testdb/udump'

--필수 파라메터 값

*.db_name='testdb'

*.db_block_size=8192

*.db_file_multiblock_read_count=16

*.control_files='/u01/oradata/testdb/control01.ctl','/u01/oradata/testdb/control02.ctl'

*.pga_aggregate_target=16777216

*.sga_target=167772160

*.undo_management='AUTO'

*.undo_tablespace='UNDOTBS1'

> password file 생성

$ orapwd file = orapwtestdb password=oracle entries=5

>SID 변경

$ export ORACLE_SID=testdb

>필수 경로 사전 생성

ocp@orcl : /home/oracle> cd /u01/oradata/
ocp@orcl : /u01/oradata> mkdir testdb
ocp@orcl : /home/oracle> cd /u01/app/oracle/admin/
ocp@orcl : /u01/app/oracle/admin/> mkdir testdb
ocp@orcl : /u01/app/oracle/admin/> cd testdb
ocp@orcl /u01/app/oracle/admin/testdb> mkdir bdump udump cdump pfile adump

> $ sqlplus sys/oracle as sysdba

SQL> startup nomount

2. create database 구문 작성

$] vi credb.sql

다음과 같이 작성을 한다.

CREATE DATABASE testdb

LOGFILE

GROUP 1 ('/u01/oradata/testdb/redo01.dbf') SIZE 10M reuse,

GROUP 2 ('/u01/oradata/testdb/redo02.dbf') SIZE 10M reuse,

GROUP 3 ('/u01/oradata/testdb/redo03.dbf') SIZE 10M reuse

MAXLOGFILES 5

MAXLOGMEMBERS 5

MAXLOGHISTORY 1

MAXDATAFILES 100

MAXINSTANCES 1

DATAFILE '/u01/oradata/testdb/system01.dbf' SIZE 600M reuse

AUTOEXTEND on NEXT 10M MAXSIZE UNLIMITED

UNDO TABLESPACE undotbs1

DATAFILE '/u01/oradata/testdb/undotbs1.dbf' SIZE 100M reuse

AUTOEXTEND on NEXT 10M MAXSIZE 1000M

sysaux DATAFILE '/u01/oradata/testdb/sysaux01.dbf' SIZE 600M reuse

AUTOEXTEND on NEXT 10M MAXSIZE UNLIMITED

DEFAULT TEMPORARY TABLESPACE temp

TEMPFILE '/u01/oradata/testdb/temp01.dbf' SIZE 50M reuse

CHARACTER SET KO16MSWIN949

;

$ sqlplus sys/oracle as sysdba

SQL> @credb.sql -- 4분정도 소요

> database 상태 확인

SQL> select instance_name,status from v$instance;

> 유저 설정

SQL> alter user sys identified by oracle

SQL> alter user system identified by oracle

3. DATABASE INSTALL

$] vi dbinstall.sql

다음과 같이 입력한다

@@$ORACLE_HOME/rdbms/admin/catalog.sql

@@$ORACLE_HOME/rdbms/admin/catproc.sql

conn system/oracle

@@$ORACLE_HOME/sqlplus/admin/pupbld.sql

$] sqlplus sys/oracle as sysdba

SQL>@dbinstall.sql

--10분정도 소요됨

4. DBMS_SPACE_ADMIN을 이용한 Dictionary ->Local migration

SYS> select tablespace_name,extent_management form dba_tablespaces;

TABLESPACE_NAME EXTENT_MAN
------------------------------ ----------
SYSTEM DICTIONARY
UNDOTBS1 LOCAL
SYSAUX LOCAL
TEMP LOCAL

4 rows selected.

-- 현재 system tablespace가 dictionary 방식으로 되어 있는 걸 확인할 수 있고,
이것을 local 방식으로 이전수행 할 수 있다.

SYS> shutdown immediate

SYS> startup mount exclusive

SYS>alter database open;

-- 나머지 tablespace 중에 dictionary 방식의 tablespace가 있다면
해당 tablespace를 read only로 변경한다. (ex ? alter tablespace xxxx read only; )

SYS>alter tablespace sysaux offline;

--제한모드 설정

SYS>alter system enable restricted session;

SYS>select logins from v$instance;

LOGINS
----------
RESTRICTED

1 row selected.

--이전 수행

SYS>exec DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL('SYSTEM');

SYS> alter tablespace sysaux online;

SYS> alter system disable restricted session;

SYS> select tablespace_name,extent_management from dba_tablespaces;

TABLESPACE_NAME EXTENT_MAN
------------------------------ ----------
SYSTEM LOCAL
UNDOTBS1 LOCAL
SYSAUX LOCAL
TEMP LOCAL

4 rows selected.

5. EM (Enterprise Manager Database Control) 수동 구성

ocp@testdb : /home/oracle> export ORACLE_SID=testdb
ocp@testdb : /home/oracle> echo $ORACLE_SID
testdb
ocp@testdb : /home/oracle> cd $ORACLE_HOME

--testdb의 EM설치 여부 확인
ocp@testdb : /u01/app/oracle/product/10.2.0/db_1> ls
assistants has log opmn root.sh
bin hs md oracore root.sh.old
cdata install mesg oraInst.loc slax
cfgtoollogs install.platform mgw ord sqlj
clone inventory network oui sqlnet.log
config javavm nls owm sqlplus
crs jdbc oc4j perl srvm
css jdk ocp.mycorpdomain.com_orcl plsql startup.log
ctx jlib ocp.mycorpdomain.com_prod precomp sysman
dbs jre odbc racg uix
demo ldap olap rdbms wwg
diagnostics lib OPatch relnotes xdk

--현재 testdb는 EM이 없음을 알 수 있다.

--사전 암호 변경

ocp@testdb : /u01/app/oracle/product/10.2.0/db_1> emca -config dbcontrol db -repos create

ocp@testdb : /u01/app/oracle/product/10.2.0/db_1> ls
assistants hs mesg oraInst.loc sqlj
bin install mgw ord sqlnet.log
cdata install.platform network oui sqlplus
cfgtoollogs inventory nls owm srvm
clone javavm oc4j perl startup.log
config jdbc ocp.mycorpdomain.com_orcl plsql sysman
crs jdk ocp.mycorpdomain.com_prod precomp uix
css jlib ocp.mycorpdomain.com_testdb racg wwg
ctx jre odbc rdbms xdk
dbs ldap olap relnotes
demo lib OPatch root.sh
diagnostics log opmn root.sh.old
has md oracore slax

ocp.mycorpdomain.com_testdb확인!!

ocp@testdb : /u01/app/oracle/product/10.2.0/db_1/install> cat portlist.ini
iSQL*Plus HTTP port number =5560
Enterprise Manager Console HTTP Port (orcl) = 1158
Enterprise Manager Agent Port (orcl) = 3938
Enterprise Manager Console HTTP Port (prod) = 5500
Enterprise Manager Agent Port (prod) = 1830
Enterprise Manager Console HTTP Port (testdb) = 5501
Enterprise Manager Agent Port (testdb) = 1831

==> http://192.168.100.100:5501/em

오늘은 예전에 OCP 자격증을 취득하기위해 공부했던 내용 중 TABLESPACE에 관한 내용을 정리해보고자 한다. Tablespace 실습 및 내용을 정리하기 전에 간단히 테이블 스페이스 개념을 집고 넘어가려 한다. 테이블 스페이스는 테이블을 생성할 수 있는 물리적인 파일이다. 데이터베이스는 데이터를  테이블에 담는다. 그 테이블들은 어디에 담기게 되는것일까? 바로  물리적 파일인 테이블 스페이스에 담기게 되는 것이다. 데이터베이스를 만드는 것은 테이블스페이스를 만드는 것이라 보아도 되겠다.

Tablespace 실습 및 내용 정리.

[Tablespace Syntax]

CREATE[undo|tempoary|smallfile] TABLESPACE테이블스페이스 이름
DATAFILE[tempfile]'/경로/파일 이름1.dbf' SIZE integer [T/G/M/K] [reuse]
[autoextend on next n [M] maxsize n [[M]| unlimnited]
,'파일 이름2' SIZE integer [M/K]...
[ENCRYPTION USING '3DES168' DEFAULT STORAGE (ENCRYPTION) ->11gR1~
[MINIMUM EXTENT integer [M/K]]
[BLOCK integer [k]]
[DEFAULT STORAGE(
INITIAL integer [M/K]
NEXT integer [M/K]
MAXEXTENTS integer
PCTINCREASE integer)]
[ONLINE|OFFLINE]
[LOGGING|NOLOGGING]
[PERMANENT|TEMPORARY]
[EXTENT MANAGEMENT
DICTIONARY|LOCAL
AUTOALLOCATE|UNIFORM SIZE integer [M/K]]
[SEGMENT SPACE MANAGEMENT (MANUAL/AUTO)]

- 테이블스페이스 생성하기

SYS> create tablespace sales_tbs
2 datafile '/u01/oradata/orcl/sales_tbs01.dbf' size 10m
3 autoextend on next 1m maxsize 100m
4 online logging
5 extent management local uniform size 128k
6 segment space management auto;

Tablespace created.

- sales_tbs 테이블 스페이스에 table생성
SYS> create table hr.sales00
2 (order# number(10) primary key, order_date date default sysdate)
3 tablespace sales_tbs;

Table created.

- 데이터 입력

SYS> begin
2 for i in 1..1000 loop
3 insert into hr.sales00 (order#) values (i);
4 end loop;
5 commit;
6 end;
7 /

PL/SQL procedure successfully completed.

-- EM을 통해 Sales_tbs의 tablespace용량이 늘어나는 것을 확인할 수 있다.

SYS> ed
Wrote file afiedt.buf

1 begin
2 for i in 1001..10000 loop
3 insert into hr.sales00 (order#) values (i);
4 end loop;
5 commit;
6* end;
SYS> /

PL/SQL procedure successfully completed.

--Viewing Tablespace Information(사용현황조회)

SYS> select * from DBA_TABLESPACE_USAGE_METRICS;

TABLESPACE_NAME USED_SPACE TABLESPACE_SIZE USED_PERCENT
------------------------------ ---------- --------------- ------------
EXAMPLE 8952 4194302 .213432414
SALES_TBS 64 12800 .5
SYSAUX 32632 4194302 .778007878
SYSTEM 60976 4194302 1.45378182
TEMP 0 4194302 0
UNDOTBS1 296 4194302 .007057193
USERS 840 4194302 .02002717

7 rows selected.

SYS>select tablespace_name from DBA_TABLESPACES;

TABLESPACE_NAME
------------------------------
SYSTEM
UNDOTBS1
SYSAUX
TEMP
USERS
EXAMPLE
INVENTORY
RMAN_TBS
UTEST02
UTEST03
UTEST04

TABLESPACE_NAME
------------------------------
UTEST05

12 rows selected.

-- tablespace 공간 변경 3가지 방법

SYS> --1. add datafile
SYS> alter tablespace inventory
2 add datafile '/u01/oradata/orcl/inventory02.dbf' size 10m;

Tablespace altered.

SYS> --2. resize
SYS> alter database datafile '/u01/oradata/orcl/inventory02.dbf' resize 20m;
Database altered.

SYS> --3. autoextend
SYS> alter database datafile '/u01/oradata/orcl/inventory02.dbf'
2 autoextend on next 1m maxsize 50m;

Database altered.

--tablespace 이동(datafile)

1. System TBS (offline 설정 안됨)

1) shutdown immediate
2) cp를 통해 복사(이동) - 물리적
3) startup mount
4) alter database rename file '원래' to '새로운'; - 논리적
5) alter database open;
6) 이전 file 삭제

2. Non-System TBS

1) 해당 TBS. offline 설정
2) cp를 통해 복사(이동) - 물리적
3) alter tablespace XXXX rename datafile '원래' to '새로운' ; - 논리적
4) 해당 TBS online설정
5) 이전 file삭제

-- tablespace삭제하기

1. TBS가 비어있는 경우
SQL> drop tablespace XXXXX;

2. TBS에 Object가 존재하는 경우 포함 삭제
SQL> drop tablespace XXXX including contents;

3. OSfile 동반삭제
SQL> drop tablespace XXXX including contents and datafiles;

4. 다른 TBS의 Object와 참조무결성 제약조건에 연결되어 있는 경우.
SQL> drop tablespace XXXX including contents and datafiles cascade constraints;

--close backup

ocp@orcl : /home/oracle> ls
20100823-3.sql Desktop insert.sql lob_04_04.sql sqlnet.log
afiedt.buf dict.sql inst.sql lock_trace.sql test1.sql
backup emp10000.sql jazn.sh lock_trace_user.sql v_scan.sql
const.sql hidden.sql lab_01_07.sql orcl_start.sh xbh.sql
creuser.sql holding.sql labs practice01_02.sql
ocp@orcl : /home/oracle> cd backup
ocp@orcl : /home/oracle/backup> ls
pfile_backup
ocp@orcl : /home/oracle/backup> mkdir close open rman rman_con archive
ocp@orcl : /home/oracle/backup> ls
archive close open pfile_backup rman rman_con
ocp@orcl : /home/oracle/backup> cd close/
ocp@orcl : /home/oracle/backup/close> cp /u01/oradata/orcl/* ./
cp: omitting directory `/u01/oradata/orcl/arch1'
ocp@orcl : /home/oracle/backup/close>

################################
############실습################
################################

1. WS1 Tablespace 생성 관리 실습

########################################
#####실습 1 데이터베이스의 저장구조##########
########################################

SYS>!t_scan.sql
/bin/bash: t_scan.sql: command not found

--자주 사용함으로 스크립트 생성

SYS>!vi t_scan.sql

SYS>!cat t_scan.sql
col tablespace_name format a15;
col file_name format a45;
select tablespace_name,status,contents,
extent_management,segment_space_management
from dba_tablespaces;

SYS>@t_scan

TABLESPACE_NAME STATUS CONTENTS EXTENT_MAN SEGMEN
--------------- --------- --------- ---------- ------
SYSTEM onLINE PERMANENT LOCAL MANUAL
UNDOTBS1 onLINE UNDO LOCAL MANUAL
SYSAUX onLINE PERMANENT LOCAL AUTO
TEMP onLINE TEMPORARY LOCAL MANUAL
USERS onLINE PERMANENT LOCAL AUTO
EXAMPLE onLINE PERMANENT LOCAL AUTO
INVENTORY onLINE PERMANENT LOCAL AUTO
INSA onLINE PERMANENT LOCAL AUTO
RMAN_TBS onLINE PERMANENT LOCAL AUTO

9 rows selected.

SYS>select tablespace_name,bytes,file_name from dba_data_files;

TABLESPACE_NAME BYTES FILE_NAME
--------------- ---------- ---------------------------------------------
USERS 6553600 /u01/oradata/orcl/users01.dbf
SYSAUX 272629760 /u01/oradata/orcl/sysaux01.dbf
UNDOTBS1 36700160 /u01/oradata/orcl/undotbs01.dbf
SYSTEM 513802240 /u01/oradata/orcl/system01.dbf
EXAMPLE 104857600 /u01/oradata/orcl/example01.dbf
INSA 2097152 /u01/oradata/orcl/insa01.dbf
INSA 2097152 /u01/oradata/orcl/insa03.dbf
INVENTORY 104857600 /u01/oradata/orcl/inventory
INSA 2097152 /home/oracle/backup/insa02.dbf
RMAN_TBS 104857600 /u01/oradata/orcl/rman01.dbf

10 rows selected.

SYS>select tablespace_name,bytes,file_name from dba_temp_files;

TABLESPACE_NAME BYTES FILE_NAME
--------------- ---------- ---------------------------------------------
TEMP 41943040 /u01/oradata/orcl/temp01.dbf

########################################
실습 2 사용자용 테이블스페이스 생성#####
########################################

SYS>drop tablespace insa including contents and datafiles cascade constraints;

Tablespace dropped.

SYS>create tablespace insa
2 datafile '/u01/oradata/orcl/insa01.dbf' size 1M
3 segment space management auto;

Tablespace created.

SYS>select tablespace_name,bytes,file_name from dba_data_files;

TABLESPACE_NAME BYTES FILE_NAME
--------------- ---------- ---------------------------------------------
USERS 6553600 /u01/oradata/orcl/users01.dbf
SYSAUX 272629760 /u01/oradata/orcl/sysaux01.dbf
UNDOTBS1 36700160 /u01/oradata/orcl/undotbs01.dbf
SYSTEM 513802240 /u01/oradata/orcl/system01.dbf
EXAMPLE 104857600 /u01/oradata/orcl/example01.dbf
INSA 1048576 /u01/oradata/orcl/insa01.dbf
INVENTORY 104857600 /u01/oradata/orcl/inventory
RMAN_TBS 104857600 /u01/oradata/orcl/rman01.dbf

8 rows selected.

SYS>@t_scan.sql

TABLESPACE_NAME STATUS CONTENTS EXTENT_MAN SEGMEN
--------------- --------- --------- ---------- ------
SYSTEM onLINE PERMANENT LOCAL MANUAL
UNDOTBS1 onLINE UNDO LOCAL MANUAL
SYSAUX onLINE PERMANENT LOCAL AUTO
TEMP onLINE TEMPORARY LOCAL MANUAL
USERS onLINE PERMANENT LOCAL AUTO
EXAMPLE onLINE PERMANENT LOCAL AUTO
INVENTORY onLINE PERMANENT LOCAL AUTO
INSA onLINE PERMANENT LOCAL AUTO
RMAN_TBS onLINE PERMANENT LOCAL AUTO

9 rows selected.

##########################################
실습 3 : 사용자용 테이블스페이스 확장#####
##########################################

SYS>alter database datafile
2 '/u01/oradata/orcl/insa01.dbf' resize 2M;

Database altered.

SYS>select tablespace_name, bytes/1024/1024,
2 file_name from dba_data_files;

TABLESPACE_NAME BYTES/1024/1024 FILE_NAME
--------------- --------------- ---------------------------------------------
USERS 6.25 /u01/oradata/orcl/users01.dbf
SYSAUX 260 /u01/oradata/orcl/sysaux01.dbf
UNDOTBS1 35 /u01/oradata/orcl/undotbs01.dbf
SYSTEM 490 /u01/oradata/orcl/system01.dbf
EXAMPLE 100 /u01/oradata/orcl/example01.dbf
INSA 2 /u01/oradata/orcl/insa01.dbf
INVENTORY 100 /u01/oradata/orcl/inventory
RMAN_TBS 100 /u01/oradata/orcl/rman01.dbf

8 rows selected.

SYS>alter tablespace insa
2 add datafile '/u01/oradata/orcl/insa02.dbf' size 2M;

Tablespace altered.

SYS>select tablespace_name,bytes,file_name from dba_data_files;

TABLESPACE_NAME BYTES FILE_NAME
--------------- ---------- ---------------------------------------------
USERS 6553600 /u01/oradata/orcl/users01.dbf
SYSAUX 272629760 /u01/oradata/orcl/sysaux01.dbf
UNDOTBS1 36700160 /u01/oradata/orcl/undotbs01.dbf
SYSTEM 513802240 /u01/oradata/orcl/system01.dbf
EXAMPLE 104857600 /u01/oradata/orcl/example01.dbf
INSA 2097152 /u01/oradata/orcl/insa01.dbf
INSA 2097152 /u01/oradata/orcl/insa02.dbf
INVENTORY 104857600 /u01/oradata/orcl/inventory
RMAN_TBS 104857600 /u01/oradata/orcl/rman01.dbf

9 rows selected.

SYS>alter tablespace insa add datafile
2 '/u01/oradata/orcl/insa03.dbf' size 2M
3 Autoextend on Next 1M Maxsize 10M;

Tablespace altered.

SYS>select tablespace_name,bytes,file_name from dba_data_files;

TABLESPACE_NAME BYTES FILE_NAME
--------------- ---------- ---------------------------------------------
USERS 6553600 /u01/oradata/orcl/users01.dbf
SYSAUX 272629760 /u01/oradata/orcl/sysaux01.dbf
UNDOTBS1 36700160 /u01/oradata/orcl/undotbs01.dbf
SYSTEM 513802240 /u01/oradata/orcl/system01.dbf
EXAMPLE 104857600 /u01/oradata/orcl/example01.dbf
INSA 2097152 /u01/oradata/orcl/insa01.dbf
INSA 2097152 /u01/oradata/orcl/insa02.dbf
INVENTORY 104857600 /u01/oradata/orcl/inventory
INSA 2097152 /u01/oradata/orcl/insa03.dbf
RMAN_TBS 104857600 /u01/oradata/orcl/rman01.dbf

10 rows selected.

##########################################
실습 4 : 사용자용(일반) 데이터 파일 이동##
##########################################

SYS>alter tablespace insa offline;

Tablespace altered.

SYS>!cp /u01/oradata/orcl/insa02.dbf /home/oracle/backup/

SYS>alter tablespace insa rename
2 datafile '/u01/oradata/orcl/insa02.dbf'
3 to '/home/oracle/backup/insa02.dbf';

Tablespace altered.

SYS>alter tablespace insa online;

Tablespace altered.

SYS>!rm /u01/oradata/orcl/insa02.dbf

SYS>select tablespace_name,bytes,file_name from dba_data_files;

TABLESPACE_NAME BYTES FILE_NAME
--------------- ---------- ---------------------------------------------
USERS 6553600 /u01/oradata/orcl/users01.dbf
SYSAUX 272629760 /u01/oradata/orcl/sysaux01.dbf
UNDOTBS1 36700160 /u01/oradata/orcl/undotbs01.dbf
SYSTEM 513802240 /u01/oradata/orcl/system01.dbf
EXAMPLE 104857600 /u01/oradata/orcl/example01.dbf
INSA 2097152 /u01/oradata/orcl/insa01.dbf
INSA 2097152 /home/oracle/backup/insa02.dbf
INVENTORY 104857600 /u01/oradata/orcl/inventory
INSA 2097152 /u01/oradata/orcl/insa03.dbf
RMAN_TBS 104857600 /u01/oradata/orcl/rman01.dbf

10 rows selected.

#################################################
실습 5 : System 테이블스페이스 데이터 파일 이동##
#################################################

SYS>shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS>!cp /u01/oradata/orcl/system01.dbf /home/oracle/backup/system01.dbf

SYS>startup mount;
ORACLE instance started.

Total System Global Area 167772160 bytes
Fixed Size 1218292 bytes
Variable Size 83888396 bytes
Database Buffers 75497472 bytes
Redo Buffers 7168000 bytes
Database mounted.
SYS>alter database rename
2 file '/u01/oradata/orcl/system01.dbf'
3 to '/home/oracle/backup/system01.dbf';

Database altered.

SYS>!rm /u01/oradata/orcl/system01.dbf

SYS>alter database open;

Database altered.

SYS>select tablespace_name,bytes,file_name from dba_data_files;

TABLESPACE_NAME BYTES FILE_NAME
--------------- ---------- ---------------------------------------------
USERS 6553600 /u01/oradata/orcl/users01.dbf
SYSAUX 272629760 /u01/oradata/orcl/sysaux01.dbf
UNDOTBS1 36700160 /u01/oradata/orcl/undotbs01.dbf
SYSTEM 513802240 /home/oracle/backup/system01.dbf
EXAMPLE 104857600 /u01/oradata/orcl/example01.dbf
INSA 2097152 /u01/oradata/orcl/insa01.dbf
INSA 2097152 /home/oracle/backup/insa02.dbf
INVENTORY 104857600 /u01/oradata/orcl/inventory
INSA 2097152 /u01/oradata/orcl/insa03.dbf
RMAN_TBS 104857600 /u01/oradata/orcl/rman01.dbf

10 rows selected.

==> System Tablespace를 다시 원래 경로로 바꾸기!

SYS>shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS>!cp /home/oracle/backup/system01.dbf /u01/oradata/orcl/system01.dbf

SYS>startup mount;
ORACLE instance started.

Total System Global Area 167772160 bytes
Fixed Size 1218292 bytes
Variable Size 83888396 bytes
Database Buffers 75497472 bytes
Redo Buffers 7168000 bytes
Database mounted.

SYS>alter database rename
2 file '/home/oracle/backup/system01.dbf'
3 to '/u01/oradata/orcl/system01.dbf';

Database altered.

SYS>!rm /home/oracle/backup/system01.dbf

SYS>alter database open;

Database altered.

SYS>select tablespace_name,bytes,file_name from dba_data_files;

TABLESPACE_NAME BYTES FILE_NAME
--------------- ---------- ---------------------------------------------
USERS 6553600 /u01/oradata/orcl/users01.dbf
SYSAUX 272629760 /u01/oradata/orcl/sysaux01.dbf
UNDOTBS1 36700160 /u01/oradata/orcl/undotbs01.dbf
SYSTEM 513802240 /u01/oradata/orcl/system01.dbf
EXAMPLE 104857600 /u01/oradata/orcl/example01.dbf
INSA 2097152 /u01/oradata/orcl/insa01.dbf
INSA 2097152 /home/oracle/backup/insa02.dbf
INVENTORY 104857600 /u01/oradata/orcl/inventory
INSA 2097152 /u01/oradata/orcl/insa03.dbf
RMAN_TBS 104857600 /u01/oradata/orcl/rman01.dbf

10 rows selected.

#################################################
실습 6. 테이블 스페이스의 삭제###################
#################################################

SYS>drop tablespace insa
2 including contents and datafiles cascade constraints;

Tablespace dropped.

SYS>select tablespace_name,bytes,file_name from dba_data_files;

TABLESPACE_NAME BYTES FILE_NAME
--------------- ---------- ---------------------------------------------
USERS 6553600 /u01/oradata/orcl/users01.dbf
SYSAUX 272629760 /u01/oradata/orcl/sysaux01.dbf
UNDOTBS1 36700160 /u01/oradata/orcl/undotbs01.dbf
SYSTEM 513802240 /u01/oradata/orcl/system01.dbf
EXAMPLE 104857600 /u01/oradata/orcl/example01.dbf
INVENTORY 104857600 /u01/oradata/orcl/inventory
RMAN_TBS 104857600 /u01/oradata/orcl/rman01.dbf

7 rows selected.

2. 1552 WS1-1-ch05. Creation Tablespace LAB (OCM-1)

문제1. dictionary를 생성할 수 없음 local로 생성 가능.

문제2.

SYS>create tablespace utest02
2 datafile '/home/oracle/backup/tstest/utest02.dbf' size 1m reuse
3 autoextend on next 1m maxsize 10m
4 extent management local uniform size 100k;

Tablespace created.

문제3.

SYS>create tablespace utest03
2 datafile '/home/oracle/backup/tstest/utest03.dbf' size 1m reuse
3 extent management local uniform size 100k;

Tablespace created.

문제4.

SYS>create tablespace utest04
2 datafile '/home/oracle/backup/tstest/utest04.dbf' size 2m reuse
3 extent management local autoallocate
4 segment space management auto;

Tablespace created.

문제5.

SYS>create temporary tablespace utest05
2 tempfile '/home/oracle/backup/tstest/utest05.dbf' size 10m reuse
3 extent management local uniform size 1m;

Tablespace created.

--tablespace 생성 확인

SYS>exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
ocp@orcl : /home/oracle> cd backup/tstest
ocp@orcl : /home/oracle/backup/tstest> ls
utest02.dbf utest03.dbf utest04.dbf utest05.dbf 

1. USER

Database 내에서 Username은 Unique(중복되지 않아야)해야 한다.

User create syntax

CREATE USER user_name

IDENTIFIED {BY password | EXTERNALLY | GLOBALLLY} => 인증

(OS인증방식) (제3의 App을 이용한 인증 ex: Directory/LDAP)

[DEFAULT TABLESPACE tablespace] - 지정 안하면 USER TABLESPACE로 지정

[TEMPORARY TABLESPACE tablespace] - default temporary tablespace : temp

[QUOTA {integer[K|M] | UNLIMITED} on tablespace -Tablespace에서 사용할 수 있는 할당값

[QUOTA {integer[K|M] | UNLIMITED} on tablespace]...]

[ACCOUNT LOCK | UNLOCK] - default UNLOCK

[PASSWORD EXPIRE] - 사용자가 로그인시 passwd만료를 표시해주고 바꾸도록 해주는 옵션

[PROFILE {Profile | DEFAULT}]

- default로 DEFAULT라는 이름의 profile이 있음 자원제한 9개 패스워드제한 6개

CREATE문을 ALTER로 바구면 수정문이 됨

EM에서 사용자 생성시 CONNECT권한 자동 부여

데스알고리즘(DES)으로 오라클의 password를 복구 가능 11g부터는 password컬럼에 내용이 보안상 안보임

사용자 삭제시 사용자가 로그인 시에는 DROP이 안됨!!

사용자가 object 소유시에는 cascade 옵션을 사용해서 DROP시켜야 함!!

SQL> alter database default tablespace 테이블 스페이스 명; - 해주면 Default tablespace가 바뀜

SQL> alter database default temporary tablespace 테이블 스페이스 명;

2. ROLE

CREATE ROLE rolename

role은 일반 사용자에게 권한ㅇ르 부여 및 회수 하듯이 롤에 권한을 부여, 회수할 수 있다.

시스템 권한처럼 사용자나 롤로부터 부여 및 회수가 가능하다.

시스템 권한(System Privilege)과 객체 권한(Object Privilege)으로 구성 될 수 있다.

각 사용자에게 부여된 롤은 활성화 되거나 비 활성화 될 수 있따.

role은 활성화하기 위해 암호가 필요 할 수 있다.

role은 특정 사용자가 소유하는 것이 아니기 때문에 어떤 스키마에도 저장되지 않는다.

권한부여

GRANT privilege on [user.]object TO USER[or ORLENAME] = object privilege

GRANT privilege TO USER[orPROLENAME] = system privilege

ALTER user HR default role {role_name | none};

SET ROLE {vacationdba | all | none};

CREATE ROLE secure_application_role

IDENTIFIED USING <security_procedure_name>; pl/sql로 만든 프로그램을 이용한 롤사용

3. profiles(자원제한 설정)

why?? 한정된 자원을 효과적으로 사용하기 위해

SQL>select * from dba_profiles;

session level = limit(한계) 초과시 session이 강제로 log out 당함 (error message 뜸)

call level = limit초과시 해당 작업이 중단됨(error message 뜸)

자원 제한이 설정되어 있을 때 실행 계획에 의해서 실행과 동시에 작업을 안하고 error를 발생시킴

현재 등록 되어 있는 profiles 조회

SQL> select * from dba_profiles;

PROFILE 만들기

CREATE PROFILE profilename LIMIT

CPU_PER_SESSION - 1/100 초 단위로 측정한 총 CPU이용시간

SESSIONS_PER_USER - 각 사용자마다 허용된 동시 세션의 수

CONNECT_TIME - 분 단위로 측정한, 경과된 접속시간

IDLE_TIME -분 단위로 측정한 비활동 시간
: 서버 프로세스에 대해서만 IDLE_TIME이 계산됩니다.
응용프로그램 작업은 포함되지 않습니다.
IDLE_TIME은 오랜시간 수행되는 질의나 다른 작업들은 포함하지 않습니다.

LOGICAL_READS_PER_SESSION - 데이터 블록 수 (물리적:디스크로부터,논리적:메모리로부터 읽은)
: LOGICAL_READS_PER_SESSION은 메모리와 디스크 모두 에게서
읽는 총 횟수를 제한 합니다.

PRIVATE_SGA - 바이트 단위로 측정한 SGA 내의 전용(private) 공간(MTS만)
: PRIVATE_SGA는 다중 스레드 서버(MTS) 구성 일 때만 적용됩니다.

CPU_PER_SEESION - 하나의 Call 당 CPU 사용시간

SESSIONS_PER_USER - 한 Call 당 I/O를 할 수 있는 데이터 블록 수

COMPOSITE_LIMIT - cpu_per_session, read_per_session, connect_time, private_sga의 합이 설정한 수치를 넘어가면 제한

패스워드 제한설정

권장 : 30일 마다 변경 권장

PASSWORD history : 이전 암호를 기억해 놓았다가 다음에 변경시 동일한 암호사용을 금지함

password_reuse_time : 동일한 password를 적용한 기간동안 사용금지

password_reuse_max : 입력된 value값만큼만 사용가능한 횟수를 제한 ex: 3이라고 입력하면 3번만 사용가능

password_life_time : password 생명주기 ex : 30 -> 30일마다 변경해야함

password_grace_time : password 변경 만료 알림을 value일 전부터 알림

failed_login_attempts : password 입력실패시 재시도 가능횟수 최종 실패시 계정 lock걸림

password_lock_time : lock걸렸을때 value값만큼 잠겨있음 1일단위임 1/24는 1시간 1/1440은 1분

password complexity verification(패스워드 복잡성)password설정시 제약조건

VERIFY_FUNCTION password 복잡도 함수

rdbms/admin/utlpwdmg.sql (이경로에 관리용 스크립트가 700여개 존재)-수정가능

scripts 실행

해제시

SQL>alter profile default limit password_verify_function null

password_reuse_time unlimited

password_reuse_max unlimited;

SQL>drop fuction verify_function;

ORACLE error코드중 20001~21000은 비어있는데 사용자정의 오류코드로 사용자가 만들어 쓴다