最后更新时间：2013年8月4日,星期日
★ oracle 10G安装环境
数据库软件安装环境不详细描述，网上到处有这方面资料，下面只简单描述下。
也可参考官方文档：
http://docs.oracle.com/cd/B19306_01/install.102/b15667/pre_install.htm
yum install libXp
oracle用户.bash_profile文件增加
export ORACLE_BASE=/oracle
export ORACLE_HOME=$ORACLE_BASE/product/10.2.0/db_1
export ORACLE_SID=primary
export ORACLE_TERM=xterm
export PATH=/usr/sbin:$ORACLE_HOME/bin:$PATH
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib
export CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib
if [ $USER = "oracle" ]; then
if [ $SHELL = "/bin/ksh" ]; then
ulimit -p 16384
ulimit -n 65536
else
ulimit -u 16384 -n 65536
fi
fi
/etc/security/limits.conf中添加以下内容
oracle soft nproc 2047
oracle hard nproc 16384
oracle soft nofile 1024
oracle hard nofile 65536
oracle soft stack 10240
/etc/sysctl.conf 增加
kernel.shmall = 2097152
kernel.shmmax = 2147483648
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
fs.file-max = 65536
net.ipv4.ip_local_port_range = 1024 65000
net.core.rmem_default = 262144
net.core.rmem_max = 262144
net.core.wmem_default = 262144
net.core.wmem_max = 262144
/etc/pam.d/login 增加：
session required pam_limits.so
安装第二台oracle，只安装数据库软件，创建监听，不建库；
★ dg配置
准备工作：
1. Enabel Forced Logging
SQL> alter database force logging;
select force_logging from v$database;
2. MOUNT状态下开归档
SQL> alter database archivelog;
SQL> archive log list;
3. 创建 Password File
默认有这文件，直接传到dg2主机目录：$ORACLE_HOME/dbs ，修改文件名为 orapwstandby
4. 配置tnsnames.ora
DG1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = dg1.bugbeta.cn)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = primary.bugbeta.cn)
)
)
DG2=
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = dg2.bugbeta.cn)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = standby.bugbeta.cn)
)
)
★ 创建standby数据库
DG1：
先生成pfile，修改好，再关实例，使用pfile创建spfile;
create pfile='/tmp/a.pfile' from spfile;
修改pfile:
*.db_unique_name=primary
*.log_archive_config='dg_config=(primary,standby)' --这里写的是db_unique_name
*.log_archive_dest_1='LOCATION=/oracle/data/primary/arch valid_for=(all_logfiles,all_roles) db_unique_name=primary'
*.log_archive_dest_2='service=DG2 arch valid_for=(online_logfiles,primary_role) db_unique_name=standby' --这里service写的是tns中配置
--以下为作为standby角色的配置，primary与standby角色互换则需在primary也配置
*.fal_server=DG2 --tns中配置
*.fal_client=DG1 --tns中配置
*.standby_file_management=auto
再使用上述修改好的pfile创建spfile；
创建standby用的control文件
SQL> alter database create standby controlfile as '/home/oracle/standby.ctl';
select FILE_NAME,TABLESPACE_NAME from dba_data_files;
primary>alter database begin backup;
primary>host scp /oracle/data/orcl/*.dbf dg2:/oracle/data/orcl/
primary>alter database end backup;
DG2:
将控制文件，密码文件，数据文件都复制到standby对应目录；
mkdir -p /oracle/data/standby/arch
echo "db_name=orcl" >/oracle/product/10.2.0/db_1/dbs/initstandby.ora
启动数据库到mount(使用修改过的pfile启动)
export ORACLE_SID=standby
SQL> startup pfile='/home/oracle/a.pfile' mount;
SQL> create spfile from pfile='/home/oracle/a.pfile';
SQL> alter database recover managed standby database disconnect from session; --应用日志；
SQL> alter database recover managed standby database using current logfile disconnect from session; --开启实时应用日志
--以下仅为作查询服务前操作
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL; --暂停应用日志
alter database open read only; --只读模式开启，可做查询操作；
★ 检查DG物理standby工作是否正常
1. 检查归档日志（DG1,DG2）
col name for a50
set linesize 200
SQL> SELECT NAME,SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE# desc;
2. 强制在DG1上切换日志
DG1> alter system switch logfile;
DG1>select name,SEQUENCE#,STANDBY_DEST,ARCHIVED,APPLIED,DELETED,STATUS,fal from v$archived_log order by sequence#;
检查日志传输到DG2且是否已应用：
DG2>select name,SEQUENCE#,STANDBY_DEST,ARCHIVED,APPLIED,DELETED,STATUS,fal from v$archived_log order by sequence#;
★ 常用视图
最最常用的两个视图：
Select dest_name,status,error from v$archive_dest; --检查DG配置spfile是否正确
select name,SEQUENCE#,STANDBY_DEST,ARCHIVED,APPLIED,DELETED,STATUS,fal from v$archived_log; --检查standby日志应用情况
SELECT DESTINATION, STATUS, ARCHIVED_THREAD#, ARCHIVED_SEQ# FROM V$ARCHIVE_DEST_STATUS; --检查最近归档在各destination状态
Select dest_name,status,error from v$archive_dest; --可检查LOG_ARCHIVE_DEST_2是否设置正确；
select name,SEQUENCE#,STANDBY_DEST,ARCHIVED,APPLIED,DELETED,STATUS,fal from v$archived_log order by sequence#; --检查日志状态 物理standby
SELECT SEQUENCE#,FIRST_CHANGE#,NEXT_CHANGE#,TIMESTAMP,APPLIED FROM DBA_LOGSTDBY_LOG; --检查日志应用状态 逻辑standby
select THREAD#,SEQUENCE#,STATUS from v$managed_standby;
select THREAD# ,SEQUENCE#,STATUS,ARCHIVED from v$standby_log;
select FACILITY,SEVERITY,MESSAGE from v$dataguard_status;
SELECT EVENT_TIME,STATUS,EVENT FROM DBA_LOGSTDBY_EVENTS ORDER BY EVENT_TIMESTAMP;
select * from V$LOGSTDBY_STATE; --逻辑Standby是否启用实时应用；
SELECT * FROM V$ARCHIVE_GAP; --查看GAP
参考
http://docs.oracle.com/cd/B19306_01/server.102/b14239/create_ps.htm#i68626 创建物理standby
http://docs.oracle.com/cd/B28359_01/backup.111/b28273/rcmsubcl010.htm %s之类格式说明
http://docs.oracle.com/cd/B19306_01/server.102/b14239/log_transport.htm#i1008089 redo传输检查