这几天给客户的Oracle 11gr2 RAC搭建到单实例的DG,整理了一下步骤,理清一下思路,文中敏感信息已做模糊处理。
RAC至单实例的Dataguard为双节点各自传递自己当前线程的归档至备库。
由于节点1存储不足,备份在节点2上跑。整体搭建思路如下:
备库文件目录创建-->主库force logging(已是归档模式)
-->备份节点二-->复制节点二口令文件à创建备库pfileà创建备库à恢复备库
-->创建主备standby logfile-->配置主备监听及TNSà修改主库(节点一、节点二)参数-->打开备库并实时应用
1.1 主备环境说明
Primary | Node1 | Node2 |
Hostname | hostname01 | hostname02 |
Public | 10.10.10.141 | 10.10.10.143 |
VIP | 10.10.10.142 | 10.10.10.144 |
SCAN | 10.10.10.140 | |
Instance_name | repprod1 | repprod2 |
DB_name | repprod | |
Service_names | repprod | |
DB_unique_name | repprod | |
Storage_mode | ASM | |
Standby | Node | |
Hostname | hostnamedg | |
IP | 10.10.10.191 | |
Instance_name | Repproddg | |
DB_name | repprod | |
Service_names | repproddg | |
DB_unique_name | repproddg | |
Storage_mode | Local disk |
1.2 文件系统规划
Primary | Standby |
+DATADG/repprod/datafile | /oradata/datafile |
+DATADG/repprod/tempfile | /oradata/tempfile |
+ARCDG/repprod/onlinelog +DATADG/repprod/onlinelog | /oradata/onlinelog |
+ARCDG/ | /oradata/arch |
1.3 实施操作步骤
1.3.1 备库存储和文件系统创建
#standby库上新建各个数据文件的存放位置
$mkdir -p /oradata/datafile --数据文件
$mkdir -p /oradata/tempfile --临时文件
$mkdir -p /oradata/onlinelog --redo日志
$mkdir -p /oradata/controlfile --控制文件
$mkdir -p /oradata/arch --归档文件
#备库为全新安装oracle 11.2.0.4软件,需要手工建立以下目录,保持与主库目录一致。
$cd $ORACLE_BASE
$mkdir -p admin/repproddg/adump
$mkdir -p admin/repproddg/dpdump
$mkdir -p admin/repproddg/hdump
$mkdir -p admin/repproddg/pfile
1.3.2 主库上设置数据库运行在force logging模式
#查看数据库的模式:
SQL> select LOG_MODE,OPEN_MODE,PROTECTION_MODE,DATABASE_ROLE,SWITCHOVER_STATUS,GUARD_STATUS,FORCE_LOGGING from v$database;
#在主库上执行:
SQL> alter database force logging;
1.3.3 备份主库
#节点二备份
$rman target /
RMAN> run{
allocate channel c1 type disk;
allocate channel c2 type disk;
backup filesperset=3 format='/oracle/backup/%T_%d_%s_%p.bus' as compressed backupset database;
backup format='/oracle/backup/%T_%d_%s_%p.ctl' current controlfile;
release channel c1;
release channel c2;
}
#传递备份至备库
$scp /oracle/backup/20180418* oracle@10.10.10.191:/oradata/backup
1.3.4 口令文件复制
#将主库的口令文件复制到备库相同目录下,并改名为orapwrepproddg。
$scp /oracle/app/oracle/db/dbs/orapwrepprod oracle@10.10.10.191:/u01/app/oracle/product/11.2.0/db_1/dbs
#重命名
$mv orapwrepprod orapwrepproddg
1.3.5 创建和修改备库pfile 参数文件
#主库上生成pfile
SQL> create pfie from spfile;
#传递至备库
$cd $ORACLE_HOME/dbs
$scp initrepprod.ora oracle@10.10.10.191: /u01/app/oracle/product/11.2.0/db_1/dbs
#修改为initrepproddg.ora并修改相关参数
$mv initrepprod.ora initrepproddg.ora
$vi initrepprod.ora
*.__db_cache_size=21206401024
*.__java_pool_size=1879048192
*.__large_pool_size=805306368
*.__pga_aggregate_target=70061654016
*.__sga_target=38386270208
*.__shared_io_pool_size=0
*.__shared_pool_size=13421772800
*.__streams_pool_size=536870912
*.audit_file_dest= '/u01/app/oracle/repproddg/adump'
*.audit_trail='db'
*.compatible='11.2.0.4.0'
*.control_files='/oradata/controlfile/control01.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name=repprod
*.db_unique_name=repproddg
*.service_names=repproddg
*.db_recovery_file_dest_size=209715200000
*.open_cursors=300
*.pga_aggregate_target=70061654016
*.processes=1500
*.sessions=1655
*.sga_target=38386270208
*._undo_autotune=FALSE
*.log_archive_format='%t_%s_%r.arc'
*.db_recovery_file_dest='/oradata/arch'
*.db_file_name_convert='+DATADG/repprod/datafile','/oradata/datafile','+DATADG/repprod/tempfile','/oradata/tempfile'
*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(repprod, repproddg)'
*.LOG_ARCHIVE_DEST_1='LOCATION=/oradata/arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=repproddg'
*.LOG_ARCHIVE_DEST_2='SERVICE=repprod_pri LGWR ASYNC NOAFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME= repprod'
*.log_file_name_convert='+ARCDG/repprod/onlinelog','/oradata/onlinelog','+DATADG/repprod/onlinelog','/oradata/onlinelog'
*.remote_login_passwordfile='exclusive'
*.standby_file_management=auto
*.diagnostic_dest= '/u01/app/oracle'
*.fal_server='repprod1_pri ','repprod2_pri'
*.fal_client='repproddg_stb'
$sqlplus / as sysdba
SQL> startup pfile='/u01/app/oracle/product/11.2.0/db_1/dbs/initrepproddg.ora' nomount;
SQL> create spfile from pfile
SQL> shut immediate
1.3.6 恢复备库
#备库恢复控制文件
SQL> startup nomount;
RMAN> restore standby controlfile from '/oradata/backup/20180418_REPPROD_4020_1.ctl';
RMAN> alter database mount;
#注册备份集
RMAN> CATALOG START WITH ‘/oradata/backup/’;
RMAN> CROSSCHECK BACKUP;
rman target /
run
{
set newname for datafile 1 to '/oradata/datafile/system01.dbf';
set newname for datafile 2 to '/oradata/datafile/sysaux01.dbf';
set newname for datafile 3 to '/oradata/datafile/undotbs01.dbf';
set newname for datafile 4 to '/oradata/datafile/undotbs02.dbf';
set newname for datafile 5 to '/oradata/datafile/users01.dbf';
set newname for datafile 6 to '/oradata/datafile/bylgt01.dbf';
set newname for tempfile 1 to '/oradata/tempfile/temp01.dbf';
restore database;
SWITCH DATAFILE ALL;
SWITCH TEMPFILE ALL;
}
#把归档从主库中copy到备库后,进行手工recover
#根据备库alter.log查看缺失的日志sequence号
#节点二上从ASM上copy归档到本地再scp至备库
$su – grid
$asmcmd
ASMCMD> cd +ARCDG/repprod/AR*/2018_04_18
ASMCMD> cp thread_2_seq_3741.2001.973785643 /oracle/backup
ASMCMD> cp thread_2_seq_3742.2012.973785651 /oracle/backup
ASMCMD> cp thread_2_seq_3743.2015.973785659 /oracle/backup
ASMCMD> cp thread_2_seq_3744.1987.973785669 /oracle/backup
ASMCMD> cp thread_2_seq_3745.2008.973785709 /oracle/backup
ASMCMD> cp thread_2_seq_3746.1977.973785717 /oracle/backup
ASMCMD> cp thread_2_seq_3747.1996.973791733 /oracle/backup
ASMCMD> cp thread_2_seq_3748.2005.973791739 /oracle/backup
$cd /oracle/backup
$ scp thread_2_seq* oracle@10.10.10.191:/oradata/arch
#备库上注册归档并recover database
SQL> alter database register logfile '/oradata/arch/thread_2_seq_3741.2001.973785643';
SQL> alter database register logfile '/oradata/arch/thread_2_seq_3742.2012.973785651';
SQL> alter database register logfile '/oradata/arch/thread_2_seq_3743.2015.973785659';
SQL> alter database register logfile '/oradata/arch/thread_2_seq_3744.1987.973785669';
SQL> alter database register logfile '/oradata/arch/thread_2_seq_3745.2008.973785709';
SQL> alter database register logfile '/oradata/arch/thread_2_seq_3746.1977.973785717';
SQL> alter database register logfile '/oradata/arch/thread_2_seq_3747.1996.973791733';
SQL> alter database register logfile '/oradata/arch/thread_2_seq_3748.2005.973791739';
SQL> recover managed standby database disconnect from session;
Media recovery complete.
SQL> recover managed standby database cancel;
Media recovery complete.
1.3.7 在主库中建立standby logfile
#最大保护及最高可用性保护模式必须创建SRL,但强烈建议也为最大性能模式添加,为了简化切换以后的操作,建议同时在主库也进行添加。SRL大小必须跟主数据库ORL一样,且比ORL数量多1。
具体可参考“thread数*(ORL+1)”,即两节点的rac,ORL为3,则SRL数量为2*(3+1)=8。配置主数据库standby redo日志,在节点一或节点二进行:
SQL> alter database add standby logfile thread 1('+ARCDG','+DATADG') size 512M;
SQL> alter database add standby logfile thread 1('+ARCDG','+DATADG') size 512M;
SQL> alter database add standby logfile thread 1('+ARCDG','+DATADG') size 512M;
SQL> alter database add standby logfile thread 1('+ARCDG','+DATADG') size 512M;
SQL> alter database add standby logfile thread 2('+ARCDG','+DATADG') size 512M;
SQL> alter database add standby logfile thread 2('+ARCDG','+DATADG') size 512M;
SQL> alter database add standby logfile thread 2('+ARCDG','+DATADG') size 512M;
SQL> alter database add standby logfile thread 2('+ARCDG','+DATADG') size 512M;
1.3.8 在备库中建立standby logfile
#与主库一样的redo组
SQL> alter database add standby logfile group 7 ('/oradata/onlinelog/redo7a.dbf','/oradata/onlinelog/redo7b.dbf') size 512m;
SQL> alter database add standby logfile group 8 ('/oradata/onlinelog/redo8a.dbf','/oradata/onlinelog/redo8b.dbf') size 512m;
SQL> alter database add standby logfile group 9 ('/oradata/onlinelog/redo9a.dbf','/oradata/onlinelog/redo9b.dbf') size 512m;
SQL> alter database add standby logfile group 10 ('/oradata/onlinelog/redo10a.dbf','/oradata/onlinelog/redo10b.dbf') size 512m;
SQL> alter database add standby logfile group 11 ('/oradata/onlinelog/redo11a.dbf','/oradata/onlinelog/redo11b.dbf') size 512m;
SQL> alter database add standby logfile group 12 ('/oradata/onlinelog/redo12a.dbf','/oradata/onlinelog/redo12b.dbf') size 512m;
SQL> alter database add standby logfile group 13 ('/oradata/onlinelog/redo13a.dbf','/oradata/onlinelog/redo13b.dbf') size 512m;
SQL> alter database add standby logfile group 14 ('/oradata/onlinelog/redo14a.dbf','/oradata/onlinelog/redo14b.dbf') size 512m;
1.3.9 备库上监听和TNS配置
#配置监听
$cd $ORACLE_HOME/network/admin
$vi listener.ora
repproddg =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.10.191)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
)
SID_LIST_repproddg =
(SID_LIST =
(SID_DESC =
(SID_NAME = repproddg)
(ORACLE_HOME =/u01/app/oracle/product/11.2.0/db_1)
#(PROGRAM = extproc)
)
)
#配置TNS
$vi tnsname.ora
repprod_pri =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST =10.10.10.140)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = repprod)
)
)
repproddg_stb=
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST =10.10.10.191)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = repproddg)
)
)
#开启监听
$lsnrctl statrt
1.3.10 主库上TNS配置
#将以下备库的tns加入到主库两节点的tnsnames.ora中:
$cd $ORACLE_HOME/network/admin
$vi tnsnames.ora
repprod_pri =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST =10.10.10.141)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = repprod)
)
)
repproddg_stb=
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST =10.10.10.191)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = repproddg)
)
)
监听和TNS检查
#在主库两节点上执行以下:
$tnsping repproddg_stb
#在备库中执行以下:
$tnsping repprod_pri
1.3.11 主库上修改参数
#节点一、节点二均设置以下参数
SQL> alter system set log_archive_config='DG_CONFIG=(repprod,repproddg)' scope=both sid='*';
SQL> alter system set log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(all_logfiles,all_roles) db_unique_name=repprod' scope=both sid='*';
SQL> alter system set log_archive_dest_2='SERVICE=repproddg_stb lgwr ASYNC NOAFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=repproddg' scope=both;
SQL> alter system set log_archive_max_processes=10 scope=both;
SQL> alter system set db_file_name_convert='/oradata/datafile', '+DATADG/repprod/datafile', 'oradata/tempfile', '+DATADG/repprod/tempfile' scope=spfile;
SQL> alter system set log_file_name_convert='/oradata/onlinelog','+ARCDG/repprod/onlinelog','/oradata/onlinelog','+DATADG/repprod/onlinelog' scope=spfile;
SQL> alter system set standby_file_management=auto scope=both;
1.3.12 打开数据库并实时应用redo
#开启数据库并打卡redo日志实时应用
SQL> alter database open;
SQL> alter database recover managed standby database using current logfile disconnect from session;
二、 *实施完成后的验证
#主库检查current sequence#
SQL> select thread#,sequence#,status from v$log;
#主库检查LNS进程正在写的sequence#
SQL> select process,sequence#,status from v$managed_standby;
#备库检查正在应用的sequence#
SQL> SELECT PROCESS,THREAD#,SEQUENCE#,STATUS FROM V$MANAGED_STANDBY;
#检查是否存在GAP
SQL> SELECT THREAD#,LOW_SEQUENCE#,HIGH_SEQUENCE# FROM V$ARCHIVE_GAP;
no rows selected
三、 问题汇总
3.1 备库未打补丁处理
1.传递与主库一致的补丁p24006111_112040_Linux-x86-64.zip至/software上
2.关闭与Oracle相关的所有进程,包括sqlplus
3.opatch version必须大于11.2.0.3.6
4.检查环境
unzip p24006111_112040_ Linux-x86-64.zip
cd 24006111
$ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -ph ./
5.应用补丁
$ORACLE_HOME/OPatch/opatch apply
3.2 Temp文件错误处理
1.恢复时set newname路径错误
2.将tempfile下线:
SQL> alter database tempfile '/oradata/tempile/temp01.dbf' offline;
3.新建正确路径的tempfile:
SQL> alter tablespace TEMP add tempfile '/oradata/tempfile/temp01.dbf' size 2G autoextend on;
4.删除错误tempfile
SQL> alter database tempfile '/oradata/tempile/temp01.dbf' drop
3.3 归档删除脚本部署
#在/home/oracle/下新建归档删除脚本,保留3天
$ vi /home/oracle/delete_arch.sh
. ~/.bash_profile
rman target / <<EOF
crosscheck archivelog all;
delete noprompt archivelog until time 'sysdate-3';
crosscheck archivelog all;
exit
EOF
#Oracle crontab,每天0点自动删除归档文件
$crontab -e
#每天0点自动删除归档文件
0 * * * * /home/oracle/delete_arch.sh 1>/home/oracle/delete_arch.sh.out 2>&1
SERVICE_NAME = repproddg