首页 > 代码库 > 冷备搭建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