一、准备阶段
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/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/30590361/viewspace-2137898/