Linux RHEL5.5 创建 oracle 10g Dataguard
2 dataguard建立
2.1 dataguard规划
Host_IP DB_NAME DB_UNIQUE_NAME Net Service Name
主库192.168.2.135 syw syw syw
备库192.168.2.136 syw syw01 syw01
主库归档路径 /sywdg/arch1
备库归档路径 /sywdg/arch1
保护模式：默认最大性能模式
2.2主库准备工作
2.2.1 检查数据库是否支持Data Guard是否归档模式，Enable force logging
SQL> select * from v$option where parameter = 'Managed Standby';
PARAMETER VALUE
------------------ -------------------------------
Managed Standby TRUE
SQL> archive log list （先检查是否归档模式，不是则修改）
数据库日志模式 存档模式
自动存档 启用
存档终点 /sywdg/arch1/
最早的联机日志序列 164
下一个存档日志序列 166
当前日志序列 166
SQL> alter database force logging;
数据库已更改。
2.2.2 如果主库没有密码文件则建立密码文件，从而可以OS验证的方式登陆
$orapwd file=$ORACLE_HOME/dbs/orapwsyw password=oracle entries=5
2.2.3、配置standby redolog（最佳性能模式可以忽略，如果将来变成备库且要转为其它两种模式则要建立）查看当前redolog大小
SQL> select group#,members,bytes/1024/1024,status from v$log;
GROUP# MEMBERS BYTES/1024/1024 STATUS
---------- ---------- --------------- ----------------
1 1 50 CURRENT
2 1 50 INACTIVE
3 1 50 INACTIVE
现有三个日志组，每个组内有一个成员，每个成员的大小为50MB
2.2.4在主库增加standby redolog
SQL> alter database add standby logfile
2 group 4 ('/u01/app/oracle/oradata/stdby_redo04.log') size 50m,
3 group 5 ('/u01/app/oracle/oradata/stdby_redo05.log') size 50m,
4 group 6 ('/u01/app/oracle/oradata/stdby_redo06.log') size 50m,
5 group 7 ('/u01/app/oracle/oradata/stdby_redo07.log') size 50m;
standby redolog的组数参考公式：(online redolog组数 + 1) * 数据库线程数；单机线程数为1，RAC一般为2。
standby redolog的组成员数和大小也尽量和online redolog一样。
2.3 设置主库初始化参数
启动db接受或发送redo data，包括所有库的db_unique_name
SQL> alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(SYW,SYW01)' scope=spfile;
设置主库归档目的地
SQL>alter system set LOG_ARCHIVE_DEST_1='LOCATION=/sywdg/arch1 VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=SYW' scope=spfile;
当该库充当主库角色时，设置物理备库redo data的传输目的地
SQL> alter system set LOG_ARCHIVE_DEST_2='SERVICE=SYW01 LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=SYW01' scope=spfile;
修改最大ARCn进程数
SQL> alter system set LOG_ARCHIVE_MAX_PROCESSES=5 scope=spfile;
允许redo传输服务传输数据到目的地，默认是enable
SQL> alter system set LOG_ARCHIVE_DEST_STATE_1=ENABLE scope=spfile;
允许redo传输服务传输数据到目的地，默认是enable
SQL> alter system set LOG_ARCHIVE_DEST_STATE_2=ENABLE scope=spfile;
主库备库库sys密码要一致，默认是exclusive
SQL> alter system set REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE scope=spfile;
--以下是主库切换为备库，充当备库角色时的一些参数设置，如果不打算做数据库切换就不用设置了
配置网络服务名，假如转换为备库角色时，从这里获取丢失的归档文件
SQL> alter system set FAL_SERVER=syw01 scope=spfile;
配置网络服务名，fal_server拷贝丢失的归档文件到这里
SQL> alter system set FAL_CLIENT=syw scope=spfile;
前为切换后的主库路径，后为切换后的备库路径，如果主备库目录结构完全一样，则无需设定
SQL> alter system set DB_FILE_NAME_CONVERT='
/u01/app/oracle/oradata/syw01',' /u01/app/oracle/oradata/syw' scope=spfile;
同上，这两个名字转换参数是主备库的路径映射关系，可能会是路径全名，看情况而定
SQL> alter system set LOG_FILE_NAME_CONVERT=' /u01/app/oracle/oradata/syw01',' /u01/app/oracle/oradata/syw' scope=spfile;
设置auto后当主库的datafiles增删时备库也同样自动操作，且会把日志传送到备库standby_archive_dest参数指定的目录下，确保该目录存在，如果你的存储采用文件系统没有问题，但是如果采用了裸设备，你就必须将该参数设置为manual
SQL> alter system set STANDBY_FILE_MANAGEMENT=auto scope=spfile;
一般和LOG_ARCHIVE_DEST_1的位置一样，如果备库采用ARCH传输方式，那么主库会把归档日志传到该目录下
SQL> alter system set STANDBY_ARCHIVE_DEST='LOCATION=/sywdg/arch1' scope=spfile;
有了以上参数设置，则无论该库充当主库角色还是备库角色都无需再修改了
配置完成之后重新启动主数据库
SQL> shutdown immediate;
数据库已经关闭。
已经卸载数据库。
ORACLE 例程已经关闭。
SQL> startup
ORACLE 例程已经启动。
Total System Global Area 285212672 bytes
Fixed Size 2020224 bytes
Variable Size 96472192 bytes
Database Buffers 184549376 bytes
Redo Buffers 2170880 bytes
数据库装载完毕。
数据库已经打开。
2.4 配置主库备库监听listener.ora及tnsnames.ora，sqlnet.ora
[oracle@DB_primary admin]$ more listener.ora
# listener.ora Network Configuration File: /u01/app/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 = /u01/app/oracle/product/10.2.0/db_1)
(PROGRAM = extproc)
)
(SID_DESC =
(GLOBAL_DBNAME = SYW)
(ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1)
(SID_NAME = SYW)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = DB_primary)(PORT = 1521))
)
)
[oracle@DB_primary admin]$ more tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/10.2.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
SYW =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.135)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = syw)
)
)
SYW01 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.136)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = syw01)
)
)
EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)
[oracle@DB_primary admin]$ more sqlnet.ora
# sqlnet.ora Network Configuration File: /u01/app/oracle/product/10.2.0/db_1/network/admin/sqlnet.ora
# Generated by Oracle configuration tools.
NAMES.DIRECTORY_PATH= (TNSNAMES)
备库监听信息
[oracle@DB_standby admin]$ more listener.ora
# listener.ora Network Configuration File: /u01/app/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 = /u01/app/oracle/product/10.2.0/db_1)
(PROGRAM = extproc)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = DB_standby)(PORT = 1521))
)
)
[oracle@DB_standby admin]$ more tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/10.2.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
SYW01 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.136)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = syw01)
)
)
SYW =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.135)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = syw)
)
)
EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)
[oracle@DB_standby admin]$ more sqlnet.ora
# sqlnet.ora Network Configuration File: /u01/app/oracle/product/10.2.0/db_1/network/admin/sqlnet.ora
# Generated by Oracle configuration tools.
NAMES.DIRECTORY_PATH= (TNSNAMES)
监听配置完成之后正常启动监听，在主库备库分别执行tnsping命令
[oracle@DB_primary ~]$ tnsping syw
[oracle@DB_primary ~]$ tnsping syw01
[oracle@DB_standby ~]$ tnsping syw
[oracle@DB_standby ~]$ tnsping syw01
确保主备都可以正常通信
2.5、RMAN备份主库数据
关闭应用服务器，停止监听，开始rman备份：
[oracle@DB_primary dgbak]$ lsnrctl stop
LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 22-8月 -2011 10:55:03
Copyright (c) 1991, 2005, Oracle. All rights reserved.
正在连接到 (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))命令执行成功
RMAN> backup full database format '/sywdg/dgbak/backup_%T_%s_%p.bak';
启动 backup 于 22-8月 -11
使用目标数据库控制文件替代恢复目录
分配的通道: ORA_DISK_1
通道 ORA_DISK_1: sid=137 devtype=DISK
通道 ORA_DISK_1: 启动全部数据文件备份集
通道 ORA_DISK_1: 正在指定备份集中的数据文件
输入数据文件 fno=00007 name=/u01/app/oracle/oradata/syw/tbs_syw.ora
输入数据文件 fno=00001 name=/u01/app/oracle/oradata/syw/system01.dbf
输入数据文件 fno=00004 name=/u01/app/oracle/oradata/syw/users01.dbf
输入数据文件 fno=00006 name=/u01/app/oracle/oradata/syw/tbs_cms.ora
输入数据文件 fno=00003 name=/u01/app/oracle/oradata/syw/sysaux01.dbf
输入数据文件 fno=00005 name=/u01/app/oracle/oradata/syw/TBS_IDX_SYW.ora
输入数据文件 fno=00002 name=/u01/app/oracle/oradata/syw/undotbs01.dbf
通道 ORA_DISK_1: 正在启动段 1 于 22-8月 -11
通道 ORA_DISK_1: 已完成段 1 于 22-8月 -11
段句柄=/sywdg/dgbak/backup_20110822_27_1.bak 标记=TAG20110822T121100 注释=NONE
通道 ORA_DISK_1: 备份集已完成, 经过时间:00:02:16
备份集中包括当前控制文件
在备份集中包含当前的 SPFILE
通道 ORA_DISK_1: 正在启动段 1 于 22-8月 -11
通道 ORA_DISK_1: 已完成段 1 于 22-8月 -11
通道 ORA_DISK_1: 备份集已完成, 经过时间:00:00:03
完成 backup 于 22-8月 -11
RMAN> sql "alter system archive log current";
sql 语句: alter system archive log current
RMAN> backup archivelog all format='/sywdg/dgbak/arch_%T_%s_%p.bak';
启动 backup 于 22-8月 -11
当前日志已存档
完成 backup 于 22-8月 -11
2.6、在主库上建立备库控制文件
[oracle@DB_primary ~]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on 星期一 8月 22 12:21:27 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
连接到:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
SQL> alter database create standby controlfile as '/sywdg/dgbak/stdby_control01.ctl';
[oracle@DB_primary ~]$ cd /sywdg/dgbak/
[oracle@DB_primary dgbak]$ ls -al
-rw-r----- 1 oracle oinstall 147193344 08-22 12:19 arch_20110822_29_1.bak
-rw-r----- 1 oracle oinstall 1855897600 08-22 12:13 backup_20110822_27_1.bak
-rw-r----- 1 oracle oinstall 7143424 08-22 12:13 backup_20110822_28_1.bak
-rw-r----- 1 oracle oinstall 7061504 08-22 12:21 stdby_control01.ctl
复制备库控制文件
[oracle@DB_primary dgbak]$ cp stdby_control01.ctl stdby_control02.ctl
[oracle@DB_primary dgbak]$ cp stdby_control01.ctl stdby_control03.ctl
[oracle@DB_primary dgbak]$ ls -lh
-rw-r----- 1 oracle oinstall 141M 08-22 12:19 arch_20110822_29_1.bak
-rw-r----- 1 oracle oinstall 1.8G 08-22 12:13 backup_20110822_27_1.bak
-rw-r----- 1 oracle oinstall 6.9M 08-22 12:13 backup_20110822_28_1.bak
-rw-r----- 1 oracle oinstall 6.8M 08-22 12:21 stdby_control01.ctl
-rw-r----- 1 oracle oinstall 6.8M 08-22 12:22 stdby_control02.ctl
-rw-r----- 1 oracle oinstall 6.8M 08-22 12:22 stdby_control03.ctl
2.7、创建备库init参数文件
在主库上执行
SQL> create pfile='/sywdg/dgbak/initsyw.ora' from spfile;
编辑pfile 注意主备库不同角色的属性配置，注意文件路径等，注意db_name要和主库一致，主要是以下参数
syw.__db_cache_size=167772160
syw01.__db_cache_size=180355072
syw.__java_pool_size=4194304
syw01.__java_pool_size=4194304
syw.__large_pool_size=4194304
syw01.__large_pool_size=4194304
syw.__shared_pool_size=104857600
syw01.__shared_pool_size=92274688
syw.__streams_pool_size=0
syw01.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/syw01/adump'
*.audit_trail='os'
*.background_dump_dest='/u01/app/oracle/admin/syw01/bdump'
*.compatible='10.2.0.1.0'
*.control_files='/u01/app/oracle/oradata/syw01/stdby_control01.ctl','/u01/app/oracle/oradata/syw01/stdby_control02.ctl','/u01/app/oracle/oradata/syw01/stdby_control03.ctl'
*.core_dump_dest='/u01/app/oracle/admin/syw01/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_file_name_convert=/u01/app/oracle/oradata/syw',' /u01/app/oracle/oradata/syw01'
*.db_name='syw'
*.db_recovery_file_dest='/u01/app/oracle/flash_recovery_area'
*.db_recovery_file_dest_size=2147483648
*.db_unique_name='syw01'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=sywXDB)'
*.fal_client='SYW01'
*.fal_server='SYW'
*.job_queue_processes=10
*.log_archive_config='DG_CONFIG=(SYW01,SYW)'
*.log_archive_dest_1='LOCATION=/sywdg/arch1/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=SYW01'
*.log_archive_dest_2='SERVICE=syw LGWR SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=SYW'
*.log_archive_dest_state_1='ENABLE'
*.log_archive_dest_state_2='ENABLE'
*.log_archive_max_processes=5
*.log_file_name_convert='/u01/app/oracle/oradata/syw','/u01/app/oracle/oradata/syw01'
*.nls_language='SIMPLIFIED CHINESE'
*.nls_territory='CHINA'
*.open_cursors=300
*.optimizer_secure_view_merging=FALSE
*.pga_aggregate_target=94371840
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=283115520
*.standby_archive_dest='LOCATION=/sywdg/arch1'
*.standby_file_management='AUTO'
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/u01/app/oracle/admin/syw01/udump'
*.user_dump_dest='/u01/app/oracle/admin/syw/udump'
在主库上用oracle身份执行，修改备库pfile
$ cp initsyw.ora initsyw01.ora
2.8 复制文件到备库
拷贝上面生成的文件backup_%T.bak、stdby_control01/02/03.ctl、initsyw01.ora到备库所在主机，注意rman备份的文件在主备库主机上目录要一致。
[oracle@DB_primary dgbak]$ scp *.bak oracle@192.168.2.136:/sywdg/dgbak/
oracle@192.168.2.136's password:
arch_20110822_29_1.bak 100% 140MB 17.6MB/s 00:08
backup_20110822_27_1.bak 100% 1770MB 17.9MB/s 01:39
backup_20110822_28_1.bak
[oracle@DB_primary dgbak]$ scp *.ctl oracle@192.168.2.136:/u01/app/oracle/oradata/syw/
oracle@192.168.2.136's password:
stdby_control01.ctl 100% 6896KB 6.7MB/s 00:00
stdby_control02.ctl 100% 6896KB 6.7MB/s 00:01
stdby_control03.ctl 100% 6896KB 6.7MB/s 00:00
[oracle@DB_primary dgbak]$ scp initsyw01.ora oracle@192.168.2.136:/u01/app/oracle/product/10.2.0/db_1/dbs/
oracle@192.168.2.136's password:
initsyw01.ora 100% 1663 1.6KB/s 00:00
2.9建立备库相关文件和目录
(备库主机已经装了oracle软件,且目录结构和主库一致)
2.9.1设置环境变量并建立备库一些必需目录
$ export ORACLE_BASE=/u01/app/oracle
$ export ORACLE_HOME=/u01/app/oracle/product/10.2.0/db_1
$ export ORACLE_SID=SYW01
$ mkdir -p $ORACLE_BASE/admin/syw01/adump
$ mkdir -p $ORACLE_BASE/admin/syw01/bdump
$ mkdir -p $ORACLE_BASE/admin/syw01/cdump
$ mkdir -p $ORACLE_BASE/admin/syw01/udump
--以下目录要看哪些地方可能会存放数据库文件，注意不能少建
$ mkdir -p /u01/app/oracle/oradata/syw01
$ mkdir -p /sywdg/arch1/
2.9.2、在备库主机上生成密码文件，且sys密码和主库得一致
$ orapwd file=$ORACLE_HOME/dbs/orapwsyw01 password=oracle entries=5
2.9.3在备库上建立spfile
[oracle@DB_standby ~]$ sqlplus / as sysdba
SQL> create spfile from pfile;
2.10 恢复创建standby database
启动物理备库
SQL> startup nomount;
ORACLE 例程已经启动。
Total System Global Area 285212672 bytes
Fixed Size 2020224 bytes
Variable Size 92277888 bytes
Database Buffers 188743680 bytes
Redo Buffers 2170880 bytes
SQL> alter database mount standby database;
数据库已更改。
$ rman target / (要求主备库rman备份文件的存放路径和文件名一致)
RMAN> restore database;
RMAN> restore archivelog all;
配置备库的standby redolog
（最佳性能模式可以忽略，如果要转为其它两种模式则要建立）
SQL> alter database add standby logfile
2 group 4 ('/u01/app/oracle/oradata/stdby_redo04.log') size 50m,
3 group 5 ('/u01/app/oracle/oradata/stdby_redo05.log') size 50m,
4 group 6 ('/u01/app/oracle/oradata/stdby_redo06.log') size 50m,
5 group 7 ('/u01/app/oracle/oradata/stdby_redo07.log') size 50m;
standby redolog的组数参考公式：(online redolog组数 + 1) * 数据库线程数；单机线程数为1，RAC一般为2。
standby redolog的组成员数和大小也尽量和online redolog一样。
SQL> select status from v$instance;
STATUS
------------
MOUNTED
2.8、在备库上,启动redo apply
SQL> alter database recover managed standby database disconnect from session;