oracle 读写分离 DG,数据库读写分离解决方案--DG实施方案

Oracle Data Guard实施方案

(SID_DESC =

(GLOBAL_DBNAME = orcl) (SID_NAME = orcl)

(ORACLE_HOME = /home/db/oracle/product/11.2.0/db_1) ) )

注意：SID_LIST_LISTENER 配置的是静态注册，如果没有该参数，而且Data Guard 启动顺序又不正确，那么在主库可能会报 PING[ARC1]: Heartbeat failed to connect to standby 'orcl_st'. Error is 12514. 错误，导致归档无法完成。

[oracle@localhost admin]$ vi tnsnames.ora ORCL =

(DESCRIPTION = (ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.204.131)(PORT = 1521)) )

(CONNECT_DATA =

(SERVICE_NAME = orcl) ) )

ORCLDG =

(DESCRIPTION = (ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.204.132)(PORT = 1521)) )

(CONNECT_DATA =

(SERVICE_NAME = orcldg) ) )

? 备库

[oracle@localhost admin]$ vi listener.ora

LISTENER =

(DESCRIPTION_LIST = (DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.204.132)(PORT = 1521)) ) ) )

SID_LIST_LISTENER = (SID_LIST = (SID_DESC =

Oracle Data Guard实施方案

(GLOBAL_DBNAME = orcldg) (SID_NAME = orcl)

(ORACLE_HOME = /home/db/oracle/product/11.2.0/db_1) ) )

[oracle@localhost admin]$ vi tnsnames.ora ORCL =

(DESCRIPTION = (ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.204.131)(PORT = 1521)) )

(CONNECT_DATA =

(SERVICE_NAME = orcl) ) )

ORCLDG =

(DESCRIPTION = (ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.204.132)(PORT = 1521)) )

(CONNECT_DATA =

(SERVICE_NAME = orcldg) ) )

5.5 主库前期准备

设置强制写日志

SQL> select FORCE_LOGGING from v$database; NO

SQL> alter database force logging;

SQL> select FORCE_LOGGING from v$database; YES

Oracle Data Guard实施方案

5.6 创建口令文件并将文件传输到备库

在主库上创建密码文件，并将文件传到备库的相关位置 [oracle@localhost dbs]$ pwd

/home/db/oracle/product/11.2.0/db_1/dbs

[oracle@localhost bin]$ orapwd file=$ORACLE_HOME/dbs/orapworcl password=oracle [oracle@localhost bin]$ cd $ORACLE_HOME/dbs/ [oracle@localhost dbs]$ ls

缺省情况下，win下口令文件的格式是pwdsid.ora，unix下的格式是orapwSID(大小写敏感)

传送文件：

[oracle@localhost

dbs]$scp

-cp

orapworcl

oracle@192.168.204.132://home/db/oracle/product/11.2.0/db_1/dbs

orapworcl 100% 1536 1.5KB/s 00:00 [oracle@localhost dbs]$

5.7 创建备份库需要的控制文件并传输到备库

创建控制文件

SQL>shutdown immediate SQL>startup mount;

SQL>alter database create standby controlfile as '/tmp/stdby_control01.ctl'; SQL>alter database open;

$scp -rp /tmp/stdby_control01.ctl

oracle@192.168.204.132://home/db/oracle/oradata/ORCL/controlfile/

$scp -rp /tmp/stdby_control01.ctl

oracle@192.168.204.132://home/db/oracle/flash_recovery_area/ORCL/controlfile/

----------------------------------------------------------- 手工复制几份(未执行) $ cd /tmp/

$ cp sdtby_control01.ctl stdby_control02.ctl $ cp sdtby_control01.ctl stdby_control03.ctl

Oracle Data Guard实施方案

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

5.8 修改主库初始化参数

创建主库pfile

sql > create pfile from spfile; 修改pfile

[oracle@localhost dbs]$vi initorcl.ora

*.db_unique_name=orcl

*.log_archive_config='dg_config=(orcl,orcldg)'

*.log_archive_dest_1='location=/home/db/oracle/oradata/ORCL/archive valid_for=(all_logfiles,all_roles) db_unique_name=orcl' *.log_archive_dest_2='service=orcldg lgwr async

valid_for=(online_logfiles,primary_role) db_unique_name=orcldg' *.log_archive_dest_state_1=enable *.log_archive_dest_state_2=defer *.log_archive_format=%t_%s_%r.arc *.fal_server=orcldg *.fal_client=orcl

*.standby_file_management=auto

pfile 拷贝到备库上

[oracle@localhost dbs]$scp –rp

/home/db/oracle/product/11.2.0/db_1/dbs/initorcl.ora

oracle@192.168.204.132:/home/db/oracle/product/11.2.0/db_1/dbs/

创建主库spfile

SQL>Shutdown immediate

SQL>startup pfile='//home/db/oracle/product/11.2.0/db_1/dbs/initorcl.ora'

SQL> create spfile from pfile='//home/db/oracle/product/11.2.0/db_1/dbs/initorcl.ora'; SQL> shutdown immediate; SQL > startup

5.9 修改数据库运行在归档模式下

SQL > SHUTDOWN IMMEDIATE; SQL > STARTUP MOUNT;