用RMAN创建DataGruad配置方案

用RMAN创建DataGruad配置方案
2009-12-10 11:29
一 : 环境配置
 
名称 主数据库 逻辑数据库 物理数据库   
操作系统 CentOS 4.7 CentOS 4.7 CentOS 4.7   
机器名称 serverdb1 serverdb2 fastrsie06   
IP地址 172.16.99.195 172.16.99.196 192.168.1.165   
数据库版本 10.2.0.1 10.2.0.1 10.2.0.1   
Oracle_Sid snowdb snowdb snowdb   
Global_Name snowdb snowdb snowdb   
Net Service Name test01 test02 test03   
Db_Unique_Name UQN_NODE1 UQN_NODE2 UQN_NODE3 

1.查看linux版本号
[oracle@serverdb1 /]$ lsb_release -a
LSB Version:    core-3.0-ia32:core-3.0-noarch:graphics-3.0-ia32:graphics-3.0-noarch
Distributor ID: CentOS
Description:    CentOS release 4.7 (Final)
Release:        4.7
Codename:       Final
2.查看oracle版本号
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0      Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
3.配置oracle帐户的环境变量
编辑 vi .bash_profile
ORACLE_BASE=/u01
ORACLE_HOME=$ORACLE_BASE/oracle
ORACLE_SID=snowdb
PATH=$ORACLE_HOME/bin:$PATH
NLS_LANG=AMERICAN_AMERICA.ZHS16GBK
LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib:$LD_LIBRARY_PATH
export ORACLE_BASE ORACLE_HOME ORACLE_SID PATH NLS_LANG LD_LIBRARY_PATH
4.配置hosts、IP、listener.ora、tnsname.ora. 
 service network  restart
 hostname serverdb2
 vi /etc/hosts
# Do not remove the following line, or various programs
# that require network functionality will fail.
127.0.0.1       localhost.localdomain   localhost       serverdb1
172.16.99.195   serverdb1.fastrise.com  serverdb1
172.16.99.196   serverdb2.fastrise.com  serverdb2
192.168.1.165   fastrise06.fastrise.com fastrise06
more /etc/sysconfig/network
NETWORKING=yes
HOSTNAME=serverdb1
 vi /etc/sysconfig/network-scripts/ifcfg-eth0
ONBOOT=yes   //在系统启动时,自动引导
USERCTL=no
IPV6INIT=no
PEERDNS=yes
GATEWAY=172.16.99.254
TYPE=Ethernet
DEVICE=eth0
HWADDR=00:0c:29:31:2d:fe
BOOTPROTO=none
NETMASK=255.255.255.0
IPADDR=172.16.99.195
 vi /u01/oracle/network/admin/listener.ora
# listener.ora Network Configuration File: /u01/oracle/network/admin/listener.ora
# Generated by Oracle configuration tools.

SUBSCRIBE_FOR_NODE_DOWN_EVENT_Listener=OFF
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /u01/oracle)
      (PROGRAM = extproc)
    )
   (SID_DESC =
      (SID_NAME =snowdb)
      (ORACLE_HOME = /u01/oracle)
    )
  )
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST =serverdb1)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
    )
  )

 vi /u01/oracle/network/admin/tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/oracle/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

TEST01= 
 (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = serverdb1)(PORT = 1521))
    (CONNECT_DATA =
      (SERVICE_NAME = snowdb)
    )
  )
TEST02=
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = serverdb2)(PORT = 1521))
    (CONNECT_DATA =
      (SERVICE_NAME = snowdb)
    )
  )
TEST03=
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = fastrise06)(PORT = 1521))
    (CONNECT_DATA =
      (SERVICE_NAME = snowdb)
    )
  )

EXTPROC_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
    )
    (CONNECT_DATA =
      (SID = PLSExtProc)
      (PRESENTATION = RO)
    )
  )
 5.创建主数据库(dbca)
ORA_SID =snowdb
其他两台备库只要装到只安装软件就可以。
在primary database上设置强制归档项archive_lag_target
二：创建并配置主、备库
    配置步骤首先，装好主库serverdb1和备库serverdb2上的oracle，建sid同为snowdb的库，所有设置完全相同。然后停止shutdown两台上的oracle，将主库serverdb1上的所有数据文件、控制文件、初始化init***.ora文件、redo文件和password文件都copy到备库serverdb2机器的相应位置（还可以利用热备，RMAN等来建立初始备库），这样，我们就拥有了两台完全一样的ORACLE SERVER 。
1. 设置primary database为archivelog模式
SQL>shutdown immediate;
SQL>startup mount;
SQL>alter database archivelog;
SQL>alter system set log_archive_format='%t_%s_%r.arc' scope=spfile;
SQL>alter system set log_archive_dest_1='LOCATION=/u01/oradata/arch1' scope=spfile;
SQL>alter database open;
2.在primary database上设置force logging
SQL>alter database force logging;
3.修改primary database初始化参数
SERVICE_NAMES=SNOWDB
DB_UNIQUE_NAME='UQN_NODE1'
LOG_ARCHIVE_CONFIG='DG_CONFIG=(UQN_NODE2,UQN_NODE1)'
LOG_ARCHIVE_DEST_2= 'SERVICE=TEST02 ASYNC
          VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
          DB_UNIQUE_NAME=UQN_NODE2'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
LOG_ARCHIVE_MAX_PROCESSES=4 
FAL_SERVER=TEST02
FAL_CLIENT=TEST01
STANDBY_FILE_MANAGEMENT=AUTO
通过 VALID_FOR 属性指定传输及接收对象
valid_for 配合其 redo_log_type,database_role 属性，其理解为：为指定角色设置日志文件的归档路径，主要目的是为了辅助一旦发生角色切换操作后数据库的正常运转。
redo_log_type 可设置为： online_logfile ，standby_logfile ，all_logfiles
database_role 可设置为： primary_role ，standby_role ， all_roles
online_logfile: 表示归档联机重做日志
standby_logfile:表示归档备用数据库的重做日志/接受的重做日志
all_logfiles: online_logfile && standby_logfile
primary_role: 仅当数据库角色为主库时候归档生效
standby_role: 仅当数据库角色为备库时候归档生效
all_role: 任意角色归档均生效
LOG_ARCHIVE_MAX_PROCESSES(归档数范围为1到30)
更改spfile如下：
alter system set DB_UNIQUE_NAME='UQN_NODE1' scope=spfile;
alter system set log_file_name_convert='/u01/oradata/arch1','/u01/oradata/arch1' scope=spfile;

alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(UQN_NODE2,UQN_NODE1)' scope=spfile;
alter system set LOG_ARCHIVE_DEST_2= 'SERVICE=TEST02 ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=UQN_NODE2' scope=spfile;
alter system set LOG_ARCHIVE_DEST_STATE_1=ENABLE scope=spfile;
alter system set LOG_ARCHIVE_DEST_STATE_2=ENABLE scope=spfile;
alter system set LOG_ARCHIVE_MAX_PROCESSES=4 scope=spfile;
alter system set FAL_SERVER=TEST02 scope=spfile;
alter system set FAL_CLIENT=TEST01 scope=spfile;
alter system set STANDBY_FILE_MANAGEMENT=AUTO scope=spfile;
4.在primary database上创建RMAN备份文件，生成后要传送到备库上。
1）在primary database上配置好监听，到时能处理到备库的服务就可以。
2）在primary database上备份，并在备库上配置相关目录和初始文件，并将备份文件拷贝到备库上，保证备库能启动到nomount状态。
SQL> shutdown immediate;
SQL> startup
SQL> create pfile from spfile;

[oracle@serverdb1 dbs]$ rman target sys/lijunyu@test01
Recovery Manager: Release 10.2.0.1.0 - Production on Sat Oct 31 09:21:22 2009
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
connected to target database: SNOWDB (DBID=455890448)
RMAN>
备份脚本：
run{
allocate channel c1 type disk;
backup as compressed backupset
incremental level=0
format='/u01/databak/inc0_%d_%U'
tag='inc0'
database;
sql"alter system switch logfile";
backup as compressed backupset
format='/u01/databak/arch_%d_%U'
tag='arch'
archivelog all;                         (delete input)
backup as compressed backupset
format='/u01/databak/snowdb.ctl'
tag='snowdb'
current controlfile for standby reuse;
release channel c1;
}
在备库上执行为
更改listener.ora ，initsnowdb.ora 、新建与主库相同的目录和启动备库的数据库到NOMOUNT状态
例：listener.ora文件
# listener.ora Network Configuration File: /u01/oracle/network/admin/listener.ora
# Generated by Oracle configuration tools.

SUBSCRIBE_FOR_NODE_DOWN_EVENT_Listener=OFF
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /u01/oracle)
      (PROGRAM = extproc)
    )
   (SID_DESC =
      (SID_NAME =snowdb)
      (ORACLE_HOME = /u01/oracle)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = serverdb2)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
    )
  )
例：initsnowdb.ora
SERVICE_NAMES=SNOWDB
DB_UNIQUE_NAME='UQN_NODE2'
LOG_ARCHIVE_CONFIG='DG_CONFIG=(UQN_NODE1,UQN_NODE2)'
LOG_ARCHIVE_DEST_2= 'SERVICE=TEST01 ASYNC
          VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
          DB_UNIQUE_NAME=UQN_NODE1'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
LOG_ARCHIVE_MAX_PROCESSES=4 
FAL_SERVER=TEST01
FAL_CLIENT=TEST02
STANDBY_FILE_MANAGEMENT=AUTO
例：创建目录结构
[oracle@serverdb2 ~]$ mkdir /u01/databak
[oracle@serverdb2 ~]$ mkdir /u01/oradata
[oracle@serverdb2 ~]$ mkdir /u01/oradata/snowdb
[oracle@serverdb2 ~]$ mkdir /u01/oradata/arch1
[oracle@serverdb2 ~]$ mkdir /u01/admin
[oracle@serverdb2 ~]$ mkdir /u01/admin/snowdb
[oracle@serverdb2 ~]$ mkdir /u01/admin/snowdb/adump
[oracle@serverdb2 ~]$ mkdir /u01/admin/snowdb/bdump
[oracle@serverdb2 ~]$ mkdir /u01/admin/snowdb/udump
[oracle@serverdb2 ~]$ mkdir /u01/admin/snowdb/dpdump
[oracle@serverdb2 ~]$ mkdir /u01/admin/snowdb/pfile
改开备库的初始化文件并启动数据库到NOMOUNT状态

[oracle@serverdb2 ~]$ lsnrctl start
[oracle@serverdb2 ~]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Sat Oct 31 09:36:40 2009
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> startup nomount
传送主库备份文件到备库的相应目录如：/u01/databak/ 和刚生成的备份文件（宁多勿少，否则因数据库不一致问题）。
（方法很多种，如scp、ftp、FileZilla、FlashFXP等等）
3) 在primary database上使用命令恢复后并传送redo.log过去。
前提为目录相同，且启动到nomount状态，完成后直接在备库上后台运用命令。
恢复命令：
[oracle@serverdb1 u01]$ rman target sys/lijunyu@test01 auxiliary sys/lijunyu@test02

Recovery Manager: Release 10.2.0.1.0 - Production on Sat Nov 28 10:22:13 2009
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
connected to target database: SNOWDB (DBID=458314910)
connected to auxiliary database: SNOWDB (not mounted)

run{
duplicate target database for standby nofilenamecheck;
}

[oracle@serverdb1 snowdb]$ scp *.log oracle@serverdb2:/u01/oradata/snowdb/
oracle@serverdb2's password:
redo01.log                                                              100%   50MB   3.3MB/s   00:15   
redo02.log                                                              100%   50MB   4.6MB/s   00:11   
redo03.log                                                              100%   50MB   4.6MB/s   00:11   
5.启动standby database到recover manage模式(从后台自动应用归档)
在备库上执行：
SQL> alter database recover managed standby database disconnect from session;
Database altered.
6.取消standby database到recover manage模式
SQL>alter database recover managed standby database cancel;
Database altered.
7. 配置另一台物理备库，方法如上第4点，所更改的配置文件如下
 serverdb1主库上执行：
alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(UQN_NODE2,UQN_NODE3,UQN_NODE1)' scope=both;
alter system set LOG_ARCHIVE_DEST_3= 'SERVICE=TEST03 ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=UQN_NODE3' scope=both;
alter system set LOG_ARCHIVE_DEST_STATE_3=ENABLE scope=both;
alter system set FAL_SERVER=TEST02,TEST03 scope=both;
将serverdb2库上的初始化文件pfile和密码文件orapwd传送上到fastrsie06库上并更改相应的标识。
这样又一台物理备库就生成了，用serverdb1 为主，对应两台物理备库分别为serverdb2 和fastrise06

三：测试主备库方案
1.查看归档情况及其传输方法
⑴查看全部归档数：
SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u01/oradata/arch1
Oldest online log sequence     48
Next log sequence to archive   50
Current log sequence           50
⑵查看正在应用归档
SQL> SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
 SEQUENCE#    APP
----------    ---
  8           NO
  8           YES
⑶查看日志无法传送
SQL>select dest_name,status,error from v$archive_dest;
查看相应的归档路径的状态是否valid，否则根据error信息进行处理
⑷查看归档是否有错误同步
 SELECT DESTINATION, STATUS, ARCHIVED_THREAD#, ARCHIVED_SEQ# FROM V$ARCHIVE_DEST_STATUS WHERE STATUS <> 'DEFERRED' AND STATUS <> 'INACTIVE';
   DESTINATION                                          STATUS    ARCHIVED_THREAD# ARCHIVED_SEQ#
   ----------------------------------                   --------- ---------------- -------------
    /u01/oradata/arch1                                  VALID                    1            49
    serverdb02                                          VALID                    1            16
STATUS备注：
VALID –初始化和可用
INACTIVE –无效的
DEFERRED -手动禁用用户
ERROR -错误在打开或复制
DISABLED -遇到错误后关闭
BAD PARAM -参数有误
ALTERNATE -目标是在一个备用状态
FULL -为目标超出配额大小
⑸查看主备库在哪台上的命令
SQL> select name,database_role from v$database;
 NAME      DATABASE_ROLE
--------- ----------------
 ORA10G    PRIMARY

四、使备库进只读状态查询
SQL> alter database recover managed standby database cancel;
Database altered.
SQL> alter database open read only;
Database altered.
SQL> shutdown immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area  452984832 bytes
Fixed Size                  1979392 bytes
Variable Size             130026496 bytes
Database Buffers          318767104 bytes
Redo Buffers                2211840 bytes
Database mounted.

五、主备库切换
1)正常切换
步骤一、在主库运行：
SQL> alter database commit to switchover to physical standby with session shutdown;
//在这个SESSION关闭之后，再开启后为转化为物理备库
SQL> shutdown immediate;
//关闭数据库
步骤二、在备库运行：
SQL> alter database commit to switchover to physical primary with session shutdown;
SQL> shutdown immediate;
SQL> startup
在主库上执行
SQL> startup nomount;
//启动到初始化文件上
SQL> alter database mount standby database;
//启动控制文件到物理备库上
SQL> recover managed standby database disconnect from session;
//在后台运行自动应用归档
2)非正常切换（即主服务器宕机的情况，启用备机）
alter database recover managed standby database finish;
alter database commit to switchover to primary;
shutdown immediate;
startup;
六、转换为逻辑备库 (以物理备库为前提条件!)
(一)、在主库上
1、先查看两机器是不是相通,归档有没传输正常
select dest_name,status,error from v$archive_dest;
查看相应的归档路径的状态是否valid，否则根据error信息进行处理
2、生成并创建LogMiner字典信息
创建一个logminer所用的表空间：
创建逻辑standby对象的默认表空间是system表空间，为了减少对系统表空间的影响，我们创建一个专门的表空间用于逻辑standby：
create tablespace logminer datafile '/u01/oradata/snowdb/logminer01.dbf' size 100m;
将分析归档的表空间从system表空间转到logminer表空间上。
execute dbms_logmnr_d.set_tablespace('logminer');
执行dbms_logstdby.build生成逻辑standby所需logminer字典信息：建立logminer字典(logical standby database在分解redo log为sql语句时需要logminer工具,而这个工具在使用之前需要建立logminer字典):
execute dbms_logstdby.build;
3、查看逻辑字典应用情况
SQL> select supplemental_log_data_pk, supplemental_log_data_ui from v$database;
SUP SUP
--- ---
YES YES
supplemental_log_data_pk对于主键的所有表，表明是否主键的所有列都将重做日志每当执行更新（是）或放置不（no）的
upplemental_log_data_ui对于具有独特的键的所有表，表明是否所有其他列属于独特的关键是将重做日志如果没有独特的键列被修改（是）或不放置（no）的
(二)、在备库上
1、选项:用spfile 和 standby logfile
create spfile from pfile;
//目的使数据库能自身进行写入。
shutdown immediate;
startup mount;
alter database recover managed standby database disconnect from session;
//应用物理备库进程
2、把物理备库上的恢复进程取消，备库上要mount状态下去执行
alter database recover managed standby database cancel;
//将之前正在应用的物理备库进程取消
3、创建逻辑备库redolog
alter database add standby logfile group 4 ('/u01/oradata/snowdb/standbyrd01.log') size 50M;
alter database add standby logfile group 5 ('/u01/oradata/snowdb/standbyrd02.log') size 50M;
alter database add standby logfile group 6 ('/u01/oradata/snowdb/standbyrd03.log') size 50M;
4、查看归档数
select member from v$logfile;
5、转换成新的逻辑备库
先关闭一次备库
shutdown immediate;
startup mount;
等待数分钟，目的是刚关闭数据库主库还未认到此数据库。
在主库上去查看以下命令：
select dest_name,status,error from v$archive_dest where error is not null;
备库上执行：
alter database recover to logical standby snowdb;
经多次测试，在主库上同时去再执行，会加快速度问题
execute dbms_logstdby.build;
alter system switch logfile;
再关闭一次备库
shutdown immediate;
打开到应用控制文件状态
startup mount;
6、第一次打开新的实例的话，要重置归档为0
alter database open resetlogs;
应用逻辑备库
alter database start logical standby apply immediate;
7、如果想停止/重启逻辑standby的SQL同步写入，可以通过下列命令：
SQL>alter database stop logical standby apply immediate;
SQL>alter database start logical standby apply immediate;
8、查看两库归档情况
主库上查询:
SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u01/oradata/arch1
Oldest online log sequence     32
Next log sequence to archive   34
Current log sequence           34
逻辑备库上查询:
SQL> select sequence#,first_change#,next_change#,timestamp,applied from dba_logstdby_log where applied<>'YES' order by sequence#;
 SEQUENCE# FIRST_CHANGE# NEXT_CHANGE# TIMESTAMP    APPLIED
---------- ------------- ------------ ------------ --------
        33        506391       509343 31-OCT-09    CURRENT
说明两库是正常的.
alter system set archive_lag_target=600 scope=both;

更改初始化文件，指定路径给逻辑备库所用
alter database stop logical standby apply;
alter system set LOG_ARCHIVE_DEST_3='LOCATION=/u01/oradata/arch2/  VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE)  DB_UNIQUE_NAME=UQN_NODE2' scope=both;
alter system set LOG_ARCHIVE_DEST_STATE_3='ENABLE' scope=both;
alter database start logical standby apply;

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

转载于:http://blog.itpub.net/15242702/viewspace-625255/