Parameter file for Oracle Learning (spfile, pfile)

The pfile (ParameterFile) file is a text-based parameter file that contains database configuration parameters. Oracle9i creates

The pfile (ParameterFile) file is a text-based parameter file that contains database configuration parameters. Oracle 9i creates

Pfile

The pfile (ParameterFile) file is a text-based parameter file that contains database configuration parameters. Oracle 9i creates a pfile for each database during installation. The default name is "init + routine name. ora", which is a text file and can be opened using any text editing tool.

Spfile

Spfile (Server Parameter File) is a Parameter File in binary format that contains database and Routine Parameters and values, but cannot be opened using a text editing tool.

Comparison of Two files

Comparison content

Spfile

Pfile

Format

Binary format

Text Format

Editing Method

(1) Use the Enterprise Manager to modify Pfile and convert it to Spfile (2) use the alter system statement in SQL Plus for Modification

(1) directly modify the configuration using a text tool (2) modify the configuration in the Enterprise Manager and then export

Default name

SPfile + routine name. ora

Init + routine name. ora actual parameter file Init. ora

Default path

Startup order

SPfile takes precedence over Pfile

Pfile is lower than Spfile

Description

Pfile is used for oracle8i and can also be used in oracle9i. It exists in text form and can be modified in vi editor. The file format is initSID. ora;

Spfile is dedicated to oracle9i and exists in the form of binary text. You cannot use the vi editor to modify the parameters. The file format is spfileSID. ora. If you want to modify the spfile file, you can use the SQL language:

SQL> ALTER SYSTEM SET PARAMETER = 'values. (Scope = memory, scope = spfile; scope = both ;)

Pfile and spfile convert each other

Create spfile

SQL> create spfile = 'd: \ oracle \ spfile. ora 'from pfile;

Create a pfile Backup Using spfile

SQL> create pfile = 'd: \ initbak. ora 'from spfile;

Check whether Oracle is started with spfile or pfile

Method 1: query the isspecified column in the v $ spparameter view. If the column is only false, pfile is enabled. If the column is true, spfile is enabled.

SQL> select distinctisspecified from v $ spparameter;

ISSPEC

------

FALSE

The example is started with pfile.

SQL> select distinctisspecified from v $ spparameter;

ISSPEC

------

TRUE

FALSE

Example: Start With spfile.

Method 2: If Show parameterspfile has a value, it indicates that spfile is used for startup. If no value is set, pfile is used for startup.

SQL> show parameter spfile;

NAME TYPE VALUE

-----------------------------------------------------------------------------

Spfile string

SQL>

The example is started with pfile.

SQL> show parameterspfile;

NAME TYPE VALUE

-----------------------------------------------------------------------------

Spfile string/u01/app/oracle/product/10.2.0

/Db_1/dbs/spfileorcl. ora

Example: Start With spfile.

Benefits of using spfile

Spfile corrected the confusion of pfile management. In a multi-node environment, pfile has multiple images that need to be tracked for the latest image at startup. This is a cumbersome process.

After spfile is used, all parameter changes are written to spfile (as long as scope = spfile or both is defined)

Pfile initialization parameter file Analysis

######################################## #############################

# Cacheand I/O high-speed cache and I/O

######################################## ###

Db_block_size = 8192 specify the data block size as 8 KB

Db_cache_size = 33554432 the specified data buffer is 32 MB. A larger value can reduce the number of I/O operations on database files and improve efficiency.

Db_file_multiblock_read_count = 16

######################################## ###

# Cursorsand Library Cache cursor and Library Cache

######################################## ###

Open_cursors = 300 specify the maximum number of cursors that a session can open at a time to 300. You should set this value high enough to prevent the application from exhausting open cursors.

######################################## ###

# Database Identification Database ID

######################################## ###

Db_domain = "" The database domain name is mynet, and the database name db_name is added to form the global database name.

Db_name = The Name Of The cicro database is myoracle.

######################################## ###

# Diagnostics and Statistics diagnosis and Statistics

######################################## ###

Background_dump_dest =/opt/oracle/admin/cicro/bdump background process tracking file directory

Core_dump_dest =/opt/oracle/admin/cicro/cdump core dump tracking file directory

Timed_statistics = TRUE collects the timing information of the operating system, which can be used to optimize databases and SQL statements.

User_dump_dest =/opt/oracle/admin/cicro/udump user process tracking file directory

######################################## ###

# FileConfiguration

Control_files = ("/opt/oracle/oradata/cicro/control01.ctl", "/opt/oracle/oradata/cicro/control02.ctl ", "/opt/oracle/oradata/cicro/control03.ctl") specifies the path and file name of the Control File

######################################## ###

# Instance Identification network registration

######################################## ###

Instance_name = test routine name: test

######################################## ###

# JobQueues

######################################## ###

Job_queue_processes = 10

######################################## ###

# MTS multi-threaded server configuration identifier, which is called shared server configuration in Oracle 9i

######################################## ###

Dispatchers = "(PROTOCOL = TCP) (SERVICE = testXDB)" multi-thread Server Configuration

######################################## ###

# Other Miscellaneous

######################################## ###

Aq_tm_processes = 1

Compatible = 9.2.0.0.0 compatible version 9.2.0

######################################## ###

# Optimizer

######################################## ###

Hash_join_enabled = TRUE

Query_rewrite_enabled = FALSE

Star_transformation_enabled = FALSE

Db_name = cicro

######################################## ###

# Pools pool

######################################## ###

Java_pool_size = 83886080 the size of the Java storage pool is 32 MB, which is used to store Java methods, class definitions, and Java objects.

Large_pool_size = 16777216 the size of a large pool is 1 MB, which is used to share the server's session memory, the message buffer for parallel execution, and the disk I/O buffer for RMAN backup and recovery.

Shared_pool_size = 83886080 the size of the Shared Pool is 32 MB. It is used to store shared cursors, stored procedures, control structures, parallel execution message buffers, and other objects. A large value can improve the performance of multi-user systems.

######################################## ###

# Processes and Sessions process and session

######################################## ###

Processes = 150 specifies that the maximum number of user processes that can be connected to an Oracle Server simultaneously is 150.

######################################## ###

# Redo Logand Recovery Redo log and restore

######################################## ###

Fast_start_mttr_target = 300 specifies the time required to recover from a single data warehouse routine crash to 300 seconds

######################################## ###

# Security and Auditing Security and verification

######################################## ###

Remote_login_passwordfile = EXCLUSIVE specifies whether the operating system or password file has the permission to check the user password. Set to EXCLUSIVE. Enter the Database Password File to each user with permissions.

######################################## ###

# Sort, Hash Joins, Bitmap Indexes sorting, Hash join, Bitmap Index

######################################## ###

Pga_aggregate_target = 25165824

Sort_area_size = 524288 the maximum memory usage in the specified sorting area is kb. After sorting is completed, the rows are returned and the memory is released. Increasing this value can improve the efficiency of large-scale sorting.

######################################## ###

# SystemManaged Undo and Rollback Segments managed by the System

######################################## ###

Undo_management = AUTO specifies that the Undo space management mode used by the system is SMU. In SMU mode, undo space is allocated externally like undo tablespace.

Undo_retention = 10800

Undo_tablespace = UNDOTBS1 specify the tablespace to be rolled back as UNDOTBS