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 Thu Jul 28 09:51:41 2016 control01.ctl control02.ctl redo01.log redo02.log redo03.log redo04.log redo05.log redo06.log redo07.log sysaux01.dbf system01.dbf undotbs01.dbf users01.dbf
传输完成,可以从备库查看,此时主备库数据文件、日志文件、参数文件保持一致:
oracle@bill01 GUOAN]$ ls -rlth total 1.9G -rw-r-----. 1 oracle oinstall 501M Jul 28 09:50 system01.dbf -rw-r-----. 1 oracle oinstall 326M Jul 28 09:50 sysaux01.dbf -rw-r-----. 1 oracle oinstall 201M Jul 28 09:50 undotbs01.dbf -rw-r-----. 1 oracle oinstall 101M Jul 28 09:50 users01.dbf -rw-r-----. 1 oracle oinstall 101M Jul 28 09:51 redo01.log -rw-r-----. 1 oracle oinstall 101M Jul 28 09:51 redo02.log -rw-r-----. 1 oracle oinstall 101M Jul 28 09:51 redo03.log -rw-r-----. 1 oracle oinstall 101M Jul 28 09:51 redo04.log -rw-r-----. 1 oracle oinstall 101M Jul 28 09:51 redo05.log -rw-r-----. 1 oracle oinstall 101M Jul 28 09:51 redo06.log -rw-r-----. 1 oracle oinstall 101M Jul 28 09:51 redo07.log -rw-r-----. 1 oracle oinstall 9.1M Jul 28 09:51 control01.ctl -rw-r-----. 1 oracle oinstall 9.1M Jul 28 09:51 control02.ctl
4.验证主备库状态
此时查看备库状态,处于MOUNT,角色是物理备库:
SQL> select database_role, open_mode from v$database; DATABASE_ROLE OPEN_MODE ---------------- -------------------- PHYSICAL STANDBY MOUNTED SQL> select status from v$instance; STATUS ------------ MOUNTED
此时主库状态,处于OPEN,可读写:
SYS@BEIJING> select database_role, open_mode from v$database; DATABASE_ROLE OPEN_MODE ---------------- -------------------- PRIMARY READ WRITE SYS@BEIJING> select status from v$instance; STATUS ------------ OPEN
5.切换ADG
打开备库实时应用:
SQL> recover managed standby database using current logfile disconnect from session; Media recovery complete. SQL> recover managed standby database cancel; Media recovery complete. SQL> alter database open; SQL> select database_role, open_mode from v$database; DATABASE_ROLE OPEN_MODE ---------------- -------------------- PHYSICAL STANDBY READ ONLY
其中disconnect from session表示后台执行,类似于shell的&。可以看出现在备库处于READ ONLY状态,不再是处于MOUNT不可使用的状态,即现在可以执行只读操作了。
继续:
SQL> recover managed standby database using current logfile disconnect from session; Media recovery complete. SQL> select database_role, open_mode from v$database; DATABASE_ROLE OPEN_MODE ---------------- -------------------- PHYSICAL STANDBY READ ONLY WITH APPLY
此时数据库状态时READ ONLY WITH APPLY,即只读且实时应用日志,通常所说的ADG,Active Data Guard。
READ ONLY WITH APPLY表示备库处于READ ONLY状态的同时可以接收主库传过来的日志文件并进行恢复,以便备库可以即时查看到主库的变化。
6.切换SNAPSHOT DATABASE
执行:
SQL> alter database recover managed standby database cancel;
SQL> alter database convert to snapshot standby;
SNAPSHOT的原理是基于闪回数据原理,此时备库是可读写状态,可以用这种真实的生产环境进行一些无法在测试环境做的实验。使用完后,可以执行命令退回,SNAPSHOT期间对备库做的所有变更都会还原:
SQL> alter database convert to physical standby; SQL> alter database recover managed standby database disconnect;
7.切换主库日志
通过主库手工切换日志,来看看备库是否实时接收应用了日志,可以看出此时备库的最新日志SEQUENCE#是15号:
SYS@BEIJING> alter system archive log current; System altered. SYS@BEIJING> select sequence#, first_time, next_time from v$archived_log order by sequence#; SEQUENCE# FIRST_TIME NEXT_TIME ---------- ------------------ ------------------ 13 29-JUL-16 29-JUL-16 13 29-JUL-16 29-JUL-16 14 29-JUL-16 29-JUL-16 14 29-JUL-16 29-JUL-16 15 29-JUL-16 29-JUL-16 15 29-JUL-16 29-JUL-16
此时查看备库,已经应用15号日志:
SQL> select sequence#, first_time, next_time from v$archived_log order by sequence#; SEQUENCE# FIRST_TIME NEXT_TIME ---------- ------------------ ------------------ 12 29-JUL-16 29-JUL-16 13 29-JUL-16 29-JUL-16 14 29-JUL-16 29-JUL-16 15 29-JUL-16 29-JUL-16
8.设置归档日志删除策略
可以设置主库的归档日志应用至备库后再删除的策略,一方面保证了归档日志传输接收,另一方面可以有效控制归档日志文件的产生量大小。默认是没有任何删除策略:
[oracle@bill02 BEIJING]$ rman target / Recovery Manager: Release 11.2.0.4.0 - Production on Fri Jul 29 10:10:55 2016 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. connected to target database: BEIJING (DBID=882855131) RMAN> show all; using target database control file instead of recovery catalog RMAN configuration parameters for database with db_unique_name BEIJING are: CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default CONFIGURE BACKUP OPTIMIZATION OFF; # default CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default CONFIGURE MAXSETSIZE TO UNLIMITED; # default CONFIGURE ENCRYPTION FOR DATABASE OFF; # default CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/11.2.0.4/dbs/snapcf_BEIJING.f'; # default
执行:
RMAN> CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY; new RMAN configuration parameters: CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY; new RMAN configuration parameters are successfully stored
再次查看:
RMAN> show all; RMAN configuration parameters for database with db_unique_name BEIJING are: CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default CONFIGURE BACKUP OPTIMIZATION OFF; # default CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default CONFIGURE MAXSETSIZE TO UNLIMITED; # default CONFIGURE ENCRYPTION FOR DATABASE OFF; # default CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY; CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/11.2.0.4/dbs/snapcf_BEIJING.f'; # default
9.总结
Data Guard的手工搭建方式,如果理清思路,还是比较清晰的,我最开始做的时候,由于监听、连接串一系列问题,总是duplicate出错,但手工方式好就好在可以让你有调试排查的机会,因为每一步手工操作都有他的意义,过程可逆,若是图形操作,则相对封闭些。
另外,这里只是DG搭建的最基础方式,其实对于一套DG生产环境,还有很多预防性设置,之前参考过某些牛人的梳理,确实很详细,有些内容自己还未掌握,还需要继续学习。