【Oracle】搭建DG（DataGuard）
操作系统：OEL 5.6
Oracle 版本：11.2.0.4.0
| DATABASE_ROLE | DB_NAME | IPADDR |
| Primary | lgr | 192.168.10.5 |
| Standby | DG | 192.168.10.88 |
1. 主库开启归档，开启强制日志，关闭闪回，删除外部表
SYS@lgr> startup mount; ORACLE instance started. Total System Global Area 835104768 bytes Fixed Size 2257840 bytes Variable Size 536874064 bytes Database Buffers 289406976 bytes Redo Buffers 6565888 bytes Database mounted. SYS@lgr> alter database archivelog; Database altered. SYS@lgr> alter database open; Database altered. SYS@lgr> select force_logging from v$database; FOR --- NO SYS@lgr> alter database force logging; Database altered. SYS@lgr> select flashback_on from v$database; FLASHBACK_ON ------------------ NO ##xxx_external_locations 可以知道当前所有的目录对象以及相关的外部表，还会查询出这些外部表所对应的操作系统文件的名字。 ##select * from all_external_locations; ##select * from user_external_locations; ##select * from dba_external_locations;
2.主库创建standby logfile
配置原则： ①standby logfile大小要和redo logfile大小相同； ②standby logfile要比redo logfile多一组，我的redo logfile是3组，所以创建4组standby logfile； ③为了安全，standby logfile可以包含多个member。 SYS@lgr> select group#,member from v$logfile; GROUP# MEMBER ---------- -------------------------------------------------- 3 /u01/app/oracle/oradata/lgr/redo03.log 2 /u01/app/oracle/oradata/lgr/redo02.log 1 /u01/app/oracle/oradata/lgr/redo01.log SYS@lgr> SYS@lgr> select group#,bytes/1024/1024 size_M from v$log; GROUP# SIZE_M ---------- ---------- 1 50 2 50 3 50 SYS@lgr> alter database add standby logfile group 4('/u01/app/oracle/oradata/lgr/sredo04.log') size 50M; Database altered. SYS@lgr> alter database add standby logfile group 5('/u01/app/oracle/oradata/lgr/sredo05.log') size 50M; Database altered. SYS@lgr> alter database add standby logfile group 6('/u01/app/oracle/oradata/lgr/sredo06.log') size 50M; Database altered. SYS@lgr> alter database add standby logfile group 7('/u01/app/oracle/oradata/lgr/sredo07.log') size 50M; Database altered. SYS@lgr> SYS@lgr> select group#,member from v$logfile; GROUP# MEMBER ---------- -------------------------------------------------- 3 /u01/app/oracle/oradata/lgr/redo03.log 2 /u01/app/oracle/oradata/lgr/redo02.log 1 /u01/app/oracle/oradata/lgr/redo01.log 4 /u01/app/oracle/oradata/lgr/sredo04.log 5 /u01/app/oracle/oradata/lgr/sredo05.log 6 /u01/app/oracle/oradata/lgr/sredo06.log 7 /u01/app/oracle/oradata/lgr/sredo07.log 7 rows selected. SYS@lgr> SYS@lgr> select group#,thread#,sequence#,bytes,status from v$standby_log; GROUP# THREAD# SEQUENCE# BYTES STATUS ---------- ---------- ---------- ---------- ---------- 4 0 0 52428800 UNASSIGNED 5 0 0 52428800 UNASSIGNED 6 0 0 52428800 UNASSIGNED 7 0 0 52428800 UNASSIGNED
3.主库配置listener
---修改local_listener为默认监听 SYS@lgr> alter system set local_listener=''; System altered. SYS@lgr> show parameter local_listener NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ local_listener string ---配置listener.ora [oracle@lgr ~]$ vi /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.10.5)(PORT = 1521)) (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521)) ) ) sid_list_listener= (sid_list= (sid_desc= (global_dbname=lgr) (ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1) (sid_name=lgr)) ) ADR_BASE_LISTENER = /u01/app/oracle
4.主库配置tnsnames
[oracle@lgr ~]$ vi /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora lgr = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.10.5)(PORT = 1521)) ) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = lgr) ) ) DG = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.10.88)(PORT = 1521)) ) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = DG) ) )
5.主库修改参数
5.1.生成pfile
SYS@lgr> startup ORACLE instance started. Total System Global Area 835104768 bytes Fixed Size 2257840 bytes Variable Size 536874064 bytes Database Buffers 289406976 bytes Redo Buffers 6565888 bytes Database mounted. Database opened. SYS@lgr> create pfile from spfile; File created. SYS@lgr> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. SYS@lgr>
5.2.修改参数
##参数模板可以在官方文档中查到，把其中的名称用自己的数据库名称替换即可。 [oracle@lgr ~]$ cd $ORACLE_HOME/dbs [oracle@lgr dbs]$ vi initlgr.ora ----追加如下内容---- #Primary Role Initialization Parameters DB_UNIQUE_NAME=lgr LOG_ARCHIVE_CONFIG='DG_CONFIG=(lgr,DG)' LOG_ARCHIVE_DEST_1= 'LOCATION=/arch1/lgr/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=lgr' LOG_ARCHIVE_DEST_2= 'SERVICE=DG ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=DG' 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=30 #Standby Role Initialization Parameters FAL_SERVER=DG DB_FILE_NAME_CONVERT='DG','lgr' LOG_FILE_NAME_CONVERT='DG','lgr' STANDBY_FILE_MANAGEMENT=AUTO ##根据参数文件创建不存在的目录 [root@lgr ~]# mkdir -p /arch1/lgr/ [root@lgr ~]# chown -R oracle:oinstall /arch1/lgr/
5.3.通过pfile重新生成spfile并启动数据库
SYS@lgr> create spfile from pfile; File created. SYS@lgr> SYS@lgr> SYS@lgr> startup ORACLE instance started. Total System Global Area 835104768 bytes Fixed Size 2257840 bytes Variable Size 536874064 bytes Database Buffers 289406976 bytes Redo Buffers 6565888 bytes Database mounted. Database opened. SYS@lgr> SYS@lgr> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. SYS@lgr>
6.拷贝相关文件至备库
[oracle@lgr dbs]$ pwd /u01/app/oracle/product/11.2.0/db_1/dbs [oracle@lgr dbs]$ ll total 24 -rw-rw---- 1 oracle oinstall 1544 Jan 4 23:29 hc_lgr.dat -rw-r--r-- 1 oracle oinstall 1487 Jan 4 23:26 initlgr.ora -rw-r--r-- 1 oracle oinstall 2851 May 15 2009 init.ora -rw-r----- 1 oracle oinstall 24 Jan 4 19:15 lkLGR -rw-r----- 1 oracle oinstall 1536 Jan 4 19:20 orapwlgr -rw-r----- 1 oracle oinstall 3584 Jan 4 23:29 spfilelgr.ora [oracle@lgr dbs]$ [oracle@lgr dbs]$ [oracle@lgr dbs]$ scp initlgr.ora oracle@192.168.10.88:/u01/app/oracle/product/11.2.0/db_1/dbs/initDG.ora The authenticity of host '192.168.10.88 (192.168.10.88)' can't be established. RSA key fingerprint is c8:bb:ef:15:8b:1c:e0:ad:e4:3f:eb:3b:a7:17:e7:dd. Are you sure you want to continue connecting (yes/no)? yes Warning: Permanently added '192.168.10.88' (RSA) to the list of known hosts. oracle@192.168.10.88's password: initlgr.ora 100% 1487 1.5KB/s 00:00 [oracle@lgr dbs]$ [oracle@lgr dbs]$ [oracle@lgr dbs]$ scp orapwlgr oracle@192.168.10.88:/u01/app/oracle/product/11.2.0/db_1/dbs/orapwDG oracle@192.168.10.88's password: orapwlgr 100% 1536 1.5KB/s 00:00 [oracle@lgr dbs]$ [oracle@lgr dbs]$ scp /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora oracle@192.168.10.88:/u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora oracle@192.168.10.88's password: tnsnames.ora 100% 404 0.4KB/s 00:00 [oracle@lgr dbs]$
7.备库创建静态监听并启动监听
[oracle@DG ~]$ cd /u01/app/oracle/product/11.2.0/db_1/network/admin/ [oracle@DG admin]$ vi listener.ora LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.10.88)(PORT = 1521)) (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521)) ) ) sid_list_listener= (sid_list= (sid_desc= (global_dbname=DG) (ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1) (sid_name=DG)) ) ADR_BASE_LISTENER = /u01/app/oracle [oracle@DG dbs]$ lsnrctl LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 04-JAN-2017 23:36:18 Copyright (c) 1991, 2013, Oracle. All rights reserved. Welcome to LSNRCTL, type "help" for information. LSNRCTL> start listener Starting /u01/app/oracle/product/11.2.0/db_1/bin/tnslsnr: please wait... TNSLSNR for Linux: Version 11.2.0.4.0 - Production System parameter file is /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora Log messages written to /u01/app/oracle/diag/tnslsnr/DG/listener/alert/log.xml Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.10.88)(PORT=1521))) Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521))) Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.10.88)(PORT=1521))) STATUS of the LISTENER ------------------------ Alias listener Version TNSLSNR for Linux: Version 11.2.0.4.0 - Production Start Date 04-JAN-2017 23:36:25 Uptime 0 days 0 hr. 0 min. 0 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora Listener Log File /u01/app/oracle/diag/tnslsnr/DG/listener/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.10.88)(PORT=1521))) (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521))) Services Summary... Service "DG" has 1 instance(s). Instance "DG", status UNKNOWN, has 1 handler(s) for this service... The command completed successfully
8.备库参数修改
8.1.修改备库pfile
##把主库名称修改为备库名称，备库名称修改为主库名称，但参数文件中的db_name要和主库名称一致。 [oracle@lgr dbs]$ pwd /u01/app/oracle/product/11.2.0/db_1/dbs [oracle@DG dbs]$ vi initDG.ora lgr.__db_cache_size=289406976 lgr.__java_pool_size=4194304 lgr.__large_pool_size=71303168 lgr.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment lgr.__pga_aggregate_target=335544320 lgr.__sga_target=503316480 lgr.__shared_io_pool_size=0 lgr.__shared_pool_size=125829120 lgr.__streams_pool_size=0 删除以上内容，并将剩余的内容修改为DG的目录 *.audit_file_dest='/u01/app/oracle/admin/DG/adump' *.audit_trail='db' *.compatible='11.2.0.4.0' *.control_files='/u01/app/oracle/oradata/DG/control01.ctl','/u01/app/oracle/fast_recovery_area/DG/control02.ctl' *.db_block_size=8192 *.db_domain='' *.db_name='lgr' *.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area' *.db_recovery_file_dest_size=4385144832 *.diagnostic_dest='/u01/app/oracle' *.dispatchers='(PROTOCOL=TCP) (SERVICE=DGXDB)' *.local_listener='' *.memory_target=838860800 *.open_cursors=300 *.processes=150 *.remote_login_passwordfile='EXCLUSIVE' *.undo_tablespace='UNDOTBS1' DB_UNIQUE_NAME=DG LOG_ARCHIVE_CONFIG='DG_CONFIG=(DG,lgr)' LOG_ARCHIVE_DEST_1= 'LOCATION=/arch1/DG/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=DG' LOG_ARCHIVE_DEST_2= 'SERVICE=lgr ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=lgr' 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=30 #Standby Role Initialization Parameters FAL_SERVER=lgr DB_FILE_NAME_CONVERT='lgr','DG' LOG_FILE_NAME_CONVERT='lgr','DG' STANDBY_FILE_MANAGEMENT=AUTO
8.2.备库按照参数文件创建目录
[oracle@DG dbs]$ mkdir -p /u01/app/oracle/admin/DG/adump [oracle@DG dbs]$ mkdir -p /u01/app/oracle/oradata/DG [oracle@DG dbs]$ mkdir -p /arch1/DG/ [oracle@DG dbs]$ mkdir -p /u01/app/oracle/fast_recovery_area
8.3.备库通过pfile生成spfile并启动数据库至nomount
[oracle@DG dbs]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.4.0 Production on Wed Jan 4 23:56:25 2017 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to an idle instance. SQL> create spfile from pfile; File created. SQL> startup nomount; ORACLE instance started. Total System Global Area 835104768 bytes Fixed Size 2257840 bytes Variable Size 536874064 bytes Database Buffers 289406976 bytes Redo Buffers 6565888 bytes SQL>
9.使用RMAN auxiliary恢复数据库
主库为open状态，备库为nomount 监听为启动状态 [oracle@lgr ~]$ rman target sys/oracle@lgr auxiliary sys/oracle@DG Recovery Manager: Release 11.2.0.4.0 - Production on Thu Jan 5 00:04:12 2017 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. connected to target database: LGR (DBID=1711819363) connected to auxiliary database: LGR (not mounted) RMAN> duplicate target database for standby from active database dorecover nofilenamecheck; Starting Duplicate Db at 05-JAN-17 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 ..... media recovery complete, elapsed time: 00:00:00 Finished recover at 05-JAN-17 Finished Duplicate Db at 05-JAN-17 RMAN>
10.备库同步数据
SQL> select open_mode from v$database; OPEN_MODE -------------------- MOUNTED SQL> alter database recover managed standby database using current logfile disconnect from session; Database altered. SQL> select database_role,open_mode from v$database; DATABASE_ROLE OPEN_MODE ---------------- -------------------- PHYSICAL STANDBY MOUNTED
至此，DataGuard搭建完成！
