在ORACLE 中怎么查看SQL 執(zhí)行日志

1、首先打開oracle sql developer工具，沒有此工具的可以去百度瀏覽器下載安裝即可，鼠標點擊查看菜單。

讓客戶滿意是我們工作的目標，不斷超越客戶的期望值來自于我們對這個行業(yè)的熱愛。我們立志把好的技術(shù)通過有效、簡單的方式提供給客戶，將通過不懈努力成為客戶在信息化領(lǐng)域值得信任、有價值的長期合作伙伴，公司提供的服務(wù)項目有：域名注冊、雅安服務(wù)器托管、營銷軟件、網(wǎng)站建設(shè)、且末網(wǎng)站維護、網(wǎng)站推廣。

2、在二級菜單中，選擇SQL歷史記錄選項，或者按快捷鍵F8打開窗口。

3、默認打開是會顯示在最下方的小窗口，在標題欄雙擊鼠標左鍵，即可最大化窗口，再次雙擊左鍵還原窗口。

4、最大化窗口之后，便于更好的查詢記錄結(jié)果，可以看到，以往執(zhí)行的sql記錄都顯示出來了可以往下翻動，查詢自己需要的日志。

oracle 如何關(guān)閉打開歸檔日志的方法

查看oracle數(shù)據(jù)庫是否為歸檔模式

SQL select name,log_mode from V$database;

NAME LOG_MODE

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

TEST NOARCHIVELOG

SQL archive log list

Database log mode No Archive Mode

Automatic archival Disabled

Archive destination USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence 80157

Current log sequence 80163

編輯本段配置數(shù)據(jù)庫的.歸檔模式

1.改變非歸檔模式到歸檔模式:

1)SQL conn / as sysdba (以DBA身份連接數(shù)據(jù)庫)

2)SQL shutdown immediate;(立即關(guān)閉數(shù)據(jù)庫)

3)SQL startup mount (啟動實例并加載數(shù)據(jù)庫，但不打開)

4)SQL alter database archivelog; (更改數(shù)據(jù)庫為歸檔模式)

5)SQL alter database open; (打開數(shù)據(jù)庫)

6)SQL alter system archive log start; (啟用自動歸檔)

7)SQL exit (退出)

做一次完全備份,因為非歸檔日志模式下產(chǎn)生的備份日志對于歸檔模式已經(jīng)不可用了.這一步非非常重要!

2.改變歸檔模式到非歸檔模式:

1)SQLSHUTDOWN NORMAL/IMMEDIATE;

2)SQLSTARTUP MOUNT;

3)SQLALTER DATABASE NOARCHIVELOG;

4)SQLALTER DATABASE OPEN;

3.歸檔相關(guān)命令

archive log stop;

archive log start;

archive log list;

show parameters;

show parameters log_archive_start;

show parameters log_archive_max_process; #歸檔進程數(shù)

alter system set log_archive_max_process=5; #將歸檔進程數(shù)改為5

select * from v$bgprocess; #檢察后臺進程

如何啟動Oracle的監(jiān)聽日志

啟動監(jiān)聽日志的步驟如下：

1.首先查看當(dāng)前監(jiān)聽的狀態(tài)

[oracle@oel ~]$ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.3.0 – Production on 01-JUL-2012 13:29:58

Copyright (c) 1991, 2011, Oracle. All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oel)(PORT=1521)))

STATUS of the LISTENER

————————

Alias listener

Version TNSLSNR for Linux: Version 11.2.0.3.0 – Production

Start Date 01-JUL-2012 13:12:51

Uptime 0 days 0 hr. 17 min. 7 sec

Trace Level off

Security ON: Local OS Authentication

SNMP OFF

Listener Parameter File /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora

Listening Endpoints Summary…

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oel)(PORT=1521)))

(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))

Services Summary…

Service “orcl” has 1 instance(s).

Instance “orcl”, status READY, has 1 handler(s) for this service…

Service “orclXDB” has 1 instance(s).

Instance “orcl”, status READY, has 1 handler(s) for this service…

The command completed successfully

從上面顯示信息來看，我們監(jiān)聽正常。并且沒有看到Listener Log File的信息。

2.進入監(jiān)聽配置，查看監(jiān)聽詳細信息

[oracle@oel ~]$ lsnrctl –進入監(jiān)聽配置

LSNRCTL for Linux: Version 11.2.0.3.0 – Production on 01-JUL-2012 13:35:00

Copyright (c) 1991, 2011, Oracle. All rights reserved.

Welcome to LSNRCTL, type “help” for information.

LSNRCTL show –查看監(jiān)聽參數(shù)信息

The following operations are available after show

An asterisk (*) denotes a modifier or extended command:

rawmode displaymode

rules trc_file

trc_directory trc_level

log_file log_directory

log_status current_listener

inbound_connect_timeout startup_waittime

snmp_visible save_config_on_stop

dynamic_registration enable_global_dynamic_endpoint

oracle_home pid

connection_rate_limit

LSNRCTL show log_status –查看監(jiān)聽日志狀態(tài)

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oel)(PORT=1521)))

LISTENER parameter “l(fā)og_status” set to OFF

The command completed successfully

上面信息顯示Listener parameter “l(fā)og_status” set to OFF說明監(jiān)聽日志是關(guān)閉的。這是時候我們需要啟動監(jiān)聽日志。

3.設(shè)置當(dāng)前監(jiān)聽，如果有多個監(jiān)聽，在修改監(jiān)聽配置的時候需要指定某個具體的監(jiān)聽，我這里只有一個默認的Listener，所以不需要設(shè)置。我這里還是演示一下。

LSNRCTL show current_listener

Current Listener is LISTENER

LSNRCTL set current_listener Listener

Current Listener is Listener

LSNRCTL show current_listener

Current Listener is Listener

4.啟動監(jiān)聽日志,并且保存配置信息

LSNRCTL set log_status on –設(shè)置監(jiān)聽日志狀態(tài)為on

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oel)(PORT=1521)))

LISTENER parameter “l(fā)og_status” set to ON

The command completed successfully

LSNRCTL save_config –保存監(jiān)聽配置

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oel)(PORT=1521)))

Saved listener configuration parameters.

Listener Parameter File /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora

Old Parameter File /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.bak

The command completed successfully

5.在查看監(jiān)聽狀態(tài)

[oracle@oel ~]$ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.3.0 – Production on 01-JUL-2012 13:44:32

Copyright (c) 1991, 2011, Oracle. All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oel)(PORT=1521)))

STATUS of the LISTENER

————————

Alias listener

Version TNSLSNR for Linux: Version 11.2.0.3.0 – Production

Start Date 01-JUL-2012 13:12:51

Uptime 0 days 0 hr. 31 min. 41 sec

Trace Level off

Security ON: Local OS Authentication

SNMP OFF

Listener Parameter File /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora

Listener Log File /u01/app/oracle/diag/tnslsnr/oel/listener/alert/log.xml

Listening Endpoints Summary…

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oel)(PORT=1521)))

(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))

Services Summary…

Service “orcl” has 1 instance(s).

Instance “orcl”, status READY, has 1 handler(s) for this service…

Service “orclXDB” has 1 instance(s).

Instance “orcl”, status READY, has 1 handler(s) for this service…

The command completed successfully

通過上述配置之后，看到了Listener Log File /u01/app/oracle/diag/tnslsnr/oel/listener/alert/log.xml

最后我們可以通過監(jiān)聽日志來查詢里面的詳細信息

[oracle@oel ~]$ grep ‘2012-07-01T13:45:40′ /u01/app/oracle/diag/tnslsnr/oel/listener/alert/log.xml|more

msg time=’2012-07-01T13:45:40.302+08:00′ org_id=’oracle’ comp_id=’tnslsnr’

如何啟動oracle歸檔日志文件

一 設(shè)置為歸檔方式

Sql代碼

sql archive log list; #查看是不是歸檔方式sql alter system set log_archive_start=true scope=spfile; #啟用主動歸檔sql alter system set log_archive_dest=''location=/oracle/ora9/oradata/arch'' scope=spfile;#設(shè)置歸檔路徑sql alter system set log_archive_dest_1=''location=/oracle/ora9/oradata/arch1'' scope=spfile;sql alter system set log_archive_dest_2=''location=/oracle/ora9/oradata/arch2'' scope=spfile;#如果歸檔到兩個位置，則可以通過上邊方法實現(xiàn)sql alter system set log_archive_format=''arch_%d_%t_%r_%s.log'' #設(shè)置歸檔日記款式sql shutdown immediate;

sql startup mount; #打開控制文件，不打開數(shù)據(jù)文件sql alter database archivelog; #將數(shù)據(jù)庫切換為歸檔模式sql alter database open; #將數(shù)據(jù)文件打開sql archive log list; #查看此時是否處于歸檔模式查詢以確定數(shù)據(jù)庫位于archivelog模式中且歸檔過程正在運行sql select log_mode from v$database;

sql select archiver from v$instance;

Sql代碼

日志切換

sql alter system switch logfile;

這次日志切換將歸檔寫到兩個目標地,

即上邊的/oracle/ora9/oradata/arch1和/oracle/ora9/oradata/arch1，要是要對目錄確認

在oracle情況中運行如下查詢：

sql select name from v$archived_log;

而后在操作系統(tǒng)中確認查詢所列出的文件

二 設(shè)置非歸檔方式

Sql代碼

sql archive log list; #查看是否是歸檔方式sql alter system set log_archive_start=false scope=spfile;#禁用自動歸檔

sql shutdown immediate;

sql startup mount; #打開控制文件，不打開數(shù)據(jù)文件sql alter database noarchivelog; #將數(shù)據(jù)庫切換為非歸檔模式sql alter database open; #將數(shù)據(jù)文件打開sql archive log list; #查看此時便處于非歸檔模式三 歸檔相關(guān)命令

Sql代碼

archive log stop;

archive log start;

archive log list;

show parameters;

show parameters log_archive_start;

show parameters log_archive_max_process; #歸檔進程數(shù)alter system set log_archive_max_process=5; #將歸檔進程數(shù)改為5select * from v$bgprocess; #檢察后臺進程