[20201221]spfile設定問題.txt

[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檢查設定的具體情況。