重庆分公司
重庆分公司，新征程启航
为企业提供网站建设、域名注册、服务器等服务
RMAN配置DataGuardfromActivedatabasewithASM
一、 环境
主库：安装grid软件及创建磁盘组；安装数据库软件并创建数据库，
网站设计、成都网站制作的开发，更需要了解用户，从用户角度来建设网站，获得较好的用户体验。成都创新互联公司多年互联网经验，见的多，沟通容易、能帮助客户提出的运营建议。作为成都一家网络公司，打造的就是网站建设产品直销的概念。选择成都创新互联公司，不只是建站，我们把建站作为产品，不断的更新、完善，让每位来访用户感受到浩方产品的价值服务。
备库：仅安装grid软件并创建asm磁盘组，同时安装数据库软件即可。
主机名 | 数据库版本 | dbname | db_unique_name | ip地址 | 系统版本 |
server1(主) |
oracle11204 |
Jason
| jason | 192.168.1.250 |
rhel6.6_x86_64 |
server2(备) | jason2 | 192.168.1.252 |
二、 主库配置
1. 确定主数据库开启强制LOGGING模式
[oracle@server1 ~]$ sqlplus /nolog
SQL*Plus: Release 11.2.0.4.0 Production on Thu Jul 14 20:45:33 2016
Copyright (c) 1982, 2013, Oracle. All rights reserved.
SQL> conn /as sysdba
Connected.
SQL> ALTER DATABASE FORCE LOGGING;
Database altered.
SQL> select force_logging from v$database;
FOR
---
YES
2. 开启归档
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination +DATA
Oldest online log sequence 24
Next log sequence to archive 26
Current log sequence 26
3. 创建standby日志组
1).standby redo log的文件大小与primary 数据库online redo log 文件大小相同
2).standby redo log日志文件组的个数依照下面的原则进行计算
Standby redo log组数公式>=(每个instance日志组个数+1)*instance个数
例如在我的环境中，只有一个节点，这个节点有三组redo，所以
Standby redo log组数公式>=(3+1)*1 == 4
所以需要创建4组Standby redo log
3).每一日志组为了安全起见，可以包含多个成员文件。
查看主数据库的日志组个数与大小，创建standy日志组,大小不能小于在线日志大小。
SQL> select member from v$logfile;
MEMBER
------------------------------------------------------------------------
+DATA/jason/onlinelog/group_3.263.919631585
+DATA/jason/onlinelog/group_2.262.919631583
+DATA/jason/onlinelog/group_1.261.919631583
3 rows selected.
SQL> select group#,bytes/1024/1024 from v$log;
GROUP# BYTES/1024/1024
---------- ---------------
1 50
3 50
2 50
在主数据库创建standby日志组，位置与原日志组相同的路径。
SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 SIZE 50M;
Database altered.
SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 SIZE 50M;
Database altered.
SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 6 SIZE 50M;
Database altered.
SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 7 SIZE 50M;
Database altered.
SQL> select group#,status,type,member from v$logfile;
GROUP# STATUS TYPE MEMBER
---------- ------- ------- --------------------------
3 ONLINE +DATA/jason/onlinelog/group_3.263.919631585
2 ONLINE +DATA/jason/onlinelog/group_2.262.919631583
1 ONLINE +DATA/jason/onlinelog/group_1.261.919631583
4 STANDBY+DATA/jason/onlinelog/group_4.269.919707467
5 STANDBY+DATA/jason/onlinelog/group_5.270.919707475
6 STANDBY +DATA/jason/onlinelog/group_6.271.919707483
7 STANDBY+DATA/jason/onlinelog/group_7.272.919707491
7 rows selected.
SQL>
4. 主库参数文件配置
在主库上修改dataguard配置相关的各个参数，各参数的具体含义可以参考oracle在线文档。
alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(JASON,JASON2)' SCOPE=SPFILE;
alter system set STANDBY_FILE_MANAGEMENT='AUTO' SCOPE=SPFILE;
alter system set LOG_ARCHIVE_DEST_1='LOCATION=+DATAVALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=JASON' scope=spfile;
alter system set LOG_ARCHIVE_DEST_2='SERVICE=JASON2 ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)DB_UNIQUE_NAME=JASON2'scope=spfile;
alter system set LOG_ARCHIVE_DEST_STATE_1='ENABLE' scope=spfile;
alter system set LOG_ARCHIVE_DEST_STATE_2='ENABLE' scope=spfile;
alter system set FAL_SERVER='JASON2' scope=spfile;
主库的DB_UNIQUE_NAME不设置，默认与数据库名一致。
SQL> alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(JASON,JASON2)'SCOPE=SPFILE;
System altered.
SQL> alter system set STANDBY_FILE_MANAGEMENT='AUTO' SCOPE=SPFILE;
System altered.
SQL> alter system set LOG_ARCHIVE_DEST_1='LOCATION=+DATAVALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=JASON' scope=spfile;
System altered.
SQL> alter system set LOG_ARCHIVE_DEST_2='SERVICE=JASON2 ASYNCVALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=JASON2'scope=spfile;
System altered.
SQL> alter system set LOG_ARCHIVE_DEST_STATE_1='ENABLE' scope=spfile;
System altered.
SQL> alter system set LOG_ARCHIVE_DEST_STATE_2='ENABLE' scope=spfile;
System altered.
SQL> alter system set FAL_SERVER='JASON2' scope=spfile;
System altered.
SQL>
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 409194496 bytes
Fixed Size 2253744 bytes
Variable Size 310381648bytes
Database Buffers 92274688 bytes
Redo Buffers 4284416 bytes
Database mounted.
Database opened.
5. 配置监听及tnsname
grid用户创建监听，如下：
[grid@server1 ~]$ cd /u01/app/11.2.0/grid/network/admin/
[grid@server1 admin]$ cat listener.ora
# listener.ora Network Configuration File:/u01/app/11.2.0/grid/network/admin/listener.ora
# Generated by Oracle configuration tools.
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL =IPC)(KEY = EXTPROC1521))
(ADDRESS = (PROTOCOL =TCP)(HOST = server1)(PORT = 1521))
)
)
ADR_BASE_LISTENER = /u01/app/grid
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON # line added by Agent
oracle用户下tnsname.ora配置如下
[oracle@server1 ~]$ cd /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/
[oracle@server1 admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File:/u01/app/11.2.0/grid/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
JASON =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL =TCP)(HOST = 192.168.1.250)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = JASON)
)
)
JASON2 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL =TCP)(HOST = 192.168.1.252)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = JASON2)
)
)
[oracle@server1 admin]$ lsnrctl status
LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 12-AUG-201622:43:25
Copyright (c) 1991, 2013, Oracle. All rights reserved.
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNRfor Linux: Version 11.2.0.4.0 - Production
Start Date 12-AUG-2016 17:56:24
Uptime 0 days 4 hr. 47 min. 0 sec
Trace Level off
Security ON:Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/11.2.0/grid/network/admin/listener.ora
Listener Log File /u01/app/grid/diag/tnslsnr/server1/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=server1)(PORT=1521)))
Services Summary...
Service "+ASM" has 1 instance(s).
Instance "+ASM",status READY, has 1 handler(s) for this service...
Service "JASON" has 1 instance(s).
Instance "JASON",status READY, has 1 handler(s) for this service...
Service "JASONXDB" has 1 instance(s).
Instance "JASON",status READY, has 1 handler(s) for this service...
The command completed successfully
[oracle@server1 admin]$
6. 生成pfile文件，同步相应文件至备库
在主数据库生成pfile文件。
SQL> create pfile=’/home/oracle/pfile.ora’ from spfile;
File created.
同步密码认证文件至备机。
[oracle@jason1 dbs]$ pwd
/u01/app/oracle/product/11.2.0/dbhome_1/dbs
[oracle@jason1 dbs]$ scp initJASON.ora orapwJASON 192.168.1.252:/u01/app/oracle/product/11.2.0/dbhome_1/dbs/
The authenticity of host '192.168.1.100 (192.168.1.100)' can't beestablished.
RSA key fingerprint is 25:ca:65:90:d3:30:fa:68:ed:11:64:b2:0e:b0:39:a7.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '192.168.1.100' (RSA) to the list of knownhosts.
oracle@192.168.1.252's password:
initJASON.ora 100%1415 1.4KB/s 00:00
orapwJASON 100% 1536 1.5KB/s 00:00
[oracle@jason1 dbs]
三、 备库配置
7. 创建对应目录
备库上创建相关目录
[oracle@server2 oracle]$ mkdir -p /u01/app/oracle/admin/JASON2/adump
8. 备库参数文件配置
备库上修改初始参数文件，配置DG所需参数如下。
JASON.__db_cache_size=104857600
JASON.__java_pool_size=4194304
JASON.__large_pool_size=8388608
JASON.__pga_aggregate_target=180355072
JASON.__sga_target=230686720
JASON.__shared_io_pool_size=0
JASON.__shared_pool_size=104857600
JASON.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/JASON2/adump'
*.audit_trail='db'
*.compatible='11.2.0.4.0'
*.control_files='+DATA'
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_domain=''
*.db_name='JASON'
*.DB_UNIQUE_NAME='JASON2'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=JASONXDB)'
*.fal_server='JASON'
*.log_archive_config='DG_CONFIG=(JASON,JASON2)'
*.log_archive_dest_1='LOCATION=+DATA VALID_FOR=(ALL_LOGFILES,ALL_ROLES)DB_UNIQUE_NAME=JASON2'
*.log_archive_dest_2='SERVICE=JASON ASYNCVALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=JASON'
*.log_archive_dest_state_1='ENABLE'
*.log_archive_dest_state_2='ENABLE'
*.log_archive_format='%t_%s_%r.dbf'
*.memory_target=411041792
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.standby_file_management='AUTO'
*.undo_tablespace='UNDOTBS1'
9.配置监听
grid用户创建监听，备库监听必须配置为静态监听。如下：
[grid@server2 admin]$ cat listener.ora
# listener.ora Network Configuration File:/u01/app/11.2.0/grid/network/admin/listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = JASON2)
(ORACLE_HOME =/u01/app/oracle/product/11.2.0/dbhome_1)
(SID_NAME = JASON)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL =IPC)(KEY = EXTPROC1521))
)
(DESCRIPTION =
(ADDRESS = (PROTOCOL =TCP)(HOST = server2)(PORT = 1521))
)
)
ADR_BASE_LISTENER = /u01/app/grid
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER = ON
oracle用户下tnsname.ora配置如下
[oracle@server1 ~]$ cd/u01/app/oracle/product/11.2.0/dbhome_1/network/admin/
[oracle@server1 admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File:/u01/app/11.2.0/grid/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
JASON =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL =TCP)(HOST = 192.168.1.250)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = JASON)
)
)
JASON2 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL =TCP)(HOST = 192.168.1.252)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = JASON2)
)
)
10.创建备库
将备库启动到nomount状态，然后在备机连接主库进行duplicate操作。
[oracle@server2 dbs]$ rman target sys/system@JASON auxiliarysys/system@JASON2
Recovery Manager: Release 11.2.0.4.0 - Production on Fri Aug 12 18:35:212016
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: JASON (DBID=2143845850)
connected to auxiliary database: JASON (not mounted)
RMAN> duplicate target database for standby nofilenamecheck fromactive database;
Starting Duplicate Db at 12-AUG-16
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=23 device type=DISK
contents of Memory Script:
{
backup as copy reuse
targetfile '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwJASON' auxiliaryformat
'/u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwJASON' ;
}
executing Memory Script
Starting backup at 12-AUG-16
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=45 device type=DISK
Finished backup at 12-AUG-16
contents of Memory Script:
{
backup as copy currentcontrolfile for standby auxiliary format '+DATA/jason2/controlfile/current.256.919708533';
sql clone "create spfilefrom memory";
shutdown clone immediate;
startup clone nomount;
sql clone "alter systemset control_files =
''+DATA/jason2/controlfile/current.256.919708533'' comment=
''Set by RMAN''scope=spfile";
shutdown clone immediate;
startup clone nomount;
}
executing Memory Script
Starting backup at 12-AUG-16
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
copying standby control file
output filename=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/snapcf_JASON.ftag=TAG20160812T183533 RECID=1 STAMP=919708533
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 12-AUG-16
sql statement: create spfile from memory
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 409194496 bytes
Fixed Size 2253744 bytes
Variable Size 318770256 bytes
Database Buffers 83886080 bytes
Redo Buffers 4284416 bytes
sql statement: alter system set control_files = ''+DATA/jason2/controlfile/current.256.919708533'' comment= ''Set byRMAN'' scope=spfile
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 409194496 bytes
Fixed Size 2253744 bytes
Variable Size 318770256 bytes
Database Buffers 83886080 bytes
Redo Buffers 4284416 bytes
contents of Memory Script:
{
sql clone 'alter database mountstandby database';
}
executing Memory Script
sql statement: alter database mount standby database
contents of Memory Script:
{
set newname for clonetempfile 1 to new;
switch clone tempfile all;
set newname for clonedatafile 1 to new;
set newname for clonedatafile 2 to new;
set newname for clonedatafile 3 to new;
set newname for clonedatafile 4 to new;
backup as copy reuse
datafile 1 auxiliary format new
datafile 2 auxiliary format new
datafile 3 auxiliary format new
datafile 4 auxiliary format new
;
sql 'alter system archive logcurrent';
}
executing Memory Script
executing command: SET NEWNAME
renamed tempfile 1 to +DATA in control file
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting backup at 12-AUG-16
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001name=+DATA/jason/datafile/system.256.919631481
output file name=+DATA/jason2/datafile/system.257.919708567tag=TAG20160812T183605
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:35
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002name=+DATA/jason/datafile/sysaux.257.919631481
output file name=+DATA/jason2/datafile/sysaux.258.919708603tag=TAG20160812T183605
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003name=+DATA/jason/datafile/undotbs1.258.919631481
output file name=+DATA/jason2/datafile/undotbs1.259.919708627tag=TAG20160812T183605
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004name=+DATA/jason/datafile/users.259.919631483
output file name=+DATA/jason2/datafile/users.260.919708631tag=TAG20160812T183605
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 12-AUG-16
sql statement: alter system archive log current
contents of Memory Script:
{
switch clone datafile all;
}
executing Memory Script
datafile 1 switched to datafile copy
input datafile copy RECID=1 STAMP=919708632 filename=+DATA/jason2/datafile/system.257.919708567
datafile 2 switched to datafile copy
input datafile copy RECID=2 STAMP=919708632 filename=+DATA/jason2/datafile/sysaux.258.919708603
datafile 3 switched to datafile copy
input datafile copy RECID=3 STAMP=919708633 filename=+DATA/jason2/datafile/undotbs1.259.919708627
datafile 4 switched to datafile copy
input datafile copy RECID=4 STAMP=919708633 filename=+DATA/jason2/datafile/users.260.919708631
Finished Duplicate Db at 12-AUG-16
RMAN>
备注：创建备库后，数据库处于mount状态，数据打开时将会初始化临时表空间、在线日志、standby日志。
11.开启ADG
将备库置于active dataguard模式下，备库创建成功后默认为mount状态，需要手动打开。
[oracle@server2 dbs]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Fri Aug 12 18:51:54 2016
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bitProduction
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
SQL> alter database open;
Database altered.
SQL> alter database recover managed standby database using currentlogfile disconnect from session;
Database altered.
SQL> select open_mode,database_role,db_unique_name from v$database;
OPEN_MODE DATABASE_ROLE DB_UNIQUE_NAME
-------------------- ---------------- ------------------------------
READ ONLY WITH APPLY PHYSICAL STANDBY JASON2
SQL> select protection_mode,protection_level from v$database;
PROTECTION_MODE PROTECTION_LEVEL
-------------------- --------------------
MAXIMUM PERFORMANCE MAXIMUMPERFORMANCE
SQL>
SQL> select status from v$standby_log;
STATUS
----------
UNASSIGNED
ACTIVE
UNASSIGNED
UNASSIGNED
SQL> select group#,status,type,member from v$logfile;
GROUP# STATUS TYPE MEMBER
---------- ------- ------- --------------------------
3 ONLINE +DATA/jason2/onlinelog/group_3.263.919708637
2 ONLINE +DATA/jason2/onlinelog/group_2.262.919708637
1 ONLINE +DATA/jason2/onlinelog/group_1.261.919708633
4 STANDBY+DATA/jason2/onlinelog/group_4.264.919708637
5 STANDBY+DATA/jason2/onlinelog/group_5.265.919708639
6 STANDBY+DATA/jason2/onlinelog/group_6.266.919708639
7 STANDBY +DATA/jason2/onlinelog/group_7.267.919708641
7 rows selected.
SQL>
主库查看数据库状态
[oracle@server1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Fri Aug 12 18:34:16 2016
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bitProduction
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
SQL> select open_mode,database_role,db_unique_name from v$database;
OPEN_MODE DATABASE_ROLE DB_UNIQUE_NAME
-------------------- ---------------- ------------------------------
READ WRITE PRIMARY JASON
SQL> select protection_mode,protection_level from v$database;
PROTECTION_MODE PROTECTION_LEVEL
-------------------- --------------------
MAXIMUM PERFORMANCE MAXIMUMPERFORMANCE
SQL>
12.创建spfile文件
备库创建结束后，需要创建spfile文件。
修改pfile文件control_files参数，指定备库的控制文件，查看asm磁盘组获取控制文件名及位置。如下：
control_files='+DATA/jason2/controlfile/current.256.919708533'，
创建spfile.
SQL> create SPFILE='+DATA/JASON2/spfileJASON.ora'from pfile='/u01/app/oracle/product/11.2.0/dbhome_1/dbs/initJASON.ora';
File created.
创建pfile文件指向
[oracle@server2dbs]$ cat initJASON.ora
SPFILE='+DATA/JASON2/spfileJASON.ora'
[oracle@server2dbs]$
spfile文件创建成功后，备库重启将使用spfile文件启动数据库
使用ALTERDATABASE RECOVER MANAGED STANDBY DATABASE CANCEL取消日志恢复，关闭数据库，再次打开，再开启日志应用。
SQL> showparameter spfile
NAME TYPE VALUE
----------------------------------------------- ------------------------------
spfile string +DATA/jason2/spfilejason.ora
SQL>
四、 测试
13.主备库查看日志
主库切换日志
SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL> SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOGORDER BY SEQUENCE#;
SEQUENCE# APPLIED
---------- ---------
5 NO
6 NO
7 NO
8 NO
9 NO
10 NO
10 YES
11 NO
11 YES
12 NO
12 YES
SEQUENCE# APPLIED
---------- ---------
13 NO
13 YES
14 NO
14 YES
15 NO
15 NO
17 rows selected.
SQL>
备库查看日志
SQL> SELECTSEQUENCE#,APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
SEQUENCE# APPLIED
---------- ---------
10 YES
11 YES
12 YES
13 YES
14 YES
15IN-MEMORY
6 rows selected.
SQL>
14.switch_over测试
主库切换
SQL> SELECTSWITCHOVER_STATUS FROM V$DATABASE;
SWITCHOVER_STATUS
--------------------
TO STANDBY
SQL> ALTERDATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH SESSION SHUTDOWN;
Database altered.
SQL> startup
ORACLE instancestarted.
Total System GlobalArea 409194496 bytes
Fixed Size 2253744 bytes
Variable Size 322964560 bytes
DatabaseBuffers 79691776 bytes
Redo Buffers 4284416 bytes
Database mounted.
Database opened.
SQL> selectopen_mode,database_role,db_unique_name from v$database;
OPEN_MODE DATABASE_ROLE DB_UNIQUE_NAME
------------------------------------ ------------------------------
READ ONLY PHYSICAL STANDBY JASON
SQL> ALTERDATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROMSESSION;
Database altered.
SQL>
备库切换
SQL> SELECTSWITCHOVER_STATUS FROM V$DATABASE;
SWITCHOVER_STATUS
--------------------
TO PRIMARY
SQL> ALTERDATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN;
Database altered.
SQL> selectopen_mode,database_role,db_unique_name from v$database;
OPEN_MODE DATABASE_ROLE DB_UNIQUE_NAME
------------------------------------ ------------------------------
MOUNTED PRIMARY JASON2
SQL> alterdatabase open;
Database altered.
SQL> selectopen_mode,database_role,db_unique_name from v$database;
OPEN_MODE DATABASE_ROLE DB_UNIQUE_NAME
------------------------------------ ------------------------------
READ WRITE PRIMARY JASON2
SQL>
五、 报错处理
15.报错1
[oracle@server2 ~]$rman target sys/system@JASON auxiliarysys/system@jason2
Recovery Manager:Release 11.2.0.4.0 - Production on Wed Aug 10 07:37:33 2016
Copyright (c) 1982,2011, Oracle and/or its affiliates. Allrights reserved.
connected to target database:JASON (DBID=2143699214)
connected toauxiliary database: JASON (not mounted)
RMAN> duplicatetarget database for standby nofilenamecheck from active database;
Starting DuplicateDb at 10-AUG-16
using targetdatabase control file instead of recovery catalog
allocated channel:ORA_AUX_DISK_1
channelORA_AUX_DISK_1: SID=22 device type=DISK
contents of MemoryScript:
{
backup as copy reuse
targetfile '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwJASON' auxiliaryformat
'/u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwJASON' ;
}
executing MemoryScript
Starting backup at10-AUG-16
allocated channel:ORA_DISK_1
channel ORA_DISK_1:SID=51 device type=DISK
Finished backup at10-AUG-16
contents of MemoryScript:
{
backup as copy current controlfile forstandby auxiliary format '+DATA/jason2/controlfile/current.256.919708533';
}
executing MemoryScript
Starting backup at10-AUG-16
using channelORA_DISK_1
channel ORA_DISK_1:starting datafile copy
copying standby controlfile
RMAN-00571:===========================================================
RMAN-00569:=============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571:===========================================================
RMAN-03002: failureof Duplicate Db command at 08/10/2016 07:38:20
RMAN-05501: abortingduplication of target database
RMAN-03015: erroroccurred in stored script Memory Script
RMAN-03009: failureof backup command on ORA_DISK_1 channel at 08/10/2016 07:38:20
ORA-17628: Oracleerror 19505 returned by remote Oracle server
RMAN>
错误分析:该错误mos也给出了对应解决方法，由于主备库目录不一致未使用参数db_file_name_convert,LOG_FILE_NAME_CONVERT参数导致。本案例中主备库都是用+data磁盘组，同时使用OMF管理文件。因此不适用本案例。在创建数据库期间，警告日志报如下错误：
ORA-15025: could not open disk"/dev/asm-diskb"
ORA-27041: unable to open file
Linux-x86_64 Error: 13: Permissiondenied
Additional information: 9
Wed Aug 10 07:38:19 2016
SUCCESS: diskgroup DATA wasdismounted
ERROR: diskgroup DATA was notmounted
Errors in file/u01/app/oracle/diag/rdbms/jason2/JASON/trace/JASON_ora_3131.trc:
ORA-19505:failed to identify file "+DATA/jason2/controlfile/current.256.919708533"
ORA-17502:ksfdcre:3 Failed to create file +DATA/jason2/controlfile/current.256.919708533
ORA-15001: diskgroup"DATA" does not exist or is not mounted
ORA-15040: diskgroup is incomplete
备库磁盘组存在，且查看主库磁盘组与备库磁盘组权限一致，那么可能是其他原因导致。最终发现主库的/u01/app/oracle/product/11.2.0/dbhome_1/bin/oracle文件权限不一致。
主库
[root@server1 bin]# ll oracle
-rwsr-s--x 1 oracle asmadmin239626731 Aug 11 20:06 oracle
备库
[root@server2 bin]# ll oracle
-rwsr-s--x 1 oracle oinstall239626731 Aug 11 20:06 oracle
主备库唯一的区别在于备库是通过rman复制创建。测试发现在dbca创建数据库后将会修改该文件权限。以下为测试过程：
安装grid,数据库软件时，未创建数据库时
[root@server1 dbhome_1]# cd bin/
[root@server1 bin]# ll oracle
-rwsr-s--x 1 oracle oinstall239626731 Aug 11 20:06 oracle
[root@server1 bin]#
dbca创建数据库之后
[root@server1 trace]# cd/u01/app/oracle/product/11.2.0/dbhome_1/bin/
[root@server1 bin]# ll oracle
-rwsr-s--x 1 oracle asmadmin239626731 Aug 11 20:06 oracle
[root@server1 bin]#
因此修改备库该文件权限,问题解决。
[root@server2 bin]#chown oracle:asmadminoracle
[root@server2 bin]#chmod 6751 oracle
[root@server2 bin]# ll oracle
-rwsr-s--x 1 oracle asmadmin239626731 Aug 11 20:06 oracle
16.报错2
备用数据库启动时报如下错误
ERROR: failed to establish dependency between database JASON2 anddiskgroup resource ora.DATA.dg
查看噶日志信息是因为备库未注册入CRS启动时报错。同时备机重启仅ASM实例与crs启动,CRS无法启动数据库。因此注册备库至CRS中。
以oracle用户执行
[oracle@server2 dbs]$ srvctl add database -d JASON2 -o/u01/app/oracle/product/11.2.0/dbhome_1 -p +DATA/JASON2/spfileJASON.ora -i jason -r PHYSICAL_STANDBY -n jason
[oracle@server2 dbs]$ srvctl modify database -d JASON2 -a 'data'
[oracle@server2 dbs]$ srvctl config database -d jason2 -a
Database unique name: JASON2
Database name: jason
Oracle home: /u01/app/oracle/product/11.2.0/dbhome_1
Oracle user: oracle
Spfile: +DATA/JASON2/spfileJASON.ora
Domain:
Start options: open
Stop options: immediate
Database role: PHYSICAL_STANDBY
Management policy: AUTOMATIC
Database instance: jason
Disk Groups: DATA
Services:
Database is enabled
[oracle@server2 dbs]$
文章题目：RMAN配置DataGuardfromActivedatabasewithASM
文章位置：http://cqcxhl.com/article/gchhsh.html