데이터베이스 분류
full DB export 사용
작성자 정보
- 웹관리자 작성
- 작성일
컨텐츠 정보
- 4,860 조회
- 0 추천
-
목록
본문
full DB export 사용
여기서는 full=y를 사용했을 경우만 생각해본다. 이는 DBA권한을 가진 사람이 모든 데이터베이스에 대해 백업을 실시할 때 사용한다. full옵션을 사용할 수 있으려면 정확히 EXP_FULL_DATABASE 권한을 가져야한다. export유틸리티를 사용하기 위해 catexp.sql이라는 유틸리티스크립트를 실행해야 하는데 이것은 catalog.sql이 실행되면서 부수적으로 실행되므로 그리 신경쓸 필요는 없다. export는 논리적인 백업으로 완벽한 복구에 사용되기에는 무리이다. 많은(아주 많은 것 같다.) 사이트들이 백업수단으로 export를 사용하는 것 같다. 사용하기 쉬운 것과 짧은 시간에 할 수 있다는 것이 그 이유가 아닌가 생각된다. 개인적인 생각에는 export의 경우, 각 schema에 대한 백업수단(export시 owner옵션을 사용한 백업)으로서만 사용하는 것이 좋을것 같다. full=y를 사용할 경우 inctype으로 complete, cumulative, incremental 3가지가 사용될 수 있다. complete는 무조건 전체 데이터베이스를 백업. cumulative는 complete이후 변경된 데이블에 대해서만 백업 incremental는 위 2가지 백업뒤 변경된 놈들에 대해 백업 cumulative는 incremental로 생성되는 파일수를 줄여주는 역활을 한다. 즉, 이전에 incremental백업을 2번한 상황에서 comulative백업을 하면 이전의 2개의 incremental백업은 cumulative백업으로 커버가 된다. 즉, 이전 2개의 incremental백업은 복구시 필요치 않고, cumulative백업 하나만 있으면 되는 것이다. 2주일간의 백업정책을 세운다면, 처음 complete백업을 받는다. 일별로 incremental백업을 받는다. 하지만, 수요일, 일요일은 incremental백업대신 cumulative백업을 한다. 2주가 되는 일요일에 complete백업을 한다. 복구의 순서는, 가장 최근의 complete백업한 것을 resotre한다. 가장 최근의 complete백업후에 발생한 cumulative백업들을 restore한다. (cumulative백업이 없다면 incremental백업으로 대체) 위에서 행한 cumulative백업들중 가장 최근의 cumulative백업후의 incremental백업들을 가장 오래된 것부터 차례로 restore한다. 정리하면, complete의 경우 주말에 한번 정도 incremental의 경우 날마다 cumulative의 경우 주중에 한번(수요일정도) 오라클이 complete백업후의 변동된 사항을 어떻게 알수 있을까? 당연히 내부적인 테이블에 이런 정보를 저장하고 있다. 언제 complete, cumulative백업들이 발생했는가등등.. 문서하단에 자세히 설명하겠다. [kang@piruks kang]$ exp userid=system/xxxxxx full=y inctype=complete Export: Release 8.1.6.1.0 - Production on Thu Nov 2 15:35:28 2000 (c) Copyright 1999 Oracle Corporation. All rights reserved. Connected to: Oracle8i Enterprise Edition Release 8.1.6.1.0 - Production With the Partitioning option JServer Release 8.1.6.0.0 - Production Export done in UTF8 character set and KO16KSC5601 NCHAR character set About to export the entire database ... . exporting tablespace definitions . exporting profiles . exporting user definitions . exporting roles . exporting resource costs . exporting rollback segment definitions . exporting database links . exporting sequence numbers . exporting directory aliases . exporting context namespaces . exporting foreign function library names . exporting object type definitions . exporting system procedural objects and actions . exporting pre-schema procedural objects and actions . exporting cluster definitions . about to export SYSTEM's tables via Conventional Path ... . . exporting table DEF$_AQCALL 0 rows exported . . exporting table DEF$_AQERROR 0 rows exported . . exporting table DEF$_CALLDEST 0 rows exported . . exporting table DEF$_DEFAULTDEST 0 rows exported . . exporting table DEF$_DESTINATION 0 rows exported . . exporting table DEF$_ERROR 0 rows exported . . exporting table DEF$_LOB 0 rows exported . . exporting table DEF$_ORIGIN 0 rows exported . . exporting table DEF$_PROPAGATOR 0 rows exported . . exporting table DEF$_PUSHED_TRANSACTIONS 0 rows exported . . exporting table DEF$_TEMP$LOB 0 rows exported . . exporting table HELP 816 rows exported . . exporting table SQLPLUS_PRODUCT_PROFILE 0 rows exported . about to export OUTLN's tables via Conventional Path ... . . exporting table OL$ 0 rows exported . . exporting table OL$HINTS 0 rows exported . about to export DBSNMP's tables via Conventional Path ... . about to export TRACESVR's tables via Conventional Path ... . about to export KANG's tables via Conventional Path ... . exporting referential integrity constraints . exporting synonyms . exporting views . exporting stored procedures . exporting operators . exporting indextypes . exporting bitmap, functional and extensible indexes . exporting posttables actions . exporting triggers . exporting snapshots . 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 without warnings. [kang@piruks kang]$ ls -l total 155 -rw-rw-r-- 1 kang kang 11월 2 15:37 expdat.dmp drwxrwxr-x 4 kang kang 1024 10월 27 13:02 public_html/ drwxrwxr-x 2 kang kang 1024 10월 15 12:54 test/ [kang@piruks kang]$ exp userid=system/xxxxxx full=y inctype=cumulative Export: Release 8.1.6.1.0 - Production on Thu Nov 2 15:39:22 2000 (c) Copyright 1999 Oracle Corporation. All rights reserved. Connected to: Oracle8i Enterprise Edition Release 8.1.6.1.0 - Production With the Partitioning option JServer Release 8.1.6.0.0 - Production Export done in UTF8 character set and KO16KSC5601 NCHAR character set About to export the entire database ... . exporting tablespace definitions . exporting profiles . exporting user definitions . exporting roles . exporting resource costs . exporting rollback segment definitions . exporting database links . exporting sequence numbers . exporting directory aliases . exporting context namespaces . exporting foreign function library names . exporting object type definitions . exporting system procedural objects and actions . exporting pre-schema procedural objects and actions . exporting cluster definitions . about to export SYSTEM's tables via Conventional Path ... . about to export OUTLN's tables via Conventional Path ... . about to export DBSNMP's tables via Conventional Path ... . about to export TRACESVR's tables via Conventional Path ... . about to export KANG's tables via Conventional Path ... . exporting referential integrity constraints . exporting synonyms . exporting views . exporting stored procedures . exporting operators . exporting indextypes . exporting bitmap, functional and extensible indexes . exporting posttables actions . exporting triggers . exporting snapshots . 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 information about dropped objects . exporting statistics Export terminated successfully without warnings. [kang@piruks kang]$ ls -l total 27 -rw-rw-r-- 1 kang kang 11월 2 15:42 expdat.dmp drwxrwxr-x 4 kang kang 1024 10월 27 13:02 public_html/ drwxrwxr-x 2 kang kang 1024 10월 15 12:54 test/ [kang@piruks kang]$ exp userid=system/xxxxxx full=y inctype=incremental Export: Release 8.1.6.1.0 - Production on Thu Nov 2 15:40:29 2000 (c) Copyright 1999 Oracle Corporation. All rights reserved. Connected to: Oracle8i Enterprise Edition Release 8.1.6.1.0 - Production With the Partitioning option JServer Release 8.1.6.0.0 - Production Export done in UTF8 character set and KO16KSC5601 NCHAR character set About to export the entire database ... . exporting tablespace definitions . exporting profiles . exporting user definitions . exporting roles . exporting resource costs . exporting rollback segment definitions . exporting database links . exporting sequence numbers . exporting directory aliases . exporting context namespaces . exporting foreign function library names . exporting object type definitions . exporting system procedural objects and actions . exporting pre-schema procedural objects and actions . exporting cluster definitions . about to export SYSTEM's tables via Conventional Path ... . about to export OUTLN's tables via Conventional Path ... . about to export DBSNMP's tables via Conventional Path ... . about to export TRACESVR's tables via Conventional Path ... . about to export KANG's tables via Conventional Path ... . exporting referential integrity constraints . exporting synonyms . exporting views . exporting stored procedures . exporting operators . exporting indextypes . exporting bitmap, functional and extensible indexes . exporting posttables actions . exporting triggers . exporting snapshots . 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 information about dropped objects . exporting statistics Export terminated successfully without warnings. [kang@piruks kang]$ ls -l total 27 -rw-rw-r-- 1 kang kang 11월 2 15:43 expdat.dmp drwxrwxr-x 4 kang kang 1024 10월 27 13:02 public_html/ drwxrwxr-x 2 kang kang 1024 10월 15 12:54 test/ [kang@piruks kang]$ sqlplus kang/xxxxxx SQL*Plus: Release 8.1.6.0.0 - Production on Thu Nov 2 15:46:34 2000 (c) Copyright 1999 Oracle Corporation. All rights reserved. Connected to: Oracle8i Enterprise Edition Release 8.1.6.1.0 - Production With the Partitioning option JServer Release 8.1.6.0.0 - Production SQL> insert into test values ('하하하', 29); 1 row created. SQL> exit Disconnected from Oracle8i Enterprise Edition Release 8.1.6.1.0 - Production With the Partitioning option JServer Release 8.1.6.0.0 - Production [kang@piruks kang]$ exp userid=system/xxxxxx full=y inctype=incremental Export: Release 8.1.6.1.0 - Production on Thu Nov 2 15:48:11 2000 (c) Copyright 1999 Oracle Corporation. All rights reserved. Connected to: Oracle8i Enterprise Edition Release 8.1.6.1.0 - Production With the Partitioning option JServer Release 8.1.6.0.0 - Production Export done in UTF8 character set and KO16KSC5601 NCHAR character set About to export the entire database ... . exporting tablespace definitions . exporting profiles . exporting user definitions . exporting roles . exporting resource costs . exporting rollback segment definitions . exporting database links . exporting sequence numbers . exporting directory aliases . exporting context namespaces . exporting foreign function library names . exporting object type definitions . exporting system procedural objects and actions . exporting pre-schema procedural objects and actions . exporting cluster definitions . about to export SYSTEM's tables via Conventional Path ... . about to export OUTLN's tables via Conventional Path ... . about to export DBSNMP's tables via Conventional Path ... . about to export TRACESVR's tables via Conventional Path ... . about to export KANG's tables via Conventional Path ... . exporting referential integrity constraints . exporting synonyms . exporting views . exporting stored procedures . exporting operators . exporting indextypes . exporting bitmap, functional and extensible indexes . exporting posttables actions . exporting triggers . exporting snapshots . 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 information about dropped objects . exporting statistics Export terminated successfully without warnings. [kang@piruks kang]$ ls -l total 27 -rw-rw-r-- 1 kang kang 11월 2 15:48 expdat.dmp drwxrwxr-x 4 kang kang 1024 10월 27 13:02 public_html/ drwxrwxr-x 2 kang kang 1024 10월 15 12:54 test/ [kang@piruks kang]$ sqlplus kang/xxxxxx SQL*Plus: Release 8.1.6.0.0 - Production on Thu Nov 2 15:49:16 2000 (c) Copyright 1999 Oracle Corporation. All rights reserved. Connected to: Oracle8i Enterprise Edition Release 8.1.6.1.0 - Production With the Partitioning option JServer Release 8.1.6.0.0 - Production SQL> insert into test values ('&a', 30); Enter value for a: 슈퍼맨 old 1: insert into test values ('&a', 30) new 1: insert into test values ('슈퍼?, 30) 1 row created. SQL> / Enter value for a: 배트맨 old 1: insert into test values ('&a', 30) new 1: insert into test values ('배트?, 30) 1 row created. SQL> / Enter value for a: vicman old 1: insert into test values ('&a', 30) new 1: insert into test values ('vicman', 30) 1 row created. SQL> quit Disconnected from Oracle8i Enterprise Edition Release 8.1.6.1.0 - Production With the Partitioning option JServer Release 8.1.6.0.0 - Production [kang@piruks kang]$ exp userid=system/xxxxxx full=y inctype=incremental Export: Release 8.1.6.1.0 - Production on Thu Nov 2 15:50:24 2000 (c) Copyright 1999 Oracle Corporation. All rights reserved. Connected to: Oracle8i Enterprise Edition Release 8.1.6.1.0 - Production With the Partitioning option JServer Release 8.1.6.0.0 - Production Export done in UTF8 character set and KO16KSC5601 NCHAR character set About to export the entire database ... . exporting tablespace definitions . exporting profiles . exporting user definitions . exporting roles . exporting resource costs . exporting rollback segment definitions . exporting database links . exporting sequence numbers . exporting directory aliases . exporting context namespaces . exporting foreign function library names . exporting object type definitions . exporting system procedural objects and actions . exporting pre-schema procedural objects and actions . exporting cluster definitions . about to export SYSTEM's tables via Conventional Path ... . about to export OUTLN's tables via Conventional Path ... . about to export DBSNMP's tables via Conventional Path ... . about to export TRACESVR's tables via Conventional Path ... . about to export KANG's tables via Conventional Path ... . exporting referential integrity constraints . exporting synonyms . exporting views . exporting stored procedures . exporting operators . exporting indextypes . exporting bitmap, functional and extensible indexes . exporting posttables actions . exporting triggers . exporting snapshots . 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 information about dropped objects . exporting statistics Export terminated successfully without warnings. [kang@piruks kang]$ ls -l total 28 -rw-r--r-- 1 kang kang 5 11월 2 15:51 HantermLog.GDPo9B -rw-rw-r-- 1 kang kang 11월 2 15:50 expdat.dmp drwxrwxr-x 4 kang kang 1024 10월 27 13:02 public_html/ drwxrwxr-x 2 kang kang 1024 10월 15 12:54 test/ [kang@piruks kang]$ exp userid=system/xxxxxx full=y inctype=cumulative Export: Release 8.1.6.1.0 - Production on Thu Nov 2 15:51:48 2000 (c) Copyright 1999 Oracle Corporation. All rights reserved. Connected to: Oracle8i Enterprise Edition Release 8.1.6.1.0 - Production With the Partitioning option JServer Release 8.1.6.0.0 - Production Export done in UTF8 character set and KO16KSC5601 NCHAR character set About to export the entire database ... . exporting tablespace definitions . exporting profiles . exporting user definitions . exporting roles . exporting resource costs . exporting rollback segment definitions . exporting database links . exporting sequence numbers . exporting directory aliases . exporting context namespaces . exporting foreign function library names . exporting object type definitions . exporting system procedural objects and actions . exporting pre-schema procedural objects and actions . exporting cluster definitions . about to export SYSTEM's tables via Conventional Path ... . about to export OUTLN's tables via Conventional Path ... . about to export DBSNMP's tables via Conventional Path ... . about to export TRACESVR's tables via Conventional Path ... . about to export KANG's tables via Conventional Path ... . exporting referential integrity constraints . exporting synonyms . exporting views . exporting stored procedures . exporting operators . exporting indextypes . exporting bitmap, functional and extensible indexes . exporting posttables actions . exporting triggers . exporting snapshots . 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 information about dropped objects . exporting statistics Export terminated successfully without warnings. [kang@piruks kang]$ exp userid=system/xxxxxx full=y inctype=incremental Export: Release 8.1.6.1.0 - Production on Thu Nov 2 15:52:05 2000 (c) Copyright 1999 Oracle Corporation. All rights reserved. Connected to: Oracle8i Enterprise Edition Release 8.1.6.1.0 - Production With the Partitioning option JServer Release 8.1.6.0.0 - Production Export done in UTF8 character set and KO16KSC5601 NCHAR character set About to export the entire database ... . exporting tablespace definitions . exporting profiles . exporting user definitions . exporting roles . exporting resource costs . exporting rollback segment definitions . exporting database links . exporting sequence numbers . exporting directory aliases . exporting context namespaces . exporting foreign function library names . exporting object type definitions . exporting system procedural objects and actions . exporting pre-schema procedural objects and actions . exporting cluster definitions . about to export SYSTEM's tables via Conventional Path ... . about to export OUTLN's tables via Conventional Path ... . about to export DBSNMP's tables via Conventional Path ... . about to export TRACESVR's tables via Conventional Path ... . about to export KANG's tables via Conventional Path ... . exporting referential integrity constraints . exporting synonyms . exporting views . exporting stored procedures . exporting operators . exporting indextypes . exporting bitmap, functional and extensible indexes . exporting posttables actions . exporting triggers . exporting snapshots . 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 information about dropped objects . exporting statistics Export terminated successfully without warnings. [kang@piruks kang]$ exp userid=/xxxxxx inctype=incremental Export: Release 8.1.6.1.0 - Production on Thu Nov 2 15:43:53 2000 (c) Copyright 1999 Oracle Corporation. All rights reserved. Connected to: Oracle8i Enterprise Edition Release 8.1.6.1.0 - Production With the Partitioning option JServer Release 8.1.6.0.0 - Production EXP-00023: must be a DBA to do Full Database export (2)U(sers), or (3)T(ables): (2)U > EXP-00022: must be SYS or SYSTEM to do incremental export EXP-00000: Export terminated unsuccessfully [kang@piruks kang]$ [kang@piruks kang]$ exp userid=system/xxxxxx inctype=complete Export: Release 8.1.6.1.0 - Production on Thu Nov 2 15:45:55 2000 (c) Copyright 1999 Oracle Corporation. All rights reserved. Connected to: Oracle8i Enterprise Edition Release 8.1.6.1.0 - Production With the Partitioning option JServer Release 8.1.6.0.0 - Production EXP-00026: conflicting modes specified EXP-00000: Export terminated unsuccessfully [kang@piruks kang]$ incexp, incfil, incvid sys계정에 보면, incexp, incfil, incvid테이블이 있다. 보통의 owner, table백업으로는 이 테이블들이 채워지지 않는다. cumulative, incremental을 해야 이 놈들의 테이블이 채워진다. 백업을 하면 백업기록을 남겨야 incremental backup을 할 수 있는데 이 놈들이다. incexp: export된 스키마들의 정보를 가짐(보통 무지하게 row가 많다) incfil: incremental backup이 된 export파일들 incvid: 다음 export id를 가짐(즉, 지금 한게 id가 1이라면 다음은 2가 된다.) exp userid=system/xxxx file=fullexp.dmp full=y inctype=complete exp userid=system/xxxx file=cumexp.dmp full=y inctype=cumulative exp userid=sys/xxxx file=incexp.dmp full=y inctype=incremental 이렇게 2번을 하고, DB 에서 조회 SQL> select * from incfil; EXPID EXPTYPE EXPFILE EXPDATE EXPUSER ---------- -------- ------------------------------ --------- ------------------------------ 1 X fullexp.dmp 12-JAN-01 SYSTEM 2 C cumexp.dmp 12-JAN-01 SYSTEM 3 I incexp.dmp 12-JAN-01 SYS SQL> select * from incvid; EXPID ---------- 3 SQL> select * from incexp where rownum<5; OWNER# NAME TYPE# CTIME ITIME EXPID ---------- -------------------- ---------- --------- --------- ---------- 5 DEF$_AQCALL 2 12-JAN-01 12-JAN-01 1 5 DEF$_AQERROR 2 12-JAN-01 12-JAN-01 1 5 DEF$_CALLDEST 2 12-JAN-01 12-JAN-01 1 5 DEF$_DEFAULTDEST 2 12-JAN-01 12-JAN-01 1
"무단배포금지: 클라우드포털(www.linux.co.kr)의 모든 강좌는 저작권에 의해 보호되는 콘텐츠입니다. 무단으로 복제하여 배포하는 행위는 금지되어 있습니다."
관련자료
-
이전
-
다음
댓글 0
등록된 댓글이 없습니다.
