Goodus 기술노트 [40 회 ] Recovery Manager(RMAN) Author Author Job Title 김상국 차장 Creation Date 2009-04-30 Last Updated 2009-04-30 Version 1.0 Copyright(C) 2004 Goodus Inc. All Rights Reserved
Contents 1. Oracle Recovery Manager(RMAN)... 3 1.1. RMAN이란?...3 1.2. RMAN의특징...4 1.3. RMAN의 Channel과 Media Management...4 1.4. RMAN CONFIGURE COMMAND...6 1.5. SHOW 명령어...7 1.6. LIST명령어...8 1.7. REPORT명령어...8 2. RECOVERY CATALOG... 8 3. RMAN without a Recovery Catalog(Backup & Recovery)... 9 4. RMAN with a Recovery Catalog...10 4.1. Recovery Catalog Setup...10 4.2. Recovery Catalog를이용한 Rman Backup(Archive mode)...12 4.3. Incremental Backup...19 5. RMAN with Recovery(Datafile,Tablespace)...20 5.1. Rman 을통한 Recovery 시나리오 ( 예제 )...22 6. Rman Recovery 를이용한 NEW 서버에 Restore...25 7. Rman Backup 을위한 Script create...28 8. Rman Repository 의유지관리...30 8.1. Removing Obsolete Backupsets...30 9. RAC 환경에서의 RMAN Backup...31 10. Migration using TTS with RMAN [ 이기종 Endian[ - Linux (Little) HP-UX (Big) 10G 이상 ]...33 Step1) Transportable Tablespace 사용을위한 Relation Check... 35 Step2) 해당 Transportable Tablespace 를 Read-only mode 로변경한다.... 35 Step3) rman 을이용한 datafile convert 작업... 35 Step4) 해당 tablespace 를 export 한다... 36 Step5) target DB 환경구성 (user)... 37 Step6) target DB 해당 tablespace Plugging 한다.... 37 Step7) 해당 tablespace Read-Write mode 로변경한다.... 37 11. Recovery Catalog Tables & Views...37 12. 기타유용한 Script & 명령어정리...39-2 - 본산출물의내용은굿어스 의사전승인없이 외부유출및공개를금지합니다.
1. Oracle Recovery Manager(RMAN) 1.1. RMAN 이란? recovery manager (RMAN) 은오라클에서제공하는 backup/recovery solution이다. RMAN을이용하여많은 backup vender에서는 solution을제공하고있다. oracle도 EM을통해 RMAN backup을지원하고있다. rman은 oracle datafile, control file, archive file 을 backup할수있다. 그러나 online redo log에대해서는 backup을하지못하므로 archive 화하여 backup을한다. 또 init file이나 password file도 backup을하지못한다. 10g에서는 spfile backup이가능하게되었다. archive mode는물론 no-archive mode에대해 backup도가능하다. 하지만 no-archive mode에서의 backup시 target database는 open 상태이면당연히안된다. rman은 catalog를이용하는운영방법과 target database의 controlfile을이용하는방법이있으며, 오라클에서는 catalog를이용하는방법을강력히권고한다. control file을이용하는경우복구가어려울수있으며, resync 등의작업들이필요하게된다 rman은 Incremental Backup을지원하므로 backup 정책을유연하게수립할수있다. 만약다음과같이 backup 정책을수립했다고하면.. 일요일에 level 0로 full backup, 월요일에는 level 2로일요일 backup시점이후변동사항만을 incremental backup, 화요일에는 level 2로월요일 backup 시점이후변동사항만을 incremental backup, 수요일에는 level 1으로일요일 backup 시점이후변동사항만을 incremental backup, 목요일에는 level 2로수요일 backup 시점이후변동사항만을 incremental backup. 금요일에는 level 2로목요일 backup 시점이후변동사항만을 incremental backup 만약토요일에장애가발생하면, 일요일, 수요일, 목요일, 금요일의 backup을이용하여 recovery하면된다. 만약 level 1 incremental backup을이틀에한번정도할수있다면 recovery 시간은더단축할수있다. - 3 -
1.2. RMAN 의특징 DB 전체, Tablespace 단위,Database files, Archive logs, 그리고 Control files 들을 Backup 자주실행되는 operation 들은 script 로저장하여간단하게실행할수있다. Incremental block level backup 을할수있다. 사용되어지지않은 database block 들은 skip 한다. Backup /resotre 시각 block 에대한 checksum 을통해 Corrupted block 을 detection Online file 을 backup 할때, tablespace 를 backup mode 로할필요가없다. Backup performance 향상 (Parallelization, less redo log 생성 ) OS 의 open file limit 을피하기위해 open file limit 을지정할수있으며, backup 의사이즈의 limit 을줄수있다. 또한 file 당, second 당 reads 를지정해서부하를조정할수있다. RMAN 의메모리사용 RMAN 을사용할시주의해야하는것은 shared pool 과 large pool 입니다. RMAN 은몇개의 Oracle PL/SQL 패키지들을기존의 PL/SQL 패키지들과마찬가지로 Shared Pool 에올려사용합니다. 이때 Shared Pool 의여유공간이부족하거나단편화현상이심할시에는 RMAN 패키지가실행되지않을수가있습니다. 항상 Shared Pool 내부에 RMAN 의실행에충분한메모리가존재해야합니다. RMAN 의주요용어와개념 - Target database backup,restore,recovery action 이수행될대상데이타베이스입니다. - Recovery Catalog RMAN 에서사용하는 Information 저장장소입니다. target database 의물리적스키마, datafile 과 archivelog 의 backup sets 과 pieces, backup script 등을포함하고있습니다. - channel allocation channel 은 target database 의 backup,restore,recover 에대한 server process 초기화를합니다. 즉, 이 channel 은 disk 를포함기타 OS device 를지정하게되며, 이에따라 parallelization 의 degree 가결정됩니다. - Backup sets 하나또는그이상의 Datafiles 또는 Archivelogs 를포함하며,Backup pieces 의 Complate Set 으로, Full 또는 Incremental Backup 으로구성됩니다. Oracle proprietary format 을사용합니다. - Backup Pieces 하나의 Backup Set 은하나또는그이상의 Backup Pieces 로구성이되어지며, 각 Backup Piece 는 Single Output File 로 O/S 의 File system Size 의제한을갖고있습니다. 1.3. RMAN 의 Channel 과 Media Management Oracle RMAN을이용하여 DataBase백업시 Channel을할당하여야한다. Channel 할당방법은 allocate channel이라는명령어에의한 Manual Channel allocation과 Configure 명령어에의한 Automatic Channel allocation이있다. - 4 -
configure defautl device type to sbt; 에서 sbt 는 tape 장치를의미한다. 즉기본적인 default 백업장치를지정해주는것이다. RMAN 에서 Backup device 에 backup 을하기위해 Media Management Library 가필요하며 media vendor 가제공하는 libraray 로 Oracle 과의 interface 를제공한다. - 5 - 본산출물의내용은굿어스 의사전승인없이 외부유출및공개를금지합니다.
1.4. RMAN CONFIGURE COMMAND configure channel device type disk format '/db01/backup/%u'; 포멧을지정해준다. 자동채널할당참고 %d : db name, %s : backup set, %p : backup piece, %c : backup copy configure retention policy to recovery window of 7 days; 복구를위한백업을유지해주는기간설정 configure retention policy to redundancy 2; default 는 1 이다. configure retention policy clear ; retention policy 정보를 clear configure datafile backup copies form device type disk to 2; 백업의복사본을 2 개로만들겠다는설정명령. Format 에서 %c 가있어야중복되지않으므로에러가발생하지않는다. configure backup optimization on ; backup 시 optimize 시킴 configure retention policy clear; retention policy 를 clear (backup 유지기간설정을 clear) configure channel device type sbt clear ; configure RMAN 관련한 default configuration (note 305565.1 Persistent Controlfile configurations for RMAN in 9i and 10g. 참고 ) - CONFIGURE RETENTION POLICY TO REDUNDANCY 1; Backup 보관주기나 backup 본의갯수를설정합니다. - CONFIGURE BACKUP OPTIMIZATION OFF; 이미 backup 된동일한 (checkpoint SCN 등 ) datafile, archived redolog, backup set 이있다면 skip 합니다. - CONFIGURE DEFAULT DEVICE TYPE TO DISK; default backup device 를설정합니다. - 6 -
- CONFIGURE CONTROLFILE AUTOBACKUP OFF; RMAN 의 BACKUP 이나 COPY 명령등의수행후자동으로 control file backup 을수행합니다. - CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; autobackup 되는 control file 의기본 format 을변경합니다. - CONFIGURE DEVICE TYPE DISK PARALLELISM 1; 특정 device 에 automatic channel allocation 될때 channel 의갯수를지정합니다. - CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; DATAFILE, CONTROL FILE 의 backup set 의 copy 본갯수를지정합니다. - CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; ARCHIVELOG FILE 의 backup set 의 copy 본갯수를지정합니다. - CONFIGURE MAXSETSIZE TO UNLIMITED; backupset 의 maximum size 를설정합니다. - CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; (10g only) flash recovery area 의 archived redo log 에대한삭제여부를설정합니다. - CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/opt/oracle/product/ora10g/dbs/snapcf_db10g.f'; RMAN 은 resync 시생성되는임시 snapshot control file 의이름을지정한다. 1.5. SHOW 명령어 show all 명령을하면모두보인다. 아래는특정한것만볼경우사용을한다. - 7 -
1.6. LIST 명령어 list backup of database; 데이터베이스백업정보를출력한다. list backup of datafile ~ : 데이터파일백업정보를출력한다. List copy of tablespace system : system tablespace 가 copy 명령으로백업되었는지확인한다. 1.7. REPORT 명령어 report need backup incremental 3 ; incremental level 3 일경우백업이필요한지 report 참고 > backup database incremental level 0 이면전체백업 report need backup days 3 ;3 일이지났을경우백업이필요한것을 report ; 만약 retention policy 에의해서 backup 한것이기간이지났을경우, report 됨. 2. RECOVERY CATALOG Recovery Catalog 는 RMAN 에의해사용되어지며 Recovery Catalog 에저장되어있는정보를사용하여요청되어진 Backup 과 Resotre 를실행한다. Rman Backup 에있어서 Backup & Recovery 를용이하게하고속도를향상시키고, 동시에데이터손실위험을감소시키려면다음지침을따라야한다. 1. 대상데이터베이스에복구카탈로그 (Recovery Catalog) 를생성하지마십시요. 2. 별도의디스크상에파일을가진별개의 DB 를생성하라. 3. 백업해야할 DB 가많다면모든대상 DB 의정보를담아둘별도의 Recovery Catalog DB 를생성하라. - 8 -
4. Recovery Catalog 를백업하라. catalog 없이 RMAN 을사용할때의단점은 recovery catalog 의 overhead 가없는대신, Point-In-Time recovery 를쉽게할수없다. 또한, control file 손상시에 recovery 할수없고, stored script 를사용할수없다. 이에 RMAN 을사용하기에앞서 Recovery Catalog 를사용할지아니면 Target DataBase 의 Control file 을사용할지 (NoCatalog) 에대해결정을하여야한다. Oracle 은항상 Recovery Catalog 를사용하도록권장한다. Recovery Catalog 를사용하므로써 Backup 의 performance 를높일수있으며 Target Database 의 Controlfile 유실에대비해서도 Recovery 를효과적으로수행할수있다. 3. RMAN without a Recovery Catalog(Backup & Recovery) Recovery Catalog 없이 rman 을사용하여기본 configure 및 Backup & Recovery 를진행한다. [busan1 $ rman target / nocatalog Recovery Catalog 없이접속 Recovery Manager: Release 10.2.0.1.0 - Production on Mon Jan 1 23:45:47 2007 Copyright (c) 1982, 2005, Oracle. All rights reserved. connected to target database: PROD (DBID=28555911) using target database control file instead of recovery catalog RMAN> configure channel device type disk format '/u01/app/oracle/backup/prod_%u'; device 에대한 channel 을설정한다. RMAN> CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/u01/app/oracle/backup/control/%f'; controlfile Auto backup channel 설정 RMAN> CONFIGURE BACKUP OPTIMIZATION ON; Backup 최적화 option RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON; controlfile Auto Backup 설정 RMAN> configure retention policy to recovery window of 7 days; 백업보관주기설정 RMAN> show all 전체 configure 를보여준다. RMAN configuration parameters are: CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS; CONFIGURE BACKUP OPTIMIZATION ON; CONFIGURE CONTROLFILE AUTOBACKUP ON; CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/u01/app/oracle/backup/control/%f'; CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '/u01/app/oracle/backup/prod_%u'; RMAN> backup as compressed backupset database spfile plus archivelog delete input; 백업진행 Recovery TEST SQL>!rm /u01/app/oracle/oradata/prod/disk1/system01.dbf SQL> shutdown abort ORACLE instance shut down. SQL> startup ORACLE instance started. Total System Global Area 264241152 bytes Fixed Size 1977976 bytes Variable Size 104862088 bytes - 9 - Datafile 삭제
Database Buffers 150994944 bytes Redo Buffers 6406144 bytes Database mounted. ORA-01157: cannot identify/lock data file 1 - see DBWR trace file ORA-01110: data file 1: '/u01/app/oracle/oradata/prod/disk1/system01.dbf' 실제파일이없으므로장애유발 [busan1:oracle:/u01]$ rman target / nocatalog Recovery Manager: Release 10.2.0.1.0 - Production on Sat Jan 27 04:32:05 2007 Copyright (c) 1982, 2005, Oracle. All rights reserved. connected to target database: PROD (DBID=24173995, not open) RMAN> restore datafile 1; Starting restore at 27-JAN-07 allocated channel: ORA_DISK_1 channel ORA_DISK_1: sid=285 devtype=disk channel ORA_DISK_1: starting datafile backupset restore channel ORA_DISK_1: specifying datafile(s) to restore from backup set restoring datafile 00001 to /u01/app/oracle/oradata/prod/disk1/system01.dbf channel ORA_DISK_1: reading from backup piece /u01/app/oracle/backup/prod_1ni8hbo2_1_1 channel ORA_DISK_1: restored backup piece 1 piece handle=/u01/app/oracle/backup/prod_1ni8hbo2_1_1 tag=tag20070127t042433 channel ORA_DISK_1: restore complete, elapsed time: 00:01:46 Finished restore at 27-JAN-07 RMAN> recover datafile 1 Starting recover at 27-JAN-07 using channel ORA_DISK_1 starting media recovery media recovery complete, elapsed time: 00:00:06 Finished recover at 27-JAN-07 RMAN> alter database open; database opened ==>DB 가 open 되고서비스가정상가동됨 < 참고 > backupset 파일이름형식 - Format : 출력이름의형식 - %c : backup piece 의 copy number - %p : backup piece number, 1 부터시작하고 1 씩증가한다. - %s : backup set number, control file 내의 counter, set 이생성될때마다증가한다. - %d : database name - %n : database name, padded on the right with x char to total length oh 8 char - %t : fixed reference time 이후경과한시간 ( 초 ) 을 4byte 로나타낸값 %s 와조합하면 backup set 에 unique name 을지정할수있다. - %u : backup set 번호와생성시간에대한단축표기법, 8 자이름지정 - %U : %u_%p_%c (default) 4. RMAN with a Recovery Catalog 4.1. Recovery Catalog Setup Target DB(PROD) 와 Catalog DB(RESP) 초기 TNS Setting ===== tnsnames.ora=====(prod,resp DB 에동일하게 setup) PROD = Target DB (DESCRIPTION = - 10 -
) (ADDRESS = (PROTOCOL = TCP)(HOST = busan1)(port = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = PROD) ) RESP = Catalog DB (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = busan2)(port = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = RESP) ) ) ======================================================== busan1 $ tnsping resp target DB 쪽에서 Catalog DB 쪽으로 tnsping TEST(Ok) TNS Ping Utility for Solaris: Version 10.2.0.1.0 - Production on 01-JAN-2007 22:52:33 Copyright (c) 1997, 2005, Oracle. All rights reserved. Used parameter files: Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = busan2)(port = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = RESP))) OK (10 msec) busan2 $ tnsping PROD Catalog DB 쪽에서 Target DB 쪽으로 tnsping TEST (Ok) TNS Ping Utility for Solaris: Version 10.2.0.1.0 - Production on 01-JAN-2007 22:53:19 Copyright (c) 1997, 2005, Oracle. All rights reserved. Used parameter files: Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = busan1)(port = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = PROD))) OK (0 msec) Catalog DB(SID=RESP) 에 Tablespace 및 User 생성작업 busan2 $ sqlplus '/as sysdba' SQL*Plus: Release 10.2.0.1.0 - Production on Mon Jan 1 22:36:46 2007 Copyright (c) 1982, 2005, Oracle. All rights reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0-64bit Production With the Partitioning and Data Mining options SQL> create tablespace catalog datafile '/oratest/oradata/resp/catalog01.dbf' size 50m; Tablespace created. SQL> create user rman identified by rman default tablespace catalog; User created. SQL> grant recovery_catalog_owner to rman; Grant succeeded. SQL> grant connect,resource to rman; SQL> select instance_name from v$instance; INSTANCE_NAME ---------------- RESP Rman catalog DB 쪽에유저를생성한다. - 11 -
Target DB(PROD) 에 Recovery Catalog 생성작업 busan1 $ rman target / catalog rman/rman@resp Recovery Manager: Release 10.2.0.1.0 - Production on Mon Jan 1 22:46:01 2007 Copyright (c) 1982, 2005, Oracle. All rights reserved. connected to target database: PROD (DBID=28555911) connected to recovery catalog database RMAN> list incarnation of database; RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of list command at 01/01/2007 22:46:35 RMAN-06428: recovery catalog is not installed 아직까지 catalog 가생성되지않았음 RMAN> create catalog; recovery catalog created RMAN> register database; database registered in recovery catalog starting full resync of recovery catalog full resync complete RMAN> list incarnation of database; DataBase 등록확인 List of Database Incarnations DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time ------- ------- -------- ---------------- --- ---------- ---------- 1 2 PROD 28555911 CURRENT 1 08-MAR-07oracle 15298 14936 0 Jan16? <note> target database 가잘못등록된경우삭제방법 RESP DB 의 rman 유저로접속후 package 를사용하여삭제 busan2 $ sqlplus rman/rman SQL*Plus: Release 10.2.0.1.0 - Production on Mon Jan 1 23:15:54 2007 Copyright (c) 1982, 2005, Oracle. All rights reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0-64bit Production With the Partitioning and Data Mining options SQL> select * from db ; DB_KEY DB_ID HIGH_CONF_RECID LAST_KCCDIVTS HIGH_IC_RECID CURR_DBINC_KEY ---------- ---------- --------------- ------------- ------------- -------------- 1 28555911 616717575 1 2 SQL> execute dbms_rcvcat.unregisterdatabase(1,28555911); 잘못등록된 DB 삭제 (DB_KEY,DB_ID) PL/SQL procedure successfully completed. SQL> select * from db ; no rows selected 4.2. Recovery Catalog 를이용한 Rman Backup(Archive mode) Recovery Catalog 는 RMAN 의백업작업에대한메타데이터의저장소입니다. 쉽게말해컨트롤파일에서 RMAN 의백업과복구에관련된정보들만을따로모아생성시킨파일과도같다. 그리고이 Recovery Catalog 는대상데이터 - 12 -
베이스와 resync 명령어를사용하여해당정보를동기화할수있습니다. 이러한이점때문에 Recovery Catalog 는복수의데이터베이스에대한백업및복구작업을중앙관리하는데편리한이점을가진다. 이와같은 Recovery Catalog 의정보보호를위해직접적으로액세스하는것은권장되지않고. 대신 RC_* 의뷰를참조하여해당 Recovery Catalog 내의정보를참조할수있다. Recovery Catalog의사용을위해서는먼저대상데이터베이스에연결한후, 두번째 NET 연결을통하여 Recovery Catalog와의세션을생성하고, 이때 Recovery Catalog의연결은기존의 RMAN의연결과는달리 as sysdba의권한을사용하지않는다. 연결된후에는수동으로대상데이터베이스와동기화를시키거나, 백업작업의실행시자동으로동기화가이루어진다. Complete Database Backup(full) busan1 $ rman target / catalog rman/rman@resp Recovery Manager: Release 10.2.0.1.0 - Production on Wed Jan 3 22:04:36 2007 Copyright (c) 1982, 2005, Oracle. All rights reserved. connected to target database: PROD (DBID=28555911) connected to recovery catalog database 전체 DB 에대한백업을수행한다. 2> # backup the complete database to disk 3> allocate channel c1 type disk; 4> backup 5> full 6> tag full_db_sunday_night 7> format '/rman_backup/prod/db_t%t_s%s_p%p' 8> (database); 9> release channel c1; 10> allocated channel: c1 channel c1: sid=144 devtype=disk Starting backup at 08-JAN-07 channel c1: starting full datafile backupset channel c1: specifying datafile(s) in backupset input datafile fno=00001 name=/oratest/oradata/prod/system01.dbf input datafile fno=00002 name=/oratest/oradata/prod/undotbs01.dbf input datafile fno=00003 name=/oratest/oradata/prod/sysaux01.dbf input datafile fno=00005 name=/oratest/oradata/prod/catalog01.dbf input datafile fno=00004 name=/oratest/oradata/prod/users01.dbf channel c1: starting piece 1 at 08-JAN-07 channel c1: finished piece 1 at 08-JAN-07 piece handle=/rman_backup/prod/db_t611286673_s3_p1 tag=full_db_sunday_night comment=none channel c1: backup set complete, elapsed time: 00:00:55 channel c1: starting full datafile backupset channel c1: specifying datafile(s) in backupset including current control file in backupset channel c1: starting piece 1 at 08-JAN-07 channel c1: finished piece 1 at 08-JAN-07 piece handle=/rman_backup/prod/db_t611286729_s4_p1 tag=full_db_sunday_night comment=none channel c1: backup set complete, elapsed time: 00:00:02 Finished backup at 08-JAN-07-13 -
released channel: c1 RMAN> list backupset of database; 백업정보를확인할수있다 List of Backup Sets =================== BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------- 65 Full 276.27M DISK 00:00:49 08-JAN-07 BP Key: 67 Status: AVAILABLE Compressed: NO Tag: FULL_DB_SUNDAY_NIGHT Piece Name: /rman_backup/prod/db_t611286673_s3_p1 List of Datafiles in backup set 65 File LV Type Ckp SCN Ckp Time Name ---- -- ---- ---------- --------- ---- 1 Full 435856 08-JAN-07 /oratest/oradata/prod/system01.dbf 2 Full 435856 08-JAN-07 /oratest/oradata/prod/undotbs01.dbf 3 Full 435856 08-JAN-07 /oratest/oradata/prod/sysaux01.dbf 4 Full 435856 08-JAN-07 /oratest/oradata/prod/users01.dbf 5 Full 435856 08-JAN-07 /oratest/oradata/prod/catalog01.dbf Tablespace Backup(Users Tablespace Backup) busan1 $ rman target / catalog rman/rman@resp Recovery Manager: Release 10.2.0.1.0 - Production on Wed Jan 3 22:04:36 2007 Copyright (c) 1982, 2005, Oracle. All rights reserved. connected to target database: PROD (DBID=28555911) connected to recovery catalog database 2> allocate channel c1 type disk; 3> backup 4> tag tbs_users 5> format '/rman_backup/prod/tbs_users_t%t_s%s' 6> (tablespace users); 2 개이상의 tablespace 를사용하고자한다면 7> release channel c1; # (tablespace users,system) 분리해서입력이가능함 8> allocated channel: c1 channel c1: sid=145 devtype=disk Starting backup at 08-JAN-07 channel c1: starting full datafile backupset channel c1: specifying datafile(s) in backupset input datafile fno=00004 name=/oratest/oradata/prod/users01.dbf channel c1: starting piece 1 at 08-JAN-07 channel c1: finished piece 1 at 08-JAN-07 piece handle=/rman_backup/prod/tbs_users_t611362619_s5 tag=tbs_users comment=none channel c1: backup set complete, elapsed time: 00:00:04 Finished backup at 08-JAN-07 released channel: c1 RMAN> list backupset of tablespace users; users 라는 tablespace 백업사항확인 BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------- 100 Full 88.00K DISK 00:00:02 08-JAN-07 BP Key: 102 Status: AVAILABLE Compressed: NO Tag: TBS_USERS Piece Name: /rman_backup/prod/tbs_users_t611362619_s5 List of Datafiles in backup set 100-14 -
File LV Type Ckp SCN Ckp Time Name ---- -- ---- ---------- --------- ---- 4 Full 473587 08-JAN-07 /oratest/oradata/prod/users01.dbf Datafile backup busan1 $ rman target / catalog rman/rman@resp Recovery Manager: Release 10.2.0.1.0 - Production on Wed Jan 3 22:04:36 2007 Copyright (c) 1982, 2005, Oracle. All rights reserved. connected to target database: PROD (DBID=28555911) connected to recovery catalog database 2> allocate channel c1 type disk; 3> backup 4> format '/rman_backup/prod/df_%d_%u' 5> (datafile '/oratest/oradata/prod/sysaux01.dbf'); 6> release channel c1; 7> 또는 report schema 를확인후 file number 로도가능하다. RMAN> report schema 2> ; Report of database schema List of Permanent Datafiles =========================== File Size(MB) Tablespace RB segs Datafile Name ---- -------- -------------------- ------- ------------------------ 1 300 SYSTEM YES /oratest/oradata/prod/system01.dbf 2 200 UNDOTBS1 YES /oratest/oradata/prod/undotbs01.dbf 3 120 SYSAUX NO /oratest/oradata/prod/sysaux01.dbf 4 5 USERS NO /oratest/oradata/prod/users01.dbf 5 50 CATALOG NO /oratest/oradata/prod/catalog01.dbf List of Temporary Files ======================= File Size(MB) Tablespace Maxsize(MB) Tempfile Name ---- -------- -------------------- ----------- -------------------- 1 20 TEMP 32767 /oratest/oradata/prod/temp01.dbf 아래와같이수정도가능하다 run { allocate channel c1 type disk; backup format '/rman_backup/df_%d_%u' (datafile 3); datafile 의번호입력 release channel c1; allocated channel: c1 channel c1: sid=145 devtype=disk - 15 -
Starting backup at 08-JAN-07 channel c1: starting full datafile backupset channel c1: specifying datafile(s) in backupset input datafile fno=00003 name=/oratest/oradata/prod/sysaux01.dbf channel c1: starting piece 1 at 08-JAN-07 channel c1: finished piece 1 at 08-JAN-07 piece handle=/rman_backup/prod/df_prod_06i71a4d tag=tag20070108t230236 comment=none channel c1: backup set complete, elapsed time: 00:00:15 Finished backup at 08-JAN-07 released channel: c1 RMAN> list backupset of datafile 3; BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------- 110 Full 84.29M DISK 00:00:12 08-JAN-07 BP Key: 112 Status: AVAILABLE Compressed: NO Tag: TAG20070108T230236 Piece Name: /rman_backup/prod/df_prod_06i71a4d List of Datafiles in backup set 110 File LV Type Ckp SCN Ckp Time Name ---- -- ---- ---------- --------- ---- 3 Full 473831 08-JAN-07 /oratest/oradata/prod/sysaux01.dbf Controlfile Backup busan1 $ rman target / catalog rman/rman@resp Recovery Manager: Release 10.2.0.1.0 - Production on Wed Jan 3 22:04:36 2007 Copyright (c) 1982, 2005, Oracle. All rights reserved. connected to target database: PROD (DBID=28555911) connected to recovery catalog database 2> allocate channel c1 type disk; 3> backup 4> format '/rman_backup/prod/cf_t%t_s%s_p%p' 5> tag cf_monday_night 6> (current controlfile); 7> release channel c1; 8> allocated channel: c1 channel c1: sid=145 devtype=disk Starting backup at 08-JAN-07 channel c1: starting full datafile backupset channel c1: specifying datafile(s) in backupset including current control file in backupset channel c1: starting piece 1 at 08-JAN-07 channel c1: finished piece 1 at 08-JAN-07 piece handle=/rman_backup/prod/cf_t611363177_s7_p1 tag=cf_monday_night comment=none channel c1: backup set complete, elapsed time: 00:00:03 Finished backup at 08-JAN-07 released channel: c1-16 -
모든 archive log backup busan1 $ rman target / catalog rman/rman@resp Recovery Manager: Release 10.2.0.1.0 - Production on Wed Jan 3 22:04:36 2007 Copyright (c) 1982, 2005, Oracle. All rights reserved. connected to target database: PROD (DBID=28555911) connected to recovery catalog database 2> allocate channel c1 type disk; 3> backup 4> format '/rman_backup/prod/log_t%t_s%s_p%p' 5> (archivelog all); 6> release channel c1; 7> allocated channel: c1 channel c1: sid=145 devtype=disk Starting backup at 08-JAN-07 current log archived channel c1: starting archive log backupset channel c1: specifying archive log(s) in backup set input archive log thread=1 sequence=1 recid=1 stamp=611284311 input archive log thread=1 sequence=2 recid=2 stamp=611316069 input archive log thread=1 sequence=3 recid=3 stamp=611349324 input archive log thread=1 sequence=4 recid=4 stamp=611359241 input archive log thread=1 sequence=5 recid=5 stamp=611359291 input archive log thread=1 sequence=6 recid=6 stamp=611359383 input archive log thread=1 sequence=7 recid=7 stamp=611363431 channel c1: starting piece 1 at 08-JAN-07 channel c1: finished piece 1 at 08-JAN-07 piece handle=/rman_backup/prod/log_t611363438_s8_p1 tag=tag20070108t231037 comment=none channel c1: backup set complete, elapsed time: 00:00:08 Finished backup at 08-JAN-07 released channel: c1 RMAN> list backupset of archivelog all; List of Backup Sets =================== BS Key Size Device Type Elapsed Time Completion Time ------- ---------- ----------- ------------ --------------- 146 40.86M DISK 00:00:04 08-JAN-07 BP Key: 148 Status: AVAILABLE Compressed: NO Tag: TAG20070108T231037 Piece Name: /rman_backup/prod/log_t611363438_s8_p1 List of Archived Logs in backup set 146 Thrd Seq Low SCN Low Time Next SCN Next Time ---- ------- ---------- --------- ---------- --------- 1 1 434126 08-JAN-07 434513 08-JAN-07 1 2 434513 08-JAN-07 448529 08-JAN-07 1 3 448529 08-JAN-07 462586 08-JAN-07 1 4 462586 08-JAN-07 467767 08-JAN-07 1 5 467767 08-JAN-07 468833 08-JAN-07 1 6 468833 08-JAN-07 470165 08-JAN-07 1 7 470165 08-JAN-07 474371 08-JAN-07-17 -
특정범위의 Sequence Archive log Backup 2> allocate channel c1 type disk; 3> backup 4> format '/rman_backup/prod/log_t%t_s%s_p%p' 5> (archivelog from sequence=1 until sequence=4 thread 1); 6> release channel c1; 7> allocated channel: c1 channel c1: sid=145 devtype=disk Starting backup at 08-JAN-07 channel c1: starting archive log backupset channel c1: specifying archive log(s) in backup set input archive log thread=1 sequence=1 recid=1 stamp=611284311 input archive log thread=1 sequence=2 recid=2 stamp=611316069 input archive log thread=1 sequence=3 recid=3 stamp=611349324 input archive log thread=1 sequence=4 recid=4 stamp=611359241 channel c1: starting piece 1 at 08-JAN-07 channel c1: finished piece 1 at 08-JAN-07 piece handle=/rman_backup/prod/log_t611363727_s9_p1 tag=tag20070108t231527 comment=none channel c1: backup set complete, elapsed time: 00:00:08 Finished backup at 08-JAN-07 released channel: c1 특정시간이경과한 Archive log Backup 2> allocate channel c1 type disk; 3> backup 4> format '/rman_backup/prod/log_t%t_s%s_p%p' 5> (archivelog from time sysdate-1 all delete input); 6> release channel c1; 7> sysdate-1 : 현재날짜와시간보다 1일전 sysdate-7 : 현재날짜와시간보다 7일전 sysdate- 1/24 : 현재날짜와시간보다 1시간전 sysdate- 9/24 : 현재날짜와시간보다 9시간전 sysdate- 5/3600 : 현재날짜와시간보다 5분전 ##Backup 이완료되면삭제가된다. 만일 Backup 이실패를한다면 Archivelog 들은지워지지않음. Online Redolog 의 Backup RMAN>run { allocate channel c1 type disk; sql "alter system archive log current"; backup format '/rman_backup/log_t%t_s%s_p%p' - 18 -
(archivelog from time 'sysdate-1' all delete input); release channel c1; Online Redolog 는백업에앞서 Archived 되어져야하므로위와같이 sql command 를이용하여백업을진행한다. 4.3. Incremental Backup Level N incremental Backup 은가장최근의 N 또는 N 보다작은 Backup 이후의변경된부분만을 Backup 하는것이 다.List Backup Set 을조회해보면 Type Column 에는 Incr, LV Column 에는 0 이라고나타난다. Level 0 DataBase Full Backup allocate channel c1 type disk; backup incremental level 0 DB 전체에대해 Level 0 로백업을받는다. filesperset 4 format '/rman_backup/prod/sunday_level0_%t' (database); release channel c1; BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------- 233 Incr 0 145.04M DISK 00:00:22 08-JAN-07 BP Key: 244 Status: AVAILABLE Compressed: NO Tag: TAG20070108T233158 Piece Name: /rman_backup/prod/sunday_level0_611364719 List of Datafiles in backup set 233 File LV Type Ckp SCN Ckp Time Name ---- -- ---- ---------- --------- ---- 1 0 Incr 474991 08-JAN-07 /oratest/oradata/prod/system01.dbf 4 0 Incr 474991 08-JAN-07 /oratest/oradata/prod/users01.dbf 5 0 Incr 474991 08-JAN-07 /oratest/oradata/prod/catalog01.dbf BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------- 234 Incr 0 139.35M DISK 00:00:19 08-JAN-07 BP Key: 245 Status: AVAILABLE Compressed: NO Tag: TAG20070108T233158 Piece Name: /rman_backup/prod/sunday_level0_611364745 List of Datafiles in backup set 234 File LV Type Ckp SCN Ckp Time Name ---- -- ---- ---------- --------- ---- 2 0 Incr 475001 08-JAN-07 /oratest/oradata/prod/undotbs01.dbf 3 0 Incr 475001 08-JAN-07 /oratest/oradata/prod/sysaux01.dbf BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------- 235 Incr 0 7.08M DISK 00:00:02 08-JAN-07 BP Key: 246 Status: AVAILABLE Compressed: NO Tag: TAG20070108T233158 Piece Name: /rman_backup/prod/sunday_level0_611364771 Control File Included: Ckp SCN: 475010 Ckp time: 08-JAN-07. - 19 -
만약다음과같이 backup 정책을수립했다고하면.. 일요일에 level 0로 full backup, 월요일에는 level 2로일요일 backup시점이후변동사항만을 incremental backup, 화요일에는 level 2로월요일 backup 시점이후변동사항만을 incremental backup, 수요일에는 level 1으로일요일 backup 시점이후변동사항만을 incremental backup, 목요일에는 level 2로수요일 backup 시점이후변동사항만을 incremental backup. 금요일에는 level 2로목요일 backup 시점이후변동사항만을 incremental backup 만약토요일에장애가발생하면, 일요일, 수요일, 목요일, 금요일의 backup을이용하여 recovery하면된다. 만약 level 1 incremental backup을이틀에한번정도할수있다면 recovery 시간은더단축할수있다. 5. RMAN with Recovery(Datafile,Tablespace) Datafile Crash or Deleted(DataBase OPEN) DataBase 가운영중에 Disk 장애및여러가지이유에서 datafile 이 deleted 되었다면두가지방법으로 recovery 가가능하다. 1. Datafile restore 후 Datafile Recovery 2. Datafile restore 후 Tablespace Recovery 단 system tablespace 에해당하는 datafile 에이상이생겼다면 Database close 후복구가진행되어야한다. DATAFILE RECOVERY busan1 $ sqlplus '/as sysdba' SQL*Plus: Release 10.2.0.1.0 - Production on Mon Jan 8 23:51:13 2007 Copyright (c) 1982, 2005, Oracle. All rights reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0-64bit Production With the Partitioning and Data Mining options SQL>!rm /oratest/oradata/prod/users01.dbf 실제 Disk 장애를유발시킨다. SQL> shutdown immediate ORA-01116: error in opening database file 4 ORA-01110: data file 4: '/oratest/oradata/prod/users01.dbf' ORA-27041: unable to open file SVR4 Error: 2: No such file or directory Additional information: 3 ##RMAN 을통해서 Recovery 를진행한다.## 2> allocate channel c1 type disk; 3> sql "alter tablespace users offline immediate"; 4> restore datafile 4; 5> recover datafile 4; 6> sql "alter tablespace users online"; 7> release channel c1; 8> released channel: ORA_DISK_1-20 -
allocated channel: c1 channel c1: sid=145 devtype=disk sql statement: alter tablespace users offline immediate Starting restore at 08-JAN-07 channel c1: starting datafile backupset restore channel c1: specifying datafile(s) to restore from backup set restoring datafile 00004 to /oratest/oradata/prod/users01.dbf channel c1: reading from backup piece /rman_backup/prod/sunday_level0_611364719 channel c1: restored backup piece 1 piece handle=/rman_backup/prod/sunday_level0_611364719 tag=tag20070108t233158 channel c1: restore complete, elapsed time: 00:00:02 Finished restore at 08-JAN-07 Starting recover at 08-JAN-07 starting media recovery media recovery complete, elapsed time: 00:00:04 Finished recover at 08-JAN-07 sql statement: alter tablespace users online Datafile 이 recovery 되었음을확인할수있다. released channel: c1 TABLESPACE RECOVERY 2> allocate channel c1 type disk; 3> sql "alter tablespace users offline immediate"; 4> restore tablespace users; 5> recover tablespace users; 6> sql "alter tablespace users online"; 7> release channel c1; 8> Online Redo log file lost(database Closed) allocate channel c1 type disk; set until logseq=105 thread=1; restore controlfile to '/oratest/oradata/prod/control01.ctl'; replicate controlfile from '/oratest/oradata/prod/control01.ctl'; restore database; sql "alter database mount"; recover database; sql "alter database open resetlogs"; release channel c1; RMAN> reset database; 1. set until command 는어떠한 log sequence 까지 recovery 하고 stop 할것인지를가리킨다. 이문장은 datafile 들이 restore 되기전에나와야한다. 그렇지않으면 RMAN 은지정한 log sequence 보다앞선가장 - 21 -
최근의 datafile 들을 restore 하려고한다. 2. 기본적으로 restore controlfile command 에의하여 init.ora 에지정되어있는 control_files 의위치로자동적으로 controlfile 들이 restore 된다. 이렇게하지않고특정한위치를지정하기위해서는 restore controlfile to filename 이라고지정하면된다. 3. replicate controlfile 은 init.ora 에지정되어있는위치에 controlfile 을 restore 하지않고특정한위치에 controlfile 을 restore 했을때이것들을다시 init.ora 에지정되어있는 control_files 의위치에 controlfile 을 copy 하기위하여사용한다. 4. Database 가 resetlogs 로 open 이되어졌기때문에 database 의새로운 incarnation 을 register 해야할필요가있다. 이것은 reset database command 를통하여할수있다. 5.1. Rman 을통한 Recovery 시나리오 ( 예제 ) #### SCENARIO 1 : COMPLETE RECOVERY - DATAFILE RECOVERY #### 1. db open 중에, 해당 tablespace 를 offline 2. datafile 을 restore 3. recover 수행 4. 해당 tablespace online RMAN> report schema; Report of database schema List of Permanent Datafiles =========================== File Size(MB) Tablespace RB segs Datafile Name ---- -------- -------------------- ------- ------------------------ 1 300 SYSTEM YES /oracle/oradata/wippy/system01.dbf 2 200 UNDOTBS1 YES /oracle/oradata/wippy/undotbs01.dbf 3 210 SYSAUX NO /oracle/oradata/wippy/sysaux01.dbf 4 5 USERS NO /oracle/oradata/wippy/users01.dbf 5 20 ORACLE NO /oracle/oradata/wippy/oracle01.dbf 8 50 RMAN_TS NO /oracle/oradata/wippy/rman01.dbf 2> sql "alter tablespace oracle offline immediate"; 3> restore datafile 5; 4> recover datafile 5; 5> sql "alter tablespace oracle online"; #### SCENARIO 2 : COMPLETE RECOVERY - TABLESPACE RECOVERY #### 1. SCENARIO 1 과과정은동일하나, command 가조금달라짐. 2> sql "alter tablespace oracle offline immediate"; 3> restore tablespace oracle; 4> recover tablespace oracle; 5> sql "alter tablespace oracle online";!! 참고!! 여러개의 backupset 중에선택해서 restore 할려면?? restore tablespace oracle from tag=' 태그이름 ' 을주면된다. #### SCENARIO 3 : COMPLETE RECOVERY - TABLESPACE RECOVERY( 새로운곳에 RESTORE 후 RECOVERY) #### 2> sql "alter tablespace oracle offline immediate"; 3> set newname for datafile '/oracle/oradata/wippy/control/oracle01.dbf' to '/oracle/oradata/wippy/oracle01.dbf'; - 22 -
4> restore tablespace oracle; 5> switch datafile all; 6> recover tablespace oracle; 7> sql "alter tablespace oracle online"; # set newname for ' 원본경로 ' to ' 이동경로 ' # switch datafile all : Update the control file and recovery catalog #### SCENARIO 4 : INCOMPLETE RECOVERY - CURRENT ONLINE REDO LOG GROUP 의유실 #### 1. alertlog 를보고 log sequence 를알아둔다. 2> set until sequence=3 thread=1; 3> restore database; 4> recover database; 5> alter database open resetlogs; executing command: SET until clause Starting restore at 09-JUL-07 allocated channel: ORA_DISK_1 channel ORA_DISK_1: sid=156 devtype=disk channel ORA_DISK_1: starting datafile backupset restore channel ORA_DISK_1: specifying datafile(s) to restore from backup set restoring datafile 00001 to /oracle/oradata/wippy/system01.dbf restoring datafile 00002 to /oracle/oradata/wippy/undotbs01.dbf restoring datafile 00003 to /oracle/oradata/wippy/sysaux01.dbf restoring datafile 00004 to /oracle/oradata/wippy/users01.dbf restoring datafile 00005 to /oracle/oradata/wippy/oracle01.dbf restoring datafile 00008 to /oracle/oradata/wippy/rman01.dbf channel ORA_DISK_1: reading from backup piece /oracle/oradata/backup/full-20070709-34imdbmn_1_1-100-1 channel ORA_DISK_1: restored backup piece 1 piece handle=/oracle/oradata/backup/full-20070709-34imdbmn_1_1-100-1 tag=full_backup channel ORA_DISK_1: restore complete, elapsed time: 00:01:16 Finished restore at 09-JUL-07 Starting recover at 09-JUL-07 using channel ORA_DISK_1 starting media recovery archive log thread 1 sequence 1 is already on disk as file /oracle/arch/10.2.0/1_1_627485531.dbf archive log thread 1 sequence 2 is already on disk as file /oracle/arch/10.2.0/1_2_627485531.dbf archive log filename=/oracle/arch/10.2.0/1_1_627485531.dbf thread=1 sequence=1 archive log filename=/oracle/arch/10.2.0/1_2_627485531.dbf thread=1 sequence=2 media recovery complete, elapsed time: 00:00:06 Finished recover at 09-JUL-07 database opened new incarnation of database registered in recovery catalog starting full resync of recovery catalog full resync complete #### SCENARIO 5 : INCOMPLETE RECOVERY - CONTROLFILE 과 DATAFILE 동시에유실 #### 1. 복구수행.(nomount 상태 ) 2> set until sequence=9 thread=1; 3> restore controlfile; 4> alter database mount; 5> restore database; 6> recover database; 7> alter database open resetlogs; 8> - 23 -
executing command: SET until clause Starting restore at 09-JUL-07 allocated channel: ORA_DISK_1 channel ORA_DISK_1: sid=155 devtype=disk channel ORA_DISK_1: starting datafile backupset restore channel ORA_DISK_1: restoring control file channel ORA_DISK_1: reading from backup piece /oracle/oradata/backup/full-20070709-2pimd5dh_1_1-89-1 channel ORA_DISK_1: restored backup piece 1 piece handle=/oracle/oradata/backup/full-20070709-2pimd5dh_1_1-89-1 tag=full_backup channel ORA_DISK_1: restore complete, elapsed time: 00:00:05 output filename=/oracle/oradata/wippy/control/control01.ctl output filename=/oracle/oradata/wippy/control/control02.ctl output filename=/oracle/oradata/wippy/control/control03.ctl Finished restore at 09-JUL-07 database mounted released channel: ORA_DISK_1 Starting restore at 09-JUL-07 allocated channel: ORA_DISK_1 channel ORA_DISK_1: sid=155 devtype=disk channel ORA_DISK_1: starting datafile backupset restore channel ORA_DISK_1: specifying datafile(s) to restore from backup set restoring datafile 00001 to /oracle/oradata/wippy/system01.dbf restoring datafile 00002 to /oracle/oradata/wippy/undotbs01.dbf restoring datafile 00003 to /oracle/oradata/wippy/sysaux01.dbf restoring datafile 00004 to /oracle/oradata/wippy/users01.dbf restoring datafile 00005 to /oracle/oradata/wippy/oracle01.dbf restoring datafile 00008 to /oracle/oradata/wippy/rman01.dbf channel ORA_DISK_1: reading from backup piece /oracle/oradata/backup/full-20070709-2oimd5bg_1_1-88-1 channel ORA_DISK_1: restored backup piece 1 piece handle=/oracle/oradata/backup/full-20070709-2oimd5bg_1_1-88-1 tag=full_backup channel ORA_DISK_1: restore complete, elapsed time: 00:01:15 Finished restore at 09-JUL-07 Starting recover at 09-JUL-07 using channel ORA_DISK_1 starting media recovery archive log thread 1 sequence 8 is already on disk as file /oracle/arch/10.2.0/1_8_627475891.dbf archive log filename=/oracle/arch/10.2.0/1_8_627475891.dbf thread=1 sequence=8 media recovery complete, elapsed time: 00:00:02 Finished recover at 09-JUL-07 database opened new incarnation of database registered in recovery catalog starting full resync of recovery catalog full resync complete 2. backupset file delete RMAN> delete backupset; RMAN> delete archivelog all; #### SCENARIO 6 : INCOMPLETE RECOVERY - 시간기반으로 RECOVERY ####.bash_profile 에 export NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS' 입력후쉘다시적용. date command 로현재시간확인. 2007-07-09 18:23:00 이라고가정. - 24 -
oracle 에서 table 삭제. nomount 상태에서복구시작. 2> set until time='2007-07-09 18:23:00'; 3> restore controlfile; 4> alter database mount; 5> restore database; 6> recover database; 7> alter database open resetlogs; 8> 6. Rman Recovery 를이용한 NEW 서버에 Restore rman 으로다른서버에전체 restore 방법 busan1 $ rman target / catalog rman/rman@resp Recovery Manager: Release 10.2.0.1.0 - Production on Thu Jan 11 14:13:13 2007 Copyright (c) 1982, 2005, Oracle. All rights reserved. connected to target database: PROD (DBID=2552232597) connected to recovery catalog database RMAN> configure channel device type disk format '/rman_backup/prod/%d_%s_%p.bak' RMAN> CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/rman_backup/prod/snap_control_prod.f'; RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON; RMAN> CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/rman_backup/prod/%f.ctl' RMAN> show all; 전체 configure 를확인한다. RMAN configuration parameters are: CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 1 DAYS; CONFIGURE BACKUP OPTIMIZATION OFF; # default CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default CONFIGURE CONTROLFILE AUTOBACKUP ON; CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/rman_backup/prod/%f.ctl'; CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '/rman_backup/prod/%d_%s_%p.bak'; CONFIGURE MAXSETSIZE TO UNLIMITED; # default CONFIGURE ENCRYPTION FOR DATABASE OFF; # default CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/rman_backup/prod/snap_control_prod.f'; RMAN> backup database; Starting backup at 11-JAN-07 allocated channel: ORA_DISK_1 channel ORA_DISK_1: sid=154 devtype=disk channel ORA_DISK_1: starting full datafile backupset channel ORA_DISK_1: specifying datafile(s) in backupset - 25 -
input datafile fno=00001 name=/oratest/oradata/prod/system01.dbf input datafile fno=00002 name=/oratest/oradata/prod/undotbs01.dbf input datafile fno=00003 name=/oratest/oradata/prod/sysaux01.dbf input datafile fno=00005 name=/oratest/oradata/prod/catalog01.dbf input datafile fno=00004 name=/oratest/oradata/prod/users01.dbf channel ORA_DISK_1: starting piece 1 at 11-JAN-07 channel ORA_DISK_1: finished piece 1 at 11-JAN-07 piece handle=/rman_backup/prod/prod_20_1.bak tag=tag20070111t141941 comment=none channel ORA_DISK_1: backup set complete, elapsed time: 00:00:45 Finished backup at 11-JAN-07 Starting Control File Autobackup at 11-JAN-07 piece handle=/rman_backup/prod/c-2552232597-20070111-00.ctl comment=none Finished Control File Autobackup at 11-JAN-07 ############ 사전작업 ###################################################### Step 1) 새로운서버로 rman backup, archive 이동 Step 2) $ORACLE_HOME/dbs 에 initprod.ora 파라미터위치 Step 3) $ORACLE_HOME/network/admin에 tnsnames.ora 파일에 REPO DB에접속가능하도록설정 Step 4) rman backup의위치는원래백업했던경로와같아야함 archive는경로를마음대로해도되나 init파라미터에꼭설정해야함 Step 5) datafile을 restore할곳은 /oratest/oradata/prod 에서 /oratest/oradata/new_prod로변경함즉, init파라미터에서 control_files 파라미터를 /oratest/oradata/new_prod/control01.ctl ~~ 으로변경해야함 새로운서버에서 PROD DB 를 nomount 상태로올림 SQL> startup nomount ORACLE instance started. Total System Global Area 353440972 bytes Fixed Size 451788 bytes Variable Size 218103808 bytes Database Buffers 134217728 bytes Redo Buffers 667648 bytes Busan2 $ rman target / catalog rman/rman@resp busan2 에서작업을한다 ( 신규서버 ) Recovery Manager: Release 10.2.0.1.0 - Production on Thu Jan 11 14:13:13 2007 Copyright (c) 1982, 2005, Oracle. All rights reserved. connected to target database: PROD (not mounted) connected to recovery catalog database RMAN> set dbid=4294045256 dbid 는실 DB 의 v$database 를보면됨 restore하고자하는시간을확인 alert log를보니.. 296 archive가있다. 1시 12분 04초에생겼으니 time based로는이전까지로복구해야한다. 1시 12분 03초로한다. Fri Sep 22 01:12:04 2007 ARC1: Evaluating archive log 2 thread 1 sequence 296-26 -
ARC1: Beginning to archive log 2 thread 1 sequence 296 Creating archive destination LOG_ARCHIVE_DEST_1: '/data/archive/1_296.arc' Fri Sep 22 01:12:04 2007 Thread 1 advanced to log sequence 297 Current log# 3 seq# 297 mem# 0: /data/oradata/prod/redo03a.log controlfile을 restore수행후 DB를 mount상태로만듦 - 주의! init파라미터에설정된경로에 restore가되니 control_files파라미터를확인만약 LOG SEQUENCE NUMBER를통해서복구하고자한다면아래와같이 SET UNTIL을지정한다. SET UNTIL SEQUENCE 276 THREAD 1; 참고 ) LOG SEQUENCE NUMBER 를통해서복구하는방법 SET UNTIL SEQUENCE 276 THREAD 1; restore controlfile; SET UNTIL TIME "TO_DATE('2007/09/22 01:12:03','YYYY/MM/DD HH24:MI:SS')"; restore controlfile; executing command: SET until clause Starting restore at 22-SEP-06 allocated channel: ORA_DISK_1 channel ORA_DISK_1: sid=12 devtype=disk channel ORA_DISK_1: starting datafile backupset restore channel ORA_DISK_1: restoring controlfile output filename=/oratest/oradata /NEW_PROD/control01.ctl channel ORA_DISK_1: restored backup piece 1 piece handle=/backup/rman/c-4294045256-20060922-00.ctl tag=null params=null channel ORA_DISK_1: restore complete replicating controlfile input filename=/oratest/oradata /NEW_PROD/control01.ctl output filename=/oratest/oradata /NEW_PROD/control02.ctl Finished restore at 22-SEP-06 RMAN> alter database mount; SET UNTIL TIME "TO_DATE('2007/09/22 01:12:03','YYYY/MM/DD HH24:MI:SS')"; set newname for datafile '/oratest/oradata /PROD/system01.dbf' to '/oratest/oradata /NEW_PROD/system01.dbf'; set newname for datafile '/oratest/oradata /PROD/undotbs01.dbf' to '/oratest/oradata /NEW_PROD/undotbs01.dbf'; set newname for datafile '/oratest/oradata /PROD/user01.dbf' to '/oratest/oradata /NEW_PROD/users01.dbf'; restore database; switch datafile all ; recover database; sql 'alter database open resetlogs'; executing command: SET until clause executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME - 27 -
Starting restore at 22-SEP-06 using channel ORA_DISK_1 channel ORA_DISK_1: starting datafile backupset restore channel ORA_DISK_1: specifying datafile(s) to restore from backup set restoring datafile 00001 to /oratest/oradata /NEW_PROD/system01.dbf restoring datafile 00002 to /oratest/oradata /NEW_PROD/undotbs01.dbf restoring datafile 00003 to /oratest/oradata /NEW_PROD/users01.dbf channel ORA_DISK_1: restored backup piece 1 piece handle=/rmna_backup/prod/prod_13_1.bak tag=tag20060922t010901 params=null channel ORA_DISK_1: restore complete Finished restore at 22-SEP-07 Starting recover at 22-SEP-07 using channel ORA_DISK_1 starting media recovery archive log thread 1 sequence 296 is already on disk as file /data/archive/1_296.arc archive log filename=/data/archive/1_296.arc thread=1 sequence=296 media recovery complete Finished recover at 22-SEP-07 sql statement: alter database open resetlogs 7. Rman Backup 을위한 Script create Create or Replace Script Recovery Catalog 내에 script 형태로저장이되어지는것으로 stored procedure 형태로불러서사용할수있다. 조회는 RC_STORED_SCRIPT 와 RC_STORED_SCRIPT_LINE 에서할수있다. RMAN> create script backup_db_full { 2> allocate channel c1 type disk; 3> backup 4> full 5> tag full_db_sunday_night 6> format '/rman_backup/prod/db_t%t_s%s_p%p' 7> (database); 8> release channel c1; 9> created script backup_db_full Execute Script run 이란명령어를통해서저장된 Script 를실행한다. - 28 -
RMAN> run{ execute script backup_db_full; executing script: backup_db_full Delete Script 저장된 Script 를 Delete 시킨다. RMAN> run{ delete script backup_db_full; deleted script: backup_db_full OS 상에서실행이하는방법 OS 의 file 형태로존재하는 Script 를실행하는방법 busan1 $ cat db_full.rcv run { execute script backup_db_full; busan1 $ rman target / catalog rman/rman@resp @db_full.rcv log full_backup.log 위 script 를쉘파일로작성한후 OS 의 Cron 명령어로도실행가능함. busan1 $ cat full_backup.log 백업로그로서완료여부를확인가능하다. Recovery Manager: Release 10.2.0.1.0 - Production on Tue Jan 9 00:49:01 2007 Copyright (c) 1982, 2005, Oracle. All rights reserved. connected to target database: PROD (DBID=2552232597) connected to recovery catalog database 2> execute script backup_db_full; 3> 4> 5> executing script: backup_db_full allocated channel: c1 channel c1: sid=143 devtype=disk Starting backup at 09-JAN-07 channel c1: starting full datafile backupset channel c1: specifying datafile(s) in backupset input datafile fno=00001 name=/oratest/oradata/prod/system01.dbf 중략 channel c1: finished piece 1 at 09-JAN-07 piece handle=/rman_backup/prod/db_t611369398_s19_p1 tag=full_db_sunday_night comment=none channel c1: backup set complete, elapsed time: 00:00:03-29 -
Finished backup at 09-JAN-07 released channel: c1 Recovery Manager complete. 8. Rman Repository 의유지관리 8.1. Removing Obsolete Backupsets 파일이존재하지않으면 EXPIRED 로갱신 RMAN> allocate channel for maintenance type disk; channel 을할당한다. allocated channel: ORA_MAINT_DISK_2 channel ORA_MAINT_DISK_2: sid=139 devtype=disk RMAN> crosscheck backup; 불필요한 backup 들을 Crosscheck 한다. crosschecked backup piece: found to be 'AVAILABLE' backup piece handle=/rman_backup/prod/df_prod_06i71a4d recid=6 stamp=611362960 crosschecked backup piece: found to be 'AVAILABLE' backup piece handle=/rman_backup/prod/cf_t611363177_s7_p1 recid=7 stamp=611363179 crosschecked backup piece: found to be 'AVAILABLE' 중략 backup piece handle=/rman_backup/prod/log_t611363727_s9_p1 recid=9 stamp=611363728 crosschecked backup piece: found to be 'AVAILABLE' crosschecked backup piece: found to be 'AVAILABLE' backup piece handle=/rman_backup/prod/db_t611369398_s19_p1 recid=19 stamp=611369400 Crosschecked 14 objects crosschecked backup piece: found to be 'EXPIRED' backup piece handle=/rman_backup/prod/db_t611286673_s3_p1 recid=3 stamp=611286673 crosschecked backup piece: found to be 'EXPIRED' backup piece handle=/rman_backup/prod/db_t611286729_s4_p1 recid=4 stamp=611286730 crosschecked backup piece: found to be 'EXPIRED' backup piece handle=/rman_backup/prod/tbs_users_t611362619_s5 recid=5 stamp=611362620 Crosschecked 3 objects RMAN> delete expired backup; Expired 된것에대해서삭제를한다. List of Backup Pieces BP Key BS Key Pc# Cp# Status Device Type Piece Name ------- ------- --- --- ----------- ----------- ---------- 67 65 1 1 EXPIRED DISK /rman_backup/prod/db_t611286673_s3_p1 68 66 1 1 EXPIRED DISK /rman_backup/prod/db_t611286729_s4_p1 102 100 1 1 EXPIRED DISK /rman_backup/prod/tbs_users_t611362619_s5 Do you really want to delete the above objects (enter YES or NO)? yes deleted backup piece backup piece handle=/rman_backup/prod/db_t611286673_s3_p1 recid=3 stamp=611286673 deleted backup piece backup piece handle=/rman_backup/prod/db_t611286729_s4_p1 recid=4 stamp=611286730 deleted backup piece backup piece handle=/rman_backup/prod/tbs_users_t611362619_s5 recid=5 stamp=611362620 Deleted 3 EXPIRED objects - 30 -
9. RAC 환경에서의 RMAN Backup Setup 양쪽노드에 remote_login_passwordfile='exclusive' 를설정한다.(target Database 로접속할때 internal user 로 connect 해야하기때문에반드시 setting 이되어있어야한다. busan1 $ crs_stat -t Name Type Target State Host ------------------------------------------------------------ ora...g1.inst application ONLINE ONLINE busan1 ora...g2.inst application ONLINE ONLINE busan2 ora.ora10g.db application ONLINE ONLINE busan1 ora...n1.lsnr application ONLINE ONLINE busan1 ora.busan1.gsd application ONLINE ONLINE busan1 ora.busan1.ons application ONLINE ONLINE busan1 ora.busan1.vip application ONLINE ONLINE busan1 ora...n2.lsnr application ONLINE ONLINE busan2 ora.busan2.gsd application ONLINE ONLINE busan2 ora.busan2.ons application ONLINE ONLINE busan2 ora.busan2.vip application ONLINE ONLINE busan2 busan1 $ rman target / catalog rman/rman@resp Recovery Manager: Release 10.2.0.1.0 - Production on Tue Jan 9 19:19:28 2007 Copyright (c) 1982, 2005, Oracle. All rights reserved. connected to target database: ORA10G (DBID=3933991550) connected to recovery catalog database RMAN> create catalog; 이미다른 DB를위해 rman이사용중이라면생략 recovery catalog already exists RMAN> register database; RAC DB를위해 register한다. database registered in recovery catalog starting full resync of recovery catalog full resync complete RMAN> CONFIGURE DEVICE TYPE DISK PARALLELISM 2; 몇개의 Channel을설정할것인가에따라 PARALLELISM의값을반드시맞춰주어야합니다. 이것을맞춰주지않으면 Error가발생하면서다른 Node의 archive file들을인식하지못하게될수도있음.( 실제로 Archived file들은정상적으로존재합니다 ) new RMAN configuration parameters: CONFIGURE DEVICE TYPE DISK PARALLELISM 2 BACKUP TYPE TO BACKUPSET; new RMAN configuration parameters are successfully stored starting full resync of recovery catalog full resync complete RMAN> configure default device type to disk; new RMAN configuration parameters: CONFIGURE DEFAULT DEVICE TYPE TO DISK; new RMAN configuration parameters are successfully stored starting full resync of recovery catalog full resync complete RMAN> configure channel 1 device type disk connect 'SYS/oracle@ORA10g1'; 각노드별로 configure 를 - 31 -