세미나(장애와복구-수강생용).ppt

Size: px

Start display at page:

Download "세미나(장애와복구-수강생용).ppt"

선미 맹
6 years ago
Views:

1 DB PLAN Consultant

2 DB

3 1

4 INSTANCE MMAN RECO RFS MRP ORBn RBAL MMON Dnnn Snnn Data Buffer Cache SGA Stream Pool Shared pool Large Pool PGA Log Buffer Java Pool PMON DBWR CKPT LGWR SMON RVWR ARCH Control files Data files Parameter File Redo log files Flash-Back Database Logs Archive files Archive files

5 Select. (Parse). (Execute). (Fetch) 1 PGA 3.. / 4 %sqlplus scott/tiger 2 1> 1> SELECT * 2> 2> FROM emp emp 3> 3> ORDER BY BY ename; Database buffer cache 1 2 Data files 1 2 Instance SGA Redo log buffer Control files Database Shared pool Library cache Data dictionary cache Redo log files

6 DML 3 Instance. (Parse). (Execute) Server process. 4 2 Database buffer cache SGA Redo log buffer 1 1 Shared pool Library cache Data dictionary cache. / %sqlplus scott/tiger UPDATE emp emp SET SET sal=sal*1.1 WHERE empno=1; 1 Data files 1 2 Control files Database Redo log files

7 Shared pool Redo log buffer DBWR Data files COMMIT COMMIT LGWR 3 Database buffer cache SGA Instance Sql> commit; Sql> commit; Committed. Committed. Committed. Redo log files CKPT Control files

8 Init.ora db_name=ora9 Shared_pool_size=1000 Log_buffer=8192 SQL> Startup Instance Started. Finxed Size DataBuffer Cache Log Buffer 8192 Shared pool Buffer Database mounted. Database opened. 1 1) Init.ora. 2) SGA. 3) Background. 4) Alert_<DB >.log. Data Buffer Cache (Instance) SGA Log Buffer Shared pool Large Pool 3 1)Control. 2). 2 C:\SYSTEM.DBF D:\INSA.DBF E:\RBS.DBF DBWR CKPT LGWR PMON SMON 1) control.ctl( ). 2) init.ora Control. 3) db_name. 4). Control.ctl db_name=ora9 C:\SYSTEM.DBF D:\INSA.DBF E:\RBS.DBF

9 SCN:100 SCN:100 SCN:100 SYSTEM01.DBF CONTROL01.CTL REDO01.LOG INIT.ORA SCN:100 SCN:100 SCN:100 UNDOTBS01.DBF CONTROL02.CTL REDO02.LOG SCN:100 SCN:100 SCN:100 TEMP01.DBF CONTROL03.CTL REDO03.LOG SCN:100 USERS01.DBF SCN:100 QUERY01.DBF

10 SQL> Startup Instance Started. Finxed Size DataBuffer Cache Logo Buffer 8192 Shared pool Buffer Database mounted. ORA ORA : 9 : c:\users01.dbf CONTROL.CTL DB Name : ORA92 SCN : 100 Log-S/N : 57 SYSTEM : C:\SYSTEM.DBF 100M ON-LINE UNDO : C:\UNDO01.DBF 300M ON-LINE TEMP USERS : C:\TEMP01.DBF 200M : C:\USERS01.DBF 500M ON-LINE OFF-LINE REDO1 : D:\REDO1.LOG 500K ON-LINE REDO2 : D:\REDO2.LOG 500K ON-LINE Max Data Files : 1200 MaxLog files : 10 MaxLog Member : 5 Characterset 100 : KO16KSC5601 Control Files 100 C:\USERS01.DBF 100 C:\SYSTEM.DBF 100 C:\TEMP01.DBF 100 C:\UNDO01.DBF

11 System Change Number Backup Backup 101 Control files Parameter files Control files Parameter files Data files Log files Data files Log files Oracle9i Control Files-101 files Parameter files Data files Log files

12 Files V$CONTROLFILE V$CONTROLFILE_RECORD_SECTION CREATE DATABASE ora90 V$DATABASE LOGFILE GROUP 1 ( c:\oracle\oradata\ora90\redo01.log ) size 10m, GROUP 2 ( c:\oracle\oradata\ora90\redo02.log ) size 10m) DATAFILE c:\oracle\oradata\ora90\system01.dbf size 100m UNDO TABLESPACE undo DATAFILE c:\oracle\oradata\ora90\undo01.dbf size 50m DEFAULT TEMPORARY TABLESPACE temp TEMPFILE c:\oracle\oradata\ora90\temp01.dbf size 30m EXTENT MANAGEMENT LOCAL UNIFORM size 1m CHARACTER SET ko16ksc5601 NATIONAL CHARACTER SET al16utf16 SET TIME_ZONE = Korea/Seoul ; Data Files Control Files Redo-Log Files Parameter File SYSTEM.DBF CONTROL.CTL REDO1.LOG,,,,,, UNDO.DBF,,,,, TEMP.DBF,,,,, INSA.DBF DB Name : ORA9 SCN : Log-S/N : 257 SYSTEM : C:\SYSTEM.DBF 100M ON-LINE UNDO : C:\UNDO.DBF 300M ON-LINE TEMP INSA : C:\TEMP.DBF 200M : C:\INSA.DBF 500M ON-LINE ON-LINE REDO1 REDO2 Max Data Files : 1200 MaxLog files : 10 : D:\REDO1.LOG 500K ON-LINE : D:\REDO2.LOG 500K ON-LINE MaxLog Member : 5 Characterset : KO16KSC REDO2.LOG INIT.ORA DB_NAME=ORA8

13 Backup Method Recovery Method Physical (Archive, NoArchive) OffLine Backup (Close, Cold Backup) Physical (Archive) OnLine Backup (Open, Hot Backup) Physical (Archive, NoArchive) Logical Mode RMAN Utility Export/Import Utility NoArchive Mode Archive Mode Full DB Recovery Complete Recovery 1) Full DB 2) Tablespace 3) Datafile InComplete Recovery 1) Cancel Based 2) Time Based 3) Change Based 4) Redo-Log

14 2 DB

15 - ( ) ( ) - ( ) ( )

16 Create tablespace chul Datafile d:\data\chul.dbf size 500m; Create table jeon(idate date, no char(2), name v2(20), qty number) Tablespace chul; jeon tv tube power cable Create tablespace chul1999 Datafile d:\data\chul1999.dbf size 500m; Create tablespace chul2000 Datafile e:\data\chul2000.dbf size 500m; Create table jeon1999(idate date, no char(2), name v2(20), qty number) Tablespace chul1999; Create table jeon2000(idate date, no char(2), name v2(20), qty number) Tablespace chul2000; Create view tot_chul As select * from chul1999 Union all select * from chul2000; jeon tv tube 100 jeon power cable

17 Create tablespace chul1999 Datafile d:\data\chul1999.dbf size 500m; Create tablespace chul2000 Datafile e:\data\chul2000.dbf size 500m; Create tablespace chul2001 Datafile f:\data\chul2001.dbf size 500m; Create table jeon(idate date, no char(2), name v2(20), qty number) Partition By Range(idate) (Partition t1 values less than(200001) Tablespace chul1999, Partition t2 values less than(200101) Tablespace chul2000, Partition t3 values less than(maxvalue) Tablespace chul2001); Jeon tv tube 100 jeon power cable jeon case box

18 CKPT Data files Disk-1 Control files Redo log files Undo file Disk-2 Control files -2 Temp file System file Disk-3 Control files -3

19 (PFILE) 1. SQL> SHUTDOWN C:\> copy control01.ctl control04.ctl INIT<DB >.ORA. Control_files= (control01.ctl,,, control04.ctl). SQL> STARTUP ALTER DATABASE BACKUP CONTROLFILE TO TRACE;

20 (SPFILE) SQL> ALTER SYSTEM SET control_files = c:\oracle\oradata\ora90\control01.ctl, c:\oracle\oradata\ora90\control02.ctl, c:\oracle\oradata\ora90\control03.ctl, c:\oracle\oradata\ora90\control04.ctl SCOPE =SPFILE; SQL> SHUTDOWN. C:\> copy control01.ctl control04.ctl. SQL> STARTUP

21 UPDATE emp emp SET SET sal=sal*1.1; %sqlplus scott/tiger Instance LGWR Redo-Log Buffer Redo log buffer ARCH Redo log files Redo log files Redo log files Redo log files Redo log files Redo log files

22 Shared pool Redo log buffer DBWR Data files LGWR 3 Database buffer cache SGA Instance Sql> commit; Sql> commit; Committed. Committed. Committed. Redo log files CKPT Control files Log_checkpoint_timeout Log_checkpoint_interval Fast_start_io_target Fast_start_mttr_target

24 Data Mirror 1). Hardware Based RAID - RAID (Redundant Arrays of Independent Disks) SYSTEM. SoftWare Based RAID - Logical Volume Manager (Unix, Linux, Window-NT/XP ) - ORACLE (Automatic Storage Management) 2) Create or replace trigger back_emp After update or delete or insert on emp Begin Insert into backup_emp Values (,,,, ); End;

25 R A I D

26 RAID 1) 1988 David A. Patterson. 2). 3) (Stripe) (Mirror). 4),. 5). (5 RAID )

27 RAID 1) HA-Solution 2) FS-Solution RAID Controller Disk Controller Disk Controller Disk Controller

28 RAID-1 D0 D3 D6 D9 DISK D0 D3 D6 D9 DISK1 D1 D4 D7 D10 DISK2 D1 D4 D7 D10 DISK3 D2 D5 D8 D11 DISK4 D2 D5 D8 D11 DISK5 1) (DISK-Mirroring) 2). 3).

29 WINDOW-NT RAID-5

30 ASM

31 Automatic Storage Management CREATE DISKGROUP dgroup1 NORMAL REDUNDANCY 2 FAILGROUP controller1 DISK '/devices/diska1', '/devices/diska2', '/devices/diska3', FAILGROUP controller2 DISK '/devices/diskb1', '/devices/diskb2', '/devices/diskb3 ; CREATE TABLESPACE sales DATAFILE '+dgroup1' SIZE 200M AUTOEXTEND ON;

33 Sqlplus scott/tiger SQL> CREATE TABLE emp ( a NUMBER) TABLESPACE t_sales; Sqlplus sales/sales123 SQL> CREATE TABLE ord ( a NUMBER) TABLESPACE t_sales; SQL> CREATE TABLE item ( b NUMBER) TABLESPACE t_account; SQL> CREATE TABLE inv ( b NUMBER) TABLESPACE t_account; Oracle DB t_sales t_account SCOTT.EMP SALES.ORD SCOTT.BORD SALES.INV SCOTT.DEPT SALES.SLIP SCOTT.ITEM SALES.PROD

34 Oracle DB Oracle DB sales_s sales_l account mis emp dept sal slip s_emp s_dept a_slip Create user sales Identified by sales123; Create user mis Identified by mis123; Create user account Identified by acc123;

35 3 -

36 Redo log buffer Redo log files-1 Redo-Logo Buffer Instance Redo log files-2 Redo log files LGWR

37 OffLine Backup Oracle 9i Oracle 8 Control File Redo-Log File Data File Parameter File 1 SQL> connect sys/man as sysdba SQL > shutdown SQL > cd $ORACLE_HOME 2 SQL > copy *.ctl c:\backup\*.ctl SQL > copy *.log c:\backup\*.log SQL > copy *.dbf c:\backup\*.dbf SQL > copy *.ora c:\/backup\*.ora

38 Backup Backup 201 Control files Parameter files Control files Parameter files Data files Log files Data files Log files 302 Oracle9i Control Files-201 files Parameter files Data files Log files

39 Full DB Recovery(NoArchive) Oracle 9i Oracle SQL> connect sys/man as sysdba SQL> shutdown SQL> cd $ORACLE_HOME SQL> copy *.ctl c:\backup\*.ctl SQL> copy *.log c:\backup\*.log SQL> copy *.dbf c:\backup\*.dbf SQL> copy *.ora c:\/backup\*.ora copy c:\backup\*.ctl *.ctl copy c:\backup\*.log *.log copy c:\backup\*.dbf *.dbf copy c:\backup\*.ora *.ora SQL> connect sys/man as sysdba SQL> startup

40 Redo log buffer Redo log files-1 Redo-Logo Buffer Instance Redo log files-2 Redo log files LGWR Redo log files ARCH Archive files Archive files

41 Archive Mode * INIT<DB >.ORA LOG_ARCHIVE_START = TRUE LOG_ARCHIVE_DEST = [archivefile ] LOG_ARCHIVE_DEST_n = [archivefile ] LOG_ARCHIVE_FORMAT = [format type].[ ] DB_RECOVERY_FILE_DEST = 2 3 SQL> STARTUP MOUNT SQL> ALTER DATABASE [ ARCHIVELOG NOARCHIVELOG]; SQL> ALTER DATABASE OPEN; SQL> ARCHIVE LOG LIST;

42 OFF-Line

43 Full DB Oracle 9i SCN:95 Oracle 9i LGWR ARCH SQL> shutdown LOG1 ARC1(6/10) del user01.dbf copy *.ctl c:\backup\*.ctl copy *.log c:\/backup\*.log copy *.dbf c:\/backup\*.dbf copy *.ora c:\/backup\*.ora LOG2 LOG3 ARC2(6/11) ARC3(6/12) copy c:\backup\users01.dbf users01.dbf SQL> startup mount SQL> recover database; SQL> alter database open;

44 SYSTEM01.DBF SCN:100 UNDOTBS01.DBF SCN:100 TEMP01.DBF SCN:100 QUERY01.DBF SCN:100 CONTROL01.CTL SCN:100 REDO01.LOG SCN:100 REDO02.LOG SCN:100 REDO03.LOG SCN:100 SYSTEM01.DBF SCN:95 UNDOTBS01.DBF SCN:95 TEMP01.DBF SCN:95 QUERY01.DBF SCN:95 CONTROL01.CTL SCN:95 REDO01.LOG SCN:95 REDO02.LOG SCN:95 REDO03.LOG SCN: USERS01.DBF SCN:95 USERS01.DBF SCN:95 USERS01.DBF SCN:100

45 SQL> Startup Instance Started. Finxed Size DataBuffer Cache Logo Buffer 8192 Shared pool Buffer Database mounted. ORA-01113: 9. ORA-01110: 9 : c:\users01.dbf CONTROL.CTL DB Name : ORA92 SCN : 100 Log-S/N : 57 SYSTEM : C:\SYSTEM.DBF 100M ON-LINE UNDO : C:\UNDO01.DBF 300M ON-LINE TEMP USERS : C:\TEMP01.DBF 200M : C:\USERS01.DBF 500M ON-LINE OFF-LINE REDO1 : D:\REDO1.LOG 500K ON-LINE REDO2 : D:\REDO2.LOG 500K ON-LINE Max Data Files : 1200 MaxLog files : 10 MaxLog Member : 5 Characterset 100 : KO16KSC5601 Control Files 95 C:\USERS01.DBF 100 C:\SYSTEM.DBF 100 C:\TEMP01.DBF 100 C:\UNDO01.DBF

46 Archive Backup Data YSTEM.DBF UNDO.DBF TEMP.DBF USERS01.DBF CONTROL.CTL REDO1.LOG REDO2.LOG ARC1.LOG ARC2.LOG ARC3.LOG

47 4 -

48 - (Instance) Data Buffer Cache SGA Logo Buffer Shared pool Large Pool PGA PMON DBWR CKPT LGWR SMON RVWR Control files Data files Redo log files Flash-Back Database Logs Parameter File

49 - Flash-Back Database Logs 2004/07/01 FlashBack Logs 2004/07/02 FlashBack Logs 2004/07/03 FlashBack Logs 2 3 Control files Data files 2004/07/ /07/ /07/03 1 SQL> FLASHBACK DATABASE TO TIMESTAMP(SYSDATE 1)

50 - Flash-Back Database Logs 2004/07/03 18:20:25 Logs 2004/07/03 19:20:43 Logs 2004/07/03 20:25:35 Logs 2 3 Control files Data files 2004/07/03 18:20:25 Update emp Set sal = sal * 2; 2004/07/03 19:20:43 Update emp Set sal = sal * 3; 2004/07/03 20:25:35 1 SQL> FLASHBACK TABLE emp TO TIMESTAMP TO_TIMESTAMP( 2004/07/03 19:20:43 )

51 1 * INIT<DB >.ORA DB_RECOVERY_FILE_DEST = [ ] # LOG_ARCHIVE_START = TRUE # LOG_ARCHIVE_DEST = [archivefile ] # LOG_ARCHIVE_DEST_n = [archivefile ] # LOG_ARCHIVE_FORMAT = [format type].[ ] 2 SQL> STARTUP MOUNT SQL> ALTER DATABASE ARCHIVELOG; SQL> ALTER DATABASE FLASHBACK ON; SQL> ALTER DATABASE OPEN;

52 - 1) Flash-Back DROP 2) Flash-Back Row History 3) Flash-Back Transaction History

53 - (V 9i) SQL> connect scott/tiger SQL> SELECT empno, ename, job FROM emp WHERE empno = 7900; EMPNO ENAME JOB JAMES CLERK SQL> SELECT systimestamp FROM dual; SYSTIMESTAMP /25 23:58: :00 SQL> DELETE FROM emp WHERE empno = 7900; SQL> COMMIT; SQL> SELECT empno, ename, job FROM emp WHERE empno = 7900;. SQL> connect system/manager SQL> EXECUTE DBMS_FLASHBACK.ENABLE_AT_TIME( /25 23:58: ); SQL> SELECT empno, ename, job FROM scott.emp WHERE empno = 7900; EMPNO ENAME JOB JAMES CLERK SQL> EXECUTE DBMS_FLASHBACK.DISABLE;

54 Init.ora 1 undo_management = auto undo_retention = 300 PMON : 2001/11/2 15:02:12 Delete From emp Where empno = 7902; Commit; Scn: Select ~~;,,,,,,,,,, Delete ~~;,,,,,,,,,, Map-Table 3 UNDOTBS Tablespace <81089> <81090> ,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,, 2001/11/2 15:02:12 5 : 2001/11/2 18:30:34 Exec dbms_flashback.enable _at_time( 01/11/2 15:02:12 ) Select * from emp Where empno = 7902; Exec dbms_flashback.disable;

55 5 Data Guard

56 Data Guard 1) Fail-Over ORA 10g (Instance A) ORA 10g (Instance B) 2) Fail-Over Primary ORA 10g (Instance A) ORA 10g (Instance B)

57 Data Guard Data Guard Broker Process Data Guard Broker Process ORA 10g ORCL ARCH ARC36 2 *.DBF *.LOG *.ORA ORA 10g ORCL1 ARC36 1 SQL> alter database create standby conrolfile as stnb.ctl ; SQL>alter system archive log current; 3 SQL>startup nomount; copy *.LOG \ \*.LOG copy *.DBF \ \*.DBF copy *.ORA \ \*.ORA copy stnb.ctl \ \*.CTL copy *.ARC \ \*.ARC SQL>alter database mount standby database; SQL>recover standby database; SQL>alter database open;

58 Data Guard Broker Process Data Guard Broker Process ORA 10g ORA 10g LGWR Synchronous RFS MRP Online Redo-Log ARCH Standby Online Redo-Log ARCH Archive Redo-Log Archive Redo-Log

59 Fail Over Listener1 (Port : 1521) Node1( ) Client Tnsnames.ora Listener2 (port : 1522) Node2( ) ORA816=

61 Time Files = 200 GB SYSTEM.DBF. RBS.DBF. Archive = 5 GB (100Mx50 ) ARC1.log ARC2.log OnLine Backup : 3 1 Datafile(4GB) Restore : 10 Datafile(200GB) Restore : 4 1 Archive-File : 5 TEMP.DBF. INSA.DBF.... 1) 1 Datafile(4GB) = 10 + (5 x 50 ) = 260 (4 20 ) REDO1.LOG ARC50.log 2) Datafile = (5 x 50 ) = 490 (8 20 ) REDO2.LOG

62

2 instance database physical storage 2 1 62 63 tablespace datafiles 2 2 64 1 2 logical view control files datafiles redo log files 65 2 3 9i OMF Oracle Managed Files, OMF 9i 9i / / OMF 9i 66 8 1MB 8 10MB