Oracle 11g RAC 配置單例項 DataGuard（通過 DUPLICATE 方式）

1 安裝備庫的資料庫軟體

1.1 安裝時使用的字符集
NLS_CHARACTERSET                    ZHS16GBK
NLS_NCHAR_CHARACTERSET              AL16UTF16

1.2 檢查備庫的系統引數是否滿足安裝要求
#檢視主機記憶體
grep MemTotal /proc/meminfo

#交換空間，如果實體記憶體在16G以上，推薦交換空間至少為16G
grep SwapTotal /proc/meminfo
free
 
#檢查機器的硬體名稱
uname -m
 
#檢查臨時表空間，至少1G
df -h /tmp
df -h
 
#檢查 linux 版本
cat /proc/version
lsb_release -id
 
#檢查核心
uname - r

1.3 禁用防火牆和SELINUX

#使用root使用者登入
service iptables status
service iptables stop
chkconfig iptables off

#然後輸入以下命令，禁用SELinux：
vi /etc/selinux/config
SELINUX=disabled

1.4 檢查軟體包
rpm -q --queryformat "%{NAME}-%{VERSION}-%{RELEASE} (%{ARCH})\n" binutils compat-libstdc elfutils-libelf elfutils-libelf-devel gcc gcc-c++ glibc glibc-common glibc-devel glibc-headers ksh libaio libaio-devel libgcc libstdc++ libstdc++-devel make sysstat unixODBC unixODBC-devel

1.5 建立組和使用者

/usr/sbin/groupadd oinstall
/usr/sbin/groupadd -g 502 dba
/usr/sbin/groupadd -g 503 oper
id oracle
/usr/sbin/useradd -u 502 -g oinstall -G dba,oper oracle
passwd oracle

1.6 設定環境變數，從rac的其中一個節點拷貝環境變數檔案到備庫上面

#登入主庫的節點1
cd $ORACLE_HOME/dbs
scp bash_profile oracle@beiku_ip:/home/oracle/.bash_profile

1.7 設定核心引數

vi /etc/sysctl.conf
#增加或者修改以下內容：
fs.aio-max-nr = 1048576
fs.file-max = 6815744
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
net.ipv4.ip_local_port_range = 9000 65500
net.core.rmem_default = 4194304
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 1048576
 
#使用以下命令驗證配置：
sysctl -p
 
#修改使用者oracle的shell限制：
vi /etc/security/limits.conf
#增加以下內容：
grid soft nproc 2047
grid hard nproc 16384
grid soft nofile 1024
grid hard nofile 65536
oracle soft nproc 2047
oracle hard nproc 16384
oracle soft nofile 1024
oracle hard nofile 65536
 
#修改登入引數，執行以下操作：
vi /etc/pam.d/login
#增加以下內容：
session required /lib64/security/pam_limits.so
session required pam_limits.so

#同樣執行以下操作：
vi /etc/profile
#增加以下內容：
if [ $USER = "oracle" ] ; then
  if [ $SHELL = "/bin/ksh" ]; then
    ulimit -p 16384
    ulimit -n 65536
  else
    ulimit -u 16384 -n 65536
  fi
  umask 022
fi

1.8 建立檔案目錄

mkdir -p /u01/app/oracle/product/11.2.0/db_1
chown -R oracle:oinstall /u01/app/oracle/product/11.2.0/db_1

#建立快速恢復區目錄
mkdir -p /ORADATA/fast_recovery
chown -R oracle:oinstall /ORADATA/fast_recovery

#建立資料庫檔案目錄
mkdir -p /ORADATA/data
chown -R oracle:oinstall /ORADATA/data

#建立資料庫日誌目錄
mkdir -p /ORADATA/redolog
chown -R oracle:oinstall /ORADATA/redolog

#建立歸檔日誌目錄
mkdir -p /ORADATA/arc
chown -R oracle:oinstall /ORADATA/arc

#建立audit目錄
mkdir -p /u01/app/oracle/admin/gtfdb/adump
chown -R oracle:oinstall /u01/app/oracle/admin/gtfdb/adump

1.9 安裝Oracle軟體，此步驟不建庫

#使用oracle使用者登入
#首先解壓安裝檔案：
unzip p10404530_112030_Linux-x86-64_1of7.zip
unzip p10404530_112030_Linux-x86-64_2of7.zip
#開啟圖形設定，開啟Xmanager4 的 Passive模式。
export DISPLAY=10.230.1.215:0.0
#此ip不是伺服器的ip，為自己的筆記本ip
#然後進入解壓後的database目錄，執行以下命令：
./runInstaller

2. DataGuard 主庫的相關配置

2.1 主庫開啟FORCE LOGGING模式
ALTER DATABASE FORCE LOGGING;
作用：不管什麼操作都會生成redo日誌
特點：1.在資料庫mount狀態和open狀態都可以啟動force logging模式
         2.臨時表空間和臨時回滾段動作不會生成redo日誌

建立檔案目錄
mkdir -p /u01/app/oracle/product/11.2.0/db_1
chown -R oracle:oinstall /u01/app/oracle/product/11.2.0/db_1
#建立快速恢復區目錄
mkdir -p /ORADATA/fast_recovery
chown -R oracle:oinstall /ORADATA/fast_recovery

#建立資料庫檔案目錄
mkdir -p /ORADATA/data
chown -R oracle:oinstall /ORADATA/data

#建立資料庫日誌目錄
mkdir -p /ORADATA/redolog
chown -R oracle:oinstall /ORADATA/redolog

#建立歸檔日誌目錄
mkdir -p /ORADATA/arc
chown -R oracle:oinstall /ORADATA/arc

chown -R oracle:oinstall /ORADATA

#建立audit目錄
mkdir -p /u01/app/oracle/admin/gtfdb/adump
chown -R oracle:oinstall /u01/app/oracle/admin/gtfdb/adump

2.2 配置主庫的引數檔案

在主庫節點一上執行
cd $ORACLE_HOME/dbs
sqlplus / as sysdba
create pfile from spfile;
exit

#備份引數檔案

cp initgtfdb2.ora /home/oracle/initgtfdb2.ora_bak

線上修改引數檔案

alter system set log_archive_config='DG_CONFIG=(gtfdb,gtfdbdg)' scope=both sid='*';
alter system set LOG_ARCHIVE_DEST_1='LOCATION=+FRA VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=gtfdb' scope=both sid='*';
alter system set LOG_ARCHIVE_DEST_2='SERVICE=gtfdbdg ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=gtfdbdg' scope=both sid='*';
alter system set LOG_ARCHIVE_DEST_STATE_1=enable  scope=both sid='*';
alter system set LOG_ARCHIVE_DEST_STATE_2=enable scope=both sid='*';
alter system set LOG_ARCHIVE_FORMAT = 'log%d_%t_%s_%r.arc' scope=both sid='*';
alter system set LOG_ARCHIVE_MAX_PROCESSES=30 scope=both sid='*';
alter system set fal_server=gtfdbdg scope=both sid='*';
alter system set STANDBY_FILE_MANAGEMENT=AUTO scope=both sid='*';

alter system set fal_server=gtfdb scope=both sid='*';
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE;

alter system set log_archive_dest_2='service=bhoms02 async valid_for=(online_logfiles,primary_role) db_unique_name=bhoms02';

alter system set LOG_ARCHIVE_DEST_2='SERVICE=gtfdbdg ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=gtfdbdg' scope=both sid='*';

alter system set log_archive_dest_2='service=gtfdbdg async valid_for=(online_logfiles,primary_role) db_unique_name=gtfdbdg';

2.3 建立standby logfile

在對應的ASM盤上建立日誌目錄

su - grid
asmcmd
cd DATA
cd GTFDB
mkdir standbylog

exit
sqlplus / as sysdba

ALTER DATABASE ADD STANDBY LOGFILE  thread 1 GROUP 9 '+DATA/gtfdb/standbylog/standby_group_01.log' SIZE 512M;
ALTER DATABASE ADD STANDBY LOGFILE  thread 1 GROUP 10 '+DATA/gtfdb/standbylog/standby_group_02.log' SIZE 512M;
ALTER DATABASE ADD STANDBY LOGFILE  thread 1 GROUP 11 '+DATA/gtfdb/standbylog/standby_group_03.log' SIZE 512M;
ALTER DATABASE ADD STANDBY LOGFILE  thread 1 GROUP 12 '+DATA/gtfdb/standbylog/standby_group_04.log' SIZE 512M;
ALTER DATABASE ADD STANDBY LOGFILE  thread 1 GROUP 13 '+DATA/gtfdb/standbylog/standby_group_05.log' SIZE 512M;

ALTER DATABASE ADD STANDBY LOGFILE  thread 2 GROUP 14 '+DATA/gtfdb/standbylog/standby_group_06.log' SIZE 512M;
ALTER DATABASE ADD STANDBY LOGFILE  thread 2 GROUP 15 '+DATA/gtfdb/standbylog/standby_group_07.log' SIZE 512M;
ALTER DATABASE ADD STANDBY LOGFILE  thread 2 GROUP 16 '+DATA/gtfdb/standbylog/standby_group_08.log' SIZE 512M;
ALTER DATABASE ADD STANDBY LOGFILE  thread 2 GROUP 17 '+DATA/gtfdb/standbylog/standby_group_09.log' SIZE 512M;
ALTER DATABASE ADD STANDBY LOGFILE  thread 2 GROUP 18 '+DATA/gtfdb/standbylog/standby_group_10.log' SIZE 512M;

作用：主庫切換到備庫角色時接收主庫redo日誌
檢查：
SELECT GROUP#,THREAD#,SEQUENCE#,ARCHIVED,STATUS FROM GV$STANDBY_LOG;

2.4 在主庫各節點分別配置TNS

在主庫的/etc/hosts裡面，新增備庫ip的解析

cd $ORACLE_HOME/network/admin
vi tnsnames.ora

#modified by jjn for the data guard on 2015/10/28
GTFDBDG =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = GTFDBDG)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = gtfdbdg)
    )
  )
GTFDB1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = GTFDB1-vip )(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = gtfdb)
      (SID=gtfdb1)
    )
  )
GTFDB2 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = GTFDB2-vip )(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = gtfdb)
      (SID=gtfdb2)
    )
  )

2.5 拷貝主庫密碼檔案到備庫上
cd $ORACLE_HOME/dbs
scp orapwgtfdb1 oracle@10.100.20.71:/u01/app/oracle/product/11.2.0/db_1/dbs/
#如果備庫密碼檔案有問題，則把主庫兩節點的密碼檔案均拷貝到備庫，並把其中一個密碼檔案複製成備庫的SID格式。
#在備庫上更改密碼檔名稱
mv orapwgtfdb1 orapwgtfdb

2.6 拷貝主庫的引數檔案到備庫
cd $ORACLE_HOME/dbs
scp initgtfdb2.ora oracle@10.100.20.71:/u01/app/oracle/product/11.2.0/db_1/dbs/

3. 配置Dataguard備庫的相關配置

3.1 配置備庫的引數檔案

vi initgtfdb2.ora
gtfdb2.__db_cache_size=10670309376
gtfdb1.__db_cache_size=10670309376
這樣的引數改為一個，如
*.__db_cache_size=10670309376
去掉下面引數
gtfdb1.instance_number=1
gtfdb2.instance_number=2
gtfdb2.thread=2
gtfdb1.thread=1
*.cluster_database=true
*.remote_listener='rac-scan:1521'
# 修改.db_recovery_file_dest='+FRA'為存在的檔案目錄

增加下面引數
DB_UNIQUE_NAME=gtfdbdg
*.db_create_file_dest='/ORADATA/data'
*.db_create_online_log_dest_1='/ORADATA/redolog'
*.db_create_online_log_dest_2='/ORADATA/redolog'
*.control_files='/u01/app/oracle/product/11.2.0/db_1/dbs/controlfile01.ctl', '/u01/app/oracle/product/11.2.0/db_1/dbs/controlfile02.ctl'
LOG_ARCHIVE_DEST_1=
 'LOCATION=/ORADATA/arc
  VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
  DB_UNIQUE_NAME=gtfdbdg'
LOG_ARCHIVE_DEST_2=
 'SERVICE=gtfdb ASYNC
  VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
  DB_UNIQUE_NAME=gtfdb'
FAL_SERVER=gftdb
LOG_ARCHIVE_CONFIG='DG_CONFIG=(gftdb,gtfdbdg)'
*.LOG_ARCHIVE_DEST_STATE_1=ENABLE
*.LOG_ARCHIVE_DEST_STATE_2=ENABLE
*.LOG_ARCHIVE_FORMAT = log%d_%t_%s_%r.arc
*.LOG_ARCHIVE_MAX_PROCESSES=30
STANDBY_FILE_MANAGEMENT=AUTO

3.2 配置備庫的監聽

在備庫的/etc/hosts裡面，新增主庫ip的解析
su - root
vi /etc/hosts
GTFDB    ip
GTFDB1   ip
GTFDB2   ip
GTFDBDG    ip

cd $ORACLE_HOME/network/admin
vi listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = gtfdbdg)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
      (SID_NAME = gtfdb)
    )
  )

LISTENER =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = GTFDBDG)(PORT = 1521))
  )
ADR_BASE_LISTENER = /u01/app/oracle

vi tnsnames.ora
GTFDBDG =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = GTFDBDG)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = gtfdbdg)
    )
  )
GTFDB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = GTFDB )(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = gtfdb)
    )
  )
GTFDB1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = GTFDB1 )(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = gtfdb)
      (SID=gtfdb1)
    )
  )
GTFDB2 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = GTFDB2 )(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = gtfdb)
      (SID=gtfdb2)
    )
  )

lsnrctl stop
lsnrctl start

3.3 通過duplicate命令進行復制

cd /home/oracle/script
nohup sh dup_db.sh &
cd $ORACLE_HOME/dbs
sqlplus / as sysdba

create spfile from pfile='initgtfdb2.ora';

startup nomount

exit

rman target sys/Sys#2013@gtfdb1 auxiliary sys/Sys#2013@gtfdbdg
run {
allocate channel ch1 type disk;
allocate auxiliary channel ch2 type disk;
duplicate target database for standby nofilenamecheck from active database;
release channel ch1;
release channel ch2;
}

#查收日誌狀態
select group#,thread#,bytes,archived,status from v$standby_log;

3.4 備庫啟動日誌應用
sqlplus / as sysdba
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;

--檢查歸檔日誌的同步情況
SELECT SEQUENCE#,THREAD#,APPLIED FROM GV$ARCHIVED_LOG ORDER BY SEQUENCE#;

3.5 啟動資料庫日誌實時應用

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
 
ALTER DATABASE OPEN;

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;

3.6 檢查備庫資料庫狀態

select database_role,switchover_status,open_mode from v$database;

select * from v$dataguard_stats;

select * from v$managed_standby;

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26506993/viewspace-2107327/，如需轉載，請註明出處，否則將追究法律責任。