Daily Archives: 2012 年 04 月 02 日

OCR/Vote disk 维护操作

Posted on by

数据库版本

SQL>  select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - Prod
PL/SQL Release 10.2.0.5.0 - Production
CORE    10.2.0.5.0      Production
TNS for Linux: Version 10.2.0.5.0 - Production
NLSRTL Version 10.2.0.5.0 - Production

ocr测试(可以online处理)

rac2-> ocrcheck
Status of Oracle Cluster Registry is as follows :
         Version                  :          2
         Total space (kbytes)     :     160396
         Used space (kbytes)      :       4376
         Available space (kbytes) :     156020
         ID                       : 1302494786
         Device/File Name         : /dev/raw/raw11
                                    Device/File integrity check succeeded

                                    Device/File not configured

         Cluster registry integrity check succeeded

rac2-> more /etc/oracle/ocr.loc 
ocrconfig_loc=/dev/raw/raw11
local_only=false

--增加ocr镜像
[root@rac2 bin]# ./ocrconfig -replace ocrmirror /dev/raw/raw12

rac2-> ocrcheck
Status of Oracle Cluster Registry is as follows :
         Version                  :          2
         Total space (kbytes)     :     160396
         Used space (kbytes)      :       4376
         Available space (kbytes) :     156020
         ID                       : 1302494786
         Device/File Name         : /dev/raw/raw11
                                    Device/File integrity check succeeded
         Device/File Name         : /dev/raw/raw12
                                    Device/File integrity check succeeded

         Cluster registry integrity check succeeded

rac2-> more /etc/oracle/ocr.loc 
#Device/file  getting replaced by device /dev/raw/raw12 
ocrconfig_loc=/dev/raw/raw11
ocrmirrorconfig_loc=/dev/raw/raw12
local_only=false

--删除ocr
[root@rac2 bin]# ./ocrconfig -replace ocr

rac2-> more /etc/oracle/ocr.loc 
#Device/file /dev/raw/raw11 being deleted 
ocrconfig_loc=/dev/raw/raw12
local_only=false

rac2-> ocrcheck
Status of Oracle Cluster Registry is as follows :
         Version                  :          2
         Total space (kbytes)     :     160396
         Used space (kbytes)      :       4376
         Available space (kbytes) :     156020
         ID                       : 1302494786
         Device/File Name         : /dev/raw/raw12
                                    Device/File integrity check succeeded

                                    Device/File not configured

         Cluster registry integrity check succeeded

--补充删除ocr镜像
[root@rac2 bin]# ./ocrconfig -replace ocrmirror

Vote disk测试(10g offline/11g online)

--关闭crs
[root@rac2 bin]# ./crsctl stop crs
[root@rac1 bin]# ./crsctl stop crs

--查询vote disk
rac2-> crsctl query css votedisk
 0.     0    /dev/raw/raw31

--增加vote disk
[root@rac2 bin]# ./crsctl add css votedisk /dev/raw/raw23 -force
Now formatting voting disk: /dev/raw/raw23
successful addition of votedisk /dev/raw/raw23.
[root@rac2 bin]# ./crsctl add css votedisk /dev/raw/raw33 -force
Now formatting voting disk: /dev/raw/raw33
successful addition of votedisk /dev/raw/raw33.
[root@rac2 bin]# ./crsctl add css votedisk /dev/raw/raw32 -force
Now formatting voting disk: /dev/raw/raw32
successful addition of votedisk /dev/raw/raw32.

rac2-> crsctl query css votedisk
 0.     0    /dev/raw/raw31
 1.     0    /dev/raw/raw23
 2.     0    /dev/raw/raw33
 3.     0    /dev/raw/raw32

located 4 votedisk(s).

--删除vote disk
[root@rac2 bin]# ./crsctl delete css votedisk /dev/raw/raw33 -force
successful deletion of votedisk /dev/raw/raw33.

--启动crs
[root@rac2 bin]# ./crsctl start crs
[root@rac1 bin]# ./crsctl start crs

补充官方操作说明[ID 428681.1]

RAC 10g升级到10.2.0.5

Posted on by

1.Back Up database
一般情况下rman备份

2.备份ocr和vote disk

[root@rac2 bin]# ./ocrconfig -export /tmp/ocr_export.bak
[root@rac2 bin]# more /etc/oracle/ocr.loc 
ocrconfig_loc=/dev/raw/raw11
local_only=FALSE
[root@rac2 bin]# dd if=/dev/raw/raw11 of=/tmp/ocr_dd.bak
[root@rac2 bin]# dd if=/dev/raw/raw31 of=/tmp/vote_dd.bak

3.Update Oracle Time Zone Definitions
Actions for the DSTv4 update in the 10.2.0.5 patchset [ID 1086400.1]

4.Stopping All Processes
滚动升级关闭一个节点所有进程，非滚动升级关闭所有进程

$ isqlplusctl stop
$ emctl stop dbconsole
$ srvctl stop service -d db_name [-s service_name_list [-i inst_name]]
$ srvctl stop instance -d db_name -i inst_name
$ srvctl stop asm -n node
$ srvctl stop listener -n node [-l listenername]
$ srvctl stop nodeapps -n node
# CRS_home/bin/crsctl stop crs(root执行,滚动升级不需要关闭)

5.Back Up the System
$ORACLE_BASE中文件，主要包括(db和crs安装文件/oraInventory文件)

6.升级crs软件
执行./runInstaller选择crs目录

执行下面命令
# CRS_home/bin/crsctl stop crs
# CRS_home/install/root102.sh

7.升级db软件
关闭crs和db所有进程(步骤同4)
执行./runInstaller选择db目录

执行下面命令
# ORACLE_HOME/root.sh

8.升级数据库
8.1)检查数据库升级需要满足条件,对存在不合适之处,进行修正
How to Download and Run Oracle’s Database Pre-Upgrade Utility [ID 884522.1]

SQL> STARTUP UPGRADE
SQL> SPOOL upgrade_info.log 
SQL> @/rdbms/admin/utlu102i.sql
SQL> SPOOL OFF
SQL> ALTER SYSTEM SET CLUSTER_DATABASE=FALSE SCOPE=spfile;
--其他根据upgrade_info.log中提示修改
SQL> SHUTDOWN IMMEDIATE
SQL> STARTUP UPGRADE

8.2)启动监听
srvctl start listener -n node

8.3)升级数据库

SQL> SPOOL patch.log
SQL> @?/rdbms/admin/catupgrd.sql
--检查patch.log，发现有错误查找原因，重新执行catupgrd.sql脚本
SQL> SPOOL OFF
SQL> SHUTDOWN IMMEDIATE
SQL> STARTUP
SQL> @?/rdbms/admin/utlrp.sql
SQL> ALTER SYSTEM SET CLUSTER_DATABASE=TRUE SCOPE=spfile;
--包括其他修改调整参数
SQL> SHUTDOWN IMMEDIATE
--使用rac管理相关命令，启动需要启动资源

9.修改相关目录权限
# ORACLE_HOME/install/changePerm.sh

具体操作步骤请阅读README.html

永久表空间出现临时段不能扩展原因探讨

Posted on by

数据库版本

SQL> select * from v$version;

BANNER
-----------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
PL/SQL Release 11.2.0.3.0 - Production
CORE    11.2.0.3.0      Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

创建5M测试表空间

SQL> CREATE TABLESPACE T_1652 DATAFILE '/tmp/t_1652_01.dbf' size 5M 
  2  AUTOEXTEND OFF LOGGING PERMANENT EXTENT MANAGEMENT LOCAL AUTOALLOCATE 
  3  SEGMENT SPACE MANAGEMENT AUTO blocksize 8192;

Tablespace created.

测试CTAS

SQL> create table CHF.T_XIFENFEI TABLESPACE T_1652 as
  2  select LPAD('XIFENFEI',1024,'F') "C_XFF" from  dual connect by level <=3500;
create table CHF.T_XIFENFEI TABLESPACE T_1652 as
*
ERROR at line 1:
ORA-01652: unable to extend temp segment by 128 in tablespace T_1652


SQL> create table CHF.T_XIFENFEI TABLESPACE T_1652 as
  2  select LPAD('XIFENFEI',1024,'F') "C_XFF" from  dual connect by level <=3000;

Table created.

测试CREATE INDEX

SQL> create index chf.i_xifenfei on chf.t_xifenfei(c_xff)
  2  tablespace t_1652;
create index chf.i_xifenfei on chf.t_xifenfei(c_xff)
                                   *
ERROR at line 1:
ORA-01658: unable to create INITIAL extent for segment in tablespace T_1652


SQL> Select MAX(d.bytes) total_bytes,
  2         nvl(SUM(f.Bytes), 0) free_bytes,
  3         d.file_name,
  4         MAX(d.bytes) - nvl(SUM(f.bytes), 0) used_bytes,
  5  from   DBA_FREE_SPACE f , DBA_DATA_FILES d
  6  where  f.tablespace_name(+) = d.tablespace_name
  7  and    f.file_id(+) = d.file_id
  8  and    d.tablespace_name = 'T_1652'
  9  group by d.file_name;

TOTAL_BYTES FREE_BYTES FILE_NAME                 USED_BYTES  
----------- ---------- ------------------------- ---------- 
    5242880          0 /tmp/t_1652_01.dbf           5242880

SQL> drop table chf.t_xifenfei purge;

Table dropped.

SQL> create table CHF.T_XIFENFEI TABLESPACE T_1652 as
  2  select LPAD('XIFENFEI',1024,'F') "C_XFF" from  dual connect by level <=2000;

Table created.

SQL> Select MAX(d.bytes) total_bytes,
  2         nvl(SUM(f.Bytes), 0) free_bytes,
  3         d.file_name,
  4         MAX(d.bytes) - nvl(SUM(f.bytes), 0) used_bytes,
  5  from   DBA_FREE_SPACE f , DBA_DATA_FILES d
  6  where  f.tablespace_name(+) = d.tablespace_name
  7  and    f.file_id(+) = d.file_id
  8  and    d.tablespace_name = 'T_1652'
  9  group by d.file_name;


TOTAL_BYTES FREE_BYTES FILE_NAME                 USED_BYTES   
----------- ---------- ------------------------- ---------- 
    5242880    1048576 /tmp/t_1652_01.dbf           4194304  

SQL> create index chf.i_xifenfei on chf.t_xifenfei(c_xff)
  2  tablespace t_1652;
create index chf.i_xifenfei on chf.t_xifenfei(c_xff)
                                   *
ERROR at line 1:
ORA-01652: unable to extend temp segment by 128 in tablespace T_1652

SQL> ALTER DATABASE DATAFILE '/tmp/t_1652_01.dbf' RESIZE 10M;

Database altered.

SQL> create index chf.i_xifenfei on chf.t_xifenfei(c_xff)
  2  tablespace t_1652;

Index created.

测试MOVE

SQL> drop table chf.t_xifenfei purge;

Table dropped.

SQL> create table CHF.T_XIFENFEI TABLESPACE T_1652 as
  2  select LPAD('XIFENFEI',1024,'F') "C_XFF" from  dual connect by level <=3500;

Table created.

SQL> alter table chf.t_xifenfei move;
alter table chf.t_xifenfei move
*
ERROR at line 1:
ORA-01652: unable to extend temp segment by 128 in tablespace T_1652


SQL> Select MAX(d.bytes) total_bytes,
  2         nvl(SUM(f.Bytes), 0) free_bytes,
  3         d.file_name,
  4         MAX(d.bytes) - nvl(SUM(f.bytes), 0) used_bytes,
  5  from   DBA_FREE_SPACE f , DBA_DATA_FILES d
  6  where  f.tablespace_name(+) = d.tablespace_name
  7  and    f.file_id(+) = d.file_id
  8  and    d.tablespace_name = 'T_1652'
  9  group by d.file_name;

TOTAL_BYTES FREE_BYTES FILE_NAME                 USED_BYTES  
----------- ---------- ------------------------- ---------- 
   10485760    4194304 /tmp/t_1652_01.dbf           6291456  

SQL>  ALTER DATABASE DATAFILE '/tmp/t_1652_01.dbf' RESIZE 15M;

Database altered.

SQL> alter table chf.t_xifenfei move;

Table altered.

这里可以发现CTAS,CREATE INDEX,MOVE操作都有个共同点:需要一次性创建一个较大SEGMENT,但是这个SEGMENT的创建过程是在数据库中逐渐实现(非初始化指定大小)。
也就是说,ORACLE对这些对象的处理方法是：对于这样的segment先当作临时段处理,当处理完成后,再把这些在永久表空间中的临时段转换为永久段;所以当这些永久表空间中的临时段在扩展的时候,遇到该永久表空间不足,而该段目前还是临时段(在永久表空间中的临时段),就出现了ORA-01652提示一个永久表空间unable to extend temp segment