国内优秀设计网站推荐,保定网站开发,网络营销设计,可以用电脑做网站主机吗Oracle 通过ALTER SYSTEM语句修改数据库参数#xff0c;其语法#xff1a;ALTER SYSTEM SET scope[ memory | spfile | both ] [sid]SCOPE表示应用的范围#xff0c;分成三种memeory#xff1a;只在当前实例中修改生效#xff0c;重启后失效(内存中)spfile#xff1a;只…Oracle 通过ALTER SYSTEM语句修改数据库参数其语法ALTER SYSTEM SET scope[ memory | spfile | both ] [sid]SCOPE表示应用的范围分成三种memeory只在当前实例中修改生效重启后失效(内存中)spfile只在服务参数文件中修改不重启不生效(物理文件中)both在内存和物理文件中同时修改立即生效且永久保存修改之。在RAC环境中需要指定SID来指明需要修改的实例名称。修改某个系统参数并查看该语句实际的执行效果范例SQL alter system set sql_tracetrue;System altered.SQL alter database open;Database altered.SQL alter system set db_cache_adviceoff scopememory;System altered.SQL show parameter db_cache_adviceNAME TYPE VALUE------------------------------------ --------------------------------- ------------------------------db_cache_advice string OFFWarning: Switching off db_cache_advice with sga_target on is not recommendedALTER SYSTEM SET db_cache_adviceOFF SCOPEMEMORY;SQL ID: aqnw1wvv45uudPlan Hash: 0alter system set db_cache_adviceoff scopememorycall count cpu elapsed disk query current rows------- ------ -------- ---------- ---------- ---------- ---------- ----------Parse 1 0.00 0.00 0 0 0 0Execute 1 0.00 0.00 0 0 0 0Fetch 0 0.00 0.00 0 0 0 0------- ------ -------- ---------- ---------- ---------- ---------- ----------total 2 0.00 0.00 0 0 0 0Misses in library cache during parse: 0Parsing user id: SYS重启数据库后观察该参数被自动还原SQL show parameter db_cache_adviceNAME TYPE VALUE------------------------------------ --------------------------------- ------------------------------db_cache_advice string ON继续修改该参数并且应用范围修改为spfile观察发现当前实例下并未生效。SQL alter system set db_cache_adviceoff scopespfile;System altered.SQL show parameter db_cache_adviceNAME TYPE VALUE------------------------------------ --------------------------------- ------------------------------db_cache_advice string ONSQL startup forceORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instanceORACLE instance started.Total System Global Area 6680915968 bytesFixed Size 2213936 bytesVariable Size 3758098384 bytesDatabase Buffers 2885681152 bytesRedo Buffers 34922496 bytesDatabase mounted.Database opened.SQL show parameter db_cache_adviceNAME TYPE VALUE------------------------------------ --------------------------------- ------------------------------db_cache_advice string OFF可以看到该参数值重启后发生改变另上面出现一个ORA报错说死过期和被弃用的参数日志提示No Resource Manager plan activereplication_dependency_tracking turned off (no async multimaster replication found)SQL alter system set replication_dependency_trackingfalse scopespfile;System altered.SQL col name for a35SQL col value for a15SQL set line 300SQL select name,value from v$spparameter where namereplication_dependency_tracking;NAME VALUE----------------------------------- ---------------replication_dependency_tracking FALSE视乎不是这个参数导致的查看网上说是log_archive_start参数在10G以后数据库通过alter database archivelog自动开启归档进程而弃用该参数。SQL col name for a30SQL col value for a30SQL select name,value,isdeprecated from v$parameter where name like %log_archive_start;NAME VALUE ISDEPRECATED------------------------------ ------------------------------ ---------------log_archive_start FALSE TRUESQL startup forceORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instanceSQL alter system reset log_archive_start scopespfile;System altered.SQL startup forceORACLE instance started.SQL alter database archivelog;修改后发现不对思考了下该错误是我在开始SQL_TRACE后发生的。那么我们先看看当前有哪些参数是被弃用的SQL select name, value from v$parameter where isdeprecatedTRUE;NAME VALUE----------------------------------- ---------------......commit_writesql_trace TRUESQL alter system set sql_tracefalse scopeboth;System altered.SQL startup forceSQL alter system reset sql_trace scopespfile;SQL shutdown immediateSQL startup好了报错问题解决了。同时实验结束。另外如果错误的修改了spfile,可以在关闭服务的情况下可以修改pfile中的参数在通过pfile生成spfile文件SQL startup nomount pfile/DBBK/oracle/product/11.2.0.1.0/dbs/initorcl.oraORACLE instance started.Total System Global Area 6680915968 bytesFixed Size 2213936 bytesVariable Size 3758098384 bytesDatabase Buffers 2885681152 bytesRedo Buffers 34922496 bytesSQL show parameter pfileNAME TYPE VALUE------------------------------- -------------------------------- ------------------------------spfile stringSQL create spfile from pfile;File created.SQL create pfile from spfile;File created.spfile之前的博客中也提到过时一个二进制文件因此尽量不要用工具直接编辑该文件以免照成破坏导致无法启用数据库的状况。上面范例中我还提到一个重置系统参数值得命令alter system reset scope[ memory | spfile | both ] sid该命令通常用于修改RAC环境中也可以在单实例中使用其目的是从spfile中去除某个参数值SQL startup mount pfile/DBBK/oracle/product/11.2.0.1.0/dbs/initorcl.oraORACLE instance started.Total System Global Area 6680915968 bytesFixed Size 2213936 bytesVariable Size 3758098384 bytesDatabase Buffers 2885681152 bytesRedo Buffers 34922496 bytesDatabase mounted.启动后有两种方式确认是否读取的pfile文件参数查询v$spparameter视图确认服务参数值或者用show parameter命令。实际上show parameter命令也是在查该视图。之前的博客已展示过show parameter 实际调用的语句SQL select count(*) from v$spparameter where value is not null;COUNT(*)----------0SQL show parameter spfileNAME TYPE VALUE------------------------------------ --------------------------------- ------------------------------spfile string其他方法通过ISSPECIFIED(表示是否被SPFILE引用的参数)字段判断SQL select decode(count(*),1,spfile,pfile) USE from v$spparameter where isspecifiedTRUE;--下面的语句如果查询TRUE为空也表明使用的是pfile文件select isspecified,count(*) from v$spparameter group by isspecified;如果使用的是默认spfile启动的结果SQL select count(*) from v$spparameter where value is not null;COUNT(*)----------27SQL show parameter spfile;NAME TYPE VALUE------------------------------------ ----------- ------------------------------spfile string /u01/oracle/product/11.2.0.3.0/dbs/spfilesss.ora
