DATAGUARD实施文档
一、前期准备及备机安装：
通过获取到的主机信息规划备机数据库安装基础信息：（注：在安装备机时需要尽量保障与主机库信息一致，以下表格中的备机信息为根据主机信息规划的安装信息。）
|
类别 |
IP |
数据库名 |
数据库SID |
安装位置 |
版本 |
DATAGUARD安装情况 |
|
主机 |
10.10.60.60 |
PORTALDB |
PORTALDB |
D:\app |
11.2.0.1.0 64位 |
OK |
|
备机 |
10.10.60.61 |
PORTALDB |
PORTALDB1 |
D:\app |
11.2.0.1.0 64位 |
OK |
查询主机ORACLE版本命令：
|
SQL> select banner from sys.v_$version; |
查询DATAGUARD安装情况：
|
SQL> select * from v$option where parameter = 'Oracle Data Guard'; |
安装数据库（备机）
根据以上表格中规划的信息安装备机数据库，具体安装过程略。
二、备份主机数据库：
1.冷备数据库。（先关闭数据库及对应服务，之后将D:\app下的所有文件进行备份。
2.导出数据库。
exp system/密码inctype=complete file=E:\OracleBakup\Full.dmp FULL=Y
三、配置过程
1.修改主机日志为归档模式，并设置强制写日志。
|
C:\>sqlplus /nolog SQL>startup mount SQL>alter database archivelog; SQL>alter database open; SQL>alter database force logging; |
验证修改后的情况：
|
SQL> archive log list; 数据库日志模式存档模式 自动存档启用 存档终点 D:\app\Administrator\flash_recovery_area 最早的联机日志序列 43 下一个存档日志序列 45 当前日志序列 45 SQL> |
2.修改主机的init文件,由于默认spfile启动，先创建pfile:
|
Sql>create pfile from spfile; |
然后修改D:\app\Administrator\product\11.2.0\dbhome_1\database\INITportaldb.ora
以下文件内容中红色字体是新追加的内容，黑色是默认存在的内容。
|
portaldb.__db_cache_size=5368709120 portaldb.__java_pool_size=67108864 portaldb.__large_pool_size=33554432 portaldb.__oracle_base='D:\app\Administrator'#ORACLE_BASE set from environment portaldb.__pga_aggregate_target=5502926848 portaldb.__sga_target=8254390272 portaldb.__shared_io_pool_size=0 portaldb.__shared_pool_size=2617245696 portaldb.__streams_pool_size=67108864 *.audit_file_dest='D:\app\Administrator\admin\PORTALDB\adump' *.audit_trail='db' *.compatible='11.2.0.0.0' *.control_files='D:\app\Administrator\oradata\PORTALDB\control01.ctl','E:\oraclebackup\PORTALDB\control02.ctl' *.db_block_size=8192 *.db_domain='' *.db_name='PORTALDB' *.db_recovery_file_dest='E:\oraclebackup' *.db_recovery_file_dest_size=4102029312 *.diagnostic_dest='D:\app\Administrator' *.dispatchers='(PROTOCOL=TCP) (SERVICE=PORTALDBXDB)' *.job_queue_processes=1000 *.memory_target=13739491328 *.open_cursors=300 *.processes=150 *.remote_login_passwordfile='EXCLUSIVE' *.undo_tablespace='UNDOTBS1'
DB_UNIQUE_NAME=PORTALDB LOG_ARCHIVE_CONFIG='DG_CONFIG=(PORTALDB,PORTALDB1)' LOG_ARCHIVE_DEST_1= 'LOCATION=D:\app\Administrator\flash_recovery_area VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=PORTALDB' LOG_ARCHIVE_DEST_2= 'SERVICE=PORTALDB1 LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=PORTALDB1' LOG_ARCHIVE_DEST_STATE_1=ENABLE LOG_ARCHIVE_DEST_STATE_2=ENABLE LOG_ARCHIVE_FORMAT=%t_%s_%r.arc FAL_SERVER=PORTALDB1 FAL_CLIENT=PORTALDB STANDBY_FILE_MANAGEMENT=AUTO |
删除D:\app\Administrator\product\11.2.0\dbhome_1\database\SPFILEportaldb.ora，让数据库用INITportaldb.ora参数启动,再通过pfile生成spfile.
|
Sql>create spfile from pfile; |
3. 把主机上的database移植到standby中：
把主机上的portaldb的库关闭。
把主机上的portaldb的相关datafile拷贝到standby机器上。文件路径要一致。
3.1. 数据文件: D:\app\Administrator\oradata
3.2. 参数文件：D:\app\Administrator\product\11.2.0\dbhome_1\database\INITportaldb.ora
3.3. 跟踪文件：D:\app\Administrator\admin
3.4. 密码文件：D:\app\Administrator\product\11.2.0\dbhome_1\database\PWDportaldb.ora
3.5. 闪回文件：D:\app\Administrator\flash_recovery_area
4．在主库上建立备库控制文件（控制文件通常需要有多份，手工将文件复制几份）
|
SQL> alter database create standby controlfile as 'd:/app/CONTOL.CTL’; |
拷贝如上文件到备机相应目录中。(d:/app/CONTOL.CTL)
5. 修改备机的init文件，以下是备机的init文件。
注意以下红色部分为新添加的内容。
|
portaldb.__db_cache_size=5368709120 portaldb.__java_pool_size=67108864 portaldb.__large_pool_size=33554432 portaldb.__oracle_base='D:\app\Administrator'#ORACLE_BASE set from environment portaldb.__pga_aggregate_target=5502926848 portaldb.__sga_target=8254390272 portaldb.__shared_io_pool_size=0 portaldb.__shared_pool_size=2617245696 portaldb.__streams_pool_size=67108864 *.audit_file_dest='D:\app\Administrator\admin\PORTALDB\adump' *.audit_trail='db' *.compatible='11.2.0.0.0' *.control_files='D:\app\control.ctl','E:\oraclebackup\PORTALDB\control.ctl' *.db_block_size=8192 *.db_domain='' *.db_name='PORTALDB' *.db_recovery_file_dest='E:\oraclebackup' *.db_recovery_file_dest_size=4102029312 *.diagnostic_dest='D:\app\Administrator' *.dispatchers='(PROTOCOL=TCP) (SERVICE=PORTALDBXDB)' *.job_queue_processes=1000 *.memory_target=13739491328 *.open_cursors=300 *.processes=150 *.remote_login_passwordfile='EXCLUSIVE' *.undo_tablespace='UNDOTBS1'
*.log_file_name_convert = '<D:\app\Administrator\oradata\PORTALDB\>','<D:\app\Administrator\oradata\PORTALDB\>'
DB_UNIQUE_NAME=PORTALDB1 LOG_ARCHIVE_CONFIG='DG_CONFIG=(PORTALDB,PORTALDB1)' LOG_ARCHIVE_FORMAT=log%t_%s_%r.arc LOG_ARCHIVE_DEST_1= 'LOCATION=D:\app\Administrator\flash_recovery_area VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=PORTALDB1' LOG_ARCHIVE_DEST_2= 'SERVICE=PORTALDB LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=PORTALDB' LOG_ARCHIVE_DEST_STATE_1=ENABLE LOG_ARCHIVE_DEST_STATE_2=ENABLE FAL_SERVER=PORTALDB FAL_CLIENT=PORTALDB1 STANDBY_FILE_MANAGEMENT=AUTO |
删除D:\app\Administrator\product\11.2.0\dbhome_1\database\SPFILEportaldb.ora，让数据库用INITportaldb.ora参数启动。
6.修改主机监听文件和TNS文件，以下是两个文件所在的目录：
C:\app\Administrator\product\11.2.0\dbhome_1\NETWORK\ADMIN
首先修改监听文件：listener 。修改好的监听文件如下：
|
# listener.ora Network Configuration File: D:\app\Administrator\product\11.2.0\dbhome_1\network\admin\listener.ora # Generated by Oracle configuration tools.
SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (SID_NAME = CLRExtProc) (ORACLE_HOME = D:\app\Administrator\product\11.2.0\dbhome_1) (PROGRAM = extproc) (ENVS = "EXTPROC_DLLS=ONLY:D:\app\Administrator\product\11.2.0\dbhome_1\bin\oraclr11.dll") ) )
LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521)) (ADDRESS = (PROTOCOL = TCP)(HOST = WIN-DMI15M4V4SO)(PORT = 1521)) ) )
ADR_BASE_LISTENER = D:\app\Administrator |
再修改tnsnames文件，修改好的文件内容如下：
|
# tnsnames.ora Network Configuration File: D:\app\Administrator\product\11.2.0\dbhome_1\network\admin\tnsnames.ora # Generated by Oracle configuration tools.
PORTALDB = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.128.60.60)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = PORTALDB) ) ) PORTALDB1 = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.128.60.61)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = PORTALDB1) ) )
ORACLR_CONNECTION_DATA = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521)) ) (CONNECT_DATA = (SID = CLRExtProc) (PRESENTATION = RO) ) ) |
7.修改备机监听文件和TNS文件，以下是两个文件所在的目录：
C:\app\Administrator\product\11.2.0\dbhome_1\NETWORK\ADMIN
首先修改监听文件：listener 。修改好的监听文件如下：
|
# listener.ora Network Configuration File: D:\app\Administrator\product\11.2.0\dbhome_1\network\admin\listener.ora # Generated by Oracle configuration tools.
SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (SID_NAME = CLRExtProc) (ORACLE_HOME = D:\app\Administrator\product\11.2.0\dbhome_1) (PROGRAM = extproc) (ENVS = "EXTPROC_DLLS=ONLY:D:\app\Administrator\product\11.2.0\dbhome_1\bin\oraclr11.dll") ) )
LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521)) (ADDRESS = (PROTOCOL = TCP)(HOST = WIN-UBS1NS352B9)(PORT = 1521)) ) )
ADR_BASE_LISTENER = D:\app\Administrator |
再修改tnsnames文件，修改好的文件内容如下：
|
# tnsnames.ora Network Configuration File: D:\app\Administrator\product\11.2.0\dbhome_1\network\admin\tnsnames.ora # Generated by Oracle configuration tools.
PORTALDB1 = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.128.60.61)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = PORTALDB1) ) )
PORTALDB = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.128.60.60)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = PORTALDB) ) )
ORACLR_CONNECTION_DATA = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521)) ) (CONNECT_DATA = (SID = CLRExtProc) (PRESENTATION = RO) ) ) |
注：以上主机和备机的tnsnames是一致的。
8. 测试主机primary和备机standby是否能互访:
分别在主机和备机上执行如下PING命令查看显示是否正常
|
C:\> tnsping PORTALDB C:\> tnsping PORTALDB1 |
若不能PING通，重启SERVICES.MSC里的ORACLE监听服务再次测试。
9. 启动备机:
|
C:\> sqlplus "/ as sysdba" SQL> alter database recover managed standby database disconnect from session; |
10.优化配置Standby RedoLog（主备机）
添加主机的Standby Redo Log 文件：
|
ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 ('D:\app\Administrator\oradata\PORTALDB\std_redo04.log') size 100M; ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 ('D:\app\Administrator\oradata\PORTALDB\std_redo05.log') size 100M; ALTER DATABASE ADD STANDBY LOGFILE GROUP 6 ('D:\app\Administrator\oradata\PORTALDB\std_redo06.log') size 100M; ALTER DATABASE ADD STANDBY LOGFILE GROUP 7 ('D:\app\Administrator\oradata\PORTALDB\std_redo07.log') size 100M; |
查看添加后的情况：
|
SQL> select group#,type, member from v$logfile; |
修改Online redo：
|
SQL> select group#,thread#,archived,status, bytes/1024/1024 from v$log; GROUP# THREAD# ARC STATUS BYTES/1024/1024
---------- ---------- --- ---------------- ---------------
1 1 YES INACTIVE 50
2 1 NO CURRENT 50
3 1 YES INACTIVE 50
|
先处理inactive，它表示已经完成规定的，可以删除。（因至少要两个redo组，所以删除号时需一个一个删除。如果同时删除两个，会报错。）
|
SQL> alter database drop logfile group 1; Database altered. |
找到指定物理目录删除对应的REDO01文件。再创建新的REDO01
|
alter database add logfile group 1 ('D:\app\Administrator\oradata\PORTALDB\redo01.log') size 100M; |
再次查看REDO使用状况：
|
SQL> select group#,thread#,archived,status, bytes/1024/1024 from v$log;
GROUP# THREAD# ARC STATUS BYTES/1024/1024
---------- ---------- --- ---------------- ---------------
1 1 YES UNUSED 100
2 1 NO CURRENT 50
3 1 YES INACTIVE 50 |
此时GROUP1已经创建成功。
按此方法删除GROUP3并创建GROUP3.
切换一下logfile，再删除group2
|
SQL> alter system switch logfile;
System altered.
SQL> select group#,thread#,archived,status, bytes/1024/1024 from v$log;
GROUP# THREAD# ARC STATUS BYTES/1024/1024
---------- ---------- --- ---------------- ---------------
1 1 NO CURRENT 100
2 1 YES ACTIVE 50
-- group 正在归档，需要等会再看一下 3 1 YES UNUSED 100 |
当查看归档完成时：
|
SQL> select group#,thread#,archived,status, bytes/1024/1024 from v$log;
GROUP# THREAD# ARC STATUS BYTES/1024/1024
---------- ---------- --- ---------------- ---------------
1 1 NO CURRENT 100
2 1 YES INACTIVE 50
3 1 YES UNUSED 100 |
再按上边的方法删除REDO02再建立REDO02的GROUP2组。
建立完查看情况SQL> select group#,type, member fromv$logfile; 若7个REDO文件都在并且大小也是100M，表示主机的所有REDO已经建立完成。
接下来建立备机的REDO文件：
先看一下备机在使用的REDO文件
|
SQL> select group#,thread#,archived,status, bytes/1024/1024 from v$log;
GROUP# THREAD# ARC STATUS BYTES/1024/1024
---------- ---------- --- ---------------- ---------------
1 1 YES CLEARING_CURRENT 50
3 1 YES CLEARING 50
2 1 YES CLEARING 50 |
停掉redo 的apply：
|
SQL> alter database recover managed standby database cancel; |
不然删除GROUP会报如下错误：
|
SQL> alter database drop logfile group 2;
alter database drop logfile group 2
*
ERROR at line 1:
ORA-01156: recovery in progress may need access to files |
添加备机的Standby Redo Log 文件：
|
ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 ('D:\app\Administrator\oradata\PORTALDB\std_redo04.log') size 100M; ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 ('D:\app\Administrator\oradata\PORTALDB\std_redo05.log') size 100M; ALTER DATABASE ADD STANDBY LOGFILE GROUP 6 ('D:\app\Administrator\oradata\PORTALDB\std_redo06.log') size 100M; ALTER DATABASE ADD STANDBY LOGFILE GROUP 7 ('D:\app\Administrator\oradata\PORTALDB\std_redo07.log') size 100M; |
处理online redo：
先将standby_file_management设为手动：
|
SQL> alter system set standby_file_management=manual; System altered. |
不然会报如下错误：
|
SQL> alter database drop logfile group 2;
alter database drop logfile group 2
*
ERROR at line 1:
ORA-01275: Operation DROP LOGFILE is not allowed if standby file management is automatic. |
关闭数据库，编辑PFILE配置。添加如下路径转换信息，并保存重新启动数据库。
|
SQL> shutdown immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down. |
编辑PFILE（注：该步骤的目的是因为ORACLE11若不添加转换路径是不允许执行删除备机ONLINE REDO）
|
*.log_file_name_convert ='d:\app\administrator \oradata\ PORTALDB \',' d:\app\administrator \oradata\ PORTALDB \' |
用PFILE启动备库：
|
SQL> startup nomount SQL> alter database mount standby database; |
修改备库的STD管理为手动：
|
SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT='MANUAL'; |
再按照主库的删除和创建GROUP REDO组的方法分别删除和建立REDO组，当所有REDO组都建立好后。将STANDBY的文件管理再次改为AUTO。
|
SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT='AUTO'; |
开启备库管理模式
|
SQL> alter database recover managed standby database disconnect from session;
Database altered. |
11. 检查DATAGUARD文档同步
在主机上变更使用的REDO文件
|
SQL> alter system switch logfile;
System altered. |
在主机上查看SEQUENCE
|
SQL> select max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
--------------
15 |
在备机上查看SEQUENCE：
|
SQL> select sequence#,applied from v$archived_log;
SEQUENCE# APP
---------- ---
2 YES
3 YES
5 YES
4 YES
7 YES
6 YES
8 YES
9 YES
13 YES
10 YES
11 YES
SEQUENCE# APP
---------- ---
12 YES
14 YES
15 YES
14 rows selected. |
主机上检查归档目录可用情况：
|
SQL>
select dest_name,status,error from v$archive_dest; |
查看实时归档目录个数：（主备机查看实时归档LOG是否相同）
|
archive log list; |
经查归档已经完全同步并生效，至此DATAGUARD设置完成。