oracle11g搭建主从集群

安装oracle11g参考：centos安装oracle11g数据库-优快云博客

一、主库操作

sqlplus / as sysdba

1、开启归档模式和强制日志模式

shutdown immediate
startup mount
#开启归档模式和强制日志模式
alter database archivelog;
alter database force logging;
#开启补充日志
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;
#备库创建redo文件（比主库多一组，所以创建4个）
ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 ('/u01/app/oracle/oradata/orcl/redo04.log') size 50M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 ('/u01/app/oracle/oradata/orcl/redo05.log') size 50M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 6 ('/u01/app/oracle/oradata/orcl/redo06.log') size 50M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 7 ('/u01/app/oracle/oradata/orcl/redo07.log') size 50M;
#创建pfile文件
create pfile from spfile;
shutdown immediate
exit

2、创建文件夹

mkdir /u01/app/oracle/oradata/orcl/archivelog
mkdir /u01/app/oracle/oradata/orcl/backup

3、配置监听器

vim /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora

增加内容 

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = orcl.com)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
      (SID_NAME = orcl) 
    )
  )
解释：#SID_NAME = orcl 就是在安装oracle软件的配置文件db_install.rsp 和创建数据库的配置文件dbca.rsp 中配置的SID

4、配置tns

vim /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora

增加

ORCL_SLAVE = 
  (DESCRIPTION = 
    (ADDRESS_LIST = 
      (ADDRESS = (PROTOCOL = TCP)(HOST = 172.21.16.4)(PORT = 1521)) 
    ) 
    (CONNECT_DATA = 
      (SERVICE_NAME =orcl) 
      (UR=A)
    ) 
  ) 
#该链接配置主库链接，和上面一样的
ORCL_MASTER =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 172.21.16.13)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME =orcl)
      (UR=A)
    )
  )

5、重启监听器并ping测试

lsnrctl stop
lsnrctl start

tnsping测试
tnsping ORCL_MASTER
tnsping ORCL_SLAVE

6、修改pfile配置文件

vim /u01/app/oracle/product/11.2.0/dbhome_1/dbs/initorcl.ora

文件尾添加：

DB_UNIQUE_NAME=orcl_master 
LOG_ARCHIVE_CONFIG='DG_CONFIG=(orcl_master,orcl_slave)' 
LOG_ARCHIVE_DEST_1= 
 'LOCATION=/u01/app/oracle/oradata/orcl/archivelog
  VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
  DB_UNIQUE_NAME=orcl_master' 
LOG_ARCHIVE_DEST_2= 
 'SERVICE=orcl_slave ASYNC 
  VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
  DB_UNIQUE_NAME=orcl_slave' 
LOG_ARCHIVE_DEST_STATE_1=ENABLE 
LOG_ARCHIVE_DEST_STATE_2=ENABLE 
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE 
LOG_ARCHIVE_FORMAT=%t_%s_%r.arc 
FAL_SERVER=orcl_slave 
STANDBY_FILE_MANAGEMENT=AUTO

7、创建spfile

sqlplus / as sysdba

startup nomount pfile='/u01/app/oracle/product/11.2.0/dbhome_1/dbs/initorcl.ora'
create spfile from pfile;
alter database mount;
alter database open;

8、传输口令验证文件和pfile文件到备库

scp /u01/app/oracle/product/11.2.0/dbhome_1/dbs/initorcl.ora oracle@172.21.16.4:/u01/app/oracle/product/11.2.0/dbhome_1/dbs/
scp /u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapworcl oracle@172.21.16.4:/u01/app/oracle/product/11.2.0/dbhome_1/dbs/

 二、备库操作

1、创建目录

mkdir /u01/app/oracle/oradata/orcl/backup

2、修改监听配置和tns配置（同主库）

vim /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
vim /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora

3、手动注册数据库实例到监听器

sqlplus / as sysdba

alter system register;

4、修改pfile文件

vim /u01/app/oracle/product/11.2.0/dbhome_1/dbs/initorcl.ora 

文件尾修改：

*.log_file_name_convert='/u01/app/oracle/oradata/orcl','/u01/app/oracle/oradata/orcl'
*.db_file_name_convert='/u01/app/oracle/oradata/orcl','/u01/app/oracle/oradata/orcl'
DB_UNIQUE_NAME=orcl_slave
LOG_ARCHIVE_CONFIG='DG_CONFIG=(orcl_master,orcl_slave)'
LOG_ARCHIVE_DEST_1= 
 'LOCATION=/u01/app/oracle/oradata/orcl/archivelog
  VALID_FOR=(ALL_LOGFILES,ALL_ROLES) 
  DB_UNIQUE_NAME=orcl_slave' 
LOG_ARCHIVE_DEST_2= 
 'SERVICE=orcl_slave ASYNC 
  VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) 
  DB_UNIQUE_NAME=orcl_master' 
LOG_ARCHIVE_DEST_STATE_1=ENABLE 
LOG_ARCHIVE_DEST_STATE_2=ENABLE 
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE 
LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
FAL_SERVER=orcl_master
STANDBY_FILE_MANAGEMENT=AUTO

5、启动备库到nomount状态

sqlplus / as sysdba

shutdown immediate
startup nomount pfile='/u01/app/oracle/product/11.2.0/dbhome_1/dbs/initorcl.ora'
quit

6、rman同步主库数据

rman target sys/123@orcl_master auxiliary sys/123@orcl_slave
开始同步
duplicate target database for standby from active database nofilenamecheck;
exit;

7、备库开启apply service

sqlplus / as sysdba

alter database open;
select open_mode from v$database;
alter database recover managed standby database disconnect from session;

8、修改成最大可用性模式（实时同步）

ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=orcl_slave ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcl_slave';
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE;

停止同步

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

恢复同步

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE disconnect from session USING CURRENT LOGFILE;

三、测试