TEXTSAMPLETEXTSAMPLEOracle数据库案例分享王安宁目录CONTENTS建库注意事项123数据库监听数据库日志分析4数据库备份5sqlserver优化-PART01-建库注意事项1、软件版本(mos文档ID742060.1)1、软件版本(mos文档ID742060.1)1、软件版本推荐11g版本，建议选择11.2.0.412c版本，nc65官方支持12.1.0.118c、19c未完全测试，理论上支持2、操作系统环境变量-win环境2、操作系统环境变量-linux环境、AIX环境3、多实例环境切换数据库实例4、EM管理器不需要安装5、建库后需要调整的参数altersystemset"_optimizer_cartesian_enabled"=falsescope=spfile;altersystemset"_b_tree_bitmap_plans"=falsescope=spfile;altersystemset"_optimizer_skip_scan_enabled"=falsescope=spfile;altersystemsetdeferred_segment_creation=falsescope=spfile;altersystemsetuse_indirect_data_buffers=falsescope=spfile;altersystemsetevent='10949tracenamecontextforever,level1'scope=spfile;altersystemsetaudit_trail=nonescope=spfile;altersystemset"_optimizer_group_by_placement"=falsescope=spfile;altersystemsetprocesses=3000scope=spfile;altersystemsetopen_cursors=3000scope=spfile;altersystemsetoptimizer_index_cost_adj=40scope=spfile;altersystemsetoptimizer_dynamic_sampling=4scope=spfile;禁止sqltuningadvisorBEGINDBMS_AUTO_TASK_ADMIN.disable(client_name=>'sqltuningadvisor',operation=>NULL,window_name=>NULL);END;/6、调整redo组数和大小alterdatabaseaddlogfilegroup4('/u01/app/oracle/oradata/PROD1/redo04.log')size512M;alterdatabaseaddlogfilegroup5('/u01/app/oracle/oradata/PROD1/redo05.log')size512M;alterdatabaseaddlogfilegroup6('/u01/app/oracle/oradata/PROD1/redo06.log')size512M;alterdatabaseaddlogfilegroup7('/u01/app/oracle/oradata/PROD1/redo07.log')size512M;alterdatabaseaddlogfilegroup8('/u01/app/oracle/oradata/PROD1/redo08.log')size512M;alterdatabaseaddlogfilegroup9('/u01/app/oracle/oradata/PROD1/redo09.log')size512M;--检查日志组状态selectgroup#,thread#,sequence#,statusfromv$log;--删除状态为inactive的日志组（主要删除前3组）alterdatabasedroplogfilegroup1;alterdatabasedroplogfilegroup2;--切换日志组（重复执行几次)altersystemswitchlogfile;--(直到前3组都为Inactive状态）altersystemcheckpint;selectgroup#,thread#,sequence#,statusfromv$log;alterdatabasedroplogfilegroup3;7、部署统计信息收集任务1、Oracle统计信息是存储在数据字典里的一组数据，从多个维度描述了oracle数据库里对象的详细信息。CBO会利用这些统计信息来计算目标SQL各种可能的、不同的执行路径成本，并从中选择一条成本值最小的执行路径来作为目标SQL的执行计划oracle数据库里的统计信息分为以下6种类型：表的统计信息：描述表的详细信息，如记录数、表块（表里的数据块数量）的数量、平均行长度等索引的统计信息：描述索引的详细信息，如索引的层级、叶子块的数量、聚簇因子等列的统计信息：描述列的详细信息，如列的distinct值的数量、列的null值数量、列的最小最大值及直方图系统统计信息：描述数据库服务器的系统处理能力，包含了CPU和I/O这两个维度，借助于系统统计信息，oracle可以更清楚地知道目标数据库服务器的实际处理能力数据字典统计信息：描述数据字典基表（如TAB$、IND$等）、数据字典基表上的索引，以及这些数据字典基表的列的详细信息，描述上述数据字典基表的统计信息与描述普通表、索引、列的统计信息没有本质区别内部对象统计信息：描述oracle数据库里的一些内部表（如X$系列表）的详细信息，它的维度和普通表的统计信息的维度类似，只不过其表块的数量为0，因为X$系列表实际上只是oracle自定义的内存结构，并不占用实际的物理存储空间2、检查表的统计信息是否是最新的（通过日期是不是最新的来判断）selecttable_name,to_char(last_analyzed,’yyyy-mm-ddhh24:mi:ss’),num_rowsfromuser_tables;3、收集某种表统计信息execdbms_stats.gather_table_stats(ownname=>'NC65',tabname=>'GL_DETAIL',estimate_percent=>100,method_opt=>'forallcolumnssize1',parallel=>8,cascade=>true);analyze_tab.sql-PART02-数据库监听1、监听器①第一步，客户端或者其他数据库，发起一个连接请求②监听器收到请求，并提供一个合适的服务句柄，注册到数据库③客户端连接到数据库后，监听器不再提供服务，而是通过服务句柄连接到数据库1、动态监听&静态监听动态注册：客户端（sys用户）只能在远程数据库启动的情况下连接上，远程数据库关闭时客户端连接不上远程数据库。静态注册：无论远程数据库处于开启还是关闭状态，客户端（sys用户）都可连接上。2、监听文件配置:$ORACLE_HOME/network/admin/listener.ora#listener.oraNetworkConfigurationFile:/u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora#GeneratedbyOracleconfigurationtools.SID_LIST_LISTENER=(SID_LIST=(SID_DESC=(GLOBAL_DBNAME=PROD1)(ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1)(SID_NAME=PROD1)))LISTENER=(DESCRIPTION_LIST=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=vdedu1.example.com)(PORT=1521))(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521))))ADR_BASE_LISTENER=/u01/app/oracle3、监听文件配置:$ORACLE_HOME/network/admin/tnsnames.ora4、常见监听问题Êý¾Ý¿â¼àÌýÎÊÌâ»ã×ÜnetcaÖؽ¨¼àÌý-PART03-数据库日志分析1、单实例数据库日志01路径：linux监听日志:$ORACLE_BASE/diag/tnslsnr/机器名/listener/traceWindows监听日志:/u01/app/oracle/diag/tnslsnr/机器名/listener/trace02路径：linux监听日志:$ORACLE_BASE/diag/rdbms/orcl/orcl/traceWindows监听日志:%ORACLE_BASE%\diag\rdbms\orcl\orcl\trace监听日志告警日志2、RAC数据库日志3、案例分享ora-4030´¦Àíora-1578&ora-1110rclatchµ¼ÖÂϵͳÑÏÖØÐÔÄÜÎÊÌâ×Ö·û¼¯ÎÊÌâalert_prod1.log-PART04-数据库备份数据库备份1.冷备VS热备2.expdp/impdp3.exp/imp4.Rman5.几种备份方式对比1、冷备vs热备2、expdp/impdpexpdp/impdp属于数据库服务器端的工具，推荐使用这种方式做逻辑备份。需要在数据库中创建directory，并在数据库服务器上创建目录。expdp\'/assysdba\'directory=expdp_20180603dumpfile=expdp_180603_%U.dmplogfile=expdp_180603.logparallel=4schemas=NC65impdpNC65/***directory=expdp_20180603dumpfile=expdp_180603_%U.dmplogfile=expdp_180603.logparallel=4win_oracle_expdp±¸·Ý.twin»·¾³É¾³ý7ÌìÇ°¹élinux_oracle_expdp.txtlinux_ɾ³ý7ÌìÇ°¹éµµ3、exp/impexp/imp属于第三方客户端工具，不支持并行，导出的dmp文件会受到客户端字符集影响。win_oracle_exp±¸·Ý.tlinux_oracle_exp.txtlinux_ɾ³ý7ÌìÇ°¹éµµwin»·¾³É¾³ý7ÌìÇ°¹é4、rman备份rman增量备份0级与全备都是全库备份,但是增量备份0级使用rman备份的话就只备份了usedblock,而全备份备份了usedandunusedblock。4、差异增量备份&累积增量备份oracle_0¼¶±¸·Ý_linux.oracle_1¼¶ÔöÁ¿±¸·Ý_linu5、几种备份方式对比-PART05-sqlserver优化1、行版本控制--查询selectsnapshot_isolation_state,is_read_committed_snapshot_onfromsys.databaseswherename=’dbname’--启用ALTERDATABASEdbnameSETALLOW_SNAPSHOT_ISOLATIONON;GOALTERDATABASEdbnameSETREAD_COMMITTED_SNAPSHOTON;GOSETTRANSACTIONISOLATIONLEVELREADCOMMITTEDGO2、限制最大内存建议将数据库最大内存设置为机器内存的50%~80%。若数据库无限占用内存，则会导致操作系统或其他应用申请不到足够内存，导致操作系统响应缓慢。最终也会影响数据库性能。--打开高级选项(这个命令开启后，高级配置参数才可以设置，只执行一次即可，其它命令不用再执行)USEmasterEXECsp_configure'showadvancedoption','1'RECONFIGURE--查询EXECsp_configure'maxservermemory'GO--value:20000表示20G--修改EXECsp_configure'maxservermemory',valueRECONFIGUREGO3、统计信息数据库统计信息是否自动创建Sqlserver查询后对必要的列自动创建（实时）--查询selectis_auto_create_stats_on,is_auto_update_stats_onfromsys.databaseswherename=’dbname’--修改ALTERDATABASEdbnameSETAUTO_CREATE_STATISTICSON数据库统计信息是否及时更新Sqlserver自动管理统计信息--查询selectis_auto_create_stats_on,is_auto_update_stats_onfromsys.databases--修改ALTERDATABASEdbnameSETAUTO_UPDATE_STATISTICSON4、数据库自动收缩数据文件中的数据移动，可能会导致应用程序失败；尤其是在数据量很大的时候，不要使用自动收缩功能，会导致验证的性能问题；同时，还可能会导致碎片。以下为关闭自动收缩熟悉：--查询SELECTDATABASEPROPERTYEX('dbname','IsAutoShrink');--修改ALTERDATABASEdbnameSETAUTO_SHRINKOFF;服务一点通服务官方微信
