2012.02.15 drop user복구(DBlink, CloneDB이용), logmnr에서 삭제된 유저의 쿼리찾기관련(글의 마지막참조)

Oracle/백업&복구

2012.02.15 drop user복구(DBlink, CloneDB이용), logmnr에서 삭제된 유저의 쿼리찾기관련(글의 마지막참조)

에몽이ㅋ 2012. 2. 15. 22:51

DB link사용해서 논리적인 장애복구하기

시나리오 : 백업 후, DB운영하다가 실수로 user를 drop해버렸다. (drop user cascade)
1. DB는 계속 운영되어야 하고,
2. cloneDB를 만들자니 하드용량이 모자라서 --> 다른 console에다가 cloneDB를 설치하고 drop한 user를 복구
(실습환경은 같은 PC에서 작업하겠습니다. 클론DB이름 : clone, 디렉토리 : /backup/clone/)
3. DB link를 이용해 바로 연결해서 drop 한 user를 복구

고찰 :
1. oracle network를 이용해 둘 간의 연결을 만들 수 있어야 한다.
2. 복구 한 후 해당 user에게 어떤 table들이 있었는지, 어떤 상태였는지 정확하게 알 수 있어야 한다.
3. 해당 user에 어떤 권한이 있었는지도 알면 금상첨화

참고포스팅 :
CLONEDB 생성하는 법 http://gyh214.tistory.com/92
USER의 상태를 볼 수 있는 딕셔너리 : http://gyh214.tistory.com/83 내용참조

해봅시다!

1. 밑밥작업 및 논리적장애발생(user drop)

1-1. 백업 및 유저생성을 위한 tbs들 생성, 유저생성

SQL> !sh ~/main_backup.sh
set begin backup mode~~
real    0m0.898s
user    0m0.031s
sys     0m0.046

send begin backup mode--
start file copy.…
real    0m36.318s
user    0m0.144s
sys     0m5.777s
end file copy~

set end backup mode~~
real    0m1.933s
user    0m0.025s
sys     0m0.059s

complete hot backup~!!

SQL> create tablespace koo  
2  datafile '/home/oracle/oradata/testdb/koo01.dbf' size 10M autoextend on;

SQL> create temporary tablespace koo_temp  
2  tempfile '/home/oracle/oradata/testdb/koo_temp.dbf' size 10M;

Tablespace created.

SQL> create user koo
  2  identified by mong_1  
  3  default tablespace koo
  4  temporary tablespace koo_temp;
User created.

SQL> grant connect, resource to koo;
Grant succeeded.

1-2. koo user에 table들을 생성(3개 생성하겠습니다)

SQL> conn koo/mong_1
Connected.

SQL> create table me
  2  (no number, address varchar(10), habit varchar(20));
Table created.

SQL> insert into me values(1, 'SUWON', 'Guitar');
SQL> insert into me values(2, 'DAEGU', 'GAMES');
1 row created.
SQL> commit;
Commit complete.

_______________________________________
SQL> create table you
  2  (no number, address varchar(10), nick varchar(10));
Table created.

SQL> insert into you values(1, 'Taiw', 'Any');
SQL> insert into you values(2, 'Anya', 'Study');
1 row created.
SQL> commit;
Commit complete.

_______________________________________
SQL> create table prof
  2  (no number, name varchar(10), height number(3));
Table created.

SQL> insert into prof values(1, 'Seo', 180);
SQL> insert into prof values(2, 'Park', 157);
1 row created
SQL> commit;
Commit complete.

1-3. 한참 운영하다가 실수로 drop user

SQL> conn / as sysdba
Connected.SQL>

SQL> alter system switch logfile;
System altered.

SQL> /
System altered

....생략...

SQL> /
System altered.
SQL> /
System altered.

SQL> drop user koo cascade;

User dropped.

SQL> select * from koo.me;
*ERROR at line 1:ORA-00942: table or view does not exist

SQL> select * from koo.you;
  *ERROR at line 1:ORA-00942: table or view does not exist

SQL> select * from koo.prof;
*ERROR at line 1:ORA-00942: table or view does not exist

-----------------안돼!!!!!!!!!!!!!!!!!----------------

2. cloneDB생성 후 복구시도

2-1. 백업파일을 클론디스크로 복사,

파라미터파일생성, 컨트롤파일재생성 스크립트생성 후 내용편집

[oracle@server15 dbs]$ cp -v /backup/open/2012-02-15-22-06-50/*.dbf /backup/clone/

`/backup/open/2012-02-15-22-06-50/example01.dbf' -> `/backup/clone/example01.dbf'

`/backup/open/2012-02-15-22-06-50/sysaux01.dbf' -> `/backup/clone/sysaux01.dbf'

`/backup/open/2012-02-15-22-06-50/system01.dbf' -> `/backup/clone/system01.dbf'

`/backup/open/2012-02-15-22-06-50/test01.dbf' -> `/backup/clone/test01.dbf'

`/backup/open/2012-02-15-22-06-50/undo01.dbf' -> `/backup/clone/undo01.dbf'

`/backup/open/2012-02-15-22-06-50/users01.dbf' -> `/backup/clone/users01.dbf'

vi initclone.ora

*.db_name='clone'

*.control_files='/backup/clone/control01.ctl'

SQL> alter database backup controlfile to trace as '/backup/clone/ctl.sql';

Database altered.

vi /backup/clone/ctl.sql

STARTUP NOMOUNT

CREATE CONTROLFILE SET DATABASE "CLONE" RESETLOGS ARCHIVELOG

MAXLOGFILES 16

MAXLOGMEMBERS 3

MAXDATAFILES 100

MAXINSTANCES 8

MAXLOGHISTORY 1314

LOGFILE

GROUP 1 (

'/backup/clone/redo01_b.log'

) SIZE 10M,

GROUP 2 (

'/backup/clone/redo02_b.log'

) SIZE 10M,

GROUP 3 (

'/backup/clone/redo03_b.log'

) SIZE 10M

DATAFILE

'/backup/clone/system01.dbf',

'/backup/clone/undo01.dbf',

'/backup/clone/sysaux01.dbf',

'/backup/clone/users01.dbf',

'/backup/clone/example01.dbf'

CHARACTER SET KO16MSWIN949

(*주의사항 : 백업 이후에 koo tablespace를 생성했으므로, DATAFILE에서 koo01.dbf부분은 빼세요)

2-2. clonedb로 접속 후 controlfile재생성하며 mount까지 올라라기

SQL> @/backup/clone/ctl
ORA-01081: cannot start already-running ORACLE - shut it down first
Control file created.

2-3. clone 에서 복구하기(복구시간은 원db에서 logmnr로 찾은 시간을 이용하겠습니다.)

SQL> recover database until time '2012-02-15:22:17:40' using backup controlfile;
ORA-00279: change 891894 generated at 02/15/2012 22:06:50 needed for thread 1ORA-00289:
 suggestion : /backup/arc/1_1_775346778.dbfORA-00280: change 891894 for thread 1 is in sequence #1

Specify log: {=suggested | filename | AUTO | CANCEL}
auto

ORA-00283: recovery session canceled due to errors
ORA-01244: unnamed datafile(s) added to control file by media recovery
ORA-01110: data file 6: '/home/oracle/oradata/testdb/koo01.dbf'
ORA-01112: media recovery not started

SQL> select a.name, b.name tbs_name from v$datafile a, v$tablespace b
  2  where a.ts# = b.ts#;

NAME-----------------------------
TBS_NAME------------------------------
/backup/clone/system01.dbf
SYSTEM

/backup/clone/sysaux01.dbf
SYSAUX

/backup/clone/users01.dbf
USERS

/backup/clone/example01.dbf
EXAMPLE

/home/oracle/product/10g/dbs/UNNAMED00006
KOO

/backup/clone/undo01.dbf
UNDO

6 rows selected.

SQL> alter database create datafile '/home/oracle/product/10g/dbs/UNNAMED00006'
  2  as '/backup/clone/koo01.dbf';
Database altered.

SQL> recover database until time '2012-02-15:22:17:40' using backup controlfile;
ORA-00279: change 892109 generated at 02/15/2012 22:08:58 needed for thread 1

ORA-00289: suggestion : /backup/arc/1_1_775346778.dbf
ORA-00280: change 892109 for thread 1 is in sequence #1

Specify log: {=suggested | filename | AUTO | CANCEL}
auto
     << 만약에 다른 디스크에 있다면, NFS로 연결하든 뭘 하든 해서
       원DB쪽의 archive file을 적어줘야합니다.
Log applied.
Media recovery complete.

SQL> alter database open resetlogs;

SQL> select * from koo.me;
        NO ADDRESS    HABIT
---------- ---------- --------------------
         1 SUWON      Guitar
         2 DAEGU      GAMES

SQL> select * from koo.you;
        NO ADDRESS    NICK
---------- ---------- ----------
         1 Taiw       Any
         2 Anya       Study

SQL> select * from koo.prof;
        NO NAME           HEIGHT
---------- ---------- ----------
         1 Seo               180
         2 Park              157<< 자료복구완료

2-4. 원db에 복구하기 위해서 koo user의 상태확인(clonedb에서 하세요)

SQL> select GRANTEE, GRANTED_ROLE from dba_role_privs
  2  where grantee='KOO';   << role 확인

GRANTEE                        GRANTED_ROLE
------------------------------ ------------------------------
KOO                            RESOURCE
KOO                            CONNECT

SQL> select username, DEFAULT_TABLESPACE, TEMPORARY_TABLESPACE from dba_users
  2  where username='KOO';  << 할당된 tbs들 확인

USERNAME                       DEFAULT_TABLESPACE             TEMPORARY_TABLESPACE
------------------------------ ------------------------------ ------------------------------
KOO                            KOO                            KOO_TEMP

SQL> select OWNER, TABLE_NAME, TABLESPACE_NAME from dba_tables
  2  where owner='KOO';   << koo는 어떤 table을 가지고 있는가 확인
OWNER                          TABLE_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------ ------------------------------
KOO                            ME                             KOO
KOO                            YOU                            KOO
KOO                            PROF                           KOO

3. DBlink 만들기http://gyh214.tistory.com/112 :DB Link 설명

3-1.oracle network 설정

($ORACLE_HOME/network/admin/ 아래에

cloneDB쪽에서는 listener.ora (DBlink에서는 Server)

원DB쪽에서는 tnsnames.ora (DBlink에서는 Client)

를 서로서로 맞게 생성 후 clonedb쪽에서는 listener start)

** clone에서 원DB로 자료를 넣어줄 것이므로, DBlink에서는 clone이 서버가 됩니다.

* 파일들이 없으면, netca를 실행시켜서 만들어주세요(http://blog.naver.com/neocp21c/140152325230 참고)

listener.ora의 SID_NAME에는 clone입력, 아래쪽 HOST=cloneDB의 IP

tnsnames.ora에서는 CONNECT_DATA아래 SID=clone입력 후

cloneDB쪽에서

$ lsnrctl 실행 후

stop 실행 했다가 start실행하세요.

3-2. 운영DB에서 clone으로 연결하는 link만들기(운영DB에서 실행시키세요)

SQL> select name from v$database;
NAME
---------
TESTDB

SQL> create database link clink
  2  connect to koo identified by mong_1
  3  using 'clone';Database link created.

--> 이제부터 clink로 연결해서 쓰면됩니다.

3-3. 운영DB에 유저생성(찾은 특징을 그대로 적용), 유저의 테이블들 CTAS로 넣어주기

SQL> create user koo
  2  identified by mong_1
  3  default tablespace koo  <<
  4  temporary tablespace koo_temp;  <<위에서 찾은 것들입니다.
User created.

SQL> grant connect,resource to koo;  <<위에서 찾은것입니다.
Grant succeeded.

SQL> create table koo.me
  2  as select * from me@clink;
Table created.

SQL> create table koo.you
  2  as select * from you@clink;
Table created.

SQL> create table koo.prof
  2  as select * from prof@clink;
Table created.

SQL> select * from koo.me;
        NO ADDRESS    HABIT
---------- ---------- --------------------
         1 SUWON      Guitar
         2 DAEGU      GAMES

SQL> select * from koo.you;
        NO ADDRESS    NICK
---------- ---------- ----------
         1 Taiw       Any
         2 Anya       Study

SQL> select * from koo.prof;
        NO NAME           HEIGHT
---------- ---------- ----------
         1 Seo               180
         2 Park              157<< 원래DB에 복구완료했습니다.

4. 마무리(cloneDB shutdown하기)

SQL> select name from v$database;
NAME
---------
CLONE

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

** 시나리오 해결하며 발생한 사항
logmnr 사용시 파일 분석후 v$logmnr_contents에서 쿼리를 찾아낼 떄, 만약 찾고자 하는 user가 drop이 된 상태에서
archive logfile, redo logfile을 등록해서 분석해보면 SEG_OWNER이나 USERNAME으로는 찾을 수가 없습니다.

--> 해당 user는 삭제되어 UNKNOWN으로 변경되어 archive 에 저장되어버립니다. 결국, user가 삭제된 상태에서의 해당user의 쿼리를 찾는 다는 것은 거의 불가능에 가깝습니다.

저작자표시 비영리 동일조건

'Oracle > 백업&복구' 카테고리의 다른 글

백업&복구 8번째(export, import) (0)	2012.02.18
Expdp 연습(datapump) ; tablespace export,import datapump 하기 (0)	2012.02.16
CLONEDB 만들어서 논리적인 장애 복구하기(No archive log mode) (0)	2012.02.15
Datapump 사전작업 (0)	2012.02.14
exp/imp 시 IMP-00013: only a DBA can import a file exported by another DBA 에러발생 (0)	2012.02.13

현재글2012.02.15 drop user복구(DBlink, CloneDB이용), logmnr에서 삭제된 유저의 쿼리찾기관련(글의 마지막참조)

에몽이's ORACLE!?

제 글이 도움되셨다면 덧글 남겨주고 가세요 ^^ 문의사항은 gyh214@naver.com <pre class="brush:sql"></pre>

Constraint, datapump, raw device, SQL, tnsnames.ora, Solaris, oui, Silent, oracle11g, EXCEPTIONS table, ASM, 리눅스, clone DB, oel, 노드추가, rman, RAC, flashback, oracle_network, listener.ora,

Today :
Yesterday :

에몽이's ORACLE!?