Oracle Dataguard 的配置安装

偏执的太偏执、 2022-04-24 00:20 220阅读 0赞

测试验证环境配置：

VMware + windowns 2003 + oracle 11g R2

1.       在两台windowns 2003上先安装oracle 11g软件，我是现在一台机器上安装，然后clone另一个2003

2.       在一台机器上创建oracle实例，当做dataguard primary数据库

3.       在primary端配置设置oracle为归档模式

4.       查看当前归档模式情况: archive log list

5.       如果数据库处在非归档模式，打开归档模式

设置为归档模式,命令如下:

SQL> shutdown immediate

SQL> startup mount

SQL> alter database archivelog;

SQL> archive log list;

6.       设置本地归档目录

alter system set log\_archive\_dest\_1='location=C:\\arch'

7.       开启forced logging模式

alter database force logging;

查看设置

SQL> select NAME,FORCE\_LOGGING from v$database;

NAME      FOR

\--------- ---

ORA11G    YES

SQL>

8.       为了双向切换添加red log file

添加一个新的Standby Redologs组（注意组号不要与当前存在的Online Redologs组重复），并为该组指定一个成员

SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 ('C:\\app\\Administrator\\oradata\\ora11g/SREDO04.LOG') size 50M;

SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 ('C:\\app\\Administrator\\oradata\\ora11g/SREDO05.LOG') size 50M;

SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 6 ('C:\\app\\Administrator\\oradata\\ora11g/SREDO06.LOG') size 50M;

SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 7 ('C:\\app\\Administrator\\oradata\\ora11g/SREDO07.LOG') size 50M;

9.       创建备库的密码文件和控制文件

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

-- 说明：判断一个数据库是Primary还是Standby，就是通过控制文件来判断的

orapwd file=name password=newpasswd entries=10

10.   修改初始化参数文件

SQL> create pfile from spfile;

在initorcl.ora 添加如下内容：

\*.DB\_UNIQUE\_NAME='PRIMARY'

\*.FAL\_CLIENT='primary'

\*.FAL\_SERVER='standby'

\*.log\_archive\_config='DG\_CONFIG=(PRIMARY,STANDBY)'

\*.log\_archive\_dest\_1='LOCATION=C:\\arch db\_unique\_name=PRIMARY'

\*.log\_archive\_dest\_2='SERVICE=STANDBY DB\_UNIQUE\_NAME=STANDBY'

\*.log\_archive\_dest\_state\_1='ENABLE'

\*.log\_archive\_dest\_state\_2='ENABLE'

\*.standby\_archive\_dest='C:\\arch'

\*.standby\_file\_management='AUTO'

\*.log\_file\_name\_convert='C:\\app\\Administrator\\oradata\\ora11g','C:\\app\\Administrator\\oradata\\ora11g'

\*.db\_file\_name\_convert='C:\\app\\Administrator\\oradata\\ora11g','C:\\app\\Administrator\\oradata\\ora11g'

11.   修改listener.ora 和tnsnames.ora 文件

LISTENER1 =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = wangwei)(PORT = 1527))

)

SID\_LIST\_LISTENER =

(SID\_LIST =

(SID\_DESC =

(GLOBAL\_DBNAME = ora11g)

(ORACLE\_HOME = C:\\app\\Administrator\\product\\11.2.0\\dbhome\_1)

(SID\_NAME = ora11g)

)

(SID\_DESC =

(GLOBAL\_DBNAME = ora12g)

(ORACLE\_HOME = C:\\app\\Administrator\\product\\11.2.0\\dbhome\_1)

(SID\_NAME = ora11g)

)

SID\_LIST\_LISTENER1 =

(SID\_LIST =

(SID\_DESC =

(GLOBAL\_DBNAME = ora11g)

(ORACLE\_HOME = C:\\app\\Administrator\\product\\11.2.0\\dbhome\_1)

(SID\_NAME = ora11g)

)

ADR\_BASE\_LISTENER1 = C:\\app\\Administrator\\product\\11.2.0\\dbhome\_1\\log

LISTENER =

(DESCRIPTION =

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

)

ADR\_BASE\_LISTENER = C:\\app\\Administrator\\product\\11.2.0\\dbhome\_1\\log

DBWW =

(DESCRIPTION =

(ADDRESS\_LIST =

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

)

(CONNECT\_DATA =

(SERVICE\_NAME = ora11g)

)

primary =

(DESCRIPTION =

(ADDRESS\_LIST =

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

)

(CONNECT\_DATA =

(SERVICE\_NAME = ora11g)

)

standby =

(DESCRIPTION =

(ADDRESS\_LIST =

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

)

(CONNECT\_DATA =

(SERVICE\_NAME = ora11g)

)

12.   Standby 端配置

13.   创建备库存放数据文件和后台跟踪目录

这个目录可以和主库相同,如果不同，就需要在主库的初始化文件中进行转换。

\*.log\_file\_name\_convert='C:\\app\\Administrator\\oradata\\ora11g','C:\\app\\Administrator\\oradata\\ora11g'

\*.db\_file\_name\_convert='C:\\app\\Administrator\\oradata\\ora11g','C:\\app\\Administrator\\oradata\\ora11g'

14.   将主库shutdown然后把密码文件，控制文件(创建的standby controlfile)，数据文件，参数文件，日志文件copy到备库

修改初始化参数文件

修改之后如下：

\*.log\_archive\_config='DG\_CONFIG=(PRIMARY,STANDBY)'

\*.log\_archive\_dest\_1='LOCATION=C:\\arch db\_unique\_name=STANDBY'

\*.log\_archive\_dest\_2='SERVICE= PRIMARY DB\_UNIQUE\_NAME=PRIMARY'

\*.log\_archive\_dest\_state\_1='ENABLE'

\*.log\_archive\_dest\_state\_2='ENABLE'

\*.fal\_client='standby'

\*.fal\_server='primary'

\*.DB\_UNIQUE\_NAME='STANDBY'

\*.standby\_archive\_dest='C:\\arch'

\*.standby\_file\_management='AUTO'

\*.log\_file\_name\_convert='C:\\app\\Administrator\\oradata\\ora11g','C:\\app\\Administrator\\oradata\\ora11g'

\*.db\_file\_name\_convert='C:\\app\\Administrator\\oradata\\ora11g','C:\\app\\Administrator\\oradata\\ora11g'

15.    配置Listener.ora和tnsnames.ora

如果不存在，就从主库上copy 过去。

16.   在备库添加Standby Redologs

添加一个新的Standby Redologs组（注意组号不要与当前存在的Online Redologs组重复），并为该组指定一个成员：

SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 ('/u01/app/oracle/oradata/orcl/redo04.log') size 100M;

SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 ('/u01/app/oracle/oradata/orcl/redo05.log') size 100M;

SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 6 ('/u01/app/oracle/oradata/orcl/redo06.log') size 100M;

SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 7 ('/u01/app/oracle/oradata/orcl/redo07.log') size 100M;

如果在主库上面已经添加了standby redologs 可以先用下面语句删除掉. 再重新添加

alter database drop standby logfile group 4;

17.   Tnsping测试primary和standby配置

C:\\Documents and Settings\\Administrator>tnsping primary

TNS Ping Utility for 32-bit Windows: Version 11.2.0.1.0 - Production on 12-5月 \-

2013 13:58:32

已使用的参数文件:

C:\\app\\Administrator\\product\\11.2.0\\dbhome\_1\\network\\admin\\sqlnet.ora

已使用 TNSNAMES 适配器来解析别名

尝试连接 (DESCRIPTION = (ADDRESS\_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.1

68.1.106)(PORT = 1521))) (CONNECT\_DATA = (SERVICE\_NAME = ora11g)))

OK (20  毫秒)

C:\\Documents and Settings\\Administrator>tnsping standby

TNS Ping Utility for 32-bit Windows: Version 11.2.0.1.0 - Production on 12-5月 \-

2013 13:58:44

已使用的参数文件:

C:\\app\\Administrator\\product\\11.2.0\\dbhome\_1\\network\\admin\\sqlnet.ora

已使用 TNSNAMES 适配器来解析别名

尝试连接 (DESCRIPTION = (ADDRESS\_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.1

68.1.107)(PORT = 1521))) (CONNECT\_DATA = (SERVICE\_NAME = ora11g)))

OK (30  毫秒)

C:\\Documents and Settings\\Administrator>

18.    Data Guard 启动：

启动顺序：先standby ,后primary;

关闭顺序：先primary 后standby;

启动standby

C:\\Documents and Settings\\Administrator>sqlplus "/as sysdba"

SQL\*Plus: Release 11.2.0.1.0 Production on 星期日 5月 12 12:12:29 2013

已连接到空闲例程。

SQL>

SQL> startup nomount;

ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance

ORACLE  例程已经启动。

Total System Global Area  535662592 bytes

Fixed Size                  1375792 bytes

Variable Size             331350480 bytes

Database Buffers          197132288 bytes

Redo Buffers                5804032 bytes

SQL> alter database mount standby database ;

数据库已更改。

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

数据库已更改。

SQL>

C:\\Documents and Settings\\Administrator>lsnrctl start

LSNRCTL for 32-bit Windows: Version 11.2.0.1.0 - Production on 12-5月 \-2013 12:1

5:36

启动tnslsnr: 请稍候...

TNSLSNR for 32-bit Windows: Version 11.2.0.1.0 - Production

系统参数文件为C:\\app\\Administrator\\product\\11.2.0\\dbhome\_1\\network\\admin\\listene

r.ora

写入c:\\app\\administrator\\product\\11.2.0\\dbhome\_1\\log\\diag\\tnslsnr\\wangwei\\listen

er\\alert\\log.xml的日志信息

监听: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=wangwei)(PORT=1521)))

正在连接到 (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=wangwei)(PORT=1521)))

LISTENER 的 STATUS

\------------------------

别名                      LISTENER

版本                      TNSLSNR for 32-bit Windows: Version 11.2.0.1.0 - Produ

ction

启动日期                  12-5月 \-2013 12:15:37

正常运行时间              0 天 0 小时 0 分 1 秒

跟踪级别                  off

安全性                    ON: Local OS Authentication

SNMP                      OFF

监听程序参数文件          C:\\app\\Administrator\\product\\11.2.0\\dbhome\_1\\network\\a

dmin\\listener.ora

监听程序日志文件          c:\\app\\administrator\\product\\11.2.0\\dbhome\_1\\log\\diag\\

tnslsnr\\wangwei\\listener\\alert\\log.xml

监听端点概要...

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

服务摘要..

服务 "ora11g" 包含 1 个实例。

实例 "ora11g", 状态 UNKNOWN, 包含此服务的 1 个处理程序...

服务 "ora12g" 包含 1 个实例。

实例 "ora11g", 状态 UNKNOWN, 包含此服务的 1 个处理程序...

命令执行成功

C:\\Documents and Settings\\Administrator>

启动primary

C:\\Documents and Settings\\Administrator>sqlplus "/as sysdba"

SQL\*Plus: Release 11.2.0.1.0 Production on 星期日 5月 12 12:20:01 2013

连接到:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> host

Microsoft Windows \[版本 5.2.3790\]

C:\\Documents and Settings\\Administrator>lsnrctl status

LSNRCTL for 32-bit Windows: Version 11.2.0.1.0 - Production on 12-5月 \-2013 12:2

0:33

正在连接到 (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=wangwei)(PORT=1521)))

LISTENER 的 STATUS

\------------------------

别名                      LISTENER

版本                      TNSLSNR for 32-bit Windows: Version 11.2.0.1.0 - Produ

ction

启动日期                  12-5月 \-2013 12:09:47

正常运行时间              0 天 0 小时 10 分 46 秒

跟踪级别                  off

安全性                    ON: Local OS Authentication

SNMP                      OFF

监听程序参数文件          C:\\app\\Administrator\\product\\11.2.0\\dbhome\_1\\network\\a

dmin\\listener.ora

监听程序日志文件          c:\\app\\administrator\\product\\11.2.0\\dbhome\_1\\log\\diag\\

tnslsnr\\wangwei\\listener\\alert\\log.xml

监听端点概要...

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

服务摘要..

服务 "PRIMARY" 包含 1 个实例。