Oracle 11g RAC Data Guard 物理standby 建立

最新推荐文章于 2021-04-06 19:53:37 发布

cqgmau820668

最新推荐文章于 2021-04-06 19:53:37 发布

阅读量177

点赞数

文章标签：数据库网络操作系统

创建物理standby数据库，需要创建一个与RAC相同的操作系统，相同版本的restart database 数据库环境，为数据库创建两个ASM磁盘组：DBFILE1和FRA1用于存放数据文件和闪回恢复设置。

建立环境

虚拟机：VMware Workstation 9.0

集群环境：RACDB 192.168.1.61/62 DB:oracle 11g R2 OS:readhat linux 5.5

DG 物料standby:RACDG 192.168.1.70 DB:oracle 11g R2 OS:readhat linux 5.5

一，创建standby数据库，

创建和主数据库相同的restart database数据库环境。

1，配置环境

[root@racdg ~]# fdisk -l

Units = cylinders of 16065 * 512 = 8225280 bytes

Device Boot Start End Blocks Id System

/dev/sda1 * 1 13 104391 83 Linux

/dev/sda2 14 5221 41833260 8e Linux LVM

Disk /dev/sdb: 12.8 GB, 12884901888 bytes

255 heads, 63 sectors/track, 1566 cylinders

Units = cylinders of 16065 * 512 = 8225280 bytes

Device Boot Start End Blocks Id System

/dev/sdb1 1 1566 12578863+ 83 Linux

Disk /dev/sdc: 4294 MB, 4294967296 bytes

255 heads, 63 sectors/track, 522 cylinders

Units = cylinders of 16065 * 512 = 8225280 bytes

Device Boot Start End Blocks Id System

/dev/sdc1 1 522 4192933+ 83 Linux

Disk /dev/sdd: 4294 MB, 4294967296 bytes

255 heads, 63 sectors/track, 522 cylinders

Units = cylinders of 16065 * 512 = 8225280 bytes

Device Boot Start End Blocks Id System

/dev/sdd1 1 522 4192933+ 83 Linux

2,首先安装Grid Infrastructure

[root@racdg ~]# /etc/init.d/oracleasm configure

Configuring the Oracle ASM library driver.

This will configure the on-boot properties of the Oracle ASM library

driver. The following questions will determine whether the driver is

loaded on boot and what permissions it will have. The current values

will be shown in brackets ('[]'). Hitting without typing an

answer will keep that current value. Ctrl-C will abort.

Default user to own the driver interface []: grid

Default group to own the driver interface []: dba

Start Oracle ASM library driver on boot (y/n) [n]: y

Scan for Oracle ASM disks on boot (y/n) [y]: y

Writing Oracle ASM library driver configuration: done

Initializing the Oracle ASMLib driver: [ OK ]

Scanning the system for Oracle ASMLib disks: [ OK ]

[root@racdg ~]# /etc/init.d/oracleasm createdisk DATA /dev/sdb1 --创建ASM磁盘

Marking disk "DATAFILE1" as an ASM disk: [ OK ]

[root@racdg ~]# /etc/init.d/oracleasm createdisk ARCH /dev/sdc1

Marking disk "FRA1" as an ASM disk: [ OK ]

[root@racdg ~]# /etc/init.d/oracleasm createdisk CRS /dev/sdd1

Marking disk "CRS" as an ASM disk: [ OK ]

[root@racdg ~]# /etc/init.d//oracleasm scandisks

Scanning the system for Oracle ASMLib disks: [ OK ]

[root@racdg ~]# /etc/init.d//oracleasm listdisks

CRS

DATAFILE1

FRA1

[root@racdg ~]# cd /etc/rc5.d/ ---开机设定启动

[root@racdg rc5.d]# ln -s /etc/init.d/oracleasm S99oracleasm

[root@racdg rc5.d]# ln -s /etc/init.d/oracleasm K01oracleasm

[grid@racdg ~]$ vi .bash_profile --配置环境变量

TMP=/tmp;export TMP

TMPDIR=$TMP;export TMPDIR

ORACLE_HOSTNAME=racdg; export ORACLE_HOSTNAME

ORACLE_BASE=/u01/app/grid; export ORACLE_BASE

ORACLE_HOME=/u01/app/11.2.0/grid; export ORACLE_HOME

#ORACLE_PATH=/u01/app/oracle/common/oracle/sql; export ORACLE_PATH

ORACLE_SID=+ASMDG; export ORACLE_SID

ORACLE_UNQNAME=ractx; export ORACLE_UNQNAME

ORACLE_TERM=xterm; export ORACLE_TERM

PATH=/user/bin:$PATH;export PATH

PATH=$ORACLE_HOME/bin:$PATH; export PATH

LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/bin;export LD_LIBARRY_PATH

CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib;export CLASSPATH

[grid@racdg ~]$ source .bash_profile ---生效

安装 grid infrastructure 记得选择 install oracle grid infrastrcture only

执行脚本

[root@racdg11.2.0]# /u01/app/oraInventory/orainstRoot.sh

[root@racdg11.2.0]# /u01/app/11.2.0/grid/root.sh

生成两个可执行的命令

1）配置stand-alone 执行任务

To configure Grid Infrastructure for a Stand-Alone Server run the following command as the root user:

/u01/app/11.2.0/grid/perl/bin/perl -I/u01/app/11.2.0/grid/perl/lib -I/u01/app/11.2.0/grid/crs/install /u01/app/11.2.0/grid/crs/install/roothas.pl

2）配置Cluster 命令

To configure Grid Infrastructure for a Cluster perform the following steps:

1. Provide values for Grid Infrastructure configuration parameters in the file - /u01/app/11.2.0/grid/crs/install/crsconfig_params. For details on how to do this, see the installation guide.

2. Run the following command as the root user:

/u01/app/11.2.0/grid/perl/bin/perl -I/u01/app/11.2.0/grid/perl/lib -I/u01/app/11.2.0/grid/crs/install /u01/app/11.2.0/grid/crs/install/rootcrs.pl

禁止掉SELinux

[root@zdtspcdb grub]# vi /etc/selinux/config

SELINUX=disabled

SELINUXTYPE=targeted

修改SELINUX=disabled ，重啟生效。也可不重啟，直接運行命令setenforce 0

根据需要我们执行1）

[root@racdg bin]# /u01/app/11.2.0/grid/perl/bin/perl -I/u01/app/11.2.0/grid/perl/lib -I/u01/app/11.2.0/grid/crs/install /u01/app/11.2.0/grid/crs/install/roothas.pl

2013-11-05 23:58:15: Checking for super user privileges

2013-11-05 23:58:15: User has super user privileges

2013-11-05 23:58:15: Parsing the host name

Using configuration parameter file: /u01/app/11.2.0/grid/crs/install/crsconfig_params

Improper Oracle Clusterware configuration found on this host

Deconfigure the existing cluster configuration before starting

to configure a new Clusterware

run '/u01/app/11.2.0/grid/crs/install/rootcrs.pl -deconfig'

to configure existing failed configuration and then rerun root.sh

解决方法 :删除节点重建

[root@racdg bin]# /u01/app/11.2.0/grid/crs/install/rootcrs.pl -deconfig -verbose -force

[root@racdg oracle]# /u01/app/11.2.0/grid/perl/bin/perl -I/u01/app/11.2.0/grid/perl/lib -I/u01/app/11.2.0/grid/crs/install /u01/app/11.2.0/grid/crs/install/roothas.pl

2013-11-06 00:16:16: Checking for super user privileges

2013-11-06 00:16:16: User has super user privileges

2013-11-06 00:16:16: Parsing the host name

Using configuration parameter file: /u01/app/11.2.0/grid/crs/install/crsconfig_params

LOCAL ADD MODE

Creating OCR keys for user 'grid', privgrp 'dba'..

Operation successful.

CRS-4664: Node racdg successfully pinned.

Adding daemon to inittab

CRS-4123: Oracle High Availability Services has been started.

ohasd is starting

racdg 2013/11/06 00:16:54 /u01/app/11.2.0/grid/cdata/racdg/backup_20131106_001654.olr

Successfully configured Oracle Grid Infrastructure for a Standalone Server

[root@racdg bin]# ./crsctl check has

CRS-4638: Oracle High Availability Services is online

[root@racdg bin]# ./crsctl check css

CRS-4530: Communications failure contacting Cluster Synchronization Services daemon

OCR检查

[root@racdg bin]# cat /etc/oracle/ocr.loc

ocrconfig_loc=/u01/app/11.2.0/grid/cdata/localhost/local.ocr

local_only=TRUE

[root@racdg bin]# ./ocrcheck

Status of Oracle Cluster Registry is as follows :

Version : 3

Total space (kbytes) : 262120

Used space (kbytes) : 120

Available space (kbytes) : 262000

ID : 1282890274

Device/File Name : /u01/app/11.2.0/grid/cdata/localhost/local.ocr

Device/File integrity check succeeded

Device/File not configured

Cluster registry integrity check succeeded

Logical corruption check succeeded

二，安装RDBMS,创建实列

[root@racdg ~]# /u01/app/oracle/product/11.2.0/dbhome_1/root.sh

Running Oracle11groot.sh script...

a，启用强制日志

[oracle@rac1 ~]$ sqlplus / as sysdba

SQL*Plus: Release11.2.0.1.0 Production on Wed Nov 6 08:46:52 2013

Connected to:

Oracle Database11gEnterprise Edition Release11.2.0.1.0 - 64bit Production

With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,

Data Mining and Real Application Testing options

SQL> select force_logging from v$database;

FORCE_LOG

---------

SQL> select force_logging from v$database;

FORCE_LOG

---------

YES

b，启用归档模式\

startup mount

alter database archivelog

alter database open

c,配置主数据库的standby redo 日志

SQL> select THREAD#,bytes/1024/1024,members from v$log;

THREAD# BYTES/1024/1024 MEMBERS

---------- --------------- ----------

1 50 2

2 50 2

4 rows selected.

在主数据库创建pfile

SQL> create pfile='/tmp/initRACDB.ora' from spfile;

在'/tmp/initRACDB.ora增加以下内容

*.DB_UNIQUE_NAMR=RACDB
*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(RACDB,RACDG)'
*.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=RACDG LGWR VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME= RACDG'

LOG_ARCHIVE_DEST_STATE_1=ENABLE

LOG_ARCHIVE_DEST_STATE_2=ENABLE
*.FAL_SERVER='RACDB'
*.STANDBY_ARCHIVE_DEST='/oradata/arch'
*.RACDB1.fal_client=RACDB1
*.RACDB2.fal_client=RACDB2
*.STANDBY_FILE_MANAGEMENT=AUTO
*.DB_FILE_NAME_CONVERT=+DATA1/RACDB/DATAFILE/','+DATA1/RACDG/DATAFILE/'

*.LOG_FILE_NAME_CONVERT='+ARCH','+ARCH'

Standby pfile 参数文件

*.cluster_database=true

*.compatible='11.2.0.0.0'

*.controCC"^l_files='+DATA1/racdg/controlfile/control01.ctl','+ARCH/racdg/controlfile/control02.ctl'

*.db_block_size=8192

*.db_create_file_dest='+DBFILE1'

*.db_create_online_log_dest_1='+DATA1'

*.db_create_online_log_dest_2='+ARCH'

*.db_domain=''

*.DB_FILE_NAME_CONVERT='+DATA1/RACDB/DATAFILE/','+DATA1/RACDG/DATAFILE/'

*.db_name='RACDB'

*.db_recovery_file_dest='+ARCH'

*.db_recovery_file_dest_size=4070572032

*.DB_UNIQUE_NAME='RACDG'

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

*.dispatchers='(PROTOCOL=TCP)CC"R% (SERVICE=RACDBXDB)'

*.FAL_SERVER='RACDB'

*.global_names=TRUE

*.instance_number=1

*.job_queue_processes=20

*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(RACDB,RACDG)'

*.LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=RACDG'

*.LOG_ARCHIVE_DEST_STATE_1='ENABLE'

*.log_archive_format='%t_%s_%r.dbf'

*.log_archive_max_processes=30

*.LOG_FILE_NAME_CONVERT='+ARCH','+ARCH'

*.memory_target=1849688064

*.nls_date_format='YYYY-MM-DD HH24:MI:SS'

*.nls_laCC" nguage='SIMPLIFIED CHINESE'

三创建ASM实列

在图像化界面用ASMCA,同时创建磁盘组

[grid@racdg ~]$ sqlplus / as sysdba --创建ASM目录

SQL> alter diskgroup dbfile1 add directory '+DBFILE1/RACDG';

Diskgroup altered.

SQL> alter diskgroup fra1 add directory '+FRA1/RACDG';

Diskgroup altered.

四，创建密码文件

Primary密碼文件設置：

[oracle@rac2 dbs]$ rm orapwRACDB2

[oracle@rac1 dbs]$ orapwd file=$ORACLE_HOME/dbs/orapwRACDB1 password=manager entries=10

[oracle@rac2 dbs]$ orapwd file=$ORACLE_HOME/dbs/orapwRACDB2 password=manager entries=10

[oracle@racdg dbs]$ orapwd file=$ORACLE_HOME/dbs/orapwRACDG password=manager entries=10

五监听配置如下:

a,主数据库

listener.ora

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(GLOBAL_DBNAME = RACDG)

(ORACLE_HOME = /u01/app/oracle/11.2.0)

(SID_NAME = RACDG)

)

tnsname.ora

RACDG =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.70)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = RACDG)

(UR=A)

)

b,standby

RACDB =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.65)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = RACDB)

)

CPDB1 =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.68)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = cpdb1)

)

RACDG =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.70)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = RACDG)

)

注意：主数据库参数监听参数

SQL> show parameter remote_listener

NAME TYPE VALUE

------------------------------------ --------------------------------- ------------------------------

remote_listener string rac-scan:1521

SQL> show parameter local_listener

NAME TYPE VALUE

------------------------------------ --------------------------------- ------------------------------

remote_listener string rac-scan:1521

六，启动standby 数据库到nomount状态下，

a,到nomount状态下

[oracle@racdg admin]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Fri Nov 22 03:27:01 2013

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,

Data Mining and Real Application Testing options

SQL> startup nomunt pfile='$ORACLE_HOME/dbs/initRACDG.ora'

b ,使用rman复制活动的主数据库到standby,必须同事连接主数据库和辅助数据库

[oracle@rac1 admin]$ rman target / auxiliary sys/manager@RACDG

Recovery Manager: Release11.2.0.1.0 - Production on Sat Nov 9 01:14:36 2013

connected to target database: RACDB (DBID=827164750)

connected to auxiliary database: RACDB (not mounted)

复制数据库

RMAN> duplicate target database for standby from active database nofilenamecheck;

ORA-17629: Cannot connect to the remote database server

ORA-17627: ORA-01017: 用户名/口令无效; 登录被拒绝

ORA-17629: Cannot connect to the remote database server

解答连接方式的问题

[oracle@rac1 admin]$ rman target / auxiliary sys/manager@RACDG

正常理解的，这里貌似没有问题，前一部分“target /”表明使用操作系统认证的方式登录PROD数据库实例，后一半部分“auxiliary sys/oracle@sbdb”表明使用密码文件方式进行验证。并且，命令行提示信息上显示也已经成功登陆。因此这个报错信息很是诡异。

rman target sys /manager auxiliary sys/manager@RACDG

RMAN-03009: failure of backup command on ORA_DISK_1 channel at 11/09/2013 02:30:02

ORA-17628: Oracle error 19505 returned by remote Oracle server

RMAN-03009: failure of backup command on ORA_DISK_1 channel at 11/09/2013 02:31:03

ORA-17628: Oracle error 19505 returned by remote Oracle server

修改参数文件

*.DB_FILE_NAME_CONVERT='+DATA1/RACDB/DATAFILE/','+DBFILE1/RACDG/DATAFILE/'

七，复制完成后，启动redo apply

[oracle@racdg dbs]$ sqlplus / as sysdba

SQL*Plus: Release11.2.0.1.0 Production on Fri Nov 8 18:56:01 2013

???:

Oracle Database11gEnterprise Edition Release11.2.0.1.0 - 64bit Production

With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,

Data Mining and Real Application Testing options

SQL> select status from v$instance;

STATUS

------------

MOUNTED

SQL> alter database open read only;

启动日志应用

Errors in file /u01/app/oracle/diag/rdbms/racdg/RACDG/trace/RACDG_m000_14767.trc:

ORA-01155: 正在打开, 关闭, 装载或卸装数据库

Fri Nov 08 21:12:49 2013

查看tnsname.ora 文件

SQL> alter database open read only;

再报错

ORA-10458: standby database requires recovery

ORA-01152: file 1 was not restored from a sufficiently old backup

ORA-01110: data file 1: '+DBFILE1/racdg/datafile/system.260.830976813'

ORA-10458 signalled during: alter database open read only..

调整参数

db_recovery_file_dest_ 错误

调整重新启动RACDG mount 整理 OK

SQL> alter database recover managed standby database using current logfile;---书写错误

可以修改为 SQL> alter database recover managed standby database using current logfile disconnect from session;

查看日志无法应用，查看报错日志

[oracle@rac1 trace]$ vi alert_RACDB1.log

[oracle@rac1 trace]$ pwd

/u01/app/oracle/diag/rdbms/racdb/RACDB1/trace

Error 1017 received logging on to the standby

------------------------------------------------------------

Check that the primary and standby are using a password file

and remote_login_passwordfile is set to SHARED or EXCLUSIVE,

and that the SYS password is same in the password files.

returning error ORA-16191

------------------------------------------------------------

FAL[client, USER]: Error 16191 connecting to RACDB for fetching gap sequence

把主的密码文件直接拷贝的到备的密码文件，重新命名

主备查询

SQL> select process,status from v$managed_standby;

PROCESS STATUS

--------------------------- ------------------------------------

ARCH CLOSING

ARCH CONNECTED

ARCH CLOSING

ARCH OPENING

ARCH CLOSING

PROCESS STATUS

--------------------------- ------------------------------------

ARCH CLOSING

ARCH OPENING

ARCH CLOSING

ARCH OPENING

ARCH CLOSING

ARCH OPENING

PROCESS STATUS

--------------------------- ------------------------------------

ARCH OPENING

ARCH CLOSING

ARCH OPENING

ARCH CLOSING

ARCH CONNECTED

ARCH OPENING

LNS WRITING

31 rows selected.

SQL> select THREAD#,DEST_ID ,max(SEQUENCE#) from v$archived_log group by THREAD#,DEST_ID order by THREAD#;

THREAD# DEST_ID MAX(SEQUENCE#)

---------- ---------- --------------

1 1 184

1 2 184

2 1 181

2 2 181

查看standby 数据库

SQL> select process,status from v$managed_standby;

PROCESS STATUS

--------------------------- ------------------------------------

ARCH CONNECTED

ARCH CLOSING

PROCESS STATUS

--------------------------- ------------------------------------

ARCH CLOSING

ARCH CONNECTED

ARCH CLOSING

ARCH CONNECTED

PROCESS STATUS

--------------------------- ------------------------------------

ARCH CONNECTED

RFS IDLE

MRP0 WAIT_FOR_LOG

PROCESS STATUS

--------------------------- ------------------------------------

RFS IDLE

PROCESS STATUS

--------------------------- ------------------------------------

RFS IDLE

PROCESS STATUS

--------------------------- ------------------------------------

RFS IDLE

59 rows selected.

SQL> select THREAD#,DEST_ID ,max(SEQUENCE#) from v$archived_log group by THREAD#,DEST_ID order by THREAD#;

THREAD# DEST_ID MAX(SEQUENCE#)

---------- ---------- --------------

1 1 184

1 2 181

2 2 181

standby 数据库日志应用

SQL> SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

SEQUENCE# APPLIED

---------- ---------------------------

122 YES

123 YES

124 YES

125 YES

126 YES

127 YES

创建主数据库的spfile 文件

后面步骤可以参考操作。

八，注册standby 数据库为Restart 数据库

1，使用grid 用户登录服务器，将来listener 注册为cluster 资源

[grid@racdg ~]$ srvctl add listener -l listener -o /u01/app/11.2.0/grid/ -s

PRCR-1086 : 资源 ora.LISTENER.lsnr 已注册

[grid@racdg ~]$ srvctl start listener -l listener

PRCC-1014 : LISTENER 已在运行

[grid@racdg ~]$ srvctl stop listener

[grid@racdg ~]$ srvctl start listener -l listener

[grid@racdg ~]$ srvctl status listener -l listener

监听程序 LISTENER 已启用

监听程序 LISTENER 正在节点上运行: racdg

2，使用oracle 用户登录服务器，将来database 注册为cluster 资源

[grid@racdg ~]$ su - oracle

口令：

[oracle@racdg ~]$ srvctl add database -d RACDB -o /u01/app/ora

oracle/ oraInventory/

[oracle@racdg ~]$ srvctl add database -d RACDB -o /u01/app/oracle/product/11.2.0/dbhome_1/

3，正常关闭，开启dataguard 流程

a 关闭dg 流程

1，alter database recover managed standby database cancel

2，关闭主数据库 srvctl stop databae –d RACDB

3，关闭standby 数据库

b 开启动流程

1，启动stanby 数据库到mount

2，启动主数据库

3，开启standby 数据库日志应用

A ,alter database open read only;

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

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/15187685/viewspace-777204/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/15187685/viewspace-777204/

cqgmau820668

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
Oracle 11g RAC Data Guard 物理standby 建立

创建物理standby数据库，需要创建一个与RAC相同的操作系统，相同版本的restart database 数据库环境，为数据库创建两个ASM磁盘组：DBFILE1和FRA1用于存放数据文件和闪回恢复设置。 ...
复制链接

扫一扫