RedHat搭建物理DataGuard

2020-11-09 来源：乌哈旅游

我的搭建环境：两台机器均为：Red Hat Enterprise Linux Server release 5.4 数据库版本为：Oracle10g10.2.0 primary机上装orac

我的搭建环境：

两台机器均为：Red Hat Enterprise Linux Server release 5.4

数据库版本为：Oracle10g10.2.0

primary机上装oracle软件并创建数据库orcl

standby机上只装oralce软件，无需装数据库

基本配置：

源数据库：

IP：10.37.1.1

数据库SID：orcl_p

db_unique_name：orcl1

standby数据库：

IP：10.37.1.2

数据库SID：orcl_s

db_unique_name：orcl2

配置步骤：

1、配置primary数据库归档，并设置本地归档路径

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount

ORACLE instance started.

Total System Global Area 167772160 bytes

Fixed Size 1218316 bytes

Variable Size 83888372 bytes

Database Buffers 79691776 bytes

Redo Buffers 2973696 bytes

Database mounted.

SQL> alter database archivelog;

Database altered.

SQL> alter database open;

Database altered.

SQL> alter system set log_archive_dest_1='location=/u01/arch';

System altered.

SQL> archive log list

Database log mode Archive Mode

Automatic archival Enabled

Archive destination /u01/arch

Oldest online log sequence 2

Next log sequence to archive 4

Current log sequence 4

2、将primary数据库置于force logging模式

SQL> select force_logging from v$database;

FOR

---

SQL> alter database force logging;

Database altered.

SQL> select force_logging from v$database;

FOR

---

YES

3、创建并修改primary数据库的初始化参数文件

SQL> create pfile='/u01/p_pfile.ora' from spfile;

File created.

[oracle@localhost ~]$ vi /u01/p_pfile.ora

修改如下内容：

*.db_unique_name=orcl1

*.log_archive_config='dg_config=(orcl1,orcl2)'

*.log_archive_dest_2='service=orcl_s.2_tns arch valid_for=(online_logfiles,primary_role)db_unique_name=orcl2'

*.log_archive_dest_state_2=defer

*.fal_server=orcl_s.2_tns

*.fal_client=orcl_p.1_tns

*.db_file_name_convert='/u01/app/oracle/oradata/orcl', '/u01/app/oracle/oradata/orcl_s'

*.log_file_name_convert='/u01/app/oracle/oradata/orcl', '/u01/app/oracle/oradata/orcl_s'

*.standby_file_management=auto

关闭数据库利用修改后的pfile创建spfile

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> !export ORACLE_SID=orcl_p

SQL> create spfile from pfile='/u01/p_pfile.ora';

File created.

SQL> startup

ORACLE instance started.

Total System Global Area 167772160 bytes

Fixed Size 1218316 bytes

Variable Size 62916852 bytes

Database Buffers 100663296 bytes

Redo Buffers 2973696 bytes

Database mounted.

Database opened.

4、创建备份：

创建standby的控制文件：

SQL> alter database create standby controlfile as '/u01/orcl2control01.ctl';

Database altered.

创建所有的数据文件备份（此处仅以users表空间下的数据文件为例，其他数据文件均要备份）

SQL> alter tablespace users begin backup;

Tablespace altered.

SQL> !cp /u01/app/oracle/oradata/orcl/users01.dbf /u01

SQL> alter tablespace users end backup;

Tablespace altered.

5、配置primary监听和tns服务

[oracle@localhost ~]$ cd $ORACLE_HOME/network/admin

[oracle@localhost admin]$ vi listener.ora

配置内容如下：

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(SID_NAME = orcl_p)

(ORACLE_HOME = /u01/app/oracle/10.2.0/db_1)

(GLOBAL_DBNAME = orcl_p)

)

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

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

)

配置tns

[oracle@localhost admin]$ vi tnsnames.ora

配置内容如下：

orcl_p.1_tns =

(DESCRIPTION =

(ADDRESS_LIST =

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

)

(CONNECT_DATA =

(SID = orcl_p)

(SERVER = DEDICATED)

)

orcl_s.2_tns =

(DESCRIPTION =

(ADDRESS_LIST =

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

)

(CONNECT_DATA =

(SID = orcl_s)

(SERVER = DEDICATED)

)

此时重启监听后，测试源数据库tns配置

[oracle@localhost admin]$ lsnrctl stop

LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 26-FEB-2014 19:11:44

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

The command completed successfully

[oracle@localhost admin]$ lsnrctl start

LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 26-FEB-2014 19:11:53

Starting /u01/app/oracle/10.2.0/db_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 10.2.0.1.0 - Production

System parameter file is /u01/app/oracle/10.2.0/db_1/network/admin/listener.ora

Log messages written to /u01/app/oracle/10.2.0/db_1/network/log/listener.log

Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.37.1.1)(PORT=1521)))

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

STATUS of the LISTENER

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

Alias LISTENER

Version TNSLSNR for Linux: Version 10.2.0.1.0 - Production

Start Date 26-FEB-2014 19:11:53

Uptime 0 days 0 hr. 0 min. 0 sec

Trace Level off

Security ON: Local OS Authentication

SNMP OFF

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

Listener Log File /u01/app/oracle/10.2.0/db_1/network/log/listener.log

Listening Endpoints Summary...

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.37.1.1)(PORT=1521)))

Services Summary...

Service "orcl_p" has 1 instance(s).

Instance "orcl_P", status UNKNOWN, has 1 handler(s) for this service...

The command completed successfully

[oracle@localhost admin]$ tnsping orcl_p.1_tns

TNS Ping Utility for Linux: Version 10.2.0.1.0 - Production on 26-FEB-2014 19:12:38

Used parameter files:

/u01/app/oracle/10.2.0/db_1/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.37.1.1)(PORT = 1521))) (CONNECT_DATA = (SID = orcl_p) (SERVER = DEDICATED)))

OK (0 msec)

6、standby数据库创建目录结构，并将源数据库的参数文件、备份的控制文件、创建的口令文件copy到相应的位置

7、

[oracle@localhost ~]$ mkdir -p /u01/arch2

[oracle@localhost u01]$ mkdir -p /u01/app/oracle/flash_recovery_area

[oracle@localhost ~]$ mkdir -p /u01/app/oracle/oradata/orcl_s

[oracle@localhost ~]$ mkdir -p /u01/app/oracle/admin/orcl_s/{a,b,c,u}dump

[oracle@localhost ~]$ scp 10.37.1.1:/u01/*.dbf /u01/app/oracle/oradata/orcl_s/

oracle@10.37.1.1's password:

example01.dbf 100% 100MB 20.0MB/s 00:05

sysaux01.dbf 100% 240MB 16.0MB/s 00:15

system01.dbf 100% 480MB 14.6MB/s 00:33

undotbs01.dbf 100% 30MB 15.0MB/s 00:02

users01.dbf 100% 5128KB 5.0MB/s 00:01

[oracle@localhost ~]$ scp 10.37.1.1:/u01/*.ctl /u01/app/oracle/oradata/orcl_s/

oracle@10.37.1.1's password:

orcl2control01.ctl 100% 6896KB 6.7MB/s 00:00

[oracle@localhost ~]$ scp 10.37.1.1:/u01/p_pfile.ora /u01s_pfile.ora

oracle@10.37.1.1's password:

p_pfile.ora 100% 1508 1.5KB/s 00:00

[oracle@localhost ~]$ scp 10.37.1.1:/u01/app/oracle/10.2.0/db_1/dbs/orapworcl_p /u01/app/oracle/10.2.0/db_1/dbs/orapworcl_s

oracle@10.37.1.1's password:

orapworcl_p 100% 5120 5.0KB/s 00:00

7、修改standby数据库的sid为orcl_s并配置copy来的参数文件

[root@localhost ~]# vi /u01/s_pfile.ora

需要修改的内容如下：（没必要照搬，可根据自己的实际情况自行修改，，注意红色部分是重点修改的地方）

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

*.background_dump_dest='/u01/app/oracle/admin/orcl_s/bdump'

*.control_files='/u01/app/oracle/oradata/orcl_s/orcl2control01.ctl','/u01/app/oracle/oradata/orcl_s/orcl2control02.ctl','/u01/app/oracle/oradata/orcl_s/orcl2control03.ctl'

*.core_dump_dest='/u01/app/oracle/admin/orcl_s/cdump'

*.user_dump_dest='/u01/app/oracle/admin/orcl_s/udump'

*.db_unique_name=orcl2

*.log_archive_dest_1='location=/u01/arch2'

*.log_archive_dest_2='service=orcl_p.1_tns arch valid_for=(online_logfiles, primary_ro

le) db_unique_name=orcl1'

*.log_archive_dest_state_2=enable

*.fal_server=orcl_p.1_tns

*.fal_client=orcl_s.2_tns

8、配置standby数据库的监听和服务名

[oracle@localhost admin]$ cd $ORACLE_HOME/network/admin

[oracle@localhost admin]$ vi listener.ora

# listener.ora Network Configuration File: /u01/app/oracle/10.2.0/db_1/network/admin/listener.ora

# Generated by Oracle configuration tools.

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(SID_NAME = orcl_s)

(ORACLE_HOME = /u01/app/oracle/10.2.0/db_1)

(GLOBAL_DBNAME = orcl_s)

)

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

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

)

[oracle@localhost admin]$ vi tnsnames.ora

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

# Generated by Oracle configuration tools.

orcl_s.2_tns =

(DESCRIPTION =

(ADDRESS_LIST =

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

)

(CONNECT_DATA =

(SID = orcl_s)

(SERVER = DEDICATED)

)

orcl_p.1_tns =

(DESCRIPTION =

(ADDRESS_LIST =

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

)

(CONNECT_DATA =

(SID = orcl_p)

(SERVER = DEDICATED)

"tnsnames.ora" 36L, 764C

配置完成后重启监听服务

[oracle@localhost admin]$ lsnrctl stop

[oracle@localhost admin]$ lsnrctl start

至此监听和服务配置完成，在primary和standby端用tnsping命令应该能ping都通两个服务，能远程登入两数据库视为配置成功

SQL> conn sys/oracle@orcl_p.1_tns as sysdba

Connected.

SQL> conn sys/oracle@orcl_s.2_tns as sysdba

Connected to an idle instance.

9、配置stanby数据库并启动到mount状态，并接受归档文件

任意终端连接到standby数据库

[oracle@localhost ~]$ sqlplus sys/oracle@orcl_s.2_tns as sysdba

利用s_pfile.ora常见standby的spfile

SQL> create spfile from pfile='/u01/s_pfile.ora';

File created.

SQL> startup mount

ORACLE instance started.

Total System Global Area 167772160 bytes

Fixed Size 1218316 bytes

Variable Size 62916852 bytes

Database Buffers 100663296 bytes

Redo Buffers 2973696 bytes

Database mounted.

连接到primary数据库并设置远程归档路径开启

SQL> conn sys/oracle@orcl_p.1_tns as sysdba

Connected.

SQL> alter system set log_archive_dest_state_2=enable;

System altered.

查看归档接受情况

SQL> select max(sequence#) from v$archived_log;

MAX(SEQUENCE#)

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

SQL> conn sys/oracle@orcl_s.2_tns as sysdba

Connected.

SQL> select max(sequence#) from v$archived_log;

MAX(SEQUENCE#)

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

查看standby的归档路径下是否有源数据库传来的归档日志

SQL> !ls /u01/arch2

1_4_840520047.dbf

10、primary数据插入，测试standby数据库能否正常接受

primary端创建表并插入数据

SQL> conn scott/tiger

Connected.

SQL> create table DG_TEST(ID VARCHAR2(10));

Table created.

SQL> insert into DG_TEST

2 values ('DG_TEST')

3 /

1 row created.

SQL> /

1 row created.

SQL> /

1 row created.

SQL> /

1 row created.

SQL> select * from DG_TEST;

----------

DG_TEST

SQL> commit;

Commit complete.

切换归档日志，使当前日志归档

SQL> conn / as sysdba

Connected.

SQL> alter system switch logfile;

System altered.

standby启动redo应用（这也正是物理standby的工作方法，等于是从获取的归档中执行恢复操作，来保持与源数据库一致）

SQL> alter database recover managed standby database disconnect from session;

Database altered.

打开数据库前必须停止redo应用

SQL> alter database recover managed standby database cancel;

Database altered.

打开standby数据，查看是否有源数据库新插入的数据

SQL> select * from scott.DG_TEST;

----------

DG_TEST

数据成功传入standby数据库，物理的DG搭建成功！

--------------------------------------分割线 --------------------------------------

Oracle Data Guard 重要配置参数

基于同一主机配置 Oracle 11g Data Guard

探索Oracle之11g DataGuard

Oracle Data Guard （RAC+DG) 归档删除策略及脚本

Oracle Data Guard 的角色转换

Oracle Data Guard的日志FAL gap问题

Oracle 11g Data Guard Error 16143 Heartbeat failed to connect to standby 处理方法

--------------------------------------分割线 --------------------------------------

本文永久更新链接地址：

显示全文

全部栏目

RedHat搭建物理DataGuard