[DB기술노트79회] Recovering Tables (with Rman)

1.   기술노트 개요

00 사이트에서 개발자의 실수로 인한 데이터가 조건절을 부여 실수로 잘못 업데이트가 되는 상황이 발생했습니다.

하지만 하루가 지나서야 데이터가 잘못 변경된 사실을 알았기 때문에 Flashback 기능을 쓸 수도 없고, 논리적인 백업 또한 수행되고 있지 않았습니다. 중요한 정보이므로 해당 테이블을 하루 전 시점으로 복구해야 합니다. 현재 Backup 은 Rman Backup 뿐입니다. 

이런 경우 Rman Backup 을 이용한 하루 전 일자로 Clone DB를 구성하는 방법이 있습니다.

( 대상 Table 의 판별 (Datafile, Tablespace), ControlFile 재생성, 구성 후 복구 작업 등 )

 

이러한 작업 절차를 Manual 하게 수행했다면 ( 10g , 11g )

12c 에서는 Recovering Tables NF ( New Feature ) 기능을 사용하여 Automatic 하게 복구할 수 있는 방법을 알아보겠습니다.

 

Recovering Tables NF 에 대해 Oracle Docs 기반으로 이론을 설명하고 해당 기능에 대해 실습하는 순으로 진행하겠습니다.

 

2.    Recovering Tables

테이블 및 테이블 파티션을 지정된 특정 시점으로 복구하는 방법에 대해 설명합니다.

 

Purpose

RMAN 을 사용하면 하나 이상의 테이블 또는 테이블 파티션을 지정된 시점으로 복구 할 수 있으며 나머지 데이터베이스 Object 에는 영향을 미치지 않습니다. 이전에 생성된 RMAN 백업을 사용하여 테이블 및 테이블 파티션을 지정된 특정 시점으로 복구할 수 있습니다.

 

l  적은 수의 테이블을 특정 시점으로 복구해야 할 경우. (이 상황에서 TSPITR 은 테이블 공간의 모든 개체를 지정된 시점으로 이동하기 때문에 가장 효과적인 솔루션이 아닙니다.)

l  논리적으로 손상되었거나 삭제 및 제거된 테이블을 복구해야 할 경우.

l  원하는 특정 시점이 사용 가능한 Undo 보다 오래되어 플래시백 테이블을 사용할 수 없는 경우.

l  DDL 조작 후 테이블 구조를 수정 한 후에 유실된 데이터를 복구하려고 하는 경우.

          원하는 시점과 현재 시간 사이의 테이블에서 DDL 이 실행되었으므로 플래시백 테이블을 사용할 수 없습니다.

          플래시백 테이블은 테이블 Truncate 작업과 같은 경우 구조적 변경을 통해 테이블을 Rewind 할수 없습니다.

 

Required

테이블 또는 테이블 파티션을 복구하려면 UNDO, SYSTEM, SYSAUX 및 테이블 또는 테이블 파티션이 포함 된 테이블 스페이스의 전체 백업이 필요합니다.

테이블을 복구하려면 테이블의 종속 오브젝트를 포함하는 모든 파티션이 Recovery Set 에 포함되어야 합니다. 테이블 스페이스 tbs1 의 테이블에 대한 인덱스 또는 파티션이 테이블 스페이스 tbs2 에 포함되어 있으면 테이블스페이스 tbs2 가 Recovery Set 에도 포함되어있는 경우에만 테이블을 복구 할 수 있습니다.

 

Basic Concepts

RMAN 백업에서 테이블 및 테이블 파티션을 복구하려면 다음 정보를 제공해야합니다.

 

l  Names of tables or table partitions that must be recovered

l  Point in time to which the tables or table partitions must be recovered

l  Whether the recovered tables or table partitions must be imported into the target database

 

RMAN 은 이 정보를 사용하여 지정된 테이블 또는 테이블 파티션을 복구하는 프로세스를 자동화합니다. 복구 프로세스의 일부로 RMAN 은 테이블 또는 테이블 파티션을 지정된 시점으로 복구하는 데 사용되는 Auxiliary 데이터베이스를 생성합니다.

복구된 테이블 또는 테이블 파티션의 이름을 바꾸거나 새 테이블 스페이스에 매핑하거나 새 스키마에 매핑해야하는 경우 테이블, 테이블 스페이스 또는 스키마의 새 이름을 지정해야합니다.

 

 

Limitations

RECOVER 명령을 사용하여 RMAN 백업에 포함된 테이블 또는 테이블 파티션을 복구 할 때 다음 제약 사항이 있습니다.

 

l  Tables and table partitions belonging to SYS schema cannot be recovered.

l  Tables and table partitions from SYSTEM and SYSAUX tablespaces cannot be recovered.

l  Tables and table partitions on standby databases cannot be recovered.

l  Tables with named NOT NULL constraints cannot be recovered with the REMAP option.

 

3.   실습

3.1.        TEST 환경

Oracle

18c Enterprise Edition Release 18.0.0.0.0 (Single)

 

ARCHIVE MODE

SQL> archive log list;

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            /arch

Oldest online log sequence     41

Next log sequence to archive   43

Current log sequence           43

 

RMAN BACKUPSET

 List of Datafiles in backup set 3

 File LV Type Ckp SCN    Ckp Time  Abs Fuz SCN Sparse Name

 ---- -- ---- ---------- --------- ----------- ------ ----

 1       Full 1584295    28-AUG-18              NO    /oradata/YOUNHA/system01.dbf

 2       Full 1584295    28-AUG-18              NO    /oradata/YOUNHA/sysaux01.dbf

 3       Full 1584295    28-AUG-18              NO    /oradata/YOUNHA/undotbs01.dbf

 4       Full 1584295    28-AUG-18              NO    /oradata/YOUNHA/users01.dbf

 5       Full 1584295    28-AUG-18              NO    /oradata/YOUNHA/younha01.dbf

 6       Full 1584295    28-AUG-18              NO    /oradata/YOUNHA/goodus01.dbf

 

TABLESPACE 정보

SQL> select tablespace_name, file_name from dba_data_files;

 

TABLESPACE_NAME                FILE_NAME

------------------------------ ----------------------------------------

SYSTEM                         /oradata/YOUNHA/system01.dbf

SYSAUX                         /oradata/YOUNHA/sysaux01.dbf

UNDOTBS1                       /oradata/YOUNHA/undotbs01.dbf

USERS                          /oradata/YOUNHA/users01.dbf

YOUNHA                         /oradata/YOUNHA/younha01.dbf

GOODUS                         /oradata/YOUNHA/goodus01.dbf

 

USER 정보

SQL> select username, default_tablespace from dba_users where username in ('YOUNHA','GOODUS');

 

USERNAME                                 DEFAULT_TABLESPACE

---------------------------------------- ------------------------------

YOUNHA                                   YOUNHA

GOODUS                                   GOODUS

 

2장에서 설명했듯이 Rman Backup 이 있어야 합니다.

YOUNHA & GOODUS Schema 에서 실습을 진행하겠습니다.

 

 

참고로 YOUNAH Schema 의 RECOVER TABLE 은 DBA_OBJECTS 를 CTAS 한 것입니다.

YOUNHA Schema 에 RECOVER TABLE 현재 OWNER 별 COUNT 수

SQL> select owner, count(*) from recover group by owner;

 

OWNER                                      COUNT(*)

---------------------------------------- ----------

SYS                                           51515

SYSTEM                                          466

DBSNMP                                           55

APPQOSSYS                                         6

DBSFWUSER                                         8

REMOTE_SCHEDULER_AGENT                           13

PUBLIC                                         7981

AUDSYS                                           28

OJVMSYS                                          18

GSMADMIN_INTERNAL                               209

OUTLN                                            10

ORACLE_OCM                                        8

XDB                                            1002

WMSYS                                           399

 

2018-08-29 02:00

Where 조건절 Object_type 에 TABLE 이 아닌 VIEW 를 했어야 했는데 실수로 Update 수행 후 Commit

SQL> update recover set owner='YOUNHA' where object_type='TABLE';

 

1807 rows updated.

 

SQL> commit;

 

Commit complete.

 

SQL> select owner, count(*) from recover group by owner;

 

OWNER                                      COUNT(*)

---------------------------------------- ----------

SYS                                           50009

SYSTEM                                          338

DBSNMP                                           35

APPQOSSYS                                         1

DBSFWUSER                                         5

REMOTE_SCHEDULER_AGENT                           13

YOUNHA                                         1807

PUBLIC                                         7981

AUDSYS                                           27

OJVMSYS                                          12

GSMADMIN_INTERNAL                               167

OUTLN                                             7

ORACLE_OCM                                        8

XDB                                             949

WMSYS                                           359

 

 

3.2.        사전 준비 단계

 

RMAN 백업에서 테이블 또는 테이블 파티션을 복구하기 위한 준비에는 다음 단계가 포함됩니다.

 

테이블 또는 테이블 파티션을 복구하는데 필요한 전제 조건이 충족되는지 확인	SYS 유저 소유 X SYSTEM, SYSAUX 테이블스페이스 X NOT NULL 조건 X STANDBY DATABASE X RMAN Full Backup 존재
테이블 또는 테이블 파티션을 복구해야하는 시점 결정	2018-08-28일자 RMAN Full Backup 이 있으므로 2018-08-29 01시로 테이블 복구 결정
복구 된 테이블 또는 테이블 파티션을 대상 데이터베이스로 Import 여부 결정	대상 데이터베이스로 Import 수행 결정 (먼저 dump 파일 생성 후 수동 Import)

 

 

3.3.        RECOVER TABLE

[oracle:YOUNHA:/home/oracle] rman target /

 

Recovery Manager: Release 18.0.0.0.0 - Production on Wed Aug 29 05:52:43 2018

Version 18.3.0.0.0

 

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

 

connected to target database: YOUNHA (DBID=1097273874)

 

RMAN> RECOVER TABLE YOUNHA.'RECOVER' UNTIL TIME  "to_date('2018/08/29 01:00:00','yyyy/mm/dd hh24:mi:ss')" AUXILIARY DESTINATION '/oracle/tmp' DATAPUMP DESTINATION '/home/oracle' DUMP FILE 'younha_recover.dmp' NOTABLEIMPORT;

여기서는 하기와 같은 방법으로 2018-08-29 01시의 테이블을 dump 파일로 받는 구문을 사용했습니다.

상세한 옵션은 4장에서 알아보겠습니다.

 

RECOVER TABLE YOUNHA.'RECOVER'

è  YOUNHA Schema 의 RECOVER 테이블을 대상으로 설정

UNTIL TIME "to_date('2018/08/29 01:00:00','yyyy/mm/dd hh24:mi:ss')"

è  복구 시간은 UNTIL TIME 구문을 사용하여 2018-08-29 01시로 설정

AUXILIARY DESTINATION '/oracle/tmp'

è  RECOVER TABLE 기능은 Clone DB 를 Auxiliary 를 사용하여 수행합니다.

è  로그를 보시면 /oracle/tmp 경로 밑으로 Rman Backup 을 Restore 합니다.

DATAPUMP DESTINATION '/home/oracle'

DUMP FILE 'younha_recover.dmp'

è  DATAPUMP 의 DUMP 파일 경로 및 이름 설정

NOTABLEIMPORT;

è  DUMP 파일을 생성하고 IMPORT 는 하지 않음으로 설정

 

YOUNHA.RECOVER 테이블은 테이블스페이스 YOUNHA 에 있는 Segment 이므로 복구 대상 테이블에 필요한 데이터 파일 (푸른색인 데이터 파일 4, 6 ) 이 아니라면 Restore 대상에서 제외하고 수행됩니다.

( 공간 산정할 때 참고하십시오. )

 

RMAN> REPORT SCHEMA;

 

Report of database schema for database with db_unique_name YOUNHA

 

List of Permanent Datafiles

===========================

File Size(MB) Tablespace           RB segs Datafile Name

---- -------- -------------------- ------- ------------------------

1    730      SYSTEM               YES     /oradata/YOUNHA/system01.dbf

2    1010     SYSAUX               NO      /oradata/YOUNHA/sysaux01.dbf

3    425      UNDOTBS1             YES     /oradata/YOUNHA/undotbs01.dbf

4    5        USERS                NO      /oradata/YOUNHA/users01.dbf

5    500      YOUNHA               NO      /oradata/YOUNHA/younha01.dbf

6    500      GOODUS               NO      /oradata/YOUNHA/goodus01.dbf

 

해당 RECOVER TABLE 구문 수행 시 로그입니다. 11g 까지는 하기와 같은 절차를 수동으로 하나하나 진행했어야 했다면, 12c 이후부터는 위 명령어만 수행하면 자동적으로 restore -> recover -> expdp -> remove 까지 깔끔하게 수행됩니다.

 

Starting recover at 29-AUG-18

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=18 device type=DISK

RMAN-05026: warning: presuming following set of tablespaces applies to specified point-in-time

 

List of tablespaces expected to have UNDO segments

Tablespace SYSTEM

Tablespace UNDOTBS1

 

Creating automatic instance, with SID='iawE'

 

initialization parameters used for automatic instance:

db_name=YOUNHA

db_unique_name=iawE_pitr_YOUNHA

compatible=18.0.0

db_block_size=8192

db_files=200

diagnostic_dest=/oracle/app

_system_trig_enabled=FALSE

sga_target=2016M

processes=200

db_create_file_dest=/oracle/tmp

log_archive_dest_1='location=/oracle/tmp'

#No auxiliary parameter file used

 

starting up automatic instance YOUNHA

 

Oracle instance started

 

Total System Global Area    2113925840 bytes

 

Fixed Size                     8659664 bytes

Variable Size                486539264 bytes

Database Buffers            1610612736 bytes

Redo Buffers                   8114176 bytes

Automatic instance created

 

contents of Memory Script:

{

# set requested point in time

set until  time "to_date('2018/08/29 01:00:00','yyyy/mm/dd hh24:mi:ss')";

# restore the controlfile

restore clone controlfile;

 

# mount the controlfile

sql clone 'alter database mount clone database';

 

# archive current online log

sql 'alter system archive log current';

}

executing Memory Script

 

executing command: SET until clause

 

Starting restore at 29-AUG-18

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: SID=244 device type=DISK

channel ORA_AUX_DISK_1: starting datafile backup set restore

channel ORA_AUX_DISK_1: restoring control file

channel ORA_AUX_DISK_1: reading from backup piece /arch/rman/controlfile_c-1097273874-20180828-02.ctl

channel ORA_AUX_DISK_1: piece handle=/arch/rman/controlfile_c-1097273874-20180828-02.ctl tag=TAG20180828T233634

channel ORA_AUX_DISK_1: restored backup piece 1

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01

output file name=/oracle/tmp/YOUNHA/controlfile/o1_mf_frcfkgny_.ctl

Finished restore at 29-AUG-18

 

sql statement: alter database mount clone database

 

sql statement: alter system archive log current

 

contents of Memory Script:

{

# set requested point in time

set until  time "to_date('2018/08/29 01:00:00','yyyy/mm/dd hh24:mi:ss')";

# set destinations for recovery set and auxiliary set datafiles

set newname for clone datafile  1 to new;

set newname for clone datafile  3 to new;

set newname for clone datafile  2 to new;

set newname for clone tempfile  1 to new;

# switch all tempfiles

switch clone tempfile all;

# restore the tablespaces in the recovery set and the auxiliary set

restore clone datafile  1, 3, 2;

 

switch clone datafile all;

}

executing Memory Script

 

executing command: SET until clause

 

executing command: SET NEWNAME

 

executing command: SET NEWNAME

 

executing command: SET NEWNAME

 

executing command: SET NEWNAME

 

renamed tempfile 1 to /oracle/tmp/YOUNHA/datafile/o1_mf_temp_%u_.tmp in control file

 

Starting restore at 29-AUG-18

using channel ORA_AUX_DISK_1

 

channel ORA_AUX_DISK_1: starting datafile backup set restore

channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set

channel ORA_AUX_DISK_1: restoring datafile 00001 to /oracle/tmp/YOUNHA/datafile/o1_mf_system_%u_.dbf

channel ORA_AUX_DISK_1: restoring datafile 00003 to /oracle/tmp/YOUNHA/datafile/o1_mf_undotbs1_%u_.dbf

channel ORA_AUX_DISK_1: restoring datafile 00002 to /oracle/tmp/YOUNHA/datafile/o1_mf_sysaux_%u_.dbf

channel ORA_AUX_DISK_1: reading from backup piece /arch/rman/YOUNHA_03tbnnfj_1_1_FULL_database_20180828.bk

channel ORA_AUX_DISK_1: piece handle=/arch/rman/YOUNHA_03tbnnfj_1_1_FULL_database_20180828.bk tag=TAG20180828T233619

channel ORA_AUX_DISK_1: restored backup piece 1

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:15

Finished restore at 29-AUG-18

 

datafile 1 switched to datafile copy

input datafile copy RECID=4 STAMP=985413205 file name=/oracle/tmp/YOUNHA/datafile/o1_mf_system_frcfkpcp_.dbf

datafile 3 switched to datafile copy

input datafile copy RECID=5 STAMP=985413205 file name=/oracle/tmp/YOUNHA/datafile/o1_mf_undotbs1_frcfkpcr_.dbf

datafile 2 switched to datafile copy

input datafile copy RECID=6 STAMP=985413205 file name=/oracle/tmp/YOUNHA/datafile/o1_mf_sysaux_frcfkpco_.dbf

 

contents of Memory Script:

{

# set requested point in time

set until  time "to_date('2018/08/29 01:00:00','yyyy/mm/dd hh24:mi:ss')";

# online the datafiles restored or switched

sql clone "alter database datafile  1 online";

sql clone "alter database datafile  3 online";

sql clone "alter database datafile  2 online";

# recover and open database read only

recover clone database tablespace  "SYSTEM", "UNDOTBS1", "SYSAUX";

sql clone 'alter database open read only';

}

executing Memory Script

 

executing command: SET until clause

 

sql statement: alter database datafile  1 online

sql statement: alter database datafile  3 online

sql statement: alter database datafile  2 online

 

Starting recover at 29-AUG-18

using channel ORA_AUX_DISK_1

 

starting media recovery

 

archived log for thread 1 with sequence 44 is already on disk as file /arch/1_44_983293266.arc

archived log file name=/arch/1_44_983293266.arc thread=1 sequence=44

media recovery complete, elapsed time: 00:00:01

Finished recover at 29-AUG-18

 

sql statement: alter database open read only

 

contents of Memory Script:

{

   sql clone "create spfile from memory";

   shutdown clone immediate;

   startup clone nomount;

   sql clone "alter system set  control_files =

  ''/oracle/tmp/YOUNHA/controlfile/o1_mf_frcfkgny_.ctl'' comment=

''RMAN set'' scope=spfile";

   shutdown clone immediate;

   startup clone nomount;

# mount database

sql clone 'alter database mount clone database';

}

 

 

executing Memory Script

 

sql statement: create spfile from memory

 

database closed

database dismounted

Oracle instance shut down

 

connected to auxiliary database (not started)

Oracle instance started

 

Total System Global Area    2113925840 bytes

 

Fixed Size                     8659664 bytes

Variable Size                486539264 bytes

Database Buffers            1610612736 bytes

Redo Buffers                   8114176 bytes

 

sql statement: alter system set  control_files =   ''/oracle/tmp/YOUNHA/controlfile/o1_mf_frcfkgny_.ctl'' comment= ''RMAN set'' scope=spfile

 

Oracle instance shut down

 

connected to auxiliary database (not started)

Oracle instance started

 

Total System Global Area    2113925840 bytes

 

Fixed Size                     8659664 bytes

Variable Size                486539264 bytes

Database Buffers            1610612736 bytes

Redo Buffers                   8114176 bytes

 

sql statement: alter database mount clone database

 

contents of Memory Script:

{

# set requested point in time

set until  time "to_date('2018/08/29 01:00:00','yyyy/mm/dd hh24:mi:ss')";

# set destinations for recovery set and auxiliary set datafiles

set newname for datafile  5 to new;

# restore the tablespaces in the recovery set and the auxiliary set

restore clone datafile  5;

 

switch clone datafile all;

}

executing Memory Script

executing command: SET until clause

executing command: SET NEWNAME

 

Starting restore at 29-AUG-18

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: SID=9 device type=DISK

 

channel ORA_AUX_DISK_1: starting datafile backup set restore

channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set

channel ORA_AUX_DISK_1: restoring datafile 00005 to /oracle/tmp/IAWE_PITR_YOUNHA/datafile/o1_mf_younha_%u_.dbf

channel ORA_AUX_DISK_1: reading from backup piece /arch/rman/YOUNHA_03tbnnfj_1_1_FULL_database_20180828.bk

channel ORA_AUX_DISK_1: piece handle=/arch/rman/YOUNHA_03tbnnfj_1_1_FULL_database_20180828.bk tag=TAG20180828T233619

channel ORA_AUX_DISK_1: restored backup piece 1

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03

Finished restore at 29-AUG-18

 

datafile 5 switched to datafile copy

input datafile copy RECID=8 STAMP=985413279 file name=/oracle/tmp/IAWE_PITR_YOUNHA/datafile/o1_mf_younha_frcfncxj_.dbf

 

contents of Memory Script:

{

# set requested point in time

set until  time "to_date('2018/08/29 01:00:00','yyyy/mm/dd hh24:mi:ss')";

# online the datafiles restored or switched

sql clone "alter database datafile  5 online";

# recover and open resetlogs

recover clone database tablespace  "YOUNHA", "SYSTEM", "UNDOTBS1", "SYSAUX" delete archivelog;

alter clone database open resetlogs;

}

executing Memory Script

 

executing command: SET until clause

 

sql statement: alter database datafile  5 online

 

 

 

Starting recover at 29-AUG-18

using channel ORA_AUX_DISK_1

 

starting media recovery

archived log for thread 1 with sequence 44 is already on disk as file /arch/1_44_983293266.arc

archived log file name=/arch/1_44_983293266.arc thread=1 sequence=44

media recovery complete, elapsed time: 00:00:01

Finished recover at 29-AUG-18

 

database opened

 

contents of Memory Script:

{

# create directory for datapump import

sql "create or replace directory TSPITR_DIROBJ_DPDIR as ''

/home/oracle''";

# create directory for datapump export

sql clone "create or replace directory TSPITR_DIROBJ_DPDIR as ''

/home/oracle''";

}

executing Memory Script

 

sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/home/oracle''

sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/home/oracle''

 

Performing export of tables...

   EXPDP> Starting "SYS"."TSPITR_EXP_iawE_tfcd":

   EXPDP> Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

   EXPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

   EXPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER

   EXPDP> Processing object type TABLE_EXPORT/TABLE/TABLE

   EXPDP> . . exported "YOUNHA"."RECOVER"                          7.684 MB   61718 rows

   EXPDP> Master table "SYS"."TSPITR_EXP_iawE_tfcd" successfully loaded/unloaded

   EXPDP> ******************************************************************************

   EXPDP> Dump file set for SYS.TSPITR_EXP_iawE_tfcd is:

   EXPDP>   /home/oracle/younha_recover.dmp

   EXPDP> Job "SYS"."TSPITR_EXP_iawE_tfcd" successfully completed at Wed Aug 29 05:55:31 2018 elapsed 0 00:00:29

Export completed

 

Not performing table import after point-in-time recovery

 

Removing automatic instance

shutting down automatic instance

Oracle instance shut down

Automatic instance removed

auxiliary instance file /oracle/tmp/YOUNHA/datafile/o1_mf_temp_frcflb40_.tmp deleted

auxiliary instance file /oracle/tmp/IAWE_PITR_YOUNHA/onlinelog/o1_mf_3_frcfnkc0_.log deleted

auxiliary instance file /oracle/tmp/IAWE_PITR_YOUNHA/onlinelog/o1_mf_2_frcfnkbh_.log deleted

auxiliary instance file /oracle/tmp/IAWE_PITR_YOUNHA/onlinelog/o1_mf_1_frcfnkb3_.log deleted

auxiliary instance file /oracle/tmp/IAWE_PITR_YOUNHA/datafile/o1_mf_younha_frcfncxj_.dbf deleted

auxiliary instance file /oracle/tmp/YOUNHA/datafile/o1_mf_sysaux_frcfkpco_.dbf deleted

auxiliary instance file /oracle/tmp/YOUNHA/datafile/o1_mf_undotbs1_frcfkpcr_.dbf deleted

auxiliary instance file /oracle/tmp/YOUNHA/datafile/o1_mf_system_frcfkpcp_.dbf deleted

auxiliary instance file /oracle/tmp/YOUNHA/controlfile/o1_mf_frcfkgny_.ctl deleted

Finished recover at 29-AUG-18

 

RECOVER TABLE 절에서 지정한 경로로 설정한 이름으로 dmp 파일이 생성된 것을 확인 할 수 있습니다.

[oracle:YOUNHA:/home/oracle] ls younha*

younha_recover.dmp

 

è  해당 Dump 파일을 가지고 GOODUS Schema에 임시적으로 복구해보겠습니다.

[oracle:YOUNHA:/home/oracle] impdp goodus/oracle directory=DATAPUMP dumpfile=younha_recover.dmp remap_schema=YOUNHA:GOODUS

 

Import: Release 18.0.0.0.0 - Production on Wed Aug 29 06:28:09 2018

Version 18.3.0.0.0

……… <중략>

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

. . imported "GOODUS"."RECOVER"                          7.684 MB   61718 rows

Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER

Job "GOODUS"."SYS_IMPORT_FULL_01" successfully completed at Wed Aug 29 06:28:44 2018 elapsed 0 00:00:27

 

2018-08-29 01:00 데이터로 복구되었습니다.

SQL> conn goodus/oracle

Connected.

SQL> select owner, count(*) from recover group by owner;

 

OWNER                                      COUNT(*)

---------------------------------------- ----------

SYS                                           51515

SYSTEM                                          466

DBSNMP                                           55

APPQOSSYS                                         6

DBSFWUSER                                         8

REMOTE_SCHEDULER_AGENT                           13

PUBLIC                                         7981

AUDSYS                                           28

OJVMSYS                                          18

GSMADMIN_INTERNAL                               209

OUTLN                                            10

ORACLE_OCM                                        8

XDB                                            1002

WMSYS                                           399

 

14 rows selected.

 

4.   OPTION

RECOVER TABLE YOUNHA.'RECOVER'

è  YOUNHA Schema 의 RECOVER 테이블을 대상으로 설정

UNTIL TIME "to_date('2018/08/29 01:00:00','yyyy/mm/dd hh24:mi:ss')"

è  복구 시간은 UNTIL TIME 구문을 사용하여 2018-08-29 01시로 설정

AUXILIARY DESTINATION '/oracle/tmp'

è  RECOVER TABLE 기능은 Clone DB 를 Auxiliary 를 사용하여 수행합니다.

è  로그를 보시면 /oracle/tmp 경로 밑으로 Rman Backup 을 Restore 합니다.

DATAPUMP DESTINATION '/home/oracle'

DUMP FILE 'younha_recover.dmp'

è  DATAPUMP 의 DUMP 파일 경로 및 이름 설정

NOTABLEIMPORT;

è  DUMP 파일을 생성하고 IMPORT 는 하지 않음으로 설정

 

해당 명령어는 NOTABLEIMPORT 절을 사용한 명령어 입니다.

DATAPUMP DESTINATION & DUMP FILE 절은 옵션입니다. 경로와 파일이름을 명시하지 않으면 AUXILIARY DESTINATION 에 명시된 ‘/oracle/tmp’ 경로에 자동으로 Naming 하여 생성합니다.

 

NOTABLEIMPORT 절을 사용하지 않는 경우 명령어 입니다.

RECOVER TABLE YOUNHA.'RECOVER'

UNTIL TIME "to_date('2018/08/29 01:00:00','yyyy/mm/dd hh24:mi:ss')"

AUXILIARY DESTINATION '/oracle/tmp';

 

하기와 같이 기존에 YOUNHA.RECOVER 테이블이 존재하여 명령이 실패합니다.

Starting recover at 21-SEP-18

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of recover command at 09/21/2018 09:58:35

RMAN-05063: Cannot recover specified tables

RMAN-05112: table "YOUNHA"."RECOVER" already exists

 

따라서 복구할 테이블을 삭제하던가 REMAP TABLE 절을 사용하여 테이블이름이 다르게 복구합니다.

RECOVER TABLE YOUNHA.'RECOVER'

UNTIL TIME "to_date('2018/08/29 01:00:00','yyyy/mm/dd hh24:mi:ss')"

AUXILIARY DESTINATION '/oracle/tmp'

REMAP TABLE 'YOUNHA'.'RECOVER':'GOODUS';

 

 

하기 수행 로그

Performing export of tables...

   EXPDP> Starting "SYS"."TSPITR_EXP_xpCs_okyb":

   EXPDP> Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

   EXPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

   EXPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER

   EXPDP> Processing object type TABLE_EXPORT/TABLE/TABLE

   EXPDP> . . exported "YOUNHA"."RECOVER"                          7.684 MB   61718 rows

   EXPDP> Master table "SYS"."TSPITR_EXP_xpCs_okyb" successfully loaded/unloaded

   EXPDP> ******************************************************************************

   EXPDP> Dump file set for SYS.TSPITR_EXP_xpCs_okyb is:

   EXPDP>   /oracle/tmp/tspitr_xpCs_93048.dmp

   EXPDP> Job "SYS"."TSPITR_EXP_xpCs_okyb" successfully completed at Fri Sep 21 09:54:29 2018 elapsed 0 00:00:39

Export completed

--> Clone db 에서 해당 테이블 EXPORT

 

contents of Memory Script:

{

# shutdown clone before import

shutdown clone abort

}

executing Memory Script

 

Oracle instance shut down

 

Performing import of tables...

   IMPDP> Master table "SYS"."TSPITR_IMP_xpCs_vzqe" successfully loaded/unloaded

   IMPDP> Starting "SYS"."TSPITR_IMP_xpCs_vzqe":

   IMPDP> Processing object type TABLE_EXPORT/TABLE/TABLE

   IMPDP> Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

   IMPDP> . . imported "YOUNHA"."GOODUS"                           7.684 MB   61718 rows

   IMPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

   IMPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER

   IMPDP> Job "SYS"."TSPITR_IMP_xpCs_vzqe" successfully completed at Fri Sep 21 09:55:39 2018 elapsed 0 00:00:41

Import completed

--> Source db 에 GOODUS 테이블로 IMPORT

 

5.    Tip

 

RMAN Recover Table Fails With ORA-00376/ORA-01110 (문서 ID 2306784.1)

 

12c 에서 나온 New Feature 로서 아직 12.1.0.2 버전에서는 Bug 로 인해 하기와 같이 실패한다면 해당 Interim Patch 를 진행하셔야 합니다.

RMAN> run {

configure device type sbt_tape parallelism 8;

recover table INTRAWARE8.TN_SCHEDULE_CALENDAR,INTRAWARE8.TN_CALENDAR,INTRAWARE8.TN_EVENT 

until time "TO_DATE('20180416180000','YYYYMMDDHH24MISS')"

auxiliary destination '/backup/imsi'

datapump destination '/backup/imsi'

dump file '0416.dmp'

log file '0416.log'

notableimport;

}

 

…………… 중략

Failed with the following errors :

Performing export of tables...

  EXPDP> Starting "SYS"."TSPITR_EXP_jgqo_mfjy":

  EXPDP> Estimate in progress using BLOCKS method...

  EXPDP> Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

  EXPDP> Processing object type TABLE_EXPORT/TABLE/TABLE

  EXPDP> Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT

  EXPDP> Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX

  EXPDP> Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT

  EXPDP> Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS

  EXPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

  EXPDP> ORA-31693: Table data object "INTRAWARE8"."TN_CALENDAR" failed to load/unload and is being skipped due to error:

  EXPDP> ORA-31693: Table data object "INTRAWARE8"."TN_EVENT" failed to load/unload and is being skipped due to error:

  EXPDP> ORA-31693: Table data object "INTRAWARE8"."TN_SCHEDULE_CALENDAR" failed to load/unload and is being skipped due to error:

ORA-00376: file 262 cannot be read at this time

ORA-01110: data file 262: '/backup/imsi/TSINTRA8_1_TDE262.dbf'

  EXPDP> . . exported "INTRAWARE8"."TN_CALENDAR" 0 KB 0 rows

  EXPDP> . . exported "INTRAWARE8"."TN_EVENT" 0 KB 0 rows

  EXPDP> . . exported "INTRAWARE8"."TN_SCHEDULE_CALENDAR" 0 KB 0 rows

 

This issue maybe caused by bug 14821907: KU$_TTS_TAB_TABLESPACE_NAME VIEW SHOWS INCORRECT TABLESPACE NAME

 

 

Hot Backup 을 이용한 Recover Table

 

본 문서에서는 RMAN Backup 을 이용한 Recover Table 을 살펴보았습니다. 이번 장에서는 Hot Backup 을 이용한 Recover Table 절차를 알아보겠습니다.

10월 1일자로 Backup Daily Backup 수행 중이며, RMAN 에는 archive 를 제외한 백업에 대한 정보가 없습니다.

[oracle:YOUNHA:/oracle/backup/dailybackup/01-Oct-2018> ls

control.bak goodus01.dbf sysaux01.dbf  system01.dbf  temp01.dbf  

undotbs01.dbf  users01.dbf  younha01.dbf younha02.dbf

 

RMAN> list backup;

 

using target database control file instead of recovery catalog

specification does not match any backup in the repository

 

RMAN> list copy;

 

specification does not match any datafile copy in the repository

specification does not match any control file copy in the repository

List of Archived Log Copies for database with db_unique_name YOUNHA

=====================================================================

 

Key     Thrd Seq     S Low Time

------- ---- ------- - ---------

206     1    216     A 30-SEP-18

        Name: /arch/1_216_983293266.arc

207     1    217     A 01-OCT-18

        Name: /arch/1_217_983293266.arc

208     1    218     A 01-OCT-18

        Name: /arch/1_218_983293266.arc

 

해당 Backup 을 Controlfile 에 등록시켜 줍니다.

RMAN> catalog start with '/oracle/backup/dailybackup/01-Oct-2018';

 

using target database control file instead of recovery catalog

searching for all files that match the pattern /oracle/backup/dailybackup/01-Oct-2018

 

List of Files Unknown to the Database

=====================================

File Name: /oracle/backup/dailybackup/01-Oct-2018/system01.dbf

<중략>

 

Do you really want to catalog the above files (enter YES or NO)? YES

cataloging files...

cataloging done

 

List of Cataloged Files

=======================

File Name: /oracle/backup/dailybackup/01-Oct-2018/system01.dbf

<중략>

 

RMAN> catalog controlfilecopy '/oracle/backup/dailybackup/01-Oct-2018/control.bak';

 

cataloged control file copy

control file copy file name=/oracle/backup/dailybackup/01-Oct-2018/control.bak RECID=16 STAMP=988380846

 

 

Hot Backup 정보가 Controlfile 에 등록됨을 확인했습니다.

RMAN> list copy;

 

List of Datafile Copies

=======================

 

Key     File S Completion Time Ckp SCN    Ckp Time        Sparse

------- ---- - --------------- ---------- --------------- ------

9       1    A 01-OCT-18       3310027    01-OCT-18       NO

        Name: /oracle/backup/dailybackup/01-Oct-2018/system01.dbf

11      2    A 01-OCT-18       3310027    01-OCT-18       NO

        Name: /oracle/backup/dailybackup/01-Oct-2018/sysaux01.dbf

10      3    A 01-OCT-18       3310027    01-OCT-18       NO

        Name: /oracle/backup/dailybackup/01-Oct-2018/undotbs01.dbf

13      4    A 01-OCT-18       3310027    01-OCT-18       NO

        Name: /oracle/backup/dailybackup/01-Oct-2018/users01.dbf

12      5    A 01-OCT-18       3310027    01-OCT-18       NO

        Name: /oracle/backup/dailybackup/01-Oct-2018/younha01.dbf

15      6    A 01-OCT-18       3310027    01-OCT-18       NO

        Name: /oracle/backup/dailybackup/01-Oct-2018/goodus01.dbf

14      7    A 01-OCT-18       3310027    01-OCT-18       NO

        Name: /oracle/backup/dailybackup/01-Oct-2018/younha02.dbf

 

List of Control File Copies

===========================

 

Key     S Completion Time Ckp SCN    Ckp Time

------- - --------------- ---------- ---------------

16      A 01-OCT-18       3310180    01-OCT-18

        Name: /oracle/backup/dailybackup/01-Oct-2018/control.bak

        Tag: TAG20181001T140035

 

백업 시점으로 테이블을 복구 하겠습니다.

SQL> select scn_to_timestamp(3310180) from dual;

 

SCN_TO_TIMESTAMP(3310180)

---------------------------------------------------------------------------

01-OCT-18 02.00.34.000000000 PM

 

Rman 에서 UNTIL SCN 명령어를 이용했습니다.

RECOVER TABLE YOUNHA.'RECOVER'

UNTIL SCN 3310180

AUXILIARY DESTINATION '/oracle/tmp'

REMAP TABLE 'YOUNHA'.'RECOVER':'RECOVER_HOT';

 

DATA기술팀 박대성 엔지니어

#굿어스데이터 #GoodusData #DB기술노트 #Oralce #오라클 #Recovering Tables(with Rman)

"People make a better world with Data"

[Written by GoodusData / 굿어스데이터]