ORACLE 11G 搭建dataguard詳細步驟（所有操作總結）

序言：
      DATAGUARD是透過建立一個PRIMARY和STANDBY組來確立其參照關係。
      STANDBY一旦建立，DATAGUARD就會透過將主資料庫(PRIMARY)的REDO傳遞給STANDBY資料庫，然後在STANDBY中應用REDO實現資料庫的同步。

      有兩種型別的STANDBY：物理STANDBY和邏輯STANDBY
      物理STANDBY提供與主資料庫完全一樣的複製（塊到塊），資料庫SCHEMA，包括索引都是一樣的。它是直接應用REDO實現同步的。
      邏輯STANDBY則不是這樣，在邏輯STANDBY中，邏輯資訊是相同的，但物理組織和資料結構可以不同，它和主庫保持同步的方法是將接收的REDO轉換成SQL語句，然後在STANDBY上執行SQL語句。邏輯STANDBY除災難恢復外還有其它用途，比如用於使用者進行查詢和報表。


1、安裝環境
在primary搭建資料庫軟體，建立lsnrctl監聽，採用dbca搭建例項，在standby上搭建資料庫軟體，建立監聽，但是不需要採用dbca建立例項。
如何在linux上搭建oracle資料庫，請參考以前的blog實驗：http://blog.itpub.net/26230597/viewspace-1413242/
作業系統： 都是centos6.4
oracle軟體版本： oracle 11.2.0.1.0
IP地址： primary庫（192.168.121.217）、standby庫（192.168.121.218）
db_unique_name： primary庫（pdunq）、standby庫（pdunq_dg）


2、準備工作 在primary上操作
2.1、開啟Forced Logging 模式
先確認primary庫處於歸檔模式
SQL> archive log list;
Database log mode       Archive Mode
Automatic archival       Enabled
Archive destination       USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     362
Next log sequence to archive   364
Current log sequence       364
SQL> 


強制歸檔
SQL> alter database force logging;
 
Database altered.


SQL>
確認primary庫是歸檔模式


新增standby檔案
alter database add standby logfile group 4 ('/home/oradata/powerdes/redo_dg_021.log') size 20M;
alter database add standby logfile group 5 ('/home/oradata/powerdes/redo_dg_022.log') size 20M;
alter database add standby logfile group 6 ('/home/oradata/powerdes/redo_dg_023.log') size 20M;
alter database drop standby logfile group 4;
alter database drop standby logfile group 5;
alter database drop standby logfile group 6;

select * from v$logfile order by 1;


2.3 準備引數檔案
2.3.1 生成pfile
create pfile from spfile;
shutdown immediate


2.3.2 修改pfile
cp $ORACLE_HOME/dbs/initpowerdes.ora $ORACLE_HOME/dbs/initpowerdes.ora.bak
vim $ORACLE_HOME/dbs/initpowerdes.ora
*.db_unique_name=pdunq
*.diagnostic_dest='/oracle/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=powerdesXDB)'
*.fal_client='pdunq'
*.fal_server='pdunq_dg'
*.standby_file_management='AUTO'
*.db_file_name_convert='/home/oradata/powerdes','/home/oradata/pwerdes'
*.log_file_name_convert='/home/oradata/powerdes','/home/oradata/powerdes'
*.log_archive_config='DG_CONFIG=(pdunq,pdunq_dg)'
*.log_archive_dest_2='SERVICE=pdunq_dg  lgwr sync affirm VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=pdunq_dg'
*.log_archive_dest_state_2='ENABLE'



2.3.3 生成spfile
create spfile from pfile;
startup #這裡可以啟動也可以不啟動，這裡不啟動，後面就要記得startup;讓新的引數檔案生效


2.4 修改監聽檔案
[oracle@powerlong4 admin]$ vim listener.ora 
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /oracle/app/oracle/product/11.2.0/dbhome_1)
      (PROGRAM = extproc)
    )
    (SID_DESC =
      (SID_NAME = powerdes)
      (ORACLE_HOME = /oracle/app/oracle/product/11.2.0/dbhome_1)
    )
  )


LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.121.217)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
    )
  )


ADR_BASE_LISTENER = /oracle/app/oracle
INBOUND_CONNECT_TIMEOUT_listener=10


2.5，修改tns配置檔案
[oracle@powerlong4 admin]$ vim tnsnames.ora 
PD =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.121.217)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = pdunq)
    )
  )


SC_SID =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.121.218)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SID = powerdes)
        (SERVER = DEDICATED)
    )
  )


EXTPROC_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
    )
    (CONNECT_DATA =
      (SID = PLSExtProc)
      (PRESENTATION = RO)
    )
  )


2.6 監聽服務重啟
lsnrctl stop
lsnrctl start


2.7 primary上配置最大可用模式：
SQL>startup
SQL>alter database set standby database to maximize availability;
  
2.8 備份資料庫
backup database plus archivelog;
backup current controlfile for standby;
exit;
備份結束後會在閃回區產生備份檔案


3，資料庫配置 standby上
3.1 建立相應的檔案目錄
包括dump檔案目錄，資料檔案目錄，透過show parameter dest;檢視，保持和primary一樣的路徑地址


3.2 從primary上copy資料檔案到standby上
在主庫上執行：
ps：在primary上執行
copy閃回區內容
copy閃迴檔案
cd /oracle/app/oracle/flash_recovery_area/
scp -r ./* 192.168.121.218:/oracle/app/oracle/flash_recovery_area/


copy引數檔案
cd /oracle/app/oracle/product/11.2.0/dbhome_1/dbs
scp -r ./* 192.168.121.218:/oracle/app/oracle/product/11.2.0/dbhome_1/dbs


copy監聽檔案
cd /oracle/app/oracle/product/11.2.0/dbhome_1/network/admin/
scp -r ./* 192.168.121.218:/oracle/app/oracle/product/11.2.0/dbhome_1/network/admin/


3.3 在standby庫 修改配置檔案 在standby上修改
[oracle@powerlong5 admin]$ vim listener.ora 

# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /oracle/app/oracle/product/11.2.0/dbhome_1)
      (PROGRAM = extproc)
    )
    (SID_DESC =
      (SID_NAME = powerdes)
      (ORACLE_HOME = /oracle/app/oracle/product/11.2.0/dbhome_1)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.121.218)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
    )
  )

在standby修改tns檔案 


3.4，修改引數檔案
*.db_unique_name='pdunq_dg'
*.diagnostic_dest='/oracle/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=powerdes)'
*.fal_client='pdunq'
*.fal_server='pdunq_dg'
*.standby_file_management='AUTO'
*.db_file_name_convert='/home/oradata/powerdes','/home/oradata/powerdes'
*.log_file_name_convert='/home/oradata/powerdes','/home/oradata/powerdes'
*.log_archive_config='DG_CONFIG=(pdunq,pdunq_dg)'
*.log_archive_dest_2='SERVICE=pdunq_dg  lgwr sync affirm VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=pdunq_dg'
*.log_archive_dest_state_2='ENABLE'


PS:將*.log_archive_dest_2=後面的DB_UNIQUE_NAME改成primary的DB_UNIQUE_NAME值改為pdunq，這樣在做switchover的時候，新的primary能透過這個將redo日誌傳到新的standby上面去。
log_archive_dest_N 目的是告訴資料庫，把歸檔放到那裡去可選項，首先是本地，然後考慮遠端的從庫，所以，假設A是主庫，B是從庫，切換之後B是主庫，A是從庫，所以，log_archive_dest_N需要設定為對方


3.5，重啟監聽 standby
[oracle@powerlong5 dbs]$ lsnrctl stop


LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 10-FEB-2015 15:41:36


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


Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.121.218)(PORT=1521)))
The command completed successfully
[oracle@powerlong5 dbs]$ lsnrctl start


LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 10-FEB-2015 15:41:41


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


Starting /oracle/app/oracle/product/11.2.0/dbhome_1/bin/tnslsnr: please wait...


TNSLSNR for Linux: Version 11.2.0.1.0 - Production
System parameter file is /oracle/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Log messages written to /oracle/app/oracle/diag/tnslsnr/powerlong5/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.121.218)(PORT=1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC0)))


Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.121.218)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.1.0 - Production
Start Date                10-FEB-2015 15:41:41
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /oracle/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Listener Log File         /oracle/app/oracle/diag/tnslsnr/powerlong5/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.121.218)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC0)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
  Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "powerdes" has 1 instance(s).
  Instance "powerdes", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
[oracle@powerlong5 dbs]$




3.6，恢復資料庫
在standby庫上操作
[oracle@powerlong5 admin]$ rman target sys/syxxlxxxx58@PD1 auxiliary /


Argument     Value          Description
-----------------------------------------------------------------------------
target       quoted-string  connect-string for target database
catalog      quoted-string  connect-string for recovery catalog
nocatalog    none           if specified, then no recovery catalog
cmdfile      quoted-string  name of input command file
log          quoted-string  name of output message log file
trace        quoted-string  name of output debugging message log file
append       none           if specified, log is opened in append mode
debug        optional-args  activate debugging
msgno        none           show RMAN-nnnn prefix for all messages
send         quoted-string  send a command to the media manager
pipe         string         building block for pipe names
timeout      integer        number of seconds to wait for pipe input
checksyntax  none           check the command file for syntax errors
-----------------------------------------------------------------------------
Both single and double quotes (' or ") are accepted for a quoted-string.
Quotes are not required unless the string contains embedded white-space.


RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00552: syntax error in command line arguments
RMAN-01009: syntax error: found "end-of-file": expecting one of: "double-quoted-string, identifier, single-quoted-string, "
RMAN-01007: at line 0 column 0 file: command line arguments
[oracle@powerlong5 admin]$ 
[oracle@powerlong5 admin]$ 
[oracle@powerlong5 admin]$


報錯，看下是否standby沒有啟動導致？
SQL> startup


ORA-00845: MEMORY_TARGET not supported on this system
SQL> SQL> startup nomount
ORA-00845: MEMORY_TARGET not supported on this system
SQL> 
[root@powerlong5 ~]# mount -t tmpfs shmfs -o size=12g /dev/shm
[root@powerlong5 ~]# df -h
Filesystem            Size  Used Avail Use% Mounted on
/dev/sda3              57G   45G  8.9G  84% /
tmpfs                  12G     0   12G   0% /dev/shm
/dev/sda1             190M   51M  129M  29% /boot
/dev/sr0              4.1G  4.1G     0 100% /media/CentOS_6.4_Final
shmfs                  12G     0   12G   0% /dev/shm
[root@powerlong5 ~]# 




SQL> startup
ORA-09925: Unable to create audit trail file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 9925
SQL> startup nomount
ORA-09925: Unable to create audit trail file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 9925
ORA-09925: Unable to create audit trail file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 9925
SQL> 
去primary庫上查詢下audit路徑
SQL> show parameter audit_file_dest


NAME     TYPE VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest     string /oracle/app/oracle/admin/powerdes/adump
SQL> 


然後在standby上操作
SQL> startup nomount
ORA-01081: cannot start already-running ORACLE - shut it down first
SQL> shutdown
ORA-01507: database not mounted




ORACLE instance shut down.
SQL> startup nomount
ORACLE instance started.


Total System Global Area 1.1358E+10 bytes
Fixed Size    2216744 bytes
Variable Size 8589937880 bytes
Database Buffers 2751463424 bytes
Redo Buffers   13946880 bytes
SQL> 


去primary修改sys密碼：
SQL> alter user sys identified by "syxxlxxxx58";


User altered.


SQL> 


在standby庫執行rman target sys/syspl1758@PD1 auxiliary /，如下所示：



3.7  standby上修改引數檔案
先關閉oracle
shutdown immediate
然後開始修改引數檔案
cd  $ORACLE_HOME/dbs 
vim initpowerdes.ora
# 主要是修改db_unique_name
*.db_unique_name='pdunq_dg'
*.diagnostic_dest='/oracle/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=powerdesXDB)'
*.fal_client='PD1'
*.fal_server='PD2'
*.global_names=FALSE
*.job_queue_processes=1000
*.log_archive_config='DG_CONFIG=(pdunq,pddgunq)'
重新建立引數檔案
create spfile from pfile;


3.8 啟動資料庫
startup nomount;
alter database mount standby database;
alter database add standby logfile;
alter database add standby logfile;
alter database add standby logfile;
alter database recover managed standby database using current logfile disconnect from session;


4，檢檢視到歸檔沒有過來
SQL> archive log list;
Database log mode       Archive Mode
Automatic archival       Enabled
Archive destination       ?/dbs/arch
Oldest online log sequence     0
Next log sequence to archive   0
Current log sequence       0
SQL> 
看到歸檔資訊為0，說明primary上的redo日誌沒有傳到standby上來。


4.1，檢視alert日誌
[oracle@powerlong5 trace]$ tail -f /oracle/app/oracle/diag/rdbms/pdunq_dg/powerdes/trace/alert_powerdes.log 
MRP0 started with pid=41, OS id=21243 
MRP0: Background Managed Standby Recovery process started (powerdes)
 started logmerger process
Sat Feb 07 20:12:18 2015
Managed Standby Recovery not using Real Time Apply
Parallel Media Recovery started with 4 slaves
Waiting for all non-current ORLs to be archived...
All non-current ORLs have been archived.
Media Recovery Waiting for thread 1 sequence 303
Completed: alter database recover managed standby database disconnect from session


 檢視日誌傳輸情況
    select sequence#,first_time,next_time from v$archived_log;
SELECT sequence#,to_char(first_time,'yyyy-mm-dd hh24:mi:ss') first_time,to_char(next_time,'yyyy-mm-dd hh24:mi:ss') next_time from v$archived_log;
primary :

問題分析解決：
primary主庫上的alert日誌有錯：
Error 12154 received logging on to the standby
Errors in file /oracle/app/oracle/diag/rdbms/pdunq/powerdes/trace/powerdes_arc2_22609.trc:
ORA-12154: TNS:could not resolve the connect identifier specified
PING[ARC2]: Heartbeat failed to connect to standby 'pdunq_dg'. Error is 12154.
錯誤很清晰了，主庫無法檢測到從庫存在
tns 12154 錯誤，主庫無法 tnsping pdunq_dg 
tnsping standby庫報錯
[oracle@powerlong4 admin]$ tnsping pdunq_dg


TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 08-FEB-2015 21:42:26


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


Used parameter files:


TNS-03505: Failed to resolve name
[oracle@powerlong4 admin]$


引數檔案裡面
*.log_archive_dest_2='SERVICE=pdunq_dg  lgwr sync affirm VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=pdunq_dg'
SERVICE=pdunq_dg 要和tnsnames.ora裡面的保持一致。



4.2，去tnsnames.ora裡面修改配置
去把tnsnames.ora裡面的改成pdunq_dg即可。
重啟lsnrctl，然後檢視從庫歸檔日誌，有日誌了，如下所示：
SQL> archive log list;
Database log mode       Archive Mode
Automatic archival       Enabled
Archive destination       USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     350
Next log sequence to archive   0
Current log sequence       351
SQL>

備庫切換到open狀態：
退出redo應用狀態
SQL> alter database recover managed standby database cancel;
Database altered.
PS：停止standby的redo應用 ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;注意，此時只是暫時redo 應用，並不是停止Standby 資料庫，standby 仍會保持接收只不過不會再應用接收到的歸檔，直到你再次啟動redo 應用為止。類似mysql裡面的stop slave功能;


開啟standby上的oracle庫
SQL> alter database open;
Database altered. 
再應用redo日誌
SQL> alter database recover managed standby database using current logfile disconnect ; 
Database altered.
SQL>

去primary、standby庫上面執行檢查
SQL> select sequence#,applied from v$archived_log;


檢視最新的scn：
SQL> select max(sequence#) from v$archived_log;


primary和standby都保持一致，OK，dataguard搭建完成。

 ----------------------------------------------------------------------------------------------------------------
<版權所有，文章允許轉載，但必須以連結方式註明源地址，否則追究法律責任!>
原部落格地址：      http://blog.itpub.net/26230597/viewspace-1432637/
原作者：黃杉 (mchdba)
----------------------------------------------------------------------------------------------------------------