Focus On Oracle

Installing, Backup & Recovery, Performance Tuning,
Troubleshooting, Upgrading, Patching, Zero-Downtime Upgrade, GoldenGate

Oracle Exadata ,Oracle ODA, Oracle ZDLRA


当前位置： 首页 » 技术文章 » Oracle

如何创建11gR2 Physical Standby(RAC to RAC )

时间: 2016-11-28 21:53:44  | 作者: ohsdba  | 
如非注明，本站文章皆为原创。欢迎转载，转载时请注明出处和作者信息。

Oracle最高可用性体系结构 (MAA)是Oracle 的最佳实践方案，它基于成熟的Oracle高可用性技术和建议。MAA 的目的是以最低的成本和复杂性获得最佳的高可用性体系结构。本文通过11gR2 RAC创建物理备库来实现高可用性，也是MAA的一种体现。


Oracle Maximum Availability Architecture




配置信息一览表



注意：阅读本文之前，需满足以下条件

         对11g RAC和DataGuard有一定的了解

         安装和配置过11gR2 RAC

         对SCAN有一定了解（本文使用了DNS）

         主库已经创建好

         备库（ASM磁盘中已配置好，数据库软件已安装好）

/etc/hosts配置信息 

10.0.2.10        od.ohsdba.cn #dns dhcp ntp server
10.0.2.21        ohs1.ohsdba.cn ohs1 # public address of node 1
10.0.2.22        ohs2.ohsdba.cn ohs2 # public address of node 2
10.0.2.23        ohs3.ohsdba.cn ohs3 # public address of node 3
10.0.2.24        ohs4.ohsdba.cn ohs4 # public address of node 4

10.0.2.31        ohs1-vip.ohsdba.cn ohs1-vip # virtual address of node 1
10.0.2.32        ohs2-vip.ohsdba.cn ohs2-vip # virtual address of node 2
10.0.2.33        ohs3-vip.ohsdba.cn ohs3-vip # virtual address of node 3
10.0.2.34        ohs4-vip.ohsdba.cn ohs4-vip # virtual address of node 4

172.16.0.21      ohs1-priv.ohsdba.cn ohs1-priv   # private address of node 1
172.16.0.22      ohs2-priv.ohsdba.cn ohs2-priv   # private address of node 2
172.16.0.23      ohs3-priv.ohsdba.cn ohs3-priv   # private address of node 3
172.16.0.24      ohs4-priv.ohsdba.cn ohs4-priv   # private address of node 4
SCAN配置信息 
[orgrid@ohs1 ~]$ nslookup prod-scan
Server:         10.0.2.10
Address:        10.0.2.10#53

Name:   prod-scan.ohsdba.cn
Address: 10.0.2.112
Name:   prod-scan.ohsdba.cn
Address: 10.0.2.110
Name:   prod-scan.ohsdba.cn
Address: 10.0.2.111

[orgrid@ohs1 ~]$ nslookup stdby-scan
Server:         10.0.2.10
Address:        10.0.2.10#53

Name:   stdby-scan.ohsdba.cn
Address: 10.0.2.211
Name:   stdby-scan.ohsdba.cn
Address: 10.0.2.212
Name:   stdby-scan.ohsdba.cn
Address: 10.0.2.210
[orgrid@ohs1 ~]$

主、备库TNS配置信息 

PROD =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = prod-scan.ohsdba.cn)(PORT = 10010))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = prod)
    )
  )

STDBY =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = stdby-scan.ohsdba.cn)(PORT = 10015))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = stdby)
    )
  )

在主库上的操作

1.设置初始化参数 

alter system set log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST' scope=spfile sid='*';
alter
 system set log_archive_dest_2='service=STDBY 
valid_for=(online_logfiles,primary_role) LGWR ASYNC=20480 OPTIONAL 
REOPEN=15 NET_TIMEOUT=30 db_unique_name=STDBY' scope=spfile sid='*';
alter system set log_archive_dest_state_1 = enable scope=spfile sid='*';
alter system set log_archive_dest_state_2 = enable scope=spfile sid='*';
alter system set log_archive_config='dg_config=(PROD,STDBY)' scope=spfile sid='*';
alter system set fal_server=PROD scope=spfile sid='*';
alter system set standby_file_management=auto scope=spfile sid='*';
#以下设置为磁盘组不一样做的设置，如果一样，以下参数可以不设置
alter system set db_file_name_convert ='+DATA_STDBY/stdby','+DATA_PROD/prod' scope=spfile sid='*';
alter system set log_file_name_convert ='+DATA_STDBY/stdby','+DATA_PROD/prod' scope=spfile sid='*';
alter system set db_create_online_log_dest_1='+DATA_PROD' scope=spfile sid='*';
alter system set db_create_file_dest='+DATA_PROD' scope=spfile sid='*';
alter system set db_recovery_file_dest='+FRA_PROD' scope=spfile sid='*';


SQL> alter system set log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST' scope=spfile sid='*';
System altered.

SQL> alter system set log_archive_dest_2='service=STDBY valid_for=(online_logfiles,primary_role) LGWR ASYNC=20480 OPTIONAL REOPEN=15 NET_TIMEOUT=30 db_unique_name=STDBY' scope=spfile sid='*';
System altered.

SQL> alter system set log_archive_dest_state_1 = enable scope=spfile sid='*';
System altered.

SQL> alter system set log_archive_dest_state_2 = enable scope=spfile sid='*';
System altered.

SQL> alter system set log_archive_config='dg_config=(PROD,STDBY)' scope=spfile sid='*';
System altered.

SQL> alter system set fal_server=PROD scope=spfile sid='*';
System altered.

SQL> alter system set standby_file_management=auto scope=spfile sid='*';
System altered.

SQL> 
SQL> alter system set db_file_name_convert ='+DATA_STDBY/stdby','+DATA_PROD/prod' scope=spfile sid='*';   
System altered.

SQL> alter system set log_file_name_convert ='+DATA_STDBY/stdby','+DATA_PROD/prod' scope=spfile sid='*';
System altered.

SQL> alter system set db_create_online_log_dest_1='+DATA_PROD' scope=spfile sid='*';
System altered.

SQL> alter system set db_create_file_dest='+DATA_PROD' scope=spfile sid='*';
System altered.

SQL> alter system set db_recovery_file_dest='+FRA_PROD' scope=spfile sid='*';
System altered.
SQL>

2.在主库上增加standby logfile,设置force logging 

SQL> alter database force logging;
Database altered.
SQL> 
SQL> select thread#,group#,bytes/1024/1024 from v$log;

   THREAD#     GROUP# BYTES/1024/1024
---------- ---------- ---------------
         1          1              50
         1          2              50
         2          3              50
         2          4              50
SQL> 

SQL> alter database add logfile thread 1 group 5 size 50M;
Database altered.

SQL> alter database add logfile thread 2 group 6 size 50M;
Database altered.

SQL> alter database add standby logfile thread 1
  2  group 7 size 50M,
  3  group 8 size 50M,
  4  group 9 size 50M,
  5  group 10 size 50M;
Database altered.

SQL> alter database add standby logfile thread 2
  2  group 11 size 50M,
  3  group 12 size 50M,
  4  group 13 size 50M,
  5  group 14 size 50M;
Database altered.
SQL>
3.在主库上增加到备库的TNS信息 
dup =
 (DESCRIPTION =
  (ADDRESS = (PROTOCOL = TCP) (HOST = ohs3.ohsdba.cn) (PORT = 1521))
   (CONNECT_DATA =
     (SERVER = DEDICATED)
     (SID = stdby1)
   )
 )


在备库上做以下操作

1.配置静态监听（在备库第一个节点增加下面的内容）

$GI_HOME/network/admin/listener.ora 

SID_LIST_LISTENER =
 (SID_LIST =
  (SID_DESC =
    (ORACLE_HOME = /pgold/ordb/oracle/product/112)
    (SID_NAME = stdby1)
   )
 )
2.在ASM实例上创建目录 
ASMCMD> pwd
+DATA_STDBY
ASMCMD> mkdir stdby
ASMCMD> ls -l
Type  Redund  Striped  Time             Sys  Name
                                        N    stdby/
ASMCMD>

3.创建审核文件存放目录（在所有备库节点执行） 

[oracle@ohs3 ~]mkdir -p /pgold/ordb/oracle/product/admin/stdby/adump
[oracle@ohs4 ~]mkdir -p /pgold/ordb/oracle/product/admin/stdby/adump

4.创建密码文件 

[oracle@ohs3 dbs]$ orapwd file=orapwstdby1 password=oracle
在备库第一个节点创建密码文件，然后复制到其他节点，并修改文件名为orapw<ORACLE_SID>的格式。如果遇到ORA-16191的情况，最好把主库上的密码文件复制到备库，修改文件名为orapw<ORACLE_SID> 的格式。

5.用setasmgidwrap修改权限 

[oracle@ohs3 112]$ ls -l /pgold/ordb/oracle/product/112/bin/oracle
-rwsr-s--x. 1 oracle oinstall 239626641 Nov 26 09:54 /pgold/ordb/oracle/product/112/bin/oracle
[oracle@ohs3 112]$ /pgold/orgrid/oracle/product/112/bin/setasmgidwrap -o=/pgold/ordb/oracle/product/112/bin/oracle
[oracle@ohs3 112]$ ls -l /pgold/ordb/oracle/product/112/bin/oracle
-rwsr-s--x. 1 oracle asmadmin 239626641 Nov 26 09:54 /pgold/ordb/oracle/product/112/bin/oracle
[oracle@ohs3 112]$
[oracle@ohs4 log]$ ls -l /pgold/ordb/oracle/product/112/bin/oracle
-rwsr-s--x. 1 oracle oinstall 239626641 Nov 26 09:58 /pgold/ordb/oracle/product/112/bin/oracle
[oracle@ohs4 log]$ cd
[oracle@ohs4 ~]$ /pgold/orgrid/oracle/product/112/bin/setasmgidwrap -o=/pgold/ordb/oracle/product/112/bin/oracle
[oracle@ohs4 ~]$ ls -l /pgold/ordb/oracle/product/112/bin/oracle
-rwsr-s--x. 1 oracle asmadmin 239626641 Nov 26 09:58 /pgold/ordb/oracle/product/112/bin/oracle
[oracle@ohs4 ~]$

6.在备库第一个节点上编辑临时pfile 

[oracle@ohs3 dbs]$ pwd
/pgold/ordb/oracle/product/112/dbs
[oracle@ohs3 dbs]$ cat pfile.ora
*.db_name=prod
*.sga_target=500M
*.db_unique_name=stdby
[oracle@ohs3 dbs]$

注意：这里的pfile只起临时作用，执行duplicate时会把主库的spfile clone到备库，然后以clone的spfile重新启动实例


通过rman duplicate创建physical standby

1.在备库的第一个实例上启动实例到nomount状态 

[oracle@ohs3 dbs]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Sun Nov 27 10:19:55 2016
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
Connected to an idle instance.
SQL> startup nomount pfile='pfile.ora'
ORACLE instance started.
Total System Global Area  521936896 bytes
Fixed Size                  2254824 bytes
Variable Size             159385624 bytes
Database Buffers          356515840 bytes
Redo Buffers                3780608 bytes
SQL>
2.在主库上执行

[oracle@ohs1 ~]$ rman target / auxiliary sys/oracle@dup |tee /tmp/rman1.log 

run {
allocate channel c1 device type disk ;
allocate channel c2 device type disk ;
allocate auxiliary channel aux1 device type disk ;
duplicate target database for standby from active database
spfile
parameter_value_convert 'prod','stdby','PROD','STDBY'
set cluster_database='false'
set db_unique_name='stdby'
set log_archive_dest_2='service=PROD valid_for=(online_logfiles,primary_role) LGWR ASYNC=20480 OPTIONAL REOPEN=15 NET_TIMEOUT=30 db_unique_name=PROD'
set db_file_name_convert '+DATA_PROD/prod','+DATA_STDBY/stdby'
set log_file_name_convert '+DATA_PROD/prod','+DATA_STDBY/stdby'
set db_create_online_log_dest_1='+DATA_STDBY'
set db_create_file_dest='+DATA_STDBY'
set db_recovery_file_dest='+FRA_STDBY'
set control_files='+DATA_STDBY/stdby/control01.ctl','+DATA_STDBY/stdby/control02.ctl'
set local_listener='(ADDRESS=(PROTOCOL=tcp)(HOST=ohs3-vip.ohsdba.cn)(PORT=1521))'
set remote_listener='stdby-scan:10015'
set audit_file_dest='/pgold/ordb/oracle/product/admin/stdby/adump';
}


[oracle@ohs1 admin]$ rman target / auxiliary sys/oracle@dup |tee /tmp/rman1.log
Recovery Manager: Release 11.2.0.4.0 - Production on Tue Nov 29 03:05:35 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: PROD (DBID=340761128)
connected to auxiliary database: PROD (not mounted)

RMAN> run {
2> allocate channel c1 device type disk ;
3> allocate channel c2 device type disk ;
4> allocate auxiliary channel aux1 device type disk ;
5> duplicate target database for standby from active database
6> spfile
7> parameter_value_convert 'prod','stdby','PROD','STDBY'
8> set cluster_database='false'
9> set db_unique_name='stdby'
10> set log_archive_dest_2='service=PROD valid_for=(online_logfiles,primary_role) LGWR ASYNC=20480 OPTIONAL REOPEN=15 NET_TIMEOUT=30 db_unique_name=PROD'
11> set db_file_name_convert '+DATA_PROD/prod','+DATA_STDBY/stdby'
12> set log_file_name_convert '+DATA_PROD/prod','+DATA_STDBY/stdby'
13> set db_create_online_log_dest_1='+DATA_STDBY'
14> set db_create_file_dest='+DATA_STDBY'
15> set db_recovery_file_dest='+FRA_STDBY'
16> set control_files='+DATA_STDBY/stdby/control01.ctl','+DATA_STDBY/stdby/control02.ctl'
17> set local_listener='(ADDRESS=(PROTOCOL=tcp)(HOST=ohs3-vip.ohsdba.cn)(PORT=1521))'
18> set remote_listener='stdby-scan:10015'
19> set audit_file_dest='/pgold/ordb/oracle/product/admin/stdby/adump';
20> }

using target database control file instead of recovery catalog
allocated channel: c1
channel c1: SID=56 instance=prod1 device type=DISK

allocated channel: c2
channel c2: SID=55 instance=prod1 device type=DISK

allocated channel: aux1
channel aux1: SID=26 device type=DISK

Starting Duplicate Db at 29-NOV-16

contents of Memory Script:
{
   backup as copy reuse
   targetfile  '/pgold/ordb/oracle/product/112/dbs/orapwprod1' auxiliary format 
 '/pgold/ordb/oracle/product/112/dbs/orapwstdby1'   targetfile 
 '+DATA_PROD/prod/spfileprod.ora' auxiliary format 
 '/pgold/ordb/oracle/product/112/dbs/spfilestdby1.ora'   ;
   sql clone "alter system set spfile= ''/pgold/ordb/oracle/product/112/dbs/spfilestdby1.ora''";
}
executing Memory Script

Starting backup at 29-NOV-16
Finished backup at 29-NOV-16

sql statement: alter system set spfile= ''/pgold/ordb/oracle/product/112/dbs/spfilestdby1.ora''

contents of Memory Script:
{
   sql clone "alter system set  dispatchers = 
 ''(PROTOCOL=TCP) (SERVICE=stdbyXDB)'' comment=
 '''' scope=spfile";
   sql clone "alter system set  cluster_database = 
 false comment=
 '''' scope=spfile";
   sql clone "alter system set  db_unique_name = 
 ''stdby'' comment=
 '''' scope=spfile";
   sql clone "alter system set  log_archive_dest_2 = 
 ''service=PROD valid_for=(online_logfiles,primary_role) LGWR ASYNC=20480 OPTIONAL REOPEN=15 NET_TIMEOUT=30 db_unique_name=PROD'' comment=
 '''' scope=spfile";
   sql clone "alter system set  db_file_name_convert = 
 ''+DATA_PROD/prod'', ''+DATA_STDBY/stdby'' comment=
 '''' scope=spfile";
   sql clone "alter system set  log_file_name_convert = 
 ''+DATA_PROD/prod'', ''+DATA_STDBY/stdby'' comment=
 '''' scope=spfile";
   sql clone "alter system set  db_create_online_log_dest_1 = 
 ''+DATA_STDBY'' comment=
 '''' scope=spfile";
   sql clone "alter system set  db_create_file_dest = 
 ''+DATA_STDBY'' comment=
 '''' scope=spfile";
   sql clone "alter system set  db_recovery_file_dest = 
 ''+FRA_STDBY'' comment=
 '''' scope=spfile";
   sql clone "alter system set  control_files = 
 ''+DATA_STDBY/stdby/control01.ctl'', ''+DATA_STDBY/stdby/control02.ctl'' comment=
 '''' scope=spfile";
   sql clone "alter system set  local_listener = 
 ''(ADDRESS=(PROTOCOL=tcp)(HOST=ohs3-vip.ohsdba.cn)(PORT=1521))'' comment=
 '''' scope=spfile";
   sql clone "alter system set  remote_listener = 
 ''stdby-scan:10015'' comment=
 '''' scope=spfile";
   sql clone "alter system set  audit_file_dest = 
 ''/pgold/ordb/oracle/product/admin/stdby/adump'' comment=
 '''' scope=spfile";
   shutdown clone immediate;
   startup clone nomount;
}
executing Memory Script

sql statement: alter system set  dispatchers =  ''(PROTOCOL=TCP) (SERVICE=stdbyXDB)'' comment= '''' scope=spfile

sql statement: alter system set  cluster_database =  false comment= '''' scope=spfile

sql statement: alter system set  db_unique_name =  ''stdby'' comment= '''' scope=spfile

sql statement: alter system set  log_archive_dest_2 =  ''service=PROD valid_for=(online_logfiles,primary_role) LGWR ASYNC=20480 OPTIONAL REOPEN=15 NET_TIMEOUT=30 db_unique_name=PROD'' comment= '''' scope=spfile

sql statement: alter system set  db_file_name_convert =  ''+DATA_PROD/prod'', ''+DATA_STDBY/stdby'' comment= '''' scope=spfile

sql statement: alter system set  log_file_name_convert =  ''+DATA_PROD/prod'', ''+DATA_STDBY/stdby'' comment= '''' scope=spfile

sql statement: alter system set  db_create_online_log_dest_1 =  ''+DATA_STDBY'' comment= '''' scope=spfile

sql statement: alter system set  db_create_file_dest =  ''+DATA_STDBY'' comment= '''' scope=spfile

sql statement: alter system set  db_recovery_file_dest =  ''+FRA_STDBY'' comment= '''' scope=spfile

sql statement: alter system set  control_files =  ''+DATA_STDBY/stdby/control01.ctl'', ''+DATA_STDBY/stdby/control02.ctl'' comment= '''' scope=spfile

sql statement: alter system set  local_listener =  ''(ADDRESS=(PROTOCOL=tcp)(HOST=ohs3-vip.ohsdba.cn)(PORT=1521))'' comment= '''' scope=spfile

sql statement: alter system set  remote_listener =  ''stdby-scan:10015'' comment= '''' scope=spfile

sql statement: alter system set  audit_file_dest =  ''/pgold/ordb/oracle/product/admin/stdby/adump'' comment= '''' scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area     784998400 bytes

Fixed Size                     2257352 bytes
Variable Size                272633400 bytes
Database Buffers             507510784 bytes
Redo Buffers                   2596864 bytes
allocated channel: aux1
channel aux1: SID=1 device type=DISK

contents of Memory Script:
{
   backup as copy current controlfile for standby auxiliary format  '+DATA_STDBY/stdby/control01.ctl';
   restore clone controlfile to  '+DATA_STDBY/stdby/control02.ctl' from 
 '+DATA_STDBY/stdby/control01.ctl';
}
executing Memory Script

Starting backup at 29-NOV-16
channel c1: starting datafile copy
copying standby control file
output file name=/pgold/ordb/oracle/product/112/dbs/snapcf_prod1.f tag=TAG20161129T030611 RECID=23 STAMP=929156772
channel c1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 29-NOV-16

Starting restore at 29-NOV-16

channel aux1: copied control file copy
Finished restore at 29-NOV-16

contents of Memory Script:
{
   sql clone 'alter database mount standby database';
}
executing Memory Script

sql statement: alter database mount standby database

contents of Memory Script:
{
   set newname for tempfile  1 to 
 "+DATA_STDBY/stdby/temp01.dbf";
   switch clone tempfile all;
   set newname for datafile  1 to 
 "+DATA_STDBY/stdby/system01.dbf";
   set newname for datafile  2 to 
 "+DATA_STDBY/stdby/sysaux01.dbf";
   set newname for datafile  3 to 
 "+DATA_STDBY/stdby/undotbs01.dbf";
   set newname for datafile  4 to 
 "+DATA_STDBY/stdby/users01.dbf";
   set newname for datafile  5 to 
 "+DATA_STDBY/stdby/undotbs02.dbf";
   backup as copy reuse
   datafile  1 auxiliary format 
 "+DATA_STDBY/stdby/system01.dbf"   datafile 
 2 auxiliary format 
 "+DATA_STDBY/stdby/sysaux01.dbf"   datafile 
 3 auxiliary format 
 "+DATA_STDBY/stdby/undotbs01.dbf"   datafile 
 4 auxiliary format 
 "+DATA_STDBY/stdby/users01.dbf"   datafile 
 5 auxiliary format 
 "+DATA_STDBY/stdby/undotbs02.dbf"   ;
   sql 'alter system archive log current';
}
executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to +DATA_STDBY/stdby/temp01.dbf in control file

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting backup at 29-NOV-16
channel c1: starting datafile copy
input datafile file number=00001 name=+DATA_PROD/prod/system01.dbf
channel c2: starting datafile copy
input datafile file number=00002 name=+DATA_PROD/prod/sysaux01.dbf
output file name=+DATA_STDBY/stdby/system01.dbf tag=TAG20161129T030620
channel c1: datafile copy complete, elapsed time: 00:00:15
channel c1: starting datafile copy
input datafile file number=00003 name=+DATA_PROD/prod/undotbs01.dbf
output file name=+DATA_STDBY/stdby/sysaux01.dbf tag=TAG20161129T030620
channel c2: datafile copy complete, elapsed time: 00:00:15
channel c2: starting datafile copy
input datafile file number=00005 name=+DATA_PROD/prod/undotbs02.dbf
output file name=+DATA_STDBY/stdby/undotbs01.dbf tag=TAG20161129T030620
channel c1: datafile copy complete, elapsed time: 00:00:02
channel c1: starting datafile copy
input datafile file number=00004 name=+DATA_PROD/prod/users01.dbf
output file name=+DATA_STDBY/stdby/undotbs02.dbf tag=TAG20161129T030620
channel c2: datafile copy complete, elapsed time: 00:00:01
output file name=+DATA_STDBY/stdby/users01.dbf tag=TAG20161129T030620
channel c1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 29-NOV-16

sql statement: alter system archive log current

contents of Memory Script:
{
   switch clone datafile all;
}
executing Memory Script

datafile 1 switched to datafile copy
input datafile copy RECID=23 STAMP=929156803 file name=+DATA_STDBY/stdby/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=24 STAMP=929156803 file name=+DATA_STDBY/stdby/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=25 STAMP=929156803 file name=+DATA_STDBY/stdby/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=26 STAMP=929156803 file name=+DATA_STDBY/stdby/users01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=27 STAMP=929156803 file name=+DATA_STDBY/stdby/undotbs02.dbf
Finished Duplicate Db at 29-NOV-16
released channel: c1
released channel: c2
released channel: aux1
RMAN>

注意：如果主库节点上log_archive_format的值不一样，需要在rman的run块(set log_archive_format='stdby1_%t_%s_%r.arc')中加入修改log_archive_format的选项，否则会失败。这个参数对于使用'LOCATION=USE_DB_RECOVERY_FILE_DEST'的选项无效。 


SQL> show parameter log_archive_dest_3

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_3                   string
log_archive_dest_30                  string
log_archive_dest_31                  string
SQL> alter system set log_archive_dest_3='location=/pgold/ordb/arch';
System altered.

SQL> alter system archive log current;
System altered.

SQL> !ls -l /pgold/ordb/arch
total 23356
-rw-r-----. 1 oracle asmadmin 23915520 Nov 30 03:09 prod1_1_108_928922794.arc
SQL>


3.创建pfile并编辑pfile 

SQL> show parameter spf
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      /pgold/ordb/oracle/product/112
                                                 /dbs/spfilestdby1.ora
SQL> create pfile='/tmp/p.ora' from spfile;
File created.
SQL>
新pfile内容如下 
/tmp/p.ora 
*.audit_file_dest='/pgold/ordb/oracle/product/admin/stdby/adump'
*.audit_trail='db'
*.cluster_database=true
*.compatible='11.2.0.4.0'
*.control_files='+DATA_STDBY/stdby/control01.ctl','+DATA_STDBY/stdby/control02.ctl'
*.db_block_size=8192
*.db_create_file_dest='+DATA_STDBY'
*.db_create_online_log_dest_1='+DATA_STDBY'
*.db_domain=''
*.db_file_name_convert='+DATA_PROD/prod','+DATA_STDBY/stdby'
*.db_name='prod'
*.db_recovery_file_dest_size=8388608000
*.db_recovery_file_dest='+FRA_STDBY'
*.db_unique_name='stdby'
*.dg_broker_config_file1='/pgold/ordb/oracle/dr1stdby.dat'
*.dg_broker_config_file2='/pgold/ordb/oracle/dr2stdby.dat'
*.diagnostic_dest='/pgold/ordb/log'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=stdbyXDB)'
*.fal_server='PROD'
stdby1.instance_number=1
stdby2.instance_number=2
stdby1.local_listener='(ADDRESS=(PROTOCOL=tcp)(HOST=ohs3-vip.ohsdba.cn)(PORT=1521))'
stdby2.local_listener='(ADDRESS=(PROTOCOL=tcp)(HOST=ohs4-vip.ohsdba.cn)(PORT=1521))'
*.log_archive_config='dg_config=(PROD,STDBY)'
*.log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST'
*.log_archive_dest_2='service=PROD valid_for=(online_logfiles,primary_role) LGWR ASYNC=20480 OPTIONAL REOPEN=15 NET_TIMEOUT=30 db_unique_name=PROD'
*.log_archive_dest_state_1='ENABLE'
*.log_archive_dest_state_2='ENABLE'
stdby1.log_archive_format='%t_%s_%r.arc'
stdby2.log_archive_format='%t_%s_%r.arc'
*.log_file_name_convert='+DATA_PROD/prod','+DATA_STDBY/stdby'
*.open_cursors=300
*.pga_aggregate_target=262144000
*.processes=150
*.remote_listener='stdby-scan:10015'
*.remote_login_passwordfile='exclusive'
*.sga_target=786432000
*.standby_file_management='AUTO'
stdby2.thread=2
stdby1.thread=1
stdby2.undo_tablespace='UNDOTBS2'
stdby1.undo_tablespace='UNDOTBS1'

4.创建新的spfile，并编辑initstdby1.ora 

SQL> create spfile='+DATA_STDBY/stdby/spfilestdby.ora' from pfile='/tmp/p.ora';
File created.
SQL>
[oracle@ohs3 dbs]$ cat initstdby1.ora 
spfile='+DATA_STDBY/stdby/spfilestdby.ora'
[oracle@ohs3 dbs]$ sqlplus / as sysdba
SQL> shut immediate
5.通过srvctl增加数据库配置信息 
[oracle@ohs3 ~]$ srvctl add database -d stdby -o /pgold/ordb/oracle/product/112
[oracle@ohs3 ~]$ srvctl add instance -d stdby -i stdby1 -n ohs3
[oracle@ohs3 ~]$ srvctl add instance -d stdby -i stdby2 -n ohs4
[oracle@ohs3 ~]$ srvctl config database -d stdby
Database unique name: stdby
Database name:
Oracle home: /pgold/ordb/oracle/product/112
Oracle user: oracle
Spfile:
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools: stdby
Database instances: stdby1,stdby2
Disk Groups:
Mount point paths:
Services:
Type: RAC
Database is administrator managed
[oracle@ohs3 ~]$

6.修改数据库配置信息

[oracle@ohs3 ~]$ srvctl modify database -d stdby -r physical_standby -p +DATA_STDBY/stdby/spfilestdby.ora -n prod -a DATA_STDBY,FRA_STDBY 

[oracle@ohs3 ~]$ srvctl config database -d stdby
Database unique name: stdby
Database name: prod
Oracle home: /pgold/ordb/oracle/product/112
Oracle user: oracle
Spfile: +DATA_STDBY/stdby/spfilestdby.ora
Domain: 
Start options: open
Stop options: immediate
Database role: PHYSICAL_STANDBY
Management policy: AUTOMATIC
Server pools: stdby
Database instances: stdby1,stdby2
Disk Groups: DATA_STDBY,FRA_STDBY
Mount point paths: 
Services: 
Type: RAC
Database is administrator managed
[oracle@ohs3 ~]$
7.启动备库，并启用real time apply 
[oracle@ohs3 dbs]$ ls
hc_stdby1.dat  init.ora  initstdby1.ora  orapwstdby1  s.ora  spfilestdby1.ora
[oracle@ohs3 dbs]$ rm spfilestdby1.ora 
[oracle@ohs4 ~]$ srvctl start database -d stdby -o 'read only'
[oracle@ohs4 ~]$
SQL> select open_mode from gv$database;
OPEN_MODE
--------------------
READ ONLY
READ ONLY
SQL> recover managed standby database using current logfile disconnect from session;
Media recovery complete.
SQL> select open_mode from gv$database;
OPEN_MODE
--------------------
READ ONLY WITH APPLY
READ ONLY WITH APPLY
SQL>

8.测试

在主库上创建用户和表 

SQL> create user ohsdba identified by oracle;
User created.

SQL> grant create session,connect,resource to ohsdba; 
Grant succeeded.
SQL> create table ohsdba.t1(name varchar2(20),age number);
Table created.

SQL> insert into ohsdba.t1 values('ohsdba',100);         
1 row created.
SQL> commit;

Commit complete.
SQL>
SQL>
在备库上查看 
[oracle@ohs4 ~]$ sqlplus system/oracle@stdby
SQL*Plus: Release 11.2.0.4.0 Production on Sun Nov 27 11:50:28 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

SQL> desc ohsdba.t1
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 NAME                                               VARCHAR2(20)
 AGE                                                NUMBER

SQL>

pfile文件及rman duplicate日志下载


注意：duplicate from active database实际上使用的是image copy。在生产环境中要考虑网络带宽，数据库的大小，宕机时间等因素，一般使用备份集的居多。本文仅供参考


Reference

http://docs.oracle.com/cd/E11882_01/server.112/e41134/toc.htm

http://docs.oracle.com/cd/E11882_01/server.112/e10803/toc.htm

http://www.oracle.com/technetwork/database/features/availability/maa-096107.html

http://www.oracle.com/technetwork/cn/database/maa-088017-zhs.html



关键词:physical_standby 11g rac 

相关文章

Oracle 19c新特性之RAC Automatic Failback Service
Install Oracle RAC Database 19c Step by Step
Oracle事务卫士(Transaction Guard)和应用连续性(Application Continuity)
Install Oracle Domain Service Cluster Step by Step
Oracle RAC and Third Party Cloud
ORA-12514 During DataPump Export/Import In RAC
How to config IB network listener
Oracle MAA汇总
在OEL6.8上安装12.2 RAC
Oracle Database 12.2 Hands-On Lab
Convert Snapshot Standby to Physical Standby
How to Convert Physical Standby to Snapshot Standby

推荐文章

联系我们

Oracle Software Download Index

Oracle Database Upgrade

Oracle SQLMaria

Oracle Database Document

Oracle Cloud Document

Oracle Cloud中文链接

eDelivery

GoldenGate

My Oracle Support

Exadata Document

Oracle Linux

Oracle Fusion Middleware

MySQL Document

PostgreSQL

Python

Apache

Github

Sourceforge

CNCF

Ruby

Go

ipcheck

ipaddressguide

db-engines

Linux Kernel

Top