Oracle Direct Seminar <Insert Picture Here>!! vs RMAN
Oracle Database RMAN RMAN RMAN RMAN 11g Appendix RMAN <Insert Picture Here> Oracle Direct SQL Server MySQL PostgreSQL Access Oracle Database Oracle Developer/2000 Web Oracle Database Oracle Database http://www.oracle.com/lang/jp/direct/services.ht ml 2
Oracle Database RMAN RMAN RMAN RMAN 11g <Insert Picture Here> Appendix RMAN 3
Oracle Export/Import OSSQL OracleRMAN 4
Oracle Export/Import Export ExportImport Export Export/Import Export/Import Import 5
Oracle OSSQL OracleRMAN Backup OSSQL RMAN Recovery Manager RMAN Restore 6
ARCHIVE LOG NOARCHIVE LOG REDO ARCHIVE LOG REDO / /RMAN NOARCHIVE LOG ARCHIVE LOG 7
NOARCHIVE LOG NOARCHIVE LOG 1 2OS 3 1 2OS 3 A () REDO A REDO 8
Oracle Database RMAN RMAN RMAN RMAN 11g <Insert Picture Here> Appendix RMAN 9
SQLOS 10
SQL> ALTER DATABASE BACKUP CONTROLFILE TO ; SQL> ALTER DATABASE BACKUP CONTROLFILE TO trace; CREATE CONTROLFILE REUSE DATABASE "ORCL" NORESETLOGS ARCHIVELOG MAXLOGFILES 16MAXLOGMEMBERS 3MAXDATAFILES 100 MAXINSTANCES 8MAXLOGHISTORY 292 LOGFILE GROUP 1 'D: ORACLE ORADATA ORCL REDO01.LOG' SIZE 50M, GROUP 2 'D: ORACLE ORADATA ORCL REDO02.LOG' SIZE 50M, GROUP 3 'D: ORACLE ORADATA ORCL REDO03.LOG' SIZE 50M DATAFILE 'D: ORACLE ORADATA ORCL SYSTEM01.DBF', 'D: ORACLE ORADATA ORCL SYSAUX01.DBF', 'D: ORACLE ORADATA ORCL UNDOTBS01.DBF', 'D: ORACLE ORADATA ORCL USERS01.DBF', 'D: ORACLE ORADATA ORCL EXAMPLE01.DBF', 'D: ORACLE ORADATA ORCL SALES02.DBF' CHARACTER SET JA16SJISTILDE; 11
SQL> ALTER TABLESPACE BEGIN BACKUP; OS SQL> ALTER TABLESPACE END BACKUP; A UPDATEAB UPDATEBC REDO A 12
13
OS REDO D A REDO AB BC CD SQL> RECOVER TABLESPACE ; ORA-00279: change 846388 generated at 05/11/2007 16:58:48 needed for thread 1 ORA-00289: suggestion :/u01/app/oracle/flash_recovery_area/orcl /archivelog/2007_05_11/o1_mf_1_15_%u_.arc ORA-00280: change 846388 for thread 1 is in sequence #15 Specify log: {<RET>=suggested filename AUTO CANCEL} Enter REDO Log applied. Media recovery complete. Enter 14
Tips REDO REDOREDO NOARCHIVE LOG 15
Tips REDO REDO REDO 16
Oracle Database RMAN RMAN RMAN RMAN 11g <Insert Picture Here> Appendix RMAN 17
Recovery ManagerRMAN Oracle 2 OSRMAN Oracle Enterprise ManagerEMGUI % rman target / RMAN> bacbakup database; RMAN> restore database; RMAN> recover database; 18
RMAN RMAN RMAN SPFILE or Oracle Secure Backup 19
RMAN Oracle Secure Backup RMAN 20
RMAN / RMAN OSUNIXcp BACKUP AS COPY OS RMAN BACKUP RMAN 21
RMAN / RMAN 9i R2EE 10gSE 2 22
RMAN Level 0 Level 1 BACKUP INCREMENTAL LEVEL 1 DATABASE; Level Level Level Level Level Level 0 BACKUP INCREMENTAL LEVEL 0 DATABASE; Level 0 BACKUP INCREMENTAL LEVEL 1 CUMULATIVE DATABASE; 23
RMAN RMAN CTWR RMAN 24
RMAN RMAN SYSDBA % rman target sys/sys TARGET CATALOG (RCVCAT) AUXILIARY (RCVCAT8.0) Data Guard Point-in- Time 25
RMAN RMAN> show all; CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default CONFIGURE BACKUP OPTIMIZATION OFF; # default CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default CONFIGURE CONTROLFILE AUTOBACKUP ON; CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPEDISK TO '%F'; # default CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISKTO 1; # default CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISKTO 1; # default CONFIGURE MAXSETSIZE TO UNLIMITED; # default CONFIGURE ENCRYPTION FOR DATABASE OFF; # default CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default CONFIGURE COMPRESSION ALGORITHM 'BZIP2'; # default CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default CONFIGURE SNAPSHOT CONTROLFILE NAME TO 'D: ORACLE PRODUCT 11.1.0 DB_1 DATABASE SNCFORCL.ORA'; # default 26
RMAN RMAN BACKUP RMAN> backup database; RMAN> backup tablespace users; RMAN> backup datafile 1,2,3; RMAN RMAN> restore database; RMAN> recover database; RMAN> restore tablespace users; RMAN> recover tablespace users; REDO 27
RMAN RMAN 4 3 2 34 28
RMAN RMAN> list backup; =================== BS Key Type LV Size Device Type Elapsed Time ------- ---- -- ---------- ----------- ------------ -------- 52 Full 426.46M DISK 00:04:12 08-12-19 BP: 52 : AVAILABLE : YES : TAG20081219T141724 :D: ORACLE FLASH_RECOVERY_AREA ORCL BACKUPSET 2008_12_19 O1_MF_NNNDF_TAG2 0081219T141724_4NPCMD3X_.BKP 52 File LV Type Ckp SCN Ckp Name ---- -- ---- ---------- -------- ---- 1 Full 3935714 08-12-19 D: ORACLE ORADATA ORCL SYSTEM01.DBF 2 Full 3935714 08-12-19 D: ORACLE ORADATA ORCL SYSAUX01.DBF 3 Full 3935714 08-12-19 D: ORACLE ORADATA ORCL UNDOTBS01.DBF 4 Full 3935714 08-12-19 D: ORACLE ORADATA ORCL USERS01.DBF 5 Full 3935714 08-12-19 D: ORACLE ORADATA ORCL EXAMPLE01.DBF 29
RMAN RMAN> show all; CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default CONFIGURE BACKUP OPTIMIZATION OFF; # default CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default RMAN> report obsolete; Recovery Manager Recovery Manager1 Type Key Completion Time Filename/Handle -------------------- ------ ------------------ ----------------- 52 08-12-19 52 08-12-19 D: ORACLE FLASH_RECOVERY_AREA ORCL BACKUPSET 2008_12_19 O1_MF_N NDF_TAG20081219T141724_4NPCMD3X_.BKP 105 08-12-19 D: ORACLE PRODUCT 11.1.0 DB_1 RDBMS ARC00217_0669310759.001 RMAN> delete obsolete; 30
RMAN 2 RMAN> report need backup redundancy 2; 2 File #bkps Name ---- ----- ---------------------------------------------------- 1 1 D: ORACLE ORADATA ORCL SYSTEM01.DBF 2 1 D: ORACLE ORADATA ORCL SYSAUX01.DBF 3 1 D: ORACLE ORADATA ORCL UNDOTBS01.DBF 5 1 D: ORACLE ORADATA ORCL EXAMPLE01.DBF 31
Tips RMAN RMANTips RMAN RMAN OSRMAN RMAN RMAN> crosscheck backup; crosschecked backup piece: found to be 'AVAILABLE backup piece handle=/u01/app/oracle/flash_recovery_area/orcl/backupset/2008_09_17/o1_mf_nnndf crosschecked backup piece: found to be 'EXPIRED backup piece handle=/u01/app/oracle/flash_recovery_area/orcl/backupset/2008_09_17/o1_mf_nnndf Crosschecked 4 objects RMAN> delete expired backup; List of of Backup Pieces BP BP Key BS BS Key Pc# Cp# Status Device Type Piece Name ------- ------- --- --- ----------- ----------- ---------- 4 4 1 1 EXPIRED DISK /u01/app/oracle/flash_recovery_area/orcl/backupset/2008_09_17/o1_mf_nnndf_tag200809 17T141100_4f14dzl0_.bkp Do you really want to delete the above objects (enter YES or NO)? Yes deleted backup piece 32
Tips Tips RMAN RMAN RMAN> backup database include current controlfile; RMAN> configure controlfile autobackup on; RMAN 33
Tips RMAN RMAN 34
RMAN11g : : 35
RMAN> list failure; RMAN> advise failure; RMAN> repair failure; 1. 13595Oracle : 4: 'D: ORACLE ORADATA ORCL USERS01.DBF' 2. D: ORACLE ORADATA ORCL SYSTEM01.DBF : d: oracle diag rdbms orcl orcl hm reco_803716847.hm : restore database; recover database; alter database open; : 00:00:03 recover(: 08-12-19) 36
Oracle Database RMAN RMAN RMAN RMAN 11g Appendix RMAN 37
<Insert Picture Here> Appendix RMAN 11g 38
SQL> ALTER TABLESPACE users BEGIN BACKUP; OSUSERS SQL>!cp /u01/app/oracle/oradata/orcl/users01.dbf /home/backup/users01.dbf SQL> ALTER TABLESPACE users END BACKUP; 39
USERS SQL> startup ORA-01157: 4/ DBWR ORA-01110: 4: 'D: ORACLE ORADATA ORCL USERS01.DBF' SQL> SELECT * FROM v$recover_file; FILE# ONLINE ONLINE_ ERROR CHANGE# TIME ----- ------- ------- -------------- ------------- 4 ONLINE ONLINE FILE NOT FOUND 0 SQL> ALTER DATABASE DATAFILE 4 OFFLINE; SQL> ALTER DATABASE OPEN; 40
OSUSERS SQL>!cp /home/backup/users01.dbf /u01/app/oracle/oradata/orcl/users01.dbf SQL> SELECT * FROM v$recover_file; FILE# ONLINE ONLINE_ ERROR CHANGE# TIME ------ ------- ------- ----------- ---------- ------- 4 OFFLINE OFFLINE 3934796 08-12-19 FILE NOT FOUND 41
SQL> recover datafile 4; ORA-00279: 3934796(12/19/2008 14:04:11)1 ORA-00289: :D: ORACLE FLASH_RECOVERY_AREA ORCL ARCHIVELOG 20 9 O1_MF_1_212_4NPBW5XS_.ARC ORA-00280: 3934796(1)212 : {<RET>=suggested filename AUTO CANCEL} :D: ORACLE FLASH_RECOVERY_AREA ORCL ARCHIVELOG 20 9 O1_MF_1_213_4NPBWCH4_.ARC ORA-00280: 3934836(1)213 : {<RET>=suggested filename AUTO CANCEL} REDO Enter 42
SQL> SELECT * FROM v$recover_file; SQL> SELECT file#,status FROM v$datafile; FILE# STATUS ---------- ------- 1 SYSTEM 2 ONLINE 3 ONLINE 4 OFFLINE 5 ONLINE 6 ONLINE 7 ONLINE 8 ONLINE SQL> ALTER DATABASE DATAFILE 4 ONLINE; 43
RMAN> BACKUP TABLESPACE users; backup(: 08-12-19) : ORA_DISK_1 ORA_DISK_1: SID=131 =DISK ORA_DISK_1: ORA_DISK_1: =00004 =D: ORACLE ORADATA ORCL USERS01.DBF ORA_DISK_1: 1(08-12-19) ORA_DISK_1: 1(08-12-19) =D: ORACLE FLASH_RECOVERY_AREA ORCL BACKUPSET 2008_12_19 O1_MF_ NNNDF_TA 20081219T142638_4NPD4OF5_.BKP =TAG20081219T142638 =NONE ORA_DISK_1: : 00:00:07 backup(: 08-12-19) 44
USERS SQL> startup ORA-01157: 4/ - DBWR ORA-01110: 4: 'D: ORACLE ORADATA ORCL USERS01.DBF' SQL> SELECT * FROM v$recover_file; FILE# ONLINE ONLINE_ ERROR CHANGE# TIME ----- ------- ------- -------------- ------------- 4 ONLINE ONLINE FILE NOT FOUND 0 SQL> ALTER DATABASE DATAFILE 4 OFFLINE; SQL> ALTER DATABASE OPEN; 45
RMAN> restore datafile 4; restore(: 08-12-19) : ORA_DISK_1 ORA_DISK_1: 00004 D: ORACLE ORADATA ORCL USERS01.DBF restore(: 08-12-19) RMAN> recover datafile 4; recover(: 08-12-19) ORA_DISK_1 : 00:00:01 recover(: 08-12-19) 46
RMAN> sql ALTER DATABASE DATAFILE 4 ONLINE"; SQL> SELECT file#,status FROM v$datafile; FILE# STATUS ---------- ------- 1 SYSTEM 2 ONLINE 3 ONLINE 4 ONLINE 5 ONLINE 6 ONLINE 7 ONLINE 8 ONLINE RMANSQL sql sql 47
SQL> startup ORACLE Total System Global Area 535662592 bytes Fixed Size 1334380 bytes Variable Size 197133204 bytes Database Buffers 331350016 bytes Redo Buffers 5844992 bytes ORA-00205:????????????????????????????????????????? REDO 48
RMAN> advise failure all; ======================= 1. D: ORACLE ORADATA ORCL CONTROL01.CTL 2. D: ORACLE ORADATA ORCL CONTROL02.CTL 3. D: ORACLE ORADATA ORCL CONTROL03.CTL ======================== ------ ------------------ 1 : : d: oracle diag rdbms orcl orcl hm reco_4060288210.hm 49
RMAN> repair failure; : : d: oracle diag rdbms orcl orcl hm reco_4060288210.hm : # restore control file restore controlfile from autobackup; sql 'alter database mount'; (YESNO) yes restore(: 08-12-19) SQL: alter database mount : ORA_DISK_1 50
RMAN> advise failure all; ======================= 1. 1. 13595Oracle: 4: 'D: ORACLE ORADATA ORCL USERS01.DBF' 2. 2. D: ORACLE ORADATA ORCL SYSTEM01.DBF 3. 3. 4. 4. D: ORACLE ORADATA ORCL SYSAUX01.DBF 5. 5. D: ORACLE ORADATA ORCL UNDOTBS01.DBF ======================== ------ ------------------ 1 : Point-in-Time : d: oracle diag rdbms orcl orcl hm reco_803716847.hm 51
REDO RMAN> repair failure ; : Point-in-Time : d: oracle diag rdbms orcl orcl hm reco_803716847.hm : # database point-in-time recovery until a missing log restore database until scn 4007519; recover database until scn 4007519; alter database open resetlogs; (YESNO) yes : 00:00:03 recover(: 08-12-19) 52
Oracle Direct Seminar Oracle Direct Seminar Oracle University!? Oracle!? Backup Recovery!? &!? Oracle By Example Backup Recovery - VS RMAN Oracle Database 25 Backup Recovery Oracle By Example OTN(Oracle Technology Network)!? :: http://www.oracle.com/technology/global/jp/columns/index.html Oracle University http://education.oracle.com/ 53
Oracle Direct Seminar 100Wendy2010 11122!! a.b.c.d. a,b,c,d http://www.oracle.com/technology/global/jp/membership/index.html ORD_SEMINAR_JP@ORACLE.COM 20091112 Oracle Direct 54
OTN!! Oracle Technology Network(OTN) http://otn.oracle.co.jp/forum/index.jspa?categoryid=2 OTN OTNOracle100% http://www.oracle.com/technology/global/jp/ondemand/otn-seminar/index.html OTN OTN OTN 55
IT Oracle Direct Concierge BI IT BI Access / SQL Server MySQL / PostgreSQL Oracle Database Oracle Developer Web RFP BI Oracle Database Oracle Database 56
Oracle Direct Oracle Direct IT / Web 0120155096 ~9:00~12:0013:00~18:00 57
OraclePeopleSoftJD EdwardsSiebel 58