【Oracle教学】10g Administration Workshop I

2. 安装Oracle ★★★★★
安装Oracle软件的相关操作系统,软件架构知识点
安装数据库的二三事

3. 创建数据库 ★★ (ref: 1-创建数据库时指定字符集,连接数设置)
使用DBCA创建自定义不同应用类型的数据库
eBay数据库和上证所Topview数据库的区别

0.在linux下安装oracle及Linux的基本用户、文件、文件夹管理
1) 安装Linux,选择安装全部“开发工具”组件,再安装“libxp.rpm”,参见网络视频。

2) 验证已安装的程序包:

# rpm -q make gcc compat-db
make-3.80-5
gcc-3.4.3-9.EL4
compat-db-4.1.25-9

3)添加组和用户

/usr/sbin/groupadd oinstall
/usr/sbin/groupadd dba
/usr/sbin/useradd -m -g oinstall -G dba oracle
id oracle

4)修改密码

passwd oracle

5)创建目录

mkdir -p /u01/app/oracle
chown -R oracle:oinstall /u01/app/oracle
chmod -R 775 /u01/app/oracle

6)root用户执行,修改内核参数

cat >> /etc/sysctl.conf <

7)root用户执行,修改系统配置:

cat >> /etc/security/limits.conf <> /etc/pam.d/login <> /etc/profile <> /etc/csh.login <

8)oracle用户执行,修改profile文件:

cat >> /home/oracle/.bash_profile <

4. 管理数据库实例 ★★★★
使用DB Control管理数据库,数据库关闭、启动内部操作
数据库启动关闭的排障方法

1. Oracle产品线简述 ★
网格化应用和云计算;Oracle数据库架构
图书馆和Oracle的关系

43-8. 监控和管理内存 ★★★★
详解Oracle实例的各个组成部分
著名的ORA-4031错误

5. 数据库存储结构 ★★★★★
数据库,表空间,数据文件,段、区间、块的概念和管理
如何规划设计一个500G的数据库

6. 用户安全性 ★★
创建用户,管理权限和密码口令
重建Oracle密码文件

7. 管理方案对象 ★★★★
管理数据库的约束、索引、表、序列等等
怎么都查不出结果的数据字典

8. 管理数据和并发处理 ★★★
PL/SQL语言和对象的使用;数据库的封锁机制
死锁:蛇吃尾巴的启示

9. 管理还原 ★★★
了解UNDO的概念和工作原理
经典的ORA-01555错误

10. 实施Oracle安全性 ★★
数据库审计、精细访问控制的使用
将要上市的公司如何欺骗四大会计事务所

SQL> alter system set audit_trail =’DB’ scope=spfile;
SQL> shutdown immediate
SQL> startup
SQL> audit session;
SQL> select * from dba_stmt_audit_opts;

11. 配置Oracle网络环境 ★★★★★
数据库如何建立一个连接,tnsnames和listener的作用
2个数据库网络故障的解决案例

12. 自主维护的Oracle ★★★
使用AWR和ADDM解决数据库性能问题,使用数据库预警功能
解读一个性能问题重重的数据库AWR报告

13. 性能管理 ★★
使用SQL指导器优化查询,如何打开自动内存共享管理
如何建立数据库性能监控和排障的体系

grant advisor to hr;

DECLARE
  my_task_name VARCHAR2(30);
  my_sqltext   CLOB;
BEGIN
  my_sqltext   := 'SELECT distinct a.employee_id, a.last_name, a.job_id, a.department_id FROM employees a, employees b WHERE a.employee_id=b.manager_id';
  my_task_name := dbms_sqltune.create_tuning_task(
      sql_text    => my_sqltext,
      bind_list   => null,
      user_name   => 'HR',
      scope       => 'COMPREHENSIVE',
      time_limit  => 60,
      task_name   => 'sql_tuning_test',
      description => 'Tuning Task');
END;
/

exec dbms_sqltune.execute_tuning_task('sql_tuning_test');

SELECT status FROM USER_ADVISOR_TASKS WHERE task_name = 'sql_tuning_test';

SET LONG 999999
set serveroutput on size 999999
SET LINESIZE 100
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('sql_tuning_test') FROM DUAL;

exec dbms_sqltune.drop_tuning_task('sql_tuning_test');

14. 备份和恢复的概念 ★★★★★
数据库的故障类型,备份恢复中最重要的是哪些文件?
因为归档导致的数据库挂起事故

15. 进行数据库备份 ★★★★
各种备份数据库的方法、优劣比对
简单的RMAN备份操作

SQL> startup nomount;
SQL> alter system set log_archive_dest_1='LOCATION=/archive';
SQL> alter database mount;
SQL> alter database archivelog; / alter database noarchivelog;
SQL> alter database open;

RMAN> backup database;
RMAN> BACKUP DATABASE FORMAT '/home/oracle/backup/%U';
RMAN> BACKUP DATABASE FORMAT '/home/oracle/backup/%U' INCLUDE CURRENT CONTROLFILE;
RMAN> backup tablespace users;
RMAN> BACKUP DATAFILE 1;
RMAN> BACKUP AS COPY DATAFILE 4 FORMAT '/home/oracle/backup/djedu_dbfno4.dbf';

RMAN> BACKUP CURRENT CONTROLFILE;
SQL> alter database backup control file to trace;

RMAN> BACKUP ARCHIVELOG ALL;
RMAN> BACKUP DATABASE PLUS ARCHIVELOG;
RMAN> BACKUP ARCHIVELOG ALL DELETE ALL INPUT; 

16. 执行数据库恢复 ★★★★
在数据文件、控制文件、重做日志丢失的情况下恢复数据库
数据文件,控制文件,重做日志都没了,还能拯救数据库么?

SQL> !rm /u01/app/oracle/oradata/djedu/system01.dbf;
SQL> select * from v$recover_file;
RMAN> restore database;
RMAN> recover database;
RMAN> sql 'alter database open';

SQL> !rm /u01/app/oracle/oradata/djedu/users01.dbf;
SQL> select * from v$recover_file;
SQL> alter database datafile 4 offline drop;
SQL> alter database open;
RMAN> restore datafile 4;
RMAN> recover datafile 4;
RMAN> sql 'alter database datafile 3 online';

17. 执行闪回 ★★
闪回在各个场景下的使用方法,与数据库恢复的区别在那里
上海电力使用闪回找回误删除的电费单

SQL> create table fb_test as select employee_id,first_name,last_name from employees;
SQL> select count(*) from fb_test;
SQL> delete from fb_test where employee_id<105;
SQL> commit;
SQL> select count(*) from fb_test;
SQL> select count(*) from fb_test as of timestamp sysdate-1/1440;

18. 移动数据 ★★★ (ref: 12-会使用exp、expdp进行用户、表的迁移)
使用SQL Loader、data pump和外部表转移数据
面对数亿的海量数据导入,如何快点解决去陪女朋友吃饭?

19. RMAN使用
1). 显示当前所有的配置

RMAN> show all;

2). 配置RMAN参数

RMAN> CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;

3). 列出数据库中的备份信息:

RMAN> LIST BACKUP;
RMAN> LIST BACKUP OF DATABASE;
RMAN> LIST COPY OF TABLESPACE SYSTEM;
RMAN> LIST BACKUP OF DATAFILE 1;

4). 检查备份:

RMAN> report obsolete;
RMAN> change backupset id delete;
RMAN> change archivelog all crosscheck;
RMAN> crosscheck backup;
RMAN> delete expired backup;

5). 删除备份

RMAN> delete backup;
RMAN> delete copy;
RMAN> delete obsolete;
RMAN> delete expired backup;
RMAN> delete expired copy;
RMAN> delete backupset 19;
RMAN> delete backuppiece 'd:\backup\DEMO_19.bak';
RMAN> delete datafilecopy 'd:\backup\DEMO_19.bak';

6). 备份后删除输入对象

RMAN> delete archivelog all delete input;
RMAN> delete backupset 22 format = ''/home/oracle/backup/%u.bak'' delete input;

7). 数据库完全备份

RMAN> backup database;
RMAN> BACKUP DATABASE FORMAT '/home/oracle/backup/%U';
RMAN> BACKUP DATABASE FORMAT '/home/oracle/backup/%U' INCLUDE CURRENT CONTROLFILE;
RMAN> backup tablespace users;
RMAN> BACKUP DATAFILE 1;
RMAN> BACKUP AS COPY DATAFILE 4 FORMAT '/home/oracle/backup/djedu_dbfno4.dbf';

8). 备份控制文件

RMAN> BACKUP CURRENT CONTROLFILE;
SQL> alter database backup control file to trace;

9). 备份归档日志

RMAN> BACKUP ARCHIVELOG ALL;
RMAN> BACKUP DATABASE PLUS ARCHIVELOG;
RMAN> BACKUP ARCHIVELOG ALL DELETE ALL INPUT; 

10). 恢复控制文件

RMAN> startup nomount;
RMAN> set dbid=1383524435;
RMAN> restore controlfile to '/u01/app/oracle/oradata/djedu/control01.ctl' from '/home/oracle/backup/04msd4c3_1_1';
RMAN> restore controlfile to '/u01/app/oracle/oradata/djedu/control01.ctl' from autobackup;

11). 基于OS操作备份恢复

SQL> alter tablespace system begin backup;
SQL> !cp /u01/app/oracle/oradata/djedu/system01.dbf /home/oracle/backup/system01.dbf.bak;
SQL> alter tablespace system end backup;
SQL> !rm /u01/app/oracle/oradata/djedu/system01.dbf;
SQL> select * from v$recover_file;
SQL> !cp /home/oracle/backup/system01.dbf.bak /u01/app/oracle/oradata/djedu/system01.dbf;
SQL> recover database;

SQL> alter tablespace users begin backup;
SQL> !cp /u01/app/oracle/oradata/djedu/users01.dbf /home/oracle/backup/users01.dbf.bak;
SQL> alter tablespace users end backup;
SQL> !rm /u01/app/oracle/oradata/djedu/users01.dbf;
SQL> select * from v$recover_file;
SQL> alter database datafile 4 offline drop;
SQL> alter database open;
SQL> !cp /home/oracle/backup/users01.dbf.bak /u01/app/oracle/oradata/djedu/users01.dbf;
SQL> recover datafile 4;

12). 基于RMAN的基本备份恢复

SQL> !rm /u01/app/oracle/oradata/djedu/system01.dbf;
SQL> select * from v$recover_file;
RMAN> restore database;
RMAN> recover database;
RMAN> sql 'alter database open';

SQL> !rm /u01/app/oracle/oradata/djedu/users01.dbf;
SQL> select * from v$recover_file;
SQL> alter database datafile 4 offline drop;
SQL> alter database open;
RMAN> restore datafile 4;
RMAN> recover datafile 4;
RMAN> sql 'alter database datafile 3 online';
Trackback

no comment untill now

Add your comment now

切换到手机版