행위

오라클 복구 RMAN 무정지 복구

DB CAFE



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

  1. 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.