注册 登录  
 加关注
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

PostgreSQL 中文网

 
 
 

日志

 
 

DataGuard之一:Oracle10g dg搭建  

2010-12-16 13:42:28|  分类: Oracle |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |

  
          最近准备给一个生产项目上oracle10g Dataguard, 两台上机上均装好oracle 10204软件,
并在一台上已经建库。
OS: Red Hat Enterprise 5
DB: Oracle 10204

        这篇日志不讲述 DataGuard 的原理,只是oracle 10g DataGuard 搭建的详细过程。

1 将主库设为LOGGING模式,并生成初始化参数文件和,standby 控制文件
SQL> alter database force logging

--将主库设为归档模式
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MOUNT;
SQL> ALTER DATABASE ARCHIVELOG;
SQL> ALTER DATABASE OPEN;

--创建初始化参数文件
SQL> create pfile='/oradata/initbc_ocp5.ora' from spfile;

File created.

--创建standby控制文件,在主库上执行
SQL> ALTER DATABASE CREATE STANDBY CONTROLFILE  AS '/home/oracle/script/control01.ctl';

Database altered.

2 关闭主库,复制数据文件,redo和 standby 控制文件到备机的相应目录
tar cvf skycard.tar /oradata/skycard
-- ftp控制文件(standby controfile),数据文件(skycard.tar)到从库相应目录

生成三份控制文件
cp control01.ctl control02.ctl
cp control01.ctl control03.ctl

3 修改主库的初始化参数文件,增加以下内容
--在主库$ORACLE_HOME/dbs/initskycard.ora,增加以下内容

*.DB_UNIQUE_NAME='PRIMARY_1'
*.log_archive_config='DG_CONFIG=(PRIMARY_1,STANDBY_1)'
*.log_archive_dest_1='LOCATION=/opt/oracle/archive/skycard/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=PRIMARY_1'
*.log_archive_dest_2='SERVICE=STANDBY_1 LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=STANDBY_1'
*.LOG_ARCHIVE_DEST_STATE_1=ENABLE
*.LOG_ARCHIVE_DEST_STATE_2=ENABLE
*.FAL_SERVER=STANDBY_1
*.FAL_CLIENT=PRIMARY_1
*.standby_file_management='AUTO'
*.LOG_ARCHIVE_MAX_PROCESSES=3

4 修改从库$ORACLE_HOME/dbs/initskycard.ora参数,增加以下内容

*.DB_UNIQUE_NAME='STANDBY_1'
*.log_archive_config='DG_CONFIG=(PRIMARY_1,STANDBY_1)'
*.log_archive_dest_1='LOCATION=/opt/oracle/archive/skycard/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=STANDBY_1'
*.log_archive_dest_2='SERVICE=PRIMARY_1 LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=PRIMARY_1'
*.LOG_ARCHIVE_DEST_STATE_1=ENABLE
*.LOG_ARCHIVE_DEST_STATE_2=ENABLE
*.FAL_SERVER=PRIMARY_1
*.FAL_CLIENT=STANDBY_1
*.standby_file_management='AUTO'
*.LOG_ARCHIVE_MAX_PROCESSES=3

5 配置主库和备库上的 tnsnames.ora ,并测试连通性
PRIMARY_1=
  (DESCRIPTION=
    (ADDRESS=
      (PROTOCOL=TCP)
      (HOST=134.109.171.72)
      (PORT=1521)
    )
    (CONNECT_DATA=
      (SERVICE_NAME=skycard)
    )
  )

STANDBY_1=
  (DESCRIPTION=
    (ADDRESS=
      (PROTOCOL=TCP)
      (HOST=134.109.171.73)
      (PORT=1521)
    )
    (CONNECT_DATA=
      (SERVICE_NAME=skycard)
    )
  )
 
6 主库的 listen.ora
# listener.ora Network Configuration File: /opt/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 = /opt/oracle/product/10.2.0/db_1)
      #(PROGRAM = extproc)

    )
 (SID_DESC =
      (GLOBAL_DBNAME = skycard)
      (ORACLE_HOME = /opt/oracle/product/10.2.0/db_1)
      (SID_NAME = skycard)
     )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = db-134-109-171-72.test-db.com.hz.sandun )(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
    )
  )
 
7 从库的 listen.ora
# listener.ora Network Configuration File: /opt/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 = /opt/oracle/product/10.2.0/db_1)
      #(PROGRAM = extproc)

    )
 (SID_DESC =
      (GLOBAL_DBNAME = skycard)
      (ORACLE_HOME = /opt/oracle/product/10.2.0/db_1)
      (SID_NAME = skycard)
     )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = db-134-109-171-73.test-db.com.hz.sandun )(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
    )
  )   


8 在从库上执行,创建密码文件
$ORACLE_HOME/dbs/orapwd file=orapwskycard password=12dfdf entries=10

9 创建相应的目录
cd $ORACLE_BASE/admin
mkdir skycard
mkdir adump  bdump  cdump  dpdump  pfile  scripts  udump

10 启动从库到mount状态
SQL> startup nomount pfile='/opt/oracle/product/10.2.0/db_1/dbs/initskycard.ora';
ORACLE instance started.

Total System Global Area 3355443200 bytes
Fixed Size                  2087640 bytes
Variable Size             671089960 bytes
Database Buffers         2667577344 bytes
Redo Buffers        

SQL> ALTER DATABASE MOUNT STANDBY DATABASE;

--初始化log apply服务
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

11 打开主库并测试archive log 是否传送

--主库上执行
SQL> startup;
ORACLE instance started.

Total System Global Area  251658240 bytes
Fixed Size                  1218820 bytes
Variable Size              96470780 bytes
Database Buffers          150994944 bytes
Redo Buffers                2973696 bytes
Database mounted.
Database opened.

--主库上执行
SQL> ALTER SYSTEM SWITCH LOGFILE;

Database altered.

--到备库主机上查看归档文件是否传来
cd /opt/oracle/archive/skycard/
oracle@db-134-109-171-73-> ll
-rw-r----- 1 oracle oinstall 196K Dec 16 09:42 1_20_737391288.dbf
-rw-r----- 1 oracle oinstall 120K Dec 16 09:45 1_21_737391288.dbf

说明日志文件已经传送

12 切换测试(停主库,启备库)
--主库上操作  
alter database commit to switchover to physical standby;

说明:如果此时有连接着的会话,这个命令会报错,需要加个"with session shutdown" 属性
alter database commit to switchover to physical standby with session shutdown;


shutdown immediate;

startup nomount;
alter database mount standby database;

SQL>  select name,open_mode,PROTECTION_MODE,DATABASE_ROLE from v$database;

NAME      OPEN_MODE  PROTECTION_MODE      DATABASE_ROLE
--------- ---------- -------------------- ----------------
SKYCARD   MOUNTED    MAXIMUM PERFORMANCE  PHYSICAL STANDBY

SQL>  ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

Database altered.

--在备库上操作
SQL> select name,open_mode,PROTECTION_MODE,DATABASE_ROLE from v$database;

NAME      OPEN_MODE  PROTECTION_MODE      DATABASE_ROLE
--------- ---------- -------------------- ----------------
SKYCARD   READ ONLY  MAXIMUM PERFORMANCE  PHYSICAL STANDBY

SQL> alter database commit to switchover to primary;

Database altered.

SQL>  select name,open_mode,PROTECTION_MODE,DATABASE_ROLE from v$database;

NAME      OPEN_MODE  PROTECTION_MODE      DATABASE_ROLE
--------- ---------- -------------------- ----------------
SKYCARD   MOUNTED    MAXIMUM PERFORMANCE  PRIMARY

SQL> shutdown immediate;

SQL> startup nomount pfile='/opt/oracle/product/10.2.0/db_1/dbs/initskycard.ora';
ORACLE instance started.

Total System Global Area 3355443200 bytes
Fixed Size                  2087640 bytes
Variable Size             671089960 bytes
Database Buffers         2667577344 bytes
Redo Buffers               14688256 bytes

SQL> alter database mount;

Database altered.

SQL> alter database open;

Database altered.


SQL> select name,open_mode,PROTECTION_MODE,DATABASE_ROLE from v$database;

NAME      OPEN_MODE  PROTECTION_MODE      DATABASE_ROLE
--------- ---------- -------------------- ----------------
SKYCARD   READ WRITE MAXIMUM PERFORMANCE  PRIMARY

   完成切换。
  
13  测试一:数据测试
--主库上创建表
SQL> create table test_tf (id integer ,remark varchar2(32));

Table created.

SQL> insert into test_tf values (1,'tf');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from test_tf;

        ID REMARK
---------- --------------------------------
         1 tf

SQL> alter system switch logfile;

System altered.

--从库上验证
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

Database altered.

SQL> alter database open read only;

Database altered.

SQL> select name,open_mode,PROTECTION_MODE,DATABASE_ROLE from v$database;

NAME      OPEN_MODE  PROTECTION_MODE      DATABASE_ROLE
--------- ---------- -------------------- ----------------
SKYCARD   READ ONLY  MAXIMUM PERFORMANCE  PHYSICAL STANDBY

SQL> select * from test_tf;

        ID REMARK
---------- --------------------------------
         1 tf
        
     数据已经过来,测试成功。
    
--测试二,在主库上新建表空间
SQL> select name from v$tablespace;

NAME
------------------------------
SYSTEM
UNDOTBS1
SYSAUX
USERS
TEMP
TS_SKYCARD
TS_TEST_02

SQL> create tablespace ts_test_03 datafile '/opt/oradata/skycard/ts_skyts_test_03.dbf' size 20M autoextend off;

Tablespace created.

SQL> alter system switch logfile;

System altered.

--备库日志情况
Thu Dec 16 10:38:29 2010
RFS[1]: Archived Log: '/opt/oracle/archive/skycard/1_33_737391288.dbf'
Primary database is in MAXIMUM PERFORMANCE mode
Thu Dec 16 10:38:34 2010
Media Recovery Log /opt/oracle/archive/skycard/1_33_737391288.dbf
WARNING: File being created with same name as in Primary
Existing file may be overwritten
Recovery created file /opt/oradata/skycard/ts_skyts_test_03.dbf
Successfully added datafile 7 to media recovery
Datafile #7: '/opt/oradata/skycard/ts_skyts_test_03.dbf'
Media Recovery Waiting for thread 1 sequence 34 (in transit)   

--到备库上验证一下
SQL> select name from v$tablespace;

NAME
------------------------------
SYSTEM
UNDOTBS1
SYSAUX
USERS
TEMP
TS_SKYCARD
TS_TEST_02
TS_TEST_03

8 rows selected.

     表空间 "TS_TEST_03" 已经同步过来了,成功。
    
14 再切回到主节点
--在当前的主节点(原来的备节点进行)
SQL>  select name,open_mode,PROTECTION_MODE,DATABASE_ROLE from v$database;

NAME      OPEN_MODE  PROTECTION_MODE      DATABASE_ROLE
--------- ---------- -------------------- ----------------
SKYCARD   READ WRITE MAXIMUM PERFORMANCE  PRIMARY

SQL>
SQL> select name,open_mode,database_role from v$database;

NAME      OPEN_MODE  DATABASE_ROLE
--------- ---------- ----------------
SKYCARD   READ WRITE PRIMARY

SQL> alter database commit to switchover to physical standby;

Database altered.
   
SQL> shutdown immediate;
ORA-01507: database not mounted

oracle@db-134-109-171-73-> sqlplus " / as sysdba"

SQL> startup nomount pfile='/opt/oracle/product/10.2.0/db_1/dbs/initskycard.ora';
ORACLE instance started.

Total System Global Area 3355443200 bytes
Fixed Size                  2087640 bytes
Variable Size             671089960 bytes
Database Buffers         2667577344 bytes
Redo Buffers               14688256 bytes
SQL> alter database mount standby database;

Database altered.

SQL> select name,open_mode ,database_role from v$database;

NAME      OPEN_MODE  DATABASE_ROLE
--------- ---------- ----------------
SKYCARD   MOUNTED    PHYSICAL STANDBY

SQL> alter database recover managed standby database disconnect from session;

Database altered.

SQL> select name,open_mode ,database_role from v$database;

NAME      OPEN_MODE  DATABASE_ROLE
--------- ---------- ----------------
SKYCARD   MOUNTED    PHYSICAL STANDBY


--在备节点(原先的主节点上进行)
SQL> select open_mode ,database_role from v$database;

OPEN_MODE  DATABASE_ROLE
---------- ----------------
MOUNTED    PHYSICAL STANDBY

SQL> alter database commit to switchover to primary;

Database altered.

SQL> shutdown  immediate;
ORA-01507: database not mounted

oracle@db-134-109-171-72-> sqlplus " / as sysdba"
SQL> startup;
ORACLE instance started.

Total System Global Area 3355443200 bytes
Fixed Size                  2087640 bytes
Variable Size             671089960 bytes
Database Buffers         2667577344 bytes
Redo Buffers               14688256 bytes
Database mounted.
Database opened.

SQL> select open_mode ,database_role from v$database;

OPEN_MODE  DATABASE_ROLE
---------- ----------------
READ WRITE PRIMARY

   到这里,完成主库和备库之间自由切换。
  
15  附:主库从库开启顺序。
开启顺序:     先将从库启动到Mount状态,然后再开主库
关闭顺序:     先关闭主库,再关闭从库

16 附上一张LGWR ASYNC原理图,来自官网,这也是这次实验应用的DATAGUARD 模式

 
DataGuard之一:Oracle10g dg搭建 - francs - My DBA LIFE
 

 说明:
When the LGWR and ASYNC attributes are specified, the log writer process writes to the local online redo log file,
while the network server (LNSn) processes (one for each destination) asynchronously transmit the redo to remote destinations.
The LGWR process continues processing the next request without waiting for the LNS network I/O to complete.

If redo transport services transmit redo data to multiple remote destinations, the LNSn processes (one for each destination)
initiate the network I/O to all of the destinations in parallel.

When an online redo log file fills up, a log switch occurs and an archiver process archives the log file locally, as usual.

ARC0 process:
     ARC0 process archives the local online redo log to the local destination (LOG_ARCHIVE_DEST_1)。
 
ARC1 process:
            transmits redo from the local archived redo log files (instead of the online redo log files) to 
            the remote standby destination (LOG_ARCHIVE_DEST_2).
       
RFS:the remote file server process
     receives redo data over the network from the LGWR process and writes the redo data to the standby redo log files
   
MRP: Redo Apply  process
     apply the redo to the standby database

--再次附上主库参数
*.DB_UNIQUE_NAME='PRIMARY_1'
*.log_archive_config='DG_CONFIG=(PRIMARY_1,STANDBY_1)'
*.log_archive_dest_1='LOCATION=/opt/oracle/archive/skycard/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=PRIMARY_1'
*.log_archive_dest_2='SERVICE=STANDBY_1 LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=STANDBY_1'
*.LOG_ARCHIVE_DEST_STATE_1=ENABLE
*.LOG_ARCHIVE_DEST_STATE_2=ENABLE
*.FAL_SERVER=STANDBY_1
*.FAL_CLIENT=PRIMARY_1
*.standby_file_management='AUTO'
*.LOG_ARCHIVE_MAX_PROCESSES=3

  评论这张
 
阅读(28724)| 评论(0)
推荐 转载

历史上的今天

在LOFTER的更多文章

评论

<#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

网易公司版权所有 ©1997-2016