Focus On Oracle

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

Oracle Exadata ,Oracle ODA, Oracle ZDLRA


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

How to create single physical standby for RAC

时间: 2017-03-10 13:24:54  | 作者: ohsdba  | 
如非注明，本站文章皆为原创。欢迎转载，转载时请注明出处和作者信息。

本文实施的环境为：11204主库为RAC + ASM，备库为单实例+文件系统，创建RAC physical standby和Single physical standby的步骤是一样的。

创建standby简要步骤

将主库密码文件复制到备库去

主库启用force logging

在备库上建立相关目录、备库静态监听

主备库TNS

由于要使用real time apply，所以需要在主库上增加standby logfile。注意：数量要比logfile group数量多1组。 

alter database add standby logfile thread 1 group 7 ('+DATA/pccb/stdredo01.log') size 512M;
alter database add standby logfile thread 1 group 8 ('+DATA/pccb/stdredo02.log') size 512M;
alter database add standby logfile thread 1 group 9 ('+DATA/pccb/stdredo03.log') size 512M;
alter database add standby logfile thread 1 group 10 ('+DATA/pccb/stdredo04.log') size 512M;
alter database add standby logfile thread 2 group 11 ('+DATA/pccb/stdredo05.log') size 512M;
alter database add standby logfile thread 2 group 12 ('+DATA/pccb/stdredo06.log') size 512M;
alter database add standby logfile thread 2 group 13 ('+DATA/pccb/stdredo07.log') size 512M;
alter database add standby logfile thread 2 group 14 ('+DATA/pccb/stdredo08.log') size 512M;


创建备库

[oracle@pccb1 ~]$ rman target sys/oracle@192.168.1.100:1521/pccb auxiliary=sys/oracle@pccbdg |tee /tmp/rman.log
Recovery Manager: Release 11.2.0.4.0 - Production on Thu Mar 9 21:55:03 2015

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: pccb (DBID=819142649)
connected to auxiliary database: pccb (not mounted)

RMAN>

RMAN> run {
2> allocate channel c1 device type disk ;
3> allocate channel c2 device type disk ;
4> allocate channel c3 device type disk ;
allocate 5> auxiliary channel aux1 device type disk ;
6> allocate auxiliary channel aux2 device type disk ;
7> allocate auxiliary channel aux3 device type disk ;
8> duplicate target database for standby from active database
spfile
9> 10> set sga_max_size '8G'
11> set sga_target '8G'
12> set pga_aggregate_target '4G'
13> set db_file_name_convert '+DATA/pccb','/oradata/pccbdg'
14> set log_file_name_convert '+DATA/pccb','/oradata/pccbdg'
15> set cluster_database='false'
16> set db_unique_name='pccbdg'
17> set instance_name='pccbdg'
18> set local_listener='(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.210)(PORT=1521))'
set db_recovery_file_dest='/fra/pccbdg'
19> 20> set control_files='/oradata/pccbdg/control01.ctl','/oradata/pccbdg/control02.ctl'
21> set audit_file_dest='/pccb/ordb/oracle/product/admin/pccbdg/adump';
22> }

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

allocated channel: c2
channel c2: SID=1780 instance=pccb1 device type=DISK

allocated channel: c3
channel c3: SID=1921 instance=pccb1 device type=DISK

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

allocated channel: aux2
channel aux2: SID=5 device type=DISK

allocated channel: aux3
channel aux3: SID=146 device type=DISK

Starting Duplicate Db at 09-MAR-17

contents of Memory Script:
{
   backup as copy reuse
   targetfile  '/pccb/ordb/oracle/product/112/dbs/orapwpccb1' auxiliary format
 '/pccb/ordb/oracle/product/112/dbs/orapwpccbdg'   targetfile
 '+DATA/pccb/spfilepccb.ora' auxiliary format
 '/pccb/ordb/oracle/product/112/dbs/spfilepccbdg.ora'   ;
   sql clone "alter system set spfile= ''/pccb/ordb/oracle/product/112/dbs/spfilepccbdg.ora''";
}
executing Memory Script

Starting backup at 09-MAR-17
Finished backup at 09-MAR-17

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

contents of Memory Script:
{
   sql clone "alter system set  sga_max_size =
 8G comment=
 '''' scope=spfile";
   sql clone "alter system set  sga_target =
 8G comment=
 '''' scope=spfile";
   sql clone "alter system set  pga_aggregate_target =
 4G comment=
 '''' scope=spfile";
   sql clone "alter system set  db_file_name_convert =
 ''+DATA/pccb'', ''/oradata/pccbdg'' comment=
 '''' scope=spfile";
   sql clone "alter system set  log_file_name_convert =
 ''+DATA/pccb'', ''/oradata/pccbdg'' comment=
 '''' scope=spfile";
   sql clone "alter system set  cluster_database =
 false comment=
 '''' scope=spfile";
   sql clone "alter system set  db_unique_name =
 ''pccbdg'' comment=
 '''' scope=spfile";
   sql clone "alter system set  instance_name =
 ''pccbdg'' comment=
 '''' scope=spfile";
   sql clone "alter system set  local_listener =
 ''(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.210)(PORT=1521))'' comment=
 '''' scope=spfile";
   sql clone "alter system set  db_recovery_file_dest =
 ''/fra/pccbdg'' comment=
 '''' scope=spfile";
   sql clone "alter system set  control_files =
 ''/oradata/pccbdg/control01.ctl'', ''/oradata/pccbdg/control02.ctl'' comment=
 '''' scope=spfile";
   sql clone "alter system set  audit_file_dest =
 ''/pccb/ordb/oracle/product/admin/pccbdg/adump'' comment=
 '''' scope=spfile";
   shutdown clone immediate;
   startup clone nomount;
}
executing Memory Script

sql statement: alter system set  sga_max_size =  8G comment= '''' scope=spfile

sql statement: alter system set  sga_target =  8G comment= '''' scope=spfile

sql statement: alter system set  pga_aggregate_target =  4G comment= '''' scope=spfile

sql statement: alter system set  db_file_name_convert =  ''+DATA/pccb'', ''/oradata/pccbdg'' comment= '''' scope=spfile

sql statement: alter system set  log_file_name_convert =  ''+DATA/pccb'', ''/oradata/pccbdg'' comment= '''' scope=spfile

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

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

sql statement: alter system set  instance_name =  ''pccbdg'' comment= '''' scope=spfile

sql statement: alter system set  local_listener =  ''(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.210)(PORT=1521))'' comment= '''' scope=spfile

sql statement: alter system set  db_recovery_file_dest =  ''/fra/pccbdg'' comment= '''' scope=spfile

sql statement: alter system set  control_files =  ''/oradata/pccbdg/control01.ctl'', ''/oradata/pccbdg/control02.ctl'' comment= '''' scope=spfile

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

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area    8551575552 bytes

Fixed Size                     2270360 bytes
Variable Size               1895828328 bytes
Database Buffers            6643777536 bytes
Redo Buffers                   9699328 bytes
allocated channel: aux1
channel aux1: SID=572 device type=DISK
allocated channel: aux2
channel aux2: SID=1141 device type=DISK
allocated channel: aux3
channel aux3: SID=1710 device type=DISK

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

Starting backup at 09-MAR-17
channel c1: starting datafile copy
copying standby control file
output file name=/pccb/ordb/oracle/product/112/dbs/snapcf_pccb1.f tag=TAG20150309T222342 RECID=1 STAMP=938211823
channel c1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 09-MAR-17

Starting restore at 09-MAR-17

channel aux2: skipped, AUTOBACKUP already found
channel aux3: skipped, AUTOBACKUP already found
channel aux1: copied control file copy
Finished restore at 09-MAR-17

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
 "/oradata/pccbdg/tempfile/temp.259.930070163";
   switch clone tempfile all;
   set newname for datafile  1 to
 "/oradata/pccbdg/datafile/system.268.930070087";
   set newname for datafile  2 to
 "/oradata/pccbdg/datafile/sysaux.267.930070107";
   set newname for datafile  3 to
 "/oradata/pccbdg/datafile/undotbs1.260.930070127";
   set newname for datafile  4 to
 "/oradata/pccbdg/datafile/undotbs2.258.930070185";
   set newname for datafile  5 to
 "/oradata/pccbdg/datafile/users.282.930070221";
   set newname for datafile  6 to
 "/oradata/pccbdg/datafile/DATA.277.930080327";
   set newname for datafile  7 to
 "/oradata/pccbdg/datafile/DATA.276.930080371";
   set newname for datafile  8 to
 "/oradata/pccbdg/datafile/DATA.275.930080397";
   set newname for datafile  9 to
 "/oradata/pccbdg/datafile/DATA.274.930080481";
   set newname for datafile  10 to
 "/oradata/pccbdg/datafile/DATA.273.930080513";
   set newname for datafile  11 to
 "/oradata/pccbdg/datafile/DATA.272.930080537";
   set newname for datafile  12 to
 "/oradata/pccbdg/datafile/DATA.271.930080571";
   set newname for datafile  13 to
 "/oradata/pccbdg/datafile/index_tbs.266.930080637";
   set newname for datafile  14 to
 "/oradata/pccbdg/datafile/index_tbs.265.930080677";
   set newname for datafile  15 to
 "/oradata/pccbdg/datafile/index_tbs.263.930080707";
   set newname for datafile  16 to
 "/oradata/pccbdg/datafile/index_tbs.262.930080989";
   set newname for datafile  17 to
 "/oradata/pccbdg/datafile/DATA.261.930086521";
   set newname for datafile  18 to
 "/oradata/pccbdg/datafile/DATA.283.930086721";
   set newname for datafile  19 to
 "/oradata/pccbdg/datafile/DATA.284.930086735";
   set newname for datafile  20 to
 "/oradata/pccbdg/datafile/index_tbs.285.930086781";
   set newname for datafile  21 to
 "/oradata/pccbdg/datafile/DATA.286.933506491";
   set newname for datafile  22 to
 "/oradata/pccbdg/datafile/DATA.287.933506515";
   backup as copy reuse
   datafile  1 auxiliary format
 "/oradata/pccbdg/datafile/system.268.930070087"   datafile
 2 auxiliary format
 "/oradata/pccbdg/datafile/sysaux.267.930070107"   datafile
 3 auxiliary format
 "/oradata/pccbdg/datafile/undotbs1.260.930070127"   datafile
 4 auxiliary format
 "/oradata/pccbdg/datafile/undotbs2.258.930070185"   datafile
 5 auxiliary format
 "/oradata/pccbdg/datafile/users.282.930070221"   datafile
 6 auxiliary format
 "/oradata/pccbdg/datafile/DATA.277.930080327"   datafile
 7 auxiliary format
 "/oradata/pccbdg/datafile/DATA.276.930080371"   datafile
 8 auxiliary format
 "/oradata/pccbdg/datafile/DATA.275.930080397"   datafile
 9 auxiliary format
 "/oradata/pccbdg/datafile/DATA.274.930080481"   datafile
 10 auxiliary format
 "/oradata/pccbdg/datafile/DATA.273.930080513"   datafile
 11 auxiliary format
 "/oradata/pccbdg/datafile/DATA.272.930080537"   datafile
 12 auxiliary format
 "/oradata/pccbdg/datafile/DATA.271.930080571"   datafile
 13 auxiliary format
 "/oradata/pccbdg/datafile/index_tbs.266.930080637"   datafile
 14 auxiliary format
 "/oradata/pccbdg/datafile/index_tbs.265.930080677"   datafile
 15 auxiliary format
 "/oradata/pccbdg/datafile/index_tbs.263.930080707"   datafile
 16 auxiliary format
 "/oradata/pccbdg/datafile/index_tbs.262.930080989"   datafile
 17 auxiliary format
 "/oradata/pccbdg/datafile/DATA.261.930086521"   datafile
 18 auxiliary format
 "/oradata/pccbdg/datafile/DATA.283.930086721"   datafile
 19 auxiliary format
 "/oradata/pccbdg/datafile/DATA.284.930086735"   datafile
 20 auxiliary format
 "/oradata/pccbdg/datafile/index_tbs.285.930086781"   datafile
 21 auxiliary format
 "/oradata/pccbdg/datafile/DATA.286.933506491"   datafile
 22 auxiliary format
 "/oradata/pccbdg/datafile/DATA.287.933506515"   ;
   sql 'alter system archive log current';
}
executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to /oradata/pccbdg/tempfile/temp.259.930070163 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

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

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 09-MAR-17
channel c1: starting datafile copy
input datafile file number=00003 name=+DATA/pccb/datafile/undotbs1.260.930070127
channel c2: starting datafile copy
input datafile file number=00004 name=+DATA/pccb/datafile/undotbs2.258.930070185
channel c3: starting datafile copy
input datafile file number=00001 name=+DATA/pccb/datafile/system.268.930070087
output file name=/oradata/pccbdg/datafile/system.268.930070087 tag=TAG20150309T222400
channel c3: datafile copy complete, elapsed time: 00:04:45
channel c3: starting datafile copy
input datafile file number=00002 name=+DATA/pccb/datafile/sysaux.267.930070107
output file name=/oradata/pccbdg/datafile/undotbs1.260.930070127 tag=TAG20150309T222400
channel c1: datafile copy complete, elapsed time: 00:09:10
channel c1: starting datafile copy
input datafile file number=00006 name=+DATA/pccb/datafile/DATA.277.930080327
output file name=/oradata/pccbdg/datafile/undotbs2.258.930070185 tag=TAG20150309T222400
channel c2: datafile copy complete, elapsed time: 00:09:10
channel c2: starting datafile copy
input datafile file number=00007 name=+DATA/pccb/datafile/DATA.276.930080371
output file name=/oradata/pccbdg/datafile/sysaux.267.930070107 tag=TAG20150309T222400
channel c3: datafile copy complete, elapsed time: 00:04:26
channel c3: starting datafile copy
input datafile file number=00008 name=+DATA/pccb/datafile/DATA.275.930080397
output file name=/oradata/pccbdg/datafile/DATA.275.930080397 tag=TAG20150309T222400
channel c3: datafile copy complete, elapsed time: 00:03:05
channel c3: starting datafile copy
input datafile file number=00009 name=+DATA/pccb/datafile/DATA.274.930080481
output file name=/oradata/pccbdg/datafile/DATA.277.930080327 tag=TAG20150309T222400
channel c1: datafile copy complete, elapsed time: 00:05:21
channel c1: starting datafile copy
input datafile file number=00010 name=+DATA/pccb/datafile/DATA.273.930080513
output file name=/oradata/pccbdg/datafile/DATA.276.930080371 tag=TAG20150309T222400
channel c2: datafile copy complete, elapsed time: 00:05:20
channel c2: starting datafile copy
input datafile file number=00011 name=+DATA/pccb/datafile/DATA.272.930080537
output file name=/oradata/pccbdg/datafile/DATA.274.930080481 tag=TAG20150309T222400
channel c3: datafile copy complete, elapsed time: 00:03:50
channel c3: starting datafile copy
input datafile file number=00012 name=+DATA/pccb/datafile/DATA.271.930080571
output file name=/oradata/pccbdg/datafile/DATA.271.930080571 tag=TAG20150309T222400
channel c3: datafile copy complete, elapsed time: 00:03:16
channel c3: starting datafile copy
input datafile file number=00013 name=+DATA/pccb/datafile/index_tbs.266.930080637
output file name=/oradata/pccbdg/datafile/DATA.272.930080537 tag=TAG20150309T222400
channel c2: datafile copy complete, elapsed time: 00:05:46
channel c2: starting datafile copy
input datafile file number=00014 name=+DATA/pccb/datafile/index_tbs.265.930080677
output file name=/oradata/pccbdg/datafile/DATA.273.930080513 tag=TAG20150309T222400
channel c1: datafile copy complete, elapsed time: 00:05:49
channel c1: starting datafile copy
input datafile file number=00015 name=+DATA/pccb/datafile/index_tbs.263.930080707
output file name=/oradata/pccbdg/datafile/index_tbs.266.930080637 tag=TAG20150309T222400
channel c3: datafile copy complete, elapsed time: 00:03:13
channel c3: starting datafile copy
input datafile file number=00016 name=+DATA/pccb/datafile/index_tbs.262.930080989
output file name=/oradata/pccbdg/datafile/index_tbs.262.930080989 tag=TAG20150309T222400
channel c3: datafile copy complete, elapsed time: 00:03:15
channel c3: starting datafile copy
input datafile file number=00017 name=+DATA/pccb/datafile/DATA.261.930086521
output file name=/oradata/pccbdg/datafile/index_tbs.263.930080707 tag=TAG20150309T222400
channel c1: datafile copy complete, elapsed time: 00:05:34
channel c1: starting datafile copy
input datafile file number=00018 name=+DATA/pccb/datafile/DATA.283.930086721
output file name=/oradata/pccbdg/datafile/index_tbs.265.930080677 tag=TAG20150309T222400
channel c2: datafile copy complete, elapsed time: 00:05:52
channel c2: starting datafile copy
input datafile file number=00019 name=+DATA/pccb/datafile/DATA.284.930086735
output file name=/oradata/pccbdg/datafile/DATA.283.930086721 tag=TAG20150309T222400
channel c1: datafile copy complete, elapsed time: 00:04:30
channel c1: starting datafile copy
input datafile file number=00020 name=+DATA/pccb/datafile/index_tbs.285.930086781
output file name=/oradata/pccbdg/datafile/DATA.261.930086521 tag=TAG20150309T222400
channel c3: datafile copy complete, elapsed time: 00:04:33
channel c3: starting datafile copy
input datafile file number=00021 name=+DATA/pccb/datafile/DATA.286.933506491
output file name=/oradata/pccbdg/datafile/DATA.284.930086735 tag=TAG20150309T222400
channel c2: datafile copy complete, elapsed time: 00:04:40
channel c2: starting datafile copy
input datafile file number=00022 name=+DATA/pccb/datafile/DATA.287.933506515
output file name=/oradata/pccbdg/datafile/index_tbs.285.930086781 tag=TAG20150309T222400
channel c1: datafile copy complete, elapsed time: 00:03:11
channel c1: starting datafile copy
input datafile file number=00005 name=+DATA/pccb/datafile/users.282.930070221
output file name=/oradata/pccbdg/datafile/users.282.930070221 tag=TAG20150309T222400
channel c1: datafile copy complete, elapsed time: 00:00:25
output file name=/oradata/pccbdg/datafile/DATA.286.933506491 tag=TAG20150309T222400
channel c3: datafile copy complete, elapsed time: 00:04:46
output file name=/oradata/pccbdg/datafile/DATA.287.933506515 tag=TAG20150309T222400
channel c2: datafile copy complete, elapsed time: 00:04:30
Finished backup at 09-MAR-17

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=1 STAMP=938213965 file name=/oradata/pccbdg/datafile/system.268.930070087
datafile 2 switched to datafile copy
input datafile copy RECID=2 STAMP=938213965 file name=/oradata/pccbdg/datafile/sysaux.267.930070107
datafile 3 switched to datafile copy
input datafile copy RECID=3 STAMP=938213965 file name=/oradata/pccbdg/datafile/undotbs1.260.930070127
datafile 4 switched to datafile copy
input datafile copy RECID=4 STAMP=938213965 file name=/oradata/pccbdg/datafile/undotbs2.258.930070185
datafile 5 switched to datafile copy
input datafile copy RECID=5 STAMP=938213965 file name=/oradata/pccbdg/datafile/users.282.930070221
datafile 6 switched to datafile copy
input datafile copy RECID=6 STAMP=938213965 file name=/oradata/pccbdg/datafile/DATA.277.930080327
datafile 7 switched to datafile copy
input datafile copy RECID=7 STAMP=938213965 file name=/oradata/pccbdg/datafile/DATA.276.930080371
datafile 8 switched to datafile copy
input datafile copy RECID=8 STAMP=938213965 file name=/oradata/pccbdg/datafile/DATA.275.930080397
datafile 9 switched to datafile copy
input datafile copy RECID=9 STAMP=938213965 file name=/oradata/pccbdg/datafile/DATA.274.930080481
datafile 10 switched to datafile copy
input datafile copy RECID=10 STAMP=938213965 file name=/oradata/pccbdg/datafile/DATA.273.930080513
datafile 11 switched to datafile copy
input datafile copy RECID=11 STAMP=938213965 file name=/oradata/pccbdg/datafile/DATA.272.930080537
datafile 12 switched to datafile copy
input datafile copy RECID=12 STAMP=938213965 file name=/oradata/pccbdg/datafile/DATA.271.930080571
datafile 13 switched to datafile copy
input datafile copy RECID=13 STAMP=938213965 file name=/oradata/pccbdg/datafile/index_tbs.266.930080637
datafile 14 switched to datafile copy
input datafile copy RECID=14 STAMP=938213965 file name=/oradata/pccbdg/datafile/index_tbs.265.930080677
datafile 15 switched to datafile copy
input datafile copy RECID=15 STAMP=938213965 file name=/oradata/pccbdg/datafile/index_tbs.263.930080707
datafile 16 switched to datafile copy
input datafile copy RECID=16 STAMP=938213965 file name=/oradata/pccbdg/datafile/index_tbs.262.930080989
datafile 17 switched to datafile copy
input datafile copy RECID=17 STAMP=938213965 file name=/oradata/pccbdg/datafile/DATA.261.930086521
datafile 18 switched to datafile copy
input datafile copy RECID=18 STAMP=938213965 file name=/oradata/pccbdg/datafile/DATA.283.930086721
datafile 19 switched to datafile copy
input datafile copy RECID=19 STAMP=938213965 file name=/oradata/pccbdg/datafile/DATA.284.930086735
datafile 20 switched to datafile copy
input datafile copy RECID=20 STAMP=938213965 file name=/oradata/pccbdg/datafile/index_tbs.285.930086781
datafile 21 switched to datafile copy
input datafile copy RECID=21 STAMP=938213965 file name=/oradata/pccbdg/datafile/DATA.286.933506491
datafile 22 switched to datafile copy
input datafile copy RECID=22 STAMP=938213965 file name=/oradata/pccbdg/datafile/DATA.287.933506515
Finished Duplicate Db at 09-MAR-17
released channel: c1
released channel: c2
released channel: c3
released channel: aux1
released channel: aux2
released channel: aux3

RMAN>
修改备库spfile
SQL> alter system set undo_tablespace='UNDOTBS1' scope=spfile;

System altered.

SQL> alter system set thread=1 scope=spfile;

System altered.

SQL>
主备库启用DG Broker 
Primary
SQL> show parameter broker

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
dg_broker_config_file1               string      +DATA/pccb/dr1pccb.dat
dg_broker_config_file2               string      +DATA/pccb/dr2pccb.dat
dg_broker_start                      boolean     TRUE
SQL>

Standby
SQL> show parameter broker
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
dg_broker_config_file1               string      /pccb/ordb/oracle/product/11
                                                 2/dbs/dr1pccbdg.dat
dg_broker_config_file2               string      /pccb/ordb/oracle/product/11
                                                 2/dbs/dr2pccbdg.dat
dg_broker_start                      boolean     TRUE
SQL>
配置DG Broker
[oracle@pccb1 admin]$ dgmgrl sys/oracle
DGMGRL for Linux: Version 11.2.0.4.0 - 64bit Production

Copyright (c) 2000, 2009, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.
Connected.
DGMGRL> help

The following commands are available:

add            Adds a standby database to the broker configuration
connect        Connects to an Oracle database instance
convert        Converts a database from one type to another
create         Creates a broker configuration
disable        Disables a configuration, a database, or fast-start failover
edit           Edits a configuration, database, or instance
enable         Enables a configuration, a database, or fast-start failover
exit           Exits the program
failover       Changes a standby database to be the primary database
help           Displays description and syntax for a command
quit           Exits the program
reinstate      Changes a database marked for reinstatement into a viable standby
rem            Comment to be ignored by DGMGRL
remove         Removes a configuration, database, or instance
show           Displays information about a configuration, database, or instance
shutdown       Shuts down a currently running Oracle database instance
sql            Executes a SQL statement
start          Starts the fast-start failover observer
startup        Starts an Oracle database instance
stop           Stops the fast-start failover observer
switchover     Switches roles between a primary and standby database

Use "help <command>" to see syntax for individual commands

DGMGRL> help create

Creates a broker configuration

Syntax:

  CREATE CONFIGURATION <configuration name> AS
    PRIMARY DATABASE IS <database name>
    CONNECT IDENTIFIER IS <connect identifier>;
    
DGMGRL> create configuration dg_pccb as primary database is pccb connect identifier is pccb;
Configuration "dg_pccb" created with primary database "pccb"
DGMGRL> add database 'pccbdg' as connect identifier is pccbdg;
Database "pccbdg" added
DGMGRL> enable configuration
Enabled.
DGMGRL>
DGMGRL> show configuration

Configuration - dg_pccb

  Protection Mode: MaxPerformance
  Databases:
    pccb   - Primary database
    pccbdg - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

DGMGRL> show database 'pccb' 'InconsistentProperties';
INCONSISTENT PROPERTIES
   INSTANCE_NAME        PROPERTY_NAME         MEMORY_VALUE         SPFILE_VALUE         BROKER_VALUE

DGMGRL>

配置归档删除策略 

[oracle@pccb1 admin]$ rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Fri Mar 10 00:05:07 2015

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: pccb (DBID=819142649)

RMAN> CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON STANDBY;

using target database control file instead of recovery catalog
new RMAN configuration parameters:
CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON STANDBY;
new RMAN configuration parameters are successfully stored
RMAN-08591: WARNING: invalid archived log deletion policy
注意此处出现RMAN-08591是由于归档位置属性没有设置成mandatory 

[oracle@pccb1 admin]$ oerr rman 8591
8591, 3, "WARNING: invalid archived log deletion policy"
// *Cause: An invalid ARCHIVELOG DELETION POLICY was supplied. The archived
//         log deletion policy was APPLIED but there was no mandatory
//         archived log destinations.
// *Action: One of the following:
//          1) Change archived log deletion policy using CONFIGURE command
//          2) Make one or more of standby destination as MANDATORY.


[oracle@pccb1 ~]$ dgmgrl sys/oracle
DGMGRL for Linux: Version 11.2.0.4.0 - 64bit Production

Copyright (c) 2000, 2009, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.
Connected.
DGMGRL> show database verbose pccbdg

Database - pccbdg

  Role:            PHYSICAL STANDBY
  Intended State:  APPLY-ON
  Transport Lag:   0 seconds (computed 1 second ago)
  Apply Lag:       0 seconds (computed 1 second ago)
  Apply Rate:      258.00 KByte/s
  Real Time Query: ON
  Instance(s):
    pccbdg

  Properties:
    DGConnectIdentifier             = 'pccbdg'
    ObserverConnectIdentifier       = ''
    LogXptMode                      = 'ASYNC'
    DelayMins                       = '0'
    Binding                         = 'optional'
    MaxFailure                      = '0'
    MaxConnections                  = '1'
    ReopenSecs                      = '300'
    NetTimeout                      = '30'
    RedoCompression                 = 'DISABLE'
    LogShipping                     = 'ON'
    PreferredApplyInstance          = ''
    ApplyInstanceTimeout            = '0'
    ApplyParallel                   = 'AUTO'
    StandbyFileManagement           = 'AUTO'
    ArchiveLagTarget                = '0'
    LogArchiveMaxProcesses          = '8'
    LogArchiveMinSucceedDest        = '1'
    DbFileNameConvert               = '+DATA/pccb, /oradata/pccbdg'
    LogFileNameConvert              = '+DATA/pccb, /oradata/pccbdg'
    FastStartFailoverTarget         = ''
    InconsistentProperties          = '(monitor)'
    InconsistentLogXptProps         = '(monitor)'
    SendQEntries                    = '(monitor)'
    LogXptStatus                    = '(monitor)'
    RecvQEntries                    = '(monitor)'
    ApplyLagThreshold               = '0'
    TransportLagThreshold           = '0'
    TransportDisconnectedThreshold  = '30'
    SidName                         = 'pccbdg'
    StaticConnectIdentifier         = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.210)(PORT=                                                                            1521))(CONNECT_DATA=(SERVICE_NAME=pccbdg_DGMGRL)(INSTANCE_NAME=pccbdg)(SERVER=DEDICATED)))'
    StandbyArchiveLocation          = 'USE_DB_RECOVERY_FILE_DEST'
    AlternateLocation               = ''
    LogArchiveTrace                 = '0'
    LogArchiveFormat                = '%t_%s_%r.dbf'
    TopWaitEvents                   = '(monitor)'

Database Status:
SUCCESS
DGMGRL> edit database pccbdg set property Binding='mandatory';
Property "binding" updated
DGMGRL> show database pccbdg Binding
  Binding = 'mandatory'
DGMGRL>


[oracle@pccb1 ~]$ rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Fri Mar 10 00:06:49 2015

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: pccb (DBID=819142649)

RMAN> CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON STANDBY;

using target database control file instead of recovery catalog
old RMAN configuration parameters:
CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON STANDBY;
new RMAN configuration parameters:
CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON STANDBY;
new RMAN configuration parameters are successfully stored

RMAN>

验证数据
[oracle@pccb1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Fri Mar 10 00:18:56 2015

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> create table system.ohsdba(name varchar2(100),age number);

Table created.

SQL> insert into system.ohsdba values ('ohsdba',120);

1 row created.

SQL> commit;

Commit complete.

SQL>


[oracle@pccb1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Fri Mar 10 00:19:54 2015

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, OLAP, Data Mining and Real Application Testing options

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

SQL> col name for a20
SQL> select * from system.ohsdba;

NAME                        AGE
-------------------- ----------
ohsdba                       120

SQL>


[oracle@pccb2 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Fri Mar 10 00:23:32 2015

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

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> update system.ohsdba set age=210;

1 row updated.

SQL> commit;

Commit complete.

SQL>


SQL> select * from system.ohsdba;

NAME                        AGE
-------------------- ----------
ohsdba                      210

SQL>

[oracle@pccbdg1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Fri Mar 10 00:25:45 2015

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, OLAP, Data Mining and Real Application Testing options

SQL> col name for a20
SQL> select * from system.ohsdba;

NAME                        AGE
-------------------- ----------
ohsdba                      210

SQL>



关键词:maa dg 11g 12c rac 

相关文章

关于max_string_size
在Oracle数据库19c中使用JSON
Oracle 19c新特性之Automatic Flashback
Oracle 19c新特性之RAC Automatic Failback Service
Install Oracle RAC Database 19c Step by Step
保障业务连续性的神器
Oracle事务卫士(Transaction Guard)和应用连续性(Application Continuity)
容器数据库（CDB）和可插拔数据库（PDB）概述
PDB Migration/Failover in Dataguard
Install Oracle Domain Service Cluster Step by Step
Oracle RAC and Third Party Cloud
How to generate AWR on PDB and ADG(12.2 afterwards)

推荐文章

联系我们

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