Linux平台搭建Oracle 11g单机dataguard容灾的操作文档

数据库环境oracle 11.1.0.6
操作系统环境 linux redhat update 4 32位
ORACLE_BASE为/u01/app/oracle
ORACLE_HOME为/u01/app/oracle/product/11.1.6/db_1
主库数据文件存放目录/oradata/asm11g/下
备库数据文件存放目录/oradata/standby/下

1.配置主数据库为归档模式和强制使用日志模式
SQL>startup force mount;
SQL>alter database archivelog;
SQL>alter database open;
SQL> ALTER DATABASE FORCE LOGGING;

2.设置主库spfile参数如下:
DB_NAME=asm11g
DB_UNIQUE_NAME=asm11g
LOG_ARCHIVE_CONFIG='DG_CONFIG=(asm11g,standby)'
CONTROL_FILES='/oradata/asm11g/controlfile/control1.ctl'
LOG_ARCHIVE_DEST_1=
'LOCATION=/u01/app/oracle/archivelog
VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
DB_UNIQUE_NAME=asm11g'
LOG_ARCHIVE_DEST_2=
'SERVICE=standby ASYNC
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
DB_UNIQUE_NAME=standby'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
LOG_ARCHIVE_MAX_PROCESSES=30

3.新建备库存放目录
cd $ORACLE_BASE
CD admin
mkdir standby
cd standby
mkdir adump
mkdir dpdump
mkdir pfile
cd /oradata
mdkir standby

4.备份主库数据文件 用rman工具
rman target /
backup database format '/oradata/%U.bak';

5.在主库生成备库控制文件
SQL>startup force mount;
SQL> ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/oradata/standby/control01.ctl';
SQL> ALTER DATABASE OPEN;

6.从主库创建参数文件用于备库
SQL> CREATE PFILE='/oradata/standby/initstandby.ora' FROM SPFILE;
修改备库参数文件如下 vi initstandby.ora:
DB_NAME=asm11g
DB_UNIQUE_NAME=standby
LOG_ARCHIVE_CONFIG='DG_CONFIG=(asm11g,standby)'
CONTROL_FILES='/oradata/standby/control1.ctl'
DB_FILE_NAME_CONVERT='asm11g','standby'
LOG_FILE_NAME_CONVERT='asm11g','standby'
LOG_ARCHIVE_FORMAT=log%t_%s_%r.arc
LOG_ARCHIVE_DEST_1=
'LOCATION=/oradata/standby/archivelog.
VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
DB_UNIQUE_NAME=standby'
LOG_ARCHIVE_DEST_2=
'SERVICE=asm11g ASYNC
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
DB_UNIQUE_NAME=asm11g'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
STANDBY_FILE_MANAGEMENT=AUTO
FAL_SERVER=asm11g
FAL_CLIENT=standby

7.备库创建密码文件 注意要和主库密码一致
cd $ORACLE_HOME/dbs
orapwd file=orapwstandby password=abcdefg entries=10 ignorecase=y

8.配置主库和备库的listener 采用静态注册 如下所示:
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = asm11g.localdomain)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /u01/app/oracle/product/11.1.6/db_1)
(PROGRAM = extproc)
)
(SID_DESC =
(GLOBAL_NAME =asm11g)
(ORACLE_HOME = /u01/app/oracle/product/11.1.6/db_1)
(SID_NAME =asm11g)
)
(SID_DESC =
(GLOBAL_NAME =standby)
(ORACLE_HOME = /u01/app/oracle/product/11.1.6/db_1)
(SID_NAME =standby)
)
)
#lsnrctl stop
#lsnrctl start

9.配置tnsnames.ora文件
[oracle@asm11g admin]$ more tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.1.6/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
ASM11G =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = asm11g.localdomain)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = asm11g)
)
)
standby =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = asm11g.localdomain)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME =standby)
)
)
tnsping 验证
[oracle@asm11g admin]$ tnsping asm11g
TNS Ping Utility for Linux: Version 11.1.0.6.0 - Production on 25-JUL-2008 13:35:21
Copyright (c) 1997, 2007, Oracle. All rights reserved.
Used parameter files:

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = asm11g.localdomain)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = asm11g)))
OK (20 msec)
[oracle@asm11g admin]$ tnsping standby
TNS Ping Utility for Linux: Version 11.1.0.6.0 - Production on 25-JUL-2008 13:35:24
Copyright (c) 1997, 2007, Oracle. All rights reserved.
Used parameter files:

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = asm11g.localdomain)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME =standby)))
OK (10 msec)
[oracle@asm11g admin]$

10.启动standby 数据库
export ORACLE_SID=standby
sqlplus / as sysdba
create spfile from pfile='/oradata/standby/initstandby.ora';
startup nomount;
alter database mount standby database;
host
恢复主库的数据文件到备库
rman target /
restore database;
exit;
添加standby logfile 启动到恢复管理模式
idle> alter database add standby logfile '/oradata/standby/redo04.log' size 50M;
Database altered.
Elapsed: 00:00:01.06
idle> alter database add standby logfile '/oradata/standby/redo05.log' size 50M;
Database altered.
Elapsed: 00:00:01.18
idle> alter database add standby logfile '/oradata/standby/redo06.log' size 50M;
Database altered.
Elapsed: 00:00:00.85
idle> recover managed standby database disconnect from session;
Media recovery complete.
..

11.验证归档是否正常传送
主库:
alter system switch logfile;
备库:是否传送过来
SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME
2> FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
是否已经apply
SQL> SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG
2 ORDER BY SEQUENCE#;
检查主库连到备库的参数文件是否正常
select * from v$archive_dest_status;

在配置完成后发现归档日志不能传送到备库
select * from v$archive_dest_status;
发现log_archive_dest_2的status为error
error报ORA-16191: Primary log shipping client not logged on standby
检查数据库日志也发现下面错误
Error 1017 received logging on to the standby
------------------------------------------------------------
Check that the primary and standby are using a password file
and remote_login_passwordfile is set to SHARED or EXCLUSIVE,
and that the SYS password is same in the password files.
returning error ORA-16191
------------------------------------------------------------
Error 16191 connecting to destination LOG_ARCHIVE_DEST_2 standby host 'standby'
Error 16191 attaching to destination LOG_ARCHIVE_DEST_2 standby host 'standby'
ORA-16191: Primary log shipping client not logged on standby
解决方法:重建主库和备库的密码文件 使密码一致
加上ignorecase参数为y
最后重新启动下主库
再查
select * from v$archive_dest_status;
log_archive_dest_2的status为valid