本文共 17057 字,大约阅读时间需要 56 分钟。
RAC,DATAGUARD,STREAM被认为是Oracle的高可用的三架马车.三架马车各有所侧重:
(1)RAC主要解决了单节点故障,实现负载均衡,也实现了高性能。常用于24x7 的商业应用。
(2)DATAGUARD用冗余的方式实现高可用的,解决容灾。
(3)STREAM更加丰富,主要是应用层面的数据共享。
------->>本篇小编主要模拟在Vmware Server2.0下搭建基于oracle10gR2_32的dataguard(主库基于ASM存储,从库基于普通文件系统存储)====================================================================================================== ORACLE 10G 搭建DATAGUARD步骤======================================================================================================
1 安装环境(VMServer2.0)
在主机1上安装数据库软件,并建监听和实例,在主机2上安装数据库软件,并建监听,但不建实例。
| 主库 | dg备库 |
操作系统 | Redhat5 32位 | Redhat5 32位 |
主机名 | Rac01 | Asm.dg |
IP | 192.168.63.100 | 192.168.63.132 |
数据库软件版本 | oracle 10.2.0.1.0 | oracle 10.2.0.1.0 |
ORACLE_BASE | /opt/oracle10g | /home/app/oracle/ |
ORACLE_HOME | $ORACLE_BASE/product/10.2.0/db_1 | $ORACLE_BASE/product/10.2.0/db_1 |
ORACLE_SID | primary | standby |
闪回区 | 2G | 2G |
归档 | 开启 | 开启 |
2 主数据库配置
2.1 设置数据库强制归档
sqlplus / as sysdba
SQL> ALTER DATABASE FORCE LOGGING;
SQL> select force_logging from v$database;
FOR
---
YES
2.2 添加STANDBY日志文件
SQL>alter database add standby logfile group 4 ('+RAC_DISK/primary/onlinelog/standby41.log','+RAC_DISK/primary/onlinelog/standby42.log') size 50m;
SQL>alter database add standby logfile group 5 ('+RAC_DISK/primary/onlinelog/standby51.log','+RAC_DISK/primary/onlinelog/standby52.log') size 50m;
SQL>alter database add standby logfile group 6 ('+RAC_DISK/primary/onlinelog/standby61.log','+RAC_DISK/primary/onlinelog/standby62.log') size 50m;
SQL>alter database add standby logfile group 7 ('+RAC_DISK/primary/onlinelog/standby71.log','+RAC_DISK/primary/onlinelog/standby72.log') size 50m;
SQL> select * from v$logfile order by 1;
2.3 修改参数文件
2.3.1 生成pfile
SQL>create pfile from spfile;
SQL>shutdown immediate;
2.3.2 修改pfile
vi $ORACLE_HOME/dbs/initprimary.ora
在最后添加如下内容:
primary.__db_cache_size=268435456
primary.__java_pool_size=4194304
primary.__large_pool_size=4194304
primary.__shared_pool_size=121634816
primary.__streams_pool_size=0
*.audit_file_dest='/opt/oracle10g/admin/primary/adump'
*.audit_trail='db'
*.compatible='10.2.0.1.0'
*.db_block_size=8192
*.db_domain=''
*.db_name='primary'
*.db_recovery_file_dest='+RAC_DISK'
*.db_recovery_file_dest_size=2097152
*.diagnostic_dest='+RAC_DISK'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=primaryXDB)'
*.log_archive_dest_1='location=+RAC_DISK/primary'
*.log_archive_format='%t_%s_%r.dbf'
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'
*.db_unique_name=primary
*.fal_server='standby'
*.fal_client='primary'
*.standby_file_management=auto
*.db_file_name_convert='/home/app/oracle/oradata/standby/','+RAC_DISK/primary/datafile/'
*.log_file_name_convert='/home/app/oracle/oradata/standby/','+RAC_DISK/primary/onlinelog/'
*.log_archive_config='dg_config=(primary,standby)'
*.log_archive_dest_2='service=standby LGWR SYNC AFFIRM valid_for=(online_logfiles,primary_role)'
2.3.3 生成spfile
SQL> create spfile from pfile;
File created.
2.4 修改监听配置文件
2.5 修改TNS配置文件
2.6 重启监听服务
lsnrctl stop
lsnrctl start
2.7 配置最大可用模式
SQL> startup
ORACLE instance started
SQL> alter database set standby database to maximize availability;
Database altered.
SQL> exit
2.8 备份数据库
---------->>rman备份:
RMAN> RUN {
allocate channel c1 type disk;
allocate channel c2 type disk;
sql 'alter system archive log current';
backup current controlfile for standby format='/home/oracle/ctl_%U';
BACKUP FORMAT '/home/oracle/db_%U_%T' skip inaccessible filesperset 5 DATABASE ;
sql 'alter system archive log current';
BACKUP FORMAT '/home/oracle/arch_%U_%T' skip inaccessible filesperset 5 ARCHIVELOG ALL DELETE INPUT;
release channel c2;
release channel c1;
}
---------->>通过scp命令命令将备份文件传输到备库相同目录下:
3 备数据库配置
3.1 建立相应的文件目录
包括dump文件目录,审计区,日志区,闪回区,数据文件目录,可以通过 show parameter dest命令查看(这里都是通过oracle用户创建)
mkdir -p /home/app/oracle/admin/standby/adump;
mkdir -p /home/app/oracle/admin/standby/udump;
mkdir -p /home/app/oracle/admin/standby/bdump;
mkdir -p /home/app/oracle/diag/rdbms/standby/standby/trace
mkdir -p /home/app/oracle/flash_recovery_area
mkdir -p /home/app/oracle/oradata/standby
3.2 修改监听配置文件
修改ip地址
3.3 修改TNS配置文件
修改后的tnsnames.ora文件内容:
3.4 重启监听服务
lsnrctl stop
lsnrctl start
3.5 主备库互相tnsping
--------->>从库tnsping主库
----------------->>主- --------->>库tnsping从库
3.6 修改参数文件
[oracle@dg2 admin]$ sqlplus / as sysdba
SQL> shutdown immediate;
vi $ORACLE_HOME/dbs/initstandby.ora
其中标红的部分都是需要调整和备库对应的:
SQL> create spfile from pfile;
3.7 启动从库到nomount,然后主库远程做RMAN恢复:
SQL> startup nomount pfile=/home/app/oracle/product/10.2.0/db_1/dbs/initstandby.ora;
-->>主库远程连接从库做recovery:
[oracle@rac1 ~]$ rman target / auxiliary sys/sys@standby
Recovery Manager: Release 10.2.0.1.0 - Production on Thu Jul 17 08:58:36 2014
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: PRIMARY (DBID=1690390844)
connected to auxiliary database: PRIMARY (not mounted)
RMAN> run
2> {
3> allocate channel c2 device type disk format '/home/oracle/ctl_%U' connect sys/sys@primary;
4> allocate channel c1 device type disk format '/home/oracle/db_%U_%T' connect sys/sys@primary;
5> allocate channel c3 device type disk format '/home/oracle/arch_%U_%T' connect sys/sys@primary;
6> allocate auxiliary channel ac2 device type disk format '/home/oracle/ctl_%U';
7> allocate auxiliary channel ac1 device type disk format '/home/oracle/db_%U_%T';
8> allocate auxiliary channel ac3 device type disk format '/home/oracle/arch_%U_%T';
9> duplicate target database for standby nofilenamecheck dorecover;
10> }
using target database control file instead of recovery catalog
allocated channel: c2
channel c2: sid=156 devtype=DISK
allocated channel: c1
channel c1: sid=128 devtype=DISK
allocated channel: c3
channel c3: sid=130 devtype=DISK
allocated channel: ac2
channel ac2: sid=36 devtype=DISK
allocated channel: ac1
channel ac1: sid=35 devtype=DISK
allocated channel: ac3
channel ac3: sid=34 devtype=DISK
Starting Duplicate Db at 19-JUL-14
contents of Memory Script:
{
set until scn 581814;
restore clone standby controlfile;
sql clone 'alter database mount standby database';
}
executing Memory Script
executing command: SET until clause
Starting restore at 19-JUL-14
channel ac2: starting datafile backupset restore
channel ac2: restoring control file
channel ac2: reading from backup piece /home/oracle/ctl_0epdpi2j_1_1
channel ac2: restored backup piece 1
piece handle=/home/oracle/ctl_0epdpi2j_1_1 tag=TAG20140719T120642
channel ac2: restore complete, elapsed time: 00:00:02
output filename=/home/app/oracle/oradata/standby/control01.ctl
Finished restore at 19-JUL-14
sql statement: alter database mount standby database
contents of Memory Script:
{
set until scn 581814;
set newname for tempfile 1 to
"+RAC_DISK/primary/tempfile/temp.282.852908177";
switch clone tempfile all;
set newname for datafile 1 to
"/home/app/oracle/oradata/standby/system.270.852908093";
set newname for datafile 2 to
"/home/app/oracle/oradata/standby/undotbs1.272.852908095";
set newname for datafile 3 to
"/home/app/oracle/oradata/standby/sysaux.271.852908095";
set newname for datafile 4 to
"/home/app/oracle/oradata/standby/users.273.852908097";
restore
check readonly
clone database
;
}
executing Memory Script
executing command: SET until clause
executing command: SET NEWNAME
renamed temporary file 1 to +RAC_DISK/primary/tempfile/temp.282.852908177 in control file
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting restore at 19-JUL-14
channel ac2: starting datafile backupset restore
channel ac2: specifying datafile(s) to restore from backup set
restoring datafile 00002 to /home/app/oracle/oradata/standby/undotbs1.272.852908095
restoring datafile 00003 to /home/app/oracle/oradata/standby/sysaux.271.852908095
channel ac2: reading from backup piece /home/oracle/db_0gpdpi2l_1_1_20140719
channel ac1: starting datafile backupset restore
channel ac1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /home/app/oracle/oradata/standby/system.270.852908093
restoring datafile 00004 to /home/app/oracle/oradata/standby/users.273.852908097
channel ac1: reading from backup piece /home/oracle/db_0fpdpi2l_1_1_20140719
channel ac2: restored backup piece 1
piece handle=/home/oracle/db_0gpdpi2l_1_1_20140719 tag=TAG20140719T120645
channel ac2: restore complete, elapsed time: 00:00:28
channel ac1: restored backup piece 1
piece handle=/home/oracle/db_0fpdpi2l_1_1_20140719 tag=TAG20140719T120645
channel ac1: restore complete, elapsed time: 00:00:28
Finished restore at 19-JUL-14
contents of Memory Script:
{
switch clone datafile all;
}
executing Memory Script
datafile 1 switched to datafile copy
input datafile copy recid=5 stamp=853338716 filename=/home/app/oracle/oradata/standby/system.270.852908093
datafile 2 switched to datafile copy
input datafile copy recid=6 stamp=853338716 filename=/home/app/oracle/oradata/standby/undotbs1.272.852908095
datafile 3 switched to datafile copy
input datafile copy recid=7 stamp=853338716 filename=/home/app/oracle/oradata/standby/sysaux.271.852908095
datafile 4 switched to datafile copy
input datafile copy recid=8 stamp=853338716 filename=/home/app/oracle/oradata/standby/users.273.852908097
contents of Memory Script:
{
set until scn 581814;
recover
standby
clone database
delete archivelog
;
}
executing Memory Script
executing command: SET until clause
Starting recover at 19-JUL-14
starting media recovery
channel ac2: starting archive log restore to default destination
channel ac2: restoring archive log
archive log thread=1 sequence=18
channel ac2: restoring archive log
archive log thread=1 sequence=19
channel ac2: reading from backup piece /home/oracle/arch_0lpdpi42_1_1_20140719
channel ac2: restored backup piece 1
piece handle=/home/oracle/arch_0lpdpi42_1_1_20140719 tag=TAG20140719T120705
channel ac2: restore complete, elapsed time: 00:00:02
archive log filename=/home/app/oracle/oradata/arch_dest/1_18_852908159.arc thread=1 sequence=18
channel clone_default: deleting archive log(s)
archive log filename=/home/app/oracle/oradata/arch_dest/1_18_852908159.arc recid=1 stamp=853338718
archive log filename=/home/app/oracle/oradata/arch_dest/1_19_852908159.arc thread=1 sequence=19
channel clone_default: deleting archive log(s)
archive log filename=/home/app/oracle/oradata/arch_dest/1_19_852908159.arc recid=2 stamp=853338718
media recovery complete, elapsed time: 00:00:03
Finished recover at 19-JUL-14
Finished Duplicate Db at 19-JUL-14
released channel: c2
released channel: c1
released channel: c3
released channel: ac2
released channel: ac1
released channel: ac3
RMAN>
从日志可以看到,oracle先根据参数文件把控制文件恢复到合适位置,然后再根据db_file_name_cover把数据文件恢复到合适位置
3.8 恢复备库并创建standby logfile
--->>主库当前序列号
--->>恢复备库:
3.9 主库切换日志,观察从库日志:
SQL> alter system switch logfile;
System altered.
3.10 主库进行数据修改,备库以只读方式查询测试:
-->>主库做修改
-->>从库以只读方式读取主库数据:
3.11 把主库切换到备库
----主库
SQL> select instance_name,status from v$instance;
INSTANCE_NAME STATUS
---------------- ------------
primary OPEN
SQL> alter database commit to switchover to physical standby;
Database altered.
SQL> alter database commit to switchover to physical standby with session shutdown;
alter database commit to switchover to physical standby with session shutdown
*
ERROR at line 1:
ORA-01507: database not mounted
SQL> alter database commit to switchover to physical standby with session shutdown;
alter database commit to switchover to physical standby with session shutdown
*
ERROR at line 1:
ORA-01507: database not mounted
SQL> select instance_name,status from v$instance;
INSTANCE_NAME STATUS
---------------- ------------
primary STARTED
SQL> alter database mount;
alter database mount
*
ERROR at line 1:
ORA-01100: database already mounted
SQL> shutdown immediate;
ORA-01507: database not mounted
ORACLE instance shut down.
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 402653184 bytes
Fixed Size 1219664 bytes
Variable Size 125830064 bytes
Database Buffers 272629760 bytes
Redo Buffers 2973696 bytes
SQL> alter database mount standby database;
Database altered.
SQL> alter database recover managed standby database disconnect from session;
Database altered.
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination +RAC_DISK/primary/
Oldest online log sequence 30
Next log sequence to archive 0
Current log sequence 31
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options
[oracle@rac1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Sat Jul 19 13:05:01 2014
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options
SQL> select instance_name,status from v$instance;
INSTANCE_NAME STATUS
---------------- ------------
primary MOUNTED
--备库
SQL> select instance_name,status from v$instance;
INSTANCE_NAME STATUS
---------------- ------------
standby OPEN
SQL> alter database commit to switchover to primary;
Database altered.
SQL> shutdown immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> startup;
ORACLE instance started.
Total System Global Area 839282688 bytes
Fixed Size 2217992 bytes
Variable Size 557844472 bytes
Database Buffers 276824064 bytes
Redo Buffers 2396160 bytes
Database mounted.
Database opened.
SQL> show user;
USER is "SYS"
SQL>create table lyl_dept as select * from scott.dept;
SQL> select * from lyl_dept;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SQL> delete from lyl_dept where deptno=10;
1 row deleted.
SQL> commit;
Commit complete.
-----顺利完成切换,并可在备库进行正常更新操作.
3.12 为备库创建tempfile
10G下面不需要给备库建立临时文件。因为当它在备用状态时候,用不到临时文件,当切换的时候,它会自动建立临时文件.如果是生产库,个人感觉还是在备库创建好临时文件.
SQL> select instance_name,status from v$instance;
INSTANCE_NAME STATUS
---------------- ------------
standby MOUNTED
SQL> select name from v$tempfile;
NAME
--------------------------------------------------------------------------------
+RAC_DISK
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
Database altered.
SQL> ALTER DATABASE OPEN READ ONLY;
Database altered.
SQL> ALTER TABLESPACE temp ADD TEMPFILE '/home/app/oracle/oradata/standby/temp.dbf' SIZE 10M REUSE;
Tablespace altered.
SQL> select name from v$tempfile;
NAME
--------------------------------------------------------------------------------
+RAC_DISK
/home/app/oracle/oradata/standby/temp.dbf
SQL>
3.13 遇到的问题及解决办法
其实搭建dg不是很难,搭建过程中主要是由于没注意细节,导致在一些环节浪费了不少时间,主要有:
(1)主库远程连接备库不能,报错信息如下:
[oracle@rac1 ~]$ rman target / auxiliary sys/sys@standby
Recovery Manager: Release 10.2.0.1.0 - Production on Wed Jul 16 15:09:50 2014
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: PRIMARY (DBID=1690390844)
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00554: initialization of internal recovery manager package failed
RMAN-04006: error from auxiliary database: ORA-12528: TNS:listener: all appropriate instances are blocking new connections
-->>主要问题是此时备库只是处于nomount状态,注册监听需要是静态的才可以.
(2)tempfile恢复报错,大概如下示:
sql statement: alter database mount standby database
released channel: c2
released channel: c1
released channel: ac2
released channel: ac1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 07/17/2014 06:32:00
RMAN-05501: aborting duplication of target database
RMAN-05517: temporary file +RAC_DISK/primary/tempfile/temp.282.852908177 conflicts with file used by target database
-->>解决方法:
在主库使用RMAN的Duplicate创建dg,如果数据文件与重做日志文件目录相同,要添加 nofilenamecheck ,否则不需要添加
(3)备库内存不足报错
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 07/19/2014 12:22:47
RMAN-03015: error occurred in stored script Memory Script
RMAN-06136: ORACLE error from auxiliary database: ORA-04031: unable to allocate 396 bytes of shared memory ("shared pool","select inst_id,alrid,alstm,a...","Typecheck","seg:kggfaAllocSeg")
-->>解决:增大sga。这种问题其实不应该有,细节问题.
(4)恢复中途中错,大概信息如下:
-->>分析解决:通过查看后台数据,发现日志能归档成功,也姝应用日志,alert中报说与从库失去连接,导致恢复进行不下去。后来发现在主库恢复过程中从库开启的连接还没有exit。Shutdown从库,清理完恢复过去的数据文 件和控制文件,然后重新在主库做远程恢复就没问题了.
4.参考资料: -->>关于主从库都是普通文件系统且未用rman做备库恢复请参见: -->>其他网友分享的搭建dg好贴子请参见:来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29119536/viewspace-1224061/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/29119536/viewspace-1224061/