[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的值。
