程式師世界 >> 數據庫知識 >> Oracle數據庫 >> Oracle教程 >> Oracle11g+CentOS6+DG安裝記錄

Oracle11g+CentOS6+DG安裝記錄

編輯：Oracle教程

Oracle11g+CentOS6+DG安裝記錄

本文根據一個實踐操作的記錄，詳細介紹利用rman熱備來創建dataguard物理Active備庫；這種方式主庫只需要重啟一次即可，使主庫具有更高的可用性！
在進行按本文檔的操作之前，我們默認主數據庫已經處於歸檔運行模式；大致流程是先配置主庫的參數及做rman備份，然後在備庫上裝oracle軟件、配置監聽，不需要dbca創建數據庫；然後修改備庫的參數文件及進行rman恢復，之後進行測試。
環境說明：
primary: CentOS6.3 64位 192.168.100.117，db_name:ahqy，db_unique_name: ahqy
standby: CentOS6.3 64位 192.168.100.118，db_name:ahqy，db_unique_name: standby
oracle版本： 11.2.0.1 64位企業版
一、primary服務器配置

1：主庫上開啟Forced Logging

alter database force logging;

2：在備庫上創建密碼文件，這裡直接從主庫復制到備庫

scp /home/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/orapwahqy 192.168.100.118:/home/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/orapwahqy

3：主庫上配置Standby Redo Log

select member from v$logfile;
du -h /home/oracle/oradata/ahqy/redo01.log
sqlplus / as sysdba
mkdir standby
alter database add standby logfile group 4 '/home/oracle/oradata/ahqy/standby/standby04.log' size 200M;
alter database add standby logfile group 5 '/home/oracle/oradata/ahqy/standby/standby05.log' size 200M;
alter database add standby logfile group 6 '/home/oracle/oradata/ahqy/standby/standby06.log' size 200M;

4.修改主庫的初始化參數

alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(ahqy,standby)';

alter system set log_archive_dest_1='LOCATION=/home/oracle/arch valid_for=(all_logfiles,all_roles) db_unique_name=ahqy' scope=spfile;

alter system set log_archive_dest_2='service=standby lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=standby' scope=spfile;

alter system set log_archive_dest_state_1=enable;
 
alter system set log_archive_dest_state_2=enable;

alter system set log_archive_format='%t_%s_%r.arc' scope=spfile;

show parameter remote_login;

alter system set log_archive_max_processes=30;

alter system set fal_server=standby;

alter system set fal_client=ahqy;

alter system set standby_file_management=auto; 

shutdown immediate; 

startup;

5：配置主庫的tnsnames.ora文件，備庫需要同樣的操作

cat /home/oracle/app/oracle/product/11.2.0/dbhome_1/network/admin//tnsnames.ora 
# tnsnames.ora Network Configuration File: /home/oracle/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

LISTENER_AHQY =
  (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.117)(PORT = 1521))


AHQY =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.117)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ahqy)
    )
  )

STANDBY =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.118)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = standby)
    )
  )

6：在主庫上准備備庫需要的pfile和數據庫備份文件，控制文件等

mkdir -p /home/oracle/backup/ 
create pfile='/home/oracle/backup/initahqy.ora' from spfile;

rman target /
backup tag 'dg_20141226' format '/home/oracle/backup/dg_%U' incremental level 0 database plus archivelog; 
backup format '/home/oracle/backup/controlfile_%U' current controlfile for standby;

二、standby服務器配置
1：設置oracle_sid,配置tnsnames.ora

echo $ORACLE_SID
scp /home/oracle/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora [email protected]:/home/oracle/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora

2：在備庫上復制主庫備份出來的備份文件，並准備相關的目錄

mkdir -p /home/oracle/backup/ 
scp -rp [email protected]:/home/oracle/backup/* /home/oracle/backup/
cp /home/oracle/backup/initahqy.ora $ORACLE_HOME/dbs
grep 'fal_' $ORACLE_HOME/dbs/initahqy.ora  
 
*.fal_client='STANDBY'
*.fal_server='AHQY'

grep 'log_archive_dest_'  $ORACLE_HOME/dbs/initahqy.ora 
*.log_archive_dest_1='LOCATION=/home/oracle/arch valid_for=(all_logfiles,all_roles) db_unique_name=standby'
*.log_archive_dest_2='service=ahqy lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=ahqy'
*.log_archive_dest_state_1='ENABLE'
*.log_archive_dest_state_2='ENABLE'

*.db_unique_name='standby' ----------------------------這一句漏了，導致默認成了AHQY，一定要加上

*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(standby,ahqy)'  --主備庫切換用，否則切換會有問題
---alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(standby,ahqy)';

mkdir -p /home/oracle/arch/ahqy
mkdir -p /home/oracle/app/oracle/admin/ahqy/{adump,bdump,cdump,dpdump,udump,pfile}   --有幾個文件夾可能用不到
mkdir -p /home/oracle/oradata/ahqy
mkdir -p /u01/app/oracle/flash_recover_area/ORCL    --未找到
--(將相應的主庫文件直接移到了備庫上，如：/home/oracle/arch/、/home/oracle/oradata/、/home/oracle/oradiag_oracle)
mkdir -p /home/oracle/app/oracle/diag/rdbms/ahqy/ahqy/{alert,cdump,hm,incident,incpkg,ir,lck,metadata,stage,sweep,trace}
考慮到可能有文件夾沒有建，所以我這裡將幾個主要文件夾直接從主庫拷貝到備庫相應的目錄中去。

3：將備庫啟動到nomount狀態

sqlplus / as sysdba
startup nomount;

4：使用rman對備庫進行恢復

rman target sys/ORACLE@AHQY auxiliary / 
duplicate target database for standby nofilenamecheck;

5: 將備庫置於應用redolog模式

sqlplus / as sysdba
archive log list;
alter database recover managed standby database disconnect from session;
select SEQUENCE#,FIRST_TIME,NEXT_TIME,APPLIED from v$archived_log;
select member from v$logfile;

可以在主庫上進行日志切換，加快備庫應用日志的速度！
sqlplus / as sysdba
alter system switch logfile;
archive log list;
備庫上再次查詢： 
select SEQUENCE#,FIRST_TIME,NEXT_TIME,APPLIED from v$archived_log;
select name,database_role from v$database;

三、數據測試
1.在主庫上創建表空間，建表

select name,database_role from v$database;
create tablespace ahqy datafile  '/home/oracle/oradata/ahqy/ahqy.dbf'  size 10M autoextend on;  
create table dg01 as select * from dba_source;
alter system switch logfile;

2.備庫應用日志後以只讀方式打開查看數據

alter database recover managed standby database cancel;
alter database open read only; 
select type from dg01;

3：從新將備庫置於應用日志模式

sqlplus / as sysdba
shutdown immediate;
startup nomount;
alter database mount standby database;
alter database recover managed standby database disconnect from session;

四、將備庫置為Active模式
1) 查看備庫當前狀態 mount

select open_mode,database_role,db_unique_name from v$database;

2) 取消備庫的自動恢復

alter database recover managed standby database cancel;

3) OPEN備庫為只讀模式（Dataguard只能啟動到readonly模式）

alter database open;
select open_mode from v$database;
alter database recover managed standby database using current logfile disconnect;
select open_mode,database_role,db_unique_name from v$database;

五、主備庫切換
主庫操作：

alter database commit to switchover to physical standby with session shutdown;
shutdown immediate
startup mount
alter database recover managed standby database disconnect from session;

備庫操作：

shutdown immediate
startup mount
alter database commit to switchover to primary;
alter database open;

六、注意事項
1：主備端都使用靜態偵聽

[oracle@118 admin]$ cat listener.ora 
# listener.ora Network Configuration File: /home/oracle/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.118)(PORT = 1521))
    )
  )
SID_LIST_LISTENER =  
  (SID_LIST =  
    (SID_DESC =  
      (SID_NAME = PLSExtProc)  
      (ORACLE_HOME = /home/oracle/app/oracle/product/11.2.0/dbhome_1)  
      (PROGRAM = extproc)  
    )  
    (SID_DESC =
      (SID_NAME = ahqy)
      (ORACLE_HOME = /home/oracle/app/oracle/product/11.2.0/dbhome_1)
      (GLOBAL_DBNAME = standby)
    )
  )  


ADR_BASE_LISTENER = /home/oracle/app/oracle

[oracle@117 admin]$ cat listener.ora 
# listener.ora Network Configuration File: /home/oracle/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.117)(PORT = 1521))
    )
  )

SID_LIST_LISTENER =  
  (SID_LIST =  
    (SID_DESC =  
      (SID_NAME = PLSExtProc)  
      (ORACLE_HOME = /home/oracle/app/oracle/product/11.2.0/dbhome_1)  
      (PROGRAM = extproc)  
    )  
    (SID_DESC =
      (SID_NAME = ahqy)
      (ORACLE_HOME = /home/oracle/app/oracle/product/11.2.0/dbhome_1)
      (GLOBAL_DBNAME = ahqy)
    )
  )

ADR_BASE_LISTENER = /home/oracle/app/oracle

2：TNSNAME配置
tnsnames裡的SERVICE_NAME可以根據show parameter name查看好之後，進行對應的配置。

[oracle@117 admin]$ cat tnsnames.ora 
# tnsnames.ora Network Configuration File: /home/oracle/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

LISTENER_AHQY =
  (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.117)(PORT = 1521))


AHQY =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.117)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ahqy)
    )
  )

standby =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.118)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = standby)
    )
  )
  
[oracle@118 admin]$ cat tnsnames.ora 
# tnsnames.ora Network Configuration File: /home/oracle/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

LISTENER_AHQY =
  (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.117)(PORT = 1521))


AHQY =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.117)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ahqy)
    )
  )

STANDBY =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.118)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = standby)
    )
  )