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
- 1.
- 2.
- 3.
- 4.
- 5.
- 6.
- 7.
- 8.
- 9.
- 10.
- 11.
- 12.
- 13.
- 14.
- 15.
- 16.
- 17.
- 18.
- 19.
- 20.
- 21.
- 22.
- 23.
- 24.
- 25.
- 26.
- 27.
- 28.
- 29.
- 30.
- 31.
- 32.
- 33.
二、修改归档模式
归档日志位置,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.
- 1.
- 2.
- 3.
- 4.
- 5.
- 6.
- 7.
- 8.
- 9.
- 10.
- 11.
- 12.
- 13.
- 14.
- 15.
- 16.
- 17.
- 18.
- 19.
- 20.
- 21.
- 22.
- 23.
- 24.
- 25.
- 26.
- 27.
- 28.
- 29.
- 30.
- 31.
- 32.
- 33.
- 34.
- 35.
- 36.
- 37.
- 38.
- 39.
- 40.
- 41.
- 42.
- 43.
- 44.
- 45.
- 46.
- 47.
配置归档文件格式(从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.
- 1.
- 2.
- 3.
- 4.
- 5.
- 6.
- 7.
- 8.
- 9.
- 10.
- 11.
- 12.
- 13.
- 14.
- 15.
- 16.
- 17.
- 18.
- 19.
- 20.
- 21.
- 22.
- 23.
- 24.
- 25.
- 26.
- 27.
- 28.
- 29.
- 30.
- 31.
- 32.
- 33.
- 34.
- 35.
- 36.
- 37.
- 38.
- 39.
- 40.
- 41.
- 42.
- 43.
- 44.
- 45.
- 46.
- 47.
- 48.
- 49.
- 50.
- 51.
- 52.
- 53.
还可以配置归档进程个数
alter system set log_archive_max_processes=n
- 1.
三、修改为非归档模式
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
- 1.
- 2.
- 3.
- 4.
- 5.
- 6.
- 7.
- 8.
- 9.
- 10.
- 11.
- 12.
- 13.
- 14.
- 15.
- 16.
- 17.
- 18.
- 19.
- 20.
- 21.
- 22.
- 23.
- 24.
- 25.
- 26.
- 27.
- 28.
- 29.
- 30.
- 31.
- 32.
- 33.
- 34.
- 35.
- 36.
- 37.
- 38.
- 39.
- 40.
- 41.
- 42.
- 43.
- 44.
- 45.
- 46.
- 47.
- 48.
- 49.
- 50.
- 51.
- 52.
- 53.
- 54.
- 55.
- 56.
- 57.
- 58.
- 59.
- 60.
- 61.
- 62.
- 63.
- 64.
- 65.
- 66.
- 67.
- 68.
- 69.
- 70.
- 71.
- 72.
- 73.
- 74.
- 75.
- 76.
- 77.
- 78.
- 79.
关于Oracle 数据库归档模式与非归档模式的设置就介绍这么多，如果您想了解更多关于Oracle数据库的知识，可以看一下这里的文章：http://database.51cto.com/oracle/，相信一定可以带给您收获的！
【编辑推荐】
