1、现象
Mon Aug 8 08:16:07 2011
The value (30) of MAXTRANS parameter ignored.
kupprdp: master process DM00 started with pid=91, OS id=21125
to execute – SYS.KUPM$MCP.MAIN(‘SYS_EXPORT_TABLE_01’, ‘YDZJ’, ‘KUPC$C_1_20110808081609’, ‘KUPC$S_1_20110808081609’, 0);
kupprdp: worker process DW01 started with worker id=1, pid=57, OS id=21127
to execute – SYS.KUPW$WORKER.MAIN(‘SYS_EXPORT_TABLE_01’, ‘YDZJ’);
kupprdp: worker process DW02 started with worker id=2, pid=95, OS id=21502
to execute – SYS.KUPW$WORKER.MAIN(‘SYS_EXPORT_TABLE_01’, ‘YDZJ’);
2、原因
这个时间段正在使用expdp进行数据库导出，当然从错误描述也可以看出和导出操作有关，不过当时导出是成功，没有报任何错误。 在metalink查询 ，发现这是Version: 10.1.0.2 to 11.1.0.7 (任何平台) 的一个bug，Bug号为：6347775
这是由于在创建备份主表时使用了如下语句：
CREATE TABLE table_name (columns_list) INITRANS 10 MAXTRANS 30
而Maxtrans参数在10gR1时已经废弃，所以在告警日志中就提示：
The value (30) of MAXTRANS parameter ignored. ， 此bug没有什么影响，所以可以忽略。
3、重现
SQL> create table xff(xifenfei varchar2(100)) initrans 10 maxtrans 20;
Table created.
查看alert日志
Mon Aug 8 16:11:28 2011
The value (20) of MAXTRANS parameter ignored.
利用DNSPod域名解析实现blog高可用
因为blog的服务器不太稳定，经常有异常断网或者关机现象发生，今天采用了利用一些基本资源，使用了高可用，整体思路采用DNSPod域名解析提供的免费检测服务器状态，如果主服务器发生宕机，自动切换到备机功能（监控功能）实现高可用。
1、配置备用服务器和主服务器一致，使用相同的域名均可以访问（修改电脑的hosts文件测试）
在本次配置中，主要是固定url的功能
1.1）apache中启用该模块mod_rewrite
1.2）.htaccess文件中内容
RewriteEngine On
RewriteBase /
RewriteRule ^index\.php$ – [L]
RewriteCond %{REQUEST_FILENAME} !-f
RewriteCond %{REQUEST_FILENAME} !-d
RewriteRule . /index.php [L]
1.3）启用FollowSymLinks功能
“Options Indexes FollowSymLinks
… …
AllowOverride None”
改为
“Options Indexes FollowSymLinks
… …
AllowOverride All”
2、配置DNSPod监控功能，添加备机ip到域名解析中
3、既然思路是当一台服务器发生故障时，域名解析自动切换到备用服务器，那么问题就是主服务器和备服务器的数据要一致，因为是网站，涉及的数据一致主要就是数据库内容一致，网站上传编辑附件一致即可
3.1）mysql数据库同步（因为这个是个人blog，对数据的实时性，一致要求不是异常的高，所以采用crontab实现同步，如果要求高，可以采用mysql 主主复制实现）
#!/bin/bash
#目标数据库
target_db=*******
target_user=*******
target_pwd=*******
target_host=*******
#源数据库
source_db=*******
source_user=*******
source_pwd=*******
source_host=*******
#其他配置
dumptime=$(date +%y%m%d%H%M)
dumpfile=/home/chengfei/backup/mysql/${dumptime}.sql
logfile=/home/chengfei/backup/log/syc_mysql.log
#开始操作
echo "开始数据库同步……`date`……">>${logfile}
#导出数据库
/usr/bin/mysqldump -u${source_user} -p${source_pwd} -h${source_host} ${source_db} >${dumpfile} 2>> ${logfile}
#导入数据库
mysql -u${target_user} -p${target_pwd} -h${target_host}<<XFF>> ${logfile}
drop database ${target_db};
create database ${target_db};
use ${target_db};
source ${dumpfile}
exit
XFF
#操作结束
echo "数据库同步结束……`date`……">>${logfile}
echo ''>>${logfile}
--crontab
00 2,12,14,16,18,20,22,24 * * * /home/chengfei/backup/script/sysc_mysql.sh
3.2)因为我这个blog上传编辑图片类的内容非常少，暂时没有考虑网站文件同步功能，以后可以考虑实现。目前初步方案是采用scp或者ftp结合crontab功能实现定时同步
V$LOCK视图相关知识
1、V$LOCK视图结构
| 列名 | 类型 | 字段说明 |
| ADDR | RAW（4 | 8） | Address of lock state object |
| KADDR | RAW（4|8） | Address of lock |
| SID | NUMBER | 会话的sid，可以和v$session 关联 |
| TYPE | VARCHAR2(2) | 区分该锁保护对象的类型（表4） TM – DML enqueue TX – Transaction enqueue UL – User supplied –我们主要关注TX和TM两种类型的锁 –UL锁用户自己定义的，一般很少会定义，基本不用关注 –其它均为系统锁，会很快自动释放，不用关注 |
| ID1 ID2 |
NUMBER | ID1,ID2的取值含义根据type的取值而有所不同 对于TM 锁 ID1表示被锁定表的object_id 可以和dba_objects视图关联取得具体表信息，ID2 值为0 对于TX 锁 ID1以十进制数值表示该事务所占用的回滚段号和事务槽slot number号,其组形式: 0xRRRRSSSS,RRRR=RBS/UNDO NUMBER,SSSS=SLOT NUMBER ID2 以十进制数值表示环绕wrap的次数，即事务槽被重用的次数 |
| LMODE | NUMBER |
|
| REQUEST | NUMBER | 同LMODE –大于0时，表示当前会话被阻塞，其它会话占有改锁的模式 |
| CTIME | NUMBER |
|
| BLOCK | NUMBER |
|
2、其它相关视图说明
| 视图名 | 描述 | 主要字段说明 |
| v$session | 查询会话的信息和锁的信息。 | sid,serial#：表示会话信息。 program：表示会话的应用程序信息。 row_wait_obj#：表示等待的对象,和dba_objects中的object_id相对应。 lockwait :该会话等待的锁的地址,与v$lock的kaddr对应. |
| v$session_wait | 查询等待的会话信息。 | sid：表示持有锁的会话信息。 Seconds_in_wait：表示等待持续的时间信息 Event：表示会话等待的事件，锁等于enqueue |
| dba_locks | 对v$lock的格式化视图。 | Session_id：和v$lock中的Sid对应。 Lock_type：和v$lock中的type对应。 Lock_ID1： 和v$lock中的ID1对应。 Mode_held,mode_requested：和v$lock中的lmode,request相对应。 |
| v$locked_object | 只包含DML的锁信息，包括回滚段和会话信息。 | Xidusn,xidslot,xidsqn：表示回滚段信息。和v$transaction相关联。 Object_id：表示被锁对象标识。 Session_id：表示持有锁的会话信息。 Locked_mode：表示会话等待的锁模式的信息，和v$lock中的lmode一致。 |
表3
| 锁模式 | 锁描述 | 解释 | SQL操作 |
| 0 | none | ||
| 1 | NULL | 空 | Select |
| 2 | SS(Row-S) | 行级共享锁，其他对象只能查询这些数据行 | Select for update Lock for update Lock row share |
| 3 | SX(Row-X) | 行级排它锁,在提交前不允许做DML操作 | Insert/update/Delete Lock row share |
| 4 | S(Share) | 共享锁 | Create index Lock share |
| 5 | SSX(S/Row-X) | 共享行级排它锁 | Lock share row exclusive |
| 6 | X(Exclusive) | 排它锁 | Alter table Drop able Drop index Truncate table Lock exclusive |
表4
| System Type | Description | System Type | Description |
| BL | Buffer hash table instance | NA..NZ | Library cache pin instance (A..Z = namespace) |
| CF | Control file schema global enqueue | PF | Password File |
| CI | Cross-instance function invocation instance | PI, PS | Parallel operation |
| CU | Cursor bind | PR | Process startup |
| DF | datafile instance | QA..QZ | Row cache instance (A..Z = cache) |
| DL | Direct loader parallel index create | RT | Redo thread global enqueue |
| DM | Mount/startup db primary/secondary instance | SC | System change number instance |
| DR | Distributed recovery process | SM | SMON |
| DX | Distributed transaction entry | SN | Sequence number instance |
| FS | File set | SQ | Sequence number enqueue |
| HW | Space management operations on a specific segment | SS | Sort segment |
| IN | Instance number | ST | Space transaction enqueue |
| IR | Instance recovery serialization global enqueue | SV | Sequence number value |
| IS | Instance state | TA | Generic enqueue |
| IV | Library cache invalidation instance | TS | Temporary segment enqueue (ID2=0) |
| JQ | Job queue | TS | New block allocation enqueue (ID2=1) |
| KK | Thread kick | TT | Temporary table enqueue |
| LA .. LP | Library cache lock instance lock (A..P = namespace) | UN | User name |
| MM | Mount definition global enqueue | US | Undo segment DDL |
| MR | Media recovery | WL | Being-written redo log instance |
Total insert collisions (ogg)
1、错误现象
Replicating from ECP.TAB_UUM_PACKAGE to RWGL.TAB_UUM_USER:
*** Total statistics since 2011-08-05 10:34:10 ***
Total inserts 20.00
Total updates 0.00
Total deletes 0.00
Total discards 0.00
Total operations 20.00
Total insert collisions 20.00
2、错误原因
RWGL.TAB_UUM_USER表上有insert触发器，导致失败。因为触发器使得插入操作为插入和触发器中的操作绑定为了一个整体，现在因为触发器失败，导致插入失败，而且还会丢失该条插入记录，需要查找出该条记录比较困难。
3、解决方案
采用自治事件结合异常捕获
自治事件使得触发器和插入操作相互分离，异常捕获记录触发器失败的原因，插入到日志表中，通过该表，可以查询查失败的记录，然后人工干预，触发器实例：
create or replace trigger ogg_t
before insert on t_1
for each row
declare
tid NUMBER;
err VARCHAR2(100);
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
SELECT t.id2 INTO tid FROM t_2 t WHERE NAME=:new.Name;
INSERT INTO t_3 VALUES(tid,:new.name);
COMMIT;
EXCEPTION
WHEN TOO_MANY_ROWS THEN
INSERT INTO t_error VALUES(:new.id,'TOO_MANY_ROWS');
COMMIT;
WHEN NO_DATA_FOUND THEN
INSERT INTO t_error VALUES(:new.id,'NO_DATA_FOUND');
COMMIT;
WHEN OTHERS THEN
err:=SUBSTR(SQLERRM(SQLCODE),1,100);
INSERT INTO t_error VALUES(:new.id,err);
COMMIT;
end ogg_t;
1)PRAGMA AUTONOMOUS_TRANSACTION;
自治事务，就是说触发器不管是成功，还是失败，数据库同步程序都能够同步成功数据到目标端
2)COMMIT;
因为采用了自治事件，所以begin end中的操作是独立与数据库中数据，需要单独提交
3)EXCEPTION
添加异常处理
4)INSERT INTO t_error VALUES(:new.id,’TOO_MANY_ROWS’);（类此语句，注意commit）
建立一张错误日志表（根据具体情况决定），如果触发器失败，把错误记录到该表中，以后出现问题查找很方便（要求：通过该表能够查询到那条语句的触发器执行失败。失败原因，失败时间，额外列（用于确定对应记录））
ORA-01012: not logged on
今天接到现场反馈，数据库出现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）