oracle 11g DG 搭建

原创作品，允许转载，转载时请务必以超链接形式标明文章 原始出处 、作者信息和本声明。否则将追究法律责任。http://yangcheng.blog.51cto.com/1388193/1297625
搭建环境: centos 5.6 oracle 11g r2

primary： 192.168.8.132 str1.example.com str1 主库sid primary
standby: 192.168.8.131 str2.example.com str2 备库sid standby

oracle 11g 软件安装 http://yangcheng.blog.51cto.com/1388193/1209421
primary配置
1.配置静默安装文件
[root@str1 ~]# vi /u01/response/d.rsp
[GENERAL]
RESPONSEFILE_VERSION = "11.2.0"
OPERATION_TYPE = "createDatabase"
[CREATEDATABASE]
GDBNAME = "primary"
SID = "primary"
TEMPLATENAME = "General_Purpose.dbc"
[createTemplateFromDB]
SOURCEDB = "myhost:1521:primary"
SYSDBAUSERNAME = "system"
TEMPLATENAME = "My Copy TEMPLATE"
[createCloneTemplate]
SOURCEDB = "primary"
TEMPLATENAME = "My Clone TEMPLATE"
[DELETEDATABASE]
SOURCEDB = "primary"
[generateScripts]
TEMPLATENAME = "New Database"
GDBNAME = "primary"
[CONFIGUREDATABASE]
[ADDINSTANCE]
DB_UNIQUE_NAME = "primary"
NODELIST=
SYSDBAUSERNAME = "sys"
[DELETEINSTANCE]
DB_UNIQUE_NAME = "primary"
INSTANCENAME = "primary"
SYSDBAUSERNAME = "sys"

2.设置oracle 环境变量
[root@str1 ~]# vi /home/oracle/.bash_profile
# .bash_profile

# Get the aliases and functions
if [ -f ~/.bashrc ]; then
. ~/.bashrc
fi

# User specific environment and startup programs

PATH=$PATH:$HOME/bin

export PATH
umask 022
export ORACLE_BASE=/u01/app
export ORACLE_HOME=$ORACLE_BASE/oracle/product/11.2.0/dbhome_1
export ORACLE_SID=primary
export PATH=$PATH:HOME/bin:$ORACLE_HOME/bin

3.安装primary 实列
$ORACLE_HOME/bin/dbca -silent -responsefile /u01/response/d.rsp
一、打开数据库修改数据库属性
[oracle@str1 ~]$ sqlplus / as sysdba;

1）修改主库属性
SQL> startup mount
SQL> alter database force logging;
2）查看状态
SQL> select FORCE_LOGGING from v$database;
3）修改数据库为归档模式

SQL> alter system set log_archive_dest_1='LOCATION=/u01/app/oradata/primary/' scope=both;
SQL> alter database archivelog;

4）添加 standby logfile (可不加)
备用日志最少应该比 redo log 多一个
select GROUP#,MEMBERS,BYTES/1024/1024 from v$log;
select GROUP#,MEMBER from v$logfile;

增加 standby logfile
alter database add standby logfile
group 4 ('/u01/app/oradata/primary/redo04.log') size 50m,
group 5 ('/u01/app/oradata/primary/redo05.log') size 50m,
group 6 ('/u01/app/oradata/primary/redo06.log') size 50m,
group 7 ('/u01/app/oradata/primary/redo07.log') size 50m;
---如果要删除 standby logfile
---　删除也同样简单： SQL> alter database drop standby logfile group 4;
5）创建pfile
SQL> create pfile='/u01/app/backup/initprimary.ora' from spfile;
二、修改initprimary.ora
增加内容
vi /u01/app/backup/initprimary.ora
DB_UNIQUE_NAME='primary'
log_archive_config='DG_CONFIG=(primary,standby)'
log_archive_dest_1='LOCATION=/u01/app/oradata/primary/archivelog VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=primary'
LOG_ARCHIVE_DEST_2='SERVICE=standby LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=standby'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
FAL_SERVER=standby
FAL_CLIENT=primary
STANDBY_FILE_MANAGEMENT=AUTO
[root@str1 ~]# vi /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

primary =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.8.132)(PORT = 1521))
(CONNECT_DATA =
(SERVICE_NAME = primary)
)
)

standby =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.8.131)(PORT = 1521))
(CONNECT_DATA =
(SERVICE_NAME = standby)
)
)
EXTPROC_CONNECTION_DATA=
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL =TPC)(KEY = EXTPROCO))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)

三、修改监听
[root@str1 ~]#vi /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_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/11.2.0/dbhome_1)
(PROGRAM = extproc)
)
(SID_DESC =
(GLOBAL_DBNAME= primary)
(ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1)
(SID_NAME=primary)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = str1)(PORT = 1521))
)
)
四、用pfile启动再重新创建spfile
startup pfile='/u01/app/backup/initprimary.ora'
create spfile from pfile='/u01/app/backup/initprimary.ora';

五、创建口令文件
orapwd file='/u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwprimary' password=letoula entries=10
六、备份控制文件
alter database create standby controlfile as '/u01/app/backup/controlstb.ctl';

七、传输 pfile ， 口令文件， 控制文件到 standby
[root@str1 ~]# scp /u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwprimary oracle@192.168.8.131:/u01/app/backup
[root@str1 ~]# scp /u01/app/backup/controlstb.ctl oracle@192.168.8.131:/u01/app/backup
[root@str1 ~]# scp /u01/app/backup/initprimary.ora oracle@192.168.8.131:/u01/app/backup
八、传输数据文件
[root@str1 ~]# scp /u01/app/oradata/primary/* root@192.168.8.131:/u01/app/oradata/primary/ --standby创建primary目录
主数据库暂时配置完毕
--------------------------------------------------------------------
--------------------------------------------------------------------
standby 配置
oracle用户登录到standby服务器
1、修改oracle环境变量
[root@str2 primary]# vi /home/oracle/.bash_profile
# .bash_profile
# Get the aliases and functions
if [ -f ~/.bashrc ]; then
. ~/.bashrc
fi
# User specific environment and startup programs
PATH=$PATH:$HOME/bin
export PATH
umask 022
export ORACLE_BASE=/u01/app
export ORACLE_HOME=$ORACLE_BASE/oracle/product/11.2.0/dbhome_1
export ORACLE_SID=standby
export PATH=$PATH:HOME/bin:$ORACLE_HOME/bin

2、将控制文件复制到对应的目录：cp /u01/app/backup/controlstb.ctl /u01/app/oradata/primary/control01.ctl
3、将密码文件复制到对应的目录：cp /u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwprimary oracle@192.168.8.131:/u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwprimary
4、备用服务器相应目录创建
mkdir -p $ORACLE_BASE/flash_recovery_area/primary
mkdir -p $ORACLE_BASE/oradata/primary
cd $ORACLE_BASE/admin/primary
[oracle@str2 standby]$ mkdir adump
m[oracle@str2 standby]$ mkdir bdump
[oracle@str2 standby]$ mkdir cdump
[oracle@str2 standby]$ mkdir dpdump
[oracle@str2 standby]$ mkdir pfile
[oracle@str2 standby]$ mkdir udump

5、修改初始化参数
[root@str2 primary]# vi /u01/app/backup/initstandby.ora
standby.__db_cache_size=197132288
standby.__java_pool_size=4194304
standby.__large_pool_size=4194304
standby.__oracle_base='/u01/app'#ORACLE_BASE set from environment
standby.__pga_aggregate_target=104857600
standby.__sga_target=314572800
standby.__shared_io_pool_size=0
standby.__shared_pool_size=100663296
standby.__streams_pool_size=0
*.audit_file_dest='/u01/app/admin/primary/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/u01/app/oradata/primary/control01.ctl','/u01/app/flash_recovery_area/primary/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='primary'
*.db_recovery_file_dest='/u01/app/flash_recovery_area'
*.db_recovery_file_dest_size=4070572032
*.diagnostic_dest='/u01/app'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=standbyXDB)'
*.open_cursors=300
*.pga_aggregate_target=104857600
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=314572800
*.undo_tablespace='UNDOTBS1'
DB_UNIQUE_NAME='standby'
log_archive_config='DG_CONFIG=(primary,standby)'
log_archive_dest_1='LOCATION=/u01/app/oradata/primary/archivelog VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=standby'
LOG_ARCHIVE_DEST_2='SERVICE=primary LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=primary'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
FAL_SERVER=primary
FAL_CLIENT=standby
STANDBY_FILE_MANAGEMENT=AUTO

6、修改备用服务器tns.ora listen.ora信息
[root@str2 primary]# vi /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

primary =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.8.132)(PORT = 1521))
(CONNECT_DATA =
(SERVICE_NAME = primary)
)
)

standby =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.8.131)(PORT = 1521))
(CONNECT_DATA =
(SERVICE_NAME = standby)
)
)

EXTPROC_CONNECTION_DATA=
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL =TPC)(KEY = EXTPROCO))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)

7、重启监听 lsnrctl stop
lsnrctl start

8、测试监听是否正常(也是判别密码文件是否正确传到standby服务器的好东东，避免权限问题))
在主库上面使用 sqlplus sys/oracle@standby as sysdba
sqlplus sys/oracle@primary as sysdba

9、用创建的备份服务器参数启动数据库到nomount
startup pfile='/u01/app/backup/initstandby.ora' nomount
alter database mount standby database;

创建spfile文件
create spfile from pfile='/u01/app/backup/initstandby.ora';

修改数据库处于应用归档状态
alter database recover managed standby database disconnect from session;

通过下列语句暂停redo应用。
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

只读模式打开数据库
alter database open read only;

配置完成
----------------------------------------------------------
----------------------------------------------------------
判断配置是否成功，主要通过查看主数据库归档日志的sequence是否一致 查看方法
SQL> select max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
--------------
55
通过在主库执行alter system switch logfile; 切换日志，可以观察到备库会自动应用通过主库传过来的日志
SQL> alter system switch logfile;
查询数据库状态 SQL> select status from v$instance;
查询归档日志是否应用（应用需要点时间）SQL> select sequence#,applied from v$archived_log where applied='YES' order by sequence#;
查看主库DG状态：
SQL> select switchover_status from v$database;
SWITCHOVER_STATUS
--------------------
TO STANDBY
日志测试：
在primary库上执行
SQL> conn scott/tiger
Connected.

SQL> create table y(y int);

Table created.

SQL> insert into y values(1);

1 row created.

SQL> commit;

Commit complete.

SQL> conn / as sysdba;
Connected.
SQL> alter system switch logfile;

System altered.

在standby 库上执行
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
只读模式打开数据库
alter database open read only;
SQL> conn scott/tiger
Connected.
SQL> select * from y;

Y
----------
1