oracle dataguard windows,Oracle windows-linux DataGuard

环境描述：

| | 主库 | 备库 |

| -------------- | ------------ | ---------- |

| 版本 | 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

---

NO

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:appAdministratorproduct11.2.0dbhome_1databaseINITorcl.ORA

内容如下：

orcl.__db_cache_size=352321536

orcl.__java_pool_size=4194304

orcl.__large_pool_size=12582912

orcl.__oracle_base='C:appAdministrator'#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:appAdministratoradminorcladump'

*.audit_trail='db'

*.compatible='11.2.0.4.0'

*.control_files='C:appAdministratororadataorclcontrol01.ctl','C:appAdministratororadataorclcontrol02.ctl'

*.db_block_size=8192

*.db_domain=''

*.db_name='orcl'

*.diagnostic_dest='C:appAdministrator'

*.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:appAdministratorarch 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:APPADMINISTRATORORADATAORCL'

log_file_name_convert='/oracle/app/oradata/stddb/','C:APPADMINISTRATORORADATAORCL'

LOG_ARCHIVE_FORMAT=%t_%s_%r.arc

LOG_ARCHIVE_MAX_PROCESSES=4

**4.创建相应目录：**

C:appAdministratorarch

**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:APPADMINISTRATORORADATAORCLSTD_REDO04.LOG' size 100M;

SYS> alter database add standby logfile group 5

'C:APPADMINISTRATORORADATAORCLSTD_REDO05.LOG' size 100m;

SYS> alter database add standby logfile group 6

'C:APPADMINISTRATORORADATAORCLSTD_REDO06.LOG' size 100M;

SYS> alter database add standby logfile group 7

'C:APPADMINISTRATORORADATAORCLSTD_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:appAdministratorarch

最早的联机日志序列 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:APPADMINISTRATORORADATAORCL','/oracle/app/oradata/stddb/'

log_file_name_convert='C:APPADMINISTRATORORADATAORCL','/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:appAdministratorproduct11.2.0dbhome_1NETWORKADMINtnsnames.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:UsersAdministrator>tnsping pridb

C:UsersAdministrator>tnsping stddb

--sqlplus测试

C:UsersAdministrator>sqlplus sys/oracle@pridb as sysdba

C:UsersAdministrator>sqlplus sys/oracle@stddb as sysdba

**四：恢复备库**

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

C:UsersAdministrator>rman target sys/oracle@pridb auxiliary sys/oracle@stddb

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

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

已连接到目标数据库: 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