ORACLE 12C DATAGUARD環境搭建和主從切換

環境說明：
目的：
（1）在linux 作業系統下，建立oracle 12c 單例項的dataguard環境
（2）測試dataguard主從切換
主庫：10.50.115.12 （DB_UNIQUE_NAME=cdbtest,service_name=cdbtest,db_name=cdbtest）
從庫：10.50.115.20 （DB_UNIQUE_NAME=cdbtest1,service_name=cdbtest,db_name=cdbtest）
備註：DB_UNIQUE_NAME不能一樣

-------------------------------------------------------------------------------
一、建立oracle 12c 單例項的dataguard環境
主庫：
mkdir -p /u01/app/oracle/arch/ 
chown oracle.oinstall /u01/app/oracle/arch/ 

1. 開啟歸檔模式
sqlplus / as sysdba
startup mount
alter database archivelog;
alter database open;

2. 配置TNS,增加連線到從庫的tns:
cat >> $ORACLE_HOME/network/admin/tnsnames.ora << EOF
cdbtest1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.50.115.20)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SID = cdbtest)
    )
  )
EOF

3. 修改主庫的dataguard相關引數
alter system set standby_file_management='auto'  scope=both;
alter system set log_archive_config='dg_config=(cdbtest,cdbtest1)'  scope=both;
alter system set LOG_ARCHIVE_DEST_1= 'LOCATION=/u01/app/oracle/arch/   VALID_FOR=(ALL_LOGFILES,ALL_ROLES)   DB_UNIQUE_NAME=cdbtest' scope=both;
alter system set LOG_ARCHIVE_DEST_2= 'SERVICE=cdbtest1 ASYNC  VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=cdbtest1' scope=both;
# 這些引數僅當主庫切換成備庫時生效
alter system set FAL_SERVER=cdbtest1  scope=both;
alter system set DB_FILE_NAME_CONVERT='cdbtest','cdbtest' scope=spfile;
alter system set LOG_FILE_NAME_CONVERT= '/u01/app/oracle/oradata/cdbtest/','/u01/app/oracle/oradata/cdbtest/'  scope=spfile;

4. 增加standby log
select member from v$logfile;
alter database add standby logfile '/u01/app/oracle/oradata/cdbtest/stdredo01.log' size 10M;
alter database add standby logfile '/u01/app/oracle/oradata/cdbtest/stdredo02.log' size 50M;
alter database add standby logfile '/u01/app/oracle/oradata/cdbtest/stdredo03.log' size 50M;

從庫：
mkdir -p /u01/app/oracle/arch/ 
chown oracle.oinstall /u01/app/oracle/arch/ 

1. 建立與主庫一樣的資料庫（主要目的是建立相關目錄，資料檔案隨後會覆蓋，不用建立pdb資料庫）

2. 配置連線到主庫的TNS:
cat >> $ORACLE_HOME/network/admin/tnsnames.ora << EOF
cdbtest_pri =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.50.115.12)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = cdbtest)
    )
  )
EOF

3. 建立靜態監聽器
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = cdbtest)
    )
  )
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

4. 修改相關引數
alter system set log_archive_config='dg_config=(cdbtest,cdbtest1)'  scope=both;
alter system set STANDBY_FILE_MANAGEMENT=AUTO scope=both;
alter system set DB_FILE_NAME_CONVERT='cdbtest','cdbtest' scope=spfile;
alter system set LOG_FILE_NAME_CONVERT= '/u01/app/oracle/oradata/cdbtest/','/u01/app/oracle/oradata/cdbtest/'  scope=spfile;
alter system set FAL_SERVER=cdbtest_pri  scope=both;

alter system set log_archive_dest_state_1='enable' scope=both;
alter system set log_archive_dest_state_2='enable' scope=both;
alter system set LOG_ARCHIVE_DEST_1= 'LOCATION=/u01/app/oracle/arch/   VALID_FOR=(ALL_LOGFILES,ALL_ROLES)   DB_UNIQUE_NAME=cdbtest1' scope=both;
alter system set LOG_ARCHIVE_DEST_2= 'SERVICE=cdbtest_pri ASYNC  VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=cdbtest'scope=both;

# 建立資料庫後，db_unique_name將不能改
alter system set db_unique_name='cdbtest1' scope=spfile;
alter system set service_names=cdbtest scope=spfile;

5. 複製密碼檔案：
scp /u01/app/oracle/product/12.1.0/dbhome_1/dbs/orapwcdbtest 10.50.115.20://u01/app/oracle/product/12.1.0/dbhome_1/dbs/

6. 建pdb資料檔案的目錄(參考主庫)
cd /u01/app/oracle/oradata/cdbtest
mkdir -p  pdb1  pdb2

7. rman複製資料庫
備庫：
rman target sys/biostime123@cdbtest_pri auxiliary sys/biostime123@cdbtest1 nocatalog <<eof
duplicate target database for standby from active database nofilenamecheck;
EOF

8.啟動日誌應用
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE cancel;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE  DISCONNECT FROM SESSION;


 二、主從切換測試

主從切換：
主庫：
1. 如果該列值為"TO STANDBY"則表示primary 資料庫支援轉換為standby
select switchover_status from v$database;

2. 首先將primary 轉換為standby 的角色
alter database commit to switchover to physical standby;
（執行完後，資料庫down掉）

3. 重啟動到mount(原primary)
shutdown immediate
startup mount
select switchover_status from v$database;
說明：PRIMARY進行轉換完畢後，檢視狀態會變成RECOVERY NEEDED;

從庫：
備庫切換成主庫的操作
1、檢查備庫的狀態
select switchover_status from v$database;
主庫關閉前為“NOT ALLOWED”，關閉後，從庫變為“TO_PRIMARY”

2. 確認沒有問題後，可以進行切換轉換standby 到primary 角色
alter database commit to switchover to primary;

3. 完成轉換，開啟新的primary 資料庫
alter database open;

4.檢查狀態
select name,open_mode,database_role,protection_mode,SWITCHOVER_STATUS  From v$database;

5. 在原主庫啟動日誌應用
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;  
ALTER DATABASE OPEN;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;

</eof