一个单实例DG的搭建记录

单实例DG搭建手册

规划

ip	db_name	db_unique_name	sid	tns
192.168.10.103	test	test	test	test
192.168.10.104	test	dgdb	dgdb	dgdb

配置监听
主库listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/11.2/network/admin/listener.ora
# Generated by Oracle configuration tools.


SID_LIST_DG =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = test)
      (ORACLE_HOME = /u01/app/oracle/product/11.2)
      (SID_NAME = test)
    )
  )
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = lzl)(PORT = 1521))
    )
  )


ADR_BASE_LISTENER = /u01/app/oracle


备库listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/11.2/network/admin/listener.ora
# Generated by Oracle configuration tools.


SID_LIST_DG =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = dgdb)
      (ORACLE_HOME = /u01/app/oracle/product/11.2)
      (SID_NAME = dgdb)
    )
  )


LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = l2l)(PORT = 1521))
    )
  )


ADR_BASE_LISTENER = /u01/app/oracle


主备库的tnsnames.ora
test =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = lzl)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SID = test)
      (SERVER = DEDICATED)
    )
  )


dgdb =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = l2l)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SID = dgdb)
      (SERVER = DEDICATED)
    )
  )




主库准备
更改日志模式
防止使用no logging子句造成主备库数据不一致
SQL> alter database force logging
  2  /


Database altered.
SQL> select force_logging from v$database;


FOR
---
YES


是否归档
SQL> select log_mode from v$database;


LOG_MODE
------------
ARCHIVELOG




更改log_archive_config
SQL>  alter system set log_archive_config=‘DG_CONFIG=(test,dgdb)' scope=both ;


System altered.


SQL> show parameter archive_config


NAME     TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_config     string DG_CONFIG=(test,dgdb)
SQL> 


配置DG参数
alter system set log_archive_dest_1='location=/home/oracle/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=test' scope=both;
alter system set log_archive_dest_2 ='SERVICE=dgdb LGWR SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=dgdb' scope=both;
alter system set log_archive_dest_state_1 = ENABLE;                
alter system set log_archive_dest_state_2 = ENABLE;
数据文件自动添加
alter system set standby_file_management=auto scope=both;


alter system set fal_server=dgdb scope=both;
alter system set fal_client=test scope=both;


1800s自动切换redo log
alter system set archive_lag_target=1800;


文件位置调整
alter system set db_file_name_convert='/u01/app/oracle/oradata/test/','/u01/app/oracle/oradata/dgdb/' scope=spfile; 
alter system set log_file_name_convert='/u01/app/oracle/oradata/logfile/','/u01/app/oracle/oradata/logfile/' scope=spfile; 


2.密码文件
使用以前的密码文件或者直接创建一个密码文件
[oracle@lzl ~]$ orapwd password=oracle file='/home/oracle/orapwd.ora' force=y ignorecase=y
[oracle@lzl ~]$ ls
orapwd.ora


3.备份数据库
backup database


4.生成pfile
create pfile='/home/oracle/initdgdb.ora' from spfile;
修改参数
*.db_unique_name='dgdb'
*.fal_client='dgdb'
*.fal_server='test'
*.service_names='dgdb'
*.log_archive_dest_1='location=/home/oracle VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=dgdb'
*.log_archive_dest_2='SERVICE=dgdb LGWR SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=test'




db_name应与主库保持一致


6.生成stanby controlfile
SQL> alter database create standby controlfile as '/home/oracle/control01.ctl';


Database altered.


备库准备
备库是一个只有数据库软件的环境
1.拷贝stanby controlfile，pfile，orapwd 文件到备库
控制文件拷贝到pfile指定的目录中
密码文件更改sid到/oracle/home/dbs中


[oracle@l2l ~]$ ls
 control01.ctl  initdgdb.ora  orapwd.ora
2.创建pfile中的目录
审计目录、控制文件目录、数据文件目录等


3.启动备库到mount阶段


[oracle@l2l ~]$ sqlplus / as sysdba


SQL*Plus: Release 11.2.0.3.0 Production on Thu Jul 27 17:04:07 2017


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


Connected to an idle instance.


SQL> startup nomount pfile='/home/oracle/initdgdb.ora';
ORACLE instance started.


Total System Global Area 1048059904 bytes
Fixed Size    2235000 bytes
Variable Size  310379912 bytes
Database Buffers  729808896 bytes
Redo Buffers    5636096 bytes
SQL> alter database mount;


Database altered.


SQL> select database_role from v$database;


DATABASE_ROLE
----------------
PHYSICAL STANDBY

4.rman数据恢复
使用rman将主库的备份应用于备库
RMAN> catalog start with ‘/home/oracle/liu/’
RMAN> restore database；


5.创建standby redo log files，数量至少是主库的两倍
standby redo log 与redo log是很相似的，但是standby redo log是用来在standby库上接收主库的redo data的。
备库上不会使用online redo log
为了方便任意切换，最好是主备库都创建standby redo log files
SQL> alter database add standby logfile group 11 ('/u01/app/oracle/oradata/test/dg_redo11.log' ) size 50M;

Database altered.
SQL> alter database add standby logfile group 12 ('/u01/app/oracle/oradata/test/dg_redo12.log' ) size 50M;


Database altered.
SQL> alter database add standby logfile group 13 ('/u01/app/oracle/oradata/test/dg_redo13.log' ) size 50M;


Database altered.
SQL> alter database add standby logfile group 14 ('/u01/app/oracle/oradata/test/dg_redo14.log' ) size 50M;


Database altered.
SQL> alter database add standby logfile group 15 ('/u01/app/oracle/oradata/test/dg_redo15.log' ) size 50M;


Database altered.
SQL> alter database add standby logfile group 16 ('/u01/app/oracle/oradata/test/dg_redo16.log' ) size 50M;


Database altered.


查看v$standby_log
SQL> select group# from v$standby_log;


    GROUP#
----------
10
11
12
13
14
15


6 rows selected.


SQL> select group# from v$log;


    GROUP#
----------
1
3
2
查看文件还是需要v$logfile
SQL> select member from v$logfile;


MEMBER
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/test/redo03.log
/u01/app/oracle/oradata/test/redo02.log
/u01/app/oracle/oradata/test/redo01.log
/u01/app/oracle/oradata/test/dg_redo11.log
/u01/app/oracle/oradata/test/dg_redo12.log
/u01/app/oracle/oradata/test/dg_redo13.log
/u01/app/oracle/oradata/test/dg_redo14.log
/u01/app/oracle/oradata/test/dg_redo15.log
/u01/app/oracle/oradata/test/dg_redo16.log


9 rows selected.




建立主备库DG关系


备库
SQL>  alter database recover managed standby database using current logfile disconnect from session ;


Database altered.


告警日志信息：
 alter database recover managed standby database using current logfile disconnect from session 
Attempt to start background Managed Standby Recovery process (dgdb)
Sat Jul 28 19:28:48 2017
MRP0 started with pid=25, OS id=3264 
MRP0: Background Managed Standby Recovery process started (dgdb)
Serial Media Recovery started
Managed Standby Recovery starting Real Time Apply
Waiting for all non-current ORLs to be archived...
All non-current ORLs have been archived.
Media Recovery Waiting for thread 1 sequence 100
Completed:  alter database recover managed standby database using current logfile disconnect from session 


查看备库角色
SQL>  select PROTECTION_MODE ,PROTECTION_LEVEL ,GUARD_STATUS,DATABASE_ROLE,to_char(current_scn) from v$database;


PROTECTION_MODE      PROTECTION_LEVEL  GUARD_S DATABASE_ROLE
-------------------- -------------------- ------- ----------------
TO_CHAR(CURRENT_SCN)
----------------------------------------
MAXIMUM AVAILABILITY MAXIMUM AVAILABILITY NONE  PHYSICAL STANDBY
2481074


主库检查


备库检查
SQL>  select PROTECTION_MODE ,PROTECTION_LEVEL ,GUARD_STATUS,DATABASE_ROLE,to_char(current_scn) from v$database;


PROTECTION_MODE      PROTECTION_LEVEL  GUARD_S DATABASE_ROLE
-------------------- -------------------- ------- ----------------
TO_CHAR(CURRENT_SCN)
----------------------------------------
MAXIMUM AVAILABILITY RESYNCHRONIZATION  NONE  PRIMARY
2482972

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

转载于:http://blog.itpub.net/31461640/viewspace-2142777/