使用Active database duplicate 建立dataguard环境:
hostname | IP | Database | DB_UNIQUE_NAME | Oracle tns name |
RAC1 | 192.168.2.101/3.101 | Primary | ogg | ogg |
RAC2 | 192.168.2.201/3.201 | Physical standby | tgg | tgg |
1.确认主库为归档模式
ARCHIVELOG LIST
2.开启FORCELOGGING
ALTERDATABASE FORCE LOGGING;
3.配置日志传输的认证
本例使用oraclepassword file认证
确保remote_login_passwordfile为EXCLUSIVE后者 SHARED
在主库生成oracle密码文件
orapwdfile=orapwogg password=oracle entries=5
scporapwogg oracle@192.168.2.201:@ORACLE_HOME/dbs/
在备库更改密码文件名字,注意这个文件名更改步骤跟10G有区别了
mvorapwogg orapwtgg
4.设置主库初始化参数
altersystem set LOG_ARCHIVE_CONFIG='DG_CONFIG=(ogg,tgg)';
altersystem set LOG_ARCHIVE_DEST_1='LOCATION=/u01/arch/ogg/VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=ogg';
altersystem set LOG_ARCHIVE_DEST_2='SERVICE=tgg LGWR SYNCVALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=tgg';
altersystem set LOG_ARCHIVE_DEST_STATE_1=ENABLE;
altersystem set FAL_SERVER=ogg;
altersystem set DB_FILE_NAME_CONVERT='/u01/oradata/ogg','/u01/oradata/tgg'scope=spfile;
altersystem setLOG_FILE_NAME_CONVERT='/u01/oradata/ogg','/u01/oradata/tgg'scope=spfile;
5.添加standby日志
alterdatabase add standby logfile '/u01/oradata/ogg/redostb1.rdo' size50m;
alterdatabase add standby logfile '/u01/oradata/ogg/redostb2.rdo' size50m;
6.备库准备目录
mkdir-p /u01/oradata/tgg
mkdir-p /u01/arch/tgg
mkdir-p /u01/oracle/admin/tgg/adump
7.建立备库临时初始化参数文件
在建立备库时,rman会自动创建spfile
vi initcc.ora
DB_NAME=ogg /*必须和主库DB_NAME一样*/
DB_UNIQUE_NAME=tgg /*必须不同于主库*/
DB_BLOCK_SIZE=8192 /*同主库*/
8.设置备库ORACLE_SID环境变量
exportORACLE_SID=tgg
9.设置TNS
tnsnames.ora 主备一样
ogg=
(DESCRIPTION=
(ADDRESS_LIST=
(ADDRESS= (PROTOCOL = TCP)(HOST = 192.168.2.101)(PORT = 1521))
)
(CONNECT_DATA=
(SERVICE_NAME= ogg)
)
)
tgg=
(DESCRIPTION=
(ADDRESS_LIST=
(ADDRESS= (PROTOCOL = TCP)(HOST = 192.168.2.201)(PORT = 1521))
)
(CONNECT_DATA=
(SERVICE_NAME= tgg)
)
)
10.设置启动监听
主:
SID_LIST_LISTENER=
(SID_LIST=
(SID_DESC=
(GLOBAL_DBNAME= ogg)
(ORACLE_HOME= /u01/oracle/product/11.2/db_1)
(SID_NAME= ogg)
)
)
LISTENER=
(DESCRIPTION_LIST=
(DESCRIPTION=
(ADDRESS= (PROTOCOL = TCP)(HOST = 192.168.2.101)(PORT = 1521))
)
(DESCRIPTION=
(ADDRESS= (PROTOCOL = TCP)(HOST = 192.168.3.101)(PORT = 1521))
)
)
ADR_BASE_LISTENER= /u01/oracle
备:
SID_LIST_LISTENER=
(SID_LIST=
(SID_DESC=
(GLOBAL_DBNAME= tgg)
(ORACLE_HOME= /u01/oracle/product/11.2/db_1)
(SID_NAME= tgg)
)
)
LISTENER=
(DESCRIPTION_LIST=
(DESCRIPTION=
(ADDRESS= (PROTOCOL = TCP)(HOST = 192.168.2.201)(PORT = 1521))
)
(DESCRIPTION=
(ADDRESS= (PROTOCOL = TCP)(HOST = 192.168.3.201)(PORT = 1521))
)
)
ADR_BASE_LISTENER= /u01/oracle
11.验证
SQL>conn sys/oracle@tgg as sysdba
Connected.
SQL>conn sys/oracle@ogg as sysdba
Connected.
12.开始duplicateactive dataguard
启动备库到nomount状态
startupnomount pfile=$ORACLE_HOME/dbs/initcc.ora
rmantarget sys/oracle@ogg auxiliary sys/oracle@tgg
rman脚本
run{
allocatechannel prmy1 type disk;
allocatechannel prmy2 type disk;
allocateauxiliary channel stby type disk;
duplicatetarget database for standby from active database
spfile
parameter_value_convert'ogg','tgg'
setdb_unique_name='tgg'
setdb_file_name_convert='/ogg/','/tgg/'
setlog_file_name_convert='/ogg/','/tgg/'
setcontrol_files='/u01/oradata/tgg/control01.ctl'
setlog_archive_max_processes='5'
setfal_server='ogg'
setstandby_file_management='AUTO'
setlog_archive_config='dg_config=(ogg,tgg)'
setlog_archive_dest_1 = 'LOCATION=/u01/arch/tgg/VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=tgg'
setlog_archive_dest_2='service=ogg ASYNCvalid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=ogg';
}
命令和输出
[oracle@rac2dbs]$ sqlplus '/as sysdba'
SQL*Plus:Release 11.2.0.4.0 Production on Mon Oct 28 16:48:39 2013
Copyright(c) 1982, 2013, Oracle. All rights reserved.
Connectedto an idle instance.
SQL>startup nomount pfile=$ORACLE_HOME/dbs/initcc.ora
ORACLEinstance started.
TotalSystem Global Area 150654976 bytes
FixedSize 1363216 bytes
VariableSize 96469744 bytes
DatabaseBuffers 50331648 bytes
RedoBuffers 2490368 bytes
SQL>exit
Disconnectedfrom Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 -Production
Withthe Partitioning, OLAP, Data Mining and Real Application Testingoptions
[oracle@rac2dbs]$ rman target sys/oracle@ogg auxiliary sys/oracle@tgg
RecoveryManager: Release 11.2.0.4.0 - Production on Mon Oct 28 16:49:10 2013
Copyright(c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connectedto target database: OGG (DBID=2931384159)
connectedto auxiliary database: OGG (not mounted)
RMAN>run {
allocatechannel prmy1 type disk;
allocatechannel prmy2 type disk;
allocateauxiliary channel stby type disk;
duplicatetarget database for standby from active database
spfile
parameter_value_convert'ogg','tgg'
setdb_unique_name='tgg'
setdb_file_name_convert='/ogg/','/tgg/'
setlog_file_name_convert='/ogg/','/tgg/'
setcontrol_files='/u01/oradata/tgg/control01.ctl'
setlog_archive_max_processes='5'
setfal_client='tgg'
setfal_server='ogg'
setstandby_file_management='AUTO'
setlog_archive_config='dg_config=(ogg,tgg)'
setlog_archive_dest_2='service=ogg ASYNCvalid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=ogg';
}2>3> 4> 5> 6> 7> 8> 9> 10> 11> 12> 13>14> 15> 16> 17> 18> 19>
usingtarget database control file instead of recovery catalog
allocatedchannel: prmy1
channelprmy1: SID=27 device type=DISK
allocatedchannel: prmy2
channelprmy2: SID=26 device type=DISK
allocatedchannel: stby
channelstby: SID=19 device type=DISK
StartingDuplicate Db at 28-OCT-13
contentsof Memory Script:
{
backupas copy reuse
targetfile '/u01/oracle/product/11.2/db_1/dbs/orapwogg' auxiliary format
'/u01/oracle/product/11.2/db_1/dbs/orapwtgg' targetfile
'/u01/oracle/product/11.2/db_1/dbs/spfileogg.ora'auxiliary format
'/u01/oracle/product/11.2/db_1/dbs/spfiletgg.ora' ;
sqlclone "alter system set spfile=''/u01/oracle/product/11.2/db_1/dbs/spfiletgg.ora''";
}
executingMemory Script
Startingbackup at 28-OCT-13
Finishedbackup at 28-OCT-13
sqlstatement: alter system set spfile=''/u01/oracle/product/11.2/db_1/dbs/spfiletgg.ora''
contentsof Memory Script:
{
sqlclone "alter system set audit_file_dest =
''/u01/oracle/admin/tgg/adump''comment=
''''scope=spfile";
sqlclone "alter system set log_archive_dest_1 =
''LOCATION=/u01/arch/tgg/VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=tgg'' comment=
''''scope=spfile";
sqlclone "alter system set db_unique_name =
''tgg''comment=
''''scope=spfile";
sqlclone "alter system set db_file_name_convert =
''/ogg/'',''/tgg/'' comment=
''''scope=spfile";
sqlclone "alter system set log_file_name_convert =
''/ogg/'',''/tgg/'' comment=
''''scope=spfile";
sqlclone "alter system set control_files =
''/u01/oradata/tgg/control01.ctl''comment=
''''scope=spfile";
sqlclone "alter system set log_archive_max_processes =
5comment=
''''scope=spfile";
sqlclone "alter system set fal_server =
''ogg''comment=
''''scope=spfile";
sqlclone "alter system set standby_file_management =
''AUTO''comment=
''''scope=spfile";
sqlclone "alter system set log_archive_config =
''dg_config=(ogg,tgg)''comment=
''''scope=spfile";
sqlclone "alter system set log_archive_dest_2 =
''service=oggASYNC valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=ogg''comment=
''''scope=spfile";
shutdownclone immediate;
startupclone nomount;
}
executingMemory Script
sqlstatement: alter system set audit_file_dest = ''/u01/oracle/admin/tgg/adump'' comment= '''' scope=spfile
sqlstatement: alter system set log_archive_dest_1 = ''LOCATION=/u01/arch/tgg/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES)DB_UNIQUE_NAME=ogg'' comment= '''' scope=spfile
sqlstatement: alter system set db_unique_name = ''tgg'' comment= ''''scope=spfile
sqlstatement: alter system set db_file_name_convert = ''/ogg/'',''/tgg/'' comment= '''' scope=spfile
sqlstatement: alter system set log_file_name_convert = ''/ogg/'',''/tgg/'' comment= '''' scope=spfile
sqlstatement: alter system set control_files = ''/u01/oradata/tgg/control01.ctl'' comment= '''' scope=spfile
sqlstatement: alter system set log_archive_max_processes = 5 comment='''' scope=spfile
sqlstatement: alter system set fal_client = ''tgg'' comment= ''''scope=spfile
sqlstatement: alter system set fal_server = ''ogg'' comment= ''''scope=spfile
sqlstatement: alter system set standby_file_management = ''AUTO''comment= '''' scope=spfile
sqlstatement: alter system set log_archive_config = ''dg_config=(ogg,tgg)'' comment= '''' scope=spfile
sqlstatement: alter system set log_archive_dest_2 = ''service=oggASYNC valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=ogg''comment= '''' scope=spfile
Oracleinstance shut down
connectedto auxiliary database (not started)
Oracleinstance started
TotalSystem Global Area 523108352 bytes
FixedSize 1365740 bytes
VariableSize 314575124 bytes
DatabaseBuffers 201326592 bytes
RedoBuffers 5840896 bytes
allocatedchannel: stby
channelstby: SID=18 device type=DISK
contentsof Memory Script:
{
backupas copy current controlfile for standby auxiliary format '/u01/oradata/tgg/control01.ctl';
}
executingMemory Script
Startingbackup at 28-OCT-13
channelprmy1: starting datafile copy
copyingstandby control file
outputfile name=/u01/oracle/product/11.2/db_1/dbs/snapcf_ogg.ftag=TAG20131028T163405 RECID=1 STAMP=830018047
channelprmy1: datafile copy complete, elapsed time: 00:00:03
Finishedbackup at 28-OCT-13
contentsof Memory Script:
{
sqlclone 'alter database mount standby database';
}
executingMemory Script
sqlstatement: alter database mount standby database
contentsof Memory Script:
{
setnewname for tempfile 1 to
"/u01/oradata/tgg/temp01.dbf";
switchclone tempfile all;
setnewname for datafile 1 to
"/u01/oradata/tgg/system01.dbf";
setnewname for datafile 2 to
"/u01/oradata/tgg/sysaux01.dbf";
setnewname for datafile 3 to
"/u01/oradata/tgg/undotbs01.dbf";
setnewname for datafile 4 to
"/u01/oradata/tgg/users01.dbf";
backupas copy reuse
datafile 1 auxiliary format
"/u01/oradata/tgg/system01.dbf" datafile
2auxiliary format
"/u01/oradata/tgg/sysaux01.dbf" datafile
3auxiliary format
"/u01/oradata/tgg/undotbs01.dbf" datafile
4auxiliary format
"/u01/oradata/tgg/users01.dbf" ;
sql'alter system archive log current';
}
executingMemory Script
executingcommand: SET NEWNAME
renamedtempfile 1 to /u01/oradata/tgg/temp01.dbf in control file
executingcommand: SET NEWNAME
executingcommand: SET NEWNAME
executingcommand: SET NEWNAME
executingcommand: SET NEWNAME
Startingbackup at 28-OCT-13
channelprmy1: starting datafile copy
inputdatafile file number=00001 name=/u01/oradata/ogg/system01.dbf
channelprmy2: starting datafile copy
inputdatafile file number=00002 name=/u01/oradata/ogg/sysaux01.dbf
outputfile name=/u01/oradata/tgg/sysaux01.dbf tag=TAG20131028T163416
channelprmy2: datafile copy complete, elapsed time: 00:01:07
channelprmy2: starting datafile copy
inputdatafile file number=00003 name=/u01/oradata/ogg/undotbs01.dbf
outputfile name=/u01/oradata/tgg/system01.dbf tag=TAG20131028T163416
channelprmy1: datafile copy complete, elapsed time: 00:01:23
channelprmy1: starting datafile copy
inputdatafile file number=00004 name=/u01/oradata/ogg/users01.dbf
outputfile name=/u01/oradata/tgg/users01.dbf tag=TAG20131028T163416
channelprmy1: datafile copy complete, elapsed time: 00:00:16
outputfile name=/u01/oradata/tgg/undotbs01.dbf tag=TAG20131028T163416
channelprmy2: datafile copy complete, elapsed time: 00:00:32
Finishedbackup at 28-OCT-13
sqlstatement: alter system archive log current
contentsof Memory Script:
{
switchclone datafile all;
}
executingMemory Script
datafile1 switched to datafile copy
inputdatafile copy RECID=1 STAMP=830019088 filename=/u01/oradata/tgg/system01.dbf
datafile2 switched to datafile copy
inputdatafile copy RECID=2 STAMP=830019088 filename=/u01/oradata/tgg/sysaux01.dbf
datafile3 switched to datafile copy
inputdatafile copy RECID=3 STAMP=830019088 filename=/u01/oradata/tgg/undotbs01.dbf
datafile4 switched to datafile copy
inputdatafile copy RECID=4 STAMP=830019088 filename=/u01/oradata/tgg/users01.dbf
FinishedDuplicate Db at 28-OCT-13
releasedchannel: prmy1
releasedchannel: prmy2
releasedchannel: stby
至此已经完成
13.验证
[oracle@rac2dbs]$ sqlplus '/as sysdba'
SQL*Plus:Release 11.2.0.4.0 Production on Mon Oct 28 16:53:09 2013
Copyright(c) 1982, 2013, Oracle. All rights reserved.
Connectedto:
OracleDatabase 11g Enterprise Edition Release 11.2.0.4.0 - Production
Withthe Partitioning, OLAP, Data Mining and Real Application Testingoptions
SQL>show parameter spfile
NAME TYPE VALUE
----------------------------------------------- ------------------------------
spfile string /u01/oracle/product/11.2/db_1/
dbs/spfiletgg.ora
SQL>alter database recover managed standby database cancel;
转为ACTIVEDATAGUARD,可以作为只读库
SQL>alter database open;
SQL>alter database recover managed standby database disconnect;
开启实时应用
alterdatabase recover managed standby database using current logfiledisconnect from session;