Time-Based Recovery(clone DB) using log miner(과정 생략)

supplemental_log_data 확인

SELECT supplemental_log_data_min FROM v$database;

SUPPLEME
--------
NO

 

supplemental_log_data 활성화하기

ALTER DATABASE add supplemental log data;

Database altered.

 

 확인

SELECT supplemental_log_data_min FROM v$database;

SUPPLEME
--------
YES

 

실수로 유저를 삭제했다!

DROP USER hr cascade;

 

로그 확인

SELECT a.group#, b.sequence#, a.member, b.bytes/1024/1024 MB, b.archived, b.status, b.FIRST_CHANGE#, b.NEXT_CHANGE#
FROM v$logfile a, v$log b
WHERE a.group# = b.group#
00:26:28   4  ORDER BY 1,2;

    GROUP#  SEQUENCE# MEMBER                                                MB ARC STATUS           FIRST_CHANGE# NEXT_CHANGE#
---------- ---------- --------------------------------------------- ---------- --- ---------------- ------------- ------------
         1         56 /u01/app/oracle/oradata/ORA19C/redo01.log            200 YES ACTIVE                 4196330      4196337
         2         57 /u01/app/oracle/oradata/ORA19C/redo02.log            200 NO  CURRENT                4196337   1.8447E+19
         3         55 /u01/app/oracle/oradata/ORA19C/redo03.log            200 YES ACTIVE                 4177529      4196330

 

# log miner로 로그 정보 확인하기

 

분석파일 지정

execute dbms_logmnr.add_logfile(logfilename=>'/u01/app/oracle/oradata/ORA19C/redo02.log',options=>dbms_logmnr.new)

 

로그마이너 분석 시작

execute dbms_logmnr.start_logmnr(options=>dbms_logmnr.dict_from_online_catalog)

 

유저 drop 시간 찾아보기

SELECT to_char(timestamp,'yyyy-mm-dd hh24:mi:ss'),operation, sql_redo, sql_undo FROM v$logmnr_contents WHERE seg_owner='HR';

...
2024-09-10 00:27:01 DDL    DROP USER hr cascade;
(DROP하기 전에 객체를 지운 시간부터 찾아야 해)

> 2024-09-10 00:26:00으로 time based recovery 할 시간 지정

 

 

-현재 리두확인

SELECT a.group#, b.sequence#, a.member, b.bytes/1024/1024 MB, b.archived, b.status, b.FIRST_CHANGE#, b.NEXT_CHANGE#
FROM v$logfile a, v$log b
WHERE a.group# = b.group#
00:43:37   4  ORDER BY 1,2;

    GROUP#  SEQUENCE# MEMBER                                                MB ARC STATUS           FIRST_CHANGE# NEXT_CHANGE#
---------- ---------- --------------------------------------------- ---------- --- ---------------- ------------- ------------
         1         56 /u01/app/oracle/oradata/ORA19C/redo01.log            200 YES INACTIVE               4196330      4196337
         2         57 /u01/app/oracle/oradata/ORA19C/redo02.log            200 YES ACTIVE                 4196337      4200524
         3         58 /u01/app/oracle/oradata/ORA19C/redo03.log            200 NO  CURRENT                4200524   1.8447E+19

 

커런트한 리두로그 아카이브로 받기

ALTER SYSTEM archive log current;

 

아카이브 디렉토리 확인

SYS@ORA19C>! ls arch*
arch1:
arch_1_55_1175606320.arc  arch_1_56_1175606320.arc  arch_1_57_1175606320.arc

arch2:
arch_1_55_1175606320.arc  arch_1_56_1175606320.arc  arch_1_57_1175606320.arc

 

초기 파라미터 만들기

CREATE pfile='/home/oracle/clone/initclone.ora' from spfile;

 

# clone DB 컨트롤 파일 생성

디렉토리 만들기

[oracle@oracle19c ~]$ mkdir clome

운영DB모습처럼 생성해야하니까 control file trace뜨기

ALTER DATABASE BACKUP CONTROLFILE TO TRACE AS '/home/oracle/clone/create_control.spl';

 

close 백업본에서 dbf만 clone 파일에 카피

아카이브 clone파일에 복사

! cp -v /home/oracle/20240905/close/*.dbf /home/oracle/clone/
cp -v arch1/* /home/oracle/clone/

>운영DB가 운영중이기때문에 리두로그를 사용할 수 없다

 

- initfile수정하기

[oracle@oracle19c clone]$ vi initclone.ora

*.compatible='19.0.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='UNDOTBS1'

 

--디폴트 DB변경하기

[oracle@oracle19c ~]$ . oraenv
ORACLE_SID = [ORA19C] ? CLONE
ORACLE_HOME = [/home/oracle] ? /u01/app/oracle/product/19.3.0/dbhome_1
The Oracle base remains unchanged with value /u01/app/oracle

 

## CLONE DB 접속하기##

[oracle@oracle19c ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Tue Sep 10 16:02:36 2024
Version 19.3.0.0.0

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

Connected to an idle instance.

16:02:37 SYS@CLONE>--CLONE DB로 접속

 

pfile로 startup nomount

16:02:37 SYS@CLONE>startup pfile=/home/oracle/clone/initclone.ora nomount
ORACLE instance started.

Total System Global Area  268434280 bytes
Fixed Size                  8895336 bytes
Variable Size             201326592 bytes
Database Buffers           50331648 bytes
Redo Buffers                7880704 bytes

 

-컨트롤파일 생성하기

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 200M BLOCKSIZE 512,
  GROUP 2 '/home/oracle/clone/redo02.log'  SIZE 200M BLOCKSIZE 512,
  GROUP 3 '/home/oracle/clone/redo03.log'  SIZE 200M BLOCKSIZE 512
DATAFILE
  '/home/oracle/clone/system01.dbf',
  '/home/oracle/clone/sysaux01.dbf',
  '/home/oracle/clone/undotbs01.dbf',
  '/home/oracle/clone/example01.dbf',
  '/home/oracle/clone/users01.dbf'
CHARACTER SET AL32UTF8
16:07:25  18  ;

 

--temp file 지정하기

ALTER TABLESPACE temp ADD TEMPFILE '/home/oracle/clone/temp01.dbf' REUSE;

 

시간 포멧 변경

ALTER SESSION SET nls_date_format='yyyy-mm-dd hh24:mi:ss';

 

Time-Based Recovery 시작

RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL TIME'2024-09-10 00:26:00';
ORA-00279: change 4182647 generated at 09/05/2024 09:54:02 needed for thread 1
ORA-00289: suggestion : /home/oracle/clone/arch_1_55_1175606320.arc
ORA-00280: change 4182647 for thread 1 is in sequence #55

16:10:08 Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
AUTO
Log applied.
Media recovery complete.

16:10:35 SYS@CLONE>ALTER DATABASE OPEN RESETLOGS;

Database altered.

 

SELECT * FROM dba_users WHERE username='HR';

>cloneDB에 존재함

 

--export받기

exp system/oracle file=clone_hr.dmp owner=hr direct=y

 

log miner로 메타정보 찾기(temp DB할 때 찾았으니 그 정보를 사용함)

 

##메타정보 이용하여 운영 DB에 HR 생성하기

[ 운영 DB SESSION ]

--유저생성   
CREATE USER "HR" IDENTIFIED BY hr
      DEFAULT TABLESPACE "SYSAUX"
      TEMPORARY TABLESPACE "TEMP";

--권한부여
  GRANT CREATE SESSION TO "HR";
  GRANT ALTER SESSION TO "HR";
  GRANT UNLIMITED TABLESPACE TO "HR";
  GRANT CREATE SYNONYM TO "HR";
  GRANT CREATE VIEW TO "HR";
  GRANT CREATE SEQUENCE TO "HR";
  GRANT CREATE DATABASE LINK TO "HR";
  GRANT CREATE PROCEDURE TO "HR";

--객체권한 부여
GRANT EXECUTE ON "SYS"."DBMS_STATS" TO "HR";

--쿼터
ALTER USER "HR" QUOTA UNLIMITED ON "SYSAUX";

--롤 
   GRANT "CONNECT" TO "HR";
   GRANT "RESOURCE" TO "HR";

 

clone DB에서 만들어진 dump 파일로 운영DB에 import하기

! imp system/oracle file=hr_owner.dmp fromuser=hr

 

생성된 것 확인

SELECT * FROM dba_users WHERE username='HR';

 

 

결론 : 운영중에 test DB 만들 때가 있다. cloneDB만드는 방법으로 만들면 돼