目录
一. 配置大纲
- 主库设置数据库属性,增加备库的属性配置;
- 主库用spfile创建pfile,和主库的密码文件一起复制给备库;
- 修改主库和备库的tns配置文件,分别添加对应的Net service name、SID等信息;
- 备库创建监听,在主库的pfile的基础上修改必要的参数;
- nomount启动备库,用pfile创建spfile;
- 主库用rman做数据库全备,和控制文件、归档日志备份;复制备份文件到备库,放在和主库相同的目录;
- 主库创建standby控制文件,复制给备库相应的目录;
- 备库创建和主库相同的adump,bdump,cdump,udump目录,创建和主库相同的归档日志目录和数据备份目录,以及自己的数据文件目录;
- 重新以mount启动备库;
- 用rman恢复备库数据库,和归档日志;
- 备库创建standby redolog file,大小数量和主库相同,group ID取大值;
- 重启备库,启动到mount,启用redo应用;
- 配置完成,测试同步情况。
二. 配置示例
环境
主机名 | 主备 | SID | SERVICE_NAME | IP | 数据目录 | 归档目录 | 备份目录 |
---|---|---|---|---|---|---|---|
FCBDC1 | 主 | bdcdb | bdcdb | 32.31.108.1 | /oradata/bdcdb | /oradata/arch | /orabkp/backup |
FCBDC4 | 备 | bdcdb | bdcdbdg | 32.31.108.4 | /oradata/bdcdbdg | /oradata/arch | /orabkp/backup |
FCBDC05 | 备 | bdcdb | bdcdbdg02 | 32.31.102.106 | /oradata/bdcdbdg02 | /oradata/arch | /orabkp/backup |
检查主库数据库是否支持DG
SQL> select * from v$option where parameter='Managed Standby';
PARAMETER
--------------------------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------
Managed Standby
TRUE
SQL>
检查主库是否启用归档模式,不是则修改:
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /oradata/arch
Oldest online log sequence 843
Next log sequence to archive 845
Current log sequence 845
SQL>
1. 主库设置数据库属性,增加备库信息的属性配置
配置语法:
alter system set db_file_name_convert='/oradata/bdcdbdg','/oradata/bdcdbdg02','/oradata/bdcdb' scope=both;
alter system set log_file_name_convert='/oradata/bdcdbdg','/oradata/bdcdbdg02','/oradata/bdcdb' scope=spfile;
以下参数,都要参照上面的语法进行设置。
*.db_file_name_convert='/oradata/bdcdbdg','/oradata/bdcdbdg02','/oradata/bdcdb'
*.fal_server='bdcdbdg','bdcdbdg02'
*.log_archive_config='DG_CONFIG=(bdcdb,bdcdbdg,bdcdbdg02)'
*.log_archive_dest_1='LOCATION=/oradata/arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=bdcdb'
*.log_archive_dest_2='SERVICE=bdcdbdg LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=bdcdbdg'
*.log_archive_dest_3='SERVICE=bdcdbdg02 LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=bdcdbdg02'
*.log_file_name_convert='/oradata/bdcdbdg','/oradata/bdcdbdg02','/oradata/bdcdb'
*.standby_file_management='AUTO'
注意!
备库上的log_archive_dest_*设置和主库略有不同,后面说明。
2. 主库用spfile创建pfile,和主库的密码文件一起复制给备库;
- 主库创建pfile复制给备库
SQL> create pfile='/home/oracle/initfcbdc.ora' from spfile;
File created.
SQL>
WAIT_FOR_GAPWAIT_FOR_GAP
[oracle@FCBDC1 ~]$ scp /home/oracle/initfcbdc.ora 32.31.102.106:/home/oracle
- 复制主库的密码文件给备库
scp $ORACLE_HOME/dbs/orapwbdcdb 32.31.102.106:$ORACLE_HOME/dbs/orapwbdcdb
3. TNS配置
3.1 修改主库的tnsnames.ora文件,添加备库的信息。
[oracle@FCBDC1 admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /oracle/app/oracle/product/10.2.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
bdcdb =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 32.31.108.1)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = bdcdb)
(SID = bdcdb)
)
)
bdcdbdg =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 32.31.108.4)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = bdcdbdg)
(SID = bdcdb)
)
)
bdcdbdg02 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 32.31.102.106)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = bdcdbdg02)
(SID = bdcdb)
)
)
3.2 复制tnsnames.ora文件给备库
4. 备库创建监听、spfile
4.1 用netca创建监听
4.2 修改listener.ora文件,增加SID信息
[oracle@FCBDC05 admin]$ cat listener.ora
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = bdcdb)
(ORACLE_HOME = /oracle/app/oracle/product/10.2.0/db_1)
(SID_NAME = bdcdb)
)