create pfile from spfile 为什么没有instance_name参数

ITPUB里一个网友通过CREATE PFILE FROM SPFILE 创建PFILE。

但是PFILE却没有包含INSTANCE_NAME。

 

其实create pfile from spfile得到的是非默认参数的值列表。

大部分也就是ALERT中列出的：

Starting up ORACLE RDBMS Version: 10.2.0.4.0.
System parameters with non-default values:
  processes                = 150
  sessions                 = 170
  __shared_pool_size       = 352321536
  __large_pool_size        = 16777216
  __java_pool_size         = 33554432
  __streams_pool_size      = 0
  sga_target               = 1610612736
  control_files            = /test/orcl/orcl/control01.ctl, /test/orcl/orcl/control02.ctl, /test/orcl/orcl/control03.ctl
  db_block_size            = 8192
  __db_cache_size          = 1191182336
  db_writer_processes      = 2
  compatible               = 10.2.0.3.0
  log_archive_dest_1       = LOCATION=/test/orcl/arch
  log_archive_format       = %t_%s_%r.arc
  log_checkpoint_timeout   = 1800
  db_file_multiblock_read_count= 16
  db_recovery_file_dest    = /test/orcl/flashback
  db_recovery_file_dest_size= 2147483648
  log_checkpoints_to_alert = TRUE
  undo_management          = AUTO
  undo_tablespace          = UNDOTBS1
  remote_login_passwordfile= EXCLUSIVE
  db_domain                =
  dispatchers              = (PROTOCOL=TCP) (SERVICE=orclXDB)
  job_queue_processes      = 10
  background_dump_dest     = /u01/oracle/admin/orcl/bdump
  user_dump_dest           = /u01/oracle/admin/orcl/udump
  core_dump_dest           = /u01/oracle/admin/orcl/cdump
  audit_file_dest          = /u01/oracle/admin/orcl/adump
  db_name                  = orcl
  open_cursors             = 300
  pga_aggregate_target     = 1646264320

SQL> show parameter instance_name

NAME TYPE VALUE
------------------------------------ ---------------------- ------------------------------

instance_name string orcl
SQL> create pfile from spfile;

File created.


SQL> create pfile='/tmp/initorcl.ora' from spfile;

File created.

SQL> !

[oracle@testdb2 ~]$ cat /tmp/initorcl.ora
orcl.__db_cache_size=1191182336
orcl.__java_pool_size=33554432
orcl.__large_pool_size=16777216
orcl.__shared_pool_size=352321536
orcl.__streams_pool_size=0
*.audit_file_dest='/u01/oracle/admin/orcl/adump'
*.background_dump_dest='/u01/oracle/admin/orcl/bdump'
*.compatible='10.2.0.3.0'
*.control_files='/test/orcl/orcl/control01.ctl','/test/orcl/orcl/control02.ctl','/test/orcl/orcl/control03.ctl'
*.core_dump_dest='/u01/oracle/admin/orcl/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='orcl'
*.db_recovery_file_dest='/test/orcl/flashback'
*.db_recovery_file_dest_size=2147483648
*.db_writer_processes=2
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
*.job_queue_processes=10
*.log_archive_dest_1='LOCATION=/test/orcl/arch'
*.log_archive_format='%t_%s_%r.arc'
*.log_checkpoint_timeout=1800
*.log_checkpoints_to_alert=TRUE
*.open_cursors=300
*.pga_aggregate_target=1646264320
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sessions=170
*.sga_target=1610612736
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/u01/oracle/admin/orcl/udump'

之所以initorcl.ora中没有INSTANCE_NAME参数的值，是因为大部分情况下这个参数我们都没有手工设置，都是默认的。

SQL> alter system set instance_name=orcl scope=spfile;

System altered.


SQL> startup force nomount
ORACLE instance started.

Total System Global Area 1610612736 bytes
Fixed Size 2084400 bytes
Variable Size 402653648 bytes
Database Buffers 1191182336 bytes
Redo Buffers 14692352 bytes

SQL> create pfile='/tmp/initorcl.ora' from spfile;

File created.

SQL> !

[oracle@testdb2 ~]$ cat /tmp/initorcl.ora
orcl.__db_cache_size=1191182336
orcl.__java_pool_size=33554432
orcl.__large_pool_size=16777216
orcl.__shared_pool_size=352321536
orcl.__streams_pool_size=0
*.audit_file_dest='/u01/oracle/admin/orcl/adump'
*.background_dump_dest='/u01/oracle/admin/orcl/bdump'
*.compatible='10.2.0.3.0'
*.control_files='/test/orcl/orcl/control01.ctl','/test/orcl/orcl/control02.ctl','/test/orcl/orcl/control03.ctl'
*.core_dump_dest='/u01/oracle/admin/orcl/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='orcl'
*.db_recovery_file_dest='/test/orcl/flashback'
*.db_recovery_file_dest_size=2147483648
*.db_writer_processes=2
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
*.instance_name='ORCL'
*.job_queue_processes=10
*.log_archive_dest_1='LOCATION=/test/orcl/arch'
*.log_archive_format='%t_%s_%r.arc'
*.log_checkpoint_timeout=1800
*.log_checkpoints_to_alert=TRUE
*.open_cursors=300
*.pga_aggregate_target=1646264320
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sessions=170
*.sga_target=1610612736
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/u01/oracle/admin/orcl/udump'

启动日志如下：

Starting ORACLE instance (normal)
Mon Jul 30 15:02:39 2012
ALTER SYSTEM SET instance_name='ORCL' SCOPE=SPFILE;
Mon Jul 30 15:09:25 2012
Shutting down instance (abort)
License high water mark = 1
Instance terminated by USER, pid = 361700
Mon Jul 30 15:09:28 2012
Starting ORACLE instance (normal)
sskgpgetexecname failed to get name
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
Interface type 1 en1 10.192.14.0 configured from OCR for use as a cluster interconnect
Interface type 1 en0 10.192.39.0 configured from OCR for use as  a public interface
Picked latch-free SCN scheme 3
Autotune of undo retention is turned on.
IMODE=BR
ILAT =18
LICENSE_MAX_USERS = 0
SYS auditing is disabled
ksdpec: called for event 13740 prior to event group initialization
Starting up ORACLE RDBMS Version: 10.2.0.4.0.
System parameters with non-default values:
  processes                = 150
  sessions                 = 170
  __shared_pool_size       = 352321536
  __large_pool_size        = 16777216
  __java_pool_size         = 33554432
  __streams_pool_size      = 0
  sga_target               = 1610612736
  control_files            = /test/orcl/orcl/control01.ctl, /test/orcl/orcl/control02.ctl, /test/orcl/orcl/control03.ctl
  db_block_size            = 8192
  __db_cache_size          = 1191182336
  db_writer_processes      = 2
  compatible               = 10.2.0.3.0
  log_archive_dest_1       = LOCATION=/test/orcl/arch
  log_archive_format       = %t_%s_%r.arc
  log_checkpoint_timeout   = 1800
  db_file_multiblock_read_count= 16
  db_recovery_file_dest    = /test/orcl/flashback
  db_recovery_file_dest_size= 2147483648
  log_checkpoints_to_alert = TRUE
  undo_management          = AUTO
  undo_tablespace          = UNDOTBS1
  remote_login_passwordfile= EXCLUSIVE
  db_domain                =
  instance_name            = ORCL
  dispatchers              = (PROTOCOL=TCP) (SERVICE=orclXDB)
  job_queue_processes      = 10
  background_dump_dest     = /u01/oracle/admin/orcl/bdump
  user_dump_dest           = /u01/oracle/admin/orcl/udump
  core_dump_dest           = /u01/oracle/admin/orcl/cdump
  audit_file_dest          = /u01/oracle/admin/orcl/adump
  db_name                  = orcl
  open_cursors             = 300
  pga_aggregate_target     = 1646264320
Mon Jul 30 15:09:29 2012
Oracle instance running with ODM: Veritas 5.0 ODM Library, Version 1.1
cluster interconnect IPC version:
        VERITAS IPC '5.0.3.400' 04:49:57 Apr 16 2010
IPC Vendor 86 proto 76
  Version 1.0
PMON started with pid=2, OS id=349380
DIAG started with pid=3, OS id=226632
PSP0 started with pid=4, OS id=259598
LMON started with pid=5, OS id=229922
LMD0 started with pid=6, OS id=222894
MMAN started with pid=7, OS id=352898
DBW0 started with pid=8, OS id=659822
DBW1 started with pid=9, OS id=287696
LGWR started with pid=10, OS id=268238
CKPT started with pid=11, OS id=349890
SMON started with pid=12, OS id=320048
RECO started with pid=13, OS id=247756
CJQ0 started with pid=14, OS id=198316
MMON started with pid=15, OS id=348700
Mon Jul 30 15:09:29 2012
starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...