Windows 环境下配置 oracle 11gR2

- 日理万妓 2022-05-31 05:18 242阅读 0赞

Data Guard 详细过程  
1 服务器环境：  
两台 PC 机，操作系统为 Windows server 2008 R2  
数据库准备为 oracle 11.2.0.3 for win32  
2 配置前准备：  
主库主机名 service1，ip 配置为 192.168.104.11，db\_name:orcl，  
db\_unique\_name：primary  
备库主机名 service2，ip 配置为 192.168.104.21.db\_name:orcl，  
db\_unique\_name：standby  
在安装 oracle 时，主库安装数据库，备库只安装软件  
安装路径在 D 盘，具体路径为默认 D:\\app\\Administrator\\。  
实例名为 orcl  
数据文件放在 E:\\oracleDB\\orcl 文件夹下，归档日志文件放在  
E:\\oracleDB\\flash\\orcl\\ARCHIVELOG 文件夹下，联机日志文件默认  
3 正式配置过程：  
3.1 主库配置  
3 正式配置过程：  
3.1 主库配置  
在主库启动数据库到 mount 模式，开启归档模式与 force  
logging flashback。  
  
Shutdown immediate  
Startup mount  
Alter database archivelog;  
Alter database force logging;  
Alter database flashback on;  
3.2 查看当前日志文件组  
Select \* from v$logfile order by group\#;  
  
3.3 为备用库创建日志文件：  
这里四组可以简单的理解为在线日志组数+1  
Alter database add standby logfile group 4  
(‘E:\\oracleDB\\orcl\\redo004.log’) size 50m;  
Alter database add standby logfile group 5  
(‘E:\\oracleDB\\orcl\\redo005.log’) size 50m;  
Alter database add standby logfile group 6  
(‘E:\\oracleDB\\orcl\\redo006.log’) size 50m;  
Alter database add standby logfile group 7  
(‘E:\\oracleDB\\orcl\\redo007.log’) size 50m;  
  
再次查询 v$logfile  
已经建好备用日志了。  
3.4 修改系统参数 spfile 文件(13 个参数)  
1）Alter system set db\_unique\_name='primary' scope=spfile;  
2）Alter system set  
log\_archive\_config='DG\_CONFIG=(primary,standby)'  
scope=spfile;  
  
3）Alter system set  
log\_archive\_dest\_1='location=E:\\oracleDB\\flash\\orcl\\ARCHIVE  
LOG\\ VALID\_FOR=(ALL\_LOGFILES,ALL\_ROLES)  
DB\_UNIQUE\_NAME=primary' scope=spfile;  
4）Alter system set log\_archive\_dest\_2='SERVICE=standby  
LGWR ASYNC VALID\_FOR=(ONLINE\_LOGFILES,PRIMARY\_ROLE)  
DB\_UNIQUE\_NAME=standby' scope=spfile;  
5）Alter system set log\_archive\_dest\_state\_1 = 'enable'  
scope=spfile;  
6 ）Alter system set log\_archive\_dest\_state\_2 = 'enable'  
scope=spfile;  
7 ）Alter system set fal\_server='standby' scope=spfile;  
8 ）Alter system set fal\_client='primary' scope=spfile;  
9 ）Alter system set archive\_lag\_target=1800 scope=spfile;  
10）Alter system set log\_archive\_format='%t\_%s\_%r.arc'  
scope=spfile;  
11）Alter system set standby\_file\_management=auto  
scope=spfile;  
12）Alter system set  
db\_file\_name\_convert='standby','primary' scope=spfile;  
13）Alter system set  
log\_file\_name\_convert='standby','primary' scope=spfile;  
  
3.5 启动数据库  
Alter database open;  
重启数据库将修改的参数加载；  
Shutdown immediate  
Startup  
3.6 创建 pfile 参数文件；  
Create pfile from spfile;  
默认路径下  
D:\\app\\Administrator\\product\\11.2.0\\dbhome\_1\\database\\INITo  
rcl.ORA 。  
  
3.7 创建密码文件  
密码文件存放在：  
D:\\app\\Administrator\\product\\11.2.0\\dbhome\_1\\database\\  
文件名为：PWDorcl.ora  
如果在目录下未能找见，则需要动手创建，创建命令为：  
Orapwd  
file=D:\\app\\Administrator\\product\\11.2.0\\dbhome\_1\\database\\  
PWDorcl.ora password=123 entries=5  
  
注：在 cmd 命令下输入 orapwd。Orcl 为实例名，123 为主库 sys  
的密码  
3.8 配置主库网络监听（备用库同理）  
文件位置在  
D:\\app\\Administrator\\product\\11.2.0\\dbhome\_1\\NETWORK\\ADMIN\\  
修改 listener.ora 文件，内容如下：  
\# listener.ora Network Configuration File:  
d:\\app\\Administrator\\product\\11.2.0\\dbhome\_1\\network\\admin\\  
listener.ora  
\# Generated by Oracle configuration tools.  
SID\_LIST\_LISTENER =  
(SID\_LIST =  
(SID\_DESC =  
(SID\_NAME = CLRExtProc)  
(ORACLE\_HOME =  
d:\\app\\Administrator\\product\\11.2.0\\dbhome\_1)  
(PROGRAM = extproc)  
(ENVS =  
"EXTPROC\_DLLS=ONLY:d:\\app\\Administrator\\product\\11.2.0\\dbhome\_1\\bin\\oraclr11.dll")  
)  
(SID\_DESC =  
(GLOBAL\_DBNAME = orcl)  
(ORACLE\_HOME =  
d:\\app\\Administrator\\product\\11.2.0\\dbhome\_1)  
(SID\_NAME = orcl)  
)  
)  
LISTENER =  
(DESCRIPTION\_LIST =  
(DESCRIPTION =  
(ADDRESS = (PROTOCOL = TCP)(HOST =  
192.168.104.11)(PORT = 1521))  
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))  
)  
)  
ADR\_BASE\_LISTENER = d:\\app\\Administrator  
  
红色内容为需要修改成的。  
修改 tnsnames.ora 文件，内容如下：  
\# tnsnames.ora Network Configuration File:  
d:\\app\\Administrator\\product\\11.2.0\\dbhome\_1\\NETWORK\\ADMIN\\  
tnsnames.ora  
\# Generated by Oracle configuration tools.  
PRIMARY =  
(DESCRIPTION =  
(ADDRESS\_LIST =  
(ADDRESS = (PROTOCOL = TCP)(HOST =  
192.168.104.11)(PORT = 1521))  
)  
(CONNECT\_DATA =  
(SERVICE\_NAME = orcl)  
)  
)  
ORACLR\_CONNECTION\_DATA =  
(DESCRIPTION =  
(ADDRESS\_LIST =  
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))  
)  
(CONNECT\_DATA =  
(SID = CLRExtProc)  
(PRESENTATION = RO)  
)  
)  
STANDBY =  
(DESCRIPTION =  
(ADDRESS\_LIST =  
(ADDRESS = (PROTOCOL = TCP)(HOST =  
192.168.104.21)(PORT = 1521))  
)  
(CONNECT\_DATA =  
(SERVICE\_NAME = orcl)  
)  
)  
ORCL =  
(DESCRIPTION =  
(ADDRESS\_LIST =  
(ADDRESS = (PROTOCOL = TCP)(HOST =  
192.168.104.11)(PORT = 1521))  
)  
(CONNECT\_DATA =  
(SERVICE\_NAME = orcl)  
)  
)  
红色内容为需要修改成的。  
3.9 测试监听  
重启监听，用网络名连接测试监听是否正常  
C:\\>lsnrctl stop  
C:\\>lsnrctl start  
重启数据库：  
shutdown immediate ---> startup  
测试监听  
C:\\>tnsping primary  
C:\\>sqlplus sys/syspwd@primary as sysdba  
  
如果不能连接，请检查防火墙。  
3.10 关闭注数据库  
3.11 在备用建立文件夹  
3.10 关闭注数据库  
3.11 在备用建立文件夹  
A. 将 D:\\app\\Administrator 目录下的 admin、cfgtoollogs、  
diag目录及E:\\oracleDB\\下的flash目录及密码文件拷贝到备用  
库相同的路径下。  
B. 将主库的 listener.ora 和 tnsnames.ora 拷贝到备库相同  
路径下。  
注意修改 listener.or 中 IP 地址为备机 IP  
LISTENER =  
(DESCRIPTION\_LIST =  
(DESCRIPTION =  
(ADDRESS = (PROTOCOL = TCP)(HOST =  
192.168.104.21)(PORT = 1521))  
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))  
)  
)  
在主备机 E 盘建立 E:\\oracleDB\\tmp 文件夹  
3.12 备机新建实例  
在备机上注册 oracle 实例到服务中，命令如下：  
Oradim -new -sid orcl（实例名）  
3.13 在备库启动监听  
3.14 备库参数文件  
将刚才主库创建的pfile参数文件initorcl.ora文件拷贝至备库  
D 盘根目录下修改为如下：  
orcl.\_\_db\_cache\_size=536870912  
orcl.\_\_java\_pool\_size=8388608  
orcl.\_\_large\_pool\_size=8388608  
orcl.\_\_oracle\_base='d:\\app\\Administrator'\#ORACLE\_BASE  
set from environment  
orcl.\_\_pga\_aggregate\_target=520093696  
orcl.\_\_sga\_target=771751936  
orcl.\_\_shared\_io\_pool\_size=0  
orcl.\_\_shared\_pool\_size=209715200  
orcl.\_\_streams\_pool\_size=0  
\*.archive\_lag\_target=1800  
\*.audit\_file\_dest='d:\\app\\Administrator\\admin\\orcl\\adump'  
\*.audit\_trail='db'  
\*.compatible='11.2.0.0.0'  
\*.control\_files='E:\\oracleDB\\orcl\\control01.ctl','E:\\ora  
cleDB\\flash\\orcl\\control02.ctl'  
\*.db\_block\_size=8192  
\*.db\_domain=''  
\*.db\_file\_name\_convert='primary','standby'  
\*.db\_name='orcl'  
\*.db\_recovery\_file\_dest='E:\\oracleDB\\flash'  
\*.db\_recovery\_file\_dest\_size=10737418240  
\*.db\_unique\_name='standby'  
\*.diagnostic\_dest='d:\\app\\Administrator'  
\*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'  
\*.fal\_client='standby'  
\*.fal\_server='primary'  
\*.log\_archive\_config='DG\_CONFIG=(primary,standby)'  
\*.log\_archive\_dest\_1='location=E:\\oracleDB\\flash\\orcl\\AR  
CHIVELOG\\ VALID\_FOR=(ALL\_LOGFILES,ALL\_ROLES)  
DB\_UNIQUE\_NAME=standby'  
\*.log\_archive\_dest\_2='SERVICE=primary LGWR ASYNC  
VALID\_FOR=(ONLINE\_LOGFILES,PRIMARY\_ROLE)  
DB\_UNIQUE\_NAME=primary'  
\*.log\_archive\_dest\_state\_1='enable'  
\*.log\_archive\_dest\_state\_2='enable'  
\*.log\_archive\_format='%t\_%s\_%r.arc'  
\*.log\_file\_name\_convert='primary','standby'  
\*.memory\_target=1287651328  
\*.open\_cursors=300  
\*.processes=150  
\*.remote\_login\_passwordfile='EXCLUSIVE'  
\*.standby\_file\_management='AUTO'  
\*.undo\_tablespace='UNDOTBS1'  
以上红色标注为需要修改成的  
用参数文件建立实例  
Startup nomount pfile=’  
D:\\app\\Administrator\\product\\11.2.0\\dbhome\_1\\database\\INITo  
rcl.ORA’  
3.15 使用 RMAN 复制主库  
在主库 startup  
3.15.1 用 rman 建立备份  
RMAN>Backup full database format=’  
E:\\oracleDB\\tmp\\FOR\_STANDBY\_%u%p%s.RMN’ include  
current controlfile for standby;  
将当前 archivelog 归档  
RMAN>sql’alter system archive log current’;  
3.15.2 将 rman 的备份集拷贝到 standby 库相同的路径  
3.15.3 复制数据库  
3.15.2 将 rman 的备份集拷贝到 standby 库相同的路径  
3.15.3 复制数据库  
RMAN>connect auxiliary sys/syspwd@standby  
RMAN>duplicate target database for standby  
nofilenamecheck;  
…………………………  
3.15.4 备库启动 standby  
Alter database mount standby database;  
Alter database recover managed standby database  
disconnect from session;  
至此 DataGuard 的搭建就算完成了  
4 DataGuard 验证  
下面来验证是否搭建成功  
主库执行  
Select name,sequence\#,applied,completion\_time from  
v$archived\_log order by completion\_time desc,sequence\# desc;  
两边 sequence\#一致，说明已经同步  
下面在主库切换活动日志，然后查询日志同步情况  
Alter system switch logfile;  
5 关于 switchover（正常主备切换）  
首先查询主库切换状态  
Select switchover\_status from v$database;  
TO STANDBY 表示可以切换，如果是其它的需要根据情况处理后再切换。  
在主库将 primary 切换到 physical standby  
主库执行以下命令  
Alter database commit to switchover to physical standby with  
session shutdown;  
Shutdown immediate  
Startup nomount  
Alter database mount standby database;  
Alter database recover managed standby database disconnect  
from session;  
在备库，switchover 到 primary：  
Alter database commit to switchover to primary;  
Shutdown immediate  
Startup  
Alter system switch logfile;  
在新主库和新备库执行如下命令：  
Select name,sequence\#,applied,completion\_time from  
v$archived\_log order by completion\_time desc,sequence\# desc;  
在新主库执行：alter system switch logfile;  
新主库执行