oracle11g 搭建 rac+dg
一、準備階段
1、配置RAC+DG的過程簡述（備機單節點，資料檔案可放至本地）
（1）根據主庫引數在備庫建立pfile引數檔案，密碼檔案和必要的目錄。
（2）在備庫修改pfile引數檔案，主庫上用rman duplicate拷貝至備庫。
（3）備庫開啟日誌應用。
（4）修改主庫引數（為switchover或failover準備）。
（5）再用switchover切換。
2、具體版本和配置情況
(1) rac配置
伺服器主機名 rac1 rac2
公共IP地址(eth0) 192.168.91.140 192.168.91.142
虛擬IP地址(eth0) 192.168.91.152 192.168.91.153
私有IP地址(eth1) 192.168.214.130 192.168.214.131
ORACLE RAC SID wxqyh1 wxqyh2
叢集例項名稱 wxqyh
SCAN IP 192.168.91.154
作業系統 CentOS 6.5
儲存 ASM
ORACLE 11.2.0.4
(2) 單節點（只安裝資料庫軟體）
伺服器主機名：racdg
作業系統： CentOS 6.5
ORACLE版本為：11.2.0.4
二、搭建RAC+DG
1、檢視OS和資料庫版本
1.1 檢視OS版本
[root@rac1 Desktop]# uname -a
Linux rac1 2.6.32-358.23.2.el6.x86_64 #1 SMP Wed Oct 16 11:16:45 PDT 2013 x86_64 x86_64 x86_64 GNU/Linux
1.2 檢視資料庫版本
[oracle@rac1 ~]$ sqlplus / as sysdba
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE 11.2.0.4.0 Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production
1.3 檢視叢集情況
[root@rac1 Desktop]# crsctl stat res -t
--------------------------------------------------------------------------------
NAME TARGET STATE SERVER STATE_DETAILS
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.DATA.dg
ONLINE ONLINE rac1
ONLINE ONLINE rac2
ora.FRA.dg
ONLINE ONLINE rac1
ONLINE ONLINE rac2
ora.LISTENER.lsnr
ONLINE ONLINE rac1
ONLINE ONLINE rac2
ora.OCRVOTE.dg
ONLINE ONLINE rac1
ONLINE ONLINE rac2
ora.asm
ONLINE ONLINE rac1 Started
ONLINE ONLINE rac2 Started
ora.gsd
OFFLINE OFFLINE rac1
OFFLINE OFFLINE rac2
ora.net1.network
ONLINE ONLINE rac1
ONLINE ONLINE rac2
ora.ons
ONLINE ONLINE rac1
ONLINE ONLINE rac2
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.LISTENER_SCAN1.lsnr
1 ONLINE ONLINE rac2
ora.cvu
1 ONLINE ONLINE rac2
ora.oc4j
1 ONLINE ONLINE rac2
ora.rac1.vip
1 ONLINE ONLINE rac1
ora.rac2.vip
1 ONLINE ONLINE rac2
ora.scan1.vip
1 ONLINE ONLINE rac2
ora.wxqyh.db
1 ONLINE ONLINE rac1 Open
2 ONLINE ONLINE rac2 Open
2、備庫配置環境變數
[oracle@racdg ~]$ cat ~/.bash_profile
export TMP=/tmp
export TMPDIR=$TMP
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/dbhome_1
export ORACLE_SID=wxqyh
export ORACLE_OWNER=oracle
export PATH=/usr/sbin:/usr/bin:/bin:/usr/local/bin:$PATH
export PATH=$ORACLE_HOME/bin:$PATH
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib
export CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib
export NLS_DATE_FORMAT="YYYY-MM-DD HH24:MI:SS"
export NLS_LANG=AMERICAN_AMERICA.AL32UTF8
umask 022
[oracle@racdg ~]$ source ~/.bash_profile
3、從主庫rac1上拷貝密碼檔案到備庫（也可在備庫直接建立）
[oracle@rac1 ~]$ cd $ORACLE_HOME/dbs
[oracle@rac1 dbs]$ scp -r orapwwxqyh1 oracle@192.168.91.134:/u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwwxqyh
The authenticity of host '192.168.91.134 (192.168.91.134)' can't be established.
RSA key fingerprint is 46:e6:f8:dc:f3:83:18:57:52:8a:5b:68:a4:c0:32:10.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '192.168.91.134' (RSA) to the list of known hosts.
oracle@192.168.91.134's password:
orapwwxqyh1 100% 1536 1.5KB/s 00:00
[oracle@racdg dbs]$ ll $ORACLE_HOME/dbs/orapwwxqyh
-rw-r----- 1 oracle oinstall 1536 Apr 23 12:14 /u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwwxqyh
4、配置監聽
4.1 rac在TNS檔案追加備庫的連線串（兩個節點一樣），備庫新增所有連線串的內容。
[oracle@rac1 ~]$cd $ORACLE_HOME/network/admin
[oracle@rac1 admin]$ vi tnsnames.ora
[oracle@rac2 admin]$ vi tnsnames.ora
[oracle@racdg admin]$ vi tnsnames.ora
WXQYH =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.91.152)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.91.153)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = wxqyh)
)
)
WXQYH_DG =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.91.134)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SID = wxqyh)
)
)
4.2 在備庫配置listener.ora 檔案
[oracle@racdg admin]$ cat listener.ora
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = wxqyh_dg)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
(SID_NAME = wxqyh)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = racdg)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
ADR_BASE_LISTENER = /u01/app/oracle
4.3 rac重啟監聽,備庫啟動監聽
[oracle@racdg admin]$ lsnrctl start
LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 23-APR-2017 12:27:35
Copyright (c) 1991, 2013, Oracle. All rights reserved.
Starting /u01/app/oracle/product/11.2.0/dbhome_1/bin/tnslsnr: please wait...
TNSLSNR for Linux: Version 11.2.0.4.0 - Production
System parameter file is /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/racdg/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=racdg)(PORT=1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=racdg)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date 23-APR-2017 12:27:36
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/dbhome_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/racdg/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=racdg)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "wxqyh_dg" has 1 instance(s).
Instance "wxqyh", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
[oracle@rac1 admin]$ srvctl stop listener
[oracle@rac1 admin]$ srvctl start listener
[oracle@rac1 admin]$ lsnrctl status listener
LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 23-APR-2017 12:28:24
Copyright (c) 1991, 2013, Oracle. All rights reserved.
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date 23-APR-2017 12:28:09
Uptime 0 days 0 hr. 0 min. 15 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/11.2.0/grid/network/admin/listener.ora
Listener Log File /u01/app/grid/diag/tnslsnr/rac1/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.91.140)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.91.152)(PORT=1521)))
Services Summary...
Service "wxqyh" has 1 instance(s).
Instance "wxqyh1", status READY, has 1 handler(s) for this service...
Service "wxqyhXDB" has 1 instance(s).
Instance "wxqyh1", status READY, has 1 handler(s) for this service...
The command completed successfully
4.4 驗證連通性
分別在主與備三臺機器上執行連線命令(如果tnsnames.ora中配置時hostname,必須要識別對方主機名)
[oracle@rac1 admin]$ sqlplus sys/oracle4U@WXQYH_DG as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Sun Apr 23 12:30:14 2017
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to an idle instance.
[oracle@rac2 admin]$ sqlplus sys/oracle4U@WXQYH_DG as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Sun Apr 23 12:31:14 2017
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to an idle instance.
[oracle@racdg admin]$ sqlplus sys/oracle4U@WXQYH as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Sun Apr 23 12:32:53 2017
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
5、修改主庫引數，配置備庫引數
5.1 在主庫rac上任意一節點執行(本人習慣在第一節點執行)
[oracle@rac1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Sun Apr 23 12:42:40 2017
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> alter system set fal_client='wxqyh1' scope=both sid='wxqyh1';
System altered.
SQL> alter system set fal_client='wxqyh2' scope=both sid='wxqyh2';
System altered.
SQL> alter system set fal_server='wxqyh_dg';
System altered.
SQL> alter system set standby_file_management='AUTO';
System altered.
SQL> alter system set db_file_name_convert='+DATA/wxqyh/datafile','/u01/app/oracle/oradata/wxqyh' scope=spfile;
System altered.
SQL> alter system set log_file_name_convert='+DATA/wxqyh/onlinelog','/u01/app/oracle/oradata/wxqyh','+FRA/wxqyh/onlinelog','/u01/app/oracle/oradata/wxqyh' scope=spfile;
System altered.
SQL> alter system set log_archive_config='DG_CONFIG=(wxqyh,wxqyh_dg)';
System altered.
SQL> alter system set log_archive_dest_1='LOCATION=+DATA/wxqyh/archivelog VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=wxqyh' sid='wxqyh1';
System altered.
SQL> alter system set log_archive_dest_1='LOCATION=+DATA/wxqyh/archivelog VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=wxqyh' sid='wxqyh2';
System altered.
SQL> alter system set log_archive_dest_state_1='enable';
System altered.
SQL> alter system set log_archive_dest_2='SERVICE=wxqyh_dg lgwr sync affirm VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=wxqyh_dg';
System altered.
SQL> alter system set log_archive_dest_state_2='enable';
System altered.
注：
FAL_CLIENT specifies the FAL (fetch archive log) client name that is used by the FAL service, configured through the FAL_SERVER
parameter, to refer to the FAL client. The value is an Oracle Net service name, which is assumed to be configured properly on the
FAL server system to point to the FAL client (standby database).
5.2 重啟主資料庫，使引數生效
[oracle@rac1 ~]$ srvctl stop database -d wxqyh -o immediate
[oracle@rac1 ~]$ srvctl start database -d wxqyh -o open
6、建立引數檔案
6.1 建立引數檔案
[oracle@rac1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Sun Apr 23 12:48:10 2017
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 pfile='/home/oracle/initwxqyh.ora' from spfile;
File created.
6.2 檢視主庫的引數檔案配置：
[oracle@rac1 ~]$ cat /home/oracle/initburton.ora
wxqyh2.__db_cache_size=608174080
wxqyh1.__db_cache_size=608174080
wxqyh2.__java_pool_size=4194304
wxqyh1.__java_pool_size=4194304
wxqyh2.__large_pool_size=8388608
wxqyh1.__large_pool_size=8388608
wxqyh1.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
wxqyh2.__pga_aggregate_target=306184192
wxqyh1.__pga_aggregate_target=306184192
wxqyh2.__sga_target=918552576
wxqyh1.__sga_target=918552576
wxqyh2.__shared_io_pool_size=0
wxqyh1.__shared_io_pool_size=0
wxqyh2.__shared_pool_size=285212672
wxqyh1.__shared_pool_size=285212672
wxqyh2.__streams_pool_size=0
wxqyh1.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/wxqyh/adump'
*.audit_trail='db'
*.cluster_database=TRUE
*.cluster_database_instances=2
*.compatible='11.2.0.4.0'
*.control_files='+DATA/wxqyh/controlfile/current.263.941896511','+FRA/wxqyh/controlfile/current.256.941896511'#Restore Controlfile
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_domain=''
*.db_file_name_convert='+DATA/wxqyh/datafile','/u01/app/oracle/oradata/wxqyh'
*.db_name='wxqyh'
*.db_recovery_file_dest='+FRA'
*.db_recovery_file_dest_size=4421074432
*.diagnostic_dest='/u01/app/oracle'
*.disk_asynch_io=TRUE
*.dispatchers='(PROTOCOL=TCP) (SERVICE=wxqyhXDB)'
*.enable_goldengate_replication=TRUE
wxqyh1.fal_client='wxqyh1'
wxqyh2.fal_client='wxqyh2'
*.fal_server='wxqyh_dg'
*.filesystemio_options='SETALL'
wxqyh1.instance_name='wxqyh1'
wxqyh2.instance_name='wxqyh2'
wxqyh1.instance_number=1
wxqyh2.instance_number=2
*.log_archive_config='DG_CONFIG=(wxqyh,wxqyh_dg)'
*.log_archive_dest_1='LOCATION=+data/wxqyh/archivelog'
wxqyh1.log_archive_dest_1='LOCATION=+DATA/wxqyh/archivelog VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=wxqyh'
wxqyh2.log_archive_dest_1='LOCATION=+DATA/wxqyh/archivelog VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=wxqyh'
*.log_archive_dest_2='SERVICE=wxqyh_dg lgwr sync affirm VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=wxqyh_dg'
*.log_archive_dest_state_1='enable'
*.log_archive_dest_state_2='enable'
*.log_archive_format='arch_%t_%s_%r.arc'
*.log_file_name_convert='+DATA/wxqyh/onlinelog','/u01/app/oracle/oradata/wxqyh','+FRA/wxqyh/onlinelog','/u01/app/oracle/oradata/wxqyh'
*.open_cursors=300
*.pga_aggregate_target=302478720
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=917436160
*.standby_file_management='AUTO'
wxqyh1.thread=1
wxqyh2.thread=2
*.undo_management='AUTO'
wxqyh1.undo_tablespace='UNDOTBS1'
wxqyh2.undo_tablespace='UNDOTBS2'
注： wxqyh1.fal_client='wxqyh1'和 wxqyh2.fal_client='wxqyh2' 可以修改成 *.fal_client='wxqyh' 。
DG配置裡的 SERVICE 必須和TNSNAMES裡面對應
7、編輯備庫引數檔案配置
7.1 直接在備庫編輯或編輯主機pfile複製到備機(scp -r initwxqyh.ora oracle@192.168.91.134:/home/oracle/)
[oracle@racdg ~]$ vi /home/oracle/initwxqyh.ora
*.audit_file_dest='/u01/app/oracle/admin/wxqyh/adump'
*.audit_trail='db'
*.cluster_database=false
*.compatible='11.2.0.4.0'
*.control_files='/u01/app/oracle/oradata/wxqyh/control01.ctl','/u01/app/oracle/oradata/wxqyh/control02.ctl'
*.db_block_size=8192
*.db_create_file_dest='/u01/app/oracle/oradata/wxqyh'
*.db_domain=''
*.db_file_name_convert='+DATA/wxqyh/datafile','/u01/app/oracle/oradata/wxqyh'
*.db_name='wxqyh'
*.db_unique_name='wxqyh_dg'
*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'
*.db_recovery_file_dest_size=4621074432
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=wxqyhXDB)'
*.fal_server='wxqyh'
*.fal_client='wxqyh_dg'
*.log_archive_config='DG_CONFIG=(wxqyh,wxqyh_dg)'
*.log_archive_dest_1='LOCATION=/u01/app/oracle/archivelog VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=wxqyh_dg'
*.log_archive_dest_2='SERVICE=wxqyh lgwr sync affirm VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=wxqyh'
*.log_archive_dest_state_1='enable'
*.log_archive_dest_state_2='enable'
*.log_archive_format='arch_%t_%s_%r.arc'
*.log_file_name_convert='+DATA/wxqyh/onlinelog','/u01/app/oracle/oradata/wxqyh','+FRA/wxqyh/onlinelog','/u01/app/oracle/oradata/wxqyh'
*.memory_target=1189085184
*.open_cursors=300
*.processes=150
*.standby_file_management='AUTO'
*.undo_tablespace='UNDOTBS2'
注：
7.2 在備庫上建立所需的目錄
[oracle@racdg ~]$ mkdir -p /u01/app/oracle/oradata/wxqyh
[oracle@racdg ~]$ mkdir -p /u01/app/oracle/admin/wxqyh/adump
[oracle@racdg ~]$ mkdir -p /u01/app/oracle/fast_recovery_area
[oracle@racdg ~]$ mkdir -p /u01/app/oracle/archivelog
8、啟動備庫到nomount狀態
[oracle@racdg admin]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Mon Apr 24 10:19:19 2017
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup nomount pfile='/home/oracle/initwxqyh.ora';
ORACLE instance started.
Total System Global Area 1185853440 bytes
Fixed Size 2252664 bytes
Variable Size 754974856 bytes
Database Buffers 419430400 bytes
Redo Buffers 9195520 bytes
SQL> create spfile from pfile='/home/oracle/initwxqyh.ora';
File created.
SQL> shutdown abort
ORACLE instance shut down.
SQL> startup nomount
ORACLE instance started.
Total System Global Area 1185853440 bytes
Fixed Size 2252664 bytes
Variable Size 754974856 bytes
Database Buffers 419430400 bytes
Redo Buffers 9195520 bytes
9、新增日誌檔案
在主庫上新增standby 日誌組 新增的個數為：（主庫原來有的個數n+1）*執行緒數 ，這裡所說的執行緒數可以理解為例項的個數，這裡有10個日誌組，
所以要新增（10+1）*2=22組。大小和路徑最好和原來的保持一致，DG有兩種傳遞日誌的方式，一種是常見的archive log，由ARCH的後臺程式控制
傳遞到standby資料庫，還有一種是和redo log一樣的傳遞方式，由產生redo log的後臺程式LGWR控制，後者就需要standby log 在最大可用和最大
保護模式，因為是採用LGWR SYNC進行redo的傳送，一定要用standby logfile，但是建議在最大效能模式也新增standby logfile，據說在失敗切換
時可以恢復更多的資料。
9.1 檢視主庫日誌檔案
[oracle@rac1 ~]$ sqlplus / as sysdba
SQL> set pagesize 9999
SQL> col member for a50
SQL> select group#,member from v$logfile;
GROUP# MEMBER
---------- --------------------------------------------------
3 +DATA/wxqyh/onlinelog/group_3.256.941900533
2 +DATA/wxqyh/onlinelog/group_2.278.941900517
1 +DATA/wxqyh/onlinelog/group_1.273.941900501
1 +FRA/wxqyh/onlinelog/group_1.259.941900513
2 +FRA/wxqyh/onlinelog/group_2.258.941900527
3 +FRA/wxqyh/onlinelog/group_3.257.941900545
4 +DATA/wxqyh/onlinelog/group_4.281.941900935
4 +FRA/wxqyh/onlinelog/group_4.260.941900937
5 +DATA/wxqyh/onlinelog/group_5.262.941900943
5 +FRA/wxqyh/onlinelog/group_5.261.941900945
6 +DATA/wxqyh/onlinelog/group_6.271.941900951
6 +FRA/wxqyh/onlinelog/group_6.262.941900953
12 rows selected.
SQL> select GROUP#,THREAD#,BYTES/1024/1024 SM,MEMBERS,STATUS from v$log;
GROUP# THREAD# SM MEMBERS STATUS
---------- ---------- ---------- ---------- ----------------
1 1 50 2 ACTIVE
2 1 50 2 ACTIVE
3 1 50 2 CURRENT
4 2 50 2 CURRENT
5 2 50 2 INACTIVE
6 2 50 2 ACTIVE
9.2 新增命令如下(這為簡便處理新增standby日誌組只建立一個成員)
SQL> alter database add standby logfile thread 1 group 7 ('+DATA','+FRA') size 50m;
Database altered.
SQL> alter database add standby logfile thread 1 group 8 ('+DATA','+FRA') size 50m;
Database altered.
SQL> alter database add standby logfile thread 1 group 9 ('+DATA','+FRA') size 50m;
Database altered.
SQL> alter database add standby logfile thread 1 group 10 ('+DATA','+FRA') size 50m;
Database altered.
SQL> alter database add standby logfile thread 2 group 11 ('+DATA','+FRA') size 50m;
Database altered.
SQL> alter database add standby logfile thread 2 group 12 ('+DATA','+FRA') size 50m;
Database altered.
SQL> alter database add standby logfile thread 2 group 13 ('+DATA','+FRA') size 50m;
Database altered.
SQL> alter database add standby logfile thread 2 group 14 ('+DATA','+FRA') size 50m;
Database altered.
10、將主庫資料檔案複製到備庫
[oracle@rac1 dbs]$ rman target / auxiliary sys/oracle4U@wxqyh_dg nocatalog
Recovery Manager: Release 11.2.0.4.0 - Production on Mon Apr 24 16:19:22 2017
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: WXQYH (DBID=1110855286)
using target database control file instead of recovery catalog
connected to auxiliary database: WXQYH (not mounted)
RMAN> duplicate target database for standby from active database nofilenamecheck;
Starting Duplicate Db at 2017-04-24 16:19:35
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=20 device type=DISK
contents of Memory Script:
{
backup as copy reuse
targetfile '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwwxqyh1' auxiliary format
'/u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwwxqyh' ;
}
executing Memory Script
Starting backup at 2017-04-24 16:19:36
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=66 instance=wxqyh1 device type=DISK
Finished backup at 2017-04-24 16:19:38
contents of Memory Script:
{
backup as copy current controlfile for standby auxiliary format '/u01/app/oracle/oradata/wxqyh/control01.ctl';
restore clone controlfile to '/u01/app/oracle/oradata/wxqyh/control02.ctl' from
'/u01/app/oracle/oradata/wxqyh/control01.ctl';
}
executing Memory Script
Starting backup at 2017-04-24 16:19:39
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_wxqyh1.f tag=TAG20170424T161939 RECID=13 STAMP=942164384
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 2017-04-24 16:19:45
Starting restore at 2017-04-24 16:19:45
using channel ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: copied control file copy
Finished restore at 2017-04-24 16:19:47
contents of Memory Script:
{
sql clone 'alter database mount standby database';
}
executing Memory Script
sql statement: alter database mount standby database
RMAN-05529: WARNING: DB_FILE_NAME_CONVERT resulted in invalid ASM names; names changed to disk group only.
contents of Memory Script:
{
set newname for tempfile 2 to
"+data";
switch clone tempfile all;
set newname for datafile 1 to
"/u01/app/oracle/oradata/wxqyh/system.280.941898225";
set newname for datafile 2 to
"/u01/app/oracle/oradata/wxqyh/sysaux.265.941898351";
set newname for datafile 3 to
"/u01/app/oracle/oradata/wxqyh/undotbs1.276.941898351";
set newname for datafile 4 to
"/u01/app/oracle/oradata/wxqyh/users.261.941898351";
set newname for datafile 5 to
"/u01/app/oracle/oradata/wxqyh/wxqyh.275.941898225";
set newname for datafile 6 to
"/u01/app/oracle/oradata/wxqyh/undotbs02.dbf";
backup as copy reuse
datafile 1 auxiliary format
"/u01/app/oracle/oradata/wxqyh/system.280.941898225" datafile
2 auxiliary format
"/u01/app/oracle/oradata/wxqyh/sysaux.265.941898351" datafile
3 auxiliary format
"/u01/app/oracle/oradata/wxqyh/undotbs1.276.941898351" datafile
4 auxiliary format
"/u01/app/oracle/oradata/wxqyh/users.261.941898351" datafile
5 auxiliary format
"/u01/app/oracle/oradata/wxqyh/wxqyh.275.941898225" datafile
6 auxiliary format
"/u01/app/oracle/oradata/wxqyh/undotbs02.dbf" ;
sql 'alter system archive log current';
}
executing Memory Script
executing command: SET NEWNAME
renamed tempfile 2 to +data 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
Starting backup at 2017-04-24 16:19:59
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00005 name=+DATA/wxqyh/datafile/wxqyh.275.941898225
output file name=/u01/app/oracle/oradata/wxqyh/wxqyh.275.941898225 tag=TAG20170424T161959
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:02:05
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=+DATA/wxqyh/datafile/sysaux.265.941898351
output file name=/u01/app/oracle/oradata/wxqyh/sysaux.265.941898351 tag=TAG20170424T161959
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:45
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=+DATA/wxqyh/datafile/system.280.941898225
output file name=/u01/app/oracle/oradata/wxqyh/system.280.941898225 tag=TAG20170424T161959
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:55
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=+DATA/wxqyh/datafile/undotbs1.276.941898351
output file name=/u01/app/oracle/oradata/wxqyh/undotbs1.276.941898351 tag=TAG20170424T161959
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
input datafile file number=00006 name=+DATA/wxqyh/datafile/undotbs02.dbf
output file name=/u01/app/oracle/oradata/wxqyh/undotbs02.dbf tag=TAG20170424T161959
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=+DATA/wxqyh/datafile/users.261.941898351
output file name=/u01/app/oracle/oradata/wxqyh/users.261.941898351 tag=TAG20170424T161959
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 2017-04-24 16:25:09
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=13 STAMP=942164714 file name=/u01/app/oracle/oradata/wxqyh/system.280.941898225
datafile 2 switched to datafile copy
input datafile copy RECID=14 STAMP=942164714 file name=/u01/app/oracle/oradata/wxqyh/sysaux.265.941898351
datafile 3 switched to datafile copy
input datafile copy RECID=15 STAMP=942164714 file name=/u01/app/oracle/oradata/wxqyh/undotbs1.276.941898351
datafile 4 switched to datafile copy
input datafile copy RECID=16 STAMP=942164714 file name=/u01/app/oracle/oradata/wxqyh/users.261.941898351
datafile 5 switched to datafile copy
input datafile copy RECID=17 STAMP=942164714 file name=/u01/app/oracle/oradata/wxqyh/wxqyh.275.941898225
datafile 6 switched to datafile copy
input datafile copy RECID=18 STAMP=942164714 file name=/u01/app/oracle/oradata/wxqyh/undotbs02.dbf
Finished Duplicate Db at 2017-04-24 16:25:41
注：臨時表空間不會複製到備。
11、啟動備庫
SQL> shutdown abort
ORACLE instance shut down.
SQL> startup nomount
ORACLE instance started.
Total System Global Area 1185853440 bytes
Fixed Size 2252664 bytes
Variable Size 754974856 bytes
Database Buffers 419430400 bytes
Redo Buffers 9195520 bytes
SQL> alter database mount standby database;
Database altered.
SQL> alter database open;
Database altered.
SQL> recover managed standby database disconnect from session;
Media recovery complete.
SQL> alter database recover managed standby database cancel;
Database altered.
SQL> alter database recover managed standby database using current logfile disconnect from session;
Database altered.
12、檢視下備庫的資料檔案和日誌檔案
SQL> col file_name for a55;
SQL> col tablespace_name for a14
SQL> select file_name,tablespace_name from dba_data_files;
FILE_NAME TABLESPACE_NAM
------------------------------------------------------- --------------
/u01/app/oracle/oradata/wxqyh/users.261.941898351 USERS
/u01/app/oracle/oradata/wxqyh/undotbs1.276.941898351 UNDOTBS1
/u01/app/oracle/oradata/wxqyh/sysaux.265.941898351 SYSAUX
/u01/app/oracle/oradata/wxqyh/system.280.941898225 SYSTEM
/u01/app/oracle/oradata/wxqyh/wxqyh.275.941898225 WXQYH
/u01/app/oracle/oradata/wxqyh/undotbs02.dbf UNDOTBS2
6 rows selected.
SQL> set lines 250
SQL> set pagesize 9999
SQL> col member for a55
SQL> select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_
---------- ------- ------- ------------------------------------------------------- ---
3 ONLINE /u01/app/oracle/oradata/wxqyh/group_3.256.942163483 NO
2 ONLINE /u01/app/oracle/oradata/wxqyh/group_2.278.942163423 NO
1 ONLINE /u01/app/oracle/oradata/wxqyh/group_1.273.942163587 NO
2 ONLINE /u01/app/oracle/oradata/wxqyh/group_2.258.942163425 NO
1 ONLINE /u01/app/oracle/oradata/wxqyh/group_1.259.942163589 NO
3 ONLINE /u01/app/oracle/oradata/wxqyh/group_3.257.942163485 NO
7 STANDBY /u01/app/oracle/oradata/wxqyh/group_7.305.942163881 NO
4 ONLINE /u01/app/oracle/oradata/wxqyh/group_4.281.941900935 NO
4 ONLINE /u01/app/oracle/oradata/wxqyh/group_4.260.941900937 NO
5 ONLINE /u01/app/oracle/oradata/wxqyh/group_5.262.941900943 NO
5 ONLINE /u01/app/oracle/oradata/wxqyh/group_5.261.941900945 NO
6 ONLINE /u01/app/oracle/oradata/wxqyh/group_6.271.941900951 NO
6 ONLINE /u01/app/oracle/oradata/wxqyh/group_6.262.941900953 NO
7 STANDBY /u01/app/oracle/oradata/wxqyh/group_7.263.942163883 NO
8 STANDBY /u01/app/oracle/oradata/wxqyh/group_8.304.942163953 NO
8 STANDBY /u01/app/oracle/oradata/wxqyh/group_8.264.942163955 NO
9 STANDBY /u01/app/oracle/oradata/wxqyh/group_9.303.942163961 NO
9 STANDBY /u01/app/oracle/oradata/wxqyh/group_9.265.942163963 NO
10 STANDBY /u01/app/oracle/oradata/wxqyh/group_10.301.942163969 NO
10 STANDBY /u01/app/oracle/oradata/wxqyh/group_10.266.942163971 NO
11 STANDBY /u01/app/oracle/oradata/wxqyh/group_11.300.942163977 NO
11 STANDBY /u01/app/oracle/oradata/wxqyh/group_11.267.942163979 NO
12 STANDBY /u01/app/oracle/oradata/wxqyh/group_12.302.942163983 NO
12 STANDBY /u01/app/oracle/oradata/wxqyh/group_12.268.942163985 NO
13 STANDBY /u01/app/oracle/oradata/wxqyh/group_13.299.942163991 NO
13 STANDBY /u01/app/oracle/oradata/wxqyh/group_13.269.942163993 NO
14 STANDBY /u01/app/oracle/oradata/wxqyh/group_14.357.942163997 NO
14 STANDBY /u01/app/oracle/oradata/wxqyh/group_14.270.942164001 NO
28 rows selected.
SQL> select GROUP#,THREAD#,SEQUENCE#,BYTES,BLOCKSIZE,MEMBERS,ARCHIVED,STATUS from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS
---------- ---------- ---------- ---------- ---------- ---------- --- ----------------
1 1 52 52428800 512 2 YES CLEARING
2 1 53 52428800 512 2 YES CURRENT
3 1 53 52428800 512 2 YES CLEARING
4 2 44 52428800 512 2 YES CLEARING
5 2 44 52428800 512 2 YES CURRENT
6 2 43 52428800 512 2 YES CLEARING
6 rows selected.
13、驗證主備庫同步
12.1 檢查歸檔目錄是否有誤（主備庫都檢視）
SQL> set line 120
SQL> col dest_name for a20
SQL> select dest_name,error,status from v$archive_dest where error is not null;
no rows selected
注：正常，沒有錯誤。我安裝碰到問題一ORA-16191，見文章末尾。
12.2 在主庫手工切換歸檔
SQL> alter system switch logfile;
12.3 檢視主備庫歸檔情況(應用過去日誌應一致)
SQL> select max(sequence#) from v$archived_log where APPLIED='YES';
MAX(SEQUENCE#)
--------------
53
12.4 檢視備庫狀態
SQL> col dest_name for a20
select dest_name,status,recovery_mode from v$archive_dest_status;SQL>
DEST_NAME STATUS RECOVERY_MODE
-------------------- --------- -----------------------
LOG_ARCHIVE_DEST_1 VALID MANAGED REAL TIME APPLY
LOG_ARCHIVE_DEST_2 VALID IDLE
LOG_ARCHIVE_DEST_3 INACTIVE IDLE
......
STANDBY_ARCHIVE_DEST VALID IDLE
32 rows selected.
14、primary上配置最大可用模式：
SQL> alter database set standby database to maximize availability;
Database altered.
15、測試下資料
rac1建立表t10
SQL> create table t10 (id number ,name varchar2(12));
Table created.
SQL> insert into t10 values (1,'burton');
1 row created.
SQL> commit;
Commit complete.
rac2檢視錶t10
SQL> select * from t10;
ID NAME
---------- ------------
1 burton
racdg檢視錶t10
SQL> select * from t10;
ID NAME
---------- ------------
1 burton
二、主備切換
1、關閉rac2
[oracle@rac1 dbs]$ srvctl stop instance -d wxqyh -i wxqyh2
檢視資料庫情況
[oracle@rac1 dbs]$ srvctl status database -d wxqyh
Instance wxqyh1 is running on node rac1
Instance wxqyh2 is not running on node rac2
2、在RAC1 節點將主庫切換到備庫：
2.1 主庫切換成備庫
SQL> select database_role,protection_level,protection_mode from v$database;
DATABASE_ROLE PROTECTION_LEVEL PROTECTION_MODE
---------------- -------------------- --------------------
PRIMARY MAXIMUM AVAILABILITY MAXIMUM AVAILABILITY
SQL> select switchover_status from v$database;
SWITCHOVER_STATUS
--------------------
TO STANDBY
SQL> alter database commit to switchover to physical standby with session shutdown;
2.2 將備庫切換成主庫：
SQL> select database_role,protection_level,protection_mode from v$database;
DATABASE_ROLE PROTECTION_LEVEL PROTECTION_MODE
---------------- -------------------- --------------------
PHYSICAL STANDBY MAXIMUM AVAILABILITY MAXIMUM AVAILABILITY
SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;
SWITCHOVER_STATUS
--------------------
TO PRIMARY
SQL> select DATABASE_ROLE from v$database;
DATABASE_ROLE
----------------
PHYSICAL STANDBY
SQL> alter database commit to switchover to primary WITH SESSION SHUTDOWN;
SQL> alter database open;
2.3 新備庫啟動
SQL> startup nomount
ORACLE instance started.
Total System Global Area 914440192 bytes
Fixed Size 2258600 bytes
Variable Size 360712536 bytes
Database Buffers 545259520 bytes
Redo Buffers 6209536 bytes
SQL> alter database mount standby database;
Database altered.
SQL> alter database open read only;
Database altered.
SQL> recover managed standby database disconnect from session;
Media recovery complete.
SQL> alter database recover managed standby database cancel;
Database altered.
SQL> alter database recover managed standby database using current logfile disconnect from session;
Database altered.
3、驗證資料
3.1 新主庫建立表
SQL> create table t6 (id number);
Table created.
3.2 新備庫檢視
SQL> select * from t6;
no rows selected
問題一：
rac2報錯
SQL> select dest_name,error,status from v$archive_dest where error is not null;
DEST_NAME ERROR STATUS
-------------------- ----------------------------------------------------------------- ---------
LOG_ARCHIVE_DEST_2 ORA-16191: Primary log shipping client not logged on standby ERROR
日誌：
------------------------------------------------------------
Mon Apr 24 13:20:21 2017
Error 1017 received logging on to the standby
------------------------------------------------------------
Check that the primary and standby are using a password file
and remote_login_passwordfile is set to SHARED or EXCLUSIVE,
and that the SYS password is same in the password files.
returning error ORA-16191
Mon Apr 24 12:18:55 2017
Primary database is in MAXIMUM PERFORMANCE mode
RFS[3]: Assigned to RFS process 4674
RFS[3]: No standby redo logfiles available for thread 1
RFS[3]: Opened log for thread 1 sequence 21 dbid 1110855286 branch 941900499
Mon Apr 24 13:09:51 2017
Warning: VKTM detected a time drift.
Time drifts can result in an unexpected behavior such as time-outs. Please check trace file for more details.
解決方法：
應該是密碼檔案有問題，我統一下密碼，刪掉原來的密碼，將rac1的密碼檔案複製到rac2,racdg。
scp -r ./orapwwxqyh1 oracle@192.168.91.142:/u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwwxqyh2
scp -r ./orapwwxqyh1 oracle@192.168.91.134:/u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwwxqyh
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/30590361/viewspace-2137898/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Oracle RAC+DG搭建Oracle
- 11G RAC+DG搭建
- RAC+DG(asm單例項)ASM單例
- ORACLE19C RAC+DGOracle
- ORACLE RAC+DG調整redo大小Oracle
- 一步一步搭建oracle 11gR2 rac+dg之環境準備(二)Oracle
- Oracle RAC+DG 表空間擴容Oracle
- 一步一步搭建11gR2 rac+dg之配置單例項的DG(八)單例
- Oracle RAC+DG 調整redo/standby log fileOracle
- Docker安裝Oracle11gDockerOracle
- Oracle11g解除安裝Oracle
- oracle11g dataguard切換Oracle
- oracle RAC+DG 擴容ASM和表空間(Linux)OracleASMLinux
- oracle11g RAC新增節點Oracle
- Oracle RAC+DG巡檢常見問題彙總(一)Oracle
- readhalt7.4 安裝 oracle11gOracle
- Oracle11g 將於何時推出?Oracle
- oracle11G歸檔日誌管理Oracle
- oracle11g安裝和下載Oracle
- redhat7.6安裝Oracle11G RACRedhatOracle
- RedHat上靜默安裝Oracle11gRedhatOracle
- CentOS 7.2靜默安裝Oracle11gCentOSOracle
- oracle11g修改控制檔案路徑Oracle
- oracle11g: Scheduler Maintenance Tasks or Autotasks (Doc ID 756734.1)OracleAINaN
- Oracle DataGuard 主備切換 (switchover) oracle11gOracle
- linux環境下解除安裝oracle11gLinuxOracle
- 透過Kerberos認證訪問Oracle11gROSOracle
- oracle11g修改使用者名稱Oracle
- 靜默安裝Oracle11g資料庫Oracle資料庫
- oracle11g修改資料檔案路徑Oracle
- oracle11g客戶端靜默安裝Oracle客戶端
- 【PROFILE】Oracle11g密碼複雜度說明Oracle密碼複雜度
- Oracle11g ORA-03135: 連線失去聯絡Oracle
- Oracle11g生成手動的快照報告報錯Oracle
- Windows環境下Oracle11g安裝的問題WindowsOracle
- RedHat 7 靜默安裝Oracle11g的補充RedhatOracle
- Linux下利用指令碼靜默安裝Oracle11GLinux指令碼Oracle
- oracle11g啟動過程中載入配置檔案Oracle
- 【RMAN】Oracle11g透過rman升級到12cOracle