我的oracle 9i学习日志(7)—确诊文件与操练351CTO博客 - 威尼斯人

我的oracle 9i学习日志(7)—确诊文件与操练351CTO博客

2019年03月09日11时05分16秒 | 作者: 涵涵 | 标签: 文件,用户,信息 | 浏览: 1887

确诊文件:包括数据库发作的严重事情的信息;用来协助解决问题和更好的保护数据库。

类型: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-rr 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-rr 1 oracle oinstall 12920 Feb 22 20:14 initdw.ora
-rw-rr 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状况时,不会中止已衔接用户的会话。

版权声明
本文来源于网络,版权归原作者所有,其内容与观点不代表威尼斯人立场。转载文章仅为传播更有价值的信息,如采编人员采编有误或者版权原因,请与我们联系,我们核实后立即修改或删除。

猜您喜欢的文章