诊断文件:包括数据库发生的重大事件的信息；用来帮助解决问题和更好的维护数据库。
类型：alertSID.log；后台跟踪文件（Background trace files）；用户跟踪文件（User trace files）。
1、以下操作或事件将被记录在alertSID.log文件：
数据库启动或关闭；
非默认值的参数列表（包括参数名称和参数值，在spfile或pfile损坏时可以借助这些信息从新构造spfile或pfile）；
启动后台进程；
被instance所用的线程；
The log sequence number LGWR is writing to；
有关log调整的信息；
创建表空间和undo segment；
已经提交的alter语句；
错误信息，如：ORA-600；
alter_SID.log所在位置由参数BACKGROUND_DUMP_DEST值定义。
2、后台跟踪文件（Background trace files）：
记录当后台程序出现重大错误时的信息。一般命名为sid_processname_PID.trc。所在位置由参数
BACKGROUND_DUMP_DEST值定义。
实验：
SQL> show parameter background_dump_dest
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
background_dump_dest string /u01/admin/lty/bdump
$ cd /u01/admin/lty/bdump
[oracle@localhost bdump]$ ls -l
total 828
-rw-r--r-- 1 oracle oinstall 142968 Mar 6 23:38 alert_lty.log
-rw-r----- 1 oracle oinstall 660699 Mar 3 17:11 lty_d000_3303.trc
-rw-r----- 1 oracle oinstall 779 Feb 23 09:59 lty_qmn0_3324.trc
-rw-r----- 1 oracle oinstall 802 Feb 23 10:05 lty_qmn0_3338.trc
-rw-r----- 1 oracle oinstall 803 Feb 23 10:10 lty_qmn0_3350.trc
3、用户跟踪文件（User trace files）:
由用户进程产生，也可以被server进程生成，包含跟踪SQL语句的统计信息和记录用户会话错误信息。可以用来对SQL的调优和排错。
命名规则一般为：sid_ora_PID.trc(如：db01_ora_23845.trc)，所处位置由参数USER_DUMP_DEST决定，文件大小由参数MAX_DUMP_FILE_SIZE决定。
启用和关闭用户跟踪
用户级别：ALTER SESSION SET SQL_TRACE = TRUE启用（注意：要在当前session设定）。也可以使用DBMS程序dbms_system.SET_SQL_TRACE_IN_SESSION。
instance级别：SQL_TRACE = TRUE启用（会产生大量信息记录，需谨慎）。
实验：
客户端：
sqlplus system/oracle@xxxx
SQL> alter session set sql_trace = true;
服务器：
$ ps -ef |grep oracle
oracle 3575 3574 0 00:57 ? 00:00:00 oraclelty (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
$cd /u01/admin/lty/udump
$ls –l
total 0
客户端：
SQL> select * from dual;
服务器：
$ls –l
total 4
-rw-r----- 1 oracle oinstall 1383 Mar 7 00:58 lty_ora_3575.trc
4、Practice 3: Managing an Oracle Instance
1 Connect to the database as user SYS and shut down the database.
2 With the database shut down, create an SPFILE from the PFILE. The SPFILE will be created in $ORACLE_HOME/dbs.
解析：SQL> create pfile from spfile;
3 From the operating system, view the SPFILE.
解析：
[oracle@localhost ~]$ cd dbs
[oracle@localhost dbs]$ ls -l
total 48
-rw-r--r-- 1 oracle oinstall 12920 Feb 22 20:14 initdw.ora
-rw-r--r-- 1 oracle oinstall 8475 Feb 22 20:14 init.ora
-rw-r----- 1 oracle oinstall 24 Mar 7 02:07 lkLTY
drwxr-xr-x 2 oracle oinstall 4096 Feb 22 16:29 old
-rwSr----- 1 oracle oinstall 1536 Feb 22 20:14 orapwlty
-rw-r----- 1 oracle oinstall 3584 Feb 22 20:14 spfilelty.ora
$strings spfilelty.ora
4 Connect as user SYS, and start the database using the SPFILE.
5 a Shut down the database and open it in read-only mode.
解析：SQL> startup mount
SQL> alter database open read only;
b Connect as user HR password HR and insert a row into the REGIONS table as
follows:
INSERT INTO regions VALUES (5, 'Mars');
What happens?
解析：HR默认为锁定状态，SQL> select username, user_id, account_status from dba_users;
USERNAME USER_ID ACCOUNT_STATUS
------------------------------ ---------- --------------------------------
HR 47 EXPIRED & LOCKED
解锁，SQL> select username, account_status from dba_users;
USERNAME ACCOUNT_STATUS
------------------------------ --------------------------------
HR EXPIRED
设置密码,SQL> alter user hr identified by hr;
查看状态，SQL> select username, account_status from dba_users;
USERNAME ACCOUNT_STATUS
------------------------------ --------------------------------
HR OPEN
另创建用户lll密码lll，SQL> create user lll identified by lll;
以HR登陆后创建表lll,SQL> create table lll(num integer, name char(10));
插入行，SQL> insert into lll values(0, 'lll');
ERROR 位于第 1 行:
ORA-00604: error occurred at recursive SQL level 1
ORA-16000: database open for read-only access
c Put the database back in read-write mode.
6 a Connect as user HR password HR and insert the following row into the REGIONS
table; do not commit or exit.
INSERT INTO regions VALUES (5, 'Mars');
b In a new telnet session start SQL*Plus. Connect user SYS and perform
a SHUTDOWN TRANSACTIONAL.
c Roll back the insert in the HR session and exit.
What happens to the HR session?
What happens to the SYS session?
7 a In the user SYS session start the database.
b In the open telnet session start SQL*Plus and connect as user HR.
Note: Keep the two SQL*Plus sessions open, one session as user SYS and one as
user HR.
c As user SYS enable a restricted session.
d As user HR, SELECT from the REGIONS table. Is the SELECT successful?
e Exit the session, then reconnect as HR. What happens? The user HR does not have
RESTRICTED SESSION privilege, and therefore, cannot log in.
f As user SYS disable the restricted session.
g Exit the HR telnet session
解析：SQL> alter system enable restricted session;
在数据库置于restricted状态时，不会中断已连接用户的会话。
©著作权归作者所有：来自51CTO博客作者d185740815的原创作品，如需转载，请注明出处，否则将追究法律责任
笔记oracle文件oracle技术