Oracle 11gr2 RAC到单实例的DG搭建(落地备份)

 

一、 实施方案

 

这几天给客户的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