一. 环境介绍
Primary Database RAC环境 11R2
IP:192.168.202.35 TESTRAC1
IP:192.168.202.29 TESTRAC2
Stanby single instance环境
10.5.60.33 standby 只安装了oracle 11R2软件
由于DG是以DB_UNIQUE_NAME作为区分数据库标识的,因此主从库的该参数一定要不同以做区分。但是数据库名db_name必须一致以
便主从切换时方便
Standby库只需要安装数据库软件,不必创建数据库
主从库的数据库软件大版本必须一致,且必须是企业版数据库
二.配置安装
1.RAC主库准备工作
1.1)RAC主库必须是归档模式
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 158
Next log sequence to archive 159
Current log sequence 159
2.2)RAC主库必须设置为ForceLogging模式
SQL> alter database force logging;
2.3)RAC主库执行RMAN全备
RMAN> run{
2> allocate channel c1 type disk;
3> allocate channel c2 type disk;
4> backup database format '/backup/full_%U.bak';
5> backup archivelog all format '/backup/arc_%U.bak';
6> release channel c1;
7> release channel c2;
8> }
2.4)RAC主库执行创建物理备库控制文件
alter database create standby controlfile as '/backup/st.ctl';
2.5)RAC主库创建物理备库初始化参数文件
SQL> create pfile='/backup/test.ora' from spfile;
File created.
修改主库参数文件
[oracle@gitiraca backup]$ vi test.ora
加入
*.service_names=TESTRAC
*.log_archive_config='dg_config=(TESTRAC,RACDG)'
*.log_archive_dest_3='location=standby valid_for=(online_logfiles,primary_role) db_unique_name=RACDG'
*.db_file_name_convert='/u01/app/standbydata','+DATA/TESTRAC/DATAFILE','/u01/app/tempdata','+DATA/TESTRAC/TEMPFILE
'
*.log_file_name_convert='/u01/app/redo','+DATA/TESTRAC/ONLINELOG/'
*.standby_file_management='auto'
*.fal_server='standby'
log_archive_dest_3参数错误会导致主库关闭够无法OPEN
alter文件会警告
WARNING: No local destinations have been defined for archival of the Standby Redo Log
关闭RAC
Srvctl stop database –d TESTRAC
创建spfile文件
SQL> create spfile='+DATA/TESTRAC/spfileTESTRAC.ora' from pfile='/backup/test.ora';
File created.
2.物理备库准备工作
2.1)修改.bash_profile文件(用户目录下)
## For Oracle ##
export EDITOR=vi
export ORACLE_SID=standby
export ORACLE_BASE=/u01/app/oracle
export ORACLE_UNQNAME=RACDG
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/dbhome_1
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
export PATH=$ORACLE_HOME/bin:$ORA_CRS_HOME/bin:$PATH
umask 022
#export NLS_LANG='AMERICAN_AMERICA.UTF8'
export NLS_LANG=AMERICAN_AMERICA.ZHS16CGB231280
#export NLS_LANG=AMERICAN_AMERICA.ZHT16BIG5
#export NLS_LANG="traditional chinese_taiwan.ZHT16BIG5"
#export LC_CTYPE=en_US.UTF-8
export LANG=en_US
#alias sqlplus="uniread sqlplus"
#alias adrci="uniread adrci"
2.2)将备份文件传递到备库
scp backup/*.bak oracle@10.5.160.33:/backup/
scp /ora01/oracle/product/11.2.0/db_1/dbs/orapwTESTRAC1 oracle@10.5.160.33:/u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwstandby
[oracle@gitiraca backup]$
scp test.ora oracle:10.5.160.33:/u01/app/oracle/product/11.2.0/dbhome_1/dbs/initstandby.ora
2.3)修改备库参数文件
standby.__db_cache_size=2583691264standby.__java_pool_size=16777216
standby.__large_pool_size=16777216
standby.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
standby.__pga_aggregate_target=1275068416
standby.__sga_target=3372220416
standby.__shared_io_pool_size=0
standby.__shared_pool_size=721420288
standby.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/standby/adump'
*.core_dump_dest='/u01/app/oracle/admin/standy/cdump'
*.user_dump_dest='/u01/app/oracle/admin/standy/udump'
*.background_dump_dest='/u01/app/oracle/admin/standy/bdump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/u01/backup/standy.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_file_name_convert='+DATA/TESTRAC/DATAFILE','/u01/app/standbydata','+DATA/TESTRAC/TEMPFILE','/u01/app/tempdata
'
*.db_unique_name='RACDG'
*.db_name='TESTRAC'
*.db_recovery_file_dest='+DATABK'
*.db_recovery_file_dest_size=6005194752
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=standbyXDB)'
*.fal_server='TESTRAC1','TESTRAC2'
*.fal_client='standby'
*.standby_archive_dest='/u01/app/arch'
*.log_archive_dest_1='location=/u01/app/arch'
*.log_archive_dest_2='service=TESTRAC1 valid_for=(online_logfiles,primary_role) db_unique_name=TESTRAC'
*.log_archive_config='dg_config=(TESTRAC,RACDG)'
*.log_archive_format='%t_%s_%r.dbf'
*.log_file_name_convert='+DATA/TESTRAC/ONLINELOG/','/u01/app/redo'
*.open_cursors=300
*.pga_aggregate_target=1263534080
*.processes=150
*.remote_login_passwordfile='exclusive'
*.sessions=170
*.sga_target=3371171840
*.standby_file_management='auto'
*.service_names='standby'
thread=1
undo_tablespace='UNDOTBS1'
注意*.db_file_name_convert和*.log_file_name_convert,因为RAC和standby的数据文件物理结构目录不一样,在备库上就要把
RAC的数据文件地址'+DATA/TESTRAC/DATAFILE'转变成备库上数据文件地址
2.4)配置RAC主库,物理备库的tnsnames.ora文件
TESTRAC =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST =192.168.202.35)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST =192.168.202.29)(PORT = 1521))
(LOAD_BLANCE = yes)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = TESTRAC)
(FAILOVER_MODE=
(TYPE = session)
(METHOD = basic)
(RETRIES = 180)
(DELAY = 5)
)
)
TESTRAC1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.202.35)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = TESTRAC)
(INSTANCE_NAME=TESTRAC1)
)
)
TESTRAC2 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.202.29)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = TESTRAC)
(INSTANCE_NAME=TESTRAC2)
)
)
standby =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.5.160.33)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = standby)
)
)
2.5)修改备库listener.ora
standby =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST =SH-HQ-USACLONE)(PORT = 1521))
)
)
SID_LIST_standby =
(SID_LIST =
(SID_DESC =
(SID_NAME = standby)
(ORACLE_HOME =/u01/app/oracle/product/11.2.0/dbhome_1)
)
)
ADR_BASE_LISTENER =/u01/app/oracle
3.创建物理备库
3.1)物理备库启动到nomount状态:
startup pfile='/u01/app/oracle/product/11.2.0/dbhome_1/dbs/initstandby.ora' nomount;
3.2)RMAN恢复备库控制文件
[oracle@SH-HQ-USACLONE admin]$ rman target/
RMAN> restore standby controlfile from '/backup/standby.ctl';
Starting restore at 19-MAR-15
using channel ORA_DISK_1
channel ORA_DISK_1: copied control file copy
output file name=/u01/backup/standy.ctl
Finished restore at 19-MAR-15
3.3)MOUNT物理备库
SQL> alter database mount;
Database altered.
主库alter报错
PING[ARC2]: Heartbeat failed to connect to standby 'standby'. Error is 16047.
PING[ARC2]: Heartbeat failed to connect to standby 'standby'. Error is 16047.
解决方法:
原因的备库的unique_name 和主库里的
*.log_archive_dest_3='location=standby valid_for=(all_logfiles,all_roles) db_unique_name=RACDG' 参数里的RACDG不一
致导致
修改备库参数*.db_unique_name='RACDG'(上面备库参数已经修改)
3.4)restore 备库
检查备份集
RMAN> crosscheck backupset
恢复备库
RMAN> restore database;
3.5)备库创建standbylogfile
SQL> alter database add standby logfile thread 1 group 5('/u01/app/redo/st_1_5.rdo') size 50M;
Database altered.
SQL> alter database add standby logfile thread 1 group 6('/u01/app/redo/st_1_6.rdo') size 50M;
Database altered.
SQL> alter database add standby logfile thread 1 group 7('/u01/app/redo/st_1_7.rdo') size 50M;
Database altered.
SQL> alter database add standby logfile thread 2 group 8('/u01/app/redo/st_2_8.rdo') size 50M;
Database altered.
SQL> alter database add standby logfile thread 2 group 9('/u01/app/redo/st_2_9.rdo') size 50M;
Database altered.
SQL> alter database add standby logfile thread 2 group 10('/u01/app/redo/st_2_10.rdo') size 50M;
Database altered.
RAC每个redo thread都需要创建对应的standby redo log。创建原则:文件大小相等,日志组数数量要多一组
RAC主库查询日常传送情况
SQL> select dest_name,status,error from v$archive_dest;
3.6)开始同步
启动MRP
SQL> recover managed standby database disconnect from session;
Media recovery complete.
3.7)备库READONLY方式打开
SQL> alter database recover managed standby database cancel;
Database altered.
SQL> alter database open read only;
Database altered.
SQL> alter database recover managed standby database using current logfile disconnect from session;
Database altered.
rac主库执行查询
select dbid,name,current_scn,protection_mode,database_role,force_logging,open_mode,switchover_status from v$database;
备库查询
select dbid,name,current_scn,protection_mode,database_role,force_logging,open_mode,switchover_status from v$database;
成功!!!!
4.修改模式
由上面可以看到配置默认的是最大性能模式 Maximun Performance
现在修改成最大可用性Maximun Availability
1. 关闭数据库重启到mount状态,如果是RAC环境需要关闭所有实例,然后启动一个实例到mount状态即可
[oracle@gitiraca ~]$ srvctl stop database -d TESTRAC
SQL> startup mount;
SQL> alter database set standby database to maximize availability;
Database altered.
SQL>alter database open
SQL> select protection_mode,protection_level from v$database;
PROTECTION_MODE
------------------------------------------------------------
PROTECTION_LEVEL
------------------------------------------------------------
MAXIMUM AVAILABILITY
RESYNCHRONIZATION
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29989552/viewspace-1471755/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/29989552/viewspace-1471755/