Oracle 11g - 建立RAC + DataGuard

Data Guard 運作圖

環境說明 
一組RAC+ASM 搭配 DataGuard (single instance)


Oracle Database :11.2.0.4

PRIMARY RAC1 IP : 10.1.53.46
PRIMARY RAC2 IP : 10.1.53.47
STANDBY  IP : 10.1.53.100

PRIMARY及Standby SID皆使用 orcl


1.設定PRIMARY及Standby Tnsnames.ora 連線資訊, RAC及Standby都設一樣

ORCL2 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.1.53.146)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl2)
      (INSTANCE_NAME = orcl2)
    )
  )

ORCL1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.1.53.147)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl1)
      (INSTANCE_NAME = orcl1)
    )
  )

ORCL_ST =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST =10.1.53.100)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SID = orcl)
    )
  )

2. 設定 Standby listener.ora, 加入以下設定

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = orcl)
(ORACLE_HOME = /ora/app/11/db)
)
)

在Standby db設定靜態註冊，如果没有該參數，待會的Standby db 在nomount 時PRIMARY會連不到 standby,
或者也有可能會報PING[ARC1]: Heartbeat failed to connect to standby 'orcl_st'. Error is 12514.錯誤


3. 把PRIMARY 設為 force狀態 
alter database force logging;

4.PRIMARY必須為 ARCHIVE Log mode

SQL> archive log list;
Database log mode        Archive Mode
Automatic archival        Enabled
Archive destination        /ora/app/11/ARCH
Oldest online log sequence     70
Next log sequence to archive   0
Current log sequence        70


SQL> 

5.PRIMARY DB中 添加3組 Standby Rode log file,thread 1,2 個三組

Alter database add standby logfile thread 1 group 5  size 50m;

Alter database add standby logfile thread 1 group 6  size 50m;

Alter database add standby logfile thread 1 group 7  size 50m;

Alter database add standby logfile thread 2 group 8  size 50m;

Alter database add standby logfile thread 2 group 9  size 50m;

Alter database add standby logfile thread 2 group 10  size 50m;

-- 
ALTER DATABASE ADD STANDBY LOGFILE  thread 1 GROUP 21 ('+DATA_IDX05/lg/onlinelog/st_redo_01_01.redo') size 2000M;
ALTER DATABASE ADD STANDBY LOGFILE  thread 1 GROUP 22 ('+DATA_IDX05/lg/onlinelog/st_redo_01_02.redo') size 2000M;
ALTER DATABASE ADD STANDBY LOGFILE  thread 1 GROUP 23 ('+DATA_IDX05/lg/onlinelog/st_redo_01_03.redo') size 2000M;
ALTER DATABASE ADD STANDBY LOGFILE  thread 1 GROUP 24 ('+DATA_IDX05/lg/onlinelog/st_redo_01_04.redo') size 2000M;
ALTER DATABASE ADD STANDBY LOGFILE  thread 1 GROUP 25 ('+DATA_IDX05/lg/onlinelog/st_redo_01_05.redo') size 2000M;
ALTER DATABASE ADD STANDBY LOGFILE  thread 1 GROUP 26 ('+DATA_IDX05/lg/onlinelog/st_redo_01_06.redo') size 2000M;
ALTER DATABASE ADD STANDBY LOGFILE  thread 1 GROUP 27 ('+DATA_IDX05/lg/onlinelog/st_redo_01_07.redo') size 2000M;
ALTER DATABASE ADD STANDBY LOGFILE  thread 1 GROUP 28 ('+DATA_IDX05/lg/onlinelog/st_redo_01_08.redo') size 2000M;
ALTER DATABASE ADD STANDBY LOGFILE  thread 1 GROUP 29 ('+DATA_IDX05/lg/onlinelog/st_redo_01_09.redo') size 2000M;
ALTER DATABASE ADD STANDBY LOGFILE  thread 1 GROUP 30 ('+DATA_IDX05/lg/onlinelog/st_redo_01_10.redo') size 2000M;
ALTER DATABASE ADD STANDBY LOGFILE  thread 1 GROUP 31 ('+DATA_IDX05/lg/onlinelog/st_redo_01_11.redo') size 2000M;



6.產生password file

RAC1:orapwd file=$ORACLE_HOME/dbs/orapworcl1  password=oracle  entries=10 ignorecase=Y

RAC2:orapwd file=$ORACLE_HOME/dbs/orapworcl2  password=oracle  entries=10 ignorecase=Y

Standby:orapwd file=$ORACLE_HOME/dbs/orapworcl  password=oracle  entries=10 ignorecase=Y

7.在PRIMARY DB 創一個 standby controlfile 

alter database create standby controlfile as '/ora/BACKUP/control01.ctl';

把檔案傳送至 Standby 端
scp /ora/BACKUP/control01.ctl datagu:/ora/app/11/oradata/control01.ctl
scp /ora/BACKUP/control01.ctl datagu:/ora/app/11/oradata/control02.ctl


8.修改 PRIMARY 及 STANDBY 的Spfile.ora

PRIMARY 設定如下

*.audit_file_dest='/ora/base/db/admin/orcl/adump'
*.audit_trail='db'
*.cluster_database=true
*.compatible='11.2.0.0.0'
*.control_files='+DATA01/orcl/controlfile/current.260.912971595'
*.db_block_size=8192
*.db_create_file_dest='+DATA01'
*.db_domain=''
*.db_name='orcl'
*.diagnostic_dest='/ora/base/db'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
orcl1.instance_number=1
orcl2.instance_number=2
*.memory_target=786432000
*.open_cursors=300
*.processes=150
*.remote_listener='scan:1521'
*.remote_login_passwordfile='exclusive'
orcl2.thread=2
orcl1.thread=1
orcl1.undo_tablespace='UNDOTBS1'
orcl2.undo_tablespace='UNDOTBS2'

*.DB_UNIQUE_NAME='orcl2'
*.log_archive_dest_state_1='ENABLE'
*.log_archive_dest_state_2='DEFER'
orcl1.log_archive_dest_1='location=+DATA01/ORCL/ARCH VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=orcl1'
orcl2.log_archive_dest_1='location=+DATA01/ORCL/ARCH VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=orcl2'
*.log_archive_dest_2='service=orcl_st LGWR ASYNC NOAFFIRM VALID_FOR=(online_logfiles,primary_role) db_unique_name=orcl_st'

*.log_archive_config='dg_config=(orcl,orcl_st)'
*.db_file_name_convert='/ora/app/11/oradata','+DATA01/ORCL/datafile','ora/app/11/oradata/tempfile','+DATA01/ORCL/tempfile'
*.log_file_name_convert='/ora/app/11/oradata', '+DATA01/orcl/onlinelog/'
*.standby_file_management=auto
*.standby_archive_dest='+DATA01/ORCL/ARCH'
*.fal_server='orcl_st'
orcl1.fal_client='orcl1'

orcl2.fal_client='orcl2'


STANDBY 設定如下
*.audit_file_dest='/ora/app/11/admin/orcl/adump'
*.background_dump_dest='/ora/app/11/admin/orcl/bdump'
*.core_dump_dest='/ora/app/11/admin/orclcdump'
*.user_dump_dest='/ora/app/11/admin/orcl/udump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/ora/app/11/oradata/control01.ctl','/ora/app/11/oradata/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='orcl'
*.db_unique_name='orcl_st'
*.diagnostic_dest='/ora/app/11/db'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
*.log_archive_dest_state_2='ENABLE'
*.memory_target=386432000
*.open_cursors=300
*.processes=150
*.job_queue_processes=10

*.db_unique_name=orcl_st
*.LOG_ARCHIVE_DEST_STATE_1=ENABLE 
*.LOG_ARCHIVE_DEST_STATE_2=ENABLE 
*.log_archive_config='dg_config=(orcl1,orcl_st)'
*.remote_login_passwordfile='exclusive'
*.log_archive_dest_1='LOCATION=/ora/app/11/ARCH VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=orcl_st'
*.log_archive_dest_2='SERVICE=orcl1 LGWR ASYNC NOAFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcl1'
*.db_file_name_convert='+DATA01/ORCL/datafile','/ora/app/11/oradata','+DATA01/ORCL/tempfile','/ora/app/11/oradata/tempfile'
*.log_file_name_convert='+DATA01/ORCL/onlinelog','/ora/app/11/oradata'
*.standby_file_management=auto
*.standby_archive_dest='/ora/app/11/ARCH'
*.fal_server='orcl1'
*.fal_client='orcl_st'


因為PRIMARY 使用ASM ,所以參數上要設定 db_file_name_convert,log_file_name_convert 讓 Standby 的datafile路徑做轉換

9.backup PRIMARY DB

RUN {

allocate channel c1 type disk;

allocate channel c2 type disk;

sql 'alter system archive log current';

backup current controlfile for standby format='/ora/BACKUP/control_%U';

BACKUP FORMAT '/ora/BACKUP/orcl_%U_%T' skip inaccessible filesperset 5 DATABASE ;

sql 'alter system archive log current';

BACKUP FORMAT '/ora/BACKUP/arch_%U_%T' skip inaccessible filesperset 5 ARCHIVELOG ALL ;

release channel c2;

release channel c1;

}

10.將備份檔案傳至Standby 

scp * datagu:/ora/BACKUP

11.standby 使用剛剛修改好的pfile.ora開至 nomount 狀態 ,listener 也一併開啟

SQL> startup nomount pfile='/ora/BACKUP/pfile.ora';

ORACLE instance started.

Total System Global Area  388317184 bytes
Fixed Size    2213656 bytes
Variable Size  306186472 bytes
Database Buffers   71303168 bytes
Redo Buffers    8613888 bytes

SQL> 

[oracle@datagu ~]$ lsnrctl start

LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 03-JUN-2016 18:05:20

Copyright (c) 1991, 2009, Oracle.  All rights reserved.

Starting /ora/app/11/db/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 11.2.0.1.0 - Production
System parameter file is /ora/app/11/db/network/admin/listener.ora
Log messages written to /ora/app/11/diag/tnslsnr/datagu/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=datagu)(PORT=1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=datagu)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.1.0 - Production
Start Date                03-JUN-2016 18:05:20
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /ora/app/11/db/network/admin/listener.ora
Listener Log File         /ora/app/11/diag/tnslsnr/datagu/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=datagu)(PORT=1521)))
Services Summary...
Service "orcl" has 1 instance(s).
  Instance "orcl", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
[oracle@datagu ~]$ 

12.在 PRIMARY端登入standby 端 rman 來還原資料庫

[root@node01 ~]# rman target / auxiliary sys/oracle@orcl_st

RMAN> duplicate target database for standby;


如果在RMAN恢復時不指定 nofilenamecheck 參數

則在數據文件相同文件名恢復時會出現RMAN-05501錯誤
duplicate target database for standby nofilenamecheck;

13.PRIMARY 將使用修改完後的pfile.ora 重啟

14.查看 PRIMARY ARchive log 傳送狀態

SQL> Select dest_name,status,error from v$archive_dest;

DEST_NAME       STATUS ERROR

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

LOG_ARCHIVE_DEST_1       VALID

LOG_ARCHIVE_DEST_2       VALID

LOG_ARCHIVE_DEST_3       INACTIVE

LOG_ARCHIVE_DEST_4       INACTIVE

LOG_ARCHIVE_DEST_5       INACTIVE

LOG_ARCHIVE_DEST_6       INACTIVE

LOG_ARCHIVE_DEST_7       INACTIVE

LOG_ARCHIVE_DEST_8       INACTIVE

LOG_ARCHIVE_DEST_9       INACTIVE

LOG_ARCHIVE_DEST_10       INACTIVE

LOG_ARCHIVE_DEST_11       INACTIVE

DEST_NAME       STATUS ERROR

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

LOG_ARCHIVE_DEST_12       INACTIVE

LOG_ARCHIVE_DEST_13       INACTIVE

LOG_ARCHIVE_DEST_14       INACTIVE

LOG_ARCHIVE_DEST_15       INACTIVE

LOG_ARCHIVE_DEST_16       INACTIVE

LOG_ARCHIVE_DEST_17       INACTIVE

LOG_ARCHIVE_DEST_18       INACTIVE

LOG_ARCHIVE_DEST_19       INACTIVE

LOG_ARCHIVE_DEST_20       INACTIVE

LOG_ARCHIVE_DEST_21       INACTIVE

LOG_ARCHIVE_DEST_22       INACTIVE

DEST_NAME       STATUS ERROR

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

LOG_ARCHIVE_DEST_23       INACTIVE

LOG_ARCHIVE_DEST_24       INACTIVE

LOG_ARCHIVE_DEST_25       INACTIVE

看是否為:VALID, 假如跳出 ORA-16191: Primary log shipping client not logged on standby

請查看 

1.remote_login_passwordfile 是否設為 SHARED or EXCLUSIVE 

2.password files PRIMARY 及 STANDBY 都要一樣

15.驗證Archive log 是否自動傳送到 STANDBY

在RAC 1切換 log

SQL> alter system switch logfile;

System altered.

SQL> 

SQL> archive log list;

Database log mode       Archive Mode

Automatic archival       Enabled

Archive destination       +DATA01/orcl/arch

Oldest online log sequence     56

Next log sequence to archive   57

Current log sequence       57

SQL> 

在RAC 2切換 log

SQL> alter system switch logfile;

System altered.

SQL> 

SQL> archive log list;

Database log mode        Archive Mode

Automatic archival        Enabled

Archive destination        +DATA01/orcl/arch

Oldest online log sequence     69

Next log sequence to archive   70

Current log sequence        70

SQL>

STANDBY 查詢

select thread#,max(sequence#) max from v$archived_log group by thread#;SQL> 

   THREAD#  MAX

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

1           69

2           56

SQL> 

SQL> select recid, SEQUENCE# ,APPLIED from v$archived_log ;

     RECID  SEQUENCE# APPLIED

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

1   52 YES

2   41 YES

3   40 YES

4   53 YES

5   43 YES

6   54 YES

7   53 YES

8   55 YES

9   42 YES

10   56 YES

11   44 YES

     RECID  SEQUENCE# APPLIED

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

12   57 YES

13   58 YES

14   59 YES

15   45 YES

16   46 YES

17   47 YES

18   60 YES

19   61 YES

20   62 YES

21   48 YES

22   63 YES

     RECID  SEQUENCE# APPLIED

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

23   64 YES

24   65 YES

25   49 YES

26   50 YES

27   51 YES

28   66 YES

29   53 YES

30   69 YES

31   52 YES

32   68 YES

33   67 YES

     RECID  SEQUENCE# APPLIED

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

34   54 YES

35   55 NO

36   56 NO

36 rows selected.

成功

相關LOG查詢

select thread#,max(sequence#) max from v$archived_log group by thread#;

select recid, SEQUENCE# ,APPLIED from v$archived_log ;

Select dest_name,status,error from v$archive_dest;

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

select * FROM V$ARCHIVE_GAP;

select group#,thread#,sequence#,archived,status from v$standby_log;

查詢錯誤
select * from v$dataguard_status order by timestamp desc 

RAC + Data guard Switchover 



1.先查一下雙方的資料庫狀態

SQL> select inst_id,database_role,OPEN_MODE from  gv$database;

   INST_ID DATABASE_ROLE       OPEN_MODE
---------- ---------------- --------------------
1 PRIMARY        READ WRITE

SQL> 

SQL> select inst_id,database_role,OPEN_MODE from  gv$database;

   INST_ID DATABASE_ROLE          OPEN_MODE

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

1 PHYSICAL STANDBY        MOUNTED

SQL> 

2.查看 PRIMARY 的Switchover status 

SQL> select switchover_status from v$database;

SWITCHOVER_STATUS
--------------------
TO STANDBY


SQL> 

TO STANDBY 表示 PRIMARY 可以進行 switch, 要是 sessions active 表示目前還有session 在進行讀取, 則必須等session結束


3.因為PRIMARY 是RAC, 留一個node就好,其他就先關閉

srvctl stop instance -d orcl -i orcl2
srvctl status database -d orcl Instance orcl1 is running on node node01 Instance orcl2 is not running on node node02


4.PRIMARY 切換成 STANDBY

SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO STANDBY WITH SESSION SHUTDOWN; Database altered.


這時候查看alert.log, 就可以看到在切換時, PRIMARY 會把目前在的redo log 丟過去 STANDBY 做 APPLIED

PRIMARY :
ALTER DATABASE COMMIT TO SWITCHOVER TO STANDBY WITH SESSION SHUTDOWN
ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY  [Process Id: 11043] (orcl1)
Sat Jun 04 22:02:17 2016
Thread 1 advanced to log sequence 127 (LGWR switch)
  Current log# 1 seq# 127 mem# 0: +DATA01/orcl/onlinelog/group_1.261.912971607

STANDBY :

Media Recovery Log /ora/app/11/ARCH/1_126_912971607.dbf
Media Recovery Waiting for thread 1 sequence 127
Sat Jun 04 22:02:34 2016
RFS[6]: Assigned to RFS process 31438
RFS[6]: Identified database type as 'physical standby': Client is ARCH pid 11069
Sat Jun 04 22:02:55 2016
RFS[7]: Assigned to RFS process 31441
RFS[7]: Identified database type as 'physical standby': Client is Foreground pid 11043
RFS[7]: Selected log 5 for thread 1 sequence 127 dbid 1440752330 branch 912971607
Sat Jun 04 22:02:55 2016
Archived Log entry 133 added for thread 1 sequence 127 ID 0x55ea51f1 dest 1:
Sat Jun 04 22:02:56 2016
RFS[8]: Assigned to RFS process 31443
RFS[8]: Identified database type as 'physical standby': Client is ARCH pid 11069
Sat Jun 04 22:03:05 2016
Media Recovery Log /ora/app/11/ARCH/1_127_912971607.dbf
Media Recovery Waiting for thread 1 sequence 128

5.STANDBY 切換成 PRIMARY 

SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN; Database altered.


這邊的alert.log 則可以看到, standby redo log 被初始化

Sat Jun 04 22:10:37 2016
 ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN
ALTER DATABASE SWITCHOVER TO PRIMARY (orcl)
Maximum wait for role transition is 15 minutes.
Backup controlfile written to trace file /ora/app/11/db/diag/rdbms/orcl_st/orcl/trace/orcl_ora_29933.trc
SwitchOver after complete recovery through change 1414599
Online log /ora/app/11/oradata/group_1.261.912971607: Thread 1 Group 1 was previously cleared
Online log /ora/app/11/oradata/group_2.262.912971615: Thread 1 Group 2 was previously cleared
Online log /ora/app/11/oradata/group_3.265.912972281: Thread 2 Group 3 was previously cleared
Online log /ora/app/11/oradata/group_4.266.912972289: Thread 2 Group 4 was previously cleared
Standby became primary SCN: 1414597
Switchover: Complete - Database mounted as primary
Completed:  ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN
Sat Jun 04 22:10:37 2016
ARC3: Becoming the 'no SRL' ARCH

6.STANDBY 切換成 PRIMARY 後就可以直接OPEN

SQL> alter database open;

Database altered.

SQL>

7.PRIMARY 切換成 STANDBY 後執行 Active Data Guard (Active Data Guard 11g 新功能, 可以允許standby資料庫用read only 開啟到open,好讓standby 可以查詢資料或產出報表)


SQL> startup nomount ;
ORACLE instance started.

Total System Global Area  784998400 bytes
Fixed Size    2217464 bytes
Variable Size  574622216 bytes
Database Buffers  201326592 bytes
Redo Buffers    6832128 bytes
SQL> 
SQL> 
SQL> 
SQL> 
SQL> alter database mount standby database;

Database altered.

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;

Database altered.

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE cancel;

Database altered.

SQL> alter database open read only;

Database altered.

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;


Database altered.

SQL>
SQL> 

8.驗證功能

PRIMARY 切換 Redo log

SQL> alter system switch logfile;

System altered.

SQL> 

SQL> select recid, SEQUENCE# ,APPLIED from v$archived_log ;

     RECID  SEQUENCE# APPLIED
---------- ---------- ---------
       133  127 YES
       134  128 YES
       135  129 NO
       136  130 NO
       137  131 NO
       138  131 YES
       139  129 YES
       140  130 YES
       141  132 NO
       142  132 YES

Standby
select recid, SEQUENCE# ,APPLIED from v$archived_log ;

     RECID  SEQUENCE# APPLIED

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

       300  127 YES

       301  127 NO

       302  128 YES

     RECID  SEQUENCE# APPLIED

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

       303  128 NO

       304  131 YES

       305  129 YES

       306  130 YES

       307  132 YES