|
physic standby configuration 最大性能模式
一，目的
公司最近一个生产库决定采用dataguard的容灾方案，所以我事先配置试验它的性能以及稳定性，供评估和研究之用。本文主要参考了Oracle公司的doc<>。以及网络上的一些配置文章，在此不一一列出。 有困难找民警，有问题看文档，for more information on otn。
二，测试环境准备
primary database
ip: 10.4.247.248
hardware：P4 2.8g 1G内存 80G硬盘
OS:linux as 4
keneral:2.6.9-22.EL
standby database
ip: 10.4.247.248
hardware：P4 2.8g 1G内存 80G硬盘
OS:linux as 4
keneral:2.6.9-22.EL
网络：LAN 100M网卡/交换机
oracle software structure
version：Release 10.2.0.1.0
edition: Enterprise
directory:default instal
0FA,FILESYSTEM
primary 的环境变量：
export DISPLAY=:0.0
export ORACLE_OWNER=oracle
export ORACLE_BASE=/opt/oracle
export ORACLE_HOME=$ORACLE_BASE/product/10.2.0.1
export ORA_CRS_HOME=$ORACLE_BASE/product/crs
export ORACLE_PATH=$ORACLE_BASE/common/oracle/sql:.:$ORACLE_HOME/rdbms/admin
export ORACLE_SID=test
export PATH=.:${PATH}:$HOME/bin:$ORACLE_HOME/bin:/usr/bin:/bin:/usr/bin/X11:/usr
/local/bin:$ORACLE_BASE/common/oracle/bin
export ORACLE_TERM=xterm
export TNS_ADMIN=$ORACLE_HOME/network/admin
export ORA_NLS10=$ORACLE_HOME/nls/data
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$ORACLE_HOME/oracm/lib:/lib:/usr/lib:/us
r/local/lib
export CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib:$ORA
CLE_HOME/network/jlib
export THREADS_FLAG=native
注意：standby库的环境和primary一致，standby只安装数据库软件，primary库正常安装，启动实例。本次试验，将clone一个primary库来生成standby库。在这里提一下，oracle 10g for linux安装比9i已经简单了很多，只要是较新的os版本，注意一下环境变量，用户用户组，基本都一气呵成。（当年装9i for linux，装了一个通宵...）
三，配置过程
force Logging
以sysdba登录primary库,open状态。
设置primary库为force Logging模式(为了便于切换，建议standby库也设置为force logging),这样所有的改变都会放入重做日志中，确保了可靠的恢复。
SQL> ALTER DATABASE FORCE LOGGING;
创建密码文件
如果你的数据库还没有使用密码文件管理方式的话，那么创建它。
cd $ORACLE_HOME/dbs
orapwd file=orapwtest password=test
SQL>alter system set remote_login_passwordfile=exclusive scope=spfile;
Oracle 10g的安装后，默认是使用密码文件验证的。文件格式一般是orapw
启用primary的归档
如果启用了flash_recovery的话，oracle10g默认安装的归档备份路径是
USE_DB_RECOVERY_FILE_DEST，使用DB_RECOVERY_FILE_DEST，也就是在/opt/oracle/flash_recovery_area/TEST/archivelog/下面。
按照下面的步骤：
SQL>create pfile from spfile;
SQL>shutdown immediate;
在$ORACLE_HOME/dbs中找到inittest.ora文件，增加一行
*.log_archive_dest_1='location=/opt/oracle/flash_recovery_area/TEST/archivelog'
SQL>startup mount;
SQL> create spfile from pfile;
File created.
SQL> alter database archivelog;
Database altered.
SQL> alter database open;
Database altered.
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /opt/oracle/flash_recovery_area/TEST/archivelog
Oldest online log sequence 41
Next log sequence to archive 43
Current log sequence 43
在主库上创建备用日志 （为切换而用）
alter database recover managed standby database cancel;
alter database add standby LOGFILE GROUP 5 ('/opt/oracle/flash_recovery_area/TEST/onlinelog/stdy_redo05.log','/opt/oracle/oradata/TEST/onlinelog/stdy_redo05.log') size 50m;
alter database add standby LOGFILE GROUP 6 ('/opt/oracle/flash_recovery_area/TEST/onlinelog/stdy_redo06.log','/opt/oracle/oradata/TEST/onlinelog/stdy_redo06.log') size 50m;
alter database add standby LOGFILE GROUP 7 ('/opt/oracle/flash_recovery_area/TEST/onlinelog/stdy_redo07.log','/opt/oracle/oradata/TEST/onlinelog/stdy_redo07.log') size 50m;
alter database recover managed standby database disconnect from session;
开始clone数据库
再次启动primary到mount状态。
用RMAN作一次全库备份，采用默认目录
$ORACLE_HOME/bin/rman
RMAN> connect target
connected to target database: TEST (DBID=1915186580, not open)
RMAN> backup database;
备份完成，备份文件如下：
[oracle@dbrac 2007_07_05]$ pwd
/opt/oracle/flash_recovery_area/TEST/backupset/2007_07_05
[oracle@dbrac 2007_07_05]$ ll
总用量 678200
-rw-rw---- 1 oracle oracle 7143424 7月 5 15:21 o1_mf_ncsnf_TAG20070705T151935_38s6z9c5_.bkp
-rw-rw---- 1 oracle oracle 686637056 7月 5 15:21 o1_mf_nnndf_TAG20070705T151935_38s6w7vc_.bkp
创建备库的控制文件
SQL> alter database create standby controlfile as '/home/oracle/controlbak.ctl';
Database altered.
拷贝文件到备库
1.拷贝/home/oracle/controlbak.ctl到备库，并改名。位置和名称参考，初始化文件的参数
*.control_files='/opt/oracle/oradata/TEST/controlfile/o1_mf_2zb28n0y_.ctl','/opt/oracle/flash_recovery_area/TEST/controlfile/o1_mf_2zb28n6f_.ctl'
2.利用FTP，拷贝RMAN备份到standby的相同目录
拷贝/opt/oracle/flash_recovery_area/TEST/backupset/2007_07_05目录和文件到standby相同位置。
3.拷贝inittest.ora到standby相同位置。
4.拷贝primary的密码文件到standby的相同位置。
配置网络连接
编辑$ORACLE_HOME/network/admin下面的tnsname.ora和listener.ora文件，配置服务名和监听。
能使用netca最好。
编辑后如下：
primary库的tnsname.ora
# tnsnames.ora Network Configuration File: /opt/oracle/product/10.2.0.1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
TEST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.4.247.248)(PORT = 1521))
(CONNECT_DATA =
(SERVER = SHARED)
(SERVICE_NAME = test)
)
)
DBPRIMARY =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.4.247.248)(PORT = 1521))
(CONNECT_DATA =
(SERVER = SHARED)
(SERVICE_NAME = test)
)
)
DBSTANDBY =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.4.5.125)(PORT = 1522))
)
(CONNECT_DATA =
(SERVICE_NAME = test)
)
)
EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)
primary库的listener.ora
# listener.ora Network Configuration File: /opt/oracle/product/10.2.0.1/network/admin/listener.ora
# Generated by Oracle configuration tools.
# listener.ora Network Configuration File: /opt/oracle/product/10.2.0.1/network/admin/listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /opt/oracle/product/10.2.0.1)
(PROGRAM = extproc)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost.localdomain)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
)
SID_LIST_LISTENERDB =
(SID_LIST =
(SID_DESC =
(SID_NAME = test)
(ORACLE_HOME = /opt/oracle/product/10.2.0.1)
(GLOBAL_DBNAME = test)
)
)
LISTENERDB =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.4.5.125)(PORT = 1522))
)
)
standby库的tnsname.ora
DBPRIMARY =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.4.247.248)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = test)
)
)
DBSTANDBY =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.4.5.125)(PORT = 1522))
)
(CONNECT_DATA =
(SERVICE_NAME = test)
)
)
EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)
standby库的listener.ora
# listener.ora Network Configuration File: /opt/oracle/product/10.2.0.1/network/admin/listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /opt/oracle/product/10.2.0.1)
(PROGRAM = extproc)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost.localdomain)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
)
LISTENERDB =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost.localdomain)(PORT = 1522))
)
)
在两台机器分别运行
LSNRCTL> start listener
LSNRCTL> start listenerdb
启动监听，查看状态。1521是默认访问端口，1522是dataguard使用的。
分别tnsping
在主机 tnsping dbstandby
在备机 tnsping dbprimary
修改主机和备机的参数文件
在主机参数增加一行
*.log_archive_dest_2='SERVICE=dbstandby LGWR DB_UNIQUE_NAME=test'
启动到mount，创建spfile。
SQL> startup mount pfile='/opt/oracle/product/10.2.0.1/dbs/inittest.ora';
ORACLE instance started.
Total System Global Area 285212672 bytes
Fixed Size 2020224 bytes
Variable Size 142609536 bytes
Database Buffers 138412032 bytes
Redo Buffers 2170880 bytes
Database mounted.
SQL> create spfile from pfile;
因为log_archive_dest_2推迟到备库基本创建后。
在备机参数增加（为以后切换而使用）
*.log_archive_dest_2='SERVICE=dbprimay LGWR DB_UNIQUE_NAME=test'
*.standby_file_management=AUTO
*.remote_archive_enable=TRUE
*.standby_archive_dest='/opt/oracle/flash_recovery_area/TEST/archivelog'
*.fal_server='DBPRIMARY'
*.fal_client='DBSTANDBY'
利用pfile启动备库到nomount状态，创建spfile
进入RMAN进行数据恢复
拷贝备份控制文件到备库，拷贝initXXX.ora在dbs目录，然后
在备库 startup mount pfile='/..../.../initXXX.ora'
$ORACLE_HOME/bin/rman
RMAN> connect target
RMAN> alter database mount;
database mounted
released channel: ORA_DISK_1
RMAN> restore database;
Starting restore at 05-JUL-07
Starting implicit crosscheck backup at 05-JUL-07
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK
Crosschecked 10 objects
Finished implicit crosscheck backup at 05-JUL-07
Starting implicit crosscheck copy at 05-JUL-07
using channel ORA_DISK_1
Crosschecked 3 objects
Finished implicit crosscheck copy at 05-JUL-07
searching for all files in the recovery area
cataloging files...
no files cataloged
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /opt/oracle/oradata/TEST/datafile/o1_mf_system_2zb270w6_.dbf
restoring datafile 00002 to /opt/oracle/oradata/TEST/datafile/o1_mf_undotbs1_2zb2711n_.dbf
restoring datafile 00003 to /opt/oracle/oradata/TEST/datafile/o1_mf_sysaux_2zb270wg_.dbf
restoring datafile 00004 to /opt/oracle/oradata/TEST/datafile/o1_mf_users_2zb2712k_.dbf
restoring datafile 00005 to /opt/oracle/oradata/TEST/datafile/o1_mf_apsdata_2zb32h4w_.dbf
restoring datafile 00006 to /opt/oracle/oradata/TEST/datafile/o1_mf_apsindex_2zb32scy_.dbf
channel ORA_DISK_1: reading from backup piece /opt/oracle/flash_recovery_area/TEST/backupset/2007_07_05/o1_mf_nnndf_TAG20070705T151935_38s6w7vc_.bkp
channel ORA_DISK_1: restored backup piece 1
piece handle=/opt/oracle/flash_recovery_area/TEST/backupset/2007_07_05/o1_mf_nnndf_TAG20070705T151935_38s6w7vc_.bkp tag=TAG20070705T151935
channel ORA_DISK_1: restore complete, elapsed time: 00:01:26
Finished restore at 05-JUL-07
RMAN>
恢复完成。
在备库上创建备用日志
实际上在刚才的恢复中，已经把主库的备用日志恢复出来。
进入后台管理恢复模式
SQL> alter database recover managed standby database disconnect from session;
Database altered.
查看主要库的归档路径
SQL> show parameter LOG_ARCHIVE_DEST_2
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_2 string SERVICE=dbstandby LGWR DB_UNIQ
UE_NAME=test
这里就是日志传递的关键，dbstandby 是tnsname里面配置的local服务，它指导log传递到standby。
切换备库到恢复模式的命令
recover managed standby database disconnect from session;
最后验证配置是否成功
连接主库的一个schema，这里为aps2。
SQL> conn aps2/******;
Connected.
SQL> select * from test;
AREAID LOGICALAREACODE COMPANYNUMBER PHYSICALAREACODE
---------- -------------------- -------------------- --------------------
1 028 51010000 028
2 0833 51110000 0833
3 0817 51130000 0817
4 0831 51150000 0831
5 0826 51160000 0826
6 0813 51030000 0813
7 0835 51310000 0835
8 0837 51320000 0837
9 0836 51330000 0836
10 0834 51340000 0834
11 0827 51370000 0827
AREAID LOGICALAREACODE COMPANYNUMBER PHYSICALAREACODE
---------- -------------------- -------------------- --------------------
12 0833 51380000 0833
13 0815 51390000 0832
14 0812 51040000 0812
15 0830 51050000 0830
16 0838 51060000 0838
17 0816 51070000 0816
18 0839 51080000 0839
19 0825 51090000 0825
19 rows selected.
SQL> insert into test select * from test;
19 rows created.
SQL> commit;
Commit complete.
SQL> alter system switch logfile;
System altered.
SQL> select count(1) from test;
COUNT(1)
----------
38
到standby库去查询，归档是否传递过去
SQL> recover managed standby database cancel;
Media recovery complete.
SQL> alter database open read only;
SQL> select * from test;
AREAID LOGICALAREACODE COMPANYNUMBER PHYSICALAREACODE
---------- -------------------- -------------------- --------------------
1 028 51010000 028
2 0833 51110000 0833
3 0817 51130000 0817
4 0831 51150000 0831
5 0826 51160000 0826
6 0813 51030000 0813
7 0835 51310000 0835
8 0837 51320000 0837
9 0836 51330000 0836
10 0834 51340000 0834
11 0827 51370000 0827
AREAID LOGICALAREACODE COMPANYNUMBER PHYSICALAREACODE
---------- -------------------- -------------------- --------------------
12 0833 51380000 0833
13 0815 51390000 0832
14 0812 51040000 0812
15 0830 51050000 0830
16 0838 51060000 0838
17 0816 51070000 0816
18 0839 51080000 0839
19 0825 51090000 0825
1 028 51010000 028
2 0833 51110000 0833
3 0817 51130000 0817
AREAID LOGICALAREACODE COMPANYNUMBER PHYSICALAREACODE
---------- -------------------- -------------------- --------------------
4 0831 51150000 0831
5 0826 51160000 0826
6 0813 51030000 0813
7 0835 51310000 0835
8 0837 51320000 0837
9 0836 51330000 0836
10 0834 51340000 0834
11 0827 51370000 0827
12 0833 51380000 0833
13 0815 51390000 0832
14 0812 51040000 0812
AREAID LOGICALAREACODE COMPANYNUMBER PHYSICALAREACODE
---------- -------------------- -------------------- --------------------
15 0830 51050000 0830
16 0838 51060000 0838
17 0816 51070000 0816
18 0839 51080000 0839
19 0825 51090000 0825
38 rows selected.
四，注意
listener的配置要确保1521和1522端口都正常监听，并且sqlplus可以连通。否则日志无法传输过去。Oracle的DOC例子，并没有配双监听。
全库恢复的方法很多，最好使用RMAN.
原文：http://valen.blog.ccidnet.com/blog-htm-itemid-158361-do-showone-type-blog-uid-51502.html
|
阅读(455) | 评论(0) | 转发(0) |
