Data Guard的搭建可以使用GC图形化安装,优缺点很明显,优点就是图形化操作,符合国人的习惯(据secooler介绍外国程序员能用图形化做的事就一定用图形做,因为boss看得懂,和国人正相反。。。),缺点就是如同Windows一样,宛如黑盒,换句话说,要时刻祈祷不要出问题,否则有时很难知道他为什么挂了。。。
Data Guard还可以使用命令行操作,正如各位所知,图形化的任何操作背后,其实都是使用的命令。OCM第七场景考试中,我也是纠结了许久,临开始前才决定使用手工方式创建DG,怕的就是图形安装的不确定性。
当然,作为练习,两种方式都熟悉下没什么不好。最近新找了两台机器资源,于是决定搭建一下DG的测试环境,以备后用。
环境准备:
OS:RH Linux
配置:2C4G,40G磁盘空间
数据库:11.2.0.4
主库SID:BEIJING,单实例
备库SID:GUOAN,单实例
1.主库准备工作
编辑listener.ora,配置主库静态监听:
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = BEIJING)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0.4)
(SID_NAME = BEIJING)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = bill02)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
ADR_BASE_LISTENER = /u01/app/oracle
编辑tnsnames.ora,配置备库连接串:
GUOAN =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = bill01)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = GUOAN)
)
)
设置force_logging参数:
SYS@BEIJING> alter database force logging;
查看是否开启归档:
SYS@BEIJING> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 6
Next log sequence to archive 8
Current log sequence 8
若未开启,将库置于mount状态,执行:
alter database archivelog;
确认shared_servers、max_shared_servers、dispatchers和max_dispatchers参数是否置空。(此步骤为了防止创建过程出错,实际验证不设置亦可)。
查看主库日志组:
SYS@BEIJING> select group#, member from v$logfile;
GROUP# MEMBER
---------- --------------------------------------------------
1 /u01/app/oracle/oradata/BEIJING/redo01.log
2 /u01/app/oracle/oradata/BEIJING/redo02.log
3 /u01/app/oracle/oradata/BEIJING/redo03.log
一共有三组,再增加四组standby日志:
SYS@BEIJING> alter database add standby logfile '/u01/app/oracle/oradata/BEIJING/redo04.log' size 100M;
SYS@BEIJING> alter database add standby logfile '/u01/app/oracle/oradata/BEIJING/redo05.log' size 100M;
SYS@BEIJING> alter database add standby logfile '/u01/app/oracle/oradata/BEIJING/redo06.log' size 100M;
SYS@BEIJING> alter database add standby logfile '/u01/app/oracle/oradata/BEIJING/redo07.log' size 100M;
SYS@BEIJING> select group#, member, type from v$logfile;
GROUP# MEMBER TYPE
---------- ---------------------------------------- -------
1 /u01/app/oracle/oradata/BEIJING/redo01.log ONLINE
2 /u01/app/oracle/oradata/BEIJING/redo02.log ONLINE
3 /u01/app/oracle/oradata/BEIJING/redo03.log ONLINE
4 /u01/app/oracle/oradata/BEIJING/redo04.log STANDBY
5 /u01/app/oracle/oradata/BEIJING/redo05.log STANDBY
6 /u01/app/oracle/oradata/BEIJING/redo06.log STANDBY
7 /u01/app/oracle/oradata/BEIJING/redo07.log STANDBY
编辑pfile文件,
DB_NAME=BEIJING
DB_UNIQUE_NAME=BEIJING
LOG_ARCHIVE_CONFIG='DG_CONFIG=(BEIJING,GUOAN)’ --顺序填写主备库
LOG_ARCHIVE_DEST_1=
'LOCATION=/home/oracle/flash --location表示本机
VALID_FOR=(ALL_LOGFILES,ALL_ROLES) --填写主库归档路径
DB_UNIQUE_NAME=BEIJING'
LOG_ARCHIVE_DEST_2=
'SERVICE=GUOAN ASYNC --service表示另一台机器
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
DB_UNIQUE_NAME=GUOAN’ --填写备库DB名称
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=4 --最大归档进程数官方文档默认30,此处改为4
FAL_SERVER=GUOAN --为了切换后主备角色互换使用,Fetch Archived Log缩写
DB_FILE_NAME_CONVERT='GUOAN',’BEIJING’ --数据文件目录结构对应关系
LOG_FILE_NAME_CONVERT='GUOAN',’BEIJING’ --日志文件目录结构对应关系
STANDBY_FILE_MANAGEMENT=AUTO --主库数据文件修改,备库可以选择自动或手工管理
将主库已经改好的参数文件和密码文件传至备库dbs路径下:
[oracle@bill02 dbs]$ scp initBEIJING.ora oracle@bill01:/u01/app/oracle/product/11.2.0.4/dbs/initGUOAN.ora
[oracle@bill02 dbs]$ scp orapwBEIJING oracle@bill01:/u01/app/oracle/product/11.2.0.4/dbs/orapwGUOAN
创建主库spfile,并启动:
SYS@BEIJING> create spfile from pfile;
SYS@BEIJING> startup;
2.备库准备工作
编辑tnsnames.ora文件,创建主库连接串:
BEIJING =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = bill02)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = BEIJING)
)
)
创建备库监听:略
编辑备库pfile文件,
db_name=‘BEIJING’ --注意此处,主备库的db_name参数要一致
memory_target=1G
processes = 150
audit_file_dest='/u01/app/oracle/admin/guoan/adump'
audit_trail ='db'
db_block_size=8192
db_domain=''
db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'
db_recovery_file_dest_size=2G
diagnostic_dest='/u01/app/oracle'
dispatchers='(PROTOCOL=TCP) (SERVICE=GUOANXDB)'
open_cursors=300
remote_login_passwordfile='EXCLUSIVE'
undo_tablespace='UNDOTBS'
control_files = (/u01/app/oracle/oradata/GUOAN/control01.ctl, /u01/app/oracle/oradata/GUOAN/control02.ctl)
compatible ='11.2.0'
DB_UNIQUE_NAME=GUOAN
LOG_ARCHIVE_CONFIG='DG_CONFIG=(GUOAN,BEIJING)'
LOG_ARCHIVE_DEST_1=
'LOCATION=/home/oracle/flash
VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
DB_UNIQUE_NAME=GUOAN'
LOG_ARCHIVE_DEST_2=
'SERVICE=BEIJING ASYNC
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
DB_UNIQUE_NAME=BEIJING'
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=4
FAL_SERVER=BEIJING
DB_FILE_NAME_CONVERT='BEIJING','GUOAN'
LOG_FILE_NAME_CONVERT='BEIJING','GUOAN'
STANDBY_FILE_MANAGEMENT=AUTO
可以采用替换的方式来修改备库pfile文件:
(1) 将BEIJING替换为中间变量TMP_BEIJING;
(2) 将GUOAN替换为BEIJING;
(3) 将TMP_BEIJING替换为GUOAN;
创建spfile,启动备库:
SYS@GUOAN> create spfile from pfile;
SYS@GUOAN> startup
ORACLE instance started.
Total System Global Area 1068937216 bytes
Fixed Size 2260088 bytes
Variable Size 671089544 bytes
Database Buffers 390070272 bytes
Redo Buffers 5517312 bytes
ORA-00205: error in identifying control file, check alert log for more info
此处有报错,可以暂时忽略,因为备库还未有控制文件。
3.主备库文件传输
此处我们采用RMAN来将主库文件传至备库:
oracle@bill02 dbs]$ rman target / auxiliary sys/oracle@guoan
Recovery Manager: Release 11.2.0.4.0 - Production on Fri Jul 29 09:46:22 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: BEIJING (DBID=882855131)
connected to auxiliary database: BEIJING (not mounted)
执行复制:
RMAN> duplicate target database for standby from active database;
Starting Duplicate Db at 29-JUL-16
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=10 device type=DISK
contents of Memory Script:
{
backup as copy reuse
targetfile '/u01/app/oracle/product/11.2.0.4/dbs/orapwBEIJING' auxiliary format
'/u01/app/oracle/product/11.2.0.4/dbs/orapwGUOAN' ;
}
executing Memory Script
Starting backup at 29-JUL-16
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=19 device type=DISK
Finished backup at 29-JUL-16
contents of Memory Script:
{
backup as copy current controlfile for standby auxiliary format '/u01/app/oracle/oradata/GUOAN/control01.ctl';
restore clone controlfile to '/u01/app/oracle/oradata/GUOAN/control02.ctl' from
'/u01/app/oracle/oradata/GUOAN/control01.ctl';
}
executing Memory Script
Starting backup at 29-JUL-16
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
copying standby control file
output file name=/u01/app/oracle/product/11.2.0.4/dbs/snapcf_BEIJING.f tag=TAG20160729T094647 RECID=1 STAMP=918467209
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 29-JUL-16
Starting restore at 29-JUL-16
using channel ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: copied control file copy
Finished restore at 29-JUL-16
contents of Memory Script:
{
sql clone 'alter database mount standby database';
}
executing Memory Script
sql statement: alter database mount standby database
contents of Memory Script:
{
set newname for tempfile 1 to
"/u01/app/oracle/oradata/GUOAN/temp01.dbf";
switch clone tempfile all;
set newname for datafile 1 to
"/u01/app/oracle/oradata/GUOAN/system01.dbf";
set newname for datafile 2 to
"/u01/app/oracle/oradata/GUOAN/sysaux01.dbf";
set newname for datafile 3 to
"/u01/app/oracle/oradata/GUOAN/undotbs01.dbf";
set newname for datafile 4 to
"/u01/app/oracle/oradata/GUOAN/users01.dbf";
backup as copy reuse
datafile 1 auxiliary format
"/u01/app/oracle/oradata/GUOAN/system01.dbf" datafile
2 auxiliary format
"/u01/app/oracle/oradata/GUOAN/sysaux01.dbf" datafile
3 auxiliary format
"/u01/app/oracle/oradata/GUOAN/undotbs01.dbf" datafile
4 auxiliary format
"/u01/app/oracle/oradata/GUOAN/users01.dbf" ;
sql 'alter system archive log current';
}
executing Memory Script
executing command: SET NEWNAME
renamed tempfile 1 to /u01/app/oracle/oradata/GUOAN/temp01.dbf in control file
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting backup at 29-JUL-16
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=/u01/app/oracle/oradata/BEIJING/system01.dbf
output file name=/u01/app/oracle/oradata/GUOAN/system01.dbf tag=TAG20160729T094657
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=/u01/app/oracle/oradata/BEIJING/sysaux01.dbf
output file name=/u01/app/oracle/oradata/GUOAN/sysaux01.dbf tag=TAG20160729T094657
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=/u01/app/oracle/oradata/BEIJING/undotbs01.dbf
output file name=/u01/app/oracle/oradata/GUOAN/undotbs01.dbf tag=TAG20160729T094657
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=/u01/app/oracle/oradata/BEIJING/users01.dbf
output file name=/u01/app/oracle/oradata/GUOAN/users01.dbf tag=TAG20160729T094657
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
Finished backup at 29-JUL-16
sql statement: alter system archive log current
contents of Memory Script:
{
switch clone datafile all;
}
executing Memory Script
datafile 1 switched to datafile copy
input datafile copy RECID=1 STAMP=918381060 file name=/u01/app/oracle/oradata/GUOAN/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=2 STAMP=918381060 file name=/u01/app/oracle/oradata/GUOAN/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=3 STAMP=918381060 file name=/u01/app/oracle/oradata/GUOAN/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=4 STAMP=918381060 file name=/u01/app/oracle/oradata/GUOAN/users01.dbf
Finished Duplicate Db at 29-JUL-16
回显所使用的文件传输脚本和过程。
此时从备库可以使用watch监控文件传输进度:
watch ls
Every 2.0s: ls