SAPBasis數(shù)據(jù)庫基礎(chǔ)

1、Business Consulting Services Copyright IBM Corporation 2005SAP數(shù)據(jù)庫管理基礎(chǔ)培訓(xùn)數(shù)據(jù)庫管理基礎(chǔ)培訓(xùn)IBM in ConfidenceBusiness Consulting Services Copyright IBM Corporation 2005 | 2021/5/25 1課程內(nèi)容課程內(nèi)容OracleOracle數(shù)據(jù)庫架構(gòu)數(shù)據(jù)庫架構(gòu)SAP R/3 Oracle數(shù)據(jù)庫管理SAP R/3 Oracle數(shù)據(jù)庫備份和歸檔Business Consulting Services Copyright IBM Corporation 2005

2、 | 2021/5/25 2OracleOracle服務(wù)器架構(gòu)服務(wù)器架構(gòu)Oracle 數(shù)據(jù)庫 物理結(jié)構(gòu) 邏輯結(jié)構(gòu)Oracle 實(shí)例 內(nèi)存結(jié)構(gòu) 進(jìn)程Business Consulting Services Copyright IBM Corporation 2005 | 2021/5/25 3Oracle Oracle 數(shù)據(jù)庫數(shù)據(jù)庫物理結(jié)構(gòu)：Oracle 數(shù)據(jù)庫在操作系統(tǒng)上的文件組織可以在不影響邏輯訪問的前提下改動(dòng)物理結(jié)構(gòu)邏輯結(jié)構(gòu)：Oracle 數(shù)據(jù)庫如何使用文件組織Business Consulting Services Copyright IBM Corporation 2005 | 202

3、1/5/25 4Oracle Oracle 數(shù)據(jù)庫數(shù)據(jù)庫 物理結(jié)構(gòu)物理結(jié)構(gòu)數(shù)據(jù)文件： 存放數(shù)據(jù)庫數(shù)據(jù)控制文件： 定義物理結(jié)構(gòu) 在Oracle實(shí)例啟動(dòng)時(shí)標(biāo)識數(shù)據(jù)文件和日志文件的位置日志文件 記錄所有的數(shù)據(jù)根改 在數(shù)據(jù)恢復(fù)時(shí)需要Business Consulting Services Copyright IBM Corporation 2005 | 2021/5/25 5SAPSAP的的OracleOracle文件結(jié)構(gòu)文件結(jié)構(gòu)initDBSID.sapinitDBSID.dbabininitDBSID.oradbsoracleSIDrdbmssaparchmirrlogBmirrlogAorigl

4、ogBoriglogAsapdata1sapreorgsapdata2sapdatanRedo log group set B and its mirrorArchives of redo logsWork directorySAP data filesddicd_1ddicd.data1/Redo log group set A and its mirrorBusiness Consulting Services Copyright IBM Corporation 2005 | 2021/5/25 6SAPSAP的的OracleOracle文件結(jié)構(gòu)文件結(jié)構(gòu) 續(xù)續(xù)bin: 可執(zhí)行文件，庫文件d

5、bs: 密碼和初始化文件rdbms: 消息庫和readme文件origlogA, origlogB: 分別含有兩個(gè)在線日志文件oraarch: 離線日至文件sapreorg: DBA工作目錄sapdata: SAP數(shù)據(jù)Business Consulting Services Copyright IBM Corporation 2005 | 2021/5/25 7Oracle Oracle 數(shù)據(jù)庫數(shù)據(jù)庫 邏輯結(jié)構(gòu)邏輯結(jié)構(gòu)表空間： 數(shù)據(jù)庫的邏輯劃分用來組織邏輯結(jié)構(gòu) （結(jié)構(gòu)對象） 包含一個(gè)或多個(gè)物理數(shù)據(jù)文件的數(shù)據(jù)空間結(jié)構(gòu)對象： 與數(shù)據(jù)庫數(shù)據(jù)相關(guān)聯(lián)的邏輯結(jié)構(gòu) 例如：表、視圖、索引、stored pro

6、cedure、synonym、cluster數(shù)據(jù)塊： 一組bytes的邏輯存儲結(jié)構(gòu) 在Oracle數(shù)據(jù)庫創(chuàng)建時(shí)定義 大小從2K到8K，在數(shù)據(jù)庫創(chuàng)建時(shí)定義Business Consulting Services Copyright IBM Corporation 2005 | 2021/5/25 8Oracle Oracle 數(shù)據(jù)庫數(shù)據(jù)庫 邏輯結(jié)構(gòu)（續(xù)）邏輯結(jié)構(gòu)（續(xù)）TablespaceSegmentExtentBlockExtentBlockSegmentExtentBlockExtentBlockBusiness Consulting Services Copyright IBM Corpo

7、ration 2005 | 2021/5/25 9Oracle Oracle 數(shù)據(jù)庫數(shù)據(jù)庫 邏輯結(jié)構(gòu)（續(xù)）邏輯結(jié)構(gòu)（續(xù)）Extent: 一組連續(xù)的數(shù)據(jù)塊，存儲在一個(gè)分區(qū)內(nèi) 用于存儲一種特定的信息（表、索引） 結(jié)構(gòu)對象擴(kuò)充空間時(shí)的最小單位 MAXEXTENTSSegment: a set of extents allocated,不必連續(xù) 之含有一種結(jié)構(gòu)對象Business Consulting Services Copyright IBM Corporation 2005 | 2021/5/25 10Oracle Oracle 實(shí)例實(shí)例內(nèi)存結(jié)構(gòu)： SGA （System Global Area

8、） PGA （Program Global Area）進(jìn)程 用戶進(jìn)程 Oracle 進(jìn)程Business Consulting Services Copyright IBM Corporation 2005 | 2021/5/25 11Oracle Oracle 實(shí)例實(shí)例 內(nèi)存結(jié)構(gòu)內(nèi)存結(jié)構(gòu)DDIC CacheShared SQLSGA： 一個(gè)Oracle實(shí)例的共享內(nèi)存 數(shù)據(jù)庫緩沖區(qū)Cache 日志緩沖區(qū) Shared Pool 數(shù)據(jù)詞典Cache 共享SQL CachePGA 每個(gè)進(jìn)程的控制和數(shù)據(jù)信息Business Consulting Services Copyright IBM Corp

9、oration 2005 | 2021/5/25 12OracleOracle實(shí)例實(shí)例 進(jìn)程進(jìn)程DBWRCKPTLGWRARCHDatabase filesArchivesControl FilesRedo Log FilesSGAUserProcessServer ProcessPGADatabase Buffer CacheOrasrvPMONSMONRedo LogBufferBusiness Consulting Services Copyright IBM Corporation 2005 | 2021/5/25 13OracleOracle實(shí)例實(shí)例 進(jìn)程（續(xù)）進(jìn)程（續(xù)）User P

10、rocess：由操作系統(tǒng)創(chuàng)建用于執(zhí)行一個(gè)應(yīng)用程序Server Process： 由Oracle創(chuàng)建，執(zhí)行User Process發(fā)出的請求 分配給SAP工作進(jìn)程（一一對應(yīng)）ORASRV：SQL *Net 通信進(jìn)程DBWR：將數(shù)據(jù)庫緩沖區(qū)內(nèi)更改的數(shù)據(jù)塊（data blocks）寫到位于硬盤的數(shù)據(jù)文件中（data file）LOWR：寫日志Business Consulting Services Copyright IBM Corporation 2005 | 2021/5/25 14OracleOracle實(shí)例實(shí)例 進(jìn)程（續(xù)）進(jìn)程（續(xù)）CKPT：特定事件的監(jiān)控系統(tǒng)，例如日志模式的改變、DBWR的

11、工作等等SMON：系統(tǒng)監(jiān)視器，在系統(tǒng)啟動(dòng)時(shí)恢復(fù)實(shí)例，清除臨時(shí)文件PMON：在用戶進(jìn)程失敗時(shí)進(jìn)行恢復(fù)，或恢復(fù)服務(wù)器進(jìn)程Business Consulting Services Copyright IBM Corporation 2005 | 2021/5/25 15課程內(nèi)容課程內(nèi)容Oracle數(shù)據(jù)庫架構(gòu)SAP R/3 OracleSAP R/3 Oracle數(shù)據(jù)庫管理數(shù)據(jù)庫管理SAP R/3 Oracle數(shù)據(jù)庫備份和歸檔Business Consulting Services Copyright IBM Corporation 2005 | 2021/5/25 16數(shù)據(jù)庫管理工具數(shù)據(jù)庫管理工具D

12、B02：表空間、表、索引監(jiān)控ST04：數(shù)據(jù)庫性能監(jiān)控DB24：數(shù)據(jù)庫管理員操作監(jiān)控RZ20：數(shù)據(jù)庫警報(bào)監(jiān)控Business Consulting Services Copyright IBM Corporation 2005 | 2021/5/25 17RZ20RZ20：數(shù)據(jù)庫警報(bào)監(jiān)控：數(shù)據(jù)庫警報(bào)監(jiān)控空間性能一致性備份R/3系統(tǒng)日志數(shù)據(jù)庫優(yōu)化模式： Rule & Cost (init.ora) SAP使用Cost based optimizer CHOOSE （在rule和cost之間動(dòng)態(tài)選擇） Cost模式需要表和索引的統(tǒng)計(jì)信息Business Consulting Services

13、 Copyright IBM Corporation 2005 | 2021/5/25 18數(shù)據(jù)庫優(yōu)化模式數(shù)據(jù)庫優(yōu)化模式 如何獲得統(tǒng)計(jì)信息如何獲得統(tǒng)計(jì)信息DB13 ：對于一整個(gè)數(shù)據(jù)庫表空間DB20：對于某一個(gè)數(shù)據(jù)庫表格DB21：更改統(tǒng)計(jì)配置Business Consulting Services Copyright IBM Corporation 2005 | 2021/5/25 19DB24DB24備份操作性能內(nèi)存結(jié)構(gòu)配置檢查操作Business Consulting Services Copyright IBM Corporation 2005 | 2021/5/25 20課程內(nèi)容課程內(nèi)容

14、Oracle數(shù)據(jù)庫架構(gòu)SAP R/3 Oracle數(shù)據(jù)庫管理SAP R/3 OracleSAP R/3 Oracle數(shù)據(jù)庫備份和歸檔數(shù)據(jù)庫備份和歸檔Business Consulting Services Copyright IBM Corporation 2005 | 2021/5/25 21備份模式備份模式 離線備份離線備份sqldba lmode=yconnect internalselect member from V$LOGFILE;select name from V$DBFILE;select name from V$CONTROLFILE;Mirror Log BRedo Lo

15、g BMirror Log ARollbackIndexData TempinitSID.oracntrlSID.dbfRedo Log ASystem Business Consulting Services Copyright IBM Corporation 2005 | 2021/5/25 22備份模式備份模式 在線備份在線備份SELECT tablespace_name, file_name FROM sys.dba_data_files ORDER BY tablespace_name, file_name; ALTER TABLESPACE name BEGIN BACKUP; D

16、ata fileALTER TABLESPACE name END BACKUP; SELECT MIN(sequence#) FROM v$log; SELECT MAX(sequence#) FROM v$log;ALTER SYSTEM SWITCH LOGFILE; Archive logArchive logcntrlSID.dbfBusiness Consulting Services Copyright IBM Corporation 2005 | 2021/5/25 23備份模式備份模式 在線備份在線備份 （續(xù)）（續(xù)）使用在線備份的前提條件：數(shù)據(jù)庫需要運(yùn)行在ARCHIVELOG

17、模式下恢復(fù)時(shí)，在備份時(shí)產(chǎn)生的日志文件不可缺少Business Consulting Services Copyright IBM Corporation 2005 | 2021/5/25 24備份模式備份模式 邏輯備份邏輯備份TABLE Modetable definitionstable dataowners grantsowners indexestable constraintstable triggersUSER Modeclustersdatabase linksviewsprivate synonymssequencessnapshotssnapshot logsstored pr

18、oceduresFULL Moderolesall synonymssystem privilegestablespace definitionstablespace quotasprofilesall triggerssystem audit optionsrollback segment definitionsWhat gets exported?Business Consulting Services Copyright IBM Corporation 2005 | 2021/5/25 25備份模式備份模式 邏輯備份（續(xù)）邏輯備份（續(xù)）邏輯備份模式不會備份任何控制文件邏輯備份時(shí)使用的ST

19、ATEMENTS必須保留，在恢復(fù)時(shí)將需要邏輯備份檢查數(shù)據(jù)一致性Business Consulting Services Copyright IBM Corporation 2005 | 2021/5/25 26備份策略備份策略備份計(jì)劃 備份種類：全備份（數(shù)據(jù)文件、控制文件、日志），部分（表空間。） 備份模式：在線、離線備份頻率：日、周、月災(zāi)難恢復(fù)策略 磁帶保留策略 On-site vs Off-site StorageBusiness Consulting Services Copyright IBM Corporation 2005 | 2021/5/25 27備份和歸檔步驟備份和歸檔步驟備

20、份策略備份參數(shù)文件：ORACLE_HOME/dbs/init.sap磁帶標(biāo)簽管理合理安排配分窗口備份模式的選擇選擇執(zhí)行方式：DB13、SAPDBA、第三方備份軟件日志： ORACLE_HOME/sapbackup/btimestamp.ext backSID.log ORACLE_HOME/saparch/btimestamp.ext archSID.logBusiness Consulting Services Copyright IBM Corporation 2005 | 2021/5/25 28BRBACKUP & BRARCHIVEBRBACKUP & BRARCHI

21、VEControl filesData filesOnline Redo LogsOfflineRedo logsDatabase- SDBAH- SDBADBRBACKUPBRARCHIVEDetailLog filesBusiness Consulting Services Copyright IBM Corporation 2005 | 2021/5/25 29備份策略的規(guī)劃備份策略的規(guī)劃 DB13 DB13Business Consulting Services Copyright IBM Corporation 2005 | 2021/5/25 30DBADBA日志日志 DB14 D

22、B14Business Consulting Services Copyright IBM Corporation 2005 | 2021/5/25 31備份參數(shù)文件備份參數(shù)文件 init.sap init.sap# (#)initHP.sap 20.23 SAP 95/11/28# SAP backup sample profile. # # backup mode all | | -# | | | () | sap_dir | ora_dirbackup_mode = all # backup type offline | offline_force | onlinebackup_type

23、 = online # backup device type# tape | disk | pipe | tape_auto | pipe_auto | util_file | util_file_onlinebackup_dev_type = disk # backup root directory | ()backup_root_dir = ?/sapbackup # compression flag yes | no | hardware | onlycompress = no # compress commandcompress_cmd = compress -c $ $ # unco

24、mpress commanduncompress_cmd = uncompress -c $ $ # directory for compression | ()compress_dir = ?/sapreorg? = $ORACLE_HOME1st $ = 2nd $ is target filename = .Z1st $ is source filename = .Z 2nd $ = Business Consulting Services Copyright IBM Corporation 2005 | 2021/5/25 32備份參數(shù)文件備份參數(shù)文件 init.sap init.sa

25、p（續(xù)）（續(xù)）# brarchive function save | second_copy | double_save | save_delete | second_copy_delete | double_save_delete | copy_save | copy_delete_save | delete_saved | delete_copiedarchive_function = save # directory for archive log copiesarchive_copy_dir = ?/sapbackup # flags for cpio output commandcp

26、io_flags = -ovB # flags for cpio input commandcpio_in_flags = -iduvB # flags for cpio command for copy of directories to diskcpio_disk_flags = -pdcu # flags for dd output commanddd_flags = obs=16k # flags for dd input commanddd_in_flags = ibs=16k # remote shell command (backup_dev_type = pipe)read_f

27、ifo_cmd = remsh hs0001 # remote copy-out command (backup_dev_type = pipe)copy_out_cmd = dd bs=5k conv=block of=$ # remote copy-in command (backup_dev_type = pipe)copy_in_cmd = dd bs=5k conv=block if=$if archiving to disk, whereBusiness Consulting Services Copyright IBM Corporation 2005 | 2021/5/25 3

28、3備份參數(shù)文件備份參數(shù)文件 init.sap init.sap（續(xù)）（續(xù)）# rewind command (operating system dependent)rewind = mt -t $ rew # rewind set offline command (operating system dependent)rewind_offline = mt -t $ offl # tape positioning command (operating system dependent)tape_pos_cmd = mt -t $ fsf $ # volume size in KB = K or

29、 in MB = M (backup device dependent)tape_size = 3600M # level of parallel executionexec_parallel = 0 # address of backup device without rewindtape_address = /dev/rmt/0mn # address of backup device without rewind to be used from BRARCHIVE# tape_address_arch = /dev/rmt/0mn # address of backup device with rewindtape_address_rew = /dev/rmt/0m # address of backup device with rewind to be used fr