实战：Windows平台下搭建Oracle11g Dataguard

lzyever

于 2024-03-06 22:18:55 发布

阅读量1.6k

点赞数 38

分类专栏： Oracle 文章标签： oracle

本文链接：https://blog.csdn.net/lzyever/article/details/136519435

版权

Oracle 专栏收录该内容

132 篇文章 5 订阅

订阅专栏

导读
本文介绍在windows server 2012 r2 环境下搭建Oracle11g Dataguard。

将数据库改为强制日志模式（此步骤只在主库上做）

SQL>alter database force logging;
SQL> select name,log_mode,force_logging from v$database;

创建密码文件（此步骤只在主库上做）

orapwd file=orapwORCL password=oracle force=y

将主库的密码文件拷贝到备库

创建standby redolog 日志组（此步骤只在主库上做）

SQL>select thread#,group#,bytes/1024/1024 from v$log;
SQL> col member for a50
SQL> select group#,member from v$logfile;

SQL>alter database add standby logfile group 101 'D:\ORADATA\ORCL\redo101.log' size 200m;
SQL>alter database add standby logfile group 102 'D:\ORADATA\ORCL\redo102.log' size 200m;
SQL>alter database add standby logfile group 103 'D:\ORADATA\ORCL\redo103.log' size 200m;
SQL>alter database add standby logfile group 104 'D:\ORADATA\ORCL\redo104.log' size 200m;

SQL> select group#,sequence#,status, bytes/1024/1024 from v$standby_log;

SQL> set pagesize 100
SQL> col member for a60
SQL> select group#,member from v$logfile order by group#;

修改主库的pfile 参数文件（此步骤只在主库上做）

SQL> show parameter spfile;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      D:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\SPFILEORCL.ORA

=======================================================================================
SQL>create pfile from spfile;

备份pfile

编辑pfile：
orcl.__db_cache_size=79691776
orcl.__java_pool_size=12582912
orcl.__large_pool_size=12582912
orcl.__oracle_base='D:\app\Administrator'#ORACLE_BASE set from environment
orcl.__pga_aggregate_target=104857600
orcl.__sga_target=419430400
orcl.__shared_io_pool_size=58720256
orcl.__shared_pool_size=243269632
orcl.__streams_pool_size=0
*.audit_file_dest='D:\app\Administrator\admin\orcl\adump'
*.audit_trail='db'
*.compatible='11.2.0.4.0'
*.control_files='D:\oradata\orcl\control01.ctl','D:\oradata\orcl\control02.ctl','D:\oradata\orcl\control03.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='orcl'
*.diagnostic_dest='D:\app\Administrator'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
*.log_archive_dest_1='LOCATION=d:\archivelog'
*.log_archive_format='ARC%S_%R.%T'
*.nls_language='SIMPLIFIED CHINESE'
*.nls_territory='CHINA'
*.open_cursors=300
*.pga_aggregate_target=104857600
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sessions=170
*.sga_target=419430400
*.undo_tablespace='UNDOTBS1'

*.DB_UNIQUE_NAME='orcl'
*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(orcl,orcldg2)'
*.log_archive_dest_1='LOCATION=D:\archivelog  VALID_FOR=(ALL_LOGFILES,ALL_ROLES)   DB_UNIQUE_NAME=orcl'
*.log_archive_dest_2='SERVICE=orcldg2  LGWR SYNC AFFIRM    VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)    DB_UNIQUE_NAME=orcldg2'
*.LOG_ARCHIVE_DEST_STATE_1='ENABLE'
*.LOG_ARCHIVE_DEST_STATE_2='ENABLE'
*.LOG_ARCHIVE_MAX_PROCESSES=30

*.FAL_CLIENT='orcl'
*.FAL_SERVER='orcldg2'
*.DB_FILE_NAME_CONVERT='D:\oradata\orcldg2','D:\oradata\orcl'
*.LOG_FILE_NAME_CONVERT='D:\oradata\orcldg2','D:\oradata\orcl'
*.STANDBY_FILE_MANAGEMENT='AUTO'

用修改过的pfile 重新创建一个spfile ，用于重启数据库（此步骤只在主库上做）

SQL>shutdown immediate;

SQL> create spfile from pfile;

此时把数据库改为归档模式：（如果当初建库时选择了启用归档，则此步骤忽略）

由于当前数据库已关闭，首先需要把数据库启动到mount 状态

SQL> startup mount;

SQL> alter database archivelog;

SQL> alter database open;

SQL> archive log list;

如上，归档路径已经改为/oradata/archivelog，证明对pfile 的修改已生效
查看当前数据库是否使用spfile 启动：

SQL> show parameter spfile;

如上，若能看到spfile 的路径，则证明数据库是使用spfile 启动的，若没有值，则说明是用
pfile 启动的。
确认数据库已经启用归档模式和强制日志模式：

SQL>  select name,log_mode,force_logging from v$database;

修改监听文件，添加静态监听（主库、备库都要做）

主库：
添加如下内容：
SID_LIST_LISTENER =
    (SID_LIST =
        (SID_DESC =
            (GLOBAL_DBNAME = orcl)
            (ORACLE_HOME = D:\app\Administrator\product\11.2.0\dbhome_1)
            (SID_NAME = orcl)
        )
    )
添加后如下：
SID_LIST_LISTENER =
    (SID_LIST =
        (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 = 10.18.3.102)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

ADR_BASE_LISTENER = D:\app\Administrator

重启监听：

lsnrctl stop
lsnrctl start
lsnrctl status



备库：
添加如下内容：
SID_LIST_LISTENER =
    (SID_LIST =
        (SID_DESC =
            (GLOBAL_DBNAME = orcldg2)
            (ORACLE_HOME = D:\app\Administrator\product\11.2.0\dbhome_1)
            (SID_NAME = orcldg2)
        )
    )
添加后如下：

SID_LIST_LISTENER =
    (SID_LIST =
        (SID_DESC =
            (GLOBAL_DBNAME = orcldg2)
            (ORACLE_HOME = D:\app\Administrator\product\11.2.0\dbhome_1)
            (SID_NAME = orcldg2)
        )
    )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.18.3.78)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

ADR_BASE_LISTENER = D:\app\Administrator


重启监听：
lsnrctl stop
lsnrctl start
lsnrctl status

编辑网络服务名配置文件tnsnames.ora （主库和备库端都要做）

主库添加：
ORCL =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.18.3.102)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = orcl)
    )
  )

ORCLDG2 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.18.3.78)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = orcldg2)
    )
  )

备库添加：
ORCL =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.18.3.102)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = orcl)
    )
  )

ORCLDG2 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.18.3.78)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = orcldg2)
    )
  )

配置完后，确保在任意一端上都能tnsping 通对方：

tnsping orcl
tnsping orcldg2

在备库端，修改pfile 参数文件（只在备库端做）

把主库的pfile拷贝到备库进行修改，修改如下：
orcl.__db_cache_size=79691776
orcl.__java_pool_size=12582912
orcl.__large_pool_size=12582912
orcl.__oracle_base='D:\app\Administrator'#ORACLE_BASE set from environment
orcl.__pga_aggregate_target=104857600
orcl.__sga_target=419430400
orcl.__shared_io_pool_size=58720256
orcl.__shared_pool_size=243269632
orcl.__streams_pool_size=0
*.audit_file_dest='D:\app\Administrator\admin\orcldg2\adump'
*.audit_trail='db'
*.compatible='11.2.0.4.0'
*.control_files='D:\oradata\orcldg2\control01.ctl','D:\oradata\orcldg2\control02.ctl','D:\oradata\orcldg2\control03.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='orcl'
*.diagnostic_dest='D:\app\Administrator'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orcldg2XDB)'
*.log_archive_dest_1='LOCATION=d:\archivelog'
*.log_archive_format='ARC%S_%R.%T'
*.nls_language='SIMPLIFIED CHINESE'
*.nls_territory='CHINA'
*.open_cursors=300
*.pga_aggregate_target=104857600
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sessions=170
*.sga_target=419430400
*.undo_tablespace='UNDOTBS1'

*.DB_UNIQUE_NAME='orcldg2'
*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(orcl,orcldg2)'
*.log_archive_dest_1='LOCATION=D:\archivelog  VALID_FOR=(ALL_LOGFILES,ALL_ROLES)   DB_UNIQUE_NAME=orcldg2'
*.log_archive_dest_2='SERVICE=orcl  LGWR SYNC AFFIRM    VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)    DB_UNIQUE_NAME=orcl'
*.LOG_ARCHIVE_DEST_STATE_1='ENABLE'
*.LOG_ARCHIVE_DEST_STATE_2='ENABLE'
*.LOG_ARCHIVE_MAX_PROCESSES=30

*.FAL_CLIENT='orcldg2'
*.FAL_SERVER='orcl'
*.DB_FILE_NAME_CONVERT='D:\oradata\orcl','D:\oradata\orcldg2'
*.LOG_FILE_NAME_CONVERT='D:\oradata\orcl','D:\oradata\orcldg2'
*.STANDBY_FILE_MANAGEMENT='AUTO'

注意：整个搭建过程最需要留意的就是主库和备库的PFILE 配置，建议修改完后仔细对照
主备库PFILE 的区别

在备库端手工创建所需的目录（备库端做，不提前创建的话恢复时会报错！）

D:\app\Administrator\admin\orcldg2\adump
D:\app\Administrator\diag\rdbms\orcldg2\orcldg2\trace
D:\oradata\orcldg2
D:\archivelog

备库创建数据库实例
对于备机因为只安装软件,没有创建数据库实例。因此在进行下面操作前需要首先创建同名的空闲实例
oradim -new -sid orcldg1
之后即可使用:
cmd> set oracle_sid=orcldg1
SQL>sqlplus / as sysdba来连接到这个空闲实例。
在备机上注册oracle实例到服务中，命令如下：
c:\oradim -new -sid 实例名
示例如下：
c:\oradim -new -sid orcldg2

用修改后的pfile 创建一个spfile ，用于启动数据库（备库端做）

SQL> create pfile from spfile;

将数据库启动到nomount 状态：

SQL> startup nomount;

利用RMAN 在备库上恢复主库（主库端做）

rman target / auxiliary sys/oracle@orcldg2

duplicate target database for standby from active database nofilenamecheck;

尝试开启备库

SQL> select status from v$instance;
STATUS
------------------------
MOUNTED
SQL>
#（RMAN 恢复完直接就是mount 状态）

备库启动日志应用

SQL> alter database recover managed standby database disconnect from session;
Database altered.
SQL>

停止日志应用：

alter database recover managed standby database cancel;
SQL> set pagesize 100
SQL> select sequence#,applied from v$archived_log order by 1;
 SEQUENCE# APPLIED
---------- ------------------
59     YES
60     YES
61     YES
SQL>

DataGuard 保护模式切换

 查看当前的保护模式：
SQL> select database_role, protection_mode, protection_level from v$database;
DATABASE_ROLE    PROTECTION_MODE      PROTECTION_LEVEL
---------------- -------------------- --------------------
PHYSICAL STANDBY MAXIMUM PERFORMANCE  MAXIMUM PERFORMANCE
当前的模式是最大性能模式，最大性能模式是默认的模式。
下面修改成最大可用模式
最大可用模式的日志参数为：lgwr sync affirm
如果没有修改，首先要修改参数文件为lgwr sync affirm后才能执行如下命令
alter database set standby database to maximize availability;