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

最新推荐文章于 2024-10-13 20:14:57 发布

cuiye1492

最新推荐文章于 2024-10-13 20:14:57 发布

阅读量144

点赞数

文章标签：数据库操作系统运维

主要研究几个管理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;~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

已选择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      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      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;

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 也就是现在的主库）

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;

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 例程已经启动。

数据库已更改。

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 例程已经启动。

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;

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;

已选择6行。

SQL> alter database recover managed standby database cancel;

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

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

数据库已更改。

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 例程已经启动。

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 例程已经启动。

数据库已更改。

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;

SQL> alter system switch logfile;（XHTEST）

系统已更改。

SQL>

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

已选择7行。

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

数据库已更改。

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

已选择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 例程已经启动。

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;

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

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 例程已经启动。

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 例程已经启动。

数据库已更改。

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;

已选择9行。

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

已选择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;

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;

已选择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;

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 例程已经启动。

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 例程已经启动。

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;

已选择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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/12020513/viewspace-607595/