Oracle dataguard
1 Dataguard架构
Dataguard架构按照功能分为3部分：
日志发送
日志接收
日志应用
1.1 日志发送
日志发送由primary database的LGWR和ARCH进程完成，两者对数据保护能力和系统可用性有很大的区别
1.1.1 使用ARCH进程
（1） primary database不断产生redo log，这些日志被LGWR进程写到联机日志；
（2） 当一组联机日志写满时，就会发生日志切换，并触发本地归档；
（3） 完成本地归档之后，联机日志就可以被覆盖复用
（4） 归档日志发送到standby，在standby上应用
ARCH进程最大的问题就是：primary database只有在发生归档时才会发送日志到standby database，如果primary database发送宕机，联机日志中的redo内容就会丢失，因此，ARCH进程无法避免数据丢失的问题。
缺省情况下，primary用的就是ARCH进程。
1.1.2 使用LGWR进程的SYNC方式
Primary database产生的redo日志要同时写到日志文件和网络，必须都成功，primary database上的事务才能提交
使用LGWR进程必须明确指定，例如：
LOG_ARCHIVE_DEST_2=’SERVICE=STANDBY LGWR SYNC NET_TIMEOUT=30’
NET_TIMEOUT代表多次时间网络发送没有响应，LGWR进程会抛出错误。
1.1.3 使用LGWR进程的ASYNC方式
Primary database产生redo日志后，LGWR把日志同时提交给日志文件和本地LNS进程，但是LGWR进程只需成功写入日志文件就可以，不必等待LNSn进程的网络进程传送成功
例如：
LOG_ARCHIVE_DEST_2=’SERVICE=STANDBY LGWR ASYNC’
1.2 日志接受
Standby的归档日志目录：
归档日志目录优先使用参数：STANDBY_ARCHIVE_DEST
如果某个LOG_ARCHIVE_DEST_N参数明确定义了VALID_FOR=（standby_logfile,*）选项，则使用这个参数指定的目录。
如果STANDBY_ARCHIVE_DEST和LOG_ARCHIVE_DEST_N都没有配置，则使用STANDBY_ARCHIVE_DEST的默认值：$ORACLE_HOME/dbs/arch
1.3 日志应用
根据redo apply发生的时间又可以分成两种，一种是实时应用，这种方式必须使用standby redo log。另一种是归档时应用。
（1）如果是physical standby，可以使用下面的命令启用real-time
Alter database recover managed standby database using current logfile;
（2）如果是logic standby 可以使用下面命令启用real-time
Alter database start logical standby apply immediate
（3）查看是否使用real-time apply
Select recovery_mode from v$archive_dest_status;
1.4 数据保护模式
（1）最大可用
（2）最大保护
（3）最大性能
（4）查看保护模式
SQL> select protection_mode,protection_level from v$database;
PROTECTION_MODE PROTECTION_LEVEL
-------------------- --------------------
MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE
1.5 自动裂隙检测和解决
当primary database的某些日志没有成功发到standby database，这就发生了归档裂隙，缺失的这些日志就是裂隙。Dataguard能够自动检测解决，不需要dba的介入，这就需要配置两个参数：FAL_CLIENT、FAL_SERVER（FAL是fetch archive log的缩写）
除了自动日志缺失解决，也可以dba手工解决：
拷贝缺失的归档日志，进行注册：
alter database register logfile ‘logfilename’;
2 rac和standby配置实例
2.1 rac primary和single standby
2.1.1 实施环境：
主库：oracle 10g rac+asm
备库：oracle 10g单实例+asm
|
主库节点1 |
主库节点2 |
备库 |
|||
|
主机名 |
rac1 |
主机名 |
rac2 |
主机名 |
std |
|
Ip |
192.168.204.8 |
Ip |
192.168.204.9 |
Ip |
192.168.204.20 |
|
数据库名 |
rac |
数据库名 |
rac |
数据库名 |
std |
|
数据库实例 |
rac1 |
数据库实例 |
rac2 |
数据库实例 |
std |
该过程主要将修改主库和备库的初始化启动文件等配置信息，通过RMAN工具从主库备份数据库，再通过RMAN工具从备库恢复数据，最后等待归档日志的正常传输，实现数据库的同步。
要点：
如果Standby是rac，则接受日志和恢复日志可以不是一个实例，single instance 的日志只发送到rac的一个实例。
2.1.2 修改主库，将主库改为归档模式及force logging状态
1、 关闭所有节点的实例服务
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
2.1.3 在其中一个节点
SQL> sqlplus / as sysdba
SQL> startup mount
SQL> alter database archivelog;
SQL> alter database open;
SQL> select log_mode from v$database;
LOG_MODE
------------
ARCHIVELOG
2.1.4 将rac数据库修改为force logging
SQL> alter database force logging;
Database altered.
SQL> select inst_id,force_logging from gv$database;
INST_ID FOR
---------- ---
1 YES
2 YES
2.1.5 配置文件及备份文件
1、 在 RAC1 上创建备份目录/u01/oracle/rman_bak
2、 在std上创建相同的备份目录，以便rman能够顺利恢复
3、 下面通过 sqlplus 创建 pfile initstandby.ora
SQL> show parameter spfile
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string +DATA/rac/spfilerac.ora
两个节点用一个spfile
SQL> show parameter db_unique_name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_unique_name string rac
SQL> CREATE PFILE='/u01/oracle/rman_bak/initstandby.ora' FROM SPFILE;
File created.
2.1.6 在主库和备库上修改oracle网络服务
1、修改RAC1和RAC2的oracle的tnsnames.ora
[oracle@rac1 admin]$ vi /u01/oracle/product/10.2.0/db_1/network/admin/tnsnames.ora
增加：
standby =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.204.20)(PORT = 3173))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = standby)
)
)
2.1.7 修改Standby的tnsnames.ora和listner.ora配置
从primary拷贝过来，增加：
standby =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.204.20)(PORT = 3173))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = standby)
)
)
4、 修改Standby的oracle的.bash_profile文件
export ORACLE_SID=standby
export ORACLE_BASE=/u01/oracle
export ORACLE_HOME=$ORACLE_BASE/product/10.2.0/db_1
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
export PATH=$ORACLE_HOME/bin:$PATH
umask 022
2.1.8 新建恢复需要的目录
mkdir -p /u01/oracle/admin/standby/adump
mkdir -p /u01/oracle/admin/standby/bdump
mkdir -p /u01/oracle/admin/standby/cdump
mkdir -p /u01/oracle/admin/standby/dpdump
mkdir -p /u01/oracle/admin/standby/hdump
mkdir -p /u01/oracle/admin/standby/udump
mkdir -p /u01/oracle/admin/standby/pfile
2.1.9 创建监听
tnsping
2.1.10 配置参数文件和密码文件
cd /u01/oracle/rman_bak/
将standby上从RAC1上拷贝过来的文件initstandby.ora拷贝至$ORACLE_HOME/dbs下。
通过指令创建orapwstandby.ora,该文件包含的是sys的用户密码,注意密码一定要保证与主库一致。
[oracle@std rman_bak]$ cd /u01/oracle/product/10.2.0/db_1/dbs/
[oracle@std dbs]$ orapwd file=orapwstandby password=sys
主库：
SQL> show parameter db_unique_name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_unique_name string rac
SQL> select file_name from dba_data_files;
FILE_NAME
--------------------------------------------------------------------------------
+DATA/rac/datafile/users.259.868180115
+DATA/rac/datafile/sysaux.257.868180115
+DATA/rac/datafile/undotbs1.258.868180115
+DATA/rac/datafile/system.256.868180113
+DATA/rac/datafile/undotbs2.264.868180247
2.1.10.1 修改主库参数文件
Rac原有参数不变，增加以下参数
SQL> ALTER SYSTEM SET service_names=rac scope=both;
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_1='LOCATION=+RECV/rac/archivelog valid_for=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=rac' scope=both;
SQL> ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(rac,standby)' scope=both;
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_3='service=standby valid_for=(online_logfiles,primary_role) db_unique_name=standby' scope=both;
SQL> ALTER SYSTEM SET db_file_name_convert='+DATA/rac/datafile/','+DATA/standby/datafile/','+DATA/rac/tempfile/','+DATA/standby/tempfile/' scope=spfile;
SQL> ALTER SYSTEM SET log_file_name_convert='+RECV/rac/onlinelog','+RECV/standby/onlinelog' scope=both;
SQL> ALTER SYSTEM SET standby_file_management=auto scope=both;
SQL> ALTER SYSTEM SET fal_server='standby' scope=both;
SQL> ALTER SYSTEM SET rac1.fal_client='rac1' scope=spfile scope=both;
SQL> ALTER SYSTEM SET rac2.fal_client='rac2' scope=spfile scope=both;
加上 scope=both立即生效。
SQL> create spfile='+DATA/rac/spfilerac.ora' from pfile='/u01/oracle/rman_bak/initrac.ora';
File created.
2.1.10.2 修改备库参数文件：
可以删除的参数：
*.cluster_database_instances=2
*.cluster_database=true
*.db_create_file_dest='+DATA'
rac2.instance_number=2
rac1.instance_number=1
*.remote_listener='LISTENERS_RAC'
修改initstandby.ora文件
修改新增参数：
*.control_files='/u01/oracle/rman_bak/standby.ctl'
*.db_unique_name='standby'
*.log_archive_config='dg_config=(rac,standby)'
*.db_file_name_convert='+DATA/rac/datafile','+DATA/standby/datafile','+DATA/rac/tempfile','+DATA/standby/tempfile'
*.log_file_name_convert='+RECV/rac/onlinelog','+RECV/standby/onlinelog'
*.log_archive_dest_1='LOCATION=+DATA/standby/archivelog valid_for=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=standby'
*.LOG_ARCHIVE_DEST_2='SERVICE=rac1 LGWR ASYNC valid_for=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=rac'
*.LOG_ARCHIVE_DEST_STATE_1=ENABLE
*.LOG_ARCHIVE_DEST_STATE_2=ENABLE
*.REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
*.FAL_SERVER='rac'
*.FAL_CLIENT='standby'
*.STANDBY_FILE_MANAGEMENT='AUTO'
thread=1
undo_tablespace='UNDOTBS1'
*.user_dump_dest='/u01/oracle/admin/standby/udump'
*.audit_file_dest='/u01/oracle/admin/standby/adump'
*.background_dump_dest='/u01/oracle/admin/standby/bdump'
*.core_dump_dest='/u01/oracle/admin/standby/cdump'
*.db_unique_name='standby'
*.service_names='standby'
注：+DATA/standby/archivelog这个目录需要在备库手动创建，开始未创建，结果一直报错归档目录不合法。
2.1.11 进行rman备份
备份数据文件及控制文件
RMAN> backup FORMAT '/u01/oracle/rman_bak/db_%U' database plus archivelog;
-----------------delete all input;不要这个
RMAN> BACKUP FORMAT '/u01/oracle/rman_bak/control_%U' CURRENT CONTROLFILE FOR STANDBY;
注：
在controlfile中记录着每一个archivelog的相关信息，OS下把这些物理文件delete掉后，在controlfile中仍然记录着这些archivelog的信息，
当我们手工清除archive目录下的文件后，这些记录并没有被我们从controlfile中清除掉，也就是oracle并不知道这些文件已经不存在了!
RMAN> crosscheck archivelog all;
RMAN> delete expired archivelog all;
进行几次归档
SQL>Alter system archive log current;
主库创建备库的控制文件
SQL> alter database create standby controlfile as '/u01/oracle/rman_bak/standby.ctl';
Database altered.
将RAC1上的/u01/oracle/rman_bak/下的所有文件，包含RMAN备份文件,tnsnames.ora, listener.ora和initstandby.ora拷贝至Standby的同样的目录上。
[oracle@std rman_bak]$ scp oracle@192.168.204.8:/u01/oracle/rman_bak/* ./
创建spfile
[oracle@std dbs]$ export ORACLE_SID=standby
[oracle@std dbs]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.4.0 - Production on Tue Jan 6 17:30:51 2015
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to an idle instance.
SQL> create spfile from pfile='/u01/oracle/product/10.2.0/db_1/dbs/initstandby.ora';
File created.
SQL> startup nomount
2.1.12 利用rman创建standby数据库
备库启动到nomount状态
SQL> startup pfile='/u01/oracle/product/10.2.0/db_1/dbs/initstandby.ora' nomount;
ORACLE instance started.
Total System Global Area 285212672 bytes
Fixed Size 2083368 bytes
Variable Size 88081880 bytes
Database Buffers 188743680 bytes
Redo Buffers 6303744 bytes
SQL> create spfile from pfile='/u01/oracle/product/10.2.0/db_1/dbs/initstandby.ora';
File created.
主库恢复备库
[oracle@rac1 ~]$ rman target / auxiliary sys/sys@standby
Recovery Manager: Release 10.2.0.4.0 - Production on Wed Jan 7 10:52:22 2015
Copyright (c) 1982, 2007, Oracle. All rights reserved.
connected to target database: RAC (DBID=2456162188)
connected to auxiliary database: RAC (not mounted)
RMAN> duplicate target database for standby;
Starting Duplicate Db at 07-JAN-15
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 07-JAN-15
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=/u01/oracle/rman_bak/standby.ctl
output filename=/u01/oracle/rman_bak/standby.ctl
Finished restore at 07-JAN-15
sql statement: alter database mount standby database
released channel: ORA_AUX_DISK_1
WARNING: DB_FILE_NAME_CONVERT resulted in invalid ASM names; names changed to diskgroup only.
contents of Memory Script:
{
set newname for tempfile 1 to
"+data";
switch clone tempfile all;
set newname for datafile 1 to
"+data";
set newname for datafile 2 to
"+data";
set newname for datafile 3 to
"+data";
set newname for datafile 4 to
"+data";
set newname for datafile 5 to
"+data";
restore
check readonly
clone database
;
}
executing Memory Script
executing command: SET NEWNAME
renamed temporary file 1 to +data in control file
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting restore at 07-JAN-15
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 +DATA
restoring datafile 00002 to +DATA
restoring datafile 00003 to +DATA
restoring datafile 00004 to +DATA
restoring datafile 00005 to +DATA
channel ORA_AUX_DISK_1: reading from backup piece /u01/oracle/rman_bak/db_0hps54f4_1_1
channel ORA_AUX_DISK_1: restored backup piece 1
piece handle=/u01/oracle/rman_bak/db_0hps54f4_1_1 tag=TAG20150107T191548
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:45
Finished restore at 07-JAN-15
contents of Memory Script:
{
switch clone datafile all;
}
executing Memory Script
datafile 1 switched to datafile copy
input datafile copy recid=8 stamp=868395271 filename=+DATA/standby/datafile/system.260.868395229
datafile 2 switched to datafile copy
input datafile copy recid=9 stamp=868395275 filename=+DATA/standby/datafile/undotbs1.279.868395233
datafile 3 switched to datafile copy
input datafile copy recid=10 stamp=868395275 filename=+DATA/standby/datafile/sysaux.280.868395233
datafile 4 switched to datafile copy
input datafile copy recid=11 stamp=868395275 filename=+DATA/standby/datafile/users.277.868395235
datafile 5 switched to datafile copy
input datafile copy recid=12 stamp=868395275 filename=+DATA/standby/datafile/undotbs2.278.868395235
Finished Duplicate Db at 07-JAN-15
[oracle@std dbs]$ export ORACLE_SID=standby
[oracle@std dbs]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.4.0 - Production on Tue Jan 6 20:48:47 2015
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select status from v$instance;
STATUS
------------
MOUNTED
SQL> select member from v$logfile;
MEMBER
--------------------------------------------------------------------------------
+DATA/rac/onlinelog/group_2.262.868180179
+DATA/rac/onlinelog/group_1.261.868180177
+DATA/rac/onlinelog/group_3.265.868180327
+DATA/rac/onlinelog/group_4.266.868180329
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
+DATA/standby/datafile/system.266.868308455
+DATA/standby/datafile/undotbs1.268.868308459
+DATA/standby/datafile/sysaux.267.868308459
+DATA/standby/datafile/users.270.868308459
+DATA/standby/datafile/undotbs2.269.868308459
SQL> SELECT NAME FROM V$TEMPFILE;
NAME
--------------------------------------------------------------------------------
+DATA
2.1.13 创建standby redo log日志
创建原则：日志文件大小相等、日志组数量要多1
主库查询联机日志
[oracle@rac1 admin]$ export ORACLE_SID=rac1
[oracle@rac1 admin]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.4.0 - Production on Tue Jan 6 20:54:05 2015
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
SQL> SELECT THREAD#,GROUP#,BYTES/1024/1024 FROM V$LOG;
THREAD# GROUP# BYTES/1024/1024
---------- ---------- ---------------
1 1 50
1 2 50
2 3 50
2 4 50
RAC有两个redo thread ，每个thread有两个日志组，每个日志文件大小50MB，所以要针对每个thread需要创建3组standby redo log，大小为50MB。
Standby 库执行：
alter database add standby logfile thread 1 group 5 ('+DATA/standby/onlinelog/st_redo_5.rdo') size 50m;
alter database add standby logfile thread 1 group 6 ('+DATA/standby/onlinelog/st_redo_6.rdo') size 50m;
alter database add standby logfile thread 1 group 7 ('+DATA/standby/onlinelog/st_redo_7.rdo') size 50m;
alter database add standby logfile thread 2 group 8 ('+DATA/standby/onlinelog/st_redo_8.rdo') size 50m;
alter database add standby logfile thread 2 group 9 ('+DATA/standby/onlinelog/st_redo_9.rdo') size 50m;
alter database add standby logfile thread 2 group 10 ('+DATA/standby/onlinelog/st_redo_10.rdo') size 50m;
SQL> select member from v$logfile;
MEMBER
--------------------------------------------------------------------------------
+DATA/rac/onlinelog/group_2.262.868180179
+DATA/rac/onlinelog/group_1.261.868180177
+DATA/rac/onlinelog/group_3.265.868180327
+DATA/rac/onlinelog/group_4.266.868180329
+DATA/standby/onlinelog/st_redo_5.rdo
+DATA/standby/onlinelog/st_redo_6.rdo
+DATA/standby/onlinelog/st_redo_7.rdo
+DATA/standby/onlinelog/st_redo_9.rdo
+DATA/standby/onlinelog/st_redo_10.rdo
+DATA/standby/onlinelog/st_redo_8.rdo
10 rows selected.
2.1.14 开始同步
在备库上启动redo apply
SQL> recover managed standby database disconnect from session;
Media recovery complete.
2.1.15 确认同步
SQL> select status from v$instance;
STATUS
------------
MOUNTED
SQL> select a.SWITCHOVER_STATUS from v$database a;
SWITCHOVER_STATUS
--------------------
SESSIONS ACTIVE
日志应用
SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
备库执行：
select name from v$archived_log;
主库查看：
SQL> col dest_name format a20
SQL> select dest_name,status,error from v$archive_dest;
在rac某个实例上执行
SQL> create tablespace zhaoja2 datafile size 10m;
Tablespace created.
Rac每个实例上执行
SQL> alter system switch logfile;
System altered.
在standby上确认文件产生
SQL> select name from v$datafile;
2.1.16 Switchover
RAC环境，切换时primary和standby都只能有一个instance活动，其他的instance都必须关闭
(1) 在rac2上执行：
SQL> shutdown immediate;
(2) 在rac1上执行：
SQL> alter database commit to switchover to physical standby with session shutdown;
Database altered.
SQL> shutdown immediate;
ORA-01507: database not mounted
ORACLE instance shut down.
(3) 在standby上执行：
SQL> alter database commit to switchover to primary with session shutdown;
Database altered.
SQL> shutdown immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> startup
(4) 在rac1上创建standby redolog file:
SQL> startup mount;
alter database add standby logfile thread 1 group 5 size 50m;
alter database add standby logfile thread 1 group 6 size 50m;
alter database add standby logfile thread 1 group 7 size 50m;
alter database add standby logfile thread 2 group 8 size 50m;
alter database add standby logfile thread 2 group 9 size 50m;
alter database add standby logfile thread 2 group 10 size 50m;
(5) 开始同步，在rac1执行
SQL> recover managed standby database disconnect from session;
Media recovery complete
(6) 确认同步
在standby上执行：
SQL> create tablespace zhaoja4 datafile size 10m;
create tablespace zhaoja3 datafile size 10m
*
ERROR at line 1:
ORA-02236: invalid file name
SQL> create tablespace zhaoja3 datafile '+DATA/standby/datafile/zhaoja3.dbf' size 10m;
Tablespace created.
SQL> alter system switch logfile;
System altered.
在rac1上查询：
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
+DATA/rac/datafile/system.256.868180113
+DATA/rac/datafile/undotbs1.258.868180115
+DATA/rac/datafile/sysaux.257.868180115
+DATA/rac/datafile/users.259.868180115
+DATA/rac/datafile/undotbs2.264.868180247
+DATA/rac/datafile/zhaoja.276.868545559
+DATA/rac/datafile/zhaoja2.275.868614513
7 rows selected.
2.1.17 其他命令
关闭同步
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
切换日志
SQL> alter system switch logfile;
System altered.
2.2 rac primary和rac standby
最好关闭其中一个，在一个上面操作
2.2.1 配置host
2.2.1.1 Primary rac两个几点的hosts内容如下：
127.0.0.1 localhost
192.168.204.8 rac1
192.168.204.3 rac1-vip
10.10.0.2 rac1-priv
192.168.204.9 rac2
192.168.204.4 rac2-vip
10.10.0.3 rac2-priv
增加：
192.168.204.101 std1
192.168.204.102 std2
192.168.204.103 std1-vip
192.168.204.104 std2-vip
2.2.1.2 Standby rac的两个几点如下：
127.0.0.1 localhost
192.168.204.101 std1
192.168.204.103 std1-vip
10.10.0.11 std1-priv
192.168.204.102 std2
192.168.204.104 std2-vip
10.10.0.12 std2-priv
增加：
192.168.204.8 rac1
192.168.204.9 rac2
192.168.204.3 rac1-vip
192.168.204.4 rac2-vip
2.2.2 修改参数文件
2.2.2.1 Primary参数文件
SQL> show parameter db_unique_name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_unique_name string rac
SQL> ALTER SYSTEM SET service_names=rac scope=both;
SQL> ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(rac,std)' scope=both;
SQL> ALTER SYSTEM SET db_file_name_convert='+DATA/rac/datafile/','+DATA/std/datafile/','+DATA/rac/tempfile/','+DATA/std/tempfile/' scope=spfile;
SQL> ALTER SYSTEM SET log_file_name_convert='+RECV/rac/onlinelog','+RECV/std/onlinelog' scope=both;
ERROR at line 1:
ORA-02095: specified initialization parameter cannot be modified
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_1='LOCATION=+RECV/rac/archivelog valid_for=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=rac' scope=both;
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='service=std valid_for=(online_logfiles,primary_role) db_unique_name=std' scope=both;
SQL> ALTER SYSTEM SET standby_file_management=auto scope=both;
SQL> ALTER SYSTEM SET fal_client='rac' scope=both;
SQL> ALTER SYSTEM SET fal_server='std' scope=both;
2.2.2.2 创建目录
mkdir -p /u01/oracle/admin/std/adump
mkdir -p /u01/oracle/admin/std/bdump
mkdir -p /u01/oracle/admin/std/cdump
mkdir -p /u01/oracle/admin/std/dpdump
mkdir -p /u01/oracle/admin/std/hdump
mkdir -p /u01/oracle/admin/std/udump
2.2.2.3 Standby参数文件
2.2.2.4 Primary上创建参数文件，copy到备库
SQL> CREATE PFILE='/u01/oracle/rman_bak/initstd.ora' FROM SPFILE;
[oracle@std rman_bak]$ scp oracle@192.168.204.8:/u01/oracle/rman_bak/initstd.ora ./
2.2.2.5 修改
*.control_files='+data/std/controlfile/Current.260.868180173'
*.log_archive_dest_1='LOCATION=+RECV/std/archivelog valid_for=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=std'
*.log_archive_dest_2='service=std valid_for=(online_logfiles,primary_role) db_unique_name=rac'
*.db_name='rac'
*.db_unique_name='std'
*.service_names='std'
std2.instance_number=2
std1.instance_number=1
*.audit_file_dest='/u01/oracle/admin/std/adump'
*.background_dump_dest='/u01/oracle/admin/std/bdump'
*.core_dump_dest='/u01/oracle/admin/std/cdump'
*.user_dump_dest='/u01/oracle/admin/std/udump'
去掉
rac1.local_listener='LISTENER_RAC1'
rac2.local_listener='LISTENER_RAC2'
*.local_listener='(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.204.8)(PORT = 3173))'
Asm上没有的目录需要手工创建下。
2.2.2.6 在ASM中创建standby的spfile
[oracle@std1 soft]$ export ORACLE_SID=+ASM1
SQL> alter diskgroup data add directory '+data/std';
或者通过 asmcmd -p 进入目录后用mkdir创建
[oracle@std1 soft]$ export ORACLE_SID=std1
[oracle@std1 soft]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.4.0 - Production on Wed Jan 14 17:52:14 2015
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to an idle instance.
SQL> create spfile='+DATA/std/spfilestd.ora' from pfile='/u01/oracle/rman_bak/initstd.ora';
2.2.2.7 为两个实例准备pfile
[oracle@std2 ~]$ cd /u01/oracle/product/10.2.0/db_1/dbs
[oracle@std1 dbs]$ echo "spfile='+DATA/std/spfilestd.ora'">initstd1.ora
[oracle@std2 dbs]$ echo "spfile='+DATA/std/spfilestd.ora'">initstd2.ora
2.2.3 为两个实例准备密码文件
[oracle@std1 dbs]$ orapwd file=orapwstd1 password=sys entries=5
[oracle@std2 dbs]$ orapwd file=orapwstd2 password=sys entries=5
2.2.4 备份主库
RMAN> backup FORMAT '/u01/oracle/rman_bak/db_%U' database;
RMAN> backup FORMAT '/u01/oracle/rman_bak/con_%U' current controlfile for standby;
[oracle@std rman_bak]$ scp oracle@192.168.204.8:/u01/oracle/rman_bak/* ./
2.2.5 创建standby
在PRIMARY的RAC1节点
[oracle@rac1 admin]$ rman target / auxiliary sys/sys@std
RMAN> duplicate target database for standby;
此时备库状态：
节点std1
SQL> select status from v$instance;
STATUS
------------
MOUNTED
注：Standby一个节点为nomount状态,这里我只启动了一个，等恢复完之后，再启动另外一个为mount状态。
2.2.6 创建standby redo log日志
创建原则：日志文件大小相等、日志组数量要多1
主库查询联机日志
[oracle@rac1 admin]$ export ORACLE_SID=rac1
[oracle@rac1 admin]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.4.0 - Production on Tue Jan 6 20:54:05 2015
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
SQL> SELECT THREAD#,GROUP#,BYTES/1024/1024 FROM V$LOG;
THREAD# GROUP# BYTES/1024/1024
---------- ---------- ---------------
1 1 50
1 2 50
2 3 50
2 4 50
RAC有两个redo thread ，每个thread有两个日志组，每个日志文件大小50MB，所以要针对每个thread需要创建3组standby redo log，大小为50MB。
Standby 库执行：
alter database add standby logfile thread 1 group 5 ('+DATA/std/onlinelog/st_redo_5.rdo') size 50m;
alter database add standby logfile thread 1 group 6 ('+DATA/std/onlinelog/st_redo_6.rdo') size 50m;
alter database add standby logfile thread 1 group 7 ('+DATA/std/onlinelog/st_redo_7.rdo') size 50m;
alter database add standby logfile thread 2 group 8 ('+DATA/std/onlinelog/st_redo_8.rdo') size 50m;
alter database add standby logfile thread 2 group 9 ('+DATA/std/onlinelog/st_redo_9.rdo') size 50m;
alter database add standby logfile thread 2 group 10 ('+DATA/std/onlinelog/st_redo_10.rdo') size 50m;
SQL> select member from v$logfile;
MEMBER
--------------------------------------------------------------------------------
+DATA/rac/onlinelog/group_2.262.868180179
+DATA/rac/onlinelog/group_1.261.868180177
+DATA/rac/onlinelog/group_3.265.868180327
+DATA/rac/onlinelog/group_4.266.868180329
+DATA/standby/onlinelog/st_redo_5.rdo
+DATA/standby/onlinelog/st_redo_6.rdo
+DATA/standby/onlinelog/st_redo_7.rdo
+DATA/standby/onlinelog/st_redo_9.rdo
+DATA/standby/onlinelog/st_redo_10.rdo
+DATA/standby/onlinelog/st_redo_8.rdo
10 rows selected.
2.2.7 把standby rac注册到crs中
因为standby rac不是通过dbca等工具创建，而是手工创建，所以不会自动注册到crs中，需要手工注册，下面操作可以在任何一个节点操作：
2.2.7.1 添加数据库和实例
[oracle@std1 rman_bak]$ srvctl add database -d std -o /u01/oracle/product/10.2.0/db_1/
[oracle@std1 rman_bak]$ srvctl add instance -d std -i std1 -n std1
[oracle@std1 rman_bak]$ srvctl add instance -d std -i std2 -n std2
Usage: srvctl add instance -d <name> -i <inst_name> -n <node_name>
2.2.7.2 指定数据库实例依赖ASM实例
[oracle@std1 rman_bak]$ srvctl modify instance -d std -i std1 -s +ASM1
[oracle@std1 rman_bak]$ srvctl modify instance -d std -i std2 -s +ASM2
如果ASM也需要手工注册???????????????????
[oracle@std1 rman_bak]$ srvctl add asm -n std1 -i +ASM1 -o /u01/oracle/product/10.2.0/db_1/ -p /u01/oracle/product/10.2.0/db_1/dbs/spfile+ASM1.ora
PRKS-1000 : ASM instance "+ASM1" already exists on node "std1"
系统启动时，自动启动ASM
[oracle@std1 rman_bak]$ srvctl enable asm -n std1 -i +ASM1
[oracle@std1 rman_bak]$ srvctl enable asm -n std2 -i +ASM2
2.2.8 在任意一个节点开始恢复，节点状态为mount
SQL> alter database recover managed standby database disconnect from session;
Database altered.
2.2.9 测试
在primary创建一个表空间
SQL> create tablespace zhaoja17 datafile size 10m;
Tablespace created.
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
+DATA/rac/datafile/system.256.868180113
+DATA/rac/datafile/undotbs1.258.868180115
+DATA/rac/datafile/sysaux.257.868180115
+DATA/rac/datafile/users.259.868180115
+DATA/rac/datafile/undotbs2.264.868180247
+DATA/rac/datafile/zhaoja.276.868545559
+DATA/rac/datafile/zhaoja2.275.868614513
+DATA/rac/datafile/zhaoja3.273.868639251
+DATA/rac/datafile/zhaoja4.277.868785219
+DATA/rac/datafile/zhaoja15.278.869083171
10 rows selected.
执行日志切换
SQL> alter system switch logfile;
System altered.
在standby rac上查看
SQL> select name from v$datafile;
SQL> SELECT PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS FROM V$MANAGED_STANDBY;
日志应用
SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
2.2.10 Switchover
3 问题：
3.1 WARNING: DB_FILE_NAME_CONVERT resulted in invalid ASM names; names changed to diskgroup only.
http://docs.oracle.com/cd/E11882_01/backup.112/e10642/rcmtspit.htm#CIHHGCGF
If the DB_FILE_NAME_CONVERT and LOG_FILE_NAME_CONVERT parameters change a substring other than the disk group name, the conversion is ignored and the resulting disk group name is used, for example:
DB_FILE_NAME_CONVERT='+DATAFILE/prod','+DATAFILE/tspitr'
The preceding command results in an invalid ASM OMF file name and the change is ignored. Instead, the files are created in disk group name +DATAFILE and the following message is issued:
WARNING: DB_FILE_NAME_CONVERT resulted in invalid ASM names; names changed to disk group only
3.2 RMAN> duplicate target database for standby;
报错：
restoring datafile 00001 to +DATA
restoring datafile 00002 to +DATA
restoring datafile 00003 to +DATA
restoring datafile 00004 to +DATA
restoring datafile 00005 to +DATA
channel ORA_AUX_DISK_1: reading from backup piece /u01/oracle/rman_bak/db_0hps54f4_1_1
ORA-19870: error reading backup piece /u01/oracle/rman_bak/db_0hps54f4_1_1
ORA-19504: failed to create file "+DATA"
ORA-17502: ksfdcre:4 Failed to create file +DATA
ORA-15001: diskgroup "DATA" does not exist or is not mounted
ORA-15077: could not locate ASM instance serving a required diskgroup
failover to previous backup
错误原因：
进入asm磁盘组，删除已有的文件
[oracle@std dbs]$ export ORACLE_SID=+ASM
[oracle@std dbs]$ asmcmd -p
ASMCMD [+] > ls
DATA/
RECV/
ASMCMD [+] > cd data
ASMCMD [+data] > ls
ASMCMD [+data/standby/datafile] > rm -rf *
3.3 日志未传送
(1)
SQL> select dest_name,status,error from v$archive_dest;
LOG_ARCHIVE_DEST_3 ERROR
ORA-00254: error in archive control string ''
查看错误日志
[oracle@rac1 dbs]$ tail -20 /u01/oracle/admin/rac/bdump/alert_rac1.log
Errors in file /u01/oracle/admin/rac/bdump/rac1_arc1_26523.trc:
ORA-00254: error in archive control string ''
Thu Jan 8 09:05:01 2015
FAL[server, ARC1]: FAL archive failed, see trace file.
Thu Jan 8 09:05:01 2015
Errors in file /u01/oracle/admin/rac/bdump/rac1_arc1_26523.trc:
ORA-16055: FAL request rejected
ARCH: FAL archive failed. Archiver continuing
Thu Jan 8 09:05:01 2015
ORACLE Instance rac1 - Archival Error. Archiver continuing.
Thu Jan 8 09:05:03 2015
Errors in file /u01/oracle/admin/rac/bdump/rac1_arc1_26523.trc:
ORA-00254: error in archive control string ''
FAL[server, ARC1]: FAL archive failed, see trace file.
Thu Jan 8 09:05:03 2015
Errors in file /u01/oracle/admin/rac/bdump/rac1_arc1_26523.trc:
ORA-16055: FAL request rejected
ARCH: FAL archive failed. Archiver continuing
Thu Jan 8 09:05:03 2015
ORACLE Instance rac1 - Archival Error. Archiver continuing.
查看trace文件
[oracle@rac1 dbs]$ tail -20 /u01/oracle/admin/rac/bdump/rac1_arc1_26523.trc
ORA-16055: FAL request rejected
ARCH: Connecting to console port...
ARCH: Connecting to console port...
FAL[server]: Selected inactive destination; resetting temporarily
Redo shipping client performing standby login
*** 2015-01-08 09:05:03.142 66535 kcrr.c
Logged on to standby successfully
Client logon and security negotiation successful!
Error 254 creating standby archive log file at host 'standby'
*** 2015-01-08 09:05:03.172 62692 kcrr.c
ARC1: Attempting destination LOG_ARCHIVE_DEST_3 network reconnect (254)
*** 2015-01-08 09:05:03.172 62692 kcrr.c
ARC1: Destination LOG_ARCHIVE_DEST_3 network reconnect abandoned
ORA-00254: error in archive control string ''
*** 2015-01-08 09:05:03.172 60970 kcrr.c
kcrrfail: dest:3 err:254 force:0 blast:1
kcrrwkx: unknown error:254
ORA-16055: FAL request rejected
ARCH: Connecting to console port...
ARCH: Connecting to console port...
查看错误代码
[oracle@rac1 rman_bak]$ oerr ora 254
00254, 00000, "error in archive control string '%s'"
// *Cause: The specified archive log location is invalid in the archive
// command or the LOG_ARCHIVE_DEST initialization parameter.
// *Action: Check the archive string used to make sure it refers to a valid
// online device.
解决办法：
LOG_ARCHIVE_DEST配置的不对，standby的参数文件：service_names未配置
(2)
PING[ARC0]: Heartbeat failed to connect to standby 'std'. Error is 16058
[oracle@rac1 admin]$ oerr ora 16058
16058, 00000, "standby database instance is not mounted"
// *Cause: The RFS process on the standby database received an internal error.
// *Action: Check the standby alert log and RFS trace files for more
// information.
查询备库alert日志，发现备库的归档日志目录由于未手工创建，报错，创建目录，正常。
3.4 其他莫名其妙的错误：
如果出现了莫名其妙的错误，绝大多数是某个参数配置错误（参数不合法），验证参数合法性即可。
3.4 日志传送了，但是未应用
SQL> SELECT PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS FROM V$MANAGED_STANDBY;
PROCESS STATUS THREAD# SEQUENCE# BLOCK# BLOCKS
--------- ------------ ---------- ---------- ---------- ----------
ARCH CLOSING 1 42 1 51
ARCH CLOSING 2 36 1 24
RFS IDLE 0 0 0 0
RFS IDLE 0 0 0 0
MRP0 WAIT_FOR_GAP 1 38 0 0
这是在等38吗？查看主库38的归档已经被我删了，重新再恢复个standby试试,重新备份恢复standby正常。
Dataguard模式
SQL> select DATABASE_ROLE,PROTECTION_MODE,PROTECTION_LEVEL from v$database;
DATABASE_ROLE PROTECTION_MODE PROTECTION_LEVEL
---------------- -------------------- --------------------
PHYSICAL STANDBY MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE
3.5 删除监听
[oracle@std1 bin]$ ./crs_stop ora.std1.LISTENER_STD1.lsnr
Attempting to stop `ora.std1.LISTENER_STD1.lsnr` on member `std1`
Stop of `ora.std1.LISTENER_STD1.lsnr` on member `std1` succeeded.
[oracle@std1 bin]$ ./crs_unregister ora.std1.LISTENER_STD1.lsnr
[oracle@std1 bin]$ ./crs_unregister ora.std2.LISTENER_STD2.lsnr
3.6 ERROR:ORA-12528: TNS:listener: all appropriate instances are blocking new connections
总结ORA-12528问题是因为监听中的服务使用了动态服务,系统启动后,数据库没有正常的MOUNT,因此在动态模式下,就会出现这个问题，用静态的就不会有这个问题,因此上面的方法就是:
把监听设置为静态
在tnsnames.ora中追加(UR=A)
从启动服务
用静态注册的风险是，如果在instance运行中，lisener重新启动，就找不到instance了。换言之，静态注册需要先启动lisener，再启动instance。而且静态模式下，lisener status显示的是unknown
而且，用动态的话，instance nomount状态下，一般是dba需要进行操作（比如恢复），这个时候一般直接登陆到host进行，不使用tns远程连接
3.7 database name 'RAC' in control file is not
db_name参数设置问题