RAC環境下DataGuard到單機

RAC環境下DataGuard到單機

環境描述：

Oracle Version:11.2.0.3.0
Primary端：
雙節點的RAC,scan使用DNS解析,跑兩個執行個體(JSTDB&PAYDB)
Standby端：
單機的Dataguard

1.設定網路環境;
vi /etc/hosts
192.168.253.101    rac1.test.com    rac1
192.168.253.102    rac2.test.com    rac2
192.168.253.103    rac1-vip.test.com  rac1-vip
192.168.253.104    rac2-vip.test.com  rac2-vip
10.10.10.11      rac1-priv.test.com  rac1-priv
10.10.10.12      rac2-priv.test.com  rac2-priv
192.168.253.200    standby
vi /etc/resolv.conf
nameserver 192.168.253.100

nslookup scan.test.com
Server:192.168.253.100
Address:192.168.253.100#53
Name:scan.test.com
Address: 192.168.253.107
Name:scan.test.com
Address: 192.168.253.106
Name:scan.test.com
Address: 192.168.253.105

2.修改force logging;
select force_logging from v$database;
alter database force logging; 

--------------------------------------分割線 --------------------------------------

Oracle 11gR2 在VMWare虛擬機器中安裝步驟

Debian 下 安裝 Oracle 11g XE R2

Oracle Data Guard 重要配置參數

基於同一主機配置 Oracle 11g Data Guard

探索Oracle之11g DataGuard

Oracle Data Guard （RAC+DG) 歸檔刪除策略及指令碼

Oracle Data Guard 的角色轉換

Oracle Data Guard的日誌FAL gap問題

Oracle 11g Data Guard Error 16143 Heartbeat failed to connect to standby 處理方法

--------------------------------------分割線 --------------------------------------

3.查看並添加standby logfile;
set line 180 pages 1000
col member for a50
select * from v$logfile order by GROUP#;
alter system set standby_file_management=manual scope=both sid='*';
alter database add standby logfile thread 1 group 5 '+DATA/jstdb/onlinelog/standby_redo1-1.log' size 50M;
alter database add standby logfile thread 1 group 6 '+DATA/jstdb/onlinelog/standby_redo1-2.log' size 50M;
alter database add standby logfile thread 1 group 7 '+DATA/jstdb/onlinelog/standby_redo1-3.log' size 50M;
alter database add standby logfile thread 1 group 8 '+DATA/jstdb/onlinelog/standby_redo1-4.log' size 50M;
alter database add standby logfile thread 2 group 9 '+DATA/jstdb/onlinelog/standby_redo2-1.log' size 50M;
alter database add standby logfile thread 2 group 10 '+DATA/jstdb/onlinelog/standby_redo2-2.log' size 50M;
alter database add standby logfile thread 2 group 11 '+DATA/jstdb/onlinelog/standby_redo2-3.log' size 50M;
alter database add standby logfile thread 2 group 12 '+DATA/jstdb/onlinelog/standby_redo2-4.log' size 50M;
alter system set standby_file_management=auto scope=both sid='*'; 

4.收集主庫的相關資訊;
set line 180 pages 1000
col value for a90
col name for a50
select name,value from v$parameter
 where name in ('db_name',
 'db_unique_name',
 'log_archive_config',
 'log_archive_dest_1',
 'log_archive_dest_2',
 'log_archive_dest_state_1',
 'log_archive_dest_state_2',
 'remote_login_passwordfile',
 'remote_listener',
 'log_archive_format',
 'log_archive_max_processes',
 'fal_server','fal_client',
 'db_file_name_convert',
 'log_file_name_convert',
 'standby_file_management'); 

5.建立本地硬碟的歸檔日誌目錄以及rman的備份目錄;
mkdir -p /u01/arch/JSTDB
chown -R oracle.oinstall /u01/arch/
mkdir -p /u01/rman/JSTDB
chown -R oracle.oinstall /u01/rman/

6.修改主庫的DG配置;
ALTER SYSTEM SET DB_UNIQUE_NAME=JSTDB scope=both sid='*';
ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(JSTDB,JSTDG1)' scope=both sid='*';
ALTER SYSTEM SET LOG_ARCHIVE_DEST_1='LOCATION=+FRA/JSTDB/ARCHIVELOG/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=JSTDB' scope=both sid='*';
ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=JSTDG1 LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=JSTDG1' 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 REMOTE_LOGIN_PASSWORDFILE=SHARED scope=spfile sid='*';
ALTER SYSTEM SET REMOTE_LISTENER='scan.test.com:1521' scope=spfile sid='*';
ALTER SYSTEM SET LOG_ARCHIVE_MAX_PROCESSES=8 SCOPE=both sid='*';
ALTER SYSTEM SET FAL_CLIENT=JSTDB SCOPE=both sid='*';
ALTER SYSTEM SET FAL_SERVER=JSTDG1 SCOPE=both sid='*';
ALTER SYSTEM SET db_file_name_convert='/u01/app/oracle/oradata/JSTDG1/','+DATA/JSTDB/' SCOPE=spfile sid='*';
ALTER SYSTEM SET log_file_name_convert='/u01/app/oracle/oradata/JSTDG1/onlinelog/','+DATA/JSTDB/onlinelog/','/u01/app/oracle/oradata/JSTDG1/onlinelog/','+FRA/JSTDB/onlinelog/','/u01/arch/JSTDG1/','+FRA/JSTDB/ARCHIVELOG/' SCOPE=spfile sid='*';
ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO SCOPE=both sid='*'; 

修改archivelog的格式
alter system set log_archive_format='arch_%r_%t_%s.arc' scope=spfile sid='*';
shutdown immediate
startup 

7.建立standby資料庫的pfile檔案;
create pfile='/u01/rman/initJSTDB.ora' from spfile; 

8.修改tns;
cd $ORACLE_HOME/network/admin
vi tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

JSTDB =
  (DESCRIPTION =
  (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(HOST = scan.test.com)(PORT = 1521))
(LOAD_BALANCE = yes)
)
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = JSTDB)
    )
  )

JSTDB1 =
  (DESCRIPTION =
  (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(HOST = rac1.test.com)(PORT = 1521))
    (ADDRESS = (PROTOCOL = TCP)(HOST = rac1-vip.test.com)(PORT = 1521))
  )
    (CONNECT_DATA =
      (SERVICE_NAME = JSTDB)
      (INSTANCE_NAME = JSTDB1)
    )
  )

JSTDB2 =
  (DESCRIPTION =
  (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(HOST = rac2.test.com)(PORT = 1521))
    (ADDRESS = (PROTOCOL = TCP)(HOST = rac2-vip.test.com)(PORT = 1521))
  )
    (CONNECT_DATA =
      (SERVICE_NAME = JSTDB)
      (INSTANCE_NAME = JSTDB2)

    )
  )

JSTDG1 =
  (DESCRIPTION =
  (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(HOST = standby)(PORT = 1521))
)
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = JSTDG1)
    )
  )

PAYDB =
  (DESCRIPTION =
  (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(HOST = scan.test.com)(PORT = 1521))
(LOAD_BALANCE = yes)
)
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = PAYDB)
    )
  )

PAYDG1 =
  (DESCRIPTION =
  (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(HOST = standby)(PORT = 1521))
)
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = PAYDB)
    )
  )

9.備庫添加監聽;

cd $ORACLE_HOME/network/admin
vi listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/network/admin/listener.ora
# Generated by Oracle configuration tools.
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = standby)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = JSTDG1)
      (ORACLE_HOME = /u01/app/oracle)
      (SID_NAME = JSTDB)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = PAYDG1)
      (ORACLE_HOME = /u01/app/oracle)
      (SID_NAME = PAYDB)
    )
  )
ADR_BASE_LISTENER = /u01/app/oracle
備庫重啟監聽服務
lsnrctl stop
lsnrctl start

10.在RAC節點1產生standby控制檔案;
export ORACLE_SID=JSTDB1
rman target /
backup device type disk format '/u01/rman/ctl01.ctl' current controlfile for standby; 

scp /u01/rman/ctl01.ctl standby:/u01/rman/

11.在standby建立相關目錄;
@root使用者
mkdir -p /u01/rman/JSTDB
chown -R oracle.oinstall /u01/rman/
mkdir -p /u01/arch/JSTDG1
chown -R oracle.oinstall /u01/arch/
@oracle使用者
mkdir -p /u01/app/oracle/oradata/JSTDG1/datafile/
mkdir -p /u01/app/oracle/oradata/JSTDG1/onlinelog
mkdir -p /u01/app/oracle/admin/JSTDG1/adump

12.複製密碼檔案;

orapwd file=/$ORACLE_HOME/dbs/orapwJSTDB1 password=oracle force=y ignorecase=y
scp /$ORACLE_HOME/dbs/orapwJSTDB1 rac2:/$ORACLE_HOME/dbs/orapwJSTDB2
scp /$ORACLE_HOME/dbs/orapwJSTDB1 standby:/u01/app/oracle/dbs/orapwJSTDB

13.拷貝參數檔案到備庫，並修改相關內容;
@主庫
scp /u01/rman/initJSTDB.ora standby:/u01/app/oracle/dbs/initJSTDB.ora
@備庫
vi /u01/app/oracle/dbs/initJSTDB.ora
*.audit_file_dest='/u01/app/oracle/admin/JSTDG1/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'

*.control_files='/u01/app/oracle/oradata/JSTDG1/control01.ctl','/u01/app/oracle/oradata/JSTDG1/control02.ctl','/u01/app/oracle/oradata/JSTDG1/control03.ctl'
*.db_block_size=8192
#*.db_create_file_dest='/u01/app/oracle/oradata/'
#*.db_create_online_log_dest_1='/u01/app/oracle/oradata/onlinelog/'
*.db_domain=''
*.db_file_name_convert='+DATA/JSTDB/','/u01/app/oracle/oradata/JSTDG1/'
*.db_name='JSTDB'
*.db_unique_name='JSTDG1'
*.db_recovery_file_dest=''
*.fal_client='JSTDG1'
*.fal_server='JSTDB'
*.log_archive_config='DG_CONFIG=(JSTDB,JSTDG1)'
*.log_archive_dest_1='LOCATION=/u01/arch/JSTDG1/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=JSTDG1'
*.log_archive_dest_2='SERVICE=JSTDB LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=JSTDB'
*.log_archive_format='arch_%r_%t_%s.arc'
*.log_archive_dest_state_1='ENABLE'
*.log_archive_dest_state_2='ENABLE'
*.log_archive_max_processes=8

*.log_file_name_convert='+DATA/JSTDB/onlinelog/','/u01/app/oracle/oradata/JSTDG1/onlinelog/','+FRA/JSTDB/onlinelog/','/u01/app/oracle/oradata/JSTDG1/onlinelog/','+FRA/JSTDB/ARCHIVELOG/','/u01/arch/JSTDG1/'
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='SHARED'
*.standby_file_management='AUTO'
*.undo_management='AUTO'
undo_tablespace='UNDOTBS1'

14.把備庫啟動到nomount狀態;
export ORACLE_SID=JSTDB
sqlplus / as sysdba
startup nomount pfile='/u01/app/oracle/dbs/initJSTDB.ora';
create spfile from pfile='/u01/app/oracle/dbs/initJSTDB.ora'; 

 

15.恢複備庫的控制檔案;
rman target /
restore standby controlfile from '/u01/rman/ctl01.ctl'; 

 

16.收集RAC資料庫的資訊;

col file_name for a70
select file_name, file_id from dba_data_files; 

FILE_NAME  FILE_ID
---------------------------------------------------------------------- ----------
+DATA/jstdb/datafile/system.259.9079385031
+DATA/jstdb/datafile/sysaux.260.9079385092
+DATA/jstdb/datafile/undotbs1.261.9079385153
+DATA/jstdb/datafile/undotbs2.263.9079385254
+DATA/jstdb/datafile/users.264.9079385275
4 rows selected.

17.備份主庫全庫（僅供保險備份，不是拷貝到standby）;
rman target /

backup format '/u01/rman/JSTDB/fulldb_%d_%U' database include current controlfile plus archivelog delete input; 

更多詳情見請繼續閱讀下一頁的精彩內容：

1 2 下一頁