致命的db_unique_name笔误引发的悲剧

故障现象：ORACLE DATAGUARD环境搭建完成后启动主库报告如下错误：

Wed Feb  8 23:12:38 2012

LGWR: Error 16047 creating archivelog file 'standby'

Wed Feb  8 23:12:38 2012

Errors in file /home/oracle/admin/port/bdump/port_lns1_1131.trc:

ORA-16047: DGID mismatch between destination setting and standby

Wed Feb  8 23:12:38 2012

FAL[server, ARC1]: Error 16047 creating remote archivelog file 'standby'

FAL[server, ARC1]: FAL archive failed, see trace file.

Wed Feb  8 23:12:38 2012

Errors in file /home/oracle/admin/port/bdump/port_arc1_1125.trc:

ORA-16055: FAL request rejected

ARCH: FAL archive failed. Archiver continuing

经过分析是主库和从库db_unique_name设置不一致引发，

通过show parameter name发现从库的db_unique_name果然和主库预想值不一致，

要求是standby，而实际是port.

主库的参数设置：

ALTER DATABASE FORCE LOGGING;

ALTER SYSTEM SET DB_UNIQUE_NAME=port scope=spfile;

ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(port,standby)' scope=both;

--主库归档目的地

ALTER SYSTEM SET LOG_ARCHIVE_DEST_1='LOCATION=/home/oracle/archivelog/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=port' scope=both;

--当该库充当主库角色时，设置物理备库redo data的传输目的地

ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=standby LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=standby' scope=spfile;

--许redo传输服务传输数据到目的地，默认是enable

ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_1=ENABLE scope=spfile;

ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE scope=spfile;

--exclusive or shared，所有库sys密码要一致，默认是exclusive

ALTER SYSTEM SET REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE scope=spfile;

--

ALTER SYSTEM SET LOG_ARCHIVE_FORMAT='%t_%s_%r.arc' scope=spfile;

ALTER SYSTEM SET LOG_ARCHIVE_MAX_PROCESSES=4 scope=spfile;

--配置网络服务名，fal_server拷贝丢失的归档文件到这里

ALTER SYSTEM SET FAL_CLIENT=port SCOPE=SPFILE;

--配置网络服务名，假如转换为备库角色时，从这里获取丢失的归档文件

ALTER SYSTEM SET FAL_SERVER=STANDBY SCOPE=SPFILE;

--auto后当主库的datafiles增删时备库也同样自动操作，且会把日志传送到备库standby_archive_dest参数指定的目录下，

--确保该目录存在，如果你的存储采用文件系统没有问题，但是如果采用了裸设备，你就必须将该参数设置为manual

ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO SCOPE=SPFILE; 

--前为切换后的主库路径，后为切换后的备库路径，如果主备库目录结构完全一样，则无需设定

ALTER SYSTEM SET DB_FILE_NAME_CONVERT='/u01/oracle/oradata/port/','/u01/oracle/oradata/port/' SCOPE=SPFILE;

--同上，这两个名字转换参数是主备库的路径映射关系，可能会是路径全名，看情况而定

ALTER SYSTEM SET LOG_FILE_NAME_CONVERT='/u01/oracle/oradata/port/','/u01/oracle/oradata/port/' SCOPE=SPFILE;

--一般和LOG_ARCHIVE_DEST_1的位置一样，如果备库采用ARCH传输方式，那么主库会把归档日志传到该目录下

ALTER SYSTEM SET STANDBY_ARCHIVE_DEST='/home/oracle/archivelog/' scope=spfile;

从库的PFILE设置

port.__db_cache_size=1644167168

port.__java_pool_size=167772160

port.__large_pool_size=16777216

port.__shared_pool_size=301989888

port.__streams_pool_size=0

*.audit_file_dest='/home/oracle/admin/port/adump'

*.background_dump_dest='/home/oracle/admin/port/bdump'

*.compatible='10.2.0.4.0'

*.control_files='/u01/oracle/oradata/port/control01.ctl','/u01/oracle/oradata/port/control02.ctl','/u01/oracle/oradata/port/control03.ctl'

*.core_dump_dest='/home/oracle/admin/port/cdump'

*.db_block_size=8192

*.db_domain=''

*.db_file_multiblock_read_count=32

*.db_file_name_convert='/u01/oracle/oradata/port/','/u01/oracle/oradata/port/'

*.db_name='port'

*.db_unique_name='standby'----------------------------这一句漏了，导致默认成了PORT

*.db_recovery_file_dest='/home/oracle/flash_recovery_area'

*.db_recovery_file_dest_size=2147483648

*.dispatchers='(PROTOCOL=TCP) (SERVICE=portXDB)'

*.java_pool_size=157286400

*.job_queue_processes=10

*.log_archive_config='DG_CONFIG=(port,standby)'

*.log_archive_dest_1='LOCATION=/home/oracle/archivelog valid_for=(all_logfiles,all_roles) db_unique_name=standby'

*.log_archive_dest_2='service=port lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=port'

*.log_archive_dest_state_1='ENABLE'

*.log_archive_dest_state_2='ENABLE'

*.log_archive_max_processes=4

*.log_file_name_convert='/u01/oracle/oradata/port/','/u01/oracle/oradata/port/'

*.open_cursors=450

*.pga_aggregate_target=823132160

*.processes=300

*.remote_login_passwordfile='EXCLUSIVE'

*.sga_target=2147483648

*.shared_pool_size=157286400

*.undo_management='AUTO'

*.undo_tablespace='UNDOTBS1'

*.user_dump_dest='/home/oracle/admin/port/udump'

由于db_unique_name成了'port'导致主库和从库无法对应，主库报如下错误：

处理方法：

修改备库参数文件/home/oracle/product/10.2.0/db_1/dbs/initport.ora后

create pfile = '/home/oracle/product/10.2.0/db_1/dbs/initport.ora' from spfile;

然后启动DG

SQL>shutdown immediate;

SQL>startup nomount;

SQL>alter database mount standby database;

SQL>alter database recover managed standby database disconnect from session;

©著作权归作者所有：来自51CTO博客作者zylhsy的原创作品，如需转载，请注明出处，否则将追究法律责任

职场休闲oracle dataguard ORAORACLE数据库