ORACLE 11G RAC 主库配置单实例ADG

ORACLE RAC ASM环境下DATAGUARD搭建步骤

1、准备工作

Host IP DB_NAME DB_UNIQUE_NAME Net Service Name

主库192.168.1.209 imsdb imsdb imsdb , imsdb1, imsdb2

备库192.168.1.42 imsdb imsdb_s imsdb_s, imsdb_s imsdb_s

保护模式：默认最大性能模式

注意DataGuard正常启动顺序：

启动顺序： 先standby ,后imsdbmary;

关闭顺序： 先imsdbmary ,后standby;

数据库版本：11.2.0.3.0

1.1、[主库]备份数据库、参数文件、控制文件

ASMCMD> cp spfileimsdb_

cp /u01

---oracle---

export ORACLE_SID=imsdb1

export ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1

cd /u01/app/oracle/product/11.2.0/dbhome_1/bin

./sqlplus /nolog

export ORACLE_SID=imsdb2

export ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1

cd /u01/app/oracle/product/11.2.0/dbhome_1/bin

----oracle----------------

export ORACLE_SID=+ASM2

export ORACLE_HOME=/opt/11.2.0/grid

cd /opt/11.2.0/grid/bin

备份参数文件：

SQL>create pfile=’ +DATA/imsdb/’ from spfile=’+DATA/imsdb/’;

cp /data/11.2.0/grid/

cp /data/11.2.0/grid/+DATA/imsdb

备份控制文件：

cp 备份，手工复制, alter database backup controlfile to trace;

备份数据库：

$ rman target /

RMAN> backup full database format '/backup/backup_%T_%s_%';

Startup nomount pfile=’/u01/11.2.0/oracle/product/11.2.0/dbs/’

1.2、[主库]检查数据库是否支持Data Guard,是否归档模式，Enable force logging

$ sqlplus '/as sysdba'

确认主库处于归档模式

SQL> archive log list （先检查是否归档模式，不是则修改）

startup mount

alter database archivelog;

alter database open;

将imsdbmary数据库置为FORCE LOGGING模式

SQL>alter database force logging; (强制产生日志)

SQL> select force_logging from v$database;

1.3、[主库]如果主库没有密码文件则建立密码文件，从而可以OS验证的方式登陆（此步骤省略）

$ orapwd file=$ORACLE_HOME/dbs/orapw$ORACLE_SID

password=oracle entries=5

若主库存在密码文件，则需要将文件拷贝到备库$ORACLE_HOME/dbs

1.4、[主库]设置主库初始化参数

$ sqlplus '/as sysdba'

修改主库参数

alter system set db_unique_name=imsdb scope=spfile;

alter system set log_archive_config = 'DG_CONFIG=(imsdb,imsdb_s)' scope=spfile;

alter

alter

alter

system

system

system set

set

set

log_archive_dest_2

log_archive_dest_1='LOCATION=/oraback/backup/log/

log_archive_dest_1='LOCATION=/oraback/backup/log/

= 'SERVICE=imsdb_s reopen=60 lgwr async

VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=imsdb' sid='imsdb2' scope=spfile;

VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=imsdb' sid='imsdb1' scope=spfile;

VALID_FOR=( ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=imsdb_s' scope=spfile;

alter system set log_archive_dest_state_1 = ENABLE;

alter system set log_archive_dest_state_2 = disable;

alter system set log_archive_dest_state_3 = ENABLE;

alter system set fal_server=imsdb scope=spfile;

alter system set fal_client=imsdb_s scope=spfile;

alter system set db_file_name_convert='/data/oradata/imsdb/','+DATA1/imsdb/datafile/',’'/data/oradata/imsdb/','+DATA1/imsdb/tempfile scope=spfile ;

alter system set log_file_name_convert='/data/oradata/imsdb/','+DATA1/imsdb/onlinelog/' scope=spfile ;

alter system set standby_file_management='AUTO' scope=both;

然后重启数据库：

SQL> shutdown immediate

SQL> startup;

1.5、[备库]上修改配置文件,添加静态监听

备库

grid@:/grid/product/11.2.0.4/network/admin >vi

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(GLOBAL_DBNAME = imsdb)

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

(SID_NAME = imsdb)

)

)

)

1.6、[主/备库]上修改配置文件

1.6.1、主库配置：

--$ORACLE_HOME/network/admin 修改ORACLE_HOME目录下的

/u01/11.2.0/oracle/product/11.2.0/network/admin/

imsdb =

(DESCRIPTION =

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

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = imsdb)

)

)

imsdb_s =

(DESCRIPTION =

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

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = imsdb)

)

)

Imsdb1 =

(DESCRIPTION =

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

(CONNECT_DATA =

(SERVER = DEDICATED)

(SID = imsdb1)

)

)

1.6.2、备份库配置：

imsdb =

(DESCRIPTION =

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

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = imsdb)

)

)

imsdb_s =

(DESCRIPTION =

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

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = imsdb)

)

)

1.7、[备库]检查备库oracle用户profile

尽量与主库oracle用户profile一致

vi /home/oracle/.bash_profile

export ORACLE_BASE=/u01/11.2.0/oracle

export ORACLE_HOME=/u01/11.2.0/oracle/product/1.2.0/dbhome_1

export ORACLE_SID=imsdb_s

1.8、[备库]创建11g数据库基本目录

mkdir -p /u01/11.2.0/oracle/admin/imsdb_s/adump

mkdir -p /u01/11.2.0/oracle/admin/imsdb_s/dpdump

mkdir -p /u01/11.2.0/oracle/admin/imsdb_s/pfile

mkdir -p/u01/11.2.0/oracle/oradata

mkdir -p/u01/11.2.0/oracle/oradata/fast_recovery_area

mkdir -p/u01/11.2.0/oracle/oradata/diag

chown -R oracle:oinstall /u01

chmod –R 775 /u01

1.9、[备库]拷贝主库口令文件并改名

mkdir -p /u01/11.2.0/oracle

chown -R oracle:oinstall /u01

chown -R oracle:oinstall /u01/11.2.0/oracle

chmod -R 775 /u01/11.2.0/oracle

chmod -R 775 /u01

mkdir -p/data/oradata/imsdb

chown -R oracle:oinstall /data

chmod -R 775 /data

-- 重建备份库密码文件 将主库文件orapwimsdb1 拷贝到备库两节点$ORACLE_HOME/dbs

./orapwdfile=/u01/11.2.0/oracle/product/11.2.0/dbhome_1/

password=ims123com entries=40 force=y;

测试远程登录 $ sqlplus sys/xxx@ imsdb as sysdba;

$ sqlplus sys/xxx@imsdb_s as sysdba;

1.10.[备库]spfile文件

alter system set db_unique_name=imsdb_s scope=spfile;

alter system set log_archive_config = 'DG_CONFIG=(imsdb,imsdb_s)' scope=spfile;

alter

DB_UNIQUE_NAME=imsdb_s' scope=spfile;

alter

alter system set log_archive_dest_state_1 = ENABLE;

alter system set log_archive_dest_state_2 = ENABLE;

alter system set log_archive_dest_state_3 = ENABLE;

alter system set fal_server=imsdb_s scope=spfile;

alter system set fal_client=imsdb scope=spfile;

alter

system

','/data/oradata/imsdb/' scope=spfile;

alter

system

set log_file_name_convert='+DATA1/imsdb/onlinelog/','/data/oradata/imsdb/'

scope=spfile;

set

db_file_name_convert='+DATA1/imsdb/datafile/','/data/oradata/imsdb/','+DATA1/imsdb/tempfile/system set log_archive_dest_2 = 'SERVICE=imsdbLGWR

system set

log_archive_dest_1='LOCATION=/u01/11.2.0/logVALID_FOR=(ALL_LOGFILES,ALL_ROLES)

SYNC AFFIRM

VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=imsdb' scope=spfile;

alter system set standby_file_management='AUTO' scope=both;

----------------测试库环境192.168.21.6------------------------------------------------------------------

alter system set log_archive_dest_1='LOCATION=/u01/log/

VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=orcl' sid='orcl2' scope=spfile;

alter system set log_archive_dest_1='LOCATION=/u01/log/

VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=orcl' sid='orcl1' scope=spfile;

alter system set log_archive_dest_1='LOCATION=/u01/log/

VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=orcl';

alter system set log_archive_dest_state_3="defer";

alter system set log_archive_dest_state_2="enable";

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

2、建立备库, RMAN duplicate 方式创建standby数据库

2.1、备库启动到nomount状态,创建init参数文件

$ vi $ORACLE_HOME/dbs/initimsdb_ DB_NAME=imsdb DB_UNIQUE_NAME=imsdb_s DB_BLOCK_SIZE=8192

export ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1

export ORACLE_SID=imsdb

cd /u01/app/oracle/product/11.2.0/dbhome_1/bin

启动数据库到nomount状态

$ sqlplus /nolog

SQL> conn / as sysdba

SQL> startup nomount pfile=’initimsdb_’;

2.2、RMAN同时连接主库192.168.1.201与备库192.168.1.42

备库一节点进入RMAN

./rman target sys/ims123com@imsdb1 auxiliary

sys/ims123com@imsdb_s

//设置rman并行通道

./rman target sys/oracle@orcl auxiliary sys/oracle@orcl_s

>configure device type disk parallelism 2

2.3、开始duplicate standby数据库

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

RMAN> quit

添加standby日志

ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 6 '/data/oradata/imsdb/'

SIZE 50M;

ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 7 '/data/oradata/imsdb/'

SIZE 50M;

ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 10

'/data/oradata/imsdb/' SIZE 50M;

ALTER DATABASE ADD STANDBY LOGFILE THREAD 2 GROUP 8 '/data/oradata/imsdb/'

SIZE 50M;

ALTER DATABASE ADD STANDBY LOGFILE THREAD 2 GROUP 9 '/data/oradata/imsdb/'

SIZE 50M;

ALTER DATABASE ADD STANDBY LOGFILE THREAD 2 GROUP 11

'/data/oradata/imsdb/' SIZE 50M;

2.4、查看备库状态

说明：duplicate数据库之后，备库只是处于mount状态，查看备库状态。

$ sqlplus / as sysdba

# 查看备库状态

SQL>select open_mode,database_role,db_unique_name from v$database;

OPEN_MODE DATABASE_ROLE DB_UNIQUE_NAME

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

MOUNTED PHYSICAL STANDBY imsdb_s

2.5、将备库切换至READ ONLY

WITH APPLY、实时应用模式

SQL> alter database open read only;

Database altered.

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE

USING CURRENT LOGFILE DISCONNECT;

Database altered.

检查当前备库上的日志应用情况

SQL>select name,thread#,sequence#,applied,archived,deleted from

v$archived_log

查看standby启动的DG进程

Sql> select process,client_process,sequence#,status from v$managed_standby;

3.问题处理：

报Error 1031 received logging on to the standby+ora-01031: insufficient

privileges

sqlplus sys/oracle@proddg as sysdba 可以连接！！！！！

重建备份库的密码文件即可：

orapwd file='/u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwimsdb'

password=ims123com ignorecase=y --修改参数文件