[terry筆記]11gR2_DataGuard搭建_primary零停機

11gR2搭建dataguard環境，此篇文章是利用rman搭建dataguard，這樣的好處是primary不用停機，當然，前提條件是primary已經開啟歸檔。

相對於可以停機，零停機傳送數據文件的方式，不能冷備然後拷貝數據文件，只能利用rman技術備份，並恢復至standby中，當然rman備份恢復我只用了最簡單的一種，一條命令備份，兩條命令恢復。

可以參考拷貝數據文件方式：http://www.cnblogs.com/kkterry/p/3819322.html

主備切換、模式調整等實驗會在以後更新。

 

環境信息介紹：

primary（正在運行，已經開啟歸檔，可正常交易）：
OS：OEL6.4
database：11.2.0.4.0
hostname：node4
192.168.100.131
ORACLE_SID=good
ORACLE_HOME=/u01/product/11.2.0
db_unique_name=good

standby（裝好database軟件）：
OS：OEL6.4
database：11.2.0.4.0
hostname：node5
192.168.100.132
ORACLE_SID=good
ORACLE_HOME=/u01/product/11.2.0
db_unique_name=bad

兩邊oracle用戶的環境變量：
export ORACLE_BASE=/u01
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0
export ORACLE_SID=good
export PATH=$PATH:$HOME/bin:$ORACLE_HOME/bin
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib
alias s='sqlplus / as sysdba'

 

1.  primary已經處於歸檔狀態（歸檔目錄/u01/arch），然後打開force logging:

SQL> archive log list

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            /u01/arch

Oldest online log sequence     24

Next log sequence to archive   26

Current log sequence           26

alter database force logging;

 

2. 配置listener與tnsnames，然後傳至standby

（primary可以是動態監聽，但是standby需要靜態監聽，因為動態監聽需要數據庫啟動至mount狀態才能監聽得到。standby目前只有軟件，沒有實例，所以動態監聽無法向外提供服務，以至於primary無法連接至standby的庫，導致無法測試連通性，並向向standby傳遞歸檔）

listner.ora:

# listener.ora Network Configuration File: /u01/product/11.2.0/network/admin/listener.ora

# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
        (ORACLE_HOME = /u01/product/11.2.0)
          (PROGRAM = extproc)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = good)
        (ORACLE_HOME = /u01/product/11.2.0)
      (SID_NAME = good)
  )
)
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost.localdomain)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

ADR_BASE_LISTENER = /u01

 tnsnames.ora:

# tnsnames.ora Network Configuration File: /u01/product/11.2.0/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
 
GOOD =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.131)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = good)
    )
  )
 
BAD =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.132)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = good)
    )
  )

 

3.兩邊打開監聽

lsnrctl start

此時可以測試兩邊是否連通

sqlplus sys/xxx@good as sysdba

sqlplus sys/xxx@bad as sysdba

 

4.配置primary參數，並添加standby logfile,以用來將來主備切換,standby logfile要比redo多一個組

此時的primary參數文件，裡面沒有相關dg的參數。

good.__db_cache_size=205520896
good.__java_pool_size=4194304
good.__large_pool_size=8388608
good.__oracle_base='/u01'#ORACLE_BASE set from environment
good.__pga_aggregate_target=209715200
good.__sga_target=343932928
good.__shared_io_pool_size=0
good.__shared_pool_size=113246208
good.__streams_pool_size=0
*.audit_file_dest='/u01/admin/good/adump'
*.audit_trail='db'
*.compatible='11.2.0.4.0'
*.control_files='/u01/oradata/good/control01.ctl','/u01/fast_recovery_area/good/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='good'
*.db_recovery_file_dest='/u01/fast_recovery_area'
*.db_recovery_file_dest_size=4385144832
*.diagnostic_dest='/u01'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=goodXDB)'
*.memory_target=550502400
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'
*.log_archive_dest_1='location=/u01/arch'

 

運行如下命令：具體參數含義在文章最後的附錄表中，也可以查詢官方文檔，解釋的更詳細。

（不用設置primary的db_unique_name,默認就有）

alter system set log_archive_config= 'DG_CONFIG=(good,bad)';

alter system set log_archive_dest_1= 'LOCATION=/u01/arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=good';

alter system set log_archive_dest_2= 'SERVICE=bad LGWR SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=bad';

alter system set log_archive_dest_state_1 = enable;

alter system set log_archive_dest_state_2 = enable;

alter system set fal_server=bad;

alter system set fal_client=good;

alter system set standby_file_management=AUTO;

添加standby redo： 

alter database add standby logfile
group 4 ('/u01/oradata/good/standby04.log')size 50m,
group 5 ('/u01/oradata/good/standby05.log')size 50m,
group 6 ('/u01/oradata/good/standby06.log')size 50m,
group 7 ('/u01/oradata/good/standby07.log')size 50m;

 

5.創建standby controlfile、pfile

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

create pfile from spfile;

 

6.利用rman備份primary，做全備即可

rman target /

backup database format '/u01/backup/full_%U'; 

 

7.把相關文件從primary傳遞至standby

監聽文件：listener.ora、tnsnames.ora --$ORACLE_HOME/network/admin(在之前已經傳過去了)

參數文件：initgood.ora  --$ORACLE_HOME/dbs

密碼文件：orapwgood  --$ORACLE_HOME/dbs

sty控制文件：control01.ctl  --$ORACLE_BASE/oradata/good

日志目錄（或直接創建文件夾）：$ORACLE_BASE/admin、$ORACLE_BASE/diag、$ORACLE_BASE/flash_recovery_area  --$ORACLE_BASE

備份文件：/u01/backup/* --/u01/backup/

 

8.修改standby參數文件,重點注意藍色的參數，綠色是重點要修改的。

good.__db_cache_size=222298112
good.__java_pool_size=4194304
good.__large_pool_size=8388608
good.__oracle_base='/u01'#ORACLE_BASE set from environment
good.__pga_aggregate_target=192937984
good.__sga_target=360710144
good.__shared_io_pool_size=0
good.__shared_pool_size=113246208
good.__streams_pool_size=0
*.audit_file_dest='/u01/admin/good/adump'
*.audit_trail='db'
*.compatible='11.2.0.4.0'
*.control_files='/u01/oradata/good/control01.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='good'
*.db_recovery_file_dest='/u01/fast_recovery_area'
*.db_recovery_file_dest_size=4385144832
*.db_unique_name='bad'
*.diagnostic_dest='/u01'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=goodXDB)'
*.fal_client='bad'
*.fal_server='good‘
*.log_archive_config='dg_config=(good,bad)'
*.log_archive_dest_1='location=/u01/arch valid_for=(all_logfiles,all_roles) db_unique_name=bad'
*.log_archive_dest_2='service=good lgwr sync valid_for=(online_logfiles,primary_role) db_unique_name=good'
*.log_archive_dest_state_1='ENABLE'
*.log_archive_dest_state_2='ENABLE'
*.memory_target=550502400
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.standby_file_management='AUTO'
*.undo_tablespace='UNDOTBS1'

 

9.standby啟動至mount，然後rman恢復

SQL*Plus: Release 11.2.0.4.0 Production on Wed Jul 2 20:32:36 2014
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
Connected to an idle instance.

SQL> startup nomount
ORACLE instance started.

Total System Global Area  551165952 bytes
Fixed Size                  2255112 bytes
Variable Size             385877752 bytes
Database Buffers          155189248 bytes
Redo Buffers                7843840 bytes

SQL> alter database mount;
Database altered.
 
SQL> exit

 

[oracle@node5 dbs]$ rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Wed Jul 2 20:33:57 2014
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
connected to target database: GOOD (DBID=1820366808, not open)

RMAN> catalog start with '/u01/backup/';

Starting implicit crosscheck backup at 02-JUL-14
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=14 device type=DISK
Crosschecked 2 objects
Finished implicit crosscheck backup at 02-JUL-14

Starting implicit crosscheck copy at 02-JUL-14
using channel ORA_DISK_1
Crosschecked 2 objects
Finished implicit crosscheck copy at 02-JUL-14

searching for all files in the recovery area
cataloging files...
no files cataloged

searching for all files that match the pattern /u01/backup/

List of Files Unknown to the Database
=====================================
File Name: /u01/backup/full_03pcdktt_1_1
File Name: /u01/backup/full_04pcdkvl_1_1

Do you really want to catalog the above files (enter YES or NO)? yes

cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /u01/backup/full_03pcdktt_1_1
File Name: /u01/backup/full_04pcdkvl_1_1

RMAN> restore database;

Starting restore at 02-JUL-14
using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /u01/oradata/good/system01.dbf
channel ORA_DISK_1: restoring datafile 00002 to /u01/oradata/good/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /u01/oradata/good/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /u01/oradata/good/users01.dbf
channel ORA_DISK_1: reading from backup piece /u01/backup/full_03pcdktt_1_1
channel ORA_DISK_1: piece handle=/u01/backup/full_03pcdktt_1_1 tag=TAG20140702T202533
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:45
Finished restore at 02-JUL-14

 

此時standby的/u01/arch會出現歸檔。

可以在primary進行alter system switch logfile測試，歸檔必須同時出現在兩端，這點很重要，不然standby無法進行恢復。

 

10.standby應用日志

alter database recover managed standby database using current logfile disconnect from session;

 

11.驗證

觀察primary與standby的/u01/arch

primary:alter system switch logfile;

SELECT STATUS,DESTINATION, ERROR FROM V$ARCHIVE_DEST;

select sequence#,status from v$archived_log;--主備一致

 

14.standby可以選擇是否啟動read only with apply

如果啟動可以在standby庫進行查詢，同時從primary日志恢復（此為11g特性，10g如果read only打開standby庫，不可以同時恢復）

alter database recover managed standby database cancel;

alter database open;

alter database recover managed standby database using current logfile disconnect from session;

select open_mode from v$database;

到這裡可以算是完成了，可以正常登錄主庫、備庫進行數據查詢。 

 

15.此時會發現一個現象，v$logfile中的文件可以查詢得到，但是文件並不存在，這種情況可以忽略，主備互轉的時候會自動創建。

SQL> col member for a30
SQL> select * from v$logfile;

    GROUP# STATUS  TYPE    MEMBER                         IS_
---------- ------- ------- ------------------------------ ---
         3         ONLINE  /u01/oradata/good/redo03.log   NO
         2         ONLINE  /u01/oradata/good/redo02.log   NO
         1         ONLINE  /u01/oradata/good/redo01.log   NO
         4         STANDBY /u01/oradata/good/redo04.log   NO
         5         STANDBY /u01/oradata/good/redo05.log   NO
         6         STANDBY /u01/oradata/good/redo06.log   NO
         7         STANDBY /u01/oradata/good/redo07.log   NO

7 rows selected.

 

 參數附錄：

角色

參數名稱

介紹

Pri

DB_NAME

數據庫名稱，primary端和standby端必須相同

DB_UNIQUE_NAME

指定唯一名稱，區別primary端和 standby端

LOG_ARCHIVE_CONFIG

指定DG的全局日志配置，包含所有數據庫的名稱，及歸檔路徑

CONTROL_FILES

控制文件路徑及名稱

LOG_ARCHIVE_DEST_n

指定主備庫的歸檔路徑

LOG_ARCHIVE_DEST_STATE_n

配置是否允許通過redo進行日志傳輸及路徑

REMOTE_LOGIN_PASSWORDFILE

配置遠程登陸模式，是否獨享還是共享

LOG_ARCHIVE_FORMAT

配置歸檔日志文件存儲格式規范

LOG_ARCHIVE_MAX_PROCESS=integer

配置歸檔進程數量，默認為4

FAL_SERVER

配置服務器角色

DB_FILE_NAME_CONVERT

配置數據庫數據文件轉換，用在主備庫數據文件路徑不一致

LOG_FILE_NAME_CONVERT

配置數據庫redo日志文件轉換，用在主備庫redo日志文件路徑不一致

STANDBY_FILE_MANAGEMENT

配置備庫是否同步主庫的表空間添加或數據文件添加。

st

DB_UNIQUE_NAME

指定唯一名稱，區別pri 端和 sty端

CONTROL_FILES

控制文件路徑及名稱

DB_FILE_NAME_CONVERT

配置數據庫數據文件轉換，用在主備庫數據文件路徑不一致

LOG_FILE_NAME_CONVERT

配置數據庫數據文件轉換，用在主備庫數據文件路徑不一致

LOG_ARCHIVE_DEST_n

指定主備庫的歸檔路徑

FAL_SERVER

配置服務器角色