코봉이와 오라클: Oracle_Admin

레이블이 Oracle_Admin_1인 게시물을 표시합니다. 모든 게시물 표시

2009년 8월 19일 수요일

OMF

▣ OMF : 파일의 위치와 이름이 자동

Datafile : DB_CREATE_FILE_DEST
Redo : DB_CREATE_ONLINE_LOG_DEST_n
FRA : DB_RECOVERY_FILE_DEST

S SYS> alter tablespace ts1 add datafile size 20M;

Tablespace altered.

S SYS> show parameter db_create_file

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

db_create_file_dest string /u01/app/oracle/oradata

S SYS> alter system set db_create_file_dest=' ' scope=both;

System altered.

S SYS> create tablespace test;

create tablespace test

ERROR at line 1:

ORA-02199: missing DATAFILE/TEMPFILE clause

S SYS> alter system set db_create_file_dest='/u01/app/oracle/oradata' scope=both;

System altered.

S SYS> create tablespace test;

Tablespace created.

▣ Storage for Locally Managed Tablespaces

ASSM : Free Space 할당에 대한 경합 줄임 by 트리구조로 빈 공간 연결해서.

▣ Tablespace 종류

Data(permanent)
Temp : user
Undo : instance

▣ Tablespace in the Preconfigured Database

교체불가 필수 : SYSTEM, SYSAUX
필수 : UNDOTBS1, TEMP

▣ Changing the size : Datafile의 크기에 의해 결정 => 자동증가값 포함

Ex) a.dbf : 10M 최대 1G까지 늘어남

a2.dbf : 20M 최대 2G까지 늘어남

=> 가지고 있는 tablespace의 크기는 : 3G

▣ Datafile 삭제

S SYS> select OWNER,SEGMENT_NAME,SEGMENT_TYPE

2 from dba_segments

3 where TABLESPACE_NAME='TS3';

OWNER SEGMENT_NAME SEGMENT_TYPE

------------------------------ -------------------- ------------------

HR X TABLE

S SYS> truncate table hr.x;

Table truncated.

S SYS> alter tablespace ts3 drop datafile '/u01/app/oracle/oradata/ORCL/datafile/o1_mf_ts3_58od8rw2_.dbf';

alter tablespace ts3 drop datafile '/u01/app/oracle/oradata/ORCL/datafile/o1_mf_ts3_58od8rw2_.dbf'

ERROR at line 1:

ORA-03262: the file is non-empty

S SYS> select segment_name,segment_type from dba_segments

2 where tablespace_name='TS3';

SEGMENT_NAME SEGMENT_TYPE

-------------------- ------------------

BIN$cWrUG8tLFxHgQAB/ TABLE

AQA4Uw==$0

S SYS> purge dba_recyclebin;

S SYS> alter tablespace ts3 drop datafile '/u01/app/oracle/oradata/ORCL/datafile/o1_mf_ts3_58od8rw2_.dbf';

Tablespace altered.

▣ Actions with Tablespaces (EM)

General D이	Alter tablespace T/S명 add datafile ' ';
Make Locally Managed	Dbua로 업그레이드한 옛 파일
Make Readonly	Alter tablespace ts명 read only;
Make Writable	Alter tablespace ts명 read write;
Place Online	Alter tablespace ts명 online
Reorganize	Move + index 재구성+통계수집
Run Segment Advisor	공간 줄일 객체 찾아줌
Show Dependencies	관련요소 찾음
Show Tablespace Contents	Segment + extents 측면으로 보여줌 => tablespacemap이 나옴
Take Offline	Alter tablespace offline;

2009년 8월 17일 월요일

External Table

▣ 성경을 DB로 이동시키는 방법

[oracle@orcl ~]$ cp /mnt/hgfs/pc/dbo_bible.txt ./

SCOTT> create table bible(c1 number(6), c2 number(6), c3 number(6), c4 number(6), c5 varchar2(4000));

Table created

[oracle@edrsr4p1 ~]$ cat bible.ctl

load data

characterset ko16mswin949

infile 'dbo_bible.txt'

into table bible

fields terminated by ','

(c1,c2,c3,c4,c5)

[oracle@edrsr4p1 ~]$ sqlldr scott/tiger control=bible.ctl log=bible.log direct=y

SQL*Loader: Release 10.2.0.1.0 - Production on Sun Aug 16 21:34:57 2009

Load completed - logical record count 17812.

PUMP

▣ Data Pump 의 장점

Fine-grained object and data selection
Explicit specification of database version
Parallel execution
Estimation of the export job space consumption
Network mode in a distributed environment
Remapping capabilities during import
Data sampling and metadata compression

▣ Data Pump Export/Import interface

Command line
Parameter file
Interactive command line => 사용하지 말것
Database control => EM

▣ Data Pump Export/Import modes

Full => 주의 : shared pool 부족할 수 있다.
Schema
Table
Tablespace => exp/imp 에서는 불가
Transportable tablespace

▣ 사용예

[oracle@orcl ~]$ expdp help=y

[oracle@orcl ~]$ expdp scott/tiger DIRECTORY=dmpdir DUMPFILE=scott.dmp => export

[oracle@orcl ~]$ ls scott.*

scott.dmp

S SYS> drop user scott cascade;

S SYS> create user scott identified by tiger;

User created.

S SYS> grant dba to scott;

S SYS> grant create procedure to scott;

Grant succeeded.

[oracle@orcl ~]$ impdp help=y

[oracle@orcl ~]$ impdp scott/tiger DIRECTORY=dmpdir DUMPFILE=scott.dmp

[oracle@orcl ~]$ expdp scott/tiger parfile=p.par

[oracle@orcl ~]$ cat >> p.par<<EOF

> DIRECTORY=dmpdir dumpfile=scott2.dmp

> EOF

[oracle@orcl ~]$ expdp scott/tiger parfile=p.par

▣ REMAP_SCHEMA : 해당 유저에 존재하는 모든 객체를 다른 유저로 이동

[oracle@edrsr4p1 ~]$ expdp scott/tiger directory=dmpdir dumpfile=scott.dmp

[oracle@edrsr4p1 ~]$ impdp system/oracle directory=dmpdir dumpfile=scott.dmp remap_schema='SCOTT':'HR'

Import: Release 10.2.0.1.0 - Production on Sunday, 16 August, 2009 21:43:07

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

With the Partitioning, OLAP and Data Mining options

Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded

Starting "SYSTEM"."SYS_IMPORT_FULL_01": system/******** directory=dmpdir dumpfile=scott.dmp r hema=SCOTT:HR

Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA

Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE

Processing object type SCHEMA_EXPORT/TABLE/TABLE

ORA-39083: Object type TABLE failed to create with error:

ORA-02264: name already used by an existing constraint

Failing sql is:

CREATE TABLE "HR"."COUNTRY" ("COUNTRY_ID" CHAR(2), "COUNTRY_NAME" VARCHAR2(40), CONSTRAINT "C C_ID_PK" PRIMARY KEY ("COUNTRY_ID") ENABLE) ORGANIZATION INDEX NOCOMPRESS PCTFREE 10 INITRANS ANS 255 NOLOGGING STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINC FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "EXAMPLE" PCTT

Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA

. . imported "HR"."TM" 3.818 MB 100000 rows

. . imported "HR"."BIBLE" 2.692 MB 17761 rows

. . imported "HR"."PT":"PT_P4" 2.435 MB 150001 rows

. . imported "HR"."PT":"PT_P1" 1.615 MB 99999 rows

. . imported "HR"."PT":"PT_P2" 1.625 MB 100000 rows

. . imported "HR"."PT":"PT_P3" 1.625 MB 100000 rows

. . imported "HR"."PT2":"PT2_P1"."PT2_P1_S1" 1.807 MB 100001 rows

. . imported "HR"."DEPT" 5.656 KB 4 rows

. . imported "HR"."EMP" 7.820 KB 14 rows

. . imported "HR"."SALGRADE" 5.585 KB 5 rows

. . imported "HR"."BONUS" 0 KB 0 rows

. . imported "HR"."PT2":"PT2_P1"."PT2_P1_S2" 0 KB 0 rows

. . imported "HR"."PT2":"PT2_P1"."PT2_P1_S3" 0 KB 0 rows

. . imported "HR"."PT2":"PT2_P1"."PT2_P1_S4" 0 KB 0 rows

. . imported "HR"."PT2":"PT2_P2"."PT2_P2_S1" 0 KB 0 rows

. . imported "HR"."PT2":"PT2_P2"."PT2_P2_S2" 0 KB 0 rows

. . imported "HR"."PT2":"PT2_P2"."PT2_P2_S3" 0 KB 0 rows

. . imported "HR"."PT2":"PT2_P2"."PT2_P2_S4" 0 KB 0 rows

. . imported "HR"."PT2":"PT2_P3"."PT2_P3_S1" 0 KB 0 rows

. . imported "HR"."PT2":"PT2_P3"."PT2_P3_S2" 0 KB 0 rows

. . imported "HR"."PT2":"PT2_P3"."PT2_P3_S3" 0 KB 0 rows

. . imported "HR"."PT2":"PT2_P3"."PT2_P3_S4" 0 KB 0 rows

. . imported "HR"."PT2":"PT2_P4"."PT2_P4_S1" 0 KB 0 rows

. . imported "HR"."PT2":"PT2_P4"."PT2_P4_S2" 0 KB 0 rows

. . imported "HR"."PT2":"PT2_P4"."PT2_P4_S3" 0 KB 0 rows

. . imported "HR"."PT2":"PT2_P4"."PT2_P4_S4" 0 KB 0 rows

Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX

Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT

Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS

Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_SPEC

Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE

Processing object type SCHEMA_EXPORT/PACKAGE/COMPILE_PACKAGE/PACKAGE_SPEC/ALTER_PACKAGE_SPEC

Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE

Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_BODY

Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT

Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

Job "SYSTEM"."SYS_IMPORT_FULL_01" completed with 1 error(s) at 21:43:19

▣ 이동된 객체 확인

SQL> show user

USER is "HR"

SQL> select * from tab;

TNAME TABTYPE CLUSTERID

------------------------------ ------- ----------

REGIONS TABLE

COUNTRIES TABLE

LOCATIONS TABLE

DEPARTMENTS TABLE

JOBS TABLE

EMPLOYEES TABLE

JOB_HISTORY TABLE

EMP_DETAILS_VIEW VIEW

EMPDEPT CLUSTER

EMPDEPT2 CLUSTER

EMPX TABLE 1

TNAME TABTYPE CLUSTERID

------------------------------ ------- ----------

DEPTX TABLE 2

PT TABLE

PT2 TABLE

DEPT TABLE

EMP TABLE

BONUS TABLE

SALGRADE TABLE

TM TABLE

BIBLE TABLE

20 rows selected.

Loader

▣ 간단한 Loader 연습

▣ 다시 실행

Moving Data

▣ 데이터의 이전

External Table	Create(인식) => insert ~ select ~
Loader	Insert(빠름 - log 생성 x, 제약조건 x 가능
expdp / impdp	10g 전용
exp / imp	9i 전용(BMR - 블록 복구에서 사용)
	DataSource	DataTarget	복구시점
BNR	-	자기자신	현재 or 과거
Export/import	-	다른 db	과거(export 시점)

▣ exp/imp 사용법

scott 계정을 export 하고 싶으면

[oracle@orcl ~]$ exp scott/tiger => 대답은 긍정적으로..

=> scott계정에 있는 모든 파일을 expdat.dmp에 저장한다.

=> 주의 : 자동 덮어쓰기 됨(기존 파일 사라질 수 있음)

---- 중략 ----

Export terminated successfully with warnings. => Successfully 확인

[oracle@orcl ~]$ ls exp*

expdat.dmp

import 준비 작업 ↓

S SYS> drop user scott cascade;

S SYS> grant create session,resource,create table,create procedure,create sequence to scott

S SYS> grant create view,create synonym to scott

scott 계정 import

[oracle@orcl ~]$ imp scott/tiger

Import entire export file (yes/no): no > yes 나머지는 기본값 이것만 yes로 설정

[oracle@orcl ~]$ sqlplus scott/tiger

S SCOTT> @t

▣ Pump Package

S SYS> @fp

Enter value for key: PUMP

OBJECT_NAME

------------------------------

DBMS_DATAPUMP

ORACLE_DATAPUMP

DBMS_STREAMS_DATAPUMP

DBMS_STREAMS_DATAPUMP_UTIL

DBMS_DATAPUMP_UTL

S SYS> spool DBMS_DATAPUMP

S SYS> desc DBMS_DATAPUMP

S SYS> spool off

▣ Directory Objects : 폴더의 Path를 저장하는 객체

▣ Create Directory Objects

2009년 8월 11일 화요일

Parameter File

▣ oracle Instance 가 shutdown 에서 nomount 로 갈 때 아래 파일을 읽음

[oracle@orcl ~]$ echo $ORACLE_SID

orcl

[oracle@orcl ~]$ ls $ORACLE_HOME/dbs/spfileorcl.ora

=> spfile (9i부터 지원,운영 중 수정가능,binary 파일-편집기 편집 불가) ※ binary file이기 때문에 운영중 수정 가능

=> 이 파일이 없으면 같은 디렉토리에 있는 initorcl.ora를 읽음

=> pfile (모든 버전에서 지원, 운영 중 수정 불가,text 파일-편집기로 편집 가능)

▣ 파라미터 파일 백업하는 디렉토리 생성

[oracle@orcl ~]$ mkdir _setting

▣ spfile에서 pfile 백업 파일 작성

S SYS> create pfile='/home/oracle/_setting/initorcl.ora' from spfile;

File created.

S SYS> !

[oracle@orcl ~]$ ls _setting/

initorcl.ora

※ 파라미터 파일과 컨트롤 파일이 매칭되면 DB는 정상적으로 OPEN 됨

▣ 파라미터 보기

▣ EM에서 검색하는 방법

▣ Simplified Initialization Parameter

[oracle@edrsr4p1 ~]$ vi _setting/initorcl.ora.090519_1

orcl.__db_cache_size=188743680

orcl.__java_pool_size=4194304

orcl.__large_pool_size=4194304

orcl.__shared_pool_size=83886080

orcl.__streams_pool_size=0

*.audit_file_dest='/u01/app/oracle/admin/orcl/adump'

*.background_dump_dest='/u01/app/oracle/admin/orcl/bdump'

*.compatible='10.2.0.1.0'

*.control_files='+DISK1/orcl/controlfile/backup.256.691078161','+DISK1/orcl/controlfile/backup.257.691078163'#Restore Controlfile

*.core_dump_dest='/u01/app/oracle/admin/orcl/cdump'

*.db_block_size=8192

*.db_create_file_dest='+DISK1'

*.db_domain='oracle.com'

*.db_file_multiblock_read_count=16

*.db_name='orcl'

*.db_recovery_file_dest_size=2147483648

*.db_recovery_file_dest='+DISK1'

*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'

*.job_queue_processes=10

*.open_cursors=300

*.pga_aggregate_target=16777216

*.processes=150

*.remote_login_passwordfile='EXCLUSIVE'

*.sga_target=283115520

*.undo_management='AUTO'

*.undo_tablespace='UNDOTBS1'

*.user_dump_dest='/u01/app/oracle/admin/orcl/udump'

=> modified 파라미터

▣ 파라미터 값 바꾸는 방법

Static

Dynamic

Script

spfile : 현재 셋팅은 바꾸지 않고 파라미터 파일만 바꿈(both로 하면 에러)

o7은 static이기 때문에 Value 수정 불가(문제점)

spfile Tab으로 이동해서 변경한다.

예) db_recovery_file_dest_size (FRA의 크기)

EM에서 Parameter 값을 수정 할 수 있다.

=> (값을 수정한 후 위의 Apply 체크를 해줘야 함,sql확인 가능)

=> 수정 후 Tab에서 spfile로 이동뒤 똑같이 값을 변경해야 함

▣ 백업한 parameter 파일로 복구하기

▶ 통상적인 parameter 파일 백업

S SYS> !date

2009. 05. 19. (화) 15:39:19 KST

S SYS> create pfile='/home/oracle/_setting/initorcl.ora.090519_1' from spfile;

File created.

▶ 파라미터 파일 고장내기

[oracle@orcl ~]$ rm /u01/app/oracle/product/10.2.0/db_1/dbs/spfileorcl.ora

[oracle@orcl ~]$ exit

exit

S SYS> shutdown abort;

ORACLE instance shut down.

S SYS> startup

ORA-01078: failure in processing system parameters

LRM-00109: could not open parameter file '/u01/app/oracle/product/10.2.0/db_1/dbs/initorcl.ora'

▶ 파라미터 파일 복구 - pfile로 시작만 하기

S SYS> startup pfile='/home/oracle/_setting/initorcl.ora.090519_1' nomount;

ORACLE instance started.

Total System Global Area 285212672 bytes

Fixed Size 1218968 bytes

Variable Size 121636456 bytes

Database Buffers 155189248 bytes

Redo Buffers 7168000 bytes

S SYS> @s

STATUS

------------

STARTED

S SYS> create spfile from pfile='/home/oracle/_setting/initorcl.ora.090519_1'; => spfile 생성

File created.

S SYS> shutdown => instance shutdown (nomount -> shutdown)

ORA-01507: database not mounted

ORACLE instance shut down.

S SYS>

S SYS> startup => instance 재부팅 (shutdown -> open)

ORACLE instance started.

Total System Global Area 285212672 bytes

Fixed Size 1218968 bytes

Variable Size 121636456 bytes

Database Buffers 155189248 bytes

Redo Buffers 7168000 bytes

Database mounted.

Database opened.

▣ Alert Log 를 Script로 보기

alertlog 위치 찾기
S SYS> @p
Enter value for key: background
NAME VALUE
-------------------- ------------------------------------------------------------
background_core_dump partial
background_dump_dest /u01/app/oracle/admin/orcl/bdump
alertlog 파일 열기
[oracle@orcl ~]$ cd /u01/app/oracle/admin/orcl/bdump/
[oracle@orcl bdump]$ vi alert_orcl.log
내용보기
/ALTER SYSTEM SET 파일에서 검색