Oracle 11g RAC Data Guard 物理standby 建立

建立物理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

---------

NO

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;

OK

檢視日誌無法應用，檢視報錯日誌

[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/，如需轉載，請註明出處，否則將追究法律責任。

Oracle 11g RAC Data Guard 物理standby 建立

相關文章