ASM 管理的 Oracle 19C 資料庫ADG的搭建及主備切換

-- 主備庫grid及oracle home 目錄

/u01/app/grid/product/19.0.0/gihome_1

/u01/app/orainventory

/u01/app/oracle/product/19.0.0/dbhome_1

-- 主庫開啟強制日志模式、歸檔模式

sql> alter database force logging;  

sql> select force_logging from v$database; 

sql> archive log list; 

sql> shutdown immediate 

sql> startup mount 

sql> alter database archivelog; 

sql> alter database open;  

sql> alter system set sga_max_size=180g scope=spfile;

sql> alter system set sga_target=180g scope=spfile;

sql> alter system set pga_aggregate_target=30g scope=spfile;

-- 主庫查詢添加 stby redo logfile

sql> select group#, members, bytes from v$log; 

sql> select member from v$logfile; 

sql> alter database add standby logfile group 7 ('+data') size 1g;

sql> alter database add standby logfile group 8 ('+data') size 1g;

sql> alter database add standby logfile group 9 ('+data') size 1g;

sql> alter database add standby logfile group 10 ('+data') size 1g;

sql> alter database add standby logfile group 11 ('+data') size 1g;

sql> alter database add standby logfile group 12 ('+data') size 1g;

sql> alter database add standby logfile group 13 ('+data') size 1g;

-- 分别在主備庫配置監聽并啟動,直接使用 netmgr 工具生成 添加如下内容

$ vi listener.ora

-- prmy

sid_list_listener =

 (sid_list =

    (sid_desc =

      (global_dbname = ynsb)

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

      (sid_name = ynsb)

    )

 )

-- stby

      (global_dbname = ynsbsby)

      (sid_name = ynsbsby)

$ lsnrctl reload

-- 主備庫配置 tnsnames.ora 内容如下

$ vi tnsnames.ora 

listener_ynsb =

  (address = (protocol = tcp)(host = sjz)(port = 1521))

ynsb =

  (description =

    (address = (protocol = tcp)(host = sjz)(port = 1521))

    (connect_data =

      (server = dedicated)

      (service_name = ynsb)

  )

ynsbsby =

      (service_name = ynsbsby)

-- 配置好後拷貝到備庫

$ scp $oracle_home/network/admin/ * [email protected]:$oracle_home/network/admin/

-- 在備庫建立必要的目錄,參考主庫的pfile中的路徑：

$ mkdir -p /u01/app/oracle/fast_recovery_area

$ mkdir -p /u01/app/oracle/oradata/... ... 

$ mkdir -p /u01/app/oracle/admin/ynsb/adump

asmcmd> cd +data

asmcmd> mkdir ynsbsby

asmcmd> cd ynsbsby

asmcmd> mkdir datafile

asmcmd> mkdir controlfile

asmcmd> mkdir onlinelog

asmcmd> mkdir parameterfile

asmcmd> mkdir tempfile

-- 主庫建立 pfile 檔案并修改 pfile 如下參數

*.db_name='ynsb'

*.db_unique_name='ynsb'

alter system set standby_file_management='auto';

alter system set log_archive_config='dg_config=(ynsb,ynsbsby)'; -- db_unique_name

alter system set log_archive_dest_1='location=/arch/archive_log/ valid_for=(all_logfiles,all_roles) db_unique_name=ynsb';

alter system set log_archive_dest_2='service=ynsbsby valid_for=(online_logfiles,primary_role) lgwr affirm sync db_unique_name=ynsbsby';

*.log_archive_dest_state_1='enable'

*.log_archive_dest_state_2='enable'

*.log_archive_format=%t_%s_%r.arc

*.remote_login_passwordfile=exclusive

*.archive_lag_target=1800

*.log_archive_max_processes=30

-- tnsname

alter system set fal_server='ynsb';

alter system set fal_client='ynsbsby';

alter system set log_file_name_convert='/arch/archive_log/','/arch/archive_log/' scope=spfile;

alter system set db_file_name_convert='+data/ynsb/datafile/','+data/ynsbsby/datafile/','+data/ynsb/tempfile/','+data/ynsbsby/tempfile/' scope=spfile;

-- 将主庫的密碼檔案及修改後的參數檔案copy到備庫

$ scp /home/oracle/temp.ora [email protected]:/home/oracle/temp.ora

$ scp $oracle_home/dbs/orapwdprmy [email protected]:$oracle_home/dbs/orapwdstby

-- 主庫的參數檔案copy到備庫并修改如下參數

*.db_unique_name='ynsbsby'

*.log_archive_config='dg_config=(ynsb,ynsbsby)'

*.log_archive_dest_1='location=/arch/archive_log/ valid_for=(all_logfiles,all_roles) db_unique_name=ynsbsby'

*.log_archive_dest_2='service=ynsb valid_for=(online_logfiles,primary_role) lgwr affirm sync db_unique_name=ynsb'

*.log_archive_dest_state_1=enable

*.log_archive_dest_state_2=enable

*.standby_file_management='auto'

*.fal_server='ynsb' -- tnsname

*.fal_client='ynsbsby'

-- 用新參數重新開機主資料庫：

sql> create spfile from pfile;

sql> startup 

sql> alter pluggable database pdborcl open;

-- 用 spfile 将備庫啟動到nomount 狀态

sql> startup nomount pfile='/home/oracle/temp.ora'

sql> create spfile='+data/ynsbsby/parameterfile/spfileynsbsby.ora' from pfile='/home/oracle/temp.ora';

-- 添加注冊資料庫到 asm 中

$ srvctl add database -db ynsbsby -o /u01/app/oracle/product/19.0/db_1

$ srvctl modify database -db ynsbsby -role physical_standby -spfile '+data/ynsbsby/parameterfile/spfileynsbsby.ora'

-- 開始進行 active duplicate， 執行完畢後，資料庫自動進入mount狀态

$ rman target sys/oracle123@ynsb auxiliary sys/oracle123@ynsbsby;

rman> duplicate target database for standby from active database;

-- 主庫配置

rman target /

configure archivelog deletion policy to applied on standby;

-- 打開備庫并并啟動 apply

sql> select open_mode from v$database; 

sql> alter database open;

-- 備庫是隻讀的

-- 檢視主備庫 

sql> select log_mode,open_mode ,database_role from v$database;

-- 備庫啟動 real-time apply：

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

sql> select open_mode from v$database;

-- 驗證dg

sql> alter system switch logfile;

-- 主備切換

-- 正常切換：主庫：

sql> select switchover_status from v$database;

-- 備庫：

-- 主切備

sql> alter database commit to switchover to physical standby;

sql> startup nomount;

sql> alter database mount standby database;

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

-- 備切主

sql> alter database commit to switchover to primary;

sql> select status from v$instance;

-- 打開備庫

-- 注：如打開新備庫出現如下錯誤的解決方式：

sql> alter database recover managed standby database cancel;

sql> alter database recover managed stby database using current logfile disconnect ; 

-- 注意：切換後驗證dg

-- 主備啟停

-- 先啟動備庫：

$ lsnrctl start

sql> startup mount;

sql> alter database open read only;

-- 檢視備庫狀态和模式

sql> select name,open_mode,protection_mode,database_role from v$database;

-- 再啟動主庫：

sql> startup

-- 檢視主庫狀态和模式：

-- 在主庫歸檔目前日志：

sql> alter system archive log current;

-- 監控備庫：

-- 檢視stby庫的log_archive_dest_1下應該有archive産生。

-- 查尋v$archived_log，有新的日志記錄出現。最大sequence#，應該為主庫v$log中current狀态日志的序列号減一。

sql>select sequence#,applied,first_time,next_time,resetlogs_change#,completion_time from v$archived_log order by sequence#;

-- 檢視服務是否啟動：

sql> select process,status,client_process,sequence#,block# from v$managed_standby;

-- 檢視是否有遺漏的歸檔日志

sql> select * from v$archive_gap;

-- 關閉 dataguard

-- 先關閉主庫：

sql>shutdown immediate;

-- 再關閉備庫：