Oracle 11g新特性-初始化文件创建(Memory)
Oracle 11g对于初始化增加了一个特别有用的功能,它可以根据内存中的参数设置来创建初始化参数文件。
Oracle从9i推出SPFILE之后,就给出了使用PFILE创建SPFILE以及由SPFILE创建PFILE的语法。
在Oracle 11g中,Oracle增强了这种语法,使得创建PFILE或SPFILE时,不在需要指定一个物理的文件,而是可以从当前内存中的设置来获取参数配置。
采用这种方式创建的PFILE或SPFILE,可以保证获得的参数就是当前运行的参数,而如果从PFILE或SPFILE则无法确保文件中的参数设置与数据库运行的参数设置一致。
语法很简单,将创建时的FROM语句后面的文件类型PFILE或SPFILE改为MEMORY就可以了。
[oracle@yangtk ~]$ sqlplus "/ as sysdba"
SQL*Plus: Release 11.1.0.6.0 - Production on Thu Jan 10 15:06:30 2008 Copyright (c) 1982, 2007, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> create pfile='/home/oracle/initora11g_p.ora' from memory;
File created.
SQL> create spfile='/home/oracle/spfileora11g_p.ora' from memory;
File created.
注意:上面的这种方式要求数据库至少处于NOMOUNT状态,否则会出现报错:
SQL> shutdown immediate
Database closed. Database dismounted. ORACLE instance shut down. SQL> create pfile='/home/oracle/initora11g_p.ora' from memory; create pfile='/home/oracle/initora11g_p.ora' from memory * ERROR at line 1: ORA-00922: missing or invalid option SQL> create spfile='/home/oracle/spfileora11g_p.ora' from memory; create spfile='/home/oracle/spfileora11g_p.ora' from memory * ERROR at line 1: ORA-00922: missing or invalid option SQL> startup nomount ORACLE instance started. Total System Global Area 267825152 bytes Fixed Size 1299316 bytes Variable Size 176163980 bytes Database Buffers 88080384 bytes Redo Buffers 2281472 bytes SQL> create pfile='/home/oracle/initora11g_p.ora' from memory; File created. SQL> create spfile='/home/oracle/spfileora11g_p.ora' from memory; File created.
案例:
[root@rh6 ~]# su - oracle
[oracle@rh6 ~]$ export ORACLE_SID=test1
[oracle@rh6 ~]$ sqlplus '/as sysdba' SQL*Plus: Release 11.2.0.1.0 Production on Mon Feb 24 16:22:52 2014 Copyright (c) 1982, 2009, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options 16:22:52 SYS@ test1 >create spfile='/home/oracle/spfiletest1.ora' from memory; File created. [oracle@rh6 ~]$ strings /home/oracle/spfiletest1.ora |more *.__db_cache_size=16M *.__java_pool_size=24M *.__large_pool_size=32M *.__oracle_base='/u01/app/oracle' # ORACLE_BASE set from environment *.__pga_aggregate_target=10M *.__sga_target=280M *.__shared_io_pool_size=0 *.__shared_pool_size=128M *.__streams_pool_size=0 *._aggregation_optimization_settings=0 *._always_anti_join='CHOOSE' *._always_semi_join='CHOOSE' *._and_pruning_enabled=TRUE *._b_tree_bitmap_plans=TRUE *._bloom_filter_enabled=TRUE *._bloom_folding_enabled=TRUE *._bloom_prun ing_enabled=TRUE *._complex_view_merging=TRUE *._compression_compatibility='11.2.0' *._connect_by_use_union_all='TRUE' *._convert_set_to_join=FALSE *._cost_equality_semi_join=TRUE *._cpu_to_io=0 *._dimension_skip_null=TRUE *._eliminate_common_subexpr=TRUE *._enable_type_dep_selectivity=TRUE *._fast_full_scan_enabled=TRUE *._first_k_rows_dynamic_proration=TRUE *._gby_hash_aggregation_enabled=TRUE *._generalized_pruning_enabled=TRUE *._globalindex_pnum_filter_enabled=TRUE *._gs_anti_se mi_join_allowed=TRUE *._improved_outerjoin_card=TRUE *._improved_row_length_enabled=TRUE *._index_join_enabled=TRUE *._ksb_restart_policy_times='0' *._ksb_restart_policy_times='60' *._ksb_restart_policy_times='120' *._ksb_restart_policy_times='240' # internal update to set default *._left_nested_loops_random=TRUE *._local_communication_costing_enabled=TRUE *._minimal_stats_aggregation=TRUE ...... 以上为截取的部分spfile里的内容 。