Oracle RAC+DG搭建

(1)  基本環境介紹
(2)  主庫：開啟歸檔模式
(3)  主庫：開啟強制寫日誌功能並關閉閃回
(4)  主庫：配置靜態監聽,配置tnsnames 檔案
(5)  主庫：增加standby logfile 檔案
(6)  修改主、備庫引數檔案
(7)  主庫: 拷貝主庫引數檔案、密碼檔案等到備庫並改名
(8)  備庫：據引數檔案建立相應的目錄
(9)  主庫: 通過rman duplicate方式搭建dg
(10) 驗證主備庫資料同步
(11) DG常規檢查
(12) 主從切換測試
(13) 實施過程中遇到的問題

===================
一：基本環境介紹
===================
主庫:oracle 11.2.0.4.0 RAC(兩節點rac1,rac2),ASM儲存
備庫:oracle 11.2.0.4.0 單例項，檔案系統儲存

====================
二：RAC啟動歸檔模式
====================
建立歸檔目錄
ASMCMD> mkdir arch
ASMCMD> cd arch
ASMCMD> pwd 
+FRA/arch

指定歸檔目錄
SQL> alter system set log_archive_dest_1='location=+FRA/arch' scope=spfile sid='*';

停止資料庫例項
[root@rac1 bin]# ./srvctl status database -d ncdb
Instance ncdb1 is running on node rac1
Instance ncdb2 is running on node rac2
[root@rac1 bin]# ./srvctl stop database -d ncdb -o immediate

啟動其中一個節點例項到mount狀態
[root@rac1 bin]# ./srvctl start instance -d ncdb -i ncdb1 -o mount

啟動歸檔
SQL> alter database archivelog;

啟動資料庫
SQL> alter database open;
SQL> archive log list;
Database log mode       Archive Mode
Automatic archival       Enabled
Archive destination       +FRA/arch
Oldest online log sequence     16
Next log sequence to archive   17
Current log sequence       1

啟動節點二例項
[root@rac1 bin]# ./srvctl start instance -d ncdb -i ncdb2
SQL> archive log list;
Database log mode       Archive Mode
Automatic archival       Enabled
Archive destination       +FRA/arch
Oldest online log sequence     4
Next log sequence to archive   5
Current log sequence       5

=======================
三：開啟強制寫日誌功能並關閉閃回
=======================
SQL> select force_logging from v$database;
SQL> alter database force logging;
SQL> select flashback_on from v$database;
SQL> alter database flashback off;

==========================
四：主庫配置靜態監聽,配置tnsnames 檔案
==========================
增加靜態監聽
rac1:
[oracle@rac1 ~]$ lsnrctl status
LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 16-MAR-2018 21:28:52
Copyright (c) 1991, 2013, Oracle.  All rights reserved.
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date                15-MAR-2018 08:15:38
Uptime                    1 days 13 hr. 13 min. 14 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/11.2.0/grid/network/admin/listener.ora
Listener Log File         /u01/app/grid/diag/tnslsnr/rac1/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.100.61)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.100.63)(PORT=1521)))
Services Summary...
Service "+ASM" has 1 instance(s).
  Instance "+ASM1", status READY, has 1 handler(s) for this service...
Service "ncdb" has 1 instance(s).
  Instance "ncdb1", status READY, has 1 handler(s) for this service...
Service "ncdbXDB" has 1 instance(s).
  Instance "ncdb1", status READY, has 1 handler(s) for this service...
The command completed successfully

[root@rac1 ~]# su - grid
[grid@rac1 ~]$ cd /u01/app/11.2.0/grid/network/admin/
[grid@rac1 admin]$ cp listener.ora listener.ora.0316bak
[grid@rac1 admin]$ vi listener.ora
        # line added by Agent
LISTENER_SCAN1=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN1))))                # line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN1=ON                # line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON              # line added by Agent

[grid@rac1 admin]$ vi listener.ora
LISTENER=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER))))            # line added by Agent
LISTENER_SCAN1=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN1))))                # line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN1=ON                # line added by Agent

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = ncdb)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
      (SID_NAME = ncdb1)
    )
  )

LISTENER =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = IPC)(KEY = LISTENER))
  )

ADR_BASE_LISTENER = /u01/app/grid

ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON              # line added by Agent

LISTENER_SCAN1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = IPC)(KEY = LISTENER_SCAN1))
  )

ADR_BASE_LISTENER_SCAN1 = /u01/app/grid

[grid@rac1 admin]$ lsnrctl stop
[grid@rac1 admin]$ lsnrctl start
[grid@rac1 admin]$ lsnrctl status
LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 16-MAR-2018 21:34:56
Copyright (c) 1991, 2013, Oracle.  All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date                16-MAR-2018 21:34:45
Uptime                    0 days 0 hr. 0 min. 10 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/11.2.0/grid/network/admin/listener.ora
Listener Log File         /u01/app/grid/diag/tnslsnr/rac1/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.100.61)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.100.63)(PORT=1521)))
Services Summary...
Service "+ASM" has 1 instance(s).
  Instance "+ASM1", status READY, has 1 handler(s) for this service...
Service "ncdb" has 2 instance(s).
  Instance "ncdb1", status UNKNOWN, has 1 handler(s) for this service...
  Instance "ncdb1", status READY, has 1 handler(s) for this service...
Service "ncdbXDB" has 1 instance(s).
  Instance "ncdb1", status READY, has 1 handler(s) for this service...
The command completed successfully

rac2:
[root@rac2 ~]# su - grid
[grid@rac2 ~]$ export LANG=C
[grid@rac2 ~]$ lsnrctl status
LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 16-MAR-2018 21:37:31
Copyright (c) 1991, 2013, Oracle.  All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date                15-MAR-2018 08:15:38
Uptime                    1 days 13 hr. 21 min. 53 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/11.2.0/grid/network/admin/listener.ora
Listener Log File         /u01/app/grid/diag/tnslsnr/rac2/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.100.62)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.100.64)(PORT=1521)))
Services Summary...
Service "+ASM" has 1 instance(s).
  Instance "+ASM2", status READY, has 1 handler(s) for this service...
Service "ncdb" has 1 instance(s).
  Instance "ncdb2", status READY, has 1 handler(s) for this service...
Service "ncdbXDB" has 1 instance(s).
  Instance "ncdb2", status READY, has 1 handler(s) for this service...
The command completed successfully

[grid@rac2 ~]$ cd /u01/app/11.2.0/grid/network/admin/
[grid@rac2 admin]$ cp listener.ora listener.ora.0316bak
[grid@rac2 admin]$ vi listener.ora
LISTENER=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER))))            # line added by Agent
LISTENER_SCAN1=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN1))))                # line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN1=ON                # line added by Agent

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = ncdb)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
      (SID_NAME = ncdb2)
    )
  )

LISTENER =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = IPC)(KEY = LISTENER))
  )

ADR_BASE_LISTENER = /u01/app/grid

ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON              # line added by Agent

LISTENER_SCAN1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = IPC)(KEY = LISTENER_SCAN1))
  )

ADR_BASE_LISTENER_SCAN1 = /u01/app/grid

[grid@rac2 admin]$ lsnrctl status
LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 16-MAR-2018 21:40:09
Copyright (c) 1991, 2013, Oracle.  All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date                16-MAR-2018 21:39:12
Uptime                    0 days 0 hr. 0 min. 57 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/11.2.0/grid/network/admin/listener.ora
Listener Log File         /u01/app/grid/diag/tnslsnr/rac2/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.100.62)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.100.64)(PORT=1521)))
Services Summary...
Service "+ASM" has 1 instance(s).
  Instance "+ASM2", status READY, has 1 handler(s) for this service...
Service "ncdb" has 2 instance(s).
  Instance "ncdb2", status UNKNOWN, has 1 handler(s) for this service...
  Instance "ncdb2", status READY, has 1 handler(s) for this service...
Service "ncdbXDB" has 1 instance(s).
  Instance "ncdb2", status READY, has 1 handler(s) for this service...
The command completed successfully

主庫：奇數機配置tnsnames 檔案：
NCDB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = rac1-vip)(PORT = 1521))
     (ADDRESS = (PROTOCOL = TCP)(HOST = rac2-vip)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ncdb)
    )
  )

NCDB1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = rac1)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ncdb)
 (INSTANCE_NAME = ncdb1)
     )
   )

NCDB2 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = rac2)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ncdb)
 (INSTANCE_NAME = ncdb2)
     )
   )
 
STANDBY =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = standby)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = STANDBY)
     )
   )

===================
五：主庫增加standby logfile
===================
（主庫原來有的個數n+1）*執行緒數
在最大可用和最大保護模式，因為是採用LGWR SYNC進行redo的傳送，一定要用standby logfile，但是建議在最大效能模式也新增standby logfile，切換時可以恢復更多的資料；
主庫
SQL> set lines 120
SQL> col member for a50
SQL> select a.thread#,a.group#,a.bytes/1024/1024,b.member from v$log a,v$logfile b where a.group#=b.group#;
   THREAD#     GROUP# A.BYTES/1024/1024 MEMBER
---------- ---------- ----------------- --------------------------------------------------
1    2     50 +DATA/ncdb/onlinelog/group_2.262.970816335
1    2     50 +FRA/ncdb/onlinelog/group_2.258.970816335
1    1     50 +DATA/ncdb/onlinelog/group_1.261.970816335
1    1     50 +FRA/ncdb/onlinelog/group_1.257.970816335
2    3     50 +DATA/ncdb/onlinelog/group_3.265.970816445
2    3     50 +FRA/ncdb/onlinelog/group_3.259.970816445
2    4     50 +DATA/ncdb/onlinelog/group_4.266.970816445
2    4     50 +FRA/ncdb/onlinelog/group_4.260.970816445
8 rows selected.

--主庫新增 standby redo log： 
alter database add standby logfile thread 1 group 11 '+DATA/ncdb/onlinelog/standby_redo11.log' size 50m;
alter database add standby logfile thread 1 group 12 '+DATA/ncdb/onlinelog/standby_redo12.log' size 50m;
alter database add standby logfile thread 1 group 13 '+DATA/ncdb/onlinelog/standby_redo13.log' size 50m;
alter database add standby logfile thread 1 group 14 '+DATA/ncdb/onlinelog/standby_redo14.log' size 50m;
alter database add standby logfile thread 1 group 15 '+DATA/ncdb/onlinelog/standby_redo15.log' size 50m;
alter database add standby logfile thread 2 group 16 '+DATA/ncdb/onlinelog/standby_redo16.log' size 50m;
alter database add standby logfile thread 2 group 17 '+DATA/ncdb/onlinelog/standby_redo17.log' size 50m;
alter database add standby logfile thread 2 group 18 '+DATA/ncdb/onlinelog/standby_redo18.log' size 50m;
alter database add standby logfile thread 2 group 19 '+DATA/ncdb/onlinelog/standby_redo19.log' size 50m;
alter database add standby logfile thread 2 group 20 '+DATA/ncdb/onlinelog/standby_redo20.log' size 50m;

SQL> select group#,type,member from v$logfile order by 1,2;
    GROUP# TYPE    MEMBER
---------- ------- --------------------------------------------------
1 ONLINE  +DATA/ncdb/onlinelog/group_1.261.970816335
1 ONLINE  +FRA/ncdb/onlinelog/group_1.257.970816335
2 ONLINE  +DATA/ncdb/onlinelog/group_2.262.970816335
2 ONLINE  +FRA/ncdb/onlinelog/group_2.258.970816335
3 ONLINE  +DATA/ncdb/onlinelog/group_3.265.970816445
3 ONLINE  +FRA/ncdb/onlinelog/group_3.259.970816445
4 ONLINE  +DATA/ncdb/onlinelog/group_4.266.970816445
4 ONLINE  +FRA/ncdb/onlinelog/group_4.260.970816445
11 STANDBY +DATA/ncdb/onlinelog/standby_redo11.log
12 STANDBY +DATA/ncdb/onlinelog/standby_redo12.log
13 STANDBY +DATA/ncdb/onlinelog/standby_redo13.log
14 STANDBY +DATA/ncdb/onlinelog/standby_redo14.log
15 STANDBY +DATA/ncdb/onlinelog/standby_redo15.log
16 STANDBY +DATA/ncdb/onlinelog/standby_redo16.log
17 STANDBY +DATA/ncdb/onlinelog/standby_redo17.log
18 STANDBY +DATA/ncdb/onlinelog/standby_redo18.log
19 STANDBY +DATA/ncdb/onlinelog/standby_redo19.log
20 STANDBY +DATA/ncdb/onlinelog/standby_redo20.log

18 rows selected.

=================
六：修改主、備庫引數檔案
=================
主庫： 
[oracle@rac1 dbs]$ cat initncdb1.ora 
SPFILE='+DATA/ncdb/spfilencdb.ora'

[oracle@rac1 dbs]$ cp initncdb1.ora initncdb1.ora.bak

SQL> show parameter spfile
NAME     TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile     string +DATA/ncdb/spfilencdb.ora

[oracle@rac2 dbs]$ cat initncdb2.ora 
SPFILE='+DATA/ncdb/spfilencdb.ora'

[oracle@rac2 dbs]$ cp initncdb2.ora initncdb2.ora.bak
SQL> show parameter spfile
NAME     TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile     string +DATA/ncdb/spfilencdb.ora

ASMCMD> cp spfilencdb.ora /home/grid
copying +data/ncdb/spfilencdb.ora -> /home/grid/spfilencdb.ora

SQL> create pfile='/u01/app/oracle/product/11.2.0/db_1/dbs/init0317.ora' from spfile;

---主庫引數如下
---1
alter system set db_unique_name='ncdb'  scope=spfile sid='*'; 
---2
alter system set log_archive_config='dg_config=(ncdb,standby)'   scope=spfile sid='*';
---3
alter system set log_archive_dest_1='location=+FRA/arch valid_for=(all_logfiles,all_roles) db_unique_name=ncdb'  scope=spfile sid='*';
---4
alter system set log_archive_dest_2='service=standby valid_for=(online_logfiles,primary_role) db_unique_name=standby'   scope=spfile sid='*';
---5
alter system set log_archive_dest_state_1=enable scope=spfile sid='*';
---6
alter system set log_archive_dest_state_2=enable scope=spfile sid='*';
---7
alter system set standby_file_management='auto' scope=spfile sid='*';
---8
alter system set fal_server='standby' scope=spfile sid='*';
---9
alter system set db_file_name_convert='/home/u01/app/oracle/oradata/standby/data','+DATA/ncdb/datafile','/home/u01/app/oracle/oradata/standby/tempfile','+DATA/ncdb/tempfile' scope=spfile sid='*';
---10
alter system set log_file_name_convert='/home/u01/app/oracle/oradata/standby/redo','+DATA/ncdb/onlinelog','/home/u01/app/oracle/oradata/standby/redo','+FRA/ncdb/onlinelog' scope=spfile sid='*';
---11
alter system set log_archive_format='%t_%s_%r.arch' scope=spfile sid='*';
---12
alter system set REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE scope=spfile sid='*';

---重啟資料庫，引數生效
[root@rac1 ~]# export LANG=C
[root@rac1 ~]# cd /u01/app/11.2.0/grid/bin/
[root@rac1 bin]# ./srvctl status database -d ncdb
Instance ncdb1 is running on node rac1
Instance ncdb2 is running on node rac2
[root@rac1 bin]# ./srvctl stop database -d ncdb -o immediate
[root@rac1 bin]# ./srvctl start database -d ncdb -o open
[root@rac1 bin]# ./srvctl status database -d ncdb
Instance ncdb1 is running on node rac1
Instance ncdb2 is running on node rac2

SQL> create pfile='/u01/app/oracle/product/11.2.0/db_1/dbs/init0317bak.ora' from spfile;

[root@rac1 oracle]# ls dg/
init0317bak.ora  listener.ora  orapwncdb1  tnsnames.ora
[root@rac1 oracle]# tar -zcvf dg.tar.gz dg/
[root@rac1 oracle]# scp dg.tar.gz standby:/home/oracle

---備庫引數：
[oracle@standby dbs]$ vi initstandby.ora
standby.__db_cache_size=18017561088
standby.__java_pool_size=939524096
standby.__large_pool_size=536870912
standby.__pga_aggregate_target=13555990528
standby.__sga_target=20533753856
standby.__shared_io_pool_size=0
standby.__shared_pool_size=2771362304
standby.__streams_pool_size=0
*.audit_file_dest='/home/u01/app/oracle/admin/standby/adump'
*.audit_trail='db'
*.compatible='11.2.0.4.0'
*.control_files='/home/u01/app/oracle/oradata/standby/data/current.260.970816331','/home/u01/app/oracle/oradata/standby/data/current.256.970816331'
*.db_block_size=8192
*.db_create_file_dest='/home/u01/app/oracle/oradata/standby/data'
*.db_domain=''
*.db_recovery_file_dest='/home/u01/app/oracle/arch'
*.db_recovery_file_dest_size=52428800000
*.deferred_segment_creation=FALSE
*.diagnostic_dest='/home/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=ncdbXDB)'
*.log_archive_dest_1='location=/home/u01/app/oracle/arch'
*.open_cursors=2000
*.pga_aggregate_target=13503561728
*.processes=1000
*.remote_login_passwordfile='exclusive'
*.sessions=1105
*.sga_target=20510685184
*.undo_tablespace='UNDOTBS1'

------備庫新加dg引數----------------
*.db_name='ncdb'
*.db_unique_name='standby'
*.fal_server='standby'
*.log_archive_config='dg_config=(ncdb,standby)'
*.log_archive_dest_1='location=/home/u01/app/oracle/arch valid_for=(all_logfiles,all_roles) db_unique_name=standby'
*.log_archive_dest_2='service=ncdb valid_for=(online_logfiles,primary_role) db_unique_name=ncdb'
*.log_archive_dest_state_1='ENABLE'
*.log_archive_dest_state_2='ENABLE'
*.log_archive_format='%t_%s_%r.arch'
*.log_file_name_convert='+DATA/ncdb/onlinelog','/home/u01/app/oracle/oradata/standby/redo','+FRA/ncdb/onlinelog','/home/u01/app/oracle/oradata/standby/redo'
*.db_file_name_convert='+DATA/ncdb/datafile','/home/u01/app/oracle/oradata/standby/data','+DATA/ncdb/tempfile','/home/u01/app/oracle/oradata/standby/tempfile'
*.remote_login_passwordfile='EXCLUSIVE'
*.standby_file_management='auto'
*.fal_server='ncdb'
*.service_names='standby'

=============================
七：主庫:拷貝主庫引數檔案、密碼檔案等到備庫
=============================
建立備庫口令檔案 
[oracle@rac1 dbs]$ mv orapwncdb1 orapwncdb1.bak
[oracle@rac1 dbs]$ orapwd file=orapwncdb1 password=oracle
節點一建立的密碼檔案，傳到節點二和備庫並改名

========================
八：備庫據引數檔案建立相應的目錄
========================
mkdir /home/u01/app/oracle/oradata/standby/data -p
mkdir /home/u01/app/oracle/oradata/standby/redo -p
mkdir /home/u01/app/oracle/oradata/standby/tempfile -p
mkdir /home/u01/app/oracle/admin/standby/adump -p
mkdir /home/u01/app/oracle/arch -p

==========================
九：主庫通過rman duplicate方式搭建dg
===========================
備庫啟動nomount
SQL> create spfile from pfile;   
SQL> startup nomount
ORACLE instance started.
Total System Global Area 2.0444E+10 bytes
Fixed Size    2262088 bytes
Variable Size 3221228472 bytes
Database Buffers 1.7180E+10 bytes
Redo Buffers   40267776 bytes

備庫檢查監聽狀態
SQL> ho lsnrctl status
......
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=standby)(PORT=1521)))
Services Summary...
Service "standby" has 2 instance(s).
  Instance "standby", status UNKNOWN, has 1 handler(s) for this service...
  Instance "standby", status BLOCKED, has 1 handler(s) for this service...
The command completed successfully

主庫其中一個節點通過rman搭建備庫
[oracle@rac1 ~]$ rman target / auxiliary sys/oracle@standby
Recovery Manager: Release 11.2.0.4.0 - Production on Sat Mar 17 13:09:38 2018
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
connected to target database: NCDB (DBID=3423957963)
connected to auxiliary database: NCDB (not mounted)

RMAN> duplicate target database for standby from active database dorecover;
---dorecover：會將備份期間產生的歸檔日誌應用到standby database

啟動備庫
SQL> select NAME,open_mode from v$database; 
NAME  OPEN_MODE
--------- --------------------
NCDB  MOUNTED

SQL> alter database open;
Database altered.

啟動 MRP 程式 
SQL> alter database recover managed standby database disconnect from session;
Database altered.

SQL> select NAME,open_mode from v$database;
NAME  OPEN_MODE
--------- --------------------
NCDB  READ ONLY WITH APPLY

================
十：驗證主備庫資料同步
================
rac1:
SQL>  create table test0317_rac1 as select * from dba_users;
SQL> alter system switch logfile;

rac2:
SQL> create table test0317_rac2 as select * from dba_users;
SQL> alter system switch logfile;

---備庫查詢
---必須執行日誌切換，備庫才能查詢到資料
SQL> select count(*) from test0317_rac1;
 COUNT(*)
----------
30

SQL> select count(*) from test0317_rac2; 
COUNT(*)
----------
30

備庫：開啟實時同步資料
SQL> recover managed standby database cancel;
Media recovery complete.
SQL> recover managed standby database using current logfile disconnect from session;
Media recovery complete.

rac1:
SQL>  create table test00317_rac1 as select * from dba_users;
rac2:
SQL> create table test00317_rac2 as select * from dba_users;

---備庫查詢
---實時同步，不需要切換日誌
SQL> select count(*) from test00317_rac1;
COUNT(*)
----------
30

SQL> select count(*) from test00317_rac2;
 COUNT(*)
----------
30

Sat Mar 17 14:27:51 2018
RFS[2]: Selected log 16 for thread 2 sequence 24 dbid -871009333 branch 970816334
Sat Mar 17 14:27:51 2018
Archived Log entry 13 added for thread 2 sequence 23 ID 0xcc1596c8 dest 1:
Sat Mar 17 14:27:54 2018
Media Recovery Log /home/u01/app/oracle/arch/2_23_970816334.arch
Media Recovery Waiting for thread 1 sequence 43 (in transit)  

==============
十一：DG常規檢查
==============
主庫： 
rac1,rac2：
SQL> select name,open_mode from v$database;
NAME  OPEN_MODE
--------- --------------------
NCDB  READ WRITE
 
rac1:
SQL> select database_role, protection_mode, protection_level from v$database;
DATABASE_ROLE PROTECTION_MODE      PROTECTION_LEVEL
---------------- -------------------- --------------------
PRIMARY MAXIMUM PERFORMANCE  MAXIMUM PERFORMANCE

rac2:
SQL> select database_role, protection_mode, protection_level from v$database;
DATABASE_ROLE PROTECTION_MODE      PROTECTION_LEVEL
---------------- -------------------- --------------------
PRIMARY MAXIMUM PERFORMANCE  MAXIMUM PERFORMANCE

主庫上檢視設定的歸檔日誌路徑是否可用
SQL> col dest_name for a30
SQL> col error for a30
SQL> select dest_name,status,error from v$archive_dest;
DEST_NAME       STATUS ERROR
------------------------------ --------- ------------------------------
LOG_ARCHIVE_DEST_1       VALID
LOG_ARCHIVE_DEST_2       VALID

主庫上查詢歸檔日誌的應用情況
SQL> set pagesize 50;
SQL> col name for a50
SQL> select name,SEQUENCE#,APPLIED from v$archived_log order by 1,2;
......

主庫上檢視DG的狀態資訊
SQL>  select process,status from v$managed_standby;
PROCESS   STATUS
--------- ------------
ARCH  CLOSING
ARCH  CLOSING
ARCH  CONNECTED
ARCH  CLOSING
LNS      WRITING

rac1:
SQL> col name for a15
SQL> select name,database_role,switchover_status from v$database;
NAME DATABASE_ROLE SWITCHOVER_STATUS
--------------- ---------------- --------------------
NCDB PRIMARY SESSIONS ACTIVE

rac2:
SQL> col name for a15
SQL> select name,database_role,switchover_status from v$database;
NAME DATABASE_ROLE SWITCHOVER_STATUS
--------------- ---------------- --------------------
NCDB PRIMARY TO STANDBY

備庫環境檢查
備庫例項的啟動狀態檢查
SQL> select instance_name,status from v$instance;
INSTANCE_NAME STATUS
---------------- ------------
standby OPEN

SQL> select name,open_mode from v$database;
NAME  OPEN_MODE
--------- --------------------
NCDB  READ ONLY WITH APPLY

備庫DG環境的保護模式檢查
SQL> select database_role, protection_mode, protection_level from v$database;
DATABASE_ROLE PROTECTION_MODE      PROTECTION_LEVEL
---------------- -------------------- --------------------
PHYSICAL STANDBY MAXIMUM PERFORMANCE  MAXIMUM PERFORMANCE

備庫上檢視同步過來的歸檔日誌的應用情況
SQL> set pagesize 50
SQL> col name for a50
SQL> select name,SEQUENCE#,APPLIED from v$archived_log order by sequence#;
NAME    SEQUENCE# APPLIED
-------------------------------------------------- ---------- ---------
/home/u01/app/oracle/arch/2_17_970816334.arch   17 YES
/home/u01/app/oracle/arch/2_18_970816334.arch   18 YES
/home/u01/app/oracle/arch/2_19_970816334.arch   19 YES
/home/u01/app/oracle/arch/2_20_970816334.arch   20 YES
/home/u01/app/oracle/arch/2_21_970816334.arch   21 YES
/home/u01/app/oracle/arch/2_22_970816334.arch   22 YES
/home/u01/app/oracle/arch/2_23_970816334.arch   23 YES
/home/u01/app/oracle/arch/2_24_970816334.arch   24 YES
/home/u01/app/oracle/arch/2_25_970816334.arch   25 YES
/home/u01/app/oracle/arch/1_37_970816334.arch   37 YES
/home/u01/app/oracle/arch/1_38_970816334.arch   38 YES
/home/u01/app/oracle/arch/1_39_970816334.arch   39 YES
/home/u01/app/oracle/arch/1_40_970816334.arch   40 YES
/home/u01/app/oracle/arch/1_41_970816334.arch   41 YES
/home/u01/app/oracle/arch/1_42_970816334.arch   42 YES
/home/u01/app/oracle/arch/1_43_970816334.arch   43 NO

16 rows selected.

備庫上檢視歸檔日誌gap
SQL> select * from v$archive_gap;
no rows selected

備庫上檢視DG環境特有程式的狀態
SQL> select process,status from v$managed_standby;
PROCESS   STATUS
--------- ------------
ARCH  CLOSING
ARCH  CLOSING
ARCH  CONNECTED
ARCH  CLOSING
RFS  IDLE
RFS  IDLE
RFS  IDLE
RFS  IDLE
RFS  IDLE
RFS  IDLE
MRP0  APPLYING_LOG

11 rows selected.

備庫上檢視DG環境的狀態資訊
SQL> col message for a100
SQL> col name for a15
SQL> select name,database_role,switchover_status from v$database;

NAME DATABASE_ROLE SWITCHOVER_STATUS
--------------- ---------------- --------------------
NCDB PHYSICAL STANDBY NOT ALLOWED

===========
十二：主從切換
===========
https://docs.oracle.com/cd/E11882_01/server.112/e41134/role_management.htm#SBYDB00625
主庫： 
SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH SESSION SHUTDOWN;
SQL> SHUTDOWN ABORT;
SQL> STARTUP MOUNT;
備庫： 
SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN;
---SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;
SQL> ALTER DATABASE OPEN;

當主庫故障時，通過上面的方式主從切換回報錯如下：
Database not available for switchover
  End-Of-REDO archived log file has not been recovered
  Archived log files detected beyond End-Of-REDO
  Incomplete recovery SCN:0:1038219 archive SCN:0:1037990
解決方案：
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH; 
---ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH FORCE; 
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN;
SQL> ALTER DATABASE OPEN;

rac1:
SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;
SWITCHOVER_STATUS
--------------------
SESSIONS ACTIVE

rac2: 
SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;
SWITCHOVER_STATUS
--------------------
TO STANDBY

rac1:
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH SESSION SHUTDOWN;
Database altered.
---rac1執行後例項自動abort，rac2節點例項也自動shutdown;

rac1:
Switchover: Complete - Database shutdown required
USER (ospid: 14987): terminating the instance
Instance terminated by USER, pid = 14987
Completed: ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH SESSION SHUTDOWN
Shutting down instance (abort)
License high water mark = 8
Sat Mar 17 15:37:51 2018
Instance shutdown complete

rac2:
Sat Mar 17 15:37:48 2018
Switchover in progress in another database instance - Database is shutdown autom
LGWR (ospid: 72154): terminating the instance due to error 16456
Instance terminated by LGWR, pid = 72154

rac1:
SQL> startup mount
ORA-01565: error in identifying file '+DATA/ncdb/spfilencdb.ora'
ORA-17503: ksfdopn:2 Failed to open file +DATA/ncdb/spfilencdb.ora
ORA-15001: diskgroup "DATA" does not exist or is not mounted
ORA-00600: internal error code, arguments: [kfksoGet01], [0x8FC8AAE78], [], [], [], [], [], [], [], [], [], []

---RAC切換為備庫角色後rac1無法mount,rac2可以正常mount;

rac2: 
SQL> startup mount
ORACLE instance started.
Total System Global Area 4.0353E+10 bytes
Fixed Size    2261928 bytes
Variable Size 7247760472 bytes
Database Buffers 3.3018E+10 bytes
Redo Buffers   85204992 bytes
Database mounted.

備庫(standby)切換主庫： 
SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;
SWITCHOVER_STATUS
--------------------
SESSIONS ACTIVE

SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN;
SQL> ALTER DATABASE OPEN;
---SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;

SQL> select name,database_role,switchover_status from v$database;
NAME DATABASE_ROLE SWITCHOVER_STATUS
--------------- ---------------- --------------------
NCDB PRIMARY RESOLVABLE GAP
......
NAME DATABASE_ROLE SWITCHOVER_STATUS
--------------- ---------------- --------------------
NCDB PRIMARY TO STANDBY

rac2:
SQL> select name,database_role,switchover_status from v$database;
NAME  DATABASE_ROLE    SWITCHOVER_STATUS
--------- ---------------- --------------------
NCDB  PHYSICAL STANDBY RECOVERY NEEDED

===================
十三：實施過程中遇到的問題
===================
主庫警告日誌報錯如下
------------------------------------------------------------
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
------------------------------------------------------------
解決方案：將rac1密碼檔案同步到rac2和standby並改名；

rac1警告日誌報錯如下：
PING[ARC2]: Heartbeat failed to connect to standby 'standby'. Error is 16058.

SQL>  select dest_id,error from v$archive_dest;
   DEST_ID ERROR
---------- -----------------------------------------------------------------
1
2 ORA-16058: standby database instance is not mounted

rac2警告日誌報錯如下：
PING[ARC2]: Heartbeat failed to connect to standby 'standby'. Error is 16058.
SQL>  select dest_id,error from v$archive_dest;
   DEST_ID ERROR
---------- -----------------------------------------------------------------
1
2 ORA-01034: ORACLE not available

解決方案：檢查引數檔案，網路檔案，備庫狀態等沒發現問題，重啟備庫例項後恢復正常；  
 
 

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