1.设置归档模式
这个生产库都是这种模式。
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。
listener.ora::(静态配置LISTENER(备库NOMOUNT状态时无法监听到动态的LSN))
SID_LIST_LISTENER =
(SID_LIST
(SID_DESC =
(GLOBAL_DBNAME = orcl200)
(ORACLE_HOME = /opt/oracle/product)
(SID_NAME = orcl)
)
)
tnsname.ora::
ORCL200 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.10.200)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl200)
)
)
ORCL206 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.10.206)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl206)
)
)
4.添加data guard参数
创建pfile,添加如下文件:
SQL> create pfile from spfile;
*.db_unique_name='orcl200'
*.log_archive_config='dg_config=(orcl200,orcl206)'
*.log_archive_dest_1='location=/u01/archivelog'
*.log_archive_dest_2='service=orcl206 lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=orcl206'
*.log_archive_dest_state_1=enable
*.log_archive_dest_state_2=enable
*.standby_file_management='auto'
*.fal_server='orcl206'
*.fal_client='orcl200'
5.用新pfile重启主库
SQL>shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>create spfile from pfile='/u01/app/oracle/product/11.2.0/dbhome_1/dbs/initorcl.ora';(create spfile from pfile='';用spfile启动)
File created.
SQL>startup
ORACLE instance started.
二.Standby端设置:
1.创建相关目录结构
对应主库目录创建 /opt/oracle/oradata等
2.创建standby的口令文件
[oracle@qs-dmm-rh2 trace]$ orapwd file=?/dbs/orapwdave password=oracle(主库与备库必须相同)
3.创建standby的初始化参数:
*.db_unique_name='orcl206'
*.log_archive_config='dg_config=(orcl200,orcl206)'
*.log_archive_dest_1='location=/u01/archivelog'
*.log_archive_dest_2='service=orcl200 lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=orcl200'
*.log_archive_dest_state_1=enable
*.log_archive_dest_state_2=enable
*.standby_file_management='auto'
*.fal_server='orcl200'
*.fal_client='orcl206'
*.log_file_name_convert='/opt/oracle/oradata/orcl200','/opt/oracle/oradata/orcl206'
*.db_file_name_convert='/opt/oracle/oradata/orcl200','/opt/oracle/oradata/orcl206'
4.用pfile将standby启动到nomount状态:
SQL> startup nomount pfile=?/dbs/initorcl.ora(可创建spfile启动)
ORACLE instance started.
(RMAN 11G用主库创建备库无备份的情况)
5.开始duplicate
[oracle@qs-dmm-rh2 dbs]$rman target sys/orcl200@orcl200 auxiliary sys/orcl200@orcl206
Recovery Manager: Release 11.2.0.1.0 - Production on Tue Mar 8 16:10:25 2011
Copyright (c) 1982, 2009, Oracle and/or its affiliates.All rights reserved.
connected to target database: ORCL (DBID=1272955137)
connected to auxiliary database: ORCL (not mounted)
RMAN>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:
Primary:
SQL> show parameter pfile
NAMETYPEVALUE
------------------------------------ ------- ------------------------------
spfilestring/u01/app/oracle/product/11.2.0
Standby:
SQL> show parameter pfile
NAMETYPEVALUE
------------------------------------ ----------- ------------------------------
spfilestring
pfile里面都是我们设置的一些基本参数。但是备库有冗余的作用,所以这里还是建议用主库的pfile copy过来,然后修改相关参数后,在创建spfile。这样即使切换了,对DB的影响也不大。
2.只要备库的监听不重启,重启备库后,主库还是能识别的。如果备库的监听重启了。那么主库也就需要重启。
3.复制结束后的Standby只启动到mount standby的状态。 并没有启动MRP的应用归档程序。 所以这个时候查询主备库,归档是不同步的。需要手动的启动MRP进程。
SQL> alter database recover managed standby database disconnect from session;
4.备库Standby redo log问题:
在duplicate结束后,备库没有添加standby redo log file。但是主库采用的是:lgwr async传送的日志。当备库的RFS 进程接收到日志后,发现备库没有standby redo log的时候,备库会自动用ARCH将其写入归档文件。
以下是备库的alert log:(/opt/oracle/diag/rdbms/orcl206/orcl/alert tail -n 50 log.xml)
Tue Mar 08 16:53:32 2011
Archived Log entry 9 added for thread 1 sequence 21 rlc 745174404 ID 0x4bdfd301 dest 2:
RFS[2]: Opened log for thread 1 sequence 22 dbid 1272955137 branch 745174404
Tue Mar 08 16:53:36 2011
Media Recovery Log/u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch1_21_745174404.dbf
Media Recovery Waiting for thread 1 sequence 22(in transit) --传输中
Tue Mar 08 16:58:58 2011
Archived Log entry 10 added for thread 1 sequence 22 rlc 745174404 ID 0x4bdfd301 dest 2:
RFS[2]: Opened log for thread 1 sequence 23 dbid 1272955137 branch 745174404
Tue Mar 08 16:59:00 2011
Media Recovery Log/u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch1_22_745174404.dbf
Media Recovery Waiting for thread 1 sequence 23 (in transit)
--注意这里归档文件目录,使用的是$ORACLE_HOME/dbs,自动转换为ARCH时,也是使用默认的归档目录。
5.在备库添加standby redo log:
SQL> alter database add standby logfile '/u01/app/oracle/oradata/dave/stdbyredo01.log' size 50m;
alter database add standby logfile '/u01/app/oracle/oradata/dave/stdbyredo01.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/dave/stdbyredo01.log' size 50m;
Database altered.
SQL> alter database add standby logfile '/u01/app/oracle/oradata/dave/stdbyredo02.log' size 50m;
Database altered.
SQL> alter database add standby logfile '/u01/app/oracle/oradata/dave/stdbyredo03.log' size 50m;
Database altered.
SQL> alter database add standby logfile '/u01/app/oracle/oradata/dave/stdbyredo04.log' size 50m;
Database altered.
SQL> alter database recover managed standby database disconnect from session;
Database altered.
在看一下日志:
Tue Mar 08 17:47:39 2011
Archived Log entry 15 added for thread 1 sequence 27 ID 0x4bdfd301 dest 1:
RFS[2]: Selected log 4 for thread 1 sequence 28 dbid 1272955137 branch 745174404
Tue Mar 08 17:47:43 2011
Archived Log entry 16 added for thread 1 sequence 28 ID 0x4bdfd301 dest 1:
Media Recovery Log /u01/archivelog/1_27_745174404.dbf
RFS[2]: Selected log 4 for thread 1 sequence 29 dbid 1272955137 branch 745174404
Media Recovery Log /u01/archivelog/1_28_745174404.dbf
Media Recovery Waiting for thread 1 sequence 29 (in transit)
--我们添加standby redo log之后,归档文件变成了我们指定的Log_archive_dest_n指定的参数。
6.在主库也添加一下standby redo log
SQL> alter database add standby logfile '/u01/app/oracle/oradata/orcl/stdbyredo01.log' size 50m;
Database altered.
SQL> alter database add standby logfile '/u01/app/oracle/oradata/orcl/stdbyredo02.log' size 50m;
Database altered.
SQL> alter database add standby logfile '/u01/app/oracle/oradata/orcl/stdbyredo03.log' size 50m;
Database altered.
SQL> alter database add standby logfile '/u01/app/oracle/oradata/orcl/stdbyredo04.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;
(alter database recover managed standby database using current logfile disconnect from session)
Database altered.
8.验证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,'tianlesoftware');
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 tianlesoftware
备注::
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;
alter database recover managed standby database using current logfile disconnect from session
oracle 日志文件
/u01/oinOracle/diag/rdbms/slave201/orcl/alert
oracle连接对方库
sqlplus sys/oracle_d@mast200 as sysdba
sqlplus sys/oracle_d@slave201 as sysdba