untitled

Size: px

Start display at page:

Download "untitled"

よしじろうおおふさ
7 years ago
Views:

1 Oracle Direct Seminar <Insert Picture Here>!! vs RMAN

2 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 ml 2

Oracle Database Oracle Developer/2000 Web Oracle Database

3 Oracle Database RMAN RMAN RMAN RMAN 11g <Insert Picture Here> Appendix RMAN 3

4 Oracle Export/Import OSSQL OracleRMAN 4

5 Oracle Export/Import Export ExportImport Export Export/Import Export/Import Import 5

6 Oracle OSSQL OracleRMAN Backup OSSQL RMAN Recovery Manager RMAN Restore 6

7 ARCHIVE LOG NOARCHIVE LOG REDO ARCHIVE LOG REDO / /RMAN NOARCHIVE LOG ARCHIVE LOG 7

8 NOARCHIVE LOG NOARCHIVE LOG 1 2OS 3 1 2OS 3 A () REDO A REDO 8

9 Oracle Database RMAN RMAN RMAN RMAN 11g <Insert Picture Here> Appendix RMAN 9

10 SQLOS 10

11 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

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.

12 SQL> ALTER TABLESPACE BEGIN BACKUP; OS SQL> ALTER TABLESPACE END BACKUP; A UPDATEAB UPDATEBC REDO A 12

13 13

14 OS REDO D A REDO AB BC CD SQL> RECOVER TABLESPACE ; ORA-00279: change generated at 05/11/ :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 for thread 1 is in sequence #15 Specify log: {<RET>=suggested filename AUTO CANCEL} Enter REDO Log applied. Media recovery complete. Enter 14

15 Tips REDO REDOREDO NOARCHIVE LOG 15

16 Tips REDO REDO REDO 16

17 Oracle Database RMAN RMAN RMAN RMAN 11g <Insert Picture Here> Appendix RMAN 17

18 Recovery ManagerRMAN Oracle 2 OSRMAN Oracle Enterprise ManagerEMGUI % rman target / RMAN> bacbakup database; RMAN> restore database; RMAN> recover database; 18

19 RMAN RMAN RMAN SPFILE or Oracle Secure Backup 19

20 RMAN Oracle Secure Backup RMAN 20

21 RMAN / RMAN OSUNIXcp BACKUP AS COPY OS RMAN BACKUP RMAN 21

22 RMAN / RMAN 9i R2EE 10gSE 2 22

23 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

24 RMAN RMAN CTWR RMAN 24

25 RMAN RMAN SYSDBA % rman target sys/sys TARGET CATALOG (RCVCAT) AUXILIARY (RCVCAT8.0) Data Guard Point-in- Time 25

26 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 DB_1 DATABASE SNCFORCL.ORA'; # default 26

27 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

28 RMAN RMAN

29 RMAN RMAN> list backup; =================== BS Key Type LV Size Device Type Elapsed Time Full M DISK 00:04: BP: 52 : AVAILABLE : YES : TAG T :D: ORACLE FLASH_RECOVERY_AREA ORCL BACKUPSET 2008_12_19 O1_MF_NNNDF_TAG T141724_4NPCMD3X_.BKP 52 File LV Type Ckp SCN Ckp Name Full D: ORACLE ORADATA ORCL SYSTEM01.DBF 2 Full D: ORACLE ORADATA ORCL SYSAUX01.DBF 3 Full D: ORACLE ORADATA ORCL UNDOTBS01.DBF 4 Full D: ORACLE ORADATA ORCL USERS01.DBF 5 Full D: ORACLE ORADATA ORCL EXAMPLE01.DBF 29

30 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 D: ORACLE FLASH_RECOVERY_AREA ORCL BACKUPSET 2008_12_19 O1_MF_N NDF_TAG T141724_4NPCMD3X_.BKP D: ORACLE PRODUCT DB_1 RDBMS ARC00217_ RMAN> delete obsolete; 30

31 RMAN 2 RMAN> report need backup redundancy 2; 2 File #bkps Name 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

32 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 EXPIRED DISK /u01/app/oracle/flash_recovery_area/orcl/backupset/2008_09_17/o1_mf_nnndf_tag T141100_4f14dzl0_.bkp Do you really want to delete the above objects (enter YES or NO)? Yes deleted backup piece 32

33 Tips Tips RMAN RMAN RMAN> backup database include current controlfile; RMAN> configure controlfile autobackup on; RMAN 33

34 Tips RMAN RMAN 34

35 RMAN11g : : 35

36 RMAN> list failure; RMAN> advise failure; RMAN> repair failure; Oracle : 4: 'D: ORACLE ORADATA ORCL USERS01.DBF' 2. D: ORACLE ORADATA ORCL SYSTEM01.DBF : d: oracle diag rdbms orcl orcl hm reco_ hm : restore database; recover database; alter database open; : 00:00:03 recover(: ) 36

37 Oracle Database RMAN RMAN RMAN RMAN 11g Appendix RMAN 37

38 <Insert Picture Here> Appendix RMAN 11g 38

39 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

40 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 ONLINE ONLINE FILE NOT FOUND 0 SQL> ALTER DATABASE DATAFILE 4 OFFLINE; SQL> ALTER DATABASE OPEN; 40

41 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 OFFLINE OFFLINE FILE NOT FOUND 41

42 SQL> recover datafile 4; ORA-00279: (12/19/ :04:11)1 ORA-00289: :D: ORACLE FLASH_RECOVERY_AREA ORCL ARCHIVELOG 20 9 O1_MF_1_212_4NPBW5XS_.ARC ORA-00280: (1)212 : {<RET>=suggested filename AUTO CANCEL} :D: ORACLE FLASH_RECOVERY_AREA ORCL ARCHIVELOG 20 9 O1_MF_1_213_4NPBWCH4_.ARC ORA-00280: (1)213 : {<RET>=suggested filename AUTO CANCEL} REDO Enter 42

43 SQL> SELECT * FROM v$recover_file; SQL> SELECT file#,status FROM v$datafile; FILE# STATUS SYSTEM 2 ONLINE 3 ONLINE 4 OFFLINE 5 ONLINE 6 ONLINE 7 ONLINE 8 ONLINE SQL> ALTER DATABASE DATAFILE 4 ONLINE; 43

44 RMAN> BACKUP TABLESPACE users; backup(: ) : 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( ) ORA_DISK_1: 1( ) =D: ORACLE FLASH_RECOVERY_AREA ORCL BACKUPSET 2008_12_19 O1_MF_ NNNDF_TA T142638_4NPD4OF5_.BKP =TAG T =NONE ORA_DISK_1: : 00:00:07 backup(: ) 44

45 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 ONLINE ONLINE FILE NOT FOUND 0 SQL> ALTER DATABASE DATAFILE 4 OFFLINE; SQL> ALTER DATABASE OPEN; 45

46 RMAN> restore datafile 4; restore(: ) : ORA_DISK_1 ORA_DISK_1: D: ORACLE ORADATA ORCL USERS01.DBF restore(: ) RMAN> recover datafile 4; recover(: ) ORA_DISK_1 : 00:00:01 recover(: ) 46

47 RMAN> sql ALTER DATABASE DATAFILE 4 ONLINE"; SQL> SELECT file#,status FROM v$datafile; FILE# STATUS SYSTEM 2 ONLINE 3 ONLINE 4 ONLINE 5 ONLINE 6 ONLINE 7 ONLINE 8 ONLINE RMANSQL sql sql 47

48 SQL> startup ORACLE Total System Global Area bytes Fixed Size bytes Variable Size bytes Database Buffers bytes Redo Buffers bytes ORA-00205:????????????????????????????????????????? REDO 48

49 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 ======================== : : d: oracle diag rdbms orcl orcl hm reco_ hm 49

50 RMAN> repair failure; : : d: oracle diag rdbms orcl orcl hm reco_ hm : # restore control file restore controlfile from autobackup; sql 'alter database mount'; (YESNO) yes restore(: ) SQL: alter database mount : ORA_DISK_1 50

51 RMAN> advise failure all; ======================= Oracle: 4: 'D: ORACLE ORADATA ORCL USERS01.DBF' D: ORACLE ORADATA ORCL SYSTEM01.DBF D: ORACLE ORADATA ORCL SYSAUX01.DBF D: ORACLE ORADATA ORCL UNDOTBS01.DBF ======================== : Point-in-Time : d: oracle diag rdbms orcl orcl hm reco_ hm 51

52 REDO RMAN> repair failure ; : Point-in-Time : d: oracle diag rdbms orcl orcl hm reco_ hm : # database point-in-time recovery until a missing log restore database until scn ; recover database until scn ; alter database open resetlogs; (YESNO) yes : 00:00:03 recover(: ) 52

53 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)!? :: Oracle University 53

54 Oracle Direct Seminar 100Wendy !! a.b.c.d. a,b,c,d Oracle Direct 54

55 OTN!! Oracle Technology Network(OTN) OTN OTNOracle100% OTN OTN OTN 55

56 IT Oracle Direct Concierge BI IT BI Access / SQL Server MySQL / PostgreSQL Oracle Database Oracle Developer Web RFP BI Oracle Database Oracle Database 56

57 Oracle Direct Oracle Direct IT / Web ~9:00~12:0013:00~18:00 57

58 OraclePeopleSoftJD EdwardsSiebel 58

意外と簡単！？ Oracle Database 11g -バックアップ・リカバリ編-

意外と簡単！？ Oracle Database 11g -バックアップ・リカバリ編- Oracle Direct Seminar !?Oracle Database 11g -- Agenda Oracle Direct Concierge SQL Server MySQL PostgreSQL Access Oracle Database Oracle Developer/2000 Web Oracle Database Oracle Database