原文：http://blog.itpub.net/29324876/viewspace-1246133/
环境：主备库都为单实例并且数据库SID相同
OS：red hat 6.5
Oracle:11.2.0.4.3
主库操作
1. 开启归档模式
创建归档目录
[root@enn ~]# mkdir -p /u01/archivelog
[root@enn ~]# chown -R oracle:oinstall /u01/archivelog
[root@enn ~]# chmod 777 /u01/archivelog
数据库到mount状态开启归档模式
[root@enn ~]# su - oracle
[oracle@enn ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Sun Jul 20 23:46:46 2014
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup mount
ORACLE instance started.
Total System Global Area 835104768 bytes
Fixed Size 2257840 bytes
Variable Size 541068368 bytes
Database Buffers 289406976 bytes
Redo Buffers 2371584 bytes
Database mounted.
设置主库归档目录
SQL> alter system set log_archive_dest='/u01/archivelog';
开启归档模式
SQL> alter database archivelog;
Database altered.
查看归档设置
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/archivelog
Oldest online log sequence 2
Next log sequence to archive 4
Current log sequence 4
2. 启动force_logging模式
查看是否force_logging模式
SQL> select log_mode,force_logging from v$database;
LOG_MODE FOR
------------ ---
ARCHIVELOG NO
开启force_logging模式
SQL> alter database force logging;
Database altered.
3.创建备库日志文件路径
查看数据库的日志组个数与大小，因为我们创建 standby 日志组的个数是原日志
组个数+1 再与 thread 的积((2+1)*3)，size 不能小于原日志文件的大小。
SQL> select group#,thread#,bytes/1024/1024 M ,STATUS from v$log;
GROUP# THREAD# M STATUS
---------- ---------- ---------- ----------------
1 1 50 CURRENT
3 1 50 INACTIVE
2 1 50 INACTIVE
SQL> select member from v$logfile;
MEMBER
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/ENN/redo03.log
/u01/app/oracle/oradata/ENN/redo02.log
/u01/app/oracle/oradata/ENN/redo01.log
创建备库日志组路径
SQL> alter database add standby logfile thread 1 group 4 ('/u01/app/oracle/oradata/ENN/redo04.log') size 50M;
Database altered.
SQL> alter database add standby logfile thread 1 group 5 ('/u01/app/oracle/oradata/ENN/redo05.log') size 50M;
Database altered.
SQL> alter database add standby logfile thread 1 group 6 ('/u01/app/oracle/oradata/ENN/redo06.log') size 50M;
Database altered.
SQL> alter database add standby logfile thread 1 group 7 ('/u01/app/oracle/oradata/ENN/redo07.log') size 50M;
Database altered.
查看状态
SQL> select group#,status,type,member from v$logfile;
GROUP# STATUS TYPE MEMBER
---------- ------- ------- ----------------------------------------
3 ONLINE /u01/app/oracle/oradata/ENN/redo03.log
2 ONLINE /u01/app/oracle/oradata/ENN/redo02.log
1 ONLINE /u01/app/oracle/oradata/ENN/redo01.log
4 STANDBY /u01/app/oracle/oradata/ENN/redo04.log
5 STANDBY /u01/app/oracle/oradata/ENN/redo05.log
6 STANDBY /u01/app/oracle/oradata/ENN/redo06.log
7 STANDBY /u01/app/oracle/oradata/ENN/redo07.log
4.创建监听
执行netca创建监听器
[oracle@enn oracle]$ netca
修改tnsname文件
[oracle@enn oracle]$ cd $ORACLE_HOME/network/admin
[oracle@enn admin]$ vim tnsname.ora
ENN =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST =192.168.80.15 )(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = enn)
)
)
ENN_DG =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.80.16)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = enn_dg)
)
)
tnsname.ora复制到备库中
[oracle@enn admin]$ scp tnsname.ora oracle@192.168.80.16:$ORACLE_HOME/network/admin/tnsname.ora
oracle@192.168.80.16's password:
tnsname.ora 100% 361 0.4KB/s 00:00
注：可以使用图形配置tnsname
[oracle@enn admin]$ netmgr
附：如果备库tnsping不通, 关闭防火墙
[oracle@enn_dg ~]$ tnsping enn
TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 21-JUL-2014 09:26:09
Copyright (c) 1997, 2013, Oracle. All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.80.15)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = ENN)))
TNS-12543: TNS:destination host unreachable
[root@enn ~]# chkconfig iptables off
[root@enn ~]# service iptables stop
5. 设置主库和备库归档路径
设置主库归档路径
SQL> alter system set log_archive_dest='';
System altered.
SQL> alter system set log_archive_dest_1='LOCATION=/u01/archivelog VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=ENN';
System altered.
设置备库归档路径
SQL> alter system set log_archive_dest_2='SERVICE=enn_dg async VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ENN';
System altered.
配置归档最大进程数
SQL> show parameter log_archive_max
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_max_processes integer 4
SQL> alter system set log_archive_max_processes=30;
System altered.
6. 配置参数文件
生成参数文件
SQL> create pfile ='/u01/app/oracle/initENN.ora' from spfile;
File created.
修改参数文件
[oracle@enn oracle]$ vim initENN.ora
DB_UNIQUE_NAME=ENN
#LOG_ARCHIVE_CONFIG='DG_CONFIG=(ENN,ENN_DG)'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
FAL_SERVER=ENN_DG
FAL_CLIENT=ENN
STANDBY_FILE_MANAGEMENT=AUTO
7. 配置密码文件
备份standby库参数文件和密码文件
[oracle@enn pfile]$ cd $ORACLE_HOME/dbs
[oracle@enn dbs]$ cp orapwENN orapwENN.back
[oracle@enn dbs]$ cp spfileENN.ora spfileENN.ora.bak
将参数文件和密码文件传到备库
[oracle@enn oracle]$ scp initENN.ora oracle@192.168.80.16:/u01/app/oracle/product/11.2.0/dbhome_1/dbs/initENN.ora
[oracle@enn oracle]$ scp /u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwENN oracle@192.168.80.16:/u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwENN
备库操作
关闭防火墙
[root@enn_dg ~]# chkconfig iptables off
[root@enn_dg ~]# service iptables stop
图形方式创建监听器，同主库操作相同
[oracle@enn ~]$ netca
注：可以使用图形方式配置tnsname.ora
[oracle@enn ~]$ netmgr
启动备库到nomount
SQL> startup nomount pfile='/u01/app/oracle/product/11.2.0/dbhome_1/dbs/initENN.ora';
Duplicate复制主库到备库
用rman连接主库和备库
[oracle@enn ~]$ rman target sys/oracle@ENN auxiliary sys/oracle@ENN_DG
Recovery Manager: Release 11.2.0.4.0 - Production on Mon Jul 21 11:02:39 2014
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: ENN (DBID=4141660501)
connected to auxiliary database: ENN (not mounted)
执行同步复制备库操作
RMAN> duplicate target database for standby from active database spfile set db_unique_name 'ENN';
出现错误：
sql statement: alter database mount standby database
RMAN-05538: WARNING: implicitly using DB_FILE_NAME_CONVERT
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 07/21/2014 11:15:58
RMAN-05501: aborting duplication of target database
RMAN-05001: auxiliary file name /u01/app/oracle/oradata/ENN/users01.dbf conflicts with a file used by the target database
RMAN-05001: auxiliary file name /u01/app/oracle/oradata/ENN/undotbs01.dbf conflicts with a file used by the target database
RMAN-05001: auxiliary file name /u01/app/oracle/oradata/ENN/sysaux01.dbf conflicts with a file used by the target database
RMAN-05001: auxiliary file name /u01/app/oracle/oradata/ENN/system01.dbf conflicts with a file used by the target database
附：a、在异机克隆时，如果auxiliary DB使用了与target DB相同的磁盘配置，目录结构以及文件名时，必须指定NOFILENAMECHECK。NOFILENAMECHECK可以阻止检查target DB的数据文件及联机日志文件是否处于正常使用的状态。而auxiliary DB与target DB的磁盘配置，目录结构以及文件名任一不同时，应避免使用NOFILENAMECHECK。
b、对于没有连接到target DB或catalog的情形，应使用BACKUP LOCATION '<bak_dir>'指定备份文件所在的位置。
继续执行duplicate(注：Duplicate时如果主库与备库文件目录结构与文件名都相同时需要使用nofilenamecheck参数)
RMAN> duplicate target database for standby from active database spfile set db_unique_name 'ENN'
nofilenamecheck;
出现问题：
Starting Duplicate Db at 21-JUL-14
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 07/21/2014 11:35:59
RMAN-05501: aborting duplication of target database
RMAN-05537: DUPLICATE without TARGET connection when auxiliary instance is started with spfile cannot use SPFILE clause
MosL(备库必需用主库传过来的pfile启动到nomount状态，才能执行duplicate)
CAUSE:
A DUPLICATE was attempted when the auxiliary database was started with a server parameter file and the SPFILE sub-clause was specified in Duplicate syntax.
RMAN cannot restore the server parameter file if the auxiliary database is already started with a server parameter file.
SOLUTION:
Start the auxiliary database with a client parameter file(pfile) or Do not specify SPFILE sub-clause and retry.
启动备库到nomount是使用pfile
SQL> startup nomount pfile='/u01/app/oracle/product/11.2.0/dbhome_1/dbs/initENN.ora';
重新同步复制
RMAN> duplicate target database for standby from active database spfile set db_unique_name 'ENN' nofilenamecheck;
报错但正常完成复制L
ORACLE error from auxiliary database: ORA-19527: physical standby redo log must be renamed
ORA-00312: online log 1 thread 1: '/u01/app/oracle/oradata/ENN/redo01.log'
RMAN-05535: WARNING: All redo log files were not defined properly.
ORACLE error from auxiliary database: ORA-19527: physical standby redo log must be renamed
ORA-00312: online log 2 thread 1: '/u01/app/oracle/oradata/ENN/redo02.log'
RMAN-05535: WARNING: All redo log files were not defined properly.
ORACLE error from auxiliary database: ORA-19527: physical standby redo log must be renamed
ORA-00312: online log 3 thread 1: '/u01/app/oracle/oradata/ENN/redo03.log'
RMAN-05535: WARNING: All redo log files were not defined properly.
ORACLE error from auxiliary database: ORA-19527: physical standby redo log must be renamed
ORA-00312: online log 4 thread 1: '/u01/app/oracle/oradata/ENN/redo04.log'
RMAN-05535: WARNING: All redo log files were not defined properly.
ORACLE error from auxiliary database: ORA-19527: physical standby redo log must be renamed
ORA-00312: online log 5 thread 1: '/u01/app/oracle/oradata/ENN/redo05.log'
RMAN-05535: WARNING: All redo log files were not defined properly.
ORACLE error from auxiliary database: ORA-19527: physical standby redo log must be renamed
ORA-00312: online log 6 thread 1: '/u01/app/oracle/oradata/ENN/redo06.log'
RMAN-05535: WARNING: All redo log files were not defined properly.
ORACLE error from auxiliary database: ORA-19527: physical standby redo log must be renamed
ORA-00312: online log 7 thread 1: '/u01/app/oracle/oradata/ENN/redo07.log'
RMAN-05535: WARNING: All redo log files were not defined properly.
Finished Duplicate Db at 21-JUL-14
备库日志应用
1. 备库开启日志应用
开启备库日志应用
SQL> alter database recover managed standby database using current logfile disconnect from session;
Database altered.
2. 验证备库日志应用
验证备库接收日志是否应用
SQL> select SEQUENCE#,FIRST_TIME,NEXT_TIME ,APPLIED from v$archived_log order by 1;
SEQUENCE# FIRST_TIME NEXT_TIME APPLIED
---------- ------------------- ------------------- ---------
8 2014-07-21 10:02:18 2014-07-21 11:51:35 YES
9 2014-07-21 11:51:35 2014-07-21 11:52:21 YES
主库切换日志
SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
SQL> /
System altered.
备库查看日志
select SEQUENCE#,FIRST_TIME,NEXT_TIME ,APPLIED from v$archived_log order by 1;
SEQUENCE# FIRST_TIME NEXT_TIME APPLIED
---------- ------------------- ------------------- ---------
8 2014-07-21 10:02:18 2014-07-21 11:51:35 YES
9 2014-07-21 11:51:35 2014-07-21 11:52:21 YES
10 2014-07-21 11:52:21 2014-07-21 12:23:06 YES
11 2014-07-21 12:23:06 2014-07-21 12:23:23 YES
12 2014-07-21 12:23:23 2014-07-21 12:23:27 YES
备库数据库开启read only
SQL> alter database open read only;
alter database open read only
*
ERROR at line 1:
ORA-10456: cannot open standby database; media recovery session may be in
Progress
关闭备库管理
SQL> alter database recover managed standby database cancel;
Database altered.
主库切换日志
SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
备库已接收但未应用
SQL> select SEQUENCE#,FIRST_TIME,NEXT_TIME ,APPLIED from v$archived_log order by 1;
SEQUENCE# FIRST_TIME NEXT_TIME APPLIED
---------- ------------------- ------------------- ---------
8 2014-07-21 10:02:18 2014-07-21 11:51:35 YES
9 2014-07-21 11:51:35 2014-07-21 11:52:21 YES
10 2014-07-21 11:52:21 2014-07-21 12:23:06 YES
11 2014-07-21 12:23:06 2014-07-21 12:23:23 YES
12 2014-07-21 12:23:23 2014-07-21 12:23:27 YES
13 2014-07-21 12:23:27 2014-07-21 12:42:17 NO
14 2014-07-21 12:42:17 2014-07-21 12:42:19 NO
备库open到read only模式
SQL> alter database open read only;
Database altered.
备库开启日志应用
SQL> alter database recover managed standby database using current logfile disconnect from session;
Database altered.
备库已经应用主库日志
SQL> select SEQUENCE#,FIRST_TIME,NEXT_TIME ,APPLIED from v$archived_log order by 1;
SEQUENCE# FIRST_TIME NEXT_TIME APPLIED
---------- ------------------- ------------------- ---------
8 2014-07-21 10:02:18 2014-07-21 11:51:35 YES
9 2014-07-21 11:51:35 2014-07-21 11:52:21 YES
10 2014-07-21 11:52:21 2014-07-21 12:23:06 YES
11 2014-07-21 12:23:06 2014-07-21 12:23:23 YES
12 2014-07-21 12:23:23 2014-07-21 12:23:27 YES
13 2014-07-21 12:23:27 2014-07-21 12:42:17 YES
14 2014-07-21 12:42:17 2014-07-21 12:42:19 YES
3. 验证数据库操作
在主库创建用户
SQL> create user test identified by test;
User created.
在备库查看
SQL> select username from dba_users where username='TEST';
USERNAME
------------------------------
TEST
主备库切换操作验证
1. 主库执行切换
主库执行切换命令
SQL> alter database commit to switchover to physical standby with session shutdown;
Database altered.
重启主库角色变为备库
SQL> startup mount;
ORACLE instance started.
Total System Global Area 835104768 bytes
Fixed Size 2257840 bytes
Variable Size 679480400 bytes
Database Buffers 150994944 bytes
Redo Buffers 2371584 bytes
Database mounted.
SQL> select database_role from v$database;
DATABASE_ROLE
----------------
PHYSICAL STANDBY
2. 备库切换成主库
查看备库状态
SQL> select database_role from v$database;
DATABASE_ROLE
----------------
PHYSICAL STANDBY
SQL> select open_mode from v$database;
OPEN_MODE
--------------------
READ ONLY WITH APPLY
将备库切换成主库
SQL> alter database commit to switchover to primary with session shutdown;
Database altered.
查看备库角色
SQL> select database_role from v$database;
DATABASE_ROLE
----------------
PRIMARY
查看备库状态
SQL> select open_mode from v$database;
OPEN_MODE
--------------------
MOUNTED
Open备库(注：现在已经是主库)
SQL> alter database open;
Database altered.
将原主库切换成备库
SQL> alter database open;
Database altered.
SQL> alter database recover managed standby database using current logfile disconnect from session;
Database altered.
------------end--------------
DBA_建瑾
2014.8.5