Oracle 11g Data Guard 使用duplicate from active database 创建 standby database 实验
使用duplicate from active database 来搭建standby database,主库的停机时间很少,只需要重启一下,使参数生效。也可以用这种方法进行DB迁移。DG搭建好,然后把备库激活就可以了,这样整个迁移中宕机时间也比较短。
Oracle 11g的physical standby支持open read only下的apply和Real-time query,因此就有了physical standby稳定和logical standby的报表查询功能。
实验环境:
System : IBM AIX 5L Oracle version : 11.2.0.1
Primary IP : 172.30.2.40 DB_NAME=orcl
Standby IP : 172.30.2.56 DB_NAME=orcl
一. Primary端操作:
1.设置归档模式
sys@ORCL> archive log list;
sys@ORCL> shutdown immediate
sys@ORCL> startup mount
sys@ORCL> alter database archivelog;
sys@ORCL> archive log list;
2.Primary设置force logging
sys@ORCL> alter database force logging;
Database altered.
sys@ORCL> select force_logging from v$database;
FORCE_LOGGING
--------------------
YES
3.配置Oracle Net
在Primary库和Standby库都要修改,建议使用netca和netmgr命令来配置。
NOTE:修改完成后记得重启listener。
listener.ora
$ cd $ORACLE_HOME/network/admin
$ vi listener.ora
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.30.2.40)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
ADR_BASE_LISTENER = /u01/app/oracle
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = orcl.saci.com.cn)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
(SID_NAME = orcl)
)
)
--配置静态注册
tnsnames.ora
$ cd $ORACLE_HOME/network/admin
$ vi tnsnames.ora
ORCL_PD =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.30.2.40)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = orcl.saci.com.cn)
)
)
ORCL_ST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.30.2.56)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = orcl.saci.com.cn)
)
)
4.添加data guard参数
创建pfile,添加如下内容:
sys@ORCL> create pfile='/u01/initorcl.ora' from spfile;
File created.
*.db_name='orcl'
*.db_unique_name='orcl_pd'
*.log_archive_config='dg_config=(orcl_pd,orcl_st)'
*.log_archive_dest_1='location=/u02/archivelog valid_for=(all_logfiles,all_roles) db_unique_name=orcl_pd'
*.log_archive_dest_2='service=orcl_st reopen=120 lgwr async valid_for=(online_logfiles,primary_role)
db_unique_name=orcl_st'
*.log_archive_dest_state_1=enable
*.log_archive_dest_state_2=enable
*.standby_file_management='auto'
*.fal_server='orcl_st'
*.fal_client='orcl_pd'
注意:在Oracle 11g的Data Guard中,standby_archive_dest参数已经被取消了。
Standby归档文件的存放位置按如下规则来进行:
(1)当LOG_ARCHIVE_DEST_n设置了valid_for=(all_logfiles,all_roles),那么在不定义standby_archive_dest参数时,Oracle就会选择LOG_ARCHIVE_DEST_n参数作为归档目标。
(2)如果在第一步设置的同时,又独立设置LOG_ARCHIVE_DEST_n参数
为 valid_for=(standby_logfile,*) 属性,那么当compatible参数大于10.0的时候,会自动的选择任意一个LOG_ARCHIVE_DEST_n的值。
(3)如果LOG_ARCHIVE_DEST_n 没有设置的话,默认位置是:$ORACLE_HOME/dbs.
不过valid_for参数的默认值就是all_logfiles和all_roles. 所以只要设置了本地的归档位置,远程的归档文件也会放到这个目录下面。
5.用新的pfile重启主库
idle> startup mount pfile='/u01/initorcl.ora';
ORACLE instance started.
Total System Global Area 6847938560 bytes
Fixed Size 2219808 bytes
Variable Size 3539992800 bytes
Database Buffers 3288334336 bytes
Redo Buffers 17391616 bytes
Database mounted.
idle> create spfile from pfile='/u01/initorcl.ora';
File created.
idle> alter database open;
Database altered.
二. Standby端设置:
1.创建相关目录结构
$ mkdir -p /u01/app/oracle/oradata/mahee
--这里我们创建的目录和Target库不同,我们在参数文件里需要转换一下。
2.创建standby的口令文件
$ cd $ORACLE_HOME/bin
$ orapwd file=$ORACLE_HOME/dbs/orapworcl password=oracle entries=8
$ cd $ORACLE_HOME/dbs
$ ls
init.ora initorcl.ora orapworcl
3.创建standby的初始化参数:
*.control_files='/u01/app/oracle/oradata/mahee/control01.ctl','/u01/app/oracle/oradata/mahee/control02.ctl','/u01/app/oracle/oradata/mahee/control03.ctl'
*.db_name='orcl'
*.db_unique_name='orcl_st'
*.log_archive_config='dg_config=(orcl_pd,orcl_st)'
*.log_archive_dest_1='location=/u02/archivelog valid_for=(all_logfiles,all_roles) db_unique_name=orcl_st'
*.log_archive_dest_2='service=orcl_pd reopen=120 lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=orcl_pd'
*.log_archive_dest_state_1=enable
*.log_archive_dest_state_2=enable
*.standby_file_management='auto'
*.fal_server='orcl_pd'
*.fal_client='orcl_st'
*.log_file_name_convert='/u01/app/oracle/oradata/orcl','/u01/app/oracle/oradata/mahee'
*.db_file_name_convert='/u01/app/oracle/oradata/orcl','/u01/app/oracle/oradata/mahee'
4.用pfile将standby启动到nomount状态:
SQL> startup nomount pfile=?/dbs/initorcl.ora
ORACLE instance started.
Total System Global Area 6847938560 bytes
Fixed Size 2219808 bytes
Variable Size 3539992800 bytes
Database Buffers 3288334336 bytes
Redo Buffers 17391616 bytes
SQL> quit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
5.开始duplicate
$ rman target sys/oracle@orcl_pd auxiliary sys/oracle@orcl_st
Recovery Manager: Release 11.2.0.1.0 - Production on Tue Apr 12 01:37:35 2011
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL (DBID=1275959622)
connected to auxiliary database: ORCL (not mounted)
RMAN> duplicate target database for standby from active database;
Starting Duplicate Db at 12-APR-11
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=63 device type=DISK
contents of Memory Script:
{
backup as copy reuse
targetfile '/u01/app/oracle/product/11.2.0/db_1/dbs/orapworcl' auxiliary format
'/u01/app/oracle/product/11.2.0/db_1/dbs/orapworcl' ;
--用duplicate创建standby时会复制口令文件
}
executing Memory Script
Starting backup at 12-APR-11
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=196 device type=DISK
Finished backup at 12-APR-11
contents of Memory Script:
{
backup as copy current controlfile for standby auxiliary format '/u01/app/oracle/oradata/mahee/control01.ctl';
restore clone controlfile to '/u01/app/oracle/oradata/mahee/control02.ctl' from
'/u01/app/oracle/oradata/mahee/control01.ctl';
restore clone controlfile to '/u01/app/oracle/oradata/mahee/control03.ctl' from
'/u01/app/oracle/oradata/mahee/control01.ctl';
--创建控制文件
}
executing Memory Script
Starting backup at 12-APR-11
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/db_1/dbs/snapcf_orcl.f tag=TAG20110412T013822 RECID=3 STAMP=748229902
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 12-APR-11
Starting restore at 12-APR-11
using channel ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: copied control file copy
Finished restore at 12-APR-11
Starting restore at 12-APR-11
using channel ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: copied control file copy
Finished restore at 12-APR-11
contents of Memory Script:
{
sql clone 'alter database mount standby database';
--将备库启动到mount standby
}
executing Memory Script
sql statement: alter database mount standby database
contents of Memory Script:
{
set newname for tempfile 1 to
"/u01/app/oracle/oradata/mahee/temp01.dbf";
switch clone tempfile all;
set newname for datafile 1 to
"/u01/app/oracle/oradata/mahee/system01.dbf";
set newname for datafile 2 to
"/u01/app/oracle/oradata/mahee/sysaux01.dbf";
set newname for datafile 3 to
"/u01/app/oracle/oradata/mahee/undotbs01.dbf";
set newname for datafile 4 to
"/u01/app/oracle/oradata/mahee/users01.dbf";
set newname for datafile 5 to
"/u01/app/oracle/oradata/mahee/example01.dbf";
backup as copy reuse
datafile 1 auxiliary format
"/u01/app/oracle/oradata/mahee/system01.dbf" datafile
2 auxiliary format
"/u01/app/oracle/oradata/mahee/sysaux01.dbf" datafile
3 auxiliary format
"/u01/app/oracle/oradata/mahee/undotbs01.dbf" datafile
4 auxiliary format
"/u01/app/oracle/oradata/mahee/users01.dbf" datafile
5 auxiliary format
"/u01/app/oracle/oradata/mahee/example01.dbf" ;
sql 'alter system archive log current';
--将datafile convert到其它目录
}
executing Memory Script
executing command: SET NEWNAME
renamed tempfile 1 to /u01/app/oracle/oradata/mahee/temp01.dbf in control file
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting backup at 12-APR-11
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
--开始copy datafile,如果数据文件比较大,这个过程会比较慢
input datafile file number=00001 name=/u01/app/oracle/oradata/orcl/system01.dbf
output file name=/u01/app/oracle/oradata/mahee/system01.dbf tag=TAG20110412T013831
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=/u01/app/oracle/oradata/orcl/sysaux01.dbf
output file name=/u01/app/oracle/oradata/mahee/sysaux01.dbf tag=TAG20110412T013831
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile copy
input datafile file number=00005 name=/u01/app/oracle/oradata/orcl/example01.dbf
output file name=/u01/app/oracle/oradata/mahee/example01.dbf tag=TAG20110412T013831
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=/u01/app/oracle/oradata/orcl/undotbs01.dbf
output file name=/u01/app/oracle/oradata/mahee/undotbs01.dbf tag=TAG20110412T013831
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=/u01/app/oracle/oradata/orcl/users01.dbf
output file name=/u01/app/oracle/oradata/mahee/users01.dbf tag=TAG20110412T013831
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 12-APR-11
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=3 STAMP=748229932 file name=/u01/app/oracle/oradata/mahee/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=4 STAMP=748229932 file name=/u01/app/oracle/oradata/mahee/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=5 STAMP=748229932 file name=/u01/app/oracle/oradata/mahee/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=6 STAMP=748229932 file name=/u01/app/oracle/oradata/mahee/users01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=7 STAMP=748229932 file name=/u01/app/oracle/oradata/mahee/example01.dbf
Finished Duplicate Db at 12-APR-11
RMAN>
DG复制到这一步已经操作完成了。
三.后续工作
1.主库已经使用了spfile,但是备库用的还是之前的pfile:
Primary:
sys@ORCL> show parameter pfile
NAME TYPE VALUE
------- ----------- ------------------------------------------------------
spfile string /u01/app/oracle/product/11.2.0/db_1/dbs/spfileorcl.ora
Standby:
SQL> show parameter pfile
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string
SQL> create spfile from pfile='?/dbs/initorcl.ora';
File created.
pfile里面都是我们设置的一些基本参数。但是备库有冗余的作用,所以这里还是建议用主库的pfile copy过来,然后修改相关参数后,在创建spfile。这样即使切换了,对DB的影响也不大。
2. 只要备库的监听不重启,重启备库后,主库还是能识别的。如果备库的监听重启了,那么主库也就需要重启。
3.复制结束后的Standby只启动到mount standby的状态。并没有启动MRP的应用归档程序。 所以这个时候查询主备库,归档是不同步的。需要手动的启动MRP进程。
SQL> select status from v$instance;
STATUS
------------
MOUNTED
SQL> alter database recover managed standby database disconnect from session;
Database altered.
4. 备库Standby redo log问题:
在duplicate结束后,备库没有添加standby redo log file。但是主库采用的是:lgwr async传送的日志。当备库的RFS进程接收到日志后,发现备库没有standby redo log的时候,备库会自动用ARCH将其写入归档文件。
以下是备库的alert log:
Tue Apr 12 02:31:00 2011
Archived Log entry 6 added for thread 1 sequence 23 rlc 748178889 ID 0x4c0d7846 dest 2:
RFS[6]: Opened log for thread 1 sequence 24 dbid 1275959622 branch 748178889
Tue Apr 12 02:31:04 2011
Media Recovery Log /u02/archivelog/1_23_748178889.dbf
Media Recovery Waiting for thread 1 sequence 24 (in transit) --传输中
Tue Apr 12 02:31:24 2011
Archived Log entry 7 added for thread 1 sequence 24 rlc 748178889 ID 0x4c0d7846 dest 2:
RFS[6]: Opened log for thread 1 sequence 25 dbid 1275959622 branch 748178889
Tue Apr 12 02:31:24 2011
Media Recovery Log /u02/archivelog/1_24_748178889.dbf
Media Recovery Waiting for thread 1 sequence 25 (in transit)
--注意这里归档文件目录
5. 在备库添加standby redo log:
SQL> alter database add standby logfile '/u01/app/oracle/oradata/mahee/redo04.log' size 50m;
alter database add standby logfile '/u01/app/oracle/oradata/mahee/redo04.log' size 50m
*
ERROR at line 1:
ORA-01156: recovery or flashback in progress may need access to files
--在备库添加standby redo log需要先停MRP
SQL> alter database recover managed standby database cancel;
Database altered.
SQL> alter database add standby logfile '/u01/app/oracle/oradata/mahee/redo04.log' size 50m;
Database altered.
SQL> alter database add standby logfile '/u01/app/oracle/oradata/mahee/redo05.log' size 50m;
Database altered.
SQL> alter database add standby logfile '/u01/app/oracle/oradata/mahee/redo06.log' size 50m;
Database altered.
SQL> alter database add standby logfile '/u01/app/oracle/oradata/mahee/redo07.log' size 50m;
Database altered.
SQL> alter database recover managed standby database disconnect from session;
Database altered.
再看一下日志:
Tue Apr 12 02:41:49 2011
Archived Log entry 8 added for thread 1 sequence 25 rlc 748178889 ID 0x4c0d7846 dest 2:
RFS[6]: Selected log 4 for thread 1 sequence 26 dbid 1275959622 branch 748178889
Tue Apr 12 02:41:51 2011
Media Recovery Log /u02/archivelog/1_25_748178889.dbf
Media Recovery Waiting for thread 1 sequence 26 (in transit)
RFS[6]: Selected log 5 for thread 1 sequence 27 dbid 1275959622 branch 748178889
Tue Apr 12 02:41:56 2011
Archived Log entry 9 added for thread 1 sequence 26 ID 0x4c0d7846 dest 1:
Media Recovery Log /u02/archivelog/1_26_748178889.dbf
Media Recovery Waiting for thread 1 sequence 27 (in transit)
6.在主库也添加一下standby redo log
sys@ORCL> alter database add standby logfile '/u01/app/oracle/oradata/orcl/redo04.log' size 50m;
Database altered.
sys@ORCL> alter database add standby logfile '/u01/app/oracle/oradata/orcl/redo05.log' size 50m;
Database altered.
sys@ORCL> alter database add standby logfile '/u01/app/oracle/oradata/orcl/redo06.log' size 50m;
Database altered.
sys@ORCL> alter database add standby logfile '/u01/app/oracle/oradata/orcl/redo07.log' size 50m;
Database altered.
7.启用real-time apply,从而实现real-time query:
SQL> alter database recover managed standby database cancel;
Database altered.
SQL> alter database open;
Database altered.
SQL> alter database recover managed standby database using current logfile disconnect;
Database altered.
8.验证real-time apply和real-time query:
Primary:
sys@ORCL> create tablespace lsf datafile '/u01/app/oracle/oradata/orcl/lsf.dbf' size 10m segment space management auto extent management local uniform;
Tablespace created.
sys@ORCL> create user lsf identified by lsf default tablespace lsf;
User created.
sys@ORCL> grant connect, resource to lsf;
Grant succeeded.
sys@ORCL> create table T (id number, name varchar2(20));
Table created.
sys@ORCL> insert into T values(1,'lsf');
1 row created.
sys@ORCL> commit;
Commit complete.
Standby:
SQL> select open_mode from v$database;
OPEN_MODE
--------------------
READ ONLY WITH APPLY
SQL> select * from T;
ID NAME
---------- --------------------
1 lsf
到此,实验结束,11gR2 Physical Data Guard 功能是相当强大。