Oracle 10GR2 DataGuard 搭建（物理Standby）
# vi /etc/hosts增加172.20.48.88 dg01172.20.48.89 dg02
?
验证网络环境
# ping dg01PING dg01 (172.20.48.88) 56(84) bytes of data.64 bytes from dg01 (172.20.48.88): icmp_seq=1 ttl=64 time=0.021 ms--- dg01 ping statistics ---1 packets transmitted, 1 received, 0% packet loss, time 0msrtt min/avg/max/mdev = 0.021/0.021/0.021/0.000 ms# ping dg02PING dg02 (172.20.48.89) 56(84) bytes of data.64 bytes from dg02 (172.20.48.89): icmp_seq=1 ttl=64 time=3.17 ms--- dg02 ping statistics ---1 packets transmitted, 1 received, 0% packet loss, time 0msrtt min/avg/max/mdev = 3.178/3.178/3.178/0.000 ms
?
登录standby
# vi /etc/hosts增加172.20.48.89 dg02172.20.48.88 dg01
?
?
验证网络环境
# ping dg01PING dg01 (172.20.48.88) 56(84) bytes of data.64 bytes from dg01 (172.20.48.88): icmp_seq=1 ttl=64 time=2.32 ms--- dg01 ping statistics ---1 packets transmitted, 1 received, 0% packet loss, time 0msrtt min/avg/max/mdev = 2.329/2.329/2.329/0.000 ms# ping dg02PING dg02 (172.20.48.89) 56(84) bytes of data.64 bytes from dg02 (172.20.48.89): icmp_seq=1 ttl=64 time=0.019 ms--- dg02 ping statistics ---1 packets transmitted, 1 received, 0% packet loss, time 0msrtt min/avg/max/mdev = 0.019/0.019/0.019/0.000 ms
?
?
2.修改primary、standby主机tnsname文件确保数据库通讯正常
?
登录primary
$ vi $ORACLE_HOME/network/admin/tnsnames.ora 增加DG02 = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = dg02)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = dg02) ) )
?
?
查看通讯状态
$ tnsping dg01TNS Ping Utility for Linux: Version 10.2.0.1.0 - Production on 18-AUG-2013 21:33:32Copyright (c) 1997, 2005, Oracle. All rights reserved.Used parameter files:Used TNSNAMES adapter to resolve the aliasAttempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = dg01)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = dg01)))OK (10 msec)$ tnsping dg02TNS Ping Utility for Linux: Version 10.2.0.1.0 - Production on 18-AUG-2013 21:33:34Copyright (c) 1997, 2005, Oracle. All rights reserved.Used parameter files:Used TNSNAMES adapter to resolve the aliasAttempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = dg02)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = dg02)))OK (10 msec)
?
登录standby
$ vi $ORACLE_HOME/network/admin/tnsnames.ora增加DG01 = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = dg01)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = dg01) ) )
?
?
查看通讯状态
$ tnsping dg01TNS Ping Utility for Linux: Version 10.2.0.1.0 - Production on 18-AUG-2013 21:34:53Copyright (c) 1997, 2005, Oracle. All rights reserved.Used parameter files:Used TNSNAMES adapter to resolve the aliasAttempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = dg01)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = dg01)))OK (0 msec)$ tnsping dg02TNS Ping Utility for Linux: Version 10.2.0.1.0 - Production on 18-AUG-2013 21:34:58Copyright (c) 1997, 2005, Oracle. All rights reserved.Used parameter files:Used TNSNAMES adapter to resolve the aliasAttempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = dg02)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = dg02)))OK (0 msec)
?
?
登录standby验证tns是否配置成功
$ sqlplus /nologSQL*Plus: Release 10.2.0.1.0 - Production on Sun Aug 18 21:36:51 2013Copyright (c) 1982, 2005, Oracle. All rights reserved.SQL> conn system/连接密码@dg01Connected. #成功连接SQL> quitDisconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - ProductionWith the Partitioning, OLAP and Data Mining options
?
?
3.设定日志记录模式及创建pfile文件
?
登录primary
$ cd /u01/app/oracle/product/db_1/dbs$ sqlplus /nologSQL*Plus: Release 10.2.0.1.0 - Production on Sun Aug 18 21:40:19 2013Copyright (c) 1982, 2005, Oracle. All rights reserved.SQL> conn /as sysdbaConnected.SQL> alter database force logging; #设定日志记录模式Database altered.SQL> create pfile from spfile; #通过spfile文件创建pfile文件File created.SQL> quitDisconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - ProductionWith the Partitioning, OLAP and Data Mining options
?
?
4.创建口令文件
?
登录primary
$ cd /u01/app/oracle/product/db_1/dbs$ orapwd file=orapwdg01 password=数据库密码 entries=5
?
?
5.修改pfile文件
?
登录primary
$ vi /u01/app/oracle/product/db_1/dbs/initdg01.ora增加log_archive_dest_1='LOCATION=/u01/app/oracle/oradata/archive'log_archive_dest_state_1=enablelog_archive_dest_2='SERVICE=dg02'log_archive_dest_state_2=enablelog_archive_start=truelog_archive_format='%t_%s_%r.arc'log_archive_max_processes=2fal_server=dg02fal_client=dg01db_unique_name=dg01$ mkdir -p /u01/app/oracle/oradata/archive$ mkdir -p /u01/app/oracle/flash_recovery_area
?
?
?6.修改archive默认目录并创建standby文件
?
登录primary
$ sqlplus /nologSQL*Plus: Release 10.2.0.1.0 - Production on Sun Aug 18 21:47:51 2013Copyright (c) 1982, 2005, Oracle. All rights reserved.SQL> conn /as sysdbaConnected.SQL> archive log list;Database log mode Archive ModeAutomatic archival EnabledArchive destination USE_DB_RECOVERY_FILE_DESTOldest online log sequence 0Next log sequence to archive 1Current log sequence 1SQL> alter system set db_recovery_file_dest='';#清空路径System altered.SQL> alter system set log_archive_dest='/u01/app/oracle/oradata/archive';#设定新路径System altered.SQL> archive log list;Database log mode Archive ModeAutomatic archival EnabledArchive destination /u01/app/oracle/oradata/archiveOldest online log sequence 0Next log sequence to archive 1Current log sequence 1SQL> startup force;ORACLE instance started.Total System Global Area 285212672 bytesFixed Size 1218968 bytesVariable Size 88082024 bytesDatabase Buffers 188743680 bytesRedo Buffers 7168000 bytesDatabase mounted.Database opened.SQL> archive log list;Database log mode Archive ModeAutomatic archival EnabledArchive destination /u01/app/oracle/oradata/archiveOldest online log sequence 1Next log sequence to archive 2Current log sequence 2SQL> shutdown immediate;Database closed.Database dismounted.ORACLE instance shut down.SQL> startup mount;ORACLE instance started.Total System Global Area 285212672 bytesFixed Size 1218968 bytesVariable Size 88082024 bytesDatabase Buffers 188743680 bytesRedo Buffers 7168000 bytesDatabase mounted.SQL> alter database create standby controlfile as '/u01/app/oracle/oradata/dg01/standby.ctl';#创建standby文件Database altered.SQL> alter database open;Database altered.SQL> shutdown immediateDatabase closed.Database dismounted.ORACLE instance shut down.SQL> quitDisconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - ProductionWith the Partitioning, OLAP and Data Mining options
?
?
7.将primary数据库拷贝至standby
?
登录primary
$ cd /u01/app/oracle/oradata/$ scp -r dg01 dg02:/u01/app/oracle/oradata $ cd /u01/app/oracle/admin/$ scp -r dg01 dg02:/u01/app/oracle/admin/$ cd /u01/app/oracle/product/db_1/dbs$ scp initdg01.ora orapwdg01 dg02:/u01/app/oracle/product/db_1/dbs
?
?
8.?使用standby替换control文件
?
登录standby
$ cd /u01/app/oracle/oradata/dg01/$ rm -rf control0*$ mv standby.ctl control01.ctl$ cp control01.ctl control02.ctl $ cp control02.ctl control03.ctl
?
?
9.修改standby pfile文件
?
登录standby
$ cd /u01/app/oracle/product/db_1/dbs/$ vi initdg01.ora 修改为log_archive_dest_1='LOCATION=/u01/app/oracle/oradata/archive'log_archive_dest_state_1=enablelog_archive_dest_2='SERVICE=dg01'log_archive_dest_state_2=enablelog_archive_start=truelog_archive_format='%t_%s_%r.arc'log_archive_max_processes=2fal_server=dg01fal_client=dg02db_unique_name=dg02$ mkdir -p /u01/app/oracle/oradata/archive$ mkdir -p /u01/app/oracle/flash_recovery_area
?
?
10.应用新的primary pfile文件
?
登录primary
$ cd /u01/app/oracle/product/db_1/dbs/$ rm -rf spfiledg01.ora $ sqlplus /nologSQL*Plus: Release 10.2.0.1.0 - Production on Sun Aug 18 22:15:55 2013Copyright (c) 1982, 2005, Oracle. All rights reserved.SQL> conn /as sysdbaConnected to an idle instance.SQL> create spfile from pfile;File created.SQL> startupORACLE instance started.Total System Global Area 285212672 bytesFixed Size 1218968 bytesVariable Size 88082024 bytesDatabase Buffers 188743680 bytesRedo Buffers 7168000 bytesDatabase mounted.Database opened.SQL> quitDisconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - ProductionWith the Partitioning, OLAP and Data Mining options
?
?
11.应用新的standby pfile文件并启动备库
?
登录standby
$ cd /u01/app/oracle/product/db_1/dbs/$ sqlplus /nologSQL*Plus: Release 10.2.0.1.0 - Production on Sun Aug 18 22:18:34 2013Copyright (c) 1982, 2005, Oracle. All rights reserved.SQL> conn /as sysdbaConnected to an idle instance.SQL> create spfile from pfile;File created.SQL> startup nomount;ORACLE instance started.Total System Global Area 285212672 bytesFixed Size 1218968 bytesVariable Size 88082024 bytesDatabase Buffers 188743680 bytesRedo Buffers 7168000 bytesSQL> alter database mount standby database;Database altered.SQL> select name,database_role from v$database;NAME DATABASE_ROLE------------------ --------------------------------DG01 PHYSICAL STANDBY
?
?
登录primary
$ sqlplus /nologSQL*Plus: Release 10.2.0.1.0 - Production on Sun Aug 18 22:20:43 2013Copyright (c) 1982, 2005, Oracle. All rights reserved.SQL> conn /as sysdbaConnected.SQL> select name,database_role from v$database;NAME DATABASE_ROLE------------------ --------------------------------DG01 PRIMARY
?
$ sqlplus /nologSQL*Plus: Release 10.2.0.1.0 - Production on Sun Aug 18 22:22:18 2013Copyright (c) 1982, 2005, Oracle. All rights reserved.SQL> conn /as sysdbaConnected.SQL> Alter database recover managed standby database disconnect from session;Database altered.
?
?
此时数据库日志状态
Sun Aug 18 22:24:17 2013Alter database recover managed standby database disconnect from sessionSun Aug 18 22:24:17 2013Attempt to start background Managed Standby Recovery process (dg01)MRP0 started with pid=19, OS id=2975Sun Aug 18 22:24:17 2013MRP0: Background Managed Standby Recovery process started (dg01)Managed Standby Recovery not using Real Time ApplyClearing online redo logfile 1 /u01/app/oracle/oradata/dg01/redo01.logClearing online log 1 of thread 1 sequence number 2Sun Aug 18 22:24:23 2013Clearing online redo logfile 1 completeMedia Recovery Waiting for thread 1 sequence 2Sun Aug 18 22:24:23 2013Completed: Alter database recover managed standby database disconnect from session
?
?
使用alter system switch logfile查看传输状态
?
primary
Thread 1 cannot allocate new log, sequence 3Private strand flush not complete Current log# 1 seq# 2 mem# 0: /u01/app/oracle/oradata/dg01/redo01.logThread 1 advanced to log sequence 3 Current log# 2 seq# 3 mem# 0: /u01/app/oracle/oradata/dg01/redo02.log
?
?
standby
RFS[1]: No standby redo logfiles createdRFS[1]: Archived Log: '/u01/app/oracle/oradata/archive/1_2_823812298.arc'Sun Aug 18 22:26:08 2013Media Recovery Log /u01/app/oracle/oradata/archive/1_2_823812298.arcMedia Recovery Waiting for thread 1 sequence 3
?
?
三、同步功能测试
?
登录primary建立test表
$ sqlplus /nologSQL*Plus: Release 10.2.0.1.0 - Production on Mon Aug 19 09:08:51 2013Copyright (c) 1982, 2005, Oracle. All rights reserved.SQL> conn /as sysdbaConnected.SQL> create table test (id int, name char(10));Table created.SQL> insert into test values(0, ' test 01'); 1 row created.SQL> insert into test values(1, ' test 02');1 row created.SQL> commit;Commit complete.SQL> select * from test; ID NAME---------- -------------------- 0 test 01 1 test 02SQL> alter system switch logfile;System altered.SQL> /System altered.SQL> quitDisconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - ProductionWith the Partitioning, OLAP and Data Mining options
?
?
primary日志状态
Mon Aug 19 09:10:02 2013Thread 1 advanced to log sequence 5 Current log# 1 seq# 5 mem# 0: /u01/app/oracle/oradata/dg01/redo01.logThread 1 advanced to log sequence 6 Current log# 2 seq# 6 mem# 0: /u01/app/oracle/oradata/dg01/redo02.logMon Aug 19 09:10:13 2013
?
?
standby日志状态
RFS[1]: Archived Log: '/u01/app/oracle/oradata/archive/1_4_823812298.arc'RFS[1]: No standby redo logfiles createdRFS[1]: Archived Log: '/u01/app/oracle/oradata/archive/1_5_823812298.arc'Mon Aug 19 09:10:22 2013Media Recovery Log /u01/app/oracle/oradata/archive/1_4_823812298.arcMedia Recovery Log /u01/app/oracle/oradata/archive/1_5_823812298.arcMedia Recovery Waiting for thread 1 sequence 6
?
?
登录standby验证
$ sqlplus /nologSQL*Plus: Release 10.2.0.1.0 - Production on Mon Aug 19 09:13:34 2013Copyright (c) 1982, 2005, Oracle. All rights reserved.SQL> conn /as sysdbaConnected.SQL> select sequence#, first_time,next_time from v$archived_log order by sequence#; SEQUENCE# FIRST_TIME NEXT_TIME---------- ------------ ------------ 2 18-AUG-13 18-AUG-13 3 18-AUG-13 18-AUG-13 4 18-AUG-13 19-AUG-13 5 19-AUG-13 19-AUG-13SQL> select sequence#,applied from v$archived_log order by sequence#; SEQUENCE# APPLIE---------- ------ 2 YES 3 YES 4 YES 5 YESSQL> alter database recover managed standby database cancel;Database altered.SQL> alter database open read only;Database altered.SQL> select * from test; ID NAME---------- -------------------- 0 test 01 1 test 02SQL> Alter database recover managed standby database disconnect from session;Database altered.SQL> quitDisconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - ProductionWith the Partitioning, OLAP and Data Mining options
?
?
standy日志应用状态
Mon Aug 19 09:13:58 2013Errors in file /u01/app/oracle/admin/dg01/bdump/dg01_mrp0_2975.trc:ORA-16037: user requested cancel of managed recovery operationMon Aug 19 09:13:58 2013MRP0: Background Media Recovery process shutdown (dg01)Mon Aug 19 09:13:58 2013Managed Standby Recovery Canceled (dg01)Mon Aug 19 09:13:58 2013Completed: alter database recover managed standby database cancelMon Aug 19 09:14:05 2013alter database open read onlyMon Aug 19 09:14:05 2013SMON: enabling cache recoveryMon Aug 19 09:14:06 2013Database Characterset is WE8ISO8859P1**********************************************************WARNING: Files may exists in db_recovery_file_destthat are not known to the database. Use the RMAN commandCATALOG RECOVERY AREA to re-catalog any such files.One of the following events caused this:1. A backup controlfile was restored.2. A standby controlfile was restored.3. The controlfile was re-created.4. db_recovery_file_dest had previously been enabled and then disabled.**********************************************************replication_dependency_tracking turned off (no async multimaster replication found)Physical standby database opened for read only access.Completed: alter database open read onlyMon Aug 19 09:14:36 2013Alter database recover managed standby database disconnect from sessionMon Aug 19 09:14:36 2013Stopping background process CJQ0Mon Aug 19 09:14:36 2013Stopping Job queue slave processesMon Aug 19 09:14:36 2013Job queue slave processes stoppedWaiting for dispatcher 'D000' to shutdownAll dispatchers and shared servers shutdownMon Aug 19 09:14:38 2013SMON: disabling cache recoveryMon Aug 19 09:14:38 2013Attempt to start background Managed Standby Recovery process (dg01)MRP0 started with pid=10, OS id=4602Mon Aug 19 09:14:38 2013MRP0: Background Managed Standby Recovery process started (dg01)Managed Standby Recovery not using Real Time ApplyClearing online redo logfile 1 /u01/app/oracle/oradata/dg01/redo01.logClearing online log 1 of thread 1 sequence number 5Mon Aug 19 09:14:43 2013Clearing online redo logfile 1 completeMedia Recovery Waiting for thread 1 sequence 6Mon Aug 19 09:14:44 2013Completed: Alter database recover managed standby database disconnect from session
?
至此恢复至原始同步状态。
?
$ sqlplus /nologSQL*Plus: Release 10.2.0.1.0 - Production on Mon Aug 19 09:27:51 2013Copyright (c) 1982, 2005, Oracle. All rights reserved.SQL> conn /as sysdbaConnected.SQL> select switchover_status from v$database; SWITCHOVER_STATUS----------------------------------------SESSIONS ACTIVESQL> shutdown immediate;Database closed.Database dismounted.ORACLE instance shut down.SQL> startup mount;ORA-32004: obsolete and/or deprecated parameter(s) specifiedORACLE instance started.Total System Global Area 285212672 bytesFixed Size 1218968 bytesVariable Size 88082024 bytesDatabase Buffers 188743680 bytesRedo Buffers 7168000 bytesDatabase mounted.SQL> select switchover_status from v$database;SWITCHOVER_STATUS----------------------------------------TO STANDBY
?
SQL> alter database open;Database altered.SQL> alter database commit to switchover to physical standby with session shutdown;Database altered.SQL> shutdown immediate ORA-01507: database not mountedORACLE instance shut down.SQL> startup mountORA-32004: obsolete and/or deprecated parameter(s) specifiedORACLE instance started.Total System Global Area 285212672 bytesFixed Size 1218968 bytesVariable Size 92276328 bytesDatabase Buffers 184549376 bytesRedo Buffers 7168000 bytesDatabase mounted.
?
select switchover_status from v$database;
Starting background process EMN0EMN0 started with pid=21, OS id=30343Mon Aug 19 09:28:02 2013Shutting down instance: further logons disabledMon Aug 19 09:28:02 2013Stopping background process QMNCMon Aug 19 09:28:02 2013Stopping background process CJQ0Mon Aug 19 09:28:04 2013Stopping background process MMNLMon Aug 19 09:28:05 2013Stopping background process MMONMon Aug 19 09:28:06 2013Shutting down instance (immediate)License high water mark = 11Mon Aug 19 09:28:06 2013Stopping Job queue slave processesMon Aug 19 09:28:06 2013Job queue slave processes stoppedAll dispatchers and shared servers shutdownMon Aug 19 09:28:17 2013PMON failed to acquire latch, see PMON dumpPMON failed to acquire latch, see PMON dumpPMON failed to acquire latch, see PMON dumpPMON failed to acquire latch, see PMON dumpPMON failed to acquire latch, see PMON dumpMon Aug 19 09:28:29 2013PMON failed to acquire latch, see PMON dumpPMON failed to acquire latch, see PMON dumpMon Aug 19 09:28:33 2013ALTER DATABASE CLOSE NORMALMon Aug 19 09:28:34 2013SMON: disabling tx recoverySMON: disabling cache recoveryMon Aug 19 09:28:34 2013Shutting down archive processesArchiving is disabledMon Aug 19 09:28:39 2013ARCH shutting downARC1: Archival stoppedMon Aug 19 09:28:44 2013ARC0: Becoming the heartbeat ARCHARC0: Archiving disabledARCH shutting downARC0: Archival stoppedMon Aug 19 09:28:45 2013Thread 1 closed at log sequence 6Successful close of redo thread 1Mon Aug 19 09:28:45 2013Completed: ALTER DATABASE CLOSE NORMALMon Aug 19 09:28:45 2013ALTER DATABASE DISMOUNTCompleted: ALTER DATABASE DISMOUNTARCH: Archival disabled due to shutdown: 1089Shutting down archive processesArchiving is disabledArchive process shutdown avoided: 0 activeARCH: Archival disabled due to shutdown: 1089Shutting down archive processesArchiving is disabledArchive process shutdown avoided: 0 activeMon Aug 19 09:28:52 2013Starting ORACLE instance (normal)LICENSE_MAX_SESSION = 0LICENSE_SESSIONS_WARNING = 0Picked latch-free SCN scheme 2Autotune of undo retention is turned on. IMODE=BRILAT =18LICENSE_MAX_USERS = 0SYS auditing is disabledksdpec: called for event 13740 prior to event group initializationStarting up ORACLE RDBMS Version: 10.2.0.1.0.System parameters with non-default values: processes = 150 __shared_pool_size = 79691776 __large_pool_size = 4194304 __java_pool_size = 4194304 __streams_pool_size = 0 sga_target = 285212672 control_files = /u01/app/oracle/oradata/dg01/control01.ctl, /u01/app/oracle/oradata/dg01/control02.ctl, /u01/app/oracle/oradata/dg01/control03.ctl db_block_size = 8192 __db_cache_size = 188743680 compatible = 10.2.0.1.0 log_archive_start = TRUE log_archive_dest_1 = LOCATION=/u01/app/oracle/oradata/archive log_archive_dest_2 = SERVICE=dg02 log_archive_dest_state_1 = enable log_archive_dest_state_2 = enable log_archive_max_processes= 2 log_archive_format = %t_%s_%r.arc fal_client = dg01 fal_server = dg02 db_file_multiblock_read_count= 16 db_recovery_file_dest = /u01/app/oracle/flash_recovery_area db_recovery_file_dest_size= 2147483648 undo_management = AUTO undo_tablespace = UNDOTBS1 remote_login_passwordfile= EXCLUSIVE db_domain = dispatchers = (PROTOCOL=TCP) (SERVICE=dg01XDB) job_queue_processes = 10 background_dump_dest = /u01/app/oracle/admin/dg01/bdump user_dump_dest = /u01/app/oracle/admin/dg01/udump core_dump_dest = /u01/app/oracle/admin/dg01/cdump audit_file_dest = /u01/app/oracle/admin/dg01/adump db_name = dg01 db_unique_name = dg01 open_cursors = 300 pga_aggregate_target = 94371840Deprecated system parameters with specified values: log_archive_start End of deprecated system parameter listingPMON started with pid=2, OS id=30389PSP0 started with pid=3, OS id=30391MMAN started with pid=4, OS id=30393DBW0 started with pid=5, OS id=30395LGWR started with pid=6, OS id=30397CKPT started with pid=7, OS id=30399SMON started with pid=8, OS id=30401RECO started with pid=9, OS id=30403CJQ0 started with pid=10, OS id=30405MMON started with pid=11, OS id=30407MMNL started with pid=12, OS id=30409Mon Aug 19 09:28:52 2013starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...starting up 1 shared server(s) ...Mon Aug 19 09:28:52 2013ALTER DATABASE MOUNTMon Aug 19 09:28:56 2013Setting recovery target incarnation to 2Mon Aug 19 09:28:56 2013Successful mount of redo thread 1, with mount id 459322772Mon Aug 19 09:28:56 2013Database mounted in Exclusive ModeCompleted: ALTER DATABASE MOUNTMon Aug 19 09:29:06 2013alter database openMon Aug 19 09:29:06 2013LGWR: STARTING ARCH PROCESSESARC0 started with pid=16, OS id=30445Mon Aug 19 09:29:06 2013ARC0: Archival startedARC1: Archival startedLGWR: STARTING ARCH PROCESSES COMPLETEARC1 started with pid=17, OS id=30449Mon Aug 19 09:29:06 2013Thread 1 opened at log sequence 6 Current log# 2 seq# 6 mem# 0: /u01/app/oracle/oradata/dg01/redo02.logSuccessful open of redo thread 1Mon Aug 19 09:29:06 2013ARC0: STARTING ARCH PROCESSESMon Aug 19 09:29:06 2013MTTR advisory is disabled because FAST_START_MTTR_TARGET is not setMon Aug 19 09:29:06 2013SMON: enabling cache recoveryMon Aug 19 09:29:06 2013ARC1: Becoming the 'no FAL' ARCHARC1: Becoming the 'no SRL' ARCHARC2 started with pid=18, OS id=30459Mon Aug 19 09:29:06 2013ARC2: Archival startedARC0: STARTING ARCH PROCESSES COMPLETEARC0: Becoming the heartbeat ARCHMon Aug 19 09:29:06 2013Successfully onlined Undo Tablespace 1.Mon Aug 19 09:29:06 2013SMON: enabling tx recoveryMon Aug 19 09:29:07 2013Database Characterset is WE8ISO8859P1replication_dependency_tracking turned off (no async multimaster replication found)Starting background process QMNCQMNC started with pid=19, OS id=30463Mon Aug 19 09:29:07 2013db_recovery_file_dest_size of 2048 MB is 0.00% used. This is auser-specified limit on the amount of space that will be used by thisdatabase for recovery-related files, and does not reflect the amount ofspace available in the underlying filesystem or ASM diskgroup.Mon Aug 19 09:29:08 2013Completed: alter database openMon Aug 19 09:29:17 2013 alter database commit to switchover to physical standby with session shutdownMon Aug 19 09:29:17 2013ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY (dg01)Mon Aug 19 09:30:07 2013SMON: disabling tx recoveryMon Aug 19 09:30:07 2013Stopping background process CJQ0Mon Aug 19 09:30:07 2013Stopping background process QMNCMon Aug 19 09:30:09 2013Stopping Job queue slave processesMon Aug 19 09:30:09 2013Job queue slave processes stoppedWaiting for dispatcher 'D000' to shutdownAll dispatchers and shared servers shutdownActive process 30529 user 'oracle' program 'oracledg01@dg01'Active process 30481 user 'oracle' program 'oracledg01@dg01'Active process 30479 user 'oracle' program 'oracledg01@dg01'Active process 30475 user 'oracle' program 'oracledg01@dg01'Active process 30473 user 'oracle' program 'oracledg01@dg01'Active process 30477 user 'oracle' program 'oracledg01@dg01'Active process 30508 user 'oracle' program 'oracledg01@dg01'Active process 30467 user 'oracle' program 'oracledg01@dg01'Active process 30485 user 'oracle' program 'oracledg01@dg01'CLOSE: waiting for server sessions to complete.Mon Aug 19 09:30:15 2013PMON failed to acquire latch, see PMON dumpPMON failed to acquire latch, see PMON dumpPMON failed to acquire latch, see PMON dumpPMON failed to acquire latch, see PMON dumpPMON failed to acquire latch, see PMON dumpMon Aug 19 09:30:27 2013PMON failed to acquire latch, see PMON dumpPMON failed to acquire latch, see PMON dumpPMON failed to acquire latch, see PMON dumpPMON failed to acquire latch, see PMON dumpMon Aug 19 09:30:36 2013CLOSE: all sessions shutdown successfully.Mon Aug 19 09:30:36 2013SMON: disabling cache recoveryMon Aug 19 09:30:36 2013Shutting down archive processesArchiving is disabledMon Aug 19 09:30:41 2013ARCH shutting downARC2: Archival stoppedMon Aug 19 09:30:46 2013ARCH shutting downARC1: Archival stoppedMon Aug 19 09:30:51 2013ARCH shutting downARC0: Archival stoppedMon Aug 19 09:30:52 2013Thread 1 closed at log sequence 6Successful close of redo thread 1Mon Aug 19 09:30:52 2013ARCH: Noswitch archival of thread 1, sequence 6ARCH: End-Of-Redo Branch archival of thread 1 sequence 6ARCH: Archiving is disabled due to current logfile archivalClearing standby activation ID 459262087 (0x1b5fc887)The primary database controlfile was created using the'MAXLOGFILES 16' clause.There is space for up to 13 standby redo logfilesUse the following SQL commands on the standby database to createstandby redo logfiles that match the primary database:ALTER DATABASE ADD STANDBY LOGFILE 'srl1.f' SIZE 52428800;ALTER DATABASE ADD STANDBY LOGFILE 'srl2.f' SIZE 52428800;ALTER DATABASE ADD STANDBY LOGFILE 'srl3.f' SIZE 52428800;ALTER DATABASE ADD STANDBY LOGFILE 'srl4.f' SIZE 52428800;Archivelog for thread 1 sequence 6 required for standby recoveryMRP0 started with pid=10, OS id=30591Mon Aug 19 09:30:53 2013MRP0: Background Managed Standby Recovery process started (dg01)Managed Standby Recovery not using Real Time ApplyOnline logfile pre-clearing operation disabled by switchoverMedia Recovery Log /u01/app/oracle/oradata/archive/1_6_823812298.arcIdentified End-Of-Redo for thread 1 sequence 6Mon Aug 19 09:30:58 2013Media Recovery End-Of-Redo indicator encounteredMon Aug 19 09:30:58 2013Media Recovery Applied until change 528858Mon Aug 19 09:30:58 2013MRP0: Media Recovery Complete: End-Of-REDO (dg01)Resetting standby activation ID 459262087 (0x1b5fc887)Mon Aug 19 09:30:59 2013Waiting for MRP0 pid 30591 to terminateWaiting for MRP0 pid 30591 to terminateMon Aug 19 09:31:01 2013MRP0: Background Media Recovery process shutdown (dg01)Mon Aug 19 09:31:01 2013idle dispatcher 'D000' terminated, pid = (13, 1)Mon Aug 19 09:31:01 2013Switchover: Complete - Database shutdown required (dg01)Mon Aug 19 09:31:01 2013Completed: alter database commit to switchover to physical standby with session shutdownShutting down instance: further logons disabledMon Aug 19 09:31:05 2013Stopping background process MMNLMon Aug 19 09:31:06 2013Stopping background process MMONMon Aug 19 09:31:07 2013Shutting down instance (immediate)License high water mark = 11Waiting for dispatcher 'D000' to shutdownAll dispatchers and shared servers shutdownMon Aug 19 09:31:09 2013ALTER DATABASE CLOSE NORMALORA-1507 signalled during: ALTER DATABASE CLOSE NORMAL...ARCH: Archival disabled due to shutdown: 1089Shutting down archive processesArchiving is disabledArchive process shutdown avoided: 0 activeARCH: Archival disabled due to shutdown: 1089Shutting down archive processesArchiving is disabledArchive process shutdown avoided: 0 activeMon Aug 19 09:31:16 2013Starting ORACLE instance (normal)LICENSE_MAX_SESSION = 0LICENSE_SESSIONS_WARNING = 0Picked latch-free SCN scheme 2Autotune of undo retention is turned on. IMODE=BRILAT =18LICENSE_MAX_USERS = 0SYS auditing is disabledksdpec: called for event 13740 prior to event group initializationStarting up ORACLE RDBMS Version: 10.2.0.1.0.System parameters with non-default values: processes = 150 __shared_pool_size = 83886080 __large_pool_size = 4194304 __java_pool_size = 4194304 __streams_pool_size = 0 sga_target = 285212672 control_files = /u01/app/oracle/oradata/dg01/control01.ctl, /u01/app/oracle/oradata/dg01/control02.ctl, /u01/app/oracle/oradata/dg01/control03.ctl db_block_size = 8192 __db_cache_size = 184549376 compatible = 10.2.0.1.0 log_archive_start = TRUE log_archive_dest_1 = LOCATION=/u01/app/oracle/oradata/archive log_archive_dest_2 = SERVICE=dg02 log_archive_dest_state_1 = enable log_archive_dest_state_2 = enable log_archive_max_processes= 2 log_archive_format = %t_%s_%r.arc fal_client = dg01 fal_server = dg02 db_file_multiblock_read_count= 16 db_recovery_file_dest = /u01/app/oracle/flash_recovery_area db_recovery_file_dest_size= 2147483648 undo_management = AUTO undo_tablespace = UNDOTBS1 remote_login_passwordfile= EXCLUSIVE db_domain = dispatchers = (PROTOCOL=TCP) (SERVICE=dg01XDB) job_queue_processes = 10 background_dump_dest = /u01/app/oracle/admin/dg01/bdump user_dump_dest = /u01/app/oracle/admin/dg01/udump core_dump_dest = /u01/app/oracle/admin/dg01/cdump audit_file_dest = /u01/app/oracle/admin/dg01/adump db_name = dg01 db_unique_name = dg01 open_cursors = 300 pga_aggregate_target = 94371840Deprecated system parameters with specified values: log_archive_start End of deprecated system parameter listingPMON started with pid=2, OS id=30621PSP0 started with pid=3, OS id=30623MMAN started with pid=4, OS id=30625DBW0 started with pid=5, OS id=30627LGWR started with pid=6, OS id=30629CKPT started with pid=7, OS id=30631SMON started with pid=8, OS id=30633RECO started with pid=9, OS id=30635CJQ0 started with pid=10, OS id=30637MMON started with pid=11, OS id=30639MMNL started with pid=12, OS id=30641Mon Aug 19 09:31:16 2013starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...starting up 1 shared server(s) ...Mon Aug 19 09:31:16 2013ALTER DATABASE MOUNTMon Aug 19 09:31:20 2013Setting recovery target incarnation to 2ARCH: STARTING ARCH PROCESSESARC0 started with pid=16, OS id=30650Mon Aug 19 09:31:20 2013ARC0: Archival startedARC1: Archival startedARCH: STARTING ARCH PROCESSES COMPLETEMon Aug 19 09:31:20 2013ARC0: Becoming the 'no FAL' ARCHARC0: Becoming the 'no SRL' ARCHARC0: Thread not mountedARC1 started with pid=17, OS id=30652ARC1: Becoming the heartbeat ARCHARC1: Thread not mountedMon Aug 19 09:31:20 2013Successful mount of redo thread 1, with mount id 459281700Mon Aug 19 09:31:20 2013Physical Standby Database mounted.Completed: ALTER DATABASE MOUNT
?
?
登录standby切换角色
$ sqlplus /nologSQL*Plus: Release 10.2.0.1.0 - Production on Mon Aug 19 09:40:49 2013Copyright (c) 1982, 2005, Oracle. All rights reserved.SQL> conn /as sysdbaConnected.SQL> select switchover_status from v$database;SWITCHOVER_STATUS----------------------------------------TO PRIMARYSQL> alter database commit to switchover to primary; Database altered.
?
?
select switchover_status from v$database;
此时待转换standby数据库switchover_status列值应该是"TO_PRIMARY"
?
standby日志状态
Mon Aug 19 09:41:03 2013alter database commit to switchover to primaryMon Aug 19 09:41:03 2013ALTER DATABASE SWITCHOVER TO PRIMARY (dg01)Mon Aug 19 09:41:03 2013If media recovery active, switchover will wait 900 secondsSwitchOver after complete recovery through change 528858Online log /u01/app/oracle/oradata/dg01/redo02.log: Thread 1 Group 2 was previously clearedStandby became primary SCN: 528856Mon Aug 19 09:41:08 2013Switchover: Complete - Database shutdown required (dg01)Completed: alter database commit to switchover to primaryMon Aug 19 09:41:14 2013ARC1: Archival disabled due to instance shutdownShutting down archive processesArchiving is disabledMon Aug 19 09:41:21 2013RFS[4]: Archival disabled due to shutdown: 16098Shutting down archive processesArchiving is disabledMon Aug 19 09:41:24 2013ARCH shutting downMon Aug 19 09:41:24 2013Archive process shutdown avoided: 0 activeMon Aug 19 09:41:24 2013ARC0: Archival stoppedMon Aug 19 09:41:24 2013RFS[4]: Archival disabled due to shutdown: 16098Shutting down archive processesArchiving is disabledArchive process shutdown avoided: 0 activeMon Aug 19 09:41:29 2013ARCH shutting downARC1: Archival stopped
?
登录查看角色是否转化成功
?
登录primary
SQL> select name,database_role from v$database;NAME DATABASE_ROLE------------------ --------------------------------DG01 PHYSICAL STANDBY
?
?
登录standby
SQL> select name,database_role from v$database;NAME DATABASE_ROLE------------------ --------------------------------DG01 PRIMARY
?
?
使用alter system switch logfile查看传输状态
?
standby
Mon Aug 19 09:58:12 2013Thread 1 advanced to log sequence 8 Current log# 1 seq# 8 mem# 0: /u01/app/oracle/oradata/dg01/redo01.logThread 1 advanced to log sequence 9 Current log# 3 seq# 9 mem# 0: /u01/app/oracle/oradata/dg01/redo03.log
?
primary
RFS[2]: Assigned to RFS process 32645RFS[2]: Identified database type as 'physical standby'RFS[2]: No standby redo logfiles createdRFS[2]: Archived Log: '/u01/app/oracle/oradata/archive/1_7_823812298.arc'RFS[2]: No standby redo logfiles createdRFS[2]: Archived Log: '/u01/app/oracle/oradata/archive/1_8_823812298.arc'
?
?
?