一、Oracle11DB+DG配置
1. 单机环境介绍(PRIMARY DATABASE)
主库 primary
public ip 192.168.0.252
instance oracle
db_name oracle
storage mode /orasjrz/crds3db/oradata /orasjrz/crds3db/oraarch
2. 单机环境介绍（standby database）
数据文件可放至本地，也可以放至ASM上，本实验中先放至本地文件方式存放
备机:只需要装实冽 和linux.
ip 192.168.0.253
instance oracle
storage mode /orasjrz/crds3db/oradata /orasjrz/crds3db/oraarch
3.edit编辑主用 hosts文件文件后面加入主备红色部分Ip地址
#Public Network – (eth0)
127.0.0.1 localhost.localdomain localhost
::1 localhost6.localdomain6 localhost6
192.168.0.252 primary
192.168.0.253 standby
edit编辑备用用 hosts文件后面加入主备Ip地址
#Public Network – (eth0)
192.168.0.252 primary
192.168.0.253 standby
4. 检查主库环境
4.1. 启动archivelog归档模式
查看是否在归档模式如果没有在归档模式下面就庶修改成归档模式
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 54
Next log sequence to archive 56
Current log sequence 56
查看当前恢复参数
SQL> show parameter RECOVERY;
NAME TYPE VALUE
———————————— ———– ——————————
db_recovery_file_dest string +DG_RECOVERY
db_recovery_file_dest_size big integer 2G
recovery_parallelism integer 0
4.2. 启动主库FORCE_LOGGING模式
首新查询是启动FORCE_LOGGING是否为YES如果不是就更改
SQL> select FORCE_LOGGING from v$database;
FOR
—
YES
exit
SQL> alter database FORCE LOGGING;
Database altered.
5. 主库tnsnames.ora和listener.ora配置
首先配置主库节点hisa的tnsnames.ora和listener.ora
5.1. listener.ora 主库主库的Listener.ora不需要改
主库的listener.ora在/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
5.2. tnsnames.ora （PRIMARY)
cd /app/oracle/product/11.2.0/db_1/network/admin/
vi tnsnames.ora
tnsnames.ora 把以下的红色部分内容加入到tnsnames.ora中。
# tnsnames.ora Network Configuration File: /app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
primary =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.20.101)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = oracle)
)
)
standby =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.20.102)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = oracle)
)
)
6. 备库tnsnames.ora和listener.ora配置
用oracle用户生成备库上的（standby）主机上的listener.ora和tnsnames.ora
6.1. listener.ora
cd /app/oracle/product/11.2.0/db_1/network/admin
vi listener.ora 把以下的配置内容拷入到新建的listener.ora中
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = oracle)
(ORACLE_HOME = /app/oracle/product/11.2.0/db_1)
(SID_NAME = oracle)
)
(SID_DESC =
(GLOBAL_DBNAME = PLSExtProc)
(ORACLE_HOME = /app/oracle/product/11.2.0/db_1)
(SID_NAME = PLSExtProc)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.20.101)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
)
6.2. tnsnames.ora
cd /app/oracle/product/11.2.0/db_1/network/admin
vi tnsnames.ora 把以下的配置内容拷入到新建的tnsnames.ora中更改host的ip主机和备用机Ip地址
primary =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.20.101)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = oracle)
)
)
standby =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.20.102)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = oracle)
)
)
7. 备库参数配置
准备参数文件,先配备库的参数，主库参数随后使用手动配置
以下的参数文件是从主库中用create pfile='/home/oracle/pfile20130627.ora' from spfile;
单机备库参数如下：参数配置见control_file.doc
cd /app/oracle/product/11.2.0/db_1/dbs
vi initcrds3db.ora
上面这个名字会根据你设置的环境变量而变化（~/.bash_profile），然后将以下文件拷入initcrds3db.ora中。下面的蓝色部分在辉县主机环境配置的时候要注意内存的大小变化。红色部分为新增内容。
*.audit_file_dest='/app/oracle/admin/crds3db/adump'
*.audit_sys_operations=TRUE
*.audit_trail='DB'
*.compatible='11.2.0.0.0'
*.control_files='/orasjrz/crds3db/oradata/controlfile/control01.ctl','/orasjrz/crds3db/oradata/controlfile/control02.ctl'
*.db_block_size=16384
*.db_domain=''
*.db_name='crds3db'
*.deferred_segment_creation=FALSE
*.diagnostic_dest='/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=crds3dbXDB)'
*.log_archive_dest_1='LOCATION=/orasjrz/crds3db/oraarch '
*.log_archive_format='%t_%s_%r.arc'
*.memory_target=1073741824
*.nls_language='SIMPLIFIED CHINESE'
*.nls_territory='CHINA'
*.open_cursors=300
*.processes=1000
*.remote_login_passwordfile='EXCLUSIVE'
*.sessions=1105
*.undo_tablespace='UNDOTBS1'
*.utl_file_dir='/home/oracle/'
*.log_archive_config='dg_config=(oracle,oracledg)'
*.standby_file_management='AUTO'
*.db_file_name_convert='/orasjrz/crds3db/oradata/datafile','/orasjrz/crds3db/oradata/datafile','/orasjrz/crds3db/oradata/tempfile','/orasjrz/crds3db/oradata/tempfile'
*.log_file_name_convert='/orasjrz/crds3db/oradata /logfile','/orasjrz/crds3db/oradata/logfile'
*.db_unique_name='oracledg'
*.log_archive_dest_1='location=/orasjrz/crds3db/oraarch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=oracledg'
*.LOG_ARCHIVE_DEST_2='SERVICE=primary LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=oracle'
*.LOG_ARCHIVE_DEST_STATE_1=ENABLE
*.LOG_ARCHIVE_DEST_STATE_2=ENABLE
*.FAL_SERVER='primary'
*.FAL_CLIENT='standby'
20130609dg-pfile
*.audit_file_dest='/app/oracle/admin/crds3db/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/orasjrz/crds3db/oradata/control01.ctl','/orasjrz/crds3db/oradata/control02.ctl'
*.db_block_size=16384
*.db_domain=''
*.db_name='crds3db'
*.deferred_segment_creation=FALSE
*.diagnostic_dest='/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=crds3dbXDB)'
*.log_archive_dest_1='LOCATION=/orasjrz/crds3db/oraarch/'
*.log_archive_format='%t_%s_%r.dbf'
*.memory_target=5368709120
*.nls_language='SIMPLIFIED CHINESE'
*.nls_territory='CHINA'
*.open_cursors=300
*.processes=1000
*.remote_login_passwordfile='EXCLUSIVE'
*.sessions=1105
*.undo_tablespace='UNDOTBS1'
*.utl_file_dir='/home/oracle/'
*.log_archive_config='dg_config=(crds3db,crds3dbdg)'
*.standby_file_management='AUTO'
*.db_file_name_convert='/orasjrz/crds3db/oradata','/orasjrz/crds3db/oradata'
*.log_file_name_convert='/orasjrz/crds3db/oradata','/orasjrz/crds3db/oradata'
*.db_unique_name='crds3dbdg'
*.log_archive_dest_1='location=/orasjrz/crds3db/oraarch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=crds3dbdg'
*.LOG_ARCHIVE_DEST_2='SERVICE=primary LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=crds3db'
*.LOG_ARCHIVE_DEST_STATE_1=ENABLE
*.LOG_ARCHIVE_DEST_STATE_2=ENABLE
*.FAL_SERVER='primary'
*.FAL_CLIENT='standby'
vi initcrds3db.ora
*.audit_file_dest='/app/orasu cle/admin/crds3db/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/orasjrz/crds3db/oradata/control01.ctl','/orasjrz/crds3db/oradata/control02.ctl'
*.db_block_size=16384
*.db_domain=''
*.db_name='crd3db'
*.deferred_segment_creation=FALSE
*.diagnostic_dest='/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=crds3dbXDB)'
*.log_archive_dest_1='LOCATION=/orasjrz/crds3db/oraarch/'
*.log_archive_format='%t_%s_%r.dbf'
*.memory_target=681574400
*.nls_language='SIMPLIFIED CHINESE'
*.nls_territory='CHINA'
*.open_cursors=300
*.processes=1000
*.remote_login_passwordfile='EXCLUSIVE'
*.sessions=1105
*.undo_tablespace='UNDOTBS1'
*.utl_file_dir='/home/oracle/'
.log_archive_config='dg_config=(crds3db,crds3dbdg)'
*.standby_file_management='AUTO'
*.db_file_name_convert='/orasjrz/crds3db/oradata','/orasjrz/crds3db/oradata'
*.log_file_name_convert='/orasjrz/crds3db/oradata','/orasjrz/crds3db/oradata'
*.db_unique_name='crds3dbdg'
*.log_archive_dest_1='location=/orasjrz/crds3db/oraarch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=crds3dbdg'
*.LOG_ARCHIVE_DEST_2='SERVICE=primary LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=crds3db'
*.LOG_ARCHIVE_DEST_STATE_1=ENABLE
*.LOG_ARCHIVE_DEST_STATE_2=ENABLE
*.FAL_SERVER='primary'
*.FAL_CLIENT='standby'
测试用的rac
8. 备*.audit_file_dest='/app/oracle/admin/crds3db/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/orasjrz/crds3db/oradata/controlfile/control01.ctl','/orasjrz/crds3db/oradata/controlfile/control02.ctl'
*.db_block_size=16384
*.db_domain=''
*.db_name='crds3db'
*.diagnostic_dest='/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=crds3dbXDB)'
*.log_archive_format='%t_%s_%r.arc'
*.memory_target= 966367641
*.nls_language='SIMPLIFIED CHINESE'
*.nls_territory='CHINA'
*.open_cursors=300
*.processes=1000
*.remote_login_passwordfile='exclusive'
*.sessions=1105
*.undo_tablespace='UNDOTBS1'
*.log_archive_config='dg_config=(crds3db,crds3dbdg)'
*.standby_file_management='AUTO'
*.db_file_name_convert='+ORADATA/crds3db/datafile','/orasjrz/crds3db/oradata/datafile','+ORADATA/crds3db/tempfile','/orasjrz/crds3db/oradata/tempfile'
*.log_file_name_convert='+ORADATA/crds3db/onlinelog','/orasjrz/crds3db/oradata/onlinelog1','+ORAARCH/crds3db/onlinelog','/orasjrz/crds3db/oradata/onlinelog2'
*.db_unique_name='crds3dbdg'
*.log_archive_dest_1='location=/orasjrz/crds3db/oraarch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=crds3dbdg'
*.LOG_ARCHIVE_DEST_2='SERVICE=primary LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=crds3db'
*.LOG_ARCHIVE_DEST_STATE_1=ENABLE
*.LOG_ARCHIVE_DEST_STATE_2=ENABLE
*.FAL_SERVER='primary'
*.FAL_CLIENT='standby'库新建数据库存放目录
exit
在备库的root用户下执行
mkdir -p /orasjrz/oracle/oradata
mkdir -p /orasjrz/oracle/oraarch
chown -R oracle:oinstall /orasjrz
chmod -R 775 /orasjrz
9. 在主库增加standby logfile
在主库standby执行下面sql语句加重做日志
创建原则和单实例一样，大小相等，但日志组数量要比主库在线日志多一组。如之前为3组1个单节点共3个，则现在要创建4组1个单节点共7个.
10. 主库rman备份
在主库hisa以oracle身份登录(2011-10-18 0:53开始)
主库已有备份则直接跳到下面11步拷备份而不需要临时进行备份
mkdir /home/oracle/rman
rman target /
RMAN>configure channel device type disk format '/home/oracle/rman/backup_%d_%T_%I_%u';
备份格式说明： %d specifies the name of the database
%I specifies the DBID.
%T specifies the year, month, and day in the Gregorian calendar in this format: YYYYMMDD.
RMAN>configure controlfile autobackup on;
RMAN>backup database;
按照下面提示输入
启动 backup 于 30-6月 -11
分配的通道: ORA_DISK_1
通道 ORA_DISK_1: SID=17 实例=crds3db1 设备类型=DISK
通道 ORA_DISK_1: 正在启动全部数据文件备份集
通道 ORA_DISK_1: 正在指定备份集内的数据文件
输入数据文件: 文件号=00002 名称=+ORADATA/crds3db/datafile/sysaux.260.755106369
输入数据文件: 文件号=00001 名称=+ORADATA/crds3db/datafile/system.259.755106357
输入数据文件: 文件号=00003 名称=+ORADATA/crds3db/datafile/undotbs1.261.755106377
输入数据文件: 文件号=00004 名称=+ORADATA/crds3db/datafile/undotbs2.263.755106393
输入数据文件: 文件号=00006 名称=+ORADATA/crds3db/datafile/rmanadm.dbf
输入数据文件: 文件号=00005 名称=+ORADATA/crds3db/datafile/users.264.755106395
通道 ORA_DISK_1: 正在启动段 1 于 30-6月 -11
通道 ORA_DISK_1: 已完成段 1 于 30-6月 -11
段句柄=/home/oracle/rman/backup_oracle_20110630_2587900074 标记=TAG20110630T104341 注释=NONE
通道 ORA_DISK_1: 备份集已完成, 经过时间:00:00:56
完成 backup 于 30-6月 -11
启动 Control File and SPFILE Autobackup 于 30-6月 -11
段 handle=/app/oracle/product/11.2.0/db_1/dbs/c-2587900074-20110630-01 comment=NONE
完成 Control File and SPFILE Autobackup 于 30-6月 -11
RMAN>exit
检查ramn备份,需将主库的备份文件拷到备库的相同目录下。
[oracle@hisa admin]$ cd /home/oracle/rman
[oracle@hisa rman]$ ls -al
total 1236540
1236540 -rw-r—– 1 oracle asmadmin 1264975872 Jun 30 10:44 backup_CRDS3DB_20110630_2587900074
11. 将主库rman传到备库
在备库以oracle身份先登录建rman目录
mkdir -p /home/oracle/rman/backup
回到主库hisa窗口上执行
[oracle@hisa rman]$ pwd
/home/oracle/rman
[oracle@hisa rman]$ scp backup_oracle_20130627_2 hisb:/home/oracle/rman
vi initoracle.ora
The authenticity of host 'hisadg (192.168.0.11)' can't be established.
RSA key fingerprint is eb:3b:c3:84:38:bf:8a:f6:d9:7c:d0:59:6e:51:61:26.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added 'hisadg,192.168.0.11' (RSA) to the list of known hosts.
oracle@hisadg's password: 输入备库oracle操作系统用户密码,成功后进行传输rman备份
backup_CRDS3DB_20110630_2587900074 100% 1206MB 10.8MB/s 01:52
[oracle@hisa rman]$
12. 在主库hisa上创建standby控制文件
在主库上进行几次归档。
sqlplus / as sysdba
s 执行3次
/符号执行上一次命令
回到hisa创建standby控制文件
alter database create standby controlfile as '/home/oracle/rman/standby.ctl';
exit
将文件拷到备库
scp /home/oracle/rman/standby.ctl hisb:/home/oracle/rman/standby.ctl vi initcrds3db.ora
相关参数及日志目录拷到备库
scp -r /app/oracle/admin/ hisb:/app/oracle/admin/
密码文件拷到备库，注意rac主库上的密码文件名上会多个数字 1 ，拷到备库时，要重命名将后面1去除
scp /app/oracle/product/11.2.0/db_1/dbs/orapwcrds3db hisb:/app/oracle/product/11.2.0/db_1/dbs/orapwcrds3db
在备库创建spfile并启动standby至nomount状态，下面的pfile在第7步已生成。
注：如果备库df -h看到tmpfs没调则要先调整tmpfs大小
exit
df -h 先查看原来大小
vi /etc/fstab 修改增加,size=6G
tmpfs /dev/shm tmpfs defaults,size=6G 0 0
mount -o remount,size=1G /dev/shm
df -h 看到已修改则ok.
su – oracle
sqlplus /nolog
conn / as sysdba
create spfile from pfile='/app/oracle/product/11.2.0/db_1/dbs/initcrds3db.ora';
startup nomount 如报ORA-00845: MEMORY_TARGET not supported on this system则检查内存只能设置物理内存的一半或更小，超过则会报这错误，否则需要先调整tmpfs大小。
host lsnrctl start
13. 利用rman创建standby数据库hisa
备注：不需要备份直接在主库执行这个命令：rman target sys/oracle@primary auxiliary sys/oracle@standby
duplicate target database for standby from active database;目录不一样执行这个
duplicate target database for standby from active database nofilenamecheck;目录一样执行这个
在主库:
过程如下：
[oracle@hisa dbs]$ rman target / auxiliary sys/oracle@standby
恢复管理器: Release 11.2.0.2.0 – Production on 星期四 6月 30 11:59:10 2011
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
已连接到目标数据库: CRDS3DB (DBID=2587900074)
已连接到辅助数据库: CRDS3DB (未装载)
RMAN> duplicate target database for standby nofilenamecheck; //当主库与备库路径相同是使用这个命令
RMAN> duplicate target database for standby;
启动 Duplicate Db 于 30-6月 -11
使用目标数据库控制文件替代恢复目录
分配的通道: ORA_AUX_DISK_1
通道 ORA_AUX_DISK_1: SID=771 设备类型=DISK
内存脚本的内容:
{
restore clone standby controlfile;
}
正在执行内存脚本
启动 restore 于 30-6月 -11
使用通道 ORA_AUX_DISK_1
通道 ORA_AUX_DISK_1: 正在还原控制文件
通道 ORA_AUX_DISK_1: 已复制控制文件副本
输入文件名=/home/oracle/rman/standby.ctl
输出文件名=/oradata/crds3db/controlfile/control01.ctl
输出文件名=/oradata/crds3db/controlfile/control02.ctl
完成 restore 于 30-6月 -11
内存脚本的内容:
{
sql clone 'alter database mount standby database';
}
正在执行内存脚本
sql 语句: alter database mount standby database
内存脚本的内容:
{
set newname for tempfile 1 to
"/oradata/crds3db/tempfile/temp.262.755106379";
switch clone tempfile all;
set newname for datafile 1 to
"/oradata/crds3db/datafile/system.259.755106357";
set newname for datafile 2 to
"/oradata/crds3db/datafile/sysaux.260.755106369";
set newname for datafile 3 to
"/oradata/crds3db/datafile/undotbs1.261.755106377";
set newname for datafile 4 to
"/oradata/crds3db/datafile/undotbs2.263.755106393";
set newname for datafile 5 to
"/oradata/crds3db/datafile/users.264.755106395";
set newname for datafile 6 to
"/oradata/crds3db/datafile/rmanadm.dbf";
restore
clone database
;
}
正在执行内存脚本
正在执行命令: SET NEWNAME
临时文件 1 在控制文件中已重命名为 /oradata/crds3db/tempfile/temp.262.755106379
正在执行命令: SET NEWNAME
正在执行命令: SET NEWNAME
正在执行命令: SET NEWNAME
正在执行命令: SET NEWNAME
正在执行命令: SET NEWNAME
正在执行命令: SET NEWNAME
启动 restore 于 30-6月 -11
使用通道 ORA_AUX_DISK_1
通道 ORA_AUX_DISK_1: 正在开始还原数据文件备份集
通道 ORA_AUX_DISK_1: 正在指定从备份集还原的数据文件
通道 ORA_AUX_DISK_1: 将数据文件 00001 还原到 /oradata/crds3db/datafile/system.259.755106357
通道 ORA_AUX_DISK_1: 将数据文件 00002 还原到 /oradata/crds3db/datafile/sysaux.260.755106369
通道 ORA_AUX_DISK_1: 将数据文件 00003 还原到 /oradata/crds3db/datafile/undotbs1.261.755106377
通道 ORA_AUX_DISK_1: 将数据文件 00004 还原到 /oradata/crds3db/datafile/undotbs2.263.755106393
通道 ORA_AUX_DISK_1: 将数据文件 00005 还原到 /oradata/crds3db/datafile/users.264.755106395
通道 ORA_AUX_DISK_1: 将数据文件 00006 还原到 /oradata/crds3db/datafile/rmanadm.dbf
通道 ORA_AUX_DISK_1: 正在读取备份片段 /home/oracle/rman/backup_CRDS3DB_20110630_2587900074
通道 ORA_AUX_DISK_1: 段句柄 = /home/oracle/rman/backup_CRDS3DB_20110630_2587900074 标记 = TAG20110630T104341
通道 ORA_AUX_DISK_1: 已还原备份片段 1
通道 ORA_AUX_DISK_1: 还原完成, 用时: 00:00:55
完成 restore 于 30-6月 -11
内存脚本的内容:
{
switch clone datafile all;
}
正在执行内存脚本
数据文件 1 已转换成数据文件副本
输入数据文件副本 RECID=1 STAMP=755179163 文件名=/oradata/crds3db/datafile/system.259.755106357
数据文件 2 已转换成数据文件副本
输入数据文件副本 RECID=2 STAMP=755179163 文件名=/oradata/crds3db/datafile/sysaux.260.755106369
数据文件 3 已转换成数据文件副本
输入数据文件副本 RECID=3 STAMP=755179163 文件名=/oradata/crds3db/datafile/undotbs1.261.755106377
数据文件 4 已转换成数据文件副本
输入数据文件副本 RECID=4 STAMP=755179163 文件名=/oradata/crds3db/datafile/undotbs2.263.755106393
数据文件 5 已转换成数据文件副本
输入数据文件副本 RECID=5 STAMP=755179163 文件名=/oradata/crds3db/datafile/users.264.755106395
数据文件 6 已转换成数据文件副本
输入数据文件副本 RECID=6 STAMP=755179163 文件名=/oradata/crds3db/datafile/rmanadm.dbf
完成 Duplicate Db 于 30-6月 -11
RMAN>exit
14. 在备库检查standby数据库
–sqlplus /nolog
–conn / as sysdba
SQL> select status from v$instance; 查看当前实例状态
STATUS
————
MOUNTED
SQL> select member from v$logfile; 查看当前重做日志状态
MEMBER
/oradata/crds3db/onlinelog1/group_1.257.755106353
/oradata/crds3db/onlinelog2/group_1.257.755106353
/oradata/crds3db/onlinelog1/group_2.258.755106355
/oradata/crds3db/onlinelog2/group_2.258.755106355
/oradata/crds3db/onlinelog1/group_3.265.755109189
/oradata/crds3db/onlinelog2/group_3.259.755109191
/oradata/crds3db/onlinelog1/group_4.266.755109191
/oradata/crds3db/onlinelog2/group_4.260.755109193
.
.
已选择7行。 rac则为32行
SQL> select name from v$datafile; 查看当前数据文件的状态
NAME
——————————————————————————–
/oradata/crds3db/datafile/system.259.755106357
/oradata/crds3db/datafile/sysaux.260.755106369
/oradata/crds3db/datafile/undotbs1.261.755106377
/oradata/crds3db/datafile/undotbs2.263.755106393
/oradata/crds3db/datafile/users.264.755106395
/oradata/crds3db/datafile/rmanadm.dbf
已选择6行。
SQL> select name from v$tempfile; 查看当前临时文件状态
NAME
——————————————————————————–
/oradata/crds3db/tempfile/temp.262.755106379
SQL> show parameter control 查看当前控制文件状态
NAME TYPE VALUE
———————————— ———– ——————————
control_file_record_keep_time integer 7
control_files string /oradata/crds3db/controlfile/c
ontrol01.ctl, /oradata/crds3db
/controlfile/control02.ctl
control_management_pack_access string DIAGNOSTIC+TUNING
15. 在主库启用参数
这三个参数预先配置，不需要再进行配置，只需要检查确认 修改则要重启数据库，所以得预先配置
show parameter db_unique;
show parameter convert;
SQL> alter system set db_unique_name='crd3db' scope=spfile;
System altered.
SQL>alter system set db_file_name_convert='/orasjrz/crds3db/oradata/datafile','/orasjrz/crds3db/oradata/datafile','/orasjrz/crds3db/oradata/tempfile','/orasjrz/crds3db/oradata/tempfile' scope=spfile;
System altered.
SQL>alter system set log_file_name_convert='/orasjrz/crds3db/oradata/logfile','/orasjrz/crds3db/oradata/logfile' scope=spfile;
System altered.
——————————————————–
SQL> alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(oracle,oracledg)' scope=both;
System altered.
SQL> alter system set log_archive_dest_1='LOCATION=/orasjrz/crds3db/oraarch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=oracle' scope=both;
*.log_archive_dest_1='LOCATION=/oracle/oraarch' /从主库搞过来的，如上面这条命令在主库应用后，主库起不来，就用这条命令也可。
System altered.
实时应用SQL> alter system set log_archive_dest_2='SERVICE=TSFYHISA_231 LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME= oracledg' scope=both;
SQL> alter system set LOG_ARCHIVE_DEST_STATE_1=ENABLE scope=both;
System altered.
SQL> alter system set LOG_ARCHIVE_DEST_STATE_2=ENABLE scope=both;
System altered.
SQL> alter system set FAL_SERVER='standby' scope=both;
System altered.
SQL> alter system set FAL_CLIENT='primary' scope=both;
System altered.
SQL> alter system set STANDBY_FILE_MANAGEMENT=AUTO scope=both;
System altered.
16. 主库启用新增及修改的参数
改完在hisa上oracle用户状态下执行，启用上面参数 如前面三个参数预先配置则不需要重启数据库，未配则需要
Shutdown immediate;
搭RAC+DG平台时,因备库没有及时搭建时,需要将下面参数启用,以防止主库一直报错.
SQL> alter system set LOG_ARCHIVE_DEST_STATE_2=defer scope=both;
SQL> alter system set LOG_ARCHIVE_DEST_STATE_2=enable scope=both;
17. 在备库开始同步
可不执行下面这二个，直接启用下面日志同步应用
–shutdown immediate //先关闭备库。需要在MOUNT状态下应用完日志再open read only
–startup mount
启动MRP(日志同步应用):以下的命令为实时应用归档
alter database recover managed standby database using current logfile disconnect;
查询同步状态
set linesize 200;
SELECT MESSAGE FROM V$DATAGUARD_STATUS;
select name,open_mode,PROTECTION_MODE,DATABASE_ROLE from v$database;
select thread#,min(sequence#) no_applied_min,max(sequence#) no_applied_max,count(1) no_applied_all from v$archived_log where applied='NO' and STANDBY_DEST='NO' and deleted<>'YES' group by thread# order by thread#;
select x.thread#,x.applied_max,to_char(y.first_time,'yyyy-mm-dd hh24:mi:ss') first_time,to_char(y.next_time,'mm-dd hh24:mi:ss') next_time,to_char(y.completion_time,'yyyy-mm-dd hh24:mi:ss') completion_time from (select thread#,max(sequence#) applied_max from v$archived_log where applied='YES' group by thread#) x,v$archived_log y where x.thread#=y.thread# and x.applied_max=y.sequence# order by x.thread#;
select thread#,sequence#,applied,name from v$archived_log where applied not in('YES','NO') order by thread#,sequence#;
查还有多少未同步
select thread#,min(sequence#) no_applied_min,max(sequence#) no_applied_max,count(1) no_applied_all from v$archived_log where applied='NO' and STANDBY_DEST='NO' and deleted<>'YES' group by thread# order by thread#;
提示没有查询的行就是同步完成了:no rows selected
另开一窗口跟踪alert…log，看归档应用情况.
tail -f /app/oracle/diag/rdbms/unq_crds3dg/crds3db/trace/alert_crds3db.log
到6.9 15点就同步完成
再用上面查询发现已全部同步，再用下面取消同步打开再应用
alter database recover managed standby database cancel;
alter database open read only;
alter database recover managed standby database using current logfile disconnect;
select name,open_mode,PROTECTION_MODE,DATABASE_ROLE from v$database;
下面这段跳过，因为用了上面这段已正常，直接到下面18步骤测试同步
//设置成自动应用归档的话需要使用以下sql
//alter database recover managed standby database disconnect from session;
查看已应用记录
select thread#,sequence#,applied from v$archived_log where applied='YES' order by thread#,sequence#;
查看未应用记录
select thread#,sequence#,applied from v$archived_log where applied='NO' order by thread#,sequence#;
应提示未选定行，如有记录则要检查，如没有则再执行下面
停止MRP,即停止主备数据库归档重做日志同步。
alter database recover managed standby database cancel;
更改数据库到只读状态。
alter database open read only;
查看主备库的数据库运行情况
启动MRP:
alter database recover managed standby database using current logfile disconnect;
主库运行情况：
SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;
SWITCHOVER_STATUS
——————–
TO STANDBY
SQL> select name,open_mode,PROTECTION_MODE,DATABASE_ROLE from v$database;
NAME OPEN_MODE PROTECTION_MODE DATABASE_ROLE
——— ——————– ——————– —————-
CRDS3DB READ WRITE MAXIMUM PERFORMANCE PRIMARY
备库运行情况
SQL> select name,open_mode,PROTECTION_MODE,DATABASE_ROLE from v$database;
NAME OPEN_MODE PROTECTION_MODE DATABASE_ROLE
——— ——————– ——————– —————-
CRDS3DB READ ONLY WITH APPLY MAXIMUM PERFORMANCE PHYSICAL STANDBY
18. 检查dg配置是否成功
需要数据库在open read only下进行
在主库执行生成一张表
create table test(a varchar2(2),b date);
在备库查询
select * from test
如有再回主库执行
insert into test values('1',sysdate);
commit;
切回备库查询
select * from test;
切回主库删除测试表
drop table test;
然后在主备执行select * from test;应报错则ok.
rac+dg 成功配置完成.
再配置自动启动dg脚本
19. 配置dg自动启动
1.远程root登录到主库查看启动信息
cat /etc/oratab将最后一行启动信息 crds3db:/app/oracle/product/11.2.0/db_1:Y 复制下来
2.远程root登录到备库增加启动信息
vi /etc/oratab在最后一行粘贴主库复制的信息
3.将下面脚本修改核对后拷入备库
一些信息可通过主库查看获取，因为主库一般设置了自动启动 cat /etc/oratab
vi /etc/init.d/oracledgnew 先按i空三行再移到中间处粘贴下面内容
#!/bin/bash
#chkconfig: 2345 98 01
#description: Oracle database dataguard server
#Starts the oracle database dataguard server
#
# processname: oracle
# Source function library.
. /etc/init.d/functions
ORACLE_SID=crd3db; export ORACLE_SID
ORACLE_BASE=/app/oracle; export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1; export ORACLE_HOME
NLS_LANG=AMERICAN_AMERICA.ZHS16GBK
ORA_NLS33=$ORACLE_HOME/ocommon/nls/admin/data
export NLS_LANG ORA_NLS33
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
export ORACLE_HOME_LISTNER=$ORACLE_HOME
PATH=$HOME/bin:$ORACLE_HOME/bin:/app/bin:/bin:/usr/bin:usr/ccs/bin:/usr/ucb:/etc:$PATH
export PATH
test -x $ORACLE_HOME/bin/oracle || exit 0
RETVAL=0
start() {
# Check if oracle is already running
if [ ! -f /var/lock/subsys/oracle ]; then
prog="listener"
echo -n $"Starting $prog: "
su – oracle -c "lsnrctl start" >> /var/log/oracle.log
RETVAL=$?
[ $RETVAL -eq 0 ] && success || failed
echo
prog="oracle dataguard"
echo -n $"Starting $prog: "
su – oracle -c "sqlplus /nolog" << EOF >> /var/log/oracle.log
connect / as sysdba
startup mount
! sleep 60
select name,open_mode,PROTECTION_MODE,DATABASE_ROLE from v$database;
select thread#,min(sequence#) no_applied_min,max(sequence#) no_applied_max,count(1) no_applied_all from v$archived_log where applied='NO' and STANDBY_DEST='NO' and deleted<>'YES' group by thread# order by thread#;
declare wyyn number;
begin
select count(1) into wyyn from v$archived_log where applied='NO' and STANDBY_DEST='NO' and deleted<>'YES';
if wyyn=0 then
EXECUTE IMMEDIATE 'alter database open';
end if;
EXECUTE IMMEDIATE 'alter database recover managed standby database using current logfile disconnect';
end;
/
select name,open_mode,PROTECTION_MODE,DATABASE_ROLE from v$database;
select thread#,min(sequence#) no_applied_min,max(sequence#) no_applied_max,count(1) no_applied_all from v$archived_log where applied='NO' and STANDBY_DEST='NO' and deleted<>'YES' group by thread# order by thread#;
exit
EOF
RETVAL=$?
[ $RETVAL -eq 0 ] && success || failed
echo
[ $RETVAL -eq 0 ] && touch /var/lock/subsys/oracle || RETVAL=1
fi
return $RETVAL
}
stop() {
prog="listener"
echo -n $"Stopping $prog: "
su – oracle -c "lsnrctl stop" >> /var/log/oracle.log
RETVAL=$?
[ $RETVAL -eq 0 ] && success || failed
echo
prog="oracle dataguard"
echo -n $"Stopping $prog: "
su – oracle -c "sqlplus /nolog" << EOF >> /var/log/oracle.log
connect / as sysdba
recover managed standby database cancel;
shutdown immediate
exit
EOF
RETVAL=$?
[ $RETVAL -eq 0 ] && success || failed
echo
[ $RETVAL -eq 0 ] && rm -f /var/lock/subsys/oracle
return $RETVAL
}
restart() {
stop
start
}
reload() {
restart
}
status_oracle() {
su – oracle -c "lsnrctl status"
su – oracle -c "sqlplus /nolog" << EOF
connect / as sysdba
select name,open_mode,PROTECTION_MODE,DATABASE_ROLE from v$database;
select process,status from v$managed_standby;
exit
EOF
}
case "$1" in
start)
start
;;
stop)
stop
;;
reload|restart)
restart
;;
status)
status_oracle
;;
*)
echo $"Usage: $0 {start|stop|restart|status}"
exit 1
esac
exit $?
exit $RETVAL
4.授权加到服务并测试
# cd /etc/init.d
# chmod u+x oracledgnew
# chkconfig –add oracledgnew
# chkconfig –list oracledgnew
–# chkconfig –del oracledgnew 删除服务
测试库则可以先开一窗监控tail -f /var/log/oracle.log文件，另一窗执行service oracledgnew stop，然后主库做几个日志切换，完成后再回上面第二窗执行service oracledgnew start再看上面日志。
测试
# service oracledgnew start #启动服务
# service oracledgnew stop #停止服务
# service oracledgnew restart #重启服务
# service oracledgnew status #检查状态 如已启动，可用这进行检查 而不需要启动，如要测试可直接reboot重启试下能否自动启动。
操作系统reboot后大约三五分钟(因为要启动数据库)再输入service oracledgnew status
或查看日志cat /var/log/oracle.log 查否正常
如正常进入检查下
su – oracle
conn / as sysdba
各节点汇总统计还有多少未应用 select thread#,count(1) from v$archived_log where applied='NO' group by thread#;
查看未应用记录select thread#,sequence#,applied ,substr(name,1,24) name from v$archived_log where applied='NO' order by thread#,sequence#;
看是不是提示无记录
select thread#,count(1) from v$archived_log where applied='YES' group by thread#;
select thread#,max(sequence#) from v$archived_log where applied='YES' group by thread#;
文章来源：http://www.51itstudy.com/9945.html