oracle12快速恢复区设置多大,【基本操作】快速恢复区存在时修改归档路径

情况描述

在开启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训练进行时