在家完整的配好 DataGuard

最新推荐文章于 2024-11-27 17:07:51 发布

原创最新推荐文章于 2024-11-27 17:07:51 发布 · 452 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#oracle #database #archive #network #sql #server

oracle - DataGuard 专栏收录该内容

7 篇文章

订阅专栏

本文详细记录了如何在 Oracle 10G Windows 平台上部署 DataGuard 的全过程，包括设置主数据库为 forcelogging 模式、添加备用联机日志文件、创建主库的初始化参数给备库使用、在主库创建备库的控制文件和密码文件、创建监听和配置 tnsnames.ora、添加 RMAN 备份策略、启动主数据库和备用数据库等关键步骤。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

今天把在家完整配好 dataguard 的过程记录一下：

Oracle 10G windows 平台 DataGuard
server A: 192.168.11.11 SID=sheng
target server B: 192.168.11.131
oracle version:10.2.0.1
OS platform : windows XP
Primary设置：
1. 设置主数据库为force logging 模式
SQL>sqlplus "/as sysdba"
SQL>alter database force logging;
2. 设置主数据库为归档模式
SQL> archive log list
SQL> shutdown immediate
SQL> startup mount
SQL> alter database archivelog;
SQL> archive log list
3. 添加"备用联机日志文件"
先查看日志文件位置：
SQL>select * from v$logfile;
在添加：
SQL> alter database add standby logfile group 4 ('D:\ORACLE\PRODUCT\10.2.0\ORADATA\sheng\redo04.log') size 50m;

SQL> alter database add standby logfile group 5 ('D:\ORACLE\PRODUCT\10.2.0\ORADATA\sheng\redo05.log') size 50m;

SQL> alter database add standby logfile group 6 ('D:\ORACLE\PRODUCT\10.2.0\ORADATA\sheng\redo06.log') size 50m;

SQL> alter database add standby logfile group 7 ('D:\ORACLE\PRODUCT\10.2.0\ORADATA\sheng\redo07.log') size 50m;

4. 创建主库的初始化参数给备库用

SQL>Create pfile from spfile;

产生的文件名为initsheng.ora 存放目录默认放在$ORACLE_HOME/database下

5. 在主库创建备库的控制文件和密码文件

SQL>Alter database create standby controlfile as 'D:\oracle\product\10.2.0\control01.ctl';

D:>orapwd file=D:\PWDsheng.ora password=admin entries=5;

缺省情况下，win下口令文件的格式是pwdsid.ora，unix下的格式是orapwSID(大小写敏感）

6. 在主库创建监听和配置tnsnams.ora

listener.ora配置如下:

# listener.ora Network Configuration File: D:\oracle\product\10.2.0\db_1\network\admin\listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
(SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = D:\oracle\product\10.2.0\db_1)
      (PROGRAM = extproc)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = sheng)
      (ORACLE_HOME = D:\oracle\product\10.2.0\db_1)
      (SID_NAME = sheng)
    )
)
LISTENER =
(DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.11.11)(PORT = 1521))
    )
)

tnsnames.ora配置如下:
# tnsnames.ora Network Configuration File: D:\oracle\product\10.2.0\db_1\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.
primary =
(DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.11.11)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = sheng)
    )
)

standby =
(DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.11.131)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = sheng)
    )
)
EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
    )
    (CONNECT_DATA =
      (SID = PLSExtProc)
      (PRESENTATION = RO)
    )
)

7. 在initsheng.ora添加以下内容：

*.log_archive_format='%T%S%r.ARC'
*.DB_UNIQUE_NAME='primary'
*.log_archive_config='DG_CONFIG=(primary,standby)'
*.log_archive_dest_1='location=D:\oracle\product\10.2.0\oradata\arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=primary'
*.log_archive_dest_2='SERVICE=standby arch ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=standby'
*.STANDBY_FILE_MANAGEMENT=AUTO
*.LOG_ARCHIVE_DEST_STATE_1=ENABLE
*.LOG_ARCHIVE_DEST_STATE_2=ENABLE
*.FAL_SERVER='standby'
*.FAL_CLIENT='primary'

关闭数据库，在用initsheng.ora重启，并创建spfile文件

SQL>startup pfile='$ORACLE_HOME/database/initsheng.ora'

SQL>create spfile from pfile='$ORACLE_HOME/database/initsheng.ora'

A. 拷贝数据文件，参数文件，控制文件，密码文件到备库上

initsheng.ora参数文件,PWDsheng.ora密码文件考到$ORACLE_HOME/database下的，

controlbak.ctl 考到$ORACLE_base/oradata/sheng/下，并分别重命名为control01.ctl,control01.ctl,control01.ctl

数据文件考到$ORACLE_base/oradata/sheng/下

B. 用Rman拷贝,不用停机

$ rman target /

RMAN> backup full format 'D:/FULL_%d_%T_%s.bak' database include current controlfile for standby;

RMAN> sql 'alter system archive log current';

RMAN> Backup ArchiveLog all format='D:/arch_%d_%T_%s.bak';

备份完后将备份文件拷到standby上同样的目录，强调：同样的目录，在standby进行rman 恢复即可

9. 启动主数据库

SQL>startup
---------------------------------------------------------------------

Standy操作：

1. 用oradim工具创建备库sheng实例
oradim.exe -new -sid sheng -startmode m
oradim.exe -edit -sid sheng -startmode a

2. 创建备库存放数据文件和后台跟踪目录

   $ORACLE_BASE\ORADATA\sheng
   $ORACLE_BASE\admin\sheng
   $ORACLE_BASE\admin\sheng\adump
   $ORACLE_BASE\admin\sheng\bdump
   $ORACLE_BASE\admin\sheng\cdump
   $ORACLE_BASE\admin\sheng\dpdump
   $ORACLE_BASE\admin\sheng\pfile
   $ORACLE_BASE\admin\sheng\udump
   $ORACLE_BASE\admin\sheng\
3. 添加"备用联机日志文件"

SQL>startup mount

先查看日志文件位置：

SQL>select * from v$logfile;

在添加：

SQL> alter database add standby logfile group 4 ('D:\ORACLE\PRODUCT\10.2.0\ORADATA\sheng\redo04.log') size 50m;

SQL> alter database add standby logfile group 5 ('D:\ORACLE\PRODUCT\10.2.0\ORADATA\sheng\redo05.log') size 50m;

SQL> alter database add standby logfile group 6 ('D:\ORACLE\PRODUCT\10.2.0\ORADATA\sheng\redo06.log') size 50m;

SQL> alter database add standby logfile group 7 ('D:\ORACLE\PRODUCT\10.2.0\ORADATA\sheng\redo07.log') size 50m;

4. 在备库创建监听和配置tnsnams.ora

listener.ora配置如下:

# listener.ora Network Configuration File: D:\oracle\product\10.2.0\db_1\network\admin\listener.ora

# Generated by Oracle configuration tools.

LISTENER =
(DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.11.131)(PORT = 1521))
    )
)

tnsnames.ora配置如下:

# tnsnames.ora Network Configuration File: D:\oracle\product\10.2.0\db_1\network\admin\tnsnames.ora

# Generated by Oracle configuration tools.

primary =

(DESCRIPTION =

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

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = sheng)

)

standby =

(DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.11.131)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = sheng)
    )
)

EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
    )
    (CONNECT_DATA =
      (SID = PLSExtProc)
      (PRESENTATION = RO)
    )
)

5. 测试主备之间网络连通

primary:

C:>lsnrctl start

C:>tnsping standby

standby

C:>lsnrctl start

C:>tnsping primary

6. 配置备库初始化参数

编辑$ORACLE_HOME/database目录下的initsheng.ora添加以下内容

*.log_archive_format='%T%S%r.ARC'

*.DB_UNIQUE_NAME='standby'

*.log_archive_config='DG_CONFIG=(primary,standby)'

*.log_archive_dest_1='location=D:\oracle\product\10.2.0\oradata\arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=standby'

*.log_archive_dest_2='SERVICE=primary arch ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=primary'

*.STANDBY_FILE_MANAGEMENT=AUTO

*.LOG_ARCHIVE_DEST_STATE_1=ENABLE

*.LOG_ARCHIVE_DEST_STATE_2=ENABLE

*.FAL_SERVER='primary'

*.FAL_CLIENT='standby'
*.DB_FILE_NAME_CONVERT='D:\oracle\product\10.2.0\oradata\sheng\','C:\oracle\product\10.2.0\oradata\sheng\'

注明：上面这个DB_FILE_NAME_CONVERT 是在datafie路径不同的情况下使用的参数。

7. 启动备用数据库

C:>set ORACLE_SID=sheng

SQL>sqlplus "/as sysdba"

SQL>startup nomount pfile='D:\oracle\product\10.2.0\db_1\database\initsheng.ora';

SQL>create spfile from pfile='D:\oracle\product\10.2.0\db_1\database\initsheng.ora';

若采用Rman备份的, 则在此standby 端进行 Rman还原数据库：

$rman target sys/admin@primary auxiliary /

RMAN> duplicate target database for standby dorecover nofilenamecheck;

SQL>alter database mount standby database ;

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

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

测试

注意Data Guard 启动顺序：

启动顺序：先standby ,后primary;
关闭顺序：先primary 后standby;

在备库将实例启动到mount 状态：

SQL> startup nomount;

SQL>alter database mount standby database ;

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

如果要查询备库中的数据：

SQL>ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL; (注意，这和上面 alter database .... disconnect from session 是互坼的)

alter database open read only;

select * from table_name

在备库启动监听：

$lsnrctl start

在主库启动实例：

SQL> startup;

在主库启动监听：

$lsnrctl start

在主库验证归档目录是否有效：

SQL> SELECT STATUS,DESTINATION, ERROR FROM V$ARCHIVE_DEST;

如果有错误，要排查原因。

SQL> alter system switch logfile;

SQL> select max(sequence#) from v$archived_log;

MAX(SEQUENCE#)

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

主备查询结果一致，Data Guard 搭建结束。

1. 测试主库产生的归档日志是否能正常传送到归档日志

主库进行日志切换:

SQL>Alter system switch logfile;

然后分别查看主库和备库的D:\arch目录下是否产生了同样的归档日志

文件。

select max(sequence#) from v$archived_log;

在家完整的 配好 DataGuard

在家完整的配好 DataGuard