Pfile和Spfile配置解释(转载)

摘抄自这里，仅用来记录，以便查阅。

Certain files in the database can completely change the way your Oracle 12c database behaves. They can influence everything from performance and tuning as well as troubleshooting. Maintaining and configuring these files are a major component of database administration.

PFILE AND SPFILES IN ORACLE 12C

These are the files that set up your database operating environment:

• PFILE
• SPFILES

Let’s go through many of the common parameters you find in these files. The parameter file is the first file read when you start your database; the parameters within it configure how your database operates.

First, take a look at an example of a PFILE and some of the commonly set parameters:

*.audit_file_dest='/u01/app/oracle/admin/dev12c/adump'
*.audit_trail='db'
*.compatible='12.0.0.0.0'
*.control_files='/u01/app/oracle/oradata/dev12c/control01.ctl',
         '/u02/app/oracle/oradata/dev12c/control02.ctl',
         '/u03/app/oracle/oradata/dev12c/control03.ctl'
*.db_block_size=8192
*.db_domain=’lfg.com’
*.db_name='dev12c'
*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'
*.db_recovery_file_dest_size=4815m
*.diagnostic_dest='/u01/app/oracle'
*.memory_target=1280m
*.open_cursors=300
*.processes=300
*.undo_tablespace='UNDOTBS1'

The parameters have a * in front of them because you can use the parameter file to set parameters in more than one Oracle instance.

Follow these steps to see the parameters that are modified in an existing Oracle database:

1. Log in to SQL*Plus as a SYSDBA.

2. Type create pfile from spfile; (including the semicolon).

   The command dumps a text version of your SPFILE.

After you create your PFILE, you want to turn it into an SPFILE. Essentially, you do the reverse of what you did before:

1. Log in to SQL*Plus as a SYSDBA.

2. Type create spfile from pfile; (including the semicolon).

   You get a file called spfileORACLE_SID.ora in the same directory as your PFILE, where ORACLE_SID is your instance_name.

HOW TO SET PARAMETERS IN THE PFILES AND SPFILES IN ORACLE 12C

Whether you use PFILES or SPFILES determines how you set your parameters.

With a new database, you always start with a PFILE. If you end up wanting to use an SPFILE, you create it from the PFILE.

The first thing you need to do is find your PFILE. For whatever reason, despite all the other similarities, Linux/UNIX and Windows store it in different locations.

Find your PFILE on Windows, where ORACLE_SID is your instance name:

ORACLE_HOMEdatabaseinitORACLE_SID.ora

Find your PFILE on Linux/UNIX, where ORACLE_SID is your instance name:

ORACLE_HOME/dbs/initORACLE_SID.ora

These parameters are some of the most commonly customized. Most parameters suit most databases at their default value.

The * means to apply the parameter to all instances that read this file.

• audit_file_dest: This parameter tells Oracle where to put auditing information on the file system. All connections to the database as SYSDBA are audited and put into this directory.
• audit_trail: This tells Oracle where you want audit records written. Audit records are written to the database or the file system. They can be in text format or XML. Records written to the database are stored in the AUD$ system table. The valid values for this parameter follow:
  • db: Normal audit records written to the AUD$ table
  • os: Normal audit records written to the audit_file_dest directory
  • db_extended: Audit records written to the AUD$ table in extended format, including SQLTEXT and bind variable values
  • xml: XML-formatted normal audit records written to the database
  • xml, extended: Normal auditing and includes all columns of the audit trail, including SqlText and SqlBind values in XML format to the database
• compatible: Set it to force the database to behave like a version earlier than Oracle 12c. In Oracle 12c, you can set it back as far as 10.0.0. However, it can be set back only before the database is created or before upgrading from an earlier version. After you migrate this parameter to 12.1.0 and open the database, you can no longer go back.
• The parameter is useful for testing before an upgrade is complete. Most of the time you find it set on the latest version for your software. If you try using a feature from a database version later than what you’ve configured, it results in an Oracle error.
• control_files: Just what is says. It tells the instance where to look for the control files during the startup phase. If the instance doesn’t find even one of them, you can’t mount your database.
• db_name: The database name. Choose this name carefully. Although you can change it, doing so is a pain. The name can be up to eight alphanumeric characters. Avoid the urge to use special characters other than #, $, and _.
• db_recovery_file_dest: This sets what’s known as the Fast Recovery Area. The area can hold files such as
  • Backups
  • Archive log files
  • Control files
  • Redo log files
• db_recovery_file_dest_size: This determines how much space is dedicated to your Flash Recovery Area. If it fills up, you get an error message and the database could come to a halt — especially if you’re storing archive log files here. If archive log files can’t be written, redo log files can’t be overwritten. User sessions hang until the situation is resolved.
• diagnostic_dest: This location is known as the Automatic Diagnostic Repository (ADR) home. It contains files that Oracle support may use to resolve issues with your database. This parameter was new in 11g. You can use a new tool called ADRCI to access the files in this directory. It contains
  • Trace files
  • Core files
  • Alert logs
  • Incident files
• memory_target: This parameter sets the memory that the Oracle instance is allowed to use for all System Global Area (SGA) and program global area activities. It doesn’t include memory consumed by server and user processes.
• open_cursors: Limits the number of open SQL cursors a session can have.
• processes: Limits the number of OS users’ processes that can connect to the instance.
• undo_tablespace: This parameter tells the instance to which tables it will write its transaction undo. It must be an undo type tablespace.