月归档：八月 2011

重建控制文件

发表于 2011 年 8 月 23 日 由 惜分飞 
#  使用alter database backup controlfile to trace;产生控制文件的trace file

-- Below are two sets of SQL statements, each of which creates a new
-- control file and uses it to open the database. The first set opens
-- the database with the NORESETLOGS option and should be used only if
-- the current versions of all online logs are available. The second
-- set opens the database with the RESETLOGS option and should be used
-- if online logs are unavailable.
-- The appropriate set of statements can be copied from the trace into
-- a script file, edited as necessary, and executed when there is a
-- need to re-create the control file.
#  需要重建控制文件的话， 如果online redo 是可用的, 使用NORESETLOGS 创建新控制文件打开数据库
#  如果online redo是不可用的, 那么需要使用RESETLOGS 创建新控制文件打开数据库, 
#  联机日志被重置，数据有可能丢失
--
--     Set #1. NORESETLOGS case
--
-- The following commands will create a new control file and use it
-- to open the database.
-- Data used by Recovery Manager will be lost.
-- Additional logs may be required for media recovery of offline
-- Use this only if the current versions of all online logs are
-- available.
-- After mounting the created controlfile, the following SQL
-- statement will place the database in the appropriate
-- protection mode:
--  ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE
#   下面的命令用来创建一个新的控制文件来开启数据库。 用于RMAN的数据将会丢失。
#   对于offline datafiles 可能需要额外的归档日志用来做介质恢复， 
#   当当前版本的所有online redo log都没有问题的情况下使用noresetlogs。

STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "XIENFEI" NORESETLOGS FORCE LOGGING ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 2921
LOGFILE
  GROUP 1 '/u01/oradata/xienfei/redo01.log'  SIZE 5M,
  GROUP 2 '/u01/oradata/xienfei/redo02.log'  SIZE 5M,
  GROUP 3 '/u01/oradata/xienfei/redo03.log'  SIZE 5M
-- STANDBY LOGFILE
DATAFILE
  '/u01/oradata/xienfei/system01.dbf',
  '/u01/oradata/xienfei/undotbs01.dbf',
  '/u01/oradata/xienfei/sysaux01.dbf',
  '/u01/oradata/xienfei/users01.dbf',
  '/u01/oradata/xienfei/example01.dbf',
  '/u01/oradata/xienfei/xff01.dbf',
  '/u01/oradata/xienfei/cf01.dbf',
  '/u01/oradata/xienfei/cfxff01.dbf'
CHARACTER SET ZHS16GBK
;
-- Commands to re-create incarnation table
-- Below log names MUST be changed to existing filenames on
-- disk. Any one log file from each branch can be used to
-- re-create incarnation records.
-- ALTER DATABASE REGISTER LOGFILE '/u01/archive/1_1_756837539.arc';
#  有些时候可能需要先注册归档日志，再才能够恢复
-- Recovery is required if any of the datafiles are restored backups,
-- or if the last shutdown was not normal or immediate.
#  恢复数据库
RECOVER DATABASE
-- All logs need archiving and a log switch is needed.
#  归档所有日志
ALTER SYSTEM ARCHIVE LOG ALL;
-- Database can now be opened normally.
#  打开数据库
ALTER DATABASE OPEN;
-- Commands to add tempfiles to temporary tablespaces.
-- Online tempfiles have complete space information.
-- Other tempfiles may require adjustment.
#  添加临时表空间
ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/oradata/xienfei/temp01.dbf'
     SIZE 32505856  REUSE AUTOEXTEND ON NEXT 655360  MAXSIZE 32767M;
-- End of tempfile additions.

##    下面的RESETLOGS 重建语句， online redo log 中的内容都会丢失，所有的备份将无效 
##    仅仅在online log 损坏的情况下使用 resetlogs 创建控制文件开启数据库。  
##    resetlog创建控制文件后的开启步骤与noresetlog有一些不一样，请注意下面步骤

--     Set #2. RESETLOGS case
--
-- The following commands will create a new control file and use it
-- to open the database.
-- Data used by Recovery Manager will be lost.
-- The contents of online logs will be lost and all backups will
-- be invalidated. Use this only if online logs are damaged.
-- After mounting the created controlfile, the following SQL
-- statement will place the database in the appropriate
-- protection mode:
--  ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "XIENFEI" RESETLOGS FORCE LOGGING ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 2921
LOGFILE
  GROUP 1 '/u01/oradata/xienfei/redo01.log'  SIZE 5M,
  GROUP 2 '/u01/oradata/xienfei/redo02.log'  SIZE 5M,
  GROUP 3 '/u01/oradata/xienfei/redo03.log'  SIZE 5M
-- STANDBY LOGFILE
DATAFILE
  '/u01/oradata/xienfei/system01.dbf',
  '/u01/oradata/xienfei/undotbs01.dbf',
  '/u01/oradata/xienfei/sysaux01.dbf',
  '/u01/oradata/xienfei/users01.dbf',
  '/u01/oradata/xienfei/example01.dbf',
  '/u01/oradata/xienfei/xff01.dbf',
  '/u01/oradata/xienfei/cf01.dbf',
  '/u01/oradata/xienfei/cfxff01.dbf'
CHARACTER SET ZHS16GBK
;
-- Commands to re-create incarnation table
-- Below log names MUST be changed to existing filenames on
-- disk. Any one log file from each branch can be used to
-- re-create incarnation records.
#  注册归档日志
-- ALTER DATABASE REGISTER LOGFILE '/u01/archive/1_1_756837539.arc';
-- Recovery is required if any of the datafiles are restored backups,
-- or if the last shutdown was not normal or immediate.
#  使用历史控制文件恢复数据库（因为redo log是新建，所以对于redo 而言，控制文件是新建的）
RECOVER DATABASE USING BACKUP CONTROLFILE
-- Database can now be opened zeroing the online logs.
#  使用RESETLOGS方式打开数据库
ALTER DATABASE OPEN RESETLOGS;
-- Commands to add tempfiles to temporary tablespaces.
-- Online tempfiles have complete space information.
-- Other tempfiles may require adjustment.
#  添加临时表空间
ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/oradata/xienfei/temp01.dbf'
     SIZE 32505856  REUSE AUTOEXTEND ON NEXT 655360  MAXSIZE 32767M;
-- End of tempfile additions.
发表在 Oracle备份恢复 | 评论关闭

EMD_MAINTENANCE.EXECUTE_EM_DBMS_JOB_PROCS维护

发表于 2011 年 8 月 22 日 由 惜分飞

1、建立
1.1）emd_maintenance包
exec sysman.emd_maintenance.submit_em_dbms_jobs;
commit;

1.2）创建job 

DECLARE
  X NUMBER;
BEGIN
  SYS.DBMS_JOB.SUBMIT
  ( job       => X 
   ,what      => 'EMD_MAINTENANCE.EXECUTE_EM_DBMS_JOB_PROCS();'
   ,next_date => to_date('18/08/2011 21:59:17','dd/mm/yyyy hh24:mi:ss')
   ,interval  => 'SYSDATE + 1 / (24 * 60)'
   ,no_parse  => FALSE
  );
  SYS.DBMS_OUTPUT.PUT_LINE('Job Number is: ' || to_char(x));
COMMIT;
END;

2、停止和启动
2.1）停止
EXEC DBMS_JOB.BROKEN(job#,TRUE);
commit;

2.2）启用
EXEC DBMS_JOB.BROKEN(job#,FALSE);
commit;

3、删除
2.1）emd_maintenance包
exec sysman.emd_maintenance.remove_em_dbms_jobs;
commit;

3.2）删除job
dbms_job.remove(job#);
commit;

注：因为EMD_MAINTENANCE.EXECUTE_EM_DBMS_JOB_PROCS是sysman用户下面用于收集em相关信息，如果要进行操作，最好使用sysman用户进行

发表在 Oracle | 评论关闭

Linux逻辑卷管理(LVM)

发表于 2011 年 8 月 20 日 由 惜分飞

前言：LVM基本术语
物理存储介质（The physical media）
这里指系统的存储设备：硬盘，如：/dev/hda、/dev/sda等等，是存储系统最低层的存储单元。
物理卷（physical volume）
物理卷就是指硬盘分区或从逻辑上与磁盘分区具有同样功能的设备(如RAID)，是LVM的基本存储逻辑块，但和基本的物理存储介质（如分区、磁盘等）比较，却包含有与LVM相关的管理参数。
卷组（Volume Group）
LVM卷组类似于非LVM系统中的物理硬盘，其由物理卷组成。可以在卷组上创建一个或多个“LVM分区”（逻辑卷），LVM卷组由一个或多个物理卷组成。
逻辑卷（logical volume）
LVM的逻辑卷类似于非LVM系统中的硬盘分区，在逻辑卷之上可以建立文件系统(比如/home或者/usr等)。
PE（physical extent）
每一个物理卷被划分为称为PE(Physical Extents)的基本单元，具有唯一编号的PE是可以被LVM寻址的最小单元。PE的大小是可配置的，默认为4MB。
LE（logical extent）
逻辑卷也被划分为被称为LE(Logical Extents) 的可被寻址的基本单位。在同一个卷组中，LE的大小和PE是相同的，并且一一对应。

一、创建逻辑卷的步骤
1）通过pvcreate命令将linux分区处理成物理卷（PV)；
2）通过vgcreate命令将创建好的物理卷处理成卷组（Vg)；
3）通过lvcreate命令将卷组分成若干个逻辑卷（Lv)；
之后我们可以对逻辑卷进行格式化，挂载，删除等操作，我们可以动态的调整逻辑卷的大小，并且该操作不会影响我们在逻辑卷（Lv)上的数据。

二、物理卷创建管理操作
1、物理分区信息
[root@node1 ~]# fdisk /dev/sdd

The number of cylinders for this disk is set to 2610.
There is nothing wrong with that, but this is larger than 1024,
and could in certain setups cause problems with:
1) software that runs at boot time (e.g., old versions of LILO)
2) booting and partitioning software from other OSs
(e.g., DOS FDISK, OS/2 FDISK)

Command (m for help): p

Disk /dev/sdd: 21.4 GB, 21474836480 bytes
255 heads, 63 sectors/track, 2610 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes

Device Boot Start End Blocks Id System
/dev/sdd1 1 200 1606468+ 83 Linux
/dev/sdd2 201 400 1606500 83 Linux
/dev/sdd3 401 600 1606500 83 Linux
/dev/sdd4 601 2610 16145325 5 Extended
/dev/sdd5 601 800 1606468+ 83 Linux
/dev/sdd6 801 1000 1606468+ 83 Linux
/dev/sdd7 1001 1200 1606468+ 83 Linux
/dev/sdd8 1201 1400 1606468+ 83 Linux
/dev/sdd9 1600 1800 1614532+ 83 Linux
/dev/sdd10 1401 1599 1598404+ 83 Linux
/dev/sdd11 1801 2000 1606468+ 83 Linux
/dev/sdd12 2001 2200 1606468+ 83 Linux
/dev/sdd13 2201 2400 1606468+ 83 Linux
/dev/sdd14 2401 2610 1686793+ 83 Linux

Partition table entries are not in disk order

2、创建物理卷
[root@node1 ~]# pvcreate /dev/sdd*
Device /dev/sdd not found (or ignored by filtering).
Physical volume “/dev/sdd1″ successfully created
Physical volume “/dev/sdd10″ successfully created
Physical volume “/dev/sdd11″ successfully created
Physical volume “/dev/sdd12″ successfully created
Physical volume “/dev/sdd13″ successfully created
Physical volume “/dev/sdd14″ successfully created
Physical volume “/dev/sdd2″ successfully created
Physical volume “/dev/sdd3″ successfully created
Device /dev/sdd4 not found (or ignored by filtering).
Physical volume “/dev/sdd5″ successfully created
Physical volume “/dev/sdd6″ successfully created
Physical volume “/dev/sdd7″ successfully created
Physical volume “/dev/sdd8″ successfully created
Physical volume “/dev/sdd9″ successfully created

3、删除物理卷
[root@node1 ~]# pvremove /dev/sdd1
Labels on physical volume “/dev/sdd1″ successfully wiped

4、添加物理卷
[root@node1 ~]# pvcreate /dev/sdd1
Physical volume “/dev/sdd1″ successfully created

5、查看物理卷信息
[root@node1 ~]# pvscan
PV /dev/sdd1 lvm2 [1.53 GB]
PV /dev/sdd2 lvm2 [1.53 GB]
PV /dev/sdd3 lvm2 [1.53 GB]
PV /dev/sdd5 lvm2 [1.53 GB]
PV /dev/sdd6 lvm2 [1.53 GB]
PV /dev/sdd7 lvm2 [1.53 GB]
PV /dev/sdd8 lvm2 [1.53 GB]
PV /dev/sdd9 lvm2 [1.54 GB]
PV /dev/sdd10 lvm2 [1.52 GB]
PV /dev/sdd11 lvm2 [1.53 GB]
PV /dev/sdd12 lvm2 [1.53 GB]
PV /dev/sdd13 lvm2 [1.53 GB]
PV /dev/sdd14 lvm2 [1.61 GB]
Total: 13 [19.99 GB] / in use: 0 [0 ] / in no VG: 13 [19.99 GB]

6、查看物理卷详细参数
root@node1 ~]# pvdisplay /dev/sdd5
“/dev/sdd5″ is a new physical volume of “1.53 GB”
— NEW Physical volume —
PV Name /dev/sdd5
VG Name
PV Size 1.53 GB
Allocatable NO
PE Size (KByte) 0
Total PE 0
Free PE 0
Allocated PE 0
PV UUID 7Ms1jT-wxUI-2d7l-h24c-t17F-J9vL-fz2zHM

二、卷组的创建管理
1、创建卷组
[root@node1 ~]# vgcreate -s 8M xifenfei /dev/sdd{1,2,3,5,6,7,8,9,10,11,12,13,14}
Volume group “xifenfei” successfully created
格式：vgcreate [-s <8M|16M|...>] xifenfei /dev/sdb{1,2,3…}
-s：指定扩展块（PE）的大小，默认为4MB；（相当与磁盘上的簇，扩展逻辑卷的基本单位。 后面的值可以是8M 16M 32M 64M …..最多65532个扩展块。
xifenfei：新创建的卷组的名字

2、查看卷组信息
[root@node1 ~]# vgscan
Reading all physical volumes. This may take a while…
Found volume group “xifenfei” using metadata type lvm2

[root@node1 ~]# pvscan
PV /dev/sdd1 VG xifenfei lvm2 [1.53 GB / 1.53 GB free]
PV /dev/sdd2 VG xifenfei lvm2 [1.53 GB / 1.53 GB free]
PV /dev/sdd3 VG xifenfei lvm2 [1.53 GB / 1.53 GB free]
PV /dev/sdd5 VG xifenfei lvm2 [1.53 GB / 1.53 GB free]
PV /dev/sdd6 VG xifenfei lvm2 [1.53 GB / 1.53 GB free]
PV /dev/sdd7 VG xifenfei lvm2 [1.53 GB / 1.53 GB free]
PV /dev/sdd8 VG xifenfei lvm2 [1.53 GB / 1.53 GB free]
PV /dev/sdd9 VG xifenfei lvm2 [1.54 GB / 1.54 GB free]
PV /dev/sdd10 VG xifenfei lvm2 [1.52 GB / 1.52 GB free]
PV /dev/sdd11 VG xifenfei lvm2 [1.53 GB / 1.53 GB free]
PV /dev/sdd12 VG xifenfei lvm2 [1.53 GB / 1.53 GB free]
PV /dev/sdd13 VG xifenfei lvm2 [1.53 GB / 1.53 GB free]
PV /dev/sdd14 VG xifenfei lvm2 [1.60 GB / 1.60 GB free]
Total: 13 [19.98 GB] / in use: 13 [19.98 GB] / in no VG: 0 [0 ]

3、查看卷组的详细参数
[root@node1 ~]# vgdisplay /dev/xifenfei
— Volume group —
VG Name xifenfei
System ID
Format lvm2
Metadata Areas 13
Metadata Sequence No 1
VG Access read/write
VG Status resizable
MAX LV 0
Cur LV 0
Open LV 0
Max PV 0
Cur PV 13
Act PV 13
VG Size 19.98 GB
PE Size 8.00 MB
Total PE 2557
Alloc PE / Size 0 / 0
Free PE / Size 2557 / 19.98 GB
VG UUID dGa5e5-Jjef-GhYN-jpP0-EHMj-pAK1-Y9jJG2

4、删除卷组中物理卷
[root@node1 ~]# vgreduce xifenfei /dev/sdd3
Removed “/dev/sdd3″ from volume group “xifenfei”

5、添加物理卷到卷组中
[root@node1 ~]# vgextend xifenfei /dev/sdd3
Volume group “xifenfei” successfully extended

6、删除卷组
[root@node1 ~]# vgremove /dev/xifenfei
Volume group “xifenfei” successfully removed

四、逻辑卷的创建及管理
1、创建逻辑卷
1.1）
[root@node1 ~]# lvcreate -L 2G -n data xifenfei
Logical volume “data” created
-L 2G：设置逻辑卷的大小为512M。
-n data：设置逻辑卷的名字为data ；表示法：/dev/卷组名/data
xifenfei ：设置是有那个卷组生成的逻辑卷
1.2）
[root@node1 ~]# lvcreate -l 128 -n xff xifenfei
Logical volume “xff” created
-l 128：生成的逻辑卷使用128个扩展块，逻辑卷大小=32*PE（默认4M，本处为8M）则为1024M
-n xff：逻辑卷的名字；
xifenfei ：设置是有那个卷组生成的逻辑卷

2、删除逻辑卷
[root@node1 ~]# lvremove /dev/xifenfei/xff
Do you really want to remove active logical volume “xff”? [y/n]: y
Logical volume “xff” successfully removed

3、查看逻辑卷信息
[root@node1 ~]# lvscan
ACTIVE ‘/dev/xifenfei/data’ [2.00 GB] inherit
ACTIVE ‘/dev/xifenfei/xff’ [1.00 GB] inherit

4、逻辑卷详细信息
[root@node1 ~]# lvdisplay /dev/xifenfei/data
— Logical volume —
LV Name /dev/xifenfei/data
VG Name xifenfei
LV UUID 84pp2v-GnfP-X3cL-a3fj-q3Cs-FDjh-i2d02u
LV Write Access read/write
LV Status available
# open 0
LV Size 2.00 GB
Current LE 256
Segments 2
Allocation inherit
Read ahead sectors auto
– currently set to 256
Block device 253:0

5、格式化逻辑卷
[root@node1 ~]# mkfs.ext3 /dev/xifenfei/data
mke2fs 1.35 (28-Feb-2004)
Filesystem label=
OS type: Linux
Block size=4096 (log=2)
Fragment size=4096 (log=2)
262144 inodes, 524288 blocks
26214 blocks (5.00%) reserved for the super user
First data block=0
Maximum filesystem blocks=536870912
16 block groups
32768 blocks per group, 32768 fragments per group
16384 inodes per group
Superblock backups stored on blocks:
32768, 98304, 163840, 229376, 294912

Writing inode tables: done
Creating journal (8192 blocks): done
Writing superblocks and filesystem accounting information: done

This filesystem will be automatically checked every 29 mounts or
180 days, whichever comes first. Use tune2fs -c or -i to override.

6、挂载逻辑卷
[root@node1 ~]# mkdir /data
[root@node1 ~]# mount /dev/xifenfei/data /data
[root@node1 ~]# df -h
Filesystem 容量 已用 可用 已用% 挂载点
/dev/sda2 18G 9.3G 7.5G 56% /
/dev/sda1 99M 13M 82M 14% /boot
none 395M 0 395M 0% /dev/shm
/dev/drbd0 1.6G 656M 810M 45% /opt/mysql
/dev/mapper/xifenfei-data
2.0G 36M 1.9G 2% /data

7、增大逻辑卷大小（因为减小实际情况中很少用，所以不做实验lvreduce）
[root@node1 ~]# lvextend -L 5.5G /dev/xifenfei/data
Extending logical volume data to 5.50 GB
Logical volume data successfully resized
[root@node1 ~]# resize2fs /dev/xifenfei/data
resize2fs 1.35 (28-Feb-2004)
/dev/xifenfei/data is mounted; can’t resize a mounted filesystem!

[root@node1 ~]# umount /data
[root@node1 ~]# resize2fs /dev/xifenfei/data
resize2fs 1.35 (28-Feb-2004)
Please run ‘e2fsck -f /dev/xifenfei/data’ first.

[root@node1 ~]# e2fsck -f /dev/xifenfei/data
e2fsck 1.35 (28-Feb-2004)
Pass 1: Checking inodes, blocks, and sizes
Pass 2: Checking directory structure
Pass 3: Checking directory connectivity
Pass 4: Checking reference counts
Pass 5: Checking group summary information
/dev/xifenfei/data: 11/655360 files (9.1% non-contiguous), 29800/1310720 blocks
[root@node1 ~]# resize2fs /dev/xifenfei/data
resize2fs 1.35 (28-Feb-2004)
Resizing the filesystem on /dev/xifenfei/data to 1441792 (4k) blocks.
The filesystem on /dev/xifenfei/data is now 1441792 blocks long.

[root@node1 ~]# mount /dev/xifenfei/data /data
[root@node1 ~]# df -h
Filesystem 容量 已用 可用 已用% 挂载点
/dev/sda2 18G 9.3G 7.5G 56% /
/dev/sda1 99M 13M 82M 14% /boot
none 395M 0 395M 0% /dev/shm
/dev/drbd0 1.6G 656M 810M 45% /opt/mysql
/dev/mapper/xifenfei-data
5.5G 37M 5.3G 1% /data

8、删除逻辑卷
[root@node1 ~]# lvremove /dev/xifenfei/xff
Do you really want to remove active logical volume “xff”? [y/n]: y
Logical volume “xff” successfully removed

发表在 Linux | 评论关闭