10g 创建 Data Guard

来源：互联网发布：sql server是干嘛的编辑：程序博客网时间：2024/05/22 04:33

一环境介绍

主库： IP：172.16.10.201 SID:dg db_unique_name:pri

备库： IP：172.16.10.202 SID:dg db_unique_name:sta

数据库版本：10.2.0.5

系统版本：RHEL6.4

二主库配置归档并强制日志_primary

将数据库启动到mount 阶段打开归档：

alter database archivelog;

进行强制日志：

alter database force logging;

三修改配置文件_primary

创建pfile并添加和修改该文件

create pfile from spfile;

打开pfile文件并进行修改

*.db_unique_name='pri'
*.log_archive_config='dg_config=(pri,sta)'
*.log_archive_dest_1='location=/u02/lolog db_unique_name=pri'
*.log_archive_dest_2='service=sta lgwr async db_unique_name=sta'
*.log_archive_dest_state_1=enable
*.log_archive_dest_state_2=enable
*.fal_server=sta
*.fal_client=pri
*.standby_file_management=auto

*.DB_FILE_NAME_CONVERT='/u02/data/dg','/u02/data/dg'
*.LOG_FILE_NAME_CONVERT='/u02/data/dg','/u02/data/dg'

四配置主库listener.ora _primary

添加如下红色字体部分

SID_LIST_LISTENER =

(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /u01/oracle/db)
(PROGRAM = extproc)
)
(SID_DESC =
(GLOBAL_DBNAME = pri)
(ORACLE_HOME = /u01/oracle/db)
(SID_NAME = dg)
)
)

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = dg1.com)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
)

五配置tnsnames.ora文件 _primary

DG =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = dg1.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = dg)
)
)
pri =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = dg1.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = pri)
)
)
sta =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = dg2.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = sta)
)
)

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

六将本地的口令文件复制到远程 _primary

scp /u01/oracle/db/dbs/orapwdg oracle@dg2:/u01/oracle/db/dbs/orapwdg

七备份数据库以及创建standby控制文件 _primary

backup full format=’/u02/db%U’ database include current controlfile for standby;

alter database create standby controlfile as '/tmp/standby.ctl';

八将primary数据库pfile文件传送到standby进行修改_standby

*.db_unique_name='sta'
*.log_archive_config='dg_config=(pri,sta)'
*.log_archive_dest_1='location=/u02/lolog db_unique_name=sta'
*.log_archive_dest_2='service=pri lgwr async db_unique_name=pri'
*.log_archive_dest_state_1=enable
*.log_archive_dest_state_2=enable
*.fal_server=pri
*.fal_client=sta
*.standby_file_management=auto
*.DB_FILE_NAME_CONVERT='/u02/data/dg','/u02/data/dg'
*.LOG_FILE_NAME_CONVERT='/u02/data/dg','/u02/data/dg'

九修改tsname.ora 和listener.ora _standby

tsname.ora：

listener.ora

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /u01/oracle/db)
(PROGRAM = extproc)
)
(SID_DESC =
(GLOBAL_DBNAME = sta)
(ORACLE_HOME = /u01/oracle/db)
(SID_NAME = dg)
)
)

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = dg2.com)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
)

十配置primary NFS服务,standby 进行挂载 _standby

配置并且启动

/etc/exports

/u02/bak *(rw)

standby进行挂载操作

mount -o rw,bg,hard,nointr,rsize=32768,wsize=32768,tcp,vers=3,timeo=600,actimeo=0 dg1:/u02/bak /u02/bak/

十一 standby本地恢复 _standby

创建实例：

exoort ORACLE_SID=dg

复制创建的sandby controle file到参数文件定义的位置

scp /tmp/standby.ctl oracle@dg2:/u02/data/dg/

启动实例到nomount

startup nomount

使用复制数据库链接(这个应该在任意位置执行都一样,第一次面试DBA时面试官坚持是primary端，我坚持是standby端.....)

rman target sys/m123@pri auxiliary sys/m123@sta;

复制数据库

duplicate target database for standby

standby应用远程日志

alter database recover managed standby database disconnect from session;

十二 standby与primary创建 standbylog

alter database add standby logfile group 4 ('/u02/data/dg/stand07.log','/u02/data/dg/stand04_2.log') size 50M;

alter database add standby logfile group 5 ('/u02/data/dg/stand07.log','/u02/data/dg/stand05_2.log') size 50M;

alter database add standby logfile group 6 ('/u02/data/dg/stand07.log','/u02/data/dg/stand06_2.log') size 50M;