分类： Oracle
2013-01-27 10:52:59
1參數文件：
初始化參數文件（initialization parameter files): 9i之前的方式，為文本文件，也叫客戶端參數文件。 名稱：initSID.ora
服務器參數文件（server parameter files),9i之後開始引用的是二進制格式，不能直接編輯，名稱spfile$ORACLE_SID.ora
server parameter files 可以通過alter system和alter session來修改不和編輯initialization parameter files.
創建語法
create spfile='spfile name' FROM PFILE='PFILE NAME';
存儲位置：
Win $ORACLE_HOME/dbs
unix & linux $ORACLE_HOME\database
DB啟動時參數文件缺省位置應用順序：
(1) spfile$ORACLE_SID.ora------->(2)spfile.ora------->(3)init$ORACLE_SID.ora------->報錯。
判斷是否啟用spfile
SQL>select * from v$spparameter where VALUE IS NOT NULL;
25
SQL>show parameter spfile
NAME TYPE VALUE
----------------- ------- ------------------------------
spfile string H:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\SPFILEORCL.ORA
有返回值即為啟用的是spfile方式.
修改參數
語法：alter system set
parameter=
comment: 備注信息，作查詢用，如為什麼這麼設置。
memory :改變當前實例並生效，重啟失效。
spfile :寫入spfile,當前無效，重啟生效。
both: :改變當前實例生效，並寫入spfile。
如果scope不寫(alter system set
parameter=
重置參數
語法：alter system reset parameter scope=memory|spfile|both sid='*';
執行後將把此參數從spfile移除,恢復默認設置。
處置一例:
有時server parameter files參數設置錯誤致DB啟不來(如undo_tablespace原來是UNDOTBS1，可spfile設置成了UDOTBS1) .
編輯一initialization parameter files參數文件initORCL.ora.
spfile=H:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\spfileORCL.ora
undo_tablespace=UNDOTBS1
重啟DB.
alter system set undo_tablespace=UNDOTBS1 comment='DBA修正，2013/01/26' scope=spfile sid='*'; 這樣即修正過來。
倒出方式：
1: cat H:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\SPFILEORCL.ORA >initORCL.ora
2: create pfile=parameter file name FROM SPFILE;
Viewing Parameter Settings
You can view parameter settings in several ways, as shown in the following table.
|
Method |
Description |
|
SHOW PARAMETERS |
This SQL*Plus command displays the values of initialization parameters in effect for the current session. |
|
SHOW SPPARAMETERS |
This SQL*Plus command displays the values of initialization parameters in the server parameter file (SPFILE). |
|
CREATE PFILE |
This SQL statement creates a text initialization parameter file (PFILE) from the SPFILE or from the current in-memory settings. You can then view the PFILE with any text editor. |
|
V$PARAMETER |
This view displays the values of initialization parameters in effect for the current session. |
|
V$PARAMETER2 |
This view displays the values of initialization parameters in effect for the current session. It is easier to distinguish list parameter values in this view because each list parameter value appears in a separate row. |
|
V$SYSTEM_PARAMETER |
This view displays the values of initialization parameters in effect for the instance. A new session inherits parameter values from the instance-wide values. |
|
V$SYSTEM_PARAMETER2 |
This view displays the values of initialization parameters in effect for the instance. A new session inherits parameter values from the instance-wide values. It is easier to distinguish list parameter values in this view because each list parameter value appears in a separate row. |
|
V$SPPARAMETER |
This view displays the current contents of the SPFILE. The view returns FALSE values in the ISSPECIFIED column if an SPFILE is not being used by the instance. |
4.v$parameter_valid_values
这个视图非常有用，只是可能不被人们所重视，它列出来的是每个参数的可取值。
5.V$OBSOLETE_PARAMETER
这个视图里记录了oracle废弃的参数
參考：http://www.itpub.net/thread-1159836-1-1.html
2:實例(INSTANCE)
Instance由Sga和至少5個後台進程（SMON、PMON、DBWR、 LGWR、 CKPT)組成。
Sga由shared pool、database buffer、log buffer、 large pool、 java pool、 stream pool組成。
Shared pool由library cache和dictionary cache組成。
後台進程：
SMON： The system monitor process (SMON) performs recovery, if necessary, at instance startup.SMON is also responsible for cleaning up temporary segments that are no longer in use and for coalescing contiguous free extents within dictionary managed tablespaces. If any terminated transactions were skipped during instance recovery because of file-read or offline errors, SMON recovers them when the tablespace or file is brought back online. SMON checks regularly to see whether it is needed. Other processes can call SMON
if they detect a need for it. With Real Application Clusters, the SMON process of one instance can perform instance recovery for a failed CPU or instance.
1.清理临时空间以及临时段
2.接合空闲空间 (DMT字典管理表空间中，这需要表空间的pctincrease设置为非零值)
3.执行实例恢复（Instance recovery)
4.离线（Offline）回滚段AUM)
5.执行并行恢复 (FAST_START_PARALLEL_ROLLBACK)
FAST_START_PARALLEL_ROLLBACK specifies the degree of parallelism used when recovering terminated transactions. Terminated transactions are transactions that are active before a system failure. If a system fails when there are uncommitted parallel DML or DDL transactions, then you can speed up transaction recovery during startup by using this parameter.
Values:
Parallel rollback is disabled
Limits the maximum degree of parallelism to 2 * CPU_COUNT
Limits the maximum degree of parallelism to 4 * CPU_COUNT
If you change the value of this parameter, then transaction recovery will be stopped and restarted with the new implied degree of parallelisme.
查詢完成狀況語法：
select undoblockstotal "Total block", undoblocksdone " Block Done", undoblockstotal-undoblocksdone "Total-Done",decode(cputime,0,'unknown',to_char(sysdate+(((undoblockstotal-undoblocksdone) / (undoblocksdone / cputime)) / 86400),'yyyy-mm-dd hh24:mi:ss')) "Estimated time to complete",to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from v$fast_start_transactions;
PMON
The process monitor (PMON) performs process recovery when a user process fails. PMON is responsible for cleaning up the database buffer cache and freeing resources that the user process was using. For example, it resets the status of the active transaction table, releases locks, and removes the process ID from the list of active processes. PMON periodically checks the status of dispatcher and server processes, and restarts any that have stopped running (but not any that Oracle has terminated intentionally). PMON also registers information about the instance and dispatcher processes with the network listener. Like SMON, PMON checks regularly to see whether it is needed and can be called if another process detects the need for it.
