oracle rac +standby

浏览数：49 / 时间：2015年06月12日

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 [email protected]:/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

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

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

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

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

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 [email protected]:/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

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 [email protected]:/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

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...

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...

查看错误代码

[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

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远程连接