目录
步骤1设置 force logging，启用强制日志... 2
步骤9 启动Standby数据库到NOMOUNT模式... 13
步骤10 从主数据库复制文件到Standby数据库... 13
步骤12 正常开启和关闭Data Guard的流程... 17
2 查看Standby数据库Standby Redo日志的使用情况... 19
说明：使用RMAN创建物理standby 备库只安装数据库软件，没有创建数据库
实验环境
1. 操作系统
[root@dg ~]#uname -r
2.6.32-279.el6.x86_64
[root@dg ~]# cat/etc/redhat-release
Red HatEnterprise Linux Server release 6.3 (Santiago)
2. 数据库
SQL> select *from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release11.2.0.3.0 - Production
CORE 11.2.0.3.0 Production
TNS for Linux:Version 11.2.0.3.0 - Production
NLSRTL Version11.2.0.3.0 - Production
3. 映射关系
Host IP DB_NAME DB_UNIQUE_NAME Net Service Name(网络服务名)
主库node1 192.168.100.66 orcl orcl orcl1
主库node2 192.168.100.67 orcl orcl orcl2
备库dg 192.168.100.70 orcl phydb phydb
4. 主库
[grid@node1 ~]$crs_stat -t
Name Type Target State Host
------------------------------------------------------------
ora.CRS.dg ora....up.type ONLINE ONLINE node1
ora.DATA.dg ora....up.type ONLINE ONLINE node1
ora.FRA.dg ora....up.type ONLINE ONLINE node1
ora....ER.lsnrora....er.type ONLINE ONLINE node1
ora....N1.lsnrora....er.type ONLINE ONLINE node1
ora.asm ora.asm.type ONLINE ONLINE node1
ora.cvu ora.cvu.type ONLINE ONLINE node1
ora.gsd ora.gsd.type OFFLINE OFFLINE
ora....networkora....rk.type ONLINE ONLINE node1
ora....SM1.asmapplication ONLINE ONLINE node1
ora....E1.lsnrapplication 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.asmapplication ONLINE ONLINE node2
ora....E2.lsnrapplication 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.orcl.db ora....se.type ONLINE ONLINE node1
ora.scan1.vip ora....ip.type ONLINE ONLINE node1
SQL> col namefor a10
SQL> selectname,state from v$asm_diskgroup;
NAME STATE
---------------------
CRS MOUNTED
DATA MOUNTED
FRA MOUNTED
5. 备库
[grid@dg ~]$crs_stat -t
Name Type Target State Host
------------------------------------------------------------
ora.CRS.dg ora....up.type ONLINE ONLINE dg
ora.DATA.dg ora....up.type ONLINE ONLINE dg
ora.FRA.dg ora....up.type ONLINE ONLINE dg
ora....ER.lsnrora....er.type ONLINE ONLINE dg
ora.asm ora.asm.type ONLINE ONLINE dg
ora.cssd ora.cssd.type ONLINE ONLINE dg
ora.diskmon ora....on.type OFFLINE OFFLINE
ora.evmd ora.evm.type ONLINE ONLINE dg
ora.ons ora.ons.type OFFLINE OFFLINE
SQL> col namefor a10
SQL> selectname,state from v$asm_diskgroup;
NAME STATE
---------------------
CRS MOUNTED
DATA MOUNTED
FRA MOUNTED
步骤1设置 force logging，启用强制日志
SQL>select name,log_mode,force_logging from gv$database;
NAME LOG_MODE FOR
--------------------- ---
ORCL NOARCHIVELOG NO
ORCL NOARCHIVELOG NO
SQL>alter database force logging;
Databasealtered.
SQL>select name,log_mode,force_logging from gv$database;
NAME LOG_MODE FOR
--------------------- ---
ORCL NOARCHIVELOG YES
ORCL NOARCHIVELOG YES
步骤2主库设为归档模式
[grid@node1~]$ srvctl status database -d orcl
Instance orcl1 is running on node node1
Instance orcl2 is running on node node2
[grid@node1 ~]$ srvctl stop database -d orcl
[grid@node1 ~]$ srvctl status database -d orcl
Instance orcl1 is not running on node node1
Instance orcl2 is not running on node node2
[oracle@node1~]$ sqlplus / as sysdba
SQL> startup mount;
SQL> alter database archivelog;
SQL> select open_mode,log_mode from v$database;
OPEN_MODE LOG_MODE
-------------------- ------------
MOUNTED ARCHIVELOG
SQL> shutdown immediate
[grid@node1 ~]$ srvctl start database -d orcl
[oracle@node1 ~]$ sqlplus / as sysdba
SQL> select name,log_mode,force_logging from gv$database;
NAME LOG_MODE FOR
--------- ------------ ---
ORCL ARCHIVELOG YES
ORCL ARCHIVELOG YES
SQL> archive log list;
Database logmode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 3
Next log sequence to archive 5
Current log sequence 5
由此可见，归档日志放置在闪回恢复区中
SQL> show parameter log_archive
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_1 string
SQL> show parameter recovery
NAME TYPE VALUE
------------------------------------ -----------------------------------------
db_recovery_file_dest string +FRA
可以在RMAN中看一下归档路径
[oracle@node2 ~]$ rman target /
RMAN> list archivelog all;
List of Archived Log Copies for database with db_unique_name ORCL
=====================================================================
Key Thrd Seq S Low Time
------- ---- ------- - ---------
1 1 10 A 07-APR-15
Name: +FRA/orcl/archivelog/2015_04_08/thread_1_seq_10.261.876453695
2 2 7 A 07-APR-15
Name:+FRA/orcl/archivelog/2015_04_08/thread_2_seq_7.262.876453695
步骤3 RAC主数据库执行RMAN全备
创建备份存放目录
[root@node1u01]# mkdir rman_backup
[root@node1u01]# chown oracle:oinstall rman_backup/ -R
查看需要备份的文件
[oracle@node1 ~]$ rman
RMAN> report need backup;
using target database control file instead of recovery catalog
RMAN retention policy will be applied to the command
RMAN retention policy is set to redundancy 1
Report of files with less than 1 redundant backups
File #bkps Name
---- ----- -----------------------------------------------------
1 0 +DATA/orcl/datafile/system.256.876035647
2 0 +DATA/orcl/datafile/sysaux.257.876035653
3 0 +DATA/orcl/datafile/undotbs1.258.876035653
4 0 +DATA/orcl/datafile/users.259.876035657
5 0 +DATA/orcl/datafile/example.264.876036177
6 0 +DATA/orcl/datafile/undotbs2.265.876037165
删除全部的归档文件（可选）
RMAN> delete archivelog all;
Do you really want to delete the above objects (enter YES or NO)? y
查看当前数据库的备份信息
RMAN>list backup;
specification does not match any backup in the repository
如果有备份并且想删除
RMAN>delete backup;
查看当前数据库的归档信息
RMAN>list archivelog all;
specification does not match any archived log in the repository
执行备份脚本备份数据文件和归档文件
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 '/u01/rman_backup/Full_%U.bak';
7> backup archivelog all format '/u01/rman_backup/ARC_%U.bak';
8> release channel c1;
9> release channel c2;
10> release channel c3;
11> release channel c4;
12> }
[oracle@node1rman_backup]$ ll
total 1237392
-rw-r----- 1 oracle asmadmin 6308352 Apr 8 05:05ARC_0dq3rdtb_1_1.bak
-rw-r----- 1 oracle asmadmin 645963776 Apr 8 05:03 Full_08q3rdd6_1_1.bak
-rw-r----- 1 oracle asmadmin 521764864 Apr 8 05:03 Full_09q3rdd7_1_1.bak
-rw-r----- 1 oracle asmadmin 74391552 Apr 8 05:01Full_0aq3rdd7_1_1.bak
-rw-r----- 1 oracle asmadmin 18546688 Apr 8 04:57Full_0bq3rdd7_1_1.bak
-rw-r----- 1 oracle asmadmin 98304 Apr 8 04:57Full_0cq3rddi_1_1.bak
[oracle@node1 rman_backup]$ du -sh
1.2G .
备份控制文件
RMAN>backup device type disk format '/u01/rman_backup/standby_%U.ctl' currentcontrolfile for standby;
备份参数文件
SQL>create pfile='/u01/rman_backup/initphydb.ora' from spfile;
[oracle@node1rman_backup]$ ll
total 1336744
-rw-r----- 1 oracle asmadmin 26608128 Apr 9 02:12ARC_0kq3to4d_1_1.bak
-rw-r----- 1 oracle asmadmin 15542784 Apr 9 02:12ARC_0lq3to4d_1_1.bak
-rw-r----- 1 oracle asmadmin 567762944 Apr 9 02:11 Full_0fq3tnlu_1_1.bak
-rw-r----- 1 oracle asmadmin 646488064 Apr 9 02:11 Full_0gq3tnlu_1_1.bak
-rw-r----- 1 oracle asmadmin 75210752 Apr 9 02:08Full_0hq3tnlv_1_1.bak
-rw-r----- 1 oracle asmadmin 18546688 Apr 9 02:05Full_0iq3tnlv_1_1.bak
-rw-r----- 1 oracle asmadmin 98304 Apr 9 02:09Full_0jq3tnrv_1_1.bak
-rw-r--r-- 1 oracle asmadmin 1464 Apr 9 02:18 initphydb.ora
-rw-r----- 1 oracle asmadmin 18546688 Apr 9 02:15standby_0mq3to9p_1_1.ctl
将备份的所有文件传递到备库上
[oracle@node1rman_backup]$ scp * 192.168.100.70:/u01/rman_backup/
将node1的口令文件分别传递到备库和node2上的对应位置，保持3个数据库的口令文件完全相同
[oracle@node1 rman_backup]$
scp $ORACLE_HOME/dbs/orapworcl1192.168.100.70://$ORACLE_HOME/dbs/orapwphydb
scp $ORACLE_HOME/dbs/orapworcl1192.168.100.67://$ORACLE_HOME/dbs/orapworcl2
步骤4 修改备库初始化参数
[oracle@dgdbs]$ cat initphydb.ora
phydb.__db_cache_size=452984832
phydb.__java_pool_size=4194304
phydb.__large_pool_size=4194304
phydb.__oracle_base='/u01/app/oracle'#ORACLE_BASE set fromenvironment
phydb.__pga_aggregate_target=209715200
phydb.__sga_target=838860800
phydb.__shared_io_pool_size=0
phydb.__shared_pool_size=369098752
phydb.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/phydb/adump'
*.audit_trail='db'
*.cluster_database=false
*.compatible='11.2.0.0.0'
*.core_dump_dest='/u01/app/oracle/diag/rdbms/phydb/cdump'
*.control_files='+DATA/phydb/controlfile/cont.ctl'
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_domain=''
*.db_name='orcl'
*.db_recovery_file_dest='+FRA'
*.db_recovery_file_dest_size=4558159872
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=phydbXDB)'
*.open_cursors=300
*.pga_aggregate_target=209715200
*.processes=200
*.remote_login_passwordfile='exclusive'
*.sessions=225
*.sga_target=838860800
*.thread=1
*.undo_tablespace='UNDOTBS1'
*.service_names='phydb'
*.db_unique_name='phydb'
*.fal_client='phydb'
*.fal_server='orcl1','orcl2'
*.log_archive_config='dg_config=(orcl,phydb)'
*.log_archive_dest_1='location=USE_DB_RECOVERY_FILE_DESTvalid_for=(all_logfiles,all_roles) db_unique_name=phydb'
*.log_archive_dest_2='service=orcl1 lgwr syncaffirm valid_for=(online_logfiles,primary_role) db_unique_name=orcl'
*.LOG_ARCHIVE_DEST_STATE_1=ENABLE
*.LOG_ARCHIVE_DEST_STATE_2=ENABLE
*.standby_file_management='AUTO'
*.log_archive_format='ARC_%t_%S_%r.arc'
*.LOG_FILE_NAME_CONVERT='+DATA/orcl/','+DATA/phydb/'
*.db_file_name_convert='+DATA/orcl/','+DATA/phydb/'
步骤5 在备库创建相关目录
[oracle@dg dbs]$ mkdir-p /u01/app/oracle/admin/phydb/adump'
[oracle@dg dbs]$ mkdir-p /u01/app/oracle/diag/rdbms/phydb/cdump
[grid@dg ~]$ asmcmd –p
ASMCMD [+data] >mkdir phydb
步骤6 配置RAC主库和物理备库的tnsnames.ora
RAC主库的各个节点和物理备库的tnsnames.ora文件的内容是完全一样的。
[oracle@dg admin]$ vitnsnames.ora
# tnsnames.ora NetworkConfiguration File:/u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora
# Generated by Oracleconfiguration tools.
ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = scan-cluster.localdomain)(PORT = 1521))
(SERVICE_NAME = orcl)
)
)
orcl1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST =192.168.100.66)(PORT = 1521))
(INSTANCE_NAME = orcl1)
)
)
orcl2 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST =192.168.100.67)(PORT = 1521))
(INSTANCE_NAME = orcl2)
)
)
phydb =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST =192.168.100.70)(PORT = 1521))
(SERVICE_NAME = phydb)
)
)
有一个不同的地方在于物理备库tnsnames.ora文件修改如下内容：
ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.71)(PORT = 1521))
(SERVICE_NAME = orcl)
)
)
验证：
[oracle@dg ~]$ tnsping orcl
TNS Ping Utility forLinux: Version 11.2.0.3.0 - Production on 10-APR-2015 23:15:51
Copyright (c) 1997,2011, Oracle. All rights reserved.
Used parameter files:
Used TNSNAMES adapterto resolve the alias
Attempting to contact(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.71)(PORT= 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl)))
OK (30 msec)
[oracle@dg ~]$ tnsping orcl1
TNS Ping Utility forLinux: Version 11.2.0.3.0 - Production on 10-APR-2015 23:16:01
Copyright (c) 1997,2011, Oracle. All rights reserved.
Used parameter files:
Used TNSNAMES adapterto resolve the alias
Attempting to contact(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.66)(PORT= 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl)(INSTANCE_NAME = orcl1)))
OK (10 msec)
[oracle@dg ~]$ tnsping orcl2
TNS Ping Utility forLinux: Version 11.2.0.3.0 - Production on 10-APR-2015 23:16:04
Copyright (c) 1997,2011, Oracle. All rights reserved.
Used parameter files:
Used TNSNAMES adapterto resolve the alias
Attempting to contact(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.67)(PORT= 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl)(INSTANCE_NAME = orcl2)))
OK (30 msec)
[oracle@dg ~]$ sqlplus / assysdba
SQL> conn sys/oracle@orcl1as sysdba
Connected.
SQL> connsys/oracle@orcl2 as sysdba
Connected.
步骤7 启动Standby数据库到NOMOUNT模式
SQL> startup nomount
ORACLEinstance started.
Total System Global Area 835104768 bytes
Fixed Size 2232960 bytes
Variable Size 377490816 bytes
Database Buffers 452984832 bytes
Redo Buffers 2396160 bytes
步骤8 在备库使用RMAN恢复数据库
1）恢复控制文件
[oracle@dgdbs]$ rman
RMAN>
restore standby controlfile from'/u01/rman_backup/standby_0mq3to9p_1_1.ctl';
Startingrestore at 09-APR-15
usingtarget database control file instead of recovery catalog
allocatedchannel: ORA_DISK_1
channelORA_DISK_1: SID=24 device type=DISK
channelORA_DISK_1: restoring control file
channelORA_DISK_1: restore complete, elapsed time: 00:00:03
outputfile name=+DATA/phydb/controlfile/cont.ctl
Finishedrestore at 09-APR-15
检查：
[grid@dg~]$ asmcmd –p
ASMCMD[+data/phydb/CONTROLFILE] > ls
cont.ctl
current.256.876539383
2）将备库启动到mount状态
[oracle@dg dbs]$ sqlplus / as sysdba
SQL> alter database mount;
3）RMAN restore备库恢复数据文件
[oracle@dgdbs]$ rman
connectedto target database: ORCL (DBID=1403816752, 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>}
4）查看恢复的文件信息
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.876540211
+DATA/phydb/datafile/sysaux.258.876540211
+DATA/phydb/datafile/system.260.876540211
+DATA/phydb/datafile/undotbs1.259.876540211
+DATA/phydb/datafile/undotbs2.261.876540215
+DATA/phydb/datafile/users.262.876540215
+DATA/phydb/onlinelog/group_1.261.876036033
+DATA/phydb/onlinelog/group_2.262.876036057
+DATA/phydb/onlinelog/group_3.266.876037547
+DATA/phydb/onlinelog/group_4.267.876037573
+FRA/orcl/onlinelog/group_1.257.876036043
+FRA/orcl/onlinelog/group_2.258.876036071
+FRA/orcl/onlinelog/group_3.259.876037561
+FRA/orcl/onlinelog/group_4.260.876037585
15 rows selected.
步骤9 创建RAC主库和物理备库的standby logfile联机redo日志文件
RAC主库和物理备库的redo日志文件的创建是完全一样的，这里只例举物理备库上的创建过程。
1）查看数据库信息
SQL> set line 300
SQL> select dbid,nameswitchover_status,db_unique_name,database_role,open_mode,current_scn fromv$database;
DBID SWITCHOVE DB_UNIQUE_NAME DATABASE_ROLE OPEN_MODE CURRENT_SCN
---------- --------- ------------------------------ ------------------------------------ -----------
1403816752 ORCL phydb PHYSICAL STANDBY MOUNTED 1639928
2） 执行下面的SQL语句查询当前数据库的联机Redo日志：
SQL> select THREAD#,BYTES/1024/1024"SIZE(MB)",MEMBERS from V$LOG;
THREAD# SIZE(MB) MEMBERS
---------- ---------- ----------
1 50 2
1 50 2
2 50 2
2 50 2
THREAD#表示数据库节点编号，当前数据库有两个节点，每个节点有2个日志组
SIZE(MB)表示日志的大小，当前每个日志的大小为50MB
MEMBERS表示日志组的成员数，当前每个日志组有两个成员
SQL> selectGROUP#,THREAD#,SEQUENCE#,BYTES/1024/1024 from v$log;
GROUP# THREAD# SEQUENCE# BYTES/1024/1024
---------- ---------- ---------- ---------------
1 1 15 50
2 1 16 50
3 2 13 50
4 2 12 50
SQL> select * from v$standby_log;
no rows selected
3）创建standby logfile联机redo日志文件
SQL> alter database add standby logfile thread 1group 5 size 50m,group 6 size 50m,group 7 size 50m;
SQL>alter database add standby logfile thread 2 group 8 size 50m,group 9 size50m,group 10 size 50m;
SQL> select GROUP#,DBID,THREAD#,SEQUENCE#,BYTESfrom v$standby_log;
GROUP# DBID THREAD# SEQUENCE# BYTES
---------- ---------------------------------------- ---------- --------------------
5 UNASSIGNED 1 0 52428800
6 UNASSIGNED 1 0 52428800
7 UNASSIGNED 1 0 52428800
8 UNASSIGNED 2 0 52428800
9 UNASSIGNED 2 0 52428800
10 UNASSIGNED 2 0 52428800
6 rows selected
步骤10 配置RAC主库的初始化参数
修改初始化参数文件有两种方法，一种是使用SQL语句直接修改，一种是使用spfile生成pfile文件，然后修改pfile文件之后再生成spfile
1） 使用SQL语句修改
再修改之前确保RAC的各个节点都是用spfile启动的，使用以下SQL语句查看：
SQL> show parameter spfile;
SQL> alter system set log_archive_dest_2='service=phydblgwr sync affirm valid_for=(online_logfiles,primary_role) db_unique_name=phydb'sid=’*’;
SQL> alter system set log_archive_dest_1='location=USE_DB_RECOVERY_FILE_DESTvalid_for=(all_logfiles,all_roles) db_unique_name=orcl' sid=’*’;
SQL> alter system setlog_archive_config='dg_config=(orcl,phydb)' sid=’*’;
SQL> alter system set DB_UNIQUE_NAME='orcl'sid=’*’;
SQL> alter system set LOG_ARCHIVE_DEST_STATE_1=ENABLEsid=’*’;
SQL> alter system set LOG_ARCHIVE_DEST_STATE_2=ENABLEsid=’*’;
SQL> alter system set standby_file_management='AUTO'sid=’*’;
SQL> alter system set fal_server='phydb'sid=’*’;
SQL> alter system set fal_ client ='orcl1'sid=’orcl1’;
SQL> alter system set fal_server='orcl2'sid=’orcl2’;
以下两个参数修改后需要重启数据库才能生效
SQL> alter system set DB_FILE_NAME_CONVERT='+DATA/phydb/','+DATA/orcl/'sid=’*’ scope=spfile;
SQL> alter system set LOG_FILE_NAME_CONVERT='+DATA/phydb/','+DATA/orcl/'sid=’*’ scope=spfile;
2）修改pfile文件生成spfile
[oracle@node1 rman_backup]$ catinitphydb.ora
orcl1.__db_cache_size=452984832
orcl2.__db_cache_size=511705088
orcl1.__java_pool_size=4194304
orcl2.__java_pool_size=4194304
orcl1.__large_pool_size=4194304
orcl2.__large_pool_size=4194304
orcl2.__oracle_base='/u01/app/oracle'#ORACLE_BASEset from environment
orcl1.__oracle_base='/u01/app/oracle'#ORACLE_BASEset from environment
orcl1.__pga_aggregate_target=209715200
orcl2.__pga_aggregate_target=209715200
orcl1.__sga_target=838860800
orcl2.__sga_target=838860800
orcl1.__shared_io_pool_size=0
orcl2.__shared_io_pool_size=0
orcl1.__shared_pool_size=369098752
orcl2.__shared_pool_size=310378496
orcl1.__streams_pool_size=0
orcl2.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/orcl/adump'
*.audit_trail='db'
*.cluster_database=true
*.compatible='11.2.0.0.0'
*.control_files='+DATA/orcl/controlfile/current.260.876036017','+FRA/orcl/controlfile/current.256.876036017'
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_domain=''
*.db_name='orcl'
*.db_recovery_file_dest='+FRA'
*.db_recovery_file_dest_size=4558159872
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP)(SERVICE=orclXDB)'
orcl1.instance_number=1
orcl2.instance_number=2
*.open_cursors=300
*.pga_aggregate_target=209715200
*.processes=200
*.remote_listener='scan-cluster.localdomain:1521'
*.remote_login_passwordfile='exclusive'
*.sessions=225
*.sga_target=838860800
orcl2.thread=2
orcl1.thread=1
orcl1.undo_tablespace='UNDOTBS1'
orcl2.undo_tablespace='UNDOTBS2'
*.DB_UNIQUE_NAME='orcl'
*.log_archive_config='dg_config=(orcl,phydb)'
*.log_archive_dest_1='location=USE_DB_RECOVERY_FILE_DESTvalid_for=(all_logfiles,all_roles) db_unique_name=orcl'
*.log_archive_dest_2='service=phydb lgwrsync affirm valid_for=(online_logfiles,primary_role) db_unique_name=phydb'
*.LOG_ARCHIVE_DEST_STATE_1=ENABLE
*.LOG_ARCHIVE_DEST_STATE_2=ENABLE
*.standby_file_management='AUTO'
*.fal_server='phydb'
*.DB_FILE_NAME_CONVERT='+DATA/phydb/','+DATA/orcl/'
*.LOG_FILE_NAME_CONVERT='+DATA/phydb/','+DATA/orcl/'
orcl1.fal_client='orcl1'
orcl2.fal_client='orcl2'
[grid@node1 ~]$ srvctl stop database -dorcl
[oracle@node1 ~]$ sqlplus / as sysdba
SQL> createspfile='+DATA/orcl/spfileorcl.ora' from pfile='/u01/rman_backup/initphydb.ora';
[grid@node1 ~]$ srvctl start database -dorcl
3）验证配置后的信息
SQL> show parameter log_archive
NAME TYPE VALUE
----------------------------------------------- ------------------------------
log_archive_config string dg_config=(orcl,phydb)
log_archive_dest_1 string location=USE_DB_RECOVERY_FILE_
DEST valid_for=(all_logfiles,a
ll_roles) db_unique_name=orcl
log_archive_dest_2 string service=phydb lgwr sync affirm
valid_for=(online_logfiles,pr
imary_role) db_unique_name=phydb
log_archive_dest_state_1 string ENABLE
log_archive_dest_state_2 string ENABLE
SQL> show parameter standby
NAME TYPE VALUE
----------------------------------------------- ------------------------------
standby_file_management string AUTO
步骤11 查看归档传递信息
1）在主库执行以下SQL语句查看归档信息
SQL>select GROUP#,THREAD#,SEQUENCE#,STATUS from v$log;
GROUP# THREAD# SEQUENCE# STATUS
---------- ---------- ---------- ----------------
1 1 21 CURRENT
2 1 20 INACTIVE
3 2 19 CURRENT
4 2 18 INACTIVE
2）在备库执行以下命令查看
RMAN>list archivelog all;
List of Archived Log Copies for database with db_unique_name PHYDB
=====================================================================
Key Thrd Seq S Low Time
------- ---- ------- - ---------
6 1 19 A 09-APR-15
Name: +FRA/phydb/archivelog/2015_04_09/thread_1_seq_19.267.876548057
11 1 20 A 09-APR-15
Name: +FRA/phydb/archivelog/2015_04_09/thread_1_seq_20.272.876548869
7 2 17 A 09-APR-15
Name: +FRA/phydb/archivelog/2015_04_09/thread_2_seq_17.268.876548753
10 2 18 A 09-APR-15
Name: +FRA/phydb/archivelog/2015_04_09/thread_2_seq_18.271.876548769
3）在主库做日志切换后再查看
SQL> alter system switch logfile;
步骤11 备库应用归档日志，与主库进行同步
1） 查看归档日志是否同步
SQL>col name for a40
SQL> select sequence#,name,applied from v$archived_log;
SEQUENCE#NAME APPLIED
---------- ---------------------------------------- ---------
19+FRA/phydb/archivelog/2015_04_09/thread_1_seq_19.267.876548057 NO
20 +FRA/phydb/archivelog/2015_04_09/thread_1_seq_20.272.876548869 NO
17+FRA/phydb/archivelog/2015_04_09/thread_2_seq_17.268.876548753 NO
18 +FRA/phydb/archivelog/2015_04_09/thread_2_seq_18.271.876548769 NO
2）备库应用归档日志
SQL> alterdatabase recover managed standby database using current logfile disconnect fromsession;
步骤12 open备库
1）查看备库信息
SQL> selectdb_unique_name,switchover_status,database_role,open_mode from v$database;
DB_UNIQUE_NAME SWITCHOVER_STATUS DATABASE_ROLE OPEN_MODE
-------------------------------------------------- ---------------- --------------------
phydb NOT ALLOWED PHYSICAL STANDBY MOUNTED
2）取消归档日志的应用
SQL> alter database recover managed standby database cancel;
3）打开备库
SQL> alter database open;
SQL> selectdb_unique_name,switchover_status,database_role,open_mode from v$database;
DB_UNIQUE_NAME SWITCHOVER_STATUS DATABASE_ROLE OPEN_MODE
-------------------------------------------------- ---------------- --------------------
phydb NOT ALLOWED PHYSICAL STANDBY READ ONLY
SQL> alterdatabase recover managed standby database using current logfile disconnect fromsession;
SQL> selectdb_unique_name,switchover_status,database_role,open_mode from v$database;
DB_UNIQUE_NAME SWITCHOVER_STATUS DATABASE_ROLE OPEN_MODE
-------------------------------------------------- ---------------- --------------------
phydb NOT ALLOWED PHYSICAL STANDBY READ ONLY WITH APPLY
1 检查日志传输服务
下面通过主库V$ARCHIVED_LOG视图检查日志传输服务。
1） 执行以下SQL语句查询实例名称对应的线程号：
SQL> select THREAD#,INSTANCE_NAME FROM GV$INSTANCE;
THREAD# INSTANCE_NAME
---------- ----------------
1 orcl1
2 orcl2
THREAD#表示RAC的第n个实例。
2） 执行以下SQL语句查询每个实例所有目的地生成的日志的最大序列号：
SQL> SELECT THREAD#,DEST_ID,MAX(SEQUENCE#) FROMV$ARCHIVED_LOG GROUP BY THREAD#,DEST_ID ORDER BY THREAD#;
THREAD# DEST_ID MAX(SEQUENCE#)
---------- ---------- --------------
1 1 42
1 2 42
2 1 36
2 2 36
DEST_ID等于1对应初始化参数配置中的LOG_ARCHIVE_DEST_1，表示本地归档目的地；等于2对应初始化参数中的LOG_ARCHIVE_DEST_2，表示远程Standby数据库目的地。
从上面的查询结果可以看出，目前所有日志都成功归档到目的地。执行下面的命令手动切换日志三次：
SQL> alter system archive log current;
System altered.
SQL> /
System altered.
SQL> r
1* alter system archive logcurrent
System altered.
再次执行上面的查询：
SQL> SELECT THREAD#,DEST_ID,MAX(SEQUENCE#) FROM V$ARCHIVED_LOGGROUP BY THREAD#,DEST_ID ORDER BY THREAD#;
THREAD# DEST_IDMAX(SEQUENCE#)
---------- ---------- --------------
1 1 45
1 2 45
2 1 39
2 2 39
从上面的结果可以看出，日志都成功归档到目的地，证明日志传输服务工作正常。
2 查看Standby数据库Standby Redo日志的使用情况
Standby数据库执行下面的SQL语句查询StandbyRedo日志的使用情况：
SQL> SELECTTHREAD#,SEQUENCE#,ARCHIVED,STATUS FROM V$STANDBY_LOG;
THREAD# SEQUENCE# ARC STATUS
-------------------- --- ----------
1 0 NO UNASSIGNED
1 43 YES ACTIVE
1 0 NO UNASSIGNED
2 37 YES ACTIVE
2 0 NO UNASSIGNED
2 0 YES UNASSIGNED
在主数据库执行以下SQL语句查询日志序列号：
SQL>SELECT THREAD#,SEQUENCE#,STATUS FROM V$LOG;
THREAD# SEQUENCE# STATUS
-------------------- ----------------
1 42 INACTIVE
1 43 CURRENT
2 36 INACTIVE
2 37 CURRENT
从上面的结果可以看出，主数据库的CURRENT联机Redo日志与Standby数据库的ACTIVE状态的Standby Redo日志序列号相同，证明Redo传输服务启用了实时传输。
3 监控日志传输性能
1） 在Redo源数据库执行以下SQL语句查询显示目的地2的响应时间，目的地ID号为2对应的源数据库的初始化参数为LOG_ARCHIVE_DEST_2：
SQL>SELECT FREQUENCY,DURATION FROM V$REDO_DEST_RESP_HISTOGRAM WHERE DEST_ID=2 ANDFREQUENCY > 1;
FREQUENCY DURATION
--------------------
1924 1
10 2
1 3
从上面的结果可以看出，响应时间为1秒的命中率为1924次，最大的响应时间是3秒，且只有一次。
2） 在Redo源数据库执行以下SQL语句查询显示目的地2的最慢响应时间：
SQL> SELECT MAX(DURATION) FROMV$REDO_DEST_RESP_HISTOGRAM WHERE DEST_ID=2 AND FREQUENCY > 1;
MAX(DURATION)
-------------
3
3）在Redo源数据库执行以下SQL语句查询显示目的地2的最快响应时间：
SQL>SELECT MIN(DURATION) FROM V$REDO_DEST_RESP_HISTOGRAM WHERE DEST_ID=2 ANDFREQUENCY > 1;
MIN(DURATION)
-------------
1
4 管理日志应用服务
1） 启动Redo Apple
执行下面的SQL语句在前台启动Redo Apple：
SQL> alter database recover managed standby database;
执行以下SQL语句在后台启动RedoApple：
SQL> alter database recover managed standbydatabase disconnect from session;
执行以下SQL语句在前台启动Redo 实时应用：
SQL> alter database recover managed standbydatabase using current logfile;
执行以下SQL语句在后台启动Redo 实时应用：
SQL> alter database recovermanaged standby database using current logfile disconnect from session;
2） 停止Redo Apple
执行以下的SQL语句停止Redo Apple：
SQL> ALTERDATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
5 主备数据库切换
DataGuard的切换有两种：Switchover（正常切换）和Failover（异常切换）
1 Switchover切换
1）主数据库执行操作
步骤1 确保主库修改了相关参数fal_client，fal_server，standby_file_management，DB_FILE_NAME_CONVERT，LOG_FILE_NAME_CONVERT
步骤2 确保主库创建了standby logfile联机redo日志文件
步骤3执行以下SQL语句确定主数据库是否满足转换条件：
Selectname,protection_mode,database_role,force_logging,open_mode,switchover_statusfrom gv$database;
NAME PROTECTION_MODE DATABASE_ROLE FOR OPEN_MODE SWITCHOVER_STATUS
--------- -------------------- ---------------- -------------------------------------------
ORCL MAXIMUM PERFORMANCE PRIMARY YES READ WRITE TO STANDBY
ORCL MAXIMUM PERFORMANCE PRIMARY YES READ WRITE TO STANDBY
显示TO STANDBY表示满足转换条件，如果显示SESSIONACTIVE表示还有活动的会话，需要关闭活动的会话再检查。
步骤4 停止RAC的其他节点，只保留一个节点
SQL> show parameter instance_name
NAME TYPE VALUE
------------------------------------ -----------------------------------------
instance_name string orcl2
SQL> shutdown immediate;
步骤5在RAC主数据库节点1上执行以下SQL语句开始进行切换操作：
SQL> alter database commit to switchover to physical standby with session shutdown;
如果步骤1执行的结果显示SESSIONACTIVE，在切换的时候可以指定with session shutdown 子句强制关闭活动的会话。
SQL>
Selectname,protection_mode,database_role,force_logging,open_mode,switchover_statusfrom gv$database;
NAMEPROTECTION_MODE DATABASE_ROLE FOR OPEN_MODS WITCHOVER_STATUS
----------------------------- ---------------- --- -------------------- --------------------
ORCL MAXIMUMPERFORMANCE PHYSICALSTANDBY YES READ WRITE RECOVERY NEEDED
步骤6执行以下SQL语句确认Standby数据库是否满足切换条件：
SQL> selectswitchover_status from v$database;
SWITCHOVER_STATUS
--------------------
TO PRIMARY
如果显示TO PRIMARY表示能将该Standby数据库转换为主数据库。
步骤7执行以下SQL语句切换Standby数据库为主数据库，并open：
SQL> alterdatabase commit to switchover to primary;
SQL> alterdatabase open;
步骤8启动切换后的RAC备库日志应用模式
在RAC备库执行以下SQL语句关闭RAC备库实例，启动RAC备库到日志应用模式：
[oracle@node1dbs]$ sqlplus / as sysdba
SQL> shutdownimmediate
[grid@node1 ~]$srvctl status database -d orcl
Instance orcl1is not running on node node1
Instance orcl2is not running on node node2
[grid@node1 ~]$srvctl start database -d orcl -o mount;
SQL> alterdatabase recover managed standby database using current logfile disconnect fromsession;
也可以打开数据库
SQL> alterdatabase recover managed standby database cancel;
[oracle@node1~]$ sqlplus / as sysdba
SQL> alterdatabase open;
[oracle@node2~]$ sqlplus / as sysdba
SQL> alter databaseopen;
SQL> alterdatabase recover managed standby database using current logfile disconnect fromsession;
上面的命令将数据库以只读的方式打开，并在后台启动日志实时应用。
步骤9执行以下SQL语句查看数据库转换后的角色：
SQL> selectdatabase_role from v$database;
DATABASE_ROLE
----------------
PHYSICAL STANDBY
以上输出表示主数据库已经转换成物理Standby数据库。
6 正常启动、关闭DG的流程
关闭
1） 执行如下SQL语句停止Standby数据库Redo日志的应用：
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
2） 关闭主数据库
3） 关闭Standby数据库
开启
1） 启动Standby数据库
2） 启动主数据库
3） 开启Standby数据库的Redo日志应用
SQL> alter database recover managed standby database using currentlogfile disconnect from session;
安装时遇到的一个小问题
在备库RMAN还原控制文件时报以下错误
RMAN> restore standby controlfile from'/u01/rman_backup/standby_0eq3re6d_1_1.ctl';
Starting restore at 08-APR-15
using target database control file instead ofrecovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=22 device type=DISK
channel ORA_DISK_1: restoring control file
RMAN-00571:===========================================================
RMAN-00569: =============== ERROR MESSAGE STACKFOLLOWS ===============
RMAN-00571:===========================================================
RMAN-03002: failure of restore command at04/08/2015 07:14:16
RMAN-10038: database session for channelORA_DISK_1 terminated unexpectedly
再次执行
RMAN> restore standby controlfile from'/u01/rman_backup/standby_0eq3re6d_1_1.ctl';
Starting restore at 08-APR-15
using channel ORA_DISK_1
RMAN-00571:===========================================================
RMAN-00569: =============== ERROR MESSAGESTACK FOLLOWS ===============
RMAN-00571:===========================================================
RMAN-03002: failure of restore command at04/08/2015 07:47:14
RMAN-00600: internal error, arguments[10019] [krmxcr] [] [] []
RMAN-00600: internal error, arguments [10019][krmxcr] [] [] []
解决的方法是：
将oracle用户添加到asmadmin组后恢复控制文件成功，原因可能是我的ASM磁盘属于asmadmin组，oracle不能对磁盘进行写入操作。
扫一扫
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
