19c ADG報錯Error 1094 attaching to RFS for reconnect

dmcatding發表於2022-12-02

1.DG基礎環境

作業系統版本:redhat7

資料庫版本主庫:19.3  

SID:db19c     

db_name:db19c  

db_unique_name:db19c


資料庫版本備庫:19.3  

SID:db19cdg     

db_name:db19c  

db_unique_name:db19cdg


說明:

紅色db_name主備庫必須保持一致;

綠色 db_unique_name主備庫名稱必須不一致;

資料庫小版本不同,不影響測試,當然建議保持一致,避免生產出現問題拍錯就麻煩了。



2.主庫開啟強制歸檔以及開啟歸檔模,以及修改主庫初始化引數

①開啟強制歸檔

alter database force logging;

select force_logging from v$database;


②初始化引數

-- 主備庫設定一致remote_db_unique_name1 [, ... remote_db_unique_name9) 

ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(db19c,db19cdg)' scope=both sid='*';

-- log_archive_dest_1設定主庫歸檔路徑; DB_UNIQUE_NAME主庫

ALTER SYSTEM SET log_archive_dest_1='LOCATION=/oracle/archivelog VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=db19c' SCOPE=BOTH;

-- log_archive_dest_2中 SERVICE設定連線備庫的tnsnames名稱 db19cdgDB_UNIQUE_NAME備庫資料庫唯一名 db19cdg

ALTER SYSTEM SET log_archive_dest_2='SERVICE=db19cdg LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=db19cdg' SCOPE=BOTH;


-- STANDBY_FILE_MANAGEMENT如果設定為auto,主庫增刪檔案會相應地自動在備庫做出修改(結合convert引數) ;如果設定為manual,當在primary刪除表空間或資料檔案,執行drop tablespace .. including contents and datafiles,standby 只是在控制檔案中將該檔案刪除,還需要手動將物理檔案刪除

alter system set STANDBY_FILE_MANAGEMENT=AUTO scope=both sid='*';


-- 如果備庫設定了db_file_name_convert與log_file_name_convert引數,那麼當資料庫啟動到mount時,就無需手動進行資料檔案重新命名,因為RMAN在恢復控制檔案過程中,會依據該引數設定,自行修改控制檔案中記錄的資料檔案路徑日誌檔案路徑。當主庫切換備庫的時候用到,所以優先寫備庫的絕對路徑!(在ASM自動管理情況下可以使用unique_name,但此處最好填寫全路勁)

alter system set DB_FILE_NAME_CONVERT='/u01/data','/oracle/app/oracle/oradata/DB19C' scope=spfile;

alter system set LOG_FILE_NAME_CONVERT ='/u01/data','/oracle/app/oracle/oradata/DB19C' scope=spfile;


-- 這兩個引數只需在standby庫設定,但也可以在primary庫設定這兩個引數,以方便switchover或failover時primary庫轉變為standby角色。 FAL_CLIENT = StandbyDB,StandbyDB是standby庫的TNS name; FAL_SERVER = PrimaryDB,此處PrimaryDB是一個TNS name,指向primary庫。

alter system set FAL_CLIENT='db19c' scope=both sid='*';

alter system set FAL_SERVER='db19cdg' scope=both sid='*';


③開啟歸檔,開啟歸檔需要重啟資料庫。

3.主庫新增standby redo logfile(連線到CDB$ROOT中執行,備庫需要,如果切換主庫也需要)

新增規則:建立standby日誌組,個數是源日誌個數+1再與例項數的積,size不能小於原來日誌的大小

SQL> select thread#,group#,members,bytes,bytes/1024/1024 from v$log;


   THREAD#     GROUP#    MEMBERS      BYTES BYTES/1024/1024

---------- ---------- ---------- ---------- ---------------

         1          1          1  209715200             200

         1          2          1  209715200             200

         1          3          1  209715200             200


alter database add standby logfile group 4 ('/oracle/app/oracle/oradata/DB19C/redo04.log') size 200M;

alter database add standby logfile group 5 ('/oracle/app/oracle/oradata/DB19C/redo05.log') size 200M;

alter database add standby logfile group 6 ('/oracle/app/oracle/oradata/DB19C/redo06.log') size 200M;

alter database add standby logfile group 7 ('/oracle/app/oracle/oradata/DB19C/redo07.log') size 200M;

SQL> select group#,status,type,member from v$logfile;

    GROUP# STATUS  TYPE    MEMBER

---------- ------- ------- ----------------------------------------------------------------------

         3         ONLINE  /oracle/app/oracle/oradata/DB19C/redo03.log

         2         ONLINE  /oracle/app/oracle/oradata/DB19C/redo02.log

         1         ONLINE  /oracle/app/oracle/oradata/DB19C/redo01.log

         4         STANDBY /oracle/app/oracle/oradata/DB19C/redo04.log

         5         STANDBY /oracle/app/oracle/oradata/DB19C/redo05.log

         6         STANDBY /oracle/app/oracle/oradata/DB19C/redo06.log

         7         STANDBY /oracle/app/oracle/oradata/DB19C/redo07.log


4.主庫和備庫監聽配置以及TNS配置(主備庫tns一樣),保證防火牆關閉

主庫:listener.ora

LISTENER =

  (DESCRIPTION_LIST =

    (DESCRIPTION =

      (ADDRESS_LIST =

        (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.227)(PORT = 1522))

      )

    )

  )


SID_LIST_LISTENER =

  (SID_LIST =

    (SID_DESC =

      (GLOBAL_DBNAME = db19c)

      (SID_NAME = db19c)

      (ORACLE_HOME = /oracle/app/oracle/product/19.3.0/db_1)

    )

  )


主庫:tnsname.ora

DB19C =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = oracle19c)(PORT = 1522))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = db19c)

    )

  )


LISTENER_DB19C =

  (ADDRESS = (PROTOCOL = TCP)(HOST = oracle19c)(PORT = 1522))


DB19CDG =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.225)(PORT = 1522))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = db19cdg)

    )

  )


LISTENER_DB19C =

  (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.225)(PORT = 1522))


備庫:listener.ora

LISTENER =

  (DESCRIPTION_LIST =

    (DESCRIPTION =

      (ADDRESS_LIST =

        (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.225)(PORT = 1522))

      )

    )

  )


SID_LIST_LISTENER =

  (SID_LIST =

    (SID_DESC =

      (GLOBAL_DBNAME = db19cdg)

      (SID_NAME = db19cdg)

      (ORACLE_HOME = /u01/app/oracle/product/19.0.0/dbhome_1)

    )

  )


備庫:tnsname.ora

DB19C =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = oracle19c)(PORT = 1522))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = db19c)

    )

  )

LISTENER_DB19C =

  (ADDRESS = (PROTOCOL = TCP)(HOST = oracle19c)(PORT = 1522))


DB19CDG =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.225)(PORT = 1522))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = db19cdg)

    )

  )


LISTENER_DB19CDG =

  (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.225)(PORT = 1522))





5.主庫建立pfile、standby controlfile以及cpoy建立好的pfile和口令檔案到備庫對應位置

主庫:

alter database create   standby controlfile as '/home/oracle/controlfile';

create pfile= initdb19c.ora from spfile;
cd $ORACLE_HOME/dbs
scp orapwdb19c 192.168.1.225:/u01/app/oracle/product/19.0.0/dbhome_1/dbs

scp initdb19c.ora 192.168.1.225:/u01/app/oracle/product/19.0.0/dbhome_1/dbs


6.備庫修改引數檔案
-----------------------------------------------------------------


*.audit_file_dest='/u01/app/oracle/admin/db19c/adump'
*.audit_trail='db'
*.compatible='19.0.0'
*.control_files='/u01/oradata/db19cdg/control01.ctl','/u01/oradata/db19cdg/control02.ctl'
*.db_block_size=8192
*.db_file_name_convert=' /oracle/app/oracle/oradata/DB19C',' /u01/oradata'
*.db_name='db19c'
*.db_unique_name='db19cdg'
*.diagnostic_dest='/u01/app/oracle'
*.fal_client='db19cdg'
*.fal_server='db19c'
*.log_archive_config='DG_CONFIG=(db19c,db19cdg)'
*.log_archive_dest_1='LOCATION= /oracle/archivelog VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=db19cdg'
*.log_archive_dest_2='SERVICE=db19c LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=db19c'
*.log_file_name_convert= '/oracle/app/oracle/oradata/DB19C','/u01/oradata'

*.standby_file_management='AUTO'


----------------------------------------------------------------------------------

7.備庫用init202009024.ora啟動到nomount
startup pfile=init202009024.ora nomount;


9.檢視備庫資料庫狀態
select open_mode from v$database;
OPEN_MODE
-------------
MOUNTED


在備庫啟動資料庫到恢復管理模式,並開始準備從主庫接受日誌的傳輸:
alter database recover managed standby database using current logfile disconnect from session;

此時備庫,已經可以收到主庫傳過來的日誌啦!


遇到報錯如下:





來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/30327022/viewspace-2926552/,如需轉載,請註明出處,否則將追究法律責任。

相關文章