当前位置:  数据库>oracle

利用rman搭建Oracle Data Guard环境

    来源: 互联网  发布时间：2017-05-31

    本文导语: Data Guard 环境：操作系统： RedHat 5.6 Primary数据库：IP地址：192.168.48.131数据库SID：orclDB_UNIQUE_NAME：orcl_pdStandby数据库：IP地址：192.168.48.132数据库SID：orclDB_UNIQUE_NAME：orcl_st1.主库设置成force logging 模式SQL> alter database force logging;Database...

Data Guard 环境：
操作系统： RedHat 5.6
Primary数据库：
IP地址：192.168.48.131
数据库SID：orcl
DB_UNIQUE_NAME：orcl_pd

Standby数据库：
IP地址：192.168.48.132
数据库SID：orcl
DB_UNIQUE_NAME：orcl_st




1.主库设置成force logging 模式
SQL> alter database force logging;


Database altered.


2.主库设为归档模式
SQL> archive log list;
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination /u01/Oracle/product/dbs/arch
Oldest online log sequence 4
Current log sequence 6
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.


Total System Global Area 1653518336 bytes
Fixed Size 2228904 bytes
Variable Size 956304728 bytes
Database Buffers 687865856 bytes
Redo Buffers 7118848 bytes
Database mounted.
SQL> alter database archivelog;


Database altered.


SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/oracle/product/dbs/arch
Oldest online log sequence 4
Next log sequence to archive 6
Current log sequence 6


SQL> create spfile from pfile='/u01/oracle/product/dbs/initorcl.ora';
3. 添加redo log file


SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 ('/u01/oracle/oradata/orcl/redo04.log') size 50M;


Database altered.


SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 ('/u01/oracle/oradata/orcl/redo05.log') size 50M;


Database altered.


SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 6 ('/u01/oracle/oradata/orcl/redo06.log') size 50M;


Database altered.


SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 7 ('/u01/oracle/oradata/orcl/redo07.log') size 50M;


Database altered.




4.创建备库的密码文件和控制文件

SQL> alter database create standby controlfile as '/u01/control01.ctl';


Database altered.


如果存在就不需要创建
[oracle@dg1 ~]$ orapwd file=/u01/orapworcl password=wwwwww


[oracle@dg1 u01]$ scp orapworcl 192.168.48.132:/u01/oracle/product/dbs/
The authenticity of host '192.168.48.132 (192.168.48.132)' can't be established.
RSA key fingerprint is ed:c9:32:48:57:38:60:f3:a3:9f:f1:e9:89:04:3a:46.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '192.168.48.132' (RSA) to the list of known hosts.
oracle@192.168.48.132's password:
orapworcl 100% 1536 1.5KB/s 00:00




Pfile 默认位置在$ORACLE_HOME/dbs目录下，也可以自己指定位置：
SQL> create pfile from spfile;


File created.






5.修改初始化参数文件
[root@dg1 admin]# cd /u01/oracle/product/dbs
在initorcl.ora 添加如下内容：
####主库参数######
*.DB_NAME ='orcl';
#--监听中配置的
*.DB_UNIQUE_NAME='orcl_pd'
#--列出DG中所有DB_UNIQUE_NAME
*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(orcl_pd,orcl_st)'
*.LOG_ARCHIVE_DEST_1='LOCATION=/u01/archive VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=orcl_pd'
*.LOG_ARCHIVE_DEST_2='service=orcl_st DB_UNIQUE_NAME=orcl_st'


*.LOG_ARCHIVE_DEST_STATE_1=ENABLE
*.LOG_ARCHIVE_DEST_STATE_2=ENABLE
*.REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
#####备库参数#######
*.FAL_SERVER=orcl_st
*.FAL_CLIENT=orcl_pd
*.standby_file_management='AUTO'
*.standby_archive_dest='/u01/archive'








6.用刚修改的pfile 启动数据库，并生成spfile。
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup pfile=?/dbs/initorcl.ora
ORACLE instance started.
Total System Global Area 247463936 bytes
Fixed Size 1218748 bytes
Variable Size 75499332 bytes
Database Buffers 163577856 bytes
Redo Buffers 7168000 bytes
Database mounted.
Database opened.
SQL> !echo $ORACLE_HOME
/u01/oracle/product
SQL> create spfile from pfile='/u01/oracle/product/dbs/initorcl.ora';
File created.


SQL> startup pfile=?/dbs/initorcl.ora
ORA-32006: STANDBY_ARCHIVE_DEST initialization parameter has been deprecated
ORACLE instance started.


Total System Global Area 1653518336 bytes
Fixed Size 2228904 bytes
Variable Size 956304728 bytes
Database Buffers 687865856 bytes
Redo Buffers 7118848 bytes
Database mounted.
Database opened.


SQL> !echo $ORACLE_HOME
/u01/oracle/product


SQL> create spfile from pfile='/u01/oracle/product/dbs/initorcl.ora';


File created.



6. 修改listener.ora 和tnsnames.ora 文件
[root@dg1 admin]# cat listener.ora
# listener.ora Network Configuration File: /u01/oracle/product/network/admin/listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /u01/oracle/product)
(PROGRAM = extproc)
)
(SID_DESC =
(GLOBAL_DBNAME =orcl)
(ORACLE_HOME = /u01/oracle/product)
(SID_NAME =orcl)
)
(SID_DESC =
(GLOBAL_DBNAME =orcl1)
(ORACLE_HOME = /u01/oracle/product)
(SID_NAME =orcl)
)
)




[root@dg1 admin]# cat tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/oracle/product/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.




ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = dg1)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)




ORCL_ST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = dg2)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)






ORCL_PD =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = dg1)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)


二standby节点设置


二． Standby 端配置
1. 创建备库存放数据文件和后台跟踪目录。
[oracle@dg2 trace]$ echo $ORACLE_BASE
/u01/oracle
[oracle@dg2 trace]$mkdir -p /u01/oracle/oradata/orcl
[oracle@dg2 trace]$mkdir -p /u01/oracle/admin/orcl
[oracle@dg2 trace]$mkdir -p /u01/oracle/admin/orcl/adump
[oracle@dg2 trace]$mkdir -p /u01/oracle/admin/orcl/bdump
[oracle@dg2 trace]$mkdir -p /u01/oracle/admin/orcl/cdump
[oracle@dg2 trace]$mkdir -p /u01/oracle/admin/orcl/ddump
[oracle@dg2 trace]$mkdir -p /u01/oracle/admin/orcl/pfile
[oracle@dg2 trace]$mkdir -p /u01/oracle/admin/orcl/udump


2.初始化拷贝过来的参数
[oracle@dg2 trace]cd /u01/oracle/product/dbs
从主库copy过来，修改如下：
*.DB_NAME ='orcl';
#--监听中配置的
*.DB_UNIQUE_NAME='orcl_st'
#--列出DG中所有DB_UNIQUE_NAME
*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(orcl_pd,orcl_st)'
*.LOG_ARCHIVE_DEST_1='LOCATION=/u01/archive VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=orcl_st'
*.LOG_ARCHIVE_DEST_2='service=orcl_pd DB_UNIQUE_NAME=orcl_pd'


*.LOG_ARCHIVE_DEST_STATE_1=ENABLE
*.LOG_ARCHIVE_DEST_STATE_2=ENABLE
*.REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
#####备库参数#######
*.FAL_SERVER=orcl_pd
*.FAL_CLIENT=orcl_st
*.standby_file_management='AUTO'
*.standby_archive_dest='/u01/archive'




3. 修改listener.ora 和 tnsnames.ora 文件，如果不存在，就从主库上copy 过去。
拷贝初始化参数文件
[oracle@dg1 admin]$scp initorcl.ora 192.168.48.132:/u01/oracle/product/dbs

拷贝listener.ora和tnsnames.ora文件
[oracle@dg1 admin]$ scp listener.ora 192.168.48.132:/u01/oracle/product/network/admin
oracle@192.168.48.132's password:
listener.ora 100% 609 0.6KB/s 00:00
[oracle@dg1 admin]$ scp tnsnames.ora 192.168.48.132:/u01/oracle/product/network/admin
oracle@192.168.48.132's password:
tnsnames.ora 100% 734 0.7KB/s 00:00


4.rman备份
[oracle@dg1 admin]$ rman target /


Recovery Manager: Release 11.2.0.3.0 - Production on Mon Jan 27 16:59:37 2014


Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.


connected to target database: ORCL (DBID=1365744995)




RUN {
allocate channel c1 type disk;
allocate channel c2 type disk;
sql 'alter system archive log current';
backup current controlfile for standby format='/oradata/backup/control_%U';
BACKUP FORMAT '/oradata/backup/orcl_%U_%T' skip inaccessible filesperset 5 DATABASE ;
sql 'alter system archive log current';
BACKUP FORMAT '/oradata/backup/arch_%U_%T' skip inaccessible filesperset 5 ARCHIVELOG ALL DELETE INPUT;
release channel c2;
release channel c1;
}
backup full database include current controlfile for standby plus archivelog FORMAT '/oradata/backup/orcl_%U_%T' skip inaccessible filesperset 5;


5.拷贝backup文件夹到dg2服务器
[oracle@dg1 oradata]$ cd /oradata/backup/
[oracle@dg1 backup]$ scp * 192.168.48.132:/oradata/backup




6.执行duplicate 复制standby库
[oracle@dg1 admin]$ rman target / auxiliary sys/wwwwww@orcl_st;
Recovery Manager: Release 10.2.0.1.0 - Production on Tue Jul 20 22:32:59 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: ORCL (DBID=1248423599)
connected to auxiliary database: ORCL (not mounted)
RMAN> duplicate target database for standby nofilenamecheck dorecover;




7. 在备库添加redo log file
如果主库没有添加redo log file，可以先用copy 过来的初始化文件将数据库启动到mount 状态。在创建个spfile，最后添加redo log。
SQL> create spfile from pfile='/u01/oracle/product/dbs/initorcl.ora';
添加一个新的Standby Redologs组（注意组号不要与当前存在的Online Redologs组重复），并为该组指定一个成员：
SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 ('/u01/oracle/oradata/orcl/redo04.log') size 50M;
SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 ('/u01/oracle/oradata/orcl/redo05.log') size 50M;
SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 6 ('/u01/oracle/oradata/orcl/redo06.log') size 50M;
SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 7 ('/u01/oracle/oradata/orcl/redo07.log') size 50M;




启动顺序：先standby ,后primary;
闭顺序：先primary 后standby;

在备库将实例启动到mount 状态：
SQL> startup nomount;
SQL>alter database mount standby database ;
SQL>alter database recover managed standby database disconnect from session;
在备库启动监听：
$lsnrctl start
在主库启动实例：
SQL> startup;
在主库启动监听：
$lsnrctl start






8.验证dg
在主机执行：
SQL> create user hbhe identified by wwwwww default tablespace users temporary tablespace temp;


User created.


SQL> grant select on dba_pending_transactions to hbhe;


Grant succeeded.


SQL> grant connect, resource to hbhe;


Grant succeeded.


SQL> grant insert any table to hbhe;


Grant succeeded.


SQL> quit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@dg1 dbs]$ sqlplus hbhe/wwwwww


SQL*Plus: Release 11.2.0.3.0 Production on Tue Jan 28 18:45:36 2014


Copyright (c) 1982, 2011, Oracle. All rights reserved.




Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options


SQL> create table sales1(empid number,depid number,area varchar(20),salenum number);


Table created.


SQL> insert into sales1 values(1,1,'china',10);


1 row created.


SQL> insert into sales1 values(2,1,'china',10);


1 row created.




在备机执行：
[oracle@dg2 dbs]$ sqlplus "/as sysdba"


SQL*Plus: Release 11.2.0.3.0 Production on Tue Jan 28 19:14:36 2014


Copyright (c) 1982, 2011, Oracle. All rights reserved.




Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options




SQL> alter database recover managed standby database cancel;


Database altered.


SQL> alter database open;


Database altered.


SQL> conn hbhe/wwwwww
Connected.
SQL> select * from sales1;


EMPID DEPID
---------- ----------
AREA SALENUM
------------------------------------------------------------ ----------
1 1
china 10


2 1
china 10

 

相关参考:

Oracle Data Guard 重要配置参数

基于同一主机配置 Oracle 11g Data Guard

探索Oracle之11g DataGuard

Oracle Data Guard （RAC+DG) 归档删除策略及脚本

Oracle Data Guard 的角色转换

Oracle Data Guard的日志FAL gap问题

Oracle 11g Data Guard Error 16143 Heartbeat failed to connect to standby 处理方法


    
 
 
 
本站(WWW.)旨在分享和传播互联网科技相关的资讯和技术，将尽最大努力为读者提供更好的信息聚合和浏览方式。
本站(WWW.)站内文章除注明原创外，均为转载、整理或搜集自网络。欢迎任何形式的转载，转载请注明出处。












  • 相关文章推荐
  • linux c下利用srand和rand函数生成随机字符串
  • 请问：Linux下用C编程计算CPU利用率和内存利用率？
  • linux下利用(cat,strings,head,sed)命令生成随机字符串
  • 在2003下利用vmware安装了linux，又利用host－only方式上了网，问题如下多谢指点！！！
  • Web服务器/前端 iis7站长之家
  • 大虾 紧急求助！！！！如何求得当前机子的处理器利用率和内存利用率？
  • 如何利用libpcap和Python嗅探数据包
  • 如何利用Bash脚本（利用awksedgrepwc等）来自动修改配置文件
  • windows堆栈溢出利用的七种方式
  • 求RADIUS的动态分配IP的问题(利用IPPOOL)
  • iowait和cpu利用率的权衡问题
  • 利用java.net.URLConnection上传文件
  • Qt中利用槽如何来传递参数
  • 浏览器漏洞利用框架 BeEF
  • 怎么样利用Socket进行Java网络编程
  • 请问如何编程获得CPU利用率？（空）
  • 如何利用Linux安装盘制作启动盘？
  • 谁知道linux/unix下利用Schema读取校验xml的开源代码，给一个链接，谢谢！
  • 怎样实现利用fprintf，输出定长字串，位数不足时在左侧填入指定字符填充。。
  • CPU、内存、数据库利用率监控
  • 怎样利用u-boot烧写？？


    • 站内导航:


    特别声明:169IT网站部分信息来自互联网,如果侵犯您的权利,请及时告知,本站将立即删除！

    ©2012-2021,