Oracle 11gr2 RAC到单实例的DG搭建(落地备份)
2018-05-15 15:49 AllegroCantabile 阅读(7067) 评论(0) 编辑 收藏 举报
一、 实施方案
这几天给客户的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