ORACLE 根本操作录入ITeye - 牛牛娱乐

ORACLE 根本操作录入ITeye

2019-01-12 12:53:49 | 作者: 又槐 | 标签: 权限,创立,用户 | 浏览: 2538

2、检查体系中存在的权限

select distinct privilege from dba_sys_privs order by 1;

 

-----------------------------------------------

3、oracle 权限分类

alter any cluster 修正恣意簇的权限

alter any index 修正恣意索引的权限

alter any role 修正恣意人物的权限

alter any sequence 修正恣意序列的权限

alter any snapshot 修正恣意快照的权限

alter any table 修正恣意表的权限

alter any trigger 修正恣意触发器的权限

alter cluster 修正具有簇的权限

alter database 修正数据库的权限

alter procedure 修正具有的存储进程权限

alter profile 修正资源约束简表的权限

alter resource cost 设置美谈资源开支的权限

alter rollback segment 修正回滚段的权限

alter sequence 修正具有的序列权限

alter session 修正数据库会话的权限

alter sytem 修正数据库效劳器设置的权限

alter table 修正具有的表权限

alter tablespace 修正表空间的权限

alter user 修正用户的权限

analyze 运用analyze指令剖析数据库中恣意的表、索引和簇

audit any 为恣意的数据库方针设置审计选项

audit system 答应体系操作审计

backup any table 备份恣意表的权限

become user 切换用户状况的权限

commit any table 提交表的权限

create any cluster 为恣意用户创立簇的权限

create any index 为恣意用户创立索引的权限

create any procedure 为恣意用户创立存储进程的权限

create any sequence 为恣意用户创立序列的权限

create any snapshot 为恣意用户创立快照的权限

create any synonym 为恣意用户创立同义名的权限

create any table 为恣意用户创立表的权限

create any trigger 为恣意用户创立触发器的权限

create any view 为恣意用户创立视图的权限

create cluster 为用户创立簇的权限

create database link 为用户创立的权限

create procedure 为用户创立存储进程的权限

create profile 创立资源约束简表的权限

create public database link 创立公共数据库链路的权限

create public synonym 创立公共同义名的权限

create role 创立人物的权限

create rollback segment 创立回滚段的权限

create session 创立会话的权限

create sequence 为用户创立序列的权限

create snapshot 为用户创立快照的权限

create synonym 为用户创立同义名的权限

create table 为用户创立表的权限

create tablespace 创立表空间的权限

create user 创立用户的权限

create view 为用户创立视图的权限

delete any table 删去恣意表行的权限

delete any view 删去恣意视图行的权限

delete snapshot 删去快照中行的权限

delete table 为用户删去表行的权限

delete view 为用户删去视图行的权限

drop any cluster 删去恣意簇的权限

drop any index 删去恣意索引的权限

drop any procedure 删去恣意存储进程的权限

drop any role 删去恣意人物的权限

drop any sequence 删去恣意序列的权限

drop any snapshot 删去恣意快照的权限

drop any synonym 删去恣意同义名的权限

drop any table 删去恣意表的权限

drop any trigger 删去恣意触发器的权限

drop any view 删去恣意视图的权限

drop profile 删去资源约束简表的权限

drop public cluster 删去公共簇的权限

drop public database link 删去公共数据链路的权限

drop public synonym 删去公共同义名的权限

drop rollback segment 删去回滚段的权限

drop tablespace 删去表空间的权限

drop user 删去用户的权限

execute any procedure 履行恣意存储进程的权限

execute function 履行存储函数的权限

execute package 履行存储包的权限

execute procedure 履行用户存储进程的权限

force any transaction 办理未提交的恣意业务的输出权限

force transaction 办理未提交的用户业务的输出权限

grant any privilege 颁发恣意体系特权的权限

grant any role 颁发恣意人物的权限

index table 给表加索引的权限

insert any table 向恣意表中插入行的权限

insert snapshot 向快照中插入行的权限

insert table 向用户表中插入行的权限

insert view 向用户视图中插行的权限

lock any table 给恣意表加锁的权限

manage tablespace 办理(备份可用性)表空间的权限

references table 参阅表的权限

restricted session 创立有约束的数据库会话的权限

select any sequence 运用恣意序列的权限

select any table 运用恣意表的权限

select snapshot 运用快照的权限

select sequence 运用用户序列的权限

select table 运用用户表的权限

unlimited tablespace 对表空间巨细不加约束的权限

update any table 修正恣意表中行的权限

update snapshot 修正快照中行的权限

update table 修正用户表中的行的权限

update view 修正视图中行的权限

 

-----------------------------------------------------

4、检查数据库表空间运用率

select df.tablespace_name "Tablespace",totalspace "Totalspace(MB)",freespace "Freespace(MB)",round((1-freespace/totalspace)*100,2) "Use(%)" from (select tablespace_name,round(sum(bytes)/1024/1024) totalspace from dba_data_files group by tablespace_name) df, (select tablespace_name,round(sum(bytes)/1024/1024) freespace from dba_free_space group by tablespace_name) fs where df.tablespace_name=fs.tablespace_name order by 1;

 

-----------------------------------------------------

5、查表空间对应的数据文件途径

select tablespace_name, file_id, file_name, round(bytes/(1024*1024),0) total_space from dba_data_files order by 3,1;

 

select tablespace_name, file_id, file_name, round(bytes/(1024*1024),0) total_space from dba_data_files where tablespace_name=UMS_LOG2 order by 3,1;

 

-----------------------------------------------------

6、查暂时表空间对应的数据文件途径

select tablespace_name, file_id, file_name, round(bytes/(1024*1024),0) total_space from dba_temp_files order by tablespace_name,file_name;

 

-----------------------------------------------------

7、检查表巨细

select owner,segment_name,segment_type,bytes/1024/1024 from dba_segments where segment_name in (MSG_SYSTEM_BAK,TI_CTI_CDR_YYYYMM);\

 

select owner,segment_name,segment_type,bytes/1024/1024 from dba_segments where segment_name=MSG_SYSTEM_BAK;

 

-----------------------------------------------------

8、扩大原表空间的巨细

ALTER DATABASE DATAFILE /oradata/orc11/system01.dbf RESIZE 4G;

 

-----------------------------------------------------

9、添加一个新的表空间文件

ALTER TABLESPACE UMS_LOG1 ADD DATAFILE +ORADG/yxdb/datafile/ums_log1_02 SIZE 10240M;

 

-----------------------------------------------------

10、添加一个新的暂时表空间文件

ALTER  TABLESPACE UMAPPM_TEMP1 ADD TEMPFILE +ORADG/yxdb/datafile/umappm_temp02 SIZE 6144M;

 

-----------------------------------------------------

11、封闭表空间的主动增加

ALTER DATABASE DATAFILE /usr/local/oracle/app/oracle/oradata/ora10/sysaux01.dbf AUTOEXTEND ON/OFF;

 

-----------------------------------------------------

12、检查数据库归档状况

archive log list;

 

-----------------------------------------------------

13、检查用户权限状况

select * from dba_sys_privs where GRANTEE=BACKUP;

 

select * from dba_role_privs where GRANTEE=UMAPTEST;

 

-----------------------------------------------------

14、创立暂时表空间

create temporary tablespace msec_temp tempfile +ORADG/yxdb/datafile/msec_temp size 2048m;

 

-----------------------------------------------------

15、创立索引表空间

create tablespace msec_index datafile +ORADG/yxdb/datafile/msec_index01 size 5120m;

 

-----------------------------------------------------

16、创立日志表空间

create tablespace msec_log datafile +ORADG/yxdb/datafile/ums2_log01 size 5120m;

 

-----------------------------------------------------

17、创立表空间msec

CREATE TABLESPACE msec

LOGGING

DATAFILE +ORADG/yxdb/datafile/msec_data01 size 5120M

AUTOEXTEND OFF

EXTENT MANAGEMENT LOCAL;

 

-----------------------------------------------------

18、创立用户msec

CREATE USER msec

IDENTIFIED BY phMyfPobbD

DEFAULT TABLESPACE msec

TEMPORARY TABLESPACE msec_temp

ACCOUNT UNLOCK;

 

19、授权ums2

GRANT CONNECT TO msec;

GRANT RESOURCE TO msec;

GRANT CREATE VIEW TO msec;

GRANT CREATE TABLE TO msec;

GRANT "EXP_FULL_DATABASE" TO backup ;

GRANT "IMP_FULL_DATABASE" TO moma;

 

 

 

-----------------------------------------------------

20、oracle 10g 整理归档日志

 

20.1、RMAN数据衔接

rman target system/manager@target_tnsname

or

rman target /

 

20.2、检查归档日志文件的状况:

RMAN list archivelog all;

 

20.3、 删去操作体系中的日志文件(到操作体系级进行手艺删去);

 

20.4、 将归档日志信息进行更新;

RMAN crosscheck archivelog all;

RMAN delete noprompt expired archivelog all;

RMAN exit

 

 

-----------------------------------------------------------------------------------------------------------

 

21、依据Linux的指令行环境下创立Oracle10gR2数据库

 

环境:RHEL 5.3+Oracle10g Release 2

 

1.设置体系环境变量:

ORACLE_HOME=/usr/local/oracle/app

ORACLE_BASE=/usr/local/oracle

 

2.体系规划:

实例称号:ORACLE_SID=typtdb

数据库称号:typtdb

 

3.手艺创立如下目录:

mkdir -p /usr/local/oracle/admin/typtdb/cdump

mkdir -p /usr/local/oracle/admin/typtdb/bdump

mkdir -p /usr/local/oracle/admin/typtdb/udump

mkdir -p /usr/local/oracle/admin/typtdb/pfile

 

4.树立暗码文件:

/usr/local/oracle/app/bin/orapwd  file=/usr/local/oracle/app/dbs/orapwdtyptdb password=typtdb

 

5.修正参数文件:

格局:/usr/local/oracle/app/dbs/init.ora

实例:/usr/local/oracle/app/dbs/inittyptdb.ora

内容如下:

large_pool_size=4294967296

shared_pool_size=4294967296

core_dump_dest=/usr/local/oracle/admin/typtdb/cdump

db_block_size=8192

db_file_multiblock_read_count=16

db_name=typtdb

db_recovery_file_dest=/usr/local/oracle/flash_recovery_area

db_recovery_file_dest_size=2147483648

dispatchers=(PROTOCOL=TCP) (SERVICE=typtdbXDB)

job_queue_processes=1000

open_cursors=300

pga_aggregate_target=8589934592

processes=3000

undo_management=auto

undo_tablespace=undotbs1

user_dump_dest=/usr/local/oracle/admin/typtdb/udump

NLS_DATE_FORMAT="Mon DD YYYY HH24:MI:SS"

NLS_DATE_LANGUAGE=AMERICAN

control_files = (/usr/local/oracle/oradata/typtdb/ora_control1.ctl, /usr/local/oracle/oradata/typtdb/ora_control2.ctl,/usr/local/oracle/oradata/typtdb/ora_control3ctl)

------------------------------------------------------------------------------------------------------

或则运用如下内容

 

*.audit_file_dest=/usr/local/oracle/admin/typtdb/adump

*.background_dump_dest=/usr/local/oracle/admin/typtdb/bdump

*.compatible=10.2.0.3.0

*.control_files=/usr/local/oracle/oradata/typtdb/control01.ctl,/usr/local/oracle/oradata/typtdb/control02.ctl,/usr/local/oracle/oradata/typtdb/control03.ctl

*.core_dump_dest=/usr/local/oracle/admin/typtdb/cdump

*.db_block_size=8192

*.db_domain=

*.db_file_multiblock_read_count=16

*.db_name=typtdb

*.dispatchers=(PROTOCOL=TCP) (SERVICE=typtdbXDB)

*.job_queue_processes=10

*.log_archive_dest=/usr/local/oracle/oradata/typtdb/archlog

*.nls_language=AMERICAN

*.open_cursors=5000

#PGA设置为(total_mem * 80%) * 20%

*.pga_aggregate_target=2748779070

*.processes=1000

*.remote_login_passwordfile=EXCLUSIVE

#SGA设置为(total_mem * 80%) * 80%

*.sga_max_size=10995116278

*.sga_target=10995116278

*.undo_management=AUTO

*.undo_tablespace=UNDOTBS1

*.user_dump_dest=/usr/local/oracle/admin/typtdb/udump

 

-----------------------------------------------------------------------------------------------------------------------

6.设置当时工作实例:

export ORACLE_SID=typtdb

 

7.登陆oracle:

sqlplus  sys/typtdb as sysdba

 

8.发动实例:

SQL startup nomount pfile="/usr/local/oracle/app/dbs/inittyptdb.ora"

 

9.创立数据库的脚本:

--创立数据库

create database typtdb

logfile group 1 /usr/local/oracle/oradata/typtdb/redo01.log size 128m reuse,

group 2 /usr/local/oracle/oradata/typtdb/redo02.log size 128m reuse,

group 3 /usr/local/oracle/oradata/typtdb/redo03.log size 128m reuse

datafile /usr/local/oracle/oradata/typtdb/typtdb01.dbf size 5120m reuse

maxdatafiles 100

archivelog

character set  ZHS16GBK

sysaux datafile /usr/local/oracle/oradata/typtdb/sys01.dbf size 512m 

undo tablespace undotbs1 datafile /usr/local/oracle/oradata/typtdb/undo01.dbf size 1024m;

--创立暂时表空间

create temporary tablespace temp tempfile /usr/local/oracle/oradata/typtdb/temp01.dbf size 2048m;

--创立回退段表空间

create tablespace rbs datafile /usr/local/oracle/oradata/typtdb/rbs01.dbf size 512m;

--创立东西表空间

create tablespace tools datafile /usr/local/oracle/oradata/typtdb/tools01.dbf size 512m;

--创立索引表空间

create tablespace indx datafile /usr/local/oracle/oradata/typtdb/indx01.dbf size 512m;

--回退段设置

create public rollback segment rbs0 tablespace rbs storage (optimal 4096k) ;

create public rollback segment rbs1 tablespace rbs storage (optimal 4096k) ;

create public rollback segment rbs2 tablespace rbs storage (optimal 4096k) ;

create public rollback segment rbs3 tablespace rbs storage (optimal 4096k) ;

--发动回退段

alter rollback segment "rbs0" online ;

alter rollback segment "rbs1" online ;

alter rollback segment "rbs2" online ;

alter rollback segment "rbs3" online ;

--创立用户表空间

create tablespace userdata datafile /usr/local/oracle/oradata/typtdb/userdata01.dbf size 1024m ;

--创立用户

create user orauser identified by orauser default tablespace userdata temporary tablespace temp;

--给用户授权

grant connect,resource to orauser;

 

10.运转如下文件:

 

(1). 运转sql创立体系表/视图和进程:

--创立体系常用的数据字典视图和近义词

@?/rdbms/admin/catalog.sql

--运转效劳器端所需求的PL/SQL脚本

@?/rdbms/admin/catproc.sql

connect system/manager

--创立PRODUCT_USER_PROFILE表,用于制止某些用户运用SQLPLUS拜访数据库

@?/sqlplus/admin/pupbld.sql

 

(2). 切换到archivelog形式

shutdown immediate

startup mount

alter database archivelog;

archive log start

alter database open;

 

(3).从pfile创立spfile

sqlplus /as sysdba

create spfile from pfile;

 

11.经验总结:

①inittyptdb.ora中的control_files关键字所指示的目录必需存在。

②inittyptdb.ora中undo_tablespace中的姓名必需与Create Database中的相同。

③剖析数据库犯错能够到/usr/local/oracle/admin/typtdb/bdump目录下的alert_typtdb.log中查找。

-----------------------------------------------------------------------------------------------------------

22、oracle修正游标巨细

 

这个问题最好是经过优化sql来处理,考试,大提示也能够经过修正ORACLE的参数open_cursors 来处理。

(1)oraclehome\admin\数据库姓名\pfile 下面的init.ora里边有参数

#游标和库高速缓存#

open_cursors=300

(2)Oracle9i以上版别可经过

SQL conn sys as sysdba

SQL show parameter open_cursors;

SQL alter system set open_cursors=4000 scope=both;

(3)重启体系使它收效。

 

-----------------------------------------------------------------------------------------------------------

23、ORACLE 中修正用户暗码的办法

用OS认证方法登陆,便是CONNECT / AS SYSDBA 登陆,然后修正暗码就是了

ALTER USER backup IDENTIFIED BY "bcinfobackup";

ALTER USER SYSTEM IDENTIFIED BY ****

然后你能够改动成暗码文件认证。

 

 

 

-----------------------------------------------------------------------------------------------------------

24、主动整理归档日志

#!/bin/bash

#################FUNCTION#############

#

# autoclear Oracle 11g archive log

#

######################################

 

# Make user of root

 

#filename:~/clearlog.sh

 

export ORACLE_BASE=/usr/local/oracle

export ORACLE_SID=moma

export ORACLE_HOME=$ORACLE_BASE/app

export ORA_NLS33=$ORACLE_HOME/ocommon/nls/admin/data

export LD_LIBRARY_PATH=$ORACLE_HOME/lib

#export DISPLAY=10.25.110.111:0.0

export NLS_LANG=american_america.zhs16gbk

#export ORA_CRS_HOME=$ORACLE_BASE/crs

export PATH=$ORACLE_HOME/bin:$ORACLE_BASE/crs/bin:/usr/ccs/bin:$PATH:$HOME/bin:/usr/bin:.

 

# autoclear the *.dbf file in /usr/local/oracle/oradata/moma/archlog/ 31 days ago

find /usr/local/oracle/oradata/moma/archlog/ -xdev -mtime +30 -name "*.dbf" |xargs rm -rf

 

# connect database as rman

rman target / EOF

crosscheck archivelog all;

delete noprompt expired archivelog all;

exit

 

EOF

-----------------------------------------------------------------------------------------------------------

25、数据库备份战略

#!/bin/bash

#####################

#2009-12-09

#Liao

#####################

 

#####################

#Oracle Envirment

#####################

export ORACLE_BASE=/usr/local/oracle

export ORACLE_HOME=$ORACLE_BASE/product/11.1.0

export ORACLE_SID=qfman

export ORACLE_TERM=xterm

export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib:/usr/local/lib

export NLS_LANG=American_america.zhs16gbk

export CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib

export PATH=$ORACLE_HOME/bin:$PATH

export DISPLAY=:0.0

 

#####################

#Backup Script

#####################

EXPFILE=`date +%F_%H_%M`

BAKPATH=/usr/local/oracle/backup

exp backup/bcinfobackup@gsdm10 file=$BAKPATH/ora.$EXPFILE compress=y buffer=16777216 full=y log=$BAKPATH/log/exp.$EXPFILE.log

gzip $BAKPATH/ora*_30

find $BAKPATH/ -xdev -mtime +4 -name "*.gz" $BAKPATH/log/exp.$EXPFILE.log

find $BAKPATH/ -xdev -mtime +4 -name "*.gz" |xargs rm -rf

find $BAKPATH/log/ -xdev -mtime +4 -name "*.log" |xargs rm -rf

 

-----------------------------------------------------------------------------------------------------------

26、查询当时履行IO最多的sql,并按大到小来排序

select cast(buffer_gets/decode(EXECUTions,0,10000,EXECUTions) as int) 均匀IO ,

EXECUTions 履行次数,buffer_gets 总逻辑IO,disk_reads 硬盘读取, sql_text SQl句子

from v$sqlarea

where parsing_schema_name=MOMA

and rownum =10

order by buffer_gets desc ;

 

-----------------------------------------------------------------------------------------------------------

27、hp unix 发动监听指令

srvctl start listener -n db1

 

-----------------------------------------------------------------------------------------------------------

28、封闭Oracle快闪功用

YP shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

 

YP startup mount;

ORACLE instance started.

 

Total System Global Area  314572800 bytes

Fixed Size                  1301704 bytes

Variable Size             261890872 bytes

Database Buffers           50331648 bytes

Redo Buffers                1048576 bytes

Database mounted.

 

YP alter database flashback off;

 

Database altered.

 

YP alter database open;

 

Database altered.

 

YP alter system set log_archive_dest_1=LOCATION=/usr/local/oracle/archivelog1 scope=both;

 

Database altered.

-----------------------------------------------------------------------------------------------------------

29、修正快闪区巨细

alter system set db_recovery_file_dest_size=50G scope=both;

 

-----------------------------------------------------------------------------------------------------------

30、oracle11g 检查日志指令

adrci

 

-----------------------------------------------------------------------------------------------------------

31、Oracle修正日志组文件的巨细

1、创立2个新的日志组

alter database add logfile group 4 (/oradata/qfman/redo04.log) size 1G;

alter database add logfile group 5 (/oradata/qfman/redo05.log) size 1G;

 

2、切换当时日志到新的日志组

alter system switch logfile;

或直接切换归档

alter system archive log current;

 

3、删去旧的日志组

alter database drop logfile group 1;

alter database drop logfile group 2;

alter database drop logfile group 3;

 

4、操作体系下删去原日志组1、2、3中的文件

rm -rf /oradata/qfman/redo01.log

rm -rf /oradata/qfman/redo02.log

rm -rf /oradata/qfman/redo03.log       

 

5、重建日志组1、2、3

alter database add logfile group 1 (/oradata/qfman/redo01.log) size 1G;

alter database add logfile group 2 (/oradata/qfman/redo02.log) size 1G;

alter database add logfile group 3 (/oradata/qfman/redo03.log) size 1G;

 

6、切换日志组

alter system switch logfile;

 

7、删去中心过渡用的日志组4、5

alter database drop logfile group 4;

alter database drop logfile group 5;

 

8、到操作体系下删去原日志组4、5中的文件

 

-----------------------------------------------------------------------------------------------------------

32、用于查询出占用资源较高的进程的HASH值

while [ 1 ]

do

echo "SESSION EVENT WAIT LIST "

sqlplus -S /as sysdba  EOF

set pagesize 2000

set linesize 800

col event format a29

col program format a29

col sess_sql_hash for a15

col username for a11

col client for a31

select substr(a.event,1,25) event,substr(b.program,1,39) program ,b.sid||:||decode(sql_hash_value,0,prev_hash_value,sql_hash_value) sess_sql_hash,b.username

,substr(b.osuser||@||b.machine||@||b.process||@||c.spid,1,31) client,to_char(b.logon_time,mm-dd hh24:mi) logon_time

from v\$session_wait a,v\$session b,v\$process c

where a.sid=b.sid  and b.paddr=c.addr

and a.event not like %SQL%

and a.event not like %message%

and a.event not like %time%

and a.event not like PX Deq:%

and a.event not like jobq slave%

/

exit

EOF

sleep 5

done

 

-----------------------------------------------------------------------------------------------------------

33、用于依据HASH值查询相应的SQL句子

sqlplus -S /as sysdba EOF

variable v_hash_value Number;

BEGIN

:v_hash_value :=$1;

END;

/

set pages 0

set lines 1000

set feedback off

set term on

select to_char(sysdate,yyyymmdd hh24:mi:ss) as curtime,hash_value,sql_text from v\$sqltext where hashvalue=:v_hash_value order by piece;

/

exit

EOF

 

---------------------------------------------------------------------------------------------------------------------------------34、检查数据库是否存在死锁

select /*+ NO_MERGE(a) NO_MERGE(b) NO_MERGE(c) */ Wait "Status", a.username, a.machine, a.sid, a.serial#, a.last_call_et "Seconds", b.id1, c.sql_text "SQL"

from v$session a, v$lock b, v$sqltext c

where a.username is not null

and a.lockwait = b.kaddr

and c.hash_value =a.sql_hash_value

union

select /*+ NO_MERGE(a) NO_MERGE(b) NO_MERGE(c) */ Lock "Status", a.username, a.machine, a.sid, a.serial#, a.last_call_et "Seconds", b.id1, c.sql_text "SQL"

from v$session a, v$lock b, v$sqltext c

where b.id1 in

(select /*+ NO_MERGE(d) NO_MERGE(e) */ distinct e.id1

from v$session d, v$lock e

where d.lockwait = e.kaddr)

and a.username is not null

and a.sid = b.sid

and b.request=0

and c.hash_value =a.sql_hash_value;

 

 

 

select t2.username||   ||t2.sid||   ||t2.serial#||   ||t2.logon_time||   ||t3.sql_text

      from v$locked_object t1,v$session t2,v$sqltext t3

      where t1.session_id=t2.sid

      and t2.sql_address=t3.address

      order by t2.logon_time;

 

 

SELECT /*+ rule */ s.username,

decode(l.type,TM,TABLE LOCK,

TX,ROW LOCK,

NULL) LOCK_LEVEL,

o.owner,o.object_name,o.object_type,

s.sid,s.serial#,s.terminal,s.machine,s.program,s.osuser

FROM v$session s,v$lock l,dba_objects o

WHERE l.sid = s.sid

AND l.id1 = o.object_id(+)

AND s.username is NOT NULL;

 

 

select distinct s.SID,s.SERIAL#,a.owner,a.object_name,q.SQL_TEXT,s.paddr,p.SPID

        from v$locked_object l,all_objects a,v$session s,v$sql q,v$process p

        where l.OBJECT_ID=a.object_id

              and s.SID=l.SESSION_ID

              and q.ADDRESS=s.PREV_SQL_ADDR

              and s.PADDR=p.ADDR

 

 

----------------------------------------------------------------------------------------------------

35、silence形式装置oracle 10g进程

 

1.Swap partition created

dd if=/dev/zero of=/swapfile bs=1M count=2048;

mkswap /swapfile

swapon /swapfile

vim /etc/fstab

/swapfile swap default 0 0

 

2.groupadd oninstall

groupadd dba

useradd -g oinstall -G dba oracle -d /home/oracle oracle

passwd oracle

mkdir -p /u01/app/oracle/product/10.2.0/db1

chown -R oracle.oinstall /u01/app/

 

3.vim /etc/hosts

 

4.vim /etc/sysctl.conf

kernel.shmmax = 1000000000(#0.5*nG*1024*1024*1024)

kernel.shmmni = 4096

kernel.shmall = 2097152

kernel.sem = 250 32000 100 128

net.ipv4.ip_local_port_range = 1024 65000

net.core.rmem_default=1048576

net.core.rmem_max=1048576

net.core.wmem_default=262144

net.core.wmem_max=262144

fs.file-max = 65536

sysctl -p

 

5.vim /home/oracle/.bash_profile

export TMP=/tmp

export TMPDIR=$TMP

export ORACLE_BASE=/u01/app/oracle

export ORACLE_HOME=$ORACLE_BASE/product/10.2.0/db1

export ORACLE_SID=db1

export ORACLE_TERM=xterm

export PATH=$PATH:$ORACLE_HOME/bin

export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/usr/lib

export CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib

export DISPLAY="127.0.0.1:0.0"

export LC_CTYPE=en_US.UTF-8

 

6.apt-get install make gcc g libc6-dev gnome xorg glibc-common gnome-libs libstdc libstdc -devel

 

7.vim /etc/redhat-release

Red Hat Enterprise Linux AS release 4 (Taroon)

 

8.apt-get install unzip

unzip -x 10201_database_linux-32

mv database /u01/app/

chown -R oracle oinstall /u01/app/database

cp /u01/app/database/response/enterprise.rsp /u01/app/db1.rsp

vim /u01/app/db1.rsp

n_configurationOption=3

UNIX_GROUP_NAME="oinstall"

ORACLE_HOME="/opt/u01/app/oracle/product/10g"

ORACLE_HOME_NAME="OraDbHome1"

ORACLE_HOSTNAME="$hostname"

s_nameForDBAGrp="dba"

s_nameForOPERGrp="dba"

 

9.cd /u01app/database

ls

./runInstaller -silent -responsefile /u01/app/db1.rsp -ignoreSysPrereqs

 

10.cd /u01/app/oracle/admin/db1

touch adump bdump cdump dpdump udump

 

11.vim /u01/app/oracle/db1.sql

CREATE DATABASE db1

USER SYS IDENTIFIED BY sys

USER SYSTEM IDENTIFIED BY sys

LOGFILE GROUP 1 (/u01/app/oracle/oradata/db1/redo01.log) SIZE 50M,

GROUP 2 (/u01/app/oracle/oradata/db1/redo02.log) SIZE 50M,

GROUP 3 (/u01/app/oracle/oradata/db1/redo03.log) SIZE 50M

MAXLOGFILES 5

MAXLOGMEMBERS 5

MAXLOGHISTORY 1

MAXDATAFILES 100

MAXINSTANCES 1

CHARACTER SET AL32UTF8

NATIONAL CHARACTER SET AL16UTF16

DATAFILE /u01/app/oracle/oradata/db1/system01.dbf SIZE 325M REUSE

EXTENT MANAGEMENT LOCAL

SYSAUX DATAFILE /u01/app/oracle/oradata/db1/sysaux01.dbf SIZE 325M REUSE

DEFAULT TABLESPACE usertbs DATAFILE /u01/app/oracle/oradata/db1/usertbs.dbf SIZE 10M REUSE

DEFAULT TEMPORARY TABLESPACE tempts1

TEMPFILE /u01/app/oracle/oradata/db1/temp01.dbf

SIZE 20M REUSE

UNDO TABLESPACE undotbs

DATAFILE /u01/app/oracle/oradata/db1/undotbs01.dbf

SIZE 20M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;

 

12.vim $ORACLE_HOME/dbs/initdb1.ora

db_name=db1

shared_pool_size=120000000

undo_tablespace=undotbs

undo_management=AUTO

 

13. $ORACLE_HOME/bin/orapwd file=$ORACLE_HOME/dbs/orapwdb1 password=123

 

14.sqlplus / as sysdba

@/u01/app/oracle/db1.sql

@$ORACLE_HOME/rdbms/admin/catalog.sql

@$ORACLE_HOME/rdbms/admin/catproc.sql

conn system/manage

@$ORACLE_HOME/sqlplus/admin/pupbld.sql

 

---------------------------------------------------------------------------------------------------------------------------

36、运用RAC的Oracle软件装置数据库时,呈现如下状况时

ORA-29702: error occurred in Cluster Group Service operation

需封闭RAC的动态链接

 

封闭

cd $ORACLE_HOME/rdbms/lib

make -f ins_rdbms.mk rac_off

make -f ins_rdbms.mk ioracle

 

翻开

cd $ORACLE_HOME/rdbms/lib

make -f ins_rdbms.mk rac_on

make -f ins_rdbms.mk ioracle

------------------------------------------------------

37、impdp/expdp运用实例

 

一、创立逻辑目录,该指令不会在操作体系创立真实的目录,最好以system等办理员创立。

create directory dpdata1 as d:\test\dump;

 

二、检查办理理员目录(一起检查操作体系是否存在,由于Oracle并不关怀该目录是否存在,假如不存在,则犯错)

select * from dba_directories;

 

三、给scott用户赋予在指定目录的操作权限,最好以system等办理员赋予。

grant read,write on directory dpdata1 to scott;

 

四、导出数据

1)按用户导

expdp scott/tiger@orcl schemas=scott dumpfile=expdp.dmp DIRECTORY=dpdata1;

2)并行进程parallel

expdp scott/tiger@orcl directory=dpdata1 dumpfile=scott3.dmp parallel=40 job_name=scott3

3)按表名导

expdp scott/tiger@orcl TABLES=emp,dept dumpfile=expdp.dmp DIRECTORY=dpdata1;

4)按查询条件导

expdp scott/tiger@orcl directory=dpdata1 dumpfile=expdp.dmp Tables=emp query=WHERE deptno=20;

5)按表空间导

expdp system/manager DIRECTORY=dpdata1 DUMPFILE=tablespace.dmp TABLESPACES=temp,example;

6)导整个数据库

expdp system/manager DIRECTORY=dpdata1 DUMPFILE=full.dmp FULL=y;

 

五、复原数据

1)导到指定用户下

impdp scott/tiger DIRECTORY=dpdata1 DUMPFILE=expdp.dmp SCHEMAS=scott;

2)改动表的owner

impdp system/manager DIRECTORY=dpdata1 DUMPFILE=expdp.dmp TABLES=scott.dept REMAP_SCHEMA=scott:system;

3)导入表空间

impdp system/manager DIRECTORY=dpdata1 DUMPFILE=tablespace.dmp TABLESPACES=example;

4)导入数据库

impdb system/manager DIRECTORY=dump_dir DUMPFILE=full.dmp FULL=y;

5)追加数据

impdp system/manager DIRECTORY=dpdata1 DUMPFILE=expdp.dmp SCHEMAS=system TABLE_EXISTS_ACTION=append;

 

假如导出和导入的用户名和表空间名不一样,则运用如下指令

impdp noep/xbwesrU3tN directory=dump_dir dumpfile=noep_test.dmp logfile=impdp_noep.log remap_schema=noep_test:noep remap_tablespace=noep_test:noep_data

 

impdp的remap运用说明

总结:impdp导入到方针数据库中,需求保证方针数据库中存在源数据库中的用户和用户所属表空间都存在,不然需求运用remap参数。

 

如:

情形一:

源数据库:create user test identified by test default tablespace ts_test;

方针数据库:create user test identified by test default tablespace ts_test1;(表空间不同)

这样有必要加remap_tablespace=ts_test:ts_test1

 

情形二:

源数据库:create user test identified by test default tablespace ts_test;

方针数据库:create user test2 identified by test default tablespace ts_test1;(用户和表空间都不同)

这样有必要加remap_schema=test:test2 remap_tablespace=ts_test:ts_test1

 

 

------------------------------------------------------------------------------------------------------------

38、修正RAC数据库的归档的默许途径:

SQL select inst_id,instance_name,version,archiver,status from gv$instance;

 

   INST_ID INSTANCE_NAME    VERSION           ARCHIVE STATUS

---------- ---------------- ----------------- ------- ------------

         1 pushdb1          10.2.0.4.0        STARTED OPEN

         2 pushdb2          10.2.0.4.0        STARTED OPEN

 

SQL alter system set log_archive_dest_1=location=/oracle/product/archivelog1 sid=pushdb1;

System altered.

 

SQL alter system set log_archive_dest_1=location=/oracle/product/archivelog2 sid=pushdb2;

System altered.

 

SQL show parameter log_archive

 

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

log_archive_config                   string

log_archive_dest                     string

log_archive_dest_1                   string      location=/oracle/product/archivelog1

log_archive_dest_10                  string

... ....

 

 

39、CRS及相关效劳的常用指令的演示

检查状况的指令

# su – oracle --先切换到oracle用户

$ crs_stat -t --检查crs及一切的service的状况

$ crsctl check crs --用crsctl指令,检查crs相关效劳的状况

$ srvctl status nodeapps -n db01 (db02) --运用srvctl检查一切节点上的效劳是否正常

$ srvctl status database -d db --运用srvctl检查数据库状况

 

发动和封闭相关效劳的指令

# su – oracle

$ crs_start –all --发动一切的crs效劳

$ crs_stop –all --中止一切的crs效劳

$ crsctl start crs --发动crs效劳

$ crsctl stop crs --中止crs效劳

$ srvctl start nodeapps -n db01(db02) --发动某节点上一切的使用

$ srvctl stop nodeapps -n db01(db02) --中止某节点上一切的使用

$ srvctl start listener -n db01(db02) --发动某个节点的listener

$ srvctl stop listener -n db01(db02) --中止某个节点的listener

$ srvctl start instance –d db –i db01(db02) --发动某个instance

$ srvctl stop instance –d db –i db01(db02) --中止某个instance

$ srvctl start database –d db --发动数据库

$ srvctl stop database –d db --中止数据库

 

------------------------------------------------------------------------------------------

40、Oracle RAC停开机器次序规范做法

开机次序:

 

srvctl start nodeapps -n node1 hostname

srvctl start nodeapps -n node2 hostname

srvctl start asm -n node1 hostname

srvctl start asm -n node2 hostname

srvctl start database -d database name

srvctl start service -d database name -s service name

 

封闭次序:

 

srvctl stop service -d database name -s service name

srvctl stop database -d database name

srvctl stop asm -n node1 hostname

srvctl stop asm -n node2 hostname

srvctl stop nodeapps -n node1 hostname

srvctl stop nodeapps -n node2 hostname

 

假如某些效劳没有发动

 

手艺SHUTDOWN 每个实例

然后CRS_STOP -ALL,封闭其他效劳。

发动的时分能够经过CRS_START -ALL发动

版权声明
本文来源于网络,版权归原作者所有,其内容与观点不代表牛牛娱乐立场。转载文章仅为传播更有价值的信息,如采编人员采编有误或者版权原因,请与我们联系,我们核实后立即修改或删除。

猜您喜欢的文章