第一章 网络概述
一、网络体系结构
1、两层
客户端:硬件条件、ORACLE CLIENT、业务程序
服务器:ORACLE SERVER、DB
限制:C、S需要相同的工作协议
C条件比较多,维护
可扩展性比较差
2、N层
客户端:瘦PC、有浏览器
中间层:ORACLE CLIENT、业务程序、中间层软件
服务器:ORACLE SERVER、DB
好处:C、S不需要相同的工作协议
C条件很少,基本不维护
可扩展性很强
二、ORACLE网络连接方案
1、连接性:oracle net组件是在网络工业协议之上的一个软件层
可以实现C-S S-S的连接
可以屏蔽协议和OS
2、目录服务:OID (LDAP)
3、可伸缩性:
1)connection manager:
连接共享
协议交叉
访问控制
2)shared server
4、安全性:
1)数据传输过程中的加密
2)OSS:提供更好的鉴权
3)防火墙:基于代理,基于包的状态检查
5、与异构服务器的数据连通
支持对外部第三方语言编写的存储过程、函数的调用
6、GUI
1)NET CA
2)NET MGR
---------------------------------------------------------------------------------
第二章 oracle net的结构
C----DS
1)SQLPLUS
2)ISQLPLUS
C-----AS------DS
注册:
1)静态注册
2)动态注册
概念
1)数据库名: db_name
2)实例名 : instance_name
3)服务名 : service_names
4)连接描述符 : 网络服务名
5)监听进程 : 接受客户端的所有请求
6)服务注册 : 静态、动态
7)本地管理 : TNSNAMES
8)集中管理 : NAMES SERVER , OID
9)全局数据库名 : service_names
-----------------------------------------------------------------------------------
第三章 服务器端的配置
1、主要的网络配置文件
server: listener.ora sqlnet.ora tnsnames.ora
client: sqlnet.ora tnsnames.ora
$ORACLE_HOME/network/admin
2、会配置新的监听进程
3、为现有的监听进程,增加PORT
4、静态注册新的数据库
-------------------------------------------------------------------------------
第四章 客户端配置
1、主机命名
sqlnet.ora : HOSTNAME
2、本地命名
sqlnet.ora : TNSNAMES
tesnames.ora
------------------------------------------------------------------------------------------
第五章 共享服务器的配置
1、工作原理
2、好处和缺陷
3、如何配置?
shared server:
create pfile from spfile;
edit pfile: dispatchers='(prot=tcp)(disp=2)'
shutdown immediate
create spfile from pfile;
startup
DBCA
-------------------------------------
dedicated server
create pfile from spfile;
edit pfile: 删除参数dispatchers
shutdown immediate
create spfile from pfile;
startup
4、相关的数据字典
5、client:
tnsnames.ora
专用连接
共享连接
默认连接
6、PGA结构变化
7、配置大池
---------------------------------------------------------------------------
备份策略:
1、物理备份 COPY
2、逻辑备份 exp.exe
3、RMAN备份 dbms_**
NOARCHIVELOG:冷、数据库
ARCHIVELOG:热、部分
物理备份:
一、数据库完全脱机备份 (archivelog, noarchivelog)
1、列出备份清单
select file_name from dba_data_files;
select file_name from dba_temp_files;
select name from v$controlfile;
select member from v$logfile;
database或者dbs 目录:参数文件、口令文件
2、shutdown immediate
3、host copy all file
4、startup
---------------------------------------------------------------------
二、
表空间的冷备份 (archivelog)
1、查看数据文件名字
select file_name from dba_data_files
where tablespace_name='USERS';
2、alter tablespace users offline;
(除了 SYSTEM 、UNDOTBS1、DEFAULT TEMPORARY)
3、host copy
4、alter tablespace users online;
------------------------------------------------------------------------
三、数据文件的冷备份 (archivelog)
alter database datafile 'f:\o9\data1.dbf' offline;
host copy
recover datafile 'f:\o9\data1.dbf'
alter database datafile 'f:\o9\data1.dbf' online;
--------------------------------------------------------------------------
四、表空间热备份(archivelog)
select file_name from dba_data_files
where tablespace_name='USERS';
alter tablespace users begin backup;
host copy
alter tablespace users end backup;
----------------------------------------------------------------------------
五、数据文件热备份(archivelog)
select tablespace_name from dba_data_files
where tablespace_name='F:\O9\DATA1.DBF';
alter tablespace users begin backup;
host copy
alter tablespace users end backup;
--------------------------------------------------------------------------------
六、数据库热备份
select tablespace_name,file_name
from dba_data_files
order by 1;
每个表空间都要执行:
bak0.sql:
alter tablespace users begin backup;
host copy
alter tablespace users end backup;
alter tablespace system begin backup;
host copy
alter tablespace system end backup;
------------------------------------------------------------------------------------
热备份时,数据库掉电,下次数据库启动不了
alter tablespace users begin backup;
shutdown abort
startup
select * from v$recover_file;
select * from v$backup;
mount:
recover database
select * from v$recover_file;
select * from v$backup;
alter database open;
----------------------------------------------------------------------------------------
noarchivelog 不完全恢复:
shutdown immediate
host copy all file (dbf ctl log)
startup
----------------------------------------------------------------------------------------
archivelog 完全恢复
tablespace 恢复
alter tablespace users offline;
host copy 这个表空间的数据文件备份
recover automatic tablespace users
alter tablespace users online;
datafile 恢复
alter database datafile '**.dbf' offline;
host copy 这个数据文件备份
recover automatic datafile '**.dbf'
alter database datafile '**.dbf' online;
DB恢复 (SYSTEM)
shutdown immediate
host copy 损坏或者丢失的数据文件备份
startup mount
recover automatic database
alter database open;
-------------------------------------------------------------------------
控制文件
3---1
3----3
startup nomount
create controlfile ....
alter database open;
alter tablespace temp add tempfile '.....' reuse;
---------------------------------------------------------------------------
redo logfile
current
不完全恢复
shutdown immediate
host copy all datafile
startup mount
recover database until cancel;
alter database open resetlogs;
马上对数据库做完全脱机备份
--------------------------------
no current :
alter database add logfile '**.log' size 2m;
alter database drop logfile group 3;
---------------------------------------------------------------------------
口令文件:
orapwd file= password=
参数文件:
create spfile from pfile;
-------------------------------------------------------------------------------
物理不完全恢复 (noarchivelog)
把上次备份的所有文件,拷贝回来
物理不完全恢复 (archivelog)
shutdown immediate
host copy all datafile
startup mount
alter session set nls_date_format='yyyy-mm-dd:hh24:mi:ss';
recover automatic database until time '2007-12-30:08:30:00';
alter database open resetlogs;
马上对数据库做完全脱机备份
recover automatic database until scn 34000;
recover database until cancel;
-----------------------------------------------------------------------------------
8:00 backup (all datafile + controlfile)
9:00 update
10:00 create tablespace
11:00
要恢复到 9:30
shutdown immediate
host copy all datafile + controlfile
startup mount
alter session set nls_date_format='yyyy-mm-dd:hh24:mi:ss';
recover automatic database until time '2007-12-30:09:30:00' using backup controlfile
alter database open resetlogs;
马上对数据库做完全脱机备份
***********************************************************************************
RMAN 备份
CATALOG创建 (test9)
1、创建表空间
create tablespace rman_ts
datafile 'd:\test9\rman.dbf' size 25m;
2、创建用户RMAN
create user rman identified by rman
default tablespace rman_ts
temporary tablespace temp
quota unlimited on rman_ts;
grant connect,resource,recovery_catalog_owner to rman;
3、创建恢复目录
c:> rman catalog rman/rman@test9 target sys/sys@o9
(test9,o9 在tnsnames.ora中配置好)
RMAN> create catalog tablespace rman_ts;
4、注册目标数据库的信息,到恢复目录中
RMAN> register database;
------------------------------------------
如果是DBCA创建的数据库,RMAN已经存在
alter user rman identified by rman account unlock;
c:> rman catalog rman/rman@test9 target sys/sys@o9
RMAN> register database;
RMAN> create script. s1{
2> backup format 'f:\bak0\%s_%p.bak'
3> (tablespace users);
4> }
RMAN> run{execute script. s1;}
RMAN> create script. s2{
2> backup format 'f:\bak0\%s_%p.bak' filesperset 3
3> (database);
4> }
RMAN> replace script. s2{
2> backup format 'f:\bak0\%s_%p.bak'
3> filesperset 3
4> (database include current controlfile);}
run{execute script. s2;}
-------------------------------------------------------
report schema;
RMAN> create script s3{
2> copy
3> datafile 10 to 'f:\bak0\df10.bak',
4> datafile 11 to 'f:\bak0\df11.bak';}
run{execute script s3;}
----------------------------------------------------------
CATALOG查询目标数据库
1、物理结构 (dbf log arc)
2、脚本名字和内容
3、BACKUP备份结果
4、COPY备份结果
----------------------------------------------------------
并行备份:
show all;
CONFIGURE DEVICE TYPE DISK PARALLELISM 3;
run{execute script. s2;}
---------------------------------------------------------------
备份归档日志文件
BACKUP
FORMAT 'f:\bak0\%s_%p.arc' filesperset 3
ARCHIVELOG ALL DELETE ALL INPUT;
--------------------------------------------------------------
查看备份结果
list backup of tablespace users;
list backup of database;
list backup of controlfile;
list backup of archivelog all;
list backup of datafile 10;
list copy of tablespace users;
list copy of tablespace users;
list copy of datafile 10;
------------------------------------------------------------------
backup:
datafile
controlfile: CONFIGURE CONTROLFILE AUTOBACKUP ON
archive logfile
spfile
tablespace
database
--------------------------------------------------------------------
RMAN> copy
2> datafile 5 to 'f:\bak0\d5.bak',
3> datafile 6 to 'f:\bak0\d6.bak',
4> datafile 7 to 'f:\bak0\d7.bak';
---------------------------------------------------
增量备份
RMAN> backup incremental level 0
2> format 'f:\bak0\%s.%p'
3> (tablespace users);
RMAN> backup incremental level 2
2> format 'f:\bak0\%s.%p'
3> (tablespace users);
累积备份
RMAN> backup incremental level 1 cumulative
2> format 'f:\bak0\%s.%p'
3> (tablespace users);
RMAN> backup incremental level 3 cumulative
2> format 'f:\bak0\%s.%p'
3> (tablespace users);
-------------------------------------------------------------------------------
表空间恢复
run{
sql 'alter tablespace users offline';
restore tablespace users;
recover tablespace users;
sql 'alter tablespace users online';}
数据文件恢复
run{
restore datafile 10;
recover datafile 10;
}
数据库恢复 (mount)
run{
restore database;
recover database;
alter database open;
}
----------------------------------------------------------------------
alter tablespace users read write;
-----------------------------------------
RMAN不完全恢复
目标数据库在MOUNT状态
rman target sys/sys catalog rman/rman
run{
set until time='2007-12-30:09:30:00'
restore database;
recover database;
alter database open resetlogs;}
----------------------------------------------------------
RMAN 维护
crosscheck copy;
crosscheck backup;
list backup of database;
delete backupset 123;
show all;
delete obsolete;
catalog datafilecopy 'f:\bak1\users01.dbf';
change datafilecopy 'f:\bak1\users01.dbf' uncatalog;
---------------------------------------------------------------
register database;
reset database;
resync catalog;
report schema;
----------------------------------------------------------------
逻辑备份和恢复
exp.exe imp.exe
***.dmp (binary)
create ......
insert into....
1、数据库 (除了SYS用户):DBA
2、用户:所有的数据库对象,结构和内容
3、表
4、传输表空间
交互式
命令行
图形工具
exp scott/tiger file=f:\bak0\tab.dmp tables=emp,dept
exp scott/tiger file=f:\bak0\u1.dmp wner=scott
exp 'sys/sys as sysdba' file=f:\bak0\db.dmp full=y
exp system/manager file=f:\bak0\u2.dmp wner=scott
恢复表
1)表不存在
2)表的结构存在 ignore=y
恢复用户
1)用户存在
2)用户被删除 :先创建用户、授予权限
恢复整个数据库
数据库必须可以正常打开
处于OPEN状态
imp scott/tiger file=f:\bak0\tab.dmp tables=emp ignore=y log=f:\bak0\t1.log
----------------------------------------------------
create user scott identified by tiger
default tablespace example
temporary tablespace temp
quota unlimited on example;
grant connect,resource to scott;
imp system/manager file=f:\bak0\u2.dmp full=y
------------------------------------------------------
使用图形工具,自动备份数据库
1)RMAN
2)EXP
首选身份证明
W:本地安全策略----> 作为批处理登录
-------------------------------------------------------
表空间传输 s1----> s2
s1:
alter tablespace data2 read only;
exp 'sys/sys as sysdba' file=f:\bak0\data2.dmp
TRANSPORT_TABLESPACE=y TABLESPACES=data2
s1----> s2:
copy **.dbf **.dmp
s2:
create user scott identified by tiger;
grant connect,resource to scott;
imp 'sys/sys@test9 as sysdba' file=f:\bak0\data2.dmp
TRANSPORT_TABLESPACE=y
TABLESPACES=data2 DATAFILES=d:\test9\data2.dbf
s1,s2 :
alter tablespace data2 read write ;
------------------------------------------------------------------------------
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/13048648/viewspace-758123/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/13048648/viewspace-758123/