Oracle 12C R2 on Linux 7.X Data Guard 搭建文檔

標籤：protocol   沒有   sel   channel   cal   waiting   ike   page   wait   

 

1.查看主機和資料庫資訊 [[email protected] ~]$ sqlplus / as sysdba SQL*Plus: Release 12.2.0.1.0 Production on Tue May 29 01:19:35 2018 Copyright (c) 1982, 2016, Oracle. All rights reserved.  Connected to:Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production SQL> set linesize 300set pagesize 999 select * from v$version;  BANNERCON_ID-------------------------------------------------------------------------------- ----------Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production0PL/SQL Release 12.2.0.1.0 - Production0CORE12.2.0.1.0Production0TNS for Linux: Version 12.2.0.1.0 - Production0NLSRTL Version 12.2.0.1.0 - Production0  SQL> set linesize 140set pagesize 999col name for a25col value for a60select name,value from v$parameter where name like ‘%dump%‘;SQL> SQL> SQL> SQL>  NAMEVALUE------------------------- ------------------------------------------------------------shadow_core_dumppartialbackground_core_dumppartialbackground_dump_dest/data/app/oracle/product/12.2.0.1/db_1/rdbms/loguser_dump_dest/data/app/oracle/product/12.2.0.1/db_1/rdbms/logcore_dump_dest/data/app/oracle/diag/rdbms/orcl/orcl/cdumpmax_dump_file_sizeunlimited 6 rows selected. SQL> col value for a60select name,value from v$parameter where name like ‘%pfile%‘; NAMEVALUE------------------------- ------------------------------------------------------------spfile/data/app/oracle/product/12.2.0.1/db_1/dbs/spfileorcl.ora SQL> col name for a50select name from v$controlfile; NAME--------------------------------------------------/data/oradata/orcl/control01.ctl/data/oradata/orcl/control02.ctl SQL> col member for a50select member from v$logfile; MEMBER--------------------------------------------------/data/oradata/orcl/redo03.log/data/oradata/orcl/redo02.log/data/oradata/orcl/redo01.log  SQL> select name from v$datafile;     NAME--------------------------------------------------/data/oradata/orcl/system01.dbf/data/oradata/orcl/slucex_01.dbf/data/oradata/orcl/sysaux01.dbf/data/oradata/orcl/undotbs01.dbf/data/oradata/orcl/qa_01.dbf/data/oradata/orcl/users01.dbf/data/oradata/orcl/ucex_service_01.dbf/data/oradata/orcl/activity_01.dbf 8 rows selected.  SQL> select name from v$tempfile; NAME--------------------------------------------------/data/oradata/orcl/temp01.dbf SQL> col PARAMETER for a30col VALUE FOR A30select * from nls_database_parameters where parameter=‘NLS_CHARACTERSET‘; PARAMETERVALUE------------------------------ ------------------------------NLS_CHARACTERSETAL32UTF8 $ echo $ORACLE_BASE/data/app/oracle  $ echo $ORACLE_HOME/data/app/oracle/product/12.2.0.1/db_1 2.準備工作實施前需要準備事情：災備端安裝好資料庫軟體，為避免不必要的麻煩，軟體目錄和資料檔案目錄結構與主庫的一致。如果兩端的資料檔案結構不一致，則需要重啟資料庫。修改主備庫/etc/hosts檔案。  3.確定主庫處于歸檔模式 SQL> archive log list;Database log modeArchive ModeAutomatic archivalEnabledArchive destination+DATAOldest online log sequence 2314Next log sequence to archive 2315Current log sequence2315如果處於非歸檔模式需要修改為歸檔模式。  4.修改主庫為強記錄模式SQL> select force_logging from v$database; FOR---NO 經檢查不是強記錄模式，先修改為強記錄模式，命令如下：SQL> alter database force logging;  5.備庫建立相同的目錄結構步驟省略，參考前面輸出內容建立  6.主庫修改參數SQL>alter system set standby_file_management=auto scope=both sid=‘*‘;alter system set fal_server=orcl scope=both sid=‘*‘;alter system set log_archive_config=‘dg_config=(orcl,standby)‘ scope=both sid=‘*‘;alter system set log_archive_dest_1=‘location=/data/arch valid_for=(all_logfiles,all_roles) db_unique_name=orcl‘ sid=‘*‘;    7.配置tnsnames.oraorcl =  (DESCRIPTION =    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.190.60)(PORT = 1521))    (CONNECT_DATA =      (SERVER = DEDICATED)      (SERVICE_NAME = orcl)    )  )  STANDBY =  (DESCRIPTION =    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.190.61)(PORT = 1521))    (CONNECT_DATA =      (SERVER = DEDICATED)      (SERVICE_NAME = standby)    )  )   8.傳送參數檔案和密碼檔案至備庫  9.主庫使用RMAN備份RMAN> run{allocate channel ch1 type disk;allocate channel ch2 type disk;backup database format ‘/data/backup/dbf_%d_%T_%s_%p‘;sql ‘alter system archive log current‘;backup archivelog all format ‘/data/backup/arc_%d_%T_%s_%p‘ delete input;backup current controlfile for standby format ‘/data/backup/control01.ctl‘;} 傳送備份檔案至備庫。  10.啟動備庫至nomount狀態SQL> startup nomount;修改DB_UNIQUE_NAMESQL> ALTER SYSTEM SET DB_UNIQUE_NAME=standby scope=spfile;注意：主備庫兩邊的db_name必須一致，db_unique_name必須不一致。 備庫修改參數alter system set db_file_name_convert=‘/data/oradata/orcl‘,‘/data/oradata/orcl‘ scope=spfile ;alter system set log_file_name_convert=‘/data/oradata/orcl‘,‘/data/oradata/orcl‘ scope=spfile ; 重新啟動，使參數生效：SQL> shutdown immediate;SQL> startup nomount;  11.恢複控制檔案RMAN> restore standby controlfile from ‘/data/backup/control01.ctl‘;  12.啟動備庫至mount狀態RMAN> alter database mount;  13.恢複備庫RMAN> restore database;RMAN> recover database;  14.添加standby logfiles（在主庫和備庫都執行）檢查當前環境logfileSQL> set lines 200 pages 300col member for a60select a.thread#,a.group#,b.member,b.type,a.bytes/1024/1024 MB from v$log a,v$logfile b where a.group#=b.group#union allselect a.thread#,a.group#,b.member,b.type,a.bytes/1024/1024 MB from v$standby_log a,v$logfile b where a.group#=b.group#;    THREAD# GROUP# MEMBERTYPE MB---------- ---------- ------------------------------------------------------------ ------- ----------  11 /data/oradata/orcl/redo01.logONLINE200  13 /data/oradata/orcl/redo03.logONLINE200  12 /data/oradata/orcl/redo02.logONLINE200  SQL> alter database add standby logfile     group 14 ‘/data/oradata/orcl/redo14.log‘ size 200M,     group 15 ‘/data/oradata/orcl/redo15.log‘ size 200M,     group 16 ‘/data/oradata/orcl/redo16.log‘ size 200M,     group 17 ‘/data/oradata/orcl/redo17.log‘ size 200M;  15.在主庫恢複開啟 SQL> alter system set log_archive_dest_2=‘service=standby lgwr async COMPRESSION=ENABLE valid_for=(online_logfiles,primary_role) db_unique_name=standby‘ scope=both;注意:檢查 log_archive_dest_state_1 和 log_archive_dest_state_2 參數是否設定為enable ,如果沒有設定為enable ,切換的時候可能會出問題.  16.在備庫開啟備庫修改參數local_listener 參數修改為備庫的host alter system set log_archive_dest_1=‘location=/data/arch valid_for=(all_logfiles,all_roles) db_unique_name=standby‘ scope=both; alter system set local_listener=‘(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.190.61)(PORT=1521))‘ scope=both; SQL> alter database open read only;SQL> recover managed standby database using current logfile disconnect; 17.檢查是否搭建成功在主庫上切歸檔SQL> alter system archive log current;SQL> alter system archive log current;SQL> archive log list;Database log modeArchive ModeAutomatic archivalEnabledArchive destination/data/archOldest online log sequence 142Next log sequence to archive 143Current log sequence144  備庫查詢能應用 $ orac -dgarc Session altered. SEQUENCE# FIRST_TIMENEXT_TIMEAPPLIED ARCHIVED--------- -------------------- -------------------- ---------- --------      135 2018-07-18 00:00:09 2018-07-18 03:40:02 YES YES      136 2018-07-18 03:40:02 2018-07-18 03:40:03 YES YES      137 2018-07-18 03:40:03 2018-07-18 10:12:50 YES YES      138 2018-07-18 10:12:50 2018-07-18 14:31:00 YES YES      139 2018-07-18 14:31:00 2018-07-18 16:00:59 YES YES      140 2018-07-18 16:00:59 2018-07-18 16:02:42 YES YES      141 2018-07-18 16:02:42 2018-07-18 16:03:27 YES YES      142 2018-07-18 16:03:27 2018-07-18 16:03:31 YES YES      143 2018-07-18 16:03:31 2018-07-18 16:08:01 YES YES      144 2018-07-18 16:08:01 2018-07-18 16:10:03 IN-MEMORY YES              檢查備庫alert日誌，沒有報錯，說明就搭建成功了 $ orac -log db 202018-07-18T16:03:31.294903+08:00Recovery of Online Redo Log: Thread 1 Group 15 Seq 143 Reading mem 0  Mem# 0: /data/oradata/orcl/redo15.log2018-07-18T16:08:01.891964+08:00RFS[1]: Selected log 14 for T-1.S-144 dbid 1507958524 branch 9801777902018-07-18T16:08:01.892027+08:00Archived Log entry 49 added for T-1.S-143 ID 0x59e1d0fc LAD:12018-07-18T16:08:01.974669+08:00Media Recovery Waiting for thread 1 sequence 144 (in transit)2018-07-18T16:08:01.974916+08:00Recovery of Online Redo Log: Thread 1 Group 14 Seq 144 Reading mem 0  Mem# 0: /data/oradata/orcl/redo14.log2018-07-18T16:10:04.097893+08:00RFS[1]: Selected log 15 for T-1.S-145 dbid 1507958524 branch 9801777902018-07-18T16:10:04.097907+08:00Archived Log entry 50 added for T-1.S-144 ID 0x59e1d0fc LAD:12018-07-18T16:10:04.180686+08:00Media Recovery Waiting for thread 1 sequence 145 (in transit)2018-07-18T16:10:04.180958+08:00Recovery of Online Redo Log: Thread 1 Group 15 Seq 145 Reading mem 0  Mem# 0: /data/oradata/orcl/redo15.log     

Oracle 12C R2 on Linux 7.X Data Guard 搭建文檔