ORACLE 11gR2 11.2.0.4 一步一步 物理DG

應朋友的需求，今天搭建物理DG同時寫下文件：

環境介紹：
系統   : RedHat6.3_x64
Oracle：11.2.0.4

這裡的做物理DG前提是你已經安裝好了Oracle軟體，同時DBCA建立好了你的主庫。

我是在同一臺機器裡搭建DG,所以目錄跟主庫的不一樣，如果是雙機做，目錄就可以一模一樣，這樣更方便。

主庫名：prod
備庫名：emrep
搭建物理dg大致一下幾步：
1、建主庫
2、修改主庫和備庫引數檔案
3、恢復備庫
4、啟用歸檔日誌應用
一、首先是建立listener和tnsname.因為DG是走監聽的。
listener.ora 為主庫的監聽
listener1.ora為備庫的監聽。
我這裡分別以例項名命名tnsname.ora裡面的。

注意：tnsname.ora如果是雙機做DG，tnsname.ora就要填寫對方的例項。
假如我的是雙機。即相互連線。 主庫prod的tnsname.ora裡面是指向備庫emrep。備庫的tnsname.ora 裡面指向的是主庫prod。
如果不明白，剛接觸的人，把兩個都加上去，以免出錯。

tnsname.ora 檔案內容：

主庫prod操作：
SQL> create pfile from spfile; ---》由於是dbca建立的主庫，這裡要手動產生PFILE

接下來，修改主庫的歸檔路徑，注意修改為歸檔，需在mount階段做，dbca建立完成例項後，預設是open；

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 1.1257E+10 bytes
Fixed Size                  2263336 bytes
Variable Size            5939136216 bytes
Database Buffers         5301600256 bytes
Redo Buffers               14352384 bytes
Database mounted.
SQL> alter system set log_archive_dest_1='location=/u01/app/oradata/prod/archivelog' ;

System altered.

SQL> alter database archivelog;

Database altered.

SQL> alter database force logging; ---》強制記錄日誌

Database altered.

預先看一下資料庫的三檔案的位置

 

修改主庫日誌檔案組的大小和成員，這裡你也可以給每個組新增日誌成員，並修改日誌成員大小。

提示：在刪除redo log時，要先檢視日誌的狀態。之後status 是inavtive 和unused才可以被刪除，CURRENT 表示當前正在使用，既然在使用肯定不能刪除啊，
ACTIVE 表示活動狀態，切換日誌後，雖然不是當前使用的，但是由於日誌不可能立即重新整理到磁碟，所以這是日誌還在被使用狀態。

新增standby logfile
standby logfile 官方給出的standby logfile要比redo log 至少多一組，成員大小一樣。
提示：關於standby log 的存放，其實跟redo log的存放類似，要考慮到冗餘，分散I/O。並以方便檢視和管理 為原則。

SQL> ALTER DATABASE ADD STANDBY LOGFILE
group 4 ('/u01/app/oradata/standbylog/redo04a.log','/u01/app/oradata/standbylog/redo04b.log')  size 50M,
group 5 ('/u01/app/oradata/standbylog/redo05a.log','/u01/app/oradata/standbylog/redo05b.log')  size 50M,
group 6 ('/u01/app/oradata/standbylog/redo06a.log','/u01/app/oradata/standbylog/redo06b.log')  size 50M,
group 7 ('/u01/app/oradata/standbylog/redo07a.log','/u01/app/oradata/standbylog/redo07b.log')  size 50M;

SQL> select member from v$logfile;

MEMBER
--------------------------------------------------------------------------------
/u01/app/oradata/prod/prod/redo03a.log
/u01/app/oradata/prod/prod/redo02a.log
/u01/app/oradata/prod/prod/redo01a.log
/u01/app/oradata/prod/prod/redo01b.log
/u01/app/oradata/prod/prod/redo03b.log
/u01/app/oradata/prod/prod/redo02b.log
/u01/app/oradata/standbylog/redo04a.log
/u01/app/oradata/standbylog/redo04b.log
/u01/app/oradata/standbylog/redo05a.log
/u01/app/oradata/standbylog/redo05b.log
/u01/app/oradata/standbylog/redo06a.log

MEMBER
--------------------------------------------------------------------------------
/u01/app/oradata/standbylog/redo06b.log
/u01/app/oradata/standbylog/redo07a.log
/u01/app/oradata/standbylog/redo07b.log

14 rows selected.

SQL>

修改init檔案

這是修改後的主庫prod ：
[oracle@newbidb dbs]$ cat initprod.ora
prod.__db_cache_size=5301600256
prod.__java_pool_size=201326592
prod.__large_pool_size=134217728
prod.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
prod.__pga_aggregate_target=4529848320
prod.__sga_target=6777995264
prod.__shared_io_pool_size=0
prod.__shared_pool_size=1073741824
prod.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/prod/adump'
*.audit_trail='db'
*.compatible='11.2.0.4.0'
*.control_files='/u01/app/oradata/prod/prod/control01.ctl','/u01/app/oradata/prod/prod/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='prod'
*.diagnostic_dest='/u01/app/oracle'
#*.dispatchers='(PROTOCOL=TCP) (SERVICE=prodXDB)'

LOG_ARCHIVE_CONFIG='DG_CONFIG=(prod,emrep)'
log_archive_dest_1='location=/u01/app/oradata/prod/archivelog   --主庫的歸檔路徑
        VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
        DB_UNIQUE_NAME=prod'
DB_UNIQUE_NAME=prod
DB_FILE_NAME_CONVERT='/u01/app/oradata/emrep/emrep/','/u01/app/oradata/prod/prod/'      --主備庫資料檔案轉換  巧記 先別人 後自己，先備後主
LOG_FILE_NAME_CONVERT='/u01/app/oradata/emrep/emrep/','/u01/app/oradata/prod/prod/'    --主備庫日誌檔案轉換
log_archive_dest_2='SERVICE=emrep  ASYNC                            
        valid_for=(online_logfiles,primary_role)                                     --備庫的歸檔路徑
        db_unique_name=emrep'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
FAL_SERVER=emrep                       -- failed switch
fal_client=prod

*.memory_target=11298406400
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.standby_file_management='AUTO'
*.undo_tablespace='UNDOTBS1'
[oracle@newbidb dbs]$
---------------------------分割線----------

cp 主庫的pfile檔案生成備庫的pfile檔案
[oracle@newbidb dbs]$ cp initprod.ora initemrep.ora

這是修改後的備庫emrep ：
[oracle@newbidb dbs]$ cat initemrep.ora
prod.__db_cache_size=5301600256
prod.__java_pool_size=201326592
prod.__large_pool_size=134217728
prod.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
prod.__pga_aggregate_target=4529848320
prod.__sga_target=6777995264
prod.__shared_io_pool_size=0
prod.__shared_pool_size=1073741824
prod.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/emrep/adump'
*.audit_trail='db'
*.compatible='11.2.0.4.0'
control_files='/u01/app/oradata/emrep/emrep/control01.ctl','/u01/app/oradata/emrep/emrep/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='prod'
*.diagnostic_dest='/u01/app/oracle'
#*.dispatchers='(PROTOCOL=TCP) (SERVICE=prodXDB)'

db_unique_name=emrep
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
LOG_ARCHIVE_CONFIG='DG_CONFIG=(prod,emrep)'
log_archive_dest_1='location=/u01/app/oradata/emrep/archivelog  VALID_FOR=(ALL_LOGFILES,ALL_ROLES)   DB_UNIQUE_NAME=emrep'
log_archive_dest_2='SERVICE=prod valid_for=(online_logfiles,primary_role) db_unique_name=prod'
DB_FILE_NAME_CONVERT='/u01/app/oradata/prod/prod/','/u01/app/oradata/emrep/emrep/'
LOG_FILE_NAME_CONVERT='/u01/app/oradata/prod/prod/','/u01/app/oradata/emrep/emrep/'
FAL_SERVER=prod
fal_client=emrep

*.memory_target=11298406400
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.standby_file_management='AUTO'
*.undo_tablespace='UNDOTBS1'
[oracle@newbidb dbs]$

cp 主庫的密碼檔案生成備庫的密碼檔案
[oracle@zhanglin dbs]$ cp orapwprod orapwemrep

備庫emrep操作：

登入到備庫，用initemrep.ora 開啟備庫到nomount階段
[oracle@zhanglin dbs]$ export ORACLE_SID=emrep
[oracle@zhanglin dbs]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Mon Sep 29 12:21:51 2014

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup nomount pfile=/u01/app/oracle/product/11.2.0.4/db_1/dbs/initemrep.ora
ORACLE instance started.

Total System Global Area 1.1257E+10 bytes
Fixed Size                  2263336 bytes
Variable Size            5872027352 bytes
Database Buffers         5368709120 bytes
Redo Buffers               14352384 bytes
SQL> ！

rman登入同時使用11g新特性duplicate  線上恢復restore 備庫
[oracle@zhanglin u01]$ rman target auxiliary

Recovery Manager: Release 11.2.0.4.0 - Production on Mon Sep 29 12:25:36 2014

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: PROD (DBID=271384494, not open)
connected to auxiliary database: PROD (not mounted)

RMAN> duplicate target database for standby from active database;
由於篇幅較大，這裡省略

rman之後例項emrep預設是mount階段：
SQL> select status from v$instance ;

STATUS
------------
MOUNTED

檢視例項emrep的檔案路徑，如果轉換成功，則引數設定正確。
SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u01/app/oradata/emrep/emrep/system01.dbf
/u01/app/oradata/emrep/emrep/sysaux01.dbf
/u01/app/oradata/emrep/emrep/undotbs01.dbf
/u01/app/oradata/emrep/emrep/users01.dbf

SQL>  select name from v$controlfile;

NAME
--------------------------------------------------------------------------------
/u01/app/oradata/emrep/emrep/control01.ctl
/u01/app/oradata/emrep/emrep/control02.ctl

SQL>  select member from v$logfile;

MEMBER
--------------------------------------------------------------------------------
/u01/app/oradata/emrep/emrep/redo03a.log
/u01/app/oradata/emrep/emrep/redo02a.log
/u01/app/oradata/emrep/emrep/redo01a.log
/u01/app/oradata/emrep/emrep/redo01b.log
/u01/app/oradata/emrep/emrep/redo03b.log
/u01/app/oradata/emrep/emrep/redo02b.log
/u01/app/oradata/standbylog/redo04a.log
/u01/app/oradata/standbylog/redo04b.log
/u01/app/oradata/standbylog/redo05a.log
/u01/app/oradata/standbylog/redo05b.log
/u01/app/oradata/standbylog/redo06a.log

MEMBER
--------------------------------------------------------------------------------
/u01/app/oradata/standbylog/redo06b.log
/u01/app/oradata/standbylog/redo07a.log
/u01/app/oradata/standbylog/redo07b.log

備庫開啟日誌實時應用
SQL> alter database recover managed standby database using current logfile disconnect from session;

Database altered.

SQL>

檢視備庫emrep當前的歸檔日誌號
SQL>  archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u01/app/oradata/emrep/archivelog
Oldest online log sequence     8
Next log sequence to archive   0
Current log sequence           10
SQL>

備庫alert.log
Primary database is in MAXIMUM PERFORMANCE mode
RFS[4]: Assigned to RFS process 10783
RFS[4]: Selected log 4 for thread 1 sequence 9 dbid 271384494 branch 859556145
Media Recovery Log /u01/app/oradata/emrep/archivelog/1_8_859556145.dbf
Media Recovery Waiting for thread 1 sequence 9 (in transit)
Recovery of Online Redo Log: Thread 1 Group 4 Seq 9 Reading mem 0
  Mem# 0: /u01/app/oradata/standbylog/redo04a.log
  Mem# 1: /u01/app/oradata/standbylog/redo04b.log
Mon Sep 29 13:56:02 2014
RFS[4]: No standby redo logfiles available for thread 1
Mon Sep 29 13:56:02 2014
Archived Log entry 7 added for thread 1 sequence 9 ID 0x102cffa7 dest 1:
RFS[4]: Opened log for thread 1 sequence 10 dbid 271384494 branch 859556145
Mon Sep 29 13:56:02 2014
Media Recovery Waiting for thread 1 sequence 10 (in transit)

 

主庫：
SQL>   archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u01/app/oradata/prod/archivelog
Oldest online log sequence     8
Next log sequence to archive   10
Current log sequence           10
SQL> 建立一個表空間測試，是否可以傳遞到備庫emrep中：

SQL> create tablespace test datafile '/u01/app/oradata/prod/prod/test.dbf' size 10m;

Tablespace created.

SQL> alter system switch logfile;

System altered.

在備庫上檢視
SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u01/app/oradata/emrep/emrep/system01.dbf
/u01/app/oradata/emrep/emrep/sysaux01.dbf
/u01/app/oradata/emrep/emrep/undotbs01.dbf
/u01/app/oradata/emrep/emrep/users01.dbf
/u01/app/oradata/emrep/emrep/test.dbf

5 rows selected.

SQL>
說明歸檔日誌成功傳遞，併成功被備庫應用。
至此物理DG搭建成功，後面可以做主備切換：