Oracle Dataguard配置搭建 10.2 RAC 到单机

ALLSTARS_ORACLE

Oracle Dataguard配置搭建 10.2RAC到单机


Primary数据库版本是10.2.0.4，2节点RAC，数据文件存储在ASM上。
Standby数据库版本是10.2.0.4，单机，数据文件存储在ASM上。


2.1        Dataguard配置过程
1)        Enable force logging in PRIMARY.
2)        Create SRL(standby redo logs) in PRIMARY.
3)        Backup the PRIMARY database.
4)        Make proper changes in the parameter file of PRIMARY.
5)        Create the parameter file for STANDBY
6)        Copy the files (RMAN Backup, init.ora) to STANDBY
7)        Establish the connectivity between PRIMARY and STANDBY.
8)        Start the STANDBY instance and use RMAN duplicate to create standby database
9)        Create SRL on standby.
10)        Start the MRP process,
11)        Verify whether the log are shipped and applied properly @the standby
2.2        Dataguard配置步骤
2.2.1        Enable Forced Logging on PRIMARY mac


SQL> ALTER DATABASE FORCE LOGGING;
SQL> select force_logging from v$database;

FOR
---
YES


2.2.2        Configure Standby Redo Log on PRIMARY
NOTE:SIZE OF STANDBY LOGFILE SHOULD BE SAME AS ONLINE LOGFILE
a.        Check the log files and sizes,

SQL> SELECT GROUP#,BYTES FROM V$LOG;

    GROUP#      BYTES
---------- ----------
         1  209715200
         2  209715200
         3  209715200
         4  209715200
         5  209715200
         6  209715200

6 rows selected.

b.        Create SRL

alter database add standby logfile thread 1 group 7 ('+DG1/mac/onlinelog/redostby_71.log','+DG1/mac/onlinelog/redostby_72.log') size 200m;
alter database add standby logfile thread 1 group 8 ('+DG1/mac/onlinelog/redostby_81.log','+DG1/mac/onlinelog/redostby_82.log') size 200m;
…
…
alter database add standby logfile thread 2 group 14 ('+DG1/mac/onlinelog/redostby_141.log','+DG1/mac/onlinelog/redostby_142.log') size 200m;

…..
…

SQL> alter database add standby logfile thread 1 group 10 ('+DG1/mac/onlinelog/redostby_101.log','+DG1/mac/onlinelog/redostby_102.log') size 200m;

Database altered.

SQL> alter database add standby logfile thread 2 group 11 ('+DG1/mac/onlinelog/redostby_111.log','+DG1/mac/onlinelog/redostby_112.log') size 200m;

Database altered.

SQL> alter database add standby logfile thread 2 group 12 ('+DG1/mac/onlinelog/redostby_121.log','+DG1/mac/onlinelog/redostby_122.log') size 200m;

Database altered.

SQL> alter database add standby logfile thread 2 group 13 ('+DG1/mac/onlinelog/redostby_131.log','+DG1/mac/onlinelog/redostby_132.log') size 200m;

Database altered.

SQL> alter database add standby logfile thread 2 group 14 ('+DG1/mac/onlinelog/redostby_141.log','+DG1/mac/onlinelog/redostby_142.log') size 200m;

Database altered.

SQL>

c.        Verify the standby redo log file groups were created(do this after the creation of stanby database)

SQL> SELECT GROUP#,THREAD#,SEQUENCE#,ARCHIVED,STATUS FROM V$STANDBY_LOG;

    GROUP#    THREAD#  SEQUENCE# ARC STATUS
---------- ---------- ---------- --- ----------
         7          1          0 YES UNASSIGNED
         8          1          0 YES UNASSIGNED
         9          1          0 YES UNASSIGNED
        10          1          0 YES UNASSIGNED
        11          2          0 YES UNASSIGNED
        12          2          0 YES UNASSIGNED
        13          2          0 YES UNASSIGNED
        14          2          0 YES UNASSIGNED

8 rows selected.

2.2.3        Use RMAN to backup PRIMARY database with archivelog & controlfile

RMAN>backup database format '/tmp/dbbkp/PRIMA_%U';
RMAN>backup archivelog all format '/tmp/dbbkp/PRIMA_ARC_%U';
RMAN> backup current controlfile for standby format '/tmp/dbbkp/PRIMA_CONTROL.bkp';

2.2.4        Make the necessary changes to PRIMARY

DB_NAME=mac
DB_UNIQUE_NAME=mac
LOG_ARCHIVE_CONFIG='DG_CONFIG=(mac,macstd)'
LOG_ARCHIVE_DEST_2= 'SERVICE=macstd LGWR ASYNC REOPEN=10 VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=macstd'
LOG_ARCHIVE_DEST_STATE_2=DEFER
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
LOG_ARCHIVE_MAX_PROCESSES=30
FAL_SERVER=macstd
FAL_CLIENT=mac
DB_FILE_NAME_CONVERT='macstd','mac'
LOG_FILE_NAME_CONVERT='macstd','mac'
STANDBY_FILE_MANAGEMENT=AUTO

2.2.5        Create the parameter file for standby

a.        CREATE PFILE='<specify any location>' from spfile; (@primary,)
b.        Make the necessary changes, for example,


DB_NAME=mac
DB_UNIQUE_NAME=macstd
LOG_ARCHIVE_CONFIG='DG_CONFIG=(mac,macstd)'
CONTROL_FILES='+DG'
DB_FILE_NAME_CONVERT='mac','macstd'
LOG_FILE_NAME_CONVERT='mac','macstd'
LOG_ARCHIVE_FORMAT=log%t_%s_%r.arc
LOG_ARCHIVE_DEST_2= 'SERVICE=mac LGWR ASYNC REOPEN=10 VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=mac'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
STANDBY_FILE_MANAGEMENT=AUTO
FAL_SERVER=mac
FAL_CLIENT=macstd

2.2.6        Copy the files (RMAN Backup, init.ora) to STANDBY

a)        Copy the RMAN backup files created in /tmp/dbbkp (step3) to STANDBY in identical location
Example:
scp /tmp/dbbkp/* oracle@core1:/tmp/dbbkp/*
b)        Copy the init.ora (step 5) to STANDBY
c)        Recreate the password file in standby using orapwd utility

2.2.7        Establish the connectivity between primary and standby.

In PRIMARY (TNSNAMES.ORA)

MYSTD=
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.9.200.108)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = MYSTD.server.com)
)
)


In STANDBY (TNSNAMES.ORA)

PRIMA=
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.9.200.107)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = PRIMA.server.com)
)
)


2.2.8        Start the STANDBY instance and use RMAN duplicate to create standby database
NOTE: Make sure the ASM instance also running.

$export ORACLE_SID=MYSTD
SQL>create spfile from pfile=’<specify the newly created parameter location>’
SQL>startup nomount

$RMAN target sys/<passwd>@primary catalog RMAN/RMAN@RMAN auxiliary sys/<passwd>
RMAN> RUN {
allocate auxiliary channel C1 device type disk;
duplicate target database for standby;
}


2.2.9        Create SRL(Standby Redo logs) on standby,

alter database add standby logfile thread 1 group 7 ('+DG1/mac/onlinelog/redostby_71.log','+DG1/mac/onlinelog/redostby_72.log') size 200m;
alter database add standby logfile thread 1 group 8 ('+DG1/mac/onlinelog/redostby_81.log','+DG1/mac/onlinelog/redostby_82.log') size 200m;
…
…
alter database add standby logfile thread 2 group 14 ('+DG1/mac/onlinelog/redostby_141.log','+DG1/mac/onlinelog/redostby_142.log') size 200m;

…..
…

SQL> alter database add standby logfile thread 1 group 10 ('+DG1/mac/onlinelog/redostby_101.log','+DG1/mac/onlinelog/redostby_102.log') size 200m;

Database altered.

SQL> alter database add standby logfile thread 2 group 11 ('+DG1/mac/onlinelog/redostby_111.log','+DG1/mac/onlinelog/redostby_112.log') size 200m;

Database altered.

SQL> alter database add standby logfile thread 2 group 12 ('+DG1/mac/onlinelog/redostby_121.log','+DG1/mac/onlinelog/redostby_122.log') size 200m;

Database altered.

SQL> alter database add standby logfile thread 2 group 13 ('+DG1/mac/onlinelog/redostby_131.log','+DG1/mac/onlinelog/redostby_132.log') size 200m;

Database altered.

SQL> alter database add standby logfile thread 2 group 14 ('+DG1/mac/onlinelog/redostby_141.log','+DG1/mac/onlinelog/redostby_142.log') size 200m;

Database altered.

SQL>

2.2.10        Start the MRP process in PRIMARY

In PRIMARY

SQL>ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE;

In STANDBY

SQL>ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

2.2.11        Verify whether the log are shipped and applied properly in the standby

a.        execute on PRIMARY database
SQL> ALTER SYSTEM SWITCH LOGFILE;
b.        execute on STANDBY database

SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

c.        Issue more log switches at PRIMARY

SQL> ALTER SYSTEM SWITCH LOGFILE;

d.        Verify the new redo data was archived on the standby database.On the standby database, query the V$ARCHIVED_LOG view to verify the redo data was received and archived on the standby database:

SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME  FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

NOTE: Verify new archived redo log files were applied. At the standby database, query the V$ARCHIVED_LOG view to verify the archived redo log files were applied.

SQL> SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

2.3        Dataguard常用命令

启动standby库恢复

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE using current logfile DISCONNECT FROM SESSION;

停止standby库恢复
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE cancel;

常用监控命令

主库端
SELECT * FROM V$ARCHIVED_LOG where thread#=2 ORDER BY SEQUENCE#;

SELECT * FROM V$ARCHIVED_LOG where thread#=2 ORDER BY SEQUENCE#;

备库端
alter session  set nls_date_format='yyyy-mm-dd hh24:mi:ss';

主备库lag
select * from V$DATAGUARD_STATS;

Dataguard状态
select * from V$DATAGUARD_STATUS;

恢复进程状态
SELECT PROCESS, STATUS, THREAD#, SEQUENCE#,BLOCK#, BLOCKS FROM V$MANAGED_STANDBY;

归档日志
SELECT THREAD#, SEQUENCE#, FIRST_CHANGE#, NEXT_CHANGE# FROM V$ARCHIVED_LOG;

恢复进程状态
select * from V$RECOVERY_PROGRESS;


2.4        当前遗留问题
2.4.1        Bug 4690705  Repeated ORA-1041 from heartbeat ping
A heartbeat ping may get stuck signalling ORA-1041 repeatedly.
When the standby is shutdown, the primary's PING gets error which is expected. However, the ping process never detaches after the original failure.  Therefore, the next heartbeat PING tries to use the same(failed) connection and tries to PING which then fails with ORA-1041.

Workaround:
  In the primary database, do "alter system archive log current".
当standby库重启后，heartbeat连接不释放，导致此问题。解决方案见上。目前没有补丁可以使用。