Oracle parameter file

Oracle Parameters

I. Differences between Oracle pfile and spfile
PfileThe default name is "init + routine name. ora "file path: e: \ oracle \ product \ 10.2.0 \ db_1 \ DBs. This is a text file and can be opened using any text editing tool.
SpfileThe default name is "spfile + routine name. ora "file path: e: \ oracle \ product \ 10.2.0 \ db_1 \ DBS exists in the form of binary text. You cannot use the VI editor to modify the parameters.
Two files can be created with the command create pfile from spfile or create spfile from pfile.

Differences:
1. the startup sequence spfile takes precedence over pfile.
2. pfile is a static file and will not take effect immediately after modification. The database must restart to read the file.
3. spfile is a dynamic parameter file and a binary file. You cannot directly use notepad or other programs to modify the file. You can use the alter command to modify the file without restarting the database.
Modification method:
# Viewing parameter information
SQL> show parameter;
# Modifying parameters
SQL> alter system set parameter name = value scope = parameter 2;
Parameter 2 has the following three values:
1. Scope = spfile: Modify the parameters in the server initialization parameter file. The modified parameters will take effect the next time you start the database. Applies to dynamic and static initialization parameters.
2. Scope = memory: the modification record of the parameter is stored in the memory, and the modification of the dynamic initialization parameter takes effect immediately. After the database is restarted, the loss is restored to the parameter value before modification.
3. Scope = both: Modifications to parameters are recorded in both the server parameter file and the internal storage. Dynamic Parameters take effect immediately and cannot be used for static parameters.
If the server parameter file is used, scope = both is the default option when the alter System statement is executed.
If the server parameter file is not used, an error occurs when scope = spfile | both is specified when the alter System statement is executed.

Ii. init. ora file (E: \ oracle \ product \ 10.2.0 \ db_1 \ srvm \ ADMIN)
Parameter description of this file:
1) db_name= "Clustdb" is a database identifier that corresponds to the name specified in the create database statement.
2) instance_name= Clustdb1 uniquely identifies a data warehouse routine when multiple routines use the same service name; instance_name should not be confused with Sid, it is actually the unique identifier of each routine that shares memory on a host.
3) control_files= Control_files = ("\. \ clustdb_control1", "\. \ clustdb_control2 ")
4) open_cursors= 300
Database cache specifies the maximum number of cursors (Environment regions) that can be opened at one session, and limits the cache size of PL/SQL cursors used by PL/SQL, to avoid re-syntax analysis when you execute the statement again. Set this value to high enough to prevent the application from exhausting open cursors.
5) db_block_buffers= 200
The number of Oracle blocks in the cache and I/O buffer. This parameter will significantly affect the total SGA size of a routine.
6) large_pool_size= 1048576
Pool -- specifies the Allocation Heap of a large storage pool, which can be used by multi-thread servers (MTS) used as the session memory, as the message buffer for parallel execution, and as the disk I/O buffer for RMAN backup and recovery.
7) java_pool_size= 67108864
The size of the Java storage pool is specified in bytes. It is used to store Java methods and class definitions in the shared memory representation, and the Java object transplanted to the Java session space at the end of the call.
8) log_checkpoint_interval= 10000
Specify the number of OS blocks (not database blocks) in the redo log file that must be written before a checkpoint occurs. No matter what the value is, the checkpoint will appear during log switching. A low value can shorten the time required for routine recovery, but may lead to excessive disk operations.
9)
Processes= 220
Log_buffer= 8388608
In bytes, specify the amount of memory used to cache the redo log entries before lgwr writes them to the redo log file. Redo entries record changes made to database blocks. If the value is greater than 65536, the I/O of the redo log file can be reduced, especially for systems with long transaction processing or massive transaction processing, ** the maximum value is 500 K or 128 K * cpu_count.
10) oracle_trace_enable= True
Start a default Oracle trace set until this value is set to null again.
11) timed_statistics= True
Collects operating system timing information, which can be used to optimize databases and SQL statements. To avoid overhead caused by request time from the operating system, set this value to zero. Setting this value to true is also useful for viewing the progress of a long operation.
12) background_dump_dest= % ORACLE_HOME %/admin/clustdb/bdump
Specify the path name (directory or disk) for the background process (lgwr, dbw N, and so on) to write the trace file during Oracle operations ). It also defines the location of the database warning file that records important events and messages.
13) core_dump_dest=/Opt/apps/Oracle/admin/51cto/cdump
Specifies the Directory Name of the core dump location (for UNIX ).
14) user_dump_dest= % ORACLE_HOME %/admin/clustdb/
Specify the path name for the directory where the server writes the debugging trace file as a user process. For example, the directory can be set as follows: C:/on the NT Operating System :/
Oracle/utrc;/Oracle/utrc on UNIX; or disk $ ur3: [Oracle. utrc] on VMS.
15) db_block_size= 8192
The size of an Oracle database block (in bytes ). This value is set when you create a database and cannot be changed later. 1024-65536 (depending on the operating system ).
16) remote_login_passwordfile= Exclusive
Specifies whether the operating system or a file checks the password of a user with permissions. If it is set to none, Oracle ignores the password file. If it is set to exclusive, the database password file will be used to verify each user with permissions. If it is set to shared, multiple databases will share the sys and internal Password File users.
17) job_queue_processes= 4
It is only used to copy the environment. It specifies the number of SNP Job Queue processes for each routine (snp0,... snp9, SNPA,... snpz ). To automatically update a table snapshot or execute a request created by dbms_job, set this parameter to 1 or a greater value. 0 to 36
18) job_queue_interval= 10
Job Queue is only used to copy the environment. It specifies the wake-up frequency of each snpn background process of the routine in seconds. 1 to 3600
19) 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 frequent network failures, a large number of pending transactions will be processed.
20) open_links= 4
Specifies the maximum number of connections that can be opened to the remote database at the same time in a session. This value should be equal to or greater than the number of databases referenced in a single SQL statement that references multiple databases. In this way, all databases can be opened to execute this statement.
21) compatible= "9.0.0"
A new release is allowed, and the backward compatibility with the previous version is guaranteed.
22) sort_area_size= 524288
In bytes, specify the maximum memory used for sorting. After sorting is completed, the rows are returned and the memory is released. Increasing this value can improve the efficiency of large-scale sorting. If the memory size exceeds this limit, a temporary disk segment is used. It is equivalent to the value (minimum value) of 6 database blocks to the value (maximum value) determined by the operating system ).

Iii. Alert. Log File(E: \ oracle \ product \ 10.2.0/admin/fgisdb/bdump \ alert_fgisdb.log)
The alarm log records the information in the alarm log when an error occurs and when the instance is started or shut down. In addition, the alarm log records the list of initial parameters different from the default values, alter DATABASE Command, operations on table space, data files, insufficient space, and corrupted files. Alarm logs also become large and can be renamed or deleted at any time. However, alarm logs record various security information, maintenance and recovery information of the database, therefore, you can choose to delete Objects Based on Time Sequence.