oracle DataGuard 主從 踩過坑的

標籤：解壓   ali   bak   OLE   nec   ima   name   dir   工作   

一、主機描述

   dbprimary: 192.168.1.57 主機名稱db1
   dbstandby: 192.168.1.58 主機名稱成db2
   SID: orcl

二、配置tns,配置好的檔案內容：

dbprimary的listener.ora(/db/app/oracle/product/11.2.0/network/admi目錄下):
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = orcl)
      (ORACLE_HOME = /db/app/oracle/product/11.2.0)
      (GLOBAL_DBNAME = orcl)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = db1)(PORT =1521))
    )
  )

dbstandby的listener.ora:
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = orcl)
      (ORACLE_HOME = /db/app/oracle/product/11.2.0)
      (GLOBAL_DBNAME = orcl)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = db2)(PORT =1521))
    )
  )

dbprimary和dbstandby上的tnsnames.ora是一樣的
# Generated by Oracle configuration tools.

DBSTANDBY =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = db2)(PORT =1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = orcl)
    )
  )

DBPRIMARY =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = db1)(PORT =1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = orcl)
    )
  )

 然後在兩台機器上分別檢查配置是否正確
 oracle>lsnrctl stop
 oracle>lsnrctl start
 oracle>lsnrctl status
 oracle>tnsping dbprimary
 oracle>tnsping dbstandby
 oracle>sqlplus sys/[email protected] as sysdba
 oracle>sqlplus sys/[email protected] as sysdba

 

三、在dbprimary上操作
1. oracle> mkdir /db/app/oracle/oradata/archive
2. cd /db/app/oracle/product/11.2.0/dbs

oracle> cp orapworcl orapworcl.bak
oracle> orapwd file=orapw$ORACLE_SID password=oracle entries=30 force=y

產生密碼檔案oraworcl，一定要拷貝到從庫上，就算從庫執行同樣的命令也不行
3. 備份並重新建立pfile檔案

oracle>mv initorcl.ora initorcl.ora.bak

oracle>sqlplus sys/password as sysdba
sql>create pfile from spfile;

在/db/app/oracle/product/11.2.0/dbs下產生initorcl.ora 在initorcl.ora檔案後面增加內容：
*.DB_UNIQUE_NAME=‘dbprimary‘
*.LOG_ARCHIVE_CONFIG=‘DG_CONFIG=(dbprimary, dbstandby)‘
*.LOG_ARCHIVE_DEST_1=‘LOCATION=/db/app/oracle/oradata/archive/VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=dbprimary‘
*.LOG_ARCHIVE_DEST_2=‘SERVICE=dbstandby LGWR ASYNCVALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=dbstandby‘
*.LOG_ARCHIVE_DEST_STATE_1=‘ENABLE‘
*.LOG_ARCHIVE_DEST_STATE_2=‘ENABLE‘
*.LOG_ARCHIVE_FORMAT=‘%t_%s_%r.arc‘
*.LOG_ARCHIVE_MAX_PROCESSES=30
*.FAL_SERVER=‘dbstandby‘
*.FAL_CLIENT=‘dbprimary‘

*.DB_FILE_NAME_CONVERT=‘dbstandby‘,‘dbprimary‘
*.STANDBY_FILE_MANAGEMENT=‘AUTO‘

4. 備份並重新建立spfile檔案

oracle>mvspfileorcl.ora spfileorcl.ora.bak

oracle>sqlplus sys/password as sysdba

sql>create spfile from pfile;
 sql>startup mount;
 sql>alter database archivelog;
 sql>alter database open;
 sql>archive log list; 查看是否處於log模式,如果不是執行如下命令
 sql>alter database force logging; 改變為logging模式
 sql>alter database create standby controlfile as ‘/db/app/oracle/oradata/standby01.ctl‘;建立dbstandby上的控制檔案
 sql>shutdown immediate;
 
5. 把/db/app/oracle/oradata打包傳到dbstandby伺服器, 把 /db/app/oracle/product/11.2.0/dbs/oraworcl也傳過去

四、在dbstandby伺服器上操作
1. oracle>sqlplus sys/password as sysdba
   sql>shutdown immediate;
2. oracle>cd /db/app/oracle
   oracle>mv oradata oradata.bak
   把剛才傳過來的包解壓
   oracle>cd oradata
   oracle>cp standby01.ctl standby02.ctl
   oracle>cp standby01.ctl standby03.ctl
3. 備份並重新建立pfile檔案

oracle>mv initorcl.ora initorcl.ora.bak

sql>create pfile from spfile
 修改initorcl.ora中的*.control_files參數
*.control_files=‘/db/app/oracle/oradata/standby01.ctl‘,‘/db/app/oracle/oradata/standby02.ctl‘,‘/db/app/oracle/oradata/standby03.ctl‘
 增加
*.DB_UNIQUE_NAME=‘dbstandby‘
*.LOG_ARCHIVE_CONFIG=‘DG_CONFIG=(dbprimary, dbstandby)‘
*.LOG_ARCHIVE_DEST_1=‘LOCATION=/db/app/oracle/oradata/archive/VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=dbstandby‘
*.LOG_ARCHIVE_DEST_2=‘SERVICE=dbprimary LGWR ASYNCVALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=dbprimary‘
*.LOG_ARCHIVE_DEST_STATE_1=‘ENABLE‘
*.LOG_ARCHIVE_DEST_STATE_2=‘ENABLE‘
*.LOG_ARCHIVE_FORMAT=‘%t_%s_%r.arc‘
*.LOG_ARCHIVE_MAX_PROCESSES=30
*.FAL_SERVER=‘dbprimary‘
*.FAL_CLIENT=‘dbstandby‘

*.DB_FILE_NAME_CONVERT=‘dbstandby‘,‘dbprimary‘
*.STANDBY_FILE_MANAGEMENT=‘AUTO‘
    
4. 備份並重新建立spfile檔案

oracle>mvspfileorcl.ora spfileorcl.ora.bak

oracle>sqlplus sys/password as sysdba

sql>create spfile from pfile;
sql>shutdown immediate;

到此為止，已經完成了oracle DataGuard主備的所有配置

五、主備配置驗證，及資料庫重啟步驟

（重點）在主庫（db1）伺服器上開啟兩個命令視窗，分別使用sqlplus登入主庫和備庫

登入主庫

sqlplus sys/[email protected] as sysdba

登入備庫

sqlplussys/[email protected] as sysdba

1.關閉主庫

SHUTDOWNIMMEDIATE;

2.關閉備庫

SHUTDOWNIMMEDIATE;

3.啟動備庫到mount

startup mount;

4.啟動主庫

startup;

5.開啟備庫

alter database open;

6.開啟備庫上的同步服務

alter database recover managed standbydatabase disconnect from session;

7．檢查是否工作正常

(備)sql>select max(sequence#) fromv$archived_log;
(主)sql>alter system switch logfile;
(主)sql>select max(sequence#) from v$archived_log; 查看當前sequence
(備)sql>select max(sequence#) from v$archived_log;
如果dbstandby的sequence跟著改變，說明已經好了

8. select dest_name,status,error fromv$archive_dest;  察看相應的歸檔路徑的狀態是否valid，否則根據error資訊進行處理

9. 可以查看alert記錄檔
 /db/app/oracle/diag/rdbms/dbprimary/orcl/alert/log.xml
 /db/app/oracle/diag/rdbms/dbprimary/orcl/trace/alert_orcl.log

 

oracle DataGuard 主從 踩過坑的