ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 自己oracle 实验笔记(physiaclstandby的进程和view,转换)

自己oracle 实验笔记(physiaclstandby的进程和view,转换)

原创 Linux操作系统 作者:fufuh2o 时间:2009-06-26 09:51:54 0 删除 编辑

主要研究几个管理physicalstandby的进程视图


v$managed_standby
此view 对 physical standby 与logical standby 很 重要
主要是监控~重做的应用,及重做的传输服务活动

 

SQL> desc v$managed_standby;
 名称                                      是否为空? 类型
 ----------------------------------------- -------- ----------------------------

 PROCESS                                            VARCHAR2(9)
 PID                                                NUMBER
 STATUS                                             VARCHAR2(12)
 CLIENT_PROCESS                                     VARCHAR2(8)
 CLIENT_PID                                         VARCHAR2(40)
 CLIENT_DBID                                        VARCHAR2(40)
 GROUP#                                             VARCHAR2(40)
 RESETLOG_ID                                        NUMBER
 THREAD#                                            NUMBER
 SEQUENCE#                                          NUMBER
 BLOCK#                                             NUMBER
 BLOCKS                                             NUMBER
 DELAY_MINS                                         NUMBER
 KNOWN_AGENTS                                       NUMBER
 ACTIVE_AGENTS                                      NUMBER

SQL> select process,client_process from v$managed_standby;

PROCESS   CLIENT_P
--------- --------
ARCH      ARCH
ARCH      ARCH
RFS       UNKNOWN
RFS       UNKNOWN
MRP0      N/A

rfs对应的 client_process  为unknown表示用的arch (log_archive_dest_n) lgwr,表示用的LGRW

SQL> show parameter log_archive_dest_2;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_2                   string      service=xhtest valid_for=(onli
                                                 ne_logfiles,primary_role) db_u
                                                 nique_name=xhtest                       现在的 为arch


改下
*.log_archive_dest_2='service=standby1 lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=standby1' 主库的

SQL> select process,client_process from v$managed_standby; 

PROCESS   CLIENT_P
--------- --------
ARCH      ARCH
ARCH      ARCH
RFS       LGWR          显示出来了
RFS       UNKNOWN
MRP0      N/A

 

                                                                                       

SQL> select process,status from v$managed_standby;

PROCESS   STATUS
--------- ------------
ARCH      CONNECTED
ARCH      CONNECTED
RFS       RECEIVING
RFS       RECEIVING
                                  一些重要的进程

RFS:


当开启动physical standby redo应用
SQL> alter database recover managed standby database disconnect from session;

数据库已更改。

SQL> select process,status from v$managed_standby;

PROCESS   STATUS
--------- ------------
ARCH      CONNECTED
ARCH      CONNECTED
RFS       RECEIVING
RFS       RECEIVING
MRP0      WAIT_FOR_LOG                             多了一个MRP0进程


SQL>

 

 

几个重要视图
SQL> desc v$archive_dest
 名称                                      是否为空? 类型
 ----------------------------------------- -------- ---------------------------

 DEST_ID                                            NUMBER
 DEST_NAME                                          VARCHAR2(256)
 STATUS                                             VARCHAR2(9)
 BINDING                                            VARCHAR2(9)
 NAME_SPACE                                         VARCHAR2(7)
 TARGET                                             VARCHAR2(7)
 ARCHIVER                                           VARCHAR2(10)
 SCHEDULE                                           VARCHAR2(8)
 DESTINATION                                        VARCHAR2(256)
 LOG_SEQUENCE                                       NUMBER
 REOPEN_SECS                                        NUMBER
 DELAY_MINS                                         NUMBER
 NET_TIMEOUT                                        NUMBER
 PROCESS                                            VARCHAR2(10)
 REGISTER                                           VARCHAR2(3)
 FAIL_DATE                                          DATE
 FAIL_SEQUENCE                                      NUMBER
 FAIL_BLOCK                                         NUMBER
 FAILURE_COUNT                                      NUMBER
 MAX_FAILURE                                        NUMBER
 ERROR                                              VARCHAR2(256)
 ALTERNATE                                          VARCHAR2(256)
 DEPENDENCY                                         VARCHAR2(256)
 REMOTE_TEMPLATE                                    VARCHAR2(256)
 QUOTA_SIZE                                         NUMBER
 QUOTA_USED                                         NUMBER
 MOUNTID                                            NUMBER
 TRANSMIT_MODE                                      VARCHAR2(12)
 ASYNC_BLOCKS                                       NUMBER
 AFFIRM                                             VARCHAR2(3)
 TYPE                                               VARCHAR2(7)
 VALID_NOW                                          VARCHAR2(16)
 VALID_TYPE                                         VARCHAR2(15)
 VALID_ROLE                                         VARCHAR2(12)
 DB_UNIQUE_NAME                                     VARCHAR2(30)
 VERIFY                                             VARCHAR2(3)
 EXPEDITE                                           VARCHAR2(3)

SQL>

 

v$archive_dest_status

SQL> desc v$archive_dest_status;
 名称                                      是否为空? 类型
 ----------------------------------------- -------- ----------------------------

 DEST_ID                                            NUMBER
 DEST_NAME                                          VARCHAR2(256)
 STATUS                                             VARCHAR2(9)
 TYPE                                               VARCHAR2(14)
 DATABASE_MODE                                      VARCHAR2(15)
 RECOVERY_MODE                                      VARCHAR2(23)
 PROTECTION_MODE                                    VARCHAR2(20)
 DESTINATION                                        VARCHAR2(256)
 STANDBY_LOGFILE_COUNT                              NUMBER
 STANDBY_LOGFILE_ACTIVE                             NUMBER
 ARCHIVED_THREAD#                                   NUMBER
 ARCHIVED_SEQ#                                      NUMBER
 APPLIED_THREAD#                                    NUMBER
 APPLIED_SEQ#                                       NUMBER
 ERROR                                              VARCHAR2(256)
 SRL                                                VARCHAR2(3)
 DB_UNIQUE_NAME                                     VARCHAR2(30)

查看一些重要信息


例在standby上 查询

SQL> select dest_name,status,database_mode,recovery_mode from v$archive_dest_sta
tus;

DEST_NAME            STATUS    DATABASE_MODE   RECOVERY_MODE
-------------------- --------- --------------- -----------------------
LOG_ARCHIVE_DEST_1   VALID     MOUNTED-STANDBY MANAGED REAL TIME APPLY~~~~~~~~~~~~~~~有效的路径
LOG_ARCHIVE_DEST_2   VALID     MOUNTED-STANDBY MANAGED REAL TIME APPLY~~~~~~~~~~~~~~~有效的路径
LOG_ARCHIVE_DEST_3   INACTIVE  MOUNTED-STANDBY MANAGED REAL TIME APPLY
LOG_ARCHIVE_DEST_4   INACTIVE  MOUNTED-STANDBY MANAGED REAL TIME APPLY
LOG_ARCHIVE_DEST_5   INACTIVE  MOUNTED-STANDBY MANAGED REAL TIME APPLY
LOG_ARCHIVE_DEST_6   INACTIVE  MOUNTED-STANDBY MANAGED REAL TIME APPLY
LOG_ARCHIVE_DEST_7   INACTIVE  MOUNTED-STANDBY MANAGED REAL TIME APPLY
LOG_ARCHIVE_DEST_8   INACTIVE  MOUNTED-STANDBY MANAGED REAL TIME APPLY
LOG_ARCHIVE_DEST_9   INACTIVE  MOUNTED-STANDBY MANAGED REAL TIME APPLY
LOG_ARCHIVE_DEST_10  INACTIVE  MOUNTED-STANDBY MANAGED REAL TIME APPLY
STANDBY_ARCHIVE_DEST VALID     MOUNTED-STANDBY MANAGED REAL TIME APPLY~~~~~~~~~~~~~~~有效的路径
里面重要的信息包括 recovery_mode 恢复的模式

MANAGED REAL TIME APPLY 是因为我们开了 实时应用

SQL> alter database recover managed standby database cancel;          关闭
 
数据库已更改。

SQL> select dest_name,status,database_mode,recovery_mode from v$archive_dest_sta
tus;

DEST_NAME            STATUS    DATABASE_MODE   RECOVERY_MODE
-------------------- --------- --------------- -----------------------
LOG_ARCHIVE_DEST_1   VALID     MOUNTED-STANDBY IDLE
LOG_ARCHIVE_DEST_2   VALID     MOUNTED-STANDBY IDLE
LOG_ARCHIVE_DEST_3   INACTIVE  MOUNTED-STANDBY IDLE
LOG_ARCHIVE_DEST_4   INACTIVE  MOUNTED-STANDBY IDLE
LOG_ARCHIVE_DEST_5   INACTIVE  MOUNTED-STANDBY IDLE                                模式为空闲
LOG_ARCHIVE_DEST_6   INACTIVE  MOUNTED-STANDBY IDLE
LOG_ARCHIVE_DEST_7   INACTIVE  MOUNTED-STANDBY IDLE
LOG_ARCHIVE_DEST_8   INACTIVE  MOUNTED-STANDBY IDLE
LOG_ARCHIVE_DEST_9   INACTIVE  MOUNTED-STANDBY IDLE
LOG_ARCHIVE_DEST_10  INACTIVE  MOUNTED-STANDBY IDLE
STANDBY_ARCHIVE_DEST VALID     MOUNTED-STANDBY IDLE

已选择11行。

SQL> alter database recover managed standby database  disconnect from session; 开启应用

数据库已更改。

SQL> select dest_name,status,database_mode,recovery_mode from v$archive_dest_sta
tus;

DEST_NAME            STATUS    DATABASE_MODE   RECOVERY_MODE~~~~~~~~~~~~~~~~~~~~查看恢复方式
-------------------- --------- --------------- -----------------------
LOG_ARCHIVE_DEST_1   VALID     MOUNTED-STANDBY MANAGED
LOG_ARCHIVE_DEST_2   VALID     MOUNTED-STANDBY MANAGED
LOG_ARCHIVE_DEST_3   INACTIVE  MOUNTED-STANDBY MANAGED
LOG_ARCHIVE_DEST_4   INACTIVE  MOUNTED-STANDBY MANAGED                               恢复为管理模式 就是应用standby归档日志
LOG_ARCHIVE_DEST_5   INACTIVE  MOUNTED-STANDBY MANAGED
LOG_ARCHIVE_DEST_6   INACTIVE  MOUNTED-STANDBY MANAGED
LOG_ARCHIVE_DEST_7   INACTIVE  MOUNTED-STANDBY MANAGED
LOG_ARCHIVE_DEST_8   INACTIVE  MOUNTED-STANDBY MANAGED
LOG_ARCHIVE_DEST_9   INACTIVE  MOUNTED-STANDBY MANAGED
LOG_ARCHIVE_DEST_10  INACTIVE  MOUNTED-STANDBY MANAGED
STANDBY_ARCHIVE_DEST VALID     MOUNTED-STANDBY MANAGED

已选择11行。

SQL>

SQL> select protection_mode from v$archive_dest_status;                 查询standby 的保护模式 默认是最高性能

PROTECTION_MODE
--------------------
MAXIMUM PERFORMANCE
MAXIMUM PERFORMANCE
MAXIMUM PERFORMANCE
MAXIMUM PERFORMANCE                                
MAXIMUM PERFORMANCE
MAXIMUM PERFORMANCE
MAXIMUM PERFORMANCE
MAXIMUM PERFORMANCE
MAXIMUM PERFORMANCE
MAXIMUM PERFORMANCE
MAXIMUM PERFORMANCE

已选择11行。

SQL> select protection_mode,applied_seq# from v$archive_dest_status;~~~~~~~~~~~~~~~~~~~查看恢复方式

PROTECTION_MODE      APPLIED_SEQ#
-------------------- ------------
MAXIMUM PERFORMANCE             0
MAXIMUM PERFORMANCE             0
MAXIMUM PERFORMANCE             0
MAXIMUM PERFORMANCE             0
MAXIMUM PERFORMANCE             0
MAXIMUM PERFORMANCE             0                       
MAXIMUM PERFORMANCE             0
MAXIMUM PERFORMANCE             0
MAXIMUM PERFORMANCE             0
MAXIMUM PERFORMANCE             0
MAXIMUM PERFORMANCE           280                       应用到的 日志序号

已选择11行。

SQL> alter database recover managed standby database using current logfile disco
nnect from session;
alter database recover managed standby database using current logfile disconnect                         使用实时应用前 ,要关闭 归档日志的应用
 from session
*
第 1 行出现错误:
ORA-01153: 激活了不兼容的介质恢复


SQL> alter database recover managed standby database cancel; 关闭

数据库已更改。

SQL> alter database recover managed standby database using current logfile disco
nnect from session;

数据库已更改。

SQL> select protection_mode,applied_seq# from v$archive_dest_status;~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

PROTECTION_MODE      APPLIED_SEQ#
-------------------- ------------
MAXIMUM PERFORMANCE             0
MAXIMUM PERFORMANCE             0
MAXIMUM PERFORMANCE             0
MAXIMUM PERFORMANCE             0
MAXIMUM PERFORMANCE             0
MAXIMUM PERFORMANCE             0
MAXIMUM PERFORMANCE             0
MAXIMUM PERFORMANCE             0
MAXIMUM PERFORMANCE             0
MAXIMUM PERFORMANCE             0
MAXIMUM PERFORMANCE           280

已选择11行。

切换时实验

SQL> show parameter log_archive_dest_2

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_2                   string      service=standby1 lgwr sync val
                                                 id_for=(online_logfiles,primar                  实验环境
                                                 y_role) db_unique_name=standby
                                                 1

v$standby_log

SQL> select sequence#,status from v$log;(primary)

 SEQUENCE# STATUS
---------- ----------------
       285 CURRENT
       283 INACTIVE
       284 INACTIVE

SQL>

 

SQL> select group#,sequence#,status,first_change#,last_change#,archived from v$s                   (standby)
tandby_log;

    GROUP#  SEQUENCE# STATUS     FIRST_CHANGE# LAST_CHANGE# ARC                                      2组standbylogfile
---------- ---------- ---------- ------------- ------------ ---
         4        285 ACTIVE           1492038      1492982 YES
         5          0 UNASSIGNED             0            0 YES


SQL> select current_scn from v$database;

CURRENT_SCN
-----------                                                  主库当前SCN(primary)
    1492998
                        我们可以看到 当前SCN 未写进logfile(primary) 所以未在 standbylogfile中

SQL> commit;      提交写进primary online logfile

提交完成。

SQL> select current_scn from v$database;

CURRENT_SCN
-----------
    1493165~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

SQL> select group#,sequence#,status,first_change#,last_change#,archived from v$s
tandby_log;

    GROUP#  SEQUENCE# STATUS     FIRST_CHANGE# LAST_CHANGE# ARC
---------- ---------- ---------- ------------- ------------ ---
         4        285 ACTIVE           1492038      1493230 YES~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~包含进来了 并且包含了最新的SCN 这个SCN虽然不是我们强制commit提交
         5          0 UNASSIGNED             0            0 YES                                的但是lgwr的启动制度是 每3秒自动触发一次

 


SQL> select group#,sequence#,status,first_change#,last_change#,archived from v$s
tandby_log;

    GROUP#  SEQUENCE# STATUS     FIRST_CHANGE# LAST_CHANGE# ARC
---------- ---------- ---------- ------------- ------------ ---
         4        285 ACTIVE           1492038      1493755 YES
         5          0 UNASSIGNED             0            0 YES

SQL> select group#,sequence#,status,first_change#,last_change#,archived from v$s
tandby_log;

    GROUP#  SEQUENCE# STATUS     FIRST_CHANGE# LAST_CHANGE# ARC
---------- ---------- ---------- ------------- ------------ ---
         4        285 ACTIVE           1492038      1493755 YES
         5          0 UNASSIGNED             0            0 YES

SQL> select group#,sequence#,status,first_change#,last_change#,archived from v$s
tandby_log;

    GROUP#  SEQUENCE# STATUS     FIRST_CHANGE# LAST_CHANGE# ARC
---------- ---------- ---------- ------------- ------------ ---
         4        285 ACTIVE           1492038      1493757 YES
         5          0 UNASSIGNED             0            0 YES
                                              主库没做任何操作,大概过了3秒中,主库自动触发LGWR 同时由LSNN给  STANDBY RFS 作用在standbylogfile中
                                              网络延迟之类可能晚几秒

                                                 主库每3秒中自动触发LGWR

 

 

                再查 standby logfile 发现 last_change#列已经包含了刚才最新的SCN,而且是现在最新的SCN 这样 就是跟prmary的 onlinelogfile是同步的(LGWR SYNC)

 

 

LGWR ASYNC

SQL> alter system set log_archive_dest_2='service=standby1 lgwr async valid_for=
(online_logfiles,primary_role) db_unique_name=standby1';

系统已更改。

主库没做任何操作
SQL> select group#,sequence#,status,first_change#,last_change#,archived from v$s
tandby_log;

    GROUP#  SEQUENCE# STATUS     FIRST_CHANGE# LAST_CHANGE# ARC
---------- ---------- ---------- ------------- ------------ ---
         4        285 ACTIVE           1492038      1493899 YES
         5          0 UNASSIGNED             0            0 YES

SQL> select group#,sequence#,status,first_change#,last_change#,archived from v$s
tandby_log;

    GROUP#  SEQUENCE# STATUS     FIRST_CHANGE# LAST_CHANGE# ARC
---------- ---------- ---------- ------------- ------------ ---
         4        285 ACTIVE           1492038      1493899 YES
         5          0 UNASSIGNED             0            0 YES

SQL> select group#,sequence#,status,first_change#,last_change#,archived from v$s
tandby_log;

    GROUP#  SEQUENCE# STATUS     FIRST_CHANGE# LAST_CHANGE# ARC
---------- ---------- ---------- ------------- ------------ ---
         4        285 ACTIVE           1492038      1493899 YES
         5          0 UNASSIGNED             0            0 YES

SQL> select group#,sequence#,status,first_change#,last_change#,archived from v$s
tandby_log;

    GROUP#  SEQUENCE# STATUS     FIRST_CHANGE# LAST_CHANGE# ARC
---------- ---------- ---------- ------------- ------------ ---
         4        285 ACTIVE           1492038      1493901 YES
         5          0 UNASSIGNED             0            0 YES

 

                                                     大概6秒的时间,中间的网络问题会有影响


也没有任何变化与lgwr sync
作用到 STANDBYLOGFILE的速度可能稍微比lgwr sync慢了几秒,由于先写进primary onlinelogfile ,然后由lsnn 给standby rfs作用在standbylogfile中


实时传输速度
主库3秒种自动LGWR 由于要传到STANDBY 到STANDBY时间可能会>3秒
要是async可能会更加多少几秒 因为primary lgwr要先写到 onlinelogfile中,由onlinelogfile 发给lsnn 不象sync一样 lgwr写onlinelogfile 同时 也发给了lsnn


SQL> alter system switch logfile;

系统已更改。

SQL> alter system switch logfile;

系统已更改。

SQL> alter system switch logfile;

系统已更改。


SQL> select group#,sequence#,status,first_change#,last_change#,archived from v$s
tandby_log;

    GROUP#  SEQUENCE# STATUS     FIRST_CHANGE# LAST_CHANGE# ARC
---------- ---------- ---------- ------------- ------------ ---
         4        286 ACTIVE           1494114      1494114 YES
         5          0 UNASSIGNED             0            0 YES

SQL> select group#,sequence#,status,first_change#,last_change#,archived from v$s
tandby_log;

    GROUP#  SEQUENCE# STATUS     FIRST_CHANGE# LAST_CHANGE# ARC
---------- ---------- ---------- ------------- ------------ ---
         4          0 UNASSIGNED             0            0 NO
         5        287 ACTIVE           1494118      1494114 YES

SQL> select group#,sequence#,status,first_change#,last_change#,archived from v$s
tandby_log;

    GROUP#  SEQUENCE# STATUS     FIRST_CHANGE# LAST_CHANGE# ARC
---------- ---------- ---------- ------------- ------------ ---
         4        288 ACTIVE           1494125      1494123 YES
         5          0 UNASSIGNED             0            0 NO

                                                               当主库交换时候可以看出他们在交替使用 每次只有一个似乎active 状态
                                                            

视图v$archived_log

SQL> desc v$archived_log;
 名称                                      是否为空? 类型
 ----------------------------------------- -------- ----------------------------

 RECID                                              NUMBER
 STAMP                                              NUMBER
 NAME                                               VARCHAR2(513)
 DEST_ID                                            NUMBER
 THREAD#                                            NUMBER
 SEQUENCE#                                          NUMBER
 RESETLOGS_CHANGE#                                  NUMBER
 RESETLOGS_TIME                                     DATE
 RESETLOGS_ID                                       NUMBER
 FIRST_CHANGE#                                      NUMBER
 FIRST_TIME                                         DATE
 NEXT_CHANGE#                                       NUMBER
 NEXT_TIME                                          DATE
 BLOCKS                                             NUMBER
 BLOCK_SIZE                                         NUMBER
 CREATOR                                            VARCHAR2(7)
 REGISTRAR                                          VARCHAR2(7)
 STANDBY_DEST                                       VARCHAR2(3)
 ARCHIVED                                           VARCHAR2(3)
 APPLIED                                            VARCHAR2(3)
 DELETED                                            VARCHAR2(3)
 STATUS                                             VARCHAR2(1)
 COMPLETION_TIME                                    DATE
 DICTIONARY_BEGIN                                   VARCHAR2(3)
 DICTIONARY_END                                     VARCHAR2(3)
 END_OF_REDO                                        VARCHAR2(3)
 BACKUP_COUNT                                       NUMBER
 ARCHIVAL_THREAD#                                   NUMBER
 ACTIVATION#                                        NUMBER
 IS_RECOVERY_DEST_FILE                              VARCHAR2(3)
 COMPRESSED                                         VARCHAR2(3)
 FAL                                                VARCHAR2(3)


SQL> select name,creator,applied from v$archived_log;
NAME                                               CREATOR APP
-------------------------------------------------- ------- ---
D:\STANDBYPHYSICAL\ARCHIVE1\ARC00467_0661538341.00 ARCH    NO
1

D:\STANDBYPHYSICAL\ARCHIVE1\ARC00468_0661538341.00 ARCH    NO
1

D:\STANDBYPHYSICAL\ARCHIVE\ARC00469_0661538341.001 LGWR    NO

已选择331行。
name 是归档过来的文件名称路径
applied是否应用~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~`creator比较重要
creator 分好几种
arch: 当执行alter system swtich logfile 时候 表示用后台进程arcn来出来~~~~~~~~~~~~~~~~自动也是arcn
lgwr:是配置了 lgwr由primary 传输过来的 (STAnDBy)
FGRD:前台执行 alter system  archive log current
RMAN:rman中执行sql'alter system switch logfile ',catalog archivelog '/data/dg/arch/1_59.arc' 。
SRMAN:在standby上catalog archivelog '/data/dg/arch/1_59.arc' 。 产生的是srman

 

~~~~~~~~~~~一个关于v$archived_log补充,查询看看那些日志没有传到standby

SQL> SELECT LOCAL.THREAD#, LOCAL.SEQUENCE# FROM
2> (SELECT THREAD#, SEQUENCE# FROM V$ARCHIVED_LOG WHERE DEST_ID=1)~~~~~~~~~~~~~~~~~~~~~~~~~~~~~`在主库上查
LOCAL
3> WHERE LOCAL.SEQUENCE# NOT IN
5> (SELECT SEQUENCE# FROM V$ARCHIVED_LOG WHERE DEST_ID=2 AND
6> THREAD# = LOCAL.THREAD#);

 

SQL> desc v$database;
 名称                                      是否为空? 类型
 ----------------------------------------- -------- ----------------------------

 DBID                                               NUMBER
 NAME                                               VARCHAR2(9)
 CREATED                                            DATE
 RESETLOGS_CHANGE#                                  NUMBER
 RESETLOGS_TIME                                     DATE
 PRIOR_RESETLOGS_CHANGE#                            NUMBER
 PRIOR_RESETLOGS_TIME                               DATE
 LOG_MODE                                           VARCHAR2(12)
 CHECKPOINT_CHANGE#                                 NUMBER
 ARCHIVE_CHANGE#                                    NUMBER
 CONTROLFILE_TYPE                                   VARCHAR2(7)
 CONTROLFILE_CREATED                                DATE
 CONTROLFILE_SEQUENCE#                              NUMBER
 CONTROLFILE_CHANGE#                                NUMBER
 CONTROLFILE_TIME                                   DATE
 OPEN_RESETLOGS                                     VARCHAR2(11)
 VERSION_TIME                                       DATE
 OPEN_MODE                                          VARCHAR2(10)
 PROTECTION_MODE                                    VARCHAR2(20)
 PROTECTION_LEVEL                                   VARCHAR2(20)
 REMOTE_ARCHIVE                                     VARCHAR2(8)
 ACTIVATION#                                        NUMBER
 SWITCHOVER#                                        NUMBER
 DATABASE_ROLE                                      VARCHAR2(16)
 ARCHIVELOG_CHANGE#                                 NUMBER
 ARCHIVELOG_COMPRESSION                             VARCHAR2(8)
 SWITCHOVER_STATUS                                  VARCHAR2(20)
 DATAGUARD_BROKER                                   VARCHAR2(8)
 GUARD_STATUS                                       VARCHAR2(7)
 SUPPLEMENTAL_LOG_DATA_MIN                          VARCHAR2(8)
 SUPPLEMENTAL_LOG_DATA_PK                           VARCHAR2(3)
 SUPPLEMENTAL_LOG_DATA_UI                           VARCHAR2(3)
 FORCE_LOGGING                                      VARCHAR2(3)
 PLATFORM_ID                                        NUMBER
 PLATFORM_NAME                                      VARCHAR2(101)
 RECOVERY_TARGET_INCARNATION#                       NUMBER
 LAST_OPEN_INCARNATION#                             NUMBER
 CURRENT_SCN                                        NUMBER
 FLASHBACK_ON                                       VARCHAR2(3)
 SUPPLEMENTAL_LOG_DATA_FK                           VARCHAR2(3)
 SUPPLEMENTAL_LOG_DATA_ALL                          VARCHAR2(3)
 DB_UNIQUE_NAME                                     VARCHAR2(30)
 STANDBY_BECAME_PRIMARY_SCN                         NUMBER


SQL> select protection_mode,protection_level,switchover_status from v$database;~~~~~~~~~~~~~

PROTECTION_MODE      PROTECTION_LEVEL     SWITCHOVER_STATUS
-------------------- -------------------- --------------------
MAXIMUM PERFORMANCE  MAXIMUM PERFORMANCE  SESSIONS ACTIVE


查看了primary的 模式,primary能否转为standby     swtichover_status  session active表示可以,但要加上WITH SESSION SHUTDOWN
ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY witch session shutdown ;


SQL> select protection_mode,protection_level,switchover_status from v$database;(standby上查)

PROTECTION_MODE      PROTECTION_LEVEL     SWITCHOVER_STATUS
-------------------- -------------------- --------------------
MAXIMUM PERFORMANCE  MAXIMUM PERFORMANCE  NOT ALLOWED~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~是否跟应用没应用完有关系


standby显示为不允许
SQL> select name,creator,applied from v$archived_log;

NAME                                               CREATOR APP
-------------------------------------------------- ------- ---
D:\STANDBYPHYSICAL\ARCHIVE1\ARC00467_0661538341.00 ARCH    NO
1

D:\STANDBYPHYSICAL\ARCHIVE1\ARC00468_0661538341.00 ARCH    NO
1

D:\STANDBYPHYSICAL\ARCHIVE\ARC00469_0661538341.001 LGWR    NO

已选择331行。
SQL> alter database recover managed standby database disconnect from session;

数据库已更改。
SQL> select name,creator,applied from v$archived_log;
NAME                                               CREATOR APP
-------------------------------------------------- ------- ---
D:\STANDBYPHYSICAL\ARCHIVE1\ARC00467_0661538341.00 ARCH    YES
1

D:\STANDBYPHYSICAL\ARCHIVE1\ARC00468_0661538341.00 ARCH    YES
1~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~都应用完了

D:\STANDBYPHYSICAL\ARCHIVE\ARC00469_0661538341.001 LGWR    YES

已选择331行。

SQL> select protection_mode,protection_level,switchover_status from v$database;

PROTECTION_MODE      PROTECTION_LEVEL     SWITCHOVER_STATUS
-------------------- -------------------- --------------------
MAXIMUM PERFORMANCE  MAXIMUM PERFORMANCE  NOT ALLOWED~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~还是不行


SQL> alter database commit to switchover to physical standby with session shutdo
wn ;~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~主库执行这个了~~~~~切换成standby

 

SQL> select protection_mode,protection_level,switchover_status from v$database;

PROTECTION_MODE      PROTECTION_LEVEL     SWITCHOVER_STATUS
-------------------- -------------------- --------------------
MAXIMUM PERFORMANCE  MAXIMUM PERFORMANCE  TO PRIMARY~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~结论只有当pramary先切换成standby,standby的切换状态才显示to primary

 

 


ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;  standby转换成primary

 


现在都转换完了  来验证下结果
SQL> select db_unique_name,database_role,open_mode from v$database;

DB_UNIQUE_NAME                 DATABASE_ROLE    OPEN_MODE
------------------------------ ---------------- ----------
xhtest                         PHYSICAL STANDBY READ ONLY


SQL> select db_unique_name,database_role,open_mode from v$database;

DB_UNIQUE_NAME                 DATABASE_ROLE    OPEN_MODE
------------------------------ ---------------- ----------
standby1                       PRIMARY          READ WRITE


在standby1上执行alter system switch logfile


SQL> select name,creator,applied from v$archived_log;(standby1  上查)
NAME                                               CREATOR APP
-------------------------------------------------- ------- ---
D:\STANDBYPHYSICAL\ARCHIVE1\ARC00472_0661538341.00 ARCH    NO~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~已经变为主库 归档到自己的本地位置
1

xhtest                                             ARCH    NO
SQL> show parameter log_archive_dest_1

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_1                   string      location=D:\standbyphysical\ar~~~~~~~~~~~~~~~~~~~~~~自己的本地位置
                                                 chive1 valid_for=(all_logfiles
                                                 ,all_roles) db_unique_name=sta
                                                 ndby1

 

SQL> select process,client_process,status,sequence# from v$managed_standby;(xhtest上查,现在已经是physiacal standby了)

PROCESS   CLIENT_P STATUS        SEQUENCE#
--------- -------- ------------ ----------
ARCH      ARCH     CONNECTED             0
ARCH      ARCH     CONNECTED             0
RFS       UNKNOWN  RECEIVING             0

SQL> show parameter standby_archive_dest(xhtest上查,现在已经是physiacal standby了)

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
standby_archive_dest                 string      F:\归档备份
SQL>

SQL> show parameter log_archive_dest_2(standby1 也就是现在的主库)

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_2                   string      service=xhtest valid_for=(onli
                                                 ne_logfiles,primary_role) db_u
                                                 nique_name=xhtest


QL> select name,creator,applied from v$archived_log;(xhtest上,也就是现在的physical standby)
F:\归档备份\ARC00472_0661538341.001                ARCH    NO

已选择937行。   很清楚的看到  归档到了standby_archive_dest位置

 

 

 

 


oracle   dataguard的三中保护模式


maximum protection最高保护模式:
要求

要求
redo写进程:LGWR
网络传输:sync
,磁盘i/0 :affirm 默认(noaffirm),
需要standbylogfile
设置为这个后:
当主库primary写redo日志的时候 必须同时写入standby standbylogfile,若故障导致主数据库无法写重做流到至少一个事务一致性备数据库的备重做日志时,全完后事务才会提交,主数据库会关闭。


maximum availability 最高实用性
要求:
redo写进程:LGWR
网络传输 sync
磁盘i/o affirm
需要 standbylogfile
 
设置后:当主库primary写redo日志时候,必须同时写入standby standbylogfile ,完成后事务才可以提交,当有故障导致主库无法写重做流到备用库的standby logfile时候,主库会自动转为‘最大性能’模式继续运行 知道故障消除时候~自动转为 最高可用性~~~~~,不会shutdown 主库

 

 

Maximum performance 最大性能

要求
rodo写进程: lgwr,arcn
网络传输:SYNC ,ASYNC
磁盘i/o affirm ,noaffirm
standbylogfile ,可以要,也可以不要
设置后:primary写redo时候,事务就可以提交,写入也可是不同步的,有故障导致无法写入时候,不会shutdown 主库

实验3种模式

实验环境

先交换过来xhtest为 primary
standby1 为 standby


SQL>  select protection_mode,db_unique_name,open_mode,database_role from v$datab
ase;

PROTECTION_MODE      DB_UNIQUE_NAME                 OPEN_MODE  DATABASE_ROLE
-------------------- ------------------------------ ---------- ----------------
MAXIMUM PERFORMANCE  xhtest                         READ WRITE PRIMARY                       xhtest  primary

 

SQL> select protection_mode,db_unique_name,open_mode,database_role from v$databa
se;

PROTECTION_MODE      DB_UNIQUE_NAME                 OPEN_MODE  DATABASE_ROLE
-------------------- ------------------------------ ---------- ----------------
MAXIMUM PERFORMANCE  standby1                       MOUNTED    PHYSICAL STANDBY


SQL> select process,client_process,status,sequence# from v$managed_standby;(standby) 现在少一个进程

PROCESS   CLIENT_P STATUS        SEQUENCE#
--------- -------- ------------ ----------
ARCH      ARCH     CONNECTED             0
ARCH      ARCH     CONNECTED             0
SQL> show parameter standby_archive_dest(standby)

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
standby_archive_dest                 string      D:\STANDBYPHYSICAL\ARCHIVE

SQL> show parameter log_archive_dest_2~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~(primary)

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_2                   string      service=standby1 lgwr async va
                                                 lid_for=(online_logfiles,prima
                                                 ry_role) db_unique_name=standb
                                                 y1

 

SQL> alter system set log_archive_dest_2='service=standby1 affirm lgwr sync vali
d_for=(online_logfiles,primary_role) db_unique_name=standby1'
  2  ;

系统已更改。
SQL> alter database set standby database to maximize  protectio
alter database set standby database to maximize  protection
*
第 1 行出现错误:
ORA-01126: 数据库必须已装载到此例程并且不在任何例程中打开

SQL> startup force mount;
ORACLE 例程已经启动。

Total System Global Area  171966464 bytes
Fixed Size                   787988 bytes
Variable Size             145750508 bytes
Database Buffers           25165824 bytes
Redo Buffers                 262144 bytes
数据库装载完毕。
SQL> alter database set standby database to maximize  protection;~~~~~~~~~~~~~~~~~~~~~~~~必须在mount下 设置~~~~~~~~~~注意都是在主库设置

数据库已更改。

 

SQL> select protection_mode,db_unique_name,open_mode,database_role from v$databa
se;

PROTECTION_MODE      DB_UNIQUE_NAME                 OPEN_MODE  DATABASE_ROLE
-------------------- ------------------------------ ---------- ----------------
MAXIMUM PROTECTION   xhtest                         MOUNTED    PRIMARY

 

 

SQL> alter database set standby database to maximize  availability;(open 下可以直接)

SQL> select protection_mode,db_unique_name,protection_level,database_role from
$database;

PROTECTION_MODE      DB_UNIQUE_ PROTECTION_LEVEL     DATABASE_ROLE
-------------------- ---------- -------------------- ----------------
MAXIMUM AVAILABILITY xhtest     RESYNCHRONIZATION    PRIMARY~
~~~~~~~~~~~~~~~~~~~~这个时候PROTECTION_LEVE 为RESYNCHRONIZATION       因为 有个归档日志还未传动到standby~,所以为再同步状态

 

SQL> alter system switch logfile;~~~~~~~~~~当主库执行 时候,刚才没传过去的被自动传过去补上了

系统已更改。

SQL> select protection_mode,db_unique_name,protection_level,database_role from v
$database;

PROTECTION_MODE      DB_UNIQUE_ PROTECTION_LEVEL     DATABASE_ROLE
-------------------- ---------- -------------------- ----------------
MAXIMUM AVAILABILITY xhtest     MAXIMUM AVAILABILITY PRIMARY

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~全部传过去了 状态正确

 

当standby,shutdown 后也为再同步状态


SQL> startup mount;
ORACLE 例程已经启动。

Total System Global Area  171966464 bytes
Fixed Size                   787988 bytes
Variable Size             145750508 bytes
Database Buffers           25165824 bytes
Redo Buffers                 262144 bytes
数据库装载完毕。
SQL> select process,client_process,status,sequence# from v$managed_standby;

PROCESS   CLIENT_P STATUS        SEQUENCE#
--------- -------- ------------ ----------
ARCH      ARCH     CONNECTED             0
ARCH      ARCH     CONNECTED             0

SQL>\


SQL> select protection_mode,db_unique_name,protection_level,database_role from v
$database;

PROTECTION_MODE      DB_UNIQUE_ PROTECTION_LEVEL     DATABASE_ROLE
-------------------- ---------- -------------------- ----------------
MAXIMUM AVAILABILITY xhtest     RESYNCHRONIZATION    PRIMARY

SQL> alter system switchi logfile;
alter system switchi logfile
             *
第 1 行出现错误:
ORA-02065: 非法的 ALTER SYSTEM 选项


SQL> alter system switch logfile;

系统已更改。

 

 

~~~~~~~~~~~~~08.08.19日实验


alter database activate standby database~~~~~~~~~~~~~这句的作用
SQL> select db_unique_name ,database_role from v$database;

DB_UNIQUE_NAME                 DATABASE_ROLE
------------------------------ ----------------
standby1                       PHYSICAL STANDBY
SQL> alter database activate standby database;
alter database activate standby database
*
第 1 行出现错误:
ORA-01154: 数据库忙。现在不允许打开, 关闭, 装载和卸载


SQL> select PROCESS,CLIENT_PROCESS,STATUS,SEQUENCE# from v$managed_standby;

PROCESS   CLIENT_P STATUS        SEQUENCE#
--------- -------- ------------ ----------
ARCH      ARCH     CONNECTED             0
ARCH      ARCH     CONNECTED             0
MRP0      N/A      WAIT_FOR_LOG        499
RFS       UNKNOWN  RECEIVING             0
RFS       UNKNOWN  RECEIVING             0


SQL> select recovery_mode,database_mode from v$archive_dest_status;

RECOVERY_MODE           DATABASE_MODE
----------------------- ---------------
MANAGED                 MOUNTED-STANDBY
MANAGED                 MOUNTED-STANDBY
MANAGED                 MOUNTED-STANDBY
MANAGED                 MOUNTED-STANDBY
MANAGED                 MOUNTED-STANDBY
MANAGED                 MOUNTED-STANDBY
MANAGED                 MOUNTED-STANDBY
MANAGED                 MOUNTED-STANDBY
MANAGED                 MOUNTED-STANDBY
MANAGED                 MOUNTED-STANDBY
MANAGED                 MOUNTED-STANDBY

已选择11行。

 

SQL> alter database recover managed standby database cancel;

数据库已更改。

SQL> select db_unique_name ,database_role,open_mode from v$database;

DB_UNIQUE_NAME                 DATABASE_ROLE    OPEN_MODE
------------------------------ ---------------- ----------
standby1                       PRIMARY          READ WRITE

SQL> select db_unique_name ,database_role,open_mode from v$database;

DB_UNIQUE_NAME                 DATABASE_ROLE    OPEN_MODE
------------------------------ ---------------- ----------
xhtest                         PRIMARY          READ WRITE

 

小结:是单项目变更以前的必须先主库转成 stanby ,standby 才可以转成primary否则hang住
而在standby 用alter database active standby database 直接就变成 primary库了,这时候产生2个primary,一般在主库出问题,已经不能转成standby时候用

,这个是单项的 转不回去~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~


SQL> alter database convert to physical standby;
alter database convert to physical standby
                       *
第 1 行出现错误:
ORA-00933: SQL 命令未正确结束

 


SQL> alter database commit to switchover to physical standby;
alter database commit to switchover to physical standby
*
第 1 行出现错误:
ORA-16038: 日志 2 序列号 2 无法归档
ORA-16009: 远程归档日志目标必须为备用数据库
ORA-00312: 联机日志 2 线程 1: 'D:\STANDBYPHYSICAL\REDO02.LOG'


SQL> alter system set log_archive_dest_state_2=defer;

系统已更改。

SQL> alter database convert to physical standby;
alter database convert to physical standby
                       *
第 1 行出现错误:
ORA-00933: SQL 命令未正确结束


SQL> alter database commit to switchover to physical standby;
alter database commit to switchover to physical standby
*
第 1 行出现错误:
ORA-01109: 数据库未打开


SQL> shutdown immediate;
ORA-01109: 数据库未打开


已经卸载数据库。
ORACLE 例程已经关闭。
SQL> startup
ORACLE 例程已经启动。

Total System Global Area  171966464 bytes
Fixed Size                   787988 bytes
Variable Size             145750508 bytes
Database Buffers           25165824 bytes
Redo Buffers                 262144 bytes
数据库装载完毕。
数据库已经打开。
SQL> alter database commit to switchover to physical standby;
alter database commit to switchover to physical standby
*
第 1 行出现错误:
ORA-01154: 数据库忙。现在不允许打开, 关闭, 装载和卸载


SQL> select db_unique_name ,database_role,open_mode from v$database;

DB_UNIQUE_NAME                 DATABASE_ROLE    OPEN_MODE
------------------------------ ---------------- ----------
standby1                       PRIMARY          READ WRITE

SQL> alter database recover managed standby database cancel;
alter database recover managed standby database cancel
*
第 1 行出现错误:
ORA-01665: 控制文件不是一个备用控制文件


SQL> startup force mount
ORACLE 例程已经启动。

Total System Global Area  171966464 bytes
Fixed Size                   787988 bytes
Variable Size             145750508 bytes
Database Buffers           25165824 bytes
Redo Buffers                 262144 bytes
数据库装载完毕。
SQL> alter database commit to switchover to physical standby;
alter database commit to switchover to physical standby
*
第 1 行出现错误:
ORA-01109: 数据库未打开


SQL> alter database open read only;
alter database open read only
*
第 1 行出现错误:
ORA-16005: 数据库需要恢复


SQL> recover database ;
完成介质恢复。
SQL> alter database open read only;
alter database open read only
*
第 1 行出现错误:
ORA-16005: 数据库需要恢复


SQL> alter database commit to switchover to physical standby;
alter database commit to switchover to physical standby
*
第 1 行出现错误:
ORA-01109: 数据库未打开


SQL> alter database open;

 


SQL> select db_unique_name ,database_role,open_mode from v$database;

DB_UNIQUE_NAME                 DATABASE_ROLE    OPEN_MODE
------------------------------ ---------------- ----------
xhtest                         PRIMARY          MOUNTED

SQL> alter system set log_archive_dest_state_2=defer;~~~~~~~~~~~~~~~~~主库的(xhtest)

系统已更改。

SQL> alter database open;

数据库已更改。

SQL> alter database commit to switchover to physical standby;
alter database commit to switchover to physical standby
*
第 1 行出现错误:
ORA-16014: 日志 2 的序列号 5 未归档, 没有可用的目的地
ORA-00312: 联机日志 2 线程 1: 'D:\STANDBYPHYSICAL\REDO02.LOG'


SQL> show parameter db_recover_file_dest
SQL> show parameter db_recovery_file_dest

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      D:\STANDBYPHYSICAL\ARCHIVE
db_recovery_file_dest_size           big integer 2G
SQL> show parameter log_archive_dest_1

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_1                   string      location=D:\standbyphysical\ar
                                                 chive1 valid_for=(all_logfiles
                                                 ,all_roles) db_unique_name=sta
                                                 ndby1
log_archive_dest_10                  string
SQL> alter database convert to physical standby;
alter database convert to physical standby
                       *
第 1 行出现错误:
ORA-00933: SQL 命令未正确结束


SQL> alter database commit to switchover to physical standby;
alter database commit to switchover to physical standby
*
第 1 行出现错误:
ORA-01109: 数据库未打开


SQL> startup force ;
ORACLE 例程已经启动。

Total System Global Area  171966464 bytes
Fixed Size                   787988 bytes
Variable Size             145750508 bytes
Database Buffers           25165824 bytes
Redo Buffers                 262144 bytes
数据库装载完毕。
数据库已经打开。
SQL> alter database commit to switchover to physical standby;
alter database commit to switchover to physical standby
*
第 1 行出现错误:
ORA-16014: 日志 3 的序列号 6 未归档, 没有可用的目的地
ORA-00312: 联机日志 3 线程 1: 'D:\STANDBYPHYSICAL\REDO03.LOG'


SQL>alter database clear unarchived logfile group 3;
做了这个操作后
ORA-00600: internal error code, arguments: [3712], [1], [1], [0], [1790983], [0], [1790982], []

 

hang住了 已经没有别的办法了 重新建立standby1吧

 

SQL> alter system set log_archive_dest_state_2=enable;(xhtest)

系统已更改。

重新建立步骤不详细介绍了~


SQL> select PROCESS,CLIENT_PROCESS,STATUS,SEQUENCE# from v$managed_standby;

PROCESS   CLIENT_P STATUS        SEQUENCE#
--------- -------- ------------ ----------
ARCH      ARCH     CONNECTED             0
ARCH      ARCH     CONNECTED             0
RFS       UNKNOWN  RECEIVING             0


SQL> select db_unique_name ,database_role,open_mode from v$database;

DB_UNIQUE_NAME                 DATABASE_ROLE    OPEN_MODE
------------------------------ ---------------- ----------
standby1                       PHYSICAL STANDBY MOUNTED


下面在实验下active  

 

SQL> startup force mount;
ORACLE 例程已经启动。

Total System Global Area  171966464 bytes
Fixed Size                   787988 bytes
Variable Size             145750508 bytes
Database Buffers           25165824 bytes
Redo Buffers                 262144 bytes
数据库装载完毕。
SQL> alter database flashback on;

数据库已更改。


SQL> alter system switch logfile;

系统已更改。

 

SQL> select PROCESS,CLIENT_PROCESS,STATUS,SEQUENCE# from v$managed_standby;

PROCESS   CLIENT_P STATUS        SEQUENCE#
--------- -------- ------------ ----------
ARCH      ARCH     CONNECTED             0
ARCH      ARCH     CONNECTED             0
RFS       UNKNOWN  ATTACHED              0
RFS       UNKNOWN  RECEIVING             0

SQL> alter database recover managed standby database disconnect from session;

数据库已更改。

SQL> alter database recover managed standby database cancel
  2  ;

数据库已更改。

SQL> alter database activate standby database;

数据库已更改。

SQL> select db_unique_name ,database_role,open_mode,switchover_status from v$dat
abase;
select db_unique_name ,database_role,open_mode,switchover_status from v$database

                                                                      *
第 1 行出现错误:
ORA-01507: 未装载数据库


SQL> startup force mount
ORACLE 例程已经启动。

Total System Global Area  171966464 bytes
Fixed Size                   787988 bytes
Variable Size             145750508 bytes
Database Buffers           25165824 bytes
Redo Buffers                 262144 bytes
数据库装载完毕。
SQL> select db_unique_name ,database_role,open_mode,switchover_status from v$dat
abase;

DB_UNIQUE_NAME                 DATABASE_ROLE    OPEN_MODE  SWITCHOVER_STATUS
------------------------------ ---------------- ---------- --------------------
standby1                       PRIMARY          MOUNTED    TO STANDBY

SQL>

 

SQL> alter database convert to physical standby;
alter database convert to physical standby
                       *
第 1 行出现错误:
ORA-00933: SQL 命令未正确结束

SQL> alter database commit to switchover to physical standby;
alter database commit to switchover to physical standby
*
第 1 行出现错误:
ORA-01109: 数据库未打开


SQL> alter database open;

数据库已更改。

SQL> alter database commit to switchover to physical standby;

数据库已更改。

SQL> select db_unique_name ,database_role,open_mode,switchover_status from v$dat
abase;
select db_unique_name ,database_role,open_mode,switchover_status from v$database

                                                                      *
第 1 行出现错误:
ORA-01507: 未装载数据库


SQL> startup force mount
ORACLE 例程已经启动。

Total System Global Area  171966464 bytes
Fixed Size                   787988 bytes
Variable Size             145750508 bytes
Database Buffers           25165824 bytes
Redo Buffers                 262144 bytes
数据库装载完毕。
SQL>

SQL> select db_unique_name ,database_role,open_mode,switchover_status from v$dat
abase;

DB_UNIQUE_NAME                 DATABASE_ROLE    OPEN_MODE  SWITCHOVER_STATUS
------------------------------ ---------------- ---------- --------------------
standby1                       PHYSICAL STANDBY MOUNTED    TO PRIMARY                                    转变回来了


SQL> show parameter log_archive_dest_state_2(standby1)

NAME                                 TYPE        VALUE
------------------------------------ ----------- ----------------------------
log_archive_dest_state_2             string      ENABLE

 

SQL> alter database activate standby database;

数据库已更改。

SQL> alter database open
  2  ;
alter database open
*
第 1 行出现错误:
ORA-01507: 未装载数据库


SQL> startup force
ORACLE 例程已经启动。

Total System Global Area  171966464 bytes
Fixed Size                   787988 bytes
Variable Size             145750508 bytes
Database Buffers           25165824 bytes
Redo Buffers                 262144 bytes
数据库装载完毕。
数据库已经打开。
SQL> select db_unique_name ,database_role,open_mode,switchover_status from v$dat
abase;

DB_UNIQUE_NAME                 DATABASE_ROLE    OPEN_MODE  SWITCHOVER_STATUS
------------------------------ ---------------- ---------- --------------------
standby1                       PRIMARY          READ WRITE SESSIONS ACTIVE

SQL> alter database commit to switchover to physical standby;(在standby1 上)
alter database commit to switchover to physical standby
*
第 1 行出现错误:
ORA-16038: 日志 2 序列号 1 无法归档
ORA-16009: 远程归档日志目标必须为备用数据库
ORA-00312: 联机日志 2 线程 1: 'D:\STANDBYPHYSICAL\REDO02.LOG'


SQL> alter database convert to physical standby;(在standby 上)
alter database convert to physical standby
                       *
第 1 行出现错误:
ORA-00933: SQL 命令未正确结束


SQL> alter database commit to switchover to physical standby;(在xhtest 上)
alter database commit to switchover to physical standby
*
第 1 行出现错误:
ORA-16038: 日志 2 序列号 504 无法归档
ORA-16009: 远程归档日志目标必须为备用数据库
ORA-00312: 联机日志 2 线程 1:
'F:\ORACLE\PRODUCT\10.1.0\ORADATA\XHTEST\REDO02.LOG'


08.08.20~~~~~~~~~~~~~~~~~~~~继续上面的实验

SQL> select db_unique_name ,database_role,open_mode,switchover_status from v$dat
abase;

DB_UNIQUE_NAME                 DATABASE_ROLE    OPEN_MODE  SWITCHOVER_STATUS
------------------------------ ---------------- ---------- --------------------
standby1                       PRIMARY          READ WRITE SESSIONS ACTIVE

 


SQL> alter system set log_archive_dest_state_2=defer;

系统已更改。

SQL> alter database commit to switchover to physical standby;
alter database commit to switchover to physical standby
*
第 1 行出现错误:
ORA-01109: 数据库未打开


SQL> startup force mount
ORACLE 例程已经启动。

Total System Global Area  171966464 bytes
Fixed Size                   787988 bytes
Variable Size             145750508 bytes
Database Buffers           25165824 bytes
Redo Buffers                 262144 bytes
数据库装载完毕。
SQL> alter database commit to switchover to physical standby;
alter database commit to switchover to physical standby
*
第 1 行出现错误:
ORA-01109: 数据库未打开


SQL> alter database open;

数据库已更改。

SQL> alter database commit to switchover to physical standby;
alter database commit to switchover to physical standby
*
第 1 行出现错误:
ORA-16014: 日志 3 的序列号 3 未归档, 没有可用的目的地
ORA-00312: 联机日志 3 线程 1: 'D:\STANDBYPHYSICAL\REDO03.LOG'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

SQL> alter system set log_archive_dest_state_2=enable;

系统已更改。


SQL> alter database create standby controlfile as 'd:\os备份\CONTROL01.CTL' reus
e;

数据库已更改。

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~有一个种方法说 在primary重新建立控制文件考回原standby然后,在变换 发现是不行的 是错误的理论
SQL> shutdown immediate;
ORA-01109: 数据库未打开


已经卸载数据库。
ORACLE 例程已经关闭。
SQL> startup
ORACLE 例程已经启动。

Total System Global Area  171966464 bytes
Fixed Size                   787988 bytes
Variable Size             145750508 bytes
Database Buffers           25165824 bytes
Redo Buffers                 262144 bytes
数据库装载完毕。
ORA-16004: 备份数据库需要恢复
ORA-01190: 控制文件或数据文件 1 来自于最后一个 RESETLOGS 之前
ORA-01110: 数据文件 1: 'D:\STANDBYPHYSICAL\SYSTEM01.DBF'


SQL> recover database;
ORA-00283: 恢复会话因错误而取消
ORA-01610: 使用 BACKUP CONTROLFILE 选项的恢复必须已完成


SQL> recover database using backup controlfile;
ORA-00283: 恢复会话因错误而取消
ORA-01666: 控制文件用于备用数据库


SQL> recover database until cancel using backup controlfile;
ORA-00283: 恢复会话因错误而取消
ORA-01666: 控制文件用于备用数据库

 

~~~小结:alter alter database activate standby database 激活了备用数据库,在open后 这个过程将自动 resetlogs。因此,在 startup 之前要做一次 cold backup,因为以往的 backup 最多只能 recover 到 standby database 被activated 这一点。 因此  这个是个单项过程
所以一般是 在激活后 利用 现在standby(已经成primary库) 来重建原来的primary~~~~~~~把primary先建成主库 ,然后切换,oracle推荐用alter database commit to switchover to  这样可以尽可能的少丢失数据(主要指lgwr 后standby logfile中的数据)
 

 


实验~~~~哪几种办法可以能切换过来
先重建立standby


SQL> select db_unique_name ,database_role,open_mode,switchover_status from v$dat
abase;

DB_UNIQUE_NAME                 DATABASE_ROLE    OPEN_MODE  SWITCHOVER_STATUS
------------------------------ ---------------- ---------- --------------------
standby1                       PHYSICAL STANDBY MOUNTED    TO PRIMARY

SQL> create restore point restore_point guarantee flashback database;

SQL> select  PROCESS from  v$managed_standby;

PROCESS
---------
ARCH
ARCH
RFS
SQL> alter system switch logfile;

系统已更改。

SQL> alter system switch logfile;

系统已更改。

SQL> alter system switch logfile;

系统已更改。
SQL> select  PROCESS, CLIENT_PROCESS,STATUS,SEQUENCE# from  v$managed_standby;

PROCESS   CLIENT_P STATUS        SEQUENCE#
--------- -------- ------------ ----------
ARCH      ARCH     CONNECTED             0
ARCH      ARCH     CONNECTED             0
RFS       UNKNOWN  ATTACHED              0
RFS       UNKNOWN  RECEIVING             0

SQL> select name from v$archived_log;

NAME
--------------------------------------------------------------------------------

D:\STANDBYPHYSICAL\ARCHIVE\ARC00507_0661538341.001
D:\STANDBYPHYSICAL\ARCHIVE\ARC00508_0661538341.001
D:\STANDBYPHYSICAL\ARCHIVE\ARC00506_0661538341.001
D:\STANDBYPHYSICAL\ARCHIVE\ARC00509_0661538341.001

 

SQL> alter database recover managed standby database disconnect from session;

数据库已更改。

SQL> select  PROCESS, CLIENT_PROCESS,STATUS,SEQUENCE# from  v$managed_standby;

PROCESS   CLIENT_P STATUS        SEQUENCE#
--------- -------- ------------ ----------
ARCH      ARCH     CONNECTED             0
ARCH      ARCH     CONNECTED             0
RFS       UNKNOWN  ATTACHED              0
RFS       UNKNOWN  RECEIVING             0
MRP0      N/A      APPLYING_LOG        507

SQL> select name,applied,creator from v$archived_log;

NAME                                               APP CREATOR
-------------------------------------------------- --- -------
D:\STANDBYPHYSICAL\ARCHIVE\ARC00507_0661538341.001 YES ARCH
D:\STANDBYPHYSICAL\ARCHIVE\ARC00508_0661538341.001 YES ARCH
D:\STANDBYPHYSICAL\ARCHIVE\ARC00506_0661538341.001 YES ARCH
D:\STANDBYPHYSICAL\ARCHIVE\ARC00509_0661538341.001 YES ARCH
D:\STANDBYPHYSICAL\ARCHIVE\ARC00505_0661538341.001 YES ARCH
D:\STANDBYPHYSICAL\ARCHIVE\ARC00510_0661538341.001 YES ARCH

已选择6行。

SQL> alter database recover managed standby database cancel;


SQL> shutdown immediate;
ORA-01109: 数据库未打开
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~做了一个冷备份

已经卸载数据库。
ORACLE 例程已经关闭。
SQL> startup mount
ORACLE 例程已经启动。

Total System Global Area  171966464 bytes
Fixed Size                   787988 bytes
Variable Size             145750508 bytes
Database Buffers           25165824 bytes
Redo Buffers                 262144 bytes
数据库装载完毕。
SQL> alter database activate standby database;

数据库已更改。

SQL> select db_unique_name ,database_role,open_mode,switchover_status from v$dat
abase;
select db_unique_name ,database_role,open_mode,switchover_status from v$database

                                                                      *
第 1 行出现错误:
ORA-01507: 未装载数据库


SQL> startup force
ORACLE 例程已经启动。

Total System Global Area  171966464 bytes
Fixed Size                   787988 bytes
Variable Size             145750508 bytes
Database Buffers           25165824 bytes
Redo Buffers                 262144 bytes
数据库装载完毕。
数据库已经打开。
SQL> select db_unique_name ,database_role,open_mode,switchover_status from v$dat
abase;

DB_UNIQUE_NAME                 DATABASE_ROLE    OPEN_MODE  SWITCHOVER_STATUS
------------------------------ ---------------- ---------- --------------------
standby1                       PRIMARY          READ WRITE SESSIONS ACTIVE


SQL> create restore point restore_point guarantee flashback database;
create restore point restore_point guarantee flashback database
       *
第 1 行出现错误:
ORA-00901: 无效 CREATE 命令


SQL> alter database commit to switchover to physical standby;
alter database commit to switchover to physical standby
*
第 1 行出现错误:
ORA-16038: 日志 1 序列号 1 无法归档
ORA-16009: 远程归档日志目标必须为备用数据库
ORA-00312: 联机日志 1 线程 1: 'D:\STANDBYPHYSICAL\REDO01.LOG'


SQL> alter database convert to  standby database;
alter database convert to  standby database
                       *
第 1 行出现错误:
ORA-00933: SQL 命令未正确结束


SQL>


~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~把刚才冷备份的 文件考回standby1目录

 

SQL> startup
ORACLE 例程已经启动。

Total System Global Area  171966464 bytes
Fixed Size                   787988 bytes
Variable Size             145750508 bytes
Database Buffers           25165824 bytes
Redo Buffers                 262144 bytes
数据库装载完毕。
数据库已经打开。
SQL> alter database commit to switchover to physical standby;

数据库已更改。

SQL> select db_unique_name ,database_role,open_mode,switchover_status from v$dat
abase;

DB_UNIQUE_NAME                 DATABASE_ROLE    OPEN_MODE  SWITCHOVER_STATUS
------------------------------ ---------------- ---------- --------------------
standby1                       PHYSICAL STANDBY READ ONLY  SWITCHOVER PENDING~~~~~~~~~~~~~~~~~~~~~~~~~~~靠转变回来了

 


SQL> select  PROCESS, CLIENT_PROCESS,STATUS,SEQUENCE# from  v$managed_standby;

PROCESS   CLIENT_P STATUS        SEQUENCE#
--------- -------- ------------ ----------
ARCH      ARCH     CONNECTED             0
ARCH      ARCH     CONNECTED             0
RFS       UNKNOWN  RECEIVING             0

 

SQL> alter system switch logfile;(XHTEST)

系统已更改。

SQL>

SQL> select name,applied,creator from v$archived_log;

NAME                                               APP CREATOR
-------------------------------------------------- --- -------
D:\STANDBYPHYSICAL\ARCHIVE\ARC00507_0661538341.001 YES ARCH
D:\STANDBYPHYSICAL\ARCHIVE\ARC00508_0661538341.001 YES ARCH
D:\STANDBYPHYSICAL\ARCHIVE\ARC00506_0661538341.001 YES ARCH
D:\STANDBYPHYSICAL\ARCHIVE\ARC00509_0661538341.001 YES ARCH
D:\STANDBYPHYSICAL\ARCHIVE\ARC00505_0661538341.001 YES ARCH
D:\STANDBYPHYSICAL\ARCHIVE\ARC00510_0661538341.001 YES ARCH
D:\STANDBYPHYSICAL\ARCHIVE\ARC00511_0661538341.001 NO  ARCH

已选择7行。

SQL> alter database recover managed standby database disconnect from session;

数据库已更改。

SQL> select name,applied,creator from v$archived_log;

NAME                                               APP CREATOR
-------------------------------------------------- --- -------
D:\STANDBYPHYSICAL\ARCHIVE\ARC00507_0661538341.001 YES ARCH
D:\STANDBYPHYSICAL\ARCHIVE\ARC00508_0661538341.001 YES ARCH
D:\STANDBYPHYSICAL\ARCHIVE\ARC00506_0661538341.001 YES ARCH
D:\STANDBYPHYSICAL\ARCHIVE\ARC00509_0661538341.001 YES ARCH
D:\STANDBYPHYSICAL\ARCHIVE\ARC00505_0661538341.001 YES ARCH
D:\STANDBYPHYSICAL\ARCHIVE\ARC00510_0661538341.001 YES ARCH
D:\STANDBYPHYSICAL\ARCHIVE\ARC00511_0661538341.001 YES ARCH

已选择7行。

SQL> select db_unique_name ,database_role,open_mode,switchover_status from v$dat
abase;

DB_UNIQUE_NAME                 DATABASE_ROLE    OPEN_MODE  SWITCHOVER_STATUS
------------------------------ ---------------- ---------- --------------------
standby1                       PHYSICAL STANDBY MOUNTED    SWITCHOVER PENDING

 


用以上这个方法我们~~~让激活的standby又变了回去,其实这个方法 就跟重新建立standby没 区别

 

推荐的转换
SQL> select db_unique_name,database_role,switchover_status from v$database;

DB_UNIQUE_NAME                 DATABASE_ROLE    SWITCHOVER_STATUS
------------------------------ ---------------- --------------------
xhtest                         PRIMARY          SESSIONS ACTIVE

 

 

SQL> alter database commit  to  switchover to physical standby with session shut     (xhtest)
down ;

数据库已更改。


SQL> select db_unique_name,database_role,switchover_status from v$database;
DB_UNIQUE_NAME                 DATABASE_ROLE    OPEN_MODE  SWITCHOVER_STATUS
------------------------------ ---------------- ---------- --------------------
standby1                       PHYSICAL STANDBY MOUNTED    TO PRIMARY


SQL> select db_unique_name,database_role,switchover_status from v$database;

DB_UNIQUE_NAME                 DATABASE_ROLE    SWITCHOVER_STATUS
------------------------------ ---------------- --------------------
xhtest                         PHYSICAL STANDBY TO PRIMARY

SQL>

 

SQL> alter database commit to  switchover to primary;

数据库已更改。

SQL> select db_unique_name,database_role,switchover_status from v$database;
select db_unique_name,database_role,switchover_status from v$database
                                                           *
第 1 行出现错误:
ORA-01507: 未装载数据库


SQL> shutdown immedaite;
SP2-0717: 非法的 SHUTDOWN 选项
SQL> shutdown immediate;
ORA-01507: 未装载数据库


ORACLE 例程已经关闭。
SQL> startup;
ORACLE 例程已经启动。

Total System Global Area  171966464 bytes
Fixed Size                   787988 bytes
Variable Size             145750508 bytes
Database Buffers           25165824 bytes
Redo Buffers                 262144 bytes
数据库装载完毕。
数据库已经打开。
SQL> select db_unique_name,database_role,switchover_status from v$database;

DB_UNIQUE_NAME                 DATABASE_ROLE    SWITCHOVER_STATUS
------------------------------ ---------------- --------------------
xhtest                         PRIMARY          SESSIONS ACTIVE

SQL>

SQL> select db_unique_name ,database_role,open_mode,switchover_status from v$dat
abase;

DB_UNIQUE_NAME                 DATABASE_ROLE    OPEN_MODE  SWITCHOVER_STATUS
------------------------------ ---------------- ---------- --------------------
standby1                       PHYSICAL STANDBY MOUNTED    TO PRIMARY~~~~~~~~~~~~~~~~~~~~~~~~

SQL> select  PROCESS, CLIENT_PROCESS,STATUS,SEQUENCE# from  v$managed_standby;

PROCESS   CLIENT_P STATUS        SEQUENCE#
--------- -------- ------------ ----------
ARCH      ARCH     CONNECTED             0
ARCH      ARCH     CONNECTED             0
RFS       UNKNOWN  RECEIVING             0

 

 

SQL> alter database commit to switchover to primary;

数据库已更改。

SQL> select db_unique_name ,database_role,open_mode,switchover_status from v$dat
abase;
select db_unique_name ,database_role,open_mode,switchover_status from v$database

*
第 1 行出现错误:
ORA-01507: 未装载数据库


SQL> shutdown immedaite;
SP2-0717: 非法的 SHUTDOWN 选项
SQL> shutdown immediate;
ORA-01507: 未装载数据库


ORACLE 例程已经关闭。
SQL> startup
ORACLE 例程已经启动。

Total System Global Area  171966464 bytes
Fixed Size                   787988 bytes
Variable Size             145750508 bytes
Database Buffers           25165824 bytes
Redo Buffers                 262144 bytes
数据库装载完毕。
数据库已经打开。
SQL> select db_unique_name ,database_role,open_mode,switchover_status from v$dat
abase;

DB_UNIQUE_NAME                 DATABASE_ROLE    OPEN_MODE  SWITCHOVER_STATUS
------------------------------ ---------------- ---------- --------------------
standby1                       PRIMARY          READ WRITE SESSIONS ACTIVE

 

在primary没有转变成standby的情况下,standby1转为了primary

 


SQL> alter database commit to switchover to physical standby with session shutdo
wn ;
alter database commit to switchover to physical standby with session shutdown~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~靠 转不回去了
*
第 1 行出现错误:
ORA-16038: 日志 3 序列号 514 无法归档
ORA-16009: 远程归档日志目标必须为备用数据库
ORA-00312: 联机日志 3 线程 1: 'D:\STANDBYPHYSICAL\REDO03.LOG'

 


SQL> shutdown immediate;
ORA-01109: 数据库未打开


已经卸载数据库。
ORACLE 例程已经关闭。
SQL>

SQL> shutdown immediate;
ORA-01109: 数据库未打开

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~把第一次的冷备份考回来(包含控制文件)
已经卸载数据库。
ORACLE 例程已经关闭。
SQL> startup
ORACLE 例程已经启动。

Total System Global Area  171966464 bytes
Fixed Size                   787988 bytes
Variable Size             145750508 bytes
Database Buffers           25165824 bytes
Redo Buffers                 262144 bytes
数据库装载完毕。
数据库已经打开。
SQL> alter database commit to switchover to physical standby with session shutdo
wn ;

数据库已更改。

SQL>


SQL> select db_unique_name,database_role,switchover_status from v$database;

DB_UNIQUE_NAME                 DATABASE_ROLE    SWITCHOVER_STATUS
------------------------------ ---------------- --------------------
standby1                       PHYSICAL STANDBY SWITCHOVER PENDING

SQL> select db_unique_name ,database_role,open_mode,switchover_status from v$dat
abase;

DB_UNIQUE_NAME                 DATABASE_ROLE    OPEN_MODE  SWITCHOVER_STATUS
------------------------------ ---------------- ---------- --------------------
standby1                       PHYSICAL STANDBY READ ONLY  SWITCHOVER PENDING


SQL> alter database recover managed standby database disconnect from session;

数据库已更改。

SQL> select name,applied,creator from v$archived_log;

NAME                                               APP CREATOR
-------------------------------------------------- --- -------
D:\STANDBYPHYSICAL\ARCHIVE\ARC00507_0661538341.001 YES ARCH
D:\STANDBYPHYSICAL\ARCHIVE\ARC00508_0661538341.001 YES ARCH
D:\STANDBYPHYSICAL\ARCHIVE\ARC00506_0661538341.001 YES ARCH
D:\STANDBYPHYSICAL\ARCHIVE\ARC00509_0661538341.001 YES ARCH
D:\STANDBYPHYSICAL\ARCHIVE\ARC00505_0661538341.001 YES ARCH
D:\STANDBYPHYSICAL\ARCHIVE\ARC00510_0661538341.001 YES ARCH
D:\STANDBYPHYSICAL\ARCHIVE\ARC00511_0661538341.001 YES ARCH
D:\STANDBYPHYSICAL\ARCHIVE\ARC00512_0661538341.001 YES ARCH
D:\STANDBYPHYSICAL\ARCHIVE\ARC00513_0661538341.001 NO  ARCH

已选择9行。


SQL> select db_unique_name ,database_role,open_mode,switchover_status from v$dat
abase;

DB_UNIQUE_NAME                 DATABASE_ROLE    OPEN_MODE  SWITCHOVER_STATUS
------------------------------ ---------------- ---------- --------------------
standby1                       PHYSICAL STANDBY MOUNTED    SWITCHOVER LATENT

SQL> select  PROCESS, CLIENT_PROCESS,STATUS,SEQUENCE# from  v$managed_standby;

PROCESS   CLIENT_P STATUS        SEQUENCE#
--------- -------- ------------ ----------
ARCH      ARCH     CONNECTED             0
ARCH      ARCH     CONNECTED             0
RFS       UNKNOWN  RECEIVING             0
RFS       UNKNOWN  RECEIVING             0~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~进程没了

SQL> alter database recover managed standby database disconnect from session;~~~~~~~~~~~~~~~~~~需要再开一次

数据库已更改。

SQL> select name,applied,creator from v$archived_log;

NAME                                               APP CREATOR
-------------------------------------------------- --- -------
D:\STANDBYPHYSICAL\ARCHIVE\ARC00507_0661538341.001 YES ARCH
D:\STANDBYPHYSICAL\ARCHIVE\ARC00508_0661538341.001 YES ARCH
D:\STANDBYPHYSICAL\ARCHIVE\ARC00506_0661538341.001 YES ARCH
D:\STANDBYPHYSICAL\ARCHIVE\ARC00509_0661538341.001 YES ARCH
D:\STANDBYPHYSICAL\ARCHIVE\ARC00505_0661538341.001 YES ARCH
D:\STANDBYPHYSICAL\ARCHIVE\ARC00510_0661538341.001 YES ARCH
D:\STANDBYPHYSICAL\ARCHIVE\ARC00511_0661538341.001 YES ARCH
D:\STANDBYPHYSICAL\ARCHIVE\ARC00512_0661538341.001 YES ARCH
D:\STANDBYPHYSICAL\ARCHIVE\ARC00513_0661538341.001 NO  ARCH

已选择9行。

SQL> select name,applied,creator from v$archived_log;

NAME                                               APP CREATOR
-------------------------------------------------- --- -------
D:\STANDBYPHYSICAL\ARCHIVE\ARC00507_0661538341.001 YES ARCH
D:\STANDBYPHYSICAL\ARCHIVE\ARC00508_0661538341.001 YES ARCH
D:\STANDBYPHYSICAL\ARCHIVE\ARC00506_0661538341.001 YES ARCH
D:\STANDBYPHYSICAL\ARCHIVE\ARC00509_0661538341.001 YES ARCH
D:\STANDBYPHYSICAL\ARCHIVE\ARC00505_0661538341.001 YES ARCH
D:\STANDBYPHYSICAL\ARCHIVE\ARC00510_0661538341.001 YES ARCH
D:\STANDBYPHYSICAL\ARCHIVE\ARC00511_0661538341.001 YES ARCH
D:\STANDBYPHYSICAL\ARCHIVE\ARC00512_0661538341.001 YES ARCH
D:\STANDBYPHYSICAL\ARCHIVE\ARC00513_0661538341.001 YES ARCH

已选择9行。

SQL>
SQL> select db_unique_name ,database_role,open_mode,switchover_status from v$dat
abase;

DB_UNIQUE_NAME                 DATABASE_ROLE    OPEN_MODE  SWITCHOVER_STATUS
------------------------------ ---------------- ---------- --------------------
standby1                       PHYSICAL STANDBY MOUNTED    SWITCHOVER PENDING

 

 

SQL> alter database commit  to  switchover to physical standby with session shut
down ;

数据库已更改。

SQL> shutdown immediate
ORA-01507: 未装载数据库


ORACLE 例程已经关闭。
SQL>


~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SQL> select db_unique_name ,database_role,open_mode,switchover_status from v$da
abase;

DB_UNIQUE_NAME                 DATABASE_ROLE    OPEN_MODE  SWITCHOVER_STATUS
------------------------------ ---------------- ---------- --------------------
standby1                       PHYSICAL STANDBY MOUNTED    TO PRIMARY

 

SQL> select db_unique_name,database_role,switchover_status from v$database;

DB_UNIQUE_NAME                 DATABASE_ROLE    SWITCHOVER_STATUS
------------------------------ ---------------- --------------------
xhtest                         PHYSICAL STANDBY TO PRIMARY

 


SQL> alter database commit to  switchover to primary;

数据库已更改。
SQL> select db_unique_name,database_role,switchover_status from v$database;

DB_UNIQUE_NAME                 DATABASE_ROLE    SWITCHOVER_STATUS
------------------------------ ---------------- --------------------
xhtest                         PRIMARY          SESSIONS ACTIVE


SQL> select db_unique_name ,database_role,open_mode,switchover_status from v$dat
abase;

DB_UNIQUE_NAME                 DATABASE_ROLE    OPEN_MODE  SWITCHOVER_STATUS
------------------------------ ---------------- ---------- --------------------
standby1                       PHYSICAL STANDBY MOUNTED    TO PRIMARY

SQL> select  PROCESS, CLIENT_PROCESS,STATUS,SEQUENCE# from  v$managed_standby;

PROCESS   CLIENT_P STATUS        SEQUENCE#
--------- -------- ------------ ----------
ARCH      ARCH     CONNECTED             0
ARCH      ARCH     CONNECTED             0
RFS       UNKNOWN  RECEIVING             0


SQL> alter system switch logfile;(xhtest)

系统已更改。

SQL> select db_unique_name ,database_role,open_mode,switchover_status from v$dat
abase;

DB_UNIQUE_NAME                 DATABASE_ROLE    OPEN_MODE  SWITCHOVER_STATUS
------------------------------ ---------------- ---------- --------------------
standby1                       PHYSICAL STANDBY MOUNTED    RECOVERY NEEDED

SQL> alter database recover managed standby database disconnect from session;

数据库已更改。

SQL> select db_unique_name ,database_role,open_mode,switchover_status from v$dat
abase;

DB_UNIQUE_NAME                 DATABASE_ROLE    OPEN_MODE  SWITCHOVER_STATUS
------------------------------ ---------------- ---------- --------------------
standby1                       PHYSICAL STANDBY MOUNTED    NOT ALLOWED

SQL> select name,applied,creator from v$archived_log;

NAME                                               APP CREATOR
-------------------------------------------------- --- -------
D:\STANDBYPHYSICAL\ARCHIVE\ARC00507_0661538341.001 YES ARCH
D:\STANDBYPHYSICAL\ARCHIVE\ARC00508_0661538341.001 YES ARCH
D:\STANDBYPHYSICAL\ARCHIVE\ARC00506_0661538341.001 YES ARCH
D:\STANDBYPHYSICAL\ARCHIVE\ARC00509_0661538341.001 YES ARCH
D:\STANDBYPHYSICAL\ARCHIVE\ARC00505_0661538341.001 YES ARCH
D:\STANDBYPHYSICAL\ARCHIVE\ARC00510_0661538341.001 YES ARCH
D:\STANDBYPHYSICAL\ARCHIVE\ARC00511_0661538341.001 YES ARCH
D:\STANDBYPHYSICAL\ARCHIVE\ARC00512_0661538341.001 YES ARCH
D:\STANDBYPHYSICAL\ARCHIVE\ARC00513_0661538341.001 YES ARCH
D:\STANDBYPHYSICAL\ARCHIVE\ARC00514_0661538341.001 YES FGRD
D:\STANDBYPHYSICAL\ARCHIVE\ARC00515_0661538341.001 YES ARCH

已选择11行。

 

SQL> alter database commit to switchover to primary ~~~~~~~~~~~~~~~~~~~~~~~~~一直hang着
  2  ;

Wed Aug 20 17:05:12 2008
alter database commit to switchover to primary

Wed Aug 20 17:05:12 2008
If media recovery active, switchover will wait 900 seconds~~~~~~~~~~~~~~~~~~~~~~~~看看能hang多久

 

大约hang了6分钟
SQL> alter database commit  to  switchover to physical standby with session shut (xhtest)
down ;

 


SQL> alter database commit to switchover to primary             主库变过来后standby1也立刻变成primary
  2  ;

数据库已更改。

SQL> select db_unique_name ,database_role,open_mode,switchover_status from v$dat
abase;

DB_UNIQUE_NAME                 DATABASE_ROLE    OPEN_MODE  SWITCHOVER_STATUS
------------------------------ ---------------- ---------- --------------------
standby1                       PRIMARY          READ WRITE SESSIONS ACTIVE


SQL> select db_unique_name,database_role,switchover_status from v$database;

DB_UNIQUE_NAME                 DATABASE_ROLE    SWITCHOVER_STATUS
------------------------------ ---------------- --------------------
xhtest                         PHYSICAL STANDBY TO PRIMARY

alter system switch logfile;(standby1)

SQL> select  PROCESS, CLIENT_PROCESS,STATUS,SEQUENCE# from  v$managed_standby;

PROCESS   CLIENT_P STATUS        SEQUENCE#
--------- -------- ------------ ----------
ARCH      ARCH     CONNECTED             0
ARCH      ARCH     CONNECTED             0
RFS       UNKNOWN  RECEIVING             0


SQL> alter database recover managed standby database disconnect from session;

 

 

QL> select db_unique_name,database_role,switchover_status from v$database;

dB_UNIQUE_NAME                 DATABASE_ROLE    SWITCHOVER_STATUS
----------------------------- ---------------- --------------------
xhtest                         PHYSICAL STANDBY NOT ALLOWED

 

 

 


08.08.21实验 ~~~~~~~~~~~~~~~~~~~~~~

 

看看 激活 standby后能闪回回去不

SQL> select OPEN_MODE,database_role,db_unique_name,switchover_status from v$data
base;

OPEN_MODE  DATABASE_ROLE    DB_UNIQUE_NAME                 SWITCHOVER_STATUS
---------- ---------------- ------------------------------ --------------------
READ WRITE PRIMARY          xhtest                         SESSIONS ACTIVE

SQL> select OPEN_MODE,database_role,db_unique_name,switchover_status from v$data
base;

OPEN_MODE  DATABASE_ROLE    DB_UNIQUE_NAME                 SWITCHOVER_STATUS
---------- ---------------- ------------------------------ --------------------
MOUNTED    PHYSICAL STANDBY standby1                       NOT ALLOWED

 


SQL> select flashback_on from v$database;

FLA
---
NO

SQL> alter database flashback on;
alter database flashback on
*
第 1 行出现错误:
ORA-01153: 激活了不兼容的介质恢复


SQL> alter database recover managed  standby database cancel;

数据库已更改。

SQL> alter database flashback on;

数据库已更改。

SQL> select flashback_on from v$database;

FLA
---
YES

SQL>


SQL> select current_scn from v$database;(standby)

CURRENT_SCN
-----------
    1835282

 


SQL> alter database activate standby database;

数据库已更改。

SQL> select OPEN_MODE,database_role,db_unique_name,switchover_status from v$data
base;
select OPEN_MODE,database_role,db_unique_name,switchover_status from v$database
                                                                     *
第 1 行出现错误:
ORA-01507: 未装载数据库


SQL> shutdown immediate;
ORA-01507: 未装载数据库


ORACLE 例程已经关闭。
SQL> startup
ORACLE 例程已经启动。

Total System Global Area  171966464 bytes
Fixed Size                   787988 bytes
Variable Size             145750508 bytes
Database Buffers           25165824 bytes
Redo Buffers                 262144 bytes
数据库装载完毕。
数据库已经打开。
SQL> select OPEN_MODE,database_role,db_unique_name,switchover_status from v$data
base;

OPEN_MODE  DATABASE_ROLE    DB_UNIQUE_NAME                 SWITCHOVER_STATUS
---------- ---------------- ------------------------------ --------------------
READ WRITE PRIMARY          standby1                       SESSIONS ACTIVE

SQL>


SQL> alter database commit to switchover to physical standby;
alter database commit to switchover to physical standby
*
第 1 行出现错误:
ORA-16038: 日志 2 序列号 1 无法归档
ORA-16009: 远程归档日志目标必须为备用数据库
ORA-00312: 联机日志 2 线程 1: 'D:\STANDBYPHYSICAL\REDO02.LOG'


SQL> select sequence# ,status from v$log;

 SEQUENCE# STATUS
---------- ----------------
         0 UNUSED~~~~~~~~~~~~~~~~~~~~~`resetlogs了
         1 CURRENT
         0 UNUSED

 


SQL> flashback database to scn 1835282;
flashback database to scn 1835282
*
第 1 行出现错误:
ORA-38729: 执行 FLASHBACK 的闪回数据库日志数据不足。

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Prod
PL/SQL Release 10.1.0.2.0 - Production
CORE    10.1.0.2.0      Production
TNS for 32-bit Windows: Version 10.1.0.2.0 - Production
NLSRTL Version 10.1.0.2.0 - Production


注意 :10.1.0.2.0         版本不行 ~~~~~~~~~~需要 10.2.0.1 版本可以

所以实验失败~~~~~~~~~~~~~~理论很简单
在standby 激活前~~~做一个 闪回点create restore point name
然后激活~~~~~standby 成readwrite 然后,变会standby前 先闪回到 restore point
然后alter database convert to physical standby;(需要 10.2.0.1 以上版本可以) 创建闪回点也是10.2.0.1以上才可以10GR2以上
这之中,我们还需要关掉primary传递给stadby的远程归档位置log_archive_dest_state_2=defer都先关了 避免primary 还继续给这个远程位置传递,这个位置已经成主库了

 

 

~~~~~~~~~~~~~~~~~~~~~~10.2.0.1 后flashback 可以 闪回到resetlogs前


重新建立standby的分析


刚才激活后,在这个版本是转换不过去了~~~~~~~~~~~没法用flashback


重新建立用 8.20好的备份,8.20日的建立的standby controlfile

 


SQL> startup
ORACLE 例程已经启动。

Total System Global Area  171966464 bytes
Fixed Size                   787988 bytes
Variable Size             145750508 bytes
Database Buffers           25165824 bytes
Redo Buffers                 262144 bytes
数据库装载完毕。
数据库已经打开。
SQL> select OPEN_MODE,database_role,db_unique_name,switchover_status from v$data
base;

OPEN_MODE  DATABASE_ROLE    DB_UNIQUE_NAME                 SWITCHOVER_STATUS
---------- ---------------- ------------------------------ --------------------
READ ONLY  PHYSICAL STANDBY standby1                       SWITCHOVER PENDING


把 冷备份直接覆盖过来后


SQL> select name,applied,creator from v$archived_log;

NAME                                               APP CREATOR
-------------------------------------------------- --- -------
D:\STANDBYPHYSICAL\ARCHIVE\ARC00507_0661538341.001 YES ARCH
D:\STANDBYPHYSICAL\ARCHIVE\ARC00508_0661538341.001 YES ARCH
D:\STANDBYPHYSICAL\ARCHIVE\ARC00506_0661538341.001 YES ARCH
D:\STANDBYPHYSICAL\ARCHIVE\ARC00509_0661538341.001 YES ARCH
D:\STANDBYPHYSICAL\ARCHIVE\ARC00505_0661538341.001 YES ARCH
D:\STANDBYPHYSICAL\ARCHIVE\ARC00510_0661538341.001 YES ARCH

已选择6行。


这几个是 20号的归档日志


SQL> select PROCESS,CLIENT_PROCESS,STATUS,SEQUENCE# from v$managed_standby;

PROCESS   CLIENT_P STATUS        SEQUENCE#
--------- -------- ------------ ----------
ARCH      ARCH     CONNECTED             0
ARCH      ARCH     CONNECTED             0
RFS       UNKNOWN  ATTACHED              0

 

SQL> alter system switch logfile ;

系统已更改。

SQL>

SQL> select name,applied,creator from v$archived_log;

NAME                                               APP CREATOR
-------------------------------------------------- --- -------
D:\STANDBYPHYSICAL\ARCHIVE\ARC00507_0661538341.001 YES ARCH
D:\STANDBYPHYSICAL\ARCHIVE\ARC00508_0661538341.001 YES ARCH
D:\STANDBYPHYSICAL\ARCHIVE\ARC00506_0661538341.001 YES ARCH
D:\STANDBYPHYSICAL\ARCHIVE\ARC00509_0661538341.001 YES ARCH
D:\STANDBYPHYSICAL\ARCHIVE\ARC00505_0661538341.001 YES ARCH
D:\STANDBYPHYSICAL\ARCHIVE\ARC00510_0661538341.001 YES ARCH
D:\STANDBYPHYSICAL\ARCHIVE\ARC00511_0661538341.001 NO  ARCH
D:\STANDBYPHYSICAL\ARCHIVE\ARC00512_0661538341.001 NO  ARCH
D:\STANDBYPHYSICAL\ARCHIVE\ARC00513_0661538341.001 NO  ARCH
D:\STANDBYPHYSICAL\ARCHIVE\ARC00514_0661538341.001 NO  ARCH
D:\STANDBYPHYSICAL\ARCHIVE\ARC00515_0661538341.001 NO  ARCH

NAME                                               APP CREATOR
-------------------------------------------------- --- -------
D:\STANDBYPHYSICAL\ARCHIVE\ARC00516_0661538341.001 NO  ARCH
D:\STANDBYPHYSICAL\ARCHIVE\ARC00517_0661538341.001 NO  ARCH
D:\STANDBYPHYSICAL\ARCHIVE\ARC00518_0661538341.001 NO  ARCH
D:\STANDBYPHYSICAL\ARCHIVE\ARC00519_0661538341.001 NO  ARCH
D:\STANDBYPHYSICAL\ARCHIVE\ARC00520_0661538341.001 NO  ARCH
D:\STANDBYPHYSICAL\ARCHIVE\ARC00521_0661538341.001 NO  ARCH
D:\STANDBYPHYSICAL\ARCHIVE\ARC00522_0661538341.001 NO  ARCH
D:\STANDBYPHYSICAL\ARCHIVE\ARC00523_0661538341.001 NO  ARCH
D:\STANDBYPHYSICAL\ARCHIVE\ARC00524_0661538341.001 NO  ARCH
D:\STANDBYPHYSICAL\ARCHIVE\ARC00525_0661538341.001 NO  ARCH

已选择21行。

把之前的日志给补回来了~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
结论用以前的 备份是可以的

 

 


~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
故障转换角色

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

08.08.25~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~`实验环境

SQL> select db_unique_name,SWITCHOVER_STATUS,database_role,PROTECTION_MODE from
v$database;

DB_UNIQUE_NAME  SWITCHOVER_STATUS    DATABASE_ROLE    PROTECTION_MODE
--------------- -------------------- ---------------- --------------------
standby1        NOT ALLOWED          PHYSICAL STANDBY MAXIMUM PERFORMANCE

 


SQL> col db_unique_name format a15
SQL> select db_unique_name,SWITCHOVER_STATUS,database_role,PROTECTION_MODE from
v$database;

DB_UNIQUE_NAME  SWITCHOVER_STATUS    DATABASE_ROLE    PROTECTION_MODE
--------------- -------------------- ---------------- --------------------
xhtest          SESSIONS ACTIVE      PRIMARY          MAXIMUM PERFORMANCE

SQL>

SQL> select PROCESS,CLIENT_PROCESS,SEQUENCE#,STATUS from v$managed_standby;

PROCESS   CLIENT_P  SEQUENCE# STATUS
--------- -------- ---------- ------------
ARCH      ARCH              0 CONNECTED
ARCH      ARCH              0 CONNECTED
RFS       UNKNOWN           0 RECEIVING
MRP0      N/A               3 WAIT_FOR_LOG

SQL> show parameter log_archive_dest_2(xhtest)

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_2                   string       service=standby1  valid_for=(
                                                 online_logfiles,primary_role)
                                                 db_unique_name=standby1

 

~~~~ V$ARCHIVE_GAP;~~~~~~~~~~~~对 故障转换很重要的一个视图


QL> desc v$archive_gap;
名称                                      是否为空? 类型
----------------------------------------- -------- ----------------------------

THREAD#                                            NUMBER
LOW_SEQUENCE#                                      NUMBER
HIGH_SEQUENCE#                                     NUMBER

SQL> select * from v$archive_gap;~~~~~~~~~视图的目的就是查询哪个 归档没有传过来

未选定行

没有就是没有缺失

 

 

制造一次 故障
SQL> select name from v$archived_log;
F:\归档备份\ARC00019_0663689986.001~~~~~~~~~~~~~~~~~~~主库在这个日志时候崩溃了~~~~~~~~~~最新 归档日志为 19


SQL> select name,creator,applied from v$archived_log;
D:\STANDBYPHYSICAL\ARCHIVE\ARC00016_0663689986.001 ARCH    NO
D:\STANDBYPHYSICAL\ARCHIVE\ARC00017_0663689986.001 ARCH    NO~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~standby没有收到 最新19 这个日志
D:\STANDBYPHYSICAL\ARCHIVE\ARC00018_0663689986.001 ARCH    NO

 

由于实验环境问题
SQL> select * from v$archive_gap;

未选定行~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~还为空,在同一台机器上,若2台机器上回显示出来

 

SQL> select db_unique_name,SWITCHOVER_STATUS,database_role,PROTECTION_MODE from
v$database;

DB_UNIQUE_NAME  SWITCHOVER_STATUS    DATABASE_ROLE    PROTECTION_MODE
--------------- -------------------- ---------------- --------------------~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~不允许转换成primary
standby1        NOT ALLOWED          PHYSICAL STANDBY MAXIMUM PERFORMANCE
下面 我们从primary将 没收到 中断的日志0017拷贝到standby 并且手动注册
alter database register physical logfile 'D:\standbyphysical\archive\ARC00019_06~~~~~~~~~~~~~~~~~~注册上这个日志 靠到 standby目录下
63689986.001'

 


ALTER DATABASE RECOVER MANAGED STANDBY DATABASE …
FINISH FORCE~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~执行后一旦成功 就可以

在standby执行切换了
alter database commit to switchover to primary;~~~~~~~~~~~~~~~~~~~~不会hang住了

FORCE 关键字将会停止当前活动的RFS 进程,以便立刻执行failover。

 


如果我们主库设置的是实时应用lgwr 并且 standby 有 standby logfile ;~~~
那么 alter database recover managed standby database finish  FINISH SKIP STANDBY LOGFILE;

 

或者应用完毕后 强行激活~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
alter database activate standby database;~~~~~~~~~~~~~~~~~~~~~~~~~~让他直接变成primary

 

 

 

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/12020513/viewspace-607595/,如需转载,请注明出处,否则将追究法律责任。

上一篇: 没有了~
请登录后发表评论 登录
全部评论

注册时间:2009-06-26

  • 博文量
    182
  • 访问量
    426862