配置DataGuard
1、概述和说明
单实例数据库.只需安装grid、oracle software、不需要安装oracle database.
2、安装前准备工作
preusers.sh
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
#!/usr/bin/bash groupadd -g 1000 oinstall groupadd -g 1200 asmadmin groupadd -g 1201 asmdba groupadd -g 1202 asmoper groupadd -g 1300 dba groupadd -g 1301 oper useradd -u 1100 -g oinstall -G dba,asmadmin,asmdba,asmoper -d /home/grid -s /bin/bash -c "grid Infrastructure Owner" grid echo "grid" | passwd --stdin grid echo 'export PS1="`/bin/hostname -s`-> "'>> /home/grid/.bash_profile echo "export TMP=/tmp">> /home/grid/.bash_profile echo 'export TMPDIR=$TMP'>>/home/grid/.bash_profile echo "export ORACLE_SID=+ASM">> /home/grid/.bash_profile echo "export ORACLE_BASE=/u01/app/grid">> /home/grid/.bash_profile echo "export ORACLE_HOME=/u01/app/11.2.0/grid">> /home/grid/.bash_profile echo "export ORACLE_TERM=xterm">> /home/grid/.bash_profile echo "export NLS_DATE_FORMAT='yyyy/mm/dd hh24:mi:ss'" >> /home/grid/.bash_profile echo 'export TNS_ADMIN=$ORACLE_HOME/network/admin' >> /home/grid/.bash_profile echo 'export PATH=/usr/sbin:$PATH'>> /home/grid/.bash_profile echo 'export PATH=$ORACLE_HOME/bin:$PATH'>> /home/grid/.bash_profile echo 'export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib' >> /home/grid/.bash_profile echo 'export CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib' >> /home/grid/.bash_profile echo "export EDITOR=vi" >> /home/grid/.bash_profile echo "export LANG=en_US" >> /home/grid/.bash_profile echo "export NLS_LANG=american_america.AL32UTF8" >> /home/grid/.bash_profile echo "umask 022">> /home/grid/.bash_profile useradd -u 1101 -g oinstall -G dba,oper,asmdba,asmadmin -d /home/oracle -s /bin/bash -c "Oracle Software Owner" oracle echo "oracle" | passwd --stdin oracle echo 'export PS1="`/bin/hostname -s`-> "' >> /home/oracle/.bash_profile echo "export TMP=/tmp" >> /home/oracle/.bash_profile echo 'export TMPDIR=$TMP'>>/home/oracle/.bash_profile echo "export ORACLE_HOSTNAME=rhels5532.localdomain">> /home/oracle/.bash_profile echo "export ORACLE_SID=phydb">> /home/oracle/.bash_profile echo "export ORACLE_BASE=/u01/app/oracle">> /home/oracle/.bash_profile echo 'export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1'>> /home/oracle/.bash_profile echo "export ORACLE_UNQNAME=phydb">> /home/oracle/.bash_profile echo 'export TNS_ADMIN=$ORACLE_HOME/network/admin' >> /home/oracle/.bash_profile echo "export ORACLE_TERM=xterm">> /home/oracle/.bash_profile echo 'export PATH=/usr/sbin:$PATH'>> /home/oracle/.bash_profile echo 'export PATH=$ORACLE_HOME/bin:$PATH'>> /home/oracle/.bash_profile echo 'export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib'>> /home/oracle/.bash_profile echo 'export CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib' >> /home/oracle/.bash_profileecho "export DISPLAY=192.168.56.1:0.0" >> /home/oracle/.bash_profile
predir.sh
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
#!/usr/bin/bash mkdir -p /u01/app/grid mkdir -p /u01/app/11.2.0/grid mkdir -p /u01/app/oracle chown -R oracle:oinstall /u01 chown -R grid:oinstall /u01/app/grid chown -R grid:oinstall /u01/app/11.2.0 chmod -R 775 /u01
prelimits.sh
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
#!/usr/bin/bash cp /etc/security/limits.conf /etc/security/limits.conf.bak echo "oracle soft nproc 2047" >>/etc/security/limits.conf echo "oracle hard nproc 16384" >>/etc/security/limits.conf echo "oracle soft nofile 1024" >>/etc/security/limits.conf echo "oracle hard nofile 65536" >>/etc/security/limits.conf echo "grid soft nproc 2047" >>/etc/security/limits.conf echo "grid hard nproc 16384" >>/etc/security/limits.conf echo "grid soft nofile 1024" >>/etc/security/limits.conf echo "grid hard nofile 65536" >>/etc/security/limits.conf echo "Modifing the /etc/security/limits.conf has been succeed."
prelogin.sh
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
#!/usr/bin/bash echo "Now modify the /etc/pam.d/login,but with a backup named /etc/pam.d/login.bak" cp /etc/pam.d/login /etc/pam.d/login.bak echo "session required /lib/security/pam_limits.so" >>/etc/pam.d/login echo "session required pam_limits.so" >>/etc/pam.d/login echo "Modifing the /etc/pam.d/login has been succeed."
preprofile.sh
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
#!/usr/bin/bash echo "Now modify the /etc/profile,but with a backup named /etc/profile.bak" cp /etc/profile /etc/profile.bak echo 'if [ $USER = "oracle" ]||[ $USER = "grid" ]; then' >> /etc/profile echo 'if [ $SHELL = "/bin/ksh" ]; then' >> /etc/profile echo 'ulimit -p 16384' >> /etc/profile echo 'ulimit -n 65536' >> /etc/profile echo 'else' >> /etc/profile echo 'ulimit -u 16384 -n 65536' >> /etc/profile echo 'fi' >> /etc/profile echo 'fi' >> /etc/profile echo "Modifing the /etc/profile has been succeed."
presysctl.sh
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
#!/usr/bin/bash echo "Now modify the /etc/sysctl.conf,but with a backup named /etc/sysctl.bak" cp /etc/sysctl.conf /etc/sysctl.conf.bak echo "fs.aio-max-nr = 1048576" >> /etc/sysctl.conf echo "fs.file-max = 6815744" >> /etc/sysctl.conf echo "kernel.shmall = 2097152" >> /etc/sysctl.conf echo "kernel.shmmax = 1054472192" >> /etc/sysctl.conf echo "kernel.shmmni = 4096" >> /etc/sysctl.conf echo "kernel.sem = 250 32000 100 128" >> /etc/sysctl.conf echo "net.ipv4.ip_local_port_range = 9000 65500" >> /etc/sysctl.conf echo "net.core.rmem_default = 262144" >> /etc/sysctl.conf echo "net.core.rmem_max = 4194304" >> /etc/sysctl.conf echo "net.core.wmem_default = 262144" >> /etc/sysctl.conf echo "net.core.wmem_max = 1048586" >> /etc/sysctl.conf echo "net.ipv4.tcp_wmem = 262144 262144 262144" >> /etc/sysctl.conf echo "net.ipv4.tcp_rmem = 4194304 4194304 4194304" >> /etc/sysctl.conf echo "Modifing the /etc/sysctl.conf has been succeed." echo "Now make the changes take effect....." sysctl -p
莫要忘记将上述脚本赋予可执行权限.
>>配置虚拟内存
默认虚拟内存大小为1G,搭建DG建议调整为3G.如何搭建,请参考文章
>>配置共享磁盘
磁盘分配情况(三块共享磁盘和一块普通磁盘)
- GRIDDG 存放grid软件安装文件
- DATA 数据库文件存放
- FLASH 闪回数据文件存放
- 普通磁盘 用来存放数据库备份文件
- 做文件系统
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
[root@node1 ~]# fdisk /dev/sdb Device contains neither a valid DOS partition table, nor Sun, SGI or OSF disklabel Building a new DOS disklabel with disk identifier 0x437fc4bc. Changes will remain in memory only, until you decide to write them. After that, of course, the previous content won't be recoverable. Warning: invalid flag 0x0000 of partition table 4 will be corrected by w(rite) WARNING: DOS-compatible mode is deprecated. It's strongly recommended to switch off the mode (command 'c') and change display units to sectors (command 'u'). Command (m for help): n Command action e extended p primary partition (1-4) p Partition number (1-4): 1 First cylinder (1-1305, default 1): Using default value 1 Last cylinder, +cylinders or +size{K,M,G} (1-1305, default 1305): Using default value 1305 Command (m for help): w The partition table has been altered! Calling ioctl() to re-read partition table. Syncing disks.
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
[root@11gdg ~]# mkfs.ext4 /dev/sdb1 mke2fs 1.41.12 (17-May-2010) Filesystem label= OS type: Linux Block size=4096 (log=2) Fragment size=4096 (log=2) Stride=0 blocks, Stripe width=0 blocks 655360 inodes, 2620595 blocks 131029 blocks (5.00%) reserved for the super user First data block=0 Maximum filesystem blocks=2684354560 80 block groups 32768 blocks per group, 32768 fragments per group 8192 inodes per group Superblock backups stored on blocks: 32768, 98304, 163840, 229376, 294912, 819200, 884736, 1605632 Writing inode tables: done Creating journal (32768 blocks): done Writing superblocks and filesystem accounting information: done This filesystem will be automatically checked every 31 mounts or 180 days, whichever comes first. Use tune2fs -c or -i to override.
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
[root@11gdg /]# mount -t ext4 /dev/sdb1 /rman_backup/
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
[root@11gdg /]# df -Th Filesystem Type Size Used Avail Use% Mounted on /dev/sda2 ext4 16G 2.9G 13G 20% / tmpfs tmpfs 751M 0 751M 0% /dev/shm /dev/sdb1 ext4 9.9G 151M 9.2G 2% /rman_backup
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
# # /etc/fstab # Created by anaconda on Fri Jun 28 01:39:52 2013 # # Accessible filesystems, by reference, are maintained under '/dev/disk' # See man pages fstab(5), findfs(8), mount(8) and/or blkid(8) for more info # UUID=d2abcbb7-dc9b-4fdd-9971-4f1060f19187 / ext4 defaults 1 1 UUID=a42a3594-4da2-4371-b20c-2ca36d569356 swap swap defaults 0 0 tmpfs /dev/shm tmpfs defaults 0 0 devpts /dev/pts devpts gid=5,mode=620 0 0 sysfs /sys sysfs defaults 0 0 proc /proc proc defaults 0 0 /dev/sdb1 /rman_backup ext4 defaults 0 0
>>安装ASM packages
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
[root@11gdg ~]# rpm -ivh oracleasm-support-2.1.5-1.el6.x86_64.rpm Preparing... ########################################### [100%] 1:oracleasm-support ########################################### [100%] [root@11gdg ~]# rpm -ivh oracleasmlib-2.0.4-1.el6.x86_64.rpm Preparing... ########################################### [100%] 1:oracleasmlib ########################################### [100%] [root@11gdg ~]# which oracleasm /usr/sbin/oracleasm
3、安装Grid Infrastructure
在rac环境下安装grid时,需要执行./runcluvfy.sh stage -pre crsinst验证,但是在dataguard中,则不需要执行此验证.
4、安装Oracle 软件
在安装grid infrastructure与oracle software时,由于与检查一项有时会报警告信息,在解决那些警告信心后,接着执行真正的安装操作时,有时会报java.lang.null错误,此时可以退出当前安装,然后再从头开始安装即可.估计是oracle的一个bug.
查看主库是否处于强制日志force logging
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
[root@node1 ~]# su - oracle node1-> sqlplus / as sysdba SQL*Plus: Release 11.2.0.3.0 Production on Wed Jul 3 17:31:40 2013 Copyright (c) 1982, 2011, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options SQL> select name,log_mode,force_logging from gv$database; NAME LOG_MODE FOR --------- ------------ --- DEVDB ARCHIVELOG YES DEVDB ARCHIVELOG YES
假如force logging 为no,通过命令alter database force logging;完成.
10、RAC主库配置单实例ActiveDataGuard
10.1、RAC主库准备工作
1、RAC主库必须设置为归档模式
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
SQL> select open_mode,log_mode from v$database; OPEN_MODE LOG_MODE -------------------- ------------ READ WRITE ARCHIVELOG
2、RAC主库必须置为ForceLogging模式
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
SQL> select name,log_mode,force_logging from gv$database; NAME LOG_MODE FOR --------- ------------ --- DEVDB ARCHIVELOG NO DEVDB ARCHIVELOG NO SQL> alter database force logging; Database altered. SQL> select name,log_mode,force_logging from gv$database; NAME LOG_MODE FOR --------- ------------ --- DEVDB ARCHIVELOG YES DEVDB ARCHIVELOG YES
3、RAC主库执行RMAN全备
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
RMAN> run{ 2> allocate channel c1 type disk; 3> allocate channel c2 type disk; 4> allocate channel c3 type disk; 5> allocate channel c4 type disk; 6> backup database format '/rman_backup/Full_%U.bak'; 7> release channel c1; release channel c2; 9> release channel c3; 10> release channel c4; 11> } allocated channel: c1 channel c1: SID=157 instance=devdb1 device type=DISK allocated channel: c2 channel c2: SID=28 instance=devdb1 device type=DISK allocated channel: c3 channel c3: SID=141 instance=devdb1 device type=DISK allocated channel: c4 channel c4: SID=29 instance=devdb1 device type=DISK Starting backup at 2013/07/07 18:01:26 channel c1: starting full datafile backup set channel c1: specifying datafile(s) in backup set input datafile file number=00001 name=+DATA/devdb/datafile/system.256.820154751 input datafile file number=00006 name=+DATA/devdb/datafile/undotbs2.265.820155189 channel c1: starting piece 1 at 2013/07/07 18:01:29 channel c2: starting full datafile backup set channel c2: specifying datafile(s) in backup set input datafile file number=00002 name=+DATA/devdb/datafile/sysaux.257.820154753 input datafile file number=00004 name=+DATA/devdb/datafile/users.259.820154753 channel c2: starting piece 1 at 2013/07/07 18:01:30 channel c3: starting full datafile backup set channel c3: specifying datafile(s) in backup set input datafile file number=00005 name=+DATA/devdb/datafile/example.264.820154917 input datafile file number=00003 name=+DATA/devdb/datafile/undotbs1.258.820154753 channel c3: starting piece 1 at 2013/07/07 18:01:34 channel c4: starting full datafile backup set channel c4: specifying datafile(s) in backup set including current control file in backup set channel c4: starting piece 1 at 2013/07/07 18:02:16 channel c4: finished piece 1 at 2013/07/07 18:02:54 piece handle=/rman_backup/Full_04oe5mru_1_1.bak tag=TAG20130707T180127 comment=NONE channel c4: backup set complete, elapsed time: 00:00:38 channel c4: starting full datafile backup set channel c4: specifying datafile(s) in backup set including current SPFILE in backup set channel c4: starting piece 1 at 2013/07/07 18:02:59 channel c3: finished piece 1 at 2013/07/07 18:03:16 piece handle=/rman_backup/Full_03oe5mrr_1_1.bak tag=TAG20130707T180127 comment=NONE channel c3: backup set complete, elapsed time: 00:01:42 channel c4: finished piece 1 at 2013/07/07 18:03:16 piece handle=/rman_backup/Full_05oe5muh_1_1.bak tag=TAG20130707T180127 comment=NONE channel c4: backup set complete, elapsed time: 00:00:17 channel c1: finished piece 1 at 2013/07/07 18:03:36 piece handle=/rman_backup/Full_01oe5mro_1_1.bak tag=TAG20130707T180127 comment=NONE channel c1: backup set complete, elapsed time: 00:02:07 channel c2: finished piece 1 at 2013/07/07 18:03:36 piece handle=/rman_backup/Full_02oe5mrp_1_1.bak tag=TAG20130707T180127 comment=NONE channel c2: backup set complete, elapsed time: 00:02:06 Finished backup at 2013/07/07 18:03:36 released channel: c1 released channel: c2 released channel: c3 released channel: c4 RMAN> backup archivelog all format '/rman_backup/ARC_%U.bak'; Starting backup at 2013/07/07 18:04:23 current log archived allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=157 instance=devdb1 device type=DISK channel ORA_DISK_1: starting archived log backup set channel ORA_DISK_1: specifying archived log(s) in backup set input archived log thread=1 sequence=6 RECID=4 STAMP=820168345 input archived log thread=2 sequence=1 RECID=1 STAMP=820155385 input archived log thread=2 sequence=2 RECID=2 STAMP=820157344 input archived log thread=2 sequence=3 RECID=3 STAMP=820159547 input archived log thread=2 sequence=4 RECID=7 STAMP=820168919 input archived log thread=1 sequence=7 RECID=5 STAMP=820168304 input archived log thread=1 sequence=8 RECID=6 STAMP=820168917 input archived log thread=1 sequence=9 RECID=8 STAMP=820168919 input archived log thread=2 sequence=5 RECID=9 STAMP=820171556 input archived log thread=2 sequence=6 RECID=11 STAMP=820173866 input archived log thread=1 sequence=10 RECID=10 STAMP=820173865 channel ORA_DISK_1: starting piece 1 at 2013/07/07 18:04:27 channel ORA_DISK_1: finished piece 1 at 2013/07/07 18:04:34 piece handle=/rman_backup/ARC_06oe5n1b_1_1.bak tag=TAG20130707T180427 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:07 Finished backup at 2013/07/07 18:04:34
4、RAC主库执行创建物理备库控制文件
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
RMAN> backup device type disk format '/rman_backup/standby_%U.ctl' current controlfile for standby; Starting backup at 2013/07/07 18:06:59 using channel ORA_DISK_1 channel ORA_DISK_1: starting full datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set including standby control file in backup set channel ORA_DISK_1: starting piece 1 at 2013/07/07 18:07:03 channel ORA_DISK_1: finished piece 1 at 2013/07/07 18:07:04 piece handle=/rman_backup/standby_07oe5n64_1_1.ctl tag=TAG20130707T180700 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01 Finished backup at 2013/07/07 18:07:04 RMAN>
5、RAC主库创建物理备库初始化参数文件
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
SQL> show parameter spfile; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ spfile string +DATA/devdb/spfiledevdb.ora SQL> create pfile='/rman_backup/initphydb.ora' from spfile; File created. SQL>
6、RAC主库修改口令文件,使双节点SYS用户口令一致
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
SQL> alter user sys identified by Oracle168; User altered. SQL>
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
SQL> show user USER is "SYS" SQL> alter user sys identified by Oracle168; User altered. SQL>
配置完口令文件之后,最好在节点上进行登陆验证一下
验证之前,需要现在各自节点中配置一下tnsnames.ora文件
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
devdb1 = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = racnode1.localdomain)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = devdb) (INSTANCE_NAME = devdb1) ) ) devdb2 = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = racnode2.localdomain)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = devdb) (INSTANCE_NAME = devdb2) ) )
10.2、物理备库准备工作
1、FTP主库备份文件、备库控制文件、备库参数文件至物理备库服务器
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
[root@node1 rman_backup]# scp ARC_06oe5n1b_1_1.bak Full_0* initphydb.ora standby_07oe5n64_1_1.ctl 192.168.1.212:/rman_backup/ The authenticity of host '192.168.1.212 (192.168.1.212)' can't be established. RSA key fingerprint is 24:d2:2e:9c:ed:0b:ca:f9:a7:94:3f:4d:e6:b7:ab:1c. Are you sure you want to continue connecting (yes/no)? yes Warning: Permanently added '192.168.1.212' (RSA) to the list of known hosts. root@192.168.1.212's password: ARC_06oe5n1b_1_1.bak 100% 95MB 9.5MB/s 00:10 Full_01oe5mro_1_1.bak 100% 626MB 2.2MB/s 04:48 Full_02oe5mrp_1_1.bak 100% 437MB 2.4MB/s 03:05 Full_03oe5mrr_1_1.bak 100% 82MB 9.1MB/s 00:09 Full_04oe5mru_1_1.bak 100% 18MB 8.8MB/s 00:02 Full_05oe5muh_1_1.bak 100% 96KB 96.0KB/s 00:00 initphydb.ora 100% 1345 1.3KB/s 00:00 standby_07oe5n64_1_1.ctl 100% 18MB 17.7MB/s 00:01 [root@node1 rman_backup]#
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
[root@11gdg rman_backup]# ll total 1306684 -rw-r----- 1 root root 99957248 Jul 8 02:10 ARC_06oe5n1b_1_1.bak -rw-r----- 1 root root 656031744 Jul 8 02:12 Full_01oe5mro_1_1.bak -rw-r----- 1 root root 458571776 Jul 8 02:13 Full_02oe5mrp_1_1.bak -rw-r----- 1 root root 86261760 Jul 8 02:14 Full_03oe5mrr_1_1.bak -rw-r----- 1 root root 18546688 Jul 8 02:14 Full_04oe5mru_1_1.bak -rw-r----- 1 root root 98304 Jul 8 02:14 Full_05oe5muh_1_1.bak -rw-r--r-- 1 root root 1345 Jul 8 02:14 initphydb.ora drwx------ 2 oracle oinstall 16384 Jul 8 01:02 lost+found -rw-r----- 1 root root 18546688 Jul 8 02:14 standby_07oe5n64_1_1.ctl [root@11gdg rman_backup]# cd .. [root@11gdg /]# ll total 116 dr-xr-xr-x. 2 root root 4096 Jul 6 21:59 bin dr-xr-xr-x. 4 root root 4096 Jun 28 01:48 boot drwxr-xr-x. 2 root root 4096 Apr 29 2012 cgroup drwxr-xr-x 17 root root 4120 Jul 8 01:07 dev drwxr-xr-x. 105 root root 12288 Jul 8 01:07 etc drwxr-xr-x. 4 root root 4096 Jul 6 21:01 home dr-xr-xr-x. 13 root root 4096 Jul 6 21:53 lib dr-xr-xr-x. 9 root root 12288 Jul 6 21:53 lib64 drwx------. 2 root root 16384 Jun 28 01:34 lost+found drwxr-xr-x. 2 root root 4096 Nov 1 2011 media drwxr-xr-x 2 root root 0 Jul 8 01:07 misc drwxr-xr-x. 2 root root 4096 Nov 1 2011 mnt drwxr-xr-x 2 root root 0 Jul 8 01:07 net drwxr-xr-x. 5 root root 4096 Jul 6 22:15 opt dr-xr-xr-x 126 root root 0 Jul 8 01:06 proc drwxrwxr-x 3 oracle oinstall 4096 Jul 8 02:14 rman_backup dr-xr-x---. 4 root root 4096 Jul 8 01:50 root dr-xr-xr-x. 2 root root 12288 Jul 6 21:42 sbin drwxr-xr-x. 2 root root 4096 Jun 28 01:39 selinux drwxr-xr-x. 2 root root 4096 Nov 1 2011 srv drwxr-xr-x 13 root root 0 Jul 8 01:06 sys drwxrwxrwt. 8 root root 4096 Jul 8 01:50 tmp drwxrwxr-x 3 oracle oinstall 4096 Jul 6 21:02 u01 drwxr-xr-x. 13 root root 4096 Jun 28 01:41 usr drwxr-xr-x. 21 root root 4096 Jun 28 01:47 var [root@11gdg /]# chown -R oracle:oinstall rman_backup/ [root@11gdg /]# cd rman_backup/ [root@11gdg rman_backup]# ll total 1306684 -rw-r----- 1 oracle oinstall 99957248 Jul 8 02:10 ARC_06oe5n1b_1_1.bak -rw-r----- 1 oracle oinstall 656031744 Jul 8 02:12 Full_01oe5mro_1_1.bak -rw-r----- 1 oracle oinstall 458571776 Jul 8 02:13 Full_02oe5mrp_1_1.bak -rw-r----- 1 oracle oinstall 86261760 Jul 8 02:14 Full_03oe5mrr_1_1.bak -rw-r----- 1 oracle oinstall 18546688 Jul 8 02:14 Full_04oe5mru_1_1.bak -rw-r----- 1 oracle oinstall 98304 Jul 8 02:14 Full_05oe5muh_1_1.bak -rw-r--r-- 1 oracle oinstall 1345 Jul 8 02:14 initphydb.ora drwx------ 2 oracle oinstall 16384 Jul 8 01:02 lost+found -rw-r----- 1 oracle oinstall 18546688 Jul 8 02:14 standby_07oe5n64_1_1.ctl [root@11gdg rman_backup]#
2、物理备库创建口令文件
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
[root@node1 rman_backup]# su - oracle node1-> cd /u01/app/oracle/product/11.2.0/db_1/dbs/ node1-> ll total 18080 -rw-rw---- 1 oracle asmadmin 1544 Jul 7 17:28 hc_devdb1.dat -rw-r----- 1 oracle oinstall 37 Jul 7 12:56 initdevdb1.ora -rw-r--r-- 1 oracle oinstall 2851 May 15 2009 init.ora -rw-r----- 1 oracle oinstall 1536 Jul 7 18:10 orapwdevdb1 -rw-r----- 1 oracle asmadmin 18497536 Jul 7 18:07 snapcf_devdb1.f node1-> scp orapwdevdb1 orapwdevdb1 node2:/u01/app/oracle/product/11.2.0/db_1/dbs/orapwdevdb2 scp: /u01/app/oracle/product/11.2.0/db_1/dbs/orapwdevdb2: No such file or directory node1-> scp orapwdevdb1 node2:/u01/app/oracle/product/11.2.0/db_1/dbs/orapwdevdb2 orapwdevdb1 100% 1536 1.5KB/s 00:00 node1-> scp orapwdevdb1 192.168.1.212:/u01/app/oracle/product/11.2.0/db_1/dbs/orapwphydb The authenticity of host '192.168.1.212 (192.168.1.212)' can't be established. RSA key fingerprint is 24:d2:2e:9c:ed:0b:ca:f9:a7:94:3f:4d:e6:b7:ab:1c. Are you sure you want to continue connecting (yes/no)? yes Warning: Permanently added '192.168.1.212' (RSA) to the list of known hosts. oracle@192.168.1.212's password: orapwdevdb1 100% 1536 1.5KB/s 00:00 node1->
3、物理备库初始化参数文件修改
4、配置RAC主库、物理备库的tnsnames.ora文件
10.3、创建物理备库
1、物理备库启动NOMOUNT状态
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
11gdg-> id uid=1101(oracle) gid=1000(oinstall) groups=1000(oinstall),1200(asmadmin),1201(asmdba),1300(dba),1301(oper) 11gdg-> env | grep ORA ORACLE_UNQNAME=phydb ORACLE_SID=phydb ORACLE_BASE=/u01/app/oracle ORACLE_HOSTNAME=11gdg.localdomain ORACLE_TERM=xterm ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1 11gdg-> sqlplus / as sysdba SQL*Plus: Release 11.2.0.3.0 Production on Mon Jul 8 07:19:08 2013 Copyright (c) 1982, 2011, Oracle. All rights reserved. Connected to an idle instance. SQL> startup nomount; ORACLE instance started. Total System Global Area 839282688 bytes Fixed Size 2233000 bytes Variable Size 574623064 bytes Database Buffers 255852544 bytes Redo Buffers 6574080 bytes SQL>
2、RMAN恢复备库控制文件
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
RMAN> restore standby controlfile from '/rman_backup/standby_07oe5n64_1_1.ctl'; Starting restore at 2013/07/08 07:23:02 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=12 device type=DISK channel ORA_DISK_1: restoring control file channel ORA_DISK_1: restore complete, elapsed time: 00:00:08 output file name=+DATA/phydb/controlfile/cont.ctl Finished restore at 2013/07/08 07:23:11 RMAN>
3、MOUNT物理备库
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
SQL> select open_mode from v$database; select open_mode from v$database * ERROR at line 1: ORA-01507: database not mounted SQL> alter database mount; Database altered. SQL>
4、RMAN restore物理备库
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
11gdg-> rman target / Recovery Manager: Release 11.2.0.3.0 - Production on Mon Jul 8 07:26:14 2013 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. connected to target database: DEVDB (DBID=724884837, not open) RMAN> run{ 2> allocate channel c1 type disk; 3> allocate channel c2 type disk; 4> allocate channel c3 type disk; 5> allocate channel c4 type disk; 6> restore database; 7> release channel c1; 8> release channel c2; 9> release channel c3; 10> release channel c4; 11> } using target database control file instead of recovery catalog allocated channel: c1 channel c1: SID=139 device type=DISK allocated channel: c2 channel c2: SID=140 device type=DISK allocated channel: c3 channel c3: SID=16 device type=DISK allocated channel: c4 channel c4: SID=141 device type=DISK Starting restore at 2013/07/08 07:26:30 Starting implicit crosscheck backup at 2013/07/08 07:26:30 Crosschecked 6 objects Finished implicit crosscheck backup at 2013/07/08 07:26:34 Starting implicit crosscheck copy at 2013/07/08 07:26:34 Finished implicit crosscheck copy at 2013/07/08 07:26:35 searching for all files in the recovery area cataloging files... no files cataloged channel c1: starting datafile backup set restore channel c1: specifying datafile(s) to restore from backup set channel c1: restoring datafile 00003 to +DATA/phydb/datafile/undotbs1.258.820154753 channel c1: restoring datafile 00005 to +DATA/phydb/datafile/example.264.820154917 channel c1: reading from backup piece /rman_backup/Full_03oe5mrr_1_1.bak channel c2: starting datafile backup set restore channel c2: specifying datafile(s) to restore from backup set channel c2: restoring datafile 00002 to +DATA/phydb/datafile/sysaux.257.820154753 channel c2: restoring datafile 00004 to +DATA/phydb/datafile/users.259.820154753 channel c2: reading from backup piece /rman_backup/Full_02oe5mrp_1_1.bak channel c3: starting datafile backup set restore channel c3: specifying datafile(s) to restore from backup set channel c3: restoring datafile 00001 to +DATA/phydb/datafile/system.256.820154751 channel c3: restoring datafile 00006 to +DATA/phydb/datafile/undotbs2.265.820155189 channel c3: reading from backup piece /rman_backup/Full_01oe5mro_1_1.bak channel c1: piece handle=/rman_backup/Full_03oe5mrr_1_1.bak tag=TAG20130707T180127 channel c1: restored backup piece 1 channel c1: restore complete, elapsed time: 00:03:16 channel c2: piece handle=/rman_backup/Full_02oe5mrp_1_1.bak tag=TAG20130707T180127 channel c2: restored backup piece 1 channel c2: restore complete, elapsed time: 00:03:47 channel c3: piece handle=/rman_backup/Full_01oe5mro_1_1.bak tag=TAG20130707T180127 channel c3: restored backup piece 1 channel c3: restore complete, elapsed time: 00:03:57 Finished restore at 2013/07/08 07:30:33 released channel: c1 released channel: c2 released channel: c3 released channel: c4 RMAN>
5、备库上创建standbylogfile
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
11gdg-> sqlplus / as sysdba SQL*Plus: Release 11.2.0.3.0 Production on Mon Jul 8 07:31:36 2013 Copyright (c) 1982, 2011, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options SQL> select * from v$standby_log; no rows selected SQL> select name from v$datafile 2 union 3 select name from v$controlfile 4 union 5 select member from v$logfile; NAME -------------------------------------------------------------------------------- +DATA/phydb/controlfile/cont.ctl +DATA/phydb/datafile/example.257.820221997 +DATA/phydb/datafile/sysaux.258.820221997 +DATA/phydb/datafile/system.259.820221997 +DATA/phydb/datafile/undotbs1.260.820221997 +DATA/phydb/datafile/undotbs2.262.820221999 +DATA/phydb/datafile/users.261.820221997 +DATA/phydb/onlinelog/group_1.261.820154859 +DATA/phydb/onlinelog/group_2.262.820154865 +DATA/phydb/onlinelog/group_3.266.820155371 +DATA/phydb/onlinelog/group_4.267.820155375 NAME -------------------------------------------------------------------------------- +FLASH/devdb/onlinelog/group_1.257.820154861 +FLASH/devdb/onlinelog/group_2.258.820154865 +FLASH/devdb/onlinelog/group_3.259.820155373 +FLASH/devdb/onlinelog/group_4.260.820155377 15 rows selected. SQL>
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
SQL> select dbid,name,switchover_status,db_unique_name,database_role,open_mode,current_scn from v$database; DBID NAME SWITCHOVER_STATUS DB_UNIQUE_NAME DATABASE_ROLE OPEN_MODE CURRENT_SCN ---------- --------- -------------------- ------------------------------ ---------------- -------------------- ----------- 724884837 DEVDB TO PRIMARY phydb PHYSICAL STANDBY MOUNTED 1211611 SQL>
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
SQL> col member for a60 SQL> select * from v$logfile; GROUP# STATUS TYPE MEMBER IS_ ---------- ------- ------- ------------------------------------------------------------ --- 2 ONLINE +DATA/phydb/onlinelog/group_2.262.820154865 NO 2 ONLINE +FLASH/devdb/onlinelog/group_2.258.820154865 NO 1 ONLINE +DATA/phydb/onlinelog/group_1.261.820154859 NO 1 ONLINE +FLASH/devdb/onlinelog/group_1.257.820154861 NO 3 ONLINE +DATA/phydb/onlinelog/group_3.266.820155371 NO 3 ONLINE +FLASH/devdb/onlinelog/group_3.259.820155373 NO 4 ONLINE +DATA/phydb/onlinelog/group_4.267.820155375 NO 4 ONLINE +FLASH/devdb/onlinelog/group_4.260.820155377 NO 8 rows selected. SQL>
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
SQL> select * from v$log; GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME ---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ------------------- ------------ ------------------- 1 1 11 52428800 512 2 NO CURRENT 1211333 2013/07/07 18:04:23 2.8147E+14 2 1 10 52428800 512 2 YES ACTIVE 1200276 2013/07/07 17:28:56 1211333 2013/07/07 18:04:23 3 2 7 52428800 512 2 NO CURRENT 1211337 2013/07/07 18:04:13 2.8147E+14 4 2 6 52428800 512 2 YES ACTIVE 1199675 2013/07/07 17:25:54 1211337 2013/07/07 18:04:13 SQL> alter database add standby logfile thread 1 group 5 size 50m,group 6 size 50m,group 7 size 50m; Database altered. SQL> alter database add standby logfile thread 2 group 8 size 50m,group 9 size 50m,group 10 size 50m; Database altered. SQL> select * from v$logfile order by 1; GROUP# STATUS TYPE MEMBER IS_ ---------- ------- ------- ------------------------------------------------------------ --- 1 ONLINE +DATA/phydb/onlinelog/group_1.261.820154859 NO 1 ONLINE +FLASH/devdb/onlinelog/group_1.257.820154861 NO 2 ONLINE +DATA/phydb/onlinelog/group_2.262.820154865 NO 2 ONLINE +FLASH/devdb/onlinelog/group_2.258.820154865 NO 3 ONLINE +DATA/phydb/onlinelog/group_3.266.820155371 NO 3 ONLINE +FLASH/devdb/onlinelog/group_3.259.820155373 NO 4 ONLINE +DATA/phydb/onlinelog/group_4.267.820155375 NO 4 ONLINE +FLASH/devdb/onlinelog/group_4.260.820155377 NO 5 STANDBY +DATA/phydb/onlinelog/group_5.263.820257945 NO 5 STANDBY +FLASH/phydb/onlinelog/group_5.256.820257955 YES 6 STANDBY +DATA/phydb/onlinelog/group_6.264.820257957 NO GROUP# STATUS TYPE MEMBER IS_ ---------- ------- ------- ------------------------------------------------------------ --- 6 STANDBY +FLASH/phydb/onlinelog/group_6.257.820257959 YES 7 STANDBY +DATA/phydb/onlinelog/group_7.265.820257961 NO 7 STANDBY +FLASH/phydb/onlinelog/group_7.258.820257961 YES 8 STANDBY +DATA/phydb/onlinelog/group_8.266.820258023 NO 8 STANDBY +FLASH/phydb/onlinelog/group_8.259.820258025 YES 9 STANDBY +DATA/phydb/onlinelog/group_9.267.820258025 NO 9 STANDBY +FLASH/phydb/onlinelog/group_9.260.820258029 YES 10 STANDBY +DATA/phydb/onlinelog/group_10.268.820258029 NO 10 STANDBY +FLASH/phydb/onlinelog/group_10.261.820258031 YES 20 rows selected. SQL>
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
SQL> select * from v$log; GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME ---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ------------------- ------------ ------------------- 1 1 13 52428800 512 2 NO CURRENT 1359022 2013/07/08 09:12:06 2.8147E+14 2 1 12 52428800 512 2 YES INACTIVE 1306889 2013/07/08 05:00:48 1359022 2013/07/08 09:12:06 3 2 9 52428800 512 2 NO CURRENT 1359741 2013/07/08 09:16:07 2.8147E+14 2013/07/08 09:16:07 4 2 8 52428800 512 2 YES INACTIVE 1359021 2013/07/08 09:12:04 1359028 2013/07/08 09:12:10 SQL> select 52428800/1024/1024 MB from dual; MB ---------- 50 SQL>
6、主库参数调整
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
SQL> show parameter spfile; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ spfile string +DATA/devdb/spfiledevdb.ora SQL> show parameter log_arch; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ log_archive_config string log_archive_dest string log_archive_dest_1 string log_archive_dest_10 string log_archive_dest_11 string log_archive_dest_12 string log_archive_dest_13 string log_archive_dest_14 string log_archive_dest_15 string log_archive_dest_16 string log_archive_dest_17 string NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ log_archive_dest_18 string log_archive_dest_19 string log_archive_dest_2 string log_archive_dest_20 string log_archive_dest_21 string log_archive_dest_22 string log_archive_dest_23 string log_archive_dest_24 string log_archive_dest_25 string log_archive_dest_26 string log_archive_dest_27 string NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ log_archive_dest_28 string log_archive_dest_29 string log_archive_dest_3 string log_archive_dest_30 string log_archive_dest_31 string log_archive_dest_4 string log_archive_dest_5 string log_archive_dest_6 string log_archive_dest_7 string log_archive_dest_8 string log_archive_dest_9 string NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ log_archive_dest_state_1 string enable log_archive_dest_state_10 string enable log_archive_dest_state_11 string enable log_archive_dest_state_12 string enable log_archive_dest_state_13 string enable log_archive_dest_state_14 string enable log_archive_dest_state_15 string enable log_archive_dest_state_16 string enable log_archive_dest_state_17 string enable log_archive_dest_state_18 string enable log_archive_dest_state_19 string enable NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ log_archive_dest_state_2 string enable log_archive_dest_state_20 string enable log_archive_dest_state_21 string enable log_archive_dest_state_22 string enable log_archive_dest_state_23 string enable log_archive_dest_state_24 string enable log_archive_dest_state_25 string enable log_archive_dest_state_26 string enable log_archive_dest_state_27 string enable log_archive_dest_state_28 string enable log_archive_dest_state_29 string enable NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ log_archive_dest_state_3 string enable log_archive_dest_state_30 string enable log_archive_dest_state_31 string enable log_archive_dest_state_4 string enable log_archive_dest_state_5 string enable log_archive_dest_state_6 string enable log_archive_dest_state_7 string enable log_archive_dest_state_8 string enable log_archive_dest_state_9 string enable log_archive_duplex_dest string log_archive_format string %t_%s_%r.dbf NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ log_archive_local_first boolean TRUE log_archive_max_processes integer 4 log_archive_min_succeed_dest integer 1 log_archive_start boolean FALSE log_archive_trace integer 0 SQL>
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
SQL> alter system set log_archive_dest_2='service=phydb lgwr sync valid_for=(online_logfiles,primary_role) db_unique_name=phydb' sid='*'; System altered.
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
SQL> show parameter log_arch; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ log_archive_config string log_archive_dest string log_archive_dest_1 string log_archive_dest_10 string log_archive_dest_11 string log_archive_dest_12 string log_archive_dest_13 string log_archive_dest_14 string log_archive_dest_15 string log_archive_dest_16 string log_archive_dest_17 string NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ log_archive_dest_18 string log_archive_dest_19 string log_archive_dest_2 string service=phydb lgwr sync valid_ for=(online_logfiles,primary_r ole) db_unique_name=phydb log_archive_dest_20 string log_archive_dest_21 string log_archive_dest_22 string log_archive_dest_23 string log_archive_dest_24 string log_archive_dest_25 string NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ log_archive_dest_26 string log_archive_dest_27 string log_archive_dest_28 string log_archive_dest_29 string log_archive_dest_3 string log_archive_dest_30 string log_archive_dest_31 string log_archive_dest_4 string log_archive_dest_5 string log_archive_dest_6 string log_archive_dest_7 string NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ log_archive_dest_8 string log_archive_dest_9 string log_archive_dest_state_1 string enable log_archive_dest_state_10 string enable log_archive_dest_state_11 string enable log_archive_dest_state_12 string enable log_archive_dest_state_13 string enable log_archive_dest_state_14 string enable log_archive_dest_state_15 string enable log_archive_dest_state_16 string enable log_archive_dest_state_17 string enable NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ log_archive_dest_state_18 string enable log_archive_dest_state_19 string enable log_archive_dest_state_2 string enable log_archive_dest_state_20 string enable log_archive_dest_state_21 string enable log_archive_dest_state_22 string enable log_archive_dest_state_23 string enable log_archive_dest_state_24 string enable log_archive_dest_state_25 string enable log_archive_dest_state_26 string enable log_archive_dest_state_27 string enable NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ log_archive_dest_state_28 string enable log_archive_dest_state_29 string enable log_archive_dest_state_3 string enable log_archive_dest_state_30 string enable log_archive_dest_state_31 string enable log_archive_dest_state_4 string enable log_archive_dest_state_5 string enable log_archive_dest_state_6 string enable log_archive_dest_state_7 string enable log_archive_dest_state_8 string enable log_archive_dest_state_9 string enable NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ log_archive_duplex_dest string log_archive_format string %t_%s_%r.dbf log_archive_local_first boolean TRUE log_archive_max_processes integer 4 log_archive_min_succeed_dest integer 1 log_archive_start boolean FALSE log_archive_trace integer 0 SQL>
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
SQL> alter system set log_archive_config='dg_config=(devdb,phydb)' sid='*'; System altered. SQL> show parameter log_arch; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ log_archive_config string dg_config=(devdb,phydb) log_archive_dest string log_archive_dest_1 string log_archive_dest_10 string log_archive_dest_11 string log_archive_dest_12 string log_archive_dest_13 string log_archive_dest_14 string log_archive_dest_15 string log_archive_dest_16 string log_archive_dest_17 string NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ log_archive_dest_18 string log_archive_dest_19 string log_archive_dest_2 string service=phydb lgwr sync valid_ for=(online_logfiles,primary_r ole) db_unique_name=phydb log_archive_dest_20 string log_archive_dest_21 string log_archive_dest_22 string log_archive_dest_23 string log_archive_dest_24 string log_archive_dest_25 string NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ log_archive_dest_26 string log_archive_dest_27 string log_archive_dest_28 string log_archive_dest_29 string log_archive_dest_3 string log_archive_dest_30 string log_archive_dest_31 string log_archive_dest_4 string log_archive_dest_5 string log_archive_dest_6 string log_archive_dest_7 string NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ log_archive_dest_8 string log_archive_dest_9 string log_archive_dest_state_1 string enable log_archive_dest_state_10 string enable log_archive_dest_state_11 string enable log_archive_dest_state_12 string enable log_archive_dest_state_13 string enable log_archive_dest_state_14 string enable log_archive_dest_state_15 string enable log_archive_dest_state_16 string enable log_archive_dest_state_17 string enable NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ log_archive_dest_state_18 string enable log_archive_dest_state_19 string enable log_archive_dest_state_2 string enable log_archive_dest_state_20 string enable log_archive_dest_state_21 string enable log_archive_dest_state_22 string enable log_archive_dest_state_23 string enable log_archive_dest_state_24 string enable log_archive_dest_state_25 string enable log_archive_dest_state_26 string enable log_archive_dest_state_27 string enable NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ log_archive_dest_state_28 string enable log_archive_dest_state_29 string enable log_archive_dest_state_3 string enable log_archive_dest_state_30 string enable log_archive_dest_state_31 string enable log_archive_dest_state_4 string enable log_archive_dest_state_5 string enable log_archive_dest_state_6 string enable log_archive_dest_state_7 string enable log_archive_dest_state_8 string enable log_archive_dest_state_9 string enable NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ log_archive_duplex_dest string log_archive_format string %t_%s_%r.dbf log_archive_local_first boolean TRUE log_archive_max_processes integer 4 log_archive_min_succeed_dest integer 1 log_archive_start boolean FALSE log_archive_trace integer 0 SQL>
7、备库接收主库日志
注意:在主库参数调整之后,备库接收主库归档日志之前,主库的alert日志文件会有个短暂的间歇调整期,会又ORA错误发生,此属于正常;具体错误日志如下:
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
node1-> tail -f alert_devdb1.log PING[ARC2]: Heartbeat failed to connect to standby 'phydb'. Error is 16057. Mon Jul 08 10:05:44 2013 PING[ARC2]: Heartbeat failed to connect to standby 'phydb'. Error is 16057. Mon Jul 08 10:06:42 2013 ALTER SYSTEM SET log_archive_config='dg_config=(devdb,phydb)' SCOPE=BOTH SID='*'; Mon Jul 08 10:08:01 2013 ARC3: Archive log rejected (thread 1 sequence 11) at host 'phydb' FAL[server, ARC3]: FAL archive failed, see trace file. ARCH: FAL archive failed. Archiver continuing ORACLE Instance devdb1 - Archival Error. Archiver continuing. Mon Jul 08 10:16:31 2013 ****************************************************************** LGWR: Setting 'active' archival for destination LOG_ARCHIVE_DEST_2 ****************************************************************** LGWR: Standby redo logfile selected for thread 1 sequence 15 for destination LOG_ARCHIVE_DEST_2 Thread 1 advanced to log sequence 15 (LGWR switch) Current log# 1 seq# 15 mem# 0: +DATA/devdb/onlinelog/group_1.261.820154859 Current log# 1 seq# 15 mem# 1: +FLASH/devdb/onlinelog/group_1.257.820154861 Mon Jul 08 10:16:33 2013 Archived Log entry 24 added for thread 1 sequence 14 ID 0x2b354062 dest 1: Mon Jul 08 10:16:33 2013 ARC0: Standby redo logfile selected for thread 1 sequence 14 for destination LOG_ARCHIVE_DEST_2 Mon Jul 08 10:18:26 2013 Thread 1 cannot allocate new log, sequence 16 Checkpoint not complete Current log# 1 seq# 15 mem# 0: +DATA/devdb/onlinelog/group_1.261.820154859 Current log# 1 seq# 15 mem# 1: +FLASH/devdb/onlinelog/group_1.257.820154861 ****************************************************************** LGWR: Setting 'active' archival for destination LOG_ARCHIVE_DEST_2 ****************************************************************** LGWR: Standby redo logfile selected for thread 1 sequence 16 for destination LOG_ARCHIVE_DEST_2 Thread 1 advanced to log sequence 16 (LGWR switch) Current log# 2 seq# 16 mem# 0: +DATA/devdb/onlinelog/group_2.262.820154865 Current log# 2 seq# 16 mem# 1: +FLASH/devdb/onlinelog/group_2.258.820154865 Mon Jul 08 10:18:32 2013 Expanded controlfile section 11 from 28 to 220 records Requested to grow by 192 records; added 7 blocks of records Archived Log entry 29 added for thread 1 sequence 15 ID 0x2b354062 dest 1:
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
SQL> alter system switch logfile; System altered. SQL> select * from v$log order by 1; GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME ---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ------------------- ------------ ------------------- 1 1 15 52428800 512 2 NO CURRENT 1372231 2013/07/08 10:16:31 2.8147E+14 2 1 14 52428800 512 2 YES ACTIVE 1366355 2013/07/08 09:54:19 1372231 2013/07/08 10:16:31 3 2 11 52428800 512 2 NO CURRENT 1372321 2013/07/08 10:14:14 2.8147E+14 4 2 10 52428800 512 2 YES ACTIVE 1366352 2013/07/08 09:51:29 1372321 2013/07/08 10:14:14 SQL> alter system switch logfile; System altered. SQL> select * from v$log order by 1; GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME ---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ------------------- ------------ ------------------- 1 1 15 52428800 512 2 YES ACTIVE 1372231 2013/07/08 10:16:31 1372533 2013/07/08 10:18:28 2 1 16 52428800 512 2 NO CURRENT 1372533 2013/07/08 10:18:28 2.8147E+14 3 2 11 52428800 512 2 NO CURRENT 1372321 2013/07/08 10:14:14 2.8147E+14 4 2 10 52428800 512 2 YES ACTIVE 1366352 2013/07/08 09:51:29 1372321 2013/07/08 10:14:14 SQL>
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
SQL> set linesize 200 SQL> select * from v$log order by 1; GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME ---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ------------------- ------------ ------------------- 1 1 15 52428800 512 2 YES ACTIVE 1372231 2013/07/08 10:16:31 1372533 2013/07/08 10:18:28 2 1 16 52428800 512 2 NO CURRENT 1372533 2013/07/08 10:18:28 2.8147E+14 3 2 11 52428800 512 2 NO CURRENT 1372321 2013/07/08 10:14:14 2.8147E+14 4 2 10 52428800 512 2 YES ACTIVE 1366352 2013/07/08 09:51:29 1372321 2013/07/08 10:14:14 SQL> alter system switch logfile; System altered. SQL> select * from v$log order by 1; GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME ---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ------------------- ------------ ------------------- 1 1 15 52428800 512 2 YES ACTIVE 1372231 2013/07/08 10:16:31 1372533 2013/07/08 10:18:28 2 1 16 52428800 512 2 NO CURRENT 1372533 2013/07/08 10:18:28 2.8147E+14 3 2 11 52428800 512 2 YES ACTIVE 1372321 2013/07/08 10:14:14 1372634 2013/07/08 10:16:38 4 2 12 52428800 512 2 NO CURRENT 1372634 2013/07/08 10:16:38 2.8147E+14 SQL>
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
RMAN> list archivelog all; List of Archived Log Copies for database with db_unique_name PHYDB ===================================================================== Key Thrd Seq S Low Time ------- ---- ------- - ------------------- 5 1 11 A 2013/07/07 18:04:23 Name: +FLASH/phydb/archivelog/2013_07_08/thread_1_seq_11.263.820259787 4 1 12 A 2013/07/08 05:00:48 Name: +FLASH/phydb/archivelog/2013_07_08/thread_1_seq_12.264.820259787 1 1 13 A 2013/07/08 09:12:06 Name: +FLASH/phydb/archivelog/2013_07_08/thread_1_seq_13.262.820259787 7 1 14 A 2013/07/08 09:54:19 Name: +FLASH/phydb/archivelog/2013_07_08/thread_1_seq_14.268.820260373 9 1 15 A 2013/07/08 10:16:31 Name: +FLASH/phydb/archivelog/2013_07_08/thread_1_seq_15.270.820260487 6 2 7 A 2013/07/07 18:04:13 Name: +FLASH/phydb/archivelog/2013_07_08/thread_2_seq_7.265.820259789 3 2 8 A 2013/07/08 09:12:04 Name: +FLASH/phydb/archivelog/2013_07_08/thread_2_seq_8.267.820259791 2 2 9 A 2013/07/08 09:16:07 Name: +FLASH/phydb/archivelog/2013_07_08/thread_2_seq_9.266.820259791 8 2 10 A 2013/07/08 09:51:29 Name: +FLASH/phydb/archivelog/2013_07_08/thread_2_seq_10.269.820260403 10 2 11 A 2013/07/08 10:14:14 Name: +FLASH/phydb/archivelog/2013_07_08/thread_2_seq_11.271.820260539 RMAN>
8、备库开始应用日志
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
SQL> select sequence#,name,applied from v$archived_log order by 1; SEQUENCE# NAME APPLIED ---------- -------------------------------------------------------------------------------- --------- 7 +FLASH/phydb/archivelog/2013_07_08/thread_2_seq_7.265.820259789 NO 8 +FLASH/phydb/archivelog/2013_07_08/thread_2_seq_8.267.820259791 NO 9 +FLASH/phydb/archivelog/2013_07_08/thread_2_seq_9.266.820259791 NO 10 +FLASH/phydb/archivelog/2013_07_08/thread_2_seq_10.269.820260403 NO 11 +FLASH/phydb/archivelog/2013_07_08/thread_2_seq_11.271.820260539 NO 11 +FLASH/phydb/archivelog/2013_07_08/thread_1_seq_11.263.820259787 NO 12 +FLASH/phydb/archivelog/2013_07_08/thread_1_seq_12.264.820259787 NO 13 +FLASH/phydb/archivelog/2013_07_08/thread_1_seq_13.262.820259787 NO 14 +FLASH/phydb/archivelog/2013_07_08/thread_1_seq_14.268.820260373 NO 15 +FLASH/phydb/archivelog/2013_07_08/thread_1_seq_15.270.820260487 NO 10 rows selected. SQL>
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
SQL> alter database recover managed standby database using current logfile disconnect from session; Database altered. SQL>
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
SQL> select sequence#,name,applied from v$archived_log order by 1; SEQUENCE# NAME APPLIED ---------- -------------------------------------------------------------------------------- --------- 6 +FLASH/phydb/archivelog/2013_07_08/thread_2_seq_6.277.820261283 YES 7 +FLASH/phydb/archivelog/2013_07_08/thread_2_seq_7.265.820259789 YES 8 +FLASH/phydb/archivelog/2013_07_08/thread_2_seq_8.267.820259791 YES 9 +FLASH/phydb/archivelog/2013_07_08/thread_2_seq_9.266.820259791 YES 10 +FLASH/phydb/archivelog/2013_07_08/thread_2_seq_10.269.820260403 YES 10 +FLASH/phydb/archivelog/2013_07_08/thread_1_seq_10.276.820261279 YES 11 +FLASH/phydb/archivelog/2013_07_08/thread_1_seq_11.263.820259787 YES 11 +FLASH/phydb/archivelog/2013_07_08/thread_2_seq_11.271.820260539 IN-MEMORY 12 +FLASH/phydb/archivelog/2013_07_08/thread_1_seq_12.264.820259787 YES 13 +FLASH/phydb/archivelog/2013_07_08/thread_1_seq_13.262.820259787 YES 14 +FLASH/phydb/archivelog/2013_07_08/thread_1_seq_14.268.820260373 YES SEQUENCE# NAME APPLIED ---------- -------------------------------------------------------------------------------- --------- 15 +FLASH/phydb/archivelog/2013_07_08/thread_1_seq_15.270.820260487 YES 12 rows selected. SQL>
9、备库以ReaOnly方式打开
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
SQL> select dbid,name,switchover_status,db_unique_name,database_role,open_mode,current_scn from v$database; DBID NAME SWITCHOVER_STATUS DB_UNIQUE_NAME DATABASE_ROLE OPEN_MODE CURRENT_SCN ---------- -------------------------------------------------------------------------------- -------------------- ------------------------------ ---------------- -------------------- ----------- 724884837 DEVDB NOT ALLOWED phydb PHYSICAL STANDBY MOUNTED 1372532
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
SQL> alter database recover managed standby database cancel; Database altered.
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
SQL> alter database open; Database altered.
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
SQL> select dbid,name,switchover_status,db_unique_name,database_role,open_mode,current_scn from v$database; DBID NAME SWITCHOVER_STATUS DB_UNIQUE_NAME DATABASE_ROLE OPEN_MODE CURRENT_SCN ---------- -------------------------------------------------------------------------------- -------------------- ------------------------------ ---------------- -------------------- ----------- 724884837 DEVDB NOT ALLOWED phydb PHYSICAL STANDBY READ ONLY 1381999 SQL>
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
SQL> alter database recover managed standby database using current logfile disconnect from session; Database altered.
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
SQL> select sequence#,name,applied from v$archived_log order by 1; SEQUENCE# NAME APPLIED ---------- -------------------------------------------------------------------------------- --------- 6 +FLASH/phydb/archivelog/2013_07_08/thread_2_seq_6.277.820261283 YES 7 +FLASH/phydb/archivelog/2013_07_08/thread_2_seq_7.265.820259789 YES 8 +FLASH/phydb/archivelog/2013_07_08/thread_2_seq_8.267.820259791 YES 9 +FLASH/phydb/archivelog/2013_07_08/thread_2_seq_9.266.820259791 YES 10 +FLASH/phydb/archivelog/2013_07_08/thread_2_seq_10.269.820260403 YES 10 +FLASH/phydb/archivelog/2013_07_08/thread_1_seq_10.276.820261279 YES 11 +FLASH/phydb/archivelog/2013_07_08/thread_1_seq_11.263.820259787 YES 11 +FLASH/phydb/archivelog/2013_07_08/thread_2_seq_11.271.820260539 YES 12 +FLASH/phydb/archivelog/2013_07_08/thread_1_seq_12.264.820259787 YES 13 +FLASH/phydb/archivelog/2013_07_08/thread_1_seq_13.262.820259787 YES 14 +FLASH/phydb/archivelog/2013_07_08/thread_1_seq_14.268.820260373 YES SEQUENCE# NAME APPLIED ---------- -------------------------------------------------------------------------------- --------- 15 +FLASH/phydb/archivelog/2013_07_08/thread_1_seq_15.270.820260487 YES 12 rows selected. SQL>
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
SQL> select dbid,name,switchover_status,db_unique_name,database_role,open_mode,current_scn from v$database; DBID NAME SWITCHOVER_STATUS DB_UNIQUE_NAME DATABASE_ROLE OPEN_MODE CURRENT_SCN ---------- -------------------------------------------------------------------------------- -------------------- ------------------------------ ---------------- -------------------- ----------- 724884837 DEVDB NOT ALLOWED phydb PHYSICAL STANDBY READ ONLY WITH APPLY 1382783 SQL>
在Oracle10g的时候,物理备库中的两个状态时二选一的,要么只能只读,要么只能应用归档日志;在oracle11g之后,则只读和应用归档日志可以同时存在.这就是oracle 11g的新特性.
11、测试物理备库功能
11.1、测试文件同步
1、主库查看表空间信息
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
SQL> select file_name,tablespace_name from dba_data_files; FILE_NAME TABLESPACE_NAME -------------------------------------------------------------------------------- ------------------------------ +DATA/devdb/datafile/users.259.820154753 USERS +DATA/devdb/datafile/undotbs1.258.820154753 UNDOTBS1 +DATA/devdb/datafile/sysaux.257.820154753 SYSAUX +DATA/devdb/datafile/system.256.820154751 SYSTEM +DATA/devdb/datafile/example.264.820154917 EXAMPLE +DATA/devdb/datafile/undotbs2.265.820155189 UNDOTBS2 6 rows selected. SQL>
2、查看物理备库表空间信息
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
SQL> col file_name format a80 SQL> select file_name,tablespace_name from dba_data_files; FILE_NAME TABLESPACE_NAME -------------------------------------------------------------------------------- ------------------------------ +DATA/phydb/datafile/users.261.820221997 USERS +DATA/phydb/datafile/undotbs1.260.820221997 UNDOTBS1 +DATA/phydb/datafile/sysaux.258.820221997 SYSAUX +DATA/phydb/datafile/system.259.820221997 SYSTEM +DATA/phydb/datafile/example.257.820221997 EXAMPLE +DATA/phydb/datafile/undotbs2.262.820221999 UNDOTBS2 6 rows selected. SQL>
3、主库创建测试表空间
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
SQL> create tablespace dg_tbs datafile size 5m; Tablespace created. SQL> select file_name,tablespace_name from dba_data_files; FILE_NAME TABLESPACE_NAME -------------------------------------------------------------------------------- ------------------------------ +DATA/devdb/datafile/users.259.820154753 USERS +DATA/devdb/datafile/undotbs1.258.820154753 UNDOTBS1 +DATA/devdb/datafile/sysaux.257.820154753 SYSAUX +DATA/devdb/datafile/system.256.820154751 SYSTEM +DATA/devdb/datafile/example.264.820154917 EXAMPLE +DATA/devdb/datafile/undotbs2.265.820155189 UNDOTBS2 +DATA/devdb/datafile/dg_tbs.269.820237183 DG_TBS 7 rows selected. SQL>
4、查看物理备库表空间信息是否同步
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
SQL> select file_name,tablespace_name from dba_data_files; FILE_NAME TABLESPACE_NAME -------------------------------------------------------------------------------- ------------------------------ +DATA/phydb/datafile/users.261.820221997 USERS +DATA/phydb/datafile/undotbs1.260.820221997 UNDOTBS1 +DATA/phydb/datafile/sysaux.258.820221997 SYSAUX +DATA/phydb/datafile/system.259.820221997 SYSTEM +DATA/phydb/datafile/example.257.820221997 EXAMPLE +DATA/phydb/datafile/undotbs2.262.820221999 UNDOTBS2 +DATA/phydb/datafile/dg_tbs.274.820265363 DG_TBS 7 rows selected. SQL>
5、主库删除表空间
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
SQL> drop tablespace dg_tbs including contents and datafiles; Tablespace dropped. SQL> select file_name,tablespace_name from dba_data_files; FILE_NAME TABLESPACE_NAME -------------------------------------------------------------------------------- ------------------------------ +DATA/devdb/datafile/users.259.820154753 USERS +DATA/devdb/datafile/undotbs1.258.820154753 UNDOTBS1 +DATA/devdb/datafile/sysaux.257.820154753 SYSAUX +DATA/devdb/datafile/system.256.820154751 SYSTEM +DATA/devdb/datafile/example.264.820154917 EXAMPLE +DATA/devdb/datafile/undotbs2.265.820155189 UNDOTBS2 6 rows selected. SQL>
6、再次查看物理备库表空间是否同步
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
SQL> select file_name,tablespace_name from dba_data_files; FILE_NAME TABLESPACE_NAME -------------------------------------------------------------------------------- ------------------------------ +DATA/phydb/datafile/users.261.820221997 USERS +DATA/phydb/datafile/undotbs1.260.820221997 UNDOTBS1 +DATA/phydb/datafile/sysaux.258.820221997 SYSAUX +DATA/phydb/datafile/system.259.820221997 SYSTEM +DATA/phydb/datafile/example.257.820221997 EXAMPLE +DATA/phydb/datafile/undotbs2.262.820221999 UNDOTBS2 6 rows selected. SQL>
11.2、测试数据同步
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
SQL> conn scott/tiger Connected. SQL> select table_name from tabs; TABLE_NAME ------------------------------ DEPT EMP BONUS SALGRADE SQL> create table t(id int,name varchar2(10)); Table created. SQL> insert into t values(0,'arcerzhang'); 1 row created. SQL> commit; Commit complete. SQL>
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
SQL> conn scott/tiger Connected. SQL> desc t; Name Null? Type ----------------------------------------------------------------------------------------------------------------- -------- ---------------------------------------------------------------------------- ID NUMBER(38) NAME VARCHAR2(10) SQL> select * from t; no rows selected SQL> select * from t; ID NAME ---------- -------------------------------------------------------------------------------- 0 arcerzhang SQL>
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
SQL> drop table t purge; Table dropped. SQL> desc t; ERROR: ORA-04043: object t does not exist SQL>
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
SQL> select * from t; select * from t * ERROR at line 1: ORA-00942: table or view does not exist SQL>
12、测试DataGuard Switchover 功能
12.1、RAC主库、Active DataGuard 角色切换
1、主库修改相关参数fal_client、fal_server
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
SQL> set line 200 SQL> select dbid,name,current_scn,protection_mode,database_role,force_logging,open_mode,switchover_status from gv$database; DBID NAME CURRENT_SCN PROTECTION_MODE DATABASE_ROLE FOR OPEN_MODE SWITCHOVER_STATUS ---------- --------- ----------- -------------------- ---------------- --- -------------------- -------------------- 724884837 DEVDB 1417022 MAXIMUM PERFORMANCE PRIMARY YES READ WRITE TO STANDBY 724884837 DEVDB 1417023 MAXIMUM PERFORMANCE PRIMARY YES READ WRITE TO STANDBY SQL>
DataGuard有三种模式:最大性能、最高保护、最高可用
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
SQL> select dbid,name,current_scn,protection_mode,database_role,force_logging,open_mode,switchover_status from gv$database; DBID NAME CURRENT_SCN PROTECTION_MODE DATABASE_ROLE FOR OPEN_MODE SWITCHOVER_STATUS ---------- --------- ----------- -------------------- ---------------- --- -------------------- -------------------- 724884837 DEVDB 1417430 MAXIMUM PERFORMANCE PHYSICAL STANDBY YES READ ONLY WITH APPLY NOT ALLOWED
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
SQL> show parameter spfile NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ spfile string +DATA/devdb/spfiledevdb.ora SQL> show parameter fal NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ fal_client string fal_server string SQL>
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
SQL> alter system set fal_client='devdb1' sid='devdb1'; System altered. SQL> alter system set fal_client='devdb2' sid='devdb2'; System altered. SQL> alter system set fal_server='phydb' sid='*'; System altered.
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
SQL> show parameter fal NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ fal_client string devdb1 fal_server string phydb SQL> show parameter instance_name NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ instance_name string devdb1 SQL>
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
SQL> show parameter spfile NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ spfile string +DATA/devdb/spfiledevdb.ora SQL> show parameter fal NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ fal_client string devdb2 fal_server string phydb SQL> show parameter instance_name NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ instance_name string devdb2 SQL>
2、主库创建standbylogfile
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
USER is "SYS" SQL> col member for a80 SQL> select * from v$logfile order by 1; GROUP# STATUS TYPE MEMBER IS_ ---------- ------- ------- -------------------------------------------------------------------------------- --- 1 ONLINE +FLASH/devdb/onlinelog/group_1.257.820154861 YES 1 ONLINE +DATA/devdb/onlinelog/group_1.261.820154859 NO 2 ONLINE +FLASH/devdb/onlinelog/group_2.258.820154865 YES 2 ONLINE +DATA/devdb/onlinelog/group_2.262.820154865 NO 3 ONLINE +DATA/devdb/onlinelog/group_3.266.820155371 NO 3 ONLINE +FLASH/devdb/onlinelog/group_3.259.820155373 YES 4 ONLINE +DATA/devdb/onlinelog/group_4.267.820155375 NO 4 ONLINE +FLASH/devdb/onlinelog/group_4.260.820155377 YES 8 rows selected. SQL> select * from v$standby_log; no rows selected SQL>
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
SQL> alter database add standby logfile thread 1 group 5 size 50m,group 6 size 50m,group 7 size 50m; Database altered. SQL> alter database add standby logfile thread 2 group 8 size 50m,group 9 size 50m,group 10 size 50m; Database altered.
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
SQL> select * from v$standby_log; GROUP# DBID THREAD# SEQUENCE# BYTES BLOCKSIZE USED ARC STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME LAST_CHANGE# LAST_TIME ---------- ---------- ---------- ---------- ---------- ---------- ---------- --- ---------- ------------- ------------------- ------------ ------------------- ------------ ------------------- 5 UNASSIGNED 1 0 52428800 512 0 YES UNASSIGNED 6 UNASSIGNED 1 0 52428800 512 0 YES UNASSIGNED 7 UNASSIGNED 1 0 52428800 512 0 YES UNASSIGNED 8 UNASSIGNED 2 0 52428800 512 0 YES UNASSIGNED 9 UNASSIGNED 2 0 52428800 512 0 YES UNASSIGNED 10 UNASSIGNED 2 0 52428800 512 0 YES UNASSIGNED 6 rows selected. SQL> select * from v$logfile order by 1; GROUP# STATUS TYPE MEMBER IS_ ---------- ------- ------- -------------------------------------------------------------------------------- --- 1 ONLINE +DATA/devdb/onlinelog/group_1.261.820154859 NO 1 ONLINE +FLASH/devdb/onlinelog/group_1.257.820154861 YES 2 ONLINE +DATA/devdb/onlinelog/group_2.262.820154865 NO 2 ONLINE +FLASH/devdb/onlinelog/group_2.258.820154865 YES 3 ONLINE +DATA/devdb/onlinelog/group_3.266.820155371 NO 3 ONLINE +FLASH/devdb/onlinelog/group_3.259.820155373 YES 4 ONLINE +DATA/devdb/onlinelog/group_4.267.820155375 NO 4 ONLINE +FLASH/devdb/onlinelog/group_4.260.820155377 YES 5 STANDBY +DATA/devdb/onlinelog/group_5.269.820249707 NO 5 STANDBY +FLASH/devdb/onlinelog/group_5.282.820249709 YES 6 STANDBY +DATA/devdb/onlinelog/group_6.270.820249711 NO GROUP# STATUS TYPE MEMBER IS_ ---------- ------- ------- -------------------------------------------------------------------------------- --- 6 STANDBY +FLASH/devdb/onlinelog/group_6.283.820249715 YES 7 STANDBY +DATA/devdb/onlinelog/group_7.271.820249717 NO 7 STANDBY +FLASH/devdb/onlinelog/group_7.284.820249719 YES 8 STANDBY +DATA/devdb/onlinelog/group_8.272.820249747 NO 8 STANDBY +FLASH/devdb/onlinelog/group_8.285.820249747 YES 9 STANDBY +DATA/devdb/onlinelog/group_9.273.820249751 NO 9 STANDBY +FLASH/devdb/onlinelog/group_9.286.820249751 YES 10 STANDBY +DATA/devdb/onlinelog/group_10.274.820249755 NO 10 STANDBY +FLASH/devdb/onlinelog/group_10.287.820249761 YES 20 rows selected. SQL>
3、主库修改相关参数standby_file_management、db_file_name_convert、log_file_name_convert;
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
SQL> show parameter standby_file NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ standby_file_management string MANUAL SQL> show parameter db_file_na NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_file_name_convert string SQL> show parameter log_file NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ log_file_name_convert string SQL>
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
SQL> alter system set db_file_name_convert='+DATA/phydb/','+DATA/devdb/' sid='*' scope=spfile; System altered. SQL> alter system set log_file_name_convert='+DATA/phydb/','+DATA/devdb/' sid='*' scope=spfile; System altered. SQL> alter system set STANDBY_FILE_MANAGEMENT='AUTO'; System altered.
4、停止RAC节点2
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL>
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
SQL> select dbid,name,current_scn,protection_mode,database_role,force_logging,open_mode,switchover_status from gv$database; DBID NAME CURRENT_SCN PROTECTION_MODE DATABASE_ROLE FOR OPEN_MODE SWITCHOVER_STATUS ---------- --------- ----------- -------------------- ---------------- --- -------------------- -------------------- ########## DEVDB 1426017 MAXIMUM PERFORMANCE PRIMARY YES READ WRITE TO STANDBY SQL>
5、RAC节点1切换原RAC主库到备库
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
SQL> alter database commit to switchover to physical standby with session shutdown; Database altered. SQL> select dbid,name,current_scn,protection_mode,database_role,force_logging,open_mode,switchover_status from gv$database; DBID NAME CURRENT_SCN PROTECTION_MODE DATABASE_ROLE FOR OPEN_MODE SWITCHOVER_STATUS ---------- --------- ----------- -------------------- ---------------- --- -------------------- -------------------- ########## DEVDB 1426342 MAXIMUM PERFORMANCE PHYSICAL STANDBY YES READ WRITE RECOVERY NEEDED SQL>
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
SQL> select dbid,name,current_scn,protection_mode,database_role,force_logging,open_mode,switchover_status from gv$database; DBID NAME CURRENT_SCN PROTECTION_MODE DATABASE_ROLE FOR OPEN_MODE SWITCHOVER_STATUS ---------- --------- ----------- -------------------- ---------------- --- -------------------- -------------------- 724884837 DEVDB 1446342 MAXIMUM PERFORMANCE PHYSICAL STANDBY YES READ ONLY WITH APPLY SESSIONS ACTIVE SQL>
6、切换原物理备库到主库角色
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
[root@11gdg ~]# su - oracle 11gdg-> sqlplus sys/Oracle168@phydb as sysdba SQL*Plus: Release 11.2.0.3.0 Production on Mon Jul 8 23:32:11 2013 Copyright (c) 1982, 2011, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options SQL> alter database commit to switchover to primary; Database altered. SQL> set line 200 SQL> select dbid,name,current_scn,protection_mode,database_role,force_logging,open_mode,switchover_status from gv$database; DBID NAME CURRENT_SCN PROTECTION_MODE DATABASE_ROLE FOR OPEN_MODE SWITCHOVER_STATUS ---------- --------- ----------- -------------------- ---------------- --- -------------------- -------------------- 724884837 DEVDB 0 MAXIMUM PERFORMANCE PRIMARY YES MOUNTED NOT ALLOWED SQL>
7、将原RAC主库2个实例都启动到MOUNT
备注:首先将node1进行shutdown immediate;操作
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
[root@node1 ~]# ps -ef | grep ora_ root 15403 15319 0 15:53 pts/3 00:00:00 grep ora_ [root@node1 ~]# su - grid node1-> clear node1-> crs_stat -t Name Type Target State Host ------------------------------------------------------------ ora.DATA.dg ora....up.type ONLINE ONLINE node1 ora.FLASH.dg ora....up.type ONLINE ONLINE node1 ora.GRIDDG.dg ora....up.type ONLINE ONLINE node1 ora....ER.lsnr ora....er.type ONLINE ONLINE node1 ora....N1.lsnr ora....er.type ONLINE ONLINE node1 ora.asm ora.asm.type ONLINE ONLINE node1 ora.cvu ora.cvu.type ONLINE ONLINE node1 ora.devdb.db ora....se.type OFFLINE OFFLINE ora.gsd ora.gsd.type OFFLINE OFFLINE ora....network ora....rk.type ONLINE ONLINE node1 ora....SM1.asm application ONLINE ONLINE node1 ora....E1.lsnr application ONLINE ONLINE node1 ora.node1.gsd application OFFLINE OFFLINE ora.node1.ons application ONLINE ONLINE node1 ora.node1.vip ora....t1.type ONLINE ONLINE node1 ora....SM2.asm application ONLINE ONLINE node2 ora....E2.lsnr application ONLINE ONLINE node2 ora.node2.gsd application OFFLINE OFFLINE ora.node2.ons application ONLINE ONLINE node2 ora.node2.vip ora....t1.type ONLINE ONLINE node2 ora.oc4j ora.oc4j.type ONLINE ONLINE node1 ora.ons ora.ons.type ONLINE ONLINE node1 ora.scan1.vip ora....ip.type ONLINE ONLINE node1 node1-> srvctl status database -d devdb Instance devdb1 is not running on node node1 Instance devdb2 is not running on node node2 node1->
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
node1-> id uid=1100(grid) gid=1000(oinstall) groups=1000(oinstall),1200(asmadmin),1201(asmdba),1202(asmoper),1300(dba) node1-> srvctl start database -d devdb -o mount; node1->
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
[root@node1 ~]# ps -ef | grep ora_ oracle 15699 1 0 15:57 ? 00:00:00 ora_pmon_devdb1 oracle 15701 1 0 15:57 ? 00:00:00 ora_psp0_devdb1 oracle 15703 1 6 15:57 ? 00:00:10 ora_vktm_devdb1 oracle 15707 1 0 15:57 ? 00:00:00 ora_gen0_devdb1 oracle 15709 1 0 15:57 ? 00:00:00 ora_diag_devdb1 oracle 15711 1 0 15:57 ? 00:00:00 ora_dbrm_devdb1 oracle 15713 1 0 15:57 ? 00:00:00 ora_ping_devdb1 oracle 15715 1 0 15:57 ? 00:00:00 ora_acms_devdb1 oracle 15718 1 0 15:57 ? 00:00:01 ora_dia0_devdb1 oracle 15722 1 0 15:57 ? 00:00:00 ora_lmon_devdb1 oracle 15724 1 0 15:57 ? 00:00:01 ora_lmd0_devdb1 oracle 15726 1 2 15:57 ? 00:00:03 ora_lms0_devdb1 oracle 15730 1 0 15:57 ? 00:00:00 ora_rms0_devdb1 oracle 15732 1 0 15:57 ? 00:00:00 ora_lmhb_devdb1 oracle 15734 1 5 15:57 ? 00:00:08 ora_mman_devdb1 oracle 15736 1 0 15:57 ? 00:00:00 ora_dbw0_devdb1 oracle 15738 1 0 15:57 ? 00:00:00 ora_lgwr_devdb1 oracle 15740 1 0 15:57 ? 00:00:00 ora_ckpt_devdb1 oracle 15743 1 0 15:57 ? 00:00:00 ora_smon_devdb1 oracle 15745 1 0 15:57 ? 00:00:00 ora_reco_devdb1 oracle 15747 1 0 15:57 ? 00:00:00 ora_rbal_devdb1 oracle 15749 1 0 15:57 ? 00:00:00 ora_asmb_devdb1 oracle 15751 1 0 15:57 ? 00:00:00 ora_mmon_devdb1 oracle 15753 1 0 15:57 ? 00:00:00 ora_mmnl_devdb1 oracle 15757 1 0 15:57 ? 00:00:00 ora_d000_devdb1 oracle 15759 1 0 15:57 ? 00:00:00 ora_mark_devdb1 oracle 15761 1 0 15:57 ? 00:00:00 ora_s000_devdb1 oracle 15763 1 0 15:57 ? 00:00:00 ora_ocf0_devdb1 oracle 15768 1 0 15:58 ? 00:00:00 ora_lck0_devdb1 oracle 15770 1 0 15:58 ? 00:00:00 ora_rsmn_devdb1 oracle 15798 1 0 15:58 ? 00:00:00 ora_o000_devdb1 oracle 15830 1 0 15:58 ? 00:00:00 ora_nss2_devdb1 oracle 15833 1 0 15:58 ? 00:00:00 ora_arc0_devdb1 oracle 15842 1 0 15:58 ? 00:00:00 ora_arc1_devdb1 oracle 15845 1 0 15:58 ? 00:00:00 ora_arc2_devdb1 oracle 15848 1 0 15:58 ? 00:00:00 ora_arc3_devdb1 root 15958 15319 0 16:00 pts/3 00:00:00 grep ora_ [root@node1 ~]#
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
[root@node2 ~]# ps -ef | grep ora_ oracle 9781 1 0 15:55 ? 00:00:00 ora_pmon_devdb2 oracle 9783 1 0 15:55 ? 00:00:00 ora_psp0_devdb2 oracle 9785 1 5 15:55 ? 00:00:07 ora_vktm_devdb2 oracle 9789 1 0 15:55 ? 00:00:00 ora_gen0_devdb2 oracle 9791 1 0 15:55 ? 00:00:00 ora_diag_devdb2 oracle 9793 1 0 15:55 ? 00:00:00 ora_dbrm_devdb2 oracle 9795 1 0 15:55 ? 00:00:00 ora_ping_devdb2 oracle 9797 1 0 15:55 ? 00:00:00 ora_acms_devdb2 oracle 9799 1 1 15:55 ? 00:00:01 ora_dia0_devdb2 oracle 9801 1 0 15:55 ? 00:00:01 ora_lmon_devdb2 oracle 9803 1 1 15:55 ? 00:00:01 ora_lmd0_devdb2 oracle 9805 1 1 15:55 ? 00:00:02 ora_lms0_devdb2 oracle 9809 1 0 15:55 ? 00:00:00 ora_rms0_devdb2 oracle 9811 1 0 15:55 ? 00:00:00 ora_lmhb_devdb2 oracle 9813 1 8 15:55 ? 00:00:11 ora_mman_devdb2 oracle 9815 1 0 15:55 ? 00:00:00 ora_dbw0_devdb2 oracle 9817 1 0 15:55 ? 00:00:00 ora_lgwr_devdb2 oracle 9819 1 0 15:55 ? 00:00:00 ora_ckpt_devdb2 oracle 9821 1 0 15:55 ? 00:00:00 ora_smon_devdb2 oracle 9823 1 0 15:55 ? 00:00:00 ora_reco_devdb2 oracle 9825 1 0 15:55 ? 00:00:00 ora_rbal_devdb2 oracle 9827 1 0 15:55 ? 00:00:00 ora_asmb_devdb2 oracle 9829 1 0 15:55 ? 00:00:00 ora_mmon_devdb2 oracle 9833 1 0 15:55 ? 00:00:00 ora_mmnl_devdb2 oracle 9835 1 0 15:55 ? 00:00:00 ora_d000_devdb2 oracle 9837 1 0 15:55 ? 00:00:00 ora_mark_devdb2 oracle 9839 1 0 15:55 ? 00:00:00 ora_s000_devdb2 oracle 9841 1 0 15:55 ? 00:00:00 ora_ocf0_devdb2 oracle 9849 1 0 15:55 ? 00:00:00 ora_lck0_devdb2 oracle 9851 1 0 15:55 ? 00:00:00 ora_rsmn_devdb2 oracle 9875 1 0 15:55 ? 00:00:00 ora_o000_devdb2 oracle 9903 1 0 15:55 ? 00:00:00 ora_nss2_devdb2 oracle 9905 1 0 15:55 ? 00:00:00 ora_arc0_devdb2 oracle 9913 1 0 15:55 ? 00:00:00 ora_arc1_devdb2 oracle 9917 1 0 15:55 ? 00:00:00 ora_arc2_devdb2 oracle 9920 1 0 15:55 ? 00:00:00 ora_arc3_devdb2 oracle 9979 1 0 15:55 ? 00:00:00 ora_gcr0_devdb2 root 9992 9600 0 15:57 pts/3 00:00:00 grep ora_ [root@node2 ~]#
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
SQL> set linesize 200 SQL> select dbid,name,current_scn,protection_mode,database_role,force_logging,open_mode,switchover_status from gv$database; DBID NAME CURRENT_SCN PROTECTION_MODE DATABASE_ROLE FOR OPEN_MODE SWITCHOVER_STATUS ---------- --------- ----------- -------------------- ---------------- --- -------------------- -------------------- 724884837 DEVDB 1446341 MAXIMUM PERFORMANCE PHYSICAL STANDBY YES MOUNTED RECOVERY NEEDED 724884837 DEVDB 1446341 MAXIMUM PERFORMANCE PHYSICAL STANDBY YES MOUNTED RECOVERY NEEDED SQL>
8、原RAC主库启动redoapply
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
SQL> alter database recover managed standby database using current logfile disconnect from session; Database altered. SQL>
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
SQL> select dbid,name,current_scn,protection_mode,database_role,force_logging,open_mode,switchover_status from gv$database; DBID NAME CURRENT_SCN PROTECTION_MODE DATABASE_ROLE FOR OPEN_MODE SWITCHOVER_STATUS ---------- --------- ----------- -------------------- ---------------- --- -------------------- -------------------- 724884837 DEVDB 0 MAXIMUM PERFORMANCE PRIMARY YES MOUNTED NOT ALLOWED SQL> alter database open; Database altered. SQL> select dbid,name,current_scn,protection_mode,database_role,force_logging,open_mode,switchover_status from gv$database; DBID NAME CURRENT_SCN PROTECTION_MODE DATABASE_ROLE FOR OPEN_MODE SWITCHOVER_STATUS ---------- --------- ----------- -------------------- ---------------- --- -------------------- -------------------- 724884837 DEVDB 1446506 MAXIMUM PERFORMANCE PRIMARY YES READ WRITE TO STANDBY SQL>
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
SQL> select dbid,name,current_scn,protection_mode,database_role,force_logging,open_mode,switchover_status from gv$database; DBID NAME CURRENT_SCN PROTECTION_MODE DATABASE_ROLE FOR OPEN_MODE SWITCHOVER_STATUS ---------- --------- ----------- -------------------- ---------------- --- -------------------- -------------------- 724884837 DEVDB 1446395 MAXIMUM PERFORMANCE PHYSICAL STANDBY YES MOUNTED NOT ALLOWED 724884837 DEVDB 1446395 MAXIMUM PERFORMANCE PHYSICAL STANDBY YES MOUNTED NOT ALLOWED SQL>
9、原RAC主库停止redoapply,并将RAC主库所有节点以READONLY打开
此时,备库devdb1的状态为mount状态,正在进行日志应用;如果要将devdb1调整为readonly状态,首先要取消日志应用,然后再调整到open(read only)状态.
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
SQL> alter database recover managed standby database cancel; Database altered. SQL> alter database open; Database altered. SQL> select dbid,inst_id,name,current_scn,protection_mode,database_role,force_logging,open_mode,switchover_status from gv$database; DBID INST_ID NAME CURRENT_SCN PROTECTION_MODE DATABASE_ROLE FOR OPEN_MODE SWITCHOVER_STATUS ---------- ---------- --------- ----------- -------------------- ---------------- --- -------------------- -------------------- 724884837 2 DEVDB 1454081 MAXIMUM PERFORMANCE PHYSICAL STANDBY YES MOUNTED NOT ALLOWED 724884837 1 DEVDB 1454081 MAXIMUM PERFORMANCE PHYSICAL STANDBY YES READ ONLY NOT ALLOWED SQL>
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
SQL> set linesize 200 SQL> select dbid,inst_id,name,current_scn,protection_mode,database_role,force_logging,open_mode,switchover_status from gv$database; DBID INST_ID NAME CURRENT_SCN PROTECTION_MODE DATABASE_ROLE FOR OPEN_MODE SWITCHOVER_STATUS ---------- ---------- --------- ----------- -------------------- ---------------- --- -------------------- -------------------- 724884837 2 DEVDB 1454081 MAXIMUM PERFORMANCE PHYSICAL STANDBY YES MOUNTED NOT ALLOWED 724884837 1 DEVDB 1454081 MAXIMUM PERFORMANCE PHYSICAL STANDBY YES READ ONLY NOT ALLOWED SQL> alter database open; Database altered. SQL> select dbid,inst_id,name,current_scn,protection_mode,database_role,force_logging,open_mode,switchover_status from gv$database; DBID INST_ID NAME CURRENT_SCN PROTECTION_MODE DATABASE_ROLE FOR OPEN_MODE SWITCHOVER_STATUS ---------- ---------- --------- ----------- -------------------- ---------------- --- -------------------- -------------------- 724884837 1 DEVDB 1454081 MAXIMUM PERFORMANCE PHYSICAL STANDBY YES READ ONLY NOT ALLOWED 724884837 2 DEVDB 1454081 MAXIMUM PERFORMANCE PHYSICAL STANDBY YES READ ONLY NOT ALLOWED SQL>
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
SQL> select dbid,name,current_scn,protection_mode,database_role,force_logging,open_mode,switchover_status from gv$database; DBID NAME CURRENT_SCN PROTECTION_MODE DATABASE_ROLE FOR OPEN_MODE SWITCHOVER_STATUS ---------- --------- ----------- -------------------- ---------------- --- -------------------- -------------------- 724884837 DEVDB 1459258 MAXIMUM PERFORMANCE PRIMARY YES READ WRITE TO STANDBY SQL>
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
SQL> alter database recover managed standby database using current logfile disconnect from session; Database altered. SQL>
10、查看新主库与新备库同步
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
SQL> conn scott/tiger Connected. SQL> select table_name from tabs; TABLE_NAME ------------------------------ DEPT EMP BONUS SALGRADE SQL> create table test_dg(id int ,name varchar2(10)); Table created. SQL> insert into test_dg values(1,'arcerzhang'); 1 row created. SQL> commit; Commit complete. SQL>
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
SQL> conn scott/tiger; Connected. SQL> select * from test_dg; ID NAME ---------- ---------- 1 arcerzhang SQL>
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
SQL> conn scott/tiger Connected. SQL> select * from test_dg; ID NAME ---------- ---------- 1 arcerzhang SQL>
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
SQL> conn / as sysdba Connected. SQL> select dbid,inst_id,name,current_scn,protection_mode,database_role,force_logging,open_mode,switchover_status from gv$database; DBID INST_ID NAME CURRENT_SCN PROTECTION_MODE DATABASE_ROLE FOR OPEN_MODE SWITCHOVER_STATUS ---------- ---------- --------- ----------- -------------------- ---------------- --- -------------------- -------------------- 724884837 1 DEVDB 1465105 MAXIMUM PERFORMANCE PHYSICAL STANDBY YES READ ONLY WITH APPLY NOT ALLOWED 724884837 2 DEVDB 1465105 MAXIMUM PERFORMANCE PHYSICAL STANDBY YES READ ONLY WITH APPLY NOT ALLOWED SQL>
12.2、单实例主库、RAC备库角色切换
1、新主库(单实例库)状态查看
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
dg-> sqlplus SQL*Plus: Release 11.2.0.3.0 Production on Sun Nov 18 13:21:53 2012 Copyright (c) 1982, 2011, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options SQL> show parameter instance_name NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ instance_name string phydb SQL> set line 300 SQL> select dbid,name,current_scn,protection_mode,database_role,force_logging,ope n_mode,switchover_status from v$database; DBID NAME CURRENT_SCN PROTECTION_MODE DATABASE_ROLE FOR OPEN_MODE SWITCHOVER_STATUS ---------- --------- ----------- -------------------- -------------- --- -------------------- -------------------- 692948393 DEVDB 7409960 MAXIMUM PERFORMANCE PRIMARY YES READ WRITE TO STANDBY SQL>
2、新备库(RAC库)状态查看
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
SQL> select inst_id,dbid,name,current_scn,protection_mode,database_role,force_log ging,open_mode,switchover_status from gv$database; INST_ID DBID NAME CURRENT_SCN PROTECTION_MODE DATABASE_ROLE FOR OPEN_MODE SWITCHOVER_STATUS ---------- ---------- --------- ----------- -------------------- ---------------- --- -------------------- -------------------- 1 692948393 DEVDB 7410081 MAXIMUM PERFORMANCE PHYSICAL STANDBY YES READ ONLY WITH APPLY NOT ALLOWED 2 692948393 DEVDB 7410081 MAXIMUM PERFORMANCE PHYSICAL STANDBY YES READ ONLY WITH APPLY NOT ALLOWED SQL>
3、新备库(RAC库)停止节点2
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
node2-> sqlplus SQL*Plus: Release 11.2.0.3.0 Production on Sun Nov 18 13:24:40 2012 Copyright (c) 1982, 2011, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options SQL> show parameter instance_name NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ instance_name string devdb2 SQL> set line 300 SQL> select inst_id,dbid,name,current_scn,protection_mode,database_role,force_log ging,open_mode,switchover_status from gv$database; INST_ID DBID NAME CURRENT_SCN PROTECTION_MODE DATABASE_ROLE FOR OPEN_MODE SWITCHOVER_STATUS ---------- ---------- --------- ----------- -------------------- ---------------- --- -------------------- -------------------- 2 692948393 DEVDB 7410177 MAXIMUM PERFORMANCE PHYSICAL STANDBY YES READ ONLY WITH APPLY NOT ALLOWED 1 692948393 DEVDB 7410177 MAXIMUM PERFORMANCE PHYSICAL STANDBY YES READ ONLY WITH APPLY NOT ALLOWED SQL> shutdown immediate; Database closed. Database dismounted. ORACL
4、新主库(单实例库)切换到备库
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
SQL> show parameter instance_name NAME TYPE VALUE ------------------------------------ ----------- ----------------------- instance_name string phydb SQL> select dbid,name,current_scn,protection_mode,database_role,force_logging,open_mode,switchover_status from v$database; DBID NAME CURRENT_SCN PROTECTION_MODE DATABASE_ROLE FOR OPEN_MODE SWITCHOVER_STATUS ---------- --------- ----------- -------------------- -------------- --- -------------------- -------------------- 692948393 DEVDB 7410498 MAXIMUM PERFORMANCE PRIMARY YES READ WRITE TO STANDBY SQL> alter database commit to switchover to physical standby with session shutdown; Database altered. SQL> select dbid,name,current_scn,protection_mode,database_role,force_logging,open_mode,switchover_status from v$database; DBID NAME CURRENT_SCN PROTECTION_MODE DATABASE_ROLE FOR OPEN_MODE SWITCHOVER_STATUS ---------- --------- ----------- -------------------- ------------------ --- ----------------- -------------------- 692948393 DEVDB 7410519 MAXIMUM PERFORMANCE PHYSICAL STANDBY YES READ WRITE RECOVERY NEEDED SQL>
5、新备库(RAC库)节点1切换到主库
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
SQL> alter database recover managed standby database using current logfile disconnect from session; Database altered. SQL> show parameter instance_name NAME TYPE VALUE -------------- ---------------------- ----------------------------------------- instance_name string devdb1 SQL> select dbid,name,current_scn,protection_mode,database_role,force_logging,open_mode,switchover_status from gv$database; DBID NAME CURRENT_SCN PROTECTION_MODE DATABASE_ROLE FOR OPEN_MODE SWITCHOVER_STATUS ---------- --------- ----------- -------------------- --------------- --- -------------------- -------------------- 692948393 DEVDB 7430520 MAXIMUM PERFORMANCE PHYSICAL STANDBY YES READ ONLY WITH APPLY TO PRIMARY SQL> alter database recover managed standby database using current logfile disconnect from session; Database altered. SQL> alter database commit to switchover to primary; Database altered. SQL> select dbid,name,current_scn,protection_mode,database_role,force_logging,open_mode,switchover_status from gv$database; DBID NAME CURRENT_SCN PROTECTION_MODE DATABASE_ROLE FOR OPEN_MODE SWITCHOVER_STATUS ---------- --------- ----------- -------------------- -------------- --- -------------------- -------------------- 692948393 DEVDB 0 MAXIMUM PERFORMANCE PRIMARY YES MOUNTED NOT ALLOWED SQL>
6、新备库(RAC库)全部启动所有节点
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
SQL> select dbid,name,current_scn,protection_mode,database_role,force_logging,open_mode,switchover_status from gv$database; DBID NAME CURRENT_SCN PROTECTION_MODE DATABASE_ROLE FOR OPEN_MODE SWITCHOVER_STATUS ---------- --------- ----------- -------------------- ---------------- --- -------------------- -------------------- 724884837 DEVDB 1552766 MAXIMUM PERFORMANCE PRIMARY YES READ WRITE TO STANDBY 724884837 DEVDB 1552766 MAXIMUM PERFORMANCE PRIMARY YES READ WRITE TO STANDBY SQL>
7、新备库(单实例库)重新启动并开始redo apply
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
SQL> alter database recover managed standby database using current logfile disconnect from session; Database altered. SQL> select dbid,name,current_scn,protection_mode,database_role,force_logging,open_mode,switchover_status from gv$database; DBID NAME CURRENT_SCN PROTECTION_MODE DATABASE_ROLE FOR OPEN_MODE SWITCHOVER_STATUS ---------- --------- ----------- -------------------- ---------------- --- -------------------- -------------------- 724884837 DEVDB 1550086 MAXIMUM PERFORMANCE PHYSICAL STANDBY YES READ ONLY WITH APPLY NOT ALLOWED SQL>