同一台主机如何实现物理Data Guard配置安装

2023-06-06 04:36

短信预约 -IT技能 免费直播动态提醒

同一台主机如何实现物理Data Guard配置安装，相信很多没有经验的人对此束手无策，为此本文总结了问题出现的原因和解决方法，通过这篇文章希望你能解决这个问题。

一.Primary数据库配置
1.确认primary库处于归档模式
export ORACLE_SID=ocmdb
[oracle@ocmdb1 ~]$ sqlplus / as sysdba
sys@ocmdb> archive log list;

2.将primary库置为FORCE LOGGING 模式
sys@ocmdb> alter database force logging;

3.在primary库创建standby数据库控制文件
sys@ocmdb> !mkdir -p /home/oracle/backup
sys@ocmdb> alter database create standby controlfile as '/home/oracle/backup/ocmdg.ctl';

4.创建primary库客户端初始化参数文件
1).创建主库中的pfile
sys@ocmdb> create pfile from spfile;
2).备份到backup目录，用于创建备库的pfile
sys@ocmdb> ! cp /u01/app/oracle/product/10.2.0/db_1/dbs/initocmdb.ora /home/oracle/backup/initocmdg.ora
3).修改后主库pfile中内容如下：
[oracle@ocmdb1 ~]$ mkdir /home/oracle/ocmdb_arch
vi $ORACLE_HOME/dbs/initocmdb.ora
以下内容追加到pfile的尾部
#################################################################
#Parameters for Primary Database.
#################################################################
*.DB_NAME='ocmdb'
*.DB_UNIQUE_NAME=ocmdb
*.LOG_ARCHIVE_FORMAT=log%t_%s_%r.arc
*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(ocmdb,ocmdg)'
*.LOG_ARCHIVE_DEST_1='LOCATION=/u01/app/oracle/oradata/ocmdb VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=ocmdb'
*.LOG_ARCHIVE_DEST_2='SERVICE=ocmdg LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ocmdg'
*.LOG_ARCHIVE_DEST_STATE_1=ENABLE
*.LOG_ARCHIVE_DEST_STATE_2=ENABLE
*.REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE

#################################################################
#Parameters which using for switch over from Primary to Standby.
#################################################################
*.FAL_SERVER=ocmdg
*.FAL_CLIENT=ocmdb
*.DB_FILE_NAME_CONVERT='/u01/app/oracle/oradata/ocmdg','/u01/app/oracle/oradata/ocmdb'
*.LOG_FILE_NAME_CONVERT='/u01/app/oracle/oradata/ocmdg','/u01/app/oracle/oradata/ocmdb'
*.STANDBY_FILE_MANAGEMENT=AUTO
*.STANDBY_ARCHIVE_DEST='/home/oracle/ocmdb_arch'

4).通过pfile 重建spfile
sys@ocmdb> shutdown immediate;
NotConnected@> create spfile from pfile='initocmdb.ora';

5.配置listener
vi $ORACLE_HOME/network/admin/tnsnames.ora

OCMDB =
(DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = ocmdb1.localdomain)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ocmdb)
    )
)

OCMDG =
(DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = ocmdb1.localdomain)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ocmdg)
    )
)

验证监听
tnsping ocmdb
tnsping ocmdg

二.Standby数据库配置
1.创建密码文件，注意保持sys 密码与primary 数据库一致
cd $ORACLE_HOME/dbs
orapwd file=orapwocmdg password=oracle1 entries=20

2.创建所需目录（注意OMF管理的文件）
cd $ORACLE_BASE/admin
mkdir -p ocmdg/adump
mkdir -p ocmdg/bdump
mkdir -p ocmdg/cdump
mkdir -p ocmdg/dpdump
mkdir -p ocmdg/udump
mkdir -p ocmdg/pfile

mkdir -p oradata/ocmdg
mkdir -p oradata/ocmdg
mkdir -p oradata/ocmdg

3.复制数据文件到standby库对应的目录（datafile,log,controlfiles）
关闭主库
export ORACLE_SID=ocmdb
sqlplus / as sysdba
shutdown immediate;

1).拷贝数据文件
cp /u01/app/oracle/oradata/ocmdb/* /u01/app/oracle/oradata/ocmdg
2).拷贝生成的standby控制文件
cp /home/oracle/backup/ocmdg.ctl /u01/app/oracle/oradata/ocmdg/control01.ctl
cp /home/oracle/backup/ocmdg.ctl /u01/app/oracle/oradata/ocmdg/control02.ctl
cp /home/oracle/backup/ocmdg.ctl /u01/app/oracle/oradata/ocmdg/control03.ctl

4.修改standby初始化参数文件
[oracle@ocmdb1 ~]$ mkdir /home/oracle/ocmdg_arch
1).standby的初始化参数如下
cp /home/oracle/backup/initocmdg.ora /u01/app/oracle/product/10.2.0/db_1/dbs/initocmdg.ora
注意需要将所有与路径相关的参数调整正确
vi $ORACLE_HOME/dbs/initocmdg.ora
ocmdb.__db_cache_size=125829120
ocmdb.__java_pool_size=4194304
ocmdb.__large_pool_size=4194304
ocmdb.__shared_pool_size=96468992
ocmdb.__streams_pool_size=8388608
*.audit_file_dest='/u01/app/oracle/admin/ocmdg/adump'
*.background_dump_dest='/u01/app/oracle/admin/ocmdg/bdump'
*.compatible='10.2.0.1.0'
*.control_files='/u01/app/oracle/oradata/ocmdg/control01.ctl','/u01/app/oracle/oradata/ocmdg/control02.ctl','/u01/app/oracle/oradata/ocmdg/control03.ctl'
*.core_dump_dest='/u01/app/oracle/admin/ocmdg/cdump'
*.db_16k_cache_size=33554432
*.db_block_size=8192
*.db_create_file_dest='/u01/app/oracle/oradata/ocmdg/'
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_keep_cache_size=33554432
*.db_name='ocmdb'
*.db_recovery_file_dest='/u01/app/oracle/flash_recovery_area'
*.db_recovery_file_dest_size=21474836480
*.dispatchers='(protocol=TCP)(serv=ocmdg)(list=ocmdg)(poo=on)(disp=6)(con=36)(sess=100)'
*.job_queue_processes=10
*.log_archive_format='%t_%s_%r.dbf'
*.max_dispatchers=0
*.max_shared_servers=0
*.open_cursors=300
*.pga_aggregate_target=62914560
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
ocmdg.resource_manager_plan='WEEKDAYS'
*.sga_target=314572800
*.shared_servers=0
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/u01/app/oracle/admin/ocmdg/udump'

#################################################################
#Parameters for Standby Database.
#################################################################
*.DB_NAME='ocmdb'
*.DB_UNIQUE_NAME=ocmdg
*.LOG_ARCHIVE_FORMAT=log%t_%s_%r.arc
*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(ocmdb,ocmdg)'
*.DB_FILE_NAME_CONVERT='/u01/app/oracle/oradata/ocmdb','/u01/app/oracle/oradata/ocmdg'
*.LOG_FILE_NAME_CONVERT='/u01/app/oracle/oradata/ocmdb','/u01/app/oracle/oradata/ocmdg'
*.STANDBY_ARCHIVE_DEST='/home/oracle/ocmdg_arch'
*.FAL_SERVER=ocmdb
*.FAL_CLIENT=ocmdg
*.STANDBY_FILE_MANAGEMENT=AUTO

#################################################################
#Parameters which using for switch over from Standby to Primary.
#################################################################
*.LOG_ARCHIVE_DEST_1='LOCATION=/u01/app/oracle/oradata/ocmdg VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=ocmdg'
*.LOG_ARCHIVE_DEST_2='SERVICE=ocmdb LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ocmdb'
*.LOG_ARCHIVE_DEST_STATE_1=ENABLE
*.LOG_ARCHIVE_DEST_STATE_2=ENABLE
*.REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE

2).通过该pfile 创建spfile
export ORACLE_SID=ocmdg
create spfile from pfile

5.启动主库实例
export ORACLE_SID=ocmdb
sqlplus / as sysdba
startup;

6.启动standby到mount
export ORACLE_SID=ocmdg
sqlplus / as sysdba
NotConnected@> startup mount;

7.启动redo应用
NotConnected@> alter database recover managed standby database disconnect from session;

到此，在同一台主机上创建物理standby的任务就完成了。
关于测试DG的有效性测试请参考《【DG】同一台主机实现物理Data Guard配置安装》（http://space.itpub.net/?uid-519536-action-viewspace-itemid-578181）。
这里描述的非OMF管理方式下GD的配置方法，因为文件目录相对比较固定，配置大同小异。