Oracle11G搭建DataGuard读写分离

1. 主备库服务器安装oracle 11.2.0.4

--主库配置
2. 开启归档模式，设置fast_recovery_data，开启日志模式
SQL> shutdown immediate;
SQL> startup mount;
SQL> alter database archivelog;
SQL> alter system set db_recovery_file_dest_size=250G;
SQL> alter database open;
SQL> alter database force logging;
SQL> archive log list;

3. 增大redo日志到2G
--新建临时redo日志文件
SQL> alter database add logfile group 4 ('/data/oradb/u01/app/oracle/oradata/spmg/redo04.log') size 10M;
SQL> alter database add logfile group 5 ('/data/oradb/u01/app/oracle/oradata/spmg/redo05.log') size 10M;
SQL> alter database add logfile group 6 ('/data/oradb/u01/app/oracle/oradata/spmg/redo06.log') size 10M;
--切换redo日志文件
SQL> alter system switch logfile;
SQL> alter system switch logfile;
--删除旧的redo日志文件
SQL> alter database drop logfile group 1;
SQL> alter database drop logfile group 2;
SQL> alter database drop logfile group 3;
$ rm -rf redo01.log;
$ rm -rf redo02.log;
$ rm -rf redo03.log;
--新建正式redo日志文件
SQL> alter database add logfile group 1 ('/data/oradb/u01/app/oracle/oradata/spmg/redo01.log') size 2048M;
SQL> alter database add logfile group 2 ('/data/oradb/u01/app/oracle/oradata/spmg/redo02.log') size 2048M;
SQL> alter database add logfile group 3 ('/data/oradb/u01/app/oracle/oradata/spmg/redo03.log') size 2048M;
--切换redo日志文件
SQL> alter system switch logfile;
SQL> alter system switch logfile;
--删除临时redo日志文件
SQL> alter database drop logfile group 4;
SQL> alter database drop logfile group 5;
SQL> alter database drop logfile group 6;
$ rm -rf redo04.log;
$ rm -rf redo05.log;
$ rm -rf redo06.log;
--查看效果
SQL> select thread#, group#, bytes/1024/1024 from v$log;

4. 创建standby redo log组。standby redo log组数>=(每个实例日志组个数+1) * 实例个数
SQL> alter database add standby logfile group 4 ('/data/oradb/u01/app/oracle/oradata/spmg/standby_redo04.log') size 2048M;
SQL> alter database add standby logfile group 5 ('/data/oradb/u01/app/oracle/oradata/spmg/standby_redo05.log') size 2048M;
SQL> alter database add standby logfile group 6 ('/data/oradb/u01/app/oracle/oradata/spmg/standby_redo06.log') size 2048M;
SQL> alter database add standby logfile group 7 ('/data/oradb/u01/app/oracle/oradata/spmg/standby_redo07.log') size 2048M;
--查看效果
select group#, sequence#, status, bytes/1024/1024 from v$standby_log;

5. 创建主库密码文件
$ su - oracle
$ orapwd file=/data/oradb/u01/app/oracle/product/11.2.0.4/db_1/dbs/orapwspmg password=M_F4_bpN38sf3j_z_B force=y

6. 配置spfile文件
SQL> show parameter spfile;
SQL> create pfile='/data/oradb/initspmg.ora' from spfile;
$ vim /data/oradb/initspmg.ora
--添加以下内容，注意修改SID，文件路径
*.db_unique_name='spmg_master'
*.fal_client='spmg_master'
*.fal_server='spmg_slave'
*.standby_file_management='AUTO'
*.log_archive_config='DG_CONFIG=(spmg_master,spmg_slave)'
*.log_archive_dest_1='location=/data/oradb/u01/app/oracle/oradata/spmg/archivelog/'
*.log_archive_dest_2='SERVICE=spmg_slave LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=spmg_slave'
*.log_archive_dest_state_1='ENABLE'
*.log_archive_dest_state_2='ENABLE'
$ mkdir /data/oradb/u01/app/oracle/oradata/spmg/archivelog/
SQL> shutdown immeidate;
SQL> create spfile from pfile='/data/oradb/initspmg.ora';
SQL> startup;

7.配置listener.ora
$ vim listener.ora
--如下配置
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = ecs007)(PORT = 12521))
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = CLRExtProc)
(ORACLE_HOME = /data/oradb/u01/app/oracle/product/11.2.0.4/db_1/)
(PROGRAM = extproc)
)
(SID_DESC =
(GLOBAL_DBNAME = SPMG)
(ORACLE_HOME = /data/oradb/u01/app/oracle/product/11.2.0.4/db_1/)
(SID_NAME = SPMG)
)
)
ADR_BASE_LISTENER = /data/oradb/u01/app/oracle
LOGGING_LISTENER = OFF

8. 配置tnsnames.ora
$ vim tnsnames.ora
--如下配置
SPMG =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 110.41.14.144)(PORT = 12521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = spmg)
)
)
SPMG_MASTER =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.4.3.2)(PORT = 12521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = spmg)
)
)
SPMG_SLAVE =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.4.3.3)(PORT = 12521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = spmg)
(UR = A)
)
)
--检查配置
$ tnsping spmg_slave

--备库配置-----------------------
9. 将主库中的密码文件，pfile文件，监听文件复制到备库
$ scp orapwspmg oracle@10.4.3.3:/data/oradb/u01/app/oracle/product/11.2.0.4/db_1/dbs/
$ scp /data/oradb/initspmg.ora oracle@10.4.3.3:/data/oradb/
$ cd /data/oradb/u01/app/oracle/product/11.2.0.4/db_1/network/admin
$ scp listener.ora oracle@10.4.3.3:/data/oradb/u01/app/oracle/product/11.2.0.4/db_1/network/admin
$ scp tnsnames.ora oracle@10.4.3.3:/data/oradb/u01/app/oracle/product/11.2.0.4/db_1/network/admin

10. 配置spfile文件
$ vim /data/oradb/initspmg.ora
--添加以下内容
*.db_unique_name='spmg_slave'
*.fal_client='spmg_slave'
*.fal_server='spmg_master'
*.standby_file_management='AUTO'
*.log_archive_config='DG_CONFIG=(spmg_master,spmg_slave)'
*.log_archive_dest_1='location=/data/oradb/u01/app/oracle/oradata/spmg/archivelog/'
*.log_archive_dest_2='SERVICE=spmg_master LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=spmg_master'
*.log_archive_dest_state_1='ENABLE'
*.log_archive_dest_state_2='ENABLE'
$ mkdir /data/oradb/u01/app/oracle/oradata/spmg/archivelog/
SQL> shutdown immeidate;
SQL> create spfile from pfile='/data/oradb/initspmg.ora';
SQL> startup;

11. 配置监听文件
$ vim listener.ora
--如下配置
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = ecs008)(PORT = 12521))
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = CLRExtProc)
(ORACLE_HOME = /data/oradb/u01/app/oracle/product/11.2.0.4/db_1/)
(PROGRAM = extproc)
)
(SID_DESC =
(GLOBAL_DBNAME = SPMG)
(ORACLE_HOME = /data/oradb/u01/app/oracle/product/11.2.0.4/db_1/)
(SID_NAME = SPMG)
)
)
ADR_BASE_LISTENER = /data/oradb/u01/app/oracle
LOGGING_LISTENER = OFF

12. 配置tnsnames.ora
$ vim tnsnames.ora
--如下配置
SPMG =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 121.37.29.171)(PORT = 12521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = spmg)
)
)
SPMG_MASTER =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.4.3.2)(PORT = 12521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = spmg)
)
)
SPMG_SLAVE =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.4.3.3)(PORT = 12521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = spmg)
(UR = A)
)
)
--检查配置
$ tnsping spmg_master

13. 创建目录
$su - oracle
$ mkdir /data/oradb/u01/app/oracle/admin/spmg/adump
$ mkdir /data/oradb/u01/app/oracle/admin/spmg/dbdump
$ mkdir /data/oradb/u01/app/oracle/admin/spmg/pfile
$ mkdir /data/oradb/u01/app/oracle/oradata/spmg
$ mkdir /data/oradb/u01/app/oracle/fast_recovery_area/spmg

14. 启动备库到nomount
SQL> shutdown immediate;
SQL> startup nomount;

15. 在主库上使用rman恢复备库
$ rrman target / auxiliary sys/M_F4_bpN38sf3j_z_B@spmg_slave nocatalog
RMAN> duplicate target database for standby from active database nofilenamecheck;

15.1 19C上恢复备库会报错 rman-05535 ora-01275
--备库在mount状态
SQL> recover managed standby database using current logfile disconnect;
SQL> recover managed standby database cancel;
SQL> alter database open;
SQL> recover managed standby database using current logfile disconnect;

16. 备库启动日志应用
--备库应为mount状态
SQL> select status from v$instance;
SQL> alter database recover managed standby database disconnect from session;
SQL> select sequence#, applied from v$archived_log order by 1;
--主库切换日志后，查看主库备库当前日志序列是否一致
SQL> alter system switch logfile;
SQL> archive log list;

17. 开启备库实时查询
--备库当前处于mount状态
SQL> select open_mode from v$database;
--取消备库的自动恢复
SQL> alter database recover managed standby database cancel;
--备库调整为 read only 状态
SQL> alter database open;
SQL> select open_mode from v$database;
--启动备库恢复
SQL> alter database recover managed standby database using current logfile disconnect;
--备库状态为 read only with apply
SQL> select open_mode from v$database;
--设置standby_file_management参数，自动同步主库数据库文件变更
SQL>alter system set standby_file_management = auto;

18. rman设置
--启用一旦归档日志应用到备库，则自动删除，主备库都需要设置
RMAN> configure archivelog deletion policy to applied on all standby;

-----------------------------------------------------------------------------
-----------------------------------------------------------------------------
SQL> alter system set db_unique_name = 'spmg_master' scope=spfile;
SQL> alter system set log_archive_config='DG_CONFIG=(spmg_master,spmg_slave)' scope=spfile;
SQL> alter system set log_archive_dest_1='LOCATION=/data/oradb/u01/app/oracle/oradata/spmg/archivelog/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=spmg_master' scope=both;
SQL> alter system set log_archive_dest_2='SERVICE=spmg_slave LGWR SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=spmg_slave' scope=spfile;
SQL> alter system set log_archive_dest_state_1 = ENABLE;
SQL> alter system set log_archive_dest_state_2 = ENABLE;
SQL> alter system set standby_file_management=auto scope=spfile;
SQL> alter system set fal_client='spmg_master' scope=both;
SQL> alter system set fal_server='spmg_slave' scope=both;