利用rman搭建OracleDataGuard环境

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

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 处理方法