我的DataGuard配置

  DG终于搭建完成了，不容易啊。记录下，方便以后查找。主要配置如下——

  OS--CentOS 5.4 x64

  DB--Oracle 10g R2

1、将主库shutdown，并备份主库的数据文件，/oracle/app/oradata/目录下所有文件

2、将主库置于mount状态，创建备库的控制文件

alter database create standby controlfile as '/tmp/control01.ctl';

3、create pfile from spfile；修改成如下内容

主库控制文件

demo1.__db_cache_size=432013312 
demo1.__java_pool_size=8388608 
demo1.__large_pool_size=4194304 
demo1.__shared_pool_size=155189248 
*.archive_lag_target=0 
*.background_dump_dest='/oracle/app/admin/demo1/bdump' 
*.compatible='10.1.0.2.0' 
*.control_files='/oracle/app/oradata/demo1/control01.ctl','/oracle/app/oradata/demo1/control02.ctl','/oracle/app/oradata/demo1/control03.ctl'#Restore Controlfile 
*.core_dump_dest='/oracle/app/admin/demo1/cdump' 
*.db_block_size=8192 
*.db_domain='' 
*.db_file_multiblock_read_count=16 
*.db_name='demo1' 
*.db_recovery_file_dest='/oracle/app/oracle/product/10.1.0/Db_1/flash_rec_area' 
*.db_recovery_file_dest_size=6442450944 
*.db_unique_name='primary'##############（备库改为standby） 
*.dg_broker_start=TRUE 
*.dispatchers='(PROTOCOL=TCP) (SERVICE=demo1XDB)' 
*.fal_client='standby' 
*.fal_server='primary' 
*.job_queue_processes=10 
*.log_archive_config='nodg_config' 
*.log_archive_dest_1='LOCATION=/oracle/archive' 
demo1.log_archive_dest_1='location="/oracle/archive"','valid_for=(ONLINE_LOGFILE,ALL_ROLES)' 
*.log_archive_dest_2='SERVICE=standby' 
*.log_archive_dest_state_1='enable' 
*.log_archive_dest_state_2='ENABLE' 
demo1.log_archive_format='%t_%s_%r.dbf' 
*.log_archive_max_processes=2 
*.log_archive_min_succeed_dest=1 
demo1.log_archive_trace=0 
*.open_cursors=300 
*.pga_aggregate_target=201326592 
*.processes=150 
*.remote_login_passwordfile='EXCLUSIVE' 
*.service_names='demo1','primary_XPT' 
*.sga_target=603979776 
demo1.standby_archive_dest='' 
*.standby_file_management='AUTO' 
*.undo_management='AUTO' 
*.undo_tablespace='UNDOTBS1' 
*.user_dump_dest='/oracle/app/admin/demo1/udump'

4、将上面三步生成的文件都拷贝到standby上。

5、主库和备库的tnsnames.ora完全一样，如下：

PRIMARY = 
  (DESCRIPTION = 
    (ADDRESS_LIST = 
      (ADDRESS = (PROTOCOL = TCP)(HOST = oracle)(PORT = 1521)) 
    ) 
    (CONNECT_DATA = 
      (SID = demo1) 
    ) 
  )

STANDBY = 
  (DESCRIPTION = 
    (ADDRESS_LIST = 
      (ADDRESS = (PROTOCOL = TCP)(HOST = standby)(PORT = 1521)) 
    ) 
    (CONNECT_DATA = 
      (SID = demo2) 
    ) 
  )

6、主库的listener.ora配置如下：

LISTENER = 
  (DESCRIPTION_LIST = 
    (DESCRIPTION = 
      (ADDRESS_LIST = 
        (ADDRESS = (PROTOCOL = TCP)(HOST = oracle)(PORT = 1521)) 
      ) 
      (ADDRESS_LIST = 
        (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC)) 
      ) 
    ) 
  )

SID_LIST_ORACLE = 
  (SID_LIST = 
    (SID_DESC = 
      (SID_NAME = PLSExtProc) 
      (ORACLE_HOME = /oracle/app/oracle/product/10.1.0/Db_1) 
      (PROGRAM = extproc) 
    ) 
  )

备库的listener.ora配置如下：

LISTENER = 
  (DESCRIPTION = 
    (ADDRESS = (PROTOCOL = TCP)(HOST = standby)(PORT = 1521)) 
  )

7、启动备库

SQL> startup nomount;

SQL> alter database mount standby database;

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

8、查看状态

主库

SQL> select name,database_role from v$database;

NAME      DATABASE_ROLE 
--------- ---------------- 
DEMO1     PRIMARY

SQL> show parameter standby;

NAME                                 TYPE        VALUE 
------------------------------------ ----------- ------------------------------ 
standby_archive_dest                 string 
standby_file_management              string      AUTO

备库

SQL> select name,database_role from v$database;

NAME      DATABASE_ROLE 
--------- ---------------- 
DEMO1     PHYSICAL STANDBY

SQL> show parameter standby;

NAME                                 TYPE        VALUE 
------------------------------------ ----------- ------------------------------ 
standby_archive_dest                 string      ?/dbs/arch 
standby_file_management              string      MANUAL

附录--

关于Standby库的维护操作

启动Redo应用：

SQL>startup mount;

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

切换到open状态供查询

关闭Redo应用：

SQL> alter database recover managed standby database cancel;

SQL>alter database open read only;

在备用数据库上，查看是否已经接受到归档日志：

select sequence#, first_time, next_time from v$archived_log order by sequence#;

在备用数据库上，查看归档日志是否被应用：

select sequence#,applied from v$archived_log order by sequence#;

SQL>ALTER DATABASE ADD STANDBY LOGFILE THREAD 5 ('/oracle/dbs/log1c.rdo','/oracle/dbs/log2c.rdo') SIZE 50M;

SQL> ALTER DATABASE FORCE LOGGING;