【Oracle RAC+DG实验】Oracle RAC+ASM+DataGuard配置实验记录+常见问题
1、环境规划：
---RAC环境介绍(primary database)
rac1 rac2
______________________________________________________
public ip 192.168.110.11 192.168.110.12
______________________________________________________
virtual ip 192.168.110.21 192.168.110.22
_____________________________________________________
instance racdb1 racdb2
______________________________________________________
db_name racdb
_______________________________________________________
storage mode ASM
__________________________________________________
---单机环境介绍（standby database）
数据文件可放至本地， 也可以放至ASM上，本实验中先放至本地实验
__________________________________________________________________________
ip 192.168.110.11 192.168.110.12
___________________________________________________________________________
instance 192.168.110.13(rac3)
___________________________________________________________________________
storage mode /oradata/racdb
___________________________________________________________________________
----hosts文件
#Public Network - (eth0)
192.168.110.11 rac1
192.168.110.12 rac2
192.168.110.13 rac3
#Private Interconnect - (eth1)
10.10.10.11 rac1priv
10.10.10.12 rac2priv
#Public Virtual IP (VIP) addresses - (eth0)
192.168.110.21 rac1vip
192.168.110.22 rac2vip
--检查环境
１）、启动archivelog归档模式
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 54
Next log sequence to archive 56
Current log sequence 56
SQL> show parameter RECOVERY
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string +DG_RECOVERY
db_recovery_file_dest_size big integer 2G
recovery_parallelism integer 0
２）、启动FORCE_LOGGING模式
SQL> alter database FORCE LOGGING;
Database altered.
SQL> select FORCE_LOGGING from v$database;
FOR
---
YES
2、首先配置两个数据库的tnsnames.ora和listener.ora
tnsnames.ora(两台主机相同)
racdb_rac1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.110.21)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = racdb_s)
(SERVICE_NAME = racdb1)
)
)
racdb_rac2 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.110.22)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = racdb_s)
(SERVICE_NAME = racdb2)
)
)
racdb_standby =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.110.13)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = racdb)
)
)
standby主机上的listener.ora
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = racdb)
(ORACLE_HOME = /oracle/app/product/10.2.0/db_1)
(SID_NAME = racdb)
)
(SID_DESC =
(GLOBAL_DBNAME = PLSExtProc)
(ORACLE_HOME = /oracle/app/product/10.2.0/db_1)
(SID_NAME = PLSExtProc)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.110.13)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
)
3、准备参数文件
RAC环境下的参数变化增加如下：
RAC主库:（注意使用ASM的时候，不要改变db_unique_name参数，否则之后创建的asm文件就会放入至新的db_unique_name目录下面，导致DB_FILE_NAME_CONVERT失效。）
#add below parameter for standy database
*.service_names=racdb_s
*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(racdb,racdb_standby)'
*.LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=racdb'
*.LOG_ARCHIVE_DEST_2='SERVICE=racdb_standby LGWR VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=racdb_standby'
*.FAL_SERVER='racdb_standby'
*.STANDBY_ARCHIVE_DEST='/oradata/arch'
*.racdb1.fal_client=racdb1
*.racdb2.fal_client=racdb2
*.STANDBY_FILE_MANAGEMENT=AUTO
*.DB_FILE_NAME_CONVERT='/oradata/racdb/datafile','+DG_DATA/racdb/datafile','/oradata/racdb/tempfile','+DG_DATA/racdb/tempfile'
*.LOG_FILE_NAME_CONVERT='/oradata/racdb/onlinelog','+DG_DATA/racdb/onlinelog'
单机备库增加以下：
*.db_name='racdb'
*.db_unique_name='racdb_standby'
*.service_names='racdb_standby'
*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(racdb_standby,racdb)'
*.LOG_ARCHIVE_DEST_1='LOCATION=/oradata/arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=racdb_standby'
*.LOG_ARCHIVE_DEST_2='SERVICE=racdb1 LGWR VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=racdb'
*.STANDBY_FILE_MANAGEMENT='AUTO'
STANDBY_ARCHIVE_DEST='/oradata/arch'
*.FAL_SERVER='racdb1','racdb2'
fal_client='racdb_standby'
*.DB_FILE_NAME_CONVERT='+DG_DATA/racdb/datafile','/oradata/racdb/datafile','+DG_DATA/racdb/tempfile','/oradata/racdb/tempfile'
*.LOG_FILE_NAME_CONVERT='+DG_DATA/racdb/onlinelog','/oradata/racdb/onlinelog'*
racdb1.thread=1
*.undo_management='AUTO'
单机备库参数如下：
#add below parameter for standy database
*.audit_file_dest='/oracle/app/admin/racdb/adump'
*.background_dump_dest='/oracle/app/admin/racdb/bdump'
*.compatible='10.2.0.4'
*.control_files='/oradata/racdb/datafile/racdb.ctl'
*.core_dump_dest='/oracle/app/admin/racdb/cdump'
*.db_block_size=16384
*.db_domain=''
*.db_name='racdb'
*.db_file_multiblock_read_count=16
*.DB_FILE_NAME_CONVERT='+DG_DATA/racdb/datafile','/oradata/racdb/datafile','+DG_DATA/racdb/tempfile','/oradata/racdb/tempfile'
*.db_unique_name='racdb_standby'
fal_client='racdb_standby'
*.FAL_SERVER='racdb1','racdb2'
*.job_queue_processes=10
*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(racdb_standby,racdb_s)'
*.LOG_ARCHIVE_DEST_1='LOCATION=/oradata/arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=racdb_standby'
*.LOG_ARCHIVE_DEST_2='SERVICE=racdb1 LGWR VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=racdb'
*.LOG_FILE_NAME_CONVERT='+DG_DATA/racdb/onlinelog','/oradata/racdb/onlinelog'
*.open_cursors=300
*.pga_aggregate_target=89128960
*.processes=150
*.remote_login_passwordfile='exclusive'
*.service_names='racdb_standby'
*.sga_target=268435456
*.STANDBY_FILE_MANAGEMENT='AUTO'
racdb1.thread=1
*.undo_management='AUTO'
racdb1.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/oracle/app/admin/racdb/udump'
STANDBY_ARCHIVE_DEST='/oradata/arch'
5、在rac上进行备份
rman target /
backup database format '/soft/racdb/racdbfull%u_%s_%p';
RMAN> backup database format '/soft/racdb/racdbfull%u_%s_%p';
Starting backup at 11-JUN-10
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=127 instance=racdb1 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including current SPFILE in backupset
including current control file in backupset
input datafile fno=00001 name=+DG_DATA/racdb/datafile/system.268.719166757
input datafile fno=00002 name=+DG_DATA/racdb/datafile/undotbs1.269.719166777
input datafile fno=00003 name=+DG_DATA/racdb/datafile/sysaux.270.719166783
input datafile fno=00004 name=+DG_DATA/racdb/datafile/undotbs2.272.719166797
input datafile fno=00006 name=+DG_DATA/racdb/datafile/rman_tbs.dbf
input datafile fno=00005 name=+DG_DATA/racdb/datafile/users.273.719166803
channel ORA_DISK_1: starting piece 1 at 11-JUN-10
channel ORA_DISK_1: finished piece 1 at 11-JUN-10
piece handle=/soft/racdb/racdbfull0elfvhv4_14_1 tag=TAG20100611T143204 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:38
Finished backup at 11-JUN-10
将备份的文件拷到另一台备机相同目录下。
[oracle@rac1 racdb]$ ls -ls
total 498268
498268 -rw-r----- 1 oracle oinstall 509722624 Jun 11 12:10 racdbfull_0clfv9jn_12_1
[oracle@rac1 racdb]$ pwd
/soft/racdb
[oracle@rac1 racdb]$ scp racdbfull_0clfv9jn_12_1 rac3:/soft/racdb/
6、创建standby控制文件
在rac两个实例上进行几次归档。
alter system archive log current;
创建standby控制文件
alter database create standby controlfile as '/oracle/standby.ctl';
创建spfile并启动standby至nomount状态。
startup nomount;
7、利用rman创建standby数据库
rac1:
rman target / auxiliarysys/sys@racdb_standby
allocate channel c1 device type disk format '/soft/racdb/%U' connectsys/6212327@rac1;
allocate channel c2 device type disk format '/soft/racdb/%U' connectsys/6212327@rac2;
allocate auxiliary channel ac1 device type disk format '/soft/racdb/%U';
allocate auxiliary channel ac2 device type disk format '/soft/racdb/%U';
duplicate target database for standby;
过程如下：
[oracle@rac1 racdb]$ rman target / auxiliarysys/sys@racdb_standby
Recovery Manager: Release 10.2.0.4.0 - Production on Fri Jun 11 13:41:48 2010
Copyright (c) 1982, 2007, Oracle. All rights reserved.
connected to target database: RACDB (DBID=716783510)
connected to auxiliary database: RACDB (not mounted)
RMAN> duplicate target database for standby;
Starting Duplicate Db at 11-JUN-10
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: sid=155 devtype=DISK
contents of Memory Script.:
{
restore clone standby controlfile;
sql clone 'alter database mount standby database';
}
executing Memory Script
Starting restore at 11-JUN-10
using channel ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: copied control file copy
input filename=/oracle/standby.ctl
output filename=/oradata/racdb/datafile/racdb.ctl
Finished restore at 11-JUN-10
sql statement: alter database mount standby database
released channel: ORA_AUX_DISK_1
contents of Memory Script.:
{
set newname for tempfile 1 to
"/oradata/racdb/tempfile/temp.271.719166789";
switch clone tempfile all;
set newname for datafile 1 to
"/oradata/racdb/datafile/system.268.719166757";
set newname for datafile 2 to
"/oradata/racdb/datafile/undotbs1.269.719166777";
set newname for datafile 3 to
"/oradata/racdb/datafile/sysaux.270.719166783";
set newname for datafile 4 to
"/oradata/racdb/datafile/undotbs2.272.719166797";
set newname for datafile 5 to
"/oradata/racdb/datafile/users.273.719166803";
set newname for datafile 6 to
"/oradata/racdb/datafile/rman_tbs.dbf";
restore
check readonly
clone database
;
}
executing Memory Script
executing command: SET NEWNAME
renamed temporary file 1 to /oradata/racdb/tempfile/temp.271.719166789 in control file
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting restore at 11-JUN-10
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: sid=155 devtype=DISK
channel ORA_AUX_DISK_1: starting datafile backupset restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /oradata/racdb/datafile/system.268.719166757
restoring datafile 00002 to /oradata/racdb/datafile/undotbs1.269.719166777
restoring datafile 00003 to /oradata/racdb/datafile/sysaux.270.719166783
restoring datafile 00004 to /oradata/racdb/datafile/undotbs2.272.719166797
restoring datafile 00005 to /oradata/racdb/datafile/users.273.719166803
restoring datafile 00006 to /oradata/racdb/datafile/rman_tbs.dbf
channel ORA_AUX_DISK_1: reading from backup piece /soft/racdb/racdbfull_0clfv9jn_12_1
channel ORA_AUX_DISK_1: restored backup piece 1
piece handle=/soft/racdb/racdbfull_0clfv9jn_12_1 tag=TAG20100611T120926
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:01:39
Finished restore at 11-JUN-10
contents of Memory Script.:
{
switch clone datafile all;
}
executing Memory Script
datafile 1 switched to datafile copy
input datafile copy recid=10 stamp=721116136 filename=/oradata/racdb/datafile/system.268.719166757
datafile 2 switched to datafile copy
input datafile copy recid=11 stamp=721116137 filename=/oradata/racdb/datafile/undotbs1.269.719166777
datafile 3 switched to datafile copy
input datafile copy recid=12 stamp=721116137 filename=/oradata/racdb/datafile/sysaux.270.719166783
datafile 4 switched to datafile copy
input datafile copy recid=13 stamp=721116137 filename=/oradata/racdb/datafile/undotbs2.272.719166797
datafile 5 switched to datafile copy
input datafile copy recid=14 stamp=721116137 filename=/oradata/racdb/datafile/users.273.719166803
datafile 6 switched to datafile copy
input datafile copy recid=15 stamp=721116137 filename=/oradata/racdb/datafile/rman_tbs.dbf
Finished Duplicate Db at 11-JUN-10
RMAN>
8、检查standby数据库
SQL> select status from v$instance;
STATUS
------------
MOUNTED
SQL> select open_mode from v$database;
OPEN_MODE
----------
MOUNTED
SQL> select member from v$logfile;
MEMBER
--------------------------------------------------------------------------------
/oradata/racdb/onlinelog/group_1.266.719166749
/oradata/racdb/onlinelog/group_2.267.719166751
/oradata/racdb/onlinelog/group_3.274.719167937
/oradata/racdb/onlinelog/group_4.275.719167939
/oradata/racdb/onlinelog/group5
/oradata/racdb/onlinelog/group6
6 rows selected.
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/oradata/racdb/datafile/system.268.719166757
/oradata/racdb/datafile/undotbs1.269.719166777
/oradata/racdb/datafile/sysaux.270.719166783
/oradata/racdb/datafile/undotbs2.272.719166797
/oradata/racdb/datafile/users.273.719166803
/oradata/racdb/datafile/rman_tbs.dbf
6 rows selected.
SQL> select name from v$tempfile;
NAME
--------------------------------------------------------------------------------
/oradata/racdb/tempfile/temp.271.719166789
SQL> show parameter control
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time integer 7
control_files string /oradata/racdb/datafile/racdb.
ctl
9、创建standby redo log日志。
创建原则和单实例一样，大小相等，但日志组数量要比primary数据库多一组。如之前为6组12个，则现在要创建7组14个。
alter database add standby logfile thread 1 group 7 '/oradata/racdb/onlinelog/group_7.log' size 50M;
alter database add standby logfile thread 1 group 8 '/oradata/racdb/onlinelog/group_8.log' size 50M;
alter database add standby logfile thread 1 group 9 '/oradata/racdb/onlinelog/group_9.log' size 50M;
alter database add standby logfile thread 2 group 10 '/oradata/racdb/onlinelog/group_10.log' size 50M;
alter database add standby logfile thread 2 group 11 '/oradata/racdb/onlinelog/group_11.log' size 50M;
alter database add standby logfile thread 2 group 12 '/oradata/racdb/onlinelog/group_12.log' size 50M;
alter database add standby logfile thread 1 group 13 '/oradata/racdb/onlinelog/group_13.log' size 50M;
alter database add standby logfile thread 2 group 14 '/oradata/racdb/onlinelog/group_14.log' size 50M;
10、开始同步
启动MRP:
SQL> alter database recover managed standby database disconnect from session;
Media recovery complete.
停止MRP:
alter database recover managed standby database cancel;
11、在rac各个实例上查看日志传送情况：
col DEST_NAME format a20
select dest_name,status,error from v$archive_dest;
DEST_NAME STATUS ERROR
-------------------- --------- -----------------------------------------------------------------
LOG_ARCHIVE_DEST_1 INACTIVE
LOG_ARCHIVE_DEST_2 ERROR ORA-16057: DGID from server not in Data Guard configuration
LOG_ARCHIVE_DEST_3 INACTIVE
LOG_ARCHIVE_DEST_4 INACTIVE
LOG_ARCHIVE_DEST_5 INACTIVE
LOG_ARCHIVE_DEST_6 INACTIVE
LOG_ARCHIVE_DEST_7 INACTIVE
LOG_ARCHIVE_DEST_8 INACTIVE
LOG_ARCHIVE_DEST_9 INACTIVE
LOG_ARCHIVE_DEST_10 VALID
错误1:
ORA-16057: DGID from server not in Data Guard configuration
原因:主库没有设置参数log_archive_config
解决方法*.log_archive_config='dg_config=(orcl,auxdb)'
alter system set log_archive_config='dg_config=(racdb,racdb_standby)' scope=both;
SQL> select dest_name,status,error from v$archive_dest;
DEST_NAME STATUS ERROR
-------------------- --------- -----------------------------------------------------------------
LOG_ARCHIVE_DEST_1 VALID
LOG_ARCHIVE_DEST_2 VALID
LOG_ARCHIVE_DEST_3 INACTIVE
LOG_ARCHIVE_DEST_4 INACTIVE
LOG_ARCHIVE_DEST_5 INACTIVE
LOG_ARCHIVE_DEST_6 INACTIVE
LOG_ARCHIVE_DEST_7 INACTIVE
LOG_ARCHIVE_DEST_8 INACTIVE
LOG_ARCHIVE_DEST_9 INACTIVE
LOG_ARCHIVE_DEST_10 INACTIVE
10 rows selected
---测试看日志是否传送成功。
主库：
Sql>alter system switch logfile;
Sql> select max(SEQUENCE#) from v$archived_log;
备库：
Sql> select max(SEQUENCE#) from v$archived_log;
#或者更详细的：select FIRST_TIME,NEXT_TIME, APPLIED,SEQUENCE# from v$archived_log order by SEQUENCE#;
SELECT MAX(R.SEQUENCE#) LAST_SEQ_RECD, MAX(L.SEQUENCE#) LAST_SEQ_SENT FROM V$ARCHIVED_LOG R, V$LOG L WHERE R.DEST_ID=2 AND L.ARCHIVED='YES'
SELECT NAME FROM V$ARCHIVED_LOG WHERE THREAD#=1 AND DEST_ID=1 AND SEQUENCE# BETWEEN 85 AND 86;
测试在RAC主库上创建一个表空间：
CREATE TABLESPACE FMISMAIN
LOGGING
DATAFILE '+dg_data' SIZE 20M AUTOEXTEND
ON NEXT 10M EXTENT MANAGEMENT LOCAL SEGMENT SPACE
MANAGEMENT AUTO;
创建用户：
CREATE USER "FMISMAIN" PROFILE "DEFAULT"
IDENTIFIED BY "FMISMAIN" DEFAULT TABLESPACE "FMISMAIN"
TEMPORARY TABLESPACE "TEMP"
ACCOUNT UNLOCK;
GRANT DBA TO "FMISMAIN";
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
+DG_DATA/racdb/datafile/system.268.719166757
+DG_DATA/racdb/datafile/undotbs1.269.719166777
+DG_DATA/racdb/datafile/sysaux.270.719166783
+DG_DATA/racdb/datafile/undotbs2.272.719166797
+DG_DATA/racdb/datafile/users.273.719166803
+DG_DATA/racdb/datafile/rman_tbs.dbf
+DG_DATA/racdb/datafile/fmismain.287.721410885
SQL> conn fmismain/fmismain
Connected.
SQL> select count(*) from xtdw3;
COUNT(*)
----------
30
SQL> select dh from xtdw3;
DH
--------
MAIN
0600
0601
1200
1201
0602
0100
0101
0400
0401
0500
DH
--------
0501
0700
0701
1300
1301
1800
1801
1900
1901
5100
5101
DH
--------
5300
5301
0301
0300
1500
1501
1600
1601
30 rows selected.
在两个RAC实例上分别手工执行查看结果：
SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
SQL> /
System altered.
检查rac3备库的日志传送：
[oracle@rac3 oradata]$ ls -lsR
total 24
4 drwxr-xr-x 2 oracle oinstall 4096 Jun 11 16:15 arch
16 drwxrwxrwx 2 oracle dba 16384 Jun 8 03:21 lost+found
4 drwxr-xr-x 5 oracle oinstall 4096 Jun 8 03:25 racdb
./arch:
total 4888
36 -rw-r----- 1 oracle oinstall 36352 Jun 8 07:26 1_76_719166742.dbf
76 -rw-r----- 1 oracle oinstall 73728 Jun 8 07:26 1_77_719166742.dbf
152 -rw-r----- 1 oracle oinstall 151040 Jun 8 07:26 1_78_719166742.dbf
168 -rw-r----- 1 oracle oinstall 165888 Jun 8 07:26 1_79_719166742.dbf
168 -rw-r----- 1 oracle oinstall 166400 Jun 8 07:51 1_80_719166742.dbf
148 -rw-r----- 1 oracle oinstall 144896 Jun 8 07:51 1_81_719166742.dbf
4 -rw-r----- 1 oracle oinstall 1024 Jun 8 07:51 1_82_719166742.dbf
144 -rw-r----- 1 oracle oinstall 140288 Jun 8 07:51 1_83_719166742.dbf
4 -rw-r----- 1 oracle oinstall 1024 Jun 8 07:42 1_84_719166742.dbf
72 -rw-r----- 1 oracle oinstall 66048 Jun 8 07:47 1_85_719166742.dbf
1464 -rw-r----- 1 oracle oinstall 1492992 Jun 8 08:25 1_86_719166742.dbf
16 -rw-r----- 1 oracle oinstall 13824 Jun 11 16:15 1_87_719166742.dbf
4 -rw-r----- 1 oracle oinstall 2560 Jun 11 16:15 1_88_719166742.dbf
24 -rw-r----- 1 oracle oinstall 23040 Jun 8 07:27 2_36_719166742.dbf
176 -rw-r----- 1 oracle oinstall 175104 Jun 8 07:27 2_37_719166742.dbf
148 -rw-r----- 1 oracle oinstall 143872 Jun 8 07:27 2_38_719166742.dbf
768 -rw-r----- 1 oracle oinstall 778752 Jun 8 07:51 2_39_719166742.dbf
152 -rw-r----- 1 oracle oinstall 149504 Jun 8 07:51 2_40_719166742.dbf
4 -rw-r----- 1 oracle oinstall 1024 Jun 8 07:51 2_41_719166742.dbf
140 -rw-r----- 1 oracle oinstall 139264 Jun 8 07:43 2_42_719166742.dbf
4 -rw-r----- 1 oracle oinstall 1024 Jun 8 07:42 2_43_719166742.dbf
60 -rw-r----- 1 oracle oinstall 54272 Jun 8 07:51 2_44_719166742.dbf
948 -rw-r----- 1 oracle oinstall 963584 Jun 11 16:14 2_45_719166742.dbf
4 -rw-r----- 1 oracle oinstall 2048 Jun 11 16:15 2_46_719166742.dbf
4 -rw-r----- 1 oracle oinstall 1536 Jun 11 16:15 2_47_719166742.dbf
./lost+found:
total 0
./racdb:
total 12
4 drwxr-xr-x 2 oracle oinstall 4096 Jun 11 16:14 datafile
4 drwxr-xr-x 2 oracle oinstall 4096 Jun 8 06:55 onlinelog
4 drwxr-xr-x 2 oracle oinstall 4096 Jun 8 08:05 tempfile
./racdb/datafile:
total 1245228
20520 -rw-r----- 1 oracle oinstall 20987904 Jun 11 16:22 fmismain.287.721410885
15076 -rw-r----- 1 oracle oinstall 15417344 Jun 11 16:29 racdb.ctl
51272 -rw-r----- 1 oracle oinstall 52445184 Jun 11 16:22 rman_tbs.dbf
215272 -rw-r----- 1 oracle oinstall 220217344 Jun 11 16:22 sysaux.270.719166783
440772 -rw-r----- 1 oracle oinstall 450904064 Jun 11 16:22 system.268.719166757
292148 -rw-r----- 1 oracle oinstall 298860544 Jun 11 16:22 undotbs1.269.719166777
205020 -rw-r----- 1 oracle oinstall 209731584 Jun 11 16:22 undotbs2.272.719166797
5148 -rw-r----- 1 oracle oinstall 5259264 Jun 11 16:22 users.273.719166803
./racdb/onlinelog:
total 717640
51260 -rw-r----- 1 oracle oinstall 52429312 Jun 11 16:29 group_10.log
51260 -rw-r----- 1 oracle oinstall 52429312 Jun 8 07:26 group_11.log
51260 -rw-r----- 1 oracle oinstall 52429312 Jun 8 06:55 group_1.266.719166749
51260 -rw-r----- 1 oracle oinstall 52429312 Jun 8 07:47 group_12.log
51260 -rw-r----- 1 oracle oinstall 52429312 Jun 8 07:37 group_13.log
51260 -rw-r----- 1 oracle oinstall 52429312 Jun 8 07:37 group_14.log
51260 -rw-r----- 1 oracle oinstall 52429312 Jun 8 06:55 group_2.267.719166751
51260 -rw-r----- 1 oracle oinstall 52429312 Jun 8 06:55 group_3.274.719167937
51260 -rw-r----- 1 oracle oinstall 52429312 Jun 8 06:55 group_4.275.719167939
51260 -rw-r----- 1 oracle oinstall 52429312 Jun 8 06:55 group5
51260 -rw-r----- 1 oracle oinstall 52429312 Jun 8 06:55 group6
51260 -rw-r----- 1 oracle oinstall 52429312 Jun 11 16:29 group_7.log
51260 -rw-r----- 1 oracle oinstall 52429312 Jun 8 07:25 group_8.log
51260 -rw-r----- 1 oracle oinstall 52429312 Jun 8 07:39 group_9.log
./racdb/tempfile:
total 84
84 -rw-r----- 1 oracle oinstall 20987904 Jun 8 08:05 temp.271.719166789
在备库中查看刚才创建的表空间与用户是否生效。
首先将数据库启动到read only模式下：
SQL> alter database recover managed standby database cancel;
Database altered.
SQL> alter database open read only;
Database altered.
SQL> !hostname
rac3
SQL>
SQL> conn fmismain/fmismain
Connected.
SQL> show parameter db_unique
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_unique_name string racdb_standby
SQL>
SQL> select count(*) from xtdw3;
COUNT(*)
----------
30
SQL> select dh from xtdw3;
DH
--------
MAIN
0600
0601
1200
1201
0602
0100
0101
0400
0401
0500
DH
--------
0501
0700
0701
1300
1301
1800
1801
1900
1901
5100
5101
DH
--------
5300
5301
0301
0300
1500
1501
1600
1601
30 rows selected.
12、增加temp文件
---在管理恢复模式下到只读模式
SQL>alter database recover managed standby database cancel;
SQL>alter database open read only;
这个时候，可以给数据库增加临时数据文件（这个在热备份的时候是没有备份过来的）
alter tablespace temp add tempfile '/oradata/racdb/tempfile/temp.271.719166789' reset 100M;
--从只读方式到管理恢复方式
SQL>recover managed standby database disconnect from session;
13、少日志的时候，维护故障解决
故障1
由于网络等原因导致归档日志没有全部传输到从库中，这些需要我们手动干预。
常见因素：从库关闭、网络故障、从库空间不足等。
维护的通常步骤；关闭：先关主库后关从库，启动：先启动从库然后启动主库。
关于日志传输的控制可以通过MANDATORY、REOPEN、MAX_FAILURE来控制
MANDATORY REOPEN=5 MAX_FAILURE=3 每5秒重试一次，最大允许错误次数为3次，如果重试3次仍然不能成功，那么主库的日志传输服务就会停止。
*.log_archive_dest_2='service=AUX VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) MANDATORY REOPEN=5 MAX_FAILURE=3 DB_UNIQUE_NAME=auxdb'
1)、查找不在standby的日志。
SQL>
SELECT MAX(R.SEQUENCE#) LAST_SEQ_RECD, MAX(L.SEQUENCE#) LAST_SEQ_SENT FROM V$ARCHIVED_LOG R, V$LOG L WHERE R.DEST_ID=2 AND L.ARCHIVED='YES';
LAST_SEQ_RECD LAST_SEQ_SENT
------------- -------------
7 10
2)、查找primary的所在路径
SQL> SELECT NAME FROM V$ARCHIVED_LOG WHERE THREAD#=1 AND DEST_ID=1 AND SEQUENCE# BETWEEN 5 AND 10;
NAME
--------------------------------------------------------------------------------
/primary/thread1_dest/arcr_1_7.arc
/primary/thread1_dest/arcr_1_8.arc
/primary/thread1_dest/arcr_1_9.arc
3)、将日志copy到standby的STANDBY_ARCHIVE_DEST下，将STANDBY_ARCHIVE_DEST的日志copy到 LOG_ARCHIVE_DEST下
4)、
SQL> STARTUP MOUNT
SQL> ALTER DATABASE RECOVER AUTOMATIC STANDBY DATABASE;
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
至此恢复成功。
故障2：
归档日志之间经常产生gap
1)、确认归档日志之间有无遗漏
SQL> SELECT THREAD#, LOW_SEQUENCE#, HIGH_SEQUENCE# FROM V$ARCHIVE_GAP;
THREAD# LOW_SEQUENCE# HIGH_SEQUENCE#
---------- ------------- --------------
1 90 92
2)、将遗漏的归档日志copy到备库的standby_archive_dest下
然后对其分别注册
ALTER DATABASE REGISTER PHYSICAL LOGFILE 'filespec1';
3)、恢复归档日志
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH FORCE;
4)、然后就可以按切换步骤进行切换了。
14、 参数说明
COMPATIBLE='10.2.0.1.0':数据库版本号，主库与从库要统一，否则有可能redo的数据不能从主库传送到从库。
DB_FILE_NAME_CONVERT=主库数据文件地址，从库数据文件地址:用于主从库在同一台机器上或主从库数据文件的路径不一致的情况下
DB_UNIQUE_NAME=:数据库的唯一名称。推荐使用，如果使用了LOG_ARCHIVE_CONFIG，那么就必须有改参数。
FAL_CLIENT=，指向从库的服务名，本例为aux
FAL_SERVER 指向主库的服务名，本例为orcl
LOG_ARCHIVE_CONFIG='DG_CONFIG=(主库的db_unique_name,从库的db_unique_name)'
LOG_ARCHIVE_DEST_n:日志归档的地址，最少需要两个，一个指向主库，另一个指向从库
LOG_ARCHIVE_DEST_STATE_n ={ENABLE|DEFER|ALTERNATE|RESET} 指定:enable or disable来决定是否传输redo的数据到从库中。
LOG_FILE_NAME_CONVERT:同DB_FILE_NAME_CONVERT
STANDBY_ARCHIVE_DEST:指定路径存放接收从主库传输过来的归档日志。
STANDBY_FILE_MANAGEMENT={AUTO|MANUAL} :AUTO当主库添加或减少数据文件时会自动同步从库而不需要手动干预。
15、 经常遇到错误
错误1:
ORA-16057: DGID from server not in Data Guard configuration
原因:主库没有设置参数log_archive_config
解决方法*.log_archive_config='dg_config=(orcl,auxdb)'
alter system set log_archive_config='dg_config=(orcl,auxdb)' scope=both;
错误2:
PING[ARC0]: Heartbeat failed to connect to standby 'aux'. Error is 1031.
ORA-01031: insufficient privileges
解决问题思路:1、检查sys密码是否正确，大部分是这个原因。
错误3:Oracle用户有写standby_archive_dest的权限
ORA-16025: parameter LOG_ARCHIVE_DEST_2 contains repeated or conflicting attributes
出现该错误的原因是LOG_ARCHIVE_DEST_2='SERVICE=orcl LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcl'
db_unique_name前少了一个空格导致的
16、日常管理
（1） 启动到管理模式
SQL>shutdown immediate;
SQL>startup nomount;
SQL>alter database mount standby database;
SQL>alter database recover managed standby database disconnect from session;
（2）启动到只读方式
SQL>shutdown immediate;
SQL>startup nomount;
SQL>alter database mount standby database;
SQL>alter database open read only;
（3）在管理恢复模式下到只读模式
SQL>recover managed standby database cancel;
SQL>alter database open read only;
这个时候，可以给数据库增加临时数据文件（这个在热备份的时候是没有备份过来的）
如
alter tablespace temp add tempfile '/oracle/oradata/ddd/temp01.dbf' size 100M;
（4）从只读方式到管理恢复方式
SQL>recover managed standby database disconnect from session;
（5）打开备库
＃sqlplus /nolog
Sql> connsys/sys@standbyas sysdba;
Sql> startup mount;
Sql> alter database recover managed standby database disconnect from session;
如果要取消恢复：alter database recover managed standby database cancel;
（5）打开主库
# sqlplus /nolog
Sql> connsys/sys@primaryas sysdba;
Sql> startup;
（6）测试是否ＯＫ
主库：
Sql>alter system switch logfile;
Sql> select max(SEQUENCE#) from v$archived_log;
备库：
Sql> select max(SEQUENCE#) from v$archived_log;
#或者更详细的：select FIRST_TIME,NEXT_TIME, APPLIED,SEQUENCE# from v$archived_log order by SEQUENCE#;
17 角色转换（switchover）
注意：Swithover时只能先从Primary切到Standby，再从Standby切到Primary.启动备库，再启动主库，先停主库再停备库。
主库：
SQL> alter database commit to switchover to physical standby with session shutdown;
alter database mount standby database;
alter database recover managed standby database disconnect;
select OPEN_MODE, PROTECTION_MODE, ACTIVATION#,DATABASE_ROLE, SWITCHOVER# ,SWITCHOVER_STATUS from v$database;
OPEN_MODE PROTECTION_MODE ACTIVATION# DATABASE_ROLE SWITCHOVER# SWITCHOVER_STATUS
---------- -------------------- ----------- ---------------- ----------- --------------------
MOUNTED MAXIMUM PERFORMANCE 0 PHYSICAL STANDBY 0 SESSIONS ACTIVE
备库：
SQL>
select OPEN_MODE, PROTECTION_MODE, ACTIVATION#,DATABASE_ROLE, SWITCHOVER# ,SWITCHOVER_STATUS from v$database;
OPEN_MODE PROTECTION_MODE ACTIVATION# DATABASE_ROLE SWITCHOVER# SWITCHOVER_STATUS
---------- - ------------------- ----------- ---------------- ----------- --------------------
MOUNTED MAXIMUM PERFORMANCE 0 PHYSICAL STANDBY 0 TO PRIMARY
SQL> alter database commit to switchover to physical primary with session shutdown;
SQL>shutdown immediate
SQL> startup
Database altered.
角色转换工作完成。剩下的是补救措施(针对原primary 数据库)，由于此时primary 数据库已经不再是
data guard 配置的一部分，我们需要做的就是尝试看看能否恢复原primary 数据库，将其改造为新的standby
服务器。具体操作方式可以分为二类：1.重建2.备份恢复。所涉及的技术前面的系列文章中均有涉及，此
处不再赘述。
SQL> alter database commit to switchover to physical standby with session shutdown;
alter database commit to switchover to physical standby with session shutdown
*
ERROR at line 1:
ORA-16416: Switchover target is not synchronized with the primary
更简单的方式就是重新从主库创建一个备用控制文件，就可以启动了。
3.1 准备备机备库日志(在原先的主库，即现在的备库上操作)
alter database add standby logfile thread 1 group 7 size 50M;
alter database add standby logfile thread 1 group 8 size 50M;
alter database add standby logfile thread 1 group 9 size 50M;
alter database add standby logfile thread 2 group 10 size 50M;
alter database add standby logfile thread 2 group 11size 50M;
alter database add standby logfile thread 2 group 12 size 50M;
alter database add standby logfile thread 1 group 13 size 50M;
alter database add standby logfile thread 2 group 14 size 50M;
3.2 准备主库和备库的参数文件
准备主库和备库的参数文件，最好就是将两个数据库的参数文件互换，在两台机器上同时保留主库和备库的参数文件,同时更换两台机的tnsnames.ora文件primary和standby相关ip。
3.3 查看状态
SQL>select OPEN_MODE, PROTECTION_MODE, ACTIVATION#,DATABASE_ROLE, SWITCHOVER# ,SWITCHOVER_STATUS from v$database;
状态如果是to standby或者to primary，则可以转换。如果是session active.则有会话没有断开。
详见三思笔记。
3.4 从primary 切换到standby
SQL> connsys/sys@primaryas sysdba;
SQL> alter database commit to switchover to physical standby with session shutdown;
SQL> shutdown
SQL> create spfile from pfile= C:oracleproduct10.2.0adminfmispfileinit.standby;
SQL> alter database recover managed standby database disconnect;
SQL> startup mount;
检查状态和日志传输，同步情况。
3.5 启动新备库端的Listener (port=1522)
[oracle@host160 admin]$ lsnrctl stop
[oracle@host160 admin]$ lsnrctl start LISTENER1
3.6 从standby 切换到primary
SQL> connsys/sys@standby as sysdba;
SQL> alter database commit to switchover to primary;
SQL> shutdown
SQL> create spfile from pfile= C:oracleproduct10.2.0adminfmispfileinit.primary;
SQL> startup
检查状态和日志传输，同步情况。
3.7 启动新主库端的Listener (port=1522)
[oracle@host161 admin]$ lsnrctl stop
[oracle@host161 admin]$ lsnrctl start LISTENER1
3.8 日志文件丢失注册
如果主备数据库日志丢失，可以把备用日志文件复制过来，或者重新注册。
alter database register logfile 'filename'
18、Data Guard数据库模式的转换
将一个data guard配置从最高性能模式改为最高可用性模式
切换时：RAC环境，切换时primary和standby 都只能有一个instance活动，其它的instance必须关闭。
关闭第二个rac实例：
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
[oracle@rac2 ~]$