Definition, function, and instance of spfile and pfile in oracle, spfilepfile

Definition, function, and instance of spfile and pfile in oracle, spfilepfile

1. Definitions and functions of parameter files

The oracle Database configures the database through a series of parameters. These parameters are displayed in the form of key-value pairs, such:

MAXLOGFILES = 50

BACKGROUND_DUMP_DEST = C:/DUMP

Here, the parameter name is on the left of the equal sign, and the value corresponding to the parameter value on the right. There are many types of values, such as numbers and strings.

Parameter files are the places where these parameters are stored. oracle reads related configurations from the parameter files at startup.

2. Classification of parameter files

Before 9i, there was only one parameter file. It was in text format and called pfile. In 9i and later versions, a new server parameter file, called spfile, was added, it is in binary format. Both parameter files are used to store parameter configurations for oracle reading, but there are also differences. Note the following:

First, pfile is a text file, and spfile is a binary file;

Second, for parameter configuration, pfile can be opened manually in a text editor, but spfile cannot. It must be modified online through SQL commands after the database is started.

Third, to use pfile to take effect after the configuration change, you must restart the database. The time limit and scope of the spfile configuration can be specified by the SQL command that modifies the parameter and can take effect immediately, or it does not take effect immediately. Of course, some parameter modifications must be restarted before they take effect;

Fourth, you can use SQL commands to create spfile by pfile or pfile by spfile;

Fifth, If you create a database manually instead of using DBCA, you can only define pfile when creating a database. Because it is in text format;

Sixth, the oracle database only uses one parameter file, either pfile or spfile, that is, how to determine which parameter file is currently used by the database? One way is to use create pfile for identification. If the previous use is not spfile, the corresponding format of create pfile will produce an error. The show parameter spfile command is used to display the position of the spfile. If the displayed value is null, pfile is used.

3. parameter file Action Principle

When an oracle instance is started, it reads the configuration in the parameter file. This process is as follows:

You can specify which pfile to start in the database's startup command. However, note that only pfile can be specified, but spfile cannot be specified.

When the startup command without the pfile clause is used, Oracle reads the initialization parameters from the server parameter file (spfile) in the default location specified by the platform. Search for spfile or chuangtong init in Oracle. the order of ora is: in the default location specified by the platform, Oracle first looks for the name spfileORACLESID. ora file. If not, search for spfile. find init "role =" presentation "> ORACLESID. ora file. If not, search for spfile. ora file. If not, find initORACLESID. ora file. If not, search for spfile. find the initORACLE_SID.ora file.

In ORACLEBASE/admin/dbname/spfile, you may see a file named init. ora.192003215317] like this. This is the initialization parameter file, but it only keeps up with the timestamp. For Oracle920, spfile is started by default, but this spfile is not created out of thin air, but created based on this file. You can remove this long suffix, that is, the standard pfile file. For WindowsNT and Windows2000, the location is: "role =" presentation "> In ORACLEBASE/admin/dbname/spfile, you may see an init like this. ora.192003215317] Name of the file, which is the initialization parameter file, but only keeps up with the timestamp. For Oracle920, spfile is started by default, but this spfile is not created out of thin air, but created based on this file. You can remove this long suffix, that is, the standard pfile file. For WindowsNT and Windows2000, the location is: ORACLEBASE/admin/dbname/spfile. You may see an init like this. ora.192003215317] Name of the file, which is the initialization parameter file, but only keeps up with the timestamp. For Oracle920, spfile is started by default, but this spfile is not created out of thin air, but created based on this file. You can remove this long suffix, that is, the standard pfile file. For WindowsNT and Windows2000, the location is ORACLE_HOME/database/spfileORACLESID. ora. After the database is started, the parameter configuration value can be queried through the data dictionary v "role =" presentation "> ORACLESID. ora. After the database is started, the parameter configuration value can be queried through the data dictionary vORACLESID. ora. After the database is started, the parameter configuration value can be obtained by querying the data dictionary vparameter.

4. How to modify the parameter file

Manual modification and online modification.

Manual modification is used to modify pfile. You can directly use text editing to open pfile modification. To use the modification to take effect, you must restart the database.

Online modification is performed using the alter system command when the database is running. The command is as follows (for detailed command statements, see the oracle official reference documentation ):

SQL> alter system set job_queue_processed = 50 scope = MEMORY

Note: scope = MEMORY indicates the application range. The values are as follows:

SPFILE: the modification is only valid for SPFILE and does not affect the current instance. You need to restart the database to make the modification take effect;

MEMORY: the modification is only effective for the MEMORY, that is, it is only valid for the current instance and takes effect immediately, but it is not saved to SPFILE. This configuration is lost after the database is restarted;

BOTH: as the name suggests, it includes the above two types. It takes effect immediately and permanently.

Note the following points for the alter system parameter modification command:

First, if the current instance uses pfile instead of spfile, scope = spfile or scope = both will produce an error;

Second, if the instance is started with pfile, the default value of scope is MEMORY. If the instance is started with spfile, the default value is BOTH;

Third, you can use DEFERRED to indicate that the changes are only applicable to future sessions. You can also use COMMENT to write the note, for example, alter system set JOB_QUEUE_PROCESSES = 50 SCOPE = both deferred comment = "COMMENT"

Fourth, the method for deleting a PARAMETER is as follows: alter system set parameter = ";

5. Create a parameter file

For pfile, you can use a text editor to directly edit one or use the create pfile command to CREATE one from spfile, for example, create PFILE = 'C:/PFILE/MYPFILE. ORA 'from SPFILE = 'd:/SPFILE/MYSPFILE. ORA ', or create from the spfile used by the current instance: create pfile = 'C:/pfile/mypfile. ora 'from spfile.

The command for creating a spfile is as follows: create spfile from pfile = 'C:/PFILE/mypfile '.

The above introduction refers to others. The following example is original:

Example:

My environment is oracle11g

For example, insert a table:

Create table t7

Select rownum as id, (rownum + 1) id2 from dual connect by rownum <5000000;

Check the current sga memory. The memory is insufficient.

Show parameter sga

Check whether the current instance is started with pfile or spfile.

Show parameter spfile

Spfile is found;

So you can fix it directly, right? Because the default value is both.

First modify sga_max_szie

Alter system set sga_max_size = 4000 M;

It is not surprising that an error is reported because, although spfile is used, the database must be restarted to modify some parameters. Therefore, you can only use:

Alter system set sga_max_size = 4000 M scope = spfile;

It takes effect after being restarted.

SQL> shutdown immediate;

The database has been closed.

The database has been detached.

The ORACLE routine has been disabled.

SQL> startup;

ORA-00844: Parameter not taking MEMORY_TARGET into account

ORA-00851: SGA_MAX_SIZE 4194304000 cannot be set to more than MEMORY_TARGET 3405774848.

Again, an error is reported. The MEMORY_TARGET parameter is added for 11 GB, and its size is equal to PGA + SGA. When the size of sga is greater than MEMORY_TARGET, the above error is reported.

When sga_max_size appeared, it was also designed to automatically manage the library cache size, java pool, and dic size areas in sga, memory_max_size is used to automatically manage the two memory areas of sga and pga.

The memory adjustment sequence at oracle11g should be:

Tmpfs> MEMORY_MAX_TARGET> MEMORY_TARGET> sga_max_size> sga_target

Alas, hurry up and modify:

SQL> conn sys/orcl as xxx

Already connected to the idle routine.

SQL> create pfile = 'f: \ oracle11g \ ysy \ product \ 11.2.0 \ dbhome_1 \ dbs \ init. ora 'from spfile;

The file has been created.

Modify the MEMORY_TARGET parameter of init. ora.

Then create a spfile.

SQL> create spfile from pfile = 'f: \ oracle11g \ ysy \ product \ 11.2.0 \ dbhome_1 \ dbs \ init. ora ';

The file has been created.

SQL> startup

The ORACLE routine has been started.

Total System Global Area 3390558208 bytes

Fixed Size 2180464 bytes

Variable Size 956304016 bytes

Database Buffers 2415919104 bytes

Redo Buffers 16154624 bytes

The database has been loaded.

The database has been opened.

SQL>

Re-Modify:

SQL> alter system set MEMORY_MAX_TARGET = 6000 M scope = spfile;

The system has been changed.

SQL> alter system set MEMORY_TARGET = 5500 M scope = spfile;

The system has been changed.

SQL> alter system set sga_max_size = 4500 M scope = spfile;

Restart

Shutdown immediate;

The database has been closed.

The database has been detached.

The ORACLE routine has been disabled.

SQL> startup

The ORACLE routine has been started.

Total System Global Area 4710043648 bytes

Fixed Size 2183632 bytes

Variable Size 2281705008 bytes

Database Buffers 2399141888 bytes

Redo Buffers 27013120 bytes

The database has been loaded.

The database has been opened.

SQL> show parameter sga

NAME TYPE VALUE

Lock_sga boolean FALSE

Pre_page_sga boolean FALSE

Sga_max_size big integer 4512 M

Sga_target big integer 3008 M

We can see that sga_max_size has been modified.

Finally, you can create the table.

Appendix: pfile Content Used for restoration (after the first modification)

Orcl. _ db_cache_size = 2415919104

Orcl. _ Java _pool_size = 16777216

Orcl. _ large_pool_size = 16777216

Orcl. _ oracle_base = 'f: \ oracle11g \ ysy '# ORACLE_BASE set from environment

Orcl. _ pga_aggregate_target = 251658240

Orcl. _ sga_target = 2013265920

Orcl. _ shared_io_pool_size = 0

Orcl. _ shared_pool_size = 671088640

Orcl. _ streams_pool_size = 0

*. Audit_file_dest = 'f: \ oracle11g \ ysy \ admin \ orcl \ adump'

*. Audit_trail = 'db'

*. Compatible = '11. 2.0.0.0'

*. Control_files = 'f: \ oracle11g \ ysy \ oradata \ orcl \ control01.ctl ', 'f: \ oracle11g \ ysy \ flash_recovery_area \ orcl \ control02.ctl'

*. Db_block_size = 8192

*. Db_domain ="

*. Db_name = 'orcl'

*. Db_recovery_file_dest = 'f: \ oracle11g \ ysy \ flash_recovery_area'

*. Db_recovery_file_dest_size = 4102029312

*. Diagnostic_dest = 'f: \ oracle11g \ ysy'

*. Dispatchers = '(PROTOCOL = TCP) (SERVICE = orclXDB )'

*. Local_listener = 'listener _ ORCL'

*. Log_buffer = 15000000

*. Memory_target = 3390046208

*. Open_cursors = 300

*. Processses = 150

*. Remote_login_passwordfile = 'clusive'

*. Sga_max_size = 3390046207

*. Sga_target = 3154116608

*. Undo_tablespace = 'undotbs1'