首页 > 代码库 > 【测试】手工搭建DG

【测试】手工搭建DG

前言:(一)准备工作:

1.数据库要处于归档模式;

2.监听参数:local_listener 默认值为空--1521

3.关闭闪回(可能会触发数据库的bug,备库不能开闪回)

4.如果有外部表,外部表要删除。

(二)环境描述:

192.168.206.3    主库    ORA11GR2

192.168.206.4    备库    PROD

 

一,将数据库处于归档模式:

SYS@ORA11GR2>startup mount;
ORACLE instance started.

Total System Global Area  830930944 bytes
Fixed Size                  2257800 bytes
Variable Size             536874104 bytes
Database Buffers          285212672 bytes
Redo Buffers                6586368 bytes
Database mounted.
SYS@ORA11GR2>archive log list;
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            /u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch
Oldest online log sequence     2
Current log sequence           4


SYS@ORA11GR2>alter database archivelog;

Database altered.

SYS@ORA11GR2>archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch
Oldest online log sequence     3
Next log sequence to archive   5
Current log sequence           5

二,验证监听:local_listener 默认值为空--1521

SYS@ORA11GR2>show parameter local_listener;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
local_listener                       string

三,关闪回(可能会触发数据库bug,备库不能开闪回)

 

SYS@ORA11GR2>select flashback_on from v$database;

FLASHBACK_ON
------------------
NO

 

四,修改参数:

在vi initORA1GR2.ora中加入:

DB_NAME=ORA11GR2
DB_UNIQUE_NAME=ORA11GR2
LOG_ARCHIVE_CONFIG=DG_CONFIG=(ORA11GR2,PROD)
LOG_ARCHIVE_DEST_1=
 LOCATION=/u01/arch1/ORA11GR2/
  VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
  DB_UNIQUE_NAME=ORA11GR2
LOG_ARCHIVE_DEST_2=
 SERVICE=PROD ASYNC
  VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
  DB_UNIQUE_NAME=PROD
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
LOG_ARCHIVE_MAX_PROCESSES=30
FAL_SERVER=PROD
DB_FILE_NAME_CONVERT=PROD,ORA11GR2
LOG_FILE_NAME_CONVERT=
 /u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch/PROD/,/u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch/ORA11GR2/
STANDBY_FILE_MANAGEMENT=AUTO

五,开启force logging

SYS@ORA11GR2>select FORCE_LOGGING from v$database;

FOR
---
NO

SYS@ORA11GR2>alter database force logging;

Database altered.

六,查看日志文件及大小,并增加standby logfile:

SYS@ORA11GR2>select group#,bytes /1024/1024 M from v$log;

    GROUP#          M
---------- ----------
         1         50
         2         50
         3         50

SYS@ORA11GR2>select GROUP#,MEMBER from v$logfile;

    GROUP# MEMBER
---------- --------------------------------------------------
         3 /u01/app/oracle/oradata/ORA11GR2/redo03.log
         2 /u01/app/oracle/oradata/ORA11GR2/redo02.log
         1 /u01/app/oracle/oradata/ORA11GR2/redo01.log

增加日志文件:
SYS@ORA11GR2>alter database add standby logfile group 4 (/u01/app/oracle/oradata/ORA11GR2/redo401_std.log,/u01/app/oracle/oradata/ORA11GR2/redo402_std.log) size 50M;

Database altered.

SYS@ORA11GR2>c/4/5
  1* alter database add standby logfile group 5 (/u01/app/oracle/oradata/ORA11GR2/redo401_std.log,/u01/app/oracle/oradata/ORA11GR2/redo402_std.log) size 50M
SYS@ORA11GR2>c/4/5
  1* alter database add standby logfile group 5 (/u01/app/oracle/oradata/ORA11GR2/redo501_std.log,/u01/app/oracle/oradata/ORA11GR2/redo402_std.log) size 50M
SYS@ORA11GR2>c/4/5
  1* alter database add standby logfile group 5 (/u01/app/oracle/oradata/ORA11GR2/redo501_std.log,/u01/app/oracle/oradata/ORA11GR2/redo502_std.log) size 50M
SYS@ORA11GR2>/

Database altered.

SYS@ORA11GR2>c/5/6
  1* alter database add standby logfile group 6 (/u01/app/oracle/oradata/ORA11GR2/redo501_std.log,/u01/app/oracle/oradata/ORA11GR2/redo502_std.log) size 50M
SYS@ORA11GR2>c/5/6
  1* alter database add standby logfile group 6 (/u01/app/oracle/oradata/ORA11GR2/redo601_std.log,/u01/app/oracle/oradata/ORA11GR2/redo502_std.log) size 50M
SYS@ORA11GR2>c/5/6
  1* alter database add standby logfile group 6 (/u01/app/oracle/oradata/ORA11GR2/redo601_std.log,/u01/app/oracle/oradata/ORA11GR2/redo602_std.log) size 50M
SYS@ORA11GR2>/

Database altered.

SYS@ORA11GR2>c/6/7
  1* alter database add standby logfile group 7 (/u01/app/oracle/oradata/ORA11GR2/redo601_std.log,/u01/app/oracle/oradata/ORA11GR2/redo602_std.log) size 50M
SYS@ORA11GR2>c/6/7
  1* alter database add standby logfile group 7 (/u01/app/oracle/oradata/ORA11GR2/redo701_std.log,/u01/app/oracle/oradata/ORA11GR2/redo602_std.log) size 50M
SYS@ORA11GR2>c/6/7
  1* alter database add standby logfile group 7 (/u01/app/oracle/oradata/ORA11GR2/redo701_std.log,/u01/app/oracle/oradata/ORA11GR2/redo702_std.log) size 50M
SYS@ORA11GR2>/

Database altered.

SYS@ORA11GR2>select GROUP#,MEMBER from v$logfile;

    GROUP# MEMBER
---------- --------------------------------------------------
         3 /u01/app/oracle/oradata/ORA11GR2/redo03.log
         2 /u01/app/oracle/oradata/ORA11GR2/redo02.log
         1 /u01/app/oracle/oradata/ORA11GR2/redo01.log
         4 /u01/app/oracle/oradata/ORA11GR2/redo401_std.log
         4 /u01/app/oracle/oradata/ORA11GR2/redo402_std.log
         5 /u01/app/oracle/oradata/ORA11GR2/redo501_std.log
         5 /u01/app/oracle/oradata/ORA11GR2/redo502_std.log
         6 /u01/app/oracle/oradata/ORA11GR2/redo601_std.log
         6 /u01/app/oracle/oradata/ORA11GR2/redo602_std.log
         7 /u01/app/oracle/oradata/ORA11GR2/redo701_std.log
         7 /u01/app/oracle/oradata/ORA11GR2/redo702_std.log

11 rows selected.

七,将主库参数文件和口令文件传给备库:

[oracle@host03 dbs]$ scp initPROD.ora orapwPROD 192.168.206.4:/u01/app/oracle/product/11.2.0/dbhome_1/dbs
oracle@192.168.206.4s password: 
initPROD.ora                                                                                                                          100% 1559     1.5KB/s   00:00    
orapwPROD                                                                                                                             100% 1536     1.5KB/s   00:00   

八,修改备库的参数文件:

[oracle@host04 dbs]$ ls
hc_ORA11GR2.dat  init.ora  initPROD.ora  lkORA11GR2  orapwORA11GR2  orapwPROD  spfileORA11GR2.ora
[oracle@host04 dbs]$ vi initPROD.ora 【加入】


DB_NAME=ORA11GR2
DB_UNIQUE_NAME=PROD
LOG_ARCHIVE_CONFIG=DG_CONFIG=(PROD,ORA11GR2)
LOG_ARCHIVE_DEST_1=
 LOCATION=/u01/arch1/PROD/
  VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
  DB_UNIQUE_NAME=PROD
LOG_ARCHIVE_DEST_2=
 SERVICE=ORA11GR2 ASYNC
  VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
  DB_UNIQUE_NAME=ORA11GR2
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
LOG_ARCHIVE_MAX_PROCESSES=30
FAL_SERVER=ORA11GR2
DB_FILE_NAME_CONVERT=ORA11GR2,PROD
LOG_FILE_NAME_CONVERT=
 /u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch/ORA11GR2/,/u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch/PROD/
STANDBY_FILE_MANAGEMENT=AUTO

九,创建参数中涉及到的目录:

[oracle@host04 dbs]$ mkdir -p /u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch/ORA11GR2/
[oracle@host04 dbs]$ mkdir -p /u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch/PROD/
[oracle@host04 dbs]$ mkdir -p /u01/arch1/PROD/
[oracle@host04 dbs]$ mkdir -p /u01/app/oracle/oradata/PROD/
[oracle@host04 dbs]$ mkdir -p /u01/app/oracle/admin/PROD/adump

此时将备库起到nomount:
SQL> startup nomount
ORACLE instance started.

Total System Global Area  830930944 bytes
Fixed Size                  2257800 bytes
Variable Size             536874104 bytes
Database Buffers          285212672 bytes
Redo Buffers                6586368 bytes

十,配置网络监听:

①主库:
[oracle@host03 admin]$ vi listener.ora 

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

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

ADR_BASE_LISTENER = /u01/app/oracle



SID_LIST_LISTENER=
   (SID_LIST=
       (SID_DESC=
          (GLOBAL_DBNAME=ORA11GR2)
          (ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1)
         (SID_NAME=ORA11GR2)
         )
        )


②主库客户端监听:
[oracle@host03 admin]$ vi tnsnames.ora

PROD=
    (DESCRIPTION=
       (ADDRESS=(PROTOCOL = TCP)(HOST = 192.168.206.4)(PORT = 1521)
          (CONNECT_DATA=
     (SERVER=DEDICATED)
     (SERVICE_NAME=PROD)
    )
  )
)
③备库监听:
[oracle@host04 admin]$ vi listener.ora 

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

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

ADR_BASE_LISTENER = /u01/app/oracle




SID_LIST_LISTENER=
   (SID_LIST=
       (SID_DESC=
          (GLOBAL_DBNAME=PROD)
          (ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1)
         (SID_NAME=PROD)
         )
        )
④备库客户端监听:
[oracle@host04 admin]$ vi tnsnames.ora

ORA11GR2=
  (DESCRIPTION=
     (ADDRESS=(PROTOCOL=TCP)(HOST=192.168.206.3)(PORT=1521))
    (CONNECT_DATA=
     (SERVER=DEDICATED)
     (SERVICE_NAME=ORA11GR2)
    )
  )
查看主库监听状态:
LSNRCTL> status
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=host03)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date                20-OCT-2016 00:52:32
Uptime                    0 days 0 hr. 1 min. 3 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/host03/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=host03)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "ORA11GR2" has 2 instance(s).
  Instance "ORA11GR2", status UNKNOWN, has 1 handler(s) for this service...
  Instance "ORA11GR2", status READY, has 1 handler(s) for this service...
Service "ORA11GR2XDB" has 1 instance(s).
  Instance "ORA11GR2", status READY, has 1 handler(s) for this service...
The command completed successfully


查看备库监听状态:
LSNRCTL> status
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=host04)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date                20-OCT-2016 00:53:12
Uptime                    0 days 0 hr. 0 min. 29 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/host04/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=host04)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "PROD" has 1 instance(s).
  Instance "PROD", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully


主库验证客户端监听:
[oracle@host03 admin]$ tnsping PROD

TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 20-OCT-2016 01:04:16

Copyright (c) 1997, 2013, Oracle.  All rights reserved.

Used parameter files:


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION= (ADDRESS=(PROTOCOL=TCP)(HOST=192.168.206.4)(PORT=1521)) (CONNECT_DATA= (SERVER=DEDICATED) (SERVICE_NAME=PROD)))
OK (10 msec)
[oracle@host03 admin]$ sqlplus sys/oracle@PROD as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Thu Oct 20 01:04:33 2016

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


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

SYS@PROD>
备库验证客户端监听:
[oracle@host04 admin]$ tnsping ORA11GR2

TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 20-OCT-2016 00:57:38

Copyright (c) 1997, 2013, Oracle.  All rights reserved.

Used parameter files:


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION= (ADDRESS=(PROTOCOL=TCP)(HOST=192.168.206.3)(PORT=1521)) (CONNECT_DATA= (SERVER=DEDICATED) (SERVICE_NAME=ORA11GR2)))
OK (50 msec)
[oracle@host04 admin]$ sqlplus sys/oracle@ORA11GR2 as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Thu Oct 20 01:01:18 2016

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


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

十一,复制主库生成备库:

[oracle@host03 dbs]$ rman target / auxiliary sys/oracle@PROD

Recovery Manager: Release 11.2.0.4.0 - Production on Thu Oct 20 01:07:15 2016

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORA11GR2 (DBID=242173609)
connected to auxiliary database: ORA11GR2 (not mounted)

RMAN> duplicate target database for standby from active database;

 

【测试】手工搭建DG