使用前面创建的数据库作为primary端，确认其处于open，归档及forcelogging状态。
SQL> alter database force logging;
Database altered.
SQL> select name,open_mode,log_mode,force_logging from gv$database;
NAME OPEN_MODE LOG_MODE FOR
--------- -------------------- ------------ ---
STONE READ WRITE ARCHIVELOG YES
STONE READ WRITE ARCHIVELOG YES
前面只创建了2个虚拟机，这里重新再创建一个或者克隆一个虚拟机，配置好网络，并安装相同版本的数据库（11.2.0.4）作为Standby端，不要创建数据库。
采用活动数据库复制的方法搭建DG，需要配置静态监听。在RAC1节点使用grid用户配置静态监听。
修改前的状态：
[grid@rac1 ~]$ lsnrctl status
LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 07-APR-2016 16:36:06
Copyright (c) 1991, 2013, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date 07-APR-2016 14:34:24
Uptime 0 days 2 hr. 1 min. 41 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/rac1/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.247.131)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.247.133)(PORT=1521)))
Services Summary...
Service "+ASM" has 1 instance(s).
Instance "+ASM1", status READY, has 1 handler(s) for this service...
Service "stone" has 1 instance(s).
Instance "stone1", status READY, has 1 handler(s) for this service...
Service "stoneXDB" has 1 instance(s).
Instance "stone1", status READY, has 1 handler(s) for this service...
The command completed successfully
使用netmgr图形化工具进行修改，结果如下：
[grid@rac1 ~]$ cat /u01/app/11.2.0/grid/network/admin/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 = stone)
(ORACLE_HOME = /u01/app/11.2.0/grid)
(SID_NAME = stone1)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = LISTENER))
)
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.247.131)(PORT = 1521))
)
)
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
重启监听后，如下：
[grid@rac1 ~]$ lsnrctl status
LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 07-APR-2016 16:42:51
Copyright (c) 1991, 2013, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date 07-APR-2016 16:41:18
Uptime 0 days 0 hr. 1 min. 33 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/rac1/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.247.131)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.247.133)(PORT=1521)))
Services Summary...
Service "+ASM" has 1 instance(s).
Instance "+ASM1", status READY, has 1 handler(s) for this service...
Service "stone" has 2 instance(s).
Instance "stone1", status UNKNOWN, has 1 handler(s) for this service...
Instance "stone1", status READY, has 1 handler(s) for this service...
Service "stoneXDB" has 1 instance(s).
Instance "stone1", status READY, has 1 handler(s) for this service...
The command completed successfully
同样使用netmgr配置静态监听，结果如下：
[oracle@dg1 ~]$ cat /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.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = stone)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
(SID_NAME = stonedg1)
)
)
LISTENER =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.247.136)(PORT = 1521))
)
ADR_BASE_LISTENER = /u01/app/oracle
[oracle@dg1 ~]$ lsnrctl status
LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 07-APR-2016 16:54:35
Copyright (c) 1991, 2013, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.247.136)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date 07-APR-2016 16:53:34
Uptime 0 days 0 hr. 1 min. 1 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/dg1/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.247.136)(PORT=1521)))
Services Summary...
Service "stone" has 1 instance(s).
Instance "stonedg1", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
在2个节点使用oracle用户，使用netmgr配置到Standby端的网络服务命名。结果如下：
[oracle@rac1 ~]$ cat /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/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.
STONE =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac-scan)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = stone)
)
)
STANDBY136 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.247.136)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = stone)
)
)
[oracle@rac2 ~]$ cat /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/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.
STONE =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac-scan)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = stone)
)
)
STANDBY136 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.247.136)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = stone)
)
)
同样使用netmgr配置服务命名，结果如下：
[oracle@dg1 ~]$ cat /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/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.
PRIMARY131 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.247.131)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = stone)
)
)
测试primary到standby的网络：
[oracle@rac1 ~]$ tnsping standby136
TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 07-APR-2016 17:11:01
Copyright (c) 1997, 2013, Oracle. All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.247.136)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = stone)))
OK (50 msec)
测试standby到primary的网络：
[oracle@dg1 ~]$ tnsping primary131
TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 07-APR-2016 17:11:37
Copyright (c) 1997, 2013, Oracle. All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.247.131)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = stone)))
OK (50 msec)
修改相关参数：
SQL> alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(stone,stonedg1)';
System altered.
SQL> alter system set LOG_ARCHIVE_DEST_2='SERVICE=standby136 LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=stonedg1';
System altered.
SQL> alter system set LOG_ARCHIVE_DEST_STATE_2=ENABLE;
System altered.
根据spfile.ora生成pfile.ora：
SQL> create pfile='/home/oracle/pfile.ora' from spfile;
File created.
将primary生成的pfile拷贝过来：
[oracle@dg1 ~]$ scp oracle@192.168.247.131:/home/oracle/pfile.ora .
reverse mapping checking getaddrinfo for bogon [192.168.247.131] failed - POSSIBLE BREAK-IN ATTEMPT!
oracle@192.168.247.131's password:
pfile.ora 100% 1529 1.5KB/s 00:00
进行修改后内容如下：（此处注意：如果主库是使用OMF管理文件，则standby端的目录路径可以不必和primary端一致，也无需设置DB_FILE_NAME_CONVERT，系统会自动进行转换；如果不是使用OMF管理文件，则standby端的目录路径需要和primary端保持一致，否则就需要使用DB_FILE_NAME_CONVERT进行转换。）
[oracle@dg1 ~]$ cat pfile.ora
stonedg1.__db_cache_size=230686720
stonedg1.__java_pool_size=4194304
stonedg1.__large_pool_size=8388608
stonedg1.__pga_aggregate_target=390070272
stonedg1.__sga_target=444596224
stonedg1.__shared_io_pool_size=0
stonedg1.__shared_pool_size=192937984
stonedg1.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/stonedg1/adump'
*.audit_trail='db'
*.compatible='11.2.0.4.0'
*.control_files='/u01/app/oracle/oradata/stonedg1/controlfile/control1.ctl','/u01/app/oracle/flash_recovery_area/stonedg1/controlfile/control2.ctl'
*.db_block_size=8192
*.db_create_file_dest='/u01/app/oracle/oradata'
*.db_domain=''
*.db_name='stone'
*.db_unique_name='stonedg1'
*.db_recovery_file_dest='/u01/app/oracle/flash_recovery_area'
*.db_recovery_file_dest_size=8487174144
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=stoneXDB)'
*.log_archive_config='DG_CONFIG=(stone,stonedg1)'
*.log_archive_dest_1='location=/u01/app/oracle/oradata/stonedg1/archivelog/ valid_for=(all_logfiles,all_roles) db_unique_name=stonedg1'
*.log_archive_format='%t_%s_%r.dbf'
*.memory_target=834666496
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='exclusive'
*.standby_file_management='auto'
*.fal_server='primary131'
*.undo_tablespace='UNDOTBS1'
根据修改后的pfile.ora生成spfilestonedg1.ora。
[oracle@dg1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Thu Apr 7 18:01:17 2016
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to an idle instance.
SQL> create spfile from pfile='/home/oracle/pfile.ora';
File created.
根据参数文件创建相关目录。
mkdir -p /u01/app/oracle/admin/stonedg1/{adump,bdump,cdump,udump}
mkdir -p /u01/app/oracle/oradata/stonedg1/{controlfile,datafile,onlinelog,archivelog}
mkdir -p /u01/app/oracle/flash_recovery_area/stonedg1/{controlfile,onlinelog,archivelog}
将primary端的密码文件复制到standby并重命名为orapwstonedg1.ora。
[oracle@dg1 ~]$ scp oracle@192.168.247.131:/u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwstone1 /u01/app/oracle/product/11.2.0/dbhome_1/dbs/
oracle@192.168.247.131's password:
orapwstone1 100% 1536 1.5KB/s 00:00
[oracle@dg1 ~]$ mv /u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwstone1 /u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwstonedg1
将standby数据库启动到nomount状态：
[oracle@dg1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Thu Apr 7 18:45:09 2016
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 830930944 bytes
Fixed Size 2257800 bytes
Variable Size 578817144 bytes
Database Buffers 247463936 bytes
Redo Buffers
在primary RAC1节点运行如下命令:
[oracle@rac1 ~]$ rman target / auxiliary sys/123456@standby136
Recovery Manager: Release 11.2.0.4.0 - Production on Thu Apr 7 18:56:14 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: STONE (DBID=3018705892)
connected to auxiliary database: STONE (not mounted)
RMAN> duplicate target database for standby nofilenamecheck from active database;
Starting Duplicate Db at 07-APR-16
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=1 device type=DISK
contents of Memory Script:
{
backup as copy reuse
targetfile '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwstone1' auxiliary format
'/u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwstonedg1' ;
}
executing Memory Script
Starting backup at 07-APR-16
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=54 instance=stone1 device type=DISK
Finished backup at 07-APR-16
contents of Memory Script:
{
backup as copy current controlfile for standby auxiliary format '/u01/app/oracle/oradata/stonedg1/controlfile/control1.ctl';
restore clone controlfile to '/u01/app/oracle/flash_recovery_area/stonedg1/controlfile/control2.ctl' from
'/u01/app/oracle/oradata/stonedg1/controlfile/control1.ctl';
}
executing Memory Script
Starting backup at 07-APR-16
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_stone1.f tag=TAG20160407T185627 RECID=3 STAMP=908564191
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 07-APR-16
Starting restore at 07-APR-16
using channel ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: copied control file copy
Finished restore at 07-APR-16
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 clone tempfile 1 to new;
switch clone tempfile all;
set newname for clone datafile 1 to new;
set newname for clone datafile 2 to new;
set newname for clone datafile 3 to new;
set newname for clone datafile 4 to new;
set newname for clone datafile 5 to new;
set newname for clone datafile 6 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
datafile 5 auxiliary format new
datafile 6 auxiliary format new
;
sql 'alter system archive log current';
}
executing Memory Script
executing command: SET NEWNAME
renamed tempfile 1 to /u01/app/oracle/oradata/STONEDG1/datafile/o1_mf_temp_%u_.tmp in control file
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting backup at 07-APR-16
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=+DATA/stone/datafile/system.256.908479415
output file name=/u01/app/oracle/oradata/STONEDG1/datafile/o1_mf_system_03r2f5n9_.dbf tag=TAG20160407T185641
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=+DATA/stone/datafile/sysaux.257.908479417
output file name=/u01/app/oracle/oradata/STONEDG1/datafile/o1_mf_sysaux_04r2f5oc_.dbf tag=TAG20160407T185641
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting datafile copy
input datafile file number=00005 name=+DATA/stone/datafile/example.264.908479575
output file name=/u01/app/oracle/oradata/STONEDG1/datafile/o1_mf_example_05r2f5p5_.dbf tag=TAG20160407T185641
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=+DATA/stone/datafile/undotbs1.258.908479417
output file name=/u01/app/oracle/oradata/STONEDG1/datafile/o1_mf_undotbs1_06r2f5pl_.dbf tag=TAG20160407T185641
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile copy
input datafile file number=00006 name=+DATA/stone/datafile/undotbs2.265.908479933
output file name=/u01/app/oracle/oradata/STONEDG1/datafile/o1_mf_undotbs2_07r2f5ps_.dbf tag=TAG20160407T185641
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/stone/datafile/users.259.908479417
output file name=/u01/app/oracle/oradata/STONEDG1/datafile/o1_mf_users_08r2f5pv_.dbf tag=TAG20160407T185641
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 07-APR-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=3 STAMP=908564296 file name=/u01/app/oracle/oradata/STONEDG1/datafile/o1_mf_system_03r2f5n9_.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=4 STAMP=908564296 file name=/u01/app/oracle/oradata/STONEDG1/datafile/o1_mf_sysaux_04r2f5oc_.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=5 STAMP=908564296 file name=/u01/app/oracle/oradata/STONEDG1/datafile/o1_mf_undotbs1_06r2f5pl_.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=6 STAMP=908564296 file name=/u01/app/oracle/oradata/STONEDG1/datafile/o1_mf_users_08r2f5pv_.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=7 STAMP=908564296 file name=/u01/app/oracle/oradata/STONEDG1/datafile/o1_mf_example_05r2f5p5_.dbf
datafile 6 switched to datafile copy
input datafile copy RECID=8 STAMP=908564296 file name=/u01/app/oracle/oradata/STONEDG1/datafile/o1_mf_undotbs2_07r2f5ps_.dbf
Finished Duplicate Db at 07-APR-16
SQL> select recovery_mode from v$archive_dest_status where dest_id=1;
RECOVERY_MODE
-----------------------
IDLE
Standby端没有启动redo应用前，恢复模式为IDLE。
SQL> alter database recover managed standby database disconnect from session;
数据库已更改。
SQL> select recovery_mode from v$archive_dest_status where dest_id=1;
RECOVERY_MODE
-----------------------
MANAGED
Standby端启动redo应用后，恢复模式为MANAGED。
SQL> alter database recover managed standby database cancel;
数据库已更改。
SQL> alter database recover managed standby database using current logfile disconnect from session;
alter database recover managed standby database using current logfile disconnect
from session
*
ERROR at line 1:
ORA-38500: USING CURRENT LOGFILE option not available without standby redo logs
由于没有standby redologs，故使用standby redologs日志启动redo实时应用失败。
17.6.3 在standby端创建standby redologs
日志组数量6组，比两个节点加起来的online redologs数量多2组。
SQL> alter database add standby logfile group 11 size 50m;
Database altered.
SQL> alter database add standby logfile group 12 size 50m;
Database altered.
SQL> alter database add standby logfile group 13 size 50m;
Database altered.
SQL> alter database add standby logfile group 14 size 50m;
Database altered.
SQL> alter database add standby logfile group 15 size 50m;
Database altered.
SQL> alter database add standby logfile group 16 size 50m;
Database altered.
SQL> alter database recover managed standby database using current logfile disconnect from session;
Database altered.
SQL> select recovery_mode from v$archive_dest_status where dest_id=1;
RECOVERY_MODE
-----------------------
MANAGED REAL TIME APPLY
在standby端查询standby redologs状态显示未使用。
SQL> select group#, sequence#, dbid,status from v$standby_log;
GROUP# SEQUENCE# DBID STATUS
---------- ---------- ---------------------------------------- ----------
4 0 UNASSIGNED UNASSIGNED
5 0 UNASSIGNED UNASSIGNED
6 0 UNASSIGNED UNASSIGNED
7 0 UNASSIGNED UNASSIGNED
在primary端切换日志将standby redologs激活。
SQL> alter system switch logfile;
System altered.
再次在standby端查询standby redologs状态显示激活。
SQL> select group#, sequence#, dbid,status from v$standby_log;
GROUP# SEQUENCE# DBID STATUS
---------- ---------- ---------------------------------------- ----------
11 28 3018705892 ACTIVE
12 0 UNASSIGNED UNASSIGNED
13 0 UNASSIGNED UNASSIGNED
14 0 UNASSIGNED UNASSIGNED
15 0 UNASSIGNED UNASSIGNED
16 0 UNASSIGNED UNASSIGNED
6 rows selected.
主备库最大归档序号相同：
SQL> select max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
--------------
32
SQL> select max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
--------------
32
两个primary节点的日志归档状态如下：
SQL> select dest_id,error,status,log_sequence,applied_scn from v$archive_dest where dest_id=2;
DEST_ID ERROR STATUS LOG_SEQUENCE APPLIED_SCN
---------- ---------------------------------------- --------- ------------ -----------
2 VALID 32 1303371
SQL> select dest_id,error,status,log_sequence,applied_scn from v$archive_dest where dest_id=2;
DEST_ID ERROR STATUS LOG_SEQUENCE APPLIED_SCN
---------- ---------------------------------------- --------- ------------ -----------
2 VALID 24 1311729
查看当前数据库打开模式。
SQL> select open_mode from v$database;
OPEN_MODE
--------------------
MOUNTED
直接打开数据库将报错。
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-10456: cannot open standby database; media recovery session may be in progress
先暂停redo应用，然后打开数据库，再重启redo应用。
SQL> alter database recover managed standby database cancel;
Database altered.
SQL> alter database open;
Database altered.
SQL> select open_mode from v$database;
OPEN_MODE
--------------------
READ ONLY
SQL> alter database recover managed standby database using current logfile disconnect from session;
Database altered.
SQL> select open_mode from v$database;
OPEN_MODE
--------------------
READ ONLY WITH APPLY
SQL> select recovery_mode from v$archive_dest_status where dest_id=1;
RECOVERY_MODE
-----------------------
MANAGED REAL TIME APPLY
进行测试：
在primary端节点1创建表并插入数据
SQL> conn hr/hr
Connected.
SQL> create table emp as select * from employees;
Table created.
在standby端查询：
SQL> conn hr/hr
Connected.
SQL> select count(*) from emp;
COUNT(*)
----------
107
在primary端节点2创建表并插入数据
SQL> create table hr.emp1 as select * from hr.employees;
Table created.
在standby端查询：
SQL> select count(*) from hr.emp1;
COUNT(*)
----------
107