|
一：實(shí)驗(yàn)環(huán)境介紹 虛擬機(jī)系統(tǒng)： RHEL Linux 6.4（64位） 數(shù)據(jù)庫版本： Oracle 11gR2 11.2.0.4 （64位） IP地址規(guī)劃： 主數(shù)據(jù)庫 192.168.11.120 SID：pri db_name:pri db_unique_name：pri 備份數(shù)據(jù)庫 192.168.11.121 SID：std db_name:pri db_unique_name：std
安裝完成后可以通過以下命令查詢： select dbid,name,open_mode,db_unique_name from v$database; select instance_name from v$instance;
基礎(chǔ)要求：（1）安裝兩臺(tái)虛擬機(jī)分別作為主庫和備庫，硬盤大小隨意。但要保證 根分區(qū)有20G，別給太小了就行。物理內(nèi)存1G，SWAP 2G 關(guān)于Active database duplication方式：（必看） 在Oracle 10g下，我們可以使用RMAN duplicate 命令創(chuàng)建一個(gè)具有不同DBID 的復(fù)制庫。 到了Oracle 11gR2， RMAN 的duplicate 有2種方法實(shí)現(xiàn)： 1. Active database duplication （本文所使用的方式，適用于11gR2之后的版本） 2. Backup-based duplication （傳統(tǒng)方式，10g，11g通用）
Active database duplication 通過網(wǎng)絡(luò)，直接copy target 庫到auxiliary 庫，然后創(chuàng)建復(fù)制庫。這種方法就不需要先用RMAN 備份數(shù)據(jù)庫，然后將備份文件發(fā)送到auxiliary端。 這個(gè)功能的作用是非常大的。 尤其是對(duì)T級(jí)別的庫。 因?yàn)閷?duì)這樣的庫進(jìn)行備份，然后將備份集發(fā)送到備庫，在進(jìn)行duplicate 的代價(jià)是非常大的。 一備份要占用時(shí)間，二要占用備份空間，三在網(wǎng)絡(luò)傳送的時(shí)候，還需要占用帶寬和時(shí)間。所以Active database duplicate 很好的解決了以上的問題。 它對(duì)大庫的遷移非常有用。 如果是從RAC duplicate 到單實(shí)例，操作是一樣的。 如果是從單實(shí)例duplicate 到RAC。 那么先duplicate 到 單實(shí)例。 然后將單實(shí)例轉(zhuǎn)換成RAC。
二：安裝數(shù)據(jù)庫軟件前的系統(tǒng)配置 （主庫和備庫端都要做下列操作） 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)系。
主庫IP：192.168.11.120 主庫主機(jī)名：ora11g 備庫IP：192.168.11.121 備庫主機(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ì)！） 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 (備庫端設(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
三：開始安裝oracle11g數(shù)據(jù)庫軟件 （主庫和備庫端都要做下列操作） 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ù)庫的話，解壓前兩個(gè)包即可，第三個(gè)包是grid) 2. root身份執(zhí)行xhost +，然后切換到oracle用戶進(jìn)入/usr/local/src/database目錄中，執(zhí)行runInstaller 開始安裝 (database是默認(rèn)解壓完的目錄名) # xhost + # su - oracle $ ./runInstaller 注意：安裝oracle11g要求分辨率最低為1024x768,不然無法完整顯示安裝過程
安裝過程：略 注意：主庫在安裝完軟件后需要進(jìn)行DBCA建庫操作，推薦不要啟用歸檔模式，會(huì)節(jié)省時(shí)間，備庫端不要建庫！可以開啟監(jiān)聽程序
四：開始搭建Dataguard
1：將數(shù)據(jù)庫改為強(qiáng)制日志模式 （此步驟只在主庫上做） [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)建密碼文件 （此步驟只在主庫上做） 注意：兩端分別創(chuàng)建自己的密碼文件好像有問題，備庫的密碼文件需要跟主庫一致，否則導(dǎo)致日志傳輸不到備庫，有待驗(yàn)證。我最后是將主庫的密碼文件直接copy到備庫，重命名后使用。 [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)前已有的密碼文件（如果有可以不建立）
將主庫的密碼文件copy給備庫，并重命名 [oracle@pri dbs]$ scp orapwpri 192.168.11.121:$ORACLE_HOME/dbs/orapwstd
3：創(chuàng)建standby redolog日志組 （此步驟只在主庫上做） 原則： 1：standby redo log的文件大小與primary 數(shù)據(jù)庫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)系及日志組的大?。?/p> 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ù)庫才有用的參數(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日志組，大小與原來的日志組一致： 由于已經(jīng)存在group1-3,，所以group號(hào)只能從4開始 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：修改主庫的pfile參數(shù)文件 （此步驟只在主庫上做）
查看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;
修改主庫的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主庫和備庫的db_name必須一致，db_unique_name不一致 *.log_archive_config='dg_config=(pri,std)' pri主數(shù)據(jù)庫SID，std備份數(shù)據(jù)庫SID *.log_archive_dest_1='location=/u01/app/oracle/arch valid_for=(all_logfiles,all_roles) db_unique_name=pri' 主數(shù)據(jù)庫的歸檔日志路徑和SID *.log_archive_dest_2='service=std valid_for=(online_logfiles,primary_role) db_unique_name=std' 備份數(shù)據(jù)庫的SID *.log_archive_dest_state_1=enable *.log_archive_dest_state_2=enable *.log_archive_max_processes=4 *.fal_server='std' 備份數(shù)據(jù)庫的SID *.fal_client='pri' 主數(shù)據(jù)庫的SID *.db_file_name_convert='/u01/app/oracle/oradata/std','/u01/app/oracle/oradata/pri' 第一個(gè)目錄是備份數(shù)據(jù)庫數(shù)據(jù)文件路徑（備份服務(wù)器上有此目錄），第二個(gè)是主數(shù)據(jù)庫數(shù)據(jù)文件路徑 *.log_file_name_convert='/u01/app/oracle/oradata/std','/u01/app/oracle/oradata/pri' 第一個(gè)目錄是備份數(shù)據(jù)庫數(shù)據(jù)文件路徑（備份服務(wù)器上有此目錄），第二個(gè)是主數(shù)據(jù)庫數(shù)據(jù)文件路徑 *.standby_file_management='auto' 修改完畢，保存退出
手工創(chuàng)建/u01/app/oracle/arch： [oracle@pri dbs]$ mkdir –p /u01/app/oracle/arch
5：用修改過的pfile重新創(chuàng)建一個(gè)spfile，用于重啟數(shù)據(jù)庫 （此步驟只在主庫上做） 關(guān)閉數(shù)據(jù)庫： SYS@pri> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down.
用修改過的pfile重新創(chuàng)建一個(gè)spfile： SYS@pri> create spfile from pfile;
此時(shí)把數(shù)據(jù)庫改為歸檔模式： （如果當(dāng)初建庫時(shí)選擇了啟用歸檔，則此步驟忽略） 由于當(dāng)前數(shù)據(jù)庫已關(guān)閉，首先需要把數(shù)據(jù)庫啟動(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ù)庫 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ù)庫是否使用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ù)庫是使用spfile啟動(dòng)的，若沒有值，則說明是用pfile啟動(dòng)的。
確認(rèn)數(shù)據(jù)庫已經(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ài)監(jiān)聽 （主庫、備庫都要做） 主庫： [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 主庫修改后最終效果如下圖：
備庫： [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 備庫修改后最終效果如下圖：
使新增加的監(jiān)聽生效： （主庫和備庫端都要做） [oracle@pri admin]$ lsnrctl stop [oracle@pri admin]$ lsnrctl start
確認(rèn)新增加的靜態(tài)監(jiān)聽有效： 主庫： [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
備庫： [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)聽添加成功
7：編輯網(wǎng)絡(luò)服務(wù)名配置文件tnsnames.ora （主庫和備庫端都要做） [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é)果如下圖：
保證主庫和備庫的tnsnames.ora文件中的內(nèi)容完全相同，可以把修改后的文件直接傳給備庫。 [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：在備庫端，修改pfile參數(shù)文件 （只在備庫端做） 首先，在主庫端把pfile拷貝給備庫端的$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 然后在備庫端進(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主庫和備庫的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è)搭建過程最需要留意的就是主庫和備庫的PFILE配置，建議修改完后仔細(xì)對(duì)照主備庫PFILE的區(qū)別
9：在備庫端手工創(chuàng)建所需的目錄 （備庫端做，不提前創(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ù)庫 （備庫端做） [oracle@std ~]$ sqlplus / as sysdba Connected to an idle instance.
SYS@std> create spfile from pfile; File created.
將數(shù)據(jù)庫啟動(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在備庫上恢復(fù)主庫 （備庫端做） [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ù)庫結(jié)束
12：嘗試開啟備庫
登陸并查看數(shù)據(jù)庫當(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：備庫啟動(dòng)日志應(yīng)用（啟用備庫前確認(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的，也是正常的，說明該日志在主庫上還沒有歸檔，可以在主庫上運(yùn)行alter system switch logfile;命令來進(jìn)行日志切換，再到備庫查看日志應(yīng)用情況
14：分別查看主庫和備庫的歸檔序列號(hào)是否一致： 先在主庫手動(dòng)切換一下日志： SYS@pri> alter system switch logfile;
System 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 備庫： 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ù)庫的保護(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ù)庫并且開啟實(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ù)與無數(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)重的性能問題，導(dǎo)致主節(jié)點(diǎn)DOWN機(jī)。
（2）MAXIMIZE AVAILABILITY（最大可用模式）： 無數(shù)據(jù)丟失模式，允許數(shù)據(jù)分歧，允許異步傳送。正常情況下運(yùn)行在最大保護(hù)模式，在主節(jié)點(diǎn)與備用節(jié)點(diǎn)的網(wǎng)絡(luò)斷開或連接不正常時(shí)，自動(dòng)切換到最大性能模式，主節(jié)點(diǎn)的操作還是可以繼續(xù)的。在網(wǎng)絡(luò)不好的情況下有較大的性能影響。
（3）MAXIMIZE PERFORMANCE（最大性能模式）： 這種模式應(yīng)當(dāng)可以說是從8i繼承過來的備用服務(wù)器模式，異步傳送，無數(shù)據(jù)同步檢查，可能丟失數(shù)據(jù)，但是能獲得主節(jié)點(diǎn)的最大性能。
|
|
|
