Oracle11g_adg+fast failover+dg_broker

1.      概述

前些时间给公司布署了一套oracleadg系统，用作备份数据使用，为了更加方便的管理和维护adg系统，还需要熟悉oracle11g adg 中许多新的功能，以下为实验adg的相关功能，包括duplicate库，fastfailover，dg broker的相关功能。

2.     环境

db 版本  11.2.0.4.0

os 版本   rhel 6u2_x86_64

	db primary	db standby	备注
hostname	dba1.fengg.cn dba1	dba2.fengg.cn dba2
db_unique_name	beijing	shanghai
db name	fengg	fengg
instance_name	beijing	shanghai
ip	192.168.2.180/24	192.168.2.181/24
dgmgrl:	beijing_dgmgrl	shanghai_dgmgrl	配置dg broker
存储	disk	disk
归档	disk	disk

 

3. 操作步骤

3.1   adg环境中的时间必须同步一致，开启ntpd时间服务

[root@dba2~]#  service ntpd start

[root@dba1~]#  service ntpd start

 

3.2  修改主机名和hosts文件：

     [root@dba1~]# cat /etc/hosts

192.168.2.180   dba1.fengg.cn dba1

192.168.2.181   dba2.fengg.cn dba2

 

[root@dba2~]# cat /etc/hosts

192.168.2.180   dba1.fengg.cn dba1

192.168.2.181   dba2.fengg.cn dba2

3.3  主库上的操作：

[oracle@dba1~]$ cat .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

 

exportpath

exportoracle_base=/u01/oracle

exportoracle_home=$oracle_base/product/11.2.0.4

exportoracle_sid=beijing

exportnls_lang=american_america.zhs16gbk

exportld_library_path=$oracle_home/lib:/lib:/usr/lib

exportpath=$path:$home/bin:$oracle_home/bin

exportoracle_home_listner=$oracle_home

3.3.1 开启归档和闪回

sys@ora11g> shutdown  immediate;

sys@ora11g>startup mount;

sys@ora11g>alter database archivelog;

sys@ora11g>alterdatabase flashback on;

sys@ora11g> alterdatabase open;

sys@ora11g>archive log list

3.3.2启用强制归档

sys@ora11g> alterdatabase force logging;

sys@ora11g>select force_logging from v$database;

sys@ora11g>archive log list;

3.3.3修改sys用户口令

[oracle@dba1 dbs]$ pwd

/u01/oracle/product/11.2.0.4/dbs

[oracle@dba1 dbs]$orapwdfile=orapw$oracle_sid password=oracle entries=10 force=y ignorecase=y

3.3.4 添加standby redo log

sys@ora11g> alter database add standby logfile               

group 4('/u01/oracle/oradata/fengg/dglog04.log') size 500m,

group 5('/u01/oracle/oradata/fengg/dglog05.log') size 500m,

group 6('/u01/oracle/oradata/fengg/dglog06.log') size 500m,

group 7('/u01/oracle/oradata/fengg/dglog7.log') size 500m,

group 8('/u01/oracle/oradata/fengg/dglog8.log') size 500m,

group 9('/u01/oracle/oradata/fengg/dglog9.log') size 500m,

group 10('/u01/oracle/oradata/fengg/dglog10.log') size 500m;

3.3.5 修改数据库参数

alter system set log_archive_dest_1='location=/archivelog' scope=spfile;

alter system set log_archive_format='%t_%s_%r.dbf'scope=spfile;

alter system set db_unique_name='beijing'scope=spfile;

alter system set log_archive_config='dg_config=(beijing,shanghai)'scope=spfile;

alter system setlog_archive_dest_1='location=/archivelog valid_for=(all_logfiles,all_roles)db_unique_name=beijing' scope=spfile;

alter system setlog_archive_dest_2='service=shanghai async lgwrvalid_for=(online_logfiles,primary_role) db_unique_name=shanghai' scope=spfile;

alter system set log_archive_max_processes=30scope=spfile;

alter system set log_archive_dest_state_1=enable;

alter system set log_archive_dest_state_2=enable;

alter system set db_file_name_convert='shanghai','beijing'scope=spfile;

alter system set log_file_name_convert='shanghai','beijing'scope=spfile;

alter system set fal_server=shanghai scope=spfile;

alter system set fal_client=beijing scope=spfile;

alter system set standby_file_management=auto;

3.3.6 配置listener.ora

[oracle@dba1 admin]$ catlistener.ora

# listener.ora networkconfiguration file: /u01/oracle/product/11.2.0.4/network/admin/listener.ora

# generated by oracleconfiguration tools.

 

sid_list_listener =

  (sid_list =

    (sid_desc =

      (global_dbname =beijing)

      (oracle_home =/u01/oracle/product/11.2.0.4)

      (sid_name = beijing)

    )

    (sid_desc =

      (global_dbname = beijing_dgmgrl)

      (oracle_home =/u01/oracle/product/11.2.0.4)

      (sid_name = beijing)

    )

  )

 

listener =

  (description_list =

    (description =

      (address = (protocol = tcp)(host =192.168.2.180)(port = 1521))

    )

  )

adr_base_listener =/u01/oracle

 

3.3.7  配置tnsnames.ora服务别名

[oracle@dba1admin]$ cat tnsnames.ora

#tnsnames.ora network configuration file:/u01/oracle/product/11.2.0.4/network/admin/tnsnames.ora

#generated by oracle configuration tools.

 

beijing=

  (description =

    (address = (protocol = tcp)(host =192.168.2.180)(port = 1521))

    (connect_data =

      (server = dedicated)

      (service_name =beijing)

    )

  )

 

shanghai=

  (description =

    (address = (protocol = tcp)(host =192.168.2.181)(port = 1521))

    (connect_data =

      (server = dedicated)

      (service_name = shanghai)

    )

  )

3.3.8 测试连接标识符是否连通

[oracle@dba1admin]$ lsnrctl start

[oracle@dba1admin]$ tnsping beijing

 

 

3.3.9 验证登陆

注意：sqlnet.ora 加入”ezconnect”,否则下面命令2-3不能执行

names.directory_path= (tnsnames, ezconnect)

[oracle@dba1 ~]$ sqlplus  sys/oracle@beijing as sysdba;  

[oracle@dba1 ~]$ sqlplus  sys/oracle@dba1:1521/beijing as sysdba;

[oracle@dba1 admin]$ sqlplus  sys/oracle@dba1:1521/beijing_dgmgrl assysdba;

 

3.4  备库上的操作

3.4.1 安装oracle，但是不创建库

[oracle@dba2~]$ cat .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

 

exportpath

exportoracle_base=/u01/oracle

exportoracle_home=$oracle_base/product/11.2.0.4

exportoracle_sid=shanghai

exportnls_lang=american_america.zhs16gbk

exportld_library_path=$oracle_home/lib:/lib:/usr/lib

exportpath=$path:$home/bin:$oracle_home/bin

exportoracle_home_listner=$oracle_home

3.4.2 创建audit目录

[oracle@dba2 ~]$  mkdir -p /u01/oracle/admin/fengg/adump

3.4.3 增加sys口令文件

[oracle@dba2 dbs]$ pwd

/u01/oracle/product/11.2.0.4/dbs

[oracle@dba2 dbs]$orapwd file=orapw$oracle_sidpassword=oracle entries=10 force=y ignorecase=y

3.4.4 配置监听

[oracle@dba2admin]$ cat listener.ora

#listener.ora network configuration file:/u01/oracle/product/11.2.0.4/network/admin/listener.ora

#generated by oracle configuration tools.

 

sid_list_listener=

  (sid_list =

    (sid_desc =

      (global_dbname = beijing)

      (oracle_home = /u01/oracle/product/11.2.0.4)

      (sid_name = shanghai)

    )

    (sid_desc =

      (global_dbname = beijing_dgmgrl)

      (oracle_home =/u01/oracle/product/11.2.0.4)

      (sid_name = shanghai)

    )

  )

 

adr_base_listener= /u01/oracle

 

listener=

  (description =

    (address = (protocol = tcp)(host =dba2.fengg.cn)(port = 1521))

  )

 

3.4.5 配置别名

[oracle@dba2admin]$ cat tnsnames.ora

#tnsnames.ora network configuration file:/u01/oracle/product/11.2.0.4/network/admin/tnsnames.ora

#generated by oracle configuration tools.

 

beijing=

  (description =

    (address_list =

      (address = (protocol = tcp)(host =192.168.2.180)(port = 1521))

    )

    (connect_data =

      (server = dedicated)

      (service_name = beijing)

    )

  )

 

shanghai=

  (description =

    (address_list =

      (address = (protocol = tcp)(host =192.168.2.181)(port = 1521))

    )

    (connect_data =

      (server = dedicated)

      (service_name = shanghai)

    )

  )

3.4.6 重启监听

[oracle@dba2admin]$ lsnrctl stop listener

[oracle@dba2admin]$ lsnrctl start listener

3.4.7 验证登陆

[oracle@dba2admin]$ sqlplus sys/oracle@dba2:1521/shanghai as sysdba;

[oracle@dba2admin]$ sqlplus sys/oracle@dba2:1521/shanghai_dgmgrl as sysdba;

[oracle@dba2admin]$ sqlplus sys/oracle@dba1:1521/beijing_dgmgrl as sysdba;

[oracle@dba2admin]$ sqlplus sys/oracle@dba1:1521/beijing as sysdba;

3.4.8 配置参数文件

[oracle@dba2dbs]$ echo "db_name=fengg" >> initshanghai.ora

3.4.9启动到nomount状态

sql>  startup nomount

3.5  duplicate standby，在dba1上执行

3.5.1 查看执行duplicate脚本

[oracle@dba1~]$ cat duplicate_fengg.sql

duplicatetarget database

forstandby nofilenamecheck

fromactive database

dorecover                                                                  

spfile

setdb_unique_name='shanghai'

setlog_archive_dest_1='location=/archivelog valid_for=(all_logfiles,all_roles)db_unique_name=shanghai'

setlog_archive_dest_2='service=beijing async lgwrvalid_for=(online_logfiles,primary_role) db_unique_name=beijing'

setstandby_file_management='auto'

setfal_server='beijing'

setfal_client='shanghai'

setcontrol_files='/u01/oracle/oradata/fengg/control01.ctl','/u01/oracle/fast_recovery_area/fengg/control02.ctl'

setmemory_target='1000m'

setsga_max_size='1000m';

 

 

3.5.2 执行duplicate脚本

[oracle@dba1~]$ rman target / auxiliary sys/oracle@shanghai cmdfile=duplicate_fengg.sql

 

4.  配置 dg_broker

4.1  2边实例均 设置dg_broker_start=true;

primary:

altersystem set dg_broker_start=true;

 

standby:

altersystem set dg_broker_start=true;

 

[oracle@dba1~]$ dgmgrl sys/oracle@beijing

createconfiguration fengg as primary database is beijing connect identifier is beijing;

 

adddatabase shanghai as connect identifier is shanghai maintained as physical;

 

enableconfiguration;

 

showconfiguration;

 

switchoverto shanghai;

 

switchover to beijing;

 

showdatabase verbose beijing

 

5. 测试adg

primary:

sql>create table test as select * from scott.emp;

sql>select count(*) from test;

  count(*)

----------

        14

standby:

alterdatabase open;

sql>selectcount(*) from test;

count(*)

----------

14

6. convert standby to snapshot

[oracle@dba1~]$ dgmgrl sys/oracle@beijing

 

dgmgrl>convert database shanghai  to snapshotstandby;

 

sql>insertinto test select * from scott.emp;

sql>commit;

 

dgmgrl>convert database macdbs to physical standby;

dgmgrl>showconfiguration;

 

7. 启动fast_startfailover

standby:

sql>shutdownimmediate;

sql>startupmount;

sql>alterdatabase flashback on;

 

dgmgrl>enable fast_start failover

dgmgrl>enablefast_start failover condition "stuck archiver";

 

dgmgrl>start observer

observerstarted

dgmgrl>stop observer;

done.

showfast_start failover;

succeeded.

showfast_start failover;

dgmgrlsys/oracle@macdbn

show configuration            

 

8. 配置archivelogdelete policy

sql>alter system set "_log_deletion_policy"=all;

 

[oracle@dba1~]$ rman target /

 

rman>configure archivelog deletion policy to applied on standby;

configurearchivelog deletion policy to shipped to standby;

rman>list archivelog all;

rman>show all;

rman>delete archivelog all;

rman>configure archivelog deletion policy clear;

rman>backup archivelog all;

注：参考来自互联网，如侵犯你的版权请及时联系我，谢谢！

=======================================================================

版权所有，文章允许转载，但必须以链接方式注明源地址，否则追究法律责任! 谢谢合作！

QQ:164798858@qq.com
Sina:Weibo.com/kaijunfeng
Gmail:KaijunFeng@gmail.com