http://www.newbooks.com.cn/info/136239.html
一、 环境配置
primary: IP:192.168.0.120
CPU:2个Intel(R) Xeon(TM) CPU 2.80GHz (HT)
Mem:2G Swap:4G
Disk:130G DB:
Oracle Database 10g Enterprise Edition Release 10.1.0.3.0 - Prod OS:Linux oracle 2.4.21-20.ELsmp #1 SMP
standby: IP:192.168.0.101 Cup:2个Intel(R) Xeon(TM) CPU 2.40GHz (HT) Mem:2G Swap:2G Disk:66G DB:
Oracle Database 10g Enterprise Edition Release 10.1.0.3.0 - Prod OS:Linux boss-3 2.4.21-15.ELsmp #1 SMP Primary
为正在使用的生产数据库,standby安装oracle软件,但不建立数据库。正在装载数据……
二、 建立物理备用数据库
1. 准备主库的oracle环境:编辑oracle用户的/HOME/.bash_profile文件,oracle相关环境变量如下:
ORACLE_BASE=/u01/app/oracle; export ORACLE_BASE ORACLE_SID=BOSS;
export ORACLE_SID ORACLE_HOME=$ORACLE_BASE/product/10.1.0/Db_1;
export ORACLE_HOME
export PATH=$ORACLE_HOME/bin:$PATH:
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/usr/lib DISPLAY=10.1.9.59:0.0;
export DISPLAY NLS_LANG=american_america.ZHS16CGB231280; export NLS_LANG
2. 修改主库为归档模式建立归档目录:
mkdir -p /u02/oradata/BOSS/arch
修改归档模式:
archive log list; create pfile from spfile;
编辑$ORACLE_HOME/dbs/initBOSS.ora 添加下面一行 log_archive_dest_1='location=/u02/oradata/BOSS/arch'
sqlplus /nolog conn sys as sysdba
shutdown immediate;
create spfile from pfile;
startup nomount; alter database mount;
alter database archivelog;
alter database open;
3. 对主数据库做一次完整热备份,获得备用数据库数据 RMAN>connect target
RMAN> backup database format='/home/oracle/%U_%s.bak';
RMAN> sql "Alter System Archive Log Current";
RMAN> Backup filesperset 10 ArchiveLog all format='/home/oracle/%U_%s.bak';
cd /home/oracle scp *.bak 192.168.0.101:/home/oracle/
4. 在standby服务器准备环境与primary相同编辑oracle用户的$HOME/.bash_profile文件,oracle相关环境变量如下:
ORACLE_BASE=/u01/app/oracle;
export ORACLE_BASE ORACLE_SID=BOSS;
export ORACLE_SID ORACLE_HOME=$ORACLE_BASE/product/10.1.0/Db_1;
export ORACLE_HOME export PATH=$ORACLE_HOME/bin:$PATH:
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/usr/lib DISPLAY=10.1.9.59:0.0;
export DISPLAY NLS_LANG=american_america.ZHS16CGB231280;
export NLS_LANG
5. 准备相应目录,如日志文件路径,归档路径,参数文件路径,数据文件准备存放路径等
$mkdir -p /u02/oradata/BOSS
$mkdir -p /u02/oradata/BOSS/arch
$mkdir -p $ORACLE_BASE/admin/BOSS
$mkdir -p $ORACLE_BASE/admin/BOSS/bdump
$mkdir -p $ORACLE_BASE/admin/BOSS/cdump
$mkdir -p $ORACLE_BASE/admin/BOSS/udump
6. 建立备用数据库参数文件主库的参数如下:
BOSS.__db_cache_size=339738624
BOSS.__java_pool_size=33554432
BOSS.__large_pool_size=4194304
BOSS.__shared_pool_size=218103808
*.background_dump_dest='/u01/app/oracle/admin/BOSS/bdump'
*.compatible='10.1.0.2.0'
*.control_files='/u02/oradata/BOSS/control01.ctl','/u02/oradata/BOSS/control02.ctl','/u02/oradata/BOSS/control03.ctl'
*.core_dump_dest='/u01/app/oracle/admin/BOSS/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='BOSS'
*.db_recovery_file_dest='/u01/app/oracle/flash_recovery_area'
*.db_recovery_file_dest_size=2147483648
*.db_writer_processes=4
*.dispatchers='(PROTOCOL=TCP) (SERVICE=BOSSXDB)'
*.global_names=FALSE
*.java_pool_size=32M
*.job_queue_processes=10
*.license_max_users=250
*.log_archive_dest_1='location=/u02/oradata/BOSS/arch'
*.log_archive_dest_2='SERVICE=dbstandby LGWR'
*.open_cursors=300
*.pga_aggregate_target=199229440
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=598736896
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/u01/app/oracle/admin/BOSS/udump'
*.utl_file_dir='/u01/app/oracle/admin/BOSS/bdump'
与主数据库不一样的参数如下:
#standby database parameter
standby_file_management=AUTO
remote_archive_enable=TRUE
standby_archive_dest='/u02/oradata/BOSS/arch'
fal_server='DBPRIMARY'
fal_client='DBSTANDBY'
7. 从主服务器拷贝口令文件到备用服务器
$cd $ORACLE_HOME/dbs/
$scp orapwBOSS 192.168.0.101: /u01/app/oracle/product/10.1.0/Db_1/dbs
8. 配置网络连接修改主服务名:
/u01/app/oracle/product/10.1.0/Db_1/network/admin/listener.ora文件如下:
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC)) )
(ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = oracle)(PORT = 1521)) ) ) )
SID_LIST_LISTENER =
(SID_LIST = (SID_DESC = (SID_NAME = PLSExtProc) (ORACLE_HOME = /u01/app/oracle/product/10.1.0/Db_1)
(PROGRAM = extproc) )
(SID_DESC = (GLOBAL_DBNAME = BOSS) (ORACLE_HOME = /u01/app/oracle/product/10.1.0/Db_1) (SID_NAME = BOSS) ) )
LISTENERDB = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.120)(PORT = 1522)) ) ) )
SID_LIST_LISTENERDB = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = BOSS)
(ORACLE_HOME = /u01/app/oracle/product/10.1.0/Db_1) (SID_NAME = BOSS) ) )
$lsnrctl start
$lsnrctl status
查看监听状态. 修改主服务器的/u01/app/oracle/product/10.1.0/Db_1/network/admin/tnsnames.ora文件如下:
BOSS = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = oracle)(PORT = 1521)) (
CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = BOSS) ) )
EXTPROC_CONNECTION_DATA = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC)) )
(CONNECT_DATA = (SID = PLSExtProc) (divSENTATION = RO) ) )
DBPRIMARY = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.120)(PORT = 1521)) )
(CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = BOSS) ) )
DBSTANDBY = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.101)(PORT = 1522)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = BOSS) ) )
修改备用服务器的 /u01/app/oracle/product/10.1.0/Db_1/network/admin/listener.ora文件如下:
LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.101)(PORT = 1521)) ) ) )
SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = BOSS) (ORACLE_HOME = /u01/app/oracle/product/10.1.0/Db_1) (SID_NAME = BOSS) ) )
LISTENERDB = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.101)(PORT = 1522)) ) ) )
SID_LIST_LISTENERDB = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = BOSS) (ORACLE_HOME = /u01/app/oracle/product/10.1.0/Db_1) (SID_NAME = BOSS) ) )
修改备用服务器的 /u01/app/oracle/product/10.1.0/Db_1/network/admin/tnsnames.ora文件如下:
DBPRIMARY = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.120)(PORT = 1521)) )
(CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = BOSS) ) )
DBSTANDBY = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.101)(PORT = 1522))
(CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = BOSS) ) )
在这里配置两个监听,一个用于主服务器到备用服务器的连接,端口是1522,另外一个用于日后的切换需要,默认端口1521。启动1522 的端口
$lsnrctl start listenerdb
$lsnrctl status listenerdb
查看1522 端口上监听的状态.
测试: 在主和备用机上分别执行 tnsping dbprimary tnsping dbstandby
9. 在主数据库创建备用服务器控制文件
alter database create standby controlfile as '/home/oracle/standby.ctl';
创建后将控制文件cp(rcp or scp)到备用数据库所在的控制文件目录下。
如$ scp control01.ctl 192.168.0.101:/u02/oradata/BOSS/
cp /u02/oradata/BOSS/control01.ctl /u02/oradata/BOSS/control02.ctl cp /u02/oradata/BOSS/control01.ctl /u02/oradata/BOSS/control03.ctl
10. 启动备用数据库
conn sys as sysdba
create spfile from pfile;
startup nomount;
alter database mount standby database;
恢复数据库:
RMAN> connect target;
RMAN> restore database;
RMAN> restore archivelog all;
如果有恢复的日志并想手工恢复,可以运行如下命令
SQL>recover automatic standby database;
如果过程中出现如下类似错误,则可以忽略 ORA-00279: change 50775 generated at 06/08/2004 21:57:21 needed for thread 1 ORA-00289:
suggestion : /u01/oracle/oradata/tbdb/archive/1_5.dbf
ORA-00280: change 50775 for thread 1 is in sequence #5
ORA-00278: log file '/u01/oracle/oradata/tbdb/archive/1_5.dbf' no longer needed for this recovery
ORA-00308: cannot open archived log '/u01/oracle/oradata/tbdb/archive/1_5.dbf'
ORA-27037: unable to obtain file status Linux Error: 2: No such file or directory Additional information:
3 因为最后需要的日志根本没有从主数据库送过来 进入到后台管理恢复状态
SQL>alter database recover managed standby database disconnect from session;
三、采用Lgwr进程传递联日志机的最大性能模式
1. 在备用数据库上创建备用日志
alter database recover managed standby database cancel;
alter database add standby LOGFILE GROUP 5 ('/u02/oradata/BOSS/stdy_redo05.log') size 10m;
alter database add standby LOGFILE GROUP 6 ('/u02/oradata/BOSS/stdy_redo06.log') size 10m;
alter database add standby LOGFILE GROUP 7 ('/u02/oradata/BOSS/stdy_redo07.log') size 10m;
alter database add standby LOGFILE GROUP 8 ('/u02/oradata/BOSS/stdy_redo08.log') size 10m;
alter database recover managed standby database disconnect from session;
2. 修改主库的归档路径
alter system set LOG_ARCHIVE_DEST_2='SERVICE=dbstandby LGWR' scope=both;
另外,如果考虑到以后该库可能被切换到备用数据库,也可以创建同样的备用日志组:
alter database add standby LOGFILE GROUP 5 ('/u02/oradata/BOSS/stdy_redo05.log') size 10m;
alter database add standby LOGFILE GROUP 6 ('/u02/oradata/BOSS/stdy_redo06.log') size 10m;
alter database add standby LOGFILE GROUP 7 ('/u02/oradata/BOSS/stdy_redo07.log') size 10m;
alter database add standby LOGFILE GROUP 8 ('/u02/oradata/BOSS/stdy_redo08.log') size 10m;
四、验证备用服务器是否工作在主库上:
create user test identified by ftp123;
grant connect,resource to test;
conn test/ftp123@primary;
create table test(name varchar2(20));
insert into test values('hi, Data Guard');
commit;
conn / as sysdba;
alter system switch logfile;
查看从库日志以只读方式打开从库查看 insert into test values('hi, Data Guard'); 已经生效。
conn / as sysdba;
alter database recover managed standby database cancel;
alter database open read only;
conn test/ftp123
select * from test;
再次设置从库在恢复模式:
alter database recover managed standby database disconnect from session;
五、日常管理
1. 备用服务器的管理模式与只读模式
(1)启动到管理模式
SQL>shutdown immediate;
SQL>startup nomount;
SQL>alter database mount standby database;
SQL>alter database recover managed standby database disconnect from session;
(2)启动到只读方式
SQL>shutdown immediate;
SQL>startup nomount;
SQL>alter database mount standby database;
SQL>alter database open read only;
(3)如果在管理恢复模式下到只读模式
SQL>recover managed standby database cancel;
SQL>alter database open read only;
这个时候,可以给数据库增加临时数据文件(这个在热备份的时候是没有备份过来的)如
alter tablespace temp add tempfile '/u02/oradata/BOSS/temp01.dbf' size 100M;
(4)从只读方式到管理恢复方式
SQL>recover managed standby database disconnect from session;
2. 备用服务器日志删除备用服务器的日志删除也必须小心,因为如果有些日志还没有被备用服务器应用而该日志被删除的话,
将引起备用数据库无法往下应用新的日志。删除备用服务器的日志的脚本为:
#!/bin/sh
# set env
cd $HOME .
.bash_profile
# start remove cd
$HOME/dbbat grep "Media Recovery Log" $ORACLE_BASE/admin/$ORACLE_SID/bdump/alert_${ORACLE_SID}.log | awk '{print $4}'|sed -e 's/^/rm /' > rmarch log.sh
chmod +x ./rmarchlog.sh
./rmarchlog.sh
cd $ORACLE_BASE/admin/$ORACLE_SID/bdump
cat alert_${ORACLE_SID}.log >>alert_${ORACLE_SID}.log.bak
echo ''>alert_${ORACLE_SID}.log rm -f ./rmarchlog.sh
3. 日志延迟检查备用服务器可能有这样的情况发生,因为日志块逻辑损坏,所以必须对日志应用进行检查,防止日志应用被停止,防患于未然,
当然我们可以手工检查,但是以下脚本则可以实现自动检查(放到cron中)
#!/bin/bash
# set env
cd $HOME .
.bash_profile
# start check
DATE=`date +%Y-%m-%d:%H:%M:%S`
filepath=/u02/oradata/$ORACLE_SID/arch/
logpath=$ORACLE_BASE/admin/$ORACLE_SID/bdump
remotefile=`ssh oracle@192.168.0.120 "ls -t /u02/oradata/BOSS/arch/*|head -1|sed -e 's/.*_1_//g' |sed -e 's/.arc$//g'"`
echo "CHECK TIME:"${DATE} echo echo "remote file : "$remotefile cd $filepath varfile=`ls -t | head -1|sed -e 's/.*_1_//g' |sed -e 's/.arc$//g'`
echo "archive file : "$filepath$varfile cd $logpath varlog=`grep "Media Recovery Log" alert_${ORACLE_SID}.log | awk '{print $4}' | tail -1 |sed -e 's/.*_1_//g' | sed -e 's/.arc$//g'` echo "applice file : "$varlog echo echo >> $HOME/dblog/check_DG_log.log
echo "CHECK TIME:"${DATE} >> $HOME/dblog/check_DG_log.log
echo >> $HOME/dblog/check_DG_log.log echo "remote file : "$remotefile >> $HOME/dblog/check_DG_log.log
echo "archive file : "$filepath$varfile >> $HOME/dblog/check_DG_log.log
echo "applice file : "$varlog >> $HOME/dblog/check_DG_log.log echo >> $HOME/dblog/check_DG_log.log
六、主库与备库的正常切换注意:
Swithover时只能先从Primary切到Standby,再从Standby切到Primary. 以下顺序不能颠倒,如果采用standby redo log的需要注意在切换前在主数据库创建同样的standby redo log。
1.切换之前先要准备init参数文件最简单的办法就是把两个数据库的文件互换,在一个机器上同时保留主数据库的初始化文件与备用数据库的初始化文件。
2. 从Primary切换到standby的脚本: [
oracle@db worksh]$ more swithstandby.sh
#!/bin/bash cd $HOME
. .bash_profile
sqlplus /nolog ALTER DATABASE REGISTER LOGFILE '/u01/oracle/oradata/tbdb/archive/1_87.dbf';
SQL>recover standby database;
(2)如果有活动日志,必须用 alter database recover managed standby database finish;
否则用 alter database recover managed standby database finish skip standby logfile;
这样切换的备用服务器可以避免最小的数据丢失和不用resetlogs,特别是对于用多个备用服务器的时候,该服务器可以马上作为主服务器而不用重新创建备用服务器。
2. 强行切换(激活)这样的切换是以激和备用服务器来完成的,在重新启动数据库的时候,备用机会 resetlogs,
这样会影响到其它备用服务器而且必须重新在主服务器上重新构造备用服务器,一般不建议这样做。
$ more activeprimary.sh
#!/bin/bash
#swith to primary with cancel
cd $HOME
. .bash_profile
#cancel and startup database sqlplus /nolog