月归档：七月 2011

mysqld_safe启动报错

发表于 2011 年 7 月 7 日 由 惜分飞

使用二进制编译文件的mysqld_safe启动报错，如下：
[mysql@ECP-UC-DB1 ~]$ mysqld_safe
110707 21:31:19 mysqld_safe Logging to ‘/opt/mysql/mysqldata/ECP-UC-DB1.err’.
110707 21:31:19 mysqld_safe The file /usr/local/mysql/bin/mysqld
does not exist or is not executable. Please cd to the mysql installation
directory and restart this script from there as follows:
./bin/mysqld_safe&
See http://dev.mysql.com/doc/mysql/en/mysqld-safe.html for more information

解决方法：
su – root
mkdir /usr/local/mysql/bin
ln -s /opt/mysql/product/5.5/bin/mysqld /usr/local/mysql/bin/mysqld

再次执行mysqld_safe
[mysql@ECP-UC-DB1 ~]$ mysqld_safe
110707 21:32:37 mysqld_safe Logging to ‘/opt/mysql/mysqldata/ECP-UC-DB1.err’.
110707 21:32:37 mysqld_safe Starting mysqld daemon with databases from /opt/mysql/mysqldata

启动结果：
mysql 26885 23452 0 21:32 pts/0 00:00:00 /bin/sh /opt/mysql/product/5.5/bin/mysqld_safe
mysql 27653 26885 0 21:32 pts/0 00:00:00 /usr/local/mysql/bin/mysqld –basedir=/opt/mysql/product/5.5 –datadir=/opt/mysql/mysqldata –plugin-dir=/usr/local/mysql/lib/plugin –log-error=/opt/mysql/mysqldata/ECP-UC-DB1.err –open-files-limit=8192 –pid-file=/var/run/mysqld/mysqld.pid –socket=/var/run/mysqld/mysqld.sock –port=3306

问题存在原因：
1、mysqld_safe只认识/usr/local/mysql/bin/mysqld（也许是配置问题）
2、my.cnf只能放置在/etc/下面，不然使用–defaults-file也不能读取my.cnf文件（有疑惑）

发表在 MySQL安装配置 | 评论关闭

mysql 安装并启动多个实例

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

1、数据库实例目录
drwxrwxrwx 6 mysql mysql 4096 Jul 6 23:25 mysqldata
drwxrwxr-x 5 mysql mysql 4096 Jul 6 23:23 mysqldata1
drwxrwxr-x 5 mysql mysql 4096 Jul 6 23:27 mysqldata2

2、创建实例
/opt/mysql/product/5.5/scripts/mysql_install_db –basedir=/opt/mysql/product/5.5 –datadir=/opt/mysql/mysqldata –user=mysql
/opt/mysql/product/5.5/scripts/mysql_install_db –basedir=/opt/mysql/product/5.5 –datadir=/opt/mysql/mysqldata1 –user=mysql
/opt/mysql/product/5.5/scripts/mysql_install_db –basedir=/opt/mysql/product/5.5 –datadir=/opt/mysql/mysqldata2 –user=mysql

3、修改my.cnf文件
[mysqld_multi]
mysqld = /opt/mysql/product/5.5/bin/mysqld
mysqladmin = /opt/mysql/product/5.5/bin/mysqladmin
user = root
password = passw0rd

[mysqld3306]
port = 3306
socket = /var/run/mysqld/mysqld3306.sock
pid-file = /var/run/mysqld/mysqld3306.pid
basedir = /opt/mysql/product/5.5
datadir = /opt/mysql/mysqldata

[mysqld3307]
port = 3307
socket = /var/run/mysqld/mysqld3307.sock
pid-file = /var/run/mysqld/mysqld3307.pid
basedir = /opt/mysql/product/5.5
datadir = /opt/mysql/mysqldata1

[mysqld3307]
port = 3307
socket = /var/run/mysqld/mysqld3308.sock
pid-file = /var/run/mysqld/mysqld3308.pid
basedir = /opt/mysql/product/5.5
datadir = /opt/mysql/mysqldata2
注：其他无关参数省略，日志等文件路径也不能相同（未写明）

4、启动数据库
mysqld_multi –defaults-file=/etc/mysql/my.cnf start 3306
mysqld_multi –defaults-file=/etc/mysql/my.cnf start 3307
mysqld_multi –defaults-file=/etc/mysql/my.cnf start 3308

5、登录数据库修改root密码
mysql -S /var/run/mysqld/mysqld3306.sock
mysql -S /var/run/mysqld/mysqld3307.sock
mysql -S /var/run/mysqld/mysqld3308.sock

use mysql
UPDATE user SET password=PASSWORD(‘passw0rd’) WHERE user=’root’;
FLUSH PRIVILEGES;
说明：三个库上分别操作

6、关闭数据库
mysqld_multi –defaults-file=/etc/mysql/my.cnf stop 3306
mysqld_multi –defaults-file=/etc/mysql/my.cnf stop 3307
mysqld_multi –defaults-file=/etc/mysql/my.cnf stop 3308

7、全部启动/关闭
mysqld_multi –defaults-file=/etc/mysql/my.cnf start 3306-3308
mysqld_multi –defaults-file=/etc/mysql/my.cnf stop 3306-3308

发表在 MySQL安装配置 | 评论关闭

Data Guard出现gap sequence修复

发表于 2011 年 7 月 5 日 由 惜分飞

一、出现gap sequence现象
备库

Fetching gap sequence in thread 1, gap sequence 710-716
Tue May 31 15:02:38 2011
FAL[client]: Failed to request gap sequence 
 GAP - thread 1 sequence 710-716
 DBID 3240478808 branch 746916894
FAL[client]: All defined FAL servers have been attempted.
-------------------------------------------------------------
Check that the CONTROL_FILE_RECORD_KEEP_TIME initialization
parameter is defined to a value that is sufficiently large
enough to maintain adequate log switch information to resolve
archivelog gaps.
-------------------------------------------------------------

主库

Tue May 31 13:50:47 2011
FAL[server]: Fail to queue the whole FAL gap
 GAP - thread 1 sequence 710-716
 DBID 3240478808 branch 746916894

二、修复操作
1、查询备库的scn

SQL> SELECT CURRENT_SCN FROM V$DATABASE;
CURRENT_SCN
-----------
    1154337
--在出现意外datafile header scn不一致的时候,需要根据提示归档日志,找出最小scn

2、确定主库是否添加数据文件

SQL> select FILE#,name from v$datafile where CREATION_CHANGE#> =1154337;
no rows selected

确定主库在这个scn之后是否有添加数据文件，如果添加文件，需要手工在备库添加

3、备库停止日志应用

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

4、主库增量备份并传输到备库上
主库进行增量备份

RMAN> BACKUP INCREMENTAL FROM SCN  1154337 DATABASE 
FORMAT '/home/oracle/xff_%U' tag 'XIFENFEI';
[oracle@localhost ~]$ scp xff* 192.168.1.30:/home/oracle/rman

说明：主库之前必须要做过rman的全备（没有全备的库，基于scn的增量备份也能够成功）

5、备库上进行恢复

RMAN> CATALOG START WITH '/home/oracle/rman';
RMAN> RECOVER DATABASE NOREDO;

说明：CATALOG START WITH是10g及其以后版本中才存在功能，没有该功能可以采用catalog或者复制主库的控制文件，rman备份放置和主库备份时相同目录实现。

6、主库上创建standby controlfile文件并传输到备库

RMAN> BACKUP CURRENT CONTROLFILE FOR STANDBY 
FORMAT '/home/oracle/xff_ctl.bck';
[oracle@localhost ~]$ scp xff_ctl.bck 192.168.1.30:/home/oracle/rman

创建standby controlfile两步可以需要根据实际情况考虑,大多数情况下不需要

7、备库恢复控制文件

RMAN> shutdown;
RMAN> STARTUP NOMOUNT;
RMAN> RESTORE STANDBY CONTROLFILE FROM '/home/oracle/rman/xff_ctl.bck';
RMAN> alter database mount;

8、清空备库日志组

SQL> ALTER DATABASE CLEAR LOGFILE GROUP 1; 
注：如果采用了standby log模式，不需要清空，如果清空会出现
SQL> ALTER DATABASE CLEAR LOGFILE GROUP 1;
ALTER DATABASE CLEAR LOGFILE GROUP 1
*
ERROR at line 1:
ORA-19527: physical standby redo log must be renamed
ORA-00312: online log 1 thread 1: '/u01/oradata/xienfei/redo01.log'

说明：如果没有采用standby log模式，有几组需要清空几组

9、备库重设flashback

SQL> ALTER DATABASE FLASHBACK OFF; 
SQL> ALTER DATABASE FLASHBACK ON;

10、备库重新接收并应用日志

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

三、修复成功标志
1、sql中操作
在主库中执行alter system switch logfile;
分别主备库中执行select max(sequence#) from v$archived_log;如果一致标示修复成功

2、通过alert文件
主库

PING[ARC0]: Error 3113 when pinging standby xff.
Tue May 31 14:11:51 2011
Thread 1 advanced to log sequence 719
  Current log# 3 seq# 719 mem# 0: /u01/oradata/xienfei/redo03.log
Tue May 31 14:20:05 2011
Thread 1 advanced to log sequence 720
  Current log# 1 seq# 720 mem# 0: /u01/oradata/xienfei/redo01.log
Tue May 31 14:20:16 2011
ARC0: Standby redo logfile selected for thread 1 sequence 719 for destination LOG_ARCHIVE_DEST_2

备库

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION
Tue May 31 15:30:37 2011
Attempt to start background Managed Standby Recovery process (xff)
MRP0 started with pid=18, OS id=14704
Tue May 31 15:30:37 2011
MRP0: Background Managed Standby Recovery process started (xff)
Managed Standby Recovery not using Real Time Apply
 parallel recovery started with 2 processes
Media Recovery Log /u01/archive/1_718_746916894.arc
Tue May 31 15:30:43 2011
Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION
Tue May 31 15:30:52 2011
RFS[1]: Successfully opened standby log 4: '/u01/oradata/xienfei/s_redo1.log'
Media Recovery Log /u01/archive/1_719_746916894.arc
Media Recovery Waiting for thread 1 sequence 720
发表在 Data Guard | 2 条评论