首页 > 代码库 > 冷备搭建DG

冷备搭建DG

1.主库开启归档

   SQL> archive log list;(查询当前归档状态)

   SQL> shutdown immediate;

   SQL> startup mount;(启动到mount状态)

   SQL> archive log list;(开启归档)

2.开启强制写日志

   SQL> alter database force logging;

3..关闭闪回

   SQL> alter database flashback off;

4.创建standby logfile.(原则是最大日志组数+1,大小和日志成员大小一致

  SQL> select group#,member from v$logfile;(查询当前日志组数)

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

 

SQL> select group#,bytes/1024/1024 as size_m from v$log;(查询日志成员大小)

GROUP#     SIZE_M
------ ----------
     1         50
     3         50
     2         50

SQL> alter database add standby logfile group 4 (‘/u01/app/oracle/oradata/ENMO/redo04.log‘) size 50m;

Database altered.

SQL> alter database add standby logfile group 5 (‘/u01/app/oracle/oradata/ENMO/redo05.log‘) size 50m;

Database altered.

SQL> alter database add standby logfile group 6 (‘/u01/app/oracle/oradata/ENMO/redo06.log‘) size 50m;

Database altered.

SQL> alter database add standby logfile group 7 (‘/u01/app/oracle/oradata/ENMO/redo07.log‘) size 50m;

Database altered.

5.生成文本文件,并修改(spfile是二进制文件,pfile是文本文件,文本文件可编辑)

   SQL> create pfile from spfile;

   [oracle@host06 ~]$ cd $ORACLE_HOME/dbs

   [oracle@host06 dbs]$ vi initENMO.ora

   追加如下内容:(可在官方文档里找(MARK BOOK LIST-->DAT-->Data Guard Concepts and Administration-->Creating a Physical Standby Database-->Example 3-1 Primary Database: Primary Role Initialization Parameters

db_unique_name=PROD
log_archive_format=%t_%s_%r.arc
log_archive_config=‘DG_CONFIG=(DG_CONFIG=(PROD,SBDB)‘
LOG_ARCHIVE_DEST_1=‘LOCATION=/u01/app/oracle/FRA
VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=PROD‘
LOG_ARCHIVE_DEST_2=‘SERVICE=ENMO70           LGWR                   SYNC
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=SBDB‘
log_archive_dest_state_1=ENABLE
log_archive_dest_state_2=ENABLE
LOG_ARCHIVE_MAX_PROCESSES=4
##Parameters which using for switch over from Primary to Standby.

fal_server=ENMO70
standby_file_management=AUTO
db_file_name_convert=‘ENMO‘,‘ENMO‘
log_file_name_convert=‘ENMO‘,‘ENMO‘

6.关闭数据库,生成最新的spfile,并启库

SQL>shutdown immediate;

SQL>create spfile from pfile;

SQL>startup

SQL> show parameter name;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
cell_offloadgroup_name               string
db_file_name_convert                 string      ENMO, ENMO
db_name                              string      ENMO
db_unique_name                       string      PROD
global_names                         boolean     FALSE
instance_name                        string      ENMO
lock_name_space                      string
log_file_name_convert                string      ENMO, ENMO
processor_group_name                 string
service_names                        string      PROD

7.配置主库静态监听listener.ora[官方文档位置:Master Book List-->NET-->Net Service Reference-->Local Naming Parameters(listener.ora)-->Example7-1 listener.ora file

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

ADR_BASE_LISTENER = /u01/app/oracle
SID_LIST_LISTENER =
 (sid_list=
   (sid_desc=
    (sid_name=ENMO)
    (oracle_home= /u01/app/oracle/product/11.2.0/dbhome_1)
    (global_dbname=SBDB)))

 

 

8..配置主库tnsnames【官方文档位置Master Book List-->NET-->Net Service Reference-->Local Naming Parameters(tnsnames.ora)-->Example 6-2 Net Service Name with Multiple Connect Descriptors in tnsnames.ora】

ENMO60=         
 (DESCRIPTION_LIST=
  (DESCRIPTION=
   (ADDRESS=(protocol = TCP(HOST=192.168.100.60)(PORT = 1521))
 )
 ) 
 (CONNECT_DATA=http://www.mamicode.com/
   (SERVER = DEDICATED)
   (SERVICE_NAME=PROD)
  )
  )
ENMO70=
 (DESCRIPTION_LIST=
  (DESCRIPTION=
   (ADDRESS=(protocol = TCP(HOST=192.168.100.70)(PORT = 1521))
 )
 )
 (CONNECT_DATA=http://www.mamicode.com/
   (SERVER = DEDICATED)
   (SERVICE_NAME=SBDB)

 

9.主库拷贝相关文件到备库

[oracle@host06 dbs]$ cd $ORACLE_HOME/network/admin(切换到主库参数文件和密码文件所在位置)

[oracle@host06 dbs]$ scp orapwENMO oracle@192.168.100.70:/u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwENMO(拷贝密码文件到备库)

[oracle@host06 dbs]$ scp initENMO.ora oracle@192.168.100.70:/u01/app/oracle/product/11.2.0/dbhome_1/dbs/initENMO.ora(拷贝参数文件到备库)

[oracle@host06 dbs]$ scp $ORACLE_HOME/network/admin/tnsnames.ora oracle@192.168.100.70:$ORACLE_HOME/network/admin/tnsnames.ora(拷贝监听到备库)

10.备库创建静态监听并启动(可参考主库的)

11.把从主库拷贝过来的pfile参数文件修改一下

12.备库按照参数文件创建目录

13.备库通过pfile生成spfile,启动到nomount状态

14.以下内容是与rman duplicate方式最主要区别:

  停止主库,拷贝数据文件,日志文件

[oracle@host06 ~]$ cd /u01/app/oracle/oradata/ENMO

[oracle@host06 ENMO]$ ls
control01.ctl  redo01.log  redo04.log  redo07.log    temp01.dbf
control02.ctl  redo02.log  redo05.log  sysaux01.dbf  undotbs01.dbf
example01.dbf  redo03.log  redo06.log  system01.dbf  users01.dbf

 

[oracle@host06 ENMO]$ tar -czvf /home/oracle/prod1.tar.gz *

(打个tar包把主库oradata下的数据文件日志文件控制文件等打个tar包到 /home/oracle下并创建prod1.tar.gz文件)

 

[oracle@host06 ENMO]$ scp /home/oracle/prod1.tar.gz 192.168.100.70:/u01/app/oracle/oradata/ENMO/

(拷贝tar包到备库/u01/app/oracle/oradata/ENMO下)

到备库下把从主库拷贝的控制文件删除,因为结构不一样,所以控制文件不能共用

[oracle@host07 ENMO]$ cd /u01/app/oracle/oradata/ENMO

[oracle@host07 ENMO]$ ls
control01.ctl  redo01.log  redo05.log    system01.dbf
control02.ctl  redo02.log  redo06.log    temp01.dbf
example01.dbf  redo03.log  redo07.log    undotbs01.dbf
prod1.tar.gz   redo04.log  sysaux01.dbf  users01.dbf

[oracle@host07 ENMO]$ rm *.ctl

 

把主库启动到mount状态,创建standby控制文件

SQL> startup mount;

SQL> alter database create standby controlfile as ‘/home/oracle/control_standby.ctl‘;

[oracle@host06 ~]$ scp control_standby.ctl 192.168.100.70:/home/oracle/

  备库在standby上进行恢复controlfile并open;

 

[oracle@host07 ENMO]$ rman target /

 

Recovery Manager: Release 11.2.0.4.0 - Production on Tue May 2 11:14:10 2017

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

connected to target database: ENMO (not mounted)

 

RMAN> restore controlfile from ‘/home/oracle/control_standby.ctl‘;

 

Starting restore at 02-MAY-17
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=20 device type=DISK

channel ORA_DISK_1: copied control file copy
output file name=/u01/app/oracle/oradata/ENMO/control01.ctl
output file name=/u01/app/oracle/oradata/ENMO/control02.ctl
Finished restore at 02-MAY-17

SQL> shutdown immediate;
ORA-01507: database not mounted


ORACLE instance shut down.
SQL> create spfile from pfile;

File created.

SQL> startup nomount;
ORACLE instance started.

Total System Global Area  830930944 bytes
Fixed Size                  2257800 bytes
Variable Size             536874104 bytes
Database Buffers          289406976 bytes
Redo Buffers                2392064 bytes
SQL> alter database mount;

Database altered.

SQL> alter database open;

Database altered.

SQL> recover managed standby database disconnect from session;
Media recovery complete.

 

 

 

 

冷备搭建DG