Oracle 數據庫如何設置歸檔模式與非歸檔模式
作者：aqszhuaihuai
本文主要介紹了Oracle數據庫中設置歸檔模式與非歸檔模式的方法，希望能夠對您有所幫助。
Oracle 數據庫操作中，數據庫可以設置為歸檔模式和非歸檔模式。歸檔模式保存所有的事務日志，包括redolog、archivelog等，而非歸檔模式只記錄redolog。我們常常會根據工作的需要將其設置為歸檔模式和非歸檔模式，本文我們就介紹它們的設置過程，接下來就讓我們一起來了解一下吧。
－、查看oracle歸檔模式
- SQL> conn evan/evan (dba)
- Connected.
- SQL> archive log list
- ORA-01031: insufficient privileges
- SQL> conn / as sysdba --archive log list需要以sysdba執行
- 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可以生成多份一樣的日志，保存多個位置，以防不測。
- 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; －－設置歸檔模式
- 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 －－已開啟自動歸檔
- 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.
還可以配置歸檔進程個數
- 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'; －－恢復為原來
- 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
關于Oracle 數據庫歸檔模式與非歸檔模式的設置就介紹這么多，如果您想了解更多關于Oracle數據庫的知識，可以看一下這里的文章：http://database.51cto.com/oracle/，相信一定可以帶給您收獲的！
【編輯推薦】
責任編輯：趙鵬
來源：
CSDN博客
