今天接到现场反馈，数据库出现ORA-01012: not logged on错误提示，这个问题上次也出现过一次，当时按照网上说的方法，直接重启数据库了，问题解决了，同时也导致因为数据库重启，现场破坏，而alert日志中无任何异常信息，所以不知从何处下手分析。这次我上数据库准备查看时，发现数据库已经正常，监控也显示正常，说明数据库已经恢复正常。从此我推理这个问题应该是外部因素导致，而不是数据库本身的bug，从而决定要找出该问题的原因来。有个重要的因素，该数据库是我几个月前因为undo损坏做过恢复的，查看相关参数，发现processes是默认值150，是不是该值导致的不敢肯定，因为一般process超了会报ORA-00020错误，而这次只有ORA-01012。但是心中还是没有底，总感觉这个的可能性最大，于是想通过试验来证实下自己的想法
1、数据库版本（10g结果相同）
SQL> select * from v$version where rownum=1;
BANNER
—————————————————————-
Oracle9i Enterprise Edition Release 9.2.0.4.0 – Production
2、创建pfile，修改processes，重启数据库，查看processes值
SQL> select name,value from v$parameter where name=’processes’;
NAME VALUE
——————– ——————–
processes 30
3、查看系统processes数量
SQL> select count(*) from v$process;
COUNT(*)
———-
13
4、添加数据库连接数（为了增加process数目）
[oracle@localhost ~]$ sqlplus chf/xifenfei
SQL*Plus: Release 9.2.0.4.0 – Production on Wed Jul 27 23:11:50 2011
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.4.0 – Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 – Production
SQL> !
5、重复3和4，发现当process数量为29时出现激动人心一幕
5.1)继续访问数据库
[oracle@localhost ~]$ sqlplus system/xifenfei
SQL*Plus: Release 9.2.0.4.0 – Production on Wed Jul 27 23:12:06 2011
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
ERROR:
ORA-00020: maximum number of processes (%s) exceeded
Enter user-name:
5.2)使用其他用户访问
[oracle@localhost ~]$ sqlplus /nolog
SQL*Plus: Release 9.2.0.4.0 – Production on Wed Jul 27 23:12:49 2011
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
SQL> conn / as sysdba
Connected to an idle instance.
SQL> select * from dual;
select * from dual
*
ERROR at line 1:
ORA-01012: not logged on
SQL> conn chf/xifenfei
ERROR:
ORA-00020: maximum number of processes (%s) exceeded
[oracle@localhost ~]$ sqlplus “/ as sysdba”
SQL*Plus: Release 9.2.0.4.0 – Production on Wed Jul 27 23:29:52 2011
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected.
SQL> select * from dual;
select * from dual
*
ERROR at line 1:
ORA-01012: not logged on
现象说明：
1)终于发现了ORA-01012错误，期待了很久。发现只有当sys登录系统，对数据库进行查询或者操作之时才会出现ORA-01012，其他用户只要一登录数据库就会提示ORA-00020错误。
2)当process达到数据库最大限时，sysdba登录系统是也显示不正常
2.1）当使用sqlplus “/ as sysdba”登录时，缺少：
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.4.0 – Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 – Production
2.2）当使用conn / as sysdba登录时，提示为：
Connected to an idle instance.
ORA-01031: insufficient privileges
今天接到开发反馈，说数据库动态sql创建表语句无法执行，提示ORA-01031: insufficient privileges
1、数据库版本
SQL> select * from v$version where rownum=1;
BANNER
—————————————————————-
Oracle9i Enterprise Edition Release 9.2.0.4.0 – Production
2、创建用户并授权
SQL> create user test identified by xifenfei;
User created.
SQL> grant connect,resource to test;
Grant succeeded.
3、查看权限
SQL> SELECT * FROM DBA_SYS_PRIVS WHERE GRANTEE=’TEST’;
GRANTEE PRIVILEGE ADM
—————————— —————————————- —
TEST UNLIMITED TABLESPACE NO
SQL> SELECT * FROM DBA_ROLE_PRIVS WHERE GRANTEE=’TEST’;
GRANTEE GRANTED_ROLE ADM DEF
—————————— —————————— — —
TEST CONNECT NO YES
TEST RESOURCE NO YES
SQL> CONN TEST/XIFENFEI
Connected.
SQL> SELECT * FROM SESSION_PRIVS;
PRIVILEGE
—————————————-
CREATE SESSION
ALTER SESSION
UNLIMITED TABLESPACE
CREATE TABLE
CREATE CLUSTER
CREATE SYNONYM
CREATE VIEW
CREATE SEQUENCE
CREATE DATABASE LINK
CREATE PROCEDURE
CREATE TRIGGER
PRIVILEGE
—————————————-
CREATE TYPE
CREATE OPERATOR
CREATE INDEXTYPE
14 rows selected.
注：目前create table权限是通过resource role授予test用户的
4、创建存储过程（Authid Current_User）
SQL> create or replace procedure create_table(t varchar2) Authid Current_User
2 is
3 P_SQL VARCHAR2(300);
4 BEGIN
5 P_SQL := ‘create table ‘||t||’ as select * from user_tables where 1=0′;
6 EXECUTE IMMEDIATE P_SQL;
END; 7
8 /
Procedure created.
5、执行存储过程
SQL> EXEC CREATE_table(‘a’);
PL/SQL procedure successfully completed.
6、重建存储过程（不包括Authid Current_User）
SQL> create or replace procedure create_table(t varchar2)
2 –Authid Current_User
3 is
4 P_SQL VARCHAR2(300);
5 BEGIN
6 P_SQL := ‘create table ‘||t||’ as select * from user_tables where 1=0′;
7 EXECUTE IMMEDIATE P_SQL;
8 END;
9 /
Procedure created.
SQL> EXEC CREATE_table(‘b’);
BEGIN CREATE_table(‘b’); END;
*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at “TEST.CREATE_TABLE”, line 7
ORA-06512: at line 1
7、授权（create table权限）
SQL> conn / as sysdba
Connected.
SQL> grant create table to test;
Grant succeeded.
SQL> conn test/xifenfei
Connected.
SQL> EXEC CREATE_table(‘c’);
PL/SQL procedure successfully completed.
8、查看结果
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
—————————— ——- ———-
A TABLE
C TABLE
9、 查询资料
默认情况下，在调用存储过程用户的角色是不起作用的，即在执行存储过程时只有Public权限。所以如果被调用的存储过程中如果有execute immediate ‘create table..’语句，将会引发ORA-01031: insufficient privileges错误。
存储过程分为两种，即DR(Definer’s Rights ) Procedure和IR(Invoker’s Rights ) Procedure。为什么会有两种存储过程呢？其实考虑完下面的问题就清楚了。比如说用户hrch创建了删除表tar_table的存储过程drop_table()，当用户hrch调用时，即删除用户hrch下的表tar_table；如果是另一个用户scott调用呢？是删除用户scott下的tar_table表呢，还是删除用户hrch下的tar_table呢？另外，如果存储过程中包含建表语句，不管是用户hrch还是用户scott调用都会失败，因为Public没有建表权限，除非为Public grant建表权限。
10、动态sql中执行create table解决方案（10g也存在同样问题）
1)加上Authid Current_User
2)直接赋予create table to 该用户
11、匿名块的动态sql不在此限制中
SQL> conn / as sysdba
Connected.
SQL> SELECT * FROM DBA_SYS_PRIVS WHERE GRANTEE=’TEST’;
GRANTEE PRIVILEGE ADM
—————————— —————————————- —
TEST UNLIMITED TABLESPACE NO
SQL> CONN TEST/XIFENFEI
Connected.
SQL> DECLARE
2 P_SQL VARCHAR2(300);
3 BEGIN
4 P_SQL := ‘create table t_t as select * from user_tables where 1=0’;
5 EXECUTE IMMEDIATE P_SQL;
6 END;
7 /
PL/SQL procedure successfully completed.
ASM迁移至文件系统
--创建pfile文件
SQL> create pfile ='/tmp/pfile' from spfile;
File created.
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options
--修改pfile中关于asm中的内容
control_files
db_recovery_file_dest
log_archive_dest_1
指定到文件系统
--登录rman
[oracle@localhost tmp]$ rman target /
Recovery Manager: Release 10.2.0.3.0 - Production on Mon Jun 27 12:48:26 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: TOS (DBID=1569606545)
--执行backup as copy datafile
RMAN> backup as copy datafile '+DATA/tos/datafile/users.276.754906035' format '/u01/oradata/tos/USERS01.dbf';
Starting backup at 27-JUN-11
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=141 devtype=DISK
channel ORA_DISK_1: starting datafile copy
input datafile fno=00004 name=+DATA/tos/datafile/users.276.754906035
output filename=/u01/oradata/tos/USERS01.dbf tag=TAG20110627T124853 recid=17 stamp=754922939
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
Finished backup at 27-JUN-11
RMAN> backup as copy datafile '+DATA/tos/datafile/sysaux.271.754905929' format '/u01/oradata/tos/SYSAUX01.dbf';
Starting backup at 27-JUN-11
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile fno=00003 name=+DATA/tos/datafile/sysaux.271.754905929
output filename=/u01/oradata/tos/SYSAUX01.dbf tag=TAG20110627T124929 recid=18 stamp=754923029
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:05
Finished backup at 27-JUN-11
RMAN> backup as copy datafile '+DATA/tos/datafile/undotbs1.273.754906021' format '/u01/oradata/tos/UNDOTBS101.dbf';
Starting backup at 27-JUN-11
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile fno=00002 name=+DATA/tos/datafile/undotbs1.273.754906021
output filename=/u01/oradata/tos/UNDOTBS101.dbf tag=TAG20110627T125049 recid=19 stamp=754923057
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
Finished backup at 27-JUN-11
RMAN> backup as copy datafile '+DATA/tos/datafile/system.270.754905833' format '/u01/oradata/tos/SYSTEM01.dbf';
Starting backup at 27-JUN-11
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile fno=00001 name=+DATA/tos/datafile/system.270.754905833
output filename=/u01/oradata/tos/SYSTEM01.dbf tag=TAG20110627T125112 recid=20 stamp=754923150
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:25
channel ORA_DISK_1: starting datafile copy
copying current control file
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 06/27/2011 12:52:39
ORA-01580: error creating control backup file /u01/oradata/tos/SYSTEM01.dbf
ORA-27038: created file already exists
Additional information: 1
continuing other job steps, job failed will not be re-run
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including current SPFILE in backupset
channel ORA_DISK_1: starting piece 1 at 27-JUN-11
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 06/27/2011 12:52:42
ORA-19504: failed to create file "/u01/oradata/tos/SYSTEM01.dbf"
ORA-27038: created file already exists
Additional information: 1
注：因为默认情况下，备份system数据文件是，会自动备份控制文件，这里因为system01.dbf已经备份好，而控制文件再次备份为该名称所以失败
RMAN> backup as copy datafile '+DATA/tos/datafile/example.272.754905995' format '/u01/oradata/tos/EXAMPLE01.dbf';
Starting backup at 27-JUN-11
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile fno=00005 name=+DATA/tos/datafile/example.272.754905995
output filename=/u01/oradata/tos/EXAMPLE01.dbf tag=TAG20110627T125341 recid=21 stamp=754923244
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:25
Finished backup at 27-JUN-11
RMAN> backup as copy datafile '+DATA/tos/datafile/xff.274.754906027' format '/u01/oradata/tos/XFF01.dbf';
Starting backup at 27-JUN-11
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile fno=00006 name=+DATA/tos/datafile/xff.274.754906027
output filename=/u01/oradata/tos/XFF01.dbf tag=TAG20110627T125415 recid=22 stamp=754923257
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 27-JUN-11
RMAN> backup as copy datafile '+DATA/tos/datafile/xff.275.754906031' format '/u01/oradata/tos/XFF02.dbf';
Starting backup at 27-JUN-11
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile fno=00007 name=+DATA/tos/datafile/xff.275.754906031
output filename=/u01/oradata/tos/XFF02.dbf tag=TAG20110627T125507 recid=23 stamp=754923309
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:04
Finished backup at 27-JUN-11
RMAN> exit
Recovery Manager complete.
--登录sqlplus
[oracle@localhost tmp]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.3.0 - Production on Mon Jun 27 12:55:29 2011
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options
--备份控制文件
SQL> alter database backup controlfile to '/tmp/control.ctl';
Database altered.
--关闭数据库
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
--启动数据库只nomount状态
SQL> startup pfile='/tmp/pfile' nomount;
ORACLE instance started.
Total System Global Area 167772160 bytes
Fixed Size 1260672 bytes
Variable Size 79692672 bytes
Database Buffers 79691776 bytes
Redo Buffers 7127040 bytes
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options
[oracle@localhost tmp]$ rman target /
Recovery Manager: Release 10.2.0.3.0 - Production on Mon Jun 27 12:58:22 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: tos (not mounted)
--恢复控制文件
RMAN> restore controlfile from '/tmp/control.ctl';
Starting restore at 27-JUN-11
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK
channel ORA_DISK_1: copied control file copy
output filename=/u01/oradata/tos/control01.ctl
output filename=/u01/oradata/tos/control02.ctl
Finished restore at 27-JUN-11
--启动数据库只mount状态
RMAN> alter database mount;
database mounted
released channel: ORA_DISK_1
--修改数据文件在控制文件中位置
RMAN> switch tablespace SYSTEM to copy;
Starting implicit crosscheck backup at 27-JUN-11
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK
Finished implicit crosscheck backup at 27-JUN-11
Starting implicit crosscheck copy at 27-JUN-11
using channel ORA_DISK_1
Crosschecked 15 objects
Finished implicit crosscheck copy at 27-JUN-11
searching for all files in the recovery area
cataloging files...
no files cataloged
datafile 1 switched to datafile copy "/u01/oradata/tos/SYSTEM01.dbf"
RMAN> switch tablespace UNDOTBS1 to copy;
datafile 2 switched to datafile copy "/u01/oradata/tos/UNDOTBS101.dbf"
RMAN> switch tablespace SYSAUX to copy;
datafile 3 switched to datafile copy "/u01/oradata/tos/SYSAUX01.dbf"
RMAN> switch tablespace USERS to copy;
datafile 4 switched to datafile copy "/u01/oradata/tos/USERS01.dbf"
RMAN> switch tablespace EXAMPLE to copy;
datafile 5 switched to datafile copy "/u01/oradata/tos/EXAMPLE01.dbf"
RMAN> switch tablespace XFF to copy;
datafile 6 switched to datafile copy "/u01/oradata/tos/XFF01.dbf"
datafile 7 switched to datafile copy "/u01/oradata/tos/XFF02.dbf"
--恢复数据库
RMAN> recover database;
Starting recover at 27-JUN-11
using channel ORA_DISK_1
starting media recovery
archive log thread 1 sequence 9 is already on disk as file +DATA/tos/onlinelog/group_6.279.754906321
archive log filename=+DATA/tos/onlinelog/group_6.279.754906321 thread=1 sequence=9
media recovery complete, elapsed time: 00:00:03
Finished recover at 27-JUN-11
--打开数据库
RMAN> alter database open;
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of alter db command at 06/27/2011 13:00:36
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
RMAN> alter database open resetlogs;
database opened
注：不能直接使用open打开
RMAN> exit
Recovery Manager complete.
[oracle@localhost tmp]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.3.0 - Production on Mon Jun 27 13:02:53 2011
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options
--增加redo log
SQL> alter database add logfile group 1 '/u01/oradata/tos/redo01.log' size 10m;
Database altered.
SQL> alter database add logfile group 2 '/u01/oradata/tos/redo02.log' size 10m;
Database altered.
SQL> alter database add logfile group 3 '/u01/oradata/tos/redo03.log' size 10m;
Database altered.
--切换日志
SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL> /
System altered.
--内存中数据写入硬盘
SQL> alter system checkpoint;
System altered.
--查询当前日志组状态
SQL> select group#,status from v$log;
GROUP# STATUS
---------- ----------------
1 CURRENT
2 INACTIVE
3 INACTIVE
4 INACTIVE
5 INACTIVE
6 INACTIVE
6 rows selected.
--删除asm中日志
SQL> alter database drop logfile group 4;
Database altered.
SQL> alter database drop logfile group 5;
Database altered.
SQL> alter database drop logfile group 6;
Database altered.
--添加临时文件
SQL> alter tablespace temp add tempfile '/u01/oradata/tos/temp01.dbf' size 30m autoextend on maxsize 1g;
Tablespace altered.
--查看临时表空间中临时文件
SQL> select name from v$tempfile;
NAME
--------------------------------------------------------------------------------
/u01/oradata/tos/temp01.dbf
+DATA/tos/tempfile/temp.280.754906369
--删除asm中临时文件
SQL> alter tablespace temp drop tempfile '+DATA/tos/tempfile/temp.280.754906369';
Tablespace altered.
--查看迁移结果
SQL> set pagesize 100
SQL> select name from v$datafile
2 union
3 select member from v$logfile
4 union
5 select name from v$controlfile
6 union
7 select name from v$tempfile;
NAME
------------------------------------------------------------------
/u01/oradata/tos/EXAMPLE01.dbf
/u01/oradata/tos/SYSAUX01.dbf
/u01/oradata/tos/SYSTEM01.dbf
/u01/oradata/tos/UNDOTBS101.dbf
/u01/oradata/tos/USERS01.dbf
/u01/oradata/tos/XFF01.dbf
/u01/oradata/tos/XFF02.dbf
/u01/oradata/tos/control01.ctl
/u01/oradata/tos/control02.ctl
/u01/oradata/tos/redo01.log
/u01/oradata/tos/redo02.log
/u01/oradata/tos/redo03.log
/u01/oradata/tos/temp01.dbf
13 rows selected.
--创建spfile文件
SQL> create spfile from pfile='/tmp/pfile';
File created.
create spfile to asm
--查看sid
SQL> show parameter instance_name ;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
instance_name string tos
--创建pfile
SQL> create pfile='/tmp/tospfile' from spfile;
File created.
--创建spfile in asm
SQL> create spfile='+data' from pfile='/tmp/tospfile';
File created.
--查看spfile name in asm
ASMCMD> pwd
+data/tos/parameterfile
ASMCMD> ls
spfile.282.754913039
--编辑pfile内容（如果有该文件，先删除/重命名）
[oracle@localhost ~]$ vi $ORACLE_HOME/dbs/inittos.ora
#内容为
spfile='+data/tos/parameterfile/spfile.282.754913039'
--重命名spfile文件
[oracle@localhost dbs]$ mv spfiletos.ora spfiletos.ora_bak
--重启数据库
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 167772160 bytes
Fixed Size 1260672 bytes
Variable Size 75498368 bytes
Database Buffers 83886080 bytes
Redo Buffers 7127040 bytes
Database mounted.
Database opened.
--查看spfile
SQL> show parameter spfile;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string +DATA/tos/parameterfile/spfile
.282.754913039
普通库迁移至ASM存储
--查看当前库文件位置
SQL> select name from v$datafile
2 union
3 select member from v$logfile
4 union
5 select name from v$controlfile
6 union
7 select name from v$tempfile;
NAME
--------------------------------------------------------------------------------
/u01/oradata/tos/control01.ctl
/u01/oradata/tos/control02.ctl
/u01/oradata/tos/control03.ctl
/u01/oradata/tos/example01.dbf
/u01/oradata/tos/redo01.log
/u01/oradata/tos/redo02.log
/u01/oradata/tos/redo03.log
/u01/oradata/tos/sysaux01.dbf
/u01/oradata/tos/system01.dbf
/u01/oradata/tos/temp01.dbf
/u01/oradata/tos/undotbs01.dbf
/u01/oradata/tos/users01.dbf
/u01/oradata/tos/xff01.dbf
/u01/oradata/tos/xff02.dbf
14 rows selected.
--备份控制文件
SQL> alter database backup controlfile to '/u01/control.ctl';
Database altered.
--修改spfile中的控制文件至asm
SQL> alter system set control_files='+DATA' scope=spfile;
System altered.
--修改db_recovery_file_dest至asm中
SQL> alter system set db_recovery_file_dest='+FLASHBACK';
System altered.
--修改归档日志至asm中
SQL> alter system set log_archive_dest_1='location=+DATA';
System altered.
--关闭数据库
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
--登录rman
[oracle@localhost ~]$ rman target /
Recovery Manager: Release 10.2.0.3.0 - Production on Mon Jun 27 08:02:37 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database (not started)
--启动数据库至nomount状态
RMAN> startup nomount;
Oracle instance started
Total System Global Area 167772160 bytes
Fixed Size 1260672 bytes
Variable Size 67109760 bytes
Database Buffers 92274688 bytes
Redo Buffers 7127040 bytes
--恢复控制文件
RMAN> restore controlfile from '/u01/control.ctl';
Starting restore at 27-JUN-11
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK
channel ORA_DISK_1: copied control file copy
output filename=+DATA/tos/controlfile/current.268.754905785
Finished restore at 27-JUN-11
--打开数据库至mount状态
RMAN> alter database mount;
database mounted
released channel: ORA_DISK_1
--copy数据文件至asm
RMAN> backup as copy database format '+DATA';
Starting backup at 27-JUN-11
Starting implicit crosscheck backup at 27-JUN-11
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK
Finished implicit crosscheck backup at 27-JUN-11
Starting implicit crosscheck copy at 27-JUN-11
using channel ORA_DISK_1
Finished implicit crosscheck copy at 27-JUN-11
searching for all files in the recovery area
cataloging files...
no files cataloged
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile fno=00001 name=/u01/oradata/tos/system01.dbf
output filename=+DATA/tos/datafile/system.270.754905833 tag=TAG20110627T080352 recid=2 stamp=754905928
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:35
channel ORA_DISK_1: starting datafile copy
input datafile fno=00003 name=/u01/oradata/tos/sysaux01.dbf
output filename=+DATA/tos/datafile/sysaux.271.754905929 tag=TAG20110627T080352 recid=3 stamp=754905990
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:05
channel ORA_DISK_1: starting datafile copy
input datafile fno=00005 name=/u01/oradata/tos/example01.dbf
output filename=+DATA/tos/datafile/example.272.754905995 tag=TAG20110627T080352 recid=4 stamp=754906010
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:26
channel ORA_DISK_1: starting datafile copy
input datafile fno=00002 name=/u01/oradata/tos/undotbs01.dbf
output filename=+DATA/tos/datafile/undotbs1.273.754906021 tag=TAG20110627T080352 recid=5 stamp=754906025
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile copy
input datafile fno=00006 name=/u01/oradata/tos/xff01.dbf
output filename=+DATA/tos/datafile/xff.274.754906027 tag=TAG20110627T080352 recid=6 stamp=754906029
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting datafile copy
input datafile fno=00007 name=/u01/oradata/tos/xff02.dbf
output filename=+DATA/tos/datafile/xff.275.754906031 tag=TAG20110627T080352 recid=7 stamp=754906032
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting datafile copy
input datafile fno=00004 name=/u01/oradata/tos/users01.dbf
output filename=+DATA/tos/datafile/users.276.754906035 tag=TAG20110627T080352 recid=8 stamp=754906035
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:04
Finished backup at 27-JUN-11
RMAN-06497: WARNING: control file is not current, control file autobackup skipped
--恢复数据库
RMAN> recover database;
Starting recover at 27-JUN-11
using channel ORA_DISK_1
starting media recovery
archive log thread 1 sequence 3 is already on disk as file /u01/oradata/tos/redo02.log
archive log filename=/u01/oradata/tos/redo02.log thread=1 sequence=3
media recovery complete, elapsed time: 00:00:03
Finished recover at 27-JUN-11
--修改数据库中数据文件路径
RMAN> switch database to copy;
datafile 1 switched to datafile copy "+DATA/tos/datafile/system.270.754905833"
datafile 2 switched to datafile copy "+DATA/tos/datafile/undotbs1.273.754906021"
datafile 3 switched to datafile copy "+DATA/tos/datafile/sysaux.271.754905929"
datafile 4 switched to datafile copy "+DATA/tos/datafile/users.276.754906035"
datafile 5 switched to datafile copy "+DATA/tos/datafile/example.272.754905995"
datafile 6 switched to datafile copy "+DATA/tos/datafile/xff.274.754906027"
datafile 7 switched to datafile copy "+DATA/tos/datafile/xff.275.754906031"
--打开数据库
RMAN> alter database open resetlogs;
database opened
--添加日志文件
SQL> alter database add logfile group 4 '+DATA' size 10m;
Database altered.
SQL> alter database add logfile group 5 '+DATA' size 10m;
Database altered.
SQL> alter database add logfile group 6 '+DATA' size 10m;
Database altered.
--添加临时文件
SQL> alter tablespace temp add tempfile '+DATA' size 30m;
Tablespace altered.
--删除原临时文件
SQL> alter tablespace temp drop tempfile '/u01/oradata/tos/temp01.dbf';
Tablespace altered.
--切换日志
SQL> alter system switch logfile; --多次
System altered.
--数据文件修改写入磁盘
SQL> alter system checkpoint;
System altered.
--检查联机日志状态
SQL> select group#,status from v$log;
GROUP# STATUS
---------- ----------------
1 INACTIVE
2 INACTIVE
3 INACTIVE
4 INACTIVE
5 INACTIVE
6 CURRENT
6 rows selected.
--删除原联机日志
SQL> alter database drop logfile group 1;
Database altered.
SQL> alter database drop logfile group 2;
Database altered.
SQL> alter database drop logfile group 3;
Database altered.
--查看修改后状态
SQL> select name from v$datafile
2 union
3 select member from v$logfile
4 union
5 select name from v$controlfile
6 union
7 select name from v$tempfile;
NAME
--------------------------------------------------------------------------------
+DATA/tos/controlfile/current.268.754905785
+DATA/tos/datafile/example.272.754905995
+DATA/tos/datafile/sysaux.271.754905929
+DATA/tos/datafile/system.270.754905833
+DATA/tos/datafile/undotbs1.273.754906021
+DATA/tos/datafile/users.276.754906035
+DATA/tos/datafile/xff.274.754906027
+DATA/tos/datafile/xff.275.754906031
+DATA/tos/onlinelog/group_4.277.754906309
+DATA/tos/onlinelog/group_5.278.754906319
+DATA/tos/onlinelog/group_6.279.754906321
+DATA/tos/tempfile/temp.280.754906369
asm数据文件迁移(asm–>os)
--查看当前情况
SQL> select count(*) from hr.a;
COUNT(*)
----------
1580
SQL> select name from v$DATAFILE;
NAME
-----------------------------------------------------------
+DATA/tasm/system01.dbf
+DATA/tasm/undotbs01.dbf
+DATA/tasm/sysaux01.dbf
+DATA/tasm/users01.dbf
+DATA/tasm/example01.dbf
+DG2/tasm/datafile/xff.256.754902279
6 rows selected.
--创建目录
SQL> create directory asmsrc as '+DG2/TASM/datafile';
Directory created.
SQL> create directory osdesc as '/u01/oradata';
Directory created.
--表空间离线
SQL> alter tablespace xff offline;
Tablespace altered.
--数据文件迁移
SQL> begin
2 dbms_file_transfer.copy_file('ASMSRC',
3 'xff.256.754902279',
4 'OSDESC',
5 'xff_new_2.dbf');
6 END;
7 /
PL/SQL procedure successfully completed.
--修改数据库中datafile路径
SQL> alter database rename file
2 '+DG2/tasm/datafile/xff.256.754902279'
3 to '/u01/oradata/xff_new_2.dbf';
Database altered.
--表空间在线
SQL> alter tablespace xff online;
Tablespace altered.
--测试迁移结果
SQL> select name from v$DATAFILE;
NAME
-------------------------------------------------------------------
+DATA/tasm/system01.dbf
+DATA/tasm/undotbs01.dbf
+DATA/tasm/sysaux01.dbf
+DATA/tasm/users01.dbf
+DATA/tasm/example01.dbf
/u01/oradata/xff_new_2.dbf
6 rows selected.
SQL> select count(*) from hr.a;
COUNT(*)
----------
1580
--删除asm中文件
ASMCMD> rm XFF.256.754902279
ORA-15032: not all alterations performed
ORA-15028: ASM file '+dg2/tasm/datafile/XFF.256.754902279' not dropped;
currently being accessed (DBD ERROR: OCIStmtExecute)
--不能直接删除，关闭数据库后可以删除（应该是bug）
asm数据文件迁移(asm–>asm)
rman迁移操作
[oracle@localhost oradata]$ rman target / Recovery Manager: Release 10.2.0.3.0 - Production on Mon Jun 27 05:50:03 2011 Copyright (c) 1982, 2005, Oracle. All rights reserved. connected to target database: TASM (DBID=3032096031) RMAN> sql ‘alter tablespace xff offline’; using target database control file instead of recovery catalog sql statement: alter tablespace xff offline RMAN> backup device type disk as copy datafile 6 format '+DG2'; Starting backup at 27-JUN-11 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: sid=159 devtype=DISK channel ORA_DISK_1: starting datafile copy input datafile fno=00006 name=+DATA/tasm/xff01.dbf output filename=+DG2/tasm/datafile/xff.256.754899605 tag=TAG20110627T062003 recid=4 stamp=754899608 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07 Finished backup at 27-JUN-11 RMAN> switch tablepspace xff to copy; datafile 6 switched to datafile copy "+DG2/tasm/datafile/xff.256.754899605" RMAN> sql 'alter tablespace xff online'; sql statement: alter tablespace xff online
sql验证操作
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
+DATA/tasm/system01.dbf
+DATA/tasm/undotbs01.dbf
+DATA/tasm/sysaux01.dbf
+DATA/tasm/users01.dbf
+DATA/tasm/example01.dbf
+DG2/tasm/datafile/xff.256.754899605
6 rows selected.
SQL> select count(*) from hr.a;
COUNT(*)
----------
1580
asm数据文件迁移(os–>asm)
--添加测试表空间
SQL> create tablespace xff datafile '/u01/oradata/xifenfei.dbf' size 10m autoextend on maxsize 100m;
Tablespace created.
--查看数据文件位置
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
+DATA/tasm/system01.dbf
+DATA/tasm/undotbs01.dbf
+DATA/tasm/sysaux01.dbf
+DATA/tasm/users01.dbf
+DATA/tasm/example01.dbf
/u01/oradata/xifenfei.dbf
6 rows selected.
--创建测试表
SQL> create table hr.a tablespace xff
2 as
3 select * from dba_tables;
Table created.
SQL> select count(*) from hr.a;
COUNT(*)
----------
1580
--转移数据文件位置
[oracle@localhost oradata]$ rman target /
Recovery Manager: Release 10.2.0.3.0 - Production on Mon Jun 27 04:30:22 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: TASM (DBID=3032096031)
RMAN> sql 'alter tablespace xff offline';
using target database control file instead of recovery catalog
sql statement: alter tablespace xff offline
RMAN> backup as copy tablespace xff format '+DATA';
Starting backup at 27-JUN-11
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=132 devtype=DISK
channel ORA_DISK_1: starting datafile copy
input datafile fno=00006 name=/u01/oradata/xifenfei.dbf
output filename=+DATA/tasm/datafile/xff.269.754893121 tag=TAG20110627T043200 recid=2 stamp=754893123
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 27-JUN-11
RMAN> switch tablespace xff to copy;
datafile 6 switched to datafile copy "+DATA/tasm/datafile/xff.269.754893121"
RMAN> sql 'alter tablespace xff online';
sql statement: alter tablespace xff online
--查看转移后的数据文件位置
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
+DATA/tasm/system01.dbf
+DATA/tasm/undotbs01.dbf
+DATA/tasm/sysaux01.dbf
+DATA/tasm/users01.dbf
+DATA/tasm/example01.dbf
+DATA/tasm/datafile/xff.269.754893121
6 rows selected.
--测试其中数据是否存在
SQL> select count(*) from hr.a;
COUNT(*)
----------
1580
--创建asm中文件别名
ASMCMD> mkalias +DATA/tasm/datafile/xff.269.754893121 +DATA/tasm/xff01.dbf
--文件重命名
SQL> alter tablespace xff offline;
Tablespace altered.
SQL> alter database rename file '+DATA/tasm/datafile/xff.269.754893121' to '+DATA/tasm/xff01.dbf';
Database altered.
SQL> alter tablespace xff online;
Tablespace altered.
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
+DATA/tasm/system01.dbf
+DATA/tasm/undotbs01.dbf
+DATA/tasm/sysaux01.dbf
+DATA/tasm/users01.dbf
+DATA/tasm/example01.dbf
+DATA/tasm/xff01.dbf
6 rows selected.
--手工删除原来数据
[oracle@localhost oradata]$ rm xifenfei.dbf
说明：可以在rman移植数据文件位置的时候，同时处理好别名，然后对表空间重命名，实现只需要表空间离线一次
Oracle数据文件大小的限制
如果你比较细心，会注意到两件事情：
1、添加一个32g的数据文件会报错
SQL> alter tablespace users add datafile ‘/opt/oracle/oradata/test/user32g.dbf’ size 32g;
alter tablespace users add datafile ‘/opt/oracle/oradata/test/user32g.dbf’ size 32g
*
ERROR at line 1:
ORA-01144: File size (4194304 blocks) exceeds maximum of 4194303 blocks
SQL> !oerr ora 1144
01144, 00000, “File size (%s blocks) exceeds maximum of %s blocks”
// *Cause: Specified file size is larger than maximum allowable size value.
// *Action: Specify a smaller size.
2、添加一个maxsize为unlimited的数据文件
SQL> alter tablespace users add datafile ‘/opt/oracle/oradata/test/user32g.dbf’ size 10M AUTOEXTEND ON maxsize unlimited;
Tablespace altered.
SQL> select MAXBYTES/1024/1024/1024 from dba_data_files where file_name=’/opt/oracle/oradata/test/user32g.dbf’;
MAXBYTES/1024/1024/1024
———————–
31.9999847
你是不是有疑惑，为什么我添加32g的数据文件报错，为什么我添加一个数据文件maxsize设置为unlimited了，还是没有突破32g这个坎
原因分析：
由于Oracle的Rowid中使用22位来代表Block号，这22位最多只能代表2^22-1(4194303)个数据块，而在我们一般情况下使用的数据块大小为8k，所以数据文件的理论大小最大为： 31.9999924G
至于maxsize为unlimited时候，数据文件的大小为什么只有31.9999847G(blocks：4194301.99)，比最大块数(4194303)少了一块，也许是和数据库和系统之间的衔接原因导致。
由上面的分析我们可以知道，数据文件大小和db_block_size有关，那我们可以得到这样的数据文件最大理论值
| 数据块 | 数据文件 |
| 2KB | 8GB |
| 4KB | 16GB |
| 8KB | 32GB |
| 16KB | 64GB |
| 32KB | 128GB |
注：以上规则适用于smallfile tablespace 下的数据文件，bigfile tablespace 下的数据文件不受此限制
ASM简单管理(2)
1、磁盘组卸载/挂载
--查看当前磁盘组状态
SQL> select group_number,name,state,total_mb,free_mb from v$asm_diskgroup;
GROUP_NUMBER NAME STATE TOTAL_MB FREE_MB
------------ -------------------- ---------------------- ---------- ----------
1 ARCHIVELOG MOUNTED 12637 12585
2 DATA MOUNTED 10228 7644
3 FLASHBACK MOUNTED 7836 7786
4 DG2 MOUNTED 5114 5012
--卸载dg2磁盘组
SQL> alter diskgroup dg2 dismount;
Diskgroup altered.
SQL> select group_number,name,state,total_mb,free_mb from v$asm_diskgroup;
GROUP_NUMBER NAME STATE TOTAL_MB FREE_MB
------------ -------------------- ---------------------- ---------- ----------
1 ARCHIVELOG MOUNTED 12637 12585
2 DATA MOUNTED 10228 7644
3 FLASHBACK MOUNTED 7836 7786
0 DG2 DISMOUNTED 0 0
--挂载dg2磁盘组
SQL> alter diskgroup dg2 mount;
Diskgroup altered.
SQL> select group_number,name,state,total_mb,free_mb from v$asm_diskgroup;
GROUP_NUMBER NAME STATE TOTAL_MB FREE_MB
------------ -------------------- ---------------------- ---------- ----------
1 ARCHIVELOG MOUNTED 12637 12585
2 DATA MOUNTED 10228 7644
3 FLASHBACK MOUNTED 7836 7786
4 DG2 MOUNTED 5114 5012
--卸载所有磁盘组
SQL> alter diskgroup all dismount;
Diskgroup altered.
SQL> select group_number,name,state,total_mb,free_mb from v$asm_diskgroup;
GROUP_NUMBER NAME STATE TOTAL_MB FREE_MB
------------ -------------------- ---------------------- ---------- ----------
0 DATA DISMOUNTED 0 0
0 DG2 DISMOUNTED 0 0
0 FLASHBACK DISMOUNTED 0 0
0 ARCHIVELOG DISMOUNTED 0 0
--挂载所有磁盘组
SQL> alter diskgroup all mount;
Diskgroup altered.
SQL> select group_number,name,state,total_mb,free_mb from v$asm_diskgroup;
GROUP_NUMBER NAME STATE TOTAL_MB FREE_MB
------------ -------------------- ---------------------- ---------- ----------
2 DATA MOUNTED 10228 7644
3 DG2 MOUNTED 5114 5012
4 FLASHBACK MOUNTED 7836 7786
1 ARCHIVELOG MOUNTED 12637 12585
--如果当前磁盘组有实例在访问，则不能被卸载
SQL> alter diskgroup data dismount;
alter diskgroup data dismount
*
ERROR at line 1:
ORA-15032: not all alterations performed
ORA-15027: active use of diskgroup "DATA" precludes its dismount
2、磁盘组目录
--创建目录
SQL> alter diskgroup dg2 add directory '+DG2/CHENGFEI';
Diskgroup altered.
--使用asmcmd查看（export ORACLE_SID=+ASM;asmcmd进入)
ASMCMD> pwd
+DG2
ASMCMD> ls -l
Type Redund Striped Time Sys Name
N CHENGFEI/
--目录重命名
SQL> alter diskgroup dg2 rename directory '+DG2/CHENGFEI' TO '+DG2/XIFENFEI';
Diskgroup altered.
ASMCMD> ls
XIFENFEI/
--删除目录
SQL> alter diskgroup dg2 drop directory '+DG2/xifenfei';
Diskgroup altered.
ASMCMD> ls
ASMCMD>
3、文件别名
--添加别名
SQL> alter diskgroup dg2 add alias '+dg2/XIFENFEI' for '+dg2/tasm/datafile/XFF.256.754832383';
Diskgroup altered.
--使用asmcmd查看别名是否成功
ASMCMD> pwd
+dg2
ASMCMD> ls -l
Type Redund Striped Time Sys Name
Y TASM/
N XIFENFEI => +DG2/TASM/DATAFILE/XFF.256.754832383
--别名重命名
SQL> alter diskgroup dg2 rename alias '+DG2/XIFENFEI' TO '+DG2/FEIFEI.DBF';
Diskgroup altered.
N XIFENFEI => +DG2/TASM/DATAFILE/XFF.256.754832383
ASMCMD> ls -l
Type Redund Striped Time Sys Name
N FEIFEI.DBF => +DG2/TASM/DATAFILE/XFF.256.754832383
Y TASM/
--删除别名
SQL> alter diskgroup dg2 drop alias '+dg2/feifei.dbf';
Diskgroup altered.
ASMCMD> ls -l
Type Redund Striped Time Sys Name
Y TASM/
说明：
1）asmcmd命令行操作中，目录文件名不区分大小写，命令关键字区分大小写
2）磁盘组中的一个文件，最多只能建立一个别名可以通过v$asm_alias视图查看别名的相关信息
ASM简单管理(1)