DATE : 2012年12月20日
ABOUT : Install Oracle 11.2.0.1 on Linux 5.5
uname : Linux node2 2.6.18-194.el5 #1 SMP Tue Mar 16 21:52:39 EDT 2010 x86_64 x86_64 x86_64 GNU/Linux
关天RMAN duplicate active database：
http://blog.csdn.net/ora_unix/article/details/12924411
一、Primary端操作：
1.Primary设置归档模式
这个生产库都是这种模式。
SQL> archive log list;
SQL> shutdown immediate
SQL> startup mount
SQL> alter database archivelog;
SQL> archive log list;
2. Primary设置force logging
SQL> alter database force logging;
SQL> select force_logging from v$database;
FORCE_LOG
---------
YES
3.配置Oracle Net
在Primary库和Standby 都要修改。也可以使用netca和netmgr命令配置。
注意：修改完后记得重启listener。lsnrctl reload
Primary:
listener.ora::（静态配置LISTENER）
-------------------
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = orcl)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
(SID_NAME = orcl)
)
)
-------------------
tnsnames.ora::
-------------------
standby =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.99.92)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = standby)
)
)
-------------------
Standby:
listener.ora::
-------------------
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = standby)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
(SID_NAME = orcl)
)
)
-------------------
tnsnames.ora::
-------------------
orcl =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.99.91)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = orcl)
)
)
-------------------
4.Primary添加data guard参数
主要添加以下参数
*.db_name='orcl'
*.db_unique_name='orcl'
*.log_archive_config='DG_CONFIG=(orcl,standby)'
*.log_archive_dest_1='LOCATION=/oradata/orcl/arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=orcl'
*.log_archive_dest_2='SERVICE=standby ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=standby'
*.remote_login_passwordfile='EXCLUSIVE'
These parameters take effect when the primary database is transitioned to the standby role
------------------------------------------------------------------------------------------
*.fal_server='standby'
*.db_file_name_convert='/orcl/standby/','/orcl/'
*.log_file_name_convert='/orcl/standby/arch/','/orcl/arch/'
*.standby_file_management='AUTO'
二、Standby端设置：
1.创建相关目录结构
2.将参口令文件传到standby 端
3.创建standby的初始化参数，主要增加以下参数：
*.db_name='orcl'
*.db_unique_name='standby'
*.control_files='/oradata/orcl/standby/control01.ctl','/oradata/orcl/standby/control02.ctl'
*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(orcl,standby)'
*.db_file_name_convert='/orcl/','/orcl/standby/'
*.log_file_name_convert='/orcl/','/orcl/standby/'
*.log_archive_dest_1='LOCATION=/oradata/orcl/standby/arch/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=standby'
*.LOG_ARCHIVE_DEST_2='SERVICE=orcl ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcl'
*.standby_file_management='AUTO'
*.remote_login_passwordfile='EXCLUSIVE'
*.FAL_SERVER='orcl'
4.用spfile将standby启动到nomount状态：
SQL> create spfile from pfile;
SQL> startup nomount
5.Primary开始duplicate
[oracle@rhel5 admin]$ rman target / auxiliary sys/oracle@standby
Recovery Manager: Release 11.2.0.1.0 - Production on Sun Oct 20 12:10:19 2013
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL (DBID=1333198576)
connected to auxiliary database: ORCL (not mounted)
run {
allocate channel prmy1 type disk;
allocate channel prmy2 type disk;
allocate channel prmy3 type disk;
allocate channel prmy4 type disk;
allocate auxiliary channel stby1 type disk;
duplicate target database for standby from active database;
}
（2） 在执行duplicate的时候，如果源库和目标库目录相同，那么在duplicate 时，需要加上nofilenamecheck，如下：
RMAN> duplicate target database for standby from active database nofilenamecheck;
三、后续工作
1.主库已经使用了spfile，但是备库用的还是之前的pfile：
2.只要备库的监听不重启，重启备库后，主库还是能识别的。如果备库的监听重启了。那么主库也就需要重启。
3.复制结束后的Standby只启动到mount standby的状态。 并没有启动MRP的应用归档程序。所以这个时候查询主备库，归档是不同步的。需要手动的启动MRP进程。
Primary:
SQL> alter system archive log current;
Sun Oct 20 13:42:29 2013
Archived Log entry 4 added for thread 1 sequence 63 ID 0x50dddec2 dest 1:
Sun Oct 20 13:42:29 2013
RFS[4]: Selected log 4 for thread 1 sequence 64 dbid 1333198576 branch 808520212
Standby 非 Real-Time Apply：:
SQL> alter database recover managed standby database disconnect from session;
Attempt to start background Managed Standby Recovery process (orcl)
Sun Oct 20 14:20:08 2013
MRP0 started with pid=26, OS id=8307
MRP0: Background Managed Standby Recovery process started (orcl)
Serial Media Recovery started
Managed Standby Recovery not using Real Time Apply
Waiting for all non-current ORLs to be archived...
All non-current ORLs have been archived.
Media Recovery Log /oradata/orcl/standby/arch/1_63_808520212.dbf
Media Recovery Log /oradata/orcl/standby/arch/1_64_808520212.dbf
Media Recovery Waiting for thread 1 sequence 65 (in transit)
Completed: alter database recover managed standby database disconnect from session
4.备库Real-Time Apply：
在duplicate结束后，备库没有添加standby redo log file。但是主库采用的是：lgwr async传送的日志。当备库的RFS 进程接收到日志后，发现备库没有standby redo log的时候，备库会自动用ARCH将其写入归档文件。
在备库添加standby redo log：
--在备库添加standby redo log需要先停MRP，可以在duplicate 之前在主库加standby redo log，这样在duplicate 后会在备库中自动增加standby redo。
ALTER DATABASE ADD STANDBY LOGFILE ('/oradata/orcl/standby/standbyredo11.dbf','/oradata/orcl/standby/standbyredo12.dbf') SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE ('/oradata/orcl/standby/standbyredo21.dbf','/oradata/orcl/standby/standbyredo22.dbf') SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE ('/oradata/orcl/standby/standbyredo31.dbf','/oradata/orcl/standby/standbyredo32.dbf') SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE ('/oradata/orcl/standby/standbyredo41.dbf','/oradata/orcl/standby/standbyredo42.dbf') SIZE 50M;
在主库也添加一下standby redo log，也是为了方便switchover
ALTER DATABASE ADD STANDBY LOGFILE ('/oradata/orcl/standbyredo11.dbf','/oradata/orcl/standbyredo12.dbf') SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE ('/oradata/orcl/standbyredo21.dbf','/oradata/orcl/standbyredo22.dbf') SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE ('/oradata/orcl/standbyredo31.dbf','/oradata/orcl/standbyredo32.dbf') SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE ('/oradata/orcl/standbyredo41.dbf','/oradata/orcl/standbyredo42.dbf') SIZE 50M;
启用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;
(alter database recover managed standby database using current logfile disconnect from session)
Database altered.
验证real-time apply和real-time query：(同步时，主库的更改操作，必须加COMMIT生效)
Primary：
SQL> create table dave(id number,name varchar2(20));
Table created.
SQL> insert into dave values(1,'hello');
1 row created.
SQL> commit;
Commit complete.
Standby：
SQL>select open_mode from v$database;
OPEN_MODE
--------------------
READ ONLY WITH APPLY
SQL> select * from dave;
ID NAME
---------- ---------------
1 hello
备注：
select max(sequence#) from v$archived_log;
select dest_id, status, destination from v$archive_dest where status != 'INACTIVE';
select message from v$dataguard_status;
col destination for a10
select dest_id, status,type,database_mode,RECOVERY_MODE,DESTINATION,GAP_STATUS from v$archive_dest_status where dest_id=2;
DEST_ID STATUS TYPE DATABASE_MODE RECOVERY_MODE DESTINATIO GAP_STATUS
---------- --------- -------------- --------------- ----------------------- ---------- ------------------------
2 VALID PHYSICAL OPEN_READ-ONLY MANAGED REAL TIME APPLY standby NO GAP
COLUMN NAME FORMAT A24
COLUMN VALUE FORMAT A16
COLUMN DATUM_TIME FORMAT A24
SELECT NAME, VALUE, DATUM_TIME FROM V$DATAGUARD_STATS;
NAME VALUE DATUM_TIME
------------------------ ---------------- ------------------------
transport lag +00 00:00:00 06/18/2009 12:22:06
apply lag +00 00:00:00 06/18/2009 12:22:06
apply finish time +00 00:00:00.000
estimated startup time 9
run {
allocate channel prmy1 type disk;
allocate channel prmy2 type disk;
allocate channel prmy3 type disk;
allocate channel prmy4 type disk;
allocate channel prmy5 type disk;
allocate auxiliary channel stby1 type disk;
duplicate target database for standby from active database
spfile
parameter_value_convert 'Chicago','Boston'
set 'db_unique_name'='Boston'
set standby_file_management='AUTO'
set db_create_file_dest='+DATA'
set db_recovery_file_dest='+FLASH'
set DB_RECOVERY_FILE_DEST_SIZE='8G'
set dg_broker_start='TRUE'
set control_files='+DATA/boston/controlfile/control01.ctl'
nofilenamecheck
;
}