Oracle 數(shù)據(jù)庫如何設(shè)置歸檔模式與非歸檔模式
作者：aqszhuaihuai
本文主要介紹了Oracle數(shù)據(jù)庫中設(shè)置歸檔模式與非歸檔模式的方法，希望能夠?qū)δ兴鶐椭?/div> 
分享到微信 
分享到微博
Oracle 數(shù)據(jù)庫操作中，數(shù)據(jù)庫可以設(shè)置為歸檔模式和非歸檔模式。歸檔模式保存所有的事務(wù)日志，包括redolog、archivelog等，而非歸檔模式只記錄redolog。我們常常會(huì)根據(jù)工作的需要將其設(shè)置為歸檔模式和非歸檔模式，本文我們就介紹它們的設(shè)置過程，接下來就讓我們一起來了解一下吧。
－、查看oracle歸檔模式
- SQL> conn evan/evan (dba)
- Connected.
- SQL> archive log list
- ORA-01031: insufficient privileges
- SQL> conn / as sysdba --archive log list需要以sysdba執(zhí)行
- Connected.
- SQL> archive log list
- Database log mode No Archive Mode
- Automatic archival Disabled
- Archive destination USE_DB_RECOVERY_FILE_DEST
- Oldest online log sequence 2
- Current log sequence 4
- 查詢v$database
- SQL> select name,log_mode from v$database;
- NAME LOG_MODE
- --------- ------------
- ORALIFE NOARCHIVELOG
二、修改歸檔模式
歸檔日志位置,Oracle 10g可以生成多份一樣的日志，保存多個(gè)位置，以防不測(cè)。
- SQL> alter system set log_archive_dest_1='location=/oracle/10g/oracle/log/archive_log';
- System altered.
- SQL> alter system set log_archive_dest_2='location=/oracle/10g/oracle/log/archive_log2';
- System altered.
- SQL> shutdown immediate
- ORA-01031: insufficient privileges
- SQL> conn / as sysdba
- Connected.
- SQL> shutdown immediate
- Database closed.
- Database dismounted.
- ORACLE instance shut down.
- SQL> startup mount
- ORACLE instance started.
- Total System Global Area 528482304 bytes
- Fixed Size 1220360 bytes
- Variable Size 163578104 bytes
- Database Buffers 356515840 bytes
- Redo Buffers 7168000 bytes
- Database mounted.
- SQL> alter database archivelog; －－設(shè)置歸檔模式
- Database altered.
- SQL> alter database open;
- Database altered.
配置歸檔文件格式(從oracle 10g 開始，必須帶有%s,%t,%r)
- SQL> alter system set log_archive_format="archive_%t_%s_%r.arclog" scope=spfile;
- System altered.
- SQL> shutdown immediate
- Database closed.
- Database dismounted.
- ORACLE instance shut down.
- SQL> startup mount
- ORACLE instance started.
- Total System Global Area 528482304 bytes
- Fixed Size 1220360 bytes
- Variable Size 163578104 bytes
- Database Buffers 356515840 bytes
- Redo Buffers 7168000 bytes
- Database mounted.
- SQL> archive log list －－查看是否歸檔
- Database log mode Archive Mode
- Automatic archival Enabled －－已開啟自動(dòng)歸檔
- Archive destination /oracle/10g/oracle/log/archive_log2
- Oldest online log sequence 2
- Next log sequence to archive 4
- Current log sequence 4
- SQL> select destination from v$archive_dest; －－查看歸檔日志位置
- DESTINATION
- --------------------------------------------------------------------------------
- /oracle/10g/oracle/log/archive_log
- /oracle/10g/oracle/log/archive_log2
- 10 rows selected.
還可以配置歸檔進(jìn)程個(gè)數(shù)
- alter system set log_archive_max_processes=n
三、修改為非歸檔模式
- SQL> startup mount
- ORACLE instance started.
- Total System Global Area 528482304 bytes
- Fixed Size 1220360 bytes
- Variable Size 167772408 bytes
- Database Buffers 352321536 bytes
- Redo Buffers 7168000 bytes
- Database mounted.
- SQL> alter database noarchivelog;
- Database altered.
- SQL> alter system set log_archive_dest_1='';
- System altered.
- SQL> alter system set log_archive_dest_2='';
- System altered.
- SQL> alter system set log_archive_dest_10='location=USE_DB_RECOVERY_FILE_DEST'; －－恢復(fù)為原來
- System altered.
- SQL> archive log list
- Database log mode No Archive Mode
- Automatic archival Disabled
- Archive destination USE_DB_RECOVERY_FILE_DEST
- Oldest online log sequence 6
- Current log sequence 8
- SQL> shutdown immediate
- ORA-01109: database not open
- Database dismounted.
- ORACLE instance shut down.
- SQL> startup mount
- ORACLE instance started.
- Total System Global Area 528482304 bytes
- Fixed Size 1220360 bytes
- Variable Size 167772408 bytes
- Database Buffers 352321536 bytes
- Redo Buffers 7168000 bytes
- Database mounted.
- SQL> archive log list
- Database log mode No Archive Mode
- Automatic archival Disabled
- Archive destination USE_DB_RECOVERY_FILE_DEST
- Oldest online log sequence 6
- Current log sequence 8
關(guān)于Oracle 數(shù)據(jù)庫歸檔模式與非歸檔模式的設(shè)置就介紹這么多，如果您想了解更多關(guān)于Oracle數(shù)據(jù)庫的知識(shí)，可以看一下這里的文章：http://database.51cto.com/oracle/，相信一定可以帶給您收獲的！
【編輯推薦】
責(zé)任編輯：趙鵬
來源：
CSDN博客
分享到微信 分享到微博相關(guān)推薦
