1.oracle RAC11G 对单机ADG搭建详细文档
来源:互联网 发布:数据库小图标 编辑:程序博客网 时间:2024/06/05 15:39
一个问题:rac对单机ADG搭建完成之后,rac是双节点,假设某一个节点发生故障done机了,或者是将一个节点关机了,然后这个数据库再启动话是否还能起来???起不来。因为一旦重启,之前修改的参数就生效了,启动的时候就会出现参数路径不一致的情况,就会导致这个节点无法启动。有两个解决办法:1.生成一个pfile文件,使用pfile文件启动,但是有弊端2、重新编辑参数文件,清空路径不一致的参数,然后再启动。生产库上如果不能立即重启使参数生效,那就等可以重启的时候在统一重启各个节点的数据库使参数生效就可以了。备库:
standby操作系统oracle版本db_namedb_uniquehostnameoracle_sid rhel-server-6.5-x86_6411.2.0.4rac11grac11gdgrac11gdgrac11gdg主库:RAC操作系统oracle版本db_namedb_uniquehostnameoracle_sid节点1rhel-server-6.5-x86_6411.2.0.4rac11grac11grac11g1rac11g1节点2rhel-server-6.5-x86_6411.2.0.4rac11grac11grac11g2rac11g21、创建物理备库的准备工作,首先保证rac数据库的状态都正常
1[grid@rac11g1 ~]$ lsnrctl status23LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 06-APR-2017 17:00:55Copyright (c) 1991, 2013, Oracle. Allrightsreserved.67Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))
8STATUS of the LISTENER
9------------------------
10Alias LISTENER
11Version TNSLSNR for Linux: Version 11.2.0.4.0 - Production12Start Date 06-APR-2017 16:05:2913Uptime 0 days 0 hr. 55 min. 25 sec14Trace Level off
15Security ON: Local OS Authentication
16SNMP OFF
17Listener Parameter File /u01/11.2.0/grid/network/admin/listener.ora18Listener Log File /u01/app/oracle/diag/tnslsnr/rac11g1/listener/alert/log.xml
19Listening Endpoints Summary...
20(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))
21(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.56.10)(PORT=1521)))22(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.56.26)(PORT=1521)))23Services Summary...
24Service "+ASM" has 1 instance(s).25Instance "+ASM1", status READY, has 1 handler(s) for this service...26Service "rac11g" has 1 instance(s).27Instance "rac11g1", status READY, has 1 handler(s) for this service...28Service "rac11gXDB" has 1 instance(s).29Instance "rac11g1", status READY, has 1 handler(s) for this service...30The command completed successfully
31[grid@rac11g1 ~]$ crsctl status resource -t32--------------------------------------------------------------------------------
33NAME TARGET STATE SERVER STATE_DETAILS
34--------------------------------------------------------------------------------
35Local Resources
36--------------------------------------------------------------------------------
37ora.ARCHDG.dg
38ONLINE ONLINE rac11g1
39ONLINE ONLINE rac11g2
40ora.DATADG.dg
41ONLINE ONLINE rac11g1
42ONLINE ONLINE rac11g2
43ora.LISTENER.lsnr
44ONLINE ONLINE rac11g1
45ONLINE ONLINE rac11g2
46ora.OCR_VOTE.dg
47ONLINE ONLINE rac11g1
48ONLINE ONLINE rac11g2
49ora.asm
50ONLINE ONLINE rac11g1 Started
51ONLINE ONLINE rac11g2 Started
52ora.gsd
53OFFLINE OFFLINE rac11g1
54OFFLINE OFFLINE rac11g2
55ora.net1.network
56ONLINE ONLINE rac11g1
57ONLINE ONLINE rac11g2
58ora.ons
59ONLINE ONLINE rac11g1
60ONLINE ONLINE rac11g2
61ora.registry.acfs
62ONLINE ONLINE rac11g1
63ONLINE ONLINE rac11g2
64--------------------------------------------------------------------------------
65Cluster Resources
66--------------------------------------------------------------------------------
67ora.LISTENER_SCAN1.lsnr
681 ONLINE ONLINE rac11g1
69ora.cvu
701 ONLINE ONLINE rac11g1
71ora.oc4j
721 ONLINE ONLINE rac11g1
73ora.rac11g.db
741 ONLINE ONLINE rac11g1 Open
752 ONLINE ONLINE rac11g2 Open
76ora.rac11g1.vip
771 ONLINE ONLINE rac11g1
78ora.rac11g2.vip
791 ONLINE ONLINE rac11g2
80ora.scan1.vip
811 ONLINE ONLINE rac11g1
10中的命令:
[grid@rac11g1 admin]$ crs_stat -tName Type Target State Host ------------------------------------------------------------ora.ARCHDG.dg ora....up.type ONLINE ONLINE rac11g1 ora.DATADG.dg ora....up.type ONLINE ONLINE rac11g1 ora....ER.lsnr ora....er.type ONLINE ONLINE rac11g1 ora....N1.lsnr ora....er.type ONLINE ONLINE rac11g1 ora....VOTE.dg ora....up.type ONLINE ONLINE rac11g1 ora.asm ora.asm.type ONLINE ONLINE rac11g1 ora.cvu ora.cvu.type ONLINE ONLINE rac11g1 ora.gsd ora.gsd.type OFFLINE OFFLINE ora....network ora....rk.type ONLINE ONLINE rac11g1 ora.oc4j ora.oc4j.type ONLINE ONLINE rac11g1 ora.ons ora.ons.type ONLINE ONLINE rac11g1 ora.rac11g.db ora....se.type ONLINE ONLINE rac11g1 ora....SM1.asm application ONLINE ONLINE rac11g1 ora....G1.lsnr application ONLINE ONLINE rac11g1 ora....1g1.gsd application OFFLINE OFFLINE ora....1g1.ons application ONLINE ONLINE rac11g1 ora....1g1.vip ora....t1.type ONLINE ONLINE rac11g1 ora....SM2.asm application ONLINE ONLINE rac11g2 ora....G2.lsnr application ONLINE ONLINE rac11g2 ora....1g2.gsd application OFFLINE OFFLINE ora....1g2.ons application ONLINE ONLINE rac11g2 ora....1g2.vip ora....t1.type ONLINE ONLINE rac11g2 ora....ry.acfs ora....fs.type ONLINE ONLINE rac11g1 ora.scan1.vip ora....ip.type ONLINE ONLINE rac11g1
1.1、 修改RAC数据库为FORCE LOGGING
1SQL> select force_logging from v$database;
23FOR
4---
5NO
67SQL> alter database force logging;
89Database altered.
1.2、查询online 的redo log
1注:通常我们设置生产库的每个redo大小为512M~2048M之间,并且大于等于三组
2SQL> set linesize 150;
3set pagesize 50;
4column MB format a5;
5column STATUS format a12;
6column MEMBER format a50;
7select l.GROUP#,l.THREAD#,l.BYTES/1024/1024||'MB' MB,l.STATUS, lf.TYPE,lf.MEMBER from v$log l,v$logfile lf where l.GROUP#=lf.GROUP#;
8SQL> SQL> SQL> SQL> SQL>
9GROUP# THREAD# MB STATUS TYPE MEMBER
10---------- ---------- ----- ------------ ------- --------------------------------------------------
111 1 512MB INACTIVE ONLINE +DATADG/rac11g/onlinelog/group_1.263.940529009
122 1 512MB INACTIVE ONLINE +DATADG/rac11g/onlinelog/group_2.262.940529023
133 1 512MB INACTIVE ONLINE +DATADG/rac11g/onlinelog/group_3.261.940529039
145 2 512MB INACTIVE ONLINE +DATADG/rac11g/onlinelog/group_5.268.940259119
156 2 512MB CURRENT ONLINE +DATADG/rac11g/onlinelog/group_6.269.940259139
167 2 512MB INACTIVE ONLINE +DATADG/rac11g/onlinelog/group_7.270.940259155
178 2 512MB INACTIVE ONLINE +DATADG/rac11g/onlinelog/group_8.271.940259169
184 1 512MB CURRENT ONLINE +DATADG/rac11g/onlinelog/group_4.273.940529057
19208 rows selected.
1.3、编辑hosts 文件主机名解析配置
1编辑所有RAC节点的hosts文件:
2[root@rac11g1 ~]# vim /etc/hosts (节点1 同节点2 )
3127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4
4::1 localhost localhost.localdomain localhost6 localhost6.localdomain6
56#rac11g Public IP
7192.168.56.10 rac11g1
8192.168.56.11 rac11g2
9#rac11g Private IP
1010.10.10.20 rac11g1-priv
1110.10.10.21 rac11g2-priv
12#rac11g VIP
13192.168.56.26 rac11g1-vip
14192.168.56.27 rac11g2-vip
15#rac11g SCAN IP
16192.168.56.29 rac11gscan
1718#ADG
19192.168.56.70 db11g1 //备库的ip 以及主机名
2021编辑备库的hosts文件:(与rac中的hosts文件相同)
22[root@db11g1 ~]# vim /etc/hosts
23127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4
24#public IP
25192.168.56.10 rac11g1
26192.168.56.11 rac11g2
27#rac11g Private IP
2810.10.10.20 rac11g1-priv
2910.10.10.21 rac11g2-priv
30#rac11g VIP
31192.168.56.26 rac11g1-vip
32192.168.56.27 rac11g2-vip
33#rac11g SCAN IP
34192.168.56.29 rac11gscan
3536#ADG
37192.168.56.70 db11g1
383940修改完成之后在各个节点上ping 一下检查是否可以ping通
41在rac的连个节点上ping一下备库:
42[root@rac11g1 ~]# ping db11g1
43PING db11g1 (192.168.56.70) 56(84) bytes of data.
4464 bytes from db11g1 (192.168.56.70): icmp_seq=1 ttl=64 time=0.327 ms
4564 bytes from db11g1 (192.168.56.70): icmp_seq=2 ttl=64 time=0.350 ms
4664 bytes from db11g1 (192.168.56.70): icmp_seq=3 ttl=64 time=0.274 ms
4748[root@rac11g2 ~]# ping db11g1
49PING db11g1 (192.168.56.70) 56(84) bytes of data.
5064 bytes from db11g1 (192.168.56.70): icmp_seq=1 ttl=64 time=0.204 ms
5164 bytes from db11g1 (192.168.56.70): icmp_seq=2 ttl=64 time=0.195 ms
5264 bytes from db11g1 (192.168.56.70): icmp_seq=3 ttl=64 time=0.420 ms
5364 bytes from db11g1 (192.168.56.70): icmp_seq=4 ttl=64 time=0.349 ms
54555657在备库上ping一下rac的两个节点:
58[root@db11g1 ~]# ping rac11g1
59PING rac11g1 (192.168.56.10) 56(84) bytes of data.
6064 bytes from rac11g1 (192.168.56.10): icmp_seq=1 ttl=64 time=1.64 ms
6164 bytes from rac11g1 (192.168.56.10): icmp_seq=2 ttl=64 time=0.556 ms
6264 bytes from rac11g1 (192.168.56.10): icmp_seq=3 ttl=64 time=0.243 ms
6364 bytes from rac11g1 (192.168.56.10): icmp_seq=4 ttl=64 time=0.270 ms
64^C
65--- rac11g1 ping statistics ---
664 packets transmitted, 4 received, 0% packet loss, time 3187ms
67rtt min/avg/max/mdev = 0.243/0.678/1.645/0.571 ms
68[root@db11g1 ~]#
69[root@db11g1 ~]# ping rac11g2
70PING rac11g2 (192.168.56.11) 56(84) bytes of data.
7164 bytes from rac11g2 (192.168.56.11): icmp_seq=1 ttl=64 time=1.02 ms
7264 bytes from rac11g2 (192.168.56.11): icmp_seq=2 ttl=64 time=0.269 ms
7364 bytes from rac11g2 (192.168.56.11): icmp_seq=3 ttl=64 time=0.268 ms
1.4、配置备库的监听
1[oracle@db11g1 admin]$ vim listener.ora
2# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
3# Generated by Oracle configuration tools.
45SID_LIST_LISTENER =
6(SID_LIST =
7(SID_DESC =
8(GLOBAL_DBNAME = rac11gdg)
9(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
10(SID_NAME = rac11gdg)
11)
12)
1314LISTENER =
15(DESCRIPTION_LIST =
16(DESCRIPTION =
17(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.70)(PORT = 1521))
18)
19(DESCRIPTION =
20(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
21)
22)
2324ADR_BASE_LISTENER = /u01/app/oracle
252627然后重启监听
28[oracle@db11g1 admin]$ lsnrctl stop
29[oracle@db11g1 admin]$ lsnrctl start
30[oracle@db11g1 admin]$ lsnrctl status
31LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 17-SEP-2017 14:06:30Copyright (c) 1991, 2013, Oracle. All rights reserved.Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.56.70)(PORT=1521)))STATUS of the LISTENER------------------------Alias LISTENERVersion TNSLSNR for Linux: Version 11.2.0.4.0 - ProductionStart Date 17-SEP-2017 14:06:26Uptime 0 days 0 hr. 0 min. 3 secTrace Level offSecurity ON: Local OS AuthenticationSNMP OFFListener Parameter File /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.oraListener Log File /u01/app/oracle/diag/tnslsnr/rac11gdg/listener/alert/log.xmlListening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.56.70)(PORT=1521))) (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))Services Summary...Service "rac11g" has 1 instance(s). Instance "rac11gdg", status UNKNOWN, has 1 handler(s) for this service...The command completed successfully
1.5 配置所有的tnsname.ora 文件
1节点1:
2[root@rac11g1 ~]# su - oracle
3[oracle@rac11g1 ~]$ cd /u01/app/oracle/product/11.2.0/db_1/network/admin/
4[oracle@rac11g1 admin]$ ls
5samples shrept.lst tnsnames.ora
6[oracle@rac11g1 admin]$ vim tnsnames.ora
7# tnsnames.ora.rac11g1 Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora.rac11g1
8# Generated by Oracle configuration tools.
910RAC11G =
11(DESCRIPTION =
12(ADDRESS = (PROTOCOL = TCP)(HOST = rac11gscan)(PORT = 1521))
13(CONNECT_DATA =
14(SERVER = DEDICATED)
15(SERVICE_NAME = rac11g)
16)
17)
18// 注:这里主机名写成rac11gscan ,可以解析两个节点
1920// 添加一个解析备库的解析地址
21RACDG =
22(DESCRIPTION =
23(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.70)(PORT = 1521))
24(CONNECT_DATA =
25(SERVER = DEDICATED)
26(SERVICE_NAME = racdg)
27)
28)
2930配置完成之后tnsping 一下
31[oracle@rac11g1 admin]$ tnsping RACDG
32TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 06-APR-2017 18:22:19
33Copyright (c) 1997, 2013, Oracle. All rights reserved.
34Used parameter files:
35Used TNSNAMES adapter to resolve the alias
36Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.70)(PORT = 1521))
37(CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = racdg)))
38OK (10 msec)
3940[oracle@rac11g1 admin]$ tnsping RAC11G
41TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 06-APR-2017 18:22:27
42Copyright (c) 1997, 2013, Oracle. All rights reserved.
43Used parameter files:
44Used TNSNAMES adapter to resolve the alias
45Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = rac11gscan)(PORT = 1521)) (CONNECT_DATA =
46(SERVER = DEDICATED) (SERVICE_NAME = rac11g)))
47OK (20 msec)
48两个解析都可以ping通
4950节点2:
51[oracle@rac11g2 admin]$ vim tnsnames.ora
52# tnsnames.ora.rac11g2 Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora.rac11g2
53# Generated by Oracle configuration tools.
5455RAC11G =
56(DESCRIPTION =
57(ADDRESS = (PROTOCOL = TCP)(HOST = rac11gscan)(PORT = 1521))
58(CONNECT_DATA =
59(SERVER = DEDICATED)
60(SERVICE_NAME = rac11g)
61)
62)
6364RACDG =
65(DESCRIPTION =
66(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.70)(PORT = 1521))
67(CONNECT_DATA =
68(SERVER = DEDICATED)
69(SERVICE_NAME = racdg)
70)
71)
7273ping一下:
74[oracle@rac11g2 admin]$ tnsping rac11g
75TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 06-APR-2017 18:26:21
76Copyright (c) 1997, 2013, Oracle. All rights reserved.
77Used parameter files:
7879Used TNSNAMES adapter to resolve the alias
80Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = rac11gscan)(PORT = 1521)) (CONNECT_DATA =
81(SERVER = DEDICATED) (SERVICE_NAME = rac11g)))
82OK (0 msec)
8384[oracle@rac11g2 admin]$ tnsping racdg
85TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 06-APR-2017 18:26:24
86Copyright (c) 1997, 2013, Oracle. All rights reserved.
87Used parameter files:
8889Used TNSNAMES adapter to resolve the alias
90Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.70)(PORT = 1521))
91(CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = racdg)))
92OK (10 msec)
9394也没有问题,都可以ping通
9596注意:tnsping能ping通不代表主库的日志可以通过它传到备库,也不代表备库的归档日志可以传到主库,只能说明你
97配置的这个解析串是可以ping通的,并不代表进程会通过这个解析串成功的把想要传输的东西传输过去
9899100备库上就有点不一样了:
101[oracle@db11g1 admin]$ vim tnsnames.ora
102# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora
103# Generated by Oracle configuration tools.
104105RACDG =
106(DESCRIPTION =
107(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.70)(PORT = 1521))
108(CONNECT_DATA =
109(SERVER = DEDICATED)
110(SERVICE_NAME = racdg)
111)
112)
113114RAC11G =
115(DESCRIPTION =
116(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.10)(PORT = 1521))
117(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.11)(PORT = 1521))
118(CONNECT_DATA =
119(SERVER = DEDICATED)
120(SERVICE_NAME = rac11g)
121)
122)
123124ping 一下:
125[oracle@db11g1 admin]$ tnsping racdg
126TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 06-APR-2017 18:33:23
127Copyright (c) 1997, 2013, Oracle. All rights reserved.
128Used parameter files:
129130Used TNSNAMES adapter to resolve the alias
131Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.70)(PORT = 1521)) (CONNECT_DATA =
132(SERVER = DEDICATED) (SERVICE_NAME = racdg)))
133OK (10 msec)
134[oracle@db11g1 admin]$ tnsping rac11g
135TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 06-APR-2017 18:33:30
136Copyright (c) 1997, 2013, Oracle. All rights reserved.
137Used parameter files:
138139Used TNSNAMES adapter to resolve the alias
140Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.10)(PORT = 1521)) (ADDRESS = (PROTOCOL = TCP)
141(HOST = 192.168.56.11)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = rac11g)))
142OK (0 msec)
143也没有问题。
144145需要注意的是:在备库中不推荐使用(ADDRESS = (PROTOCOL = TCP)(HOST = rac11gscan)(PORT = 1521)) 这种方式,因为
这样写的话将来备库连接主库的时候,无法保证归档日志是怎么传输的,因为他是随机分配的。所以还是写成具体的地址2、主库初始化参数检查与设置
2.1、查看主库的 DB_NAME
1SQL> set linesize 150;
2SQL> show parameter DB_NAME;
34NAME TYPE VALUE
5------------------------------------ ----------- ------------------------------
6db_name string rac11g
2.2、查看数据库的唯一名和归档模式
1SQL> show parameter db_unique_name;
23NAME TYPE VALUE
4------------------------------------ ----------- ------------------------------
5db_unique_name string rac11g
6SQL> select db_unique_name,name,log_mode from v$database;
78DB_UNIQUE_NAME NAME LOG_MODE
9------------------------------ --------- ------------
10rac11g RAC11G ARCHIVELOG
11SQL> archive log list;
12Database log mode Archive Mode
13Automatic archival Enabled
14Archive destination +ARCHDG
15Oldest online log sequence 15
16Next log sequence to archive 18
17Current log sequence 18
1819确认一下是否只有这一条归档路径:
20SQL> show parameter archive;
2122NAME TYPE VALUE
23------------------------------------ ----------- ------------------------------
24archive_lag_target integer 0
25log_archive_config string
26log_archive_dest string
27log_archive_dest_1 string LOCATION=+ARCHDG
28log_archive_dest_10 string
29log_archive_dest_11 string
2.3、查看 log_archive_config 参数并进行设置
1SQL> set linesize 150;
23SQL> show parameters LOG_ARCHIVE_CONFIG
4NAME TYPE VALUE
5------------------------------------ ----------- ------------------------------
6log_archive_config string
78SQL> alter system set log_archive_config='dg_config=(racllg,racdg)'; // 这个写的是db_unique_name
910System altered.
1112SQL> show parameters LOG_ARCHIVE_CONFIG;
1314NAME TYPE VALUE
15------------------------------------ ----------- ------------------------------
16log_archive_config string dg_config=(racllg,racdg)
2.4、查看一下控制文件的位置
1SQL> show parameter control_files
23NAME TYPE VALUE
4------------------------------------ ----------- ------------------------------
5control_files string +DATADG/rac11g/controlfile/cur
6rent.260.940258727
2.5、在所有节点查看并设置归档参数
1主库:
2SQL> show parameter log_archive_dest_1;
34NAME TYPE VALUE
5------------------------------------ ----------- ------------------------------
6log_archive_dest_1 string LOCATION=+ARCHDG
7log_archive_dest_10 string
8log_archive_dest_11 string
910SQL> alter system set LOG_ARCHIVE_DEST_1='LOCATION=+ARCHDG VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=rac11g';
1112System altered.
1314SQL> alter system set LOG_ARCHIVE_DEST_2='SERVICE=racdg ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=racdg';
1516System altered.
1718SQL> show parameter LOG_ARCHIVE_DEST_1 //第一条归档路径是本地+ARCHDG
1920NAME TYPE VALUE
21------------------------------------ ----------- ------------------------------
22log_archive_dest_1 string LOCATION=+ARCHDG VALID_FOR=(A
23LL_LOGFILES,ALL_ROLES) DB_UNIQ
24UE_NAME=rac11g
25log_archive_dest_10 string
26log_archive_dest_11 string
27log_archive_dest_12 string
28log_archive_dest_13 string
29log_archive_dest_14 string
30log_archive_dest_15 string
31log_archive_dest_16 string
32log_archive_dest_17 string
33log_archive_dest_18 string
34log_archive_dest_19 string
35SQL> show parameters LOG_ARCHIVE_DEST_2 //第二条归档路径是远端 DB_UNIQUE_NAME=racdg
3637NAME TYPE VALUE
38------------------------------------ ----------- ------------------------------
39log_archive_dest_2 string SERVICE=racdg ASYNC VALID_FOR=
40(ONLINE_LOGFILES,PRIMARY_ROLE)
41DB_UNIQUE_NAME=racdg
42log_archive_dest_20 string
43log_archive_dest_21 string
44log_archive_dest_22 string
45log_archive_dest_23 string
46log_archive_dest_24 string
47log_archive_dest_25 string
48log_archive_dest_26 string
49log_archive_dest_27 string
50log_archive_dest_28 string
51log_archive_dest_29 string
52SQL>
注意的问题:11、注:搭建DG后,若发现主库无法解析LOG_ARCHIVE_DEST_2中备库的service名,修改LOG_ARCHIVE_DEST_2参数,直接指定tns连接字符串,绕过tnsnames.ora文件的解析。3例:
4SQL> alter system set log_archive_dest_2='service="(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = 172.17.0.14)(PORT = 1521))
5(CONNECT_DATA =(SERVER = DEDICATED) (SERVICE_NAME = orcldg)))" ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
6DB_UNIQUE_NAME=orcldg' scope=both;
78System altered.
910112、注:若是10g数据库,必须采用归档模式,即:
12alter system set log_archive_dest_2='service="(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)
13(HOST = 172.17.0.14)(PORT = 1521))(CONNECT_DATA =(SERVER = DEDICATED) (SERVICE_NAME = orcldg)))" ARCH VALID_FOR=
14(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcldg' scope=both;
1516如果不采用这种方式,一旦备库done机,主库也一定会夯住
2.6确认LOG_ARCHIVE_DEST_STATE_1与LOG_ARCHIVE_DEST_STATE_2参数为" enable "
1SQL> show parameters LOG_ARCHIVE_DEST_STATE
23NAME TYPE VALUE
4------------------------------------ ----------- ------------------------------
5log_archive_dest_state_1 string enable
6log_archive_dest_state_10 string enable
7log_archive_dest_state_11 string enable
8log_archive_dest_state_12 string enable
9log_archive_dest_state_13 string enable
10log_archive_dest_state_14 string enable
11log_archive_dest_state_15 string enable
12log_archive_dest_state_16 string enable
13log_archive_dest_state_17 string enable
14log_archive_dest_state_18 string enable
15log_archive_dest_state_19 string enable
16log_archive_dest_state_2 string enable
1718归档路径启用方法:
19SQL> alter system set log_archive_dest_state_2=ENABLE;
20System altered.
21SQL>
2223归档路径禁用方法:
24SQL> alter system set log_archive_dest_state_2=defer;
25System altered.
2.7 、确认REMOTE_LOGIN_PASSWORDFILE参数为"EXCLUSIVE"
1SQL> show parameters REMOTE_LOGIN_PASSWORDFILE
23NAME TYPE VALUE
4------------------------------------ ----------- ------------------------------
5remote_login_passwordfile string EXCLUSIVE
67查看并设置LOG_ARCHIVE_MAX_PROCESSES参,4个或者5个都行
8SQL> show parameters LOG_ARCHIVE_MAX_PROCESSES
910NAME TYPE VALUE
11------------------------------------ ----------- ------------------------------
12log_archive_max_processes integer 4
1314SQL> alter system set LOG_ARCHIVE_MAX_PROCESSES=5;
15System altered.
2.8、设置switch over参数FAL_SERVER,FAL_CLIENT
1注:主库的服务端是备库,客户端是主库;备库的服务端是主库,客户端是备库;不要混淆。
23只在一个节点上执行就可以:
4SQL> alter system set FAL_SERVER=racdg; //备库的tns解析名
56System altered.
78SQL> alter system set FAL_CLIENT=rac11g; //主库的tns解析名
910System altered.
1112SQL> show parameters FAL_
1314NAME TYPE VALUE
15------------------------------------ ----------- ------------------------------
16fal_client string RAC11G
17fal_server string RACDG
2.9、 设置数据文件名,redo log 文件名转换参数(这个是以后做主备库切换的时候用到)
1注:当为备库角色时,上面两个参数对数据文件、redo log 文件名做转换;若主备库的数据库物理结构完全一致,忽略该步骤
23从asmdg转换到文件系统
4SQL> select name from v$datafile;
5NAME
6--------------------------------------------------------------------------------------------------------------------------
7+DATADG/rac11g/datafile/system.256.940258647
8+DATADG/rac11g/datafile/sysaux.257.940258647
9+DATADG/rac11g/datafile/undotbs1.258.940529005
10+DATADG/rac11g/datafile/users.259.940258647
11+DATADG/rac11g/datafile/example.266.940258813
12+DATADG/rac11g/datafile/undotbs2.267.940258987
13+DATADG/rac11g/datafile/undotbs1_1.264.940524699
14157 rows selected.
1617SQL> select member from v$logfile;
1819MEMBER
20--------------------------------------------------
21+DATADG/rac11g/onlinelog/group_1.263.940529009
22+DATADG/rac11g/onlinelog/group_2.262.940529023
23+DATADG/rac11g/onlinelog/group_3.261.940529039
24+DATADG/rac11g/onlinelog/group_5.268.940259119
25+DATADG/rac11g/onlinelog/group_6.269.940259139
26+DATADG/rac11g/onlinelog/group_7.270.940259155
27+DATADG/rac11g/onlinelog/group_8.271.940259169
28+DATADG/rac11g/onlinelog/group_4.273.940529057
29308 rows selected.
3132设置数据文件路径:
33SQL> alter system set DB_FILE_NAME_CONVERT='/oradata/rac11g/','+DATADG/RAC/' scope=spfile;
3435System altered.
3637设置归档日志路径:
38SQL> alter system set LOG_FILE_NAME_CONVERT='/oradata/rac11g/onlinelog/','+DATADG/RAC/onlinelog' scope=spfile;
3940System altered.
2.10 设置备用文件管理方式:1SQL> show parameters STANDBY_FILE_MANAGEMENT
23NAME TYPE VALUE
4------------------------------------ ----------- ------------------------------
5standby_file_management string MANUAL
6SQL> alter system set STANDBY_FILE_MANAGEMENT=AUTO;
78System altered.
910SQL> show parameters STANDBY_FILE_MANAGEMENT
1112NAME TYPE VALUE
13------------------------------------ ----------- ------------------------------
14standby_file_management string AUTO
2.11 重启所有节点的数据库实例,使修改的参数生效(生产库按具体情况选择重启或者忽略)
1注:关闭数据库前,严格检查数据库信息
23确认当前主机:hostname
4确认当前实例:echo $ORACLE_SID
5确认当前数据库唯一名:show parameter db_un
67确认无误后
8关闭所有节点数据库实例:
9SQL> shutdown immediate
10Database closed.
11Database dismounted.
12ORACLE instance shut down.
131415启动所有节点实例:
16SQL> startup
1718ORACLE instance started.
19Total System Global Area 5.3447E+10 bytes
20Fixed Size 2265864 bytes
21Variable Size 1.3556E+10 bytes
22Database Buffers 3.9863E+10 bytes
23Redo Buffers 26480640 bytes
24Database mounted.
25Database opened.
3、对数据库进行全备,并将备份scp到备库
1创建备份目录(主备库目录一致):
2主库:
3[root@rac11g1 ~]# mkdir /home/oracle/backup/
4[root@rac11g1 ~]# ll /home/oracle/
5total 804
6-rw-r--r-- 1 oracle oinstall 40304 Sep 1 11:36 ashrpt_1_0901_1135.html
7-rw-r--r-- 1 oracle oinstall 725903 Sep 1 09:41 awrrpt_1_43_47.html
8drwxr-xr-x 2 root root 4096 Sep 8 00:33 backup
9-rw-r--r-- 1 oracle oinstall 40847 Sep 3 05:22 cis_sequence.txt
10drwxr-xr-x 3 oracle oinstall 4096 Aug 28 16:16 oradiag_oracle
11[root@rac11g1 ~]# chown oracle:oinstall /home/oracle/backup/
12[root@rac11g1 ~]# ll /home/oracle/
13total 804
14-rw-r--r-- 1 oracle oinstall 40304 Sep 1 11:36 ashrpt_1_0901_1135.html
15-rw-r--r-- 1 oracle oinstall 725903 Sep 1 09:41 awrrpt_1_43_47.html
16drwxr-xr-x 2 oracle oinstall 4096 Sep 8 00:33 backup
17-rw-r--r-- 1 oracle oinstall 40847 Sep 3 05:22 cis_sequence.txt
18drwxr-xr-x 3 oracle oinstall 4096 Aug 28 16:16 oradiag_oracle
19备库:
20[root@rac11gdg ~]# mkdir /home/oracle/backup/
21[root@rac11gdg ~]# ll /home/oracle/
22total 4
23drwxr-xr-x 2 root root 4096 Sep 8 20:25 backup
24[root@rac11gdg ~]# chown oracle:oinstall /home/oracle/backup/
25[root@rac11gdg ~]# ll /home/oracle/
26total 4
27drwxr-xr-x 2 oracle oinstall 4096 Sep 8 20:25 backup
2829[oracle@rac11g1 ~]$ vim backup.sh
30#!/bin/bash
3132ORACLE_BASE=/u01/app/oracle
33ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1
34PATH=$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:$PATH
35ORACLE_UNQNAME=rac11g
36export ORACLE_BASE ORACLE_HOME PATH ORACLE_UNQNAME
3738/u01/app/oracle/product/11.2.0/db_1/bin/rman target sys/oracle <<eof
39run{
40allocate channel c1 type disk;
41allocate channel c2 type disk;
42allocate channel c3 type disk;
43backup database format '/home/oracle/backup/%d_%T_%s_%p.dbf';
44sql 'alter system archive log current';
45sql 'alter system archive log current';
46sql 'alter system archive log current';
47backup archivelog all format '/home/oracle/backup/%d_%T_%s_%p.arch';
48backup spfile format '/home/oracle/backup/%d_%T_%s_%p.spf';
49backup current controlfile format '/home/oracle/backup/%d_%T_%s_%p.ctl';
50release channel c1;
51release channel c2;
52release channel c3;
53}
54report obsolete;
55delete obsolete;
56yes
5758exit
59eof
[oracle@rac11g1 ~]$ chmod +x backup.sh
查看一下备份:
[oracle@rac11g1 backup]$ ll -htotal 1.7G-rw-r----- 1 oracle asmadmin 32K Sep 17 15:37 RAC11G_20170917_10_1.arch-rw-r----- 1 oracle asmadmin 96K Sep 17 15:37 RAC11G_20170917_11_1.spf-rw-r----- 1 oracle asmadmin 642M Sep 17 15:35 RAC11G_20170917_1_1.dbf-rw-r----- 1 oracle asmadmin 114M Sep 17 15:38 RAC11G_20170917_12_1.ctl-rw-r----- 1 oracle asmadmin 461M Sep 17 15:35 RAC11G_20170917_2_1.dbf-rw-r----- 1 oracle asmadmin 6.7M Sep 17 15:34 RAC11G_20170917_3_1.dbf-rw-r----- 1 oracle asmadmin 114M Sep 17 15:36 RAC11G_20170917_4_1.dbf-rw-r----- 1 oracle asmadmin 96K Sep 17 15:36 RAC11G_20170917_5_1.dbf-rw-r----- 1 oracle asmadmin 141M Sep 17 15:37 RAC11G_20170917_6_1.arch-rw-r----- 1 oracle asmadmin 35M Sep 17 15:37 RAC11G_20170917_7_1.arch-rw-r----- 1 oracle asmadmin 172M Sep 17 15:37 RAC11G_20170917_8_1.arch-rw-r----- 1 oracle asmadmin 34M Sep 17 15:37 RAC11G_20170917_9_1.arch把备份传到备库:[oracle@rac11g1 backup]$ scp * 192.168.56.70:/home/oracle/backup/oracle@192.168.56.70's password:
ctrback: not a regular file
RAC11G_20170407_18_1.dbf 100% 641MB 18.9MB/s 00:34
RAC11G_20170407_19_1.dbf 100% 464MB 16.0MB/s 00:29
RAC11G_20170407_20_1.dbf 100% 75MB 24.9MB/s 00:03
RAC11G_20170407_26_1.arch 100% 72MB 14.4MB/s 00:05
RAC11G_20170407_27_1.spf 100% 96KB 96.0KB/s 00:00
RAC11G_20170407_28_1.ctl 100% 114MB 12.6MB/s 00:09
72备份一下最新的控制文件:(standby模式的控制文件)
73RMAN> backup current controlfile for standby format '/home/oracle/backup/new_controlfile.bak';
7475Starting backup at 07-APR-17
76using target database control file instead of recovery catalog
77allocated channel: ORA_DISK_1
78channel ORA_DISK_1: SID=941 instance=rac11g1 device type=DISK
79channel ORA_DISK_1: starting full datafile backup set
80channel ORA_DISK_1: specifying datafile(s) in backup set
81including standby control file in backup set
82channel ORA_DISK_1: starting piece 1 at 07-APR-17
83channel ORA_DISK_1: finished piece 1 at 07-APR-17
84piece handle=/home/oracle/backup/new_controlfile.bak tag=TAG20170407T210126 comment=NONE
85channel ORA_DISK_1: backup set complete, elapsed time: 00:00:07
86Finished backup at 07-APR-17
8788将备份的控制文件scp到备库
89[oracle@rac11g1 backup]$ scp new_controlfile.bak 192.168.56.70:/home/oracle/backup/
90oracle@192.168.56.70's password:
91new_controlfile.bak 100% 114MB 22.8MB/s 00:05
4、物理备库的创建
1[oracle@db11g admin]$ rman target / auxiliary sys/oracle@orcldg
2RMAN> duplicate target database for standby from active database nofilenamecheck;
4.1 配置物理备库 oracle 的环境变量1[oracle@db11g1 ~]$ vim .bash_profile
2# .bash_profile
34# Get the aliases and functions
5if [ -f ~/.bashrc ]; then
6. ~/.bashrc
7fi
89# User specific environment and startup programs
1011PATH=$PATH:$HOME/bin
1213export PATH
14export ORACLE_SID=orcl
15ORACLE_BASE=/u01/app/oracle
16ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1
17PATH=$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:$PATH
18ORACLE_UNQNAME=racdg
19export ORACLE_BASE ORACLE_HOME PATH ORACLE_UNQNAME
4.2 在备库中恢复参数文件:
111g 中密码区分大小写
23在rman 中启动到DUMMY实例:
4[oracle@db11g1 dbs]$ rman target / nocatalog
56Recovery Manager: Release 11.2.0.4.0 - Production on Fri Apr 7 19:51:11 2017
78Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
910connected to target database: DUMMY (not mounted)
11using target database control file instead of recovery catalog
1213RMAN> restore spfile from '/home/oracle/backup/RAC11G_20170407_27_1.spf';
1415Starting restore at 07-APR-17
16using channel ORA_DISK_1
1718channel ORA_DISK_1: restoring spfile from AUTOBACKUP /home/oracle/backup/RAC11G_20170407_27_1.spf
19channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete
20Finished restore at 07-APR-17
4.3生成init.ora 文件,并编辑init.ora文件
补充:
SQL> create pfile='/home/oracle/initbak.ora' from spfile='+datadg/rac11g/spfilerac11g.ora';
45File created.
67SQL> create spfile='+datadg/rac11g/spfilerac11g.ora' from pfile='/home/oracle/initb
89File created.
[oracle@db11g1 ~]$ sqlplus / as sysdbaSQL*Plus: Release 11.2.0.4.0 Production on Fri Apr 7 20:16:14 2017Copyright (c) 1982, 2013, Oracle. All rights reserved.Connected to an idle instance.SQL> create spfile from pfile;[oracle@rac11gdg ~]$ cd $ORACLE_HOME/dbs[oracle@rac11gdg dbs]$ lshc_rac11gdg.dat init.ora initrac11gdg.ora lkDUMMY spfilerac11gdg.ora[oracle@rac11gdg dbs]$ vim initrac11gdg.orarac11g2.__db_cache_size=738197504rac11g1.__db_cache_size=754974720rac11g2.__java_pool_size=16777216rac11g1.__java_pool_size=16777216rac11g2.__large_pool_size=33554432rac11g1.__large_pool_size=33554432rac11g2.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environmentrac11g1.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environmentrac11g2.__pga_aggregate_target=402653184rac11g1.__pga_aggregate_target=402653184rac11g2.__sga_target=1207959552rac11g1.__sga_target=1207959552rac11g2.__shared_io_pool_size=0rac11g1.__shared_io_pool_size=0rac11g2.__shared_pool_size=402653184rac11g1.__shared_pool_size=385875968rac11g2.__streams_pool_size=0rac11g1.__streams_pool_size=0*.audit_file_dest='/u01/app/oracle/admin/racdg/adump' //确认这些目录有没有,权限对不对*.audit_trail='db' //再确认一下*.cluster_database=false*.compatible='11.2.0.4.0'*.control_files='/oradata/racdg/current.260.940258727'*.db_block_size=8192*.db_create_file_dest='/oradata/'*.db_domain=''*.db_file_name_convert='+DATADG/RAC11G/','/oradata/rac11g/'*.db_name='rac11g'*.db_unique_name='RACDG'*.diagnostic_dest='/u01/app/oracle'*.dispatchers='(PROTOCOL=TCP) (SERVICE=rac11gXDB)'*.fal_client='racdg'*.fal_server='rac11g'rac11g2.instance_number=2rac11g1.instance_number=1*.log_archive_config='dg_config=(racllg,racdg)'*.log_archive_dest_1='LOCATION=/oradata/arch'*.log_archive_dest_2='SERVICE=rac11g ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=rac11g'*.log_archive_format='racdg_%t_%s_%r.dbf'*.log_file_name_convert='+DATADG/RAC11G/onlinelog','/oradata/racdg/onlinelog'*.open_cursors=300*.pga_aggregate_target=400556032*.processes=1200*.remote_login_passwordfile='exclusive'*.sessions=1325*.sga_max_size=1000M*.sga_target=900M*.standby_file_management='AUTO'rac11g2.thread=2rac11g1.thread=1rac11g2.undo_tablespace='UNDOTBS2'rac11g1.undo_tablespace='UNDOTBS1'必须删除 监听参数,注释不管用
4.4 重新生成spfile 并启动到nomount1[oracle@db11g1 ~]$ sqlplus / as sysdba
23SQL*Plus: Release 11.2.0.4.0 Production on Fri Apr 7 20:54:28 2017
45Copyright (c) 1982, 2013, Oracle. All rights reserved.
67Connected to an idle instance.
89SQL> create spfile from pfile='/u01/app/oracle/product/11.2.0/db_1/dbs/initorcl.ora';
1011File created.
1213SQL> startup nomount
14ORACLE instance started.
1516Total System Global Area 1043886080 bytes
17Fixed Size 2259840 bytes
18Variable Size 822084736 bytes
19Database Buffers 213909504 bytes
20Redo Buffers 5632000 bytes
4.5 恢复standby模式控制文件
1[oracle@db11g1 ~]$ rman target /
23Recovery Manager: Release 11.2.0.4.0 - Production on Sat Apr 8 15:14:58 2017
45Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
67connected to target database: RAC11G (not mounted)
89RMAN> restore standby controlfile from '/home/oracle/backup/new_controlfile.bak';
1011Starting restore at 08-APR-17
12using target database control file instead of recovery catalog
13allocated channel: ORA_DISK_1
14channel ORA_DISK_1: SID=10 device type=DISK
1516channel ORA_DISK_1: restoring control file
17channel ORA_DISK_1: restore complete, elapsed time: 00:00:07
18output file name=/oradata/racdg/current.260.940258727
19Finished restore at 08-APR-17
1恢复出来之后查看一下恢复的路径:
2RMAN> report schema;
34RMAN-06139: WARNING: control file is not current for REPORT SCHEMA
5Report of database schema for database with db_unique_name RACDG
67List of Permanent Datafiles
8===========================
9File Size(MB) Tablespace RB segs Datafile Name
10---- -------- -------------------- ------- ------------------------
111 0 SYSTEM *** /oradata/rac11g/datafile/system.256.940258647
122 0 SYSAUX *** /oradata/rac11g/datafile/sysaux.257.940258647
133 0 UNDOTBS1 *** /oradata/rac11g/datafile/undotbs1.258.940529005
144 0 USERS *** /oradata/rac11g/datafile/users.259.940258647
155 0 EXAMPLE *** /oradata/rac11g/datafile/example.266.940258813
166 0 UNDOTBS2 *** /oradata/rac11g/datafile/undotbs2.267.940258987
177 0 UNDOTBS1_1 *** /oradata/rac11g/datafile/undotbs1_1.264.940524699
1819List of Temporary Files
20=======================
21File Size(MB) Tablespace Maxsize(MB) Tempfile Name
22---- -------- -------------------- ----------- --------------------
231 20 TEMP 32767 /oradata/rac11g/tempfile/temp.265.940258797
1然后查看一下redo 的恢复路径:
2SQL> select member from v$logfile;
34MEMBER
5--------------------------------------------------------------------------------
6/oradata/racdg/onlinelog/group_1.263.940529009
7/oradata/racdg/onlinelog/group_2.262.940529023
8/oradata/racdg/onlinelog/group_3.261.940529039
9/oradata/racdg/onlinelog/group_5.268.940259119
10/oradata/racdg/onlinelog/group_6.269.940259139
11/oradata/racdg/onlinelog/group_7.270.940259155
12/oradata/racdg/onlinelog/group_8.271.940259169
13/oradata/racdg/onlinelog/group_4.273.940529057
14158 rows selected.
adrci 可以查看各种日志:(生产库不要使用这种方法,这种方法相当于vi,如果日志很大会消耗大量内存)4.6 恢复数据文件
1忽略此部分:
记得将主库的口令文件传送到备库上
2编写脚本(脚本不好用,直接手动restore 、recover)
3[oracle@db11g1 backup]$ vim restore.sh
4#!/bin/sh
5source ~/.bash_profile
67rman target / <<eof
8run {
9allocate channel c1 device type disk;
10restore databse;
11recover database until scn 1309123; //这个值在下面查询出来
12release channel c1;
1314[oracle@db11g1 backup]$ chmod +x restore.sh // 添加可执行权限
15[oracle@db11g1 backup]$ ll
16total 1515192
17-rw-r----- 1 oracle oinstall 119275520 Apr 8 16:26 new_controlfile.bak
18-rw-r----- 1 oracle oinstall 672022528 Apr 7 19:49 RAC11G_20170407_18_1.dbf
19-rw-r----- 1 oracle oinstall 486940672 Apr 7 19:49 RAC11G_20170407_19_1.dbf
20-rw-r----- 1 oracle oinstall 78209024 Apr 7 19:49 RAC11G_20170407_20_1.dbf
21-rw-r----- 1 oracle oinstall 75715072 Apr 7 19:49 RAC11G_20170407_26_1.arch
22-rw-r----- 1 oracle oinstall 98304 Apr 7 19:49 RAC11G_20170407_27_1.spf
23-rw-r----- 1 oracle oinstall 119275520 Apr 7 19:49 RAC11G_20170407_28_1.ctl
24-rwxr-xr-x 1 oracle oinstall 171 Apr 817:38 restore.sh
25[oracle@db11g1 backup]$
查询scn:1[oracle@db11g1 ~]$ rman target /
23Recovery Manager: Release 11.2.0.4.0 - Production on Sat Apr 8 17:33:27 2017
45Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
67connected to target database: RAC11G (DBID=1992506470, not open)
8910RMAN> list backup of archivelog all;
1112using target database control file instead of recovery catalog
1314List of Backup Sets
15===================
161718BS Key Size Device Type Elapsed Time Completion Time
19------- ---------- ----------- ------------ ---------------
2025 72.21M DISK 00:00:14 07-APR-17
21BP Key: 25 Status: AVAILABLE Compressed: NO Tag: TAG20170407T194438
22Piece Name: /home/oracle/backup/RAC11G_20170407_26_1.arch
2324List of Archived Logs in backup set 25
25Thrd Seq Low SCN Low Time Next SCN Next Time
26---- ------- ---------- --------- ---------- ---------
271 23 1237492 06-APR-17 1237508 06-APR-17
281 24 1237508 06-APR-17 1241004 07-APR-17
291 25 1241004 07-APR-17 1241483 07-APR-17
301 26 1286888 07-APR-17 1286893 07-APR-17
311 27 1286893 07-APR-17 1287267 07-APR-17
321 28 1287267 07-APR-17 1309070 07-APR-17
331 29 1309070 07-APR-17 1309082 07-APR-17
341 30 1309082 07-APR-17 1309101 07-APR-17
351 31 1309101 07-APR-17 1309123 07-APR-17
362 18 1237477 06-APR-17 1237489 06-APR-17
372 19 1237489 06-APR-17 1237514 06-APR-17
382 20 1237514 06-APR-17 1241001 07-APR-17
392 21 1241001 07-APR-17 1241067 07-APR-17
402 22 1241067 07-APR-17 1287533 07-APR-17
412 23 1287533 07-APR-17 1309067 07-APR-17
422 24 1309067 07-APR-17 1309086 07-APR-17
432 25 1309086 07-APR-17 1309104 07-APR-17
442 26 1309104 07-APR-171309126 07-APR-17
454647取这两个值中较小的一个 1309123
检查一下备份和归档有没有过期的:1[oracle@db11g1 ~]$ rman target /
23Recovery Manager: Release 11.2.0.4.0 - Production on Sat Apr 8 17:42:28 2017
45Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
67connected to target database: RAC11G (DBID=1992506470, not open)
89RMAN> crosscheck backup;
1011using target database control file instead of recovery catalog
12allocated channel: ORA_DISK_1
13channel ORA_DISK_1: SID=11 device type=DISK
14crosschecked backup piece: found to be 'AVAILABLE'
15backup piece handle=/home/oracle/backup/RAC11G_20170407_20_1.dbf RECID=17 STAMP=940707789
16crosschecked backup piece: found to be 'AVAILABLE'
17backup piece handle=/home/oracle/backup/RAC11G_20170407_18_1.dbf RECID=18 STAMP=940707787
18crosschecked backup piece: found to be 'AVAILABLE'
19backup piece handle=/home/oracle/backup/RAC11G_20170407_19_1.dbf RECID=19 STAMP=940707789
20crosschecked backup piece: found to be 'AVAILABLE'
21backup piece handle=/home/oracle/backup/RAC11G_20170407_26_1.arch RECID=25 STAMP=940707903
22crosschecked backup piece: found to be 'AVAILABLE'
23backup piece handle=/home/oracle/backup/RAC11G_20170407_27_1.spf RECID=26 STAMP=940707910
24crosschecked backup piece: found to be 'AVAILABLE'
25backup piece handle=/home/oracle/backup/RAC11G_20170407_28_1.ctl RECID=27 STAMP=940707918
26crosschecked backup piece: found to be 'EXPIRED'
27backup piece handle=/home/oracle/backup/new_controlfile.bak RECID=28 STAMP=940712501
28Crosschecked 7 objects
293031RMAN> delete expired backup;
3233using channel ORA_DISK_1
3435List of Backup Pieces
36BP Key BS Key Pc# Cp# Status Device Type Piece Name
37------- ------- --- --- ----------- ----------- ----------
3828 28 1 1 EXPIRED DISK /home/oracle/backup/new_controlfile.bak
3940Do you really want to delete the above objects (enter YES or NO)? yes
41deleted backup piece
42backup piece handle=/home/oracle/backup/new_controlfile.bak RECID=28 STAMP=940712501
43Deleted 1 EXPIRED objects
444546RMAN> crosscheck archivelog all;
4748released channel: ORA_DISK_1
49allocated channel: ORA_DISK_1
50channel ORA_DISK_1: SID=11 device type=DISK
51specification does not match any archived log in the repository
5253RMAN> delete expired archivelog all;
5455released channel: ORA_DISK_1
56allocated channel: ORA_DISK_1
57channel ORA_DISK_1: SID=11 device type=DISK
58specification does not match any archived log in the repository
596061[oracle@db11g1 ~]$ rman target /
6263Recovery Manager: Release 11.2.0.4.0 - Production on Sat Apr 8 19:04:17 2017
6465Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
6667connected to target database: RAC11G (DBID=1992506470, not open)
6869RMAN> restore database;
7071Starting restore at 08-APR-17
72using target database control file instead of recovery catalog
73allocated channel: ORA_DISK_1
74channel ORA_DISK_1: SID=11 device type=DISK
7576channel ORA_DISK_1: starting datafile backup set restore
77channel ORA_DISK_1: specifying datafile(s) to restore from backup set
78channel ORA_DISK_1: restoring datafile 00001 to /oradata/rac11g/datafile/system.256.940258647
79channel ORA_DISK_1: reading from backup piece /home/oracle/backup/RAC11G_20170407_18_1.dbf
80channel ORA_DISK_1: piece handle=/home/oracle/backup/RAC11G_20170407_18_1.dbf tag=TAG20170407T194306
81channel ORA_DISK_1: restored backup piece 1
82channel ORA_DISK_1: restore complete, elapsed time: 00:00:16
83channel ORA_DISK_1: starting datafile backup set restore
84channel ORA_DISK_1: specifying datafile(s) to restore from backup set
85channel ORA_DISK_1: restoring datafile 00002 to /oradata/rac11g/datafile/sysaux.257.940258647
86channel ORA_DISK_1: restoring datafile 00004 to /oradata/rac11g/datafile/users.259.940258647
87channel ORA_DISK_1: restoring datafile 00007 to /oradata/rac11g/datafile/undotbs1_1.264.940524699
88channel ORA_DISK_1: reading from backup piece /home/oracle/backup/RAC11G_20170407_19_1.dbf
89channel ORA_DISK_1: piece handle=/home/oracle/backup/RAC11G_20170407_19_1.dbf tag=TAG20170407T194306
90channel ORA_DISK_1: restored backup piece 1
91channel ORA_DISK_1: restore complete, elapsed time: 00:00:15
92channel ORA_DISK_1: starting datafile backup set restore
93channel ORA_DISK_1: specifying datafile(s) to restore from backup set
94channel ORA_DISK_1: restoring datafile 00003 to /oradata/rac11g/datafile/undotbs1.258.940529005
95channel ORA_DISK_1: restoring datafile 00005 to /oradata/rac11g/datafile/example.266.940258813
96channel ORA_DISK_1: restoring datafile 00006 to /oradata/rac11g/datafile/undotbs2.267.940258987
97channel ORA_DISK_1: reading from backup piece /home/oracle/backup/RAC11G_20170407_20_1.dbf
98channel ORA_DISK_1: piece handle=/home/oracle/backup/RAC11G_20170407_20_1.dbf tag=TAG20170407T194306
99channel ORA_DISK_1: restored backup piece 1
100channel ORA_DISK_1: restore complete, elapsed time: 00:00:15
101Finished restore at 08-APR-17
102103RMAN> recover database until scn 1309123;
104105Starting recover at 08-APR-17
106using channel ORA_DISK_1
107108starting media recovery
109110channel ORA_DISK_1: starting archived log restore to default destination
111channel ORA_DISK_1: restoring archived log
112archived log thread=1 sequence=28
113channel ORA_DISK_1: restoring archived log
114archived log thread=2 sequence=23
115channel ORA_DISK_1: restoring archived log
116archived log thread=2 sequence=24
117channel ORA_DISK_1: restoring archived log
118archived log thread=1 sequence=29
119channel ORA_DISK_1: restoring archived log
120archived log thread=1 sequence=30
121channel ORA_DISK_1: restoring archived log
122archived log thread=2 sequence=25
123channel ORA_DISK_1: restoring archived log
124archived log thread=1 sequence=31
125channel ORA_DISK_1: restoring archived log
126archived log thread=2 sequence=26
127channel ORA_DISK_1: reading from backup piece /home/oracle/backup/RAC11G_20170407_26_1.arch
128channel ORA_DISK_1: piece handle=/home/oracle/backup/RAC11G_20170407_26_1.arch tag=TAG20170407T194438
129channel ORA_DISK_1: restored backup piece 1
130channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
131archived log file name=/oradata/arch/racdg_1_28_940258729.dbf thread=1 sequence=28
132archived log file name=/oradata/arch/racdg_2_23_940258729.dbf thread=2 sequence=23
133archived log file name=/oradata/arch/racdg_2_24_940258729.dbf thread=2 sequence=24
134archived log file name=/oradata/arch/racdg_1_29_940258729.dbf thread=1 sequence=29
135archived log file name=/oradata/arch/racdg_1_30_940258729.dbf thread=1 sequence=30
136archived log file name=/oradata/arch/racdg_2_25_940258729.dbf thread=2 sequence=25
137archived log file name=/oradata/arch/racdg_1_31_940258729.dbf thread=1 sequence=31
138archived log file name=/oradata/arch/racdg_2_26_940258729.dbf thread=2 sequence=26
139Oracle Error:
140ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
141ORA-01152: file 1 was not restored from a sufficiently old backup
142ORA-01110: data file 1: '/oradata/rac11g/datafile/system.256.940258647'
143144media recovery complete, elapsed time: 00:00:03
145Finished recover at 08-APR-17
恢复完成,查看一下数据文件和归档日志1[oracle@db11g1 datafile]$ ll
2total 1795268
3-rw-r----- 1 oracle oinstall 328343552 Apr 8 19:07 example.266.940258813
4-rw-r----- 1 oracle oinstall 639639552 Apr 8 19:07 sysaux.257.940258647
5-rw-r----- 1 oracle oinstall 786440192 Apr 8 19:07 system.256.940258647
6-rw-r----- 1 oracle oinstall 26222592 Apr 8 19:07 undotbs1_1.264.940524699
7-rw-r----- 1 oracle oinstall 26222592 Apr 8 19:07 undotbs1.258.940529005
8-rw-r----- 1 oracle oinstall 26222592 Apr 8 19:07 undotbs2.267.940258987
9-rw-r----- 1 oracle oinstall 5251072 Apr 8 19:07 users.259.940258647
10[oracle@db11g1 arch]$ ll
11total 19924
12-rw-r----- 1 oracle oinstall 9034752 Apr 8 19:07 racdg_1_28_940258729.dbf
13-rw-r----- 1 oracle oinstall 1536 Apr 8 19:07 racdg_1_29_940258729.dbf
14-rw-r----- 1 oracle oinstall 3072 Apr 8 19:07 racdg_1_30_940258729.dbf
15-rw-r----- 1 oracle oinstall 5632 Apr 8 19:07 racdg_1_31_940258729.dbf
16-rw-r----- 1 oracle oinstall 11331072 Apr 8 19:07 racdg_2_23_940258729.dbf
17-rw-r----- 1 oracle oinstall 2560 Apr 8 19:07 racdg_2_24_940258729.dbf
18-rw-r----- 1 oracle oinstall 2048 Apr 8 19:07 racdg_2_25_940258729.dbf
19-rw-r----- 1 oracle oinstall 6144 Apr 8 19:07 racdg_2_26_940258729.dbf
20[oracle@db11g1 arch]$ pwd
21/oradata/arch
4.7 主备库添加standby redo
1查看一下备库的redo:
2SQL> select group#,thread#,sequence#,status,bytes/1024/1024 from v$log;
34GROUP# THREAD# SEQUENCE# STATUS BYTES/1024/1024
5---------- ---------- ---------- ------------ ---------------
61 1 35 INACTIVE 512
72 1 36 INACTIVE 512
83 1 37 INACTIVE 512
94 1 38 CURRENT 512
105 2 29 INACTIVE 512
116 2 26 INACTIVE 512
127 2 27 INACTIVE 512
138 2 28 INACTIVE 512
14158 rows selected.
16【这里我们看到有8组redo,我们的备库是一个单机,将来备库切换成主库的话,他只用thread1 的redo ,也就是4组,
17所以我们的rac 要添加4+1 组standby redo 。而现在这个单机要为 rac添加(4+1)*2=10组 standby redo,每个节点5组】
在备库上添加主库的standby redo1SQL> select open_mode from v$database;
23OPEN_MODE
4--------------------
5MOUNTED
67SQL> alter database add standby logfile thread 1 group 21 '/oradata/racdg/srl/srl21.log' size 520M;
89Database altered.
1011SQL> c/21/22
121* alter database add standby logfile thread 1 group 22 '/oradata/racdg/srl/srl21.log' size 520M
13SQL> c/l21/l22
141* alter database add standby logfile thread 1 group 22 '/oradata/racdg/srl/srl22.log' size 520M
15SQL> /
1617Database altered.
1819SQL> c/22/23
201* alter database add standby logfile thread 1 group 23 '/oradata/racdg/srl/srl22.log' size 520M
21SQL> c/l22/l23
221* alter database add standby logfile thread 1 group 23 '/oradata/racdg/srl/srl23.log' size 520M
23SQL> /
2425Database altered.
2627SQL> c/21/22
28SP2-0023: String not found.
29SQL> c/23/24
301* alter database add standby logfile thread 1 group 24 '/oradata/racdg/srl/srl23.log' size 520M
31SQL> c/l23/l24
321* alter database add standby logfile thread 1 group 24 '/oradata/racdg/srl/srl24.log' size 520M
33SQL> /
3435Database altered.
3637SQL> c/24/25
381* alter database add standby logfile thread 1 group 25 '/oradata/racdg/srl/srl24.log' size 520M
39SQL> c/l24/l25
401* alter database add standby logfile thread 1 group 25 '/oradata/racdg/srl/srl25.log' size 520M
41SQL> /
4243Database altered.
4445SQL>
46SQL> alter database add standby logfile thread 2 group 26 '/oradata/racdg/srl/srl26.log' size 520M;
4748Database altered.
4950SQL> c/26/27
511* alter database add standby logfile thread 2 group 27 '/oradata/racdg/srl/srl26.log' size 520M
52SQL> c/l26/l27
531* alter database add standby logfile thread 2 group 27 '/oradata/racdg/srl/srl27.log' size 520M
54SQL> /
5556Database altered.
5758SQL> c/27/28
591* alter database add standby logfile thread 2 group 28 '/oradata/racdg/srl/srl27.log' size 520M
60SQL> c/l27/l28
611* alter database add standby logfile thread 2 group 28 '/oradata/racdg/srl/srl28.log' size 520M
62SQL> /
6364Database altered.
6566SQL> c/28/29
671* alter database add standby logfile thread 2 group 29 '/oradata/racdg/srl/srl28.log' size 520M
68SQL> c/l28/l29
691* alter database add standby logfile thread 2 group 29 '/oradata/racdg/srl/srl29.log' size 520M
70SQL> /
7172Database altered.
7374SQL> c/29/30
751* alter database add standby logfile thread 2 group 30 '/oradata/racdg/srl/srl29.log' size 520M
76SQL> c/l29/l30
771* alter database add standby logfile thread 2 group 30 '/oradata/racdg/srl/srl30.log' size 520M
78SQL> /
7980Database altered.
8182SQL> set linesize 150;
83set pagesize 50;
84column MB format a5;
85column STATUS format a12;
86column MEMBER format a50;
87select sl.GROUP#,sl.THREAD#,sl.BYTES/1024/1024||'MB' MB,sl.STATUS, lf.TYPE,lf.MEMBER from v$standby_log sl,v$logfile lf where sl.GROUP#=lf.GROUP#;
88SQL> SQL> SQL> SQL> SQL>
89GROUP# THREAD# MB STATUS TYPE MEMBER
90---------- ---------- ----- ------------ ------- --------------------------------------------------
9121 1 520MB UNASSIGNED STANDBY /oradata/racdg/srl/srl21.log
9222 1 520MB UNASSIGNED STANDBY /oradata/racdg/srl/srl22.log
9323 1 520MB UNASSIGNED STANDBY /oradata/racdg/srl/srl23.log
9424 1 520MB UNASSIGNED STANDBY /oradata/racdg/srl/srl24.log
9525 1 520MB UNASSIGNED STANDBY /oradata/racdg/srl/srl25.log
9626 2 520MB UNASSIGNED STANDBY /oradata/racdg/srl/srl26.log
9727 2 520MB UNASSIGNED STANDBY /oradata/racdg/srl/srl27.log
9828 2 520MB UNASSIGNED STANDBY /oradata/racdg/srl/srl28.log
9929 2 520MB UNASSIGNED STANDBY /oradata/racdg/srl/srl29.log
10030 2 520MB UNASSIGNED STANDBY /oradata/racdg/srl/srl30.log
10110210 rows selected.
在主库上添加standby redo1SQL> alter database add standby logfile thread 1 group 21 '+datadg' size 520m;
23Database altered.
45SQL> c/21/22
61* alter database add standby logfile thread 1 group 22 '+datadg' size 520m
7SQL> /
89Database altered.
1011SQL> c/22/23
121* alter database add standby logfile thread 1 group 23 '+datadg' size 520m
13SQL> /
1415Database altered.
1617SQL> c/23/24
181* alter database add standby logfile thread 1 group 24 '+datadg' size 520m
19SQL> /
2021Database altered.
2223SQL> c/24/25
241* alter database add standby logfile thread 1 group 25 '+datadg' size 520m
25SQL> /
2627Database altered.
查看一下:1SQL> set linesize 150;
2set pagesize 50;
3column MB format a5;
4column STATUS format a12;
5column MEMBER format a50;
6select sl.GROUP#,sl.THREAD#,sl.BYTES/1024/1024||'MB' MB,sl.STATUS, lf.TYPE,lf.MEMBER from v$standby_log sl,v$logfile lf where sl.GROUP#=lf.GROUP#;
7SQL> SQL> SQL> SQL> SQL>
8GROUP# THREAD# MB STATUS TYPE MEMBER
9---------- ---------- ----- ------------ ------- --------------------------------------------------
1021 1 520MB UNASSIGNED STANDBY +DATADG/rac11g/onlinelog/group_21.274.940794577
1122 1 520MB UNASSIGNED STANDBY +DATADG/rac11g/onlinelog/group_22.275.940794669
1223 1 520MB UNASSIGNED STANDBY +DATADG/rac11g/onlinelog/group_23.276.940794723
1324 1 520MB UNASSIGNED STANDBY +DATADG/rac11g/onlinelog/group_24.277.940794751
1425 1 520MB UNASSIGNED STANDBY +DATADG/rac11g/onlinelog/group_25.278.940794773
1516SQL>
4.8 现在备库就可以启动了
1如果启动的时候报错
2SQL> alter database open;
3alter database open
4*
5ERROR at line 1:
6ORA-10458: standby database requires recovery
7ORA-01152: file 1 was not restored from a sufficiently old backup
8ORA-01110: data file 1: '/oradata/rac11g/datafile/system.256.940258647'
9就将主库的归档日志重新备份scp到备库上,然后一定要catalog一下保证元数据一致,然后recover。在启动就ok
10当然具体情况具体分析
1112SQL> alter database open;
1314Database altered.
1516然后开启备库日志应用进程
17SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;
1819Database altered.
2021备库察看 RFS(Remote File Service) 接收日志情况和 MRP 应用日志同步主库况
22SQL> select process,client_process,sequence#,status from v$managed_standby;
2324PROCESS CLIENT_P SEQUENCE# STATUS
25--------- -------- ---------- ------------
26ARCH ARCH 0 CONNECTED
27ARCH ARCH 0 CONNECTED
28ARCH ARCH 0 CONNECTED
29ARCH ARCH 0 CONNECTED
30MRP0 N/A 44 WAIT_FOR_LOG
3132看一下alert 日:
33Additional information: 3
34Clearing online redo logfile 8 /oradata/racdg/onlinelog/group_8.271.940259169
35Clearing online log 8 of thread 2 sequence number 28
36Errors in file /u01/app/oracle/diag/rdbms/racdg/orcl/trace/orcl_mrp0_3672.trc:
37ORA-00313: open failed for members of log group 8 of thread 2
38ORA-00312: online log 8 thread 2: '/oradata/racdg/onlinelog/group_8.271.940259169'
39ORA-27037: unable to obtain file status
40Linux-x86_64 Error: 2: No such file or directory
41Additional information: 3
42Errors in file /u01/app/oracle/diag/rdbms/racdg/orcl/trace/orcl_mrp0_3672.trc:
43ORA-00313: open failed for members of log group 8 of thread 2
44ORA-00312: online log 8 thread 2: '/oradata/racdg/onlinelog/group_8.271.940259169'
45ORA-27037: unable to obtain file status
46Linux-x86_64 Error: 2: No such file or directory
47Additional information: 3
48Clearing online redo logfile 8 complete
49Sat Apr 08 20:29:34 2017
50Media Recovery Waiting for thread 1 sequence 44 //这里显示正在等待1节点的44 ,我们去节点1 查看一下归档日志的情况
节点1:1SQL> archive log list;
2Database log mode Archive Mode
3Automatic archival Enabled
4Archive destination +ARCHDG
5Oldest online log sequence 41
6Next log sequence to archive 44
7Current log sequence 44
8SQL> select group#,thread#,sequence#,status,bytes/1024/1024 from v$log;
910GROUP# THREAD# SEQUENCE# STATUS BYTES/1024/1024
11---------- ---------- ---------- ------------ ---------------
121 1 43 INACTIVE 512
132 1 44 CURRENT 512
143 1 41 INACTIVE 512
154 1 42 INACTIVE 512
165 2 33 INACTIVE 512
176 2 34 INACTIVE 512
187 2 35 CURRENT 512
198 2 32 INACTIVE 512
20218 rows selected.
22没问题,节点1 的44 是current,表示一切正常!!!!!!!
再看一下备库的redo:1SQL> set linesize 150;
2set pagesize 50;
3column MB format a5;
4column STATUS format a12;
5column MEMBER format a50;
6select sl.GROUP#,sl.THREAD#,sl.BYTES/1024/1024||'MB' MB,sl.STATUS, lf.TYPE,lf.MEMBER from v$standby_log sl,v$logfile lf where
7sl.GROUP#=lf.GROUP#;
89GROUP# THREAD# MB STATUS TYPE MEMBER
10---------- ---------- ----- ------------ ------- --------------------------------------------------
1121 1 520MB UNASSIGNED STANDBY /oradata/racdg/srl/srl21.log
1223 1 520MB UNASSIGNED STANDBY /oradata/racdg/srl/srl23.log
1324 1 520MB UNASSIGNED STANDBY /oradata/racdg/srl/srl24.log
1425 1 520MB UNASSIGNED STANDBY /oradata/racdg/srl/srl25.log
1522 1 520MB UNASSIGNED STANDBY /oradata/racdg/srl/srl22.log
1626 2 520MB UNASSIGNED STANDBY /oradata/racdg/srl/srl26.log
1727 2 520MB UNASSIGNED STANDBY /oradata/racdg/srl/srl27.log
1828 2 520MB UNASSIGNED STANDBY /oradata/racdg/srl/srl28.log
1929 2 520MB UNASSIGNED STANDBY /oradata/racdg/srl/srl29.log
2030 2 520MB UNASSIGNED STANDBY /oradata/racdg/srl/srl30.log
212210 rows selected.
2324发现都是UNASSIGNED ,因为主库没有进行日志切换,所以备库的日志没有激活。
25然后我们去节点1 切换一下日志:
26SQL> alter system archive log current;
2728System altered.
29(这里虽然只是在节点上切换了日志,但是节点1 和节点2 都会发生切换)
再看备库的redo 状态:1SQL> set linesize 150;
2set pagesize 50;
3column MB format a5;
4column STATUS format a12;
5column MEMBER format a50;
6select sl.GROUP#,sl.THREAD#,sl.BYTES/1024/1024||'MB' MB,sl.STATUS, lf.TYPE,lf.MEMBER from v$standby_log sl,v$logfile lf where
7sl.GROUP#=lf.GROUP#;SQL> SQL> SQL> SQL> SQL>
89GROUP# THREAD# MB STATUS TYPE MEMBER
10---------- ---------- ----- ------------ ------- --------------------------------------------------
1121 1 520MB ACTIVE STANDBY /oradata/racdg/srl/srl21.log
1223 1 520MB UNASSIGNED STANDBY /oradata/racdg/srl/srl23.log
1324 1 520MB UNASSIGNED STANDBY /oradata/racdg/srl/srl24.log
1425 1 520MB UNASSIGNED STANDBY /oradata/racdg/srl/srl25.log
1522 1 520MB UNASSIGNED STANDBY /oradata/racdg/srl/srl22.log
1626 2 520MB ACTIVE STANDBY /oradata/racdg/srl/srl26.log
1727 2 520MB UNASSIGNED STANDBY /oradata/racdg/srl/srl27.log
1828 2 520MB UNASSIGNED STANDBY /oradata/racdg/srl/srl28.log
1929 2 520MB UNASSIGNED STANDBY /oradata/racdg/srl/srl29.log
2030 2 520MB UNASSIGNED STANDBY /oradata/racdg/srl/srl30.log
212210 rows selected.
23现在就成了active
242526SQL> select process,client_process,sequence#,status from v$managed_standby;
2728PROCESS CLIENT_P SEQUENCE# STATUS
29--------- -------- ---------- ------------
30ARCH ARCH 63 CLOSING
31ARCH ARCH 0 CONNECTED
32ARCH ARCH 0 CONNECTED
33ARCH ARCH 75 CLOSING
34RFS LGWR 76 IDLE
35RFS UNKNOWN 0 IDLE
36RFS UNKNOWN 0 IDLE
37RFS LGWR 64 IDLE
38RFS UNKNOWN 0 IDLE
39RFS UNKNOWN 0 IDLE
40RFS UNKNOWN 0 IDLE
4142PROCESS CLIENT_P SEQUENCE# STATUS
43--------- -------- ---------- ------------
44RFS UNKNOWN 0 IDLE
45MRP0 N/A57 APPLYING_LOG
查看一下备库日志应用延迟:1SQL> set linesize 150;
2set pagesize 20;
3column name format a13;
4column value format a20;
5column unit format a30;
6column TIME_COMPUTED format a30;
7select name,value,unit,time_computed from v$dataguard_stats where name in ('transport lag','apply lag');
89SQL> SQL> SQL> SQL> SQL> SQL>
10NAME VALUE UNIT TIME_COMPUTED
11------------- -------------------- ------------------------------ ------------------------------
12transport lag +00 00:00:00 day(2) to second(0) interval 04/09/2017 10:14:14
13apply lag +00 00:00:00 day(2) to second(0) interval 04/09/2017 10:14:14
心跳出现问题:查看alert日志的时候,出现tns的报错首先查看/etc/host文件,ping一下备库的解析名称,如果可以ping通说明物理通信之间没有问题然后再看一下备库的监听状态,如果不是解析的问题,基本就是监听的问题。可能监听未启动。配置问题heartspeed 心跳问题有可能是口令文件有问题,可以将两个节点 的口令文件cp过去也有可能是tnsname.ora 解析的配置问题3、备库应用延迟values为空,查看一下备库监听是否启动,监看备库日志应用是否开启。
SQL> select process,client_process,sequence#,status from v$managed_standby;
PROCESS CLIENT_P SEQUENCE# STATUS
---------------------------------------
ARCH ARCH 0 CONNECTED
ARCH ARCH 0 CONNECTED
ARCH ARCH 0 CONNECTED
ARCH ARCH 0 CONNECTED
ARCH ARCH 0 CONNECTED
MRP0 N/A 42 APPLYING_LOG
6 rows selected.
SQL>set linesize 150;
set pagesize 20;
column name format a13;
column value format a20;
column unit format a30;
column TIME_COMPUTED format a30;
select name,value,unit,time_computed from v$dataguard_stats where name in ('transport lag','apply lag');
NAME VALUE UNIT TIME_COMPUTED
---------------------------------------------------------------------------------------------
transport lag day(2) to second(0) interval 10/10/201711:48:06
apply lag day(2) to second(0) interval 10/10/201711:48:06
[oracle@rac11gdg ~]$ lsnrctl status
LSNRCTL forLinux:Version11.2.0.4.0-Production on 10-OCT-201711:49:25
Copyright(c)1991,2013,Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.56.70)(PORT=1521)))
TNS-12541: TNS:no listener
TNS-12560: TNS:protocol adapter error
TNS-00511:No listener
LinuxError:111:Connection refused
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
TNS-12541: TNS:no listener
TNS-12560: TNS:protocol adapter error
TNS-00511:No listener
LinuxError:111:Connection refused
[oracle@rac11gdg ~]$ lsnrctl start
LSNRCTL forLinux:Version11.2.0.4.0-Production on 10-OCT-201711:49:32
Copyright(c)1991,2013,Oracle. All rights reserved.
Starting/u01/app/oracle/product/11.2.0/db_1/bin/tnslsnr: please wait...
TNSLSNR forLinux:Version11.2.0.4.0-Production
System parameter file is /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/rac11gdg/listener/alert/log.xml
Listening on:(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.56.70)(PORT=1521)))
Listening on:(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.56.70)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR forLinux:Version11.2.0.4.0-Production
StartDate 10-OCT-201711:49:34
Uptime 0 days 0 hr.0 min.0 sec
TraceLevel off
Security ON:Local OS Authentication
SNMP OFF
ListenerParameterFile /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
ListenerLogFile /u01/app/oracle/diag/tnslsnr/rac11gdg/listener/alert/log.xml
ListeningEndpointsSummary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.56.70)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
ServicesSummary...
Service"rac11g" has 1 instance(s).
Instance"rac11gdg", status UNKNOWN, has 1 handler(s)forthis service...
The command completed successfully
[oracle@rac11gdg ~]$ lsnrctl status
LSNRCTL forLinux:Version11.2.0.4.0-Production on 10-OCT-201711:49:37
Copyright(c)1991,2013,Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.56.70)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR forLinux:Version11.2.0.4.0-Production
StartDate 10-OCT-201711:49:34
Uptime 0 days 0 hr.0 min.3 sec
TraceLevel off
Security ON:Local OS Authentication
SNMP OFF
ListenerParameterFile /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
ListenerLogFile /u01/app/oracle/diag/tnslsnr/rac11gdg/listener/alert/log.xml
ListeningEndpointsSummary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.56.70)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
ServicesSummary...
Service"rac11g" has 1 instance(s).
Instance"rac11gdg", status UNKNOWN, has 1 handler(s)forthis service...
The command completed successfully
SQL>set linesize 150;
SQL>set pagesize 20;
SQL> column name format a13;
SQL> column value format a20;
SQL> column unit format a30;
SQL> column TIME_COMPUTED format a30;
SQL> select name,value,unit,time_computed from v$dataguard_stats where name in ('transport lag','apply lag');
NAME VALUE UNIT TIME_COMPUTED
---------------------------------------------------------------------------------------------
transport lag +0000:00:00 day(2) to second(0) interval 10/10/201711:54:46
apply lag +0000:00:00 day(2) to second(0) interval 10/10/201711:54:46
阅读全文0 0
- 1.oracle RAC11G 对单机ADG搭建详细文档
- oracle adg搭建
- ORACLE之搭建DG(ADG方式)
- ORACLE之搭建DG(ADG方式)
- redis单机搭建--详细
- Oracle 11G Active DataGuard(ADG)搭建配置过程
- Oracle 11g搭建DG(ADG方式)
- linux下搭建rac11g详解
- Oracle Dataguard搭建-单机到单机
- 搭建ADG遭遇各种问题
- 超详细单机版搭建hadoop环境
- win7 linux 搭建redis单机环境 详细
- 使用duplicate搭建oracle 11.2.0.4.0数据库active dataguard (ADG)
- 配置ORACLE 11G ADG
- adg
- oracle utl_file详细文档
- 详细Oracle 安装文档
- 单机搭建oracle dg 实验与总结
- Ubuntu16.04LTS 安装搜狗输入法
- clang 在Ubuntu上的配置
- Android onClick 按钮单击事件 四种常用写法
- hdu 1847 Good Luck in CET-4 Everybody!(基础博弈)
- poj3565-Ants
- 1.oracle RAC11G 对单机ADG搭建详细文档
- hihocoder week171分析---并查集
- Redis安装和自启动配置
- BZOJ 2201 期望DP 解题报告
- 浅谈Java中Collection、Iterable、List、ArrayList间的关系
- JSP之3大指令、9大内置对象、4大域对象、动作标签
- 定义常量类
- HDU3265(线段树+扫描线+挖空矩形面积并)
- java文件传输基础:字节字符流的转换OutputStreamWriter/InputStreamReader
原创粉丝点击热门IT博客热门问题 老师的惩罚 人脸识别 我在镇武司摸鱼那些年 重生之率土为王 我在大康的咸鱼生活 盘龙之生命进化 天生仙种 凡人之先天五行 春回大明朝 姑娘不必设防,我是瞎子 电视墙装饰 卧室挂衣架 铁花架 花架 铁艺 多层 吊兰花架 古董架 一字板 多宝格 墙上 支架 客厅花架 阳台装饰 木板 花盆架 洗衣机托架 落地衣架挂衣架 绿萝花架 柱斑分身内壁惩罚文 壁橱图片 壁橱橱柜 掏壁橱 壁橱隐形门 电视壁橱 壁橱衣柜 卧室壁橱 儿童房壁橱 家庭壁橱 卧室壁橱门 壁橱装修效果图 壁橱内部设计图片 壁橱装修效果图 卧室 卧室壁橱设计 卧室壁橱装修效果图 卧室壁橱效果图 家庭卧室壁橱样式图片 壁橱装修效果图大全 卧室壁橱推拉门 卧室壁橱内部设计图片 壁橱衣柜效果图 客厅壁橱装修效果图 装修壁橱效果图