先观察当前的状态：

[root@o_target ~]# su - oracle    
[oracle@o_target ~]$ sqlplus / as sysdba    
    
SQL*Plus: Release 11.2.0.1.0 Production on Wed Mar 19 12:38:24 2014    
    
Copyright (c) 1982, 2009, Oracle.  All rights reserved.    
    
Connected to an idle instance.    
    
SQL> startup;    
ORACLE instance started.    
    
Total System Global Area 1023004672 bytes    
Fixed Size                  2219752 bytes    
Variable Size             624951576 bytes    
Database Buffers          390070272 bytes    
Redo Buffers                5763072 bytes    
Database mounted.    
Database opened.    
SQL> archive log list;    
Database log mode              No Archive Mode    
Automatic archival             Disabled    
Archive destination            USE_DB_RECOVERY_FILE_DEST    
Oldest online log sequence     4    
Current log sequence           6 

SQL>

发现是没有在 Archive模式下。

 

进行转换，发现必须对数据库在mount状态下进行操作。

   
SQL> alter database archivelog;    
alter database archivelog    
*    
ERROR at line 1:    
ORA-01126: database must be mounted in this instance and not open in any    
instance    

然后，进入mount状态，进行转换：

SQL> shutdown immediate;    
Database closed.    
Database dismounted.    
ORACLE instance shut down.    
SQL> startup mount;           
ORACLE instance started.    
    
Total System Global Area 1023004672 bytes    
Fixed Size                  2219752 bytes    
Variable Size             624951576 bytes    
Database Buffers          390070272 bytes    
Redo Buffers                5763072 bytes    
Database mounted.    
SQL> alter database archivelog;    
    
Database altered.    
    
SQL> archive log list;    
Database log mode              Archive Mode    
Automatic archival             Enabled    
Archive destination            USE_DB_RECOVERY_FILE_DEST    
Oldest online log sequence     4    
Next log sequence to archive   6    
Current log sequence           6    
SQL>      

发现现在缺省使用的路径为 DB_RECOVERY_FILE_DEST，此路径是和Oracle的Flash_back_recovery 路径混杂在一起的，不太妥当。

参见：

http://jinyumantang110.blog.163.com/blog/static/5457915620107994131968/

http://anuj-singh.blogspot.jp/2011/10/oracle-archive-log-on.html

其提到：never use this area for archive log file , system will hang after destination full

 

先观察实际路径设置：

SQL> show parameter DB_RECOVERY_FILE_DEST;    
    
NAME                                 TYPE        VALUE    
------------------------------------ ----------- ------------------------------    
db_recovery_file_dest                string      /u01/app/oracle/flash_recovery    
                                                 _area    
db_recovery_file_dest_size           big integer 3882M    

 

尝试更改路径：

SQL> alter system set log_archive_dest = '/u01/app/arch' scope=both;    
alter system set log_archive_dest = '/u01/app/arch' scope=both    
*    
ERROR at line 1:    
ORA-02097: parameter cannot be modified because specified value is invalid    
ORA-16018: cannot use LOG_ARCHIVE_DEST with LOG_ARCHIVE_DEST_n or    
DB_RECOVERY_FILE_DEST    
   

需要把 USE_DB_RECOVERY_FILE_DEST变成空值：

SQL> alter system set DB_RECOVERY_FILE_DEST='';    
    
System altered.    
    
SQL> alter system set log_archive_dest =     
  2  '/u01/app/arch' scope=both;    
    
System altered.    
    
SQL>     
    
    
SQL> archive log list;    
Database log mode              Archive Mode    
Automatic archival             Enabled    
Archive destination            /u01/app/arch    
Oldest online log sequence     4    
Next log sequence to archive   6    
Current log sequence           6    
SQL>

 

看archive log 目录是否可以自动生成：

SQL> alter system switch logfile;    
alter system switch logfile    
*    
ERROR at line 1:    
ORA-01109: database not open    
    
    
SQL>     
    
    
    
    
SQL> alter database open;    
    
Database altered.    
    
SQL> alter system switch logfile;    
    
System altered.    
    
SQL>     
    
    
    
    
SQL> shutdown immediate;    
Database closed.    
Database dismounted.    
ORACLE instance shut down.    
SQL>     
    
   
SQL>     
SQL> startup;    
ORACLE instance started.    
    
Total System Global Area 1023004672 bytes    
Fixed Size                  2219752 bytes    
Variable Size             624951576 bytes    
Database Buffers          390070272 bytes    
Redo Buffers                5763072 bytes    
Database mounted.    
Database opened.    
SQL> archive log list;    
Database log mode              Archive Mode    
Automatic archival             Enabled    
Archive destination            /u01/app/arch    
Oldest online log sequence     5    
Next log sequence to archive   7    
Current log sequence           7   

    
SQL> !ls -lrt /u01/app    
total 836    
drwxrwx--- 5 oracle oinstall   4096 Mar 13 09:18 oraInventory    
drwxr-xr-x 9 oracle oinstall   4096 Mar 13 09:24 oracle    
-rw-r----- 1 oracle oinstall 840192 Mar 19 14:07 arch1_6_842088414.dbf    
    
SQL> !ls -lrt /u01/app/arch    
ls: /u01/app/arch: No such file or directory    
    
SQL>     
    
必须手动设置：

    
SQL> !ls -lrt /u01/app/arch    
ls: /u01/app/arch: No such file or directory    
    
SQL> !mkdir -p /u01/app/arch             
    
SQL> !ls -lrt /u01/app/arch    
total 0    
    
SQL> !ls -lrt /u01/app    
total 840    
drwxrwx--- 5 oracle oinstall   4096 Mar 13 09:18 oraInventory    
drwxr-xr-x 9 oracle oinstall   4096 Mar 13 09:24 oracle    
-rw-r----- 1 oracle oinstall 840192 Mar 19 14:07 arch1_6_842088414.dbf    
drwxr-xr-x 2 oracle oinstall   4096 Mar 19 14:11 arch    
 

SQL>

本文转自健哥的数据花园博客园博客，原文链接：http://www.cnblogs.com/gaojian/p/3611641.html，如需转载请自行联系原作者