分享程序员开发的那些事...
更新时间：2021-10-27 08:54:36
【DATAGUARD】 基于同一个主机建立物理备库和逻辑备库（一）
之前发布过一步一步搭建 oracle 11gR2 rac + dg，这里的dg为物理dg，但是实际自己使用过程中发现需要开3个虚拟机，机器特卡，所以决定在同一台机器上再搭建一台物理和逻辑dg。
一步一步搭建 oracle 11gR2 rac + dg 之前传（一） http://blog.itpub.net/26736162/viewspace-1290405/
一步一步搭建oracle 11gR2 rac+dg之环境准备(二) http://blog.itpub.net/26736162/viewspace-1290416/
一步一步搭建oracle 11gR2 rac+dg之共享磁盘设置(三) http://blog.itpub.net/26736162/viewspace-1291144/
一步一步搭建 oracle 11gR2 rac+dg之grid安装(四) http://blog.itpub.net/26736162/viewspace-1297101/
一步一步搭建oracle 11gR2 rac+dg之database安装(五) http://blog.itpub.net/26736162/viewspace-1297113/
一步一步搭建11gR2 rac+dg之安装rac出现问题解决(六) http://blog.itpub.net/26736162/viewspace-1297128/
一步一步搭建11gR2 rac+dg之DG 机器配置(七) http://blog.itpub.net/26736162/viewspace-1298733/
一步一步搭建11gR2 rac+dg之配置单实例的DG(八) http://blog.itpub.net/26736162/viewspace-1298735/
一步一步搭建11gR2 rac+dg之DG SWITCHOVER功能(九) http://blog.itpub.net/26736162/viewspace-1328050/
本篇blog结构图：
创建物理备库的方法很多，对于Oracle 11g而言，可以直接从active database来创建，也可以基于10g 的RMAN使用duplicate方式来创建。
--演示环境
[root@rhel6_lhr ~]# su - oracle
[oracle@rhel6_lhr ~]$ cat /etc/issue
Red Hat Enterprise Linux Server release 6.5 (Santiago)
Kernel \r on an \m
[oracle@rhel6_lhr ~]$ sqlplus -v
SQL*Plus: Release 11.2.0.3.0 Production
[oracle@rhel6_lhr ~]$
--主库：ora11g
--备库: ora11gdg
--配置物理standby，使用***性能模式
--主库：配置归档且force logging
[oracle@rhel6_lhr oradata]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Wed Mar 4 10:20:43 2015
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, OLAP, Data Mining and Real Application Testing options
SQL> create user lhr identified by lhr;
User created.
SQL> grant dba to lhr;
Grant succeeded.
SQL> create table lhr.test as select * from dba_tables;
Table created.
SQL> select count(1) from lhr.test;
COUNT(1)
----------
2834
SQL> alter database force logging;
Database altered.
SQL> set line 9999
SQL> select name , open_mode, log_mode,force_logging,DATABASE_ROLE,switchover_status from v$database;
NAME OPEN_MODE LOG_MODE FOR DATABASE_ROLE SWITCHOVER_STATUS
--------- ------------- ------------ --- ---------------- --------------------
LILOVE READ WRITE ARCHIVELOG YES PRIMARY NOT ALLOWED
SQL>
--为主库添加standby redo log，简要描述一下standby redo log的作用
--实际上就是与主库接收到的重做日志相对应，也就是说备库调用RFS进程将从主库接收到的重做日志按顺序写入到standby logfile
--在主库创建standby logfile是便于发生角色转换后备用
--sandby redo log创建原则：
--a)、确保standby redo log的大小与主库online redo log的大小一致
--b)、如主库为单实例数据库：standby redo log组数=主库日志组总数+1
--c)、如果主库是RAC数据库：standby redo log组数=(每线程的日志组数+1)*最大线程数
--d)、不建议复用standby redo log，避免增加额外的I/O以及延缓重做传输
SQL> select * from v$standby_log;
no rows selected
SQL> alter database add standby logfile group 4 ('/u01/app/oracle/oradata/ora11g/standby_redo/standby_redo01.log') size 50m;
Database altered.
SQL> alter database add standby logfile group 5 ('/u01/app/oracle/oradata/ora11g/standby_redo/standby_redo02.log') size 50m;
Database altered.
SQL> alter database add standby logfile group 6 ('/u01/app/oracle/oradata/ora11g/standby_redo/standby_redo03.log') size 50m;
Database altered.
SQL> alter database add standby logfile group 7 ('/u01/app/oracle/oradata/ora11g/standby_redo/standby_redo04.log') size 50m;
Database altered.
SQL> select * from v$standby_log;
GROUP# DBID THREAD# SEQUENCE# BYTES BLOCKSIZE USED ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME LAST_CHANGE# LAST_TIME
---------- ---------------------------------------- ---------- ---------- ---------- ---------- ---------- --- ---------- ------------- --------- ------------ --------- ------------ ---------
4 UNASSIGNED 0 0 52428800 512 0 YES UNASSIGNED
5 UNASSIGNED 0 0 52428800 512 0 YES UNASSIGNED
6 UNASSIGNED 0 0 52428800 512 0 YES UNASSIGNED
7 UNASSIGNED 0 0 52428800 512 0 YES UNASSIGNED
SQL>
--使用下面的命令修改主库参数(此时主库应当使用spfile启动参数)
SQL> edit a.sql
SQL> ! more a.sql
--Add below item when DB acts as primary role
alter system set db_unique_name='ora11g' scope=spfile;
alter system set log_archive_config='DG_CONFIG=(ora11g,ora11gdg)';
alter system set log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST db_unique_name=ora11g valid_for=(ALL_LOGFILES,ALL_ROLES)';
alter system set log_archive_dest_2='SERVICE=ora11gdg ASYNC db_unique_name=ora11gdg valid_for=(ONLINE_LOGFILES,PRIMARY_ROLE)';
alter system set log_archive_dest_state_1=enable;
alter system set log_archive_dest_state_2=enable;
alter system set log_archive_max_processes=4;
alter system set remote_login_passwordfile='EXCLUSIVE' scope=spfile;
--Add below item when DB turn to standby role
alter system set db_file_name_convert='ora11gdg','ora11g' scope=spfile;
alter system set log_file_name_convert='ora11gdg','ora11g' scope=spfile;
alter system set standby_file_management='AUTO';
alter system set fal_server='ora11gdg';
alter system set fal_client='ora11g';
SQL> @a.sql;
System altered.
System altered.
System altered.
System altered.
System altered.
System altered.
System altered.
System altered.
SQL>
--为主库和备库配置监听，整个DG的redo传输服务，都依赖于Oracle Net，因此需要为主备库配置监听
--配置方法多种多样，可用netmgr，netca，以及直接编辑listener.ora 与tnsnames.ora文件
--下面是配置之后的listener.ora 与tnsnames.ora文件内容
[oracle@rhel6_lhr ~]$ more /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.59.130)(PORT = 1522))
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = ora11g)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
(SID_NAME= ora11g)
)
(SID_DESC =
(GLOBAL_DBNAME = ora11gdg)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
(SID_NAME= ora11gdg)
)
)
[oracle@rhel6_lhr admin]$ more tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
ora11g =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.59.130)(PORT = 1522))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ora11g)
)
)
ora11gdg =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.59.130)(PORT = 1522))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ora11gdg)
)
)
[oracle@rhel6_lhr oradata]$ lsnrctl start
LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 04-MAR-2015 10:17:45
Copyright (c) 1991, 2011, Oracle. All rights reserved.
Starting /u01/app/oracle/product/11.2.0/dbhome_1/bin/tnslsnr: please wait...
TNSLSNR for Linux: Version 11.2.0.3.0 - Production
System parameter file is /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/rhel6_lhr/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.59.130)(PORT=1522)))
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.59.130)(PORT=1522)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.3.0 - Production
Start Date 04-MAR-2015 10:17:45
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/dbhome_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/rhel6_lhr/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.59.130)(PORT=1522)))
Services Summary...
Service "ora11g" has 1 instance(s).
Instance "ora11g", status UNKNOWN, has 1 handler(s) for this service...
Service "ora11gdg" has 1 instance(s).
Instance "ora11gdg", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
--由于要求主库与备库sys使用相同的密码，在此处，我们直接复制了主库的密码文件到备库
[oracle@rhel6_lhr dbs]$ cp $ORACLE_HOME/dbs/orapwora11g $ORACLE_HOME/dbs/orapwora11gdg
[oracle@rhel6_lhr dbs]$ echo db_name=ora11g >$ORACLE_HOME/dbs/initora11gdg.ora
[oracle@rhel6_lhr dbs]$ ll $ORACLE_HOME/dbs/initora11gdg.ora
-rw-r--r-- 1 oracle oinstall 15 Mar 4 10:34 /u01/app/oracle/product/11.2.0/dbhome_1/dbs/initora11gdg.ora
[oracle@rhel6_lhr dbs]$
[oracle@rhel6_lhr oradata]$ mkdir -p /u01/app/oracle/admin/ora11gdg/adump
[oracle@rhel6_lhr oradata]$ mkdir -p /u01/app/oracle/oradata/ora11gdg/
[oracle@rhel6_lhr oradata]$ mkdir -p /u01/app/oracle/oradata/ora11gdg/standby_redo/
--对于从主库克隆standby有多种方法，而且Oracle 11g支持从ative database直接克隆数据库
--为主库生成控制文件,注，对于配置standby，不能直接使用copy方式复制控制文件到备库
[oracle@rhel6_lhr ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Wed Mar 4 10:40:55 2015
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup nomount
ORACLE instance started.
Total System Global Area 250560512 bytes
Fixed Size 2227256 bytes
Variable Size 192938952 bytes
Database Buffers 50331648 bytes
Redo Buffers 5062656 bytes
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@rhel6_lhr ~]$ rman target sys/lhr@ora11g auxiliary sys/lhr@ora11gdg
Recovery Manager: Release 11.2.0.3.0 - Production on Wed Mar 4 10:52:09 2015
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORA11G (DBID=4269654224)
connected to auxiliary database: ORA11G (not mounted)
RMAN>
duplicate target database
for standby
from active database
DORECOVER
spfile
set db_unique_name='ora11gdg'
set log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST'
set log_archive_dest_2='service=ora11g async lgwr register valid_for=(online_logfile,primary_role) db_unique_name=ora11g'
set standby_file_management='AUTO'
set fal_server='ora11g'
set fal_client='ora11gdg'
set control_files='/u01/app/oracle/oradata/ora11gdg/crontal01.ctl','/u01/app/oracle/oradata/ora11gdg/control02.ctl'
set db_file_name_convert='ora11g','ora11gdg'
set log_file_name_convert='ora11g','ora11gdg'
set memory_target='400M'
16> ;
Starting Duplicate Db at 2015-03-04 10:52:23
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=171 device type=DISK
contents of Memory Script:
{
backup as copy reuse
targetfile '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwora11g' auxiliary format
'/u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwora11gdg' targetfile
'/u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfileora11g.ora' auxiliary format
'/u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfileora11gdg.ora' ;
sql clone "alter system set spfile= ''/u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfileora11gdg.ora''";
}
executing Memory Script
Starting backup at 2015-03-04 10:52:24
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=144 device type=DISK
Finished backup at 2015-03-04 10:52:26
sql statement: alter system set spfile= ''/u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfileora11gdg.ora''
contents of Memory Script:
{
sql clone "alter system set db_unique_name =
''ora11gdg'' comment=
'''' scope=spfile";
sql clone "alter system set log_archive_dest_1 =
''LOCATION=USE_DB_RECOVERY_FILE_DEST'' comment=
'''' scope=spfile";
sql clone "alter system set log_archive_dest_2 =
''service=ora11g async lgwr register valid_for=(online_logfile,primary_role) db_unique_name=ora11g'' comment=
'''' scope=spfile";
sql clone "alter system set standby_file_management =
''AUTO'' comment=
'''' scope=spfile";
sql clone "alter system set fal_server =
''ora11g'' comment=
'''' scope=spfile";
sql clone "alter system set fal_client =
''ora11gdg'' comment=
'''' scope=spfile";
sql clone "alter system set control_files =
''/u01/app/oracle/oradata/ora11gdg/crontal01.ctl'', ''/u01/app/oracle/oradata/ora11gdg/control02.ctl'' comment=
'''' scope=spfile";
sql clone "alter system set db_file_name_convert =
''ora11g'', ''ora11gdg'' comment=
'''' scope=spfile";
sql clone "alter system set log_file_name_convert =
''ora11g'', ''ora11gdg'' comment=
'''' scope=spfile";
sql clone "alter system set memory_target =
400M comment=
'''' scope=spfile";
shutdown clone immediate;
startup clone nomount;
}
executing Memory Script
sql statement: alter system set db_unique_name = ''ora11gdg'' comment= '''' scope=spfile
sql statement: alter system set log_archive_dest_1 = ''LOCATION=USE_DB_RECOVERY_FILE_DEST'' comment= '''' scope=spfile
sql statement: alter system set log_archive_dest_2 = ''service=ora11g async lgwr register valid_for=(online_logfile,primary_role) db_unique_name=ora11g'' comment= '''' scope=spfile
sql statement: alter system set standby_file_management = ''AUTO'' comment= '''' scope=spfile
sql statement: alter system set fal_server = ''ora11g'' comment= '''' scope=spfile
sql statement: alter system set fal_client = ''ora11gdg'' comment= '''' scope=spfile
sql statement: alter system set control_files = ''/u01/app/oracle/oradata/ora11gdg/crontal01.ctl'', ''/u01/app/oracle/oradata/ora11gdg/control02.ctl'' comment= '''' scope=spfile
sql statement: alter system set db_file_name_convert = ''ora11g'', ''ora11gdg'' comment= '''' scope=spfile
sql statement: alter system set log_file_name_convert = ''ora11g'', ''ora11gdg'' comment= '''' scope=spfile
sql statement: alter system set memory_target = 400M comment= '''' scope=spfile
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 417546240 bytes
Fixed Size 2228944 bytes
Variable Size 272633136 bytes
Database Buffers 134217728 bytes
Redo Buffers 8466432 bytes
contents of Memory Script:
{
backup as copy current controlfile for standby auxiliary format '/u01/app/oracle/oradata/ora11gdg/crontal01.ctl';
restore clone controlfile to '/u01/app/oracle/oradata/ora11gdg/control02.ctl' from
'/u01/app/oracle/oradata/ora11gdg/crontal01.ctl';
}
executing Memory Script
Starting backup at 2015-03-04 10:52:34
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/dbhome_1/dbs/snapcf_ora11g.f tag=TAG20150304T105234 RECID=3 STAMP=873456755
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 2015-03-04 10:52:36
Starting restore at 2015-03-04 10:52:36
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=134 device type=DISK
channel ORA_AUX_DISK_1: copied control file copy
Finished restore at 2015-03-04 10:52:37
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/ora11gdg/temp01.dbf";
switch clone tempfile all;
set newname for datafile 1 to
"/u01/app/oracle/oradata/ora11gdg/system01.dbf";
set newname for datafile 2 to
"/u01/app/oracle/oradata/ora11gdg/sysaux01.dbf";
set newname for datafile 3 to
"/u01/app/oracle/oradata/ora11gdg/undotbs01.dbf";
set newname for datafile 4 to
"/u01/app/oracle/oradata/ora11gdg/users01.dbf";
set newname for datafile 5 to
"/u01/app/oracle/oradata/ora11gdg/example01.dbf";
backup as copy reuse
datafile 1 auxiliary format
"/u01/app/oracle/oradata/ora11gdg/system01.dbf" datafile
2 auxiliary format
"/u01/app/oracle/oradata/ora11gdg/sysaux01.dbf" datafile
3 auxiliary format
"/u01/app/oracle/oradata/ora11gdg/undotbs01.dbf" datafile
4 auxiliary format
"/u01/app/oracle/oradata/ora11gdg/users01.dbf" datafile
5 auxiliary format
"/u01/app/oracle/oradata/ora11gdg/example01.dbf" ;
sql 'alter system archive log current';
}
executing Memory Script
executing command: SET NEWNAME
renamed tempfile 1 to /u01/app/oracle/oradata/ora11gdg/temp01.dbf 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 2015-03-04 10:52:43
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=/u01/app/oracle/oradata/ora11g/system01.dbf
output file name=/u01/app/oracle/oradata/ora11gdg/system01.dbf tag=TAG20150304T105243
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:35
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=/u01/app/oracle/oradata/ora11g/sysaux01.dbf
output file name=/u01/app/oracle/oradata/ora11gdg/sysaux01.dbf tag=TAG20150304T105243
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:45
channel ORA_DISK_1: starting datafile copy
input datafile file number=00005 name=/u01/app/oracle/oradata/ora11g/example01.dbf
output file name=/u01/app/oracle/oradata/ora11gdg/example01.dbf tag=TAG20150304T105243
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=/u01/app/oracle/oradata/ora11g/undotbs01.dbf
output file name=/u01/app/oracle/oradata/ora11gdg/undotbs01.dbf tag=TAG20150304T105243
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=/u01/app/oracle/oradata/ora11g/users01.dbf
output file name=/u01/app/oracle/oradata/ora11gdg/users01.dbf tag=TAG20150304T105243
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 2015-03-04 10:54:37
sql statement: alter system archive log current
contents of Memory Script:
{
backup as copy reuse
archivelog like "/u01/app/oracle/flash_recovery_area/ORA11G/archivelog/2015_03_04/o1_mf_1_10_bhdwvv9v_.arc" auxiliary format
"/u01/app/oracle/flash_recovery_area/ORA11GDG/archivelog/2015_03_04/o1_mf_1_10_%u_.arc" archivelog like
"/u01/app/oracle/flash_recovery_area/ORA11G/archivelog/2015_03_04/o1_mf_1_11_bhdwzfd3_.arc" auxiliary format
"/u01/app/oracle/flash_recovery_area/ORA11GDG/archivelog/2015_03_04/o1_mf_1_11_%u_.arc" ;
catalog clone recovery area;
switch clone datafile all;
}
executing Memory Script
Starting backup at 2015-03-04 10:54:37
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log copy
input archived log thread=1 sequence=10 RECID=3 STAMP=873456763
output file name=/u01/app/oracle/flash_recovery_area/ORA11GDG/archivelog/2015_03_04/o1_mf_1_10_0dq0vp7d_.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=11 RECID=4 STAMP=873456877
output file name=/u01/app/oracle/flash_recovery_area/ORA11GDG/archivelog/2015_03_04/o1_mf_1_11_0eq0vp7f_.arc RECID=0 STAMP=0
channel ORA_DISK_1: archived log copy complete, elapsed time: 00:00:01
Finished backup at 2015-03-04 10:54:40
searching for all files in the recovery area
List of Files Unknown to the Database
=====================================
File Name: /u01/app/oracle/flash_recovery_area/ORA11GDG/archivelog/2015_03_04/o1_mf_1_11_0eq0vp7f_.arc
File Name: /u01/app/oracle/flash_recovery_area/ORA11GDG/archivelog/2015_03_04/o1_mf_1_10_0dq0vp7d_.arc
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: /u01/app/oracle/flash_recovery_area/ORA11GDG/archivelog/2015_03_04/o1_mf_1_11_0eq0vp7f_.arc
File Name: /u01/app/oracle/flash_recovery_area/ORA11GDG/archivelog/2015_03_04/o1_mf_1_10_0dq0vp7d_.arc
datafile 1 switched to datafile copy
input datafile copy RECID=3 STAMP=873456880 file name=/u01/app/oracle/oradata/ora11gdg/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=4 STAMP=873456880 file name=/u01/app/oracle/oradata/ora11gdg/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=5 STAMP=873456880 file name=/u01/app/oracle/oradata/ora11gdg/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=6 STAMP=873456880 file name=/u01/app/oracle/oradata/ora11gdg/users01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=7 STAMP=873456880 file name=/u01/app/oracle/oradata/ora11gdg/example01.dbf
contents of Memory Script:
{
set until scn 1111652;
recover
standby
clone database
delete archivelog
;
}
executing Memory Script
executing command: SET until clause
Starting recover at 2015-03-04 10:54:40
using channel ORA_AUX_DISK_1
starting media recovery
archived log for thread 1 with sequence 10 is already on disk as file /u01/app/oracle/flash_recovery_area/ORA11GDG/archivelog/2015_03_04/o1_mf_1_10_0dq0vp7d_.arc
archived log for thread 1 with sequence 11 is already on disk as file /u01/app/oracle/flash_recovery_area/ORA11GDG/archivelog/2015_03_04/o1_mf_1_11_0eq0vp7f_.arc
archived log file name=/u01/app/oracle/flash_recovery_area/ORA11GDG/archivelog/2015_03_04/o1_mf_1_10_0dq0vp7d_.arc thread=1 sequence=10
archived log file name=/u01/app/oracle/flash_recovery_area/ORA11GDG/archivelog/2015_03_04/o1_mf_1_11_0eq0vp7f_.arc thread=1 sequence=11
media recovery complete, elapsed time: 00:00:01
Finished recover at 2015-03-04 10:54:43
Finished Duplicate Db at 2015-03-04 10:54:46
[oracle@rhel6_lhr standby_redo]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Wed Mar 4 11:02:24 2015
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, OLAP, Data Mining and Real Application Testing options
SQL> shutdown abort
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 417546240 bytes
Fixed Size 2228944 bytes
Variable Size 289410352 bytes
Database Buffers 117440512 bytes
Redo Buffers 8466432 bytes
Database mounted.
SQL> alter system set dg_broker_start=true;
System altered.
SQL> show parameter instance_name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
instance_name string ora11gdg
SQL>
以只读方式打开数据库，oracle知道我们在备用数据库控制文件中进行装载，所以当打开数据时，他将自动置于只读模式。
SQL> alter database open;
Database altered.
SQL> select count(1) from lhr.test;
COUNT(1)
----------
2834
SQL> set line 9999
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
---------- --------- ----------- -------------------- ---------------- --- -------------------- --------------------
4269654224 ORA11G 1113620 MAXIMUM PERFORMANCE PHYSICAL STANDBY YES READ ONLY WITH APPLY NOT ALLOWED
SQL>
为了实时查询，启用管理恢复
SQL> alter database recover managed standby database using current logfile disconnect;
主库切换日志
SQL> ALTER SYSTEM SWITCH LOGFILE;
System altered.
SQL> ALTER SYSTEM SWITCH LOGFILE;
System altered.
SQL>
--在备库端启动redo apply
SQL> select name,open_mode,database_role,protection_mode from v$database;
NAME OPEN_MODE DATABASE_ROLE PROTECTION_MODE
--------- -------------------- ---------------- --------------------
LILOVE MOUNTED PHYSICAL STANDBY MAXIMUM PERFORMANCE
SQL> alter database recover managed standby database disconnect from session;
Database altered.
SQL> select sequence#, first_time, next_time,applied from v$archived_log order by sequence#;
SEQUENCE# FIRST_TIM NEXT_TIME APPLIED
---------- --------- --------- ---------
31 03-MAR-15 03-MAR-15 NO
32 03-MAR-15 03-MAR-15 NO
33 03-MAR-15 03-MAR-15 NO
34 03-MAR-15 03-MAR-15 NO
35 03-MAR-15 03-MAR-15 NO
36 03-MAR-15 03-MAR-15 NO
6 rows selected.
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 35
Next log sequence to archive 0
Current log sequence 37
SQL>
至此，物理备库搭建完成。