今天把在家完整配好 dataguard 的过程记录一下:
Oracle 10G windows 平台 DataGuard
server A: 192.168.11.11 SID=sheng
target server B: 192.168.11.131
oracle version:10.2.0.1
OS platform : windows XP
Primary设置:
1. 设置主数据库为force logging 模式
SQL>sqlplus "/as sysdba"
SQL>alter database force logging;
2. 设置主数据库为归档模式
SQL> archive log list
SQL> shutdown immediate
SQL> startup mount
SQL> alter database archivelog;
SQL> archive log list
3. 添加"备用联机日志文件"
先查看日志文件位置:
SQL>select * from v$logfile;
在添加:
SQL> alter database add standby logfile group 4 ('D:\ORACLE\PRODUCT\10.2.0\ORADATA\sheng\redo04.log') size 50m;
SQL> alter database add standby logfile group 5 ('D:\ORACLE\PRODUCT\10.2.0\ORADATA\sheng\redo05.log') size 50m;
SQL> alter database add standby logfile group 6 ('D:\ORACLE\PRODUCT\10.2.0\ORADATA\sheng\redo06.log') size 50m;
SQL> alter database add standby logfile group 7 ('D:\ORACLE\PRODUCT\10.2.0\ORADATA\sheng\redo07.log') size 50m;
4. 创建主库的初始化参数给备库用
SQL>Create pfile from spfile;
产生的文件名为initsheng.ora 存放目录默认放在$ORACLE_HOME/database下
5. 在主库创建备库的控制文件 和密码文件
SQL>Alter database create standby controlfile as 'D:\oracle\product\10.2.0\control01.ctl';
D:>orapwd file=D:\PWDsheng.ora password=admin entries=5;
缺省情况下,win下口令文件的格式是pwdsid.ora,unix下的格式是orapwSID(大小写敏感)
6. 在主库创建监听和配置tnsnams.ora
listener.ora配置如下:
# listener.ora Network Configuration File: D:\oracle\product\10.2.0\db_1\network\admin\listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = D:\oracle\product\10.2.0\db_1)
(PROGRAM = extproc)
)
(SID_DESC =
(GLOBAL_DBNAME = sheng)
(ORACLE_HOME = D:\oracle\product\10.2.0\db_1)
(SID_NAME = sheng)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.11.11)(PORT = 1521))
)
)
tnsnames.ora配置如下:
# tnsnames.ora Network Configuration File: D:\oracle\product\10.2.0\db_1\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.
primary =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.11.11)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = sheng)
)
)
standby =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.11.131)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = sheng)
)
)
EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)
7. 在initsheng.ora添加以下内容:
*.log_archive_format='%T%S%r.ARC'
*.DB_UNIQUE_NAME='primary'
*.log_archive_config='DG_CONFIG=(primary,standby)'
*.log_archive_dest_1='location=D:\oracle\product\10.2.0\oradata\arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=primary'
*.log_archive_dest_2='SERVICE=standby arch ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=standby'
*.STANDBY_FILE_MANAGEMENT=AUTO
*.LOG_ARCHIVE_DEST_STATE_1=ENABLE
*.LOG_ARCHIVE_DEST_STATE_2=ENABLE
*.FAL_SERVER='standby'
*.FAL_CLIENT='primary'
关闭数据库,在用initsheng.ora重启,并创建spfile文件
SQL>startup pfile='$ORACLE_HOME/database/initsheng.ora'
SQL>create spfile from pfile='$ORACLE_HOME/database/initsheng.ora'
8.
A. 拷贝数据文件,参数文件,控制文件,密码文件到备库上
initsheng.ora参数文件,PWDsheng.ora密码文件考到$ORACLE_HOME/database下的,
controlbak.ctl 考到$ORACLE_base/oradata/sheng/下,并分别重命名为control01.ctl,control01.ctl,control01.ctl
数据文件考到$ORACLE_base/oradata/sheng/下
B. 用Rman拷贝,不用停机
$ rman target /
RMAN> backup full format 'D:/FULL_%d_%T_%s.bak' database include current controlfile for standby;
RMAN> sql 'alter system archive log current';
RMAN> Backup ArchiveLog all format='D:/arch_%d_%T_%s.bak';
备份完后将备份文件拷到standby上同样的目录,强调:同样的目录,在standby进行rman 恢复即可
9. 启动主数据库
SQL>startup
---------------------------------------------------------------------
Standy操作:
1. 用oradim工具创建备库sheng实例
oradim.exe -new -sid sheng -startmode m
oradim.exe -edit -sid sheng -startmode a
2. 创建备库存放数据文件和后台跟踪目录
$ORACLE_BASE\ORADATA\sheng
$ORACLE_BASE\admin\sheng
$ORACLE_BASE\admin\sheng\adump
$ORACLE_BASE\admin\sheng\bdump
$ORACLE_BASE\admin\sheng\cdump
$ORACLE_BASE\admin\sheng\dpdump
$ORACLE_BASE\admin\sheng\pfile
$ORACLE_BASE\admin\sheng\udump
$ORACLE_BASE\admin\sheng\
3. 添加"备用联机日志文件"
SQL>startup mount
先查看日志文件位置:
SQL>select * from v$logfile;
在添加:
SQL> alter database add standby logfile group 4 ('D:\ORACLE\PRODUCT\10.2.0\ORADATA\sheng\redo04.log') size 50m;
SQL> alter database add standby logfile group 5 ('D:\ORACLE\PRODUCT\10.2.0\ORADATA\sheng\redo05.log') size 50m;
SQL> alter database add standby logfile group 6 ('D:\ORACLE\PRODUCT\10.2.0\ORADATA\sheng\redo06.log') size 50m;
SQL> alter database add standby logfile group 7 ('D:\ORACLE\PRODUCT\10.2.0\ORADATA\sheng\redo07.log') size 50m;
4. 在备库创建监听和配置tnsnams.ora
listener.ora配置如下:
# listener.ora Network Configuration File: D:\oracle\product\10.2.0\db_1\network\admin\listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = D:\oracle\product\10.2.0\db_1)
(PROGRAM = extproc)
)
(SID_DESC =
(GLOBAL_DBNAME = sheng)
(ORACLE_HOME = D:\oracle\product\10.2.0\db_1)
(SID_NAME = sheng)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.11.131)(PORT = 1521))
)
)
tnsnames.ora配置如下:
# tnsnames.ora Network Configuration File: D:\oracle\product\10.2.0\db_1\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.
primary =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.11.11)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = sheng)
)
)
standby =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.11.131)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = sheng)
)
)
EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)
5. 测试主备之间网络连通
primary:
C:>lsnrctl start
C:>tnsping standby
standby
C:>lsnrctl start
C:>tnsping primary
6. 配置备库初始化参数
编辑$ORACLE_HOME/database目录下的initsheng.ora添加以下内容
*.log_archive_format='%T%S%r.ARC'
*.DB_UNIQUE_NAME='standby'
*.log_archive_config='DG_CONFIG=(primary,standby)'
*.log_archive_dest_1='location=D:\oracle\product\10.2.0\oradata\arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=standby'
*.log_archive_dest_2='SERVICE=primary arch ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=primary'
*.STANDBY_FILE_MANAGEMENT=AUTO
*.LOG_ARCHIVE_DEST_STATE_1=ENABLE
*.LOG_ARCHIVE_DEST_STATE_2=ENABLE
*.FAL_SERVER='primary'
*.FAL_CLIENT='standby'
*.DB_FILE_NAME_CONVERT='D:\oracle\product\10.2.0\oradata\sheng\','C:\oracle\product\10.2.0\oradata\sheng\'
注明:上面这个DB_FILE_NAME_CONVERT 是在datafie路径不同的情况下使用的参数。
7. 启动备用数据库
C:>set ORACLE_SID=sheng
SQL>sqlplus "/as sysdba"
SQL>startup nomount pfile='D:\oracle\product\10.2.0\db_1\database\initsheng.ora';
SQL>create spfile from pfile='D:\oracle\product\10.2.0\db_1\database\initsheng.ora';
若采用Rman备份的, 则在此standby 端进行 Rman还原数据库:
$rman target sys/admin@primary auxiliary /
RMAN> duplicate target database for standby dorecover nofilenamecheck;
SQL>alter database mount standby database ;
SQL>alter database recover managed standby database disconnect from session;
---------------------------------------------------------------------------
测试
注意Data Guard 启动顺序:
启动顺序:先standby ,后primary;
关闭顺序:先primary 后standby;
在备库将实例启动到mount 状态:
SQL> startup nomount;
SQL>alter database mount standby database ;
SQL>alter database recover managed standby database disconnect from session;
如果要查询备库中的数据:
SQL>ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL; (注意,这和上面 alter database .... disconnect from session 是互坼的)
alter database open read only;
select * from table_name
在备库启动监听:
$lsnrctl start
在主库启动实例:
SQL> startup;
在主库启动监听:
$lsnrctl start
在主库验证归档目录是否有效:
SQL> SELECT STATUS,DESTINATION, ERROR FROM V$ARCHIVE_DEST;
如果有错误,要排查原因。
SQL> alter system switch logfile;
SQL> select max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
--------------
70
主备查询结果一致,Data Guard 搭建结束。
1. 测试主库产生的归档日志是否能正常传送到归档日志
主库进行日志切换:
SQL>Alter system switch logfile;
然后分别查看主库和备库的D:\arch目录下是否产生了同样的归档日志
文件。
select max(sequence#) from v$archived_log;