Oracle DG如何搭建

這篇文章主要為大家展示了“Oracle DG如何搭建”，內容簡而易懂，條理清晰，希望能夠幫助大家解決疑惑，下面讓小編帶領大家一起研究并學習一下“Oracle DG如何搭建”這篇文章吧。

Oracle DG搭建(duplicate方式)

(1) 主庫：開啟歸檔模式
(2) 主庫：開啟強制寫日志功能
(3) 主庫：關閉閃回
(4) 主庫：配置靜態監聽,配置tnsnames 文件
(5) 主庫：增加standby logfile 文件
(6) 主庫：修改參數文件
(7) 主庫: 拷貝主庫文件到備庫
(8) 備庫：配置靜態監聽,配置tnsnames.ora文件
(9) 備庫：修改參數文件和口令文件
(10) 備庫：根據據參數文件創建相應的目錄
(11) 備庫：startup nomount
(12)主庫: 通過rman duplicate 方式進行備庫恢復
(13)驗證是否搭建成功
(14)主從切換

(1) 主庫：開啟歸檔模式

SQL> shutdown immediate
SQL> startup mount
SQL> alter database archivelog;
SQL> alter database open;
SQL> alter system set db_recovery_file_dest_size=10G;  
SQL> alter system set db_recovery_file_dest='/home/oracle/flash';

(2) 主庫：開啟強制寫日志功能

SQL> select force_logging from v$database;
SQL> alter database force logging;

(3) 主庫：關閉閃回

SQL> select flashback_on from v$database;
SQL> alter database flashback off;

(4) 主庫：配置靜態監聽,配置tnsnames 文件

[oracle@chen ~]$ cd $ORACLE_HOME/network/admin
[oracle@chen admin]$ vi listener.ora
SID_LIST_LISTENER=
  (SID_LIST=
    (SID_DESC=
      (GLOBAL_DBNAME=chicago.us.oracle.com)
      (ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1)
      (SID_NAME=chicago)
     )
   )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = chen.example.com)(PORT = 1521))
    )
  )

[oracle@chen admin]$ vi tnsnames.ora
chicago =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = chen.example.com)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = chicago.us.oracle.com)
    )
  )

boston =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = jch.example.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = boston.us.oracle.com)
    )
  )

[oracle@chen admin]$ lsnrctl stop
[oracle@chen admin]$ lsnrctl start 

(5) 主庫：增加standby logfile 文件

SQL> select member from v$logfile;
SQL> select bytes/1024/1024 from v$log;
SQL> alter database add standby logfile group 4 '/u01/app/oracle/oradata/chicago/standby_redo04.log' size 50M;
SQL> alter database add standby logfile group 5 '/u01/app/oracle/oradata/chicago/standby_redo05.log' size 50M;
SQL> alter database add standby logfile group 6 '/u01/app/oracle/oradata/chicago/standby_redo06.log' size 50M;
SQL> alter database add standby logfile group 7 '/u01/app/oracle/oradata/chicago/standby_redo07.log' size 50M;

(6) 主庫：修改參數文件

https://docs.oracle.com/cd/E11882_01/server.112/e41134/create_ps.htm#SBYDB00426
Example 3-1 Primary Database: Primary Role Initialization Parameters
Example 3-2 Primary Database: Standby Role Initialization Parameters

SQL> create pfile from spfile;
[oracle@chen admin]$ cd $ORACLE_HOME/dbs
[oracle@chen dbs]$ vi initchicago.ora
*.DB_NAME=chicago 
*.DB_UNIQUE_NAME=chicago 
*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(chicago,boston)'
*.LOG_ARCHIVE_DEST_1='LOCATION=/home/oracle/flash VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=chicago'
*.LOG_ARCHIVE_DEST_2='SERVICE=boston ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=boston'
*.LOG_ARCHIVE_DEST_STATE_1=ENABLE
*.LOG_ARCHIVE_DEST_STATE_2=ENABLE
*.REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
*.LOG_ARCHIVE_FORMAT=%t_%s_%r.arc

*.FAL_SERVER=boston
*.DB_FILE_NAME_CONVERT='boston','chicago'
*.LOG_FILE_NAME_CONVERT='boston','chicago' 
*.STANDBY_FILE_MANAGEMENT=AUTO

[oracle@chen dbs]$ cp spfilechicago.ora spfilechicago.ora.bak
SQL> shutdown immediate
SQL> create spfile from pfile;
SQL> startup
SQL> show parameter log_archive_dest_2

(7) 主庫: 拷貝主庫文件到備庫

監聽文件，TNS文件，參數文件、密碼文件到備庫并改名
[oracle@chen dbs]$ mkdir /home/oracle/dg
[oracle@chen dbs]$ cp initchicago.ora /home/oracle/dg/
[oracle@chen dbs]$ cp orapwchicago /home/oracle/dg/
[oracle@chen admin]$ cp listener.ora /home/oracle/dg/
[oracle@chen admin]$ cp tnsnames.ora /home/oracle/dg/
[oracle@chen ~]$ tar -zcvf dg.tar.gz dg/
[oracle@chen ~]$ scp dg.tar.gz jch:/home/oracle

(8) 備庫：配置靜態監聽,配置tnsnames.ora文件

[oracle@jch ~]$ tar -zxvf dg.tar.gz
[oracle@jch ~]$ cd $ORACLE_HOME/network/admin
[oracle@jch admin]$ mv listener.ora listener.ora.bak
[oracle@jch admin]$ mv tnsnames.ora tnsnames.ora.bak
[oracle@jch admin]$ cp /home/oracle/dg/listener.ora .
[oracle@jch admin]$ cp /home/oracle/dg/tnsnames.ora .
[oracle@jch admin]$ vi listener.ora
SID_LIST_LISTENER=
  (SID_LIST=
    (SID_DESC=
      (GLOBAL_DBNAME=boston.us.oracle.com)
      (ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1)
      (SID_NAME=boston)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = jch.example.com)(PORT = 1521))
    )
  )

[oracle@jch admin]$ lsnrctl start

(9) 備庫：修改參數文件和口令文件
[oracle@jch dbs]$ cp /home/oracle/dg/initchicago.ora .
[oracle@jch dbs]$ cp /home/oracle/dg/orapwchicago .
[oracle@jch dbs]$ mv orapwchicago orapwboston
[oracle@jch dbs]$ mv initchicago.ora initboston.ora

[oracle@jch dbs]$ vi initboston.ora
:%s/chicago/AAAA/g 
:%s/boston/chicago/g 
:%s/AAAA/boston/g

*.DB_NAME=chicago
*.DB_UNIQUE_NAME=boston
*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(boston,chicago)'
*.LOG_ARCHIVE_DEST_1='LOCATION=/home/oracle/flash VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=boston'
*.LOG_ARCHIVE_DEST_2='SERVICE=chicago ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=chicago'
*.LOG_ARCHIVE_DEST_STATE_1=ENABLE
*.LOG_ARCHIVE_DEST_STATE_2=ENABLE
*.REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
*.LOG_ARCHIVE_FORMAT=%t_%s_%r.arc

*.FAL_SERVER=chicago
*.DB_FILE_NAME_CONVERT='chicago','boston'
*.LOG_FILE_NAME_CONVERT='chicago','boston'
*.STANDBY_FILE_MANAGEMENT=AUTO

(10) 備庫：根據據參數文件創建相應的目錄
[oracle@jch ~]$ mkdir flash
[oracle@jch ~]$ cd /u01/app/oracle/admin/
[oracle@jch admin]$ mkdir boston/adump -p
[oracle@jch ~]$ mkdir /u01/app/oracle/oradata/boston

(11) 備庫：startup nomount
[oracle@jch ~]$ export ORACLE_SID=boston
[oracle@jch ~]$ sqlplus / as sysdba
SQL> create spfile from pfile;
SQL> startup nomount

(12)主庫: 通過rman duplicate 方式進行備庫恢復
[oracle@chen ~]$ export ORACLE_SID=chicago
[oracle@chen ~]$ rman target / auxiliary sys/oracle@boston
RMAN> duplicate target database for standby from active database;

(13)驗證是否搭建成功
主庫：
SQL> archive log list;
SQL> alter system switch logfile;
備庫： 
SQL> archive log list;
SQL> select process, pid, status, client_process from v$managed_standby;
SQL> SELECT PROTECTION_MODE, PROTECTION_LEVEL,DATABASE_ROLE ROLE,SWITCHOVER_STATUS FROM V$DATABASE;
備庫：sync 數據
SQL> alter database open;
SQL> recover managed standby database using current logfile disconnect from session;
-----SQL> recover managed standby database cancel;
主庫： 
SQL> create table test1 as select level as id from dual connect by level<=3; 
備庫： 
SQL> select * from test1;

(14)主從切換
https://docs.oracle.com/cd/E11882_01/server.112/e41134/role_management.htm#SBYDB00625
主庫： 
SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH SESSION SHUTDOWN;
SQL> SHUTDOWN ABORT;
SQL> STARTUP MOUNT;
備庫： 
SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN;
SQL> ALTER DATABASE OPEN;
---SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;

當主庫故障時，通過上面的方式主從切換回報錯如下：
Database not available for switchover
  End-Of-REDO archived log file has not been recovered
  Archived log files detected beyond End-Of-REDO
  Incomplete recovery SCN:0:1038219 archive SCN:0:1037990

解決方案：
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH; 
---ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH FORCE; 
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN;
SQL> ALTER DATABASE OPEN;

以上是“Oracle DG如何搭建”這篇文章的所有內容，感謝各位的閱讀！相信大家都有了一定的了解，希望分享的內容對大家有所幫助，如果還想學習更多知識，歡迎關注億速云行業資訊頻道！