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.ora spfileimsdb_bak.ora
cp spfileimsdb.ora /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/initimsdb.txt’ from spfile=’+DATA/imsdb/spfileimsdb.ora’;
cp spfileimsdb.ora /data/11.2.0/grid/spfileimsdb.ora
cp /data/11.2.0/grid/spfileimsdb.ora+DATA/imsdb
备份控制文件：
cp 备份，手工复制, alter database backup controlfile to trace;
备份数据库：
$ rman target /
RMAN> backup full database format '/backup/backup_%T_%s_%p.bak';
Startup nomount pfile=’/u01/11.2.0/oracle/product/11.2.0/dbs/initimsdb2.ora’
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 system set log_archive_dest_1='LOCATION=/oraback/backup/log/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=imsdb' sid='imsdb2' scope=spfile;
alter system set log_archive_dest_1='LOCATION=/oraback/backup/log/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=imsdb' sid='imsdb1' scope=spfile;
alter system set log_archive_dest_2 = 'SERVICE=imsdb_s reopen=60 lgwr async 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/' sco pe=spfile ;
alter system set standby_file_management='AUTO' scope=both;
然后重启数据库：
SQL> shutdown immediate
SQL> startup;
1.5、[备库]上修改配置listener.ora文件,添加静态监听
备库
grid@mylxd0005.intranet.local:/grid/product/11.2.0.4/network/admin > vi listener.ora
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = imsdb)
(ORACLE_HOME = /app/oracle/11.2.0/db_1)
(SID_NAME = imsdb)
)
)
)
1.6、[主/备库]上修改配置tnsname.ora文件
1.6.1、主库配置：
--$ORACLE_HOME/network/admin 修改ORACLE_HOME目录下的tnasnames.ora
/u01/11.2.0/oracle/product/11.2.0/network/admin/tnsnames.ora
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/PWDimsdb.ORA
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 system set log_archive_dest_1='LOCATION=/u01/11.2.0/logVALID_FOR=(ALL_LOGFILES,ALL_ROLES)
DB_UNIQUE_NAME=imsdb_s' scope=spfile;
alter system set log_archive_dest_2 = 'SERVICE=imsdb LGWR SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=imsdb' scope=spfile;
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 set db_file_name_convert='+DATA1/imsdb/datafile/','/data/oradata/imsdb/','+DATA1/imsdb/tempfile/ ','/data/oradata/imsdb/' scope=spfile;
alter
system set log_file_name_convert='+DATA1/imsdb/onlinelog/','/data/oradata/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_s1.ora
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_s1.ora’;
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/standby1.dbf' SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 7 '/data/oradata/imsdb/standby2.dbf' SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 10 '/data/oradata/imsdb/standby5.dbf' SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 2 GROUP 8 '/data/oradata/imsdb/standby3.dbf' SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 2 GROUP 9 '/data/oradata/imsdb/standby4.dbf' SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 2 GROUP 11 '/data/oradata/imsdb/standby6.dbf' SIZE 50M;
2.4、查看备库状态
说明：duplicate数据库之后，备库只是处于mount状态，查看备库状态。

$ sqlplus / as sysdba
# 查看备库状态
SQL>select open_mode,database_role,db_unique_name from v$databas e;
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 --修改参数文件。