分类目录归档:Data Guard

dataguard配合flashback实现主备任意切换(failover和switchover)

有客户使用本地和公有云通过vpn搭建数据库容灾,其中有一个需求,当本地环境出现问题云端容灾库接管业务,当本地环境恢复之后,本地继续做为主库,云端作为备库.我这里使用oracle dataguard结合flashback模拟实现客户需求(也可以在failover之后通过重新搭建dg实现类似需求,具体需要看客户的实际场景:数据量,带宽,恢复时间,技术能力等)
正常dg同步的主备环境
这里ora11g主机是主库,ora10g主机是备库

--数据库版本
SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE    11.2.0.4.0      Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production
---主库(ora11g)
SQL> select open_mode,database_role,(select HOST_NAME from v$instance) HOST_NAME,flashback_on from v$database;

OPEN_MODE                      DATABASE_ROLE                  HOST_NAME                      FLASHBACK_
------------------------------ ------------------------------ ------------------------------ ----------
READ WRITE                     PRIMARY                        ora11g                         YES

SQL> create  table xff.t_xifenfei as select * from dba_objects;

Table created.

SQL> select count(*) from xff.t_xifenfei;

  COUNT(*)
----------
     86348


---备库(ora10g)
SQL> select open_mode,database_role,(select HOST_NAME from v$instance) HOST_NAME,flashback_on from v$database;

OPEN_MODE                      DATABASE_ROLE                  HOST_NAME                      FLASHBACK_
------------------------------ ------------------------------ ------------------------------ ----------
READ ONLY WITH APPLY           PHYSICAL STANDBY               ora10g                         YES

SQL> select count(*) from xff.t_xifenfei;

  COUNT(*)
----------
     86348

模拟主库(ora11g)故障,直接激活备库(ora10g)
模拟dg备库(ora10g)直接failover操作

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

Database altered.

SQL> ALTER DATABASE ACTIVATE PHYSICAL STANDBY DATABASE;

Database altered.
Sat Mar 24 16:05:40 2017
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL
MRP0: Background Media Recovery cancelled with status 16037
Errors in file /u01/app/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_pr00_13428.trc:
ORA-16037: user requested cancel of managed recovery operation
Managed Standby Recovery not using Real Time Apply
Recovery interrupted!
Recovered data files to a consistent state at change 1470499
Sat Mar 24 16:05:40 2017
MRP0: Background Media Recovery process shutdown (ora11g)
Managed Standby Recovery Canceled (ora11g)
Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL
Sat Mar 24 16:05:50 2017
ALTER DATABASE ACTIVATE PHYSICAL STANDBY DATABASE
ALTER DATABASE ACTIVATE [PHYSICAL] STANDBY DATABASE (ora11g)
All dispatchers and shared servers shutdown
CLOSE: killing server sessions.
CLOSE: all sessions shutdown successfully.
Sat Mar 24 16:05:50 2017
SMON: disabling cache recovery
Killing 3 processes with pids 13372,13393,13388 (all RFS) in order to
    disallow current and future RFS connections. Requested by OS process 13350
Begin: Standby Redo Logfile archival
End: Standby Redo Logfile archival
RESETLOGS after incomplete recovery UNTIL CHANGE 1470499
Archived Log entry 9 added for thread 1 sequence 14 ID 0xfd5a5fc1 dest 1:
Resetting resetlogs activation ID 4250558401 (0xfd5a5fc1)
Online log /u01/app/oracle/oradata/ora11g/redo01.log: Thread 1 Group 1 was previously cleared
Online log /u01/app/oracle/oradata/ora11g/redo02.log: Thread 1 Group 2 was previously cleared
Online log /u01/app/oracle/oradata/ora11g/redo03.log: Thread 1 Group 3 was previously cleared
Standby became primary SCN: 1470497
Sat Mar 24 16:05:52 2017
Setting recovery target incarnation to 4
ACTIVATE STANDBY: Complete - Database mounted as primary
Completed: ALTER DATABASE ACTIVATE PHYSICAL STANDBY DATABASE

模拟新主库(ora10g)业务操作

SQL> select open_mode,database_role,(select HOST_NAME from v$instance) HOST_NAME,flashback_on from v$database;

OPEN_MODE                      DATABASE_ROLE                  HOST_NAME                      FLASHBACK_
------------------------------ ------------------------------ ------------------------------ ----------
MOUNTED                        PRIMARY                        ora10g                         YES

SQL> alter database open;

Database altered.

SQL>  select open_mode,database_role,(select HOST_NAME from v$instance) HOST_NAME,flashback_on from v$database;

OPEN_MODE                      DATABASE_ROLE                  HOST_NAME                      FLASHBACK_
------------------------------ ------------------------------ ------------------------------ ----------
READ WRITE                     PRIMARY                        ora10g                         YES

SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

SQL> /
/

System altered.

SQL> 
System altered.

SQL> 
SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     5
Next log sequence to archive   7
Current log sequence           7
SQL> delete from xff.t_xifenfei;

86348 rows deleted.

SQL> commit;

Commit complete.

SQL> select count(*) from xff.t_xifenfei;

  COUNT(*)
----------
         0

原主库(ora11g)配置为新备库

---原主库(ora10g)
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup mount
ORACLE instance started.

Total System Global Area 3056513024 bytes
Fixed Size                  2257152 bytes
Variable Size             704646912 bytes
Database Buffers         2332033024 bytes
Redo Buffers               17575936 bytes
Database mounted.
SQL> Flashback database to scn 1470490;

Flashback complete.

SQL> select open_mode,database_role,(select HOST_NAME from v$instance) HOST_NAME,flashback_on from v$database;

OPEN_MODE                      DATABASE_ROLE                  HOST_NAME                      FLASHBACK_
------------------------------ ------------------------------ ------------------------------ ----------
MOUNTED                        PRIMARY                        ora11g                         YES

--现在主库(ora10g)
SQL> alter database create standby controlfile as '/tmp/ctl.3';

Database altered.

[oracle@ora10g ~]$ scp /tmp/ctl.3 192.168.222.11:/u01/app/oracle/oradata/ora11g/control01.ctl 
oracle@192.168.222.11's password: 
ctl.3      


--现在备库(ora11g)
SQL> shutdown abort
ORACLE instance shut down.

SQL> startup mount
ORACLE instance started.

Total System Global Area 3056513024 bytes
Fixed Size                  2257152 bytes
Variable Size             704646912 bytes
Database Buffers         2332033024 bytes
Redo Buffers               17575936 bytes
Database mounted.
SQL> col open_mode for a30
SQL> col database_role for a30
SQL> col HOST_NAME for a30
SQL> col flashback_on for a10
SQL> set lines 150
SQL> select open_mode,database_role,(select HOST_NAME from v$instance) HOST_NAME,flashback_on from v$database;

OPEN_MODE                      DATABASE_ROLE                  HOST_NAME                      FLASHBACK_
------------------------------ ------------------------------ ------------------------------ ----------
MOUNTED                        PHYSICAL STANDBY               ora11g                         NO

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT  LOGFILE DISCONNECT FROM SESSION;

Database altered.
Sat Mar 24 16:10:09 2017
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT  LOGFILE DISCONNECT FROM SESSION
Attempt to start background Managed Standby Recovery process (ora11g)
Sat Mar 24 16:10:09 2017
MRP0 started with pid=27, OS id=27086 
MRP0: Background Managed Standby Recovery process started (ora11g)
 started logmerger process
Sat Mar 24 16:10:14 2017
Managed Standby Recovery starting Real Time Apply
Parallel Media Recovery started with 4 slaves
Media Recovery start incarnation depth : 1, target inc# : 4, irscn : 1470499
Waiting for all non-current ORLs to be archived...
All non-current ORLs have been archived.
Clearing online redo logfile 1 /u01/app/oracle/oradata/ora11g/redo01.log
Clearing online log 1 of thread 1 sequence number 7
Clearing online redo logfile 1 complete
Clearing online redo logfile 2 /u01/app/oracle/oradata/ora11g/redo02.log
Clearing online log 2 of thread 1 sequence number 8
Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT  LOGFILE DISCONNECT FROM SESSION
Clearing online redo logfile 2 complete
Clearing online redo logfile 3 /u01/app/oracle/oradata/ora11g/redo03.log
Clearing online log 3 of thread 1 sequence number 9
Clearing online redo logfile 3 complete
Media Recovery Waiting for thread 1 sequence 13 branch(resetlogs_id) 854379205
Sat Mar 24 16:10:16 2017
RFS[3]: Assigned to RFS process 27098
RFS[3]: Opened log for thread 1 sequence 13 dbid 48871109 branch 854379205
Archived Log entry 2 added for thread 1 sequence 13 rlc 854379205 ID 0xfd5a5fc1 dest 2:
Sat Mar 24 16:10:16 2017
RFS[4]: Assigned to RFS process 27100
RFS[4]: Opened log for thread 1 sequence 14 dbid 48871109 branch 854379205
Archived Log entry 3 added for thread 1 sequence 14 rlc 854379205 ID 0xfd5a5fc1 dest 2:
RFS[3]: Opened log for thread 1 sequence 1 dbid 48871109 branch 947798452
RFS[4]: Opened log for thread 1 sequence 3 dbid 48871109 branch 947798452
Sat Mar 24 16:10:16 2017
RFS[5]: Assigned to RFS process 27070
RFS[5]: Opened log for thread 1 sequence 2 dbid 48871109 branch 947798452
Archived Log entry 4 added for thread 1 sequence 1 rlc 947798452 ID 0x2ec1ed0 dest 2:
Archived Log entry 5 added for thread 1 sequence 3 rlc 947798452 ID 0x2ec1ed0 dest 2:
RFS[3]: Opened log for thread 1 sequence 4 dbid 48871109 branch 947798452
RFS[4]: Opened log for thread 1 sequence 5 dbid 48871109 branch 947798452
Archived Log entry 6 added for thread 1 sequence 5 rlc 947798452 ID 0x2ec1ed0 dest 2:
Archived Log entry 7 added for thread 1 sequence 4 rlc 947798452 ID 0x2ec1ed0 dest 2:
Archived Log entry 8 added for thread 1 sequence 2 rlc 947798452 ID 0x2ec1ed0 dest 2:
RFS[4]: Opened log for thread 1 sequence 6 dbid 48871109 branch 947798452
Archived Log entry 9 added for thread 1 sequence 6 rlc 947798452 ID 0x2ec1ed0 dest 2:
RFS[3]: Opened log for thread 1 sequence 7 dbid 48871109 branch 947798452
Archived Log entry 10 added for thread 1 sequence 7 rlc 947798452 ID 0x2ec1ed0 dest 2:
Media Recovery Log /u01/app/oracle/fast_recovery_area/ORA11GDG/archivelog/2017_03_24/o1_mf_1_13_9xmyh8cs_.arc
Media Recovery Log /u01/app/oracle/fast_recovery_area/ORA11GDG/archivelog/2017_03_24/o1_mf_1_14_9xmyh8dr_.arc
Media Recovery Log /u01/app/oracle/fast_recovery_area/ORA11GDG/archivelog/2017_03_24/o1_mf_1_1_9xmyh8fk_.arc
Media Recovery Log /u01/app/oracle/fast_recovery_area/ORA11GDG/archivelog/2017_03_24/o1_mf_1_2_9xmyh8fo_.arc
Media Recovery Log /u01/app/oracle/fast_recovery_area/ORA11GDG/archivelog/2017_03_24/o1_mf_1_3_9xmyh8fm_.arc
Media Recovery Log /u01/app/oracle/fast_recovery_area/ORA11GDG/archivelog/2017_03_24/o1_mf_1_4_9xmyh8g3_.arc
Media Recovery Log /u01/app/oracle/fast_recovery_area/ORA11GDG/archivelog/2017_03_24/o1_mf_1_5_9xmyh8g4_.arc
Media Recovery Log /u01/app/oracle/fast_recovery_area/ORA11GDG/archivelog/2017_03_24/o1_mf_1_6_9xmyh8gs_.arc
Media Recovery Log /u01/app/oracle/fast_recovery_area/ORA11GDG/archivelog/2017_03_24/o1_mf_1_7_9xmyh8hl_.arc
Media Recovery Log /u01/app/oracle/fast_recovery_area/ORA11GDG/archivelog/2017_03_24/o1_mf_1_8_9xmy5d3f_.arc
Media Recovery Waiting for thread 1 sequence 9 (in transit)
Recovery of Online Redo Log: Thread 1 Group 4 Seq 9 Reading mem 0
  Mem# 0: /u01/app/oracle/oradata/ora11g/std_redo10.log
SQL>  ALTER DATABASE RECOVER MANAGED STANDBY DATABASE cancel;

Database altered.

SQL> alter database open;

Database altered.

SQL>  ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT  LOGFILE DISCONNECT FROM SESSION;

Database altered.

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE cancel;

Database altered.

SQL> alter database flashback on;

Database altered.

SQL> select open_mode,database_role,(select HOST_NAME from v$instance) HOST_NAME,flashback_on from v$database;

OPEN_MODE                      DATABASE_ROLE                  HOST_NAME                      FLASHBACK_
------------------------------ ------------------------------ ------------------------------ ----------
READ ONLY                      PHYSICAL STANDBY               ora11g                         YES

SQL> select count(*) from xff.t_xifenfei;

  COUNT(*)
----------
         0

--现在主库
SQL> drop table xff.t_xifenfei ;

Table dropped.

SQL> select count(*) from xff.t_xifenfei;
select count(*) from xff.t_xifenfei
                         *
ERROR at line 1:
ORA-00942: table or view does not exist

--现在备库
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT  LOGFILE DISCONNECT FROM SESSION;

Database altered.

SQL> select open_mode,database_role,(select HOST_NAME from v$instance) HOST_NAME,flashback_on from v$database;

OPEN_MODE                      DATABASE_ROLE                  HOST_NAME                      FLASHBACK_
------------------------------ ------------------------------ ------------------------------ ----------
READ ONLY WITH APPLY           PHYSICAL STANDBY               ora11g                         YES

SQL> select count(*) from xff.t_xifenfei;
select count(*) from xff.t_xifenfei
                         *
ERROR at line 1:
ORA-00942: table or view does not exist

通过上述一系列操作,以前假设故障的主库,现在变成了被failover激活的主库的备库,也就是说数据库主备关系由ora11g主库—>ora10g备库变成了ora10g主库—>ora11g备库

switchover实现主备库互换

---现在主库(ora10g)
SQL>  ALTER DATABASE COMMIT TO SWITCHOVER TO STANDBY WITH SESSION SHUTDOWN;

Database altered.


--现在备库(ora11g)
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN;

Database altered.

SQL>  select open_mode,database_role,(select HOST_NAME from v$instance) HOST_NAME,flashback_on from v$database;

OPEN_MODE                      DATABASE_ROLE                  HOST_NAME                      FLASHBACK_
------------------------------ ------------------------------ ------------------------------ ----------
MOUNTED                        PRIMARY                        ora11g                         YES

SQL> alter database open;

Database altered.

SQL> select open_mode,database_role,(select HOST_NAME from v$instance) HOST_NAME,flashback_on from v$database;

OPEN_MODE                      DATABASE_ROLE                  HOST_NAME                      FLASHBACK_
------------------------------ ------------------------------ ------------------------------ ----------
READ WRITE                     PRIMARY                        ora11g                         YES

--最新备库(ora10g)
SQL> startup
ORACLE instance started.

Total System Global Area 3056513024 bytes
Fixed Size                  2257152 bytes
Variable Size             687869696 bytes
Database Buffers         2348810240 bytes
Redo Buffers               17575936 bytes
Database mounted.
Database opened.
SQL>  ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT  LOGFILE DISCONNECT FROM SESSION;

Database altered.

SQL> select open_mode,database_role,(select HOST_NAME from v$instance) HOST_NAME,flashback_on from v$database;

OPEN_MODE                      DATABASE_ROLE                  HOST_NAME                      FLASHBACK_
------------------------------ ------------------------------ ------------------------------ ----------
READ ONLY WITH APPLY           PHYSICAL STANDBY               ora10g                         YES

--最新主库(ora11g)
SQL> alter system switch logfile;

System altered.

SQL> create table t_xifenfei as select * from dba_objects;

Table created.

SQL> select count(*) from t_xifenfei;

  COUNT(*)
----------
     86347

--最新备库(ora10g)
SQL>  select count(*) from t_xifenfei;

  COUNT(*)
----------
     86347

通过switchover主备库再次互换由ora10g主库—>ora11g备库变成了ora11g主库—>ora10g备库,再次恢复到最初的状态.这个不是唯一的方法,可以通过重建dg,也能够实现类似需求.

发表在 Data Guard | 标签为 , | 2 条评论

Oracle 12c active dataguard switchover

从12.1开始adg的切换发生了一些改变,直接使用alter database switchover to [target standby db_unique_name] verify; alter database switchover to [target standby db_unique_name]; 即可完成切换,以下是一次生产环境的具体操作步骤
主库操作

SQL> select database_role from v$database;

DATABASE_ROLE
----------------
PRIMARY

SQL> alter database switchover to xifenfei verify;

数据库已更改。

--alert日志
Sun Jun 25 09:07:08 2017
diag_adl:SWITCHOVER VERIFY: Send VERIFY request to switchover target xifenfei
diag_adl:SWITCHOVER VERIFY COMPLETE


SQL> alter database switchover to xifenfei;

数据库已更改。

--alert日志
Sun Jun 25 09:07:46 2017
diag_adl:Starting switchover [Process ID: 37024]
Sun Jun 25 09:07:46 2017
diag_adl:ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY [Process Id: 37024] (xifenfei)
diag_adl:Waiting for target standby to receive all redo
Sun Jun 25 09:07:46 2017
diag_adl:Waiting for all non-current ORLs to be archived...
Sun Jun 25 09:07:46 2017
diag_adl:All non-current ORLs have been archived.
Sun Jun 25 09:07:46 2017
diag_adl:Waiting for all FAL entries to be archived...
Sun Jun 25 09:07:46 2017
diag_adl:All FAL entries have been archived.
Sun Jun 25 09:07:46 2017
diag_adl:Waiting for dest_id 2 to become synchronized...
Sun Jun 25 09:07:47 2017
diag_adl:Active, synchronized Physical Standby switchover target has been identified
diag_adl:Preventing updates and queries at the Primary
diag_adl:Generating and shipping final logs to target standby
diag_adl:Switchover End-Of-Redo Log thread 1 sequence 96534 has been fixed
diag_adl:Switchover: Primary highest seen SCN set to 0x3.0x109d7502
diag_adl:ARCH: Noswitch archival of thread 1, sequence 96534
diag_adl:ARCH: End-Of-Redo Branch archival of thread 1 sequence 96534
diag_adl:ARCH: LGWR is scheduled to archive destination LOG_ARCHIVE_DEST_2 after log switch
diag_adl:ARCH: Standby redo logfile selected for thread 1 sequence 96534 for destination LOG_ARCHIVE_DEST_2
diag_adl:ARCH: Archiving is disabled due to current logfile archival
diag_adl:Primary will check for some target standby to have received all redo
diag_adl:Waiting for target standby to apply all redo
diag_adl:Backup controlfile written to trace file
            /u01/app/oracle/diag/rdbms/xifenfeildg/xifenfei/trace/xifenfei_ora_37024.trc
diag_adl:Converting the primary database to a new standby database
diag_adl:Clearing standby activation ID 612004791 (0x247a73b7)
diag_adl:The primary database controlfile was created using the
diag_adl:'MAXLOGFILES 16' clause.
diag_adl:There is space for up to 11 standby redo logfiles
diag_adl:Use the following SQL commands on the standby database to create
diag_adl:standby redo logfiles that match the primary database:
diag_adl:ALTER DATABASE ADD STANDBY LOGFILE 'srl1.f' SIZE 1073741824;
diag_adl:ALTER DATABASE ADD STANDBY LOGFILE 'srl2.f' SIZE 1073741824;
diag_adl:ALTER DATABASE ADD STANDBY LOGFILE 'srl3.f' SIZE 1073741824;
diag_adl:ALTER DATABASE ADD STANDBY LOGFILE 'srl4.f' SIZE 1073741824;
diag_adl:ALTER DATABASE ADD STANDBY LOGFILE 'srl5.f' SIZE 1073741824;
diag_adl:ALTER DATABASE ADD STANDBY LOGFILE 'srl6.f' SIZE 1073741824;
diag_adl:Archivelog for thread 1 sequence 96534 required for standby recovery
diag_adl:Switchover: Primary controlfile converted to standby controlfile succesfully.
diag_adl:Switchover: Complete - Database shutdown required
diag_adl:Sending request(convert to primary database) to switchover target xifenfei
Sun Jun 25 09:07:58 2017
diag_adl:Switchover complete. Database shutdown required
diag_adl:USER (ospid: 37024): terminating the instance
Sun Jun 25 09:07:59 2017
diag_adl:Instance terminated by USER, pid = 37024
diag_adl:Shutting down instance (abort)
diag_adl:License high water mark = 527
Sun Jun 25 09:07:59 2017
Instance shutdown complete

备库alert日志

Sun Jun 25 09:05:54 2017
SWITCHOVER VERIFY BEGIN
SWITCHOVER VERIFY COMPLETE
Sun Jun 25 09:06:35 2017
RFS[107]: Assigned to RFS process (PID:7330)
RFS[107]: Selected log 12 for thread 1 sequence 96534 dbid 588725663 branch 916962073
Sat Jun 24 20:06:35 2017
Archived Log entry 100576 added for thread 1 sequence 96534 ID 0x247a73b7 dest 1:
Sat Jun 24 20:06:35 2017
Resetting standby activation ID 612004791 (0x247a73b7)
Sat Jun 24 20:06:35 2017
Media Recovery End-Of-Redo indicator encountered
Sat Jun 24 20:06:35 2017
Media Recovery Continuing
Media Recovery Waiting for thread 1 sequence 96535
Sun Jun 25 09:06:36 2017
SWITCHOVER: received request 'ALTER DTABASE COMMIT TO SWITCHOVER  TO PRIMARY' from primary database.
Sun Jun 25 09:06:36 2017
ALTER DATABASE SWITCHOVER TO PRIMARY (xifenfei)
Maximum wait for role transition is 15 minutes.
Switchover: Media recovery is still active
Role Change: Canceling MRP - no more redo to apply
Sat Jun 24 20:06:36 2017
MRP0: Background Media Recovery cancelled with status 16037
Sat Jun 24 20:06:36 2017
Errors in file /u01/app/oracle/diag/rdbms/xifenfei/xifenfei/trace/xifenfei_pr00_4590.trc:
ORA-16037: user requested cancel of managed recovery operation
Managed Standby Recovery not using Real Time Apply
Recovery interrupted!
Sat Jun 24 20:06:36 2017
Errors in file /u01/app/oracle/diag/rdbms/xifenfei/xifenfei/trace/xifenfei_pr00_4590.trc:
ORA-16037: user requested cancel of managed recovery operation
Sat Jun 24 20:06:37 2017
MRP0: Background Media Recovery process shutdown (xifenfei)
Sun Jun 25 09:06:38 2017
Role Change: Canceled MRP
Killing 2 processes(PIDS:7328,4704)(all RFS)
      in order to disallow current and future RFS connections.Requested by OS process 7334
Stopping Emon pool
All dispatchers and shared servers shutdown
CLOSE: killing server sessions.
Active process 5428 user 'oracle' program 'oracle@kage7.hk0620.com (TNS V1-V3)'
Active process 5161 user 'oracle' program 'oracle@kage7.hk0620.com'
…………
Active process 5428 user 'oracle' program 'oracle@kage7.hk0620.com (TNS V1-V3)'
Active process 5161 user 'oracle' program 'oracle@kage7.hk0620.com'
Active process 5178 user 'oracle' program 'oracle@kage7.hk0620.com'
CLOSE: all sessions shutdown successfully.
Stopping Emon pool
Sat Jun 24 20:06:43 2017
SMON: disabling cache recovery
Sat Jun 24 20:06:44 2017
Buffer Cache Full DB Caching mode changing from FULL CACHING DISABLED to FULL CACHING ENABLED 
Sun Jun 25 09:06:44 2017
Backup controlfile written to trace file /u01/app/oracle/diag/rdbms/xifenfei/xifenfei/trace/xifenfei_rmi_7334.trc
SwitchOver after complete recovery through change 13163656450
Online logfile pre-clearing operation disabled by switchover
Online log /u01/app/oracle/oradata/xifenfei/redo01n.log: Thread 1 Group 1 was previously cleared
Online log /u01/app/oracle/fast_recovery_area/xifenfei/redo01n.log: Thread 1 Group 1 was previously cleared
Online log /u01/app/oracle/oradata/xifenfei/redo02n.log: Thread 1 Group 2 was previously cleared
Online log /u01/app/oracle/fast_recovery_area/xifenfei/redo02n.log: Thread 1 Group 2 was previously cleared
Online log /u01/app/oracle/oradata/xifenfei/redo03n.log: Thread 1 Group 3 was previously cleared
Online log /u01/app/oracle/fast_recovery_area/xifenfei/redo03n.log: Thread 1 Group 3 was previously cleared
Online log /u01/app/oracle/oradata/xifenfei/redo04n.log: Thread 1 Group 4 was previously cleared
Online log /u01/app/oracle/fast_recovery_area/xifenfei/redo04n.log: Thread 1 Group 4 was previously cleared
Online log /u01/app/oracle/oradata/xifenfei/redo05n.log: Thread 1 Group 5 was previously cleared
Online log /u01/app/oracle/fast_recovery_area/xifenfei/redo05n.log: Thread 1 Group 5 was previously cleared
Standby became primary SCN: 13163656448
Switchover: Complete - Database mounted as primary
SWITCHOVER: completed request from primary database.
Sat Jun 24 20:07:12 2017
ARC0: Becoming the 'no SRL' ARCH

原备库(现主库)操作

SQL> conn / as sysdba
Connected.
SQL>  select database_role,open_mode from v$database;

DATABASE_ROLE    OPEN_MODE
---------------- --------------------
PRIMARY          MOUNTED

SQL> alter database open;

Database altered.

原主库(现备库)操作

[oracle@localhost scripts]$ ss

SQL*Plus: Release 12.1.0.2.0 Production on 星期日 6月 25 09:09:40 2017

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

已连接到空闲例程。

SQL> startup
ORACLE 例程已经启动。

Total System Global Area 8.5899E+10 bytes
Fixed Size                  7654304 bytes
Variable Size            1.2616E+10 bytes
Database Buffers         7.3014E+10 bytes
Redo Buffers              260780032 bytes
数据库装载完毕。
数据库已经打开。
SQL> alter database recover managed standby database disconnect;

数据库已更改。
发表在 Data Guard, ORACLE 12C | 标签为 | 评论关闭

mount数据库也可能有LOCAL=NO的进程

在一次无意中发现mount状态的数据库也有LOCAL=NO的进程,经过分析确定是由于主库连接到备库的nls或者arch进程连接到备库引起的
发现mount库中有LOCAL=NO的进程

[oracle@localhost ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.5.0 - Production on Fri Jul 29 11:59:57 2016

Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select database_role ,open_mode from v$database;

DATABASE_ROLE    OPEN_MODE
---------------- ----------
PHYSICAL STANDBY MOUNTED

SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@localhost ~]$ ps -ef|grep LOCAL
oracle   11394     1  0 Apr27 ?        08:08:41 oracleorcl (LOCAL=NO)
oracle   11398     1  0 Apr27 ?        15:36:29 oracleorcl (LOCAL=NO)
oracle   18854 18752  0 12:00 pts/2    00:00:00 grep LOCAL
[oracle@localhost ~]$ ps -ef|grep pmon
oracle   14374     1  0  2015 ?        00:10:54 ora_pmon_orcl
oracle   18893 18752  0 12:01 pts/2    00:00:00 grep pmon

SQL>  select sid,status,username from v$session where paddr in
   2  (select addr from v$process where spid in(11394,11398));

       SID STATUS   USERNAME
---------- -------- ------------------------------
       510 INACTIVE PUBLIC
       507 INACTIVE PUBLIC

查看备库进程连接

[oracle@localhost ~]$ netstat -natp|grep -E '11394|11398'
(Not all processes could be identified, non-owned process info
 will not be shown, you would have to be root to see it all.)
tcp        0      0 192.168.160.22:1521         192.168.160.23:42783        ESTABLISHED 11394/oracleorcl    
tcp        0      0 192.168.160.22:1521         192.168.160.23:42785        ESTABLISHED 11398/oracleorcl 

主库上查看,确定192.168.160.22是备库

SQL> show parameter log_archive_dest_2;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_2                   string      service=orcl lgwr async valid_
                                                 for=(online_logfiles,primary_r
                                                 ole) db_unique_name=orcl
SQL> !tnsping orcl

TNS Ping Utility for Linux: Version 10.2.0.5.0 - Production on 29-JUL-2016 12:20:01

Copyright (c) 1997,  2010, Oracle.  All rights reserved.

Used parameter files:


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST =192.168.160.22)(PORT = 1521))
 (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl)))
OK (0 msec)

查看主库连接

[oracle@localhost ~]$ netstat -natp|grep "192.168.160.22"
(Not all processes could be identified, non-owned process info
 will not be shown, you would have to be root to see it all.)
tcp        0      0 192.168.160.23:42785        192.168.160.22:1521         ESTABLISHED 12394/ora_arc1_orcl 
tcp        0      0 192.168.160.23:42783        192.168.160.22:1521         ESTABLISHED 12400/ora_lns1_orcl

通过分析确定在mount情况的备库中,会有LOCAL=NO的进程,他们是主库arch和lns进程对应的服务进程

发表在 Data Guard | 评论关闭