老熊的三分地-Oracle及数据恢复

作了一个实验，删除unused column时出现异常情况：

SQL> create table test1.t1 (a number,b number, c number);

Table created.

SQL> alter table test1.t1 set unused (a);

Table altered.

SQL> alter table test1.t1 set unused (b);

Table altered.

SQL> select object_id from dba_objects where object_name='T1';

 OBJECT_ID
----------
      6578

SQL> select col#,segcol#,name,intcol# from col$ where obj#=6578;

      COL#    SEGCOL# NAME                            INTCOL#
---------- ---------- ---------------------------- ----------
         0          1 SYS_C00001_08011916:08:41$            1
         0          2 SYS_C00002_08011916:08:47$            2
         1          3 C                                     3

SQL> alter table test1.t1 drop (b);
alter table test1.t1 drop (b)
                           *
ERROR at line 1:
ORA-00904: "B": invalid identifier

SQL> alter table test1.t1 drop (a);
alter table test1.t1 drop (a)
                           *
ERROR at line 1:
ORA-00904: "A": invalid identifier

SQL> alter table test1.t1 drop (SYS_C00002_08011916:08:47$);
alter table test1.t1 drop (SYS_C00002_08011916:08:47$)
                                              *
ERROR at line 1:
ORA-00907: missing right parenthesis

SQL> alter table test1.t1 drop ("SYS_C00002_08011916:08:47$");

Table altered.

SQL> select col#,segcol#,name,intcol# from col$ where obj#=6578;

      COL#    SEGCOL# NAME                            INTCOL#
---------- ---------- ---------------------------- ----------
         1          0 C                                     0

这里segcol#和intcol#居然为0.

SQL> select * from test1.t1;

no rows selected

SQL> insert into test1.t1 values (1);

1 row created.

SQL> select * from test1.t1;

SQL> select * from test1.t1;

         C
----------

SQL> commit;

Commit complete.

SQL> select * from test1.t1;

         C
----------

SQL> select * from test1.t1;

         C
----------

SQL> insert into test1.t1 values (2);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from test1.t1;

         C
----------

SQL> select count(*) from test1.t1;

 COUNT(*)
----------
         2

虽然可以看到有两行数据，但是查询出的值均为NULL。

将数据块DUMP数据，看到的列数居然是0.：

tab 0, row 0, @0xf95
tl: 3 fb: --H-FL-- lb: 0x1  cc: 0
tab 0, row 1, @0xf92
tl: 3 fb: --H-FL-- lb: 0x2  cc: 0

可见此处出现了异常。
而按正常做法则没有什么问题：

SQL> drop table t1;

Table dropped.

SQL> create table test1.t1 (a number,b number,c number, d number);

Table created.

SQL> alter table test1.t1 set unused (a);

Table altered.

SQL> alter table test1.t1 set unused (b);

Table altered.

SQL> alter table test1.t1 drop (d);

Table altered.

SQL> insert into test1.t1 values (1);

1 row created.

SQL> insert into test1.t1 values (2);

1 row created.

SQL> select * from test1.t1;

         C
----------
         1
         2

看起来还是不要删除本身就已经是UNUSED的列。

下面再看看这种做法对已经有数据的表的影响：

SQL> drop table test1.t1;

Table dropped.

SQL> create table test1.t1 as select rownum a,rownum*10 b,rownum*100 c from dba_
objects where rownum<=100;

Table created.

SQL> alter table test1.t1 set unused (b);

Table altered.

SQL> alter table test1.t1 set unused (c);

Table altered.

SQL> select object_id from dba_objects where object_name='T1';

 OBJECT_ID
----------
      6581

SQL> select col#,name,segcol#,intcol# from col$ where obj#=6581;

      COL# NAME                            SEGCOL#    INTCOL#
---------- ---------------------------- ---------- ----------
         1 A                                     1          1
         0 SYS_C00002_08011916:34:21$            2          2
         0 SYS_C00003_08011916:34:23$            3          3

SQL> alter table test1.t1 drop ("SYS_C00003_08011916:34:23$");

Table altered.

SQL> select col#,name,segcol#,intcol# from col$ where obj#=6581;

      COL# NAME                            SEGCOL#    INTCOL#
---------- ---------------------------- ---------- ----------
         1 A                                     1          1

SQL> select * from test1.t1 where rownum<=10;

         A
----------

现在数据仍然是空，出现了异常。因此在9i下强制删除unused column是有问题的。我认为这里一个BUG。不知道10g是不是还是这种情况。

删除unused column的正确方法仍然是：
ALTER TABLE ...... DROP UNUSED COLUMNS

下面以例子说话：

SQL> create table test1.unused_test  as select rownum a,rownum*2 b,rownum*10 c from dba_objects where rownum<=100;

Table created.

看看数据字典：

SQL> select column_name,data_type,column_id,hidden_column,segment_column_id seg_
cid,internal_column_id internal_cid from dba_tab_cols where owner='TEST1' and ta
ble_name='UNUSED_TEST';

COLUMN_NAM DATA_TYPE            COLUMN_ID HIDDEN    SEG_CID INTERNAL_CID
---------- -------------------- --------- ------ ---------- ------------
A          NUMBER                       1 NO              1            1
B          NUMBER                       2 NO              2            2
C          NUMBER                       3 NO              3            3

SQL> select column_name,data_type,column_id from dba_tab_columns where wner='TE
ST1' and table_name='UNUSED_TEST';

COLUMN_NAM DATA_TYPE            COLUMN_ID
---------- -------------------- ---------
A          NUMBER                       1
B          NUMBER                       2
C          NUMBER                       3

SQL> select object_id from dba_objects where wner='TEST1' and object_name='UNUS
ED_TEST' and object_type='TABLE';

 OBJECT_ID
----------
      6577

SQL> select col#,segcol#,name,intcol# from col$ where obj#=6577;

      COL#    SEGCOL# NAME          INTCOL#
---------- ---------- ---------- ----------
         1          1 A                   1
         2          2 B                   2
         3          3 C                   3

通过DUMP数据库文件块可以看到每行有三列（这里不再列出DUMP文件内容）
下面将B列置为unused状态：

SQL> alter table test1.unused_test set unused (b);

Table altered.

SQL> select column_name,data_type,column_id,hidden_column,segment_column_id seg_
cid,internal_column_id internal_cid from dba_tab_cols where wner='TEST1' and ta
ble_name='UNUSED_TEST';

COLUMN_NAME                  DATA_T COLUMN_ID HIDDEN    SEG_CID INTERNAL_CID
---------------------------- ------ --------- ------ ---------- ------------
A                            NUMBER         1 NO              1            1
SYS_C00002_08011915:24:34$   NUMBER           YES             2            2
C                            NUMBER         2 NO              3            3

这里原来的B列，其名字为系统自动生成的一列，命名形式为SYS_CNNNNN_YYMMDDHH24:MI:SS$，NNNNN为原来的COLUMN_ID，前面补0补足成5数。hidden已经变为YES，COLUMN_ID为空。其他两列A和C的COLUMN_ID顺序作了调整。这三列的SEGMENT_COLUMN_ID和INTERNAL_COLUMN_ID没有变化。

SQL> select column_name,data_type,column_id from dba_tab_columns where wner='TE
ST1' and table_name='UNUSED_TEST';

COLUMN_NAME                  DATA_T COLUMN_ID
---------------------------- ------ ---------
A                            NUMBER         1
C                            NUMBER         2

在DBA_TAB_COLUMNS视图中，B列已经没有显示出来。

SQL> select col#,segcol#,name,intcol# from col$ where obj#=6577;

      COL#    SEGCOL# NAME                            INTCOL#
---------- ---------- ---------------------------- ----------
         1          1 A                                     1
         0          2 SYS_C00002_08011915:24:34$            2
         2          3 C                                     3

这里B列的COL#已经变成0.SEGCOL#和INTCOL#列没有变化，NAME也已经变化
DUMP出来的数据中，每一行仍然有三列。
尝试插入数据：

SQL> insert into test1.unused_test values (1234,4321,1);
insert into test1.unused_test values (1234,4321,1)
                  *
ERROR at line 1:
ORA-00913: too many values

SQL> insert into test1.unused_test values (1234,4321);

1 row created.

SQL> select rowid from test1.unused_test where a=1234 and c=4321;

ROWID
------------------
AAABmxAAFAAAAEuAAA

此ROWID对应的rfile#为5，block#为302，row number为0
DUMP出这一块查看第0行数据，发现在数据块中每行仍然是3列，第二列也就是原来的B列其值为NULL。
现在我们将C列删除：

SQL> alter table test1.unused_test drop (c);

Table altered.

SQL> select column_name,data_type,column_id,hidden_column,segment_column_id seg_
cid,internal_column_id internal_cid from dba_tab_cols where wner='TEST1' and ta
ble_name='UNUSED_TEST';

COLUMN_NAME                  DATA_T COLUMN_ID HIDDEN    SEG_CID INTERNAL_CID
---------------------------- ------ --------- ------ ---------- ------------
A                            NUMBER         1 NO              1            1

这里可以看出B列和C列都已经删除。

SQL> select column_name,data_type,column_id from dba_tab_columns where wner='TE
ST1' and table_name='UNUSED_TEST';

COLUMN_NAME                  DATA_T COLUMN_ID
---------------------------- ------ ---------
A                            NUMBER         1

SQL> select col#,segcol#,name,intcol# from col$ where obj#=6577;

      COL#    SEGCOL# NAME                            INTCOL#
---------- ---------- ---------------------------- ----------
         1          1 A                                     1

都可以看出B列和C列已经被删除。从这个实验就可以看出，在删除C时会将UNUSED列一并删除。

DUMP出数据块可以发展，块中每一行只有1列。因此SET UNUSED只是修改了数据字典，速度较快。而将COLUMN DROP掉，不仅修改数据字典，而且修改实际的块数据。如果表比较大，会耗费比较长的时间。

 

某个刚安装好的RAC库，由于需要更换存储，所以需要重建。由于主机系统并没有重新安装，因此只需要重建CRS和库就行了。

环境：AIX 5306+HACMP 5.2+ORACLE 10.2.0.1+祼设备，文中对节点名、网络配置等信息处了更改处理。

以下是详细操作步骤：

1、在两个节点上修改主机配置：由于网络变更的原因，需要更换VIP，因此修改/etc/hosts文件，将VIP地址对应的地址更改为新的IP地址

2、在两个节点上修改/etc/oracle/ocr.loc文件，将文件中的ocrconfig_loc=后的地址改为新的存储ocr的祼设备名（如果用的是集群文件系统，则为文件名）

3、在两个节点上删除文件/etc/oracle/scls_scr/<节点名>/oracle/cssfatal

4、在两个节点上，进入$ORA_CRS_HOME/install目录，修改paramfile.crs文件，修改变动的配置数据。这里主要包括CRS_OCR_LOCATIONS、CRS_VOTING_DISKS、CRS_NODEVIPS

5、对存储OCR CONFIG的祼设备，用dd命令进行清除。（如果是集群文件系统，只需要删除OCR CONFIG的文件即可）。这里祼设备名为rocr，dd if=/dev/zero f=/dev/rocr bs=4096 count=10000 （如果是OCR本来是存在，只是需要重建，则必须要执行这一步。就算是完全新建在祼设备上，在后面的步骤中有时也会遇到莫名其妙的问题，则也需要对祼设备用dd进行清除，dd清除的大小不能过小，bs=4096的情况下，count为10之类的数值就显得过小，后面也会出现问题）

6、在两个节点上修改文件$ORA_CRS_HOME/install/rootconfig，修改在文件前面的变量。这里也主要是CRS_OCR_LOCATIONS、CRS_VOTING_DISKS、CRS_NODEVIPS

7、如果是通过远程telnet、ssh在主机上操作，则要设置DISPLAY变量。export DISPLAY=x.x.x.x:0.0。这里x.x.x.x为操作的终端的IP地址。在操作终端上运行如xmanager这样的软件。

7、在节点一上以root用户运行$ORA_CRS_HOME/install/rootconfig，注意不要运行rootinstall

8、待节点一完全运行完后，在节点二上运行$ORA_CRS_HOME/install/rootconfig。正常情况下会弹出vip设置窗口。如果VIP设置窗口没有弹出来，则看一下是否只是vipca启动出现问题。

9、在两个节点上运行crs_stat -t，如果出现CRS没有资源或有VIP相关的资源启动（在VIP已经设置的情况下），说明CRS已经建立成功。

10、如果前面没有配置VIP，则以root用户运行vipca，配置VIP。注意在弹出的窗口中，提示选择网络接口时，选择public接口。（如果显示接口异常，在shell用oifcfg命令检查一下网络接口，如有必要，用该命令对网络接口进行重新配置）

11、至此crs已经配置完毕，用crs_stat检查crs是否正常运行。如果没有正常运行，检查crs日志。此时应该有VIP、ONS、GSD等资源运行。在两个节点上运行ifconfig -a检查VIP是否已经绑定到PUBLIC网卡上（注意要确保是在PUBLIC网卡上，有的时候粗略一下VIP已经起了，但实际上绑在了PRIVATE网卡上）

12、清除原来的监听设置，确认监听是处于关闭状态，运行netca，配置监听，配置完成后将会自动把监听加入到crs中。

12、由于存在原来的建库脚本，打开原来的脚本，修改对应的数据文件名为新的文件名（祼设备名）

13、在节点一上运行建库脚本(shell脚本)

14、一番耐心等候之后，在节点一上的数据库创建完成

15、在节点二上运行建库脚本(shell脚本)，这个过程很快

16、在两个节点上修改tnsnames.ora，内容如下（根据实际情况进行修改）：

LISTENERS_DMDB =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = dm1-vip)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = dm2-vip)(PORT = 1521))
)

DMDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = dm1-vip)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = dm2-vip)(PORT = 1521))
(LOAD_BALANCE = yes)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = dmdb)
)
)

RAC2 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = dm2-vip)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = dmdb)
(INSTANCE_NAME = rac2)
)
)

RAC1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = dm1-vip)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = dmdb)
(INSTANCE_NAME = rac1)
)
)

EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)

17、修改两个节点的初始化参数REMOTE_LISTENERS为 'LISTENERS_DMDB'，节点一的LOCAL_LISTENER='(ADDRESS = (PROTOCOL = TCP)(HOST = IP1 )(PORT = 1521))'，节点二的
LOCAL_LISTENER='(ADDRESS = (PROTOCOL = TCP)(HOST = IP2)(PORT = 1521))'，注意这里的IP1和IP2分别是节点一和节点二的VIP地址（注意一定是IP地址，而不能是主机名）。设置LOCAL_LISTENER的目的是避免在使用负载均衡时出现ORA-12545错误。

18、在其中一个结点上创建spfile，这里spfile为祼设务rspfile：create spfile='/dev/rspfile' from pfile='xxxx'

19、关闭两个节点的实例。将数据库和实例增加到crs中，以便能够能用crs命令进行监控和用srvctl命令启停数据库实例：

srvctl add database -d dbname -o $ORACLE_HOME -y manual
srvctl add instance -d dbname -n 节点名1 -i 实例名1
srvctl add instance -d dbname -n 节点名2 -i 实例名2

这里实例名1和实例名2应分别与两个节点的ORACLE_SID一致

注意：10.2.0.1版本，实例依赖于VIP，因此如果某结点如网卡DOWN掉、VIP BUG等，将导致实例也DOWN掉。为避免出现这样的情况，可省略此步骤，不要将实例加入到CRS资源中。

至此所有工作已经全部完成

crs, rac

由于ORACLE默认的表名都是不区分大小写，在创建表时，在数据字典中存储的表名为大写。在有些情况下，如果创建的表在表名上加上双引号(")，则创建的表其表名在数据字典中不作转换。比如

create table test1."Table1" as select * from dba_objects where rownum<=10;

表已创建。

select table_name from dba_tables where owner='TEST1';

TABLE_NAME
------------------------------------------------------------
T1
T2
Table1
tt

 

可以看到刚创建的Table1表在数据字典中为"Table1"而不是TABLE1

在导出这样的表时，按用户导出是没有问题的：

exp test1/test1 wner=test1

. 即将导出 TEST1 的表通过常规路径 ...
. . 正在导出表 T1 0 行被导出
. . 正在导出表 T2 0 行被导出
. . 正在导出表 Table1 10 行被导出
. . 正在导出表 tt 10 行被导出

而要单独导出表的话，则需求作特殊处理

exp test1/test1 tables=Table1

即将导出指定的表通过常规路径 ...
EXP-00011: TEST1.TABLE1 不存在
导出成功终止，但出现警告。

将table1用双引号或单引号引起也是一样

exp test1/test1 tables=\"Table1\"

也是一样的结果

用下面的写法可以成功

exp test1/test1 tables='\"Table1\"'

即将导出指定的表通过常规路径 ...
. . 正在导出表 Table1 10 行被导出
在没有警告的情况下成功终止导出。

用参数文件时，由用tables='"Table1"'，去掉反斜杠

注意tables='\"Table1\"'这里是外面一个单引号加反斜杠再加一双引号

在job执行过程中，all_jobs中，this_date和this_sec记录本次开始执行的时间。next_date和next_sec则在开始执行时按interval计算好。执行完成后，无论失败与否，last_date和last_sec为上次执行（也即刚执行过）开始的时间。next_date和next_sec为下次开始执行时间。

如果执行失败，failures字段加1，同时下次执行时间为本次执行时间+2分钟（不知这两分钟从何而来，有空再研究一下），如果再次失败，由下次执行的时间为上次开始执行时间+4，再次失败则+8，以此类推。

执行成功后，failures字段清0.

在同一个数据库内，被引用对象上进行了alter、drop等操作，该对象的依赖对象如view、function、procedure等，状态会自动标记为Invalid，再重新使用这些依赖对象时，系统会自动重新compile。

而一个数据库内的对象引用了远程数据库的对象（这里指程序对象，如procedure等），则远程数据库对象发生了变更，由于本地数据库并不知晓此种情况，本地数据库的这些对象状态仍然为valid，在调用这些对象时，Oracle会根据remote_dependencies_mode参数值，确定采用timestamp或signature进行依赖性检查。如果发现不匹配，则会直接报错返回，同时将依赖该远程对象的所有本地对象标记为invalid。

通过以下测试可以进行验证

先在远程数据库上创建一个测试存储过程

SQL> create or replace procedure p_r_test
2 is
3 begin
4 null;
5 end;
6 /

Procedure created

然后在本地数据库上创建两个测试存储过程

create or replace procedure p_test1
is
begin
p_r_test@testlink
end;

/

create or replace procedure p_test2
is
begin
p_r_test@testlink
end;

/

执行一下测试程序过程

SQL> exec p_test1;

PL/SQL procedure successfully completed

查看这两个存储过程的状态

SQL> select object_name,object_type,status from all_objects where object_name in ('P_TEST1','P_TEST2');

OBJECT_NAME                    OBJECT_TYPE        STATUS
------------------------------ ------------------ -------
P_TEST1                        PROCEDURE          VALID
P_TEST2                        PROCEDURE          VALID

在远程数据库上重新create or replace一下程储过程，再查看本地数据库两个存储过程的状态

SQL> select object_name,object_type,status from all_objects where object_name in ('P_TEST1','P_TEST2');

OBJECT_NAME                    OBJECT_TYPE        STATUS
------------------------------ ------------------ -------
P_TEST1                        PROCEDURE          VALID
P_TEST2                        PROCEDURE          VALID

执行存储过程p_test1

SQL> exec p_test1;

begin p_test1; end;

ORA-04068: 已丢弃程序包 的当前状态
ORA-04062: timestamp (属 procedure "TEST.P_R_TEST") 已被更改
ORA-06512: 在"TEST.P_TEST1", line 4
ORA-06512: 在line 2

再看两个存储过程的状态

SQL> select object_name,object_type,status from all_objects where object_name in ('P_TEST1','P_TEST2');

OBJECT_NAME                    OBJECT_TYPE        STATUS
------------------------------ ------------------ -------
P_TEST1                        PROCEDURE          INVALID
P_TEST2                        PROCEDURE          INVALID

如果这个时候再执行p_test1，则系统发现状态为INVALID，会进行重新编译。

因此在涉及到调用远程存储过程的本地对象，如function、package、procedure等，需要注意远程对象变更这种情况的发生，避免出现本地对象失效，从而引起程序问题，特别是在JOB这样的应用中。
 

如题，边转移文章边修饰老熊的三分地，至少不要让这里显得太贫瘠太荒废。

测试一下

   select * from dual
   connect by level <= 1000;

终于拥有了自己独立域名的网站，也算是自己的三分自留地吧。这里将见证老熊的生活、工作、想法和技术。