11gRac+single DG

rac基础上搭建单机dg

 

主机系统均是oracle linux 5.8  oracle 11.2.0.2

主库是双节点rac，备库是单节点，主库是asm存储，备库是本地存储

主库为hhtdb1、hhtdb2  备库为hhtdbdg

1、主库、备库配置好监听、tns，传输主库的密码文件到备库

2、主库开启归档模式、开启force logging 模式

alter database archivelog;

alter database force logging;

3、创建pfile，传输pfile到备库

传输：create pfile ='/backup/initoradbdg.ora' from spfile;

      scp ./* oracle@192.168.10.226:/backup/backupdg/

主库原来的pfile为：

*.audit_file_dest='/u01/app/oracle/admin/hhtdb/adump'

*.audit_trail='db'

*.cluster_database=true

*.compatible='11.2.0.0.0'

*.control_files='+DATA/hhtdb/controlfile/current.261.836761885','+DATA/hhtdb/controlfile/current.260.836761885'

*.db_block_size=8192

*.db_create_file_dest='+DATA'

*.db_domain=''

*.db_name='hhtdb'

*.db_recovery_file_dest='+DATA'

*.db_recovery_file_dest_size=4227858432

*.diagnostic_dest='/u01/app/oracle'

*.dispatchers='(PROTOCOL=TCP) (SERVICE=hhtdbXDB)'

hhtdb1.instance_number=1

hhtdb2.instance_number=2

hhtdb1.log_archive_dest_1='LOCATION=+arch1/hhtdb'

hhtdb2.log_archive_dest_1='LOCATION=+arch2/hhtdb'

*.memory_target=27051163648

*.open_cursors=300

*.processes=150

*.remote_listener='hhtdb-scanip:1521'

*.remote_login_passwordfile='exclusive'

hhtdb2.thread=2

hhtdb1.thread=1

hhtdb1.undo_tablespace='UNDOTBS1'

hhtdb2.undo_tablespace='UNDOTBS2'

传输pfile到备库，修改pfile文件；修改为：

*.audit_file_dest='/u01/app/oracle/admin/hhtdbdg/adump'

*.audit_trail='db'

*.cluster_database=false

*.compatible='11.2.0.0.0'

*.control_files='/dgdata/hhtdbdg/control01.ctl'#Restore Controlfile

*.db_block_size=8192

*.db_create_file_dest='/dgdata/hhtdbdg'

*.db_domain=''

*.DB_FILE_NAME_CONVERT='+DATA/hhtdb/datafile','/dgdata/hhtdbdg/datafile'  ---主备数据日志文件路径

*.db_name='hhtdb'

*.db_unique_name='hhtdbdg'

*.diagnostic_dest='/u01/app/oracle'

*.fal_client='hhtdbdg'   ---db unique name

*.fal_server='hhtdb'   ---db unique name

*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(hhtdb,hhtdbdg)'   ---db unique name

*.log_archive_dest_1='LOCATION=/rman/arch   VALID_FOR=(ALL_LOGFILES,ALL_ROLES)   DB_UNIQUE_NAME=hhtdbdg'  ---本库归档目录

*.log_archive_dest_2='SERVICE=hhtdb   LGWR   VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)   DB_UNIQUE_NAME=hhtdb'  ---主库字符串

*.LOG_FILE_NAME_CONVERT='+DATA/hhtdb/onlinelog','/dgdata/hhtdbdg/onlinelog'  ---主备redo日志文件路径

*.memory_target=3221225472

*.open_cursors=300

*.processes=150

*.remote_login_passwordfile='exclusive'

*.standby_file_management='AUTO'

*.thread=1

*.undo_management='AUTO'

*.undo_tablespace='UNDOTBS1'

蓝色为修改参数，红色为增加参数，还有一部分参数去掉了；

 

4、主库用Rman备份：

#rman target /

rman > CONFIGURE CONTROLFILE AUTOBACKUP ON;

rman > CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/rman/ctl%F';

也可以：alter database create standby controlfile as '/backup/standbyctl.ctl';

rman > backup database format '/rman/db%U';

用nfs共享/rman目录给备份主机，备库主机挂载/rman目录到/rman

 

恢复controlfile

restore standby controlfile from '/backup/ctlc-2687307724-20170214-00';

5、恢复数据库

run {

allocate channel c1 type disk;

allocate channel c2 type disk;

allocate channel c3 type disk;

allocate channel c4 type disk;

restore database; 

release channel c1; 

release channel c2;

release channel c3;

release channel c4;

}

=========================================

4、使用duplicate方式恢复备库

在主库主机上操作

rman target / auxiliary sys/oracle@hhtdbdg

如果主库上连接不上备库，那就用备库执行：

rman auxiliary / target  sys/oracle@hhtdb

rman > duplicate target database for standby; (该操作会自动创建standby control到备库，然后mount数据库)

报错之后运行：

rman > run {

set newname for tempfile 1      to      "/dgdata/hhtdbdg/temp_01.dbf";

switch clone tempfile all;

set newname for datafile "+DATA/hhtdb/datafile/users.259.836761729"        to      "/dgdata/hhtdbdg/users_01.dbf" ;

set newname for datafile "+DATA/hhtdb/datafile/undotbs1.258.836761729"     to      "/dgdata/hhtdbdg/undotbs1_01.dbf";

set newname for datafile "+DATA/hhtdb/datafile/undotbs1.258.836761729"     to      "/dgdata/hhtdbdg/undotbs2_01.dbf"

set newname for datafile "+DATA/hhtdb/datafile/sysaux.257.836761729"       to      "/dgdata/hhtdbdg/sysaux_01.dbf"   ;

set newname for datafile "+DATA/hhtdb/datafile/system.256.836761729"       to      "/dgdata/hhtdbdg/system_01.dbf"    ;

set newname for datafile "+DATA/hhtdb/datafile/undotbs2.267.836761981"     to      "/dgdata/hhtdbdg/undotbs2_01.dbf"   ;

restore

check readonly

clone database;

}

或者写个脚本，在后台运行，脚本名为bk.sh，内容为：

/u01/app/oracle/product/11.2.0/dbhome_1/bin/rman  log=/tmp/rman.log <<EOF

connect auxiliary  sys/sitech123@hhtdbdg

connect target sys/sitech123@hhtdb

run {

configure device type disk parallelism 4;

set newname for tempfile 1      to      "/dgdata/hhtdbdg/temp_01.dbf";

switch clone tempfile all;

set newname for datafile "+DATA/hhtdb/datafile/users.259.836761729"        to      "/dgdata/hhtdbdg/users.259.836761729" ;

set newname for datafile "+DATA/hhtdb/datafile/undotbs1.258.836761729"     to      "/dgdata/hhtdbdg/undotbs1.258.836761729";

set newname for datafile "+DATA/hhtdb/datafile/undotbs2.267.836761981"     to      "/dgdata/hhtdbdg/undotbs2.267.836761981";

set newname for datafile "+DATA/hhtdb/datafile/sysaux.257.836761729"       to      "/dgdata/hhtdbdg/sysaux.257.836761729"   ;

set newname for datafile "+DATA/hhtdb/datafile/system.256.836761729"       to      "/dgdata/hhtdbdg/system.256.836761729"    ;

restore

check readonly

clone database;

}

EOF

=====================================

6、等脚本跑完，给备库创建standby logfile group，主库有6个，那就给备库创建7个（因为主库是rac库，所以创建的时候需要添加tread参数，日志大小要跟主库保持一致）

alter database add standby logfile thread 1 group 7 ('/dgdata/hhtdbdg/arch/onlinelog/group7_01.log','/dgdata/hhtdbdg/arch/onlinelog/group7_02.log') size 1024M;

thread 1 的添加4组，thread 2 的添加3组，或者两个都添加4组

特别注意：要给每个节点都要加

alter database add standby logfile thread 1 group 9  size 50M ,group 10 size 50M ,group 11 size 50M;

alter database add standby logfile thread 2 group 12 size 50M ,group 13 size 50M ,group 14 size 50M;

alter database add standby logfile thread 3 group 15 size 50M ,group 16 size 50M ,group 17 size 50M;

alter database add standby logfile thread 4 group 18 size 50M ,group 19 size 50M ,group 20 size 50M;

查看日志情况：select members from v$log;

 

7、主库的添加参数为（两个rac库都要执行）：

alter system set fal_server='hhtdbdg' scope=both sid='*';

alter system set log_archive_config='DG_CONFIG=(hhtdb,hhtdbdg)' scope=both sid='*';

alter system set log_archive_dest_2='SERVICE=hhtdbdg   LGWR ASYNC  VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)   DB_UNIQUE_NAME=hhtdbdg' scope=both sid='*';

alter system set standby_file_management='AUTO' scope=both sid='*';

 

alter database add standby logfile group 6  size 50M ,group 7 size 50M ,group 8 size 50M,group 9 size 50M,group 10 size 50M;

alter system set log_archive_dest_3='SERVICE=rac2 LGWR VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=rac' scope=both;

 

均用alter system 命令添加（建议在最后再添加（备库同步主库之前添加））

添加完之后就可以进行日志文件传输了（如果前边没有修改主库参数，现在修改），

备库： alter database recover managed standby database disconnect from session;

查看主库和备库的alter文件；11g的alter文件路径为：/u01/app/oracle/diag/rdbms/hhtdbdg/hhtdbdg/trace

默认是主库向备库传输归档日志，如果主库传输失败，备库会主动向主库抓取归档日志

查看备库的归档日志有没有传输

11g dataguard新特性： 可以open之后在传输日志：alter database recover managed standby database disconnect from session; （10g不可以）

 

8、打开数据库备库提供查询，开启日志应用

alter database recover managed standby database cancel;

alter database open;

alter database recover managed standby database using current logfile disconnect from session;

 

9、注意验证时切换日志

 

查看日志应用情况

column NAME format a50

set lines 200

select sequence#,name,applied from v$archived_log;