Oracle 主库rac + 备库rac 11.2.0.4的DG环境部署
Oracle 主库rac + 备库rac 11.2.0.4的DG环境部署
AiDBA宝典
发布于 2023-04-27 13:15:42
发布于 2023-04-27 13:15:42
文章被收录于专栏：小麦苗的DB宝专栏
前言部分
导读和注意事项
各位技术爱好者，看完本文后，你可以掌握如下的技能，也可以学到一些其它你所不知道的知识，~O(∩_∩)O~：
① 主库为rac，备库为rac的物理dg的搭建（重点）
② dbca 静默方式创建rac数据库
③ 如何将数据库加入crsctl管理
④ rac库修改归档路径和归档模式
⑤ 如何添加standby日志
⑥ 简单物理dg的维护
本文简介
虽然之前已经多次安装过dg了，但都是基于单实例的，无论是物理还是逻辑的，感觉都非常轻松，没有碰到大的问题，但是最近同事安装dg老是碰到问题，尤其是在执行duplicate命令的时候报错，所以我想还是自己也搭建的试试吧，顺便也写写文档。
相关知识点扫盲
Physical Standby使用的是Media Recovery 技术，在数据块级别进行恢复，这种方式没有数据类型的限制，可以保证两个数据库完全一致。 Physical Standby数据库只能在Mount 状态下进行恢复，也可以是打开，但只能已只读方式打开，并且打开时不能执行恢复操作。Logical Standby使用的是Logminer 技术，通过把日志内容还原成SQL 语句，然后SQL引擎执行这些语句，Logminer Standby不支持所有数据类型，可以在视图DBA_LOGSTDBY_UNSUPPORTED 中查看不支持的数据类型，如果使用了这种数据类型，则不能保证数据库完全一致。LogicalStandby数据库可以在恢复的同时进行读写操作。
不少未实际接触过dg的初学者可能会下意识以为data guard是一个备份恢复的工具。我要说的是，这种形容不完全错，dg拥有备份的功能，某些情况下它甚至可以与primary数据库完全一模一样，但是它存在的目的并不仅仅是为了恢复数据，应该说它的存在是为了 确保企业数据的高可用性，数据保护以及灾难恢复 ( 注意这个字眼，灾难恢复) 。dg提供全面的服务包括：创建，维护，管理以及监控standby数据库，确保数据安全，管理员可以通过将一些操作转移到standby数据库执行的方式改善数据库性能 ，构建高可用的企业数据库应用环境。
在Data Gurad 环境中，至少有两个数据库，一个处于Open 状态对外提供服务，这个数据库叫作Primary Database。 第二个处于恢复状态，叫作Standby Database。 运行时primary Database 对外提供服务，用户在Primary Database 上进行操作，操作被记录在联机日志和归档日志中，这些日志通过网络传递给Standby Database。 这个日志会在Standby Database 上重演，从而实现Primary Database 和Standby Database 的数据同步。 Oracle Data Gurad 对这一过程进一步的优化设计，使得日志的传递，恢复工作更加自动化，智能化，并且提供一系列参数和命令简化了DBA工作。 如果是可预见因素需要关闭Primary Database，比如软硬件升级，可以把Standby Database 切换为Primary Database 继续对外服务，这样即减少了服务停止时间，并且数据不会丢失。如果异常原因导致Primary Database 不可用，也可以把Standby Database 强制切换为Primary Database继续对外服务，这时数据损失都和配置的数据保护级别有关系。因此Primary 和Standby 只是一个角色概念，并不固定在某个数据库中。
问题：RAC 和DG 有什么区别
RAC， Data Gurad， 高可用性体系中的二种工具，每个工具即可以独立应用，也可以相互配合。 他们各自的侧重点不同，适用场景也不同。
RAC 它的强项在于解决单点故障和负载均衡，因此RAC 方案常用于7*24 的核心系统，但RAC 方案中的数据只有一份，尽管可以通过RAID 等机制可以避免存储故障，但是数据本身是没有冗余的，容易形成单点故障。
Data Gurad 通过冗余数据来提供数据保护，Data Gurad 通过日志同步机制保证冗余数据和主数据之前的同步，这种同步可以是实时，延时，同步，异步多种形式。Data Gurad 常用于异地容灾和小企业的高可用性方案，虽然可以在Standby 机器上执行只读查询，从而分散Primary 数据库的性能压力，但是Data Gurad 决不是性能解决方案。
实验部分
创建物理备库的方法很多，对于Oracle 11g而言，可以直接从active database来创建，也可以基于10g 的RMAN备份方式来创建。
实验环境介绍
项目 | primary db | physical standby db |
|---|---|---|
db 类型 | rac | rac |
db version | 11.2.0.4.0 | 11.2.0.4.0 |
db 存储 | ASM | ASM |
主机IP地址/hosts配置 | 22.188.194.31 LHRAXXTDB322.188.194.33 LHRAXXTDB3-vip222.188.194.31 LHRAXXTDB3-priv22.188.194.32 LHRAXXTDB422.188.194.34 LHRAXXTDB4-vip222.188.194.32 LHRAXXTDB4-priv22.188.194.35 LHRAXXTDB-scan | 22.188.194.64 LHRAXXTDB122.188.194.65 LHRAXXTDB1-vip222.188.194.64 LHRAXXTDB1-priv22.188.194.66 LHRAXXTDB222.188.194.67 LHRAXXTDB2-vip222.188.194.66 LHRAXXTDB2-priv22.188.194.68 LHRAXXTDB-scan |
OS版本及kernel版本 | AIX 64位 7.1.0.0 | AIX 64位 7.1.0.0 |
OS hostname | LHRAXXTDB3 LHRAXXTDB4 | LHRAXXTDB1 LHRAXXTDB2 |
platform_name | AIX-Based Systems (64-bit) | AIX-Based Systems (64-bit) |
db time zone | 14 | 14 |
字符集 | ZHS16GBK | ZHS16GBK |
compatible | 11.2.0.4.0 | 11.2.0.4.0 |
归档模式 | Archive Mode | Archive Mode |
ORACLE_SID | DGPRI | DGPHY |
db_name/GLOBAL_DBNAME | TESTDG | TESTDG |
db_unique_name | TESTDG | TESTDGPHY |
TNS_NAME | TNS_DGPRI | TNS_DGPHY |
磁盘组 | +DATA | +DATA |
归档路径 | GPFS方式共享路径： /arch | GPFS方式共享路径： /arch |
ORACLE_HOME | /oracle/app/oracle/product/11.2.0/db | /oracle/app/oracle/product/11.2.0/db |
dbid | 2836886746 | 2836886746 |
注：标红的选项是必填，且需要特别关注的项目 |
该环境中我主要是为了区分dg中各种参数配置的是tnsnames、oracle_sid、dbname还是db_unique_name，所以设置的值不同，一般情况这4者可以设置成一样的，dg中db_unique_name、oracle_sid和tns可以设置成一样的。
主库操作
先创建主库
dbca -silent -createDatabase -templateName General_Purpose.dbc -gdbname TESTDG -sid DGPRI -sysPassword lhr -systemPassword lhr -datafileDestination 'DATA/' -redoLogFileSize 50 -recoveryAreaDestination '/arch' -storageType ASM -asmsnmpPassword lhr -diskGroupName 'DATA' -responseFile NO_VALUE -characterset ZHS16GBK -nationalCharacterSet AL16UTF16 -sampleSchema true -automaticMemoryManagement true -totalMemory 1024 -nodeinfo LHRAXXTDB3,LHRAXXTDB4
注意：sid不能含有下横线，如DG_PRI会报错：
The SID name can only contain alphanumeric characters.
静默方式创建一个rac主库：
[LHRAXXTDB3:root]:/>crsctl stat res -t
--------------------------------------------------------------------------------
NAME TARGET STATE SERVER STATE_DETAILS
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.DATA.dg
ONLINE ONLINE LHRAXXTDB3
ONLINE ONLINE LHRAXXTDB4
ora.LISTENER.lsnr
ONLINE ONLINE LHRAXXTDB3
ONLINE ONLINE LHRAXXTDB4
ora.asm
ONLINE ONLINE LHRAXXTDB3 Started
ONLINE ONLINE LHRAXXTDB4 Started
ora.gsd
OFFLINE OFFLINE LHRAXXTDB3
OFFLINE OFFLINE LHRAXXTDB4
ora.net1.network
ONLINE ONLINE LHRAXXTDB3
ONLINE ONLINE LHRAXXTDB4
ora.ons
ONLINE ONLINE LHRAXXTDB3
ONLINE ONLINE LHRAXXTDB4
ora.registry.acfs
ONLINE ONLINE LHRAXXTDB3
ONLINE ONLINE LHRAXXTDB4
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.LISTENER_SCAN1.lsnr
1 ONLINE ONLINE LHRAXXTDB4
ora.cvu
1 ONLINE ONLINE LHRAXXTDB4
ora.oc4j
1 ONLINE ONLINE LHRAXXTDB4
ora.oradesdb.db
1 ONLINE ONLINE LHRAXXTDB3 Open,Readonly
2 ONLINE ONLINE LHRAXXTDB4 Open,Readonly
ora.scan1.vip
1 ONLINE ONLINE LHRAXXTDB4
ora.testdg.db
1 OFFLINE OFFLINE
2 OFFLINE OFFLINE
ora.LHRAXXTDB3.vip
1 ONLINE ONLINE LHRAXXTDB3
ora.LHRAXXTDB4.vip
1 ONLINE ONLINE LHRAXXTDB4
[LHRAXXTDB3:root]:/>cat /etc/hosts
# IBM_PROLOG_BEGIN_TAG
# This is an automatically generated prolog.
#
# bos61D src/bos/usr/sbin/netstart/hosts 1.2
#
# Licensed Materials - Property of IBM
#
# COPYRIGHT International Business Machines Corp. 1985,1989
# All Rights Reserved
#
# US Government Users Restricted Rights - Use, duplication or
# disclosure restricted by GSA ADP Schedule Contract with IBM Corp.
#
# @(#)47 1.2 src/bos/usr/sbin/netstart/hosts, cmdnet, bos61D, d2007_49A2 10/1/07 13:57:52
# IBM_PROLOG_END_TAG
#
# COMPONENT_NAME: TCPIP hosts
#
# FUNCTIONS: loopback
#
# ORIGINS: 26 27
#
# (C) COPYRIGHT International Business Machines Corp. 1985, 1989
# All Rights Reserved
# Licensed Materials - Property of IBM
#
# US Government Users Restricted Rights - Use, duplication or
# disclosure restricted by GSA ADP Schedule Contract with IBM Corp.
#
# /etc/hosts
#
# This file contains the hostnames and their address for hosts in the
# network. This file is used to resolve a hostname into an Internet
# address.
#
# At minimum, this file must contain the name and address for each
# device defined for TCP in your /etc/net file. It may also contain
# entries for well-known (reserved) names such as timeserver
# and printserver as well as any other host name and address.
#
# The format of this file is:
# Internet Address Hostname # Comments
# Internet Address can be either IPv4 or IPv6 address.
# Items are separated by any number of blanks and/or tabs. A '#'
# indicates the beginning of a comment; characters up to the end of the
# line are not interpreted by routines which search this file. Blank
# lines are allowed.
# Internet Address Hostname # Comments
# 192.9.200.1 net0sample # ethernet name/address
# 128.100.0.1 token0sample # token ring name/address
# 10.2.0.2 x25sample # x.25 name/address
# 2000:1:1:1:209:6bff:feee:2b7f ipv6sample # ipv6 name/address
127.0.0.1 loopback localhost # loopback (lo0) name/address
::1 loopback localhost # IPv6 loopback (lo0) name/address
172.16.5.242 ZBNIM
22.188.129.254 aix7nim
22.188.189.42 ZFBKUPSVR
22.188.129.240 ZTINIMSERVER ZTI17
22.188.129.202 ZTDNETAP3 count_dest
22.188.189.42 ZFBKUPSVR
22.188.194.31 LHRAXXTDB3
22.188.194.33 LHRAXXTDB3-vip
222.188.194.31 LHRAXXTDB3-priv
22.188.194.32 LHRAXXTDB4
22.188.194.34 LHRAXXTDB4-vip
222.188.194.32 LHRAXXTDB4-priv
22.188.194.35 LHRAXXTDB-scan
[LHRAXXTDB3:root]:/>
[LHRAXXTDB3:root]:/>oslevel
7.1.0.0
[LHRAXXTDB3:root]:/>su - oracle
[LHRAXXTDB3:oracle]:/oracle>dbca -silent -createDatabase -templateName General_Purpose.dbc -gdbname TESTDG -sid DGPRI -sysPassword lhr -systemPassword lhr -datafileDestination 'DATA/' -redoLogFileSize 50 -recoveryAreaDestination '/arch' -storageType ASM -asmsnmpPassword lhr -diskGroupName 'DATA' -responseFile NO_VALUE -characterset ZHS16GBK -nationalCharacterSet AL16UTF16 -sampleSchema true -automaticMemoryManagement true -totalMemory 1024 -nodeinfo LHRAXXTDB3,LHRAXXTDB4
Copying database files
1% complete
3% complete
30% complete
Creating and starting Oracle instance
32% complete
36% complete
40% complete
44% complete
45% complete
48% complete
50% complete
Creating cluster database views
52% complete
70% complete
Completing Database Creation
73% complete
76% complete
85% complete
94% complete
100% complete
Look at the log file "/oracle/app/oracle/cfgtoollogs/dbca/TESTDG/TESTDG.log" for further details.
[LHRAXXTDB3:oracle]:/oracle>more /oracle/app/oracle/cfgtoollogs/dbca/TESTDG/TESTDG.log
Copying database files
DBCA_PROGRESS : 1%
DBCA_PROGRESS : 3%
DBCA_PROGRESS : 30%
Creating and starting Oracle instance
DBCA_PROGRESS : 32%
DBCA_PROGRESS : 36%
DBCA_PROGRESS : 40%
DBCA_PROGRESS : 44%
DBCA_PROGRESS : 45%
DBCA_PROGRESS : 48%
DBCA_PROGRESS : 50%
Creating cluster database views
DBCA_PROGRESS : 52%
DBCA_PROGRESS : 70%
Completing Database Creation
DBCA_PROGRESS : 73%
DBCA_PROGRESS : 76%
DBCA_PROGRESS : 85%
DBCA_PROGRESS : 94%
DBCA_PROGRESS : 100%
Database creation complete. For details check the logfiles at:
/oracle/app/oracle/cfgtoollogs/dbca/TESTDG.
Database Information:
Global Database Name:TESTDG
System Identifier(SID) Prefix:DGPRI
[LHRAXXTDB3:oracle]:/oracle>
[LHRAXXTDB3:oracle]:/oracle>crsctl stat res -t
--------------------------------------------------------------------------------
NAME TARGET STATE SERVER STATE_DETAILS
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.DATA.dg
ONLINE ONLINE LHRAXXTDB3
ONLINE ONLINE LHRAXXTDB4
ora.LISTENER.lsnr
ONLINE ONLINE LHRAXXTDB3
ONLINE ONLINE LHRAXXTDB4
ora.asm
ONLINE ONLINE LHRAXXTDB3 Started
ONLINE ONLINE LHRAXXTDB4 Started
ora.gsd
OFFLINE OFFLINE LHRAXXTDB3
OFFLINE OFFLINE LHRAXXTDB4
ora.net1.network
ONLINE ONLINE LHRAXXTDB3
ONLINE ONLINE LHRAXXTDB4
ora.ons
ONLINE ONLINE LHRAXXTDB3
ONLINE ONLINE LHRAXXTDB4
ora.registry.acfs
ONLINE ONLINE LHRAXXTDB3
ONLINE ONLINE LHRAXXTDB4
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.LISTENER_SCAN1.lsnr
1 ONLINE ONLINE LHRAXXTDB4
ora.cvu
1 ONLINE ONLINE LHRAXXTDB4
ora.oc4j
1 ONLINE ONLINE LHRAXXTDB4
ora.oradesdb.db
1 ONLINE ONLINE LHRAXXTDB3 Open,Readonly
2 ONLINE ONLINE LHRAXXTDB4 Open,Readonly
ora.scan1.vip
1 ONLINE ONLINE LHRAXXTDB4
ora.testdg.db
1 ONLINE ONLINE LHRAXXTDB3 Open
2 ONLINE ONLINE LHRAXXTDB4 Open
ora.LHRAXXTDB3.vip
1 ONLINE ONLINE LHRAXXTDB3
ora.LHRAXXTDB4.vip
1 ONLINE ONLINE LHRAXXTDB4
[LHRAXXTDB3:root]:/>
主库前期准备
force logging + archivelog
- 创建一个用户lhr用于测试
- 修改主库为force logging模式
- 修改主库为归档模式
select INST_ID, dbid,name,DB_UNIQUE_NAME,current_scn,protection_mode,database_role,force_logging,open_mode,switchover_status from gv$database;
[LHRAXXTDB3:oracle]:/oracle>export ORACLE_SID=DGPRI1
[LHRAXXTDB3:oracle]:/oracle>sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Wed Feb 17 11:19:24 2016
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
SYS@DGPRI1> create user lhr identified by lhr;
User created.
SYS@DGPRI1> grant dba to lhr;
Grant succeeded.
SYS@DGPRI1> set line 9999
SYS@DGPRI1> select name , open_mode, log_mode,force_logging,DATABASE_ROLE,switchover_status from gv$database;
NAME OPEN_MODE LOG_MODE FOR DATABASE_ROLE SWITCHOVER_STATUS
--------- -------------------- ------------ --- ---------------- --------------------
TESTDG READ WRITE NOARCHIVELOG NO PRIMARY NOT ALLOWED
TESTDG READ WRITE NOARCHIVELOG NO PRIMARY NOT ALLOWED
SYS@DGPRI1> alter database force logging;
Database altered.
SYS@DGPRI1> select name , open_mode, log_mode,force_logging,DATABASE_ROLE,switchover_status from gv$database;
NAME OPEN_MODE LOG_MODE FOR DATABASE_ROLE SWITCHOVER_STATUS
--------- -------------------- ------------ --- ---------------- --------------------
TESTDG READ WRITE NOARCHIVELOG YES PRIMARY NOT ALLOWED
TESTDG READ WRITE NOARCHIVELOG YES PRIMARY NOT ALLOWED
SYS@DGPRI1>
alter system set log_archive_dest_1='LOCATION=/arch' scope=spfile sid='DGPRI1';
alter system set log_archive_dest_1='LOCATION=/arch' scope=spfile sid='DGPRI2';
alter system set log_archive_dest_1='LOCATION=/arch' scope=spfile sid='*';
host srvctl stop database -d TESTDG -o immediate
host srvctl status database -d TESTDG
host srvctl start database -d TESTDG -o mount
alter database archivelog;
修改rac主库为归档模式：
SYS@DGPRI1> alter system set log_archive_dest_1='LOCATION=/arch' scope=spfile sid='DGPRI1';
System altered.
SYS@DGPRI1> alter system set log_archive_dest_1='LOCATION=/arch' scope=spfile sid='DGPRI2';
System altered.
SYS@DGPRI1> host srvctl stop database -d TESTDG -o immediate
SYS@DGPRI1> host srvctl status database -d TESTDG
Instance DGPRI1 is not running on node LHRAXXTDB3
Instance DGPRI2 is not running on node LHRAXXTDB4
SYS@DGPRI1> host srvctl start database -d TESTDG -o mount
SYS@DGPRI1> archive log list;
ORA-03135: connection lost contact
SYS@DGPRI1> conn / as sysdba
Connected.
SYS@DGPRI1> alter database archivelog;
Database altered.
SYS@DGPRI1> set line 9999
SYS@DGPRI1> select name , open_mode, log_mode,force_logging,DATABASE_ROLE,switchover_status from gv$database;
NAME OPEN_MODE LOG_MODE FOR DATABASE_ROLE SWITCHOVER_STATUS
--------- -------------------- ------------ --- ---------------- --------------------
TESTDG MOUNTED ARCHIVELOG YES PRIMARY NOT ALLOWED
TESTDG MOUNTED ARCHIVELOG YES PRIMARY NOT ALLOWED
SYS@DGPRI1> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /arch
Oldest online log sequence 5
Next log sequence to archive 6
Current log sequence 6
SYS@DGPRI1> alter database open;
Database altered.
SYS@DGPRI1> select name , open_mode, log_mode,force_logging,DATABASE_ROLE,switchover_status from gv$database;
NAME OPEN_MODE LOG_MODE FOR DATABASE_ROLE SWITCHOVER_STATUS
--------- -------------------- ------------ --- ---------------- --------------------
TESTDG READ WRITE ARCHIVELOG YES PRIMARY NOT ALLOWED
TESTDG MOUNTED ARCHIVELOG YES PRIMARY NOT ALLOWED
SYS@DGPRI1>
启动第二个节点：
[LHRAXXTDB4:root]:/>ORACLE_SID=DGPRI2
[LHRAXXTDB4:root]:/>su - oracle
[LHRAXXTDB4:oracle]:/oracle>ORACLE_SID=DGPRI2
[LHRAXXTDB4:oracle]:/oracle>sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Wed Feb 17 12:26:42 2016
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
SYS@DGPRI2> alter database open;
Database altered.
SYS@DGPRI2> set line 9999
SYS@DGPRI2> select name , open_mode, log_mode,force_logging,DATABASE_ROLE,switchover_status from gv$database;
NAME OPEN_MODE LOG_MODE FOR DATABASE_ROLE SWITCHOVER_STATUS
--------- -------------------- ------------ --- ---------------- --------------------
TESTDG READ WRITE ARCHIVELOG YES PRIMARY NOT ALLOWED
TESTDG READ WRITE ARCHIVELOG YES PRIMARY NOT ALLOWED
SYS@DGPRI2> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /arch
Oldest online log sequence 1
Next log sequence to archive 1
Current log sequence 1
SYS@DGPRI2>
为主库添加standby redo log
为主库添加standby redo log，简要描述一下standby redo log的作用
实际上就是与主库接收到的重做日志相对应，也就是说备库调用RFS进程将从主库接收到的重做日志按顺序写入到standby logfile
在主库创建standby logfile是便于发生角色转换后备用
sandby redo log创建原则：
a)、确保standby redo log的大小与主库online redo log的大小一致
b)、如主库为单实例数据库：standby redo log组数=主库日志组总数+1
c)、如果主库是RAC数据库：standby redo log组数=(每线程的日志组数+1)*最大线程数
d)、不建议复用standby redo log，避免增加额外的I/O以及延缓重做传输
单实例:
alter database add standby logfile group 4 ('/u01/app/oracle/oradata/oralg/standby_redo04.log') size 50m;
alter database add standby logfile group 5 ('/u01/app/oracle/oradata/oralg/standby_redo05.log') size 50m;
alter database add standby logfile group 6 ('/u01/app/oracle/oradata/oralg/standby_redo06.log') size 50m;
alter database add standby logfile group 7 ('/u01/app/oracle/oradata/oralg/standby_redo07.log') size 50m;
rac下：
alter database add standby logfile thread 1 group 5 size 50M ,group 6 size 50M ,group 7 size 50M ;
alter database add standby logfile thread 2 group 8 size 50M ,group 9 size 50M ,group 10 size 50M ;
SYS@DGPRI1> select * from v$standby_log;
no rows selected
SYS@DGPRI1> select group#,THREAD#,MEMBERS from v$log ;
GROUP# THREAD# MEMBERS
---------- ---------- ----------
1 1 2
2 1 2
3 2 2
4 2 2
SYS@DGPRI1>
SYS@DGPRI1> col member format a100
SYS@DGPRI1> select GROUP# ,STATUS , TYPE , MEMBER from v$logfile;
GROUP# STATUS TYPE MEMBER
---------- ------- ------- ----------------------------------------------------------------------------------------------------
2 ONLINE +DATA/testdg/onlinelog/group_2.260.904043421
2 ONLINE +DATA/testdg/onlinelog/group_2.263.904043421
1 ONLINE +DATA/testdg/onlinelog/group_1.262.904043421
1 ONLINE +DATA/testdg/onlinelog/group_1.261.904043421
3 ONLINE +DATA/testdg/onlinelog/group_3.303.904043623
3 ONLINE +DATA/testdg/onlinelog/group_3.304.904043623
4 ONLINE +DATA/testdg/onlinelog/group_4.305.904043623
4 ONLINE +DATA/testdg/onlinelog/group_4.306.904043623
8 rows selected.
SYS@DGPRI1> alter database add standby logfile thread 1 group 5 size 50M ,group 6 size 50M ,group 7 size 50M ;
Database altered.
SYS@DGPRI1> alter database add standby logfile thread 2 group 8 size 50M ,group 9 size 50M ,group 10 size 50M ;
Database altered.
SYS@DGPRI1> select GROUP#,DBID,THREAD#,SEQUENCE#,BYTES,BLOCKSIZE,USED,ARCHIVED,STATUS from v$standby_log;
GROUP# DBID THREAD# SEQUENCE# BYTES BLOCKSIZE USED ARC STATUS
---------- ---------------------------------------- ---------- ---------- ---------- ---------- ---------- --- ----------
5 UNASSIGNED 1 0 52428800 512 0 YES UNASSIGNED
6 UNASSIGNED 1 0 52428800 512 0 YES UNASSIGNED
7 UNASSIGNED 1 0 52428800 512 0 YES UNASSIGNED
8 UNASSIGNED 2 0 52428800 512 0 YES UNASSIGNED
9 UNASSIGNED 2 0 52428800 512 0 YES UNASSIGNED
10 UNASSIGNED 2 0 52428800 512 0 YES UNASSIGNED
6 rows selected.
SYS@DGPRI1>
SYS@DGPRI1> select GROUP# ,STATUS , TYPE , MEMBER from v$logfile;
GROUP# STATUS TYPE MEMBER
---------- ------- ------- ----------------------------------------------------------------------------------------------------
2 ONLINE +DATA/testdg/onlinelog/group_2.260.904043421
2 ONLINE +DATA/testdg/onlinelog/group_2.263.904043421
1 ONLINE +DATA/testdg/onlinelog/group_1.262.904043421
1 ONLINE +DATA/testdg/onlinelog/group_1.261.904043421
3 ONLINE +DATA/testdg/onlinelog/group_3.303.904043623
3 ONLINE +DATA/testdg/onlinelog/group_3.304.904043623
4 ONLINE +DATA/testdg/onlinelog/group_4.305.904043623
4 ONLINE +DATA/testdg/onlinelog/group_4.306.904043623
5 STANDBY +DATA/testdg/onlinelog/group_5.267.904048731
5 STANDBY +DATA/testdg/onlinelog/group_5.294.904048731
6 STANDBY +DATA/testdg/onlinelog/group_6.308.904048733
6 STANDBY +DATA/testdg/onlinelog/group_6.309.904048733
7 STANDBY +DATA/testdg/onlinelog/group_7.310.904048733
7 STANDBY +DATA/testdg/onlinelog/group_7.311.904048733
8 STANDBY +DATA/testdg/onlinelog/group_8.312.904048737
8 STANDBY +DATA/testdg/onlinelog/group_8.313.904048737
9 STANDBY +DATA/testdg/onlinelog/group_9.314.904048737
9 STANDBY +DATA/testdg/onlinelog/group_9.315.904048739
10 STANDBY +DATA/testdg/onlinelog/group_10.316.904048739
10 STANDBY +DATA/testdg/onlinelog/group_10.317.904048739
20 rows selected.
SYS@DGPRI1> show parameter name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
cell_offloadgroup_name string
db_file_name_convert string
db_name string TESTDG
db_unique_name string TESTDG
global_names boolean FALSE
instance_name string DGPRI1
lock_name_space string
log_file_name_convert string
processor_group_name string
service_names string TESTDG
SYS@DGPRI1>
修改主库参数文件
使用下面的命令修改主库参数(此时主库应当使用spfile启动参数)
LOG_ARCHIVE_CONFIG = 'DG_CONFIG ( db_unique_name, db_unique_name, … )' 主库与备库端采用相同设置。 LOG_ARCHIVE_DEST_n ='SERVICE=。。。。。 SERIVCE: 用于指定备用数据库的TNSNAMES描述符 db_file_name_convert、 log_file_name_convert 参数值为路径，可以直接写db_unique_name， 如果使用ASM，可以设置为*.db_file_name_convert =('+DATA','+RECOVERY')
fal_server、 fal_client 参数值为TNSNAMES描述符
alter system set db_unique_name='TESTDG' scope=spfile;
alter system set log_archive_config='DG_CONFIG=(TESTDG,TESTDGPHY)' sid='*';
alter system set log_archive_dest_1='LOCATION=/arch db_unique_name=TESTDG valid_for=(ALL_LOGFILES,ALL_ROLES)' sid='*';
alter system set log_archive_dest_2='SERVICE=TNS_DGPHY LGWR ASYNC db_unique_name=TESTDGPHY valid_for=(ONLINE_LOGFILES,PRIMARY_ROLE)' sid='*';
alter system set log_archive_dest_state_1=enable sid='*';
alter system set log_archive_dest_state_2=enable sid='*';
alter system set log_archive_max_processes=4 sid='*';
alter system set remote_login_passwordfile='EXCLUSIVE' scope=spfile;
--Add below item when DB turn to standby role
alter system set db_file_name_convert='TESTDGPHY','TESTDG' scope=spfile;
alter system set log_file_name_convert='TESTDGPHY','TESTDG' scope=spfile;
alter system set standby_file_management='AUTO' sid='*';
alter system set fal_server='TNS_DGPHY' sid='*';
alter system set fal_client='TNS_DGPRI' sid='*';
SYS@DGPRI1> show parameter spfile
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string +DATA/testdg/spfiledgpri.ora
SYS@DGPRI1>
SYS@DGPRI1>alter system set db_unique_name='TESTDG' scope=spfile;
alter system set log_archive_config='DG_CONFIG=(TESTDG,TESTDGPHY)' sid='*';
alter system set log_archive_dest_1='LOCATION=/arch db_unique_name=TESTDG valid_for=(ALL_LOGFILES,ALL_ROLES)' sid='*';
alter system set log_archive_dest_2='SERVICE=TNS_DGPHY LGWR ASYNC db_unique_name=TESTDGPHY valid_for=(ONLINE_LOGFILES,PRIMARY_ROLE)' sid='*';
alter system set log_archive_dest_state_1=enable sid='*';
alter system set log_archive_dest_state_2=enable sid='*';
alter system set log_archive_max_processes=4 sid='*';
alter system set remote_login_passwordfile='EXCLUSIVE' scope=spfile;
--Add below item when DB turn to standby role
alter system set db_file_name_convert='TESTDGPHY','TESTDG' scope=spfile;
alter system set log_file_name_convert='TESTDGPHY','TESTDG' scope=spfile;
alter system set standby_file_management='AUTO' sid='*';
alter system set fal_server='TNS_DGPHY' sid='*';
alter system set fal_client='TNS_DGPRI' sid='*';
SYS@DGPRI1>
System altered.
SYS@DGPRI1>
System altered.
SYS@DGPRI1>
System altered.
SYS@DGPRI1>
System altered.
SYS@DGPRI1>
System altered.
SYS@DGPRI1>
System altered.
SYS@DGPRI1>
System altered.
SYS@DGPRI1>
System altered.
SYS@DGPRI1>
System altered.
SYS@DGPRI1>
System altered.
SYS@DGPRI1>
System altered.
SYS@DGPRI1>
System altered.
SYS@DGPRI1>
System altered.
SYS@DGPRI1> SYS@DGPRI1> SYS@DGPRI1> SYS@DGPRI1>
SYS@DGPRI1>
SYS@DGPRI1>
SYS@DGPRI1>
SYS@DGPRI1>
SYS@DGPRI1>
配置主库和备库的监听
为主库和备库配置监听，整个DG的redo传输服务，都依赖于Oracle Net，因此需要为主备库配置监听，且需要配置静态监听
配置方法多种多样，可用netmgr，netca，以及直接编辑listener.ora 与tnsnames.ora文件
下面是配置之后的listener.ora 与tnsnames.ora文件内容
more $ORACLE_HOME/network/admin/listener.ora
主库
在主库的第一个节点的监听文件中加入如下内容：
在主库的第一个节点的监听文件中加入如下内容：
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 22.188.194.31)(PORT = 1521))
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /oracle/app/oracle/product/11.2.0/db)
(PROGRAM = extproc)
)
(SID_DESC =
(GLOBAL_DBNAME = TESTDG)
(ORACLE_HOME = /oracle/app/oracle/product/11.2.0/db)
(SID_NAME= DGPRI1)
)
)
第二个节点加入：
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 22.188.194.32)(PORT = 1521))
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /oracle/app/oracle/product/11.2.0/db)
(PROGRAM = extproc)
)
(SID_DESC =
(GLOBAL_DBNAME = TESTDG)
(ORACLE_HOME = /oracle/app/oracle/product/11.2.0/db)
(SID_NAME= DGPRI2)
)
)
以第一个节点为例，其它类似：
[LHRAXXTDB3:oracle]:/oracle>lsnrctl status
LSNRCTL for IBM/AIX RISC System/6000: Version 11.2.0.4.0 - Production on 17-FEB-2016 16:50:02
Copyright (c) 1991, 2013, Oracle. All rights reserved.
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for IBM/AIX RISC System/6000: Version 11.2.0.4.0 - Production
Start Date 31-DEC-2015 15:02:50
Uptime 48 days 1 hr. 47 min. 12 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /oracle/app/11.2.0/grid/network/admin/listener.ora
Listener Log File /oracle/app/grid/diag/tnslsnr/LHRAXXTDB3/listener/alert/log.xml
将上边所说的内容加入文件：/oracle/app/11.2.0/grid/network/admin/listener.ora ，然后重启监听器，lsnrctl stop , lsnrctl start，需要注意的是rac下是在grid用户下修改监听文件的。
备库
监听配置参考主库的操作：
备库第一个节点：
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 22.188.194.64)(PORT = 1521))
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /oracle/app/oracle/product/11.2.0/db)
(PROGRAM = extproc)
)
(SID_DESC =
(GLOBAL_DBNAME = TESTDG)
(ORACLE_HOME = /oracle/app/oracle/product/11.2.0/db)
(SID_NAME= DGPHY1)
)
)
备库第二个节点：
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 22.188.194.66)(PORT = 1521))
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /oracle/app/oracle/product/11.2.0/db)
(PROGRAM = extproc)
)
(SID_DESC =
(GLOBAL_DBNAME = TESTDG)
(ORACLE_HOME = /oracle/app/oracle/product/11.2.0/db)
(SID_NAME= DGPHY2)
)
)
配置主库和备库的tnsname
[LHRAXXTDB3:grid]:/oracle/app/11.2.0/grid/network/admin>exit
[LHRAXXTDB3:root]:/>su - oracle
[LHRAXXTDB3:oracle]:/oracle>cd $ORACLE_HOME/network/admin
[LHRAXXTDB3:oracle]:/oracle/app/oracle/product/11.2.0/db/network/admin>ls
samples shrept.lst sqlnet.ora tnsnames.b tnsnames.ora
[LHRAXXTDB3:oracle]:/oracle/app/oracle/product/11.2.0/db/network/admin>vi $ORACLE_HOME/network/admin/tnsnames.ora
注意：tns文件是在oracle用户下配置，将主库和备库的一共4个节点的tnsnames.ora文件加入如下内容：
TNS_DGPRI =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 22.188.194.31)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = 22.188.194.32)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = TESTDG)
)
)
TNS_DGPHY =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 22.188.194.64)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = 22.188.194.66)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = TESTDG)
)
)
在4个节点上分别测试：
[LHRAXXTDB1:oracle]:/oracle>tnsping TNS_DGPHY
TNS Ping Utility for IBM/AIX RISC System/6000: Version 11.2.0.4.0 - Production on 17-FEB-2016 17:50:00
Copyright (c) 1997, 2013, Oracle. All rights reserved.
Used parameter files:
/oracle/app/oracle/product/11.2.0/db/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 22.188.194.64)(PORT = 1521)) (ADDRESS = (PROTOCOL = TCP)(HOST = 22.188.194.66)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = TESTDG)))
OK (80 msec)
[LHRAXXTDB1:oracle]:/oracle>tnsping TNS_DGPRI
TNS Ping Utility for IBM/AIX RISC System/6000: Version 11.2.0.4.0 - Production on 17-FEB-2016 17:50:09
Copyright (c) 1997, 2013, Oracle. All rights reserved.
Used parameter files:
/oracle/app/oracle/product/11.2.0/db/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 22.188.194.31)(PORT = 1521)) (ADDRESS = (PROTOCOL = TCP)(HOST = 22.188.194.32)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = TESTDG)))
OK (0 msec)
[LHRAXXTDB1:oracle]:/oracle>
配置主备库密码文件
由于要求主库与备库sys使用相同的密码，在此处，我们直接复制了主库的密码文件到备库，将主库第一个节点的密码文件copy到其它3个节点并修改名称，名称为orapw+ORACLE_SID
[LHRAXXTDB3:oracle]:/oracle>l $ORACLE_HOME/dbs/orapwDGPRI*
-rw-r----- 1 oracle dba 1536 Feb 17 11:12 /oracle/app/oracle/product/11.2.0/db/dbs/orapwDGPRI1
[LHRAXXTDB3:oracle]:/oracle>cp /oracle/app/oracle/product/11.2.0/db/dbs/orapwDGPRI1 /oracle/app/oracle/product/11.2.0/db/dbs/orapwDGPRI2
[LHRAXXTDB3:oracle]:/oracle>cp /oracle/app/oracle/product/11.2.0/db/dbs/orapwDGPRI1 /oracle/app/oracle/product/11.2.0/db/dbs/orapwDGPHY1
[LHRAXXTDB3:oracle]:/oracle>cp /oracle/app/oracle/product/11.2.0/db/dbs/orapwDGPRI1 /oracle/app/oracle/product/11.2.0/db/dbs/orapwDGPHY2
[LHRAXXTDB3:oracle]:/oracle>scp /oracle/app/oracle/product/11.2.0/db/dbs/orapwDGPRI2 oracle@22.188.194.32:/oracle/app/oracle/product/11.2.0/db/dbs/orapwDGPRI2
[LHRAXXTDB3:oracle]:/oracle>scp /oracle/app/oracle/product/11.2.0/db/dbs/orapwDGPHY1 oracle@22.188.194.64:/oracle/app/oracle/product/11.2.0/db/dbs/orapwDGPHY1
[LHRAXXTDB3:oracle]:/oracle>scp /oracle/app/oracle/product/11.2.0/db/dbs/orapwDGPHY2 oracle@22.188.194.66:/oracle/app/oracle/product/11.2.0/db/dbs/orapwDGPHY2
备库操作
配置备库路径
11g一般创建如下路径，若是asm的话只需要创建 /u01/app/oracle/admin/TESTDGPHY/adump
mkdir -p $ORACLE_BASE/admin/TESTDGPHY/adump
mkdir -p $ORACLE_BASE/oradata/TESTDGPHY/
mkdir -p $ORACLE_BASE/oradata/TESTDGPHY/standby_redo/
[LHRAXXTDB1:oracle]:/oracle>mkdir -p $ORACLE_BASE/admin/TESTDGPHY/adump
[LHRAXXTDB1:oracle]:/oracle>
第二个节点创建：
[LHRAXXTDB2:oracle]:/oracle>mkdir -p $ORACLE_BASE/admin/TESTDGPHY/adump
[LHRAXXTDB2:oracle]:/oracle>
11g如果不创建audit_file_dest的路径会报如下的错误，而10g的话创建的路径比较多，可以查看spfile内容进行创建：
RMAN-04014: startup failed: ORA-09925: Unable to create audit trail file
IBM AIX RISC System/6000 Error: 2: No such file or directory
Additional information: 9925
配置备库pfile文件并启动到nomount状态
我们选择备库的第一个节点作为实施节点:
[LHRAXXTDB1:root]:/>su - oracle
[LHRAXXTDB1:oracle]:/oracle>cd $ORACLE_HOME/dbs
[LHRAXXTDB1:oracle]:/oracle/app/oracle/product/11.2.0/db/dbs>l
total 42416
-rw-rw---- 1 oracle dba 1544 Dec 25 09:31 hc_oraESK1.dat
-rw-rw---- 1 oracle dba 1544 Feb 18 09:32 hc_oraESKDB1.dat
-rw-rw---- 1 oracle dba 1544 Jul 16 2015 hc_oraMCIS1.dat
-rw-rw---- 1 oracle dba 1544 Nov 18 2014 hc_oraNUW1.dat
-rw-rw---- 1 oracle dba 1544 Dec 23 2014 hc_oraTEST2.dat
-rw-rw---- 1 oracle dba 1544 Jan 29 16:17 hc_oralhr.dat
-rw-rw---- 1 oracle dba 1544 Jan 29 15:47 hc_oralhr1.dat
-rw-r--r-- 1 oracle dba 2851 May 15 2009 init.ora
-rw-r----- 1 oracle dba 162 Jul 15 2015 initDBUA4155109.ora
-rw-r----- 1 oracle dba 39 Dec 24 17:21 initoraESK1.ora
-rw-r--r-- 1 oracle dba 66 Feb 01 17:08 initoraESKDB1.ora
-rw-r----- 1 oracle dba 42 Jul 15 2015 initoraMCIS1.ora
-rw-r----- 1 oracle dba 40 Nov 06 2014 initoraNUW1.ora
-rw-r----- 1 oracle dba 42 Nov 19 2014 initoraTEST2.ora
-rw-r--r-- 1 oracle dba 923 Jan 29 15:51 initoralhr.ora
-rw-r----- 1 oracle dba 843 Jan 08 09:20 initoralhr.ora.bak.LHRAXXTDB1
-rw-r--r-- 1 oracle dba 80 Jan 29 15:47 initoralhr1.ora
-rw-r----- 1 oracle dba 1536 Jul 15 2015 orapwDBUA4155109
-rw-r----- 1 oracle dba 1536 Feb 17 17:59 orapwDGPHY1
-rw-r----- 1 oracle dba 1536 Jan 08 16:16 orapworaESKDB1
-rw-r----- 1 oracle dba 1536 Jul 15 2015 orapworaMCIS1
-rw-r----- 1 oracle dba 1536 Nov 06 2014 orapworaNUW1
-rw-r----- 1 oracle dba 1536 Nov 19 2014 orapworaTEST2
-rw-r----- 1 oracle dba 1536 Jan 07 15:43 orapworalhr
-rw-r----- 1 oracle dba 21610496 Jan 04 14:52 snapcf_oraESKDB1.f
[LHRAXXTDB1:oracle]:/oracle>export ORACLE_SID=DGPHY1
[LHRAXXTDB1:oracle]:/oracle>sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Thu Feb 18 10:20:55 2016
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to an idle instance.
SYS@DGPHY1> startup nomount pfile=?/dbs/initDGPHY1.ora
ORACLE instance started.
Total System Global Area 271437824 bytes
Fixed Size 2245464 bytes
Variable Size 213912744 bytes
Database Buffers 50331648 bytes
Redo Buffers 4947968 bytes
SYS@DGPHY1>
利用rman的duplicate复制主库文件到备库
--对于从主库克隆standby有多种方法，而且Oracle 11g支持从ative database直接克隆数据库
--为主库生成控制文件,注，对于配置standby，不能直接使用copy方式复制控制文件到备库
我们选择备库的第一个节点作为实施节点，脚本需要修改的地方参考前边的说明，懒人可以把TNS和db_unique_name设置成一样的:
duplicate target database
for standby nofilenamecheck
from active database
DORECOVER
spfile
set db_unique_name='TESTDGPHY'
set log_archive_dest_1='LOCATION=/arch valid_for=(ALL_LOGFILES,ALL_ROLES) db_unique_name=TESTDGPHY'
set log_archive_dest_2='service=TNS_DGPRI async lgwr register valid_for=(online_logfile,primary_role) db_unique_name=TESTDG'
set standby_file_management='AUTO'
set fal_server='TNS_DGPRI'
set fal_client='TESTDGPHY'
set control_files='+DATA/TESTDGPHY/controlfile/crontal01.ctl','+DATA/TESTDGPHY/controlfile/control02.ctl'
set db_file_name_convert='TESTDG','TESTDGPHY'
set log_file_name_convert='TESTDG','TESTDGPHY'
set memory_target='1024M'
set audit_file_dest='/oracle/app/oracle/admin/TESTDGPHY/adump'
set db_create_file_dest = '+DATA'
set instance_number = '1'
;
-- 若主库比较大，也可以多开几个channel来传递，如下脚本：
run {
allocate channel ch001 type disk;
allocate channel ch002 type disk;
allocate channel ch003 type disk;
allocate channel ch004 type disk;
allocate auxiliary channel ch005 type disk;
duplicate target database
for standby nofilenamecheck
from active database
DORECOVER
spfile
set db_unique_name='TESTDGPHY'
set log_archive_dest_1='LOCATION=/arch valid_for=(ALL_LOGFILES,ALL_ROLES) db_unique_name=TESTDGPHY'
set log_archive_dest_2='service=TNS_DGPRI async lgwr register valid_for=(online_logfile,primary_role) db_unique_name=TESTDG'
set standby_file_management='AUTO'
set fal_server='TNS_DGPRI'
set fal_client='TESTDGPHY'
set control_files='+DATA/TESTDGPHY/controlfile/crontal01.ctl','+DATA/TESTDGPHY/controlfile/control02.ctl'
set db_file_name_convert='TESTDG','TESTDGPHY'
set log_file_name_convert='TESTDG','TESTDGPHY'
set memory_target='1024M'
set audit_file_dest='/oracle/app/oracle/admin/TESTDGPHY/adump'
set db_create_file_dest = '+DATA'
set instance_number = '1'
;
release channel ch001;
release channel ch002;
release channel ch003;
release channel ch004;
release channel ch005;
}
示例：
[LHRAXXTDB1:oracle]:/oracle>rman target sys/lhr@TNS_DGPRI auxiliary sys/lhr@TNS_DGPHY
Recovery Manager: Release 11.2.0.4.0 - Production on Thu Feb 18 14:29:29 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: TESTDG (DBID=2836886746)
connected to auxiliary database: TESTDG (not mounted)
RMAN> duplicate target database
2> for standby nofilenamecheck
3> from active database
4> DORECOVER
5> spfile
6> set db_unique_name='TESTDGPHY'
7> set log_archive_dest_1='LOCATION=/arch valid_for=(ALL_LOGFILES,ALL_ROLES) db_unique_name=TESTDGPHY'
8> set log_archive_dest_2='service=TNS_DGPRI async lgwr register valid_for=(online_logfile,primary_role) db_unique_name=TESTDG'
9> set standby_file_management='AUTO'
10> set fal_server='TNS_DGPRI'
11> set fal_client='TESTDGPHY'
12> set control_files='+DATA/TESTDGPHY/controlfile/crontal01.ctl','+DATA/TESTDGPHY/controlfile/control02.ctl'
13> set db_file_name_convert='TESTDG','TESTDGPHY'
14> set log_file_name_convert='TESTDG','TESTDGPHY'
15> set memory_target='1024M'
16> set audit_file_dest='/oracle/app/oracle/admin/TESTDGPHY/adump'
17> set db_create_file_dest = '+DATA'
18> set instance_number = '1'
19> ;
Starting Duplicate Db at 2016-02-18 14:29:41
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=44 device type=DISK
contents of Memory Script:
{
backup as copy reuse
targetfile '/oracle/app/oracle/product/11.2.0/db/dbs/orapwDGPRI1' auxiliary format
'/oracle/app/oracle/product/11.2.0/db/dbs/orapwDGPHY1' targetfile
'+DATA/testdg/spfiledgpri.ora' auxiliary format
'/oracle/app/oracle/product/11.2.0/db/dbs/spfileDGPHY1.ora' ;
sql clone "alter system set spfile= ''/oracle/app/oracle/product/11.2.0/db/dbs/spfileDGPHY1.ora''";
}
executing Memory Script
Starting backup at 2016-02-18 14:29:41
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=71 instance=DGPRI1 device type=DISK
Finished backup at 2016-02-18 14:29:44
sql statement: alter system set spfile= ''/oracle/app/oracle/product/11.2.0/db/dbs/spfileDGPHY1.ora''
contents of Memory Script:
{
sql clone "alter system set db_unique_name =
''TESTDGPHY'' comment=
'''' scope=spfile";
sql clone "alter system set log_archive_dest_1 =
''LOCATION=/arch valid_for=(ALL_LOGFILES,ALL_ROLES) db_unique_name=TESTDGPHY'' comment=
'''' scope=spfile";
sql clone "alter system set log_archive_dest_2 =
''service=TNS_DGPRI async lgwr register valid_for=(online_logfile,primary_role) db_unique_name=TESTDG'' comment=
'''' scope=spfile";
sql clone "alter system set standby_file_management =
''AUTO'' comment=
'''' scope=spfile";
sql clone "alter system set fal_server =
''TNS_DGPRI'' comment=
'''' scope=spfile";
sql clone "alter system set fal_client =
''TESTDGPHY'' comment=
'''' scope=spfile";
sql clone "alter system set control_files =
''+DATA/TESTDGPHY/controlfile/crontal01.ctl'', ''+DATA/TESTDGPHY/controlfile/control02.ctl'' comment=
'''' scope=spfile";
sql clone "alter system set db_file_name_convert =
''TESTDG'', ''TESTDGPHY'' comment=
'''' scope=spfile";
sql clone "alter system set log_file_name_convert =
''TESTDG'', ''TESTDGPHY'' comment=
'''' scope=spfile";
sql clone "alter system set memory_target =
1024M comment=
'''' scope=spfile";
sql clone "alter system set audit_file_dest =
''/oracle/app/oracle/admin/TESTDGPHY/adump'' comment=
'''' scope=spfile";
sql clone "alter system set db_create_file_dest =
''+DATA'' comment=
'''' scope=spfile";
sql clone "alter system set instance_number =
1 comment=
'''' scope=spfile";
shutdown clone immediate;
startup clone nomount;
}
executing Memory Script
sql statement: alter system set db_unique_name = ''TESTDGPHY'' comment= '''' scope=spfile
sql statement: alter system set log_archive_dest_1 = ''LOCATION=/arch valid_for=(ALL_LOGFILES,ALL_ROLES) db_unique_name=TESTDGPHY'' comment= '''' scope=spfile
sql statement: alter system set log_archive_dest_2 = ''service=TNS_DGPRI async lgwr register valid_for=(online_logfile,primary_role) db_unique_name=TESTDG'' comment= '''' scope=spfile
sql statement: alter system set standby_file_management = ''AUTO'' comment= '''' scope=spfile
sql statement: alter system set fal_server = ''TNS_DGPRI'' comment= '''' scope=spfile
sql statement: alter system set fal_client = ''TESTDGPHY'' comment= '''' scope=spfile
sql statement: alter system set control_files = ''+DATA/TESTDGPHY/controlfile/crontal01.ctl'', ''+DATA/TESTDGPHY/controlfile/control02.ctl'' comment= '''' scope=spfile
sql statement: alter system set db_file_name_convert = ''TESTDG'', ''TESTDGPHY'' comment= '''' scope=spfile
sql statement: alter system set log_file_name_convert = ''TESTDG'', ''TESTDGPHY'' comment= '''' scope=spfile
sql statement: alter system set memory_target = 1024M comment= '''' scope=spfile
sql statement: alter system set audit_file_dest = ''/oracle/app/oracle/admin/TESTDGPHY/adump'' comment= '''' scope=spfile
sql statement: alter system set db_create_file_dest = ''+DATA'' comment= '''' scope=spfile
sql statement: alter system set instance_number = 1 comment= '''' scope=spfile
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 1068937216 bytes
Fixed Size 2253216 bytes
Variable Size 608177760 bytes
Database Buffers 452984832 bytes
Redo Buffers 5521408 bytes
contents of Memory Script:
{
backup as copy current controlfile for standby auxiliary format '+DATA/testdgphy/controlfile/crontal01.ctl';
restore clone controlfile to '+DATA/testdgphy/controlfile/control02.ctl' from
'+DATA/testdgphy/controlfile/crontal01.ctl';
}
executing Memory Script
Starting backup at 2016-02-18 14:29:59
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
copying standby control file
output file name=/oracle/app/oracle/product/11.2.0/db/dbs/snapcf_DGPRI1.f tag=TAG20160218T143003 RECID=2 STAMP=904141804
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 2016-02-18 14:30:01
Starting restore at 2016-02-18 14:30:01
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=97 instance=DGPHY1 device type=DISK
channel ORA_AUX_DISK_1: copied control file copy
Finished restore at 2016-02-18 14:30:03
contents of Memory Script:
{
sql clone 'alter database mount standby database';
}
executing Memory Script
sql statement: alter database mount standby database
RMAN-05529: WARNING: DB_FILE_NAME_CONVERT resulted in invalid ASM names; names changed to disk group only.
contents of Memory Script:
{
set newname for tempfile 1 to
"+data";
switch clone tempfile all;
set newname for datafile 1 to
"+data";
set newname for datafile 2 to
"+data";
set newname for datafile 3 to
"+data";
set newname for datafile 4 to
"+data";
set newname for datafile 5 to
"+data";
set newname for datafile 6 to
"+data";
backup as copy reuse
datafile 1 auxiliary format
"+data" datafile
2 auxiliary format
"+data" datafile
3 auxiliary format
"+data" datafile
4 auxiliary format
"+data" datafile
5 auxiliary format
"+data" datafile
6 auxiliary format
"+data" ;
sql 'alter system archive log current';
}
executing Memory Script
executing command: SET NEWNAME
renamed tempfile 1 to +data in control file
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting backup at 2016-02-18 14:30:08
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=+DATA/testdg/datafile/system.293.904043353
output file name=+DATA/testdgphy/datafile/system.456.904141809 tag=TAG20160218T143012
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=+DATA/testdg/datafile/sysaux.292.904043353
output file name=+DATA/testdgphy/datafile/sysaux.457.904141825 tag=TAG20160218T143012
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
input datafile file number=00005 name=+DATA/testdg/datafile/example.301.904043427
output file name=+DATA/testdgphy/datafile/example.458.904141839 tag=TAG20160218T143012
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=+DATA/testdg/datafile/undotbs1.291.904043355
output file name=+DATA/testdgphy/datafile/undotbs1.459.904141847 tag=TAG20160218T143012
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting datafile copy
input datafile file number=00006 name=+DATA/testdg/datafile/undotbs2.302.904043579
output file name=+DATA/testdgphy/datafile/undotbs2.460.904141849 tag=TAG20160218T143012
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=+DATA/testdg/datafile/users.290.904043355
output file name=+DATA/testdgphy/datafile/users.461.904141851 tag=TAG20160218T143012
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 2016-02-18 14:30:51
sql statement: alter system archive log current
contents of Memory Script:
{
backup as copy reuse
archivelog like "/arch/1_11_904043420.dbf" auxiliary format
"/arch/1_11_904043420.dbf" archivelog like
"/arch/1_12_904043420.dbf" auxiliary format
"/arch/1_12_904043420.dbf" archivelog like
"/arch/1_10_904043420.dbf" auxiliary format
"/arch/1_10_904043420.dbf" archivelog like
"/arch/2_5_904043420.dbf" auxiliary format
"/arch/2_5_904043420.dbf" archivelog like
"/arch/2_4_904043420.dbf" auxiliary format
"/arch/2_4_904043420.dbf" ;
catalog clone archivelog "/arch/1_11_904043420.dbf";
catalog clone archivelog "/arch/1_12_904043420.dbf";
catalog clone archivelog "/arch/1_10_904043420.dbf";
catalog clone archivelog "/arch/2_5_904043420.dbf";
catalog clone archivelog "/arch/2_4_904043420.dbf";
switch clone datafile all;
}
executing Memory Script
Starting backup at 2016-02-18 14:30:54
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log copy
input archived log thread=1 sequence=11 RECID=9 STAMP=904141813
output file name=/arch/1_11_904043420.dbf RECID=0 STAMP=0
channel ORA_DISK_1: archived log copy complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting archived log copy
input archived log thread=1 sequence=12 RECID=12 STAMP=904141858
output file name=/arch/1_12_904043420.dbf RECID=0 STAMP=0
channel ORA_DISK_1: archived log copy complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting archived log copy
input archived log thread=1 sequence=10 RECID=8 STAMP=904122715
output file name=/arch/1_10_904043420.dbf RECID=0 STAMP=0
channel ORA_DISK_1: archived log copy complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting archived log copy
input archived log thread=2 sequence=5 RECID=11 STAMP=904141841
output file name=/arch/2_5_904043420.dbf RECID=0 STAMP=0
channel ORA_DISK_1: archived log copy complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting archived log copy
input archived log thread=2 sequence=4 RECID=10 STAMP=904141800
output file name=/arch/2_4_904043420.dbf RECID=0 STAMP=0
channel ORA_DISK_1: archived log copy complete, elapsed time: 00:00:01
Finished backup at 2016-02-18 14:31:02
cataloged archived log
archived log file name=/arch/1_11_904043420.dbf RECID=1 STAMP=904141862
cataloged archived log
archived log file name=/arch/1_12_904043420.dbf RECID=2 STAMP=904141862
cataloged archived log
archived log file name=/arch/1_10_904043420.dbf RECID=3 STAMP=904141862
cataloged archived log
archived log file name=/arch/2_5_904043420.dbf RECID=4 STAMP=904141863
cataloged archived log
archived log file name=/arch/2_4_904043420.dbf RECID=5 STAMP=904141863
datafile 1 switched to datafile copy
input datafile copy RECID=2 STAMP=904141863 file name=+DATA/testdgphy/datafile/system.456.904141809
datafile 2 switched to datafile copy
input datafile copy RECID=3 STAMP=904141863 file name=+DATA/testdgphy/datafile/sysaux.457.904141825
datafile 3 switched to datafile copy
input datafile copy RECID=4 STAMP=904141863 file name=+DATA/testdgphy/datafile/undotbs1.459.904141847
datafile 4 switched to datafile copy
input datafile copy RECID=5 STAMP=904141863 file name=+DATA/testdgphy/datafile/users.461.904141851
datafile 5 switched to datafile copy
input datafile copy RECID=6 STAMP=904141863 file name=+DATA/testdgphy/datafile/example.458.904141839
datafile 6 switched to datafile copy
input datafile copy RECID=7 STAMP=904141863 file name=+DATA/testdgphy/datafile/undotbs2.460.904141849
contents of Memory Script:
{
set until scn 1182684;
recover
standby
clone database
delete archivelog
;
}
executing Memory Script
executing command: SET until clause
Starting recover at 2016-02-18 14:31:03
using channel ORA_AUX_DISK_1
starting media recovery
archived log for thread 1 with sequence 11 is already on disk as file /arch/1_11_904043420.dbf
archived log for thread 1 with sequence 12 is already on disk as file /arch/1_12_904043420.dbf
archived log for thread 2 with sequence 4 is already on disk as file /arch/2_4_904043420.dbf
archived log for thread 2 with sequence 5 is already on disk as file /arch/2_5_904043420.dbf
archived log file name=/arch/1_11_904043420.dbf thread=1 sequence=11
archived log file name=/arch/2_4_904043420.dbf thread=2 sequence=4
archived log file name=/arch/1_12_904043420.dbf thread=1 sequence=12
archived log file name=/arch/2_5_904043420.dbf thread=2 sequence=5
media recovery complete, elapsed time: 00:00:00
Finished recover at 2016-02-18 14:31:06
Finished Duplicate Db at 2016-02-18 14:31:13
RMAN>
创建备库spfile并启动备库
由于备库是rac，我们应该修改spfile到磁盘组，然后才能启动rac的2个节点：
[LHRAXXTDB1:oracle]:/oracle>sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Thu Feb 18 14:36:27 2016
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
SYS@DGPHY1> select open_mode from v$database;
OPEN_MODE
--------------------
MOUNTED
SYS@DGPHY1> show parameter cluster
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
cluster_database boolean TRUE
cluster_database_instances integer 2
cluster_interconnects string
SYS@DGPHY1> show parameter spfile
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /oracle/app/oracle/product/11.
2.0/db/dbs/spfileDGPHY1.ora
SYS@DGPHY1>
SYS@DGPHY1> create pfile='/tmp/aa.txt' from spfile;
File created.
这里修改pfile文件内容修改后的内容如下：
*.audit_file_dest='/oracle/app/oracle/admin/TESTDGPHY/adump'
*.audit_trail='db'
*.cluster_database=true
*.compatible='11.2.0.4.0'
*.control_files='+DATA/TESTDGPHY/controlfile/crontal01.ctl','+DATA/TESTDGPHY/controlfile/control02.ctl'
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_domain=''
*.db_file_name_convert='TESTDG','TESTDGPHY'
*.db_name='TESTDG'
*.db_recovery_file_dest='+DATA'
*.db_recovery_file_dest_size=4621074432
*.db_unique_name='TESTDGPHY'
*.diagnostic_dest='/oracle/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=DGPRIXDB)'
*.fal_client='TESTDGPHY'
*.fal_server='TNS_DGPRI'
*.log_archive_config='DG_CONFIG=(TESTDG,TESTDGPHY)'
*.log_archive_dest_1='LOCATION=/arch valid_for=(ALL_LOGFILES,ALL_ROLES) db_unique_name=TESTDGPHY'
*.log_archive_dest_2='service=TNS_DGPRI async lgwr register valid_for=(online_logfile,primary_role) db_unique_name=TESTDG'
*.log_archive_dest_state_1='ENABLE'
*.log_archive_dest_state_2='ENABLE'
*.log_archive_max_processes=4
*.log_file_name_convert='TESTDG','TESTDGPHY'
*.memory_target=1073741824
*.open_cursors=300
*.pga_aggregate_target=268435456
*.processes=150
*.remote_listener='LHRAXXTDB-scan:1521'
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=805306368
*.standby_file_management='AUTO'
DGPHY2.instance_number=2
DGPHY1.instance_number=1
DGPHY2.thread=2
DGPHY1.thread=1
DGPHY2.undo_tablespace='UNDOTBS2'
DGPHY1.undo_tablespace='UNDOTBS1'
需要关注上边蓝色的部分，如下：
DGPHY2.instance_number=2
DGPHY1.instance_number=1
DGPHY2.thread=2
DGPHY1.thread=1
DGPHY2.undo_tablespace='UNDOTBS2'
DGPHY1.undo_tablespace='UNDOTBS1'
创建spfile文件到磁盘组，并在pfile文件中添加spfile的路径：
SYS@DGPHY1> create spfile='+DATA/TESTDGPHY/PARAMETERFILE/spfiledgphy.ora' from pfile='/tmp/aa.txt';
File created.
SYS@DGPHY1> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
[LHRAXXTDB1:oracle]:/oracle>echo "SPFILE='+DATA/TESTDGPHY/PARAMETERFILE/spfiledgphy.ora'" > $ORACLE_HOME/dbs/initDGPHY1.ora
[LHRAXXTDB1:oracle]:/oracle>more $ORACLE_HOME/dbs/initDGPHY1.ora
SPFILE='+DATA/TESTDGPHY/PARAMETERFILE/spfiledgphy.ora'
[LHRAXXTDB1:oracle]:/oracle>
在第二个节点中，在pfile文件中添加spfile的路径：
[LHRAXXTDB2:oracle]:/oracle>echo "SPFILE='+DATA/TESTDGPHY/PARAMETERFILE/spfiledgphy.ora'" > $ORACLE_HOME/dbs/initDGPHY2.ora
[LHRAXXTDB2:oracle]:/oracle>more $ORACLE_HOME/dbs/initDGPHY2.ora
SPFILE='+DATA/TESTDGPHY/PARAMETERFILE/spfiledgphy.ora'
删除原spfile文件：/oracle/app/oracle/product/11.2.0/db/dbs/spfileDGPHY1.ora
启动2个节点后查看：
SYS@DGPHY1> startup force;
ORACLE instance started.
Total System Global Area 1068937216 bytes
Fixed Size 2253216 bytes
Variable Size 608177760 bytes
Database Buffers 452984832 bytes
Redo Buffers 5521408 bytes
Database mounted.
Database opened.
SYS@DGPHY1> show parameter spfile
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string +DATA/testdgphy/parameterfile/
spfiledgphy.ora
SYS@DGPHY1>
SYS@DGPHY1> set line 9999
SYS@DGPHY1> select name , open_mode, log_mode,force_logging,DATABASE_ROLE,switchover_status from gv$database;
NAME OPEN_MODE LOG_MODE FOR DATABASE_ROLE SWITCHOVER_STATUS
--------- -------------------- ------------ --- ---------------- --------------------
TESTDG READ ONLY ARCHIVELOG YES PHYSICAL STANDBY NOT ALLOWED
TESTDG READ ONLY ARCHIVELOG YES PHYSICAL STANDBY NOT ALLOWED
SYS@DGPHY1>
将备库加入crsctl中
srvctl stop database -d TESTDG -o immediate
srvctl status database -d DGPHY
srvctl start database -d TESTDG -o mount
srvctl add database -d TESTDGPHY -c RAC -o /oracle/app/oracle/product/11.2.0/db -p '+DATA/TESTDGPHY/PARAMETERFILE/spfiledgphy.ora' -r physical_standby -n TESTDG
srvctl add instance -d TESTDGPHY -i DGPHY1 -n LHRAXXTDB1
srvctl add instance -d TESTDGPHY -i DGPHY2 -n LHRAXXTDB2
srvctl status database -d TESTDGPHY
srvctl start database -d TESTDGPHY
srvctl remove database -d TESTDGPHY
srvctl config database -d TESTDGPHY -a
dbca创建的数据库会自动加入crsctl中，但通过rman创建的库不会加入crsctl中，需要手动添加，将备库加入crsctl中后可以通过srvctl来管理数据库了。
[LHRAXXTDB2:oracle]:/oracle>crsctl stat res -t
--------------------------------------------------------------------------------
NAME TARGET STATE SERVER STATE_DETAILS
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.DATA.dg
ONLINE ONLINE LHRAXXTDB1
ONLINE ONLINE LHRAXXTDB2
ora.LISTENER.lsnr
ONLINE ONLINE LHRAXXTDB1
ONLINE ONLINE LHRAXXTDB2
ora.asm
ONLINE ONLINE LHRAXXTDB1 Started
ONLINE ONLINE LHRAXXTDB2 Started
ora.gsd
OFFLINE OFFLINE LHRAXXTDB1
OFFLINE OFFLINE LHRAXXTDB2
ora.net1.network
ONLINE ONLINE LHRAXXTDB1
ONLINE ONLINE LHRAXXTDB2
ora.ons
ONLINE ONLINE LHRAXXTDB1
ONLINE ONLINE LHRAXXTDB2
ora.registry.acfs
ONLINE ONLINE LHRAXXTDB1
ONLINE ONLINE LHRAXXTDB2
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.LISTENER_SCAN1.lsnr
1 ONLINE ONLINE LHRAXXTDB1
ora.cvu
1 ONLINE ONLINE LHRAXXTDB1
ora.oc4j
1 ONLINE ONLINE LHRAXXTDB1
ora.ora2lhr.db
1 ONLINE ONLINE LHRAXXTDB2 Open
ora.oraeskdb.db
1 ONLINE ONLINE LHRAXXTDB1 Open
2 ONLINE ONLINE LHRAXXTDB2 Open
ora.oralhr.db
1 ONLINE ONLINE LHRAXXTDB2 Open
ora.scan1.vip
1 ONLINE ONLINE LHRAXXTDB1
ora.LHRAXXTDB1.vip
1 ONLINE ONLINE LHRAXXTDB1
ora.LHRAXXTDB2.vip
1 ONLINE ONLINE LHRAXXTDB2
[LHRAXXTDB2:oracle]:/oracle>
[LHRAXXTDB2:oracle]:/oracle>
[LHRAXXTDB2:oracle]:/oracle>srvctl add database -h
Adds a database configuration to the Oracle Clusterware.
Usage: srvctl add database -d <db_unique_name> -o <oracle_home> [-c {RACONENODE | RAC | SINGLE} [-e <server_list>] [-i <inst_name>] [-w <timeout>]] [-m <domain_name>] [-p <spfile>] [-r {PRIMARY | PHYSICAL_STANDBY | LOGICAL_STANDBY | SNAPSHOT_STANDBY}] [-s <start_options>] [-t <stop_options>] [-n <db_name>] [-y {AUTOMATIC | MANUAL | NORESTART}] [-g "<serverpool_list>"] [-x <node_name>] [-a "<diskgroup_list>"] [-j "<acfs_path_list>"]
-d <db_unique_name> Unique name for the database
-o <oracle_home> ORACLE_HOME path
-c <type> Type of database: RAC One Node, RAC, or Single Instance
-e <server_list> Candidate server list for RAC One Node database
-i <inst_name> Instance name prefix for administrator-managed RAC One Node database (default first 12 characters of <db_unique_name>)
-w <timeout> Online relocation timeout in minutes
-x <node_name> Node name. -x option is specified for single-instance databases
-m <domain> Domain for database. Must be set if database has DB_DOMAIN set.
-p <spfile> Server parameter file path
-r <role> Role of the database (primary, physical_standby, logical_standby, snapshot_standby)
-s <start_options> Startup options for the database. Examples of startup options are OPEN, MOUNT, or 'READ ONLY'.
-t <stop_options> Stop options for the database. Examples of shutdown options are NORMAL, TRANSACTIONAL, IMMEDIATE, or ABORT.
-n <db_name> Database name (DB_NAME), if different from the unique name given by the -d option
-y <dbpolicy> Management policy for the database (AUTOMATIC, MANUAL, or NORESTART)
-g "<serverpool_list>" Comma separated list of database server pool names
-a "<diskgroup_list>" Comma separated list of disk groups
-j "<acfs_path_list>" Comma separated list of ACFS paths where database's dependency will be set
-h Print usage
[LHRAXXTDB2:oracle]:/oracle>echo $ORACLE_HOME
/oracle/app/oracle/product/11.2.0/db
[LHRAXXTDB2:oracle]:/oracle>srvctl add database -d TESTDGPHY -c RAC -o /oracle/app/oracle/product/11.2.0/db -p '+DATA/TESTDGPHY/PARAMETERFILE/spfiledgphy.ora' -r physical_standby -n TESTDG -i DGPHY
[LHRAXXTDB2:oracle]:/oracle>
[LHRAXXTDB2:oracle]:/oracle>srvctl add instance -d TESTDGPHY -i DGPHY1 -n LHRAXXTDB1
[LHRAXXTDB2:oracle]:/oracle>srvctl add instance -d TESTDGPHY -i DGPHY2 -n LHRAXXTDB2
[LHRAXXTDB2:oracle]:/oracle>srvctl status database -d TESTDGPHY
Instance DGPHY1 is not running on node LHRAXXTDB1
Instance DGPHY2 is not running on node LHRAXXTDB2
[LHRAXXTDB2:oracle]:/oracle>srvctl start database -d TESTDGPHY
[LHRAXXTDB2:oracle]:/oracle>srvctl status database -d TESTDGPHY
Instance DGPHY1 is running on node LHRAXXTDB1
Instance DGPHY2 is running on node LHRAXXTDB2
[LHRAXXTDB2:oracle]:/oracle>srvctl config database -d TESTDGPHY -a
Database unique name: TESTDGPHY
Database name: TESTDG
Oracle home: /oracle/app/oracle/product/11.2.0/db
Oracle user: oracle
Spfile: +DATA/TESTDGPHY/PARAMETERFILE/spfiledgphy.ora
Domain:
Start options: open
Stop options: immediate
Database role: PHYSICAL_STANDBY
Management policy: AUTOMATIC
Server pools: DGPHY
Database instances: DGPHY1,DGPHY2
Disk Groups:
Mount point paths:
Services:
Type: RAC
Database is enabled
Database is administrator managed
[LHRAXXTDB2:oracle]:/oracle>
[LHRAXXTDB2:root]:/>crsctl stat res -t
--------------------------------------------------------------------------------
NAME TARGET STATE SERVER STATE_DETAILS
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.DATA.dg
ONLINE ONLINE LHRAXXTDB1
ONLINE ONLINE LHRAXXTDB2
ora.LISTENER.lsnr
ONLINE ONLINE LHRAXXTDB1
ONLINE ONLINE LHRAXXTDB2
ora.asm
ONLINE ONLINE LHRAXXTDB1 Started
ONLINE ONLINE LHRAXXTDB2 Started
ora.gsd
OFFLINE OFFLINE LHRAXXTDB1
OFFLINE OFFLINE LHRAXXTDB2
ora.net1.network
ONLINE ONLINE LHRAXXTDB1
ONLINE ONLINE LHRAXXTDB2
ora.ons
ONLINE ONLINE LHRAXXTDB1
ONLINE ONLINE LHRAXXTDB2
ora.registry.acfs
ONLINE ONLINE LHRAXXTDB1
ONLINE ONLINE LHRAXXTDB2
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.LISTENER_SCAN1.lsnr
1 ONLINE ONLINE LHRAXXTDB1
ora.cvu
1 ONLINE ONLINE LHRAXXTDB1
ora.oc4j
1 ONLINE ONLINE LHRAXXTDB1
ora.ora2lhr.db
1 ONLINE ONLINE LHRAXXTDB2 Open
ora.oraeskdb.db
1 ONLINE ONLINE LHRAXXTDB1 Open
2 ONLINE ONLINE LHRAXXTDB2 Open
ora.oralhr.db
1 ONLINE ONLINE LHRAXXTDB2 Open
ora.scan1.vip
1 ONLINE ONLINE LHRAXXTDB1
ora.testdgphy.db
1 ONLINE ONLINE LHRAXXTDB1 Open,Readonly
2 ONLINE ONLINE LHRAXXTDB2 Open,Readonly
ora.LHRAXXTDB1.vip
1 ONLINE ONLINE LHRAXXTDB1
ora.LHRAXXTDB2.vip
1 ONLINE ONLINE LHRAXXTDB2
[LHRAXXTDB2:root]:/>
启动备库到open read only状态
[LHRAXXTDB1:oracle]:/oracle>sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Thu Feb 18 16:16:32 2016
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
SYS@DGPHY1> set line 9999
SYS@DGPHY1> select GROUP#,THREAD#,SEQUENCE#,BYTES,BLOCKSIZE,MEMBERS,STATUS from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS STATUS
---------- ---------- ---------- ---------- ---------- ---------- ----------------
1 1 17 52428800 512 2 CLEARING
2 1 18 52428800 512 2 CURRENT
3 2 13 52428800 512 2 CURRENT
4 2 12 52428800 512 2 CLEARING
SYS@DGPHY1>
SYS@DGPHY1> set line 9999
SYS@DGPHY1> select dbid,name,current_scn,protection_mode,database_role,force_logging,open_mode,switchover_status from gv$database;
DBID NAME CURRENT_SCN PROTECTION_MODE DATABASE_ROLE FOR OPEN_MODE SWITCHOVER_STATUS
---------- --------- ----------- -------------------- ---------------- --- -------------------- --------------------
2836886746 TESTDG 1182683 MAXIMUM PERFORMANCE PHYSICAL STANDBY YES READ ONLY NOT ALLOWED
2836886746 TESTDG 1182683 MAXIMUM PERFORMANCE PHYSICAL STANDBY YES READ ONLY NOT ALLOWED
SYS@DGPHY1>
SYS@DGPHY1> select GROUP#,DBID,THREAD#,SEQUENCE#,BYTES,BLOCKSIZE,USED,ARCHIVED,STATUS from v$standby_log;
GROUP# DBID THREAD# SEQUENCE# BYTES BLOCKSIZE USED ARC STATUS
---------- ---------------------------------------- ---------- ---------- ---------- ---------- ---------- --- ----------
5 2836886746 1 18 52428800 512 1556992 YES ACTIVE
6 UNASSIGNED 1 0 52428800 512 0 YES UNASSIGNED
7 UNASSIGNED 1 0 52428800 512 0 YES UNASSIGNED
8 UNASSIGNED 2 0 52428800 512 0 NO UNASSIGNED
9 2836886746 2 13 52428800 512 1239040 YES ACTIVE
10 UNASSIGNED 2 0 52428800 512 0 YES UNASSIGNED
6 rows selected.
SYS@DGPHY1>
SYS@DGPHY1>
校验实时同步功能
为了实时查询，启用管理恢复
SYS@DGPHY1> alter database recover managed standby database using current logfile disconnect;
Database altered.
SYS@DGPHY1> select INST_ID, dbid,name,DB_UNIQUE_NAME,current_scn,protection_mode,database_role,force_logging,open_mode,switchover_status from gv$database;
INST_ID DBID NAME DB_UNIQUE_NAME CURRENT_SCN PROTECTION_MODE DATABASE_ROLE FOR OPEN_MODE SWITCHOVER_STATUS
---------- ---------- --------- --------------- ----------- -------------------- ---------------- --- -------------------- --------------------
1 2836886746 TESTDG TESTDGPHY 1219930 MAXIMUM PERFORMANCE PHYSICAL STANDBY YES READ ONLY WITH APPLY NOT ALLOWED
2 2836886746 TESTDG TESTDGPHY 1219930 MAXIMUM PERFORMANCE PHYSICAL STANDBY YES READ ONLY WITH APPLY NOT ALLOWED
SYS@DGPHY1>
SYS@DGPHY1>
SYS@DGPHY1> col name for a100
SYS@DGPHY1> set linesize 9999 pagesize 9999
SYS@DGPHY1> SELECT dest_id,
2 THREAD#,
3 NAME,
4 sequence#,
5 archived,
6 applied,
7 a.NEXT_CHANGE#
8 FROM v$archived_log a
9 WHERE a.sequence# >= 12
10 AND resetlogs_change# = (SELECT d.RESETLOGS_CHANGE# FROM v$database d)
11 ORDER BY a.THREAD#,
12 a.sequence#,
13 a.dest_id;
DEST_ID THREAD# NAME SEQUENCE# ARC APPLIED NEXT_CHANGE#
---------- ---------- ---------------------------------- --------- --- --------- ------------
2 1 /arch/1_13_904043420.dbf 13 YES YES 1206723
2 1 /arch/1_14_904043420.dbf 14 YES YES 1206725
2 1 /arch/1_15_904043420.dbf 15 YES YES 1213882
2 1 /arch/1_16_904043420.dbf 16 YES YES 1214395
2 1 /arch/1_17_904043420.dbf 17 YES YES 1218112
1 2 /arch/2_12_904043420.dbf 12 YES IN-MEMORY 1218115
7 rows selected.
SYS@DGPHY1>
主库切换日志，建表测试：
SYS@DGPRI1> ALTER SYSTEM SWITCH LOGFILE;
System altered.
SYS@DGPRI1> ALTER SYSTEM SWITCH LOGFILE;
System altered.
SYS@DGPRI1> ALTER SYSTEM SWITCH LOGFILE;
System altered.
SYS@DGPRI1>
SYS@DGPRI1> create table lhr.test as select * from user_tables;
Table created.
SYS@DGPRI1> select count(1) from lhr.test;
COUNT(1)
----------
1014
SYS@DGPRI1>
在备库端查询日志应用情况
SYS@DGPHY1> col name for a100
SYS@DGPHY1> set linesize 9999 pagesize 9999
SYS@DGPHY1> SELECT dest_id,
2 THREAD#,
3 NAME,
4 sequence#,
5 archived,
6 applied,
7 a.NEXT_CHANGE#
8 FROM v$archived_log a
9 WHERE a.sequence# >= 12
10 AND resetlogs_change# = (SELECT d.RESETLOGS_CHANGE# FROM v$database d)
11 ORDER BY a.THREAD#,
12 a.sequence#,
13 a.dest_id;
DEST_ID THREAD# NAME SEQUENCE# ARC APPLIED NEXT_CHANGE#
---------- ---------- -------------------------------------------------------- ---------- --- --------- ------------
1 1 /arch/1_12_904043420.dbf 12 YES YES 1182687
2 1 /arch/1_13_904043420.dbf 13 YES YES 1206723
2 1 /arch/1_14_904043420.dbf 14 YES YES 1206725
2 1 /arch/1_15_904043420.dbf 15 YES YES 1213882
2 1 /arch/1_16_904043420.dbf 16 YES YES 1214395
2 1 /arch/1_17_904043420.dbf 17 YES YES 1218112
1 1 /arch/1_18_904043420.dbf 18 YES YES 1221758
1 1 /arch/1_19_904043420.dbf 19 YES YES 1221764
1 1 /arch/1_20_904043420.dbf 20 YES YES 1221770
1 2 /arch/2_12_904043420.dbf 12 YES YES 1218115
1 2 /arch/2_13_904043420.dbf 13 YES IN-MEMORY 1221775
11 rows selected.
SYS@DGPHY1> select count(1) from lhr.test;
COUNT(1)
----------
1014
SYS@DGPHY1>
可以看到数据已经实时同步了，至此，物理备库搭建完成，至于备库的维护操作还有很多内容，参考我的其它blog。
实验总结
实验过程中需要注意的几项内容：
主库修改参数一定要加 sid='*'
监听必须配置好
执行duplicate命令的时候，如果是rac库需要添加参数：set instance_number = '1'，否则报错：
RMAN-03015: error occurred in stored script Memory Script
RMAN-04014: startup failed: ORA-29760: instance_number parameter not specified
本文参与 腾讯云自媒体同步曝光计划，分享自微信公众号。
原始发表：2022-11-23，如有侵权请联系 cloudcommunity@tencent.com 删除
评论
登录后参与评论
推荐阅读
腾讯云开发者
