环境信息
操作系统版本:AIX6.1
数据库版本: 11.2.0.3(psu5)
主库为双节点rac
DG部署方案:
1.检查数据库是否支持Data Guard(只有企业版才支持DG)
SQL> select * from v$option where parameter = 'Managed Standby';
2.修改主库为归档模式及force logging状态
1)开启数据库归档模式
SQL> archive log list;
如果未开归档,开启归档模式
alter system set log_archive_dest_2='location=/archlog/egap';
alter system set log_archive_format='egap_%t_%s_%r.arch' scope=spfile; --静态参数,重启后生效
shutdown immediate;
startup mount;
alter database archivelog;
alter database open;
archive log list;
2) 修改数据库为force logging状态
select force_logging from v$database;
SQL> alter database force logging;
Database altered.
3.创建备库pfile文件
在主库上创建pfile,修改,并添加DG备库所有参数,然后传至备库
SQL> create pfile='/data01/pfileegap' from spfile;
1)备库需要添加的参数
DB_UNIQUE_NAME;LOG_ARCHIVE_DEST_1;FAL_SERVER;FAL_CLIENT; STANDBY_FILE_MANAGEMENT=AUTO;DB_FILE_NAME_CONVERT;LOG_FILE_NAME_CONVERT;
remote_login_passwordfile
2)根据pfile中涉及到路径需要提前在备库主机上建好()
如主库*.audit_file_dest='/apps/oracle/admin/egpdcdr/adump'
我们在备库需要建 mkdir -p /apps/oracle/admin/egpdcdr/adump
cd /apps/oracle/admin
chown -R oracle:oinstall egpdcdr
chmod -R 775 egpdcdr
改变前参数文件:
egpdcdb1.__db_cache_size=26776436736
egpdcdb2.__db_cache_size=26776436736
egpdcdb1.__java_pool_size=67108864
egpdcdb2.__java_pool_size=67108864
egpdcdb1.__large_pool_size=67108864
egpdcdb2.__large_pool_size=67108864
egpdcdb1.__oracle_base='/apps/oracle'#ORACLE_BASE set from environment
egpdcdb2.__oracle_base='/apps/oracle'#ORACLE_BASE set from environment
egpdcdb1.__pga_aggregate_target=10334765056
egpdcdb2.__pga_aggregate_target=10334765056
egpdcdb1.__sga_target=30937186304
egpdcdb2.__sga_target=30937186304
egpdcdb1.__shared_io_pool_size=0
egpdcdb2.__shared_io_pool_size=0
egpdcdb1.__shared_pool_size=3825205248
egpdcdb2.__shared_pool_size=3825205248
egpdcdb1.__streams_pool_size=0
egpdcdb2.__streams_pool_size=0
*.audit_file_dest='/apps/oracle/admin/egpdcdb/adump'
*.audit_trail='db'
*.cluster_database=true
*.compatible='11.2.0.0.0'
*.control_files='/data01/egpdcdb/control01.ctl','/data01/egpdcdb/control02.ctl'
*.db_block_size=16384
*.db_domain=''
*.db_name='egpdcdb'
*.diagnostic_dest='/apps/oracle'
egpdcdb1.fal_client='egpdcdb1'
egpdcdb2.instance_number=2
egpdcdb1.instance_number=1
*.log_archive_dest_1='location=/archlog/egpdcdb'
*.log_archive_format='egpdcdb_%t_%s_%r.arc'
*.open_cursors=300
*.pga_aggregate_target=10307502080
*.processes=2000
*.remote_listener='egpdcdb-sip:1521'
*.remote_login_passwordfile='exclusive'
*.sessions=2205
*.sga_target=30922506240
egpdcdb2.thread=2
egpdcdb1.thread=1
egpdcdb1.undo_tablespace='UNDOTBS1'
egpdcdb2.undo_tablespace='UNDOTBS2'
改变后参数文件:
*.audit_file_dest='/apps/oracle/admin/egpdcdr/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/data01/egpdcdr/control01.ctl','/data01/egpdcdr/control02.ctl'
*.db_block_size=16384
*.db_domain=''
*.db_name='egpdcdb'
*.diagnostic_dest='/apps/oracle'
*.log_archive_format='egpdcdr_%t_%s_%r.arc'
*.open_cursors=300
*.pga_aggregate_target=10307502080
*.processes=2000
#*.remote_listener='egpdcdb-sip:1521'
*.remote_login_passwordfile='exclusive'
*.sessions=2205
*.sga_target=30922506240
*.undo_tablespace='UNDOTBS1'
*.DB_UNIQUE_NAME='egpdcdr'
*.LOG_ARCHIVE_DEST_1='LOCATION=/archlog/egpdcdr valid_for=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=egpdcdr'
*.FAL_SERVER='egpdcdb1','egpdcdb2'
*.FAL_CLIENT='egpdcdr'
*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(egpdcdr,egpdcdb)'
*.STANDBY_FILE_MANAGEMENT='AUTO'
*.DB_FILE_NAME_CONVERT='/data01/egpdcdb','/data01/egpdcdr'
*.LOG_FILE_NAME_CONVERT='/data01/egpdcdb','/data01/egpdcdr'
*.remote_login_passwordfile='EXCLUSIVE'
*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(egpdcdb,egpdcdr)'
##注意参数文件中涉及路径的参数,需根据备库实际情况进行修改.如*.audit_file_dest参数
##注意db_file_name_convert和log_file_name_convert参数指定的路径要存在
##以下是在备库参数文件中添加的DG有关参数:
*.DB_UNIQUE_NAME='egpdcdr'
*.LOG_ARCHIVE_DEST_1='LOCATION=/archlog/egpdcdr valid_for=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=egpdcdr'
*.FAL_SERVER='egpdcdb1','egpdcdb2'
*.FAL_CLIENT='egpdcdr'
*.STANDBY_FILE_MANAGEMENT='AUTO'
*.DB_FILE_NAME_CONVERT='/data01/egpdcdb','/data01/egpdcdr'
*.LOG_FILE_NAME_CONVERT='/data01/egpdcdb','/data01/egpdcdr'
*.remote_login_passwordfile='EXCLUSIVE'
*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(egpdcdb,egpdcdr)'
4)根据修改后的pfile创建备库spfile
export ORACLE_SID=egpdcdr
sqlplus / as sysdba
create spfile from pfile;
--用新生成的spfile看是否能够成功启动实例
4.生成备库的密码文件
scp主库密码文件到备库,并改名
scp orapwegpdcdb oracle@192.168.96.16:/apps/oracle/product/11.2.0.3/db_1/dbs
如果主库没有密码文件,需要新建
orapwd file=/oracle/app/oracle/product/v11.2.0.3/db_1/dbs/orapwegap password=oracle entries=5 ignorecase=y
5.配置主备库监听及net服务
1)listener
--主库
一般建库后都会配置监听我们无需再配置
--备库(因为安装了cluster,所以用的是cluster的监听)
lsnrctl status 查看监听文件位置,并在监听文件中加入如下类容(注意两个节点都进行配置)
LISTENER_SCAN3=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN3)))) # line added by Agent
LISTENER_SCAN2=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN2)))) # line added by Agent
#LISTENER=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))) # line added by Agent
LISTENER_SCAN1=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN1)))) # line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN1=ON # line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON # line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN2=ON # line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN3=ON # line added by Agent
--注意集群安装完毕以后,上面部分内容在监听中已经存在
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /apps/oracle/product/11.2.0.3/db_1)
(PROGRAM = extproc)
)
(SID_DESC =
(ORACLE_HOME = /apps/oracle/product/11.2.0.3/db_1)
(SID_NAME = egapdb1)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.96.1)(PORT = 1521)(IP = FIRST))
)
)
2)tns
主备库tnsnames.ora文件中加入如下部分:
egpdcdb1 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.96.42)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = egpdcdb) (UR = A)
)
)
egpdcdb2 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.96.43)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = egpdcdb) (UR = A)
)
)
egpdcdr =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.96.18)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = egpdcdr) (UR = A)
)
)
6. 测试网络连接
1)查看监听状态是否正常
lsnrctl status
2) 测试连接串是否能正常连接到指定数据库
tnsping egpdcdb1
tnsping egpdcdb2
tnsping egpdcdr
sqlplus sys/oracle@egpdcdb1 as sysdba
sqlplus sys/oracle@egpdcdb2 as sysdba
sqlplus sys/oracle@egpdcdr as sysdba
7.对主库进行全备,并传输备份文件到备库(这个操作可以提前完成)
nohup /home/oracle/backup_egpdcdb_database.sh &
备份脚本如下:
$ vi /home/oracle/backup_egpdcdb_database.sh
#
ORACLE_BASE=/apps/oracle
ORACLE_HOME=/apps/oracle/product/11.2.0.3/db_1
ORACLE_SID=egpdcdb1
export ORACLE_BASE ORACLE_HOME ORACLE_SID
export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK
export NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS'
export PATH=$ORACLE_HOME/OPatch:$ORACLE_HOME/bin:$JAVA_HOME/bin:$PATH
#--execute rman command
rman target / nocatalog log /home/oracle/backup_egpdcdb_database.log<<EOF
run
{allocate channel c1 type disk maxpiecesize 30G;
allocate channel c2 type disk maxpiecesize 30G;
allocate channel c3 type disk maxpiecesize 30G;
backup as backupset database format '/archlog/backup/egpdcdbfull_%d_%T_%s_%p';
}
run
{allocate channel c1 type disk;
allocate channel c2 type disk connect 'sys/oracle@egpdcdb2';
sql 'alter system archive log current';
sql 'alter system archive log current';
sql 'alter system archive log current';
sql 'alter system archive log current';
sql 'alter system archive log current';
sql 'alter system archive log current';
sql 'alter system archive log current';
sql 'alter system archive log current';
sql 'alter system archive log current';
crosscheck archivelog all;
delete noprompt expired copy;
backup as backupset archivelog all format '/archlog/backup/egpdcdbarch_%d_%T_%s_%p';
}
exit;
EOF
注意:
--注意因为我们的环境把归档放在了本地,所以备份归档的时候要分配一个到另一个节点的通道
(因为/archlog为本地目录,记得要把第二个节点的归档备份也传到备库。当然如果有共享目录,备份集最好放在共享目录下)
--如果空间不够可以用压缩备份
backup as compressed backupset maxsetsize 70G database format '/orastg/rmanbk/11gfull_%d_%T_%s_%p';
8.将主库的备份集传到备库
scp *** oracle@192.168.96.16:/archlog/databak
##注意最好保证备份集在备库的存放路径同主库一致,如果不一致那么需要重新对备份集进行注册;
rman target /
catalog backuppiece '/oradata/orcl11g/arch_ORCL10G_20131102_6_1';
9. 在主库创建备库用控制文件
SQL> alter database create standby controlfile as '/home/oracle/control01.ctl';
Database altered.
##把控制文件scp到备库相应目录下(如果备库指定了多个参数文件就cp多份)
##注意控制文件的备份顺序(要在备份数据库之后备份控制文件,此时空间文件包含了,备份集的信息)
10.将备库启动到mount状态
export ORACLE_SID=egpdcdr
sqlplus / as sysdba
alter database mount standby database;
11.restore备库
脚本如下:
$ vi /home/oracle/restore_egpdcdb_database.sh
#
ORACLE_BASE=/apps/oracle
ORACLE_HOME=/apps/oracle/product/11.2.0.3/db_1
ORACLE_SID=egpdcdr
export ORACLE_BASE ORACLE_HOME ORACLE_SID
export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK
export NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS'
export PATH=$ORACLE_HOME/OPatch:$ORACLE_HOME/bin:$JAVA_HOME/bin:$PATH
#--execute rman command
rman target / nocatalog log /home/oracle/restore_egpdcdb_database.log<<EOF
run
{allocate channel c1 type disk;
allocate channel c2 type disk;
allocate channel c3 type disk;
restore database;
}
exit;
EOF
12.添加standby log
alter database add standby logfile thread 1
GROUP 5('/data01/egpdcdr/stand05.log') SIZE 2G,
GROUP 6('/data01/egpdcdr/stand06.log') SIZE 2G,
GROUP 7('/data01/egpdcdr/stand07.log') SIZE 2G,
GROUP 8('/data01/egpdcdr/stand08.log') SIZE 2G,
GROUP 9('/data01/egpdcdr/stand09.log') SIZE 2G,
GROUP 10('/data01/egpdcdr/stand010.log') SIZE 2G;
--ALTER DATABASE ADD STANDBY LOGFILE THREAD 2 group 3('/oracle/dbs/log1c.rdo','/oracle/dbs/log2c.rdo') SIZE 500M;
alter database drop logfile group 7;--删除日志组
SELECT GROUP#,THREAD#,SEQUENCE#,ARCHIVED,STATUS FROM V$STANDBY_LOG;
检查是否成功创建
standy log 的推荐数目为=(每个线程的日志文件的最大数目+1)×线程最大数目
注意:
##为了日后切换,建议为主库也添加standby log
##注意stnadby log的大小(同主库redolog相同大小)
##注意添加合适数量的standby log
13.配置主库DG参数
主库需要配置的参数为DB_UNIQUE_NAME;LOG_ARCHIVE_CONFIG;LOG_ARCHIVE_DEST_2;REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
*.DB_UNIQUE_NAME=egpdcdr
*.FAL_SERVER=egpdcdr
egmmdb1.FAL_CLIENT=egpdcdb1
egmmdb2.FAL_CLIENT=egpdcdb2
*.STANDBY_FILE_MANAGEMENT=AUTO
*.DB_FILE_NAME_CONVERT='/data01/egpdcdr/','/data01/egpdcdb/'
*.LOG_FILE_NAME_CONVERT='/data01/egpdcdr','/data01/egpdcdb/'
*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(egpdcdr,egpdcdb)'
*.log_archive_dest_1='location=/archlog/egpdcdb'
*.log_archive_dest_2='SERVICE=egpdcdr LGWR ASYNC
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
DB_UNIQUE_NAME=egpdcdr'
alter system set fal_server='egpdcdr';
alter system set fal_client='egpdcdb1' sid='egpdcdb1';
alter system set fal_client='egpdcdb2' sid='egpdcdb2';
alter system set standby_file_management=auto;
alter system set db_file_name_convert='/data01/egpdcdr/','/data01/egpdcdb/' scope=spfile;
alter system set log_file_name_convert='/data01/egpdcdr','/data01/egpdcdb/' scope=spfile;
alter system set log_archive_config='DG_CONFIG=(egpdcdr,egpdcdb)';
alter system set log_archive_dest_2='SERVICE=egpdcdr LGWR ASYNC
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
DB_UNIQUE_NAME=egpdcdr';
注意:
##为了方便以后切主库也应该添加备库所需要的参数
14.恢复备库
recover database;
此步可以省略
15.在备库上启动恢复
启动恢复
alter database recover managed standby database using current logfile disconnect;
alter database recover managed standby database cancel;
##主库切换日志,观察DG能否正常应用日志****切换前检查主备库所有参数
1)在备库查看 data guard 为哪种日志接受方式,以及当前被应用的日志
select process,client_process,sequence#,status from v$managed_standby;
2)在备库查看新的归档日志有没有正常传输过来,并被应用
select THREAD#,SEQUENCE#,ARCHIVED,APPLIED,DELETED,STATUS from v$archived_log order by 1,2;
3)查看主备库的alert日志,是否正常
4)查看延时
alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
select name,value from v$dataguard_stats where name in ('apply lag','apply finish time');
5)查看日志是否有gap
select * from V$ARCHIVE_GAP;