Oracle Dataguard搭建(下)
第六章:adg配置
1、传输密码文件 -->将主库的密码文件传到备库
分发库:
scp $ORACLE_HOME/dbs/orapwxxxxxxxx 目标端ip:$ORACLE_HOME/dbs
2、配置tns文件 -->主库中有备库的tns信息,备库中有主库的tns信息
主库:
vi $ORACLE_HOME/network/admin/tnsnames.ora xxxxxxxx= (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = xx.xx.xx.xx)(PORT = xxxx))-->主库信息(网络连接符、ip、端口) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = xxxxxxxx) ) ) xxxxxxxx_st= (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = xx.xx.xx.xx)(PORT = xxxx))-->备库1(网络连接符、ip、端口) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = xxxxxxxx) ) ) xxxxxxxx_st1= (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = xx.xx.xx.xx)(PORT = xxxx))-->备库2(网络连接符、ip、端口) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = xxxxxxxx) ) ) |
备库:-->与主库相同
cd $ORACLE_HOME/network/admin vi tnsnames.ora xxxxxxxx= (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = xx.xx.xx.xx)(PORT = xxxxxxxx)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = xxxxxxxx) ) ) xxxxxxxx_st = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = xx.xx.xx.xx)(PORT = xxxx)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = xxxxxxxx) ) ) xxxxxxxx_st = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = xx.xx.xx.xx)(PORT = xxxx))-->副本库2(网络连接符、ip、端口) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = xxxxxxx) ) ) |
3、备库开启监听
su - oracle
lsnrctl start
4、测试主库到备库的tns连通性
主库: tnsping xxxxxxxx_st 备库: tnsping xxxxxxxx sqlplus ggs/register@xxxxxxxx |
5、主库检查归档模式
archive log list; -->查看归档目录文职 SQL> select FORCE_LOGGING from v$database; -->检查是否为强制记日志 FOR --- YES alter database force logging; -->如果这里查询出来的是NO,执行以下语句修改为YES |
6、创建数据库相关目录(备库执行)
su - root mkdir /u01/oradata chown -R oracle:oinstall /u01/oradata/ chmod -R 777 /u01/oradata/ mkdir hlsthxff -->与你的实例名相同 cd hlsthxff-->创建数据文件、日志文件、临时文件目录 mkdir datafile onlinelog tempfile chown -R oracle:oinstall /u01/oradata/hlsthxff chmod -R 777 /u01/oradata/hlsthxff ---审计文件目录 su - oracle mkdir -p /u01/app/oracle/admin/xmsthxff/ cd /u01/app/oracle/admin/xmsthxff mkdir adump dpdump hdump pfile ---归档目录 su - oracle cd /goldengate mkdir arch |
7、修改相关参数
主库执行:
alter system set log_archive_config='dg_config=(hlsthxff,hlsthxff_st,hlsthxff_st1)' scope=both sid='*';-->当设置为“dg_config”时,可以最多指定9个唯一数据库名 alter system set LOG_ARCHIVE_DEST_2='SERVICE=hlsthxff LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=hlsthxff_st1 COMPRESSION=ENABLE' scope=both sid='*'; -->SERVICE=备库service_name alter system set LOG_ARCHIVE_DEST_STATE_1=ENABLE scope=both sid='*'; -->不需修改.LOG_ARCHIVE_DEST_STATE_n是LOG_ARCHIVE_DEST的状态参数。enable:默认值,表示允许传输服务;defer:路径有效,暂时不使用该归档路径;alternate:禁止传输,若其他相关的目的地的连接都失败,则他将变成enable; reset:与defer属性类似,若传输目的地之前有过错误,他将清除所有错误信息。 alter system set LOG_ARCHIVE_DEST_STATE_2=ENABLE scope=both sid='*'; -->不许修改,同上 alter system set FAL_SERVER=cqsthxff_st scope=both sid='*'; -->修改为主库的server name alter system set FAL_CLIENT=hlsthxff scope=both sid='*'; -->修改为备库的server name FAL_SERVER和FAL_CLIENT参数的作用:FAL为FETCH ARCHIVE LOG,其值为oracle net service 那么,即tnsnames.ora中的服务名。设置这两个参数,可以用来解决archive gaps。一旦产生了gap,FAL_CLIENT会自动向fal_server请求传输gap的archivelog。设置了这两个参数,就不需要在产生gap时手动向standby注 册归档日志了。所需要做的就是确认主库有这些归档日志,并且主库的控制文件中有这些日志的注册信息。 alter system set STANDBY_FILE_MANAGEMENT=auto scope=both sid='*'; -->STANDBY_FILE_MANAGEMENT参数值为auto和manual。auto:主库添加数据文件,备库会根据db_file_name_convert参数自动将数据文件创建到正确路径;manual:不能自动创建,需手动创建 |
8、编辑参数文件
备库执行:
vi $ORACLE_HOME/dbs/inithlsthxff.ora -->此处参数文件的名字为initSID.ora,内容可参考分发库 *.audit_trail='FALSE' *.client_result_cache_size=1048576 *.compatible='11.2.0.0.0' -->修改,数据库版本,启动数据库时可以看到 *.db_block_size=8192 *.db_domain='' *.db_files=10000 *.db_writer_processes=8 *.deferred_segment_creation=FALSE *.diagnostic_dest='/u01/app/oracle' *.job_queue_processes=300 *.log_archive_format='%t_%s_%r.dbf' *.open_cursors=2000 *.processes=2600 *.remote_login_passwordfile='exclusive' *.sessions=2600 *.standby_file_management='AUTO' *.undo_retention=1800 *.undo_tablespace='UNDOTBS1' *.audit_file_dest='/u01/app/oracle/admin/xmsthxff/adump' -->修改,记录审计信息;bdump:后台进程trace和alert log,也就是alert_sid.log也存在这个目录中;cdump:core trace,一般用来日志应用程序,一些控制文件的dump文件;dpdump:存放一些登录信息的udump *.control_files='/u01/app/oracle/product/11.2.0/db_1/dbs/control01.ctl','/u01/app/oracle/product/11.2.0/db_1/dbs/control02.ctl' *.db_name='xmsthxff'-->修改,show parameter name可查 *.db_unique_name='xmsthxff_st'-->修改,同上 *.fal_client='xmsthxff_st'-->修改,备库service name *.fal_server='xmsthxff'-->修改,主库server name *.log_archive_config='dg_config=(xmsthxff,xmsthxff_st)'-->修改,主库、本备库的数据库db_unique_name名。该参数在主库中是(主库名、备1、备2) *.log_archive_dest_1='location=/goldengate/arch/ valid_for=(all_logfiles,all_roles) db_unique_name=xmsthxff_st'-->修改,备库归档日志的存放位置 *.log_archive_dest_2='SERVICE=xmsthxff LGWR ASYNC valid_for=(online_logfiles,primary_roles) db_unique_name=xmsthxff'------修改为备库的信息------ *.log_file_name_convert='+DATA/hlsthxff/onlinelog/','/u01/oradata/hlsthxff/onlinelog/'-->第一个为主库的online日志存放路径(select group#,member from v$logfile; ),后一个为备库的online日志存放路径(select group#,member from v$logfile;) *.db_file_name_convert='+DATA/hlsthxff/datafile/','/u01/oradata/hlsthxff/datafile/','+DATA/hlsthxff/tempfile/','/u01/oradata/hlsthxff/tempfile/'-->修改,第1个和第2个为主备库的数据文件(select name from v$datafile;) ,第3个和第4个为主备库的临时文件(select NAME from v$tempfile;) *.pga_aggregate_target=4G *.service_names='hlsthxff'-->修改,show parameter name可见 *.sga_target=10G |
更改主备库数据文件对应关系
alter system set db_file_name_convert='+DATA/halthxff/datafile, /u01/app/oracle/oradata/halthxff, +DATA/halthxff/tempfile, /u01/app/oracle/oradata/halthxff/tempfile,+FRA/halthxff/tempfile,/u01/app/oracle/oradata/halthxff/tempfile' scope=both; 注意: --log_file_name_convert、db_file_name_convert两个参数的值需要根据查询结果而定 log_file_name_convert 参数值的查询语句: 查询日志文件的路径 select group#,member from v$logfile; db_file_name_convert 参数值的查询语句: 查询数据文件的路径: select name from v$datafile; 查询临时文件的路径: select NAME from v$tempfile; |
9、备库还原控制文件
sqlplus / as sysdba 将数据库启到nomount状态: startup nomount -->有参数文件就能启动到nomount状态 exit 使用rman连接到数据库: rman target / 还原控制文件 restore standby controlfile from '/u01/rman/ctrl_st'; 把数据库起到mount状态: alter database mount; -->有控制文件能启动到mount状态 主库与备库存储备份集路径不一致时,执行以下命令 catalog start with '/u01/rman/'; -->备库的备份文件存放路径 |
8、还原数据文件
编辑还原脚本:
vi /u01/rman/restore.sh #!/bin/ksh export ORACLE_BASE=/u01/app/oracle export ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1 export NLS_LANG=AMERICAN_AMERICA.AL32UTF8 export ORACLE_SID=hlsthxff export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/usr/lib export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:$PATH export PS1=`hostname`:'$PWD'"$" rman target / log=/u01/rman/rman_restore_`date +%Y%m%d`.log <<EOF run{ allocate channel ch1 type disk; allocate channel ch2 type disk; allocate channel ch3 type disk; allocate channel ch4 type disk; allocate channel ch5 type disk; allocate channel ch6 type disk; allocate channel ch7 type disk; allocate channel ch8 type disk; allocate channel ch9 type disk; allocate channel ch10 type disk; set newname for database to '/u01/oradata/hlsthxff/datafile/%b'; restore database; switch datafile all; release channel ch1; release channel ch2; release channel ch3; release channel ch4; release channel ch5; release channel ch6; release channel ch7; release channel ch8; release channel ch9; release channel ch10; } exit EOF |
给还原脚本添加执行权限:
chmod +x /u01/rman/restore.sh |
后台运行还原脚本:
nohup sh /u01/rman/restore.sh >/u01/rman/restore.log& |
查看还原日志确保还原过程中无error
tail -f /u01/rman/restore.log----查看带日期的log |
查看还原进度:
SELECT SID,OPNAME,SERIAL#,CONTEXT,SOFAR,TOTALWORK, ROUND(SOFAR/TOTALWORK*100,2) "%_COMPLETE"FROM V$SESSION_LONGOPS where opname like 'RMAN%' and opname not like '%aggregate%' and totalwork != 0 and sofar <> totalwork order by '%_complete' desc; |
9、添加standby日志组
主库查看分发库日志组数:
sqlplus / as sysdba set line 300 col member for a60 select group#,member from v$logfile; -->为了知道命名方式吗? select * from v$log; |
备库添加日志组:
主库日志个数+1,(单节点+1,(双节点+1)*2,)SIZE相同 GROUP#组号唯一 -->黑龙江XX为(15+1)*2个
alter database add standby logfile thread 1 group 16 '/u01/oradata/hlsthxff/onlinelog/redo16.log' size 1024m; ........... alter database add standby logfile thread 1 group 32 '/u01/oradata/hlsthxff/onlinelog/redo32.log' size 1024m; alter database add standby logfile thread 2 group 33 '/u01/oradata/hlsthxff/onlinelog/redo33.log' size 1024m; .................. alter database add standby logfile thread 2 group 49 '/u01/oradata/hlsthxff/onlinelog/redo49.log' size 1024m; |
创建完毕检查确认:
set line 300 select * from v$standby_log; -->standby_log和log的区别 |
10、检查确认有ARCH和RFS这两个进程
检查语句:
col STATUS for a30 select process,status,thread#,sequence# from v$managed_standby order by 3,1; PROCESS STATUS THREAD# SEQUENCE# --------- ------------------------------ ---------- ---------- ARCH CONNECTED 0 0 RFS IDLE 0 0 ARCH CLOSING 1 50595 RFS RECEIVING 1 50596 ARCH CLOSING 2 22067 ARCH CLOSING 2 22055 |
11、检查主备库状态,无error即为正确
检查主库状态:
set line 300 col DEST_NAME for a50 col ERROR for a40 col STATUS for a10 Select INST_ID,dest_name,status,error from gv$archive_dest where dest_name='LOG_ARCHIVE_DEST_3'; |
检查备库状态:
set line 300 col DEST_NAME for a50 col ERROR for a40 col STATUS for a10 Select INST_ID,dest_name,status,error from gv$archive_dest where dest_name='LOG_ARCHIVE_DEST_3'; 打开主备库告警日志,确认无error后继续往下进行。 |
12、开启adg归档应用 -->开启adg归档应用意味着出现RFS进程
备库执行:
sqlplus / as sysdba recover managed standby database using current logfile disconnect;-->为什么需要开adg应用才能open库 |
13、检查归档的应用状态,直到APPLIED全部为YES后,关闭adg归档应用,打开数据库,重新打开adg归档应用。
备库执行
检查归档应用状态:
set line 300 col name for a100 select thread#,sequence#, applied,name from v$archived_log; THREAD# SEQUENCE# APPLIED NAME ------ ---------- -------- -------------------------------------------------------------------------- 1 50693 YES +DATA/nxltcxff_st/archivelog/2015_12_04/thread_1_seq_50693.1211.897605081 2 22148 YES +DATA/nxltcxff_st/archivelog/2015_12_04/thread_2_seq_22148.1212.897605083 2 22149 YES +DATA/nxltcxff_st/archivelog/2015_12_04/thread_2_seq_22149.1213.897606881 1 50694 YES +DATA/nxltcxff_st/archivelog/2015_12_04/thread_1_seq_50694.1214.897606881 2 22150 YES +DATA/nxltcxff_st/archivelog/2015_12_04/thread_2_seq_22150.1216.897608679 1 50695 YES +DATA/nxltcxff_st/archivelog/2015_12_04/thread_1_seq_50695.1215.897608679 2 22151 YES +DATA/nxltcxff_st/archivelog/2015_12_05/thread_2_seq_22151.1217.897610477 1 50696 YES +DATA/nxltcxff_st/archivelog/2015_12_05/thread_1_seq_50696.1218.897610477 2 22152 YES +DATA/nxltcxff_st/archivelog/2015_12_05/thread_2_seq_22152.1220.897612275 1 50697 YES +DATA/nxltcxff_st/archivelog/2015_12_05/thread_1_seq_50697.1219.897612275 1 50698 YES +DATA/nxltcxff_st/archivelog/2015_12_05/thread_1_seq_50698.1221.897614073 |
关闭adg归档应用 -->一定要先关闭ADG归档,才能打开数据库吗?
alter database recover managed standby database cancel; |
打开数据库:
ALTER DATABASE OPEN; |
重新打开adg归档应用:
recover managed standby database using current logfile disconnect; |
14、检查数据库状态
select name,open_mode,database_role,db_unique_name from v$database; set line 300 col DEST_NAME for a50 col ERROR for a40 col STATUS for a10 Select INST_ID,dest_name,status,error from gv$archive_dest where dest_name='LOG_ARCHIVE_DEST_2'; select name,value from v$dataguard_stats; select max(sequence#), applied,thread# from v$archived_log group by applied,thread#; |
第七章:测试
测试建表
--主 create table hx_bak.adg_tab (id number,operation varchar2(10)); insert into hx_bak.adg_tab values (1,'create'); commit; select * from hx_bak.adg_tab; --备 select * from hx_bak.adg_tab; --主 drop table hx_bak.adg_tab purge; select * from hx_bak.adg_tab; --备 select * from hx_bak.adg_tab; |
测试创建表空间
--主
select file_name from dba_data_files where rownum < 2; SQL> create tablespace adg_tablsp1 datafile '+DATA' size 1m; Tablespace created. SQL> select FILE_NAME,TABLESPACE_NAME from dba_data_files where TABLESPACE_NAME = 'ADG_TABLSP1'; FILE_NAME -------------------------------------------------------------------------------- TABLESPACE_NAME ------------------------------ +APPDATA/nxltcxff/datafile/adg_tablsp01.dbf ADG_TABLSP DROP TABLESPACE ADG_TABLSP1 INCLUDING CONTENTS AND DATAFILES; --备 SQL> select FILE_NAME,TABLESPACE_NAME from dba_data_files where TABLESPACE_NAME = 'ADG_TABLSP1'; FILE_NAME -------------------------------------------------------------------------------- TABLESPACE_NAME ------------------------------ +DATA/nxltcxff/datafile/adg_tablsp01.dbf |
1、检查临时文件
select file_name ,tablespace_name from dba_temp_files; |
2、创建spfile
create spfile from pfile; shutdown immediate; startup 开启adg归档应用 recover managed standby database using current logfile disconnect; |
3、再次检查数据库状态
select name,open_mode,database_role,db_unique_name from v$database; set line 300 col DEST_NAME for a50 col ERROR for a40 col STATUS for a10 Select INST_ID,dest_name,status,error from gv$archive_dest where dest_name='LOG_ARCHIVE_DEST_2'; select name,value from v$dataguard_stats; select max(sequence#), applied,thread# from v$archived_log group by applied,thread#; |
无报错就OK啦!!!