[20170123]db_unique_name与大小写2.txt

[20170123]db_unique_name与大小写2.txt

--上个星期测试了db_unique_name的大小写问题,链接http://blog.itpub.net/267265/viewspace-2132745/
--想起来我的测试参数ORACLE_SID=book,使用小写,换成大写测试看看.

1.环境:
SCOTT@book> @ &r/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> alter system reset db_unique_name;
System altered.

--关闭数据库,修改参数ORACLE_SID=BOOK(大写).

$ cd  /u01/app/oracle/product/11.2.0.4/dbhome_1/dbs
$ cp spfilebook.ora spfileBOOK.ora
$ cp orapwbook orapwBOOK

--//spfile 文件中不设置db_unique_name参数.

2.重启:
$ export ORACLE_SID=BOOK

SYS@BOOK> startup
ORACLE instance started.

Total System Global Area  634732544 bytes
Fixed Size                  2255792 bytes
Variable Size             197133392 bytes
Database Buffers          427819008 bytes
Redo Buffers                7524352 bytes
Database mounted.
Database opened.

$ strings spfileBOOK.ora | grep -i db_unique_name
*.log_archive_dest_1='LOCATION=/u01/app/oracle/archivelog/book MANDATORY VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=book'
*.log_archive_dest_2='SERVICE=bookdg LGWR ASYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=bookdg'
--//没有配置db_unique_name参数.

SYS@BOOK> show parameter db_unique_name
NAME           TYPE    VALUE
-------------- ------- -------
db_unique_name string  book

--// db_unique_name 缺省是小写.

SYS@BOOK> show parameter dump
NAME                 TYPE    VALUE
-------------------- ------- ------------------------------------------
background_core_dump string  partial
background_dump_dest string  /u01/app/oracle/diag/rdbms/book/BOOK/trace
core_dump_dest       string  /u01/app/oracle/diag/rdbms/book/BOOK/cdump
max_dump_file_size   string  unlimited
shadow_core_dump     string  partial
user_dump_dest       string  /u01/app/oracle/diag/rdbms/book/BOOK/trace

--//可以发现*dump的路径发生了变量.也再次证明了跟踪文件位置是
{ORACLE_BASE}/diag/rdbms/{DB_UNIQUE_NAME}/{SID}/trace

--//而且{DB_UNIQUE_NAME}选择的是小写.(根据前面链接的测试:http://blog.itpub.net/267265/viewspace-2132745/)

3.设置参数DB_UNIQUE_NAME看看.
SYS@BOOK> alter system set db_unique_name=book scope=spfile;
System altered.

$ strings spfileBOOK.ora | grep -i db_unique_name
*.db_unique_name='BOOK'
*.log_archive_dest_1='LOCATION=/u01/app/oracle/archivelog/book MANDATORY VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=book'
*.log_archive_dest_2='SERVICE=bookdg LGWR ASYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=bookdg'

--//这样设置记录的是大写.重启看看:

SYS@BOOK> show parameter db_unique_name
NAME            TYPE    VALUE
--------------- ------- -------
db_unique_name  string  BOOK

SYS@BOOK> show parameter dump
NAME                 TYPE   VALUE
-------------------- ------ ------------------------------------------
background_core_dump string partial
background_dump_dest string /u01/app/oracle/diag/rdbms/book/BOOK/trace
core_dump_dest       string /u01/app/oracle/diag/rdbms/book/BOOK/cdump
max_dump_file_size   string unlimited
shadow_core_dump     string partial
user_dump_dest       string /u01/app/oracle/diag/rdbms/book/BOOK/trace

总结:
1.db_unique_name 缺省是小写. 不管ORACLE_SID是大写还是小写.
2.如果仅仅简单执行:
alter system set db_unique_name=book scope=spfile;
alter system set db_unique_name=booK scope=spfile;
--spfile文件记录的都是大写,没有引号:
*.db_unique_name='BOOK'

--如果小写,要加引号:
alter system set db_unique_name=book scope=spfile;

3.跟踪文件位置是准确的路径是:
{ORACLE_BASE}/diag/rdbms/lower({DB_UNIQUE_NAME})/{SID}/trace.

4.oracle 有时候就是乱.我的测试不会这个参数大小写影响dg的日志应用.(这个过程略).
SYS@bookdg> @ &r/dg_status
NAME                           VALUE                          UNIT                           TIME_COMPUTED
------------------------------ ------------------------------ ------------------------------ ------------------------------
transport lag                  +00 00:00:00                   day(2) to second(0) interval   2017-01-23 16:15:51
apply lag                      +00 00:00:00                   day(2) to second(0) interval   2017-01-23 16:15:51
apply finish time              +00 00:00:00.000               day(2) to second(3) interval   2017-01-23 16:15:51
estimated startup time         7                              second                         2017-01-23 16:15:51

SYS@bookdg> @ &r/dg
PROCESS       PID STATUS       GROUP#  THREAD#  SEQUENCE#     BLOCK#     BLOCKS DELAY_MINS
--------- ------- ------------ ------- ------- ---------- ---------- ---------- ----------
RFS         15529 IDLE         N/A           0          0          0          0          0
RFS         15527 IDLE         3             1        214        427          1          0
ARCH         7869 CLOSING      6             1        213          1         91          0
MRP0         7937 APPLYING_LOG N/A           1        214        427     102400          0