由于換了一家公司，這家公司全部是用的11GR2+DG，11GR2沒有玩過，DG也沒有玩過，是以這裡在自己的VM裡面搭建一個11GR2+DG的測試環境，STANDBY啟動到READ ONLY
測試環境為：OS REDHAT 5.6 X86_64,DB 11.2.0.2
安裝環境與建立資料庫這裡就不用說了，很簡單的。
整個環境為成4個步驟：
1，主備修改
1.1 修改參數
1.2 修改監聽
1.3 cp相關檔案到standby上面
1.4 建立standby日志
2，備備修改
2.1 修改參數
2.2 修改監聽與測試
2.3 啟動到mount
2.4 建立相關目錄
3，主備duplicate資料庫
4，測試DG是否成功
正在開始正式的測試過程
1.1 檢視資料庫是否在歸檔與是否強制LOGGING模式。
SQL> select log_mode,force_logging from v$database;
LOG_MODE FOR
------------ ---
ARCHIVELOG NO
SQL> ALTER DATABASE FORCE LOGGING;
Database altered.
1.2 檢視資料庫的日志組個數與大小，因為我們建立standby日志組的個數是原日志組個數+1再與thread的積，size不能小于原日志檔案的大小。
SQL> select group#,bytes/1024/1024 from v$log;
GROUP# BYTES/1024/1024
---------- ---------------
1 50
2 50
3 50
SQL> select member from v$logfile;
MEMBER
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/htz/redo03.log
/u01/app/oracle/oradata/htz/redo02.log
/u01/app/oracle/oradata/htz/redo01.log
1.3 建立standby日志組，位置與原日志組相同的路徑。建立完成後查詢是否成功
SQL> alter database add standby logfile '/u01/app/oracle/oradata/htz/standby01.log' size 50m;
SQL> alter database add standby logfile '/u01/app/oracle/oradata/htz/standby02.log' size 50m;
SQL> alter database add standby logfile '/u01/app/oracle/oradata/htz/standby03.log' size 50m;
SQL> alter database add standby logfile '/u01/app/oracle/oradata/htz/standby04.log' size 50m;
SQL> select group#,status,type,member from v$logfile;
GROUP# STATUS TYPE MEMBER
---------- ------- ------- --------------------------------------------------
3 ONLINE /u01/app/oracle/oradata/htz/redo03.log
2 ONLINE /u01/app/oracle/oradata/htz/redo02.log
1 ONLINE /u01/app/oracle/oradata/htz/redo01.log
4 STANDBY /u01/app/oracle/oradata/htz/standby01.log
5 STANDBY /u01/app/oracle/oradata/htz/standby02.log
6 STANDBY /u01/app/oracle/oradata/htz/standby03.log
7 STANDBY /u01/app/oracle/oradata/htz/standby04.log
1.4 修改相關的參數，與DG的參數就隻與幾個參數相關，大概就是日志，檔案的位置的轉換，GAP的處理，其實GAP已經會自動的處理，不過這裡我們還是介紹配置FAL_SERVER,FAL_CLIENT參數。
SQL> alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(htz,htzb)';
System altered.
SQL> alter system set LOG_ARCHIVE_DEST_1='LOCATION=/u01/app/oracle/arch/htz/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=htz';
SQL> alter system set LOG_ARCHIVE_DEST_2='SERVICE=htzb LGWR SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=htzb';
SQL> alter system set LOG_ARCHIVE_DEST_STATE_1=ENABLE;
SQL> alter system set FAL_SERVER=htzb;
SQL> alter system set FAL_CLIENT=htz;
SQL> alter system set db_unique_name=htz scope=spfile;
SQL> alter system set db_file_name_convert='/u01/app/oracle/oradata/htz/','/u01/app/oracle/oradata/htz/' scope=spfile;
SQL> alter system set log_file_name_convert='/u01/app/oracle/oradata/htz/','/u01/app/oracle/oradata/htz/' scope=spfile;
SQL> create pfile from spfile;
File created.
1.5 把dbs下的内容同步到standby主機上面，主要是密碼檔案（一定要複制過去的）與init檔案。
[oracle@11g admin]$ rsync -avlR /u01/app/oracle/product/11.2.0/db_1/dbs/* 192.168.100.31:/
[email protected]'s password:
building file list ... done
/u01/
/u01/app/
/u01/app/oracle/
/u01/app/oracle/product/
/u01/app/oracle/product/11.2.0/
/u01/app/oracle/product/11.2.0/db_1/
/u01/app/oracle/product/11.2.0/db_1/dbs/
/u01/app/oracle/product/11.2.0/db_1/dbs/hc_DBUA0.dat
/u01/app/oracle/product/11.2.0/db_1/dbs/hc_htz.dat
/u01/app/oracle/product/11.2.0/db_1/dbs/hc_luo.dat
/u01/app/oracle/product/11.2.0/db_1/dbs/init.ora
/u01/app/oracle/product/11.2.0/db_1/dbs/inithtz.ora
/u01/app/oracle/product/11.2.0/db_1/dbs/lkHTZ
/u01/app/oracle/product/11.2.0/db_1/dbs/lkHTZA
/u01/app/oracle/product/11.2.0/db_1/dbs/orapwhtz
/u01/app/oracle/product/11.2.0/db_1/dbs/snapcf_htz.f
/u01/app/oracle/product/11.2.0/db_1/dbs/spfilehtz.ora
sent 9764651 bytes received 282 bytes 161403.85 bytes/sec
total size is 9762574 speedup is 1.00
1.6 監聽的修改，特别注意這裡我們使用了靜态的監聽，是為了以後我們測試broker時使用的，如果你不用這個，那邊可以用動态監聽，
$ lsnrctl stop
LSNRCTL for Solaris: Version 11.2.0.1.0 - Production on 31-AUG-2012 21:38:51
Copyright (c) 1991, 2009, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.100.30)(PORT=1521)))
The command completed successfully
[oracle@11g admin]$ cat listener.ora
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = htz)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
(SID_NAME = htz)
)
)
LISTENER =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.30)(PORT = 1521))
)
[oracle@11g admin]$ cat tnsnames.ora
HTZB =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.31)(PORT = 1521))
(CONNECT_DATA =
(SERVER = dedicate)
(SERVICE_NAME = htzb)
)
HTZ =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.30)(PORT = 1521))
(SERVICE_NAME = htz)
啟動監聽
[oracle@11g admin]$ lsnrctl start
LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 07-SEP-2012 05:35:50
Copyright (c) 1991, 2010, Oracle. All rights reserved.
Starting /u01/app/oracle/product/11.2.0/db_1/bin/tnslsnr: please wait...
TNSLSNR for Linux: Version 11.2.0.2.0 - Production
System parameter file is /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/11g/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.100.30)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.2.0 - Production
Start Date 07-SEP-2012 05:35:50
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/11g/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.100.30)(PORT=1521)))
Services Summary...
Service "htz" has 1 instance(s).
Instance "htz", status UNKNOWN, has 1 handler(s) for this service...
主庫上面修改的内容差不多就是這些了。
下面就是庫備的修改了。
2.1 修改監聽
[oracle@11gdg admin]$ cat 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.
(GLOBAL_DBNAME = htzb)
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.31)(PORT = 1521))
ADR_BASE_LISTENER = /u01/app/oracle
[oracle@11gdg admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora
[oracle@11gdg admin]$ lsnrctl stop
LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 06-SEP-2012 18:13:17
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.100.31)(PORT=1521)))
[oracle@11gdg admin]$ lsnrctl start
LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 06-SEP-2012 18:13:23
Log messages written to /u01/app/oracle/diag/tnslsnr/11gdg/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.100.31)(PORT=1521)))
Start Date 06-SEP-2012 18:13:24
Listener Log File /u01/app/oracle/diag/tnslsnr/11gdg/listener/alert/log.xml
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.100.31)(PORT=1521)))
Service "htzb" has 1 instance(s).
測試監聽是否正常
[oracle@11gdg /]$ sqlplus sys/oracle@htz as sysdba
SQL*Plus: Release 11.2.0.2.0 Production on Thu Sep 6 18:19:20 2012
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
[oracle@11gdg /]$ sqlplus sys/oracle@htzb as sysdba
SQL*Plus: Release 11.2.0.2.0 Production on Thu Sep 6 18:19:24 2012
Connected to an idle instance.
2.1 建立相關檔案與修改參數檔案
建立相關目錄
[oracle@11gdg admin]$ mkdir -p /u01/app/oracle/arch/htz
[oracle@11gdg admin]$ mkdir -p /u01/app/oracle/flash_recovery_area/htz
[oracle@11gdg admin]$ mkdir -p /u01/app/oracle/oradata/htz
[oracle@11gdg admin]$ mkdir -p /u01/app/oracle/admin/htz/adump
修改參數檔案
[oracle@11gdg /]$ cd $ORACLE_HOME/dbs
[oracle@11gdg dbs]$ rm spfilehtz.ora
[oracle@11gdg dbs]$ cat inithtz.ora
htz.__db_cache_size=67108864
htz.__java_pool_size=4194304
htz.__large_pool_size=4194304
htz.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
htz.__pga_aggregate_target=134217728
htz.__sga_target=180355072
htz.__shared_io_pool_size=0
htz.__shared_pool_size=96468992
htz.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/htz/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/u01/app/oracle/oradata/htz/control01.ctl','/u01/app/oracle/oradata/htz/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_file_name_convert='/u01/app/oracle/oradata/htz/','/u01/app/oracle/oradata/htz/'
*.db_name='htz'
*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'
*.db_recovery_file_dest_size=4227858432
*.db_unique_name='HTZB'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=htzXDB)'
*.fal_client='HTZB'
*.fal_server='HTZ'
*.log_archive_config='DG_CONFIG=(htz,htzb)'
*.log_archive_dest_1='LOCATION=/u01/app/oracle/arch/htz/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=htzb'
*.log_archive_dest_2='SERVICE=htz LGWR SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=htz'
*.log_archive_dest_state_1='ENABLE'
*.log_archive_format='%t_%s_%r.dbf'
*.log_file_name_convert='/u01/app/oracle/oradata/htz/','/u01/app/oracle/oradata/htz/'
*.memory_target=314572800
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'
1.3 建立spfile與啟動資料庫到mount
建立spfile,并啟動資料庫到NOMOUNT
[oracle@11gdg dbs]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.2.0 Production on Thu Sep 6 18:24:02 2012
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 313159680 bytes
Fixed Size 2226072 bytes
Variable Size 239077480 bytes
Database Buffers 67108864 bytes
Redo Buffers 4747264 bytes
SQL> create spfile from pfile;
SQL> startup force mount;
ORA-00205: error in identifying control file, check alert log for more info
SQL> show parameter spfile;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /u01/app/oracle/product/11.2.0
/db_1/dbs/spfilehtz.ora
1.4 修改/etc/oratab檔案，由于OS不一樣，這裡修改的位置也不一樣,如果SUN /var/opt/oracle/oratab,IBM的/etc/oratab
[root@11gdg ~]# echo "htz:/u01/app/oracle/product/11.2.0/db_1:N">>/etc/oratab
[root@11gdg ~]# tail -1 /etc/oratab
htz:/u01/app/oracle/product/11.2.0/db_1:N
備庫的相關操作到此就差不多，到了duplicate的時候了。
回到主備上面。
在前面我們修改了兩個參數(db/log_file_name_convertt)到spfile檔案中，還沒有重新開機資料庫，這裡我們重新開機一下主資料庫
3.1
[oracle@11g admin]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.2.0 Production on Fri Sep 7 05:50:13 2012
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
Variable Size 247466088 bytes
Database Buffers 58720256 bytes
Database mounted.
Database opened.
3.2 duplicate開始
[oracle@11g admin]$ rman target sys/oracle@htz auxiliary sys/oracle@htzb
Recovery Manager: Release 11.2.0.2.0 - Production on Fri Sep 7 05:50:46 2012
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: HTZ (DBID=1848107928)
connected to auxiliary database: HTZ (not mounted)
RMAN> run {
2> allocate channel prmy1 type disk;
3> allocate channel prmy2 type disk;
4> allocate channel prmy3 type disk;
5> allocate channel prmy4 type disk;
6> allocate auxiliary channel stby type disk;
7> duplicate target database for standby from active database nofilenamecheck
8> ;
9> }
using target database control file instead of recovery catalog
allocated channel: prmy1
channel prmy1: SID=36 device type=DISK
allocated channel: prmy2
channel prmy2: SID=37 device type=DISK
allocated channel: prmy3
channel prmy3: SID=38 device type=DISK
allocated channel: prmy4
channel prmy4: SID=39 device type=DISK
allocated channel: stby
channel stby: SID=21 device type=DISK
Starting Duplicate Db at 07-SEP-12
contents of Memory Script:
{
backup as copy reuse
targetfile '/u01/app/oracle/product/11.2.0/db_1/dbs/orapwhtz' auxiliary format
'/u01/app/oracle/product/11.2.0/db_1/dbs/orapwhtz' ;
}
executing Memory Script
Starting backup at 07-SEP-12
Finished backup at 07-SEP-12
backup as copy current controlfile for standby auxiliary format '/u01/app/oracle/oradata/htz/control01.ctl';
restore clone controlfile to '/u01/app/oracle/oradata/htz/control02.ctl' from
'/u01/app/oracle/oradata/htz/control01.ctl';
channel prmy1: starting datafile copy
copying standby control file
output file name=/u01/app/oracle/product/11.2.0/db_1/dbs/snapcf_htz.f tag=TAG20120907T055127 RECID=1 STAMP=793345887
channel prmy1: datafile copy complete, elapsed time: 00:00:01
Starting restore at 07-SEP-12
channel stby: copied control file copy
Finished restore at 07-SEP-12
sql clone 'alter database mount standby database';
sql statement: alter database mount standby database
set newname for tempfile 1 to
"/u01/app/oracle/oradata/htz/temp01.dbf";
switch clone tempfile all;
set newname for datafile 1 to
"/u01/app/oracle/oradata/htz/system01.dbf";
set newname for datafile 2 to
"/u01/app/oracle/oradata/htz/sysaux01.dbf";
set newname for datafile 3 to
"/u01/app/oracle/oradata/htz/undotbs01.dbf";
set newname for datafile 4 to
"/u01/app/oracle/oradata/htz/users01.dbf";
datafile 1 auxiliary format
"/u01/app/oracle/oradata/htz/system01.dbf" datafile
2 auxiliary format
"/u01/app/oracle/oradata/htz/sysaux01.dbf" datafile
3 auxiliary format
"/u01/app/oracle/oradata/htz/undotbs01.dbf" datafile
4 auxiliary format
"/u01/app/oracle/oradata/htz/users01.dbf" ;
sql 'alter system archive log current';
executing command: SET NEWNAME
renamed tempfile 1 to /u01/app/oracle/oradata/htz/temp01.dbf in control file
input datafile file number=00001 name=/u01/app/oracle/oradata/htz/system01.dbf
channel prmy2: starting datafile copy
input datafile file number=00002 name=/u01/app/oracle/oradata/htz/sysaux01.dbf
channel prmy3: starting datafile copy
input datafile file number=00003 name=/u01/app/oracle/oradata/htz/undotbs01.dbf
channel prmy4: starting datafile copy
input datafile file number=00004 name=/u01/app/oracle/oradata/htz/users01.dbf
output file name=/u01/app/oracle/oradata/htz/undotbs01.dbf tag=TAG20120907T055135
channel prmy3: datafile copy complete, elapsed time: 00:00:08
output file name=/u01/app/oracle/oradata/htz/users01.dbf tag=TAG20120907T055135
channel prmy4: datafile copy complete, elapsed time: 00:00:08
output file name=/u01/app/oracle/oradata/htz/sysaux01.dbf tag=TAG20120907T055135
channel prmy2: datafile copy complete, elapsed time: 00:00:56
output file name=/u01/app/oracle/oradata/htz/system01.dbf tag=TAG20120907T055135
channel prmy1: datafile copy complete, elapsed time: 00:01:06
sql statement: alter system archive log current
switch clone datafile all;
datafile 1 switched to datafile copy
input datafile copy RECID=1 STAMP=793304890 file name=/u01/app/oracle/oradata/htz/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=2 STAMP=793304890 file name=/u01/app/oracle/oradata/htz/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=3 STAMP=793304890 file name=/u01/app/oracle/oradata/htz/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=4 STAMP=793304890 file name=/u01/app/oracle/oradata/htz/users01.dbf
Finished Duplicate Db at 07-SEP-12
released channel: prmy1
released channel: prmy2
released channel: prmy3
released channel: prmy4
released channel: stby
RMAN> exit
Recovery Manager complete.
下面把備庫啟動到open only下面。并recover。
SQL> alter database open read only;
SQL> alter database recover managed standby database using current logfile disconnect from session;
在alert日志中可以看到下面的内容：
Waiting for all non-current ORLs to be archived...
All non-current ORLs have been archived.
Media Recovery Log /u01/app/oracle/arch/htz/1_6_793343515.dbf
Media Recovery Log /u01/app/oracle/arch/htz/1_7_793343515.dbf
Media Recovery Log /u01/app/oracle/arch/htz/1_8_793343515.dbf
Media Recovery Log /u01/app/oracle/arch/htz/1_9_793343515.dbf
Completed: ALTER DATABASE RECOVER managed standby database using current logfile disconnect
Media Recovery Waiting for thread 1 sequence 10 (in transit)
Recovery of Online Redo Log: Thread 1 Group 4 Seq 10 Reading mem 0
Mem# 0: /u01/app/oracle/oradata/htz/standby01.log
下面就是開始測試了。這裡我們是用的SCOTT這個使用者來做測試，在SCOTT使用者下面建立一個表，在備庫去查詢看是否建立成功，與在主庫上面SWIRCH一次日志，看alert中的日志輸出内容。
在主庫上面做測試
SQL*Plus: Release 11.2.0.2.0 Production on Fri Sep 7 05:57:41 2012
SQL> alter database set standby to maximize availability;
SQL> conn scott/tiger;
ERROR:
ORA-28000: the account is locked
Warning: You are no longer connected to ORACLE.
SQL> conn / as sysdba
Connected.
SQL> alter user scott identified by oracle;
User altered.
SQL> alter user scott account unlock;
SQL> create table scott.test1 as select * from dba_objects;
Table created.
備庫上面
SQL> select count(*) from scott.test1;
COUNT(*)
----------
72391
說明已經同步
主庫上面重新整理日志。
SQL> alter system archive log current;
日志
ALTER SYSTEM ARCHIVE LOG
Fri Sep 07 06:04:45 2012
LGWR: Standby redo logfile selected to archive thread 1 sequence 11
LGWR: Standby redo logfile selected for thread 1 sequence 11 for destination LOG_ARCHIVE_DEST_2
Thread 1 advanced to log sequence 11 (LGWR switch)
Current log# 2 seq# 11 mem# 0: /u01/app/oracle/oradata/htz/redo02.log
Fri Sep 07 06:04:48 2012
Archived Log entry 12 added for thread 1 sequence 10 ID 0x6e285e98 dest 1:
備庫日志
Standby controlfile consistent with primary
RFS[5]: Selected log 5 for thread 1 sequence 11 dbid 1848107928 branch 793343515
Thu Sep 06 18:40:13 2012
Media Recovery Waiting for thread 1 sequence 11 (in transit)
Recovery of Online Redo Log: Thread 1 Group 5 Seq 11 Reading mem 0
Mem# 0: /u01/app/oracle/oradata/htz/standby02.log
Thu Sep 06 18:40:16 2012
Archived Log entry 5 added for thread 1 sequence 10 ID 0x6e285e98 dest 1:
本文轉自7343696 51CTO部落格，原文連結：http://blog.51cto.com/luoping/983952，如需轉載請自行聯系原作者
![【MySQL資料庫】資料庫索引事務1.索引2.事務[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)