Oracle Data Guard RAC到单机ASM配置(物理standby)

来源:互联网 时间:1970-01-01

本次配置11G rac到单机ASM的dataguard配置,11G rac的安装参考oracle 11G rac 安装(VMware + rhel6.3),11G 单机ASM安装参考Oracle 11G 单机asm安装。 
环境介绍:
主库:
11203 2节点RAC   实例名:racdb1,racdb2  db_name:racdb  db_unique_name:racdb
备库:
11203 单机 ASM   实例名:racdbstd        db_name:racdb  db_unique_name:racdbstd 
整个操作过程按照以下进行
主库操作
1.打开Forced Logging
2.配置主库参数
3.打开主库归档
4.创建主库备份用来作为备库恢复
5.创建standby数据库的控制文件
6.创建配置备库参数文件
7.拷贝所需文件到备库
 
备库操作
1.设置备库环境变量
  1.1密码文件拷贝
  1.2监听配置
  1.3创建网络服务名
2.创建所需的目录
3.参数文件配置
4.恢复备库
  4.1恢复参数文件
  4.2恢复控制文件
  4.3恢复数据库
5.创建standby redo
  5.1主库创建
  5.2备库创建
6.网络服务名测试
  6.1主库测试
  6.2备库测试
7.开启实时应用
8.测试
9.启动关闭data guard
具体操作如下所示:
 
主库操作
 
1.打开Forced Logging
 
SQL> alter database force logging ;
 
Database altered.
 
SQL> select force_logging from v$database;
 
FOR
---
YES
 
2.配置主库参数
log_archive_dest_n参数的配置参数详解参考log_archive_dest_n详解
 
[[email protected] ~]# mkdir /oradata/arch -p
[[email protected] ~]# chown oracle.oinstall /oradata/arch/
 
[[email protected] ~]# mkdir /oradata/arch -p
[[email protected] ~]# chown oracle.oinstall /oradata/arch/
 
[[email protected] ~]$ mkdir /home/oracle/standby
修改参数
 
 
SQL> alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(racdb,racdbstd)' sid='*' ;
 
System altered.
 
括号中为db_unique_name
 
设置主库归档路径
 
SQL> alter system set log_archive_dest_1='location=+datadg1' sid='racdb1' ;
 
System altered.
 
SQL> alter system set log_archive_dest_1='location=+datadg1' sid='racdb2'; 
 
System altered. 
 
SQL> alter system set log_archive_dest_2='SERVICE=racdbstd LGWR AFFIRM ASYNC valid_for=(ONLINE_LOGFILES,PRIMARY_ROLE) db_unique_name=racdbstd' sid='*' ;
 
System altered.
 
SQL> alter system set log_archive_max_processes=30 sid='*' ;
 
System altered.
 
SQL> alter system set fal_server='racdbstd' sid='*' ;
 
System altered.
 
SQL> alter system set STANDBY_FILE_MANAGEMENT=AUTO  sid='*';
 
System altered.
 
3.打开主库归档
 
关闭数据库
 
[[email protected] ~]# su - oracle -c "srvctl stop instance -d racdb -i racdb1"
[[email protected] ~]# su - oracle -c "srvctl stop instance -d racdb -i racdb2"
 
在一个节点启动mount状态
 
[[email protected] ~]# su - oracle
[[email protected] ~]$ sqlplus / as sysdba
 
SQL*Plus: Release 11.2.0.3.0 Production on Wed Oct 21 11:23:10 2015
 
Copyright (c) 1982, 2011, Oracle.  All rights reserved.
 
Connected to an idle instance.
 
SQL> startup mount ;
 
SQL> alter database archivelog;
 
Database altered.
 
SQL> alter database open ;
 
Database altered.
 
节点2打开数据库
 
[[email protected] ~]$ sqlplus / as sysdba
 
SQL*Plus: Release 11.2.0.3.0 Production on Wed Oct 21 11:24:39 2015
 
Copyright (c) 1982, 2011, Oracle.  All rights reserved.
 
Connected to an idle instance.
 
SQL> startup
 
4.创建主库备份用来作为备库恢复
 
使用以下脚本备份数据库

run{allocate channel c1 device type disk ; allocate channel c2 device type disk ; backup full database format '/home/oracle/standby/full_%U_%d_%T';sql 'alter system archive log current';sql 'alter system archive log current';sql 'alter system archive log current';backup archivelog all format '/home/oracle/standby/arch_%u_%d_%T';release channel c1;release channel c2; }

 


5.创建standby数据库的控制文件
 
SQL>  alter database create standby controlfile as '/home/oracle/standby/standby.ctl';
 
Database altered.
6.创建配置备库参数文件
 
SQL> create pfile='/home/oracle/standby/a.txt' from spfile ;
 
File created.
 
7.拷贝所需文件到备库
[[email protected] ~]$ scp -r /home/oracle/standby/ 192.168.10.30:/home/oracle/full_15qk946n_1_1_RACDB_20151021 100% 507MB 16.9MB/s 00:30 arch_0vqk9379_RACDB_20151021 100% 20KB 19.5KB/s 00:00arch_19qk94al_RACDB_20151021 100% 8960KB 8.8MB/s 00:00 arch_18qk94al_RACDB_20151021 100% 15MB 15.2MB/s 00:01 standby.ctl 100% 18MB 17.9MB/s 00:00 arch_0tqk9377_RACDB_20151021 100% 1898KB 1.9MB/s 00:00 full_16qk946n_1_1_RACDB_20151021 100% 312MB 11.1MB/s 00:28 full_13qk93vj_1_1_RACDB_20151021 100% 506MB 11.8MB/s 00:43 full_0rqk9346_1_1_RACDB_20151021 100% 310MB 11.1MB/s 00:28 a.txt 100% 1196 1.2KB/s 00:00 full_11qk93jb_1_1_RACDB_20151021 100% 505MB 13.3MB/s 00:38 arch_0uqk9377_RACDB_20151021 100% 1367KB 1.3MB/s 00:00

 


 
备库操作
 
1.设置备库环境变量
 
1.1密码文件拷贝
 
[[email protected] dbs]$ scp 192.168.10.10:/oracle/db/product/11.2/dbs/orapwracdb1 $ORACLE_HOME/dbs/orapw$ORACLE_SID
 
1.2监听配置
 
为备库创建监听,主库的监听已经创建,当前创建备库的监听,11G ASM使用grid用户管理监听,使用netmgr命令操作,创建完毕之后,grid用户下的$ORACLE_HOME/network/admin下的listener.ora文件如下所示:
SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = racdbstd) (ORACLE_HOME = /oracle/asm) (SID_NAME = racdbstd) ) )LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521)) ) (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = dg)(PORT = 1521)) ) )ADR_BASE_LISTENER = /oracle/gridENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER = ON

 


 
1.3创建网络服务名
 
使用oracle用户登录,在/oracle/db/product/11.2/network/admin的tnsnames.ora中加入以下内容,同样,rac的两个节点需需要添加
 
RACDBSTD =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.10.30)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = racdbstd)
    )
  )
 
2.创建所需的目录
 
[[email protected] ~]$ mkdir -p /oracle/db/admin/racdbstd/adump
 
3.参数文件配置
 
修改完毕之后,参数如下所示:
 
[[email protected] ~]$ vi standby/a.txt*.audit_file_dest='/oracle/db/admin/racdbstd/adump'*.audit_trail='db'*.compatible='11.2.0.0.0'*.control_files='+DATADG1/racdbstd/controlfile/control01.ctl'*.db_block_size=8192*.db_create_file_dest='+DATADG1'*.db_domain=''*.db_name='racdb'*.diagnostic_dest='/oracle/db'*.dispatchers='(PROTOCOL=TCP) (SERVICE=racdbXDB)'*.open_cursors=300*.pga_aggregate_target=301989888*.processes=150*.remote_login_passwordfile='exclusive'*.sga_target=905969664*.db_unique_name=racdbstd*.log_archive_config='dg_config=(racdb,racdbstd)'*.log_archive_dest_2='location=+datadg1'*.log_archive_dest_2='service=racdb LGWR AFFIRM ASYNC valid_for=(ONLINE_LOGFILES,PRIMARY_ROLE) db_unique_name=racdb'*.log_archive_max_processes=30*.fal_server=racdb*.standby_file_management=auto

 


 
4.恢复备库
 
4.1恢复参数文件
 
[[email protected] ~]$ mv standby/a.txt $ORACLE_HOME/dbs/init$ORACLE_SID.ora[[email protected] ~]$ sqlplus / as sysdbaSQL*Plus: Release 11.2.0.3.0 Production on Wed Oct 21 15:39:21 2015Copyright (c) 1982, 2011, Oracle. All rights reserved.Connected to an idle instance.SQL> startup nomount ;ORACLE instance started.Total System Global Area 901914624 bytesFixed Size 2233600 bytesVariable Size 243272448 bytesDatabase Buffers 650117120 bytesRedo Buffers 6291456 bytesSQL>

 


 
4.2恢复控制文件
 
[[email protected] ~]$ rman target /Recovery Manager: Release 11.2.0.3.0 - Production on Wed Oct 21 15:43:55 2015Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.connected to target database: RACDB (not mounted)RMAN> restore standby controlfile from '/home/oracle/standby/standby.ctl';Starting restore at 21-OCT-15using target database control file instead of recovery catalogallocated channel: ORA_DISK_1channel ORA_DISK_1: SID=24 device type=DISKchannel ORA_DISK_1: copied control file copyoutput file name=+DATADG1/racdbstd/controlfile/control01.ctlFinished restore at 21-OCT-15SQL> alter database mount standby database ;Database altered.SQL> select controlfile_type from v$database ;CONTROL-------STANDBYSQL>

 


 
4.3恢复数据库
 
RMAN> restore database ;Starting restore at 21-OCT-15using target database control file instead of recovery catalogallocated channel: ORA_DISK_1channel ORA_DISK_1: SID=57 device type=DISKchannel ORA_DISK_1: starting datafile backup set restorechannel ORA_DISK_1: specifying datafile(s) to restore from backup setchannel ORA_DISK_1: restoring datafile 00002 to +DATADG1/racdb/datafile/sysaux.260.892746071channel ORA_DISK_1: restoring datafile 00004 to +DATADG1/racdb/datafile/undotbs2.263.892746151channel ORA_DISK_1: reading from backup piece /home/oracle/standby/full_16qk946n_1_1_RACDB_20151021channel ORA_DISK_1: piece handle=/home/oracle/standby/full_16qk946n_1_1_RACDB_20151021 tag=TAG20151021T140614channel ORA_DISK_1: restored backup piece 1channel ORA_DISK_1: restore complete, elapsed time: 00:00:45channel ORA_DISK_1: starting datafile backup set restorechannel ORA_DISK_1: specifying datafile(s) to restore from backup setchannel ORA_DISK_1: restoring datafile 00001 to +DATADG1/racdb/datafile/system.259.892746035channel ORA_DISK_1: restoring datafile 00003 to +DATADG1/racdb/datafile/undotbs1.261.892746097channel ORA_DISK_1: restoring datafile 00005 to +DATADG1/racdb/datafile/users.264.892746163channel ORA_DISK_1: reading from backup piece /home/oracle/standby/full_15qk946n_1_1_RACDB_20151021channel ORA_DISK_1: piece handle=/home/oracle/standby/full_15qk946n_1_1_RACDB_20151021 tag=TAG20151021T140614channel ORA_DISK_1: restored backup piece 1channel ORA_DISK_1: restore complete, elapsed time: 00:01:56Finished restore at 21-OCT-15RMAN> recover database ;Starting recover at 21-OCT-15using channel ORA_DISK_1starting media recoverychannel ORA_DISK_1: starting archived log restore to default destinationchannel ORA_DISK_1: restoring archived logarchived log thread=1 sequence=75channel ORA_DISK_1: restoring archived logarchived log thread=2 sequence=30channel ORA_DISK_1: restoring archived logarchived log thread=1 sequence=76channel ORA_DISK_1: restoring archived logarchived log thread=2 sequence=31channel ORA_DISK_1: restoring archived logarchived log thread=2 sequence=32channel ORA_DISK_1: restoring archived logarchived log thread=1 sequence=77channel ORA_DISK_1: restoring archived logarchived log thread=1 sequence=78channel ORA_DISK_1: restoring archived logarchived log thread=2 sequence=33channel ORA_DISK_1: reading from backup piece /home/oracle/standby/arch_19qk94al_RACDB_20151021channel ORA_DISK_1: piece handle=/home/oracle/standby/arch_19qk94al_RACDB_20151021 tag=TAG20151021T140820channel ORA_DISK_1: restored backup piece 1channel ORA_DISK_1: restore complete, elapsed time: 00:00:01archived log file name=/oracle/db/product/11.2.0/dbhome_1/dbs/arch1_75_892746023.dbf thread=1 sequence=75archived log file name=/oracle/db/product/11.2.0/dbhome_1/dbs/arch2_30_892746023.dbf thread=2 sequence=30archived log file name=/oracle/db/product/11.2.0/dbhome_1/dbs/arch1_76_892746023.dbf thread=1 sequence=76archived log file name=/oracle/db/product/11.2.0/dbhome_1/dbs/arch2_31_892746023.dbf thread=2 sequence=31archived log file name=/oracle/db/product/11.2.0/dbhome_1/dbs/arch2_32_892746023.dbf thread=2 sequence=32archived log file name=/oracle/db/product/11.2.0/dbhome_1/dbs/arch1_77_892746023.dbf thread=1 sequence=77archived log file name=/oracle/db/product/11.2.0/dbhome_1/dbs/arch1_78_892746023.dbf thread=1 sequence=78archived log file name=/oracle/db/product/11.2.0/dbhome_1/dbs/arch2_33_892746023.dbf thread=2 sequence=33archived log file name=/oracle/db/product/11.2.0/dbhome_1/dbs/arch1_79_892746023.dbf thread=1 sequence=79archived log file name=/oracle/db/product/11.2.0/dbhome_1/dbs/arch2_34_892746023.dbf thread=2 sequence=34archived log file name=/oracle/db/product/11.2.0/dbhome_1/dbs/arch1_80_892746023.dbf thread=1 sequence=80archived log file name=/oracle/db/product/11.2.0/dbhome_1/dbs/arch2_35_892746023.dbf thread=2 sequence=35archived log file name=/oracle/db/product/11.2.0/dbhome_1/dbs/arch2_36_892746023.dbf thread=2 sequence=36unable to find archived logarchived log thread=2 sequence=37RMAN-00571: ===========================================================RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============RMAN-00571: ===========================================================RMAN-03002: failure of recover command at 10/21/2015 16:17:16RMAN-06054: media recovery requesting unknown archived log for thread 2 with sequence 37 and starting SCN of 1033093RMAN>

 


 
5.创建standby redo
 
注意:如果主库已经提前创建好了standby redo log file,备库会根据LOG_FILE_NAME_CONVERT参数转换后自动创建与之对应的standby redo log file日志组。因此此步骤可省略。
 
如有主库未创建standby redo logfile,则备库必须手工创建日志文件组
公式如下:
 
如果主库是单实例数据库:Standby Redo Log组数=主库日志组总数+1
如果主库是RAC数据库:StandbyRedo Log组数=(所有节点中日志组数最大值 + 1) * RAC节点数
 
5.1主库创建
 
5.2备库创建
 
SQL> alter database add standby logfile thread 1 ('+datadg1') size 50m ;
 
Database altered.
 
SQL> /
 
Database altered.
 
SQL> /
 
Database altered.
 
SQL> l
  1* alter database add standby logfile thread 1 ('+datadg1') size 50m
SQL> c/1/2
  1* alter database add standby logfile thread 2 ('+datadg1') size 50m
SQL> /
 
Database altered.
 
SQL> /
 
Database altered.
 
SQL> /
 
Database altered.
 
SQL>
 
6.网络服务名测试
 
6.1主库测试
 
如果成功连接,说明正常
[[email protected] ~]$ sqlplus system/[email protected]
[[email protected] ~]$ sqlplus system/[email protected]

6.2备库测试
 
[[email protected] ~]$ sqlplus system/[email protected]
 
7.开启实时应用
 
启动redo apply,在此过程中注意监控备库日志的变化
 
 
SQL> alter database recover managed standby database using current logfile disconnect from session ;
 
Database altered.
8.测试
9.启动关闭data guard
以上参照Oracle Data Guard 单实例到单实例配置(物理standby)



相关阅读:
Top