Oracle 11g 搭建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 GROUP 5 ('/u01/oradata/blockOra/stredo05.log') size 200M; ALTER DATABASE ADD STANDBY LOGFILE GROUP 6 ('/u01/oradata/blockOra/stredo06.log') size 200M; ALTER DATABASE ADD STANDBY LOGFILE GROUP 7 ('/u01/oradata/blockOra/stredo07.log') size 200M; ALTER DATABASE ADD STANDBY LOGFILE GROUP 8 ('/u01/oradata/blockOra/stredo08.log') size 200M; ALTER DATABASE ADD STANDBY LOGFILE GROUP 9 ('/u01/oradata/blockOra/stredo09.log') size 200M;standby redo log多少组才合理
单机情况下
所有redo_log组数+1RAC环境下
所有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
主库listener.ora[oracle@dgmaster ~]$ cat /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora # listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora # 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 ~]$备库listener.ora
[oracle@standby ~]$ cat /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora # listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora # 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 ~]$主库、备库tnsnames.ora
[oracle@standby ~]$ cat /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora # tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora # 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/dbs/initblockOra.ora 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' *.compatible='11.2.0.0.0' *.control_files='/u01/app/oracle/oradata/blockOra/control01.ctl','/u01/app/oracle/flash_recovery_area/blockOra/control02.ctl' *.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/dbs/initblockOra.ora' 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]$ scp initblockOra.ora 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/dbs/initblockOra.ora 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' *.compatible='11.2.0.0.0' *.control_files='/u01/app/oracle/oradata/blockOra/control01.ctl','/u01/app/oracle/flash_recovery_area/blockOra/control02.ctl' *.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 23 16:52:07 2016 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
确保sqlnet.ora 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 23 17:26:33 2016 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/blockOra/control01.ctl'; restore clone controlfile to '/u01/app/oracle/fast_recovery_area/blockOra/control02.ctl' from '/u01/app/oracle/oradata/blockOra/control01.ctl'; } 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 1 to "/u01/app/oracle/oradata/blockOra/temp01.dbf"; switch clone tempfile all; set newname for datafile 1 to "/u01/app/oracle/oradata/blockOra/system01.dbf"; set newname for datafile 2 to "/u01/app/oracle/oradata/blockOra/sysaux01.dbf"; set newname for datafile 3 to "/u01/app/oracle/oradata/blockOra/undotbs01.dbf"; set newname for datafile 4 to "/u01/app/oracle/oradata/blockOra/users01.dbf"; set newname for datafile 5 to "/u01/app/oracle/oradata/blockOra/example01.dbf"; backup as copy reuse datafile 1 auxiliary format "/u01/app/oracle/oradata/blockOra/system01.dbf" datafile 2 auxiliary format "/u01/app/oracle/oradata/blockOra/sysaux01.dbf" datafile 3 auxiliary format "/u01/app/oracle/oradata/blockOra/undotbs01.dbf" datafile 4 auxiliary format "/u01/app/oracle/oradata/blockOra/users01.dbf" datafile 5 auxiliary format "/u01/app/oracle/oradata/blockOra/example01.dbf" ; sql 'alter system archive log current'; } executing Memory Script executing command: SET NEWNAME renamed tempfile 1 to /u01/app/oracle/oradata/blockOra/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 23-MAR-16 using channel ORA_DISK_1 channel ORA_DISK_1: starting datafile copy input datafile file number=00001 name=/u01/app/oracle/oradata/blockOra/system01.dbf output file name=/u01/app/oracle/oradata/blockOra/system01.dbf tag=TAG20160323T172653 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:25 channel ORA_DISK_1: starting datafile copy input datafile file number=00002 name=/u01/app/oracle/oradata/blockOra/sysaux01.dbf output file name=/u01/app/oracle/oradata/blockOra/sysaux01.dbf tag=TAG20160323T172653 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:25 channel ORA_DISK_1: starting datafile copy input datafile file number=00005 name=/u01/app/oracle/oradata/blockOra/example01.dbf output file name=/u01/app/oracle/oradata/blockOra/example01.dbf tag=TAG20160323T172653 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15 channel ORA_DISK_1: starting datafile copy input datafile file number=00003 name=/u01/app/oracle/oradata/blockOra/undotbs01.dbf output file name=/u01/app/oracle/oradata/blockOra/undotbs01.dbf tag=TAG20160323T172653 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07 channel ORA_DISK_1: starting datafile copy input datafile file number=00004 name=/u01/app/oracle/oradata/blockOra/users01.dbf output file name=/u01/app/oracle/oradata/blockOra/users01.dbf tag=TAG20160323T172653 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01 Finished backup at 23-MAR-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=4 STAMP=907262888 file name=/u01/app/oracle/oradata/blockOra/system01.dbf datafile 2 switched to datafile copy input datafile copy RECID=5 STAMP=907262888 file name=/u01/app/oracle/oradata/blockOra/sysaux01.dbf datafile 3 switched to datafile copy input datafile copy RECID=6 STAMP=907262888 file name=/u01/app/oracle/oradata/blockOra/undotbs01.dbf datafile 4 switched to datafile copy input datafile copy RECID=7 STAMP=907262888 file name=/u01/app/oracle/oradata/blockOra/users01.dbf datafile 5 switched to datafile copy input datafile copy RECID=8 STAMP=907262888 file name=/u01/app/oracle/oradata/blockOra/example01.dbf Finished Duplicate Db at 23-MAR-16 RMAN>
3.3打开备库并开启apply service
[oracle@standby dbs]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.4.0 Production on Wed Mar 23 17:48:47 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, OLAP, Data Mining and Real Application Testing options SQL> alter database open; Database altered. SQL> select open_mode from v$database; OPEN_MODE -------------------- READ ONLY SQL> alter database recover managed standby database disconnect from session; Database altered.
以上需要切换日志后才能同步数据，如果需要实时同步，备库执行下面操作
alter database recover managed standby database cancel;
alter database recover managed standby database using current logfile disconnect;