log miner, 클론 db, export & import, data pump

YoonSeo Park ·2024년 1월 22일
clone dbdata pumpexportimportlog miner
0

Oracle Architecture

목록 보기
10/11

2023/01/22

log miner

: redo logfile 에 있는 내용을 추출해서 SQL문장으로 변환해주는 유틸리티

  • 현재 세션에서만 가능하다.

  • 최소 supplemental 로그 데이터를 사용하는지 여부

select supplemental_log_data_min from v$database;
  • yes로 변경
alter database add supplemental log data;
  • 확인
select supplemental_log_data_min from v$database;
---
SUPPLEME
--------
YES
---
  • 작업
create table hr.test_20240122(id number, name varchar2(30));

insert into hr.test_20240122(id,name) values(1,'oracle');
insert into hr.test_20240122(id,name) values(2,'overwatch');
commit;

update hr.test_20240122
set name ='itwill' where id=1;

delete from hr.test_20240122 where id=2;

commit;
  • 리두로그파일, 그룹 보기
select a.group#,b.thread#,b.sequence#, member, b.bytes/1024/1024 mb, b.archived, b.status,
        b.first_change#, b.next_change#, b.next_time
from v$logfile a, v$log b
where a.group# = b.group#
order by 1,2;
  • 로그마이너 파일 생성
begin
    dbms_logmnr.add_logfile(logfilename=>'/u01/app/oracle/oradata/ora11g/redo01.log',
                            options=> dbms_logmnr.new); -- 처음꺼만 new
    dbms_logmnr.add_logfile(logfilename=>'/u01/app/oracle/oradata/ora11g/redo02.log',
                            options=> dbms_logmnr.addfile); -- 이후부터는 addfile                  
end;
/
  • 로그마이너 생성한것 확인
select db_name, filename from v$logmnr_logs;
  • 로그마이너 start
BEGIN
    dbms_logmnr.start_logmnr(options=>dbms_logmnr.dict_from_online_catalog);
END;
/
  • 로그마이너로 생성한 파일의 redolog 의 sql문 보기
select to_char(timestamp,'yyyy/mm/dd hh24:mi:ss'),operation,
    sql_redo, sql_undo
from v$logmnr_contents
where seg_name ='TEST_20240122';
---
2024/01/13 23:15:24	DDL	create table hr.test_20240122(id number, name varchar2(30));
2024/01/13 23:15:25	INSERT	insert into "HR"."TEST_20240122"("ID","NAME") values ('1','oracle');
2024/01/13 23:15:26	INSERT	insert into "HR"."TEST_20240122"("ID","NAME") values ('2','overwatch');
2024/01/13 23:15:29	UPDATE	update "HR"."TEST_20240122" set "NAME" = 'itwill' where "NAME" = 'oracle' and ROWID = 'AAAVrkAAEAAAAKcAAA';
2024/01/13 23:15:29	DELETE	delete from "HR"."TEST_20240122" where "ID" = '2' and "NAME" = 'overwatch' and ROWID = 'AAAVrkAAEAAAAKcAAB';
  • 로그마이너 end
exec dbms_logmnr.end_logmnr
  • 로그마이너 종료했으므로 에러가뜬다.
select to_char(timestamp,'yyyy/mm/dd hh24:mi:ss'),operation,
    sql_redo, sql_undo
from v$logmnr_contents
where seg_name ='TEST_20240122';

복제 데이터베이스

: 운영 데이터파일, 리두로그 파일을 이용해서 데이터베이스를 생성

■ 복제 db 생성

● 1. 복제 db에 datafile, redologfile 복제
: sysaux 와 undo 테이블스페이스는 필수이다.

[oracle@oracle ~]$ mkdir clone
[oracle@oracle ~]$ cd clone/
[oracle@oracle clone]$ cp -av /home/oracle/backup/arch/cold_20240116/*.dbf .
‘/home/oracle/backup/arch/cold_20240116/example01.dbf’ -> ‘./example01.dbf’
‘/home/oracle/backup/arch/cold_20240116/sysaux01.dbf’ -> ‘./sysaux01.dbf’
‘/home/oracle/backup/arch/cold_20240116/system01.dbf’ -> ‘./system01.dbf’
‘/home/oracle/backup/arch/cold_20240116/temp01.dbf’ -> ‘./temp01.dbf’
‘/home/oracle/backup/arch/cold_20240116/undotbs01.dbf’ -> ‘./undotbs01.dbf’
‘/home/oracle/backup/arch/cold_20240116/users01.dbf’ -> ‘./users01.dbf’
[oracle@oracle clone]$ cp -av /home/oracle/backup/arch/cold_20240116/*.log .
‘/home/oracle/backup/arch/cold_20240116/redo01.log’ -> ‘./redo01.log’
‘/home/oracle/backup/arch/cold_20240116/redo02.log’ -> ‘./redo02.log’
‘/home/oracle/backup/arch/cold_20240116/redo03.log’ -> ‘./redo03.log’
[oracle@oracle clone]$ ls
example01.dbf  redo02.log  sysaux01.dbf  temp01.dbf     users01.dbf
redo01.log     redo03.log  system01.dbf  undotbs01.dbf

● 2. pfile 생성

  • 버전확인
select * from v$version;
[oracle@oracle ~]$ vi $ORACLE_HOME/dbs/initclone.ora
---
compatible='11.2.0.4.0'
*.control_files='/home/oracle/clone/control01.ctl'
*.db_name='clone'
*.log_archive_dest_1='location=/home/oracle/clone mandatory'
*.log_archive_format='arch_%t_%s_%r.arc'
*.undo_tablespace='undotbs'
---

● 3. clone 접속

  • $ORACLE_HOME 확인
[oracle@oracle dbs]$ echo $ORACLE_HOME
/u01/app/oracle/product/11.2.0.4/db_1
  • SID, sqlplus 띄어줄 디렉토리 지정
    : 이 스크립트는 사용자에게 현재 사용 가능한 Oracle 데이터베이스 목록을 보여주고, 사용자가 선택한 데이터베이스의 환경을 설정합니다.
    • . oraenv
      Oracle 사용자가 여러 데이터베이스 간에 전환할때 사용
[oracle@oracle ~]$ . oraenv
ORACLE_SID = [ora11g] ? clone
ORACLE_HOME = [/home/oracle] ? /u01/app/oracle/product/11.2.0.4/db_1
The Oracle base remains unchanged with value /u01/app/oracle
  • startup nomount
    : 초기파라미터인 initclone.ora 를 먼저 찾는다
[oracle@oracle clone]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Sun Jan 14 03:10:43 2024

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to an idle instance

SQL> startup nomount
ORACLE instance started.

Total System Global Area  167407616 bytes
Fixed Size                  1363300 bytes
Variable Size             109052572 bytes
Database Buffers           50331648 bytes
Redo Buffers                6660096 bytes

● 4. control file 생성

  • trace 남기기
SQL> alter database backup controlfile to trace as '/home/oracle/new_control.sql';

Database altered.
  • trace를 보고 controlfile 미리 만들고 실행
SQL> CREATE CONTROLFILE SET DATABASE "CLONE" RESETLOGS  ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 '/home/oracle/clone/redo01.log'  SIZE 50M BLOCKSIZE 512,
  GROUP 2 '/home/oracle/clone/redo02.log'  SIZE 50M BLOCKSIZE 512,
  GROUP 3 '/home/oracle/clone/redo03.log'  SIZE 50M BLOCKSIZE 512
DATAFILE
  '/home/oracle/clone/system01.dbf',
  '/home/oracle/clone/sysaux01.dbf',
  '/home/oracle/clone/users01.dbf',
  '/home/oracle/clone/example01.dbf',
  '/home/oracle/clone/undotbs01.dbf'
CHARACTER SET AL32UTF8
;

Control file created.
  • 변경한것
  1. REUSE -> SET
  2. NORESETLOGS -> RESETLOGS
  3. DB 이름 변경
  4. /home/oracle/clone/ 경로변경
  5. 공백줄 없는지 확인
  • controlfile 생성하면 mount단계까지 db가 올라온다.
SQL> select status from v$instance;

STATUS
------------
MOUNTED

● 5. cancel base recovery

SQL> recover database until cancel using backup controlfile
ORA-00279: change 1588754 generated at 01/12/2024 17:26:30 needed for thread 1
ORA-00289: suggestion : /home/oracle/clone/arch_1_1_1158057974.arc
ORA-00280: change 1588754 for thread 1 is in sequence #1


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
Media recovery cancelled.

SQL> alter database open resetlogs;

Database altered.
  • 확인
SQL> select name from v$database;

NAME
---------
CLONE

SQL> select count(*) from hr.employees;

  COUNT(*)
----------
       107
  • tempfile도 아까 cp 했으니깐 추가해준다
SQL> select * from v$tempfile;

no rows selected

SQL> ! ls /home/oracle/clone
arch_1_1_1158057974.arc  redo01.log  sysaux01.dbf  undotbs01.dbf
control01.ctl            redo02.log  system01.dbf  users01.dbf
example01.dbf            redo03.log  temp01.dbf
  • os상에 파일은 있으니깐 reuse
SQL> alter tablespace temp add tempfile '/home/oracle/clone/temp01.dbf' reuse;

■ 복제 db 삭제

  • OS상에서 모두 삭제하면 된다
    : datafile, redologfile, 초기파라미터 파일
[oracle@oracle ~]$ rm -r clone
[oracle@oracle ~]$ cd $ORACLE_HOME/dbs
[oracle@oracle dbs]$ ls
hc_clone.dat   initclone.ora  initora11g.ora  lkORA11G     spfileora11g.ora
hc_ora11g.dat  init.ora       lkCLONE         orapwora11g
[oracle@oracle dbs]$ rm initclone.ora
SQL> select name from v$database;

NAME
---------
CLONE

SQL> shu abort
ORACLE instance shut down.
  • 삭제후 다시 sqlplus 하면 안된다.
    : . oraenv 이용해서 sid와 다시지정
[oracle@oracle dbs]$ sqlplus / as sysdba
bash: sqlplus: command not found...
[oracle@oracle dbs]$ . oraenv
ORACLE_SID = [clone] ? ora11g
The Oracle base has been set to /u01/app/oracle
[oracle@oracle dbs]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Sun Jan 14 00:48:10 2024

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select name from v$database;

NAME
---------
ORA11G
  • ps -ef 명령은 현재 실행 중인 모든 프로세스를 상세하게 보여주는 명령어

▶ 테이블 드랍시에 복제 db를 이용하여 복구후 export, import

● 1. 작업

  • 테이블 생성 ctas
create table hr.insa_emp tablespace users as select * from hr.employees;
select count(*) from hr.insa_emp;
  • tip
    : ctas 로 테이블을 만들면서 not null 제약조건만 걸린다.
select constraint_name, constraint_type, search_condition,
        status,index_name
from dba_constraints
where table_name = 'INSA_EMP';
---
SYS_C0011533	C	"JOB_ID" IS NOT NULL	ENABLED	
SYS_C0011532	C	"HIRE_DATE" IS NOT NULL	ENABLED	
SYS_C0011531	C	"EMAIL" IS NOT NULL	ENABLED	
SYS_C0011530	C	"LAST_NAME" IS NOT NULL	ENABLED
  • pk 제약조건 추가
alter table hr.insa_emp add constraint insa_emp_id_pk primary key(employee_id);
  • 로그스위치 발생
alter system switch logfile;
  • logfile 확인
select a.group#,b.thread#,b.sequence#, member, b.bytes/1024/1024 mb, b.archived, b.status,
        b.first_change#, b.next_change#, b.next_time
from v$logfile a, v$log b
where a.group# = b.group#
order by 1,2;

● 2. 실수 : 테이블 삭제

  • 테이블 드랍
drop table hr.insa_emp purge;

● 3. 로그마이너로 삭제한 시간 확인

  • 로그파일 시작
select a.group#,b.thread#,b.sequence#, member, b.bytes/1024/1024 mb, b.archived, b.status,
        b.first_change#, b.next_change#, b.next_time
from v$logfile a, v$log b
where a.group# = b.group#
order by 1,2;
  • 로그마이너 (redologfile, 아카이브파일)
    : add_logfile로 추가한 것과 add_logfile로 추가한 아카이브 파일이 같은것이면 에러가뜨므로 다른것을 넣어준다.
  • 리두로그파일, 아카이브 파일 시퀀스 확인하고 로그마이너 생성한다.
SQL> ! ls /home/oracle/arch1

SQL> select a.group#,b.thread#,b.sequence#, member, b.bytes/1024/1024 mb, b.archived, b.status,
        b.first_change#, b.next_change#, b.next_time
from v$logfile a, v$log b
where a.group# = b.group#
order by 1,2;
  • 생성
begin
    dbms_logmnr.add_logfile(logfilename=>'/u01/app/oracle/oradata/ora11g/redo02.log',
                            options=> dbms_logmnr.new); -- current한 리두로그그룹
    dbms_logmnr.add_logfile(logfilename=>'/u01/app/oracle/oradata/ora11g/redo01.log',
                            options=> dbms_logmnr.addfile);
    dbms_logmnr.add_logfile(logfilename=>'/u01/app/oracle/oradata/ora11g/redo03.log',
                            options=> dbms_logmnr.addfile);
    dbms_logmnr.add_logfile(logfilename=>'/home/oracle/arch1/arch_1_3_1158057974.arc',
                            options=> dbms_logmnr.addfile);  -- 아카이브 파일경로 
    dbms_logmnr.add_logfile(logfilename=>'/home/oracle/arch1/arch_1_4_1158057974.arc',
                            options=> dbms_logmnr.addfile);  -- 아카이브 파일경로                              
end;
/
  • 로그마이너 확인
select db_name, filename from v$logmnr_logs;
---
ORA11G	/home/oracle/arch1/arch_1_1_1158057974.arc
ORA11G	/home/oracle/arch1/arch_1_2_1158057974.arc
ORA11G	/u01/app/oracle/oradata/ora11g/redo03.log
ORA11G	/u01/app/oracle/oradata/ora11g/redo01.log
ORA11G	/u01/app/oracle/oradata/ora11g/redo02.log
  • 로그마이너 start
BEGIN
    dbms_logmnr.start_logmnr(options=>dbms_logmnr.dict_from_online_catalog);
END;
/
  • 로그마이너로 INSA_EMP 테이블의 sql문 확이
select to_char(timestamp,'yyyy/mm/dd hh24:mi:ss'),operation,
    sql_redo, sql_undo
from v$logmnr_contents
where seg_name ='INSA_EMP';
---
2024/01/14 01:06:24	DDL	create table hr.insa_emp tablespace users as select * from hr.employees;
2024/01/14 01:21:26	DDL	alter table hr.insa_emp add constraint insa_emp_id_pk primary key(employee_id);
2024/01/14 02:58:20	DDL	drop table hr.insa_emp purge;
  • 로그마이너 end
exec dbms_logmnr.end_logmnr

● 4. 복제 데이터베이스 생성시 필요한 데이터파일, 리두로그파일, 아카이브 파일 복사
: sysaux, system, undo 필수

[oracle@oracle cold_20240116]$ pwd
/home/oracle/backup/arch/cold_20240116

[oracle@oracle cold_20240116]$ cp -av system01.dbf /home/oracle/clone
‘system01.dbf’ -> ‘/home/oracle/clone/system01.dbf’
[oracle@oracle cold_20240116]$ cp -av sysaux01.dbf /home/oracle/clone
‘sysaux01.dbf’ -> ‘/home/oracle/clone/sysaux01.dbf’
[oracle@oracle cold_20240116]$ cp -av undotbs01.dbf /home/oracle/clone
‘undotbs01.dbf’ -> ‘/home/oracle/clone/undotbs01.dbf’
[oracle@oracle cold_20240116]$ cp -av users01.dbf /home/oracle/clone
‘users01.dbf’ -> ‘/home/oracle/clone/users01.dbf’
[oracle@oracle cold_20240116]$ cp -av temp01.dbf /home/oracle/clone
‘temp01.dbf’ -> ‘/home/oracle/clone/temp01.dbf’
[oracle@oracle cold_20240116]$ cp -av *.log /home/oracle/clone
‘redo01.log’ -> ‘/home/oracle/clone/redo01.log’
‘redo02.log’ -> ‘/home/oracle/clone/redo02.log’
‘redo03.log’ -> ‘/home/oracle/clone/redo03.log’
[oracle@oracle cold_20240116]$ cd /home/oracle/arch1
[oracle@oracle arch1]$ cp -av *.* /home/oracle/clone/
‘arch_1_1_1158057974.arc’ -> ‘/home/oracle/clone/arch_1_1_1158057974.arc’
‘arch_1_2_1158057974.arc’ -> ‘/home/oracle/clone/arch_1_2_1158057974.arc’
‘arch_1_3_1158057974.arc’ -> ‘/home/oracle/clone/arch_1_3_1158057974.arc’
‘arch_1_4_1158057974.arc’ -> ‘/home/oracle/clone/arch_1_4_1158057974.arc’
  • 확인
[oracle@oracle arch1]$ cd /home/oracle/clone
[oracle@oracle clone]$ ls
arch_1_1_1158057974.arc  arch_1_4_1158057974.arc  redo03.log    temp01.dbf
arch_1_2_1158057974.arc  redo01.log               sysaux01.dbf  undotbs01.dbf
arch_1_3_1158057974.arc  redo02.log               system01.dbf  users01.dbf

● 5. pfile 생성

[oracle@oracle clone]$ vi $ORACLE_HOME/dbs/initclone.ora
---
compatible='11.2.0.4.0'
*.control_files='/home/oracle/clone/control01.ctl'
*.db_name='clone'
*.log_archive_dest_1='location=/home/oracle/clone mandatory'
*.log_archive_format='arch_%t_%s_%r.arc'
*.undo_tablespace='undotbs'
---

● 6. clone db 접속 

[oracle@oracle clone]$ . oraenv
ORACLE_SID = [ora11g] ? clone
ORACLE_HOME = [/home/oracle] ? /u01/app/oracle/product/11.2.0.4/db_1
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@oracle clone]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Sun Jan 14 03:10:43 2024

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to an idle instance.
  • nomount 상태로 startup
    : 생서한 pfile로 열기
SQL> startup nomount
ORACLE instance started.

Total System Global Area  167407616 bytes
Fixed Size                  1363300 bytes
Variable Size             109052572 bytes
Database Buffers           50331648 bytes
Redo Buffers                6660096 bytes

● 7. control file 생성

SQL>
CREATE CONTROLFILE SET DATABASE "CLONE" RESETLOGS  ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 '/home/oracle/clone/redo01.log'  SIZE 50M BLOCKSIZE 512,
  GROUP 2 '/home/oracle/clone/redo02.log'  SIZE 50M BLOCKSIZE 512,
  GROUP 3 '/home/oracle/clone/redo03.log'  SIZE 50M BLOCKSIZE 512
DATAFILE
  '/home/oracle/clone/system01.dbf',
  '/home/oracle/clone/sysaux01.dbf',
  '/home/oracle/clone/users01.dbf',
  '/home/oracle/clone/undotbs01.dbf'
CHARACTER SET AL32UTF8
;

Control file created.

: temp는 cp안했으므로 삭제했다.

  • controlfile 생성하면 mount단계까지 db가 올라온다.
SQL> select status from v$instance;

STATUS
------------
MOUNTED
  • datafile 이름,상태 보기
SQL> select name, status from v$datafile;

NAME
--------------------------------------------------------------------------------
STATUS
-------
/home/oracle/clone/system01.dbf
SYSTEM

/home/oracle/clone/sysaux01.dbf
RECOVER

/home/oracle/clone/users01.dbf
RECOVER


NAME
--------------------------------------------------------------------------------
STATUS
-------
/home/oracle/clone/undotbs01.dbf
RECOVER
  • 날짜 형식 바꾸기
SQL> alter session set nls_date_format ='yyyy/mm/dd hh24:mi:ss';

Session altered.

● 8. drop한 시간 전으로 time base recovery

  • auto
SQL> recover database until time '2024/01/14 02:57:2' using backup controlfile
ORA-00279: change 1588754 generated at 01/12/2024 17:26:30 needed for thread 1
ORA-00289: suggestion : /home/oracle/clone/arch_1_1_1158057974.arc
ORA-00280: change 1588754 for thread 1 is in sequence #1


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00279: change 1593539 generated at 01/13/2024 23:15:05 needed for thread 1
ORA-00289: suggestion : /home/oracle/clone/arch_1_2_1158057974.arc
ORA-00280: change 1593539 for thread 1 is in sequence #2
ORA-00278: log file '/home/oracle/clone/arch_1_1_1158057974.arc' no longer
needed for this recovery


ORA-00279: change 1593542 generated at 01/13/2024 23:15:06 needed for thread 1
ORA-00289: suggestion : /home/oracle/clone/arch_1_3_1158057974.arc
ORA-00280: change 1593542 for thread 1 is in sequence #3
ORA-00278: log file '/home/oracle/clone/arch_1_2_1158057974.arc' no longer
needed for this recovery


ORA-00279: change 1601683 generated at 01/14/2024 01:22:06 needed for thread 1
ORA-00289: suggestion : /home/oracle/clone/arch_1_4_1158057974.arc
ORA-00280: change 1601683 for thread 1 is in sequence #4
ORA-00278: log file '/home/oracle/clone/arch_1_3_1158057974.arc' no longer
needed for this recovery


ORA-00279: change 1601737 generated at 01/14/2024 01:23:49 needed for thread 1
ORA-00289: suggestion : /home/oracle/clone/arch_1_5_1158057974.arc
ORA-00280: change 1601737 for thread 1 is in sequence #5
ORA-00278: log file '/home/oracle/clone/arch_1_4_1158057974.arc' no longer
needed for this recovery


ORA-00308: cannot open archived log
'/home/oracle/clone/arch_1_5_1158057974.arc'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
  • redologfile 넣어주면 된다
    : 하지만 나는 redo, 아카이브 파일 둘다 시퀀스#5 가 없어서 그냥 cancel base recovery 했다.
SQL> recover database until time '2024/01/14 02:57:2' using backup controlfile
ORA-00279: change 1601737 generated at 01/14/2024 01:23:49 needed for thread 1
ORA-00289: suggestion : /home/oracle/clone/arch_1_5_1158057974.arc
ORA-00280: change 1601737 for thread 1 is in sequence #5


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
Media recovery cancelled.
  • open resetlogs 
SQL> alter database open resetlogs;

Database altered.

● 9. 확인

  • 테이블 드랍하기전으로 돌아가서 테이블이 조회된다.
SQL> select count(*) from hr.insa_emp;

  COUNT(*)
----------
       107

● 10. 클론db에서 export, 원래 db에서 import

  • 요약
    클론 db에서 살린 data를 export해서
    원래 db에 import하면 된다.
  • 클론 db에서 export
    : 익스포트는 일반적으로 sys 를 사용하지 않고, system 이 일반적이다.

    exp hr/hr file=insa_emp.dmp tables=hr.insa_emp statistics=none

[oracle@oracle ~]$ exp hr/hr file=insa_emp.dmp tables=hr.insa_emp statistics=none
-- 통계정보는 none
Export: Release 11.2.0.4.0 - Production on Sun Jan 14 03:33:53 2024

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


Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses AL32UTF8 character set (possible charset conversion)

About to export specified tables via Conventional Path ...
. . exporting table                       INSA_EMP        107 rows exported
Export terminated successfully without warnings.
[oracle@oracle ~]$ ls insa_emp.dmp
insa_emp.dmp
  • ora11g db로 접속
[oracle@oracle ~]$ . oraenv
ORACLE_SID = [ora11g] ? ora11g
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@oracle ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Sun Jan 14 03:29:46 2024

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select name from v$database;

NAME
---------
ORA11G
  • ora11g db에서 import

    imp hr/hr file=insa_emp.dmp tables=insa_emp

[oracle@oracle ~]$ imp hr/hr file=insa_emp.dmp tables=insa_emp

Import: Release 11.2.0.4.0 - Production on Sun Jan 14 03:35:43 2024

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


Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Export file created by EXPORT:V11.02.00 via conventional path
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses AL32UTF8 character set (possible charset conversion)
. importing HR's objects into HR
. importing HR's objects into HR
. . importing table                     "INSA_EMP"        107 rows imported
Import terminated successfully without warnings.
[oracle@oracle ~]$ exit
exit

● 11. 원래 db (ora11g) 확인

  • 확인
SQL> select instance_name, status from v$instance;

INSTANCE_NAME    STATUS
---------------- ------------
ora11g           OPEN

SQL> select count(*) from hr.insa_emp;

  COUNT(*)
----------
       107

▶ export & import

  • export

    export는 오라클 db가 서로 다른 하드웨어 및 소프트웨어 구성을 사용하는 플랫폼에서 데이터베이스 간 데이터 객체를 이동할 수 있는 방법을 제공한다.

  • import

    import는 export 덤프 파일에서 db에 로드하는 기능을 제공한다.

export & import 할떄
system 계정에서하는것은 필수이다.

● 1. 작업

  • 테이블 생성
create table hr.emp as select * from hr.employees;
select count(*) from hr.emp;

select *
from dba_constraints
  • export hr.emp
[oracle@oracle ~]$ exp userid=system/oracle tables=hr.emp file=hr_emp.dmp statistics=none

Export: Release 11.2.0.4.0 - Production on Sun Jan 14 05:08:28 2024

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


Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses AL32UTF8 character set (possible charset conversion)

About to export specified tables via Conventional Path ...
Current user changed to HR
. . exporting table                            EMP        107 rows exported
Export terminated successfully without warnings.
[oracle@oracle ~]$ ls -lh hr_emp.dmp
-rw-r--r--. 1 oracle oinstall 24K Jan 14 05:08 hr_emp.dmp
  • 테이블 삭제
drop table hr.emp cascade constraint purge;
  • system 권한으로 hr계정으로 import해줘
[oracle@oracle ~]$ imp userid=system/oracle file=hr_emp.dmp fromuser=hr

Import: Release 11.2.0.4.0 - Production on Sun Jan 14 05:13:44 2024

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


Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Export file created by EXPORT:V11.02.00 via conventional path
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses AL32UTF8 character set (possible charset conversion)
. importing SYSTEM's objects into SYSTEM
. importing HR's objects into HR
. . importing table                          "EMP"        107 rows imported
Import terminated successfully without warnings.
[oracle@oracle ~]$ exit
exit

SQL> select count(*) from hr.emp;

  COUNT(*)
----------
       107
  • truncate한것 import로 복구
SQL> truncate table hr.emp;

Table truncated.

SQL> select count(*) from hr.emp;

  COUNT(*)
----------
         0
         
SQL> imp system/oracle file=hr_emp.dmp fromuser=hr tables=emp data_only=y

SQL> select count(*) from hr.emp;

  COUNT(*)
----------
       127
  • 유저생성
create user michael identified by oracle default tablespace users quota 10m on users;

grant create session to michael;
  • hr import 하는데 소유권을 michael
imp userid=system/oracle file=hr_emp.dmp fromuser=hr touser=michael


SQL> select count(*) from michael.emp;

  COUNT(*)
----------
       107

▶ user 레벨 export

● 1. user 레벨 export

[oracle@oracle ~]$ exp system/oracle file=michael_owner.dmp owner=michael

[oracle@oracle ~]$ exp system/oracle file=hr_owner.dmp owner=hr

● 2. 유저삭제

SQL> drop user michael cascade;

User dropped.

● 3. import하기 위해 유저만들기

SQL> create user michael identified by oracle default tablespace users quota 10m on users;

User created.

SQL> grant create session to michael;

Grant succeeded.

● 4. user 레벨에서의 import

[oracle@oracle ~]$ imp system/oracle fromuser=michael file=michael_owner.dmp

● 5. 확인

SQL> conn michael/oracle
Connected.
SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
EMP                            TABLE

2023/01/23

create tablespace insa_tbs datafile
'/u01/app/oracle/oradata/ora11g/insa_tbs01.dbf' size 10m;


-- ctas 로 테이블스페이스 지정할때는 이렇게해야한다.
CREATE TABLE hr.emp
TABLESPACE insa_tbs
AS
SELECT * FROM hr.employees;


DROP TABLESPACE insa_tbs INCLUDING CONTENTS AND DATAFILES;

Data Pump

: 고속 데이터/메타 데이터 이동(export, import)을 위한 유틸리티

  • 데이터: 데이터 내용

  • 메타 데이터: 테이블구조

  • dbms_datapump

  • expdp, impdp

  • 논리적인 디렉토리생성
SQL> create directory pump_dir as '/home/oracle/data_pump';
Directory created.
  • 디렉토리 확인
SQL> select * from dba_directories where directory_name ='PUMP_DIR';
  • 논리적 디렉토를 읽고 쓰는 권한 부여
SQL> grant read, write on directory pump_dir to hr;
Grant succeeded.
  • 논리적인 디렉토리 삭제
SQL> drop directory pump_dir;
Directory dropped.

export & import pump 시나리오

● 1. hr유저의 특정테이블 export
: hr유저의 3개의 테이블을 덤프파일로 export

[oracle@oracle ~]$ expdp system/oracle directory=pump_dir dumpfile=hr_emp_dept_job.dmp tables=hr.employees, hr.departments, hr.job_history
------------------------------
Export: Release 11.2.0.4.0 - Production on Sun Jan 14 08:12:43 2024

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_TABLE_01":  system/******** directory=pump_dir dumpfile=hr_emp_dept_job.dmp tables=hr.employees, hr.departments, hr.job_history
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 192 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type TABLE_EXPORT/TABLE/COMMENT
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/TRIGGER
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "HR"."DEPARTMENTS"                          7.007 KB      27 rows
. . exported "HR"."EMPLOYEES"                            16.80 KB     107 rows
. . exported "HR"."JOB_HISTORY"                          7.054 KB      10 rows
Master table "SYSTEM"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TABLE_01 is:
  /home/oracle/data_pump/hr_emp_dept_job.dmp
Job "SYSTEM"."SYS_EXPORT_TABLE_01" successfully completed at Sun Jan 14 08:12:47 2024 elapsed 0 00:00:04

[oracle@oracle ~]$ ls /home/oracle/data_pump/
expdat.dmp  export.log  hr_emp_dept_job.dmp

● 2. 테이블 지우기

SQL> select count(*) from hr.job_history;

  COUNT(*)
----------
        10

SQL> drop table hr.job_history purge;

Table dropped.

SQL> select count(*) from hr.job_history;
select count(*) from hr.job_history
                        *
ERROR at line 1:
ORA-00942: table or view does not exist

● 3. import로 hr.job_history 테이블만 가져오기

expdp=유저/비밀번호
directory=논리적이름
dumpfile=덤프받을파일이름
schemas=유저이름

SQL> !
[oracle@oracle ~]$ impdp system/oracle directory=pump_dir dumpfile=hr_emp_dept_job.dmp tables=hr.job_history
-------------------
Import: Release 11.2.0.4.0 - Production on Sun Jan 14 08:16:04 2024

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_TABLE_01":  system/******** directory=pump_dir dumpfile=hr_emp_dept_job.dmp tables=hr.job_history
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "HR"."JOB_HISTORY"                          7.054 KB      10 rows
Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type TABLE_EXPORT/TABLE/COMMENT
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "SYSTEM"."SYS_IMPORT_TABLE_01" successfully completed at Sun Jan 14 08:16:06 2024 elapsed 0 00:00:01

● 4. 확인

[oracle@oracle ~]$ exit
exit

SQL> select count(*) from hr.job_history;

  COUNT(*)
----------
        10

▶ data only

● 1. truncate 테이블

SQL> select count(*) from hr.job_history;

  COUNT(*)
----------
        10

SQL> grant read, write on directory pump_dir to hr;

Grant succeeded.

SQL> truncate table hr.job_history;

Table truncated.

SQL> select count(*) from hr.job_history;

  COUNT(*)
----------
         0

● 2. truncate 했으므로 테이블구조는 있다.
: 따라서 데이터만 가져오면 된다
-> content=data_only

SQL>
SQL> !
[oracle@oracle ~]$ impdp system/oracle directory=pump_dir dumpfile=hr_emp_dept_job.dmp tables=hr.job_history content=data_only
-----------------------------------
Import: Release 11.2.0.4.0 - Production on Sun Jan 14 08:18:21 2024

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_TABLE_01":  system/******** directory=pump_dir dumpfile=hr_emp_dept_job.dmp tables=hr.job_history content=data_only
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "HR"."JOB_HISTORY"                          7.054 KB      10 rows
Job "SYSTEM"."SYS_IMPORT_TABLE_01" successfully completed at Sun Jan 14 08:18:23 2024 elapsed 0 00:00:01

● 3. 확인

[oracle@oracle ~]$ exit
exit

SQL> select count(*) from hr.job_history;

  COUNT(*)
----------
        10

▶ meta data only

● 1. 테이블 삭제

SQL> drop table hr.job_history purge;

Table dropped.

● 2. 테이블 구조만 가져오겠다
-> content=metadata_only

SQL> !
[oracle@oracle ~]$ impdp system/oracle directory=pump_dir dumpfile=hr_emp_dept_job.dmp tables=hr.job_history content=metadata_only
--------------------
Import: Release 11.2.0.4.0 - Production on Sun Jan 14 08:39:07 2024

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_TABLE_01":  system/******** directory=pump_dir dumpfile=hr_emp_dept_job.dmp tables=hr.job_history content=metadata_only
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type TABLE_EXPORT/TABLE/COMMENT
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "SYSTEM"."SYS_IMPORT_TABLE_01" successfully completed at Sun Jan 14 08:39:09 2024 elapsed 0 00:00:01

● 3. 확인

[oracle@oracle ~]$ exit
exit

SQL> select * from hr.job_history;

no rows selected

SQL> desc hr.job_history;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 EMPLOYEE_ID                               NOT NULL NUMBER(6)
 START_DATE                                NOT NULL DATE
 END_DATE                                  NOT NULL DATE
 JOB_ID                                    NOT NULL VARCHAR2(10)
 DEPARTMENT_ID                                      NUMBER(4)
  • 휴지통비우기, 테이블보기
SQL> purge recyclebin;

Recyclebin purged.

SQL> select * from tab;

hr의 모든 스키마 export

  • hr의 모든 스키마 export pump
    ( exclude 쓰면 어떤것을 제외하고 export )
    : 리눅스에서는 문자앞에 \ 을 써야한다.
[oracle@oracle ~]$ expdp system/oracle directory=pump_dir dumpfile=hr_schema.dmp schemas=hr exclude=table:\"=\'EXCEPTIONS\'\"
  • hr의 모든 스키마 export pump
    ( exclude 쓰면 어떤것을 제외하고 export
    in 을 써서 여러개 )
[oracle@oracle ~]$ expdp system/oracle directory=pump_dir dumpfile=hr_e_j_schema.dmp schemas=hr exclude=table:\"in \(\'EXCEPTIONS\', \'JOBS\'\)\"
  • hr의 테이블만 export pump
[oracle@oracle ~]$ expdp system/oracle directory=pump_dir dumpfile=hr_table.dmp schemas=hr include=table

hr 테이블 expdp 한것을 새로운 유저한테 impdp

● 1. 유저 생성

create user henry identified by oracle default tablespace users quota 10m on users;
  • create session 권한 부여
grant create session to henry;
  • HR 유저의의 테이블,테이블스페이스 확인
SQL> select table_name, tablespace_name from dba_tables where owner ='HR';

TABLE_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------
JOB_HISTORY                    EXAMPLE
EMPLOYEES                      EXAMPLE
JOBS                           EXAMPLE
DEPARTMENTS                    EXAMPLE
LOCATIONS                      EXAMPLE
REGIONS                        EXAMPLE
DEPT                           USERS
TEST1                          USERS
TEST2                          USERS
TEST_20240122                  USERS
AUDIT_EMP                      USERS

TABLE_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------
FGA_LOG                        USERS
TEST                           USERS
EMP_BEFORE_2005                USERS
EXCEPTIONS                     USERS
EMP_SAL                        USERS
EMP2005                        USERS
EMP_NEW                        USERS
COUNTRIES

● 2. import

hr의 스키마로 export받은것을
hr을 henry로 사용자 변경
EXAMPLE 테이블스페이스를 USERS로 변경

impdp system/oracle directory=pump_dir dumpfile=hr_table.dmp remap_schema='hr':'henry' remap_tablespace='EXAMPLE':'USERS'

● 3. 확인

SQL> select table_name, tablespace_name from dba_tables where owner ='HENRY';

TABLE_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------
EMPLOYEES                      USERS
JOBS                           USERS
DEPARTMENTS                    USERS
LOCATIONS                      USERS
REGIONS                        USERS
JOB_HISTORY                    USERS
DEPT                           USERS
AUDIT_EMP                      USERS
FGA_LOG                        USERS
EXCEPTIONS                     USERS
EMP_SAL                        USERS

TABLE_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------
TEST                           USERS
EMP_BEFORE_2005                USERS
EMP2005                        USERS
EMP_NEW                        USERS
TEST1                          USERS
TEST2                          USERS
TEST_20240122                  USERS
COUNTRIES

19 rows selected.

hr 테이블애서 조건절을 써서 특정 데이터만 expdp 한것을 새로운 테이블에 impdp

● 1. expdp : 특정 row만 (where절)

[oracle@oracle ~]$ expdp system/oracle directory=pump_dir dumpfile=hr_50_st_man.dmp tables=hr.employees query=hr.employees:\"where department_id \= 50 and job_id\=\'ST_MAN\'\"

● 2. 테이블 생성

  • 구조만 생성
create table hr.emp_50_stman as select * from hr.employees where 1=2;

● 3. impdp

[oracle@oracle ~]$ impdp system/oracle directory=pump_dir dumpfile=hr_50_st_man.dmp remap_table=employees:emp_50_stman content=data_only

서로 다른 DB에 이관작업 (db링크, expdp, impdp)

● 1. sqlplus XE DB로 접속

C:\Users\itwill>sqlplus system/oracle@XE

SQL*Plus: Release 11.2.0.2.0 Production on 화 1월 23 14:04:23 2024

Copyright (c) 1982, 2014, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
  • user 확인
SQL> show user
USER is "SYSTEM"
  • db 확인
SQL> select name from v$database;

NAME
------------------
XE

● 2. DB링크 생성

  • public DB링크 생성
SQL> create public database link ora11g_link connect to system identified by oracle using 'ora11g';
Database link created.
  • public DB링크 삭제
drop public database link ORA11G_LINL;
  • db링크 확인
SQL> select * from dba_db_links;
  • 디렉토리 확인
SQL> select * from dba_directories;
---
SQL> select * from dba_directories
  2  where directory_name = 'DATA_PUMP_DIR';

OWNER
------------------------------------------------------------
DIRECTORY_NAME
------------------------------------------------------------
DIRECTORY_PATH
--------------------------------------------------------------------------------
SYS
DATA_PUMP_DIR
C:\oraclexe\app\oracle/admin/xe/dpdump/
  • host : cmd창에서 OS로 나갈때
SQL> host
Microsoft Windows [Version 10.0.22621.3007]
(c) Microsoft Corporation. All rights reserved.

● 3. expdp : db링크를 이용해서 

C:\Users\itwill>expdp system/oracle directory=data_pump_dir dumpfile=hr_.dmp schemas=hr network_link=ora11g_link
---------
Export: Release 11.2.0.2.0 - Production on 화 1월 23 14:13:56 2024

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

Connected to: Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
FLASHBACK automatically enabled to preserve database integrity.
  • tnsnames.ora 파일 확인하기
    : 없으면 추가해주기 (db링크학습)
XE =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = DESKTOP-6FUPLFQ)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = XE)
    )
  )
ora11g =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.104)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ora11g)
    )
  )

● 4. import

  • 테이블 드랍후 조회하여 없는것 확인
C:\Users\itwill>sqlplus hr/hr

SQL*Plus: Release 11.2.0.2.0 Production on 화 1월 23 14:52:20 2024

Copyright (c) 1982, 2014, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production

SQL> show user
USER is "HR"
SQL> select * from tab;
SQL> select table_name, tablespace_name from user_tables;

SQL> drop table hr.job_history purge;

Table dropped.

SQL> select * from hr.job_history;
select * from hr.job_history
                 *
ERROR at line 1:
ORA-00942: table or view does not exist
  • import 작업
    : remap 해줘야하고, grant는 제외시켰다.
    : example 테이블스페이스는 없으므로 users로 remap_tablespace 해줬다.
C:\Users\itwill>impdp system/oracle directory=data_pump_dir dumpfile=hr.dmp tables=hr.job_history remap_tablespace=example:users exclude=grant
  • 확인
SQL> select * from hr.job_history;

EMPLOYEE_ID START_DA END_DATE JOB_ID               DEPARTMENT_ID
----------- -------- -------- -------------------- -------------
        102 01/01/13 06/07/24 IT_PROG                         60
        101 97/09/21 01/10/27 AC_ACCOUNT                     110
        101 01/10/28 05/03/15 AC_MGR                         110
        201 04/02/17 07/12/19 MK_REP                          20
        114 06/03/24 07/12/31 ST_CLERK                        50
        122 07/01/01 07/12/31 ST_CLERK                        50
        200 95/09/17 01/06/17 AD_ASST                         90
        176 06/03/24 06/12/31 SA_REP                          80
        176 07/01/01 07/12/31 SA_MAN                          80
        200 02/07/01 06/12/31 AC_ACCOUNT                      90

10 rows selected.

서로다른 db에서 db링크를 통해 export한 유저이름이 다른경우 import

-> remap_schema

● 1. 작업

  • db 확인
SQL> select name from v$database;

NAME
------------------
XE
  • 유저생성후 접근권한주기
SQL> create user james identified by oracle default tablespace users quota unlimited on users;

User created.

SQL> grant create session to james;

Grant succeeded.

SQL> host
Microsoft Windows [Version 10.0.22621.3007]
(c) Microsoft Corporation. All rights reserved.

● 2. import
: remap_schema = hr:james
hr에서 james로 변경해준다.

C:\oraclexe\app\oracle>impdp system/oracle directory=data_pump_dir dumpfile=hr.dmp remap_schema=hr:james remap_tablespace=example:users exclude=grant

● 3. 확인

SQL> conn james/oracle
Connected.
SQL> select * from tab;

서로 다른 DB링크를 통해서 export 없이 바로 import

db링크를 이용하면 export 없이 import 가능하다.

● 1. 가져올 data의 유저명과 같게 유저 생성

SQL> select name from v$database;

NAME
------------------
XE
SQL> create user henry identified by oracle default tablespace users quota unlimited on users;

User created.

SQL> grant create session to henry;

Grant succeeded.

SQL> host
Microsoft Windows [Version 10.0.22621.3007]
(c) Microsoft Corporation. All rights reserved.

● 2. import
: 가져올 이름이 다르면 remap_schema 로 변경해주면 된다

C:\oraclexe\app\oracle>impdp system/oracle network_link=ora11g_link schemas=henry exclude=grant,trigger

SQLPLUS 켰을때 무슨 DB로 접속했는지 알려주는것

SET sqlprompt "_USER'@'_CONNECT_IDENTIFIER> "

위치 :
/u01/app/oracle/product/11.2.0.4/db_1/sqlplus/admin/glogin.sql
C:\oraclexe\app\oracle\product\11.2.0\server\sqlplus\admin

profile
YoonSeo Park
DB 공부를 하고 있는 사람입니다. 글을 읽어주셔서 감사하고 더 좋은 글을 쓰기 위해 노력하겠습니다 :)
이전 포스트

Backup-Recovery 모든 시나리오 User Managed

다음 포스트

RMAN backup, recovery, flashback

0개의 댓글