|
一：實(shí)驗(yàn)環(huán)境介紹 虛擬機(jī)系統(tǒng)： RHEL Linux 6.4（64位） 數(shù)據(jù)庫(kù)版本： Oracle 11gR2 11.2.0.4 （64位） IP地址規(guī)劃： 主數(shù)據(jù)庫(kù) 192.168.11.120 SID：pri db_name:pri db_unique_name：pri 備份數(shù)據(jù)庫(kù) 192.168.11.121 SID：std db_name:pri db_unique_name：std
安裝完成后可以通過(guò)以下命令查詢： select dbid,name,open_mode,db_unique_name from v$database; select instance_name from v$instance;
基礎(chǔ)要求：（1）安裝兩臺(tái)虛擬機(jī)分別作為主庫(kù)和備庫(kù)，硬盤(pán)大小隨意。但要保證 根分區(qū)有20G，別給太小了就行。物理內(nèi)存1G，SWAP 2G 關(guān)于Active database duplication方式：（必看） 在Oracle 10g下，我們可以使用RMAN duplicate 命令創(chuàng)建一個(gè)具有不同DBID 的復(fù)制庫(kù)。 到了Oracle 11gR2， RMAN 的duplicate 有2種方法實(shí)現(xiàn)： 1. Active database duplication （本文所使用的方式，適用于11gR2之后的版本） 2. Backup-based duplication （傳統(tǒng)方式，10g，11g通用）
Active database duplication 通過(guò)網(wǎng)絡(luò)，直接copy target 庫(kù)到auxiliary 庫(kù)，然后創(chuàng)建復(fù)制庫(kù)。這種方法就不需要先用RMAN 備份數(shù)據(jù)庫(kù)，然后將備份文件發(fā)送到auxiliary端。 這個(gè)功能的作用是非常大的。 尤其是對(duì)T級(jí)別的庫(kù)。 因?yàn)閷?duì)這樣的庫(kù)進(jìn)行備份，然后將備份集發(fā)送到備庫(kù)，在進(jìn)行duplicate 的代價(jià)是非常大的。 一備份要占用時(shí)間，二要占用備份空間，三在網(wǎng)絡(luò)傳送的時(shí)候，還需要占用帶寬和時(shí)間。所以Active database duplicate 很好的解決了以上的問(wèn)題。 它對(duì)大庫(kù)的遷移非常有用。 如果是從RAC duplicate 到單實(shí)例，操作是一樣的。 如果是從單實(shí)例duplicate 到RAC。 那么先duplicate 到 單實(shí)例。 然后將單實(shí)例轉(zhuǎn)換成RAC。
二：安裝數(shù)據(jù)庫(kù)軟件前的系統(tǒng)配置 （主庫(kù)和備庫(kù)端都要做下列操作） 1．基本配置： 一定要用新裝的系統(tǒng)（這里以紅帽企業(yè)版6.4為例），系統(tǒng)要求最低配置1G內(nèi)存，2Gswap分區(qū)，根分區(qū)20G以上，裝好系統(tǒng)后先配置好yum，IP地址，/etc/sysconfig/network文件中的主機(jī)名，以及/etc/hosts文件中的IP地址和主機(jī)名的對(duì)應(yīng)關(guān)系。
主庫(kù)IP：192.168.11.120 主庫(kù)主機(jī)名：ora11g 備庫(kù)IP：192.168.11.121 備庫(kù)主機(jī)名：ora11g-dg
2．裝包 binutils-2.20.51.0.2-5.11.el6 (x86_64) compat-libcap1-1.10-1 (x86_64) compat-libstdc++-33-3.2.3-69.el6 (x86_64) compat-libstdc++-33-3.2.3-69.el6.i686 gcc-4.4.4-13.el6 (x86_64) gcc-c++-4.4.4-13.el6 (x86_64) glibc-2.12-1.7.el6 (i686) glibc-2.12-1.7.el6 (x86_64) glibc-devel-2.12-1.7.el6 (x86_64) glibc-devel-2.12-1.7.el6.i686 ksh pdksh-5.2.14-21.x86_64.rpm libgcc-4.4.4-13.el6 (i686) libgcc-4.4.4-13.el6 (x86_64) libstdc++-4.4.4-13.el6 (x86_64) libstdc++-4.4.4-13.el6.i686 libstdc++-devel-4.4.4-13.el6 (x86_64) libstdc++-devel-4.4.4-13.el6.i686 libaio-0.3.107-10.el6 (x86_64) libaio-0.3.107-10.el6.i686 libaio-devel-0.3.107-10.el6 (x86_64) libaio-devel-0.3.107-10.el6.i686 make-3.81-19.el6 sysstat-9.0.4-11.el6 (x86_64) unixODBC-2.2.14-11.el6 (x86_64) or later unixODBC-2.2.14-11.el6.i686 or later unixODBC-devel-2.2.14-11.el6 (x86_64) or later unixODBC-devel-2.2.14-11.el6.i686 or later
3．創(chuàng)建相關(guān)的組與用戶： /usr/sbin/groupadd oinstall /usr/sbin/groupadd -g 502 dba /usr/sbin/groupadd -g 503 oper /usr/sbin/useradd -u 502 -g oinstall -G dba,oper oracle
# passwd oracle
4．創(chuàng)建所需的目錄并賦予特定的屬主和屬組 mkdir -p /u01/app/oracle chown -R oracle:oinstall /u01/app chmod -R 775 /u01/app
5．編輯limits.conf 文件 vim /etc/security/limits.conf 添加下面5行內(nèi)容： vim /etc/security/limits.conf oracle soft nproc 2047 oracle hard nproc 16384 oracle soft nofile 1024 oracle hard nofile 65536 oracle soft stack 10240 6．編輯sysctl.conf文件，設(shè)置相關(guān)參數(shù) vim /etc/sysctl.conf 添加/修改下列內(nèi)容：（注意！下面的參數(shù)，若是已經(jīng)存在，則直接修改數(shù)值，不要再添加同樣的參數(shù)，相同的參數(shù)只能有一個(gè)！如果需要修改的參數(shù)已經(jīng)大于下面的數(shù)字，則不用修改，請(qǐng)仔細(xì)核對(duì)?。?/p> vim /etc/sysctl.conf
fs.aio-max-nr = 1048576 fs.file-max = 6815744 kernel.shmall = 2097152 kernel.shmmax = 4294967295 kernel.shmmni = 4096 kernel.sem = 250 32000 100 128 net.ipv4.ip_local_port_range = 9000 65500 net.core.rmem_default = 262144 net.core.rmem_max = 4194304 net.core.wmem_default = 262144 net.core.wmem_max = 1048576 #sysctl –p 使其生效 # /sbin/sysctl -p 保存退出后，別忘了用sysctl -p命令使參數(shù)生效！
7．編輯vim /home/oracle/.bash_profile文件，配置相關(guān)環(huán)境變量 添加下列幾行： export ORACLE_BASE=/u01/app/oracle export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1 export ORACLE_SID=pri (備庫(kù)端設(shè)置為std) export LD_LIBRARY_PATH=$ORACLE_HOME/bin:/bin:/usr/bin:/usr/local/bin: export CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib export PATH=$ORACLE_HOME/bin:$PATH
注意：服務(wù)器需要關(guān)閉iptables和selinux linux6.4系統(tǒng)：iptables –F Service iptables save Setenforce 0 Vi /etc/selinux/config [oracle@ora11g admin]$ more /etc/selinux/config
# This file controls the state of SELinux on the system. # SELINUX= can take one of these three values: # enforcing - SELinux security policy is enforced. # permissive - SELinux prints warnings instead of enforcing. # disabled - No SELinux policy is loaded. SELINUX=disabled # SELINUXTYPE= can take one of these two values: # targeted - Targeted processes are protected, # mls - Multi Level Security protection. SELINUXTYPE=targeted
三：開(kāi)始安裝oracle11g數(shù)據(jù)庫(kù)軟件 （主庫(kù)和備庫(kù)端都要做下列操作） 1.掛載oracle11g的鏡像到/mnt/cdrom 目錄下，并把3個(gè)包解壓到/usr/local/src 目錄下 cd /mnt/cdrom/ unzip p13390677_112040_Linux-x86-64_1of7.zip -d /usr/local/src unzip p13390677_112040_Linux-x86-64_2of7.zip -d /usr/local/src unzip p13390677_112040_Linux-x86-64_3of7.zip -d /usr/local/src (只裝數(shù)據(jù)庫(kù)的話，解壓前兩個(gè)包即可，第三個(gè)包是grid) 2. root身份執(zhí)行xhost +，然后切換到oracle用戶進(jìn)入/usr/local/src/database目錄中，執(zhí)行runInstaller 開(kāi)始安裝 (database是默認(rèn)解壓完的目錄名) # xhost + # su - oracle $ ./runInstaller 注意：安裝oracle11g要求分辨率最低為1024x768,不然無(wú)法完整顯示安裝過(guò)程
安裝過(guò)程：略 注意：主庫(kù)在安裝完軟件后需要進(jìn)行DBCA建庫(kù)操作，推薦不要啟用歸檔模式，會(huì)節(jié)省時(shí)間，備庫(kù)端不要建庫(kù)！可以開(kāi)啟監(jiān)聽(tīng)程序
四：開(kāi)始搭建Dataguard
1：將數(shù)據(jù)庫(kù)改為強(qiáng)制日志模式 （此步驟只在主庫(kù)上做） [oracle@pri ~]$ sqlplus / as sysdba
查看當(dāng)前是否強(qiáng)制日志模式： SYS@pri> select name,log_mode,force_logging from v$database;
NAME LOG_MODE FOR --------- ------------ --- PRI NOARCHIVELOG NO
SYS@pri> alter database force logging;
Database altered.
SYS@pri> select name,log_mode,force_logging from v$database;
NAME LOG_MODE FOR --------- ------------ --- PRI NOARCHIVELOG YES
2：創(chuàng)建密碼文件 （此步驟只在主庫(kù)上做） 注意：兩端分別創(chuàng)建自己的密碼文件好像有問(wèn)題，備庫(kù)的密碼文件需要跟主庫(kù)一致，否則導(dǎo)致日志傳輸不到備庫(kù)，有待驗(yàn)證。我最后是將主庫(kù)的密碼文件直接copy到備庫(kù)，重命名后使用。 [oracle@pri ~]$ cd $ORACLE_HOME/dbs [oracle@ora11g dbs]$ ls hc_pri.dat init.ora initpri.ora lkPRI orapwpri snapcf_pri.f spfilepri.ora 已經(jīng)有一個(gè)密碼文件了 [oracle@pri dbs]$ orapwd file=orapwpri password=oracle force=y 這條命令可以手動(dòng)生成密碼文件，force=y的意思是強(qiáng)制覆蓋當(dāng)前已有的密碼文件（如果有可以不建立）
將主庫(kù)的密碼文件copy給備庫(kù)，并重命名 [oracle@pri dbs]$ scp orapwpri 192.168.11.121:$ORACLE_HOME/dbs/orapwstd
3：創(chuàng)建standby redolog日志組 （此步驟只在主庫(kù)上做） 原則： 1：standby redo log的文件大小與primary 數(shù)據(jù)庫(kù)online redo log 文件大小相同 2：standby redo log日志文件組的個(gè)數(shù)依照下面的原則進(jìn)行計(jì)算： Standby redo log組數(shù)公式>=(每個(gè)instance日志組個(gè)數(shù)+1)*instance個(gè)數(shù) 假如只有一個(gè)節(jié)點(diǎn)，這個(gè)節(jié)點(diǎn)有三組redolog， 所以Standby redo log組數(shù)>=(3+1)*1 == 4 所以至少需要?jiǎng)?chuàng)建4組Standby redo log
查看當(dāng)前線程與日志組的對(duì)應(yīng)關(guān)系及日志組的大小： SYS@pri> select thread#,group#,bytes/1024/1024 from v$log;
THREAD# GROUP# BYTES/1024/1024 --------------- ------------- ------------------------ 1 1 50 1 2 50 1 3 50 如上，我現(xiàn)在的環(huán)境有三組redolog，每個(gè)日志組的大小都是50M， 所以Standby redo log組數(shù)>=(3+1)*1== 4 所以至少需要?jiǎng)?chuàng)建4組Standby redo log，大小均為50M （thread：線程，只有在多實(shí)例數(shù)據(jù)庫(kù)才有用的參數(shù)，例如RAC環(huán)境，單實(shí)例不考慮）
查看當(dāng)前有哪些日志組及其成員： SYS@pri> col member for a50 SYS@pri> select group#,member from v$logfile;
GROUP# MEMBER ---------- -------------------------------------------------- 3 /u01/app/oracle/oradata/pri/redo03.log 2 /u01/app/oracle/oradata/pri/redo02.log 1 /u01/app/oracle/oradata/pri/redo01.log
先手動(dòng)創(chuàng)建standby log日志組所需的目錄： （創(chuàng)建新目錄只是為了便于區(qū)分，并非必須） [oracle@ora11g oradata]$ cd /u01/app/oracle/oradata/ [oracle@ora11g oradata]$ ls standbylog pri
新建4個(gè)日志組作為standby redolog日志組，大小與原來(lái)的日志組一致： 由于已經(jīng)存在group1-3,，所以group號(hào)只能從4開(kāi)始 SYS@pri> alter database add standby logfile group 4 '/u01/app/oracle/oradata/standbylog/std_redo04.log' size 50m; alter database add standby logfile group 5 '/u01/app/oracle/oradata/standbylog/std_redo05.log' size 50m; alter database add standby logfile group 6 '/u01/app/oracle/oradata/standbylog/std_redo06.log' size 50m; alter database add standby logfile group 7 '/u01/app/oracle/oradata/standbylog/std_redo07.log' size 50m; 查看standby 日志組的信息： SYS@pri> select group#,sequence#,status, bytes/1024/1024 from v$standby_log;
GROUP# SEQUENCE# STATUS BYTES/1024/1024 ---------- ------------------ ------------------- ------------------------ 4 0 UNASSIGNED 50 5 0 UNASSIGNED 50 6 0 UNASSIGNED 50 7 0 UNASSIGNED 50
查看當(dāng)前有哪些日志組及其成員： SYS@pri> set pagesize 100 SYS@pri> col member for a60 SYS@pri> select group#,member from v$logfile order by group#;
GROUP# MEMBER ------------ ------------------------------------------------------------ 1 /u01/app/oracle/oradata/pri/redo01.log 2 /u01/app/oracle/oradata/pri/redo02.log 3 /u01/app/oracle/oradata/pri/redo03.log 4 /u01/app/oracle/oradata/standbylog/std_redo04.log 5 /u01/app/oracle/oradata/standbylog/std_redo05.log 6 /u01/app/oracle/oradata/standbylog/std_redo06.log 7 /u01/app/oracle/oradata/standbylog/std_redo07.log
4：修改主庫(kù)的pfile參數(shù)文件 （此步驟只在主庫(kù)上做）
查看spfile的路徑： SYS@pri> show parameter spfile;
NAME TYPE VALUE ----------- ----------- ----------------------------------------------------------------- spfile string /u01/app/oracle/product/11.2.0/db_1/dbs/spfilepri.ora
用spfile創(chuàng)建一個(gè)pfile,用于修改： SYS@pri> create pfile from spfile;
修改主庫(kù)的pfile： /u01/app/oracle/product/11.2.0/db_1/dbs/ initpri.ora [oracle@pri ~]$ cd $ORACLE_HOME/dbs pri.__db_cache_size=318767104 pri.__java_pool_size=4194304 pri.__large_pool_size=4194304 pri.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment pri.__pga_aggregate_target=335544320 pri.__sga_target=503316480 pri.__shared_io_pool_size=0 pri.__shared_pool_size=163577856 pri.__streams_pool_size=0 *.audit_file_dest='/u01/app/oracle/admin/pri/adump' *.audit_trail='db' *.compatible=' 11.2.0.4.0 ' *.control_files='/u01/app/oracle/oradata/pri/control01.ctl','/u01/app/oracle/flash_recovery_area/pri/control02.ctl' *.db_block_size=8192 *.db_domain='' *.db_name='pri' *.db_recovery_file_dest='/u01/app/oracle/flash_recovery_area' *.db_recovery_file_dest_size=4070572032 *.diagnostic_dest='/u01/app/oracle' *.dispatchers='(PROTOCOL=TCP) (SERVICE=priXDB)' *.log_archive_format='%t_%s_%r.dbf' *.memory_target=836763648 *.open_cursors=300 *.processes=150 *.remote_login_passwordfile='EXCLUSIVE' *.undo_tablespace='UNDOTBS1' 以下內(nèi)容是需要新增加的： *.db_unique_name='pri' DG主庫(kù)和備庫(kù)的db_name必須一致，db_unique_name不一致 *.log_archive_config='dg_config=(pri,std)' pri主數(shù)據(jù)庫(kù)SID，std備份數(shù)據(jù)庫(kù)SID *.log_archive_dest_1='location=/u01/app/oracle/arch valid_for=(all_logfiles,all_roles) db_unique_name=pri' 主數(shù)據(jù)庫(kù)的歸檔日志路徑和SID *.log_archive_dest_2='service=std valid_for=(online_logfiles,primary_role) db_unique_name=std' 備份數(shù)據(jù)庫(kù)的SID *.log_archive_dest_state_1=enable *.log_archive_dest_state_2=enable *.log_archive_max_processes=4 *.fal_server='std' 備份數(shù)據(jù)庫(kù)的SID *.fal_client='pri' 主數(shù)據(jù)庫(kù)的SID *.db_file_name_convert='/u01/app/oracle/oradata/std','/u01/app/oracle/oradata/pri' 第一個(gè)目錄是備份數(shù)據(jù)庫(kù)數(shù)據(jù)文件路徑（備份服務(wù)器上有此目錄），第二個(gè)是主數(shù)據(jù)庫(kù)數(shù)據(jù)文件路徑 *.log_file_name_convert='/u01/app/oracle/oradata/std','/u01/app/oracle/oradata/pri' 第一個(gè)目錄是備份數(shù)據(jù)庫(kù)數(shù)據(jù)文件路徑（備份服務(wù)器上有此目錄），第二個(gè)是主數(shù)據(jù)庫(kù)數(shù)據(jù)文件路徑 *.standby_file_management='auto' 修改完畢，保存退出
手工創(chuàng)建/u01/app/oracle/arch： [oracle@pri dbs]$ mkdir –p /u01/app/oracle/arch
5：用修改過(guò)的pfile重新創(chuàng)建一個(gè)spfile，用于重啟數(shù)據(jù)庫(kù) （此步驟只在主庫(kù)上做） 關(guān)閉數(shù)據(jù)庫(kù)： SYS@pri> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down.
用修改過(guò)的pfile重新創(chuàng)建一個(gè)spfile： SYS@pri> create spfile from pfile;
此時(shí)把數(shù)據(jù)庫(kù)改為歸檔模式： （如果當(dāng)初建庫(kù)時(shí)選擇了啟用歸檔，則此步驟忽略） 由于當(dāng)前數(shù)據(jù)庫(kù)已關(guān)閉，首先需要把數(shù)據(jù)庫(kù)啟動(dòng)到mount狀態(tài) SYS@pri> startup mount; ORACLE instance started. Database mounted. SYS@pri> alter database archivelog; 啟用歸檔模式 Database altered.
SYS@pri> alter database open; OPEN數(shù)據(jù)庫(kù) Database altered.
SYS@pri> archive log list; 查看是否啟用歸檔模式 SQL> archive log list; Database log mode Archive Mode Automatic archival Enabled Archive destination /u01/app/oracle/arch Oldest online log sequence 22 Next log sequence to archive 24 Current log sequence 24 如上，歸檔路徑已經(jīng)改為/u01/app/oracle/arch，證明對(duì)pfile的修改已生效
查看當(dāng)前數(shù)據(jù)庫(kù)是否使用spfile啟動(dòng)： SYS@pri> show parameter spfile;
NAME TYPE VALUE ----------- ----------- ----------------------------------------------------------------- spfile string /u01/app/oracle/product/11.2.0/db_1/dbs/spfilepri.ora
如上，若能看到spfile的路徑，則證明數(shù)據(jù)庫(kù)是使用spfile啟動(dòng)的，若沒(méi)有值，則說(shuō)明是用pfile啟動(dòng)的。
確認(rèn)數(shù)據(jù)庫(kù)已經(jīng)啟用歸檔模式和強(qiáng)制日志模式： SYS@pri> select name,log_mode,force_logging from v$database;
NAME LOG_MODE FOR --------- ------------------- ------- PRI ARCHIVELOG YES
6：修改監(jiān)聽(tīng)文件，添加靜態(tài)監(jiān)聽(tīng) （主庫(kù)、備庫(kù)都要做） 主庫(kù)： [oracle@pri ~]$ cd $ORACLE_HOME/network/admin [oracle@pri admin]$ vim listener.ora 添加的內(nèi)容如下紅色字體部分： # listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora # Generated by Oracle configuration tools.
LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.11.120)(PORT = 1521)) (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521)) ) )
SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = pri) (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1) (SID_NAME = pri) ) )
ADR_BASE_LISTENER = /u01/app/oracle 主庫(kù)修改后最終效果如下圖：
備庫(kù)： [oracle@pri ~]$ cd $ORACLE_HOME/network/admin [oracle@pri admin]$ vim listener.ora 添加的內(nèi)容如下紅色字體部分： # listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora # Generated by Oracle configuration tools.
LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.11.121)(PORT = 1521)) (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521)) ) )
SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = std) (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1) (SID_NAME = std) ) )
ADR_BASE_LISTENER = /u01/app/oracle 備庫(kù)修改后最終效果如下圖：
使新增加的監(jiān)聽(tīng)生效： （主庫(kù)和備庫(kù)端都要做） [oracle@pri admin]$ lsnrctl stop [oracle@pri admin]$ lsnrctl start
確認(rèn)新增加的靜態(tài)監(jiān)聽(tīng)有效： 主庫(kù)： [oracle@pri ~]$ lsnrctl status ..........................................(N行省略) Services Summary... Service "pri" has 2 instance(s). Instance "pri", status UNKNOWN, has 1 handler(s) for this service... Instance "pri", status READY, has 1 handler(s) for this service... Service "priXDB" has 1 instance(s). Instance "pri", status READY, has 1 handler(s) for this service... The command completed successfully
備庫(kù)： [oracle@std ~]$ lsnrctl status ..........................................(N行省略) Services Summary... Service "std" has 2 instance(s). Instance "std", status UNKNOWN, has 1 handler(s) for this service... The command completed successfully 如上，靜態(tài)監(jiān)聽(tīng)添加成功
7：編輯網(wǎng)絡(luò)服務(wù)名配置文件tnsnames.ora （主庫(kù)和備庫(kù)端都要做） [oracle@ora11g admin]$ pwd /u01/app/oracle/product/11.2.0/db_1/network/admin [oracle@ora11g admin]$ ls listener.ora samples tnsnames.ora listener.ora_bak shrept.lst tnsnames.ora_bak [oracle@ora11g admin]$ more tnsnames.ora # tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora # Generated by Oracle configuration tools. pri = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.11.120)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = pri) ) )
std = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.11.121)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = std) ) ) 編輯結(jié)果如下圖：
保證主庫(kù)和備庫(kù)的tnsnames.ora文件中的內(nèi)容完全相同，可以把修改后的文件直接傳給備庫(kù)。 [oracle@ora11g admin]$ scp tnsnames.ora 192.168.11.121:$ORACLE_HOME/network/admin tnsnames.ora 100% 925 0.9KB/s 00:00
配置完后，確保在任意一端上都能tnsping通對(duì)方： [oracle@pri admin]$ tnsping std [oracle@std admin]$ tnsping pri
8：在備庫(kù)端，修改pfile參數(shù)文件 （只在備庫(kù)端做） 首先，在主庫(kù)端把pfile拷貝給備庫(kù)端的$ORACLE_HOME/dbs目錄下，并重命名： [oracle@ora11g-dg ~]$ cd $ORACLE_HOME/dbs [oracle@ora11g-dg dbs]$ ls hc_std.dat init.ora initstd.ora lkSTD orapwstd spfilestd.ora [oracle@ora11g dbs]$ scp initpri.ora 192.168.2.253:$ORACLE_HOME/dbs/initstd.ora initpri.ora 100% 1497 1.5KB/s 00:00 然后在備庫(kù)端進(jìn)行修改： [oracle@ora11g-db ~]$ cd $ORACLE_HOME/dbs
[oracle@ora11g-dg dbs]$ more initstd.ora pri.__db_cache_size=318767104 pri.__java_pool_size=4194304 pri.__large_pool_size=4194304 pri.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment pri.__pga_aggregate_target=335544320 pri.__sga_target=503316480 pri.__shared_io_pool_size=0 pri.__shared_pool_size=163577856 pri.__streams_pool_size=0 *.audit_file_dest='/u01/app/oracle/admin/std/adump' *.audit_trail='db' *.compatible='11.2.0.0.0' *.control_files='/u01/app/oracle/oradata/std/control01.ctl','/u01/app/oracle/oradata/std/control02.ctl' *.db_block_size=8192 *.db_domain='' *.db_name='pri' DG主庫(kù)和備庫(kù)的db_name必須一致，db_unique_name不一致 *.db_recovery_file_dest='/u01/app/oracle/flash_recovery_area' *.db_recovery_file_dest_size=4070572032 *.diagnostic_dest='/u01/app/oracle' *.dispatchers='(PROTOCOL=TCP) (SERVICE=stdXDB)' *.log_archive_format='%t_%s_%r.dbf' *.memory_target=836763648 *.open_cursors=300 *.processes=150 *.remote_login_passwordfile='EXCLUSIVE' *.undo_tablespace='UNDOTBS1' 以下需要手工添加： *.db_unique_name='std' *.log_archive_config='dg_config=(pri,std)' *.log_archive_dest_1='location=/u01/app/oracle/arch valid_for=(all_logfiles,all_ roles) db_unique_name=std' *.log_archive_dest_2='service=pri valid_for=(online_logfiles,primary_role) db _unique_name=pri' *.log_archive_dest_state_1=enable *.log_archive_dest_state_2=enable *.log_archive_max_processes=4 *.fal_server='pri' *.fal_client='std' *.db_file_name_convert='/u01/app/oracle/oradata/pri','/u01/app/oracle/oradata /std' *.log_file_name_convert='/u01/app/oracle/oradata/pri','/u01/app/oracle/oradat a/std' *.standby_file_management='auto'
修改完畢，保存退出
注意：整個(gè)搭建過(guò)程最需要留意的就是主庫(kù)和備庫(kù)的PFILE配置，建議修改完后仔細(xì)對(duì)照主備庫(kù)PFILE的區(qū)別
9：在備庫(kù)端手工創(chuàng)建所需的目錄 （備庫(kù)端做，不提前創(chuàng)建的話恢復(fù)時(shí)會(huì)報(bào)錯(cuò)?。?/p> mkdir -pv /u01/app/oracle/admin/std/adump mkdir -pv /u01/app/oracle/diag/rdbms/std/std/trace mkdir -pv /u01/app/oracle/arch mkdir -pv /u01/app/oracle/oradata/std mkdir -pv /u01/app/oracle/oradata/standbylog mkdir -pv /u01/app/oracle/flash_recovery_area
10：用修改后的pfile創(chuàng)建一個(gè)spfile，用于啟動(dòng)數(shù)據(jù)庫(kù) （備庫(kù)端做） [oracle@std ~]$ sqlplus / as sysdba Connected to an idle instance.
SYS@std> create spfile from pfile; File created.
將數(shù)據(jù)庫(kù)啟動(dòng)到nomount狀態(tài)： SYS@std> startup nomount; ORACLE instance started.
Total System Global Area 839282688 bytes Fixed Size 2233000 bytes Variable Size 482348376 bytes Database Buffers 352321536 bytes Redo Buffers 2379776 bytes SYS@std>
11：利用RMAN在備庫(kù)上恢復(fù)主庫(kù) （備庫(kù)端做） [oracle@std ~]$ rman target sys/oracle@pri auxiliary sys/oracle@std
Recovery Manager: Release 11.2.0.3.0 - Production on Tue Apr 15 16:39:28 2014
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: PRI (DBID=775616459) connected to auxiliary database: PRI (not mounted)
RMAN> duplicate target database for standby from active database nofilenamecheck; 這條命令可以直接恢復(fù)數(shù)據(jù)文件，standby控制文件，standby日志組，非常霸道 Starting Duplicate Db at 16-MAR-16 using target database control file instead of recovery catalog allocated channel: ORA_AUX_DISK_1 channel ORA_AUX_DISK_1: SID=134 device type=DISK
contents of Memory Script: { backup as copy reuse targetfile '/u01/app/oracle/product/11.2.0/db_1/dbs/orapwpri' auxiliary format '/u01/app/oracle/product/11.2.0/db_1/dbs/orapwstd' ; } executing Memory Script
Starting backup at 16-MAR-16 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=140 device type=DISK Finished backup at 16-MAR-16
contents of Memory Script: { backup as copy current controlfile for standby auxiliary format '/u01/app/oracle/oradata/std/control01.ctl'; } executing Memory Script
Starting backup at 16-MAR-16 using channel ORA_DISK_1 channel ORA_DISK_1: starting datafile copy copying standby control file output file name=/u01/app/oracle/product/11.2.0/db_1/dbs/snapcf_pri.f tag=TAG20160316T110737 RECID=2 STAMP=906635257 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01 Finished backup at 16-MAR-16
contents of Memory Script: { sql clone 'alter database mount standby database'; } executing Memory Script
sql statement: alter database mount standby database
contents of Memory Script: { set newname for tempfile 1 to "/u01/app/oracle/oradata/std/temp01.dbf"; switch clone tempfile all; set newname for datafile 1 to "/u01/app/oracle/oradata/std/system01.dbf"; set newname for datafile 2 to "/u01/app/oracle/oradata/std/sysaux01.dbf"; set newname for datafile 3 to "/u01/app/oracle/oradata/std/undotbs01.dbf"; set newname for datafile 4 to "/u01/app/oracle/oradata/std/users01.dbf"; backup as copy reuse datafile 1 auxiliary format "/u01/app/oracle/oradata/std/system01.dbf" datafile 2 auxiliary format "/u01/app/oracle/oradata/std/sysaux01.dbf" datafile 3 auxiliary format "/u01/app/oracle/oradata/std/undotbs01.dbf" datafile 4 auxiliary format "/u01/app/oracle/oradata/std/users01.dbf" ; sql 'alter system archive log current'; } executing Memory Script
executing command: SET NEWNAME
renamed tempfile 1 to /u01/app/oracle/oradata/std/temp01.dbf in control file
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting backup at 16-MAR-16 using channel ORA_DISK_1 channel ORA_DISK_1: starting datafile copy input datafile file number=00001 name=/u01/app/oracle/oradata/pri/system01.dbf output file name=/u01/app/oracle/oradata/std/system01.dbf tag=TAG20160316T110744 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:38 channel ORA_DISK_1: starting datafile copy input datafile file number=00002 name=/u01/app/oracle/oradata/pri/sysaux01.dbf output file name=/u01/app/oracle/oradata/std/sysaux01.dbf tag=TAG20160316T110744 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:09 channel ORA_DISK_1: starting datafile copy input datafile file number=00003 name=/u01/app/oracle/oradata/pri/undotbs01.dbf output file name=/u01/app/oracle/oradata/std/undotbs01.dbf tag=TAG20160316T110744 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15 channel ORA_DISK_1: starting datafile copy input datafile file number=00004 name=/u01/app/oracle/oradata/pri/users01.dbf output file name=/u01/app/oracle/oradata/std/users01.dbf tag=TAG20160316T110744 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01 Finished backup at 16-MAR-16
sql statement: alter system archive log current
contents of Memory Script: { switch clone datafile all; } executing Memory Script
datafile 1 switched to datafile copy input datafile copy RECID=2 STAMP=906635463 file name=/u01/app/oracle/oradata/std/system01.dbf datafile 2 switched to datafile copy input datafile copy RECID=3 STAMP=906635463 file name=/u01/app/oracle/oradata/std/sysaux01.dbf datafile 3 switched to datafile copy input datafile copy RECID=4 STAMP=906635463 file name=/u01/app/oracle/oradata/std/undotbs01.dbf datafile 4 switched to datafile copy input datafile copy RECID=5 STAMP=906635463 file name=/u01/app/oracle/oradata/std/users01.dbf Finished Duplicate Db at 16-MAR-16
RMAN> 恢復(fù)數(shù)據(jù)庫(kù)結(jié)束
12：嘗試開(kāi)啟備庫(kù)
登陸并查看數(shù)據(jù)庫(kù)當(dāng)前狀態(tài)：
[oracle@std ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Tue Jan 14 16:41:50 2014 Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options
SYS@std> startup SYS@std> select status from v$instance;
STATUS ------------ MOUNTED （RMAN恢復(fù)完直接就是mount狀態(tài)）
13：備庫(kù)啟動(dòng)日志應(yīng)用（啟用備庫(kù)前確認(rèn)歸檔日志是否都已拷貝） SYS@std> alter database recover managed standby database disconnect from session; Database altered. （停止日志應(yīng)用的命令是：alter database recover managed standby database cancel;）
查看日志應(yīng)用情況： SYS@std> set pagesize 100 SYS@std> select sequence#,applied from v$archived_log order by 1;
SEQUENCE# APPLIED ---------- --------- 8 YES 9 YES 10 YES
如上，如果發(fā)現(xiàn)有個(gè)NO的，也是正常的，說(shuō)明該日志在主庫(kù)上還沒(méi)有歸檔，可以在主庫(kù)上運(yùn)行alter system switch logfile;命令來(lái)進(jìn)行日志切換，再到備庫(kù)查看日志應(yīng)用情況
14：分別查看主庫(kù)和備庫(kù)的歸檔序列號(hào)是否一致： 先在主庫(kù)手動(dòng)切換一下日志： SYS@pri> alter system switch logfile;
System altered. 然后查看主庫(kù)： SYS@pri> archive log list; SQL> archive log list; Database log mode Archive Mode Automatic archival Enabled Archive destination /u01/app/oracle/arch Oldest online log sequence 22 Next log sequence to archive 24 Current log sequence 24 備庫(kù)： SQL> archive log list; Database log mode Archive Mode Automatic archival Enabled Archive destination /u01/app/oracle/arch Oldest online log sequence 22 Next log sequence to archive 0 Current log sequence 24
結(jié)果完全一致，至此，DataGuard的搭建成功！
15：檢查命令 查看standby啟動(dòng)的DG進(jìn)程 SQL> select process,client_process,sequence#,status from v$managed_standby;
PROCESS CLIENT_P SEQUENCE# STATUS --------- -------- ---------- ------------ ARCH ARCH 23 CLOSING ARCH ARCH 0 CONNECTED //歸檔進(jìn)程 ARCH ARCH 21 CLOSING ARCH ARCH 0 CONNECTED RFS ARCH 0 IDLE RFS UNKNOWN 0 IDLE RFS LGWR 24 IDLE //歸檔傳輸進(jìn)程 RFS UNKNOWN 0 IDLE MRP0 N/A 24 APPLYING_LOG //日志應(yīng)用進(jìn)程
9 rows selected.
查看數(shù)據(jù)庫(kù)的保護(hù)模式： SQL> select database_role,protection_mode,protection_level,open_mode from v$database;
DATABASE_ROLE PROTECTION_MODE PROTECTION_LEVEL OPEN_MODE ---------------- -------------------- -------------------- -------------------- PRIMARY MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE READ WRITE
#standby 端查看，也是一樣的。 SQL> select database_role,protection_mode,protection_level,open_mode from v$database;
DATABASE_ROLE PROTECTION_MODE PROTECTION_LEVEL OPEN_MODE ---------------- -------------------- -------------------- -------------------- PHYSICAL STANDBY MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE MOUNTED 查看DG的日志信息 SQL> select * from v$dataguard_status;
16：Open Read Only standby數(shù)據(jù)庫(kù)并且開(kāi)啟實(shí)時(shí)日志應(yīng)用 SQL> shutdown immediate ORA-01109: database not open
Database dismounted. ORACLE instance shut down. SQL> startup ORACLE instance started.
Total System Global Area 1188511744 bytes Fixed Size 1364228 bytes Variable Size 754978556 bytes Database Buffers 419430400 bytes Redo Buffers 12738560 bytes Database mounted. Database opened. SQL> select database_role,protection_mode,protection_level,open_mode from v$database;
DATABASE_ROLE PROTECTION_MODE PROTECTION_LEVEL OPEN_MODE ---------------- -------------------- -------------------- -------------------- PHYSICAL STANDBY MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE READ ONLY
SQL> select process,client_process,sequence#,status from v$managed_standby;
PROCESS CLIENT_P SEQUENCE# STATUS --------- -------- ---------- ------------ ARCH ARCH 0 CONNECTED ARCH ARCH 0 CONNECTED ARCH ARCH 0 CONNECTED ARCH ARCH 26 CLOSING RFS ARCH 0 IDLE RFS UNKNOWN 0 IDLE RFS LGWR 27 IDLE
7 rows selected.
SQL> recover managed standby database using current logfile disconnect from session; Media recovery complete.
SQL> select process,client_process,sequence#,status from v$managed_standby;
PROCESS CLIENT_P SEQUENCE# STATUS --------- -------- ---------- ------------ ARCH ARCH 0 CONNECTED ARCH ARCH 0 CONNECTED ARCH ARCH 0 CONNECTED ARCH ARCH 26 CLOSING RFS ARCH 0 IDLE RFS UNKNOWN 0 IDLE RFS LGWR 27 IDLE MRP0 N/A 27 APPLYING_LOG
8 rows selected.
SQL> select process,client_process,sequence#,status from v$managed_standby;
PROCESS CLIENT_P SEQUENCE# STATUS --------- -------- ---------- ------------ ARCH ARCH 19 CLOSING ARCH ARCH 20 CLOSING ARCH ARCH 0 CONNECTED ARCH ARCH 21 CLOSING MRP0 N/A 22 WAIT_FOR_LOG RFS ARCH 0 IDLE RFS UNKNOWN 0 IDLE RFS UNKNOWN 0 IDLE RFS LGWR 22 IDLE
9 rows selected.
五：相關(guān)知識(shí)補(bǔ)充： 1：DataGuard的三種數(shù)據(jù)保護(hù)模式： （1）MAXIMIZE PROTECTION（最大保護(hù)模式）： 最大數(shù)據(jù)保護(hù)與無(wú)數(shù)據(jù)分歧，LGWR將同時(shí)傳送到備用節(jié)點(diǎn)，在主節(jié)點(diǎn)事務(wù)確認(rèn)之前，備用節(jié)點(diǎn)也必須完全收到日志數(shù)據(jù)。如果網(wǎng)絡(luò)不好，引起LGWR不能傳送數(shù)據(jù)，將引起嚴(yán)重的性能問(wèn)題，導(dǎo)致主節(jié)點(diǎn)DOWN機(jī)。
（2）MAXIMIZE AVAILABILITY（最大可用模式）： 無(wú)數(shù)據(jù)丟失模式，允許數(shù)據(jù)分歧，允許異步傳送。正常情況下運(yùn)行在最大保護(hù)模式，在主節(jié)點(diǎn)與備用節(jié)點(diǎn)的網(wǎng)絡(luò)斷開(kāi)或連接不正常時(shí)，自動(dòng)切換到最大性能模式，主節(jié)點(diǎn)的操作還是可以繼續(xù)的。在網(wǎng)絡(luò)不好的情況下有較大的性能影響。
（3）MAXIMIZE PERFORMANCE（最大性能模式）： 這種模式應(yīng)當(dāng)可以說(shuō)是從8i繼承過(guò)來(lái)的備用服務(wù)器模式，異步傳送，無(wú)數(shù)據(jù)同步檢查，可能丟失數(shù)據(jù)，但是能獲得主節(jié)點(diǎn)的最大性能。
|
|
|
