RMAN遠程複製搭建物理DG過程小結

RMAN遠程複製搭建物理DG過程小結

本文僅記錄搭建的過程，具體詳細的參數意義和配置原理請參考之前的總結 

搭建環境前配置主備庫的tns，確保兩資料庫能正常彼此通訊

primary

確定資料庫開啟強制歸檔

startup mount;

alter database archivelog;

alter database force logging;

alter database open;

修改配置，並匯出pfile，將pfile複製到目標備庫

alter system set db_unique_name=pri scope=spfile;

alter system set log_archive_config = 'DG_CONFIG=(pri,sty)' scope=spfile;

alter system set log_archive_dest_1 = 'LOCATION=/opt/app/Oracle/product/11.2.0/dbhome_1/dbs/arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=pri' scope=spfile;

alter system set log_archive_dest_2 = 'SERVICE=sty LGWR SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=sty' scope=spfile;

alter system set log_archive_dest_state_1 = ENABLE;

alter system set log_archive_dest_state_2 = ENABLE;

alter system set fal_server=sty scope=spfile;

alter system set fal_client=pri scope=spfile;

alter system set standby_file_management=AUTO scope=spfile;

create pfile='/home/oracle/pripfile.ora' from spfile;

standby

 

安裝資料庫軟體，無需安裝資料庫

複製元庫的sys密碼檔案，確保兩庫的密碼一致

scp 192.168.20.46:$ORACLE_HOME/dbs/orapwxtttestdb $ORACLE_HOME/dbs/

複製目標庫匯出的pfile，並添加 *.log_file_name_convert參數選項（10g之後必須添加，即使路徑沒有改變）

 scp 192.168.20.46:/home/oracle/pripfile.ora /home/oracle/

*.log_file_name_convert='/opt/app/oracle/oradata/xtttestdb/','/opt/app/oracle/oradata/xtttestdb/'

建立要恢複備庫的必要目錄

mkdir -p /opt/app/oracle/admin/xtttestdb/adump
 mkdir -p /opt/app/oracle/oradata/xtttestdb
 mkdir -p /opt/app/oracle/product/11.2.0/dbhome_1/dbs/arch

設定SID登入資料庫

export $ORACLE_SID=xtttestdb

sqlplus / as sysdba

利用copy並修改後的pfile建立spfile，並啟動到nomount
 SQL> create spfile from pfile='/home/oracle/pripfile.ora';
 File created.

 SQL> startup nomount;
 ORACLE instance started.
 Total System Global Area 1570009088 bytes
 Fixed Size                  2213696 bytes
 Variable Size            1174407360 bytes
 Database Buffers          385875968 bytes
 Redo Buffers                7512064 bytes

修改備庫的參數配置

alter system set db_unique_name=sty scope=spfile;

alter system set log_archive_config='DG_CONFIG=(pri,dg)' scope=spfile;

alter system set log_archive_dest_1 ='LOCATION=/opt/app/oracle/product/11.2.0/dbhome_1/dbs/arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=sty' scope=spfile;

alter system set log_archive_dest_2 ='SERVICE=pri LGWR SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=pri' scope=spfile;

alter system set fal_server=pri scope=spfile;

alter system set fal_client=sty scope=spfile;

重啟資料庫到nomount，是配置生效（這些配置也可以在pfile中修改完成後再啟動資料庫庫）

SQL> shutdown immediate;

SQL> startup mount;

primary利用rman複製資料庫

rman target sys/[email protected] auxiliary sys/[email protected]
 RMAN> duplicate target database for standby from active database nofilenamecheck;

複製完成後在主備庫天劍standby redo（至少要三組）

alter database add standby logfile

group 4 ('/opt/app/oracle/oradata/xtttestdb/styredo04.log') size 50m,

group 5 ('/opt/app/oracle/oradata/xtttestdb/styredo05.log') size 50m,

group 6 ('/opt/app/oracle/oradata/xtttestdb/styredo06.log') size 50m,

group 7 ('/opt/app/oracle/oradata/xtttestdb/styredo07.log') size 50m;

啟動standby的redo應用的兩種方式

①、預設的物理DG啟動應用後，在主庫arch日誌被完整寫入後才會開始應用該arch log

SQL> alter database recover managed standby database disconnect from session;

②、可以添加current logfile參數，使得應用當前正在讀寫，還沒有完成歸檔的日誌

SQL> alter database recover managed standby database using current logfile disconnect from session;

關閉REDO應用

SQL> alter database recover managed standby database cancel;

查看standby log狀態

select group#,thread#,sequence#,archived,status from v$standby_log;

查看應用日誌情況
select name,creator,sequence#,applied,completion_time from v$archived_log;

驗證：

primary端建立測試表，並添加資料

SQL> select count(*) from test;

  COUNT(*)

----------

        7

SQL> insert into test select * from test;

7 rows created.

SQL> commit;

Commit complete.

SQL> select count(*) from test;

  COUNT(*)

----------

        14

standby端驗證資料是否同步

SQL> select count(*) from test;

  COUNT(*)

----------

        14

搭建過程問題小結：

1、在備庫啟動到nomount後用tns測試連接時發現數無法串連
ORA-12528: TNS:listener: all appropriate instances are blocking new connections

原因是11g之後動態監聽不支援在nomount狀態下遠端tns訪問，自己的伺服器中配置的監聽一直是動態

添加listener.ora 檔案，為standby設定靜態監聽

SID_LIST_LISTENER =

  (SID_LIST =

    (SID_DESC =

      (SID_NAME = PLSExtProc)

      (ORACLE_HOME = /opt/app/oracle/product/11.2.0/dbhome_1)

      (PROGRAM = extproc)

    )

    (SID_DESC =

      (GLOBAL_DBNAME = xtttestdb)

      (ORACLE_HOME = /opt/app/oracle/product/11.2.0/dbhome_1)

      (SID_NAME = xtttestdb)

    )

  )

之後再測試連接正常

2、RMAN遠程複製資料庫完成後有redo的報錯

ORACLE error from auxiliary database: ORA-19527: physical standby redo log must be renamed

ORA-00312: online log 1 thread 1: '/opt/app/oracle/oradata/xtttestdb/redo01.log'

根據錯誤提示，加上網上搜尋一下，原來10g之後的DG即使日誌的原備庫路徑一樣，為了區分開來，還是要設定log_file_name_convert參數，建立備庫的pfile檔案，並添加該參數進去，利用pfile啟動資料庫，問題解決

create pfile='/home/oracle/stypfile.ora' from spfile;

添加

*.log_file_name_convert='/opt/app/oracle/oradata/xtttestdb/','/opt/app/oracle/oradata/xtttestdb/'

creaet spfile from pfile='/home/oracle/stypfile.ora'

startup

--------------------------------------分割線 --------------------------------------

Oracle Data Guard 重要配置參數

基於同一主機配置 Oracle 11g Data Guard

探索Oracle之11g DataGuard

Oracle Data Guard （RAC+DG) 歸檔刪除策略及指令碼

Oracle Data Guard 的角色轉換

Oracle Data Guard的日誌FAL gap問題

Oracle 11g Data Guard Error 16143 Heartbeat failed to connect to standby 處理方法

--------------------------------------分割線 --------------------------------------