오라클 복구 RMAN 무정지 복구
DB CAFE
- DBA { Oracle DBA 명령어 > DBA 초급 과정 > DBA 고급 과정 }
- 튜닝 { 오라클 튜닝 목록 }
- 모델링 { 데이터 모델링 가이드 }
RMAN Backup, duplicate database 로 무정지 복구_11g
Oracle-Backup & Recovery 2015.04.27 16:32
- 장애상황 : 운영DB에서 테이블을 삭제, 삭제전 RMAN백업된 상황
- 조치 : RMAN-duplicate database로 drop table 전 시점으로 DB duplicating >> 드랍된 테이블 exp/imp
- pfile사용 중
- 운영DB Archive Log Mode로 운영중이어야 함
mkdir -p /data/rman/
rman target /
backup as compressed backupset database format '/data/rman/data_%U_%T' database;
sqlplus / as sysdba
create table scott.gogak (
no number,
name varchar2(10),
addr varchar2(20)
);
insert into scott.gogak values(1, '서진수', '서울 강남구');
insert into scott.gogak values(2, '김범석', '경기도 부천시');
insert into scott.gogak values(3, '김번준', '경기도 부천시');
commit;
select * from scott.gogak;
alter system switch logfile;
/
/
/
drop table scott.gogak purge;
select to_char(sysdate, 'yyyy-mm-dd:hh24:mi:ss') from dual;
TO_CHAR(SYSDATE,'YY
2015-04-13:17:59:41
@dbf
!
cp $ORACLE_HOME/dbs/inittestdb.ora $ORACLE_HOME/dbs/initclone.ora
vi $ORACLE_HOME/dbs/initclone.ora
(수정)
testdb -> clone
- memory_target 주석처리
(추가)
db_file_name_convert('/app/oracle/oradata/orcl/', '/data/db/clone/')
log_file_name_convert('/app/oracle/oradata/orcl/', '/data/db/clone/')
(oradata가 아닌 다른경로에도 dbf가있으면 그대로 복사해서 추가)
mkdir -p /data/db/clone/
mkdir -p /app/oracle/admin/clone/adump
mkdir -p /app/oracle/clone/admin/clone/dpdump
cd /data/arc1
cp -av* /data/rman/
cd /data/rman
ls -l
export ORACLE_SID=clone
export NLS_DATE_FORMAT='YYYY-MM-DD:HH24:MI:SS'
sqlplus / as sysdba
startup nomount
exit
rman auxiliary /
duplicate database to 'clone'
pfile='/app/oracle/product/11g/dbs/initclone.ora'
nofilenamecheck
backup location '/data/rman'
until time '2015-04-13:17:59:29';
- 실패 시 pfile 수정하면 RMAN 생성한 spfileclone.ora를 지우고 startup nomount 해야한다.
>> RMAN으로 Full 백업
[oracle@server130 ~]$ rman target /
Recovery Manager: Release 11.2.0.1.0 - Production on Mon Apr 13 17:21:07 2015
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL (DBID=1401667982)
RMAN> backup as compressed backupset database;
Starting backup at 13-APR-15
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=33 device type=DISK
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/app/oracle/oradata/orcl/system01.dbf
input datafile file number=00002 name=/app/oracle/oradata/orcl/sysaux01.dbf
input datafile file number=00003 name=/app/oracle/oradata/orcl/undotbs01.dbf
input datafile file number=00004 name=/app/oracle/oradata/orcl/users01.dbf
input datafile file number=00005 name=/app/oracle/oradata/orcl/example01.dbf
channel ORA_DISK_1: starting piece 1 at 13-APR-15
channel ORA_DISK_1: finished piece 1 at 13-APR-15
piece handle=/data/backup/rman/0iq49ush_1_1_20150413 tag=TAG20150413T172121 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:05:46
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
channel ORA_DISK_1: starting piece 1 at 13-APR-15
channel ORA_DISK_1: finished piece 1 at 13-APR-15
piece handle=/data/backup/rman/0jq49v7c_1_1_20150413 tag=TAG20150413T172121 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
Finished backup at 13-APR-15
RMAN> exit
Recovery Manager complete.
>> drop table 장애 만듦 - 시각 알고 있음
[oracle@server130 ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Mon Apr 13 17:30:51 2015
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning option
SYS>create table scott.gogak (
no number,
name varchar2(10),
addr varchar2(20)
); 2 3 4 5
Table created.
SYS>insert into scott.gogak values(1, '서진수', '서울 강남구');
insert into scott.gogak values(2, '김범석', '경기도 부천시');
insert into scott.gogak values(3, '김번준', '경기도 부천시');
commit;
1 row created.
SYS>
1 row created.
SYS>
1 row created.
SYS>
Commit complete.
SYS>select * from scott.gogak;
NO NAME ADDR
---------- --------------------
1 서진수 서울 강남구
2 김범석 경기도 부천시
3 김번준 경기도 부천시
1 row created.
SYS>commit;
select * from scott.gogak;
Commit complete.
SYS>alter system switch logfile;
System altered.
SYS>/
/
System altered.
SYS>
System altered.
SYS>/
System altered.
SYS>select to_char(sysdate, 'yyyy-mm-dd:hh24:mi:ss') from dual;
TO_CHAR(SYSDATE,'YY
2015-04-13:17:35:13
SYS>drop table scott.gogak purge;
Table dropped.
SYS>!
>> Archive Log 파일 복사
[oracle@server130 ~]$ cp $ORACLE_HOME/dbs/inittestdb.ora $ORACLE_HOME/dbs/initclone.ora
[oracle@server130 ~]$ vi $ORACLE_HOME/dbs/initclone.ora
clone.__db_cache_size=171966464
clone.__java_pool_size=4194304
clone.__large_pool_size=4194304
clone.__oracle_base='/app/oracle'#ORACLE_BASE set from environment
clone.__pga_aggregate_target=33554432
clone.__sga_target=390070272
clone.__shared_io_pool_size=0
clone.__shared_pool_size=201326592
clone.__streams_pool_size=0
- .audit_file_dest='/app/oracle/admin/clone/adump'
- .audit_trail='db'
- .compatible='11.2.0.0.0'
- .control_files='/data/db/clone/control01.ctl'
- .db_block_size=8192
- .db_domain=
- .db_name='clone'
- .db_recovery_file_dest='/app/oracle/flash_recovery_area'
- .db_recovery_file_dest_size=4039114752
- .diagnostic_dest='/app/oracle'
- .log_archive_dest_1='location=/data/arc1'
- .log_archive_dest_2='location=/data/arc2'
- .log_archive_dest_state_1='ENABLE'
- .log_archive_dest_state_2='ENABLE'
- .log_archive_format='%s_%t_%r.arc'
- .memory_target=423624704
- .open_cursors=300
- .processes=150
- .remote_login_passwordfile='EXCLUSIVE'
- .resource_limit=TRUE
- .sga_target=390070272
- .undo_tablespace='UNDOTBS1'
db_file_name_convert=('/app/oracle/oradata/orcl/', '/data/db/clone/')
log_file_name_convert=('/app/oracle/oradata/orcl/', '/data/db/clone/')
[oracle@server130 ~]$ mkdir -p /data/db/clone/
[oracle@server130 ~]$ mkdir -p /app/oracle/admin/clone
[oracle@server130 ~]$ mkdir -p /app/oracle/admin/clone/adump
[oracle@server130 ~]$ mkdir -p /app/oracle/admin/clone/dpdump
[oracle@server130 ~]$ cd /data/arc1
[oracle@server130 arc1]$ ls
10_1_876712211.arc 13_1_876712211.arc 16_1_876712211.arc 6_1_876712211.arc
11_1_876712211.arc 14_1_876712211.arc 17_1_876712211.arc 7_1_876712211.arc
12_1_876712211.arc 15_1_876712211.arc 5_1_876712211.arc 8_1_876712211.arc
[oracle@server130 arc1]$ cp -av * /data/rman/
`10_1_876712211.arc' -> `/data/rman/10_1_876712211.arc'
`11_1_876712211.arc' -> `/data/rman/11_1_876712211.arc'
`12_1_876712211.arc' -> `/data/rman/12_1_876712211.arc'
`13_1_876712211.arc' -> `/data/rman/13_1_876712211.arc'
`14_1_876712211.arc' -> `/data/rman/14_1_876712211.arc'
`15_1_876712211.arc' -> `/data/rman/15_1_876712211.arc'
`16_1_876712211.arc' -> `/data/rman/16_1_876712211.arc'
`17_1_876712211.arc' -> `/data/rman/17_1_876712211.arc'
`5_1_876712211.arc' -> `/data/rman/5_1_876712211.arc'
`6_1_876712211.arc' -> `/data/rman/6_1_876712211.arc'
`7_1_876712211.arc' -> `/data/rman/7_1_876712211.arc'
`8_1_876712211.arc' -> `/data/rman/8_1_876712211.arc'
[oracle@server130 arc1]$ cd /data/rman
[oracle@server130 rman]$ ls -l
합계 490604
-rw-r----- 1 oracle oinstall 33562624 4월 13 17:32 10_1_876712211.arc
-rw-r----- 1 oracle oinstall 1024 4월 13 17:32 11_1_876712211.arc
-rw-r----- 1 oracle oinstall 2560 4월 13 17:32 12_1_876712211.arc
-rw-r----- 1 oracle oinstall 15360 4월 13 17:33 13_1_876712211.arc
-rw-r----- 1 oracle oinstall 1001984 4월 13 17:59 14_1_876712211.arc
-rw-r----- 1 oracle oinstall 5632 4월 13 17:59 15_1_876712211.arc
-rw-r----- 1 oracle oinstall 3072 4월 13 17:59 16_1_876712211.arc
-rw-r----- 1 oracle oinstall 1536 4월 13 17:59 17_1_876712211.arc
-rw-r----- 1 oracle oinstall 42873344 4월 12 18:16 5_1_876712211.arc
-rw-r----- 1 oracle oinstall 47059456 4월 12 20:12 6_1_876712211.arc
-rw-r----- 1 oracle oinstall 41060864 4월 12 23:21 7_1_876712211.arc
-rw-r----- 1 oracle oinstall 13919232 4월 12 23:58 8_1_876712211.arc
-rw-r----- 1 oracle oinstall 321200128 4월 13 17:53 data_0lq4a0g2_1_1_20150413
-rw-r----- 1 oracle oinstall 1114112 4월 13 17:58 data_0nq4a11k_1_1_20150413
>> Clone DB nomount
[oracle@server130 rman]$ export ORACLE_SID=clone
[oracle@server130 rman]$ export NLS_DATE_FORMAT='YYYY-MM-DD:HH24:MI:SS'
[oracle@server130 rman]$ sqlplus / as sysdba.
SQL*Plus: Release 11.2.0.1.0 Production on Mon Apr 13 18:17:14 2015
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup nomount
ORACLE instance started.
Total System Global Area 389189632 bytes
Fixed Size 1336736 bytes
Variable Size 209717856 bytes
Database Buffers 171966464 bytes
Redo Buffers 6168576 bytes
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning option
>> RMAN duple db 수행
[oracle@server130 rman]$ rman auxiliary /
Recovery Manager: Release 11.2.0.1.0 - Production on Mon Apr 13 21:01:33 2015
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to auxiliary database: CLONE (not mounted)
RMAN> duplicate database to 'clone'
pfile='/app/oracle/product/11g/dbs/initclone.ora'
nofilenamecheck
backup location '/data/rman'
until time '2015-04-13:17:59:29';2> 3> 4> 5>
Starting Duplicate Db at 2015-04-13:21:01:34
contents of Memory Script:
{
sql clone "create spfile from memory";
}
executing Memory Script
sql statement: create spfile from memory
contents of Memory Script:
{
shutdown clone immediate;
startup clone nomount;
}
executing Memory Script
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 389189632 bytes
Fixed Size 1336736 bytes
Variable Size 130026080 bytes
Database Buffers 251658240 bytes
Redo Buffers 6168576 bytes
contents of Memory Script:
{
sql clone "alter system set db_name =
ORCL comment=
Modified by RMAN duplicate scope=spfile";
sql clone "alter system set db_unique_name =
CLONE comment=
Modified by RMAN duplicate scope=spfile";
shutdown clone immediate;
startup clone force nomount
restore clone primary controlfile from '/data/rman/data_0nq4a11k_1_1_20150413';
alter clone database mount;
}
executing Memory Script
sql statement: alter system set db_name = ORCL comment= Modified by RMAN duplicate scope=spfile
sql statement: alter system set db_unique_name = CLONE comment= Modified by RMAN duplicate scope=spfile
Oracle instance shut down
Oracle instance started
Total System Global Area 389189632 bytes
Fixed Size 1336736 bytes
Variable Size 130026080 bytes
Database Buffers 251658240 bytes
Redo Buffers 6168576 bytes
Starting restore at 2015-04-13:21:01:46
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=19 device type=DISK
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/data/db/clone/control01.ctl
Finished restore at 2015-04-13:21:01:47
database mounted
released channel: ORA_AUX_DISK_1
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=19 device type=DISK
contents of Memory Script:
{
set until scn 2364931;
set newname for datafile 1 to
"/data/db/clone/system01.dbf";
set newname for datafile 2 to
"/data/db/clone/sysaux01.dbf";
set newname for datafile 3 to
"/data/db/clone/undotbs01.dbf";
set newname for datafile 4 to
"/data/db/clone/users01.dbf";
set newname for datafile 5 to
"/data/db/clone/example01.dbf";
restore
clone database
;
}
executing Memory Script
executing command: SET until clause
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting restore at 2015-04-13:21:01:52
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 /data/db/clone/system01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00002 to /data/db/clone/sysaux01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00003 to /data/db/clone/undotbs01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00004 to /data/db/clone/users01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00005 to /data/db/clone/example01.dbf
channel ORA_AUX_DISK_1: reading from backup piece /data/rman/data_0lq4a0g2_1_1_20150413
channel ORA_AUX_DISK_1: piece handle=/data/rman/data_0lq4a0g2_1_1_20150413 tag=TAG20150413T174849
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:02:56
Finished restore at 2015-04-13:21:04:48
contents of Memory Script:
{
switch clone datafile all;
}
executing Memory Script
datafile 1 switched to datafile copy
input datafile copy RECID=6 STAMP=876949488 file name=/data/db/clone/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=7 STAMP=876949488 file name=/data/db/clone/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=8 STAMP=876949488 file name=/data/db/clone/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=9 STAMP=876949488 file name=/data/db/clone/users01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=10 STAMP=876949488 file name=/data/db/clone/example01.dbf
contents of Memory Script:
{
set until time "2015-04-13:17:59:29";
recover
clone database
delete archivelog
;
}
executing Memory Script
executing command: SET until clause
Starting recover at 2015-04-13:21:04:49
using channel ORA_AUX_DISK_1
starting media recovery
archived log for thread 1 with sequence 14 is already on disk as file /data/rman/14_1_876712211.arc
archived log for thread 1 with sequence 15 is already on disk as file /data/rman/15_1_876712211.arc
archived log for thread 1 with sequence 16 is already on disk as file /data/rman/16_1_876712211.arc
archived log for thread 1 with sequence 17 is already on disk as file /data/rman/17_1_876712211.arc
archived log file name=/data/rman/14_1_876712211.arc thread=1 sequence=14
archived log file name=/data/rman/15_1_876712211.arc thread=1 sequence=15
archived log file name=/data/rman/16_1_876712211.arc thread=1 sequence=16
archived log file name=/data/rman/17_1_876712211.arc thread=1 sequence=17
media recovery complete, elapsed time: 00:00:02
Finished recover at 2015-04-13:21:04:52
contents of Memory Script:
{
shutdown clone immediate;
startup clone nomount pfile= '/app/oracle/product/11g/dbs/initclone.ora';
}
executing Memory Script
database dismounted
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 389189632 bytes
Fixed Size 1336736 bytes
Variable Size 209717856 bytes
Database Buffers 171966464 bytes
Redo Buffers 6168576 bytes
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "CLONE" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 5
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 ( '/data/db/clone/redo01_a.log', '/data/db/clone/redo01_b.log' ) SIZE 50 M REUSE,
GROUP 2 ( '/data/db/clone/redo02_a.log', '/data/db/clone/redo02_b.log' ) SIZE 50 M REUSE,
GROUP 3 ( '/data/db/clone/redo03_a.log', '/data/db/clone/redo03_b.log' ) SIZE 50 M REUSE
DATAFILE
'/data/db/clone/system01.dbf'
CHARACTER SET KO16MSWIN949
contents of Memory Script:
{
set newname for tempfile 1 to
"/data/db/clone/temp.dbf";
switch clone tempfile all;
catalog clone datafilecopy "/data/db/clone/sysaux01.dbf",
"/data/db/clone/undotbs01.dbf",
"/data/db/clone/users01.dbf",
"/data/db/clone/example01.dbf";
switch clone datafile all;
}
executing Memory Script
executing command: SET NEWNAME
renamed tempfile 1 to /data/db/clone/temp.dbf in control file
cataloged datafile copy
datafile copy file name=/data/db/clone/sysaux01.dbf RECID=1 STAMP=876949509
cataloged datafile copy
datafile copy file name=/data/db/clone/undotbs01.dbf RECID=2 STAMP=876949509
cataloged datafile copy
datafile copy file name=/data/db/clone/users01.dbf RECID=3 STAMP=876949509
cataloged datafile copy
datafile copy file name=/data/db/clone/example01.dbf RECID=4 STAMP=876949509
datafile 2 switched to datafile copy
input datafile copy RECID=1 STAMP=876949509 file name=/data/db/clone/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=876949509 file name=/data/db/clone/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=3 STAMP=876949509 file name=/data/db/clone/users01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=4 STAMP=876949509 file name=/data/db/clone/example01.dbf
contents of Memory Script:
{
Alter clone database open resetlogs;
}
executing Memory Script
database opened
Finished Duplicate Db at 2015-04-13:21:05:43
RMAN> exit
>> 복구된 Clone DB의 gogak table exp >> 운영 DB에 imp
[oracle@server130 rman]$ ORACLE_SID=clone
[oracle@server130 rman]$
[oracle@server130 rman]$
[oracle@server130 rman]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Mon Apr 13 21:07:57 2015
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning option
SQL> select * from scott.gogak;
NO NAME ADDR
---------- --------------------
1 서진수 서울 강남구
2 김범석 경기도 부천시
3 김번준 경기도 부천시
SQL> !
[oracle@server130 ~]$ exp scott/tiger file='/data/dp/gogaktbl.dmp' tables='gogak';
Export: Release 11.2.0.1.0 - Production on Mon Apr 13 22:15:10 2015
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning option
Export done in KO16MSWIN949 character set and AL16UTF16 NCHAR character set
About to export specified tables via Conventional Path ...
. . exporting table GOGAK 3 rows exported
Export terminated successfully without warnings.
[oracle@server130 ~]$ exp scott/tiger file='/data/dp/gogaktbl.dmp' tables='gogak';
[oracle@server130 ~]$
[oracle@server130 ~]$ [oracle@server130 ~]$ mkdir -p /app/oracle/admin/clone/adump
bash: [oracle@server130: command not found
[oracle@server130 ~]$
[oracle@server130 ~]$
[oracle@server130 ~]$
[oracle@server130 ~]$
[oracle@server130 ~]$
[oracle@server130 ~]$ vi /app/oracle/product/11g/dbs/initclone.ora
[oracle@server130 ~]$
[oracle@server130 ~]$ORACLE_SID=testdb
[oracle@server130 ~]$ imp scott/tiger file='/data/dp/gogaktbl.dmp'
Import: Release 11.2.0.1.0 - Production on Mon Apr 13 22:38:01 2015
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning option
Export file created by EXPORT:V11.02.00 via conventional path
import done in KO16MSWIN949 character set and AL16UTF16 NCHAR character set
. importing SCOTT's objects into SCOTT
. importing SCOTT's objects into SCOTT
. . importing table "GOGAK" 3 rows imported
Import terminated successfully without warnings.