CentOS 6.5+Oracle 11.2.0.4的ADG环境搭建

预先下载

1.VMware workstation

2. CentOS 6.5

3. linux Oracle 11.2.0.4

链接：https://pan.baidu.com/s/1_VaYV-uFO06k_49A_U8y9A 提取码：iqax

安装

1.安装CentOS6.5虚拟机，IP 192.168.18.23，作为 primary（主机）

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

3.完成以上两步后，关闭虚拟机，然后克隆新虚拟机，IP 192.168.18.24 作为standby（备机）

 

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 ('/opt/oracle/oradata/orcl/standby_log04.log') size 50M;
alter database add standby logfile group 5 ('/opt/oracle/oradata/orcl/standby_log05.log') size 50M;
alter database add standby logfile group 6 ('/opt/oracle/oradata/orcl/standby_log06.log') size 50M;
alter database add standby logfile group 7 ('/opt/oracle/oradata/orcl/standby_log07.log') size 50M;

      查看standby redo log

select * from v$standby_log;

2. TNS通信配置（主备）

主备分别配置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 =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = orcl)
      (ORACLE_HOME = /opt/oracle/product/OraHome)
      (SID_NAME = orcl)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.18.2#)(PORT = 1521))
    )
  )

ADR_BASE_LISTENER = /opt/oracle

tnsname.ora

# tnsnames.ora Network Configuration File: /opt/oracle/product/OraHome/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

LISTENER_ORCL =
  (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.18.2#)(PORT = 1521))

main =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.18.23)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

back =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.18.24)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

重启监听

[oracle@cent6 admin]$ lsnrctl stop

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 30-NOV-2020 08:31:20

Copyright (c) 1991, 2013, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
The command completed successfully
[oracle@cent6 admin]$ lsnrctl start

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 30-NOV-2020 08:31:26

Copyright (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=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.18.23)(PORT=1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=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=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.18.23)(PORT=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@cent6 admin]$ 

确认配置正确，相互之间能tnsping通

[oracle@cent6 admin]$ tnsping main

TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 30-NOV-2020 08:30:17

Copyright (c) 1997, 2013, Oracle.  All rights reserved.

Used parameter files:
/opt/oracle/product/OraHome/network/admin/sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.18.23)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl)))
OK (0 msec)

[oracle@cent6 admin]$ tnsping back

TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 30-NOV-2020 08:30:22

Copyright (c) 1997, 2013, Oracle.  All rights reserved.

Used parameter files:
/opt/oracle/product/OraHome/network/admin/sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.18.24)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl)))
OK (10 msec)
[oracle@cent6 admin]$

3.ADG配置文件准备（主备）

创建pfile

create pfile = '/opt/oracle/pfile' from spfile;

Primary（主）库修改pfile配置，添加如下部分

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

standby（备）库修改pfile配置，添加如下部分

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

Primary（主）库使用pfile重启

SQL>shutdown immediate;
SQL>startup  pfile='/opt/oracle/pfile';

Standby（备）库使用pfile重启至nomount状态

SQL>shutdown immediate;
SQL>startup nomount pfile='/opt/oracle/pfile';

4.RMAN复制（仅Standby（备）库）

RMAN连接主备库

[oracle@cent6 admin]$ rman target sys/schina@main auxiliary sys/schina@back

Recovery Manager: Release 11.2.0.4.0 - Production on Mon Nov 30 05:29:28 2020

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORCL (DBID=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=129 device type=DISK

contents of Memory Script:
{
   backup as copy reuse
   targetfile  '/opt/oracle/product/OraHome/dbs/orapworcl' auxiliary format 
 '/opt/oracle/product/OraHome/dbs/orapworcl'   ;
}
executing Memory Script

Starting backup at 30-NOV-20
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=201 device type=DISK
Finished backup at 30-NOV-20
省略部分。。。。

复制完成后，Standby默认处于mount状态，需要手动打开

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.确认日志序号（主备）

分别在主备执行archive log list，确保主备的Current log sequence（当前日志序列）一致

SQL>archive log list

6.数据同步

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

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

登录Standby（备）库，查询T表

7.修改数据库启动为spfile

将pfile转换为spfile

SQL>create spfile from pfile='/opt/oracle/pfile'

重启数据库

SQL>shutdown immediate;

SQL>startup;

8.备份虚拟机

将主备虚拟机关机，然后创建快照

 

问题排查

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

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