情况描述
在开启flashback情况下，默认归档路径为快速恢复区，尝试修改归档路径为指定路径。
初始化环境
SYS@PROD>show parameter recovery

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time integer 7
db_recovery_file_dest string
db_recovery_file_dest_size big integer 0
recovery_parallelism integer 0
SYS@PROD>alter system set db_recovery_file_dest_size=4g;

System altered.

SYS@PROD>alter system set db_recovery_file_dest='/home/oracle/flash';

System altered.

SYS@PROD>shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SYS@PROD>startup mount
ORACLE instance started.

Total System Global Area 419430400 bytes
Fixed Size 1219784 bytes
Variable Size 138412856 bytes
Database Buffers 276824064 bytes
Redo Buffers 2973696 bytes
Database mounted.
SYS@PROD>alter database flashback on；

Database altered.

SYS@PROD>alter database open;

Database altered.

查看归档路径
SYS@PROD>archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 9
Next log sequence to archive 12
Current log sequence 12

我们看到默认归档路径为快速恢复区；现在欲将归档路径改为指定路径：/home/oracle/archive

SYS@PROD>alter system set log_archive_dest_1='/home/oracle/archive';
alter system set log_archive_dest_1='/home/oracle/archive'
*
ERROR at line 1:
ORA-32017: failure in updating SPFILE
ORA-16179: incremental changes to "log_archive_dest_1" not allowed with SPFILE

SYS@PROD>alter system set log_archive_dest_1='/home/oracle/archive' scope=spfile;
alter system set log_archive_dest_1='/home/oracle/archive' scope=spfile
*
ERROR at line 1:
ORA-32017: failure in updating SPFILE
ORA-16179: incremental changes to "log_archive_dest_1" not allowed with SPFILE

操作失败，查看官方文档中的描述：

LOG_ARCHIVE_DEST_n
Property Description
Parameter type String
Syntax LOG_ARCHIVE_DEST_[1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10] =

{ null_string |
{ LOCATION=path_name | SERVICE=service_name }
[ { MANDATORY | OPTIONAL } ]
[ REOPEN[=seconds] ]
[ DELAY[=minutes] ]
[ NOREGISTER ]
[ TEMPLATE=template] ]
[ ALTERNATE=destination ]
[ DEPENDENCY=destination ]
[ MAX_FAILURE=count ]
[ ARCH | LGWR ]
[ SYNC | ASYNC ]
[ AFFIRM | NOAFFIRM ]
[ NET_TIMEOUT=seconds ]
[ VALID_FOR=(redo_log_type,database_role) ]
[ DB_UNIQUE_NAME ]
[ VERIFY ]
}

#

LOCATION=local_disk_directory or USE_DB_RECOVERY_FILE_DEST

Specifies either a local file system destination or the directory, file system, or Oracle Storage Manager disk group that will serve as the flash recovery area. You must specify this attribute for at least one destination. You can specify either a local disk directory or flash recovery area with the LOCATION attribute. You must include either the LOCATION or the SERVICE attribute for each destination to specify where to archive the redo data.

依照官方文档描述，修改sql语句为：

SYS@PROD>alter system set log_archive_dest_1='location=/home/oracle/archive';

System altered.

SYS@PROD>archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /home/oracle/archive
Oldest online log sequence 9
Next log sequence to archive 12
Current log sequence 12

修改完毕；

总结：基本命令不熟悉的话，多看oracle官方文档中的解释是有效的学习方法。

2014-9-16
ocm训练进行时

ITPub博客

【基本操作】快速恢复区存在时修改归档路径

最新文章