Oracle 10G R2的Data Guard物理standby搭建_oracle 10g r2 dataguard 部署-优快云博客

本文详细介绍了在RHEL5.4操作系统下，如何通过虚拟机搭建Oracle 10gR2双活数据库环境，包括环境配置、参数规划、关键操作步骤及验证过程。

搭建环境：
OS :RHEL 5.4 Oracle版本：10g R2 ORACLE_HOME=/u01/app/oracle
虚拟机1 hostname :dg1 IP:192.168.0.103
虚拟机2 hostname :dg2 IP:192.168.0.104

参数规划
数据库 db_name sid instance_name service_names db_unique_name fal_server fal_client
主库 dg1 dg1 dg1 dg1 dg1 dg2 dg1
备库 dg1 dg1 dg1 dg2 dg2 dg1 dg2
注意：搭建10g DG时数据库版本必须一致，包括小版本，且操作系统平台之间必须满足一定要求具体可以参见
http://czmmiao.iteye.com/blog/1447643
主库上的操作
将主库至于foce logging状态
[oracle@dg1 ~]$ sqlplus / as sysdba;
SQL*Plus: Release 10.2.0.1.0 - Production on Tue Jan 25 19:56:11 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to an idle instance.

SQL> startup mount;
ORACLE instance started.
Total System Global Area 373293056 bytes
Fixed Size 1219496 bytes
Variable Size 138413144 bytes
Database Buffers 230686720 bytes
Redo Buffers 2973696 bytes
Database mounted.
SQL> alter database force logging;
Database altered.
关闭force logging模式的语法如下
alter database no force logging ;
执行force logging的原因：有一些DDL语句可以通过指定NOLOGGING子句的方式避免写REDO（目的是提高速度，某些时候确实有效）。指定数据库为Force Logging模式后，数据库将会记录除临时表空间或临时回滚段外所有的操作，而忽略类似NOLOGGING之类的指定参数。如果在执行Force Logging时有NOLOGGING之类的语句在执行，那么Force Logging会等待，直到这类语句全部执行。
Force Logging是作为固定参数保存在控制文件中，因此其不受重启之类操作的影响（只执行一次即可），如果想取消，可以通过ALTER DATABASE NO FORCE LOGGING语句关闭强制记录。
注意：force logging并不比一般的logging 记录的日志多,数据库在force logging状态下,nologging选项将无效,因为nologging将破坏dataguard的可恢复性.force logging
强制数据库在任何状态下必须记录日志而已。
并处于归档模式
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/app/oradata/archive
Oldest online log sequence 40
Next log sequence to archive 42
Current log sequence 42
查看需要拷贝的数据文件
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/czmmiao/system01.dbf
/u01/app/oracle/oradata/czmmiao/undotbs01.dbf
/u01/app/oracle/oradata/czmmiao/sysaux01.dbf
/u01/app/oracle/oradata/czmmiao/users01.dbf
/u01/app/oracle/oradata/czmmiao/example.dbf
/u01/app/oracle/oradata/czmmiao/nk_tbs.dbf
6 rows selected.
查看需要拷贝的临时文件
SQL> select name from v$tempfile;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/czmmiao/temp01.dbf
/u01/app/oracle/oradata/czmmiao/temp.dbf
2 rows selected.
利用主库的控制文件创建备库的控制文件
SQL> alter database create standby controlfile as '/u01/app/oracle/dbs/standby.ctl';
Database altered.
利用主库的spfile创建pfile，主要是为了便于参数的修改
SQL> create pfile from spfile;
File created.
关闭主库
SQL> shutdown immediate;
Database dismounted.
ORACLE instance shut down.
主库参数文件配置
在主库的pfile增加如下几行，其中红色的需要特别注意
*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(dg1,dg2)'
*.log_archive_dest_1='LOCATION=/u01/app/oradata/archive VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=dg1 ' #这个参数的意义为：在数据库dg1上log_archive_dest_1对主备库上的联机日志都有效，这里的 db_unique_name可以省略
*.log_archive_dest_2='SERVICE=dg2 LGWR ASYNC=40960 VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=dg2 '
*.log_archive_dest_state_1='enable'
*.log_archive_dest_state_2='enable'
*.log_archive_format='%t_%s_%r.arc'
*.log_archive_max_processes=2
*.log_archive_start=true
*.service_names='dg1 '
*.db_name='dg1 '
*.db_unique_name='dg1 '
*.fal_client='dg1 '
*.fal_server='dg2 '
注意：这里的fal_client、fal_server参数是针对主库转换为备库之后而言的。
将所需文件拷贝到dg2上
[oracle@dg1 dbs]$ more /etc/hosts
# Do not remove the following line, or various programs
# that require network functionality will fail.
127.0.0.1 dg1 localhost.localdomain localhost
192.168.0.104 dg2
192.168.0.103 dg1
拷贝数据文件和日志文件
[oracle@dg1 oradata]$ pwd
/u01/app/oradata
[oracle@dg1 oradata]$ ls
archive dg1
这里采用关闭主库进行数据文件拷贝的方式，当然也可以采用rman备份的方式进行拷贝，采用rman的方式可以不用关闭主库。
[oracle@dg1 oradata]$ scp -r dg1/ dg2:/u01/app/oradata/
拷贝admin下的文件，主要是为了拷贝这个目录的目录结构
[oracle@dg1 app]$ pwd
/u01/app
[oracle@dg1 app]$ ls
admin flash_recovery_area oracle oradata oraInventory
[oracle@dg1 app]$ scp -r admin/ dg2:/u01/app/
在主库上创建口令文件
[oracle@dg1 app]$ orapwd file=/u01/app/oracle/dbs/orapwdg1 password=oracle entries=5
拷贝口令文件，参数文件到备库上
[oracle@dg1 oracle]$ scp orapwdg1 initdg1 dg2:/u01/app/oracle/dbs
登陆到备机，重命名口令文件
[oracle@dg2 oracle]$ mv orapwdg1 orapwdg2
下面为采用rman备份创建备库的简要步骤如下，供读者参考
主库操作
RMAN>backup database format '/home/oracle/full_%U.dbf';
$scp 备份文件 dg2:/home/oracle/
创建standby controlfile，一定要在备份后创建
SQL>alter database create standby controlfile as '/home/oracle/st.ctl';
$scp st.ctl dg2:/home/oracle/
备库操作
RMAN>restore database
RMAN>recover database
即可
配置主库的listener.ora和tnsname.ora
listener.ora
[oracle@dg1 admin]$ pwd
/u01/app/oracle/network/admin
[oracle@dg1 admin]$ cat listener.ora
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = dg1 )
(ORACLE_HOME = /u01/app/oracle )
# (PROGRAM = extproc)
(GLOBAL_DBNAME=dg1 )
)
)

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
(ADDRESS = (PROTOCOL = TCP)(HOST = dg1 )(PORT = 1521))
)
)
tnsname.ora
DG1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.103 )(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = dg1 )
(INSTANCE_NAME = dg1 )
)
)

DG2 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.104 )(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SID = dg1 )
)
)

EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)
重启监听程序
[oracle@dg1 admin]$ lsnrctl stop
[oracle@dg1 admin]$ lsnrctl start
利用pfile创建spfile
[oracle@dg1 ~]$ sqlplus / as sysdba;
SQL*Plus: Release 10.2.0.1.0 - Production on Tue Jan 25 19:56:11 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to an idle instance.
SQL> create spfile from pfile;
File created.
至此在主库上的操作告一段落
备库操作
备库参数文件
*.control_files='/u01/app/oradata/dg1/standby.ctl' ＃＃确保控制文件置于该目录下
*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(dg1,dg2)'
*.log_archive_dest_1='LOCATION=/u01/app/oradata/archive VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=dg2 ' ＃确保 /u01/app/oradata/archive该路径存在
*.log_archive_dest_2='SERVICE=dg2 LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=dg1 ' #同上
*.log_archive_dest_state_1='enable'
*.log_archive_dest_state_2='enable'
*.log_archive_format='%t_%s_%r.arc'
*.log_archive_max_processes=2
*.log_archive_start=true
*.service_names='dg2 '
*.db_name='dg1 '
*.db_unique_name='dg2 '
*.fal_client='dg2 '
*.fal_server='dg1 '
配置备库上的listener.ora和tnsname.ora
lstener.ora
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = dg2 )
(GLOBAL_DBNAME= dg2 )
(ORACLE_HOME = /u01/app/oracle )
)
)

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.104 )(PORT = 1521))
)
)
tnsname.ora和主库上的一模一样，建议直接从主库上拷贝避免出错
重启监听程序
[oracle@dg1 admin]$ lsnrctl stop
[oracle@dg1 admin]$ lsnrctl start
至此备库上的操作完成
测试 DG
依次打开主库和备库
主库:
SQL> STARTUP MOUNT;
SQL> ALTER DATABASE OPEN;
备库:
SQL> STARTUP MOUNT;
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT
FROM SESSION;
在主库上
SQL>archive log list;
SQL>alter system archive log current;
SQL>archive log list;
主库进行日志切换:
SQL>Alter system switch logfile;
然后分别查看主库和备库的目录下是否产生了同样的归档日志文件。
执行如下SQL
select max(sequence#) from v$archived_log;
select max(sequence#) from v$log_history;
select group#,sequence#,archived,status from v$log;
select name,sequence#,applied from v$archived_log;
select sequence#,applied from v$archived_log;
亦可在主库进行一些DML操作然后在备库上进行验证，具体操作如下
主库
SQL> select * from dg;
ID NAME
---------- ---------------------------------------------------------------------
1 czm
SQL> insert into dg values(2,'czm');
1 row created.
SQL> commit;
Commit complete.
SQL> select * from dg;
ID NAME
---------- ---------------------------------------------------------------------
1 czm
2 czm
SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
SQL> /
System altered.
备库上以read only方式打开
SQL> alter database recover managed standby database cancel;
SQL> alter database read only;
SQL> select * from dg;
ID NAME
---------- ---------------------------------------------------------------------
1 czm
2 czm
至此DG顺利搭建成功