ITPub博客
首页 博客 专栏 学院 社区 IT新媒体评选
发博

首页 > 数据库 > Oracle > ORACLE 11g rac 单实例数据库构建dataguard

ORACLE 11g rac 单实例数据库构建dataguard

Oracle 作者：selang021200 时间：2013-12-07 21:42:28 0 删除 编辑

一、数据库及操做系统初始环境：

筹办已经安装完毕可以正常运行的RAC数据库，和一台安装完ORACLE软件已竖立数据库的。
RAC和单实例安装可以参考链接：
此中RAC做为DG的主库。

>1.我的规划设置以下：

RAC   节面1：-IP192.168.1.221 主机名 bysrac1.bys.com ，和数据库名 bysrac，Database unique name: bysrac
备库  IP 192.168.1.211 主机名racdg.bys.com，实例名 racdg，数据库名 bysrac，Database unique name: racdg
以下：
[grid@bysrac1 ~]$ cat /etc/hosts
127.0.0.1              localhost.localdomain localhost
::1             localhost6.localdomain6localhost6
192.168.1.221  bysrac1  bysrac1.bys.com
192.168.1.226   bysrac1-vip
192.168.1.222   bysrac2  bysrac2.bys.com
192.168.1.227   bysrac2-vip
192.168.10.1    bysrac1-priv
192.168.10.2    bysrac2-priv
192.168.1.228   bysrac-cluster bysrac-cluster-scan
192.168.1.211   racdg  racdg.bys.com
[grid@bysrac1 ~]$ srvctl config database -d bysrac -a
Database unique name: bysrac
Database name: bysrac
Oracle home: /u01/app/oracle/product/11.2.0/dbhome_1
Oracle user: oracle
Spfile: +BYSASMDATA/bysrac/spfilebysrac.ora
Domain: 
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools: bysrac
Database instances: bysrac1,bysrac2
Disk Groups: BYSASMDATA,BYSASMDG

备库： /etc/hosts与主库完整不异即可。
[oracle@racdg ~]$ cat .bash_profile 
ORACLE_BASE=/u01
ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1
ORACLE_SID=racdg
PATH=$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:$PATH:$HOME/bin
export PATH ORACLE_BASE ORACLE_HOME ORACLE_SID
alias sqlplus="rlwrap sqlplus"
alias rman="rlwrap rman"

>2.RAC数据库中各文件位置--光复到备库时转化文件名字用

SYS@bysrac1>archive log list
Database log mode             Archive Mode
Automatic archival             Enabled
Archive destination           +BYSASMDATA
Oldest online log sequence     156
Next log sequence to archive   157
Current log sequence           157
SYS@bysrac1>show parameter recovery
NAME                               TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest               string      +BYSASMDG
db_recovery_file_dest_size          big integer 4977M
recovery_parallelism                integer     0
SYS@bysrac1>select db_unique_name,name from v$database;
DB_UNIQUE_NAME                NAME
------------------------------ ---------
bysrac                        BYSRAC
SYS@bysrac1>select name from v$datafile;
NAME
----------------------------------------------------------------------------------------------------
+BYSASMDATA/bysrac/datafile/system.259.818615175
+BYSASMDATA/bysrac/datafile/sysaux.260.818615237
+BYSASMDATA/bysrac/datafile/undotbs1.261.818615291
+BYSASMDATA/bysrac/datafile/undotbs2.263.818615365
+BYSASMDATA/bysrac/datafile/users.264.818615419
+BYSASMDATA/bysrac/datafile/test1_undo.dbf
+BYSASMDATA/bysrac/datafile/test1.dbf
BYS@bysrac1>selectname,block_size*file_size_blks/1024/1024 as bytes_m from v$controlfile;
NAME                                                BYTES_M
-------------------------------------------------- ----------
+BYSASMDATA/bysrac/controlfile/current.256.8186151    17.6875
19
+BYSASMDG/bysrac/controlfile/current.256.818615127    17.6875
BYS@bysrac1>select group#,member from v$logfile;
    GROUP# MEMBER
---------- --------------------------------------------------
         1+BYSASMDATA/bysrac/onlinelog/group_1.257.818615137
         1+BYSASMDG/bysrac/onlinelog/group_1.257.818615145
         2+BYSASMDATA/bysrac/onlinelog/group_2.258.818615153
         2+BYSASMDG/bysrac/onlinelog/group_2.258.818615163
         3+BYSASMDATA/bysrac/onlinelog/group_3.265.818619941
         3+BYSASMDG/bysrac/onlinelog/group_3.259.818619949
         4+BYSASMDATA/bysrac/onlinelog/group_4.266.818619961
         4+BYSASMDG/bysrac/onlinelog/group_4.260.818619967

>二、修改RAC中初始化参数

>3.正在RAC主库中修改初始化参数

SQL> alter system set standby_file_management=auto scope=spfile;
SQL> alter system set log_archive_config="DG_CONFIG=(bysrac,racdg)"scope=spfile;
SQL> alter system set log_archive_dest_2="SERVICE=racdg LGWRSYNC  VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)DB_UNIQUE_NAME=racdg" scope=spfile;

SQL> alter system set fal_server=racdg scope=spfile;
SQL> alter system set fal_client=bysrac;

SQL> alter system setdb_file_name_convert="+BYSASMDATA/bysrac/datafile/","/u01/oradata/racdg","+BYSASMDATA/bysrac/tempfile/","/u01/oradata/racdg/" scope=spfile;
SQL> alter system set log_file_name_convert="+BYSASMDATA/bysrac/onlinelog/","/u01/oradata/racdg"，"+BYSASMDG/bysrac/onlinelog/","/u01/oradata/racdg/"  scope=spfile;
SQL>create pfile="/home/oracle/initaa.ora" from spfile;    ------那边那边竖立的PFILE第五步时候收收到备库racdg。

>三、设置单圆监听相互颠末进程办事名接睹

>4.单圆监听设置文件示歧下：

RAC的listener.ora 便利用RAC安装好后的不消修改。
[oracle@bysrac1 admin]$ cat tnsnames.ora 
BYSRAC =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = bysrac-cluster-scan)(PORT =1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = bysrac)
    )
  )

racdg =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = racdg)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = racdg)
    )
  )
racdg的tnsnames.ora同上：
[oracle@racdg admin]$ cat listener.ora 

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST =racdg.bys.com)(PORT = 1521))
    )
  )

SID_LIST_LISTENER = 
  (SID_LIST = 
     (SID_DESC =
      (GLOBAL_DBNAME = racdg)
      (ORACLE_HOME =/u01/app/oracle/product/11.2.0/dbhome_1)
      (SID_NAME = racdg)
    ) 
  ) 
ADR_BASE_LISTENER = /u01

>5.收暗码及初化参数文件到racdg，并修改初始化参数文件

[oracle@bysrac1 dbs]$ ls
hc_bysrac1.dat  initbysrac1.ora  orapwbysrac1   peshm_DBUA0_0
hc_DBUA0.dat    init.ora        peshm_bysrac_1  snapcf_bysrac1.f
[oracle@bysrac1 dbs]$ scp orapwbysrac1  racdg:$ORACLE_HOME/dbs/
oracle@racdg"s password: 
orapwbysrac1                                    100% 1536     1.5KB/s  00:00    
[oracle@bysrac1 dbs]$ cd
[oracle@bysrac1 ~]$ ls
alert_bysrac1.log  initaa.ora    inittest.ora     oradiag_oracle
Desktop           initaa.orabak  inittest.orabak
[oracle@bysrac1 ~]$ scp inittest.ora  racdg:$ORACLE_HOME/dbs
oracle@racdg"s password: 
inittest.ora                                    100% 1024     1.0KB/s  00:00

>6.正在racdg上修改暗码及参数文件名，并修改初始化参数

[oracle@racdg dbs]$ ls
inittest.ora  orapwbysrac1
[oracle@racdg dbs]$ mv orapwbysrac1 orapwracdg
[oracle@racdg dbs]$ mv inittest.ora initracdg.ora 
[oracle@racdg dbs]$ ls
initracdg.ora  orapwracdg

修改备库初始化参数文件，最末是那样：
注重面有：
db_file_name_convert时，由于利用ASM,DATAFILE 和TEMPFILE的皆要写上。
log_file_name_convert时，由于利用了正在差别磁盘组的多镜像，也需供皆写上。
audit_file_dest指定的目次要脚动往竖立。
control_files只管写一个，少失足。可以正在STANDBY备库竖立好后再促进镜像。
.log_archive_dest_1指定的目次要竖立一下。.log_archive_dest_2是为了SWITCHOVER  或FAILOVER时备库切换为主库用的。
db_unique_name需供脚动促进上。
[oracle@racdg dbs]$ cat initracdg.ora
*.audit_file_dest="/u01/admin/bysrac/adump"
*.audit_trail="db"
*.compatible="11.2.0.0.0"
*.control_files="/u01/oradata/racdg/control01.ctl"
*.db_block_size=8192
*.db_create_file_dest="/u01/oradata/racdg/"
*.db_domain=""
*.db_file_name_convert="+BYSASMDATA/bysrac/datafile/","/u01/oradata/racdg/","+BYSASMDATA/bysrac/tempfile/","/u01/oradata/racdg/"
*.db_name="bysrac"
*.db_recovery_file_dest="/u01/flash_recovery_area/"
*.db_recovery_file_dest_size=5218762752
*.diagnostic_dest="/u01"
*.fal_server="RACDG"
*.log_archive_config="DG_CONFIG=(bysrac,racdg)"
*.log_archive_dest_1="LOCATION=/u01/archracdg/"
*.log_archive_dest_2="SERVICE=bysrac1 LGWRSYNC  VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=bysrac"
*.log_archive_format="arch_%r_%t_%s.arc"
*.log_file_name_convert="+BYSASMDATA/bysrac/onlinelog/","/u01/oradata/racdg/","+BYSASMDG/bysrac/onlinelog/","/u01/oradata/racdg/"
*.memory_target=741343232
*.open_cursors=300
*.processes=150
*.remote_listener="bysrac-cluster-scan:1521"
*.remote_login_passwordfile="exclusive"
*.standby_file_management="AUTO"
*.undo_tablespace="undotbs1"
*.db_unique_name="racdg"

>7.单圆互测监听是可正常

racdg 上岸bysrac1
[oracle@racdg admin]$ sqlplus sys/oraclesys@bysrac as sysdba
SYS@bys1>
bysrac1 上岸racdg
[oracle@bysrac1 admin]$ sqlplus sys/oraclesys@racdg as sysdba
Connected to an idle instance.    ---备库实例已启动。
SYS@bysrac1>

>4、复造数据库到备库

>8.正在主库上为备库竖立一个standby controlfile

SYS@bysrac1>alter system archive log current;
SYS@bysrac1>alter database create standby controlfile as"/home/oracle/control01.ctl";
收收到备库的不异位置，前面光复数据库到备库时要用。
[oracle@bysrac1 ~]$ scp control01.ctlracdg:/home/oracle/control01.ctl
oracle@racdg"s password: 
control01.ctl         100%  18MB  17.7MB/s   00:01  
此时可以正在实拟机闭机做快照

>9.正在主库做备份并把备份集收收到备库

[oracle@bysrac1 ~]$ rman target /
RMAN> backup database format  "/home/oracle/racfull_%s_%p";

[oracle@bysrac1 ~]$ du -sh racfull_*
766M    racfull_5_1
18M     racfull_6_1
[oracle@bysrac1 ~]$ scp racfull_5_1 racfull_6_1  racdg:/home/oracle/
oracle@racdg"s password: 
racfull_5_1          100%  765MB   5.8MB/s  02:11    
racfull_6_1           100%  18MB   4.5MB/s   00:04

>10.正在备库用RMAN做复造STANDBY数据库操做：有日志

那边那边要注重的有要用用户名和暗码毗连主库和备库。
借有假设利用实拟机的话，性能也很重要，我正在那边那边便卡死机了一次。
[oracle@racdg ~]$ rman target sys/oraclesys@bysrac auxiliary sys/oraclesys
Recovery Manager: Release 11.2.0.1.0 - Production on Mon Aug 12 22:13:22 2013
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rightsreserved.
connected to target database: BYSRAC (DBID=2573560818)
connected to auxiliary database: BYSRAC (not mounted)

RMAN> duplicate target database for standby;
Starting Duplicate Db at 12-AUG-13
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=19 device type=DISK

contents of Memory Script:
{
   restore clone standby controlfile;
}
executing Memory Script

Starting restore at 12-AUG-13
using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: copied control file copy
input file name=/home/oracle/control01.ctl
output file name=/u01/oradata/racdg/control01.ctl
Finished restore at 12-AUG-13

contents of Memory Script:
{
   sql clone "alter database mount standby database";
}
executing Memory Script

sql statement: alter database mount standby database

contents of Memory Script:
{
   set newname for tempfile  1 to 
"/u01/oradata/racdg/temp.262.818615321";
   switch clone tempfile all;
   set newname for datafile  1 to 
"/u01/oradata/racdg/system.259.818615175";
   set newname for datafile  2 to 
"/u01/oradata/racdg/sysaux.260.818615237";
   set newname for datafile  3 to 
"/u01/oradata/racdg/undotbs1.261.818615291";
   set newname for datafile  4 to 
"/u01/oradata/racdg/undotbs2.263.818615365";
   set newname for datafile  5 to 
"/u01/oradata/racdg/users.264.818615419";
   restore
   clone database
   ;
}
executing Memory Script
executing command: SET NEWNAME
renamed tempfile 1 to /u01/oradata/racdg/temp.262.818615321 in control file
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting restore at 12-AUG-13
using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00001 to/u01/oradata/racdg/system.259.818615175
channel ORA_AUX_DISK_1: restoring datafile 00002 to/u01/oradata/racdg/sysaux.260.818615237
channel ORA_AUX_DISK_1: restoring datafile 00003 to/u01/oradata/racdg/undotbs1.261.818615291
channel ORA_AUX_DISK_1: restoring datafile 00004 to/u01/oradata/racdg/undotbs2.263.818615365
channel ORA_AUX_DISK_1: restoring datafile 00005 to /u01/oradata/racdg/users.264.818615419
channel ORA_AUX_DISK_1: reading from backup piece /home/oracle/racfull_5_1
channel ORA_AUX_DISK_1: piece handle=/home/oracle/racfull_5_1tag=TAG20130812T200604
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:04:15
Finished restore at 12-AUG-13

contents of Memory Script:
{
   switch clone datafile all;
}
executing Memory Script

datafile 1 switched to datafile copy
input datafile copy RECID=2 STAMP=823299518 file name=/u01/oradata/racdg/system.259.818615175
datafile 2 switched to datafile copy
input datafile copy RECID=3 STAMP=823299518 filename=/u01/oradata/racdg/sysaux.260.818615237
datafile 3 switched to datafile copy
input datafile copy RECID=4 STAMP=823299518 file name=/u01/oradata/racdg/undotbs1.261.818615291
datafile 4 switched to datafile copy
input datafile copy RECID=5 STAMP=823299518 filename=/u01/oradata/racdg/undotbs2.263.818615365
datafile 5 switched to datafile copy
input datafile copy RECID=6 STAMP=823299518 file name=/u01/oradata/racdg/users.264.818615419
Finished Duplicate Db at 12-AUG-13

>5、翻开DG，检测是可可用

>11.翻开备库，此时DG处于最年夜性能形式

可以颠末进程select protection_mode,database_role,open_mode from v$database;正在主备库少进行考据当前DG形式
同时可以正在备库上alter database recover managed standby database disconnect fromsession;此时DG可以正常运行。
那边那边便不演示查询功能了，由于我正在下面一步要设置DG为最年夜可用形式。

>12.设置为最年夜可用形式

正在备库要竖立STANDBY REDO LOGFILE。所要竖立正在的目次当然要先存正在了。

利用的语句以下：

alter database add standby logfile group 5"/u01/oradata/racdg/standbyredo/standbyredo05.log" size 50M;

alter database add standby logfile group 6"/u01/oradata/racdg/standbyredo/standbyredo06.log" size 50M;

alter database add standby logfile group 7"/u01/oradata/racdg/standbyredo/standbyredo07.log" size 50M;

alter database add standby logfile group 8"/u01/oradata/racdg/standbyredo/standbyredo08.log" size 50M;

alter database add standby logfile group 9"/u01/oradata/racdg/standbyredo/standbyredo09.log" size 50M;

BYS@racdg>select member from v$logfile;

MEMBER

----------------------------------------------------------------------------------------------------

/u01/oradata/racdg/group_1.257.818615137

/u01/oradata/racdg/group_1.257.818615145

/u01/oradata/racdg/group_2.258.818615153

/u01/oradata/racdg/group_2.258.818615163

/u01/oradata/racdg/group_3.265.818619941

/u01/oradata/racdg/group_3.259.818619949

/u01/oradata/racdg/group_4.266.818619961

/u01/oradata/racdg/group_4.260.818619967

/u01/oradata/racdg/standbyredo/standbyredo05.log

/u01/oradata/racdg/standbyredo/standbyredo06.log

/u01/oradata/racdg/standbyredo/standbyredo07.log

/u01/oradata/racdg/standbyredo/standbyredo08.log

/u01/oradata/racdg/standbyredo/standbyredo09.log

>正在主库考据DG工做形式并修改庇护形式为最年夜可用

22:44:28  SYS@bysrac1>selectprotection_mode,database_role,open_mode from v$database;

PROTECTION_MODE      DATABASE_ROLE    OPEN_MODE
-------------------- ---------------- --------------------
MAXIMUM PERFORMANCE  PRIMARY         READ WRITE
22:47:13  SYS@bysrac1>show parameter log_archive_dest_2
NAME                        TYPE       VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_2                  string   SERVICE=racdgLGWR SYNC  VALID
                                       _FOR=(ONLINE_LOGFILES,PRIMARY_
                                       ROLE) DB_UNIQUE_NAME=racdg
22:48:52 SYS@bysrac1>shutdown immediate;
22:50:00 SYS@bysrac1>startup mount;
ORACLE instance started.
Total System Global Area  740724736 bytes
Fixed Size                 1339092 bytes
Variable Size             549454124bytes
Database Buffers          184549376 bytes
Redo Buffers               5382144 bytes
Database mounted.
22:52:31 SYS@bysrac1>alter database set standbydatabase to maximize availability;
Database altered.
22:53:48 SYS@bysrac1>alter database open;
Database altered.
22:55:56 SYS@bysrac1>select protection_mode,database_role,open_mode fromv$database;
PROTECTION_MODE      DATABASE_ROLE    OPEN_MODE
-------------------- ---------------- --------------------
MAXIMUM AVAILABILITY PRIMARY         READ WRITE
###############################

>备库上的操做：

22:58:17 SYS@racdg>shutdown immediate;
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
22:59:18 SYS@racdg>startup mount;
ORACLE instance started.
Total System Global Area  740724736 bytes
Fixed Size                 1339092 bytes
Variable Size             436207916bytes
Database Buffers          297795584 bytes
Redo Buffers               5382144 bytes
Database mounted.
22:59:45 SYS@racdg>selectprotection_mode,database_role,open_mode from v$database;
PROTECTION_MODE      DATABASE_ROLE    OPEN_MODE
-------------------- ---------------- --------------------
MAXIMUM AVAILABILITY PHYSICAL STANDBY MOUNTED
那边那边我要利用了11G新特性ACTIVE STANDBY
SYS@racdg>alter database open;
Database altered.
SYS@racdg>set time on
23:01:06 SYS@racdg>select protection_mode,database_role,open_mode fromv$database;
PROTECTION_MODE      DATABASE_ROLE    OPEN_MODE
-------------------- ---------------- --------------------
MAXIMUM AVAILABILITY PHYSICAL STANDBY READ ONLY

>利用 using current logfile参数启动实韶光复

23:01:08 SYS@racdg> alter database recover managedstandby database using current logfile disconnect from session;
Database altered.
23:02:05 SYS@racdg>select protection_mode,database_role,open_mode fromv$database;
PROTECTION_MODE      DATABASE_ROLE    OPEN_MODE
-------------------- ---------------- --------------------
MAXIMUM AVAILABILITY PHYSICAL STANDBY READ ONLY WITH APPLY

>13.测试DG单圆能可同步

切换日志，并做DML操做
SYS@racdg>selectsequence#,status,thread#,block# from v$managed_standby;
SEQUENCE# STATUS          THREAD#    BLOCK#
---------- ------------ ---------- ----------
       168 CLOSING              1          1
       166 CLOSING              1          1
         0 CONNECTED            0          0
       169 CLOSING              1          1
       170 APPLYING_LOG         1         10
         0 IDLE                 0         0
         0 IDLE                 0         0
         0 IDLE                 0         0
       170 IDLE                 1        10
BYS@bysrac1>set time on
23:03:45 BYS@bysrac1>alter system switch logfile;
System altered.
23:03:53 BYS@bysrac1>select sequence#,status,thread#,block# fromv$managed_standby;
SEQUENCE# STATUS          THREAD#    BLOCK#
---------- ------------ ---------- ----------
       166 CLOSING              1       1673
       164 CLOSING              1          1
       169 CLOSING              1          1
       168 CLOSING              1          1
       170 WRITING              1        26

正在主库中竖立一个新表
23:06:49 BYS@bysrac1>create table test(a varchar2(10));
Table created.
23:07:12 BYS@bysrac1>select * from test;
no rows selected
正在备库中立即便可以查到那个新表已经存正在。
23:08:01 SYS@racdg>conn bys/bys
Connected.
23:08:24 BYS@racdg>select * from test;
no rows selected


<!-- 正文结束 -->

来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/22762540/viewspace-1115668/ ，如需转载，请注明出处，否则将追究法律责任。

0
0
上一篇： 没有了~
下一篇： 没有了~
请登录后发表评论 登录
全部评论
selang021200

注册时间：2009-10-24

支持我们 作者招募 用户协议 FAQ Contact Us

北京盛拓优讯信息技术有限公司. 版权所有  京ICP备16024965号-8 北京市公安局海淀分局网监中心备案编号：11010802021510 niuxiaotong@pcpop.com 17352615567 未成年人举报专区

广播电视节目制作经营许可证(京) 字第1234号 中国互联网协会会员

违法和不良信息举报电话:18101376593