[20201221]spfile设置问题.txt

--//上个星期维护dg遇到的问题，做一个测试模拟生产系统的情况。

1.环境：
SYS@book> @ ver1
PORT_STRING                    VERSION        BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

SYS@book> show parameter log_archive_dest_1
NAME               TYPE   VALUE
------------------ ------ ----------------------------------------------------------------------------------------------------
log_archive_dest_1 string LOCATION=/u01/app/oracle/archivelog/book/ MANDATORY VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=book

SYS@book> show spparameter log_archive_dest_1
SID      NAME               TYPE   VALUE
-------- ------------------ ------ ----------------------------------------------------------------------------------------------------
*        log_archive_dest_1 string LOCATION=/u01/app/oracle/archivelog/book/ MANDATORY VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=book

SYS@book> alter system set log_archive_dest_1='LOCATION=/tmp/book MANDATORY VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=book' sid='book';
System altered.

SYS@book> alter system reset log_archive_dest_1 sid='*';
System altered.

SYS@book> show spparameter log_archive_dest_1
SID      NAME               TYPE   VALUE
-------- ------------------ ------ ----------------------------------------------------------------------------------------------------
book     log_archive_dest_1 string LOCATION=/tmp/book MANDATORY VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=book

--//绕了一大圈,设置参数log_archive_dest_1,但是注意sid=book.

2.测试：

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

SYS@book> startup
ORACLE instance started.
Total System Global Area  643084288 bytes
Fixed Size                  2255872 bytes
Variable Size             205521920 bytes
Database Buffers          427819008 bytes
Redo Buffers                7487488 bytes
Database mounted.
Database opened.

SYS@book> show parameter log_archive_dest_1
NAME               TYPE   VALUE
------------------ ------ ----------------------------------------------------------------------------------------------------
log_archive_dest_1 string LOCATION=/tmp/book MANDATORY VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=book

SYS@book> alter system set log_archive_dest_1='LOCATION=/u01/app/oracle/archivelog/book/ MANDATORY VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=book' ;
System altered.

SYS@book>  show parameter log_archive_dest_1
NAME               TYPE   VALUE
------------------ ------ ----------------------------------------------------------------------------------------------------
log_archive_dest_1 string LOCATION=/u01/app/oracle/archivelog/book/ MANDATORY VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=book

SYS@book> show spparameter log_archive_dest_1
SID      NAME               TYPE   VALUE
-------- ------------------ ------ ----------------------------------------------------------------------------------------------------
book     log_archive_dest_1 string LOCATION=/tmp/book MANDATORY VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=book
*        log_archive_dest_1 string LOCATION=/u01/app/oracle/archivelog/book/ MANDATORY VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=book
--//实际上现在在sid=book,*上都设置了这个参数。实际生效的是sid='*'的情况。
--//如果重启呢？

3.继续：
SYS@book> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SYS@book> startup
ORACLE instance started.
Total System Global Area  643084288 bytes
Fixed Size                  2255872 bytes
Variable Size             205521920 bytes
Database Buffers          427819008 bytes
Redo Buffers                7487488 bytes
Database mounted.
Database opened.

SYS@book> show parameter log_archive_dest_1
NAME               TYPE   VALUE
------------------ ------ -----------------------------------------------------------------------------------
log_archive_dest_1 string LOCATION=/tmp/book MANDATORY VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=book

--//这样重启后sid='book'设置的参数生效，导致生产系统对应的磁盘撑爆。在做系统维护时要注意。

SYS@book> show spparameter log_archive_dest_1
SID      NAME               TYPE   VALUE
-------- ------------------ ------ ----------------------------------------------------------------------------------------------------
book     log_archive_dest_1 string LOCATION=/tmp/book MANDATORY VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=book
*        log_archive_dest_1 string LOCATION=/u01/app/oracle/archivelog/book/ MANDATORY VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=book

--//也就是在维护中注意，我们的问题出现在dg上。许多人在dg上建立dg时，保留了前面的sid部分，或者有一些dba喜欢设置参数加入sid参数，
--//我个人不喜欢这样设置。也就是在设置参数时，注意使用 show spparameter检查设置的具体情况。