oracle11.2.0.4-active datagurd安装配置全过程实战

Database Name :- primarydb
Primary db_unique_name :- primarydb
standby db_unique_name :- targetdb
===========================================
hosts文件配置
127.0.0.1     localhost
192.168.137.20  primarydb
192.168.137.30  targetdb


主库创建完成数据库primarydb，备库安装完软件不建库，开始配置dg。


primarydb  listener.ora
=====================================================================




LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.137.20)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )


SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = primarydb)
      (SERVICE_NAME = primarydb)
      (SID_NAME = primarydb)
      (ORACLE_HOME = /local/oracle/OraHome1)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = primarydb_DGMGRL)
      (SERVICE_NAME = primarydb)
      (SID_NAME = primarydb)
      (ORACLE_HOME = /local/oracle/OraHome1)
  )
)


ADR_BASE_LISTENER = /local/oracle


targetdb  listener.ora
=====================================================================
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.137.30)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )


SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =(GLOBAL_DBNAME = targetdb)
      (SERVICE_NAME = targetdb)
      (SID_NAME = targetdb)
      (ORACLE_HOME = /local/oracle/OraHome1))
    (SID_DESC =(GLOBAL_DBNAME = targetdb_DGMGRL)
      (SERVICE_NAME = targetdb)
      (SID_NAME = targetdb)
      (ORACLE_HOME = /local/oracle/OraHome1))
  )


ADR_BASE_LISTENER = /local/oracle


====================================================================
tnsnames.ora 两节点配置一样


BEIJING =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.137.20)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = primarydb)(UR=A)
    )
  )


CHICAGO =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.137.30)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = targetdb)(UR=A)
    )
  )


% tnsping beijing
% tnsping CHICAGO




确保主数据库是 archivelog 模式。


shutdown immediate;
startup mount
alter database archivelog;
alter database open;
archive log list;


alter system set log_archive_dest_1='location=/archlog';




SQL> select log_mode from v$database;
LOG_MODE
------------
ARCHIVELOG




修改主数据库参数
SQL> alter database force logging;  
Database altered.  
SQL> select DB_UNIQUE_NAME,OPEN_MODE,FORCE_LOGGING,DATABASE_ROLE,SWITCHOVER_STATUS from v$database;


DB_UNIQUE_NAME                 OPEN_MODE            FOR DATABASE_ROLE    SWITCHOVER_STATUS
------------------------------ -------------------- --- ---------------- --------------------
primarydb                         READ WRITE           YES PRIMARY          NOT ALLOWED




===============================================================================================================
创建standby redo 为N+1组  N为目前组数，standby=N+1


SQL> col member for a50
SQL> select group#,status,type,member,IS_RECOVERY_DEST_FILE from v$logfile order by 1;


    GROUP# STATUS  TYPE    MEMBER                                             IS_
---------- ------- ------- -------------------------------------------------- ---
         1         ONLINE  /local/oracle/oradata/primarydb/redo01.log            NO
         2         ONLINE  /local/oracle/oradata/primarydb/redo02.log            NO
         3         ONLINE  /local/oracle/oradata/primarydb/redo03.log            NO


select group#,bytes/1024/1024  from v$log;


    GROUP# BYTES/1024/1024
---------- ---------------
         1              50
         2              50
         3              50


SQL> alter database add standby logfile  '/local/oracle/oradata/primarydb/standby01.log' size 50M;
SQL> alter database add standby logfile  '/local/oracle/oradata/primarydb/standby02.log' size 50M;
SQL> alter database add standby logfile  '/local/oracle/oradata/primarydb/standby03.log' size 50M;
SQL> alter database add standby logfile  '/local/oracle/oradata/primarydb/standby04.log' size 50M;
 
============================================================================================
备库
创建需要的目录和参数文件
mkdir -p /local/oracle/admin/targetdb/adump
mkdir -p /oradata/targetdb/
mkdir -p /archlog


创建密码文件 命名规则 orapw+sid
scp /local/oracle/OraHome1/dbs/orapwprimarydb 192.168.170.30:/local/oracle/OraHome1/dbs/orapwtargetdb


创建init.ora文件
[oracle@targetdb dbs]$ vi inittargetdb.ora 
DB_NAME=primarydb


==============================================================================================
四：将备库启动到nomount状态，然后连接主库进行duplicate操作


$ lsnrctl start  


sqlplus "/ as sysdba"
SQL> startup nomount pfile=/local/oracle/OraHome1/dbs/inittargetdb.ora


从备机验证“AS SYSDBA”连接正在运行


SQL> connect sys/oracle@BEIJING AS SYSDBA




主机上进行数据同步


创建同步脚本
vi /home/oracle/dup.txt


run{
  allocate channel prmy1 type disk;
  allocate channel prmy2 type disk;
  allocate channel prmy3 type disk;
  allocate auxiliary channel stby4 type disk;
  allocate auxiliary channel stby5 type disk;
  allocate auxiliary channel stby6 type disk;
  duplicate targetdb database for standby from active database
   spfile
     parameter_value_convert 'primarydb','targetdb'
     set db_unique_name='targetdb'
     set db_file_name_convert='/primarydb/','/targetdb/'
     set log_file_name_convert='/primarydb/','/targetdb/'
     set log_archive_max_processes='5'
     set fal_client='chicago'
     set fal_server='beijing'
     set standby_file_management='AUTO'
     set log_archive_config='dg_config=(primarydb,targetdb)'
     set log_archive_dest_2='service=beijing ASYNC NOAFFIRM REOPEN=60 valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=primarydb'
     nofilenamecheck;
   release channel prmy1;
   release channel prmy2;
   release channel prmy3;
   release channel stby4;
   release channel stby5;
   release channel stby6;
}




rman targetdb sys/oracle@beijing auxiliary sys/oracle@chicago


rman> @dup.txt






完成后在主库上修改参数
alter system set log_archive_config='DG_CONFIG=(primarydb,targetdb)' scope=both;  
alter system set log_archive_dest_2='SERVICE=chicago ASYNC NOAFFIRM REOPEN=60 valid_for=(online_logfile,primary_role) db_unique_name=targetdb' scope=both; 


alter system set fal_client='beijing' scope=both;   
alter system set fal_server='chicago' scope=both; 


alter system set log_archive_dest_state_1='ENABLE' scope=both; 
alter system set log_archive_dest_state_2='ENABLE' scope=both; 


alter system set standby_file_management='AUTO' scope=both; 
alter system set remote_login_passwordfile='EXCLUSIVE' scope=both; 


备机上执行
alter system set log_archive_dest_state_1='ENABLE' scope=both; 
alter system set log_archive_dest_state_2='ENABLE' scope=both; 
alter system set remote_login_passwordfile='EXCLUSIVE' scope=both; 




将备库置于active dataguard模式下现在连接备用数据库 sqlplus 并启动 MRP（管理恢复进程）。
比较主数据库的最新序列和 MRP（管理恢复进程）的应用序列。


SQL> select process,status from v$managed_standby; 
查看archilog 目录中归档数量是否一致。
select thread#,max(sequence#) from v$log_history group by thread#;
查询结果数值必须一致。


开始应用恢复日志
SQL>alter database recover managed standby database using current logfile disconnect from session;


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


  
五. 用只读模式打开 DB 并启动恢复。


SQL> alter database recover managed standby database cancel;
SQL> alter database open;
SQL> alter database recover managed standby database using current logfile disconnect;




查看数据库状态


在备库
SQL> select process,status from v$managed_standby; 
PROCESS   STATUS
--------- ------------
ARCH      CLOSING
ARCH      CONNECTED
RFS       IDLE
RFS       IDLE
MRP0      APPLYING_LOG


select thread#,max(sequence#) from v$log_history group by thread#;
查询结果数值必须一致


在主节点多次切换日志文件，查询主备节点是否一致
alter system switch logfile;
select thread#,max(sequence#) from v$log_history group by thread#;
--以下为查询DataGuard状态
select thread#,sequence#,archived,applied from v$archived_log order by sequence# desc;










切换操作
=============================主备切换===============================================================
查看节点主备关系


主
SQL> select database_role,OPEN_MODE,PROTECTION_MODE,PROTECTION_LEVEL,SWITCHOVER_STATUS from v$database;
SWITCHOVER_STATUS
--------------------
TO STANDBY


备
SQL> select database_role,OPEN_MODE,PROTECTION_MODE,PROTECTION_LEVEL,SWITCHOVER_STATUS from v$database;
SWITCHOVER_STATUS
-----------------
TO PRIMARY




2.将主库切换至备用模式
SQL> alter database commit to switchover to physical standby with session shutdown;
SQL> select database_role,OPEN_MODE,PROTECTION_MODE,PROTECTION_LEVEL,SWITCHOVER_STATUS from v$database;




3 将备库切成主库的角色
SQL> alter database recover managed standby database cancel;
SQL> alter database commit to switchover to primary;
SQL> select database_role,OPEN_MODE,PROTECTION_MODE,PROTECTION_LEVEL,SWITCHOVER_STATUS from v$database;
SQL> alter database open;
SQL> select database_role,OPEN_MODE,PROTECTION_MODE,PROTECTION_LEVEL,SWITCHOVER_STATUS from v$database;


4 将原主库启动到mount
$sqlplus “/ as sysdba”
SQL> startup mount


5 将原主库开始恢复应用日志
SQL> alter database recover managed standby database using current logfile disconnect from session;
SQL> select database_role,OPEN_MODE,PROTECTION_MODE,PROTECTION_LEVEL,SWITCHOVER_STATUS from v$database;


6 打开原主库并恢复应用日志
SQL> alter database recover managed standby database cancel;
SQL> alter database open;
SQL> select database_role,OPEN_MODE,PROTECTION_MODE,PROTECTION_LEVEL,SWITCHOVER_STATUS from v$database;
SQL> alter database recover managed standby database using current logfile disconnect from session;
SQL> select database_role,OPEN_MODE,PROTECTION_MODE,PROTECTION_LEVEL,SWITCHOVER_STATUS from v$database;




===================================主（原备）-〉备（原主）切换=========================================================


1 查看节点主备关系


主（原备）
SQL> select database_role,OPEN_MODE,PROTECTION_MODE,PROTECTION_LEVEL,SWITCHOVER_STATUS from v$database;
SWITCHOVER_STATUS
--------------------
TO STANDBY


备（原主）
SQL> select database_role,OPEN_MODE,PROTECTION_MODE,PROTECTION_LEVEL,SWITCHOVER_STATUS from v$database;
SWITCHOVER_STATUS
-----------------
TO PRIMARY




2.将主（原备）库切换至备用模式
SQL> alter database commit to switchover to physical standby with session shutdown;
SQL> select database_role,OPEN_MODE,PROTECTION_MODE,PROTECTION_LEVEL,SWITCHOVER_STATUS from v$database;




3 将备（原主）库切成主库的角色


SQL> alter database recover managed standby database cancel;
SQL> alter database commit to switchover to primary;
SQL> select database_role,OPEN_MODE,PROTECTION_MODE,PROTECTION_LEVEL,SWITCHOVER_STATUS from v$database;
SQL> alter database open;
SQL> select database_role,OPEN_MODE,PROTECTION_MODE,PROTECTION_LEVEL,SWITCHOVER_STATUS from v$database;


4 将主（原备）库启动
$sqlplus “/ as sysdba”
SQL> startup 
SQL> alter database recover managed standby database using current logfile disconnect from session;
SQL> select database_role,OPEN_MODE,PROTECTION_MODE,PROTECTION_LEVEL,SWITCHOVER_STATUS from v$database;