DataBase

27 02 2014

Oracle Flash Back을 이용한 데이타 복구

Flash Back Query 예제 10분전의 데이타를 복구합니다.

insert into dept

select * from dept

AS OF TIMESTAMP(SYSTIMESTAMP - INTERVAL '10' MINUTE)

where tutorid = '******';

insert into dept

select * from dept

AS OF TIMESTAMP(SYSTIMESTAMP - INTERVAL '10' MINUTE)

where tutorid = '******';

27 02 2014

Oracle 백업/복구 Import or Export

Haeng Ho Kang | DataBase, Oracle | 0

FULL Level Export  C:\>exp  userid=system/manager  file='C:\full.dmp'   full=y

User Level Export C:\>exp   userid=scott/tiger owner='scott'  file='C:\scott.dmp'

FULL Level Export C:\>exp userid=system/manager file='C:\full.dmp' full=y

User Level Export C:\>exp userid=scott/tiger owner='scott' file='C:\scott.dmp'

Table Level Export

      C:\>exp userid=system/manager file='C:exp.dmp' tables=(scott.EMP, scott.DEPT)

Table Level Export

C:\>exp userid=system/manager file='C:exp.dmp' tables=(scott.EMP, scott.DEPT)

=> 위와 같이 table의 schema(user)명까지 지정해야만 export가 성공합니다. C:\>exp userid=scott/tiger file=’C:\exp.dmp’ tables=(EMP, DEPT) log=exp.log => user가 자신의 table을 export할 때에는 schema 명을 지정할 필요 없습니다. FULL Level Import 예제1) 전체 데이터베이스가 IMPORT됩니다. (Full Level)

 C:\>imp userid=system/manager file='C:\full.dmp'  full=y

1	C:\>imp userid=system/manager file='C:\full.dmp' full=y

예제2) scott의 유저 IMPORT를 실행 합니다.(User Level)

C:\>imp userid=scott/tiger fromuser='scott' touser='scott' file='C:\scott.dmp'

1	C:\>imp userid=scott/tiger fromuser='scott' touser='scott' file='C:\scott.dmp'

예제3) […]

27 02 2014

Oracle Table Space 생성

Haeng Ho Kang | DataBase, Oracle | 0

9i에서는 svrmgrl이 없어졌습니다. 그래서 …

1. sqlplus를 DB에 접속하지 않고 실행시킨다.

    먼저 oracle계정으로 접속한뒤 $sqlplus /nolog

2. sysdba로 오라클에 접속한다.

SQL>connect 유저명/패스워드 as sysdba

( connect system/비밀번호  as sysdba)

create tablespace abcd_maindb datafile 'D:\web\NURICORE\database\abcd_maindb.dbf' size 300M default storage

(initial 1M                  next    1M

minextents 2                  maxextents unlimited                  pctincrease 0                  );

create tablespace abcd_indexdb datafile 'D:\web\NURICORE\database\abcd_indexdb.dbf' size 300M default storage

(initial 1M                  next    1M

minextents 2                  maxextents unlimited                  pctincrease 0                  );

create temporary tablespace "abcd_tempdb" tempfile 'D:\web\NURICORE\database\abcd_tempdb.dbf' size 50M extent management local uniform size 5M;

create user abcd identified by 비밀번호

default tablespace  abcd_maindb temporary tablespace "abcd_tempdb";

grant connect,resource to abcd

grant connect,resource,EXP_FULL_DATABASE,IMP_FULL_DATABASE to abcd

1. sqlplus를 DB에 접속하지 않고 실행시킨다.

먼저 oracle계정으로 접속한뒤 $sqlplus /nolog

2. sysdba로 오라클에 접속한다.

SQL>connect 유저명/패스워드 as sysdba

( connect system/비밀번호 as sysdba)

create tablespace abcd_maindb datafile 'D:\web\NURICORE\database\abcd_maindb.dbf' size 300M default storage

(initial 1M next 1M

minextents 2 maxextents unlimited pctincrease 0 );

create tablespace abcd_indexdb datafile 'D:\web\NURICORE\database\abcd_indexdb.dbf' size 300M default storage

(initial 1M next 1M

minextents 2 maxextents unlimited pctincrease 0 );

create temporary tablespace "abcd_tempdb" tempfile 'D:\web\NURICORE\database\abcd_tempdb.dbf' size 50M extent management local uniform size 5M;

create user abcd identified by 비밀번호

default tablespace abcd_maindb temporary tablespace "abcd_tempdb";

grant connect,resource to abcd

grant connect,resource,EXP_FULL_DATABASE,IMP_FULL_DATABASE to abcd