一、前期准备
在准备搭建DataGuard的时候,看过许多篇教程,按着教程去走,出现过问题,然后就不知道该如何进行下一步。
后来去看了官方文档的,整理一下思路后事情就简单多了。
以一主主库一备库为例,搭建DG的思路如下:
关闭防火墙。
配置ssh互信(非必须,减少SCP时输入密码的次数)
主库上的操作:
1)开启归档模式,开启forced logging;
2)添加standby logfile,standby logfile组比logfile组多一个;
3)修改参数文件
4)配置listener.ora 和tnsnames.ora
5)备份数据库和控制文件
备库准备:
1)将主库上的参数文件、密码文件拷贝到备库上,修改参数文件;
2)将主库上的listener.ora 和tnsnames.ora复制到备库上,修改listener.ora文件
3)将备份复制到备库上
4)在备库上创建对应的文件夹
5)启动备库到nomount模式
6)在备库上使用RMAN恢复数据库
7)备库上添加standby logfile
8)应用归档
9)查看状态
二、基本配置准备
2.1 primary服务器安装数据库软件并安装ORCL数据库,standby服务器上安装
2.2 关闭防火墙
primary服务器关闭防火墙:
standby服务器关闭防火墙
2.3 配置ssh互信
百度下“linux scp免密码”,随便找篇文章看下。
[Oracle@primary dbs]$ ssh-keygen -t rsa
[oracle@primary dbs]$ scp ~/.ssh/id_rsa.pub oracle@192.168.62.202:/home/oracle/.ssh/authorized_keys
三、主库配置
1)enable forced logging:
SQL> ALTER DATABASE FORCE LOGGING;
查看是否已经是归档模式,如果是未归档模式,则将其修改成归档模式。
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MOUNT;
SQL> ALTER DATABASE ARCHIVELOG;
SQL> ALTER DATABASE OPEN;
3)配置standby logfile文件,当主库变成备库时,可以接收来自备库的日志。
alter database add standby logfile group 4 ('/u01/app/oracle/oradata/orcl/redo_dg_021.log') size 50M;
alter database add standby logfile group 5 ('/u01/app/oracle/oradata/orcl/redo_dg_022.log') size 50M;
alter database add standby logfile group 6 ('/u01/app/oracle/oradata/orcl/redo_dg_023.log') size 50M;
alter database add standby logfile group 7 ('/u01/app/oracle/oradata/orcl/redo_dg_024.log') size 50M;
3)配置主库的参数:这里有两种方式,一种是使用alter system 来修改参数,另外一种是生成静态参数文件之后修改参数;我采用的是后一种配置方式。
参数的配置参考Oracle Data Guard Concepts and Administration 11g Release 2 (11.2) E41134-03中3-3和3-4页
initorcl.ora已经有的参数,可以不用配置。
SQL> create pfile from spfile;
[oracle@primary ~]$ cd $ORACLE_HOME/dbs
[oracle@primary dbs]$ vim initorcl.ora
#primary
#*.db_name='orcl'
*.db_unique_name=pri
*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(pri,sty)'
#*.control_files='/u01/app/oracle/oradata/orcl/control01.ctl','/u01/app/oracle/oradata/orcl/control02.ctl'
*.LOG_ARCHIVE_DEST_1= 'LOCATION=/u01/app/oracle/arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=pri'
*.LOG_ARCHIVE_DEST_2= 'SERVICE=sty ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=sty'
*.LOG_ARCHIVE_DEST_STATE_1=ENABLE
*.LOG_ARCHIVE_DEST_STATE_2=ENABLE
*.remote_login_passwordfile='EXCLUSIVE'
*.LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
#standby role
*.FAL_SERVER=sty
*.DB_FILE_NAME_CONVERT='/u01/app/oracle/oradata/orcl','/u01/app/oracle/oradata/orcl'
*.LOG_FILE_NAME_CONVERT= '/u01/app/oracle/oradata/orcl','/u01/app/oracle/oradata/orcl'
*.STANDBY_FILE_MANAGEMENT=AUTO
SQL> shutdown immediate
SQL> create spfile from pfile;
4)主库上配置listener.ora 和tnsnames.ora
主库上的listener.ora :
[oracle@primary admin]$ cat listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = ORCL)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
(SID_NAME = ORCL)
)
)
LISTENER =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.62.201)(PORT = 1521))
)
ADR_BASE_LISTENER = /u01/app/oracle
主库上的tnsnames.ora:
[oracle@primary admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
PRI =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.62.201)(PORT = 1521))
)
(CONNECT_DATA =
(SID = orcl)
)
)
STY =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.62.202)(PORT = 1521))
)
(CONNECT_DATA =
(SID = orcl)
)
)
修改完成之后要重启监听。
5)备份数据库和控制文件
[oracle@primary admin]$ rman target sys/asd@pri
RMAN> backup database format '/u01/app/oracle/backup/full_db_%U';
SQL> ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/u01/app/oracle/backup/sty.ctl';
四、备库上的操作
1)将主库上的参数文件、密码文件拷贝到备库上,修改参数文件;
[oracle@primary admin]$ cd $ORACLE_HOME/dbs
[oracle@primary dbs]$ scp initorcl.ora orapworcl oracle@192.168.62.202:/u01/app/oracle/product/11.2.0/dbhome_1/dbs/
修改参数文件:
vim /u01/app/oracle/product/11.2.0/dbhome_1/dbs/initorcl.ora
*.db_name='orcl'
*.DB_UNIQUE_NAME=sty
*.control_files='/u01/app/oracle/oradata/orcl/control01.ctl','/u01/app/oracle/flash_recovery_area/orcl/control02.ctl'
*.DB_FILE_NAME_CONVERT='/u01/app/oracle/oradata/orcl','/u01/app/oracle/oradata/orcl'
*.LOG_FILE_NAME_CONVERT= '/u01/app/oracle/oradata/orcl','/u01/app/oracle/oradata/orcl'
*.LOG_ARCHIVE_FORMAT=log%t_%s_%r.arc
*.LOG_ARCHIVE_DEST_1='LOCATION=/u01/app/oracle/arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=sty'
*.LOG_ARCHIVE_DEST_2='SERVICE=pri ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=pri'
*.LOG_ARCHIVE_DEST_STATE_1=ENABLE
*.LOG_ARCHIVE_DEST_STATE_2=ENABLE
*.REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
*.STANDBY_FILE_MANAGEMENT=AUTO
*.FAL_SERVER=pri
标红的是修改的部分
密码文件说明:主库上有授权或者收回sysdba时,要更新备库上的密码文件。
2)将主库上的listener.ora 和tnsnames.ora复制到备库上,修改listener.ora文件
主库上执行:
[oracle@primary admin]$ scp listener.ora tnsnames.ora oracle@192.168.62.202:/u01/app/oracle/product/11.2.0/dbhome_1/network/admin/
备库上执行:
[oracle@standby ~]$ cd /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/
[oracle@standby admin]$ vim listener.ora
将host中的201 改成202,也就是将host改成备库机器上的IP,tnsnames.ora不用修改。
修改完成之后要重启监听
3)将备份复制到备库上
[oracle@primary backup]$ scp /u01/app/oracle/backup/* oracle@192.168.62.202:/u01/app/oracle/backup
4)在备库上创建对应的文件夹
[oracle@standby oracle]$ cd $ORACLE_BASE
[oracle@standby oracle]$ ls
arch backup checkpoints database product
[oracle@standby oracle]$ mkdir -p oradata/orcl
[oracle@standby oracle]$ mkdir -p admin/orcl/adump
[oracle@standby oracle]$ mkdir -p admin/orcl/dpdump
[oracle@standby oracle]$ mkdir -p admin/orcl/pfile
5)启动备库到nomount模式
SQL>create spfile from pfile='/u01/app/oracle/product/11.2.0/dbhome_1/dbs/initorcl.ora';
SQL> startup nomount;
6)在备库上使用RMAN恢复数据库
[oracle@standby ~]$ rman target sys/asd@pri
RMAN> connect auxiliary /
run {
allocate auxiliary channel c1 device type disk;
allocate auxiliary channel c2 device type disk;
duplicate target database for standby nofilenamecheck dorecover;
release channel c1;
release channel c2;
}
虽然最后出现了这个问题,但备库还是可以打开的。
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 06/08/2016 17:38:36
RMAN-03015: error occurred in stored script Memory Script
RMAN-06053: unable to perform media recovery because of missing log
RMAN-06025: no backup of archived log for thread 1 with sequence 16 and starting SCN of 1010403 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 15 and starting SCN of 1009794 found to restore
7)备库上添加standby logfile
备库上添加standby logfile,我做测试时,v$logfile视图里有standby logfile group4 至group 7,而/u01/app/oracle/oradata/orcl并没有这些文件,所以要先把这几组日志删除再重建:
SQL>alter database open;#未试过在mount状态创建standby日志
SQL> alter database drop logfile group 4;
SQL> alter database drop logfile group 5;
SQL> alter database drop logfile group 6;
SQL> alter database drop logfile group 7;
SQL>alter database add standby logfile group 4 ('/u01/app/oracle/oradata/orcl/redo_dg_021.log') size 50M;
SQL>alter database add standby logfile group 5 ('/u01/app/oracle/oradata/orcl/redo_dg_022.log') size 50M;
SQL>alter database add standby logfile group 6 ('/u01/app/oracle/oradata/orcl/redo_dg_023.log') size 50M;
SQL>alter database add standby logfile group 7 ('/u01/app/oracle/oradata/orcl/redo_dg_024.log') size 50M;
8)应用归档
--应用归档
SQL>shutdown immediate;
SQL>startup mount;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;
--退出归档
alter database recover managed standby database cancel;
9)查看状态
SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME,APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
SEQUENCE# FIRST_TIME NEXT_TIME APPLIED
---------- ------------------ ------------------ ---------
15 08-JUN-16 08-JUN-16 YES
16 08-JUN-16 08-JUN-16 YES
17 08-JUN-16 08-JUN-16 YES
18 08-JUN-16 08-JUN-16 YES
19 08-JUN-16 08-JUN-16 YES
20 08-JUN-16 08-JUN-16 YES
21 08-JUN-16 08-JUN-16 IN-MEMORY
说明DG已经搭建成功
注:如果备库处于open状态,告警日志中会出现:ORA-16058: 未装载备用数据库实例的告警信息。
Oracle 11g Data Guard Error 16143 Heartbeat failed to connect to standby 处理方法 http://www.linuxidc.com/Linux/2013-03/82009.htm