【基本操作】快速恢復區存在時修改歸檔路徑

GM_DBA發表於2014-09-16
情況描述
在開啟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部落格 ” ，連結：http://blog.itpub.net/29319205/viewspace-1270801/，如需轉載，請註明出處，否則將追究法律責任。

相關文章