实例管理及数据库的启动/关闭
2.1 实例和参数文件
1、instance 功能:用于管理和访问database。
instance在启动阶段读取初始化参数文件（init parameter files)。
2、init parameter files ：管理实例相关启动参数 。位置：$ORACLE_HOME/dbs
3、pfile ：（parameter file）静态参数文件。
1）文本文件，必须通过编辑器修改参数。
2）修改参数下次重启实例才生效。
3）pfile参数文件可以不在database server上。
命名方式：init+SID.ora
4、spfile ：（system parameter file) 动态参数文件。
1）二进制文件，不可以通过编辑器修改。
2 ) Linux下strings可以查看 。
3） 必须在database server段的指定路径下。
命名方式: spfile+SID.ora
静态参数和动态参数
在spfile读到内存后，有一部分参数是可以直接在内存中修改，并对当前instance立即生效，这样的参数叫动态参数。除了动态参数都是静态参数。静态参数修改spfile文件。动态参数在instance关闭后失效，而静态参数是下次instance启动后才生效。
修改spfile文件的方法：
alter system set 参数=值 [scope=memory|spfile|both]
alter system reset 参数 [scope=memory|spfile|both] SID='*' //恢复缺省值。
第一种scope=memory 参数修改立刻生效，但不修改spfile文件。
第二种scope=spfile 修改了spfile文件，重启后生效。
第三种scope=both 前两种都要满足。
如果不写scope，即缺省，那就是第三种。
*注意：如果不修改spfile，将无法更改静态参数。
通过查看v$parameter ，可以确定哪些参数可以在memory修改，制定scope。
10:38:35 SQL> desc v$parameter;
其中：
ISSYS_MODIFIABLE参数：对应alter system 命令，即系统级修改
10:38:35 SQL> select distinct issys_modifiable from v$parameter;
ISSYS_MODIFIABLE
----------------------
IMMEDIATE //对应scope=memory
FALSE //只能scope=spfile,即修改spfile文件，下次启动才生效。
DEFERRED //其他session有效
ISSES_MODIFIABLE参数：对应alter session命令，即session级修改
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
这个结果表示 sql_trace参数在session级别可以改，在system级也可以both修改（动态参数）。
如何判断参数是动态参数还是静态参数？
第一种方法：查看动态性能视图v$parameter
ISSES_MODIFIABLE （session级别）
TRUE //动态参数
FALSE //静态参数
ISSYS_MODIFIABLE （数据库级）
IMMEDIATE //动态参数 ，对应scope=memory，
FALSE //静态参数 ，只能scope=spfile,即修改spfile文件，下次启动才生效。
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 行出现错误:
ORA-02095: 无法修改指定的初始化参数
说明上述为静态参数，需要写入到spfile中：
SQL> alter system set log_buffer=5242880 scope=spfile;
系统已更改。
SQL> show parameter log_buffer;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_buffer integer 3145728
SQL>
SQL> shutdown immediate
数据库已经关闭。
已经卸载数据库。
ORACLE 例程已经关闭。
SQL> startup
ORACLE 例程已经启动。
Total System Global Area 849530880 bytes
Fixed Size 1339824 bytes
Variable Size 595594832 bytes
Database Buffers 243269632 bytes
Redo Buffers 9326592 bytes
数据库装载完毕。
数据库已经打开。
SQL> show parameter log_buffer;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_buffer integer 5242880
举例2：（动态参数）
暂略
5、startup时读取参数文件，找到$ORACLE_HOME/dbs目录，顺序是优先spfile启动，没有spfile 才从pfile启动。
pfile和spfile可以相互生成：
SQL>create pfile from spfile
SQL>create spfile from pfile（使用spfile启动后不能在线生成spfile，ORA-32002: 无法创建已由实例使用的 SPFILE）
*注意：
1）如果使用pfile启动,设置scope=spfile将失败！但可以设置scope=memory。
可以通过当前内存参数生成pfile和spfile（11g新特性）：
SQL>create pfile from memory;
SQL>create spfile from memory;
有了spfile，pfile一般留做备用，特殊情况也可以使用pfile启动,命令如下：
10:38:35 SQL> startup pfile=$ORACLE_HOME/dbs/inittimran.ora
怎样知道实例是spfile启动还是pfile启动的？
10:38:35 SQL> show parameter spfile
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /u01/oracle/dbs/spfiletimran11g.ora
//如果value有值，说明数据库启动时读的是spfile
另一个办法是看v$spparameter（spfile参数视图）中的参数memory_target的isspecified字段值，如果是TRUE 说明是spfile启动的（考点）
10:42:35 SQL> select name,value,isspecified from v$spparameter where name like 'memory_target';
NAME VALUE ISSPECIFIED
-------------------------------------------------------------------------------- -------------------------------------------
memory_target 423624704 TRUE
EM对初始参数有较好的可视化界面，可以看看
oracle官方文档参数文件介绍：pfile,spfile
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:
| Platform | Default Name | Default Location |
|---|---|---|
| UNIX and Linux | initORACLE_SID.oraFor example, the initialization parameter file for the
| ORACLE_HOME/dbs |
| Windows | initORACLE_SID.ora | ORACLE_HOME\database |
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.
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
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".
If you are currently using a text initialization parameter file, then use the following steps to migrate to a server parameter file.
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.
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.
Start up or restart the instance.
The instance finds the new SPFILE in the default location and starts up with it.
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
| Platform | PFILE Default Name | SPFILE Default Name | PFILE Default Location | SPFILE Default Location |
|---|---|---|---|---|
UNIX and Linux |
|
| OH | Without Oracle ASM: OH When Oracle ASM is present: In the same disk group as the data filesFoot 2 |
Windows |
|
| OH | Without Oracle ASM: OH When Oracle ASM is present: In the same disk group as the data filesFootref 2 |
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 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.
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.
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:
| SCOPE Clause | Description |
|---|---|
SCOPE = SPFILE | The change is applied in the server parameter file only. The effect is as follows:
This is the only |
SCOPE = MEMORY | The change is applied in memory only. The effect is as follows:
For static parameters, this specification is not allowed. |
SCOPE = BOTH | The change is applied in both the server parameter file and memory. The effect is as follows:
For static parameters, this specification is not allowed. |
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.
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
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;
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
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:
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.
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.
You can view parameter settings in several ways, as shown in the following table.
| Method | Description |
|---|---|
SHOW PARAMETERS | This SQL*Plus command displays the values of initialization parameters in effect for the current session. |
SHOW SPPARAMETERS | This SQL*Plus command displays the values of initialization parameters in the server parameter file (SPFILE). |
CREATE PFILE | This SQL statement creates a text initialization parameter file (PFILE) from the SPFILE or from the current in-memory settings. You can then view the PFILE with any text editor. |
V$PARAMETER | This view displays the values of initialization parameters in effect for the current session. |
V$PARAMETER2 | This view displays the values of initialization parameters in effect for the current session. It is easier to distinguish list parameter values in this view because each list parameter value appears in a separate row. |
V$SYSTEM_PARAMETER | This view displays the values of initialization parameters in effect for the instance. A new session inherits parameter values from the instance-wide values. |
V$SYSTEM_PARAMETER2 | This view displays the values of initialization parameters in effect for the instance. A new session inherits parameter values from the instance-wide values. It is easier to distinguish list parameter values in this view because each list parameter value appears in a separate row. |
V$SPPARAMETER | This view displays the current contents of the SPFILE. The view returns FALSE values in the ISSPECIFIEDcolumn if an SPFILE is not being used by the instance. |
See Also:
Oracle Database Reference for a complete description of views
2.2 数据库启动与关闭：
2.2.1启动分为三个阶段
1）nomount阶段：读取init parameter(读取初始化参数，启动实例)
10:38:35 SQL> select status from v$instance; （这条命令很实用，看当前数据库启动的状态，有三个 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、检查数据库的一致性（controlfile、datafile、redo file的检查点是否一致）
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.
数据库open之前要检查controlfile所记录SCN和datafile header 记录的SCN是否一致；一致就正常打开库，不一致需要做media recover
10:38:35 SQL> select status from v$instance;
STATUS
------------
OPEN
2.2.2 启动数据库时的一些特殊选项
SQL> alter database open read only;
SQL> startup force
SQL> startup upgrade (只有sysdba能连接)
SQL> startup restrict （有restrict session权限才可登录，sys不受限制）
SQL> alter system enable restricted session; (open后再限制)
2.2.3 实例关闭：
shutdown normal： 拒绝新的连接，等待当前事务结束，等待当前会话结束，生成检查点
shutdown transactional ：拒绝新的连接，等待当前事务结束，生成检查点
shutdown immediate： 拒绝新的连接，未提交的事务回滚，生成检查点
shutdown abort（startup force） ：事务不回滚，不生成检查点，下次启动需要做instance recovery
*注意：shutdown abort 不会损坏database。
2.3 自动诊断信息库ADR（Automatic Diagnostic Repository) 11g新特性
存储在操作系统下的一个目录（树）结构，包括：预警日志文件，跟踪文件，健康检查，DUMP转储文件等
11g用DIAGNOSTIC_DEST一个参数代替了许多老版本的参数，如BACKGROUND_DUMP_DEST，CORE_DUMP_DEST，USER_DUMP_DEST等。
SQL> show parameter diag
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
diagnostic_dest string /u01
注意：这是ADR的基目录，如果你设置了ORACLE_BASE环境变量，则diagnostic_dest默认值被设置为同样的目录，否则，oracle给你设置的目录是$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中，故障诊断及跟踪的文件路径改变较大，告警文件分别以两种文件格式存在，xml的文件格式和普通文本格式。这两份文件的位置分别是V$DIAG_INFO中的Diag Alert 和Diag Trace 对应的目录。
如果熟悉9i的话，你会发现11g将bdump和udump合并到一个目录/u01/diag/rdbms/timran11g/timran11g/trace下了。
1）跟踪文件：
其命名方法依然是:
Background Trace Files（针对bg process) :SID_processname_PID.trc 如：timran11g_lgwr_5616.trc
User Trace Files(针对server process) :SID_ora_PID.trc 如：timran11g_ora_10744.trc
另外增加.trm（trace map)文件，记录trc文件的结构信息。
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对应的目录里为文本格式的告警日志和跟踪文件，并沿用在10g中命名方法
2）告警日志：
文本格式的告警日子命名是：alter_SID.log，它包含通知性的消息，如数据库启动或关闭，以及有关表空间创建和删除的信息,也包括一些内部错误信息等。
alter_SID.log不断增长，定期清理是必要的
$cat dev/null > alert_timran11g.log //将文件清空
或
$rm alter_timran11g.log //删掉也没有关系，下次启动会自动创建（考点）
检查告警日志和跟踪文件的有关错误信息的记录，如lwgr不能写日志组时，会创建一个跟踪文件，并将一条信息放入告警日志。
[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
答案解析：
每个数据库都有一个alert_<sid >.log文件。此文件位于数据库所在的服务器中，如果设置了 $ORACLE_BASE，则此文件默认存储在$ORACLE_BASE/diag/rdbms/<db_name>/<SID>/trace中。
数据库预警文件是按时间顺序列出消息的日志文件，例如：
启动时使用的任何非默认初始化参数
已发生的所有内部错误( ORA-600) 、块损坏错误 ( ORA-1578 ) 和死锁错误( ORA-60 )
管理操作，如 SQL 语句 CREATE 、 ALTER、 DROP DATABASE 和 TABLESPACE，以及 Enterprise Manager 或
SQL*Plus 语句 STARTUP、 SHUTDOWN 、 ARCHIVE LOG和 RECOVER
与共享服务器和分派程序进程的功能相关的多个消息和错误
自动刷新实体化视图时发生的错误
Oracle DB 使用预警日志来保留这些事件的记录，以此作为在操作员控制台上显示这些信息的替代方法。（许多系统会同时在控制台中显示这些信息。）如果某个管理操作成功完成，系统会将 “completed（已完成） ”消息和一个时间戳写入预警日志中。
ADR的概念在053试题中较多，因为它涉及了11g在数据库自动管理方面的一些重要知识，如度量，阀值，预警系统，健康监测等等，我们在053课程里会继续介绍。
2.4 口令文件
oracle登录认证方式有多种
2.4.1 sys的两种常用的登录认证方式：OS认证和口令文件认证。
1）OS认证：本地认证方式。Oracle不验证用户密码，前提：用户必须属于DBA组，且使用本地登录。
如：sqlplua / as sysdba
2）口令文件认证：是一种网络远程认证方式，只有sysdba权限的用户可以使用口令文件，必须输入密码和网络连接符。
如：sqlplus sys/oracle@timran11g as sysdba
2.4.2 普通用户登录
1）普通用户是指没有sysdba权限的用户，比如system 、scott,或者是tim什么的，登录都必须输入口令，不管是本地还是远程，它们的口令密码不是以文件形式存放的，而是由oracle保管在其内部的数据字典里。
2）通过设置这个参数为TURE，可以让口令是大小写敏感的（11g新特性）
SQL> show parameter case
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sec_case_sensitive_logon boolean TRUE
本节讨论的口令文件是sysdba用户的远程登录认证密码文件，主要用于sys用户远程登录的认证。
位置：$ORACLE_HOME/dbs/orapwSID， 所谓口令文件，指的就是sys的口令文件，可以通过remote_login_passwordfile参数控制是否生效
参数remote_login_passwordfile的三种模式：
1) none 拒绝sys用户从远程连接
2）exclusive sys用户可以从远程连接
3）share 多个库可以共享口令文件
[oracle@timran ~]$ cd /u01/oracle/dbs
[oracle@timran dbs]$ ll
总计 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
这里是放参数文件和（sys)口令文件的地方,orapwtimran11g就是我的sys口令文件
使用orapwd命令创建新的sys口令文件：
你可以先删掉它，再创建它，在linux下做：
[oracle@timran dbs]$ rm orapwtimran11g //把sys口令文件删了
[oracle@timran dbs]$orapwd file=orapwtimran11g password=sys entries=5 force=y //重新建立口令文件
注意：file=orapw+sid的写法
entries的含义是表示口令文件中可包含的SYSDBA/SYSOPER权限登录的最大用户数。
2.5 添加scott 案例（SCOTT误删恢复，执行脚本即可）
有时候，scott用户被误删掉了，不要紧，可以通过执行下列脚本重新建立。
SQL> @$ORACLE_HOME/rdbms/admin/utlsampl.sql
大家可以试一下：先删除scott用户，再执行一下脚本即可。
//补充说明
对于单个user和tablespace 来说， 可以使用如下命令来完成。
步骤一： 删除user
drop user ×× cascade
说明： 删除了user，只是删除了该user下的schema objects，是不会删除相应的tablespace的。
步骤二： 删除tablespace
DROP TABLESPACE tablespace_name INCLUDING CONTENTS AND DATAFILES;
免责声明：本站发布的内容（图片、视频和文字）以原创、转载和分享为主，文章观点不代表本网站立场，如果涉及侵权请联系站长邮箱：is@yisu.com进行举报，并提供相关证据，一经查实，将立刻删除涉嫌侵权内容。
