Oracle DG如何搭建

135次閱讀

共計 6738 個字符，預計需要花費 17 分鐘才能閱讀完成。

這篇文章主要為大家展示了“Oracle DG 如何搭建”，內容簡而易懂，條理清晰，希望能夠幫助大家解決疑惑，下面讓丸趣 TV 小編帶領大家一起研究并學習一下“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
備庫：
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 如何搭建”這篇文章的所有內容，感謝各位的閱讀！相信大家都有了一定的了解，希望分享的內容對大家有所幫助，如果還想學習更多知識，歡迎關注丸趣 TV 行業資訊頻道！

正文完