내맘대로긍정이 알려주는
Oracle 23ai 신기능
무료 세미나 발표자료
OS환경 : Oracle Linux 6.8 (64bit)
DB 환경 : Oracle Database 11.2.0.4
방법 : Oracle partition tablespace, table rman 복구 테스트
테스트 요약 : lv 생성 후 테이블스페이스, 테이블 생성, 데이터 삽입 후 rman 으로 해당 tablespace 만 백업 후
데이터 추가 삽입 후 dd 명령으로 임의 raw device 를 초기화 시킨후 rman으로 복구하는 테스트
사전작업
pv 남은 용량 확인
1 2 3 4 5 6 7 8 9 10 11 | # pvdisplay /dev/sdc1 --- Physical volume --- PV Name /dev/sdc1 VG Name rac PV Size 6.00 GiB / not usable 2.01 MiB Allocatable yes PE Size 4.00 MiB Total PE 1535 Free PE 1160 Allocated PE 375 PV UUID GbfjhH-fKkr-7kWl-mFsR-tBMs-0g3j-GWKH0g |
lv 생성
1 2 3 4 5 6 7 8 9 | # lvcreate -L 100m -n pt01 rac # lvcreate -L 100m -n pt02 rac # lvcreate -L 100m -n pt03 rac # lvcreate -L 100m -n pt04 rac # lvcreate -L 100m -n pt05 rac # lvcreate -L 100m -n pt06 rac # lvcreate -L 100m -n pt07 rac # lvcreate -L 100m -n pt08 rac # lvcreate -L 100m -n pt09 rac |
pv 남은 용량 확인
1 2 3 4 5 6 7 8 9 10 11 | # pvdisplay /dev/sdc1 --- Physical volume --- PV Name /dev/sdc1 VG Name rac PV Size 6.00 GiB / not usable 2.01 MiB Allocatable yes PE Size 4.00 MiB Total PE 1535 Free PE 910 Allocated PE 625 PV UUID GbfjhH-fKkr-7kWl-mFsR-tBMs-0g3j-GWKH0g |
910 PE 사용가능
raw와 매핑 rc.local 파일에 아래 내용 추가 후 os에서도 입력
1 2 3 4 5 6 7 8 9 10 11 | # vi /etc/rc.local raw /dev/raw/raw30 /dev/rac/pt01 raw /dev/raw/raw31 /dev/rac/pt02 raw /dev/raw/raw32 /dev/rac/pt03 raw /dev/raw/raw33 /dev/rac/pt04 raw /dev/raw/raw34 /dev/rac/pt05 raw /dev/raw/raw35 /dev/rac/pt06 raw /dev/raw/raw36 /dev/rac/pt07 raw /dev/raw/raw37 /dev/rac/pt08 raw /dev/raw/raw38 /dev/rac/pt09 chown oracle:dba /dev/raw/* |
raw 파일 확인
1 2 3 4 5 6 7 8 9 10 | # ls -al /dev/raw* crw-rw---- 1 oracle dba 162, 30 Feb 19 10:38 raw30 crw-rw---- 1 oracle dba 162, 31 Feb 19 10:38 raw31 crw-rw---- 1 oracle dba 162, 32 Feb 19 10:38 raw32 crw-rw---- 1 oracle dba 162, 33 Feb 19 10:38 raw33 crw-rw---- 1 oracle dba 162, 34 Feb 19 10:38 raw34 crw-rw---- 1 oracle dba 162, 35 Feb 19 10:38 raw35 crw-rw---- 1 oracle dba 162, 36 Feb 19 10:38 raw36 crw-rw---- 1 oracle dba 162, 37 Feb 19 10:38 raw37 crw-rw---- 1 oracle dba 162, 38 Feb 19 10:38 raw38 |
시나리오 진행
테스트를 위해 파티션 테이블용 테이블스페이스(part_reco) 생성
1 2 3 4 5 6 7 8 9 10 | SQL> create tablespace part_reco datafile '/dev/raw/raw30' size 90m autoextend off; alter tablespace part_reco add datafile '/dev/raw/raw31' size 90m autoextend off; alter tablespace part_reco add datafile '/dev/raw/raw32' size 90m autoextend off; alter tablespace part_reco add datafile '/dev/raw/raw33' size 90m autoextend off; alter tablespace part_reco add datafile '/dev/raw/raw34' size 90m autoextend off; alter tablespace part_reco add datafile '/dev/raw/raw35' size 90m autoextend off; alter tablespace part_reco add datafile '/dev/raw/raw36' size 90m autoextend off; alter tablespace part_reco add datafile '/dev/raw/raw37' size 90m autoextend off; alter tablespace part_reco add datafile '/dev/raw/raw38' size 90m autoextend off; |
추가된 테이블 스페이스 확인
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | SQL> col tablespace_name for a15 col file_name for a15 select tablespace_name, file_name, bytes/1048576 mb from dba_data_files where tablespace_name = 'PART_RECO'; TABLESPACE_NAME FILE_NAME MB --------------- --------------- ---------- PART_RECO /dev/raw/raw30 90 PART_RECO /dev/raw/raw31 90 PART_RECO /dev/raw/raw32 90 PART_RECO /dev/raw/raw33 90 PART_RECO /dev/raw/raw34 90 PART_RECO /dev/raw/raw35 90 PART_RECO /dev/raw/raw36 90 PART_RECO /dev/raw/raw37 90 PART_RECO /dev/raw/raw38 90 |
테스트용 계정 test_pt 생성 및 권한부여
1 2 | SQL> create user test_pt identified by "Oracle!234" default tablespace part_reco account unlock; grant resource, connect to test_pt; |
유저 접속 후 파티션 테이블 생성
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 | SQL> conn test_pt/"Oracle!234" create table tb_sale ( sale_date varchar2(8 byte) not null , sale_time varchar2(6 byte) not null , cust_no varchar2(10 byte) not null , sale_amt number , input_date date ) tablespace part_reco partition by range (sale_date) ( partition pr_tb_sale_201801 values less than ('20180201') , partition pr_tb_sale_201802 values less than ('20180301') , partition pr_tb_sale_201803 values less than ('20180401') , partition pr_tb_sale_201804 values less than ('20180501') , partition pr_tb_sale_201805 values less than ('20180601') , partition pr_tb_sale_201806 values less than ('20180701') , partition pr_tb_sale_201807 values less than ('20180801') , partition pr_tb_sale_201808 values less than ('20180901') , partition pr_tb_sale_201809 values less than ('20181001') , partition pr_tb_sale_201810 values less than ('20181101') , partition pr_tb_sale_201811 values less than ('20181201') , partition pr_tb_sale_201901 values less than ('20190101') ) ; create unique index pk_tb_sale on tb_sale (sale_date, sale_time, cust_no) tablespace part_reco local; alter table tb_sale add constraint pk_tb_sale primary key (sale_date, sale_time, cust_no); |
생성된 테이블 확인
1 2 3 4 5 6 7 | SQL> select * from tab; TNAME TABTYPE CLUSTERID -------------------- ------- ---------- TB_SALE TABLE (null) 1 row selected. |
각 파티션 마다 3개의 데이터 삽입
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 | SQL> insert into tb_sale values (20180201,183030,'aabbccddee',50000,sysdate); insert into tb_sale values (20180205,183030,'aabbccddee',50000,sysdate); insert into tb_sale values (20180208,183030,'aabbccddee',50000,sysdate); insert into tb_sale values (20180301,183030,'aabbccddee',50000,sysdate); insert into tb_sale values (20180305,183030,'aabbccddee',50000,sysdate); insert into tb_sale values (20180308,183030,'aabbccddee',50000,sysdate); insert into tb_sale values (20180401,183030,'aabbccddee',50000,sysdate); insert into tb_sale values (20180405,183030,'aabbccddee',50000,sysdate); insert into tb_sale values (20180408,183030,'aabbccddee',50000,sysdate); insert into tb_sale values (20180501,183030,'aabbccddee',50000,sysdate); insert into tb_sale values (20180505,183030,'aabbccddee',50000,sysdate); insert into tb_sale values (20180508,183030,'aabbccddee',50000,sysdate); insert into tb_sale values (20180601,183030,'aabbccddee',50000,sysdate); insert into tb_sale values (20180605,183030,'aabbccddee',50000,sysdate); insert into tb_sale values (20180608,183030,'aabbccddee',50000,sysdate); insert into tb_sale values (20180701,183030,'aabbccddee',50000,sysdate); insert into tb_sale values (20180705,183030,'aabbccddee',50000,sysdate); insert into tb_sale values (20180708,183030,'aabbccddee',50000,sysdate); insert into tb_sale values (20180701,183030,'aabbccddee',50000,sysdate); insert into tb_sale values (20180705,183030,'aabbccddee',50000,sysdate); insert into tb_sale values (20180708,183030,'aabbccddee',50000,sysdate); insert into tb_sale values (20180801,183030,'aabbccddee',50000,sysdate); insert into tb_sale values (20180805,183030,'aabbccddee',50000,sysdate); insert into tb_sale values (20180808,183030,'aabbccddee',50000,sysdate); insert into tb_sale values (20180901,183030,'aabbccddee',50000,sysdate); insert into tb_sale values (20180905,183030,'aabbccddee',50000,sysdate); insert into tb_sale values (20180908,183030,'aabbccddee',50000,sysdate); insert into tb_sale values (20181001,183030,'aabbccddee',50000,sysdate); insert into tb_sale values (20181005,183030,'aabbccddee',50000,sysdate); insert into tb_sale values (20181008,183030,'aabbccddee',50000,sysdate); insert into tb_sale values (20181101,183030,'aabbccddee',50000,sysdate); insert into tb_sale values (20181105,183030,'aabbccddee',50000,sysdate); insert into tb_sale values (20181108,183030,'aabbccddee',50000,sysdate); insert into tb_sale values (20181201,183030,'aabbccddee',50000,sysdate); insert into tb_sale values (20181205,183030,'aabbccddee',50000,sysdate); insert into tb_sale values (20181208,183030,'aabbccddee',50000,sysdate); commit; |
파티션 별 데이터 조회
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 | SQL> select * from tb_sale partition for ('20180201'); SALE_DAT SALE_T CUST_NO SALE_AMT INPUT_DAT -------- ------ ---------- ---------- --------- 20180201 183030 aabbccddee 50000 19-FEB-19 20180205 183030 aabbccddee 50000 19-FEB-19 20180208 183030 aabbccddee 50000 19-FEB-19 SQL> select * from tb_sale partition for ('20180301'); SALE_DAT SALE_T CUST_NO SALE_AMT INPUT_DAT -------- ------ ---------- ---------- --------- 20180301 183030 aabbccddee 50000 19-FEB-19 20180305 183030 aabbccddee 50000 19-FEB-19 20180308 183030 aabbccddee 50000 19-FEB-19 SQL> select * from tb_sale partition for ('20180401'); SALE_DAT SALE_T CUST_NO SALE_AMT INPUT_DAT -------- ------ ---------- ---------- --------- 20180401 183030 aabbccddee 50000 19-FEB-19 20180405 183030 aabbccddee 50000 19-FEB-19 20180408 183030 aabbccddee 50000 19-FEB-19 . . 12월까지 |
rman 으로 part_reco 테이블스페이스 백업
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 | $ rman target / Recovery Manager: Release 11.2.0.4.0 - Production on Tue Feb 19 13:52:02 2019 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. connected to target database: PRORCLDB (DBID=655536183) RMAN> backup tablespace part_reco; Starting backup at 19-FEB-19 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=29 instance=ORCLDB2 device type=DISK channel ORA_DISK_1: starting full datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set input datafile file number=00006 name=/dev/raw/raw30 input datafile file number=00007 name=/dev/raw/raw31 input datafile file number=00008 name=/dev/raw/raw32 input datafile file number=00009 name=/dev/raw/raw33 input datafile file number=00010 name=/dev/raw/raw34 input datafile file number=00011 name=/dev/raw/raw35 input datafile file number=00012 name=/dev/raw/raw36 input datafile file number=00013 name=/dev/raw/raw37 input datafile file number=00014 name=/dev/raw/raw38 channel ORA_DISK_1: starting piece 1 at 19-FEB-19 channel ORA_DISK_1: finished piece 1 at 19-FEB-19 piece handle=/oracle/app/11.2.0/db/dbs/03tq9bke_1_1 tag=TAG20190219T135214 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01 Finished backup at 19-FEB-19 |
파티션 테이블에 추가적으로 데이터 삽입
1 2 3 4 5 6 7 8 9 10 11 12 13 | SQL> insert into tb_sale values (20180215,183030,'aabbccddee',50000,sysdate); insert into tb_sale values (20180315,183030,'aabbccddee',50000,sysdate); insert into tb_sale values (20180415,183030,'aabbccddee',50000,sysdate); insert into tb_sale values (20180515,183030,'aabbccddee',50000,sysdate); insert into tb_sale values (20180615,183030,'aabbccddee',50000,sysdate); insert into tb_sale values (20180715,183030,'aabbccddee',50000,sysdate); insert into tb_sale values (20180815,183030,'aabbccddee',50000,sysdate); insert into tb_sale values (20180915,183030,'aabbccddee',50000,sysdate); insert into tb_sale values (20181015,183030,'aabbccddee',50000,sysdate); insert into tb_sale values (20181115,183030,'aabbccddee',50000,sysdate); insert into tb_sale values (20181215,183030,'aabbccddee',50000,sysdate); commit; |
추가 lv 생성
1 | # lvcreate -L 100m -n pt10 rac |
raw와 매핑 rc.local 파일에 아래 내용 추가 후 os에서도 입력
1 2 3 | # vi /etc/rc.local raw /dev/raw/raw39 /dev/rac/pt10 chown oracle:dba /dev/raw/* |
파티션 테이블에 디스크 추가
1 | SQL> alter tablespace part_reco add datafile '/dev/raw/raw39' size 90m autoextend off; |
별도의 tablespace users에 의 및 ddl 작업 수행
1 2 3 4 5 6 7 8 9 10 11 12 13 | SQL> conn scott/tiger insert into emp2 select * from emp2; 14 rows created. SQL> commit; Commit complete. SQL> create table tabtable as select * from tab; Table created. |
파티션 테이블중 특정 디스크 장애 발생
1 2 3 4 5 6 7 8 9 | # dd if=/dev/zero of=/dev/raw/raw33 bs=8k count=10000 10000+0 records in 10000+0 records out 81920000 bytes (82 MB) copied, 0.64504 s, 127 MB/s # dd if=/dev/zero of=/dev/raw/raw35 bs=8k count=10000 10000+0 records in 10000+0 records out 81920000 bytes (82 MB) copied, 0.530012 s, 155 MB/s |
dba_data_files조회 결과
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 | SQL> col tablespace_name for a15 col file_name for a15 select tablespace_name, file_name, bytes/1048576 mb from dba_data_files where tablespace_name = 'PART_RECO'; TABLESPACE_NAME FILE_NAME MB --------------- --------------- ---------- PART_RECO /dev/raw/raw30 90 PART_RECO /dev/raw/raw31 90 PART_RECO /dev/raw/raw32 90 PART_RECO /dev/raw/raw33 90 PART_RECO /dev/raw/raw34 90 PART_RECO /dev/raw/raw35 90 PART_RECO /dev/raw/raw36 90 PART_RECO /dev/raw/raw37 90 PART_RECO /dev/raw/raw38 90 PART_RECO /dev/raw/raw39 90 |
tb_sale 테이블 조회 결과 장애 발생한 디스크에 데이터가 없어 정상 조회 가능
1 2 3 4 5 6 7 8 9 10 11 12 13 | SQL> conn test_pt/"Oracle!234" select * from tb_sale; SALE_DAT SALE_T CUST_NO SALE_AMT INPUT_DAT -------- ------ ---------- ---------- --------- 20180201 183030 aabbccddee 50000 19-FEB-19 20180205 183030 aabbccddee 50000 19-FEB-19 20180208 183030 aabbccddee 50000 19-FEB-19 20180301 183030 aabbccddee 50000 19-FEB-19 20180305 183030 aabbccddee 50000 19-FEB-19 . . |
파티션 테이블 생성후 데이터 중복 방지를 위해 pk 지정 해 두었으나 자가 복제를 위해 삭제
1 2 3 4 5 6 7 8 | SQL> insert into tb_sale select * from tb_sale; insert into tb_sale select * from tb_sale * ERROR at line 1: ORA-00001: unique constraint (TEST_PT.PK_TB_SALE) violated SQL> alter table tb_sale drop constraint PK_TB_SALE; SQL> drop index pk_tb_sale; |
데이터 자가복제
1 2 3 4 | SQL> insert into tb_sale select * from tb_sale; / / /(반복) |
여러번 반복시 아래 에러 발생
1 2 3 4 5 6 7 8 9 | SQL> insert into tb_sale select * from tb_sale * ERROR at line 1: ORA-03113: end-of-file on communication channel Process ID: 5747 Session ID: 29 Serial number: 879 SQL> ERROR: ORA-03114: not connected to ORACLE |
alert 로그 확인
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 | Tue Feb 19 14:24:33 2019 Read of datafile '/dev/raw/raw33' (fno 9) header failed with ORA-01210 Hex dump of (file 9, block 1) in trace file /oracle/app/oracle/diag/rdbms/orcldb/ORCLDB2/trace/ORCLDB2_ckpt_14742.trc Corrupt block relative dba: 0x02400001 (file 9, block 1) Completely zero block found during datafile header read Rereading datafile 9 header failed with ORA-01210 Hex dump of (file 9, block 1) in trace file /oracle/app/oracle/diag/rdbms/orcldb/ORCLDB2/trace/ORCLDB2_ckpt_14742.trc Corrupt block relative dba: 0x02400001 (file 9, block 1) Completely zero block found during datafile header read Errors in file /oracle/app/oracle/diag/rdbms/orcldb/ORCLDB2/trace/ORCLDB2_ckpt_14742.trc: ORA-63999: data file suffered media failure ORA-01122: database file 9 failed verification check ORA-01110: data file 9: '/dev/raw/raw33' ORA-01210: data file header is media corrupt Errors in file /oracle/app/oracle/diag/rdbms/orcldb/ORCLDB2/trace/ORCLDB2_ckpt_14742.trc: ORA-63999: data file suffered media failure ORA-01122: database file 9 failed verification check ORA-01110: data file 9: '/dev/raw/raw33' ORA-01210: data file header is media corrupt . . Tue Feb 19 14:24:57 2019 ALTER SYSTEM SET local_listener=' (ADDRESS=(PROTOCOL=TCP)(HOST=192.168.0.128)(PORT=2521))' SCOPE=MEMORY SID='ORCLDB2'; ALTER DATABASE MOUNT /* db agent *//* {0:9:357} */ This instance was first to mount Successful mount of redo thread 2, with mount id 664511929 Database mounted in Shared Mode (CLUSTER_DATABASE=TRUE) Lost write protection disabled Completed: ALTER DATABASE MOUNT /* db agent *//* {0:9:357} */ ALTER DATABASE OPEN /* db agent *//* {0:9:357} */ This instance was first to open Errors in file /oracle/app/oracle/diag/rdbms/orcldb/ORCLDB2/trace/ORCLDB2_dbw0_5945.trc: ORA-01157: cannot identify/lock data file 9 - see DBWR trace file ORA-01110: data file 9: '/dev/raw/raw33' ORA-27048: skgfifi: file header information is invalid Additional information: 13 Errors in file /oracle/app/oracle/diag/rdbms/orcldb/ORCLDB2/trace/ORCLDB2_dbw0_5945.trc: ORA-01157: cannot identify/lock data file 11 - see DBWR trace file ORA-01110: data file 11: '/dev/raw/raw35' ORA-27048: skgfifi: file header information is invalid Additional information: 15 Abort recovery for domain 0 Errors in file /oracle/app/oracle/diag/rdbms/orcldb/ORCLDB2/trace/ORCLDB2_ora_5963.trc: ORA-01157: cannot identify/lock data file 9 - see DBWR trace file ORA-01110: data file 9: '/dev/raw/raw33' ORA-1157 signalled during: ALTER DATABASE OPEN /* db agent *//* {0:9:357} */... |
에러 발생 후 자동 재기동되었지만 OPEN 상태까지 가지 못함
DB를 mount 상태로 startup
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | $ sqlplus / as sysdba SQL*Plus: Release 11.2.0.4.0 Production on Tue Feb 19 14:26:29 2019 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to an idle instance. ORCLDB2> startup mount ORACLE instance started. Total System Global Area 830930944 bytes Fixed Size 2257800 bytes Variable Size 570428536 bytes Database Buffers 255852544 bytes Redo Buffers 2392064 bytes Database mounted. |
rman을 이용하여 part_reco tablespace 리스토어
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 | $ rman target / Recovery Manager: Release 11.2.0.4.0 - Production on Tue Feb 19 14:27:58 2019 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. connected to target database: PRORCLDB (DBID=655536183, not open) RMAN> restore tablespace part_reco; Starting restore at 19-FEB-19 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=29 instance=ORCLDB2 device type=DISK creating datafile file number=15 name=/dev/raw/raw39 channel ORA_DISK_1: starting datafile backup set restore channel ORA_DISK_1: specifying datafile(s) to restore from backup set channel ORA_DISK_1: restoring datafile 00006 to /dev/raw/raw30 channel ORA_DISK_1: restoring datafile 00007 to /dev/raw/raw31 channel ORA_DISK_1: restoring datafile 00008 to /dev/raw/raw32 channel ORA_DISK_1: restoring datafile 00009 to /dev/raw/raw33 channel ORA_DISK_1: restoring datafile 00010 to /dev/raw/raw34 channel ORA_DISK_1: restoring datafile 00011 to /dev/raw/raw35 channel ORA_DISK_1: restoring datafile 00012 to /dev/raw/raw36 channel ORA_DISK_1: restoring datafile 00013 to /dev/raw/raw37 channel ORA_DISK_1: restoring datafile 00014 to /dev/raw/raw38 channel ORA_DISK_1: reading from backup piece /oracle/app/11.2.0/db/dbs/03tq9bke_1_1 channel ORA_DISK_1: piece handle=/oracle/app/11.2.0/db/dbs/03tq9bke_1_1 tag=TAG20190219T135214 channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: restore complete, elapsed time: 00:00:03 Finished restore at 19-FEB-19 |
리스토어 시 alert 로그
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 | Tue Feb 19 14:28:18 2019 Full restore complete of datafile 6 /dev/raw/raw30. Elapsed time: 0:00:01 checkpoint is 13242625 last deallocation scn is 3 Full restore complete of datafile 7 /dev/raw/raw31. Elapsed time: 0:00:01 checkpoint is 13242625 last deallocation scn is 3 Full restore complete of datafile 8 /dev/raw/raw32. Elapsed time: 0:00:01 checkpoint is 13242625 last deallocation scn is 3 Full restore complete of datafile 9 /dev/raw/raw33. Elapsed time: 0:00:01 checkpoint is 13242625 last deallocation scn is 3 Full restore complete of datafile 11 /dev/raw/raw35. Elapsed time: 0:00:01 checkpoint is 13242625 last deallocation scn is 3 Full restore complete of datafile 12 /dev/raw/raw36. Elapsed time: 0:00:01 checkpoint is 13242625 last deallocation scn is 3 Full restore complete of datafile 13 /dev/raw/raw37. Elapsed time: 0:00:01 checkpoint is 13242625 last deallocation scn is 3 Full restore complete of datafile 14 /dev/raw/raw38. Elapsed time: 0:00:01 checkpoint is 13242625 last deallocation scn is 3 Full restore complete of datafile 10 /dev/raw/raw34. Elapsed time: 0:00:02 checkpoint is 13242625 last deallocation scn is 3 |
파티션 테이블 복구 작업 진행
1 2 3 4 5 6 7 8 9 | RMAN> recover tablespace part_reco; Starting recover at 19-FEB-19 using channel ORA_DISK_1 starting media recovery media recovery complete, elapsed time: 00:00:07 Finished recover at 19-FEB-19 |
복구 작업 시 alert 로그
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | Tue Feb 19 14:28:48 2019 alter database recover datafile list clear Completed: alter database recover datafile list clear alter database recover if needed tablespace PART_RECO Media Recovery Start Serial Media Recovery started Recovery of Online Redo Log: Thread 2 Group 4 Seq 313 Reading mem 0 Mem# 0: /dev/raw/raw15 Recovery of Online Redo Log: Thread 2 Group 5 Seq 314 Reading mem 0 Mem# 0: /dev/raw/raw16 Recovery of Online Redo Log: Thread 2 Group 6 Seq 315 Reading mem 0 Mem# 0: /dev/raw/raw17 Media Recovery Complete (ORCLDB2) Completed: alter database recover if needed tablespace PART_RECO |
DB OPEN 상태로 변경
1 2 3 | RMAN> sql'alter database open'; sql statement: alter database open |
테이블 데이터 확인
1 2 3 4 5 | SQL> select count(*) from tb_sale COUNT(*) ---------- 47 |
복구 완료 후 파티션 테이블 데이터 조회 시 장애 직전까지 입력한 데이터는 정상 조회 가능
insert into tb_sale select * from tb_sale 명령으로
복제 중이던 데이터는 작업이 정상적으로 마무리 되지 않았으므로 해당 트랜젝션 전부 rollback됨
별도의 USERS 테이블스페이스 사용중이던 계정은 작업 내역 확인
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | SQL> conn scott/tiger select * from tab; TNAME TABTYPE CLUSTERID -------------------- ------- ---------- EMP2 TABLE (null) TABTABLE TABLE (null) select count(*) from emp2; COUNT(*) ---------- 28 |
손실 없이 정상적으로 확인됨
참조 : https://blog.naver.com/latetree
'ORACLE > Backup&Recover' 카테고리의 다른 글
오라클 rman에서 show all 정보와 list backup 정보는 어디있을까? (0) | 2020.02.24 |
---|---|
rac db(asm)에서 특정시점의 데이터를 single db(filesystem) 복구 테스트 (0) | 2020.02.23 |
datapump stop_job 후 재기동 후 start_job (0) | 2019.01.08 |
hot backup 복구 시나리오2 (0) | 2018.12.19 |
hot backup 복구 시나리오1 (0) | 2018.12.17 |