Oracle Parameters File

I. Oracle pfile/spfile differences
PfileThe default name is "init+ routine name. Ora" file path: E:\oracle\product\10.2.0\db_1\dbs, which is a text file that can be opened with any text editing tool.
SPFileThe default name is "spfile+ routine name. Ora" File path: E:\oracle\product\10.2.0\db_1\dbs is in binary text form and cannot be modified with the VI editor.
Two files can be created with the command PFILE from SPFILE or create SPFILE from PFILE to each other

Difference:
1, the starting order SPFile priority over Pfile.
2, Pfile is a static file, after modification will not take effect immediately, the database must be restarted to read this file.
3, SPFile is a dynamic parameter file, is a binary file, you can not directly use Notepad and other programs to make changes, you can use the ALTER command to make changes, without restarting the database can also be effective.
Modification Method:
#查看参数信息
Sql>show parameter;
#修改参数
Sql>alter system Set Parameter name = value scope= parameter 2;
There are three values for parameter 2:
1. Scope=spfile: Changes to parameters are recorded in the server initialization parameter file, and the modified parameters take effect the next time the DB is started. Applies to dynamic and static initialization parameters.
2. Scope=memory: Changes to parameters are recorded in the memory, and changes to the dynamic initialization parameters take effect immediately. After the DB is restarted, it is lost and reverts to the parameter value before the change.
3. Scope=both: Changes to the parameters are recorded in both the server parameters file and the memory, for the dynamic parameters to take effect immediately, the static parameters can not use this option.
If you are using a server parameter file, Scope=both is the default option when you execute the ALTER SYSTEM statement.
If you do not use the server parameter file, you will get an error specifying Scope=spfile|both when you execute the ALTER SYSTEM statement.

ii.. Init.ora document (E:\oracle\product\10.2.0\db_1\srvm\admin)
The parameter description for this file:
1) db_name= "Clustdb" a database identifier that corresponds to the name specified in the CREATE DATABASE statement.
2) instance_name= CLUSTDB1 is used to uniquely identify a database routine in cases where multiple routines use the same service name, and instance_name should not be confused with SIDS, which is actually a unique identifier for each routine that shares memory on a single host.
3) Control_files= Control_files = ("\\.\clustdb_control1", "\\.\clustdb_control2")
4) Open_cursors=300
The library cache specifies the maximum number of cursors (environment zones) that can be opened at one time, and limits the size of the PL/SQL cursor cache used by PL/SQL to avoid re-parsing when the user executes the statement again. Set the value high enough to prevent the application from draining open cursors.
5) Db_block_buffers= 200
The number of Oracle blocks in the cache and I/O buffer cache. This parameter can significantly affect the total SGA size of a routine.
6) Large_pool_size=1048576
Pool-Specifies the allocation heap for a large storage pool, which can be used as session memory by multithreaded servers (MTS), as a message buffer for parallel execution, and as a disk I/O buffer for Rman backup and recovery.
7) Java_pool_size=67108864
Specifies, in bytes, the size of the Java storage pool, which is used to store Java methods and classes that define representations in shared memory, and Java objects that are ported to Java session space at the end of a call.
8) Log_checkpoint_interval= 10000
Specifies the number of OS blocks (not database blocks) that must be written to the redo log file before a checkpoint occurs. Regardless of the value, checkpoints occur when the log is toggled. A lower value can shorten the time required for routine recovery, but may result in excessive disk operation.
9)
Processes= 220
Log_buffer= 8388608
Specifies, in bytes, the amount of memory used to cache these entries before LGWR writes redo log entries to the Redo log files. Redo entries retain a record of changes made to the database block. If the value is greater than 65536, you can reduce the redo log file I/O, especially on systems with long transactions or large volumes of transactions * * The maximum value is 500K or 128K * cpu_count, whichever is larger
) oracle_trace_enable= True
Starts a default Oracle Trace collection until the value is set to NULL again.
One ) timed_statistics=true
Collects timing information for the operating system, which can be used to optimize databases and SQL statements. To prevent the overhead caused by requesting time from the operating system, set the value to zero. Setting this value to TRUE is also useful for viewing the progress of long-time operations.
background_dump_dest)=%oracle_home%/admin/clustdb/bdump
Specifies the path name (directory or disk) that is written to the trace file for the background process (LGWR,DBW n, and so on) during Oracle operations. It also defines the location of the database alert files that record important events and messages.
core_dump_dest)=/opt/apps/oracle/admin/51cto/cdump
Specifies the directory name for the core dump location (for UNIX).
user_dump_dest)=%oracle_home%/admin/clustdb/
Specifies the pathname for the directory where the server will write the debug trace file in a user process identity. For example, this directory can be set up: c:/on NT operating system
/ORACLE/UTRC on the Oracle/utrc;unix operating system, or disk$ur3:[oracle on the VMS operating system. UTRC].
db_block_size)= 8192
The size, in bytes, of an Oracle database block. This value is set when the database is created and cannot be changed thereafter. 1024-65536 (depends on operating system).
remote_login_passwordfile)= Exclusive
Specifies whether the operating system or a file checks the password of a user with permissions. If set to None,oracle, the password file is ignored. If set to exclusive, each user with permissions will be authenticated using the password file for the database. If set to shared, multiple databases will share the SYS and internal password file users
job_queue_processes)= 4
Used only for replication environments. It specifies the number of SNP job queue processes per routine (SNP0, ... SNP9, Snpa, ... SNPZ).   To automatically update a table snapshot or to execute a request created by Dbms_job, set the parameter to a value of 1 or greater. 0 to 36
) Job_queue_interval= 10
The job queue is used only for replication environments.  It specifies, in seconds, the wake-up frequency of each SNPN background process for the routine. 1 to 3600
distributed_transactions)= 5
The maximum number of distributed transactions that a database can participate in at a time. If this value is reduced due to an unusually frequent network failure, a large number of pending transactions are caused.
open_links)= 4
Specifies the maximum number of connections to the remote database that are open at the same time in a session. The value should be equal to or more than the number of databases referenced in a single SQL statement that references multiple databases, so that all databases can be opened to execute the statement.
) compatible= "9.0.0"
Allows the use of a new release with backward compatibility with previous versions.
sort_area_size)= 524288
Specifies, in bytes, the maximum amount of memory used by the sort. When the sort is complete, the rows are returned, and the memory is freed. Increasing this value can increase the efficiency of large sorting. If the amount of memory is exceeded, a temporary disk segment is used. Corresponds to the value (minimum) of 6 database blocks to the value determined by the operating system (maximum value).

iii.. alert.log file(E:\oracle\product\10.2.0/admin/fgisdb/bdump\alert_fgisdb.log)
Alarm logs, when an error is generated, when the instance is started and closed, the information is logged to the alarm log, and a list of initial parameters different from the default values is also recorded, alter SYSTEM,ALTER database command, table space, data file operation, insufficient space, corrupted files, etc. The alarm log also becomes large, can rename or delete the alarm log at any time, but the alarm log records the various security information of the database, maintenance and recovery information, so it can be selectively deleted according to time.

Oracle Parameters File