背景:
马里现场有现成的dataguard环境(生产和容灾均为rac one node,asm存储),但是生产端需要现有的主机和村存储资源重建为CDAS环境,需要铲掉pr站点。但是单个容灾站点运行有风险,客户提出先建一主二备(一主一备在生产端,另一备在容灾端)。
一、新备库环境准备
生产站点主机安装操作系统并按照集成文档优化配置,安装同版本数据库(无grid),安装同版本psu,dbca建同名库(文件系统存储)
二、数据库信息
db_unique_name tnsname 数据文件路径 日志文件路径 ip 监听端口 备注
主库 pr_cc pr_cc /oradata/dgtests/ /oradata/dgtests/ 10.45.53.31 1522 文件系统
备库1 dr_cc dr_cc /oradata/dr_cc/ /oradata/dr_cc/ 10.45.53.30 1524 文件系统
备库2 dr_cc2 dr_cc2 +DATA/dgtest2/ +DATA/dgtest2/ 172.16.23.170 1524 asm存储
三、搭建过程
1、修改主库设置
su - oracle
export ORACLE_SID=cc
$ORACLE_HOME/network/admin/tnsnames.ora增加:
dr_cc2 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL= TCP)(HOST = 172.16.23.170)(PORT = 1524))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME =dr_cc2)
)
)
sqlplus / as sysdba
ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(pr_cc,dr_cc,dr_cc2)' scope=spfile;
ALTER SYSTEM SET LOG_ARCHIVE_DEST_3='SERVICE=dr_cc2 LGWR ASYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=dr_cc2' scope=spfile;
ALTER SYSTEM SET db_file_name_convert='/oradata/dr_cc/','/oradata/dgtests/','+DATA/dgtest2/','/oradata/dgtests/' scope=spfile;
ALTER SYSTEM SET log_file_name_convert='/oradata/dr_cc/','/oradata/dgtests/','+DATA/dgtest2/','/oradata/dgtests/' scope=spfile;
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_1=ENABLE scope=spfile;
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE scope=spfile;
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_3=ENABLE scope=spfile;
ALTER SYSTEM SET FAL_SERVER = 'dr_cc','dr_cc2' scope=spfile;
2、修改备库1设置:
su - oracle
export ORACLE_SID=cc
$ORACLE_HOME/network/admin/tnsnames.ora增加:
dr_cc2 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL= TCP)(HOST = 172.16.23.170)(PORT = 1524))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME =dr_cc2)
)
)
sqlplus / as sysdba
ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(pr_cc,dr_cc,dr_cc2)' scope=spfile;
ALTER SYSTEM SET LOG_ARCHIVE_DEST_3='SERVICE=dr_cc2 LGWR ASYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=dr_cc2' scope=spfile;
ALTER SYSTEM SET db_file_name_convert='/oradata/dgtests/','/oradata/dr_cc/','+DATA/dgtest2/','/oradata/dr_cc/' scope=spfile;
ALTER SYSTEM SET log_file_name_convert='/oradata/dgtests/','/oradata/dr_cc/','+DATA/dgtest2/','/oradata/dr_cc/' scope=spfile;
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_1=ENABLE scope=spfile;
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE scope=spfile;
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_3=ENABLE scope=spfile;
ALTER SYSTEM SET FAL_SERVER = 'pr_cc','dr_cc2' scope=spfile;
3、修改备库2设置:
su - oracle
export ORACLE_SID=cc
$ORACLE_HOME/network/admin/listener.ora增加:
LISTENER2 =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST =172.16.23.170)(PORT = 1524))
)
)
SID_LIST_LISTENER2 =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = dr_cc2)
(ORACLE_HOME = /oracle11/product/112)
(SID_NAME = dgtest2)
)
)
$ORACLE_HOME/network/admin/tnsnames.ora增加:
pr_cc =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL= TCP)(HOST = 10.45.53.31)(PORT = 1522))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME =pr_cc)
)
)
dr_cc =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL= TCP)(HOST = 10.45.53.30)(PORT = 1524))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME =dr_cc)
)
)
dr_cc2 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL= TCP)(HOST = 172.16.23.170)(PORT = 1524))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME =dr_cc2)
)
)
sqlplus / as sysdba
ALTER SYSTEM SET DB_UNIQUE_NAME='dr_cc2' scope=spfile;
ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(pr_cc,dr_cc,dr_cc2)' scope=spfile;
ALTER SYSTEM SET LOG_ARCHIVE_DEST_1='LOCATION=+VOTE VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=dr_cc2' scope=spfile;
ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=pr_cc LGWR ASYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=pr_cc' scope=spfile;
ALTER SYSTEM SET LOG_ARCHIVE_DEST_3='SERVICE=dr_cc LGWR ASYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=dr_cc' scope=spfile;
ALTER SYSTEM SET db_file_name_convert='/oradata/dgtests/','+DATA/dgtest2/','/oradata/dr_cc/','+DATA/dgtest2/' scope=spfile;
ALTER SYSTEM SET log_file_name_convert='/oradata/dgtests/','+DATA/dgtest2/','/oradata/dr_cc/','+DATA/dgtest2/' scope=spfile;
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_1=ENABLE scope=spfile;
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE scope=spfile;
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_3=ENABLE scope=spfile;
ALTER SYSTEM SET FAL_CLIENT = dr_cc2 scope=spfile;
ALTER SYSTEM SET FAL_SERVER = 'pr_cc','dr_cc' scope=spfile;
alter system set standby_file_management='AUTO' scope=spfile;
4、主库备份数据
su - root
export ORACLE_SID=cc
sqlplus / as sysdba
rman target /
RMAN>run {
allocate channel t1 type disk;
allocate channel t2 type disk;
allocate channel t3 type disk;
allocate channel t4 type disk;
crosscheck archivelog all;
delete noprompt expired archivelog all;
backup full filesperset 6 format '/oradata/backup/db_%d_%U' database;
sql 'alter system archive log current';
backup archivelog all format '/oradata/backup/arch_%T_%s_%p';
backup current controlfile for standby format '/oradata/backup/ctl_%U';
release channel t1;
release channel t2;
release channel t3;
release channel t4;
}
crosscheck backup of controlfile;
delete expired backup of controlfile;
list backup of controlfile;
得到恢复的控制文件里可以恢复的scn
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
37 Full 12.95M DISK 00:00:01 12-MAR-20
BP Key: 37 Status: AVAILABLE Compressed: NO Tag: TAG20200312T131955
Piece Name: /oradata/backup/db_DGTEST_16uqte82_1_1
Control File Included: Ckp SCN: 23915348 Ckp time: 12-MAR-20
5、停止主库清理归档crontab脚本
6、传输主库备份和密码文件至备库2
nohup scp * oracle11@172.16.23.170:/oracle11/backup/ &
scp orapwdgtests oracle11@172.16.23.170:/oracle11/product/112/dbs/orapwdgtest2
7、备库2恢复数据和归档日志
su - oracle
export ORACLE_SID=cc
sqlplus / as sysdba
shutdown immediate
startup nomount
rman target /
restore controlfile from '/oracle11/backup/ctl_1duqte86_1_1';
exit
sqlplus / as sysdba
alter database mount standby database;
rman target /
catalog start with '/oracle11/backup/';
run{
allocate channel t1 type disk;
allocate channel t2 type disk;
allocate channel t3 type disk;
allocate channel t4 type disk;
allocate channel t5 type disk;
allocate channel t6 type disk;
restore database;
release channel t1;
release channel t2;
release channel t3;
release channel t4;
release channel t5;
release channel t6;
recover database until scn 23915348;
}
8、重启主库
备库取消应用:
alter database recover managed standby database cancel;
主库:
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=defer scope=both;
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_3=defer scope=both;
shutdown immediate
startup
9、备库1重启并应用归档,检查状态
shutdown immediate
startup nomount;
alter database mount standby database;
alter database recover managed standby database disconnect from session;
10、备库2重启并应用归档
shutdown immediate
startup nomount;
alter database mount standby database;
alter database recover managed standby database disconnect from session;
11、主库enable
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE scope=both;
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_3=ENABLE scope=both;
12、检查主备库状态
archive log list
set lines 400
col DEST_NAME for a100
select DEST_NAME , STATUS , RECOVERY_MODE from v$archive_dest_status;
主库:
LOG_ARCHIVE_DEST_1 VALID IDLE
LOG_ARCHIVE_DEST_2 VALID MANAGED
LOG_ARCHIVE_DEST_3 VALID MANAGED
备库1&2:
LOG_ARCHIVE_DEST_1 VALID MANAGED
LOG_ARCHIVE_DEST_2 VALID IDLE
LOG_ARCHIVE_DEST_3 VALID IDLE
select process ,status , sequence# from v$managed_standby;
主库:
LNS WRITING 177
LNS WRITING 177
备库1&2:
RFS IDLE 177
MRP0 WAIT_FOR_LOG 177
13、主库开启清理归档crontab
四、一主二备switchover
1.检查一主二备是否完全同步
select open_mode,database_role,db_unique_name,switchover_status from v$database;
set lines 400
col DEST_NAME for a100
select DEST_NAME , STATUS , RECOVERY_MODE from v$archive_dest_status;
select process ,status , sequence# from v$managed_standby;
2.停应用杀会话
3.主库变备库:
alter system switch logfile;
alter database commit to switchover to physical standby with session shutdown;
startup nomount
alter database mount standby database;
4.备库1变主库:
alter database commit to switchover to primary with session shutdown;
shutdown immediate
startup
5.原主库开启归档应用:
alter database recover managed standby database disconnect from session;
6.备库2重启归档应用:
alter database recover managed standby database cancel;
alter database recover managed standby database disconnect from session;
7.检查主备库状态
archive log list
set lines 400
col DEST_NAME for a100
select DEST_NAME , STATUS , RECOVERY_MODE from v$archive_dest_status;
主库:
LOG_ARCHIVE_DEST_1 VALID IDLE
LOG_ARCHIVE_DEST_2 VALID MANAGED
LOG_ARCHIVE_DEST_3 VALID MANAGED
备库1&2:
LOG_ARCHIVE_DEST_1 VALID MANAGED
LOG_ARCHIVE_DEST_2 VALID IDLE
LOG_ARCHIVE_DEST_3 VALID IDLE
select process ,status , sequence# from v$managed_standby;
主库:
LNS WRITING 190
LNS WRITING 190
备库1&2:
RFS IDLE 177
RP0 WAIT_FOR_LOG 190
8.原主库的归档清理脚本等传到新主库,并设置crontab