Author Archives: 惜分飞

ORA-01410: invalid ROWID 分析

Posted on by

1、跟踪错误表
alter session set max_dump_file_size=unlimited;
alter session set db_file_multiblock_read_count=1;
alter session set events ‘10200 trace name context forever, level 1’;
执行下面语句
SELECT COUNT(*) FROM QXTDEV1.TAB_SMS_MT_DOWN
报错
ORA-01410: invalid ROWID
alter session set events ‘10200 trace name context off’;

2、查看跟踪文件尾部
Consistent read started for block 0 : 00400652
env: (scn: 0x0abf.1ee04657 xid: 0x0000.000.00000000 uba: 0x00000000.0000.00 statement num=0 parent xid: xid: 0x0000.000.00000000 scn: 0x0000.00000000 0sch: scn: 0x0000.00000000)
Consistent read finished for block 0 : 400652
Consistent read finished for block 0 : 400652
通过这个文件看到文件读到400652的块的时候终止了，错误应该在这块上

3、分析400652块内容
–转换为10进制
SELECT to_number(‘400652′,’xxxxxxxx’) FROM dual;
–求文件号和块号
SELECT DBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE(4195922),
DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK(4195922)
FROM DUAL;

4、dump文件号为1，块号为1618的内容
alter system dump datafile 1 block 1618;
Block header dump: 0x00400652
Object id on Block? Y
seg/obj: 0xde csc: 0xabf.1e400c0c itc: 2 flg: O typ: 1 – DATA
fsl: 0 fnx: 0x0 ver: 0x01
得到object_id为de，转化为十进制
SELECT to_number(‘de’,’xx’) FROM dual;
查询all_objects表
SELECT * FROM All_Objects WHERE object_id=222;
发现这个数据库（绝对块号400652，相对：文件号1，块号1618）指向的表为dual

5、得出结论
我们的QXTDEV1.TAB_SMS_MT_DOWN表中的数据块指向了sys.dual表，从而出现了ORA-01410错误

undo损坏恢复–有事务

Posted on by

startup nomount;
create pfile=’/tmp/pfile’ from spfile;

修改pfile
*._allow_resetlogs_corruption=true
*._corrupted_rollback_segments=(_SYSSMU1$,_SYSSMU2$,_SYSSMU3$,_SYSSMU4$,
_SYSSMU5$,_SYSSMU6$,_SYSSMU7$,_SYSSMU8$,_SYSSMU9$,_SYSSMU10$)

startup pfile=’/tmp/pfile’ mount;
–undo数据文件不用离线也可以打开的时候（2），不行采用下面的方法
alter database datafile 2 offline drop;
alter database open;

CREATE UNDO TABLESPACE UNDOTBS01
DATAFILE ‘/opt/oracle/oradata/xifenfei/UNDOTBS01.dbf’ SIZE 50M;
select segment_name,status from dba_rollback_segs;–查看是否有回滚段被创建，处于offline状态
shutdown immediate;

修改pfile：
*.undo_tablespace=’UNDOTBS01′

startup pfile=’/tmp/pfile’
–alter tablespace undotbs1 offline normal;（2）–使数据文件先离线，如果开始已经离线，这里不需要处理
select segment_name,status from dba_rollback_segs;
drop rollback segment “_SYSSMUx$”;
drop tablespace UNDOTBS02 including contents and datafiles;
select * from v$recover_file;–验证是否还有文件需要恢复
shutdown immediate

修改参数文件
#*._allow_resetlogs_corruption=true
#*._corrupted_rollback_segments=(_SYSSMU1$,_SYSSMU2$,_SYSSMU3$,_SYSSMU4$,_SYSSMU5$,
_SYSSMU6$,_SYSSMU7$,_SYSSMU8$,_SYSSMU9$,_SYSSMU10$)

startup pfile=’/tmp/pfile’;
create spfile from pfile=’/tmp/pfile’;

undo损坏恢复–无事务

Posted on by

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 配置主从服务器

Posted on by

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分割函数

Posted on by

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进行查询。