Oracle 12C DG 搭建(RAC-RAC/RAC-單機）

2019-11-09 13:33:27

字體：大中小

來源：轉載

供稿：網友

主庫上操作1.開啟RAC的 force loggingSQL> alter database force logging;SQL> 2.修改RAC初始化參數文件SQL> alter system set log_archive_config='DG_CONFIG=(eisoo,eisoos)';SQL> alter system set log_archive_dest_2='SERVICE=eisoos ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=eisoos' scope=spfile;SQL> alter system set log_archive_dest_state_1=ENABLE;SQL> alter system set log_archive_dest_state_2=ENABLE;SQL> alter system set fal_server=eisoos;SQL> alter system set db_file_name_convert='eisoos','eisoo' scope=spfile;SQL> alter system set db_file_name_convert='/data/oradata/eisoos','+DATA/EISOO/DATAFILE' scope=spfile; //如果備庫沒有使用asmSQL> alter system set log_file_name_convert='eisoos','eisoo' scope=spfile;SQL> alter system set log_file_name_convert='/data/oradata/eisoos','+DATA/EISOO/ONLINELOG' scope=spfile;//如果備庫沒有使用asmSQL> alter system set standby_file_management='AUTO';SQL> alter system set log_archive_max_processes=30;3.開啟歸檔模式srvctl stop database -d eisoosrvctl start database -d eisoo-i eisoo -o mountSQL>alter database archivelog;SQL>alter database open;4.創建standby logfile；SQL> select thread#,group#,bytes/1024/1024 from v$log; THREAD# GROUP# BYTES/1024/1024---------- ---------- --------------- 1 1 50 1 2 50 2 3 50 2 4 50SQL> alter database add standby logfile thread 1 group 10 size 50M;SQL> alter database add standby logfile thread 1 group 11 size 50M;SQL> alter database add standby logfile thread 1 group 12 size 50M;SQL> alter database add standby logfile thread 2 group 13 size 50M;SQL> alter database add standby logfile thread 2 group 14 size 50M;SQL> alter database add standby logfile thread 2 group 15 size 50M;SQL> select thread#,group#,bytes/1024/1024 from v$standby_log; THREAD# GROUP# BYTES/1024/1024---------- ---------- --------------- 1 10 50 1 11 50 1 12 50 2 13 50 2 14 50 2 15 505.創建備庫參數文件SQL> show parameter spfile;NAME TYPE VALUE------------------------------------ ----------- ------------------------------spfile string +DATA/EISOO/PARAMETERFILE/spfile.281.923255053SQL> create pfile='/tmp/initeisoos.ora' from spfile='+DATA/EISOO/PARAMETERFILE/spfile.281.923255053';[Oracle@rac1 tmp]$ scp initeisoos.ora 192.168.180.48:$ORACLE_HOME/dbs備庫上操作：1.修改參數文件使用asm：*.audit_file_dest='/u01/app/oracle/admin/eisoos/adump'*.audit_trail='db'*.compatible='12.1.0.2.0'*.control_files='/data/oradata/eisoos/control01.ctl'*.db_block_size=8192*.db_create_file_dest='/data/oradata/eisoos'*.db_domain=''*.db_file_name_convert='eisoo','eisoos'*.db_name='eisoo'*.db_unique_name='eisoos'*.diagnostic_dest='/u01/app/oracle'*.dispatchers='(PROTOCOL=TCP) (SERVICE=eisoosXDB)'*.fal_server='EISOOS'*.log_archive_config='DG_CONFIG=(eisoo,eisoos)'*.log_archive_dest_1='LOCATION=/data/oradata/eisoos/archivelog'*.log_archive_dest_2='SERVICE=eisoo ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=eisoo'*.log_archive_dest_state_1='ENABLE'*.log_archive_dest_state_2='ENABLE'*.log_archive_format='%t_%s_%r.arc'*.log_archive_max_processes=30*.log_file_name_convert='eisoo','eisoos'*.open_cursors=300*.pga_aggregate_target=453m*.processes=300*.remote_login_passWordfile='exclusive'*.sga_target=1361m*.standby_file_management='AUTO'eisoos.undo_tablespace='UNDOTBS1'單機未使用asm：*.audit_file_dest='/u01/app/oracle/admin/eisoos/adump'*.audit_trail='db'*.compatible='12.1.0.2.0'*.control_files='/data/oradata/eisoos/control01.ctl'#Restore Controlfile*.db_block_size=8192*.db_create_file_dest='/data/oradata/eisoos'*.db_domain=''*.db_file_name_convert='+DATA/EISOO/DATAFILE','/data/oradata/eisoos','+DATA/EISOO/TEMPFILE','/data/oradata/eisoos'*.db_name='eisoo'*.db_unique_name='eisoos'*.diagnostic_dest='/u01/app/oracle'*.dispatchers='(PROTOCOL=TCP) (SERVICE=eisoosXDB)'*.fal_server='EISOOS'*.log_archive_config='DG_CONFIG=(eisoo,eisoos)'*.log_archive_dest_1='LOCATION=/data/oradata/eisoos/archivelog'*.log_archive_dest_2='SERVICE=eisoo ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=eisoo'*.log_archive_dest_state_1='ENABLE'*.log_archive_dest_state_2='ENABLE'*.log_archive_format='%t_%s_%r.arc'*.log_archive_max_processes=30*.log_file_name_convert='+DATA/EISOO/ONLINELOG','/data/oradata/eisoos'*.open_cursors=300*.pga_aggregate_target=453m*.processes=300*.remote_login_passwordfile='exclusive'*.sga_target=1361m*.standby_file_management='AUTO'eisoos.undo_tablespace='UNDOTBS1'2.啟動到 nomout狀態SQL> startup nomount pfile='/u01/app/oracle/product/12.1.0/db_1/dbs/initeisoos.ora';ORACLE instance started.Total System Global Area 1442840576 bytesFixed Size 2924448 bytesVariable Size 486539360 bytesDatabase Buffers 939524096 bytesRedo Buffers 13852672 bytesSQL> SQL>ALTER SYSTEM SET SEC_CASE_SENSITIVE_LOGON=false;備注：SEC_CASE_SENSITIVE_LOGON參數是決定密碼文件是否可以在本地創建，否則只能從主庫拷貝到備庫。默認值是“true”,3.在本地創建密碼文件ocrl:/u01/app/oracle/product/12.1.0/db_1/dbs@oracle1>orapwd file=orapweisoos password=oracle entries=10 ignorecase=y force=y4.配置監聽文件，保證primary和standby能夠互連備庫:LISTENER = (DESCRipTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.180.41)(PORT = 1521)) (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521)) ) )SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = eisoos) (ORACLE_HOME = /u01/app/oracle/product/12.1.0/db_1) (SID_NAME = eisoos) ) )或者SID_LIST_LISTENER_EISOOS = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = eisoos) (ORACLE_HOME = /u01/app/oracle/product/12.1.0/db_1) (SID_NAME = eisoos) ) )主庫和備庫是tnsname.ora 配置如下：eisoo = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.180.51)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = eisoo) ) )eisoos = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.180.41)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = eisoos) ) )主備分別驗證：[oracle@rac1 ~]$ sqlplus sys/oracle@eisoo as sysdba[oracle@rac1 ~]$ sqlplus sys/oracle@eisoos as sysdba[oracle@rac2 ~]$ sqlplus sys/oracle@eisoo as sysdba[oracle@rac2 ~]$ sqlplus sys/oracle@eisoos as sysdba5.備份恢復數據eisoos:/home/oracle@oracle1>rman target sys/oracle@eisoo auxiliary sys/oracle@eisoosRecovery Manager: Release 12.1.0.2.0 - Production on Mon Sep 26 16:50:42 2016Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.connected to target database: EISOO (DBID=3774196505)connected to auxiliary database: EISOO (not mounted)RMAN> duplicate target database for standby from active database;Starting Duplicate Db at 2016/09/26 16:52:126.開啟實時同步SQL> alter database recover managed standby database using current logfile disconnect from session;驗證:1）SQL> select dest_name,error from v$archive_dest;//通過查看archive_log_dest_2列是否有error報錯，如果有報錯，則需要先根據報錯內容解決問題2）查詢主庫最大歸檔序號和備庫最大歸檔序號select max(sequence#) from v$archived_log;然后在主庫切換日志：alter system switch logfile;再次查詢備庫最大歸檔序號，一致即歸檔同步成功。主庫：SQL> select max(sequence#) from v$archived_log;MAX(SEQUENCE#)-------------- 133SQL> alter system switch logfile;System altered.SQL> select max(sequence#) from v$archived_log;MAX(SEQUENCE#)-------------- 134SQL> 備庫：SQL> select max(sequence#) from v$archived_log;MAX(SEQUENCE#)-------------- 134SQL> 3) 主庫驗證SQL>select SEQUENCE#, FIRST_TIME, NEXT_TIME, APPLIED, ARCHIVED from V$ARCHIVED_LOG；備庫驗證SQL>select SEQUENCE#, FIRST_TIME, NEXT_TIME, APPLIED, ARCHIVED from V$ARCHIVED_LOG；備注：觀察主備庫日志是否同步，如一致則表示日志CDP同步正常。主備切換1.檢查DG是否同步是否正常主庫： SQL> select switchover_status,database_role from gv$database; SWITCHOVER_STATUS DATABASE_ROLE-------------------- ----------------TO STANDBY PRIMARYTO STANDBY PRIMARY備庫:SQL> select switchover_status,database_role from gv$database; SWITCHOVER_STATUS DATABASE_ROLE-------------------- ----------------NOT ALLOWED PHYSICAL STANDBY2.準備切換工作:關閉RAC庫，并把rac1起到open 狀態[oracle@rac1 ~]$ srvctl stop database -d eisoo[oracle@rac1 ~]$ sqlplus / as sysdbaSQL*Plus: Release 12.1.0.2.0 Production on Tue Sep 27 09:12:25 2016Copyright (c) 1982, 2014, Oracle. All rights reserved.Connected to an idle instance.SQL> startupORACLE instance started.Total System Global Area 1442840576 bytesFixed Size 2924448 bytesVariable Size 553648224 bytesDatabase Buffers 872415232 bytesRedo Buffers 13852672 bytesDatabase mounted.Database opened.SQL> 3.開始切換主庫：SQL> alter database commit to switchover to physical standby with session shutdown;重啟數據庫到mount狀態SQL> startup mountORACLE instance started.Total System Global Area 1442840576 bytesFixed Size 2924448 bytesVariable Size 553648224 bytesDatabase Buffers 872415232 bytesRedo Buffers 13852672 bytesDatabase mounted.SQL> 查看數據庫角色與狀態SQL> select status from v$instance;STATUS------------MOUNTEDSQL> select database_role from v$database;DATABASE_ROLE----------------PHYSICAL STANDBYSQL> 此時rac1已變成備庫備庫：SQL> alter database commit to switchover to primary with session shutdown;Database altered.SQL> alter database open;Database altered.

上一篇：oracle 系統權限和對象權限

下一篇：MySQL一次更新多條不同的記錄