Oracle parameter file, v$parameter dynamic view, OMF feature, startup

• PFILE and SPFILE

1, meaning:PFILE is the abbreviationof Parameterfile, SPFILE is the abbreviation of server parameter file, By the literal meaning of the full name, we can get pfile and SPFile are both Oracle supplied parameter files, which write the initial values of various parameters, such as Database Buffer The value is 196M , etc., the file suffix name is . Ora;

2, difference:spfile is oracle9i release2 introduced new things, before this is only pfile concept of Oracle. Pfile is a static parameter file that can be opened, edited, and saved by any text editor. SPFile is a binary file that can only be accessed by the ALTER SYSTEM SET ... The Scope=spfile statement is modified and will not take effect until the database instance is restarted after modification. Compared to Pfile,SPFile has the advantage of security;

3. starting mode: after oracle9i, if the DB instance is not explicitly specified to start the DB instance using Pfile, it will go to the default path to find the SPFile file. What you need to add here is that the default path for pfile and spfile is $oracle _home/database, on my database natively, my path is:E:\app\ Neusoft\product\11.2.0\dbhome_1\database;

4. default pfile and spfile file name: normal by default, the file name for pfile files should be init+ DB instance name +. ORA suffix name, such as the instance name of my native database is ORCL, the default pfile file name should be Initorcl.ora,and The default file name for SPFile spfile+ DB instance name +. The suffix name of ORA, for example my native spfile filename is spfileorcl.ora;

5, The creation method: If need to create pfile file, we can according to the native database SPFile to create, the creation syntax is:create Pfile from SPFile; You can also specify a build directory and a custom name for the Pfile file that will be generated, for example:CREATE pfile= ' F:\PFILE123. ORA ' from SPFILE; However, we cannot regenerate the default name of the spfile file in the SPFile default path that the current DB instance is using , i.e. we cannot use the following statement:CREATE spfile from PFILE; because this statement represents a SPFile file that generates the default name Spfileorcl.ora under the default path, the database path, and this file is already used by the current DB instance.

6. Create permission requirements: Create a pfile or spfile file requires the user to have sysdba permissions, the default sys user has sysdba permissions, if you use other users (such as system), first give the system user sysdba permissions, the authorization statement is:grantsysdba to System;

7. Check whether the current DB instance is started with spfile :sqlplus window executes showspfileif spfile has a value ( spfile Path), the current DB instance is started by spfile mode;

• V$parameter Dynamic View

1, We can view the value of the specified parameters, modify the v$parameter dynamic view;

2. query statement:

Select name, value, IsModified, issys_modifiable from V$parameter

Where name like ' db_create_file_dest% ';

3, about the field ismodified Description:

The field meaning is whether the parameter is modified after the instance is started, with a value of 3 :modified\system_mod\false

MODIFIED indicates that the instance has been modified with alter session after it has been started;

System_mod indicates that the instance was modified with alter system after it was started ;

FALSE indicates that the instance has never been modified since it was started;

4, about the field issys_modifiable Description:

The field meaning is the time at which the parameter can be modified by Altersystem and has an effect after modification, with a value of 3 :

IMMEDIATE: Indicates that it can be modified with alter system and takes effect immediately after modification;

DEFERRED: Indicates that the alter system can be used to modify and take effect in the next session;

FALSE: Except for parameters that have an impact on the startup instance, no modification is allowed and the instance needs to be restarted;

Select name as parameter name,

value as current parameter values,

Decode (ismodified,

' MODIFIED ',

' modified with altersession ',

' system_mod ',

               ' modified with Altersystem"

' unmodified ') as instance after the boot modification mode,

Decode (issys_modifiable,

' IMMEDIATE ',

' current session takes effect immediately ',

' DEFERRED ',

' delay the next session in effect ',

' restart instance in effect ') as effective time

from V$parameter

• OMF Technology

1, OMF is the abbreviation of Oracle MANAGED files, translated by Oracle management files, is a new concept from the Oracle 10g, Simplified daily operations Command writing after specifying the default path information;

2, Check whether to enable OMF, you can enter in the sqlplus window interface:showparameter db_create_file_dest, If the parameter has a value (path information), the proxy is enabled for OMF management;

3, You can use Altersystem or alter session to enable the Db_create_file_dest parameter, The path to the parameter should default to the directory where the database file for the DB instance resides, as in the following example:

ALTER SYSTEM SET db_create_file_dest= ' E:\APP\NEUSOFT\ORADATA\ORCL ';

4, according to the above 3 steps to complete the operation, you can enable the OMF technical characteristics.

5, below we give an example to illustrate, if we do not open the OMF technical features, such as we need to create a table space test, we should at least write the statement:Create Tablespace test datafile ' E:\app\neusoft\oradata\orcl\TEST. DBF '; statement contains a storage path for the data table space, and if not specified,Oracle will prompt for errors and create failures. If we specify the default path of db_create_file_dest, which means that the OMF technology feature is turned on, we can save the path information, size, token space created in the future when we create the tablespace, log file, control file. The newly created tablespace is generated under the default path. However, they are used as appropriate in the actual production environment.

6, the Windows operating system Normally,the Oracle data files, log files, control files belong to the path should be in the oradata directory, for example, my native database directory is: E:\APP\NEUSOFT\ORADATA\ORCL

If we set the value of the parameter db_create_file_dest to E:\app\neusoft\oradata

We can achieve this:

Alter system setdb_create_file_dest= ' E:\app\neusoft\oradata ';

and then we're going to create a tablespace test01that we can write directly

Create tablespace test01;

then we'll find that Oracle automatically creates a new directory of DataFile in the E:\APP\NEUSOFT\ORADATA\ORCL directory with the table space we just created , the size of the tablespace defaults to 100M, and the name defaults to:o1_mf_Dushuai_bbq2gwsk_. DBF, which contains the name of the table space we specify;

We can view the property information for our newly created tablespace through the built-in function package, as follows:

SELECTDBMS_METADATA.GET_DDL (' tablespace ', ' Dushuai ') from dual;

get the big text CLOB results are as follows:

CREATE tablespace "Dushuai" datafile

SIZE 104857600

Autoextend on NEXT 104857600MAXSIZE 32767M

LOGGING ONLINE permanentblocksize 8192

EXTENT MANAGEMENT LOCAL autoallocate defaultnocompress SEGMENT SPACE MANAGEMENT AUTO

• DB Instance Start hierarchy

1, nomount: Only know the basic information such as the address of the files that are required to launch the various instances;

2, mount: mounted instance started, but no consistency check and other operations;

3, Open: The default database instance starts startup default Project, indicates the normal startup database instance;

Attachment: The order in which databases are started after an abnormal shutdown
1. roll forward all REDO that are not written to the data file
2. Open the database
3. Roll back all uncommitted data

This article is from the "Oralce Learning path" blog, make sure to keep this source http://dushuai.blog.51cto.com/9461011/1599738

Oracle parameter file, v$parameter dynamic view, OMF feature, startup