startup nomount;
create pfile=’/tmp/pfile’ from spfile;
修改pfile
*.undo_management=’MANUAL’
*.undo_tablespace=’SYSTEM’
#*.undo_tablespace=’UNDOTBS02′
#*.undo_management=’AUTO’
startup pfile=’/tmp/pfile’ mount;
alter database datafile 2 offline drop;
alter database open;
drop tablespace undotbs1 including contents;
select segment_name,status from dba_rollback_segs;
drop rollback segment “_SYSSMUx”$;
drop tablespace undotbs1 including contents;
CREATE UNDO TABLESPACE UNDOTBS02
DATAFILE ‘/opt/oracle/oradata/xifenfei/UNDOTBS02.dbf’ SIZE 50M
shutdown immediate;
修改pfile
#*.undo_management=’MANUAL’
#*.undo_tablespace=’SYSTEM’
*.undo_tablespace=’UNDOTBS02′
*.undo_management=’AUTO’
startup pfile=’/tmp/pfile’;
create spfile from pfile=’/tmp/pfile’;
使用xtrabackup 配置主从服务器
1、备份主机数据
innobackupex –user=root –password=xifenfei –defaults-file=/etc/my.cnf –stream /tmp/mysql 2>/tmp/mysql.log | gzip> /tmp/data/mysql.tar.gz
2、查看/tmp/mysql.log，记录下当前日志位置
innobackupex: MySQL binlog position: filename ‘mysql-bin.000018’, position 107
3、备份文件恢复
scp /tmp/data/mysql.tar.gz root@192.168.1.2:/tmp/
mkdir /tmp/mysql
cd /tmp/mysql
tar izxvf /tmp/mysql.tar.gz
–恢复数据库
innobackupex –apply-log –user=root –password=xifenfei /tmp/mysql
–复制到my.cnf中指定地方
innobackupex –copy-back –user=root –password=xifenfei /tmp/mysql
chmod -R mysql.mysql /var/lib/mysql
4、修改my.cnf文件
主服务器：
server-id=1
innodb_flush_log_at_trx_commit=1
sync-binlog=1
从服务器：
server-id=2
relay-log=/var/lib/mysql/replicate
relay-log-index=/var/lib/mysql/replicate.index
read-only
7、添加复制用户（主数据库上）
GRANT REPLICATION SLAVE ON *.*
TO ‘repl’@’192.168.1.2’ IDENTIFIED BY ‘xifenfei’;
8、配置从数据库
CHANGE MASTER TO
MASTER_HOST=’192.168.1.4′,
MASTER_USER=’repl’,
MASTER_PASSWORD=’xifenfei’,
MASTER_LOG_FILE=’mysql-bin.000018′,
MASTER_LOG_POS=107;
start slave;
9、查看主从是否正常
SHOW SLAVE STATUS\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.4
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000022
Read_Master_Log_Pos: 1185
Relay_Log_File: replicate.000007
Relay_Log_Pos: 588
Relay_Master_Log_File: mysql-bin.000022
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 1185
Relay_Log_Space: 1627
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
SHOW MASTER STATUS\G
*************************** 1. row ***************************
File: mysql-bin.000022
Position: 1185
Binlog_Do_DB:
Binlog_Ignore_DB:
1 row in set (0.00 sec)
SHOW PROCESSLIST\G
–从服务器进程
*************************** 2. row ***************************
Id: 22
User: system user
Host:
db: NULL
Command: Connect
Time: 1136
State: Waiting for master to send event
Info: NULL
*************************** 3. row ***************************
Id: 23
User: system user
Host:
db: NULL
Command: Connect
Time: 1676370
State: Slave has read all relay log; waiting for the slave I/O thread to update it
Info: NULL
–主服务器进程
*************************** 1. row ***************************
Id: 14
User: repl
Host: 192.168.1.2:34594
db: NULL
Command: Binlog Dump
Time: 1207
State: Master has sent all binlog to slave; waiting for binlog to be updated
Info: NULL
10、管理从服务器
start slave;
reset slave;
stop slave;
oracle分割函数
1、创建varry
CREATE OR REPLACE TYPE Varchar2Varray IS VARRAY(100) of VARCHAR2(40);
2、创建分割函数
CREATE OR REPLACE FUNCTION f_strsplit (STRING VARCHAR2, substring VARCHAR2)
RETURN varchar2varray
IS
len INTEGER := LENGTH (substring);
lastpos INTEGER := 1 - len;
pos INTEGER;
num INTEGER;
i INTEGER := 1;
ret varchar2varray := varchar2varray (NULL);
v_str VARCHAR2 (20);
/**自定义split函数,将指定的字符串按指定的标志符分割成字符数组*/
BEGIN
LOOP
pos := INSTR (STRING, substring, lastpos + len);
IF pos > 0
THEN --found
num := pos - (lastpos + len);
ELSE --not found
num := LENGTH (STRING) + 1 - (lastpos + len);
END IF;
IF i > ret.LAST
THEN
ret.EXTEND;
END IF;
v_str := SUBSTR (STRING, lastpos + len, num);
--DBMS_OUTPUT.put_line (v_str);
ret(i) := v_str;
EXIT WHEN pos = 0;
lastpos := pos;
i := i + 1;
END LOOP;
RETURN ret;
END;
3、调用
select * from tab_dailyreport_user where user_id in (
SELECT * FROM
( TABLE( select f_strsplit(user_invite,',')
from tab_dailyreport_user t
where t.user_id = 168148)));
说明：主要是改写开发提过来的一个sql语句的帮助
select * from tab_dailyreport_user where user_id in (
--'166445','164216','171128','184427','160113','160133','160134','160138','160144','160163','160587','166457','167914','168076','168192','168997','169842','169901','184407','184747','185089','185130','208134','208141','208143','208183','160142','166455','167838','168074','168194','168666','185088','185138','185145','208103','169844','171071','160107','166421','166874','168193','179871','179872','184096','184228','184232','184269','184774','184969','185111','207871','160167','161813','14880','134','164355','168149')
select substr(regexp_replace,3,length(regexp_replace)-4) from (
select regexp_replace((select user_invite
from tab_dailyreport_user t
where t.user_id = 168148),
'(,)',
chr(39)||','||chr(39)) regexp_replace
from dual) )
括号里面的语句查询出来的结果是注释部分，直接使用注释的部分在in中可以查询，如果使用里面的语句不能查询，他们的需求是想把外面的user_id在里面的语句中的，可是oracle会把里面的in查询出来的结果作为一个整体，从而出现number类型不能和varchar类型匹配的提示，采用方法是分割in里面查询出来的语句，然后类型转换为table进行查询。
该关注的sql语句
一般来说，调优的第一手资料，很可能就是典型业务期的一个statspack报告，那么如何根据statspack报告来判断是哪些SQL消耗了最多的系统资源?哪些SQL是最需要调整的呢?这里给出了一个大致的优化思路。当然，思路是死的，人是活的，优化也需要随需应变。
一般来说，需要关注下面四种Top SQL
消耗最多CPU的(逻辑IO过多)
导致过多物理I/O的
执行次数较频繁的
执行时间较长的
我们知道，一个语句的响应时间有个很著名的公式：
响应时间=服务时间+等待时间
其中服务时间就是CPU为执行该语句花费的时间。
服务时间=分析时间+递归时间+执行时间
分析时间是CPU用于分析语句的时间，递归时间是CPU用于语句的递归SQL的时间，剩下的则就是CPU用于执行语句的真正时间了。
那么，上面的这些时间信息从哪里来的?Oracle提供的系统统计信息中就有部分的时间统计信息：
服务时间=CPU used by this session
分析时间=parse time cpu
递归时间=recursive cpu usage
那么，执行时间就可以根据上面三个统计信息计算得出：
执行时间=CPU used by this session – parse time cpu – recursive cpu usage
如果执行时间在整个响应时间中占较大的比例，那么下一步就是找出那些造成了最多逻辑IO的SQL语句，可以从statspack报告的SQL ordered by Gets部分找到。
如果分析时间在整个响应时间中占较大的比例，那么下一步就是查找哪些SQL分析过多，这在statspack报告中在SQL ordered by Parse Calls中列出。
如果等待时间在整个响应时间中占较大的比例，并且主要是块读取相关的等待时，下一步就是找出哪些SQL造成了过多的物理读，可以查看statspack报告中的SQL ordered by Reads部分。
那么，根据上面列出的一个简单的原则，我们需要关注三个关于CPU时间的统计信息: CPU used by this session, parse time cpu和recursive cpu usage，以及top5等待事件中和IO相关的等待时间。如果是其他的一些等待事件出现在Top5中，那么可能需要根据不同的等待事件来分析原因了。然后优先调优时间消耗最多的相关SQL。
除了上面的SQL ordered by Gets(逻辑IO最多)，SQL ordered by Parse Calls(软解析过多)，SQL ordered by Reads(物理IO过多)，statspack还按照其他的一些方式列出了Top SQL，这些Top SQL在某些情况下都是需要给予特别关注的。比如：
SQL ordered by Executions 执行次数超过100的
SQL ordered by Sharable Memory 占用library cache超过1M的
SQL ordered by Version Count 子cursor超过20的
如果没有statspack，那么根据v$sysstat/v$sesstat中的统计信息，结合v$sql/v$sqlarea，一样可以得到相关的SQL。
v$sql对于每一个子cursor都有一行统计记录，而v$sqlarea则对同一个父cursor只有一行统计记录，也就是v$sqlarea是对v$sql按照父cursor进行group by后的一个结果。这两个视图中都有诸如buffer_gets,parse_calls,disk_reads,,executions,sharable_mem等列，和上面提到的statspack中列出Top SQL的条件对应。
insert引起的死锁
DEADLOCK DETECTED ( ORA-00060 )
[Transaction Deadlock]
The following deadlock is not an ORACLE error. It is a
deadlock due to user error in the design of an application
or from issuing incorrect ad-hoc SQL. The following
information may aid in determining the deadlock:
Deadlock graph:
———Blocker(s)——– ———Waiter(s)———
Resource Name process session holds waits process session holds waits
TX-000a0002-00005420 68 440 X 65 496 S
TX-0001001b-00002b19 65 496 X 68 440 S
session 440: DID 0001-0044-000A2346 session 496: DID 0001-0041-000CBA54
session 496: DID 0001-0041-000CBA54 session 440: DID 0001-0044-000A2346
Rows waited on:
Session 496: obj – rowid = 00014282 – AAAUKCAAMAAAEZrAAA
(dictionary objn – 82562, file – 12, block – 18027, slot – 0)
Session 440: no row
Information on the OTHER waiting sessions:
Session 496:
pid=65 serial=58006 audsid=1896040 user: 88/VAS
O/S info: user: web_meg, term: unknown, ospid: , machine: ezg-web1
program: JDBC Thin Client
application name: JDBC Thin Client, hash value=2546894660
Current SQL Statement:
insert into TAB_XN_CONTENT_TEMP (key,content) values (:1,:2)
End of information on OTHER waiting sessions.
Current SQL statement for this session:
insert into TAB_XN_CONTENT_TEMP (key,content) values (:1,:2)
我一看到这个错误，有点不明白，oracle 的insert操作竟然导致表被锁，然后查找些资料终于有了眉目：
当ORACLE执行insert等DML语句时，会首先自动在所要操作的表上申请一个TM锁，当TM锁获得后，再自动申请TX类型的锁。当两个或多个会话在表的同一条记录上执行DML语言时，第一个会话在记录上加锁，其它的会话处于等待状态，一直到第一个会话提交后TX锁释放，其它的会话才可以加锁。考虑是因为两个insert语句同时试图向一个表中插入PK或unique值相同的数据，而造成其中会话被阻塞，等待其它会话提交或回滚，因而造成死锁。这种情况，只要其中任何一个session提交，另外一个就会报出ORA-00001：违反唯一性约束条件，死锁终止；或者其中一个session回滚，另外一个即可正常执行。
通过对这段话的理解，应该是一个会话插入了一条记录未提交，然后另外一个会话继续插入主键或者唯一索引列相同的记录，导致死锁的发生。
环境模拟：
在会话1中执行下面语句
CREATE TABLE t1(ID NUMBER);
ALTER TABLE t1 ADD primary key (ID);
INSERT INTO t1 VALUES(1);
INSERT INTO t1 VALUES(2);
然后在会话2中执行下面语句
INSERT INTO t1 VALUES(1);
INSERT INTO t1 VALUES(2);
在会话3中查询死锁情况
通过上面模拟，重现了insert死锁现象，说明红色标注出来的文字的正确性
DBMS_STATS常用方法（收集oracle信息）
–收集数据库信息
EXEC DBMS_STATS.gather_database_stats;
EXEC DBMS_STATS.gather_database_stats(estimate_percent => 15);
–收集schema信息
EXEC DBMS_STATS.gather_schema_stats(‘SCOTT’);
EXEC DBMS_STATS.gather_schema_stats(‘SCOTT’, estimate_percent => 15);
–收集表信息
EXEC DBMS_STATS.gather_table_stats(‘SCOTT’, ‘EMPLOYEES’);
EXEC DBMS_STATS.gather_table_stats(‘SCOTT’, ‘EMPLOYEES’, estimate_percent => 15);
–收集index信息
EXEC DBMS_STATS.gather_index_stats(‘SCOTT’, ‘EMPLOYEES_PK’);
EXEC DBMS_STATS.gather_index_stats(‘SCOTT’, ‘EMPLOYEES_PK’, estimate_percent => 15);
–删除收集信息
EXEC DBMS_STATS.delete_database_stats;
EXEC DBMS_STATS.delete_schema_stats(‘SCOTT’);
EXEC DBMS_STATS.delete_table_stats(‘SCOTT’, ‘EMPLOYEES’);
EXEC DBMS_STATS.delete_index_stats(‘SCOTT’, ‘EMPLOYEES_PK’);
–创建备份收集信息表
begin
dbms_stats.create_stat_table(USER,stattab => ‘STAT_TABLE’);
end;
–备份收集信息
BEGIN
dbms_stats.export_table_stats(USER,tabname => ‘FEI_T’,stattab => ‘STAT_TABLE’);
END;
–删除收集信息
BEGIN
DBMS_STATS.delete_table_stats(USER,tabname => ‘FEI_T’);
END;
–导入收集信息
BEGIN
dbms_stats.IMPORT_TABLE_STATS(USER,’FEI_T’,stattab => ‘STAT_TABLE’);
END;
–说明：
当前用户可以使用user代替用户名
分析表相关对象信息cascade => true
oracle的监听日志太大，正确的删除步骤
1.进入$ORACLE_HOME/network/log,查看日志大小 du -a
2. 把 listen log 关闭.
lsnrctl set log_status off;
3.把日志改名,mv listener.log listener.log_bak
4.启动listen log
lsnrctl set log_status on;此时检查log目录下,会自动生成一个新的log文件,rm掉之前的监听文件即可
表在线重定义(有主键)
主要是使用DBMS_REDEFINITION包实现，我这里主要是实现一个由普通表转为分区表的处理
在线重定义的表自行验证，看该表是否可以重定义
EXEC DBMS_REDEFINITION.CAN_REDEF_TABLE(user, ‘T_DEF’, DBMS_REDEFINITION.CONS_USE_PK);
–如果T_DEF表无主键，请添加上，不然会爆如下错误
begin DBMS_REDEFINITION.CAN_REDEF_TABLE(user, ‘T_DEF’, DBMS_REDEFINITION.CONS_USE_PK); end;
ORA-12089: cannot online redefine table “CHF”.”T_DEF” with no primary key
ORA-06512: at “SYS.DBMS_REDEFINITION”, line 137
ORA-06512: at “SYS.DBMS_REDEFINITION”, line 1479
ORA-06512: at line 2
执行表的在线重定义
EXEC DBMS_REDEFINITION.START_REDEF_TABLE(USER, ‘T_DEF’, ‘T_DEF_NEW’);
同步数据
exec dbms_redefinition.sync_interim_table(USER, ‘T_DEF’, ‘T_DEF_NEW’);
执行结束在线定义过程
EXEC DBMS_REDEFINITION.FINISH_REDEF_TABLE(user, ‘T_DEF’, ‘T_DEF_NEW’);
说明：
1、和使用交换分区的办法处理，这个是在线的，不用停业务
2、在线重定义处理后，原表中的数据不会被清空，可能需要额外的空间
3、处理速度没有直接使用交互分区快
Rman异机恢复
一、查看数据库的dbid
select dbid from v$database;
二、修改参数文件
create pfile=’/tmp/pfile’ from spfile;
直接恢复spfile
三、rman操作
1.rman target /
2.startup nomount pfile=’xxxxxx’
3.set DBID=XXXXXXX
4.恢复控制文件
run{
allocate channel c1 type disk;
restore controlfile from '控制文件rman备份路径';
release channel c1;
}
5.加载控制文件
alter database mount;
6.恢复
run{
allocate channel c1 type disk;
[Set newname for datafile 1 to '新数据文件路径';]
restore database;
[switch datafile all;]
--rman中修改控制文件中datafile path
--sql "alter database rename file ''/opt/oracle/oradata/test/xff_02.dbf'' to ''/opt/oracle/oradata/test/xifenfei02.dbf''"
recover database;
release channel c1;
}
vi中行号管理
显示行号 :set number 或 :set nu
去除行号 :set nonumber 或 :set nonu