Oracle 11gR2 搭建活动的物理DG/DataGuard (READ ONLY模式)
Oracle 11gR2 搭建活动的物理DG/DataGuard (READ ONLY模式)
由于换了一家公司,这家公司全部是用的Oracle 11GR2+DG,11GR2没有玩过,DG也没有玩过,所以这里在自己的VM里面搭建一个11GR2+DG的测试环境,STANDBY启动到READ ONLY测试环境为:OS RedHat 5.6 X86_64,DB 11.2.0.2
安装环境与创建数据库这里就不用说了,很简单的。
整个环境为成4个步骤:
1,主备修改
1.1 修改参数
1.2 修改监听
1.3 cp相关文件到standby上面
1.4 创建standby日志
2,备备修改
2.1 修改参数
2.2 修改监听与测试
2.3 启动到mount
2.4 创建相关目录
3,主备duplicate数据库
4,测试DG是否成功
正在开始正式的测试过程
1.1 查看数据库是否在归档与是否强制LOGGING模式。
- SQL> select log_mode,force_logging from v$database;
- LOG_MODE FOR
- ------------ ---
- ARCHIVELOG NO
- SQL> ALTER DATABASE FORCE LOGGING;
- Database altered.
1.2 查看数据库的日志组个数与大小,因为我们创建standby日志组的个数是原日志组个数+1再与thread的积,size不能小于原日志文件的大小。
- SQL> select group#,bytes/1024/1024 from v$log;
- GROUP# BYTES/1024/1024
- ---------- ---------------
- 1 50
- 2 50
- 3 50
- SQL> select member from v$logfile;
- MEMBER
- --------------------------------------------------------------------------------
- /u01/app/oracle/oradata/htz/redo03.log
- /u01/app/oracle/oradata/htz/redo02.log
- /u01/app/oracle/oradata/htz/redo01.log
1.3 创建standby日志组,位置与原日志组相同的路径。创建完成后查询是否成功
- SQL> alter database add standby logfile '/u01/app/oracle/oradata/htz/standby01.log' size 50m;
- Database altered.
- SQL> alter database add standby logfile '/u01/app/oracle/oradata/htz/standby02.log' size 50m;
- Database altered.
- SQL> alter database add standby logfile '/u01/app/oracle/oradata/htz/standby03.log' size 50m;
- Database altered.
- SQL> alter database add standby logfile '/u01/app/oracle/oradata/htz/standby04.log' size 50m;
- Database altered.
- SQL> select group#,status,type,member from v$logfile;
- GROUP# STATUS TYPE MEMBER
- ---------- ------- ------- --------------------------------------------------
- 3 ONLINE /u01/app/oracle/oradata/htz/redo03.log
- 2 ONLINE /u01/app/oracle/oradata/htz/redo02.log
- 1 ONLINE /u01/app/oracle/oradata/htz/redo01.log
- 4 STANDBY /u01/app/oracle/oradata/htz/standby01.log
- 5 STANDBY /u01/app/oracle/oradata/htz/standby02.log
- 6 STANDBY /u01/app/oracle/oradata/htz/standby03.log
- 7 STANDBY /u01/app/oracle/oradata/htz/standby04.log
1.4 修改相关的参数,与DG的参数就只与几个参数相关,大概就是日志,文件的位置的转换,GAP的处理,其实GAP已经会自动的处理,不过这里我们还是介绍配置FAL_SERVER,FAL_CLIENT参数。
- SQL> alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(htz,htzb)';
- System altered.
- SQL> alter system set LOG_ARCHIVE_DEST_1='LOCATION=/u01/app/oracle/arch/htz/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=htz';
- System altered.
- SQL> alter system set LOG_ARCHIVE_DEST_2='SERVICE=htzb LGWR SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=htzb';
- System altered.
- SQL> alter system set LOG_ARCHIVE_DEST_STATE_1=ENABLE;
- System altered.
- SQL> alter system set FAL_SERVER=htzb;
- System altered.
- SQL> alter system set FAL_CLIENT=htz;
- System altered.
- SQL> alter system set db_unique_name=htz scope=spfile;
- System altered.
- SQL> alter system set db_file_name_convert='/u01/app/oracle/oradata/htz/','/u01/app/oracle/oradata/htz/' scope=spfile;
- System altered.
- SQL> alter system set log_file_name_convert='/u01/app/oracle/oradata/htz/','/u01/app/oracle/oradata/htz/' scope=spfile;
- System altered.
- SQL> create pfile from spfile;
- File created.
1.5 把dbs下的内容同步到standby主机上面,主要是密码文件(一定要复制过去的)与init文件。
- [oracle@11g admin]$ rsync -avlR /u01/app/oracle/product/11.2.0/db_1/dbs/* 192.168.100.31:/
- oracle@192.168.100.31's password:
- building file list ... done
- /u01/
- /u01/app/
- /u01/app/oracle/
- /u01/app/oracle/product/
- /u01/app/oracle/product/11.2.0/
- /u01/app/oracle/product/11.2.0/db_1/
- /u01/app/oracle/product/11.2.0/db_1/dbs/
- /u01/app/oracle/product/11.2.0/db_1/dbs/hc_DBUA0.dat
- /u01/app/oracle/product/11.2.0/db_1/dbs/hc_htz.dat
- /u01/app/oracle/product/11.2.0/db_1/dbs/hc_luo.dat
- /u01/app/oracle/product/11.2.0/db_1/dbs/init.ora
- /u01/app/oracle/product/11.2.0/db_1/dbs/inithtz.ora
- /u01/app/oracle/product/11.2.0/db_1/dbs/lkHTZ
- /u01/app/oracle/product/11.2.0/db_1/dbs/lkHTZA
- /u01/app/oracle/product/11.2.0/db_1/dbs/orapwhtz
- /u01/app/oracle/product/11.2.0/db_1/dbs/snapcf_htz.f
- /u01/app/oracle/product/11.2.0/db_1/dbs/spfilehtz.ora
- sent 9764651 bytes received 282 bytes 161403.85 bytes/sec
- total size is 9762574 speedup is 1.00
1.6 监听的修改,特别注意这里我们使用了静态的监听,是为了以后我们测试broker时使用的,如果你不用这个,那边可以用动态监听,
- $ lsnrctl stop
- LSNRCTL for Solaris: Version 11.2.0.1.0 - Production on 31-AUG-2012 21:38:51
- Copyright (c) 1991, 2009, Oracle. All rights reserved.
- Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.100.30)(PORT=1521)))
- The command completed successfully
- [oracle@11g admin]$ cat listener.ora
- SID_LIST_LISTENER =
- (SID_LIST =
- (SID_DESC =
- (GLOBAL_DBNAME = htz)
- (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
- (SID_NAME = htz)
- )
- )
- LISTENER =
- (DESCRIPTION =
- (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.30)(PORT = 1521))
- )
- [oracle@11g admin]$ cat tnsnames.ora
- HTZB =
- (DESCRIPTION =
- (ADDRESS_LIST =
- (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.31)(PORT = 1521))
- )
- (CONNECT_DATA =
- (SERVER = dedicate)
- (SERVICE_NAME = htzb)
- )
- )
- HTZ =
- (DESCRIPTION =
- (ADDRESS_LIST =
- (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.30)(PORT = 1521))
- )
- (CONNECT_DATA =
- (SERVER = dedicate)
- (SERVICE_NAME = htz)
- )
- )
- 启动监听
- [oracle@11g admin]$ lsnrctl start
- LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 07-SEP-2012 05:35:50
- Copyright (c) 1991, 2010, Oracle. All rights reserved.
- Starting /u01/app/oracle/product/11.2.0/db_1/bin/tnslsnr: please wait...
- TNSLSNR for Linux: Version 11.2.0.2.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/11g/listener/alert/log.xml
- Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.100.30)(PORT=1521)))
- Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.100.30)(PORT=1521)))
- STATUS of the LISTENER
- ------------------------
- Alias LISTENER
- Version TNSLSNR for Linux: Version 11.2.0.2.0 - Production
- Start Date 07-SEP-2012 05:35:50
- 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/11g/listener/alert/log.xml
- Listening Endpoints Summary...
- (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.100.30)(PORT=1521)))
- Services Summary...
- Service "htz" has 1 instance(s).
- Instance "htz", status UNKNOWN, has 1 handler(s) for this service...
- The command completed successfully
主库上面修改的内容差不多就是这些了。
下面就是库备的修改了。
2.1 修改监听
- [oracle@11gdg admin]$ cat listener.ora
- # listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
- # Generated by Oracle configuration tools.
- SID_LIST_LISTENER =
- (SID_LIST =
- (SID_DESC =
- (GLOBAL_DBNAME = htzb)
- (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
- (SID_NAME = htz)
- )
- )
- LISTENER =
- (DESCRIPTION =
- (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.31)(PORT = 1521))
- )
- ADR_BASE_LISTENER = /u01/app/oracle
- [oracle@11gdg admin]$ cat tnsnames.ora
- # tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora
- # Generated by Oracle configuration tools.
- HTZB =
- (DESCRIPTION =
- (ADDRESS_LIST =
- (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.31)(PORT = 1521))
- )
- (CONNECT_DATA =
- (SERVER = dedicate)
- (SERVICE_NAME = htzb)
- )
- )
- HTZ =
- (DESCRIPTION =
- (ADDRESS_LIST =
- (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.30)(PORT = 1521))
- )
- (CONNECT_DATA =
- (SERVER = dedicate)
- (SERVICE_NAME = htz)
- )
- )
- [oracle@11gdg admin]$ lsnrctl stop
- LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 06-SEP-2012 18:13:17
- Copyright (c) 1991, 2010, Oracle. All rights reserved.
- Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.100.31)(PORT=1521)))
- The command completed successfully
- [oracle@11gdg admin]$ lsnrctl start
- LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 06-SEP-2012 18:13:23
- Copyright (c) 1991, 2010, Oracle. All rights reserved.
- Starting /u01/app/oracle/product/11.2.0/db_1/bin/tnslsnr: please wait...
- TNSLSNR for Linux: Version 11.2.0.2.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/11gdg/listener/alert/log.xml
- Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.100.31)(PORT=1521)))
- Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.100.31)(PORT=1521)))
- STATUS of the LISTENER
- ------------------------
- Alias LISTENER
- Version TNSLSNR for Linux: Version 11.2.0.2.0 - Production
- Start Date 06-SEP-2012 18:13:24
- 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/11gdg/listener/alert/log.xml
- Listening Endpoints Summary...
- (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.100.31)(PORT=1521)))
- Services Summary...
- Service "htzb" has 1 instance(s).
- Instance "htz", status UNKNOWN, has 1 handler(s) for this service...
- The command completed successfully
- 测试监听是否正常
- [oracle@11gdg /]$ sqlplus sys/oracle@htz as sysdba
- SQL*Plus: Release 11.2.0.2.0 Production on Thu Sep 6 18:19:20 2012
- Copyright (c) 1982, 2010, Oracle. All rights reserved.
- Connected to:
- Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
- With the Partitioning, OLAP, Data Mining and Real Application Testing options
- SQL> exit
- Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
- With the Partitioning, OLAP, Data Mining and Real Application Testing options
- [oracle@11gdg /]$ sqlplus sys/oracle@htzb as sysdba
- SQL*Plus: Release 11.2.0.2.0 Production on Thu Sep 6 18:19:24 2012
- Copyright (c) 1982, 2010, Oracle. All rights reserved.
- Connected to an idle instance.
2.1 创建相关文件与修改参数文件
- 创建相关目录
- [oracle@11gdg admin]$ mkdir -p /u01/app/oracle/arch/htz
- [oracle@11gdg admin]$ mkdir -p /u01/app/oracle/flash_recovery_area/htz
- [oracle@11gdg admin]$ mkdir -p /u01/app/oracle/oradata/htz
- [oracle@11gdg admin]$ mkdir -p /u01/app/oracle/admin/htz/adump
- 修改参数文件
- [oracle@11gdg /]$ cd $ORACLE_HOME/dbs
- [oracle@11gdg dbs]$ rm spfilehtz.ora
- [oracle@11gdg dbs]$ cat inithtz.ora
- htz.__db_cache_size=67108864
- htz.__java_pool_size=4194304
- htz.__large_pool_size=4194304
- htz.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
- htz.__pga_aggregate_target=134217728
- htz.__sga_target=180355072
- htz.__shared_io_pool_size=0
- htz.__shared_pool_size=96468992
- htz.__streams_pool_size=0
- *.audit_file_dest='/u01/app/oracle/admin/htz/adump'
- *.audit_trail='db'
- *.compatible='11.2.0.0.0'
- *.control_files='/u01/app/oracle/oradata/htz/control01.ctl','/u01/app/oracle/oradata/htz/control02.ctl'
- *.db_block_size=8192
- *.db_domain=''
- *.db_file_name_convert='/u01/app/oracle/oradata/htz/','/u01/app/oracle/oradata/htz/'
- *.db_name='htz'
- *.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'
- *.db_recovery_file_dest_size=4227858432
- *.db_unique_name='HTZB'
- *.diagnostic_dest='/u01/app/oracle'
- *.dispatchers='(PROTOCOL=TCP) (SERVICE=htzXDB)'
- *.fal_client='HTZB'
- *.fal_server='HTZ'
- *.log_archive_config='DG_CONFIG=(htz,htzb)'
- *.log_archive_dest_1='LOCATION=/u01/app/oracle/arch/htz/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=htzb'
- *.log_archive_dest_2='SERVICE=htz LGWR SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=htz'
- *.log_archive_dest_state_1='ENABLE'
- *.log_archive_format='%t_%s_%r.dbf'
- *.log_file_name_convert='/u01/app/oracle/oradata/htz/','/u01/app/oracle/oradata/htz/'
- *.memory_target=314572800
- *.open_cursors=300
- *.processes=150
- *.remote_login_passwordfile='EXCLUSIVE'
- *.undo_tablespace='UNDOTBS1'
1.3 创建spfile与启动数据库到mount
- 创建spfile,并启动数据库到NOMOUNT
- [oracle@11gdg dbs]$ sqlplus / as sysdba
- SQL*Plus: Release 11.2.0.2.0 Production on Thu Sep 6 18:24:02 2012
- Copyright (c) 1982, 2010, Oracle. All rights reserved.
- Connected to an idle instance.
- SQL> startup nomount;
- ORACLE instance started.
- Total System Global Area 313159680 bytes
- Fixed Size 2226072 bytes
- Variable Size 239077480 bytes
- Database Buffers 67108864 bytes
- Redo Buffers 4747264 bytes
- SQL> create spfile from pfile;
- File created.
- SQL> startup force mount;
- ORACLE instance started.
- Total System Global Area 313159680 bytes
- Fixed Size 2226072 bytes
- Variable Size 239077480 bytes
- Database Buffers 67108864 bytes
- Redo Buffers 4747264 bytes
- ORA-00205: error in identifying control file, check alert log for more info
- SQL> show parameter spfile;
- NAME TYPE VALUE
- ------------------------------------ ----------- ------------------------------
- spfile string /u01/app/oracle/product/11.2.0
- /db_1/dbs/spfilehtz.ora
1.4 修改/etc/oratab文件,由于OS不一样,这里修改的位置也不一样,如果SUN /var/opt/oracle/oratab,IBM的/etc/oratab
- [root@11gdg ~]# echo "htz:/u01/app/oracle/product/11.2.0/db_1:N">>/etc/oratab
- [root@11gdg ~]# tail -1 /etc/oratab
- htz:/u01/app/oracle/product/11.2.0/db_1:N
备库的相关操作到此就差不多,到了duplicate的时候了。
|
评论暂时关闭