ORA-01105 ORA-01677解决方法和原因

2023-10-05 10:59:28

保障时遇到甲方工程师想修改log_file_name_convert参数并将rac的两个实例循环重启，修改后关闭节点1启动遇到ORA-01105 ORA-01677就起不来，而节点1有业务不能同时关闭

其实只要关闭节点的参数修改成节点2的即可启动，验证过程如下

[oracle@rac2 ~]$ ora

SQL*Plus: Release 11.2.0.4.0 Production on Tue Nov 23 15:44:43 2021

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

SQL> select * from v$asm_diskgroup;

GROUP_NUMBER NAME SECTOR_SIZE BLOCK_SIZE ALLOCATION_UNIT_SIZE STATE TYPE TOTAL_MB FREE_MB HOT_USED_MB COLD_USED_MB REQUIRED_MIRROR_FREE_MB USABLE_FILE_MB
------------ ------------------------------ ----------- ---------- -------------------- ----------- ------ ---------- ---------- ----------- ------------ ----------------------- --------------
OFFLINE_DISKS COMPATIBILITY DATABASE_COMPATIBILITY V
------------- ------------------------------------------------------------ ------------------------------------------------------------ -
1 DATADG 512 4096 4194304 CONNECTED EXTERN 10240 2688 0 7552 0 2688
0 11.2.0.0.0 10.1.0.0.0 N

2 OCRDG 512 4096 1048576 MOUNTED EXTERN 3072 2672 0 400 0 2672
0 11.2.0.0.0 10.1.0.0.0 Y

SQL> show parameter log;

PARAMETER_NAME TYPE VALUE
------------------------------------------------------------ ----------- ----------------------------------------------------------------------------------------------------
audit_syslog_level string
commit_logging string
db_create_online_log_dest_1 string
db_create_online_log_dest_2 string
db_create_online_log_dest_3 string
db_create_online_log_dest_4 string
db_create_online_log_dest_5 string
enable_ddl_logging boolean FALSE
log_archive_config string
log_archive_dest string
log_archive_dest_1 string location=+DATADG
log_archive_dest_10 string
log_archive_dest_11 string
log_archive_dest_12 string
log_archive_dest_13 string
log_archive_dest_14 string
log_archive_dest_15 string
log_archive_dest_16 string
log_archive_dest_17 string
log_archive_dest_18 string
log_archive_dest_19 string
log_archive_dest_2 string
log_archive_dest_20 string
log_archive_dest_21 string
log_archive_dest_22 string
log_archive_dest_23 string
log_archive_dest_24 string
log_archive_dest_25 string
log_archive_dest_26 string
log_archive_dest_27 string
log_archive_dest_28 string
log_archive_dest_29 string
log_archive_dest_3 string
log_archive_dest_30 string
log_archive_dest_31 string
log_archive_dest_4 string
log_archive_dest_5 string
log_archive_dest_6 string
log_archive_dest_7 string
log_archive_dest_8 string
log_archive_dest_9 string
log_archive_dest_state_1 string enable
log_archive_dest_state_10 string enable
log_archive_dest_state_11 string enable
log_archive_dest_state_12 string enable
log_archive_dest_state_13 string enable
log_archive_dest_state_14 string enable
log_archive_dest_state_15 string enable
log_archive_dest_state_16 string enable
log_archive_dest_state_17 string enable
log_archive_dest_state_18 string enable
log_archive_dest_state_19 string enable
log_archive_dest_state_2 string enable
log_archive_dest_state_20 string enable
log_archive_dest_state_21 string enable
log_archive_dest_state_22 string enable
log_archive_dest_state_23 string enable
log_archive_dest_state_24 string enable
log_archive_dest_state_25 string enable
log_archive_dest_state_26 string enable
log_archive_dest_state_27 string enable
log_archive_dest_state_28 string enable
log_archive_dest_state_29 string enable
log_archive_dest_state_3 string enable
log_archive_dest_state_30 string enable
log_archive_dest_state_31 string enable
log_archive_dest_state_4 string enable
log_archive_dest_state_5 string enable
log_archive_dest_state_6 string enable
log_archive_dest_state_7 string enable
log_archive_dest_state_8 string enable
log_archive_dest_state_9 string enable
log_archive_duplex_dest string
log_archive_format string %t_%s_%r.dbf
log_archive_local_first boolean TRUE
log_archive_max_processes integer 4
log_archive_min_succeed_dest integer 1
log_archive_start boolean FALSE
log_archive_trace integer 0
log_buffer integer 6250496
log_checkpoint_interval integer 0
log_checkpoint_timeout integer 1800
log_checkpoints_to_alert boolean FALSE
log_file_name_convert string
remote_login_passwordfile string EXCLUSIVE
sec_case_sensitive_logon boolean TRUE
sec_max_failed_login_attempts integer 10
SQL> show parameter spfile

PARAMETER_NAME TYPE VALUE
------------------------------------------------------------ ----------- ----------------------------------------------------------------------------------------------------
spfile string +DATADG/racdb/spfileracdb.ora
SQL> alter system set log_file_name_convert='+DATADG','+ARCHDG';
alter system set log_file_name_convert='+DATADG','+ARCHDG'
*
ERROR at line 1:
ORA-02095: specified initialization parameter cannot be modified

SQL> alter system set log_file_name_convert='+DATADG','+ARCHDG' scope=spfile;

System altered.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 839282688 bytes
Fixed Size 2257880 bytes
Variable Size 675286056 bytes
Database Buffers 155189248 bytes
Redo Buffers 6549504 bytes
ORA-01105: mount is incompatible with mounts by other instances
ORA-01677: standby file name convert parameters differ from other instance <<<<<

===========inst1

SQL> show parameter log_file_name_convert

PARAMETER_NAME TYPE VALUE
------------------------------------------------------------ ----------- ----------------------------------------------------------------------------------------------------
log_file_name_convert string +DATADG, +ARCHDG
SQL>
=======inst2
SQL> show parameter log_file_name_convert

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

SQL> alter system reset log_file_name_convert scope=spfile;
alter system reset log_file_name_convert scope=spfile
*
ERROR at line 1:
ORA-32010: cannot find entry to delete in SPFILE

SQL> alter system set log_file_name_convert='' scope=spfile;

System altered.

SQL>

SQL> alter system set log_file_name_convert='' scope=spfile;

System altered.

SQL> alter database mount;
alter database mount
*
ERROR at line 1:
ORA-01105: mount is incompatible with mounts by other instances
ORA-01677: standby file name convert parameters differ from other instance

SQL> show parameter log_file_name_convert

SQL> shutdown immediate;
ORA-01507: database not mounted

ORACLE instance shut down.
SQL> startup
ORA-01678: parameter log_file_name_convert must be pairs of pattern and replacement strings
SQL>

ASMCMD> cp spfile.268.1026437239 /home/oracle/
copying +datadg/racdb/parameterfile/spfile.268.1026437239 -> /home/oracle//spfile.268.1026437239
ASMCMD-8016: copy source '+datadg/racdb/parameterfile/spfile.268.1026437239' and target '/home/oracle//spfile.268.1026437239' failed
ORA-19505: failed to identify file "/home/oracle/spfile.268.1026437239"
ORA-27040: file create error, unable to create file
Linux-x86_64 Error: 13: Permission denied
Additional information: 1
ORA-15120: ASM file name '/home/oracle//spfile.268.1026437239' does not begin with the ASM prefix character
ORA-06512: at "SYS.X$DBMS_DISKGROUP", line 415
ORA-06512: at line 3 (DBD ERROR: OCIStmtExecute)
ASMCMD> cp spfile.268.1026437239 /home/grid/
copying +datadg/racdb/parameterfile/spfile.268.1026437239 -> /home/grid//spfile.268.1026437239

[grid@rac1 ~]$ strings spfile.268.1026437239
racdb1.__db_cache_size=218103808
racdb2.__db_cache_size=289406976
racdb1.__java_pool_size=4194304
racdb2.__java_pool_size=4194304
racdb1.__large_pool_size=8388608
racdb2.__large_pool_size=8388608
racdb1.__oracle_base='/oracle/app/oracle'#ORACLE_BASE set from environment
racdb2.__oracle_base='/oracle/app/oracle'#ORACLE_BASE set from environment
racdb1.__pga_aggregate_target=436207616
racdb2.__pga_aggregate_target=339738624
racdb1.__sga_target=406847488
racdb2.__sga_target=503316480
cdb1.__shared_io_pool_size=0
racdb2.__shared_io_pool_size=0
racdb1.__shared_pool_size=163577856
racdb2.__shared_pool_size=188743680
racdb1.__streams_pool_size=0
racdb2.__streams_pool_size=0
*.archive_lag_target=1800
*.audit_file_dest='/oracle/app/oracle/admin/racdb/adump'
*.audit_trail='db'
*.cluster_database=true
*.compatible='11.2.0.4.0'
*.control_files='+DATADG/racdb/controlfile/current.270.1026436209'
*.db_block_size=8192
*.db_create_file_dest='+DATADG'
*.db_name='racdb'
*.db_recovery_file_dest_size=5368709120
*.db_recovery_file_dest='+DATADG'
*.db_securefile='PERMITTED'
*.diagnostic_dest='/oracle/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=racdbXDB)'
*.enable_goldengate_replication=TRUE
racdb1.instance_number=1
racdb2.instance_number=2
*.local_listener='192.168.110.111:1521'
*.log_archive_dest_1='location=+DATADG'
*.log_file_name_convert='' <<<<<<<<<<<<删掉
*.memory_target=839909376
*.open_cursors=300
*.optimizer_features_enable='11.2.0.4'
*.processes=
*.remote_listener='rac-scan:1521'
*.remote_login_passwordfile='exclusive'
racdb1.thread=1
racdb2.thread=2
racdb1.undo_tablespace='UNDOTBS1'
racdb2.undo_tablespace='UNDOTBS2'

[oracle@rac1 ~]$ vim spfile1123.ora
[oracle@rac1 ~]$

起不来，又删掉了一批参数空值
*.db_domain=''
*.optimizer_features_enable='11.2.0.4'
*.processes=
*.remote_listener='rac-scan:1521'
*.remote_login_passwordfile='exclusive'

成功启动
[oracle@rac1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Tue Nov 23 16:33:42 2021
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup mount pfile='/home/oracle/spfile1123.ora';
ORACLE instance started.

Total System Global Area 839282688 bytes
Fixed Size 2257880 bytes
Variable Size 687868968 bytes
Database Buffers 142606336 bytes
Redo Buffers 6549504 bytes
Database mounted.
SQL> show parameter pfile;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string

SQL> alter database open;

Database altered.

============================节点2测试
SQL> show parameter spfile;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string +DATADG/racdb/spfileracdb.ora
SQL> alter system set log_file_name_convert='+DATADG','+ARCHDG' scope=spfile sid='2';

System altered.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORA-01678: parameter log_file_name_convert must be pairs of pattern and replacement strings 起不来了
SQL>

=====================================同上启动2节点
[oracle@rac2 ~]$ vim spfile1123.ora
[oracle@rac2 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Tue Nov 23 16:43:44 2021

Connected to an idle instance.

SQL> startup mount pfile='/home/oracle/spfile1123.ora';
ORACLE instance started.

Total System Global Area 839282688 bytes
Fixed Size 2257880 bytes
Variable Size 616565800 bytes
Database Buffers 213909504 bytes
Redo Buffers 6549504 bytes
Database mounted.
SQL> alter database open;

Database altered.

SQL>
SQL>
SQL>
SQL> show parameter pfile;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string

===后边将spfile恢复到datadg即可

=================上边测试是log_file_name_convert开始不存在参数后来设置了起不来，下边测试下开始有参数，再更改的情况

SQL> show parameter log_file;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_file_name_convert string +DATADG, +ARCHDG

SQL> alter system set log_file_name_convert='+DATADG2','+ARCHDG' scope=spfile; <<<<<实例1更改

System altered.

SQL> SHUTDOWN IMMEDIATE;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 839282688 bytes
Fixed Size 2257880 bytes
Variable Size 687868968 bytes
Database Buffers 142606336 bytes
Redo Buffers 6549504 bytes
ORA-01105: mount is incompatible with mounts by other instances
ORA-01677: standby file name convert parameters differ from other instance <<<<<报错

从asm中cp一份spfile
[oracle@rac1 ~]$ strings /home/grid/spfile11.ora
racdb1.__db_cache_size=222298112
racdb2.__db_cache_size=293601280
racdb2.__java_pool_size=4194304
racdb1.__java_pool_size=4194304
racdb1.__large_pool_size=8388608
racdb2.__large_pool_size=8388608
racdb1.__oracle_base='/oracle/app/oracle'#ORACLE_BASE set from environment
racdb2.__oracle_base='/oracle/app/oracle'#ORACLE_BASE set from environment
racdb2.__pga_aggregate_target=339738624
racdb1.__pga_aggregate_target=436207616
racdb2.__sga_target=503316480
racdb1.__sga_target=406847488
b1.__shared_io_pool_size=0
racdb2.__shared_io_pool_size=0
racdb1.__shared_io_pool_size=0
racdb2.__shared_pool_size=184549376
racdb1.__shared_pool_size=159383552
racdb2.__streams_pool_size=0
racdb1.__streams_pool_size=0
*.archive_lag_target=1800
*.audit_file_dest='/oracle/app/oracle/admin/racdb/adump'
*.audit_trail='db'
*.cluster_database=true
*.compatible='11.2.0.4.0'
*.control_files='+DATADG/racdb/controlfile/current.270.1026436209'
*.db_block_size=8192
*.db_create_file_dest='+DATADG'
*.db_name='racdb'
*.db_recovery_file_dest_size=5368709120
*.db_recovery_file_dest='+DATADG'
*.db_securefile='PERMITTED'
*.diagnostic_dest='/oracle/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=racdbXDB)'
*.enable_goldengate_replication=TRUE
racdb1.instance_number=1
racdb2.instance_number=2
*.local_listener='192.168.110.111:1521'
*.log_archive_dest_1='location=+DATADG'
*.memory_target=839909376
*.open_cursors=300
*.optimizer_features_enable='11.2.0.4'
racdb1.thread=1
racdb2.thread=2
racdb1.undo_tablespace='UNDOTBS1'
racdb2.undo_tablespace='UNDOTBS2'
*.log_file_name_convert='+DATADG2','+ARCHDG' <<<<删掉

====删掉后置空还是无法启动

SQL> shutdown abort;
ORACLE instance shut down.
SQL> startup mount pfile='/home/oracle/spfile11.ora';
ORACLE instance started.

===将问题参数修改成更改前的呢
*.log_file_name_convert='+DATADG','+ARCHDG'

SQL> shutdown abort;
ORACLE instance shut down.
SQL> startup mount pfile='/home/oracle/spfile11.ora';
ORACLE instance started.

Database altered.

所以改问题的关键点就在于找到另一个实例设置的参数是什么，可以从alert上次启动时找到，也可以库里查询查寻，该参数是静态参数，只能修改spfile中的再重启生效

=====如何循环启动实例更改静态参数呢，将两个实例单独分别使用各自的spfile

SQL> startup nomount;
ORACLE instance started.

Total System Global Area 839282688 bytes
Fixed Size 2257880 bytes
Variable Size 687868968 bytes
Database Buffers 142606336 bytes
Redo Buffers 6549504 bytes
SQL> show parameter pfile;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /oracle/app/oracle/product/11.
2.0/db_1/dbs/spfileracdb1.ora
SQL>

SQL> startup
ORACLE instance started.

Total System Global Area 839282688 bytes
Fixed Size 2257880 bytes
Variable Size 616565800 bytes
Database Buffers 213909504 bytes
Redo Buffers 6549504 bytes
Database mounted.
Database opened.
SQL> show parameter pfile;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /oracle/app/oracle/product/11.
2.0/db_1/dbs/spfileracdb2.ora

SQL>
SQL> alter system set log_file_name_convert='+DATADG2','+ARCHDG' scope=spfile;

System altered.

SQL> shutdown abort;
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

原因
===========当ISSYS_MODIFIABLE为false的静态参数不能循环重启实例更改，如出现更改后ORA-01677可参考上边的方法恢复

SQL> select NAME,ISSYS_MODIFIABLE,ISINSTANCE_MODIFIABLE from v$parameter where name='log_file_name_convert';

NAME ISSYS_MOD ISINS
-------------------------------------------------------------------------------- --------- -----
log_file_name_convert FALSE FALSE

SQL> select NAME,ISSYS_MODIFIABLE,isinstance_modifiable from v$parameter where ISSYS_MODIFIABLE='FALSE'
2 ;

NAME ISSYS_MOD ISINS
-------------------------------------------------------------------------------- --------- -----
lock_name_space FALSE FALSE
processes FALSE FALSE
sessions FALSE FALSE
instance_groups FALSE FALSE
event FALSE FALSE
sga_max_size FALSE FALSE
use_large_pages FALSE FALSE
pre_page_sga FALSE FALSE
shared_memory_address FALSE FALSE
hi_shared_memory_address FALSE FALSE
use_indirect_data_buffers FALSE FALSE

NAME ISSYS_MOD ISINS
-------------------------------------------------------------------------------- --------- -----
lock_sga FALSE FALSE
processor_group_name FALSE FALSE
shared_pool_reserved_size FALSE FALSE
java_soft_sessionspace_limit FALSE FALSE
java_max_sessionspace_size FALSE FALSE
instance_type FALSE FALSE
nls_language FALSE FALSE
nls_territory FALSE FALSE
nls_sort FALSE FALSE
nls_date_language FALSE FALSE
nls_date_format FALSE FALSE

NAME ISSYS_MOD ISINS
-------------------------------------------------------------------------------- --------- -----
nls_currency FALSE FALSE
nls_numeric_characters FALSE FALSE
nls_iso_currency FALSE FALSE
nls_calendar FALSE FALSE
nls_time_format FALSE FALSE
nls_timestamp_format FALSE FALSE
nls_time_tz_format FALSE FALSE
nls_timestamp_tz_format FALSE FALSE
nls_dual_currency FALSE FALSE
nls_comp FALSE FALSE
fileio_network_adapters FALSE FALSE

NAME ISSYS_MOD ISINS
-------------------------------------------------------------------------------- --------- -----
filesystemio_options FALSE FALSE
dnfs_batch_size FALSE FALSE
clonedb FALSE FALSE
instant_restore FALSE FALSE
disk_asynch_io FALSE FALSE
tape_asynch_io FALSE FALSE
dbwr_io_slaves FALSE FALSE
cluster_interconnects FALSE FALSE
gcs_server_processes FALSE FALSE
active_instance_count FALSE FALSE
memory_max_target FALSE FALSE

NAME ISSYS_MOD ISINS
-------------------------------------------------------------------------------- --------- -----
control_files FALSE FALSE
db_file_name_convert FALSE FALSE
log_file_name_convert FALSE FALSE
db_block_buffers FALSE FALSE
db_ultra_safe FALSE FALSE
db_block_size FALSE FALSE
db_writer_processes FALSE FALSE
buffer_pool_keep FALSE FALSE
buffer_pool_recycle FALSE FALSE
db_flash_cache_file FALSE FALSE
compatible FALSE FALSE

NAME ISSYS_MOD ISINS
-------------------------------------------------------------------------------- --------- -----
log_archive_start FALSE FALSE
log_archive_format FALSE FALSE
log_buffer FALSE FALSE
db_files FALSE FALSE
read_only_open_delayed FALSE FALSE
cluster_database FALSE FALSE
parallel_server FALSE FALSE
parallel_server_instances FALSE FALSE
cluster_database_instances FALSE FALSE
recovery_parallelism FALSE FALSE
dml_locks FALSE FALSE

NAME ISSYS_MOD ISINS
-------------------------------------------------------------------------------- --------- -----
replication_dependency_tracking FALSE FALSE
transactions FALSE FALSE
transactions_per_rollback_segment FALSE FALSE
rollback_segments FALSE FALSE
undo_management FALSE FALSE
instance_number FALSE FALSE
serial_reuse FALSE FALSE
ldap_directory_sysauth FALSE FALSE
os_roles FALSE FALSE
rdbms_server_dn FALSE FALSE
max_enabled_roles FALSE FALSE

NAME ISSYS_MOD ISINS
-------------------------------------------------------------------------------- --------- -----
remote_os_authent FALSE FALSE
remote_os_roles FALSE FALSE
O7_DICTIONARY_ACCESSIBILITY FALSE FALSE
remote_login_passwordfile FALSE FALSE
audit_sys_operations FALSE FALSE
global_context_pool_size FALSE FALSE
db_domain FALSE FALSE
distributed_lock_timeout FALSE FALSE
commit_point_strength FALSE FALSE
instance_name FALSE FALSE
cursor_space_for_time FALSE FALSE

NAME ISSYS_MOD ISINS
-------------------------------------------------------------------------------- --------- -----
session_cached_cursors FALSE FALSE
utl_file_dir FALSE FALSE
permit_92_wrap_format FALSE FALSE
java_restrict FALSE FALSE
parallel_min_percent FALSE FALSE
create_bitmap_area_size FALSE FALSE
bitmap_merge_area_size FALSE FALSE
parallel_execution_message_size FALSE FALSE
hash_area_size FALSE FALSE
shadow_core_dump FALSE FALSE
background_core_dump FALSE FALSE

NAME ISSYS_MOD ISINS
-------------------------------------------------------------------------------- --------- -----
audit_syslog_level FALSE FALSE
session_max_open_files FALSE FALSE
open_links FALSE FALSE
open_links_per_instance FALSE FALSE
audit_trail FALSE FALSE
db_name FALSE FALSE
db_unique_name FALSE FALSE
ifile FALSE FALSE
os_authent_prefix FALSE FALSE
sql92_security FALSE FALSE
blank_trimming FALSE FALSE

NAME ISSYS_MOD ISINS
-------------------------------------------------------------------------------- --------- -----
parallel_automatic_tuning FALSE FALSE
sec_max_failed_login_attempts FALSE FALSE
sec_return_server_release_banner FALSE FALSE
client_result_cache_size FALSE FALSE
client_result_cache_lag FALSE FALSE
tracefile_identifier FALSE FALSE

116 rows selected.

码农公寓

相关文章