GO
RECONFIGURE WITH OVERRIDE
GO
UPDATE sysdatabases SET status = 16 WHERE dbid = db_id(N '数据库名 ')
GO
EXEC SP_CONFIGURE 'allow updates ',0
GO
RECONFIGURE WITH OVERRIDE
GO
有可能是数据库的状态还没有在正常使用的情况，那么可以通过SP_CONFIGURE来获得修改数据库状态的权限，然后把数据库的状态配置成正常情况。
在执行语句完以后要重新启动一次数据库服务才行。以上只做参考！我用这种方法解决了数据库正处于还原状态的问题。
你这个属于主、备机切换
1. 察看主库状态select switchover_status from v$database
收集主库上的临时表空间的情况，原因是备库激活后临时文件可能丢失，需要手工建上去：
col file_name format a40
select file_name,tablespace_name,bytes/1024/1024 from dba_temp_files
FILE_NAME TABLESPACE_NAME BYTES/1024/1024
---------------------------------------- -------------------- ---------------
/data/oradata/alihr/temp01.dbf TEMP 2048
2.切换主库到standby
alter database commit to switchover to physical standby
或：
ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH SESSION SHUTDOWN
shutdown immediate
startup nomount
alter database mount standby database
3.验证要被切换的standby是否接收到switch to 通知
SELECT SWITCHOVER_STATUS FROM V$DATABASE
以前的状态就是SESSIONS ACTIVE,现在就变为TO PRIMARY
4.切换物理standby到主用模式，检查redo log是否创建好
ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY
shutdown immediate
startup
5. standby数据库切换成主库后，检查是否需要、对临时表空间增加临时文件:
先检查临时文件是否丢失：
col file_name format a60
select file_name,tablespace_name from dba_temp_files
把结果前面原主库上的临时文件进行对比，如有丢失则使用如下命令增加：
alter tablespace temp add tempfile '/data/oradata/alihr/temp02.dbf' size 2048M reuse
正常情况下，如果db_file_name_convert参数设置正确的话，11g会自动建立temp file
6.在新的standby机器上
alter system set log_archive_dest_state_2='defer'
alter database recover managed standby database disconnect from session
7.检查主备库中fal参数
fal_server服务名是在standby机器的tnsnames中，指向主库，fal_client是在主库上的tnsnames中，指向standby。
fal参数只在standby机器上生效，所以在standby机器上fal_server指向主库，fal_client机器指向备库.
而主库上的fal参数虽然不生效，但为了避免主库切换成standby时，我们还要修改fal参数，所以现在就设置好，
让fal_server指向现在的standby数据库，fal_client指向自己。
8.在新的主库
alter system set log_archive_dest_state_2='enable'
alter system archive log current
然后到备库上检查备库机器上日志是否正常的传过来了。
9.检查和调整主备库两台机器中的crontab中的数据库备份脚本和删除归档脚本。
=================oracle11g的最大保护和最大可用模式下的切换==================
如果主库是意外宕机的,则直接把备库切换成主库：
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH
shutdown immediate
connect / as sysdba
startup mount
ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY
shutdown immediate
connect / as sysdba
startup mount
ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE AVAILABILITY
alter database open
注意：上面 *** 作中是手工的把数据库重新置成MAXIMIZE AVAILABILITY，否则数据库起来后是最大性能模式。
检查数据库的角色：
select database_role from v$database
原有主库启动后，如果不是硬盘坏，主库上的数据还存在，则把主库转换成standby就可以了：
主库上：
startup mount
recover automatic database
ALTER DATABASE CONVERT TO PHYSICAL STANDBY
shutdown immediate
注意千万不要把主库打开了，否则会导致主库的SCN发生变化，无法转换成standby数据库了。
在把原先的主库转化为standby时，有时可能报如下错误：
SQL> alter database recover managed standby database finish
alter database recover managed standby database finish
*
ERROR at line 1:
ORA-00283: recovery session canceled due to errors
ORA-19909: datafile 1 belongs to an orphan incarnation
ORA-01110: data file 1: '/opt/oracle/oradata/oratest/system01.dbf'
这里把到rman中：
list incarnation of database
reset database to incarnation 1
recover database
reset database to incarnation 2
=========================================================
failover在物理standby的切换
1.检查standby看是否使用了standby log
2.有standby log,执行下面的命令
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH
2.1 没有standby log则不执行上面的
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH SKIP STANDBY LOGFILE
3. 切换
ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY
3.1 如果上面3步骤失败,则
ALTER DATABASE ACTIVATE STANDBY DATABASE
4. 重启db
shutdown immediate
startup
switchover的方法：
主库和物理standby的切换
1.察看主库状态
SELECT SWITCHOVER_STATUS FROM V$DATABASE
2.切换
ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY
3.原主库
shutdown immediate
startup nomount
alter database mount standby database
4.验证要被切换的standby是否接收到switch to 通知
SELECT SWITCHOVER_STATUS FROM V$DATABASE
增加online redo日志
5.切换物理standby到主
ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY
shutdown immediate
startup
6.在新的standby机器（也就是老的主库）
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION
7.在新的主库
alter system archive log current
========active standby database===========
当:
alter database activate standby database
原来的主库只能通过闪回转化成standby database，但要求数据库的flashback打开。
在新主库上：
select to_char(standby_became_primary_scn) from v$database
在旧主库上：
SHUTDOWN IMMEDIATE
STARTUP MOUNT
FLASHBACK DATABASE TO SCN standby_became_primary_scn
ALTER DATABASE CONVERT TO PHYSICAL STANDBY
select max(sequence#) from v$log_history where RESETLOGS_TIME=(select max(RESETLOGS_TIME) from v$log_history)
实例恢复：
recover managed standby database disconnect using current logfile
=================lgwr设置====================================
alter system set log_archive_dest_3 = 'location=/disk3/arch/bopscha reopen=2 MAX_FAILURE=3'
alter system set log_archive_dest_state_3 = alternate
alter system set log_archive_dest_1 = 'location=/disk2/arch/bopscha alternate=log_archive_dest_3 reopen=60 MAX_FAILURE=5'
*.log_archive_dest_2='SERVICE=DTMRT LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=DTMRT'
alter database add standby logfile group 4 ('/oracle/u02/ORA10GDG/STANDBYRD01.LOG') size 200M
select group#,thread#,sequence#,archived,status from v$standby_log
alter database set standby database to maximize {availability | performance | protection}
select protection_mode from v$database
ORA-19527:
LOG_FILE_NAME_CONVERT='/u02/oracle/oradata/oract/','/u02/oracle/oradata/oract/'
alter system set log_archive_dest_2='SERVICE=bopsteststb lgwr sync affirm'
select frequency, duration from v$redo_dest_resp_histogram where dest_id=2 and frequency>1
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL
SQL> ALTER DATABASE OPEN
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT
alter database convert to snapshot standby
alter database convert to physical standby
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT
alter database commit to switchover to physical standby
alter database activate standby database finish apply
alter database recover managed standby database finish
旧主库转换成standby的步骤：
在新主库上：
select to_char(standby_became_primary_scn) from v$database
在旧主库上：
SHUTDOWN IMMEDIATE
STARTUP MOUNT
FLASHBACK DATABASE TO SCN standby_became_primary_scn
ALTER DATABASE CONVERT TO PHYSICAL STANDBY
ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH FORCE
切换：1.停止Redo Apply
如果备库正处于RedoApply过程，需要先取消。
sys@ora11gdg>alter database recover managed standby database cancel
Database altered.
2.查看当前备库状态确保备库处于MOUNTED状态
sys@ora11gdg>select database_role,open_mode from v$database
DATABASE_ROLEOPEN_MODE
---------------- --------------------
PHYSICAL STANDBY MOUNTED
此时备库是物理备库角色，运行模式是MOUNTED。
3.确保闪回恢复区已指定
友情提示：实现SnapshotStandby数据库功能并不需要开启主库和备库的闪回数据库（Flashback Database）功能，与是否开启闪回数据库无关。
sys@ora11gdg>show parameter db_recovery_file_dest
NAME TYPE VALUE
--------------------------- ------------ ------------------------------------
db_recovery_file_dest string /u01/app/oracle/flash_recovery_area
db_recovery_file_dest_size big integer 3852M
确认主库闪回功能并未开启
sys@ora11g>select FLASHBACK_ON from v$database
FLASHBACK_ON
------------------
NO
确认备库闪回功能并未开启
sys@ora11gdg>select FLASHBACK_ON from v$database
FLASHBACK_ON
------------------
NO
4.调整备库到SnapshotStandby数据库状态
只需要执行一条非常简单的SQL命令便可以将备库调整到Snapshot Standby数据库。
sys@ora11gdg>alter database convert to snapshot standby
Database altered.
sys@ora11gdg>select database_role,open_mode from v$database
DATABASE_ROLEOPEN_MODE
---------------- --------------------
SNAPSHOT STANDBY MOUNTED
5.将备库置于对外可读写状态
sys@ora11gdg>alter database open
Database altered.
sys@ora11gdg>select database_role,open_mode from v$database
DATABASE_ROLEOPEN_MODE
---------------- --------------------
SNAPSHOT STANDBY READ WRITE
一套全新的可读写数据库展现在我们面前。
6.分析切换过程中的日志信息
ora11g主库alert日志：
Mon Mar 19 18:46:28 2012
LNS: Attempting destination LOG_ARCHIVE_DEST_2 network reconnect (3135)
LNS: Destination LOG_ARCHIVE_DEST_2 network reconnect abandoned
Errors in file/u01/app/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_nsa2_27302.trc:
ORA-03135: connection lost contact
Error 3135 for archive log file 2 to 'ora11gdg'
Errors in file /u01/app/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_nsa2_27302.trc:
ORA-03135: connection lost contact
LNS: Failed to archive log 2 thread 1 sequence 50 (3135)
Errors in file/u01/app/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_nsa2_27302.trc:
ORA-03135: connection lost contact
ora11gdg备库alert日志：
Mon Mar 19 18:46:26 2012
alter database convert to snapshot standby
Starting background process RVWR
Mon Mar 19 18:46:26 2012
RVWR started with pid=26, OS id=8824
Allocated 3981204 bytes in shared pool for flashback generation buffer
Created guaranteed restore point SNAPSHOT_STANDBY_REQUIRED_03/19/201218:46:26
krsv_proc_kill: Killing 3 processes (all RFS)
Begin: Standby Redo Logfile archival
End: Standby Redo Logfile archival
RESETLOGS after complete recovery through change 1472476
Resetting resetlogs activation ID 4174194338 (0xf8cd26a2)
Online log /u01/app/oracle/oradata/ora11gdg/redo01.log: Thread 1 Group 1 waspreviously cleared
Online log /u01/app/oracle/oradata/ora11gdg/redo02.log: Thread 1 Group 2 waspreviously cleared
Online log /u01/app/oracle/oradata/ora11gdg/redo03.log: Thread 1 Group 3 waspreviously cleared
Standby became primary SCN: 1472474
Mon Mar 19 18:46:29 2012
Setting recovery target incarnation to 5
CONVERT TO SNAPSHOT STANDBY: Complete - Database mounted as snapshot standby
Completed: alter database convert to snapshot standby
关键的一行提示信息“Created guaranteed restore point SNAPSHOT_STANDBY_REQUIRED_03/19/201218:46:26”，这里给出了我们转换成snapshot的时刻，便于后面的回切。
7.测试备库处于SnapshotStandby数据库对主库日志的接收
当主库切换日志时，备库依然可以接收到日志，只是并不应用
1）主库切换日志
sys@ora11g>alter system switch logfile
System altered.
2）主库记录的alert日志内容
ora11g主库alert日志：
Mon Mar 19 18:52:00 2012
Thread 1 cannot allocate new log, sequence 52
Private strand flush not complete
Current log# 3 seq# 51 mem# 0: /u01/app/oracle/oradata/ora11g/redo03.log
Mon Mar 19 18:52:00 2012
ARC3: Standby redo logfile selected for thread 1 sequence 50 for destinationLOG_ARCHIVE_DEST_2
Thread 1 advanced to log sequence 52 (LGWR switch)
Current log# 1 seq# 52 mem# 0: /u01/app/oracle/oradata/ora11g/redo01.log
Mon Mar 19 18:52:03 2012
Archived Log entry 91 added for thread 1 sequence 51 ID 0xf8cd26a2 dest 1:
Mon Mar 19 18:52:03 2012
LNS: Standby redo logfile selected for thread 1 sequence 51 for destinationLOG_ARCHIVE_DEST_2
LNS: Standby redo logfile selected for thread 1 sequence 52 for destinationLOG_ARCHIVE_DEST_2
ora11gdg备库alert日志：
Mon Mar 19 18:52:00 2012
RFS[5]: Assigned to RFS process 9174
RFS[5]: Identified database type as 'snapshot standby': Client is ARCH pid27296
Mon Mar 19 18:52:00 2012
RFS[6]: Assigned to RFS process 9176
RFS[6]: Identified database type as 'snapshot standby': Client is ARCH pid27300
RFS[6]: Selected log 4 for thread 1 sequence 50 dbid -120744030 branch778023141
Mon Mar 19 18:52:00 2012
Archived Log entry 47 added for thread 1 sequence 50 ID 0xf8cd26a2 dest 1:
Mon Mar 19 18:52:03 2012
RFS[7]: Assigned to RFS process 9180
RFS[7]: Identified database type as 'snapshot standby': Client is LGWR ASYNCpid 27302
RFS[7]: Selected log 4 for thread 1 sequence 51 dbid -120744030 branch778023141
Mon Mar 19 18:52:04 2012
Archived Log entry 48 added for thread 1 sequence 51 ID 0xf8cd26a2 dest 1:
RFS[7]: Selected log 4 for thread 1 sequence 52 dbid -120744030 branch778023141
3）查看主库和备库归档目录下的日志文件内容
（1）主库归档日志文件
ora11g@secdb /home/oracle/arch/ora11g$ ls -ltr
total 879M
……省略其他……
-rw-r----- 1 oracle oinstall 1.1M Mar 19 18:51 1_50_778023141.arc
-rw-r----- 1 oracle oinstall 363K Mar 19 18:52 1_51_778023141.arc
（2）备库归档日志文件
ora11g@secdb /home/oracle/arch/ora11gdg$ ls -ltr
total 847M
……省略其他……
-rw-r----- 1 oracle oinstall 1.1M Mar 19 18:52 1_50_778023141.arc
-rw-r----- 1 oracle oinstall 363K Mar 19 18:52 1_51_778023141.arc
可见，备库已经接受到主库发过来的日志。
8.在SnapshotStandby数据创建用户和表并初始化数据
sys@ora11gdg>create user ocmu identified by ocmu
User created.
secooler@ora11gdg>grant dba to ocmu
Grant succeeded.
secooler@ora11gdg>conn ocmu/ocmu
Connected.
ocmu@ora11gdg>create table t (x varchar2(8))
Table created.
ocmu@ora11gdg>insert into t values ('Secooler')
1 row created.
ocmu@ora11gdg>commit
Commit complete.
ocmu@ora11gdg>select * from t
X
--------
Secooler
欢迎分享，转载请注明来源：内存溢出
微信扫一扫
支付宝扫一扫
评论列表（0条）