DB PLAN Consultant jina6678@yahoo.co.kr 011-864-1858
- - 1. 2. DB 3. - 4. - 5. 6.
1
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
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
DML 3 Instance. (Parse). (Execute) Server process. 4 2 Database buffer cache 1 1 1 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
Shared pool Redo log buffer DBWR Data files COMMIT COMMIT LGWR 3 Database buffer cache SGA Instance Sql> commit; Sql> commit; 1 5 1 1 2 2 7 1 4 Committed. Committed. Committed. Redo log files 1 1 102 2 2 103 CKPT 8014 6 Control files 103 8014 8014 8014 1 2 1 2
Init.ora db_name=ora9 Shared_pool_size=1000 Log_buffer=8192 SQL> Startup Instance Started. Finxed Size DataBuffer Cache 3000000 Log Buffer 8192 Shared pool Buffer 3500000 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
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
SQL> Startup Instance Started. Finxed Size DataBuffer Cache 3000000 Logo Buffer 8192 Shared pool Buffer 3500000 Database mounted. ORA-01157 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 100 C:\USERS01.DBF 100 C:\SYSTEM.DBF 100 C:\TEMP01.DBF 100 C:\UNDO01.DBF
System Change Number 1 2001 6 30 Backup 2 100 2001 7 31 Backup 101 Control files Parameter files Control files Parameter files Data files Log files Data files Log files 3 102 Oracle9i Control Files-101 files Parameter files Data files Log files
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 80014 80014 80014 SYSTEM.DBF CONTROL.CTL REDO1.LOG,,,,,, 1 1 80014 UNDO.DBF,,,,, 80014 TEMP.DBF,,,,, 80014 INSA.DBF DB Name : ORA9 SCN : 800014 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 : KO16KSC5601 256 80014 REDO2.LOG 2 2 257 INIT.ORA DB_NAME=ORA8
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
2 DB
- ( ) ( ) - ( ) ( )
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 199912 1 tv tube 100 200001 2 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; jeon1999 199912 1 tv tube 100 jeon2000 200001 2 power cable
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 199911 1 tv tube 100 jeon 200001 2 power cable jeon 200101 3 case box
CKPT Data files Disk-1 Control files -1 102 103 Redo log files 2 1 2 1 Undo file Disk-2 Control files -2 Temp file System file Disk-3 Control files -3
(PFILE) 1. SQL> SHUTDOWN 2 3 4. C:\> copy control01.ctl control04.ctl INIT<DB >.ORA. Control_files= (control01.ctl,,, control04.ctl). SQL> STARTUP ALTER DATABASE BACKUP CONTROLFILE TO TRACE;
(SPFILE) 1 2 3 4 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
UPDATE emp emp SET SET sal=sal*1.1; %sqlplus scott/tiger Instance 2 2 1 1 LGWR Redo-Log Buffer 4 6 4 6 Redo log buffer 8 8 3 7 3 5 7 ARCH 1 7 1 7 Redo log files-1 2 8 2 8 3 3 Redo log files-2 4 4 5 5 Redo log files-3 6 6 1 7 1 7 Redo log files-1 3 3 Redo log files-2 5 5 Redo log files-3 2 8 2 8 4 4 6 6
Shared pool Redo log buffer DBWR Data files LGWR 3 Database buffer cache SGA Instance Sql> commit; Sql> commit; 1 5 1 1 2 2 7 1 4 Committed. Committed. Committed. Redo log files 1 1 102 2 2 103 CKPT 8014 6 Control files 103 8014 8014 8014 1 2 1 2 Log_checkpoint_timeout Log_checkpoint_interval Fast_start_io_target Fast_start_mttr_target
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;
R A I D
RAID 1) 1988 David A. Patterson. 2). 3) (Stripe) (Mirror). 4),. 5). (5 RAID )
RAID 1) HA-Solution 2) FS-Solution RAID Controller Disk Controller Disk Controller Disk Controller
RAID-1 D0 D3 D6 D9 DISK0 1 2 3 0 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).
WINDOW-NT RAID-5
ASM
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;
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
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;
3 -
Redo log buffer Redo log files-1 Redo-Logo Buffer Instance 1 1 1 1 Redo log files-2 Redo log files-3 2 2 3 3 4 4 5 6 6 7 7 8 8 2 2 3 3 4 4 5 5 6 6 7 7 8 8 LGWR
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
2001 6 30 Backup 100 2001 7 31 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
Full DB Recovery(NoArchive) 2001 6 10 12 2001 6 12 13 Oracle 9i Oracle 8 1 2 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
Redo log buffer Redo log files-1 Redo-Logo Buffer Instance 1 1 1 1 Redo log files-2 Redo log files-3 2 2 3 3 4 4 5 6 6 7 7 8 8 2 2 3 3 4 4 5 5 6 6 7 7 8 8 LGWR Redo log files-1 1 1 2 2 ARCH Archive files-2 3 3 4 4 Archive files-3 5 5 6 6
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;
OFF-Line
Full DB 2001 6 10 12 2001 6 12 13 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;
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:95 2001 6 10 12 2001 6 12 13 USERS01.DBF SCN:95 USERS01.DBF SCN:95 USERS01.DBF SCN:100
SQL> Startup Instance Started. Finxed Size DataBuffer Cache 3000000 Logo Buffer 8192 Shared pool Buffer 3500000 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
Archive 2001 6 10 12 Backup Data 100 100 100 95 100 100 100 YSTEM.DBF UNDO.DBF TEMP.DBF USERS01.DBF CONTROL.CTL REDO1.LOG REDO2.LOG ARC1.LOG ARC2.LOG ARC3.LOG
4 -
- (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
- 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/01 2004/07/02 2004/07/03 1 SQL> FLASHBACK DATABASE TO TIMESTAMP(SYSDATE 1)
- 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 )
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;
- 1) Flash-Back DROP 2) Flash-Back Row History 3) Flash-Back Transaction History
- (V 9i) SQL> connect scott/tiger SQL> SELECT empno, ename, job FROM emp WHERE empno = 7900; EMPNO ENAME JOB ----------------------------------------- 7900 JAMES CLERK SQL> SELECT systimestamp FROM dual; SYSTIMESTAMP ------------------------------------------------------ 2002.03/25 23:58:13.000000 +09: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( 2002.03/25 23:58:13.000000 ); SQL> SELECT empno, ename, job FROM scott.emp WHERE empno = 7900; EMPNO ENAME JOB ----------------------------------------- 7900 JAMES CLERK SQL> EXECUTE DBMS_FLASHBACK.DISABLE;
Init.ora 1 undo_management = auto undo_retention = 300 PMON : 2001/11/2 15:02:12 Delete From emp Where empno = 7902; Commit; Scn: 81089 2 4 81012 Select ~~;,,,,,,,,,, 81089 Delete ~~;,,,,,,,,,, Map-Table 3 UNDOTBS Tablespace <81089> 7902 1000 <81090> 7456 2300,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,, 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; 7902 1000 Exec dbms_flashback.disable;
5 Data Guard
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)
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 \192.9.200.2\*.LOG copy *.DBF \192.9.200.2\*.DBF copy *.ORA \192.9.200.2\*.ORA copy stnb.ctl \192.9.200.2\*.CTL copy *.ARC \192.9.200.2\*.ARC SQL>alter database mount standby database; SQL>recover standby database; SQL>alter database open;
Data Guard Broker Process Data Guard Broker Process ORA 10g 192.9.200.1 ORA 10g 192.9.200.2 LGWR Synchronous RFS MRP Online Redo-Log ARCH Standby Online Redo-Log ARCH Archive Redo-Log Archive Redo-Log
Fail Over Listener1 (Port : 1521) Node1(192.9.200.1) Client Tnsnames.ora Listener2 (port : 1522) Node2(192.9.200.2) ORA816=
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 = 240 + (5 x 50 ) = 490 (8 20 ) REDO2.LOG