[B&R] [Clone DB] Drop table 복구

OS : CentOS Linux release 7.9.2009 (Core)

DB : Oracle Database 11.2.0.4

• 기존 DB SID : TEST11
• 기존 DB 데이터 파일 경로 : /oracle/app/oracle/oradata/TEST11/
• Clone DB SID : hot
• Clone DB 복구 경로 : /data/backuptest

시나리오

• 복사 대상 : datafile (sysaux01.dbf system01.dbf undotbs01.dbf users01.dbf)

환경 구성

아카이브 모드 확인

SYS>archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     155
Next log sequence to archive   157
Current log sequence           157

 

Hotbackup 확인

SYS>alter session set nls_date_format = 'yyyy-mm-dd:hh24:mi:ss';
select ' not have hotbackup '  from dual
where (select count(*) from dba_data_files) = (select count(*) from v$backup where change#=0);
select *  from v$backup
where (select count(*) from dba_data_files) != (select count(*) from v$backup where change#=0);
tti off
set lines 140 pages 100
col KEY          for 9,999
col INPUT_TYPE   for a12
col STATUS       for a10
col JOB_TIME     for a8
col RATIO        for 999
col INPUT_SEC    for a10
col OUTPUT_SEC   for a10
col start_time   for a15
col end_time   for a15

Session altered.

SYS>  2
no rows selected

SYS>  2
     FILE# STATUS                CHANGE# TIME
---------- ------------------ ---------- -------------------
         1 NOT ACTIVE           10738198 2022-09-15:16:25:47
         2 NOT ACTIVE           10738203 2022-09-15:16:25:47
         3 NOT ACTIVE           10738208 2022-09-15:16:25:47
         4 NOT ACTIVE           10738214 2022-09-15:16:25:47
         5 NOT ACTIVE           10738219 2022-09-15:16:25:48
         6 NOT ACTIVE           10738225 2022-09-15:16:25:48

6 rows selected.

테이블이 속한 테이블스페이스 확인

SYS>select table_name, tablespace_name from dba_tables where table_name like '%TEST11%'; -- %대문자%

TABLE_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------
TEST11                          USERS

scott 계정의 ‘test11’ table 확인 후 삭제 (table 삭제 시간 2022/09/15:17:20:00 )

SCOTT>select count(*) from test11;

  COUNT(*)
----------
  18180000

SCOTT>drop table test11;
Table dropped.

SCOTT>commit;
Commit complete.

SCOTT>select count(*) from test11;
select count(*) from test11
                     *
ERROR at line 1:
ORA-00942: table or view does not exist

복구

Clone DB 필요한 경로 생성

[test11]dbtest:/oracle/app/oracle/product/11.2.0.4> :mkdir -p /data/backuptest

hotbackup 받은 파일 복사

[test11]dbtest:/data/hotbackup> :cp users01.dbf system01.dbf sysaux01.dbf undotbs01.dbf /data/backuptest/

[test11]dbtest:/data/backuptest> :ls
sysaux01.dbf  system01.dbf  undotbs01.dbf  users01.dbf

아카이브 로그 떨어뜨리기

SQL> alter system archive log current;

System altered.

controlfile 생성문 backup

SYS>alter database backup controlfile to trace as '/oracle/truin/re.sql';

Database altered.

pfile 복사 및 변경

[test11]dbtest:/oracle/app/oracle/product/11.2.0.4/dbs> :cp inittest11.ora inithot.ora

[test11]dbtest:/oracle/app/oracle/product/11.2.0.4/dbs> :vi inithot.ora
test11.__db_cache_size=1056964608
test11.__java_pool_size=16777216
test11.__large_pool_size=33554432
test11.__oracle_base='/oracle/app/oracle'#ORACLE_BASE set from environment
test11.__pga_aggregate_target=520093696
test11.__sga_target=1526726656
test11.__shared_io_pool_size=0
test11.__shared_pool_size=369098752
test11.__streams_pool_size=33554432
*.audit_file_dest=**'/oracle/app/oracle/admin/hot/adump'** -- 디렉토리 생성 필수
*.audit_trail='DB'
*.cluster_database=FALSE
*.compatible='11.2.0.1.0'
*.control_files=**'/data/backuptest/control01.ctl'** -- 복구용으로 파일 1개만 생성
*.db_block_size=8192
*.db_domain=''
*.db_name=**'hot'**
*.db_unique_name=**'hot'**
*.diagnostic_dest='/oracle/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=test11XDB)'
*.log_archive_format='%t_%s_%r.dbf'
*.nls_language='AMERICAN'
*.nls_territory='AMERICA'
*.open_cursors=300
*.pga_aggregate_target=508559360
*.processes=320
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=1526726656
*.undo_tablespace='UNDOTBS1'

control file 생성문 수정

CREATE CONTROLFILE SET DATABASE "hot" RESETLOGS  ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 '/data/backuptest/redo01.log'  SIZE 50M BLOCKSIZE 512,
  GROUP 2 '/data/backuptest/redo02.log'  SIZE 50M BLOCKSIZE 512,
  GROUP 3 '/data/backuptest/redo03.log'  SIZE 50M BLOCKSIZE 512 --**마지막 파일 작성 후 ‘,’ 꼭 지울 것**
-- STANDBY LOGFILE
DATAFILE
  '/data/backuptest/system01.dbf',
  '/data/backuptest/sysaux01.dbf',
  '/data/backuptest/undotbs01.dbf',
  '/data/backuptest/users01.dbf'--**마지막 파일 작성 후 ‘,’ 꼭 지울 것**
CHARACTER SET AL32UTF8

복구할 sid로 변경 후 nomount

export ORACLE_SID=hot

SYS>startup nomount
ORACLE instance started.

Total System Global Area 1519898624 bytes
Fixed Size                  2253464 bytes
Variable Size             503319912 bytes
Database Buffers         1006632960 bytes
Redo Buffers                7692288 bytes
Database mounted.

re.sql 파일 실행

SYS>@re.sql

Control file created.

nls_date_format 형식 지정

SYS>alter session set nls_date_format='YYYY/MM/DD:HH:MI:SS';

Session altered.

recover 시점 복구 수행 (테이블 삭제 시간 이전)

SYS>recover database using backup controlfile until time '2022/09/15:17:00:00';
ORA-00279: change 10738198 generated at 09/15/2022 16:25:47 needed for thread 1
ORA-00289: suggestion :
/oracle/app/oracle/product/11.2.0.4/dbs/arch1_157_1111925132.dbf
ORA-00280: change 10738198 for thread 1 is in sequence #157

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00308: cannot open archived log
'/oracle/app/oracle/product/11.2.0.4/dbs/arch1_157_1111925132.dbf'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

resetlogs open

SYS>alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01195: online backup of file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/data/backuptest/system01.dbf'

-- 위 오류 발생 시 pfile에 해당 히든파라미터 추가
-- redo log의 한계범위 내에서 데이터파일의 SCN이 틀어져 있을 경우 또는 recover시에 archive를 찾지 못해 fail났을 경우 사용

[test11]dbtest:/oracle/app/oracle/product/11.2.0.4/dbs> :vi inithot.ora
_allow_resetlogs_corruption = true --추가

-- shutdown immdeiate 후 mount실행

[hot]dbtest:/oracle/app/oracle/product/11.2.0.4/dbs> :sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Thu Sep 15 19:55:46 2022

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

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

SYS>shutdown immediate
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
SYS>startup mount
ORACLE instance started.

Total System Global Area 1519898624 bytes
Fixed Size                  2253464 bytes
Variable Size             503319912 bytes
Database Buffers         1006632960 bytes
Redo Buffers                7692288 bytes
Database mounted.

SYS>alter database open resetlogs;

Database altered.

Drop 테이블 확인

SYS>conn scott/1234
Connected.
SCOTT>select count(*)
Message from syslogd@dbtest at Sep 15 19:56:51 ...
 kernel:do_IRQ: 3.101 No irq handler for vector (irq -1)
 f
  2
SCOTT>select count(*) from test11;

  COUNT(*)
----------
  18180000

datapump

hot계정에서 expdp 실행

expdp scott/1234 directory=datapump dumpfile=hottest.dmp logfile=hottest.log tables=test11

test11계정에서 impdp 실행

impdp scott/1234 directory=datapump dumpfile=hottest.dmp logfile=hottest_imp.log;

기존 DB에서 테이블 확인

SCOTT>select count(*) from test11;

  COUNT(*)
----------
  18180000