一、前期准备
在准备搭建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不用修改。
修改完成之后要重启监听