오라클유치원

환경설정

SQL> create or replace directory datapump as '/data/dp';

SQL> grant read,write on directory datapump to scott;

SQL> grant create any directory to scott;

※ exp 받은 dmp 파일은 나중에 imp 를 하기 위해서 바이너리 파일로 변환되어 만들어지고 내부 순서를 보면 Create table -> insert -> Create index -> add constraint 순이다.

※ vi dmp파일

^C^CNEXPORT:V11.02.00 -> export 버전

DSYSTEM -> exp 수행한 계정

RENTIRE -> 전부 다 full 로 exp 받았다는 것.

8192 -> 블락 크기

※ TABLESPACES 옵션을 주었다 하더라도 dmp 파일에서 확인되는 것과 같이 imp 할 때 Tablespace를 생성하고 Table을 만드는 것이 아니라는 뜻이다. imp하길 원하는 서버에 Tablespace를 먼저 만들어 두고 imp 를 해야 한다는 뜻.

※ 서버1 에서 scott 사용자의 default tablespace가 users 이고 서버2 사용자는 example 인 경우 서버1에서 scott사용자의 tbl600 테이블을 exp받아 서버2 로 imp 하면 서버1 scott 사용자의 default tablespace인 users로 들어가게된다.

※ 서버2에 서버1에 있던 테이블스페이스가 없으면 해당 스키마의 default tablespace로 입력된다.

따라서 마이그레이션 작업 순서 중 가장 기본적인 것은 AS-IS 서버와 TO-BE 서버의 사용자 계정과 각 사용자의 default tablespace 를 동일하게 설정하고 Privilege 와 role 을 동일하게 설정한 후에 schema 별로 exp 를 수행해서 imp를 수행하는 것이다.

중요한 특징 중 한가지는 Export 와 import 유틸리티는 DB가 OPEN 된 상태에서만 사용 가능하다는 것. 또 OS 기종이나 오라클 버전에 달라도 데이터를 이동시킬 수 있다. 데이터가 많을 경우 물리적 백업보다 시간이 많이 걸린다는 단점이 있다.

Export 유틸리티로 데이터와 데이터베이스의 정의를 복사하고 이 내용을 오라클 내부 포맷의 바이너리 파일로 변환해 OS에 저장.

1. Conventional Path Export

Data -> Database buffer cache -> Evaluation Buffer -> Dmp 파일로 변환(binary)

※ 무조건 시작시점의 데이터가 반영되고 중간에 다른 사용자에 의해 변경된 데이터는 반영되지 않는다. 이유는 다른 사용자들은 DB버퍼캐시에서 작업을 하고 exp 작업은 evaluation buffer에서 작업하기 때문이다. 

2. Direct Path Export

Data -> Database buffer cache -> Dmp 파일로 변환

※ 이 작업 중에는 다른 사용자들이 못쓰게 Holding 된다. 잘못쓰면 역적됨. 조심.

Import 유틸리티가 저장된 바이너리 파일을 읽어 데이터베이스에 저장. 아래는 import 순서.

1. create table ~

2. insert into ~

3. create index ~ 

4. constraint ~  ※ 중간에 에러가 나면 이후 모든 작업이 중단된다.

EXPORT 수행하기 

들어가기 앞서..

Data Dictionary Views. 
---------------------- 
EXPORT 또는 IMPORT utility 를 사용하기 전에, 반드시 이 
utilities 에 의해 서용될 Data Dictionary가 준비되어야만 
합니다. 다음 script를 실행하면 됩니다. 

UNIX : SQL> @$ORACLE_HOME/rdbms/admin/catexp.sql 

Windows: SQL> @%ORACLE_HOME%\rdbms\admin\catexp.sql 

이 CATEXP.SQL script는 반드시 SYS user로 실행되어야만 합니다. 
이 script는 CATALOG.SQL script에 의해 자동으로 호출됩니다. 
CATEXP.SQL 와 CATALOG.SQL script는 하나의 database에 단지 
한번만 실행되면 됩니다. 정상적인 상태라면 export나 import 
전에 이 script를 실행해 줄 필요는 결코 없습니다. 

CATEXP.SQL는 다음과 같은 일을 해서 database를 export와 import 
에 대해 준비시켜 줍니다. 

- data dictionary에 필요한 export와 import views들을 생성합니다. 
- EXP_FULL_DATABASE role 과 IMP_FULL_DATABASE role을 생성합니다. 
- EXP_FULL_DATABASE role 과 IMP_FULL_DATABASE role에 
필요한 모든 privileges를 할당합니다. 
- EXP_FULL_DATABASE 과 IMP_FULL_DATABASE 를 DBA role에 할당합니다. 
- sys.props$ table을 export 와 import views의 version으로 update 
합니다. 

출처 : http://www.syhds.com/it_kor/support/date.php?ptype=view&code=pds&idx=32

※ export / import 는 temporary tablespace 공간을 사용하기때문에 용량이 없다는 에러가 뜨면 바로 아래와 같이 다른 temporary tablespace 생성 후 디폴트로 설정해준다.

sql> create temporary tablespace temp2 tempfile '/app/oracle/temp2.dbf' size 20M autoextend on;

sql> alter database default temporary tablespace temp2;

※ SYS 계정으로 작업한건 exp 되지 않는다. 무조건 SYSTEM 계정으로!!

[oracle@server128 ~]$ time exp system/oracle full=y file=/data/exp/full01.dmp log=/data/exp/full_log01.log

[oracle@server128 ~]$ time exp system/oracle full=y file=/data/exp/full02.dmp log=/data/exp/full_log02.log direct=y

Export: Release 11.2.0.2.0 - Production on Fri Apr 17 18:05:26 2015

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production

With the Partitioning option

Export done in KO16MSWIN949 character set and AL16UTF16 NCHAR character set

About to export the entire database ...

. exporting tablespace definitions

[oracle@server128 ~]$ time exp system/oracle full=y file=/data/exp/full04_1.dmp , /data/exp/full04_2.dmp , /data/exp/full04_3.dmp filesize=10M

Export: Release 11.2.0.2.0 - Production on Fri Apr 17 19:05:50 2015

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production

With the Partitioning option

Export done in KO16MSWIN949 character set and AL16UTF16 NCHAR character set

About to export the entire database ...

. exporting tablespace definitions

Export file: expdat.dmp >  /data/exp/full04_4.dmp

continuing export into file /data/exp/full04_4.dmp 

(이하생략)

[oracle@server128 ~]$ time exp system/oracle file=/data/exp/ex_users.dmp tablespaces=example,users

[oracle@server128 ~]$ exp scott/tiger tables=emp,dept file=emp_dept.dmp

[oracle@server128 ~]$ time exp system/oracle file=/data/exp/scott_hr.dmp owner=scott,hr

SYS>alter database datafile '/data/yeje/users01.dbf' autoextend on;

Database altered.

SYS>alter database datafile '/data/yeje/undotbs01.dbf' autoextend on;

Database altered.

SYS> begin

  2  for i in 1..5000000 loop

  3  insert into scott.test01

  4  values (i, dbms_random.string('A',49) ,

  5             dbms_random.string('Q',49) ) ;

  6  end loop;

  7  commit;

  8  end;

  9  /

[oracle@server128 ~]$ vi full.dat

file=/data/exp/full02.dmp

full=y

direct=y

[oracle@server128 ~]$ exp system/oracle parfile=full.dat

[oracle@server128 ~]$ exp scott/tiger query=\"where ename like \'F%\'\" tables=emp file=/data/exp/test06.dmp

-> 이름 첫글자가 F인 사람만

[oracle@server128 ~]$ exp scott/tiger query=\"where job=\'CLERK\' and sal \>1000\" file=/data/exp/scott2.dmp tables=emp

-> emp 테이블에서 job 이 CLERK 이고 급여가 1000 이상인 사람만

[oracle@server128 ~]$ vi par2.dat

tables=emp query="where job='CLERK' and sal>1000"

file=/data/exp/scott3.dmp

[oracle@localhost ~]$ exp scott/tiger parfile=par2.dat

[oracle@server128 ~]$ vi exp_script.sh

export LANG=C

export ORACLE_BASE=/app/oracle

export ORACLE_HOME=$ORACLE_BASE/product/11g

export PATH=$ORACLE_HOME/bin:$PATH

export ORACLE_SID=testdb

sqlplus /nolog<<EOF3

conn / as sysdba

set head off

set time off

set timing off

set feedback off

set echo off

set line 200

col name for a100

spool /app/oracle/exp.tmp

select 'mkdir -p /data/backup/exp/'||to_char(sysdate,'yyyy-mm-dd-hh24-mi') from dual;

select distinct 'exp system/oracle'||' owner='||lower(owner)||' file=/data/backup/exp/'||to_char(sysdate,'yyyy-mm-dd-hh24-mi')||'/'||lower(owner)||'.dmp'||' filesize=100m direct=y' from dba_tables where owner not in ('SYS','DBSNMP','WMSYS','IX','SYSTEM','OE','PM','EXFSYS','CTXSYS','OLAPSYS','MDSYS','SYSMAN','LOADER','XDB','ORDSYS','OUTLN','TSMSYS','DMSYS');

spool off

!cat /app/oracle/exp.tmp | egrep -v SQL | egrep -v SYS > /app/oracle/exp.sh

!sh /app/oracle/exp.sh

exit

EOF3

Import 수행하기

imp 실행순서

1) create table ~

2) insert into ~

3) create index ~

4) constraint 생성 ※ 중간과정에서 하나라도 에러가 나면 모든 작업이 캔슬됨.

Import 작업은 DDL과 DML을 수행하는 것이므로 Redo log 와 Undo segment 를 사용한다.

따라서 대량의 데이터를 import 할 때는 반드시 충분한 용량의 Undo tablespace 가 필요하다.

용량이 부족하면 마지막에 에러나면서 전부 rollback 될 수도 있음.

이런 위험을 줄이기 위해서는 import 할 때 commit=y 로 변경하고 수행하면 array 단위로 commit 을 수행하기 때문에 전체가 rollbak 되는 현상은 막을 수 있다.

[oracle@server128 ~]$ imp system/oracle file=/data/exp/full11.dmp ignore=y full=y

[oracle@server128 ~]$ imp system/oracle file=/data/exp/full11.dmp fromuser=scott tables=test01 ignore=y

SCOTT> create table test02 (no number , addr varchar2(10));

SCOTT> begin

for i in 1..1000 loop

insert into test02 values(i,dbms_random.string('A',10));

end loop;

commit;

end;

/

SCOTT> select count(*) from test02;

  COUNT(*)

----------

      1000

[oracle@server128 ~]$ imp scott/tiger file=/data/exp/test02.dmp show=y log=test02.log

[oracle@server128 ~]$ vi test02.log

기본적인 대처 순서

1. Current 파일 / Current 아닌 파일 지웠는지 확인

2. 아카이브 파일 / 노 아카이브 파일을 지웠는지 확인

얼럿로그 확인

-> 지워진 목록 drop

SYS> alter database drop logfile member '/data/backup/close/redo01_a.log';

-> 지워진 멤버 생성

SYS> alter database add logfile member '/data/backup/close/redo01_a.log' to group 1;

끝.

얼럿로그 확인

-> 지워진 그룹 drop

SYS> alter database drop logfile group 1;

-> DB 오픈

SYS> alter database open;

-> 지워진 그룹과 멤버 생성

SYS> alter database add logfile group 1('/data/backup/close/redo01_a.log','/data/backup/close/redo01_b.log') size 50M;

끝.

얼럿로그 확인

-> 장애가 발생하는 그룹 확인

-> Redo Log 초기화 및 신규생성

SYS> alter database clear unarchived logfile group 1;

끝.

※ 서버 운영 중 특정 그룹이 지워진 후 계속 log switch 가 발생하게 되면 Archive Hang 현상이 발생하여 중단되다가 강제로 비정상 종료됨. 그리고 오라클에서는 Archiving 을 순서대로 하기 때문에 한개의 그룹에서 아카이빙이 완료되지 않으면 그 다음 그룹의 로그파일 모두 아카이빙 되지 않는다. 계속 없는 그룹에 아카이빙을 시도하다보니 Hang 이 발생하게 되는 것이다. SEQ# 가장 작은 번호부터 정체가 시작.

재시작시키면 에러 발생하면서 중단됨. ORA-03113 : end-of-file on communication channel

-> 얼럿로그 확인

-> 지워진 그룹 drop

SYS> alter database drop logfile group 1;

Archive 안 된 그룹은 삭제를 할 수 없다고 에러 메시지 나옴.

-> 초기화 및 재생성

SYS> alter database clear unarchived logfile group 1;

끝.

얼럿로그 메시지 확인해본다.

-> 삭제된 리두 로그 그룹 멤버를 찾을 수 없다는 에러 확인.

SYS> alter database clear unarchived logfile group 1;

끝.

Current 그룹이 삭제된 후 DB가 종료되었다면 정상 종료인지 비정상 종료인지에 따라 향후 재시작 시 심각한 문제가 될 수 있다. 먼저 shutdown immediate; 로 종료 한 경우.

-> 얼럿 로그 확인

->삭제된 그룹의 멤버를 찾을 수 없다는 에러 확인

-> 불완전 복구 후 리셋로그 옵션으로 오픈

SYS> recover database until cancel;

Media recovery complete.

SYS> alter database open resetlogs;

Database altered.

끝.

※ 원리를 아는 것이 매우 중요하다. 

 현재 상황은 Current 상태의 Redo log group 이 삭제된 후 shutdown immediate; 로 종료된 것이다. shutdown immediate; 란 DB 버퍼캐시에 있는 내용 중 commit 완료 된 내용은 데이터 파일로 전부 저장하고 commit 안 된 내용은 rollback 한 후 종료하는 것이다. 

 테스트용 테이블에 인서트 후 커밋 했기 때문에 shutdown immediate 한 순간 해당 내용은 데이터 파일에 저장 완료되었다. 그래서 사실 복구할 필요가 없다. 그런데 Current 상태의 Redo log file 이 없기때문에 Open 되지 않는다.

 이럴 때 Control File 에는 명단이 있으나 실제 파일이 없는 Redo log 를 재생성시키는 resetlogs 옵션을 사용하는 것이다. 문제는 resetlogs 옵션은 불완전 복구를 수행했을 경우만 쓸 수 있다는 점.

SYS>alter system set log_archive_dest_1='location=/data/arc1' scope=spfile;

System altered.

SYS>alter system set log_archive_dest_1='location=/data/arc2' scope=spfile;

System altered.

SYS>alter system set log_archive_format="%s_%t_%r.arc" scope=spfile;

System altered.

ORACLE instance started.

Total System Global Area  619360256 bytes

Fixed Size                  1345936 bytes

Variable Size             394266224 bytes

Database Buffers          218103808 bytes

Redo Buffers                5644288 bytes

Database mounted.

SYS>archive log list;

Database log mode              Archive Mode (아카이브 모드로 변경 완료)

Automatic archival             Enabled

Archive destination            /data/arc2

Oldest online log sequence     21

Next log sequence to archive   23

Current log sequence           23

SYS>alter database open;

Database altered.

SYS>select name from v$controlfile;

NAME

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

/home/oracle/disk3/ctrl01.ctl

/home/oracle/disk4/ctrl02.ctl

/home/oracle/disk5/ctrl03.ctl

SYS>select name from v$datafile;

NAME

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

/app/oracle/disk4/system01.dbf

/app/oracle/disk4/sysaux01.dbf

/app/oracle/disk3/undo01.dbf

/app/oracle/disk5/users01.dbf

/app/oracle/disk5/example01.dbf

/app/oracle/oradata/testdb/undo01.dbf

/app/oracle/oradata/testdb/hakjum01.dbf

7 rows selected.

SYS>select member from v$logfile;

MEMBER

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

/app/oracle/disk1/redo03_a.log

/app/oracle/disk1/redo02_a.log

/app/oracle/disk1/redo01_a.log

/app/oracle/disk2/redo01_b.log

/app/oracle/disk2/redo02_b.log

/app/oracle/disk2/redo03_b.log

6 rows selected.

[oracle@server128 backup]$  cp /home/oracle/disk3/ctrl01.ctl /data/backup/close/

[oracle@server128 backup]$  cp /home/oracle/disk4/ctrl02.ctl /data/backup/close/

[oracle@server128 backup]$  cp /home/oracle/disk5/ctrl03.ctl /data/backup/close/

[oracle@server128 backup]$

[oracle@server128 backup]$  cp /app/oracle/disk4/system01.dbf /data/backup/close/

[oracle@server128 backup]$  cp /app/oracle/disk4/sysaux01.dbf /data/backup/close/

[oracle@server128 backup]$ cp /app/oracle/disk3/undo01.dbf /data/backup/close/

[oracle@server128 backup]$ cp /app/oracle/disk5/*.dbf /data/backup/close/

[oracle@server128 backup]$ cp /app/oracle/oradata/testdb/undo01.dbf /data/backup/close/

[oracle@server128 backup]$ cp /app/oracle/oradata/testdb/hakjum01.dbf /data/backup/close/

[oracle@server128 backup]$

[oracle@server128 backup]$ cp /app/oracle/disk1/*.log /data/backup/close/

[oracle@server128 backup]$ cp /app/oracle/disk2/*.log /data/backup/close/

컨트롤파일 장애유형 3가지

1. 파라미터와 실제 경로가 다른경우

-> 안맞는 부분을 서로 맞춰주면 끝

2. Controlfile 끼리 내용이 다른경우 (Version 에러)

-> 높은 버전의 컨트롤파일이 최신 파일이므로 복사해서 해결

3. Old Controlfile

-> 컨트롤파일 재생성 후 resetlogs / noresetlogs 모드로 오픈

현재 경로 확인 후 경로 중 하나를 지워서 에러를 발생시키기.

SYS>select name from v$controlfile;

NAME

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

/home/oracle/disk3/ctrl01.ctl

/home/oracle/disk4/ctrl02.ctl

/home/oracle/disk5/ctrl03.ctl

야호 에러가 발생했다.

얼럿 로그를 살펴보자.

[oracle@server128 trace]$ tail -f /app/oracle/diag/rdbms/testdb/testdb/trace/alert_testdb.log

Sat Apr 11 19:34:20 2015

ALTER DATABASE   MOUNT

ORA-00210: cannot open the specified control file

ORA-00202: control file: '/home/hahaha/hahahaha/'

ORA-27037: unable to obtain file status

Linux Error: 2: No such file or directory

Additional information: 3

ORA-205 signalled during: ALTER DATABASE   MOUNT...

Sat Apr 11 19:34:21 2015

Checker run found 1 new persistent data failures

Old Controlfile 에러 만들기.

백업받아놓은 예전 컨트롤파일을 복사한다.

[oracle@server128 close]$ cp /data/backup/close/ctrl01.ctl /home/oracle/disk3/ctrl01.ctl

[oracle@server128 close]$ cp /data/backup/close/ctrl01.ctl /home/oracle/disk4/ctrl02.ctl

[oracle@server128 close]$ cp /data/backup/close/ctrl01.ctl /home/oracle/disk5/ctrl03.ctl

SYS>startup

ORACLE instance started.

Total System Global Area  619360256 bytes

Fixed Size                  1345936 bytes

Variable Size             394266224 bytes

Database Buffers          218103808 bytes

Redo Buffers                5644288 bytes

Database mounted.

ORA-01122: database file 1 failed verification check

ORA-01110: data file 1: '/app/oracle/disk4/system01.dbf'

ORA-01207: file is more recent than control file - old control file

select name , status from v$datafile;

@log

select name from v$controlfile;

!ls $ORACLE_HOME/dbs/

!ls $ORACLE_HOME/network/admin

shutdown immediate;

cp /app/oracle/disk4/system01.dbf /data/backup/close/

cp /app/oracle/disk4/sysaux01.dbf /data/backup/close/ 

cp /app/oracle/disk3/undo01.dbf /data/backup/close/ 

cp /app/oracle/disk5/users01.dbf /data/backup/close/ 

cp /app/oracle/disk5/example01.dbf /data/backup/close/ 

cp /app/oracle/oradata/testdb/undo01.dbf /data/backup/close/ 

cp /app/oracle/oradata/testdb/hakjum01.dbf /data/backup/close/

cp /app/oracle/disk1/redo01_a.log /data/backup/close/ 

cp /app/oracle/disk2/redo01_b.log /data/backup/close/  

cp /app/oracle/disk1/redo02_a.log /data/backup/close/  

cp /app/oracle/disk2/redo02_b.log /data/backup/close/  

cp /app/oracle/disk1/redo03_a.log /data/backup/close/  

cp /app/oracle/disk2/redo03_b.log /data/backup/close/ 

cp /home/oracle/disk3/ctrl01.ctl /data/backup/close/

cp /home/oracle/disk4/ctrl02.ctl /data/backup/close/

cp /home/oracle/disk5/ctrl03.ctl /data/backup/close/

cp $ORACLE_HOME/dbs/*.ora /data/backup/close/

cp $ORACLE_HOME/dbs/orapwtestdb /data/backup/close/

cp $ORACLE_HOME/network/admin/* /data/backup/close/