Windows2012 安装oracle11g DG

安装环境

操作系统：windows 2012 r2企业版，内存4G

数据库：Oracle 11.2.0.4 for win64

DG架构

安装步骤

以下安装步骤需要分别在主备库操作。备库不需要安装数据库(dbca).

上传安装介质

解压到同一目录下

然后将2of7文件夹下stage的文件夹复制到1of7文件夹里。

双击setup.exe安装

正在启动安装OUI

主库创建数据库实例

cmd模式下运行dbca

测试

数据库状态正常。安装成功，下面就是一些参数的调整。

主备库相关配置

主库设置为force logging

SQL> alter database force logging;

Database altered.

--验证

SQL> select force_logging from v$database;

FORCE_

------

YES

修改为归档模式

现在是非归档

SQL> archive log list;

Database log mode No Archive Mode

Automatic archival Disabled

Archive destination USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence 4

Current log sequence 6

启动到mount阶段

Shutdown immediate;

Startup mount;

SQL> alter database archivelog;

Database altered.

SQL> alter database open;

Database altered.

--已经归档模式了

SQL> archive log list;

Database log mode Archive Mode

Automatic archival Enabled

Archive destination USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence 4

Next log sequence to archive 6

Current log sequence 6

修改online log的大小

--查看原来大小，50M显然不够的。调整为10G一个

SQL> select GROUP#,SEQUENCE#,BYTES/1024/1024,STATUS from v$log;

GROUP# SEQUENCE# BYTES/1024/1024 STATUS

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

1 4 50 INACTIVE

3 3 50 INACTIVE

2 5 50 CURRENT

SQL> col member for a50

SQL> select group#,type, member from v$logfile;

GROUP# TYPE MEMBER

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

3 ONLINE C:\APP\ADMINISTRATOR\ORADATA\ORCL\REDO03.LOG

2 ONLINE C:\APP\ADMINISTRATOR\ORADATA\ORCL\REDO02.LOG

1 ONLINE C:\APP\ADMINISTRATOR\ORADATA\ORCL\REDO01.LO-创建新的日志组，删除之前的日志组

创建新的日志组

alter database add logfile group 4 ('C:\APP\ADMINISTRATOR\ORADATA\ORCL\REDO04.LOG’) size 500M;

alter database add logfile group 5 ('C:\APP\ADMINISTRATOR\ORADATA\ORCL\REDO05.LOG’) size 500M;

alter database add logfile group 6 ('C:\APP\ADMINISTRATOR\ORADATA\ORCL\REDO06.LOG’) size 500M;

alter database add logfile group 7 ('C:\APP\ADMINISTRATOR\ORADATA\ORCL\REDO07.LOG’) size 500M;

--删除之前日志组

alter system switch logfile;

alter system switch logfile;

alter system switch logfile;

alter database drop logfile group 1;
alter database drop logfile group 2;
alter database drop logfile group 3;

--操作系统下删除原日志组1、2、3中的文件

--新的日志组

GROUP# SEQUENCE# BYTES/1024/1024 STATUS

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

4 33 500 INACTIVE

5 34 500 CURRENT

6 29 500 INACTIVE

7 30 500 INACTIVE

8 rows selected.

SQL> select group#,type, member from v$logfile;

GROUP# TYPE MEMBER

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

4 ONLINE C:\APP\ADMINISTRATOR\ORADATA\ORCL\REDO04.LOG

5 ONLINE C:\APP\ADMINISTRATOR\ORADATA\ORCL\REDO05.LOG

6 ONLINE C:\APP\ADMINISTRATOR\ORADATA\ORCL\REDO06.LOG

7 ONLINE C:\APP\ADMINISTRATOR\ORADATA\ORCL\REDO07.LOG

8 rows selected.

主库添加standby redo log

主库添加 standby redo log：大小和 online redo 相同，比 online redo group 多一组。

ALTER DATABASE ADD STANDBY LOGFILE GROUP 9 ('C:\APP\ADMINISTRATOR\ORADATA\ORCL\redo08.log') size 500M;

ALTER DATABASE ADD STANDBY LOGFILE GROUP 10 ('C:\APP\ADMINISTRATOR\ORADATA\ORCL\redo09.log') size 500M;

ALTER DATABASE ADD STANDBY LOGFILE GROUP 11 ('C:\APP\ADMINISTRATOR\ORADATA\ORCL\redo10.log') size 500M;

ALTER DATABASE ADD STANDBY LOGFILE GROUP 12 ('C:\APP\ADMINISTRATOR\ORADATA\ORCL\redo11.log') size 500M;

ALTER DATABASE ADD STANDBY LOGFILE GROUP 13 ('C:\APP\ADMINISTRATOR\ORADATA\ORCL\redo12.log') size 500M;

--查看结果

SQL> select group#,type, member from v$logfile;

GROUP# TYPE MEMBER

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

9 STANDBY C:\APP\ADMINISTRATOR\ORADATA\ORCL\REDO08.LOG

10 STANDBY C:\APP\ADMINISTRATOR\ORADATA\ORCL\REDO09.LOG

11 STANDBY C:\APP\ADMINISTRATOR\ORADATA\ORCL\REDO10.LOG

4 ONLINE C:\APP\ADMINISTRATOR\ORADATA\ORCL\REDO04.LOG

5 ONLINE C:\APP\ADMINISTRATOR\ORADATA\ORCL\REDO05.LOG

6 ONLINE C:\APP\ADMINISTRATOR\ORADATA\ORCL\REDO06.LOG

7 ONLINE C:\APP\ADMINISTRATOR\ORADATA\ORCL\REDO07.LOG

12 STANDBY C:\APP\ADMINISTRATOR\ORADATA\ORCL\REDO11.LOG

13 STANDBY C:\APP\ADMINISTRATOR\ORADATA\ORCL\REDO12.LOG

9 rows selected.这里创建的 standby redo log 在 ADG 复制过程中会自动复制到备库。

主备库配置监听

推荐使用netca和netmgr工具进行配置

点击”开始”，找到Net Configuration Assistant。如图

配置结束。

查看监听服务是否启动

1. 通过cmd查看

lsnrctl status

1. 通过服务查看

Win + r 然后输入 services.msc

配置静态监听

Net manager

配置服务名

继续配置orcl_p的服务名

最后结果是：

重启监听

C:\Users\Administrator>lsnrctl stop

C:\Users\Administrator>lsnrctl start

验证配置是否正确

C:\Users\Administrator>tnsping orcl_p

TNS Ping Utility for 64-bit Windows: Version 11.2.0.4.0 - Production on 27-12月-2016 15:31:03

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

已使用的参数文件:

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

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

尝试连接 (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.18)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = ORCL)))

OK (0 毫秒)

C:\Users\Administrator>tnsping orcl_s

TNS Ping Utility for 64-bit Windows: Version 11.2.0.4.0 - Production on 27-12月-2016 15:31:05

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

已使用的参数文件:

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

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

尝试连接 (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.19)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = ORCL)))

OK (0 毫秒)

连接主库看是否正常

主库创建参数文件

Create pfile from spfile;

红框中是新增的内容。

关闭主库

SQL> shutdown immediate

备库创建文件夹

在 cmd 下执行

MD C:\app\Administrator\oradata

MD C:\app\Administrator\oradata\orcl

MD C:\app\Administrator\diag\rdbms\orcl

MD C:\app\Administrator\diag\rdbms\orcl\orcl

MD C:\app\Administrator\diag\rdbms\orcl\orcl\alert

MD C:\app\Administrator\diag\rdbms\orcl\orcl\cdump

MD C:\app\Administrator\diag\rdbms\orcl\orcl\hm

MD C:\app\Administrator\diag\rdbms\orcl\orcl\incident

MD C:\app\Administrator\diag\rdbms\orcl\orcl\incpkg

MD C:\app\Administrator\diag\rdbms\orcl\orcl\ir

MD C:\app\Administrator\diag\rdbms\orcl\orcl\lck

MD C:\app\Administrator\diag\rdbms\orcl\orcl\metadata

MD C:\app\Administrator\diag\rdbms\orcl\orcl\stage

MD C:\app\Administrator\diag\rdbms\orcl\orcl\sweep

MD C:\app\Administrator\diag\rdbms\orcl\orcl\trace

MD C:\app\Administrator\flash_recovery_area\orcl

MD C:\app\Administrator\flash_recovery_area\orcl\ONLINELOG

备库新建实例

在备库上注册oracle实例到服务中，命令
oradim -new -sid orcl

备库启动监听

修改备库参数文件

将主库的pfile文件复制到备库并修改为红色部分

用参数文件建立实例

ADG来复制文件到备库

--如果路径不相同，使用这个：

duplicate target database for standby from active database;

RMAN> duplicate target database for standby from active database nofilenamecheck; --主备库路径相同

稍等一会。。

出现上面的信息，表示已经成功了。

验证DG

#打开备库

SQL>alter database open;

#启动MRP进程

alter database recover managed standby database disconnect from session;

#查看进行MRP

SQL> select process,status from v$managed_standby;

#查看备库状态

select open_mode from v$database;

#查看主备库日志序列号是否一致

主库：

备库：

#主库创建个表

备库查看

可以看到主备库是同步的。

其他参数的调整

修改DB_FILES,PROCESS参数sele

重启生效。

设置CONTROL_FILE_RECORD_KEEP_TIME 参数

设置open_links_per_instance 和 open_links 参数

需要重启实例

修改用户的profile 参数

Enabling and Using Direct I/O

SQL> show parameter filesystem_
NAME TYPE VALUE
-------------------- ------ -----
filesystemio_options string none

SQL> alter system set filesystemio_options="directIO" sid='*' scope=spfile;

System altered.

禁用dictory path read

二选一操作即可：

SQL>alter system set "_serial_direct_read"=never;

SQL> ALTER system SET EVENTS '10949 TRACE NAME CONTEXT off';

调整UNDO 和 Temp 表空间

SQL> show parameter undo_

NAME TYPE VALUE

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

undo_management string AUTO

undo_retention integer 900

undo_tablespace string UNDOTBS1

undo_retention 只是指定undo 数据的过期时间，默认是900s，15分钟。建议改成3600s，即1小时。

SQL> alter system set undo_retention=3600 scope=both sid='*';

System altered.

注意UNDO 表空间一定要设成成自动扩展并设置最大值。

ALTER DATABASE DATAFILE '/u01/app/oracle/oradata/orcl/undo01.dbf' RESIZE 30720M;

ALTER DATABASE DATAFILE '/u01/app/oracle/oradata/orcl/undo01.dbf' AUTOEXTEND ON NEXT 1M MAXSIZE 30720M;

ALTER DATABASE tmpefile '/u01/app/oracle/oradata/orcl/temp01.dbf' RESIZE 30720M;

ALTER DATABASE tmpefile '/u01/app/oracle/oradata/orcl/temp01.dbf' AUTOEXTEND ON NEXT 1M MAXSIZE 30720M;