ITPub博客

首页 > 数据库 > Oracle > database+dataguard部署

database+dataguard部署

原创 Oracle 作者:muxinqing 时间:2014-02-08 08:27:27 0 删除 编辑

DB+DG

一、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#;
尊重版权 转载请与链接
技术群:132304250

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29065182/viewspace-1077851/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2014-01-14

  • 博文量
    143
  • 访问量
    1200539