Oracle 11g rac adg for linux 安装文档

一、 环境介绍

1、主库：Oracle 11g rac

2、备库：Oracle 11g 单库

3、操作系统 ORHEL 7.8 x64

4、主备库信息

序号	名称	数据唯一名称	实例名称	服务名称	备注
1	主库	orcl_st	orcl1/orcl2	Orcl_st	/
2	备库	orcl	orcl	orcl	/

5、网络拓扑

二、 主备库ADG配置

1、开启主库强制日志( Enable Forced Logging)

SQL> select name,open_mode,FORCE_LOGGING from v$database;

NAME OPEN_MODE FOR

--------- -------------------- ---

ORCL READ WRITE YES

如果没有强制日志归档：

SQL> alter database force logging;

2、配置备库登录验证（采用orapwdorcl文件）

[oracle@rac1 dbs]$ ls

hc_orcl1.dat init.ora orapworcl.bak orapworcl1 snapcf_orcl1.f spfileorcl1.ora

[oracle@rac1 dbs]$ scp orapworcl1 oracle@10.10.10.20:/u01/app/oracle/product/11.2.0/db_1/dbs/orapworcl

oracle@10.10.10.20's password:

orapworcl1 100% 1536 1.4MB/s 00:00

3、配置主数据库以接受重做数据

查看主库当前日志组情况：

SQL> SELECT GROUP#, BYTES FROM V$LOG;

GROUP# BYTES

---------- ----------

1 52428800

2 52428800

3 52428800

13 52428800

14 52428800

15 52428800

6 rows selected.

查看备用日志组：（创建备用日志组）

SQL> SELECT GROUP#, BYTES FROM V$STANDBY_LOG;

alter database add standby logfile thread 1 group 4 size 50m;

alter database add standby logfile thread 1 group 5 size 50m;

alter database add standby logfile thread 1 group 6 size 50m;

alter database add standby logfile thread 1 group 7 size 50m;

alter database add standby logfile thread 2 group 9 size 50m;

alter database add standby logfile thread 2 group 10 size 50m;

alter database add standby logfile thread 2 group 11 size 50m;

alter database add standby logfile thread 2 group 12 size 50m;

SQL> SELECT GROUP#, BYTES FROM V$STANDBY_LOG;

GROUP# BYTES

---------- ----------

4 52428800

5 52428800

6 52428800

7 52428800

9 52428800

10 52428800

11 52428800

12 52428800

8 rows selected.

SQL> select * from v$logfile;

GROUP# STATUS TYPE MEMBER IS_

------ -- ------- ------------------------------------------------ ---

3 ONLINE +DATA/orcl_st/onlinelog/group_3.289.1197719565 NO

1 ONLINE +DATA/orcl_st/onlinelog/group_1.287.1197718013 NO

4 STANDBY +DATA/orcl_st/onlinelog/group_4.256.1197724689 NO

4 STANDBY +FLA/orcl_st/onlinelog/group_4.270.1197724689 YES

5 STANDBY +DATA/orcl_st/onlinelog/group_5.276.1197724689 NO

5 STANDBY +FLA/orcl_st/onlinelog/group_5.432.1197724689 YES

6 STANDBY +DATA/orcl_st/onlinelog/group_6.260.1197724689 NO

6 STANDBY +FLA/orcl_st/onlinelog/group_6.431.1197724689 YES

7 STANDBY +DATA/orcl_st/onlinelog/group_7.275.1197724729 NO

7 STANDBY +FLA/orcl_st/onlinelog/group_7.268.1197724729 YES

9 STANDBY +DATA/orcl_st/onlinelog/group_9.263.1197724771 NO

------ -- ------- ------------------------------------------------ ---

9 STANDBY +FLA/orcl_st/onlinelog/group_9.266.1197724771 YES

10 STANDBY +DATA/orcl_st/onlinelog/group_10.278.1197724771 NO

10 STANDBY +FLA/orcl_st/onlinelog/group_10.428.1197724771 YES

11 STANDBY +DATA/orcl_st/onlinelog/group_11.282.1197724771 NO

11 STANDBY +FLA/orcl_st/onlinelog/group_11.540.1197724771 YES

12 STANDBY +DATA/orcl_st/onlinelog/group_12.281.1197724787 NO

12 STANDBY +FLA/orcl_st/onlinelog/group_12.541.1197724787 YES

13 ONLINE +DATA/orcl_st/onlinelog/group_13.284.1197717093 NO

13 ONLINE +FLA/orcl_st/onlinelog/group_13.445.1197717095 YES

14 ONLINE +DATA/orcl_st/onlinelog/group_14.285.1197717103 NO

14 ONLINE +FLA/orcl_st/onlinelog/group_14.446.1197717103 YES

------ -- ------- ------------------------------------------------ ---

15 ONLINE +DATA/orcl_st/onlinelog/group_15.286.1197717111 NO

15 ONLINE +FLA/orcl_st/onlinelog/group_15.447.1197717111 YES

1 ONLINE +FLA/orcl_st/onlinelog/group_1.460.1197718013 YES

2 ONLINE +DATA/orcl_st/onlinelog/group_2.288.1197718021 NO

2 ONLINE +FLA/orcl_st/onlinelog/group_2.461.1197718021 YES

3 ONLINE +FLA/orcl_st/onlinelog/group_3.497.1197719565 YES

28 rows selected.

4、设置主库初始化参数

在主库运行的情况下，在线配置参数如下：

primary:

DB_NAME=orcl # 查询主库

DB_UNIQUE_NAME=orcl_st # 查询主库

alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(orcl,orcl_st)' sid='*';

alter system set LOG_ARCHIVE_DEST_1='location=+fla/orcl/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=orcl_st' sid='*' scope=spfile;

alter system set LOG_ARCHIVE_DEST_2='SERVICE=orcl ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcl' sid='*';

alter system set LOG_ARCHIVE_DEST_STATE_1=ENABLE sid='*';

alter system set LOG_ARCHIVE_DEST_STATE_2=ENABLE sid='*';

alter system set REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE sid='*' scope=spfile;

alter system set LOG_ARCHIVE_FORMAT='%t_%s_%r.dbf' sid='*' scope=spfile;

alter system set FAL_SERVER=orcl_st sid='*';

alter system set DB_FILE_NAME_CONVERT='/oradata/orcl/','+DATA/orcl/' sid='*' scope=spfile;

alter system set LOG_FILE_NAME_CONVERT='/flash_recovery/orcl/','+FLA/orcl/' sid='*' scope=spfile;

alter sysetm set STANDBY_FILE_MANAGEMENT=AUTO sid='*' scope=spfile;

备注：主库在运行的情况下，可以选用原来库的文件和日志位置，确保文件夹都在；

5、启动归档

SQL> archive log list;

Database log mode Archive Mode

Automatic archival Enabled

Archive destination +FLA/orcl/

Oldest online log sequence 195

Next log sequence to archive 197

Current log sequence 197

备注：如未启用归档，在mount状态下：alter database archivelog 随后启动数据库即可alter database open;

6、配置主库tnsnames.ora文件（rac1、rac2）

[oracle@rac1 ~]$ cd /u01/app/oracle/product/11.2.0/db_1/network/admin/

[oracle@rac1 admin]$ pwd

/u01/app/oracle/product/11.2.0/db_1/network/admin

[oracle@rac1 admin]$ cat tnsnames.ora

# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora

# Generated by Oracle configuration tools.

orcl =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.10.20)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = orcl)

)

orcl_st=

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.10.15)(PORT = 1521))

(CONNECT_DATA =

(SERVICE_NAME = orcl)

(UR=A)

)

三、 配置备库

1、修改备库参数文件（编辑参数文件，涉及内存大小可根据实际情况确定）

orcl.__db_cache_size=1912602624

orcl.__java_pool_size=16777216

orcl.__large_pool_size=33554432

orcl.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment

orcl.__pga_aggregate_target=838860800

orcl.__sga_target=2499805184

orcl.__shared_io_pool_size=0

orcl.__shared_pool_size=503316480

orcl.__streams_pool_size=0

*.audit_file_dest='/u01/app/oracle/admin/orcl/adump'

*.audit_trail='db'

*.compatible='11.2.0.4.0'

*.control_files='/oradata/orcl/control01.ctl','/oradata/orcl/control02.ctl'

*.db_block_size=8192

*.db_create_file_dest='/oradata/'

*.db_domain=''

*.db_file_name_convert='+DATA/orcl/','/oradata/orcl/'

*.db_name='orcl'

*.db_recovery_file_dest_size=10737418240

*.db_recovery_file_dest='/flash_recovery/'

*.db_unique_name='orcl'

*.diagnostic_dest='/u01/app/oracle'

*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'

*.fal_server='ORCL_ST'

*.log_archive_config='DG_CONFIG=(orcl,orcl_st)'

*.log_archive_dest_1='location=/flash_recovery/orcl/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=orcl'

*.log_archive_dest_2='SERVICE=orcl_st ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcl_st'

*.log_archive_dest_state_1='ENABLE'

*.log_archive_dest_state_2='ENABLE'

*.log_archive_format='%t_%s_%r.dbf'

*.log_file_name_convert='+FLA/orcl/','/flash_recovery/orcl/'

*.open_cursors=300

*.pga_aggregate_target=832569344

*.processes=150

*.remote_login_passwordfile='EXCLUSIVE'

*.sga_target=2497708032

*.standby_file_management='auto'

*.undo_tablespace='UNDOTBS1'

2 、创建文件目录

[oracle@mirrordb ~]$ mkdir -p /u01/app/oracle/admin/orcl/adump /oradata/orcl/ /flash_recovery/orcl/

3 、配置监听及tnsnames.ora文件

Listener.ora

[oracle@mirrordb ~]$ cd /u01/app/oracle/product/11.2.0/db_1/network/admin/

[oracle@mirrordb admin]$ pwd

/u01/app/oracle/product/11.2.0/db_1/network/admin

[oracle@mirrordb admin]$ ls

listener.ora samples shrept.lst tnsnames.ora

[oracle@mirrordb admin]$ cat listener.ora

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.10.20)(PORT = 1521))

)

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(GLOBAL_DBNAME = orcl)

(SID_NAME = orcl)

)

Tnsnames.ora 文件

[oracle@mirrordb admin]$ cat tnsnames.ora

# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora

# Generated by Oracle configuration tools.

ORCL_ST =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.10.15)(PORT = 1521))

)

(CONNECT_DATA =

(SERVICE_NAME = orcl)

(UR = A)

)

ORCL =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.10.20)(PORT = 1521))

)

(CONNECT_DATA =

(SERVICE_NAME = orcl)

(UR = A) # 为复制数据库所配置，因为没有库的情况下监听服务为block状态

)

启动监听服务：lsnrctl start

[oracle@mirrordb dbs]$ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 13-APR-2025 21:16:17

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.10.10.20)(PORT=1521)))

STATUS of the LISTENER

------------------------

Alias LISTENER

Version TNSLSNR for Linux: Version 11.2.0.4.0 - Production

Start Date 13-APR-2025 07:15:24

Uptime 0 days 14 hr. 0 min. 52 sec

Trace Level off

Security ON: Local OS Authentication

SNMP OFF

Listener Parameter File /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora

Listener Log File /u01/app/oracle/diag/tnslsnr/mirrordb/listener/alert/log.xml

Listening Endpoints Summary...

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.10.10.20)(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

4 、检查密码文件

[oracle@mirrordb dbs]$ pwd

/u01/app/oracle/product/11.2.0/db_1/dbs

[oracle@mirrordb dbs]$ ls

hc_orcl.dat init.ora lkORCL lkORCL_PD lkORCL_ST orapworcl snapcf_orcl.f spfileorcl.ora

5 、启动备库到nomount状态

[oracle@mirrordb dbs]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Sun Apr 13 21:19:08 2025

Connected to an idle instance.

SQL> create spfile from pfile='/home/oracle/pfile13.ora';

SQL> startup nomount;

ORACLE instance started.

Total System Global Area 2488635392 bytes

Fixed Size 2255792 bytes

Variable Size 603980880 bytes

Database Buffers 1862270976 bytes

Redo Buffers 20127744 bytes

6 、在线方式复制主库做dg（tnsnames.ora选择主库一个节点）

ORCL_ST =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.10.11)(PORT = 1521))

)

(CONNECT_DATA =

(SERVICE_NAME = orcl)

(UR = A)

)

[oracle@mirrordb admin]$ rman target sys/oracle@orcl_st auxiliary sys/oracle@orcl

Recovery Manager: Release 11.2.0.4.0 - Production on Sun Apr 13 21:23:34 2025

connected to target database: ORCL (DBID=1725403206)

connected to auxiliary database: ORCL (not mounted)

# 检查主库表空间创建方式

SQL> select file_id,file_name,tablespace_name from dba_data_files;

FILE_ID FILE_NAME TABLESPACE_NAME

-- ------------------------------------------------------------

3 +DATA/orcl_st/datafile/undotbs1.258.1197723661 UNDOTBS1

4 +DATA/orcl/users01.dbf USERS

6 +DATA/orcl_st/datafile/undotbs2.283.1197723497 UNDOTBS2

2 +DATA/orcl/sysaux01.dbf SYSAUX

1 +DATA/orcl/system01.dbf SYSTEM

5 rows selected.

备注：以手工方式建立的表空间通过直接复制方式无法实现表空间的名称转换，duplicate时采用以下语句：

run

{

set newname for datafile 1 to "/oradata/orcl/system01.dbf";

set newname for datafile 2 to "/oradata/orcl/sysaux01.dbf";

set newname for datafile 3 to "/oradata/orcl/undotbs1.dbf";

set newname for datafile 4 to "/oradata/orcl/users01.dbf";

set newname for datafile 6 to "/oradata/orcl/undotbs2.dbf";

duplicate target database for standby from active database nofilenamecheck;

}

[oracle@mirrordb admin]$ rman target sys/oracle@orcl_st auxiliary sys/oracle@orcl

Recovery Manager: Release 11.2.0.4.0 - Production on Sun Apr 13 21:23:34 2025

connected to target database: ORCL (DBID=1725403206)

connected to auxiliary database: ORCL (not mounted)

RMAN> run

{

2> 3> set newname for datafile 1 to "/oradata/orcl/system01.dbf";

set newname for datafile 2 to "/oradata/orcl/sysaux01.dbf";

set newname for datafile 3 to "/oradata/orcl/undotbs1.dbf";

set newname for datafile 4 to "/oradata/orcl/users01.dbf";

set newname for datafile 6 to "/oradata/orcl/undotbs2.dbf";

4> 5> 6> 7> 8> duplicate target database for standby from active database nofilenamecheck;

}9>

7 、检查备库状态

SQL> select name,open_mode from v$database;

NAME OPEN_MODE

--------- --------------------

ORCL MOUNTED

SQL> show parameter db_unique;

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

db_unique_name string orcl

# 检查主库日志传输情况

SQL> select status,error from v$archive_dest where dest_id=2;

STATUS ERROR

--------- -----------------------------------------------------------------

VALID

# 备库应用日志（同步日志信息）

SQL> select name,open_mode from v$database;

NAME OPEN_MODE

--------- --------------------

ORCL MOUNTED

SQL> alter database recover managed standby database using current logfile disconnect from session;

Database altered.

# 主库：

SQL> archive log list;

Database log mode Archive Mode

Automatic archival Enabled

Archive destination +FLA/orcl/

Oldest online log sequence 204

Next log sequence to archive 206

Current log sequence 206

# 备库：

SQL> archive log list;

Database log mode Archive Mode

Automatic archival Enabled

Archive destination /flash_recovery/orcl/

Oldest online log sequence 0

Next log sequence to archive 0

Current log sequence 206

备库取消应用日志，开启到open状态；

SQL> alter database recover managed standby database cancel;

Database altered.

SQL> alter database open;

Database altered.

SQL> select name,open_mode from v$database;

NAME OPEN_MODE

--------- --------------------

ORCL READ ONLY

SQL> alter database recover managed standby database using current logfile disconnect from session;

Database altered.

# 检查备库应用日志

SQL> select process,sequence#,status from v$managed_standby;

PROCESS SEQUENCE# STATUS

--------- ---------- ------------

ARCH 205 CLOSING

ARCH 0 CONNECTED

ARCH 80 CLOSING

RFS 0 IDLE

RFS 81 IDLE

RFS 206 IDLE

RFS 0 IDLE

MRP0 81 APPLYING_LOG

11 rows selected.

四、 测试数据同步

1、主库创建表空间和表

SQL> select tablespace_name,status from dba_data_files;

TABLESPACE_NAME STATUS

------------------------------ ---------

UNDOTBS1 AVAILABLE

USERS AVAILABLE

UNDOTBS2 AVAILABLE

SYSAUX AVAILABLE

SYSTEM AVAILABLE

SQL> create tablespace data datafile size 100m autoextend on maxsize 2g;

Tablespace created.

SQL> select tablespace_name,status from dba_data_files;

TABLESPACE_NAME STATUS

------------------------------ ---------

UNDOTBS1 AVAILABLE

USERS AVAILABLE

UNDOTBS2 AVAILABLE

SYSAUX AVAILABLE

SYSTEM AVAILABLE

DATA AVAILABLE

SQL> select * from it;

ID

----------

1000

SQL> insert into it values(9999);

1 row created.

SQL> commit;

Commit complete.

SQL> select *from it;

ID

----------

1000

9999

6 rows selected.

2、备库检查表空间和表

SQL> select tablespace_name,status from dba_data_files;

TABLESPACE_NAME STATUS

------------------------------ ---------

UNDOTBS1 AVAILABLE

USERS AVAILABLE

UNDOTBS2 AVAILABLE

SYSAUX AVAILABLE

SYSTEM AVAILABLE

DATA AVAILABLE

6 rows selected.

SQL> select * from it;

ID

----------

1000

9999

3、检查主备库alert.log

# 主库：（显示正常，无报错）

Thread 1 advanced to log sequence 206 (LGWR switch)

Current log# 2 seq# 206 mem# 0: +DATA/orcl_st/onlinelog/group_2.288.1197718021

Current log# 2 seq# 206 mem# 1: +FLA/orcl_st/onlinelog/group_2.461.1197718021

Sun Apr 13 22:03:38 2025

******************************************************************

LGWR: Setting 'active' archival for destination LOG_ARCHIVE_DEST_2

******************************************************************

Sun Apr 13 22:03:38 2025

Archived Log entry 357 added for thread 1 sequence 205 ID 0x66e19fb0 dest 1:

LNS: Standby redo logfile selected for thread 1 sequence 206 for destination LOG_ARCHIVE_DEST_2

Sun Apr 13 22:03:38 2025

ARC3: Standby redo logfile selected for thread 1 sequence 205 for destination LOG_ARCHIVE_DEST_2

Sun Apr 13 22:15:25 2025

create tablespace data datafile size 100m autoextend on maxsize 2g

Completed: create tablespace data datafile size 100m autoextend on maxsize 2g

# 备库（正常，无报错）

Completed: alter database recover managed standby database using current logfile disconnect from session

Sun Apr 13 22:15:27 2025

Successfully added datafile 5 to media recovery

Datafile #5: '/oradata/ORCL/datafile/o1_mf_data_mzqkqyc4_.dbf'

总结：主备库数据同步正常，主库rac 同步到单库实验结束。

ITPub博客

Oracle 11g rac adg for linux安装文档

最新文章