查看oracle數(shù)據(jù)庫是否為歸檔模式
創(chuàng)新互聯(lián)-專業(yè)網(wǎng)站定制、快速模板網(wǎng)站建設(shè)、高性價(jià)比東蘭網(wǎng)站開發(fā)、企業(yè)建站全套包干低至880元,成熟完善的模板庫,直接使用。一站式東蘭網(wǎng)站制作公司更省心,省錢,快速模板網(wǎng)站建設(shè)找我們，業(yè)務(wù)覆蓋東蘭地區(qū)。費(fèi)用合理售后完善，10多年實(shí)體公司更值得信賴。
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í)例并加載數(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 (退出)
做一次完全備份,因?yàn)榉菤w檔日志模式下產(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; #歸檔進(jìn)程數(shù)
alter system set log_archive_max_process=5; #將歸檔進(jìn)程數(shù)改為5
select * from v$bgprocess; #檢察后臺進(jìn)程
oracle RAC——?dú)w檔日志的開啟方法
＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
g R ? 要修改cluster_database參數(shù)? G R 和以后的版本就不需要了
例子（ g R 或之前） srvctl stop database d RAC節(jié)點(diǎn) sqlstartup mount SQL alter system set log_archive_dest_ = location=/racdb _arch scope=spfile sid= RAC SQL alter system set log_archive_dest_ = location=/racdb _arch scope=spfile sid= RAC SQL alter system set log_archive_dest_ = service=rac scope=spfile sid= RAC SQL alter system set log_archive_dest_ = service=rac scope=spfile sid= RAC SQL alter system set? standby_archive_dest= /racdb _arch scope=spfile sid= RAC SQL alter system set? standby_archive_dest= /racdb _arch scope=spfile sid= RAC sqlalter system set cluster_database=false scope=spfile sqlshutdown immediate sqlstartup mount sqlalter database archivelog sqlalter database open sqlalter system set cluster_database=true scope=spfile sqlshutdown immediate srvctl start database d RAC
例子（ g R 或之后） 節(jié)點(diǎn) SQLstartup mount SQL alter system set log_archive_dest_ = location=/racdb _arch scope=spfile sid= RAC SQL alter system set log_archive_dest_ = location=/racdb _arch scope=spfile sid= RAC SQL alter system set log_archive_dest_ = service=rac scope=spfile sid= RAC SQL alter system set log_archive_dest_ = service=rac scope=spfile sid= RAC SQL alter system set? standby_archive_dest= /racdb _arch scope=spfile sid= RAC SQL alter system set? standby_archive_dest= /racdb _arch scope=spfile sid= RAC srvctl stop database d RAC節(jié)點(diǎn) SQL startup mount SQL alter database archivelog srvctl start database d RAC
測試歸檔日志的切換
節(jié)點(diǎn)一 二上分別執(zhí)行切換日志操作如下 SQL alter system switch logfile 操作執(zhí)行這條語句 次
節(jié)點(diǎn)一查詢 SQL ！ls l /racdb* /racdb _arch total rw r—— oracle oinstall? Oct? _ _ dbf rw r—— oracle oinstall Oct? _ _ dbf rw r—— oracle oinstall?? Oct? _ _ dbf rw r—— oracle oinstall?? Oct? _ _ dbf rw r—— oracle oinstall?? Oct? _ _ dbf /racdb _arch total rw rw—— oracle oinstall Oct? _ _ dbf rw rw—— oracle oinstall Oct? _ _ dbf rw rw—— oracle oinstall?? Oct? _ _ dbf rw rw—— oracle oinstall?? Oct? _ _ dbf rw rw—— oracle oinstall?? Oct? _ _ dbf
節(jié)點(diǎn)二查詢
SQL ！ls l /racdb* /racdb _arch total rw rw—— oracle oinstall? Oct? _ _ dbf rw rw—— oracle oinstall Oct? _ _ dbf rw rw—— oracle oinstall?? Oct? _ _ dbf rw rw—— oracle oinstall?? Oct? _ _ dbf rw rw—— oracle oinstall?? Oct? _ _ dbf /racdb _arch total rw r—— oracle oinstall Oct? _ _ dbf rw r—— oracle oinstall Oct? _ _ dbf rw r—— oracle oinstall?? Oct? _ _ dbf rw r—— oracle oinstall?? Oct? _ _ dbf rw r—— oracle oinstall?? Oct? _ _ dbf
節(jié)點(diǎn)一 SQL show parameter archive_dest NAME???????????????????????????????? TYPE??????? VALUE
log_archive_dest???????????????????? string log_archive_dest_ ?????????????????? string????? location=/racdb _arch log_archive_dest_ ????????????????? string log_archive_dest_ ?????????????????? string????? service=rac log_archive_dest_ ?????????????????? string log_archive_dest_ ?????????????????? string log_archive_dest_ ?????????????????? string log_archive_dest_ ?????????????????? string log_archive_dest_ ?????????????????? string log_archive_dest_ ?????????????????? string log_archive_dest_ ?????????????????? string log_archive_dest_state_ ???????????? string????? enable log_archive_dest_state_ ??????????? string????? enable log_archive_dest_state_ ???????????? string????? enable log_archive_dest_state_ ???????????? string????? enable log_archive_dest_state_ ???????????? string????? enable log_archive_dest_state_ ???????????? string????? enable log_archive_dest_state_ ???????????? string????? enable log_archive_dest_state_ ???????????? string????? enable log_archive_dest_state_ ???????????? string????? enable log_archive_dest_state_ ???????????? string????? enable standby_archive_dest???????????????? string????? /racdb _arch
節(jié)點(diǎn)二
SQL show parameter archive_dest NAME???????????????????????????????? TYPE??????? VALUE
log_archive_dest???????????????????? string log_archive_dest_ ?????????????????? string????? location=/racdb _arch log_archive_dest_ ????????????????? string log_archive_dest_ ?????????????????? string????? service=rac log_archive_dest_ ?????????????????? string log_archive_dest_ ?????????????????? string log_archive_dest_ ?????????????????? string log_archive_dest_ ?????????????????? string log_archive_dest_ ?????????????????? string log_archive_dest_ ?????????????????? string log_archive_dest_ ?????????????????? string log_archive_dest_state_ ???????????? string????? enable log_archive_dest_state_ ??????????? string????? enable log_archive_dest_state_ ???????????? string????? enable log_archive_dest_state_ ???????????? string????? enable log_archive_dest_state_ ???????????? string????? enable log_archive_dest_state_ ???????????? string????? enable log_archive_dest_state_ ???????????? string????? enable log_archive_dest_state_ ???????????? string????? enable log_archive_dest_state_ ???????????? string????? enable log_archive_dest_state_ ???????????? string????? enable standby_archive_dest???????????????? string????? /racdb _arch
lishixinzhi/Article/program/Oracle/201311/18379
1、打開oracle sql developer工具，沒有此工具的可以去oracle官網(wǎng)下載安裝即可，剛剛安裝完成的是沒有記錄的，只有使用工具執(zhí)行過后才會留下記錄可以查詢。打開之后不需要連接數(shù)據(jù)庫就可以查詢到sql記錄了。
2、在二級菜單中，選擇SQL歷史記錄選項(xiàng)，或者按快捷鍵F8打開窗口。
3、默認(rèn)打開是會顯示在最下方的小窗口，在標(biāo)題欄雙擊鼠標(biāo)左鍵，即可最大化窗口，再次雙擊左鍵還原窗口。
4、最大化窗口之后，便于更好的查詢記錄結(jié)果，如圖所示，以往執(zhí)行的sql記錄都顯示出來了，可以在每個(gè)欄目標(biāo)題上單擊，進(jìn)行排序操作，一般可以點(diǎn)擊時(shí)間戳字段，按時(shí)間來排序查詢記錄。第二次點(diǎn)擊，可以切換順序和逆序。
5、最后一個(gè)字段是持續(xù)時(shí)間，點(diǎn)擊排序之后可以查詢出哪些sql語句最快，哪些sql語句執(zhí)行比較慢，需要優(yōu)先等。
6、對于一些不需要的sql記錄可以刪除的，因?yàn)橛涗浂嗔瞬槐阌诓樵?，沒有用的sql最好及時(shí)清除了。只需要選擇您需要刪除的記錄，然后點(diǎn)擊如圖所示的清除按鈕，在二次確認(rèn)彈框中選擇是，即可刪除。
7、在刪除按鈕后面就是一個(gè)搜索輸入框，在此處輸入搜索關(guān)鍵字可以模糊匹配到記錄，對于記不清的記錄，采用模糊搜索的方式查詢，效率非常高。工具會自動根據(jù)我們輸入的關(guān)鍵字進(jì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.進(jìn)入監(jiān)聽配置，查看監(jiān)聽詳細(xì)信息
[oracle@oel ~]$ lsnrctl –進(jìn)入監(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)閉的。這是時(shí)候我們需要啟動監(jiān)聽日志。
3.設(shè)置當(dāng)前監(jiān)聽，如果有多個(gè)監(jiān)聽，在修改監(jiān)聽配置的時(shí)候需要指定某個(gè)具體的監(jiān)聽，我這里只有一個(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.啟動監(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)聽日志來查詢里面的詳細(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’
一 設(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;#如果歸檔到兩個(gè)位置，則可以通過上邊方法實(shí)現(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í)是否處于歸檔模式查詢以確定數(shù)據(jù)庫位于archivelog模式中且歸檔過程正在運(yùn)行sql select log_mode from v$database;
sql select archiver from v$instance;
Sql代碼
日志切換
sql alter system switch logfile;
這次日志切換將歸檔寫到兩個(gè)目標(biāo)地,
即上邊的/oracle/ora9/oradata/arch1和/oracle/ora9/oradata/arch1，要是要對目錄確認(rèn)
在oracle情況中運(yùn)行如下查詢：
sql select name from v$archived_log;
而后在操作系統(tǒng)中確認(rèn)查詢所列出的文件
二 設(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; #查看此時(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; #檢察后臺進(jìn)程
重慶分公司
重慶分公司
