Oracle数据库的非归档模式迁移到归档模式

浏览数：123 / 时间：2015年06月12日

先观察当前的状态：

[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> 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 /u10/app/
ls: /u10/app/: No such file or directory

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
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> !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>

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