#主备库 C:\Windows\System32\drivers\etc\hosts 文件

192.168.11.10  dg1

192.168.11.11  dg2

#环境

主库主机名：dg1 现有实例orcl

备库主机名：dg2 只安装软件

#主库如果已开启归档，不需要停机

sqlplus / as sysdba

select force_logging from v$database;

select flashback_on from v$database;

alter database force logging; -- 开启强制日志模式

#######################################################

#如果没开归档

sqlplus / as sysdba

shudown immediate;

startup mount;

alter database archivelog; -- 开启归档模式

alter database force logging; -- 开启强制日志模式

#alter database flashback on;  -- 开启闪回，不是必须，推荐开启

#######################################################

#主库添加standby日志组

#查看日志文件大小 select bytes/1024/1024 from v$log;这里是50M

alter database add standby logfile group 10 ('D:\app\Administrator\oradata\orcl\standby_redo01.log') size 50m;

alter database add standby logfile group 11 ('D:\app\Administrator\oradata\orcl\standby_redo02.log') size 50m;

alter database add standby logfile group 12 ('D:\app\Administrator\oradata\orcl\standby_redo03.log') size 50m;

alter database add standby logfile group 13 ('D:\app\Administrator\oradata\orcl\standby_redo04.log') size 50m;

########################################################

#设置文件管理自动

alter system set standby_file_management=auto;

#主库listener.ora

SID_LIST_LISTENER =

  (SID_LIST =

    (SID_DESC =

      (GLOBAL_DBNAME = orcl)

      (ORACLE_HOME = D:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1)

      (SID_NAME = orcl)

    )

    (SID_DESC =

      (GLOBAL_DBNAME = orcl_DGMGRL)  #用于dg broker的静态监听

      (ORACLE_HOME = D:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1)

      (SID_NAME = orcl)

    )

  )

LISTENER =

  (DESCRIPTION =

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

    (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))

  )

#主库tnsnames.ora

ORCL_STBY =

  (DESCRIPTION =

    (ADDRESS_LIST =

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

    )

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = orcl)

    )

  )

ORCL =

  (DESCRIPTION =

    (ADDRESS_LIST =

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

    )

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = orcl)

    )

  )

#备库listener.ora

SID_LIST_LISTENER =

  (SID_LIST =

    (SID_DESC =

      (GLOBAL_DBNAME = orcl)

      (ORACLE_HOME = D:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1)

      (SID_NAME = orcl)

    )

    (SID_DESC =

      (GLOBAL_DBNAME = orcl_stby_DGMGRL) #用于dg broker的静态监听

      (ORACLE_HOME = D:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1)

      (SID_NAME = orcl)

    )

  )

LISTENER =

  (DESCRIPTION =

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

    (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))

  )

#备库tnsnames.ora

ORCL_STBY =

  (DESCRIPTION =

    (ADDRESS_LIST =

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

    )

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = orcl)

    )

  )

ORCL =

  (DESCRIPTION =

    (ADDRESS_LIST =

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

    )

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = orcl)

    )

  )

#主库监听reload

lsnrctl reload

#备库启动监听

lsnrctl start

#创建一个临时参数文件如d:\pfile.txt内容如下

*.db_name='orcl'

#创建密码文件，或者从主库拷贝一个

orapwd file=D:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\database\PWDorcl.ora password=oracle entries=10

#创建备库所需目录

mkdir D:\app\Administrator\oradata\orcl\

mkdir D:\app\Administrator\admin\orcl\adump\

mkdir D:\app\Administrator\fast_recovery_area\orcl\

#用ORADIM创建实例

oradim -new -sid orcl

#用临时参数文件启动

sqlplus / as sysdba

starup nomount pfile='d:\pfile.txt';

#tnsping测试互通性

tnsping orcl

tnsping orcl_stby

#主库连接备库

sqlplus sys/oracle@stddb as sysdba

#备库连接主库

sqlplus sys/oracle@orcl as sysdba

#########################################################

#备库执行，连接主备库

rman target sys/oracle@orcl auxilary sys/oracle@orcl_stby

#创建dg备库，这里假设主备库路径相同

duplicate target database

  for standby

  from active database

  dorecover

  spfile

    set db_unique_name='orcl_stby'

  nofilenamecheck;

#########################################################

#如果主备库路径不同

duplicate target database

  for standby

  from active database

  dorecover

  spfile

    set db_unique_name='orcl_stby'

    set db_file_name_convert='orcl','orcl_stby'

    set log_file_name_convert='orcl','orcl_stby'

    set job_queue_processes='0'

  nofilenamecheck;

#开启ADG

sqlplus / as sysdba

alter database open read only;

alter database recover managed standby database disconnect from session;

#主备库两边执行

alter system set dg_broker_start=true;

#主库连接dgmgrl

dgmgrl sys/oracle@orcl

#创建dg broker配置

create configuration dg_config as primary database is orcl connect identifier is orcl;

#添加备库到配置文件

add database orcl_stby as connect identifier is orcl_stby;

#启用配置

enable configuration;

############################################################################

#显示DG配置信息

show configuration

show configuration verbose

#显示主备库信息

show database orcl

show database orcl_stby

show database verbose orcl

show database verbose orcl_stby

#测试Database Switchover

dgmgrl sys/oracle@orcl

switchover to orcl_stby;

show configuration

#切换回来

switchover to orcl;

show configuration

###########################################################################

#测试Database Failover，此时dg关系已经打破

dgmgrl sys/oracle@orcl

failover to orcl_stby;

#如果主库开启了flashback，执行以下语句自动重建主库

 reinstate database orcl;

#如果没有开启flashback，删除重建主库，重新建立dg关系

############################################################################

#测试快照备库

dgmgrl sys/oracle@orcl

convert database orcl_stby to snapshot standby;

show configuration;

#快照转成正常备库

convert database orcl_stby to physical standby;

show configuration;