데이터베이스 분류
full export and import실습
작성자 정보
- 웹관리자 작성
- 작성일
컨텐츠 정보
- 4,413 조회
- 0 추천
-
목록
본문
full export and import실습
작성자 : 강명규
OS : LINUX kernel 2.4.18(x86)
ORACLE : 8i(8.1.7) EE
메모리: 160MB
CPU : Celeron (Mendocino) 400
ORACLE_BASE: /u01/app/oracle
ORACLE_HOME: /u01/app/oracle/product/8.17
ORACLE_SID : db
tablespace내 free space의 fragmentation을 제거하기 위한 방법으로
full export -> DB recreate -> full import의 방법이 많이 사용된다.
1. Do a full database export (FULL=Y) to back up the entire database.
2. Shut down Oracle after all users are logged off.
3. Delete the database. See your Oracle operating system-specific documentation for information on how to delete a database.
4. Re-create the database using the CREATE DATABASE command.
5. Do a full database import (FULL=Y) to restore the entire database.
[실습]
1. Do a full database export (FULL=Y) to back up the entire database.
[root@ns /backup]#
>
>
>
Export: Release 8.1.7.0.1 - Production on Thu May 23 14:07:37 2002
(c) Copyright 2000 Oracle Corporation. All rights reserved.
Connected to: Oracle8i Enterprise Edition Release 8.1.7.0.1 - Production
With the Partitioning option
JServer Release 8.1.7.0.1 - Production
Export done in KO16KSC5601 character set and US7ASCII 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 QUEST_COM_PRODUCTS 2 rows exported
. . exporting table QUEST_COM_PRODUCTS_USED_BY 0 rows exported
. . exporting table QUEST_COM_PRODUCT_PRIVS 0 rows exported
. . exporting table QUEST_COM_USERS 0 rows exported
. . exporting table QUEST_COM_USER_PRIVILEGES 0 rows exported
. . exporting table QUEST_SL_COLLECTION_DEFINITION 0 rows exported
. . exporting table QUEST_SL_COLLECTION_DEF_REPOS 0 rows exported
. . exporting table QUEST_SL_COLLECTION_REPOSITORY 0 rows exported
. . exporting table QUEST_SL_ERRORS 0 rows exported
. . exporting table QUEST_SL_EXPLAIN 0 rows exported
. . exporting table QUEST_SL_EXPLAIN_PICK 0 rows exported
. . exporting table QUEST_SL_QUERY_DEFINITIONS 0 rows exported
. . exporting table QUEST_SL_QUERY_DEF_REPOSITORY 0 rows exported
. . exporting table QUEST_SL_REPOSITORY_EXPLAIN 0 rows exported
. . exporting table QUEST_SL_REPOSITORY_SQLAREA 0 rows exported
. . exporting table QUEST_SL_REPOSITORY_SQLTEXT 0 rows exported
. . exporting table QUEST_SL_REPOSITORY_STATISTICS 0 rows exported
. . exporting table QUEST_SL_REPOSITORY_TRANS_INFO 0 rows exported
. . exporting table QUEST_SL_REPOS_BIND_VALUES 0 rows exported
. . exporting table QUEST_SL_REPOS_LAB_DETAILS 0 rows exported
. . exporting table QUEST_SL_REPOS_PICK_DETAILS 0 rows exported
. . exporting table QUEST_SL_REPOS_ROOT 0 rows exported
. . exporting table QUEST_SL_REPOS_SGA_DETAILS 0 rows exported
. . exporting table QUEST_SL_REPOS_SGA_STATISTICS 0 rows exported
. . exporting table QUEST_SL_SQLAREA 0 rows exported
. . exporting table QUEST_SL_SQLTEXT 0 rows exported
. . exporting table QUEST_SL_USER 1 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 table DEPT 0 rows exported
. . exporting table DM_TIME 0 rows exported
. . exporting table HITEL 15786 rows exported
. . exporting table 거래처 166 rows exported
. 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.
[root@ns /backup]#
total 78M
-rw-r--r-- 1 root root 431 Mar 18 01:29 apache_mysql_oracle_zend.txt
-rw-r--r-- 1 root root 174k Oct 20 2001 busansms.tgz
-rw-r--r-- 1 root root 35M May 19 04:22 dbakorea-20020519.tgz
-rw-r--r-- 1 root root 34M May 16 18:21 dbakorea20020516.tgz
-rw-r--r-- 1 root root 3.8M May 11 00:49 full_20020511.dmp
-rw-r--r-- 1 root root 3.2k May 11 00:49 full_20020511.log
-rw-r--r-- 1 root root 3.9M May 23 14:08 full_20020523.dmp
-rw-r--r-- 1 root root 5.4k May 23 14:08 full_20020523.log
-rw-r--r-- 1 root root 35k Mar 17 22:22 httpd.conf
[root@ns /backup]#
Filesystem Size Used Avail Use% Mounted on
/dev/hda2 3.8G 2.9G 624M 83% /
[root@ns /backup]#
2. Shut down Oracle after all users are logged off.(and 기존 DB의 백업)
testDB겸 devDB지만(중요한 데이터가 없다는 뜻이겠다), 정석을 따라 일단 기존의 DB를 백업해 둔다.
중요한 작업이므로 가장 쉬운 백업/복구를 지원하는 cold backup으로 하겠다.
사실 위에서 exp를 했으므로 해줄 필요가 없지만, 괜히 잘못되면 복구시간 장난 아니게 늘어나니
cold백업을 해주기를 권하겠다. archive log를 사용할 경우 LOG_ARCHIVE_DEST를 고려해야 겠지만,
cold백업하므로 상관없다.
테스트없이 prodDB로 하는 사람이 있는데, 절대 처음부터 prodDB를 가지고 장난하지 말기 바란다.
[root@ns /backup]#
total 302824
-rw-r----- 1 oracle oinstall 4296704 May 23 14:17 control01.ctl
-rw-r----- 1 oracle oinstall 4296704 May 23 14:17 control02.ctl
-rw-r----- 1 oracle oinstall 10489856 May 23 14:08 indx01.dbf
-rw-r----- 1 oracle oinstall 52432896 May 23 14:08 rbs01.dbf
-rw-r----- 1 oracle oinstall 512512 May 23 14:08 redo01.log
-rw-r----- 1 oracle oinstall 512512 May 23 14:08 redo02.log
-rw-r----- 1 oracle oinstall 512512 May 23 14:08 redo03.log
-rw-r----- 1 oracle oinstall 104861696 May 23 14:08 system01.dbf
-rw-r----- 1 oracle oinstall 52432896 May 23 14:08 temp01.dbf
-rw-r----- 1 oracle oinstall 26087424 May 23 14:08 tools01.dbf
-rw-r----- 1 oracle oinstall 52432896 May 23 14:08 users01.dbf
[root@ns /backup]#
total 22
-rw-r--r-- 1 oracle oinstall 8385 Oct 22 1999 init.ora
lrwxrwxrwx 1 oracle oinstall 41 Apr 28 13:51 initdb.ora -> /u01/app/oracle/admin/db/pfile/initdb.ora
-rw-r--r-- 1 oracle oinstall 9219 Oct 22 1999 initdw.ora
-rw-rw---- 1 oracle oinstall 24 May 22 14:29 lkDB
-rwSr----- 1 oracle oinstall 1536 May 11 01:28 orapwdb
[root@ns /backup]#
[oracle@ns 8.1.7]$
Oracle Server Manager Release 3.1.7.0.0 - Production
Copyright (c) 1997, 1999, Oracle Corporation. All Rights Reserved.
Oracle8i Enterprise Edition Release 8.1.7.0.1 - Production
With the Partitioning option
JServer Release 8.1.7.0.1 - Production
SVRMGR>
Connected.
SVRMGR>
Database closed.
Database dismounted.
ORACLE instance shut down.
SVRMGR>
Server Manager complete.
[oracle@ns 8.1.7]$
logout
[root@ns /backup]#
tar: Removing leading `/' from archive names
u01/app/oracle/oradata/db/
u01/app/oracle/oradata/db/control01.ctl
u01/app/oracle/oradata/db/control02.ctl
u01/app/oracle/oradata/db/indx01.dbf
u01/app/oracle/oradata/db/rbs01.dbf
u01/app/oracle/oradata/db/redo01.log
u01/app/oracle/oradata/db/redo02.log
u01/app/oracle/oradata/db/redo03.log
u01/app/oracle/oradata/db/system01.dbf
u01/app/oracle/oradata/db/temp01.dbf
u01/app/oracle/oradata/db/tools01.dbf
u01/app/oracle/oradata/db/users01.dbf
u01/app/oracle/product/8.1.7/dbs/
u01/app/oracle/product/8.1.7/dbs/init.ora
u01/app/oracle/product/8.1.7/dbs/initdw.ora
u01/app/oracle/product/8.1.7/dbs/orapwdb
u01/app/oracle/product/8.1.7/dbs/initdb.ora
u01/app/oracle/product/8.1.7/dbs/lkDB
[root@ns /backup]#
total 102534
-rw-r--r-- 1 root root 431 Mar 18 01:29 apache_mysql_oracle_zend.txt
-rw-r--r-- 1 root root 177871 Oct 20 2001 busansms.tgz
-rw-r--r-- 1 root root 23474576 May 23 14:32 db_os_cold_20020523.tgz
-rw-r--r-- 1 root root 37055913 May 19 04:22 dbakorea-20020519.tgz
-rw-r--r-- 1 root root 35760091 May 16 18:21 dbakorea20020516.tgz
-rw-r--r-- 1 root root 3932160 May 11 00:49 full_20020511.dmp
-rw-r--r-- 1 root root 3261 May 11 00:49 full_20020511.log
-rw-r--r-- 1 root root 4128768 May 23 14:08 full_20020523.dmp
-rw-r--r-- 1 root root 5541 May 23 14:08 full_20020523.log
-rw-r--r-- 1 root root 35697 Mar 17 22:22 httpd.conf
[root@ns /backup]#
drwxr-xr-x oracle/oinstall 0 2002-05-11 01:25:05 u01/app/oracle/oradata/db/
-rw-r----- oracle/oinstall 4296704 2002-05-23 14:28:00 u01/app/oracle/oradata/db/control01.ctl
-rw-r----- oracle/oinstall 4296704 2002-05-23 14:28:00 u01/app/oracle/oradata/db/control02.ctl
-rw-r----- oracle/oinstall 10489856 2002-05-23 14:28:00 u01/app/oracle/oradata/db/indx01.dbf
-rw-r----- oracle/oinstall 52432896 2002-05-23 14:28:00 u01/app/oracle/oradata/db/rbs01.dbf
-rw-r----- oracle/oinstall 512512 2002-05-23 14:28:00 u01/app/oracle/oradata/db/redo01.log
-rw-r----- oracle/oinstall 512512 2002-05-23 14:08:13 u01/app/oracle/oradata/db/redo02.log
-rw-r----- oracle/oinstall 512512 2002-05-23 14:08:20 u01/app/oracle/oradata/db/redo03.log
-rw-r----- oracle/oinstall 104861696 2002-05-23 14:28:00 u01/app/oracle/oradata/db/system01.dbf
-rw-r----- oracle/oinstall 52432896 2002-05-23 14:28:00 u01/app/oracle/oradata/db/temp01.dbf
-rw-r----- oracle/oinstall 26087424 2002-05-23 14:28:00 u01/app/oracle/oradata/db/tools01.dbf
-rw-r----- oracle/oinstall 52432896 2002-05-23 14:28:00 u01/app/oracle/oradata/db/users01.dbf
drwxr-xr-x oracle/oinstall 0 2002-05-22 14:29:15 u01/app/oracle/product/8.1.7/dbs/
-rw-r--r-- oracle/oinstall 8385 1999-10-22 18:39:44 u01/app/oracle/product/8.1.7/dbs/init.ora
-rw-r--r-- oracle/oinstall 9219 1999-10-22 18:39:46 u01/app/oracle/product/8.1.7/dbs/initdw.ora
-rwSr----- oracle/oinstall 1536 2002-05-11 01:28:25 u01/app/oracle/product/8.1.7/dbs/orapwdb
lrwxrwxrwx oracle/oinstall 0 2002-04-28 13:51:59 u01/app/oracle/product/8.1.7/dbs/initdb.ora -> /u01/app/oracle/admin/db/pfile/initdb.ora
-rw-rw---- oracle/oinstall 24 2002-05-22 14:29:47 u01/app/oracle/product/8.1.7/dbs/lkDB
[root@ns /backup]#
3. Delete the database.
$ORACLE_HOME/dbs를 그대로 두고(init.ora가 있으므로), /u01/app/oracle/oradata/db의 모든 것들을 지워준다.
/u01/app/oracle/admin/db/중 pfile에는 init.ora파일이 있으므로 /u01/app/oracle/admin/db/를 지우고
싶다면 /u01/app/oracle/admin/db/pfile디렉토리는 놔두거나 init.ora을 $ORACLE_HOME/dbs에 옮긴뒤 지우도록한다.
나는 그냥 두기로 했다.
[root@ns /backup]#
[root@ns /backup]#
total 0
4. Re-create the database using the CREATE DATABASE command.
db생성은 oracle유저에서 합시다.
[root@ns /backup]#
/u01/app/oracle/product/8.1.7/dbs/initdb.ora의 패러미터를 다음과 같이 설정
[oracle@ns 8.1.7]$
Oracle Server Manager Release 3.1.7.0.0 - Production
Copyright (c) 1997, 1999, Oracle Corporation. All Rights Reserved.
Oracle8i Enterprise Edition Release 8.1.7.0.1 - Production
With the Partitioning option
JServer Release 8.1.7.0.1 - Production
SVRMGR>
SVRMGR>
Connected.
SVRMGR>
ORACLE instance started.
Total System Global Area 15868064 bytes
Fixed Size 73888 bytes
Variable Size 7233536 bytes
Database Buffers 8388608 bytes
Redo Buffers 172032 bytes
SVRMGR>
2>
3>
4>
5>
6>
7>
8>
9>
10>
11>
Statement processed.
SVRMGR>
SVRMGR>
2>
3>
4>
Statement processed.
SVRMGR>
2>
3>
4>
Statement processed.
SVRMGR>
2>
3>
4>
Statement processed.
SVRMGR>
2>
3>
4>
Statement processed.
SVRMGR>
Statement processed.
SVRMGR>
Statement processed.
SVRMGR>
Statement processed.
SVRMGR>
Statement processed.
SVRMGR>
Statement processed.
SVRMGR>
Statement processed.
SVRMGR>
Statement processed.
SVRMGR>
Statement processed.
SVRMGR>
Statement processed.
SVRMGR>
Statement processed.
SVRMGR>
Statement processed.
SVRMGR>
Statement processed.
SVRMGR>
Statement processed.
SVRMGR>
Statement processed.
SVRMGR>
Statement processed.
SVRMGR>
Statement processed.
SVRMGR>
Statement processed.
SVRMGR>
SVRMGR>
SVRMGR>
SVRMGR>
Connected.
SVRMGR>
SVRMGR>
Server Manager complete.
[oracle@ns db]$
5. Do a full database import (FULL=Y) to restore the entire database.
[root@ns /backup]#
>
>
>
>
Import: Release 8.1.7.0.1 - Production on Thu May 23 17:21:59 2002
(c) Copyright 2000 Oracle Corporation. All rights reserved.
Connected to: Oracle8i Enterprise Edition Release 8.1.7.0.1 - Production
With the Partitioning option
JServer Release 8.1.7.0.1 - Production
Export file created by EXPORT:V08.01.07 via conventional path
import done in KO16KSC5601 character set and US7ASCII NCHAR character set
. importing KANG's objects into KANG
. importing SYS's objects into SYS
. importing SYSTEM's objects into SYSTEM
. importing SYS's objects into SYS
. importing SYSTEM's objects into SYSTEM
. . importing table "DEF$_AQCALL" 0 rows imported
. . importing table "DEF$_AQERROR" 0 rows imported
. . importing table "DEF$_CALLDEST" 0 rows imported
. . importing table "DEF$_DEFAULTDEST" 0 rows imported
. . importing table "DEF$_DESTINATION" 0 rows imported
. . importing table "DEF$_ERROR" 0 rows imported
. . importing table "DEF$_LOB" 0 rows imported
. . importing table "DEF$_ORIGIN" 0 rows imported
. . importing table "DEF$_PROPAGATOR" 0 rows imported
. . importing table "DEF$_PUSHED_TRANSACTIONS" 0 rows imported
. . importing table "DEF$_TEMP$LOB" 0 rows imported
. . importing table "QUEST_COM_PRODUCTS" 2 rows imported
. . importing table "QUEST_COM_PRODUCTS_USED_BY" 0 rows imported
. . importing table "QUEST_COM_PRODUCT_PRIVS" 0 rows imported
. . importing table "QUEST_COM_USERS" 0 rows imported
. . importing table "QUEST_COM_USER_PRIVILEGES" 0 rows imported
. . importing table "QUEST_SL_COLLECTION_DEFINITION" 0 rows imported
. . importing table "QUEST_SL_COLLECTION_DEF_REPOS" 0 rows imported
. . importing table "QUEST_SL_COLLECTION_REPOSITORY" 0 rows imported
. . importing table "QUEST_SL_ERRORS" 0 rows imported
. . importing table "QUEST_SL_EXPLAIN" 0 rows imported
. . importing table "QUEST_SL_EXPLAIN_PICK" 0 rows imported
. . importing table "QUEST_SL_QUERY_DEFINITIONS" 0 rows imported
. . importing table "QUEST_SL_QUERY_DEF_REPOSITORY" 0 rows imported
. . importing table "QUEST_SL_REPOSITORY_EXPLAIN" 0 rows imported
. . importing table "QUEST_SL_REPOSITORY_SQLAREA" 0 rows imported
. . importing table "QUEST_SL_REPOSITORY_SQLTEXT" 0 rows imported
. . importing table "QUEST_SL_REPOSITORY_STATISTICS" 0 rows imported
. . importing table "QUEST_SL_REPOSITORY_TRANS_INFO" 0 rows imported
. . importing table "QUEST_SL_REPOS_BIND_VALUES" 0 rows imported
. . importing table "QUEST_SL_REPOS_LAB_DETAILS" 0 rows imported
. . importing table "QUEST_SL_REPOS_PICK_DETAILS" 0 rows imported
. . importing table "QUEST_SL_REPOS_ROOT" 0 rows imported
. . importing table "QUEST_SL_REPOS_SGA_DETAILS" 0 rows imported
. . importing table "QUEST_SL_REPOS_SGA_STATISTICS" 0 rows imported
. . importing table "QUEST_SL_SQLAREA" 0 rows imported
. . importing table "QUEST_SL_SQLTEXT" 0 rows imported
. . importing table "QUEST_SL_USER" 1 rows imported
. . importing table "SQLPLUS_PRODUCT_PROFILE" 0 rows imported
. importing OUTLN's objects into OUTLN
. . importing table "OL$" 0 rows imported
. . importing table "OL$HINTS" 0 rows imported
. importing KANG's objects into KANG
. . importing table "DEPT" 0 rows imported
. . importing table "DM_TIME" 0 rows imported
. . importing table "HITEL" 15786 rows imported
. . importing table "거래처" 166 rows imported
. importing SYSTEM's objects into SYSTEM
. importing KANG's objects into KANG
. importing SYSTEM's objects into SYSTEM
. importing DBSNMP's objects into DBSNMP
. importing SYSTEM's objects into SYSTEM
. importing OUTLN's objects into OUTLN
. importing SYSTEM's objects into SYSTEM
. importing KANG's objects into KANG
. importing SYSTEM's objects into SYSTEM
About to enable constraints...
. importing KANG's objects into KANG
. importing SYSTEM's objects into SYSTEM
Import terminated successfully without warnings.
[root@ns /backup]#
"무단배포금지: 클라우드포털(www.linux.co.kr)의 모든 강좌는 저작권에 의해 보호되는 콘텐츠입니다. 무단으로 복제하여 배포하는 행위는 금지되어 있습니다."
관련자료
-
이전
-
다음
댓글 0
등록된 댓글이 없습니다.
