Archive for 2014年11月3日


LVM磁盘管理

如何挂载一块从别的机器上取下的做了LVM的硬盘:
1.fdisk -l //查看新增的硬盘是否已经被识别
2.vgscan //扫描LVM卷
3.vgchange -ay
4.lvscan //如果正常则会显示出硬盘的LV状态都是 active
5.mount -t ext3 /dev/VGname/LVname /mnt/lvmdisk/
——————————————————————————————-
如何将一块新硬盘添加到现有的LVM中,以达到扩容目的:
1.fdisk -l //查看新增的硬盘是否已经被识别
2.fdisk /dev/sdb //创建一个新的分区sdb1,并使用t参数标记为8e(即Linux LVM)
>n
>t
>8e
3.pvcreate //建立物理卷
4.vgextend VolGroup00 /dev/sdb1 //将新增的屋里卷加入到卷组中去
5.lvextend -L +800G /dev/VolGroup00/LogVol00 //将新增的80G硬盘的所有空间都加到逻辑卷中去
6.RHEL4: ext2online /dev/VolGroup00/LogVol00
RHEL5: resize2fs -p /dev/VolGroup00/LogVol00
//激活新增的空间
7.df -h //此时便可以看到新增的空间了
——————————————————————————————-
如何删除一个现有的LVM:
1.umount 所有vg0下的lv
2.lvremove /dev/vg0/lv0
3.vgchange -an /dev/vg0 (休眠vg0,-ay是激活)
4.vgremove vg0 //移除vg0
——————————————————————————————-
如何删除一个现有LVM中的物理卷,以取出新增的硬盘:
1.转移数据 pvmove /dev/sdb1 [sdc1] //如果想指定转移的物理卷则在后面输入,默认是其它地方
2.pvreduce vg0 /dev/sdb1 把sdb1从卷组中删除
——————————————————————————————-
一些常用的LVM管理命令:
扩展VG: vgextend vg0(卷组名) /dev/sdb1(PV名)
扩展LV: lvextend -L +10G(空间大小) /dev/vg0/lv0(LV名)
查看信息: vgdisplay /dev/vg0 , lvdisplay /dev/vg0/lv0
数据迁移: pvmove /dev/sdb1 /dev/sdc1
——————————————————————————————-
所有命令列表:
1.extendfs 扩展一个离线文件系统
2.lvchange 改变一个逻辑卷的的属性
3.lvcreate 在卷组中创建一个逻辑卷
4.lvdisplay 显示逻辑卷的信息
5.lvextend 增加分配给逻辑卷的物理区域数
6.lvlnboot 将逻辑卷设为启动,交换或内存映像卷
7.lvmerge 将以前镜像的卷合并成一个逻辑镜像卷
8.lvreduce 减少分配给逻辑卷的物理区域数
9.lvremove 从卷组中删除一个或多个逻辑卷
10.lvrmboot 删除联接到启动,交换或内存映像卷的逻辑卷
11.lvsplit 将镜像的逻辑卷分成两个逻辑卷
12.lvsync 同步在一个或多少失效逻辑卷上的逻辑卷镜像
13.pvchange 改变卷组中的物理卷的属性
14.pvcreate 创建一个可以被卷组使用的物理卷
15.pvdisplay 显示卷组中一个或多个物理卷的信息
16.pvmove 将分配的物理区域从一个物理卷转移鲐其他物理卷
17.vgcfgbackup 保存卷组LVM配置
18.vgcfgrestore 将LVM配置恢复到卷组
19.vgchange 开关卷组的一些状态
20.vgcreate 创建一个卷组
21.vgdisplay 显示卷组信息
22.vgextend 通过添加物理卷扩展一个卷组
23.vgexport 从系统输出一个卷组
24.vgimport 向系统输入一个卷组
25.vgscan 扫描卷组的系统物理卷
26.vgreduce 通过删除一个或多个物理卷减小卷组
27.vgremove 从系统上删除一个或多个卷组的定义
28.vgsync 同步在一个或多个失效卷组上的逻辑镜像

第一章:日志管理

1.forcing log switches
sql>; alter system switch logfile;
2.forcing checkpoints
sql>; alter system checkpoint;
3.adding online redo log groups
sql>; alter database add logfile [group 4]
sql>; (‘/disk3/log4a.rdo’,’/disk4/log4b.rdo’) size 1m;
4.adding online redo log members
sql>; alter database add logfile member
sql>; ‘/disk3/log1b.rdo’ to group 1,
sql>; ‘/disk4/log2b.rdo’ to group 2;
5.changes the name of the online redo logfile
sql>; alter database rename file ‘c:/oracle/oradata/oradb/redo01.log’
sql>; to ‘c:/oracle/oradata/redo01.log’;
6.drop online redo log groups
sql>; alter database drop logfile group 3;
7.drop online redo log members
sql>; alter database drop logfile member ‘c:/oracle/oradata/redo01.log’;
8.clearing online redo log files
sql>; alter database clear [unarchived] logfile ‘c:/oracle/log2a.rdo’;
9.using logminer analyzing redo logfiles
a. in the init.ora specify utl_file_dir = ‘ ‘
b. sql>; execute dbms_logmnr_d.build(‘oradb.ora’,’c:\oracle\oradb\log’);
c. sql>; execute dbms_logmnr_add_logfile(‘c:\oracle\oradata\oradb\redo01.log’,sql>; dbms_logmnr.new);
d. sql>; execute dbms_logmnr.add_logfile(‘c:\oracle\oradata\oradb\redo02.log’,sql>; dbms_logmnr.addfile);
e. sql>; execute dbms_logmnr.start_logmnr(dictfilename=>;’c:\oracle\oradb\log\oradb.ora’);
f. sql>; select * from v$logmnr_contents(v$logmnr_dictionary,v$logmnr_parameters
sql>; v$logmnr_logs);
g. sql>; execute dbms_logmnr.end_logmnr;

第二章:表空间管理

1.create tablespaces
sql>; create tablespace tablespace_name datafile ‘c:\oracle\oradata\file1.dbf’ size 100m,
sql>; ‘c:\oracle\oradata\file2.dbf’ size 100m minimum extent 550k [logging/nologging]
sql>; default storage (initial 500k next 500k maxextents 500 pctinccease 0)
sql>; [online/offline] [permanent/temporary] [extent_management_clause]
2.locally managed tablespace
sql>; create tablespace user_data datafile ‘c:\oracle\oradata\user_data01.dbf’
sql>; size 500m extent management local uniform size 10m;
3.temporary tablespace
sql>; create temporary tablespace temp tempfile ‘c:\oracle\oradata\temp01.dbf’
sql>; size 500m extent management local uniform size 10m;
4.change the storage setting
sql>; alter tablespace app_data minimum extent 2m;
sql>; alter tablespace app_data default storage(initial 2m next 2m maxextents 999);
5.taking tablespace offline or online
sql>; alter tablespace app_data offline;
sql>; alter tablespace app_data online;
6.read_only tablespace
sql>; alter tablespace app_data read only|write;
7.droping tablespace
sql>; drop tablespace app_data including contents;
8.enableing automatic extension of data files
sql>; alter tablespace app_data add datafile ‘c:\oracle\oradata\app_data01.dbf’ size 200m
sql>; autoextend on next 10m maxsize 500m;
9.change the size fo data files manually
sql>; alter database datafile ‘c:\oracle\oradata\app_data.dbf’ resize 200m;
10.Moving data files: alter tablespace
sql>; alter tablespace app_data rename datafile ‘c:\oracle\oradata\app_data.dbf’
sql>; to ‘c:\oracle\app_data.dbf’;
11.moving data files:alter database
sql>; alter database rename file ‘c:\oracle\oradata\app_data.dbf’
sql>; to ‘c:\oracle\app_data.dbf’;

第三章:表

1.create a table
sql>; create table table_name (column datatype,column datatype]….)
sql>; tablespace tablespace_name [pctfree integer] [pctused integer]
sql>; [initrans integer] [maxtrans integer]
sql>; storage(initial 200k next 200k pctincrease 0 maxextents 50)
sql>; [logging|nologging] [cache|nocache]
2.copy an existing table
sql>; create table table_name [logging|nologging] as subquery
3.create temporary table
sql>; create global temporary table xay_temp as select * from xay;
on commit preserve rows/on commit delete rows
4.pctfree = (average row size – initial row size) *100 /average row size
pctused = 100-pctfree- (average row size*100/available data space)
5.change storage and block utilization parameter
sql>; alter table table_name pctfree=30 pctused=50 storage(next 500k
sql>; minextents 2 maxextents 100);
6.manually allocating extents
sql>; alter table table_name allocate extent(size 500k datafile ‘c:/oracle/data.dbf’);
7.move tablespace
sql>; alter table employee move tablespace users;
8.deallocate of unused space
sql>; alter table table_name deallocate unused [keep integer]
9.truncate a table
sql>; truncate table table_name;
10.drop a table
sql>; drop table table_name [cascade constraints];
11.drop a column
sql>; alter table table_name drop column comments cascade constraints checkpoint 1000;
alter table table_name drop columns continue;
12.mark a column as unused
sql>; alter table table_name set unused column comments cascade constraints;
alter table table_name drop unused columns checkpoint 1000;
alter table orders drop columns continue checkpoint 1000
data_dictionary : dba_unused_col_tabs

第四章:索引

1.creating function-based indexes
sql>; create index summit.item_quantity on summit.item(quantity-quantity_shipped);
2.create a B-tree index
sql>; create [unique] index index_name on table_name(column,.. asc/desc) tablespace
sql>; tablespace_name [pctfree integer] [initrans integer] [maxtrans integer]
sql>; [logging | nologging] [nosort] storage(initial 200k next 200k pctincrease 0
sql>; maxextents 50);
3.pctfree(index)=(maximum number of rows-initial number of rows)*100/maximum number of rows
4.creating reverse key indexes
sql>; create unique index xay_id on xay(a) reverse pctfree 30 storage(initial 200k
sql>; next 200k pctincrease 0 maxextents 50) tablespace indx;
5.create bitmap index
sql>; create bitmap index xay_id on xay(a) pctfree 30 storage( initial 200k next 200k
sql>; pctincrease 0 maxextents 50) tablespace indx;
6.change storage parameter of index
sql>; alter index xay_id storage (next 400k maxextents 100);
7.allocating index space
sql>; alter index xay_id allocate extent(size 200k datafile ‘c:/oracle/index.dbf’);
8.alter index xay_id deallocate unused;

第五章:约束

1.define constraints as immediate or deferred
sql>; alter session set constraint[s] = immediate/deferred/default;
set constraint[s] constraint_name/all immediate/deferred;
2. sql>; drop table table_name cascade constraints
sql>; drop tablespace tablespace_name including contents cascade constraints
3. define constraints while create a table
sql>; create table xay(id number(7) constraint xay_id primary key deferrable
sql>; using index storage(initial 100k next 100k) tablespace indx);
primary key/unique/references table(column)/check
4.enable constraints
sql>; alter table xay enable novalidate constraint xay_id;
5.enable constraints
sql>; alter table xay enable validate constraint xay_id;

第六章:LOAD数据

1.loading data using direct_load insert
sql>; insert /*+append */ into emp nologging
sql>; select * from emp_old;
2.parallel direct-load insert
sql>; alter session enable parallel dml;
sql>; insert /*+parallel(emp,2) */ into emp nologging
sql>; select * from emp_old;
3.using sql*loader
sql>; sqlldr scott/tiger \
sql>; control = ulcase6.ctl \
sql>; log = ulcase6.log direct=true

第七章:reorganizing data

1.using export
$exp scott/tiger tables(dept,emp) file=c:\emp.dmp log=exp.log compress=n direct=y
2.using import
$imp scott/tiger tables(dept,emp) file=emp.dmp log=imp.log ignore=y
3.transporting a tablespace
sql>;alter tablespace sales_ts read only;
$exp sys/.. file=xay.dmp transport_tablespace=y tablespace=sales_ts
triggers=n constraints=n
$copy datafile
$imp sys/.. file=xay.dmp transport_tablespace=y datafiles=(/disk1/sles01.dbf, /disk2/sles02.dbf)
sql>; alter tablespace sales_ts read write;
4.checking transport set
sql>; DBMS_tts.transport_set_check(ts_list =>;’sales_ts’ .., incl_constraints=>;true);
在表transport_set_violations 中查看
sql>; dbms_tts.isselfcontained 为true 是, 表示自包含

第八章: managing password security and resources

1.controlling account lock and password
sql>; alter user juncky identified by oracle account unlock;
2.user_provided password function
sql>; function_name(userid in varchar2(30),password in varchar2(30), old_password in varchar2(30)) return boolean
3.create a profile : password setting
sql>; create profile grace_5 limit failed_login_attempts 3
sql>; password_lock_time unlimited password_life_time 30
sql>;password_reuse_time 30 password_verify_function verify_function
sql>; password_grace_time 5;
4.altering a profile
sql>; alter profile default failed_login_attempts 3
sql>; password_life_time 60 password_grace_time 10;
5.drop a profile
sql>; drop profile grace_5 [cascade];
6.create a profile : resource limit
sql>; create profile developer_prof limit sessions_per_user 2
sql>; cpu_per_session 10000 idle_time 60 connect_time 480;
7. view =>; resource_cost : alter resource cost
dba_Users,dba_profiles
8. enable resource limits
sql>; alter system set resource_limit=true;

第九章:Managing users

1.create a user: database authentication
sql>; create user juncky identified by oracle default tablespace users
sql>; temporary tablespace temp quota 10m on data password expire
sql>; [account lock|unlock] [profile profilename|default];
2.change user quota on tablespace
sql>; alter user juncky quota 0 on users;
3.drop a user
sql>; drop user juncky [cascade];
4. monitor user
view: dba_users , dba_ts_quotas

第十章:managing privileges

1.system privileges: view =>; system_privilege_map ,dba_sys_privs,session_privs
2.grant system privilege
sql>; grant create session,create table to managers;
sql>; grant create session to scott with admin option;
with admin option can grant or revoke privilege from any user or role;
3.sysdba and sysoper privileges:
sysoper: startup,shutdown,alter database open|mount,alter database backup controlfile,
alter tablespace begin/end backup,recover database
alter database archivelog,restricted session
sysdba: sysoper privileges with admin option,create database,recover database until
4.password file members: view:=>; v$pwfile_users
5.O7_dictionary_accessibility =true restriction access to view or tables in other schema
6.revoke system privilege
sql>; revoke create table from karen;
sql>; revoke create session from scott;
7.grant object privilege
sql>; grant execute on dbms_pipe to public;
sql>; grant update(first_name,salary) on employee to karen with grant option;
8.display object privilege : view =>; dba_tab_privs, dba_col_privs
9.revoke object privilege
sql>; revoke execute on dbms_pipe from scott [cascade constraints];
10.audit record view :=>; sys.aud$
11. protecting the audit trail
sql>; audit delete on sys.aud$ by access;
12.statement auditing
sql>; audit user;
13.privilege auditing
sql>; audit select any table by summit by access;
14.schema object auditing
sql>; audit lock on summit.employee by access whenever successful;
15.view audit option : view=>; all_def_audit_opts, dba_stmt_audit_opts, dba_priv_audit_opts, dba_obj_audit_opts
16.view audit result: view=>; dba_audit_trail, dba_audit_exists, dba_audit_object, dba_audit_session, dba_audit_statement

第十一章: manager role

1.create roles
sql>; create role sales_clerk;
sql>; create role hr_clerk identified by bonus;
sql>; create role hr_manager identified externally;
2.modify role
sql>; alter role sales_clerk identified by commission;
sql>; alter role hr_clerk identified externally;
sql>; alter role hr_manager not identified;
3.assigning roles
sql>; grant sales_clerk to scott;
sql>; grant hr_clerk to hr_manager;
sql>; grant hr_manager to scott with admin option;
4.establish default role
sql>; alter user scott default role hr_clerk,sales_clerk;
sql>; alter user scott default role all;
sql>; alter user scott default role all except hr_clerk;
sql>; alter user scott default role none;
5.enable and disable roles
sql>; set role hr_clerk;
sql>; set role sales_clerk identified by commission;
sql>; set role all except sales_clerk;
sql>; set role none;
6.remove role from user
sql>; revoke sales_clerk from scott;
sql>; revoke hr_manager from public;
7.remove role
sql>; drop role hr_manager;
8.display role information
view: =>;dba_roles, dba_role_privs, role_role_privs, dba_sys_privs, role_sys_privs, role_tab_privs, session_roles

第十二章: BACKUP and RECOVERY

1. v$sga, v$instance, v$process, v$bgprocess, v$database, v$datafile, v$sgastat
2. Rman need set dbwr_IO_slaves or backup_tape_IO_slaves and large_pool_size
3. Monitoring Parallel Rollback
>; v$fast_start_servers , v$fast_start_transactions
4.perform a closed database backup (noarchivelog)
>; shutdown immediate
>; cp files /backup/
>; startup
5.restore to a different location
>; connect system/manager as sysdba
>; startup mount
>; alter database rename file ‘/disk1/../user.dbf’ to ‘/disk2/../user.dbf’;
>; alter database open;
6.recover syntax
–recover a mounted database
>;recover database;
>;recover datafile ‘/disk1/data/df2.dbf’;
>;alter database recover database;
–recover an opened database
>;recover tablespace user_data;
>;recover datafile 2;
>;alter database recover datafile 2;
7.how to apply redo log files automatically
>;set autorecovery on
>;recover automatic datafile 4;
8.complete recovery:
–method 1(mounted databae)
>;copy c:\backup\user.dbf c:\oradata\user.dbf
>;startup mount
>;recover datafile ‘c:\oradata\user.dbf;
>;alter database open;
–method 2(opened database,initially opened,not system or rollback datafile)
>;copy c:\backup\user.dbf c:\oradata\user.dbf (alter tablespace offline)
>;recover datafile ‘c:\oradata\user.dbf’ or
>;recover tablespace user_data;
>;alter database datafile ‘c:\oradata\user.dbf’ online or
>;alter tablespace user_data online;
–method 3(opened database,initially closed not system or rollback datafile)
>;startup mount
>;alter database datafile ‘c:\oradata\user.dbf’ offline;
>;alter database open
>;copy c:\backup\user.dbf d:\oradata\user.dbf
>;alter database rename file ‘c:\oradata\user.dbf’ to ‘d:\oradata\user.dbf’
>;recover datafile ‘e:\oradata\user.dbf’ or recover tablespace user_data;
>;alter tablespace user_data online;
–method 4(loss of data file with no backup and have all archive log)
>;alter tablespace user_data offline immediate;
>;alter database create datafile ‘d:\oradata\user.dbf’ as ‘c:\oradata\user.dbf"
>;recover tablespace user_data;
>;alter tablespace user_data online
5.perform an open database backup
>; alter tablespace user_data begin backup;
>; copy files /backup/
>; alter database datafile ‘/c:/../data.dbf’ end backup;
>; alter system switch logfile;
6.backup a control file
>; alter database backup controlfile to ‘control1.bkp’;
>; alter database backup controlfile to trace;
7.recovery (noarchivelog mode)
>; shutdown abort
>; cp files
>; startup
8.recovery of file in backup mode
>;alter database datafile 2 end backup;
9.clearing redo log file
>;alter database clear unarchived logfile group 1;
>;alter database clear unarchived logfile group 1 unrecoverable datafile;
10.redo log recovery
>;alter database add logfile group 3 ‘c:\oradata\redo03.log’ size 1000k;
>;alter database drop logfile group 1;
>;alter database open;
or >;cp c:\oradata\redo02.log’ c:\oradata\redo01.log
>;alter database clear logfile ‘c:\oradata\log01.log’;

批量转换文件编码gb2312到utf3

for i in `find ./ -type f -name *` ; do iconv $i -f gb2312 -t utf8 -o ${i}.tmp && mv ${i}.tmp $i ; done

批量转换txt文件编码gbk到utf8

for i in `find ./ -type f -name “*.htm"` ; do iconv $i -f gbk -t utf8 -o ${i}.tmp && mv ${i}.tmp $i ; done