공부흔적

1. conventional path로 full export 받기(기본모드)

SYS>!

[oracle@localhost ~]$ mkdir /data/exp/

. exporting posttables actions

. exporting triggers

. exporting materialized views

. exporting snapshot logs

. exporting job queues

. exporting refresh groups and children

. exporting dimensions

. exporting post-schema procedural objects and actions

. exporting user history table

. exporting default and system auditing options

. exporting statistics

Export terminated successfully with warnings.

real    4m13.880s

user    0m22.326s

sys     0m6.277s

2. direct path로 full export  받기

마지막에 옵션 하나만 더 추가함

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

. exporting synonyms

. exporting views

. exporting referential integrity constraints

. exporting stored procedures

. exporting operators

. exporting indextypes

. exporting bitmap, functional and extensible indexes

. exporting posttables actions

. exporting triggers

. exporting materialized views

. exporting snapshot logs

. exporting job queues

. exporting refresh groups and children

. exporting dimensions

. exporting post-schema procedural objects and actions

. exporting user history table

. exporting default and system auditing options

. exporting statistics

Export terminated successfully with warnings.

real    3m11.137s

user    0m20.209s

sys     0m5.754s

약 3분 11초 걸림

3. export를 저장하는 파일을 분할해서 받기

큰 파일 하나를 여러 개의 작은 파일로 분할해서 백업받음

[oracle@localhost ~]$ time exp system/oracle full=y file=/data/exp/full03_1.dmp,\

/data/exp/full03_2.dmp,\

/data/exp/full03_3.dmp\

filesize=100M;

실행할 때 파일명을 고치지 않고 그대로 실행을 다시 하면 원본 파일을 그대로 물어보지 않고, 덮어써 버린다.

주의하자. 

. exporting posttables actions

. exporting triggers

. exporting materialized views

. exporting snapshot logs

. exporting job queues

. exporting refresh groups and children

. exporting dimensions

. exporting post-schema procedural objects and actions

. exporting user history table

. exporting default and system auditing options

. exporting statistics

Export terminated successfully with warnings.

real    2m45.807s

user    0m21.065s

sys     0m5.704s

위와 같이 파일 이름 입력해줌

한 세번 정도 떠서 각각 /data/exp/full04_5.dmp, /data/exp/full04_6.dmp ...이런식으로 계속 멈추면 원하는 파일명을 입력하면 됨

. exporting post-schema procedural objects and actions

. exporting user history table

. exporting default and system auditing options

. exporting statistics

Export terminated successfully with warnings.

real    7m11.178s

user    0m20.848s

sys     0m5.364s

4. 특정 tablespace 만 export 하기

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

...생략

. . exporting table                TEST_NOVALIDATE          3 rows exported

. . exporting table                  TEST_VALIDATE          3 rows exported

. . exporting table                          TT111          1 rows exported

. . exporting table                 CATEGORIES_TAB         22 rows exported

. . exporting table     PRODUCT_REF_LIST_NESTEDTAB        288 rows exported

. . exporting table SUBCATEGORY_REF_LIST_NESTEDTAB         21 rows exported

EXP-00079: Data in table "PURCHASEORDER" is protected. Conventional path may only be exporting partial table.

. . exporting table                  PURCHASEORDER        132 rows exported

. exporting referential integrity constraints

. exporting triggers

Export terminated successfully with warnings.

real    0m18.972s

user    0m2.302s

sys     0m0.469s

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

Export: Release 11.2.0.2.0 - Production on Fri Nov 7 01:56:55 2014

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 specified tables via Conventional Path ...

. . exporting table                            EMP         14 rows exported

. . exporting table                           DEPT          4 rows exported

Export terminated successfully without warnings.

현재 경로에 만들어진 것 확인

[oracle@localhost ~]$ ls

!           ar.sh      Desktop       hot_back.sql  redo.sql

afiedt.buf  cold_back  emp_dept.dmp  ll.sql        shut

a.out       database   expdat.dmp    login.sql     test_data.sql

arch.sql    dd.sql     hot_back      log.sql

6. 여러 사용자를 동시에 exp 하기

[oracle@localhost ~]$ time exp system/oracle file=/data/exp/scott_hr.emp owner=scott,hr;

... 생략

. exporting dimensions

. exporting post-schema procedural objects and actions

. exporting statistics

Export terminated successfully without warnings.

real    0m4.183s

user    0m0.120s

sys     0m0.023s

7. evaluation Buffer값이 export에 주는 영향 테스트 하기

실습을 위해 테스트용 테이블 생성

SQL> create table scott.test01

  2  (no number,

  3  name varchar2(50),

  4  address varchar2(50))

  5  tablespace users;

Table created.

대량의 데이터를 users tablespace에 입력하기 때문에 용량이 부족할 수 있으므로

용량을 충분히 늘려줌

SQL> @/home/oracle/dd.sql

TABLESPACE    MB FILE_NAME

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

EXAMPLE      346 /data/temp7/example01.dbf

USERS          8 /data/temp7/users01.dbf

UNDOTBS1      90 /data/temp7/undotbs01.dbf

SYSAUX       560 /data/temp7/sysaux01.dbf

SYSTEM       710 /data/temp7/system01.dbf

SQL> alter database datafile '/data/temp7/users01.dbf' autoextend on;

Database altered.

SQL> alter database datafile '/data/temp7/undotbs01.dbf' autoextend on;

Database altered.

SQL>begin

for i in 1..5000000 loop

insert into scott.test01

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

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

end loop;

commit;

end;

/

하지만 시간이 한 20~30분 잡아야 함 ㅠ 

생성 후 해당테이블 크기 확인

SYS> select sum(bytes)/1024/1024 MB

from dba_segments

where owner='SCOTT'

and segment_name='TEST01';

테스트 

1 .evaluation buffer 값을 설정하지 않고 export 수행

time exp scott/tiger file=/data/exp/test_01_1.dmp tables=test01;

2. evaluation buffer 값을 1M 설정하고 export 수행

time exp scott/tiger file=/data/exp/test_01_2.dmp tables=test01 buffer=1024000

3. evaluation buffer 값을 10M 설정하고 export 수행

time exp scott/tiger file=/data/exp/test_01_3.dmp tables=test01 buffer=10240000

4. evaluation buffer 값을 20M 설정하고 export 수행

time exp scott/tiger file=/data/exp/test_01_4.dmp tables=test01 buffer=20480000

5. direct path로 export 수행

time exp scott/tiger file=/data/exp/tect_01_5.dmp tables=test01 direct=y

결과 :

evaluation buffer 의 크기를 조절하면서 export를 수행하는 것이 속도에 영향을 미침

무조건 크기가 크다고 속도가 빨라지는 것이 아님

또 temporary tablespace의 크기도 exp/imp에 영향을 많이 줌

작업 전에 미리 큰 용량을 확보한 수 작업을 하자 

8. parameter file을 이용한 export 수행

[oracle@localhost ~]$ vi full.dat

file=/data/exp/full02.dmp

full=y

direct=y

:wq!

Current 아닌 그룹 중 archive 안 된 그룹이 삭제되고 DB open 상태일 경우

삭제된 그룹이 archive되지 않은 경우

서버 운영중에 특정 그룹이 지워진 후 계속 log switch가 발생하면 이와 같은 상황이 됨

DB에 archive hang 현상이 발생하여 중단 후 강제 비정상 종료됨

- 평소에 꾸준히 alert log 모니터링을 잘 해서 이런 상황을 막아야 함

현재상태 확인

SYS>@log

GROUP# MEMBER                                          MB SEQ# STATUS   ARC

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

     1 /data/temp7/redo01_a.log                         5   63 INACTIVE YES

     1 /data/temp7/redo01_b.log                         5   63 INACTIVE YES

     2 /data/temp7/redo02_a.log                        50   64 CURRENT  NO

     2 /data/temp7/redo02_b.log                        50   64 CURRENT  NO

     3 /data/temp7/redo03_a.log                        50    0 UNUSED   YES

     3 /data/temp7/redo03_b.log                        50    0 UNUSED   YES

6 rows selected.

SYS>alter system switch logfile;

System altered.

SYS>alter system switch logfile;

System altered.

SYS>@log

GROUP# MEMBER                                          MB SEQ# STATUS   ARC

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

     1 /data/temp7/redo01_a.log                         5   66 CURRENT  NO

     1 /data/temp7/redo01_b.log                         5   66 CURRENT  NO

     2 /data/temp7/redo02_a.log                        50   64 ACTIVE   YES

     2 /data/temp7/redo02_b.log                        50   64 ACTIVE   YES

     3 /data/temp7/redo03_a.log                        50   65 ACTIVE   YES

     3 /data/temp7/redo03_b.log                        50   65 ACTIVE   YES

6 rows selected.

SYS>alter system switch logfile;

System altered.

SYS>@log

GROUP# MEMBER                                          MB SEQ# STATUS   ARC

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

     1 /data/temp7/redo01_a.log                         5   66 INACTIVE YES

     1 /data/temp7/redo01_b.log                         5   66 INACTIVE YES

     2 /data/temp7/redo02_a.log                        50   67 CURRENT  NO

     2 /data/temp7/redo02_b.log                        50   67 CURRENT  NO

     3 /data/temp7/redo03_a.log                        50   65 INACTIVE YES

     3 /data/temp7/redo03_b.log                        50   65 INACTIVE YES

6 rows selected.

SYS>!rm -rf /data/temp7/redo03*

SYS>!ls /data/temp7/redo03*

ls: /data/temp7/redo03*: #׷# #####̳# ###丮## ###

SYS>!ls /data/temp7

control01.ctl  example01.dbf  redo02_a.log  system01.dbf  undotbs01.dbf

control02.ctl  redo01_a.log   redo02_b.log  temp01.dbf    users01.dbf

control03.ctl  redo01_b.log   sysaux01.dbf  temp02.dbf

alter system switch logfile

*

ERROR at line 1:

ORA-01013: user requested cancel of current operation

SYS>@log

GROUP# MEMBER                                          MB SEQ# STATUS   ARC

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

     1 /data/temp7/redo01_a.log                         5   69 INACTIVE  NO

     1 /data/temp7/redo01_b.log                         5   69 INACTIVE  NO

     2 /data/temp7/redo02_a.log                        50   70 CURRENT NO

     2 /data/temp7/redo02_b.log                        50   70 CURRENT NO

     3 /data/temp7/redo03_a.log                        50   68 INACTIVE  NO

     3 /data/temp7/redo03_b.log                        50   68 INACTIVE  NO

6 rows selected.

장애해결

[oracle@localhost ~]$ exit

exit

SYS>alter database clear unarchived logfile group 3;

Database altered.

SYS>@log

GROUP# MEMBER                                          MB SEQ# STATUS   ARC

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

     1 /data/temp7/redo01_a.log                         5   69 INACTIVE YES

     1 /data/temp7/redo01_b.log                         5   69 INACTIVE YES

     2 /data/temp7/redo02_a.log                        50   70 CURRENT  NO

     2 /data/temp7/redo02_b.log                        50   70 CURRENT  NO

     3 /data/temp7/redo03_a.log                        50    0 UNUSED   YES

     3 /data/temp7/redo03_b.log                        50    0 UNUSED   YES

6 rows selected.

11g 이상 해결법

아래 상황은

10g 이하 해결법 (추가 할 것)

archive 완료된 그룹이 지워진 후 DB Close 상태

current 아닌 active / inactive 하나의 그룹이 삭제된 후 DB가 종료된 경우

이 상황에서는 DB가 오픈되지 않음

살펴봐야 할 것

archive 완료 여부 :  삭제된 그룹이 삭제 전에 다른 곳으로 저장(archive) 해두었다면 별다른 문제 생기지 않음

장애이유

오라클이 시작할 때 control file을 읽음->지정된 redo log file을 읽으려고 시도 : 현재 os에는 리두로그 파일이 없지만 control file 내용 내에는 redo log file의 경로가 있기때문에 오류가 남

해결책

실제 파일이 없으므로 control file에수 해당그룹의 명단은 삭제

 SYS>startup

ORACLE instance started.

Total System Global Area  422670336 bytes

Fixed Size                  1344616 bytes

Variable Size             293604248 bytes

Database Buffers          121634816 bytes

Redo Buffers                6086656 bytes

Database mounted.

Database opened.

현재상태 확인

SYS>@log

GROUP# MEMBER                                          MB SEQ# STATUS   ARC

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

     1 /data/temp7/redo01_a.log                         5   63 CURRENT  NO

     1 /data/temp7/redo01_b.log                         5   63 CURRENT  NO

     2 /data/temp7/redo02_a.log                         5   62 INACTIVE YES

     2 /data/temp7/redo02_b.log                         5   62 INACTIVE YES

1개의 멤버가 삭제되는 장애 발생

SYS>startup

ORACLE instance started.

Total System Global Area  422670336 bytes

Fixed Size                  1344616 bytes

Variable Size             293604248 bytes

Database Buffers          121634816 bytes

Redo Buffers                6086656 bytes

Database mounted.

Database opened.

SYS>!vi log.sql

SYS>@log

GROUP# MEMBER                                          MB SEQ# STATUS   ARC

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

     1 /data/temp7/redo01_a.log                         5   57 CURRENT  NO

     1 /data/temp7/redo01_b.log                         5   57 CURRENT  NO

     2 /data/temp7/redo02_a.log                         5   56 INACTIVE YES

     2 /data/temp7/redo02_b.log                         5   56 INACTIVE YES

Errors in file /app/oracle/diag/rdbms/testdb/testdb/trace/testdb_arc1_7763.trc:

ORA-00313: open failed for members of log group 1 of thread 1

ORA-00312: online log 1 thread 1: '/data/temp7/redo01_a.log'

ORA-27037: unable to obtain file status

Linux Error: 2: No such file or directory

Additional information: 3

SYS>alter database add logfile member

  2  '/data/temp7/redo01_a.log' to group 1;

Database altered.

SYS>@log

GROUP# MEMBER                                          MB SEQ# STATUS   ARC

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

     1 /data/temp7/redo01_a.log                         5   61 INACTIVE YES

     1 /data/temp7/redo01_b.log                         5   61 INACTIVE YES

     2 /data/temp7/redo02_a.log                         5   62 CURRENT  NO

     2 /data/temp7/redo02_b.log                         5   62 CURRENT  NO

 supplemental 기능의 활성화 여부 확인

SYS>select supplemental_log_data_min from v$database;

비활성화 상태

SUPPLEME

--------

NO

활성화상태로 변경

Database altered.

SYS>select supplemental_log_data_min from v$database;

SUPPLEME

--------

YES

SYS>alter database drop supplemental log data;

Database altered.

SYS>select supplemental_log_data_min from v$database;

SUPPLEME

--------

NO

SYS>select supplemental_log_data_min from v$database;

SUPPLEME

--------

NO

SYS>alter database add supplemental log data;

Database altered.

주의!!! 테스트 환경을 만들때, 오라클 설치 파일경로인 /app/oracle 에 남은 용량이 많이 없어서 위와 같이 

작업을 하게 되면 나중에 리두로그마이너가 많이 생겨서 용량이 부족하다는 에러가 생길수도 있음. 

그래서 나는 아카이브 경로를 용량이 많은 다른 경로로 이동해 주겠다.

pfile을 사용하는 경우

[oracle@localhost ~]$ vi $ORACLE_HOME/dbs/inittestdb.ora

#아카이브 로그 파일 저장경로와 이름 지정

log_archive_dest_1='location=/data/arc1'

log_archive_dest_2='location=/data/arc2'

log_archive_format=%s_%t_%r.arc #아카이브 로그파일 이름지정#

SYS>startup

ORACLE instance started.

Total System Global Area  422670336 bytes

Fixed Size                  1344616 bytes

Variable Size             293604248 bytes

Database Buffers          121634816 bytes

Redo Buffers                6086656 bytes

Database mounted.

Database opened.

SYS>alter system switch logfile;

System altered.

SYS>alter system switch logfile;

System altered.

[oracle@localhost arc1]$ ls

4_1_862809266.arc  5_1_862809266.arc

[oracle@localhost arc2]$ ls

4_1_862809266.arc  5_1_862809266.arc

SYS>@/app/oracle/arch.sql 

[oracle@localhost ~]$ vi ar.sh

for i in $(ls /data/arc1);

 do echo "exec dbms_logmnr.add_logfile('/data/arc1/$i',3);" >> /home/oracle/arch.sql;

done;

wq!

[oracle@localhost ~]$ sh ar.sh

[oracle@localhost ~]$ vi /home/oracle/arch.sql

exec dbms_logmnr.add_logfile('/data/arc1/4_1_862809266.arc',3);

exec dbms_logmnr.add_logfile('/data/arc1/5_1_862809266.arc',3);

로그마이너를 시작해서 log를 분석한 후 결과 조회

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

157

SYS>select supplemental_log_data_min from v$database;

SUPPLEME

--------

YES

SYS>create table scott.test2(no number, name varchar2(10));

Table created.

SYS>insert into scott.test2 values(1,'AAA');

1 row created.

SYS>insert into scott.test2 values(2,'BBB');

1 row created.

SYS>insert into scott.test2 values(3,'CCC');

1 row created.

SYS>commit;

Commit complete.

SYS>select * from scott.test2;

        NO NAME

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

         1 AAA

         2 BBB

         3 CCC

SYS>update scott.test2 set name='DDD';

3 rows updated.

SYS>select * from scott.test2;

        NO NAME

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

         1 DDD

         2 DDD

         3 DDD

SYS>commit;

Commit complete.

SYS>show parameter utl

NAME                                 TYPE        VALUE

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

create_stored_outlines               string

utl_file_dir                         string      /data/logminer

SYS>exec dbms_logmnr_d.build(dictionary_filename=>'/dict2.dat',dictionary_location=>'/data/logminer');

PL/SQL procedure successfully completed.

SYS>exec dbms_logmnr.start_logmnr(dictfilename=>'/data/logminer/dict2.dat');

PL/SQL procedure successfully completed.

SYS>col username for a10

SYS>col operation for a10

SYS>col sql_redo for a70

SYS>set pagesize 50

SYS>alter session set nls_date_format='yyyy-mm-dd:hh24:mi:ss';

Session altered.

SYS>select timestamp, username, operation, sql_redo

  2  from v$logmnr_contents

  3  where seg_name='TEST2';

TIMESTAMP           USERNAME   OPERATION  SQL_REDO

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

2014-11-06:02:19:14 SYS        DDL        create table scott.test2(no number, name varchar2(10));

2014-11-06:02:19:47 SYS        INSERT     insert into "SCOTT"."TEST2"("NO","NAME") values ('1','AAA');

2014-11-06:02:20:08 SYS        INSERT     insert into "SCOTT"."TEST2"("NO","NAME") values ('2','BBB');

2014-11-06:02:21:23 SYS        INSERT     insert into "SCOTT"."TEST2"("NO","NAME") values ('3','CCC');

2014-11-06:02:21:44 SYS        UPDATE     update "SCOTT"."TEST2" set "NAME" = 'DDD' where "NAME" = 'AAA' and ROW

                                          ID = 'AAASm+AAEAAAAMnAAA';

2014-11-06:02:21:44 SYS        UPDATE     update "SCOTT"."TEST2" set "NAME" = 'DDD' where "NAME" = 'BBB' and ROW

                                          ID = 'AAASm+AAEAAAAMnAAB';

2014-11-06:02:21:44 SYS        UPDATE     update "SCOTT"."TEST2" set "NAME" = 'DDD' where "NAME" = 'CCC' and ROW

                                          ID = 'AAASm+AAEAAAAMnAAC';

7 rows selected.

redo log file까지 손상이 있을 경우 resetlogs 모드로 재생성

SYS>alter database backup controlfile to trace as '/app/oracle/back.sql';

Database altered.

SYS>shut immediate

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

정상적인 DB 에서 old control file error 에러 발생시키기

에러의 원인

- 컨트롤 파일 정보가 저장된 시점이 데이터파일의 정보저장 시점보다 예전 내용이기 때문

문제 해결 방법

1. 백업 데이터파일과 아카이브 리두로그파일을 사용 using backup controlfile 옵션으로 recovery 하는 방법

2. 1의 방법이 되지 않으면 컨트롤파일을 재생성 해야 함

     - 현재 리두로그 파일에 손상이 없을 경우 : noresetlogs 모드로 재생성

     - 현재 리두고르 파일에 손상이 있을 경우 : resetlogs 모드로 재생성

나의 경우는 리두로그 파일에 손상이 없을 경우임

nomount 상태에서 컨트롤 파일을 재생성하는 명령어를 입력하면 됨, 

but 그 명령어가 너무 길어서 외우기가 힘듬

현재 운영 중인 서버에서 재생성용 스크립트를 만들어 생성하는 방법으로 실습할 예정임 

컨트롤 파일을 재생성하는 스크립트는 현재 올드 컨트롤 파일로부터 trace 해서 만들게 됨

이 방법은 현재 컨트롤 파일의 정보를 사용해서 재생성하는 것이므로 mount상태에서 수행해야 함

현재 상태 확인

모든 컨트롤 파일끼리는 정보가 동일해야하는데 

컨트롤 파일끼리 정보가 달라서 생기는 version error임

에러 뜨는 이유 컨트롤 파일의 경로와 실제 컨트롤 파일이 서로 맞지 않을 때,

경로는 있지만, 파일이 없는 경우 위와 같은 에러가 생김

그러면 경로를 수정해주거나, 파일을 추가 혹은 삭제해서 같도록 만들어주면 startup 이 정상적으로 됨

나와 같은 경우

실제 경로

파일명 : $ORACLE_HOME/dbs/inittestdb.ora (파라미터파일, pfile) 

- 내가 한 실수

시점복구를 할 때, 

오라클의 날짜와 시간을 보고 

시점복구를 해야한다. 

윈도우의 시간과 오라클의 시간이 다르기 때문에

특히 주의를 해야한다. !!

윈도우 시간으로 복구하려고 했다가

시점 복구할 때 그래서 삽질함 

- 시점복구 명령어

recover until time '2014-11-01:07:17:55';

- 현재 시간보는 쿼리문

SQL> select to_char(sysdate,'yyyy-mm-dd:hh24:mi:ss') from dual;

TO_CHAR(SYSDATE,'YY

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

2014-11-01:07:17:56

- alert_testdb.log의 시간 형태

/app/oracle/diag/rdbms/testdb/testdb/trace/alert_testdb.log 

Sat Nov 01 06:53:25 2014

SMCO started with pid=28, OS id=22285

Sat Nov 01 06:55:24 2014

drop tablespace  ts_new including contents and datafiles