​實(shí)例管理及數(shù)據(jù)庫(kù)的啟動(dòng)/關(guān)閉

實(shí)例管理及數(shù)據(jù)庫(kù)的啟動(dòng)/關(guān)閉

2.1 實(shí)例和參數(shù)文件

1、instance 功能:用于管理和訪問(wèn)database。

instance在啟動(dòng)階段讀取初始化參數(shù)文件（init parameter files)。

2、init parameter files ：管理實(shí)例相關(guān)啟動(dòng)參數(shù) 。位置：$ORACLE_HOME/dbs

3、pfile ：（parameter file）靜態(tài)參數(shù)文件。

1）文本文件，必須通過(guò)編輯器修改參數(shù)。

2）修改參數(shù)下次重啟實(shí)例才生效。

3）pfile參數(shù)文件可以不在database server上。

命名方式：init+SID.ora

4、spfile ：（system parameter file) 動(dòng)態(tài)參數(shù)文件。

1）二進(jìn)制文件，不可以通過(guò)編輯器修改。

2 )  Linux下strings可以查看 。

3） 必須在database server段的指定路徑下。

命名方式: spfile+SID.ora

靜態(tài)參數(shù)和動(dòng)態(tài)參數(shù)

在spfile讀到內(nèi)存后，有一部分參數(shù)是可以直接在內(nèi)存中修改，并對(duì)當(dāng)前instance立即生效，這樣的參數(shù)叫動(dòng)態(tài)參數(shù)。除了動(dòng)態(tài)參數(shù)都是靜態(tài)參數(shù)。靜態(tài)參數(shù)修改spfile文件。動(dòng)態(tài)參數(shù)在instance關(guān)閉后失效，而靜態(tài)參數(shù)是下次instance啟動(dòng)后才生效。

修改spfile文件的方法：

alter system set 參數(shù)=值 [scope=memory|spfile|both]

alter system reset 參數(shù) [scope=memory|spfile|both] SID='*'         //恢復(fù)缺省值。

第一種scope=memory 參數(shù)修改立刻生效，但不修改spfile文件。

第二種scope=spfile 修改了spfile文件，重啟后生效。

第三種scope=both 前兩種都要滿足。

如果不寫(xiě)scope，即缺省，那就是第三種。

*注意：如果不修改spfile，將無(wú)法更改靜態(tài)參數(shù)。

通過(guò)查看v$parameter ，可以確定哪些參數(shù)可以在memory修改，制定scope。

10:38:35 SQL> desc v$parameter;

其中：

ISSYS_MODIFIABLE參數(shù)：對(duì)應(yīng)alter system 命令，即系統(tǒng)級(jí)修改

10:38:35 SQL> select distinct issys_modifiable from v$parameter;

ISSYS_MODIFIABLE

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

IMMEDIATE                     //對(duì)應(yīng)scope=memory

FALSE                              //只能scope=spfile,即修改spfile文件，下次啟動(dòng)才生效。

DEFERRED                       //其他session有效

ISSES_MODIFIABLE參數(shù)：對(duì)應(yīng)alter session命令，即session級(jí)修改

10:38:35 SQL> select distinct isses_modifiable from v$parameter;

ISSES_MODIFIABLE

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

TRUE                               //表示可以修改

FALSE                              //表示不能修改

SQL> select name,ISSES_MODIFIABLE,ISSYS_MODIFIABLE from v$parameter where name='sql_trace';

SQL> select name,ISSES_MODIFIABLE,ISSYS_MODIFIABLE from v$parameter where name='sql_trace';

NAME                                        ISSES_MODIFIABLE                         ISSYS_MODIFIABLE

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

sql_trace                                       TRUE                                                IMMEDIATE

這個(gè)結(jié)果表示 sql_trace參數(shù)在session級(jí)別可以改，在system級(jí)也可以both修改（動(dòng)態(tài)參數(shù)）。

如何判斷參數(shù)是動(dòng)態(tài)參數(shù)還是靜態(tài)參數(shù)？

第一種方法：查看動(dòng)態(tài)性能視圖v$parameter

ISSES_MODIFIABLE （session級(jí)別）    

TRUE                                 //動(dòng)態(tài)參數(shù)

FALSE                                //靜態(tài)參數(shù)

ISSYS_MODIFIABLE  （數(shù)據(jù)庫(kù)級(jí)）

IMMEDIATE                     //動(dòng)態(tài)參數(shù) ，對(duì)應(yīng)scope=memory，

FALSE                              //靜態(tài)參數(shù) ，只能scope=spfile,即修改spfile文件，下次啟動(dòng)才生效。

DEFERRED                       //其他session有效

第二種方法：試探法

舉例1：log_buffer

1）alter system set log_buffer=3145728 scope=both;（alter system set log_buffer=3145728 ;）

SQL> alter system set log_buffer=3145980 scope=both;

alter system set log_buffer=3145980 scope=both

                 *

第 1 行出現(xiàn)錯(cuò)誤:

ORA-02095: 無(wú)法修改指定的初始化參數(shù)

說(shuō)明上述為靜態(tài)參數(shù)，需要寫(xiě)入到spfile中：

SQL> alter system set log_buffer=5242880 scope=spfile;

系統(tǒng)已更改。

SQL> show parameter log_buffer;

NAME                                 TYPE        VALUE

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

log_buffer                           integer     3145728

SQL>

SQL> shutdown immediate

數(shù)據(jù)庫(kù)已經(jīng)關(guān)閉。

已經(jīng)卸載數(shù)據(jù)庫(kù)。

ORACLE 例程已經(jīng)關(guān)閉。

SQL> startup

ORACLE 例程已經(jīng)啟動(dòng)。

Total System Global Area  849530880 bytes

Fixed Size                  1339824 bytes

Variable Size             595594832 bytes

Database Buffers          243269632 bytes

Redo Buffers                9326592 bytes

數(shù)據(jù)庫(kù)裝載完畢。

數(shù)據(jù)庫(kù)已經(jīng)打開(kāi)。

SQL> show parameter log_buffer;

NAME                                 TYPE        VALUE

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

log_buffer                           integer     5242880

舉例2：（動(dòng)態(tài)參數(shù)）

暫略

5、startup時(shí)讀取參數(shù)文件，找到$ORACLE_HOME/dbs目錄，順序是優(yōu)先spfile啟動(dòng)，沒(méi)有spfile 才從pfile啟動(dòng)。

pfile和spfile可以相互生成：

SQL>create pfile from spfile

SQL>create spfile from pfile（使用spfile啟動(dòng)后不能在線生成spfile，ORA-32002: 無(wú)法創(chuàng)建已由實(shí)例使用的 SPFILE）

*注意：

1）如果使用pfile啟動(dòng),設(shè)置scope=spfile將失??！但可以設(shè)置scope=memory。

可以通過(guò)當(dāng)前內(nèi)存參數(shù)生成pfile和spfile（11g新特性）：

SQL>create pfile from memory;

SQL>create spfile from memory;

有了spfile，pfile一般留做備用，特殊情況也可以使用pfile啟動(dòng),命令如下：

10:38:35 SQL> startup pfile=$ORACLE_HOME/dbs/inittimran.ora

怎樣知道實(shí)例是spfile啟動(dòng)還是pfile啟動(dòng)的？

10:38:35 SQL> show parameter spfile                                                                                                 

NAME                                 TYPE        VALUE

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

spfile                               string      /u01/oracle/dbs/spfiletimran11g.ora

//如果value有值，說(shuō)明數(shù)據(jù)庫(kù)啟動(dòng)時(shí)讀的是spfile

另一個(gè)辦法是看v$spparameter（spfile參數(shù)視圖）中的參數(shù)memory_target的isspecified字段值，如果是TRUE 說(shuō)明是spfile啟動(dòng)的（考點(diǎn)）

10:42:35 SQL> select name,value,isspecified from v$spparameter where name like 'memory_target';

NAME                                VALUE                                        ISSPECIFIED

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

memory_target                       423624704                                    TRUE

EM對(duì)初始參數(shù)有較好的可視化界面，可以看看

oracle官方文檔參數(shù)文件介紹：pfile,spfile

About Initialization Parameters and Initialization Parameter Files

When an Oracle instance starts, it reads initialization parameters from an initialization parameter file. This file must at a minimum specify the DB_NAMEparameter. All other parameters have default values.

The initialization parameter file can be either a read-only text file, a PFILE, or a read/write binary file.

The binary file is called a server parameter file. A server parameter file enables you to change initialization parameters with ALTER SYSTEM commands and to persist the changes across a shutdown and startup. It also provides a basis for self-tuning by Oracle Database. For these reasons, it is recommended that you use a server parameter file. You can create one manually from your edited text initialization file, or automatically by using Database Configuration Assistant (DBCA) to create your database.

Before you manually create a server parameter file, you can start an instance with a text initialization parameter file. Upon startup, the Oracle instance first searches for a server parameter file in a default location, and if it does not find one, searches for a text initialization parameter file. You can also override an existing server parameter file by naming a text initialization parameter file as an argument of the STARTUP command.

Default file names and locations for the text initialization parameter file are shown in the following table:

If you are creating an Oracle database for the first time, Oracle suggests that you minimize the number of parameter values that you alter. As you become more familiar with your database and environment, you can dynamically tune many initialization parameters using the ALTER SYSTEM statement. If you are using a text initialization parameter file, then your changes are effective only for the current instance. To make them permanent, you must update them manually in the initialization parameter file, or they will be lost over the next shutdown and startup of the database. If you are using a server parameter file, then initialization parameter file changes made by the ALTER SYSTEM statement can persist across shutdown and startup.

Managing Initialization Parameters Using a Server Parameter File

Initialization parameters for the Oracle Database have traditionally been stored in a text initialization parameter file. For better manageability, you can choose to maintain initialization parameters in a binary server parameter file that is persistent across database startup and shutdown. This section introduces the server parameter file, and explains how to manage initialization parameters using either method of storing the parameters. The following topics are contained in this section.

• What Is a Server Parameter File?

• Migrating to a Server Parameter File

• Creating a Server Parameter File

• The SPFILE Initialization Parameter

• Changing Initialization Parameter Values

• Clearing Initialization Parameter Values

• Exporting the Server Parameter File

• Backing Up the Server Parameter File

• Recovering a Lost or Damaged Server Parameter File

• Viewing Parameter Settings

What Is a Server Parameter File?

A server parameter file can be thought of as a repository for initialization parameters that is maintained on the system running the Oracle Database server. It is, by design, a server-side initialization parameter file. Initialization parameters stored in a server parameter file are persistent, in that any changes made to the parameters while an instance is running can persist across instance shutdown and startup. This arrangement eliminates the need to manually update initialization parameters to make persistent any changes effected by ALTER SYSTEM statements. It also provides a basis for self-tuning by the Oracle Database server.

A server parameter file is initially built from a text initialization parameter file using the CREATE SPFILE statement. (It can also be created directly by the Database Configuration Assistant.) The server parameter file is a binary file that cannot be edited using a text editor. Oracle Database provides other interfaces for viewing and modifying parameter settings in a server parameter file.

Caution:

Although you can open the binary server parameter file with a text editor and view its text, do not manually edit it. Doing so will corrupt the file. You will not be able to start your instance, and if the instance is running, it could fail.

When you issue a STARTUP command with no PFILE clause, the Oracle instance searches an operating system–specific default location for a server parameter file from which to read initialization parameter settings. If no server parameter file is found, the instance searches for a text initialization parameter file. If a server parameter file exists but you want to override it with settings in a text initialization parameter file, you must specify the PFILE clause when issuing theSTARTUP command. Instructions for starting an instance using a server parameter file are contained in "Starting Up a Database".

Migrating to a Server Parameter File

If you are currently using a text initialization parameter file, then use the following steps to migrate to a server parameter file.

1. If the initialization parameter file is located on a client system, then transfer the file (for example, FTP) from the client system to the server system.

   Note:

   If you are migrating to a server parameter file in an Oracle Real Application Clusters environment, you must combine all of your instance-specific initialization parameter files into a single initialization parameter file. Instructions for doing this and other actions unique to using a server parameter file for instances that are part of an Oracle Real Application Clusters installation are discussed in Oracle Real Application Clusters Administration and Deployment Guide and in your platform-specific Oracle Real Application Clusters Installation Guide.

2. Create a server parameter file in the default location using the CREATE SPFILE FROM PFILE statement. See "Creating a Server Parameter File" for instructions.

   This statement reads the text initialization parameter file to create a server parameter file. The database does not have to be started to issue a CREATE SPFILE statement.

3. Start up or restart the instance.

   The instance finds the new SPFILE in the default location and starts up with it.

Creating a Server Parameter File

You use the CREATE SPFILE statement to create a server parameter file. You must have the SYSDBA or the SYSOPER system privilege to execute this statement.

Note:

When you use the Database Configuration Assistant to create a database, it automatically creates a server parameter file for you.

The CREATE SPFILE statement can be executed before or after instance startup. However, if the instance has been started using a server parameter file, an error is raised if you attempt to re-create the same server parameter file that is currently being used by the instance.

You can create a server parameter file (SPFILE) from an existing text initialization parameter file or from memory. Creating the SPFILE from memory means copying the current values of initialization parameters in the running instance to the SPFILE.

The following example creates a server parameter file from text initialization parameter file /u01/oracle/dbs/init.ora. In this example no SPFILE name is specified, so the file is created with the platform-specific default name and location shown in Table 2-3.

CREATE SPFILE FROM PFILE='/u01/oracle/dbs/init.ora';

The next example illustrates creating a server parameter file and supplying a name and location.

CREATE SPFILE='/u01/oracle/dbs/test_spfile.ora'
       FROM PFILE='/u01/oracle/dbs/test_init.ora';

The next example illustrates creating a server parameter file in the default location from the current values of the initialization parameters in memory.

CREATE SPFILE FROM MEMORY;

Whether you use the default SPFILE name and default location or specify an SPFILE name and location, if an SPFILE of the same name already exists in the location, it is overwritten without a warning message.

When you create an SPFILE from a text initialization parameter file, comments specified on the same lines as a parameter setting in the initialization parameter file are maintained in the SPFILE. All other comments are ignored.

Oracle recommends that you allow the database to give the SPFILE the default name and store it in the default location. This eases administration of your database. For example, the STARTUP command assumes this default location to read the SPFILE.

Table 2-3 shows the default name and location for both the text initialization parameter file (PFILE) and server parameter file (SPFILE) for the UNIX, Linux, and Windows platforms, both with and without the presence of Oracle Automatic Storage Management (Oracle ASM). The table assumes that the SPFILE is a file. If it is a raw device, the default name could be a logical volume name or partition device name, and the default location could differ.

Table 2-3 PFILE and SPFILE Default Names and Locations on UNIX, LInux, and Windows

^Footnote 1 OH represents the Oracle home directory

^Footnote 2 Assumes database created with DBCA

Note:

Upon startup, the instance first searches for an SPFILE named spfileORACLE_SID.ora, and if not found, searches forspfile.ora. Using spfile.ora enables all Real Application Cluster (Oracle RAC) instances to use the same server parameter file.

If neither SPFILE is found, the instance searches for the text initialization parameter file initORACLE_SID.ora.

If you create an SPFILE in a location other than the default location, you must create in the default PFILE location a "stub" PFILE that points to the server parameter file. For more information, see "Starting Up a Database".

When you create the database with DBCA when Oracle ASM is present, DBCA places the SPFILE in an Oracle ASM disk group, and also causes this stub PFILE to be created.

The SPFILE Initialization Parameter

The SPFILE initialization parameter contains the name of the current server parameter file. When the default server parameter file is used by the database—that is, you issue a STARTUP command and do not specify a PFILE parameter—the value of SPFILE is internally set by the server. The SQL*Plus command SHOW PARAMETERS SPFILE (or any other method of querying the value of a parameter) displays the name of the server parameter file that is currently in use.

Changing Initialization Parameter Values

The ALTER SYSTEM statement enables you to set, change, or restore to default the values of initialization parameters. If you are using a text initialization parameter file, the ALTER SYSTEM statement changes the value of a parameter only for the current instance, because there is no mechanism for automatically updating text initialization parameters on disk. You must update them manually to be passed to a future instance. Using a server parameter file overcomes this limitation.

There are two kinds of initialization parameters:

• Dynamic initialization parameters can be changed for the current Oracle Database instance. The changes take effect immediately.

• Static initialization parameters cannot be changed for the current instance. You must change these parameters in the text initialization file or server parameter file and then restart the database before changes take effect.

Setting or Changing Initialization Parameter Values

Use the SET clause of the ALTER SYSTEM statement to set or change initialization parameter values. The optional SCOPE clause specifies the scope of a change as described in the following table:

It is an error to specify SCOPE=SPFILE or SCOPE=BOTH if the instance did not start up with a server parameter file. The default is SCOPE=BOTH if a server parameter file was used to start up the instance, and MEMORY if a text initialization parameter file was used to start up the instance.

For dynamic parameters, you can also specify the DEFERRED keyword. When specified, the change is effective only for future sessions.

When you specify SCOPE as SPFILE or BOTH, an optional COMMENT clause lets you associate a text string with the parameter update. The comment is written to the server parameter file.

The following statement changes the maximum number of failed login attempts before the connection is dropped. It includes a comment, and explicitly states that the change is to be made only in the server parameter file.

ALTER SYSTEM SET SEC_MAX_FAILED_LOGIN_ATTEMPTS=3
                 COMMENT='Reduce from 10 for tighter security.'
                 SCOPE=SPFILE;

The next example sets a complex initialization parameter that takes a list of attributes. Specifically, the parameter value being set is the LOG_ARCHIVE_DEST_ninitialization parameter. This statement could change an existing setting for this parameter or create a new archive destination.

ALTER SYSTEM 
     SET LOG_ARCHIVE_DEST_4='LOCATION=/u02/oracle/rbdb1/',MANDATORY,'REOPEN=2'
         COMMENT='Add new destination on Nov 29'
         SCOPE=SPFILE;

When a value consists of a list of parameters, you cannot edit individual attributes by the position or ordinal number. You must specify the complete list of values each time the parameter is updated, and the new list completely replaces the old list.

Clearing Initialization Parameter Values

You can use the ALTER SYSTEM RESET command to clear (remove) the setting of any initialization parameter in the SPFILE that was used to start the instance. Neither SCOPE=MEMORY nor SCOPE=BOTH are allowed. The SCOPE = SPFILE clause is not required, but can be included.

You may want to clear a parameter in the SPFILE so that upon the next database startup a default value is used.

See Also:

Oracle Database SQL Language Reference for information about the ALTER SYSTEM command

Exporting the Server Parameter File

You can use the CREATE PFILE statement to export a server parameter file (SPFILE) to a text initialization parameter file. Doing so might be necessary for several reasons:

• For diagnostic purposes, listing all of the parameter values currently used by an instance. This is analogous to the SQL*Plus SHOW PARAMETERS command or selecting from the V$PARAMETER or V$PARAMETER2 views.

• To modify the &spfile;server parameter file by first exporting it, editing the resulting text file, and then re-creating it using the CREATE SPFILE statement

The exported file can also be used to start up an instance using the PFILE clause.

You must have the SYSDBA or the SYSOPER system privilege to execute the CREATE PFILE statement. The exported file is created on the database server system. It contains any comments associated with the parameter in the same line as the parameter setting.

The following example creates a text initialization parameter file from the SPFILE:

CREATE PFILE FROM SPFILE;

Because no names were specified for the files, the database creates an initialization parameter file with a platform-specific name, and it is created from the platform-specific default server parameter file.

The following example creates a text initialization parameter file from a server parameter file, but in this example the names of the files are specified:

CREATE PFILE='/u01/oracle/dbs/test_init.ora'
       FROM SPFILE='/u01/oracle/dbs/test_spfile.ora';

Note:

An alternative is to create a PFILE from the current values of the initialization parameters in memory. The following is an example of the required command:

CREATE PFILE='/u01/oracle/dbs/test_init.ora' FROM MEMORY;

Backing Up the Server Parameter File

You can create a backup of your server parameter file (SPFILE) by exporting it, as described in "Exporting the Server Parameter File". If the backup and recovery strategy for your database is implemented using Recovery Manager (RMAN), then you can use RMAN to create a backup of the SPFILE. The SPFILE is backed up automatically by RMAN when you back up your database, but RMAN also enables you to specifically create a backup of the currently active SPFILE.

See Also:

Oracle Database Backup and Recovery User's Guide

Recovering a Lost or Damaged Server Parameter File

If your server parameter file (SPFILE) becomes lost or corrupted, the current instance may fail, or the next attempt at starting the database instance may fail. There are several ways to recover the SPFILE:

• If the instance is running, issue the following command to re-create the SPFILE from the current values of initialization parameters in memory:

  CREATE SPFILE FROM MEMORY;

  This command creates the SPFILE with the default name and in the default location. You can also create the SPFILE with a new name or in a specified location. See "Creating a Server Parameter File" for examples.

• If you have a valid text initialization parameter file (PFILE), re-create the SPFILE from the PFILE with the following command:

  CREATE SPFILE FROM PFILE;

  This command assumes that the PFILE is in the default location and has the default name. See "Creating a Server Parameter File" for the command syntax to use when the PFILE is not in the default location or has a nondefault name.

• Restore the SPFILE from backup.

  See "Backing Up the Server Parameter File" for more information.

• If none of the previous methods are possible in your situation, perform these steps:

• 
  

1. Create a text initialization parameter file (PFILE) from the parameter value listings in the alert log.

   When an instance starts up, the initialization parameters used for startup are written to the alert log. You can copy and paste this section from the text version of the alert log (without XML tags) into a new PFILE.

   See "Viewing the Alert Log" for more information.

2. Create the SPFILE from the PFILE.

   See "Creating a Server Parameter File" for instructions.

Read/Write Errors During a Parameter Update

If an error occurs while reading or writing the server parameter file during a parameter update, the error is reported in the alert log and all subsequent parameter updates to the server parameter file are ignored. At this point, you can take one of the following actions:

• Shut down the instance, recover the server parameter file and described earlier in this section, and then restart the instance.

• Continue to run the database if you do not care that subsequent parameter updates will not be persistent.

Viewing Parameter Settings

You can view parameter settings in several ways, as shown in the following table.

See Also:

Oracle Database Reference for a complete description of views

2.2 數(shù)據(jù)庫(kù)啟動(dòng)與關(guān)閉：

2.2.1啟動(dòng)分為三個(gè)階段

1）nomount階段：讀取init parameter(讀取初始化參數(shù)，啟動(dòng)實(shí)例)

10:38:35 SQL> select status from v$instance;   （這條命令很實(shí)用，看當(dāng)前數(shù)據(jù)庫(kù)啟動(dòng)的狀態(tài)，有三個(gè) started,mounted,open)                                                                                         

STATUS

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

STARTED

2）mount階段：  讀取控制文件

20:32:53 SQL> select status from v$instance;                                                                                             

STATUS

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

MOUNTED             

3）open階段：  1、檢查所有的datafile、redo log、 group 、password file。   

           2、檢查數(shù)據(jù)庫(kù)的一致性（controlfile、datafile、redo file的檢查點(diǎn)是否一致）

10:38:35 SQL> select file#,checkpoint_change# from v$datafile;     //從控制文件讀出                                                                     

     FILE#     CHECKPOINT_CHANGE#

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

         1             570836

         2             570836

         3             570836

         4             570836

         5             570836

         6             570836

6 rows selected.

10:38:35 SQL> select file#,checkpoint_change# from v$datafile_header;     //從datafile header 讀出                                                       

     FILE# CHECKPOINT_CHANGE#

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

         1             570836

         2             570836

         3             570836

         4             570836

         5             570836

         6             570836

6 rows selected.

數(shù)據(jù)庫(kù)open之前要檢查controlfile所記錄SCN和datafile header 記錄的SCN是否一致；一致就正常打開(kāi)庫(kù)，不一致需要做media recover

10:38:35 SQL> select status from v$instance;                                                                                       

STATUS

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

OPEN   

2.2.2 啟動(dòng)數(shù)據(jù)庫(kù)時(shí)的一些特殊選項(xiàng)

SQL> alter database open read only;

SQL> startup force

SQL> startup upgrade    (只有sysdba能連接)

SQL> startup restrict    （有restrict session權(quán)限才可登錄，sys不受限制）

SQL> alter system enable restricted session;  (open后再限制) 

2.2.3 實(shí)例關(guān)閉：

    shutdown normal： 拒絕新的連接，等待當(dāng)前事務(wù)結(jié)束，等待當(dāng)前會(huì)話結(jié)束，生成檢查點(diǎn)

    shutdown transactional ：拒絕新的連接，等待當(dāng)前事務(wù)結(jié)束，生成檢查點(diǎn)

    shutdown immediate：     拒絕新的連接，未提交的事務(wù)回滾，生成檢查點(diǎn)

    shutdown abort（startup force） ：事務(wù)不回滾，不生成檢查點(diǎn)，下次啟動(dòng)需要做instance  recovery   

*注意：shutdown abort 不會(huì)損壞database。 

2.3 自動(dòng)診斷信息庫(kù)ADR（Automatic Diagnostic Repository) 11g新特性

存儲(chǔ)在操作系統(tǒng)下的一個(gè)目錄（樹(shù)）結(jié)構(gòu)，包括：預(yù)警日志文件，跟蹤文件，健康檢查，DUMP轉(zhuǎn)儲(chǔ)文件等

11g用DIAGNOSTIC_DEST一個(gè)參數(shù)代替了許多老版本的參數(shù)，如BACKGROUND_DUMP_DEST，CORE_DUMP_DEST，USER_DUMP_DEST等。

SQL> show parameter diag

NAME                                 TYPE        VALUE

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

diagnostic_dest                      string      /u01

注意：這是ADR的基目錄，如果你設(shè)置了ORACLE_BASE環(huán)境變量，則diagnostic_dest默認(rèn)值被設(shè)置為同樣的目錄，否則，oracle給你設(shè)置的目錄是$ORALE_HOME/log

10:38:35 SQL> show parameter dump      //這是Oracle11g的。                                                                                     

SQL> show parameter dump

NAME                                         TYPE        VALUE

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

background_core_dump                     string      partial

background_dump_dest                     string      /u01/diag/rdbms/timran11g/timran11g/trace

core_dump_dest                           string      /u01/diag/rdbms/timran11g/timran11g/cdump

max_dump_file_size                      string      unlimited

shadow_core_dump                         string      partial

user_dump_dest                           string      /u01/diag/rdbms/timran11g/timran11g/trace

在oracle 11g中，故障診斷及跟蹤的文件路徑改變較大，告警文件分別以?xún)煞N文件格式存在，xml的文件格式和普通文本格式。這兩份文件的位置分別是V$DIAG_INFO中的Diag Alert 和Diag Trace 對(duì)應(yīng)的目錄。

如果熟悉9i的話，你會(huì)發(fā)現(xiàn)11g將bdump和udump合并到一個(gè)目錄/u01/diag/rdbms/timran11g/timran11g/trace下了。

1）跟蹤文件：

其命名方法依然是:

Background Trace Files（針對(duì)bg process)    :SID_processname_PID.trc      如：timran11g_lgwr_5616.trc

User Trace Files(針對(duì)server process)        :SID_ora_PID.trc        如：timran11g_ora_10744.trc

另外增加.trm（trace map)文件，記錄trc文件的結(jié)構(gòu)信息。

SQL> select * from v$diag_info;

   INST_ID NAME                                   VALUE

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

         1 Diag Enabled                                     TRUE

         1 ADR Base                                             /u01

         1 ADR Home                                        /u01/diag/rdbms/timran11g/timran11g

         1 Diag Trace                                          /u01/diag/rdbms/timran11g/timran11g/trace

         1 Diag Alert                                           /u01/diag/rdbms/timran11g/timran11g/alert

         1 Diag Incident                                    /u01/diag/rdbms/timran11g/timran11g/incident

         1 Diag Cdump                                       /u01/diag/rdbms/timran11g/timran11g/cdump

         1 Health Monitor                                /u01/diag/rdbms/timran11g/timran11g/hm

         1 Default Trace File                       

         1 Active Problem Count                                0

         1 Active Incident Count                                 0

其中Diag Trace對(duì)應(yīng)的目錄里為文本格式的告警日志和跟蹤文件，并沿用在10g中命名方法

2）告警日志：

文本格式的告警日子命名是：alter_SID.log，它包含通知性的消息，如數(shù)據(jù)庫(kù)啟動(dòng)或關(guān)閉，以及有關(guān)表空間創(chuàng)建和刪除的信息,也包括一些內(nèi)部錯(cuò)誤信息等。

alter_SID.log不斷增長(zhǎng)，定期清理是必要的

$cat dev/null > alert_timran11g.log        //將文件清空

或

$rm alter_timran11g.log            //刪掉也沒(méi)有關(guān)系，下次啟動(dòng)會(huì)自動(dòng)創(chuàng)建（考點(diǎn)）

檢查告警日志和跟蹤文件的有關(guān)錯(cuò)誤信息的記錄，如lwgr不能寫(xiě)日志組時(shí)，會(huì)創(chuàng)建一個(gè)跟蹤文件，并將一條信息放入告警日志。

[oracle@timran trace]$ tail -f /u01/diag/rdbms/timran11g/timran11g/trace/alert_timran11g.log

space available in the underlying filesystem or ASM diskgroup.

Tue Sep 04 09:12:19 2012

Completed: ALTER DATABASE OPEN

Tue Sep 04 09:16:41 2012

Starting background process CJQ0

Tue Sep 04 09:16:41 2012

CJQ0 started with pid=29, OS id=2483

Tue Sep 04 10:19:11 2012

drop tablespace tb1

Completed: drop tablespace tb1

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

126. Identify the two situations in which you use the alert log file in your database to check the details.

(Choose two.)

A.Running a query on a table returns "ORA-600: Internal Error."

B.Inserting a value in a table returns "ORA-01722: invalid number."

C.Creating a table returns "ORA-00955: name is already used by an existing object."

D.Inserting a value in a table returns "ORA-00001: unique constraint (SYS.PK_TECHP) violated."

E.Inserting a row in a table returns "ORA-00060: deadlock detected while waiting for resource."

Answer: AE  

答案解析：

 每個(gè)數(shù)據(jù)庫(kù)都有一個(gè)alert_<sid >.log文件。此文件位于數(shù)據(jù)庫(kù)所在的服務(wù)器中，如果設(shè)置了 $ORACLE_BASE，則此文件默認(rèn)存儲(chǔ)在$ORACLE_BASE/diag/rdbms/<db_name>/<SID>/trace中。

數(shù)據(jù)庫(kù)預(yù)警文件是按時(shí)間順序列出消息的日志文件，例如：

  啟動(dòng)時(shí)使用的任何非默認(rèn)初始化參數(shù)

 已發(fā)生的所有內(nèi)部錯(cuò)誤( ORA-600) 、塊損壞錯(cuò)誤 ( ORA-1578 ) 和死鎖錯(cuò)誤( ORA-60 )

  管理操作，如 SQL 語(yǔ)句 CREATE 、 ALTER、 DROP DATABASE 和 TABLESPACE，以及 Enterprise Manager 或 

     SQL*Plus 語(yǔ)句 STARTUP、 SHUTDOWN 、 ARCHIVE LOG和 RECOVER

   與共享服務(wù)器和分派程序進(jìn)程的功能相關(guān)的多個(gè)消息和錯(cuò)誤

   自動(dòng)刷新實(shí)體化視圖時(shí)發(fā)生的錯(cuò)誤

Oracle DB 使用預(yù)警日志來(lái)保留這些事件的記錄，以此作為在操作員控制臺(tái)上顯示這些信息的替代方法。（許多系統(tǒng)會(huì)同時(shí)在控制臺(tái)中顯示這些信息。）如果某個(gè)管理操作成功完成，系統(tǒng)會(huì)將 “completed（已完成） ”消息和一個(gè)時(shí)間戳寫(xiě)入預(yù)警日志中。

ADR的概念在053試題中較多，因?yàn)樗婕傲?1g在數(shù)據(jù)庫(kù)自動(dòng)管理方面的一些重要知識(shí)，如度量，閥值，預(yù)警系統(tǒng)，健康監(jiān)測(cè)等等，我們?cè)?53課程里會(huì)繼續(xù)介紹。

2.4 口令文件

oracle登錄認(rèn)證方式有多種

2.4.1 sys的兩種常用的登錄認(rèn)證方式：OS認(rèn)證和口令文件認(rèn)證。

1）OS認(rèn)證：本地認(rèn)證方式。Oracle不驗(yàn)證用戶(hù)密碼，前提：用戶(hù)必須屬于DBA組，且使用本地登錄。

如：sqlplua / as sysdba

2）口令文件認(rèn)證：是一種網(wǎng)絡(luò)遠(yuǎn)程認(rèn)證方式，只有sysdba權(quán)限的用戶(hù)可以使用口令文件，必須輸入密碼和網(wǎng)絡(luò)連接符。

如：sqlplus sys/oracle@timran11g as sysdba

2.4.2 普通用戶(hù)登錄

1）普通用戶(hù)是指沒(méi)有sysdba權(quán)限的用戶(hù)，比如system 、scott,或者是tim什么的，登錄都必須輸入口令，不管是本地還是遠(yuǎn)程，它們的口令密碼不是以文件形式存放的，而是由oracle保管在其內(nèi)部的數(shù)據(jù)字典里。

2）通過(guò)設(shè)置這個(gè)參數(shù)為T(mén)URE，可以讓口令是大小寫(xiě)敏感的（11g新特性）

SQL> show parameter case

NAME                                 TYPE        VALUE

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

sec_case_sensitive_logon             boolean     TRUE

本節(jié)討論的口令文件是sysdba用戶(hù)的遠(yuǎn)程登錄認(rèn)證密碼文件，主要用于sys用戶(hù)遠(yuǎn)程登錄的認(rèn)證。

位置：$ORACLE_HOME/dbs/orapwSID， 所謂口令文件，指的就是sys的口令文件，可以通過(guò)remote_login_passwordfile參數(shù)控制是否生效

參數(shù)remote_login_passwordfile的三種模式：

  1) none           拒絕sys用戶(hù)從遠(yuǎn)程連接

  2）exclusive    sys用戶(hù)可以從遠(yuǎn)程連接

  3）share          多個(gè)庫(kù)可以共享口令文件

[oracle@timran ~]$ cd /u01/oracle/dbs

[oracle@timran dbs]$ ll

總計(jì) 52

-rw-rw---- 1 oracle oinstall  1544 08-17 07:19 hc_timran11g.dat

-rw-r--r-- 1 oracle oinstall 12920 2001-05-03 initdw.ora

-rw-r--r-- 1 oracle oinstall  8385 1998-09-11 init.ora

-rw-r--r-- 1 oracle oinstall  1024 08-17 13:23 inittimran11g.ora

-rw-r----- 1 oracle oinstall    24 08-17 07:21 lkTIMRAN11

-rw-r----- 1 oracle oinstall    24 08-17 10:36 lkTIMRAN11G

-rw-r----- 1 oracle oinstall  1536 08-31 10:47 orapwtimran11g

-rw-r----- 1 oracle oinstall  3584 09-04 17:49 spfiletimran11g.ora

這里是放參數(shù)文件和（sys)口令文件的地方,orapwtimran11g就是我的sys口令文件

使用orapwd命令創(chuàng)建新的sys口令文件：

你可以先刪掉它，再創(chuàng)建它，在linux下做：

[oracle@timran dbs]$ rm orapwtimran11g         //把sys口令文件刪了

[oracle@timran dbs]$orapwd file=orapwtimran11g password=sys entries=5 force=y   //重新建立口令文件

注意：file=orapw+sid的寫(xiě)法

entries的含義是表示口令文件中可包含的SYSDBA/SYSOPER權(quán)限登錄的最大用戶(hù)數(shù)。

2.5 添加scott 案例（SCOTT誤刪恢復(fù)，執(zhí)行腳本即可）

有時(shí)候，scott用戶(hù)被誤刪掉了，不要緊，可以通過(guò)執(zhí)行下列腳本重新建立。

SQL> @$ORACLE_HOME/rdbms/admin/utlsampl.sql

大家可以試一下：先刪除scott用戶(hù)，再執(zhí)行一下腳本即可。

//補(bǔ)充說(shuō)明

對(duì)于單個(gè)user和tablespace 來(lái)說(shuō)， 可以使用如下命令來(lái)完成。

 步驟一：  刪除user

drop user ×× cascade

說(shuō)明： 刪除了user，只是刪除了該user下的schema objects，是不會(huì)刪除相應(yīng)的tablespace的。

步驟二： 刪除tablespace

DROP TABLESPACE tablespace_name INCLUDING CONTENTS AND DATAFILES;