oracle数据库管理学习笔记

1、 查看数据库状态：select status from v$instance;

2、 恢复初始化文件：

D:\\developtool\\oracle\\product\\10.2.0\\admin\\orcl\\bdump\\alert_orcl.log文件中提取内容。

手动创建initsid.ora

创建spfilesid.ora: create spfile from pflie

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

show parameter spfile 查看spfile位置

initorcl.ora 文本

spfileorcl.ora 二进制文件（9i后有，使用原因：a 动态修改，b 存在设备 c Rman自动备份 d server parameter file）

3、备份

一、逻辑备份（数据）

二、手工备份

a、归档模式：热备份（优点：不影响工作，局部备份；缺点：备份日志，不一致）

1、改初始化参数

2、改数据库

9i：alter system set log_archive_dest_1='LOCATION=c:\\arc' scope=spfile;

alter system set log_archive_format='%s.arc' scope=spfile;

alter system set log_archive_start=true scope=spfile;

10g：alter system set log_archive_dest_1='LOCATION=c:\\arc' scope=spfile;

alter system set log_archive_format='%s_%t_%r.arc' scope=spfile;

启动关闭：

shutdown immediate;

startup mount;

archive log list;

修改数据库为归档数据库：

alter database archivelog；

alter system switch logfile;

scn（system change number）只能大，不能小。如果要后退，请从更小的scn增长到相对小。

控制文件一定要正确的描述数据库的结构和行为。

查看scn号：select dbms_flashback.get_system_change_number from dual;

查看备份:

select * from v$backup;

查看表空间：

select file_name,bytes from dba_data_files where tablespace_name='USERS';

查看改变的scn:

COL NAME FOR A55

SET LINES 100

SET PAGES 100

SELECT NAME,CHECKPOINT_CHANGE# FROM V$DATAFILE;

修改表空间：

ALTER TABLESPACE USERS BEGIN BACKUP;

存盘：

ALTER SYSTEM CHECKPOINT;

结束backup：

alter tablespace users end backup;

热备份脚本：

数据文件：

SELECT 'ALTER TABLESPACE '||TABLESPACE_NAME ||' BEGIN BACKUP;'

||CHR(10)

||'HOST COPY '||FILE_NAME||' C:\\BK'

||CHR(10)||

'ALTER TABLESPACE '||TABLESPACE_NAME ||' END BACKUP;'

FROM DBA_DATA_FILES ORDER BY 1;

ALTER TABLESPACE EXAMPLE BEGIN BACKUP;

HOST COPY D:\\DB237\\ORCL\\EXAMPLE01.DBF C:\\BK

ALTER TABLESPACE EXAMPLE END BACKUP;

ALTER TABLESPACE SYSAUX BEGIN BACKUP;

HOST COPY D:\\DB237\\ORCL\\SYSAUX01.DBF C:\\BK

ALTER TABLESPACE SYSAUX END BACKUP;

ALTER TABLESPACE SYSTEM BEGIN BACKUP;

HOST COPY D:\\DB237\\ORCL\\SYSTEM01.DBF C:\\BK

ALTER TABLESPACE SYSTEM END BACKUP;

ALTER TABLESPACE UNDOTBS1 BEGIN BACKUP;

HOST COPY D:\\DB237\\ORCL\\UNDOTBS01.DBF C:\\BK

ALTER TABLESPACE UNDOTBS1 END BACKUP;

ALTER TABLESPACE USERS BEGIN BACKUP;

HOST COPY D:\\DB237\\ORCL\\USERS01.DBF C:\\BK

ALTER TABLESPAC

E USERS END BACKUP;

控制文件备份：

ALTER DATABASE BACKUP CONTROLFILE TO 'C:\\CONTROL.BK' REUSE;

ALTER DATABASE BACKUP CONTROLFILE TO TRACE;

SHOW PARAMETER USER_D;

日志：归档日志

控制文件恢复：

1 ALTER DATABASE BACKUP CONTROLFILE TO TRACE; 备份控制文件

打开oracle\\product\\10.2.0\\admin\\orcl\下最新的备份文件，查找 Set #1. NORESETLOGS case后面的控制文件脚本

执行脚本

执行RECOVER DATABASE;

ALTER DATABASE OPEN;

执行临时恢复脚本

2 将备份的C:\\CONTROL.BK拷贝到控制文件路径下，重新起名01，02，03

具体见RECOVER_USING_BACKUP_CONTROLFILE.txt文件。

b、非归档模式：冷备份 （步骤：停：一致性停库，shutdown immediately

考： select name from v$datafile

union all

select name from v$controlfile

union all

select member from v$logfile

union all

select name from v$tempfile;

启动：生成脚本

select 'alter database rename file '||

''''||name||''''||' to '||chr(10)

||''''||'d:\\db237\\orcl\\'||substr(name,24)||''''||';' from v$datafile;

select 'alter database rename file '||

''''||member||''''||' to '||chr(10)

||''''||'d:\\db237\\orcl\\'||substr(member,24)||''''||';' from v$logfile;

alter database rename file 'old' to 'new';

alter system set control_files='#####','#####','#####' scope=spfile;

）

优点：安全可靠

缺点：停数据库，不能局部备份

三、Rman（小工具，替我们管理备份）

4、修改数据库实例

1、oradim 建立服务：oradim -new -sid name 删除命令：oradim -delete -sid name

2、注册表：local\\software\\oracle\\home ,oracle_sid=要更改的实例名

3、直接更改初始化参数文件和密码文件名称（\\oracle\\product\\10.2.0\\db_1\\database路径下，initsid.ora，pwdsid.ora）

4、更改初始化参数文件名：instance_name=实例名

通过create spfile from pfile; 将文本文件initsid.ora转换成spfilesid.ora

5、show parameter name 查看结果

5、冷备份在其他机器上恢复：

1、拷贝datafile，controlfile，logfile

2、初始化参数文件，密码

3、注册表 oracle_sid

4、oradim

5、control_files=

6、初始化参数文件中的路径

6、orapwd密码文件修改

ALTER SESSION SET NLS_LANGUAGE=AMERICAN; 修改语言环境。

7、数据库改名

改变配置文件中的数据库名

执行打开oracle\\product\\10.2.0\\admin\\orcl\下最新的备份文件中 Set #2. RESETLOGS case 后面的脚本，并在实例名后增加set database 数据库名

8、dml语句失败（不需要恢复，自动回退了，

事物保留）

ddl语句失败（不需要恢复，自动提交了）

网络失败（自动恢复，不需要恢复）

9、系统表文件热备：

备份：

查看文件：

select name from V$datafile;

备份开始

alter tablespace system begin backup;

host copy 源路径 目标路径

alter tablespace system end backup;

查看备份：

select * from v$backup;

查看备份文件：

select * from v$recover_file

查看备份日志：

select * from v$recovery_log;

恢复备份文件1：

recover datafile 1;

选择日志文件。

10、怎样将scott移到users表空间

DROP USER SCOTT CASCADE;

@%ORACLE_HOME%\\RDBMS\\ADMIN\\SCOTT

CONN / AS SYSDBA

select tablespace_name from dba_tables where

table_name='EMP'

11、修改9i离线

startup

alter database datafile 9 offline;

alter database open;

12、临时表空间

新建 修改默认 删除旧的

13、日志挖掘

a、指定要挖掘的文件，可以是归档日志文件，可以同时挖掘多个。

b、开始挖掘

c、查看挖掘结果

d、结束挖掘

查看日志文件路径：

select member from V$logfile;

查看dbms_logmnr结构：

desc dbms_logmnr

增加日志挖掘日志文件：

exec dbms_logmnr.add_logfile('文件路径');

开始挖掘：

exec dbms_logmnr.START_LOGMNR(OPTIONS=>16);

或者

exec dbms_logmnr.START_LOGMNR(OPTIONS=>DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG);

查看options=16的原因

select text from dba_source where name='DBMS_LOGMNR'

AND ROWNUM<300;

查看V$LOGMNR_CONTENTS结构：

DESC V$LOGMNR_CONTENTS

查看被drop的表

SELECT SCN,SQL_REDO FROM V$LOGMNR_CONTENTS

WHERE UPPER(SQL_REDO) LIKE '%DROP TABLE%';

查看被修改的表，需要执行下面语句：

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

查看被修改的表：

SELECT SCN,SQL_REDO FROM V$LOGMNR_CONTENTS

WHERE UPPER(SQL_REDO) LIKE '%UPDATE "SCOTT%';

14、rman

list：做了什么

list backup (list (操作）... of （内容、文件）....

report：要做什么

report schema;

copy：映像拷贝

copy datafile 4 to 'c:\\bk\\f4.cp';

backup：打包备份

backup datafile 4 format 'c:\\bk\\f4_%u.bk';

备份集：一次备份，逻辑的

RUN {

ALLOCATE CHANNEL d1 TYPE disk;

set limit channel d1 kbytes=1000;

backup datafile 4 FORMAT 'c:\\bk\\%s_%p_%u.bk' ;}

备份片：物理上的（备份集默认对应一个备份片，也可以对应多个）

%s 备份集 %p备份片 %u自动命名

全备份：

增量备份：（0，1，1c，2，2c，3，3c，4，4c）

backup incremental level 0 datafile 4 format 'c:\\bk\\f40_%u.bk';

backup incremental level 1 datafile 4 format 'c:\\bk\\f41_%u.bk';

backup incremental level 2 datafile 4 format 'c:\\bk\\f42_%u.bk';

backup i

ncremental level 3 datafile 4 format 'c:\\bk\\f43_%u.bk';

backup incremental level 4 datafile 4 format 'c:\\bk\\f44_%u.bk';

备份1c

backup incremental level 1 cumulative datafile 4 format 'c:\\bk\\f4_%u.bk';

--------------------归档日志文件的备份和恢复----------------------------

list archivelog all;

list copy of archivelog all;

list backup of archivelog all;

delete archivelog all;

list copy of archivelog sequence between 2 and 265 thread=1 ;

copy archivelog 'F:\\oracle\\oradata\\zl9\\arch\\ARC2.LOG' to 'c:\\bk\\a.cp';

BACKUP ARCHIVELOG ALL DELETE INPUT format 'c:\\bk\\arc%s.bk';

backup archivelog sequence between 207 and 208 thread=1 format 'c:\\bk\\arc%s.bk';

backup archivelog until time 'sysdate' format 'c:\\bk\\arc%s';

backup archivelog like '%_1_%' format 'c:\\bk\\arc%s';

--------------------spfile参数文件的备份和恢复----------------------------

backup spfile format 'c:\\bk\\spfile.%s';

list backup of spfile;

--------------------控制文件备份----------------------------------------

copy current controlfile to 'c:\\bk\\c1.ctl';

backup current controlfile format 'c:\\bk\\c1.%s';

list copy of controlfile;

list backup of controlfile;

数据文件的恢复

数据库处在mount状态，可通过alter database open看哪个数据文件有问题。

restore datafile 数据文件号;

recover datafile 数据文件号;

控制文件的恢复

数据库nomount状态

rman：restore controlfile from ‘备份文件路径’；

oracle：alter database mount；

rman：recover database；

oracle：alter database open resetlogs；

将恢复的文件换到其他目录

RUN

{

ALLOCATE CHANNEL d1 DEVICE TYPE DISK;

SQL "ALTER TABLESPACE users OFFLINE IMMEDIATE"; --下线

SET NEWNAME FOR DATAFILE 'D:\\DB237\\ORCL\\USERS01.DBF'

TO 'C:\01.dbf'; -- 原有数据文件设定新路径

RESTORE DATAFILE 'D:\\DB237\\ORCL\\USERS01.DBF'; -- 恢复旧的控制文件

SWITCH DATAFILE 'D:\\DB237\\ORCL\\USERS01.DBF'; -- 转换旧文件到新文件

RECOVER DATAFILE 'C:\01.dbf'; -- 恢复新文件

SQL "ALTER TABLESPACE users ONLINE"; -- 上线

}

资料库

创建资料库用户

create user zlk identified by a;

alter user zlk default tablespace users;

grant recovery_catalog_owner, connect, resource to zlk;

连接到资料库

rman catalog zlk/a@##

创建资料库

create catalog ;

查看资料库对象

select object_type,count(*) from user_objects

group by rollup(object_type);

查看表

select *　from　tab；

连接本地库和资料库

rman target / catalog zlk/a@##

注册资料库

register database;

创建备份脚本

db_name:db124

-- zhanglie

CREATE script b0 {

backup

incremental level 0

format 'c:\\bk\\%d_%s_%p_%u.bk'

filesperset 3 ---每三个打一个包

(database include current controlfile);

sql "alter system archive log current"; ---归档

BACKUP ARCHIVELOG ALL DELETE INPUT format 'c:\\bk\\arc%s_%u.bk';

delete noprompt OBSOLETE ;

Backup current controlfile format 'c:\\bk\\%d_%s_%p_%u.ctl';

}

-- zhanglie

CREATE script b1 {

backup

incremental level 1

format 'c:\\bk\\%d_%s_%p_%u.bk'

filesperset 3

(database include current controlfile);

sql "alter system archive log current";

BACKUP ARCHIVELOG ALL DELETE INPUT format 'c:\\bk\\arc%s_%u.bk';

delete noprompt OBSOLETE ;

Backup current controlfile format 'c:\\bk\\%d_%s_%p_%u.ctl';

}

-- zhanglie

CREATE script b2 {

backup

incremental level 2

format 'c:\\bk\\%d_%s_%p_%u.bk'

filesperset 3

(database include current controlfile);

sql "alter system archive log current";

BACKUP ARCHIVELOG ALL DELETE INPUT format 'c:\\bk\\arc%s_%u.bk';

delete noprompt OBSOLETE ;

Backup current controlfile format 'c:\\bk\\%d_%s_%p_%u.ctl';

}

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

tablespace：

SEGMENT:相同属性的范围（段）

EXTENT:一次分配，连续的块（范围）

BLOCK:最小io单元 (块）

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

dbv file='N:\\ORACLE\\ORADATA\\ORA10\\SYSTEM01.DBF' blocksize=8192 检查块

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

查看表空间使用情况

select tablespace_name,round(sum(bytes)/1024/1024) mb

from dba_data_files

group by rollup(tablespace_name);

查看当前连接数据库的用户

select sid,username,machine,sql_hash_value from V$session where SQL_HASH_VALUE<>0;

select sid,username,machine,sql_text

from V$session a,v$sqlarea b

where a.SQL_HASH_VALUE=b.hash_value;