내맘대로긍정이 알려주는
Oracle 23ai 신기능
무료 세미나 발표자료
다운로드
trending_flat
OS환경 : Oracle Linux 6.8 (64bit)
DB 환경 : Oracle Database 11.2.0.4
방법 : 오라클 11g R2 rman 백업 후 until scn 을 이용한 복구 방법
rman 을 이용해 full 백업 후 데이터 생성 삭제 후 원하는 scn으로 이동하는 시나리오
rman full 백업
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
|
RMAN> backup database;
Starting backup at 23-APR-21
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=16 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=00002 name=/oracle/app/oracle/oradata/ORCL11/sysaux01.dbf
input datafile file number=00004 name=/oracle/app/oracle/oradata/ORCL11/users01.dbf
input datafile file number=00003 name=/oracle/app/oracle/oradata/ORCL11/undotbs01.dbf
input datafile file number=00001 name=/oracle/app/oracle/oradata/ORCL11/system01.dbf
channel ORA_DISK_1: starting piece 1 at 23-APR-21
channel ORA_DISK_1: finished piece 1 at 23-APR-21
piece handle=/oracle/app/oracle/product/11.2.0/db_1/dbs/0svt21m0_1_1 tag=TAG20210423T222920 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:07
Finished backup at 23-APR-21
Starting Control File and SPFILE Autobackup at 23-APR-21
piece handle=/oracle/app/oracle/product/11.2.0/db_1/dbs/c-13364092-20210423-02 comment=NONE
Finished Control File and SPFILE Autobackup at 23-APR-21
|
테이블 생성 및 count, scn 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
SQL> create table reco as select * from dba_objects;
Table created.
SQL> select count(*) from reco;
COUNT(*)
----------
13632
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
12233775
|
데이터 delete 및 count, scn 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
|
SQL> delete reco where rownum <= 10000;
10000 rows deleted.
SQL> commit;
Commit complete.
SQL> select count(*) from reco;
COUNT(*)
----------
3632
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
12233788
|
데이터 insert 및 count, scn 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
|
SQL> insert into reco select * from dba_objects;
13632 rows created.
SQL> commit;
Commit complete.
SQL> select count(*) from reco;
COUNT(*)
----------
17264
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
12233796
|
db 강제종료
|
1
2
|
SQL> shutdown abort
ORACLE instance shut down.
|
db를 데이터 delete 이후 시점으로 복구하고자함
mount 기동
|
1
2
3
4
5
6
7
8
9
|
SQL> startup mount
ORACLE instance started.
Total System Global Area 2137886720 bytes
Fixed Size 2254952 bytes
Variable Size 1442842520 bytes
Database Buffers 687865856 bytes
Redo Buffers 4923392 bytes
Database mounted.
|
rman 접속 후 database restore
|
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
|
$ rman target /
Recovery Manager: Release 11.2.0.4.0 - Production on Fri Apr 23 22:31:07 2021
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL11 (DBID=13364092, not open)
RMAN> restore database;
Starting restore at 23-APR-21
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=18 device type=DISK
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 00001 to /oracle/app/oracle/oradata/ORCL11/system01.dbf
channel ORA_DISK_1: restoring datafile 00002 to /oracle/app/oracle/oradata/ORCL11/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /oracle/app/oracle/oradata/ORCL11/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /oracle/app/oracle/oradata/ORCL11/users01.dbf
channel ORA_DISK_1: reading from backup piece /oracle/app/oracle/product/11.2.0/db_1/dbs/0svt21m0_1_1
channel ORA_DISK_1: piece handle=/oracle/app/oracle/product/11.2.0/db_1/dbs/0svt21m0_1_1 tag=TAG20210423T222920
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:15
Finished restore at 23-APR-21
|
recover database until scn 실행(delete 이후 시점 scn 12233788)
|
1
2
3
4
5
6
7
8
9
|
RMAN> recover database until scn 12233788;
Starting recover at 23-APR-21
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:00:00
Finished recover at 23-APR-21
|
sqlplus 에서 alter database open resetlogs 로 기동
|
1
2
3
|
SQL> alter database open resetlogs;
Database altered.
|
데이터 확인
|
1
2
3
4
5
|
SQL> select count(*) from reco;
COUNT(*)
----------
3632
|
정상적으로 delete 후 시점으로 와짐
'ORACLE > Backup&Recover' 카테고리의 다른 글
| 오라클 11g R2 에서 오라클 19c Rman 백업셋 이용 업그레이드 방법 (4) | 2021.06.10 |
|---|---|
| 오라클 11g R2 hot 백업 후 until scn 을 이용한 복구 방법 (0) | 2021.04.23 |
| 오라클 11g R2 엔진 백업 복구 테스트(windows) (0) | 2021.02.06 |
| 오라클 DDL(drop 등)도 로그마이너로 복구가 될까? (0) | 2021.01.31 |
| 오라클 11g R2 datapump db full 백업 (2) | 2021.01.27 |