【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';
no comment untill now