CentOS6.5+Oracle11.2.0.4的ADG环境搭建

预先下载

1.VMware workstation

2. CentOS 6.5

3. linux Oracle 11.2.0.4

链接&＃xff1a;https://pan.baidu.com/s/1_VaYV-uFO06k_49A_U8y9A 提取码&＃xff1a;iqax

安装

1.安装CentOS6.5虚拟机&＃xff0c;IP 192.168.18.23&＃xff0c;作为 primary&＃xff08;主机&＃xff09;

2.安装并配置单实例Oracle 11.2.0.4

3.完成以上两步后&＃xff0c;关闭虚拟机&＃xff0c;然后克隆新虚拟机&＃xff0c;IP 192.168.18.24 作为standby&＃xff08;备机&＃xff09;

ADG安装

1. Primary主机配置

开启归档模式

SQL>shutdown immediate;SQL>startup mount;
设置强制日志模式
SQL>alter database force logging;
设置为归档模式
SQL>alter database archivelog;SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /opt/oracle/oradata/orcl
Oldest online log sequence 17
Next log sequence to archive 19
Current log sequence 19
SQL>

添加standby redo log

       查看online redo log

select * from v$log;

      添加的standby redo log比online redo log多一组

alter database add standby logfile group 4 (&＃39;/opt/oracle/oradata/orcl/standby_log04.log&＃39;) size 50M;
alter database add standby logfile group 5 (&＃39;/opt/oracle/oradata/orcl/standby_log05.log&＃39;) size 50M;
alter database add standby logfile group 6 (&＃39;/opt/oracle/oradata/orcl/standby_log06.log&＃39;) size 50M;
alter database add standby logfile group 7 (&＃39;/opt/oracle/oradata/orcl/standby_log07.log&＃39;) size 50M;

      查看standby redo log

select * from v$standby_log;

2. TNS通信配置&＃xff08;主备&＃xff09;

主备分别配置listner.ora tnsname.ora

#表示主备机器的实际IP

listner.ora

# listener.ora Network Configuration File: /opt/oracle/product/OraHome/network/admin/listener.ora
# Generated by Oracle configuration tools.SID_LIST_LISTENER &＃61;(SID_LIST &＃61;(SID_DESC &＃61;(GLOBAL_DBNAME &＃61; orcl)(ORACLE_HOME &＃61; /opt/oracle/product/OraHome)(SID_NAME &＃61; orcl)))LISTENER &＃61;(DESCRIPTION_LIST &＃61;(DESCRIPTION &＃61;(ADDRESS &＃61; (PROTOCOL &＃61; IPC)(KEY &＃61; EXTPROC1521))(ADDRESS &＃61; (PROTOCOL &＃61; TCP)(HOST &＃61; 192.168.18.2#)(PORT &＃61; 1521))))ADR_BASE_LISTENER &＃61; /opt/oracle

tnsname.ora

# tnsnames.ora Network Configuration File: /opt/oracle/product/OraHome/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.LISTENER_ORCL &＃61;(ADDRESS &＃61; (PROTOCOL &＃61; TCP)(HOST &＃61; 192.168.18.2#)(PORT &＃61; 1521))main &＃61;(DESCRIPTION &＃61;(ADDRESS &＃61; (PROTOCOL &＃61; TCP)(HOST &＃61; 192.168.18.23)(PORT &＃61; 1521))(CONNECT_DATA &＃61;(SERVER &＃61; DEDICATED)(SERVICE_NAME &＃61; orcl)))back &＃61;(DESCRIPTION &＃61;(ADDRESS &＃61; (PROTOCOL &＃61; TCP)(HOST &＃61; 192.168.18.24)(PORT &＃61; 1521))(CONNECT_DATA &＃61;(SERVER &＃61; DEDICATED)(SERVICE_NAME &＃61; orcl)))

重启监听

[oracle&＃64;cent6 admin]$ lsnrctl stopLSNRCTL for Linux: Version 11.2.0.4.0 - Production on 30-NOV-2020 08:31:20Copyright (c) 1991, 2013, Oracle. All rights reserved.Connecting to (DESCRIPTION&＃61;(ADDRESS&＃61;(PROTOCOL&＃61;IPC)(KEY&＃61;EXTPROC1521)))
The command completed successfully
[oracle&＃64;cent6 admin]$ lsnrctl startLSNRCTL for Linux: Version 11.2.0.4.0 - Production on 30-NOV-2020 08:31:26Copyright (c) 1991, 2013, Oracle. All rights reserved.Starting /opt/oracle/product/OraHome/bin/tnslsnr: please wait...TNSLSNR for Linux: Version 11.2.0.4.0 - Production
System parameter file is /opt/oracle/product/OraHome/network/admin/listener.ora
Log messages written to /opt/oracle/diag/tnslsnr/cent6/listener/alert/log.xml
Listening on: (DESCRIPTION&＃61;(ADDRESS&＃61;(PROTOCOL&＃61;ipc)(KEY&＃61;EXTPROC1521)))
Listening on: (DESCRIPTION&＃61;(ADDRESS&＃61;(PROTOCOL&＃61;tcp)(HOST&＃61;192.168.18.23)(PORT&＃61;1521)))Connecting to (DESCRIPTION&＃61;(ADDRESS&＃61;(PROTOCOL&＃61;IPC)(KEY&＃61;EXTPROC1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date 30-NOV-2020 08:31:26
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /opt/oracle/product/OraHome/network/admin/listener.ora
Listener Log File /opt/oracle/diag/tnslsnr/cent6/listener/alert/log.xml
Listening Endpoints Summary...(DESCRIPTION&＃61;(ADDRESS&＃61;(PROTOCOL&＃61;ipc)(KEY&＃61;EXTPROC1521)))(DESCRIPTION&＃61;(ADDRESS&＃61;(PROTOCOL&＃61;tcp)(HOST&＃61;192.168.18.23)(PORT&＃61;1521)))
Services Summary...
Service "orcl" has 1 instance(s).Instance "orcl", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
[oracle&＃64;cent6 admin]$

确认配置正确&＃xff0c;相互之间能tnsping通

[oracle&＃64;cent6 admin]$ tnsping mainTNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 30-NOV-2020 08:30:17Copyright (c) 1997, 2013, Oracle. All rights reserved.Used parameter files:
/opt/oracle/product/OraHome/network/admin/sqlnet.oraUsed TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION &＃61; (ADDRESS &＃61; (PROTOCOL &＃61; TCP)(HOST &＃61; 192.168.18.23)(PORT &＃61; 1521)) (CONNECT_DATA &＃61; (SERVER &＃61; DEDICATED) (SERVICE_NAME &＃61; orcl)))
OK (0 msec)


[oracle&＃64;cent6 admin]$ tnsping backTNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 30-NOV-2020 08:30:22Copyright (c) 1997, 2013, Oracle. All rights reserved.Used parameter files:
/opt/oracle/product/OraHome/network/admin/sqlnet.oraUsed TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION &＃61; (ADDRESS &＃61; (PROTOCOL &＃61; TCP)(HOST &＃61; 192.168.18.24)(PORT &＃61; 1521)) (CONNECT_DATA &＃61; (SERVER &＃61; DEDICATED) (SERVICE_NAME &＃61; orcl)))
OK (10 msec)
[oracle&＃64;cent6 admin]$

3.ADG配置文件准备&＃xff08;主备&＃xff09;

创建pfile

create pfile &＃61; &＃39;/opt/oracle/pfile&＃39; from spfile;

Primary&＃xff08;主&＃xff09;库修改pfile配置&＃xff0c;添加如下部分

*.db_unique_name&＃61;&＃39;main&＃39;
这个参数代表唯一名称&＃xff0c;设置成刚刚tnsnames配置的别名名称
*.log_archive_config&＃61;&＃39;dg_config&＃61;(main,back)&＃39;
归档配置&＃xff0c;参数里面需要填写两个库的db_unique_name。
*.log_archive_dest_1&＃61;&＃39;location&＃61;/opt/oracle/oradata/orcl valid_for&＃61;(all_logfiles,all_roles) db_unique_name&＃61;main&＃39;
这里dest_1 代表主库&＃xff0c;localtion 代表主库归档日志存放路径
*.log_archive_dest_2&＃61;&＃39;service&＃61;back lgwr async affirm valid_for&＃61;(online_logfiles,primary_role) db_unique_name&＃61;back&＃39;
这里dest_2代表备库&＃xff0c;service代表备库的 db_unique_name&＃xff0c;设置成刚刚tnsnames配置的别名名称
*.log_archive_dest_state_1&＃61;enable
*.log_archive_dest_state_2&＃61;enable
*.log_archive_max_processes&＃61;4
*.fal_server&＃61;&＃39;back&＃39;
*.fal_client&＃61;&＃39;main&＃39;
*.db_file_name_convert&＃61;&＃39;/opt/oracle/oradata/orcl&＃39;,&＃39;/opt/oracle/oradata/orcl&＃39;
*.log_file_name_convert&＃61;&＃39;/opt/oracle/oradata/orcl&＃39;,&＃39;/opt/oracle/oradata/orcl&＃39;
*.standby_file_management&＃61;&＃39;auto&＃39;

standby&＃xff08;备&＃xff09;库修改pfile配置&＃xff0c;添加如下部分

*.db_unique_name&＃61;&＃39;back&＃39;
*.log_archive_config&＃61;&＃39;dg_config&＃61;(main,back)&＃39;
*.log_archive_dest_1&＃61;&＃39;location&＃61;/opt/oracle/oradata/orcl valid_for&＃61;(all_logfiles,all_roles) db_unique_name&＃61;main&＃39;
*.log_archive_dest_2&＃61;&＃39;service&＃61;main lgwr async affirm valid_for&＃61;(online_logfiles,primary_role) db_unique_name&＃61;main&＃39;
*.log_archive_dest_state_1&＃61;enable
*.log_archive_dest_state_2&＃61;enable
*.log_archive_max_processes&＃61;4
*.fal_server&＃61;&＃39;main&＃39;
*.fal_client&＃61;&＃39;back&＃39;
*.db_file_name_convert&＃61;&＃39;/opt/oracle/oradata/orcl&＃39;,&＃39;/opt/oracle/oradata/orcl&＃39;
*.log_file_name_convert&＃61;&＃39;/opt/oracle/oradata/orcl&＃39;,&＃39;/opt/oracle/oradata/orcl&＃39;
*.standby_file_management&＃61;&＃39;auto&＃39;

Primary&＃xff08;主&＃xff09;库使用pfile重启

SQL>shutdown immediate;
SQL>startup pfile&＃61;&＃39;/opt/oracle/pfile&＃39;;

Standby&＃xff08;备&＃xff09;库使用pfile重启至nomount状态

SQL>shutdown immediate;
SQL>startup nomount pfile&＃61;&＃39;/opt/oracle/pfile&＃39;;

4.RMAN复制&＃xff08;仅Standby&＃xff08;备&＃xff09;库&＃xff09;

RMAN连接主备库

[oracle&＃64;cent6 admin]$ rman target sys/schina&＃64;main auxiliary sys/schina&＃64;backRecovery Manager: Release 11.2.0.4.0 - Production on Mon Nov 30 05:29:28 2020Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.connected to target database: ORCL (DBID&＃61;1585353176)
connected to auxiliary database: ORCL (not mounted)

使用RMAN开始复制

RMAN> duplicate target database for standby from active database nofilenamecheck;Starting Duplicate Db at 30-NOV-20
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID&＃61;129 device type&＃61;DISKcontents of Memory Script:
{backup as copy reusetargetfile &＃39;/opt/oracle/product/OraHome/dbs/orapworcl&＃39; auxiliary format &＃39;/opt/oracle/product/OraHome/dbs/orapworcl&＃39; ;
}
executing Memory ScriptStarting backup at 30-NOV-20
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID&＃61;201 device type&＃61;DISK
Finished backup at 30-NOV-20
省略部分。。。。

复制完成后&＃xff0c;Standby默认处于mount状态&＃xff0c;需要手动打开

SQL>alter database open;
数据库已更改。启用备库日志文件
SQL>alter database recover managed standby database using current logfile disconnect from session;
关闭备库日志文件
SQL>alter database recover managed standby database cancel;

5.确认日志序号&＃xff08;主备&＃xff09;

分别在主备执行archive log list&＃xff0c;确保主备的Current log sequence&＃xff08;当前日志序列&＃xff09;一致

SQL>archive log list

6.数据同步

在主库创建表T并插入数据&＃xff0c;然后在备库查看&＃xff0c;确认是否已同步

CREATE TABLE t(cno INT PRIMARY KEY,NAME VARCHAR2(20),sex CHAR(5),birthday DATE);
INSERT INTO t VALUES(1,&＃39;宋爱梅&＃39;,&＃39;女&＃39;,SYSDATE-1000);
INSERT INTO t VALUES(2,&＃39;王志芳&＃39;,&＃39;女&＃39;,SYSDATE-1234);
INSERT INTO t VALUES(3,&＃39;贾隽仙&＃39;,&＃39;女&＃39;,SYSDATE-1034);
INSERT INTO t VALUES(4,&＃39;刘振杰&＃39;,&＃39;男&＃39;,SYSDATE-1340);
INSERT INTO t VALUES(5,&＃39;郭卫东&＃39;,&＃39;男&＃39;,SYSDATE-1312);
INSERT INTO t VALUES(6,&＃39;崔红宇&＃39;,&＃39;男&＃39;,SYSDATE-1888);
INSERT INTO t VALUES(7,&＃39;马福平&＃39;,&＃39;男&＃39;,SYSDATE-1777);
INSERT INTO t VALUES(8,&＃39;冯红&＃39;,&＃39;女&＃39;,SYSDATE-1666);
INSERT INTO t VALUES(9,&＃39;穆增志&＃39;,&＃39;男&＃39;,SYSDATE-1555);
INSERT INTO t VALUES(10,&＃39;付金旺&＃39;,&＃39;男&＃39;,SYSDATE-1444);
COMMIT;

登录Standby&＃xff08;备&＃xff09;库&＃xff0c;查询T表

7.修改数据库启动为spfile

将pfile转换为spfile

SQL>create spfile from pfile&＃61;&＃39;/opt/oracle/pfile&＃39;

重启数据库

SQL>shutdown immediate;SQL>startup;

8.备份虚拟机

将主备虚拟机关机&＃xff0c;然后创建快照

问题排查

1.ORA-01153: an incompatible media recovery is active

2.ORA-16014: not archived, no available destinations