azure云上 在线将oracle单实例扩展成oracle dataguard高可用集群的详细过程
来源:互联网 发布:排八字软件 编辑:程序博客网 时间:2024/04/30 10:11
<->环境准备
线上单实例oracle11g(PD1),已经运行了一段时间,属于单点实例,按照云上一不小心发生的vm文件故障来看,这个很危险,所以需要做高可用。
所以今天在线上刚刚新装了oracle备库实例(PD2),现在需要在PD1和PD2上做一个dataguard用来高可用。PD1为master库,PD2为standby库。
PS:需要注意的是,在安装PD2的时候,备库PD2的各种目录路径都要保持和主库PD1一致。
1,确保主库状态
查看主库状态:
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>
2,配置监听、口令,传输到备库
2.1 listenor.ora文件配置静态监听
配置前为两台数据库配置静态监听(因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]$
2.2 tnsnames.ora配置复制服务
修改主库的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]$
2.3传输口令文件到备库
# 在主库设定口令文件:
[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]$
3,在线设置主库参数
# 设置参数
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>
4,把主库最近的备份传输到备库同样的目录之上
[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]$
5,修改备库参数并启动到nomount
修改备库启动参数文件:
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>
6,在master库PD1上使用auxiliary启动连接
[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>
7,使用duplicate target 进行数据复制
命令: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>
8,将主库的LOG_ARCHIVE_DEST_STATE_2设置为ENABLE
# alter system setlog_archive_dest_state_2='enable';
SQL> alter system setlog_archive_dest_state_2='enable';
System altered.
SQL>
9,在主库备库上添加standby文件
# 在主库备库上执行:
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>
10,准备开启备库redo应用
启动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>
11,打开备库
# 在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高可用就成功做完了。
12,问题记录redo log路径不一致
备库上是:
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
13,问题记录ORA-16057
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
14,问题记录RMAN-04006: error from auxiliary database
在备库启动到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/
- azure云上 在线将oracle单实例扩展成oracle dataguard高可用集群的详细过程
- zabbix3.0.2 监控oracle高可用dataguard的standby库状态详细过程
- ORACLE 11g 高可用Data Guard在线降级详细过程
- ORACLE11G 将dataguard的rman备份恢复到测试环境的单机oracle中的详细过程
- oracle 单实例+文件管理 +dataguard ---------------------》》 oracle rac+asm+ dataguard
- azure 云上 oracle11.2.0.4里dataguard归档日志传输 1034 问题详细解决过程
- oracle rac与oracle单实例做dataguard
- 利用Oracle CRS搭建应用的高可用集群
- oracle的physical dataguard的配置过程
- ORACLE 11g rac+单实例数据库构建dataguard
- Oracle 11G 通过 duplicate 配置单实例DataGuard
- oracle单实例通过dataguard 迁移到RAC
- Oracle 11G DataGuard生产环境重新启动详细过程
- Oracle 11G DataGuard ORA-16086问题修复详细过程
- Oracle 11G DataGuard重启详细过程~~
- 主库为oracle 10G ASM RAC,备库为单实例ASM的DATAGUARD
- 搭建ORACLE高可用 高性能 高扩展的 MMM_APE 架构
- oracle11g如何将dataguard的主库数据恢复到oracle单机实例?
- 一个完整系统的测试过程
- Java 单例
- 飛飛(四十五)动物的叫声
- leetcode 318. Maximum Product of Word Lengths-最长单词积|位运算
- View的三大流程之View的测量
- azure云上 在线将oracle单实例扩展成oracle dataguard高可用集群的详细过程
- MySQL学习
- Android6.0 权限申请封装
- Linux:内核模块实现替换系统调用的简单例子
- C# 调用TTS 并输出WAV
- cookie案例-显示用户上次访问网站的时间
- 垃圾收集器与内存分配策略
- 滴滴打车2015-2016
- Android 5.x(一) 新控件之RecyclerView,CardView,Palette的使用