ORACLE DataGuard 物理备库搭建

最新推荐文章于 2024-09-15 08:03:51 发布

cko82679

最新推荐文章于 2024-09-15 08:03:51 发布

阅读量143

点赞数

文章标签：数据库网络

试验环境：

Hyper-v 6.0.7601

RHEL 5.3

ORACLE 10gr2

VNC 4.1.3 (用于图像化界面)

主库：

ORACLE_SID: dgtest

DB_UNIQUE_NAME: dgtest_pd

IP: 11.11.0.101

HOSTNAME: dataguard1

备库：

ORACLE_SID: dgtest

DB_UNIQUE_NAME: dgtest_st

IP: 11.11.0.102

HOSTNAME: dataguard2

试验步骤：

构建基础环境：

1. Hyper-v 上安装一台RHEL5.3 虚拟系统；

2. 新建虚拟网络"HOSTONLY" (Hyper-v不用设置子网，就选择内部模式，并将IP设置在同一网段)

3. 配置网络并设置HOSTS文件；

hosts文件修改后内容：

127.0.0.1 localhost.localdomain localhost

11.11.0.101 dataguard1

11.11.0.102 dataguard2

4. 安装VNCServer服务；(步骤： http://space.itpub.net/24751738/viewspace-709094)

5. 安装ORACLE 10gr2软件，不建数据库；(步骤： http://space.itpub.net/24751738/viewspace-707164)

注意：配置文件中的ORACLE_HOME变量设置正确。

6. 使用导出，导入的方法克隆一台RHEL5.3虚拟系统；

主库服务器操作：

1. 使用netca, 创建监听器；

2. 使用DBCA, 创建数据库；

注意：

a. 创建过程中碰到的global name和SID均使用oracle_sid;

b. 创建是开启ARCHIVE; (检查：archive log list)

3. 开启强制日志模式：

su - oracle

sqlplus / as sysdba

alter database force logging;

4. 创建备库在线日志：（大小应该与主库的日志一样）

alter database add standby logfile group 4 ('/u01/app/oracle/oradata/redo04.log') size 50M;

alter database add standby logfile group 5 ('/u01/app/oracle/oradata/redo05.log') size 50M;

alter database add standby logfile group 6 ('/u01/app/oracle/oradata/redo06.log') size 50M;

alter database add standby logfile group 7 ('/u01/app/oracle/oradata/redo07.log') size 50M;

查看备库在线日志是否创建成功：

select group#, thread#, archived, status from v$standby_log;

5. 修改参数文件；

su - oracle

sqlplus / as sysdba

create pfile from spfile;

cd $ORACLE_HOME/dbs

vi initdgtest.ora

添加如下内容：

*.DB_UNIQUE_NAME='dgtest_pd'
*.log_archive_dest_1='LOCATION=/u01/app/oracle/flash_recovery_area/DGTEST/archivelog/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=dgtest_pd'
*.log_archive_dest_2='SERVICE=dgtest_st'
*.log_archive_dest_state_1=ENABLE
*.log_archive_dest_state_2=ENABLE
*.standby_file_management='AUTO'
*.standby_archive_dest='/u01/app/oracle/flash_recovery_area/DGTEST/archivelog'
*.FAL_SERVER='dgtest_pd'
*.FAL_CLIENT='dgtest_st'
*.LOG_ARCHIVE_MAX_PROCESSES=30

6. 使用新建的pfile启动数据库，并重建数据库

删除$ORACLE_HOME/dbs/spfiledgtest.ora

su - oracle

sqlplus / as sysdba

startup

create spfile from pfile

7. 创建备库控制文件：

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

8. 修改listener.ora, tnsnames.ora

listener.ora修改后的结果：

# listener.ora Network Configuration File: /u01/app/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 = /u01/app/oracle/product/10.2.0/db_1)
      (PROGRAM = extproc)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = dgtest)
      (ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1)
    (SID_NAME = dgtest)
    )
)

LISTENER =
(DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = dataguard1)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
    )
)

tnsnames.ora修改后的结果：

# tnsnames.ora Network Configuration File: /u01/app/oracle/product/10.2.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

DGTEST_PD =
(DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = dataguard1)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = dgtest)
    )
)

DGTEST_ST =
(DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = dataguard2)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = dgtest)
    )
)

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

9. 关闭主库

su - oracle

sqlplus / as sysdba

shutdown immediate;

备库服务器操作：

1. 新建文件夹：

su - oracle

cd $ORACLE_BASE

mkdir -p oradata/dgtest

mkdir -p admin/dgtest/adump

mkdir -p admin/dgtest/bdump

mkdir -p admin/dgtest/cdump

mkdir -p admin/dgtest/dpdump

mkdir -p admin/dgtest/pfile

mkdir -p admin/dgtest/udump

mkdir -p flash_recovery_area/DGTEST/archivelog

mkdir -p flash_recovery_area/DGTEST/onlinelog

2. 从主库服务器复制文件至备库服务器：

a. 控制文件：

scp /u01/standby.ctl dataguard2:/u01/app/oracle/oradata/dgtest/control1.ctl;

在备库服务器上：

cp control01.ctl control02.ctl;

cp control02.ctl control03.ctl;

b. 数据文件：

scp /u01/app/oracle/oradata/dgtest/*.dbf dataguard2:/u01/app/oracle/oradata/dgtest/*.dbf;

c. 在线日志文件：

scp /u01/app/oracle/oradata/dgtest/*.log dataguard2:/u01/app/oracle/oradata/dgtest/*.log;

d. 密码文件：

cd $ORACLE_HOME/dbs

scp orapwdgtest dataguard2:/u01/app/oracle/product/10.2.0/db_1/dbs

e. 参数文件：

scp initdgtest.ora dataguard2:/u01/app/oracle/product/10.2.0/db_1/dbs

3. 修改参数文件：

*.DB_UNIQUE_NAME='dgtest_st'
*.log_archive_dest_1='location=/u01/app/oracle/flash_recovery_area/DGTEST/archivelog VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=dgtest_st'
*.log_archive_dest_2='SERVICE=dgtest_pd'
*.log_archive_dest_state_1=ENABLE
*.log_archive_dest_state_2=ENABLE
*.standby_file_management='AUTO'
*.standby_archive_dest='/u01/app/oracle/flash_recovery_area/DGTEST/archivelog'
*.FAL_SERVER='dgtest_pd'
*.FAL_CLIENT='dgtest_st'

4. 从主库复制并修改listener.ora和tnsnames.ora

listener.ora修改后：

# listener.ora Network Configuration File: /u01/app/oracle/product/10.2.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

LISTENER =
(DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = dataguard2)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
    )
)

tnsnames.ora修改后内容：

# tnsnames.ora Network Configuration File: /u01/app/oracle/product/10.2.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

DGTEST_PD =
(DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = dataguard1)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = dgtest)
    )
)

DGTEST_ST =
(DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = dataguard2)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = dgtest)
    )
)

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

Dataguard启动：

1. 启动备库

su - oracle

sqlplus / as sysdba

startup mout;

2. 启动备库日志实施进程：

alter database recover managed standby database disconnect from session;

3. 启动备库监听器：

lsnrctl start

4. 启动主库：

su - oracle

sqlplus / as sysdba

startup

5. 启动主库监听器：

lsnrctl start

检查：

1. 检查日志是否成功更新：

a. 查看备库日志状态：

select sequence#, first_time, next_time from v$archived_log;

b. 在主库上执行：

alter system switch logfile;

c. 检查备库上检查日志状态，是否与之前有变化：

select sequence#, first_time, next_time from v$archived_log;

d. 检查备库上日志是否实施：

select sequence#, applied from v$archived_log;

2. 数据检查：

a. 新建表空间：

在主库服务器：
sqlplus / as sysdba
create tablespace ts_test datafile '/u01/app/oracle/oradata/dgtest/ts_test.dbf' size 100M autoextend on next 10M;
alter system archive log current;

查看结果：

select * from v$dbfile;

在备库服务器检查是否同样新建了表空间：

select * from v$dbfile;

b. 新建用户，表：

sqlplus / as sysdba;

create user test identified by test default tablespace ts_test;

grant dba to u_test;

create table test.t_test as select * from dba_tables;

select count(*) from test.t_test;

查看结果：

select count(*) from test.t_test;