Oracle11g+CentOS6+DG安裝記錄,oracle11gcentos6
本文根據一個實踐操作的記錄,詳細介紹利用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.logsqlplus / as sysdbamkdir standbyalter 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和Database Backup檔案,控制檔案等
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_SIDscp /home/oracle/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora oracle@192.168.100.118:/home/oracle/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora
2:在備庫上複製主庫備份出來的備份檔案,並準備相關的目錄
mkdir -p /home/oracle/backup/ scp -rp oracle@192.168.100.117:/home/oracle/backup/* /home/oracle/backup/cp /home/oracle/backup/initahqy.ora $ORACLE_HOME/dbsgrep '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/ahqymkdir -p /home/oracle/app/oracle/admin/ahqy/{adump,bdump,cdump,dpdump,udump,pfile} --有幾個檔案夾可能用不到mkdir -p /home/oracle/oradata/ahqymkdir -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 sysdbastartup nomount;
4:使用rman對備庫進行恢複
rman target sys/ORACLE@AHQY auxiliary / duplicate target database for standby nofilenamecheck;
5: 將備庫置於應用redolog模式
sqlplus / as sysdbaarchive 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 sysdbaalter 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 sysdbashutdown 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 immediatestartup mountalter database recover managed standby database disconnect from session;
備庫操作:
shutdown immediatestartup mountalter 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) ) )
七、參考文章
http://ylw6006.blog.51cto.com/470441/661105
http://www.educity.cn/shujuku/1177494.html
http://blog.csdn.net/w63667329/article/details/7900270