windows,oracle,dg報錯：ORA-12528，ORA-12154，ORA-10456
最近有需求在windows的2臺oracle服務(wù)器上搭建dg，在過程中遇到了一些錯誤，跟在linux上不一樣
如下：
環(huán)境：11.2.0.4
主庫
ip:192.168.19.197
sid:ORCL
db_unique_name:ORCL
安裝路徑：C:\app\Administrator\product\11.2.0\dbhome_1\
數(shù)據(jù)文件路徑：C:\app\Administrator\oradata\ORCL
本地歸檔路徑：C:\app\Administrator\fast_recovery_area\ORCL\ARCHIVELOG
備庫
ip:192.168.19.194
sid:orclbk
db_unique_name:orclbk
安裝路徑：C:\app\Administrator\product\11.2.0\dbhome_1\
數(shù)據(jù)文件路徑：C:\app\Administrator\oradata\orclbk
本地歸檔路徑：C:\app\Administrator\fast_recovery_area\orclbk\ARCHIVELOG
dg的搭建過程就不詳細(xì)描述，只針對錯誤進(jìn)行處理，總體在windows下搭建dg跟再linux下類似，只有一處，一會回講到
--使用rman?auxiliary
主庫：
SQL> archive log listSQL> shutdown immediateSQL> startup mountSQL> alter database archivelog;SQL> archive log list;SQL> select name , open_mode, log_mode,force_logging from gv$database;SQL> show parameter db_recoverSQL> alter database force logging;SQL> alter system switch logfile;
--添加standby redo log
alter database add standby logfile group 4 ('C:\APP\ADMINISTRATOR\ORADATA\ORCL\REDO04.LOG') size 50m;
--tns監(jiān)聽配置文件
orclbk_192.168.19.194 = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.19.194)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orclbk)
? ? ? (UR=A) ) )orcl_192.168.19.197 = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.19.197)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl) ) )
--修改參數(shù)文件
alter system set log_archive_config='dg_config=(ORCL,orclbk)' scope=spfile sid='*';alter system set log_archive_dest_1='location=C:\app\Administrator\fast_recovery_area\ORCL\ARCHIVELOG valid_for=(all_logfiles,all_roles) db_unique_name=ORCL' scope=spfile sid='*';alter system set log_archive_dest_2='service=orclbk_192.168.19.194 valid_for=(online_logfiles,primary_role) db_unique_name=orclbk' scope=spfile sid='*';alter system set log_archive_dest_state_1=enable scope=spfile sid='*';alter system set log_archive_dest_state_2=enable scope=spfile sid='*';alter system set standby_file_management='auto' scope=spfile sid='*';alter system set fal_server='orclbk_192.168.19.194' scope=spfile sid='*';alter system set fal_client='orcl_192.168.19.197' scope=spfile sid='*';alter system set db_file_name_convert='C:\app\Administrator\oradata\ORCL','C:\app\Administrator\oradata\orclbk' scope=spfile sid='*';alter system set log_file_name_convert='C:\app\Administrator\oradata\ORCL','C:\app\Administrator\oradata\orclbk' scope=spfile sid='*';alter system set log_archive_format='%t_%s_%r.arch' scope=spfile sid='*';### scope=spfile 后面主庫要重啟才生效
備庫
注意：在windows上（linux略過），由于cadbk實(shí)例沒有創(chuàng)建，所以需要ORADIM -NEW -SID cadbk
創(chuàng)建目錄
比如
cd C:\app\Administrator\adminmkdir orclbk
修改備庫參數(shù)文件
*.audit_file_dest='C:\app\Administrator\admin\orclbk\adump'*.audit_trail='db'*.compatible='11.2.0.4.0'*.control_files='C:\app\Administrator\oradata\orclbk\control01.ctl','C:\app\Administrator\fast_recovery_area\orclbk\control02.ctl'*.db_block_size=8192*.db_domain=''*.db_file_name_convert='C:\app\Administrator\oradata\ORCL','C:\app\Administrator\oradata\orclbk'*.db_name='ORCL'*.service_names='orclbk'*.db_unique_name='orclbk'*.db_recovery_file_dest='C:\app\Administrator\fast_recovery_area'*.db_recovery_file_dest_size=4385144832*.diagnostic_dest='C:\app\Administrator'*.dispatchers='(PROTOCOL=TCP) (SERVICE=CADXDB)'*.fal_server='orcl_192.168.19.197'*.fal_client='orclbk_192.168.19.194'*.log_archive_config='dg_config=(ORCL,orclbk)'*.log_archive_dest_1='location=C:\app\Administrator\fast_recovery_area\orclbk\ARCHIVELOG valid_for=(all_logfiles,all_roles) db_unique_name=orclbk'*.log_archive_dest_2='service=cad_192.168.19.197 valid_for=(online_logfiles,primary_role) db_unique_name=ORCL'*.log_archive_dest_state_1='ENABLE'*.log_archive_dest_state_2='ENABLE'*.log_archive_format='%t_%s_%r.arch'*.log_file_name_convert='C:\app\Administrator\oradata\ORCL','C:\app\Administrator\oradata\orclbk'
--備庫tns
orclbk_192.168.19.194 = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.19.194)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orclbk) (UR=A) ) )orcl_192.168.19.197 = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.19.197)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl) ) )
tnsping orcl_192.168.19.197
tnsping orclbk_192.168.19.194
1 報錯ORA-12528: TNS:listener: all appropriate instances are blocking new connections
由于cadbk實(shí)例是手工ORADIM -NEW -SID orclbk命令添加的，在lsnrctl status的時候，狀態(tài)為blocked，數(shù)據(jù)庫實(shí)例orclbk狀態(tài)為nomount，因?yàn)閯討B(tài)監(jiān)聽的問題，對輔助數(shù)據(jù)庫沒法注冊，這里修改為靜態(tài)注冊
或者在tns文件中增加
(UR=A)（主庫備庫的tns對應(yīng)都要增加）
在主備執(zhí)行
sqlplus /nolog
conn sys/*@orcl_192.168.19.197 as sysdba
conn sys/*@orclbk_192.168.19.194 as sysdba
正常就可以
2?
rror 12154 received logging on to the standbyFAL[server, ARC3]: Error 12154 creating remote archivelog file 'orclbk'FAL[server, ARC3]: FAL archive failed, see trace file.ARCH: FAL archive failed. Archiver continuingTue Jun 25 15:47:46 2019Error 12154 received logging on to the standbyPING[ARC1]: Heartbeat failed to connect to standby 'orclbk'. Error is 12154.Tue Jun 25 15:47:50 2019Error 12154 received logging on to the standbyTue Jun 25 15:47:50 2019Error 12154 received logging on to the standbyError 12154 for archive log file 3 to 'orclbk'FAL[server, ARC0]: Error 12154 creating remote archivelog file 'orclbk'FAL[server, ARC0]: FAL archive failed, see trace file.ARCH: FAL archive failed. Archiver continuingErrors in file C:\APP\ADMINISTRATOR\diag\rdbms\orcl\orcl\trace\orcl_nsa2_15224.trc:ORA-12154: TNS:could not resolve the connect identifier specifiedTue Jun 25 15:49:44 2019Tue Jun 25 15:49:48 2019Archived Log entry 17 added for thread 1 sequence 1840 ID 0x5e779e7a dest 1:Tue Jun 25 15:52:09 2019Starting background process SMCOTue Jun 25 15:52:09 2019SMCO started with pid=49, OS id=11840 Tue Jun 25 15:53:42 2019Error 12154 received logging on to the standbyPING[ARC1]: Heartbeat failed to connect to standby 'orclbk'. Error is 12154.Tue Jun 25 15:55:23 2019Tue Jun 25 16:02:00 2019Warning: VKTM detected a time drift.Time drifts can result in an unexpected behavior such as time-outs. Please check trace file for more details.Tue Jun 25 16:02:07 2019Error 12154 received logging on to the standbyPING[ARC1]: Heartbeat failed to connect to standby 'orclbk'. Error is 12154.
查詢主備庫
select message from v$dataguard_status;
select dest_name,error,status from v$archive_dest;
LOG_ARCHIVE_DEST_2 ORA-12154: TNS: 無法解析指定的連接標(biāo)識符 ERROR
主庫報錯，這里參數(shù)LOG_ARCHIVE_DEST_2錯誤，之前的值是cadbk
alter system set log_archive_dest_2='service=orclbk valid_for=(online_logfiles,primary_role) db_unique_name=orclbk' scope=both sid='*';
但是在tns文件中定義的orclbk_192.168.19.194，所以要修改--上面貼出的文件中已修改，所以如果按照上面參數(shù)文件是不會遇到此錯誤的。
alter system set log_archive_dest_2='service=orclbk_192.168.19.194 valid_for=(online_logfiles,primary_role) db_unique_name=orclbk' scope=both sid='*';
alter system set fal_server='orclbk_192.168.19.194' scope=both sid='*';alter system set fal_client='orcl_192.168.19.197' scope=both sid='*';
SQL> select * from v$archive_gap; THREAD# LOW_SEQUENCE# HIGH_SEQUENCE#---------- ------------- -------------- 1 1836 1840SQL> alter database register physical logfile 'C:\app\Administrator\fast_recovery_area\orclbk\ARCHIVELOG\1_1837_976879612.ARCH';
3?
alter database open
ORA-10456 signalled during: alter database open...
SQL> select max(sequence#) from v$archived_log;MAX(SEQUENCE#)-------------- 1842SQL> select DBID,NAME,OPEN_MODE,DATABASE_ROLE from v$database; DBID NAME OPEN_MODE DATABASE_ROLE---------- --------- -------------------- ----------------1584932730 ORCL MOUNTED PHYSICAL STANDBYSQL> alter database open;alter database open*第 1 行出現(xiàn)錯誤:ORA-10456: cannot open standby database; media recovery session may be inprogressSQL> select DBID,NAME,OPEN_MODE,DATABASE_ROLE from v$database; DBID NAME OPEN_MODE DATABASE_ROLE---------- --------- -------------------- ----------------1584932730 ORCL MOUNTED PHYSICAL STANDBYSQL> alter database recover managed standby database cancel;數(shù)據(jù)庫已更改。SQL> alter database open;數(shù)據(jù)庫已更改。SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;數(shù)據(jù)庫已更改。SQL> select DBID,NAME,OPEN_MODE,DATABASE_ROLE from v$database; DBID NAME OPEN_MODE DATABASE_ROLE---------- --------- -------------------- ----------------1584932730 ORCL READ ONLY WITH APPLY PHYSICAL STANDBY來源：https://www.icode9.com/content-2-267651.html
