oracle数据库sql基本操作
~~~~~~~~~~~~~~~~~~~~~~
一.DML(insert into update delete)操纵
~~~~~~~~~~~~~~~~~~~~~~
insert into
1.insert into user.table1@orcl(name,age) values('zhangsan',23);
2.insert into table1(name,age) values('zhangsan',23);
3.insert into table1('zhangsan',23);
4.insert into table1(name,age) select name,age from table2;
update
1.update table1 set age=age+1 where name='zhangsan';
2.update table1 set age= (select avg(age) from table1 where sex='M') where class='A1';
delete
1.delete from table1 where class='A1';
truncate
1.truncate table table1;
~~~~~~~~~~~~~~~~~~~~~~
二.DCL(grant revoke commit rollback)控制
~~~~~~~~~~~~~~~~~~~~~~
grant
1. grant select on table table1 to user;
2. grant all privileges on table table1,table2 to user1,user2;
3. grant select on table table1 to public;
4. grant select,update(age) on table table1 to user;
5. grant insert on table table1 to user with grant option;
6. grant createtab on database orcl to user;
revoke
1. revoke update(age) on table table1 from user;
2. revoke select on table table1 from public;
3. revoke insert on table table1 from user;
~~~~~~~~~~~~~~~~~~~~~~
常用函数:
~~~~~~~~~~~~~~~~~~~~~~
1.ASCII('字符串')----返回字符串的第一个字符的ASC码
select ASCII('A') name from dual;-----返回65
2.CHR(i)----i是一个数字,返回数据对应的ASC码字符
select CHR(97) name from dual;------返回a
3.concat(c1,c2)-----连接C1和2,与||等价.
4.initcap('字符串')-----返回字符串的第一个字符大写
5.instr(c1,c2,i,j)----返回c2在c1中从i开始,j次出现的位置
6.length('字符串')---返回字符串的长度
7.lower(c1)----返回c1的小写字符
8.ltrim(c1,c2)-----c1在c2中最左边的字符串去掉,使c1第一个字符不在c2中。
9.replace(c1,c2,c3)----c3去替换c2在c1中的内容。
10.substr(c1,i,j)---c1第i个位置开始返回长度为j的字符串
~~~~~~~~~~~~~~~~~~~~~~
P/L SQL数据类型：
~~~~~~~~~~~~~~~~~~~~~~
数字型：number/pls_integer/binary_integer
字符型：varcher2/char/long
32767/32767/32760
对比 数据库类型：varcher2/char/long
4000/2000/2BG
布尔型：true/false/null
---------------------------------------------
oracle中允许用户定义两种类型：record(记录类型)、table(表类型)
~~~~~~~~~~~~~~~~~~~~~~
type定义的数据类型：
~~~~~~~~~~~~~~~~~~~~~~
type A is record
(
id number(10)not null:=0,
name varchar2(20)
);
定义一个type类型的变量：
a1 A;
~~~~~~~~~~~~~~~~~~~~~~
两个特殊的变量：
~~~~~~~~~~~~~~~~~~~~~~
%TYPE %ROWTYPE
type A is record
(
id table1.id%TYPE not null:=0,
name table1.name%TYPE
);
A table1%ROWTYPE;
~~~~~~~~~~~
常量
~~~~~~~~~~~
a constant integer:=8;
~~~~~~~~~~~
变量
~~~~~~~~~~~
a integer:=8;
~~~~~~~~~~~
选择结构：
~~~~~~~~~~~
if 条件 then
{语句；}
elsif 条件 then
{语句；}
else
{语句；}
end if;
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
包、函数、过程、游标
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
创建一个包的说明部分：
create package bao
is
man_id number;---全局变量
CURSOR C;
CREATE FUNCTION f(age in table1.age%TYPE) RETURN NUMBER;
CREATE PROCEDURE P(age in table1.age%TYPE, id out number);
end bao;
创建一个包的包体部分：
create package bao
as
CURSOR C is
select age,name,id from where id<11;
FUNCTION f(age in table1.age%TYPE)
return number
as
out_num number;
BEGIN
if in_sex='M' then
select count(sex) into out_num from tables1 where sex='m';
else
select count(sex) into out_num from tables1 where sex='f';
end if;
return (out_num);
end f;
PROCEDURE P(age in table1.age%TYPE, id out number)
AS
BEGIN
if in_sex='M' then
select count(sex) into out_num from tables1 where sex='m';
else
select count(sex) into out_num from tables1 where sex='f';
end if;
END P;
END BAO;S
~~~~~~~~~~~~~~~~
oracle安装与卸载
~~~~~~~~~~~~~~~~
组件目录：
product/10.2.0/db_1/inventory/ContentsXML
操作系统版本文件：
product/10.2.0/db_1/inventory/Templates/oui/oraparam.ini
遇到.so文件错误时
1.判断包安装问题2.检查日志文件
安装时日志文件：
E:/oracle/product/10.2.0/db_1/cfgtoollogs/oui/installActions2010-05-22_01-53-21PM.log
libaoio.so.1错误
1，更新库缓存：ldconfig
2.libao包未安装
说明：共享函数库路径信息放在：/etc/ld.so.conf里 ldconfig===更新====ld.so.cache
卸载oracle数据库（包括数据库与相关的软件）
[windows篇]
1.DBCA删除数据库－停止所有服务－OUI删组件－universal install-手工删除注册表－搜inst_loc的值/ora/oracle/orcl.enumora
-删除环境变量－oracle_home/oralce_sid/tns_admin/jserv-重启删除目录－删inst_loc值的目录－删除C:/pro../oracle目录
－删除环境变量指向目录－删控制、日志等文件－启动菜单中的oracle快捷方式
[linux篇]
标识系统中所有实例与oracle主目录
/etc/oratab
/var/opt/oracle/oratab(solaris)
一、删除一般实例
2.删除oracle数据库
如果shell,k,b,shell执行：/usr/local/bin/oraenv
如果c shell执行：%source /usr/local/bin/coraenv
提示输入SID
3.dbca删除数据库删除一般实例
二、删除ASM实例
2.删除oracle数据库
如果shell,k,b,shell执行：/usr/local/bin/oraenv
如果c shell执行：%source /usr/local/bin/coraenv
提示输入SID
3.登录ASM实例：sqlplus 'sys/wy as sysdba'
查哪些数据库在使用ASM实例：SELECT INSTANCE_NAME FROM V$ASM_CLIENT;
磁盘组查询：sql>select name from v$asm_diskgroup;
删磁盘组： select diskgroup dg_k including contents;
(磁盘组名称)
关闭ASM实例:SHUTDOWN
删/etc/oratab 中关于ASM信息
4.
删CSS服务：root用户－设置环境变量－
b shell #export ORACLE_HOME=/oracle/...
c shell #setenv ORACLE_HOME /oracle/...
进入$ORACLE_HOME/bin-执行./localconfig
5.
删除oracle软件
oracle用户－emctl stop dbconsole-lsnrctl stop-isqlplusctl stop-searchctl stop-启动oui-
-$ORACLE_HOME/oui/bin/runInstaller-卸载软件－删除oratab文件－删除数据文件/控制文件等－删除
$ORACLE_HOME目录和$ORACLE_BASE目录－重启操作系统
~~~~~~~~~~~~~~~~~~~~~
手动创建数据库
~~~~~~~~~~~~~~~~~~~~~
1.检查环境变量是否设置好
2.ORACLE_SID实例名环境变量中是否存在
3.创建口令文件：orapwd file=/oracle/app/orapwnbo password=wy entries=5
4.到参数文件目录创建参数文件：initnbo.ora ..... 注意里面的参数名称与数据库一致
5.sqlplus sys as sysdba---create spfile=/../../spfilenbo.ora from pfile=/../../initnbo.ora
6.sql>startup nomount
7.创建initnbo.ora中目录
8.创建create database语句中的目录
9.修改目录属性oracle.dba,权限775
10.sql>create datbase nbo ......（该语句执行后会自动进入mount状态）
11.创建额外的表空间（可选）包括users表空间
12.创建数据库字典与包：执行脚本：sql>@$ORACLE_HOME/rdbms/admin/catalog.sql 生成字典
sql>@$ORACLE_HOME/rdbms/admin/catproc.sql 生成p/LSQL需要的包、函数、过程
~~~~~~~~~~~~~~~~
手动删除数据库
~~~~~~~~~~~~~~~~
1.sql>connect sys as sysdba
2.sql>startup force mount;
3.sql>drop database; 此句会自动删除所有相关的数据文件和控制文件
4.手动删除归档日志与数据库备份
~~~~~~~~~~~~~~~~
oralce网络管理
~~~~~~~~~~~~~~~~
TNS_ADMIN环境变量决定tnsnames.ora/listener.ora
没有此变量默认地址为：$oracle_home/network/admin/
报告错误“TNS-12541:TNS:no listener”解决方法：
参数文件中定义local_listener=LISTENER_AUXI
监听指向LISTENER_AUXI
由于LISTENER_AUXI是在网络配置文件中的端口为1521与实际的监听配置的1522不同，
所以出现“无监听程序”错误
sqlnet.ora中SQLNET.AUTHENTICATION_SERVICES参数控制oracle认证方式
SQLNET.AUTHENTICATION_SERVICES＝(NTS)-----通过操作系统认证
NAMES.DEFAULT_DOMAIN=www.com.cn-----------定义了默认的域名
如果定义了这个域名，网络服务名＝tnsnames.ora中网络服务名+域名
服务器端限制客户端访问数据库：
1.防火墙中设置限制
2.sqlnet.ora中限制
tcp.validnode_checking=yes----------------------------客户端ip检查
tcp.invited_nodes=(192.168.200.88,192.168.200.191)----允许IP
tcp.exclded_nodes=(192.168.200.33) －－－－－－－－－拒绝IP
~~~~~~~~~~~~~~~~~~~~~~~~~~~
数据库启动与关闭
~~~~~~~~~~~~~~~~~~~~~~~~~~~
随系统启动、关闭数据库
[linux]篇
oracle_home/bin目录下两个脚本dbstart/dbshut会读取oratab文件，启动、关闭数据库。
/etc/oratab内容：数据库信息、实例名、oracle主目录等信息
nbo:/oracle/app/oracle/product/10.1.0/Db_1:N
其中Ｎ表示不允许自动启动实例，Ｙ表示允许自动启动实例
1.修改/etc/oratab文件
nbo:/oracle/app/oracle/product/10.1.0/Db_1:Y
2.验证手动执行：./dbstart 是否能启动数据库
3.env得到oracle主目录－
以root登录－进入/etc/init.d－创建脚本start_shut
#!/bin/sh
OPT_=$1
case "$OPT_" in
start)
/bin/echo "$0 :(start)"
su - oracle -c "/oracle/app/.../lsnrctl start"
su - oracle -c "/oracle/app/.../dbstart"
chmod 1777 /tmp
chown sys:sys /tmp
exit 0
;;
stop)
/bin/echo "$0 :(stop)"
su - oracle -c "/oracle/app/.../lsnrctl stop"
su - oracle -c "/oracle/app/.../dbshut"
exit 0
;;
*) /bin/echo ''
/bin/echo "Usage: $0 [start|stop]"
/bin/echo " Invalid argument ==> /"${OPT_}/""
/bin/echo ''
exit 0
;;
esac
-修改权限start_shut为775
－进入/etc/rc2.d/－创建连接ln -s /etc/init.d/start_shut S99start_shut(solaris)
ln -s /etc/rc.d/init.d/start_shut S99start_shut(其它linux)
－进入/etc/rc0.d-创建连接 ln -s /etc/init.d/start_shut k01start_shut(solaris)
ln -s /etc/rc.d/init.d/start_shut k01start_shut(其它linux)
4.重启系统init 6
数据库启动到restrict限制模式
一般做：1.执行数据导入、导出
2.执行数据的装载（sql*Loader）
3.阻止某些用户访问数据
4.执行某些升级和迁移操作
在restrict模式，只有CREATE SESSION和RESTRICTED SESSION权限用户可以访问数据库
如果是关闭的数据库执行：startup restrict
打开的数据库执行：alter system enable restricted session;
数据库启动到read only（只读模式）
startup open read only
数据库启动到recover恢复模式（需要介质恢复时）
startup open recover;
重启数据库
startup force ==shutdown abort+startup open
远程启动、关闭数据库的用户，必须使用专用数据库连接（Dedicatied Server）连接
在tnsnames.ora中加入server=dedicated
强行中止活动的会话：
select sid,serial# from v$session; 查出ＳＩＤ和serial#号
alter system kill session '8,232' 直接中止活动会话
~~~~~~~~~~~~~~~~~~~~~~~~~~
专有/共享服务器模式
~~~~~~~~~~~~~~~~~~~~~~~~~~
DBCA配置共享服务时，某些参数并不会生效，配置完成后，有时候DBCA会重启数据库，有时不会。
鉴于DBCA问题多，设置比较复杂。强烈推荐使用手工配置。
专用服务器模式适合：批处理和大任务的应用
共享服务器模式适合：大量并发用户的应用
专用服务器原理：
用户请求专用服务器连接－用户进程请求连接－服务器端监听－监听创建一个专有服务器进程－把用户进程连到服务器进程
简述：用户进程请求连接－监听－监听建服务进程－连接用户进程与服务进程
共享服务器原理：
数据库启动时－调度进程－将地址给－监听
用户进程请求连接时－监听查是否允许共享服务器进程－是－监听将负载最小调度进程地址－给用户进程－连接到调度进程
－否－使用专用服务器连接
手工配置共享服务器模式
1.sys登录
2.通过spfile生成pfile文件
3.修改pfile文件
shared_servers=3
max_shared_servers=39
dispatchers="(protocol=TCP)(serv=wm)(list=wm)(poo=on)(disp=5)(con=33)(sess=224)"
max_dispatchers=19
shared_servers:共享服务进程最小数量。繁忙系统设置高些，空闲系统设置低些。
一般系统：shared servers=连接数*1/10
max_shared_servers:为最大共享服务进程数量
dispatchers:调度进程 protocol：通迅协议 serv/list:网络服务名 poo:共享池启动
disp:调度进程最小数量 con:每个调度进程最大网络连接数 sess：每个调度进程最大会话数量
max_dispatchers：最大调度进程数量
4.关闭数据库
5.通过pfile生成spfile
6.重启数据库
显示共享服务器进程配置参数：
1.show parameter share
显示调度进程配置参数：
1.show parameter disp
显示共享服务进程情况：
1.select * from v$shared_server
显示调度进程情况：
1.select * from v$dispatcher
修改调度进程数量：
alter system set dispathers="(protocol=tcp)(disp=9)" scope=both
修改共享服务进程数量：
alter system set shared_servers=8 scope=both
关闭指定调度进程：
select * from dispatcher --查看
alter system shutdown immediate 'D004';
错误：ora-00104:deadlock detected;all public servers blocked waiting for resources
解决：需要增大max_shared_servers的值
把共享模式转成专用模式：
1.静态方式：
在参数文件中注释掉shared_servers.max_shared_servers.dispatchers.max_dispatchers
2.动态方式：
1.alter system set shared_servers=0 scope=both --请求共享服务器进程的连接会话退出系统（并不是马上停止）
1.alter system set max_shared_servers=0 scope=both --强行中止（非常危险）
判断客户端请求：是专用还是共享连接
通过查看v$session视图
select server from v$session where sid=(select distinct sid from v$mystat);
~~~~~~~~~~~~~~~~~~~~~
数据导出
~~~~~~~~~~~~~~~~~~~~~
把数据库中表导成文本文件
set termount off
set feedback off
set timing off
set echo off
set heading off
set pageSIZE 0
set lineSIZE 10000
set long 5000
set arraySIZE 5000
set trimout on
set trimspool on
spool /tmp/students
select * from students where rownum<10;
spool off
定时导出
1.脚本名为：expbbs_info
2.crontab -e
加入以下内容：
25 23 * * * /export/home/oracle/expbbs_info>>/export/home/oracle/expbbs_log 2>&1
3.查看定时情况：
crontab -l
错误：低版本导出工具导高版本的表时，如果有LOB字段，就会错误，exp-00003:no storage definition found for segment...
解决：
1.执行下面的脚 本重建exu9tne
create or replace view exu9tne(
tsno,fileno,blockno,length) as
select ts#,segfile#,segblock#,length
from sys.uet$
where ext# =1
union all
select * from sys.exu9tneb
2.执行导出
3.导出后执行下面脚本：
create or replace view exu9tue (
tsno,fileno,blockno,length) as
select ts#,segfile#,segblock#,length
from sys.uet$
where ext# =1
1、参数文件目录（启动实例）
/Oracle/app/Oracle/product/10.2.0/Db_1/dbs
2、监听、连接字符串、登陆验证方式 配置文件目录
/Oracle/app/Oracle/product/10.2.0/Db_1/network/admin
3、报警文件目录（与后台进程相关）命名方式：alterSID.log
*.background_dump_dest='/Oracle/app/Oracle/admin/nbo/bdump'
不设置默认：$ORACLE_HOME/rdbms/log
4、跟踪文件目录（与服务器进程相关）
*.user_dump_dest='/Oracle/app/Oracle/admin/nbo/udump'(跟踪文件)
=============================================================================================
跟踪文件nbo进程
=============================================================================================
初始化参数:
与后台进程相关 *.background_dump_dest='/Oracle/app/Oracle/admin/nbo/bdump'(告警文件)
如果不设置这个文件位置在:$ORACLE_HOME/rdbms/log
与服务器进程相关*.user_dump_dest='/Oracle/app/Oracle/admin/nbo/udump'(跟踪文件)
限制跟踪文件大小MAX_DUMP_FILE_SIZE
报警文件alterSID.log
初始化参数： *.background_dump_dest='/Oracle/app/Oracle/admin/nbo/bdump'
V$process为查看进程
中SPID对应操作系统中的进程PID.
=============================================================================================
块管理
=============================================================================================
自动管理：位图管理
手动管理：PCTFREE参数用于指定块中必须保留的最小空闲空间百分比。
PCTUSED参数块中已使用空间比例小于这个百分比，块被标识有效。
db_block_size 指定标准块
blocksize 指定非标准块
======================================================================================================
区管理
======================================================================================================
字典管理:分配/使用/释放时会记录到字典fet,uet,产生回滚信息.
本地管理:推荐本地管理(位图管理)
extent management local(本地表空间管理)
extent management dictionary(字典表空间管理)
一.[自定义区的大小]
[表空间级别]存储参数
create tablespace to1
datafile 'd:/nbo/to_01.dbf' size 20M
default storage(initial 128k next 128k pctincrease 60
minextents 1 maxextents 999)
online;
[表级别]存储参数(表级也可以不指定storage，直接继承表空间级别)
create table t1
(id number(10) not null,
name varchar2(10) not null)
tablespace to1
storage(initial 1M next 2M pctincrease 60
minextents 1 maxextents unlimited);
二.[固定区的大小]
说明：如果是本地管理表空间，可以使用
extent management local autoallocate;(oracle默认64K区，如块超过16k，oracle会分配1M的区)
extent management local uniform 128K;(每个区都是128k)
回收分配给段的区：
truncate...drop storage
已经分配给段，但未使用的区
alter table...deallocate unused
==========================================================================
段管理（数据、临时、索引、回滚）
==========================================================================
自动管理：segment space management auto;
手动管理：segment space management manual;
回滚段:(insert,update,delete时使用)手动和自动管理
1.说明:可以有多个回滚表空间,但只能有一个活动的表空间.
2.块大小为8K的表空间. 一个回滚段默认区32765区
v$transaction:查询回滚段中未提交事务的情况
v$rollname:查询当前活动回滚段的信息.
*.undo_management='AUTO'
临时段:(创建索引,查询,排序时,需要用到临时段)
由系统自动创建和管理.
======================
表空间
======================
表空间-----扩展空间
1.手动增加数据文件大小
alter database datafile 'd:/p2.dbf' resize 1000M
2.表空间数据文件自动扩展
alter database datafile '/export/home/nbodata/product1' autoextend on
next 5M maxsize unlimited;
3.添加数据文件去空间中
alter tablespace tp2 add datafile 'd:/tp2_03.dbf' size 100M autoextend on;
表空间-----用户限制
1.
alter user test quota 50M on tbsk;
2.
alter user sun quota unlimited on tbsk;
3.
grant unlimited tablespace to sun;
表空间-----删除数据文件:
drop tablespace tigtbs_1 including contents and datafiles
cascade contraints;
表空间-----数据文件位置:
select tablespace_name,file_name,file_id from dba_data_files
表空间-----在线/离线/读写/只读
alter tablespace tbsk online/offline/read write/only read;
表空间----重命名
alter tablespace tbsk rename to tbsk2;
表空间----查看存储参数
select tablespace_name "TABLESPACE",
initial_extent "INITIAL_EXT",
NEXT_EXTENT "NEXT_EXT",
MIN_EXTENTS "MIN_EXT",
MAX_EXTENTS "MAX_EXT",
PCT_INCREASE
FROM DBA_TABLESPACES;
大文件表空间：
一个单位很大的数据文件
优点：1.减少SGA的需求.2减少控制文件尺寸.
最大尺寸为:127TB (块大小:32KB)
64TB (db_block_size=16K)
32TB (块大小:8KB)
表空间-----创建大文件:
create bigfile tablespace bigtbs_1
datafile 'e:/bigtbs_1_01.dbf'
size 10M autoextend on;
表空间-----组成的数据文件:
select file_name from dba_data_files where tablespace_name='system';
表空间-----创建非标准块:
1.
alter system set DB_16k_CACHE_SIZE=4M;
2.
create tablespace ublock
blocksize 16k
datafile '/zx/ublock.dbf'
size 30M
autoextend on;
表空间-----字典管理转本地管理
1.查id
select tablespace_name,file_name,file_id from dba_data_files
where tablespace_name='USERS';
2.执行包中过程
execute dbms_space_admin.tablespace_migrate_to_local('USERS','512','4')
表空间-----本地管理转字典管理
execute dbms_space_admin.tablespace_migrate_from_local('USERS');
表空间-----非正常离线,上线
1.介质恢复
alter database recover tablespace tp;
2.在线
alter tablespace tp online;
表空间-----用DELETE删除表后,空间没有增加,使用下面命令:
1.TRUNCATE TABLE
==========================
联机日志文件
==========================
1.查询日志文件组
select * from V$log;
2.查询日志文件
select * from v$logfile order by group#;
3.查询日志文件大小
select group#,bytes/1024/1024||'M' from v$log;
4.创联机日志组
alter database
add logfile group 4
('/export/redo41.log','/export/redo42.log')
size 50M;
5.删除日志文件组
alter database drop logfile group 4;
6.删除日志文件组内文件
alter database drop logfile member '/export/redo43.log';
7.添加日志文件去组
alter database add logfile member '/export/redo43.log' to group 4;
8.联机日志文件切换频率
alter system set archive_lag_target=60 scope=both;
9.日志文件的移动:
1.sys登陆
2.关闭数据库
3.操作系统移动文件
4.到mount状态
5.改控制文件中的日志文件内容:
alter database rename file '/export/redo4.log' to '/redo4.log';
10.参数maxlogmembers限制日志的个数
11.联机日志文件的切换:
alter system switch logfile
==========================================================================
归档日志
==========================================================================
一.切换到归档模式:
1.查看归档情况:
archive log list;
2.关闭数据库
3.参数文件中设置归档日志格式使用变量定义:
*.log_archive_format='log%t_%s_%r.arc'
4.参数文件中设置归档日志路径: /指定
*.log_archive_dest_1='LOCATION=/export/arch1' alter system set log_archive_dest_3='LOCATION=/export/arch1'
*.log_archive_dest_2='LOCATION=/export/arch2'
5.启动到mount状态
startup mount
6.改变数据库为归档模式
alter database archivelog
二.手动归档日志文件(在日志文件还没有写满时,强制归档)
1.归档所有已经填满的日志文件
alter system archive log all;
2.归档当前未写满日志文件,并有一个日志切换
alter system archive log current;
三.手动归档进程启动
说明:ora9i需要手动启动归档进程,否则自动归档需参数文件中加入log_archive_start=true(ora10g不需要,到归档模式就自己启动归档进程)
alter system archive log start;
alter system archive log stop;
四.查看启动归档进程:
select * from v$bgprocess;
五.查看已经归档的日志文件:
select * from v$archived_log;
六.查看归档路径
select * from v$archive_dest;
show parameter log_archive_dest
==========================================================================
备份rman(物理,不包含imp/exp)
==========================================================================
将rman执行结果输入到日志中.
方法1.rman>spool log to '/export/rmanlog1.txt' append;
方法2.c:/>rman log '/export/rmanlog1.txt'
关闭spool log off;
1.用户管理的备份
1.1如处在非归档模式
1.1.1 关闭数据库
1.1.2 复制控制/数据/spfile/等文件到其它地方存放
1.1.3 启动数据库
1.2如处在归档模式
1.2.1 使备份表空间在备份模式
alter tablespace '表空间名' begin backup
1.2.2 复制数据文件到其它地方存放
1.2.3 alter tablespace '表空间名' end backup
1.2.4 归档联机日志文件
2.rman由全库备份
run{
allocate channel c1 type disk;
backup
full
database
format '/home/oracle/target/Backup_ALL_DATABASE_%s_%p_%t';
release channel c1;
}
~~~~~~~~~~~~~~~~~~~
二.恢复目录创建
~~~~~~~~~~~~~~~~~~~
恢复目录数据库上创建表空间/rman用户/授权/
1.create tablespace tbs_catalog
datafile '/export/tools' size 1G;
2.create user rman identified by wy
temporary tablespace temp
default tablespace tbs_catalog
quota unlimited on tbs_catalog;
3.grant recovery_catalog_owner to rman;
启动rman/rman用户登录恢复目录/创建恢复目录指定表空间/rman用户登陆sql/检查
4.$rman
5.rman>connect catalog rman@x
6.create catalog tablespace tbs_catalog;
7.SQL>connect rman@x
8.SQL>select table_name from user_tables;
DB
NODE
CONF
DBINC
在以上内容存在,说明创建成功
~~~~~~~~~~~~~~~~~~~
三.目标数据库注册到恢复目录
~~~~~~~~~~~~~~~~~~~
登陆rman/system用户连接目标库/连到恢复目录数据库/注册
1.$rman
2.rman>connect target sys@z
3.connect catalog rman@x
4.register database
~~~~~~~~~~~~~~~~~~~
四.控制文件rman备份
~~~~~~~~~~~~~~~~~~~
rman自动备份控制文件(当数据库结构发生改变,或rman进行数据库备份时,自动备份控制文件)
1.configure controlfile autobackup on;
2.configure controlfile autobackup format for device type disk to '/zx/cf_%F';
用户管理备份控制文件
1.alter database backup controlfile to '/export/controlbak';
用户管理备份,把控制文件备份成跟踪文件(文本文件)(这个跟踪文件中有创造控制文件的脚本,以后可以来恢复控制文件)
1.alter database backup controlfile to trace;
~~~~~~~~~~~~~~~~~~~
五.归档日志文件rman备份
~~~~~~~~~~~~~~~~~~~
run{
allocate channel c1 type disk;
backup
format '/home/oracle/target/archive_file_%s_%p_%t'
archivelog all delete all input;
release channel c1;
}
~~~~~~~~~~~~~~~~~~~
六.备份表空间rman备份
~~~~~~~~~~~~~~~~~~~
run{
allocate channel c1 type disk;
backup
tablespace USERS
format '/export/bak4_%s_%p_%t';
release channel c1;
}
~~~~~~~~~~~~~~~~~~~
七.SPFILE备份
~~~~~~~~~~~~~~~~~~~
==========================================================================
rman恢复联机日志
==========================================================================
active 不是LGWR正在写的，实例恢复时需要该日志组
current 是 LGWR正在写的，
inactive不是LGWR正在写的，实例恢复时不需要该日志组
~~~~~~~~~~~~~~~~~~~~~~~~~~~
一.损坏(active/inactive/current)一个联机日志文件的恢复(至少有两个日志文件的日志组中损坏一个的情况)
~~~~~~~~~~~~~~~~~~~~~~~~~~~
日志组1中日志文件redo11.log发生错误
1.查看日志组状态
SQL>select group#,status,archived from v$log;
GROUP# STATUS ARC
------ ------------ ---
1 INACTIVE YES
2 CURRENT NO
2.查看损坏日志文件状态
SQL> SELECT GROUP#,STATUS,MEMBER FROM V$LOGFILE;
解决方法一:
1.alter system switch logfile;(如果是当前，需要日志切换(进行归档)):
2.alter database drop logfile member '/Export/redo11.log';(删除组中坏掉文件)
3.alter database add logfile member '/Export/redo11.log' reuse to group 1;(重建坏掉日志文件redo11.log)
解决方法二:
1.shutdown immediate
2.startup mount (启动到日志文件还没有启动的mount状态)
3.$cp /Export/redo12.log /Export/redo11.log(同组好的生成副本)
4.alter database open;
其中一个日志组中的日志文件损坏，如果在线，就切换日志
如果不在线，
~~~~~~~~~~~~~~~~~~~~~~~~~~~
二.损坏inactive(不活动)联机日志组的恢复(日志组中,两个日志文件都损坏情况)
~~~~~~~~~~~~~~~~~~~~~~~~~~~
1.startup mount
2.alter database clear logfile group 1;(清除所有日志组1中所有日志文件)(日志文件归档情况使用)
2.alter database clear unarchived logfile group 1;(日志文件没有归档情况使用)
2.alter database clear logfile group 1 unrecoverable datafile;(数据文件重新在线时，有时需要日志，强行清除恢复时不使用日志)
3.alter database open;
4.立即对库进行全备份
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
三.损坏active(活动)联机日志组的恢复(日志组中,两个日志文件都损坏情况)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
1.alter system checkpoint;(手工产生校验点)
2.如果检验点产生成功:
1.1 shutdown immediate;
1.2 startup mount;
1.3 alter database clear logfile group 1;(清除所有日志组1中所有日志文件)(日志文件归档情况使用)
1.3 alter database clear unarchived logfile group 1;(日志文件没有归档情况使用)
1.3 alter database clear logfile group 1 unrecoverable datafile;(数据文件重新在线时，有时需要日志，强行清除恢复时不使用日志)
1.4 alter database open;
1.5 立即对库进行全备份
2.如果检验点产生失败(或数据库已经宕掉):[三种情况操作恢复]:
1.数据库运行在归档模式 且 数据库有全备份的恢复
1.1 startup mount;
1.2 $rman
1.3 rman>connect target sys/aa@z;(登录到目标数据库,对其进行恢复或备份的数据库)
1.4 rman>connect catalog rman/a@x;(如果使用了恢复目录,可以连接)
1.5 $cp /disk2/backup/*.dbf $ORACLE_HOME/oradata/trgt/(如果是冷备份的,可以用这种方式复制文件)
1.5 RMAN>restore database;(如何是rman进行备份的,使用还原)
1.6 SQL>recover database until cancel;(对数据库不完全恢复)输入:auto
1.7 alter database open resetlogs;
1.8 立即对库进行全备份(rman)
2.数据库运行在非归档模式 且 数据库有全备份的恢复（相当于冷备份恢复）
2.1 $cp /disk2/backup/*.dbf $ORACLE_HOME/oradata/trgt/(复制备份的数据文件)
2.2 startup mount;
2.3 SQL>recover database until cancel;(对数据库不完全恢复)输入:cancel
2.4 alter database open resetlogs;
2.5 shutdown immediate;
2.6 立即对库进行全备份(冷备份)
3.数据库没有任何备份的恢复(强行恢复)
3.1 _allow_RESETLOGS_corruption=TRUE(参数文件中加入,数据库不一致的情况,允许打开)
3.2 startup mount pfile='/initnbo.ora';
3.3 recover database until cancel;(对数据库不完全恢复)输入:cancel
3.4 alter database open resetlogs;
3.5 库导出
3.6 重建数据库
3.7 库导入
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
四.损坏current(当前)联机日志组的恢复(日志组中,两个日志文件都损坏情况)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
如果联机日志组是在数据库正常关闭后损坏的
1.startup mount
2.select group#,status,archived from v$log; (查看日志组状态)
3.recover database until cancel;(对数据库不完全恢复)
4.alter database open resetlogs;
5.立即对库进行全备份
如果联机日志组不是在数据库正常关闭后损坏的
[三种情况操作恢复]
1.数据库运行在归档模式 且 数据库有全备份的恢复
1.1 startup mount;
1.2 $rman
1.3 rman>connect target sys/aa@z;(登录到目标数据库,对其进行恢复或备份的数据库)
1.4 rman>connect catalog rman/a@x;(如果使用了恢复目录,可以连接)
1.5 $cp /disk2/backup/*.dbf $ORACLE_HOME/oradata/trgt/(如果是冷备份的,可以用这种方式复制文件)
1.5 RMAN>restore database;(如何是rman进行备份的,使用还原)
1.6 SQL>recover database until cancel;(对数据库不完全恢复)输入:auto
1.7 alter database open resetlogs;
1.8 立即对库进行全备份(rman)
2.数据库运行在非归档模式 且 数据库有全备份的恢复（相当于冷备份恢复）
2.1 $cp /disk2/backup/*.dbf $ORACLE_HOME/oradata/trgt/(复制备份的数据文件)
2.2 startup mount;
2.3 SQL>recover database until cancel;(对数据库不完全恢复)输入:cancel
2.4 alter database open resetlogs;
2.5 shutdown immediate;
2.6 立即对库进行全备份(冷备份)
3.数据库没有任何备份的恢复(强行恢复)
3.1 _allow_RESETLOGS_corruption=TRUE(参数文件中加入,数据库不一致的情况,允许打开)
3.2 startup mount pfile='/initnbo.ora';
3.3 recover database until cancel;(对数据库不完全恢复)输入:cancel
3.4 alter database open resetlogs;
3.5 库导出
3.6 重建数据库
3.7 库导入
==========================================================================
rman恢复控制文件损坏
==========================================================================
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
镜像文件存在解决方法:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
1.SQL>connect sys@z as sysdba
2.shutdown immediate
3.$cp /wx/control02.ctl /export/control01.ctl
4.startup
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
只有控制文件的自动备份的解决方法:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
1.$rman
2.rman>connect target sys@z;(登录目标数据库)
3.rman>startup nomount
4.rman>set DBID 1365862705 (设置数据库的id)
4.1 $rman target sys/aa@z (连到目标数据库会显示 DBID)
4.1 备份文件名时,里面有个%F格式参数,这个就是DBID
5.rman>restore controlfile from autobackup maxdays 8;
如果自动备份的控制文件不在默认路径,可以设置控制文件的路径与格式:
set controlfile autobackup format for device type disk to '/zx/cf_%F';
6.rman>alter database mount;
7.rman>recover database;
8.rman>alter database open resetlogs;
9.立即对库进行全备份(rman)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
只有rman使用recovery catalog (使用了恢复目录)生成的备份的解决方法:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
现象:SQL>archive log list;时出错或alter database mount;出错
1. $rman
2. rman>connect target sys@z
3. rman>connect catalog rman@x
4. rman>startup nomount
5. rman>set DBID 1365862705 (设置数据库的id)
6. rman>restore controlfile;
7. rman>alter database mount;
8. rman>recover database;
9. rman>alter database open resetlogs;
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
只有用户管理备份，进行恢复的解决方法:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
用户管理备份是采用：alter database backup controlfile to生成的控制文件
1.pfile中,control_files确定控制文件路径
2.$cp bak_controlfile /Export/control01.ctl (备份生成参数指定控制文件)
$cp bak_controlfile /Export/control02.ctl
3.sql>connect sys@z as sysdba (登录目标数据库)
4.sql>startup mount
5.sql>recover database using backup controlfile until cancel;(应用归档日志,输入auto)
6.应用联机日志
1.查数据库SCN
sql>select checkpoint_change# from v$database;
2.查数据文件SCN
sql>select name,checkpoint_change# from V$datafile;
3.查看开始SCN
sql>select name,checkpoint_change# from v$datafile_header;
4.查看终止SCN
sql>select name,last_change# from v$datafile;
数据文件SCN和开始SCN不等,并且终止SCN为空,说明数据库处在不一致状态.需要进一步恢复
5.查看联机日志文件名字
sql>column_member format a50;
sql>select group#,member from v$logfile order by group#;
6.应用联机日志文件
sql>recover database using backup controlfile until cancel;(输入联机日志文件名字,然后回车)
7.打开数据库
sql>alter database open;
8.立即对库进行全备份
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
没有任何备份时，进行恢复的解决方法:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
1.知道数据文件和联机日志文件文件名及路径,数据库字符集和数据库的名字.
2.sql>connect sys@z as sysdba
3.对数据库当前数据文件及联机日志文件备份
4.sql>startup nomount(启动数据库到nomount状态)
5.重建控制文件
create controlfile reuse
set database "NBO" resetlogs noarchivelog
maxlogfiles 16
maxlogmembers 3
maxdatafiles 100
maxinstances 8
maxloghistory 454
logfile
group 1 (
'/export/home/nbodata/nbo/redo12.log',
'/export/home/nbodata/nbo/redo11.log'
)size 20M,
group 2 (
'/export/home/nbodata/nbo/redo22.log',
'/export/home/nbodata/nbo/redo21.log'
)size 20M,
group 3 (
'/export/home/nbodata/nbo/redo32.log',
'/export/home/nbodata/nbo/redo31.log'
)size 20M
datafile
'/export/system01.dbf',
'/export/undotbs01.dbf',
'/export/sysaux01.dbf',
'/export/users01.dbf'
CHARACTER SET ZHS16GBK
6.恢复数据库
创建时使用noresetlogs,可执行完全恢复
resetlogs,可执行不完全恢复
7.打开数据库
数据没有进行恢复或进行完全恢复,使用alter database open
如创建控制文件时使用resetlogs ,使用alter database open resetlogs
8.查检警报文件
如在创建控制文件时,漏掉了某些数据文件,在警报文件中有相关信息MISSINGnnnn....
8.1如:数据文件是只读或者正常离线的,可以重命名MISSINGnnnn..为实际存在的数据文件.
alter database rename file 'MISSING000006' to '/export/indtbs1';
8.2如:数据文件不是只读或者不是正常离线的,只能drop掉数据文件表空间.
==========================================================================
rman恢复数据文件损坏
==========================================================================
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
文件中有坏块，进行恢复的解决方法:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~
1.$rman
2.rman>connect target sys/aa@z
3.rman>connect catalog rman/a@x
4.rman>blockrecover
2>datafile 7 block 3 (修复数据文件7中,块3)
3>datafile 2 block 235
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
数据打开状态,误删掉数据文件,有数据文件备份，
进行恢复的解决方法:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~
1.$rman
2.rman>connect target sys/aa@z
3.rman>connect catalog rman/a@x
4.rman>restore datafile '/export/piT1';(还原)
5.rman>recover datafile '/export/pit1';(恢复)
6.sql>alter database datafile '/export/pit1' online;
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
数据打开状态,误删掉数据文件,没有数据文件备份，
有自创造数据文件以来所有的归档日志文件存在
进行恢复的解决方法:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~
1.alter database create datafile 'e:/wm/pit22' as 'e:/wm/pit22new';(新文件替换原来的pit22)
2.recover datafile 'e:/wm/pit22new';(对新文件执行恢复)
3.alter database datafile 'e:/wm/pit22new' online;(使新数据文件pit22new在线)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
数据库损坏,但只有resetlogs之前的备份
进行恢复的解决方法:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~
满足两个条件:(10g有这个功能)
1.有数据库resetlogs之前的全备份
2.有该全备份以后的所有归档日志文件
1.$rman
2.rman>connect target sys/aa@z
3.rman>connect catalog rman/a@x
4.rman>startup force mount;(启动到mount)
5.rman>run{
2>restore database; (还原resetlogs之前的所有的备份)
3>recover database; (应用自备份以来所有归档日志)
}
6.rman>alter database open;
使用这种方法恢复数据库,不会丢失一条记录
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
把数据库恢复到resetlogs之前的某个状态,进行恢复的解决方法:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~
满足下面条件:
1.要使用恢复目录
2.必须要有resetlogs之前的数据库备份.
3.有该全备份以后的所有归档日志文件
1.$rman
2.rman>connect target sys/aa@z
3.rman>connect catalog rman/a@x
4.rman>list incarnation of database nbo;(nbo是数据库的名字)(确定适合的位置)
5.rman>startup force nomount;
6.rman>reset database to incarnation 4460;(4460:incarnation中的InC Key)
7.rman>set DBID=1365862705 (设置DBID)
8.rman>run{
set unitl SCN 940832;
restore controlfile;
alter database mount;
restore database;
recover database;
}
9.rman>alter database open resetlogs;
==========================================================================
用户错误恢复(表恢复)
==========================================================================
~~~~~~~~~~~
利用表闪回
~~~~~~~~~~~
1.sql>grant flashback any table to test;(把闪回权限赋给操作用户,对表有select,insert,delete,alter权限)
2.sql>alter table syspara enable row movement;(启用表userlist的行移动)
3.sql>flashback table bb to timestamp
to_timestamp('2010-06-13 15:00:00','YYYY-MM-DD HH24:mi:ss');(把表恢复到过去某个时间)
4.select count(*) from syspara;
~~~~~~~~~~~~~~~
利用数据库闪回
~~~~~~~~~~~~~~~
1.决定要恢复的点(可以是SCN/DBA定义的闪回点)
SQL> select name,SCN,to_char(TIME,'YYYY-MM-DD hh24:mi:ss'),database_incarnation# inc,guaranter_
flashback_database,storage_size from v$restore_point;(查出闪回点before_upgrade)
2.rman
3.rman>connect target sys/aa@z
4.rman>shutdown immediate
5.rman>startup mount;
6.sql>select oldest_flashback_scn,oldest_flashback_time from v$flashback_database_log;(查出数据库中实际可以闪回的距离)
7.rman>flashback database
2>to restore point before_upgrade;(执行整个数据库的闪回,闪回到before_upgrade)
7.rman>flashback database to SCN 2917341(闪回也可以用SCN)
7.rman>flashback database to time "to_date('2010-06-13 15:00:00','YYYY-MM-DD HH24:mi:ss')"(闪回某个时间点,这里时间是被删除时的时间)
8.rman>SQL 'alter database open read only';(以只读方式打开数据库,并验证数据库是否正确)
闪回成功以后,需要做的工作:
1.
关闭数据库
把数据库启动到mount状态
用resetlogs打开数据库
2.
export把表导成dump
关闭数据库
把数据库启动到mount状态
rman>recover database;(把数据库恢复到最近的状态,应用日志,恢复到闪回以前的状态,找回丢失的数据库)
rman>alter database open;
使用导入工具,导入dump
1038
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
