linux oracle11g dataguard配置

最新推荐文章于 2024-06-29 22:01:09 发布

robbie1314520

最新推荐文章于 2024-06-29 22:01:09 发布

阅读量3.7k

点赞数

分类专栏： oracle相关

本文链接：https://blog.csdn.net/robbie1314520/article/details/8282197

版权

oracle相关专栏收录该内容

54 篇文章 1 订阅

订阅专栏

系统环境：两台相同的centos6.2
在主库(primary database)中提前安装好了Oracle 11gR2软件并创建了数据库，实例名为DB11G。
IP:192.168.1.203 hostname:robbiehost db_unique_name:DB11G
备库(standby database)(192.168.1.201)中只安装了Oracle 11gR2软件，未创建数据库。
IP:192.168.1.201 hostname:robbiehost2 db_unique_name:DB11G_STBY

主库配置
1、确认主库开启了archivelog模式，并设置为force logging模式

sys@DB11G> ALTER DATABASE FORCE LOGGING;

2、创建standby controlfile

sys@DB11G>alter database create standby controlfile as '/u01/app/oracle/oradata/DB11G/standby.ctl

3、登录到主库，了解一下数据文件的信息，之后安全关闭数据库，将全部数据文件拷贝到备库的相应目录下.

sys@DB11G> select name from v$datafile;
NAME
-----------------------------------
/u01/app/oracle/oradata/DB11G/system01.dbf
/u01/app/oracle/oradata/DB11G/sysaux01.dbf
/u01/app/oracle/oradata/DB11G/undotbs01.dbf
/u01/app/oracle/oradata/DB11G/users01.dbfsys@DB11G> show parameter control_files
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_files                        string      /u01/app/oracle/oradata/DB11G/
                                                 control01.ctl, /u01/app/oracle
                                                 /flash_recovery_area/DB11G/con
                                                 trol02.ctlsys@DB11G> select group#,member from v$logfile;
GROUP# MEMBER
------ --------------------------------------------------
     3 /u01/app/oracle/oradata/DB11G/redo03.log
     2 /u01/app/oracle/oradata/DB11G/redo02.log
     1 /u01/app/oracle/oradata/DB11G/redo01.log
sys@DB11G> shutdown immediate
Database closed.Database dismounted.ORACLE instance shut down.
sys@DB11G> quit

[oracle@robbiehost~]$ cd /u01/app/oracle/oradata/[oracle@robbiehostoradata]$ scp -r DB11G/ oracle@192.168.1.201:/u01/app/oracle/oradata/
oracle@192.168.1.201's password:
system01.dbf 100% 172MB 34.4MB/s 00:05 
redo01b.rdo 100% 100MB 33.3MB/s 00:03 
sysaux01.dbf 100% 100MB 50.0MB/s 00:02 
undotbs01.dbf 100% 133MB 44.3MB/s 00:03 
....... 
[oracle@robbiehostoradata]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Tue Feb 21 20:54:47 2012Copyright (c) 1982, 2009, Oracle. 
All rights reserved.Connected to an idle instance.
sys@DB11G>startup
ORACLE instance started.
Total System Global Area 1603411968 bytes
Fixed Size 2213776 bytes
Variable Size 402655344 bytes
Database Buffers 1191182336 bytes
Redo Buffers 7360512 bytes
Database mounted.
Database opened.

4、主库修改初始化参数文件

sys@DB11G> create pfile from spfile;
File created.
sys@DB11G> !
[oracle@robbiehost oradata] cd $ORACLE_HOME/dbs
[oracle@robbiehost dbs] cat initDB11G.ora 
DB11G.__db_cache_size=29360128
DB11G.__java_pool_size=16777216
DB11G.__large_pool_size=4194304
DB11G.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
DB11G.__pga_aggregate_target=557842432
DB11G.__sga_target=230686720
DB11G.__shared_io_pool_size=0
DB11G.__shared_pool_size=167772160
DB11G.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/DB11G/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/u01/app/oracle/oradata/DB11G/control01.ctl','/u01/app/oracle/flash_recovery_area/DB11G/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='DB11G'
*.db_recovery_file_dest='/u01/app/oracle/flash_recovery_area'
*.db_recovery_file_dest_size=4070572032
*.db_unique_name='DB11G'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=DB11GXDB)'
*.fal_client='DB11G'
*.fal_server='DB11G_STBY'
*.local_listener='LISTENER_DB11G'
*.log_archive_config='DG_CONFIG=(DB11G,DB11G_STBY)'
*.log_archive_dest_1='LOCATION=/u01/arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=DB11G'
*.log_archive_dest_2='SERVICE=db11g_stby OPTIONAL LGWR SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=DB11G_STBY'
*.log_archive_dest_state_1='ENABLE'
*.log_archive_dest_state_2='ENABLE'
*.log_archive_format='%t_%s_%r.arc'
*.log_archive_max_processes=30
*.memory_target=787480576
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.standby_file_management='AUTO'
*.undo_tablespace='UNDOTBS1'

5、主库创建pfile和passwordfile后，拷贝到备库的相同路径下

[oracle@robbiehost dbs]orapwd file=orapwDB11G password=sys force=y ignorecase=y
[oracle@robbiehost dbs]scp -r orapwDB11G initDB11G.ora 192.168.1.201:/u01/app/oracle/product/11.2.0/db_1/dbs
oracle@192.168.1.201's password:
orapwDB11G                                                                               100% 1536     1.5KB/s   00:00   
initDB11G.ora                                                                            100% 1496     1.5KB/s   00:00

6、备库获取数据文件，初始化参数文件和口令文件后，对初始化参数文件进行相应修改。其实就将这几个参数依据备库，与主库做一个映像般的调换

[oracle@robbiehost2 dbs]$ cat initDB11G.ora
DB11G.__db_cache_size=92274688
DB11G.__java_pool_size=4194304
DB11G.__large_pool_size=4194304
DB11G.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
DB11G.__pga_aggregate_target=507510784
DB11G.__sga_target=281018368
DB11G.__shared_io_pool_size=0
DB11G.__shared_pool_size=167772160
DB11G.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/DB11G/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/u01/app/oracle/oradata/DB11G/standby.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='DB11G'
*.db_recovery_file_dest='/u01/app/oracle/flash_recovery_area'
*.db_recovery_file_dest_size=4070572032
*.db_unique_name='DB11G_STBY'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=DB11GXDB)'
*.fal_server='DB11G'
*.fal_client='DB11G_STBY'
*.local_listener='DB11G'
*.log_archive_config='DG_CONFIG=(DB11G,DB11G_STBY)'
*.log_archive_dest_1='LOCATION=/u01/arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=DB11G_STBY'
*.log_archive_dest_2='SERVICE=db11g OPTIONAL LGWR SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=DB11G'
*.log_archive_dest_state_1='ENABLE'
*.log_archive_dest_state_2='ENABLE'
*.log_archive_format='%t_%s_%r.arc'
*.log_archive_max_processes=30
*.memory_target=787480576
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.standby_file_management='AUTO'
*.undo_tablespace='UNDOTBS1'

7、配置主库和备库中的监听服务和tnsnames.ora

主库：

[oracle@robbiehost admin]$ cat listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/11.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 = /u01/app/oracle/product/11.2.0/db_1)
      (PROGRAM = extproc)
    )
 
    (SID_DESC =
      (SID_NAME = DB11G)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
      (GLOBAL_DBNAME = db11g)
    )
  )
 
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.203)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
    )
  )

ADR_BASE_LISTENER = /u01/app/oracle

[oracle@robbiehost admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

DB11G_STBY =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.201)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = db11g)
    )
  )
 
EXTPROC_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
    )
    (CONNECT_DATA =
      (SID = PLSExtProc)
      (PRESENTATION = RO)
    )
  )

备库：

[oracle@robbiehost2 admin]$ cat listener.ora 
# listener.ora Network Configuration File: /u01/app/oracle/product/11.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 = /u01/app/oracle/product/11.2.0/db_1)
      (PROGRAM = extproc)
    )
 
    (SID_DESC =
      (SID_NAME = DB11G)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
      (GLOBAL_DBNAME = db11g)
    )
  )
 
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.201)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
    )
  )
[oracle@robbiehost2 admin]$ cat tnsnames.ora 
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

DB11G =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.203)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = db11g)
    )
  )

配置完成后，尝试从两台服务器以sysdba身份互相访问（也可用tnsping）

[oracle@robbiehost admin]$ sqlplus sys/robbie@DB11G_STBY as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Tue Dec 11 17:38:24 2012

Copyright (c) 1982, 2009, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> 

---------------------
[oracle@robbiehost2 admin]$ sqlplus sys/robbie@DB11G as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Tue Dec 11 17:37:58 2012

Copyright (c) 1982, 2009, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>

8、启动备库

[oracle@robbiehost2 admin]sqlplus / as sysdba
sys@DB11G> startup nomount;
ORACLE instance started.
Total System Global Area  784998400 bytes
Fixed Size                  2217464 bytes
Variable Size             683674120 bytes
Database Buffers           92274688 bytes
Redo Buffers                6832128 bytes
sys@DB11G> alter database mount standby database;
Database altered.
sys@DB11G> alter database recover managed standby database disconnect from session;
Database altered.
sys@DB11G> select database_role from v$database; 
DATABASE_ROLE
----------------
PHYSICAL STANDBY

到这里基本已经配置完了

9、验证：主库中创建一个表，并插入若凡数据后，备库中能够同步出这些数据，则认为DG实施成功
主库：

sys@DB11G>create table test(id number);
Table created.
sys@DB11G> commit;
Commit complete.
sys@DB11G> begin
  2  for i in 1..10 loop
  3  insert into test values(i);
  4  end loop;
  5  commit;
  6  end;
  7  /
PL/SQL procedure successfully completed.
sys@DB11G> select * from test;
        ID
----------
         1
         2
         3
         4
         5
         6
         7
         8
         9
        10
10 rows selected.
sys@DB11G> alter system switch logfile;
System altered.
sys@DB11G> /
System altered.

备库

sys@DB11G> select name,database_role from v$database;
NAME                                DATABASE_ROLE
----------------------------------- ----------------
DB11G                               PHYSICAL STANDBY
sys@DB11G> select sequence#,first_time,next_time from v$archived_log;
SEQUENCE# FIRST_TIM NEXT_TIME
--------- --------- ---------
       40 06-DEC-12 06-DEC-12
       41 06-DEC-12 06-DEC-12
       36 06-DEC-12 06-DEC-12
       37 06-DEC-12 06-DEC-12
       44 06-DEC-12 06-DEC-12
       45 06-DEC-12 06-DEC-12
       46 06-DEC-12 06-DEC-12
       47 06-DEC-12 06-DEC-12
       53 07-DEC-12 07-DEC-12
       50 07-DEC-12 07-DEC-12
       49 07-DEC-12 07-DEC-12
       52 07-DEC-12 07-DEC-12
       51 07-DEC-12 07-DEC-12
       48 06-DEC-12 07-DEC-12
       54 07-DEC-12 07-DEC-12
       55 07-DEC-12 07-DEC-12
       56 07-DEC-12 07-DEC-12
.....
sys@DB11G> alter database recover managed standby database cancel;
Database altered.
sys@DB11G> alter database open read only;
Database altered.
sys@DB11G> select * from test;
        ID
----------
         1
         2
         3
         4
         5
         6
         7
         8
         9
        10
10 rows selected.

10、配置成功
本文参考：http://www.oracle-base.com/articles/11g/data-guard-setup-11gr2.php

例子：http://ylw6006.blog.51cto.com/470441/841815

dateguard日常管理维护：http://blog.csdn.net/robbie1314520/article/details/8267283

robbie1314520

关注

0
点赞
踩
4

收藏

觉得还不错? 一键收藏
0
评论
linux oracle11g dataguard配置

系统环境：两台相同的centos6.2在主库(primary database)中提前安装好了Oracle 11gR2软件并创建了数据库，实例名为DB11G。IP:192.168.1.203 hostname:robbiehost db_unique_name:DB11G备库(standby database)(192.168.1.201)中只安装了Oracle 11gR2软件，
复制链接

扫一扫

专栏目录