DataGuard部署及主从切换-mysql教程

主库：vsgpdb 备库：dgdb 1、确认主库处于归档模式 SQLgt; archive log list Database log mode Archive Mode Au

主库：vsgpdb
备库：dgdb

1、确认主库处于归档模式
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /gp/arch
Oldest online log sequence 1307
Next log sequence to archive 1312
Current log sequence 1312

2、将primary 数据库置为FORCE LOGGING 模式。通过下列语句：
SQL> select force_logging from v$database;

FOR
---
YES

SQL> alter database force logging;

3.配置Primary数据库的初始化参数
修改客户端初始化参数文件，增加下列内容
*.DB_UNIQUE_NAME=vsgpdb
*.LOG_ARCHIVE_COnFIG='DG_COnFIG=(vsgpdb,dgdb)'
*.LOG_ARCHIVE_DEST_2='SERVICE=dgdb LGWR SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=dgdb'
*.LOG_ARCHIVE_DEST_STATE_2=DEFER

SQL> alter system set LOG_ARCHIVE_COnFIG='DG_COnFIG=(vsgpdb,dgdb)';
SQL> alter system set LOG_ARCHIVE_DEST_2='SERVICE=dgdb LGWR SYNC AFFIRM
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=dgdb';
SQL> alter system set LOG_ARCHIVE_DEST_STATE_2=DEFER;

#--------配置standby 角色的参数用于角色转换
*.FAL_SERVER=DGDB
*.FAL_CLIENT=VSGPDB
*.DB_FILE_NAME_COnVERT='/opt/ora10g/oradata/vsgpdb','/opt/dgdb/oradata/dgdb'
*.LOG_FILE_NAME_COnVERT='/opt/ora10g/oradata/vsgpdb','/opt/dgdb/oradata/dgdb'
*.STANDBY_FILE_MANAGEMENT=AUTO

SQL> alter system set FAL_SERVER=DGDB;

System altered.

SQL> alter system set FAL_CLIENT=VSGPDB;

System altered.

SQL> alter system set DB_FILE_NAME_COnVERT='/opt/ora10g/oradata/vsgpdb','/opt/dgdb/oradata/dgdb';
alter system set DB_FILE_NAME_COnVERT='/opt/ora10g/oradata/vsgpdb','/opt/dgdb/oradata/dgdb'
*
ERROR at line 1:
ORA-02096: specified initialization parameter is not modifiable with this
option

SQL> alter system set DB_FILE_NAME_COnVERT='/opt/ora10g/oradata/vsgpdb','/opt/dgdb/oradata/dgdb','/gp/oradata', '/opt/dgdb/oradata/dgdb' scope=spfile;

System altered.

SQL>
SQL> alter system set LOG_FILE_NAME_COnVERT='/opt/ora10g/oradata/vsgpdb','/opt/dgdb/oradata/dgdb' scope=spfile;
SQL> alter system set STANDBY_FILE_MANAGEMENT=AUTO;
修改完之后重启

4.创建控制文件--primary 库操作
SQL> alter database create standby controlfile as '/opt/ora10g/oradata/vsgpdb/dgdb03.ctl';

SQL> create pfile from spfile;

File created.

shutdown immediate --一定要关掉主库再传送数据文件

5.传送文件到备库
初始化参数文件+控制文件+数据文件
备：192.168.165.26是备库IP
scp /opt/ora10g/product/10.2.0/db_1/dbs/initvsgpdb.ora dgdb@192.168.165.26:/opt/dgdb/product/10.2.0/db_1/dbs/initdgdb.ora
scp /opt/ora10g/oradata/vsgpdb/dgdb03.ctl dgdb@192.168.165.26:/opt/dgdb/oradata/dgdb/dgdb01.ctl
scp /opt/ora10g/oradata/vsgpdb/dgdb03.ctl dgdb@192.168.165.26:/opt/dgdb/oradata/dgdb/dgdb02.ctl
scp /opt/ora10g/oradata/vsgpdb/dgdb03.ctl dgdb@192.168.165.26:/opt/dgdb/oradata/dgdb/dgdb03.ctl
scp /opt/ora10g/oradata/vsgpdb/redo02.log dgdb@192.168.165.26:/opt/dgdb/oradata/dgdb/
scp /opt/ora10g/oradata/vsgpdb/system01.dbf dgdb@192.168.165.26:/opt/dgdb/oradata/dgdb/
scp /gp/oradata/undo0201.dbf dgdb@192.168.165.26:/opt/dgdb/oradata/dgdb/
scp /opt/ora10g/oradata/vsgpdb/redo01.log dgdb@192.168.165.26:/opt/dgdb/oradata/dgdb/
scp /opt/ora10g/oradata/vsgpdb/redo03.log dgdb@192.168.165.26:/opt/dgdb/oradata/dgdb/
scp /opt/ora10g/oradata/vsgpdb/sysaux01.dbf dgdb@192.168.165.26:/opt/dgdb/oradata/dgdb/
scp /opt/ora10g/oradata/vsgpdb/temp01.dbf dgdb@192.168.165.26:/opt/dgdb/oradata/dgdb/
scp /gp/oradata/temp01.dbf dgdb@192.168.165.26:/opt/dgdb/oradata/dgdb/
scp /gp/oradata/users01.dbf dgdb@192.168.165.26:/opt/dgdb/oradata/dgdb/
6.修改备库初始化参数文件
[dgdb@rac1 dbs]$ cat initdgdb.ora
dgdb.__db_cache_size=557842432
dgdb.__java_pool_size=25165824
dgdb.__large_pool_size=4194304
dgdb.__shared_pool_size=255852544
dgdb.__streams_pool_size=130023424
*.aq_tm_processes=0
*.audit_file_dest='/opt/dgdb/admin/dgdb/adump'
*.audit_trail='DB'
*.background_dump_dest='/opt/dgdb/admin/dgdb/bdump'
*.compatible='10.2.0.3.0'
*.control_files='/opt/dgdb/oradata/dgdb/dg01.ctl','/opt/dgdb/oradata/dgdb/dg02.ctl','/opt/dgdb/oradata/dgdb/dg03.ctl'
*.core_dump_dest='/opt/dgdb/admin/dgdb/cdump'
*.cursor_sharing='EXACT'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=32
*.db_file_name_cOnvert='/opt/ora10g/oradata/vsgpdb','/opt/dgdb/oradata/dgdb','/opt/dgdb/oradata/dgdb','/opt/ora10g/oradata/vsgpdb'
*.db_keep_cache_size=33554432
*.db_name='vsgpdb'
*.db_recovery_file_dest='/opt/dgdb/flash_recovery_area'
*.db_recovery_file_dest_size=10737418240
*.dispatchers='(PROTOCOL=TCP) (SERVICE=dgdbXDB)'
*.fal_client='DGDB'
*.fal_server='VSGPDB'
*.global_names=TRUE
*.job_queue_processes=10
*.log_archive_cOnfig='DG_COnFIG=(vsgpdb,dgdb)'
*.log_archive_dest_1='location=/gp/arch'
*.log_archive_dest_2='SERVICE=vsgpdb LGWR SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=vsgpdb'
*.log_archive_dest_state_2=ENABLE
*.log_archive_format='arch%t_%s_%r.arc'
*.log_buffer=61081600
*.log_file_name_cOnvert='/opt/ora10g/oradata/vsgpdb','/opt/dgdb/oradata/dgdb','/opt/dgdb/oradata/dgdb','/opt/ora10g/oradata/vsgpdb'
*.nls_date_format='YYYY-MM-DD HH24:MI:SS'
*.open_cursors=300
*.open_links=4
*.parallel_max_servers=20
*.pga_aggregate_target=170917888
*.processes=150
*.recyclebin='OFF'
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_max_size=1073741824
*.sga_target=1073741824
*.standby_file_management='AUTO'
*.star_transformation_enabled='TRUE'
*.streams_pool_size=26214400
*.trace_enabled=FALSE
*.undo_management='AUTO'
*.undo_retention=3600
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/opt/dgdb/admin/dgdb/udump'
*.utl_file_dir='*'
[dgdb@rac1 dbs]$

[dgdb@rac1 dbs]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Wed Aug 21 08:55:38 2013

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

Connected to an idle instance.

SQL>create spfile from pfile='/opt/dgdb/product/10.2.0/db_1/dbs/initdgdb.ora';

startup mount

相关参考:

Oracle Data Guard 重要配置参数

基于同一主机配置 Oracle 11g Data Guard

探索Oracle之11g DataGuard

Oracle Data Guard （RAC+DG) 归档删除策略及脚本

Oracle Data Guard 的角色转换

Oracle Data Guard的日志FAL gap问题

Oracle 11g Data Guard Error 16143 Heartbeat failed to connect to standby 处理方法

更多详情见请继续阅读下一页的精彩内容：