线上单实例oracle11g（PD1），已经运行了一段时间，属于单点实例，按照云上一不小心发生的vm文件故障来看，这个很危险，所以需要做高可用。
所以今天在线上刚刚新装了oracle备库实例（PD2），现在需要在PD1和PD2上做一个dataguard用来高可用。PD1为master库，PD2为standby库。
PS：需要注意的是，在安装PD2的时候，备库PD2的各种目录路径都要保持和主库PD1一致。
查看主库状态：
SQL> selectname,open_mode,database_role,log_mode,force_logging from v$database;
NAME OPEN_MODE DATABASE_ROLE LOG_MODE FOR
--------- ------------------------------------ ------------ ---
POWERDES READ WRITE PRIMARY ARCHIVELOG YES
SQL>
#1 如果这里没有开启归档不是ARCHIVELOG，则手动设置归档，归档模式的设置：
1、shutdown abort；
2、alter database mount;
3、alter database archive log；
4、alter database open；
#2 如果这里不是YES是NO，则需要使用命令alter databaseforce logging;强制归档
SQL> show parameter name;
NAME TYPE VALUE
----------------------------------------------- ------------------------------
db_file_name_convert string
db_name string powerdes
db_unique_name string powerdes
global_names boolean FALSE
instance_name string powerdes
lock_name_space string
log_file_name_convert string
service_names string powerdes
SQL>
确保数据库的归档模式：
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 4510
Next log sequence to archive 4512
Current log sequence 4512
SQL>
配置前为两台数据库配置静态监听（因dataguard需要设置db_unique_name，环境配置好后会出现两个不同的instance，如果是动态监听，会默认的监听到db_unique_name的监听上去，导致原来的db_name所在的实例无法连接，因此需要配置好静态监听）
主库监听：
[oracle@plys1 admin]$ more listener.ora # listener.ora Network Configuration File: /oracle/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora # Generated by Oracle configuration tools. # 配置静态监听 SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (SID_NAME = PLSExtProc) (ORACLE_HOME = /oracle/app/oracle/product/11.2.0/dbhome_1) (PROGRAM = extproc) ) (SID_DESC = (SID_NAME = powerdes) # 这里如果GLOBAL_DBNAME和SID_NAME是一样的话，只需要设置下SID_NAME就可以了，否则2个都要设置上。 (ORACLE_HOME = /oracle/app/oracle/product/11.2.0/dbhome_1) ) )
# 本机监听 LISTENER = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.161.3.11)(PORT = 1521)) )
ADR_BASE_LISTENER = /oracle/app/oracle
[oracle@plys1 admin]$ |
从库监听：
[oracle@plys02 admin]$ more listener.ora # listener.ora Network Configuration File: /oracle/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora # Generated by Oracle configuration tools. # 配置静态监听 SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (SID_NAME = PLSExtProc) (ORACLE_HOME = /oracle/app/oracle/product/11.2.0/dbhome_1) (PROGRAM = extproc) ) (SID_DESC =# 这里如果GLOBAL_DBNAME和SID_NAME是一样的话，只需要设置下SID_NAME就可以了，否则2个都要设置上。 (GLOBAL_DBNAME=pdunq_dg) (ORACLE_HOME = /oracle/app/oracle/product/11.2.0/dbhome_1) (SID_NAME = powerdesdg2) ) )
LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.161.3.13)(PORT = 1521)) ) )
ADR_BASE_LISTENER = /oracle/app/oracle
[oracle@plys02 admin]$ |
修改主库的tnsnames.ora配置：
[oracle@plys1 admin]$ vim tnsnames.ora
# tnsnames.ora Network Configuration File: /oracle/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora # Generated by Oracle configuration tools.
POWERDES = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.161.3.11)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = powerdes) ) )
PD1 = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.161.3.11)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = pdunq) ) )
PD2 = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.161.3.13)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = powerdes) ) ) |
传输到备库：
[oracle@plys1 admin]$ scp tnsnames.ora 192.161.3.13:/oracle/app/oracle/product/11.2.0/dbhome_1/network/admin/ The authenticity of host '192.161.3.13 (192.161.3.13)' can't be established. RSA key fingerprint is 67:0f:7d:69:e8:ed:9c:7e:b4:9d:36:35:2f:68:a2:d9. Are you sure you want to continue connecting (yes/no)? yes Warning: Permanently added '192.161.3.13' (RSA) to the list of known hosts. oracle@192.161.3.13's password: tnsnames.ora 100% 697 0.7KB/s 00:00 [oracle@plys1 admin]$ |
# 在主库设定口令文件：
[oracle@plys01dbs]$ orapwd file=orapw$ORACLE_SID password=313pd-ys2037 entries=10 force=yignorecase=Y
[oracle@plys01dbs]$
# 传输到备库
[oracle@plys1 dbs]$ scp orapwpowerdes 192.161.3.13:$ORACLE_HOME/dbs
oracle@192.161.3.13's password:
orapwpowerdes 100% 1536 1.5KB/s 00:00
[oracle@plys1 dbs]$
# 覆盖备库口令文件
[oracle@plys02 admin]$ cd $ORACLE_HOME/dbs
[oracle@plys02 admin]$ cp orapwpowerdes orapwpowerdesdg2
[oracle@plys02 dbs]$ ll ora*
-rw-r----- 1 oracle oinstall 1536 May 2314:37 orapwpowerdes
-rw-r----- 1 oracle oinstall 1536 May 2314:22 orapwpowerdesdg2
[oracle@plys02 dbs]$
# 设置参数 ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(powerdes,pdunq_dg)'; ALTER SYSTEM SET LOG_ARCHIVE_DEST_1='LOCATION=/oracle/app/oracle/flash_recovery_area/archivelog LGWR VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=powerdes'; ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=PD2 ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=pdunq_dg'; ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_1='ENABLE'; ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2='DEFER'; ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT='AUTO'; ALTER SYSTEM SET FAL_SERVER=pdung; ALTER SYSTEM SET DB_FILE_NAME_CONVERT='/home/oradata/powerdes','/home/oradata/powerdes' scope=spfile; ALTER SYSTEM SET LOG_FILE_NAME_CONVERT='/home/oradata/powerdes','/home/oradata/powerdes' scope=spfile;
# 写入启动参数文件 SQL> create pfile from spfile;
File created.
SQL> |
[oracle@plys1 data]$ scp -r 2016-05-20 192.161.3.13:/backupdisk/backup/data/ oracle@192.161.3.13's password: arch_POWERDES_20160520_6050.bak 100% 24MB 12.2MB/s 00:02 full_POWERDES_20160520_6049.bak 100% 10GB 10.1MB/s 17:36 rman_backup.log 100% 60KB 60.1KB/s 00:00 arch_POWERDES_20160520_6048.bak 100% 26MB 13.2MB/s 00:02 [oracle@plys1 data]$ |
修改备库启动参数文件：
cd $ORACLE_HOME/dbs vim initpowerdesdg2.pra
#DG CONFIG *.log_archive_config='dg_config=(powerdes,pdunq_dg)' *.log_archive_dest_1='LOCATION=/oracle/app/oracle/flash_recovery_area/archivelog LGWR VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=pdunq_dg' *.log_archive_dest_2='SERVICE=PD1 ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=powerdes *.standby_file_management='AUTO' *.log_archive_dest_state_1=enable *.log_archive_dest_state_2=enable *.fal_server=pdunq_dg *.db_unique_name=pdunq_dg |
PS:将*.log_archive_dest_2=后面的DB_UNIQUE_NAME改成primary的DB_UNIQUE_NAME值改为powerdes，这样在做switchover的时候，新的primary能通过这个将redo日志传到新的standby上面去。
log_archive_dest_N 目的是告诉数据库，把归档放到那里去可选项，首先是本地，然后考虑远程的从库，所以，假设A是主库，B是从库，切换之后B是主库，A是从库，所以，log_archive_dest_N需要设置为对方
把备库启动到nomount：
SQL> startup nomount; ORACLE instance started.
Total System Global Area 1.1825E+10 bytes Fixed Size 2217024 bytes Variable Size 6039800768 bytes Database Buffers 5771362304 bytes Redo Buffers 11849728 bytes SQL>
|
[oracle@plys1 dbs]$ rman target sys/313pd_ys2016@PD1 auxiliarysys/313pd_ys2016@PD2
Recovery Manager: Release 11.2.0.1.0 -Production on Mon May 23 15:26:17 2016
Copyright (c) 1982, 2009, Oracle and/or itsaffiliates. All rights reserved.
connected to target database: POWERDES(DBID=3391761643)
connected to auxiliary database: POWERDES(not mounted)
RMAN>
命令：duplicatetarget database for standby from active database nofilenamecheck;
开始同步，这里消耗时间比较长，因为需要写入数据文件到备库上面：
RMAN> duplicate target database for standby from active database nofilenamecheck;
Starting Duplicate Db at 23-MAY-16 using target database control file instead of recovery catalog allocated channel: ORA_AUX_DISK_1 channel ORA_AUX_DISK_1: SID=1885 device type=DISK
contents of Memory Script: { backup as copy reuse targetfile '/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/orapwpowerdes' auxiliary format '/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/orapwpowerdesdg2' ; } executing Memory Script
Starting backup at 23-MAY-16 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=4245 device type=DISK Finished backup at 23-MAY-16
contents of Memory Script: { backup as copy current controlfile for standby auxiliary format '/oracle/app/oracle/oradata/powerdes/control01.ctl'; restore clone controlfile to '/oracle/app/oracle/flash_recovery_area/powerdes/control02.ctl' from '/oracle/app/oracle/oradata/powerdes/control01.ctl'; } executing Memory Script
Starting backup at 23-MAY-16 using channel ORA_DISK_1 channel ORA_DISK_1: starting datafile copy copying standby control file output file name=/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/snapcf_powerdes.f tag=TAG20160523T160425 RECID=5 STAMP=912614666 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01 Finished backup at 23-MAY-16
Starting restore at 23-MAY-16 using channel ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: copied control file copy Finished restore at 23-MAY-16
contents of Memory Script: { sql clone 'alter database mount standby database'; } executing Memory Script
sql statement: alter database mount standby database
contents of Memory Script: { set newname for tempfile 1 to "/home/oradata/powerdes/temp01.dbf"; set newname for tempfile 2 to "/home/oradata/powerdes/temp02.dbf"; set newname for tempfile 3 to "/home/oradata/powerdes/temp03.dbf"; set newname for tempfile 4 to "/home/oradata/powerdes/temp04.dbf"; switch clone tempfile all; set newname for datafile 1 to "/home/oradata/powerdes/system01.dbf"; set newname for datafile 2 to "/home/oradata/powerdes/sysaux01.dbf"; set newname for datafile 3 to "/home/oradata/powerdes/undotbs01.dbf"; set newname for datafile 4 to "/home/oradata/powerdes/users01.dbf"; set newname for datafile 5 to "/home/oradata/powerdes/powerdesk01.dbf"; set newname for datafile 6 to "/home/oradata/powerdes/plas01.dbf"; set newname for datafile 7 to "/home/oradata/powerdes/pl01.dbf"; set newname for datafile 8 to "/home/oradata/powerdes/help01.dbf"; set newname for datafile 9 to "/home/oradata/powerdes/adobelc01.dbf"; set newname for datafile 10 to "/home/oradata/powerdes/sms01.dbf"; set newname for datafile 11 to "/home/oradata/powerdes/plcrm01.dbf"; set newname for datafile 12 to "/home/oradata/powerdes/powerdesk02.dbf"; set newname for datafile 13 to "/home/oradata/powerdes/datagm01.dbf"; backup as copy reuse datafile 1 auxiliary format "/home/oradata/powerdes/system01.dbf" datafile 2 auxiliary format "/home/oradata/powerdes/sysaux01.dbf" datafile 3 auxiliary format "/home/oradata/powerdes/undotbs01.dbf" datafile 4 auxiliary format "/home/oradata/powerdes/users01.dbf" datafile 5 auxiliary format "/home/oradata/powerdes/powerdesk01.dbf" datafile 6 auxiliary format "/home/oradata/powerdes/plas01.dbf" datafile 7 auxiliary format "/home/oradata/powerdes/pl01.dbf" datafile 8 auxiliary format "/home/oradata/powerdes/help01.dbf" datafile 9 auxiliary format "/home/oradata/powerdes/adobelc01.dbf" datafile 10 auxiliary format "/home/oradata/powerdes/sms01.dbf" datafile 11 auxiliary format "/home/oradata/powerdes/plcrm01.dbf" datafile 12 auxiliary format "/home/oradata/powerdes/powerdesk02.dbf" datafile 13 auxiliary format "/home/oradata/powerdes/datagm01.dbf" ; sql 'alter system archive log current'; } executing Memory Script
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
renamed tempfile 1 to /home/oradata/powerdes/temp01.dbf in control file renamed tempfile 2 to /home/oradata/powerdes/temp02.dbf in control file renamed tempfile 3 to /home/oradata/powerdes/temp03.dbf in control file renamed tempfile 4 to /home/oradata/powerdes/temp04.dbf in control file
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting backup at 23-MAY-16 using channel ORA_DISK_1 channel ORA_DISK_1: starting datafile copy input datafile file number=00005 name=/home/oradata/powerdes/powerdesk01.dbf output file name=/home/oradata/powerdes/powerdesk01.dbf tag=TAG20160523T160433 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:08:35 channel ORA_DISK_1: starting datafile copy input datafile file number=00006 name=/home/oradata/powerdes/plas01.dbf output file name=/home/oradata/powerdes/plas01.dbf tag=TAG20160523T160433 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:02:15 channel ORA_DISK_1: starting datafile copy input datafile file number=00012 name=/home/oradata/powerdes/powerdesk02.dbf output file name=/home/oradata/powerdes/powerdesk02.dbf tag=TAG20160523T160433 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:55 channel ORA_DISK_1: starting datafile copy input datafile file number=00003 name=/home/oradata/powerdes/undotbs01.dbf output file name=/home/oradata/powerdes/undotbs01.dbf tag=TAG20160523T160433 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:55 channel ORA_DISK_1: starting datafile copy input datafile file number=00002 name=/home/oradata/powerdes/sysaux01.dbf output file name=/home/oradata/powerdes/sysaux01.dbf tag=TAG20160523T160433 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:25 channel ORA_DISK_1: starting datafile copy input datafile file number=00001 name=/home/oradata/powerdes/system01.dbf output file name=/home/oradata/powerdes/system01.dbf tag=TAG20160523T160433 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:15 channel ORA_DISK_1: starting datafile copy input datafile file number=00011 name=/home/oradata/powerdes/plcrm01.dbf output file name=/home/oradata/powerdes/plcrm01.dbf tag=TAG20160523T160433 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:45 channel ORA_DISK_1: starting datafile copy input datafile file number=00007 name=/home/oradata/powerdes/pl01.dbf output file name=/home/oradata/powerdes/pl01.dbf tag=TAG20160523T160433 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:45 channel ORA_DISK_1: starting datafile copy input datafile file number=00004 name=/home/oradata/powerdes/users01.dbf output file name=/home/oradata/powerdes/users01.dbf tag=TAG20160523T160433 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15 channel ORA_DISK_1: starting datafile copy input datafile file number=00010 name=/home/oradata/powerdes/sms01.dbf output file name=/home/oradata/powerdes/sms01.dbf tag=TAG20160523T160433 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07 channel ORA_DISK_1: starting datafile copy input datafile file number=00008 name=/home/oradata/powerdes/help01.dbf output file name=/home/oradata/powerdes/help01.dbf tag=TAG20160523T160433 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07 channel ORA_DISK_1: starting datafile copy input datafile file number=00009 name=/home/oradata/powerdes/adobelc01.dbf output file name=/home/oradata/powerdes/adobelc01.dbf tag=TAG20160523T160433 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07 channel ORA_DISK_1: starting datafile copy input datafile file number=00013 name=/home/oradata/powerdes/datagm01.dbf output file name=/home/oradata/powerdes/datagm01.dbf tag=TAG20160523T160433 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03 Finished backup at 23-MAY-16
sql statement: alter system archive log current
contents of Memory Script: { switch clone datafile all; } executing Memory Script
datafile 1 switched to datafile copy input datafile copy RECID=5 STAMP=912615845 file name=/home/oradata/powerdes/system01.dbf datafile 2 switched to datafile copy input datafile copy RECID=6 STAMP=912615845 file name=/home/oradata/powerdes/sysaux01.dbf datafile 3 switched to datafile copy input datafile copy RECID=7 STAMP=912615845 file name=/home/oradata/powerdes/undotbs01.dbf datafile 4 switched to datafile copy input datafile copy RECID=8 STAMP=912615845 file name=/home/oradata/powerdes/users01.dbf datafile 5 switched to datafile copy input datafile copy RECID=9 STAMP=912615845 file name=/home/oradata/powerdes/powerdesk01.dbf datafile 6 switched to datafile copy input datafile copy RECID=10 STAMP=912615845 file name=/home/oradata/powerdes/plas01.dbf datafile 7 switched to datafile copy input datafile copy RECID=11 STAMP=912615845 file name=/home/oradata/powerdes/pl01.dbf datafile 8 switched to datafile copy input datafile copy RECID=12 STAMP=912615845 file name=/home/oradata/powerdes/help01.dbf datafile 9 switched to datafile copy input datafile copy RECID=13 STAMP=912615845 file name=/home/oradata/powerdes/adobelc01.dbf datafile 10 switched to datafile copy input datafile copy RECID=14 STAMP=912615845 file name=/home/oradata/powerdes/sms01.dbf datafile 11 switched to datafile copy input datafile copy RECID=15 STAMP=912615845 file name=/home/oradata/powerdes/plcrm01.dbf datafile 12 switched to datafile copy input datafile copy RECID=16 STAMP=912615845 file name=/home/oradata/powerdes/powerdesk02.dbf datafile 13 switched to datafile copy input datafile copy RECID=17 STAMP=912615845 file name=/home/oradata/powerdes/datagm01.dbf Finished Duplicate Db at 23-MAY-16
RMAN> |
# alter system setlog_archive_dest_state_2='enable';
SQL> alter system setlog_archive_dest_state_2='enable';
System altered.
SQL>
# 在主库备库上执行： alter database add standby logfile group 4 ('/home/oradata/powerdes/redo_dg_021.log') size 200M; alter database add standby logfile group 5 ('/home/oradata/powerdes/redo_dg_022.log') size 200M; alter database add standby logfile group 6 ('/home/oradata/powerdes/redo_dg_023.log') size 200M;
# 在主库备库上查看： SQL> select member from v$logfile;
MEMBER -------------------------------------------------------------------------------- /home/oradata/powerdes/redo03.log /home/oradata/powerdes/redo02.log /home/oradata/powerdes/redo01.log /home/oradata/powerdes/redo_dg_021.log /home/oradata/powerdes/redo_dg_022.log /home/oradata/powerdes/redo_dg_023.log
6 rows selected.
SQL> SQL> SELECT GROUP#, BYTES FROM V$STANDBY_LOG;
GROUP# BYTES ---------- ---------- 4 52428800 5 52428800 6 52428800
SQL> |
启动standby的redo应用的两种方式：
（1）默认的物理DG启动应用后，在主库arch日志被完整写入后才会开始应用该arch log
SQL> alter database recover managedstandby database disconnect from session;
（2）可以添加current logfile参数，使得应用当前正在读写，还没有完成归档的日志
SQL> alter database recover managedstandby database using current logfile disconnect from session;
（3）开启多个并行度提高应用效率
SQL> alter database recover managedstandby database parallel 8 using current logfile disconnect from session;
（4）关闭REDO应用
SQL> alter database recover managedstandby database using current logfile disconnect from session nodelay;
（5）取消延时应用
SQL> alter database recover managedstandby database cancel;
我们这里采用第二种，可以实时在备库拿到最新记录数据：
SQL> alter database recover managed standby database using current logfile disconnect from session;
Database altered.
SQL> |
# 在mount状态应用
SQL> alter database recover managedstandby database using current logfile disconnect from session;
Database altered.
# 取消延时应用
SQL> alter database recover managedstandby database cancel;
Database altered.
# 打开库
SQL> alter database open;
Database altered.
# 再次应用redo，添加current logfile参数，使得应用当前正在读写，还没有完成归档的日志
SQL> alter database recover managedstandby database using current logfile disconnect from session;
Database altered.
SQL>
# 查看备库状态
SQL> selectgroup#,thread#,sequence#,archived,status from v$standby_log;
GROUP# THREAD# SEQUENCE# ARC STATUS
---------- ---------- ---------- -------------
4 1 4729 YES ACTIVE
5 0 0 YES UNASSIGNED
6 0 0 YES UNASSIGNED
SQL>
# 去检查主库备库的当前归档日志, archive log list;
SQL> archive log list; Database log mode Archive Mode Automatic archival Enabled Archive destination /oracle/app/oracle/flash_recovery_area/archivelog Oldest online log sequence 4727 Next log sequence to archive 4729 Current log sequence 4729 SQL>
|
# 去查下备库standby是否已经完全应用了：
SQL> select sequence#,applied from v$archived_log order by sequence# asc; SEQUENCE# APPLIED ---------- --------- 4722 YES 4723 YES 4724 YES 4725 YES 4726 YES 4727 YES 4728 YES 7 rows selected. SQL> |
OK，发现传输过来的日志已经被应用了，主库备库保持一致，至此在线上由单实例扩展而成dataguard高可用就成功做完了。
备库上是：
SQL> select member from v$logfile;
MEMBER -------------------------------------------------------------------------------- /oracle/app/oracle/flash_recovery_area/PDUNQ_DG/onlinelog/o1_mf_3_cn5hk8b1_.log /oracle/app/oracle/flash_recovery_area/PDUNQ_DG/onlinelog/o1_mf_2_cn5hk7cl_.log /oracle/app/oracle/flash_recovery_area/PDUNQ_DG/onlinelog/o1_mf_1_cn5hk6db_.log
|
Errors in file/oracle/app/oracle/diag/rdbms/powerdes/powerdes/trace/powerdes_arc1_2626.trc:
ORA-16057: server not in Data Guardconfiguration
PING[ARC1]: Heartbeat failed to connect tostandby 'PD2'. Error is 16057.
在主库上查看db_unique_name：
SQL> show parameter db_unique_name;
NAME TYPE VALUE
----------------------------------------------- ------------------------------
db_unique_name string powerdes
SQL>
Ok，重新设置LOG_ARCHIVE_CONFIG，设置前面的为powerdes，
ALTER SYSTEM SETLOG_ARCHIVE_CONFIG='DG_CONFIG=(powerdes,pdunq_dg)';
参考：http://czmmiao.iteye.com/blog/1311070
看到alert日志还在报新的错误：ORA-16057: server not in Data Guard configuration
Errors in file/oracle/app/oracle/diag/rdbms/powerdes/powerdes/trace/powerdes_arc1_2626.trc:
ORA-16057: server not in Data Guardconfiguration
PING[ARC1]: Heartbeat failed to connect tostandby 'PD2'. Error is 16057.
Mon May 23 18:43:06 2016
解决方法：
在primary 主库上：重启下到备库的连接就好了：
ALTER SYSTEM SETlog_archive_dest_state_2='DEFER' SCOPE=BOTH;
ALTER SYSTEM SETlog_archive_dest_state_2='ENABLE' SCOPE=BOTH;
PS：这个是oracle 的一个bug
在备库启动到nomount后用tns测试连接时发现数无法连接，报错信息如下：
[oracle@plys1 admin]$ rman target sys/313pd_ys2016@PD1 auxiliarysys/313pd_ys2016@PD2
Recovery Manager: Release 11.2.0.1.0 -Production on Mon May 23 14:32:38 2016
Copyright (c) 1982, 2009, Oracle and/or itsaffiliates. All rights reserved.
connected to target database: POWERDES(DBID=3391761643)
RMAN-00571:===========================================================
RMAN-00569: =============== ERROR MESSAGESTACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00554: initialization of internalrecovery manager package failed
RMAN-04006: error from auxiliary database:ORA-01031: insufficient privileges
[oracle@plys1 admin]$
原因是11g之后动态监听不支持在nomount状态下远程的tns访问，自己的服务器中配置的监听一直是动态的，然后需要设置好静态注册，在listenr.ora文件里面。之后在登录连接就正常了。
参考：http://blog.itpub.net/12679300/viewspace-1127775/
参考文章、SID各种NAME的名称：http://blog.itpub.net/7590112/viewspace-1064826/
参考文章、静态注册动态注册：http://blog.itpub.net/7590112/viewspace-1064826/