Oracle11g搭建DG（ADG⽅式）
1.准备⼯作
系统版本： Red Hat Enterprise Linux 6.5（64位）
软件版本：Oracle Database 11g Release 2 (11.2.0.4)
⾸先得准备两个能够PING通，并且装了Oracle软件且已经建库的Linux虚拟机。操作系统不限，⾄少有⼀个虚拟机已经建好库，最好两个都建好相同SID的库，这样少很多创建⽬录的⿇烦，这⾥库的SID都是blockOra，Linux严格区分⼤⼩写，所以SID的⼤⼩写得注意。
我这⾥有两个名为dgmaster和standby的Linux虚拟机
172.16.7.193 dgmaster（主库）
172.16.7.194 standby （备库）
2.在主库进⾏操作
2.1强制force logging
修改数据库为强制记⽇志，这是必须的操作，主库的每⼀步操作都得记录到⽇志中去。
SQL>shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 1653518336 bytes
Fixed Size            2253784 bytes
Variable Size        1006636072 bytes
Database Buffers      637534208 bytes
Redo Buffers            7094272 bytes
Database mounted.
SQL>alter database force logging;
Database altered.
2.2开启主库的归档模式
修改数据库为归档模式，因为dg是通过传送归档⽇志到备库然后应⽤来保证主备库⼀致的。
SQL>alter database archivelog;
Database altered.
2.3创建standby redo log
ALTER DATABASE ADD STANDBY LOGFILE GROUP5 ('/u01/oradata/blockOra/stredo05.log') size 200M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP6 ('/u01/oradata/blockOra/stredo06.log') size 200M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP7 ('/u01/oradata/blockOra/stredo07.log') size 200M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP8 ('/u01/oradata/blockOra/stredo08.log') size 200M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP9 ('/u01/oradata/blockOra/stredo09.log') size 200M;
standby redo log多少组才合理
单机情况下
所有redo_log组数+1
RAC环境下
所有redo log组数+实例数
正常情况下，⼀般每个实例的redo log组数⽬是⼀样的，⽐如为你，则standbby redo log组数为(n+1)*thread
假如有个rac共三个实例，每个实例都是3个log组，那么如果要做dg的standby log要增加12个standby loggroup
(3+1)*3=12
假如有个rac共三个实例，实例1有3个log组，实例2有4个log组，实例3有5个log组，总共有12个log组，那么如果要做dg的
standby log要增加15个standby loggroup
所有redo log组数+实例数=(3+4+5)+3=15
2.4创建pfile
这⾥创建pfile是为了做⼀些主库参数的配置，并且还得拷贝到备库再次修改成备库的配置。
SQL>create pfile from spfile;
File created.
SQL>shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
2.5创建主库归档⽬录
建⽴这个⽬录是为了存放主库的归档⽇志⽂件，并且这个⽬录会和其他数据⽂件等等⼀起拷贝到备库。[oracle@dgmaster blockOra]$ mkdir archivelog
[oracle@dgmaster blockOra]$ cd archivelog/
[oracle@dgmaster archivelog]$ ls
[oracle@dgmaster archivelog]$ pwd
/u01/app/oracle/oradata/blockOra/archivelog
2.6在主备库同时创建静态监听listener和tnsname
主库a
[oracle@dgmaster ~]$ cat /u01/app/oracle/product/11.2.0/db_1/network/a
# a Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/a
# Generated by Oracle configuration tools.
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY= EXTPROC1521))
(ADDRESS = (PROTOCOL = TCP)(HOST =172.16.7.193)(PORT =1521))
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = blockOra)
(ORACLE_HOME =/u01/app/oracle/product/11.2.0/db_1)
(SID_NAME = blockOra)
)
)
ADR_BASE_LISTENER =/u01/app/oracle
[oracle@dgmaster ~]$
备库a
[oracle@standby ~]$ cat /u01/app/oracle/product/11.2.0/db_1/network/a
# a Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/a
# Generated by Oracle configuration tools.
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY= EXTPROC1521))
(ADDRESS = (PROTOCOL = TCP)(HOST =172.16.7.194)(PORT =1521))
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = blockOra)
(ORACLE_HOME =/u01/app/oracle/product/11.2.0/db_1)
(SID_NAME = blockOra)
)
)
ADR_BASE_LISTENER =/u01/app/oracle
[oracle@standby ~]$
主库、备库a
[oracle@standby ~]$ cat /u01/app/oracle/product/11.2.0/db_1/network/a
# a Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/a
# Generated by Oracle configuration tools.
BLOCKORA_PD =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST =172.16.7.193)(PORT =1521))
)
(CONNECT_DATA =
(SERVICE_NAME = blockOra)
)
)
BLOCKORA_ST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST =172.16.7.194)(PORT =1521))
)
(CONNECT_DATA =
(SERVICE_NAME = blockOra)
)
)
[oracle@standby ~]$
2.7修改主库pfile⽂件
[oracle@dgmaster ~]$ cat /u01/app/oracle/product/11.2.0/db_1/a
blockOra.__db_cache_size=1593835520
blockOra.__java_pool_size=16777216
blockOra.__large_pool_size=16777216
blockOra.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
blockOra.__pga_aggregate_target=1459617792
blockOra.__sga_target=2164260864
blockOra.__shared_io_pool_size=0
blockOra.__shared_pool_size=469762048
blockOra.__streams_pool_size=33554432
*.audit_file_dest='/u01/app/oracle/admin/blockOra/adump'
*.audit_trail='db'
*patible='11.2.0.0.0'
*.control_files='/u01/app/oracle/oradata/l','/u01/app/oracle/flash_recovery_area/l' *.db_block_size=8192
*.db_domain=''
*.db_name='blockOra'
*.db_recovery_file_dest='/u01/app/oracle/flash_recovery_area'
*.db_recovery_file_dest_size=4070572032
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=blockOraXDB)'
*.memory_target=3613392896
*.nls_language='AMERICAN'
*.nls_territory='AMERICA'
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'
DB_UNIQUE_NAME=blockOra_pd
LOG_ARCHIVE_CONFIG='DG_CONFIG=(blockOra_pd,blockOra_st)'
LOG_ARCHIVE_DEST_1=
'LOCATION=/u01/archivelog/blockOra/
VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
DB_UNIQUE_NAME=blockOra_pd'
LOG_ARCHIVE_DEST_2=
'SERVICE=blockOra_st ASYNC
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
DB_UNIQUE_NAME=blockOra_st'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
FAL_SERVER=blockOra_st
STANDBY_FILE_MANAGEMENT=AUTO
[oracle@dgmaster ~]$
2.8⽤pfile启动主库，并创建spfile
SQL>shutdown immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> startup nomount pfile='/u01/app/oracle/product/11.2.0/dbhome_1/a'
ORACLE instance started.
Total System Global Area 1653518336 bytes
Fixed Size            2253784 bytes
Variable Size        1006636072 bytes
Database Buffers      637534208 bytes
Redo Buffers            7094272 bytes
SQL>create spfile from pfile;
File created.
2.9将⼝令验证⽂件和pfile发送到备库
[oracle@dgmaster dbs]$ pwd
/u01/app/oracle/product/11.2.0/dbhome_1/dbs
[oracle@dgmaster dbs]$ scp orapwblockOra 192.168.56.43:/u01/app/oracle/product/11.2.0/dbhome_1/dbs/
[oracle@dgmaster dbs]$ a 192.168.56.43:/u01/app/oracle/product/11.2.0/dbhome_1/dbs/
3.在备库做⼀些操作
3.1在备库修改从主库拷贝来的pfile
[oracle@standby ~]$ cat /u01/app/oracle/product/11.2.0/db_1/a
blockOra.__db_cache_size=1593835520
blockOra.__java_pool_size=16777216
blockOra.__large_pool_size=16777216
blockOra.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
blockOra.__pga_aggregate_target=1459617792
linux系统安装oracle11gblockOra.__sga_target=2164260864
blockOra.__shared_io_pool_size=0
blockOra.__shared_pool_size=469762048
blockOra.__streams_pool_size=33554432
*.audit_file_dest='/u01/app/oracle/admin/blockOra/adump'
*.audit_trail='db'
*patible='11.2.0.0.0'
*.control_files='/u01/app/oracle/oradata/l','/u01/app/oracle/flash_recovery_area/l' *.db_block_size=8192
*.db_domain=''
*.db_name='blockOra'
*.db_recovery_file_dest='/u01/app/oracle/flash_recovery_area'
*.db_recovery_file_dest_size=4070572032
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=blockOraXDB)'
*.memory_target=3613392896
*.nls_language='AMERICAN'
*.nls_territory='AMERICA'
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'
DB_UNIQUE_NAME=blockOra_st
LOG_ARCHIVE_CONFIG='DG_CONFIG=(blockOra_pd,blockOra_st)'
LOG_ARCHIVE_DEST_1=
'LOCATION=/u01/archivelog/blockOra
VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
DB_UNIQUE_NAME=blockOra_st'
LOG_ARCHIVE_DEST_2=
'SERVICE=blockOra_pd ASYNC
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
DB_UNIQUE_NAME=blockOra_pd'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
FAL_SERVER=blockOra_pd
STANDBY_FILE_MANAGEMENT=AUTO
[oracle@standby ~]$
3.2启动备库到nomount
[oracle@standbydbs]$ sqlplus /as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Wed Mar 2316:52:072016
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
Connected to an idle instance.
SQL> startup nomount
ORACLE instance started.
Total System Global Area 1653518336 bytes
Fixed Size            2253784 bytes
Variable Size        1006636072 bytes
Database Buffers      637534208 bytes
Redo Buffers            7094272 bytes
SQL>
3.3开始使⽤RMAN进⾏ADG
确保a
SQLNET.AUTHENTICATION_SERVICES = (NONE) 不要ALL
[oracle@standby dbs]$ rman target sys/sp851531@blockOra_pd auxiliary sys/sys@blockOra_st
Recovery Manager: Release 11.2.0.4.0- Production on Wed Mar 2317:26:332016
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
connected to target database: ORCL (DBID=1434698509)
connected to auxiliary database: ORCL (not mounted)
RMAN> duplicate target database for standby from active database nofilenamecheck;
Starting Duplicate Db at 23-MAR-16
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=19 device type=DISK
contents of Memory Script:
{
backup as copy reuse
targetfile  '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwblockOra' auxiliary format
'/u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwblockOra'  ;
}
executing Memory Script
Starting backup at 23-MAR-16
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=40 device type=DISK
Finished backup at 23-MAR-16
contents of Memory Script:
{
backup as copy current controlfile for standby auxiliary format  '/u01/app/oracle/oradata/l';
restore clone controlfile to'/u01/app/oracle/fast_recovery_area/l'from
'/u01/app/oracle/oradata/l';
}
executing Memory Script
Starting backup at 23-MAR-16
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
copying standby control file
output file name=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/snapcf_blockOra.f tag=TAG20160323T172644 RECID=4 STAMP=907262805 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 23-MAR-16
Starting restore at 23-MAR-16
using channel ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: copied control file copy
Finished restore at 23-MAR-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  1to
"/u01/app/oracle/oradata/blockOra/temp01.dbf";
switch clone tempfile all;
set newname for datafile  1to
"/u01/app/oracle/oradata/blockOra/system01.dbf";
set newname for datafile  2to
"/u01/app/oracle/oradata/blockOra/sysaux01.dbf";
set newname for datafile  3to