查询主库的状态:
SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;
SWITCHOVER_STATUS
--------------------
TO STANDBY
查询物理备库的状态:
SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;
SWITCHOVER_STATUS
--------------------
NOT ALLOWED
主库转化为物理备库:
SQL> alter database commit to switchover to physical standby with session shutdown;
Database altered.
相应的告警日志内容为:
Tue Apr 15 21:36:18 2014
alter database commit to switchover to physical standby with session shutdown
ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY [Process Id: 4752] (dbpri)
Tue Apr 15 21:36:22 2014
Thread 1 advanced to log sequence 64 (LGWR switch)
Current log# 1 seq# 64 mem# 0: /opt/ora10g/oradata/dbpri/redo01.log
Tue Apr 15 21:36:25 2014
Archived Log entry 178 added for thread 1 sequence 63 ID 0xea3ea9c1 dest 1:
Waiting for all non-current ORLs to be archived...
All non-current ORLs have been archived.
Waiting for all FAL entries to be archived...
All FAL entries have been archived.
Waiting for potential switchover target to become synchronized...
Tue Apr 15 21:36:55 2014
Active, synchronized Physical Standby switchover target has been identified
Tue Apr 15 21:36:55 2014
Thread 1 cannot allocate new log, sequence 65
Checkpoint not complete
Current log# 1 seq# 64 mem# 0: /opt/ora10g/oradata/dbpri/redo01.log
Thread 1 advanced to log sequence 65 (LGWR switch)
Current log# 2 seq# 65 mem# 0: /opt/ora10g/oradata/dbpri/redo02.log
Tue Apr 15 21:37:19 2014
Archived Log entry 179 added for thread 1 sequence 64 ID 0xea3ea9c1 dest 1:
Tue Apr 15 21:37:19 2014
Stopping background process CJQ0
Tue Apr 15 21:37:20 2014
SMON: disabling tx recovery
Stopping background process QMNC
All dispatchers and shared servers shutdown
CLOSE: killing server sessions.
CLOSE: all sessions shutdown successfully.
SMON: disabling cache recovery
Tue Apr 15 21:37:22 2014
Shutting down archive processes
Archiving is disabled
Tue Apr 15 21:37:22 2014
ARCH shutting down
ARC3: Archival stopped
Tue Apr 15 21:37:22 2014
ARCH shutting down
ARC2: Archival stopped
Tue Apr 15 21:37:22 2014
ARCH shutting down
ARC1: Archival stopped
Tue Apr 15 21:37:22 2014
ARCH shutting down
ARC0: Archival stopped
Thread 1 closed at log sequence 65
Successful close of redo thread 1
ARCH: Noswitch archival of thread 1, sequence 65
ARCH: End-Of-Redo Branch archival of thread 1 sequence 65
Tue Apr 15 21:37:46 2014
Archived Log entry 182 added for thread 1 sequence 65 ID 0xea3ea9c1 dest 1:
ARCH: Archiving is disabled due to current logfile archival
Primary will check for some target standby to have received all redo
Final check for a synchronized target standby. Check will be made once.
Tue Apr 15 21:37:57 2014
LOG_ARCHIVE_DEST_2 is a potential Physical Standby switchover target
LOG_ARCHIVE_DEST_3 is not a Physical Standby
Active, synchronized target has been identified
Target has also applied all redo
Backup controlfile written to trace file /opt/ora10g/diag/rdbms/dbpri/dbpri/trace/dbpri_ora_4752.trc
Clearing standby activation ID 3929975233 (0xea3ea9c1)
The primary database controlfile was created using the
'MAXLOGFILES 16' clause.
There is space for up to 13 standby redo logfiles
Use the following SQL commands on the standby database to create
standby 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 65 required for standby recovery
Switchover: Primary controlfile converted to standby controlfile succesfully.
Tue Apr 15 21:37:57 2014
MRP0 started with pid=18, OS id=5137
MRP0: Background Managed Standby Recovery process started (dbpri)
Serial Media Recovery started
Managed Standby Recovery not using Real Time Apply
Online logfile pre-clearing operation disabled by switchover
Media Recovery Log /opt/ora10g/archive/1_65_844672005.arc
Identified End-Of-Redo for thread 1 sequence 65
Resetting standby activation ID 0 (0x0)
Media Recovery End-Of-Redo indicator encountered
Media Recovery Applied until change 985511
MRP0: Media Recovery Complete: End-Of-REDO (dbpri)
MRP0: Background Media Recovery process shutdown (dbpri)
Tue Apr 15 21:38:04 2014
idle dispatcher 'D000' terminated, pid = (17, 1)
Switchover: Complete - Database shutdown required (dbpri)
Completed: alter database commit to switchover to physical standby with session shutdown
重启原主库(dbpri):
SQL> shutdown abort
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 740724736 bytes
Fixed Size 1339092 bytes
Variable Size 461373740 bytes
Database Buffers 272629760 bytes
Redo Buffers 5382144 bytes
Database mounted.
SQL> select switchover_status from v$database;
SWITCHOVER_STATUS
--------------------
TO PRIMARY
再次查询物理备库:
SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;
SWITCHOVER_STATUS
--------------------
SWITCHOVER PENDING
这是由于物理备库dbstd没有开启redo 应用。
开启并再次查询:
SQL> alter database recover managed standby database disconnect from session;
Database altered.
SQL> select switchover_status from v$database;
SWITCHOVER_STATUS
--------------------
TO PRIMARY
物理备库dbstd转化为主库:
SQL> alter database commit to switchover to primary with session shutdown;
Database altered.
相应的告警日志为:
alter database commit to switchover to primary with session shutdown
ALTER DATABASE SWITCHOVER TO PRIMARY (dbstd)
Maximum wait for role transition is 15 minutes.
Backup controlfile written to trace file /opt/ora10g/diag/rdbms/dbstd/dbstd/trace/dbstd_ora_4959.trc
SwitchOver after complete recovery through change 985511
Tue Apr 15 20:05:06 2014
Standby became primary SCN: 985509
Switchover: Complete - Database mounted as primary
Tue Apr 15 20:05:06 2014
ARC1: Becoming the 'no SRL' ARCH
Completed: alter database commit to switchover to primary with session shutdown
打开原物理备库dbstd(现在为主库):
SQL> alter database open;
Database altered.
告警日志为:
Tue Apr 15 20:05:44 2014
alter database open
Tue Apr 15 20:05:44 2014
Assigning activation ID 3930278240 (0xea434960)
Thread 1 advanced to log sequence 67 (thread open)
Thread 1 opened at log sequence 67
Current log# 2 seq# 67 mem# 0: /opt/ora10g/oradata/dbpri/redo02.log
Successful open of redo thread 1
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Tue Apr 15 20:05:45 2014
SMON: enabling cache recovery
Tue Apr 15 20:05:46 2014
Archived Log entry 61 added for thread 1 sequence 66 ID 0xea434960 dest 1:
Tue Apr 15 20:05:47 2014
NSA2 started with pid=24, OS id=5061
ARC3: Standby redo logfile selected for thread 1 sequence 66 for destination LOG_ARCHIVE_DEST_2
Thread 1 advanced to log sequence 68 (LGWR switch)
Current log# 3 seq# 68 mem# 0: /opt/ora10g/oradata/dbpri/redo03.log
Tue Apr 15 20:05:50 2014
Archived Log entry 63 added for thread 1 sequence 67 ID 0xea434960 dest 1:
******************************************************************
LGWR: Setting 'active' archival for destination LOG_ARCHIVE_DEST_2
******************************************************************
LNS: Standby redo logfile selected for thread 1 sequence 67 for destination LOG_ARCHIVE_DEST_2
LNS: Standby redo logfile selected for thread 1 sequence 68 for destination LOG_ARCHIVE_DEST_2
Successfully onlined Undo Tablespace 2.
Dictionary check beginning
Dictionary check complete
Verifying file header compatibility for 11g tablespace encryption..
Verifying 11g file header compatibility for tablespace encryption completed
SMON: enabling tx recovery
Database Characterset is AL32UTF8
Tue Apr 15 20:05:55 2014
No Resource Manager plan active
**********************************************************
WARNING: Files may exists in db_recovery_file_dest
that are not known to the database. Use the RMAN command
CATALOG RECOVERY AREA to re-catalog any such files.
If files cannot be cataloged, then manually delete them
using OS command.
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)
Tue Apr 15 20:06:02 2014
db_recovery_file_dest_size of 3852 MB is 0.00% used. This is a
user-specified limit on the amount of space that will be used by this
database for recovery-related files, and does not reflect the amount of
space available in the underlying filesystem or ASM diskgroup.
Starting background process QMNC
Tue Apr 15 20:06:03 2014
QMNC started with pid=25, OS id=5067
LOGSTDBY: Validating controlfile with logical metadata
LOGSTDBY: Validation complete
Tue Apr 15 20:06:09 2014
Completed: alter database open
同时dbpri中告警日志为:
Tue Apr 15 21:46:59 2014
Using STANDBY_ARCHIVE_DEST parameter default value as /opt/ora10g/archive
RFS[1]: Assigned to RFS process 5318
RFS[1]: Identified database type as 'physical standby': Client is ARCH pid 4961
Tue Apr 15 21:46:59 2014
RFS[2]: Assigned to RFS process 5316
RFS[2]: Identified database type as 'physical standby': Client is ARCH pid 4963
RFS[2]: Selected log 4 for thread 1 sequence 66 dbid -364981823 branch 844672005
Tue Apr 15 21:47:01 2014
Archived Log entry 185 added for thread 1 sequence 66 ID 0xea434960 dest 1:
Tue Apr 15 21:47:03 2014
RFS[3]: Assigned to RFS process 5320
RFS[3]: Identified database type as 'physical standby': Client is LGWR ASYNC pid 5061
Primary database is in MAXIMUM PERFORMANCE mode
RFS[3]: Selected log 4 for thread 1 sequence 67 dbid -364981823 branch 844672005
Tue Apr 15 21:47:04 2014
Archived Log entry 186 added for thread 1 sequence 67 ID 0xea434960 dest 1:
RFS[3]: Selected log 4 for thread 1 sequence 68 dbid -364981823 branch 844672005
Tue Apr 15 21:47:15 2014
RFS[4]: Assigned to RFS process 5322
RFS[4]: Identified database type as 'physical standby': Client is ARCH pid 4961
再次查看dbstd的状态:
SQL> select switchover_status from v$database;
SWITCHOVER_STATUS
--------------------
TO STANDBY
当前dbstd的角色为:
SQL> select database_role from v$database;
DATABASE_ROLE
----------------
PRIMARY
当前dbpri的角色为:
SQL> select database_role from v$database;
DATABASE_ROLE
----------------
PHYSICAL STANDBY
SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;
SWITCHOVER_STATUS
--------------------
TO STANDBY
查询物理备库的状态:
SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;
SWITCHOVER_STATUS
--------------------
NOT ALLOWED
主库转化为物理备库:
SQL> alter database commit to switchover to physical standby with session shutdown;
Database altered.
相应的告警日志内容为:
Tue Apr 15 21:36:18 2014
alter database commit to switchover to physical standby with session shutdown
ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY [Process Id: 4752] (dbpri)
Tue Apr 15 21:36:22 2014
Thread 1 advanced to log sequence 64 (LGWR switch)
Current log# 1 seq# 64 mem# 0: /opt/ora10g/oradata/dbpri/redo01.log
Tue Apr 15 21:36:25 2014
Archived Log entry 178 added for thread 1 sequence 63 ID 0xea3ea9c1 dest 1:
Waiting for all non-current ORLs to be archived...
All non-current ORLs have been archived.
Waiting for all FAL entries to be archived...
All FAL entries have been archived.
Waiting for potential switchover target to become synchronized...
Tue Apr 15 21:36:55 2014
Active, synchronized Physical Standby switchover target has been identified
Tue Apr 15 21:36:55 2014
Thread 1 cannot allocate new log, sequence 65
Checkpoint not complete
Current log# 1 seq# 64 mem# 0: /opt/ora10g/oradata/dbpri/redo01.log
Thread 1 advanced to log sequence 65 (LGWR switch)
Current log# 2 seq# 65 mem# 0: /opt/ora10g/oradata/dbpri/redo02.log
Tue Apr 15 21:37:19 2014
Archived Log entry 179 added for thread 1 sequence 64 ID 0xea3ea9c1 dest 1:
Tue Apr 15 21:37:19 2014
Stopping background process CJQ0
Tue Apr 15 21:37:20 2014
SMON: disabling tx recovery
Stopping background process QMNC
All dispatchers and shared servers shutdown
CLOSE: killing server sessions.
CLOSE: all sessions shutdown successfully.
SMON: disabling cache recovery
Tue Apr 15 21:37:22 2014
Shutting down archive processes
Archiving is disabled
Tue Apr 15 21:37:22 2014
ARCH shutting down
ARC3: Archival stopped
Tue Apr 15 21:37:22 2014
ARCH shutting down
ARC2: Archival stopped
Tue Apr 15 21:37:22 2014
ARCH shutting down
ARC1: Archival stopped
Tue Apr 15 21:37:22 2014
ARCH shutting down
ARC0: Archival stopped
Thread 1 closed at log sequence 65
Successful close of redo thread 1
ARCH: Noswitch archival of thread 1, sequence 65
ARCH: End-Of-Redo Branch archival of thread 1 sequence 65
Tue Apr 15 21:37:46 2014
Archived Log entry 182 added for thread 1 sequence 65 ID 0xea3ea9c1 dest 1:
ARCH: Archiving is disabled due to current logfile archival
Primary will check for some target standby to have received all redo
Final check for a synchronized target standby. Check will be made once.
Tue Apr 15 21:37:57 2014
LOG_ARCHIVE_DEST_2 is a potential Physical Standby switchover target
LOG_ARCHIVE_DEST_3 is not a Physical Standby
Active, synchronized target has been identified
Target has also applied all redo
Backup controlfile written to trace file /opt/ora10g/diag/rdbms/dbpri/dbpri/trace/dbpri_ora_4752.trc
Clearing standby activation ID 3929975233 (0xea3ea9c1)
The primary database controlfile was created using the
'MAXLOGFILES 16' clause.
There is space for up to 13 standby redo logfiles
Use the following SQL commands on the standby database to create
standby 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 65 required for standby recovery
Switchover: Primary controlfile converted to standby controlfile succesfully.
Tue Apr 15 21:37:57 2014
MRP0 started with pid=18, OS id=5137
MRP0: Background Managed Standby Recovery process started (dbpri)
Serial Media Recovery started
Managed Standby Recovery not using Real Time Apply
Online logfile pre-clearing operation disabled by switchover
Media Recovery Log /opt/ora10g/archive/1_65_844672005.arc
Identified End-Of-Redo for thread 1 sequence 65
Resetting standby activation ID 0 (0x0)
Media Recovery End-Of-Redo indicator encountered
Media Recovery Applied until change 985511
MRP0: Media Recovery Complete: End-Of-REDO (dbpri)
MRP0: Background Media Recovery process shutdown (dbpri)
Tue Apr 15 21:38:04 2014
idle dispatcher 'D000' terminated, pid = (17, 1)
Switchover: Complete - Database shutdown required (dbpri)
Completed: alter database commit to switchover to physical standby with session shutdown
重启原主库(dbpri):
SQL> shutdown abort
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 740724736 bytes
Fixed Size 1339092 bytes
Variable Size 461373740 bytes
Database Buffers 272629760 bytes
Redo Buffers 5382144 bytes
Database mounted.
SQL> select switchover_status from v$database;
SWITCHOVER_STATUS
--------------------
TO PRIMARY
再次查询物理备库:
SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;
SWITCHOVER_STATUS
--------------------
SWITCHOVER PENDING
这是由于物理备库dbstd没有开启redo 应用。
开启并再次查询:
SQL> alter database recover managed standby database disconnect from session;
Database altered.
SQL> select switchover_status from v$database;
SWITCHOVER_STATUS
--------------------
TO PRIMARY
物理备库dbstd转化为主库:
SQL> alter database commit to switchover to primary with session shutdown;
Database altered.
相应的告警日志为:
alter database commit to switchover to primary with session shutdown
ALTER DATABASE SWITCHOVER TO PRIMARY (dbstd)
Maximum wait for role transition is 15 minutes.
Backup controlfile written to trace file /opt/ora10g/diag/rdbms/dbstd/dbstd/trace/dbstd_ora_4959.trc
SwitchOver after complete recovery through change 985511
Tue Apr 15 20:05:06 2014
Standby became primary SCN: 985509
Switchover: Complete - Database mounted as primary
Tue Apr 15 20:05:06 2014
ARC1: Becoming the 'no SRL' ARCH
Completed: alter database commit to switchover to primary with session shutdown
打开原物理备库dbstd(现在为主库):
SQL> alter database open;
Database altered.
告警日志为:
Tue Apr 15 20:05:44 2014
alter database open
Tue Apr 15 20:05:44 2014
Assigning activation ID 3930278240 (0xea434960)
Thread 1 advanced to log sequence 67 (thread open)
Thread 1 opened at log sequence 67
Current log# 2 seq# 67 mem# 0: /opt/ora10g/oradata/dbpri/redo02.log
Successful open of redo thread 1
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Tue Apr 15 20:05:45 2014
SMON: enabling cache recovery
Tue Apr 15 20:05:46 2014
Archived Log entry 61 added for thread 1 sequence 66 ID 0xea434960 dest 1:
Tue Apr 15 20:05:47 2014
NSA2 started with pid=24, OS id=5061
ARC3: Standby redo logfile selected for thread 1 sequence 66 for destination LOG_ARCHIVE_DEST_2
Thread 1 advanced to log sequence 68 (LGWR switch)
Current log# 3 seq# 68 mem# 0: /opt/ora10g/oradata/dbpri/redo03.log
Tue Apr 15 20:05:50 2014
Archived Log entry 63 added for thread 1 sequence 67 ID 0xea434960 dest 1:
******************************************************************
LGWR: Setting 'active' archival for destination LOG_ARCHIVE_DEST_2
******************************************************************
LNS: Standby redo logfile selected for thread 1 sequence 67 for destination LOG_ARCHIVE_DEST_2
LNS: Standby redo logfile selected for thread 1 sequence 68 for destination LOG_ARCHIVE_DEST_2
Successfully onlined Undo Tablespace 2.
Dictionary check beginning
Dictionary check complete
Verifying file header compatibility for 11g tablespace encryption..
Verifying 11g file header compatibility for tablespace encryption completed
SMON: enabling tx recovery
Database Characterset is AL32UTF8
Tue Apr 15 20:05:55 2014
No Resource Manager plan active
**********************************************************
WARNING: Files may exists in db_recovery_file_dest
that are not known to the database. Use the RMAN command
CATALOG RECOVERY AREA to re-catalog any such files.
If files cannot be cataloged, then manually delete them
using OS command.
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)
Tue Apr 15 20:06:02 2014
db_recovery_file_dest_size of 3852 MB is 0.00% used. This is a
user-specified limit on the amount of space that will be used by this
database for recovery-related files, and does not reflect the amount of
space available in the underlying filesystem or ASM diskgroup.
Starting background process QMNC
Tue Apr 15 20:06:03 2014
QMNC started with pid=25, OS id=5067
LOGSTDBY: Validating controlfile with logical metadata
LOGSTDBY: Validation complete
Tue Apr 15 20:06:09 2014
Completed: alter database open
同时dbpri中告警日志为:
Tue Apr 15 21:46:59 2014
Using STANDBY_ARCHIVE_DEST parameter default value as /opt/ora10g/archive
RFS[1]: Assigned to RFS process 5318
RFS[1]: Identified database type as 'physical standby': Client is ARCH pid 4961
Tue Apr 15 21:46:59 2014
RFS[2]: Assigned to RFS process 5316
RFS[2]: Identified database type as 'physical standby': Client is ARCH pid 4963
RFS[2]: Selected log 4 for thread 1 sequence 66 dbid -364981823 branch 844672005
Tue Apr 15 21:47:01 2014
Archived Log entry 185 added for thread 1 sequence 66 ID 0xea434960 dest 1:
Tue Apr 15 21:47:03 2014
RFS[3]: Assigned to RFS process 5320
RFS[3]: Identified database type as 'physical standby': Client is LGWR ASYNC pid 5061
Primary database is in MAXIMUM PERFORMANCE mode
RFS[3]: Selected log 4 for thread 1 sequence 67 dbid -364981823 branch 844672005
Tue Apr 15 21:47:04 2014
Archived Log entry 186 added for thread 1 sequence 67 ID 0xea434960 dest 1:
RFS[3]: Selected log 4 for thread 1 sequence 68 dbid -364981823 branch 844672005
Tue Apr 15 21:47:15 2014
RFS[4]: Assigned to RFS process 5322
RFS[4]: Identified database type as 'physical standby': Client is ARCH pid 4961
再次查看dbstd的状态:
SQL> select switchover_status from v$database;
SWITCHOVER_STATUS
--------------------
TO STANDBY
当前dbstd的角色为:
SQL> select database_role from v$database;
DATABASE_ROLE
----------------
PRIMARY
当前dbpri的角色为:
SQL> select database_role from v$database;
DATABASE_ROLE
----------------
PHYSICAL STANDBY
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/26451536/viewspace-1142901/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/26451536/viewspace-1142901/