[20141225]ORA-1105.txt

--前一阵子，服务器内存耗尽重启，数据库无法启动，查看alert*.log文件,出现如下提示：

Wed Nov 19 10:52:42 2014
ALTER SYSTEM SET local_listener=' (ADDRESS=(PROTOCOL=TCP)(HOST=192.168.100.104)(PORT=1521))' SCOPE=MEMORY SID='dben1';
ALTER DATABASE MOUNT /* db agent *//* {1:1897:2} */
ORA-1105 signalled during: ALTER DATABASE MOUNT /* db agent *//* {1:1897:2} */...
Wed Nov 19 10:52:46 2014
Shutting down instance (abort)

$ oerr ora 1150
01150, 00000, "cannot prevent writes - file %s has online backup set"
// *Cause: An attempt to make a tablespace read only or offline normal found
//          that an online backup is still in progress. It will be necessary
//          to write the file header to end the backup, but that would not
//          be allowed if this command succeeded.
// *Action: End the backup of the offending tablespace and retry this command.

--出现这种情况知道建立dataguard时，修改这些参数仅仅修改spfile，这样启动后出现不一致。
SYS@dben1> ALTER SYSTEM SET db_file_name_convert='/u01/app/oracle/oradata/dbendg/','+datac1/dben/' scope=both;
ALTER SYSTEM SET db_file_name_convert='/u01/app/oracle/oradata/dbendg/','+datac1/dben/' scope=both
                 *
ERROR at line 1:
ORA-02096: specified initialization parameter is not modifiable with this option

ALTER SYSTEM SET db_file_name_convert='/u01/app/oracle/oradata/dbendg/','+datac1/dben/' SCOPE=SPFILE;
ALTER SYSTEM SET log_file_name_convert='/u01/app/oracle/oradata/dbendg/','+datac1/dben/' SCOPE=SPFILE;

SYS@dben1> show parameter convert

NAME                    TYPE     VALUE
----------------------- -------- ---------
db_file_name_convert    string
log_file_name_convert   string

ALTER SYSTEM RESET db_file_name_convert SCOPE=SPFILE;
ALTER SYSTEM RESET log_file_name_convert SCOPE=SPFILE;

--这两个参数都要修改spfile文件。先取消设置，启动就ok了。特此做一个记录。

--以上是我知道自己修改了那些参数，但是在其他情况下如何知道spfile与现在的情况不一致呢?

SELECT name, VALUE FROM V$SPPARAMETER WHERE VALUE IS NOT NULL
MINUS
SELECT name, VALUE FROM v$parameter2  WHERE VALUE IS NOT NULL;

NAME                 VALUE
-------------------- ------------------------------
db_cache_size        440401920

SYS@test> show parameter db_cache_size
NAME           TYPE         VALUE
-------------- ------------ ------
db_cache_size  big integer  432M

-- 写法问题，实际上两者是一致的。修改一个参数db_file_multiblock_read_count到spfile看看。

SYS@test> show parameter db_file_multiblock_read_count
NAME                           TYPE     VALUE
------------------------------ -------- --------
db_file_multiblock_read_count  integer  8

SYS@test> alter system set db_file_multiblock_read_count=16 scope=spfile ;
System altered.

SELECT name, VALUE FROM V$SPPARAMETER WHERE VALUE IS NOT NULL
MINUS
SELECT name, VALUE FROM v$parameter2  WHERE VALUE IS NOT NULL;

NAME                           VALUE
------------------------------ ------------------------------
db_cache_size                  440401920
db_file_multiblock_read_count  16

--不是很完美，但是还是基本可以确定问题的。

--补充测试，拿上面的脚本在服务器测试，换成gv视图:

SELECT INST_ID,name, VALUE FROM gV$SPPARAMETER WHERE VALUE IS NOT NULL
MINUS
SELECT INST_ID,name, VALUE FROM gv$parameter2  WHERE VALUE IS NOT NULL;

   INST_ID NAME                           VALUE
---------- ------------------------------ --------------------------------------------------
         1 audit_trail                    db
         1 cluster_database               true
         1 instance_number                2
         1 remote_login_passwordfile      exclusive
         1 sessions                       8000
         1 sga_target                     44023414784
         1 thread                         2
         1 undo_tablespace                UNDOTBS2
         2 audit_trail                    db
         2 cluster_database               true
         2 instance_number                1
         2 remote_login_passwordfile      exclusive
         2 sessions                       8000
         2 thread                         1
         2 undo_tablespace                UNDOTBS1

17 rows selected.

--我看了一下，一些是大小写问题，另外一些是单位问题。undo_tablespace，thread，instance 比较特殊，自己看看。

SYS@dben1> SELECT inst_id,sid,name,value FROM gV$SPPARAMETER WHERE VALUE IS NOT NULL and name like 'undo_tablespace' and inst_id=1 and sid='dben2';
INST_ID SID     NAME              VALUE
-------- ------- ----------------- --------------------------------------------------
       1 dben2   undo_tablespace   UNDOTBS2

--实例1要知道节点2的undo_tablespace的值。