分类目录归档：数据库

undo损坏恢复–有事务

发表于 2011 年 6 月 10 日 由 惜分飞

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’;

发表在 Oracle备份恢复 | 评论关闭

undo损坏恢复–无事务

发表于 2011 年 6 月 10 日 由 惜分飞

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’;

发表在 Oracle备份恢复 | 评论关闭

使用xtrabackup 配置主从服务器

发表于 2011 年 6 月 3 日 由 惜分飞

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;

发表在 MySQL安装配置 | 2 条评论