在家完整的 配好 DataGuard

本文详细记录了如何在 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'

8.

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.

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.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#)

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

            70

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

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

主库进行日志切换:

SQL>Alter system switch logfile;

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

文件。

select max(sequence#) from v$archived_log;

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

shenghuiping2001

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付:¥1
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值