最近有不少朋友咨询12.1.0.2及其以后的版本使用oradebug去修改scn失败,这里做了一个测试正常情况下确实无法修改(oradebug poke报 ORA-32519 或者 ORA-32521) ,这里进行了一系列修改测试最后修改成功.
数据库版本
SQL>
select
*
from
v$version;
BANNER CON_ID
-------------------------------------------------------------------------------- ----------
Oracle
Database
12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production 0
PL/SQL Release 12.1.0.2.0 - Production 0
CORE 12.1.0.2.0 Production 0
TNS
for
64-
bit
Windows: Version 12.1.0.2.0 - Production 0
NLSRTL Version 12.1.0.2.0 - Production 0
|
oradebug poke测试
SQL> oradebug setmypid
已处理的语句
SQL> oradebug DUMPvar SGA kcsgscn_
kcslf kcsgscn_ [14C8D6270, 14C8D62A0) = 009EA333 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 4C8D5CF0 00000001
SQL> oradebug poke 0x14C8D6274 4 0x00000001
ORA-32521: 对 ORADEBUG 命令 进行语法分析时出错
--或者该提示
SQL> oradebug setmypid
已处理的语句
SQL> oradebug DUMPvar SGA kcsgscn_
kcslf kcsgscn_ [14C8D6270, 14C8D62A0) = 009EAE3D 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 4C8D5CF0 00000001
SQL> oradebug poke 0x14C8D6274 4 0x0000000a
ORA-32519: 权限不足, 无法执行 ORADEBUG 命令: execution
of
ORADEBUG commands
is
disabled
for
this instance
|
通过测试确定oradebug正常情况无法执行poke,不是提示ORA-32521就是提示ORA-32519错误导致scn无法修改.
通过一些修改之后oradebug 修改scn
SQL>
select
dbid,
name
,open_mode,
2 created created,
3 open_mode, log_mode,
4 checkpoint_change#
as
checkpoint_change#,
5 controlfile_type ctl_type,
6 controlfile_created ctl_created,
7 controlfile_change#
as
ctl_change#,
8 controlfile_time ctl_time,
9 resetlogs_change#
as
resetlogs_change#,
10 resetlogs_time resetlogs_time
11
from
v$
database
;
DBID
NAME
OPEN_MODE CREATED OPEN_MODE
LOG_MODE
---------- ---------------------------------------------------- -------------------- -------------- ------------
------------
CHECKPOINT_CHANGE# CTL_TYP CTL_CREATED CTL_CHANGE# CTL_TIME RESETLOGS_CHANGE# RESETLOGS_TIME
------------------ ------- -------------- ----------- -------------- ----------------- --------------
1504692401 XIFENFEI
READ
WRITE 16-8月 -15
READ
WRITE
ARCHIVELOG
10407853
CURRENT
16-8月 -15 10408361 07-7月 -16 1 16-8月 -15
SQL>
select
con_id,file#,checkpoint_change#
from
v$datafile_header;
CON_ID FILE# CHECKPOINT_CHANGE#
---------- ---------- ------------------
1 1 10407853
2 2 9457324
1 3 10407853
2 4 9457324
1 5 10407853
1 6 10407853
3 7 10407853
3 8 10407853
3 9 10407853
4 10 9559964
4 11 9559964
4 12 9559964
3 13 10407853
已选择 13 行。
SQL> shutdown abort;
ORACLE 例程已经关闭。
SQL> startup
ORACLE 例程已经启动。
Total System
Global
Area 3221225472 bytes
Fixed
Size
3837232 bytes
Variable
Size
838861520 bytes
Database
Buffers 2365587456 bytes
Redo Buffers 12939264 bytes
数据库装载完毕。
SQL> oradebug setmypid
已处理的语句
SQL> oradebug DUMPvar SGA kcsgscn_
kcslf kcsgscn_ [14BE16270, 14BE162A0) = 009ED5C6 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 4BE15CF0 00000001
SQL> oradebug poke 0x14BE16274 4 0x0001
BEFORE: [14BE16274, 14BE16276) = 0000
AFTER
: [14BE16274, 14BE16276) = 0001
SQL>
alter
database
open
;
数据库已更改。
SQL>
select
dbid,
name
,open_mode,
2 created created,
3 open_mode, log_mode,
4 checkpoint_change#
as
checkpoint_change#,
5 controlfile_type ctl_type,
6 controlfile_created ctl_created,
7 controlfile_change#
as
ctl_change#,
8 controlfile_time ctl_time,
9 resetlogs_change#
as
resetlogs_change#,
10 resetlogs_time resetlogs_time
11
from
v$
database
;
DBID
NAME
OPEN_MODE CREATED OPEN_MODE
LOG_MODE
---------- ---------------------------------------------------- -------------------- -------------- -----------
------------
CHECKPOINT_CHANGE# CTL_TYP CTL_CREATED CTL_CHANGE# CTL_TIME RESETLOGS_CHANGE# RESETLOGS_TIME
------------------ ------- -------------- ----------- -------------- ----------------- --------------
1504692401 XIFENFEI
READ
WRITE 16-8月 -15
READ
WRITE
ARCHIVELOG
4305478053
CURRENT
16-8月 -15 4305478245 07-7月 -16 1 16-8月 -15
SQL>
select
con_id,file#,checkpoint_change#
from
v$datafile_header;
CON_ID FILE# CHECKPOINT_CHANGE#
---------- ---------- ------------------
1 1 4305478053
2 2 9457324
1 3 4305478053
2 4 9457324
1 5 4305478053
1 6 4305478053
3 7 4305478053
3 8 4305478053
3 9 4305478053
4 10 9559964
4 11 9559964
4 12 9559964
3 13 4305478053
已选择 13 行。
SQL>
select
con_id,file#,checkpoint_change#
from
v$datafile;
CON_ID FILE# CHECKPOINT_CHANGE#
---------- ---------- ------------------
1 1 4305478053
2 2 9457324
1 3 4305478053
2 4 9457324
1 5 4305478053
1 6 4305478053
3 7 4305478053
3 8 4305478053
3 9 4305478053
4 10 9559964
4 11 9559964
4 12 9559964
3 13 4305478053
已选择 13 行。
|
通过上述测试证明scn已经被完美修改.证明我们已经具备了不使用bbed的情况下推进12.1.0.2版本的scn问题,为12c的一系列需要推scn的恢复提供完美技术支持.