oracle数据库切换实验
切换前准备：
1.切换前对主库做全备
run{
allocate channel c1 type disk;
allocate channel c2 type disk;
BACKUP DATABASE format
'C:\Users\Administrator\Desktop\tostandby\paydb_%d_%T_%s_%p_%u.bak';
sql 'alter system archive log current';
sql 'alter system archive log current';
sql 'alter system archive log current';
backup filesperset 3 archivelog all tag 'arch'
format 'C:\Users\Administrator\Desktop\tostandby\arch_%d_%T_%s_%p_%u.bak';
backup current controlfile format
''C:\Users\Administrator\Desktop\tostandby\taobao_control__%s_%p_%t.ctl';
release channel c1;
release channel c2;
}
2.备份参数文件：
create pfile='C:\Users\Administrator\Desktop\tostandby\pri.ora'
from memory;
3.检查参数脚本（两边）
set linesize 200
set pagesize 100
col name for a35
col value for a80
select name,value from v$parameter where name like
'log_archive_dest_%' and value is not null ;
pr:
SQL> SQL> SQL> SQL>
NAME
VALUE
-----------------------------------
----------------------------------------------------------------------
----------
log_archive_dest_1
location=E:\app\Administrator\oradata\taobao\arch VALID_FOR=(ALL_LOGF
ILES,ALL_R
OLES) DB_UNIQUE_NAME=taobao
log_archive_dest_2
SERVICE=taobaodg LGWR ASYNC NOAFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMA
RY_ROLE) D
B_UNIQUE_NAME=taobaodg
log_archive_dest_state_1
enable
std:
NAME
VALUE
-----------------------------------
-------------------------------------------------------------------------------
-
log_archive_dest_1
location=E:\app\Administrator\oradata\taobao\arch VALID_FOR=(ALL_LOGFILES,ALL_
R
OLES) DB_UNIQUE_NAME=taobaodg
log_archive_dest_2
SERVICE=taobao LGWR ASYNC NOAFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB
_
UNIQUE_NAME=taobao
set linesize 200
set pagesize 100
col name for a35
col value for a80
select name,value from v$parameter where name like
'fal_server%' or name like '%db_unique_name%' or name like '%job%' or name like
'%file_name_convert%' or name like '%standby_file_management%' or name like 'log_archive_config%';
eg:
--pri:
NAME
VALUE
-----------------------------------
----------------------------------------------------------------------
----------
db_file_name_convert
E:\app\Administrator\oradata\taobao, E:\app\Administrator\oradata\tou
biao
log_file_name_convert
E:\app\Administrator\oradata\taobao, E:\app\Administrator\oradata\tou
biao
fal_server
taobaodg
log_archive_config
DG_CONFIG=(taobao,taobaodg)
standby_file_management
AUTO
job_queue_processes
1000
db_unique_name
taobao
--std:
NAME
VALUE
-----------------------------------
------------------------------------------------------------------------------
-
db_file_name_convert
E:\app\Administrator\oradata\taobao, E:\app\Administrator\oradata\taobao
log_file_name_convert
E:\app\Administrator\oradata\taobao, E:\app\Administrator\oradata\taobao
fal_server
taobao
log_archive_config
DG_CONFIG=(taobao,taobaodg)
standby_file_management
AUTO
job_queue_processes
1000
db_unique_name
taobaodg
已选择7行。
5.主库查看
set linesize 200 pagesize 100
col dest_name for a25
col error for a45
select dest_id,dest_name,status,error from
v$archive_dest;
eg:
DEST_ID
DEST_NAME
STATUS
ERROR
------- -------------------------
------------------ ---------------------------------------------
1
LOG_ARCHIVE_DEST_1 VALID
2
LOG_ARCHIVE_DEST_2 VALID
6.检查主从最大sequence，查询出来的结果须一致。
SELECT UNIQUE THREAD#, MAX(SEQUENCE#)
OVER(PARTITION BY THREAD#) LAST FROM V$ARCHIVED_LOG;
eg:
#pri:
THREAD# LAST
-------- ----------
1 61
#std:
THREAD# LAST
-------- ----------
1 61
7.从库查看日志应用状态
select process,client_process,sequence#,status
from v$managed_standby;
select * from v$archive_gap;
eg:
SQL> select
process,client_process,sequence#,status from v$managed_standby;
PROCESS
CLIENT_PROCESS SEQUENCE# STATUS
------------------ ---------------- ----------
------------------------
ARCH
ARCH
60 CLOSING
ARCH
ARCH
59 CLOSING
ARCH
ARCH
0 CONNECTED
ARCH
ARCH
61 CLOSING
RFS
ARCH
0 IDLE
RFS
LGWR
62 IDLE
RFS
UNKNOWN
0 IDLE
MRP0
N/A
62 APPLYING_LOG
已选择8行。
SQL> select * from v$archive_gap;
未选定行
8.检查主从临时表空间是否一致
set linesize 200
col filename for a55
col bytes for 999999999999999
col tablespace for a20
SELECT TMP.NAME FILENAME, BYTES, TS.NAME
TABLESPACE FROM V$TEMPFILE TMP, V$TABLESPACE TS WHERE TMP.TS#=TS.TS#;
eg:
pri:
FILENAME
BYTES TABLESPACE
-------------------------------------------------------
---------------- --------------------
E:\APP\ADMINISTRATOR\ORADATA\TAOBAO\TEMP01.DBF
920649728 TEMP
std:
FILENAME
BYTES TABLESPACE
-------------------------------------------------------
---------------- --------------------
E:\APP\ADMINISTRATOR\ORADATA\TAOBAO\TEMP01.DBF
20971520 TEMP
9.检查spfile
show parameter spfile
eg:
pri:
NAME
TYPE
VALUE
------------------------------------
---------------------- ------------------------------
spfile
string
E:\APP\ADMINISTRATOR\PRODUCT\1
1.2.0\DBHOME_1\DATABASE\SPFILE
TAOBAO.ORA
std:
NAME
TYPE
VALUE
------------------------------------
---------------------- ------------------------------
spfile
string
E:\APP\ADMINISTRATOR\PRODUCT\1
1.2.0\DBHOME_1\DATABASE\SPFILE
TAOBAODG.ORA
10.检查tnsnames.ora
eg:
pri:
E:\app\Administrator\product\11.2.0\dbhome_1\NETWORK\ADMIN
# tnsnames.ora Network Configuration File:
E:\app\Administrator\product\11.2.0\dbhome_1\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.
TAOBAO =
(DESCRIPTION =
(ADDRESS = (PROTOCOL =
TCP)(HOST = 10.11.13.5)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = taobao)
)
)
TAOBAODG =
(DESCRIPTION =
(ADDRESS = (PROTOCOL =
TCP)(HOST = 10.11.13.6)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = taobaodg)
)
)
std:
# tnsnames.ora Network Configuration File:
E:\app\Administrator\product\11.2.0\dbhome_1\NETWORK\ADMIN\tnsnames.ora
# Generated by Oracle configuration tools.
TAOBAO =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS =
(PROTOCOL = TCP)(HOST = 10.11.13.5)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = taobao)
)
)
TAOBAODG =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS =
(PROTOCOL = TCP)(HOST = 10.11.13.6)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = taobaodg)
)
)
11.检查是否有error(主备执行）
set linesize 200 pagesize 100
col dest_name for a23
col error for a12
col DB_UNIQUE_NAME for a15
col APPLIED_SCN for 9999999999999
select DB_UNIQUE_NAME , COMPRESSION
,APPLIED_SCN ,status, ERROR , PROCESS ,NET_TIMEOUT, DEST_NAME
from v$archive_dest order by status desc;
12.检查是否有offline数据文件
SELECT NAME FROM V$DATAFILE WHERE STATUS='OFFLINE';
eg:
pri:
SQL> SELECT NAME FROM V$DATAFILE WHERE
STATUS='OFFLINE';
no rows selected
std:
SQL> SELECT NAME FROM V$DATAFILE WHERE
STATUS='OFFLINE';
no rows selected
13.检查是否有正在运行的job
SELECT * FROM DBA_JOBS_RUNNING;
14. 检查是否有活动的会话
SELECT COUNT(*) FROM V$SESSION WHERE USERNAME IS
NOT NULL;
eg:
pri:
SQL> SELECT COUNT(*) FROM V$SESSION WHERE
USERNAME IS NOT NULL;
COUNT(*)
----------
10
std:
SQL> SELECT COUNT(*) FROM V$SESSION WHERE
USERNAME IS NOT NULL;
COUNT(*)
---------
4
15.检查详细进程
col username format a15
col machine format a20
col OSUSER format a20
col program format a30
select inst_id, username, machine, osuser,
program, count(1) from gv$session where osuser not in
('oracle', 'grid', 'root') and program not in
('plsqldev.exe') group by inst_id, username, machine, osuser, program
order by inst_id, username, machine, osuser, program;
eg：
pri:
SQL> SQL>
INST_ID
USERNAME
MACHINE
OSUSER
PROGRAM
1)
-------- --------------- --------------------
-------------------- ----------------------------
--
1
CARNETBID WCF\WCF
Administrator CNBService.exe
1
1
DBSNMP
WORKGROUP\ORACLE1 NT AUTHORITY\SYSTEM emagent.exe
2
1
SENDCARD
WCF\WCF
Administrator CNBService.exe
1
1
SYS
WORKGROUP\ORACLE1 ORACLE1\Administrato sqlplus.exe
1
r
1
SYSMAN
Oracle1
ORACLE1$
OMS
5
1
ORACLE1
SYSTEM
ORACLE.EXE (ARC0)
1
1
ORACLE1
SYSTEM
ORACLE.EXE (ARC1)
1
1
ORACLE1
SYSTEM
ORACLE.EXE (ARC2)
1
1
ORACLE1
SYSTEM
ORACLE.EXE (ARC3)
1
1
ORACLE1
SYSTEM
ORACLE.EXE (CJQ0)
1
1
ORACLE1
SYSTEM
ORACLE.EXE (CKPT)
1
1
ORACLE1
SYSTEM
ORACLE.EXE (DBRM)
1
1
ORACLE1
SYSTEM
ORACLE.EXE (DBW0)
1
1
ORACLE1
SYSTEM
ORACLE.EXE (DIA0)
1
1
ORACLE1
SYSTEM
ORACLE.EXE (DIAG)
1
1
ORACLE1
SYSTEM
ORACLE.EXE (GEN0)
1
1
ORACLE1
SYSTEM
ORACLE.EXE (LGWR)
1
1
ORACLE1
SYSTEM
ORACLE.EXE (MMAN)
1
1
ORACLE1
SYSTEM
ORACLE.EXE (MMNL)
1
1
ORACLE1
SYSTEM
ORACLE.EXE (MMON)
1
1
ORACLE1
SYSTEM
ORACLE.EXE (NSA2)
1
1
ORACLE1
SYSTEM
ORACLE.EXE (PMON)
1
1
ORACLE1
SYSTEM
ORACLE.EXE (PSP0)
1
1
ORACLE1
SYSTEM
ORACLE.EXE (Q000)
1
1
ORACLE1
SYSTEM
ORACLE.EXE (Q001)
1
1
ORACLE1
SYSTEM
ORACLE.EXE (QMNC)
1
1
ORACLE1
SYSTEM
ORACLE.EXE (RECO)
1
1
ORACLE1
SYSTEM
ORACLE.EXE (SMCO)
1
1
ORACLE1
SYSTEM
ORACLE.EXE (SMON)
1
1
ORACLE1
SYSTEM
ORACLE.EXE (VKTM)
1
1
ORACLE1
SYSTEM
ORACLE.EXE (W000)
1
选择31行。
SQL>
std:
INST_ID
USERNAME
MACHINE
OSUSER
PROGRAM
COUNT(1)
-------- --------------- --------------------
-------------------- ------------------------------ ----------
1
PUBLIC
ORACLE1
NT AUTHORITY\ANONYMO ORACLE.EXE
3
US LOGON
1 SYS
WORKGROUP\ORACLE2 ORACLE2\Administrato
sqlplus.exe
1
r
1
ORACLE2
SYSTEM
ORACLE.EXE
(ARC0)
1
1
ORACLE2
SYSTEM
ORACLE.EXE
(ARC1)
1
1
ORACLE2
SYSTEM
ORACLE.EXE
(ARC2)
1
1
ORACLE2
SYSTEM
ORACLE.EXE
(ARC3)
1
1
ORACLE2
SYSTEM
ORACLE.EXE
(CKPT)
1
1
ORACLE2
SYSTEM
ORACLE.EXE
(DBRM)
1
1
ORACLE2
SYSTEM
ORACLE.EXE
(DBW0)
1
1
ORACLE2
SYSTEM
ORACLE.EXE
(DIA0)
1
1
ORACLE2
SYSTEM
ORACLE.EXE
(DIAG)
1
1
ORACLE2
SYSTEM
ORACLE.EXE
(GEN0)
1
1
ORACLE2
SYSTEM
ORACLE.EXE
(LGWR)
1
1
ORACLE2
SYSTEM
ORACLE.EXE
(MMAN)
1
1
ORACLE2
SYSTEM
ORACLE.EXE (MMNL)
1
1
ORACLE2
SYSTEM
ORACLE.EXE
(MMON)
1
1
ORACLE2
SYSTEM
ORACLE.EXE
(MRP0)
1
1
ORACLE2
SYSTEM
ORACLE.EXE
(PMON)
1
1
ORACLE2
SYSTEM
ORACLE.EXE
(PR00)
1
1
ORACLE2
SYSTEM
ORACLE.EXE
(PR01)
1
1
ORACLE2
SYSTEM
ORACLE.EXE
(PR02)
1
1
ORACLE2
SYSTEM
ORACLE.EXE (PR03)
1
1
ORACLE2
SYSTEM
ORACLE.EXE
(PR04)
1
1
ORACLE2
SYSTEM
ORACLE.EXE
(PR05)
1
1
ORACLE2
SYSTEM
ORACLE.EXE (PR06)
1
1
ORACLE2
SYSTEM
ORACLE.EXE
(PR07)
1
1
ORACLE2
SYSTEM
ORACLE.EXE
(PR08)
1
1
ORACLE2
SYSTEM
ORACLE.EXE
(PSP0)
1
1
ORACLE2
SYSTEM
ORACLE.EXE
(RECO)
1
1
ORACLE2
SYSTEM
ORACLE.EXE
(SMON)
1
1
ORACLE2
SYSTEM
ORACLE.EXE
(VKTM)
1
选择31行。
16.关闭主从其他节点(RAC关闭其他节点，留下一个节点用于切换；文件系统不需要执行此步骤)
16.1 主库数据库切换状态检查
select
database_role,protection_mode,open_mode,switchover_status from v$database;
eg：
pri:
DATABASE_ROLE
PROTECTION_MODE
OPEN_MODE
SWITCHOVER_STATUS
--------------------------------
------------------------- ----------------------------------------
-------------------------
PRIMARY
MAXIMUM PERFORMANCE READ
WRITE
SESSIONS ACTIVE
std:
DATABASE_ROLE
PROTECTION_MODE
OPEN_MODE
SWITCHOVER_STATUS
--------------------------------
------------------------- ------------------------- ------------------
----------
PHYSICAL
STANDBY
MAXIMUM PERFORMANCE READ ONLY WITH
APPLY NOT ALLOWED
17. 主库执行切换成备库命令
alter database commit to switchover to physical
standby with session shutdown;
eg：
SQL> alter database commit to switchover to
physical standby with session shutdown;
数据库已更改。
17.1. 新备库检查
startup nomount
eg:
SQL> startup nomount
ORACLE 例程已经启动。
Total System Global Area 5144301568 bytes
Fixed
Size
2290160 bytes
Variable
Size
1023413776 bytes
Database
Buffers 4110417920 bytes
Redo
Buffers
8179712 bytes
alter database mount standby database;
eg:
SQL> alter database mount standby database;
数据库已更改。
select
dbid,name,switchover_status,db_unique_name,database_role,open_mode,current_scn
from v$database;
eg:
SQL> select
dbid,name,switchover_status,db_unique_name,database_role,open_mode,current_scn
from v$database;
DBID
NAME
SWITCHOVER_STATUS
DB_UNIQUE_NAME
DATABASE_ROLE
OPEN_MODE
CURRENT_SCN
---------- -----------------------------------
------------------------- --------------- --------------------------------
---------------------------------------- -----------
1213159909 TAOBAO
RECOVERY NEEDED taobao
PHYSICAL
STANDBY
MOUNTED
1378426
select open_mode,database_role,switchover_status
from v$database;
SQL> select
open_mode,database_role,switchover_status from v$database;
OPEN_MODE
DATABASE_ROLE
SWITCHOVER_STATUS
----------------------------------------
-------------------------------- -------------------------
MOUNTED
PHYSICAL STANDBY
RECOVERY NEEDED
18.备库切换状态检查
select
database_role,protection_mode,open_mode,switchover_status from v$database;
eg:
SQL> select
database_role,protection_mode,open_mode,switchover_status from v$database;
DATABASE_ROLE
PROTECTION_MODE
OPEN_MODE
SWITCHOVER_STATUS
--------------------------------
------------------------- -------------------------
------------------------------
----------
PHYSICAL
STANDBY
MAXIMUM PERFORMANCE READ ONLY WITH
APPLY TO PRIMARY
18.1 备库执行切换成主命令
alter database commit to switchover to primary
with session shutdown;
eg:
SQL> alter database commit to switchover to
primary with session shutdown;
数据库已更改
select
database_role,protection_mode,open_mode,switchover_status from v$database;
eg:
SQL> select
database_role,protection_mode,open_mode,switchover_status from v$database;
DATABASE_ROLE
PROTECTION_MODE
OPEN_MODE
SWITCHOVER_STATUS
--------------------------------
------------------------- -------------------------
------------------------------
----------
PRIMARY
MAXIMUM PERFORMANCE
MOUNTED
NOT ALLOWED
19. 新备库执行应用归档。
alter database open;
eg:
SQL> alter database open;
数据库已更改。
alter database recover managed standby database
using current logfile disconnect from session;
eg:
SQL> alter database recover managed standby
database using current logfile disconnect from session;
数据库已更改。
20.新主库创建测试插入数据测试：
create table t1 (id int);
SQL> insert into t1 values( 2);
已创建 1 行。
SQL> commit;
提交完成。
SQL> select count(1) from t1;
COUNT(1)
----------
2
SQL>
20.1. 切换日志
alter system switch logfile；
21.新备库查询
SQL> select count(1) from t1;
COUNT(1)
----------
2
SQL>
21.主备再次切换前检查是否有error(主备执行）
set linesize 200 pagesize 100
col dest_name for a23
col error for a12
col DB_UNIQUE_NAME for a15
col APPLIED_SCN for 9999999999999
select DB_UNIQUE_NAME , COMPRESSION
,APPLIED_SCN ,status, ERROR , PROCESS ,NET_TIMEOUT, DEST_NAME
from v$archive_dest order by status desc;
22.主库数据库切换状态检查
select
database_role,protection_mode,open_mode,switchover_status from v$database;
23.主库执行切换成备库命令
alter database commit to switchover to physical
standby with session shutdown;
24.新备库检查
startup nomount
alter database mount standby database;
select
dbid,name,switchover_status,db_unique_name,database_role,open_mode,current_scn
from v$database;
select
database_role,protection_mode,open_mode,switchover_status from v$database;
25.备库切换状态检查
select
database_role,protection_mode,open_mode,switchover_status from v$database;
26.备库执行切换成主命令
alter database commit to switchover to primary
with session shutdown;
27.备库切换检查
select
database_role,protection_mode,open_mode,switchover_status from v$database;
28. 新备库执行应用归档。
alter database open;
alter database recover managed standby database
using current logfile disconnect from session;
29.新主库创建测试插入数据测试：
SQL> insert into t1 values( 4);
已创建 1 行。
SQL> commit;
提交完成。
SQL> select count(1) from t1;
COUNT(1)
----------
2
SQL>
20.1. 切换日志
alter system switch logfile；
21.新备库查询
SQL> select count(1) from t1;
COUNT(1)
----------
2
SQL>
浙公网安备 33010602011771号
