RAC+ADG(单节点ADG)

最新推荐文章于 2024-06-20 14:31:02 发布

BaldwinSu

最新推荐文章于 2024-06-20 14:31:02 发布

阅读量6.6k

点赞数 5

分类专栏： Data Guard 文章标签： Oracle RAC ADG

本文链接：https://blog.csdn.net/sudahai102448567/article/details/105221673

版权

Data Guard 专栏收录该内容

12 篇文章 4 订阅

订阅专栏

RAC+ADG有两种存储数据文件的形式，一个是将DG的数据文件放在ASM上，另一个是将数据文件放在本地磁盘上
本实验做的是数据放在本地磁盘上。
在搭建RAC+DG之前，请确保RAC是没有问题的
1.检查集群状态 grid用户下执行：

crsctl status res -t

2.前期规划

RAC Primary	RACDB1（主机）	RACDB2（主机）	备注
Public IP	192.168.1.130	192.168.1.140	24位掩码
Virtual IP	172.16.1.131	172.16.1.141	24位掩码
Instance	racdb1	racdb2
DB name	RACDB
DATA file	+DATA/racdb/datafile/
Control file	+DATA/racdb/controlfile/ +ARC/racdb/controlfile/
Redo Log file	+DATA/racdb/onlinelog/ +ARC/racdb/onlinelog/
db_unique_name	racdb
service_names	racdb
Oracle_Version	11.2.0.4.0

DATA file，Control file，Redo Log file根据自己的RAC来。
Standby 端

Single instance standby	主机名	备注
IP	192.168.1.160	24位掩码
Oracle_version	11.2.0.4.0
Instance	Racdb
DB name	RACDB
DB_unique_name	dg1
service_names	dg1
DATA file	/u01/app/oracle/oradata/dg1/
Control file	/u01/app/oracle/oradata/dg1/
Redo Log file	/u01/app/oracle/oradata/dg1/ /u01/app/oracle/oradata/arclog/	此arclog不是存放归档日志，只是按照RAC中两个不同的日志组路径，对应设置的

3.备库操作系统初始设置
①关闭防火墙

/etc/init.d/iptables stop
chkconfig iptables off

②关闭selinux

vim /etc/selinux/config 
SELINUX=disabled

③关闭networkmaanger

service NetworkManager stop
chkconfig NetworkManager off

④关闭NTP服务

service ntpd stop
chkconfig ntpd off

⑤配置yum源

mkdir /source
mount /dev/cdrom /source/

vim /etc/yum.repos.d/x.repo
----------------------------
[ok]
name=ok
baseurl=file:///source/
gpgcheck=0
enabled=1
---------------------------- 
yum clean all
yum list

⑥安装相关的软件包

yum install -y  readline* binutils compat-libstdc++ compat-libstdc++ elfutils-libelf elfutils-libelf-devel  expat gcc gcc-c++ glibc glibc glibc-common glibc-devel glibc-headers libaio libaio libaio-devel libaio-devel  libgcc libgcc libstdc++ libstdc++ libstdc++-devel  make pdksh sysstat unixODBC unixODBC unixODBC-devel unixODBC-devel  binutils  libaio-devel libaio elfutils-libelf-devel compat-libstdc++-33 libgcc  gcc gcc-c++ glibc sysstat libstdc++ libstdc++-devel  unixODBC-devel unixODBC

如果少包再试试下面的（通常是不用）：

yum install binutils compat-libcap1 compat-libstdc++-33 gcc  gcc-c++ glibc glibc-devel ksh  libgcc libstdc++ libstdc++-devel libaio libaio-devel make sysstat unixODBC-devel unixODBC

⑦建立oracle用户
所有节点root执行：

groupadd -g 1000 oinstall
groupadd -g 1201 asmdba
groupadd -g 1300 dba
groupadd -g 1301 oper
useradd -u 1101 -g oinstall -G dba,oper,asmdba -d /home/oracle -s /bin/bash -c "Oracle Software Owner" oracle
echo "oracle" | passwd --stdin oracle

配置oracle用户的环境变量（切换到oracle用户）

[oracle@up1 ~]$ vim .bash_profile

# .bash_profile
 
# Get the aliases and functions
if [ -f ~/.bashrc ]; then
       . ~/.bashrc
fi
 
# User specific environment and startup programs
 
PATH=$PATH:$HOME/bin
 
export PATH
export TMP=/tmp
export TMPDIR=$TMP
export ORACLE_SID=racdb1
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1
export ORACLE_UNQNAME=racdb
export TNS_ADMIN=$ORACLE_HOME/network/admin
export ORACLE_TERM=xterm
export PATH=/usr/sbin:$PATH
export PATH=$ORACLE_HOME/bin:/u01/app/11.2.0/grid/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_DATA_FORMAT='yyyy-mm-dd hh24:mi:ss'
alias sqlplus='/usr/local/bin/rlwrap sqlplus'
alias rman='/usr/local/bin/rlwrap rman'
umask 022

⑧配置限制参数和内核参数

限制参数
echo "oracle soft nproc 2047" >>/etc/security/limits.conf
echo "oracle hard nproc 16384" >>/etc/security/limits.conf 
echo "oracle soft nofile 1024" >>/etc/security/limits.conf
echo "oracle hard nofile 65536" >>/etc/security/limits.conf
echo "grid soft nproc 2047" >>/etc/security/limits.conf 
echo "grid hard nproc 16384" >>/etc/security/limits.conf
echo "grid soft nofile 1024" >>/etc/security/limits.conf
echo "grid hard nofile 65536" >>/etc/security/limits.conf
echo "session required /lib/security/pam_limits.so" >>/etc/pam.d/login
echo "session required pam_limits.so" >>/etc/pam.d/login

内核参数

echo "fs.aio-max-nr = 1048576" >> /etc/sysctl.conf
echo "fs.file-max = 6815744" >> /etc/sysctl.conf
echo "kernel.shmall = 2097152" >> /etc/sysctl.conf
echo "kernel.shmmax = 1054472192" >> /etc/sysctl.conf
echo "kernel.shmmni = 4096" >> /etc/sysctl.conf
echo "kernel.sem = 250 32000 100 128" >> /etc/sysctl.conf
echo "net.ipv4.ip_local_port_range = 9000 65500" >> /etc/sysctl.conf
echo "net.core.rmem_default = 262144" >> /etc/sysctl.conf
echo "net.core.rmem_max = 4194304" >> /etc/sysctl.conf
echo "net.core.wmem_default = 262144" >> /etc/sysctl.conf
echo "net.core.wmem_max = 1048586" >> /etc/sysctl.conf
echo "net.ipv4.tcp_wmem = 262144 262144 262144" >> /etc/sysctl.conf
echo "net.ipv4.tcp_rmem = 4194304 4194304 4194304" >> /etc/sysctl.conf
sysctl -p

⑨上传安装介质，安装和指定lib包
在备库上上传oracle安装包

[root@up1 src]# ls
p10404530_112030_Linux-x86-64_1of7.zip
p10404530_112030_Linux-x86-64_2of7.zip
p10404530_112030_Linux-x86-64_3of7.zip
rlwrap-0.32.tar.gz

tar zxvf rlwrap-0.32.tar.gz
unzip  p10404530_112040_Linux-x86-64_1of7.zip  -d  /usr/local/src
unzip  p10404530_112040_Linux-x86-64_2of7.zip  -d  /usr/local/src
unzip  p10404530_112040_Linux-x86-64_3of7.zip  -d  /usr/local/src

[root@up1 src]# ls
database
rlwrap-0.32
rlwrap-0.32.tar.gz

chown -R oracle:oinstall database

安装插件包 rlwrap-0.32.tar.gz

cd rlwrap-0.32
./configure
make ; make install

4.建立相关路径
备库：
数据文件，日志文件，控制文件全部放在这里（为了方便,生产环境不要这样,io压力大）

mkdir -p /u01/app/oracle/oradata/dg1/

登陆时的审计文件放到这里

mkdir -p /u01/app/oracle/admin/racdb/adump

记录核心dump的目录，如果oracle核心进程应为BUG等原因崩溃，会做内存的dump

mkdir -p /u01/app/oracle/admin/racdb/cdump

存放归档的路径

mkdir -p /u01/archivelog

rman备份存放在此

mkdir -p /home/oracle/backup

由于RAC日志组有两个路径，所以对应建立另一个路径

mkdir -p /u01/app/oracle/oradata/arclog/

$ORACLE_BASE，根据 .bash_profile来的（根据自己的来设置）

mkdir -p /u01/app/oracle

$ORACLE_HOME，也根据 .bash_profile 来的（根据自己的来设置）

mkdir -p /u01/app/oracle/product/11.2.0/db_1
chown -R oracle:oinstall /u01/
chown -R oracle:oinstall /u01/app/oracle
chmod -R 775 /u01

RAC1，RAC2：

su - oracle
mkdir -p /u01/app/oracle/admin/racdb/adump
mkdir -p /home/oracle/backup
root用户
mkdir -p /u01/archivelog
chown -R oracle:oinstall /u01/archivelog

5.备库安装ORACLE软件
安装方式两种：
1.图形化安装
2.静默安装
注意：
1.版本号要与主库一样
2.环境变量，内核参数要一致，相关路径要建立全。
6.RAC必须开启到归档模式（自行检查）

SQL> archive log list;
Database log mode	       Archive Mode
Automatic archival	             Enabled
Archive destination	       /u01/archivelog
Oldest online log sequence      27
Next log sequence to archive    28
Current log sequence	         28

7.配置监听（此步骤极为重要，配置错误会导致后续步骤报错）
监听路径：$ORACLE_HOME/network/admin/
RAC1和RAC2的listener.ora如下
oracle_home和dbname，sid_name根据自己的来

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
      (PROGRAM = extproc)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = racdb)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
      (SID_NAME = racdb)
    )
  )

DG1的listener.ora如下

LISTENER=
  (DESCRIPTION=
    (ADDRESS_LIST=
      (ADDRESS=(PROTOCOL=tcp)(HOST=172.16.13.30)(PORT=1521))
      (ADDRESS=(PROTOCOL=ipc)(KEY=extproc))))
SID_LIST_LISTENER=
  (SID_LIST=
    (SID_DESC=
      (GLOBAL_DBNAME=prod.neves.com)
      (ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1)
      (SID_NAME=prod))
    (SID_DESC=
      (SID_NAME=plsextproc)
      (ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1)
      (PROGRAM=extproc)))

RAC1，RAC2和DG1的tnsnames.ora 如下

racdb1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.130)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = racdb)
    )
  )
racdb2 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.140)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = racdb)
    )
  )
dg1 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.160)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = dg1)
    )
  )

测试监听是否正常

tnsping racdb1;tnsping racdb2;tnsping dg1 
--- 全部看到OK就代表正常

不正常的原因可能：
防火墙没关；监听配置文件格式错误；监听配置文件内容错误；监听服务没有开启（lsnrctl status查看）
8.将密码文件传到备库

scp orapwracdb1  192.168.1.160:$ORACLE_HOME/dbs/

—注意密码文件和参数文件传过去后要记得改名字，改成和SID一样的格式：orapw$ORACLE_SID
在RAC1/2、DG1上测试监听
rac1/2

sqlplus sys/oracle@racdb1 as sysdba
select instance_name from v$instance
sqlplus sys/oracle@racdb2 as sysdba
select instance_name from v$instance
sqlplus sys/oracle@dg1 as sysdba

DG1测试

sqlplus sys/oracle@racdb1 as sysdba
select instance_name from v$instance
sqlplus sys/oracle@racdb2 as sysdba
select instance_name from v$instance
sqlplus sys/oracle@dg1 as sysdba

9…RAC主节点开启开启Force logging

SQL> ALTER DATABASE FORCE LOGGING;

10.主库RAC1创建静态参数文件

SQL>  show parameter pfile

NAME			     TYPE	      VALUE
--------------    ----------- ----------------------------
spfile				string	   +DATA/racdb/spfileracdb.ora

SQL> create  pfile='/u01/app/oracle/product/11.2.0/db_1/dbs/initracdb1.ora' from spfile='+DATA/racdb/spfileracdb.ora';

主库进到ORACLE_HOME/dbs/下编辑静态参数文件

[oracle@up1 dbs]$ pwd
/u01/app/oracle/product/11.2.0/db_1/dbs

原参数不变，添加以下参数

*.db_unique_name=racdb
*.service_names=racdb
*.log_archive_config='DG_CONFIG=(racdb,dg1)'
*.log_archive_dest_1 = 'LOCATION=/u01/archivelog VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=racdb' 
*.log_archive_dest_2 = 'SERVICE=dg1 LGWR SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=dg1'
*.log_archive_dest_state_1=enable
*.log_archive_dest_state_2=enable
*.db_file_name_convert='+DATA/racdb/datafile/','/u01/app/oracle/oradata/dg1/'        ---由于数据文件放到备库本地，所以需要做映射
*.log_file_name_convert='+DATA/racdb/onlinelog/','/u01/app/oracle/oradata/dg1/'
*.standby_file_management=AUTO
*.FAL_SERVER=dg1
####以下三个参数主要针对日后的主备模式切换
racdb1.fal_client=racdb1 
####注意：fal_srver，fal_client其实是net server name，根据tnsnames.ora中的字符串来的。
racdb2.fal_client=racdb2  ####此参数写错，在备库启到open时候会报错(ora_10458)
SPFILE='+DATA/racdb/spfileracdb.ora'		# line added by Agent

完整参数如下

racdb2.__db_cache_size=385875968
racdb1.__db_cache_size=385875968
racdb2.__java_pool_size=4194304
racdb1.__java_pool_size=4194304
racdb2.__large_pool_size=8388608
racdb1.__large_pool_size=8388608
racdb2.__pga_aggregate_target=209715200
racdb1.__pga_aggregate_target=209715200
racdb2.__sga_target=629145600
racdb1.__sga_target=629145600
racdb2.__shared_io_pool_size=0
racdb1.__shared_io_pool_size=0
racdb2.__shared_pool_size=222298112
racdb1.__shared_pool_size=222298112
racdb2.__streams_pool_size=0
racdb1.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/racdb/adump'
*.audit_trail='db'
*.cluster_database=true
*.compatible='11.2.0.4.0'
*.control_files='+DATA/racdb/controlfile/current.260.951501039','+ARC/racdb/controlfile/current.256.951501039'
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_domain=''
*.db_name='racdb'
*.db_recovery_file_dest='+ARC'
*.db_recovery_file_dest_size=4621074432
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=racdbXDB)'
racdb1.instance_number=1
racdb2.instance_number=2
*.log_archive_format='%t_%s_%r.dbf'
*.open_cursors=300
*.pga_aggregate_target=209715200
*.processes=150
*.remote_listener='scanip:1521'
*.remote_login_passwordfile='exclusive'
*.sga_target=629145600
racdb2.thread=2
racdb1.thread=1
racdb1.undo_tablespace='UNDOTBS1'
racdb2.undo_tablespace='UNDOTBS2'
*.db_unique_name=racdb
*.service_names=racdb
*.log_archive_config='DG_CONFIG=(racdb,dg1)'
*.log_archive_dest_1 = 'LOCATION=/u01/archivelog VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=racdb' 
*.log_archive_dest_2 = 'SERVICE=dg1 LGWR SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=dg1'
*.log_archive_dest_state_1=enable
*.log_archive_dest_state_2=enable
*.db_file_name_convert='+DATA/racdb/datafile/','/u01/app/oracle/oradata/dg1/'
*.log_file_name_convert='+DATA/racdb/onlinelog/','/u01/app/oracle/oradata/dg1/'
*.standby_file_management=AUTO
*.FAL_SERVER=dg1
racdb1.fal_client=racdb1
racdb2.fal_client=racdb2
SPFILE='+DATA/racdb/spfileracdb.ora'		# line added by Agent

将静态参数文件传给备库

scp initracdb1.ora  192.168.1.160:$ORACLE_HOME/dbs/
---记得传过去后将参数文件改名

11.关闭RAC1和RAC2数据库

[grid@racdb1 ~]$ srvctl stop database -d racdb

[oracle@racdb1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production onTue Mar 17 18:18:09 2015
Copyright (c) 1982, 2009, Oracle.  All rights reserved.
Connected to an idle instance.
SQL> create  spfile='+DATA/racdb/spfileracdb.ora' from pfile='/u01/app/oracle/product/11.2.0/db_1/dbs/initracdb1.ora'
File created.

12.启动RAC1和RAC2数据库：

[grid@racdb1 ~]$ srvctl start database -d racdb

启动后登陆两个节点查看参数：

SQL> show parameter fal
NAME				     TYPE	 VALUE
--------------       ----------- ----------------
fal_client			   string	 racdb1
fal_server			   string	 dg1

SQL> show parameter archive
NAME				       TYPE	     VALUE
------------------------------------ ----------- ------------------------------
archive_lag_target		        integer	 0
log_archive_config		         string	 DG_CONFIG=(racdb,dg1)
log_archive_dest		         string
log_archive_dest_1		     string	 LOCATION=/u01/archivelog VALID
						             _FOR=(ALL_LOGFILES,ALL_ROLES)
						             DB_UNIQUE_NAME=racdb
log_archive_dest_10		     string
log_archive_dest_11		     string
log_archive_dest_12		     string
log_archive_dest_13		     string
log_archive_dest_14		     string

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_15		     string
log_archive_dest_16		     string
log_archive_dest_17		     string
log_archive_dest_18		     string
log_archive_dest_19		     string
log_archive_dest_2		     string	 SERVICE=dg1 LGWR SYNC AFFIRM V
						             ALID_FOR=(ONLINE_LOGFILES,PRIM
						             ARY_ROLE) DB_UNIQUE_NAME=dg1
log_archive_dest_20		     string
log_archive_dest_21		     string
log_archive_dest_22		     string

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_23		     string
log_archive_dest_24		     string
log_archive_dest_25		     string
log_archive_dest_26		     string
log_archive_dest_27		     string
log_archive_dest_28		     string
log_archive_dest_29		     string
log_archive_dest_3		     string
log_archive_dest_30		     string
log_archive_dest_31		     string
log_archive_dest_4		     string

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_5		     string
log_archive_dest_6		     string
log_archive_dest_7		     string
log_archive_dest_8		     string
log_archive_dest_9		     string
log_archive_dest_state_1	     string	 enable
log_archive_dest_state_10	     string	 enable
log_archive_dest_state_11	     string	 enable
log_archive_dest_state_12	     string	 enable
log_archive_dest_state_13	     string	 enable
log_archive_dest_state_14	     string	 enable

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_state_15	     string	 enable
log_archive_dest_state_16	     string	 enable
log_archive_dest_state_17	     string	 enable
log_archive_dest_state_18	     string	 enable
log_archive_dest_state_19	     string	 enable
log_archive_dest_state_2	     string	 enable
log_archive_dest_state_20	     string	 enable
log_archive_dest_state_21	     string	 enable
log_archive_dest_state_22	     string	 enable
log_archive_dest_state_23	     string	 enable
log_archive_dest_state_24	     string	 enable

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_state_25	     string	 enable
log_archive_dest_state_26	     string	 enable
log_archive_dest_state_27	     string	 enable
log_archive_dest_state_28	     string	 enable
log_archive_dest_state_29	     string	 enable
log_archive_dest_state_3	     string	 enable
log_archive_dest_state_30	     string	 enable
log_archive_dest_state_31	     string	 enable
log_archive_dest_state_4	     string	 enable
log_archive_dest_state_5	     string	 enable
log_archive_dest_state_6	     string	 enable

NAME				            TYPE	     VALUE
-------------------------   ----------- ----------------
log_archive_dest_state_7	     string	 enable
log_archive_dest_state_8	     string	 enable
log_archive_dest_state_9	     string	 enable
log_archive_duplex_dest 	     string
log_archive_format		     string	 %t_%s_%r.dbf
log_archive_local_first 	        boolean	 TRUE
log_archive_max_processes	     integer	 4
log_archive_min_succeed_dest	 integer	 1
log_archive_start		        boolean	 FALSE
log_archive_trace		         integer	 0
standby_archive_dest		     string	 ?/dbs/arch

SQL> show parameter log_file_name_convert
NAME				      TYPE	   VALUE
----------------------- ----------- ----------------
log_file_name_convert	 string	 +DATA/racdb/onlinelog/, /u01/app/oracle/oradata/dg1/

13.在RAC1上用rman进行备份

[oracle@up1 backup]$ rman target /
Recovery Manager:Release 11.2.0.1.0 - Production on Tue Mar 17 18:58:43 2015
Copyright (c) 1982,2009, Oracle and/or its affiliates.  Allrights reserved.
connected to targetdatabase: RACDG (DBID=1109864007)
RMAN> backup database format'/home/oracle/backup/cc_%u.bak ';
 
Starting backup at17-MAR-15
using targetdatabase control file instead of recovery catalog
allocated channel:ORA_DISK_1
channel ORA_DISK_1:SID=32 instance=racdg1 device type=DISK
channel ORA_DISK_1:starting full datafile backup set
channel ORA_DISK_1:specifying datafile(s) in backup set
input datafile filenumber=00001 name=+DATA1/racdg/datafile/system.292.874603637
input datafile filenumber=00002 name=+DATA1/racdg/datafile/sysaux.293.874603643
input datafile filenumber=00003 name=+DATA1/racdg/datafile/undotbs1.294.874603645
input datafile filenumber=00005 name=+DATA1/racdg/datafile/undotbs2.300.874604699
input datafile filenumber=00004 name=+DATA1/racdg/datafile/users.295.874603647
channel ORA_DISK_1:starting piece 1 at 17-MAR-15
channel ORA_DISK_1:finished piece 1 at 17-MAR-15
piecehandle=/home/oracle/db_backup/racdbfull_20150317_874609135_01q22uff_1_1tag=TAG20150317T185854 comment=NONE
channel ORA_DISK_1:backup set complete, elapsed time: 00:02:45
channel ORA_DISK_1:starting full datafile backup set
channel ORA_DISK_1:specifying datafile(s) in backup set
including currentcontrol file in backup set
including currentSPFILE in backup set
channel ORA_DISK_1:starting piece 1 at 17-MAR-15
channel ORA_DISK_1:finished piece 1 at 17-MAR-15
piece handle=/home/oracle/db_backup/racdbfull_20150317_874609302_02q22ukm_2_1tag=TAG20150317T185854 comment=NONE
channel ORA_DISK_1:backup set complete, elapsed time: 00:00:16
Finished backup at17-MAR-15
RMAN>

14.为standby db创建controlfile

sqlplus / as sysdba
SQL> ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/home/oracle/backup/racdb.ctl';
Database altered.
SQL> alter system switch logfile;
System altered.

15.将备份考到备库的相应路径下

scp  /home/oracle/backup/* 192.168.1.160:/home/oracle/backup

16.修改备库的静态参数文件
在备库上去掉primary parameter中的内容如下：

*.cluster_database=TRUE
racdg1.instance_number=1
racdg2.instance_number=2
*.remote_listener='scan.localdomain:1521'
*.db_create_file_dest='+DATA1'
*.cluster_database=true
*.memory_target=842006528
*.db_recovery_file_dest='+DATA3'
*.db_recovery_file_dest_size=4070572032

添加修改参数：

*.pga_aggregate_target=339738624
*.sga_target=503316480
*.audit_file_dest='' /u01/app/oracle/admin/racdb/adump
*.core_dump_dest='' /u01/app/oracle/admin/racdb/cdump
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files= ' /u01/app/oracle/oradata/dg1/control01.ctl ',' /u01/app/oracle/oradata/dg1/control02.ctl '
*.db_block_size=8192
*.db_domain=''
*.db_name='racdg'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP)(SERVICE=racdgXDB)'
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='exclusive'
*.thread=1
*.undo_tablespace='UNDOTBS1'
*.db_unique_name='racdb'
*.service_names='racdb'
*.log_archive_config='dg_config=(racdb,dg1)'
*.log_archive_dest_1= 'LOCATION=/u01/archivelog VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=dg1'
*.log_archive_dest_2='SERVICE=racdb1 LGWR SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=racdb'
*.log_archive_dest_state_1='enable'
*.log_archive_dest_state_2='enable'
*.log_archive_format='%t_%s_%r.dbf'
*.log_file_name_convert= '+DATA/racdb/onlinelog/','/u01/app/oracle/oradata/dg1/','+ARC/racdb/onlinelog/','/u01/app/oracle/oradata/arclog/'
*.db_file_name_convert= '+DATA/racdb/datafile/','/u01/app/oracle/oradata/dg1/'
*.log_archive_max_processes=30
*.standby_file_management='auto'
*.FAL_SERVER='racdb1','racdb2' ###写的是tnsname里字符串的名字 就是net server name
*.fal_client='dg1'   #####同上

完整参数如下
注意：不要直接无脑复制，因为每个环境都不一样，以下仅作为参考

*.__db_cache_size=385875968
*.__java_pool_size=4194304
*.__large_pool_size=8388608
*.__pga_aggregate_target=209715200
*.__sga_target=629145600
*.__shared_io_pool_size=0
*.__shared_pool_size=222298112
*.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/racdb/adump'
*.core_dump_dest='/u01/app/oracle/admin/racdb/cdump'
*.audit_trail='db'
*.compatible='11.2.0.4.0'
*.control_files='/u01/app/oracle/oradata/dg1/control01.ctl','/u01/app/oracle/oradata/dg1/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='racdb'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=racdbXDB)'
*.log_archive_format='%t_%s_%r.dbf'
*.open_cursors=300
*.pga_aggregate_target=209715200
*.processes=150
*.remote_login_passwordfile='exclusive'
*.sga_target=629145600
*.thread=1
*.undo_tablespace='UNDOTBS1'
*.db_unique_name='dg1'
*.service_names='dg1'
*.log_archive_config='DG_CONFIG=(racdb,dg1)'
*.log_archive_dest_1 = 'LOCATION=/u01/archivelog VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=dg1'
*.log_archive_dest_2 = 'SERVICE=racdb1 LGWR SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=racdb'
*.log_archive_dest_state_1=enable
*.log_archive_dest_state_2=enable
*.db_file_name_convert='+DATA/racdb/datafile/','/u01/app/oracle/oradata/dg1/'
*.log_file_name_convert='+DATA/racdb/onlinelog/','/u01/app/oracle/oradata/dg1/','+ARC/racdb/onlinelog/','/u01/app/oracle/oradata/arclog/'
*.standby_file_management=AUTO
*.FAL_SERVER='racdb1','racdb2'               
*.fal_client='dg1'

17.备库开启到nomount

SQL> startup pfile='/u01/app/oracle/product/11.2.0/db_1/dbs/initdg1.ora' nomount;
ORACLE instance started.
Total System Global Area  217157632 bytes
Fixed Size                  2211928 bytes
Variable Size             159387560 bytes
Database Buffers           50331648 bytes
Redo Buffers                5226496 bytes

创建spfile

SQL>
create spfile='/u01/app/oracle/product/11.2.0/db_1/dbs/spfiledg1.ora' from pfile='/u01/app/oracle/product/11.2.0/db_1/dbs/initdg1.ora';

18.查看相关参数是否生效

SQL> show parameter fal
NAME				     TYPE	 VALUE
-------------       ----------- ------------
fal_client			    string	 dg1
fal_server			    string	 racdb1, racdb2

SQL> show parameter archive
NAME				        TYPE	 VALUE
------------------------------------ ----------- ----------------
archive_lag_target		         integer	 0
log_archive_config		         string	 DG_CONFIG=(racdb,dg1)
log_archive_dest		         string
log_archive_dest_1		     string	 LOCATION=/u01/archivelog VALID
						             _FOR=(ALL_LOGFILES,ALL_ROLES)
						             DB_UNIQUE_NAME=dg1
log_archive_dest_10		     string
log_archive_dest_11		     string
log_archive_dest_12		     string
log_archive_dest_13		     string
log_archive_dest_14		     string

NAME				     TYPE	 VALUE
------------------------------------ ----------- ----------------
log_archive_dest_15		     string
log_archive_dest_16		     string
log_archive_dest_17		     string
log_archive_dest_18		     string
log_archive_dest_19		     string
log_archive_dest_2		     string	 SERVICE=racdb1 LGWR SYNC AFFIR
						             M VALID_FOR=(ONLINE_LOGFILES,P
						             RIMARY_ROLE) DB_UNIQUE_NAME=racdb
log_archive_dest_20		     string
log_archive_dest_21		     string

NAME				     TYPE	 VALUE
------------------------------------ ----------- ----------------
log_archive_dest_22		     string
log_archive_dest_23		     string
log_archive_dest_24		     string
log_archive_dest_25		     string
log_archive_dest_26		     string
log_archive_dest_27		     string
log_archive_dest_28		     string
log_archive_dest_29		     string
log_archive_dest_3		     string
log_archive_dest_30		     string
log_archive_dest_31		     string

NAME				     TYPE	 VALUE
------------------------------------ ----------- ----------------
log_archive_dest_4		     string
log_archive_dest_5		     string
log_archive_dest_6		     string
log_archive_dest_7		     string
log_archive_dest_8		     string
log_archive_dest_9		     string
log_archive_dest_state_1	     string	 enable
log_archive_dest_state_10	     string	 enable
log_archive_dest_state_11	     string	 enable
log_archive_dest_state_12	     string	 enable
log_archive_dest_state_13	     string	 enable

NAME				     TYPE	 VALUE
------------------------------------ ----------- ----------------
log_archive_dest_state_14	     string	 enable
log_archive_dest_state_15	     string	 enable
log_archive_dest_state_16	     string	 enable
log_archive_dest_state_17	     string	 enable
log_archive_dest_state_18	     string	 enable
log_archive_dest_state_19	     string	 enable
log_archive_dest_state_2	     string	 enable
log_archive_dest_state_20	     string	 enable
log_archive_dest_state_21	     string	 enable
log_archive_dest_state_22	     string	 enable
log_archive_dest_state_23	     string	 enable

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_state_24	     string	 enable
log_archive_dest_state_25	     string	 enable
log_archive_dest_state_26	     string	 enable
log_archive_dest_state_27	     string	 enable
log_archive_dest_state_28	     string	 enable
log_archive_dest_state_29	     string	 enable
log_archive_dest_state_3	     string	 enable
log_archive_dest_state_30	     string	 enable
log_archive_dest_state_31	     string	 enable
log_archive_dest_state_4	     string	 enable
log_archive_dest_state_5	     string	 enable

NAME				     TYPE	 VALUE
------------------------------------ ----------- ----------------
log_archive_dest_state_6	     string	 enable
log_archive_dest_state_7	     string	 enable
log_archive_dest_state_8	     string	 enable
log_archive_dest_state_9	     string	 enable
log_archive_duplex_dest 	     string
log_archive_format		     string	 %t_%s_%r.dbf
log_archive_local_first 	     boolean	 TRUE
log_archive_max_processes	     integer	 4
log_archive_min_succeed_dest	     integer	 1
log_archive_start		     boolean	 FALSE
log_archive_trace		     integer	 0

NAME				     TYPE	 VALUE
------------------------------------ ----------- ----------------
standby_archive_dest		     string	 ?/dbs/arch

19.在备库上用rman恢复数据

[oracle@standydb~]$ rman target sys/密码@racdb1 auxiliary  sys/密码@dg1
Recovery Manager:Release 11.2.0.1.0 - Production on Tue Mar 17 20:22:42 2015
Copyright (c) 1982,2009, Oracle and/or its affiliates.  Allrights reserved.
connected to targetdatabase: RACDB (DBID=1109864007)
connected toauxiliary database: RACDB (not mounted)
RMAN>duplicate target database for standby from active database nofilenamecheck;  
--如果遇到ora-17628，则是相关路径没有建立rman里查看report schema
看到Finished DuplicateDb at 17-MAR-15代表成功。

20.在主库和备库上分别添加备库重做日志组
RAC有两个redo thread,每个thread有两个日志组，每个日志组有一个日志文件，文件大小为50M。
主库查看

SQL> select thread#,group#,members,bytes/1024/1024 from v$log;
   THREAD#     GROUP#    MEMBERS BYTES/1024/1024
---------- ---------- ---------- ---------------
         1          1          1              50
         1          2          1              50
         2          3          1              50
         2          4          1              50

SQL> select GROUP#,STATUS,TYPE,MEMBER from v$logfile;
    GROUP# STATUS  TYPE    MEMBER
---------- ------- ------- --------------------------------------
         2         ONLINE  +DATA/paydb/onlinelog/group_2.262.927484759
         1         ONLINE  +DATA/paydb/onlinelog/group_1.261.927484751
         3         ONLINE  +DATA/paydb/onlinelog/group_3.266.927485173
         4         ONLINE  +DATA/paydb/onlinelog/group_4.267.927485181

主库添加备库重做日志组

alter database add standby logfile thread 1 group 5 size 50M,group 6 size 50M;
alter database add standby logfile thread 2 group 7 size 50M,group 8 size 50M;

备库查看状态

SQL> select instance_name,status from v$instance;
 INSTANCE_NAME    STATUS
---------------- ------------
racdg            MOUNTED

SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;
SWITCHOVER_STATUS
--------------------
RECOVERY NEEDED

备库添加备库重做日志组

alter database add standby logfile thread 1 group 5('/u01/app/oracle/oradata/dg1/styredo05.log','/u01/app/oracle/oradata/arclog/styarc05.log') size 50M;
alter database add standby logfile thread 1 group 6('/u01/app/oracle/oradata/dg1/styredo06.log','/u01/app/oracle/oradata/arclog/styarc06.log') size 50M;
alter database add standby logfile thread 2 group 7('/u01/app/oracle/oradata/dg1/styredo07.log','/u01/app/oracle/oradata/arclog/styarc07.log') size 50M;
alter database add standby logfile thread 2 group 8('/u01/app/oracle/oradata/dg1/styredo08.log','/u01/app/oracle/oradata/arclog/styarc08.log') size 50M;

以下是关于备库重做日志组的要求：

--a)、确保standbyredo log的大小与主库online redolog的大小一致
--b)、如主库为单实例数据库：standbyredo log组数=主库日志组总数+1?
--c)、如果主库是RAC数据库：standby redo log组数=(每线程的日志组数+1)*线程数?
--d)、不建议复用standbyredo log，避免增加额外的I/O以及延缓重做传输

为了确保安全，上述试验并没有按照B和C的要求来，有兴趣的同学可以试一试按照上述要求来会不会出错误。
21.在standby端开启实时日志应用

recover managed standby database using current logfile disconnect from session;  
Media recovery complete.

22.测试ADG
①执行日志切换测试（在pri端切换归档，在节点二上检查是否也发生了切换）
–primary执行日志切换

archive log list; 
Current log sequence          18

alter system switch logfile;

archive log list;
Current log sequence          19

–standby查看日志的sequence号也跟着变了

archive log list;
Current log sequence          19

②查看standby启动的DG进程

select process,client_process,sequence#,status from v$managed_standby;  
PROCESS  CLIENT_P  SEQUENCE# STATUS  
--------- -------- ---------- ------------  
ARCH      ARCH            23 CLOSING  
ARCH      ARCH              0 CONNECTED            //归档进程  
ARCH      ARCH            21 CLOSING  
ARCH      ARCH              0 CONNECTED  
RFS      ARCH              0 IDLE  
RFS      UNKNOWN          0 IDLE  
RFS      LGWR            24 IDLE                  //归档传输进程  
RFS      UNKNOWN          0 IDLE  
MRP0      N/A              24 APPLYING_LOG      //日志应用进程       ---这个地方要是appying log就对了

③查看数据库的保护模式
#primary 端查看，我们可以看到数据库的保护模式为最大性能

select database_role,protection_mode,protection_level,open_mode from v$database;  
  
DATABASE_ROLE    PROTECTION_MODE      PROTECTION_LEVEL    OPEN_MODE  
---------------- -------------------- -------------------- --------------------  
PRIMARY          MAXIMUM PERFORMANCE  MAXIMUM PERFORMANCE  READ WRITE

#standby 端查看，也是一样的。

select database_role,protection_mode,protection_level,open_mode from v$database;  
  
DATABASE_ROLE    PROTECTION_MODE      PROTECTION_LEVEL    OPEN_MODE  
---------------- -------------------- -------------------- --------------------  
PHYSICAL STANDBY MAXIMUM PERFORMANCE  MAXIMUM PERFORMANCE  MOUNTED

④Open Read Only standby数据库并且开启实时日志应用

shutdown immediate  
startup                 
select OPEN_MODE from v$database ;            
OPEN_MODE
--------------------
READ ONLY

注意：如果无法启动—如果无法open，很有可能参数文件的fal_server和fal_client与tnsnames.ora的字符串不符合会报ora_10458

select database_role,protection_mode,protection_level,open_mode from v$database;  
DATABASE_ROLE    PROTECTION_MODE      PROTECTION_LEVEL    OPEN_MODE  
---------------- -------------------- -------------------- --------------------  
PHYSICAL STANDBY MAXIMUM PERFORMANCE  MAXIMUM PERFORMANCE  READ ONLY

select process,client_process,sequence#,status from v$managed_standby;  
PROCESS  CLIENT_P  SEQUENCE# STATUS  
--------- -------- ---------- ------------  
ARCH      ARCH              0 CONNECTED  
ARCH      ARCH              0 CONNECTED  
ARCH      ARCH              0 CONNECTED  
ARCH      ARCH            26 CLOSING  
RFS      ARCH              0 IDLE  
RFS      UNKNOWN          0 IDLE  
RFS      LGWR            27 IDLE

⑤开启到read only with apply模式

recover managed standby database using current logfile disconnect from session;  
select OPEN_MODE from v$database ;
OPEN_MODE
--------------------
READ ONLY WITH APPLY

解锁scott用户，添加数据，验证数据是否能同步
#在primary端创建解锁scott用户并创建测试表
#primary 端操作如下内容

alter user scott account unlock identified by tiger;  
conn scott/tiger;  

create table d1 as select * from dept;  
  
update d1 set deptno=90;
  
commit;  
select * from d1;

#standby端查询scott用户是否解锁，以及d1表是否创建并且更新数据：

conn scott/tiger;  
select * from tab;    
  
TNAME                          TABTYPE  CLUSTERID  
------------------------------ ------- ----------  
BONUS                          TABLE  
DEPT                          TABLE  
EMP                            TABLE  
SALGRADE                      TABLE  
d1                        TABLE  
  
select * from d1;

#至此Oracle 11g RAC+ADG配置完成
以下是主备模式切换，本人并没有做过以下试验，有兴趣的同学可以自己试验一下。注意路径要正确。
1、因环境中primaryDB是双节点Rac集群，standbyDB为单节点。在切换时需要将rac节点中关闭racdb2节点实例。对于为什么关闭racdb2节点，原因是在standbyDB Initialization Parameters 中配置接收点为racdb1

SQL>show parameter log_archive_dest_2
 
NAME                            TYPE                          VALUE
-------------------------------- ------------------------------
log_archive_dest_2         string      service=racdb1 async valid_for=(online_logfiles,primary_role)
db_unique_name=racdg
[grid@racdg2 ~]$ srvctl stop instance -d racdg -i racdb2
查看alter_racdb2.log

在这里插入图片描述
2、检查主库switchover_status
PrimaryDB

SQL> select SWITCHOVER#,SWITCHOVER_STATUS,DATABASE_ROLE from v$database;

SWITCHOVER#       SWITCHOVER_STATUS    DATABASE_ROLE
------------------   ------------- ----------------
 1110966030              TO STANDBY           PRIMARY
StandbyDB
SQL> select SWITCHOVER#,SWITCHOVER_STATUS,DATABASE_ROLE from v$database;
 
SWITCHOVER#SWITCHOVER_STATUS    DATABASE_ROLE
------------------------------- ----------------
 1110966030    NOT ALLOWED           PHYSICAL STANDBY

3、开启式切换primaryDB

ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBYWITH SESSION SHUTDOWN;

切换完成后，查看实例状态
在这里插入图片描述
4、关闭实例racdg1并启动到mount状态

SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MOUNT;

5、查看standbyDB 状态，并查看

SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;
 
SWITCHOVER_STATUS
--------------------
TOPRIMARY

6、切换physicalstandby DB to prmary role

ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN;

7、创建接收日志文件
查看logfile
在这里插入图片描述
添加日志文件

ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 5
('+DATA1/racdg/onlinelog/slog5_1.rdo','+DATA2/racdg/onlinelog/slog5_2.rdo') SIZE 50M;
 
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 6
  ('+DATA1/racdg/onlinelog/slog6_1.rdo','+DATA2/racdg/onlinelog/slog6_2.rdo') SIZE 50M;
 
ALTER DATABASE ADD STANDBY LOGFILE THREAD 2 GROUP 7
  ('+DATA1/racdg/onlinelog/slog7_1.rdo','+DATA2/racdg/onlinelog/slog7_2.rdo') SIZE 50M;
  
ALTER DATABASE ADD STANDBY LOGFILE THREAD 2 GROUP 8
  ('+DATA1/racdg/onlinelog/slog8_1.rdo','+DATA2/racdg/onlinelog/slog8_2.rdo') SIZE 50M;

8、打开新的primaryDB

SQL> alter database open;

9、在新的physicalstandby DB上Start redo log

SQL>ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE
  2  DISCONNECT FROM SESSION;
 
Database altered.

10、查看状态
新的physical standby DB
在这里插入图片描述
新的primary DB

到此为止，切换已经完成。
查看并验证：
第一种：
在新的primary DB 节点上查看

SQL> SELECT SEQUENCE#,APPLIED FROMV$ARCHIVED_LOG ORDER BY SEQUENCE#;
SQL> alter system switch logfile;

在这里插入图片描述
在新的standby DB 节点上查看

SQL> SELECT SEQUENCE#,APPLIED FROMV$ARCHIVED_LOG ORDER BY SEQUENCE#;

在这里插入图片描述
这说明包括primary 上做了一次alter system switch logfile之后的142，和140 、141 一并同步过来了。
第二种：
删除表空间DBF

drop tablespace VAV1 including contents anddatafiles cascade constraints;

1、查看
New primary 节点
在这里插入图片描述
New physical standby DB

2、删除VAV1表空间

drop tablespace VAV1 including contentsand datafiles cascade constraints;

3、在new physicalstandby DB 节点查看
在这里插入图片描述
到此说明switchover primary to standby 成功

BaldwinSu

关注

5
点赞
踩
18

收藏

觉得还不错? 一键收藏
1
评论
RAC+ADG(单节点ADG)

RAC+ADG有两种存储数据文件的形式，一个是将DG的数据文件放在ASM上，另一个是将数据文件放在本地磁盘上本实验做的是数据放在本地磁盘上。在搭建RAC+DG之前，请确保RAC是没有问题的1.检查集群状态 grid用户下执行：crsctl status res -tRAC PrimaryRACDB1（主机）RACDB2（主机）备注Public IP192....
复制链接

扫一扫

专栏目录