11g Active DataGuard部署、运维小记
自从11G之后的Active DataGuard新特性的推出，早先的Local Standby基本已经没竞争力。项目中需要实现一点写多点读，双活等这样的功能，在ADG与OGG间几经权衡后最终选用了ADG。
1 环境
pri:
ip:192.168.10.150 os：rhel6.4 64bit oracle版本：11.2.0.4.6 64bit sid：gxd db_name：gxd db_unique_name:oradb1
stdby:
ip:192.168.10.151 os：rhel6.4 64bit oracle版本：11.2.0.4.6 64bit sid：gxd db_name：gxd db_unique_name:oradb2
备库安装好oracle软件即可，不用建库，但需要把trace,bdump,udump等这些目录建好。
2 配置主从端的listener.ora和tnsnames.ora
pri:
listener.ora:
[oracle@oradb1 admin]$ cat listener.ora
# listener.ora Network Configuration File: /oracle/app/oracle/product/11.2.0/network/admin/listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(ORACLE_HOME = /oracle/app/oracle/product/11.2.0)
(SID_NAME = gxd)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.10.150)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
ADR_BASE_LISTENER = /oracle/app/oracle
tnsnames.ora:
[oracle@oradb1 admin]$ cat tnsnames.ora
ORADB1 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.10.150)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = gxd)
)
)
ORADB2 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.10.151)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = gxd)
)
)
LOCAL_ORADB1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.10.150)(PORT = 1521))
)
stdby:
listener:
[oracle@oradb2 admin]$ cat listener.ora
# listener.ora Network Configuration File: /oracle/app/oracle/product/11.2.0/network/admin/listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(ORACLE_HOME = /oracle/app/oracle/product/11.2.0)
(SID_NAME = gxd)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = oradb2)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
ADR_BASE_LISTENER = /oracle/app/oracle
tnsnames.ora
[oracle@oradb2 admin]$ cat tns*.ora
ORADB1 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.10.150)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = gxd)
)
)
ORADB2 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.10.151)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = gxd)
)
)
LOCAL_ORADB2 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.10.151)(PORT = 1521))
)
3 修改主库参数
pri:
修改ADG相关参数，数据库要置于归档模式
[oracle@oradb1 ~]$ sqlplus / as sysdba SQL> SQL> ALTER DATABASE FORCE LOGGING; Database altered. SQL> alter system set db_unique_name='oradb1' scope=spfile; System altered. SQL> alter system set log_archive_config='DG_CONFIG=(oradb1,oradb2)'; System altered. SQL> alter system set log_archive_dest_1='LOCATION=/arch/arch1 valid_for=(all_logfiles,primary_role) db_unique_name=oradb1' scope=spfile; System altered. SQL> alter system set log_archive_dest_2='SERVICE=oradb2 lgwr async valid_for=(online_logfile,primary_role) db_unique_name=oradb2'; System altered. SQL> alter system set log_archive_dest_3='LOCATION=/arch/stdbylog valid_for=(standby_logfile,standby_role) db_unique_name=oradb1' scope=spfile; System altered. SQL> alter system set fal_client='oradb1'; System altered. SQL> alter system set fal_server='oradb2'; System altered. SQL> alter system set standby_file_management=auto; System altered. SQL> alter database add standby logfile group 5 '/oracle/oradata/ora_stdby01.dbf' size 510M; Database altered. SQL> alter database add standby logfile group 6 '/oracle/oradata/ora_stdby02.dbf' size 510M; Database altered. SQL> alter database add standby logfile group 7 '/oracle/oradata/ora_stdby03.dbf' size 510M; Database altered. SQL> alter database add standby logfile group 8 '/oracle/oradata/ora_stdby04.dbf' size 510M; Database altered. SQL> alter system set local_listener='local_oradb1' scope=both; System altered. SQL> alter system set log_file_name_convert='/oracle/oradata/','/oracle/oradata/' scope=spfile; System altered. SQL> alter system set db_file_name_convert='/oracle/oradata/','/oracle/oradata/' scope=spfile; System altered.
生成pfile传到备库，以用于生成备库参数
SQL> create pfile='/oracle/inityang.ora' from spfile; File created.
4 修改备库参数
stdby:
copy 主库inityang.ora到备库,修改后如下：
[oracle@oradb2 ~]$ cat inityang.ora gxd.__oracle_base='/oracle/app/oracle'#ORACLE_BASE set from environment *.audit_file_dest='/oracle/app/oracle/admin/gxd/adump' *.audit_trail='db' *.compatible='11.2.0.4.0' *.control_files='/oracle/oradata/ora_ctrlfile01.dbf','/oracle/oradata/ora_ctrlfile02.dbf','/oracle/oradata/ora_ctrlfile03.dbf' *.db_block_size=8192 *.db_cache_size=19327352832 *.db_domain='' *.db_file_name_convert='/oracle/oradata/','/oracle/oradata/' *.db_name='gxd' *.db_unique_name='oradb2' *.diagnostic_dest='/oracle/app/oracle' *.dispatchers='(PROTOCOL=TCP) (SERVICE=gxdXDB)' *.fal_client='oradb2' *.fal_server='oradb1' *.java_pool_size=209715200 *.large_pool_size=524288000 *.local_listener='local_oradb2' *.log_archive_config='DG_CONFIG=(oradb1,oradb2)' *.log_archive_dest_1='LOCATION=/arch/arch1 valid_for=(all_logfiles,primary_role) db_unique_name=oradb2' *.log_archive_dest_2='SERVICE=oradb1 lgwr sync valid_for=(online_logfile,primary_role) db_unique_name=oradb1' *.log_archive_dest_3='LOCATION=/arch/stdbylog valid_for=(standby_logfile,standby_role) db_unique_name=oradb2' *.log_file_name_convert='/oracle/oradata/','/oracle/oradata/' *.open_cursors=300 *.pga_aggregate_target=2097152000 *.processes=5000 *.remote_login_passwordfile='EXCLUSIVE' *.sessions=5505 *.shared_pool_size=9126805504 *.standby_file_management='AUTO' *.undo_tablespace='UNDOTBS1'
注意DB_UNIQUE_NAME、CONTROL_FILES、DB_FILE_NAME_CONVERT、LOG_FILE_NAME_CONVERT、LOG_ARCHIVE_DEST_*、FAI_CLIENT、FAL_SERVER几个参数。
5 生成备份的spfile
stdby:
生成后启动到nmount
[oracle@oradb2 ~]$ sqlplus /nolog SQL*Plus: Release 11.2.0.4.0 Production on Sat Aug 22 21:08:45 2015 Copyright (c) 1982, 2013, Oracle. All rights reserved. SQL> connect / as sysdba Connected to an idle instance. SQL> create spfile from pfile='/oracle/inityang.ora'; File created. SQL> startup nomount ORACLE instance started. Total System Global Area 2.9531E+10 bytes Fixed Size 2267424 bytes Variable Size 1.0133E+10 bytes Database Buffers 1.9327E+10 bytes Redo Buffers 67526656 bytes SQL>
6 duplicate pri to stdby
pri:
[oracle@oradb1 ~]$ rman target sys/gxd_2345@oradb1 auxiliary sys/gxd_2345@oradb2
Recovery Manager: Release 11.2.0.4.0 - Production on Sun Aug 23 05:00:04 2015
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: GXD (DBID=3061110533)
connected to auxiliary database: GXD (not mounted)
RMAN> duplicate target database for standby nofilenamecheck from active database;
Starting Duplicate Db at 20150823050043
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=4726 device type=DISK
contents of Memory Script:
{
backup as copy reuse
targetfile '/oracle/app/oracle/product/11.2.0/dbs/orapwgxd' auxiliary format
'/oracle/app/oracle/product/11.2.0/dbs/orapwgxd' ;
}
executing Memory Script
Starting backup at 20150823050044
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=7183 device type=DISK
Finished backup at 20150823050045
contents of Memory Script:
{
backup as copy current controlfile for standby auxiliary format '/oracle/oradata/ora_ctrlfile01.dbf';
restore clone primary controlfile to '/oracle/oradata/ora_ctrlfile02.dbf' from
'/oracle/oradata/ora_ctrlfile01.dbf';
restore clone primary controlfile to '/oracle/oradata/ora_ctrlfile03.dbf' from
'/oracle/oradata/ora_ctrlfile01.dbf';
}
executing Memory Script
Starting backup at 20150823050045
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
copying standby control file
output file name=/oracle/app/oracle/product/11.2.0/dbs/snapcf_gxd.f tag=TAG20150823T050045 RECID=3 STAMP=888469245
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 20150823050048
Starting restore at 20150823050048
using channel ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: copied control file copy
Finished restore at 20150823050049
Starting restore at 20150823050049
using channel ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: copied control file copy
Finished restore at 20150823050051
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
"/oracle/oradata/ora_temp01.dbf";
switch clone tempfile all;
set newname for datafile 1 to
"/oracle/oradata/ora_system01.dbf";
set newname for datafile 2 to
"/oracle/oradata/ora_sysaux01.dbf";
set newname for datafile 3 to
"/oracle/oradata/ora_rbs01.dbf";
set newname for datafile 4 to
"/oracle/oradata/ora_user01.dbf";
backup as copy reuse
datafile 1 auxiliary format
"/oracle/oradata/ora_system01.dbf" datafile
2 auxiliary format
"/oracle/oradata/ora_sysaux01.dbf" datafile
3 auxiliary format
"/oracle/oradata/ora_rbs01.dbf" datafile
4 auxiliary format
"/oracle/oradata/ora_user01.dbf" ;
sql 'alter system archive log current';
}
executing Memory Script
executing command: SET NEWNAME
renamed tempfile 1 to /oracle/oradata/ora_temp01.dbf in control file
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting backup at 20150823050058
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=/oracle/oradata/ora_rbs01.dbf
output file name=/oracle/oradata/ora_rbs01.dbf tag=TAG20150823T050058
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:45:45
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=/oracle/oradata/ora_system01.dbf
output file name=/oracle/oradata/ora_system01.dbf tag=TAG20150823T050058
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:24:25
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=/oracle/oradata/ora_sysaux01.dbf
output file name=/oracle/oradata/ora_sysaux01.dbf tag=TAG20150823T050058
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:24:25
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=/oracle/oradata/ora_user01.dbf
output file name=/oracle/oradata/ora_user01.dbf tag=TAG20150823T050058
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:55
Finished backup at 20150823063629
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=3 STAMP=888474989 file name=/oracle/oradata/ora_system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=4 STAMP=888474989 file name=/oracle/oradata/ora_sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=5 STAMP=888474989 file name=/oracle/oradata/ora_rbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=6 STAMP=888474989 file name=/oracle/oradata/ora_user01.dbf
Finished Duplicate Db at 20150823063634
RMAN>
RMAN>
7 启动从库到open和实时日志应用状态
stdby:
SQL> select status from v$instance; STATUS ------------ MOUNTED SQL> alter database open; Database altered. SQL> alter database recover managed standby database using current logfile disconnect from session; Database altered. SQL> SQL> select open_mode,database_role,db_unique_name from v$database; OPEN_MODE DATABASE_ROLE DB_UNIQUE_NAME -------------------- ---------------- ------------------------------ READ ONLY WITH APPLY PHYSICAL STANDBY oradb2
8 switch over
pri:
SQL> alter database commit to switchover to physical standby; Database altered. SQL> shutdown immediate ORA-01012: not logged on SQL> exit [oracle@oradb2 ~]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.4.0 Production on Sun Aug 23 19:59:02 2015 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to an idle instance. SQL> startup ORACLE instance started. Total System Global Area 2.9531E+10 bytes Fixed Size 2267424 bytes Variable Size 1.0133E+10 bytes Database Buffers 1.9327E+10 bytes Redo Buffers 67526656 bytes Database mounted. Database opened. SQL> alter database recover managed standby database using current logfile disconnect from session; Database altered. SQL> select database_role,switchover_status from v$database; DATABASE_ROLE SWITCHOVER_STATUS ---------------- -------------------- PHYSICAL STANDBY NOT ALLOWED SQL>
stdby:
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN; Database altered. SQL> shutdown immediate ORA-01109: database not open Database dismounted. ORACLE instance shut down. SQL> startup ORACLE instance started. Total System Global Area 2.9531E+10 bytes Fixed Size 2267424 bytes Variable Size 1.0133E+10 bytes Database Buffers 1.9327E+10 bytes Redo Buffers 67526656 bytes Database mounted. Database opened. SQL> alter system switch logfile; System altered. SQL> select database_role,switchover_status from v$database; DATABASE_ROLE SWITCHOVER_STATUS ---------------- -------------------- PRIMARY RESOLVABLE GAP SQL> / DATABASE_ROLE SWITCHOVER_STATUS ---------------- -------------------- PRIMARY TO STANDBY SQL> DATABASE_ROLE SWITCHOVER_STATUS ---------------- -------------------- PRIMARY SESSIONS ACTIV
9 failover
1）如果主库能启动到mount,把日志flush到standby.
pri:
SQL> alter system flush redo to oradb2;
oradb2是standby。
2） 通过primary alert.log及归档文件系统的文件确认主库当前归档的sequence。
确认standby 与primary之间的gap.
pri:
如果主库已经打不开，查alert.log
........... Archived Log entry 1064 added for thread 1 sequence 462 ID 0x4eede1f7 dest 1:
stdby:
SQL> select max(sequence#) from v$archived_log where applied='YES'; MAX(SEQUENCE#) -------------- 449
差的归档日志手动ftp到stdby,手动注册,手动注册命令：
SQL> ALTER DATABASE REGISTER PHYSICAL LOGFILE '/arch/arch1/461_87jllpq3.arc'; Database altered.
必要时执行recover到当前sequence。
3)Stop Redo Apply
SQL> alter database recover managed standby database cancel; Database altered.
4) Finish applying all received redo data.
SQL> alter database recover managed standby database finish; Database altered.
必要时可以用finish force立即中断掉RFS进程。
上面命令如果报错，执行如下命令：
SQL> alter database activate physical standby database;
5)确认standby准备为变为primary了。
SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE; SWITCHOVER_STATUS ----------------- TO PRIMARY 1 row selected
TO PRIMARY或SESSIONS ACTIVE都是对的。
6)执行切换
SQL> alter database commit to switchover to primary with session shutdown; Database altered.
7) 启动新主库
SQL> SHUTDOWN IMMEDIATE; SQL> STARTUP;
10 其它常见维护
1) 备库需重启，重启前需要如下操作
pri:
SQL> show parameter log_archive_dest_state_2; NAME TYPE VALUE ----------------------------------- ----------- ------------------------------ log_archive_dest_state_2 string ENABLE SQL> alter system set log_archive_dest_state_2='DEFER'; System altered.
重启后改回ENABLE。
2) 改SYS口令
a.停主库向备库redo传送
pri:
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2 = 'DEFER'; System altered.
b.改口令
pri:
SQL> ALTER USER SYS IDENTIFIED BY newpassword; User altered.
c.口令文件传至备库
pri:
$ cd $ORACLE_HOME/dbs $ scp orapwgxd oradb2:/oracle/app/oracle/product/11.2.0/dbs/orapwgxd
d.以新口令登陆一下
stdby:
$ sqlplus sys/newpassword as sysdba
e.启动redo传送
pri:
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2 = 'ENABLE'; System altered.
f.检查redo传送是不是正常
pri:
SQL> ALTER SYSTEM SWITCH LOGFILE; System altered.
stdby:
SQL> SELECT PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS FROM V$MANAGED_STANDBY ; PROCESS STATUS THREAD# SEQUENCE# BLOCK# BLOCKS --------- ------------ ---------- ---------- ---------- ---------- ARCH CLOSING 1 48 264192 824 ARCH CONNECTED 0 0 0 0 ARCH CONNECTED 0 0 0 0 ARCH CLOSING 1 47 1 102 MRP0 APPLYING_LOG 1 49 5785 1044480 RFS IDLE 0 0 0 0 RFS IDLE 0 0 0 0 RFS IDLE 1 49 5786 1 8 rows selected. SQL>
近期评论