Oracle windows-linux（DG）_windows oracle dg 网络环境要求-CSDN博客

本文链接：https://blog.csdn.net/baoyuhang0/article/details/108099581

环境描述：

	主库	备库
版本	windows11204	linux11204
IP地址	209	145
SID	orcl	stddb
DB_NAME	orcl	orcl
DB_UNIQUE_NAME	pridb	stddb
SERVICE_NAME	pridb	stddb

一、主库的前期准备工作

1.Enable Forced Logging --主库设置强制日志，保证所有的操作都记录到日志文件

SQL> select force_logging from v$database;

FOR

—

SQL> alter database force logging;

数据库已更改。

SQL> select force_logging from v$database;

FOR

—

YES

2.检查主库的密码文件，同一个Data Guard 配置中所有数据库必须都拥有独立的密码文件，

–并且必须保证同一个Data Guard 配置中所有数据库服务器的SYS 用户拥有相同密码以保证redo 数据的顺利传输，

–因为redo传输服务通过认证的网络会话来传输redo 数据，而会话使用包含在密码文件中的SYS 用户密码来认证。

3.设置主库的参数文件

手动编辑

C:\app\Administrator\product\11.2.0\dbhome_1\database\INITorcl.ORA

内容如下：

orcl.__db_cache_size=352321536

orcl.__java_pool_size=4194304

orcl.__large_pool_size=12582912

orcl.__oracle_base=‘C:\app\Administrator’#ORACLE_BASE set from environment

orcl.__pga_aggregate_target=343932928

orcl.__sga_target=515899392

orcl.__shared_io_pool_size=0

orcl.__shared_pool_size=134217728

orcl.__streams_pool_size=0

*.audit_file_dest=‘C:\app\Administrator\admin\orcl\adump’

*.audit_trail=‘db’

*.compatible=‘11.2.0.4.0’

*.control_files=‘C:\app\Administrator\oradata\orcl\control01.ctl’,‘C:\app\Administrator\oradata\orcl\control02.ctl’

*.db_block_size=8192

*.db_domain=’’

*.db_name=‘orcl’

*.diagnostic_dest=‘C:\app\Administrator’

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

*.memory_target=857735168

*.nls_language=‘SIMPLIFIED CHINESE’

*.nls_territory=‘CHINA’

*.open_cursors=300

*.processes=150

*.remote_login_passwordfile=‘EXCLUSIVE’

*.undo_tablespace=‘UNDOTBS1’

db_unique_name=pridb

log_archive_config=‘dg_config=(pridb,stddb)’

log_archive_dest_1=‘location=C:\app\Administrator\arch valid_for=(all_logfiles,all_roles) db_unique_name=pridb’

log_archive_dest_2=‘service=stddb valid_for=(online_logfiles,primary_role) db_unique_name=stddb’

log_archive_dest_state_1=enable

log_archive_dest_state_2=enable

db_file_name_convert=’/oracle/app/oradata/stddb/’,‘C:\APP\ADMINISTRATOR\ORADATA\ORCL’

log_file_name_convert=’/oracle/app/oradata/stddb/’,‘C:\APP\ADMINISTRATOR\ORADATA\ORCL’

LOG_ARCHIVE_FORMAT=%t_%s_%r.arc

LOG_ARCHIVE_MAX_PROCESSES=4

4.创建相应目录：

C:\app\Administrator\arch

5.重新应用新的参数文件：

SYS> shutdown immediate; --一致性停库

SYS> create spfile from pfile; --创建新的spfile

SYS> startup;

6.给主库增加standby redo logfile

主库不需要这个文件，当主库变成备库的时候才使用，个数一般是比现有日志组的数量+1

SYS> alter database add standby logfile group 4

‘C:\APP\ADMINISTRATOR\ORADATA\ORCL\STD_REDO04.LOG’ size 100M;

SYS> alter database add standby logfile group 5

‘C:\APP\ADMINISTRATOR\ORADATA\ORCL\STD_REDO05.LOG’ size 100m;

SYS> alter database add standby logfile group 6

‘C:\APP\ADMINISTRATOR\ORADATA\ORCL\STD_REDO06.LOG’ size 100M;

SYS> alter database add standby logfile group 7

‘C:\APP\ADMINISTRATOR\ORADATA\ORCL\STD_REDO07.LOG’ size 100m;

SYS> select group#,status,used from v$standby_log; --验证standby redo log 文件组是否创建成功

GROUP# STATUS USED

4 UNASSIGNED 0

5 UNASSIGNED 0

6 UNASSIGNED 0

7 UNASSIGNED 0

7.主库要设置为归档模式

SQL> archive log list;

数据库日志模式存档模式

自动存档启用

存档终点 C:\app\Administrator\arch

最早的联机日志序列 15

下一个存档日志序列 17

当前日志序列 17

二、设置standby database

1.设置一个密码文件

[oracle@service1 dbs]$ export ORACLE_SID=stddb

–指定standby database的ORACLE_SID

[oracle@service1 dbs]$ cd $ORACLE_HOME/dbs

[oracle@service1 dbs]$ orapwd file=orapwstddb password=oracle --设置standby database的密码文件

–主库备库的sys用户的密码必须一致，如果出现ora-16191错误

2.设置standby database的参数文件

[oracle@service1~]$ $ cd $ORACLE_HOME/dbs

//从主库复制一份修改

备库参数文件内容：

stddb.__db_cache_size=352321536

stddb.__java_pool_size=4194304

stddb.__large_pool_size=8388608

stddb.__oracle_base=’/u01/app/oracle’

stddb.__pga_aggregate_target=335544320

stddb.__sga_target=503316480

stddb.__shared_io_pool_size=0

stddb.__shared_pool_size=125829120

stddb.__streams_pool_size=0

*.audit_file_dest=’/oracle/app/admin/stddb/adump’

*.audit_trail=‘db’

*.compatible=‘11.2.0.4.0’

*.control_files=’/oracle/app/oradata/stddb/control01.ctl’,’/oracle/app/oradata/stddb/control02.ctl’

*.db_block_size=8192

*.db_name=‘orcl’

*.diagnostic_dest=’/oracle/app’

*.memory_target=838860800

*.open_cursors=300

*.processes=150

*.remote_login_passwordfile=‘EXCLUSIVE’

*.sessions=170

*.undo_tablespace=‘UNDOTBS1’

db_unique_name=stddb

log_archive_config=‘dg_config=(pridb,stddb)’

log_archive_dest_1=‘location=/oracle/app/arch/stddb valid_for=(all_logfiles,all_roles) db_unique_name=stddb’

log_archive_dest_2=‘service=pridb valid_for=(online_logfiles,primary_role) db_unique_name=pridb’

log_archive_dest_state_1=enable

log_archive_dest_state_2=enable

LOG_ARCHIVE_FORMAT=%t_%s_%r.arc

LOG_ARCHIVE_MAX_PROCESSES=4

fal_server=pridb

db_file_name_convert=‘C:\APP\ADMINISTRATOR\ORADATA\ORCL’,’/oracle/app/oradata/stddb/’

log_file_name_convert=‘C:\APP\ADMINISTRATOR\ORADATA\ORCL’,’/oracle/app/oradata/stddb/’

STANDBY_FILE_MANAGEMENT=AUTO

3.创建相应的路径：

[oracle@server1 ~]$ mkdir /oracle/app/oradata/stddb -pv

[oracle@server1 ~]$ mkdir /oracle/app/admin/stddb/adump -pv

[oracle@server1 ~]$ mkdir /oracle/app/arch/stddb -pv

4.启动备库实例

[oracle@service1 dbs]$ export ORACLE_SID=stddb

[oracle@service1 dbs]$ sqlplus / as sysdba

SYS@stddb>create spfile from pfile;

SYS@stddb> startup nomount;

三、配置网络

1.备库需要配置静态注册 --主库使用动态注册即可

LISTENER=

(DESCRIPTION=

(ADDRESS_LIST=

(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.99.145)(PORT=1522))

)

SID_LIST_LISTENER=

(SID_LIST=

(SID_DESC=

(GLOBAL_DBNAME=stddb)

(ORACLE_HOME=/oracle/app/product/11.2.0/db_1)

(SID_NAME=stddb))

)

2.重载监听

[oracle@service2 admin]$ lsnrctl start

[oracle@service2 admin]$ lsnrctl reload

SYS@stddb>alter system register;

[oracle@service2 admin]$ lsnrctl status

Service “stddb.neves.com” has 2 instance(s).

Instance “stddb”, status UNKNOWN, has 1 handler(s) for this service…

Instance “stddb”, status BLOCKED, has 1 handler(s) for this service…

The command completed successfully

3.在主备库配置本地服务名

主库：

C:\app\Administrator\product\11.2.0\dbhome_1\NETWORK\ADMIN\tnsnames.ora

pridb =

(DESCRIPTION =

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

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = pridb)

)

stddb =

(DESCRIPTION =

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

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = stddb)

)

备库：

[oracle@service1 admin]$ vim tnsnames.ora

pridb =

(DESCRIPTION =

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

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = pridb)

)

stddb =

(DESCRIPTION =

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

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = stddb)

)

4.测试网络连接

–tnsping：

C:\Users\Administrator>tnsping pridb

C:\Users\Administrator>tnsping stddb

–sqlplus测试

C:\Users\Administrator>sqlplus sys/oracle@pridb as sysdba

C:\Users\Administrator>sqlplus sys/oracle@stddb as sysdba

四：恢复备库

1.在主库使用RMAN同时连接主库以及备库

C:\Users\Administrator>rman target sys/oracle@pridb auxiliary sys/oracle@stddb

恢复管理器: Release 11.2.0.4.0 - Production on 星期二 8月 11 10:34:49 2020

已连接到目标数据库: ORCL (DBID=1574596224)

已连接到辅助数据库: PROD (未装载)

RMAN> duplicate target database for standby from active database

//from active database ：

//通过网络直接传输，如果使用此方式，需要将主库的备份拷贝到备库相应位置。

//恢复完之后备库自动启动到mount状态

2.查看日志传输情况：

SYS@stddb>select sequence#,applied from v$archived_log;

SEQUENCE# APPLIED

18 NO

20 NO

19 NO

3.启动日志应用服务：

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

4.查看日志应用情况：

SQL> select sequence#,applied from v$archived_log;

SEQUENCE# APPLIED

19 YES

20 YES

21 YES

18 YES

17 YES