共計 6828 個字符，預計需要花費 18 分鐘才能閱讀完成。
這期內容當中丸趣 TV 小編將會給大家帶來有關 Oracle DG 搭建是怎樣的，文章內容豐富且以專業的角度為大家分析和敘述，閱讀完這篇文章希望大家可以有所收獲。
Oracle DG 搭建(冷備方式)
(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 spfileboston.ora.bak
SQL shutdown immediate
SQL create spfile from pfile;
SQL startup
SQL show parameter log_archive_dest_2
(7)主庫：停庫冷備
SQL shutdown immediatre
[oracle@chen admin]$ cd /u01/app/oracle/oradata/chicago
[oracle@chen PROD1]$ tar -zcvf /home/oracle/dg/chicago.tar.gz *
(8)主庫：生成 standby controlfile
SQL startup mount;
SQL alter database create standby controlfile as /home/oracle/dg/standby_control01.ctl
(9) 主庫: 拷貝主庫文件到備庫
冷備文件，監聽文件，TNS 文件，參數文件、密碼文件
[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 PROD1]$ cp chicago.tar.gz /home/oracle/dg
[oracle@chen ~]$ tar -zcvf dg.tar.gz dg/
[oracle@chen ~]$ scp dg.tar.gz jch:/home/oracle
(10) 備庫：配置靜態監聽, 配置 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@edbjr2p2 admin]$ lsnrctl start
(11) 備庫：修改參數文件和口令文件
[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
(12) 備庫：根據據參數文件創建相應的目錄
[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
(13)備庫：解壓備份文件
[oracle@jch dg]$ cd /u01/app/oracle/oradata/boston/
[oracle@jch dg]$ tar -zxvf chicago.tar.gz
(14)備庫：啟動備庫
SQL startup nomount;
[oracle@jch dg]$ export ORACLE_SID=boston
[oracle@jch dg]$ rman target /
rman restore controlfile from /home/oracle/control_standby.ctl
SQL alter database mount;
SQL alter database open;
SQL recover managed standby database using current logfile disconnect from session;
(15)驗證是否搭建成功
主庫：
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;
(16)主從切換
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;
上述就是丸趣 TV 小編為大家分享的 Oracle DG 搭建是怎樣的了，如果剛好有類似的疑惑，不妨參照上述分析進行理解。如果想知道更多相關知識，歡迎關注丸趣 TV 行業資訊頻道。
