{"id":1788,"date":"2016-07-19T08:44:12","date_gmt":"2016-07-19T00:44:12","guid":{"rendered":"http:\/\/www.821121.com\/?p=1788"},"modified":"2022-10-11T09:07:40","modified_gmt":"2022-10-11T01:07:40","slug":"oracle%e5%88%9d%e5%a7%8b%e5%8c%96%e5%8f%82%e6%95%b0%e6%96%87%e4%bb%b6spfile%e3%80%81pfile%e4%bb%8b%e7%bb%8d","status":"publish","type":"post","link":"http:\/\/www.821121.com\/?p=1788","title":{"rendered":"ORACLE\u521d\u59cb\u5316\u53c2\u6570\u6587\u4ef6SPFILE\u3001PFILE\u4ecb\u7ecd"},"content":{"rendered":"

1.\u7b80\u4ecb<\/h3>\n

\u57289i\u4e4b\u524d\uff0c\u53c2\u6570\u6587\u4ef6\u53ea\u6709\u4e00\u79cd\uff0c\u5b83\u662f\u6587\u672c\u683c\u5f0f\u7684\uff0c\u79f0\u4e3apfile\uff0c\u57289i\u53ca\u4ee5\u540e\u7684\u7248\u672c\u4e2d\uff0c\u65b0\u589e\u4e86\u670d\u52a1\u5668\u53c2\u6570\u6587\u4ef6,\u79f0\u4e3aspfile,\u5b83\u662f\u4e8c\u8fdb\u5236\u683c\u5f0f\u7684\u3002\u8fd9\u4e24\u79cd\u53c2\u6570\u6587\u4ef6\u90fd\u662f\u7528\u6765\u5b58\u50a8\u53c2 \u6570\u914d\u7f6e\u4ee5\u4f9boracle\u8bfb\u53d6\u7684\uff0c\u4f46\u4e5f\u6709\u4e0d\u540c\u70b9\uff0c\u6ce8\u610f\u4ee5\u4e0b\u51e0\u70b9\uff1a<\/p>\n

1)pfile\u662f\u6587\u672c\u6587\u4ef6\uff0cspfile\u662f\u4e8c\u8fdb\u5236\u6587\u4ef6\uff1b<\/p>\n

2)\u5bf9\u4e8e\u53c2\u6570\u7684\u914d\u7f6e\uff0cpfile\u53ef\u4ee5\u76f4\u63a5\u4ee5\u6587\u672c\u7f16\u8f91\u5668\u6253\u5f00\u624b\u5de5\u914d\u7f6e\uff0c\u800cspfile\u4e0d\u884c\uff0c\u5fc5\u987b\u5728\u6570\u636e\u5e93\u542f\u52a8\u540e\uff0c\u901a\u8fc7sql\u547d\u4ee4\u8fdb\u884c\u5728\u7ebf\u4fee\u6539\uff0c\u5f53\u7136spfile\u4e5f\u53ef\u7528\u6587\u672c\u7f16\u8f91\u5668\u6253\u5f00\uff0c\u628a\u91cc\u9762\u6587\u672c\u7684\u5185\u5bb9\u590d\u5236\u51fa\u6765\u653e\u5728pfile\u4e2d\uff0c\u4f46\u51b3\u5bf9\u4e0d\u53ef\u4ee5\u76f4\u63a5\u4fee\u6539\uff0c\u76f4\u63a5\u4fee\u6539\u540e\u4f1a\u4f7f\u6587\u4ef6\u65e0\u6cd5\u8bfb\u53d6\u3002<\/p>\n

3)pfile\u914d\u7f6e\u6539\u53d8\u540e\uff0c\u8981\u4f7f\u7528\u5176\u751f\u6548\uff0c\u5fc5\u987b\u91cd\u65b0\u542f\u52a8\u6570\u636e\u5e93\uff0cspfile\u7684\u914d\u7f6e\u751f\u6548\u65f6\u9650\u548c\u4f5c\u7528\u57df\u53ef\u4ee5\u7531\u4fee\u6539\u53c2\u6570\u7684sql\u547d\u4ee4\u6307\u5b9a\uff0c\u53ef\u4ee5\u7acb\u5373\u751f\u6548\uff0c\u4e5f\u53ef\u4ee5\u4e0d\u7acb\u5373\u751f\u6548\u3002\u5f53\u7136\u6709\u4e9b\u53c2\u6570\u7684\u4fee\u6539\u5fc5\u987b\u91cd\u542f\u6570\u636e\u5e93\u624d\u80fd\u751f\u6548\uff1b<\/p>\n

4)\u53ef\u4ee5\u7528sql\u547d\u4ee4\u7531pfile\u521b\u5efaspfile,\u4e5f\u53ef\u4ee5\u7531spfile\u521b\u5efapfile\uff1b<\/p>\n

5)\u5982\u679c\u662f\u624b\u52a8\u521b\u5efa\u6570\u636e\u5e93\u800c\u4e0d\u662f\u901a\u8fc7DBCA\uff0c\u5219\u5f00\u59cb\u521b\u5efa\u6570\u636e\u5e93\u65f6\uff0c\u4f60\u53ea\u80fd\u5b9a\u4e49pfile \u3002\u56e0\u4e3a\u5b83\u662f\u6587\u672c\u683c\u5f0f\u7684\uff1b<\/p>\n

\u73b0\u5728\u4e00\u822c\u666e\u901a\u751f\u4ea7\u5e93\u90fd\u91c7\u7528spfile\u542f\u52a8\u6570\u636e\u5e93\uff0cpfile\u786e\u5b9e\u6ca1\u6709\u7528\u7684\u7406\u7531\u4e86\uff0c\u611f\u89c9\u53ea\u6709\u4ee5\u4e0b\u60c5\u51b5\u624d\u4f1a\u4f7f\u7528pfile\uff1a<\/p>\n

1)\u5165\u95e8\u5b66\u4e60\u65f6\uff0c\u4e3a\u4e86\u7406\u89e3\u521d\u59cb\u5316\u53c2\u6570<\/p>\n

2)\u521b\u5efa\u6570\u636e\u5e93\u65f6\uff0c\u56e0\u4e3a\u8fd9\u65f6\u8fd8\u6ca1spfile<\/p>\n

3)\u6062\u590d\u6570\u636e\u5e93\u65f6<\/p>\n

4)\u91c7\u7528RAC\u7684\u751f\u4ea7\u5e93\u4e2d\u5982\u679c\u8981\u5171\u7528spfile\uff0c\u53ef\u4ee5\u5efa\u4e00\u4e2a\u521d\u59cb\u7684pfile,\u7136\u540e\u5728pfile\u6307\u5b9aspfile\u4e3a\u5171\u4eab\u8bbe\u5907\u6587\u4ef6<\/p>\n

2.\u521b\u5efaspfile\u548cpfile\u8bed\u6cd5<\/h3>\n

\u521b\u5efaSPFILE(\u53ea\u80fd\u5efa\u5728\u6570\u636e\u5e93\u6240\u5728\u7684\u673a\u5668\u4e0a<\/h4>\n
CREATE SPFILE [= 'spfile_name'] FROM PFILE [= 'pfile_name'];<\/code><\/pre>\n
\u5982\u679c\u547d\u4ee4\u4e2d\u672a\u6307\u5b9aspfile\u7684\u8def\u5f84\u5219\u4f1a\u5728\u7f3a\u7701\u8def\u5f84\u521b\u5efaspfile\u6587\u4ef6\uff08%ORACLE_HOME%\\database\\spfile.ora\uff09
\n\u8fd9\u4e2a\u4e0espfile\u53c2\u6570\u65e0\u5173(rac**\u4eabspfile\u65f6\u8981\u6ce8\u610f)<\/p>\n
\u521b\u5efaPFILE<\/h4>\n
CREATE PFILE [= 'pfile_name'] FROM SPFILE [= 'spfile_name'];<\/code><\/pre>\n
\u5982\u679c\u547d\u4ee4\u4e2d\u672a\u6307\u5b9aspfile\u7684\u8def\u5f84\u5219\u4f1a\u4ece\u7f3a\u7701\u8def\u5f84\uff08%ORACLE_HOME%\\database\\spfile.ora\uff09\u521b\u5efapfile\u6587\u4ef6
\n\u6ce8\uff1a\u8bed\u6cd5\u4e2d\u521b\u5efa\u548c\u6307\u5b9a\u7684spfile\u548cpfile\u90fd\u662f\u670d\u52a1\u5668\u8def\u5f84
\n\u6ce8\uff1a\u4e0d\u80fd\u521b\u5efa\u5df2\u7ecf\u7531\u6570\u636e\u5e93\u5b9e\u4f8b\u542f\u52a8\u7684spfile(\u5728nomount\u72b6\u6001\u4e0b\u4e5f\u4e0d\u884c)<\/p>\n
3.\u6570\u636e\u5e93\u542f\u52a8\u65f6spfile\u548cpfile\u7684\u52a0\u8f7d\u987a\u5e8f<\/h3>\n
\u521d\u59cb\u5316\u53c2\u6570\u662f\u5728\u6570\u636e\u5e93\u5b9e\u4f8b\u542f\u52a8\u65f6(startup nomount)\u52a0\u8f7d\u3002
\n\u5982\u679cstartup\u547d\u4ee4\u4e2d\u672a\u6307\u5b9apfile\u53c2\u6570\uff0c\u5728windows\u7cfb\u7edf\u4e2d\u6570\u636e\u5e93\u542f\u52a8\u65f6\u4f1a\u6309\u5982\u4e0b\u987a\u5e8f\u67e5\u627e\u542f\u52a8\u53c2\u6570\u6587\u4ef6:<\/p>\n
%ORACLE_HOME%\\database\\SPFILE<SID>.ora\n%ORACLE_HOME%\\database\\SPFILE.ora\n%ORACLE_HOME%\\database\\init<SID>.ora<\/code><\/pre>\n
\u5728unix\u6216linux\u7cfb\u7edf\u4e2d\u6570\u636e\u5e93\u542f\u52a8\u65f6\u4f1a\u6309\u5982\u4e0b\u987a\u5e8f\u67e5\u627e\u542f\u52a8\u53c2\u6570\u6587\u4ef6:<\/p>\n
%ORACLE_HOME%\\dbs\\SPFILE<SID>.ora\n%ORACLE_HOME%\\dbs\\SPFILE.ora\n%ORACLE_HOME%\\dbs\\init<SID>.ora<\/code><\/pre>\n
\u4e5f\u5c31\u662f\u8bf4\u5148\u627espfile.ora\u6587\u4ef6\uff0c\u5982\u679cspfile.ora\u6ca1\u6709\u5219\u627espfile.ora\u6587\u4ef6\uff0c\u6700\u540e\u624d\u4f1a\u53bb\u627einit.ora\u7684pfile\u6587\u4ef6.
\n\u4f7f\u7528\u6307\u5b9apfile\u542f\u52a8\u6570\u636e\u5e93\u5219\u6570\u636e\u5e93\u4f1a\u4f7f\u7528\u6307\u5b9a\u7684pfile\u6765\u52a0\u8f7d\u6570\u636e\u5e93\u53c2\u6570\uff1a
\n\u547d\u4ee4\u5982\u4e0b\uff1a
\nstartup pfile=''
\n\u6ce8\uff1a\u4ee5pfile\u542f\u52a8\u7684\u5b9e\u4f8b,\u4fee\u6539\u521d\u59cb\u5316\u53c2\u6570\u65f6\u4e0d\u80fd\u4f7f\u7528scope=spfile\u7684\u8bed\u6cd5<\/p>\n
\u5982\u679c\u53c2\u6570\u6587\u4ef6(\u4e0d\u7ba1\u662fpfile\u8fd8\u662fspfile)\u4e2d\u6307\u5b9a\u4e86spfile\u7684\u53c2\u6570,\u5219\u8fd8\u4f1a\u4ece\u6307\u5b9a\u7684spfile\u4e2d\u518d\u8bfb\u53d6\u914d\u7f6e\u7684\u53c2\u6570\uff0c\u8fd9\u6837\u6570\u636e\u5e93\u7684\u53c2\u6570\u5c31\u5305\u62ec\u4e86\u4e24\u4e2a\u6587\u4ef6\u7684\u5185\u5bb9.4.\u6570\u636e\u5e93\u542f\u52a8\u53c2\u6570\u7684\u52a0\u8f7d\u987a\u5e8f
\n\u52a0\u8f7d\u987a\u5e8f\u6309\u6587\u4ef6\u4e2d\u987a\u5e8f\u8bfb\u53d6
\n\u5982\u679c\u6587\u4ef6\u4e2d\u6709\u4e24\u4e2a\u76f8\u540c\u7684\u53c2\u6570\u540d\u5219\u4ee5\u6700\u540e\u51fa\u73b0\u7684\u4e3a\u51c6\uff0c\u4ee5\u4e0b\u4f8b\u5b50\u8bf4\u660e\u4e86\u8fd9\u4e2a\u95ee\u9898\u3002<\/p>\n
\u5982\u6709\u4e00\u4e2aspfile\u6587\u4ef6(myspfile.ora)\uff0c\u5305\u62ec\u4e24\u4e2a\u53c2\u6570:
\nsessions=150
\nprocesses=200
\n\u53e6\u5916\u6709\u4e00\u4e2apfile\u6587\u4ef6(mypfile.ora)\uff0c\u91cc\u9762\u5305\u62ec\u4e09\u4e2a\u53c2\u6570\uff0c\u5176\u4e2d\u6307\u5b9a\u4e86spfile\u4e3a\u524d\u9762\u7684spfile\u6587\u4ef6
\nprocesses=100
\nsessions=100
\nspfile='myspfile.ora'
\nprocesses=180
\n\u5f53\u4f7f\u7528mypfile.ora\u542f\u52a8\u6570\u636e\u5e93\u65f6\uff0c\u4f1a\u6309\u5982\u4e0b\u987a\u5e8f\u8bfb\u53d6\u53c2\u6570
\n1)processes=100
\n2) sessions=100
\n3) spfile='myspfile.ora'
\n3.1) sessions=150(\u91cd\u65b0\u52a0\u8f7d)
\n3.2) processes=200(\u91cd\u65b0\u52a0\u8f7d)
\n4)processes=180(\u518d\u6b21\u91cd\u65b0\u52a0\u8f7d)
\n\u6700\u540e\u52a0\u8f7d\u7684\u6570\u636e\u5e93\u53c2\u6570\u4e3a
\nsessions=150
\nspfile='myspfile.ora'
\nprocesses=180<\/p>\n
5.\u5173\u4e8eshow parameter spfile\u65f6\u663e\u793a\u7684\u5185\u5bb9<\/h3>\n
\u5982\u679c\u662f\u91c7\u7528pfile\u6307\u5b9a\u4e86spfile\u53c2\u6570\uff0c\u5219\u663e\u793a\u6307\u5b9a\u7684spfile
\n\u5982\u679c\u662f\u91c7\u7528\u7f3a\u7701\u7684spfile\u542f\u52a8\uff0c\u5219\u663e\u793a\u7f3a\u7701\u7684spfile\u6587\u4ef6\u540d\uff0c\u4e0d\u7ba1spfile\u8fd8\u6709\u6ca1\u6709\u6307\u5b9a\u5176\u5b83\u7684spfile\u6587\u4ef6\u3002<\/p>\n
6.\u5173\u4e8espfile\u9012\u5f52\u8c03\u7528<\/h3>\n
\u5982\u679c\u51fa\u73b0spfile\u9012\u5f52\u8c03\u7528\uff0c\u5219ORACLE\u542f\u52a8\u65f6\u4f1a\u62a5\u9519\uff1a
\nSQL> startup nomount;
\nORA-03113: \u901a\u4fe1\u901a\u9053\u7684\u6587\u4ef6\u7ed3\u675f
\nSQL>
\nspfile\u9012\u5f52\u8c03\u7528\u5c31\u662f\u6307
\nspfile1.spfile='spfile2'
\nspfile2.spfile='spfile1'
\n\u8fd9\u6837\u7684\u53c2\u6570\u914d\u7f6e<\/p>\n
7.\u521d\u59cb\u5316\u53c2\u6570\u7684\u4fee\u6539\u65b9\u6cd5<\/h3>\n
\u5206\u4e3a\u624b\u52a8\u4fee\u6539\u548c\u5728\u7ebf\u4fee\u6539<\/h4>\n
\u3000\u3000\u624b\u52a8\u4fee\u6539\u7528\u4e8e\u4fee\u6539pfile\uff0c\u76f4\u63a5\u7528\u6587\u672c\u7f16\u8f91\u6253\u5f00pfile\u4fee\u6539\u3002\u8981\u4f7f\u7528\u4fee\u6539\u751f\u6548\uff0c\u987b\u91cd\u542f\u6570\u636e\u5e93\u3002
\n\u3000\u3000\u5728\u7ebf\u4fee\u6539\u662f\u5728\u6570\u636e\u5e93\u8fd0\u884c\u65f6\uff0c\u7528alter system\u547d\u4ee4\u8fdb\u884c\u4fee\u6539\uff0c\u547d\u4ee4\u5982\u4e0b(\u8be6\u7ec6\u7684\u547d\u4ee4 \u8bed\u53e5\u8bf7\u53c2\u8003oracle\u5b98\u65b9\u53c2\u8003\u6587\u6863)\uff1a<\/p>\n
alter system set parameter_name = parameter_value [, parameter_value ]...\n  [ COMMENT 'text' ]\n  [ DEFERRED ]\n  [ SCOPE = { MEMORY | SPFILE | BOTH } ]\n  [ SID = { 'sid' | * } ]\n  ```\n\u6ce8\uff1a\n[ COMMENT 'text' ] :\u8868\u793a\u53ef\u4ee5\u52a0\u6ce8\u91ca\uff0c\u5982 ALTER SYSTEM SET JOB_QUEUE_PROCESSES=50 SCOPE=BOTH DEFERRED COMMENT="  2016-07-19"\n\n[ DEFERRED ]:\u8868\u793a\u6240\u4f5c\u4fee\u6539\u53ea\u9002\u7528\u4e8e\u5c06\u6765\u7684\u4f1a\u8bdd\uff0c\u5bf9\u5f53\u524d\u5df2\u6709\u7684\u4f1a\u8bdd\u4e0d\u5f71\u54cd\uff0c\u8fd9\u4e2a\u53c2\u6570\u53ea\u5bf9\u5c11\u6570\u53c2\u6570\u6709\u7528\uff0c\u5177\u4f53\u53ef\u4f7f\u7528\u7684\u53c2\u6570\u53ef\u4ee5\u67e5\u770bv$parameter\u89c6\u56fe\n```shell\nselect * from v$parameter where issys_modifiable='DEFERRED'\n[ SCOPE = { MEMORY | SPFILE | BOTH } ]:<\/code><\/pre>\n
SPFILE:\u4fee\u6539\u53ea\u5bf9SPFILE\u6709\u6548\uff0c\u4e0d\u5f71\u54cd\u5f53\u524d\u5b9e\u4f8b\uff0c\u9700\u8981\u91cd\u542f\u6570\u636e\u5e93\u624d\u80fd\u751f\u6548\uff1b<\/p>\n","protected":false},"excerpt":{"rendered":"
1.\u7b80\u4ecb \u57289i\u4e4b\u524d\uff0c\u53c2\u6570\u6587\u4ef6\u53ea\u6709\u4e00\u79cd\uff0c\u5b83\u662f\u6587\u672c\u683c<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"class_list":["post-1788","post","type-post","status-publish","format-standard","hentry","category-uncategorized"],"_links":{"self":[{"href":"http:\/\/www.821121.com\/index.php?rest_route=\/wp\/v2\/posts\/1788","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/www.821121.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/www.821121.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/www.821121.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/www.821121.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1788"}],"version-history":[{"count":1,"href":"http:\/\/www.821121.com\/index.php?rest_route=\/wp\/v2\/posts\/1788\/revisions"}],"predecessor-version":[{"id":1789,"href":"http:\/\/www.821121.com\/index.php?rest_route=\/wp\/v2\/posts\/1788\/revisions\/1789"}],"wp:attachment":[{"href":"http:\/\/www.821121.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1788"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.821121.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1788"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.821121.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1788"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}