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

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

專(zhuān)注于為中小企業(yè)提供網(wǎng)站設(shè)計(jì)、成都網(wǎng)站建設(shè)服務(wù),電腦端+手機(jī)端+微信端的三站合一,更高效的管理,為中小企業(yè)平和免費(fèi)做網(wǎng)站提供優(yōu)質(zhì)的服務(wù)。我們立足成都，凝聚了一批互聯(lián)網(wǎng)行業(yè)人才，有力地推動(dòng)了成百上千企業(yè)的穩(wěn)健成長(zhǎng)，幫助中小企業(yè)通過(guò)網(wǎng)站建設(shè)實(shí)現(xiàn)規(guī)模擴(kuò)充和轉(zhuǎn)變。

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ù)庫(kù)的.歸檔模式

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

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

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

3)SQL startup mount (啟動(dòng)實(shí)例并加載數(shù)據(jù)庫(kù)，但不打開(kāi))

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

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

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

7)SQL exit (退出)

做一次完全備份,因?yàn)榉菤w檔日志模式下產(chǎn)生的備份日志對(duì)于歸檔模式已經(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; #歸檔進(jìn)程數(shù)

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

select * from v$bgprocess; #檢察后臺(tái)進(jìn)程

Oracle歸檔日志

顯示歸檔日志信息

1,使用ARCHIVE LOG LIST命令可以顯示日志操作模式,歸檔位置,自動(dòng)歸檔機(jī)器要?dú)w檔的日志序列號(hào)等信息.

2顯示日志操作模式

SELECT name,log_mode FROM v$database;

3,顯示Oracle歸歸檔日志信息.

Col name format a46

Select name, swquence#, first_change# FROM v$archived_log;

Name用于表示Oracle歸歸檔日志文件名,sequence#用于表示歸檔日志對(duì)應(yīng)的日志序列號(hào),firs_change#用于標(biāo)識(shí)歸檔日志的起始SCN值.

4、執(zhí)行介質(zhì) 恢復(fù) 時(shí),需要使用歸檔日志文件,此四必須準(zhǔn)確定位歸檔日志的存放位置.通過(guò)查詢(xún)動(dòng)態(tài)性能視圖v$archive_dest可以取得歸檔日志所在目錄.

SELECT destination FROM v$archive dest;

5,顯示日志歷史信息

SELECT * FROM v$loghist;

THREAD#用于標(biāo)識(shí)重做線(xiàn)程號(hào),SEQUNCE#用于標(biāo)識(shí)日志序列號(hào),FIRST_CHANGE#用于標(biāo)識(shí)日志序列號(hào)對(duì)應(yīng)的起始SCN值,FIRST_TIME用于標(biāo)識(shí)起始SCN的發(fā)生時(shí)間.SWICTH_CHANGE#用于標(biāo)識(shí)日志切換的SCN值.

6.顯示歸檔進(jìn)程信息.

進(jìn)行日志切換時(shí),ARCH進(jìn)程會(huì)自動(dòng)將重做日志內(nèi)容復(fù)制到Oracle歸歸檔日志中,為了加快歸檔速度,應(yīng)該啟用多個(gè)ARCH進(jìn)程.通過(guò)查詢(xún)動(dòng)態(tài)性能視圖V$ARCHIVE_PROCESSES可以顯示所有歸檔進(jìn)程的信息!

SELECT * FROM v$archive_processes;

Porcess用于標(biāo)識(shí)ARCH進(jìn)程的編號(hào),status用于標(biāo)識(shí)ARCH進(jìn)程的狀態(tài)(ACTIVE:活動(dòng),STOPPED:未啟動(dòng)),log_sequence用于標(biāo)識(shí)正在進(jìn)行歸檔的日志序列號(hào),state用于標(biāo)識(shí)ARCH進(jìn)程的工作狀態(tài)

如何啟動(dòng)oracle歸檔日志文件

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

Sql代碼

sql archive log list; #查看是不是歸檔方式sql alter system set log_archive_start=true scope=spfile; #啟用主動(dòng)歸檔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;#如果歸檔到兩個(gè)位置，則可以通過(guò)上邊方法實(shí)現(xiàn)sql alter system set log_archive_format=''arch_%d_%t_%r_%s.log'' #設(shè)置歸檔日記款式sql shutdown immediate;

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

sql select archiver from v$instance;

Sql代碼

日志切換

sql alter system switch logfile;

這次日志切換將歸檔寫(xiě)到兩個(gè)目標(biāo)地,

即上邊的/oracle/ora9/oradata/arch1和/oracle/ora9/oradata/arch1，要是要對(duì)目錄確認(rèn)

在oracle情況中運(yùn)行如下查詢(xún)：

sql select name from v$archived_log;

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

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

Sql代碼

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

sql shutdown immediate;

sql startup mount; #打開(kāi)控制文件，不打開(kāi)數(shù)據(jù)文件sql alter database noarchivelog; #將數(shù)據(jù)庫(kù)切換為非歸檔模式sql alter database open; #將數(shù)據(jù)文件打開(kāi)sql archive log list; #查看此時(shí)便處于非歸檔模式三 歸檔相關(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; #歸檔進(jìn)程數(shù)alter system set log_archive_max_process=5; #將歸檔進(jìn)程數(shù)改為5select * from v$bgprocess; #檢察后臺(tái)進(jìn)程

如何打開(kāi) oracle 日志文件

監(jiān)聽(tīng)日志在$ORACLE_BASE/diag/tnslsnr/hostname/listener/trace目錄下，文件名為listener.log上面的hostname根據(jù)你的實(shí)際主機(jī)名而定

oracle 日志文件不可以打開(kāi)查看嗎

可以打開(kāi)查看

Oracle日志查看

一．Oracle日志的路徑： 登錄：sqlplus "/as sysdba" 查看路徑：SQL select * from v$logfile; SQL select * from v$logfile;(#日志文件路徑)

二．Oracle日志文件包含哪些內(nèi)容：（日志的數(shù)量可能略有不同） control01.ctl example01.dbf redo02.log sysaux01.dbf undotbs01.dbf control02.ctl redo03.log system01.dbf users01.dbf control03.ctl redo01.log SHTTEST.dbf temp01.dbf

三．Oracle日志的查看方法： SQLselect * from v$sql (#查看最近所作的操作) SQLselect * fromv $sqlarea（#查看最近所作的操作） Oracle 數(shù)據(jù)庫(kù)的所有更改都記錄在日志中，從目前來(lái)看，分析Oracle日志的唯一方法就是使用Oracle公司提供的LogMiner來(lái)進(jìn)行，因?yàn)樵嫉娜罩拘畔⑽覀兏緹o(wú)法看懂，Oracle8i后續(xù)版本中自帶了LogMiner,而LogMiner就是讓我們看懂日志信息的工具,通過(guò)這個(gè)工具可以：查明數(shù)據(jù)庫(kù)的邏輯更改，偵察并更正用戶(hù)的誤操作，執(zhí)行事后審計(jì)，執(zhí)行變化分析。

如何啟動(dòng)Oracle的監(jiān)聽(tīng)日志

啟動(dòng)監(jiān)聽(tīng)日志的步驟如下：

1.首先查看當(dāng)前監(jiān)聽(tīng)的狀態(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

從上面顯示信息來(lái)看，我們監(jiān)聽(tīng)正常。并且沒(méi)有看到Listener Log File的信息。

2.進(jìn)入監(jiān)聽(tīng)配置，查看監(jiān)聽(tīng)詳細(xì)信息

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

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)聽(tīng)參數(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īng)日志狀態(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說(shuō)明監(jiān)聽(tīng)日志是關(guān)閉的。這是時(shí)候我們需要啟動(dòng)監(jiān)聽(tīng)日志。

3.設(shè)置當(dāng)前監(jiān)聽(tīng)，如果有多個(gè)監(jiān)聽(tīng)，在修改監(jiān)聽(tīng)配置的時(shí)候需要指定某個(gè)具體的監(jiān)聽(tīng)，我這里只有一個(gè)默認(rè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.啟動(dòng)監(jiān)聽(tīng)日志,并且保存配置信息

LSNRCTL set log_status on –設(shè)置監(jiān)聽(tīng)日志狀態(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)聽(tīng)配置

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īng)狀態(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

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

最后我們可以通過(guò)監(jiān)聽(tīng)日志來(lái)查詢(xún)里面的詳細(xì)信息

[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日志怎么開(kāi)啟,oracle日志在哪
URL網(wǎng)址：http://chinadenli.net/article14/dsgdhge.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供自適應(yīng)網(wǎng)站、App開(kāi)發(fā)、手機(jī)網(wǎng)站建設(shè)、App設(shè)計(jì)、域名注冊(cè)、關(guān)鍵詞優(yōu)化

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶(hù)投稿、用戶(hù)轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請(qǐng)盡快告知，我們將會(huì)在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如需處理請(qǐng)聯(lián)系客服。電話(huà)：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來(lái)源： 創(chuàng)新互聯(lián)