Oracle 11g RAC 到 单实例 DG 环境搭建
2017-05-29 11:2645300原创Oracle 11g 本文链接：https://www.cndba.cn/leo1990/article/19391 环境说明
Primary Database 是一个两节点的RAC,存储采用ASM 方式，具体如下图：
| RAC Primary | rac1 | rac2 |
| Public IP | 192.168.1.60/24 | 192.168.1.62/24 |
| Private IP | 192.168.56.80/24 | 192.168.56.82/24 |
| Vritual IP | 192.168.1.61/24 | 192.168.1.63/24 |
| San IP | 192.168.1.125 | |
| Instance | leo1 | leo2 |
| DB_NAME | leo | |
| Data、Control File、Redo File | ASM | |
Standby Database (Single Instance) 环境介绍
| Single instance Standby | 说明 |
| IP | 192.168.1.65/24 |
| Oracle | 单实例 |
| Instance | orcl |
| DB_NAME | leo |
| Data | /u01/app/oracle/oradata/orcl/data tempfile |
| Control File | /u01/app/oracle/oradata/orcl/control01.ctl |
| /u01/app/oracle/fast_recovery_area/orcl | |
| Redo FileRedo File | /u01/app/oracle/oradata/orcl/redo |
2 主库设置为 force logging 模式
rac 节点1 执行
SQL> alter database force logging;
Database altered.
SQL> select force_logging from v$database;
FOR
---
YESSQL> archive log list
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 30
Current log sequence 31
切换归档，将所有节点都必须处于 mount 状态。 在其中一个节点修改模式，然后在其他节点正常启动即可。
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 1269366784 bytes
Fixed Size 2252864 bytes
Variable Size 872419264 bytes
Database Buffers 385875968 bytes
Redo Buffers 8818688 bytes
Database mounted.
SQL> alter database archivelog;
Database altered.
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 30
Next log sequence to archive 31
Current log sequence 31
SQL> select name , open_mode, log_mode,force_logging from gv$database;
NAME OPEN_MODE LOG_MODE FOR
--------- -------------------- ------------ ---
LEO READ WRITE ARCHIVELOG YES
LEO READ WRITE ARCHIVELOG YES
SQL> show parameter db_recover
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string +DATA
db_recovery_file_dest_size big integer 4407M
我 2 个节点的归档都指向了+DATA 这个磁盘组。 也可以指向其他的磁盘组或者本地的位置，如：
SQL> alter system set log_archive_dest_1='location=/u01/leo1arch' sid='leo1';
SQL> alter system set log_archive_dest_1='location=/u01/leo2arch' sid='leo2';RAC 每个 Redo Thread 都需要创建对应的 Standby Redo Log。 创建原则和单实例一样，包括日志 文件大小相等，日志组数量要多 1 组。
主库
SQL> set lines 120
SQL> col member for a50
SQL> select a.thread#,a.group#,a.bytes/1024/1024,b.member from v$log a,v$logfile b where a.group#=b.group#;
THREAD# GROUP# A.BYTES/1024/1024 MEMBER
---------- ---------- ----------------- --------------------------------------------------
1 2 50 +DATA/leo/onlinelog/group_2.277.943664411
1 2 50 +DATA/leo/onlinelog/group_2.278.943664413
1 1 50 +DATA/leo/onlinelog/group_1.275.943664407
1 1 50 +DATA/leo/onlinelog/group_1.276.943664409
2 3 50 +DATA/leo/onlinelog/group_3.281.943668673
2 3 50 +DATA/leo/onlinelog/group_3.282.943668675
2 4 50 +DATA/leo/onlinelog/group_4.283.943668677
2 4 50 +DATA/leo/onlinelog/group_4.284.943668679
8 rows selected.
--主库添加 standby redo log：
SQL> alter database add standby logfile thread 1 group 10 ('+DATA') size 50m;
Database altered.
SQL> alter database add standby logfile thread 1 group 11 ('+DATA') size 50m;
Database altered.
SQL> alter database add standby logfile thread 1 group 12 ('+DATA') size 50m;
Database altered.
SQL> alter database add standby logfile thread 1 group 13 ('+DATA') size 50m;
Database altered.
SQL> alter database add standby logfile thread 1 group 14 ('+DATA') size 50m;
Database altered.
SQL> alter database add standby logfile thread 2 group 15 ('+DATA') size 50m;
Database altered.
SQL> alter database add standby logfile thread 2 group 16 ('+DATA') size 50m;
Database altered.
SQL> alter database add standby logfile thread 2 group 17 ('+DATA') size 50m;
Database altered.
SQL> alter database add standby logfile thread 2 group 18 ('+DATA') size 50m;
Database altered.
SQL> alter database add standby logfile thread 2 group 19 ('+DATA') size 50m;
Database altered.
--验证：
SQL> select group#,type,member from v$logfile order by 2;
GROUP# TYPE MEMBER
---------- ------- --------------------------------------------------
2 ONLINE +DATA/leo/onlinelog/group_2.277.943664411
4 ONLINE +DATA/leo/onlinelog/group_4.284.943668679
4 ONLINE +DATA/leo/onlinelog/group_4.283.943668677
3 ONLINE +DATA/leo/onlinelog/group_3.282.943668675
3 ONLINE +DATA/leo/onlinelog/group_3.281.943668673
1 ONLINE +DATA/leo/onlinelog/group_1.276.943664409
1 ONLINE +DATA/leo/onlinelog/group_1.275.943664407
2 ONLINE +DATA/leo/onlinelog/group_2.278.943664413
18 STANDBY +DATA/leo/onlinelog/group_18.330.945089519
17 STANDBY +DATA/leo/onlinelog/group_17.329.945089515
16 STANDBY +DATA/leo/onlinelog/group_16.328.945089509
GROUP# TYPE MEMBER
---------- ------- --------------------------------------------------
15 STANDBY +DATA/leo/onlinelog/group_15.327.945089505
14 STANDBY +DATA/leo/onlinelog/group_14.294.944422059
13 STANDBY +DATA/leo/onlinelog/group_13.293.944422047
12 STANDBY +DATA/leo/onlinelog/group_12.292.944422039
11 STANDBY +DATA/leo/onlinelog/group_11.291.944422031
19 STANDBY +DATA/leo/onlinelog/group_19.331.945089523
10 STANDBY +DATA/leo/onlinelog/group_10.290.944422017
18 rows selected.用 net manager 工具，在备库创建一个监听。 也可以手动的修改 listener.ora 文件。
--对于 RAC 环境:
在 grid 用户的 listener.ora 文件中加入如下内容：
[grid@rac1 admin]$ cat listener.ora
# listener.ora Network Configuration File: /u01/app/11.2.0/grid/network/admin/listener.ora
# Generated by Oracle configuration tools.
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN1 = ON
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = leo)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
(SID_NAME = leo1)
)
)
LISTENER =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = LISTENER))
)
ADR_BASE_LISTENER = /u01/app/grid
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER = ON
LISTENER_SCAN1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = LISTENER_SCAN1))
)
ADR_BASE_LISTENER_SCAN1 = /u01/app/grid
节点 2，对应修改即可。
[grid@rac2 admin]$ cat listener.ora
# listener.ora Network Configuration File: /u01/app/11.2.0/grid/network/admin/listener.ora
# Generated by Oracle configuration tools.
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN1 = ON
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = leo)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
(SID_NAME = leo2)
)
)
LISTENER =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = LISTENER))
)
ADR_BASE_LISTENER = /u01/app/grid
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER = ON
LISTENER_SCAN1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = LISTENER_SCAN1))
)
ADR_BASE_LISTENER_SCAN1 = /u01/app/grid
--这里写的 Oracle 用户的 ORACLE_HOME，否则连接时会报错:
ORA-01031: insufficient privileges
然后重启监听。
注意在 oracle 11gR2 的 RAC 环境下，监听是在 grid 用户下配置的。所以这里可以用 grid 用户连接，
并修 改。 最后重启监听。
对于单实例，直接在 listener.ora 里添加：
[oracle@localhost admin]$ cat listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = orcl)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
(SID_NAME = orcl)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
)
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
ADR_BASE_LISTENER = /u01/app/oracle节点1，节点2，单实例 的 tnsnames.ora 文件是一致的，添加以下内容：
leo=
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.61)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.63)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME =leo)
)
)
orcl_st=
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.65)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
配置完成后，使用 tnsping 命令效验：
[oracle@rac1 admin]$ tnsping orcl_st
TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 29-MAY-2017 01:59:09
Copyright (c) 1997, 2013, Oracle. All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.65)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl)))
OK (0 msec)
[oracle@localhost dbs]$ tnsping leo
TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 29-MAY-2017 01:59:38
Copyright (c) 1997, 2013, Oracle. All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.61)(PORT = 1521)) (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.63)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME =leo)))
OK (0 msec)--FRA目录
[oracle@rac2 ~]$ mkdir -p /u01/app/oracle/fast_recovery_area/orcl
--DATAFILE
[oracle@rac2 ~]$ mkdir -p /u01/app/oracle/oradata/orcl
[oracle@rac2 ~]$ mkdir -p /u01/app/oracle/admin/orcl/adump
[oracle@rac2 ~]$ mkdir -p /u01/app/oracle/admin/orcl/data
[oracle@rac2 ~]$ mkdir -p /u01/app/oracle/admin/orcl/redo
[oracle@rac2 ~]$ mkdir -p /u01/app/oracle/admin/orcl/tempfile[oracle@localhost dbs]$ pwd
/u01/app/oracle/product/11.2.0/db_1/dbs
[oracle@localhost dbs]$ orapwd file=$ORACLE_HOME/dbs/orapwleo1 password=oracle
[oracle@localhost dbs]$ orapwd file=$ORACLE_HOME/dbs/orapwleo2 password=oracle
[oracle@localhost dbs]$ orapwd file=$ORACLE_HOME/dbs/orapworcl password=oracle
或者把节点的口令文件copy 到备库(在哪个节点执行操作就在把那个节点的口令文件copy过去)
[oracle@rac1 dbs]$ scp orapwleo1 192.168.1.65:/u01/app/oracle/product/11.2.0/db_1/dbs/orapworcl --主库参数
db_file_name_convert 和 log_file_name_convert 仅当数据库被 standby 时才会生效，
这里配置，是 为切换做准备。 log_file_name_convert 指的是 online redo log。
在 Oracle 11g 已经废除了 fal_client 参数。
RAC 的 spfile 是放在共享设备上的，所以如果想创建，就需要先创建一份 pfile 到本地，在修改，
如果不想这么折腾，就直接使用 SQL 语句修改：
1）使用 ASM 作为存储时，datafile 和 tempfile 是分别放在两个目录下的，所以在Standby
上也单独创建一个tempdata 目录。并在db_file_name_convert 中作相应的设置。
2）在使用ASM 的RAC中，注意不要改变db_unique_name 的参数值；因为ASM 存放文件的规则，是按照
+diskgroup_name/data_unique_name/file/tag_name.file_member.incarnation 这样一个规则存放的，
但是第二项database_unique_name 并不是db_name;如果改变了db_unique_name,则之后创建的数据文件
会放在新的目录下，会导致db_file_name_convert 的失效，这一点需要特别注意。
3）如果RAC中使用db_create_online_log_dest_n 系列参数，要相应调整stangby 上的log_file_name_convert 参数。
alter system set db_unique_name='leo' scope=spfile sid='*';
alter system set log_archive_config='dg_config=(leo,orcl_st)' scope=spfile sid='*';
alter system set log_archive_dest_1='location=+FRA valid_for=(all_logfiles,all_roles) db_unique_name=leo' scope=spfile sid='*';
alter system set log_archive_dest_2='service=orcl_st valid_for=(online_logfiles,primary_role) db_unique_name=orcl_st' scope=spfile sid='*';
alter system set log_archive_dest_state_1=enable scope=spfile sid='*';
alter system set log_archive_dest_state_2=enable scope=spfile sid='*';
alter system set standby_file_management='auto' scope=spfile sid='*';
alter system set fal_server='orcl_st' scope=spfile sid='*';
alter system set db_file_name_convert='/u01/app/oracle/oradata/orcl/data','+DATA/leo/datafile','/u01/app/oracle/oradata/orcl/tempfile','+DATA/leo/tempfile' scope=spfile sid='*';
alter system set log_file_name_convert='/u01/app/oracle/oradata/orcl/redo','+DATA/leo/onlinelog' scope=spfile sid='*';
alter system set log_archive_format='%t_%s_%r.arch' scope=spfile sid='*';
--备库参数
--在主库创建pfile 文件并scp 到备库修改
主要指定一些pfile的路径，不要直接create pfile from spfile
create pfile='/u01/app/oracle/product/11.2.0/db_1/dbs/tmp.ora' from spfile;
[oracle@rac1 dbs]$ scp tmp.ora 192.168.1.65:/u01/app/oracle/product/11.2.0/db_1/dbs/initorcl.ora
[oracle@localhost dbs]$ pwd
/u01/app/oracle/product/11.2.0/db_1/dbs
[oracle@localhost dbs]$ cat initorcl.ora
orcl.__db_cache_size=436207616
orcl.__java_pool_size=16777216
orcl.__large_pool_size=33554432
orcl.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
orcl.__pga_aggregate_target=520093696
orcl.__sga_target=754974720
orcl.__shared_io_pool_size=0
orcl.__shared_pool_size=251658240
orcl.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/orcl/adump'
*.audit_trail='db'
*.compatible='11.2.0.4.0'
*.control_files='/u01/app/oracle/oradata/orcl/control01.ctl','/u01/app/oracle/fast_recovery_area/orcl/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='leo'
*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'
*.db_recovery_file_dest_size=4621074432
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=leoXDB)'
*.log_archive_dest_1='location=/u01/archive/'
*.memory_target=1048576000
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='exclusive'
*.undo_tablespace='UNDOTBS1'
--添加以下内容，对应修改上面的参数
*.service_names='orcl_st'
*.db_unique_name='orcl_st'
*.log_archive_config='dg_config=(leo,orcl_st)'
*.log_archive_dest_1='location=/u01/archive valid_for=(all_logfiles,all_roles) db_unique_name=orcl_st'
*.log_archive_dest_2='service=leo valid_for=(online_logfiles,primary_role) db_unique_name=leo'
*.log_archive_dest_state_1=enable
*.log_archive_dest_state_2=enable
*.log_archive_format=%t_%s_%r.arc
*.standby_file_management='auto'
*.fal_server='leo'
*.log_file_name_convert='+DATA/leo/onlinelog','/u01/app/oracle/oradata/orcl/redo'
*.db_file_name_convert='+DATA/leo/datafile','/u01/app/oracle/oradata/orcl/data','+DATA/leo/tempfile','/u01/app/oracle/oradata/orcl/tempfile'[oracle@localhost ~]$ lsnrctl start
LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 28-MAY-2017 10:31:13
Copyright (c) 1991, 2013, Oracle. All rights reserved.
Starting /u01/app/oracle/product/11.2.0/db_1/bin/tnslsnr: please wait...
TNSLSNR for Linux: Version 11.2.0.4.0 - Production
System parameter file is /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/localhost/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date 28-MAY-2017 10:31:15
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/localhost/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "orcl" has 1 instance(s).
Instance "orcl", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
[oracle@localhost dbs]$ sqlplus /nolog
SQL*Plus: Release 11.2.0.4.0 Production on Sun May 28 22:36:14 2017
Copyright (c) 1982, 2013, Oracle. All rights reserved.
SQL> conn /as sysdba
Connected.
SQL> create spfile from pfile;
File created.
SQL> startup nomount
ORACLE instance started.
Total System Global Area 1043886080 bytes
Fixed Size 2259840 bytes
Variable Size 654312576 bytes
Database Buffers 381681664 bytes
Redo Buffers 5632000 bytes[oracle@rac1 ~]$ rman target sys/oracle@leo auxiliary sys/oracle@orcl_st
Recovery Manager: Release 11.2.0.4.0 - Production on Sun May 28 22:42:26 2017
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: LEO (DBID=1717931218)
connected to auxiliary database: LEO (not mounted)
RMAN> duplicate target database for standby from active database dorecover;
Starting Duplicate Db at 2017:05:28 22:42:36
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=19 device type=DISK
contents of Memory Script:
{
backup as copy reuse
targetfile '/u01/app/oracle/product/11.2.0/db_1/dbs/orapwleo2' auxiliary format
'/u01/app/oracle/product/11.2.0/db_1/dbs/orapworcl' ;
}
executing Memory Script
Starting backup at 2017:05:28 22:42:37
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=53 instance=leo2 device type=DISK
Finished backup at 2017:05:28 22:42:38
contents of Memory Script:
{
backup as copy current controlfile for standby auxiliary format '/u01/app/oracle/oradata/orcl/control01.ctl';
restore clone controlfile to '/u01/app/oracle/fast_recovery_area/orcl/control02.ctl' from
'/u01/app/oracle/oradata/orcl/control01.ctl';
}
executing Memory Script
Starting backup at 2017:05:28 22:42:38
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
copying standby control file
output file name=/u01/app/oracle/product/11.2.0/db_1/dbs/snapcf_leo2.f tag=TAG20170528T224238 RECID=12 STAMP=945211363
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:04
Finished backup at 2017:05:28 22:42:45
Starting restore at 2017:05:28 22:42:45
using channel ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: copied control file copy
Finished restore at 2017:05:28 22:42:46
contents of Memory Script:
{
sql clone 'alter database mount standby database';
}
executing Memory Script
sql statement: alter database mount standby database
contents of Memory Script:
{
set newname for tempfile 1 to
"/u01/app/oracle/oradata/orcl/tempfile/temp.279.943664427";
switch clone tempfile all;
set newname for datafile 1 to
"/u01/app/oracle/oradata/orcl/data/system.269.943664299";
set newname for datafile 2 to
"/u01/app/oracle/oradata/orcl/data/sysaux.270.943664299";
set newname for datafile 3 to
"/u01/app/oracle/oradata/orcl/data/undotbs1.271.943664301";
set newname for datafile 4 to
"/u01/app/oracle/oradata/orcl/data/users.272.943664301";
set newname for datafile 5 to
"/u01/app/oracle/oradata/orcl/data/undotbs2.280.943665551";
backup as copy reuse
datafile 1 auxiliary format
"/u01/app/oracle/oradata/orcl/data/system.269.943664299" datafile
2 auxiliary format
"/u01/app/oracle/oradata/orcl/data/sysaux.270.943664299" datafile
3 auxiliary format
"/u01/app/oracle/oradata/orcl/data/undotbs1.271.943664301" datafile
4 auxiliary format
"/u01/app/oracle/oradata/orcl/data/users.272.943664301" datafile
5 auxiliary format
"/u01/app/oracle/oradata/orcl/data/undotbs2.280.943665551" ;
sql 'alter system archive log current';
}
executing Memory Script
executing command: SET NEWNAME
renamed tempfile 1 to /u01/app/oracle/oradata/orcl/tempfile/temp.279.943664427 in control file
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting backup at 2017:05:28 22:42:53
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=+DATA/leo/datafile/system.269.943664299
output file name=/u01/app/oracle/oradata/orcl/data/system.269.943664299 tag=TAG20170528T224253
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:46
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=+DATA/leo/datafile/sysaux.270.943664299
output file name=/u01/app/oracle/oradata/orcl/data/sysaux.270.943664299 tag=TAG20170528T224253
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:45
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=+DATA/leo/datafile/undotbs1.271.943664301
output file name=/u01/app/oracle/oradata/orcl/data/undotbs1.271.943664301 tag=TAG20170528T224253
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:08
channel ORA_DISK_1: starting datafile copy
input datafile file number=00005 name=+DATA/leo/datafile/undotbs2.280.943665551
output file name=/u01/app/oracle/oradata/orcl/data/undotbs2.280.943665551 tag=TAG20170528T224253
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=+DATA/leo/datafile/users.272.943664301
output file name=/u01/app/oracle/oradata/orcl/data/users.272.943664301 tag=TAG20170528T224253
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 2017:05:28 22:44:36
sql statement: alter system archive log current
contents of Memory Script:
{
backup as copy reuse
archivelog like "+FRA/leo_pd/archivelog/2017_05_28/thread_2_seq_56.304.945211383" auxiliary format
"/u01/archive/2_56_943664406.arc" archivelog like
"+FRA/leo_pd/archivelog/2017_05_28/thread_1_seq_72.302.945199597" auxiliary format
"/u01/archive/1_72_943664406.arc" archivelog like
"+FRA/leo_pd/archivelog/2017_05_28/thread_2_seq_57.306.945211479" auxiliary format
"/u01/archive/2_57_943664406.arc" archivelog like
"+FRA/leo_pd/archivelog/2017_05_28/thread_1_seq_73.303.945211381" auxiliary format
"/u01/archive/1_73_943664406.arc" archivelog like
"+FRA/leo_pd/archivelog/2017_05_28/thread_1_seq_74.305.945211477" auxiliary format
"/u01/archive/1_74_943664406.arc" ;
catalog clone archivelog "/u01/archive/2_56_943664406.arc";
catalog clone archivelog "/u01/archive/1_72_943664406.arc";
catalog clone archivelog "/u01/archive/2_57_943664406.arc";
catalog clone archivelog "/u01/archive/1_73_943664406.arc";
catalog clone archivelog "/u01/archive/1_74_943664406.arc";
switch clone datafile all;
}
executing Memory Script
Starting backup at 2017:05:28 22:44:39
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log copy
input archived log thread=2 sequence=56 RECID=98 STAMP=945211394
output file name=/u01/archive/2_56_943664406.arc RECID=0 STAMP=0
channel ORA_DISK_1: archived log copy complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting archived log copy
input archived log thread=1 sequence=72 RECID=96 STAMP=945199602
output file name=/u01/archive/1_72_943664406.arc RECID=0 STAMP=0
channel ORA_DISK_1: archived log copy complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting archived log copy
input archived log thread=2 sequence=57 RECID=100 STAMP=945211479
output file name=/u01/archive/2_57_943664406.arc RECID=0 STAMP=0
channel ORA_DISK_1: archived log copy complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting archived log copy
input archived log thread=1 sequence=73 RECID=97 STAMP=945211393
output file name=/u01/archive/1_73_943664406.arc RECID=0 STAMP=0
channel ORA_DISK_1: archived log copy complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting archived log copy
input archived log thread=1 sequence=74 RECID=99 STAMP=945211477
output file name=/u01/archive/1_74_943664406.arc RECID=0 STAMP=0
channel ORA_DISK_1: archived log copy complete, elapsed time: 00:00:00
Finished backup at 2017:05:28 22:44:51
cataloged archived log
archived log file name=/u01/archive/2_56_943664406.arc RECID=1 STAMP=945211492
cataloged archived log
archived log file name=/u01/archive/1_72_943664406.arc RECID=2 STAMP=945211492
cataloged archived log
archived log file name=/u01/archive/2_57_943664406.arc RECID=3 STAMP=945211493
cataloged archived log
archived log file name=/u01/archive/1_73_943664406.arc RECID=4 STAMP=945211493
cataloged archived log
archived log file name=/u01/archive/1_74_943664406.arc RECID=5 STAMP=945211493
datafile 1 switched to datafile copy
input datafile copy RECID=12 STAMP=945211493 file name=/u01/app/oracle/oradata/orcl/data/system.269.943664299
datafile 2 switched to datafile copy
input datafile copy RECID=13 STAMP=945211493 file name=/u01/app/oracle/oradata/orcl/data/sysaux.270.943664299
datafile 3 switched to datafile copy
input datafile copy RECID=14 STAMP=945211493 file name=/u01/app/oracle/oradata/orcl/data/undotbs1.271.943664301
datafile 4 switched to datafile copy
input datafile copy RECID=15 STAMP=945211493 file name=/u01/app/oracle/oradata/orcl/data/users.272.943664301
datafile 5 switched to datafile copy
input datafile copy RECID=16 STAMP=945211493 file name=/u01/app/oracle/oradata/orcl/data/undotbs2.280.943665551
contents of Memory Script:
{
set until scn 2749620;
recover
standby
clone database
delete archivelog
;
}
executing Memory Script
executing command: SET until clause
Starting recover at 2017:05:28 22:44:53
using channel ORA_AUX_DISK_1
starting media recovery
archived log for thread 1 with sequence 73 is already on disk as file /u01/archive/1_73_943664406.arc
archived log for thread 1 with sequence 74 is already on disk as file /u01/archive/1_74_943664406.arc
archived log for thread 2 with sequence 56 is already on disk as file /u01/archive/2_56_943664406.arc
archived log for thread 2 with sequence 57 is already on disk as file /u01/archive/2_57_943664406.arc
archived log file name=/u01/archive/1_73_943664406.arc thread=1 sequence=73
archived log file name=/u01/archive/2_56_943664406.arc thread=2 sequence=56
archived log file name=/u01/archive/2_57_943664406.arc thread=2 sequence=57
archived log file name=/u01/archive/1_74_943664406.arc thread=1 sequence=74
media recovery complete, elapsed time: 00:00:00
Finished recover at 2017:05:28 22:44:56
Finished Duplicate Db at 2017:05:28 22:45:14--完成 duplicate 之后，备库就是 mount 状态：
SQL> select NAME,open_mode from v$database;
NAME OPEN_MODE
--------- --------------------
LEO MOUNTED
SQL> alter database open;
Database altered.
SQL> select open_mode from v$database;
OPEN_MODE
--------------------
READ ONLYSQL> alter database recover managed standby database disconnect from session;
Database altered.
SQL> select open_mode from v$database;
OPEN_MODE
--------------------
READ ONLY WITH APPLY节点1 执行
SQL> create table leo2 as select * from dba_users;
Table created.
SQL> alter system switch logfile;
System altered.
节点2 执行
SQL> create table nancy as select * from dba_users;
Table created.
SQL> alter system switch logfile;
System altered.
--备库查询
SQL> select count(*) from leo2;
COUNT(*)
----------
31
SQL> select count(*) from nancy;
COUNT(*)
----------
31
SQL> select THREAD#,sequence#,applied from v$archived_log order by 1,2;
THREAD# SEQUENCE# APPLIED
---------- ---------- ---------
1 72 NO
1 73 YES
1 74 YES
2 56 YES
2 57 NO
版权声明：本文为博主原创文章，未经博主允许不得转载。